APPS.AHL_RM_RT_OPER_RESOURCE_PVT SQL Statements

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

Line: 12

G_LAST_UPDATE_DATE  DATE    := SYSDATE;

Line: 13

G_LAST_UPDATED_BY   NUMBER(15)  := FND_GLOBAL.user_id;

Line: 14

G_LAST_UPDATE_LOGIN   NUMBER(15)  := FND_GLOBAL.login_id;

Line: 289

  p_x_rt_oper_resource_rec.last_update_date := SYSDATE;

Line: 290

  p_x_rt_oper_resource_rec.last_updated_by := FND_GLOBAL.user_id;

Line: 291

  p_x_rt_oper_resource_rec.last_update_login := FND_GLOBAL.login_id;

Line: 429

SELECT  aso_resource_id,
        quantity,
        duration,
        attribute_category,
        attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15,
        scheduled_type_id,  -- added for bug fix 6512803.
        -- Bug # 7644260 (FP for ER # 6998882) -- start
        schedule_seq
        -- Bug # 7644260 (FP for ER # 6998882) -- end
FROM    AHL_RT_OPER_RESOURCES
WHERE   rt_oper_resource_id = c_rt_oper_resource_id;

Line: 458

SELECT resource_type_id
FROM AHL_RESOURCES
WHERE resource_id = c_resource_id;

Line: 684

    SELECT 'X'
      FROM ahl_alternate_resources
     WHERE rt_oper_resource_id = c_rt_oper_resource_id
       AND aso_resource_id = c_aso_resource_id;

Line: 692

  SELECT  resource_type_id,
      resource_type,
      aso_resource_name
  FROM    AHL_RT_OPER_RESOURCES_V
  WHERE   rt_oper_resource_id = c_rt_oper_resource_id;

Line: 701

  SELECT alternate_resource_id FROM AHL_ALTERNATE_RESOURCES
  WHERE rt_oper_resource_id = c_rt_oper_resource_id;

Line: 899

SELECT 'X'
FROM   AHL_RESOURCES
WHERE  resource_id = c_aso_resource_id
 AND  resource_type_id = c_resource_type_id;

Line: 990

SELECT   resource_type_id,
         resource_type,
         aso_resource_id,
         aso_resource_name
FROM     AHL_RT_OPER_RESOURCES_V
WHERE    object_id = c_object_id
AND      association_type_code = c_association_type_code
GROUP BY resource_type_id,
         resource_type,
         aso_resource_id,
         aso_resource_name
HAVING   count(*) > 1;

Line: 1007

SELECT 'X'
FROM    ahl_resource_mappings
WHERE   DEPARTMENT_ID IS NOT NULL
        AND aso_resource_id in
        (SELECT ASO_RESOURCE_ID
        FROM    ahl_rt_oper_resources
        WHERE   object_id                 = c_object_id
                AND ASSOCIATION_TYPE_CODE = c_association_type_code
        )
GROUP BY bom_org_id
HAVING count(DISTINCT DEPARTMENT_ID) > 1;

Line: 1022

SELECT 'X'
FROM    ahl_resource_mappings
WHERE   DEPARTMENT_ID IS NOT NULL
        AND aso_resource_id in
        (SELECT ALTR.ASO_RESOURCE_ID
        FROM    ahl_alternate_resources ALTR,
                (SELECT RT_OPER_RESOURCE_ID
                FROM    ahl_rt_oper_resources
                WHERE   RT_OPER_RESOURCE_ID      <> c_rt_oper_res_id
                        AND ASSOCIATION_TYPE_CODE = c_association_type_code
                        AND OBJECT_ID             = c_object_id
                )
                ROR
        WHERE   ROR.RT_OPER_RESOURCE_ID = ALTR.rt_oper_resource_id
        )
        OR aso_resource_id =
        (SELECT aso_resource_id
        FROM    ahl_rt_oper_resources
        WHERE   RT_OPER_RESOURCE_ID = c_rt_oper_res_id
        )
GROUP BY bom_org_id
HAVING count(DISTINCT DEPARTMENT_ID) > 1;

Line: 1047

SELECT  RT_OPER_RESOURCE_ID
FROM    AHL_RT_OPER_RESOURCES
WHERE   ASSOCIATION_TYPE_CODE = c_association_type_code
        AND OBJECT_ID         = c_object_id;

