APPS.JTF_RS_GROUP_MEMBERS_PVT SQL Statements

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

Line: 6

   like create and delete resource group members.
   Its main procedures are as following:
   Create Resource Group Members
   Delete Resource Group Members
   These procedures does the business validations and then Calls the corresponding
   table handlers to do actual inserts and deletes into tables.
   ******************************************************************************************/

  /* Package variables. */

  G_PKG_NAME         VARCHAR2(30) := 'JTF_RS_GROUP_MEMBERS_PVT';

Line: 109

	 SELECT 'Y'
	 FROM jtf_rs_group_members
	 WHERE ROWID = l_rowid;

Line: 115

      SELECT source_id
      FROM jtf_rs_resource_extns
      WHERE resource_id = l_resource_id
	   AND category = 'EMPLOYEE';

Line: 121

      SELECT 'x'
      FROM JTF_RS_GROUPS_B a, JTF_RS_RESOURCE_EXTNS b
      WHERE a.group_id = l_group_id
       AND b.resource_id = l_resource_id
       AND trunc(sysdate) between a.start_date_active and nvl(a.end_date_active, sysdate)
       AND trunc(sysdate) between b.start_date_active and nvl(b.end_date_active, sysdate);

Line: 131

	 SELECT 'Y'
	 FROM jtf_rs_active_grp_mbrs
	 WHERE ROWID = l_rowid;

Line: 284

    SELECT count(*)
    INTO l_check_count
    FROM jtf_rs_group_members
    WHERE group_id = l_group_id
	 AND resource_id = l_resource_id
	 AND nvl(delete_flag,'N') <> 'Y';

Line: 322

    SELECT jtf_rs_group_members_s.nextval
    INTO l_group_member_id
    FROM dual;

Line: 329

    jtf_rs_group_members_aud_pvt.insert_member
    (P_API_VERSION => 1,
     P_INIT_MSG_LIST => fnd_api.g_false,
     P_COMMIT => fnd_api.g_false,
     P_GROUP_MEMBER_ID => l_group_member_id,
     P_GROUP_ID => l_group_id,
     P_RESOURCE_ID => l_resource_id,
     P_PERSON_ID => l_person_id,
	P_OBJECT_VERSION_NUMBER => 1,
     X_RETURN_STATUS => x_return_status,
     X_MSG_COUNT => x_msg_count,
     X_MSG_DATA => x_msg_data
    );

Line: 357

    /* Insert the row into the table by calling the table handler. */

    jtf_rs_group_members_pkg.insert_row(
      x_rowid => l_rowid,
      x_group_member_id => l_group_member_id,
      x_group_id => l_group_id,
      x_resource_id => l_resource_id,
      x_person_id => l_person_id,
	 x_delete_flag => 'N',
      x_attribute1 => p_attribute1,
      x_attribute2 => p_attribute2,
      x_attribute3 => p_attribute3,
      x_attribute4 => p_attribute4,
      x_attribute5 => p_attribute5,
      x_attribute6 => p_attribute6,
      x_attribute7 => p_attribute7,
      x_attribute8 => p_attribute8,
      x_attribute9 => p_attribute9,
      x_attribute10 => p_attribute10,
      x_attribute11 => p_attribute11,
      x_attribute12 => p_attribute12,
      x_attribute13 => p_attribute13,
      x_attribute14 => p_attribute14,
      x_attribute15 => p_attribute15,
      x_attribute_category => p_attribute_category,
      x_creation_date => SYSDATE,
      x_created_by => jtf_resource_utl.created_by,
      x_last_update_date => SYSDATE,
      x_last_updated_by => jtf_resource_utl.updated_by,
      x_last_update_login => jtf_resource_utl.login_id
    );

