VIEW: APPS.OKS_USAGE_COUNTERS_V

Source

View Text - Preformatted

SELECT CG.COUNTER_GROUP_ID, CT.COUNTER_ID, CG.NAME || '.' || CTL.NAME NAME, CT.COUNTER_TYPE TYPE, CT.UOM_CODE UOM_CODE, CV.NET_READING NET_READING, CV.VALUE_TIMESTAMP VALUE_TIMESTAMP, CCA.SOURCE_OBJECT_ID SOURCE_OBJECT_ID, CCA.SOURCE_OBJECT_CODE SOURCE_OBJECT_CODE, CT.USAGE_ITEM_ID USAGE_ITEM_ID, IT.NAME SOURCE_DETAILS, CII.SERIAL_NUMBER CURRENT_SERIAL_NUMBER, TO_CHAR(CII.INSTANCE_NUMBER) REFERENCE_NUMBER, CG.TEMPLATE_FLAG FROM OKX_SYSTEM_ITEMS_V IT, CSI_ITEM_INSTANCES CII, CS_CSI_COUNTER_GROUPS CG, CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_B CT, CSI_COUNTERS_TL CTL, CSI_COUNTER_READINGS CV WHERE IT.ID1 = CII.INVENTORY_ITEM_ID AND IT.ORGANIZATION_ID = OKC_CONTEXT.GET_OKC_ORGANIZATION_ID AND CCA.SOURCE_OBJECT_ID = CII.INSTANCE_ID AND CCA.SOURCE_OBJECT_CODE = 'CP' AND CT.COUNTER_ID = CCA.COUNTER_ID AND CT.GROUP_ID = CG.COUNTER_GROUP_ID AND CT.COUNTER_ID = CTL.COUNTER_ID AND CTL.LANGUAGE = USERENV('LANG') AND CV.COUNTER_ID(+) = CT.COUNTER_ID AND CV.COUNTER_VALUE_ID(+) = OKS_AUTH_UTIL_PVT.GET_NET_READING(CT.COUNTER_ID) UNION SELECT CG.COUNTER_GROUP_ID COUNTER_GROUP_ID, CT.COUNTER_ID COUNTER_ID, CG.NAME || '.' || CTL.NAME NAME, CT.COUNTER_TYPE TYPE, CT.UOM_CODE UOM_CODE, CV.NET_READING NET_READING, CV.VALUE_TIMESTAMP VALUE_TIMESTAMP, CCA.SOURCE_OBJECT_ID SOURCE_OBJECT_ID, CCA.SOURCE_OBJECT_CODE SOURCE_OBJECT_CODE, CT.USAGE_ITEM_ID USAGE_ITEM_ID, KL.NAME || '-' || KH.CONTRACT_NUMBER SOURCE_DETAILS, NULL CURRENT_SERIAL_NUMBER, NULL REFERENCE_NUMBER, CG.TEMPLATE_FLAG FROM CS_CSI_COUNTER_GROUPS CG, CSI_COUNTER_ASSOCIATIONS CCA, CSI_COUNTERS_B CT, CSI_COUNTERS_TL CTL, CSI_COUNTER_READINGS CV, OKC_K_HEADERS_V KH, OKC_K_LINES_V KL WHERE CG.COUNTER_GROUP_ID = CT.GROUP_ID AND CT.USAGE_ITEM_ID IS NOT NULL AND CT.COUNTER_ID = CCA.COUNTER_ID AND CCA.SOURCE_OBJECT_CODE = 'CONTRACT_LINE' AND CCA.SOURCE_OBJECT_ID = KL.ID AND KH.ID = KL.DNZ_CHR_ID AND CT.COUNTER_ID = CTL.COUNTER_ID AND CTL.LANGUAGE = USERENV('LANG') AND CV.COUNTER_ID(+) = CT.COUNTER_ID AND CV.COUNTER_VALUE_ID(+) = OKS_AUTH_UTIL_PVT.GET_NET_READING(CT.COUNTER_ID)

