DBA Data[Home] [Help]

APPS.AHL_PRD_MRSHL_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 24

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;
Line: 53

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;
Line: 96

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;
Line: 115

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;
Line: 184

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;
Line: 230

SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;
Line: 240

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;
Line: 533

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;
Line: 551

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;
Line: 618

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;
Line: 660

SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;
Line: 670

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;
Line: 904

 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);
Line: 915

 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)
));
Line: 943

 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);
Line: 954

 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)
));
Line: 981

 SELECT COUNT(*)
 FROM AHL_SEARCH_WORKORDERS_V
 WHERE visit_id = p_visit_id
 AND JOB_STATUS_CODE IN('4','5','7','12');
Line: 988

 SELECT COUNT(*)
 FROM AHL_SEARCH_WORKORDERS_V
 WHERE visit_id = p_visit_id
 AND JOB_STATUS_CODE <> '22';
Line: 1050

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);
Line: 1066

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)
));
Line: 1098

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);
Line: 1113

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);
Line: 1129

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)
));
Line: 1161

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);
Line: 1178

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;
Line: 1203

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;
Line: 1244

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;
Line: 1266

SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;
Line: 1398

 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;
Line: 1405

 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;
Line: 1488

       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
       );
Line: 1617

 SELECT UC_NAME,CSI_INSTANCE_ID FROM ahl_unit_config_headers_v
 WHERE UC_HEADER_ID = c_unit_header_id;
Line: 1621

 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;
Line: 1629

 Select * FROM   ahl_prd_mb_uc_details
 WHERE UNIT_HEADER_ID = c_unit_header_id;*/
Line: 1634

   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);
Line: 1923

 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;
Line: 1931

 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);