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.

FNDLOAD Dowload and Upload objects

FNDLOAD is used to migrate objects between instances.


DOWNLOAD



1 – Printer Styles
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"

2 – Lookups
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND"
LOOKUP_TYPE="lookup name"

3 – Descriptive Flexfield with all of specific Contexts
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=COL_ALL:REF_ALL:CTX_ONE:SEG_ALL APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"

4 – Key Flexfield Structures
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL APPLICATION_SHORT_NAME="FND" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"

5 – Concurrent Programs
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="FND" CONCURRENT_PROGRAM_NAME="concurrent name"

6 – Value Sets
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name"

7 – Value Sets with values
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"

8 – Profile Options
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="FND"

8 – Request Groups
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="FND"

10 – Request Sets
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME="request set"

11 – Responsibilities
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"

12 – Menus
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"

13 – Forms Personalization
FNDLOAD apps/$APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES function_name=FUNCTION_NAME

14 - XML Data Definition and Associated Template Definition ***Doesn't download attachments.
FNDLOAD apps/$APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct file_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='FND' DATA_SOURCE_CODE='DATA SOURCE CODE' TMPL_APP_SHORT_NAME='FND' TEMPLATE_CODE='TEMPLATE_CODE'

15 - Workflow Download

WFLOAD apps/$APPS_PWD 0 Y DOWNLOAD file_name.ldt WF_INTERNAL_NAME



UPLOAD

UPLOAD command is same for all except replacing the .lct and passing any extra parameters if you want to
pass

FNDLOAD apps/$APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/corresponding.lct upload_file.ldt

Use the condition below to override the existing.

FNDLOAD apps/$APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/corresponding.lct upload_file.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Oracle compare dates with timestamp

Dates with timestamp can be compared by using

to_date(DATE_FIELD_VAL, 'DD/MM/YY HH24:MI:SS')

Date from : '07/11/13 01:01:01'
Date To :  '07/11/13 23:59:59'

Note that it's 24Hr format.


to_date(DATE_FIELD_VAL, 'DD/MM/YY HH24:MI:SS') between to_date('07/11/13 01:01:01','DD/MM/YY HH24:MI:SS') and to_date('07/11/13 23:59:59','DD/MM/YY HH24:MI:SS')

Leading zero's XML Publisher

When chosen Excel as XML publisher output, text with prefix zeros will be converted to number in excel output. To maintain the format

Option1
Change xml tag  in template from
<?your_field?> to ="<?your_field?>"


Option2
Add NONBREAKING SPACE before the column.

Place cursor in front of the column you need press CTRL + SHIFT + SPACE. You can also choose this from menu.

Insert -  Symbol - Special Characters.