View: OKL_LA_COV_ASSET_UV

Product:	OKL - Leasing and Finance Management
Description:	OKL Service Covered Asset Line LOV View
Implementation/DBA Data:	APPS.OKL_LA_COV_ASSET_UV

View Text

SELECT OKL_FIN_L.ID ID1
, '#' ID2
, OKL_H.ID CHR_ID
, OKL_H.CONTRACT_NUMBER CONTRACT_NUMBER
, OKL_FIN_TL.NAME NAME
, OKL_FIN_TL.ITEM_DESCRIPTION DESCRIPTION
, OKL_SERV_L.ID SERV_TOP_LINE_ID
, SRV_COV_L.ID OKS_COV_LINE_ID
, INV_ITEM.SEGMENT1 ITEM_NUMBER
, INV_ITEM.DESCRIPTION ITEM_DESCRIPTION
, INV_ITEM.INVENTORY_ITEM_ID ITEM_ID
, INV_ITEM.SERIAL_NUMBER_CONTROL_CODE
, OKL_ITEM.NUMBER_OF_ITEMS LEASE_ITEM_QTY
, CS_PROD.QUANTITY SERVICE_ITEM_QTY
, SRV_COV_L.PRICE_NEGOTIATED
, CS_PROD.INSTANCE_ID SRV_PROD_INSTANCE_ID
FROM OKC_LINE_STYLES_B SERV_STYLE
, OKC_K_LINES_B OKL_SERV_L
, OKC_K_ITEMS OKL_ITEM
, OKC_LINE_STYLES_B ITEM_STYLE
, OKC_K_LINES_B OKL_ITEM_L
, OKC_LINE_STYLES_B FIN_STYLE
, OKC_K_LINES_TL OKL_FIN_TL
, OKC_K_LINES_B OKL_FIN_L
, OKC_K_HEADERS_B OKL_H
, OKC_STATUSES_B LINE_STATUS
, CSI_ITEM_INSTANCES CS_PROD
, OKC_K_LINES_B SRV_COV_L
, OKC_K_ITEMS SRV_ITEM
, OKC_K_LINES_B SRV_TOP_L
, OKC_K_REL_OBJS REL
, MTL_SYSTEM_ITEMS_B INV_ITEM
WHERE OKL_H.ID = OKL_FIN_L.DNZ_CHR_ID
AND OKL_FIN_L.LSE_ID = FIN_STYLE.ID
AND FIN_STYLE.LTY_CODE = 'FREE_FORM1'
AND OKL_FIN_L.ID = OKL_FIN_TL.ID
AND OKL_FIN_L.STS_CODE = LINE_STATUS.CODE
AND LINE_STATUS.STE_CODE NOT IN ('EXPIRED'
, 'TERMINATED'
, 'CANCELLED'
, 'HOLD')
AND OKL_FIN_TL.LANGUAGE = USERENV('LANG')
AND OKL_ITEM_L.CLE_ID = OKL_FIN_L.ID
AND OKL_ITEM_L.DNZ_CHR_ID = OKL_FIN_L.DNZ_CHR_ID
AND OKL_ITEM_L.LSE_ID = ITEM_STYLE.ID
AND ITEM_STYLE.LTY_CODE = 'ITEM'
AND OKL_ITEM.CLE_ID = OKL_ITEM_L.ID
AND OKL_ITEM.DNZ_CHR_ID = OKL_ITEM_L.DNZ_CHR_ID
AND OKL_ITEM.JTOT_OBJECT1_CODE = 'OKX_SYSITEM'
AND OKL_SERV_L.DNZ_CHR_ID = OKL_FIN_L.DNZ_CHR_ID
AND OKL_SERV_L.LSE_ID = SERV_STYLE.ID
AND SERV_STYLE.LTY_CODE = 'SOLD_SERVICE'
AND REL.JTOT_OBJECT1_CODE = 'OKL_SERVICE_LINE'
AND REL.RTY_CODE = 'OKLSRV'
AND REL.CLE_ID = OKL_SERV_L.ID
AND REL.CHR_ID = OKL_FIN_L.DNZ_CHR_ID
AND REL.OBJECT1_ID1 = SRV_TOP_L.ID
AND SRV_TOP_L.ID = SRV_COV_L.CLE_ID
AND SRV_TOP_L.DNZ_CHR_ID = SRV_COV_L.DNZ_CHR_ID
AND SRV_COV_L.ID = SRV_ITEM.CLE_ID
AND SRV_COV_L.DNZ_CHR_ID = SRV_ITEM.DNZ_CHR_ID
AND SRV_ITEM.OBJECT1_ID1 = CS_PROD.INSTANCE_ID
AND SRV_ITEM.JTOT_OBJECT1_CODE = 'OKX_CUSTPROD'
AND CS_PROD.INVENTORY_ITEM_ID = OKL_ITEM.OBJECT1_ID1
AND OKL_ITEM.OBJECT1_ID1 = INV_ITEM.INVENTORY_ITEM_ID
AND OKL_ITEM.OBJECT1_ID2 = INV_ITEM.ORGANIZATION_ID UNION SELECT OKL_FIN_L.ID ID1
, '#' ID2
, OKL_H.ID CHR_ID
, OKL_H.CONTRACT_NUMBER CONTRACT_NUMBER
, OKL_FIN_TL.NAME NAME
, OKL_FIN_TL.ITEM_DESCRIPTION DESCRIPTION
, OKL_SERV_L.ID SERV_TOP_LINE_ID
, TO_NUMBER( NULL) OKS_COV_LINE_ID
, INV_ITEM.SEGMENT1 ITEM_NUMBER
, INV_ITEM.DESCRIPTION ITEM_DESCRIPTION
, INV_ITEM.INVENTORY_ITEM_ID
, INV_ITEM.SERIAL_NUMBER_CONTROL_CODE
, OKL_ITEM.NUMBER_OF_ITEMS
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM OKC_LINE_STYLES_B SERV_STYLE
, OKC_K_LINES_B OKL_SERV_L
, OKC_K_ITEMS OKL_ITEM
, OKC_LINE_STYLES_B ITEM_STYLE
, OKC_K_LINES_B OKL_ITEM_L
, OKC_LINE_STYLES_B FIN_STYLE
, OKC_K_LINES_TL OKL_FIN_TL
, OKC_K_LINES_B OKL_FIN_L
, OKC_K_HEADERS_B OKL_H
, OKC_STATUSES_B LINE_STATUS
, MTL_SYSTEM_ITEMS_B INV_ITEM
WHERE OKL_H.ID = OKL_FIN_L.DNZ_CHR_ID
AND OKL_FIN_L.LSE_ID = FIN_STYLE.ID
AND FIN_STYLE.LTY_CODE = 'FREE_FORM1'
AND OKL_FIN_L.ID = OKL_FIN_TL.ID
AND OKL_FIN_L.STS_CODE = LINE_STATUS.CODE
AND LINE_STATUS.STE_CODE NOT IN ('EXPIRED'
, 'TERMINATED'
, 'CANCELLED'
, 'HOLD')
AND OKL_FIN_TL.LANGUAGE = USERENV('LANG')
AND OKL_ITEM_L.CLE_ID = OKL_FIN_L.ID
AND OKL_ITEM_L.DNZ_CHR_ID = OKL_FIN_L.DNZ_CHR_ID
AND OKL_ITEM_L.LSE_ID = ITEM_STYLE.ID
AND ITEM_STYLE.LTY_CODE = 'ITEM'
AND OKL_ITEM.CLE_ID = OKL_ITEM_L.ID
AND OKL_ITEM.DNZ_CHR_ID = OKL_ITEM_L.DNZ_CHR_ID
AND OKL_ITEM.JTOT_OBJECT1_CODE = 'OKX_SYSITEM'
AND OKL_SERV_L.DNZ_CHR_ID = OKL_FIN_L.DNZ_CHR_ID
AND OKL_SERV_L.LSE_ID = SERV_STYLE.ID
AND SERV_STYLE.LTY_CODE = 'SOLD_SERVICE'
AND OKL_ITEM.OBJECT1_ID1 = INV_ITEM.INVENTORY_ITEM_ID
AND OKL_ITEM.OBJECT1_ID2 = INV_ITEM.ORGANIZATION_ID
AND NOT EXISTS (SELECT 'Y'
FROM OKC_K_REL_OBJS REL
WHERE REL.CLE_ID = OKL_SERV_L.ID
AND REL.RTY_CODE = 'OKLSRV')

Columns