Single Insert Script for AP Invoices in 11.5.10 Oracle Applications

Single Insertion scripts for AP Invoices

This Document provides the guidance to the user with the necessary information for creating invoices through Single Insert in accurate way.

The Interface table used here is:

AP_INVOICES_INTERFACE.

AP_INVOICE_DISTRIBUTIONS_INTERFACE

 Insertion steps

Following are the steps that must be followed, in order, to perform the Single insertions for Invoices:

Execution Step	Description
Step 1	select * from AP_INVOICES_ALL where invoice_num='XX_INV_102’; --No rows selected. Hence we can create an Item with ‘XX_INV_002’
Step 2	select DISTINCT master_organization_id from MTL_PARAMETERS; --select one record from the above query For Example choose MASTER_ORGANIZATION_ID=204
Step 3	INSERT INTO ap_invoices_interface (invoice_id, invoice_num, invoice_amount, invoice_date, invoice_type_lookup_code, payment_method_lookup_code, gl_date, vendor_id, vendor_site_id, source, invoice_currency_code, payment_currency_code, terms_id, org_id, terms_date, creation_date, created_by, last_update_date, last_updated_by ) VALUES (ap_invoices_interface_s.NEXTVAL, 'XX_INV_007', 2000, SYSDATE, 'STANDARD', 'CHECK', SYSDATE, 29925, 5064, 'External', 'USD', 'USD', 10003, 204, SYSDATE, SYSDATE, 1008480, SYSDATE, 1008480 );
Step 4	INSERT INTO ap_invoice_lines_interface (invoice_id, invoice_line_id, dist_code_combination_id, line_number, line_type_lookup_code, amount, accounting_date, creation_date, created_by, last_update_date, last_updated_by ) VALUES (ap_invoices_interface_s.CURRVAL, ap_invoice_lines_interface_s.NEXTVAL, 17347, 1, 'ITEM', 2000, SYSDATE, SYSDATE, 1008480, SYSDATE, 1008480 );
Step 5	COMMIT;
Step 6	Select * from ap_invoices_interface where trunc(creation_date)=trunc (sysdate);
Step 7	Select * from ap_invoice_lines_interface where trunc(creation_date) =trunc(sysdate);
Step 8	Then Login to Applications and switch to Payables, Vision Operations (USA) Responsibility: Payables, Vision Operations (USA) Navigation: Others -->   Requests -->  Run Run Payables Open Import Program Parameters: Source → External Group → Null Batch Name → N/A Hold Name → Null Hold Reason → Null GL Date → Null Purge → No Summarize Report → No Then submit the request. After successful completion of the request please follow the next step.
Step 9	Select * from ap_invoices_all where invoice_num like ‘XX_INV_102’
Step 10	Select * from ap_invoices_distributions_all where invoice_id=64210

Pre-Requisitions

Execution Step	Description
Step 1	Organization should already exist.
Step 2	Template should already exist.
Step 3	Item to be created should not exist before.
Step 4	Accounting period must be in Open status.

Code Attachments

single_insert_invoices.sql

The above sql file has the code to perform single insert into Interface tables for Invoices.

INSERT INTO ap_invoices_interface

(invoice_id, invoice_num, invoice_amount, invoice_date,

invoice_type_lookup_code, payment_method_lookup_code, gl_date,

vendor_id, vendor_site_id, source, invoice_currency_code,

payment_currency_code, terms_id, org_id, terms_date,

creation_date, created_by, last_update_date, last_updated_by

)

VALUES (ap_invoices_interface_s.NEXTVAL, 'XX_INV_007', 2000, SYSDATE,

'STANDARD', 'CHECK', SYSDATE,

29925, 5064, 'External', 'USD',

'USD', 10003, 204, SYSDATE,

SYSDATE, 1008480, SYSDATE, 1008480

);

INSERT INTO ap_invoice_lines_interface

(invoice_id,

invoice_line_id, dist_code_combination_id, line_number,

line_type_lookup_code, amount, accounting_date, creation_date,

created_by, last_update_date, last_updated_by

)

VALUES (ap_invoices_interface_s.CURRVAL,

ap_invoice_lines_interface_s.NEXTVAL, 17347, 1,

'ITEM', 2000, SYSDATE, SYSDATE,

1008480, SYSDATE, 1008480

);

COMMIT;