VIEW: APPS.OKL_CS_ASSET_COUNTERS_UV

Source

View Text - Preformatted

SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER, FAA.ASSET_NUMBER ASSET_NUMBER, ITEM.DESCRIPTION ITEM_DESCRIPTION, CSI.SERIAL_NUMBER, CSI.QUANTITY QUANTITY, CSC.NAME COUNTER_NAME, CLGV.NAME CONSOLIDATED_COUNTER_NAME, CSH.VALUE_TIMESTAMP LAST_READING_DATE, CSH.COUNTER_READING LAST_READING, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE, CSC.COUNTER_ID COUNTER_ID, FAA.ASSET_ID ASSET_ID, CLL.ID CLL_ID, CLGV.ID CLG_ID, CHR.ID KHR_ID, CLE1.ID PARENT_LINE_ID FROM OKC_K_HEADERS_B CHR,OKC_K_LINES_B CLE1,OKC_K_LINES_B CLE2,OKC_K_LINES_B CLE3,OKC_K_LINES_B CLE4,OKC_LINE_STYLES_B LSE1,OKC_LINE_STYLES_B LSE2,OKC_LINE_STYLES_B LSE3,OKC_LINE_STYLES_B LSE4,OKC_K_ITEMS CIM,OKC_K_ITEMS CIM1,CSI_ITEM_INSTANCES CSI,MTL_UNITS_OF_MEASURE_VL UOM,MTL_SYSTEM_ITEMS_VL ITEM,CS_COUNTER_GROUPS CSG,CS_COUNTERS CSC,CS_COUNTER_VALUES CSH,FA_ADDITIONS_B FAA,OKL_CNTR_LVLNG_LNS_B CLL,OKL_CNTR_LVLNG_GRPS_V CLGV WHERE chr.id = cle1.dnz_chr_id AND CLE1.LSE_ID = LSE1.ID AND LSE1.LTY_CODE = 'FREE_FORM1' AND CLE2.CLE_ID = CLE1.ID AND CLE2.LSE_ID = LSE2.ID AND LSE2.LTY_CODE = 'FREE_FORM2' AND CLE3.CLE_ID = CLE2.ID AND CLE3.LSE_ID = LSE3.ID AND LSE3.LTY_CODE = 'INST_ITEM' AND CLE3.ID = CIM.CLE_ID AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM' AND CIM.object1_id1 = CSI.instance_id AND CSI.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID AND CSI.INV_MASTER_ORGANIZATION_ID = ITEM.ORGANIZATION_ID and ITEM.PRIMARY_UOM_CODE = UOM.UOM_CODE AND CSI.INSTANCE_ID = CSG.SOURCE_OBJECT_ID AND CSG.COUNTER_GROUP_ID = CSC.COUNTER_GROUP_ID AND CSC.VALID_FLAG = 'Y' AND CSC.UOM_CODE = UOM.UOM_CODE AND CSC.COUNTER_ID = CSH.COUNTER_ID(+) AND (NOT EXISTS (SELECT NULL FROM CS_COUNTER_VALUES CSH1 where CSC.COUNTER_ID = CSH1.COUNTER_ID) OR CSH.VALUE_TIMESTAMP = (SELECT MAX(VALUE_TIMESTAMP ) FROM CS_COUNTER_VALUES WHERE COUNTER_ID = CSH.COUNTER_ID AND NVL(VALID_FLAG, 'Y') = 'Y')) AND CLE1.ID = CLE4.CLE_ID AND CLE4.LSE_ID = LSE4.ID AND LSE4.LTY_CODE = 'FIXED_ASSET' AND CLE4.ID = CIM1.CLE_ID AND CIM1.JTOT_OBJECT1_CODE = 'OKX_ASSET' AND CIM1.OBJECT1_ID1 = FAA.ASSET_ID and csc.counter_id = cll.kle_id AND CLL.CLG_ID = CLGV.ID UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER, FAA.ASSET_NUMBER ASSET_NUMBER, ITEM.DESCRIPTION ITEM_DESCRIPTION, CSI.SERIAL_NUMBER SERIAL_NUMBER, CSI.QUANTITY QUANTITY, CSC.NAME COUNTER_NAME, NULL CONSOLIDATED_COUNTER_NAME, CSH.VALUE_TIMESTAMP LAST_READING_DATE, CSH.COUNTER_READING LAST_READING, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE, CSC.COUNTER_ID COUNTER_ID, FAA.ASSET_ID ASSET_ID, TO_NUMBER(NULL) CLL_ID, TO_NUMBER(NULL) CLG_ID, CHR.ID KHR_ID , CLE1.ID PARENT_LINE_ID FROM OKC_K_HEADERS_B CHR, OKC_K_LINES_B CLE1, OKC_K_LINES_B CLE2, OKC_K_LINES_B CLE3, OKC_K_LINES_B CLE4, OKC_LINE_STYLES_B LSE1, OKC_LINE_STYLES_B LSE2, OKC_LINE_STYLES_B LSE3, OKC_LINE_STYLES_B LSE4, OKC_K_ITEMS CIM, OKC_K_ITEMS CIM1, CSI_ITEM_INSTANCES CSI, MTL_UNITS_OF_MEASURE_VL UOM, MTL_SYSTEM_ITEMS_VL ITEM, CS_COUNTER_GROUPS CSG, CS_COUNTERS CSC, CS_COUNTER_VALUES CSH, FA_ADDITIONS_B FAA Where chr.id = cle1.dnz_chr_id AND CLE1.LSE_ID = LSE1.ID AND LSE1.LTY_CODE = 'FREE_FORM1' AND CLE2.CLE_ID = CLE1.ID AND CLE2.LSE_ID = LSE2.ID AND LSE2.LTY_CODE = 'FREE_FORM2' AND CLE3.CLE_ID = CLE2.ID AND CLE3.LSE_ID = LSE3.ID AND LSE3.LTY_CODE = 'INST_ITEM' AND CLE3.ID = CIM.CLE_ID AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM' AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID AND CSI.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID AND CSI.INV_MASTER_ORGANIZATION_ID = ITEM.ORGANIZATION_ID and ITEM.PRIMARY_UOM_CODE = UOM.UOM_CODE AND CSI.INSTANCE_ID = CSG.SOURCE_OBJECT_ID AND CSG.COUNTER_GROUP_ID = CSC.COUNTER_GROUP_ID AND CSC.VALID_FLAG = 'Y' AND CSC.UOM_CODE = UOM.UOM_CODE AND CSC.COUNTER_ID = CSH.COUNTER_ID(+) AND (NOT EXISTS (SELECT NULL FROM CS_COUNTER_VALUES CSH1 where CSC.COUNTER_ID = CSH1.COUNTER_ID) OR CSH.VALUE_TIMESTAMP = (SELECT MAX(VALUE_TIMESTAMP) FROM CS_COUNTER_VALUES WHERE COUNTER_ID = CSH.COUNTER_ID AND NVL(VALID_FLAG, 'Y') = 'Y')) AND CLE1.ID = CLE4.CLE_ID AND CLE4.LSE_ID = LSE4.ID AND LSE4.LTY_CODE = 'FIXED_ASSET' AND CLE4.ID = CIM1.CLE_ID AND CIM1.JTOT_OBJECT1_CODE = 'OKX_ASSET' AND CIM1.OBJECT1_ID1 = FAA.ASSET_ID AND NOT EXISTS (SELECT 1 FROM OKL_CNTR_LVLNG_LNS_B WHERE KLE_ID = CSC.COUNTER_ID)

View Text - HTML Formatted

SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, FAA.ASSET_NUMBER ASSET_NUMBER
, ITEM.DESCRIPTION ITEM_DESCRIPTION
, CSI.SERIAL_NUMBER
, CSI.QUANTITY QUANTITY
, CSC.NAME COUNTER_NAME
, CLGV.NAME CONSOLIDATED_COUNTER_NAME
, CSH.VALUE_TIMESTAMP LAST_READING_DATE
, CSH.COUNTER_READING LAST_READING
, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE
, CSC.COUNTER_ID COUNTER_ID
, FAA.ASSET_ID ASSET_ID
, CLL.ID CLL_ID
, CLGV.ID CLG_ID
, CHR.ID KHR_ID
, CLE1.ID PARENT_LINE_ID
FROM OKC_K_HEADERS_B CHR
, OKC_K_LINES_B CLE1
, OKC_K_LINES_B CLE2
, OKC_K_LINES_B CLE3
, OKC_K_LINES_B CLE4
, OKC_LINE_STYLES_B LSE1
, OKC_LINE_STYLES_B LSE2
, OKC_LINE_STYLES_B LSE3
, OKC_LINE_STYLES_B LSE4
, OKC_K_ITEMS CIM
, OKC_K_ITEMS CIM1
, CSI_ITEM_INSTANCES CSI
, MTL_UNITS_OF_MEASURE_VL UOM
, MTL_SYSTEM_ITEMS_VL ITEM
, CS_COUNTER_GROUPS CSG
, CS_COUNTERS CSC
, CS_COUNTER_VALUES CSH
, FA_ADDITIONS_B FAA
, OKL_CNTR_LVLNG_LNS_B CLL
, OKL_CNTR_LVLNG_GRPS_V CLGV
WHERE CHR.ID = CLE1.DNZ_CHR_ID
AND CLE1.LSE_ID = LSE1.ID
AND LSE1.LTY_CODE = 'FREE_FORM1'
AND CLE2.CLE_ID = CLE1.ID
AND CLE2.LSE_ID = LSE2.ID
AND LSE2.LTY_CODE = 'FREE_FORM2'
AND CLE3.CLE_ID = CLE2.ID
AND CLE3.LSE_ID = LSE3.ID
AND LSE3.LTY_CODE = 'INST_ITEM'
AND CLE3.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEM.PRIMARY_UOM_CODE = UOM.UOM_CODE
AND CSI.INSTANCE_ID = CSG.SOURCE_OBJECT_ID
AND CSG.COUNTER_GROUP_ID = CSC.COUNTER_GROUP_ID
AND CSC.VALID_FLAG = 'Y'
AND CSC.UOM_CODE = UOM.UOM_CODE
AND CSC.COUNTER_ID = CSH.COUNTER_ID(+)
AND (NOT EXISTS (SELECT NULL
FROM CS_COUNTER_VALUES CSH1
WHERE CSC.COUNTER_ID = CSH1.COUNTER_ID) OR CSH.VALUE_TIMESTAMP = (SELECT MAX(VALUE_TIMESTAMP )
FROM CS_COUNTER_VALUES
WHERE COUNTER_ID = CSH.COUNTER_ID
AND NVL(VALID_FLAG
, 'Y') = 'Y'))
AND CLE1.ID = CLE4.CLE_ID
AND CLE4.LSE_ID = LSE4.ID
AND LSE4.LTY_CODE = 'FIXED_ASSET'
AND CLE4.ID = CIM1.CLE_ID
AND CIM1.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM1.OBJECT1_ID1 = FAA.ASSET_ID
AND CSC.COUNTER_ID = CLL.KLE_ID
AND CLL.CLG_ID = CLGV.ID UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, FAA.ASSET_NUMBER ASSET_NUMBER
, ITEM.DESCRIPTION ITEM_DESCRIPTION
, CSI.SERIAL_NUMBER SERIAL_NUMBER
, CSI.QUANTITY QUANTITY
, CSC.NAME COUNTER_NAME
, NULL CONSOLIDATED_COUNTER_NAME
, CSH.VALUE_TIMESTAMP LAST_READING_DATE
, CSH.COUNTER_READING LAST_READING
, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE
, CSC.COUNTER_ID COUNTER_ID
, FAA.ASSET_ID ASSET_ID
, TO_NUMBER(NULL) CLL_ID
, TO_NUMBER(NULL) CLG_ID
, CHR.ID KHR_ID
, CLE1.ID PARENT_LINE_ID
FROM OKC_K_HEADERS_B CHR
, OKC_K_LINES_B CLE1
, OKC_K_LINES_B CLE2
, OKC_K_LINES_B CLE3
, OKC_K_LINES_B CLE4
, OKC_LINE_STYLES_B LSE1
, OKC_LINE_STYLES_B LSE2
, OKC_LINE_STYLES_B LSE3
, OKC_LINE_STYLES_B LSE4
, OKC_K_ITEMS CIM
, OKC_K_ITEMS CIM1
, CSI_ITEM_INSTANCES CSI
, MTL_UNITS_OF_MEASURE_VL UOM
, MTL_SYSTEM_ITEMS_VL ITEM
, CS_COUNTER_GROUPS CSG
, CS_COUNTERS CSC
, CS_COUNTER_VALUES CSH
, FA_ADDITIONS_B FAA
WHERE CHR.ID = CLE1.DNZ_CHR_ID
AND CLE1.LSE_ID = LSE1.ID
AND LSE1.LTY_CODE = 'FREE_FORM1'
AND CLE2.CLE_ID = CLE1.ID
AND CLE2.LSE_ID = LSE2.ID
AND LSE2.LTY_CODE = 'FREE_FORM2'
AND CLE3.CLE_ID = CLE2.ID
AND CLE3.LSE_ID = LSE3.ID
AND LSE3.LTY_CODE = 'INST_ITEM'
AND CLE3.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
AND CSI.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND ITEM.PRIMARY_UOM_CODE = UOM.UOM_CODE
AND CSI.INSTANCE_ID = CSG.SOURCE_OBJECT_ID
AND CSG.COUNTER_GROUP_ID = CSC.COUNTER_GROUP_ID
AND CSC.VALID_FLAG = 'Y'
AND CSC.UOM_CODE = UOM.UOM_CODE
AND CSC.COUNTER_ID = CSH.COUNTER_ID(+)
AND (NOT EXISTS (SELECT NULL
FROM CS_COUNTER_VALUES CSH1
WHERE CSC.COUNTER_ID = CSH1.COUNTER_ID) OR CSH.VALUE_TIMESTAMP = (SELECT MAX(VALUE_TIMESTAMP)
FROM CS_COUNTER_VALUES
WHERE COUNTER_ID = CSH.COUNTER_ID
AND NVL(VALID_FLAG
, 'Y') = 'Y'))
AND CLE1.ID = CLE4.CLE_ID
AND CLE4.LSE_ID = LSE4.ID
AND LSE4.LTY_CODE = 'FIXED_ASSET'
AND CLE4.ID = CIM1.CLE_ID
AND CIM1.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM1.OBJECT1_ID1 = FAA.ASSET_ID
AND NOT EXISTS (SELECT 1
FROM OKL_CNTR_LVLNG_LNS_B
WHERE KLE_ID = CSC.COUNTER_ID)