The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(TRANSACTION_QUANTITY)
FROM MTL_ONHAND_QUANTITIES
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = P_ITEM_ID
AND LOCATOR_ID = p_locator_id
AND SUBINVENTORY_CODE = p_SUBINVENTORY;
SELECT SUM(TRANSACTION_QUANTITY)
FROM MTL_ONHAND_QUANTITIES
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item_id
AND LOCATOR_ID <> p_locator_id;
SELECT VTS.instance_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id = p_item_instance_id
AND WO.status_code <> 22
AND ROWNUM < 2;
SELECT * FROM(
SELECT ASML.scheduled_material_id,AWOS.workorder_id,AWOS.job_number,AWOS.job_status_code,AWOS.job_status_meaning,ASML.operation_sequence, MSIK.concatenated_segments ,
MSIK.description, MSIK.primary_uom_code,MSIK.primary_unit_of_measure,
WIRO.REQUIRED_QUANTITY , WIRO.DATE_REQUIRED,
asml.scheduled_quantity ,asml.scheduled_date ,
nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
asml.inventory_item_id,
ASML.organization_id,
AWOS.wip_entity_id,
AWOS.visit_id,
DECODE(MSIK.SERIAL_NUMBER_CONTROL_CODE,1,'N','Y') Is_Serialized,
DECODE(NVL(MSIK.LOT_CONTROL_CODE,-1),1,'N','Y') Is_Lot_Controlled,
DECODE(NVL(MSIK.REVISION_QTY_CONTROL_CODE,-1),1,'N','Y') Is_Revision_Controlled,
GET_ONHAND_AVAILABLE(ASML.organization_id,ASML.inventory_item_id,
p_subinventory_code,p_locator_id) AVAILABLE_QUANTITY,
GET_ONHAND_NOTAVAILABLE(ASML.organization_id,ASML.inventory_item_id,
p_subinventory_code,p_locator_id) NOT_AVAILABLE_QUANTITY,
ASML.requested_quantity QTY_PER_ASSEMBLY,
ASML.scheduled_date EXCEPTION_DATE,
(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) RESERVED_QUANTITY
from ahl_search_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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id IN(
SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
UNION
SELECT
SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS
WHERE 1=1
START WITH OBJECT_ID = p_root_instance_id /*root instance id*/
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
--CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
)) )REQ WHERE
REQ.concatenated_segments LIKE NVL(p_part_number,'%')
AND REQ.description LIKE NVL(p_part_desc,'%')
AND REQ.job_number LIKE NVL(p_workorder_name,'%')
AND REQ.REQUIRED_QUANTITY > (REQ.issued_quantity + REQ.AVAILABLE_QUANTITY)
AND decode(p_fetch_mode,'UM',REQ.NOT_AVAILABLE_QUANTITY,1) > 0
AND decode(p_fetch_mode,'UI',REQ.REQUIRED_QUANTITY - (REQ.issued_quantity +
REQ.AVAILABLE_QUANTITY + REQ.NOT_AVAILABLE_QUANTITY ),1) > 0;
SELECT * FROM(
SELECT ASML.scheduled_material_id,AWOS.workorder_id,AWOS.job_number,AWOS.job_status_code,AWOS.job_status_meaning,ASML.operation_sequence, MSIK.concatenated_segments ,
MSIK.description, MSIK.primary_uom_code,MSIK.primary_unit_of_measure,
WIRO.REQUIRED_QUANTITY , WIRO.DATE_REQUIRED,
asml.scheduled_quantity ,asml.scheduled_date ,
nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
asml.inventory_item_id,
ASML.organization_id,
AWOS.wip_entity_id,
AWOS.visit_id,
DECODE(MSIK.SERIAL_NUMBER_CONTROL_CODE,1,'N','Y') Is_Serialized,
DECODE(NVL(MSIK.LOT_CONTROL_CODE,-1),1,'N','Y') Is_Lot_Controlled,
DECODE(NVL(MSIK.REVISION_QTY_CONTROL_CODE,-1),1,'N','Y') Is_Revision_Controlled,
GET_ONHAND_AVAILABLE(ASML.organization_id,ASML.inventory_item_id,
p_subinventory_code,p_locator_id) AVAILABLE_QUANTITY,
GET_ONHAND_NOTAVAILABLE(ASML.organization_id,ASML.inventory_item_id,
p_subinventory_code,p_locator_id) NOT_AVAILABLE_QUANTITY,
ASML.requested_quantity QTY_PER_ASSEMBLY,
ASML.scheduled_date EXCEPTION_DATE,
(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) RESERVED_QUANTITY
from ahl_search_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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
))REQ WHERE
REQ.concatenated_segments LIKE NVL(p_part_number,'%')
AND REQ.description LIKE NVL(p_part_desc,'%')
AND REQ.job_number LIKE NVL(p_workorder_name,'%')
AND REQ.REQUIRED_QUANTITY > (REQ.issued_quantity + REQ.AVAILABLE_QUANTITY)
AND decode(p_fetch_mode,'UM',REQ.NOT_AVAILABLE_QUANTITY,1) > 0
AND decode(p_fetch_mode,'UI',REQ.REQUIRED_QUANTITY - (REQ.issued_quantity +
REQ.AVAILABLE_QUANTITY + REQ.NOT_AVAILABLE_QUANTITY ),1) > 0;
SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;
select msn.serial_number , msn.lot_number ,msn.revision,
msn.current_subinventory_code,msn.current_locator_id,
decode(msi.segment19, null, mil.concatenated_segments, INV_PROJECT.GET_LOCSEGS(mil.concatenated_segments) ||
fnd_flex_ext.get_delimiter('INV', 'MTLL', 101) || INV_ProjectLocator_PUB.get_project_number(msi.segment19) ||
fnd_flex_ext.get_delimiter('INV', 'MTLL', 101) || INV_ProjectLocator_PUB.get_task_number(msi.segment20))
locator_segments
from mtl_serial_numbers msn,
mtl_system_items_kfv mkfv,
mtl_item_locations_kfv mil,
mtl_item_locations msi
WHERE msi.inventory_item_id = mkfv.inventory_item_id
and msi.organization_id=mkfv.organization_id
and msn.inventory_item_id = mkfv.inventory_item_id
and msn.current_organization_id=mkfv.organization_id
and msn.current_locator_id = mil.INVENTORY_locatION_ID (+)
and msn.current_locator_id = msi.INVENTORY_locatION_ID (+)
and mkfv.serial_number_control_code <> 1
and msn.current_status=3
and msn.inventory_item_id = p_inventory_item_id
and msn.current_organization_id = p_org_id
and msn.current_locator_id <> p_locator_id;
SELECT VTS.instance_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id = p_item_instance_id
AND WO.status_code <> 22
AND ROWNUM < 2;
SELECT * FROM(
SELECT AWOS.workorder_id,AWOS.job_number,AWOS.job_status_code,AWOS.job_status_meaning,ASML.operation_sequence, MSIK.concatenated_segments ,
MSIK.description, MSIK.primary_uom_code,MSIK.primary_unit_of_measure,
WIRO.REQUIRED_QUANTITY , WIRO.DATE_REQUIRED,
asml.scheduled_quantity ,asml.scheduled_date ,
nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
asml.inventory_item_id,
ASML.organization_id,
ASML.scheduled_material_id,
AWOS.wip_entity_id,
AWOS.visit_id,
DECODE(MSIK.SERIAL_NUMBER_CONTROL_CODE,1,'N','Y') Is_Serialized,
DECODE(NVL(MSIK.LOT_CONTROL_CODE,-1),1,'N','Y') Is_Lot_Controlled,
DECODE(NVL(MSIK.REVISION_QTY_CONTROL_CODE,-1),1,'N','Y') Is_Revision_Controlled,
GET_ONHAND_AVAILABLE(ASML.organization_id,ASML.inventory_item_id,
p_subinventory_code,p_locator_id) AVAILABLE_QUANTITY,
ASML.requested_quantity QTY_PER_ASSEMBLY,
ASML.scheduled_date EXCEPTION_DATE,
(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) RESERVED_QUANTITY
from ahl_search_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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id IN(
SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
UNION
SELECT
SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS
WHERE 1=1
START WITH OBJECT_ID = p_root_instance_id /*root instance id*/
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
--CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
)))REQ WHERE
REQ.concatenated_segments LIKE NVL(p_part_number,'%')
AND REQ.description LIKE NVL(p_part_desc,'%')
AND REQ.job_number LIKE NVL(p_workorder_name,'%')
AND REQ.REQUIRED_QUANTITY > REQ.issued_quantity
AND AVAILABLE_QUANTITY > 0;
SELECT * FROM(SELECT AWOS.workorder_id,AWOS.job_number,AWOS.job_status_code,AWOS.job_status_meaning,ASML.operation_sequence, MSIK.concatenated_segments ,
MSIK.description, MSIK.primary_uom_code,MSIK.primary_unit_of_measure,
WIRO.REQUIRED_QUANTITY , WIRO.DATE_REQUIRED,
asml.scheduled_quantity ,asml.scheduled_date ,
nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0) issued_quantity,
asml.inventory_item_id,
ASML.organization_id,
ASML.scheduled_material_id,
AWOS.wip_entity_id,
AWOS.visit_id,
DECODE(MSIK.SERIAL_NUMBER_CONTROL_CODE,1,'N','Y') Is_Serialized,
DECODE(NVL(MSIK.LOT_CONTROL_CODE,-1),1,'N','Y') Is_Lot_Controlled,
DECODE(NVL(MSIK.REVISION_QTY_CONTROL_CODE,-1),1,'N','Y') Is_Revision_Controlled,
GET_ONHAND_AVAILABLE(ASML.organization_id,ASML.inventory_item_id,
p_subinventory_code,p_locator_id) AVAILABLE_QUANTITY,
ASML.requested_quantity QTY_PER_ASSEMBLY,
ASML.scheduled_date EXCEPTION_DATE,
(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) RESERVED_QUANTITY
from ahl_search_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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
))REQ WHERE
REQ.concatenated_segments LIKE NVL(p_part_number,'%')
AND REQ.description LIKE NVL(p_part_desc,'%')
AND REQ.job_number LIKE NVL(p_workorder_name,'%')
AND REQ.REQUIRED_QUANTITY > REQ.issued_quantity
AND AVAILABLE_QUANTITY > 0;
SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;
select msn.serial_number , msn.lot_number ,msn.revision
from mtl_serial_numbers msn,
mtl_system_items_kfv mkfv,
mtl_item_locations_kfv mil
where msn.inventory_item_id = mkfv.inventory_item_id
and msn.current_organization_id=mkfv.organization_id
and msn.current_locator_id = mil.INVENTORY_locatION_ID (+)
and mkfv.serial_number_control_code <> 1
and msn.current_status=3
and msn.inventory_item_id = p_inventory_item_id
and msn.current_organization_id = p_org_id
and msn.current_locator_id = p_locator_id
and msn.current_subinventory_code = p_subinventory_code;
SELECT COUNT(*)
FROM AHL_SEARCH_WORKORDERS_V
WHERE visit_id = p_visit_id
AND JOB_STATUS_CODE IN('4','5','7','12')
AND workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id = p_item_instance_id);
SELECT COUNT(*)
FROM AHL_SEARCH_WORKORDERS_V
WHERE visit_id = p_visit_id
AND JOB_STATUS_CODE IN('4','5','7','12')
AND workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id IN(
SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
UNION
SELECT
SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS
WHERE 1=1
START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
--CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
));
SELECT COUNT(*)
FROM AHL_SEARCH_WORKORDERS_V
WHERE visit_id = p_visit_id
AND JOB_STATUS_CODE <> '22'
AND workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id = p_item_instance_id);
SELECT COUNT(*)
FROM AHL_SEARCH_WORKORDERS_V
WHERE visit_id = p_visit_id
AND JOB_STATUS_CODE <> '22'
AND workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id IN(
SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
UNION
SELECT
SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS
WHERE 1=1
START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
--CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
));
SELECT COUNT(*)
FROM AHL_SEARCH_WORKORDERS_V
WHERE visit_id = p_visit_id
AND JOB_STATUS_CODE IN('4','5','7','12');
SELECT COUNT(*)
FROM AHL_SEARCH_WORKORDERS_V
WHERE visit_id = p_visit_id
AND JOB_STATUS_CODE <> '22';
SELECT SUM(WIRO.REQUIRED_QUANTITY)
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id = p_item_instance_id);
SELECT SUM(WIRO.REQUIRED_QUANTITY)
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id IN(
SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
UNION
SELECT
SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS
WHERE 1=1
START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
--CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
));
SELECT SUM(WIRO.REQUIRED_QUANTITY)
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id);
SELECT SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id = p_item_instance_id);
SELECT SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id IN(
SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
UNION
SELECT
SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS
WHERE 1=1
START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
--CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
));
SELECT SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id);
SELECT SUM(WIRO.REQUIRED_QUANTITY) REQUIRED_QUANTITY,
SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty,
SUM(GET_ONHAND_AVAILABLE(
ASML.organization_id,
ASML.inventory_item_id,
p_subinventory_code,
p_locator_id)) available_quantity
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id = p_item_instance_id)
GROUP BY asml.inventory_item_id,ASML.organization_id,AWOS.WIP_ENTITY_ID,ASML.OPERATION_SEQUENCE;
SELECT SUM(WIRO.REQUIRED_QUANTITY) REQUIRED_QUANTITY,
SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty,
SUM(GET_ONHAND_AVAILABLE(
ASML.organization_id,
ASML.inventory_item_id,
p_subinventory_code,
p_locator_id)) available_quantity
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id
AND VTS.instance_id IN(
SELECT instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_item_instance_id
UNION
SELECT
SUBJECT_ID INSTANCE_ID
FROM CSI_II_RELATIONSHIPS
WHERE 1=1
START WITH OBJECT_ID = p_item_instance_id /*root instance id*/
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
--CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
))
GROUP BY asml.inventory_item_id,ASML.organization_id,AWOS.WIP_ENTITY_ID,ASML.OPERATION_SEQUENCE;
SELECT SUM(WIRO.REQUIRED_QUANTITY) REQUIRED_QUANTITY,
SUM( nvl(ahl_pp_materials_pvt.get_issued_qty(msik.organization_id, asml.inventory_item_id, asml.workorder_operation_id), 0)) issued_qty,
SUM(GET_ONHAND_AVAILABLE(
ASML.organization_id,
ASML.inventory_item_id,
p_subinventory_code,
p_locator_id)) available_quantity
from ahl_workorders 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 = 'ACTIVE'
AND AWOS.workorder_id IN (SELECT workorder_id FROM ahl_workorders WO,ahl_visit_tasks_b VTS WHERE
WO.visit_id = p_visit_id
AND WO.visit_id = VTS.visit_id
AND WO.visit_task_id = VTS.visit_task_id)
GROUP BY asml.inventory_item_id,ASML.organization_id,AWOS.WIP_ENTITY_ID,ASML.OPERATION_SEQUENCE;
SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;
SELECT SUM(SCHEDULED_END_DATE - SCHEDULED_START_DATE)
FROM AHL_SEARCH_WORKORDERS_V
WHERE visit_id = p_visit_id
AND JOB_STATUS_CODE IN('4','5','7','12')
GROUP BY visit_id;
SELECT SUM(SCHEDULED_END_DATE - SCHEDULED_START_DATE)
FROM AHL_SEARCH_WORKORDERS_V
WHERE visit_id = p_visit_id
AND JOB_STATUS_CODE <> '22'
GROUP BY visit_id;
INSERT INTO ahl_prd_mb_uc_details
(
Unit_Header_id,
Unit_Name,
--Path_position_id,
relationship_id,
parent_rel_id,
POSITION,
IS_POSITION_SER_CTRLD,
CURR_ITEM_ID,
CURR_INSTANCE_ID,
parent_instance_id,
ALLOWED_QTY,
CURR_ITEM_NUMBER,
CURR_SERIAL_NUMBER,
CURR_INSTLD_QTY,
REQ_QTY,
ISSUED_QTY,
AVAILABLE_QTY,
NOT_AVAILABLE_QTY,
COMPL_WO_COUNT,
TOTAL_WO_COUNT,
CUMM_REQ_QTY,
CUMM_ISSUED_QTY,
CUMM_AVAILABLE_QTY,
CUMM_NOT_AVAILABLE_QTY,
CUMM_COMPL_WO_COUNT,
CUMM_TOTAL_WO_COUNT,
ROOT_INSTANCE_ID
)VALUES
(
x_mrshl_details_tbl(i).Unit_Header_id,
x_mrshl_details_tbl(i).Unit_Name,
x_mrshl_details_tbl(i).relationship_id,
x_mrshl_details_tbl(i).parent_rel_id,
x_mrshl_details_tbl(i).POSITION,
x_mrshl_details_tbl(i).IS_POSITION_SER_CTRLD,
x_mrshl_details_tbl(i).CURR_ITEM_ID,
x_mrshl_details_tbl(i).CURR_INSTANCE_ID,
x_mrshl_details_tbl(i).parent_instance_id,
x_mrshl_details_tbl(i).ALLOWED_QTY,
x_mrshl_details_tbl(i).CURR_ITEM_NUMBER,
x_mrshl_details_tbl(i).CURR_SERIAL_NUMBER,
x_mrshl_details_tbl(i).CURR_INSTLD_QTY,
x_mrshl_details_tbl(i).REQ_QTY,
x_mrshl_details_tbl(i).ISSUED_QTY,
x_mrshl_details_tbl(i).AVAILABLE_QTY,
x_mrshl_details_tbl(i).NOT_AVAILABLE_QTY,
x_mrshl_details_tbl(i).COMPL_WO_COUNT,
x_mrshl_details_tbl(i).TOTAL_WO_COUNT,
x_mrshl_details_tbl(i).CUMM_REQ_QTY,
x_mrshl_details_tbl(i).CUMM_ISSUED_QTY,
x_mrshl_details_tbl(i).CUMM_AVAILABLE_QTY,
x_mrshl_details_tbl(i).CUMM_NOT_AVAILABLE_QTY,
x_mrshl_details_tbl(i).CUMM_COMPL_WO_COUNT,
x_mrshl_details_tbl(i).CUMM_TOTAL_WO_COUNT,
x_mrshl_details_tbl(i).ROOT_INSTANCE_ID
);
SELECT UC_NAME,CSI_INSTANCE_ID FROM ahl_unit_config_headers_v
WHERE UC_HEADER_ID = c_unit_header_id;
SELECT M.inventory_item_id,M.concatenated_segments,C.serial_number,
C.QUANTITY
FROM mtl_system_items_kfv M, csi_item_instances C
WHERE C.instance_id = c_instance_id
AND C.inventory_item_id = M.inventory_item_id
AND C.inv_master_organization_id = M.organization_id;
Select * FROM ahl_prd_mb_uc_details
WHERE UNIT_HEADER_ID = c_unit_header_id;*/
SELECT iasso.quantity Itm_qty,
iasso.uom_code Itm_uom_code,
iasso.revision Itm_revision,
iasso.item_association_id,
reln.quantity Posn_qty,
reln.uom_code Posn_uom_code,
reln.parent_relationship_id,
reln.position_ref_code,
csi.INVENTORY_ITEM_ID,
csi.QUANTITY Inst_qty,
csi.UNIT_OF_MEASURE Inst_uom_code
FROM ahl_mc_relationships reln, ahl_item_associations_b iasso, csi_item_instances csi
WHERE csi.INSTANCE_ID = c_instance_id
AND reln.relationship_id = c_mc_relationship_id
AND iasso.item_group_id = reln.item_group_id
AND iasso.inventory_item_id = CSI.INVENTORY_ITEM_ID
AND (iasso.revision IS NULL OR iasso.revision = CSI.INVENTORY_REVISION)
AND iasso.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
AND trunc(nvl(reln.active_start_date, sysdate)) <= trunc(sysdate)
AND trunc(nvl(reln.active_end_date, sysdate+1)) > trunc(sysdate);
SELECT M.inventory_item_id,M.concatenated_segments,C.serial_number,
DECODE(M.serial_number_control_code,'1','N','Y') is_serial_cntld,C.quantity
FROM mtl_system_items_kfv M, csi_item_instances C
WHERE C.instance_id = c_instance_id
AND C.inventory_item_id = M.inventory_item_id
AND C.inv_master_organization_id = M.organization_id;
SELECT instance_id,to_number(NULL) parent_instance_id FROM csi_item_instances WHERE INSTANCE_ID = p_root_instance_id
UNION
SELECT
SUBJECT_ID INSTANCE_ID,
OBJECT_ID PARENT_INSTANCE_ID
FROM CSI_II_RELATIONSHIPS
WHERE 1=1
START WITH OBJECT_ID = p_root_instance_id
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE)
CONNECT BY OBJECT_ID = PRIOR SUBJECT_ID
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE);