VIEW: APPS.AHL_SCHEDULE_MATERIALS_V

Source

View Text - Preformatted

SELECT AWOS.workorder_id, ASML.visit_task_id, AWOS.job_number wo_name, ASML.scheduled_material_id schedule_material_id, ASML.inventory_item_id, MSIK.concatenated_segments, MSIK.primary_uom_code uom_code, MSIK.primary_unit_of_measure uom , ASML.object_version_number, ASML.item_group_id, ASML.rt_oper_material_id, ASML.visit_id, WIRO.DATE_REQUIRED requested_date, WIRO.REQUIRED_QUANTITY requested_quantity , asml.scheduled_date schedule_date, asml.scheduled_quantity schedule_quantity, ASML. workorder_operation_id, ASML.operation_sequence, awos.priority, awos.organization_name, awos.department_name, awos.incident_number, awos.visit_number, nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity, awos.job_status_code, awos.job_status_meaning, asml.scheduled_material_id sales_order_line_id, AHL_PRD_UTIL_PKG.is_unit_locked(AWOS.WORKORDER_ID, NULL, NULL, NULL), (SELECT nvl(SUM(mrv.primary_reservation_quantity),0) FROM mtl_reservations MRV WHERE MRV.INVENTORY_ITEM_ID =WIRO.INVENTORY_ITEM_ID AND MRV. EXTERNAL_SOURCE_CODE = 'AHL' AND MRV.DEMAND_SOURCE_HEADER_ID = WIRO.WIP_ENTITY_ID AND MRV.DEMAND_SOURCE_LINE_ID =WIRO.OPERATION_SEQ_NUM), DECODE(ASML.STATUS,'IN-SERVICE','Y','N') IN_SERVICE, ahl_pp_materials_pvt.get_net_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id) net_quantity from ahl_workorders_v AWOS, ahl_schedule_materials ASML, WIP_REQUIREMENT_OPERATIONS WIRO, mtl_system_items_kfv MSIK WHERE AWOS.visit_task_id = ASML.visit_task_id and ASML.inventory_item_id = MSIK.inventory_item_id and ASML.organization_id = MSIK.organization_id AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID AND asml.status IN ( 'ACTIVE', 'IN-SERVICE', 'HISTORY')

View Text - HTML Formatted

SELECT AWOS.WORKORDER_ID
, ASML.VISIT_TASK_ID
, AWOS.JOB_NUMBER WO_NAME
, ASML.SCHEDULED_MATERIAL_ID SCHEDULE_MATERIAL_ID
, ASML.INVENTORY_ITEM_ID
, MSIK.CONCATENATED_SEGMENTS
, MSIK.PRIMARY_UOM_CODE UOM_CODE
, MSIK.PRIMARY_UNIT_OF_MEASURE UOM
, ASML.OBJECT_VERSION_NUMBER
, ASML.ITEM_GROUP_ID
, ASML.RT_OPER_MATERIAL_ID
, ASML.VISIT_ID
, WIRO.DATE_REQUIRED REQUESTED_DATE
, WIRO.REQUIRED_QUANTITY REQUESTED_QUANTITY
, ASML.SCHEDULED_DATE SCHEDULE_DATE
, ASML.SCHEDULED_QUANTITY SCHEDULE_QUANTITY
, ASML. WORKORDER_OPERATION_ID
, ASML.OPERATION_SEQUENCE
, AWOS.PRIORITY
, AWOS.ORGANIZATION_NAME
, AWOS.DEPARTMENT_NAME
, AWOS.INCIDENT_NUMBER
, AWOS.VISIT_NUMBER
, NVL(AHL_PP_MATERIALS_PVT.GET_ISSUED_QTY(MSIK.ORGANIZATION_ID
, ASML.INVENTORY_ITEM_ID
, ASML.WORKORDER_OPERATION_ID)
, 0) ISSUED_QUANTITY
, AWOS.JOB_STATUS_CODE
, AWOS.JOB_STATUS_MEANING
, ASML.SCHEDULED_MATERIAL_ID SALES_ORDER_LINE_ID
, AHL_PRD_UTIL_PKG.IS_UNIT_LOCKED(AWOS.WORKORDER_ID
, NULL
, NULL
, NULL)
, (SELECT NVL(SUM(MRV.PRIMARY_RESERVATION_QUANTITY)
, 0)
FROM MTL_RESERVATIONS MRV
WHERE MRV.INVENTORY_ITEM_ID =WIRO.INVENTORY_ITEM_ID
AND MRV. EXTERNAL_SOURCE_CODE = 'AHL'
AND MRV.DEMAND_SOURCE_HEADER_ID = WIRO.WIP_ENTITY_ID
AND MRV.DEMAND_SOURCE_LINE_ID =WIRO.OPERATION_SEQ_NUM)
, DECODE(ASML.STATUS
, 'IN-SERVICE'
, 'Y'
, 'N') IN_SERVICE
, AHL_PP_MATERIALS_PVT.GET_NET_QTY(MSIK.ORGANIZATION_ID
, ASML.INVENTORY_ITEM_ID
, ASML.WORKORDER_OPERATION_ID) NET_QUANTITY
FROM AHL_WORKORDERS_V AWOS
, AHL_SCHEDULE_MATERIALS ASML
, WIP_REQUIREMENT_OPERATIONS WIRO
, MTL_SYSTEM_ITEMS_KFV MSIK
WHERE AWOS.VISIT_TASK_ID = ASML.VISIT_TASK_ID
AND ASML.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND ASML.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND AWOS.WIP_ENTITY_ID = WIRO.WIP_ENTITY_ID
AND ASML.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
AND ASML.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
AND ASML.ORGANIZATION_ID = WIRO.ORGANIZATION_ID
AND ASML.STATUS IN ( 'ACTIVE'
, 'IN-SERVICE'
, 'HISTORY')