[Home] [Help]
285: BEGIN
286: --Standard Start of API SAVEPOINT
287: SAVEPOINT RESOURCE_SKILLS_SP;
288:
289: x_return_status := fnd_api.g_ret_sts_success;
290:
291: --Standard Call to check API compatibility
292: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
293: THEN
288:
289: x_return_status := fnd_api.g_ret_sts_success;
290:
291: --Standard Call to check API compatibility
292: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
293: THEN
294: RAISE FND_API.G_EXC_ERROR;
295: END IF;
296:
290:
291: --Standard Call to check API compatibility
292: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
293: THEN
294: RAISE FND_API.G_EXC_ERROR;
295: END IF;
296:
297: --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
298: IF FND_API.To_boolean(P_INIT_MSG_LIST)
294: RAISE FND_API.G_EXC_ERROR;
295: END IF;
296:
297: --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
298: IF FND_API.To_boolean(P_INIT_MSG_LIST)
299: THEN
300: FND_MSG_PUB.Initialize;
301: END IF;
302:
315:
316: OPEN subcategory_cur(p_subcategory);
317: FETCH subcategory_cur into subcategory_rec;
318: IF (subcategory_cur%NOTFOUND) THEN
319: x_return_status := fnd_api.g_ret_sts_error;
320: fnd_message.set_name ('JTF', 'JTF_RS_SUBCATEGORY_INVALID');
321: FND_MSG_PUB.add;
322: RAISE fnd_api.g_exc_error;
323: END IF;
318: IF (subcategory_cur%NOTFOUND) THEN
319: x_return_status := fnd_api.g_ret_sts_error;
320: fnd_message.set_name ('JTF', 'JTF_RS_SUBCATEGORY_INVALID');
321: FND_MSG_PUB.add;
322: RAISE fnd_api.g_exc_error;
323: END IF;
324: CLOSE subcategory_cur;
325:
326: END IF;
335: OR (p_platform_id IS NOT NULL)
336: OR (p_platform_org_id IS NOT NULL)
337: /*OR (p_problem_code IS NOT NULL)*/)
338: THEN
339: x_return_status := fnd_api.g_ret_sts_error;
340: fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
341: FND_MSG_PUB.add;
342: RAISE fnd_api.g_exc_error;
343: END IF;
338: THEN
339: x_return_status := fnd_api.g_ret_sts_error;
340: fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
341: FND_MSG_PUB.add;
342: RAISE fnd_api.g_exc_error;
343: END IF;
344:
345: -- If user doesn't pass org_id determine it and set it
346: IF (p_product_org_id IS NULL) THEN
358: OR (p_product_org_id IS NOT NULL)
359: OR (p_component_id IS NOT NULL)
360: OR (p_problem_code IS NOT NULL))
361: THEN
362: x_return_status := fnd_api.g_ret_sts_error;
363: fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
364: FND_MSG_PUB.add;
365: RAISE fnd_api.g_exc_error;
366: END IF;
361: THEN
362: x_return_status := fnd_api.g_ret_sts_error;
363: fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
364: FND_MSG_PUB.add;
365: RAISE fnd_api.g_exc_error;
366: END IF;
367:
368: -- If user doesn't pass org_id determine it and set it
369: IF (p_platform_org_id IS NULL) THEN
382: OR (p_component_id IS NOT NULL)
383: OR (p_platform_id IS NOT NULL)
384: OR (p_platform_org_id IS NOT NULL))
385: THEN
386: x_return_status := fnd_api.g_ret_sts_error;
387: fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
388: FND_MSG_PUB.add;
389: RAISE fnd_api.g_exc_error;
390: END IF;
385: THEN
386: x_return_status := fnd_api.g_ret_sts_error;
387: fnd_message.set_name ('JTF', 'JTF_RS_PARAM_COMBO_INVALID');
388: FND_MSG_PUB.add;
389: RAISE fnd_api.g_exc_error;
390: END IF;
391: ELSE
392: IF (p_subcategory IS NOT NULL) THEN
393: x_return_status := fnd_api.g_ret_sts_error;
389: RAISE fnd_api.g_exc_error;
390: END IF;
391: ELSE
392: IF (p_subcategory IS NOT NULL) THEN
393: x_return_status := fnd_api.g_ret_sts_error;
394: fnd_message.set_name ('JTF', 'JTF_RS_SUBCATEGORY_INVALID');
395: FND_MSG_PUB.add;
396: RAISE fnd_api.g_exc_error;
397: END IF;
392: IF (p_subcategory IS NOT NULL) THEN
393: x_return_status := fnd_api.g_ret_sts_error;
394: fnd_message.set_name ('JTF', 'JTF_RS_SUBCATEGORY_INVALID');
395: FND_MSG_PUB.add;
396: RAISE fnd_api.g_exc_error;
397: END IF;
398: END IF;
399:
400: -----------------------------------------------------------------------
403:
404: OPEN resource_id_cur(p_resource_id);
405: FETCH resource_id_cur into resource_id_rec;
406: IF (resource_id_cur%NOTFOUND) THEN
407: x_return_status := fnd_api.g_ret_sts_error;
408: fnd_message.set_name ('JTF', 'JTF_RS_RES_ID_INVALID');
409: FND_MSG_PUB.add;
410: RAISE fnd_api.g_exc_error;
411: END IF;
406: IF (resource_id_cur%NOTFOUND) THEN
407: x_return_status := fnd_api.g_ret_sts_error;
408: fnd_message.set_name ('JTF', 'JTF_RS_RES_ID_INVALID');
409: FND_MSG_PUB.add;
410: RAISE fnd_api.g_exc_error;
411: END IF;
412: CLOSE resource_id_cur;
413:
414: -----------------------------------------------------------------------
417:
418: OPEN skill_level_id_cur(p_skill_level_id);
419: FETCH skill_level_id_cur into skill_level_id_rec;
420: IF (skill_level_id_cur%NOTFOUND) THEN
421: x_return_status := fnd_api.g_ret_sts_error;
422: fnd_message.set_name ('JTF', 'JTF_RS_SKILL_LEVEL_ID_INVALID');
423: FND_MSG_PUB.add;
424: RAISE fnd_api.g_exc_error;
425: END IF;
420: IF (skill_level_id_cur%NOTFOUND) THEN
421: x_return_status := fnd_api.g_ret_sts_error;
422: fnd_message.set_name ('JTF', 'JTF_RS_SKILL_LEVEL_ID_INVALID');
423: FND_MSG_PUB.add;
424: RAISE fnd_api.g_exc_error;
425: END IF;
426: CLOSE skill_level_id_cur;
427:
428: -----------------------------------------------------------------------
432: IF (l_catset IS NULL) THEN
433: OPEN category_id_cur(p_category_id);
434: FETCH category_id_cur into category_id_rec;
435: IF (category_id_cur%NOTFOUND) THEN
436: x_return_status := fnd_api.g_ret_sts_error;
437: fnd_message.set_name ('JTF', 'JTF_RS_CATEGORY_ID_INVALID');
438: FND_MSG_PUB.add;
439: RAISE fnd_api.g_exc_error;
440: END IF;
435: IF (category_id_cur%NOTFOUND) THEN
436: x_return_status := fnd_api.g_ret_sts_error;
437: fnd_message.set_name ('JTF', 'JTF_RS_CATEGORY_ID_INVALID');
438: FND_MSG_PUB.add;
439: RAISE fnd_api.g_exc_error;
440: END IF;
441: CLOSE category_id_cur;
442: ELSE
443: OPEN category_catset_id_cur(p_category_id, l_catset);
442: ELSE
443: OPEN category_catset_id_cur(p_category_id, l_catset);
444: FETCH category_catset_id_cur into category_catset_id_rec;
445: IF (category_catset_id_cur%NOTFOUND) THEN
446: x_return_status := fnd_api.g_ret_sts_error;
447: fnd_message.set_name ('JTF', 'JTF_RS_CATEGORY_ID_INVALID');
448: FND_MSG_PUB.add;
449: RAISE fnd_api.g_exc_error;
450: END IF;
445: IF (category_catset_id_cur%NOTFOUND) THEN
446: x_return_status := fnd_api.g_ret_sts_error;
447: fnd_message.set_name ('JTF', 'JTF_RS_CATEGORY_ID_INVALID');
448: FND_MSG_PUB.add;
449: RAISE fnd_api.g_exc_error;
450: END IF;
451: CLOSE category_catset_id_cur;
452: END IF;
453: END IF;
460: IF (p_category_id IS NULL) THEN
461: OPEN product_id_cur(p_product_id, l_product_org_id);
462: FETCH product_id_cur into product_id_rec;
463: IF (product_id_cur%NOTFOUND) THEN
464: x_return_status := fnd_api.g_ret_sts_error;
465: fnd_message.set_name ('JTF', 'JTF_RS_PRODUCT_ID_INVALID');
466: FND_MSG_PUB.add;
467: RAISE fnd_api.g_exc_error;
468: END IF;
463: IF (product_id_cur%NOTFOUND) THEN
464: x_return_status := fnd_api.g_ret_sts_error;
465: fnd_message.set_name ('JTF', 'JTF_RS_PRODUCT_ID_INVALID');
466: FND_MSG_PUB.add;
467: RAISE fnd_api.g_exc_error;
468: END IF;
469: CLOSE product_id_cur;
470: ELSE
471: OPEN product_cat_id_cur(p_product_id, p_category_id, l_product_org_id);
470: ELSE
471: OPEN product_cat_id_cur(p_product_id, p_category_id, l_product_org_id);
472: FETCH product_cat_id_cur into product_cat_id_rec;
473: IF (product_cat_id_cur%NOTFOUND) THEN
474: x_return_status := fnd_api.g_ret_sts_error;
475: fnd_message.set_name ('JTF', 'JTF_RS_PROD_CAT_ID_INVALID');
476: FND_MSG_PUB.add;
477: RAISE fnd_api.g_exc_error;
478: END IF;
473: IF (product_cat_id_cur%NOTFOUND) THEN
474: x_return_status := fnd_api.g_ret_sts_error;
475: fnd_message.set_name ('JTF', 'JTF_RS_PROD_CAT_ID_INVALID');
476: FND_MSG_PUB.add;
477: RAISE fnd_api.g_exc_error;
478: END IF;
479: CLOSE product_cat_id_cur;
480: END IF;
481:
480: END IF;
481:
482:
483: IF (p_component_id IS NOT NULL AND p_problem_code IS NOT NULL) THEN
484: x_return_status := fnd_api.g_ret_sts_error;
485: fnd_message.set_name ('JTF', 'JTF_RS_COMP_PROB_CODE_MUTEX');
486: FND_MSG_PUB.add;
487: RAISE fnd_api.g_exc_error;
488: END IF;
483: IF (p_component_id IS NOT NULL AND p_problem_code IS NOT NULL) THEN
484: x_return_status := fnd_api.g_ret_sts_error;
485: fnd_message.set_name ('JTF', 'JTF_RS_COMP_PROB_CODE_MUTEX');
486: FND_MSG_PUB.add;
487: RAISE fnd_api.g_exc_error;
488: END IF;
489:
490: IF (p_component_id IS NOT NULL) THEN
491: OPEN component_id_cur(p_component_id, p_product_id, l_product_org_id);
490: IF (p_component_id IS NOT NULL) THEN
491: OPEN component_id_cur(p_component_id, p_product_id, l_product_org_id);
492: FETCH component_id_cur into component_id_rec;
493: IF (component_id_cur%NOTFOUND) THEN
494: x_return_status := fnd_api.g_ret_sts_error;
495: fnd_message.set_name ('JTF', 'JTF_RS_COMPONENT_ID_INVALID');
496: FND_MSG_PUB.add;
497: RAISE fnd_api.g_exc_error;
498: END IF;
493: IF (component_id_cur%NOTFOUND) THEN
494: x_return_status := fnd_api.g_ret_sts_error;
495: fnd_message.set_name ('JTF', 'JTF_RS_COMPONENT_ID_INVALID');
496: FND_MSG_PUB.add;
497: RAISE fnd_api.g_exc_error;
498: END IF;
499: CLOSE component_id_cur;
500: END IF;
501:
512: USING p_problem_code, p_product_id, l_product_org_id;
513:
514: FETCH prod_prob_code_cur into l_problem_code, l_product_id;
515: IF (prod_prob_code_cur%NOTFOUND) THEN
516: x_return_status := fnd_api.g_ret_sts_error;
517: fnd_message.set_name ('JTF', 'JTF_RS_PROD_PROB_CODE_INVALID');
518: FND_MSG_PUB.add;
519: RAISE fnd_api.g_exc_error;
520: END IF;
515: IF (prod_prob_code_cur%NOTFOUND) THEN
516: x_return_status := fnd_api.g_ret_sts_error;
517: fnd_message.set_name ('JTF', 'JTF_RS_PROD_PROB_CODE_INVALID');
518: FND_MSG_PUB.add;
519: RAISE fnd_api.g_exc_error;
520: END IF;
521: END IF;
522:
523: ELSIF (p_subcategory = 'PLATFORM') THEN
524: IF (p_category_id IS NULL) THEN
525: OPEN platform_id_cur(p_platform_id, l_platform_org_id);
526: FETCH platform_id_cur into platform_id_rec;
527: IF (platform_id_cur%NOTFOUND) THEN
528: x_return_status := fnd_api.g_ret_sts_error;
529: fnd_message.set_name ('JTF', 'JTF_RS_PLATFORM_ID_INVALID');
530: FND_MSG_PUB.add;
531: RAISE fnd_api.g_exc_error;
532: END IF;
527: IF (platform_id_cur%NOTFOUND) THEN
528: x_return_status := fnd_api.g_ret_sts_error;
529: fnd_message.set_name ('JTF', 'JTF_RS_PLATFORM_ID_INVALID');
530: FND_MSG_PUB.add;
531: RAISE fnd_api.g_exc_error;
532: END IF;
533: CLOSE platform_id_cur;
534: ELSE
535: OPEN platform_cat_id_cur(p_platform_id, p_category_id, l_platform_org_id);
534: ELSE
535: OPEN platform_cat_id_cur(p_platform_id, p_category_id, l_platform_org_id);
536: FETCH platform_cat_id_cur into platform_cat_id_rec;
537: IF (platform_cat_id_cur%NOTFOUND) THEN
538: x_return_status := fnd_api.g_ret_sts_error;
539: fnd_message.set_name ('JTF', 'JTF_RS_PLAT_CAT_ID_INVALID');
540: FND_MSG_PUB.add;
541: RAISE fnd_api.g_exc_error;
542: END IF;
537: IF (platform_cat_id_cur%NOTFOUND) THEN
538: x_return_status := fnd_api.g_ret_sts_error;
539: fnd_message.set_name ('JTF', 'JTF_RS_PLAT_CAT_ID_INVALID');
540: FND_MSG_PUB.add;
541: RAISE fnd_api.g_exc_error;
542: END IF;
543: CLOSE platform_cat_id_cur;
544: END IF;
545:
546: ELSIF (p_subcategory = 'PROBLEM_CODE') THEN
547: OPEN problem_code_cur(p_problem_code);
548: FETCH problem_code_cur into problem_code_rec;
549: IF (problem_code_cur%NOTFOUND) THEN
550: x_return_status := fnd_api.g_ret_sts_error;
551: fnd_message.set_name ('JTF', 'JTF_RS_PROBLEM_CODE_INVALID');
552: FND_MSG_PUB.add;
553: RAISE fnd_api.g_exc_error;
554: END IF;
549: IF (problem_code_cur%NOTFOUND) THEN
550: x_return_status := fnd_api.g_ret_sts_error;
551: fnd_message.set_name ('JTF', 'JTF_RS_PROBLEM_CODE_INVALID');
552: FND_MSG_PUB.add;
553: RAISE fnd_api.g_exc_error;
554: END IF;
555: CLOSE problem_code_cur;
556: END IF;
557:
565: IF (product_dup_cur%NOTFOUND) THEN
566: l_go_ahead := 'YES';
567: ELSE
568: l_go_ahead := 'NO';
569: x_return_status := fnd_api.g_ret_sts_error;
570: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
571: FND_MSG_PUB.add;
572: RAISE fnd_api.g_exc_error;
573: END IF;
568: l_go_ahead := 'NO';
569: x_return_status := fnd_api.g_ret_sts_error;
570: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
571: FND_MSG_PUB.add;
572: RAISE fnd_api.g_exc_error;
573: END IF;
574: CLOSE product_dup_cur;
575: ELSIF (p_subcategory = 'PLATFORM') THEN
576: OPEN platform_dup_cur(p_resource_id, p_category_id, p_subcategory, p_platform_id, l_platform_org_id);
578: IF (platform_dup_cur%NOTFOUND) THEN
579: l_go_ahead := 'YES';
580: ELSE
581: l_go_ahead := 'NO';
582: x_return_status := fnd_api.g_ret_sts_error;
583: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
584: FND_MSG_PUB.add;
585: RAISE fnd_api.g_exc_error;
586: END IF;
581: l_go_ahead := 'NO';
582: x_return_status := fnd_api.g_ret_sts_error;
583: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
584: FND_MSG_PUB.add;
585: RAISE fnd_api.g_exc_error;
586: END IF;
587: CLOSE platform_dup_cur;
588: ELSIF (p_subcategory = 'PROBLEM_CODE') THEN
589: OPEN problem_code_dup_cur(p_resource_id, p_category_id, p_subcategory, p_problem_code);
591: IF (problem_code_dup_cur%NOTFOUND) THEN
592: l_go_ahead := 'YES';
593: ELSE
594: l_go_ahead := 'NO';
595: x_return_status := fnd_api.g_ret_sts_error;
596: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
597: FND_MSG_PUB.add;
598: RAISE fnd_api.g_exc_error;
599: END IF;
594: l_go_ahead := 'NO';
595: x_return_status := fnd_api.g_ret_sts_error;
596: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
597: FND_MSG_PUB.add;
598: RAISE fnd_api.g_exc_error;
599: END IF;
600: CLOSE problem_code_dup_cur;
601: ELSE
602: IF (p_subcategory IS NULL and p_category_id is NOT NULL) THEN
606: IF (category_dup_cur%NOTFOUND) THEN
607: l_go_ahead := 'YES';
608: ELSE
609: l_go_ahead := 'NO';
610: x_return_status := fnd_api.g_ret_sts_error;
611: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
612: FND_MSG_PUB.add;
613: RAISE fnd_api.g_exc_error;
614: END IF;
609: l_go_ahead := 'NO';
610: x_return_status := fnd_api.g_ret_sts_error;
611: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
612: FND_MSG_PUB.add;
613: RAISE fnd_api.g_exc_error;
614: END IF;
615: CLOSE category_dup_cur;
616: l_go_ahead := 'YES';
617: ELSE
670: ELSE
671: fnd_message.set_name ('JTF', 'JTF_RS_DUPLICATE_SKILL');
672: FND_MSG_PUB.add;
673: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
674: raise fnd_api.g_exc_error;
675: END IF;
676:
677:
678: --standard commit
675: END IF;
676:
677:
678: --standard commit
679: IF fnd_api.to_boolean (p_commit)
680: THEN
681: COMMIT WORK;
682: END IF;
683:
683:
684: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
685:
686: EXCEPTION
687: WHEN fnd_api.g_exc_unexpected_error
688: THEN
689: ROLLBACK TO RESOURCE_SKILLS_SP;
690: x_return_status := fnd_api.g_ret_sts_unexp_error;
691: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
686: EXCEPTION
687: WHEN fnd_api.g_exc_unexpected_error
688: THEN
689: ROLLBACK TO RESOURCE_SKILLS_SP;
690: x_return_status := fnd_api.g_ret_sts_unexp_error;
691: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
692: WHEN fnd_api.g_exc_error
693: THEN
694: ROLLBACK TO RESOURCE_SKILLS_SP;
688: THEN
689: ROLLBACK TO RESOURCE_SKILLS_SP;
690: x_return_status := fnd_api.g_ret_sts_unexp_error;
691: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
692: WHEN fnd_api.g_exc_error
693: THEN
694: ROLLBACK TO RESOURCE_SKILLS_SP;
695: x_return_status := fnd_api.g_ret_sts_error;
696: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
691: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
692: WHEN fnd_api.g_exc_error
693: THEN
694: ROLLBACK TO RESOURCE_SKILLS_SP;
695: x_return_status := fnd_api.g_ret_sts_error;
696: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
697: WHEN OTHERS
698: THEN
699: ROLLBACK TO RESOURCE_SKILLS_SP;
701: fnd_message.set_token('P_SQLCODE',SQLCODE);
702: fnd_message.set_token('P_SQLERRM',SQLERRM);
703: fnd_message.set_token('P_API_NAME', l_api_name);
704: FND_MSG_PUB.add;
705: x_return_status := fnd_api.g_ret_sts_unexp_error;
706: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
707:
708: END create_resource_skills;
709:
843: BEGIN
844: --Standard Start of API SAVEPOINT
845: SAVEPOINT RESOURCE_SKILLS_SP;
846:
847: x_return_status := fnd_api.g_ret_sts_success;
848:
849: --Standard Call to check API compatibility
850: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
851: THEN
846:
847: x_return_status := fnd_api.g_ret_sts_success;
848:
849: --Standard Call to check API compatibility
850: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
851: THEN
852: RAISE FND_API.G_EXC_ERROR;
853: END IF;
854:
848:
849: --Standard Call to check API compatibility
850: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
851: THEN
852: RAISE FND_API.G_EXC_ERROR;
853: END IF;
854:
855: --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
856: IF FND_API.To_boolean(P_INIT_MSG_LIST)
852: RAISE FND_API.G_EXC_ERROR;
853: END IF;
854:
855: --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
856: IF FND_API.To_boolean(P_INIT_MSG_LIST)
857: THEN
858: FND_MSG_PUB.Initialize;
859: END IF;
860:
868: FETCH resource_skills_cur INTO resource_skills_rec;
869:
870: IF (resource_skills_cur%found) THEN
871:
872: IF (p_resource_id = FND_API.G_MISS_NUM)
873: THEN
874: l_resource_id := resource_skills_rec.resource_id;
875: ELSE
876: l_resource_id := p_resource_id;
875: ELSE
876: l_resource_id := p_resource_id;
877: END IF;
878:
879: IF (p_SKILL_LEVEL_ID = FND_API.G_MISS_NUM)
880: THEN
881: l_SKILL_LEVEL_ID := resource_skills_rec.SKILL_LEVEL_ID;
882: ELSE
883: l_SKILL_LEVEL_ID := p_SKILL_LEVEL_ID;
882: ELSE
883: l_SKILL_LEVEL_ID := p_SKILL_LEVEL_ID;
884: END IF;
885:
886: IF (p_category_id = FND_API.G_MISS_NUM)
887: THEN
888: l_category_id := resource_skills_rec.category_id;
889: ELSE
890: l_category_id := p_category_id;
889: ELSE
890: l_category_id := p_category_id;
891: END IF;
892:
893: IF (p_subcategory = FND_API.G_MISS_CHAR)
894: THEN
895: l_subcategory := resource_skills_rec.subcategory;
896: ELSE
897: l_subcategory := p_subcategory;
896: ELSE
897: l_subcategory := p_subcategory;
898: END IF;
899:
900: IF (p_product_id = FND_API.G_MISS_NUM)
901: THEN
902: l_product_id := resource_skills_rec.product_id;
903: ELSE
904: l_product_id := p_product_id;
903: ELSE
904: l_product_id := p_product_id;
905: END IF;
906:
907: IF (p_product_org_id = FND_API.G_MISS_NUM)
908: THEN
909: l_product_org_id := resource_skills_rec.product_org_id;
910: ELSE
911: l_product_org_id := p_product_org_id;
910: ELSE
911: l_product_org_id := p_product_org_id;
912: END IF;
913:
914: IF (p_platform_id = FND_API.G_MISS_NUM)
915: THEN
916: l_platform_id := resource_skills_rec.platform_id;
917: ELSE
918: l_platform_id := p_platform_id;
917: ELSE
918: l_platform_id := p_platform_id;
919: END IF;
920:
921: IF (p_platform_org_id = FND_API.G_MISS_NUM)
922: THEN
923: l_platform_org_id := resource_skills_rec.platform_org_id;
924: ELSE
925: l_platform_org_id := p_platform_org_id;
924: ELSE
925: l_platform_org_id := p_platform_org_id;
926: END IF;
927:
928: IF (p_problem_code = FND_API.G_MISS_CHAR)
929: THEN
930: l_problem_code := resource_skills_rec.problem_code;
931: ELSE
932: l_problem_code := p_problem_code;
931: ELSE
932: l_problem_code := p_problem_code;
933: END IF;
934:
935: IF (p_component_id = FND_API.G_MISS_NUM)
936: THEN
937: l_component_id := resource_skills_rec.component_id;
938: ELSE
939: l_component_id := p_component_id;
938: ELSE
939: l_component_id := p_component_id;
940: END IF;
941:
942: IF (p_subcomponent_id = FND_API.G_MISS_NUM)
943: THEN
944: l_subcomponent_id := resource_skills_rec.subcomponent_id;
945: ELSE
946: l_subcomponent_id := p_subcomponent_id;
945: ELSE
946: l_subcomponent_id := p_subcomponent_id;
947: END IF;
948:
949: IF(p_attribute1 = FND_API.G_MISS_CHAR)
950: THEN
951: l_attribute1 := resource_skills_rec.attribute1;
952: ELSE
953: l_attribute1 := p_attribute1;
952: ELSE
953: l_attribute1 := p_attribute1;
954: END IF;
955:
956: IF(p_attribute2 = FND_API.G_MISS_CHAR)
957: THEN
958: l_attribute2 := resource_skills_rec.attribute2;
959: ELSE
960: l_attribute2 := p_attribute2;
959: ELSE
960: l_attribute2 := p_attribute2;
961: END IF;
962:
963: IF(p_attribute3 = FND_API.G_MISS_CHAR)
964: THEN
965: l_attribute3 := resource_skills_rec.attribute3;
966: ELSE
967: l_attribute3 := p_attribute3;
966: ELSE
967: l_attribute3 := p_attribute3;
968: END IF;
969:
970: IF(p_attribute4 = FND_API.G_MISS_CHAR)
971: THEN
972: l_attribute4 := resource_skills_rec.attribute4;
973: ELSE
974: l_attribute4 := p_attribute4;
973: ELSE
974: l_attribute4 := p_attribute4;
975: END IF;
976:
977: IF(p_attribute5 = FND_API.G_MISS_CHAR)
978: THEN
979: l_attribute5 := resource_skills_rec.attribute5;
980: ELSE
981: l_attribute5 := p_attribute5;
980: ELSE
981: l_attribute5 := p_attribute5;
982: END IF;
983:
984: IF(p_attribute6 = FND_API.G_MISS_CHAR)
985: THEN
986: l_attribute6 := resource_skills_rec.attribute6;
987: ELSE
988: l_attribute6 := p_attribute6;
987: ELSE
988: l_attribute6 := p_attribute6;
989: END IF;
990:
991: IF(p_attribute7 = FND_API.G_MISS_CHAR)
992: THEN
993: l_attribute7 := resource_skills_rec.attribute7;
994: ELSE
995: l_attribute7 := p_attribute7;
994: ELSE
995: l_attribute7 := p_attribute7;
996: END IF;
997:
998: IF(p_attribute8 = FND_API.G_MISS_CHAR)
999: THEN
1000: l_attribute8 := resource_skills_rec.attribute8;
1001: ELSE
1002: l_attribute8 := p_attribute8;
1001: ELSE
1002: l_attribute8 := p_attribute8;
1003: END IF;
1004:
1005: IF(p_attribute9 = FND_API.G_MISS_CHAR)
1006: THEN
1007: l_attribute9 := resource_skills_rec.attribute9;
1008: ELSE
1009: l_attribute9 := p_attribute9;
1008: ELSE
1009: l_attribute9 := p_attribute9;
1010: END IF;
1011:
1012: IF(p_attribute10 = FND_API.G_MISS_CHAR)
1013: THEN
1014: l_attribute10 := resource_skills_rec.attribute10;
1015: ELSE
1016: l_attribute10 := p_attribute10;
1015: ELSE
1016: l_attribute10 := p_attribute10;
1017: END IF;
1018:
1019: IF(p_attribute11 = FND_API.G_MISS_CHAR)
1020: THEN
1021: l_attribute11 := resource_skills_rec.attribute11;
1022: ELSE
1023: l_attribute11 := p_attribute11;
1022: ELSE
1023: l_attribute11 := p_attribute11;
1024: END IF;
1025:
1026: IF(p_attribute12 = FND_API.G_MISS_CHAR)
1027: THEN
1028: l_attribute12 := resource_skills_rec.attribute12;
1029: ELSE
1030: l_attribute12 := p_attribute12;
1029: ELSE
1030: l_attribute12 := p_attribute12;
1031: END IF;
1032:
1033: IF(p_attribute13 = FND_API.G_MISS_CHAR)
1034: THEN
1035: l_attribute13 := resource_skills_rec.attribute13;
1036: ELSE
1037: l_attribute13 := p_attribute13;
1036: ELSE
1037: l_attribute13 := p_attribute13;
1038: END IF;
1039:
1040: IF(p_attribute14 = FND_API.G_MISS_CHAR)
1041: THEN
1042: l_attribute14 := resource_skills_rec.attribute14;
1043: ELSE
1044: l_attribute14 := p_attribute14;
1043: ELSE
1044: l_attribute14 := p_attribute14;
1045: END IF;
1046:
1047: IF(p_attribute15 = FND_API.G_MISS_CHAR)
1048: THEN
1049: l_attribute15 := resource_skills_rec.attribute15;
1050: ELSE
1051: l_attribute15 := p_attribute15;
1050: ELSE
1051: l_attribute15 := p_attribute15;
1052: END IF;
1053:
1054: IF(p_attribute_category = FND_API.G_MISS_CHAR)
1055: THEN
1056: l_attribute_category := resource_skills_rec.attribute_category;
1057: ELSE
1058: l_attribute_category := p_attribute_category;
1070:
1071: EXCEPTION
1072:
1073: WHEN OTHERS THEN
1074: x_return_status := fnd_api.g_ret_sts_error;
1075: fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1076: fnd_msg_pub.add;
1077: RAISE fnd_api.g_exc_error;
1078:
1073: WHEN OTHERS THEN
1074: x_return_status := fnd_api.g_ret_sts_error;
1075: fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
1076: fnd_msg_pub.add;
1077: RAISE fnd_api.g_exc_error;
1078:
1079: END;
1080:
1081: l_object_version_number := l_object_version_number +1;
1118:
1119: P_OBJECT_VERSION_NUM := l_object_version_number;
1120:
1121: ELSIF (resource_skills_cur%notfound) THEN
1122: x_return_status := fnd_api.g_ret_sts_error;
1123: fnd_message.set_name ('JTF', 'JTF_RS_RES_SKILL_ID_INVALID');
1124: FND_MSG_PUB.add;
1125: RAISE fnd_api.g_exc_error;
1126:
1121: ELSIF (resource_skills_cur%notfound) THEN
1122: x_return_status := fnd_api.g_ret_sts_error;
1123: fnd_message.set_name ('JTF', 'JTF_RS_RES_SKILL_ID_INVALID');
1124: FND_MSG_PUB.add;
1125: RAISE fnd_api.g_exc_error;
1126:
1127: END IF;
1128:
1129: CLOSE resource_skills_cur;
1127: END IF;
1128:
1129: CLOSE resource_skills_cur;
1130:
1131: IF fnd_api.to_boolean (p_commit)
1132: THEN
1133: COMMIT WORK;
1134: END IF;
1135:
1135:
1136: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1137:
1138: EXCEPTION
1139: WHEN fnd_api.g_exc_error
1140: THEN
1141: ROLLBACK TO RESOURCE_SKILLS_SP;
1142: x_return_status := fnd_api.g_ret_sts_error;
1143: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1138: EXCEPTION
1139: WHEN fnd_api.g_exc_error
1140: THEN
1141: ROLLBACK TO RESOURCE_SKILLS_SP;
1142: x_return_status := fnd_api.g_ret_sts_error;
1143: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1144: WHEN fnd_api.g_exc_unexpected_error
1145: THEN
1146: ROLLBACK TO RESOURCE_SKILLS_SP;
1140: THEN
1141: ROLLBACK TO RESOURCE_SKILLS_SP;
1142: x_return_status := fnd_api.g_ret_sts_error;
1143: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1144: WHEN fnd_api.g_exc_unexpected_error
1145: THEN
1146: ROLLBACK TO RESOURCE_SKILLS_SP;
1147: x_return_status := fnd_api.g_ret_sts_unexp_error;
1148: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1143: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1144: WHEN fnd_api.g_exc_unexpected_error
1145: THEN
1146: ROLLBACK TO RESOURCE_SKILLS_SP;
1147: x_return_status := fnd_api.g_ret_sts_unexp_error;
1148: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1149: WHEN OTHERS
1150: THEN
1151: ROLLBACK TO RESOURCE_SKILLS_SP;
1153: fnd_message.set_token('P_SQLCODE',SQLCODE);
1154: fnd_message.set_token('P_SQLERRM',SQLERRM);
1155: fnd_message.set_token('P_API_NAME',l_api_name);
1156: FND_MSG_PUB.add;
1157: x_return_status := fnd_api.g_ret_sts_unexp_error;
1158: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1159: END update_resource_skills;
1160:
1161:
1202: BEGIN
1203: --Standard Start of API SAVEPOINT
1204: SAVEPOINT RESOURCE_SKILLS_SP;
1205:
1206: x_return_status := fnd_api.g_ret_sts_success;
1207:
1208: --Standard Call to check API compatibility
1209: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1210: THEN
1205:
1206: x_return_status := fnd_api.g_ret_sts_success;
1207:
1208: --Standard Call to check API compatibility
1209: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1210: THEN
1211: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212: END IF;
1213:
1207:
1208: --Standard Call to check API compatibility
1209: IF NOT FND_API.Compatible_API_CALL(L_API_VERSION,P_API_VERSION,L_API_NAME,G_PKG_NAME)
1210: THEN
1211: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212: END IF;
1213:
1214: --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1215: IF FND_API.To_boolean(P_INIT_MSG_LIST)
1211: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1212: END IF;
1213:
1214: --Initialize the message List if P_INIT_MSG_LIST is set to TRUE
1215: IF FND_API.To_boolean(P_INIT_MSG_LIST)
1216: THEN
1217: FND_MSG_PUB.Initialize;
1218: END IF;
1219:
1225: JTF_RS_RESOURCE_SKILLS_PKG.DELETE_ROW(
1226: X_RESOURCE_SKILL_ID => l_resource_skill_id);
1227:
1228: ELSIF (chk_res_exist_cur%notfound) THEN
1229: x_return_status := fnd_api.g_ret_sts_error;
1230: fnd_message.set_name ('JTF', 'JTF_RS_RES_SKILL_ID_INVALID');
1231: FND_MSG_PUB.add;
1232: RAISE fnd_api.g_exc_error;
1233:
1228: ELSIF (chk_res_exist_cur%notfound) THEN
1229: x_return_status := fnd_api.g_ret_sts_error;
1230: fnd_message.set_name ('JTF', 'JTF_RS_RES_SKILL_ID_INVALID');
1231: FND_MSG_PUB.add;
1232: RAISE fnd_api.g_exc_error;
1233:
1234: END IF;
1235:
1236: CLOSE chk_res_exist_cur;
1234: END IF;
1235:
1236: CLOSE chk_res_exist_cur;
1237:
1238: IF fnd_api.to_boolean (p_commit)
1239: THEN
1240: COMMIT WORK;
1241: END IF;
1242:
1242:
1243: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1244:
1245: EXCEPTION
1246: WHEN fnd_api.g_exc_unexpected_error
1247: THEN
1248: ROLLBACK TO RESOURCE_SKILLS_SP;
1249: x_return_status := fnd_api.g_ret_sts_unexp_error;
1250: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1245: EXCEPTION
1246: WHEN fnd_api.g_exc_unexpected_error
1247: THEN
1248: ROLLBACK TO RESOURCE_SKILLS_SP;
1249: x_return_status := fnd_api.g_ret_sts_unexp_error;
1250: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1251: WHEN fnd_api.g_exc_error
1252: THEN
1253: ROLLBACK TO RESOURCE_SKILLS_SP;
1247: THEN
1248: ROLLBACK TO RESOURCE_SKILLS_SP;
1249: x_return_status := fnd_api.g_ret_sts_unexp_error;
1250: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1251: WHEN fnd_api.g_exc_error
1252: THEN
1253: ROLLBACK TO RESOURCE_SKILLS_SP;
1254: x_return_status := fnd_api.g_ret_sts_error;
1255: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1250: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1251: WHEN fnd_api.g_exc_error
1252: THEN
1253: ROLLBACK TO RESOURCE_SKILLS_SP;
1254: x_return_status := fnd_api.g_ret_sts_error;
1255: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1256: WHEN OTHERS
1257: THEN
1258: ROLLBACK TO RESOURCE_SKILLS_SP;
1260: fnd_message.set_token('P_SQLCODE',SQLCODE);
1261: fnd_message.set_token('P_SQLERRM',SQLERRM);
1262: fnd_message.set_token('P_API_NAME',l_api_name);
1263: FND_MSG_PUB.add;
1264: x_return_status := fnd_api.g_ret_sts_unexp_error;
1265: FND_MSG_PUB.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1266:
1267: END delete_resource_skills;
1268: