[Home] [Help]
| Product: | ICX - Oracle iProcurement |
|---|---|
| Description: | Obsolete |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PS.CUSTOMER_ID
, RAC.CUSTOMER_NAME
, PS.INVOICE_CURRENCY_CODE
, SUM(DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, NULL
, TRUNC(SYSDATE) - PS.DUE_DATE)) DAYS_PAST_DUE
, TO_CHAR(SUM(PS.AMOUNT_DUE_ORIGINAL)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_DUE_ORIGINAL
, TO_CHAR(SUM(PS.AMOUNT_DUE_REMAINING)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_DUE_REMAINING
, TO_CHAR(SUM(PS.AMOUNT_LINE_ITEMS_ORIGINAL)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_LINE_ITEMS_ORIGINAL
, TO_CHAR(SUM(PS.TAX_ORIGINAL)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) TAX_ORIGINAL
, TO_CHAR(SUM(PS.FREIGHT_ORIGINAL)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) FREIGHT_ORIGINAL
, TO_CHAR(SUM(PS.AMOUNT_ADJUSTED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_ADJUSTED
, TO_CHAR(SUM(PS.AMOUNT_APPLIED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_APPLIED
, TO_CHAR(SUM(PS.AMOUNT_CREDITED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_CREDITED
, TO_CHAR(SUM(PS.DISCOUNT_TAKEN_EARNED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) DISCOUNT_TAKEN_EARNED
, SUM(PS.ACCTD_AMOUNT_DUE_REMAINING) ACCTD_AMOUNT_DUE_REMAINING
, SUM(PS.AMOUNT_LINE_ITEMS_REMAINING) AMOUNT_LINE_ITEMS_REMAINING
, SUM(PS.TAX_REMAINING) TAX_REMAINING
, SUM(PS.FREIGHT_REMAINING) FREIGHT_REMAINING
, SUM(PS.AMOUNT_ADJUSTED_PENDING) AMOUNT_ADJUSTED_PENDING
, SUM(PS.RECEIVABLES_CHARGES_CHARGED) RECEIVABLES_CHARGES_CHARGED
, SUM(PS.RECEIVABLES_CHARGES_REMAINING) RECEIVABLES_CHARGES_REMAINING
, SUM(PS.AMOUNT_IN_DISPUTE) AMOUNT_IN_DISPUTE
, MIN(PS.DISPUTE_DATE) DISPUTE_DATE
, SUM(PS.DISCOUNT_TAKEN_UNEARNED) DISCOUNT_TAKEN_UNEARNED
FROM AR_PAYMENT_SCHEDULES PS
, RA_CUSTOMERS RAC
, RA_SITE_USES SU
, RA_ADDRESSES ADDR
, RA_CUSTOMER_TRX CT
, AR_LOOKUPS AL_CLASS
, ORG_FREIGHT FRT
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE PS.CUSTOMER_ID = RAC.CUSTOMER_ID (+)
AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID (+)
AND PS.CLASS = AL_CLASS.LOOKUP_CODE
AND CT.SHIP_VIA = FRT.FREIGHT_CODE(+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(FRT.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)
AND AL_CLASS.LOOKUP_TYPE = 'INV/CM' GROUP BY PS.CUSTOMER_ID
, PS.INVOICE_CURRENCY_CODE
, RAC.CUSTOMER_NAME