VIEW: APPS.OKL_CS_SERVICE_ASSETS_UV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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