AP:Query fetching both Prepayment Data and Invoice Data of a Payable Invoice

This Query will find a query which fetches both invoice and prepayment information of a payable invoice ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance.

SELECT pv.vendor_name "Vendor Name",

       ai.invoice_num "Invoice Num",

       ai.invoice_id "Invoice Id",

       ai.invoice_amount "Invoice amount",

       ail.line_number "Invoice Line Num",

       ai2.invoice_id "Prepay Invoice Id",

       ai2.invoice_num "Prepay Invoice Num",

       ail.prepay_line_number "Prepay Invoice Line Num",

       (-1)*(ail.amount - NVL(ail.included_tax_amount,0))

       "Prepay Amount Applied",

       NULLIF((-1)*(NVL(ail.total_rec_tax_amount, 0) +      

       NVL(ail.total_nrec_tax_amount, 0)), 0)

       "Tax amount Applied"

FROM   AP_INVOICES_ALL ai,

       AP_INVOICES_ALL ai2,

       AP_INVOICE_LINES_ALL ail,

       AP_SUPPLIERS pv

WHERE ai.invoice_id                  = ail.invoice_id

AND   ai2.invoice_id                   = ail.prepay_invoice_id

AND   ail.amount                       < 0

AND   NVL(ail.discarded_flag,'N')     <> 'Y'

AND   ail.line_type_lookup_code        = 'PREPAY'

AND   ai.vendor_id                     = pv.vendor_id

AND   ai.invoice_type_lookup_code NOT IN ('PREPAYMENT', 'CREDIT','DEBIT')

AND   ai.invoice_id                    = '166014';

Read More

Payable : Find Bank information

 SELECT cba.bank_account_name            "Bank Account Name",
       cba.bank_account_num             "Bank Account Number",
       cba.multi_currency_allowed_flag  "Multi Currency Flag",
       cba.zero_amount_allowed          "Zero Amount Flag",
       cba.account_classification       "Account Classification",
       bb.bank_name                     "Bank Name",
       bb.bank_branch_type              "Bank Branch Type",
       bb.bank_branch_name              "Bank Branch Name",
       bb.bank_branch_number            "Bank Branch Number",
       bb.eft_swift_code                "Swift Code",
       ou.name                          "Operating Unit",
       gcf.concatenated_segments        "GL Code Combination"
  FROM ce_bank_accounts          cba,
       ce_bank_acct_uses_all     bau,
       cefv_bank_branches        bb,
       hr_operating_units        ou,
       gl_code_combinations_kfv  gcf
 WHERE cba.bank_account_id = bau.bank_account_id
   AND cba.bank_branch_id  = bb.bank_branch_id
   AND ou.organization_id  = bau.org_id
   AND cba.asset_code_combination_id = gcf.code_combination_id
   AND (cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE))
 ORDER BY TO_NUMBER(cba.bank_account_num);

Read More

AP: Master Query for PO , Invoice, Payment Details

SELECT DISTINCT a.org_id "ORG ID", e.segment1 "VENDOR NUMBER",
e.vendor_name "VENDOR NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE",
d.segment1 "PO NUM", d.type_lookup_code "PO TYPE",
c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED",
g.item_description "ITEM DESCRIPTION",
g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag,
'Y', 'Approved'
)
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid, h.check_id, i.check_number,
h.invoice_payment_id,
TO_CHAR (TRUNC (i.check_date)) "PAYMENT DATE"
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
ORDER BY E.VENDOR_NAME

Read More

AP: Analyzing A/P Accrual Account Balance

If you are Analyzing the A/P Accrual Account Balance , then you need to monitor potential problems with purchasing and receiving activities that can affect the accuracy of your A/P accrual accounts.

You can use the Accrual Reconciliation Report to identify the following problems in receiving, purchasing, inventory, work in process, or accounts payable transactions:

•         Quantities differ between receipts and invoices
•         Incorrect purchase order or invoice unit prices (previous releases only)
•         Discrepancies in supplier billing
•         Invoice matched to wrong purchase order or wrong purchase order line
•         Received against the wrong purchase order or order line
•         Miscellaneous inventory or work in process transactions that do not belong to the accrual accounts
•         Payables entries for sales tax and freight that do not belong to the accrual accounts

Using the Accrual Reconciliation Report

You should use the Accrual Reconciliation Report to analyze the balance of the Accounts Payable (A/P) accrual accounts.

To submit this report, you must have Purchasing and Payables installed.

You can accrue both expense and inventory purchases as you receive them. When this happens, you temporarily record an accounts payable liability to your Expense or Inventory A/P accrual accounts.

When Payables matches and approves the invoice, Payables clears the A/P accrual accounts and records the liability from the supplier site. See: Accrual Reconciliation Report.

Typically, you run this report at month end. After you have entered your receipt transactions and matched your invoices, you can run the Accrual Reconciliation Report for any transaction date range and identify any differences between your PO Receipts and A/P Invoices.

This report also displays any miscellaneous transactions recorded in error to your accrual accounts. These miscellaneous transactions or transactions unrelated to purchase order receipts may be from Payables, Inventory, or Work in Process (depending on your installation).

 After you have researched the reported accrual balances, you can use the Accrual Write-Off form to indicate which entries you wish to remove and write off from this report.

And, after you have written off these entries, you can use the Accrual Write-Off Report as supporting detail for your manual journal entry.

Read More

AP : Resolving Quantity Differences in Accrual Reconciliation Report

For resolving Quantity Differences issue , the best way is to use Accrual Reconciliation Report.

