eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.OKS_VRM_LINES_V

Source

View Text - Preformatted


SELECT LIN.DNZ_CHR_ID CONTRACT_ID, SUBLIN.ID CONTRACT_SUB_LINE_ID, (SELECT LINE.LINE_NUMBER FROM OKC_K_LINES_B LINE WHERE LINE.ID = SUBLIN.CLE_ID) || '.' || SUBLIN.LINE_NUMBER LINE_NUMBER, LSV.NAME LINE_TYPE, SUB_LSV.NAME SUBLINE_TYPE, STS.MEANING CLVL_STS_CODE, CHRB.INV_ORGANIZATION_ID, CHRB.AUTHORING_ORG_ID ORG_ID, ITM.OBJECT1_ID1 INVENTORY_ITEM_ID, Nvl(OKSSUBLIN.TOPLVL_UOM_CODE ,oks_auth_util_pvt.get_uom(SUBLIN.START_DATE,SUBLIN.END_DATE)) UOM, Decode(SUBLIN.DATE_TERMINATED,NULL,itm_sline.Number_of_items * NVL(OKSSUBLIN.TOPLVL_PRICE_QTY, oks_auth_util_pvt.get_DURATION (SUBLIN.START_DATE,SUBLIN.END_DATE)), SUBLIN.PRICE_NEGOTIATED/Decode(SUBLIN.price_unit,0,1,sublin.price_unit)) QUANTITY, Decode(SUBLIN.DATE_TERMINATED,NULL,NVL(OKSSUBLIN.TOPLVL_ADJ_PRICE, OKS_EXTWAR_UTIL_PVT.round_currency_amt(SUBLIN.PRICE_NEGOTIATED,CHRB.currency_code) / DECODE((itm_sline.Number_of_items * NVL(OKSSUBLIN.TOPLVL_PRICE_QTY, oks_auth_util_pvt.get_DURATION(SUBLIN.START_DATE,SUBLIN.END_DATE))),0,1, (itm_sline.Number_of_items * NVL(OKSSUBLIN.TOPLVL_PRICE_QTY, oks_auth_util_pvt.get_DURATION(SUBLIN.START_DATE,SUBLIN.END_DATE))))),SUBLIN.price_unit) PRICE_UNIT, SUBLIN.LINE_LIST_PRICE, LIN.BILL_TO_SITE_USE_ID, LIN.SHIP_TO_SITE_USE_ID, OKSLIN.CUST_PO_NUMBER, CHRB.payment_term_id , OKSLIN.ACCT_RULE_ID, SUBLIN.START_DATE START_DATE, Decode(SUBLIN.DATE_TERMINATED, NULL, SUBLIN.END_DATE, SUBLIN.DATE_TERMINATED - 1) END_DATE, SUBLIN.DATE_TERMINATED SUBLINE_DATE_TERMINATED, (Nvl(OKSSUBLIN.CREDIT_AMOUNT,0) + Nvl(OKSSUBLIN.SUPPRESSED_CREDIT,0)) SUBLINE_TRM_AMOUNT, OKS_EXTWAR_UTIL_PVT.round_currency_amt(SUBLIN.PRICE_NEGOTIATED,CHRB.currency_code) SUBLINE_AMOUNT, LIN.ORIG_SYSTEM_SOURCE_CODE, LIN.ORIG_SYSTEM_ID1, SUBLIN.TRN_CODE, OKSSUBLIN.CREATION_DATE, OKSSUBLIN.LAST_UPDATE_DATE, LIN.CONFIG_TOP_MODEL_LINE_ID, LIN.PRICE_LIST_ID, Decode(ORD_LINE.Line_Number,NULL, ORD_Hdr.Order_Number,ORD_Hdr.Order_Number || '/' || ORD_LINE.Line_Number) ORDER_LINE_NUMBER, LIN.ATTRIBUTE_CATEGORY, LIN.ATTRIBUTE1, LIN.ATTRIBUTE2, LIN.ATTRIBUTE3, LIN.ATTRIBUTE4, LIN.ATTRIBUTE5, LIN.ATTRIBUTE6, LIN.ATTRIBUTE7, LIN.ATTRIBUTE8, LIN.ATTRIBUTE9, LIN.ATTRIBUTE10, LIN.ATTRIBUTE11, LIN.ATTRIBUTE12, LIN.ATTRIBUTE13, LIN.ATTRIBUTE14, LIN.ATTRIBUTE15, oks_code_hook.get_contract_source_details('SOURCE_TRANSACTION',LIN.LSE_ID ,SUBLIN.ID ,LIN.DNZ_CHR_ID) SOURCE_TRANSACTION, oks_code_hook.get_contract_source_details('SOURCE_DOCUMENT',LIN.LSE_ID ,SUBLIN.ID ,LIN.DNZ_CHR_ID) SOURCE_DOCUMENT, oks_code_hook.get_contract_source_details('SOURCE_LINE',LIN.LSE_ID ,SUBLIN.ID ,LIN.DNZ_CHR_ID) SOURCE_LINE, (SELECT UNIT_SELLING_PRICE FROM oe_order_lines_all ool,csi_item_instances csi WHERE csi.last_oe_order_line_id=ool.line_id AND csi.instance_id=itm_sline.object1_id1 AND SUBLIN.LSE_ID IN (18,25,9)) BASE_PRICE , 1 BILLING_ID , Decode(SUBLIN.DATE_TERMINATED,NULL,OKSSUBLIN.revenue_impact_date,SUBLIN.LAST_UPDATE_DATE) REVENUE_IMPACT_DATE , Decode(STS.STE_CODE,'TERMINATED','Y','EXPIRED','Y' ) DELIVERED_FLAG FROM OKC_K_LINES_B LIN, OKC_K_LINES_B SUBLIN, OKS_K_LINES_B OKSLIN, OKS_K_LINES_B OKSSUBLIN, OKC_STATUSES_V STS, OKC_K_HEADERS_ALL_B CHRB, OKS_K_HEADERS_B KHR, OKC_K_ITEMS ITM, OKC_LINE_STYLES_V LSV, OKC_LINE_STYLES_V SUB_LSV, okx_order_lines_v ord_line, okc_k_rel_objs_v Rel, okx_order_headers_v ord_hdr , okc_k_items itm_sline WHERE LIN.ID = SUBLIN.CLE_ID AND LIN.LSE_ID IN (1, 14, 19) AND SUBLIN.LSE_ID IN (7, 8, 9, 10, 11, 18, 25, 35) AND SUBLIN.ID = OKSSUBLIN.CLE_ID AND LIN.ID = OKSLIN.CLE_ID AND LIN.CHR_ID = CHRB.ID AND SUBLIN.STS_CODE = STS.CODE AND LIN.ID = ITM.CLE_ID and sublin.id=itm_sline.cle_id AND LIN.LSE_ID = LSV.ID AND SUBLIN.LSE_ID = SUB_LSV.ID AND CHRB.ID = KHR.CHR_ID AND ORD_Hdr.Id1(+) = ORD_Line.Header_Id And ORD_Line.id1(+) = Rel.Object1_Id1 And Rel.Jtot_Object1_Code(+) = 'OKX_ORDERLINE' And Rel.cle_id(+) = LIN.ID AND NVL(KHR.AR_INTERFACE_YN, 'Y') = 'Y' AND STS.STE_CODE NOT IN ('ENTERED', 'CANCELLED','HOLD') UNION ALL SELECT LIN.DNZ_CHR_ID CONTRACT_ID, SUBLIN.ID CONTRACT_SUB_LINE_ID, (SELECT LINE.LINE_NUMBER FROM OKC_K_LINES_B LINE WHERE LINE.ID = SUBLIN.CLE_ID) || '.' || SUBLIN.LINE_NUMBER LINE_NUMBER, LSV.NAME LINE_TYPE, SUB_LSV.NAME SUBLINE_TYPE, STS.MEANING CLVL_STS_CODE, CHRB.INV_ORGANIZATION_ID, CHRB.AUTHORING_ORG_ID ORG_ID, ITM.OBJECT1_ID1 INVENTORY_ITEM_ID, bsd.unit_of_measure UOM, bsd.result QUANTITY, (bsl.amount/DECODE(bsd.result,0,1,BSD.RESULT)) PRICE_UNIT, NULL LINE_LIST_PRICE, LIN.BILL_TO_SITE_USE_ID, LIN.SHIP_TO_SITE_USE_ID, OKSLIN.CUST_PO_NUMBER, CHRB.PAYMENT_TERM_ID, OKSLIN.ACCT_RULE_ID, SUBLIN.START_DATE START_DATE, Decode(SUBLIN.DATE_TERMINATED, NULL,SUBLIN.END_DATE, SUBLIN.DATE_TERMINATED - 1) END_DATE, SUBLIN.DATE_TERMINATED SUBLINE_DATE_TERMINATED, NVL2( SUBLIN.DATE_TERMINATED, bsl.amount , null ) SUBLINE_TRM_AMOUNT, nvl2(SUBLIN.DATE_TERMINATED,NULL,bsl.amount) SUBLINE_AMOUNT, LIN.ORIG_SYSTEM_SOURCE_CODE, LIN.ORIG_SYSTEM_ID1, SUBLIN.TRN_CODE, OKSSUBLIN.CREATION_DATE, OKSSUBLIN.LAST_UPDATE_DATE, LIN.CONFIG_TOP_MODEL_LINE_ID, LIN.PRICE_LIST_ID, NULL ORDER_LINE_NUMBER, LIN.ATTRIBUTE_CATEGORY, LIN.ATTRIBUTE1, LIN.ATTRIBUTE2, LIN.ATTRIBUTE3, LIN.ATTRIBUTE4, LIN.ATTRIBUTE5, LIN.ATTRIBUTE6, LIN.ATTRIBUTE7, LIN.ATTRIBUTE8, LIN.ATTRIBUTE9, LIN.ATTRIBUTE10, LIN.ATTRIBUTE11, LIN.ATTRIBUTE12, LIN.ATTRIBUTE13, LIN.ATTRIBUTE14, LIN.ATTRIBUTE15 , oks_code_hook.get_contract_source_details('SOURCE_TRANSACTION',LIN.LSE_ID ,SUBLIN.ID ,LIN.DNZ_CHR_ID) SOURCE_TRANSACTION, oks_code_hook.get_contract_source_details('SOURCE_DOCUMENT',LIN.LSE_ID ,SUBLIN.ID ,LIN.DNZ_CHR_ID) SOURCE_DOCUMENT, oks_code_hook.get_contract_source_details('SOURCE_LINE',LIN.LSE_ID ,SUBLIN.ID ,LIN.DNZ_CHR_ID) SOURCE_LINE , NULL BASE_PRICE , BSL.id BILLING_ID , BSL.CREATION_DATE REVENUE_IMPACT_DATE, Decode(STS.STE_CODE,'TERMINATED','Y','EXPIRED','Y' ) DELIVERED_FLAG FROM OKC_K_LINES_B LIN, OKC_K_LINES_B SUBLIN, OKS_K_LINES_B OKSLIN, OKS_K_LINES_B OKSSUBLIN, OKC_STATUSES_V STS, OKC_K_HEADERS_ALL_B CHRB, OKS_K_HEADERS_B KHR, OKC_K_ITEMS ITM, OKC_LINE_STYLES_V LSV, OKC_LINE_STYLES_V SUB_LSV, OKC_K_ITEMS ITM_SLINE , OKS_BILL_SUB_LINES BSL, OKS_BILL_SUB_LINE_DTLS BSD WHERE LIN.ID = SUBLIN.CLE_ID AND LIN.LSE_ID = 12 AND SUBLIN.LSE_ID IN (13,66) AND SUBLIN.ID = OKSSUBLIN.CLE_ID AND LIN.ID = OKSLIN.CLE_ID AND bsl.cle_id= sublin.id AND bsd.bsl_id=bsl.id AND LIN.CHR_ID = CHRB.ID AND SUBLIN.STS_CODE = STS.CODE AND LIN.ID = ITM.CLE_ID and sublin.id=itm_sline.cle_id AND LIN.LSE_ID = LSV.ID AND SUBLIN.LSE_ID = SUB_LSV.ID AND CHRB.ID = KHR.CHR_ID AND NVL(KHR.AR_INTERFACE_YN, 'Y') = 'Y' AND STS.STE_CODE NOT IN ('ENTERED', 'CANCELLED','HOLD') UNION ALL select LIN.DNZ_CHR_ID CONTRACT_ID, lin.id CONTRACT_SUB_LINE_ID, LIN.line_number LINE_NUMBER, LSV.NAME LINE_TYPE, null SUBLINE_TYPE, STS.MEANING CLVL_STS_CODE, CHRB.INV_ORGANIZATION_ID, CHRB.AUTHORING_ORG_ID ORG_ID, ITM.OBJECT1_ID1 INVENTORY_ITEM_ID, oks_code_hook.get_subs_uom(lin.id) UOM, oks_code_hook.get_subs_qty(lin.id) QUANTITY, NVL(okslin.toplvl_adj_price,OKS_EXTWAR_UTIL_PVT.round_currency_amt(lin.PRICE_NEGOTIATED,CHRB.currency_code) /DECODE(oks_code_hook.get_subs_qty(lin.id),0,1,oks_code_hook.get_subs_qty(lin.id))) PRICE_UNIT, lin.LINE_LIST_PRICE LINE_LIST_PRICE, LIN.BILL_TO_SITE_USE_ID, LIN.SHIP_TO_SITE_USE_ID, OKSLIN.CUST_PO_NUMBER, CHRB.PAYMENT_TERM_ID, OKSLIN.ACCT_RULE_ID, LIN.START_DATE START_DATE, Decode(LIN.DATE_TERMINATED, NULL, LIN.END_DATE, LIN.DATE_TERMINATED - 1) END_DATE, LIN.DATE_TERMINATED SUBLINE_DATE_TERMINATED, (Nvl(okslin.CREDIT_AMOUNT,0) + Nvl(okslin.SUPPRESSED_CREDIT,0)) SUBLINE_TRM_AMOUNT, OKS_EXTWAR_UTIL_PVT.round_currency_amt(lin.PRICE_NEGOTIATED,CHRB.currency_code) SUBLINE_AMOUNT, LIN.ORIG_SYSTEM_SOURCE_CODE, LIN.ORIG_SYSTEM_ID1, LIN.TRN_CODE, okslin.CREATION_DATE, okslin.LAST_UPDATE_DATE, LIN.CONFIG_TOP_MODEL_LINE_ID, LIN.PRICE_LIST_ID, null ORDER_LINE_NUMBER, LIN.ATTRIBUTE_CATEGORY, LIN.ATTRIBUTE1, LIN.ATTRIBUTE2, LIN.ATTRIBUTE3, LIN.ATTRIBUTE4, LIN.ATTRIBUTE5, LIN.ATTRIBUTE6, LIN.ATTRIBUTE7, LIN.ATTRIBUTE8, LIN.ATTRIBUTE9, LIN.ATTRIBUTE10, LIN.ATTRIBUTE11, LIN.ATTRIBUTE12, LIN.ATTRIBUTE13, LIN.ATTRIBUTE14, LIN.ATTRIBUTE15, NULL SOURCE_TRANSACTION, NULL SOURCE_DOCUMENT, NULL SOURCE_LINE, NULL BASE_PRICE , 1 BILLING_ID , Decode(lin.DATE_TERMINATED,NULL,okslin.revenue_impact_date,lin.LAST_UPDATE_DATE) REVENUE_IMPACT_DATE , Decode(STS.STE_CODE,'TERMINATED','Y','EXPIRED','Y' ) DELIVERED_FLAG from okc_k_lines_b lin, oks_k_lines_b okslin, oKC_LINE_STYLES_V LSV, OKC_STATUSES_V STS, OKC_K_HEADERS_ALL_B CHRB, OKS_K_HEADERS_B KHR, OKC_K_ITEMS ITM where lin.id=okslin.cle_id and lin.lse_id = 46 AND LIN.CHR_ID = CHRB.ID AND LIN.STS_CODE = STS.CODE(+) AND LIN.ID = ITM.CLE_ID AND LIN.LSE_ID = LSV.ID AND CHRB.ID = KHR.CHR_ID AND NVL(KHR.AR_INTERFACE_YN, 'Y') = 'Y' AND STS.STE_CODE NOT IN ('ENTERED', 'CANCELLED','HOLD')

View Text - HTML Formatted

SELECT LIN.DNZ_CHR_ID CONTRACT_ID
, SUBLIN.ID CONTRACT_SUB_LINE_ID
, (SELECT LINE.LINE_NUMBER
FROM OKC_K_LINES_B LINE
WHERE LINE.ID = SUBLIN.CLE_ID) || '.' || SUBLIN.LINE_NUMBER LINE_NUMBER
, LSV.NAME LINE_TYPE
, SUB_LSV.NAME SUBLINE_TYPE
, STS.MEANING CLVL_STS_CODE
, CHRB.INV_ORGANIZATION_ID
, CHRB.AUTHORING_ORG_ID ORG_ID
, ITM.OBJECT1_ID1 INVENTORY_ITEM_ID
, NVL(OKSSUBLIN.TOPLVL_UOM_CODE
, OKS_AUTH_UTIL_PVT.GET_UOM(SUBLIN.START_DATE
, SUBLIN.END_DATE)) UOM
, DECODE(SUBLIN.DATE_TERMINATED
, NULL
, ITM_SLINE.NUMBER_OF_ITEMS * NVL(OKSSUBLIN.TOPLVL_PRICE_QTY
, OKS_AUTH_UTIL_PVT.GET_DURATION (SUBLIN.START_DATE
, SUBLIN.END_DATE))
, SUBLIN.PRICE_NEGOTIATED/DECODE(SUBLIN.PRICE_UNIT
, 0
, 1
, SUBLIN.PRICE_UNIT)) QUANTITY
, DECODE(SUBLIN.DATE_TERMINATED
, NULL
, NVL(OKSSUBLIN.TOPLVL_ADJ_PRICE
, OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(SUBLIN.PRICE_NEGOTIATED
, CHRB.CURRENCY_CODE) / DECODE((ITM_SLINE.NUMBER_OF_ITEMS * NVL(OKSSUBLIN.TOPLVL_PRICE_QTY
, OKS_AUTH_UTIL_PVT.GET_DURATION(SUBLIN.START_DATE
, SUBLIN.END_DATE)))
, 0
, 1
, (ITM_SLINE.NUMBER_OF_ITEMS * NVL(OKSSUBLIN.TOPLVL_PRICE_QTY
, OKS_AUTH_UTIL_PVT.GET_DURATION(SUBLIN.START_DATE
, SUBLIN.END_DATE)))))
, SUBLIN.PRICE_UNIT) PRICE_UNIT
, SUBLIN.LINE_LIST_PRICE
, LIN.BILL_TO_SITE_USE_ID
, LIN.SHIP_TO_SITE_USE_ID
, OKSLIN.CUST_PO_NUMBER
, CHRB.PAYMENT_TERM_ID
, OKSLIN.ACCT_RULE_ID
, SUBLIN.START_DATE START_DATE
, DECODE(SUBLIN.DATE_TERMINATED
, NULL
, SUBLIN.END_DATE
, SUBLIN.DATE_TERMINATED - 1) END_DATE
, SUBLIN.DATE_TERMINATED SUBLINE_DATE_TERMINATED
, (NVL(OKSSUBLIN.CREDIT_AMOUNT
, 0) + NVL(OKSSUBLIN.SUPPRESSED_CREDIT
, 0)) SUBLINE_TRM_AMOUNT
, OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(SUBLIN.PRICE_NEGOTIATED
, CHRB.CURRENCY_CODE) SUBLINE_AMOUNT
, LIN.ORIG_SYSTEM_SOURCE_CODE
, LIN.ORIG_SYSTEM_ID1
, SUBLIN.TRN_CODE
, OKSSUBLIN.CREATION_DATE
, OKSSUBLIN.LAST_UPDATE_DATE
, LIN.CONFIG_TOP_MODEL_LINE_ID
, LIN.PRICE_LIST_ID
, DECODE(ORD_LINE.LINE_NUMBER
, NULL
, ORD_HDR.ORDER_NUMBER
, ORD_HDR.ORDER_NUMBER || '/' || ORD_LINE.LINE_NUMBER) ORDER_LINE_NUMBER
, LIN.ATTRIBUTE_CATEGORY
, LIN.ATTRIBUTE1
, LIN.ATTRIBUTE2
, LIN.ATTRIBUTE3
, LIN.ATTRIBUTE4
, LIN.ATTRIBUTE5
, LIN.ATTRIBUTE6
, LIN.ATTRIBUTE7
, LIN.ATTRIBUTE8
, LIN.ATTRIBUTE9
, LIN.ATTRIBUTE10
, LIN.ATTRIBUTE11
, LIN.ATTRIBUTE12
, LIN.ATTRIBUTE13
, LIN.ATTRIBUTE14
, LIN.ATTRIBUTE15
, OKS_CODE_HOOK.GET_CONTRACT_SOURCE_DETAILS('SOURCE_TRANSACTION'
, LIN.LSE_ID
, SUBLIN.ID
, LIN.DNZ_CHR_ID) SOURCE_TRANSACTION
, OKS_CODE_HOOK.GET_CONTRACT_SOURCE_DETAILS('SOURCE_DOCUMENT'
, LIN.LSE_ID
, SUBLIN.ID
, LIN.DNZ_CHR_ID) SOURCE_DOCUMENT
, OKS_CODE_HOOK.GET_CONTRACT_SOURCE_DETAILS('SOURCE_LINE'
, LIN.LSE_ID
, SUBLIN.ID
, LIN.DNZ_CHR_ID) SOURCE_LINE
, (SELECT UNIT_SELLING_PRICE
FROM OE_ORDER_LINES_ALL OOL
, CSI_ITEM_INSTANCES CSI
WHERE CSI.LAST_OE_ORDER_LINE_ID=OOL.LINE_ID
AND CSI.INSTANCE_ID=ITM_SLINE.OBJECT1_ID1
AND SUBLIN.LSE_ID IN (18
, 25
, 9)) BASE_PRICE
, 1 BILLING_ID
, DECODE(SUBLIN.DATE_TERMINATED
, NULL
, OKSSUBLIN.REVENUE_IMPACT_DATE
, SUBLIN.LAST_UPDATE_DATE) REVENUE_IMPACT_DATE
, DECODE(STS.STE_CODE
, 'TERMINATED'
, 'Y'
, 'EXPIRED'
, 'Y' ) DELIVERED_FLAG
FROM OKC_K_LINES_B LIN
, OKC_K_LINES_B SUBLIN
, OKS_K_LINES_B OKSLIN
, OKS_K_LINES_B OKSSUBLIN
, OKC_STATUSES_V STS
, OKC_K_HEADERS_ALL_B CHRB
, OKS_K_HEADERS_B KHR
, OKC_K_ITEMS ITM
, OKC_LINE_STYLES_V LSV
, OKC_LINE_STYLES_V SUB_LSV
, OKX_ORDER_LINES_V ORD_LINE
, OKC_K_REL_OBJS_V REL
, OKX_ORDER_HEADERS_V ORD_HDR
, OKC_K_ITEMS ITM_SLINE
WHERE LIN.ID = SUBLIN.CLE_ID
AND LIN.LSE_ID IN (1
, 14
, 19)
AND SUBLIN.LSE_ID IN (7
, 8
, 9
, 10
, 11
, 18
, 25
, 35)
AND SUBLIN.ID = OKSSUBLIN.CLE_ID
AND LIN.ID = OKSLIN.CLE_ID
AND LIN.CHR_ID = CHRB.ID
AND SUBLIN.STS_CODE = STS.CODE
AND LIN.ID = ITM.CLE_ID
AND SUBLIN.ID=ITM_SLINE.CLE_ID
AND LIN.LSE_ID = LSV.ID
AND SUBLIN.LSE_ID = SUB_LSV.ID
AND CHRB.ID = KHR.CHR_ID
AND ORD_HDR.ID1(+) = ORD_LINE.HEADER_ID
AND ORD_LINE.ID1(+) = REL.OBJECT1_ID1
AND REL.JTOT_OBJECT1_CODE(+) = 'OKX_ORDERLINE'
AND REL.CLE_ID(+) = LIN.ID
AND NVL(KHR.AR_INTERFACE_YN
, 'Y') = 'Y'
AND STS.STE_CODE NOT IN ('ENTERED'
, 'CANCELLED'
, 'HOLD') UNION ALL SELECT LIN.DNZ_CHR_ID CONTRACT_ID
, SUBLIN.ID CONTRACT_SUB_LINE_ID
, (SELECT LINE.LINE_NUMBER
FROM OKC_K_LINES_B LINE
WHERE LINE.ID = SUBLIN.CLE_ID) || '.' || SUBLIN.LINE_NUMBER LINE_NUMBER
, LSV.NAME LINE_TYPE
, SUB_LSV.NAME SUBLINE_TYPE
, STS.MEANING CLVL_STS_CODE
, CHRB.INV_ORGANIZATION_ID
, CHRB.AUTHORING_ORG_ID ORG_ID
, ITM.OBJECT1_ID1 INVENTORY_ITEM_ID
, BSD.UNIT_OF_MEASURE UOM
, BSD.RESULT QUANTITY
, (BSL.AMOUNT/DECODE(BSD.RESULT
, 0
, 1
, BSD.RESULT)) PRICE_UNIT
, NULL LINE_LIST_PRICE
, LIN.BILL_TO_SITE_USE_ID
, LIN.SHIP_TO_SITE_USE_ID
, OKSLIN.CUST_PO_NUMBER
, CHRB.PAYMENT_TERM_ID
, OKSLIN.ACCT_RULE_ID
, SUBLIN.START_DATE START_DATE
, DECODE(SUBLIN.DATE_TERMINATED
, NULL
, SUBLIN.END_DATE
, SUBLIN.DATE_TERMINATED - 1) END_DATE
, SUBLIN.DATE_TERMINATED SUBLINE_DATE_TERMINATED
, NVL2( SUBLIN.DATE_TERMINATED
, BSL.AMOUNT
, NULL ) SUBLINE_TRM_AMOUNT
, NVL2(SUBLIN.DATE_TERMINATED
, NULL
, BSL.AMOUNT) SUBLINE_AMOUNT
, LIN.ORIG_SYSTEM_SOURCE_CODE
, LIN.ORIG_SYSTEM_ID1
, SUBLIN.TRN_CODE
, OKSSUBLIN.CREATION_DATE
, OKSSUBLIN.LAST_UPDATE_DATE
, LIN.CONFIG_TOP_MODEL_LINE_ID
, LIN.PRICE_LIST_ID
, NULL ORDER_LINE_NUMBER
, LIN.ATTRIBUTE_CATEGORY
, LIN.ATTRIBUTE1
, LIN.ATTRIBUTE2
, LIN.ATTRIBUTE3
, LIN.ATTRIBUTE4
, LIN.ATTRIBUTE5
, LIN.ATTRIBUTE6
, LIN.ATTRIBUTE7
, LIN.ATTRIBUTE8
, LIN.ATTRIBUTE9
, LIN.ATTRIBUTE10
, LIN.ATTRIBUTE11
, LIN.ATTRIBUTE12
, LIN.ATTRIBUTE13
, LIN.ATTRIBUTE14
, LIN.ATTRIBUTE15
, OKS_CODE_HOOK.GET_CONTRACT_SOURCE_DETAILS('SOURCE_TRANSACTION'
, LIN.LSE_ID
, SUBLIN.ID
, LIN.DNZ_CHR_ID) SOURCE_TRANSACTION
, OKS_CODE_HOOK.GET_CONTRACT_SOURCE_DETAILS('SOURCE_DOCUMENT'
, LIN.LSE_ID
, SUBLIN.ID
, LIN.DNZ_CHR_ID) SOURCE_DOCUMENT
, OKS_CODE_HOOK.GET_CONTRACT_SOURCE_DETAILS('SOURCE_LINE'
, LIN.LSE_ID
, SUBLIN.ID
, LIN.DNZ_CHR_ID) SOURCE_LINE
, NULL BASE_PRICE
, BSL.ID BILLING_ID
, BSL.CREATION_DATE REVENUE_IMPACT_DATE
, DECODE(STS.STE_CODE
, 'TERMINATED'
, 'Y'
, 'EXPIRED'
, 'Y' ) DELIVERED_FLAG
FROM OKC_K_LINES_B LIN
, OKC_K_LINES_B SUBLIN
, OKS_K_LINES_B OKSLIN
, OKS_K_LINES_B OKSSUBLIN
, OKC_STATUSES_V STS
, OKC_K_HEADERS_ALL_B CHRB
, OKS_K_HEADERS_B KHR
, OKC_K_ITEMS ITM
, OKC_LINE_STYLES_V LSV
, OKC_LINE_STYLES_V SUB_LSV
, OKC_K_ITEMS ITM_SLINE
, OKS_BILL_SUB_LINES BSL
, OKS_BILL_SUB_LINE_DTLS BSD
WHERE LIN.ID = SUBLIN.CLE_ID
AND LIN.LSE_ID = 12
AND SUBLIN.LSE_ID IN (13
, 66)
AND SUBLIN.ID = OKSSUBLIN.CLE_ID
AND LIN.ID = OKSLIN.CLE_ID
AND BSL.CLE_ID= SUBLIN.ID
AND BSD.BSL_ID=BSL.ID
AND LIN.CHR_ID = CHRB.ID
AND SUBLIN.STS_CODE = STS.CODE
AND LIN.ID = ITM.CLE_ID
AND SUBLIN.ID=ITM_SLINE.CLE_ID
AND LIN.LSE_ID = LSV.ID
AND SUBLIN.LSE_ID = SUB_LSV.ID
AND CHRB.ID = KHR.CHR_ID
AND NVL(KHR.AR_INTERFACE_YN
, 'Y') = 'Y'
AND STS.STE_CODE NOT IN ('ENTERED'
, 'CANCELLED'
, 'HOLD') UNION ALL SELECT LIN.DNZ_CHR_ID CONTRACT_ID
, LIN.ID CONTRACT_SUB_LINE_ID
, LIN.LINE_NUMBER LINE_NUMBER
, LSV.NAME LINE_TYPE
, NULL SUBLINE_TYPE
, STS.MEANING CLVL_STS_CODE
, CHRB.INV_ORGANIZATION_ID
, CHRB.AUTHORING_ORG_ID ORG_ID
, ITM.OBJECT1_ID1 INVENTORY_ITEM_ID
, OKS_CODE_HOOK.GET_SUBS_UOM(LIN.ID) UOM
, OKS_CODE_HOOK.GET_SUBS_QTY(LIN.ID) QUANTITY
, NVL(OKSLIN.TOPLVL_ADJ_PRICE
, OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(LIN.PRICE_NEGOTIATED
, CHRB.CURRENCY_CODE) /DECODE(OKS_CODE_HOOK.GET_SUBS_QTY(LIN.ID)
, 0
, 1
, OKS_CODE_HOOK.GET_SUBS_QTY(LIN.ID))) PRICE_UNIT
, LIN.LINE_LIST_PRICE LINE_LIST_PRICE
, LIN.BILL_TO_SITE_USE_ID
, LIN.SHIP_TO_SITE_USE_ID
, OKSLIN.CUST_PO_NUMBER
, CHRB.PAYMENT_TERM_ID
, OKSLIN.ACCT_RULE_ID
, LIN.START_DATE START_DATE
, DECODE(LIN.DATE_TERMINATED
, NULL
, LIN.END_DATE
, LIN.DATE_TERMINATED - 1) END_DATE
, LIN.DATE_TERMINATED SUBLINE_DATE_TERMINATED
, (NVL(OKSLIN.CREDIT_AMOUNT
, 0) + NVL(OKSLIN.SUPPRESSED_CREDIT
, 0)) SUBLINE_TRM_AMOUNT
, OKS_EXTWAR_UTIL_PVT.ROUND_CURRENCY_AMT(LIN.PRICE_NEGOTIATED
, CHRB.CURRENCY_CODE) SUBLINE_AMOUNT
, LIN.ORIG_SYSTEM_SOURCE_CODE
, LIN.ORIG_SYSTEM_ID1
, LIN.TRN_CODE
, OKSLIN.CREATION_DATE
, OKSLIN.LAST_UPDATE_DATE
, LIN.CONFIG_TOP_MODEL_LINE_ID
, LIN.PRICE_LIST_ID
, NULL ORDER_LINE_NUMBER
, LIN.ATTRIBUTE_CATEGORY
, LIN.ATTRIBUTE1
, LIN.ATTRIBUTE2
, LIN.ATTRIBUTE3
, LIN.ATTRIBUTE4
, LIN.ATTRIBUTE5
, LIN.ATTRIBUTE6
, LIN.ATTRIBUTE7
, LIN.ATTRIBUTE8
, LIN.ATTRIBUTE9
, LIN.ATTRIBUTE10
, LIN.ATTRIBUTE11
, LIN.ATTRIBUTE12
, LIN.ATTRIBUTE13
, LIN.ATTRIBUTE14
, LIN.ATTRIBUTE15
, NULL SOURCE_TRANSACTION
, NULL SOURCE_DOCUMENT
, NULL SOURCE_LINE
, NULL BASE_PRICE
, 1 BILLING_ID
, DECODE(LIN.DATE_TERMINATED
, NULL
, OKSLIN.REVENUE_IMPACT_DATE
, LIN.LAST_UPDATE_DATE) REVENUE_IMPACT_DATE
, DECODE(STS.STE_CODE
, 'TERMINATED'
, 'Y'
, 'EXPIRED'
, 'Y' ) DELIVERED_FLAG
FROM OKC_K_LINES_B LIN
, OKS_K_LINES_B OKSLIN
, OKC_LINE_STYLES_V LSV
, OKC_STATUSES_V STS
, OKC_K_HEADERS_ALL_B CHRB
, OKS_K_HEADERS_B KHR
, OKC_K_ITEMS ITM
WHERE LIN.ID=OKSLIN.CLE_ID
AND LIN.LSE_ID = 46
AND LIN.CHR_ID = CHRB.ID
AND LIN.STS_CODE = STS.CODE(+)
AND LIN.ID = ITM.CLE_ID
AND LIN.LSE_ID = LSV.ID
AND CHRB.ID = KHR.CHR_ID
AND NVL(KHR.AR_INTERFACE_YN
, 'Y') = 'Y'
AND STS.STE_CODE NOT IN ('ENTERED'
, 'CANCELLED'
, 'HOLD')