View: CE_AP_FC_DISC_INVOICES_V

Product:	CE - Cash Management
Description:	Forecasting source - AP invoices with discounts
Implementation/DBA Data:	APPS.CE_AP_FC_DISC_INVOICES_V

View Text

SELECT APS.INVOICE_ID
, /* 1 */ APS.PAYMENT_NUM
, /* 2 */ APS.DUE_DATE
, /* 3 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 4 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 5 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AP_UTILITIES_PKG.AP_ROUND_CURRENCY(AI.AMOUNT_PAID * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY( (NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.DISCOUNT_DATE
, DUE_DATE)
, /* 6 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 7 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.SECOND_DISC_AMT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 8 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AP_UTILITIES_PKG.AP_ROUND_CURRENCY(AI.AMOUNT_PAID * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY( (NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.SECOND_DISC_AMT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.SECOND_DISCOUNT_DATE
, DUE_DATE)
, /* 9 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 10 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.THIRD_DISC_AMT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 11 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AP_UTILITIES_PKG.AP_ROUND_CURRENCY(AI.AMOUNT_PAID * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY( (NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.THIRD_DISC_AMT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.THIRD_DISCOUNT_DATE
, DUE_DATE)
, /* 12 */ AI.PAYMENT_CURRENCY_CODE
, /* 13 */ NVL(APS.PAYMENT_PRIORITY
, 99)
, /* 14 */ NVL(PV.VENDOR_TYPE_LOOKUP_CODE
, '-1')
, /* 15 */ NVL(AI.PAY_GROUP_LOOKUP_CODE
, '-1')
, /* 16 */ AI.ORG_ID
, /* 17 */ AI.PROJECT_ID
, /* 18 */ NVL(APS.HOLD_FLAG
, 'N')
, /* 19 */ AI.INVOICE_NUM /* 20 */ FROM AP_PAYMENT_SCHEDULES_ALL APS
, AP_INVOICES_ALL AI
, PO_VENDOR_SITES_ALL PVS
, PO_VENDORS PV
, AP_SYSTEM_PARAMETERS_ALL ASP WHERE APS.INVOICE_ID = AI.INVOICE_ID AND (ASP.ORG_ID = AI.ORG_ID OR ASP.ORG_ID IS NULL) AND (AI.ORG_ID = PVS.ORG_ID OR AI.ORG_ID IS NULL) AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND PVS.VENDOR_ID = PV.VENDOR_ID
AND AI.INVOICE_TYPE_LOOKUP_CODE != 'PAYMENT REQUEST' AND (APS.ORG_ID = AI.ORG_ID OR APS.ORG_ID IS NULL) AND APS.PAYMENT_NUM = DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, 1
, APS.PAYMENT_NUM) AND (APS.PAYMENT_STATUS_FLAG != 'Y' OR (APS.PAYMENT_STATUS_FLAG = 'Y' AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND AI.AMOUNT_PAID != 0)) UNION ALL SELECT APS.INVOICE_ID
, /* 1 */ APS.PAYMENT_NUM
, /* 2 */ APS.DUE_DATE
, /* 3 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 4 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 5 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AP_UTILITIES_PKG.AP_ROUND_CURRENCY(AI.AMOUNT_PAID * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY( (NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.DISCOUNT_DATE
, DUE_DATE)
, /* 6 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 7 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.SECOND_DISC_AMT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 8 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AP_UTILITIES_PKG.AP_ROUND_CURRENCY(AI.AMOUNT_PAID * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY( (NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.SECOND_DISC_AMT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.SECOND_DISCOUNT_DATE
, DUE_DATE)
, /* 9 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 10 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.THIRD_DISC_AMT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 11 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AP_UTILITIES_PKG.AP_ROUND_CURRENCY(AI.AMOUNT_PAID * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY( (NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.THIRD_DISC_AMT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.THIRD_DISCOUNT_DATE
, DUE_DATE)
, /* 12 */ AI.PAYMENT_CURRENCY_CODE
, /* 13 */ NVL(APS.PAYMENT_PRIORITY
, 99)
, /* 14 */ '-1'
, /* 15 */ NVL(AI.PAY_GROUP_LOOKUP_CODE
, '-1')
, /* 16 */ AI.ORG_ID
, /* 17 */ AI.PROJECT_ID
, /* 18 */ NVL(APS.HOLD_FLAG
, 'N')
, /* 19 */ AI.INVOICE_NUM /* 20 */
FROM AP_PAYMENT_SCHEDULES_ALL APS
, AP_INVOICES_ALL AI
, HZ_PARTIES HZP
, HZ_PARTY_SITES HZS
, AP_SYSTEM_PARAMETERS_ALL ASP
WHERE APS.INVOICE_ID = AI.INVOICE_ID
AND (ASP.ORG_ID = AI.ORG_ID OR ASP.ORG_ID IS NULL)
AND (APS.ORG_ID = AI.ORG_ID OR APS.ORG_ID IS NULL)
AND AI.PARTY_ID = HZP.PARTY_ID
AND AI.PARTY_SITE_ID = HZS.PARTY_SITE_ID
AND HZP.PARTY_ID = HZS.PARTY_ID
AND APS.PAYMENT_NUM = DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, 1
, APS.PAYMENT_NUM)
AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PAYMENT REQUEST'
AND (APS.PAYMENT_STATUS_FLAG != 'Y' OR (APS.PAYMENT_STATUS_FLAG = 'Y'
AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND AI.AMOUNT_PAID != 0))

Columns