VIEW: APPS.OKL_CURE_OFFSET_CONTRACTS_UV

Source

View Text - Preformatted

SELECT chr.contract_number contract_number, cam.chr_id chr_id, chr.currency_code currency_code, pty.object1_id1 vendor_id, sum(cam.negotiated_amount - nvl(cam.received_amount,0)) cure_amount_due FROM OKL_K_HEADERS KHR,OKC_K_HEADERS_B PROG,OKL_CURE_AMOUNTS_ALL CAM,OKC_K_PARTY_ROLES_B PTY,OKX_VENDORS_V VND,OKC_K_HEADERS_B CHR WHERE chr.id = cam.chr_id and khr.id = chr.id AND khr.khr_id = prog.id AND prog.scs_code = 'PROGRAM' AND chr.scs_code in ('LEASE','LOAN') AND prog.id = pty.chr_id AND pty.rle_code = 'OKL_VENDOR' AND pty.object1_id1 = to_char(vnd.id1) AND pty.object1_id2 = to_char(vnd.id2) and cam.status = 'CURESINPROGRESS' and nvl(cam.negotiated_amount,0) > 0 group by cam.chr_id,chr.contract_number, pty.object1_id1, chr.currency_code having sum(cam.negotiated_amount - nvl(cam.received_amount,0)) >0

View Text - HTML Formatted

SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CAM.CHR_ID CHR_ID
, CHR.CURRENCY_CODE CURRENCY_CODE
, PTY.OBJECT1_ID1 VENDOR_ID
, SUM(CAM.NEGOTIATED_AMOUNT - NVL(CAM.RECEIVED_AMOUNT
, 0)) CURE_AMOUNT_DUE
FROM OKL_K_HEADERS KHR
, OKC_K_HEADERS_B PROG
, OKL_CURE_AMOUNTS_ALL CAM
, OKC_K_PARTY_ROLES_B PTY
, OKX_VENDORS_V VND
, OKC_K_HEADERS_B CHR
WHERE CHR.ID = CAM.CHR_ID
AND KHR.ID = CHR.ID
AND KHR.KHR_ID = PROG.ID
AND PROG.SCS_CODE = 'PROGRAM'
AND CHR.SCS_CODE IN ('LEASE'
, 'LOAN')
AND PROG.ID = PTY.CHR_ID
AND PTY.RLE_CODE = 'OKL_VENDOR'
AND PTY.OBJECT1_ID1 = TO_CHAR(VND.ID1)
AND PTY.OBJECT1_ID2 = TO_CHAR(VND.ID2)
AND CAM.STATUS = 'CURESINPROGRESS'
AND NVL(CAM.NEGOTIATED_AMOUNT
, 0) > 0 GROUP BY CAM.CHR_ID
, CHR.CONTRACT_NUMBER
, PTY.OBJECT1_ID1
, CHR.CURRENCY_CODE HAVING SUM(CAM.NEGOTIATED_AMOUNT - NVL(CAM.RECEIVED_AMOUNT
, 0)) >0