Line: 1148

select revision_status_code
from ahl_routes_app_v
where route_id = p_route_id;

Line: 1159

select revision_status_code
from ahl_operations_b
where operation_id = p_operation_id;

Line: 1166

select min(schedule_seq)
from   ahl_rt_oper_resources
where  object_id = c_object_id
and    association_type_code = c_association_type_code
and    schedule_seq IS NOT NULL ;

Line: 1402

      UPDATE ahl_operations_b
      SET revision_status_code = 'DRAFT'
      WHERE operation_id = p_object_id;

Line: 1433

      UPDATE ahl_routes_b
      SET revision_status_code = 'DRAFT'
      WHERE route_id = p_object_id;

Line: 1455

        INSERT INTO AHL_RT_OPER_RESOURCES
        (
          rt_oper_resource_ID,
          OBJECT_VERSION_NUMBER,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN,
          association_type_code,
          object_ID,
          aso_resource_id,
          quantity,
          duration,
          ATTRIBUTE_CATEGORY,
          ATTRIBUTE1,
          ATTRIBUTE2,
          ATTRIBUTE3,
          ATTRIBUTE4,
          ATTRIBUTE5,
          ATTRIBUTE6,
          ATTRIBUTE7,
          ATTRIBUTE8,
          ATTRIBUTE9,
          ATTRIBUTE10,
          ATTRIBUTE11,
          ATTRIBUTE12,
          ATTRIBUTE13,
          ATTRIBUTE14,
          ATTRIBUTE15,
          scheduled_type_id,  -- added for bug fix 6512803.
          -- Bug # 7644260 (FP for ER # 6998882) -- start
          schedule_seq
          -- Bug # 7644260 (FP for ER # 6998882) -- end
        ) VALUES
        (
          AHL_RT_OPER_RESOURCES_S.NEXTVAL,
          1,
          G_LAST_UPDATE_DATE,
          G_LAST_UPDATED_BY,
          G_CREATION_DATE,
          G_CREATED_BY,
          G_LAST_UPDATE_LOGIN,
          p_association_type_code,
          p_object_id,
          p_x_rt_oper_resource_tbl(i).aso_resource_id,
          p_x_rt_oper_resource_tbl(i).quantity,
          p_x_rt_oper_resource_tbl(i).duration,
          p_x_rt_oper_resource_tbl(i).attribute_category,
          p_x_rt_oper_resource_tbl(i).attribute1,
          p_x_rt_oper_resource_tbl(i).attribute2,
          p_x_rt_oper_resource_tbl(i).attribute3,
          p_x_rt_oper_resource_tbl(i).attribute4,
          p_x_rt_oper_resource_tbl(i).attribute5,
          p_x_rt_oper_resource_tbl(i).attribute6,
          p_x_rt_oper_resource_tbl(i).attribute7,
          p_x_rt_oper_resource_tbl(i).attribute8,
          p_x_rt_oper_resource_tbl(i).attribute9,
          p_x_rt_oper_resource_tbl(i).attribute10,
          p_x_rt_oper_resource_tbl(i).attribute11,
          p_x_rt_oper_resource_tbl(i).attribute12,
          p_x_rt_oper_resource_tbl(i).attribute13,
          p_x_rt_oper_resource_tbl(i).attribute14,
          p_x_rt_oper_resource_tbl(i).attribute15,
          p_x_rt_oper_resource_tbl(i).scheduled_type_id,
          -- Bug # 7644260 (FP for ER # 6998882) -- start
          p_x_rt_oper_resource_tbl(i).schedule_seq
          -- Bug # 7644260 (FP for ER # 6998882) -- end
        ) RETURNING rt_oper_resource_id INTO l_rt_oper_resource_id;

Line: 1540

      'AHL_RT_OPER_RESOURCES insert error = ['||SQLERRM||']'
    );

