APPS.AHL_VWP_PRICE_PVT SQL Statements

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

Line: 806

SELECT MAT.inventory_item_id,
         MAT.requested_date,
         SUM(NVL(MAT.requested_quantity,0)) quantity,
         MAT.uom_code
  FROM AHL_SCHEDULE_MATERIALS_V MAT
  WHERE  MAT.visit_task_id =c_visit_task_id
  AND MAT.JOB_STATUS_CODE <> 22  -- 'deleted' status
  AND MAT.JOB_STATUS_CODE <> 7  -- 'cancelled' status
  AND MAT.inventory_item_id is not null
  AND MAT.uom_code is not null
  GROUP BY MAT.inventory_item_id,
         MAT.requested_date,
         MAT.uom_code;

Line: 822

SELECT   asm.inventory_item_id,
         wiro.DATE_REQUIRED requested_date,
         sum(nvl(wiro.REQUIRED_QUANTITY,0)) quantity,
         mtl.primary_uom_code uom_code
FROM     ahl_workorders awo,
         ahl_schedule_materials asm,
         wip_requirement_operations wiro,
         mtl_system_items_b mtl
WHERE    awo.visit_task_id = c_visit_task_id
AND      awo.visit_task_id = asm.visit_task_id
AND      asm.inventory_item_id = mtl.inventory_item_id
AND      asm.organization_id   = mtl.organization_id
AND      awo.wip_entity_id = wiro.wip_entity_id
AND      awo.STATUS_CODE NOT IN (22,7)
AND      ASM.inventory_item_id is not null
AND      mtl.primary_uom_code is not null
AND      ASM.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
AND      ASM.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
AND      ASM.ORGANIZATION_ID = WIRO.ORGANIZATION_ID
GROUP BY asm.inventory_item_id,
         wiro.DATE_REQUIRED,
         mtl.primary_uom_code;

Line: 924

  SELECT sum(NVL(AR.TOTAL_REQUIRED,0)) duration,
--         sum(NVL(AR.quantity,0)) quantity,
--       sum(NVL(AR.TOTAL_REQUIRED,0)) total_quantity,
         BR.BILLABLE_ITEM_ID,
         AR.REQUIRED_START_DATE,
         MSIV.concatenated_segments item_name,
         MSIV.primary_uom_code uom_code
  FROM   AHL_PP_REQUIREMENT_V AR,
         MTL_SYSTEM_ITEMS_VL MSIV,
         AHL_VISITS_B V,
         AHL_VISIT_TASKS_B VT,
         BOM_RESOURCES BR
  WHERE  BR.resource_id=AR.resource_id
  AND    BR.BILLABLE_ITEM_ID is not null
  AND    AR.visit_task_id = c_visit_task_id
  AND    V.visit_id = VT.visit_id
  AND    VT.visit_task_id = c_visit_task_id
  AND    MSIV.inventory_item_id = BR.BILLABLE_ITEM_ID
  AND    MSIV.organization_id = V.organization_id
  AND    AR.JOB_STATUS_CODE <> 22  -- 'deleted' status
  AND    AR.JOB_STATUS_CODE <> 7  -- 'cancelled' status
  Group By BR.BILLABLE_ITEM_ID,
           MSIV.concatenated_segments,
           MSIV.primary_uom_code,
           AR.REQUIRED_START_DATE;

Line: 1049

  SELECT MAT.rt_oper_material_id,
         MAT.inventory_item_id,
         MAT.quantity,
         MSIV.primary_uom_code uom_code
--         MAT.uom_code
  FROM AHL_RT_OPER_MATERIALS MAT,
       AHL_VISITS_B V,
       AHL_VISIT_TASKS_B VT,
       MTL_SYSTEM_ITEMS_VL MSIV
  WHERE  MAT.association_type_code='ROUTE'
  AND  MSIV.organization_id = V.organization_id
  AND  V.visit_id = VT.visit_id
  AND  VT.visit_task_id = c_visit_task_id
  AND  MAT.inventory_item_id = MSIV.inventory_item_id
  AND  MAT.object_id=c_route_id;

Line: 1071

  SELECT MAT.inventory_item_id,
         sum(NVL(MAT.quantity,0)) quantity,
         MSIV.primary_uom_code uom_code
--         MAT.uom_code
  FROM   MTL_SYSTEM_ITEMS_VL MSIV,
         AHL_VISITS_B V,
         AHL_VISIT_TASKS_B VT,
         AHL_RT_OPER_MATERIALS MAT
  WHERE  MAT.association_type_code='OPERATION'
--  AND MAT.inventory_item_id is not null
  AND  MSIV.organization_id = V.organization_id
  AND  V.visit_id = VT.visit_id
  AND  VT.visit_task_id = c_visit_task_id
  AND  MSIV.inventory_item_id = MAT.inventory_item_id
  AND  MAT.object_id in
    (  SELECT   RO.operation_id
       FROM     AHL_OPERATIONS_VL OP,
           AHL_ROUTE_OPERATIONS RO
       WHERE    OP.operation_id=RO.operation_id
       AND      OP.revision_status_code='COMPLETE'
       AND      RO.route_id=c_route_id
       AND      OP.revision_number IN
           ( SELECT MAX(revision_number)
             FROM   AHL_OPERATIONS_B_KFV
             WHERE  concatenated_segments=OP.concatenated_segments
             AND    TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
                                           TRUNC(NVL(end_date_active,SYSDATE+1))
           )
    )
  Group By MAT.inventory_item_id,
           MSIV.primary_uom_code;

Line: 1244

  SELECT AR.rt_oper_resource_id
  FROM   AHL_RT_OPER_RESOURCES AR
  WHERE  AR.association_type_code='ROUTE'
  AND    AR.object_id=c_route_id;

Line: 1256

SELECT sum(NVL(AR.duration,0)* NVL(AR.quantity,0)) duration,
         BR.BILLABLE_ITEM_ID,
         MSIV.concatenated_segments item_name,
         MSIV.primary_uom_code uom_code
  FROM   AHL_RT_OPER_RESOURCES AR,
         BOM_RESOURCES BR,
         MTL_SYSTEM_ITEMS_VL MSIV,
         AHL_RESOURCE_MAPPINGS MAP
  WHERE  AR.association_type_code='ROUTE'
  AND    BR.resource_id=MAP.bom_resource_id
  AND    AR.object_id=c_route_id
  AND    BR.organization_id=c_org_id
  AND    MAP.aso_resource_id=AR.aso_resource_id
  AND    BR.BILLABLE_ITEM_ID is not null
  AND    MSIV.organization_id = c_org_id
  AND    MSIV.inventory_item_id = BR.BILLABLE_ITEM_ID
  Group By BR.BILLABLE_ITEM_ID,
           MSIV.concatenated_segments,
           MSIV.primary_uom_code;

Line: 1283

  /*  SELECT sum(NVL(AR.duration,0)* NVL(AR.quantity,0)) duration,
         MSIV.primary_uom_code uom_code,
         MSIV.concatenated_segments item_name,
         BR.BILLABLE_ITEM_ID
  FROM   AHL_RT_OPER_RESOURCES AR,
         BOM_RESOURCES BR,
         MTL_SYSTEM_ITEMS_VL MSIV,
         AHL_RESOURCE_MAPPINGS MAP
  WHERE  AR.association_type_code='OPERATION'
  AND    BR.resource_id=MAP.bom_resource_id
  AND    BR.organization_id=c_org_id
  AND    MAP.aso_resource_id=AR.aso_resource_id
  AND    BR.BILLABLE_ITEM_ID is not null
  AND    MSIV.inventory_item_id = BR.BILLABLE_ITEM_ID
  AND    MSIV.organization_id = c_org_id
  AND  AR.object_id in
    (  SELECT   RO.operation_id
       FROM     AHL_OPERATIONS_VL OP,
           AHL_ROUTE_OPERATIONS RO
       WHERE    OP.operation_id=RO.operation_id
       AND      OP.revision_status_code='COMPLETE'
       AND      RO.route_id=c_route_id
       AND      OP.revision_number IN
           ( SELECT MAX(revision_number)
             FROM   AHL_OPERATIONS_B_KFV
             WHERE  concatenated_segments=OP.concatenated_segments
             AND    TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
                                           TRUNC(NVL(end_date_active,SYSDATE+1))
           )
    )
  Group By BR.BILLABLE_ITEM_ID,
           MSIV.concatenated_segments,
           MSIV.primary_uom_code;

Line: 1317

   select   sum(nvl(ar.duration,0)* nvl(ar.quantity,0)) duration,
            msiv.primary_uom_code uom_code,
            msiv.concatenated_segments item_name,
            br.billable_item_id
   from     ahl_rt_oper_resources ar,
            bom_resources br,
            mtl_system_items_kfv msiv,
            ahl_resource_mappings map
   where    ar.association_type_code='OPERATION'
   and      br.resource_id=map.bom_resource_id
   and      br.organization_id=c_org_id
   and      map.aso_resource_id=ar.aso_resource_id
   and      br.billable_item_id is not null
   and      msiv.inventory_item_id = br.billable_item_id
   and      msiv.organization_id = c_org_id
   and      ar.object_id in
               (  select   ro.operation_id
                  from     ahl_operations_b_kfv op,
                           ahl_route_operations ro
                  where    op.operation_id=ro.operation_id
                  and      op.revision_status_code='COMPLETE'
                  and      ro.route_id=c_route_id
                  and      op.revision_number in
                              (  select   max(revision_number)
                                 from     ahl_operations_b_kfv
                                 where    concatenated_segments=op.concatenated_segments
                                 and      trunc(sysdate) between trunc(start_date_active)
                                 and      trunc(nvl(end_date_active,sysdate+1))
                              )
               )
   group by    br.billable_item_id,
               msiv.concatenated_segments,
               msiv.primary_uom_code;

Line: 1520

  SELECT sum(NVL(AR.USAGE_RATE_OR_AMOUNT,0)) duration,
       --sum(NVL(AR.QUANTITY,0)) quantity,
--         AR.primary_uom uom_code,
         BR.BILLABLE_ITEM_ID,
         AR.TRANSACTION_DATE,
         MSIV.concatenated_segments item_name,
         MSIV.primary_uom_code uom_code
  FROM   AHL_WIP_RESOURCE_TXNS_V AR,
         MTL_SYSTEM_ITEMS_VL MSIV,
         AHL_VISITS_B V,
         AHL_VISIT_TASKS_B VT,
         BOM_RESOURCES BR
  WHERE  BR.resource_id=AR.resource_id
  AND    BR.BILLABLE_ITEM_ID is not null
  AND    AR.visit_task_id = c_visit_task_id
  AND    MSIV.organization_id = V.organization_id
  AND    V.visit_id = VT.visit_id
  AND    VT.visit_task_id = c_visit_task_id
  AND    MSIV.inventory_item_id = BR.BILLABLE_ITEM_ID
  AND    AR.JOB_STATUS_CODE <> 22  -- 'deleted' status
  AND    AR.JOB_STATUS_CODE <> 7  -- 'cancelled' status
  Group By BR.BILLABLE_ITEM_ID,
           MSIV.concatenated_segments,
           MSIV.primary_uom_code,
           AR.TRANSACTION_DATE;

Line: 1627

SELECT MAT.inventory_item_id,
         MAT.creation_date,
         SUM(NVL(MAT.quantity,0)) quantity,
         MSIV.primary_uom_code uom_code
 --        MAT.uom uom_code
  FROM AHL_WORKORDER_MTL_TXNS MAT,
       AHL_WORKORDERS_V AWOS,
       MTL_SYSTEM_ITEMS_VL MSIV,
       AHL_VISITS_B V,
       AHL_VISIT_TASKS_B VT,
       AHL_WORKORDER_OPERATIONS_V AWOP
  WHERE AWOP.WORKORDER_ID = AWOS.WORKORDER_ID
  AND  AWOP.WORKORDER_OPERATION_ID = MAT.WORKORDER_OPERATION_ID
  AND  MAT.inventory_item_id = MSIV.inventory_item_id
  AND  MSIV.organization_id = V.organization_id
  AND  V.visit_id = VT.visit_id
  AND  VT.visit_task_id = c_visit_task_id
  AND  AWOS.visit_task_id = c_visit_task_id
  AND  AWOS.job_status_code <> 22  -- 'deleted' status
  AND  AWOS.JOB_STATUS_CODE <> 7  -- 'cancelled' status
  GROUP BY MAT.inventory_item_id,
         MAT.creation_date,
         MSIV.primary_uom_code;

Line: 1653

SELECT   MAT.inventory_item_id,
         MAT.creation_date,
         SUM(NVL(MAT.quantity,0)) quantity,
         MSIV.primary_uom_code uom_code
FROM     AHL_WORKORDER_MTL_TXNS MAT,
         AHL_WORKORDERS AWOS,
         MTL_SYSTEM_ITEMS_B MSIV,
         AHL_VISITS_B V,
         AHL_VISIT_TASKS_B VT,
         AHL_WORKORDER_OPERATIONS_V AWOP
WHERE    AWOP.WORKORDER_ID = AWOS.WORKORDER_ID
AND      AWOP.WORKORDER_OPERATION_ID = MAT.WORKORDER_OPERATION_ID
AND      MAT.inventory_item_id = MSIV.inventory_item_id
AND      MSIV.organization_id = V.organization_id
AND      V.visit_id = VT.visit_id
AND      VT.visit_task_id = c_visit_task_id
AND      AWOS.visit_task_id = c_visit_task_id
AND      AWOS.status_code <> 22  -- 'deleted' status
AND      AWOS.STATUS_CODE <> 7  -- 'cancelled' status
GROUP BY MAT.inventory_item_id,
         MAT.creation_date,
         MSIV.primary_uom_code;

Line: 1857

  SELECT UOM_CODE
  FROM mtl_units_of_measure_vl
  WHERE UOM_CLASS = 'Time'
  AND UOM_CODE = c_UOM_code;