[Home] [Help]
PACKAGE BODY: APPS.PSP_ER_AME
Source
1 PACKAGE BODY PSP_ER_AME as
2 /* $Header: PSPERAMB.pls 120.4 2005/09/30 02:46 dpaudel noship $*/
3 g_retry_request_id integer;
4 procedure insert_error(p_request_id in integer,
5 p_message_level in varchar2,
6 p_source_id in integer,
7 p_err_mesg in varchar2,
8 p_retry_request_id in integer) is
9 l_return_status varchar2(1);
10 failed_insertion exception;
11 begin
12 psp_general.add_report_error(p_request_id,
13 p_message_level,
14 p_source_id ,
15 p_retry_request_id,
16 nULL,
17 p_err_mesg ,
18 l_return_status ) ;
19 if l_return_status = 'E' then
20 fnd_msg_pub.add_exc_msg('PSP_ER_AME','INSERT_ERROR');
21 raise failed_insertion;
22 end if;
23 end;
24 procedure get_first_approvers(p_request_id in integer,
25 p_start_person in integer,
26 p_end_person in integer,
27 p_return_status out nocopy varchar2,
28 p_retry_request_id in integer default null) is
29
30 l_err_mesg varchar2(2000);
31 no_approver_found exception;
32 l_no_approver_found boolean;
33 populate_error exception;
34 type er_rec_type is
35 record (effort_report_detail_id integer,
36 person_id integer,
37 assignment_id integer,
38 project_id integer,
39 award_id integer,
40 task_id integer,
41 expenditure_org_id integer,
42 expenditure_type varchar2(30),
43 segment1 varchar2(25),
44 segment2 varchar2(25),
45 segment3 varchar2(25),
46 segment4 varchar2(25),
47 segment5 varchar2(25),
48 segment6 varchar2(25),
49 segment7 varchar2(25),
50 segment8 varchar2(25),
51 segment9 varchar2(25),
52 segment10 varchar2(25),
53 segment11 varchar2(25),
54 segment12 varchar2(25),
55 segment13 varchar2(25),
56 segment14 varchar2(25),
57 segment15 varchar2(25),
58 segment16 varchar2(25),
59 segment17 varchar2(25),
60 segment18 varchar2(25),
61 segment19 varchar2(25),
62 segment20 varchar2(25),
63 segment21 varchar2(25),
64 segment22 varchar2(25),
65 segment23 varchar2(25),
66 segment24 varchar2(25),
67 segment25 varchar2(25),
68 segment26 varchar2(25),
69 segment27 varchar2(25),
70 segment28 varchar2(25),
71 segment29 varchar2(25),
72 segment30 varchar2(25));
73
74 type er_cur_type is ref cursor;
75 er_cur er_cur_type;
76 er_rec er_rec_type;
77 er_rec_prev er_rec_type;
78 sql_string varchar2(2000) := '';
79 l_ame_txn_id varchar2(50);
80 l_counter integer;
81 l_process_complete varchar2(1000);
82 l_next_approver ame_util.approversTable2;
83 l_approver_sql_stmnt varchar2(2000) := null;
84 l_sqlerrm varchar2(4000);
85 type t_integer is table of number(15) index by binary_integer;
86 type break_rec_type is record
87 (array_detail_id t_integer,
88 array_break_attribute t_integer,
89 array_break_attribute2 t_integer);
90
91 break_rec break_rec_type;
92 t_erd_id t_integer;
93
94
95 cursor wf_approval_cur is
96 select decode(approval_type,'PRE','N','Y') workflow_approval_req_flag,
97 approval_type,
98 custom_approval_code,
99 sup_levels
100 from psp_report_templates_h
101 where request_id = p_request_id;
102
103 cursor check_valid_prj is
104 select er.person_id
105 from psp_eff_report_details erd,
106 psp_eff_reports er
107 where erd.project_id is null
108 and erd.effort_Report_id = er.effort_report_id
109 and er.request_id = p_request_id;
110
111 cursor get_report_layout is
112 select substr(report_Template_code,6,3)
113 from psp_report_templates_h
114 where request_id = p_request_id;
115
116 l_report_layout_code varchar2(20);
117 l_err_person_id number;
118
119 cursor check_non_sponsered_prj is
120 select er.person_id
121 from psp_eff_report_details erd,
122 psp_eff_reports er
123 where erd.award_id is null
124 and erd.effort_Report_id = er.effort_report_id
125 and er.request_id = p_request_id;
126
127 --- changed following 3 cursors to remove ame calls
128 cursor er_cur_gpi_pmg_tmg is
129 select min(erd.effort_report_detail_id),
130 erd.investigator_person_id,
131 null
132 from psp_eff_reports er,
133 psp_eff_report_details erd
134 where er.effort_report_id = erd.effort_report_id
135 and er.status_code = 'N'
136 and er.request_id = p_request_id
137 and er.person_id between p_start_person and p_end_person
138 group by erd.investigator_person_id;
139
140 ---- uva fix end
141
142 cursor er_cur_emp is select min(effort_report_detail_id),
143 person_id,
144 null
145 from psp_eff_reports er,
146 psp_eff_report_details erd
147 where er.effort_report_id = erd.effort_report_id
148 and er.status_code = 'N'
149 and er.request_id = p_request_id
150 and er.person_id between p_start_person and p_end_person
151 group by person_id;
152
153 --- AME will be called only for supervisor, i.e min person, min of er_detail_id
154 cursor er_cur_sup_1 is select min(erd.effort_report_detail_id),
155 min(er.person_id),
156 asg.supervisor_id
157 from per_assignments_f asg,
158 psp_eff_reports er,
159 psp_eff_report_details erd
160 where asg.person_id = er.person_id
161 and er.status_code = 'N'
162 and asg.assignment_type ='E'
163 and trunc(er.end_date) between asg.effective_start_date and
164 asg.effective_end_date
165 and asg.primary_flag = 'Y'
166 and er.effort_report_id = erd.effort_report_id
167 and er.request_id = p_request_id
168 and er.person_id between p_start_person and p_end_person
169 group by asg.supervisor_id;
170
171 i integer;
172 l_error_out varchar2(4000);
173 approval_type_rec wf_approval_cur%rowtype;
174 approver_rec ame_util.approverRecord2;
175
176
177 ---============= Local Procedures =============---
178
179 --- Function to build Select stmnt for custom approvals -----
180 function make_select return varchar2 is
181 select_string varchar2(1000) := null;
182 begin
183 hr_utility.trace ('psperamb--> Entered make_select');
184
185 if instr(g_approver_basis, 'assignment_id') > 0 then
186 select_string := ',assignment_id' ;
187 else
188 select_string := ',null';
189 end if;
190
191 if instr(g_approver_basis, 'project_id') > 0 then
192 select_string := select_string||',project_id' ;
193 else
194 select_string := select_string||',null';
195 end if;
196
197 if instr(g_approver_basis, 'award_id') > 0 then
198 select_string := select_string ||',award_id' ;
199 else
200 select_string := select_string ||',null';
201 end if;
202
203 if instr(g_approver_basis, 'task_id') > 0 then
204 select_string := select_string ||',task_id' ;
205 else
206 select_string := select_string ||',null';
207 end if;
208
209 if instr(g_approver_basis, 'expenditure_organization_id') > 0 then
210 select_string := select_string ||',expenditure_organization_id' ;
211 else
212 select_string := select_string ||',null';
213 end if;
214
215 if instr(g_approver_basis, 'expenditure_type') > 0 then
216 select_string := select_string ||',expenditure_type' ;
217 else
218 select_string := select_string ||',null';
219 end if;
220
221 if instr(g_approver_basis, 'segment1') > 0 then
222 select_string := select_string ||',segment1' ;
223 else
224 select_string := select_string ||',null';
225 end if;
226
227 if instr(g_approver_basis, 'segment2') > 0 then
228 select_string := select_string ||',segment2' ;
229 else
230 select_string := select_string ||',null';
231 end if;
232
233 if instr(g_approver_basis, 'segment3') > 0 then
234 select_string := select_string ||',segment3' ;
235 else
236 select_string := select_string ||',null';
237 end if;
238
239 if instr(g_approver_basis, 'segment4') > 0 then
240 select_string := select_string ||',segment4' ;
241 else
242 select_string := select_string ||',null';
243 end if;
244
245 if instr(g_approver_basis, 'segment5') > 0 then
246 select_string := select_string ||',segment5' ;
247 else
248 select_string := select_string ||',null';
249 end if;
250
251 if instr(g_approver_basis, 'segment6') > 0 then
252 select_string := select_string ||',segment6' ;
253 else
254 select_string := select_string ||',null';
255 end if;
256
257 if instr(g_approver_basis, 'segment7') > 0 then
258 select_string := select_string ||',segment7' ;
259 else
260 select_string := select_string ||',null';
261 end if;
262
263 if instr(g_approver_basis, 'segment8') > 0 then
264 select_string := select_string ||',segment8' ;
265 else
266 select_string := select_string ||',null';
267 end if;
268
269 if instr(g_approver_basis, 'segment9') > 0 then
270 select_string := select_string ||',segment9' ;
271 else
272 select_string := select_string ||',null';
273 end if;
274
275 if instr(g_approver_basis, 'segment10') > 0 then
276 select_string := select_string ||',segment10' ;
277 else
278 select_string := select_string ||',null';
279 end if;
280
281 if instr(g_approver_basis, 'segment11') > 0 then
282 select_string := select_string ||',segment11' ;
283 else
284 select_string := select_string ||',null';
285 end if;
286
287 if instr(g_approver_basis, 'segment12') > 0 then
288 select_string := select_string ||',segment12' ;
289 else
290 select_string := select_string ||',null';
291 end if;
292
293 if instr(g_approver_basis, 'segment13') > 0 then
294 select_string := select_string ||',segment13' ;
295 else
296 select_string := select_string ||',null';
297 end if;
298
299 if instr(g_approver_basis, 'segment14') > 0 then
300 select_string := select_string ||',segment14' ;
301 else
302 select_string := select_string ||',null';
303 end if;
304
305 if instr(g_approver_basis, 'segment15') > 0 then
306 select_string := select_string ||',segment15' ;
307 else
308 select_string := select_string ||',null';
309 end if;
310
311 if instr(g_approver_basis, 'segment16') > 0 then
312 select_string := select_string ||',segment16' ;
313 else
314 select_string := select_string ||',null';
315 end if;
316
317 if instr(g_approver_basis, 'segment17') > 0 then
318 select_string := select_string ||',segment17' ;
319 else
320 select_string := select_string ||',null';
321 end if;
322
323 if instr(g_approver_basis, 'segment18') > 0 then
324 select_string := select_string ||',segment18' ;
325 else
326 select_string := select_string ||',null';
327 end if;
328
329 if instr(g_approver_basis, 'segment19') > 0 then
330 select_string := select_string ||',segment19' ;
331 else
332 select_string := select_string ||',null';
333 end if;
334
335 if instr(g_approver_basis, 'segment20') > 0 then
336 select_string := select_string ||',segment20' ;
337 else
338 select_string := select_string ||',null';
339 end if;
340
341 if instr(g_approver_basis, 'segment21') > 0 then
342 select_string := select_string ||',segment21' ;
343 else
344 select_string := select_string ||',null';
345 end if;
346
347 if instr(g_approver_basis, 'segment22') > 0 then
348 select_string := select_string ||',segment22' ;
349 else
350 select_string := select_string ||',null';
351 end if;
352
353 if instr(g_approver_basis, 'segment23') > 0 then
354 select_string := select_string ||',segment23' ;
355 else
356 select_string := select_string ||',null';
357 end if;
358
359 if instr(g_approver_basis, 'segment24') > 0 then
360 select_string := select_string ||',segment24' ;
361 else
362 select_string := select_string ||',null';
363 end if;
364
365 if instr(g_approver_basis, 'segment25') > 0 then
366 select_string := select_string ||',segment25' ;
367 else
368 select_string := select_string ||',null';
369 end if;
370
371 if instr(g_approver_basis, 'segment26') > 0 then
372 select_string := select_string ||',segment26' ;
373 else
374 select_string := select_string ||',null';
375 end if;
376
377 if instr(g_approver_basis, 'segment27') > 0 then
378 select_string := select_string ||',segment27' ;
379 else
380 select_string := select_string ||',null';
381 end if;
382
383 if instr(g_approver_basis, 'segment28') > 0 then
384 select_string := select_string ||',segment28' ;
385 else
386 select_string := select_string ||',null';
387 end if;
388
389 if instr(g_approver_basis, 'segment29') > 0 then
390 select_string := select_string ||',segment29' ;
391 else
392 select_string := select_string ||',null';
393 end if;
394
395 if instr(g_approver_basis, 'segment30') > 0 then
396 select_string := select_string ||',segment30' ;
397 else
398 select_string := select_string ||',null';
399 end if;
400 hr_utility.trace ('psperamb--> Exiting make_select, string='||select_string);
401 return select_string;
402 exception
403 when others then
404 fnd_msg_pub.add_exc_msg('PSP_ER_AME','MAKE_SELECT');
405 raise;
406 end;
407
408
409 --- Function to determine break group, hence call AME for custom approval---
410 function break_group return boolean is
411 i integer := 1;
412 begin
413
414 --- the first record, don't break the group
415 if er_rec_prev.effort_report_detail_id is null then
416 return true;
417 end if;
418
419 if instr(g_approver_basis, 'assignment_id') > 0 then
420 if nvl(er_rec.assignment_id, -999) <> nvl(er_rec_prev.assignment_id, -999) then
421 return true;
422 elsif i = g_no_of_attributes then
423 return false;
424 end if;
425 i := i + 1;
426 end if;
427
428 if instr(g_approver_basis, 'project_id') > 0 then
429 if nvl(er_rec.project_id, -999) <> nvl(er_rec_prev.project_id, -999) then
430 return true;
431 elsif i = g_no_of_attributes then
432 return false;
433 end if;
434 i := i + 1;
435 end if;
436
437 if instr(g_approver_basis, 'award_id') > 0 then
438 if nvl(er_rec.award_id, -999) <> nvl(er_rec_prev.award_id, -999) then
439 return true;
440 elsif i = g_no_of_attributes then
441 return false;
442 end if;
443 i := i + 1;
444 end if;
445
446 if instr(g_approver_basis, 'task_id') > 0 then
447 if nvl(er_rec.task_id, -999) <> nvl(er_rec_prev.task_id, -999) then
448 return true;
449 elsif i = g_no_of_attributes then
450 return false;
451 end if;
452 i := i + 1;
453 end if;
454
455 if instr(g_approver_basis, 'expenditure_organization_id') > 0 then
456 if nvl(er_rec.expenditure_org_id, -999) <> nvl(er_rec_prev.expenditure_org_id, -999) then
457 return true;
458 elsif i = g_no_of_attributes then
459 return false;
460 end if;
461 i := i + 1;
462 end if;
463
464 if instr(g_approver_basis, 'expenditure_type') > 0 then
465 if (er_rec.expenditure_type is null and
466 er_rec_prev.expenditure_type is null ) OR
467 er_rec.expenditure_type <> er_rec_prev.expenditure_type then
468 return true;
469 elsif i = g_no_of_attributes then
470 return false;
471 end if;
472 i := i + 1;
473 end if;
474
475 if instr(g_approver_basis, 'segment1') > 0 then
476 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
477 er_rec.segment1 <> er_rec_prev.segment1 then
478 return true;
479 elsif i = g_no_of_attributes then
480 return false;
481 end if;
482 i := i + 1;
483 end if;
484
485 if instr(g_approver_basis, 'segment2') > 0 then
486 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
487 er_rec.segment1 <> er_rec_prev.segment1 then
488 return true;
489 elsif i = g_no_of_attributes then
490 return false;
491 end if;
492 i := i + 1;
493 end if;
494
495 if instr(g_approver_basis, 'segment3') > 0 then
496 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
497 er_rec.segment1 <> er_rec_prev.segment1 then
498 return true;
499 elsif i = g_no_of_attributes then
500 return false;
501 end if;
502 i := i + 1;
503 end if;
504
505 if instr(g_approver_basis, 'segment4') > 0 then
506 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
507 er_rec.segment1 <> er_rec_prev.segment1 then
508 return true;
509 elsif i = g_no_of_attributes then
510 return false;
511 end if;
512 i := i + 1;
513 end if;
514
515 if instr(g_approver_basis, 'segment5') > 0 then
516 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
517 er_rec.segment1 <> er_rec_prev.segment1 then
518 return true;
519 elsif i = g_no_of_attributes then
520 return false;
521 end if;
522 i := i + 1;
523 end if;
524
525 if instr(g_approver_basis, 'segment6') > 0 then
526 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
527 er_rec.segment1 <> er_rec_prev.segment1 then
528 return true;
529 elsif i = g_no_of_attributes then
530 return false;
531 end if;
532 i := i + 1;
533 end if;
534
535 if instr(g_approver_basis, 'segment7') > 0 then
536 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
537 er_rec.segment1 <> er_rec_prev.segment1 then
538 return true;
539 elsif i = g_no_of_attributes then
540 return false;
541 end if;
542 i := i + 1;
543 end if;
544
545 if instr(g_approver_basis, 'segment8') > 0 then
546 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
547 er_rec.segment1 <> er_rec_prev.segment1 then
548 return true;
549 elsif i = g_no_of_attributes then
550 return false;
551 end if;
552 i := i + 1;
553 end if;
554
555 if instr(g_approver_basis, 'segment9') > 0 then
556 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
557 er_rec.segment1 <> er_rec_prev.segment1 then
558 return true;
559 elsif i = g_no_of_attributes then
560 return false;
561 end if;
562 i := i + 1;
563 end if;
564
565 if instr(g_approver_basis, 'segment10') > 0 then
566 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
567 er_rec.segment1 <> er_rec_prev.segment1 then
568 return true;
569 elsif i = g_no_of_attributes then
570 return false;
571 end if;
572 i := i + 1;
573 end if;
574
575 if instr(g_approver_basis, 'segment11') > 0 then
576 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
577 er_rec.segment1 <> er_rec_prev.segment1 then
578 return true;
579 elsif i = g_no_of_attributes then
580 return false;
581 end if;
582 i := i + 1;
583 end if;
584
585 if instr(g_approver_basis, 'segment12') > 0 then
586 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
587 er_rec.segment1 <> er_rec_prev.segment1 then
588 return true;
589 elsif i = g_no_of_attributes then
590 return false;
591 end if;
592 i := i + 1;
593 end if;
594
595 if instr(g_approver_basis, 'segment13') > 0 then
596 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
597 er_rec.segment1 <> er_rec_prev.segment1 then
598 return true;
599 elsif i = g_no_of_attributes then
600 return false;
601 end if;
602 i := i + 1;
603 end if;
604
605 if instr(g_approver_basis, 'segment14') > 0 then
606 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
607 er_rec.segment1 <> er_rec_prev.segment1 then
608 return true;
609 elsif i = g_no_of_attributes then
610 return false;
611 end if;
612 i := i + 1;
613 end if;
614
615 if instr(g_approver_basis, 'segment15') > 0 then
616 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
617 er_rec.segment1 <> er_rec_prev.segment1 then
618 return true;
619 elsif i = g_no_of_attributes then
620 return false;
621 end if;
622 i := i + 1;
623 end if;
624
625 if instr(g_approver_basis, 'segment16') > 0 then
626 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
627 er_rec.segment1 <> er_rec_prev.segment1 then
628 return true;
629 elsif i = g_no_of_attributes then
630 return false;
631 end if;
632 i := i + 1;
633 end if;
634
635 if instr(g_approver_basis, 'segment17') > 0 then
636 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
637 er_rec.segment1 <> er_rec_prev.segment1 then
638 return true;
639 elsif i = g_no_of_attributes then
640 return false;
641 end if;
642 i := i + 1;
643 end if;
644
645 if instr(g_approver_basis, 'segment18') > 0 then
646 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
647 er_rec.segment1 <> er_rec_prev.segment1 then
648 return true;
649 elsif i = g_no_of_attributes then
650 return false;
651 end if;
652 i := i + 1;
653 end if;
654
655 if instr(g_approver_basis, 'segment19') > 0 then
656 if (er_rec.segment1 is null and er_rec_prev.segment1 is null ) or
657 er_rec.segment1 <> er_rec_prev.segment1 then
658 return true;
659 elsif i = g_no_of_attributes then
660 return false;
661 end if;
662 i := i + 1;
663 end if;
664
665 if instr(g_approver_basis, 'segment20') > 0 then
666 if (er_rec.segment20 is null and er_rec_prev.segment20 is null ) or
667 er_rec.segment20 <> er_rec_prev.segment20 then
668 return true;
669 elsif i = g_no_of_attributes then
670 return false;
671 end if;
672 i := i + 1;
673 end if;
674
675 if instr(g_approver_basis, 'segment21') > 0 then
676 if (er_rec.segment21 is null and er_rec_prev.segment21 is null ) or
677 er_rec.segment21 <> er_rec_prev.segment21 then
678 return true;
679 elsif i = g_no_of_attributes then
680 return false;
681 end if;
682 i := i + 1;
683 end if;
684
685 if instr(g_approver_basis, 'segment22') > 0 then
686 if (er_rec.segment22 is null and er_rec_prev.segment22 is null ) or
687 er_rec.segment22 <> er_rec_prev.segment22 then
688 return true;
689 elsif i = g_no_of_attributes then
690 return false;
691 end if;
692 i := i + 1;
693 end if;
694
695 if instr(g_approver_basis, 'segment23') > 0 then
696 if (er_rec.segment23 is null and er_rec_prev.segment23 is null ) or
697 er_rec.segment23 <> er_rec_prev.segment23 then
698 return true;
699 elsif i = g_no_of_attributes then
700 return false;
701 end if;
702 i := i + 1;
703 end if;
704
705 if instr(g_approver_basis, 'segment24') > 0 then
706 if (er_rec.segment24 is null and er_rec_prev.segment24 is null ) or
707 er_rec.segment24 <> er_rec_prev.segment24 then
708 return true;
709 elsif i = g_no_of_attributes then
710 return false;
711 end if;
712 i := i + 1;
713 end if;
714
715 if instr(g_approver_basis, 'segment25') > 0 then
716 if (er_rec.segment25 is null and er_rec_prev.segment25 is null ) or
717 er_rec.segment25 <> er_rec_prev.segment25 then
718 return true;
719 elsif i = g_no_of_attributes then
720 return false;
721 end if;
722 i := i + 1;
723 end if;
724
725 if instr(g_approver_basis, 'segment26') > 0 then
726 if (er_rec.segment26 is null and er_rec_prev.segment26 is null ) or
727 er_rec.segment26 <> er_rec_prev.segment26 then
728 return true;
729 elsif i = g_no_of_attributes then
730 return false;
731 end if;
732 i := i + 1;
733 end if;
734
735 if instr(g_approver_basis, 'segment27') > 0 then
736 if (er_rec.segment27 is null and er_rec_prev.segment27 is null ) or
737 er_rec.segment27 <> er_rec_prev.segment27 then
738 return true;
739 elsif i = g_no_of_attributes then
740 return false;
741 end if;
742 i := i + 1;
743 end if;
744
745 if instr(g_approver_basis, 'segment28') > 0 then
746 if (er_rec.segment28 is null and er_rec_prev.segment28 is null ) or
747 er_rec.segment28 <> er_rec_prev.segment28 then
748 return true;
749 elsif i = g_no_of_attributes then
750 return false;
751 end if;
752 i := i + 1;
753 end if;
754
755 if instr(g_approver_basis, 'segment29') > 0 then
756 if (er_rec.segment29 is null and er_rec_prev.segment29 is null ) or
757 er_rec.segment29 <> er_rec_prev.segment29 then
758 return true;
759 elsif i = g_no_of_attributes then
760 return false;
761 end if;
762 i := i + 1;
763 end if;
764
765 if instr(g_approver_basis, 'segment30') > 0 then
766 if (er_rec.segment30 is null and er_rec_prev.segment30 is null ) or
767 er_rec.segment30 <> er_rec_prev.segment30 then
768 return true;
769 elsif i = g_no_of_attributes then
770 return false;
771 end if;
772 i := i + 1;
773 end if;
774
775 return false;
776 exception
777 when others then
778 fnd_msg_pub.add_exc_msg('PSP_ER_AME','BREAK_GROUP');
779 raise;
780 end;
781
782 procedure insert_into_approvals(p_custom_approvals in varchar2,
783 p_approval_type in varchar2,
784 p_effort_report_detail_id in integer,
785 p_break_attribute in integer,
786 p_break_attribute2 in integer,
787 p_wf_role_name in varchar2,
788 p_wf_orig_system in varchar2,
789 p_wf_orig_system_id in integer,
790 p_er_approval_status in varchar2,
791 p_approver_order_number in integer,
792 p_ame_transaction_id in varchar2,
793 p_approver_display_name in varchar2) is
794
795 l_approval_status varchar2(1) := nvl( p_er_approval_status,'P');
796 l_user_id fnd_user.user_id%type := fnd_global.user_id;
797 l_login_id number:= fnd_global.conc_login_id;
798
799 begin
800 if p_custom_approvals = 'Y' then
801 insert into psp_eff_report_approvals
802 (effort_report_approval_id,
803 effort_report_detail_id,
804 wf_role_name,
805 wf_orig_system_id,
806 wf_orig_system,
807 approver_order_num,
808 approval_status,
809 last_update_date,
810 last_updated_by,
811 last_update_login,
812 creation_date,
813 created_by,
814 wf_role_display_name,
815 object_version_number)
816 values (psp_eff_report_approvals_s.nextval,
817 p_effort_report_detail_id,
818 p_wf_role_name,
819 p_wf_orig_system_id,
820 p_wf_orig_system,
821 p_approver_order_number,
822 l_approval_status,
823 sysdate,
824 l_user_id,
825 l_login_id,
826 sysdate,
827 l_user_id,
828 p_approver_display_name,
829 1);
830 else --- seeded options
831 if p_approval_type in ( 'GPI', 'PMG', 'TMG') then
832 insert into psp_eff_report_approvals
833 (effort_report_approval_id,
834 effort_report_detail_id,
835 wf_role_name,
836 wf_orig_system,
837 wf_orig_system_id,
838 approver_order_num,
839 approval_status,
840 last_update_date,
841 last_updated_by,
842 last_update_login,
843 creation_date,
844 created_by,
845 wf_role_display_name,
846 object_version_number)
847 select psp_eff_report_approvals_s.nextval,
848 effort_report_detail_id,
849 p_wf_role_name,
850 p_wf_orig_system,
851 p_wf_orig_system_id,
852 p_approver_order_number,
853 l_approval_status,
854 sysdate,
855 l_user_id,
856 l_login_id,
857 sysdate,
858 l_user_id,
859 p_approver_display_name,
860 1
861 from psp_eff_report_details erd,
862 psp_eff_reports er
863 where erd.effort_report_id = er.effort_report_id
864 and er.request_id = p_request_id
865 and nvl(investigator_person_id,-999) = nvl(p_break_attribute,-999)
866 and er.person_id between p_start_person and p_end_person
867 and er.status_code = 'N';
868
869 --- Employee or Employee/Supervisor
870 elsif p_approval_type in ('EMP', 'ESU') then
871 hr_utility.trace('psperamb-->emp and esu insert');
872 hr_utility.trace('psperamb-->brk attrib ,rquest id,st person, end person==='||p_break_attribute||'='|| p_request_id||'='|| p_start_person||'='|| p_end_person);
873 insert into psp_eff_report_approvals
874 (effort_report_approval_id,
875 effort_report_detail_id,
876 wf_role_name,
877 wf_orig_system,
878 wf_orig_system_id,
879 approver_order_num,
880 approval_status,
881 last_update_date,
882 last_updated_by,
883 last_update_login,
884 creation_date,
885 created_by,
886 wf_role_display_name,
887 object_version_number)
888 select psp_eff_report_approvals_s.nextval,
889 effort_report_detail_id,
890 p_wf_role_name,
891 p_wf_orig_system,
892 p_wf_orig_system_id,
893 p_approver_order_number,
894 l_approval_status,
895 sysdate,
896 l_user_id,
897 l_login_id,
898 sysdate,
899 l_user_id,
900 p_approver_display_name,
901 1
902 from psp_eff_report_details erd,
903 psp_eff_reports er
904 where erd.effort_report_id = er.effort_report_id
905 and er.request_id = p_request_id
906 and nvl(er.person_id,-999) = nvl(p_break_attribute,-999)
907 and er.person_id between p_start_person and p_end_person
908 and er.status_code = 'N';
909
910 hr_utility.trace('psperamb-->emp and esu insert -1');
911
912 elsif p_approval_type in ('SUP') then
913 insert into psp_eff_report_approvals
914 (effort_report_approval_id,
915 effort_report_detail_id,
916 wf_role_name,
917 wf_orig_system,
918 wf_orig_system_id,
919 approver_order_num,
920 approval_status,
921 last_update_date,
922 last_updated_by,
923 last_update_login,
924 creation_date,
925 created_by,
926 wf_role_display_name,
927 object_version_number)
928 select psp_eff_report_approvals_s.nextval,
929 effort_report_detail_id,
930 p_wf_role_name,
931 p_wf_orig_system,
932 p_wf_orig_system_id,
933 p_approver_order_number,
934 l_approval_status,
935 sysdate,
936 l_user_id,
937 l_login_id,
938 sysdate,
939 l_user_id,
940 p_approver_display_name,
941 1
942 from psp_eff_report_details erd,
943 psp_eff_reports er,
944 per_all_assignments_f asg
945 where erd.effort_report_id = er.effort_report_id
946 and er.request_id = p_request_id
947 and er.person_id between p_start_person and p_end_person
948 and er.status_code = 'N'
949 and asg.person_id = er.person_id
950 and asg.assignment_type ='E'
951 and trunc(er.end_date) between asg.effective_start_date
952 and asg.effective_end_date
953 and asg.primary_flag = 'Y'
954 and nvl(asg.supervisor_id,-999) = nvl(p_break_attribute2,-999);
955
956 end if;
957 end if;
958 exception
959 when others then
960 fnd_msg_pub.add_exc_msg('PSP_ER_AME','INSERT_INTO_APPROVALS');
961 raise;
962 end;
963
964 --- procedure to populate the PSP_REPORT_ERRORS table.
965 procedure populate_errors(p_approval_type in varchar2,
966 p_sup_levels in number,
967 p_group_attribute in number,
968 p_group_attribute2 in number,
969 p_er_detail_id in number,
970 p_ame_transaction_id in varchar2,
971 p_sqlerrm in varchar2,
972 p_error_out out NOCOPY varchar2) is
973
974
975 cursor er_PTA_cur(p_effort_Report_detail_id integer) is
976 select project_id, task_id, award_id
977 from psp_eff_Report_details
978 where effort_report_Detail_id = p_effort_Report_Detail_id;
979
980 er_pta_rec er_pta_cur%rowtype;
981
982 cursor task_manager_error is
983 select er.person_id,
984 erd.task_number,
985 erd.assignment_number
986 from psp_eff_report_details erd,
987 psp_eff_reports er
988 where erd.effort_report_id = er.effort_report_id
989 and er.request_id = p_request_id
990 and nvl(erd.task_id,-9999) = nvl(p_group_attribute, -9999)
991 and er.person_id between p_start_person and p_end_person
992 group by er.person_id,
993 erd.task_number,
994 erd.assignment_number;
995 task_rec task_manager_error%rowtype;
996
997 cursor project_manager_error is
998 select er.person_id,
999 erd.project_number,
1000 erd.assignment_number
1001 from psp_eff_report_details erd,
1002 psp_eff_reports er
1003 where erd.effort_report_id = er.effort_report_id
1004 and er.request_id = p_request_id
1005 and nvl(erd.project_id,-9999) = nvl(p_group_attribute, -9999)
1006 and er.person_id between p_start_person and p_end_person
1007 group by er.person_id,
1008 erd.project_number,
1009 erd.assignment_number;
1010 project_rec project_manager_error%rowtype;
1011
1012 cursor principal_investigator_error(p_request_id integer,
1013 p_group_attribute integer) is
1014 select er.person_id,
1015 erd.award_number,
1016 erd.assignment_number
1017 from psp_eff_report_details erd,
1018 psp_eff_reports er
1019 where erd.effort_report_id = er.effort_report_id
1020 and er.request_id = p_request_id
1021 and nvl(erd.award_id,-9999) = nvl(p_group_attribute, -9999)
1022 and er.person_id between p_start_person and p_end_person
1023 group by er.person_id,
1024 erd.award_number,
1025 erd.assignment_number;
1026
1027 cursor get_supervisor_1_emps is
1028 select distinct er.person_id
1029 from psp_eff_reports er,
1030 psp_eff_report_details erd
1031 where er.effort_report_id = erd.effort_report_id
1032 and erd.ame_transaction_id = p_ame_transaction_id
1033 and er.person_id between p_start_person and p_end_person
1034 and er.request_id = p_request_id;
1035
1036 award_rec principal_investigator_error%rowtype;
1037 l_person_id per_all_people_f.person_id%type;
1038
1039 type t_error_source_id is table of integer index by binary_integer;
1040 type t_error_message is table of psp_report_errors.error_message%type
1041 index by binary_integer;
1042
1043 t_source_id t_error_source_id;
1044 t_err_mesg t_error_message;
1045 i integer;
1046 l_incorrect_apr_type_msg varchar2(4000);
1047
1048
1049 procedure insert_errors is
1050 pragma autonomous_transaction;
1051 l_sqlerrm psp_report_errors.error_message%type;
1052 begin
1053 p_error_out := NULL;
1054
1055 forall i in 1..t_source_id.count
1056 insert into psp_report_errors (error_sequence_id,
1057 request_id,
1058 message_level,
1059 source_id,
1060 error_message,
1061 retry_request_id)
1062 values (psp_report_errors_s.nextval,
1063 p_request_id,
1064 'E',
1065 t_source_id(i) ,
1066 t_err_mesg(i),
1067 p_retry_request_id);
1068 commit;
1069 exception
1070 when others then
1071 l_sqlerrm := sqlerrm;
1072 hr_utility.trace('PSPERAMB-->POPULATER_ERRORS--> INSERT_ERRORS When others='||l_sqlerrm);
1073 insert into psp_report_errors
1074 (error_sequence_id,
1075 request_id,
1076 message_level,
1077 source_id,
1078 error_message,
1079 retry_request_id)
1080 select psp_report_errors_s.nextval,
1081 p_request_id,
1082 'E',
1083 null ,
1084 'Package, procedure = PSP_ER_AME,insert_errors-->ERROR inserting into psp_report_errors ',
1085 p_retry_request_id
1086 from dual;
1087 commit;
1088 end;
1089
1090
1091
1092 begin
1093 if (approval_type_rec.approval_type in ('GPI','TMG','PMG')) then
1094 open er_PTA_cur(p_er_detail_id);
1095 fetch er_PTA_cur into er_PTA_rec;
1096 close er_PTA_cur;
1097 if (er_PTA_rec.project_id is null and approval_type_rec.approval_type = 'PMG') or
1098 (er_PTA_rec.award_id is null and approval_type_rec.approval_type = 'GPI') or
1099 (er_PTA_rec.task_id is null and approval_type_rec.approval_type = 'TMG') then
1100 fnd_message.set_name('PSP','PSP_ER_AME_WRONG_APR_TYP');
1101 l_incorrect_apr_type_msg := substr(fnd_message.get,1,2000);
1102 end if;
1103 end if;
1104 hr_utility.trace('psperamb--> POPULATE ERROR p_approval_type ='|| p_approval_type ||
1105 ', p_sup_levels ='|| p_sup_levels ||
1106 ', p_group_attribute ='|| p_group_attribute ||
1107 ', p_group_attribute2 ='|| p_group_attribute2 ||
1108 ', p_er_detail_id ='|| p_er_detail_id ||
1109 ', p_ame_transaction_id ='||p_ame_transaction_id ||
1110 ', p_sqlerrm ='|| p_sqlerrm||
1111 ', approval_type_rec.approval_type ='|| approval_type_rec.approval_type||
1112 ', p_request_id =' || p_request_id);
1113
1114
1115 if approval_type_rec.approval_type = 'EMP' then
1116 fnd_message.set_name('PSP','PSP_ER_AME_EMP_APPRV_ERR');
1117 fnd_message.set_token('Error',p_sqlerrm);
1118 l_err_mesg := substr(fnd_message.get,1,2000);
1119 insert_error(p_request_id, 'E', p_group_attribute, l_err_mesg, p_retry_request_id);
1120 elsif approval_type_rec.approval_type = 'ESU' then
1121 fnd_message.set_name('PSP','PSP_ER_AME_ESU_APPRV_ERR');
1122 fnd_message.set_token('Error',p_sqlerrm);
1123 l_err_mesg := substr(fnd_message.get,1,2000);
1124 insert_error(p_request_id, 'E', p_group_attribute, l_err_mesg, p_retry_request_id);
1125 elsif approval_type_rec.approval_type = 'SUP' then
1126 fnd_message.set_name('PSP','PSP_ER_AME_SUP_APPRV_ERR');
1127 fnd_message.set_token('Error',p_sqlerrm);
1128 l_err_mesg := substr(fnd_message.get,1,2000);
1129 open get_supervisor_1_emps;
1130 fetch get_supervisor_1_emps bulk collect into t_source_id;
1131 close get_supervisor_1_emps;
1132 if t_source_id.count > 0 then
1133 i := 1;
1134 loop
1135 if i > t_source_id.count then
1136 exit;
1137 end if;
1138 t_err_mesg(i) := l_err_mesg;
1139 i := i + 1;
1140 end loop;
1141 end if;
1142 insert_errors;
1143
1144 elsif approval_type_rec.approval_type = 'TMG' then
1145 open task_manager_error;
1146 i := 1;
1147 loop
1148 fetch task_manager_error into task_rec;
1149 if task_manager_error%notfound then
1150 close task_manager_error;
1151 exit;
1152 end if;
1153 if l_incorrect_apr_type_msg is not null then
1154 t_err_mesg(i) := l_incorrect_apr_type_msg;
1155 t_source_id(i) := task_rec.person_id;
1156 else
1157 fnd_message.set_name('PSP','PSP_ER_AME_TMG_APPRV_ERR');
1158 fnd_message.set_token('TASK_NUMBER',task_rec.task_number);
1159 fnd_message.set_token('ASG_NUMBER',task_rec.assignment_number);
1160 fnd_message.set_token('Error',p_sqlerrm);
1161 l_err_mesg := substr(fnd_message.get,1,2000);
1162 t_source_id(i) := task_rec.person_id;
1163 t_err_mesg(i) := l_err_mesg;
1164 end if;
1165 i := i + 1;
1166 end loop;
1167 if i > 1 then
1168 insert_errors;
1169 end if;
1170
1171
1172 elsif approval_type_rec.approval_type = 'PMG' then
1173 open project_manager_error;
1174 i := 1;
1175 loop
1176 fetch project_manager_error into project_rec;
1177 if project_manager_error%notfound then
1178 close project_manager_error;
1179 exit;
1180 end if;
1181
1182 if l_incorrect_apr_type_msg is not null then
1183 t_err_mesg(i) := l_incorrect_apr_type_msg;
1184 t_source_id(i) := project_rec.person_id;
1185 else
1186 fnd_message.set_name('PSP','PSP_ER_AME_PMG_APPRV_ERR');
1187 fnd_message.set_token('PROJECT_NUMBER',project_rec.project_number);
1188 fnd_message.set_token('ASG_NUMBER',project_rec.assignment_number);
1189 fnd_message.set_token('Error',p_sqlerrm);
1190 l_err_mesg := substr(fnd_message.get,1,2000);
1191 t_source_id(i) := project_rec.person_id;
1192 t_err_mesg(i) := l_err_mesg;
1193 end if;
1194
1195 i := i + 1;
1196 end loop;
1197 if i > 1 then
1198 insert_errors;
1199 end if;
1200 elsif approval_type_rec.approval_type = 'GPI' then
1201 i := 1;
1202 open principal_investigator_error(p_request_id, p_group_attribute);
1203 loop
1204 fetch principal_investigator_error into award_rec;
1205 if principal_investigator_error%notfound then
1206 close principal_investigator_error;
1207 exit;
1208 end if;
1209 if l_incorrect_apr_type_msg is not null then
1210 t_err_mesg(i) := l_incorrect_apr_type_msg;
1211 t_source_id(i) := award_rec.person_id;
1212 else
1213 fnd_message.set_name('PSP','PSP_ER_AME_GPI_APPRV_ERR');
1214 fnd_message.set_token('AWARD_NUMBER',award_rec.award_number);
1215 fnd_message.set_token('ASG_NUMBER',award_rec.assignment_number);
1216 fnd_message.set_token('Error',p_sqlerrm);
1217 l_err_mesg := substr(fnd_message.get,1,2000);
1218 hr_utility.trace('before insert into GPI');
1219 t_source_id(i) := award_rec.person_id;
1220 t_err_mesg(i) := l_err_mesg;
1221 end if;
1222 i := i + 1;
1223 end loop;
1224 if i > 1 then
1225 insert_errors;
1226 end if;
1227 end if;
1228 hr_utility.trace('PSPERAMB-->POPULATER_ERRORS--> COMMIT');
1229 exception
1230 when others then
1231 p_error_out := sqlerrm;
1232 p_error_out := 'Error in PSP_ER_AME - POPULATE_ERRORS '||p_error_out ;
1233 ---fnd_msg_pub.add_exc_msg('PSP_ER_AME','POPULATE_ERRORS');
1234 hr_utility.trace('psperamb--> POPULATE ERROR when others:'||p_error_out);
1235 p_error_out := substr(p_error_out,1,2000);
1236 insert_error(p_request_id, 'E', null, p_error_out, p_retry_request_id);
1237 raise;
1238 end;
1239
1240
1241 ---============= END Local Procedures =============---
1242
1243 ---- BEGIN main procedure body.
1244 begin
1245
1246 --hr_utility.trace_on('Y','ORACLE');
1247 g_retry_request_id := p_retry_request_id;
1248 hr_utility.trace( 'psperamb-->Start rqid, stp, end person==='||p_request_id ||'='|| p_start_person ||'='|| p_end_person );
1249 l_no_approver_found := false;
1250
1251 open wf_approval_cur;
1252 --- need to check if it moves to history at this point
1253 fetch wf_approval_cur into approval_type_rec;
1254 close wf_approval_cur;
1255
1256 if nvl(approval_type_rec.workflow_approval_req_flag,'N') = 'N' then
1257
1258 open get_report_layout;
1259 fetch get_report_layout into l_report_layout_code;
1260 close get_report_layout;
1261
1262 if l_report_layout_code in ('PIV','PMG', 'TMG') then
1263 l_counter := 0;
1264 open check_valid_prj;
1265 loop
1266 fetch check_valid_prj into l_err_person_id;
1267 if check_valid_prj%notfound then
1268 close check_valid_prj;
1269 exit;
1270 end if;
1271 fnd_message.set_name('PSP','PSP_ER_WRNG_RPT_LAYOUT');
1272 l_error_out := substr(fnd_message.get,1,2000);
1273 insert_error(p_request_id, 'E', l_err_person_id, l_error_out, p_retry_request_id);
1274 l_counter := l_counter + 1;
1275 end loop;
1276 if l_counter > 0 then
1277 p_return_status := fnd_api.g_ret_sts_error;
1278 return;
1279 end if;
1280
1281 if l_report_layout_code = 'PIV' then
1282 open check_non_sponsered_prj;
1283 loop
1284 fetch check_non_sponsered_prj into l_err_person_id;
1285 if check_non_sponsered_prj%notfound then
1286 close check_non_sponsered_prj;
1287 exit;
1288 end if;
1289 fnd_message.set_name('PSP','PSP_ER_WRNG_RPT_LAYOUT');
1290 l_error_out := substr(fnd_message.get,1,2000);
1291 insert_error(p_request_id, 'E', l_err_person_id, l_error_out, p_retry_request_id);
1292 l_counter := l_counter + 1;
1293 end loop;
1294 end if;
1295 if l_counter > 0 then
1296 p_return_status := fnd_api.g_ret_sts_error;
1297 return;
1298 end if;
1299 l_counter := 0;
1300 end if;
1301 hr_utility.trace( 'psperamb--> Workflow approval not reqd .. exiting' );
1302 update psp_eff_reports
1303 set status_code = 'A'
1304 where status_code = 'N'
1305 and request_id = p_request_id
1306 and person_id between p_start_person and p_end_person;
1307 p_return_status := fnd_api.g_ret_sts_success;
1308 return;
1309 end if;
1310
1311 if approval_type_rec.approval_type = 'CUS' then
1312 hr_utility.trace ('psperamb--> Entered Custom approval');
1313
1314 --- ensure g_approver_basis will have lower case characters
1315 g_approver_basis := lower(g_approver_basis);
1316 sql_string := 'select dtls.effort_report_detail_id,
1317 rep.person_id '
1318 ||make_select||
1319 ' from psp_eff_report_details dtls,
1320 psp_eff_reports rep
1321 where rep.effort_report_id = dtls.effort_report_id
1322 and rep.status_code ='|| ''''||'N'||'''' || '
1323 and rep.request_id = :1
1324 and rep.person_id between :1 and :2 ';
1325 hr_utility.trace('psperamb--> custom select string='||sql_string);
1326 if trim(rtrim(g_approver_basis)) is not null then
1327 sql_string := sql_string ||' order by '||g_approver_basis;
1328 else
1329 g_approver_basis := null;
1330 end if;
1331
1332 l_counter := 0;
1333 open er_cur for sql_string using p_request_id, p_start_person, p_end_person;
1334 loop
1335 fetch er_cur into er_rec;
1336 if er_cur%notfound then
1337 close er_cur;
1338 exit;
1339 end if;
1340 t_erd_id(l_counter) := er_rec.effort_report_detail_id;
1341 l_ame_txn_id := rpad(approval_type_rec.custom_approval_code,30)||rpad(' ',5)||
1342 lpad(er_rec.effort_report_detail_id,15);
1343 hr_utility.trace('psperamb-->'||er_rec.person_id||'--'||er_rec.assignment_id||'--'||er_rec.project_id);
1344 begin
1345 hr_utility.trace ('psperamb-->size, AME TXN Id before ame call-102 length, txn_id ='||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1346 if g_approver_basis is not null then
1347 if break_group then
1348 forall i in 1..t_erd_id.count
1349 update psp_eff_report_details
1350 set ame_transaction_id = l_ame_txn_id
1351 where effort_report_detail_id = t_erd_id(i);
1352 ame_api2.getNextApprovers4(applicationidIn => 8403,
1353 transactiontypeIn => 'PSP-ER-APPROVAL',
1354 transactionIdIn => l_ame_txn_id,
1355 flagApproversAsNotifiedIn => 'Y',
1356 approvalProcessCompleteYNout => l_process_complete,
1357 nextApproversOut=> l_next_approver);
1358 l_counter := 0;
1359 end if;
1360 else
1361 update psp_eff_report_details
1362 set ame_transaction_id = l_ame_txn_id
1363 where effort_report_detail_id = er_rec.effort_report_detail_id;
1364
1365 ame_api2.getNextApprovers4(applicationidIn => 8403,
1366 transactiontypeIn => 'PSP-ER-APPROVAL',
1367 transactionIdIn => l_ame_txn_id,
1368 flagApproversAsNotifiedIn => 'Y',
1369 approvalProcessCompleteYNout => l_process_complete,
1370 nextApproversOut=> l_next_approver);
1371 end if;
1372 exception
1373 when others then
1374 l_sqlerrm := sqlerrm;
1375 hr_utility.trace(sqlerrm);
1376 fnd_msg_pub.add_exc_msg('PSP_ER_AME','GET_FIRST_APPROVER - AME CUSTOM ERR');
1377 fnd_message.set_name('PSP','PSP_ER_AME_CUST_APPRV_ERR');
1378 fnd_message.set_token('Error',l_sqlerrm);
1379 l_err_mesg := substr(fnd_message.get,1,2000);
1380 insert_error(p_request_id,
1381 'E',
1382 null ,
1383 l_err_mesg, p_retry_request_id);
1384 begin
1385 select er.person_id
1386 into l_error_out
1387 from psp_eff_reports er,
1388 psp_eff_report_details erd
1389 where er.effort_report_id = erd.effort_report_id
1390 and er.request_id = p_request_id
1391 and erd.effort_report_detail_id = er_rec.effort_report_detail_id;
1392 l_sqlerrm := substr(l_sqlerrm,1,2000);
1393 insert_error(p_request_id,
1394 'E',
1395 l_error_out,
1396 l_sqlerrm, p_retry_request_id);
1397 ---commit;
1398 exception
1399 when others then
1400 null;
1401 end;
1402 hr_utility.trace ('psperamb-->exception when others: '||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1403 ---raise;
1404 p_return_status := fnd_api.g_ret_sts_unexp_error;
1405 end;
1406 update psp_eff_report_details
1407 set ame_transaction_id = l_ame_txn_id
1408 where effort_report_detail_id = er_rec.effort_report_detail_id;
1409 i := 1;
1410 loop
1411 if i > l_next_approver.count then
1412 exit;
1413 end if;
1414 insert_into_approvals('Y',
1415 approval_type_rec.approval_type,
1416 er_rec.effort_report_detail_id,
1417 null,
1418 null,
1419 l_next_approver(i).name,
1420 l_next_approver(i).orig_system,
1421 l_next_approver(i).orig_system_id,
1422 l_next_approver(i).approval_status,
1423 l_next_approver(i).approver_order_number,
1424 l_ame_txn_id,
1425 l_next_approver(i).display_name);
1426 i := i + 1; --- i > 1 only for parallel approvers
1427 end loop;
1428 er_rec_prev := er_rec;
1429 end loop;
1430
1431 hr_utility.trace('psperamb--> **** CUSTOM APPROVAL TYPE **** make_select return='||make_select);
1432 p_return_status := fnd_api.g_ret_sts_success;
1433 return;
1434 else
1435 hr_utility.trace('psperamb-->Seeded options approval type='||
1436 approval_type_rec.approval_type||' Worflow approval reqd '||
1437 approval_type_rec.workflow_approval_req_flag);
1438 --- seeded approval options
1439 if approval_type_rec.approval_type in ( 'GPI', 'PMG', 'TMG') then
1440 open er_cur_gpi_pmg_tmg;
1441 fetch er_cur_gpi_pmg_tmg bulk collect into break_rec.array_detail_id,
1442 break_rec.array_break_attribute,
1443 break_rec.array_break_attribute2;
1444 close er_cur_gpi_pmg_tmg;
1445 elsif approval_type_rec.approval_type in ( 'EMP', 'ESU') then
1446 hr_utility.trace('psperamb-->to open EMP Cursor ');
1447 open er_cur_emp;
1448 fetch er_cur_emp bulk collect into break_rec.array_detail_id,
1449 break_rec.array_break_attribute,
1450 break_rec.array_break_attribute2;
1451 close er_cur_emp;
1452 elsif approval_type_rec.approval_type = 'SUP' then
1453 open er_cur_sup_1;
1454 fetch er_cur_sup_1 bulk collect into break_rec.array_detail_id,
1455 break_rec.array_break_attribute,
1456 break_rec.array_break_attribute2;
1457 close er_cur_sup_1;
1458 end if;
1459 end if;
1460 if break_rec.array_detail_id.count = 0 then
1461 hr_utility.trace('psperamb-->no data found in the seed break groups ');
1462 --- nothing to process for this chunk
1463 p_return_status := fnd_api.g_ret_sts_success;
1464 return;
1465 end if;
1466
1467 hr_utility.trace('psperamb-->count='||break_rec.array_detail_id.count);
1468 l_counter := 1;
1469 loop
1470 if l_counter > break_rec.array_detail_id.count then
1471 exit;
1472 end if;
1473
1474
1475 l_ame_txn_id := 'SEED-'|| rpad(approval_type_rec.approval_type,15)
1476 || rpad(nvl(break_rec.array_break_attribute(l_counter),-9),15)
1477 || lpad(break_rec.array_detail_id(l_counter),15);
1478
1479 if approval_type_rec.approval_type = 'GPI'
1480 or approval_type_rec.approval_type = 'PMG'
1481 or approval_type_rec.approval_type = 'TMG' then
1482 update psp_eff_report_details
1483 set ame_transaction_id = l_ame_txn_id
1484 where nvl(investigator_person_id,-999) = nvl(break_rec.array_break_attribute(l_counter),-999)
1485 and effort_report_id in
1486 (select effort_report_id
1487 from psp_eff_reports er
1488 where er.request_id = p_request_id
1489 and er.person_id between p_start_person and p_end_person
1490 and er.status_code = 'N');
1491 --- Employee or Employee/Supervisor
1492 elsif approval_type_rec.approval_type in ('EMP', 'ESU') then
1493 update psp_eff_report_details
1494 set ame_transaction_id = l_ame_txn_id
1495 where effort_report_id in
1496 (select erd.effort_report_id
1497 from psp_eff_report_details erd
1498 where erd.effort_report_detail_id = break_rec.array_detail_id(l_counter));
1499 elsif approval_type_rec.approval_type in ('SUP') then
1500 update psp_eff_report_details erd
1501 set erd.ame_transaction_id = l_ame_txn_id
1502 where erd.effort_report_id in
1503 (select er.effort_report_id
1504 from psp_eff_reports er,
1505 per_all_assignments_f asg
1506 where er.request_id = p_request_id
1507 and er.person_id between p_start_person and p_end_person
1508 and er.status_code = 'N'
1509 and asg.person_id = er.person_id
1510 and asg.assignment_type ='E'
1511 and trunc(er.end_date) between asg.effective_start_date
1512 and asg.effective_end_date
1513 and asg.primary_flag = 'Y'
1514 and nvl(asg.supervisor_id,-999) = nvl(break_rec.array_break_attribute2(l_counter),-999));
1515 end if;
1516
1517 if approval_type_rec.approval_type not in ('GPI' , 'PMG' , 'TMG') then
1518 hr_utility.trace ('psperamb-->size,AME TXN Id='||length( l_ame_txn_id)||','||l_ame_Txn_id);
1519
1520 begin
1521 hr_utility.trace ('psperamb-->size, AME TXN Id before ame call-101= length, txn_id '||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1522
1523 ame_api2.getNextApprovers4(applicationidIn => 8403,
1524 transactiontypeIn => 'PSP-ER-APPROVAL',
1525 transactionIdIn => l_ame_txn_id,
1526 flagApproversAsNotifiedIn => 'N',
1527 approvalProcessCompleteYNout => l_process_complete,
1528 nextApproversOut=> l_next_approver);
1529 hr_utility.trace ('psperamb-->2L='||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1530 exception
1531 when others then
1532 l_sqlerrm := sqlerrm;
1533 hr_utility.trace(l_sqlerrm);
1534 hr_utility.trace ('psperamb-->L ERR 1000='||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1535 fnd_msg_pub.add_exc_msg('PSP_ER_AME','GET_FIRST_APPROVER - AME CALL ERR');
1536 l_sqlerrm := substr(l_Sqlerrm,1,1700);
1537 l_no_approver_found := true;
1538 /* insert_error(p_request_id,
1539 'E',
1540 null,
1541 l_sqlerrm); */
1542 populate_errors(approval_type_rec.approval_type,
1543 approval_type_rec.sup_levels,
1544 break_rec.array_break_attribute(l_counter),
1545 break_rec.array_break_attribute2(l_counter),
1546 break_rec.array_detail_id(l_counter),
1547 l_ame_Txn_id,
1548 l_sqlerrm,
1549 l_error_out);
1550 if l_error_out is not null then
1551 raise populate_error;
1552 end if;
1553 end;
1554
1555 if l_sqlerrm is null then
1556 if l_next_approver.count = 0 then
1557 hr_utility.trace ('psperamb-->#### APPRVL COUNT ZERO #### TXN Length, Id ='||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1558 --- AME did not return approver.
1559 fnd_msg_pub.add_exc_msg('PSP_ER_AME','GET_FIRST_APPROVER - AME CALL');
1560 l_no_approver_found := true;
1561 populate_errors(approval_type_rec.approval_type,
1562 approval_type_rec.sup_levels,
1563 break_rec.array_break_attribute(l_counter),
1564 break_rec.array_break_attribute2(l_counter),
1565 break_rec.array_detail_id(l_counter),
1566 l_ame_Txn_id,
1567 l_sqlerrm,
1568 l_error_out);
1569 if l_error_out is not null then
1570 raise populate_error;
1571 end if;
1572 hr_utility.trace ('psperamb-->#### APPRVL COUNT ZERO #### After Populate Error');
1573 else
1574 i := 1;
1575 loop
1576 if i > l_next_approver.count then
1577 hr_utility.trace ('psperamb-->L= inside array loop'||l_ame_Txn_id);
1578 exit;
1579 end if;
1580
1581 insert_into_approvals('N',
1582 approval_type_rec.approval_type,
1583 break_rec.array_detail_id(l_counter),
1584 break_rec.array_break_attribute(l_counter),
1585 break_rec.array_break_attribute2(l_counter),
1586 l_next_approver(i).name,
1587 l_next_approver(i).orig_system,
1588 l_next_approver(i).orig_system_id,
1589 l_next_approver(i).approval_status,
1590 l_next_approver(i).approver_order_number,
1591 l_ame_txn_id,
1592 l_next_approver(i).display_name);
1593 i := i + 1; --- i > 1 only for parallel approvers
1594 end loop;
1595 end if;
1596 else
1597 l_sqlerrm := null;
1598 end if;
1599 else
1600 wf_directory.getUserName('PER',
1601 break_rec.array_break_attribute(l_counter),
1602 l_next_approver(1).name,
1603 l_next_approver(1).display_name);
1604
1605 l_next_approver(1).approval_status := 'P';
1606 l_next_approver(1).approver_order_number := 1;
1607 l_next_approver(1).orig_system := 'PER';
1608 l_next_approver(1).orig_system_id := break_rec.array_break_attribute(l_counter);
1609
1610 insert_into_approvals('N',
1611 approval_type_rec.approval_type,
1612 break_rec.array_detail_id(l_counter),
1613 break_rec.array_break_attribute(l_counter),
1614 break_rec.array_break_attribute2(l_counter),
1615 l_next_approver(1).name,
1616 l_next_approver(1).orig_system,
1617 l_next_approver(1).orig_system_id,
1618 l_next_approver(1).approval_status,
1619 l_next_approver(1).approver_order_number,
1620 l_ame_txn_id,
1621 l_next_approver(1).display_name);
1622
1623 /*
1624 approver_rec.name := l_next_approver(1).name;
1625 approver_rec.orig_system := l_next_approver(1).orig_system;
1626 approver_rec.orig_system_id := l_next_approver(1).orig_system_id;
1627 approver_rec.approval_status:= ame_util.notifiedstatus;
1628 ame_api2.updateapprovalstatus(applicationidin => 8403,
1629 transactiontypein => 'PSP-ER-APPROVAL',
1630 transactionidin => l_ame_txn_id,
1631 approverin => approver_rec); */
1632 end if;
1633 l_counter := l_counter + 1;
1634 end loop;
1635 break_rec.array_detail_id.delete;
1636 break_rec.array_break_attribute.delete;
1637 break_rec.array_break_attribute2.delete;
1638 if l_no_approver_found then
1639 raise no_approver_found;
1640 else
1641 p_return_status := fnd_api.g_ret_sts_success;
1642 end if;
1643 hr_utility.trace_off;
1644 exception
1645 -- removed the usage of this exception.. LOOP to continue till it finishes the
1646 -- all eff records.
1647 when no_approver_found then
1648 hr_utility.trace ('psperamb--> no_approver_found: '||length( l_ame_txn_id)||','||l_ame_Txn_id);
1649 ----raise;
1650 --- commit;
1651 fnd_msg_pub.add_exc_msg('PSP_ER_AME','GET_FIRST_APPROVER NO-APP-FOUND');
1652 p_return_status := fnd_api.g_ret_sts_error;
1653 when populate_error then
1654 fnd_msg_pub.add_exc_msg('PSP_ER_AME','', l_error_out);
1655 p_return_status := fnd_api.g_ret_sts_error;
1656 when others then
1657 fnd_msg_pub.add_exc_msg('PSP_ER_AME','GET_FIRST_APPROVER');
1658 l_sqlerrm := sqlerrm;
1659 hr_utility.trace(l_sqlerrm);
1660 begin
1661 select er.person_id
1662 into l_error_out
1663 from psp_eff_reports er,
1664 psp_eff_report_details erd
1665 where er.effort_report_id = erd.effort_report_id
1666 and er.request_id = p_request_id
1667 and erd.effort_report_detail_id = break_rec.array_detail_id(l_counter);
1668 l_sqlerrm := substr(l_sqlerrm,1,2000);
1669 insert_error(p_request_id,
1670 'E',
1671 l_error_out,
1672 l_sqlerrm, p_retry_request_id);
1673 ---commit;
1674 exception
1675 when others then
1676 null;
1677 end;
1678 hr_utility.trace ('psperamb-->exception when others: '||length( l_ame_txn_id)||' '||l_ame_Txn_id);
1679 ---raise;
1680 p_return_status := fnd_api.g_ret_sts_unexp_error;
1681 end;
1682 end;