[Home] [Help]
PACKAGE BODY: APPS.PSP_CREATE_EFF_REPORTS
Source
1 PACKAGE BODY PSP_CREATE_EFF_REPORTS AS
2 /* $Header: PSPERCRB.pls 120.21 2006/08/04 11:28:55 dpaudel noship $*/
3
4
5 PROCEDURE effort_asg_action_code (p_pactid IN NUMBER,
6 stperson IN NUMBER,
7 endperson IN NUMBER,
8 p_chunk_num IN NUMBER) IS
9
10 l_object_id PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
11 l_object_type varchar2(3) :='PER';
12 l_cnt number;
13 l_asgactid number;
14
15
16 CURSOR c_person_actions(p_pactid number, stperson NUMBER, endperson NUMBER) is
17 SELECT person_id from psp_selected_persons_t where
18 person_id between stperson and endperson and request_id in
19 (select request_id from pay_payroll_actions where payroll_action_id= p_pactid);
20
21 begin
22
23 --fnd_file.put_line(fnd_file.log,'ST===='||stperson||'--END='||endperson);
24
25
26 open c_person_actions(p_pactid, stperson, endperson);
27
28 fetch c_person_actions BULK COLLECT into person_array.person_id;
29
30 CLOSE c_person_actions;
31
32
33 FOR i in 1..person_array.person_id.count
34
35 LOOP
36
37 select pay_assignment_actions_s.nextval into l_asgactid from dual;
38
39 hr_nonrun_asact.insact( l_asgactid ,
40 pactid => p_pactid,
41 chunk => p_chunk_num,
42 object_id => person_array.person_id(i),
43 object_type => l_object_type,
44 p_transient_action => TRUE);
45 END LOOP;
46
47
48 select count(object_id) into l_cnt from pay_temp_object_actions where
49 payroll_action_id = p_pactid;
50
51 -- insert into psp_debug values(null, l_cnt);
52
53 end;
54
55
56
57 PROCEDURE PSPREP_INIT(p_payroll_action_id IN NUMBER) IS
58
59 param_string PAY_PAYROLL_ACTIONS.LEGISLATIVE_PARAMETERS%TYPE;
60
61
62 BEGIN
63
64
65 /* get the required parameters */
66
67
68 SELECT request_id, legislative_parameters into g_psp_request_id, param_string from pay_payroll_actions where payroll_action_id = p_payroll_action_id;
69
70 g_psp_template_id := psp_template_selection.get_parameter_value('TEMPLATE_ID', param_string);
71
72 g_psp_effort_start:= trunc(fnd_date.canonical_to_date(psp_template_selection.get_parameter_value('START_DATE', param_string)));
73 g_psp_effort_end:= trunc(fnd_date.canonical_to_date(psp_template_selection.get_parameter_value('END_DATE', param_string)));
74
75
76
77 -- fnd_file.put_line(fnd_file.log, 'start date i s '||g_psp_effort_start);
78 -- fnd_file.put_line(fnd_file.log, 'end date is '||g_psp_effort_end);
79
80
81 /* insert record in psp_report_templates_h */
82
83
84
85 END;
86
87
88
89 PROCEDURE effort_archive(payroll_action_id IN NUMBER,
90 chunk_number in NUMBER) IS
91 errBuf varchar2(240);
92 retcode varchar2(1);
93
94 begin
95
96
97 --hr_utility.trace_on('Y','ORACLE');
98
99 --fnd_file.put_line(fnd_file.log,' inside archiver' );
100 --fnd_file.put_line(fnd_file.log,' chunk is '||chunk_number );
101 create_effort_reports(errBuf, retcode, payroll_action_id, g_psp_request_id, chunk_number);
102
103 end;
104
105
106 PROCEDURE CREATE_EFFORT_REPORTS(
107 errBuf OUT NOCOPY VARCHAR2,
108 retCode OUT NOCOPY VARCHAR2,
109 p_pactid IN NUMBER,
110 p_request_id IN NUMBER,
111 p_chunk_num IN NUMBER
112 ) AS
113
114 BEGIN
115
116
117
118
119 populate_eff_tables(errBuf ,
120 retCode ,
121 p_pactid,
122 p_request_id ,
123 p_chunk_num
124 );
125
126
127
128 /*
129
130 p_eff_report_details_api.update_eff_report_details(p_validate , p_request_id )
131
132
133 */
134
135
136 end;
137
138 PROCEDURE POPULATE_EFF_TABLES(errBuf OUT NOCOPY VARCHAR2,
139 retCode OUT NOCOPY VARCHAR2,
140 p_pactid IN NUMBER,
141 p_request_id IN NUMBER,
142 p_chunk_num IN NUMBER,
143 p_supercede_mode IN VARCHAR2 --- supercede
144
145 )
146 AS
147
148 l_template_id NUMBER;
149 i NUMBER;
150
151 CURSOR get_summarization_criteria(p_request_id IN NUMBER) is Select criteria_lookup_code, criteria_value1
152 from psp_report_template_details_h where request_id = p_request_id and
153 criteria_lookup_type='PSP_SUMMARIZATION_CRITERIA' order by
154 to_number(criteria_value1);
155
156
157 p_effort_Start DATE;
158 p_effort_end DATE;
159
160 l_loop_count INTEGER :=0;
161 l_msg_count NUMBER;
162 l_msg_data VARCHAR2(2000);
163 min_effort_report_id number;
164 l_sqlerrm varchar2(240);
165 p_validate BOOLEAN :=FALSE;
166 p_warning BOOLEAN;
167
168 l_grp_string varchar2(4000);
169 l_asg_ci_string varchar2(4000);
170 l_select_string varchar2(4000);
171 l_start_person number;
172 l_end_person number;
173 l_element_set_id number;
174 l_return_status varchar2(30);
175
176 no_records_found EXCEPTION;
177 l_retry_request_id number;
178
179 IN_USE_EXCEPTION EXCEPTION;
180 PRAGMA EXCEPTION_INIT (IN_USE_EXCEPTION, -54);
181
182 effort_det_lines_rec eff_det_lines_type;
183 l_sbd_string varchar2(120);
184 l_sed_string varchar2(120);
185
186
187 l_request_id number;
188
189 l_profile_sob_id number;
190 l_profile_bg_id number;
191 l_bg_currency_code varchar2(3);
192 l_gl_flag varchar2(1);
193 l_err_mesg varchar2(2000);
194 /*
195
196 det_person_id varchar2(240) := 'effort_det_lines_rec.person_id' ;
197 det_assignment_id varchar2(240) := 'effort_det_lines_rec.assignment_id' ;
198 det_project_id varchar2(240) := 'effort_det_lineS_rec.project_id';
199
200 */
201
202 cursor get_all_person(p_request_id in number) is
203 select distinct person_id from psp_selected_persons_t where request_id = p_request_id and person_id between
204 l_start_person and l_end_person;
205
206 -- for supercede
207 cursor get_supercede_persons is
208 select DISTINCT person_id -- Introduced DISTINCT for bug fix 4429787/4506505
209 from psp_supercede_persons_gt
210 where person_id > 0;
211
212 -- Introduced for bug fix 4182358
213 CURSOR conc_program_name_cur IS
214 SELECT fcp.concurrent_program_name
215 FROM fnd_concurrent_programs fcp,
216 fnd_concurrent_requests fcr
217 WHERE fcp.concurrent_program_id = fcr.concurrent_program_id
218 AND fcr.request_id = l_request_id;
219
220 l_program_name fnd_concurrent_programs.concurrent_program_name%TYPE;
221 -- End of bug fix 4182358
222
223 Cursor HUNDRED_PCENT_EFF_CSR (p_request_id varchar2) is
224 SELECT HUNDRED_PCENT_EFF_AT_PER_ASG, selection_match_level
225 FROM psp_report_templates_h
226 WHERE request_id = p_request_id;
227
228 l_HUNDRED_PCENT_EFF_AT_PER_ASG varchar2(1);
229 l_proj_segment varchar2(30);
230 l_tsk_segment varchar2(30);
231 l_awd_sgement varchar2(30);
232 l_exp_org_segment varchar2(30);
233 l_exp_type_segment varchar2(30);
234 l_use_gl_ptaoe_mapping varchar2(1);
235 l_selection_match_level varchar2(10);
236
237 l_gt_count integer;
238 BEGIN
239
240 ---select count(*) into l_gt_count from psp_supercede_persons_gt ;
241 ---hr_utility.trace(' COUNT of supercede persons_gt ='|| l_gt_Count);
242
243 l_profile_sob_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID');
244 l_profile_bg_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
245 l_bg_currency_code := psp_general.get_currency_code(l_profile_bg_id);
246
247
248 --OPEN HUNDRED_PCENT_EFF_CSR(p_request_id); Commented for UVA bug fix 4537063
249 --fetch HUNDRED_PCENT_EFF_CSR into l_HUNDRED_PCENT_EFF_AT_PER_ASG, l_selection_match_level; Commented for UVA bug fix 4537063
250 --close HUNDRED_PCENT_EFF_CSR; Commented for UVA bug fix 4537063
251
252 if p_supercede_mode is null then --- supercede
253
254
255 Select nvl(max(request_id),p_request_id) into l_request_id from psp_report_templates_h prth where request_id < p_request_id
256 and payroll_action_id = p_pactid;
257
258
259 select min(object_id), max(object_id) into l_start_person, l_end_person from pay_temp_object_actions
260 where payroll_action_id =p_pactid and chunk_number = p_chunk_num;
261 /*
262 -- Commented for bug fix 5050802
263 populate_error_table( l_request_id, l_start_person, l_end_person, min_effort_report_id, l_retry_request_id, 'PRE-POPULATE',
264 l_selection_match_level);
265 */
266 else
267
268
269 l_request_id :=p_request_id;
270
271 end if;
272
273 -- Moved the following cursor here as p_rquest_id will not have CER request id during Retry Runs.
274 OPEN HUNDRED_PCENT_EFF_CSR(l_request_id);
275 fetch HUNDRED_PCENT_EFF_CSR into l_HUNDRED_PCENT_EFF_AT_PER_ASG, l_selection_match_level;
276 close HUNDRED_PCENT_EFF_CSR;
277
278 -- Moved the following call here as l_selection_match_level was not available before opening the cursor
279 if p_supercede_mode is null then
280 populate_error_table( l_request_id, l_start_person, l_end_person, min_effort_report_id, l_retry_request_id, 'PRE-POPULATE',
281 l_selection_match_level);
282 end if;
283
284
285 /*
286 OPEN conc_program_name_cur;
287 FETCH conc_program_name_cur INTO l_program_name;
288 CLOSE conc_program_name_cur;
289
290 IF (l_program_name = 'PSPRTEF') THEN
291 l_retry_request_id :=g_psp_request_id;
292 END IF;
293
294
295 else
296 l_request_id := p_request_id;
297 end if;
298
299 */
300
301
302
303 -- fnd_file.put_line(fnd_file.log, 'g_psp_request_id is '||g_psp_request_id);
304 -- fnd_file.put_line(fnd_file.log, 'l_request__id '||l_request_id);
305 -- fnd_file.put_line(fnd_file.log, ' before starting l_retry_request__id '||l_retry_request_id);
306
307 IF g_psp_request_id = l_request_id then
308 l_retry_request_id := NULL;
309 ELSE
310 l_retry_request_id :=g_psp_request_id;
311 END IF;
312
313
314
315 -- fnd_file.put_line(fnd_file.log, 'request i dis '|| p_request_id);
316 -- fnd_file.put_line(fnd_file.log, ' retry request i dis ' || l_retry_request_id);
317
318
319
320 hr_utility.trace('psp_create_eff_reports--> request_id, retry_request_id, st_person, end_person = '|| p_request_id||' '||l_retry_request_id ||' '|| l_start_person||' '|| l_end_person);
321
322
323 -- fnd_file.put_line(fnd_file.log,' retry_Request_id = '||l_retry_request_id );
324 select parameter_value_2, parameter_value_3 into p_effort_start, p_effort_end from
325 psp_report_templates_h where request_id = l_request_id;
326
327 select criteria_value1 into l_element_set_id from psp_report_template_details_h where
328 criteria_lookup_code='EST' and request_id = l_request_id;
329
330
331 /*
332 and action_status<> 'C';
333
334 */
335
336 --fnd_file.put_line(fnd_file.log,' chunk num = *** '||p_chunk_num||'start_end= '||l_Start_person ||' '||l_end_person );
337 ---- Warning: any change in this string will impact superceding,
338 ----- change here needs to sync up with query for superceding.
339 g_exec_string := '
340 SELECT
341 person_id , ASSIGNMENT_ID,
342 PROJECT_ID, TASK_ID,
343 AWARD_ID, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_TYPE,
344 SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
345 SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,
346 SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,
347 SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,
348 SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,
349 SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30,
350 SUM(distribution_amount) distribution_amount, MAX(distribution_date1) , max(distribution_date2)
351
352 bulk collect into
353 :det_person_id, :det_assignment_id, :det_project_id, :det_task_id , :det_award_id,
354 :det_exp_org_id,
355 :det_exp_type,
356 :det_segment1, :det_segment2, :det_segment3, :det_segment4, :det_segment5, :det_segment6,
357 :det_segment7, :det_segment8, :det_segment9,
358 :det_segment10, :det_segment11, :det_segment12,
359 :det_segment13, :det_segment14, :det_segment15,
360 :det_segment16, :det_segment17, :det_segment18,
361 :det_segment19, :det_segment20, :det_segment21,
362 :det_segment22 , :det_segment23, :det_segment24,
363 :det_segment25, :det_segment26, :det_segment27,
364 :det_segment28, :det_segment29, :det_segment30 ,
365 :det_dist_amount, :det_sch_st_date,
366 :det_sch_end_date
367 from (
368 SELECT
369 psl.person_id person_id, null ASSIGNMENT_ID,
370 null PROJECT_ID, null TASK_ID,
371 null AWARD_ID, null EXPENDITURE_ORGANIZATION_ID, null EXPENDITURE_TYPE, null SEGMENT1, null SEGMENT2, null SEGMENT3, null SEGMENT4,
372 null SEGMENT5, null SEGMENT6, null SEGMENT7, null SEGMENT8, null SEGMENT9,
373 null SEGMENT10, null SEGMENT11, null SEGMENT12, null SEGMENT13, null SEGMENT14,
374 Null SEGMENT15, null SEGMENT16, null SEGMENT17, null SEGMENT18, null SEGMENT19, null SEGMENT20,
375 Null SEGMENT21, null SEGMENT22, null SEGMENT23, null SEGMENT24, null SEGMENT25, null SEGMENT26, null SEGMENT27, null SEGMENT28, null SEGMENT29, null SEGMENT30,
376 /*
377 sum(decode(psl.dr_cr_flag,'||''''||'D'||''''||',nvl(pdnh.distribution_amount,nvl(ppg.distribution_amount, pal.distribution_amount)), nvl(-pdnh.distribution_amount, nvl(-ppg.distribution_amount,
378 -pal.distribution_amount)))) distribution_amount,
379 */
380 decode(psl.dr_cr_flag,'||''''||'D'||''''||',nvl(pdnh.distribution_amount,nvl(ppg.distribution_amount, pal.distribution_amount)), nvl(-pdnh.distribution_amount, nvl(-ppg.distribution_amount,
381 -pal.distribution_amount))) distribution_amount,
382 null distribution_date1, null distribution_date2
383 FROM
384 psp_distribution_lines_history pdnh,
385 psp_summary_lines psl,
386 psp_Selected_persons_t pspt,
387 psp_pre_gen_dist_lines_history ppg,
388 psp_adjustment_lineS_history pal,
389 psp_payroll_sub_lines ppsl,
390 psp_payroll_lines ppl
391 WHERE psl.person_id = pspt.person_id
392 and pspt.request_id = :l_request_id
393 and pspt.person_id between :l_start_person and :l_end_person
394 and psl.summary_line_id = pdnh.summary_line_id(+)
395 and pdnh.payroll_sub_line_id= ppsl.payroll_sub_line_id(+) and
396 ppsl.payroll_line_id = ppl.payroll_line_id(+)
397 and psl.summary_line_id = ppg.summary_line_id(+)
398 and psl.summary_line_id = pal.summary_line_id(+)
399 AND psl.status_code = '||''''||'A'||''''||'
400 AND
401 ( EXISTS
402 (select 1 from psp_element_set_members_v pesr where pesr.element_set_id = :l_element_set_id and
403 pesr.element_type_id = ppl.element_type_id )
404 OR EXISTS
405 (select 1 from psp_element_set_members_v pesr where pesr.element_set_id =
406 :l_element_set_id and pesr.element_type_id = pal.element_type_id)
407 OR EXISTS
408 (select 1 from psp_element_set_members_v pesr where pesr.element_set_id =
409 :l_element_set_id and pesr.element_type_id = ppg.element_type_id )
410 ) AND
411 /* pspt.person_id not in (select person_id from psp_eff_reports pea where
412 pea.end_date >= :effort_start and pea.start_date <= :effort_end and
413 status_code in ('||''''||'N'||''''||','||''''||'A'||''''||')) AND */
414 ((psl.source_type in ('||''''||'N'||''''||' , '|| ''''|| 'O' ||'''' ||' ) and
415 psl.summary_line_id = pdnh.summary_line_id
416 and pdnh.distribution_date between :effort_start and :effort_end
417 AND pdnh.reversal_entry_flag is NULL
418 AND pdnh.adjustment_batch_name is null
419 )
420 OR (psl.source_type=' ||''''|| 'P'||'''' || '
421 AND ppg.status_code ='||''''||'A' || ''''||'
422 and ppg.distribution_date between :effort_start and :effort_end
423 AND ppg.reversal_entry_flag is NULL
424 AND ppg.summary_line_id = psl.summary_line_id
425 AND ppg.adjustment_batch_name is null
426 )
427 OR (psl.source_type='||'''' || 'A' || ''''||'
428 and pal.distribution_date between :effort_start and :effort_end
429 AND pal.status_code = ' || ''''|| 'A' || ''''|| '
430 AND NVL(pal.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
431 || ' AND pal.reversal_entry_flag is NULL
432 AND pal.summary_line_id = psl.summary_line_id
433 AND pal.adjustment_batch_name is null
434 ))
435 /* Added for hospital effort report Change*/
436 UNION ALL
437 SELECT
438 psl.person_id person_id, null ASSIGNMENT_ID,
439 null PROJECT_ID, null TASK_ID,
440 null AWARD_ID, null EXPENDITURE_ORGANIZATION_ID, null EXPENDITURE_TYPE,
441 null SEGMENT1, null SEGMENT2, null SEGMENT3, null SEGMENT4, null SEGMENT5,
442 null SEGMENT6, null SEGMENT7, null SEGMENT8, null SEGMENT9, null SEGMENT10,
443 null SEGMENT11, null SEGMENT12, null SEGMENT13, null SEGMENT14, Null SEGMENT15,
444 null SEGMENT16, null SEGMENT17, null SEGMENT18, null SEGMENT19, null SEGMENT20,
445 Null SEGMENT21, null SEGMENT22, null SEGMENT23, null SEGMENT24, null SEGMENT25,
446 null SEGMENT26, null SEGMENT27, null SEGMENT28, null SEGMENT29, null SEGMENT30,
447 psl.distribution_amount distribution_amount, null , null
448 FROM psp_external_effort_lines psl,
449 psp_Selected_persons_t pspt
450 WHERE psl.person_id = pspt.person_id
451 and pspt.request_id = :l_request_id
452 AND pspt.person_id between :l_start_person and :l_end_person
453 /*
454 AND EXISTS (select 1
455 from psp_element_set_members_v pesr
456 where pesr.element_set_id = :l_element_set_id
457 and pesr.element_type_id = psl.element_type_id)
458 */
459 AND psl.distribution_date between :effort_start and :effort_end
460 )
461 group by
462 person_id , ASSIGNMENT_ID,
463 PROJECT_ID, TASK_ID,
464 AWARD_ID, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_TYPE,
465 SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
466 SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,
467 SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,
468 SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,
469 SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,
470 SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30
471 /*psl.person_id */
472 ';
473
474 if p_supercede_mode is not null then
475 ---fnd_file.put_line(fnd_file.log,' supercede mode is not null, changing the exec string ');
476 hr_utility.trace(' create_eff --> supercede mode is not null, changing the exec string ');
477 --- added for uva issues (assignment matching only)
478 -- rows inserted thru supercede will have -ve request_id and will be rolled
479 --- back immediately by summarize_transfer process (transient)
480
481 --Bug 5237611: Fixed terminated assignment Issue, added FF suport for assignment
482 /*
483 if l_selection_match_level = 'EMP' then
484 insert into psp_selected_persons_t (request_id, person_id)
485 select -1 * l_request_id, gt.person_id from psp_supercede_persons_gt gt
486 where gt.person_id > 0 ;
487 else
488 */
489 insert into psp_selected_persons_t (request_id, person_id, assignment_id)
490 select -1 * l_request_id, person_id, assignment_id
491 from psp_Eff_reports er,
492 psp_eff_report_details erd
493 where er.effort_Report_id = erd.effort_Report_id
494 and er.request_id = l_request_id
495 and er.status_code in ('N','A')
496 and er.person_id in (select person_id from psp_supercede_persons_gt where person_id > 0) ;
497 g_exec_string := replace(g_exec_string, 'psl.person_id = pspt.person_id' ,
498 'psl.person_id = pspt.person_id and psl.assignment_id = pspt.assignment_id ');
499 -- end if;
500 g_exec_string := replace(g_exec_string, 'pspt.person_id between :l_start_person and :l_end_person' ,
501 ' nvl(:l_start_person,-9) = nvl(:l_start_person,-9) and
502 nvl(:l_end_person,-9) = nvl(:l_end_person,-9) ');
503 g_exec_string := replace(g_exec_string, 'pea.end_date >= :effort_start and pea.start_date <= :effort_end and',null);
504 g_exec_string := replace(g_exec_string, 'status_code in ('||''''||'N'||''''||','||''''||'A'||''''||')) AND',null);
505 g_exec_string := replace(g_exec_string, 'pspt.person_id not in (select person_id from psp_eff_reports pea where',null);
506 g_exec_string := replace(g_exec_string, 'group by psl.person_id',
507 ' and pspt.person_id in (select person_id from psp_Eff_reports where request_id ='|| p_request_id||' and status_code in ( '||''''||'A'||''''||','||''''||'N'||''''||')) group by psl.person_id');
508 g_exec_string := replace(g_exec_string, 'and pspt.request_id = :l_request_id',
509 ' and -1 * pspt.request_id = :l_request_id and pspt.skip_reason is null ');
510 else
511 -- added for UVA issue.
512 g_exec_string := replace(g_exec_string, 'and pspt.request_id = :l_request_id',
513 ' and pspt.request_id = :l_request_id and pspt.skip_reason is null ');
514 -- if l_selection_match_level = 'ASG' then
515 g_exec_string := replace(g_exec_string, 'psl.person_id = pspt.person_id' ,
516 'psl.person_id = pspt.person_id and psl.assignment_id = pspt.assignment_id ');
517 -- end if;
518 end if;
519
520 OPEN get_summarization_criteria(l_request_id);
521
522
523 FETCH get_summarization_criteria BULK collect into eff_template_sum_rec.array_sum_criteria,
524 eff_template_sum_rec.array_sum_order;
525
526 CLOSE get_summarization_criteria;
527 -- Bug Fix 4244924:YALE ENHANCEMENTS
528 IF PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(l_profile_bg_id,'PSP_USE_GL_PTAOE_MAPPING') = 'Y' THEN
529 PSP_GENERAL.GET_GL_PTAOE_MAPPING(p_business_group_id => l_profile_bg_id,
530 p_proj_segment => l_proj_segment,
531 p_tsk_segment => l_tsk_segment,
532 p_awd_sgement => l_awd_sgement,
533 p_exp_org_segment=> l_exp_org_segment,
534 p_exp_type_segment => l_exp_type_segment);
535 END IF;
536
537
538 FOR I IN 1..EFF_TEMPLATE_SUM_REC.ARRAY_SUM_CRITERIA.COUNT
539
540 LOOP
541
542 IF PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(l_profile_bg_id,'PSP_USE_GL_PTAOE_MAPPING') = 'N' THEN
543 IF substr(eff_template_sum_rec.array_sum_criteria(i),1,7) = 'SEGMENT' then
544 g_exec_string:= replace(g_exec_string, 'null '||eff_template_sum_rec.array_sum_criteria(i)||',',
545 eff_template_sum_rec.array_sum_criteria(i) ||',');
546 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
547 l_gl_flag:='Y';
548 -- g_exec_string:= replace(g_exec_string, ' psl.person_id = pspt.person_id', ' psl.person_id = pspt.person_id '||l_select_string);
549 l_grp_string:= l_grp_string || ',' ||'gcc.'|| eff_template_sum_rec.array_sum_criteria(i) ;
550
551 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'SSD' then
552 -- l_sbd_string := 'min(nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date))) distribution_date1';
553 l_sbd_string := 'nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date)) distribution_date1';
554 g_exec_string:= replace(g_exec_string, 'null distribution_date1', l_sbd_string);
555 ELSIF eff_template_sum_rec.array_sum_criteria(i)='SED' then
556 -- l_sed_string:=' max(nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date ))) distribution_date2';
557 l_sed_string:=' nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date )) distribution_date2';
558 g_exec_string:= replace(g_exec_string, 'null distribution_date2', l_sed_string);
559 ELSE
560 g_exec_string := replace(g_exec_string, 'null '||eff_template_sum_rec.array_sum_criteria(i)||',', 'psl.'||eff_template_sum_rec.array_sum_criteria(i)||',');
561 l_grp_string := l_grp_string || ', ' ||'psl.'||eff_template_sum_rec.array_sum_criteria(i);
562 END IF;
563 ELSE
564 -- Bug Fix 4244924:YALE ENHANCEMENTS
565 IF eff_template_sum_rec.array_sum_criteria(i)= 'GL:PROJECT_ID' then
566 g_exec_string:= replace(g_exec_string, 'null PROJECT_ID,', 'gcc.'|| l_proj_segment ||' PROJECT_ID,');
567 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
568 l_gl_flag:='Y';
569 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_proj_segment ;
570 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'GL:TASK_ID' then
571 g_exec_string:= replace(g_exec_string, 'null TASK_ID,', 'gcc.'|| l_tsk_segment ||' TASK_ID,');
572 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
573 l_gl_flag:='Y';
574 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_tsk_segment ;
575 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'GL:AWARD_ID' then
576 g_exec_string:= replace(g_exec_string, 'null AWARD_ID,', 'gcc.'|| l_awd_sgement ||' AWARD_ID,');
577 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
578 l_gl_flag:='Y';
579 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_awd_sgement ;
580 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'GL:EXPENDITURE_ORGANIZATION_ID' then
581 g_exec_string:= replace(g_exec_string, 'null EXPENDITURE_ORGANIZATION_ID,', 'gcc.'|| l_exp_org_segment ||' EXPENDITURE_ORGANIZATION_ID,');
582 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
583 l_gl_flag:='Y';
584 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_exp_org_segment ;
585 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'GL:EXPENDITURE_TYPE' then
586 g_exec_string:= replace(g_exec_string, 'null EXPENDITURE_TYPE,', 'gcc.'|| l_exp_type_segment ||' EXPENDITURE_TYPE,');
587 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
588 l_gl_flag:='Y';
589 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_exp_type_segment ;
590 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'SSD' then
591 -- l_sbd_string := 'min(nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date))) distribution_date1';
592 l_sbd_string := 'nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date)) distribution_date1';
593 g_exec_string:= replace(g_exec_string, 'null distribution_date1', l_sbd_string);
594 ELSIF eff_template_sum_rec.array_sum_criteria(i)='SED' then
595 -- l_sed_string:=' max(nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date))) distribution_date2';
596 l_sed_string:=' nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date)) distribution_date2';
597 g_exec_string:= replace(g_exec_string, 'null distribution_date2', l_sed_string);
598 ELSE
599 g_exec_string := replace(g_exec_string, 'null '||eff_template_sum_rec.array_sum_criteria(i)||',', 'psl.'||eff_template_sum_rec.array_sum_criteria(i)||',');
600 l_grp_string := l_grp_string || ', ' ||'psl.'||eff_template_sum_rec.array_sum_criteria(i);
601 END IF;
602 END IF;
603 END LOOP;
604
605 IF NVL(l_gl_flag, 'N') ='Y' then
606
607 g_exec_string:= replace(g_exec_string, ' psl.person_id = pspt.person_id', ' psl.person_id = pspt.person_id '||l_select_string);
608
609 -- g_exec_string:=replace(g_exec_string,'ppl WHERE','ppl, gl_code_combinations gcc WHERE');
610 g_exec_string:=replace(g_exec_string,'WHERE psl.person_id = pspt.person_id',', gl_code_combinations gcc WHERE psl.person_id = pspt.person_id');
611 END IF;
612
613 -- g_exec_string := replace(g_exec_string, ' group by psl.person_id ' , ' group by person_id'||l_grp_string);
614
615
616 --fnd_file.put_line(fnd_file.log,' dyn sql = '||g_exec_string);
617 --fnd_file.put_line(fnd_file.log,' dyn sql = '||l_grp_string);
618
619 --- hr_utility.trace(' dyn sql = '||g_exec_string);
620
621 /*
622 det_string := '
623 effort_det_lines_rec.person_id,
624 effort_det_lines_rec.assignment_id,
625 effort_det_lineS_rec.project_id, effort_det_lineS_rec.task_id ,
626 effort_Det_lines_rec.award_id,
627 effort_det_lines_rec.expenditure_organization_id,
628 */
629
630
631 execute immediate ' begin ' ||g_exec_string ||';
632 end; ' using
633 OUT det_person_id,
634 OUT det_assignment_id ,
635 OUT det_project_id,
636 OUT det_task_id ,
637 OUT det_award_id,
638 OUT det_exp_org_id,
639 OUT det_expenditure_type,
640 OUT det_segment1,
641 OUT det_segment2,
642 OUT det_segment3,
643 OUT det_segment4,
644 OUT det_segment5,
645 OUT det_segment6,
646 OUT det_segment7,
647 OUT det_segment8,
648 OUT det_segment9,
649 OUT det_segment10,
650 OUT det_segment11,
651 OUT det_segment12,
652 OUT det_segment13,
653 OUT det_segment14,
654 OUT det_segment15,
655 OUT det_segment16,
656 OUT det_segment17,
657 OUT det_segment18,
658 OUT det_segment19,
659 OUT det_segment20,
660 OUT det_segment21,
661 OUT det_segment22 ,
662 OUT det_segment23,
663 OUT det_segment24,
664 OUT det_segment25,
665 OUT det_segment26,
666 OUT det_segment27,
667 OUT det_segment28,
668 OUT det_segment29,
669 OUT det_segment30 ,
670 OUT det_distribution_amount,
671 OUT det_schedule_start_date,
672 OUT det_schedule_end_date,
673 IN l_request_id, l_start_person, l_end_person, l_element_set_id, p_effort_start, p_effort_end
674 ;
675
676 -- fnd_file.put_line(fnd_file.log,' =====after dyn sql '||i);
677
678 hr_utility.trace('psp_create_eff_reports--> After dyn sql');
679
680
681
682
683 SELECT NVL(max(effort_report_id),0) into min_effort_report_id from psp_eff_reports;
684 if p_supercede_mode is null then
685 OPEN get_all_person(l_request_id);
686 FETCH get_all_person bulk collect into person_rec.array_person_id;
687 CLOSE get_all_person;
688 else
689 -- added following global var for Supercede process
690 g_summarization_criteria := l_grp_string;
691 OPEN get_supercede_persons;
692 FETCH get_supercede_persons bulk collect into person_rec.array_person_id;
693 CLOSE get_supercede_persons;
694 hr_utility.trace(' psp_create_eff_reports--> get_supercede_persons Count = '|| person_rec.array_person_id.count);
695 end if;
696
697
698
699 hr_utility.trace('psp_create_eff_reports--> After bulk fetch');
700 -- fnd_file.put_line(fnd_file.log,' after bulk fetch ');
701
702 -- fnd_file.put_line(fnd_file.log, 'count is '||effort_det_lines_rec.person_id.count);
703
704
705 /*
706 IF effort_det_lines_rec.person_id.count > 0 then
707
708 l_loop_count := l_loop_count + effort_det_lines_rec.person_id.count;
709
710
711 */
712
713 -- fnd_file.put_line(fnd_file.log, 'count person is '||det_person_id.count);
714 hr_utility.trace ('count person is '||det_person_id.count);
715
716 IF det_person_id.count >0 then
717 l_loop_count := l_loop_count + det_person_id.count;
718 -- fnd_file.put_line(fnd_file.log,' before inserting into psp_eff_reports ');
719
720 -- fnd_file.put_line(fnd_file.log,' =====inserting in psp_eff_reports ');
721
722 FORALL i IN 1..person_rec.array_person_id.count
723 insert into psp_eff_reports(
724 effort_report_id,
725 status_code,
726 person_id,
727 object_version_number,
728 start_date,
729 end_date,
730 template_id,
731 request_id,
732 currency_code,
733 last_update_date,
734 last_updated_by,
735 last_update_login,
736 created_by,
737 creation_date,
738 business_group_id,
739 set_of_books_id
740 )
741 values
742 (
743 psp_effort_reports_s.nextval,
744 decode(p_supercede_mode , null, 'N', 'T'),
745 person_rec.array_person_id(i),
746 1,
747 p_effort_Start,
748 p_effort_end,
749 decode(p_supercede_mode, null, g_psp_template_id, -999),
750 l_request_id,
751 l_bg_currency_code,
752 sysdate,
753 fnd_global.user_id ,
754 fnd_global.user_id,
755 fnd_global.user_id,
756 sysdate,
757 l_profile_bg_id,
758 l_profile_sob_id) returning effort_report_id bulk collect into person_rec.array_effort_report_id;
759
760 end if;
761
762 -- fnd_file.put_line(fnd_file.log , 'after insert into eff '||person_rec.array_effort_report_id.count);
763
764
765 hr_utility.trace('psp_create_eff_reports--> After insert into eff '||person_rec.array_effort_report_id.count);
766 /*
767
768 */
769
770
771
772
773 -- fnd_file.put_line(fnd_file.log,' before inserting into psp_report_details ');
774
775 -- fnd_file.put_line(fnd_file.log,' =====inserting in psp_eff_report_details ');
776
777 hr_utility.trace('psp_create_eff_reports--> before inserting into details ');
778
779 FORALL i in 1..det_person_id.count
780 insert into psp_eff_report_details(
781 effort_report_detail_id,
782 effort_report_id,
783 object_version_number,
784 assignment_id,
785 GL_SEGMENT1,
786 GL_SEGMENT2,
787 GL_SEGMENT3,
788 GL_SEGMENT4,
789 GL_SEGMENT5,
790 GL_SEGMENT6,
791 GL_SEGMENT7,
792 GL_SEGMENT8,
793 GL_SEGMENT9,
794 GL_SEGMENT10,
795 GL_SEGMENT11,
796 GL_SEGMENT12,
797 GL_SEGMENT13,
798 GL_SEGMENT14,
799 GL_SEGMENT15,
800 GL_SEGMENT16,
801 GL_SEGMENT17,
802 GL_SEGMENT18,
803 GL_SEGMENT19,
804 GL_SEGMENT20,
805 GL_SEGMENT21,
806 GL_SEGMENT22,
807 GL_SEGMENT23,
808 GL_SEGMENT24,
809 GL_SEGMENT25,
810 GL_SEGMENT26,
811 GL_SEGMENT27,
812 GL_SEGMENT28,
813 GL_SEGMENT29,
814 GL_SEGMENT30,
815 project_id,
816 expenditure_organization_id,
817 expenditure_type,
818 task_id,
819 award_id,
820 actual_salary_amt,
821 payroll_percent,
822 schedule_start_date,
823 schedule_end_date,
824 last_update_date,
825 last_updated_by,
826 last_update_login,
827 created_by,
828 creation_date)
829 values
830 (
831 psp_eff_report_details_s.nextval,
832 1,
833 1,
834 det_assignment_id(i),
835 det_segment1(i),
836 det_segment2(i),
837 det_segment3(i),
838 det_segment4(i),
839 det_segment5(i),
840 det_segment6(i),
841 det_segment7(i),
842 det_segment8(i),
843 det_segment9(i),
844 det_segment10(i),
845 det_segment11(i),
846 det_segment12(i),
847 det_segment13(i),
848 det_segment14(i),
849 det_segment15(i),
850 det_segment16(i),
851 det_segment17(i),
852 det_segment18(i),
853 det_segment19(i),
854 det_segment20(i),
855 det_segment21(i),
856 det_segment22(i),
857 det_segment23(i),
858 det_segment24(i),
859 det_segment25(i),
860 det_segment26(i),
861 det_segment27(i),
862 det_segment28(i),
863 det_segment29(i),
864 det_segment30(i),
865 det_project_id(i),
866 det_exp_org_id(i) ,
867 det_expenditure_type(i),
868 det_task_id(i),
869 det_award_id(i),
870 det_distribution_amount(i),
871 0,
872 det_schedule_start_date(i),
873 det_schedule_end_date(i),
874 sysdate,
875 fnd_global.user_id,
876 fnd_global.user_id,
877 fnd_global.user_id,
878 sysdate
879 ) returning to_number(det_person_id(i)), effort_report_detail_id bulk collect into effort_det_lines_rec.person_id, effort_det_lines_rec.effort_report_detail_id;
880
881
882 -- fnd_file.put_line(fnd_file.log,'==== after det '|| effort_det_lines_rec.effort_report_detail_id.count);
883
884
885
886
887 -- fnd_file.put_line(fnd_file.log,' after deatils ');
888
889 -- fnd_file.put_line(fnd_file.log,' ===== IF p_supercede_mode is not null THEN ');
890
891 IF p_supercede_mode is not null THEN
892 hr_utility.trace(' psp_create_eff_reports--> updating erd count ='|| effort_det_lines_rec.effort_report_detail_id.count ||' min_effort_Report_id = '||min_effort_report_id );
893
894 FORALL i in 1..effort_det_lines_rec.effort_report_detail_id.count
895 update psp_eff_report_details set effort_report_id =
896 (select effort_report_id from psp_eff_reports where
897 person_id =effort_Det_lines_rec.person_id(i) and status_code = 'T'
898 and request_id = l_request_id ) where
899 effort_report_detail_id= effort_det_lines_rec.effort_report_detail_id(i) ;
900
901 ELSE
902
903 FORALL i in 1..effort_det_lines_rec.effort_report_detail_id.count
904 update psp_eff_report_details set effort_report_id =
905 (select effort_report_id from psp_eff_reports where
906 person_id =effort_Det_lines_rec.person_id(i) and effort_report_id > min_effort_report_id ) where
907 effort_report_detail_id= effort_det_lines_rec.effort_report_detail_id(i) ;
908
909 END IF;
910
911
912
913 -- fnd_file.put_line(fnd_file.log,' after update of effort report details ');
914
915 hr_utility.trace('psp_create_eff_reports--> After update of psp_eff_report_detail ');
916
917 effort_Det_lines_rec.effort_report_detail_id.delete;
918 effort_det_lines_rec.person_id.delete;
919
920
921
922 /* delete the det_details arrays */
923
924 det_person_id.delete;
925 det_assignment_id.delete;
926 det_project_id.delete;
927 det_task_id.delete;
928 det_award_id.delete;
929 det_exp_org_id.delete;
930 det_expenditure_type.delete;
931 det_segment1.delete;
932 det_segment2.delete;
933 det_segment3.delete;
934 det_segment4.delete;
935 det_segment5.delete;
936 det_segment6.delete;
937 det_segment7.delete;
938 det_segment8.delete;
939 det_segment9.delete;
940 det_segment10.delete;
941 det_segment11.delete;
942 det_segment12.delete;
943 det_segment13.delete;
944 det_segment14.delete;
945 det_segment15.delete;
946 det_segment16.delete;
947 det_segment17.delete;
948 det_segment18.delete;
949 det_segment19.delete;
950 det_segment20.delete;
951 det_segment21.delete;
952 det_segment22.delete;
953 det_segment23.delete;
954 det_segment24.delete;
955 det_segment25.delete;
956 det_segment26.delete;
957 det_segment27.delete;
958 det_segment28.delete;
959 det_segment29.delete;
960 det_segment30.delete;
961 det_distribution_amount.delete;
962 det_schedule_start_date.delete;
963 det_schedule_end_date.delete;
964
965 -- OHSU Changes
966
967
968 -- fnd_file.put_line(fnd_file.log,' ===== IF l_HUNDRED_PCENT_EFF_AT_PER_ASG');
969
970 IF l_HUNDRED_PCENT_EFF_AT_PER_ASG = 'A' then
971
972 select sum(actual_salary_amt), effort_report_id, assignment_id bulk collect
973 into person_rec.sum_tot, person_rec.array_effort_report_id, person_rec.array_assignment_id
974 from psp_eff_report_details where
975 effort_report_id > min_effort_report_id group by effort_report_id, assignment_id;
976
977 hr_utility.trace('psp_create_eff_reports--> After getting sum');
978
979 FORALL i in 1..person_rec.array_assignment_id.count
980 update psp_Eff_report_details set payroll_percent = decode(person_rec.sum_tot(i),0,0,round( ((actual_salary_amt * 100) / person_rec.sum_tot(i)),2))
981 where effort_report_id = person_rec.array_effort_report_id(i)
982 and assignment_id = person_rec.array_assignment_id(i);
983
984
985 person_rec.array_effort_report_id.delete;
986 person_rec.sum_tot.delete;
987 person_rec.array_assignment_id.delete;
988
989
990 select sum(payroll_percent), effort_report_id, assignment_id bulk collect
991 into person_rec.payroll_percent_tot, person_rec.array_effort_report_id, person_rec.array_assignment_id
992 from psp_eff_report_details where effort_report_id > min_effort_report_id group by effort_report_id , assignment_id;
993
994
995 hr_utility.trace('psp_create_eff_reports--> After sum payroll percent');
996
997 FORALL i in 1..person_rec.array_assignment_id.count
998 update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i))
999 where effort_report_detail_id in (
1000 select max(effort_report_detail_id)
1001 from psp_eff_report_details
1002 where effort_report_id = person_rec.array_effort_report_id(i)
1003 and person_rec.payroll_percent_tot(i)<>0 and
1004 assignment_id = person_rec.array_assignment_id(i)
1005 ) ;
1006 ELSE
1007
1008 select sum(actual_salary_amt), effort_report_id bulk collect into person_rec.sum_tot, person_rec.array_effort_report_id
1009 from psp_eff_report_details where
1010 effort_report_id > min_effort_report_id group by effort_report_id ;
1011
1012 hr_utility.trace('psp_create_eff_reports--> After getting sum');
1013
1014 --- fnd_file.put_line(fnd_file.log,' after getting sum ');
1015
1016 FORALL i in 1..person_rec.array_effort_report_id.count
1017 -- update psp_Eff_report_details set payroll_percent = round(actual_salary_amt/person_rec.sum_tot(i),2)*100 where effort_report_id =
1018 -- person_rec.array_effort_report_id(i);
1019 -- check for zero sum , flag error if so
1020 update psp_Eff_report_details set payroll_percent = decode(person_rec.sum_tot(i),0,0,round( ((actual_salary_amt * 100) / person_rec.sum_tot(i)),2)) where effort_report_id =
1021 person_rec.array_effort_report_id(i);
1022
1023 person_rec.array_effort_report_id.delete;
1024 person_rec.sum_tot.delete;
1025
1026
1027 select sum(payroll_percent), effort_report_id
1028 bulk collect into person_rec.payroll_percent_tot, person_rec.array_effort_report_id
1029 from psp_eff_report_details where effort_report_id > min_effort_report_id group by effort_report_id ;
1030
1031
1032 FORALL i in 1..person_rec.array_effort_report_id.count
1033 update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i)) where
1034 effort_report_detail_id in (select max(effort_report_detail_id) from psp_eff_report_details where
1035 effort_report_id = person_rec.array_effort_report_id(i) and person_rec.payroll_percent_tot(i)<>0) ;
1036 END IF;
1037
1038 -- fnd_file.put_line(fnd_file.log,' after getting sum ');
1039
1040
1041 /*
1042 should be person or assignment depending on whether assignment is used as a summarization criteria
1043 */
1044
1045
1046 -- OHSU Changes
1047 /*
1048
1049 */
1050
1051
1052 /* to update the last line in the case it exceeds 100 */
1053
1054
1055 -- fnd_file.put_line(fnd_file.log,' after details update ');
1056
1057
1058
1059 -- OHSU Changes
1060 /*
1061
1062 */
1063
1064 /*
1065
1066
1067
1068 */
1069
1070 /* Add the difference if any to the last detail line for each effort report_id */
1071
1072
1073 -- OHSU Changes
1074 /*
1075 FORALL i in 1..person_rec.array_effort_report_id.count
1076 update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i)) where
1077 effort_report_detail_id in (select max(effort_report_detail_id) from psp_eff_report_details where
1078 effort_report_id = person_rec.array_effort_report_id(i) and person_rec.payroll_percent_tot(i)<>0) ;
1079 */
1080
1081
1082 delete from psp_eff_reports per where effort_report_id > min_effort_report_id and
1083 not exists (select 1 from psp_eff_report_details perd where
1084 perd.effort_report_id = per.effort_report_id);
1085
1086 -- fnd_file.put_line(fnd_file.log,' after delete of orphan record ');
1087
1088
1089 /* added this call just to see how retry works
1090
1091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1092
1093
1094 */
1095
1096
1097
1098
1099 if p_supercede_mode is not null then
1100
1101
1102 -- fnd_file.put_line(fnd_file.log,' before return ');
1103 retcode := 0;
1104 return;
1105 end if;
1106
1107
1108
1109
1110
1111 -- get cost share info
1112
1113 -- psp_eff_report_details_api.update_eff_report_details(l_request_id);
1114
1115 --psp_eff_report_details_api.update_eff_report_details(p_validate , l_request_id, p_warning );
1116 /* for Bug fix 4089645 Added Person id check */
1117 -- fnd_file.put_line(fnd_file.log,' ===== Populate_error_table');
1118
1119
1120 Populate_error_table( l_request_id, l_start_person, l_end_person, min_effort_report_id, l_retry_request_id, null, null);
1121
1122 if person_rec.array_effort_report_id.count >0 then
1123
1124 person_rec.array_effort_report_id.delete;
1125 person_rec.payroll_percent_tot.delete;
1126 /*
1127 IF PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(l_profile_bg_id,'PSP_USE_GL_PTAOE_MAPPING') = 'Y' THEN
1128 psp_xmlgen.copy_ptaoe_from_gl_segments(l_start_person ,
1129 l_end_person ,
1130 l_request_id,
1131 l_retry_request_id,
1132 l_profile_bg_id,
1133 l_return_status) ;
1134 hr_utility.trace('psp_create_eff_reports--> After xmlgen.copy_ptaoe_from_gl_segments');
1135
1136 If (l_return_status = fnd_api.g_ret_sts_error) or (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1137 psp_message_s.print_error(p_mode=>FND_FILE.log,
1138 p_print_header=>FND_API.G_TRUE);
1139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1140
1141 END IF;
1142 END IF;
1143 */
1144
1145 if p_warning = true then
1146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1147 end if;
1148
1149 hr_utility.trace('psp_create_eff_reports--> After costs share API');
1150 -- fnd_file.put_line(fnd_file.log,' ===== psp_xmlgen.update_er_details');
1151
1152 psp_xmlgen.update_er_details(l_start_person ,
1153 l_end_person ,
1154 l_request_id,
1155 l_retry_request_id,
1156 l_return_status) ;
1157
1158 hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_er_details');
1159 If (l_return_status = fnd_api.g_ret_sts_error) or (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1160 psp_message_s.print_error(p_mode=>FND_FILE.log,
1161 p_print_header=>FND_API.G_TRUE);
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1163
1164 END IF;
1165 -- fnd_file.put_line(fnd_file.log,' ===== psp_eff_report_details_api.update_eff_report_details');
1166
1167
1168 psp_xmlgen.update_grouping_category(l_start_person, l_end_person, l_request_id, l_return_status) ;
1169
1170 hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_grouping_category');
1171 If (l_return_status = fnd_api.g_ret_sts_error) or (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1172 psp_message_s.print_error(p_mode=>FND_FILE.log,
1173 p_print_header=>FND_API.G_TRUE);
1174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1175 END IF;
1176
1177 -- fnd_file.put_line(fnd_file.log,' ===== psp_eff_report_details_api.update_eff_report_details');
1178
1179 psp_eff_report_details_api.update_eff_report_details(p_validate , l_request_id,l_start_person, l_end_person, p_warning );
1180
1181 -- Check weather Project, Task, Award, Exp Org are valid or not
1182
1183 VALIDATE_PTAOE(p_start_person => l_start_person,
1184 p_end_person => l_end_person,
1185 p_request_id => l_request_id,
1186 p_retry_request_id => l_retry_request_id,
1187 p_return_status => l_return_status) ;
1188
1189 -- fnd_file.put_line(fnd_file.log,' ===== after ptaoe validate');
1190
1191 If (l_return_status = fnd_api.g_ret_sts_unexp_error) or (l_return_status = fnd_api.g_ret_sts_error) then
1192 -- fnd_file.put_line(fnd_file.log,' ===== after ptaoe validate in side error');
1193
1194 psp_message_s.print_error(p_mode=>FND_FILE.log,
1195 p_print_header=>FND_API.G_TRUE);
1196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1197 END IF;
1198
1199 -- fnd_file.put_line(fnd_file.log,' ===== psp_er_ame.get_first_approvers');
1200
1201 psp_er_ame.get_first_approvers(l_request_id ,
1202 l_start_person,
1203 l_end_person ,
1204 l_return_status,
1205 l_retry_request_id );
1206
1207
1208 hr_utility.trace('psp_create_eff_reports--> After psp_er_ame.get_first_approvers');
1209
1210
1211
1212 If (l_return_status = fnd_api.g_ret_sts_unexp_error) or (l_return_status=fnd_api.g_ret_sts_error) then
1213 psp_message_s.print_error(p_mode=>FND_FILE.log,
1214 p_print_header=>FND_API.G_TRUE);
1215
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217
1218 END IF;
1219
1220 -- fnd_file.put_line(fnd_file.log,' ===== psp_xmlgen.update_er_person_xml');
1221
1222 psp_xmlgen.update_er_person_xml(l_start_person ,
1223 l_end_person ,
1224 l_request_id,
1225 l_retry_request_id,
1226 l_return_status) ;
1227
1228 hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_er_person_xml');
1229
1230 If (l_return_status = fnd_api.g_ret_sts_error ) or (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1231 psp_message_s.print_error(p_mode=>FND_FILE.log,
1232 p_print_header=>FND_API.G_TRUE);
1233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1234
1235 END IF;
1236
1237 END IF;
1238 -- hr_utility.trace_off;
1239
1240 EXCEPTION
1241 when FND_API.G_EXC_UNEXPECTED_ERROR then
1242 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1243 when others then
1244 BEGIN
1245 -- hr_utility.trace('psp_create_eff_reports--> Exception '||sqlerrm);
1246 fnd_file.put_line(fnd_file.log,' Unhandled exception raised '||sqlerrm);
1247 fnd_message.set_name('PSP','PSP_ER_CREATE_UNEXPECTED_ERROR');
1248 l_err_mesg := substr(fnd_message.get,1,2000);
1249 l_sqlerrm := l_err_mesg || substr(sqlerrm,1,200);
1250 psp_general.add_report_error(l_request_id, 'E',null, l_retry_request_id, null, l_sqlerrm,l_return_status);
1251
1252 /* Below lines added for supercedence requirement
1253 */
1254 -- hr_utility.trace_off;
1255
1256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1257 END;
1258
1259
1260 END;
1261
1262 PROCEDURE populate_error_table(p_request_id IN NUMBER, p_start_person IN NUMBER, p_end_person IN NUMBER, p_min_effort_report_id IN NUMBER, p_retry_request_id IN NUMBER, p_mode in varchar2, p_match_level in varchar2) IS
1263
1264 l_msg_str varchar2(240);
1265 --l_old_request_id number(15);
1266
1267 --l_retry_request_id number(15);
1268 l_sqlerrm varchar2(240);
1269
1270
1271 l_return_status varchar2(1);
1272
1273 cursor get_skipped_asg is
1274 select distinct pspt.skip_reason, pspt.person_id, paf.assignment_number
1275 from psp_selected_persons_t pspt,
1276 per_all_assignments_f paf
1277 where pspt.request_id = p_request_id
1278 and pspt.person_id between p_start_person and p_end_person
1279 and pspt.assignment_id = paf.assignment_id
1280 and pspt.skip_reason is not null
1281 and paf.effective_start_date = (select max(paf2.effective_start_date)
1282 from per_all_assignments_f paf2
1283 where paf2.assignment_id = paf.assignment_id);
1284
1285 skipped_asg_rec get_skipped_asg%rowtype;
1286
1287 BEGIN
1288
1289 /*
1290
1291 Find out if any persons in person_array do not have new effort reports created.
1292
1293
1294
1295 1. If previous effort report exists for the person, then appropriate error message.
1296
1297 2. if a person in range_cursor is missing entry in psp_eff_rep_details and prev_eff_report does not exist then
1298 say no active distributions for this person.
1299
1300 3. pending distribution Adjustment for employee -- how to determine
1301
1302
1303 4. Errors returned by cost share api.
1304
1305 */
1306
1307
1308
1309 /*
1310
1311 Select max(request_id) into l_old_request_id from psp_report_templates_h prth where request_id < p_request_id and payroll_action_id = p_pact_id;
1312
1313
1314
1315 -- select request_id into l_retry_request_id from pay_payroll_actions where payroll_action_id = p_pact_id;
1316
1317 if g_psp_request_id =p_request_id then
1318 l_retry_request_id :=NULL;
1319 else
1320 l_retry_request_id:=g_psp_request_id;
1321 end if;
1322
1323
1324 */
1325
1326 -- fnd_file.put_line(fnd_file.log,'orig is '||p_request_id||'--RETRY '||l_retry_request_id);
1327 if p_mode = 'PRE-POPULATE' then
1328
1329 /* A pending or Approved Effort Report already exists */
1330
1331 if p_match_level = 'EMP' then
1332 fnd_message.set_name('PSP', 'PSP_EFF_ALREADY_EXISTS');
1333 l_msg_str:= substr(fnd_message.get ,1,240);
1334
1335 update psp_selected_persons_t
1336 set skip_reason = 'ALREADY_EXISTS'
1337 where person_id between p_start_person and p_end_person
1338 and request_id = p_request_id
1339 and person_id in
1340 (select person_id from psp_eff_reports
1341 where status_code in ('N', 'A')
1342 and g_psp_effort_end >= start_date
1343 and g_psp_effort_start <= end_date );
1344
1345
1346 insert into psp_report_errors
1347 (error_sequence_id, request_id, message_level,
1348 source_id, error_message, retry_request_id, pdf_request_id,
1349 source_name, parent_source_id, parent_source_name)
1350 (select psp_report_errors_s.nextval , p_request_id,
1351 'W', pspt.person_id , l_msg_str , p_retry_request_id, null, NULL, NULL, null
1352 from psp_selected_persons_t pspt where pspt.person_id between p_start_person and p_end_person and
1353 pspt.request_id = p_request_id and skip_reason is not null
1354 AND rowid = (select min(rowid) from psp_selected_persons_t inner
1355 where inner.request_id = pspt.request_id
1356 AND inner.person_id = pspt.person_id ));
1357
1358 else
1359
1360 update psp_selected_persons_t
1361 set skip_reason = 'ALREADY_EXISTS'
1362 where person_id between p_start_person and p_end_person
1363 and request_id = p_request_id
1364 and (person_id, assignment_id) in
1365 (select er.person_id, erd.assignment_id
1366 from psp_eff_reports er, psp_eff_report_details erd
1367 where er.status_code in ('N', 'A')
1368 and er.effort_Report_id = erd.effort_report_id
1369 and g_psp_effort_end >= er.start_date
1370 and g_psp_effort_start <= er.end_date );
1371
1372 --- existing ER without asg_id summarization.
1373 update psp_selected_persons_t
1374 set skip_reason = 'ALREADY_EXISTS'
1375 where person_id between p_start_person and p_end_person
1376 and request_id = p_request_id
1377 and person_id in
1378 (select er.person_id
1379 from psp_eff_reports er, psp_eff_report_details erd
1380 where er.status_code in ('N', 'A')
1381 and er.effort_Report_id = erd.effort_report_id
1382 and g_psp_effort_end >= er.start_date
1383 and g_psp_effort_start <= er.end_date
1384 and erd.assignment_id is null);
1385
1386 update psp_selected_persons_t
1387 set skip_reason = 'OTHER_ASG_SKIPPED'
1388 where person_id between p_start_person and p_end_person
1389 and request_id = p_request_id
1390 and skip_reason is null
1391 and person_id in
1392 (select person_id
1393 from psp_selected_persons_t
1394 where person_id between p_start_person and p_end_person
1395 and request_id = p_request_id
1396 and skip_reason = 'ALREADY_EXISTS' );
1397
1398 open get_skipped_asg;
1399 loop
1400
1401 fetch get_skipped_asg into skipped_asg_rec;
1402 if get_skipped_asg%notfound then
1403 close get_skipped_asg;
1404 exit;
1405 end if;
1406
1407 if skipped_asg_rec.skip_reason = 'ALREADY_EXISTS' then
1408 fnd_message.set_name('PSP', 'PSP_ASG_EFF_ALREADY_EXISTS');
1409 fnd_message.set_token('ASG_NUMBER',skipped_asg_rec.assignment_number);
1410 l_msg_str:= substr(fnd_message.get ,1,240);
1411 else
1412 fnd_message.set_name('PSP', 'PSP_ASG_EFF_ALREADY_EXISTS2');
1413 fnd_message.set_token('ASG_NUMBER',skipped_asg_rec.assignment_number);
1414 l_msg_str:= substr(fnd_message.get ,1,240);
1415 end if;
1416
1417 insert into psp_report_errors
1418 (error_sequence_id, request_id, message_level,
1419 source_id, error_message, retry_request_id, pdf_request_id,
1420 source_name, parent_source_id, parent_source_name)
1421 (select psp_report_errors_s.nextval , p_request_id,
1422 'W', skipped_asg_rec.person_id , l_msg_str , p_retry_request_id, null, NULL, NULL, null
1423 from dual);
1424
1425 end loop;
1426
1427 end if;
1428
1429 else
1430 /* No distributions Found */
1431
1432
1433
1434 fnd_message.set_name('PSP', 'PSP_EFF_NO_DISTRIB');
1435 l_msg_str:= substr(fnd_message.get ,1,240);
1436
1437 insert into psp_report_errors
1438 (error_sequence_id, request_id, message_level,
1439 source_id, error_message, retry_request_id, pdf_request_id,
1440 source_name, parent_source_id, parent_source_name)
1441 select psp_report_errors_s.nextval, p_request_id,'W',
1442 pspt.person_id, l_msg_str, p_retry_request_id , null, NULL, NULL, NULL
1443 from psp_selected_persons_t pspt where pspt.person_id between p_start_person
1444 and p_end_person and
1445 pspt.request_id = p_request_id and
1446 pspt.person_id not in (select nvl(person_id,0) from psp_eff_reports where g_psp_effort_end >= start_date and
1447 g_psp_effort_start <= end_date and status_code in ('N', 'A'))
1448 and pspt.person_id not in (select nvl(source_id,0) from psp_report_errors where request_id = g_psp_request_id);
1449
1450
1451 fnd_message.set_name('PSP', 'PSP_EFF_PENDING_DIST_ADJ');
1452 l_msg_str:= substr(fnd_message.get ,1,200);
1453 insert into psp_report_errors
1454 (error_sequence_id, request_id, message_level,
1455 source_id, error_message, retry_request_id, pdf_request_id,
1456 source_name, parent_source_id, parent_source_name)
1457 select psp_report_errors_s.nextval, p_request_id,'W',
1458 pspt.person_id , l_msg_str, p_retry_request_id , null, NULL, NULL, NULL
1459 from psp_selected_persons_t pspt where pspt.person_id between p_start_person and p_end_person and
1460 pspt.request_id = p_request_id and
1461 exists (select nvl(person_id,0) from psp_adjustment_lines where effective_date between g_psp_effort_start
1462 and g_psp_effort_end ) and pspt.person_id not in (select source_id from psp_report_errors where request_id=p_request_id) and
1463 pspt.person_id not in (select nvl(person_id, 0) from psp_eff_reports where g_psp_effort_end>=start_date and
1464 g_psp_effort_start <= end_date);
1465 end if;
1466
1467 EXCEPTION
1468
1469 WHEN NO_DATA_FOUND THEN NULL;
1470
1471 WHEN OTHERS THEN
1472
1473 l_sqlerrm := 'Error inserting in psp_report_errors '|| substr(sqlerrm,1,200);
1474
1475 psp_general.add_report_error(p_request_id, 'E',null, p_retry_request_id, null, l_sqlerrm,l_return_status);
1476
1477
1478
1479 END;
1480
1481 PROCEDURE VALIDATE_PTAOE (p_start_person IN NUMBER,
1482 p_end_person IN NUMBER,
1483 p_request_id IN NUMBER,
1484 p_retry_request_id IN NUMBER,
1485 p_return_status OUT NOCOPY VARCHAR2) IS
1486 l_profile_bg_id number;
1487 l_person_id Number ;
1488 l_project_id Number ;
1489 l_task_id Number ;
1490 l_award_id Number ;
1491 l_exp_org_id Number ;
1492 l_project_name VARCHAR2(30);
1493 l_task_name VARCHAR2(30);
1494 l_award_name VARCHAR2(30);
1495 l_exp_org_name VARCHAR2(240);
1496 l_expenditure_type VARCHAR2(30);
1497 l_return_status CHAR(1);
1498 l_err_mesg varchar2(2000);
1499 l_error_type Number;
1500
1501 Cursor check_ptaoe is
1502 select 1, per.person_id, perd.project_id, NVL(perd.project_name,'NOTFOUND'), perd.task_id, NVL(perd.task_name,'NOTFOUND'),
1503 perd.award_id, NVL(perd.AWARD_SHORT_NAME,'NOTFOUND'), EXPENDITURE_ORGANIZATION_ID ,NVL(perd.exp_org_name,'NOTFOUND'),
1504 perd.expenditure_type
1505 from psp_eff_reports per ,
1506 psp_eff_report_details perd
1507 where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
1508 AND per.request_id = p_request_id
1509 AND person_id between p_start_person and p_end_person
1510 AND(( PROJECT_ID is not null AND PROJECT_NAME is NULL)
1511 OR ( TASK_ID is not null AND TASK_NAME is NULL)
1512 OR ( AWARD_ID is not null AND AWARD_SHORT_NAME is NULL)
1513 OR ( EXPENDITURE_ORGANIZATION_ID is not null AND EXP_ORG_NAME is NULL))
1514 UNION ALL
1515 select 2, per.person_id, perd.project_id, perd.project_name, perd.task_id, perd.task_name,
1516 perd.award_id, perd.AWARD_SHORT_NAME, EXPENDITURE_ORGANIZATION_ID ,perd.exp_org_name,
1517 perd.expenditure_type
1518 from psp_eff_reports per ,
1519 psp_eff_report_details perd
1520 where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
1521 and per.request_id = p_request_id
1522 AND person_id between p_start_person and p_end_person
1523 AND PROJECT_ID is NULL
1524 AND TASK_ID is NULL
1525 AND AWARD_ID is NULL
1526 AND EXPENDITURE_ORGANIZATION_ID is NULL
1527 AND EXPENDITURE_TYPE is NULL
1528 AND gl_sum_criteria_segment_name IS NULL;
1529
1530 CURSOR layout_type_cur IS
1531 SELECT SUBSTR(report_template_code, 6, 3) layout_type
1532 FROM psp_report_templates_h prth
1533 WHERE prth.request_id = p_request_id;
1534
1535 l_layout_type CHAR(3);
1536 l_award_number VARCHAR2(240);
1537 l_project_number VARCHAR2(25);
1538 l_task_number VARCHAR2(25);
1539 l_check_project_number NUMBER;
1540
1541 CURSOR check_award_pi_cur IS
1542 SELECT DISTINCT per.person_id,
1543 perd.award_id,
1544 perd.award_number
1545 FROM psp_eff_reports per,
1546 psp_eff_report_details perd
1547 WHERE per.request_id = p_request_id
1548 AND per.effort_report_id = perd.effort_report_id
1549 AND per.person_id BETWEEN p_start_person AND p_end_person
1550 --AND perd.award_id IS NOT NULL Commented as part of UVA fix 4537063
1551 AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1552
1553 CURSOR check_pm_cur IS
1554 SELECT DISTINCT per.person_id,
1555 perd.project_id,
1556 perd.project_number
1557 FROM psp_eff_reports per,
1558 psp_eff_report_details perd
1559 WHERE per.request_id = p_request_id
1560 AND per.effort_report_id = perd.effort_report_id
1561 AND per.person_id BETWEEN p_start_person AND p_end_person
1562 AND perd.project_id IS NOT NULL
1563 AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1564
1565 CURSOR check_tm_cur IS
1566 SELECT DISTINCT per.person_id,
1567 perd.project_number,
1568 perd.task_id,
1569 perd.task_number
1570 FROM psp_eff_reports per,
1571 psp_eff_report_details perd
1572 WHERE per.request_id = p_request_id
1573 AND per.effort_report_id = perd.effort_report_id
1574 AND per.person_id BETWEEN p_start_person AND p_end_person
1575 AND perd.task_id IS NOT NULL
1576 AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1577
1578 CURSOR check_tm_cur1 IS
1579 SELECT DISTINCT per.person_id,
1580 ppa.segment1,
1581 perd.task_id,
1582 perd.task_number
1583 FROM psp_eff_reports per,
1584 psp_eff_report_details perd,
1585 pa_tasks pt,
1586 pa_projects_all ppa
1587 WHERE per.request_id = p_request_id
1588 AND per.effort_report_id = perd.effort_report_id
1589 AND pt.task_id = perd.task_id
1590 AND ppa.project_id = pt.project_id
1591 AND per.person_id BETWEEN p_start_person AND p_end_person
1592 AND perd.task_id IS NOT NULL
1593 AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1594
1595 CURSOR check_project_number_cur IS
1596 SELECT COUNT(1)
1597 FROM psp_eff_reports per,
1598 psp_eff_report_details perd
1599 WHERE per.request_id = p_request_id
1600 AND per.person_id BETWEEN p_start_person AND p_end_person
1601 AND perd.task_id IS NOT NULL
1602 AND perd.project_id IS NULL
1603 AND ROWNUM = 1;
1604 BEGIN
1605
1606 l_profile_bg_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
1607 p_return_status := fnd_api.g_ret_sts_success;
1608
1609 OPEN layout_type_cur;
1610 FETCH layout_type_cur INTO l_layout_type;
1611 CLOSE layout_type_cur;
1612
1613 OPEN check_ptaoe;
1614 LOOP
1615 FETCH check_ptaoe into l_error_type, l_person_id,l_project_id, l_project_name,l_task_id, l_task_name,
1616 l_award_id, l_award_name, l_exp_org_id, l_exp_org_name, l_expenditure_type ;
1617 EXIT WHEN check_ptaoe%NOTFOUND;
1618 l_err_mesg := NULL;
1619
1620 IF l_error_type = 1 THEN
1621
1622 IF l_project_name = 'NOTFOUND' THEN
1623 fnd_message.set_name('PSP','PSP_ER_INVALID_PROJECT');
1624 fnd_message.set_token('PROJECT_ID',l_project_id);
1625 l_err_mesg := l_err_mesg || fnd_message.get;
1626 END IF;
1627
1628 IF l_task_name = 'NOTFOUND' THEN
1629 fnd_message.set_name('PSP','PSP_ER_INVALID_TASK');
1630 fnd_message.set_token('TASK_ID',l_task_id);
1631 l_err_mesg := l_err_mesg || fnd_message.get;
1632 END IF;
1633
1634 IF l_award_name = 'NOTFOUND' THEN
1635 fnd_message.set_name('PSP','PSP_ER_INVALID_AWARD');
1636 fnd_message.set_token('AWARD_ID',l_award_id);
1637 l_err_mesg := l_err_mesg || fnd_message.get;
1638 END IF;
1639
1640 IF l_exp_org_name = 'NOTFOUND' THEN
1641 fnd_message.set_name('PSP','PSP_ER_INVALID_EXP_ORG');
1642 fnd_message.set_token('EXP_ORG_ID',l_exp_org_id);
1643 l_err_mesg := l_err_mesg || fnd_message.get;
1644 END IF;
1645
1646 ELSE
1647
1648 fnd_message.set_name('PSP','PSP_ER_CI_NOT_FOUND');
1649 l_err_mesg := l_err_mesg || fnd_message.get;
1650 END IF;
1651
1652 IF l_err_mesg IS NOT NULL THEN
1653
1654 IF l_error_type = 1 THEN
1655 fnd_message.set_name('PSP','PSP_ER_CAN_NOT_DISPLAY_DATA');
1656 l_err_mesg := l_err_mesg || fnd_message.get;
1657 END IF;
1658 l_err_mesg := substr(l_err_mesg,1,2000);
1659 psp_general.add_report_error( p_request_id => p_request_id ,
1660 p_message_level => 'E',
1661 p_source_id => l_person_id,
1662 p_retry_request_id => p_retry_request_id,
1663 p_pdf_request_id => NULL,
1664 p_error_message => l_err_mesg,
1665 p_return_status => l_return_status);
1666 p_return_status := fnd_api.g_ret_sts_error;
1667 END IF;
1668
1669 END LOOP;
1670 CLOSE check_ptaoe;
1671
1672 IF (l_layout_type = 'PIV') THEN
1673 OPEN check_award_pi_cur;
1674 LOOP
1675 FETCH check_award_pi_cur INTO l_person_id, l_award_id, l_award_number;
1676 EXIT WHEN check_award_pi_cur%NOTFOUND;
1677
1678 fnd_message.set_name('PSP', 'PSP_ER_INVALID_PI');
1679 fnd_message.set_token('AWARD_NUMBER', l_award_number);
1680 l_err_mesg := fnd_message.get;
1681
1682 psp_general.add_report_error( p_request_id => p_request_id ,
1683 p_message_level => 'E',
1684 p_source_id => l_person_id,
1685 p_retry_request_id => p_retry_request_id,
1686 p_pdf_request_id => NULL,
1687 p_error_message => l_err_mesg,
1688 p_return_status => l_return_status);
1689 p_return_status := fnd_api.g_ret_sts_error;
1690 END LOOP;
1691 CLOSE check_award_pi_cur;
1692
1693 ELSIF (l_layout_type = 'PMG') THEN
1694 OPEN check_pm_cur;
1695 LOOP
1696 FETCH check_pm_cur INTO l_person_id, l_project_id, l_project_number;
1697 EXIT WHEN check_pm_cur%NOTFOUND;
1698
1699 fnd_message.set_name('PSP', 'PSP_ER_INVALID_PM');
1700 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
1701 l_err_mesg := fnd_message.get;
1702
1703 psp_general.add_report_error( p_request_id => p_request_id ,
1704 p_message_level => 'E',
1705 p_source_id => l_person_id,
1706 p_retry_request_id => p_retry_request_id,
1707 p_pdf_request_id => NULL,
1708 p_error_message => l_err_mesg,
1709 p_return_status => l_return_status);
1710 p_return_status := fnd_api.g_ret_sts_error;
1711 END LOOP;
1712 CLOSE check_pm_cur;
1713
1714 ELSIF (l_layout_type = 'TMG') THEN
1715 OPEN check_project_number_cur;
1716 FETCH check_project_number_cur INTO l_check_project_number;
1717 CLOSE check_project_number_cur;
1718
1719 IF (l_check_project_number = 0) THEN
1720 OPEN check_tm_cur;
1721 LOOP
1722 FETCH check_tm_cur INTO l_person_id, l_project_number, l_task_id, l_task_number;
1723 EXIT WHEN check_tm_cur%NOTFOUND;
1724
1725 fnd_message.set_name('PSP', 'PSP_ER_INVALID_TM');
1726 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
1727 fnd_message.set_token('TASK_NUMBER', l_task_number);
1728 l_err_mesg := fnd_message.get;
1729
1730 psp_general.add_report_error( p_request_id => p_request_id ,
1731 p_message_level => 'E',
1732 p_source_id => l_person_id,
1733 p_retry_request_id => p_retry_request_id,
1734 p_pdf_request_id => NULL,
1735 p_error_message => l_err_mesg,
1736 p_return_status => l_return_status);
1737 p_return_status := fnd_api.g_ret_sts_error;
1738 END LOOP;
1739 CLOSE check_tm_cur;
1740 ELSE
1741 OPEN check_tm_cur1;
1742 LOOP
1743 FETCH check_tm_cur1 INTO l_person_id, l_project_number, l_task_id, l_task_number;
1744 EXIT WHEN check_tm_cur1%NOTFOUND;
1745
1746 fnd_message.set_name('PSP', 'PSP_ER_INVALID_TM');
1747 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
1748 fnd_message.set_token('TASK_NUMBER', l_task_number);
1749 l_err_mesg := fnd_message.get;
1750
1751 psp_general.add_report_error( p_request_id => p_request_id ,
1752 p_message_level => 'E',
1753 p_source_id => l_person_id,
1754 p_retry_request_id => p_retry_request_id,
1755 p_pdf_request_id => NULL,
1756 p_error_message => l_err_mesg,
1757 p_return_status => l_return_status);
1758 p_return_status := fnd_api.g_ret_sts_error;
1759 END LOOP;
1760 CLOSE check_tm_cur1;
1761 END IF;
1762 END IF;
1763
1764 EXCEPTION
1765 WHEN OTHERS THEN
1766 p_return_status := fnd_api.g_ret_sts_unexp_error;
1767 END VALIDATE_PTAOE;
1768
1769 END;