VIEW: APPS.OKL_SEC_BUYBACK_STREAMS_UV

Source

View Text - Preformatted

SELECT pocb.khr_id khr_id, chrb.contract_number contract_number, chrb.currency_code currency_code, hzpb.party_name lessee, pocb.pol_id pol_id, polv.pool_number pool_number, polv.khr_id agreement_id, agrb.contract_number agreement_number, styv.stream_type_subclass sty_subclass_code, fnds.meaning sty_subclass, NVL(SUM(selb.amount), 0) streams_amount FROM OKL_POOL_CONTENTS POCB,OKC_K_HEADERS_ALL_B CHRB,OKC_K_PARTY_ROLES_B CPLB,HZ_PARTIES HZPB,OKL_POOLS POLV,OKL_STRM_TYPE_V STYV,OKL_STREAMS STMB,OKL_STRM_ELEMENTS SELB,FND_LOOKUPS FNDS,OKC_K_HEADERS_ALL_B AGRB WHERE pocb.status_code = 'ACTIVE' AND pocb.khr_id = chrb.id AND cplb.dnz_chr_id = chrb.id AND cplb.object1_id1 = hzpb.party_id AND cplb.object1_id2 = '#' AND cplb.rle_code = 'LESSEE' AND pocb.pol_id = polv.id AND polv.khr_id = agrb.id AND pocb.sty_id = styv.id AND pocb.stm_id = stmb.id AND selb.stm_id = stmb.id AND selb.DATE_BILLED IS NULL AND (selb.STREAM_ELEMENT_DATE > SYSDATE OR (styv.stream_type_subclass = 'RESIDUAL' and chrb.STS_CODE IN ('TERMINATED','EXPIRED') ) ) AND selb.STREAM_ELEMENT_DATE BETWEEN pocb.STREAMS_FROM_DATE AND NVL(pocb.STREAMS_TO_DATE,TO_DATE('1','j')+5300000) AND fnds.lookup_type = 'OKL_STREAM_TYPE_SUBCLASS' AND fnds.lookup_code = styv.stream_type_subclass GROUP BY pocb.khr_id,chrb.contract_number,chrb.currency_code,hzpb.party_name,pocb.pol_id,pool_number,polv.khr_id,agrb.contract_number,styv.stream_type_subclass,fnds.meaning ORDER BY contract_number,stream_type_subclass

View Text - HTML Formatted

SELECT POCB.KHR_ID KHR_ID
, CHRB.CONTRACT_NUMBER CONTRACT_NUMBER
, CHRB.CURRENCY_CODE CURRENCY_CODE
, HZPB.PARTY_NAME LESSEE
, POCB.POL_ID POL_ID
, POLV.POOL_NUMBER POOL_NUMBER
, POLV.KHR_ID AGREEMENT_ID
, AGRB.CONTRACT_NUMBER AGREEMENT_NUMBER
, STYV.STREAM_TYPE_SUBCLASS STY_SUBCLASS_CODE
, FNDS.MEANING STY_SUBCLASS
, NVL(SUM(SELB.AMOUNT)
, 0) STREAMS_AMOUNT
FROM OKL_POOL_CONTENTS POCB
, OKC_K_HEADERS_ALL_B CHRB
, OKC_K_PARTY_ROLES_B CPLB
, HZ_PARTIES HZPB
, OKL_POOLS POLV
, OKL_STRM_TYPE_V STYV
, OKL_STREAMS STMB
, OKL_STRM_ELEMENTS SELB
, FND_LOOKUPS FNDS
, OKC_K_HEADERS_ALL_B AGRB
WHERE POCB.STATUS_CODE = 'ACTIVE'
AND POCB.KHR_ID = CHRB.ID
AND CPLB.DNZ_CHR_ID = CHRB.ID
AND CPLB.OBJECT1_ID1 = HZPB.PARTY_ID
AND CPLB.OBJECT1_ID2 = '#'
AND CPLB.RLE_CODE = 'LESSEE'
AND POCB.POL_ID = POLV.ID
AND POLV.KHR_ID = AGRB.ID
AND POCB.STY_ID = STYV.ID
AND POCB.STM_ID = STMB.ID
AND SELB.STM_ID = STMB.ID
AND SELB.DATE_BILLED IS NULL
AND (SELB.STREAM_ELEMENT_DATE > SYSDATE OR (STYV.STREAM_TYPE_SUBCLASS = 'RESIDUAL'
AND CHRB.STS_CODE IN ('TERMINATED'
, 'EXPIRED') ) )
AND SELB.STREAM_ELEMENT_DATE BETWEEN POCB.STREAMS_FROM_DATE
AND NVL(POCB.STREAMS_TO_DATE
, TO_DATE('1'
, 'J')+5300000)
AND FNDS.LOOKUP_TYPE = 'OKL_STREAM_TYPE_SUBCLASS'
AND FNDS.LOOKUP_CODE = STYV.STREAM_TYPE_SUBCLASS GROUP BY POCB.KHR_ID
, CHRB.CONTRACT_NUMBER
, CHRB.CURRENCY_CODE
, HZPB.PARTY_NAME
, POCB.POL_ID
, POOL_NUMBER
, POLV.KHR_ID
, AGRB.CONTRACT_NUMBER
, STYV.STREAM_TYPE_SUBCLASS
, FNDS.MEANING ORDER BY CONTRACT_NUMBER
, STREAM_TYPE_SUBCLASS