[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update resource skills
Delete resource skills
Calls to these procedures will invoke procedures from JTF_RS_RESOURCE_SKILLS_PUB
to do business validations and to do actual inserts, updates and deletes into tables.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME VARCHAR2(30) := 'JTF_RS_RESOURCE_SKILLS_PVT';
SELECT resource_id
FROM JTF_RS_RESOURCE_EXTNS
WHERE resource_id = ll_resource_id ;
SELECT skill_level_id
FROM JTF_RS_SKILL_LEVELS_B
WHERE skill_level_id = ll_skill_level_id;
SELECT category_id
FROM JTF_RS_ITEM_CATEGORIES_V
WHERE category_id = ll_category_id
AND nvl(enabled_flag, 'Y') <> 'N'
AND trunc(sysdate) < nvl(disable_date, sysdate);
SELECT cat.category_id
FROM JTF_RS_ITEM_CATEGORIES_V cat,
mtl_category_set_valid_cats ic
WHERE cat.category_id = ll_category_id
AND nvl(cat.enabled_flag, 'Y') <> 'N'
AND trunc(sysdate) < nvl(cat.disable_date, sysdate)
AND cat.category_id = ic.category_id
AND ic.category_set_id = ll_catset_id ;
SELECT lookup_code, meaning
FROM FND_LOOKUPS
WHERE lookup_type = 'JTF_RS_SKILL_CAT_TYPE'
AND enabled_flag = 'Y';
SELECT product_id
FROM JTF_RS_PRODUCTS_V
WHERE product_id = ll_product_id
AND product_org_id = ll_product_org_id
AND enabled_flag = 'Y';
SELECT p.product_id
FROM JTF_RS_PRODUCTS_V p
WHERE p.product_id = lpco_product_id
AND p.product_org_id = lpco_product_org_id
AND p.enabled_flag = 'Y'
AND EXISTS ( SELECT null FROM MTL_ITEM_CATEGORIES c
WHERE p.product_id = c.inventory_item_id
AND p.product_org_id = c.organization_id
AND c.category_id = lpco_category_id) ;
SELECT component_id, product_id
FROM JTF_RS_COMPONENTS_V
WHERE component_id = ll_component_id
AND product_id = ll_product_id
AND product_org_id = ll_product_org_id ;
SELECT platform_id
FROM JTF_RS_PLATFORMS_V
WHERE platform_id = ll_platform_id
AND platform_org_id = ll_platform_org_id;
SELECT platform_id
FROM JTF_RS_PLATFORMS_V
WHERE platform_id = ll_platform_id
AND platform_org_id = ll_platform_org_id
AND category_id = ll_category_id ;
SELECT problem_code
FROM JTF_RS_PROBLEM_CODES_V
WHERE problem_code = ll_problem_code
AND enabled_flag = 'Y'
AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
nvl(end_date_active, sysdate);
SELECT resource_skill_id
FROM JTF_RS_RESOURCE_SKILLS
WHERE resource_id = lpcat_resource_id
AND nvl(category_id,-99) = nvl(lpcat_category_id, -99)
AND subcategory is NULL;
SELECT resource_skill_id
FROM JTF_RS_RESOURCE_SKILLS
WHERE resource_id = lpd_resource_id
AND subcategory = lpd_subcategory
AND product_id = lpd_product_id
AND product_org_id = lpd_product_org_id
-- AND nvl(category_id,-99) = nvl(lpd_category_id, -99)
AND nvl(problem_code,-99) = nvl(lpd_problem_code, -99)
AND nvl(component_id, -99) = nvl(lpd_component_id, -99);
SELECT resource_skill_id
FROM JTF_RS_RESOURCE_SKILLS
WHERE resource_id = lpt_resource_id
AND subcategory = lpt_subcategory
AND platform_id = lpt_platform_id
AND platform_org_id = lpt_platform_org_id
AND nvl(category_id,-99) = nvl(lpt_category_id, -99) ;
SELECT resource_skill_id
FROM JTF_RS_RESOURCE_SKILLS
WHERE resource_id = lpc_resource_id
AND subcategory = lpc_subcategory
AND problem_code = lpc_problem_code
AND nvl(category_id, -99) = nvl(lpc_category_id, -99) ;
' SELECT problem_code, inventory_item_id
FROM CS_SR_PROB_CODE_MAPPING
WHERE problem_code = :1
AND inventory_item_id = :2
AND organization_id = :3
AND trunc(sysdate) between trunc(nvl(start_date_active, sysdate)) and
nvl(end_date_active, sysdate) '
USING p_problem_code, p_product_id, l_product_org_id;
SELECT jtf_rs_resource_skills_s.nextval
INTO l_resource_skill_id
FROM dual;
JTF_RS_RESOURCE_SKILLS_PKG.INSERT_ROW(
X_ROWID => l_rowid,
X_RESOURCE_SKILL_ID => l_resource_skill_id,
X_RESOURCE_ID => p_resource_id,
X_SKILL_LEVEL_ID => p_SKILL_LEVEL_ID,
X_CATEGORY_ID => p_category_id,
X_SUBCATEGORY => p_subcategory,
X_PRODUCT_ID => p_product_id,
X_PRODUCT_ORG_ID => l_product_org_id,
X_PLATFORM_ID => p_platform_id,
X_PLATFORM_ORG_ID => l_platform_org_id,
X_PROBLEM_CODE => p_problem_code,
X_COMPONENT_ID => p_component_id,
X_SUBCOMPONENT_ID => p_subcomponent_id,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
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 => l_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0);
/* 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';
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,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM jtf_rs_resource_skills
WHERE resource_skill_id = ll_resource_skill_id;
jtf_rs_resource_skills_pkg.update_row(
X_RESOURCE_SKILL_ID => l_resource_skill_id,
X_RESOURCE_ID => l_resource_id,
X_SKILL_LEVEL_ID => l_SKILL_LEVEL_ID,
X_CATEGORY_ID => l_category_id,
X_SUBCATEGORY => l_subcategory,
X_PRODUCT_ID => l_product_id,
X_PRODUCT_ORG_ID => l_product_org_id,
X_PLATFORM_ID => l_platform_id,
X_PLATFORM_ORG_ID => l_platform_org_id,
X_PROBLEM_CODE => l_problem_code,
X_COMPONENT_ID => l_component_id,
X_SUBCOMPONENT_ID => l_subcomponent_id,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_ATTRIBUTE1 => l_attribute1,
X_ATTRIBUTE2 => l_attribute2,
X_ATTRIBUTE3 => l_attribute3,
X_ATTRIBUTE4 => l_attribute4,
X_ATTRIBUTE5 => l_attribute5,
X_ATTRIBUTE6 => l_attribute6,
X_ATTRIBUTE7 => l_attribute7,
X_ATTRIBUTE8 => l_attribute8,
X_ATTRIBUTE9 => l_attribute9,
X_ATTRIBUTE10 => l_attribute10,
X_ATTRIBUTE11 => l_attribute11,
X_ATTRIBUTE12 => l_attribute12,
X_ATTRIBUTE13 => l_attribute13,
X_ATTRIBUTE14 => l_attribute14,
X_ATTRIBUTE15 => l_attribute15,
X_ATTRIBUTE_CATEGORY => l_attribute_category,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id);
END update_resource_skills;
/* 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
CURSOR chk_res_exist_cur(ll_resource_skill_id JTF_RS_RESOURCE_SKILLS.RESOURCE_SKILL_ID%TYPE)
IS
SELECT resource_id
FROM JTF_RS_RESOURCE_SKILLS
WHERE resource_skill_id = ll_resource_skill_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_SKILLS';
JTF_RS_RESOURCE_SKILLS_PKG.DELETE_ROW(
X_RESOURCE_SKILL_ID => l_resource_skill_id);
END delete_resource_skills;