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
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