APPS.AHL_PRD_UTIL_PKG SQL Statements

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

Line: 34

  select subinventory_code
     from mtl_item_locations_kfv
     where organization_id = p_org_id
     and inventory_location_id = p_locator_id
     and nvl(disable_Date, sysdate) >= sysdate;

Line: 100

     select 'x'
            from mtl_material_statuses
            where status_id=p_condn_id
            and enabled_flag =1;

Line: 131

      select 'x'
            from MTL_TRANSACTION_REASONS
            where reason_id=p_reason_id
            and nvl(disable_date, sysdate) >= sysdate;

Line: 171

        SELECT status_id
                FROM MTL_SECONDARY_INVENTORIES
                WHERE ORGANIZATION_ID = p_org_Id
                AND SECONDARY_INVENTORY_NAME = p_Subinv_Code;

Line: 237

SELECT
        workorder_id
FROM
        ahl_workorders
WHERE
        visit_id = p_visit_id AND
        status_code not in (22, 7, 17) AND
        master_workorder_flag <> 'Y';

Line: 253

SELECT
        vst.item_instance_id,
        vtk.instance_id
FROM
        ahl_visit_tasks_b vtk,
        ahl_visits_b vst,
        ahl_workorders wo
WHERE
        wo.workorder_id = p_workorder_id AND
        vtk.visit_task_id = wo.visit_task_id AND
        vtk.visit_id = vst.visit_id;

Line: 270

SELECT
        csi_item_instance_id
FROM
        AHL_UNIT_EFFECTIVITIES_B
WHERE
        unit_effectivity_id = p_ue_id;

Line: 360

 * Cursor for selecting workorder status validity.
 */
CURSOR c_validate_wo_status(p_workorder_id IN NUMBER) IS
SELECT
        'X'
FROM
        ahl_workorders
WHERE
        workorder_id = p_workorder_id AND
        status_code in (7, 12, 22);

Line: 429

 * Cursor for selecting workorder status validity.
 */
CURSOR c_validate_wo_status(p_workorder_id IN NUMBER)
IS
SELECT
        'X'
FROM
        ahl_workorders
WHERE
        workorder_id = p_workorder_id AND
        status_code in (4, 5, 7, 12, 22);

Line: 445

SELECT
        'X'
FROM
        ahl_workorder_operations
WHERE
        workorder_id = p_workorder_id AND
        operation_sequence_num = p_op_seq_no AND
        status_code = 1;

Line: 520

SELECT
        mr_header_id,
        qa_collection_id,
        qa_inspection_type
FROM
        AHL_UNIT_EFFECTIVITIES_B
WHERE
        unit_effectivity_id = p_ue_id;

Line: 534

SELECT
        DISTINCT plan_id
FROM
        QA_RESULTS
WHERE
        collection_id = p_qa_collection_id;

Line: 546

SELECT
        qa_inspection_type
FROM
        AHL_MR_HEADERS_B
WHERE
        mr_header_id = p_mr_header_id;

Line: 558

SELECT
  QP.PLAN_ID
FROM
  QA_PLANS_VAL_V QP,
  QA_PLAN_TRANSACTIONS QPT,
  QA_PLAN_COLLECTION_TRIGGERS QPCT
WHERE
  QP.PLAN_ID = QPT.PLAN_ID AND
  QPT.PLAN_TRANSACTION_ID = QPCT.PLAN_TRANSACTION_ID AND
  QP.ORGANIZATION_ID =   (SELECT
                                ORGANIZATION_ID
                          FROM
                                AHL_VISITS_B VST,
                                AHL_VISIT_TASKS_B TSK
                          WHERE VST.VISIT_ID = TSK.VISIT_ID AND
                                TSK.UNIT_EFFECTIVITY_ID = p_ue_id AND
                          ROWNUM < 2) AND
  QPT.TRANSACTION_NUMBER = 2001 AND
  QPCT.COLLECTION_TRIGGER_ID = 87 AND
  QPCT.LOW_VALUE = p_qa_insp_type
GROUP BY
        QP.PLAN_ID,
        QPT.TRANSACTION_NUMBER
HAVING
        TRANSACTION_NUMBER = MAX(TRANSACTION_NUMBER);

Line: 677

SELECT AWOS.STATUS_CODE,
       NVL(VTS.INSTANCE_ID, VST.ITEM_INSTANCE_ID)
FROM
        AHL_WORKORDERS AWOS,
        AHL_VISITS_B VST,
        AHL_VISIT_TASKS_B VTS
