View: OKL_CS_ASSET_COUNTERS_UV

Product:	OKL - Leasing and Finance Management
Description:
Implementation/DBA Data:	APPS.OKL_CS_ASSET_COUNTERS_UV

View Text

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)

Columns