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;
