Set system profile values from SQL Oracle Apps

Use fnd_profile.save to set system profile at site level from SQL.


Declare
value Boolean;
Begin
value := fnd_profile.save('put profile name code','new_profile_value','SITE');
commit;
End;

OM: Your profile option does not allow automatic numbering e-Commerce Gateway

e-Commerce Gateway Inbound need the Profile Option ‘Sequential Numbering’ needs to be set
at the application and responsibility level to be able to generate order numbers in Order Management"

 

Check FTP/SFTP status in Shell script

Status of FTP / SFTP can be check with below code in the shell script


2>&1 -- This is going to redirect stderr to stdout


result=`ftp -nv 111.111.111.111 << EOF 2>&1
user $USER $PASSWD
put file.txt
bye
EOF`

ctr=`echo "$result" | grep "226 Transfer complete" | wc -l`

#for error check, check the return string as it's different for FTP and SFTP

err_ctr=`echo "$result" | grep "Couldn't read packet" | wc -l`


if [ $ctr -gt 0 ]
then
echo "File Transfer successful"
elif [ $err_ctr -gt 0 ]
then
echo "connection failure"
fi

Passing More than 9 parameters for Oracle Apps Shell/Unix Concurrent program

Oracle Apps Shell Host Concurrent program can accept more than 9 parameters but when passed 10,11 and so on to unix variable as $10 or $11 it only gets the $1 parameter which is apps username and password.

To use $10, $11 parmaters it has to be assigned as ${10}, ${11},,,

Eg: Assign 10th or 11th parameter to unix variable in shell script


tenthparam=${10}
eleventhparam=${11}

Query to find Workflow Notification Mailer Outbound Server Name

Below is the query to find the Workflow notification Mailer Outbound server details.


SELECT   parameter_value
  FROM fnd_svc_comp_param_vals fscpv
  , fnd_svc_comp_params_b fscp
  WHERE fscpv.parameter_id = fscp.parameter_id
    AND fscp.parameter_name = 'OUTBOUND_SERVER';


You can find the other workflow mailer related attributes using the above tables.

XML PUBLISHER TABLES

select a.*, rowid from xdo_templates_b a
where template_code like '%ARXSGP%'

select a.*, rowid from xdo_templates_tl a
where template_code like '%ARXSGP%'

select a.*,rowid from xdo_lobs a where lob_code like '%ARXSGP%'

select a.*, rowid from XDO_DS_DEFINITIONS_B a where data_source_code like '%ARXSGP%'

select a.*, rowid from XDO_DS_DEFINITIONS_TL a where data_source_code like '%ARXSGP%'

Finally Close PO Requisition Lines API Oracle Apps R12

At times there is need to Finally close only requisition lines but not header.
Below API can be used to fulfill the requirement. Test API thoroughly before using it in further instances as this is a ripped version from Requisition Header close api.

DECLARE
  v_returned           BOOLEAN;
  v_return_code_h      VARCHAR2(1);
  lc_return_status     VARCHAR2(50);
  lc_exception_msg     VARCHAR2(4000);
  lc_return_code       VARCHAR2(50);
  ln_online_report_id  NUMBER;
  v_error_code         VARCHAR2(4000);
  lc_req_control_error VARCHAR2(4000);
BEGIN
  fnd_global.Apps_initialize (user_id,resp_id, 201);
  mo_global.Set_policy_context('S',org_id);
  FOR po_req IN
  (
         SELECT *
         FROM   req_closed_tbl opc; )
  LOOP
    v_error_code := NULL;
    po_req_dist_sv.Update_reqs_distributions (x_req_header_id => po_req.requisition_header_id ,
                                              x_req_line_id => po_req.requisition_line_id,
                                              x_req_control_action => 'FINALLY CLOSE' ,
                                              x_req_action_date => SYSDATE,
                                              x_req_control_error_rc => v_error_code);

    IF v_error_code IS NULL THEN
      po_reqs_control_sv.Maintain_supply (x_supply_action => 'Remove_Req_Line_Supply',
                                          x_supply_id => po_req.requisition_line_id,
                                          x_req_control_error_rc => v_error_code);

      IF v_error_code IS NULL THEN
      po_req_lines_sv.update_reqs_lines_status (x_req_header_id => po_req.requisition_header_id ,
                                                x_req_line_id => po_req.requisition_line_id ,
                                                x_req_control_action => 'FINALLY CLOSE' ,
                                                x_req_control_reason => 'Put Reason here' ,
                                                x_req_action_date => SYSDATE,
                                                x_oe_installed_flag => 'Y',
                                                x_req_control_error_rc => v_error_code);
    END IF;
  END IF;
IF v_error_code IS NOT NULL THEN
 -- Put logic here
END IF;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(' Exception closing Requisition lines using po_reqs_control_sv.update_reqs_lines_status '||SQLERRM);
END;