Line: 419

	  /* Insert the row into the active group members table by
	  calling the table handler. */

          jtf_rs_active_grp_mbrs_pkg.insert_row(x_rowid => l_rowid,
						x_group_member_id => l_group_member_id,
						x_group_id => l_group_id,
						x_resource_id => l_resource_id,
						x_person_id => l_person_id,
						x_creation_date => SYSDATE,
						x_created_by => jtf_resource_utl.created_by,
						x_last_update_date => SYSDATE,
						x_last_updated_by => jtf_resource_utl.updated_by,
						x_last_update_login => jtf_resource_utl.login_id
						);

Line: 726

  /* Procedure to update the resource group members. */

  PROCEDURE  update_resource_group_members
  (P_API_VERSION          IN   NUMBER,
   P_INIT_MSG_LIST        IN   VARCHAR2,
   P_COMMIT               IN   VARCHAR2,
   P_GROUP_MEMBER_ID      IN   JTF_RS_GROUP_MEMBERS.GROUP_MEMBER_ID%TYPE,
   P_GROUP_ID             IN   JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
   P_RESOURCE_ID          IN   JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
   P_PERSON_ID            IN   JTF_RS_GROUP_MEMBERS.PERSON_ID%TYPE,
   P_DELETE_FLAG          IN   JTF_RS_GROUP_MEMBERS.DELETE_FLAG%TYPE,
   P_ATTRIBUTE1           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE1%TYPE,
   P_ATTRIBUTE2           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE2%TYPE,
   P_ATTRIBUTE3           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE3%TYPE,
   P_ATTRIBUTE4           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE4%TYPE,
   P_ATTRIBUTE5           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE5%TYPE,
   P_ATTRIBUTE6           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE6%TYPE,
   P_ATTRIBUTE7           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE7%TYPE,
   P_ATTRIBUTE8           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE8%TYPE,
   P_ATTRIBUTE9           IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE9%TYPE,
   P_ATTRIBUTE10          IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE10%TYPE,
   P_ATTRIBUTE11          IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE11%TYPE,
   P_ATTRIBUTE12          IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE12%TYPE,
   P_ATTRIBUTE13          IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE13%TYPE,
   P_ATTRIBUTE14          IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE14%TYPE,
   P_ATTRIBUTE15          IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE15%TYPE,
   P_ATTRIBUTE_CATEGORY   IN   JTF_RS_GROUP_MEMBERS.ATTRIBUTE_CATEGORY%TYPE,
   P_OBJECT_VERSION_NUMBER   IN OUT NOCOPY  JTF_RS_GROUP_MEMBERS.OBJECT_VERSION_NUMBER%TYPE,
   X_RETURN_STATUS        OUT NOCOPY  VARCHAR2,
   X_MSG_COUNT            OUT NOCOPY  NUMBER,
   X_MSG_DATA             OUT NOCOPY VARCHAR2
  )
 IS
      l_api_version         	CONSTANT NUMBER := 1.0;

Line: 760

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

Line: 783

      CURSOR c_rs_group_members_update(l_group_member_id IN NUMBER) is
      SELECT
         DECODE(p_group_id, fnd_api.g_miss_num, group_id, p_group_id) l_group_id,
         DECODE(p_resource_id, fnd_api.g_miss_num, resource_id, p_resource_id) l_resource_id,
         DECODE(p_person_id, fnd_api.g_miss_num, person_id, p_person_id) l_person_id,
         DECODE(p_delete_flag, fnd_api.g_miss_char, delete_flag, p_delete_flag) l_delete_flag,
         DECODE(p_attribute1,fnd_api.g_miss_char, attribute1, p_attribute1) l_attribute1,
         DECODE(p_attribute2,fnd_api.g_miss_char, attribute2, p_attribute2) l_attribute2,
         DECODE(p_attribute3,fnd_api.g_miss_char, attribute3, p_attribute3) l_attribute3,
         DECODE(p_attribute4,fnd_api.g_miss_char, attribute4, p_attribute4) l_attribute4,
         DECODE(p_attribute5,fnd_api.g_miss_char, attribute5, p_attribute5) l_attribute5,
         DECODE(p_attribute6,fnd_api.g_miss_char, attribute6, p_attribute6) l_attribute6,
         DECODE(p_attribute7,fnd_api.g_miss_char, attribute7, p_attribute7) l_attribute7,
         DECODE(p_attribute8,fnd_api.g_miss_char, attribute8, p_attribute8) l_attribute8,
         DECODE(p_attribute9,fnd_api.g_miss_char, attribute9, p_attribute9) l_attribute9,
         DECODE(p_attribute10,fnd_api.g_miss_char, attribute10, p_attribute10) l_attribute10,
         DECODE(p_attribute11,fnd_api.g_miss_char, attribute11, p_attribute11) l_attribute11,
         DECODE(p_attribute12,fnd_api.g_miss_char, attribute12, p_attribute12) l_attribute12,
         DECODE(p_attribute13,fnd_api.g_miss_char, attribute13, p_attribute13) l_attribute13,
         DECODE(p_attribute14,fnd_api.g_miss_char, attribute14, p_attribute14) l_attribute14,
         DECODE(p_attribute15,fnd_api.g_miss_char, attribute15, p_attribute15) l_attribute15,
         DECODE(p_attribute_category,fnd_api.g_miss_char, attribute1, p_attribute_category) l_attribute_category
      FROM jtf_rs_group_members
      WHERE group_member_id = l_group_member_id;

Line: 808

    rs_group_member_rec c_rs_group_members_update%ROWTYPE;

Line: 810

      SAVEPOINT update_rs_group_members_pvt;

Line: 825

	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'C')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'C')
    THEN

      jtf_rs_group_member_cuhk.update_group_members_pre(
        p_group_member_id => l_group_member_id,
	   x_return_status => x_return_status);

Line: 861

	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'V')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'V')
    THEN

      jtf_rs_group_member_vuhk.update_group_members_pre(
        p_group_member_id => l_group_member_id,
	   x_return_status => x_return_status);

Line: 897

	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'I')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'I')
    THEN

      jtf_rs_group_member_iuhk.update_group_members_pre(
        p_group_member_id => l_group_member_id,
	   x_return_status => x_return_status);

Line: 927

      OPEN c_rs_group_members_update(l_group_member_id);

Line: 928

      FETCH c_rs_group_members_update INTO rs_group_member_rec;

Line: 929

      IF c_rs_group_members_update%NOTFOUND THEN
         CLOSE c_rs_group_members_update;

Line: 947

    jtf_rs_group_members_aud_pvt.update_member
    (
     P_API_VERSION => 1,
     P_INIT_MSG_LIST => fnd_api.g_false,
     P_COMMIT => fnd_api.g_false,
     P_GROUP_MEMBER_ID => l_group_member_id,
     P_GROUP_ID   => rs_group_member_rec.l_group_id,
     P_RESOURCE_ID => rs_group_member_rec.l_resource_id,
     P_PERSON_ID => rs_group_member_rec.l_person_id,
     P_OBJECT_VERSION_NUMBER => l_object_version_number,
     X_RETURN_STATUS => x_return_status,
     X_MSG_COUNT => x_msg_count,
     X_MSG_DATA => x_msg_data
    );

Line: 976

      jtf_rs_group_members_pkg.update_row (
         X_GROUP_MEMBER_ID              => l_group_member_id,
         X_ATTRIBUTE1           => rs_group_member_rec.l_attribute1,
         X_ATTRIBUTE2           => rs_group_member_rec.l_attribute2,
         X_ATTRIBUTE3           => rs_group_member_rec.l_attribute3,
         X_ATTRIBUTE4           => rs_group_member_rec.l_attribute4,
         X_ATTRIBUTE5           => rs_group_member_rec.l_attribute5,
         X_ATTRIBUTE6           => rs_group_member_rec.l_attribute6,
         X_ATTRIBUTE7           => rs_group_member_rec.l_attribute7,
         X_ATTRIBUTE8           => rs_group_member_rec.l_attribute8,
         X_ATTRIBUTE9           => rs_group_member_rec.l_attribute9,
         X_ATTRIBUTE10          => rs_group_member_rec.l_attribute10,
         X_ATTRIBUTE11          => rs_group_member_rec.l_attribute11,
         X_ATTRIBUTE12          => rs_group_member_rec.l_attribute12,
         X_ATTRIBUTE13          => rs_group_member_rec.l_attribute13,
         X_ATTRIBUTE14          => rs_group_member_rec.l_attribute14,
         X_ATTRIBUTE15          => rs_group_member_rec.l_attribute15,
         X_ATTRIBUTE_CATEGORY   => rs_group_member_rec.l_attribute_category,
         X_GROUP_ID            => rs_group_member_rec.l_group_id,
         X_RESOURCE_ID       => rs_group_member_rec.l_resource_id,
         X_PERSON_ID          => rs_group_member_rec.l_person_id,
         X_DELETE_FLAG        => rs_group_member_rec.l_delete_flag,
         X_OBJECT_VERSION_NUMBER=> l_object_version_number,
         X_LAST_UPDATE_DATE     => sysdate,
         X_LAST_UPDATED_BY	=> jtf_resource_utl.updated_by,
         X_LAST_UPDATE_LOGIN	=> jtf_resource_utl.login_id
      );

Line: 1008

         CLOSE c_rs_group_members_update;

Line: 1021

	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'C')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'C')
    THEN

      jtf_rs_group_member_cuhk.update_group_members_post(
        p_group_member_id => l_group_member_id,
	   x_return_status => x_return_status);

Line: 1057

	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'V')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'V')
    THEN

      jtf_rs_group_member_vuhk.update_group_members_post(
        p_group_member_id => l_group_member_id,
	   x_return_status => x_return_status);

Line: 1093

	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'I')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'I')
    THEN

      jtf_rs_group_member_iuhk.update_group_members_post(
        p_group_member_id => l_group_member_id,
	   x_return_status => x_return_status);

Line: 1127

	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'M',
	 'M')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'UPDATE_RESOURCE_GROUP_MEMBERS',
	 'M',
	 'M')
    THEN

      IF (jtf_rs_group_member_cuhk.ok_to_generate_msg(
	       p_group_member_id => l_group_member_id,
	       x_return_status => x_return_status) )
      THEN

        /* Get the bind data id for the Business Object Instance */

        l_bind_data_id := jtf_usr_hks.get_bind_data_id;

Line: 1194

         ROLLBACK TO update_rs_group_members_pvt;

Line: 1201

         ROLLBACK TO update_rs_group_members_pvt;

Line: 1213

  /* Procedure to delete the resource group members. */

  PROCEDURE  delete_resource_group_members
  (P_API_VERSION          IN   NUMBER,
   P_INIT_MSG_LIST        IN   VARCHAR2,
   P_COMMIT               IN   VARCHAR2,
   P_GROUP_ID             IN   JTF_RS_GROUP_MEMBERS.GROUP_ID%TYPE,
   P_RESOURCE_ID          IN   JTF_RS_GROUP_MEMBERS.RESOURCE_ID%TYPE,
   P_OBJECT_VERSION_NUM   IN   JTF_RS_GROUP_MEMBERS.OBJECT_VERSION_NUMBER%TYPE,
   X_RETURN_STATUS        OUT NOCOPY  VARCHAR2,
   X_MSG_COUNT            OUT NOCOPY  NUMBER,
   X_MSG_DATA             OUT NOCOPY VARCHAR2
  )

  IS

    l_api_version         CONSTANT NUMBER := 1.0;

Line: 1230

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

Line: 1247

      SELECT group_member_id
      FROM jtf_rs_group_members
      WHERE group_id = l_group_id
	   AND resource_id = l_resource_id
	   AND nvl(delete_flag,'N') <> 'Y';

