The following is query to find all AP open invoices in Oracle ebs R12, run it, export the result on excel sheet, and move it to Oracle template FBDI for Oracle Fusion data migration.
This query can used for the following:
1 – Oracle EBS R12 to Fusion data migration, just extract the results and put it in your FBDI template, indeed it will give you the desire results.
2 – Also you can use it to Detect all the remaining amount on invoices which is not paid yet.
3 – At the same time use it if user ask you for a report of All AP invoices in Oracle EBS R12.
SELECT DISTINCT
A.INVOICE_ID ,
a.org_id, "Business Unit" ,
a.source ,
a.invoice_num invoice_number,
a.invoice_amount "Invoice Amount",
a.invoice_amount - NVL(a.amount_paid, 0) "Remaining Amount",
(a.invoice_amount - NVL(a.amount_paid,0)) * nvl(a.exchange_rate, 1 ) "Remaing in KWD",
to_char(a.invoice_date, 'YYYY/MM/DD') "Invoice Date",
b.vendor_name "Supplier Name",
b.segment1 "Supplier Number",
c.vendor_site_code "Supplier Site",
a.invoice_currency_code "Invoice Currency",
a.payment_currency_code "Payment Currency" ,
a.description,
null "Import Set" ,
a.invoice_type_lookup_code "Invoice Type",
null "Legal Entity" ,
null "Customer Tax Registration Number",
null "Customer Registration Code",
null "First-Party Tax Registration Number",
null "Supplier Tax Registration Number",
trm.name,
to_char(a.terms_date, ' YYYY/MM/DD') ,
null "Goods Received Date" ,
null "Invoice Received Date",
to_char(a.gl_date , 'YYYY/MM/DD') "Accounting Date",
a.payment_method_code "Payment Method",
a.pay_group_lookup_code "Pay Group",
a.exclusive_payment_flag "Pay Alone",
null "Discountable Amount",
null "Prepayment Number",
null "Prepayment Line Number",
null "Prepayment Application Amount",
null "Prepayment Accounting Date",
null "Invoice Includes Prepayment",
a.exchange_rate_type "Convertion Rate Type",
a.exchange_date "Converstion Date",
a.exchange_rate "Converstion Rate",
--a.accts_pay_code_combination_id "Liability acctoun PLZ Check it",
--gcc.segment1||'.' || gcc.segment2 ||'.'|| gcc.segment3 ||'.' || gcc.segment4 ||'.'|| gcc.segment5
"accts_pay_code_combination_id " ,
a.doc_category_code "Docuemtn Category Code",
a.voucher_num "Voucher Number",
a.delivery_channel_code "Delivery Channel Code",
a.bank_charge_bearer "Bank Charge Bearer",
null "Remit-to Supplier",
null "Remit-to Supplier Number",
null "Remit-to Address Name",
--d.payment_priority "payment priority",
(select payment_priority from ap_payment_schedules_all pay where pay.invoice_id = a.invoice_id
)"payment priority",
a.settlement_priority "settlement_priority",
null "Unique Remittance Identifier",
null "Unique Remittance Identifier Check Digit",
a.payment_reason_code "payment reason code",
a.payment_reason_comments "payment reason comments" ,
null "Remittance Message 1", -- this added to be exactly matched with FBDI templete you can change it
null "Remittance Message 2",
null "Remittance Message 3",
null "Withholding Tax Group",
null "Ship-to Location",
null "Taxation Country",
null "Document Sub Type",
null "Tax Invoice Internal Sequence Number",
null "Supplier Tax Invoice Number",
null "Tax Invoice Recording Date",
null "Supplier Tax Invoice Date",
null "Supplier Tax Invoice Conversion Rate",
null "Port Of Entry Code",
null "Import Document Number",
null "Import Document Date",
null "Tax Control Amount",
null "Calculate Tax During Import",
null "Add Tax To Invoice Amount",
null "Attribute Category",
null "Attribute 1",
a.attribute2 "attribute2 Contract lookup code",
null "Attribute 3",
null "Attribute 4",
null "Attribute 5",
null "Attribute 6",
null "Attribute 7",
null "Attribute 8",
null "Attribute 9",
null "Attribute 10",
null "Attribute 11",
null "Attribute 12",
null "Attribute 13",
null "Attribute 14",
null "Attribute 15",
null "Attribute Number 1",
null "Attribute Number 2" ,
null "Attribute Number 3" ,
null "Attribute Number 4",
null "Attribute Number 5",
null "Attribute Date 1" ,
null "Attribute Date 2" ,
null "Attribute Date 3" ,
null "Attribute Date 4" ,
null "Attribute Date 5" ,a.invoice_date
FROM
apps.ap_terms_tl trm ,
apps.ap_invoices_all a,
apps.ap_suppliers b,
apps.ap_supplier_sites_all c
WHERE 1 = 1
AND trm.term_id = a.terms_id
AND a.vendor_id = b.vendor_id
AND a.vendor_site_id = c.vendor_site_id
AND b.vendor_id = c.vendor_id
AND a.org_id :P_ORGID -- Your Organization ID
AND a.invoice_date <= :PDATE -- All the invoice in or before this date
AND a.payment_status_flag IN ('P', 'N') -- the invoice should not be paid or the remainning amount
AND a.cancelled_date IS null
ORDER BY a.invoice_date , a.invoice_id desc
Read More:
More ERP Scope:
https://erpscope365.blogspot.com/
https://www.youtube.com/@ERPScope/featured
https://www.instagram.com/erp_scope/
https://www.reddit.com/r/erpscope/
Leave a Reply