VIEW: APPS.OKL_SEC_INVESTOR_DTLS_UV

Source

View Text - Preformatted

SELECT distinct chra.id khr_id, chrb.contract_number investor_agrmnt_number, sts.meaning investor_agrmnt_status, fndt.meaning investor_agrmnt_type, fnds.meaning strm_type_subclass, fndp.meaning strm_type_purpose, pol.pool_number pool_number, pol.display_in_lease_center FROM OKC_K_HEADERS_ALL_B CHRA,OKC_K_HEADERS_ALL_B CHRB,OKL_K_HEADERS KHR,OKC_STATUSES_TL STS,FND_LOOKUPS FNDT,FND_LOOKUPS FNDS,FND_LOOKUPS FNDP,OKL_POOLS POL,OKL_POOL_CONTENTS POC,OKL_STRM_TYPE_B STY WHERE chra.id= poc.khr_id and poc.sty_id = sty.id and sty.stream_type_subclass in ('RENT','RESIDUAL','LOAN_PAYMENT') AND fnds.lookup_type = 'OKL_STREAM_TYPE_SUBCLASS' AND fnds.lookup_code = sty.stream_type_subclass and pol.id = poc.pol_id and pol.khr_id =chrb.id and chrb.scs_code = 'INVESTOR' and chrb.sts_code = sts.code and sts.LANGUAGE= USERENV('LANG') and khr.id = chrb.id and fndt.lookup_type = 'OKL_SECURITIZATION_TYPE' and fndt.lookup_code = decode(khr.securitization_type,'SALE','SECURITIZATION','LOAN','SYNDICATION','SECURITIZATION','SECURITIZATION','SYNDICATION','SYNDICATION') and fndp.lookup_type = 'OKL_STREAM_TYPE_PURPOSE' AND fndp.lookup_code = sty.stream_type_purpose UNION ALL SELECT distinct poc.khr_id khr_id, to_char(null) investor_agrmnt_number, to_char(null) investor_agrmnt_status, to_char(null) investor_agrmnt_type, fnds.meaning strm_type_subclass, fndp.meaning strm_type_purpose, pol.pool_number pool_number, pol.display_in_lease_center FROM fnd_lookups fnds, fnd_lookups fndp, okl_pools pol, okl_pool_contents poc, okl_strm_type_b sty WHERE pol.id = poc.pol_id and poc.sty_id = sty.id and sty.stream_type_subclass in ('RENT','RESIDUAL','LOAN_PAYMENT') AND fnds.lookup_type = 'OKL_STREAM_TYPE_SUBCLASS' AND fnds.lookup_code = sty.stream_type_subclass AND fndp.lookup_type = 'OKL_STREAM_TYPE_PURPOSE' AND fndp.lookup_code = sty.stream_type_purpose and pol.khr_id is null

View Text - HTML Formatted

SELECT DISTINCT CHRA.ID KHR_ID
, CHRB.CONTRACT_NUMBER INVESTOR_AGRMNT_NUMBER
, STS.MEANING INVESTOR_AGRMNT_STATUS
, FNDT.MEANING INVESTOR_AGRMNT_TYPE
, FNDS.MEANING STRM_TYPE_SUBCLASS
, FNDP.MEANING STRM_TYPE_PURPOSE
, POL.POOL_NUMBER POOL_NUMBER
, POL.DISPLAY_IN_LEASE_CENTER
FROM OKC_K_HEADERS_ALL_B CHRA
, OKC_K_HEADERS_ALL_B CHRB
, OKL_K_HEADERS KHR
, OKC_STATUSES_TL STS
, FND_LOOKUPS FNDT
, FND_LOOKUPS FNDS
, FND_LOOKUPS FNDP
, OKL_POOLS POL
, OKL_POOL_CONTENTS POC
, OKL_STRM_TYPE_B STY
WHERE CHRA.ID= POC.KHR_ID
AND POC.STY_ID = STY.ID
AND STY.STREAM_TYPE_SUBCLASS IN ('RENT'
, 'RESIDUAL'
, 'LOAN_PAYMENT')
AND FNDS.LOOKUP_TYPE = 'OKL_STREAM_TYPE_SUBCLASS'
AND FNDS.LOOKUP_CODE = STY.STREAM_TYPE_SUBCLASS
AND POL.ID = POC.POL_ID
AND POL.KHR_ID =CHRB.ID
AND CHRB.SCS_CODE = 'INVESTOR'
AND CHRB.STS_CODE = STS.CODE
AND STS.LANGUAGE= USERENV('LANG')
AND KHR.ID = CHRB.ID
AND FNDT.LOOKUP_TYPE = 'OKL_SECURITIZATION_TYPE'
AND FNDT.LOOKUP_CODE = DECODE(KHR.SECURITIZATION_TYPE
, 'SALE'
, 'SECURITIZATION'
, 'LOAN'
, 'SYNDICATION'
, 'SECURITIZATION'
, 'SECURITIZATION'
, 'SYNDICATION'
, 'SYNDICATION')
AND FNDP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND FNDP.LOOKUP_CODE = STY.STREAM_TYPE_PURPOSE UNION ALL SELECT DISTINCT POC.KHR_ID KHR_ID
, TO_CHAR(NULL) INVESTOR_AGRMNT_NUMBER
, TO_CHAR(NULL) INVESTOR_AGRMNT_STATUS
, TO_CHAR(NULL) INVESTOR_AGRMNT_TYPE
, FNDS.MEANING STRM_TYPE_SUBCLASS
, FNDP.MEANING STRM_TYPE_PURPOSE
, POL.POOL_NUMBER POOL_NUMBER
, POL.DISPLAY_IN_LEASE_CENTER
FROM FND_LOOKUPS FNDS
, FND_LOOKUPS FNDP
, OKL_POOLS POL
, OKL_POOL_CONTENTS POC
, OKL_STRM_TYPE_B STY
WHERE POL.ID = POC.POL_ID
AND POC.STY_ID = STY.ID
AND STY.STREAM_TYPE_SUBCLASS IN ('RENT'
, 'RESIDUAL'
, 'LOAN_PAYMENT')
AND FNDS.LOOKUP_TYPE = 'OKL_STREAM_TYPE_SUBCLASS'
AND FNDS.LOOKUP_CODE = STY.STREAM_TYPE_SUBCLASS
AND FNDP.LOOKUP_TYPE = 'OKL_STREAM_TYPE_PURPOSE'
AND FNDP.LOOKUP_CODE = STY.STREAM_TYPE_PURPOSE
AND POL.KHR_ID IS NULL