Thursday, August 21, 2014

Oracle AP Invoice Interface

How to use Payables Open Interface Import program (APXIIMPT) to create invoices using AP Invoice Interface tables


Oracle Applications 11i, R12

Here I will explain flow to create invoices using AP invoice Interface tables and launching Payables Open Interface Import program (APXIIMPT) using fnd_request.submit_request API-

Derive Supplier and site details-
SELECT asu.vendor_name, asu.vendor_id, asus.vendor_site_id
INTO   v_vendor_name, v_vendor_id, v_vendor_site_id
FROM ap_suppliers asu
    ,ap_supplier_sites_all asus
WHERE asu.vendor_name='XXX'
  AND asus.vendor_site_code = 'XXX'
  AND asus.org_id = (SELECT organization_id
                      FROM hr_operating_units
                     WHERE NAME = 'XXX')
  AND asu.vendor_id = asus.vendor_id;
        
DERIVE SET OF BOOKS AND CHART OF ACCOUNTS FOR THT OU

SELECT hou.organization_id, hou.set_of_books_id,gl.chart_of_accounts_id
INTO x_org_id, l_set_of_bks_id ,l_chart_of_acct_id
FROM hr_operating_units hou,gl_ledgers gl
WHERE UPPER (hou.NAME) = UPPER ('XXX')
AND hou.set_of_books_id = gl.ledger_id;
        
CHECK INVOICE IS ALREADY EXIST FOR THT SUPPLIER AND OU

SELECT COUNT (1)
INTO x_count
FROM ap_invoices_all aia
WHERE aia.invoice_num = p_invoice_number
AND aia.vendor_id = v_vendor_id
AND aia.vendor_site_id = v_vendor_site_id
AND aia.org_id = (SELECT organization_id
                    FROM hr_operating_units
                   WHERE NAME = 'XXX');
        
CHECK INVOICE AMOUNT IS EQUAL TO ITEM  AND TAX AMOUNT

CHECK CURRENCY CODE EXIST OR NOT

TAX CODE IS NOT NULL

PAYMENT TERM VALIDATION

SELECT term_id
INTO l_terms_id
FROM ap_terms
WHERE UPPER (NAME) = UPPER (p_term_name) AND enabled_flag = 'Y';
      
POPULATE CODE COMBINATION ID

STANDARD ORACLE API FOR VALIDATING CODE COMBINATIONS AGAINSTRULES CONFIGURED IN ORALCE GL
FND_FLEX_EXT.GET_COMBINATION_ID
SELECT code_combination_id, enabled_flag
INTO p_ccid, l_enabled_flag
FROM gl_code_combinations gcc,gl_sets_of_books gsb
WHERE gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
  AND segment1 = p_segment1
  AND segment2 = p_segment2
  AND segment3 = p_segment3
  AND segment4 = p_segment4
  AND segment5 = p_segment5
  AND segment6 = p_segment6
  AND set_of_books_id = p_set_of_book_id;
        
DERIVE SOURCE VALUE

SELECT flv.lookup_code
INTO v_source
FROM fnd_lookup_values flv
,fnd_lookup_types flt
,fnd_application_tl fat
WHERE flt.lookup_type = 'SOURCE'
AND UPPER (fat.application_name) = 'PAYABLES'
AND flt.application_id = fat.application_id
AND flt.lookup_type = flv.lookup_type
AND flv.enabled_flag = 'Y'
AND flv.meaning = 'XXX';


Insert data into interface tables-

c_apinv_main is cursor type variables of cursor created on invoice temp table
          
INSERT INTO ap_invoices_interface
                    (invoice_id
                    ,invoice_num
                    ,invoice_type_lookup_code
                    ,invoice_date
                    ,vendor_id
                    ,vendor_name
                    ,vendor_site_id
                    ,invoice_amount
                    ,invoice_currency_code
                    ,terms_id
                    ,terms_name
                    ,attribute_category
                    ,attribute1
                    ,attribute2
                    ,attribute3
                    ,attribute5
                    ,attribute7
                    ,SOURCE
                    ,org_id
                    ,creation_date
                    ,created_by
                    ,last_update_date
                    ,last_updated_by
                    ,last_update_login
                    ,payment_method_code
                    ,calc_tax_during_import_flag
                    ,description
                    ,accts_pay_code_combination_id
                    )
VALUES (ap_invoices_interface_s.NEXTVAL
                    ,c_apinv_main.invoice_num
                    ,'STANDARD'
                    ,c_apinv_main.invoice_date
                    ,v_vendor_id
                    ,v_vendor_name
                    ,v_vendor_site_id
                    ,c_apinv_main.invoice_amount
                    ,c_apinv_main.invoice_currency_code
                    ,l_terms_id
                    ,'Immediate'
                    ,'OTS01'
                    ,c_apinv_main.attribute1
                    ,c_apinv_main.attribute2
                    ,c_apinv_main.attribute3
                    ,c_apinv_main.attribute5
                    ,'0'
                    ,v_source
                    ,x_org_id
                    ,SYSDATE
                    ,l_user_id
                    ,SYSDATE
                    ,l_user_id
                    ,l_login_id
                    ,'JLP_Cheque'
                    ,'Y'
                    ,c_apinv_main.reason_description
                    ,NULL
                    );


INSERT INTO ap_invoice_lines_interface
                    (invoice_id
                    ,invoice_line_id
                    ,line_type_lookup_code
                    ,amount
                    ,accounting_date
                    ,dist_code_combination_id
                    ,org_id
                    ,tax_classification_code
                    ,creation_date
                    ,created_by
                    ,last_update_date
                    ,last_updated_by
                    ,last_update_login
                    )
VALUES (ap_invoices_interface_s.CURRVAL
                    ,ap_invoice_lines_interface_s.NEXTVAL
                    ,'ITEM'
                    ,c_apinv_main.line_amount
                    ,SYSDATE
                    ,c_apinv_main.ccid
                    ,x_org_id
                    ,c_apinv_main.tax_code
                    ,SYSDATE
                    ,l_user_id
                    ,SYSDATE
                    ,l_user_id
                    ,l_login_id
                    );
                   
After inserting into interface tables, launch Payables Open Interface Import program using fnd_request.submit_request


mo_global.set_policy_context ('S', l_org_id);
l_standard_request_id :=fnd_request.submit_request (application                  => 'SQLAP'
                                  ,program                      => 'APXIIMPT'
                                  ,description                  => NULL
                                  ,start_time                   => NULL
                                  ,sub_request                  => FALSE
                                 ,argument1                    => l_org_id
                                 ,argument2                    => v_source
                                 ,argument3                    => NULL                                                         
                                 ,argument4                    => NULL                                                         
                                 ,argument5                    => NULL                  
                                 ,argument6                    => NULL                                                           
                                 ,argument7                    => NULL
                                 ,argument8                    => 'Y'
                                 );

l_completed :=
      fnd_concurrent.wait_for_request (l_standard_request_id
                                      ,1
                                      ,NULL
                                      ,l_phase
                                      ,l_vstatus
                                      ,l_dev_phase
                                      ,l_dev_status
                                      ,l_message
                                      ); 
Check l_dev_phase and l_dev_status. it should be complete and normal

Base Table for invoices:

AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL

1 comment:

  1. thank you a lot, I had been searching information like this but i had not been luck, now I have the way of start this.

    ReplyDelete