Query : Invoices on hold

select distinct
ai.invoice_num,ai.invoice_amount,ai.invoice_date
,ai.invoice_received_date,ah.hold_lookup_code,ah.hold_reason,pv.vendor_name,pv.segment1 vendor_number
,ph.segment1 po_number
,gcc.segment1||’.’||gcc.segment2||’.’||gcc.segment3||’.’||gcc.segment4||’.’||gcc.segment5||’.’||gcc.segment6
–,aid.AMOUNT
from ap_invoices_all ai
,ap_holds_all ah
,po_vendors pv
,po_headers_all ph
,ap_invoice_distributions_all aid
,po_distributions_all pda
,gl_code_combinations gcc
where 1=1
and ai.invoice_id = ah.invoice_id
and ai.vendor_id = pv.vendor_id
–and ai.po_header_id = ph.po_header_id(+)
and ph.PO_HEADER_ID = pda.PO_HEADER_ID
and ah.invoice_id = aid.invoice_id
and aid.po_distribution_id = pda.po_distribution_id
and aid.dist_code_combination_id = gcc.code_combination_id

Payables Open Interface

AP Invoice Interface is concerned with the creation of payable invoices in the Oracle System. Standard Payable invoices are created in the system for the PO sent across to the supplier and invoice details received for the same.

Payable invoices can be created manually by entering from the Invoice screen but in business having recurring transactions opt for auto invoice creation. This mechanism involves populating the interface tables with the invoice table and run the payables import program to create the invoice.


Table Details:

AP Invoice Interface tables

1.AP_INVOICES_INTERFACE

2.AP_INVOICE_LINES_INTERFACE


AP Invoice Base Tables:

1. AP_INVOICES_ALL

2.AP_INVOICE_DISTRIBUTIONS_ALL

3. AP_PAYMENT_SCHEDULES_ALL


Error table:1.AP_INTERFACE_REJECTIONS2.AP_INTERFACE_CONTROLS


Users can use the Payables Open Interface Import program to create Payables invoices from invoice data in the Payables Open Interface Tables.  Payables Open Interface Tables need to be populated with invoice data from the following sources:

        Supplier EDI invoices (ASC X12 810/EDIFACT INVOIC) transferred through Oracle EDI Gateway

        Invoices from other accounting systems with a custom SQL*Loader program

        Credit card transactions you have transferred using the Credit Card Invoice Interface Summary

After the interface tables are populated users need to submit

    The Payables import program to create payables invoices.Enter the report parameters.  In the Source field, select the source name from the list of values. If your records have a Group and you want to import invoices for only that group, enter the Group. This will allow you to import smaller sets of records concurrently for the same source, which will improve your performance. If you use batch control, enter a Batch Name.

Debugging Interface Errors


After the interface program completes invoices will be created in the system. For the records failing the validations no invoices will be created and the records will be errored out in the interface.

Some of the most fatal errors are

    No supplier or supplier site

    The invoice number is a duplicate

If the invoice level information is correct, Payables will validate all values at the line level, and the rejections report will list all line level problems. If a distribution is rejected, the whole invoice is rejected.You can correct the data in one of the following ways:


    Use the Open Interface Invoices window to correct problems directly in the Payables Open Interface tables.

Invoice Validation


Once the Invoices have been created invoice validation needs to be done so that invoices can be processed further to make payments and transfer to GL. For this we need to either manually validate individual invoices from the Invoice Screen or run the Invoice Validation Program. This activity will validate the invoices and allow further processing, In case invoices fail in validation then the invoices will be put on hold . the user can see the hold details by pressing the "HOLDS" button on the invoice screen. at the backend hold details will be stored in the table : ap_invoice_holds

Get Invoice Number from PO Number

SELECT ap_inv.invoice_num,
po_hdr.segment1,
po_hdr.org_id
FROM ap_invoices_all ap_inv,
ap_invoice_distributions_all ap_dis,
po_distributions_all po_dis,
po_headers_all po_hdr
WHERE ap_inv.invoice_id = ap_dis.invoice_id
AND ap_dis.po_distribution_id = po_dis.po_distribution_id
AND po_dis.po_header_id = po_hdr.po_header_id
AND ap_inv.org_id = po_hdr.org_id
AND po_hdr.segment1 like ‘Give PO Number’
–AND ap_inv.invoice_num like ‘Give Invoice Number’

R12 Query to find supplier payment method

In R12 supplier payment method information no more exists in the ap_supplier_sites_all table.
Query to find the supplier payment method


SELECT   ieppm.payment_method_code
       FROM ap_supplier_sites_all assa,
            ap_suppliers sup,
            iby_external_payees_all iepa,
            iby_ext_party_pmt_mthds ieppm
      WHERE sup.vendor_id = assa.vendor_id
        AND assa.pay_site_flag = 'Y'
        AND assa.vendor_site_id = iepa.supplier_site_id
        AND iepa.ext_payee_id = ieppm.ext_pmt_party_id
        AND ((ieppm.inactive_date IS NULL) OR (ieppm.inactive_date > SYSDATE)
            )
        AND assa.vendor_site_id = :p_vendor_site_id
        AND ieppm.primary_flag = 'Y'
   ORDER BY sup.vendor_name, assa.vendor_site_code