View: CE_AP_FC_DUE_INVOICES_V

Product:	CE - Cash Management
Description:	Forecasting source - AP invoices based on due dates
Implementation/DBA Data:	APPS.CE_AP_FC_DUE_INVOICES_V

View Text

SELECT APS.INVOICE_ID
, /* 1 */ APS.PAYMENT_NUM
, /* 2 */ NVL(APS.AMOUNT_REMAINING
, 0)
, /*3*/ AP_UTILITIES_PKG.AP_ROUND_CURRENCY(NVL(APS.AMOUNT_REMAINING
, 0) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE)
, /*4*/ AI.PAYMENT_CURRENCY_CODE
, /* 5 */ NVL(APS.PAYMENT_PRIORITY
, 99)
, /* 6 */ NVL(PV.VENDOR_TYPE_LOOKUP_CODE
, '-1')
, /* 7 */ NVL(AI.PAY_GROUP_LOOKUP_CODE
, '-1')
, /* 8 */ APS.DUE_DATE
, /* 9 */ APS.ORG_ID
, /* 10 */ AI.PROJECT_ID
, /* 11 */ NVL(APS.HOLD_FLAG
, 'N')
, /* 12 */ AI.INVOICE_NUM
, /* 13 */ (APS.INVOICE_ID ||'X' ||APS.PAYMENT_NUM) /* 14 */
FROM AP_PAYMENT_SCHEDULES_ALL APS
, AP_INVOICES_ALL AI
, AP_SUPPLIER_SITES_ALL PVS
, AP_SUPPLIERS PV
, AP_SYSTEM_PARAMETERS_ALL ASP
WHERE APS.INVOICE_ID = AI.INVOICE_ID
AND AI.ORG_ID = ASP.ORG_ID
AND AI.ORG_ID = PVS.ORG_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PVS.VENDOR_ID = PV.VENDOR_ID
AND APS.ORG_ID = AI.ORG_ID
AND APS.PAYMENT_STATUS_FLAG != 'Y'
AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT'
, 'PAYMENT REQUEST')
AND NOT EXISTS (SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID = AI.INVOICE_ID
AND MATCH_STATUS_FLAG != 'A')
AND EXISTS (SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID = AI.INVOICE_ID) UNION ALL SELECT APS.INVOICE_ID
, /* 1 */ APS.PAYMENT_NUM
, /* 2 */ NVL(APS.AMOUNT_REMAINING
, 0)
, /*3*/ AP_UTILITIES_PKG.AP_ROUND_CURRENCY(NVL(APS.AMOUNT_REMAINING
, 0) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE)
, /*4*/ AI.PAYMENT_CURRENCY_CODE
, /* 5 */ NVL(APS.PAYMENT_PRIORITY
, 99)
, /* 6 */ '-1'
, /* 7 */ NVL(AI.PAY_GROUP_LOOKUP_CODE
, '-1')
, /* 8 */ APS.DUE_DATE
, /* 9 */ APS.ORG_ID
, /* 10 */ AI.PROJECT_ID
, /* 11 */ NVL(APS.HOLD_FLAG
, 'N')
, /* 12 */ AI.INVOICE_NUM
, /* 13 */ (APS.INVOICE_ID ||'X' ||APS.PAYMENT_NUM) /* 14 */
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 AI.ORG_ID = ASP.ORG_ID
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.ORG_ID = AI.ORG_ID
AND APS.PAYMENT_STATUS_FLAG != 'Y'
AND AI.INVOICE_TYPE_LOOKUP_CODE ='PAYMENT REQUEST'
AND NOT EXISTS (SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID = AI.INVOICE_ID
AND MATCH_STATUS_FLAG != 'A' )
AND EXISTS (SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID = AI.INVOICE_ID )

Columns