APPS.JTF_RS_TEAM_MEMBERS_PVT SQL Statements

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

Line: 6

   like create and delete resource team members.
   Its main procedures are as following:
   Create Resource Team Members
   Delete Resource Team 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         CONSTANT VARCHAR2(30) := 'JTF_RS_TEAM_MEMBERS_PVT';

Line: 71

	 SELECT 'Y'
	 FROM jtf_rs_team_members
	 WHERE ROWID = l_rowid;

Line: 77

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

Line: 229

    SELECT count(*)
    INTO l_check_count
    FROM jtf_rs_team_members
    WHERE team_id = l_team_id
	 AND team_resource_id = l_team_resource_id
	 AND resource_type = l_resource_type
	 AND nvl(delete_flag,'N') <> 'Y';

Line: 273

    SELECT jtf_rs_team_members_s.nextval
    INTO l_team_member_id
    FROM dual;

Line: 278

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

    jtf_rs_team_members_pkg.insert_row(
      x_rowid => l_rowid,
      x_team_member_id => l_team_member_id,
      x_team_id => l_team_id,
      x_team_resource_id => l_team_resource_id,
	 x_resource_type => l_resource_type,
      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: 603

  /* Procedure to update the resource team members
	based on input values passed by calling routines. */

  PROCEDURE  update_resource_team_members
  (P_API_VERSION          IN   NUMBER,
   P_INIT_MSG_LIST        IN   VARCHAR2,
   P_COMMIT               IN   VARCHAR2,
   P_TEAM_MEMBER_ID       IN   JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE,
   P_TEAM_ID              IN   JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
   P_TEAM_RESOURCE_ID     IN   JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
   P_PERSON_ID		  IN   JTF_RS_TEAM_MEMBERS.PERSON_ID%TYPE,
   P_RESOURCE_TYPE        IN   JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
   P_DELETE_FLAG          IN   JTF_RS_TEAM_MEMBERS.DELETE_FLAG%TYPE,
   P_ATTRIBUTE1           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE1%TYPE,
   P_ATTRIBUTE2           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE2%TYPE,
   P_ATTRIBUTE3           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE3%TYPE,
   P_ATTRIBUTE4           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE4%TYPE,
   P_ATTRIBUTE5           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE5%TYPE,
   P_ATTRIBUTE6           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE6%TYPE,
   P_ATTRIBUTE7           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE7%TYPE,
   P_ATTRIBUTE8           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE8%TYPE,
   P_ATTRIBUTE9           IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE9%TYPE,
   P_ATTRIBUTE10          IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE10%TYPE,
   P_ATTRIBUTE11          IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE11%TYPE,
   P_ATTRIBUTE12          IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE12%TYPE,
   P_ATTRIBUTE13          IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE13%TYPE,
   P_ATTRIBUTE14          IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE14%TYPE,
   P_ATTRIBUTE15          IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE15%TYPE,
   P_ATTRIBUTE_CATEGORY   IN   JTF_RS_TEAM_MEMBERS.ATTRIBUTE_CATEGORY%TYPE,
   P_OBJECT_VERSION_NUMBER IN OUT NOCOPY JTF_RS_TEAM_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: 639

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

Line: 645

    l_delete_flag		   jtf_rs_team_members.delete_flag%TYPE;

Line: 668

    CURSOR c_rs_team_members_update(l_team_member_id IN NUMBER) IS
    SELECT DECODE(p_team_id, fnd_api.g_miss_num, team_id, p_team_id) l_team_id,
	DECODE(p_team_resource_id, fnd_api.g_miss_num, team_resource_id, p_team_resource_id) l_team_resource_id,
	DECODE(p_person_id, fnd_api.g_miss_num, person_id, p_person_id) l_person_id,
	DECODE(p_resource_type, fnd_api.g_miss_char, resource_type, p_resource_type) l_resource_type,
	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, attribute_category, p_attribute_category) l_attribute_category
    FROM jtf_rs_team_members
    WHERE team_member_id = l_team_member_id ;

Line: 693

    rs_team_member_rec		c_rs_team_members_update%ROWTYPE ;

Line: 702

    l_delete_flag                := p_delete_flag ;

Line: 721

    SAVEPOINT update_resource_member_pvt;

Line: 738

	 'UPDATE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'C')
    THEN
	    IF jtf_usr_hks.ok_to_execute(
		 'JTF_RS_TEAM_MEMBERS_PVT',
		 'UPDATE_RESOURCE_TEAM_MEMBERS',
		 'B',
		 'C')
	    THEN
	      jtf_rs_team_member_cuhk.update_team_members_pre(
		p_team_member_id => l_team_member_id,
		   x_return_status => x_return_status);

Line: 766

	 'UPDATE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'V')
    THEN
	    IF jtf_usr_hks.ok_to_execute(
		 'JTF_RS_TEAM_MEMBERS_PVT',
		 'UPDATE_RESOURCE_TEAM_MEMBERS',
		 'B',
		 'V')
	    THEN
	      jtf_rs_team_member_vuhk.update_team_members_pre(
		p_team_member_id => l_team_member_id,
		   x_return_status => x_return_status);

Line: 794

	 'UPDATE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'I')
    THEN
	    IF jtf_usr_hks.ok_to_execute(
		 'JTF_RS_TEAM_MEMBERS_PVT',
		 'UPDATE_RESOURCE_TEAM_MEMBERS',
		 'B',
		 'I')
	    THEN
	      jtf_rs_team_member_iuhk.update_team_members_pre(
		p_team_member_id => l_team_member_id,
		   x_return_status => x_return_status);

Line: 816

    /* Validate the team member for update. */

    OPEN c_rs_team_members_update(l_team_member_id) ;

Line: 819

    FETCH c_rs_team_members_update
    INTO  rs_team_member_rec ;

Line: 821

    IF c_rs_team_members_update%NOTFOUND THEN
	CLOSE c_rs_team_members_update ;

Line: 845

    /* Update the Object Version Number by Incrementing It */
    l_object_version_number := l_object_version_number + 1 ;

Line: 848

    /* Update the row into the table by calling the table handler. */
    BEGIN
	    jtf_rs_team_members_pkg.update_row(
	      x_team_member_id => l_team_member_id,
	      x_team_id => rs_team_member_rec.l_team_id,
	      x_team_resource_id => rs_team_member_rec.l_team_resource_id,
	      x_resource_type => rs_team_member_rec.l_resource_type,
	      x_person_id => rs_team_member_rec.l_person_id,
	      x_delete_flag => rs_team_member_rec.l_delete_flag,
	      x_object_version_number =>l_object_version_number,
	      x_attribute1 => rs_team_member_rec.l_attribute1,
	      x_attribute2 => rs_team_member_rec.l_attribute2,
	      x_attribute3 => rs_team_member_rec.l_attribute3,
	      x_attribute4 => rs_team_member_rec.l_attribute4,
	      x_attribute5 => rs_team_member_rec.l_attribute5,
	      x_attribute6 => rs_team_member_rec.l_attribute6,
	      x_attribute7 => rs_team_member_rec.l_attribute7,
	      x_attribute8 => rs_team_member_rec.l_attribute8,
	      x_attribute9 => rs_team_member_rec.l_attribute9,
	      x_attribute10 => rs_team_member_rec.l_attribute10,
	      x_attribute11 => rs_team_member_rec.l_attribute11,
	      x_attribute12 => rs_team_member_rec.l_attribute12,
	      x_attribute13 => rs_team_member_rec.l_attribute13,
	      x_attribute14 => rs_team_member_rec.l_attribute14,
	      x_attribute15 => rs_team_member_rec.l_attribute15,
	      x_attribute_category => rs_team_member_rec.l_attribute_category,
	      x_last_update_date => SYSDATE,
	      x_last_updated_by => jtf_resource_utl.updated_by,
	      x_last_update_login => jtf_resource_utl.login_id
	    );

Line: 880

	    --  dbms_output.put_line('Updated Row');

Line: 884

			CLOSE c_rs_team_members_update ;

Line: 891

    IF c_rs_team_members_update%ISOPEN THEN
       CLOSE c_rs_team_members_update;

Line: 901

	 'UPDATE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'C')
    THEN
	    IF jtf_usr_hks.ok_to_execute(
		 'JTF_RS_TEAM_MEMBERS_PVT',
		 'UPDATE_RESOURCE_TEAM_MEMBERS',
		 'A',
		 'C')
	    THEN
	      jtf_rs_team_member_cuhk.update_team_members_post(
		  p_team_member_id => l_team_member_id,
		   x_return_status => x_return_status);

Line: 928

	 'UPDATE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'V')
    THEN
	    IF jtf_usr_hks.ok_to_execute(
		 'JTF_RS_TEAM_MEMBERS_PVT',
		 'UPDATE_RESOURCE_TEAM_MEMBERS',
		 'A',
		 'V')
	    THEN
	      jtf_rs_team_member_vuhk.update_team_members_post(
		p_team_member_id => l_team_member_id,
		   x_return_status => x_return_status);

Line: 956

	 'UPDATE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'I')
    THEN
	    IF jtf_usr_hks.ok_to_execute(
		 'JTF_RS_TEAM_MEMBERS_PVT',
		 'UPDATE_RESOURCE_TEAM_MEMBERS',
		 'A',
		 'I')
	    THEN
	      jtf_rs_team_member_iuhk.update_team_members_post(
		p_team_member_id => l_team_member_id,
		   x_return_status => x_return_status);

Line: 983

	 'UPDATE_RESOURCE_TEAM_MEMBERS',
	 'M',
	 'M')
    THEN
	    IF jtf_usr_hks.ok_to_execute(
		 'JTF_RS_TEAM_MEMBERS_PVT',
		 'UPDATE_RESOURCE_TEAM_MEMBERS',
		 'M',
		 'M')
	    THEN
	      IF (jtf_rs_team_member_cuhk.ok_to_generate_msg(
		       p_team_member_id => l_team_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: 1036

      ROLLBACK TO update_resource_member_pvt;

Line: 1040

      ROLLBACK TO update_resource_member_pvt;

Line: 1047

  END update_resource_team_members;

Line: 1049

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

  PROCEDURE  delete_resource_team_members
  (P_API_VERSION          IN   NUMBER,
   P_INIT_MSG_LIST        IN   VARCHAR2,
   P_COMMIT               IN   VARCHAR2,
   P_TEAM_ID              IN   JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
   P_TEAM_RESOURCE_ID     IN   JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
   P_RESOURCE_TYPE        IN   JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
   P_OBJECT_VERSION_NUM   IN   JTF_RS_TEAM_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: 1065

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

Line: 1083

      SELECT team_member_id
      FROM jtf_rs_team_members
      WHERE team_id = l_team_id
	   AND team_resource_id = l_team_resource_id
	   AND resource_type = l_resource_type
	   AND nvl(delete_flag,'N') <> 'Y';

Line: 1094

	 SELECT count(*)
	 FROM jtf_rs_role_relations
	 WHERE role_resource_type = 'RS_TEAM_MEMBER'
	   AND role_resource_id = l_team_member_id
	   AND ( end_date_active is null OR end_date_active >= sysdate )
	   AND nvl(delete_flag,'N') <> 'Y';

Line: 1109

    SAVEPOINT delete_resource_member_pvt;

Line: 1136

	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'C')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_TEAM_MEMBERS_PVT',
	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'C')
    THEN

      jtf_rs_team_member_cuhk.delete_team_members_pre(
        p_team_id => l_team_id,
        p_team_resource_id => l_team_resource_id,
        p_resource_type => l_resource_type,
	   x_return_status => x_return_status);

Line: 1174

	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'V')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_TEAM_MEMBERS_PVT',
	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'V')
    THEN

      jtf_rs_team_member_vuhk.delete_team_members_pre(
        p_team_id => l_team_id,
        p_team_resource_id => l_team_resource_id,
        p_resource_type => l_resource_type,
	   x_return_status => x_return_status);

Line: 1212

	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'I')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_TEAM_MEMBERS_PVT',
	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'B',
	 'I')
    THEN

      jtf_rs_team_member_iuhk.delete_team_members_pre(
        p_team_id => l_team_id,
        p_team_resource_id => l_team_resource_id,
        p_resource_type => l_resource_type,
	   x_return_status => x_return_status);

Line: 1359

    /* Call the private procedure for logical delete */

    BEGIN

      -- delete wf_user_role record for the new team member
      -- Don't care for its success status
      BEGIN
	IF l_resource_type = 'INDIVIDUAL' THEN
	  l_resource_id := l_team_resource_id;

Line: 1374

        jtf_rs_wf_integration_pub.delete_resource_team_members
          (P_API_VERSION      => 1.0,
           P_RESOURCE_ID      =>  l_resource_id,
           P_GROUP_ID      =>  l_group_id,
           P_TEAM_ID   => l_team_id,
           X_RETURN_STATUS   => l_return_status,
           X_MSG_COUNT      => l_msg_count,
           X_MSG_DATA      => l_msg_data);

Line: 1387

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

      jtf_rs_team_members_pkg.logical_delete_row(
        x_team_member_id => l_team_member_id
      );

Line: 1415

	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'C')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_TEAM_MEMBERS_PVT',
	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'C')
    THEN

      jtf_rs_team_member_cuhk.delete_team_members_post(
        p_team_id => l_team_id,
        p_team_resource_id => l_team_resource_id,
        p_resource_type => l_resource_type,
	   x_return_status => x_return_status);

