[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;
442: l_res_type_code;
443: CLOSE Cur_Txn_Attributes;
444:
445:
446: INSERT INTO pa_resource_list_members
447: (resource_list_id,
448: resource_list_member_id,
449: resource_id,
450: alias,
511: l_res_type_code
512: FROM
513: sys.dual
514: WHERE NOT EXISTS
515: (SELECT 'x' FROM PA_RESOURCE_LIST_MEMBERS
516: WHERE resource_list_id = p_resource_list_id
517: AND resource_id = l_resource_id
518: AND parent_member_id IS NULL );
519:
585: WHERE NAME = p_resource_list_name;
586:
587: CURSOR c_res_list_member_seq_csr IS
588: SELECT
589: pa_resource_list_members_s.NEXTVAL
590: FROM SYS.DUAL;
591:
592: CURSOR csr_get_formats IS
593: SELECT fmt.res_format_id, fmt.resource_class_id, cls.resource_class_code
870: p_err_code := l_err_code;
871: RETURN;
872: END IF;
873:
874: p_err_stage := 'Insert into pa_resource_list_members ';
875:
876: -- Following block of code is added for the resolution of bug 1889671
877:
878: OPEN Cur_Unclassified_Resource_List;
878: OPEN Cur_Unclassified_Resource_List;
879: FETCH Cur_Unclassified_Resource_List INTO l_resource_type_id , l_resource_type_code;
880: CLOSE Cur_Unclassified_Resource_List;
881:
882: INSERT INTO pa_resource_list_members
883: (resource_list_id,
884: resource_list_member_id,
885: resource_id,
886: alias,
898: created_by,
899: last_update_login )
900: VALUES (
901: l_resource_list_id,
902: pa_resource_list_members_s.NEXTVAL,
903: l_resource_id,
904: l_resource_name,
905: NULL,
906: 9999999,
963: * to 'Y' while doing the insert.
964: ***********************************************/
965:
966:
967: INSERT INTO pa_resource_list_members
968: (resource_list_id,
969: resource_list_member_id,
970: resource_id,
971: alias,
994: unit_of_measure
995: )
996: VALUES (
997: l_resource_list_id,
998: pa_resource_list_members_s.NEXTVAL,
999: -99,
1000: initcap(replace(l_res_class_code, '_', ' ')),
1001: 'Y',
1002: 'Y',
1063: * Bug - 3591751
1064: * Desc - Defaulting the value of wp_eligible_flag
1065: * to 'Y' while doing the insert.
1066: ***********************************************/
1067: INSERT INTO pa_resource_list_members
1068: (resource_list_id,
1069: resource_list_member_id,
1070: resource_id,
1071: alias,
1095: expenditure_type
1096: )
1097: VALUES (
1098: l_resource_list_id,
1099: pa_resource_list_members_s.NEXTVAL,
1100: -99,
1101: 'Project Resource',
1102: 'Y',
1103: 'Y',
1211: where rla.resource_list_id = rl.resource_list_id);
1212:
1213: CURSOR chk_job_group_allow IS
1214: SELECT 'N'
1215: FROM pa_resource_list_members
1216: WHERE resource_list_id = p_resource_list_id
1217: AND job_id IS NOT NULL;
1218:
1219: CURSOR get_job_group_id IS
1530:
1531: -- New Planning Resource Lists
1532: -- Add four resources - one for each class.
1533:
1534: INSERT INTO pa_resource_list_members
1535: (resource_list_id,
1536: resource_list_member_id,
1537: resource_id,
1538: alias,
1561: unit_of_measure
1562: )
1563: VALUES (
1564: p_resource_list_id,
1565: pa_resource_list_members_s.NEXTVAL,
1566: -99,
1567: initcap(replace(l_res_class_code, '_', ' ')),
1568: 'Y',
1569: 'Y',
1617:
1618: -- New Planning Resource Lists
1619: -- Add four resources - one for each class.
1620:
1621: INSERT INTO pa_resource_list_members
1622: (resource_list_id,
1623: resource_list_member_id,
1624: resource_id,
1625: alias,
1648: unit_of_measure
1649: )
1650: VALUES (
1651: p_resource_list_id,
1652: pa_resource_list_members_s.NEXTVAL,
1653: -99,
1654: 'Project Resource',
1655: 'Y',
1656: 'Y',
2882: CURSOR c_exp_categ_csr IS
2883: SELECT
2884: rta.expenditure_category
2885: FROM
2886: pa_resource_list_members rlm,
2887: pa_resources re,
2888: pa_resource_txn_attributes rta
2889: WHERE rlm.resource_list_member_id = p_parent_member_id
2890: AND rlm.resource_id = re.resource_id
2893: CURSOR c_rev_categ_csr IS
2894: SELECT
2895: rta.revenue_category
2896: FROM
2897: pa_resource_list_members rlm,
2898: pa_resources re,
2899: pa_resource_txn_attributes rta
2900: WHERE rlm.resource_list_member_id = p_parent_member_id
2901: AND rlm.resource_id = re.resource_id
2929:
2930: CURSOR c_res_list_member_csr_1 IS
2931: SELECT 'x'
2932: FROM
2933: pa_resource_list_members
2934: WHERE resource_list_id = p_resource_list_id
2935: AND parent_member_id = p_parent_member_id
2936: AND sort_order = p_sort_order;
2937:
2937:
2938: CURSOR c_res_list_member_csr_1a IS
2939: SELECT 'x'
2940: FROM
2941: pa_resource_list_members
2942: WHERE resource_list_id = p_resource_list_id
2943: AND parent_member_id IS NULL
2944: AND sort_order = p_sort_order;
2945:
2946: CURSOR c_res_list_member_csr_2 IS
2947: SELECT
2948: NVL(MAX(sort_order),0)+10
2949: FROM
2950: pa_resource_list_members
2951: WHERE resource_list_id = p_resource_list_id
2952: AND parent_member_id = p_parent_member_id
2953: AND sort_order < 999999;
2954:
2955: CURSOR c_res_list_member_csr_2a IS
2956: SELECT
2957: NVL(MAX(sort_order),0)+10
2958: FROM
2959: pa_resource_list_members
2960: WHERE resource_list_id = p_resource_list_id
2961: AND parent_member_id IS NULL
2962: AND sort_order < 999999;
2963:
2963:
2964: CURSOR c_res_list_member_csr_3 IS
2965: SELECT 'x'
2966: FROM
2967: pa_resource_list_members
2968: WHERE resource_list_id = p_resource_list_id
2969: AND parent_member_id = p_parent_member_id
2970: AND alias = p_alias;
2971:
2971:
2972: CURSOR c_res_list_member_csr_3a IS
2973: SELECT 'x'
2974: FROM
2975: pa_resource_list_members
2976: WHERE resource_list_id = p_resource_list_id
2977: AND parent_member_id IS NULL
2978: AND alias = p_alias;
2979:
2979:
2980: CURSOR c_res_list_member_csr_4 IS
2981: SELECT track_as_labor_flag
2982: FROM
2983: pa_resource_list_members
2984: WHERE resource_list_id = p_resource_list_id
2985: AND resource_list_member_id = p_parent_member_id;
2986:
2987: CURSOR c_res_list_member_seq_csr IS
2985: AND resource_list_member_id = p_parent_member_id;
2986:
2987: CURSOR c_res_list_member_seq_csr IS
2988: SELECT
2989: pa_resource_list_members_s.NEXTVAL
2990: FROM SYS.DUAL;
2991:
2992: --Cursor added for resolution of bug 1889671
2993:
3174: END IF;
3175: END IF;
3176: END IF;
3177:
3178: p_err_stage := ' Select x from pa_resource_list_members';
3179:
3180: IF (p_sort_order IS NULL OR p_sort_order = 0) THEN
3181: l_get_new_sort_order := 'TRUE';
3182: END IF;
3206: END IF;
3207: END IF;
3208:
3209: IF l_get_new_sort_order = 'TRUE' THEN
3210: p_err_stage := ' Select max(sort_order) from pa_resource_list_members';
3211: IF p_parent_member_id IS NULL THEN
3212: OPEN c_res_list_member_csr_2a;
3213: FETCH c_res_list_member_csr_2a INTO
3214: l_sort_order;
3229: END IF;
3230: -- Check whether alias is unique
3231:
3232: IF (p_alias IS NOT NULL ) THEN
3233: p_err_stage := ' Select x from pa_resource_list_members - alias';
3234: IF p_parent_member_id IS NULL THEN
3235: OPEN c_res_list_member_csr_3a;
3236: FETCH c_res_list_member_csr_3a INTO
3237: l_dummy;
3257: -- track_as_labor_flag;
3258:
3259: IF p_parent_member_id IS NOT NULL THEN
3260: p_err_stage :=
3261: 'Select track_as_labor_flag from pa_resource_list_members';
3262: OPEN c_res_list_member_csr_4;
3263: FETCH c_res_list_member_csr_4 INTO
3264: l_parent_track_as_labor_flag;
3265: IF c_res_list_member_csr_4%NOTFOUND THEN
3439: l_new_track_as_labor_flag := 'N';
3440: END IF;
3441:
3442: -- Need to generate the resource_list_member_id
3443: p_err_stage := 'Select pa_resource_list_members_s.nextval ';
3444:
3445: OPEN c_res_list_member_seq_csr;
3446: FETCH c_res_list_member_seq_csr INTO
3447: l_resource_list_member_id;
3451: ELSE
3452: CLOSE c_res_list_member_seq_csr;
3453: END IF;
3454:
3455: p_err_stage := 'Insert into pa_resource_list_members ';
3456:
3457: /*Changes done for Resource Mapping Enhancements */
3458:
3459: OPEN Cur_Txn_Attributes(l_resource_id);
3476: l_res_type_code;
3477: CLOSE Cur_Txn_Attributes;
3478:
3479:
3480: INSERT INTO pa_resource_list_members
3481: (resource_list_id,
3482: resource_list_member_id,
3483: resource_id,
3484: alias,
3584: RETURN;
3585: END IF;
3586:
3587: IF l_resource_list_member_id IS NULL THEN
3588: p_err_stage := 'Insert into pa_resource_list_members ';
3589:
3590: -- Following block of code is added for the resolution of bug 1889671
3591:
3592: OPEN Cur_Unclassified_Resource_List;
3592: OPEN Cur_Unclassified_Resource_List;
3593: FETCH Cur_Unclassified_Resource_List INTO l_resource_type_id , l_resource_type_code;
3594: CLOSE Cur_Unclassified_Resource_List;
3595:
3596: INSERT INTO pa_resource_list_members
3597: (resource_list_id,
3598: resource_list_member_id,
3599: resource_id,
3600: alias,
3613: last_update_login )
3614:
3615: VALUES (
3616: p_resource_list_id,
3617: pa_resource_list_members_s.NEXTVAL,
3618: l_resource_id,
3619: l_resource_name,
3620: p_parent_member_id,
3621: 999999,
3794: (select NULL
3795: from pa_resource_lists_tl T
3796: where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
3797:
3798: X_err_stage := 'Insert Into PA_RESOURCE_LIST_MEMBERS';
3799:
3800: INSERT INTO pa_resource_list_members (
3801: resource_list_member_id
3802: , resource_list_id
3796: where T.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
3797:
3798: X_err_stage := 'Insert Into PA_RESOURCE_LIST_MEMBERS';
3799:
3800: INSERT INTO pa_resource_list_members (
3801: resource_list_member_id
3802: , resource_list_id
3803: , resource_id
3804: , alias
3829: , INCURRED_BY_RES_FLAG
3830: , WP_ELIGIBLE_FLAG
3831: , UNIT_OF_MEASURE
3832: )
3833: SELECT pa_resource_list_members_s.NEXTVAL
3834: , X_resource_list_id
3835: , rlm.resource_id
3836: , rlm.alias
3837: , rlm.parent_member_id
3861: , 1 -- record version number
3862: , rlm.INCURRED_BY_RES_FLAG
3863: , rlm.WP_ELIGIBLE_FLAG
3864: , rlm.UNIT_OF_MEASURE
3865: FROM pa_resource_list_members rlm
3866: WHERE rlm.resource_list_id = eachRL.resource_list_id
3867: --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.
3868: --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
3869: AND EXISTS
3864: , rlm.UNIT_OF_MEASURE
3865: FROM pa_resource_list_members rlm
3866: WHERE rlm.resource_list_id = eachRL.resource_list_id
3867: --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.
3868: --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
3869: AND EXISTS
3870: (SELECT NULL
3871: FROM pa_resource_lists_all_bg T1
3872: WHERE T1.RESOURCE_LIST_ID = X_RESOURCE_LIST_ID);
3895: x_msg_data OUT NOCOPY VARCHAR2) IS
3896:
3897: CURSOR get_members (p_res_list_id in NUMBER) IS
3898: SELECT resource_list_member_id
3899: FROM pa_resource_list_members
3900: WHERE resource_list_id = p_res_list_id;
3901:
3902: l_res_list_member_id NUMBER;
3903: l_err_code NUMBER;
3952: END LOOP;
3953: */
3954:
3955: -- Delete Planning resources from the list
3956: delete from pa_resource_list_members
3957: where resource_list_id = p_resource_list_id;
3958:
3959: -- Delete resource formats from the list
3960: delete from pa_plan_rl_formats
3988: BEGIN
3989: BEGIN
3990: SELECT 'N'
3991: INTO l_check_unique_res
3992: FROM pa_resource_list_members
3993: WHERE resource_list_id = p_resource_list_id
3994: AND alias = p_resource_alias
3995: AND resource_list_member_id <>
3996: nvl(p_resource_list_member_id,-99);
4139: END IF;
4140:
4141: /******************************************************
4142: * This select would check for the existance of recource
4143: * members in the pa_resource_list_members table which have the same
4144: * resource_list_id and project_id.
4145: * *****************************************************/
4146: BEGIN
4147: SELECT 'Y'
4147: SELECT 'Y'
4148: INTO l_exist_record
4149: FROM dual --For perf bug 4067435
4150: WHERE EXISTS (SELECT resource_list_id,object_id
4151: FROM pa_resource_list_members
4152: WHERE object_id = p_project_id
4153: AND object_type = 'PROJECT'
4154: AND resource_list_id = p_resource_list_id)
4155: AND ROWNUM = 1;
4164: x_return_status := FND_API.G_RET_STS_SUCCESS;
4165: RETURN;
4166: END IF;
4167: /******************************************************
4168: * Copying into the Pa_Resource_list_members table based
4169: * on existing values for the same resource_list_id.
4170: ******************************************************/
4171: /*****************************************************
4172: * Bug - 3591751
4169: * on existing values for the same resource_list_id.
4170: ******************************************************/
4171: /*****************************************************
4172: * Bug - 3591751
4173: * Desc - While inserting into the Pa_resource_list_members
4174: * insert value into the wp_eligible_flag as well.
4175: *****************************************************/
4176: /**********************************************************************
4177: * Bug - 3597011
4176: /**********************************************************************
4177: * Bug - 3597011
4178: * Desc - While inserting we need to check for enabled_flag <> N
4179: ***********************************************************************/
4180: INSERT INTO Pa_Resource_List_Members
4181: ( RESOURCE_LIST_MEMBER_ID,
4182: RESOURCE_LIST_ID,
4183: RESOURCE_ID,
4184: ALIAS,
4257: CREATED_BY,
4258: LAST_UPDATE_LOGIN,
4259: expenditure_type_2) --Added for Bug13654538
4260: SELECT
4261: pa_resource_list_members_s.NEXTVAL,
4262: a.RESOURCE_LIST_ID,
4263: a.RESOURCE_ID,
4264: a.ALIAS,
4265: a.DISPLAY_FLAG,
4336: SYSDATE,
4337: FND_GLOBAL.USER_ID,
4338: FND_GLOBAL.LOGIN_ID,
4339: expenditure_type_2 --Added for Bug13654538
4340: FROM pa_resource_list_members a
4341: WHERE a.resource_list_id = p_resource_list_id
4342: AND a.object_id = p_resource_list_id
4343: AND a.object_type = 'RESOURCE_LIST'
4344: -- 3597011
4417: p_business_group_id PA_RESOURCE_LISTS_ALL_BG.business_group_id%TYPE;
4418: p_adw_notify_flag PA_RESOURCE_LISTS_ALL_BG.adw_notify_flag%TYPE;
4419: p_resource_list_type PA_RESOURCE_LISTS_ALL_BG.resource_list_type%TYPE;
4420: p_migration_code PA_RESOURCE_LISTS_ALL_BG.migration_code%TYPE;
4421: l_resource_list_member_id PA_RESOURCE_LIST_MEMBERS.RESOURCE_LIST_MEMBER_ID%TYPE;
4422: x_resource_list_id PA_RESOURCE_LISTS_ALL_BG.resource_list_id%TYPE;
4423: l_resource_list_id NUMBER:=NULL;
4424: --3596702
4425: l_res_list_member_id Number;
4679: Old_Element_Id,
4680: Old_Parent_Element_Id,
4681: New_Parent_Element_Id )
4682: (Select
4683: Pa_resource_list_members_S.NextVal,
4684: resource_list_member_id,
4685: Parent_member_Id,
4686: Null
4687: From Pa_resource_list_members
4683: Pa_resource_list_members_S.NextVal,
4684: resource_list_member_id,
4685: Parent_member_Id,
4686: Null
4687: From Pa_resource_list_members
4688: Where resource_list_id = p_parent_resource_list_id
4689: and (object_type = 'RESOURCE_LIST' OR object_type is NULL)
4690: --don't want to copy proj specific resources
4691: and enabled_flag <> 'N' );
4706: ***********************************************************************/
4707:
4708: -- hr_utility.trace('before copy members insert');
4709: -- hr_utility.trace('p_parent_resource_list_id is : ' || p_parent_resource_list_id);
4710: INSERT INTO pa_resource_list_members
4711: ( resource_list_member_id,
4712: RESOURCE_LIST_ID,
4713: RESOURCE_ID,
4714: ALIAS ,
4894: -- Bug 3636926
4895: a.UNIT_OF_MEASURE,
4896: --a.MIGRATED_RBS_ELEMENT_ID
4897: a.EXPENDITURE_TYPE_2 --Added for Bug13655396
4898: From Pa_resource_list_members a, Pa_Rbs_Elements_Temp Tmp
4899: Where Tmp.Old_Element_Id = a.resource_list_member_id;
4900:
4901: --End: Bug 3710189
4902: /*
4900:
4901: --End: Bug 3710189
4902: /*
4903: FROM
4904: pa_resource_list_members a
4905: WHERE
4906: a.resource_list_id=p_parent_resource_list_id
4907: AND
4908: a.object_type='RESOURCE_LIST'
4959: SELECT resource_list_id
4960: FROM pa_resource_list_assignments_v
4961: WHERE project_id = p_source_project_id;
4962:
4963: l_resource_list_id pa_resource_list_members.resource_list_id%TYPE;
4964: l_control_flag Varchar2(1);
4965:
4966: BEGIN
4967: X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
5013: IF l_control_flag <> 'Y' THEN
5014: BEGIN
5015: /************************************************
5016: * Insert resource list members into the
5017: * pa_resource_list_members table as those that
5018: * exist for the p_source_project_id.
5019: * The project_id should be the destination project id
5020: * and the resource_list_member_id should be the one from
5021: * the sequence.
5028: /**********************************************************************
5029: * Bug - 3597011
5030: * Desc - While inserting we need to check for enabled_flag <> N
5031: ***********************************************************************/
5032: INSERT INTO PA_RESOURCE_LIST_MEMBERS
5033: ( RESOURCE_LIST_MEMBER_ID,
5034: RESOURCE_LIST_ID,
5035: RESOURCE_ID,
5036: ALIAS,
5108: CREATION_DATE,
5109: CREATED_BY,
5110: LAST_UPDATE_LOGIN)
5111: SELECT
5112: pa_resource_list_members_s.NEXTVAL,
5113: l_resource_list_id,
5114: a.RESOURCE_ID,
5115: a.ALIAS,
5116: a.DISPLAY_FLAG,
5186: SYSDATE,
5187: SYSDATE,
5188: FND_GLOBAL.USER_ID,
5189: FND_GLOBAL.LOGIN_ID
5190: FROM pa_resource_list_members a
5191: WHERE a.resource_list_id = l_resource_list_id
5192: AND a.object_id = p_source_project_id
5193: AND a.object_type = 'PROJECT'
5194: -- 3597011
5196: AND
5197: (a.resource_id,a.res_format_id,NVL(a.alias,'XXX'))
5198: IN
5199: (SELECT resource_id,res_format_id,NVL(alias,'XXX')
5200: FROM pa_resource_list_members
5201: WHERE resource_list_id = l_resource_list_id
5202: AND object_id = p_source_project_id
5203: AND object_type = 'PROJECT'
5204: MINUS
5202: AND object_id = p_source_project_id
5203: AND object_type = 'PROJECT'
5204: MINUS
5205: SELECT resource_id,res_format_id,NVL(alias,'XXX')
5206: FROM pa_resource_list_members
5207: WHERE resource_list_id = l_resource_list_id
5208: AND object_id = p_destination_project_id
5209: AND object_type = 'PROJECT');
5210: