APPS.AHL_INV_RESERVATIONS_GRP SQL Statements

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

Line: 59

           SELECT   requested_quantity,
                    NVL(completed_quantity, 0) completed_quantity
           FROM     ahl_schedule_materials
           WHERE    scheduled_material_id = c_scheduled_material_id;

Line: 67

      SELECT   SUM(primary_reservation_quantity) reserved_quantity
      FROM     mtl_reservations mrsv,ahl_schedule_materials asmt
      WHERE    mrsv.demand_source_line_detail = c_scheduled_material_id
      AND      mrsv.external_source_code = 'AHL'
      AND      mrsv.demand_source_line_detail = asmt.scheduled_material_id
      AND      mrsv.organization_id = asmt.organization_id
      AND      mrsv.requirement_date = asmt.requested_date
      AND      mrsv.inventory_item_id = asmt.inventory_item_id
      GROUP BY mrsv.demand_source_line_detail;

Line: 81

   SELECT 1
   FROM
   ahl_workorders wo, ahl_visit_tasks_b vts
   WHERE
   wo.wip_entity_id = c_wip_entity_id
   AND wo.status_code NOT IN (7, 22, 17, 12, 18, 21, 4, 5) -- cancelled, deleted, draft, closed,Deffered,Pending Defer/Cancel Approval, Complete, Complete No-charge
   AND vts.visit_task_id = wo.visit_task_id
   AND vts.return_to_supply_flag = 'Y'
  /* commented out by debadey for WO-WO reservation updation validation
  AND NOT EXISTS (SELECT 'X' FROM mtl_reservations
         WHERE external_source_code = 'AHL'
         AND supply_source_type_id = c_source_type
         AND supply_source_header_id = c_wip_entity_id)*/;

Line: 365

           SELECT   scheduled_material_id
           FROM     ahl_schedule_materials
           WHERE    scheduled_material_id = c_SCHEDULED_MATERIAL_ID
           AND      organization_id = c_organization_ID
           AND      inventory_item_id = c_item_ID
           AND      visit_task_id = c_visit_task_ID
           AND      rt_oper_material_id = c_rt_oper_material_id
           AND      status = 'ACTIVE'
           AND      requested_quantity <>0
           AND      EXISTS ( SELECT 1
                             FROM ahl_visit_tasks_b vt
                             WHERE vt.status_code = 'PLANNING'
                             AND vt.visit_task_id =   c_visit_task_id);

Line: 385

           SELECT scheduled_material_id
           FROM ahl_schedule_materials
           WHERE scheduled_material_id = c_scheduled_material_id
      AND  organization_id = c_organization_id
           AND  inventory_item_id = c_item_id
           AND  Operation_sequence = c_oper_seq_num
           AND  status = 'ACTIVE'
           AND  requested_quantity <>0
           AND  visit_task_id = (  SELECT   aw.visit_task_id
                                   FROM     ahl_visit_tasks_b vt, ahl_workorders aw
                                   WHERE    vt.status_code IN ('PLANNING','RELEASED')
                                   AND      aw.wip_entity_id = c_wip_entity_id
                                   AND      aw.status_code in ('1','3')
                                   AND      aw.visit_task_id= vt.visit_task_id );

Line: 403

            -> job status must not be cancelled, deleted, draft, closed.
            -> job must be RTS job.
            -> no reservation exists for the job as a valid supply in mtl_reservation table.
    */
    CURSOR validate_wip_supply(c_wip_entity_id IN NUMBER, c_item_id IN NUMBER, c_organization_id IN NUMBER, c_source_type IN NUMBER) IS
    SELECT 'X'
    FROM
    ahl_workorders wo, ahl_visit_tasks_b vts, wip_discrete_jobs wdj
    WHERE
    wdj.wip_entity_id = c_wip_entity_id
    AND wo.wip_entity_id = wdj.wip_entity_id
    AND wo.status_code NOT IN (7, 22, 17, 12, 18, 21, 4, 5) -- cancelled, deleted, draft, closed,Deffered,Pending Defer/Cancel Approval, Complete, Complete No-charge
    AND vts.visit_task_id = wo.visit_task_id
    AND wdj.rebuild_item_id = c_item_id
    AND vts.return_to_supply_flag = 'Y'
    AND wdj.organization_id = c_organization_id
   /* Commented out for WO-WO validation by debadey as per instructions from Balaji
   AND NOT EXISTS (SELECT 'X' FROM mtl_reservations
                    WHERE external_source_code = 'AHL'
                    AND supply_source_type_id = c_source_type
                    AND supply_source_header_id = c_wip_entity_id)*/;