[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PSP_CREATE_EFF_REPORTS
Source
1 PACKAGE BODY PSP_CREATE_EFF_REPORTS AS
2 /* $Header: PSPERCRB.pls 120.24.12020000.5 2013/04/05 11:05:11 lkodaman ship $*/
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.set_of_books_id = :l_profile_sob_id -- Added for Bug 11923895
400 and psl.business_group_id = :l_profile_bg_id -- Added for Bug 11923895
401 AND psl.status_code = '||''''||'A'||''''||'
402 AND
403 ( EXISTS
404 (select 1 from psp_element_set_members_v pesr where pesr.element_set_id = :l_element_set_id and
405 pesr.element_type_id = ppl.element_type_id )
406 OR EXISTS
407 (select 1 from psp_element_set_members_v pesr where pesr.element_set_id =
408 :l_element_set_id and pesr.element_type_id = pal.element_type_id)
409 OR EXISTS
410 (select 1 from psp_element_set_members_v pesr where pesr.element_set_id =
411 :l_element_set_id and pesr.element_type_id = ppg.element_type_id )
412 ) AND
413 /* pspt.person_id not in (select person_id from psp_eff_reports pea where
414 pea.end_date >= :effort_start and pea.start_date <= :effort_end and
415 status_code in ('||''''||'N'||''''||','||''''||'A'||''''||')) AND */
416 ((psl.source_type in ('||''''||'N'||''''||' , '|| ''''|| 'O' ||'''' ||' ) and
417 psl.summary_line_id = pdnh.summary_line_id
418 and pdnh.distribution_date between :effort_start and :effort_end
419 AND pdnh.reversal_entry_flag is NULL
420 AND pdnh.adjustment_batch_name is null
421 )
422 OR (psl.source_type=' ||''''|| 'P'||'''' || '
423 AND ppg.status_code ='||''''||'A' || ''''||'
424 and ppg.distribution_date between :effort_start and :effort_end
425 AND ppg.reversal_entry_flag is NULL
426 AND ppg.summary_line_id = psl.summary_line_id
427 AND ppg.adjustment_batch_name is null
428 )
429 OR (psl.source_type='||'''' || 'A' || ''''||'
430 and pal.distribution_date between :effort_start and :effort_end
431 AND pal.status_code = ' || ''''|| 'A' || ''''|| '
432 AND NVL(pal.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
433 || ' AND pal.reversal_entry_flag is NULL
434 AND pal.summary_line_id = psl.summary_line_id
435 AND pal.adjustment_batch_name is null
436 ))
437 /* Added for hospital effort report Change*/
438 UNION ALL
439 SELECT
440 psl.person_id person_id, null ASSIGNMENT_ID,
441 null PROJECT_ID, null TASK_ID,
442 null AWARD_ID, null EXPENDITURE_ORGANIZATION_ID, null EXPENDITURE_TYPE,
443 null SEGMENT1, null SEGMENT2, null SEGMENT3, null SEGMENT4, null SEGMENT5,
444 null SEGMENT6, null SEGMENT7, null SEGMENT8, null SEGMENT9, null SEGMENT10,
445 null SEGMENT11, null SEGMENT12, null SEGMENT13, null SEGMENT14, Null SEGMENT15,
446 null SEGMENT16, null SEGMENT17, null SEGMENT18, null SEGMENT19, null SEGMENT20,
447 Null SEGMENT21, null SEGMENT22, null SEGMENT23, null SEGMENT24, null SEGMENT25,
448 null SEGMENT26, null SEGMENT27, null SEGMENT28, null SEGMENT29, null SEGMENT30,
449 psl.distribution_amount distribution_amount, null , null
450 FROM psp_external_effort_lines psl,
451 psp_Selected_persons_t pspt
452 WHERE psl.person_id = pspt.person_id
453 and pspt.request_id = :l_request_id
454 AND pspt.person_id between :l_start_person and :l_end_person
455 /*
456 AND EXISTS (select 1
457 from psp_element_set_members_v pesr
458 where pesr.element_set_id = :l_element_set_id
459 and pesr.element_type_id = psl.element_type_id)
460 */
461 AND psl.distribution_date between :effort_start and :effort_end
462 )
463 group by
464 person_id , ASSIGNMENT_ID,
465 PROJECT_ID, TASK_ID,
466 AWARD_ID, EXPENDITURE_ORGANIZATION_ID, EXPENDITURE_TYPE,
467 SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5,
468 SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10,
469 SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15,
470 SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20,
471 SEGMENT21, SEGMENT22, SEGMENT23, SEGMENT24, SEGMENT25,
472 SEGMENT26, SEGMENT27, SEGMENT28, SEGMENT29, SEGMENT30
473 /*psl.person_id */
474 ';
475
476 if p_supercede_mode is not null then
477 ---fnd_file.put_line(fnd_file.log,' supercede mode is not null, changing the exec string ');
478 hr_utility.trace(' create_eff --> supercede mode is not null, changing the exec string ');
479 --- added for uva issues (assignment matching only)
480 -- rows inserted thru supercede will have -ve request_id and will be rolled
481 --- back immediately by summarize_transfer process (transient)
482
483 --Bug 5237611: Fixed terminated assignment Issue, added FF suport for assignment
484 /*
485 if l_selection_match_level = 'EMP' then
486 insert into psp_selected_persons_t (request_id, person_id)
487 select -1 * l_request_id, gt.person_id from psp_supercede_persons_gt gt
488 where gt.person_id > 0 ;
489 else
490 */
491 insert into psp_selected_persons_t (request_id, person_id, assignment_id)
492 select -1 * l_request_id, person_id, assignment_id
493 from psp_Eff_reports er,
494 psp_eff_report_details erd
495 where er.effort_Report_id = erd.effort_Report_id
496 and er.request_id = l_request_id
497 and er.status_code in ('N','A')
498 and er.person_id in (select person_id from psp_supercede_persons_gt where person_id > 0) ;
499 g_exec_string := replace(g_exec_string, 'psl.person_id = pspt.person_id' ,
500 'psl.person_id = pspt.person_id and psl.assignment_id = pspt.assignment_id ');
501 -- end if;
502 g_exec_string := replace(g_exec_string, 'pspt.person_id between :l_start_person and :l_end_person' ,
503 ' nvl(:l_start_person,-9) = nvl(:l_start_person,-9) and
504 nvl(:l_end_person,-9) = nvl(:l_end_person,-9) ');
505 g_exec_string := replace(g_exec_string, 'pea.end_date >= :effort_start and pea.start_date <= :effort_end and',null);
506 g_exec_string := replace(g_exec_string, 'status_code in ('||''''||'N'||''''||','||''''||'A'||''''||')) AND',null);
507 g_exec_string := replace(g_exec_string, 'pspt.person_id not in (select person_id from psp_eff_reports pea where',null);
508 g_exec_string := replace(g_exec_string, 'group by psl.person_id',
509 ' 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');
510 g_exec_string := replace(g_exec_string, 'and pspt.request_id = :l_request_id',
511 ' and -1 * pspt.request_id = :l_request_id and pspt.skip_reason is null ');
512 else
513 -- added for UVA issue.
514 g_exec_string := replace(g_exec_string, 'and pspt.request_id = :l_request_id',
515 ' and pspt.request_id = :l_request_id and pspt.skip_reason is null ');
516 -- if l_selection_match_level = 'ASG' then
517 g_exec_string := replace(g_exec_string, 'psl.person_id = pspt.person_id' ,
518 'psl.person_id = pspt.person_id and psl.assignment_id = pspt.assignment_id ');
519 -- end if;
520 end if;
521
522 OPEN get_summarization_criteria(l_request_id);
523
524
525 FETCH get_summarization_criteria BULK collect into eff_template_sum_rec.array_sum_criteria,
526 eff_template_sum_rec.array_sum_order;
527
528 CLOSE get_summarization_criteria;
529 -- Bug Fix 4244924:YALE ENHANCEMENTS
530 IF PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(l_profile_bg_id,'PSP_USE_GL_PTAOE_MAPPING') = 'Y' THEN
531 PSP_GENERAL.GET_GL_PTAOE_MAPPING(p_business_group_id => l_profile_bg_id,
532 p_proj_segment => l_proj_segment,
533 p_tsk_segment => l_tsk_segment,
534 p_awd_sgement => l_awd_sgement,
535 p_exp_org_segment=> l_exp_org_segment,
536 p_exp_type_segment => l_exp_type_segment);
537 END IF;
538
539
540 FOR I IN 1..EFF_TEMPLATE_SUM_REC.ARRAY_SUM_CRITERIA.COUNT
541
542 LOOP
543
544 IF PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(l_profile_bg_id,'PSP_USE_GL_PTAOE_MAPPING') = 'N' THEN
545 IF substr(eff_template_sum_rec.array_sum_criteria(i),1,7) = 'SEGMENT' then
546 g_exec_string:= replace(g_exec_string, 'null '||eff_template_sum_rec.array_sum_criteria(i)||',',
547 eff_template_sum_rec.array_sum_criteria(i) ||',');
548 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
549 l_gl_flag:='Y';
550 -- g_exec_string:= replace(g_exec_string, ' psl.person_id = pspt.person_id', ' psl.person_id = pspt.person_id '||l_select_string);
551 l_grp_string:= l_grp_string || ',' ||'gcc.'|| eff_template_sum_rec.array_sum_criteria(i) ;
552
553 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'SSD' then
554 -- l_sbd_string := 'min(nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date))) distribution_date1';
555 l_sbd_string := 'nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date)) distribution_date1';
556 g_exec_string:= replace(g_exec_string, 'null distribution_date1', l_sbd_string);
557 ELSIF eff_template_sum_rec.array_sum_criteria(i)='SED' then
558 -- l_sed_string:=' max(nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date ))) distribution_date2';
559 l_sed_string:=' nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date )) distribution_date2';
560 g_exec_string:= replace(g_exec_string, 'null distribution_date2', l_sed_string);
561 ELSE
562 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)||',');
563 l_grp_string := l_grp_string || ', ' ||'psl.'||eff_template_sum_rec.array_sum_criteria(i);
564 END IF;
565 ELSE
566 -- Bug Fix 4244924:YALE ENHANCEMENTS
567 IF eff_template_sum_rec.array_sum_criteria(i)= 'GL:PROJECT_ID' then
568 g_exec_string:= replace(g_exec_string, 'null PROJECT_ID,', 'gcc.'|| l_proj_segment ||' PROJECT_ID,');
569 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
570 l_gl_flag:='Y';
571 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_proj_segment ;
572 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'GL:TASK_ID' then
573 g_exec_string:= replace(g_exec_string, 'null TASK_ID,', 'gcc.'|| l_tsk_segment ||' TASK_ID,');
574 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
575 l_gl_flag:='Y';
576 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_tsk_segment ;
577 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'GL:AWARD_ID' then
578 g_exec_string:= replace(g_exec_string, 'null AWARD_ID,', 'gcc.'|| l_awd_sgement ||' AWARD_ID,');
579 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
580 l_gl_flag:='Y';
581 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_awd_sgement ;
582 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'GL:EXPENDITURE_ORGANIZATION_ID' then
583 g_exec_string:= replace(g_exec_string, 'null EXPENDITURE_ORGANIZATION_ID,', 'gcc.'|| l_exp_org_segment ||' EXPENDITURE_ORGANIZATION_ID,');
584 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
585 l_gl_flag:='Y';
586 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_exp_org_segment ;
587 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'GL:EXPENDITURE_TYPE' then
588 g_exec_string:= replace(g_exec_string, 'null EXPENDITURE_TYPE,', 'gcc.'|| l_exp_type_segment ||' EXPENDITURE_TYPE,');
589 l_select_string:= ' and psl.gl_code_combination_id = gcc.code_combination_id(+) ';
590 l_gl_flag:='Y';
591 l_grp_string:= l_grp_string || ',' ||'gcc.'|| l_exp_type_segment ;
592 ELSIF eff_template_sum_rec.array_sum_criteria(i)= 'SSD' then
593 -- l_sbd_string := 'min(nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date))) distribution_date1';
594 l_sbd_string := 'nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date)) distribution_date1';
595 g_exec_string:= replace(g_exec_string, 'null distribution_date1', l_sbd_string);
596 ELSIF eff_template_sum_rec.array_sum_criteria(i)='SED' then
597 -- l_sed_string:=' max(nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date))) distribution_date2';
598 l_sed_string:=' nvl(pdnh.distribution_date, nvl(ppg.distribution_date, pal.distribution_date)) distribution_date2';
599 g_exec_string:= replace(g_exec_string, 'null distribution_date2', l_sed_string);
600 ELSE
601 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)||',');
602 l_grp_string := l_grp_string || ', ' ||'psl.'||eff_template_sum_rec.array_sum_criteria(i);
603 END IF;
604 END IF;
605 END LOOP;
606
607 IF NVL(l_gl_flag, 'N') ='Y' then
608
609 g_exec_string:= replace(g_exec_string, ' psl.person_id = pspt.person_id', ' psl.person_id = pspt.person_id '||l_select_string);
610
611 -- g_exec_string:=replace(g_exec_string,'ppl WHERE','ppl, gl_code_combinations gcc WHERE');
612 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');
613 END IF;
614
615 -- g_exec_string := replace(g_exec_string, ' group by psl.person_id ' , ' group by person_id'||l_grp_string);
616
617
618 --fnd_file.put_line(fnd_file.log,' dyn sql = '||g_exec_string);
619 --fnd_file.put_line(fnd_file.log,' dyn sql = '||l_grp_string);
620
621 --- hr_utility.trace(' dyn sql = '||g_exec_string);
622
623 /*
624 det_string := '
625 effort_det_lines_rec.person_id,
626 effort_det_lines_rec.assignment_id,
627 effort_det_lineS_rec.project_id, effort_det_lineS_rec.task_id ,
628 effort_Det_lines_rec.award_id,
629 effort_det_lines_rec.expenditure_organization_id,
630 */
631
632
633 execute immediate ' begin ' ||g_exec_string ||';
634 end; ' using
635 OUT det_person_id,
636 OUT det_assignment_id ,
637 OUT det_project_id,
638 OUT det_task_id ,
639 OUT det_award_id,
640 OUT det_exp_org_id,
641 OUT det_expenditure_type,
642 OUT det_segment1,
643 OUT det_segment2,
644 OUT det_segment3,
645 OUT det_segment4,
646 OUT det_segment5,
647 OUT det_segment6,
648 OUT det_segment7,
649 OUT det_segment8,
650 OUT det_segment9,
651 OUT det_segment10,
652 OUT det_segment11,
653 OUT det_segment12,
654 OUT det_segment13,
655 OUT det_segment14,
656 OUT det_segment15,
657 OUT det_segment16,
658 OUT det_segment17,
659 OUT det_segment18,
660 OUT det_segment19,
661 OUT det_segment20,
662 OUT det_segment21,
663 OUT det_segment22 ,
664 OUT det_segment23,
665 OUT det_segment24,
666 OUT det_segment25,
667 OUT det_segment26,
668 OUT det_segment27,
669 OUT det_segment28,
670 OUT det_segment29,
671 OUT det_segment30 ,
672 OUT det_distribution_amount,
673 OUT det_schedule_start_date,
674 OUT det_schedule_end_date,
675 IN l_request_id, l_start_person, l_end_person, l_profile_sob_id, l_profile_bg_id, l_element_set_id, p_effort_start, p_effort_end --Added for Bug 11923895
676 ;
677
678 -- fnd_file.put_line(fnd_file.log,' =====after dyn sql '||i);
679
680 hr_utility.trace('psp_create_eff_reports--> After dyn sql');
681
682
683
684
685 SELECT NVL(max(effort_report_id),0) into min_effort_report_id from psp_eff_reports;
686 if p_supercede_mode is null then
687 OPEN get_all_person(l_request_id);
688 FETCH get_all_person bulk collect into person_rec.array_person_id;
689 CLOSE get_all_person;
690 else
691 -- added following global var for Supercede process
692 g_summarization_criteria := l_grp_string;
693 OPEN get_supercede_persons;
694 FETCH get_supercede_persons bulk collect into person_rec.array_person_id;
695 CLOSE get_supercede_persons;
696 hr_utility.trace(' psp_create_eff_reports--> get_supercede_persons Count = '|| person_rec.array_person_id.count);
697 end if;
698
699
700
701 hr_utility.trace('psp_create_eff_reports--> After bulk fetch');
702 -- fnd_file.put_line(fnd_file.log,' after bulk fetch ');
703
704 -- fnd_file.put_line(fnd_file.log, 'count is '||effort_det_lines_rec.person_id.count);
705
706
707 /*
708 IF effort_det_lines_rec.person_id.count > 0 then
709
710 l_loop_count := l_loop_count + effort_det_lines_rec.person_id.count;
711
712
713 */
714
715 -- fnd_file.put_line(fnd_file.log, 'count person is '||det_person_id.count);
716 hr_utility.trace ('count person is '||det_person_id.count);
717
718 IF det_person_id.count >0 then
719 l_loop_count := l_loop_count + det_person_id.count;
720 -- fnd_file.put_line(fnd_file.log,' before inserting into psp_eff_reports ');
721
722 -- fnd_file.put_line(fnd_file.log,' =====inserting in psp_eff_reports ');
723
724 FORALL i IN 1..person_rec.array_person_id.count
725 insert into psp_eff_reports(
726 effort_report_id,
727 status_code,
728 person_id,
729 object_version_number,
730 start_date,
731 end_date,
732 template_id,
733 request_id,
734 currency_code,
735 last_update_date,
736 last_updated_by,
737 last_update_login,
738 created_by,
739 creation_date,
740 business_group_id,
741 set_of_books_id
742 )
743 values
744 (
745 psp_effort_reports_s.nextval,
746 decode(p_supercede_mode , null, 'N', 'T'),
747 person_rec.array_person_id(i),
748 1,
749 p_effort_Start,
750 p_effort_end,
751 decode(p_supercede_mode, null, g_psp_template_id, -999),
752 l_request_id,
753 l_bg_currency_code,
754 sysdate,
755 fnd_global.user_id ,
756 fnd_global.user_id,
757 fnd_global.user_id,
758 sysdate,
759 l_profile_bg_id,
760 l_profile_sob_id) returning effort_report_id bulk collect into person_rec.array_effort_report_id;
761
762 end if;
763
764 -- fnd_file.put_line(fnd_file.log , 'after insert into eff '||person_rec.array_effort_report_id.count);
765
766
767 hr_utility.trace('psp_create_eff_reports--> After insert into eff '||person_rec.array_effort_report_id.count);
768 /*
769
770 */
771
772
773
774
775 -- fnd_file.put_line(fnd_file.log,' before inserting into psp_report_details ');
776
777 -- fnd_file.put_line(fnd_file.log,' =====inserting in psp_eff_report_details ');
778
779 hr_utility.trace('psp_create_eff_reports--> before inserting into details ');
780
781 FORALL i in 1..det_person_id.count
782 insert into psp_eff_report_details(
783 effort_report_detail_id,
784 effort_report_id,
785 object_version_number,
786 assignment_id,
787 GL_SEGMENT1,
788 GL_SEGMENT2,
789 GL_SEGMENT3,
790 GL_SEGMENT4,
791 GL_SEGMENT5,
792 GL_SEGMENT6,
793 GL_SEGMENT7,
794 GL_SEGMENT8,
795 GL_SEGMENT9,
796 GL_SEGMENT10,
797 GL_SEGMENT11,
798 GL_SEGMENT12,
799 GL_SEGMENT13,
800 GL_SEGMENT14,
801 GL_SEGMENT15,
802 GL_SEGMENT16,
803 GL_SEGMENT17,
804 GL_SEGMENT18,
805 GL_SEGMENT19,
806 GL_SEGMENT20,
807 GL_SEGMENT21,
808 GL_SEGMENT22,
809 GL_SEGMENT23,
810 GL_SEGMENT24,
811 GL_SEGMENT25,
812 GL_SEGMENT26,
813 GL_SEGMENT27,
814 GL_SEGMENT28,
815 GL_SEGMENT29,
816 GL_SEGMENT30,
817 project_id,
818 expenditure_organization_id,
819 expenditure_type,
820 task_id,
821 award_id,
822 actual_salary_amt,
823 payroll_percent,
824 schedule_start_date,
825 schedule_end_date,
826 last_update_date,
827 last_updated_by,
828 last_update_login,
829 created_by,
830 creation_date)
831 values
832 (
833 psp_eff_report_details_s.nextval,
834 1,
835 1,
836 det_assignment_id(i),
837 det_segment1(i),
838 det_segment2(i),
839 det_segment3(i),
840 det_segment4(i),
841 det_segment5(i),
842 det_segment6(i),
843 det_segment7(i),
844 det_segment8(i),
845 det_segment9(i),
846 det_segment10(i),
847 det_segment11(i),
848 det_segment12(i),
849 det_segment13(i),
850 det_segment14(i),
851 det_segment15(i),
852 det_segment16(i),
853 det_segment17(i),
854 det_segment18(i),
855 det_segment19(i),
856 det_segment20(i),
857 det_segment21(i),
858 det_segment22(i),
859 det_segment23(i),
860 det_segment24(i),
861 det_segment25(i),
862 det_segment26(i),
863 det_segment27(i),
864 det_segment28(i),
865 det_segment29(i),
866 det_segment30(i),
867 det_project_id(i),
868 det_exp_org_id(i) ,
869 det_expenditure_type(i),
870 det_task_id(i),
871 det_award_id(i),
872 det_distribution_amount(i),
873 0,
874 det_schedule_start_date(i),
875 det_schedule_end_date(i),
876 sysdate,
877 fnd_global.user_id,
878 fnd_global.user_id,
879 fnd_global.user_id,
880 sysdate
881 ) 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;
882
883
884 -- fnd_file.put_line(fnd_file.log,'==== after det '|| effort_det_lines_rec.effort_report_detail_id.count);
885
886
887
888
889 -- fnd_file.put_line(fnd_file.log,' after deatils ');
890
891 -- fnd_file.put_line(fnd_file.log,' ===== IF p_supercede_mode is not null THEN ');
892
893 IF p_supercede_mode is not null THEN
894 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 );
895
896 FORALL i in 1..effort_det_lines_rec.effort_report_detail_id.count
897 update psp_eff_report_details set effort_report_id =
898 (select effort_report_id from psp_eff_reports where
899 person_id =effort_Det_lines_rec.person_id(i) and status_code = 'T'
900 and request_id = l_request_id ) where
901 effort_report_detail_id= effort_det_lines_rec.effort_report_detail_id(i) ;
902
903 ELSE
904
905 FORALL i in 1..effort_det_lines_rec.effort_report_detail_id.count
906 update psp_eff_report_details set effort_report_id =
907 (select effort_report_id from psp_eff_reports where
908 person_id =effort_Det_lines_rec.person_id(i) and effort_report_id > min_effort_report_id ) where
909 effort_report_detail_id= effort_det_lines_rec.effort_report_detail_id(i) ;
910
911 END IF;
912
913
914
915 -- fnd_file.put_line(fnd_file.log,' after update of effort report details ');
916
917 hr_utility.trace('psp_create_eff_reports--> After update of psp_eff_report_detail ');
918
919 effort_Det_lines_rec.effort_report_detail_id.delete;
920 effort_det_lines_rec.person_id.delete;
921
922
923
924 /* delete the det_details arrays */
925
926 det_person_id.delete;
927 det_assignment_id.delete;
928 det_project_id.delete;
929 det_task_id.delete;
930 det_award_id.delete;
931 det_exp_org_id.delete;
932 det_expenditure_type.delete;
933 det_segment1.delete;
934 det_segment2.delete;
935 det_segment3.delete;
936 det_segment4.delete;
937 det_segment5.delete;
938 det_segment6.delete;
939 det_segment7.delete;
940 det_segment8.delete;
941 det_segment9.delete;
942 det_segment10.delete;
943 det_segment11.delete;
944 det_segment12.delete;
945 det_segment13.delete;
946 det_segment14.delete;
947 det_segment15.delete;
948 det_segment16.delete;
949 det_segment17.delete;
950 det_segment18.delete;
951 det_segment19.delete;
952 det_segment20.delete;
953 det_segment21.delete;
954 det_segment22.delete;
955 det_segment23.delete;
956 det_segment24.delete;
957 det_segment25.delete;
958 det_segment26.delete;
959 det_segment27.delete;
960 det_segment28.delete;
961 det_segment29.delete;
962 det_segment30.delete;
963 det_distribution_amount.delete;
964 det_schedule_start_date.delete;
965 det_schedule_end_date.delete;
966
967 -- OHSU Changes
968
969
970 -- fnd_file.put_line(fnd_file.log,' ===== IF l_HUNDRED_PCENT_EFF_AT_PER_ASG');
971
972 IF l_HUNDRED_PCENT_EFF_AT_PER_ASG = 'A' then
973
974 select sum(actual_salary_amt), effort_report_id, assignment_id bulk collect
975 into person_rec.sum_tot, person_rec.array_effort_report_id, person_rec.array_assignment_id
976 from psp_eff_report_details where
977 effort_report_id > min_effort_report_id group by effort_report_id, assignment_id;
978
979 hr_utility.trace('psp_create_eff_reports--> After getting sum');
980
981 FORALL i in 1..person_rec.array_assignment_id.count
982 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))
983 where effort_report_id = person_rec.array_effort_report_id(i)
984 and assignment_id = person_rec.array_assignment_id(i);
985
986
987 person_rec.array_effort_report_id.delete;
988 person_rec.sum_tot.delete;
989 person_rec.array_assignment_id.delete;
990
991
992 select sum(payroll_percent), effort_report_id, assignment_id bulk collect
993 into person_rec.payroll_percent_tot, person_rec.array_effort_report_id, person_rec.array_assignment_id
994 from psp_eff_report_details where effort_report_id > min_effort_report_id group by effort_report_id , assignment_id;
995
996
997 hr_utility.trace('psp_create_eff_reports--> After sum payroll percent');
998
999 FORALL i in 1..person_rec.array_assignment_id.count
1000 update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i))
1001 where effort_report_detail_id in (
1002 select max(effort_report_detail_id)
1003 from psp_eff_report_details
1004 where effort_report_id = person_rec.array_effort_report_id(i)
1005 and person_rec.payroll_percent_tot(i)<>0 and
1006 assignment_id = person_rec.array_assignment_id(i)
1007 ) ;
1008 ELSE
1009
1010 select sum(actual_salary_amt), effort_report_id bulk collect into person_rec.sum_tot, person_rec.array_effort_report_id
1011 from psp_eff_report_details where
1012 effort_report_id > min_effort_report_id group by effort_report_id ;
1013
1014 hr_utility.trace('psp_create_eff_reports--> After getting sum');
1015
1016 --- fnd_file.put_line(fnd_file.log,' after getting sum ');
1017
1018 FORALL i in 1..person_rec.array_effort_report_id.count
1019 -- update psp_Eff_report_details set payroll_percent = round(actual_salary_amt/person_rec.sum_tot(i),2)*100 where effort_report_id =
1020 -- person_rec.array_effort_report_id(i);
1021 -- check for zero sum , flag error if so
1022 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 =
1023 person_rec.array_effort_report_id(i);
1024
1025 person_rec.array_effort_report_id.delete;
1026 person_rec.sum_tot.delete;
1027
1028
1029 select sum(payroll_percent), effort_report_id
1030 bulk collect into person_rec.payroll_percent_tot, person_rec.array_effort_report_id
1031 from psp_eff_report_details where effort_report_id > min_effort_report_id group by effort_report_id ;
1032
1033
1034 FORALL i in 1..person_rec.array_effort_report_id.count
1035 update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i)) where
1036 effort_report_detail_id in (select max(effort_report_detail_id) from psp_eff_report_details where
1037 effort_report_id = person_rec.array_effort_report_id(i) and person_rec.payroll_percent_tot(i)<>0) ;
1038 END IF;
1039
1040 -- fnd_file.put_line(fnd_file.log,' after getting sum ');
1041
1042
1043 /*
1044 should be person or assignment depending on whether assignment is used as a summarization criteria
1045 */
1046
1047
1048 -- OHSU Changes
1049 /*
1050
1051 */
1052
1053
1054 /* to update the last line in the case it exceeds 100 */
1055
1056
1057 -- fnd_file.put_line(fnd_file.log,' after details update ');
1058
1059
1060
1061 -- OHSU Changes
1062 /*
1063
1064 */
1065
1066 /*
1067
1068
1069
1070 */
1071
1072 /* Add the difference if any to the last detail line for each effort report_id */
1073
1074
1075 -- OHSU Changes
1076 /*
1077 FORALL i in 1..person_rec.array_effort_report_id.count
1078 update psp_eff_report_details set payroll_percent = payroll_percent + (100.00 - person_rec.payroll_percent_tot(i)) where
1079 effort_report_detail_id in (select max(effort_report_detail_id) from psp_eff_report_details where
1080 effort_report_id = person_rec.array_effort_report_id(i) and person_rec.payroll_percent_tot(i)<>0) ;
1081 */
1082
1083
1084 delete from psp_eff_reports per where effort_report_id > min_effort_report_id and
1085 not exists (select 1 from psp_eff_report_details perd where
1086 perd.effort_report_id = per.effort_report_id);
1087
1088 -- fnd_file.put_line(fnd_file.log,' after delete of orphan record ');
1089
1090
1091 /* added this call just to see how retry works
1092
1093 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1094
1095
1096 */
1097
1098
1099
1100
1101 if p_supercede_mode is not null then
1102
1103
1104 -- fnd_file.put_line(fnd_file.log,' before return ');
1105 retcode := 0;
1106 return;
1107 end if;
1108
1109
1110
1111
1112
1113 -- get cost share info
1114
1115 -- psp_eff_report_details_api.update_eff_report_details(l_request_id);
1116
1117 --psp_eff_report_details_api.update_eff_report_details(p_validate , l_request_id, p_warning );
1118 /* for Bug fix 4089645 Added Person id check */
1119 -- fnd_file.put_line(fnd_file.log,' ===== Populate_error_table');
1120
1121
1122 Populate_error_table( l_request_id, l_start_person, l_end_person, min_effort_report_id, l_retry_request_id, null, null);
1123
1124 if person_rec.array_effort_report_id.count >0 then
1125
1126 person_rec.array_effort_report_id.delete;
1127 person_rec.payroll_percent_tot.delete;
1128 /*
1129 IF PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(l_profile_bg_id,'PSP_USE_GL_PTAOE_MAPPING') = 'Y' THEN
1130 psp_xmlgen.copy_ptaoe_from_gl_segments(l_start_person ,
1131 l_end_person ,
1132 l_request_id,
1133 l_retry_request_id,
1134 l_profile_bg_id,
1135 l_return_status) ;
1136 hr_utility.trace('psp_create_eff_reports--> After xmlgen.copy_ptaoe_from_gl_segments');
1137
1138 If (l_return_status = fnd_api.g_ret_sts_error) or (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1139 psp_message_s.print_error(p_mode=>FND_FILE.log,
1140 p_print_header=>FND_API.G_TRUE);
1141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1142
1143 END IF;
1144 END IF;
1145 */
1146
1147 if p_warning = true then
1148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1149 end if;
1150
1151 hr_utility.trace('psp_create_eff_reports--> After costs share API');
1152 -- fnd_file.put_line(fnd_file.log,' ===== psp_xmlgen.update_er_details');
1153
1154 psp_xmlgen.update_er_details(l_start_person ,
1155 l_end_person ,
1156 l_request_id,
1157 l_retry_request_id,
1158 l_return_status) ;
1159
1160 hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_er_details');
1161 If (l_return_status = fnd_api.g_ret_sts_error) or (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1162 psp_message_s.print_error(p_mode=>FND_FILE.log,
1163 p_print_header=>FND_API.G_TRUE);
1164 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1165
1166 END IF;
1167 -- fnd_file.put_line(fnd_file.log,' ===== psp_eff_report_details_api.update_eff_report_details');
1168
1169
1170 psp_xmlgen.update_grouping_category(l_start_person, l_end_person, l_request_id, l_return_status) ;
1171
1172 hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_grouping_category');
1173 If (l_return_status = fnd_api.g_ret_sts_error) or (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1174 psp_message_s.print_error(p_mode=>FND_FILE.log,
1175 p_print_header=>FND_API.G_TRUE);
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177 END IF;
1178
1179 -- fnd_file.put_line(fnd_file.log,' ===== psp_eff_report_details_api.update_eff_report_details');
1180
1181 psp_eff_report_details_api.update_eff_report_details(p_validate , l_request_id,l_start_person, l_end_person, p_warning );
1182
1183 -- Check weather Project, Task, Award, Exp Org are valid or not
1184
1185 VALIDATE_PTAOE(p_start_person => l_start_person,
1186 p_end_person => l_end_person,
1187 p_request_id => l_request_id,
1188 p_retry_request_id => l_retry_request_id,
1189 p_return_status => l_return_status) ;
1190
1191 -- fnd_file.put_line(fnd_file.log,' ===== after ptaoe validate');
1192
1193 If (l_return_status = fnd_api.g_ret_sts_unexp_error) or (l_return_status = fnd_api.g_ret_sts_error) then
1194 -- fnd_file.put_line(fnd_file.log,' ===== after ptaoe validate in side error');
1195
1196 psp_message_s.print_error(p_mode=>FND_FILE.log,
1197 p_print_header=>FND_API.G_TRUE);
1198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1199 END IF;
1200
1201 -- fnd_file.put_line(fnd_file.log,' ===== psp_er_ame.get_first_approvers');
1202
1203 psp_er_ame.get_first_approvers(l_request_id ,
1204 l_start_person,
1205 l_end_person ,
1206 l_return_status,
1207 l_retry_request_id );
1208
1209
1210 hr_utility.trace('psp_create_eff_reports--> After psp_er_ame.get_first_approvers');
1211
1212
1213
1214 If (l_return_status = fnd_api.g_ret_sts_unexp_error) or (l_return_status=fnd_api.g_ret_sts_error) then
1215 psp_message_s.print_error(p_mode=>FND_FILE.log,
1216 p_print_header=>FND_API.G_TRUE);
1217
1218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219
1220 END IF;
1221
1222 -- fnd_file.put_line(fnd_file.log,' ===== psp_xmlgen.update_er_person_xml');
1223
1224 psp_xmlgen.update_er_person_xml(l_start_person ,
1225 l_end_person ,
1226 l_request_id,
1227 l_retry_request_id,
1228 l_return_status) ;
1229
1230 hr_utility.trace('psp_create_eff_reports--> After xmlgen.update_er_person_xml');
1231
1232 If (l_return_status = fnd_api.g_ret_sts_error ) or (l_return_status = fnd_api.g_ret_sts_unexp_error) then
1233 psp_message_s.print_error(p_mode=>FND_FILE.log,
1234 p_print_header=>FND_API.G_TRUE);
1235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1236
1237 END IF;
1238
1239 END IF;
1240 -- hr_utility.trace_off;
1241
1242 EXCEPTION
1243 when FND_API.G_EXC_UNEXPECTED_ERROR then
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1245 when others then
1246 BEGIN
1247 -- hr_utility.trace('psp_create_eff_reports--> Exception '||sqlerrm);
1248 fnd_file.put_line(fnd_file.log,' Unhandled exception raised '||sqlerrm);
1249 fnd_message.set_name('PSP','PSP_ER_CREATE_UNEXPECTED_ERROR');
1250 l_err_mesg := substr(fnd_message.get,1,2000);
1251 l_sqlerrm := l_err_mesg || substr(sqlerrm,1,200);
1252 psp_general.add_report_error(l_request_id, 'E',null, l_retry_request_id, null, l_sqlerrm,l_return_status);
1253
1254 /* Below lines added for supercedence requirement
1255 */
1256 -- hr_utility.trace_off;
1257
1258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1259 END;
1260
1261
1262 END;
1263
1264 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
1265
1266 l_msg_str varchar2(240);
1267 --l_old_request_id number(15);
1268
1269 --l_retry_request_id number(15);
1270 l_sqlerrm varchar2(240);
1271
1272
1273 l_return_status varchar2(1);
1274
1275 cursor get_skipped_asg is
1276 select distinct pspt.skip_reason, pspt.person_id, paf.assignment_number
1277 from psp_selected_persons_t pspt,
1278 per_all_assignments_f paf
1279 where pspt.request_id = p_request_id
1280 and pspt.person_id between p_start_person and p_end_person
1281 and pspt.assignment_id = paf.assignment_id
1282 and pspt.skip_reason is not null
1283 and paf.effective_start_date = (select max(paf2.effective_start_date)
1284 from per_all_assignments_f paf2
1285 where paf2.assignment_id = paf.assignment_id);
1286
1287 skipped_asg_rec get_skipped_asg%rowtype;
1288
1289 BEGIN
1290
1291 /*
1292
1293 Find out if any persons in person_array do not have new effort reports created.
1294
1295
1296
1297 1. If previous effort report exists for the person, then appropriate error message.
1298
1299 2. if a person in range_cursor is missing entry in psp_eff_rep_details and prev_eff_report does not exist then
1300 say no active distributions for this person.
1301
1302 3. pending distribution Adjustment for employee -- how to determine
1303
1304
1305 4. Errors returned by cost share api.
1306
1307 */
1308
1309
1310
1311 /*
1312
1313 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;
1314
1315
1316
1317 -- select request_id into l_retry_request_id from pay_payroll_actions where payroll_action_id = p_pact_id;
1318
1319 if g_psp_request_id =p_request_id then
1320 l_retry_request_id :=NULL;
1321 else
1322 l_retry_request_id:=g_psp_request_id;
1323 end if;
1324
1325
1326 */
1327
1328 -- fnd_file.put_line(fnd_file.log,'orig is '||p_request_id||'--RETRY '||l_retry_request_id);
1329 if p_mode = 'PRE-POPULATE' then
1330
1331 /* A pending or Approved Effort Report already exists */
1332
1333 if p_match_level = 'EMP' then
1334 fnd_message.set_name('PSP', 'PSP_EFF_ALREADY_EXISTS');
1335 l_msg_str:= substr(fnd_message.get ,1,240);
1336
1337 update psp_selected_persons_t
1338 set skip_reason = 'ALREADY_EXISTS'
1339 where person_id between p_start_person and p_end_person
1340 and request_id = p_request_id
1341 and person_id in
1342 (select person_id from psp_eff_reports
1343 where status_code in ('N', 'A')
1344 and g_psp_effort_end >= start_date
1345 and g_psp_effort_start <= end_date );
1346
1347
1348 insert into psp_report_errors
1349 (error_sequence_id, request_id, message_level,
1350 source_id, error_message, retry_request_id, pdf_request_id,
1351 source_name, parent_source_id, parent_source_name)
1352 (select psp_report_errors_s.nextval , p_request_id,
1353 'W', pspt.person_id , l_msg_str , p_retry_request_id, null, NULL, NULL, null
1354 from psp_selected_persons_t pspt where pspt.person_id between p_start_person and p_end_person and
1355 pspt.request_id = p_request_id and skip_reason is not null
1356 AND rowid = (select min(rowid) from psp_selected_persons_t inner
1357 where inner.request_id = pspt.request_id
1358 AND inner.person_id = pspt.person_id ));
1359
1360 else
1361
1362 update psp_selected_persons_t
1363 set skip_reason = 'ALREADY_EXISTS'
1364 where person_id between p_start_person and p_end_person
1365 and request_id = p_request_id
1366 and (person_id, assignment_id) in
1367 (select er.person_id, erd.assignment_id
1368 from psp_eff_reports er, psp_eff_report_details erd
1369 where er.status_code in ('N', 'A')
1370 and er.effort_Report_id = erd.effort_report_id
1371 and g_psp_effort_end >= er.start_date
1372 and g_psp_effort_start <= er.end_date );
1373
1374 --- existing ER without asg_id summarization.
1375 update psp_selected_persons_t
1376 set skip_reason = 'ALREADY_EXISTS'
1377 where person_id between p_start_person and p_end_person
1378 and request_id = p_request_id
1379 and person_id in
1380 (select er.person_id
1381 from psp_eff_reports er, psp_eff_report_details erd
1382 where er.status_code in ('N', 'A')
1383 and er.effort_Report_id = erd.effort_report_id
1384 and g_psp_effort_end >= er.start_date
1385 and g_psp_effort_start <= er.end_date
1386 and erd.assignment_id is null);
1387
1388 update psp_selected_persons_t
1389 set skip_reason = 'OTHER_ASG_SKIPPED'
1390 where person_id between p_start_person and p_end_person
1391 and request_id = p_request_id
1392 and skip_reason is null
1393 and person_id in
1394 (select person_id
1395 from psp_selected_persons_t
1396 where person_id between p_start_person and p_end_person
1397 and request_id = p_request_id
1398 and skip_reason = 'ALREADY_EXISTS' );
1399
1400 open get_skipped_asg;
1401 loop
1402
1403 fetch get_skipped_asg into skipped_asg_rec;
1404 if get_skipped_asg%notfound then
1405 close get_skipped_asg;
1406 exit;
1407 end if;
1408
1409 if skipped_asg_rec.skip_reason = 'ALREADY_EXISTS' then
1410 fnd_message.set_name('PSP', 'PSP_ASG_EFF_ALREADY_EXISTS');
1411 fnd_message.set_token('ASG_NUMBER',skipped_asg_rec.assignment_number);
1412 l_msg_str:= substr(fnd_message.get ,1,240);
1413 else
1414 fnd_message.set_name('PSP', 'PSP_ASG_EFF_ALREADY_EXISTS2');
1415 fnd_message.set_token('ASG_NUMBER',skipped_asg_rec.assignment_number);
1416 l_msg_str:= substr(fnd_message.get ,1,240);
1417 end if;
1418
1419 insert into psp_report_errors
1420 (error_sequence_id, request_id, message_level,
1421 source_id, error_message, retry_request_id, pdf_request_id,
1422 source_name, parent_source_id, parent_source_name)
1423 (select psp_report_errors_s.nextval , p_request_id,
1424 'W', skipped_asg_rec.person_id , l_msg_str , p_retry_request_id, null, NULL, NULL, null
1425 from dual);
1426
1427 end loop;
1428
1429 end if;
1430
1431 else
1432 /* No distributions Found */
1433
1434
1435
1436 fnd_message.set_name('PSP', 'PSP_EFF_NO_DISTRIB');
1437 l_msg_str:= substr(fnd_message.get ,1,240);
1438
1439 insert into psp_report_errors
1440 (error_sequence_id, request_id, message_level,
1441 source_id, error_message, retry_request_id, pdf_request_id,
1442 source_name, parent_source_id, parent_source_name)
1443 select psp_report_errors_s.nextval, p_request_id,'W',
1444 pspt.person_id, l_msg_str, p_retry_request_id , null, NULL, NULL, NULL
1445 from psp_selected_persons_t pspt where pspt.person_id between p_start_person
1446 and p_end_person and
1447 pspt.request_id = p_request_id and
1448 pspt.person_id not in (select nvl(person_id,0) from psp_eff_reports where g_psp_effort_end >= start_date and
1449 g_psp_effort_start <= end_date and status_code in ('N', 'A'))
1450 and pspt.person_id not in (select nvl(source_id,0) from psp_report_errors where request_id = g_psp_request_id);
1451
1452
1453 fnd_message.set_name('PSP', 'PSP_EFF_PENDING_DIST_ADJ');
1454 l_msg_str:= substr(fnd_message.get ,1,200);
1455 insert into psp_report_errors
1456 (error_sequence_id, request_id, message_level,
1457 source_id, error_message, retry_request_id, pdf_request_id,
1458 source_name, parent_source_id, parent_source_name)
1459 select psp_report_errors_s.nextval, p_request_id,'W',
1460 pspt.person_id , l_msg_str, p_retry_request_id , null, NULL, NULL, NULL
1461 from psp_selected_persons_t pspt where pspt.person_id between p_start_person and p_end_person and
1462 pspt.request_id = p_request_id and
1463 exists (select nvl(person_id,0) from psp_adjustment_lines where effective_date between g_psp_effort_start
1464 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
1465 pspt.person_id not in (select nvl(person_id, 0) from psp_eff_reports where g_psp_effort_end>=start_date and
1466 g_psp_effort_start <= end_date);
1467 end if;
1468
1469 EXCEPTION
1470
1471 WHEN NO_DATA_FOUND THEN NULL;
1472
1473 WHEN OTHERS THEN
1474
1475 l_sqlerrm := 'Error inserting in psp_report_errors '|| substr(sqlerrm,1,200);
1476
1477 psp_general.add_report_error(p_request_id, 'E',null, p_retry_request_id, null, l_sqlerrm,l_return_status);
1478
1479
1480
1481 END;
1482
1483 PROCEDURE VALIDATE_PTAOE (p_start_person IN NUMBER,
1484 p_end_person IN NUMBER,
1485 p_request_id IN NUMBER,
1486 p_retry_request_id IN NUMBER,
1487 p_return_status OUT NOCOPY VARCHAR2) IS
1488 l_profile_bg_id number;
1489 l_person_id Number ;
1490 l_project_id Number ;
1491 l_task_id Number ;
1492 l_award_id Number ;
1493 l_exp_org_id Number ;
1494 l_project_name VARCHAR2(30);
1495 l_task_name VARCHAR2(300); -- Bug : 16591849
1496 l_award_name VARCHAR2(30);
1497 l_exp_org_name VARCHAR2(240);
1498 l_expenditure_type VARCHAR2(30);
1499 l_return_status CHAR(1);
1500 l_err_mesg varchar2(2000);
1501 l_error_type Number;
1502
1503 Cursor check_ptaoe is
1504 select 1, per.person_id, perd.project_id, NVL(perd.project_name,'NOTFOUND'), perd.task_id, NVL(perd.task_name,'NOTFOUND'),
1505 perd.award_id, NVL(perd.AWARD_SHORT_NAME,'NOTFOUND'), EXPENDITURE_ORGANIZATION_ID ,NVL(perd.exp_org_name,'NOTFOUND'),
1506 perd.expenditure_type
1507 from psp_eff_reports per ,
1508 psp_eff_report_details perd
1509 where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
1510 AND per.request_id = p_request_id
1511 AND person_id between p_start_person and p_end_person
1512 AND(( PROJECT_ID is not null AND PROJECT_NAME is NULL)
1513 OR ( TASK_ID is not null AND TASK_NAME is NULL)
1514 OR ( AWARD_ID is not null AND AWARD_SHORT_NAME is NULL)
1515 OR ( EXPENDITURE_ORGANIZATION_ID is not null AND EXP_ORG_NAME is NULL))
1516 UNION ALL
1517 select 2, per.person_id, perd.project_id, perd.project_name, perd.task_id, perd.task_name,
1518 perd.award_id, perd.AWARD_SHORT_NAME, EXPENDITURE_ORGANIZATION_ID ,perd.exp_org_name,
1519 perd.expenditure_type
1520 from psp_eff_reports per ,
1521 psp_eff_report_details perd
1522 where per.EFFORT_REPORT_ID = perd.EFFORT_REPORT_ID
1523 and per.request_id = p_request_id
1524 AND person_id between p_start_person and p_end_person
1525 AND PROJECT_ID is NULL
1526 AND TASK_ID is NULL
1527 AND AWARD_ID is NULL
1528 AND EXPENDITURE_ORGANIZATION_ID is NULL
1529 AND EXPENDITURE_TYPE is NULL
1530 AND gl_sum_criteria_segment_name IS NULL;
1531
1532 CURSOR layout_type_cur IS
1533 SELECT SUBSTR(report_template_code, 6, 3) layout_type
1534 FROM psp_report_templates_h prth
1535 WHERE prth.request_id = p_request_id;
1536
1537 l_layout_type CHAR(3);
1538 l_award_number VARCHAR2(240);
1539 l_project_number VARCHAR2(25);
1540 l_task_number VARCHAR2(300); -- Bug 16391366 (27/03/2013)
1541 l_check_project_number NUMBER;
1542
1543 CURSOR check_award_pi_cur IS
1544 SELECT DISTINCT per.person_id,
1545 perd.award_id,
1546 perd.award_number
1547 FROM psp_eff_reports per,
1548 psp_eff_report_details perd
1549 WHERE per.request_id = p_request_id
1550 AND per.effort_report_id = perd.effort_report_id
1551 AND per.person_id BETWEEN p_start_person AND p_end_person
1552 --AND perd.award_id IS NOT NULL Commented as part of UVA fix 4537063
1553 AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1554
1555 CURSOR check_pm_cur IS
1556 SELECT DISTINCT per.person_id,
1557 perd.project_id,
1558 perd.project_number
1559 FROM psp_eff_reports per,
1560 psp_eff_report_details perd
1561 WHERE per.request_id = p_request_id
1562 AND per.effort_report_id = perd.effort_report_id
1563 AND per.person_id BETWEEN p_start_person AND p_end_person
1564 AND perd.project_id IS NOT NULL
1565 AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1566
1567 CURSOR check_tm_cur IS
1568 SELECT DISTINCT per.person_id,
1569 perd.project_number,
1570 perd.task_id,
1571 perd.task_number
1572 FROM psp_eff_reports per,
1573 psp_eff_report_details perd
1574 WHERE per.request_id = p_request_id
1575 AND per.effort_report_id = perd.effort_report_id
1576 AND per.person_id BETWEEN p_start_person AND p_end_person
1577 AND perd.task_id IS NOT NULL
1578 AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1579
1580 CURSOR check_tm_cur1 IS
1581 SELECT DISTINCT per.person_id,
1582 ppa.segment1,
1583 perd.task_id,
1584 perd.task_number
1585 FROM psp_eff_reports per,
1586 psp_eff_report_details perd,
1587 pa_tasks_expend_v pt, -- Bug : 16391366 (20/03/2013)
1588 pa_projects_all ppa
1589 WHERE per.request_id = p_request_id
1590 AND per.effort_report_id = perd.effort_report_id
1591 AND pt.task_id = perd.task_id
1592 AND ppa.project_id = pt.project_id
1593 AND per.person_id BETWEEN p_start_person AND p_end_person
1594 AND perd.task_id IS NOT NULL
1595 AND (perd.investigator_person_id IS NULL OR perd.investigator_name IS NULL);
1596
1597 CURSOR check_project_number_cur IS
1598 SELECT COUNT(1)
1599 FROM psp_eff_reports per,
1600 psp_eff_report_details perd
1601 WHERE per.request_id = p_request_id
1602 AND per.person_id BETWEEN p_start_person AND p_end_person
1603 AND perd.task_id IS NOT NULL
1604 AND perd.project_id IS NULL
1605 AND ROWNUM = 1;
1606 BEGIN
1607
1608 l_profile_bg_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
1609 p_return_status := fnd_api.g_ret_sts_success;
1610
1611 OPEN layout_type_cur;
1612 FETCH layout_type_cur INTO l_layout_type;
1613 CLOSE layout_type_cur;
1614
1615 OPEN check_ptaoe;
1616 LOOP
1617 FETCH check_ptaoe into l_error_type, l_person_id,l_project_id, l_project_name,l_task_id, l_task_name,
1618 l_award_id, l_award_name, l_exp_org_id, l_exp_org_name, l_expenditure_type ;
1619 EXIT WHEN check_ptaoe%NOTFOUND;
1620 l_err_mesg := NULL;
1621
1622 IF l_error_type = 1 THEN
1623
1624 IF l_project_name = 'NOTFOUND' THEN
1625 fnd_message.set_name('PSP','PSP_ER_INVALID_PROJECT');
1626 fnd_message.set_token('PROJECT_ID',l_project_id);
1627 l_err_mesg := l_err_mesg || fnd_message.get;
1628 END IF;
1629
1630 IF l_task_name = 'NOTFOUND' THEN
1631 fnd_message.set_name('PSP','PSP_ER_INVALID_TASK');
1632 fnd_message.set_token('TASK_ID',l_task_id);
1633 l_err_mesg := l_err_mesg || fnd_message.get;
1634 END IF;
1635
1636 IF l_award_name = 'NOTFOUND' THEN
1637 fnd_message.set_name('PSP','PSP_ER_INVALID_AWARD');
1638 fnd_message.set_token('AWARD_ID',l_award_id);
1639 l_err_mesg := l_err_mesg || fnd_message.get;
1640 END IF;
1641
1642 IF l_exp_org_name = 'NOTFOUND' THEN
1643 fnd_message.set_name('PSP','PSP_ER_INVALID_EXP_ORG');
1644 fnd_message.set_token('EXP_ORG_ID',l_exp_org_id);
1645 l_err_mesg := l_err_mesg || fnd_message.get;
1646 END IF;
1647
1648 ELSE
1649
1650 fnd_message.set_name('PSP','PSP_ER_CI_NOT_FOUND');
1651 l_err_mesg := l_err_mesg || fnd_message.get;
1652 END IF;
1653
1654 IF l_err_mesg IS NOT NULL THEN
1655
1656 IF l_error_type = 1 THEN
1657 fnd_message.set_name('PSP','PSP_ER_CAN_NOT_DISPLAY_DATA');
1658 l_err_mesg := l_err_mesg || fnd_message.get;
1659 END IF;
1660 l_err_mesg := substr(l_err_mesg,1,2000);
1661 psp_general.add_report_error( p_request_id => p_request_id ,
1662 p_message_level => 'E',
1663 p_source_id => l_person_id,
1664 p_retry_request_id => p_retry_request_id,
1665 p_pdf_request_id => NULL,
1666 p_error_message => l_err_mesg,
1667 p_return_status => l_return_status);
1668 p_return_status := fnd_api.g_ret_sts_error;
1669 END IF;
1670
1671 END LOOP;
1672 CLOSE check_ptaoe;
1673
1674 IF (l_layout_type = 'PIV') THEN
1675 OPEN check_award_pi_cur;
1676 LOOP
1677 FETCH check_award_pi_cur INTO l_person_id, l_award_id, l_award_number;
1678 EXIT WHEN check_award_pi_cur%NOTFOUND;
1679
1680 fnd_message.set_name('PSP', 'PSP_ER_INVALID_PI');
1681 fnd_message.set_token('AWARD_NUMBER', l_award_number);
1682 l_err_mesg := fnd_message.get;
1683
1684 psp_general.add_report_error( p_request_id => p_request_id ,
1685 p_message_level => 'E',
1686 p_source_id => l_person_id,
1687 p_retry_request_id => p_retry_request_id,
1688 p_pdf_request_id => NULL,
1689 p_error_message => l_err_mesg,
1690 p_return_status => l_return_status);
1691 p_return_status := fnd_api.g_ret_sts_error;
1692 END LOOP;
1693 CLOSE check_award_pi_cur;
1694
1695 ELSIF (l_layout_type = 'PMG') THEN
1696 OPEN check_pm_cur;
1697 LOOP
1698 FETCH check_pm_cur INTO l_person_id, l_project_id, l_project_number;
1699 EXIT WHEN check_pm_cur%NOTFOUND;
1700
1701 fnd_message.set_name('PSP', 'PSP_ER_INVALID_PM');
1702 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
1703 l_err_mesg := fnd_message.get;
1704
1705 psp_general.add_report_error( p_request_id => p_request_id ,
1706 p_message_level => 'E',
1707 p_source_id => l_person_id,
1708 p_retry_request_id => p_retry_request_id,
1709 p_pdf_request_id => NULL,
1710 p_error_message => l_err_mesg,
1711 p_return_status => l_return_status);
1712 p_return_status := fnd_api.g_ret_sts_error;
1713 END LOOP;
1714 CLOSE check_pm_cur;
1715
1716 ELSIF (l_layout_type = 'TMG') THEN
1717 OPEN check_project_number_cur;
1718 FETCH check_project_number_cur INTO l_check_project_number;
1719 CLOSE check_project_number_cur;
1720
1721 IF (l_check_project_number = 0) THEN
1722 OPEN check_tm_cur;
1723 LOOP
1724 FETCH check_tm_cur INTO l_person_id, l_project_number, l_task_id, l_task_number;
1725 EXIT WHEN check_tm_cur%NOTFOUND;
1726
1727 fnd_message.set_name('PSP', 'PSP_ER_INVALID_TM');
1728 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
1729 fnd_message.set_token('TASK_NUMBER', l_task_number);
1730 l_err_mesg := fnd_message.get;
1731
1732 psp_general.add_report_error( p_request_id => p_request_id ,
1733 p_message_level => 'E',
1734 p_source_id => l_person_id,
1735 p_retry_request_id => p_retry_request_id,
1736 p_pdf_request_id => NULL,
1737 p_error_message => l_err_mesg,
1738 p_return_status => l_return_status);
1739 p_return_status := fnd_api.g_ret_sts_error;
1740 END LOOP;
1741 CLOSE check_tm_cur;
1742 ELSE
1743 OPEN check_tm_cur1;
1744 LOOP
1745 FETCH check_tm_cur1 INTO l_person_id, l_project_number, l_task_id, l_task_number;
1746 EXIT WHEN check_tm_cur1%NOTFOUND;
1747
1748 fnd_message.set_name('PSP', 'PSP_ER_INVALID_TM');
1749 fnd_message.set_token('PROJECT_NUMBER', l_project_number);
1750 fnd_message.set_token('TASK_NUMBER', l_task_number);
1751 l_err_mesg := fnd_message.get;
1752
1753 psp_general.add_report_error( p_request_id => p_request_id ,
1754 p_message_level => 'E',
1755 p_source_id => l_person_id,
1756 p_retry_request_id => p_retry_request_id,
1757 p_pdf_request_id => NULL,
1758 p_error_message => l_err_mesg,
1759 p_return_status => l_return_status);
1760 p_return_status := fnd_api.g_ret_sts_error;
1761 END LOOP;
1762 CLOSE check_tm_cur1;
1763 END IF;
1764 END IF;
1765
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 p_return_status := fnd_api.g_ret_sts_unexp_error;
1769 END VALIDATE_PTAOE;
1770
1771 END;