Friday, March 5, 2021

Script to Create AP Invoice with Tax lines in Oracle Apps R12 (AP_INVOICES_INTERFACE, AP_INVOICE_LINES_INTERFACE)

 

 

 

 

 

 

In this post, we have given a sample script to insert data into payables open invoice import tables and submit payables open invoice import program.

Interface Tables:

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

Script:

SET SERVEROUTPUT ON; DECLARE ln_strd_request_id NUMBER; lv_group_id NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSDATE,'HHMISS')); ln_line_group_num NUMBER; lv_source VARCHAR2(200) DEFAULT 'INVOICE IMPORT'; ln_org_id NUMBER DEFAULT 111; lv_tax_rate_code VARCHAR2(1000); lv_tax_regime_code VARCHAR2(1000); lv_tax VARCHAR2(1000); lv_tax_status_code VARCHAR2(1000); lv_tax_jurisdiction_code VARCHAR2(1000); lv_tax_rate VARCHAR2(1000); PROCEDURE submit_payables_import(pin_org_id IN NUMBER, piv_source IN VARCHAR2, piv_group_id IN VARCHAR2, pon_request_id OUT NUMBER ) IS lv_request_id NUMBER; lv_result BOOLEAN; lv_phase1 VARCHAR2(100); lv_status1 VARCHAR2(100); lv_dev_phase1 VARCHAR2(100); lv_dev_status1 VARCHAR2(100); lv_message1 VARCHAR2(100); position_ NUMBER; BEGIN position_ := 500; -- Submit Payables Invoice Import program: lv_request_id := fnd_request.submit_request (application => 'SQLAP', program => 'APXIIMPT', description => NULL, start_time => NULL, sub_request => NULL, argument1 => pin_org_id, --Operating Unit argument2 => piv_source, --Source argument3 => piv_group_id, --Group argument4 => piv_group_id, --Invoice Batch Name argument5 => NULL, --Hold Name argument6 => NULL, --Hold Reason argument7 => NULL, --GL Date argument8 => 'N', --Purge argument9 => 'N', --Trace Switch argument10 => 'N', --Debug Switch argument11 => 'N', --Summarize Report argument12 => 1000, --Commit Batch Size argument13 => fnd_profile.VALUE('USER_ID'), --User ID argument14 => fnd_profile.VALUE('LOGIN_ID') --Login ID ); COMMIT; position_ := 510; IF lv_request_id = 0 THEN dbms_output.put_line(' Failed to submit Process APXIIMPT.'); ELSE lv_result := fnd_concurrent.wait_for_request(lv_request_id, 1, 0, lv_phase1, lv_status1, lv_dev_phase1, lv_dev_status1, lv_message1); END IF; position_ := 520; IF NOT lv_result THEN dbms_output.put_line('No Status for the request Id: ' ||lv_request_id); ELSE dbms_output.put_line('The Req-Id of APXIIMPT Process is ' || lv_request_id); END IF; pon_request_id := lv_request_id; EXCEPTION WHEN OTHERS THEN pon_request_id := NVL(lv_request_id, 0); dbms_output.put_line('Others exception. Error' ||SQLERRM); END submit_payables_import; BEGIN dbms_output.put_line('Group id:'||lv_group_id); SELECT tax_rate_code, tax_regime_code, tax, tax_status_code, tax_jurisdiction_code, percentage_rate INTO lv_tax_rate_code, lv_tax_regime_code, lv_tax, lv_tax_status_code, lv_tax_jurisdiction_code, lv_tax_rate FROM xxif_ou_country_vat_v WHERE tax_rate_code = '25%' AND organization_id = ln_org_id; INSERT INTO ap_invoices_interface ( invoice_id, invoice_num, invoice_currency_code, invoice_type_lookup_code, exchange_rate_type, exchange_date, invoice_date, invoice_received_date, vendor_id, vendor_site_id, invoice_amount, source, org_id, group_id, --batch_id, taxation_country, calc_tax_during_import_flag ) VALUES ( AP_INVOICES_INTERFACE_S.NEXTVAL ,'4200094338' ,'USD' ,'STANDARD' ,'Corporate' ,SYSDATE ,SYSDATE --<invoice date> ,SYSDATE ,8656 --<vendor_id validated against po_vendors.vendor_id>, ,3663 --<vendor_site_id validated against po_vendor_sites.vendor_site_id>, ,1450.84 --<invoice_amount> ,lv_source -- It can be retrived by following query --(select lookup_code --from ap_lookup_codes --where lookup_type='SOURCE') ,ln_org_id ,lv_group_id -- ,lv_group_id ,'SE' ,'N' ); INSERT INTO ap_invoice_lines_interface ( invoice_id, invoice_line_id, line_number, line_type_lookup_code, amount, accounting_date, line_group_number, amount_includes_tax_flag, dist_code_combination_id ) VALUES ( AP_INVOICES_INTERFACE_S.CURRVAL ,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL ,1 --<some unique_number>, ,'ITEM' --<ITEM>/<FREIGHT>/<TAX> ,1160.67 -- sum of line_amount should always be equal to invoice_amount, ,SYSDATE ,AP_INVOICE_LINES_INTERFACE_S.CURRVAL ,'N' ,'144825' --'<account code>' ); ln_line_group_num := AP_INVOICE_LINES_INTERFACE_S.CURRVAL; INSERT INTO ap_invoice_lines_interface ( invoice_id, invoice_line_id, line_number, line_type_lookup_code, amount, accounting_date, line_group_number, dist_code_combination_id, --tax_rate_id, tax_rate_code, tax_regime_code, tax, tax_status_code, tax_jurisdiction_code, tax_rate ) VALUES ( AP_INVOICES_INTERFACE_S.CURRVAL ,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL ,2 --<some unique_number>, ,'TAX' --<ITEM>/<FREIGHT>/<TAX> ,290.17 -- sum of line_amount should always be equal to invoice_amount, ,SYSDATE ,ln_line_group_num ,NULL --'<account code>' -- ,25 --> Tax_RATE_ID ,lv_tax_rate_code, lv_tax_regime_code, lv_tax, lv_tax_status_code, lv_tax_jurisdiction_code, lv_tax_rate ); COMMIT; fnd_global.apps_initialize(user_id => 4484, resp_id => 90833, -- 'AP Admin' resp_appl_id => 200 ); mo_global.init('SQLAP'); fnd_client_info.set_org_context(ln_org_id); mo_global.set_policy_context ('S', ln_org_id); submit_payables_import(pin_org_id => ln_org_id, piv_source => lv_source, piv_group_id => lv_group_id, pon_request_id => ln_strd_request_id ); DBMS_OUTPUT.PUT_LINE('ln_strd_request_id :'||ln_strd_request_id); END;

 

1 Responses to “Script to Create AP Invoice with Tax lines in Oracle Apps R12 (AP_INVOICES_INTERFACE, AP_INVOICE_LINES_INTERFACE)”

tike mik said...
August 17, 2021 at 1:10 AM

With this mentorship program, you will not get forced into selling their products and services. What I liked most of all is that the strategies they teach they even encourage you to focus on your interests and passions and start your business in those spaces through their mentoring. free online invoice generator


Post a Comment

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.