APPS.AHL_CMP_UTIL_PKG SQL Statements

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

Line: 42

    SELECT VST.visit_id
    FROM   AHL_VISITS_B VST, AHL_VISIT_TYPES_B VTB
    WHERE  VST.organization_id                = c_org_id
    AND    VST.visit_type_code                = VTB.visit_type_code
    AND    NVL(VTB.component_visit_flag, 'N') = 'Y'
    AND    VTB.status_code                    = 'COMPLETE';

Line: 53

    SELECT TSK.repair_batch_name
    FROM   AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
           CSI_ITEM_INSTANCES CSI, MTL_ITEM_LOCATIONS MTL
    WHERE  TSK.visit_id             = c_visit_id
    AND    VST.visit_id             = TSK.visit_id
    AND    (
           MTL.physical_location_id = VST.comp_planning_loc_id
    OR     MTL.physical_location_id = VST.comp_inrepair_loc_id
           )
    AND    MTL.project_id           = VST.project_id
    AND    MTL.task_id              = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)
    AND    CSI.inv_locator_id       = MTL.inventory_location_id
    AND    CSI.instance_id          = c_instance_id
    AND    TSK.task_type_code       = 'SUMMARY'
    AND    TSK.status_code          NOT IN ('CANCELLED', 'CLOSED', 'DELETED')
    AND    TSK.repair_batch_name    IS NOT NULL;

Line: 151

    SELECT 'X'
    FROM   CSI_ITEM_INSTANCES
    WHERE  instance_id                             = c_instance_id
    AND    location_type_code                      = 'INVENTORY'
    AND    TRUNC(NVL(active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(active_end_date, SYSDATE+1)) > TRUNC(SYSDATE);

Line: 241

    SELECT AVB.visit_id,
           AVB.status_code

    FROM   AHL_VISITS_B      AVB,
           AHL_VISIT_TYPES_B AVTB

    WHERE  AVB.organization_id        = c_org_id
    AND    AVB.visit_type_code        = AVTB.visit_type_code
    AND    AVTB.status_code           = 'COMPLETE'
    AND    AVTB.component_visit_flag  = 'Y'                     -- criterion 1
    AND    AVB.start_date_time        <= SYSDATE
    AND    AVB.close_date_time        >  SYSDATE                -- criterion 2
    AND    NVL(AVB.locked_flag, 'N')  = 'N'                     -- criterion 3
    AND    AVB.status_code NOT IN ('CANCELLED','CLOSED','DELETED');-- PRAKKUM :: BUG 14542057 :: 31/08/2012

Line: 258

    SELECT visit_id,
           organization_id,
           department_id,
           start_date_time,
           close_date_time,
           comp_planning_loc_id,
           comp_inrepair_loc_id,
           firmed_flag
    FROM   AHL_VISITS_B
    WHERE  visit_id = c_visit_id;

Line: 383

    SELECT 'Y'
    FROM   AHL_VISITS_B      AVB,
           AHL_VISIT_TYPES_B AVTB
    WHERE  AVB.visit_id              = c_visit_id
    AND    AVB.visit_type_code       = AVTB.visit_type_code
    AND    AVTB.status_code          = 'COMPLETE'
    AND    AVTB.component_visit_flag = 'Y';

Line: 461

 SELECT 'x'
 FROM    AHL_WORKORDERS
 WHERE   status_code NOT IN ('7','12','17','22' )  -- Cancelled,Closed,Draft and Deleted
 AND     visit_task_id IN
                 (SELECT vt.visit_task_id
                  FROM   AHL_VISIT_TASKS_B vt
                  WHERE  vt.cost_parent_id IS NOT NULL
                  AND    nvl(vt.return_to_supply_flag,'N') = 'Y'
                  AND    vt.instance_id = nvl(c_ins_id,vt.instance_id)
                  START WITH vt.visit_task_id = (SELECT visit_task_id
                                                 FROM   AHL_VISIT_TASKS_B
                                                 WHERE  repair_batch_name = c_repair_batch)
                  CONNECT BY PRIOR  vt.visit_task_id = vt.cost_parent_id );

Line: 515

    SELECT NVL(SUM(CII.quantity),0)
    INTO   l_planning_qty
    FROM   CSI_ITEM_INSTANCES CII,
           AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
           MTL_ITEM_LOCATIONS MTL
    WHERE  TSK.repair_batch_name    = p_repair_batch
    AND    VST.visit_id             = TSK.visit_id
    AND    MTL.physical_location_id = VST.comp_planning_loc_id
    AND    MTL.project_id           = VST.project_id
    AND    MTL.task_id              = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)
    AND    CII.LOCATION_TYPE_CODE = 'INVENTORY'
    AND    CII.inv_locator_id       =  MTL.inventory_location_id
    AND    CII.inv_organization_id = VST.organization_id
    AND    CII.inventory_item_id    = TSK.inventory_item_id
    AND    TRUNC(NVL(CII.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(CII.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 535

    SELECT NVL(SUM(CII.quantity),0)
    INTO   l_inrepair_qty
    FROM   CSI_ITEM_INSTANCES CII,
           AHL_VISIT_TASKS_B TSK, AHL_VISITS_B VST,
           MTL_ITEM_LOCATIONS MTL
    WHERE  TSK.repair_batch_name    = p_repair_batch
    AND    VST.visit_id             = TSK.visit_id
    AND    MTL.physical_location_id = VST.comp_inrepair_loc_id
    AND    MTL.project_id           = VST.project_id
    AND    MTL.task_id              = AHL_VWP_PROJ_PROD_PVT.Get_RB_Transaction_Task(VST.project_id,TSK.project_task_id,VST.visit_id)
    AND    CII.LOCATION_TYPE_CODE = 'INVENTORY'
    AND    CII.inv_locator_id       =  MTL.inventory_location_id
    AND    CII.inv_organization_id = VST.organization_id
    AND    CII.inventory_item_id    = TSK.inventory_item_id
    AND    TRUNC(NVL(CII.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
    AND    TRUNC(NVL(CII.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 585

        SELECT  vt.visit_task_id
        FROM   AHL_VISIT_TASKS_B vt
        WHERE  VT.COST_PARENT_ID IS NOT NULL
        START WITH vt.visit_task_id = ( SELECT visit_task_id
                                        FROM   AHL_VISIT_TASKS_B
                                        WHERE  REPAIR_BATCH_NAME = C_REPAIR_BATCH )
        CONNECT BY PRIOR  vt.visit_task_id = vt.cost_parent_id;

Line: 595

 SELECT CII.QUANTITY
 FROM   CSI_ITEM_INSTANCES CII,
               AHL_WORKORDERS WO
        where  WO.visit_task_id = c_visit_task_id
        AND    CII.location_type_code = 'WIP'
        and    CII.wip_job_id = WO.wip_entity_id
        AND    TRUNC(NVL(CII.ACTIVE_START_DATE, SYSDATE)) <= TRUNC(SYSDATE)
        AND    TRUNC(NVL(CII.active_end_date, SYSDATE+1))  > TRUNC(SYSDATE);

Line: 653

SELECT DECODE(MIL.segment19, NULL, MIL_kfv.concatenated_segments, INV_PROJECT.GET_LOCSEGS(MIL_kfv.concatenated_segments)
       || fnd_flex_ext.get_delimiter('INV', 'MTLL', 101)
       || INV_ProjectLocator_PUB.get_project_number(MIL.segment19)
       || FND_FLEX_EXT.GET_DELIMITER('INV', 'MTLL', 101)
       || INV_ProjectLocator_PUB.get_task_number(MIL.segment20)) locator_segments
  FROM  MTL_ITEM_LOCATIONS MIL,MTL_ITEM_LOCATIONS_KFV MIL_kfv
  WHERE MIL_kfv.INVENTORY_LOCATION_ID = MIL.INVENTORY_LOCATION_ID AND
        MIL_kfv.ORGANIZATION_ID = MIL.ORGANIZATION_ID AND
        MIL.INVENTORY_LOCATION_ID = p_locator_id;

Line: 665

   /*SELECT DECODE(MIL.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(MIL.segment19)
                  || FND_FLEX_EXT.GET_DELIMITER('INV', 'MTLL', 101)
                  || INV_PROJECTLOCATOR_PUB.GET_TASK_NUMBER(MIL.SEGMENT20))
  INTO  l_concat_segs
  FROM  MTL_ITEM_LOCATIONS_KFV MIL
  WHERE MIL.INVENTORY_LOCATION_ID = p_locator_id;*/

Line: 705

        SELECT  REPAIR_BATCH_NAME
        INTO    l_rpr_batch
        FROM    AHL_VISIT_TASKS_B
        WHERE   cost_parent_id IS NULL
        AND     repair_batch_name IS NOT NULL
        START WITH visit_task_id = p_child_task_id
        CONNECT BY PRIOR  cost_parent_id = visit_task_id ;

Line: 759

  SELECT SUM(task.quantity) plan_qty, task.instance_id FROM ahl_visit_tasks_b task,
  ahl_workorders wo,ahl_visits_b visit,WIP_DISCRETE_JOBS WDJ
  WHERE
    wo.VISIT_TASK_ID                            = task.VISIT_TASK_ID
        AND WDJ.WIP_ENTITY_ID                       = WO.WIP_ENTITY_ID
    AND WO.STATUS_CODE                          IN (1,3,6,17,19,20)
        AND task.VISIT_ID                           = visit.visit_id
        AND task.inventory_item_id                  = p_item_id
        AND visit.organization_id                   = p_org_id
        AND (p_comp_visit_flag IS NULL OR
        AHL_CMP_UTIL_PKG.Is_Comp_Visit(task.visit_id) = p_comp_visit_flag)
        AND WDJ.scheduled_completion_date           >=
        NVL(p_start_date,WDJ.scheduled_completion_date)
    AND WDJ.scheduled_completion_date           <=
        NVL(p_end_date,WDJ.scheduled_completion_date)
            AND task_type_code NOT IN ('SUMMARY')
  GROUP BY
    task.instance_id;

Line: 785

  SELECT 'Y' FROM ahl_visit_tasks_b task,
  ahl_workorders wo,WIP_DISCRETE_JOBS WDJ
  WHERE
    wo.VISIT_TASK_ID                            = task.VISIT_TASK_ID
        AND WDJ.WIP_ENTITY_ID                       = WO.WIP_ENTITY_ID
    AND WO.STATUS_CODE                          IN (1,3,6,17,19,20)
        AND task.instance_id                        = p_instance_id
        AND (p_comp_visit_flag IS NULL OR
        AHL_CMP_UTIL_PKG.Is_Comp_Visit(task.visit_id) = p_comp_visit_flag)
        AND WDJ.scheduled_completion_date           >=
        NVL(p_start_date,WDJ.scheduled_completion_date)
    AND WDJ.scheduled_completion_date           <=
        NVL(p_end_date,WDJ.scheduled_completion_date)
        AND return_to_supply_flag = 'Y'
        AND task_type_code NOT IN ('SUMMARY')
        AND rownum < 2;

Line: 870

  SELECT SUM(task.quantity) sched_qty FROM ahl_visit_tasks_b task,
  ahl_workorders wo,ahl_visits_b visit,WIP_DISCRETE_JOBS WDJ
  WHERE
    wo.VISIT_TASK_ID                            = task.VISIT_TASK_ID
        AND WDJ.WIP_ENTITY_ID                       = WO.WIP_ENTITY_ID
    AND WO.STATUS_CODE                          IN (1,3,6,17,19,20)
        AND task.VISIT_ID                           = visit.visit_id
        AND task.inventory_item_id                  = p_item_id
        AND visit.organization_id                   = p_org_id
        AND return_to_supply_flag                   = 'Y'
        AND (p_comp_visit_flag IS NULL OR
        AHL_CMP_UTIL_PKG.Is_Comp_Visit(task.visit_id) = p_comp_visit_flag)
        AND WDJ.scheduled_completion_date           >=
        NVL(p_start_date,WDJ.scheduled_completion_date)
    AND WDJ.scheduled_completion_date           <=
        NVL(p_end_date,WDJ.scheduled_completion_date)
        AND task_type_code NOT IN ('SUMMARY')
  GROUP BY
    task.instance_id;

Line: 938

 SELECT 'x'
 FROM    AHL_WORKORDERS
 WHERE   status_code NOT IN ('4','12','7','5','17' ) -- Complete,Closed,Cancelled,Complete-No-Charge and Draft
 AND     visit_task_id IN
                 (SELECT visit_task_id
                  FROM   AHL_VISIT_TASKS_B vt
                  WHERE  vt.cost_parent_id IS NOT NULL
                  AND    vt.instance_id = nvl(c_ins_id,vt.instance_id)
                  START WITH vt.visit_task_id = (SELECT visit_task_id
                                                 FROM   AHL_VISIT_TASKS_B
                                                 WHERE  repair_batch_name = c_repair_batch)
                  CONNECT BY PRIOR  vt.visit_task_id = vt.cost_parent_id );

Line: 997

  SELECT nvl(ret_serviceable_qty, 0) serv_qty, nvl(ret_unserviceable_qty,0) unserv_qty FROM
  ahl_visit_tasks_b task WHERE repair_batch_name = p_repair_batch_csr;