DBA Data[Home] [Help]

APPS.AHL_PRD_MRSHL_PVT SQL Statements

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

Line: 25

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: 54

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: 97

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: 116

SELECT * FROM(
SELECT ASML.scheduled_material_id,AWOS.workorder_id,
--AWOS.job_number,
AWOS.workorder_name job_number,
--AWOS.job_status_code,
AWOS.status_code job_status_code,
--AWOS.job_status_meaning,
(SELECT MLU.meaning from fnd_lookup_values_vl MLU where MLU.LOOKUP_TYPE ='AHL_JOB_STATUS' AND MLU.LOOKUP_CODE = AWOS.status_code) 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,
--ARUNJK modified Exception date and added AOG_FLAG and OBJCET_VERSION_NUMBER for Marshalling
DECODE(SIGN(TRUNC(ASML.SCHEDULED_DATE)- TRUNC(ASML.REQUESTED_DATE)), 1, ASML.SCHEDULED_DATE, NULL) EXCEPTION_DATE,
ASML.AOG_FLAG,
ASML.OBJECT_VERSION_NUMBER ASM_OBJECT_VERSION_NUMBER,
(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_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_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: 196

SELECT * FROM(
SELECT ASML.scheduled_material_id,AWOS.workorder_id,
--AWOS.job_number,
AWOS.workorder_name job_number,
--AWOS.job_status_code,
AWOS.status_code job_status_code,
--AWOS.job_status_meaning,
(SELECT MLU.meaning from fnd_lookup_values_vl MLU where MLU.LOOKUP_TYPE ='AHL_JOB_STATUS' AND MLU.LOOKUP_CODE = AWOS.status_code) 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,
--ARUNJK modified Exception date and added AOG_FLAG and OBJCET_VERSION_NUMBER for Marshalling
DECODE(SIGN(TRUNC(ASML.SCHEDULED_DATE)- TRUNC(ASML.REQUESTED_DATE)), 1, ASML.SCHEDULED_DATE, NULL) EXCEPTION_DATE,
ASML.AOG_FLAG,
ASML.OBJECT_VERSION_NUMBER ASM_OBJECT_VERSION_NUMBER,
(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_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
))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: 253

SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;*/
Line: 256

Select AVTS.inv_locator_id,
    MTLI.subinventory_code subinventory,
    DECODE(MSI.segment19, NULL, MTLI.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MTLI.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 AHL_VISITS_B AVTS,mtl_item_locations_kfv MTLI,
    mtl_item_locations MSI
WHERE
  AVTS.STATUS_CODE             IN ('RELEASED', 'CLOSED', 'PARTIALLY RELEASED')
  AND MTLI.inventory_location_id(+) = AVTS.inv_locator_id
  AND MSI.inventory_location_id(+)  = AVTS.inv_locator_id
  AND AVTS.visit_id = p_visit_id;
Line: 279

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: 526

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: 544

SELECT * FROM(
SELECT AWOS.workorder_id,
--AWOS.job_number,
AWOS.workorder_name job_number,
--AWOS.job_status_code,
AWOS.status_code job_status_code,
--AWOS.job_status_meaning,
(SELECT MLU.meaning from fnd_lookup_values_vl MLU where MLU.LOOKUP_TYPE ='AHL_JOB_STATUS' AND MLU.LOOKUP_CODE = AWOS.status_code) 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,
--ARUNJK modified Exception date and added AOG_FLAG and OBJCET_VERSION_NUMBER for Marshalling
DECODE(SIGN(TRUNC(ASML.SCHEDULED_DATE)- TRUNC(ASML.REQUESTED_DATE)), 1, ASML.SCHEDULED_DATE, NULL) EXCEPTION_DATE,
ASML.AOG_FLAG,
ASML.OBJECT_VERSION_NUMBER ASM_OBJECT_VERSION_NUMBER,
(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_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_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: 622

SELECT * FROM(SELECT AWOS.workorder_id,
--AWOS.job_number,
AWOS.workorder_name job_number,
--AWOS.job_status_code,
AWOS.status_code job_status_code,
--AWOS.job_status_meaning,
(SELECT MLU.meaning from fnd_lookup_values_vl MLU where MLU.LOOKUP_TYPE ='AHL_JOB_STATUS' AND MLU.LOOKUP_CODE = AWOS.status_code) 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,
--ARUNJK modified Exception date and added AOG_FLAG and OBJCET_VERSION_NUMBER for Marshalling
DECODE(SIGN(TRUNC(ASML.SCHEDULED_DATE)- TRUNC(ASML.REQUESTED_DATE)), 1, ASML.SCHEDULED_DATE, NULL) EXCEPTION_DATE,
ASML.AOG_FLAG,
ASML.OBJECT_VERSION_NUMBER ASM_OBJECT_VERSION_NUMBER,
(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_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
))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: 675

SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;*/
Line: 678

Select AVTS.inv_locator_id,
    MTLI.subinventory_code subinventory,
    DECODE(MSI.segment19, NULL, MTLI.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MTLI.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 AHL_VISITS_B AVTS,mtl_item_locations_kfv MTLI,
    mtl_item_locations MSI
WHERE
  AVTS.STATUS_CODE             IN ('RELEASED', 'CLOSED', 'PARTIALLY RELEASED')
  AND MTLI.inventory_location_id(+) = AVTS.inv_locator_id
  AND MSI.inventory_location_id(+)  = AVTS.inv_locator_id
  AND AVTS.visit_id = p_visit_id;
Line: 700

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: 910

 SELECT COUNT(*)
 FROM AHL_WORKORDERS WO,ahl_visit_tasks_b VTS
 WHERE WO.visit_id = p_visit_id
 AND WO.STATUS_CODE IN('4','5','7','12')
 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: 919

 SELECT COUNT(*)
 FROM AHL_WORKORDERS WO,ahl_visit_tasks_b VTS
 WHERE WO.visit_id = p_visit_id
 AND WO.STATUS_CODE IN('4','5','7','12')
 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: 946

  SELECT COUNT(*)
  FROM AHL_WORKORDERS WO,
    ahl_visit_tasks_b VTS
  WHERE WO.visit_id    = p_visit_id
  AND WO.STATUS_CODE NOT IN ('22','17')
  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: 957

  SELECT COUNT(*)
  FROM AHL_WORKORDERS WO,
    ahl_visit_tasks_b VTS
  WHERE WO.visit_id    = p_visit_id
  AND WO.STATUS_CODE NOT IN ('22','17')
  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: 985

 SELECT COUNT(*)
 FROM AHL_WORKORDERS
 WHERE visit_id = p_visit_id
 AND STATUS_CODE IN('4','5','7','12');
Line: 992

 SELECT COUNT(*)
 FROM AHL_WORKORDERS
 WHERE visit_id = p_visit_id
AND STATUS_CODE NOT IN ('22','17');
Line: 1053

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: 1069

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: 1101

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: 1116

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: 1132

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: 1164

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: 1181

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: 1206

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: 1247

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: 1269

SELECT inv_locator_id, subinventory, locator_segments FROM ahl_prd_visits_v
WHERE visit_id = p_visit_id;*/
Line: 1272

Select AVTS.inv_locator_id,
    MTLI.subinventory_code subinventory,
    DECODE(MSI.segment19, NULL, MTLI.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MTLI.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 AHL_VISITS_B AVTS,mtl_item_locations_kfv MTLI,
    mtl_item_locations MSI
WHERE
  AVTS.STATUS_CODE             IN ('RELEASED', 'CLOSED', 'PARTIALLY RELEASED')
  AND MTLI.inventory_location_id(+) = AVTS.inv_locator_id
  AND MSI.inventory_location_id(+)  = AVTS.inv_locator_id
  AND AVTS.visit_id = p_visit_id;
Line: 1416

 SELECT nvl(SUM(WDJ.SCHEDULED_COMPLETION_DATE - WDJ.SCHEDULED_START_DATE),0)
 FROM AHL_WORKORDERS WO,WIP_DISCRETE_JOBS WDJ
 WHERE WO.visit_id = p_visit_id
 AND WO.wip_entity_id = WDJ.wip_entity_id
 AND WO.STATUS_CODE IN('4','5','7','12')
 GROUP BY WO.visit_id;
Line: 1424

 SELECT count(*)
 FROM AHL_WORKORDERS
 WHERE visit_id = p_visit_id
 AND STATUS_CODE IN('4','5','7','12')
 GROUP BY visit_id;
Line: 1431

 SELECT nvl(SUM(WDJ.SCHEDULED_COMPLETION_DATE - WDJ.SCHEDULED_START_DATE),0)
 FROM AHL_WORKORDERS WO,WIP_DISCRETE_JOBS WDJ
 WHERE WO.visit_id = p_visit_id
 AND WO.wip_entity_id = WDJ.wip_entity_id
 AND wo.STATUS_CODE NOT IN ('22','17')
 GROUP BY WO.visit_id;
Line: 1439

 SELECT count(*)
 FROM AHL_WORKORDERS
 WHERE visit_id = p_visit_id
AND STATUS_CODE NOT IN ('22','17')
 GROUP BY visit_id;
Line: 1533

  DELETE ahl_prd_mb_uc_details;
Line: 1537

       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: 1659

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

 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: 1671

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

   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: 1955

 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: 1963

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