[Home] [Help]
PACKAGE BODY: APPS.PA_RES_LIST_UPGRADE_PKG
Source
1 package body PA_RES_LIST_UPGRADE_PKG as
2 /* $Header: PARTPLRB.pls 120.5 2008/07/24 10:13:52 vgovvala ship $ */
3
4 PROCEDURE RES_LIST_PLAN_LIST_WHEN_NULL( x_return_status OUT NOCOPY VARCHAR2, -- Added for 6710419
5 x_msg_count OUT NOCOPY NUMBER,
6 x_msg_data OUT NOCOPY VARCHAR2)
7
8 IS
9
10 cursor get_res_list_csr is
11 select RESOURCE_LIST_ID from pa_resource_lists where migration_code is null
12 and resource_list_id <> nvl(FND_PROFILE.VALUE('PA_FORECAST_RESOURCE_LIST'), -99);
13
14 tbl_res_id_list_tbl PA_PLSQL_DATATYPES.IdTabTyp;
15
16 l_count NUMBER;
17 l_debug_mode VARCHAR2(1);
18 l_module_name VARCHAR2(300) := 'PA_RES_LIST_UPGRADE_PKG' || 'RES_LIST_PLAN_LIST_WHEN_NULL';
19 l_debug_level5 CONSTANT NUMBER := 5;
20 l_data VARCHAR2(2000);
21 l_msg_index_out NUMBER;
22
23
24
25 BEGIN
26
27 x_msg_count := 0;
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
30
31 IF l_debug_mode = 'Y' THEN
32 pa_debug.set_curr_function( p_function => 'RES_LIST_PLAN_LIST_WHEN_NULL',
33 p_debug_mode => l_debug_mode );
34
35 END IF;
36
37 OPEN get_res_list_csr;
38 FETCH get_res_list_csr BULK COLLECT INTO tbl_res_id_list_tbl;
39 CLOSE get_res_list_csr;
40
41 IF (tbl_res_id_list_tbl.count > 0)
42 THEN
43 FOR l_count in tbl_res_id_list_tbl.first..tbl_res_id_list_tbl.last
44 LOOP
45
46 pa_res_list_upgrade_pkg.res_list_to_plan_res_list(
47 P_Resource_List_Id => tbl_res_id_list_tbl(l_count),
48 p_commit => 'T',
49 p_init_msg_list => 'T',
50 x_return_status => x_return_status,
51 x_msg_count => x_msg_count,
52 x_msg_data => x_msg_data );
53
54 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
55 IF l_debug_mode = 'Y' THEN
56 pa_debug.g_err_stage:='Error occured when processing the Resource List '||tbl_res_id_list_tbl(l_count);
57 pa_debug.write('Res_list_plan_list_when_null: ' || l_module_name,pa_debug.g_err_stage,5);
58 END IF;
59 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
60 END IF;
61
62 END LOOP;
63 ELSE
64 RETURN;
65
66 END IF;
67
68
69
70 EXCEPTION
71
72 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
73 ROLLBACK;
74
75 x_msg_count := FND_MSG_PUB.count_msg;
76 IF x_msg_count = 1 THEN
77 PA_INTERFACE_UTILS_PUB.get_messages
78 (p_encoded => FND_API.G_TRUE
79 ,p_msg_index => 1
80 ,p_msg_count => x_msg_count
81 ,p_msg_data => x_msg_data
82 ,p_data => l_data
83 ,p_msg_index_out => l_msg_index_out);
84
85 x_msg_data := l_data;
86 x_msg_count := x_msg_count;
87
88 ELSE
89
90 x_msg_count := x_msg_count;
91 END IF;
92 x_return_status := FND_API.G_RET_STS_ERROR;
93 IF l_debug_mode = 'Y' THEN
94 pa_debug.reset_curr_function;
95 END IF;
96
97
98 WHEN OTHERS THEN
99
100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
101 x_msg_count := 1;
102 x_msg_data := SQLERRM;
103 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_RES_LIST_UPGRADE_PKG'
104 ,p_procedure_name => 'RES_LIST_PLAN_LIST_WHEN_NULL');
105
106 IF l_debug_mode = 'Y' THEN
107 pa_debug.g_err_stage:='Error occured when processing the Resource List '||tbl_res_id_list_tbl(l_count);
108 pa_debug.write('Res_list_plan_list_when_null: ' || l_module_name,pa_debug.g_err_stage,5);
109 pa_debug.reset_curr_function;
110 END IF;
111 ROLLBACK;
112 RAISE;
113
114 END RES_LIST_PLAN_LIST_WHEN_NULL;
115
116 Procedure Res_List_To_Plan_Res_List(
117 P_Resource_List_Id IN pa_resource_lists_all_bg.resource_list_id%type,
118 p_commit IN VARCHAR2,
119 p_init_msg_list IN VARCHAR2,
120 X_Return_Status OUT NOCOPY Varchar2,
121 X_Msg_Count OUT NOCOPY Number,
122 X_Msg_Data OUT NOCOPY Varchar2) IS
123
124 cursor get_res_format_table_csr is
125 SELECT f.res_format_id,f.resource_class_id,
126 decode(f.group_res_type_code,'None',0,'REVENUE_CATEGORY',300,'EXPENDITURE_CATEGORY',
127 100, 'ORGANIZATION',200, -1 ) + decode(f.res_type_code , 'EMPLOYEE',1,
128 'EXPENDITURE_CATEGORY', 2, 'EXPENDITURE_TYPE', 3, 'JOB',4,'ORGANIZATION',
129 5, 'REVENUE_CATEGORY',6,'EVENT_TYPE',7,'VENDOR', 8, 'UNCATEGORIZED',9,-1 ) +
130 decode(nvl(f.labor_flag,'N'),'Y',0,15) map_seq
131 FROM pa_restype_map_to_resformat f
132 ORDER BY map_seq;
133
134 cursor res_class_csr is
135 SELECT fmt.resource_class_id,cls.resource_class_code,fmt.res_format_id,
136 def.spread_curve_id, def.etc_method_code, def.mfc_cost_type_id,
137 def.object_id,def.object_type,b.res_type_code
138 FROM pa_res_formats_b fmt, pa_resource_classes_b cls,pa_plan_res_defaults def,
139 pa_res_types_b b
140 WHERE fmt.resource_class_flag = 'Y'
141 AND fmt.resource_class_id = cls.resource_class_id
142 AND def.resource_class_id = cls.resource_class_id
143 AND fmt.res_type_id = b.res_type_id
144 AND fmt.res_type_enabled_flag = 'Y'
145 AND def.object_type = 'CLASS';
146
147 cursor get_res_list_id_csr(c_res_list_id pa_resource_lists_all_bg.resource_list_id%type) is
148 SELECT b.resource_list_id,b.group_resource_type_id,b.uncategorized_flag,
149 b.last_update_login, b.creation_date, b.created_by, b.last_update_date,
150 b.last_updated_by, b.name,t.resource_type_code,
151 decode(nvl(t.resource_type_code,'None'),'None',0, 'REVENUE_CATEGORY',300,
152 'EXPENDITURE_CATEGORY',100, 'ORGANIZATION',200, 0 ) grp_seq
153 FROM pa_resource_lists_all_bg b, pa_resource_types t
154 WHERE t.resource_type_id(+) = b.group_resource_type_id and
155 b.resource_list_id = c_res_list_id and
156 EXISTS ( select 'X' from pa_resource_list_members m
157 WHERE m.res_format_id is null and m.resource_list_id = b.resource_list_id
158 OR b.uncategorized_flag = 'Y');
159
160 cursor get_res_list_mem_id_csr(l_res_list_id pa_resource_lists_all_bg.resource_list_id%type) is
161 SELECT m.resource_list_member_id,m.resource_type_id,m.parent_member_id,
162 m.organization_id,m.revenue_category, m.expenditure_category,m.expenditure_type,
163 decode(rl.uncategorized_flag,'Y',9,decode(nvl(t.resource_type_code,'None'),'None',0 , 'EMPLOYEE',1, 'EXPENDITURE_CATEGORY',
164 2, 'EXPENDITURE_TYPE', 3, 'JOB',4,'ORGANIZATION',5, 'REVENUE_CATEGORY',6,'EVENT_TYPE',
165 7,'VENDOR', 8, 'UNCATEGORIZED',9,-1 )) + decode(nvl(track_as_labor_flag,'N'),'Y',0,15) res_seq,t.resource_type_code
166 FROM pa_resource_list_members m,pa_resource_types t,
167 pa_resource_lists_all_bg rl
168 WHERE m.resource_list_id = l_res_list_id
169 AND m.resource_type_id = t.resource_type_id(+)
170 AND m.res_format_id is null
171 AND NVL(t.resource_type_code,-99) not in ('UNCLASSIFIED', 'PROJECT_ROLE', 'HZ_PARTY')
172 AND rl.resource_list_id = m.resource_list_id
173 ORDER BY m.parent_member_id desc;
174
175 cursor get_alias_csr is
176 SELECT t.name,t.resource_class_id
177 FROM pa_resource_classes_tl t,pa_resource_classes_b c
178 WHERE t.resource_class_id = c.resource_class_id
179 AND language = userenv('LANG');
180
181 cursor res_list_form_exists_csr(c_res_list_id pa_resource_lists_all_bg.resource_list_id%type,c_res_for_id pa_res_formats_b.res_format_id%type) is
182 SELECT 'Y'
183 FROM pa_plan_rl_formats
184 WHERE resource_list_id = c_res_list_id
185 AND res_format_id = c_res_for_id;
186
187 cursor res_list_exists_csr(c_res_list_id pa_resource_lists_all_bg.resource_list_id%type) is
188 SELECT 'Y'
189 FROM pa_resource_lists_all_bg
190 WHERE resource_list_id = c_res_list_id
191 AND migration_code = 'M';
192
193 cursor chk_res_for_exists_csr(c_res_list_id pa_resource_lists_all_bg.resource_list_id%type) is
194 SELECT 'Y'
195 FROM pa_resource_lists_all_bg b
196 WHERE resource_list_id = c_res_list_id
197 AND exists ( select 'Y' from pa_resource_list_members m
198 WHERE m.resource_list_id = b.resource_list_id and
199 m.res_format_id is not null);
200
201 TYPE res_class_csr_tbl is table of res_class_csr%ROWTYPE
202 index by binary_integer;
203 l_res_class_csr_tbl res_class_csr_tbl;
204
205 TYPE res_alias_tbl is table of pa_resource_classes_tl.name%type
206 index by binary_integer;
207 l_res_alias_tbl res_alias_tbl;
208
209 TYPE res_class_id_tbl is table of pa_resource_classes_b.resource_class_id%TYPE
210 index by binary_integer;
211 l_res_class_id_tbl res_class_id_tbl;
212
213 TYPE res_class_flag_tbl is table of pa_res_formats_b.resource_class_flag%TYPE
214 index by binary_integer;
215 l_res_class_flag_tbl res_class_flag_tbl;
216
217 TYPE res_for_id_tbl is TABLE of pa_res_formats_b.res_format_id%type
218 index by binary_integer;
219 l_res_for_id_tbl res_for_id_tbl;
220
221 TYPE rev_cat_tbl is table of pa_resource_list_members.revenue_category%type
222 index by binary_integer;
223 l_rev_cat_tbl rev_cat_tbl;
224
225 TYPE exp_cat_tbl is table of pa_resource_list_members.expenditure_category%type
226 index by binary_integer;
227 l_exp_cat_tbl exp_cat_tbl;
228
229 TYPE org_id_tbl is table of pa_resource_list_members.organization_id%type
230 index by binary_integer;
231 l_org_id_tbl org_id_tbl;
232
233 TYPE res_format_tbl is table of pa_resource_list_members.res_format_id%type
234 index by binary_integer;
235 l_res_format_tbl res_format_tbl;
236
237 l_res_format_id_exists varchar2(1) := 'N';
238 l_stage VARCHAR2(240) :='';
239 l_res_format_id pa_res_formats_b.res_format_id%type;
240 l_org_id pa_resource_list_members.organization_id%type;
241 l_rev_cat pa_resource_list_members.revenue_category%type;
242 l_exp_cat pa_resource_list_members.expenditure_category%type;
243 l_res_class_id pa_resource_list_members.resource_class_id%type;
244 l_res_class_flag pa_resource_list_members.resource_class_flag%type;
245 l_res_class_code pa_resource_list_members.resource_class_code%type;
246 l_etc_method_code pa_resource_list_members.etc_method_code%TYPE;
247 l_spread_curve_id pa_resource_list_members.spread_curve_id%TYPE;
248 l_object_id pa_resource_list_members.object_id%TYPE;
249 l_object_type pa_resource_list_members.object_type%TYPE;
250 --l_mfc_cost_type_id pa_resource_list_members.mfc_cost_type_id%TYPE;
251 l_fc_res_type_code pa_resource_list_members.fc_res_type_code%type;
252 l_inventory_item_id pa_resource_list_members.inventory_item_id%TYPE := NULL;
253 l_item_category_id pa_resource_list_members.item_category_id%TYPE := NULL;
254 l_migration_code pa_resource_list_members.migration_code%TYPE := NULL;
255 g_last_updated_by pa_resource_list_members.last_updated_by%TYPE:= FND_GLOBAL.USER_ID;
256 g_last_update_date pa_resource_list_members.last_update_date%TYPE := SYSDATE;
257 g_creation_date pa_resource_list_members.creation_date%TYPE := SYSDATE;
258 g_created_by pa_resource_list_members.last_update_login%TYPE := FND_GLOBAL.USER_ID;
259 g_last_update_login pa_resource_list_members.last_update_login%TYPE := FND_GLOBAL.USER_ID;
260 l_resource_id pa_resource_list_members.resource_id%TYPE :=-99;
261 l_incur_by_res_flag pa_resource_list_members.incurred_by_res_flag%TYPE := 'N';
262 l_incur_by_res_code pa_resource_list_members.incur_by_res_class_code%TYPE := NULL;
263 l_incur_by_role_id pa_resource_list_members.incur_by_role_id%TYPE := NULL;
264 l_track_as_labor_flag pa_resource_list_members.track_as_labor_flag%TYPE := NULL;
265 l_alias pa_resource_list_members.alias%TYPE := NULL;
266 l_indx NUMBER;
267 l_indx_count NUMBER;
268 l_res_list_exists_flag varchar2(1) :='N';
269 l_res_list_form_exists varchar2(1) :='N';
270 l_wp_eligible_flag varchar2(1) ;
271 l_uom varchar2(30);
272 res_list_upgraded EXCEPTION;
273 null_res_list EXCEPTION;
274 null_res_class_id EXCEPTION;
275 null_res_class_code EXCEPTION;
276 null_res_format_id EXCEPTION;
277 null_alias EXCEPTION;
278 PRAGMA EXCEPTION_INIT(null_res_class_id,-20103);
279 PRAGMA EXCEPTION_INIT(null_res_class_code,-20104);
280 PRAGMA EXCEPTION_INIT(null_res_format_id,-20105);
281 PRAGMA EXCEPTION_INIT(null_alias,-20106);
282 PRAGMA EXCEPTION_INIT(res_list_upgraded,-20101);
283 PRAGMA EXCEPTION_INIT(null_res_list,-20102);
284 l_debug_mode varchar2(30);
285 l_module_name VARCHAR2(100):= 'pa.plsql.pa_res_list_upgrade_pkg';
286 l_msg_index_out NUMBER;
287 l_data VARCHAR2(2000);
288 l_msg_data VARCHAR2(2000);
289 l_msg_count number;
290 l_return_status varchar2(1);
291
292 l_res_alias varchar2(80);
293 l_unique_alias varchar2(1);
294 l_exists_alias_id number := Null;
295 l_first_alias_id number := Null;
296 l_concat_no number := 0;
297 l_alias_concat varchar2(30):= Null;
298 l_updated_alias pa_resource_list_members.alias%TYPE := NULL;
299 l_res_parent_alias varchar2(80);
300 l_parent_member_id number := Null;
301
302 l_enabled_flag varchar2(1); -- bug 3682103
303
304 begin
305
306 --dbms_output.put_line('R1');
307 --hr_utility.trace_on(NULL, 'RMUPG');
308 --hr_utility.trace('begin');
309
310 IF (P_Resource_List_Id IS NULL)
311 THEN
312 PA_RES_LIST_UPGRADE_PKG.RES_LIST_PLAN_LIST_WHEN_NULL(x_return_status => X_Return_Status,
313 x_msg_count => X_Msg_Count,
314 x_msg_data => X_Msg_Data);
315
316 ELSE
317
318
319 savepoint pa_res_list_upgrade_pkg;
320
321 -- Bug 3802762, 30-JUN-2004, jwhite ------------------------------------
322 -- Added if/then test for p_init_msg_list
323
324
325 IF FND_API.TO_BOOLEAN( p_init_msg_list )
326 THEN
327
328 FND_MSG_PUB.initialize;
329
330 END IF;
331
332 -- End Bug 3802762 -----------------------------------------------------
333
334 x_msg_count := 0;
335 x_return_status := FND_API.G_RET_STS_SUCCESS;
336 pa_debug.init_err_stack('PA_RES_LIST_UPGRADE_PKG.RES_LIST_TO_PLAN_RES_LIST');
337 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
338 l_debug_mode := NVL(l_debug_mode, 'Y');
339 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
340 --dbms_output.put_line('R2');
341 IF l_debug_mode = 'Y' THEN
342 pa_debug.g_err_stage := 'Entered Resource List to Plan Resource List Upgrade';
343 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
344
345 pa_debug.g_err_stage := 'Checking for valid parameters';
346 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
347 END IF;
348 -- Table for storing mapping code for each Resource Format Id
349 if l_debug_mode = 'Y' THEN
350 pa_debug.g_err_stage := 'Mapping code for res format id into PL/SQL Table';
351 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
352 end if;
353 for l_get_res_format_table_csr in get_res_format_table_csr
354 loop
355 l_res_for_id_tbl(l_get_res_format_table_csr.map_seq) := l_get_res_format_table_csr.res_format_id;
356 l_res_class_id_tbl(l_get_res_format_table_csr.res_format_id) := l_get_res_format_table_csr.resource_class_id;
357 end loop;
358
359 --dbms_output.put_line('R3');
360 -- Table for storing class realted information for each Resource Class Id
361 if l_debug_mode = 'Y' THEN
362 pa_debug.g_err_stage := 'Resource Class Atrributes into PL/SQL Table';
363 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
364 end if;
365 for l_res_class_csr in res_class_csr
366 loop
367 l_res_class_csr_tbl(l_res_class_csr.resource_class_id).resource_class_code := l_res_class_csr.resource_class_code;
368 l_res_class_csr_tbl(l_res_class_csr.resource_class_id).res_format_id := l_res_class_csr.res_format_id;
369 l_res_class_csr_tbl(l_res_class_csr.resource_class_id).spread_curve_id := l_res_class_csr.spread_curve_id;
370 l_res_class_csr_tbl(l_res_class_csr.resource_class_id).etc_method_code := l_res_class_csr.etc_method_code;
371 l_res_class_csr_tbl(l_res_class_csr.resource_class_id).mfc_cost_type_id :=l_res_class_csr.mfc_cost_type_id;
372 l_res_class_csr_tbl(l_res_class_csr.resource_class_id).object_id :=l_res_class_csr.object_id;
373 l_res_class_csr_tbl(l_res_class_csr.resource_class_id).object_type :=l_res_class_csr.object_type;
374 l_res_class_csr_tbl(l_res_class_csr.resource_class_id).res_type_code :=l_res_class_csr.res_type_code;
375 end loop;
376
377 --dbms_output.put_line('R4');
378
379 -- Table for storing alias for each Resource Class Id
380 for l_get_alias_csr in get_alias_csr
381 loop
382 l_res_alias_tbl(l_get_alias_csr.resource_class_id) := l_get_alias_csr.name;
383 end loop;
384
385
386 open chk_res_for_exists_csr(p_resource_list_id);
387 fetch chk_res_for_exists_csr into l_res_format_id_exists;
388 close chk_res_for_exists_csr;
389
390 if (l_res_format_id_exists = 'Y') then
391 return;
392 --raise res_list_upgraded;
393 end if;
394 if (p_resource_list_id is null) then
395 IF l_debug_mode = 'Y' THEN
396 pa_debug.g_err_stage := 'Resource List='||to_char(p_resource_list_id);
397 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
398 END IF;
399 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
400 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
401 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
402 end if;
403
404 -- Check if the in parameter is not null
405 --dbms_output.put_line('R5');
406
407 -- Loop for Resource List Header
408 for l_get_res_list_id_csr in get_res_list_id_csr(p_resource_list_id)
409 loop
410
411 -- Loop for Resource List Member
412 for l_get_res_list_mem_id_csr in get_res_list_mem_id_csr(l_get_res_list_id_csr.resource_list_id)
413 loop
414 --dbms_output.put_line ('L1');
415
416 l_res_format_id := NULL;
417 l_res_class_id := NULL;
418 l_res_class_code := NULL;
419 l_etc_method_code :=NULL;
420 l_spread_curve_id :=NULL;
421 l_object_id :=NULL;
422 l_object_type :=NULL;
423 --l_mfc_cost_type_id :=NULL;
424 l_migration_code :=NULL;
425 l_org_id := NULL;
426 l_rev_cat := NULL;
427 l_exp_cat := NULL;
428 l_alias := NULL;
429 l_wp_eligible_flag := NULL;
430 l_uom := NULL;
431 l_enabled_flag := Null; -- bug 3682103
432
433
434 IF l_debug_mode = 'Y' THEN
435 pa_debug.g_err_stage := 'Entering Item No 1';
436 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
437 end if;
438 -- ITEM NO 1
439 if (l_get_res_list_mem_id_csr.parent_member_id is null) then
440 if(l_res_for_id_tbl.exists(l_get_res_list_mem_id_csr.res_seq)) then
441 l_res_format_id := l_res_for_id_tbl(l_get_res_list_mem_id_csr.res_seq);
442 end if;
443 else
444 if(l_res_for_id_tbl.exists(l_get_res_list_id_csr.grp_seq + l_get_res_list_mem_id_csr.res_seq)) then
445 l_res_format_id := l_res_for_id_tbl(l_get_res_list_id_csr.grp_seq +
446 l_get_res_list_mem_id_csr.res_seq );
447 end if;
448 end if;
449
450
451 IF l_debug_mode = 'Y' THEN
452 pa_debug.g_err_stage := 'Entering Item No 2';
453 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
454 end if;
455 -- ITEM NO 2
456 -- Get expenditure,revenue,organization of the parent and copy to its children
457 if (l_get_res_list_mem_id_csr.parent_member_id is null) then
458 l_rev_cat := l_get_res_list_mem_id_csr.revenue_category;
459 l_exp_cat := l_get_res_list_mem_id_csr.expenditure_category;
460 l_org_id := l_get_res_list_mem_id_csr.organization_id;
461 l_rev_cat_tbl(l_get_res_list_mem_id_csr.resource_list_member_id) := l_get_res_list_mem_id_csr.revenue_category;
462 l_exp_cat_tbl(l_get_res_list_mem_id_csr.resource_list_member_id) := l_get_res_list_mem_id_csr.expenditure_category;
463 l_org_id_tbl(l_get_res_list_mem_id_csr.resource_list_member_id) := l_get_res_list_mem_id_csr.organization_id;
464 else
465 if ( l_rev_cat_tbl.exists(l_get_res_list_mem_id_csr.parent_member_id)) then
466 l_rev_cat := l_rev_cat_tbl(l_get_res_list_mem_id_csr.parent_member_id);
467 end if;
468 if ( l_exp_cat_tbl.exists(l_get_res_list_mem_id_csr.parent_member_id)) then
469 l_exp_cat := l_exp_cat_tbl(l_get_res_list_mem_id_csr.parent_member_id);
470 end if;
471 if ( l_org_id_tbl.exists(l_get_res_list_mem_id_csr.parent_member_id)) then
472 l_org_id := l_org_id_tbl(l_get_res_list_mem_id_csr.parent_member_id);
473 end if;
474 end if;
475
476
477
478 IF l_debug_mode = 'Y' THEN
479 pa_debug.g_err_stage := 'Entering Item No 3';
480 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
481 end if;
482 -- ITEM NO 3
483 -- Store resource format id into a table to get all distinct resource format Id's
484 if (l_res_format_id is not null) then
485 l_res_format_tbl(l_res_format_id) := l_res_format_id;
486 end if;
487
488 -- Get resource class id and resource class code for each resource format id
489 if(l_res_class_id_tbl.exists(l_res_format_id)) then
490 l_res_class_id := l_res_class_id_tbl(l_res_format_id);
491 end if;
492
493 --dbms_output.put_line('l_res_class_id :' || l_res_class_id);
494 --dbms_output.put_line('l_res_format_id :' || l_res_format_id);
495
496 if (l_res_class_csr_tbl.exists(l_res_class_id)) then
497 l_res_class_code := l_res_class_csr_tbl(l_res_class_id).resource_class_code;
498 end if;
499
500 IF l_debug_mode = 'Y' THEN
501 pa_debug.g_err_stage := 'Entering Item No 5';
502 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
503 end if;
504 -- ITEM NO 5
505 -- Get Resource class related Information for each Resource Class Id
506 if(l_res_class_csr_tbl.exists(l_res_class_id)) then
507 l_object_id := l_res_class_csr_tbl(l_res_class_id).object_id;
508 l_object_type := l_res_class_csr_tbl(l_res_class_id).object_type;
509 l_spread_curve_id := l_res_class_csr_tbl(l_res_class_id).spread_curve_id;
510 l_etc_method_code := l_res_class_csr_tbl(l_res_class_id).etc_method_code;
511 -- l_mfc_cost_type_id := l_res_class_csr_tbl(l_res_class_id).mfc_cost_type_id;
512 end if;
513
514 if(l_get_res_list_id_csr.uncategorized_flag = 'Y') then
515 l_res_class_id := 4;
516 l_res_class_code := 'FINANCIAL_ELEMENTS';
517 end if;
518
519 if(l_get_res_list_id_csr.resource_type_code in ('REVENUE_CATEGORY', 'EXPENDITURE_CATEGORY')) then
520
521 -- Begin bug 3682103
522 if (l_get_res_list_mem_id_csr.resource_type_code in ('EVENT_TYPE',
523 'EXPENDITURE_CATEGORY',
524 'EXPENDITURE_TYPE',
525 'REVENUE_CATEGORY') And
526 l_get_res_list_mem_id_csr.parent_member_id IS NOT NULL ) then
527 l_fc_res_type_code := l_get_res_list_mem_id_csr.resource_type_code;
528
529 If ((l_get_res_list_id_csr.resource_type_code = 'REVENUE_CATEGORY' AND
530 l_get_res_list_mem_id_csr.resource_type_code = 'EXPENDITURE_CATEGORY') OR
531 (l_get_res_list_id_csr.resource_type_code = 'EXPENDITURE_CATEGORY' AND
532 l_get_res_list_mem_id_csr.resource_type_code = 'REVENUE_CATEGORY') OR
533 (l_get_res_list_id_csr.resource_type_code = 'EXPENDITURE_CATEGORY' AND
534 l_get_res_list_mem_id_csr.resource_type_code = 'EVENT_TYPE')) THEN
535 l_enabled_flag := 'N';
536 End If;
537 l_exp_cat := Null;
538 l_rev_cat := Null;
539 -- end bug 3682103
540 else
541 l_fc_res_type_code := l_get_res_list_id_csr.resource_type_code;
542 end if;
543
544 else
545
546 if (l_get_res_list_mem_id_csr.resource_type_code in ('EVENT_TYPE',
547 'EXPENDITURE_CATEGORY',
548 'EXPENDITURE_TYPE',
549 'REVENUE_CATEGORY')) Then
550 l_fc_res_type_code := l_get_res_list_mem_id_csr.resource_type_code;
551 else
552 l_fc_res_type_code := null;
553 end if;
554
555 end if;
556 if(nvl(l_fc_res_type_code,'') = 'REVENUE_CATEGORY' or nvl(l_fc_res_type_code,'') = 'EVENT_TYPE') then
557 l_wp_eligible_flag := 'N';
558 else
559 l_wp_eligible_flag := 'Y';
560 end if;
561
562 if (l_res_format_id is null) then
563 l_stage := 'Resource Format Id is null for the resource list' || l_get_res_list_id_csr.resource_list_id;
564 RAISE null_res_format_id;
565 end if;
566 if (l_res_class_id is null) then
567 l_stage := 'Resource Class Id is null';
568 RAISE null_res_class_id;
569 end if;
570 if (l_res_class_code is null) then
571 l_stage := 'Resource Class Code is null';
572 RAISE null_res_class_code;
573 end if;
574
575 if l_res_class_code = 'PEOPLE' THEN
576 l_uom := 'HOURS';
577 elsif l_res_class_code = 'FINANCIAL_ELEMENTS' THEN
578 IF l_get_res_list_mem_id_csr.expenditure_type IS NOT NULL THEN
579 SELECT unit_of_measure
580 INTO l_uom
581 FROM pa_expenditure_types et
582 WHERE et.expenditure_type = l_get_res_list_mem_id_csr.expenditure_type
583 AND ROWNUM = 1;
584 else
585 l_uom := 'DOLLARS';
586 end if;
587 end if;
588
589 SELECT nvl(incurred_by_enabled_flag, 'N')
590 INTO l_incur_by_res_flag
591 FROM pa_res_formats_b
592 WHERE res_format_id = l_res_format_id;
593
594 IF l_debug_mode = 'Y' THEN
595 pa_debug.g_err_stage := 'Updating pa_resource_list_members table';
596 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
597 end if;
598 -- Update each resource list member with the info obtained above
599 update pa_resource_list_members
600 set res_format_id = l_res_format_id,
601 revenue_category = nvl(l_rev_cat,l_get_res_list_mem_id_csr.revenue_category),
602 expenditure_category = nvl(l_exp_cat,l_get_res_list_mem_id_csr.expenditure_category),
603 organization_id = nvl(l_org_id,l_get_res_list_mem_id_csr.organization_id),
604 resource_class_id = l_res_class_id,
605 resource_class_code = l_res_class_code,
606 spread_curve_id = l_spread_curve_id,
607 mfc_cost_type_id = NULL,
608 etc_method_code = l_etc_method_code,
609 resource_class_flag = decode(l_get_res_list_id_csr.uncategorized_flag,'Y','Y','N'),
610 object_id = l_get_res_list_id_csr.resource_list_id,
611 object_type = 'RESOURCE_LIST',
612 inventory_item_id = null,
613 item_category_id = null,
614 migration_code = 'M',
615 fc_res_type_code = l_fc_res_type_code,
616 wp_eligible_flag = l_wp_eligible_flag,
617 unit_of_measure = l_uom,
618 incurred_by_res_flag = l_incur_by_res_flag,
619 record_version_number = 1,
620 enabled_flag = nvl(l_enabled_flag, enabled_flag)
621 where resource_list_member_id = l_get_res_list_mem_id_csr.resource_list_member_id;
622
623 IF l_debug_mode = 'Y' THEN
624 pa_debug.g_err_stage := 'Get alias and parent_member_id from pa_resource_list_members';
625 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
626 end if;
627
628 Select
629 alias,
630 parent_member_id
631 Into
632 l_res_alias,
633 l_parent_member_id
634 from
635 pa_resource_list_members
636 where
637 resource_list_member_id = l_get_res_list_mem_id_csr.resource_list_member_id;
638
639 IF l_debug_mode = 'Y' THEN
640 pa_debug.g_err_stage := 'Check if alias is unique by calling Pa_Planning_Resource_Pvt.Check_pl_alias_unique()';
641 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
642 end if;
643 l_unique_alias := Pa_Planning_Resource_Pvt.Check_pl_alias_unique(
644 p_resource_list_id => l_get_res_list_id_csr.resource_list_id,
645 p_resource_alias => l_res_alias,
646 p_resource_list_member_id => l_get_res_list_mem_id_csr.resource_list_member_id,
647 p_object_type => 'RESOURCE_LIST',
648 p_object_id => l_get_res_list_id_csr.resource_list_id);
649
650 IF l_debug_mode = 'Y' THEN
651 pa_debug.g_err_stage := 'Alias is unique ' || l_unique_alias ;
652 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
653 end if;
654 If l_unique_alias = 'N' Then
655
656 IF l_debug_mode = 'Y' THEN
657 pa_debug.g_err_stage := 'Get parent alias';
658 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
659 end if;
660 select
661 alias
662 Into
663 l_res_parent_alias
664 from
665 pa_resource_list_members
666 where
667 resource_list_member_id = l_parent_member_id;
668
669 IF l_debug_mode = 'Y' THEN
670 pa_debug.g_err_stage := 'Concatenate the parent and child alias and substr to 80 characters.';
671 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
672 end if;
673 l_res_alias := substr(l_res_parent_alias || ' - ' || l_res_alias,1,80);
674
675 IF l_debug_mode = 'Y' THEN
676 pa_debug.g_err_stage := 'Update the pa_resource_list_members record with the combined alias';
677 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
678 end if;
679 update pa_resource_list_members
680 set alias = l_res_alias
681 where resource_list_member_id = l_get_res_list_mem_id_csr.resource_list_member_id;
682
683 End If;
684
685 end loop;
686 -- Resource List Member Loop ends here
687 --dbms_output.put_line('R10');
688
689 IF l_debug_mode = 'Y' THEN
690 pa_debug.g_err_stage := 'Entering Item No 4';
691 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
692 end if;
693 --ITEM N0 4
694 -- For each resource list header create new four class resource list members
695 l_indx_count := l_res_class_csr_tbl.count;
696 l_indx := l_res_class_csr_tbl.first;
697 l_res_list_exists_flag := 'N';
698 open res_list_exists_csr(l_get_res_list_id_csr.resource_list_id);
699 fetch res_list_exists_csr into l_res_list_exists_flag;
700 close res_list_exists_csr;
701
702 --dbms_output.put_line('R10.1');
703
704 if (l_res_list_exists_flag = 'N') then
705 while(l_indx_count > 0)
706 loop
707 if not((upper(l_get_res_list_id_csr.uncategorized_flag) like 'Y' and
708 (l_res_class_csr_tbl(l_indx).resource_class_code like 'FINANCIAL_ELEMENTS'))) then
709 if(l_res_class_csr_tbl(l_indx).resource_class_code like 'PEOPLE') then
710 l_track_as_labor_flag := 'Y';
711 else
712 l_track_as_labor_flag := 'N';
713 end if;
714 --dbms_output.put_line('R10.2: l_res_class_id: ' || l_res_class_id);
715 --dbms_output.put_line('R10.2: l_indx: ' || l_indx);
716 if (l_res_alias_tbl.exists(l_indx)) then
717 l_alias := l_res_alias_tbl(l_indx);
718 end if;
719 --dbms_output.put_line('R10.3');
720 if (l_alias is null) then
721 --dbms_output.put_line('R10.4');
722 l_stage := 'ALIAS IS NULL';
723 RAISE null_alias;
724 end if;
725 IF l_debug_mode = 'Y' THEN
726 pa_debug.g_err_stage := 'Inserting into pa_resource_list_members table';
727 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
728 end if;
729
730
731 -- Bug 4766426 - check for duplicate alias
732 -- of these resources.
733 -- Added a loop to avoid duplicate of alias
734 -- if already existing in the database.
735 l_first_alias_id := NULL;
736 l_exists_alias_id := NULL;
737 l_concat_no := 0;
738 l_alias_concat := NULL;
739 l_updated_alias := l_alias;
740
741 LOOP
742
743 BEGIN
744 SELECT resource_list_member_id
745 INTO l_exists_alias_id
746 FROM pa_resource_list_members
747 WHERE alias = l_updated_alias||l_alias_concat
748 AND resource_list_id =
749 l_get_res_list_id_csr.resource_list_id
750 AND object_type = 'RESOURCE_LIST'
751 AND object_id =
752 l_get_res_list_id_csr.resource_list_id;
753
754 EXCEPTION
755 WHEN NO_DATA_FOUND THEN
756 l_updated_alias := l_updated_alias||l_alias_concat;
757 EXIT;
758 END;
759
760 IF l_exists_alias_id IS NOT NULL THEN
761 IF l_first_alias_id IS NULL THEN
762 l_first_alias_id := l_exists_alias_id;
763 END IF;
764 l_concat_no := l_concat_no + 1;
765 l_alias_concat := '-'||l_concat_no;
766 END IF;
767
768 END LOOP;
769
770
771 IF l_exists_alias_id IS NOT NULL THEN
772 UPDATE pa_resource_list_members
773 SET alias = l_updated_alias
774 WHERE resource_list_member_id =
775 l_first_alias_id;
776 END IF;
777 /* End of fix for bug 4766426 */
778 --dbms_output.put_line('R10.5');
779
780 insert into pa_resource_list_members(
781 RESOURCE_LIST_MEMBER_ID,
782 RESOURCE_LIST_ID,
783 RESOURCE_ID,
784 ALIAS,
785 PARENT_MEMBER_ID,
786 SORT_ORDER,
787 MEMBER_LEVEL,
788 DISPLAY_FLAG,
789 ENABLED_FLAG,
790 TRACK_AS_LABOR_FLAG,
791 LAST_UPDATED_BY,
792 LAST_UPDATE_DATE,
793 CREATION_DATE,
794 CREATED_BY,
795 LAST_UPDATE_LOGIN,
796 OBJECT_TYPE,
797 OBJECT_ID,
798 RESOURCE_CLASS_ID,
799 RESOURCE_CLASS_CODE,
800 RES_FORMAT_ID,
801 SPREAD_CURVE_ID,
802 ETC_METHOD_CODE,
803 MFC_COST_TYPE_ID,
804 RESOURCE_CLASS_FLAG,
805 INVENTORY_ITEM_ID,
806 ITEM_CATEGORY_ID,
807 MIGRATION_CODE ,
808 INCURRED_BY_RES_FLAG,
809 INCUR_BY_RES_CLASS_CODE,
810 INCUR_BY_ROLE_ID,
811 RES_TYPE_CODE,
812 PERSON_ID ,
813 JOB_ID ,
814 ORGANIZATION_ID,
815 VENDOR_ID ,
816 EXPENDITURE_TYPE ,
817 EVENT_TYPE ,
818 NON_LABOR_RESOURCE ,
819 EXPENDITURE_CATEGORY,
820 REVENUE_CATEGORY ,
821 NON_LABOR_RESOURCE_ORG_ID ,
822 EVENT_TYPE_CLASSIFICATION ,
823 SYSTEM_LINKAGE_FUNCTION ,
824 WP_ELIGIBLE_FLAG,
825 UNIT_OF_MEASURE,
826 PROJECT_ROLE_ID,
827 RECORD_VERSION_NUMBER)
828 (select pa_resource_list_members_s.NEXTVAL,
829 l_get_res_list_id_csr.resource_list_id,
830 l_resource_id,
831 l_alias,
832 null,
833 1,
834 1,
835 'Y',
836 'Y',
837 l_track_as_labor_flag,
838 g_last_updated_by,
839 g_last_update_date,
840 g_creation_date,
841 g_created_by,
842 g_last_update_login,
843 'RESOURCE_LIST',
844 l_get_res_list_id_csr.resource_list_id,
845 l_indx,
846 l_res_class_csr_tbl(l_indx).resource_class_code,
847 l_res_class_csr_tbl(l_indx).res_format_id,
848 l_res_class_csr_tbl(l_indx).spread_curve_id ,
849 l_res_class_csr_tbl(l_indx).etc_method_code ,
850 NULL, --l_res_class_csr_tbl(l_indx).mfc_cost_type_id,
851 'Y',
852 l_inventory_item_id,
853 l_item_category_id ,
854 'N',
855 'N', --l_incur_by_res_flag,
856 l_incur_by_res_code,
857 l_incur_by_role_id,
858 l_res_class_csr_tbl(l_indx).res_type_code ,
859 null,
860 null,
861 null,
862 null,
863 null,
864 null,
865 null,
866 null,
867 null,
868 null,
869 null,
870 null,
871 'Y',
872 decode(l_res_class_csr_tbl(l_indx).resource_class_code,
873 'PEOPLE','HOURS',
874 'FINANCIAL_ELEMENTS','DOLLARS',
875 'MATERIAL_ITEMS','DOLLARS',
876 'EQUIPMENT','HOURS'),
877 null,
878 1
879 from dual);
880
881 --dbms_output.put_line('R10.6');
882 end if;
883 if ( l_res_class_csr_tbl(l_indx).res_format_id is not null) then
884 l_res_format_tbl(l_res_class_csr_tbl(l_indx).res_format_id) := l_res_class_csr_tbl(l_indx).res_format_id;
885 end if;
886 l_indx_count := l_indx_count - 1;
887 l_indx := l_res_class_csr_tbl.next(l_indx);
888 end loop;
889 end if;
890 -- Item No 4 Loop ends here
891
892 --dbms_output.put_line('R11');
893
894 IF l_debug_mode = 'Y' THEN
895 pa_debug.g_err_stage := 'Entering Item No 7';
896 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
897 end if;
898 --ITEM NO 7
899 -- Insert into this table with distinct resource_list_id and resource_format_id's
900 IF l_debug_mode = 'Y' THEN
901 pa_debug.g_err_stage := 'Inserting into pa_plan_rl_formats table';
902 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
903 end if;
904 if (l_res_format_tbl.count > 0) then
905 for i in l_res_format_tbl.first..l_res_format_tbl.last
906 loop
907 if (l_res_format_tbl.exists(i)) then
908 l_res_list_form_exists := 'N';
909 open res_list_form_exists_csr(l_get_res_list_id_csr.resource_list_id,l_res_format_tbl(i));
910 fetch res_list_form_exists_csr into l_res_list_form_exists;
911 close res_list_form_exists_csr;
912 if (l_res_list_form_exists = 'N' ) then
913 insert into pa_plan_rl_formats (
914 PLAN_RL_FORMAT_ID,
915 RESOURCE_LIST_ID,
916 RES_FORMAT_ID ,
917 LAST_UPDATE_DATE,
918 LAST_UPDATED_BY ,
919 CREATION_DATE ,
920 CREATED_BY ,
921 LAST_UPDATE_LOGIN,
922 RECORD_VERSION_NUMBER
923 )
924 select
925 pa_plan_rl_formats_s.nextval,
926 l_get_res_list_id_csr.resource_list_id,
927 l_res_format_tbl(i) ,
928 g_last_update_date,
929 g_last_updated_by ,
930 g_creation_date ,
931 g_created_by ,
932 g_last_update_login,
933 1
934 from dual;
935 end if;
936 end if;
937 end loop;
938 -- Item No 7 loop ends here.
939 l_res_format_tbl.delete;
940 end if;
941
942 IF l_debug_mode = 'Y' THEN
943 pa_debug.g_err_stage := 'Entering Item No 8';
944 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
945 end if;
946 --ITEM NO 9
947
948 IF l_debug_mode = 'Y' THEN
949 pa_debug.g_err_stage := 'Updating pa_resource_lists_all_bg table';
950 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL3);
951 end if;
952 -- Update migration code for each resource list header
953 update pa_resource_lists_all_bg
954 set control_flag = 'Y',
955 use_for_wp_flag = 'Y',
956 migration_code = 'M',
957 record_version_number = 1
958 where resource_list_id = l_get_res_list_id_csr.resource_list_id;
959
960 PA_PLANNING_RESOURCE_UTILS.POPULATE_LIST_INTO_TL(
961 p_resource_list_id => l_get_res_list_id_csr.resource_list_id,
962 x_return_status => l_return_status,
963 x_msg_count => l_msg_count,
964 x_msg_data => l_msg_data
965 );
966 if (x_return_status <> 'S') then
967 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
968 end if;
969 PA_RBS_UTILS.UPGRADE_LIST_TO_RBS(
970 p_resource_list_id => l_get_res_list_id_csr.resource_list_id,
971 x_return_status => l_return_status,
972 x_msg_count => l_msg_count,
973 x_msg_data => l_msg_data
974 );
975 if (x_return_status <> 'S') then
976 raise PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
977 end if;
978
979
980 -- Bug 3802762, 30-JUN-2004, jwhite ------------------------------------
981 -- Added if/then test for p_commit.
982
983 IF FND_API.TO_BOOLEAN( p_commit )
984 THEN
985 COMMIT;
986 END IF;
987
988
989 -- End Bug 3802762, 30-JUN-2004, jwhite --------------------------------
990
991
992
993
994 end loop;
995 END IF; -- P_Resource_List_Id IS NULL
996 -- Resource List Header Loop ends
997 --hr_utility.trace('end');
998 --hr_utility.trace('x_return_status is ' || x_return_status);
999 --dbms_output.put_line('R12');
1000
1001
1002 EXCEPTION
1003 WHEN null_res_format_id OR null_res_class_id OR null_res_class_code OR null_alias THEN
1004 l_msg_count := FND_MSG_PUB.count_msg;
1005 IF l_msg_count = 1 THEN
1006 PA_INTERFACE_UTILS_PUB.get_messages
1007 (p_encoded => FND_API.G_TRUE
1008 ,p_msg_index => 1
1009 ,p_msg_count => l_msg_count
1010 ,p_msg_data => l_msg_data
1011 ,p_data => l_data
1012 ,p_msg_index_out => l_msg_index_out);
1013 x_msg_data := l_data;
1014 x_msg_count := l_msg_count;
1015 ELSE
1016 x_msg_count := l_msg_count;
1017 x_msg_data := l_msg_data;
1018 END IF;
1019
1020 IF l_debug_mode = 'Y' THEN
1021 pa_debug.g_err_stage:= l_stage;
1022 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1023 END IF;
1024 x_return_status:= FND_API.G_RET_STS_ERROR;
1025 pa_debug.write_file('RES_LIST_TO_PLAN_RES_LIST: Upgrade has failed for the resource list '|| p_resource_list_id,5);
1026 pa_debug.write_file('RES_LIST_TO_PLAN_RES_LIST:: Failure Reason:'||x_msg_data,5);
1027 pa_debug.reset_err_stack;
1028 ROLLBACK to PA_RES_LIST_UPGRADE_PKG;
1029 RAISE;
1030 rollback;
1031 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc or RES_LIST_UPGRADED THEN
1032 l_msg_count := FND_MSG_PUB.count_msg;
1033 IF l_msg_count = 1 THEN
1034 PA_INTERFACE_UTILS_PUB.get_messages
1035 (p_encoded => FND_API.G_TRUE
1036 ,p_msg_index => 1
1037 ,p_msg_count => l_msg_count
1038 ,p_msg_data => l_msg_data
1039 ,p_data => l_data
1040 ,p_msg_index_out => l_msg_index_out);
1041 x_msg_data := l_data;
1042 x_msg_count := l_msg_count;
1043 ELSE
1044 x_msg_count := l_msg_count;
1045 x_msg_data := l_msg_data;
1046 END IF;
1047
1048 IF l_debug_mode = 'Y' THEN
1049 pa_debug.g_err_stage:='Invalid Arguments Passed';
1050 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1051 END IF;
1052 x_return_status:= FND_API.G_RET_STS_ERROR;
1053 pa_debug.write_file('RES_LIST_TO_PLAN_RES_LIST: Upgrade has failed for the resource list '|| p_resource_list_id,5);
1054 pa_debug.write_file('RES_LIST_TO_PLAN_RES_LIST:: Failure Reason:'||x_msg_data,5);
1055 pa_debug.reset_err_stack;
1056 ROLLBACK to PA_RES_LIST_UPGRADE_PKG;
1057 RAISE;
1058 WHEN OTHERS THEN
1059 if (get_res_format_table_csr%ISOPEN) then
1060 close get_res_format_table_csr;
1061 end if;
1062
1063 if (res_class_csr%ISOPEN) then
1064 close res_class_csr;
1065 end if;
1066
1067 if (get_res_list_id_csr%ISOPEN) then
1068 close get_res_list_id_csr;
1069 end if;
1070
1071 if (get_res_list_mem_id_csr%ISOPEN) then
1072 close get_res_list_mem_id_csr;
1073 end if;
1074
1075 if (get_alias_csr%ISOPEN) then
1076 close get_alias_csr;
1077 end if;
1078
1079 if (res_list_form_exists_csr%ISOPEN) then
1080 close res_list_form_exists_csr;
1081 end if;
1082
1083 if (res_list_exists_csr%ISOPEN) then
1084 close res_list_exists_csr;
1085 end if;
1086
1087 if (chk_res_for_exists_csr%ISOPEN) then
1088 close chk_res_for_exists_csr;
1089 end if;
1090
1091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1092 x_msg_count := 1;
1093 x_msg_data := SQLERRM;
1094
1095 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_RES_LIST_UPGRADE_PKG',p_procedure_name => 'RES_LIST_TO_PLAN_RES_LIST');
1096 IF l_debug_mode = 'Y' THEN
1097 pa_debug.g_err_stage:='Unexpected Error'||SQLERRM;
1098 pa_debug.write(l_module_name,pa_debug.g_err_stage,PA_FP_CONSTANTS_PKG.G_DEBUG_LEVEL5);
1099 END IF;
1100
1101 pa_debug.write_file('RES_LIST_TO_PLAN_RES_LIST: Upgrade has failed for the resource list '||p_resource_list_id,5);
1102 pa_debug.write_file('RES_LIST_TO_PLAN_RES_LIST: Failure Reason:'||pa_debug.G_Err_Stack,5);
1103 pa_debug.reset_err_stack;
1104 ROLLBACK to PA_RES_LIST_UPGRADE_PKG;
1105 RAISE;
1106 end RES_LIST_TO_PLAN_RES_LIST;
1107
1108 END PA_RES_LIST_UPGRADE_PKG;