VIEW: APPS.AHL_PRD_DISPOSITIONS_V

Source

View Text - Preformatted

SELECT B.DISPOSITION_ID , B.OBJECT_VERSION_NUMBER , B.LAST_UPDATE_DATE , B.LAST_UPDATED_BY , B.CREATION_DATE , B.CREATED_BY , B.LAST_UPDATE_LOGIN , B.WORKORDER_ID , WO.WORKORDER_NAME JOB_NUMBER , WO.STATUS_CODE JOB_STATUS_CODE , B.PART_CHANGE_ID , B.PATH_POSITION_ID , MPP.PATH_POS_COMMON_ID , FND4.MEANING POSITION_REFERENCE , B.ORGANIZATION_ID , B.INVENTORY_ITEM_ID , MTL.CONCATENATED_SEGMENTS ITEM_NUMBER , B.ITEM_REVISION , decode(B.instance_id, null, decode(B.path_position_id, null, 'N', 'Y'), 'Y') TRACKABLE_FLAG , B.ITEM_GROUP_ID , GRP.NAME ITEM_GROUP_NAME , B.CONDITION_ID , COND.STATUS_CODE CONDITION_CODE , B.INSTANCE_ID , CSI.INSTANCE_NUMBER , B.SERIAL_NUMBER , B.LOT_NUMBER , B.IMMEDIATE_DISPOSITION_CODE , FND1.MEANING IMMEDIATE_TYPE , B.SECONDARY_DISPOSITION_CODE , FND2.MEANING SECONDARY_TYPE , B.STATUS_CODE , FND3.MEANING STATUS , B.QUANTITY , B.UOM , B.COMMENTS , B.COLLECTION_ID , B.PRIMARY_SERVICE_REQUEST_ID , B.NON_ROUTINE_WORKORDER_ID , B.WO_OPERATION_ID , OP.OPERATION_SEQUENCE_NUM OPERATION_SEQUENCE , UC.UNIT_CONFIG_HEADER_ID , UC.NAME UNIT_NAME , UC.CSI_ITEM_INSTANCE_ID UNIT_INSTANCE_ID , MC.MC_HEADER_ID , MC.NAME MC_HEADER_NAME , MC.REVISION MC_REVISION , B.ATTRIBUTE_CATEGORY , B.ATTRIBUTE1 , B.ATTRIBUTE2 , B.ATTRIBUTE3 , B.ATTRIBUTE4 , B.ATTRIBUTE5 , B.ATTRIBUTE6 , B.ATTRIBUTE7 , B.ATTRIBUTE8 , B.ATTRIBUTE9 , B.ATTRIBUTE10 , B.ATTRIBUTE11 , B.ATTRIBUTE12 , B.ATTRIBUTE13 , B.ATTRIBUTE14 , B.ATTRIBUTE15 FROM AHL_PRD_DISPOSITIONS_VL B, AHL_WORKORDERS WO, MTL_SYSTEM_ITEMS_KFV MTL, CSI_ITEM_INSTANCES CSI, AHL_ITEM_GROUPS_B GRP, FND_LOOKUPS FND1, FND_LOOKUPS FND2, FND_LOOKUPS FND3, FND_LOOKUPS FND4, AHL_UNIT_CONFIG_HEADERS UC, AHL_MC_HEADERS_B MC, MTL_MATERIAL_STATUSES_VL COND, AHL_VISITS_VL VST, AHL_VISIT_TASKS_VL VTS, AHL_MC_PATH_POSITIONS MPP, AHL_WORKORDER_OPERATIONS OP WHERE B.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+) AND B.ORGANIZATION_ID = MTL.organization_id (+) AND B.INSTANCE_ID = CSI.INSTANCE_ID (+) AND B.ITEM_GROUP_ID = GRP.ITEM_GROUP_ID (+) AND FND1.LOOKUP_TYPE (+)= 'AHL_IMMED_DISP_TYPE' AND B.immediate_disposition_code = FND1.LOOKUP_CODE (+) AND FND2.LOOKUP_TYPE (+)= 'AHL_SECND_DISP_TYPE' AND B.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+) AND FND3.LOOKUP_TYPE (+)= 'AHL_DISP_STATUS' AND B.STATUS_CODE = FND3.LOOKUP_CODE (+) AND B.WORKORDER_ID = WO.WORKORDER_ID AND VST.VISIT_ID=VTS.VISIT_ID AND WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID AND AHL_PRD_DISP_UTIL_PVT.Get_WO_UNIT_ID(WO.workorder_id) = UC.unit_config_header_id(+) AND TRUNC(NVL(UC.active_end_date (+), SYSDATE+1)) > TRUNC(sysdate) AND UC.MASTER_CONFIG_ID = MC.MC_HEADER_ID (+) AND B.condition_id = cond.status_id (+) AND B.path_position_id = MPP.path_position_id (+) AND B.wo_operation_id = OP.workorder_operation_id (+) AND FND4.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE' AND FND4.LOOKUP_CODE (+) = AHL_MC_PATH_POSITION_PVT.get_posref_by_id(B.PATH_POSITION_ID, 'T')

View Text - HTML Formatted

