View: OKL_CS_SERVICE_ASSETS_UV

Product:	OKL - Lease and Finance Management
Description:	Service contracts line details view.
Implementation/DBA Data:	APPS.OKL_CS_SERVICE_ASSETS_UV

View Text

SELECT OKL.DNZ_CHR_ID CONTRACT_ID
, OKL.ID LINE_ID
, OKL.COGNOMEN LINE_REFERENCE
, OKL.LINE_NUMBER SERVICE_LINE_NUMBER
, OSI.DESCRIPTION SERVICE_NAME
, OKI.OBJECT1_ID1 OBJECT1_ID1
, OKI.OBJECT1_ID2 OBJECT1_ID2
, OKL.START_DATE SERVICE_START_DATE
, OKL.END_DATE SERVICE_END_DATE
, OCA.NAME CUSTOMER_ACCOUNT_NAME
, BTO.DESCRIPTION LINE_BTO_ADDRESS
, STO.DESCRIPTION LINE_STO_ADDRESS
, CSL.NAME
, CSL.LSE_TYPE
FROM OKC_K_LINES_V OKL
, OKC_K_ITEMS OKI
, OKX_CUSTOMER_ACCOUNTS_V OCA
, OKX_SYSTEM_ITEMS_V OSI
, OKL_CS_ASSET_LINES_V CSL
, OKX_CUST_SITE_USES_V BTO
, OKX_CUST_SITE_USES_V STO
WHERE 1!=1 UNION ALL SELECT SERVICE.CHR_ID CONTRACT_ID
, SERVICE.LINE_ID LINE_ID
, SERVICE.LINE_REFERENCE LINE_REFERENCE
, SERVICE.LINE_NUMBER SERVICE_LINE_NUMBER
, SERVICE.NAME SERVICE_NAME
, SERVICE.OBJECT1_ID1 OBJECT1_ID1
, SERVICE.OBJECT1_ID2 OBJECT1_ID2
, SERVICE.START_DATE SERVICE_START_DATE
, SERVICE.END_DATE SERVICE_END_DATE
, SERVICE.DESCRIPTION CUSTOMER_ACCOUNT_NAME
, ASSET.LINE_STYLE LINE_BTO_ADDRESS
, ASSET.ASSET_DESCRIPTION LINE_STO_ADDRESS
, ASSET.ASSET_NAME ASSET_NAME
, LINE_STYLE LINE_STYLE
FROM (SELECT O1.ID ID
, O1.CLE_ID LINE_ID
, O.NAME NAME
, O.DESCRIPTION DESCRIPTION
, O1.START_DATE START_DATE
, O1.END_DATE END_DATE
, O1.CHR_ID CHR_ID
, O1.LINE_NUMBER LINE_NUMBER
, O1.LINE_REFERENCE LINE_REFERENCE
, O1.OBJECT1_ID1 OBJECT1_ID1
, O1.OBJECT1_ID2 OBJECT1_ID2
FROM OKX_SYSTEM_ITEMS_V O
, (SELECT OBJECT1_ID1
, OBJECT1_ID2
, I1.CLE_ID
, I1.ID
, I1.START_DATE START_DATE
, I1.END_DATE END_DATE
, I2.CHR_ID CHR_ID
, I1.LINE_NUMBER LINE_NUMBER
, I1.LINE_REFERENCE LINE_REFERENCE
FROM OKC_K_ITEMS I2
, (SELECT A.CLE_ID
, A.ID
, A.START_DATE START_DATE
, A.END_DATE END_DATE
, A.CHR_ID
, A.LINE_NUMBER LINE_NUMBER
, COGNOMEN LINE_REFERENCE
FROM OKC_K_LINES_V A
, OKC_LINE_STYLES_B B
WHERE A.LSE_ID = B.ID
AND LTY_CODE='COVER_PROD') I1
WHERE I2.CLE_ID =I1.CLE_ID) O1
WHERE O.ID1 = O1.OBJECT1_ID1
AND TO_CHAR(O.ID2) = O1.OBJECT1_ID2
AND O1.OBJECT1_ID2!= '#' ) SERVICE
, ( SELECT O1.I3_I2_I1_ID ID
, O1.I3_I2_I1_LINE_ID LINE_ID
, X.NAME ASSET_NAME
, X.DESCRIPTION ASSET_DESCRIPTION
, X.ITEM_TYPE ITEM_TYPE
, X.INVENTORY_ITEM_STATUS_CODE INV_STATUS
, O1.LINE_STYLE
FROM OKX_SYSTEM_ITEMS_V X
, (SELECT INVENTORY_ITEM_ID
, INV_MASTER_ORGANIZATION_ID
, I2_I1_ID I3_I2_I1_ID
, I2_I1_LINE_ID I3_I2_I1_LINE_ID
, I3.LINE_STYLE LINE_STYLE
FROM CSI_ITEM_INSTANCES O
, (SELECT OBJECT1_ID1
, I1_ID I2_I1_ID
, I1_LINE_ID I2_I1_LINE_ID
, I2.LINE_STYLE LINE_STYLE
FROM OKC_K_ITEMS O
, (SELECT A.ID
, I1.ID I1_ID
, I1.LINE_ID I1_LINE_ID
, I1.LINE_STYLE LINE_STYLE
FROM OKC_K_LINES_V A
, (SELECT A.ID ID
, A.CLE_ID LINE_ID
, B.LSE_TYPE LINE_STYLE
FROM OKC_K_LINES_V A
, OKC_LINE_STYLES_B B
WHERE A.LSE_ID = B.ID
AND LTY_CODE='SERVICE') I1
WHERE LSE_ID = 9
AND CLE_ID = I1.ID ) I2
WHERE O.CLE_ID = I2.ID) I3
WHERE O.INSTANCE_ID = I3.OBJECT1_ID1) O1
WHERE X.ID1= O1.INVENTORY_ITEM_ID
AND X.ID2 = INV_MASTER_ORGANIZATION_ID ) ASSET
WHERE ASSET.ID = SERVICE.LINE_ID

Columns