Friday, October 22, 2010

API to find AP INVOICE Status (AP_INVOICES_PKG.GET_APPROVAL_STATUS)


When I tried to find the AP invoice status from backend, I found the column name APPROVAL_STATUS_LOOKUP_CODE under the view name AP_INVOICE_V. Since it is org based view, setting the context is mandatory. Please find my script to set the context here.

Below query will give you the status,

SELECT invoice_num
      ,approval_status_lookup_code
FROM   ap_invoices_v
WHERE  invoice_num = 'DM080310';

When I further traced to understand the source of the view column, I found that there is no column in the AP_INVOICES_ALL table that stores the validation status. An API named AP_INVOICES_PKG.GET_APPROVAL_STATUS is used by the view to finding the status.

Below query will give you the usage of the mentioned api,


SELECT APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS
            (
             I.INVOICE_ID
            ,I.INVOICE_AMOUNT
            ,I.PAYMENT_STATUS_FLAG
            ,I.INVOICE_TYPE_LOOKUP_CODE
            ) Approval_Status
      ,invoice_num
FROM   AP_INVOICES I
WHERE  invoice_num = 'DM080310';
    


When I went one more step deeper, I got the below,

Invoice distributions are validated individually and the status is stored at the invoice distribution level.  This status is stored in AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG.
Valid values for the column are:

A - Validated (it used to be called Approved)
N or NULL - Never validated
T - Tested but not validated

The invoice header form derives the invoice validation status based on the following:
'Validated'
-          If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'
'Never Validated'
-          If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'
'Needs Revalidation'
-          If there are any rows in AP_HOLDS that do not have a release code.
-          If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T'.
-          If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed).
I hope this information helps you.

Friday, October 22, 2010 by Team search · 16

FNDLOAD for CONCURRENT PROGRAMS


<<-- Basics of FNDLOAD -->

Let us understand the basics before I reveal the command. FNDLOAD is nothing but a concurrent program. The concurrent executable takes the following parameters

FNDLOAD apps/pwd
                   0 Y
                   mode
                   configfile
                   datafile
                   entity
                   [ param ...]

Here let us see the importance of each parameter one by one.. 
Ø      apps/pwd - The APPS schema and password in the form username/password[@connect_string]. If connect_string is omitted, it is taken in a platform-specific manner from the environment
using the name TWO_TASK. 
Ø      0 Y - Concurrent program flags. 
Ø      mode - UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the database. DOWNLOAD causes the loader to fetch rows and write them to the datafile. 
Ø      configfile - The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader).
Ø      datafile - The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten.
Ø      entity - The entity(ies) to upload or download. When uploading, you should always upload all entities, so specify a "-" to upload all entities. 
Ø      <[param] - Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.

<-- Shell Script to DOWNLOAD ldt file for the specific concurrent program -->

echo "Enter Concurrent Program Short Name : \c"
read CONC_SHT_NAME
echo "Enter Concurrent Program Application Short Name : \c"
read APPL_CONC_SHT_NAME
stty -echo
echo "Enter APPS Password : \c"
read APPS_PASSWORD

stty echo

FNDLOAD 'APPS'/$APPS_PASSWORD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct "$CONC_SHT_NAME.ldt" PROGRAM APPLICATION_SHORT_NAME=$APPL_CONC_SHT_NAME CONCURRENT_PROGRAM_NAME=$CONC_SHT_NAME

<-- Shell Script to UPLOAD ldt file for the specific concurrent program -->

echo "Enter APPS User ID : \c"
read APPS_USER_ID
stty -echo
echo "Enter APPS Password : \c"
read APPS_PASSWORD
stty echo
echo "Enter ldt File name with path : \c"
read XXFILENAME

FNDLOAD $APPS_USER_ID/$APPS_PASSWORD 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct $XXFILENAME

by Team search · 0

Data Flow between Receiving Transactions (RCV) – Sub ledger Accounting (XLA) – General Ledger (GL)

When I tried to understand the flow of data between PO transactions to GL, I found the below and sharing the same. All the queries used below were tested in R12.1.1 instance. 

Collecting the Receiving data related to PO

SELECT rrsl.rcv_sub_ledger_id -- Connects to SLA tables
      ,rrsl.rcv_transaction_id
      ,rrsl.je_source_name
      ,rrsl.code_combination_id
      ,rrsl.accounted_dr
      ,rrsl.accounted_cr
      ,rrsl.reference1
      ,rrsl.reference2   PO_HEADER_ID
      ,rrsl.reference3   PO_DISTRIBUTION_ID
      ,rt.transaction_id
      ,rt.transaction_type
      ,rt.po_header_id
      ,rt.po_release_id
      ,rt.po_line_id
      ,rt.po_line_location_id
      ,pd.code_combination_id
FROM   rcv_receiving_sub_ledger rrsl
      ,rcv_transactions rt
      ,po_distributions_all pd
WHERE pd.po_distribution_id = rt.PO_DISTRIBUTION_ID
AND   rt.transaction_id = rrsl.rcv_transaction_id
AND   rt.PO_DISTRIBUTION_ID = rrsl.reference3
AND   rt.po_header_id = 500;


Collecting the data from XLA
In R12, we have XLA tables that holds link between any sub ledger data to General ledger data.
The below query gives you the important columns available in the XLA tables and GL linking columns for the above RCV_SUB_LEDGER_ID

SELECT xal.gl_sl_link_id     -- Connects to GL tables
      ,xal.gl_sl_link_table  -- Connects to GL tables
      ,xdl.source_dIstribution_id_num_1 rcv_sub_ledger_id
      ,xdl.line_definition_code
      ,xdl.event_class_code
      ,xdl.event_type_code    
      ,xah.ae_header_id
      ,xah.event_id
      ,xah.entity_id
      ,xah.event_type_code
      ,xah.description
      ,xal.ae_line_num
      ,xal.code_combination_id
      ,xal.accounting_class_code     
FROM   xla_events               xev
      ,xla_ae_headers           xah
      ,xla_ae_lines             xal
      ,XLA_DISTRIBUTION_LINKS   xdl
WHERE xah.application_id    = xev.application_id
AND   xev.event_id          = xah.event_id
AND xal.application_id    = xah.application_id
AND xal.ae_header_id      = xah.ae_header_id
AND xev.application_id    = xdl.application_id
AND xal.ae_line_num       = xdl.ae_line_num
AND xah.ae_header_id      = xdl.ae_header_id
AND xdl.SOURCE_DISTRIBUTION_TYPE ='RCV_RECEIVING_SUB_LEDGER'
AND xdl.source_dIstribution_id_num_1   = 3002;



Collecting the data from GL
Below query collects the data from GL.
In the below query, you can notice the linking columns in the first five places and others were just important ones.

SELECT glimp.reference_5 ENTITY_ID
      ,glimp.reference_6 EVENT_ID
      ,glimp.reference_7 AE_HEADER_ID
      ,glimp.gl_sl_link_id
      ,glimp.gl_sl_link_table
      ,glb.je_batch_id
      ,glb.name
      ,glb.status
      ,glb.description
      ,glh.je_header_id
      ,glh.je_category
      ,glh.je_source
      ,glh.name
      ,glh.description
      ,glh.running_total_accounted_cr
      ,glh.running_total_accounted_dr
      ,gll.je_line_num
      ,gll.code_combination_id
FROM   gl_import_references glimp
      ,gl_je_batches glb
      ,gl_je_headers glh
      ,gl_je_lines   gll
WHERE  glimp.je_header_id     = glh.je_header_id
AND    glimp.je_line_num      = gll.je_line_num
AND    glimp.je_batch_id      = glb.je_batch_id
AND    glh.je_header_id       = gll.je_header_id
AND    glh.je_batch_id        = glb.je_batch_id
AND    glimp.gl_sl_link_id    = 245133         
AND    glimp.gl_sl_link_table = 'XLAJEL';

