View: OKS_USAGE_ITEM_V

Product:	OKS - Service Contracts
Description:	View for Usage LOV
Implementation/DBA Data:	APPS.OKS_USAGE_ITEM_V

View Text

SELECT IT.CONCATENATED_SEGMENTS NAME
, CG.SOURCE_OBJECT_CODE || ';' || CP.SERIAL_NUMBER || ';' || CP.INSTANCE_NUMBER SOURCE_DETAILS
, IT.DESCRIPTION
, CT.UOM_CODE
, CT.COUNTER_ID ID1
, '#' ID2
, 'OKX_COUNTER' OBJECT_CODE
, CP.INSTANCE_ID SOURCE_ID
, CG.COUNTER_GROUP_ID
, CG.CREATED_FROM_CTR_GRP_TMPL_ID TEMPLATE_COUNTER_GROUP_ID
, CT.NAME COUNTER
, CT.USAGE_ITEM_ID USAGE_ITEM_ID
, CP.OWNER_PARTY_ID
, 'CP' REC_TYPE
, CT.START_DATE_ACTIVE START_DATE_ACTIVE
, CT.END_DATE_ACTIVE END_DATE_ACTIVE
FROM CS_COUNTERS CT
, CS_COUNTER_GROUPS CG
, CSI_ITEM_INSTANCES CP
, MTL_SYSTEM_ITEMS_KFV IT
WHERE CT.COUNTER_GROUP_ID = CG.COUNTER_GROUP_ID
AND CG.SOURCE_OBJECT_CODE = 'CP'
AND CG.SOURCE_OBJECT_ID = CP.INSTANCE_ID
AND CP.INVENTORY_ITEM_ID = IT.INVENTORY_ITEM_ID
AND IT.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT'
, 'ORGANIZATION_ID') UNION ALL SELECT IT.CONCATENATED_SEGMENTS NAME
, CG.SOURCE_OBJECT_CODE || ';' ||KH.CONTRACT_NUMBER || ';' || CONTRACT_NUMBER_MODIFIER SOURCE_DETAILS
, IT.DESCRIPTION DESCRIPTION
, CT.UOM_CODE
, CT.COUNTER_ID ID1
, '#' ID2
, 'OKX_COUNTER' OBJECT_CODE
, KL.ID SOURCE_ID
, CG.COUNTER_GROUP_ID
, CG.CREATED_FROM_CTR_GRP_TMPL_ID TEMPLATE_COUNTER_GROUP_ID
, CT.NAME COUNTER
, CT.USAGE_ITEM_ID USAGE_ITEM_ID
, TO_NUMBER(KP.OBJECT1_ID1) PARTY_ID
, 'PARTY' REC_TYPE
, TRUNC( SYSDATE ) START_DATE_ACTIVE
, TRUNC(SYSDATE ) END_DATE_ACTIVE
FROM CS_COUNTERS CT
, CS_COUNTER_GROUPS CG
, OKC_K_LINES_B KL
, OKC_K_ITEMS KI
, MTL_SYSTEM_ITEMS_KFV IT
, OKC_K_HEADERS_B KH
, OKC_K_PARTY_ROLES_B KP
WHERE CT.COUNTER_GROUP_ID = CG.COUNTER_GROUP_ID
AND CG.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
AND CG.SOURCE_OBJECT_ID = KL.ID
AND KL.ID = KI.CLE_ID
AND KI.OBJECT1_ID1 = IT.INVENTORY_ITEM_ID
AND IT.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT'
, 'ORGANIZATION_ID')
AND KI.DNZ_CHR_ID = KH.ID
AND KH.ID = KP.DNZ_CHR_ID
AND KI.DNZ_CHR_ID = KP.DNZ_CHR_ID
AND KP.CLE_ID IS NULL
AND KP.RLE_CODE IN('CUSTOMER'
, 'SUBSCRIBER')
AND KP.JTOT_OBJECT1_CODE = 'OKX_PARTY'
AND KP.OBJECT1_ID2 = '#'

Columns