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;

Gather Schema Stats / Fix Locks / Performance issues

Below commands might help to resolve the performance issues or Locks in Oracle Apps.
Be cautious while running below steps. 

Follow step wise. You need to have apps privilege to database.

1. select distinct owner, table_name, stattype_locked  from dba_tab_statistics where stattype_locked is not null; (Find all locks)
2. exec dbms_stats.unlock_schema_stats('SYS') ; 
3. exec dbms_stats.unlock_table_stats('SYS','WRH$_SYSSTAT');
4. exec dbms_stats.gather_table_stats(ownname => 'PO', tabname => 'PO_SESSION_GT', cascade=> true, degree=> 8); (Execute Gather schema stats on Table if needed or next step to execute on whole schema)
5. exec dbms_stats.gather_schema_stats(ownname=>'PO', estimate_percent=>40)

PO Workflow Debug

The following is exceptionally useful when trying to diagnose problem encountered during workflow execution.This can be exceptionally useful in determining why a section of code is failing.

Step 1) clearing any existing debug entries. truncate table po_wf_debug

Step2) Set Profile Option ‘PO: Set Debug Workflow ON’ to Yes, then reproduce the issue

Step 3) SELECT document_id, document_number,authorization_status,debug_message FROM po_wf_debug ; 

optionally add itemtype and itemkey to limit the data returned.