by Team search · 0

Tuesday, October 19, 2010

FND_REQUEST.SUBMIT_REQUEST in R12

FND_REQUEST.SUBMIT_REQUEST is an API used to submit a concurrent program in ORACLE Applications.

Parameters:

Application : Short name of the application associated with the concurrent request to be submitted. 
Program - Short name of the concurrent program (not the executable) for which the request should be submitted. 
Description - Description of the request that is displayed in the Concurrent Requests form (Optional.) 
Start_time - Time at which the request should start running, formatted as HH24:MI or HH24:MI:SS (Optional.) 
Sub_request - Set to TRUE if the request is submitted from another request and should be treated as a sub-request. 
Argument1...100 - Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle Forms, you must specify all 100 arguments.

Example: Submission of Standard Order Import Program


SET SERVEROUTPUT ON;
DECLARE
     v_request_id                        NUMBER           DEFAULT 0;
    
    --Order Import Parameters
    p_operating_unit                     VARCHAR2(20)    := NULL;
    p_order_source                       VARCHAR2(20)    := 'XYZ';
    p_orig_sys_document_ref              VARCHAR2(20)    := NULL;
    p_operation_code                     VARCHAR2(20)    := NULL;
    p_validate_only                      VARCHAR2(20)    := 'N';
    p_debug_level                        VARCHAR2(20)    := '1';
    p_num_instances                      VARCHAR2(20)    := '4';
    p_sold_to_org_id                     VARCHAR2(20)    := NULL;
    p_sold_to_org                        VARCHAR2(20)    := NULL;
    p_change_sequence                    VARCHAR2(20)    := NULL;
    p_perf_param                         VARCHAR2(20)    := 'Y';
    p_rtrim_data                         VARCHAR2(20)    := 'N';
    p_pro_ord_with_null_flag             VARCHAR2(20)    := 'Y';
    p_default_org_id                     VARCHAR2(20)    := '83';
    p_validate_desc_flex                 VARCHAR2(20)    := 'N';

    -- End of Parameters -----

    v_context varchar2(100);


    FUNCTION set_context( i_user_name    IN  VARCHAR2
                         ,i_resp_name    IN  VARCHAR2
                         ,i_org_id       IN  NUMBER)
    RETURN VARCHAR2
    IS
        /* Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here */
    END set_context;


BEGIN
      -- Setting the context ----
      v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
      IF v_context = 'F'
      THEN
        DBMS_OUTPUT.PUT_LINE('Error while setting the context');       
      END IF;

      DBMS_OUTPUT.PUT_LINE('Submit Order Import Concurrent Program');

      v_request_id:=  FND_REQUEST.SUBMIT_REQUEST (
               application  =>  'ONT'
              ,program      =>  'OEOIMP'
              ,description  =>  'Order Import'
              ,start_time   =>  SYSDATE
              ,sub_request  =>  NULL
              ,argument1    =>  p_operating_unit
              ,argument2    =>  p_order_source
              ,argument3    =>  p_orig_sys_document_ref
              ,argument4    =>  p_operation_code
              ,argument5    =>  p_validate_only
              ,argument6    =>  p_debug_level
              ,argument7    =>  p_num_instances
              ,argument8    =>  p_sold_to_org_id
              ,argument9    =>  p_sold_to_org
              ,argument10   =>  p_change_sequence
              ,argument11   =>  p_perf_param
              ,argument12   =>  p_rtrim_data
              ,argument13   =>  p_pro_ord_with_null_flag
              ,argument14   =>  p_default_org_id
              ,argument15   =>  p_validate_desc_flex
             );

       COMMIT;

       DBMS_OUTPUT.PUT_LINE('Request_id: '||v_request_id);

EXCEPTION WHEN OTHERS THEN        
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' Error :'||SQLERRM);
END;

Tuesday, October 19, 2010 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.