View Text - HTML Formatted

SELECT CG.COUNTER_GROUP_ID
, CT.COUNTER_ID
, CG.NAME || '.' || CTL.NAME NAME
, CT.COUNTER_TYPE TYPE
, CT.UOM_CODE UOM_CODE
, CV.NET_READING NET_READING
, CV.VALUE_TIMESTAMP VALUE_TIMESTAMP
, CCA.SOURCE_OBJECT_ID SOURCE_OBJECT_ID
, CCA.SOURCE_OBJECT_CODE SOURCE_OBJECT_CODE
, CT.USAGE_ITEM_ID USAGE_ITEM_ID
, IT.NAME SOURCE_DETAILS
, CII.SERIAL_NUMBER CURRENT_SERIAL_NUMBER
, TO_CHAR(CII.INSTANCE_NUMBER) REFERENCE_NUMBER
, CG.TEMPLATE_FLAG
FROM OKX_SYSTEM_ITEMS_V IT
, CSI_ITEM_INSTANCES CII
, CS_CSI_COUNTER_GROUPS CG
, CSI_COUNTER_ASSOCIATIONS CCA
, CSI_COUNTERS_B CT
, CSI_COUNTERS_TL CTL
, CSI_COUNTER_READINGS CV
WHERE IT.ID1 = CII.INVENTORY_ITEM_ID
AND IT.ORGANIZATION_ID = OKC_CONTEXT.GET_OKC_ORGANIZATION_ID
AND CCA.SOURCE_OBJECT_ID = CII.INSTANCE_ID
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND CT.COUNTER_ID = CCA.COUNTER_ID
AND CT.GROUP_ID = CG.COUNTER_GROUP_ID
AND CT.COUNTER_ID = CTL.COUNTER_ID
AND CTL.LANGUAGE = USERENV('LANG')
AND CV.COUNTER_ID(+) = CT.COUNTER_ID
AND CV.COUNTER_VALUE_ID(+) = OKS_AUTH_UTIL_PVT.GET_NET_READING(CT.COUNTER_ID) UNION SELECT CG.COUNTER_GROUP_ID COUNTER_GROUP_ID
, CT.COUNTER_ID COUNTER_ID
, CG.NAME || '.' || CTL.NAME NAME
, CT.COUNTER_TYPE TYPE
, CT.UOM_CODE UOM_CODE
, CV.NET_READING NET_READING
, CV.VALUE_TIMESTAMP VALUE_TIMESTAMP
, CCA.SOURCE_OBJECT_ID SOURCE_OBJECT_ID
, CCA.SOURCE_OBJECT_CODE SOURCE_OBJECT_CODE
, CT.USAGE_ITEM_ID USAGE_ITEM_ID
, KL.NAME || '-' || KH.CONTRACT_NUMBER SOURCE_DETAILS
, NULL CURRENT_SERIAL_NUMBER
, NULL REFERENCE_NUMBER
, CG.TEMPLATE_FLAG
FROM CS_CSI_COUNTER_GROUPS CG
, CSI_COUNTER_ASSOCIATIONS CCA
, CSI_COUNTERS_B CT
, CSI_COUNTERS_TL CTL
, CSI_COUNTER_READINGS CV
, OKC_K_HEADERS_V KH
, OKC_K_LINES_V KL
WHERE CG.COUNTER_GROUP_ID = CT.GROUP_ID
AND CT.USAGE_ITEM_ID IS NOT NULL
AND CT.COUNTER_ID = CCA.COUNTER_ID
AND CCA.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
AND CCA.SOURCE_OBJECT_ID = KL.ID
AND KH.ID = KL.DNZ_CHR_ID
AND CT.COUNTER_ID = CTL.COUNTER_ID
AND CTL.LANGUAGE = USERENV('LANG')
AND CV.COUNTER_ID(+) = CT.COUNTER_ID
AND CV.COUNTER_VALUE_ID(+) = OKS_AUTH_UTIL_PVT.GET_NET_READING(CT.COUNTER_ID)