VIEW: APPS.AHL_JOB_OPER_MATERIALS_V

Source

View Text - Preformatted

SELECT ASML.SCHEDULED_MATERIAL_ID, ASML.VISIT_TASK_ID, AWOS.WORKORDER_ID, ASML.INVENTORY_ITEM_ID, MSIv.CONCATENATED_SEGMENTS, WIPR.REQUIRED_QUANTITY REQUESTED_QUANTITY, ASML.ITEM_GROUP_ID, ASML.RT_OPER_MATERIAL_ID, MSIV.PRIMARY_UNIT_OF_MEASURE UOM, ASML.OBJECT_VERSION_NUMBER, AVST.VISIT_ID, AVST.VISIT_NUMBER, wipr.date_required REQUESTED_DATE, asml.scheduled_date SCHEDULE_DATE, asml.scheduled_quantity SCHEDULE_QUANTITY, wipr.operation_seq_num OPERATION_SEQUENCE, MSIv.DESCRIPTION, MSIV.MRP_PLANNING_CODE , AWOP.OPERATION_CODE, ASML.WORKORDER_OPERATION_ID, nvl(ahl_pp_materials_pvt.get_issued_qty(msiv.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity, asml.scheduled_material_id sales_order_line_id, AWOS.WIP_ENTITY_ID, AWOS.WORKORDER_NAME, AVST.DEPARTMENT_ID, AVST.ORGANIZATION_ID, (SELECT nvl(SUM(mrv.primary_reservation_quantity),0) FROM mtl_reservations MRV WHERE MRV.INVENTORY_ITEM_ID = WIPR.INVENTORY_ITEM_ID AND MRV. EXTERNAL_SOURCE_CODE = 'AHL' AND MRV.DEMAND_SOURCE_HEADER_ID = WIPR.WIP_ENTITY_ID AND MRV.DEMAND_SOURCE_LINE_ID =WIPR.OPERATION_SEQ_NUM), ASML.status, ahl_pp_materials_pvt.get_net_qty(msiv.organization_id, asml.inventory_item_id, asml.workorder_operation_id) net_quantity FROM AHL_WORKORDERS AWOS, AHL_WORKORDER_OPERATIONS_V AWOP, AHL_SCHEDULE_MATERIALS ASML, wip_requirement_operations wipr, MTL_SYSTEM_ITEMS_VL MSIV, AHL_VISITS_VL AVST WHERE AWOS.VISIT_TASK_ID = ASML.VISIT_TASK_ID AND ASML.VISIT_ID = AVST.VISIT_ID and awos.wip_entity_id = wipr.wip_entity_id and asml.operation_sequence = wipr.operation_seq_num and asml.inventory_item_id = wipr.inventory_item_id and asml.organization_id = wipr.organization_id AND AWOP.WORKORDER_OPERATION_ID = ASML.WORKORDER_OPERATION_ID AND asml.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID AND ASML.ORGANIZATION_ID = MSIV.ORGANIZATION_ID AND asml.status IN ('ACTIVE', 'IN-SERVICE', 'HISTORY')

View Text - HTML Formatted

SELECT ASML.SCHEDULED_MATERIAL_ID
, ASML.VISIT_TASK_ID
, AWOS.WORKORDER_ID
, ASML.INVENTORY_ITEM_ID
, MSIV.CONCATENATED_SEGMENTS
, WIPR.REQUIRED_QUANTITY REQUESTED_QUANTITY
, ASML.ITEM_GROUP_ID
, ASML.RT_OPER_MATERIAL_ID
, MSIV.PRIMARY_UNIT_OF_MEASURE UOM
, ASML.OBJECT_VERSION_NUMBER
, AVST.VISIT_ID
, AVST.VISIT_NUMBER
, WIPR.DATE_REQUIRED REQUESTED_DATE
, ASML.SCHEDULED_DATE SCHEDULE_DATE
, ASML.SCHEDULED_QUANTITY SCHEDULE_QUANTITY
, WIPR.OPERATION_SEQ_NUM OPERATION_SEQUENCE
, MSIV.DESCRIPTION
, MSIV.MRP_PLANNING_CODE
, AWOP.OPERATION_CODE
, ASML.WORKORDER_OPERATION_ID
, NVL(AHL_PP_MATERIALS_PVT.GET_ISSUED_QTY(MSIV.ORGANIZATION_ID
, ASML.INVENTORY_ITEM_ID
, ASML.WORKORDER_OPERATION_ID)
, 0) ISSUED_QUANTITY
, ASML.SCHEDULED_MATERIAL_ID SALES_ORDER_LINE_ID
, AWOS.WIP_ENTITY_ID
, AWOS.WORKORDER_NAME
, AVST.DEPARTMENT_ID
, AVST.ORGANIZATION_ID
, (SELECT NVL(SUM(MRV.PRIMARY_RESERVATION_QUANTITY)
, 0)
FROM MTL_RESERVATIONS MRV
WHERE MRV.INVENTORY_ITEM_ID = WIPR.INVENTORY_ITEM_ID
AND MRV. EXTERNAL_SOURCE_CODE = 'AHL'
AND MRV.DEMAND_SOURCE_HEADER_ID = WIPR.WIP_ENTITY_ID
AND MRV.DEMAND_SOURCE_LINE_ID =WIPR.OPERATION_SEQ_NUM)
, ASML.STATUS
, AHL_PP_MATERIALS_PVT.GET_NET_QTY(MSIV.ORGANIZATION_ID
, ASML.INVENTORY_ITEM_ID
, ASML.WORKORDER_OPERATION_ID) NET_QUANTITY
FROM AHL_WORKORDERS AWOS
, AHL_WORKORDER_OPERATIONS_V AWOP
, AHL_SCHEDULE_MATERIALS ASML
, WIP_REQUIREMENT_OPERATIONS WIPR
, MTL_SYSTEM_ITEMS_VL MSIV
, AHL_VISITS_VL AVST
WHERE AWOS.VISIT_TASK_ID = ASML.VISIT_TASK_ID
AND ASML.VISIT_ID = AVST.VISIT_ID
AND AWOS.WIP_ENTITY_ID = WIPR.WIP_ENTITY_ID
AND ASML.OPERATION_SEQUENCE = WIPR.OPERATION_SEQ_NUM
AND ASML.INVENTORY_ITEM_ID = WIPR.INVENTORY_ITEM_ID
AND ASML.ORGANIZATION_ID = WIPR.ORGANIZATION_ID
AND AWOP.WORKORDER_OPERATION_ID = ASML.WORKORDER_OPERATION_ID
AND ASML.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID
AND ASML.ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND ASML.STATUS IN ('ACTIVE'
, 'IN-SERVICE'
, 'HISTORY')