Line: 1257

	 SELECT count(*)
	 FROM jtf_rs_role_relations
	 WHERE role_resource_type = 'RS_GROUP_MEMBER'
	   AND role_resource_id = l_group_member_id
	   AND nvl(delete_flag,'N') <> 'Y';

Line: 1273

    SAVEPOINT delete_resource_member_pvt;

Line: 1300

	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'C')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'C')
    THEN

      jtf_rs_group_member_cuhk.delete_group_members_pre(
        p_group_id => l_group_id,
        p_resource_id => l_resource_id,
	   x_return_status => x_return_status);

Line: 1341

	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'V')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'V')
    THEN

      jtf_rs_group_member_vuhk.delete_group_members_pre(
        p_group_id => l_group_id,
        p_resource_id => l_resource_id,
	   x_return_status => x_return_status);

Line: 1381

	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'I')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'B',
	 'I')
    THEN

      jtf_rs_group_member_iuhk.delete_group_members_pre(
        p_group_id => l_group_id,
        p_resource_id => l_resource_id,
	   x_return_status => x_return_status);

Line: 1528

    jtf_rs_group_members_aud_pvt.delete_member
    (P_API_VERSION => 1,
     P_INIT_MSG_LIST => fnd_api.g_false,
     P_COMMIT => fnd_api.g_false,
     P_GROUP_MEMBER_ID => l_group_member_id,
     X_RETURN_STATUS => x_return_status,
     X_MSG_COUNT => x_msg_count,
     X_MSG_DATA => x_msg_data
    );

Line: 1554

    /* Call the private procedure for logical delete */

    BEGIN

      -- delete wf_user_role record for the new group member
      -- Don't care for its success status
      BEGIN
	jtf_rs_wf_integration_pub.delete_resource_group_members
	  (P_API_VERSION      => 1.0,
	   P_RESOURCE_ID      =>  l_resource_id,
	   P_GROUP_ID   => l_group_id,
	   X_RETURN_STATUS   => l_return_status,
	   X_MSG_COUNT      => l_msg_count,
	   X_MSG_DATA      => l_msg_data);

Line: 1573

      /* Delete the row into the table by calling the table handler. */

      jtf_rs_group_members_pkg.logical_delete_row(
        x_group_member_id => l_group_member_id
      );

Line: 1581

	jtf_rs_active_grp_mbrs_pkg.delete_row(
	  x_group_member_id => l_group_member_id
	);

Line: 1609

	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'C')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'C')
    THEN

      jtf_rs_group_member_cuhk.delete_group_members_post(
        p_group_id => l_group_id,
        p_resource_id => l_resource_id,
	   x_return_status => x_return_status);

Line: 1650

	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'V')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'V')
    THEN

      jtf_rs_group_member_vuhk.delete_group_members_post(
        p_group_id => l_group_id,
        p_resource_id => l_resource_id,
	   x_return_status => x_return_status);

Line: 1690

	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'I')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'A',
	 'I')
    THEN

      jtf_rs_group_member_iuhk.delete_group_members_post(
        p_group_id => l_group_id,
        p_resource_id => l_resource_id,
	   x_return_status => x_return_status);

Line: 1728

	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'M',
	 'M')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_GROUP_MEMBERS_PVT',
	 'DELETE_RESOURCE_GROUP_MEMBERS',
	 'M',
	 'M')
    THEN

      IF (jtf_rs_group_member_cuhk.ok_to_generate_msg(
	       p_group_member_id => l_group_member_id,
	       x_return_status => x_return_status) )
      THEN

        /* Get the bind data id for the Business Object Instance */

        l_bind_data_id := jtf_usr_hks.get_bind_data_id;

Line: 1798

      ROLLBACK TO delete_resource_member_pvt;

Line: 1803

      ROLLBACK TO delete_resource_member_pvt;

Line: 1808

      ROLLBACK TO delete_resource_member_pvt;

Line: 1818

  END delete_resource_group_members;