[Home] [Help]
PACKAGE BODY: APPS.PSP_UPGRADE_EFF_REPORTS
Source
1 PACKAGE BODY PSP_UPGRADE_EFF_REPORTS AS
2 /*$Header: PSPERUPB.pls 120.7 2006/09/28 21:29:18 vdharmap noship $*/
3
4 PROCEDURE MIGRATE_EFF_REPORTS(
5 errBuf OUT NOCOPY VARCHAR2,
6 retCode OUT NOCOPY VARCHAR2,
7 p_diagnostic_mode IN VARCHAR2,
8 p_ignore_appr IN VARCHAR2,
9 p_ignore_da IN VARCHAR2,
10 p_element_set_name IN VARCHAR2
11 ) IS
12
13 l_period_name VARCHAR2(80);
14 l_deleted BOOLEAN := TRUE;
15 l_appr_exists NUMBER :=0 ;
16 l_pending_da NUMBER :=0 ;
17 l_pending_summ_trans Number :=0 ;
18 l_configuration_value_id NUMBER;
19 l_object_version_number NUMBER;
20 cnt NUMBER := 0;
21 l_person_id NUMBER :=0;
22 l_element_set_id NUMBER;
23 l_element_Set_name VARCHAR2(80);
24 i NUMBER;
25 l_rowid VARCHAR2(80);
26 l_current_run NUMBER;
27 l_err_phase NUMBER;
28 l_curr_phase NUMBER;
29 l_business_group_id NUMBER;
30 l_element_set_suffix_number Number :=1 ;
31 l_msg_buf VARCHAR(2000);
32 l_migration_not_allowed Exception;
33 l_element_set_alredy_exist Exception;
34 l_er_already_migrated Exception;
35 l_is_element_set_alredy_exist Number := 0;
36
37 CURSOR effort_master_csr IS
38 select outer.effort_report_id, outer.person_id, ppf.full_name,
39 -- people.full_name approver_name,
40 pert.begin_date, pert.End_date, pbg.name
41 from psp_effort_reports outer,
42 psp_effort_report_templates pert ,
43 per_all_people_f ppf,
44 -- wf_notifications wfis,
45 per_business_groups pbg
46 -- ,
47 -- per_assignments_f assignment,
48 -- per_people_f people
49 where outer.status_code in ('N', 'A')
50 and outer.template_id = pert.template_id
51 and pert.report_type='N'
52 and pbg.BUSINESS_GROUP_ID = pert.BUSINESS_GROUP_ID
53 /* Commented for bug 5048771
54 and wfis.notification_id = (select max(wfas.notification_id)
55 from wf_item_activity_statuses wfas
56 where wfas.item_type='PSPEFFWF'
57 and wfas.item_key= outer.effort_report_id || outer.VERSION_NUM)
58 */
59 and outer.person_id =ppf.person_id
60 and ppf.effective_start_date = ( SELECT MAX (ppf2.effective_start_date)
61 FROM per_all_people_f ppf2
62 WHERE ppf.person_id = ppf2.person_id
63 AND ppf2.effective_start_date <=pert.end_date
64 AND ppf2.effective_end_date >= pert.begin_date)
65 -- and pert.begin_date between ppf.effective_start_date and ppf.effective_End_date
66 -- AND assignment.person_id = ppf.person_id
67 -- AND assignment.supervisor_id = people.person_id (+)
68 -- AND assignment.assignment_type ='E'
69 -- AND trunc(SYSDATE) BETWEEN people.effective_start_date (+) AND people.effective_end_date(+)
70 -- AND trunc(SYSDATE) BETWEEN assignment.effective_start_date AND assignment.effective_end_date
71 -- AND assignment.primary_flag = 'Y'
72 ;
73
74 CURSOR element_set_alredy_exist_csr (p_element_set_name IN Varchar2) is
75 SELECT 1 from pay_element_sets
76 where ELEMENT_SET_NAME = p_element_set_name;
77
78 CURSOR element_striped_by_bg_csr is
79 SELECT DISTINCT business_group_id
80 from psp_effort_report_elements;
81
82 Cursor effort_element_csr(l_business_group_id IN NUMBER) is
83 select distinct element_type_id
84 from psp_effort_report_elements
85 where use_in_effort_report='Y'
86 and business_group_id =l_business_group_id;
87
88
89 CURSOR pending_da_csr is
90 select effort_report_id, outer.person_id, ppf.full_name ,
91 -- people.full_name approver_name,
92 pert.begin_date, pert.end_date , pal.adjustment_batch_name, pbg.name
93 from psp_effort_reports outer,
94 psp_effort_report_templates pert,
95 per_all_people_f ppf,
96 ----wf_notifications wfis,
97 psp_adjustment_control_table pal,
98 per_business_groups pbg
99 -- ,
100 -- per_assignments_f assignment,
101 -- per_people_f people
102 where outer.person_id = pal.person_id
103 and pert.end_date >= pal.distribution_start_date
104 and pert.begin_date <= pal.distribution_end_date
105 and pal.approver_id is null
106 and outer.status_code in ('S')
107 and outer.person_id =ppf.person_id
108 and outer.template_id = pert.template_id
109 -- and pert.begin_date between ppf.effective_Start_date and ppf.effective_end_date
110 and ppf.effective_start_date = ( SELECT MAX (ppf2.effective_start_date)
111 FROM per_all_people_f ppf2
112 WHERE ppf.person_id = ppf2.person_id
113 AND ppf2.effective_start_date <=pert.end_date
114 AND ppf2.effective_end_date >= pert.begin_date)
115 and pbg.BUSINESS_GROUP_ID = pert.BUSINESS_GROUP_ID
116 ---and wfis.notification_id = ( --- removed this condn and added void check for 4665930
117 ---- SELECT ias.notification_id
118 and pal.void is null
119 /* Commented for bug 5048771
120 and exists (select 1
121 FROM wf_lookups l_at,
122 wf_lookups l_as,
123 wf_activities_vl a,
124 wf_process_activities pa,
125 wf_item_types_vl it,
126 wf_items i,
127 wf_item_activity_statuses ias
128 WHERE ias.item_type = 'PSPADJWF'
129 AND ias.item_key = pal.adjustment_batch_name
130 AND i.item_type = 'PSPADJWF'
131 AND i.item_key = pal.adjustment_batch_name
132 AND i.begin_date between a.begin_date AND nvl(a.end_date, i.begin_date)
133 AND i.item_type = it.name
134 AND ias.process_activity = pa.instance_id
135 AND pa.activity_name = a.name
136 AND pa.activity_item_type = a.item_type
137 AND l_at.lookup_type = 'WFENG_ACTIVITY_TYPE'
138 AND l_at.lookup_code = a.type
139 AND l_as.lookup_type = 'WFENG_STATUS'
140 AND l_as.lookup_code = ias.activity_status
141 AND a.name = 'NOT_APPROVAL_REQUIRED')
142 */
143 -- AND assignment.person_id = ppf.person_id
144 -- AND assignment.supervisor_id = people.person_id (+)
145 -- AND assignment.assignment_type ='E'
146 -- AND trunc(SYSDATE) BETWEEN people.effective_start_date (+) AND people.effective_end_date(+)
147 -- AND trunc(SYSDATE) BETWEEN assignment.effective_start_date AND assignment.effective_end_date
148 -- AND assignment.primary_flag = 'Y'
149 ;
150
151
152
153 CURSOR pending_summ_trans_csr is
154 select effort_report_id, outer.person_id, ppf.full_name ,
155 -- people.full_name approver_name,
156 pert.begin_date, pert.end_date, pal.adjustment_batch_name, pbg.name
157 from psp_effort_reports outer,
158 psp_effort_report_templates pert,
159 per_all_people_f ppf,
160 ----wf_notifications wfis,
161 psp_adjustment_control_table pal,
162 psp_payroll_controls ppc,
163 per_business_groups pbg
164 -- ,
165 -- per_assignments_f assignment,
166 -- per_people_f people
167 where outer.person_id = pal.person_id
168 and pert.end_date >= pal.distribution_start_date
169 and pert.begin_date <= pal.distribution_end_date
170 and pal.ADJUSTMENT_BATCH_NAME = ppc.BATCH_NAME
171 and ppc.SOURCE_TYPE = 'A'
172 and ppc.STATUS_CODE = 'N'
173 and outer.status_code = 'S'
174 and outer.person_id =ppf.person_id
175 and outer.template_id = pert.template_id
176 -- and pert.begin_date between ppf.effective_Start_date and ppf.effective_end_date
177 and ppf.effective_start_date = ( SELECT MAX (ppf2.effective_start_date)
178 FROM per_all_people_f ppf2
179 WHERE ppf.person_id = ppf2.person_id
180 AND ppf2.effective_start_date <=pert.end_date
181 AND ppf2.effective_end_date >= pert.begin_date)
182 and pbg.BUSINESS_GROUP_ID = pert.BUSINESS_GROUP_ID
183 -- and wfis.notification_id = (
184 -- SELECT ias.notification_id
185 and void is null ---added for 4665930
186 /* Commented for bug 5048771
187 and exists (select 1 --- 4665930
188 FROM wf_lookups l_at,
189 wf_lookups l_as,
190 wf_activities_vl a,
191 wf_process_activities pa,
192 wf_item_types_vl it,
193 wf_items i,
194 wf_item_activity_statuses ias
195 WHERE ias.item_type = 'PSPADJWF'
196 AND ias.item_key = pal.adjustment_batch_name
197 AND i.item_type = 'PSPADJWF'
198 AND i.item_key = pal.adjustment_batch_name
199 AND i.begin_date between a.begin_date AND nvl(a.end_date, i.begin_date)
200 AND i.item_type = it.name
201 AND ias.process_activity = pa.instance_id
202 AND pa.activity_name = a.name
203 AND pa.activity_item_type = a.item_type
204 AND l_at.lookup_type = 'WFENG_ACTIVITY_TYPE'
205 AND l_at.lookup_code = a.type
206 AND l_as.lookup_type = 'WFENG_STATUS'
207 AND l_as.lookup_code = ias.activity_status
208 AND a.name = 'NOT_APPROVAL_REQUIRED')
209 */
210 -- AND assignment.person_id = ppf.person_id
211 -- AND assignment.supervisor_id = people.person_id (+)
212 -- AND assignment.assignment_type ='E'
213 -- AND trunc(SYSDATE) BETWEEN people.effective_start_date (+) AND people.effective_end_date(+)
214 -- AND trunc(SYSDATE) BETWEEN assignment.effective_start_date AND assignment.effective_end_date
215 -- AND assignment.primary_flag = 'Y'
216 ;
217
218 BEGIN
219
220 --hr_utility.trace_on(null,'ORACLE');
221 hr_utility.trace('Run in Diagnostic Mode: ' || p_diagnostic_mode);
222 hr_utility.trace('Proceed with Migration when Effort Reports are not Approved: ' || p_ignore_appr);
223 hr_utility.trace('Proceed with Migration when Adjustment Batches are Pending: ' || p_ignore_da);
224 hr_utility.trace('Element Set Name for Effort Report Elements: ' || p_element_set_name);
225
226 fnd_msg_pub.initialize;
227
228 -- Check if the migration has already occurred. If not Proceed further else do nothing
229 IF psp_general.IS_EFFORT_REPORT_MIGRATED THEN
230 hr_utility.trace('Effort Report Already Migrated');
231 raise l_er_already_migrated;
232 ELSE
233 IF p_ignore_appr= 'N' THEN
234 hr_utility.trace('Checking for Pending Effort Report: START');
235
236 OPEN effort_master_csr;
237 FETCH effort_master_csr BULK COLLECT into eff_master_rec.effort_report_id, eff_master_rec.person_id,
238 eff_master_rec.full_name, eff_master_rec.start_date, eff_master_rec.end_date,eff_master_rec.business_group_name;
239 CLOSE effort_master_csr;
240
241 l_appr_exists := eff_master_rec.effort_report_id.count;
242 hr_utility.trace('Pending Effort reports count =' || l_appr_exists);
243
244 FOR i in 1..l_appr_exists
245 LOOP
246 fnd_message.set_name('PSP', 'PSP_EFF_REP_PEND_STATUS');
247 fnd_message.set_token('EMPNAME',eff_master_rec.full_name(i));
248 fnd_message.set_token('BGNAME',eff_master_rec.business_group_name(i));
249 fnd_message.set_token('STARTDATE',eff_master_rec.start_date(i));
250 fnd_message.set_token('ENDDATE',eff_master_rec.end_date(i));
251 l_msg_buf := Fnd_Message.Get ;
252 fnd_file.put_line( FND_FILE.LOG, l_msg_buf);
253 END LOOP;
254
255 eff_master_rec.effort_report_id.delete;
256 eff_master_rec.person_id.delete;
257 eff_master_rec.full_name.delete;
258 eff_master_rec.start_date.delete;
259 eff_master_rec.end_date.delete;
260
261 hr_utility.trace('Checking for Pending Effort Report: END');
262 /* check for Pending distribution adjustments -> check for unapproved DA batches
263 for whom effort reports with previous 'N'/'S' status exist. Dump that information */
264 IF p_ignore_da = 'N' THEN
265 hr_utility.trace('Checking for Pending distribution adjustments: START');
266 OPEN pending_da_csr;
267 FETCH PENDING_DA_CSR BULK COLLECT INTO eff_master_rec.effort_report_id, eff_master_rec.person_id,
268 eff_master_rec.full_name,
269 eff_master_rec.start_date,
270 eff_master_rec.end_date, eff_master_rec.da_batch,eff_master_rec.business_group_name;
271 CLOSE pending_da_csr;
272
273 l_pending_da := eff_master_rec.effort_report_id.count;
274 hr_utility.trace('Pending distribution adjustments =' || l_pending_da );
275 FOR i in 1..l_pending_da
276 LOOP
277 fnd_message.set_name('PSP', 'PSP_EFF_DA_PEND_STATUS');
278 fnd_message.set_token('DABATCH',eff_master_rec.da_batch(i));
279 fnd_message.set_token('BGNAME',eff_master_rec.business_group_name(i));
280 fnd_message.set_token('EMPNAME',eff_master_rec.full_name(i));
281 fnd_message.set_token('STARTDATE',eff_master_rec.start_date(i));
282 fnd_message.set_token('ENDDATE',eff_master_rec.end_date(i));
283 l_msg_buf := Fnd_Message.Get ;
284 fnd_file.put_line (FND_FILE.LOG, l_msg_buf );
285 END LOOP;
286 eff_master_rec.effort_report_id.delete;
287 eff_master_rec.person_id.delete;
288 eff_master_rec.full_name.delete;
289 eff_master_rec.start_date.delete;
290 eff_master_rec.end_date.delete;
291
292 hr_utility.trace('Checking for Pending distribution adjustments: END');
293
294 /* check for Pending Distribution Adjustment Batch that has not been summarized and transferred
295 Dump that information */
296
297 hr_utility.trace('Checking for distribution adjustments not S and T: START');
298
299 OPEN pending_summ_trans_csr;
300 FETCH pending_summ_trans_csr BULK COLLECT INTO eff_master_rec.effort_report_id, eff_master_rec.person_id,
301 eff_master_rec.full_name,
302 eff_master_rec.start_date,
303 eff_master_rec.end_date, eff_master_rec.da_batch,eff_master_rec.business_group_name;
304 CLOSE pending_summ_trans_csr;
305
306 l_pending_summ_trans := eff_master_rec.effort_report_id.count;
307 hr_utility.trace('distribution adjustments not S and T ='|| l_pending_summ_trans);
308 FOR i in 1..l_pending_summ_trans
309 LOOP
310 fnd_message.set_name('PSP', 'PSP_EFF_DA_NOT_SUMM_TRANS');
311 fnd_message.set_token('DABATCH',eff_master_rec.da_batch(i));
312 fnd_message.set_token('BGNAME',eff_master_rec.business_group_name(i));
313 fnd_message.set_token('EMPNAME',eff_master_rec.full_name(i));
314 fnd_message.set_token('STARTDATE',eff_master_rec.start_date(i));
315 fnd_message.set_token('ENDDATE',eff_master_rec.end_date(i));
316 l_msg_buf := Fnd_Message.Get ;
317 fnd_file.put_line (FND_FILE.LOG, l_msg_buf );
318 END LOOP;
319 END IF;
320 eff_master_rec.effort_report_id.delete;
321 eff_master_rec.person_id.delete;
322 eff_master_rec.full_name.delete;
323 eff_master_rec.start_date.delete;
324 eff_master_rec.end_date.delete;
325 hr_utility.trace('Checking for distribution adjustments not S and T: END');
326 END IF;
327
328
329 IF (p_diagnostic_mode='N') and ((l_pending_da <>0) or (l_appr_exists <> 0) or (l_pending_summ_trans <> 0)) then
330 raise l_migration_not_allowed;
331 END IF;
332 IF (p_diagnostic_mode='N') and (l_pending_da=0) and (l_appr_exists=0) and (l_pending_summ_trans = 0) then
333
334 hr_utility.trace('Migration:START');
335
336 /* Regular Mode, No pending Distribution Adjustments, No pending Effort Reports */
337
338 -- fnd_file.put_line( FND_FILE.LOG, ' Before ES');
339
340 hr_utility.trace('Set the Element Set Name');
341 IF p_element_Set_name is null then
342
343 l_element_Set_name :='Effort Reporting Element Set';
344
345 ELSE
346 l_element_Set_name:=p_element_Set_name;
347
348 END IF;
349
350
351
352 OPEN element_striped_by_bg_csr;
353 OPEN element_set_alredy_exist_csr(p_element_set_name);
354 FETCH element_set_alredy_exist_csr into l_is_element_set_alredy_exist;
355 CLOSE element_set_alredy_exist_csr;
356 IF (l_is_element_set_alredy_exist = 1) and (p_element_set_name is not null) then
357 raise l_element_set_alredy_exist;
358 END IF;
359
360 hr_utility.trace(' Open element_striped_by_bg_csr');
361
362 LOOP
363 FETCH element_striped_by_bg_csr into l_business_group_id;
364 EXIT when element_striped_by_bg_csr%NOTFOUND;
365
366 -- <<loop_again>>
367 -- OPEN element_set_alredy_exist_csr(l_element_set_name);
368 -- FETCH element_set_alredy_exist_csr into l_is_element_set_alredy_exist;
369 -- CLOSE element_set_alredy_exist_csr;
370
371 -- IF (l_is_element_set_alredy_exist = 1) then
372 -- l_element_Set_name := substr(l_element_Set_name,1,length(l_element_Set_name)-length(l_element_set_suffix_number-1)) || l_element_set_suffix_number ;
373 -- l_element_set_suffix_number := l_element_set_suffix_number + 1;
374 -- fnd_file.put_line (FND_FILE.LOG, 'Deep l_element_Set_name= '||l_element_Set_name );
375 -- GOTO loop_again;
376 -- END IF;
377
378 OPEN effort_element_csr(l_business_group_id);
379 FETCH effort_element_csr BULK COLLECT into eff_element_rec.element_type_id;
380 CLOSE effort_element_csr;
381
382 hr_utility.trace(' Create Elements: pay_element_sets_pkg.insert_row');
383
384 l_rowid := null;
385 l_element_Set_id := null;
386 pay_element_sets_pkg.insert_row(l_rowid, l_element_Set_id, l_business_group_id, null, l_element_set_name,'C', 'LD Eff Reports Migration Set', null, null);
387 if l_element_set_suffix_number = 1 then
388 l_element_Set_name := substr(l_element_Set_name,1,length(l_element_Set_name)) || l_element_set_suffix_number;
389 ELSE
390 l_element_Set_name := substr(l_element_Set_name,1,length(l_element_Set_name)-length(l_element_set_suffix_number-1)) || l_element_set_suffix_number ;
391 END IF;
392 l_element_set_suffix_number := l_element_set_suffix_number + 1;
393
394 -- FND_FILE.PUT_LINE( FND_FILE.LOG, ' After ES insert ');
395
396 -- FORALL i in 1..eff_element_rec.element_type_id.count
397
398 For i in 1..eff_element_rec.element_type_id.count
399
400 LOOP
401 hr_utility.trace(' Create Elements: pay_element_type_rules_pkg.insert_row');
402
403 l_rowid:=NULL;
404 pay_element_type_rules_pkg.insert_row(l_rowid, eff_element_rec.element_type_id(i), l_element_Set_id, 'I',
405 sysdate, fnd_global.user_id, fnd_global.user_id, fnd_global.user_id, sysdate);
406
407 END LOOP;
408 -- fnd_file.put_line( FND_FILE.LOG, ' After members insert ');
409 psp_message_s.print_success;
410
411
412 END LOOP;
413
414 CLOSE element_striped_by_bg_csr;
415 hr_utility.trace(' Create Elements: END');
416
417
418 /* Delete the obsolete menu items */
419
420 -- fnd_file.put_line( FND_FILE.LOG, ' Before menu delete ');
421
422 hr_utility.trace(' Delete Menus : START');
423 hr_utility.trace(' Delete Menu : Effort Report Period Summary');
424
425 IF (fnd_function_security.menu_entry_exists('PSP_EFFORT_MENU', '', 'PSPERPRD')) THEN
426 l_err_phase:=1;
427 fnd_function_security.menu_entry(
428 menu_name => 'PSP_EFFORT_MENU',
429 entry_sequence => 1,
430 prompt => 'Effort Report Period Summary',
431 function_name => 'PSPERPRD',
432 description => '',
433 delete_flag => 'Y');
434
435
436 END IF;
437 hr_utility.trace(' Delete Menu : Effort Report Creation');
438
439 IF (fnd_function_security.menu_entry_exists('PSP_EFFORT_MENU', '', 'PSPERCRT')) THEN
440 l_err_phase:=2;
441 fnd_function_security.menu_entry(
442 menu_name => 'PSP_EFFORT_MENU',
443 entry_sequence => 2,
444 prompt => 'Effort Report Creation',
445 function_name => 'PSPERCRT',
446 description => '',
447 delete_flag => 'Y');
448 END IF;
449
450
451 hr_utility.trace(' Delete Menu :Adhoc Effort Report Creation');
452 IF (fnd_function_security.menu_entry_exists('PSP_EFFORT_MENU', '', 'PSPERADH')) THEN
453 l_err_phase:=3;
454 fnd_function_security.menu_entry(
455 menu_name => 'PSP_EFFORT_MENU',
456 entry_sequence => 3,
457 prompt => 'Adhoc Effort Report Creation',
458 function_name => 'PSPERADH',
459 description => '',
460 delete_flag => 'Y');
461 END IF;
462
463
464 hr_utility.trace(' Delete Menu :Review Effort Report');
465 IF (fnd_function_security.menu_entry_exists('PSP_EFFORT_MENU', '', 'PSPERRVW')) THEN
466 l_err_phase:=4;
467 fnd_function_security.menu_entry(
468 menu_name => 'PSP_EFFORT_MENU',
469 entry_sequence => 4,
470 prompt => 'Review Effort Report',
471 function_name => 'PSPERRVW',
472 description => '',
473 delete_flag => 'Y');
474 END IF;
475
476 hr_utility.trace(' Delete Menu :Review Effort Report');
477 IF (fnd_function_security.menu_entry_exists('PSP_WORKFLOW_MENU', '', 'PSPERRVW')) THEN
478 l_err_phase:=4;
479 fnd_function_security.menu_entry(
480 menu_name => 'PSP_WORKFLOW_MENU',
481 entry_sequence => 3,
482 prompt => 'Review Effort Report',
483 function_name => 'PSPERRVW',
484 description => '',
485 delete_flag => 'Y');
486 END IF;
487
488 hr_utility.trace(' Delete Menu :Effort Report Aging');
489 IF (fnd_function_security.menu_entry_exists('PSP_EFFORT_MENU', '', 'PSPERAGI')) THEN
490 l_err_phase:=5;
491 fnd_function_security.menu_entry(
492 menu_name => 'PSP_EFFORT_MENU',
493 entry_sequence => 5,
494 prompt => 'Effort Report Aging',
495 function_name => 'PSPERAGI',
496 description => '',
497 delete_flag => 'Y');
498 END IF;
499
500 hr_utility.trace(' Delete Menu :Effort Report Messages');
501 IF (fnd_function_security.menu_entry_exists('PSP_EFFORT_MENU', '', 'PSPERMES')) THEN
502 l_err_phase:=6;
503 fnd_function_security.menu_entry(
504 menu_name => 'PSP_EFFORT_MENU',
505 entry_sequence => 6,
506 prompt => 'Effort Report Messages',
507 function_name => 'PSPERMES',
508 description => '',
509 delete_flag => 'Y');
510 END IF;
511
512 hr_utility.trace(' Delete Menu :Setup: Effort Report Element Types');
513 IF (fnd_function_security.menu_entry_exists('PSP_OTHERS', '', 'PSPSUEFF')) THEN
514 l_err_phase:=7;
515 fnd_function_security.menu_entry(
516 menu_name => 'PSP_OTHERS',
517 entry_sequence => 7,
518 prompt => 'Setup: Effort Report Element Types',
519 function_name => 'PSPSUEFF',
520 description => '',
521 delete_flag => 'Y');
522 END IF;
523
524 hr_utility.trace(' Delete Menu :Setup: Create Notification Users');
525 IF (fnd_function_security.menu_entry_exists('PSP_OTHERS', '', 'PSPSUCU')) THEN
526 l_err_phase:=8;
527 fnd_function_security.menu_entry(
528 menu_name => 'PSP_OTHERS',
529 entry_sequence => 9,
530 prompt => 'Setup: Create Notification Users',
531 function_name => 'PSPSUCU',
532 description => '',
533 delete_flag => 'Y');
534 END IF;
535
536
537 -- If all successful, insert row in psp_upgrade_115
538
539 select psp_upgrade_115_s.nextval
540 into l_current_run
541 from dual;
542 hr_utility.trace(' Insert into psp_upgrade_115');
543
544 INSERT into psp_upgrade_115(run_id, phase,object_name,date_time,status,error_message)
545 VALUES (l_current_run,10000,'PSP_UPGRADE_115',sysdate,'R','Migrated Effort Reports') ;
546
547 COMMIT;
548
549 END IF;
550 hr_utility.trace(' Migration Successful');
551 hr_utility.trace_off;
552 END IF;
553 EXCEPTION
554 WHEN l_er_already_migrated then
555 ROLLBACK;
556 fnd_message.set_name('PSP', 'PSP_ER_ALREADY_MIGRATED');
557 l_msg_buf := Fnd_Message.Get ;
558 fnd_file.put_line (FND_FILE.LOG, l_msg_buf );
559 fnd_msg_pub.add;
560 retCode :=0;
561 WHEN l_element_set_alredy_exist then
562 ROLLBACK;
563 fnd_message.set_name('PSP', 'PSP_ER_DUPLICATE_ELEMENT_SET');
564 fnd_message.set_token('ELEMENTSET',p_element_set_name);
565 l_msg_buf := Fnd_Message.Get ;
566 fnd_file.put_line (FND_FILE.LOG, l_msg_buf );
567 fnd_msg_pub.add;
568 retCode :=2;
569 hr_utility.trace_off;
570 WHEN l_migration_not_allowed then
571 ROLLBACK;
572 fnd_message.set_name('PSP', 'PSP_ER_MIGRATION_NOT_ALLOWED');
573 l_msg_buf := Fnd_Message.Get ;
574 fnd_file.put_line (FND_FILE.LOG, l_msg_buf );
575 fnd_msg_pub.add;
576 retCode :=2;
577 hr_utility.trace_off;
578 WHEN OTHERS THEN
579 ROLLBACK;
580 fnd_message.set_name('PSP','PSP_SQL_ERROR');
581 fnd_message.set_token('SQLERROR',sqlerrm||l_err_phase);
582 fnd_msg_pub.add;
583 psp_message_s.print_error(p_mode => FND_FILE.LOG,
584 p_print_header => FND_API.G_TRUE);
585 retCode :=2;
586 hr_utility.trace_off;
587 END migrate_eff_reports;
588 END psp_upgrade_eff_reports;