VIEW: APPS.AHL_VISIT_TASK_MATRL_V

Source

View Text - Preformatted

SELECT ASMT.VISIT_ID, ASMT.VISIT_TASK_ID, AVTL.VISIT_TASK_NAME, AVTL.STATUS_CODE TASK_STATUS_CODE, ASMT.REQUESTED_QUANTITY, ASMT.REQUESTED_DATE, (select scheduled_date from ahl_schedule_materials asml where asml.scheduled_material_id = asmt.scheduled_material_id and trunc(scheduled_date) > trunc(requested_date)) SCHEDULED_DATE, asmt.scheduled_quantity SCHEDULED_QUANTITY, ASMT.INVENTORY_ITEM_ID, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER, MTL.DESCRIPTION ITEM_DESCRIPTION, KFV.PRIMARY_UNIT_OF_MEASURE UOM, ASMT.RT_OPER_MATERIAL_ID, ASMT.ITEM_GROUP_ID, ASMT.OPERATION_CODE, ASMT.OPERATION_SEQUENCE, ASMT.SCHEDULED_MATERIAL_ID schedule_material_id, asmt.object_version_number, ASMT.POSITION_PATH_ID, ahl_mc_path_position_pvt.get_posref_by_id(ASMT.POSITION_PATH_ID) POSITION_PATH, RELATIONSHIP_ID, (SELECT name FROM AHL_MC_HEADERS_VL WHERE MC_HEADER_ID = (SELECT mc_header_id FROM AHL_MC_RELATIONSHIPS WHERE relationship_id = ASMT.relationship_id)) NAME, RTOM.REWORK_PERCENT, RTOM.REPLACE_PERCENT, RTOM.ITEM_COMP_DETAIL_ID, (select concatenated_visit_task from ahl_visit_tasks_v avtv where avtv.visit_id = asmt.visit_id and avtv.visit_task_id = asmt.visit_task_id and asmt.scheduled_date is not null) sales_order_line_id, ASMT.STATUS FROM AHL_VISIT_TASKS_VL AVTL, MTL_SYSTEM_ITEMS_KFV KFV, MTL_SYSTEM_ITEMS_TL MTL, AHL_SCHEDULE_MATERIALS ASMT, AHL_RT_OPER_MATERIALS RTOM WHERE AVTL.VISIT_ID = ASMT.VISIT_ID AND AVTL.VISIT_TASK_ID = ASMT.VISIT_TASK_ID AND ASMT.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID(+) AND ASMT.ORGANIZATION_ID = KFV.ORGANIZATION_ID(+) and asmt.rt_oper_material_id = rtom.rt_oper_material_id and NVL(ASMT.STATUS, ' ') <> 'DELETED' AND NVL(AVTL.STATUS_CODE,'X') <> 'DELETED' AND KFV.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID AND KFV.ORGANIZATION_ID = MTL.ORGANIZATION_ID AND MTL.LANGUAGE = userenv('LANG')

View Text - HTML Formatted

SELECT ASMT.VISIT_ID
, ASMT.VISIT_TASK_ID
, AVTL.VISIT_TASK_NAME
, AVTL.STATUS_CODE TASK_STATUS_CODE
, ASMT.REQUESTED_QUANTITY
, ASMT.REQUESTED_DATE
, (SELECT SCHEDULED_DATE
FROM AHL_SCHEDULE_MATERIALS ASML
WHERE ASML.SCHEDULED_MATERIAL_ID = ASMT.SCHEDULED_MATERIAL_ID
AND TRUNC(SCHEDULED_DATE) > TRUNC(REQUESTED_DATE)) SCHEDULED_DATE
, ASMT.SCHEDULED_QUANTITY SCHEDULED_QUANTITY
, ASMT.INVENTORY_ITEM_ID
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, MTL.DESCRIPTION ITEM_DESCRIPTION
, KFV.PRIMARY_UNIT_OF_MEASURE UOM
, ASMT.RT_OPER_MATERIAL_ID
, ASMT.ITEM_GROUP_ID
, ASMT.OPERATION_CODE
, ASMT.OPERATION_SEQUENCE
, ASMT.SCHEDULED_MATERIAL_ID SCHEDULE_MATERIAL_ID
, ASMT.OBJECT_VERSION_NUMBER
, ASMT.POSITION_PATH_ID
, AHL_MC_PATH_POSITION_PVT.GET_POSREF_BY_ID(ASMT.POSITION_PATH_ID) POSITION_PATH
, RELATIONSHIP_ID
, (SELECT NAME
FROM AHL_MC_HEADERS_VL
WHERE MC_HEADER_ID = (SELECT MC_HEADER_ID
FROM AHL_MC_RELATIONSHIPS
WHERE RELATIONSHIP_ID = ASMT.RELATIONSHIP_ID)) NAME
, RTOM.REWORK_PERCENT
, RTOM.REPLACE_PERCENT
, RTOM.ITEM_COMP_DETAIL_ID
, (SELECT CONCATENATED_VISIT_TASK
FROM AHL_VISIT_TASKS_V AVTV
WHERE AVTV.VISIT_ID = ASMT.VISIT_ID
AND AVTV.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
AND ASMT.SCHEDULED_DATE IS NOT NULL) SALES_ORDER_LINE_ID
, ASMT.STATUS
FROM AHL_VISIT_TASKS_VL AVTL
, MTL_SYSTEM_ITEMS_KFV KFV
, MTL_SYSTEM_ITEMS_TL MTL
, AHL_SCHEDULE_MATERIALS ASMT
, AHL_RT_OPER_MATERIALS RTOM
WHERE AVTL.VISIT_ID = ASMT.VISIT_ID
AND AVTL.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
AND ASMT.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID(+)
AND ASMT.ORGANIZATION_ID = KFV.ORGANIZATION_ID(+)
AND ASMT.RT_OPER_MATERIAL_ID = RTOM.RT_OPER_MATERIAL_ID
AND NVL(ASMT.STATUS
, ' ') <> 'DELETED'
AND NVL(AVTL.STATUS_CODE
, 'X') <> 'DELETED'
AND KFV.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND MTL.LANGUAGE = USERENV('LANG')