Oracle ERP Tech Blogs: September 2023

AP Invoice Query

AP Invoice

In this post, we will discuss about AP invoice. An invoice is created when organization has to pay to supplier. Supplier send invoice to company and further company pay to supplier for its goods or services.

Below query will provide invoice details, created in system.

Invoice Query

SELECT aia.invoice_id,
aia.invoice_num,
psv.vendor_name,
pssv.vendor_site_code,
gcc.segment1||'.'||gcc.segment2||gcc.segment3||'.'||gcc.segment4||gcc.segment5||'.'||gcc.segment6 account_combination,
SUM(aid.amount) invoice_amount
FROM ap_invoices_all aia,
poz_suppliers_v psv,
poz_supplier_sites_v pssv,
ap_invoice_lines_all ail,
ap_invoice_distributions_all aid,
gl_code_combinations gcc
WHERE aia.vendor_id = psv.vendor_id
AND aia.vendor_site_id = pssv.vendor_site_id
AND psv.vendor_id = pssv.vendor_id
AND aia.org_id = pssv.org_id
AND aia.invoice_id = ail.invoice_id
AND ail.invoice_id = aid.invoice_id
AND ail.line_number = aid.invoice_line_number
AND aid.code_combination_id = gcc.code_combination_id
AND aia.invoice_num = 'Test12345'
GROUP BY aia.invoice_id,
aia.invoice_num,
psv.vendor_name,
pssv.vendor_site_code,
gcc.segment1||'.'||gcc.segment2||gcc.segment3||'.'||gcc.segment4||gcc.segment5||'.'||gcc.segment6

XLA to GL Link

XLA to GL Table Links

Subledger is a subset of general ledger which contain accounting information but do not have any chart of accounts. It is linked from source table and move accounting details to GL after final accounting.

Subledger Tables

XLA_TRANSACTION_ENTITIES

XLA_EVENTS

XLA_AE_HEADERS

XLA_AE_LINES

General Ledger tables

GL_IMPORT_REFERENCES

GL_JE_HEADERS

GL_JE_LINES

GL_JE_BATCHES

select * from xla_transaction_entities where transaction_number =250619 -- For entity_id

select * from xla_events where event_id = 373021620 --entity_id = 211817231

select * from xla_ae_headers where event_id = 373021620 --event_id from xla_events

select * from xla_ae_lines where ae_header_id = 465629469 --ae_header_id from xla_ae_headers

select * from gl_import_references where gl_sl_link_id in (1100155677) --gl_sl_link_id from xla_ae_lines

select * from gl_je_headers where je_header_id =808500 -- je_header_id from gl_import_references

select * from gl_je_batches where je_batch_id = 811239

Query

SELECT xte.source_id_int_1 ,

xte.transaction_number,

gjh.name journal_name,

NVL(gjl.accounted_dr,0) accounted_dr,

NVL(gjl.accounted_cr,0) accounted_cr,

gjb.name journal_batch,

gjl.je_line_num,

gcc.segment1||'.'||

gcc.segment2||'.'||

gcc.segment3||'.'||

gcc.segment4||'.'||

gcc.segment5||'.'||

gcc.segment6 account_code

FROM xla_transaction_entities xte,

xla_events xe,

xla_ae_headers xah,

xla_ae_lines xal,

gl_import_references gir,

gl_je_headers gjh,

gl_je_lines gjl,

gl_je_batches gjb,

gl_code_combinations gcc

WHERE xte.entity_id = xe.entity_id

AND xte.application_id = xe.application_id

AND xte.entity_code = 'TRANSACTIONS'

AND xte.application_id = 222 --Receivables

AND xe.entity_id = xah.entity_id

AND xe.event_id = xah.event_id

AND xah.ae_header_id = xal.ae_header_id

AND xal.gl_sl_link_id = gir.gl_sl_link_id

AND gir.je_header_id = gjh.je_header_id

AND gjh.je_header_id = gjl.je_header_id

AND gir.je_line_num = gjl.je_line_num

AND gjh.je_batch_id = gjb.je_batch_id

AND xah.application_id = 222 --Receivables

AND xah.application_id = xal.application_id

AND gjl.code_combination_id = gcc.code_combination_id;

AP Supplier Aging Query

Supplier Aging

Aging shows outstanding amount of suppliers and this amount is further showing in buckets. Bucket is basically created on business requirement i.e. 1 day - 30 days, 31 days - 60 days and so on. Below logic is developed using account payables base tables.

Outstanding logic manipulated considering price variance, freight, miscellaneous, prepay, recoverable tax, non-recoverable tax, prepay tax, discount price. This query also includes multiple installment of payment.

The logic considered amount from distribution in case any invoice cancelled later on as on date. In cancelled case, invoice and invoice line amount got zero but AP distribution hold that amount.

Tables used in Supplier Aging.

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

AP_INVOICE_PAYMENTS_ALL

AP_CHECKS_ALL

AP_PAYMENT_SCHEDULES_ALL

HZ_PARTIES
HZ_PARTY_SITES

SELECT ORG_ID,
ORG_NAME,
VOUCHER_NUMBER,
VENDOR_NAME,
VENDOR_NUMBER,
SHORT_VENDOR_NAME,
VENDOR_ID,
VENDOR_SITE_CODE,
VENDOR_SITE,
REFERENCE_NUMBER,
ADDRESS_ID,
INVOICE_TYPE,
INVOICE_ID,
DUE_DATE,
LEDGER_CURRENCY,
INVOICE_CURRENCY_CODE,
TERMS_NAME,
ACCTS_PAY_CODE_COMBINATION_ID,
INVOICE_NUMBER,
PO_NUM,
PROJECT_NUM,
INSTALLMENT_NUMBER,
DECODE (INVOICE_TYPE,
'PREPAYMENT', (-1) * INVOICE_AMOUNT,
INVOICE_AMOUNT)
INVOICE_AMOUNT,
DECODE (INVOICE_TYPE,
'PREPAYMENT', (-1) * GROSS_AMOUNT,
INVOICE_AMOUNT)
GROSS_AMOUNT,
INVOICE_DATE,
INV_DATE,
DAYS_PAST_DUE,
DATA_CONVERTED,
EXCHANGE_RATE,
DECODE (INVOICE_TYPE,
'PREPAYMENT', (-1) * AMT_DUE_ORIGINAL,
AMT_DUE_ORIGINAL)
AMT_DUE_ORIGINAL,
C_AMT_DUE_REMAINING,
NVL (c_amt_due_remaining, 0) TOT_OUTST_SAR,
DAY_OVER_DUE,
CASE WHEN DAY_OVER_DUE <= 0 THEN C_AMT_DUE_REMAINING ELSE 0 END
NOT_DUE,
CASE
WHEN DAY_OVER_DUE BETWEEN 1 AND 30 THEN C_AMT_DUE_REMAINING
ELSE 0
END
BUK1_30,
CASE
WHEN DAY_OVER_DUE BETWEEN 31 AND 60 THEN C_AMT_DUE_REMAINING
ELSE 0
END
BUK31_60,
CASE
WHEN DAY_OVER_DUE BETWEEN 61 AND 90 THEN C_AMT_DUE_REMAINING
ELSE 0
END
BUK61_90,
CASE
WHEN DAY_OVER_DUE BETWEEN 91 AND 180 THEN C_AMT_DUE_REMAINING
ELSE 0
END
BUK91_180,
CASE
WHEN DAY_OVER_DUE BETWEEN 181 AND 360 THEN C_AMT_DUE_REMAINING
ELSE 0
END
BUK181_360,
CASE WHEN DAY_OVER_DUE >= 361 THEN C_AMT_DUE_REMAINING ELSE 0 END
BUK361,
ASONDATE,
TERMS_DATE,
NVL(c_amt_due_rem_orig,0) c_amt_due_rem_orig
FROM (
SELECT ORG_ID,
ORG_NAME,
VOUCHER_NUMBER,
VENDOR_NAME,
VENDOR_NUMBER,
SHORT_VENDOR_NAME,
VENDOR_ID,
VENDOR_SITE_CODE,
VENDOR_SITE,
REFERENCE_NUMBER,
ADDRESS_ID,
INVOICE_TYPE,
INVOICE_ID,
DUE_DATE,
LEDGER_CURRENCY,
INVOICE_CURRENCY_CODE,
TERMS_NAME,
ACCTS_PAY_CODE_COMBINATION_ID,
INVOICE_NUMBER,
PO_NUM,
PROJECT_NUM,
INSTALLMENT_NUMBER,
INVOICE_AMOUNT,
GROSS_AMOUNT,
INVOICE_DATE,
INV_DATE,
DAYS_PAST_DUE,
DATA_CONVERTED,
EXCHANGE_RATE,
AMT_DUE_ORIGINAL,
ROUND(C_AMT_DUE_REMAINING,2) C_AMT_DUE_REMAINING,
ROUND(TOT_OUTST_SAR,2) TOT_OUTST_SAR,
DAY_OVER_DUE,
ASONDATE,
TERMS_DATE,
ROUND(c_amt_due_rem_orig,2) c_amt_due_rem_orig,
prepay_amount_ex,
prepay_amount_orig
FROM (
SELECT ORG_ID,
ORG_NAME,
VOUCHER_NUMBER,
VENDOR_NAME,
VENDOR_NUMBER,
SHORT_VENDOR_NAME,
VENDOR_ID,
VENDOR_SITE_CODE,
VENDOR_SITE,
Rtrim(Xmlagg (Xmlelement (e,REFERENCE_NUMBER || ',')).extract ( '//text()' ).GetClobVal(), ',') REFERENCE_NUMBER,
ADDRESS_ID,
INVOICE_TYPE,
INVOICE_ID,
--DUE_DATE,
Rtrim(Xmlagg (Xmlelement (e,DUE_DATE || ',')).extract ( '//text()' ).GetClobVal(), ',') DUE_DATE,
LEDGER_CURRENCY,
INVOICE_CURRENCY_CODE,
TERMS_NAME,
ACCTS_PAY_CODE_COMBINATION_ID,
INVOICE_NUMBER,
PO_NUM,
PROJECT_NUM,
--INSTALLMENT_NUMBER,
Rtrim(Xmlagg (Xmlelement (e,INSTALLMENT_NUMBER || ',')).extract ( '//text()' ).GetClobVal(), ',') INSTALLMENT_NUMBER,
(INVOICE_AMOUNT) INVOICE_AMOUNT,
SUM(GROSS_AMOUNT) GROSS_AMOUNT,
INVOICE_DATE,
INV_DATE,
Rtrim(Xmlagg (Xmlelement (e,DAYS_PAST_DUE || ',')).extract ( '//text()' ).GetClobVal(), ',') DAYS_PAST_DUE,
DATA_CONVERTED,
EXCHANGE_RATE,
AMT_DUE_ORIGINAL,
DAY_OVER_DUE,
to_char(:P_TO_DATE,'DD-MON-RRRR') As ASONDATE,
TERMS_DATE,
(SUM(NVL(inv_amount,0)) - SUM(NVL(prepay_amount_ex,0)) - SUM(NVL(check_payment,0)) + SUM(NVL(rec_tax,0)) + SUM(NVL(awt_amount,0)) + SUM(NVL(ipv_amount,0)) + SUM(NVL(freight_amount,0)) + SUM(NVL(misc_amount,0))) C_AMT_DUE_REMAINING,
(SUM(NVL(inv_amount,0)) - SUM(NVL(prepay_amount_ex,0)) - SUM(NVL(check_payment,0)) + SUM(NVL(rec_tax,0)) + SUM(NVL(awt_amount,0)) + SUM(NVL(ipv_amount,0)) + SUM(NVL(freight_amount,0)) + SUM(NVL(misc_amount,0))) TOT_OUTST_SAR,
(SUM(NVL(inv_amount_orig,0)) - SUM(NVL(prepay_amount_orig,0)) - SUM(NVL(check_payment_orig,0)) + SUM(NVL(rec_tax_orig,0)) + SUM(NVL(awt_amount_orig,0)) + SUM(NVL(ipv_amount_orig,0)) + SUM(NVL(freight_amount_orig,0)) + SUM(NVL(misc_amount_orig,0))) c_amt_due_rem_orig,
prepay_amount_ex, prepay_amount_orig
FROM (
SELECT AIA.ORG_ID,
HOU.NAME ORG_NAME,
AIA.DOC_SEQUENCE_VALUE VOUCHER_NUMBER,
APSS.BASE_CURRENCY_CODE LEDGER_CURRENCY,
NVL (PSV.VENDOR_NAME, HP.PARTY_NAME) VENDOR_NAME,
NVL (PSV.SEGMENT1, PARTY_NUMBER) VENDOR_NUMBER,
NVL (PSV.VENDOR_NAME, HP.PARTY_NAME) SHORT_VENDOR_NAME,
PSV.VENDOR_ID VENDOR_ID,
NVL (PSSV.VENDOR_SITE_CODE, SITES.PARTY_SITE_NAME)
VENDOR_SITE_CODE,
NVL (PSSV.VENDOR_SITE_CODE, SITES.PARTY_SITE_NAME) VENDOR_SITE,
APSA.PAYMENT_NUM REFERENCE_NUMBER,
AIA.VENDOR_SITE_ID ADDRESS_ID,
AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
AIA.INVOICE_ID,
TO_CHAR (APSA.DUE_DATE, 'DD-MON-RRRR') DUE_DATE,
(TO_NUMBER (TRUNC (AIA.INVOICE_DATE) - TRUNC (:P_TO_DATE)) * -1)
DAY_OVER_DUE,
AIA.INVOICE_CURRENCY_CODE,
(SELECT TRM.NAME
FROM AP_TERMS_TL TRM
WHERE TRM.TERM_ID = AIA.TERMS_ID AND TRM.LANGUAGE = 'US')
TERMS_NAME,
AIA.ACCTS_PAY_CODE_COMBINATION_ID
ACCTS_PAY_CODE_COMBINATION_ID,
AIA.INVOICE_NUM INVOICE_NUMBER,
AIA.INVOICE_AMOUNT,
APSA.GROSS_AMOUNT,
TO_CHAR (AIA.INVOICE_DATE, 'DD-MON-RRRR') INVOICE_DATE,
TO_CHAR (AIA.INVOICE_DATE, 'YYYYMMDD') INV_DATE,
CEIL (SYSDATE - APSA.DUE_DATE) DAYS_PAST_DUE,
DECODE (AIA.INVOICE_CURRENCY_CODE,
APSS.BASE_CURRENCY_CODE, ' ',
DECODE (AIA.EXCHANGE_RATE, NULL, '*', ' '))
DATA_CONVERTED,
NVL (AIA.EXCHANGE_RATE, 1) EXCHANGE_RATE,
DECODE (
AIA.INVOICE_CURRENCY_CODE,
APSS.BASE_CURRENCY_CODE, DECODE (
NVL (
APSS.
MINIMUM_ACCOUNTABLE_UNIT,
0),
0, ROUND (
( (NVL (
APSA.GROSS_AMOUNT,
0)
/ (NVL (
AIA.
PAYMENT_CROSS_RATE,
1)))
* NVL (
AIA.EXCHANGE_RATE,
1)),
APSS.PRECISION),
ROUND (
( (NVL (APSA.GROSS_AMOUNT,
0)
/ (NVL (
AIA.
PAYMENT_CROSS_RATE,
1)))
* NVL (AIA.EXCHANGE_RATE,
1))
/ NVL (
APSS.
MINIMUM_ACCOUNTABLE_UNIT,
0))
* NVL (
APSS.
MINIMUM_ACCOUNTABLE_UNIT,
0)),
DECODE (
AIA.EXCHANGE_RATE,
NULL, 0,
DECODE (
NVL (APSS.MINIMUM_ACCOUNTABLE_UNIT, 0),
0, ROUND (
( (NVL (APSA.GROSS_AMOUNT, 0)
/ (NVL (AIA.PAYMENT_CROSS_RATE, 1)))
* NVL (AIA.EXCHANGE_RATE, 1)),
APSS.PRECISION),
ROUND (
( (NVL (APSA.GROSS_AMOUNT, 0)
/ (NVL (AIA.PAYMENT_CROSS_RATE, 1)))
* NVL (AIA.EXCHANGE_RATE, 1))
/ NVL (APSS.MINIMUM_ACCOUNTABLE_UNIT, 0))
* NVL (APSS.MINIMUM_ACCOUNTABLE_UNIT, 0))))
AMT_DUE_ORIGINAL,
ROUND (
NVL((SELECT SUM((apid.amount) * NVL (aia.exchange_rate, 1))
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all apid,
ap_payment_schedules_all apsa1
WHERE ail.invoice_id = aia.invoice_id
AND apid.invoice_id = aia.invoice_id
AND apid.invoice_line_number = ail.line_number
AND apsa1.invoice_id = aia.invoice_id
AND apsa1.payment_num = apsa.payment_num
AND ail.org_id = aia.org_id
AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')
AND apid.accounting_date <= :P_TO_DATE),0)
,2)
inv_amount,
NVL (
(SELECT SUM ((aip.amount + NVL(aip.discount_taken,0)) * NVL (aia.EXCHANGE_RATE, 1))
FROM ap_invoice_payments_all aip,
ap_checks_All aca
WHERE aip.invoice_id = aia.invoice_id
AND aca.check_id = aip.check_id
AND ((aca.void_date IS NULL) OR (aca.void_date > :P_TO_DATE))
AND accounting_date <= :P_TO_DATE
),
0) check_payment,
NVL (
(SELECT SUM ((aida1.amount * NVL (aia.EXCHANGE_RATE, 1)))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'REC_TAX'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) rec_tax,
NVL (
(SELECT SUM ((aida1.amount)* NVL(aia.exchange_rate, 1))
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'AWT'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) awt_amount,
NVL (
(SELECT SUM ((aida1.amount)* NVL(aia.exchange_rate, 1))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'MISCELLANEOUS'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) misc_amount,
NVL (
(SELECT SUM ((aida1.amount)* NVL(aia.exchange_rate, 1))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'IPV'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) ipv_amount,
NVL (
(SELECT SUM ((aida1.amount)* NVL(aia.exchange_rate, 1))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'FREIGHT'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) freight_amount,
AP_INVOICES_PKG.GET_PO_NUMBER_LIST (AIA.INVOICE_ID) PO_NUM,
(SELECT LISTAGG (PPAB.SEGMENT1, ',')
WITHIN GROUP (ORDER BY PPAB.SEGMENT1)
FROM PJF_PROJECTS_ALL_B PPAB, ap_invoice_lines_all AID
WHERE AIA.INVOICE_ID = AID.INVOICE_ID
AND AIA.ORG_ID = AID.ORG_ID
AND AID.PJC_PROJECT_ID = PPAB.PROJECT_ID(+))
PROJECT_NUM,
APSA.PAYMENT_NUM INSTALLMENT_NUMBER,
TO_CHAR (AIA.TERMS_DATE, 'DD-MON-RRRR') TERMS_DATE,
ROUND (
NVL((SELECT SUM((apid.amount))
FROM ap_invoice_lines_all ail,
ap_invoice_distributions_all apid,
ap_payment_schedules_all apsa1
WHERE ail.invoice_id = aia.invoice_id
AND apid.invoice_id = aia.invoice_id
AND apid.invoice_line_number = ail.line_number
AND apsa1.invoice_id = aia.invoice_id
AND apsa1.payment_num = apsa.payment_num
AND ail.org_id = aia.org_id
AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')
AND apid.accounting_date <= :P_TO_DATE),0)
,2)
inv_amount_orig,
NVL (
(SELECT SUM ((aip.amount + NVL(aip.discount_taken,0)))
FROM ap_invoice_payments_all aip,
ap_checks_All aca
WHERE aip.invoice_id = aia.invoice_id
AND aca.check_id = aip.check_id
AND accounting_date <= :P_TO_DATE
),
0) check_payment_orig,
NVL (
(SELECT SUM ((aida1.amount))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'REC_TAX'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) rec_tax_orig,
NVL (
(SELECT SUM ((aida1.amount))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'AWT'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) awt_amount_orig,
NVL (
(SELECT SUM ((aida1.amount))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'MISCELLANEOUS'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) misc_amount_orig,
NVL (
(SELECT SUM ((aida1.amount))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'IPV'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) ipv_amount_orig,
NVL (
(SELECT SUM ((aida1.amount))
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'FREIGHT'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) freight_amount_orig,
NVL (
(SELECT SUM ((aida1.amount * NVL (aia.EXCHANGE_RATE, 1))*-1)
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) prepay_amount_ex,
NVL (
(SELECT SUM ((aida1.amount )*-1)
FROM ap_invoice_lines_all ail, ap_invoice_distributions_all aida1
WHERE 1=1
AND aida1.LINE_TYPE_LOOKUP_CODE = 'PREPAY'
AND ail.INVOICE_ID = aia.invoice_id
AND ail.invoice_id = aida1.invoice_id
AND ail.line_number = aida1.invoice_line_number
AND trunc(aida1.accounting_date) <= trunc(:P_TO_DATE)),
0) prepay_amount_orig
FROM AP_INVOICES AIA,
AP_PAYMENT_SCHEDULES APSA,
HZ_PARTIES HP,
HZ_PARTY_SITES SITES,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_V PSSV,
HR_OPERATING_UNITS HOU,
(SELECT P.BASE_CURRENCY_CODE,
C.PRECISION,
NVL (C.MINIMUM_ACCOUNTABLE_UNIT, 0)
MINIMUM_ACCOUNTABLE_UNIT,
C.DESCRIPTION,
P.ORG_ID
FROM AP_SYSTEM_PARAMETERS P, FND_CURRENCIES_VL C
WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE) APSS
WHERE APSA.INVOICE_ID = AIA.INVOICE_ID
AND APSA.ORG_ID = AIA.ORG_ID
AND HP.PARTY_ID = AIA.PARTY_ID
AND PSV.VENDOR_ID(+) = AIA.VENDOR_ID
AND PSSV.VENDOR_SITE_ID(+) = AIA.VENDOR_SITE_ID
AND PSSV.VENDOR_ID(+) = AIA.VENDOR_ID
AND APSS.ORG_ID = AIA.ORG_ID
AND HOU.ORGANIZATION_ID = AIA.ORG_ID
AND SITES.PARTY_ID = HP.PARTY_ID
AND aia.invoice_type_lookup_code <> 'PREPAYMENT'
AND aia.PARTY_SITE_ID = sites.PARTY_SITE_ID
AND (AIA.APPROVAL_STATUS IN ('APPROVED')
OR aia.wfapproval_status IN
('MANUALLY APPROVED', 'NOT REQUIRED'))
AND ( (AIA.CANCELLED_DATE IS NOT NULL
AND TRUNC (AIA.CANCELLED_DATE) > :P_TO_DATE)
OR (AIA.CANCELLED_DATE IS NULL))
AND EXISTS (SELECT '1'
FROM AP_INVOICE_DISTRIBUTIONS
WHERE INVOICE_ID = AIA.INVOICE_ID
AND ACCOUNTING_DATE <= :P_TO_DATE
AND NVL(POSTED_FLAG,'N') = 'Y'
)
AND UPPER (HOU.NAME) = UPPER (NVL (:P_ORG_NAME, HOU.NAME))
AND LTRIM(RTRIM(HP.party_name)) = LTRIM(RTRIM(NVL (:P_FROM_VENDOR_NAME, HP.party_name)))
) Z
GROUP BY ORG_ID,
ORG_NAME,
VOUCHER_NUMBER,
VENDOR_NAME,
VENDOR_NUMBER,
SHORT_VENDOR_NAME,
VENDOR_ID,
VENDOR_SITE_CODE,
VENDOR_SITE,
ADDRESS_ID,
INVOICE_TYPE,
INVOICE_ID,
LEDGER_CURRENCY,
INVOICE_CURRENCY_CODE,
TERMS_NAME,
ACCTS_PAY_CODE_COMBINATION_ID,
INVOICE_NUMBER,
PO_NUM,
PROJECT_NUM,
INVOICE_AMOUNT,
INVOICE_DATE,
INV_DATE,
DATA_CONVERTED,
EXCHANGE_RATE,
AMT_DUE_ORIGINAL,
DAY_OVER_DUE,
TERMS_DATE
,prepay_amount_ex,
prepay_amount_orig
))
WHERE ROUND(NVL(C_AMT_DUE_REMAINING,0),1) != 0
ORDER BY VENDOR_NAME, INV_DATE;

Oracle Fusion Supplier Available Prepayment Queries

Supplier Available Prepayment

In this post, we will get detail of supplier available prepayment. Available prepayment means the amount which the organization received from its supplier but still not adjusted. Below query will provide the complete detail of prepayment.

Detail Query

SELECT typ,ORG_ID,

ORG_NAME,

Prepayment_Type,

VENDOR_NAME lpp_vendor,

VENDOR_NUMBER lpp_vendor_num,

VENDOR_SITE_CODE lpp_site,

INVOICE_TYPE,

DUE_DATE lpp_due_date,

Payment_CURRENCY_CODE curr_code,

INVOICE_NUMBER,

INSTALLMENT_NUMBER,

INVOICE_DATE,

INV_DATE,

to_char(:P_TO_DATE,'DD-MON-RRRR') As ASONDATE,

C_AMT_DUE_REMAINING,

NVL (c_amt_due_remaining, 0) PP_TOT_OUTST_SAR,

DAY_OVER_DUE,

CASE WHEN DAY_OVER_DUE <= 0 THEN C_AMT_DUE_REMAINING ELSE 0 END

pp_NOT_DUE,

CASE

WHEN DAY_OVER_DUE BETWEEN 1 AND 30 THEN C_AMT_DUE_REMAINING

ELSE 0

END

pp_BUK1_30,

CASE

WHEN DAY_OVER_DUE BETWEEN 31 AND 60 THEN C_AMT_DUE_REMAINING

ELSE 0

END

pp_BUK31_60,

CASE

WHEN DAY_OVER_DUE BETWEEN 61 AND 90 THEN C_AMT_DUE_REMAINING

ELSE 0

END

pp_BUK61_90,

CASE

WHEN DAY_OVER_DUE BETWEEN 91 AND 180 THEN C_AMT_DUE_REMAINING

ELSE 0

END

pp_BUK91_180,

CASE

WHEN DAY_OVER_DUE BETWEEN 181 AND 360 THEN C_AMT_DUE_REMAINING

ELSE 0

END

pp_BUK181_360,

CASE WHEN DAY_OVER_DUE >= 361 THEN C_AMT_DUE_REMAINING ELSE 0 END

pp_BUK361,

TERMS_DATE,

c_amt_due_rem_orig

FROM (SELECT '1' typ,AIA.ORG_ID,

HOU.NAME ORG_NAME,

NVL (PSV.VENDOR_NAME, HP.PARTY_NAME) VENDOR_NAME,

NVL (PSV.SEGMENT1, PARTY_NUMBER) VENDOR_NUMBER,

PSV.VENDOR_ID VENDOR_ID,

NVL (PSSV.VENDOR_SITE_CODE, SITES.PARTY_SITE_NAME)

VENDOR_SITE_CODE,

AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,

TO_CHAR (APSA.DUE_DATE, 'DD-MON-RRRR') DUE_DATE,

(TO_NUMBER (

TRUNC (NVL (AIA.INVOICE_DATE, :P_TO_DATE))

- TRUNC (:P_TO_DATE))

* -1)

DAY_OVER_DUE,

aia.Payment_CURRENCY_CODE,

AIA.INVOICE_NUM INVOICE_NUMBER,

TO_CHAR (AIA.INVOICE_DATE, 'DD-MON-RRRR') INVOICE_DATE,

TO_CHAR (AIA.INVOICE_DATE, 'YYYYMMDD') INV_DATE,

NVL(DECODE(NVL(gross_Amount,0) * NVL (aia.exchange_rate, 1),0,

NVL((SELECT SUM((apid.amount) * NVL (aia.exchange_rate, 1))

FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all apid,

ap_payment_schedules_all apsa1

WHERE ail.invoice_id = aia.invoice_id

AND apid.invoice_id = aia.invoice_id

AND apid.invoice_line_number = ail.line_number

AND apsa1.invoice_id = aia.invoice_id

AND apsa1.payment_num = apsa.payment_num

AND ail.org_id = aia.org_id

AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')

AND apid.accounting_date <= :P_TO_DATE),0),NVL(gross_Amount,0) * NVL (aia.exchange_rate, 1))

-NVL((SELECT SUM((aipa.amount + NVL(aipa.discount_taken,0)) * NVL (aia.exchange_rate, 1)) FROM ap_invoice_payments_all aipa

WHERE aipa.invoice_id = aia.invoice_id

AND aipa.payment_num = apsa.payment_num

AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))

),0),0) c_amt_due_remaining,

----

APSA.PAYMENT_NUM INSTALLMENT_NUMBER,

DECODE (AP_INVOICES_UTILITY_PKG.

get_approval_status (aia.invoice_id,

aia.invoice_amount,

aia.payment_status_flag,

aia.invoice_type_lookup_code),

'PERMANENT', 'Permanant',

'Temporary')

Prepayment_Type,

TO_CHAR (AIA.TERMS_DATE, 'DD-MON-RRRR') TERMS_DATE,

NVL(DECODE(NVL(gross_Amount,0) ,0,

NVL((SELECT SUM((apid.amount) )

FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all apid,

ap_payment_schedules_all apsa1

WHERE ail.invoice_id = aia.invoice_id

AND apid.invoice_id = aia.invoice_id

AND apid.invoice_line_number = ail.line_number

AND apsa1.invoice_id = aia.invoice_id

AND apsa1.payment_num = apsa.payment_num

AND ail.org_id = aia.org_id

AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')

AND apid.accounting_date <= :P_TO_DATE

),0),NVL(gross_Amount,0)) -

NVL((SELECT SUM((aipa.amount + NVL(aipa.discount_taken,0))) FROM ap_invoice_payments_all aipa

WHERE aipa.invoice_id = aia.invoice_id

AND aipa.payment_num = apsa.payment_num

AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))

),0),0) c_amt_due_rem_orig

FROM AP_INVOICES AIA,

AP_PAYMENT_SCHEDULES APSA,

HZ_PARTIES HP,

HZ_PARTY_SITES SITES,

POZ_SUPPLIERS_V PSV,

POZ_SUPPLIER_SITES_V PSSV,

HR_OPERATING_UNITS HOU,

(SELECT P.BASE_CURRENCY_CODE,

C.PRECISION,

NVL (C.MINIMUM_ACCOUNTABLE_UNIT, 0)

MINIMUM_ACCOUNTABLE_UNIT,

C.DESCRIPTION,

P.ORG_ID

FROM AP_SYSTEM_PARAMETERS_ALL P, FND_CURRENCIES_VL C

WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE) APSS

WHERE APSA.INVOICE_ID = AIA.INVOICE_ID

AND APSA.ORG_ID = AIA.ORG_ID

AND HP.PARTY_ID = AIA.PARTY_ID

AND PSV.VENDOR_ID(+) = AIA.VENDOR_ID

AND PSSV.VENDOR_SITE_ID(+) = AIA.VENDOR_SITE_ID

AND PSSV.VENDOR_ID(+) = AIA.VENDOR_ID

AND APSS.ORG_ID = AIA.ORG_ID

AND HOU.ORGANIZATION_ID = AIA.ORG_ID

AND SITES.PARTY_ID = HP.PARTY_ID

AND aia.invoice_type_lookup_code = 'PREPAYMENT'

AND aia.PARTY_SITE_ID = sites.PARTY_SITE_ID

AND aia.wfapproval_status IN

('MANUALLY APPROVED', 'NOT REQUIRED', 'WFAPPROVED', 'AVAILABLE')

AND ( (AIA.CANCELLED_DATE IS NOT NULL

AND TRUNC (AIA.CANCELLED_DATE) > :P_TO_DATE)

OR (AIA.CANCELLED_DATE IS NULL))

AND EXISTS (SELECT '1'

FROM AP_INVOICE_DISTRIBUTIONS

WHERE INVOICE_ID = AIA.INVOICE_ID

AND ACCOUNTING_DATE <= :P_TO_DATE

AND NVL(POSTED_FLAG,'N') = 'Y'

)

AND UPPER (HOU.NAME) = UPPER (NVL (:P_ORG_NAME, HOU.NAME))

AND ((psv.VENDOR_TYPE_LOOKUP_CODE IN (:P_Supplier_type)) OR ('All' IN ('All'||:P_Supplier_type)))

AND LTRIM(RTRIM(HP.party_name)) = LTRIM(RTRIM(NVL (:P_FROM_VENDOR_NAME, HP.party_name)))

AND ((PSSV.VENDOR_SITE_CODE IN (:P_VENDOR_SITE)) OR ('All' IN (:P_VENDOR_SITE||'All')))

AND NOT EXISTS (SELECT '1'

FROM ap_invoice_payments_all aipa, ap_checks_all aca

WHERE aipa.invoice_id = aia.invoice_id

AND aca.check_id = aipa.check_id

AND aipa.payment_num = apsa.payment_num

--AND aca.status_lookup_code <> 'VOIDED'

AND ( (aca.VOID_DATE IS NOT NULL

AND TRUNC (aca.VOID_DATE) > :P_TO_DATE)

OR (aca.VOID_DATE IS NULL))

AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))

)

UNION

SELECT '2' typ,AIA.ORG_ID,

HOU.NAME ORG_NAME,

NVL (PSV.VENDOR_NAME, HP.PARTY_NAME) VENDOR_NAME,

NVL (PSV.SEGMENT1, PARTY_NUMBER) VENDOR_NUMBER,

PSV.VENDOR_ID VENDOR_ID,

NVL (PSSV.VENDOR_SITE_CODE, SITES.PARTY_SITE_NAME)

VENDOR_SITE_CODE,

AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,

TO_CHAR (APSA.DUE_DATE, 'DD-MON-RRRR') DUE_DATE,

(TO_NUMBER (

TRUNC (NVL (AIA.INVOICE_DATE, :P_TO_DATE))

- TRUNC (:P_TO_DATE))

* -1)

DAY_OVER_DUE,

aia.Payment_CURRENCY_CODE,

AIA.INVOICE_NUM INVOICE_NUMBER,

TO_CHAR (AIA.INVOICE_DATE, 'DD-MON-RRRR') INVOICE_DATE,

TO_CHAR (AIA.INVOICE_DATE, 'YYYYMMDD') INV_DATE,

NVL(DECODE(NVL(gross_Amount,0) * NVL (aia.exchange_rate, 1),0, --commented on 17NOV2022

NVL((SELECT SUM((apid.amount) * NVL (aia.exchange_rate, 1))

FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all apid,

ap_payment_schedules_all apsa1

WHERE ail.invoice_id = aia.invoice_id

AND apid.invoice_id = aia.invoice_id

AND apid.invoice_line_number = ail.line_number

AND apsa1.invoice_id = aia.invoice_id

AND apsa1.payment_num = apsa.payment_num

AND ail.org_id = aia.org_id

AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')

AND apid.accounting_date <= :P_TO_DATE),0),NVL(gross_Amount,0) * NVL (aia.exchange_rate, 1))

NVL((SELECT SUM((aipa.amount + NVL(aipa.discount_taken,0)) * NVL (aia.exchange_rate, 1)) FROM ap_invoice_payments_all aipa

WHERE aipa.invoice_id = aia.invoice_id

AND aipa.payment_num = apsa.payment_num

AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))

),0),0) c_amt_due_remaining,

----

APSA.PAYMENT_NUM INSTALLMENT_NUMBER,

DECODE (AP_INVOICES_UTILITY_PKG.

get_approval_status (aia.invoice_id,

aia.invoice_amount,

aia.payment_status_flag,

aia.invoice_type_lookup_code),

'PERMANENT', 'Permanant',

'Temporary')

Prepayment_Type,

TO_CHAR (AIA.TERMS_DATE, 'DD-MON-RRRR') TERMS_DATE,

NVL(DECODE(NVL(gross_Amount,0) ,0,

NVL((SELECT SUM((apid.amount) )

FROM ap_invoice_lines_all ail,

ap_invoice_distributions_all apid,

ap_payment_schedules_all apsa1

WHERE ail.invoice_id = aia.invoice_id

AND apid.invoice_id = aia.invoice_id

AND apid.invoice_line_number = ail.line_number

AND apsa1.invoice_id = aia.invoice_id

AND apsa1.payment_num = apsa.payment_num

AND ail.org_id = aia.org_id

AND apid.LINE_TYPE_LOOKUP_CODE IN ('ITEM','ACCRUAL')

AND apid.accounting_date <= :P_TO_DATE

),0),NVL(gross_Amount,0)) -

NVL((SELECT SUM((aipa.amount + NVL(aipa.discount_taken,0)) ) FROM ap_invoice_payments_all aipa

WHERE aipa.invoice_id = aia.invoice_id

AND aipa.payment_num = apsa.payment_num

AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))

),0),0) c_amt_due_rem_orig

FROM AP_INVOICES AIA,

AP_PAYMENT_SCHEDULES APSA,

HZ_PARTIES HP,

HZ_PARTY_SITES SITES,

POZ_SUPPLIERS_V PSV,

POZ_SUPPLIER_SITES_V PSSV,

HR_OPERATING_UNITS HOU,

(SELECT P.BASE_CURRENCY_CODE,

C.PRECISION,

NVL (C.MINIMUM_ACCOUNTABLE_UNIT, 0)

MINIMUM_ACCOUNTABLE_UNIT,

C.DESCRIPTION,

P.ORG_ID

FROM AP_SYSTEM_PARAMETERS_ALL P, FND_CURRENCIES_VL C

WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE) APSS

WHERE APSA.INVOICE_ID = AIA.INVOICE_ID

AND APSA.ORG_ID = AIA.ORG_ID

AND HP.PARTY_ID = AIA.PARTY_ID

AND PSV.VENDOR_ID(+) = AIA.VENDOR_ID

AND PSSV.VENDOR_SITE_ID(+) = AIA.VENDOR_SITE_ID

AND PSSV.VENDOR_ID(+) = AIA.VENDOR_ID

AND APSS.ORG_ID = AIA.ORG_ID

AND HOU.ORGANIZATION_ID = AIA.ORG_ID

AND SITES.PARTY_ID = HP.PARTY_ID

AND aia.invoice_type_lookup_code = 'PREPAYMENT'

AND aia.PARTY_SITE_ID = sites.PARTY_SITE_ID

AND aia.wfapproval_status IN

('MANUALLY APPROVED', 'NOT REQUIRED', 'WFAPPROVED', 'AVAILABLE')

AND ( (AIA.CANCELLED_DATE IS NOT NULL

AND TRUNC (AIA.CANCELLED_DATE) > :P_TO_DATE)

OR (AIA.CANCELLED_DATE IS NULL))

AND EXISTS (SELECT '1'

FROM AP_INVOICE_DISTRIBUTIONS

WHERE INVOICE_ID = AIA.INVOICE_ID

AND ACCOUNTING_DATE <= :P_TO_DATE

AND NVL(POSTED_FLAG,'N') = 'Y'

)

AND UPPER (HOU.NAME) = UPPER (NVL (:P_ORG_NAME, HOU.NAME))

AND ((psv.VENDOR_TYPE_LOOKUP_CODE IN (:P_Supplier_type)) OR ('All' IN ('All'||:P_Supplier_type)))

AND LTRIM(RTRIM(HP.party_name)) = LTRIM(RTRIM(NVL (:P_FROM_VENDOR_NAME, HP.party_name)))

AND ((PSSV.VENDOR_SITE_CODE IN (:P_VENDOR_SITE)) OR ('All' IN (:P_VENDOR_SITE||'All')))

AND EXISTS (SELECT '1'

FROM ap_invoice_payments_all aipa, ap_checks_all aca

WHERE aipa.invoice_id = aia.invoice_id

AND aca.check_id = aipa.check_id

AND aipa.payment_num = apsa.payment_num

--AND aca.status_lookup_code <> 'VOIDED'

AND ( (aca.VOID_DATE IS NOT NULL

AND TRUNC (aca.VOID_DATE) > :P_TO_DATE)

OR (aca.VOID_DATE IS NULL))

AND aipa.accounting_date <= NVL (:p_to_date,TRUNC (aipa.accounting_date))

) ) Z

WHERE NVL(C_AMT_DUE_REMAINING,0) != 0

ORDER BY VENDOR_NAME, INV_DATE, INSTALLMENT_NUMBER, DAY_OVER_DUE

Pages

AP Invoice Query

AP Invoice

Invoice Query

XLA to GL Link

XLA to GL Table Links

Subledger Tables

AP Supplier Aging Query

Supplier Aging

Tables used in Supplier Aging.

Oracle Fusion Supplier Available Prepayment Queries

Supplier Available Prepayment

Detail Query