VIEW: APPS.OKS_USAGE_ITEM_V

Source

View Text - Preformatted

SELECT it.concatenated_segments Name , cp.serial_number || ';' ||cp.instance_number source_details , it.Description , ccb.uom_code , ccb.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 , cct.name COUNTER , ccb.usage_item_id usage_item_id , cp.owner_party_id , 'CP' REC_TYPE , ccb.start_date_active start_date_active , ccb.end_date_active end_date_active , cca.source_object_code source_object_code , CP.INSTANCE_NUMBER , CP.SERIAL_NUMBER , CP.EXTERNAL_REFERENCE from csi_counters_b ccb , csi_counters_tl cct , cs_csi_counter_groups cg , csi_counter_associations cca , csi_item_instances cp , mtl_system_items_kfv it WHERE ccb.counter_id = cct.counter_id AND cct.language = USERENV('LANG') AND ccb.group_id = cg.counter_group_id AND ccb.counter_id = cca.counter_id AND cca.source_object_code = 'CP' AND cca.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') AND cp.inv_master_organization_id= it.organization_id and nvl(cp.active_end_date,sysdate)>=sysdate UNION ALL SELECT it.concatenated_segments Name , kh.contract_number || ';' ||contract_number_modifier||';'||kl.line_number||';'||kl.cognomen source_details , it.description Description , ccb.uom_code , ccb.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 , cct.name COUNTER , ccb.usage_item_id usage_item_id , to_number(NULL) party_id , 'PARTY' REC_TYPE , TRUNC( SYSDATE ) start_date_active , TRUNC(SYSDATE ) end_date_active , cca.source_object_code source_object_code , NULL INSTANCE_NUMBER , NULL SERIAL_NUMBER , NULL EXTERNAL_REFERENCE FROM csi_counters_b ccb , csi_counters_tl cct , cs_csi_counter_groups cg , csi_counter_associations cca , okc_k_lines_v kl , okc_k_items ki , mtl_system_items_kfv it , okc_k_headers_b kh WHERE ccb.counter_id = cct.counter_id AND cct.language = USERENV('LANG') AND ccb.group_id = cg.counter_group_id AND ccb.counter_id = cca.counter_id AND cca.source_object_code = 'CONTRACT_LINE' AND cca.source_object_id = kl.id AND kl.id = ki.cle_id AND ki.object1_id1 = to_char(it.inventory_item_id) AND it.organization_id = sys_context('OKC_CONTEXT','ORGANIZATION_ID') AND ki.dnz_chr_id = kh.id

View Text - HTML Formatted

SELECT IT.CONCATENATED_SEGMENTS NAME
, CP.SERIAL_NUMBER || ';' ||CP.INSTANCE_NUMBER SOURCE_DETAILS
, IT.DESCRIPTION
, CCB.UOM_CODE
, CCB.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
, CCT.NAME COUNTER
, CCB.USAGE_ITEM_ID USAGE_ITEM_ID
, CP.OWNER_PARTY_ID
, 'CP' REC_TYPE
, CCB.START_DATE_ACTIVE START_DATE_ACTIVE
, CCB.END_DATE_ACTIVE END_DATE_ACTIVE
, CCA.SOURCE_OBJECT_CODE SOURCE_OBJECT_CODE
, CP.INSTANCE_NUMBER
, CP.SERIAL_NUMBER
, CP.EXTERNAL_REFERENCE
FROM CSI_COUNTERS_B CCB
, CSI_COUNTERS_TL CCT
, CS_CSI_COUNTER_GROUPS CG
, CSI_COUNTER_ASSOCIATIONS CCA
, CSI_ITEM_INSTANCES CP
, MTL_SYSTEM_ITEMS_KFV IT
WHERE CCB.COUNTER_ID = CCT.COUNTER_ID
AND CCT.LANGUAGE = USERENV('LANG')
AND CCB.GROUP_ID = CG.COUNTER_GROUP_ID
AND CCB.COUNTER_ID = CCA.COUNTER_ID
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND CCA.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')
AND CP.INV_MASTER_ORGANIZATION_ID= IT.ORGANIZATION_ID
AND NVL(CP.ACTIVE_END_DATE
, SYSDATE)>=SYSDATE UNION ALL SELECT IT.CONCATENATED_SEGMENTS NAME
, KH.CONTRACT_NUMBER || ';' ||CONTRACT_NUMBER_MODIFIER||';'||KL.LINE_NUMBER||';'||KL.COGNOMEN SOURCE_DETAILS
, IT.DESCRIPTION DESCRIPTION
, CCB.UOM_CODE
, CCB.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
, CCT.NAME COUNTER
, CCB.USAGE_ITEM_ID USAGE_ITEM_ID
, TO_NUMBER(NULL) PARTY_ID
, 'PARTY' REC_TYPE
, TRUNC( SYSDATE ) START_DATE_ACTIVE
, TRUNC(SYSDATE ) END_DATE_ACTIVE
, CCA.SOURCE_OBJECT_CODE SOURCE_OBJECT_CODE
, NULL INSTANCE_NUMBER
, NULL SERIAL_NUMBER
, NULL EXTERNAL_REFERENCE
FROM CSI_COUNTERS_B CCB
, CSI_COUNTERS_TL CCT
, CS_CSI_COUNTER_GROUPS CG
, CSI_COUNTER_ASSOCIATIONS CCA
, OKC_K_LINES_V KL
, OKC_K_ITEMS KI
, MTL_SYSTEM_ITEMS_KFV IT
, OKC_K_HEADERS_B KH
WHERE CCB.COUNTER_ID = CCT.COUNTER_ID
AND CCT.LANGUAGE = USERENV('LANG')
AND CCB.GROUP_ID = CG.COUNTER_GROUP_ID
AND CCB.COUNTER_ID = CCA.COUNTER_ID
AND CCA.SOURCE_OBJECT_CODE = 'CONTRACT_LINE'
AND CCA.SOURCE_OBJECT_ID = KL.ID
AND KL.ID = KI.CLE_ID
AND KI.OBJECT1_ID1 = TO_CHAR(IT.INVENTORY_ITEM_ID)
AND IT.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT'
, 'ORGANIZATION_ID')
AND KI.DNZ_CHR_ID = KH.ID