How To Create Ap Invoice In Oracle Apps R12
Introduction:
In this guide we'll explain how to create AP invoice in oracle apps r12 using Open interface tables.
Oracle AP open interface tables are using to create new AP invoices whether from supplier invoices or imported from external systems.
AP Interface Tables In Oracle APPS R12 :
There are two interface tables in AP:
- AP_INVOICES_INTERFACE : Stores header information about invoices that you create or load for import.
- AP_INVOICE_LINES_INTERFACE : Stores lines & distributions information about invoices. Use it in conjunction with AP_INVOICE_INTERFACE table.
Note: during the payable import process, one row in this table may create more than one invoice distribution lines. For example, enter a Tax line in this table and prorate it across multiple Item lines during Open Interface Import. The system may create multiple Tax invoice distributions based on the single Tax line in this table.
Payables Open Interface Error Tables In Oracle APPS R12 :
There are two error interface tables in AP:
- AP_INTERFACE_REJECTIONS : stores information about invoice data from the AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables which could not be processed by Payables Open Interface Import.
Note : can purge data in this table by using the Payables Open Interface Purge. - AP_INTERFACE_CONTROLS : is a temporary table that holds control information about segregated data in the AP_INVOICES_INTERFACE table during the Payables Open Interface Import. The table ensures that each import must be unique with respect to the combination of SOURCE and GROUP_ID. This allows the submission of multiple imports at the same time.
Note : Oracle Payables application deletes the information from this table when you complete an import.
Delete / Purge Records From Payable Interface Tables :
Use the Payables Open Interface Purge Program to purges records from the Payables Open Interface tables (AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE). One parameter determines how Oracle deletes the data from Payable, open interface tables, which is called (Purge All).
Select Yes If we want Oracle to delete all the records with the status 'PROCESSED'
Select No If we want Oracle to delete only records that have been successfully imported.
Goal:
Create new AP invoice using interface tables
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
Steps:
- Populate required information's into Payable invoice header which is (AP_INVOICES_INTERFACE).
- Populate required information's into payable invoice lines which is (AP_INVOICE_LINES_INTERFACE)
- Run Payable Open interface Import program to import invoices from AP interface to AP base tables.
Note : for more information's about all required and optional values to populate both table you can refer to this link from oracle click here.
Forms Or Pages:
Example:
Required Columns:
- AP_INVOICES_INTERFACE
INVOICE_ID (Required) : Unique identifier for this invoice within this batch. Same value should be populated in invoice's lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.
INVOICE_NUM (Required) : Enter the invoice number that needs to be assigned to the invoice created in Payables from this record.
INVOICE_TYPE_LOOKUP_CODE (Optional) : Type of invoice: Credit or Standard.
INVOICE DATE (Optional) : Date of the invoice. If you do not enter a value, the system uses the date you submit Payables Open Interface Import as the invoice date.
PO_NUMBER (Optional) : Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if invoice to be matched with an purchase order.
VENDOR_ID & VENDOR_SITE_ID (Required) : VENDOR_ID is unique identifier for a supplier and VENDOR_SITE_ID is Internal supplier site identifier. Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER.
VENDOR_NUM & VENDOR_NAME (Optional) : Supplier number and name. You must identify the supplier by entering a value for one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER.
INVOICE_AMOUNT (Required) : Amount of the invoice.
INVOICE_CURRENCY_CODE (Optional) : Currency code for the invoice. If you want to create foreign currency invoices, enter a currency code that is different from your functional currency.
EXCHANGE_RATE (Optional) : This column is required if you enter a foreign currency code in the INVOICE_CURRENCY_CODE column and you enter User as the EXCHANGE_RATE_TYPE.
TERMS_ID (Optional) : Internal identifier for the payment terms.
DESCRIPTION (Optional) : Enter the description that you want to assign to the invoice created from this record.
SOURCE (Required) : Source of the invoice data. If you import EDI invoices from the Oracle EDI Gateway, the source is EDI Gateway. For invoices you import using SQL*Loader, use a QuickCode with the type Source that you have defined in the QuickCodes window in Payables.
- AP_INVOICE_LINES_INTERFACE
INVOICE_ID (Required) :Enter the INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE table.
INVOICE_LINE_ID : This value is not required. You can enter a unique number for each invoice line of an invoice.
LINE_NUMBER (Optional) : You can enter a unique number to identify the line.
LINE_TYPE_LOOKUP_CODE (Required) : Enter the lookup code for the type of invoice distribution that you want Payables Open Interface Import to create from this record. The code you enter must be ITEM, TAX, MISCELLANEOUS, or FREIGHT. These lookup codes are stored in the AP_LOOKUP_CODES table.
AMOUNT (Required) : The invoice distribution amount. If you are matching to a purchase order, the AMOUNT = QUANTITY_INVOICED x UNIT PRICE. If the total amount of all the invoice distributions does not equal the amount of the invoice that has the same INVOICE_ID, then Payables Open Interface Import will reject the invoice.
AP Open Interface populate example:
DECLARE l_po_number po_headers_all.segment1%TYPE; l_payment_method_code ap_invoices_all.payment_method_code%TYPE := :payment_method_code; l_org_id NUMBER := :p_org_id; l_vendor_id po_headers_all.vendor_id%TYPE; l_vendor_site_id po_headers_all.vendor_site_id%TYPE; l_invoice_id ap_invoices_all.invoice_id%TYPE; v_request_id NUMBER; l_boolean BOOLEAN; l_phase VARCHAR2 (200); l_status VARCHAR2 (200); l_dev_phase VARCHAR2 (200); l_dev_status VARCHAR2 (200); l_message VARCHAR2 (200); g_invalid_ex EXCEPTION; l_error_seq VARCHAR2 (200); l_error_msg VARCHAR2 (4000); BEGIN l_po_number := :p_po_number; l_payment_method_code := :payment_method_code; l_org_id := :p_org_id; l_invoice_id := ap_invoices_interface_s.nextval; BEGIN SELECT poh.vendor_id, poh.vendor_site_id INTO l_vendor_id, l_vendor_site_id FROM po_headers_all poh, po_lines_all pol WHERE poh.segment1 = l_po_number AND poh.po_header_id = pol.po_header_id; EXCEPTION WHEN OTHERS THEN l_error_seq := '01'; l_error_msg := sqlerrm; RAISE g_invalid_ex; END; BEGIN INSERT INTO ap_invoices_interface (invoice_id ,invoice_num ,vendor_id ,vendor_site_id ,invoice_amount ,invoice_currency_code ,invoice_date ,description ,source ,org_id ,payment_method_code) VALUES (l_invoice_id ,'ORAASK-' || l_invoice_id ,l_vendor_id ,l_vendor_site_id ,25000 ,'USD' ,to_date ('05-MAY-2012', 'dd-mon-yyyy') ,'This Invoice is created by ORAASK.COM' ,'MANUAL INVOICE ENTRY' ,l_org_id ,l_payment_method_code); END; BEGIN INSERT INTO ap_invoice_lines_interface (invoice_id ,line_number ,line_type_lookup_code ,amount) VALUES (l_invoice_id ,1 ,'ITEM' ,25000); END; COMMIT; BEGIN mo_global.init ('SQLAP'); fnd_global.apps_initialize (user_id => 1318, resp_id => 50554, resp_appl_id => 200); -- fnd_request.set_org_id (204); -- mo_global.set_policy_context ('S', 204); v_request_id := fnd_request.submit_request (application => 'SQLAP' ,program => 'APXIIMPT' ,description => '' ,start_time => NULL ,sub_request => FALSE ,argument1 => 204 ,argument2 => 'MANUAL INVOICE ENTRY' ,argument3 => NULL ,argument4 => NULL ,argument5 => NULL ,argument6 => NULL ,argument7 => NULL ,argument8 => 'N' ,argument9 => 'Y'); COMMIT; IF v_request_id > 0 THEN l_boolean := fnd_concurrent.wait_for_request (v_request_id ,20 ,0 ,l_phase ,l_status ,l_dev_phase ,l_dev_status ,l_message); END IF; dbms_output.put_line ('********************************'); IF (l_status = 'Normal') THEN dbms_output.put_line ('Invoice Created Successfully, Please see the output of Payables OPEN Invoice Import program request id :' || v_request_id); ELSE l_error_seq := '02'; l_error_msg := 'Payable Open Ivoice Pogram failed you can see the log from the application for the following reqiest id :' || v_request_id; RAISE g_invalid_ex; END IF; END; dbms_output.put_line ('********************************'); EXCEPTION WHEN g_invalid_ex THEN dbms_output.put_line ('l_error_seq = ' || l_error_seq); dbms_output.put_line ('l_error_msg = ' || l_error_msg); WHEN OTHERS THEN dbms_output.put_line ('Error :' || sqlerrm); END; Final Result:
Payables Open Interface Import :
Payable Invoice Screen :
Warning : Please Don't use this API's (AP_AI_TABLE_HANDLER_PKG.INSERT_ROW & AP_AIL_TABLE_HANDLER_PKG.INSERT_ROW) to create AP invoice because they are private API's not public.
In this article, we have demonstrate how to Create AP Invoice using AP open interface .
Hopefully, it was clear and concise.
Please don't use this code directly on production environment, instead test it on test environment first to make sure that API working correctly as per your requirements.
We are only giving one example of how to allocate one line as constant value.
Related posts:
How To Create Ap Invoice In Oracle Apps R12
Source: https://www.oraask.com/2021/01/create-ap-invoice-using-ap-open-interface-in-oracle-apps-r12.html
Posted by: tanneronsch1951.blogspot.com
0 Response to "How To Create Ap Invoice In Oracle Apps R12"
Post a Comment