VIEW: APPS.AHL_MR_EFFECTIVITIES_V

Source

View Text - Preformatted

SELECT distinct EF.MR_EFFECTIVITY_ID, EF.OBJECT_VERSION_NUMBER, EF.MR_HEADER_ID, DECODE(EF.RELATIONSHIP_ID, null, MTL.INVENTORY_ITEM_ID, null), DECODE(EF.RELATIONSHIP_ID, null, MTL.CONCATENATED_SEGMENTS, null), EF.RELATIONSHIP_ID, decode(EF.relationship_id,null, null, AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(ef.relationship_id)), DECODE(EF.RELATIONSHIP_ID,null,null,MTL.INVENTORY_ITEM_ID), DECODE(EF.RELATIONSHIP_ID, null, null, MTL.CONCATENATED_SEGMENTS), EF.PC_NODE_ID, PC.NAME, EF.NAME, EF.THRESHOLD_DATE, EF.PROGRAM_DURATION, EF.PROGRAM_DURATION_UOM_CODE, UOM.MEANING, EF.DEFAULT_FLAG, DEF.MEANING, EF.ATTRIBUTE_CATEGORY, EF.ATTRIBUTE1, EF.ATTRIBUTE2, EF.ATTRIBUTE3, EF.ATTRIBUTE4, EF.ATTRIBUTE5, EF.ATTRIBUTE6, EF.ATTRIBUTE7, EF.ATTRIBUTE8, EF.ATTRIBUTE9, EF.ATTRIBUTE10, EF.ATTRIBUTE11, EF.ATTRIBUTE12, EF.ATTRIBUTE13, EF.ATTRIBUTE14, EF.ATTRIBUTE15, EF.application_usg_code From AHL_MR_EFFECTIVITIES_APP_V EF, MTL_SYSTEM_ITEMS_KFV MTL, AHL_PC_NODES_B PC, AHL_POSITION_ALTERNATES_V PA, FND_LOOKUP_VALUES_VL UOM, FND_LOOKUP_VALUES_VL DEF WHERE MTL.inventory_item_id (+) = EF.inventory_item_id and PC.pc_node_id (+) = EF.pc_node_id and PA.relationship_id (+) = EF.relationship_id and PA.inventory_item_id (+) = EF.inventory_item_id and UOM.lookup_code (+) = EF.program_duration_uom_code and UOM.lookup_type (+) = 'AHL_UMP_TIME_UOM' and DEF.lookup_code (+) = EF.default_flag and DEF.lookup_type (+) = 'AHL_YES_NO_TYPE'

View Text - HTML Formatted

SELECT DISTINCT EF.MR_EFFECTIVITY_ID
, EF.OBJECT_VERSION_NUMBER
, EF.MR_HEADER_ID
, DECODE(EF.RELATIONSHIP_ID
, NULL
, MTL.INVENTORY_ITEM_ID
, NULL)
, DECODE(EF.RELATIONSHIP_ID
, NULL
, MTL.CONCATENATED_SEGMENTS
, NULL)
, EF.RELATIONSHIP_ID
, DECODE(EF.RELATIONSHIP_ID
, NULL
, NULL
, AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(EF.RELATIONSHIP_ID))
, DECODE(EF.RELATIONSHIP_ID
, NULL
, NULL
, MTL.INVENTORY_ITEM_ID)
, DECODE(EF.RELATIONSHIP_ID
, NULL
, NULL
, MTL.CONCATENATED_SEGMENTS)
, EF.PC_NODE_ID
, PC.NAME
, EF.NAME
, EF.THRESHOLD_DATE
, EF.PROGRAM_DURATION
, EF.PROGRAM_DURATION_UOM_CODE
, UOM.MEANING
, EF.DEFAULT_FLAG
, DEF.MEANING
, EF.ATTRIBUTE_CATEGORY
, EF.ATTRIBUTE1
, EF.ATTRIBUTE2
, EF.ATTRIBUTE3
, EF.ATTRIBUTE4
, EF.ATTRIBUTE5
, EF.ATTRIBUTE6
, EF.ATTRIBUTE7
, EF.ATTRIBUTE8
, EF.ATTRIBUTE9
, EF.ATTRIBUTE10
, EF.ATTRIBUTE11
, EF.ATTRIBUTE12
, EF.ATTRIBUTE13
, EF.ATTRIBUTE14
, EF.ATTRIBUTE15
, EF.APPLICATION_USG_CODE
FROM AHL_MR_EFFECTIVITIES_APP_V EF
, MTL_SYSTEM_ITEMS_KFV MTL
, AHL_PC_NODES_B PC
, AHL_POSITION_ALTERNATES_V PA
, FND_LOOKUP_VALUES_VL UOM
, FND_LOOKUP_VALUES_VL DEF
WHERE MTL.INVENTORY_ITEM_ID (+) = EF.INVENTORY_ITEM_ID
AND PC.PC_NODE_ID (+) = EF.PC_NODE_ID
AND PA.RELATIONSHIP_ID (+) = EF.RELATIONSHIP_ID
AND PA.INVENTORY_ITEM_ID (+) = EF.INVENTORY_ITEM_ID
AND UOM.LOOKUP_CODE (+) = EF.PROGRAM_DURATION_UOM_CODE
AND UOM.LOOKUP_TYPE (+) = 'AHL_UMP_TIME_UOM'
AND DEF.LOOKUP_CODE (+) = EF.DEFAULT_FLAG
AND DEF.LOOKUP_TYPE (+) = 'AHL_YES_NO_TYPE'