View: OKL_CURE_REFUNDS_DTLS_UV

Product:	OKL - Lease and Finance Management
Description:	View for creating cure refunds
Implementation/DBA Data:	APPS.OKL_CURE_REFUNDS_DTLS_UV

View Text

SELECT CONTRACT_ID
, CONTRACT_NUMBER
, CURRENCY_CODE1
, VENDOR_NAME
, VENDOR_ID1
, VENDOR_SITE_CODE
, VENDOR_SITE_ID1
, ( SELECT NVL(SUM(A.AMOUNT_APPLIED)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PS1
, OKL_BPD_TLD_AR_LINES_V ST1
, AR_RECEIVABLE_APPLICATIONS_ALL A
, OKL_TXD_AR_LN_DTLS_B XLS1
, OKL_TXL_AR_INV_LNS_V TIL1
, OKL_TRX_AR_INVOICES_V TAI1
WHERE ST1.CUSTOMER_TRX_ID = PS1.CUSTOMER_TRX_ID
AND PS1.CLASS IN ('INV'
, 'CM')
AND A.APPLIED_PAYMENT_SCHEDULE_ID = PS1.PAYMENT_SCHEDULE_ID
AND ST1.KHR_ID = CONTRACT_ID
AND XLS1.ID = ST1.TLD_ID
AND TAI1.ID = TIL1.TAI_ID
AND TIL1.ID = XLS1.TIL_ID_DETAILS
AND TAI1.CPY_ID IS NOT NULL ) - ( SELECT NVL(SUM(B.DISBURSEMENT_AMOUNT)
, 0)
FROM OKL_CURE_REFUNDS B
WHERE B.CHR_ID = CONTRACT_ID ) REFUND
, ( SELECT NVL(SUM(PS2.AMOUNT_DUE_REMAINING)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PS2
, OKL_BPD_TLD_AR_LINES_V ST2
, OKC_K_HEADERS_V CHR2
, OKL_K_VENDOR_PROGRAMS_UV VKHR2
, OKL_TXD_AR_LN_DTLS_B XLS2
, OKL_TXL_AR_INV_LNS_V TIL2
, OKL_TRX_AR_INVOICES_V TAI2
WHERE ST2.CUSTOMER_TRX_ID = PS2.CUSTOMER_TRX_ID
AND PS2.CLASS IN ('INV'
, 'CM')
AND CHR2.ID = ST2.KHR_ID
AND VKHR2.CONTRACT_ID = CHR2.ID
AND XLS2.ID = ST2.TLD_ID
AND TAI2.ID = TIL2.TAI_ID
AND TIL2.ID = XLS2.TIL_ID_DETAILS
AND TAI2.CPY_ID IS NOT NULL
AND VKHR2.VENDOR_ID = VENDOR_ID1
AND CHR2.CURRENCY_CODE = CURRENCY_CODE1) VENDOR_CURE_DUE
, ( SELECT NVL(SUM(PS4.AMOUNT_DUE_REMAINING)
, 0)
FROM AR_PAYMENT_SCHEDULES_ALL PS4
, OKL_BPD_TLD_AR_LINES_V ST4
, OKC_K_HEADERS_V CHR4
, OKL_CURE_VENDOR_SUMRY_UV VKHR4
, OKL_TXD_AR_LN_DTLS_B XLS4
, OKL_TXL_AR_INV_LNS_V TIL4
, OKL_TRX_AR_INVOICES_V TAI4
, OKL_K_HEADERS KHR4
WHERE ST4.CUSTOMER_TRX_ID = PS4.CUSTOMER_TRX_ID
AND PS4.CLASS IN ('INV'
, 'CM')
AND CHR4.ID = ST4.KHR_ID
AND XLS4.ID = ST4.TLD_ID
AND KHR4.ID = CHR4.ID
AND VKHR4.PROGRAM_ID = KHR4.KHR_ID
AND TAI4.ID = TIL4.TAI_ID
AND TIL4.ID = XLS4.TIL_ID_DETAILS
AND TAI4.CPY_ID IS NOT NULL
AND VKHR4.VENDOR_SITE_ID = VENDOR_SITE_ID1
AND CHR4.CURRENCY_CODE = CURRENCY_CODE1) VENDOR_SITE_CURE_DUE
FROM (SELECT ST.KHR_ID CONTRACT_ID
, CN.CONTRACT_NUMBER
, CN.CURRENCY_CODE CURRENCY_CODE1
, VKHR.VENDOR_NAME
, VKHR.VENDOR_ID VENDOR_ID1
, VKHR.VENDOR_SITE_CODE
, VKHR.VENDOR_SITE_ID VENDOR_SITE_ID1
FROM OKL_TXD_AR_LN_DTLS_B XLS
, OKL_TXL_AR_INV_LNS_V TIL
, OKL_TRX_AR_INVOICES_V TAI
, OKC_K_HEADERS_B CN
, AR_PAYMENT_SCHEDULES_ALL PS
, AR_RECEIVABLE_APPLICATIONS_ALL ARAPP
, OKL_K_HEADERS CHR
, OKL_BPD_TLD_AR_LINES_V ST
, OKL_CURE_VENDOR_SUMRY_UV VKHR
WHERE ST.TLD_ID = XLS.ID
AND ST.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND PS.CLASS IN ('INV'
, 'CM')
AND ARAPP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND CN.ID = ST.KHR_ID(+)
AND TAI.ID = TIL.TAI_ID
AND TIL.ID = XLS.TIL_ID_DETAILS
AND TAI.CPY_ID IS NOT NULL
AND VKHR.PROGRAM_ID = CHR.KHR_ID
AND CHR.ID = CN.ID GROUP BY ST.KHR_ID
, CN.CONTRACT_NUMBER
, CN.CURRENCY_CODE
, VKHR.VENDOR_NAME
, VKHR.VENDOR_ID
, VKHR.VENDOR_SITE_CODE
, VKHR.VENDOR_SITE_ID)

Columns