Line: 1550

        UPDATE AHL_RT_OPER_RESOURCES SET
          object_version_number   = object_version_number + 1,
          last_update_date        = G_LAST_UPDATE_DATE,
          last_updated_by         = G_LAST_UPDATED_BY,
          last_update_login       = G_LAST_UPDATE_LOGIN,
          aso_resource_id         = p_x_rt_oper_resource_tbl(i).aso_resource_id,
          quantity                = p_x_rt_oper_resource_tbl(i).quantity,
          duration                = p_x_rt_oper_resource_tbl(i).duration,
          attribute_category      = p_x_rt_oper_resource_tbl(i).attribute_category,
          attribute1              = p_x_rt_oper_resource_tbl(i).attribute1,
          attribute2              = p_x_rt_oper_resource_tbl(i).attribute2,
          attribute3              = p_x_rt_oper_resource_tbl(i).attribute3,
          attribute4              = p_x_rt_oper_resource_tbl(i).attribute4,
          attribute5              = p_x_rt_oper_resource_tbl(i).attribute5,
          attribute6              = p_x_rt_oper_resource_tbl(i).attribute6,
          attribute7              = p_x_rt_oper_resource_tbl(i).attribute7,
          attribute8              = p_x_rt_oper_resource_tbl(i).attribute8,
          attribute9              = p_x_rt_oper_resource_tbl(i).attribute9,
          attribute10             = p_x_rt_oper_resource_tbl(i).attribute10,
          attribute11             = p_x_rt_oper_resource_tbl(i).attribute11,
          attribute12             = p_x_rt_oper_resource_tbl(i).attribute12,
          attribute13             = p_x_rt_oper_resource_tbl(i).attribute13,
          attribute14             = p_x_rt_oper_resource_tbl(i).attribute14,
          attribute15             = p_x_rt_oper_resource_tbl(i).attribute15,
          -- added for bug fix# 6512803.
          scheduled_type_id       = p_x_rt_oper_resource_tbl(i).scheduled_type_id,
          -- Bug # 7644260 (FP for ER # 6998882) -- start
          schedule_seq            = p_x_rt_oper_resource_tbl(i).schedule_seq
          -- Bug # 7644260 (FP for ER # 6998882) -- end
        WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id
        AND object_version_number = p_x_rt_oper_resource_tbl(i).object_version_number;

Line: 1604

      'AHL_RT_OPER_RESOURCES update error = ['||SQLERRM||']'
    );

Line: 1613

      DELETE FROM ahl_alternate_resources
      WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id;

Line: 1618

      DELETE FROM AHL_RT_OPER_RESOURCES
      WHERE rt_oper_resource_id = p_x_rt_oper_resource_tbl(i).rt_oper_resource_id
      AND object_version_number = p_x_rt_oper_resource_tbl(i).object_version_number;

Line: 1665

            UPDATE ahl_rt_oper_resources
            SET    schedule_seq = l_min_sch_seq
            WHERE  object_id = p_object_id
            AND    association_type_code = p_association_type_code
            AND    schedule_seq IS NULL ;

Line: 1952

SELECT  activity_id,
        activity,
        cost_basis_id,
        cost_basis,
        scheduled_type_id,
        scheduled_type,
        autocharge_type_id,
        autocharge_type,
        standard_rate_flag,
        standard_rate
FROM    AHL_RT_OPER_RESOURCES_V
WHERE   rt_oper_resource_id = c_rt_oper_resource_id;

Line: 2073

SELECT --DISTINCT
RES.OBJECT_ID,
RES.ASSOCIATION_TYPE_CODE,
AR.resource_type_id,
RES.duration,
AR.NAME
FROM AHL_RT_OPER_RESOURCES RES, AHL_RESOURCES AR
WHERE RES.aso_resource_id = AR.resource_id
AND RES.RT_OPER_RESOURCE_ID = C_RT_OPER_RESOURCE_ID;

Line: 2085

select revision_status_code
from ahl_routes_app_v
where route_id = p_route_id;

Line: 2093

select revision_status_code
from ahl_operations_b
where operation_id = p_operation_id;

Line: 2190

              SELECT meaning
              INTO p_x_rt_oper_cost_rec.scheduled_type
              FROM fnd_lookup_values_vl
              WHERE lookup_type = 'BOM_RESOURCE_SCHEDULE_TYPE'
                AND lookup_code = p_x_rt_oper_cost_rec.scheduled_type_id;

Line: 2294

  UPDATE ahl_operations_b
  SET revision_status_code = 'DRAFT'
  WHERE operation_id = l_object_id;

Line: 2342

  UPDATE ahl_routes_b
  SET revision_status_code = 'DRAFT'
  WHERE route_id = l_object_id;

Line: 2358

  UPDATE AHL_RT_OPER_RESOURCES SET
          object_version_number   = object_version_number + 1,
          activity_id             = p_x_rt_oper_cost_rec.activity_id,
          cost_basis_id           = p_x_rt_oper_cost_rec.cost_basis_id,
          scheduled_type_id       = p_x_rt_oper_cost_rec.scheduled_type_id,
          autocharge_type_id      = p_x_rt_oper_cost_rec.autocharge_type_id,
          standard_rate_flag      = p_x_rt_oper_cost_rec.standard_rate_flag,
          last_update_date        = SYSDATE,
          last_updated_by         = FND_GLOBAL.user_id,
          last_update_login       = FND_GLOBAL.login_id
  WHERE rt_oper_resource_id = p_x_rt_oper_cost_rec.rt_oper_resource_id
  AND object_version_number = p_x_rt_oper_cost_rec.object_version_number;

Line: 2592

  p_x_alt_resource_rec.last_update_date := SYSDATE;

Line: 2593

  p_x_alt_resource_rec.last_updated_by := FND_GLOBAL.user_id;

Line: 2594

  p_x_alt_resource_rec.last_update_login := FND_GLOBAL.login_id;

Line: 2704

SELECT  alternate_resource_id,
        aso_resource_id,
        priority,
        attribute_category,
        attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15
FROM    AHL_ALTERNATE_RESOURCES
WHERE   alternate_resource_id = c_alt_resource_id;

Line: 2958

SELECT resource_type_id
FROM   AHL_RT_OPER_RESOURCES_V
WHERE  rt_oper_resource_id = c_rt_oper_resource_id;

Line: 2964

SELECT resource_type_id
FROM   AHL_RESOURCES
WHERE  resource_id = c_aso_resource_id;

Line: 3004

SELECT   name
FROM     AHL_ALTERNATE_RESOURCES_V
WHERE    rt_oper_resource_id = c_rt_oper_resource_id
GROUP BY NAME
HAVING   count(*) > 1;

Line: 3015

SELECT   priority
FROM     AHL_ALTERNATE_RESOURCES
WHERE    rt_oper_resource_id = c_rt_oper_resource_id
GROUP BY priority
HAVING   count(*) > 1;

Line: 3025

SELECT aso_resource_name
from   ahl_rt_oper_resources_v
where  rt_oper_resource_id = c_rt_oper_resource_id;

Line: 3031

select name
from   AHL_alternate_resources_v
where  rt_oper_resource_id = c_rt_oper_resource_id
and    name = c_aso_resource_name;

Line: 3039

SELECT 'X'
FROM    ahl_resource_mappings
WHERE   DEPARTMENT_ID IS NOT NULL
        AND aso_resource_id in
        (
        SELECT ASO_RESOURCE_ID
        FROM    ahl_rt_oper_resources
        WHERE   object_id                 = c_object_id
                AND ASSOCIATION_TYPE_CODE = c_association_type_code
                AND ASO_RESOURCE_ID <> c_aso_res_id
        )
        OR aso_resource_id = c_alt_res_id
GROUP BY bom_org_id
HAVING count(DISTINCT DEPARTMENT_ID) > 1;

Line: 3056

SELECT OBJECT_ID,ASSOCIATION_TYPE_CODE, ASO_RESOURCE_ID
FROM AHL_RT_OPER_RESOURCES
WHERE RT_OPER_RESOURCE_ID= c_rt_oper_resource_id;

Line: 3062

SELECT ASO_RESOURCE_ID
FROM AHL_ALTERNATE_RESOURCES
WHERE RT_OPER_RESOURCE_ID= c_rt_oper_resource_id;

Line: 3352

        INSERT INTO AHL_ALTERNATE_RESOURCES
        (
          ALTERNATE_RESOURCE_ID,
          OBJECT_VERSION_NUMBER,
          LAST_UPDATE_DATE,
          LAST_UPDATED_BY,
          CREATION_DATE,
          CREATED_BY,
          LAST_UPDATE_LOGIN,
          RT_OPER_RESOURCE_ID,
          ASO_RESOURCE_ID,
          PRIORITY,
          ATTRIBUTE_CATEGORY,
          ATTRIBUTE1,
          ATTRIBUTE2,
          ATTRIBUTE3,
          ATTRIBUTE4,
          ATTRIBUTE5,
          ATTRIBUTE6,
          ATTRIBUTE7,
          ATTRIBUTE8,
          ATTRIBUTE9,
          ATTRIBUTE10,
          ATTRIBUTE11,
          ATTRIBUTE12,
          ATTRIBUTE13,
          ATTRIBUTE14,
          ATTRIBUTE15
        ) VALUES
        (
          AHL_ALTERNATE_RESOURCES_S.NEXTVAL,
          1,
          G_LAST_UPDATE_DATE,
          G_LAST_UPDATED_BY,
          G_CREATION_DATE,
          G_CREATED_BY,
          G_LAST_UPDATE_LOGIN,
          p_rt_oper_resource_id,
          p_x_alt_resource_tbl(i).aso_resource_id,
          p_x_alt_resource_tbl(i).priority,
          p_x_alt_resource_tbl(i).attribute_category,
          p_x_alt_resource_tbl(i).attribute1,
          p_x_alt_resource_tbl(i).attribute2,
          p_x_alt_resource_tbl(i).attribute3,
          p_x_alt_resource_tbl(i).attribute4,
          p_x_alt_resource_tbl(i).attribute5,
          p_x_alt_resource_tbl(i).attribute6,
          p_x_alt_resource_tbl(i).attribute7,
          p_x_alt_resource_tbl(i).attribute8,
          p_x_alt_resource_tbl(i).attribute9,
          p_x_alt_resource_tbl(i).attribute10,
          p_x_alt_resource_tbl(i).attribute11,
          p_x_alt_resource_tbl(i).attribute12,
          p_x_alt_resource_tbl(i).attribute13,
          p_x_alt_resource_tbl(i).attribute14,
          p_x_alt_resource_tbl(i).attribute15
        ) RETURNING alternate_resource_id INTO l_alt_resource_id ;

Line: 3426

      'AHL_ALTERNATE_RESOURCES insert error = ['||SQLERRM||']'
    );

Line: 3436

        UPDATE AHL_ALTERNATE_RESOURCES SET
          object_version_number   = object_version_number + 1,
          last_update_date        = G_LAST_UPDATE_DATE,
          last_updated_by         = G_LAST_UPDATED_BY,
          last_update_login       = G_LAST_UPDATE_LOGIN,
          aso_resource_id         = p_x_alt_resource_tbl(i).aso_resource_id,
          priority                = p_x_alt_resource_tbl(i).priority,
          attribute_category      = p_x_alt_resource_tbl(i).attribute_category,
          attribute1              = p_x_alt_resource_tbl(i).attribute1,
          attribute2              = p_x_alt_resource_tbl(i).attribute2,
          attribute3              = p_x_alt_resource_tbl(i).attribute3,
          attribute4              = p_x_alt_resource_tbl(i).attribute4,
          attribute5              = p_x_alt_resource_tbl(i).attribute5,
          attribute6              = p_x_alt_resource_tbl(i).attribute6,
          attribute7              = p_x_alt_resource_tbl(i).attribute7,
          attribute8              = p_x_alt_resource_tbl(i).attribute8,
          attribute9              = p_x_alt_resource_tbl(i).attribute9,
          attribute10             = p_x_alt_resource_tbl(i).attribute10,
          attribute11             = p_x_alt_resource_tbl(i).attribute11,
          attribute12             = p_x_alt_resource_tbl(i).attribute12,
          attribute13             = p_x_alt_resource_tbl(i).attribute13,
          attribute14             = p_x_alt_resource_tbl(i).attribute14,
          attribute15             = p_x_alt_resource_tbl(i).attribute15
        WHERE alternate_resource_id = p_x_alt_resource_tbl(i).alternate_resource_id
        AND object_version_number = p_x_alt_resource_tbl(i).object_version_number;

Line: 3484

      'AHL_ALTERNATE_RESOURCES update error = ['||SQLERRM||']'
    );

Line: 3493

      DELETE FROM AHL_ALTERNATE_RESOURCES
      WHERE alternate_resource_id = p_x_alt_resource_tbl(i).alternate_resource_id
      AND object_version_number = p_x_alt_resource_tbl(i).object_version_number;