APPS.AMS_APPROVERS_PVT SQL Statements

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

Line: 52

FUNCTION seed_needs_update(
   p_approvers_rec   IN  Approvers_Rec_Type
) RETURN VARCHAR2;   -- FND_API.g_true/g_false

Line: 90

PROCEDURE Check_Dates_Update_Range (
   p_approvers_rec   IN  Approvers_Rec_Type,
   x_return_status  OUT NOCOPY   VARCHAR2
);

Line: 144

     SELECT ams_approvers_s.NEXTVAL
     FROM   dual;

Line: 148

     SELECT 1 FROM   dual
       WHERE EXISTS (SELECT 1 FROM   ams_approvers
                   WHERE  approver_id = x_id);

Line: 217

   Ams_Utility_Pvt.debug_message (l_full_name || ': Insert');

Line: 222

   Ams_Approvers_Pkg.INSERT_ROW (
       X_ROWID  => l_row_id,
       X_APPROVER_ID => l_approvers_rec.approver_id,
       X_SEEDED_FLAG => NVL(l_approvers_rec.SEEDED_FLAG, 'N'),
       X_ACTIVE_FLAG => NVL(l_approvers_rec.ACTIVE_FLAG, 'Y'),
       X_START_DATE_ACTIVE => l_approvers_rec.START_DATE_ACTIVE,
       X_END_DATE_ACTIVE => l_approvers_rec.END_DATE_ACTIVE,
       X_OBJECT_VERSION_NUMBER => 1, --l_approvers_rec.l_obj_verno,
       --X_SECURITY_GROUP_ID => l_approvers_rec.SECURITY_GROUP_ID,
       X_AMS_APPROVAL_DETAIL_ID => l_approvers_rec.AMS_APPROVAL_DETAIL_ID,
       X_APPROVER_SEQ => l_approvers_rec.APPROVER_SEQ,
       X_APPROVER_TYPE => l_approvers_rec.APPROVER_TYPE,
       X_OBJECT_APPROVER_ID => l_approvers_rec.OBJECT_APPROVER_ID,
       X_NOTIFICATION_TYPE => l_approvers_rec.NOTIFICATION_TYPE,
       X_NOTIFICATION_TIMEOUT => l_approvers_rec.NOTIFICATION_TYPE,
       X_CREATION_DATE => SYSDATE,
       X_CREATED_BY => Fnd_Global.User_Id,
       X_LAST_UPDATE_DATE => SYSDATE,
       X_LAST_UPDATED_BY => Fnd_Global.User_Id,
       X_LAST_UPDATE_LOGIN => Fnd_Global.Conc_Login_Id
      ) ;

Line: 308

PROCEDURE Update_approvers (
   p_api_version       IN  NUMBER,
   p_init_msg_list     IN  VARCHAR2  := Fnd_Api.g_false,
   p_commit            IN  VARCHAR2  := Fnd_Api.g_false,
   p_validation_level  IN  NUMBER    := Fnd_Api.g_valid_level_full,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_approvers_rec   IN  Approvers_Rec_Type
)
IS

   L_API_VERSION   CONSTANT NUMBER := 1.0;

Line: 323

   L_API_NAME    CONSTANT VARCHAR2(30) := 'Update_Approvers';

Line: 332

    SAVEPOINT Update_approvers;

Line: 369

          p_validation_mode =>  Jtf_Plsql_Api.g_update,
        x_return_status   => l_return_status
       );

Line: 396

       OR seed_needs_update(l_approvers_rec) = Fnd_Api.g_true
    THEN
   -------------------------- update --------------------
      IF (AMS_DEBUG_HIGH_ON) THEN

      Ams_Utility_Pvt.debug_message (l_full_name || ': Update');

Line: 403

    Ams_Approvers_Pkg.UPDATE_ROW(
         X_APPROVER_ID => l_approvers_rec.approver_id,
         X_SEEDED_FLAG => l_approvers_rec.SEEDED_FLAG,
       X_ACTIVE_FLAG => l_approvers_rec.ACTIVE_FLAG,
         X_START_DATE_ACTIVE => l_approvers_rec.START_DATE_ACTIVE,
         X_END_DATE_ACTIVE => l_approvers_rec.END_DATE_ACTIVE,
         X_OBJECT_VERSION_NUMBER => l_approvers_rec.OBJECT_VERSION_NUMBER+1,
         --X_SECURITY_GROUP_ID => l_approvers_rec.SECURITY_GROUP_ID,
         X_AMS_APPROVAL_DETAIL_ID => l_approvers_rec.AMS_APPROVAL_DETAIL_ID,
         X_APPROVER_SEQ => l_approvers_rec.APPROVER_SEQ,
         X_APPROVER_TYPE => l_approvers_rec.APPROVER_TYPE,
         X_OBJECT_APPROVER_ID => l_approvers_rec.OBJECT_APPROVER_ID,
         X_NOTIFICATION_TYPE => l_approvers_rec.NOTIFICATION_TYPE,
         X_NOTIFICATION_TIMEOUT => l_approvers_rec.NOTIFICATION_TIMEOUT,
         X_LAST_UPDATE_DATE => SYSDATE,
         X_LAST_UPDATED_BY => Fnd_Global.User_Id,
         X_LAST_UPDATE_LOGIN => Fnd_Global.Conc_Login_Id
        );

Line: 439

     ROLLBACK TO Update_approvers;

Line: 448

     ROLLBACK TO Update_approvers;

Line: 457

     ROLLBACK TO Update_approvers;

Line: 468

END Update_approvers;

Line: 486

PROCEDURE Delete_approvers (
   p_api_version       IN  NUMBER,
   p_init_msg_list     IN  VARCHAR2  := Fnd_Api.g_false,
   p_commit            IN  VARCHAR2  := Fnd_Api.g_false,
   p_validation_level  IN  NUMBER    := Fnd_Api.g_valid_level_full,

   x_return_status     OUT NOCOPY VARCHAR2,
   x_msg_count         OUT NOCOPY NUMBER,
   x_msg_data          OUT NOCOPY VARCHAR2,

   p_approver_id          IN  NUMBER,
   p_object_version    IN  NUMBER
) IS

   CURSOR c_approvers IS
   SELECT   *
   FROM  ams_approvers
   WHERE approver_id = p_approver_id;

Line: 512

   l_api_name    CONSTANT VARCHAR2(30) := 'Delete_Approvers';

Line: 529

    SAVEPOINT Delete_approvers;

Line: 550

    Ams_Utility_Pvt.debug_message (l_full_name || ': Delete');

Line: 555

       Ams_Approvers_Pkg.DELETE_ROW (p_approver_id);

Line: 580

     ROLLBACK TO Delete_approvers;

Line: 589

     ROLLBACK TO Delete_approvers;

Line: 598

   ROLLBACK TO Delete_approvers;

Line: 609

END Delete_approvers;

Line: 941

   SELECT   *
   FROM     ams_approvers
   WHERE    approver_id = p_approvers_rec.approver_id;

Line: 1045

           SELECT 1 INTO l_count
         FROM AMS_APPROVERS
         WHERE approver_id =p_approvers_rec.approver_id
         AND start_date_active = p_approvers_rec.start_date_active
         AND end_date_active = p_approvers_rec.end_Date_active
         --AND security_group_id = p_approvers_rec.security_group_id
         AND ams_approval_detail_id = p_approvers_rec.ams_approval_detail_id
         AND approver_seq = p_approvers_rec.approver_seq
         AND approver_type = p_approvers_rec.approver_type
         AND object_approver_id = p_approvers_rec.object_approver_id
         AND notification_type = p_approvers_rec.notification_type
         AND notification_timeout = p_approvers_rec.notification_timeout
             AND seeded_flag = 'Y';

Line: 1064

         SELECT 1 INTO l_count
         FROM AMS_APPROVERS
         WHERE approver_id =p_approvers_rec.approver_id
         AND start_date_active = p_approvers_rec.start_date_active
         AND end_date_active = p_approvers_rec.end_Date_active
         --AND security_group_id = p_approvers_rec.security_group_id
         AND ams_approval_detail_id = p_approvers_rec.ams_approval_detail_id
         AND approver_seq = p_approvers_rec.approver_seq
         AND approver_type = p_approvers_rec.approver_type
         AND object_approver_id = p_approvers_rec.object_approver_id
         AND notification_type = p_approvers_rec.notification_type
         AND notification_timeout = p_approvers_rec.notification_timeout
         AND seeded_flag = 'Y';

Line: 1084

         SELECT 1 INTO l_count
       FROM AMS_APPROVERS
       WHERE approver_id =p_approvers_rec.approver_id
       AND start_date_active = p_approvers_rec.start_date_active
       AND end_date_active = p_approvers_rec.end_Date_active
       --AND security_group_id = p_approvers_rec.security_group_id
       AND ams_approval_detail_id = p_approvers_rec.ams_approval_detail_id
       AND approver_seq = p_approvers_rec.approver_seq
       AND approver_type = p_approvers_rec.approver_type
       AND object_approver_id = p_approvers_rec.object_approver_id
       AND notification_type = p_approvers_rec.notification_type
       AND notification_timeout = p_approvers_rec.notification_timeout
       AND seeded_flag = 'Y';

Line: 1114

FUNCTION seed_needs_update(
   p_approvers_rec         IN  Approvers_Rec_Type
)
RETURN VARCHAR2
IS
  l_count NUMBER := 0;

Line: 1123

   SELECT 1 INTO l_count
   FROM AMS_APPROVERS
   WHERE approver_id = p_approvers_rec.approver_id
   AND   seeded_flag = 'Y';

Line: 1133

      RETURN Fnd_Api.g_true;  -- needs update

Line: 1135

      RETURN Fnd_Api.g_false;  -- doesnt need update

Line: 1137

END seed_needs_update;

Line: 1149

   SELECT START_DATE_ACTIVE, END_DATE_ACTIVE
   FROM AMS_APPROVAL_DETAILS
   WHERE APPROVAL_DETAIL_ID = id_in;

Line: 1220

      SELECT 1 FROM   dual
      WHERE EXISTS (SELECT 1 FROM   ams_approvers
            WHERE  approver_seq = seq_num_in
            AND ams_approval_detail_id = id_in
            AND active_flag = 'Y'
            );

Line: 1257

   ELSIF (p_validation_mode = Jtf_Plsql_Api.g_update
         AND (p_approvers_rec.active_flag = 'Y' OR
              p_approvers_rec.active_flag = NULL OR p_approvers_rec.active_flag = Fnd_Api.g_miss_char)) THEN
      Check_Dates_Update_Range (
            p_approvers_rec => p_approvers_rec,
            x_return_status  => x_return_status
            );

Line: 1282

      SELECT 1 FROM dual
      WHERE EXISTS (SELECT 1 FROM ams_jtf_rs_emp_v
            WHERE  RESOURCE_ID = id_in);

Line: 1286

      SELECT 1 FROM dual
      WHERE EXISTS ( SELECT 1
                     FROM jtf_rs_role_relations rr, jtf_rs_roles_b rl
                     WHERE rr.role_id = rl.role_id
                     AND rr.role_resource_type = 'RS_INDIVIDUAL'
                     AND rr.delete_flag = 'N'
                     AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
                     AND rl.role_type_code in ( 'MKTGAPPR', 'AMSAPPR')
                     AND rr.role_id = id_in);

Line: 1297

      SELECT 1 FROM JTF_RS_DEFRESROLES_vl
            WHERE  ROLE_ID = id_in);

Line: 1301

      SELECT COUNT(1)
      FROM jtf_rs_role_relations rr, jtf_rs_roles_b rl
      WHERE rr.role_id = rl.role_id
      AND rr.role_resource_type = 'RS_INDIVIDUAL'
      AND rr.delete_flag = 'N'
      AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
      AND rl.role_type_code in ( 'MKTGAPPR', 'AMSAPPR')
      AND rr.role_id = id_in;

Line: 1316

         AND delete_flag = 'N'
         AND TRUNC(SYSDATE) BETWEEN TRUNC(res_rl_start_date)
         AND TRUNC(NVL(res_rl_end_date,SYSDATE));

Line: 1448

       SELECT start_date_active , end_date_active FROM ams_approval_details
              WHERE approval_detail_id = p_approvers_rec.ams_approval_detail_id;

Line: 1545

PROCEDURE Check_Dates_Update_Range (
   p_approvers_rec   IN  Approvers_Rec_Type,
   x_return_status  OUT NOCOPY   VARCHAR2
)IS
p_start_date DATE := p_approvers_rec.start_date_active;

Line: 1558

       SELECT start_date_active , end_date_active FROM ams_approval_details
              WHERE approval_detail_id = p_approvers_rec.ams_approval_detail_id;

Line: 1562

       SELECT start_date_active , end_date_active FROM ams_approvers
              WHERE approver_id = p_approvers_rec.approver_id;

Line: 1683

END Check_Dates_Update_Range;

Line: 1697

       SELECT approval_object, approval_type
       FROM ams_approval_details
       WHERE approval_detail_id = p_approvers_rec.ams_approval_detail_id;

Line: 1702

       SELECT seeded_flag, package_name, procedure_name
       FROM ams_object_rules_vl
       WHERE object_rule_id = p_approvers_rec.object_approver_id;