[Home] [Help]
75:
76: CURSOR c_res_list_member_csr_1 IS
77: SELECT 'x'
78: FROM
79: pa_resource_list_members
80: WHERE resource_list_id = p_resource_list_id
81: AND parent_member_id IS NULL
82: AND sort_order = p_sort_order;
83:
84: CURSOR c_res_list_member_csr_2 IS
85: SELECT
86: NVL(MAX(sort_order),0)+10
87: FROM
88: pa_resource_list_members
89: WHERE resource_list_id = p_resource_list_id
90: AND parent_member_id IS NULL
91: AND sort_order < 999999;
92:
92:
93: CURSOR c_res_list_member_csr_3 IS
94: SELECT 'x'
95: FROM
96: pa_resource_list_members
97: WHERE resource_list_id = p_resource_list_id
98: AND parent_member_id IS NULL
99: AND alias = p_alias;
100:
133: organization_id = l_org_id ;
134:
135: CURSOR c_res_list_member_seq_csr IS
136: SELECT
137: pa_resource_list_members_s.NEXTVAL
138: FROM SYS.DUAL;
139:
140: l_err_code NUMBER := 0;
141: l_old_stack VARCHAR2(2000);
196: END IF;
197: CLOSE c_resource_types_csr;
198:
199:
200: p_err_stage := ' Select x from pa_resource_list_members';
201:
202: -- Check whether sort_order is unique
203: IF (p_sort_order IS NOT NULL AND p_sort_order > 0 ) THEN
204: OPEN c_res_list_member_csr_1;
212: CLOSE c_res_list_member_csr_1;
213: END IF;
214:
215: IF l_get_new_sort_order = 'TRUE' THEN
216: p_err_stage := ' Select max(sort_order) from pa_resource_list_members';
217: OPEN c_res_list_member_csr_2;
218: FETCH c_res_list_member_csr_2 INTO
219: l_sort_order;
220: CLOSE c_res_list_member_csr_2;
441: l_res_type_code;
442: CLOSE Cur_Txn_Attributes;
443:
444:
445: INSERT INTO pa_resource_list_members
446: (resource_list_id,
447: resource_list_member_id,
448: resource_id,
449: alias,
510: l_res_type_code
511: FROM
512: sys.dual
513: WHERE NOT EXISTS
514: (SELECT 'x' FROM PA_RESOURCE_LIST_MEMBERS
515: WHERE resource_list_id = p_resource_list_id
516: AND resource_id = l_resource_id
517: AND parent_member_id IS NULL );
518:
582: WHERE NAME = p_resource_list_name;
583:
584: CURSOR c_res_list_member_seq_csr IS
585: SELECT
586: pa_resource_list_members_s.NEXTVAL
587: FROM SYS.DUAL;
588:
589: CURSOR csr_get_formats IS
590: SELECT fmt.res_format_id, fmt.resource_class_id, cls.resource_class_code
854: p_err_code := l_err_code;
855: RETURN;
856: END IF;
857:
858: p_err_stage := 'Insert into pa_resource_list_members ';
859:
860: -- Following block of code is added for the resolution of bug 1889671
861:
862: OPEN Cur_Unclassified_Resource_List;
862: OPEN Cur_Unclassified_Resource_List;
863: FETCH Cur_Unclassified_Resource_List INTO l_resource_type_id , l_resource_type_code;
864: CLOSE Cur_Unclassified_Resource_List;
865:
866: INSERT INTO pa_resource_list_members
867: (resource_list_id,
868: resource_list_member_id,
869: resource_id,
870: alias,
882: created_by,
883: last_update_login )
884: VALUES (
885: l_resource_list_id,
886: pa_resource_list_members_s.NEXTVAL,
887: l_resource_id,
888: l_resource_name,
889: NULL,
890: 9999999,
943: * Bug - 3591751
944: * Desc - Defaulting the value of wp_eligible_flag
945: * to 'Y' while doing the insert.
946: ***********************************************/
947: INSERT INTO pa_resource_list_members
948: (resource_list_id,
949: resource_list_member_id,
950: resource_id,
951: alias,
974: unit_of_measure
975: )
976: VALUES (
977: l_resource_list_id,
978: pa_resource_list_members_s.NEXTVAL,
979: -99,
980: initcap(replace(l_res_class_code, '_', ' ')),
981: 'Y',
982: 'Y',
1085: where rla.resource_list_id = rl.resource_list_id);
1086:
1087: CURSOR chk_job_group_allow IS
1088: SELECT 'N'
1089: FROM pa_resource_list_members
1090: WHERE resource_list_id = p_resource_list_id
1091: AND job_id IS NOT NULL;
1092:
1093: CURSOR get_job_group_id IS
2534: CURSOR c_exp_categ_csr IS
2535: SELECT
2536: rta.expenditure_category
2537: FROM
2538: pa_resource_list_members rlm,
2539: pa_resources re,
2540: pa_resource_txn_attributes rta
2541: WHERE rlm.resource_list_member_id = p_parent_member_id
2542: AND rlm.resource_id = re.resource_id
2545: CURSOR c_rev_categ_csr IS
2546: SELECT
2547: rta.revenue_category
2548: FROM
2549: pa_resource_list_members rlm,
2550: pa_resources re,
2551: pa_resource_txn_attributes rta
2552: WHERE rlm.resource_list_member_id = p_parent_member_id
2553: AND rlm.resource_id = re.resource_id
2581:
2582: CURSOR c_res_list_member_csr_1 IS
2583: SELECT 'x'
2584: FROM
2585: pa_resource_list_members
2586: WHERE resource_list_id = p_resource_list_id
2587: AND parent_member_id = p_parent_member_id
2588: AND sort_order = p_sort_order;
2589:
2589:
2590: CURSOR c_res_list_member_csr_1a IS
2591: SELECT 'x'
2592: FROM
2593: pa_resource_list_members
2594: WHERE resource_list_id = p_resource_list_id
2595: AND parent_member_id IS NULL
2596: AND sort_order = p_sort_order;
2597:
2598: CURSOR c_res_list_member_csr_2 IS
2599: SELECT
2600: NVL(MAX(sort_order),0)+10
2601: FROM
2602: pa_resource_list_members
2603: WHERE resource_list_id = p_resource_list_id
2604: AND parent_member_id = p_parent_member_id
2605: AND sort_order < 999999;
2606:
2607: CURSOR c_res_list_member_csr_2a IS
2608: SELECT
2609: NVL(MAX(sort_order),0)+10
2610: FROM
2611: pa_resource_list_members
2612: WHERE resource_list_id = p_resource_list_id
2613: AND parent_member_id IS NULL
2614: AND sort_order < 999999;
2615:
2615:
2616: CURSOR c_res_list_member_csr_3 IS
2617: SELECT 'x'
2618: FROM
2619: pa_resource_list_members
2620: WHERE resource_list_id = p_resource_list_id
2621: AND parent_member_id = p_parent_member_id
2622: AND alias = p_alias;
2623:
2623:
2624: CURSOR c_res_list_member_csr_3a IS
2625: SELECT 'x'
2626: FROM
2627: pa_resource_list_members
2628: WHERE resource_list_id = p_resource_list_id
2629: AND parent_member_id IS NULL
2630: AND alias = p_alias;
2631:
2631:
2632: CURSOR c_res_list_member_csr_4 IS
2633: SELECT track_as_labor_flag
2634: FROM
2635: pa_resource_list_members
2636: WHERE resource_list_id = p_resource_list_id
2637: AND resource_list_member_id = p_parent_member_id;
2638:
2639: CURSOR c_res_list_member_seq_csr IS
2637: AND resource_list_member_id = p_parent_member_id;
2638:
2639: CURSOR c_res_list_member_seq_csr IS
2640: SELECT
2641: pa_resource_list_members_s.NEXTVAL
2642: FROM SYS.DUAL;
2643:
2644: --Cursor added for resolution of bug 1889671
2645:
2826: END IF;
2827: END IF;
2828: END IF;
2829:
2830: p_err_stage := ' Select x from pa_resource_list_members';
2831:
2832: IF (p_sort_order IS NULL OR p_sort_order = 0) THEN
2833: l_get_new_sort_order := 'TRUE';
2834: END IF;
2858: END IF;
2859: END IF;
2860:
2861: IF l_get_new_sort_order = 'TRUE' THEN
2862: p_err_stage := ' Select max(sort_order) from pa_resource_list_members';
2863: IF p_parent_member_id IS NULL THEN
2864: OPEN c_res_list_member_csr_2a;
2865: FETCH c_res_list_member_csr_2a INTO
2866: l_sort_order;
2880: END IF;
2881: -- Check whether alias is unique
2882:
2883: IF (p_alias IS NOT NULL ) THEN
2884: p_err_stage := ' Select x from pa_resource_list_members - alias';
2885: IF p_parent_member_id IS NULL THEN
2886: OPEN c_res_list_member_csr_3a;
2887: FETCH c_res_list_member_csr_3a INTO
2888: l_dummy;
2908: -- track_as_labor_flag;
2909:
2910: IF p_parent_member_id IS NOT NULL THEN
2911: p_err_stage :=
2912: 'Select track_as_labor_flag from pa_resource_list_members';
2913: OPEN c_res_list_member_csr_4;
2914: FETCH c_res_list_member_csr_4 INTO
2915: l_parent_track_as_labor_flag;
2916: IF c_res_list_member_csr_4%NOTFOUND THEN
3090: l_new_track_as_labor_flag := 'N';
3091: END IF;
3092:
3093: -- Need to generate the resource_list_member_id
3094: p_err_stage := 'Select pa_resource_list_members_s.nextval ';
3095:
3096: OPEN c_res_list_member_seq_csr;
3097: FETCH c_res_list_member_seq_csr INTO
3098: l_resource_list_member_id;
3102: ELSE
3103: CLOSE c_res_list_member_seq_csr;
3104: END IF;
3105:
3106: p_err_stage := 'Insert into pa_resource_list_members ';
3107:
3108: /*Changes done for Resource Mapping Enhancements */
3109:
3110: OPEN Cur_Txn_Attributes(l_resource_id);
3127: l_res_type_code;
3128: CLOSE Cur_Txn_Attributes;
3129:
3130:
3131: INSERT INTO pa_resource_list_members
3132: (resource_list_id,
3133: resource_list_member_id,
3134: resource_id,
3135: alias,
3235: RETURN;
3236: END IF;
3237:
3238: IF l_resource_list_member_id IS NULL THEN
3239: p_err_stage := 'Insert into pa_resource_list_members ';
3240:
3241: -- Following block of code is added for the resolution of bug 1889671
3242:
3243: OPEN Cur_Unclassified_Resource_List;
3243: OPEN Cur_Unclassified_Resource_List;
3244: FETCH Cur_Unclassified_Resource_List INTO l_resource_type_id , l_resource_type_code;
3245: CLOSE Cur_Unclassified_Resource_List;
3246:
3247: INSERT INTO pa_resource_list_members
3248: (resource_list_id,
3249: resource_list_member_id,
3250: resource_id,
3251: alias,
3264: last_update_login )
3265:
3266: VALUES (
3267: p_resource_list_id,
3268: pa_resource_list_members_s.NEXTVAL,
3269: l_resource_id,
3270: l_resource_name,
3271: p_parent_member_id,
3272: 999999,
3445: (select NULL
3446: from pa_resource_lists_tl T
3447: where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
3448:
3449: X_err_stage := 'Insert Into PA_RESOURCE_LIST_MEMBERS';
3450:
3451: INSERT INTO pa_resource_list_members (
3452: resource_list_member_id
3453: , resource_list_id
3447: where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
3448:
3449: X_err_stage := 'Insert Into PA_RESOURCE_LIST_MEMBERS';
3450:
3451: INSERT INTO pa_resource_list_members (
3452: resource_list_member_id
3453: , resource_list_id
3454: , resource_id
3455: , alias
3480: , INCURRED_BY_RES_FLAG
3481: , WP_ELIGIBLE_FLAG
3482: , UNIT_OF_MEASURE
3483: )
3484: SELECT pa_resource_list_members_s.NEXTVAL
3485: , X_resource_list_id
3486: , rlm.resource_id
3487: , rlm.alias
3488: , rlm.parent_member_id
3512: , 1 -- record version number
3513: , rlm.INCURRED_BY_RES_FLAG
3514: , rlm.WP_ELIGIBLE_FLAG
3515: , rlm.UNIT_OF_MEASURE
3516: FROM pa_resource_list_members rlm
3517: WHERE rlm.resource_list_id = eachRL.resource_list_id
3518: --begin:bug:5925973:Implementing the logic to restrict the creation of resource list member records when new OU ('Implementation Options' form of the OU)is created for an existing Business Group.
3519: --The Check:check whether the 'X_RESOURCE_LIST_ID' exists in the 'pa_resource_lists_all_bg' table before inserting records into 'pa_resource_list_members' table
3520: AND EXISTS
3515: , rlm.UNIT_OF_MEASURE
3516: FROM pa_resource_list_members rlm
3517: WHERE rlm.resource_list_id = eachRL.resource_list_id
3518: --begin:bug:5925973:Implementing the logic to restrict the creation of resource list member records when new OU ('Implementation Options' form of the OU)is created for an existing Business Group.
3519: --The Check:check whether the 'X_RESOURCE_LIST_ID' exists in the 'pa_resource_lists_all_bg' table before inserting records into 'pa_resource_list_members' table
3520: AND EXISTS
3521: (SELECT NULL
3522: FROM pa_resource_lists_all_bg T1
3523: WHERE T1.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
3546: x_msg_data OUT NOCOPY VARCHAR2) IS
3547:
3548: CURSOR get_members (p_res_list_id in NUMBER) IS
3549: SELECT resource_list_member_id
3550: FROM pa_resource_list_members
3551: WHERE resource_list_id = p_res_list_id;
3552:
3553: l_res_list_member_id NUMBER;
3554: l_err_code NUMBER;
3603: END LOOP;
3604: */
3605:
3606: -- Delete Planning resources from the list
3607: delete from pa_resource_list_members
3608: where resource_list_id = p_resource_list_id;
3609:
3610: -- Delete resource formats from the list
3611: delete from pa_plan_rl_formats
3639: BEGIN
3640: BEGIN
3641: SELECT 'N'
3642: INTO l_check_unique_res
3643: FROM pa_resource_list_members
3644: WHERE resource_list_id = p_resource_list_id
3645: AND alias = p_resource_alias
3646: AND resource_list_member_id <>
3647: nvl(p_resource_list_member_id,-99);
3790: END IF;
3791:
3792: /******************************************************
3793: * This select would check for the existance of recource
3794: * members in the pa_resource_list_members table which have the same
3795: * resource_list_id and project_id.
3796: * *****************************************************/
3797: BEGIN
3798: SELECT 'Y'
3798: SELECT 'Y'
3799: INTO l_exist_record
3800: FROM dual --For perf bug 4067435
3801: WHERE EXISTS (SELECT resource_list_id,object_id
3802: FROM pa_resource_list_members
3803: WHERE object_id = p_project_id
3804: AND object_type = 'PROJECT'
3805: AND resource_list_id = p_resource_list_id)
3806: AND ROWNUM = 1;
3815: x_return_status := FND_API.G_RET_STS_SUCCESS;
3816: RETURN;
3817: END IF;
3818: /******************************************************
3819: * Copying into the Pa_Resource_list_members table based
3820: * on existing values for the same resource_list_id.
3821: ******************************************************/
3822: /*****************************************************
3823: * Bug - 3591751
3820: * on existing values for the same resource_list_id.
3821: ******************************************************/
3822: /*****************************************************
3823: * Bug - 3591751
3824: * Desc - While inserting into the Pa_resource_list_members
3825: * insert value into the wp_eligible_flag as well.
3826: *****************************************************/
3827: /**********************************************************************
3828: * Bug - 3597011
3827: /**********************************************************************
3828: * Bug - 3597011
3829: * Desc - While inserting we need to check for enabled_flag <> N
3830: ***********************************************************************/
3831: INSERT INTO Pa_Resource_List_Members
3832: ( RESOURCE_LIST_MEMBER_ID,
3833: RESOURCE_LIST_ID,
3834: RESOURCE_ID,
3835: ALIAS,
3907: CREATION_DATE,
3908: CREATED_BY,
3909: LAST_UPDATE_LOGIN)
3910: SELECT
3911: pa_resource_list_members_s.NEXTVAL,
3912: a.RESOURCE_LIST_ID,
3913: a.RESOURCE_ID,
3914: a.ALIAS,
3915: a.DISPLAY_FLAG,
3985: SYSDATE,
3986: SYSDATE,
3987: FND_GLOBAL.USER_ID,
3988: FND_GLOBAL.LOGIN_ID
3989: FROM pa_resource_list_members a
3990: WHERE a.resource_list_id = p_resource_list_id
3991: AND a.object_id = p_resource_list_id
3992: AND a.object_type = 'RESOURCE_LIST'
3993: -- 3597011
4065: p_business_group_id PA_RESOURCE_LISTS_ALL_BG.business_group_id%TYPE;
4066: p_adw_notify_flag PA_RESOURCE_LISTS_ALL_BG.adw_notify_flag%TYPE;
4067: p_resource_list_type PA_RESOURCE_LISTS_ALL_BG.resource_list_type%TYPE;
4068: p_migration_code PA_RESOURCE_LISTS_ALL_BG.migration_code%TYPE;
4069: l_resource_list_member_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
4070: x_resource_list_id PA_RESOURCE_LISTS_ALL_BG.resource_list_id%TYPE;
4071: l_resource_list_id NUMBER:=NULL;
4072: --3596702
4073: l_res_list_member_id Number;
4326: Old_Element_Id,
4327: Old_Parent_Element_Id,
4328: New_Parent_Element_Id )
4329: (Select
4330: Pa_resource_list_members_S.NextVal,
4331: resource_list_member_id,
4332: Parent_member_Id,
4333: Null
4334: From Pa_resource_list_members
4330: Pa_resource_list_members_S.NextVal,
4331: resource_list_member_id,
4332: Parent_member_Id,
4333: Null
4334: From Pa_resource_list_members
4335: Where resource_list_id = p_parent_resource_list_id
4336: and (object_type = 'RESOURCE_LIST' OR object_type is NULL)
4337: --don't want to copy proj specific resources
4338: and enabled_flag <> 'N' );
4353: ***********************************************************************/
4354:
4355: -- hr_utility.trace('before copy members insert');
4356: -- hr_utility.trace('p_parent_resource_list_id is : ' || p_parent_resource_list_id);
4357: INSERT INTO pa_resource_list_members
4358: ( resource_list_member_id,
4359: RESOURCE_LIST_ID,
4360: RESOURCE_ID,
4361: ALIAS ,
4539: a.WP_ELIGIBLE_FLAG,
4540: -- Bug 3636926
4541: a.UNIT_OF_MEASURE
4542: --a.MIGRATED_RBS_ELEMENT_ID
4543: From Pa_resource_list_members a, Pa_Rbs_Elements_Temp Tmp
4544: Where Tmp.Old_Element_Id = a.resource_list_member_id;
4545:
4546: --End: Bug 3710189
4547: /*
4545:
4546: --End: Bug 3710189
4547: /*
4548: FROM
4549: pa_resource_list_members a
4550: WHERE
4551: a.resource_list_id=p_parent_resource_list_id
4552: AND
4553: a.object_type='RESOURCE_LIST'
4604: SELECT resource_list_id
4605: FROM pa_resource_list_assignments_v
4606: WHERE project_id = p_source_project_id;
4607:
4608: l_resource_list_id pa_resource_list_members.resource_list_id%TYPE;
4609: l_control_flag Varchar2(1);
4610:
4611: BEGIN
4612: X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
4658: IF l_control_flag <> 'Y' THEN
4659: BEGIN
4660: /************************************************
4661: * Insert resource list members into the
4662: * pa_resource_list_members table as those that
4663: * exist for the p_source_project_id.
4664: * The project_id should be the destination project id
4665: * and the resource_list_member_id should be the one from
4666: * the sequence.
4673: /**********************************************************************
4674: * Bug - 3597011
4675: * Desc - While inserting we need to check for enabled_flag <> N
4676: ***********************************************************************/
4677: INSERT INTO PA_RESOURCE_LIST_MEMBERS
4678: ( RESOURCE_LIST_MEMBER_ID,
4679: RESOURCE_LIST_ID,
4680: RESOURCE_ID,
4681: ALIAS,
4753: CREATION_DATE,
4754: CREATED_BY,
4755: LAST_UPDATE_LOGIN)
4756: SELECT
4757: pa_resource_list_members_s.NEXTVAL,
4758: l_resource_list_id,
4759: a.RESOURCE_ID,
4760: a.ALIAS,
4761: a.DISPLAY_FLAG,
4831: SYSDATE,
4832: SYSDATE,
4833: FND_GLOBAL.USER_ID,
4834: FND_GLOBAL.LOGIN_ID
4835: FROM pa_resource_list_members a
4836: WHERE a.resource_list_id = l_resource_list_id
4837: AND a.object_id = p_source_project_id
4838: AND a.object_type = 'PROJECT'
4839: -- 3597011
4841: AND
4842: (a.resource_id,a.res_format_id,NVL(a.alias,'XXX'))
4843: IN
4844: (SELECT resource_id,res_format_id,NVL(alias,'XXX')
4845: FROM pa_resource_list_members
4846: WHERE resource_list_id = l_resource_list_id
4847: AND object_id = p_source_project_id
4848: AND object_type = 'PROJECT'
4849: MINUS
4847: AND object_id = p_source_project_id
4848: AND object_type = 'PROJECT'
4849: MINUS
4850: SELECT resource_id,res_format_id,NVL(alias,'XXX')
4851: FROM pa_resource_list_members
4852: WHERE resource_list_id = l_resource_list_id
4853: AND object_id = p_destination_project_id
4854: AND object_type = 'PROJECT');
4855: