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
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;
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;