VIEW: APPS.OKL_CURE_REFUNDS_DTLS_UV

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)