Line: 1453

	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'V')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_TEAM_MEMBERS_PVT',
	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'V')
    THEN

      jtf_rs_team_member_vuhk.delete_team_members_post(
        p_team_id => l_team_id,
        p_team_resource_id => l_team_resource_id,
        p_resource_type => l_resource_type,
	   x_return_status => x_return_status);

Line: 1491

	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'I')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_TEAM_MEMBERS_PVT',
	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'A',
	 'I')
    THEN

      jtf_rs_team_member_iuhk.delete_team_members_post(
        p_team_id => l_team_id,
        p_team_resource_id => l_team_resource_id,
        p_resource_type => l_resource_type,
	   x_return_status => x_return_status);

Line: 1529

	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'M',
	 'M')
    THEN
    IF jtf_usr_hks.ok_to_execute(
	 'JTF_RS_TEAM_MEMBERS_PVT',
	 'DELETE_RESOURCE_TEAM_MEMBERS',
	 'M',
	 'M')
    THEN

      IF (jtf_rs_team_member_cuhk.ok_to_generate_msg(
	       p_team_member_id => l_team_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: 1602

      ROLLBACK TO delete_resource_member_pvt;

Line: 1617

      ROLLBACK TO delete_resource_member_pvt;

Line: 1624

  END delete_resource_team_members;