APPS.JTF_RS_RESOURCE_SKILLS_PUB SQL Statements

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

Line: 7

   create, update and delete rows
   Its main procedures are as following:
   Create resource_skills
   Update resource_skills
   Delete resource_skills
   Calls to these procedures will call procedures of jtf_rs_resource_skills_pvt
   to do inserts, updates and deletes into tables.

   Modification history

   Date		Name		Description
   02-DEC-02   asachan	 	Added two overloaded procedures create_resource_skills and
				update_resource_skills for providing product skill
				cascading capability. Also added two package body level
				procedure update_existing_comp_skills and
				create_unrated_comp_skills(bug#2002193)
   ******************************************************************************************/

 /* Package variables. */

  G_PKG_NAME         VARCHAR2(30) := 'JTF_RS_RESOURCE_SKILLS_PUB';

Line: 36

/* Package body level procedure to update existing component level skill ratings
    for a given resource. introduced as part of bug#2002193 */

 PROCEDURE UPDATE_EXISTING_COMP_SKILLS
 (  P_API_VERSION          IN   NUMBER,
    P_INIT_MSG_LIST        IN   VARCHAR2,
    P_COMMIT               IN   VARCHAR2,
    P_RESOURCE_ID          IN   JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
    P_SKILL_LEVEL_ID       IN   JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
    P_CATEGORY_ID          IN   JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
    P_SUBCATEGORY          IN   JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
    P_PRODUCT_ID           IN   JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
    P_PRODUCT_ORG_ID       IN   JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
    X_RETURN_STATUS        OUT  NOCOPY VARCHAR2,
    X_MSG_COUNT            OUT  NOCOPY NUMBER,
    X_MSG_DATA             OUT  NOCOPY VARCHAR2
 )
 IS
 	-- Cursor for retrieving existing component level skills
	    CURSOR  component_skills_curr( p_product_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
	      		   	  p_product_org_id JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
	  			  p_resource_id JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE)
	    IS
	    SELECT
	      RESOURCE_SKILL_ID,
	      RESOURCE_ID,
	      SKILL_LEVEL_ID,
	      CATEGORY_ID,
	      SUBCATEGORY,
	      PRODUCT_ID,
	      PRODUCT_ORG_ID,
	      PLATFORM_ID,
	      PLATFORM_ORG_ID,
	      PROBLEM_CODE,
	      COMPONENT_ID,
	      SUBCOMPONENT_ID,
	      OBJECT_VERSION_NUMBER,
	      ATTRIBUTE1,
	      ATTRIBUTE2,
	      ATTRIBUTE3,
	      ATTRIBUTE4,
	      ATTRIBUTE5,
	      ATTRIBUTE6,
	      ATTRIBUTE7,
	      ATTRIBUTE8,
	      ATTRIBUTE9,
	      ATTRIBUTE10,
	      ATTRIBUTE11,
	      ATTRIBUTE12,
	      ATTRIBUTE13,
	      ATTRIBUTE14,
	      ATTRIBUTE15,
	      ATTRIBUTE_CATEGORY
	    FROM jtf_rs_resource_skills
	    WHERE   resource_id = p_resource_id
	    AND     product_id = p_product_id
	    AND     product_org_id = p_product_org_id
    	    AND     component_id IS NOT null;

Line: 107

 		-- Update skill with new skill level

        	UPDATE_RESOURCE_SKILLS(
			P_API_VERSION            => p_api_version,
			P_INIT_MSG_LIST          => p_init_msg_list,
			P_COMMIT                 => p_commit,
			P_RESOURCE_SKILL_ID      => component_skills_curr_rec.resource_skill_id,
			P_RESOURCE_ID            => component_skills_curr_rec.resource_id,
			P_SKILL_LEVEL_ID         => p_skill_level_id,
			P_CATEGORY_ID            => component_skills_curr_rec.category_id,
			P_SUBCATEGORY            => component_skills_curr_rec.subcategory,
			P_PRODUCT_ID             => component_skills_curr_rec.product_id,
			P_PRODUCT_ORG_ID         => component_skills_curr_rec.product_org_id,
			P_PLATFORM_ID            => component_skills_curr_rec.platform_id,
			P_PLATFORM_ORG_ID        => component_skills_curr_rec.platform_org_id,
			P_PROBLEM_CODE           => component_skills_curr_rec.problem_code,
			P_COMPONENT_ID           => component_skills_curr_rec.component_id,
			P_SUBCOMPONENT_ID        => component_skills_curr_rec.subcomponent_id,
			P_OBJECT_VERSION_NUM     => l_object_version_number,
			P_ATTRIBUTE1		=> component_skills_curr_rec.attribute1,
			P_ATTRIBUTE2		=> component_skills_curr_rec.attribute2,
			P_ATTRIBUTE3		=> component_skills_curr_rec.attribute3,
			P_ATTRIBUTE4		=> component_skills_curr_rec.attribute4,
			P_ATTRIBUTE5		=> component_skills_curr_rec.attribute5,
			P_ATTRIBUTE6		=> component_skills_curr_rec.attribute6,
			P_ATTRIBUTE7		=> component_skills_curr_rec.attribute7,
			P_ATTRIBUTE8		=> component_skills_curr_rec.attribute8,
			P_ATTRIBUTE9		=> component_skills_curr_rec.attribute9,
			P_ATTRIBUTE10	        => component_skills_curr_rec.attribute10,
			P_ATTRIBUTE11	        => component_skills_curr_rec.attribute11,
			P_ATTRIBUTE12	        => component_skills_curr_rec.attribute12,
			P_ATTRIBUTE13	        => component_skills_curr_rec.attribute13,
			P_ATTRIBUTE14	        => component_skills_curr_rec.attribute14,
			P_ATTRIBUTE15	        => component_skills_curr_rec.attribute15,
			P_ATTRIBUTE_CATEGORY     => component_skills_curr_rec.attribute_category,
			X_RETURN_STATUS          => X_RETURN_STATUS,
			X_MSG_COUNT              => X_MSG_COUNT,
			X_MSG_DATA               => X_MSG_DATA
		       );

Line: 151

 END UPDATE_EXISTING_COMP_SKILLS;

Line: 194

	SELECT component_id
	FROM   jtf_rs_components_v comp
	WHERE  comp.product_id = p_product_id
	AND    comp.product_org_id = p_product_org_id
	AND NOT EXISTS ( SELECT null
	                     FROM   jtf_rs_resource_skills skills
	                     WHERE  skills.resource_id = p_resource_id
	                     AND    skills.product_id = p_product_id
	     		     AND    skills.product_org_id = p_product_org_id
                     	     AND    skills.component_id = comp.component_id);

Line: 258

/* Package body level procedure to update existing product level skill ratings
    for a given resource. Introduced as part of */

 PROCEDURE UPDATE_EXISTING_PROD_SKILLS
 (  P_API_VERSION          IN   NUMBER,
    P_INIT_MSG_LIST        IN   VARCHAR2,
    P_COMMIT               IN   VARCHAR2,
    P_RESOURCE_ID          IN   JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
    P_SKILL_LEVEL_ID       IN   JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
    P_CATEGORY_ID          IN   JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
    X_RETURN_STATUS        OUT  NOCOPY VARCHAR2,
    X_MSG_COUNT            OUT  NOCOPY NUMBER,
    X_MSG_DATA             OUT  NOCOPY VARCHAR2
 )
 IS
        -- Cursor for retrieving existing product level skills
            CURSOR  product_skills_curr( p_category_id JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
                                         p_resource_id JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE)
            IS
            SELECT
              RESOURCE_SKILL_ID,
              RESOURCE_ID,
              SKILL_LEVEL_ID,
              CATEGORY_ID,
              SUBCATEGORY,
              PRODUCT_ID,
              PRODUCT_ORG_ID,
              PLATFORM_ID,
              PLATFORM_ORG_ID,
              PROBLEM_CODE,
              COMPONENT_ID,
              SUBCOMPONENT_ID,
              OBJECT_VERSION_NUMBER,
              ATTRIBUTE1,
              ATTRIBUTE2,
              ATTRIBUTE3,
              ATTRIBUTE4,
              ATTRIBUTE5,
              ATTRIBUTE6,
              ATTRIBUTE7,
              ATTRIBUTE8,
              ATTRIBUTE9,
              ATTRIBUTE10,
              ATTRIBUTE11,
              ATTRIBUTE12,
              ATTRIBUTE13,
              ATTRIBUTE14,
              ATTRIBUTE15,
              ATTRIBUTE_CATEGORY
            FROM jtf_rs_resource_skills a
            WHERE   resource_id = p_resource_id
            AND     ((category_id is null and
		      exists (SELECT null
                              FROM   mtl_item_categories c
                              WHERE  a.product_id = c.inventory_item_id
                              AND    c.organization_id = a.product_org_id
                              AND    c.category_id = p_category_id)) OR
		     category_id = p_category_id)
            AND     product_id IS NOT null
            AND     component_id IS NULL
            AND     problem_code IS NULL;

Line: 334

                UPDATE_RESOURCE_SKILLS(
                        P_API_VERSION            => p_api_version,
                        P_INIT_MSG_LIST          => p_init_msg_list,
                        P_COMMIT                 => p_commit,
                        P_RESOURCE_SKILL_ID      => product_skills_curr_rec.resource_skill_id,
                        P_RESOURCE_ID            => product_skills_curr_rec.resource_id,
                        P_SKILL_LEVEL_ID         => p_skill_level_id,
                        P_CATEGORY_ID            => product_skills_curr_rec.category_id,
                        P_SUBCATEGORY            => product_skills_curr_rec.subcategory,
                        P_PRODUCT_ID             => product_skills_curr_rec.product_id,
                        P_PRODUCT_ORG_ID         => product_skills_curr_rec.product_org_id,
                        P_PLATFORM_ID            => product_skills_curr_rec.platform_id,
                        P_PLATFORM_ORG_ID        => product_skills_curr_rec.platform_org_id,
                        P_PROBLEM_CODE           => product_skills_curr_rec.problem_code,
                        P_COMPONENT_ID           => product_skills_curr_rec.component_id,
                        P_SUBCOMPONENT_ID        => product_skills_curr_rec.subcomponent_id,
                        P_OBJECT_VERSION_NUM     => l_object_version_number,
                        P_ATTRIBUTE1             => product_skills_curr_rec.attribute1,
                        P_ATTRIBUTE2             => product_skills_curr_rec.attribute2,
                        P_ATTRIBUTE3             => product_skills_curr_rec.attribute3,
                        P_ATTRIBUTE4             => product_skills_curr_rec.attribute4,
                        P_ATTRIBUTE5             => product_skills_curr_rec.attribute5,
                        P_ATTRIBUTE6             => product_skills_curr_rec.attribute6,
                        P_ATTRIBUTE7             => product_skills_curr_rec.attribute7,
                        P_ATTRIBUTE8             => product_skills_curr_rec.attribute8,
                        P_ATTRIBUTE9             => product_skills_curr_rec.attribute9,
                        P_ATTRIBUTE10            => product_skills_curr_rec.attribute10,
                        P_ATTRIBUTE11            => product_skills_curr_rec.attribute11,
                        P_ATTRIBUTE12            => product_skills_curr_rec.attribute12,
                        P_ATTRIBUTE13            => product_skills_curr_rec.attribute13,
                        P_ATTRIBUTE14            => product_skills_curr_rec.attribute14,
                        P_ATTRIBUTE15            => product_skills_curr_rec.attribute15,
                        P_ATTRIBUTE_CATEGORY     => product_skills_curr_rec.attribute_category,
                        X_RETURN_STATUS          => X_RETURN_STATUS,
                        X_MSG_COUNT              => X_MSG_COUNT,
                        X_MSG_DATA               => X_MSG_DATA
                       );

Line: 377

 END UPDATE_EXISTING_PROD_SKILLS;

Line: 419

        SELECT a.product_id
        FROM   jtf_rs_products_v a
        WHERE  a.product_org_id = p_product_org_id
        AND    nvl(a.enabled_flag, 'Y') <> 'N'
        AND    EXISTS(SELECT null
                      FROM   mtl_item_categories c
                      WHERE  a.product_id = c.inventory_item_id
                      AND    c.organization_id = p_product_org_id
                      AND    c.category_id = p_category_id)
        AND    NOT EXISTS (SELECT null
                           FROM   jtf_rs_resource_skills skills
                           WHERE  skills.resource_id = p_resource_id
--                           AND    skills.category_id = p_category_id
-- user cannot create same skill twice, once at product level and second time at category->product level.  Bug # 2171572.
                           AND    skills.product_id = a.product_id
                           AND    skills.product_org_id = p_product_org_id);

Line: 645

  /* Procedure to update resource skills
	based on input values passed by calling routines. */

  PROCEDURE  update_resource_skills
  (P_API_VERSION          IN   NUMBER,
   P_INIT_MSG_LIST        IN   VARCHAR2,
   P_COMMIT               IN   VARCHAR2,
   P_RESOURCE_SKILL_ID    IN   JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE,
   P_RESOURCE_ID          IN   JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
   P_SKILL_LEVEL_ID       IN   JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
   P_CATEGORY_ID          IN   JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
   P_SUBCATEGORY          IN   JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
   P_PRODUCT_ID           IN   JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
   P_PRODUCT_ORG_ID       IN   JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
   P_PLATFORM_ID          IN   JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
   P_PLATFORM_ORG_ID      IN   JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
   P_PROBLEM_CODE         IN   JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
   P_COMPONENT_ID         IN   JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
   P_SUBCOMPONENT_ID      IN   JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE,
   P_OBJECT_VERSION_NUM   IN OUT NOCOPY  JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE,
   P_ATTRIBUTE1		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
   P_ATTRIBUTE2		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
   P_ATTRIBUTE3		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
   P_ATTRIBUTE4		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
   P_ATTRIBUTE5		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
   P_ATTRIBUTE6		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
   P_ATTRIBUTE7		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
   P_ATTRIBUTE8		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
   P_ATTRIBUTE9		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
   P_ATTRIBUTE10	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
   P_ATTRIBUTE11	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
   P_ATTRIBUTE12	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
   P_ATTRIBUTE13	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
   P_ATTRIBUTE14	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
   P_ATTRIBUTE15	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
   P_ATTRIBUTE_CATEGORY	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
   X_RETURN_STATUS       OUT NOCOPY     VARCHAR2,
   X_MSG_COUNT           OUT NOCOPY     NUMBER,
   X_MSG_DATA            OUT NOCOPY     VARCHAR2
  )IS
  l_api_name    CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_SKILLS';

Line: 701

     SAVEPOINT UPDATE_RESOURCE_SKILLS_SP;

Line: 717

		 JTF_RS_RESOURCE_SKILLS_PVT.UPDATE_RESOURCE_SKILLS(
                               P_API_VERSION            => l_api_version,
                               P_INIT_MSG_LIST          => p_init_msg_list,
                               P_COMMIT                 => p_commit,
                               P_RESOURCE_SKILL_ID      => p_resource_skill_id,
                               P_RESOURCE_ID            => p_resource_id,
                               P_SKILL_LEVEL_ID         => p_SKILL_LEVEL_ID,
                               P_CATEGORY_ID            => p_category_id,
                               P_SUBCATEGORY            => p_subcategory,
                               P_PRODUCT_ID             => p_product_id,
                               P_PRODUCT_ORG_ID         => p_product_org_id,
                               P_PLATFORM_ID            => p_platform_id,
                               P_PLATFORM_ORG_ID        => p_platform_org_id,
                               P_PROBLEM_CODE           => p_problem_code,
                               P_COMPONENT_ID           => p_component_id,
                               P_SUBCOMPONENT_ID        => p_subcomponent_id,
                               P_OBJECT_VERSION_NUM     => l_object_version_number,
                               P_ATTRIBUTE1		=> p_attribute1,
                               P_ATTRIBUTE2		=> P_attribute2,
                               P_ATTRIBUTE3		=> p_attribute3,
                               P_ATTRIBUTE4		=> p_attribute4,
                               P_ATTRIBUTE5		=> p_attribute5,
                               P_ATTRIBUTE6		=> p_attribute6,
                               P_ATTRIBUTE7		=> p_attribute7,
                               P_ATTRIBUTE8		=> p_attribute8,
                               P_ATTRIBUTE9		=> p_attribute9,
                               P_ATTRIBUTE10	        => p_attribute10,
                               P_ATTRIBUTE11	        => p_attribute11,
                               P_ATTRIBUTE12	        => p_attribute12,
                               P_ATTRIBUTE13	        => p_attribute13,
                               P_ATTRIBUTE14	        => p_attribute14,
                               P_ATTRIBUTE15	        => p_attribute15,
                               P_ATTRIBUTE_CATEGORY     => p_attribute_category,
                               X_RETURN_STATUS          => l_return_status,
                               X_MSG_COUNT              => l_msg_count,
                               X_MSG_DATA               => l_msg_data
                              );

Line: 777

      ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;

Line: 783

      ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;

Line: 788

      ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;

Line: 796

   END  update_resource_skills;

Line: 799

  /* Procedure to delete the resource skills */

  PROCEDURE  delete_resource_skills
  (P_API_VERSION          IN     NUMBER,
   P_INIT_MSG_LIST        IN     VARCHAR2,
   P_COMMIT               IN     VARCHAR2,
   P_RESOURCE_SKILL_ID    IN     JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE,
   P_OBJECT_VERSION_NUM   IN     JTF_RS_RESOURCE_SKILLS.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_name    CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_SKILLS';

Line: 827

     SAVEPOINT DELETE_RESOURCE_SKILLS_SP;

Line: 843

                    JTF_RS_RESOURCE_SKILLS_PVT.DELETE_RESOURCE_SKILLS
                           (P_API_VERSION         => l_api_version,
                            P_INIT_MSG_LIST       => p_init_msg_list,
                            P_COMMIT              => p_commit,
                            P_RESOURCE_SKILL_ID   => p_resource_skill_id,
                            P_OBJECT_VERSION_NUM  => p_object_version_num,
                            X_RETURN_STATUS       => l_return_status,
                            X_MSG_COUNT           => l_msg_count,
                            X_MSG_DATA            => l_msg_data
                           );

Line: 874

      ROLLBACK TO DELETE_RESOURCE_SKILLS_SP;

Line: 879

      ROLLBACK TO DELETE_RESOURCE_SKILLS_SP;

Line: 884

      ROLLBACK TO DELETE_RESOURCE_SKILLS_SP;

Line: 893

 END delete_resource_skills;

Line: 1058

         UPDATE_EXISTING_PROD_SKILLS
                   (
                     P_API_VERSION              => l_api_version,
                     P_INIT_MSG_LIST            => l_init_msg_list,
                     P_COMMIT                   => l_commit,
                     P_RESOURCE_ID              => p_resource_id,
                     P_SKILL_LEVEL_ID           => p_skill_level_id,
                     P_CATEGORY_ID              => p_category_id,
                     X_RETURN_STATUS            => X_RETURN_STATUS,
                     X_MSG_COUNT                => X_MSG_COUNT,
                     X_MSG_DATA                 => X_MSG_DATA
                   );

Line: 1076

         UPDATE_EXISTING_COMP_SKILLS
	           (
                     P_API_VERSION         	=> l_api_version,
                     P_INIT_MSG_LIST       	=> l_init_msg_list,
                     P_COMMIT              	=> l_commit,
                     P_RESOURCE_ID         	=> p_resource_id,
                     P_SKILL_LEVEL_ID      	=> p_skill_level_id,
                     P_CATEGORY_ID         	=> p_category_id,
                     P_SUBCATEGORY         	=> p_subcategory,
                     P_PRODUCT_ID          	=> p_product_id,
                     P_PRODUCT_ORG_ID      	=> l_product_org_id,
                     X_RETURN_STATUS       	=> X_RETURN_STATUS,
                     X_MSG_COUNT           	=> X_MSG_COUNT,
                     X_MSG_DATA            	=> X_MSG_DATA
                   );

Line: 1264

   /* Procedure to update skill rating with cascading.
   introduced as part of bug#2002193 */

  PROCEDURE  update_resource_skills
  (P_API_VERSION          IN   NUMBER,
   P_INIT_MSG_LIST        IN   VARCHAR2,
   P_COMMIT               IN   VARCHAR2,
   P_RESOURCE_SKILL_ID    IN   JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE,
   P_RESOURCE_ID          IN   JTF_RS_RESOURCE_SKILLS.RESOURCE_ID%TYPE,
   P_SKILL_LEVEL_ID       IN   JTF_RS_RESOURCE_SKILLS.SKILL_LEVEL_ID%TYPE,
   P_CATEGORY_ID          IN   JTF_RS_RESOURCE_SKILLS.CATEGORY_ID%TYPE,
   P_SUBCATEGORY          IN   JTF_RS_RESOURCE_SKILLS.SUBCATEGORY%TYPE,
   P_PRODUCT_ID           IN   JTF_RS_RESOURCE_SKILLS.PRODUCT_ID%TYPE,
   P_PRODUCT_ORG_ID       IN   JTF_RS_RESOURCE_SKILLS.PRODUCT_ORG_ID%TYPE,
   P_PLATFORM_ID          IN   JTF_RS_RESOURCE_SKILLS.PLATFORM_ID%TYPE,
   P_PLATFORM_ORG_ID      IN   JTF_RS_RESOURCE_SKILLS.PLATFORM_ORG_ID%TYPE,
   P_PROBLEM_CODE         IN   JTF_RS_RESOURCE_SKILLS.PROBLEM_CODE%TYPE,
   P_COMPONENT_ID         IN   JTF_RS_RESOURCE_SKILLS.COMPONENT_ID%TYPE,
   P_SUBCOMPONENT_ID      IN   JTF_RS_RESOURCE_SKILLS.SUBCOMPONENT_ID%TYPE,
   P_OBJECT_VERSION_NUM   IN OUT NOCOPY  JTF_RS_RESOURCE_SKILLS.OBJECT_VERSION_NUMBER%TYPE,
   P_ATTRIBUTE1		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE1%TYPE,
   P_ATTRIBUTE2		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE2%TYPE,
   P_ATTRIBUTE3		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE3%TYPE,
   P_ATTRIBUTE4		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE4%TYPE,
   P_ATTRIBUTE5		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE5%TYPE,
   P_ATTRIBUTE6		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE6%TYPE,
   P_ATTRIBUTE7		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE7%TYPE,
   P_ATTRIBUTE8		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE8%TYPE,
   P_ATTRIBUTE9		  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE9%TYPE,
   P_ATTRIBUTE10	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE10%TYPE,
   P_ATTRIBUTE11	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE11%TYPE,
   P_ATTRIBUTE12	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE12%TYPE,
   P_ATTRIBUTE13	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE13%TYPE,
   P_ATTRIBUTE14	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE14%TYPE,
   P_ATTRIBUTE15	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE15%TYPE,
   P_ATTRIBUTE_CATEGORY	  IN   JTF_RS_RESOURCE_SKILLS.ATTRIBUTE_CATEGORY%TYPE,
   P_CASCADE_OPTION       IN   NUMBER,
   X_RETURN_STATUS        OUT NOCOPY   VARCHAR2,
   X_MSG_COUNT            OUT NOCOPY   NUMBER,
   X_MSG_DATA             OUT NOCOPY   VARCHAR2
  )IS


  CURSOR  skill_curr( p_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE)
  IS
  SELECT
  	      RESOURCE_SKILL_ID,
  	      RESOURCE_ID,
  	      SKILL_LEVEL_ID,
  	      CATEGORY_ID,
  	      SUBCATEGORY,
  	      PRODUCT_ID,
  	      PRODUCT_ORG_ID,
  	      PLATFORM_ID,
  	      PLATFORM_ORG_ID,
  	      PROBLEM_CODE,
  	      COMPONENT_ID,
  	      SUBCOMPONENT_ID,
  	      OBJECT_VERSION_NUMBER,
  	      ATTRIBUTE1,
  	      ATTRIBUTE2,
  	      ATTRIBUTE3,
  	      ATTRIBUTE4,
  	      ATTRIBUTE5,
  	      ATTRIBUTE6,
  	      ATTRIBUTE7,
  	      ATTRIBUTE8,
  	      ATTRIBUTE9,
  	      ATTRIBUTE10,
  	      ATTRIBUTE11,
  	      ATTRIBUTE12,
  	      ATTRIBUTE13,
  	      ATTRIBUTE14,
  	      ATTRIBUTE15,
  	      ATTRIBUTE_CATEGORY
  FROM jtf_rs_resource_skills
  WHERE   resource_skill_id = p_resource_skill_id;

Line: 1344

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

Line: 1358

   SAVEPOINT UPDATE_RESOURCE_SKILLS_SP;

Line: 1397

  UPDATE_RESOURCE_SKILLS(
		   P_API_VERSION            => l_api_version,
		   P_INIT_MSG_LIST          => l_init_msg_list,
		   P_COMMIT                 => l_commit,
		   P_RESOURCE_SKILL_ID      => p_resource_skill_id,
		   P_RESOURCE_ID            => p_resource_id,
		   P_SKILL_LEVEL_ID         => p_skill_level_id,
		   P_CATEGORY_ID            => p_category_id,
		   P_SUBCATEGORY            => p_subcategory,
		   P_PRODUCT_ID             => p_product_id,
		   P_PRODUCT_ORG_ID         => p_product_org_id,
		   P_PLATFORM_ID            => p_platform_id,
		   P_PLATFORM_ORG_ID        => p_platform_org_id,
		   P_PROBLEM_CODE           => p_problem_code,
		   P_COMPONENT_ID           => p_component_id,
		   P_SUBCOMPONENT_ID        => p_subcomponent_id,
		   P_OBJECT_VERSION_NUM     => p_object_version_num,
		   P_ATTRIBUTE1             => p_attribute1,
		   P_ATTRIBUTE2             => p_attribute2,
		   P_ATTRIBUTE3             => p_attribute3,
		   P_ATTRIBUTE4             => p_attribute4,
		   P_ATTRIBUTE5             => p_attribute5,
		   P_ATTRIBUTE6             => p_attribute6,
		   P_ATTRIBUTE7             => p_attribute7,
		   P_ATTRIBUTE8             => p_attribute8,
		   P_ATTRIBUTE9             => p_attribute9,
		   P_ATTRIBUTE10            => p_attribute10,
		   P_ATTRIBUTE11            => p_attribute11,
		   P_ATTRIBUTE12            => p_attribute12,
		   P_ATTRIBUTE13            => p_attribute13,
		   P_ATTRIBUTE14            => p_attribute14,
		   P_ATTRIBUTE15            => p_attribute15,
		   P_ATTRIBUTE_CATEGORY     => p_attribute_category,
		   X_RETURN_STATUS          => X_RETURN_STATUS,
		   X_MSG_COUNT              => X_MSG_COUNT,
		   X_MSG_DATA               => X_MSG_DATA
		);

Line: 1451

	-- Update existing component level skill ratings
	IF ( l_cascade_option = CASCADE_ALL )
	THEN
	   IF (SKILL_CURR_REC.PRODUCT_ID IS NULL) THEN
	       UPDATE_EXISTING_PROD_SKILLS
			 (
			   P_API_VERSION              => l_api_version,
			   P_INIT_MSG_LIST            => l_init_msg_list,
			   P_COMMIT                   => l_commit,
			   P_RESOURCE_ID              => skill_curr_rec.resource_id,
			   P_SKILL_LEVEL_ID           => skill_curr_rec.skill_level_id,
			   P_CATEGORY_ID              => skill_curr_rec.category_id,
			   X_RETURN_STATUS            => X_RETURN_STATUS,
			   X_MSG_COUNT                => X_MSG_COUNT,
			   X_MSG_DATA                 => X_MSG_DATA
			 );

Line: 1469

	      UPDATE_EXISTING_COMP_SKILLS
	      (
		 P_API_VERSION         	=> l_api_version,
		 P_INIT_MSG_LIST       	=> l_init_msg_list,
		 P_COMMIT              	=> l_commit,
		 P_RESOURCE_ID         	=> skill_curr_rec.resource_id,
		 P_SKILL_LEVEL_ID      	=> skill_curr_rec.skill_level_id,
		 P_CATEGORY_ID         	=> skill_curr_rec.category_id,
		 P_SUBCATEGORY         	=> skill_curr_rec.subcategory,
		 P_PRODUCT_ID          	=> skill_curr_rec.product_id,
		 P_PRODUCT_ORG_ID      	=> skill_curr_rec.product_org_id,
		 X_RETURN_STATUS       	=> X_RETURN_STATUS,
		 X_MSG_COUNT           	=> X_MSG_COUNT,
		 X_MSG_DATA            	=> X_MSG_DATA
	      );

Line: 1587

      ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;

Line: 1595

      ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;

Line: 1603

      ROLLBACK TO UPDATE_RESOURCE_SKILLS_SP;

Line: 1612

  END UPDATE_RESOURCE_SKILLS;