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;

Finally Close PO Requisition API Oracle Apps R12

Below API is used to Finally Close PO Requisitions.

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
po_reqs_control_sv.update_reqs_status( X_req_header_id => po_req.requisition_header_id 
                                             , x_req_line_id =>  po_req.requisition_line_id 
                                             , x_agent_id => po_req.preparer_id 
                                             , x_req_doc_type => po_req.type_lookup_code--TYPE_LOOKUP_CODE
                                             , x_req_doc_subtype => po_req.document_type_code--DOCUMENT_TYPE_CODE
                                             , X_req_control_action => 'FINALLY CLOSE' 
                                             , X_req_control_reason => 'Put your description' 
                                             , X_req_action_date => SYSDATE 
                                             , X_encumbrance_flag => 'N' 
                                             , X_oe_installed_flag => 'Y' 
                                             , X_req_control_error_rc => v_error_code);

              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 using po_reqs_control_sv.update_reqs_status'||SQLERRM);

END;

Finally Close PO API Oracle APPS R12

Below API is used to Finally Close PO's in R12. API will not finally close partially received PO's. Also it will return success in case if it fails to close PO's. So write your own validation program/block.

In Order to close only lines/shipments pass values to p_line_id / p_shipment_id arguments in the API along with header level details.

Eg: If only line has to be closed then pass line_id along with header_id. Same way if shipment line has to be closed then header_id, line_id, line_location_id(shipment_id) has to be passed.


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;
     lc_doc_type VARCHAR2(240);
BEGIN

     FOR po_head IN
     (SELECT * FROM PO_CLOSED_TBL opc WHERE process_flag = 'N'
     )
     LOOP

          FND_GLOBAL.APPS_INITIALIZE (user_id,resp_id, 201);

         select decode(po_head.document_type_code,'STANDARD','PO','PA') INTO lc_doc_type from dual;

         PO_DOCUMENT_ACTION_PVT.do_manual_close( p_action => 'FINALLY CLOSE' ,
                                                 p_document_id => po_head.po_header_id ,
                                                 p_document_type => lc_doc_type ,
                                                 p_document_subtype => po_head.document_type_code ,
                                                 p_line_id => null,--po_head.po_line_id ,
                                                 p_shipment_id => NULL ,
                                                 p_reason => 'Put reason for close' ,
                                                 p_action_date => SYSDATE ,
                                                 p_calling_mode => 'PO' ,
                                                 p_origin_doc_id => NULL ,
                                                 p_called_from_conc => FALSE ,
                                                 p_use_gl_date => 'N' ,
                                                 x_return_status => lc_return_status ,
                                                 x_exception_msg => lc_exception_msg ,
                                                 x_return_code => lc_return_code ,
                                                 x_online_report_id => ln_online_report_id );


          IF lc_return_status = 'S' THEN
               --do your thing
          ELSE
               --do your thing
         END IF;
       
          COMMIT;

     END LOOP;

EXCEPTION

WHEN OTHERS THEN
     dbms_output.put_line(' Exception closing PO using PO_ACTIONS.CLOSE_PO'||SQLERRM);

END;