SELECT B.DISPOSITION_ID
, B.OBJECT_VERSION_NUMBER
, B.LAST_UPDATE_DATE
, B.LAST_UPDATED_BY
, B.CREATION_DATE
, B.CREATED_BY
, B.LAST_UPDATE_LOGIN
, B.WORKORDER_ID
, WO.WORKORDER_NAME JOB_NUMBER
, WO.STATUS_CODE JOB_STATUS_CODE
, B.PART_CHANGE_ID
, B.PATH_POSITION_ID
, MPP.PATH_POS_COMMON_ID
, FND4.MEANING POSITION_REFERENCE
, B.ORGANIZATION_ID
, B.INVENTORY_ITEM_ID
, MTL.CONCATENATED_SEGMENTS ITEM_NUMBER
, B.ITEM_REVISION
, DECODE(B.INSTANCE_ID
, NULL
, DECODE(B.PATH_POSITION_ID
, NULL
, 'N'
, 'Y')
, 'Y') TRACKABLE_FLAG
, B.ITEM_GROUP_ID
, GRP.NAME ITEM_GROUP_NAME
, B.CONDITION_ID
, COND.STATUS_CODE CONDITION_CODE
, B.INSTANCE_ID
, CSI.INSTANCE_NUMBER
, B.SERIAL_NUMBER
, B.LOT_NUMBER
, B.IMMEDIATE_DISPOSITION_CODE
, FND1.MEANING IMMEDIATE_TYPE
, B.SECONDARY_DISPOSITION_CODE
, FND2.MEANING SECONDARY_TYPE
, B.STATUS_CODE
, FND3.MEANING STATUS
, B.QUANTITY
, B.UOM
, B.COMMENTS
, B.COLLECTION_ID
, B.PRIMARY_SERVICE_REQUEST_ID
, B.NON_ROUTINE_WORKORDER_ID
, B.WO_OPERATION_ID
, OP.OPERATION_SEQUENCE_NUM OPERATION_SEQUENCE
, UC.UNIT_CONFIG_HEADER_ID
, UC.NAME UNIT_NAME
, UC.CSI_ITEM_INSTANCE_ID UNIT_INSTANCE_ID
, MC.MC_HEADER_ID
, MC.NAME MC_HEADER_NAME
, MC.REVISION MC_REVISION
, B.ATTRIBUTE_CATEGORY
, B.ATTRIBUTE1
, B.ATTRIBUTE2
, B.ATTRIBUTE3
, B.ATTRIBUTE4
, B.ATTRIBUTE5
, B.ATTRIBUTE6
, B.ATTRIBUTE7
, B.ATTRIBUTE8
, B.ATTRIBUTE9
, B.ATTRIBUTE10
, B.ATTRIBUTE11
, B.ATTRIBUTE12
, B.ATTRIBUTE13
, B.ATTRIBUTE14
, B.ATTRIBUTE15
FROM AHL_PRD_DISPOSITIONS_VL B
, AHL_WORKORDERS WO
, MTL_SYSTEM_ITEMS_KFV MTL
, CSI_ITEM_INSTANCES CSI
, AHL_ITEM_GROUPS_B GRP
, FND_LOOKUPS FND1
, FND_LOOKUPS FND2
, FND_LOOKUPS FND3
, FND_LOOKUPS FND4
, AHL_UNIT_CONFIG_HEADERS UC
, AHL_MC_HEADERS_B MC
, MTL_MATERIAL_STATUSES_VL COND
, AHL_VISITS_VL VST
, AHL_VISIT_TASKS_VL VTS
, AHL_MC_PATH_POSITIONS MPP
, AHL_WORKORDER_OPERATIONS OP
WHERE B.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
AND B.ORGANIZATION_ID = MTL.ORGANIZATION_ID (+)
AND B.INSTANCE_ID = CSI.INSTANCE_ID (+)
AND B.ITEM_GROUP_ID = GRP.ITEM_GROUP_ID (+)
AND FND1.LOOKUP_TYPE (+)= 'AHL_IMMED_DISP_TYPE'
AND B.IMMEDIATE_DISPOSITION_CODE = FND1.LOOKUP_CODE (+)
AND FND2.LOOKUP_TYPE (+)= 'AHL_SECND_DISP_TYPE'
AND B.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
AND FND3.LOOKUP_TYPE (+)= 'AHL_DISP_STATUS'
AND B.STATUS_CODE = FND3.LOOKUP_CODE (+)
AND B.WORKORDER_ID = WO.WORKORDER_ID
AND VST.VISIT_ID=VTS.VISIT_ID
AND WO.VISIT_TASK_ID=VTS.VISIT_TASK_ID
AND AHL_PRD_DISP_UTIL_PVT.GET_WO_UNIT_ID(WO.WORKORDER_ID) = UC.UNIT_CONFIG_HEADER_ID(+)
AND TRUNC(NVL(UC.ACTIVE_END_DATE (+)
, SYSDATE+1)) > TRUNC(SYSDATE)
AND UC.MASTER_CONFIG_ID = MC.MC_HEADER_ID (+)
AND B.CONDITION_ID = COND.STATUS_ID (+)
AND B.PATH_POSITION_ID = MPP.PATH_POSITION_ID (+)
AND B.WO_OPERATION_ID = OP.WORKORDER_OPERATION_ID (+)
AND FND4.LOOKUP_TYPE (+) = 'AHL_POSITION_REFERENCE'
AND FND4.LOOKUP_CODE (+) = AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(B.PATH_POSITION_ID
, 'T')