VIEW: APPS.OKL_FEE_SERVICE_STRMS_UV

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')