View: OKL_FEE_SERVICE_STRMS_UV

Product:	OKL - Lease and Finance Management
Description:	View for list of fee and services in a contract
Implementation/DBA Data:	APPS.OKL_FEE_SERVICE_STRMS_UV

View Text

SELECT DISTINCT CLEB.DNZ_CHR_ID KHR_ID
, CLEB.LSE_ID LSE_ID
, STYV.NAME SERV_INST_NAME
, STYV.DESCRIPTION SERV_INST_DESC
FROM OKL_STRMTYP_SOURCE_V STYV
, OKC_K_ITEMS CIM
, OKC_K_LINES_B CLEB
, OKC_STATUSES_B STSB
, OKL_K_LINES KLE
, FND_LOOKUPS FNDV
WHERE STYV.ID1 = CIM.OBJECT1_ID1
AND TO_CHAR(STYV.ID2) = TO_CHAR(CIM.OBJECT1_ID2)
AND CIM.JTOT_OBJECT1_CODE = 'OKL_STRMTYP'
AND CIM.CLE_ID = CLEB.ID
AND CIM.DNZ_CHR_ID =CLEB.DNZ_CHR_ID
AND STSB.CODE = CLEB.STS_CODE
AND STSB.STE_CODE NOT IN ('HOLD'
, 'EXPIRED'
, 'TERMINATED'
, 'CANCELLED')
AND CLEB.ID = KLE.ID
AND FNDV.LOOKUP_TYPE = 'OKL_FEE_TYPES'
AND FNDV.LOOKUP_CODE = KLE.FEE_TYPE UNION SELECT DISTINCT KLINES.DNZ_CHR_ID KHR_ID
, KLINES.LSE_ID LSE_ID
, SYSITMS.NAME SERVICE_INST_NAME
, SYSITMS.DESCRIPTION SERVICE_INST_DESC
FROM OKX_SYSTEM_ITEMS_V SYSITMS
, OKC_K_ITEMS KITMS
, OKC_LINE_STYLES_B LSE
, OKC_K_LINES_B KLINES
, OKC_STATUSES_B STS
WHERE SYSITMS.ID1 = KITMS.OBJECT1_ID1
AND TO_CHAR(SYSITMS.ID2) = TO_CHAR(KITMS.OBJECT1_ID2)
AND LSE.ID = KLINES.LSE_ID
AND LSE.LTY_CODE IN ('FEE'
, 'SOLD_SERVICE')
AND KITMS.JTOT_OBJECT1_CODE = 'OKX_SERVICE'
AND KITMS.CLE_ID = KLINES.ID
AND STS.CODE = KLINES.STS_CODE
AND STS.STE_CODE NOT IN ('HOLD'
, 'EXPIRED'
, 'TERMINATED'
, 'CANCELLED')
AND NOT EXISTS (SELECT 'Y'
FROM OKC_K_REL_OBJS REL
WHERE REL.CLE_ID = KLINES.ID
AND REL.RTY_CODE = 'OKLSRV'
AND REL.JTOT_OBJECT1_CODE = 'OKL_SERVICE_LINE')

Columns