WHERE
        AWOS.VISIT_TASK_ID = VTS.VISIT_TASK_ID   AND
        VST.VISIT_ID = VTS.VISIT_ID  AND
        WORKORDER_ID = p_workorder_id;

Line: 760

SELECT
        WREQ.operation_sequence,
        WREQ.resource_sequence
FROM
        AHL_WORK_ASSIGNMENTS WASS,
        AHL_PP_REQUIREMENT_V WREQ
WHERE
        WASS.assignment_id = p_assignment_id AND
        WREQ.resource_id = WASS.operation_resource_id AND
        login_date IS NOT NULL;

Line: 832

 * Cursor for selecting workorder status validity.
 */
CURSOR c_validate_wo_status(p_workorder_id IN NUMBER) IS
SELECT
        'X'
FROM
        ahl_workorders
WHERE
        workorder_id = p_workorder_id AND
        status_code in (1,4,5,7, 12,21, 22);

Line: 844

 * Cursor for selecting operation status validity.
 */
CURSOR c_validate_op_status(p_workorder_id IN NUMBER) IS
SELECT
        'X'
FROM
        ahl_workorder_operations
WHERE
        workorder_id = p_workorder_id AND
        status_code in (0,2);

Line: 928

SELECT STATUS_CODE
FROM AHL_WORKORDERS
WHERE WORKORDER_ID = c_wo_id;

Line: 994

SELECT 'x'
FROM AHL_WORKORDERS WO,wip_discrete_jobs WIP
WHERE WO.wip_entity_id = WIP.wip_entity_id
AND WIP.date_released IS NOT NULL AND
WO.WORKORDER_ID = c_wo_id;

Line: 1062

    SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
    FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
    WHERE WO.workorder_operation_id = AOR.workorder_operation_id
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND BOM.resource_id = AOR.resource_id
      AND BOM.resource_type = 2  -- Person
      AND WO.workorder_id = p_workorder_id
      AND WO.operation_sequence_num = p_operation_seq_num
      AND AWAS.employee_id = p_employee_id;

Line: 1076

    SELECT SUM(NVL(AOR.duration, 0))
    FROM AHL_OPERATION_RESOURCES AOR,
         BOM_RESOURCES BOMR,
         AHL_WORKORDER_OPERATIONS AWOP
    WHERE AOR.RESOURCE_ID = BOMR.RESOURCE_ID
      AND BOMR.resource_type = 2  -- Person
      AND AOR.WORKORDER_OPERATION_ID = AWOP.WORKORDER_OPERATION_ID
      AND AWOP.workorder_id = p_workorder_id
      AND AWOP.operation_sequence_num = p_operation_seq_num;

Line: 1147

    SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
    FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
    WHERE WO.workorder_operation_id = AOR.workorder_operation_id
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND BOM.resource_id = AOR.resource_id
      AND BOM.resource_type = 2  -- Person
      AND WO.workorder_id = p_workorder_id
      AND WO.operation_sequence_num = p_operation_seq_num
      AND AOR.resource_id = p_resource_id
      AND AWAS.employee_id = p_employee_id
      AND AOR.RESOURCE_SEQUENCE_NUM = p_resource_seq_num;

Line: 1164

    SELECT SUM((AWAS.assign_end_date - AWAS.assign_start_date) * 24)
    FROM AHL_WORKORDER_OPERATIONS WO, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, BOM_RESOURCES BOM
    WHERE WO.workorder_operation_id = AOR.workorder_operation_id
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND BOM.resource_id = AOR.resource_id
      AND BOM.resource_type = 2  -- Person
      AND WO.workorder_id = p_workorder_id
      AND WO.operation_sequence_num = p_operation_seq_num
      AND AOR.resource_id = p_resource_id
      AND AOR.RESOURCE_SEQUENCE_NUM = p_resource_seq_num;

Line: 1230

    SELECT   NVL( SUM( transaction_quantity ), 0 )
      FROM    WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
      WHERE   WT.resource_id = BRS.resource_id
        AND   BRS.resource_type = 2  -- person.
        AND   wt.wip_entity_id = p_wip_entity_id
        AND   wt.operation_seq_num = p_operation_seq_num
        AND   wt.employee_id = p_employee_id;

Line: 1242

    SELECT NVL( SUM( transaction_quantity ), 0 )
    FROM   WIP_COST_TXN_INTERFACE wcti, bom_resources br, wip_operation_resources wor
    WHERE  wcti.wip_entity_id = wor.wip_entity_id
      AND  wcti.operation_seq_num = wor.operation_seq_num
      AND  wcti.resource_seq_num = wor.resource_seq_num
      AND  wcti.organization_id = wor.organization_id
      AND  wor.resource_id = br.resource_id
      AND  br.resource_type = 2
      AND  wcti.wip_entity_id = p_wip_entity_id
      AND  wcti.operation_seq_num = p_operation_seq_num
      AND  wcti.employee_id = p_employee_id
      AND  wcti.process_status <> 3; -- skip errored txns.

Line: 1258

    SELECT   NVL( SUM( transaction_quantity ), 0 )
      FROM   WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
      WHERE  WT.resource_id = BRS.resource_id
        AND  BRS.resource_type = 2  -- person.
        AND  WT.wip_entity_id = p_wip_entity_id
        AND  WT.operation_seq_num = p_operation_seq_num;

Line: 1270

    SELECT NVL( SUM( transaction_quantity ), 0 )
    FROM   WIP_COST_TXN_INTERFACE wcti, bom_resources br, wip_operation_resources wor
    WHERE  wcti.wip_entity_id = wor.wip_entity_id
      AND  wcti.operation_seq_num = wor.operation_seq_num
      AND  wcti.resource_seq_num = wor.resource_seq_num
      AND  wcti.organization_id = wor.organization_id
      AND  wor.resource_id = br.resource_id
      AND  br.resource_type = 2
      AND  wcti.wip_entity_id = p_wip_entity_id
      AND  wcti.operation_seq_num = p_operation_seq_num
      AND  wcti.process_status <> 3; -- skip errored txns.

Line: 1357

    SELECT   NVL( SUM( transaction_quantity ), 0 )
      FROM    WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
      WHERE   WT.resource_id = BRS.resource_id
        AND   BRS.resource_type = 2  -- person.
        AND   WT.transaction_type = 1 -- resource txn.
        AND   wt.wip_entity_id = p_wip_entity_id
        AND   wt.operation_seq_num = p_operation_seq_num
        AND   wt.resource_seq_num = p_resource_seq_num
        AND   wt.employee_id = p_employee_id;

Line: 1372

    SELECT NVL( SUM( transaction_quantity ), 0 )
    FROM   WIP_COST_TXN_INTERFACE wcti, bom_resources br,
           wip_operation_resources wor
    WHERE  wcti.wip_entity_id = wor.wip_entity_id
      AND  wcti.operation_seq_num = wor.operation_seq_num
      AND  wcti.resource_seq_num = wor.resource_seq_num
      AND  wcti.organization_id = wor.organization_id
      AND  wor.resource_id = br.resource_id
      AND  br.resource_type = 2  -- person
      AND  wcti.transaction_type = 1  -- resource txn.
      AND  wcti.wip_entity_id = p_wip_entity_id
      AND  wcti.operation_seq_num = p_operation_seq_num
      AND  wcti.resource_seq_num = p_resource_seq_num
      AND  wcti.employee_id = p_employee_id
      AND  wcti.process_status <> 3; -- skip errored txns.

Line: 1392

    SELECT   NVL( SUM( transaction_quantity ), 0 )
      FROM    WIP_TRANSACTIONS WT, BOM_RESOURCES BRS
      WHERE   WT.resource_id = BRS.resource_id
        AND   BRS.resource_type = 2  -- person.
        AND   WT.transaction_type = 1 -- resource txn.
        AND   wt.wip_entity_id = p_wip_entity_id
        AND   wt.operation_seq_num = p_operation_seq_num
        AND   wt.resource_seq_num = p_resource_seq_num;

Line: 1407

    SELECT NVL( SUM( transaction_quantity ), 0 )
    FROM   WIP_COST_TXN_INTERFACE wcti, bom_resources br,
           wip_operation_resources wor
    WHERE  wcti.wip_entity_id = wor.wip_entity_id
      AND  wcti.operation_seq_num = wor.operation_seq_num
      AND  wcti.resource_seq_num = wor.resource_seq_num
      AND  wcti.organization_id = wor.organization_id
      AND  wor.resource_id = br.resource_id
      AND  br.resource_type = 2  -- person
      AND  wcti.transaction_type = 1  -- resource txn.
      AND  wcti.wip_entity_id = p_wip_entity_id
      AND  wcti.operation_seq_num = p_operation_seq_num
      AND  wcti.resource_seq_num = p_resource_seq_num
      AND  wcti.process_status <> 3; -- skip errored txns.

Line: 1517

    SELECT MIN(AWAS.ASSIGN_START_DATE)
    FROM WIP_Operation_Resources WOR, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDERS AW
    WHERE WOR.resource_id = AOR.resource_id
      AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND WOR.wip_entity_id = AW.wip_entity_id
      AND AWAS.employee_id = p_employee_id
      AND AW.workorder_id = p_workorder_id
      AND WOR.operation_seq_num = p_operation_seq_num;

Line: 1531

    SELECT SCHEDULED_START_DATE
    FROM AHL_WORKORDER_OPERATIONS_V  AO
    WHERE AO.workorder_id = p_workorder_id
      AND AO.operation_sequence_num = p_operation_seq_num;

Line: 1600

    SELECT MAX(AWAS.ASSIGN_END_DATE)
    FROM WIP_Operation_Resources WOR, AHL_OPERATION_RESOURCES AOR,
         AHL_WORK_ASSIGNMENTS AWAS, AHL_WORKORDERS AW
    WHERE WOR.resource_id = AOR.resource_id
      AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
      AND AOR.operation_resource_id = AWAS.operation_resource_id
      AND WOR.wip_entity_id = AW.wip_entity_id
      AND AWAS.employee_id = p_employee_id
      AND AW.workorder_id = p_workorder_id
      AND WOR.operation_seq_num = p_operation_seq_num;

Line: 1614

    SELECT SCHEDULED_END_DATE
    FROM AHL_WORKORDER_OPERATIONS_V  AO
    WHERE AO.workorder_id = p_workorder_id
      AND AO.operation_sequence_num = p_operation_seq_num;

Line: 1712

  SELECT AOR.rt_oper_resource_id,
         WIPO.organization_id,
         WIPO.department_id

  FROM   WIP_OPERATIONS  WIPO,
         AHL_WORKORDERS  AHLW,
         AHL_WORKORDER_OPERATIONS  AWO,
         AHL_OPERATION_RESOURCES   AOR

  WHERE  WIPO.wip_entity_id = AHLW.wip_entity_id
         AND AHLW.workorder_id      = AWO.workorder_id
         AND WIPO.operation_seq_num = AWO.operation_sequence_num
         AND AWO.workorder_operation_id = AOR.workorder_operation_id
         AND AOR.resource_id        = c_bom_resource_id
         AND WIPO.wip_entity_id     = c_wip_entity_id
         AND WIPO.operation_seq_num = c_operation_seq_num

  ORDER BY AOR.rt_oper_resource_id nulls last;

Line: 1732

  SELECT (select AHLR.name
            from AHL_RT_OPER_RESOURCES AROR,  AHL_RESOURCES AHLR
           where AROR.aso_resource_id = AHLR.resource_id
             and AROR.rt_oper_resource_id = AOR.rt_oper_resource_id
         ) resource_name,
         WIPO.organization_id,
         WIPO.department_id
  FROM
         WIP_OPERATIONS  WIPO,
         AHL_WORKORDERS  AHLW,
         AHL_WORKORDER_OPERATIONS  AWO,
         AHL_OPERATION_RESOURCES   AOR
  WHERE
         WIPO.wip_entity_id = AHLW.wip_entity_id
         AND WIPO.operation_seq_num = AWO.operation_sequence_num
         AND AWO.workorder_id = AHLW.workorder_id
         AND AWO.workorder_operation_id = AOR.workorder_operation_id
         AND WIPO.operation_seq_num = c_operation_seq_num
         AND AWO.OPERATION_SEQUENCE_NUM = c_operation_seq_num
         AND AHLW.wip_entity_id = c_wip_entity_id
         AND AOR.RESOURCE_SEQUENCE_NUM = c_resource_seq_num;

Line: 1758

  SELECT AHLR.name

  FROM   AHL_RESOURCE_MAPPINGS MAP,
         AHL_RESOURCES         AHLR

  WHERE  MAP.aso_resource_id = AHLR.resource_id
         AND MAP.bom_resource_id = c_bom_resource_id
         AND MAP.bom_org_id      = c_organization_id
         AND NVL(MAP.department_id, c_department_id) = c_department_id

  ORDER BY MAP.department_id nulls last;

Line: 1845

select plan_id,collection_id from ahl_workorders where workorder_id = P_workorder_id;

Line: 1886

select UE.qa_collection_id, NVL(UE.qa_inspection_type,MR.qa_inspection_type) from ahl_unit_effectivities_b UE,ahl_mr_headers_b MR
where UE.mr_header_id = MR.mr_header_id(+) and UE.unit_effectivity_id = P_ue_id;

Line: 1929

select plan_id,collection_id from ahl_workorder_operations where workorder_operation_id = P_workorder_operation_id;

Line: 1958

select organization_name into l_org_name from inv_organization_name_v where organization_id = P_org_id;