VIEW: APPS.OKL_LA_COV_ASSET_UV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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