VIEW: APPS.CE_AR_FC_INVOICES_V

Source

View Text - Preformatted

SELECT APS.TRX_NUMBER, /* 1 */ APS.ACCTD_AMOUNT_DUE_REMAINING, /* 2 */ APS.AMOUNT_DUE_REMAINING, /* 3 */ NVL(APS.AMOUNT_IN_DISPUTE,0), /* 4 */ APS.INVOICE_CURRENCY_CODE, /* 5 */ APS.EXCHANGE_RATE, /* 6 */ APS.DUE_DATE, /* 7 */ CP_CLASS.PROFILE_CLASS_ID, /* 8 */ APS.CUSTOMER_TRX_ID, /* 9 */ APS.ORG_ID, /* 10 */ APS.CUSTOMER_ID, /* 11 */ APS.CUSTOMER_SITE_USE_ID, /* 12 */ APS.TRX_DATE, /* 13 */ CP_CLASS.NAME, /* 14 */ 'AR' || APS.PAYMENT_SCHEDULE_ID /* 15 */ FROM AR_PAYMENT_SCHEDULES_ALL APS, HZ_CUSTOMER_PROFILES CP_SITE, HZ_CUST_PROFILE_CLASSES CP_CLASS WHERE APS.CUSTOMER_SITE_USE_ID = CP_SITE.SITE_USE_ID AND APS.STATUS = 'OP' AND APS.CLASS NOT IN ('PMT','GUAR') AND CP_CLASS.PROFILE_CLASS_ID = CP_SITE.PROFILE_CLASS_ID AND AR_PUBLIC_UTILS.Check_Prepay_Transaction (APS.CUSTOMER_TRX_ID) = 'N' UNION SELECT APS.TRX_NUMBER, /* 1 */ APS.ACCTD_AMOUNT_DUE_REMAINING, /* 2 */ APS.AMOUNT_DUE_REMAINING, /* 3 */ NVL(APS.AMOUNT_IN_DISPUTE,0), /* 4 */ APS.INVOICE_CURRENCY_CODE, /* 5 */ APS.EXCHANGE_RATE, /* 6 */ APS.DUE_DATE, /* 7 */ CP_CLASS.PROFILE_CLASS_ID, /* 8 */ APS.CUSTOMER_TRX_ID, /* 9 */ APS.ORG_ID, /* 10 */ APS.CUSTOMER_ID, /* 11 */ APS.CUSTOMER_SITE_USE_ID, /* 12 */ APS.TRX_DATE, /* 13 */ CP_CLASS.NAME, /* 14 */ 'AR' || APS.PAYMENT_SCHEDULE_ID /* 15 */ FROM AR_PAYMENT_SCHEDULES_ALL APS, HZ_CUSTOMER_PROFILES CP_CUST, HZ_CUST_PROFILE_CLASSES CP_CLASS WHERE APS.CUSTOMER_ID = CP_CUST.CUST_ACCOUNT_ID AND APS.STATUS = 'OP' AND APS.CLASS NOT IN ('PMT','GUAR') AND CP_CLASS.PROFILE_CLASS_ID = CP_CUST.PROFILE_CLASS_ID AND CP_CUST.SITE_USE_ID IS NULL AND Not Exists ( select 'x' from ar_payment_schedules_all arps, hz_customer_profiles hzcp where arps.CUSTOMER_TRX_ID = aps.CUSTOMER_TRX_ID and arps.due_date = aps.due_date and arps.CUSTOMER_SITE_USE_ID = hzcp.SITE_USE_ID ) AND AR_PUBLIC_UTILS.Check_Prepay_Transaction (APS.CUSTOMER_TRX_ID) = 'N'

View Text - HTML Formatted

SELECT APS.TRX_NUMBER
, /* 1 */ APS.ACCTD_AMOUNT_DUE_REMAINING
, /* 2 */ APS.AMOUNT_DUE_REMAINING
, /* 3 */ NVL(APS.AMOUNT_IN_DISPUTE
, 0)
, /* 4 */ APS.INVOICE_CURRENCY_CODE
, /* 5 */ APS.EXCHANGE_RATE
, /* 6 */ APS.DUE_DATE
, /* 7 */ CP_CLASS.PROFILE_CLASS_ID
, /* 8 */ APS.CUSTOMER_TRX_ID
, /* 9 */ APS.ORG_ID
, /* 10 */ APS.CUSTOMER_ID
, /* 11 */ APS.CUSTOMER_SITE_USE_ID
, /* 12 */ APS.TRX_DATE
, /* 13 */ CP_CLASS.NAME
, /* 14 */ 'AR' || APS.PAYMENT_SCHEDULE_ID /* 15 */
FROM AR_PAYMENT_SCHEDULES_ALL APS
, HZ_CUSTOMER_PROFILES CP_SITE
, HZ_CUST_PROFILE_CLASSES CP_CLASS
WHERE APS.CUSTOMER_SITE_USE_ID = CP_SITE.SITE_USE_ID
AND APS.STATUS = 'OP'
AND APS.CLASS NOT IN ('PMT'
, 'GUAR')
AND CP_CLASS.PROFILE_CLASS_ID = CP_SITE.PROFILE_CLASS_ID
AND AR_PUBLIC_UTILS.CHECK_PREPAY_TRANSACTION (APS.CUSTOMER_TRX_ID) = 'N' UNION SELECT APS.TRX_NUMBER
, /* 1 */ APS.ACCTD_AMOUNT_DUE_REMAINING
, /* 2 */ APS.AMOUNT_DUE_REMAINING
, /* 3 */ NVL(APS.AMOUNT_IN_DISPUTE
, 0)
, /* 4 */ APS.INVOICE_CURRENCY_CODE
, /* 5 */ APS.EXCHANGE_RATE
, /* 6 */ APS.DUE_DATE
, /* 7 */ CP_CLASS.PROFILE_CLASS_ID
, /* 8 */ APS.CUSTOMER_TRX_ID
, /* 9 */ APS.ORG_ID
, /* 10 */ APS.CUSTOMER_ID
, /* 11 */ APS.CUSTOMER_SITE_USE_ID
, /* 12 */ APS.TRX_DATE
, /* 13 */ CP_CLASS.NAME
, /* 14 */ 'AR' || APS.PAYMENT_SCHEDULE_ID /* 15 */
FROM AR_PAYMENT_SCHEDULES_ALL APS
, HZ_CUSTOMER_PROFILES CP_CUST
, HZ_CUST_PROFILE_CLASSES CP_CLASS
WHERE APS.CUSTOMER_ID = CP_CUST.CUST_ACCOUNT_ID
AND APS.STATUS = 'OP'
AND APS.CLASS NOT IN ('PMT'
, 'GUAR')
AND CP_CLASS.PROFILE_CLASS_ID = CP_CUST.PROFILE_CLASS_ID
AND CP_CUST.SITE_USE_ID IS NULL
AND NOT EXISTS ( SELECT 'X'
FROM AR_PAYMENT_SCHEDULES_ALL ARPS
, HZ_CUSTOMER_PROFILES HZCP
WHERE ARPS.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND ARPS.DUE_DATE = APS.DUE_DATE
AND ARPS.CUSTOMER_SITE_USE_ID = HZCP.SITE_USE_ID )
AND AR_PUBLIC_UTILS.CHECK_PREPAY_TRANSACTION (APS.CUSTOMER_TRX_ID) = 'N'