The Accrual Reconciliation Report lets you easily identify quantity differences (i.e., when the quantity received for a purchase order shipment is smaller than the quantity invoiced).

Such differences leave residual balances that never clear from the A/P accrual accounts. You should investigate the cause of these differences and take corrective actions before closing your period.

Common causes of quantity differences include late inventory receipts, incorrect receipt quantities, and supplier overbilling.

To correct late receipts, ensure that receivers enter all receipts into inventory. To correct receipt quantities, enter receipt corrections.

To correct overbilling errors, follow your standard procedure for supplier debit memos to clear the difference.

Read More

AP : Oracle Payables Standard Reports

1.    Update Income Tax Details Report
2.    Preliminary Invoice Sweep Report
3.    Actual Invoice Sweep Report
4.    Automatic Clearing for Future Dated Payments
5.    Vendors Report
6.    New Vendor/New Vendor Site Listing
7.    Vendor Payment History Report
8.    Vendor Paid Invoice History
9.    Vendor Mailing Labels
10.    Vendor Audit Report
11.    Vendor Merge Report
12.    Purchase Order Header Updates Report
13.    Tax Information Verification Letter
14.    1099 Invoice Exceptions Report
15.    1099 Vendor Exceptions Report
16.    1099 Payments Report
17.    Withholding Tax By Invoice Report
18.    Withholding Tax By Payment Report
19.    Withholding Tax By Tax Authority Report
20.    Withholding Tax By Vendor Report
21.    Withholding Tax Certificate Listing
22.    Invoice Register
23.    Recurring Payments Report
24.    Invoice Import Report
25.    Invoice Import Exceptions Report
26.    AutoApproval Report
27.    Invoice on Hold Report
28.    Matching Hold Detail Report
29.    Invoice Approval Quick Release Report
30.    Batch Control Report By Batch Name
31.    Prepayments Status Report
32.    Invoice Audit By Voucher Number
33.    Invoice Aging Report
34.    Expense Distribution Detail Report
35.    Invoice Audit Listing
36.    Invoice Audit Report
37.    Unrealized Gain/Loss Report
38.    Mass Additions Create Report
39.    Accrual Reconciliation Report
40.    Accrual Write-Off Report
41.    Budgetary Control Transactions Report
42.    Tax Audit Trail (By Tax Code or Invoice)
43.    Use Tax Liability Report
44.    Intra-EC VAT Audit Trail
45.    Tax Declaration Report
46.    Payment Register
47.    Payment Audit By Voucher Number
48.    Cash Requirement Report
49.    Discount Available Report
50.    Payment Batch Control Report
51.    Preliminary Payment Register
52.    Final Payment Register
53.    Discounts Taken and Lost Report
54.    Payment Distribution Report
55.    Undistributed Payments Report
56.    Stop Payments Report
57.    Void Payment Register
58.    Missing Document Numbers Report
59.    Payment Exceptions Report
60.    Payment Reconciliation Exceptions Report
61.    Reconciliation Summary Report
62.    Payment Reconciliation Detail Report
63.    Posting Hold Report
64.    Accounts Payable Journal Entry Audit Report
65.    Accounts Payable Journal Entry Exception Report
66.    Posted Invoice Register
67.    Posted Payment Register
68.    Accounts Payable Trial Balance
69.    Preliminary Purged Listing
70.    Void
71.    Receipt Date/Receiver
72.    Last Active Date
73.    Invoice and Payments Listing
74.    Requisition. Purchase Orders, and Requisition Listings
75.    Last Active Date
76.    Invoice Payment Listings
77.    Requisitions, Purchase Orders, and Requisitions Listings
78.    Key Indicators Current Activity
79.    Key Indicators State-of-the-System
80.    Print Invoice Report
81.    Prepayment Remittance Report
82.    Matching Hold Agent Notice
83.    Receiving Hold Requester Notice
84.    Invalid PO Vendor Notice
85.    AutoClear Code Listing
86.    Bank Account Listing
87.    Distribution Set Listing
88.    Employee Listing
89.    Payment Terms Listing
90.    QuickCode Listing
91.    Standard Notes Listing
92.    Tax Names Listing
93.    Expense Express Report Listing

Read More

AP : 2 WAY MATCHING QUERY

Two–Way: Purchase order and invoice quantities must match within tolerance before the corresponding invoice can be paid.

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'N'

Read More

AP : 3 WAY MATCHING QUERY

Three–Way: Purchase order, receipt, and invoice quantities must match within tolerance before the corresponding invoice can be paid

Here are the query for 3 way Matching

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'N'
AND g1.receipt_required_flag = 'Y'

Read More

AP : 4 WAY MATCHING QUERY

Four–Way: Purchase order, receipt, accepted, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Here is query for 4 way matching .

SELECT DISTINCT
a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO DATE", d.segment1 "PO NUM",
d.type_lookup_code "PO TYPE", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g1.quantity_received, g1.quantity_rejected, g1.quantity_billed,
g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO LINE AMOUNT",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO APPROVED?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"INVOICE APPROVED?",
a.amount_paid
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
ap.ap_suppliers e,
ap.ap_supplier_sites_all f,
po.po_lines_all g,
po.po_line_locations_all g1
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND g1.po_header_id = c.po_header_id
AND g1.po_line_id = g.po_line_id
AND c.po_line_id = g.po_line_id
AND c.line_location_id = g1.line_location_id
AND g1.inspection_required_flag = 'Y'
AND g1.receipt_required_flag = 'Y'

Read More