1 PACKAGE BODY PSP_TEMPLATE_SELECTION AS
2 /* $Header: PSPTPLSLB.pls 120.19 2006/08/11 06:43:21 dpaudel noship $*/
3
4
5
6 PROCEDURE insert_into_template_history(p_payroll_action_id IN NUMBER, p_request_id OUT NOCOPY NUMBER ) IS
7 PRAGMA AUTONOMOUS_TRANSACTION;
8
9 param_string PAY_PAYROLL_ACTIONS.LEGISLATIVE_PARAMETERS%TYPE;
10
11 l_psp_template_id number;
12 l_psp_effort_start date;
13 l_psp_effort_end date;
14 l_psp_sort_option1 varchar(150);
15 l_psp_sort_option2 varchar2(150);
16 l_psp_sort_option3 varchar2(150);
17 l_psp_sort_option4 varchar2(150);
18 l_psp_order_by1 varchar2(150);
19 l_psp_order_by2 varchar2(150);
20 l_psp_order_by3 varchar2(150);
21 l_psp_order_by4 varchar2(150);
22 l_psp_report_layout varchar2(150);
23
24 l_sqlerrm varchar2(240);
25 l_return_status varchar2(1);
26
27 BEGIN
28
29
30 SELECT request_id, legislative_parameters into p_request_id, param_string from pay_payroll_actions where payroll_action_id = p_payroll_action_id;
31
32 l_psp_template_id := get_parameter_value('TEMPLATE_ID', param_string);
33
34
35 --fnd_file.put_line(fnd_file.log,'TEMPLATE_ID ===='||l_psp_template_id);
36 --fnd_file.put_line(fnd_file.log,'Return String '||get_parameter_value('START_DATE', param_string));
37
38
39 l_psp_effort_start:= trunc(fnd_date.canonical_to_date(get_parameter_value('START_DATE', param_string)));
40 --fnd_file.put_line(fnd_file.log,'start_date ===='||l_psp_effort_start);
41
42 l_psp_effort_end:= trunc(fnd_date.canonical_to_date(get_parameter_value('END_DATE', param_string)));
43 --fnd_file.put_line(fnd_file.log,'end date ===='||l_psp_effort_end);
44
45 l_psp_report_layout := get_parameter_value('REPORT_LAYOUT', param_string);
46 l_psp_sort_option1 := get_parameter_value('FIRST_SORT', param_string);
47 l_psp_order_by1 := get_parameter_value('FIRST_ORDER' , param_string);
48 l_psp_sort_option2 := get_parameter_value('SECOND_SORT', param_string);
49 l_psp_order_by2 := get_parameter_value('SECOND_ORDER' , param_string);
50 l_psp_sort_option3 := get_parameter_value('THIRD_SORT', param_string);
51 l_psp_order_by3 := get_parameter_value('THIRD_ORDER' , param_string);
52 l_psp_sort_option4 := get_parameter_value('FOURTH_SORT', param_string);
53 l_psp_order_by4 := get_parameter_value('FOURTH_ORDER' , param_string);
54
55
56 -- fnd_file.put_line(fnd_file.log,'sort option 1 ===='||l_psp_sort_option1);
57 -- fnd_file.put_line(fnd_file.log,'ORDER BY ===='||l_psp_order_by1);
58 -- fnd_file.put_line(fnd_file.log,' sort option 2 ===='||l_psp_sort_option2);
59 -- fnd_file.put_line(fnd_file.log,'order_by_2 ===='||l_psp_order_by2);
60 -- fnd_file.put_line(fnd_file.log,'layout ===='||l_psp_report_layout);
61
62
63 /* insert record in psp_report_templates_h */
64
65 insert into psp_report_templates_h(request_id, template_id, template_name,
66 business_group_id, set_of_books_id, report_type, period_frequency_id,
67 report_template_code, display_all_emp_distrib_flag,
68 manual_entry_override_flag, approval_type, custom_approval_code,
69 sup_levels, preview_effort_report_flag, notification_reminder_in_days, parameter_name_1, parameter_name_2, parameter_name_3, parameter_name_4, parameter_name_5,
70 parameter_name_6, parameter_name_7, parameter_name_8, parameter_name_9, parameter_name_10,
71 parameter_name_11, parameter_name_12, parameter_name_13, parameter_name_14, parameter_name_15,
72 parameter_name_16, parameter_name_17, parameter_name_18, parameter_name_19,
73 parameter_name_20,parameter_value_1, parameter_value_2, parameter_value_3, parameter_value_4,
74 parameter_value_5, parameter_value_6, parameter_value_7, parameter_value_8,
75 parameter_value_9, parameter_value_10, parameter_value_11,
76 parameter_value_12, parameter_value_13, parameter_value_14, parameter_value_15,
77 parameter_value_16, parameter_value_17, parameter_value_18, parameter_value_19, parameter_value_20, submission_date,
78 initiator_person_id, initiator_file_id, initiator_accept_flag,
79 final_recipients_file_id, sprcd_tolerance_amt, sprcd_tolerance_percent, description, legislation_code,
80 payroll_action_id, last_update_date, last_updated_by, last_update_login,
81 created_by, creation_date,hundred_pcent_eff_at_per_asg,selection_match_level) ( select p_request_id, template_id, template_name,
82 business_group_id, set_of_books_id , report_type, period_frequency_id,
83 report_template_code, display_all_emp_distrib_flag,
84 manual_entry_override_flag, approval_type,
85 custom_approval_code, sup_levels, preview_effort_report_flag, notification_reminder_in_days,
86 'TEMPLATE_ID', 'EFFORT_START', 'EFFORT_END',
87 'REPORT_LAYOUT', 'SORT_OPTION1','ORDER_BY1', 'SORT_OPTION2', 'ORDER_BY' , 'SORT_OPTION3', 'ORDER_BY3', 'SORT_OPTION4',
88 'ORDER_BY4', NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, l_psp_template_id, l_psp_effort_start, l_psp_effort_end, l_psp_report_layout, l_psp_sort_option1, l_psp_order_by1,
89 l_psp_sort_option2, l_psp_order_by2, l_psp_sort_option3, l_psp_order_by3, l_psp_sort_option4, l_psp_order_by4, NULL, NULL, NULL
90 , NULL, NULL, NULL, NULL, NULL, sysdate, fnd_global.employee_id, NULL,
91 NULL, NULL, sprcd_tolerance_amt, sprcd_tolerance_percent, description, legislation_code,
92 p_payroll_action_id, last_update_date, last_updated_by, last_update_login, created_by, creation_date, hundred_pcent_eff_at_per_asg,selection_match_level from psp_report_templates where template_id = l_psp_template_id);
93
94
95 insert into psp_report_template_details_h (request_id, template_detail_id, criteria_lookup_type,
96 criteria_lookup_code, include_exclude_flag, criteria_value1, criteria_value2, criteria_value3,
97 last_update_date, last_updated_by, last_update_login, created_by, creation_date) (select
98 p_request_id, template_detail_id,
99 criteria_lookup_type, criteria_lookup_code, include_exclude_flag,
100 criteria_value1, criteria_value2, criteria_value3 , last_update_date,
101 last_updated_by, last_update_login, created_by, creation_date from
102 psp_report_template_details where template_id = l_psp_template_id);
103
104 COMMIT;
105
106 EXCEPTION WHEN OTHERS THEN
107
108 l_sqlerrm := 'Error inserting into psp_report_templates_history '|| substr(sqlerrm,1,180);
109
110 /* Removed payroll_action_id to make it independent of payroll_action_id */
111 -- psp_general.add_report_error(p_request_id, 'E',null, l_sqlerrm, p_payroll_action_id, l_return_status);
112 psp_general.add_report_error
113 (p_request_id => p_request_id,
114 p_message_level => 'E',
115 p_source_id => NULL,
116 p_error_message => l_sqlerrm,
117 -- p_payroll_action_id => pactid,
118 p_return_status => l_return_status);
119
120 fnd_file.put_line(fnd_file.log, 'Insert into Template History failed '||sqlerrm);
121 raise fnd_api.g_exc_unexpected_error;
122
123 END;
124
125
126
127 PROCEDURE range_code(pactid IN NUMBER, sqlstr out nocopy varchar2) IS
128
129 errBuf varchar2(240);
130
131 retCode varchar(1);
132
133 l_request_id number;
134
135 l_cnt number:=0;
136
137 l_msg_str varchar2(240);
138 l_migration_status BOOLEAN;
139
140 l_proj_segment varchar2(30);
141 l_tsk_segment varchar2(30);
142 l_awd_sgement varchar2(30);
143 l_exp_org_segment varchar2(30);
144 l_exp_type_segment varchar2(30);
145 l_profile_bg_id Number;
146 l_return_status varchar2(30);
147
148
149 BEGIN
150
151 --fnd_file.put_line(fnd_file.log,'Before Insert in template history ');
152 --hr_utility.trace_on('Y','ORACLE');
153
154
155 insert_into_template_history(pactid, l_request_id );
156 l_profile_bg_id := FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
157 IF PSP_GENERAL.GET_CONFIGURATION_OPTION_VALUE(l_profile_bg_id,'PSP_USE_GL_PTAOE_MAPPING') = 'Y' THEN
158
159 PSP_GENERAL.GET_GL_PTAOE_MAPPING(p_business_group_id => l_profile_bg_id,
160 p_proj_segment => l_proj_segment,
161 p_tsk_segment => l_tsk_segment,
162 p_awd_sgement => l_awd_sgement,
163 p_exp_org_segment=> l_exp_org_segment,
164 p_exp_type_segment => l_exp_type_segment);
165 IF (l_proj_segment is null) OR (l_tsk_segment is null) OR (l_awd_sgement is null) OR
166 (l_exp_org_segment is null) OR (l_exp_type_segment is null) THEN
167
168 fnd_message.set_name('PSP', 'PSP_GL_PTAOE_NOT_MAPPED');
169 l_msg_str:=substr(fnd_message.get, 1,240);
170 /* Removed payroll_action_id to make it independent of payroll_action_id */
171 psp_general.add_report_error
172 (p_request_id => l_request_id,
173 p_message_level => 'E',
174 p_source_id => NULL,
175 p_error_message => l_msg_str,
176 -- p_payroll_action_id => pactid,
177 p_return_status => l_return_status);
178 raise fnd_api.g_exc_unexpected_error;
179 END IF;
180 END IF;
181
182
183 l_migration_status:= psp_general.is_effort_report_migrated;
184
185 IF l_migration_status then
186
187 --fnd_file.put_line(fnd_file.log,'==== After Insert in template history ');
188
189 get_final_selection_list(errBuf, retCode, l_request_id, TRUE);
190
191 --fnd_file.put_line(fnd_file.log,'==== After getting sleection list ');
192
193 SELECT nvl(count( person_id),0) into l_cnt from psp_selected_persons_t where
194 request_id = l_request_id;
195
196 --fnd_file.put_line(fnd_file.log,'==== count is '|| l_cnt);
197
198 IF l_cnt=0 then
199
200 fnd_message.set_name('PSP', 'PSP_EFF_NO_EMP_FOUND');
201 l_msg_str:= substr(fnd_message.get ,1,240);
202
203 /* Removed payroll_action_id to make it independent of payroll_action_id */
204 INSERT INTO psp_report_errors
205 (error_sequence_id, request_id, message_level,
206 source_id, error_message, retry_request_id,
207 pdf_request_id, source_name, parent_source_id,
208 parent_source_name)
209 VALUES (psp_report_errors_s.NEXTVAL, l_request_id, 'W',
210 NULL, l_msg_str, NULL,
211 NULL, NULL, NULL,
212 NULL);
213
214
215 ELSE
216
217 -- delete duplicates
218 delete from psp_Selected_persons_t outer where request_id = l_request_id and
219 (person_id, nvl( assignment_id, -999) ) in (select
220 person_id, nvl( assignment_id, -999) from psp_Selected_persons_t inner where inner.person_id=outer.person_id and inner.request_id =outer.request_id and inner.rowid > outer.rowid);
221
222 END IF;
223
224
225 -- fnd_file.put_line(fnd_file.log,'==== cnt sleection '||l_cnt);
226
227 sqlstr := 'select distinct person_id from psp_selected_persons_t pspt,
228 pay_payroll_actions pact
229 where pact.payroll_action_id = :payroll_action_id and
230 pspt.request_id= pact.request_id ORDER BY pspt.person_id';
231
232
233 ELSE
234
235 fnd_message.set_name('PSP', 'PSP_COMP_EFF_MIG');
236
237 l_msg_str:=substr(fnd_message.get, 1,240);
238 /* Removed payroll_action_id to make it independent of payroll_action_id */
239 INSERT INTO psp_report_errors
240 (error_sequence_id, request_id, message_level,
241 source_id, error_message, retry_request_id,
242 pdf_request_id, source_name, parent_source_id,
243 parent_source_name)
244 VALUES (psp_report_errors_s.NEXTVAL, l_request_id, 'E',
245 NULL, l_msg_str, NULL,
246 NULL, NULL, NULL,
247 NULL);
248
249
250 fnd_message.set_name('PSP', 'PSP_COMP_EFF_MIG');
251 fnd_msg_pub.add;
252
253 raise fnd_api.g_exc_unexpected_error;
254
255 END IF;
256
257
258 -- hr_utility.trace_off;
259
260 EXCEPTION WHEN OTHERS THEN
261
262 psp_message_s.print_error(p_mode=>FND_FILE.log,
263
264 p_print_header=>FND_API.G_TRUE);
265
266 sqlstr:= NULL;
267 commit;
268 raise fnd_api.g_exc_unexpected_error;
269 END;
270
271
272
273 PROCEDURE GET_FINAL_SELECTION_LIST(errBuf OUT NOCOPY VARCHAR2,
274 retCode OUT NOCOPY VARCHAR2,
275 p_request_id IN NUMBER,
276 p_person_asg_flag IN BOOLEAN
277 )
278 AS
279
280 PRAGMA AUTONOMOUS_TRANSACTION;
281
282 /* logic needs to be added for case when
283
284 user has not selected any selection criteria
285
286
287 */
288
289 CURSOR get_template_selection
290 IS
291 SELECT template_id, parameter_value_2, parameter_value_3, business_group_id, set_of_books_id, selection_match_level
292 from psp_report_templates_h
293 where request_id = p_request_id;
294
295 l_template_id NUMBER;
296 l_count NUMBER;
297 i NUMBER;
298 l_effort_start DATE;
299 l_effort_end DATE;
300 l_business_group_id NUMBER;
301 l_set_of_books_id NUMBER;
302 l_template_selection varchar2(3);
303 ---l_count_x integer;
304 BEGIN
305
306
307 OPEN get_template_selection;
308
309 FETCH get_template_selection into l_template_id, l_effort_start, l_effort_end, l_business_group_id, l_set_of_books_id, l_template_selection;
310
311 CLOSE get_template_selection;
312
313
314 IF (l_template_selection = 'EMP') THEN
315 get_lowest_cardinality(p_request_id , l_effort_start, l_effort_end, l_business_group_id, l_set_of_books_id);
316 ---fnd_file.put_line(fnd_file.log,' after get lowest cardinality ');
317
318
319 hr_utility.trace( 'get_template_selection-> After get_lowest_cardinality' );
320
321 prepare_initial_person_list(p_request_id , l_effort_start, l_effort_end, l_business_group_id, l_set_of_books_id);
322 ---select count(*) into l_count_x from psp_selected_persons_t where request_id
323 ---= p_request_id;
324 ---fnd_file.put_line(fnd_file.log,' after initial person list ');
325
326 FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');
327
328 hr_utility.trace( 'get_template_selection-> After prepare_initial_person_list ' );
329 If g_lookup_code <> 'ALL' then
330
331 prune_initial_person_list(p_request_id , l_effort_start, l_effort_end, l_business_group_id, l_set_of_books_id);
332 --fnd_file.put_line(fnd_file.log,' after prune person list ');
333 hr_utility.trace( 'get_template_selection-> After prune initial_person_list ' );
334 END IF;
335
336
337 apply_exclusion_criteria(p_request_id , l_effort_start, l_effort_end, l_business_group_id, l_set_of_books_id);
338 --fnd_file.put_line(fnd_file.log,' after apply excl criteria ');
339 hr_utility.trace( 'get_template_selection-> After apply exclusion criteria ' );
340
341 END IF;
342
343 IF (l_template_selection = 'ASG') THEN
344 get_asg_lowest_cardinality (p_request_id => p_request_id,
345 p_effort_start => l_effort_start,
346 p_effort_end => l_effort_end,
347 p_business_group_id => l_business_group_id,
348 p_set_of_books_id => l_set_of_books_id);
349 hr_utility.trace('get_template_selection-> After get_asg_lowest_cardinality');
350
351 prepare_initial_asg_list (p_request_id => p_request_id,
352 p_effort_start => l_effort_start,
353 p_effort_end => l_effort_end,
354 p_business_group_id => l_business_group_id,
355 p_set_of_books_id => l_set_of_books_id);
356 hr_utility.trace('get_template_selection-> After prepare_initial_asg_list');
357
358 FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');
359
360 IF g_lookup_code <> 'ALL' THEN
361 prune_initial_asg_list (p_request_id => p_request_id,
362 p_effort_start => l_effort_start,
363 p_effort_end => l_effort_end,
364 p_business_group_id => l_business_group_id,
365 p_set_of_books_id => l_set_of_books_id);
366 hr_utility.trace('get_template_selection-> After prune initial_asg_list');
367 END IF;
368
369 apply_asg_exclusion_criteria (p_request_id => p_request_id,
370 p_effort_start => l_effort_start,
371 p_effort_end => l_effort_end,
372 p_business_group_id => l_business_group_id,
373 p_set_of_books_id => l_set_of_books_id);
374 hr_utility.trace('get_template_selection-> After apply asg exclusion criteria');
375 END IF;
376
377 apply_ff_formula_exclusion(p_request_id, l_effort_start, l_effort_end);
378 -- fnd_file.put_line(fnd_file.log,' after ff exclusion ');
379 hr_utility.trace( 'get_template_selection-> After apply_ff_formula_exclusion ' );
380
381
382 --fnd_file.put_line(fnd_file.log,' before count ');
383
384 select nvl(count(person_id),0) into l_count from psp_Selected_persons_t where request_id=p_request_id;
385 --fnd_file.put_line(fnd_file.log,' after count ' || l_count);
386 hr_utility.trace( 'get_template_selection-> After getting count= '||l_count );
387
388 COMMIT;
389
390 If l_count > 0 then
391
392
393 FND_STATS.GATHER_TABLE_STATS(ownname=>'PSP', tabname=>'PSP_SELECTED_PERSONS_T');
394 -- fnd_file.put_line(fnd_file.log,' after gather stats ');
395
396 end if;
397 EXCEPTION
398 WHEN OTHERS THEN
399 BEGIN
400 fnd_file.put_line(fnd_file.log,' after gather error '||sqlerrm);
401 hr_utility.trace( 'get_template_selection->'||sqlerrm );
402
403 END;
404
405
406 END;
407
408
409 PROCEDURE get_lowest_cardinality(p_request_id IN NUMBER, p_effort_start IN
410 DATE, p_effort_end IN DATE, p_business_group_id IN NUMBER, p_set_of_books_id IN NUMBER) IS
411
412 i number;
413
414 l_criteria_value1 varchar2(30);
415
416 l_criteria_value2 varchar2(30);
417
418 l_criteria_value3 varchar2(30);
419
420 l_dyn_criteria varchar2(30);
421
422 l_atleast_one_criteria varchar2(1);
423
424
425 l_sql_string varchar2(1000);
426
427 CURSOR get_lowest_cardinality_csr is select lookup_code from
428 psp_selection_cardinality_gt where total_count > 0 ORDER BY total_count asc;
429
430
431 CURSOR get_zero_cardinality_csr is select lookup_code from
432 psp_selection_cardinality_gt where total_count=0;
433
434
435 CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER)
436 IS
437 Select distinct (criteria_lookup_code) from
438 psp_report_template_details_h where request_id = p_request_id and
439 include_exclude_flag='I' and criteria_lookup_type='PSP_SELECTION_CRITERIA';
440
441
442 /* The below cursors would only be used only when no statis selection criteria have been chosen */
443
444 CURSOR PPG_CURSOR IS
445 select criteria_value1, criteria_value2 from
446 psp_report_template_details_h where request_id = p_request_id and
447 include_exclude_flag='I' and
448 criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG';
449
450
451 CURSOR GLA_CURSOR IS
452 select criteria_value1 , criteria_value2, criteria_value3 from
453 psp_report_template_details_h where request_id = p_request_id and
454 include_exclude_flag='I' and
455 criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA';
456
457
458 BEGIN
459
460 OPEN get_selection_cardinality_csr(p_request_id);
461 FETCH get_selection_cardinality_csr BULK COLLECT into template_rec.array_sel_criteria;
462
463 CLOSE get_selection_cardinality_csr;
464
465
466 FOR i in 1.. template_rec.array_sel_criteria.count
467
468
469 LOOP
470
471 IF template_rec.array_sel_criteria(i) = 'PTY' THEN
472
473 insert into psp_selection_cardinality_gt(lookup_code, total_count) (
474 select 'PTY', count(distinct ppf.person_id) from per_people_f ppf, per_assignments_f paf where
475 person_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
476 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='PTY'
477 and include_exclude_flag='I'
478 and request_id = p_request_id )
479 AND paf.person_id = ppf.person_id
480 AND paf.assignment_type = 'E'
481 AND paf.effective_start_date <= p_effort_end
482 AND paf.effective_end_date >= p_effort_start
483 and
484 ppf.effective_start_date <= p_effort_end and
485 ppf.effective_end_date >= p_effort_start) ;
486
487
488 ELSIF template_rec.array_sel_criteria(i) ='EMP' THEN
489
490 insert into psp_selection_cardinality_gt(lookup_code, total_count) (
491 select 'EMP', count(distinct ppf.person_id) from per_all_people_f ppf, per_assignments_f paf where
492 ppf.person_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
493 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='EMP' and
494 include_exclude_flag='I'
495 and request_id = p_request_id )
496 AND paf.person_id = ppf.person_id
497 AND paf.assignment_type = 'E'
498 AND paf.effective_start_date <= p_effort_end
499 AND paf.effective_end_date >= p_effort_start
500 and
501 ppf.effective_start_date <= p_effort_end and
502 ppf.effective_end_date >= p_effort_start) ;
503
504 ELSIF template_rec.array_sel_criteria(i) ='SUP' THEN
505
506 insert into psp_selection_cardinality_gt(lookup_code, total_count) (
507 select 'SUP', count(distinct person_id) from per_all_assignments_f paf where
508 supervisor_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
509 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='SUP'
510 and request_id = p_request_id
511 and include_exclude_flag='I'
512 )
513 AND paf.assignment_type = 'E'
514 and
515 effective_start_date <= p_effort_end and
516 effective_end_date >= p_effort_start) ;
517
518 ELSIF template_rec.array_sel_criteria(i) ='AWD' THEN
519
520 --- replaced non-performant insert with this for 4429787
521 insert into psp_selection_cardinality_gt(lookup_code, total_count)(
522 SELECT 'AWD', COUNT(DISTINCT psl.person_id)
523 FROM psp_summary_lines psl,
524 psp_report_template_details_h prtd ,
525 per_time_periods ptp
526 WHERE
527 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
528 prtd.criteria_lookup_code='AWD' AND
529 prtd.include_exclude_flag='I' AND
530 prtd.request_id =p_request_id AND
531 psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
532 psl.business_group_id = p_business_group_id AND
533 psl.set_of_books_id = p_set_of_books_id AND
534 psl.status_code= 'A' AND
535 ptp.time_period_id = psl.time_period_id AND
536 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
537 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
538 WHERE pdnh.summary_line_id = psl.summary_line_id
539 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
540 AND pdnh.reversal_entry_flag IS NULL
541 AND pdnh.adjustment_batch_name IS NULL )
542 OR EXISTS
543 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
544 WHERE ppg.summary_line_id = psl.summary_line_id
545 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
546 AND ppg.adjustment_batch_name IS NULL AND
547 ppg.reversal_entry_flag IS NULL)
548 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
549 WHERE palh.summary_line_id = psl.summary_line_id
550 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
551 AND palh.adjustment_batch_name IS NULL
552 AND palh.reversal_entry_flag IS NULL AND
553 NVL(palh.original_line_flag, 'N') ='N')));
554
555 ELSIF template_rec.array_sel_criteria(i)='ATY' THEN
556 --- replaced non-performant insert with this for 4429787
557
558
559 INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
560 SELECT 'ATY', COUNT(DISTINCT psl.person_id)
561 FROM psp_summary_lines psl,
562 psp_report_template_details_h prtd ,
563 gms_awards_all gaa,
564 per_time_periods ptp
565 WHERE psl.award_id = gaa.award_id AND
566 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
567 prtd.criteria_lookup_code='ATY' AND
568 prtd.include_exclude_flag='I' AND
569 prtd.request_id = p_request_id AND
570 gaa.type=prtd.criteria_value1 AND
571 psl.business_group_id = p_business_group_id AND
572 psl.set_of_books_id = p_set_of_books_id AND
573 psl.status_code= 'A' AND
574 ptp.time_period_id = psl.time_period_id AND
575 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
576 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
577 WHERE pdnh.summary_line_id = psl.summary_line_id
578 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
579 AND pdnh.reversal_entry_flag IS NULL
580 AND pdnh.adjustment_batch_name IS NULL )
581 OR EXISTS
582 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
583 WHERE ppg.summary_line_id = psl.summary_line_id
584 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
585 AND ppg.adjustment_batch_name IS NULL AND
586 ppg.reversal_entry_flag IS NULL)
587 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
588 WHERE palh.summary_line_id = psl.summary_line_id
589 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
590 AND palh.adjustment_batch_name IS NULL
591 AND palh.reversal_entry_flag IS NULL AND
592 NVL(palh.original_line_flag, 'N') ='N')));
593
594 ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
595 --- replaced non-performant insert with this for 4429787
596 INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)(
597 SELECT 'PRT', COUNT(DISTINCT psl.person_id)
598 FROM psp_summary_lines psl,
599 psp_report_template_details_h prtd ,
600 pa_projects_all ppa ,
601 per_time_periods ptp
602 WHERE psl.project_id = ppa.project_id AND
603 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
604 prtd.criteria_lookup_code='PRT' AND
605 prtd.include_exclude_flag='I' AND
606 prtd.request_id =p_request_id AND
607 ppa.project_type=prtd.criteria_value1 AND
608 psl.business_group_id = p_business_group_id AND
609 psl.set_of_books_id = p_set_of_books_id AND
610 psl.status_code= 'A' AND
611 ptp.time_period_id = psl.time_period_id AND
612 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
613 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
614 WHERE pdnh.summary_line_id = psl.summary_line_id
615 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
616 AND pdnh.reversal_entry_flag IS NULL
617 AND pdnh.adjustment_batch_name IS NULL )
618 OR EXISTS
619 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
620 WHERE ppg.summary_line_id = psl.summary_line_id
621 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
622 AND ppg.adjustment_batch_name IS NULL AND
623 ppg.reversal_entry_flag IS NULL)
624 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
625 WHERE palh.summary_line_id = psl.summary_line_id
626 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
627 AND palh.adjustment_batch_name IS NULL
628 AND palh.reversal_entry_flag IS NULL AND
629 NVL(palh.original_line_flag, 'N') ='N')));
630
631 ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
632 --- replaced non-performant insert with this for 4429787
633 insert into psp_selection_cardinality_gt(lookup_code, total_count)(
634 SELECT 'PRJ', COUNT(DISTINCT psl.person_id)
635 FROM psp_summary_lines psl,
636 psp_report_template_details_h prtd ,
637 per_time_periods ptp
638 WHERE
639 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
640 prtd.criteria_lookup_code='PRJ' AND
641 prtd.include_exclude_flag='I' AND
642 prtd.request_id =p_request_id AND
643 psl.business_group_id = p_business_group_id AND
644 psl.set_of_books_id = p_set_of_books_id AND
645 psl.status_code= 'A' AND
646 ptp.time_period_id = psl.time_period_id AND
647 psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
648 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
649 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
650 WHERE pdnh.summary_line_id = psl.summary_line_id
651 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
652 AND pdnh.reversal_entry_flag IS NULL
653 AND pdnh.adjustment_batch_name IS NULL )
654 OR EXISTS
655 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
656 WHERE ppg.summary_line_id = psl.summary_line_id
657 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
658 AND ppg.adjustment_batch_name IS NULL AND
659 ppg.reversal_entry_flag IS NULL)
660 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
661 WHERE palh.summary_line_id = psl.summary_line_id
662 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
663 AND palh.adjustment_batch_name IS NULL
664 AND palh.reversal_entry_flag IS NULL AND
665 NVL(palh.original_line_flag, 'N') ='N')));
666
667 ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
668
669 insert into psp_selection_cardinality_gt(lookup_code, total_count)(
670 select 'PAY', count(distinct person_id) from per_assignments_f paf where
671 payroll_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
672 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='PAY'
673 and include_exclude_flag='I' and request_id = p_request_id
674 )
675 AND paf.assignment_type = 'E'
676 and
677 effective_start_date <= p_effort_end and
678 effective_end_date >= p_effort_start) ;
679
680 ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
681
682 insert into psp_selection_cardinality_gt(lookup_code, total_count)(
683 select 'LOC', count(distinct person_id) from per_assignments_f paf where
684 location_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
685 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='LOC'
686 and include_exclude_flag='I' and request_id = p_request_id
687 )
688 AND paf.assignment_type = 'E'
689 and
690 effective_start_date <= p_effort_end and
691 effective_end_date >= p_effort_start) ;
692
693 ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
694
695 insert into psp_selection_cardinality_gt(lookup_code, total_count)(
696 select 'ORG', count(distinct person_id) from per_assignments_f paf where
697 organization_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd
698 where
699 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='ORG'
700 and include_exclude_flag='I' and request_id = p_request_id
701 )
702 AND paf.assignment_type = 'E'
703 and effective_start_date <= p_effort_end and
704 effective_end_date >= p_effort_start);
705
706 ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
707
708 insert into psp_selection_cardinality_gt(lookup_code, total_count)(
709 select 'JOB', count(distinct person_id) from per_assignments_f paf where
710 job_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
711 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='JOB'
712 and include_exclude_flag='I' and request_id=p_request_id
713 )
714 AND paf.assignment_type = 'E'
715 and
716 effective_start_date <= p_effort_end and
717 effective_end_date >= p_effort_start) ;
718
719 ELSIF template_rec.array_sel_criteria(i)='POS' THEN
720
721 insert into psp_selection_cardinality_gt(lookup_code, total_count) (
722 select 'POS', count(distinct person_id) from per_assignments_f paf where
723 position_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h prtd where
724 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='POS'
725 and include_exclude_flag='I' and request_id = p_request_id
726 )
727 AND paf.assignment_type = 'E'
728 and
729 effective_start_date <= p_effort_end and
730 effective_end_date >= p_effort_start) ;
731
732 ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
733
734 insert into psp_selection_cardinality_gt(lookup_code, total_count) (
735 select 'ASS', count(distinct person_id) from per_assignments_f paf where
736 assignment_status_type_id in (select TO_NUMBER(criteria_value1) from psp_report_template_details_h
737 prtd where
738 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='ASS'
739 and include_exclude_flag='I' and request_id = p_request_id
740 )
741 AND paf.assignment_type = 'E'
742 and
743 effective_start_date <= p_effort_end and
744 effective_end_date >= p_effort_start) ;
745
746 ELSIF template_rec.array_sel_criteria(i)='CST' THEN
747
748 insert into psp_selection_cardinality_gt(lookup_code, total_count) (
749 select 'CST', count(distinct paf.person_id) from per_assignments_f paf
750 , pay_payrolls_f ppf where
751 ppf.payroll_id = paf.payroll_id and
752 ppf.consolidation_set_id in
753 (select TO_NUMBER(criteria_value1) from psp_report_template_details_h
754 prtd where
755 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='CST'
756 and include_exclude_flag='I' and request_id = p_request_id
757 )
758 AND paf.assignment_type = 'E'
759 and
760 ppf.effective_start_date <= p_effort_end
761 and ppf.effective_end_date >= p_effort_start
762 and paf.effective_start_date <= p_effort_end and
763 paf.effective_end_date >= p_effort_start);
764
765
766 ELSIF template_rec.array_sel_criteria(i) = 'AST' THEN
767
768 insert into psp_selection_cardinality_gt(lookup_code, total_count) (
769 select 'AST', count(distinct paf.person_id) from per_all_assignments_f paf, hr_assignment_sets has ,
770 hr_assignment_Set_amendments hasa
771 where
772 has.assignment_set_id in (
773 select TO_NUMBER(criteria_value1) from psp_report_template_details_h
774 prtd where
775 criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='AST'
776 and include_exclude_flag='I' and request_id =p_request_id
777 )
778 and
779 ( ( paf.payroll_id = has.payroll_id and
780 paf.effective_start_date <= p_effort_end and
781 paf.effective_end_date >= p_effort_start
782 AND paf.assignment_type = 'E'
783 and has.assignment_set_id = hasa.assignment_Set_id
784 )
785 OR
786 (
787 paf.assignment_id = hasa.assignment_id and
788 paf.effective_start_date <= p_effort_end and
789 paf.effective_end_date >= p_effort_start
790 AND paf.assignment_type = 'E'
791 and hasa.assignment_Set_id=has.assignment_Set_id and include_or_exclude ='I') )
792 and not exists (select assignment_id from hr_assignment_Set_amendments hasa where
793 hasa.assignment_id = paf.assignment_id and hasa.include_or_exclude ='E'
794 AND paf.assignment_type = 'E'
795 and paf.effective_start_date <= p_effort_end and
796 paf.effective_end_date >= p_effort_start
797 ));
798
799
800
801
802
803 ELSIF template_rec.array_sel_criteria(i)='PPG' THEN
804
805 l_dyn_criteria:='PPG';
806 -- fnd_file.put_line(fnd_file.log, ' dyn_criteria is ' ||l_dyn_criteria);
807
808 ELSIF template_rec.array_sel_criteria(i)='GLA' THEN
809
810 l_dyn_criteria:='GLA';
811 -- fnd_file.put_line(fnd_file.log, ' dyn_criteria is ' ||l_dyn_criteria);
812
813 END IF;
814
815 END LOOP;
816
817
818
819 /* Next find the selection criteria with lowest cardinality. Use it to prepare the initial list */
820
821 OPEN get_lowest_cardinality_csr;
822 FETCH get_lowest_cardinality_csr into g_lookup_code;
823 CLOSE get_lowest_cardinality_csr;
824
825 IF g_lookup_code is not null then
826 l_atleast_one_criteria:='Y';
827
828 -- fnd_file.put_line(fnd_file.log, ' g_lookup_code is '||g_lookup_code);
829
830 hr_utility.trace( 'g_lookup_code -> '||g_lookup_code );
831
832 ELSE
833
834 OPEN get_zero_cardinality_csr;
835 FETCH get_zero_cardinality_csr into g_lookup_code;
836 CLOSE get_zero_cardinality_csr;
837
838 -- fnd_file.put_line(fnd_file.log, ' g_lookup_code is '||g_lookup_code);
839
840 hr_utility.trace( ' Inside zero cardinality => g_lookup_code= '||g_lookup_code );
841
842 IF g_lookup_code is not null then
843 l_atleast_one_criteria:='Y';
844
845 END IF;
846
847 /* To handle the case where one or more of the selection criterai have 0 cardinality */
848
849 END IF;
850
851 /*
852 EXCEPTION WHEN no_data_found then
853
854 */
855
856 IF g_lookup_code is NULL then
857
858 BEGIN
859
860 /* when no static selection criteria have been chosen, then invoke the dynamic selection criteria
861 */
862
863
864 -- fnd_file.put_line(fnd_file.log, ' g_lookup_code is null ');
865 hr_utility.trace( ' Inside zero cardinality => g_lookup_code is null');
866
867 IF l_dyn_criteria ='PPG' then
868 l_atleast_one_criteria:='Y';
869
870
871 OPEN ppg_cursor;
872
873 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
874 -- fnd_file.put_line(fnd_file.log, 'after fetch of ppg cursor ');
875 IF l_criteria_value1 is not null then
876
877
878 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
879
880 -- g_exec_string:= l_sql_string;
881
882 LOOP
883
884 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
885 -- fnd_file.put_line(fnd_file.log, 'after second fetch of ppg cursor ');
886 EXIT WHEN PPG_CURSOR%NOTFOUND;
887
888 /*
889 select l_sql_string
890 || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 ||''''
891 into g_exec_string from psp_report_template_details_h ;
892
893 */
894
895 g_exec_string:= l_sql_string|| ' OR '||l_criteria_value1 ||' = '||'''' || l_criteria_value2|| '''';
896
897 l_sql_string:=g_exec_string;
898
899 END LOOP;
900 close ppg_cursor;
901
902 -- l_sql_string:= g_exec_string;
903
904 IF l_sql_string is not null then
905 g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)
906 (select '|| '''' || 'PPG' || ''''||' , count(person_id)
907 from per_assignments_f paf,
908 pay_people_groups ppg
909 where paf.people_group_id= ppg.people_group_id
910 AND paf.assignment_type = ''' || 'E' || '''
911 and paf.effective_end_date >= :p_effort_Start and
912 paf.effective_start_date <= :p_effort_end
913 and
914 ppg.people_group_id
915 in (select people_group_id from pay_people_groups
916 where ' || l_sql_string
917 || ' ))
918 ';
919
920 hr_utility.trace( ' g_exec_string = '||g_exec_string );
921
922 -- fnd_file.put_line(fnd_file.log , ' g_exec_string 1 is '||g_exec_string);
923
924 execute immediate g_exec_string using IN p_effort_Start, p_effort_end;
925 g_lookup_code := 'PPG';
926 END IF;
927 END IF;
928
929
930 ELSIF l_dyn_criteria ='GLA' then
931
932 l_atleast_one_criteria:='Y';
933
934 OPEN gla_cursor;
935
936 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
937
938 IF l_criteria_value1 is not null then
939
940 l_sql_string := l_criteria_value1 ||' between '|| '''' || l_criteria_value2 || '''' ||
941 ' and ' || ''''||l_criteria_value3||'''' ;
942
943 -- g_exec_string := l_sql_string;
944
945
946 LOOP
947
948 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
949 EXIT WHEN GLA_CURSOR%NOTFOUND;
950
951 g_exec_string:= l_sql_string
952 || ' OR ' || l_criteria_value1 || ' between ' || ''''|| l_criteria_value2 || '''' ||
953 ' and ' || ''''|| l_criteria_value3 || '''' ;
954 l_sql_string:=g_exec_string;
955
956 END LOOP;
957 close gla_cursor;
958
959 -- l_sql_string:= g_exec_string;
960
961 -- fnd_file.put_line(fnd_file.log,' before isnerting g_exec_string in GL criteria ');
962 if l_sql_string is not null then
963 g_exec_string := 'insert into psp_selection_cardinality_gt(lookup_code, total_count)
964 ( select '|| '''' || 'GLA' || ''''|| ' , count( distinct psl.person_id)
965 from psp_summary_lines psl, psp_distribution_lines_history pdnh, psp_pre_gen_dist_lines_history ppg,
966 psp_adjustment_lines_history palh
967 , gl_code_combinations gcc
968 where
969 psl.business_group_id = '|| p_business_group_id || ' and
970 psl.set_of_books_id =' || p_set_of_books_id || ' and
971 gcc.code_combination_id= psl.gl_code_combination_id
972 and
973 psl.summary_line_id = pdnh.summary_line_id(+) and
974 psl.summary_line_id = ppg.summary_line_id(+) and
975 psl.summary_line_id = palh.summary_line_id(+) and
976 psl.status_code='||''''||'A'||''''||' and
977 ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
978 pdnh.distribution_date between :p_effort_start and :p_effort_end
979 and pdnh.summary_line_id = psl.summary_line_id
980 and pdnh.reversal_entry_flag is null
981 and pdnh.adjustment_batch_name is null
982 ) OR
983 ( psl.source_type='||''''||'P'||''''||' and
984 ppg.distribution_date between :p_effort_start and :p_effort_end and
985 ppg.summary_line_id = psl.summary_line_id and
986 ppg.adjustment_batch_name is null and
987 ppg.reversal_entry_flag is null)
988 OR (psl.source_type='||''''||'A'||''''||' and
989 palh.summary_line_id = psl.summary_line_id and
990 palh.reversal_entry_flag is null and
991 palh.adjustment_batch_name is null and
992 NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
993 || ' and palh.distribution_date between :p_effort_start and :p_effort_end )) and
994 gcc.code_combination_id= psl.gl_code_combination_id and
995 gcc.code_combination_id in (select code_combination_id from gl_code_combinations
996 where ' || l_sql_string
997 || ' )) ';
998
999
1000 -- fnd_file.put_line(fnd_file.log,' g_exec_string '||g_exec_string);
1001
1002 hr_utility.trace( ' g_exec_string = '||g_exec_string );
1003 execute immediate g_exec_string using IN p_effort_start, p_effort_end, p_effort_start, p_effort_end ,p_effort_start, p_effort_end;
1004 g_lookup_code :='GLA';
1005
1006 END IF;
1007 END IF;
1008
1009
1010 END IF;
1011
1012 END;
1013
1014 END IF;
1015
1016 IF NVL(l_atleast_one_criteria, 'N')='N' then
1017
1018 g_lookup_code:='ALL' ;
1019
1020 END IF;
1021
1022 EXCEPTION WHEN OTHERS THEN
1023 fnd_file.put_line(fnd_file.log, ' EXCEPTION '||sqlerrm);
1024
1025
1026 END;
1027
1028 PROCEDURE prepare_initial_person_list(p_request_id IN NUMBER, p_effort_start
1029 IN DATE, p_effort_end IN DATE, p_business_group_id IN NUMBER, p_Set_of_books_id IN NUMBER) IS
1030 PRAGMA AUTONOMOUS_TRANSACTION;
1031 CURSOR PPG_CURSOR IS
1032 select criteria_value1, criteria_value2 from
1033 psp_report_template_details_h where request_id = p_request_id and
1034 criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG' and
1035 include_exclude_flag='I';
1036
1037
1038 CURSOR GLA_CURSOR IS
1039 select criteria_value1 , criteria_value2, criteria_value3 from
1040 psp_report_template_details_h where request_id = p_request_id and
1041 criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA' and
1042 include_exclude_flag='I';
1043
1044 i number;
1045 l_criteria_value1 varchar2(30);
1046 l_criteria_value2 varchar2(30);
1047 l_criteria_value3 varchar2(30);
1048
1049 l_sql_string varchar2(1000);
1050
1051 l_cnt number;
1052
1053 BEGIN
1054 IF g_lookup_code = 'PTY' then
1055
1056 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1057 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1058 FROM per_people_f ppf,
1059 per_assignments_f paf,
1060 per_assignment_status_types past
1061 WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
1062 FROM psp_report_template_details_h prtd
1063 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1064 AND criteria_lookup_code='PTY'
1065 AND request_id = p_request_id
1066 AND include_exclude_flag='I')
1067 AND paf.assignment_type = 'E'
1068 AND paf.person_id = ppf.person_id
1069 AND ppf.effective_start_date <= p_effort_end
1070 AND ppf.effective_end_date >= p_effort_start
1071 AND paf.effective_start_date <= p_effort_end
1072 AND paf.effective_end_date >= p_effort_start
1073 AND paf.assignment_status_type_id = past.assignment_status_type_id
1074 AND ( past.per_system_status <> 'TERM_ASSIGN'
1075 OR EXISTS ( select null
1076 FROM psp_pre_gen_dist_lines_history
1077 WHERE distribution_date between p_effort_start and p_effort_end
1078 AND assignment_id = paf.assignment_id
1079 AND reversal_entry_flag IS NULL
1080 AND rownum=1 )
1081 OR EXISTS (SELECT null
1082 FROM psp_distribution_lines_history pdlh
1083 , psp_summary_lines psl
1084 WHERE pdlh.summary_line_id = psl.summary_line_id
1085 AND distribution_date between p_effort_start and p_effort_end
1086 AND psl.person_id = paf.person_id
1087 AND psl.assignment_id = paf.assignment_id
1088 AND reversal_entry_flag IS NULL
1089 AND rownum=1));
1090
1091 ELSIF g_lookup_code ='EMP' then
1092
1093 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1094 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1095 FROM per_all_people_f ppf,
1096 per_assignments_f paf,
1097 per_assignment_status_types past
1098 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1099 FROM psp_report_template_details_h prtd
1100 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1101 AND criteria_lookup_code='EMP'
1102 AND include_exclude_flag='I'
1103 AND request_id = p_request_id)
1104 AND paf.assignment_type = 'E'
1105 AND paf.person_id = ppf.person_id
1106 AND ppf.effective_start_date <= p_effort_end
1107 AND ppf.effective_end_date >= p_effort_start
1108 AND paf.effective_start_date <= p_effort_end
1109 AND paf.effective_end_date >= p_effort_start
1110 AND paf.assignment_status_type_id = past.assignment_status_type_id
1111 AND ( past.per_system_status <> 'TERM_ASSIGN'
1112 OR EXISTS ( select null
1113 FROM psp_pre_gen_dist_lines_history
1114 WHERE distribution_date between p_effort_start and p_effort_end
1115 AND assignment_id = paf.assignment_id
1116 AND reversal_entry_flag IS NULL
1117 AND rownum=1 )
1118 OR EXISTS (SELECT null
1119 FROM psp_distribution_lines_history pdlh
1120 , psp_summary_lines psl
1121 WHERE pdlh.summary_line_id = psl.summary_line_id
1122 AND distribution_date between p_effort_start and p_effort_end
1123 AND psl.person_id = paf.person_id
1124 AND psl.assignment_id = paf.assignment_id
1125 AND reversal_entry_flag IS NULL
1126 AND rownum=1));
1127
1128 ELSIF g_lookup_code ='SUP' then
1129
1130 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1131 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1132 FROM per_all_people_f ppf,
1133 per_assignments_f paf,
1134 per_assignment_status_types past
1135 WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
1136 FROM psp_report_template_details_h prtd
1137 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1138 AND criteria_lookup_code='SUP'
1139 AND include_exclude_flag='I'
1140 AND request_id = p_request_id)
1141 AND paf.assignment_type = 'E'
1142 AND paf.person_id = ppf.person_id
1143 AND ppf.effective_start_date <= p_effort_end
1144 AND ppf.effective_end_date >= p_effort_start
1145 AND paf.effective_start_date <= p_effort_end
1146 AND paf.effective_end_date >= p_effort_start
1147 AND paf.assignment_status_type_id = past.assignment_status_type_id
1148 AND ( past.per_system_status <> 'TERM_ASSIGN'
1149 OR EXISTS ( select null
1150 FROM psp_pre_gen_dist_lines_history
1151 WHERE distribution_date between p_effort_start and p_effort_end
1152 AND assignment_id = paf.assignment_id
1153 AND reversal_entry_flag IS NULL
1154 AND rownum=1 )
1155 OR EXISTS (SELECT null
1156 FROM psp_distribution_lines_history pdlh
1157 , psp_summary_lines psl
1158 WHERE pdlh.summary_line_id = psl.summary_line_id
1159 AND distribution_date between p_effort_start and p_effort_end
1160 AND psl.person_id = paf.person_id
1161 AND psl.assignment_id = paf.assignment_id
1162 AND reversal_entry_flag IS NULL
1163 AND rownum=1));
1164
1165
1166 ELSIF g_lookup_code='AWD' then
1167
1168 --- replaced original query for performance issues -- 4429787
1169 insert into psp_selected_persons_t(request_id, person_id, assignment_id)(
1170 select distinct p_request_id, psl.person_id , paf.assignment_id
1171 FROM psp_summary_lines psl,
1172 psp_report_template_details_h prtd ,
1173 per_time_periods ptp,
1174 per_assignments_f paf
1175 WHERE
1176 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
1177 prtd.criteria_lookup_code='AWD' AND
1178 prtd.include_exclude_flag='I' AND
1179 prtd.request_id =p_request_id AND
1180 psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
1181 psl.business_group_id = p_business_group_id AND
1182 psl.set_of_books_id = p_set_of_books_id AND
1183 psl.status_code= 'A' AND
1184 ptp.time_period_id = psl.time_period_id AND
1185 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
1186 paf.person_id = psl.person_id AND
1187 paf.assignment_type = 'E' AND
1188 paf.effective_start_date <= p_effort_end AND
1189 paf.effective_end_date >= p_effort_start AND
1190 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
1191 WHERE pdnh.summary_line_id = psl.summary_line_id
1192 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
1193 AND pdnh.reversal_entry_flag IS NULL
1194 AND pdnh.adjustment_batch_name IS NULL )
1195 OR EXISTS
1196 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
1197 WHERE ppg.summary_line_id = psl.summary_line_id
1198 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
1199 AND ppg.adjustment_batch_name IS NULL AND
1200 ppg.reversal_entry_flag IS NULL)
1201 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
1202 WHERE palh.summary_line_id = psl.summary_line_id
1203 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
1204 AND palh.adjustment_batch_name IS NULL
1205 AND palh.reversal_entry_flag IS NULL
1206 AND NVL(palh.original_line_flag, 'N') ='N')));
1207
1208 ELSIF g_lookup_code ='ATY' then
1209
1210 --- replaced non-performant insert with this for 4429787
1211 INSERT INTO psp_selected_persons_t(request_id , person_id, assignment_id)(
1212 SELECT DISTINCT p_request_id, psl.person_id, paf.assignment_id
1213 FROM psp_summary_lines psl,
1214 psp_report_template_details_h prtd ,
1215 gms_awards_all gaa ,
1216 per_time_periods ptp,
1217 per_assignments_f paf
1218 WHERE psl.award_id = gaa.award_id AND
1219 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
1220 prtd.criteria_lookup_code='ATY' AND
1221 prtd.include_exclude_flag='I' AND
1222 prtd.request_id =p_request_id AND
1223 gaa.type=prtd.criteria_value1 AND
1224 psl.business_group_id = p_business_group_id AND
1225 psl.set_of_books_id = p_set_of_books_id AND
1226 psl.status_code= 'A' AND
1227 ptp.time_period_id = psl.time_period_id AND
1228 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
1229 paf.person_id = psl.person_id AND
1230 paf.assignment_type = 'E' AND
1231 paf.effective_start_date <= p_effort_end AND
1232 paf.effective_end_date >= p_effort_start AND
1233 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
1234 WHERE pdnh.summary_line_id = psl.summary_line_id
1235 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
1236 AND pdnh.reversal_entry_flag IS NULL
1237 AND pdnh.adjustment_batch_name IS NULL )
1238 OR EXISTS
1239 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
1240 WHERE ppg.summary_line_id = psl.summary_line_id
1241 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
1242 AND ppg.adjustment_batch_name IS NULL AND
1243 ppg.reversal_entry_flag IS NULL)
1244 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
1245 WHERE palh.summary_line_id = psl.summary_line_id
1246 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
1247 AND palh.adjustment_batch_name IS NULL
1248 AND palh.reversal_entry_flag IS NULL AND
1249 NVL(palh.original_line_flag, 'N') ='N')));
1250
1251 ELSIF g_lookup_code ='PRT' then
1252 --- replaced non-performant insert with this for 4429787
1253
1254 INSERT INTO psp_selected_persons_t(request_id , person_id, assignment_id)(
1255 SELECT DISTINCT p_request_id, psl.person_id, paf.assignment_id
1256 FROM psp_summary_lines psl,
1257 psp_report_template_details_h prtd ,
1258 pa_projects_all ppa ,
1259 per_time_periods ptp,
1260 per_assignments_f paf
1261 WHERE psl.project_id = ppa.project_id AND
1262 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
1263 prtd.criteria_lookup_code='PRT' AND
1264 prtd.include_exclude_flag='I' AND
1265 prtd.request_id =p_request_id AND
1266 ppa.project_type=prtd.criteria_value1 AND
1267 psl.business_group_id = p_business_group_id AND
1268 psl.set_of_books_id = p_set_of_books_id AND
1269 psl.status_code= 'A' AND
1270 ptp.time_period_id = psl.time_period_id AND
1271 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
1272 paf.person_id = psl.person_id AND
1273 paf.assignment_type = 'E' AND
1274 paf.effective_start_date <= p_effort_end AND
1275 paf.effective_end_date >= p_effort_start AND
1276 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
1277 WHERE pdnh.summary_line_id = psl.summary_line_id
1278 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
1279 AND pdnh.reversal_entry_flag IS NULL
1280 AND pdnh.adjustment_batch_name IS NULL )
1281 OR EXISTS
1282 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
1283 WHERE ppg.summary_line_id = psl.summary_line_id
1284 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
1285 AND ppg.adjustment_batch_name IS NULL AND
1286 ppg.reversal_entry_flag IS NULL)
1287 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
1288 WHERE palh.summary_line_id = psl.summary_line_id
1289 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
1290 AND palh.adjustment_batch_name IS NULL
1291 AND palh.reversal_entry_flag IS NULL AND
1292 NVL(palh.original_line_flag, 'N') ='N')));
1293
1294 -- select count(person_id) into l_cnt from psp_selected_persons_t where request_id = p_request_id;
1295
1296 -- fnd_file.put_line(fnd_file.log,'after insert into psp_selected_persons_t '||l_cnt);
1297
1298
1299
1300 ELSIF g_lookup_code ='PRJ' then
1301 --- replaced original query for performance issues -- 4429787
1302 insert into psp_selected_persons_t(request_id, person_id, assignment_id)(
1303 select distinct p_request_id, psl.person_id, paf.assignment_id
1304 FROM psp_summary_lines psl,
1305 psp_report_template_details_h prtd ,
1306 per_time_periods ptp,
1307 per_assignments_f paf
1308 WHERE
1309 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
1310 prtd.criteria_lookup_code='PRJ' AND
1311 prtd.include_exclude_flag='I' AND
1312 prtd.request_id =p_request_id AND
1313 psl.business_group_id = p_business_group_id AND
1314 psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
1315 psl.set_of_books_id = p_set_of_books_id AND
1316 psl.status_code= 'A' AND
1317 ptp.time_period_id = psl.time_period_id AND
1318 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
1319 paf.person_id = psl.person_id AND
1320 paf.assignment_type = 'E' AND
1321 paf.effective_start_date <= p_effort_end AND
1322 paf.effective_end_date >= p_effort_start AND
1323 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
1324 WHERE pdnh.summary_line_id = psl.summary_line_id
1325 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
1326 AND pdnh.reversal_entry_flag IS NULL
1327 AND pdnh.adjustment_batch_name IS NULL )
1328 OR EXISTS
1329 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
1330 WHERE ppg.summary_line_id = psl.summary_line_id
1331 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
1332 AND ppg.adjustment_batch_name IS NULL AND
1333 ppg.reversal_entry_flag IS NULL)
1334 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
1335 WHERE palh.summary_line_id = psl.summary_line_id
1336 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
1337 AND palh.adjustment_batch_name IS NULL
1338 AND palh.reversal_entry_flag IS NULL AND
1339 NVL(palh.original_line_flag, 'N') ='N')));
1340
1341 ELSIF g_lookup_code ='PAY' then
1342
1343 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1344 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1345 FROM per_all_people_f ppf,
1346 per_assignments_f paf,
1347 per_assignment_status_types past
1348 WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
1349 FROM psp_report_template_details_h prtd
1350 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1351 AND criteria_lookup_code='PAY'
1352 AND include_exclude_flag='I'
1353 AND request_id = p_request_id)
1354 AND paf.assignment_type = 'E'
1355 AND paf.person_id = ppf.person_id
1356 AND ppf.effective_start_date <= p_effort_end
1357 AND ppf.effective_end_date >= p_effort_start
1358 AND paf.effective_start_date <= p_effort_end
1359 AND paf.effective_end_date >= p_effort_start
1360 AND paf.assignment_status_type_id = past.assignment_status_type_id
1361 AND ( past.per_system_status <> 'TERM_ASSIGN'
1362 OR EXISTS ( select null
1363 FROM psp_pre_gen_dist_lines_history
1364 WHERE distribution_date between p_effort_start and p_effort_end
1365 AND assignment_id = paf.assignment_id
1366 AND reversal_entry_flag IS NULL
1367 AND rownum=1 )
1368 OR EXISTS (SELECT null
1369 FROM psp_distribution_lines_history pdlh
1370 , psp_summary_lines psl
1371 WHERE pdlh.summary_line_id = psl.summary_line_id
1372 AND distribution_date between p_effort_start and p_effort_end
1373 AND psl.person_id = paf.person_id
1374 AND psl.assignment_id = paf.assignment_id
1375 AND reversal_entry_flag IS NULL
1376 AND rownum=1));
1377
1378 ELSIF g_lookup_code ='LOC' then
1379
1380 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1381 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1382 FROM per_all_people_f ppf,
1383 per_assignments_f paf,
1384 per_assignment_status_types past
1385 WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
1386 FROM psp_report_template_details_h prtd
1387 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1388 AND criteria_lookup_code='LOC'
1389 AND include_exclude_flag='I'
1390 AND request_id = p_request_id)
1391 AND paf.assignment_type = 'E'
1392 AND paf.person_id = ppf.person_id
1393 AND ppf.effective_start_date <= p_effort_end
1394 AND ppf.effective_end_date >= p_effort_start
1395 AND paf.effective_start_date <= p_effort_end
1396 AND paf.effective_end_date >= p_effort_start
1397 AND paf.assignment_status_type_id = past.assignment_status_type_id
1398 AND ( past.per_system_status <> 'TERM_ASSIGN'
1399 OR EXISTS ( select null
1400 FROM psp_pre_gen_dist_lines_history
1401 WHERE distribution_date between p_effort_start and p_effort_end
1402 AND assignment_id = paf.assignment_id
1403 AND reversal_entry_flag IS NULL
1404 AND rownum=1 )
1405 OR EXISTS (SELECT null
1406 FROM psp_distribution_lines_history pdlh
1407 , psp_summary_lines psl
1408 WHERE pdlh.summary_line_id = psl.summary_line_id
1409 AND distribution_date between p_effort_start and p_effort_end
1410 AND psl.person_id = paf.person_id
1411 AND psl.assignment_id = paf.assignment_id
1412 AND reversal_entry_flag IS NULL
1413 AND rownum=1));
1414
1415 ELSIF g_lookup_code ='ORG' then
1416
1417 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1418 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1419 FROM per_all_people_f ppf,
1420 per_assignments_f paf,
1421 per_assignment_status_types past
1422 WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
1423 FROM psp_report_template_details_h prtd
1424 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1425 AND criteria_lookup_code='ORG'
1426 AND request_id = p_request_id
1427 AND include_exclude_flag='I')
1428 AND paf.assignment_type = 'E'
1429 AND paf.person_id = ppf.person_id
1430 AND ppf.effective_start_date <= p_effort_end
1431 AND ppf.effective_end_date >= p_effort_start
1432 AND paf.effective_start_date <= p_effort_end
1433 AND paf.effective_end_date >= p_effort_start
1434 AND paf.assignment_status_type_id = past.assignment_status_type_id
1435 AND ( past.per_system_status <> 'TERM_ASSIGN'
1436 OR EXISTS ( select null
1437 FROM psp_pre_gen_dist_lines_history
1438 WHERE distribution_date between p_effort_start and p_effort_end
1439 AND assignment_id = paf.assignment_id
1440 AND reversal_entry_flag IS NULL
1441 AND rownum=1 )
1442 OR EXISTS (SELECT null
1443 FROM psp_distribution_lines_history pdlh
1444 , psp_summary_lines psl
1445 WHERE pdlh.summary_line_id = psl.summary_line_id
1446 AND distribution_date between p_effort_start and p_effort_end
1447 AND psl.person_id = paf.person_id
1448 AND psl.assignment_id = paf.assignment_id
1449 AND reversal_entry_flag IS NULL
1450 AND rownum=1));
1451
1452 ELSIF g_lookup_code='CST' then
1453
1454 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1455 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1456 FROM pay_payrolls_f pp,
1457 per_all_people_f ppf,
1458 per_assignments_f paf,
1459 per_assignment_status_types past
1460 WHERE pp.payroll_id = paf.payroll_id
1461 AND pp.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
1462 FROM psp_report_template_details_h prtd
1463 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1464 AND criteria_lookup_code='CST'
1465 AND include_exclude_flag='I'
1466 AND request_id = p_request_id)
1467 AND paf.assignment_type = 'E'
1468 AND paf.person_id = ppf.person_id
1469 AND pp.effective_start_date <= p_effort_end
1470 AND pp.effective_end_date >= p_effort_start
1471 AND ppf.effective_start_date <= p_effort_end
1472 AND ppf.effective_end_date >= p_effort_start
1473 AND paf.effective_start_date <= p_effort_end
1474 AND paf.effective_end_date >= p_effort_start
1475 AND paf.assignment_status_type_id = past.assignment_status_type_id
1476 AND ( past.per_system_status <> 'TERM_ASSIGN'
1477 OR EXISTS ( select null
1478 FROM psp_pre_gen_dist_lines_history
1479 WHERE distribution_date between p_effort_start and p_effort_end
1480 AND assignment_id = paf.assignment_id
1481 AND reversal_entry_flag IS NULL
1482 AND rownum=1 )
1483 OR EXISTS (SELECT null
1484 FROM psp_distribution_lines_history pdlh
1485 , psp_summary_lines psl
1486 WHERE pdlh.summary_line_id = psl.summary_line_id
1487 AND distribution_date between p_effort_start and p_effort_end
1488 AND psl.person_id = paf.person_id
1489 AND psl.assignment_id = paf.assignment_id
1490 AND reversal_entry_flag IS NULL
1491 AND rownum=1));
1492
1493 ELSIF g_lookup_code = 'AST' then
1494
1495 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1496 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1497 FROM per_all_assignments_f paf,
1498 hr_assignment_sets has,
1499 hr_assignment_set_amendments hasa,
1500 per_all_people_f ppf,
1501 per_assignment_status_types past
1502 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
1503 FROM psp_report_template_details_h prtd
1504 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1505 AND criteria_lookup_code='AST'
1506 AND include_exclude_flag='I'
1507 AND request_id =p_request_id)
1508 AND ( (paf.payroll_id = has.payroll_id
1509 AND paf.assignment_type = 'E'
1510 AND paf.effective_start_date <= p_effort_end
1511 AND paf.effective_end_date >= p_effort_start
1512 AND has.assignment_set_id = hasa.assignment_set_id)
1513 OR (paf.assignment_id = hasa.assignment_id
1514 AND paf.assignment_type = 'E'
1515 AND paf.effective_start_date <= p_effort_end
1516 AND paf.effective_end_date >= p_effort_start
1517 AND hasa.assignment_set_id=has.assignment_set_id AND include_or_exclude ='I'))
1518 AND NOT EXISTS (SELECT assignment_id
1519 FROM hr_assignment_set_amendments hasa
1520 WHERE hasa.assignment_id = paf.assignment_id AND hasa.include_or_exclude ='E'
1521 AND paf.assignment_type = 'E'
1522 AND paf.effective_start_date <= p_effort_end
1523 AND paf.effective_end_date >= p_effort_start)
1524 AND paf.person_id = ppf.person_id
1525 AND ppf.effective_start_date <= p_effort_end
1526 AND ppf.effective_end_date >= p_effort_start
1527 AND paf.assignment_status_type_id = past.assignment_status_type_id
1528 AND ( past.per_system_status <> 'TERM_ASSIGN'
1529 OR EXISTS ( select null
1530 FROM psp_pre_gen_dist_lines_history
1531 WHERE distribution_date between p_effort_start and p_effort_end
1532 AND assignment_id = paf.assignment_id
1533 AND reversal_entry_flag IS NULL
1534 AND rownum=1 )
1535 OR EXISTS (SELECT null
1536 FROM psp_distribution_lines_history pdlh
1537 , psp_summary_lines psl
1538 WHERE pdlh.summary_line_id = psl.summary_line_id
1539 AND distribution_date between p_effort_start and p_effort_end
1540 AND psl.person_id = paf.person_id
1541 AND psl.assignment_id = paf.assignment_id
1542 AND reversal_entry_flag IS NULL
1543 AND rownum=1));
1544
1545
1546 ELSIF g_lookup_code ='JOB' then
1547
1548 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1549 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1550 FROM per_all_people_f ppf,
1551 per_assignments_f paf,
1552 per_assignment_status_types past
1553 WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
1554 FROM psp_report_template_details_h prtd
1555 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1556 AND criteria_lookup_code='JOB'
1557 AND include_exclude_flag='I'
1558 AND request_id = p_request_id)
1559 AND paf.assignment_type = 'E'
1560 AND paf.person_id = ppf.person_id
1561 AND ppf.effective_start_date <= p_effort_end
1562 AND ppf.effective_end_date >= p_effort_start
1563 AND paf.effective_start_date <= p_effort_end
1564 AND paf.effective_end_date >= p_effort_start
1565 AND paf.assignment_status_type_id = past.assignment_status_type_id
1566 AND ( past.per_system_status <> 'TERM_ASSIGN'
1567 OR EXISTS ( select null
1568 FROM psp_pre_gen_dist_lines_history
1569 WHERE distribution_date between p_effort_start and p_effort_end
1570 AND assignment_id = paf.assignment_id
1571 AND reversal_entry_flag IS NULL
1572 AND rownum=1 )
1573 OR EXISTS (SELECT null
1574 FROM psp_distribution_lines_history pdlh
1575 , psp_summary_lines psl
1576 WHERE pdlh.summary_line_id = psl.summary_line_id
1577 AND distribution_date between p_effort_start and p_effort_end
1578 AND psl.person_id = paf.person_id
1579 AND psl.assignment_id = paf.assignment_id
1580 AND reversal_entry_flag IS NULL
1581 AND rownum=1));
1582
1583 ELSIF g_lookup_code ='POS' then
1584
1585 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1586 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1587 FROM per_all_people_f ppf,
1588 per_assignments_f paf,
1589 per_assignment_status_types past
1590 WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
1591 FROM psp_report_template_details_h prtd
1592 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1593 AND criteria_lookup_code='POS'
1594 AND request_id = p_request_id
1595 AND include_exclude_flag='I')
1596 AND paf.assignment_type = 'E'
1597 AND paf.person_id = ppf.person_id
1598 AND ppf.effective_start_date <= p_effort_end
1599 AND ppf.effective_end_date >= p_effort_start
1600 AND paf.effective_start_date <= p_effort_end
1601 AND paf.effective_end_date >= p_effort_start
1602 AND paf.assignment_status_type_id = past.assignment_status_type_id
1603 AND ( past.per_system_status <> 'TERM_ASSIGN'
1604 OR EXISTS ( select null
1605 FROM psp_pre_gen_dist_lines_history
1606 WHERE distribution_date between p_effort_start and p_effort_end
1607 AND assignment_id = paf.assignment_id
1608 AND reversal_entry_flag IS NULL
1609 AND rownum=1 )
1610 OR EXISTS (SELECT null
1611 FROM psp_distribution_lines_history pdlh
1612 , psp_summary_lines psl
1613 WHERE pdlh.summary_line_id = psl.summary_line_id
1614 AND distribution_date between p_effort_start and p_effort_end
1615 AND psl.person_id = paf.person_id
1616 AND psl.assignment_id = paf.assignment_id
1617 AND reversal_entry_flag IS NULL
1618 AND rownum=1));
1619
1620 ELSIF g_lookup_code ='ASS' then
1621
1622 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1623 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1624 FROM per_all_people_f ppf,
1625 per_assignments_f paf,
1626 per_assignment_status_types past
1627 WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
1628 FROM psp_report_template_details_h prtd
1629 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1630 AND criteria_lookup_code='ASS'
1631 AND request_id = p_request_id
1632 AND include_exclude_flag='I')
1633 AND paf.assignment_type = 'E'
1634 AND paf.person_id = ppf.person_id
1635 AND ppf.effective_start_date <= p_effort_end
1636 AND ppf.effective_end_date >= p_effort_start
1637 AND paf.effective_start_date <= p_effort_end
1638 AND paf.effective_end_date >= p_effort_start
1639 AND ppf.business_group_id = p_business_group_id
1640 AND paf.assignment_status_type_id = past.assignment_status_type_id
1641 AND ( past.per_system_status <> 'TERM_ASSIGN'
1642 OR EXISTS ( select null
1643 FROM psp_pre_gen_dist_lines_history
1644 WHERE distribution_date between p_effort_start and p_effort_end
1645 AND assignment_id = paf.assignment_id
1646 AND reversal_entry_flag IS NULL
1647 AND rownum=1 )
1648 OR EXISTS (SELECT null
1649 FROM psp_distribution_lines_history pdlh
1650 , psp_summary_lines psl
1651 WHERE pdlh.summary_line_id = psl.summary_line_id
1652 AND distribution_date between p_effort_start and p_effort_end
1653 AND psl.person_id = paf.person_id
1654 AND psl.assignment_id = paf.assignment_id
1655 AND reversal_entry_flag IS NULL
1656 AND rownum=1));
1657
1658
1659 ELSIF g_lookup_code='PPG' then
1660
1661
1662 open ppg_cursor;
1663 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
1664 IF l_criteria_value1 is not null then
1665
1666
1667 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
1668
1669 -- g_exec_string:= l_sql_string;
1670
1671 LOOP
1672
1673 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
1674 EXIT WHEN PPG_CURSOR%NOTFOUND;
1675
1676 /*
1677 select l_sql_string
1678 || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 ||''''
1679 into g_exec_string from psp_report_template_details_h ;
1680
1681 */
1682 g_exec_string:= l_sql_string || ' OR '||l_criteria_value1 ||' = ' || ''''||l_criteria_value2 || '''';
1683
1684 l_sql_string:= g_exec_string;
1685
1686 END LOOP;
1687 close ppg_cursor;
1688
1689
1690 if l_sql_string is not null then
1691 g_exec_string := 'insert into psp_selected_persons_t
1692 (request_id, person_id, assignment_id)
1693 (select :request_id , paf.person_id, paf.assignment_id
1694 from per_assignments_f paf,
1695 pay_people_groups ppg,
1696 per_all_people_f ppf,
1697 per_assignment_status_types past
1698 where paf.people_group_id= ppg.people_group_id
1699 AND paf.person_id = ppf.person_id
1700 AND paf.assignment_status_type_id = past.assignment_status_type_id
1701 AND paf.assignment_type = ''' || 'E' || '''
1702 AND paf.effective_end_date >= :p_effort_Start
1703 AND paf.effective_start_date <= :p_effort_end
1704 AND ppf.effective_end_date >= :p_effort_start
1705 AND ppf.effective_start_date <= :p_effort_end
1706 AND ppg.people_group_id IN (SELECT people_group_id FROM pay_people_groups
1707 WHERE ' || l_sql_string|| ' )
1708 AND ( past.per_system_status <> '''|| 'TERM_ASSIGN' ||'''
1709 OR EXISTS ( select null
1710 FROM psp_pre_gen_dist_lines_history
1711 WHERE distribution_date between :p_effort_start and :p_effort_end
1712 AND assignment_id = paf.assignment_id
1713 AND reversal_entry_flag IS NULL
1714 AND rownum=1 )
1715 OR EXISTS (SELECT null
1716 FROM psp_distribution_lines_history pdlh
1717 , psp_summary_lines psl
1718 WHERE pdlh.summary_line_id = psl.summary_line_id
1719 AND distribution_date between :p_effort_start and :p_effort_end
1720 AND psl.person_id = paf.person_id
1721 AND psl.assignment_id = paf.assignment_id
1722 AND reversal_entry_flag IS NULL
1723 AND rownum=1)))';
1724
1725 -- fnd_file.put_line(fnd_file.log, 'g_exec_string2 = '||g_exec_string);
1726
1727 execute immediate g_exec_string using IN p_request_id, p_effort_start, p_effort_end,
1728 p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_effort_start, p_effort_end;
1729 end if;
1730 end if;
1731
1732
1733 ELSIF g_lookup_code ='GLA' then
1734
1735 OPEN gla_cursor;
1736
1737 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
1738
1739 IF l_criteria_value1 is not null then
1740
1741 l_sql_string := l_criteria_value1 ||' between '|| '''' || l_criteria_value2 ||''''||
1742 ' and ' || ''''|| l_criteria_value3 || '''' ;
1743 -- g_exec_string := l_sql_string;
1744
1745
1746 LOOP
1747
1748 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
1749 EXIT WHEN GLA_CURSOR%NOTFOUND;
1750
1751 g_exec_string:= l_sql_string
1752 || ' OR ' || l_criteria_value1 || ' between ' || ''''|| l_criteria_value2 || '''' ||
1753 ' and ' || ''''|| l_criteria_value3 || '''' ;
1754
1755 l_sql_string:=g_exec_string;
1756
1757
1758 END LOOP;
1759 close gla_cursor;
1760
1761 -- l_sql_string:= g_exec_string;
1762
1763 if l_sql_string is not null then
1764 g_exec_string := 'insert into psp_selected_persons_t(request_id, person_id, assignment_id)
1765 ( select :p_request_id, psl.person_id, psl.assignment_id
1766 from psp_summary_lines psl, psp_distribution_lines_history pdnh,
1767 psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
1768 gl_code_combinations gcc
1769 where
1770 psl.business_group_id = '|| p_business_group_id || ' and
1771 psl.set_of_books_id = ' || p_Set_of_books_id ||' and
1772 psl.summary_line_id = pdnh.summary_line_id(+) and
1773 psl.summary_line_id = ppg.summary_line_id(+) and
1774 psl.summary_line_id = palh.summary_line_id(+) and
1775 psl.status_code= '||''''||'A'||''''||' and
1776 ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
1777 pdnh.distribution_date between :p_effort_start and :p_effort_end
1778 and pdnh.reversal_entry_flag is null
1779 and pdnh.summary_line_id = psl.summary_line_id
1780 and pdnh.adjustment_batch_name is null
1781 ) OR
1782 ( psl.source_type='||''''||'P'||''''||' and
1783 ppg.distribution_date between :p_effort_start and :p_effort_end and
1784 ppg.summary_line_id = psl.summary_line_id and
1785 ppg.adjustment_batch_name is null and
1786 ppg.reversal_entry_flag is null)
1787 OR (psl.source_type='||''''||'A'||''''||' and
1788 palh.adjustment_batch_name is null and
1789 palh.summary_line_id =psl.summary_line_id and
1790 NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
1791 || ' and palh.distribution_date between :p_effort_start and :p_effort_end )) and
1792 gcc.code_combination_id= psl.gl_code_combination_id and
1793 gcc.code_combination_id in (select code_combination_id from gl_code_combinations
1794 where ' || l_sql_string
1795 || ' )) ';
1796
1797 -- fnd_file.put_line(fnd_file.log, 'g_exec_string 2= '||g_exec_string);
1798
1799 execute immediate g_exec_string using iN p_request_id, p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_effort_start, p_effort_end;
1800
1801
1802 END IF;
1803 END IF;
1804
1805 ELSIf g_lookup_code='ALL' then
1806
1807 insert into psp_selected_persons_t(request_id, person_id)(select p_request_id,
1808 person_id from per_people_f where
1809 business_group_id = p_business_group_id and
1810 effective_start_date <= p_effort_end and
1811 effective_end_date >= p_effort_start);
1812 END IF;
1813
1814
1815 COMMIT;
1816 END;
1817
1818
1819
1820 PROCEDURE prune_initial_person_list(p_request_id IN NUMBER , p_effort_start IN DATE, p_effort_end IN DATE,
1821 p_business_group_id IN NUMBER, p_Set_of_books_id IN NUMBER)
1822 IS
1823
1824
1825 CURSOR get_all_selection_criteria(p_request_id IN NUMBER) is
1826 SELECT distinct criteria_lookup_code,
1827 include_exclude_flag from
1828 psp_report_template_details_h where request_id = p_request_id and
1829 criteria_lookup_type='PSP_SELECTION_CRITERIA' ORDER BY include_exclude_flag;
1830
1831
1832 CURSOR PPG_CURSOR IS
1833 select criteria_value1, criteria_value2 from
1834 psp_report_template_details_h where request_id = p_request_id and
1835 criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG' and include_exclude_flag='I';
1836
1837
1838 CURSOR GLA_CURSOR IS
1839 select criteria_value1 , criteria_value2, criteria_value3 from
1840 psp_report_template_details_h where request_id = p_request_id and
1841 criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA' and include_exclude_flag='I';
1842
1843
1844 type t_varchar_30_type is TABLE Of VARCHAR2(30) INDEX BY BINARY_INTEGER;
1845 type t_varchar_1_type is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
1846
1847 l_criteria_value1 varchar2(30);
1848
1849 l_criteria_value2 varchar2(30);
1850
1851
1852 l_criteria_value3 varchar2(30);
1853
1854
1855
1856 l_sql_string varchar2(1000);
1857
1858
1859 i number;
1860
1861 BEGIN
1862 open get_all_selection_criteria(p_request_id);
1863 fetch get_all_selection_criteria BULK COLLECT into template_sel_criteria.array_sel_criteria,
1864 template_sel_criteria.array_inc_exc_flag;
1865
1866 close get_all_selection_criteria;
1867
1868
1869
1870 for i in 1..template_sel_criteria.array_sel_criteria.count
1871
1872 LOOP
1873
1874 IF template_sel_criteria.array_inc_exc_flag(i) = 'I' THEN
1875
1876
1877 IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code then
1878 IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
1879
1880 DELETE FROM psp_selected_persons_t pspt
1881 WHERE request_id = p_request_id
1882 /* Bug 5087294 : Performance fix replacing not in with not exists */
1883 -- AND person_id NOT IN (SELECT NVL( person_id, 0)
1884 AND NOT EXISTS ( SELECT 1
1885 FROM per_people_f ppf,
1886 psp_report_template_details_h prtd,
1887 per_assignments_f paf,
1888 per_assignment_status_types past
1889 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1890 AND criteria_lookup_code='PTY'
1891 AND paf.person_id = ppf.person_id
1892 AND paf.assignment_type = 'E'
1893 AND paf.effective_start_date <= p_effort_end
1894 AND paf.effective_end_date >= p_effort_start
1895 AND ppf.effective_start_date <= p_effort_end
1896 AND ppf.effective_end_date >= p_effort_start
1897 AND include_exclude_flag='I'
1898 AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
1899 AND prtd.request_id = p_request_id
1900 AND ppf.person_id = pspt.person_id
1901 AND paf.assignment_status_type_id = past.assignment_status_type_id
1902 AND ( past.per_system_status <> 'TERM_ASSIGN'
1903 OR EXISTS ( select null
1904 FROM psp_pre_gen_dist_lines_history
1905 WHERE distribution_date between p_effort_start and p_effort_end
1906 AND assignment_id = paf.assignment_id
1907 AND reversal_entry_flag IS NULL
1908 AND rownum=1 )
1909 OR EXISTS (SELECT null
1910 FROM psp_distribution_lines_history pdlh
1911 , psp_summary_lines psl
1912 WHERE pdlh.summary_line_id = psl.summary_line_id
1913 AND distribution_date between p_effort_start and p_effort_end
1914 AND psl.person_id = paf.person_id
1915 AND psl.assignment_id = paf.assignment_id
1916 AND reversal_entry_flag IS NULL
1917 AND rownum=1)));
1918
1919 ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
1920
1921 DELETE FROM psp_selected_persons_t pspt
1922 WHERE request_id = p_request_id
1923 /* Bug 5087294 : Performance fix replacing not in with not exists */
1924 -- AND person_id NOT IN (SELECT NVL(person_id,0)
1925 AND NOT EXISTS ( SELECT 1
1926 FROM per_all_people_f ppf,
1927 per_assignments_f paf,
1928 per_assignment_status_types past
1929 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1930 FROM psp_report_template_details_h prtd
1931 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1932 AND criteria_lookup_code='EMP'
1933 AND include_exclude_flag='I'
1934 AND prtd.request_id = p_request_id)
1935 AND paf.person_id = ppf.person_id
1936 AND paf.assignment_type = 'E'
1937 AND paf.effective_start_date <= p_effort_end
1938 AND paf.effective_end_date >= p_effort_start
1939 AND ppf.effective_start_date <= p_effort_end
1940 AND ppf.effective_end_date >= p_effort_start
1941 AND ppf.person_id = pspt.person_id
1942 AND paf.assignment_status_type_id = past.assignment_status_type_id
1943 AND ( past.per_system_status <> 'TERM_ASSIGN'
1944 OR EXISTS ( select null
1945 FROM psp_pre_gen_dist_lines_history
1946 WHERE distribution_date between p_effort_start and p_effort_end
1947 AND assignment_id = paf.assignment_id
1948 AND reversal_entry_flag IS NULL
1949 AND rownum=1 )
1950 OR EXISTS (SELECT null
1951 FROM psp_distribution_lines_history pdlh
1952 , psp_summary_lines psl
1953 WHERE pdlh.summary_line_id = psl.summary_line_id
1954 AND distribution_date between p_effort_start and p_effort_end
1955 AND psl.person_id = paf.person_id
1956 AND psl.assignment_id = paf.assignment_id
1957 AND reversal_entry_flag IS NULL
1958 AND rownum=1)));
1959
1960
1961 ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
1962
1963 DELETE FROM psp_selected_persons_t pspt
1964 WHERE request_id = p_request_id
1965 /* Bug 5087294 : Performance fix replacing not in with not exists */
1966 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
1967 AND NOT EXISTS ( SELECT 1
1968 FROM per_all_assignments_f paf,
1969 psp_report_template_details_h prtd,
1970 per_assignment_status_types past
1971 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1972 AND criteria_lookup_code='SUP'
1973 AND include_exclude_flag='I'
1974 AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
1975 AND prtd.request_id = p_request_id
1976 AND paf.assignment_type = 'E'
1977 AND paf.effective_start_date <= p_effort_end
1978 AND effective_end_date >= p_effort_start
1979 AND paf.person_id = pspt.person_id
1980 AND paf.assignment_status_type_id = past.assignment_status_type_id
1981 AND ( past.per_system_status <> 'TERM_ASSIGN'
1982 OR EXISTS ( select null
1983 FROM psp_pre_gen_dist_lines_history
1984 WHERE distribution_date between p_effort_start and p_effort_end
1985 AND assignment_id = paf.assignment_id
1986 AND reversal_entry_flag IS NULL
1987 AND rownum=1 )
1988 OR EXISTS (SELECT null
1989 FROM psp_distribution_lines_history pdlh
1990 , psp_summary_lines psl
1991 WHERE pdlh.summary_line_id = psl.summary_line_id
1992 AND distribution_date between p_effort_start and p_effort_end
1993 AND psl.person_id = paf.person_id
1994 AND psl.assignment_id = paf.assignment_id
1995 AND reversal_entry_flag IS NULL
1996 AND rownum=1)));
1997
1998
1999 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
2000
2001
2002 --- replaced original query for performance issues -- 4429787
2003 delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2004 select psl.person_id
2005 FROM psp_summary_lines psl,
2006 psp_report_template_details_h prtd ,
2007 per_time_periods ptp
2008 WHERE
2009 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2010 prtd.criteria_lookup_code='AWD' AND
2011 prtd.include_exclude_flag='I' AND
2012 prtd.request_id =p_request_id AND
2013 psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
2014 psl.business_group_id = p_business_group_id AND
2015 psl.set_of_books_id = p_set_of_books_id AND
2016 psl.status_code= 'A' AND
2017 ptp.time_period_id = psl.time_period_id AND
2018 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2019 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2020 WHERE pdnh.summary_line_id = psl.summary_line_id
2021 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2022 AND pdnh.reversal_entry_flag IS NULL
2023 AND pdnh.adjustment_batch_name IS NULL )
2024 OR EXISTS
2025 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2026 WHERE ppg.summary_line_id = psl.summary_line_id
2027 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2028 AND ppg.adjustment_batch_name IS NULL AND
2029 ppg.reversal_entry_flag IS NULL)
2030 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2031 WHERE palh.summary_line_id = psl.summary_line_id
2032 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2033 AND palh.adjustment_batch_name IS NULL
2034 AND palh.reversal_entry_flag IS NULL
2035 AND NVL(palh.original_line_flag, 'N') ='N')));
2036
2037 ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' then
2038
2039
2040 --- replaced non-performant delete with this for 4429787
2041 DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2042 SELECT NVL(psl.person_id,0)
2043 FROM psp_summary_lines psl,
2044 psp_report_template_details_h prtd ,
2045 gms_awards_all gaa ,
2046 per_time_periods ptp
2047 WHERE psl.award_id = gaa.award_id AND
2048 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2049 prtd.criteria_lookup_code='ATY' AND
2050 prtd.include_exclude_flag='I' AND
2051 prtd.request_id =p_request_id AND
2052 gaa.type=prtd.criteria_value1 AND
2053 psl.business_group_id = p_business_group_id AND
2054 psl.set_of_books_id = p_set_of_books_id AND
2055 psl.status_code= 'A' AND
2056 ptp.time_period_id = psl.time_period_id AND
2057 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2058 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2059 WHERE pdnh.summary_line_id = psl.summary_line_id
2060 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2061 AND pdnh.reversal_entry_flag IS NULL
2062 AND pdnh.adjustment_batch_name IS NULL )
2063 OR EXISTS
2064 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2065 WHERE ppg.summary_line_id = psl.summary_line_id
2066 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2067 AND ppg.adjustment_batch_name IS NULL AND
2068 ppg.reversal_entry_flag IS NULL)
2069 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2070 WHERE palh.summary_line_id = psl.summary_line_id
2071 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2072 AND palh.adjustment_batch_name IS NULL
2073 AND palh.reversal_entry_flag IS NULL AND
2074 NVL(palh.original_line_flag, 'N') ='N')));
2075
2076 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
2077
2078 --- replaced non-performant delete with this for 4429787
2079 DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2080 SELECT NVL(psl.person_id,0)
2081 FROM psp_summary_lines psl,
2082 psp_report_template_details_h prtd ,
2083 pa_projects_all ppa ,
2084 per_time_periods ptp
2085 WHERE psl.project_id = ppa.project_id AND
2086 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2087 prtd.criteria_lookup_code='PRT' AND
2088 prtd.include_exclude_flag='I' AND
2089 prtd.request_id =p_request_id AND
2090 ppa.project_type=prtd.criteria_value1 AND
2091 psl.business_group_id = p_business_group_id AND
2092 psl.set_of_books_id = p_set_of_books_id AND
2093 psl.status_code= 'A' AND
2094 ptp.time_period_id = psl.time_period_id AND
2095 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2096 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2097 WHERE pdnh.summary_line_id = psl.summary_line_id
2098 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2099 AND pdnh.reversal_entry_flag IS NULL
2100 AND pdnh.adjustment_batch_name IS NULL )
2101 OR EXISTS
2102 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2103 WHERE ppg.summary_line_id = psl.summary_line_id
2104 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2105 AND ppg.adjustment_batch_name IS NULL AND
2106 ppg.reversal_entry_flag IS NULL)
2107 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2108 WHERE palh.summary_line_id = psl.summary_line_id
2109 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2110 AND palh.adjustment_batch_name IS NULL
2111 AND palh.reversal_entry_flag IS NULL AND
2112 NVL(palh.original_line_flag, 'N') ='N')));
2113
2114 elsif template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
2115
2116 --- replaced non-performant insert with this for 4429787
2117 delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2118 select nvl( psl.person_id ,0)
2119 FROM psp_summary_lines psl,
2120 psp_report_template_details_h prtd ,
2121 per_time_periods ptp
2122 WHERE
2123 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2124 prtd.criteria_lookup_code='PRJ' AND
2125 prtd.include_exclude_flag='I' AND
2126 prtd.request_id =p_request_id AND
2127 psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
2128 psl.business_group_id = p_business_group_id AND
2129 psl.set_of_books_id = p_set_of_books_id AND
2130 psl.status_code= 'A' AND
2131 ptp.time_period_id = psl.time_period_id AND
2132 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2133 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2134 WHERE pdnh.summary_line_id = psl.summary_line_id
2135 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2136 AND pdnh.reversal_entry_flag IS NULL
2137 AND pdnh.adjustment_batch_name IS NULL )
2138 OR EXISTS
2139 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2140 WHERE ppg.summary_line_id = psl.summary_line_id
2141 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2142 AND ppg.adjustment_batch_name IS NULL AND
2143 ppg.reversal_entry_flag IS NULL)
2144 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2145 WHERE palh.summary_line_id = psl.summary_line_id
2146 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2147 AND palh.adjustment_batch_name IS NULL
2148 AND palh.reversal_entry_flag IS NULL AND
2149 NVL(palh.original_line_flag, 'N') ='N')));
2150
2151 elsif template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
2152
2153 DELETE FROM psp_selected_persons_t pspt
2154 WHERE request_id = p_request_id
2155 /* Bug 5087294 : Performance fix replacing not in with not exists */
2156 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2157 AND NOT EXISTS ( SELECT 1
2158 FROM per_assignments_f paf,
2159 psp_report_template_details_h prtd,
2160 per_assignment_status_types past
2161 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2162 AND criteria_lookup_code='PAY'
2163 AND include_exclude_flag='I'
2164 AND paf.assignment_type = 'E'
2165 AND effective_start_date <= p_effort_end
2166 AND effective_end_date >= p_effort_start
2167 AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
2168 AND prtd.request_id = p_request_id
2169 AND paf.person_id = pspt.person_id
2170 AND paf.assignment_status_type_id = past.assignment_status_type_id
2171 AND ( past.per_system_status <> 'TERM_ASSIGN'
2172 OR EXISTS ( select null
2173 FROM psp_pre_gen_dist_lines_history
2174 WHERE distribution_date between p_effort_start and p_effort_end
2175 AND assignment_id = paf.assignment_id
2176 AND reversal_entry_flag IS NULL
2177 AND rownum=1 )
2178 OR EXISTS (SELECT null
2179 FROM psp_distribution_lines_history pdlh
2180 , psp_summary_lines psl
2181 WHERE pdlh.summary_line_id = psl.summary_line_id
2182 AND distribution_date between p_effort_start and p_effort_end
2183 AND psl.person_id = paf.person_id
2184 AND psl.assignment_id = paf.assignment_id
2185 AND reversal_entry_flag IS NULL
2186 AND rownum=1)));
2187
2188
2189
2190 elsif template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
2191
2192 DELETE FROM psp_selected_persons_t pspt
2193 WHERE request_id = p_request_id
2194 /* Bug 5087294 : Performance fix replacing not in with not exists */
2195 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2196 AND NOT EXISTS ( SELECT 1
2197 FROM per_assignments_f paf ,
2198 psp_report_template_details_h prtd,
2199 per_assignment_status_types past
2200 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2201 AND criteria_lookup_code='LOC'
2202 AND paf.assignment_type = 'E'
2203 AND effective_start_date <= p_effort_end
2204 AND effective_end_date >= p_effort_start
2205 AND include_exclude_flag='I'
2206 AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
2207 AND prtd.request_id = p_request_id
2208 AND paf.person_id = pspt.person_id
2209 AND paf.assignment_status_type_id = past.assignment_status_type_id
2210 AND ( past.per_system_status <> 'TERM_ASSIGN'
2211 OR EXISTS ( select null
2212 FROM psp_pre_gen_dist_lines_history
2213 WHERE distribution_date between p_effort_start and p_effort_end
2214 AND assignment_id = paf.assignment_id
2215 AND reversal_entry_flag IS NULL
2216 AND rownum=1 )
2217 OR EXISTS (SELECT null
2218 FROM psp_distribution_lines_history pdlh
2219 , psp_summary_lines psl
2220 WHERE pdlh.summary_line_id = psl.summary_line_id
2221 AND distribution_date between p_effort_start and p_effort_end
2222 AND psl.person_id = paf.person_id
2223 AND psl.assignment_id = paf.assignment_id
2224 AND reversal_entry_flag IS NULL
2225 AND rownum=1)));
2226
2227 elsif template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
2228
2229 DELETE FROM psp_selected_persons_t pspt
2230 WHERE request_id = p_request_id
2231 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2232 AND NOT EXISTS ( SELECT 1
2233 FROM per_assignments_f paf ,
2234 psp_report_template_details_h prtd,
2235 per_assignment_status_types past
2236 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2237 AND criteria_lookup_code='ORG'
2238 AND include_exclude_flag='I'
2239 AND paf.assignment_type = 'E'
2240 AND effective_start_date <= p_effort_end
2241 AND effective_end_date >= p_effort_start
2242 AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
2243 AND prtd.request_id = p_request_id
2244 AND paf.person_id = pspt.person_id
2245 AND paf.assignment_status_type_id = past.assignment_status_type_id
2246 AND ( past.per_system_status <> 'TERM_ASSIGN'
2247 OR EXISTS ( select null
2248 FROM psp_pre_gen_dist_lines_history
2249 WHERE distribution_date between p_effort_start and p_effort_end
2250 AND assignment_id = paf.assignment_id
2251 AND reversal_entry_flag IS NULL
2252 AND rownum=1 )
2253 OR EXISTS (SELECT null
2254 FROM psp_distribution_lines_history pdlh
2255 , psp_summary_lines psl
2256 WHERE pdlh.summary_line_id = psl.summary_line_id
2257 AND distribution_date between p_effort_start and p_effort_end
2258 AND psl.person_id = paf.person_id
2259 AND psl.assignment_id = paf.assignment_id
2260 AND reversal_entry_flag IS NULL
2261 AND rownum=1)));
2262
2263
2264 elsif template_sel_criteria.array_sel_criteria(i)='CST' THEN
2265 DELETE FROM psp_selected_persons_t pspt
2266 WHERE request_id = p_request_id
2267 /* Bug 5087294 : Performance fix replacing not in with not exists */
2268 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2269 AND NOT EXISTS ( SELECT 1
2270 FROM per_assignments_f paf,
2271 pay_payrolls_f ppf,
2272 per_assignment_status_types past
2273 WHERE ppf.payroll_id = paf.payroll_id
2274 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
2275 FROM psp_report_template_details_h prtd
2276 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2277 AND criteria_lookup_code='CST'
2278 AND include_exclude_flag='I'
2279 AND request_id = p_request_id)
2280 AND paf.assignment_type = 'E'
2281 AND ppf.effective_start_date <= p_effort_end
2282 AND ppf.effective_end_date >= p_effort_start
2283 AND paf.effective_start_date <= p_effort_end
2284 AND paf.effective_end_date >= p_effort_start
2285 AND paf.person_id = pspt.person_id
2286 AND paf.assignment_status_type_id = past.assignment_status_type_id
2287 AND ( past.per_system_status <> 'TERM_ASSIGN'
2288 OR EXISTS ( select null
2289 FROM psp_pre_gen_dist_lines_history
2290 WHERE distribution_date between p_effort_start and p_effort_end
2291 AND assignment_id = paf.assignment_id
2292 AND reversal_entry_flag IS NULL
2293 AND rownum=1 )
2294 OR EXISTS (SELECT null
2295 FROM psp_distribution_lines_history pdlh
2296 , psp_summary_lines psl
2297 WHERE pdlh.summary_line_id = psl.summary_line_id
2298 AND distribution_date between p_effort_start and p_effort_end
2299 AND psl.person_id = paf.person_id
2300 AND psl.assignment_id = paf.assignment_id
2301 AND reversal_entry_flag IS NULL
2302 AND rownum=1)));
2303
2304
2305 elsif template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
2306
2307 DELETE FROM psp_selected_persons_t
2308 WHERE request_id = p_request_id
2309 AND person_id NOT IN (SELECT NVL(paf.person_id,0)
2310 FROM per_all_assignments_f paf,
2311 hr_assignment_sets has,
2312 hr_assignment_set_amendments hasa,
2313 per_assignment_status_types past
2314 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
2315 FROM psp_report_template_details_h prtd
2316 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2317 AND criteria_lookup_code='AST'
2318 AND include_exclude_flag='I'
2319 AND request_id =p_request_id)
2320 AND ( (paf.payroll_id = has.payroll_id
2321 AND paf.assignment_type = 'E'
2322 AND paf.effective_start_date <= p_effort_end
2323 AND paf.effective_end_date >= p_effort_start
2324 AND has.assignment_set_id = hasa.assignment_set_id)
2325 OR (paf.assignment_id = hasa.assignment_id
2326 AND paf.assignment_type = 'E'
2327 AND paf.effective_start_date <= p_effort_end
2328 AND paf.effective_end_date >= p_effort_start
2329 AND hasa.assignment_set_id=has.assignment_set_id
2330 AND include_or_exclude ='I'))
2331 AND NOT EXISTS (SELECT assignment_id
2332 FROM hr_assignment_set_amendments hasa
2333 WHERE hasa.assignment_id = paf.assignment_id
2334 AND paf.assignment_type = 'E'
2335 AND hasa.include_or_exclude ='E'
2336 AND paf.effective_start_date <= p_effort_end
2337 AND paf.effective_end_date >= p_effort_start)
2338 AND paf.assignment_status_type_id = past.assignment_status_type_id
2339 AND ( past.per_system_status <> 'TERM_ASSIGN'
2340 OR EXISTS ( select null
2341 FROM psp_pre_gen_dist_lines_history
2342 WHERE distribution_date between p_effort_start and p_effort_end
2343 AND assignment_id = paf.assignment_id
2344 AND reversal_entry_flag IS NULL
2345 AND rownum=1 )
2346 OR EXISTS (SELECT null
2347 FROM psp_distribution_lines_history pdlh
2348 , psp_summary_lines psl
2349 WHERE pdlh.summary_line_id = psl.summary_line_id
2350 AND distribution_date between p_effort_start and p_effort_end
2351 AND psl.person_id = paf.person_id
2352 AND psl.assignment_id = paf.assignment_id
2353 AND reversal_entry_flag IS NULL
2354 AND rownum=1)));
2355
2356
2357 elsif template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
2358
2359 DELETE FROM psp_selected_persons_t pspt
2360 WHERE request_id = p_request_id
2361 /* Bug 5087294 : Performance fix replacing not in with not exists */
2362 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2363 AND NOT EXISTS ( SELECT 1
2364 FROM per_assignments_f paf ,
2365 psp_report_template_details_h prtd,
2366 per_assignment_status_types past
2367 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2368 AND criteria_lookup_code='JOB'
2369 AND include_exclude_flag='I'
2370 AND paf.assignment_type = 'E'
2371 AND effective_start_date <= p_effort_end
2372 AND effective_end_date >= p_effort_start
2373 AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
2374 AND prtd.request_id = p_request_id
2375 AND paf.person_id = pspt.person_id
2376 AND paf.assignment_status_type_id = past.assignment_status_type_id
2377 AND ( past.per_system_status <> 'TERM_ASSIGN'
2378 OR EXISTS ( select null
2379 FROM psp_pre_gen_dist_lines_history
2380 WHERE distribution_date between p_effort_start and p_effort_end
2381 AND assignment_id = paf.assignment_id
2382 AND reversal_entry_flag IS NULL
2383 AND rownum=1 )
2384 OR EXISTS (SELECT null
2385 FROM psp_distribution_lines_history pdlh
2386 , psp_summary_lines psl
2387 WHERE pdlh.summary_line_id = psl.summary_line_id
2388 AND distribution_date between p_effort_start and p_effort_end
2389 AND psl.person_id = paf.person_id
2390 AND psl.assignment_id = paf.assignment_id
2391 AND reversal_entry_flag IS NULL
2392 AND rownum=1)));
2393
2394 elsif template_sel_criteria.array_sel_criteria(i) ='POS' THEN
2395
2396 DELETE FROM psp_selected_persons_t pspt
2397 WHERE request_id = p_request_id
2398 /* Bug 5087294 : Performance fix replacing not in with not exists */
2399 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2400 AND NOT EXISTS ( SELECT 1
2401 FROM per_assignments_f paf ,
2402 psp_report_template_details_h prtd,
2403 per_assignment_status_types past
2404 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2405 AND criteria_lookup_code='POS'
2406 AND paf.assignment_type = 'E'
2407 and
2408 effective_start_date <= p_effort_end and
2409 effective_end_date >= p_effort_start
2410 AND include_exclude_flag='I'
2411 AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
2412 AND prtd.request_id = p_request_id
2413 AND paf.person_id = pspt.person_id
2414 AND paf.assignment_status_type_id = past.assignment_status_type_id
2415 AND ( past.per_system_status <> 'TERM_ASSIGN'
2416 OR EXISTS ( select null
2417 FROM psp_pre_gen_dist_lines_history
2418 WHERE distribution_date between p_effort_start and p_effort_end
2419 AND assignment_id = paf.assignment_id
2420 AND reversal_entry_flag IS NULL
2421 AND rownum=1 )
2422 OR EXISTS (SELECT null
2423 FROM psp_distribution_lines_history pdlh
2424 , psp_summary_lines psl
2425 WHERE pdlh.summary_line_id = psl.summary_line_id
2426 AND distribution_date between p_effort_start and p_effort_end
2427 AND psl.person_id = paf.person_id
2428 AND psl.assignment_id = paf.assignment_id
2429 AND reversal_entry_flag IS NULL
2430 AND rownum=1)));
2431
2432 elsif template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
2433
2434
2435 DELETE FROM psp_selected_persons_t pspt
2436 WHERE request_id = p_request_id
2437 /* Bug 5087294 : Performance fix replacing not in with not exists */
2438 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2439 AND NOT EXISTS ( SELECT 1
2440 FROM per_assignments_f paf ,
2441 psp_report_template_details_h prtd,
2442 per_assignment_status_types past
2443 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2444 AND criteria_lookup_code='ASS'
2445 AND include_exclude_flag='I'
2446 AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
2447 AND prtd.request_id = p_request_id
2448 AND paf.assignment_type = 'E'
2449 AND effective_start_date <= p_effort_end
2450 AND effective_end_date >= p_effort_start
2451 AND paf.person_id = pspt.person_id
2452 AND paf.assignment_status_type_id = past.assignment_status_type_id
2453 AND ( past.per_system_status <> 'TERM_ASSIGN'
2454 OR EXISTS ( select null
2455 FROM psp_pre_gen_dist_lines_history
2456 WHERE distribution_date between p_effort_start and p_effort_end
2457 AND assignment_id = paf.assignment_id
2458 AND reversal_entry_flag IS NULL
2459 AND rownum=1 )
2460 OR EXISTS (SELECT null
2461 FROM psp_distribution_lines_history pdlh
2462 , psp_summary_lines psl
2463 WHERE pdlh.summary_line_id = psl.summary_line_id
2464 AND distribution_date between p_effort_start and p_effort_end
2465 AND psl.person_id = paf.person_id
2466 AND psl.assignment_id = paf.assignment_id
2467 AND reversal_entry_flag IS NULL
2468 AND rownum=1)));
2469
2470 /*
2471 elsif template_rec.array_sel_criteria(i)='EST' then
2472
2473 */
2474
2475 elsif template_sel_criteria.array_sel_criteria(i)='PPG' THEN
2476
2477 OPEN ppg_cursor;
2478
2479 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
2480 IF l_criteria_value1 is not null then
2481
2482
2483 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
2484
2485 -- g_exec_string:= l_sql_string;
2486
2487 LOOP
2488
2489 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
2490 EXIT WHEN PPG_CURSOR%NOTFOUND;
2491
2492 /*
2493 select l_sql_string
2494 || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 ||''''
2495 into g_exec_string from psp_report_template_details_h ;
2496
2497
2498 */
2499
2500
2501 g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 || ' = ' || '''' || l_criteria_value2||'''';
2502
2503
2504 l_sql_string:= g_exec_string;
2505
2506 END LOOP;
2507
2508 close ppg_cursor;
2509
2510
2511 if l_sql_string is not null then
2512 --- replaced non-performant delete stmnt for 4429787
2513
2514 g_exec_string := 'delete from psp_selected_persons_t sel where request_id = :request_id
2515 AND NOT EXISTS
2516 (SELECT 1 FROM per_assignments_f paf, pay_people_groups ppg, per_assignment_status_types past
2517 WHERE paf.people_group_id= ppg.people_group_id
2518 AND paf.assignment_type = ''' || 'E' || '''
2519 AND paf.effective_end_date >= :p_effort_Start AND
2520 paf.effective_start_date <= :p_effort_end
2521 AND (' || l_sql_string || ')
2522 AND paf.person_id = sel.person_id
2523 AND paf.assignment_status_type_id = past.assignment_status_type_id
2524 AND ( past.per_system_status <> ''' || 'TERM_ASSIGN' || '''
2525 OR EXISTS ( select null
2526 FROM psp_pre_gen_dist_lines_history
2527 WHERE distribution_date between :p_effort_start and :p_effort_end
2528 AND assignment_id = paf.assignment_id
2529 AND reversal_entry_flag IS NULL
2530 AND rownum=1 )
2531 OR EXISTS (SELECT null
2532 FROM psp_distribution_lines_history pdlh
2533 , psp_summary_lines psl
2534 WHERE pdlh.summary_line_id = psl.summary_line_id
2535 AND distribution_date between :p_effort_start and :p_effort_end
2536 AND psl.person_id = paf.person_id
2537 AND psl.assignment_id = paf.assignment_id
2538 AND reversal_entry_flag IS NULL
2539 AND rownum=1)))';
2540
2541
2542 --fnd_file.put_line(fnd_file.log , ' g_exec_string 3 is '||g_exec_string);
2543
2544 EXECUTE IMMEDIATE g_exec_string USING IN p_request_id, p_Effort_start, p_effort_end, p_Effort_start, p_effort_end, p_Effort_start, p_effort_end;
2545 end if;
2546 end if;
2547
2548 elsif template_sel_criteria.array_sel_criteria(i)='GLA' then
2549
2550
2551 OPEN gla_cursor;
2552
2553 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
2554 IF l_criteria_value1 is not null then
2555
2556 l_sql_string := l_criteria_value1 ||' between '|| ''''|| l_criteria_value2 || '''' || ' and ' || '''' || l_criteria_value3 || '''';
2557 -- g_exec_string := l_sql_string;
2558
2559
2560 LOOP
2561
2562 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
2563 EXIT WHEN GLA_CURSOR%NOTFOUND;
2564
2565 g_exec_string:= l_sql_string
2566 || ' OR ' || l_criteria_value1 || ' between ' || ''''|| l_criteria_value2 || '''' ||
2567 ' and ' || ''''|| l_criteria_value3 || '''' ;
2568 l_sql_string:=g_exec_string;
2569
2570 END LOOP;
2571 close gla_cursor;
2572
2573 -- l_sql_string:= g_exec_string;
2574
2575 if l_sql_string is not null then
2576
2577 g_exec_string := 'delete from psp_selected_persons_t where person_id
2578 not in (select nvl(psl.person_id,0) from psp_summary_lines psl,
2579 psp_distribution_lines_history pdnh,
2580 psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
2581 gl_code_combinations gcc
2582 where gcc.code_combination_id= psl.gl_code_combination_id and
2583 psl.business_group_id = '|| p_business_group_id || ' and
2584 psl.set_of_books_id = ' || p_set_of_books_id || ' and
2585 psl.summary_line_id = pdnh.summary_line_id(+) and
2586 psl.summary_line_id = ppg.summary_line_id(+) and
2587 psl.summary_line_id = palh.summary_line_id(+) and
2588 psl.status_code='||''''||'A'||''''||' and
2589 ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
2590 pdnh.distribution_date between :p_effort_start and :p_effort_end
2591 and pdnh.reversal_entry_flag is null
2592 and psl.summary_line_id = pdnh.summary_line_id
2593 and pdnh.adjustment_batch_name is null
2594 ) OR
2595 ( psl.source_type='||''''||'P'||''''||' and
2596 ppg.distribution_date between :p_effort_start and :p_effort_end and
2597 ppg.adjustment_batch_name is null and
2598 ppg.summary_line_id =psl.summary_line_id and
2599 ppg.reversal_entry_flag is null)
2600 OR (psl.source_type='||''''||'A'||''''||' and
2601 palh.summary_line_id =psl.summary_line_id and
2602 palh.adjustment_batch_name is null and
2603 NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
2604 || ' and palh.distribution_date between :p_effort_start and :p_effort_end )) and
2605 gcc.code_combination_id= psl.gl_code_combination_id and
2606 gcc.code_combination_id in (select code_combination_id from gl_code_combinations
2607 where ' || l_sql_string
2608 || ' )) and request_id = :request_id';
2609
2610 -- fnd_file.put_line(fnd_file.log , ' g_exec_string 3 is '||g_exec_string);
2611
2612 execute immediate g_exec_string using IN p_effort_start, p_effort_end , p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_request_id;
2613
2614 end if;
2615 end if;
2616
2617
2618 end if;
2619
2620
2621 END IF;
2622
2623 END IF;
2624 END LOOP;
2625
2626 END;
2627
2628
2629 PROCEDURE APPLY_EXCLUSION_CRITERIA(P_REQUEST_ID IN NUMBER, P_EFFORT_START IN DATE , P_EFFORT_END IN DATE, P_BUSINESS_GROUP_ID IN NUMBER, P_SET_OF_BOOKS_ID IN NUMBER) IS
2630
2631 CURSOR PPG_CURSOR IS
2632 select criteria_value1, criteria_value2 from
2633 psp_report_template_details_h where request_id = p_request_id and
2634 criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG'
2635 and include_exclude_flag='E';
2636
2637
2638 CURSOR GLA_CURSOR IS
2639 select criteria_value1 , criteria_value2, criteria_value3 from
2640 psp_report_template_details_h where request_id = p_request_id and
2641 criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA'
2642 and include_exclude_flag='E';
2643
2644
2645 type t_varchar_30_type is TABLE Of VARCHAR2(30) INDEX BY BINARY_INTEGER;
2646 type t_varchar_1_type is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
2647
2648 l_criteria_value1 varchar2(30);
2649
2650 l_criteria_value2 varchar2(30);
2651
2652
2653 l_criteria_value3 varchar2(30);
2654
2655 l_sql_string varchar2(1000);
2656
2657
2658 i number;
2659
2660 BEGIN
2661 for i in 1..template_sel_criteria.array_sel_criteria.count
2662
2663 LOOP
2664
2665 IF template_sel_criteria.array_inc_exc_flag(i) = 'E' THEN
2666
2667
2668 -- IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code THEN
2669
2670 IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
2671
2672 DELETE FROM psp_selected_persons_t
2673 WHERE request_id = p_request_id
2674 AND person_id IN (SELECT ppf.person_id
2675 FROM per_people_f ppf,
2676 psp_report_template_details_h prtd,
2677 per_assignments_f paf,
2678 per_assignment_status_types past
2679 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2680 AND criteria_lookup_code='PTY'
2681 AND paf.person_id = ppf.person_id
2682 AND paf.assignment_type = 'E'
2683 AND paf.effective_start_date <= p_effort_end
2684 AND paf.effective_end_date >= p_effort_start
2685 AND ppf.effective_start_date <= p_effort_end
2686 AND ppf.effective_end_date >= p_effort_start
2687 AND include_exclude_flag='E'
2688 AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
2689 AND prtd.request_id = p_request_id
2690 AND paf.assignment_status_type_id = past.assignment_status_type_id
2691 AND ( past.per_system_status <> 'TERM_ASSIGN'
2692 OR EXISTS ( select null
2693 FROM psp_pre_gen_dist_lines_history
2694 WHERE distribution_date between p_effort_start and p_effort_end
2695 AND assignment_id = paf.assignment_id
2696 AND reversal_entry_flag IS NULL
2697 AND rownum=1 )
2698 OR EXISTS (SELECT null
2699 FROM psp_distribution_lines_history pdlh
2700 , psp_summary_lines psl
2701 WHERE pdlh.summary_line_id = psl.summary_line_id
2702 AND distribution_date between p_effort_start and p_effort_end
2703 AND psl.person_id = paf.person_id
2704 AND psl.assignment_id = paf.assignment_id
2705 AND reversal_entry_flag IS NULL
2706 AND rownum=1)));
2707
2708
2709 ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
2710
2711 DELETE FROM psp_selected_persons_t
2712 WHERE request_id = p_request_id
2713 AND person_id IN (SELECT DISTINCT ppf.person_id
2714 FROM per_all_people_f ppf,
2715 per_assignments_f paf,
2716 per_assignment_status_types past
2717 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
2718 FROM psp_report_template_details_h prtd
2719 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
2720 AND criteria_lookup_code='EMP'
2721 AND include_exclude_flag='E'
2722 AND prtd.request_id = p_request_id)
2723 AND paf.person_id = ppf.person_id
2724 AND paf.assignment_type = 'E'
2725 AND paf.effective_start_date <= p_effort_end
2726 AND paf.effective_end_date >= p_effort_start
2727 AND ppf.effective_start_date <= p_effort_end
2728 AND ppf.effective_end_date >= p_effort_start
2729 AND paf.assignment_status_type_id = past.assignment_status_type_id
2730 AND ( past.per_system_status <> 'TERM_ASSIGN'
2731 OR EXISTS ( select null
2732 FROM psp_pre_gen_dist_lines_history
2733 WHERE distribution_date between p_effort_start and p_effort_end
2734 AND assignment_id = paf.assignment_id
2735 AND reversal_entry_flag IS NULL
2736 AND rownum=1 )
2737 OR EXISTS (SELECT null
2738 FROM psp_distribution_lines_history pdlh
2739 , psp_summary_lines psl
2740 WHERE pdlh.summary_line_id = psl.summary_line_id
2741 AND distribution_date between p_effort_start and p_effort_end
2742 AND psl.person_id = paf.person_id
2743 AND psl.assignment_id = paf.assignment_id
2744 AND reversal_entry_flag IS NULL
2745 AND rownum=1)));
2746
2747
2748 ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
2749
2750 DELETE FROM psp_selected_persons_t
2751 WHERE request_id = p_request_id
2752 AND person_id IN (SELECT NVL(person_id,0)
2753 FROM per_all_assignments_f paf,
2754 psp_report_template_details_h prtd,
2755 per_assignment_status_types past
2756 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2757 AND criteria_lookup_code='SUP'
2758 AND include_exclude_flag='E'
2759 AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
2760 AND prtd.request_id = p_request_id
2761 AND paf.assignment_type = 'E'
2762 AND paf.effective_start_date <= p_effort_end
2763 AND paf.effective_end_date >= p_effort_start
2764 AND paf.assignment_status_type_id = past.assignment_status_type_id
2765 AND ( past.per_system_status <> 'TERM_ASSIGN'
2766 OR EXISTS ( select null
2767 FROM psp_pre_gen_dist_lines_history
2768 WHERE distribution_date between p_effort_start and p_effort_end
2769 AND assignment_id = paf.assignment_id
2770 AND reversal_entry_flag IS NULL
2771 AND rownum=1 )
2772 OR EXISTS (SELECT null
2773 FROM psp_distribution_lines_history pdlh
2774 , psp_summary_lines psl
2775 WHERE pdlh.summary_line_id = psl.summary_line_id
2776 AND distribution_date between p_effort_start and p_effort_end
2777 AND psl.person_id = paf.person_id
2778 AND psl.assignment_id = paf.assignment_id
2779 AND reversal_entry_flag IS NULL
2780 AND rownum=1)));
2781
2782
2783 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
2784
2785 --- replaced original query for performance issues -- 4429787
2786 delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2787 select psl.person_id
2788 FROM psp_summary_lines psl,
2789 psp_report_template_details_h prtd ,
2790 per_time_periods ptp
2791 WHERE
2792 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2793 prtd.criteria_lookup_code='AWD' AND
2794 prtd.include_exclude_flag='E' AND
2795 prtd.request_id =p_request_id AND
2796 psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
2797 psl.business_group_id = p_business_group_id AND
2798 psl.set_of_books_id = p_set_of_books_id AND
2799 psl.status_code= 'A' AND
2800 ptp.time_period_id = psl.time_period_id AND
2801 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2802 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2803 WHERE pdnh.summary_line_id = psl.summary_line_id
2804 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2805 AND pdnh.reversal_entry_flag IS NULL
2806 AND pdnh.adjustment_batch_name IS NULL )
2807 OR EXISTS
2808 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2809 WHERE ppg.summary_line_id = psl.summary_line_id
2810 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2811 AND ppg.adjustment_batch_name IS NULL AND
2812 ppg.reversal_entry_flag IS NULL)
2813 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2814 WHERE palh.summary_line_id = psl.summary_line_id
2815 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2816 AND palh.adjustment_batch_name IS NULL
2817 AND palh.reversal_entry_flag IS NULL
2818 AND NVL(palh.original_line_flag, 'N') ='N')));
2819
2820 ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
2821
2822 --- replaced non-performant delete with this for 4429787
2823 DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2824 SELECT NVL(psl.person_id,0)
2825 FROM psp_summary_lines psl,
2826 psp_report_template_details_h prtd ,
2827 gms_awards_all gaa ,
2828 per_time_periods ptp
2829 WHERE psl.award_id = gaa.award_id AND
2830 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2831 prtd.criteria_lookup_code='ATY' AND
2832 prtd.include_exclude_flag='E' AND
2833 prtd.request_id =p_request_id AND
2834 gaa.type=prtd.criteria_value1 AND
2835 psl.business_group_id = p_business_group_id AND
2836 psl.set_of_books_id = p_set_of_books_id AND
2837 psl.status_code= 'A' AND
2838 ptp.time_period_id = psl.time_period_id AND
2839 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2840 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2841 WHERE pdnh.summary_line_id = psl.summary_line_id
2842 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2843 AND pdnh.reversal_entry_flag IS NULL
2844 AND pdnh.adjustment_batch_name IS NULL )
2845 OR EXISTS
2846 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2847 WHERE ppg.summary_line_id = psl.summary_line_id
2848 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2849 AND ppg.adjustment_batch_name IS NULL AND
2850 ppg.reversal_entry_flag IS NULL)
2851 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2852 WHERE palh.summary_line_id = psl.summary_line_id
2853 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2854 AND palh.adjustment_batch_name IS NULL
2855 AND palh.reversal_entry_flag IS NULL AND
2856 NVL(palh.original_line_flag, 'N') ='N')));
2857
2858 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
2859
2860 --- replaced non-performant delete with this for 4429787
2861 DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2862 SELECT NVL(psl.person_id,0)
2863 FROM psp_summary_lines psl,
2864 psp_report_template_details_h prtd ,
2865 pa_projects_all ppa ,
2866 per_time_periods ptp
2867 WHERE psl.project_id = ppa.project_id AND
2868 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2869 prtd.criteria_lookup_code='PRT' AND
2870 prtd.include_exclude_flag='E' AND
2871 prtd.request_id =p_request_id AND
2872 ppa.project_type=prtd.criteria_value1 AND
2873 psl.business_group_id = p_business_group_id AND
2874 psl.set_of_books_id = p_set_of_books_id AND
2875 psl.status_code= 'A' AND
2876 ptp.time_period_id = psl.time_period_id AND
2877 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2878 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2879 WHERE pdnh.summary_line_id = psl.summary_line_id
2880 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2881 AND pdnh.reversal_entry_flag IS NULL
2882 AND pdnh.adjustment_batch_name IS NULL )
2883 OR EXISTS
2884 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2885 WHERE ppg.summary_line_id = psl.summary_line_id
2886 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2887 AND ppg.adjustment_batch_name IS NULL AND
2888 ppg.reversal_entry_flag IS NULL)
2889 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2890 WHERE palh.summary_line_id = psl.summary_line_id
2891 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2892 AND palh.adjustment_batch_name IS NULL
2893 AND palh.reversal_entry_flag IS NULL AND
2894 NVL(palh.original_line_flag, 'N') ='N')));
2895
2896 elsif template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
2897
2898 --- replaced non-performant insert with this for 4429787
2899 delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2900 select nvl( psl.person_id ,0)
2901 FROM psp_summary_lines psl,
2902 psp_report_template_details_h prtd ,
2903 per_time_periods ptp
2904 WHERE
2905 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2906 prtd.criteria_lookup_code='PRJ' AND
2907 prtd.include_exclude_flag='E' AND
2908 prtd.request_id =p_request_id AND
2909 psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
2910 psl.business_group_id = p_business_group_id AND
2911 psl.set_of_books_id = p_set_of_books_id AND
2912 psl.status_code= 'A' AND
2913 ptp.time_period_id = psl.time_period_id AND
2914 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2915 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2916 WHERE pdnh.summary_line_id = psl.summary_line_id
2917 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2918 AND pdnh.reversal_entry_flag IS NULL
2919 AND pdnh.adjustment_batch_name IS NULL )
2920 OR EXISTS
2921 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2922 WHERE ppg.summary_line_id = psl.summary_line_id
2923 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2924 AND ppg.adjustment_batch_name IS NULL AND
2925 ppg.reversal_entry_flag IS NULL)
2926 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2927 WHERE palh.summary_line_id = psl.summary_line_id
2928 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2929 AND palh.adjustment_batch_name IS NULL
2930 AND palh.reversal_entry_flag IS NULL AND
2931 NVL(palh.original_line_flag, 'N') ='N')));
2932
2933
2934 elsif template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
2935
2936 DELETE FROM psp_selected_persons_t
2937 WHERE request_id = p_request_id
2938 AND person_id IN (SELECT person_id
2939 FROM per_assignments_f paf,
2940 psp_report_template_details_h prtd,
2941 per_assignment_status_types past
2942 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2943 AND criteria_lookup_code='PAY'
2944 AND include_exclude_flag='E'
2945 AND paf.assignment_type = 'E'
2946 AND effective_start_date <= p_effort_end
2947 AND effective_end_date >= p_effort_start
2948 AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
2949 AND prtd.request_id = p_request_id
2950 AND paf.assignment_status_type_id = past.assignment_status_type_id
2951 AND ( past.per_system_status <> 'TERM_ASSIGN'
2952 OR EXISTS ( select null
2953 FROM psp_pre_gen_dist_lines_history
2954 WHERE distribution_date between p_effort_start and p_effort_end
2955 AND assignment_id = paf.assignment_id
2956 AND reversal_entry_flag IS NULL
2957 AND rownum=1 )
2958 OR EXISTS (SELECT null
2959 FROM psp_distribution_lines_history pdlh
2960 , psp_summary_lines psl
2961 WHERE pdlh.summary_line_id = psl.summary_line_id
2962 AND distribution_date between p_effort_start and p_effort_end
2963 AND psl.person_id = paf.person_id
2964 AND psl.assignment_id = paf.assignment_id
2965 AND reversal_entry_flag IS NULL
2966 AND rownum=1)));
2967
2968
2969 elsif template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
2970
2971 DELETE FROM psp_selected_persons_t
2972 WHERE request_id = p_request_id
2973 AND person_id IN (SELECT person_id
2974 FROM per_assignments_f paf,
2975 psp_report_template_details_h prtd,
2976 per_assignment_status_types past
2977 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2978 AND criteria_lookup_code='LOC'
2979 AND include_exclude_flag='E'
2980 AND paf.assignment_type = 'E'
2981 AND effective_start_date <= p_effort_end
2982 AND effective_end_date >= p_effort_start
2983 AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
2984 AND prtd.request_id = p_request_id
2985 AND paf.assignment_status_type_id = past.assignment_status_type_id
2986 AND ( past.per_system_status <> 'TERM_ASSIGN'
2987 OR EXISTS ( select null
2988 FROM psp_pre_gen_dist_lines_history
2989 WHERE distribution_date between p_effort_start and p_effort_end
2990 AND assignment_id = paf.assignment_id
2991 AND reversal_entry_flag IS NULL
2992 AND rownum=1 )
2993 OR EXISTS (SELECT null
2994 FROM psp_distribution_lines_history pdlh
2995 , psp_summary_lines psl
2996 WHERE pdlh.summary_line_id = psl.summary_line_id
2997 AND distribution_date between p_effort_start and p_effort_end
2998 AND psl.person_id = paf.person_id
2999 AND psl.assignment_id = paf.assignment_id
3000 AND reversal_entry_flag IS NULL
3001 AND rownum=1)));
3002
3003
3004 elsif template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
3005
3006 DELETE FROM psp_selected_persons_t
3007 WHERE request_id = p_request_id
3008 AND person_id IN (SELECT person_id
3009 FROM per_assignments_f paf,
3010 psp_report_template_details_h prtd,
3011 per_assignment_status_types past
3012 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3013 AND criteria_lookup_code='ORG'
3014 AND include_exclude_flag='E'
3015 AND paf.assignment_type = 'E'
3016 AND effective_start_date <= p_effort_end
3017 AND effective_end_date >= p_effort_start
3018 AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
3019 AND prtd.request_id = p_request_id
3020 AND paf.assignment_status_type_id = past.assignment_status_type_id
3021 AND ( past.per_system_status <> 'TERM_ASSIGN'
3022 OR EXISTS ( select null
3023 FROM psp_pre_gen_dist_lines_history
3024 WHERE distribution_date between p_effort_start and p_effort_end
3025 AND assignment_id = paf.assignment_id
3026 AND reversal_entry_flag IS NULL
3027 AND rownum=1 )
3028 OR EXISTS (SELECT null
3029 FROM psp_distribution_lines_history pdlh
3030 , psp_summary_lines psl
3031 WHERE pdlh.summary_line_id = psl.summary_line_id
3032 AND distribution_date between p_effort_start and p_effort_end
3033 AND psl.person_id = paf.person_id
3034 AND psl.assignment_id = paf.assignment_id
3035 AND reversal_entry_flag IS NULL
3036 AND rownum=1)));
3037
3038 /*
3039
3040 elsif template_rec.array_sel_criteria(i)='CST' then
3041 */
3042
3043 elsif template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
3044
3045 DELETE FROM psp_selected_persons_t
3046 WHERE request_id = p_request_id
3047 AND person_id IN (SELECT person_id
3048 FROM per_assignments_f paf,
3049 psp_report_template_details_h prtd,
3050 per_assignment_status_types past
3051 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3052 AND criteria_lookup_code='JOB'
3053 AND include_exclude_flag='E'
3054 AND paf.assignment_type = 'E'
3055 AND effective_start_date <= p_effort_end
3056 AND effective_end_date >= p_effort_start
3057 AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
3058 AND prtd.request_id = p_request_id
3059 AND paf.assignment_status_type_id = past.assignment_status_type_id
3060 AND ( past.per_system_status <> 'TERM_ASSIGN'
3061 OR EXISTS ( select null
3062 FROM psp_pre_gen_dist_lines_history
3063 WHERE distribution_date between p_effort_start and p_effort_end
3064 AND assignment_id = paf.assignment_id
3065 AND reversal_entry_flag IS NULL
3066 AND rownum=1 )
3067 OR EXISTS (SELECT null
3068 FROM psp_distribution_lines_history pdlh
3069 , psp_summary_lines psl
3070 WHERE pdlh.summary_line_id = psl.summary_line_id
3071 AND distribution_date between p_effort_start and p_effort_end
3072 AND psl.person_id = paf.person_id
3073 AND psl.assignment_id = paf.assignment_id
3074 AND reversal_entry_flag IS NULL
3075 AND rownum=1)));
3076
3077
3078 elsif template_sel_criteria.array_sel_criteria(i) ='POS' THEN
3079
3080 DELETE FROM psp_selected_persons_t
3081 WHERE request_id = p_request_id
3082 AND person_id IN (SELECT person_id
3083 FROM per_assignments_f paf,
3084 psp_report_template_details_h prtd,
3085 per_assignment_status_types past
3086 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3087 AND criteria_lookup_code='POS'
3088 AND include_exclude_flag='E'
3089 AND paf.assignment_type = 'E'
3090 AND effective_start_date <= p_effort_end
3091 AND effective_end_date >= p_effort_start
3092 AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
3093 AND prtd.request_id = p_request_id
3094 AND paf.assignment_status_type_id = past.assignment_status_type_id
3095 AND ( past.per_system_status <> 'TERM_ASSIGN'
3096 OR EXISTS ( select null
3097 FROM psp_pre_gen_dist_lines_history
3098 WHERE distribution_date between p_effort_start and p_effort_end
3099 AND assignment_id = paf.assignment_id
3100 AND reversal_entry_flag IS NULL
3101 AND rownum=1 )
3102 OR EXISTS (SELECT null
3103 FROM psp_distribution_lines_history pdlh
3104 , psp_summary_lines psl
3105 WHERE pdlh.summary_line_id = psl.summary_line_id
3106 AND distribution_date between p_effort_start and p_effort_end
3107 AND psl.person_id = paf.person_id
3108 AND psl.assignment_id = paf.assignment_id
3109 AND reversal_entry_flag IS NULL
3110 AND rownum=1)));
3111
3112 elsif template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
3113
3114 DELETE FROM psp_selected_persons_t
3115 WHERE request_id = p_request_id
3116 AND person_id IN (SELECT person_id
3117 FROM per_assignments_f paf,
3118 psp_report_template_details_h prtd,
3119 per_assignment_status_types past
3120 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3121 AND criteria_lookup_code='ASS'
3122 AND include_exclude_flag='E'
3123 AND paf.assignment_type = 'E'
3124 AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
3125 AND prtd.request_id = p_request_id
3126 AND effective_start_date <= p_effort_end
3127 AND effective_end_date >= p_effort_start
3128 AND paf.assignment_status_type_id = past.assignment_status_type_id
3129 AND ( past.per_system_status <> 'TERM_ASSIGN'
3130 OR EXISTS ( select null
3131 FROM psp_pre_gen_dist_lines_history
3132 WHERE distribution_date between p_effort_start and p_effort_end
3133 AND assignment_id = paf.assignment_id
3134 AND reversal_entry_flag IS NULL
3135 AND rownum=1 )
3136 OR EXISTS (SELECT null
3137 FROM psp_distribution_lines_history pdlh
3138 , psp_summary_lines psl
3139 WHERE pdlh.summary_line_id = psl.summary_line_id
3140 AND distribution_date between p_effort_start and p_effort_end
3141 AND psl.person_id = paf.person_id
3142 AND psl.assignment_id = paf.assignment_id
3143 AND reversal_entry_flag IS NULL
3144 AND rownum=1)));
3145
3146
3147 elsif template_sel_criteria.array_sel_criteria(i)='CST' THEN
3148
3149 DELETE FROM psp_selected_persons_t
3150 WHERE request_id = p_request_id
3151 AND person_id IN (SELECT NVL(person_id,0)
3152 FROM per_assignments_f paf,
3153 pay_payrolls_f ppf,
3154 per_assignment_status_types past
3155 WHERE ppf.payroll_id = paf.payroll_id
3156 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3157 FROM psp_report_template_details_h prtd
3158 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3159 AND criteria_lookup_code='CST'
3160 AND include_exclude_flag='E'
3161 AND request_id = p_request_id)
3162 AND paf.assignment_type = 'E'
3163 AND ppf.effective_start_date <= p_effort_end
3164 AND ppf.effective_end_date >= p_effort_start
3165 AND paf.effective_start_date <= p_effort_end
3166 AND paf.effective_end_date >= p_effort_start
3167 AND paf.assignment_status_type_id = past.assignment_status_type_id
3168 AND ( past.per_system_status <> 'TERM_ASSIGN'
3169 OR EXISTS ( select null
3170 FROM psp_pre_gen_dist_lines_history
3171 WHERE distribution_date between p_effort_start and p_effort_end
3172 AND assignment_id = paf.assignment_id
3173 AND reversal_entry_flag IS NULL
3174 AND rownum=1 )
3175 OR EXISTS (SELECT null
3176 FROM psp_distribution_lines_history pdlh
3177 , psp_summary_lines psl
3178 WHERE pdlh.summary_line_id = psl.summary_line_id
3179 AND distribution_date between p_effort_start and p_effort_end
3180 AND psl.person_id = paf.person_id
3181 AND psl.assignment_id = paf.assignment_id
3182 AND reversal_entry_flag IS NULL
3183 AND rownum=1)));
3184
3185
3186 elsif template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
3187
3188
3189 DELETE FROM psp_selected_persons_t
3190 WHERE request_id = p_request_id
3191 AND person_id IN (SELECT NVL(paf.person_id,0)
3192 FROM per_all_assignments_f paf,
3193 hr_assignment_sets has,
3194 hr_assignment_set_amendments hasa,
3195 per_assignment_status_types past
3196 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
3197 FROM psp_report_template_details_h prtd
3198 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3199 AND criteria_lookup_code='AST'
3200 AND include_exclude_flag='E'
3201 AND request_id =p_request_id)
3202 AND ( (paf.payroll_id = has.payroll_id
3203 AND paf.assignment_type = 'E'
3204 AND paf.effective_start_date <= p_effort_end
3205 AND paf.effective_end_date >= p_effort_start
3206 AND has.assignment_set_id = hasa.assignment_set_id)
3207 OR (paf.assignment_id = hasa.assignment_id
3208 AND paf.assignment_type = 'E'
3209 AND paf.effective_start_date <= p_effort_end
3210 AND paf.effective_end_date >= p_effort_start
3211 AND hasa.assignment_set_id=has.assignment_set_id
3212 AND include_or_exclude ='I'))
3213 AND NOT EXISTS (SELECT assignment_id
3214 FROM hr_assignment_set_amendments hasa
3215 WHERE hasa.assignment_id = paf.assignment_id
3216 AND paf.assignment_type = 'E'
3217 AND hasa.include_or_exclude ='E'
3218 AND paf.effective_start_date <= p_effort_end
3219 AND paf.effective_end_date >= p_effort_start)
3220 AND paf.assignment_status_type_id = past.assignment_status_type_id
3221 AND ( past.per_system_status <> 'TERM_ASSIGN'
3222 OR EXISTS ( select null
3223 FROM psp_pre_gen_dist_lines_history
3224 WHERE distribution_date between p_effort_start and p_effort_end
3225 AND assignment_id = paf.assignment_id
3226 AND reversal_entry_flag IS NULL
3227 AND rownum=1 )
3228 OR EXISTS (SELECT null
3229 FROM psp_distribution_lines_history pdlh
3230 , psp_summary_lines psl
3231 WHERE pdlh.summary_line_id = psl.summary_line_id
3232 AND distribution_date between p_effort_start and p_effort_end
3233 AND psl.person_id = paf.person_id
3234 AND psl.assignment_id = paf.assignment_id
3235 AND reversal_entry_flag IS NULL
3236 AND rownum=1)));
3237
3238
3239
3240
3241
3242
3243 elsif template_sel_criteria.array_sel_criteria(i)='PPG' THEN
3244
3245 OPEN ppg_cursor;
3246
3247 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
3248 IF l_criteria_value1 is not null then
3249
3250 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 || '''' ;
3251 --g_exec_string := l_sql_string;
3252
3253
3254 LOOP
3255
3256 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
3257 EXIT WHEN PPG_CURSOR%NOTFOUND;
3258
3259 g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 || ' = ' || '''' || l_criteria_value2||'''';
3260 /*
3261 select l_sql_string
3262 || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 || ''''
3263 into g_exec_string from psp_report_template_details_h ;
3264
3265 */
3266 l_sql_string:= g_exec_string;
3267
3268 END LOOP;
3269
3270
3271 IF l_sql_string is not null then
3272
3273 g_exec_string := 'delete from psp_selected_persons_t where person_id
3274 in (select person_id from per_assignments_f paf,
3275 pay_people_groups ppg , per_assignment_status_types past
3276 where paf.people_group_id= ppg.people_group_id
3277 AND paf.assignment_type = ''' || 'E' || '''
3278 and paf.effective_end_date >= :p_effort_Start and
3279 paf.effective_start_date <= :p_effort_end
3280 and
3281 ppg.people_group_id in (select people_group_id from pay_people_groups
3282 where ' || l_sql_string
3283 || ' )
3284 AND paf.assignment_status_type_id = past.assignment_status_type_id
3285 AND ( past.per_system_status <> ''' || 'TERM_ASSIGN' || '''
3286 OR EXISTS ( select null
3287 FROM psp_pre_gen_dist_lines_history
3288 WHERE distribution_date between :p_effort_start and :p_effort_end
3289 AND assignment_id = paf.assignment_id
3290 AND reversal_entry_flag IS NULL
3291 AND rownum=1 )
3292 OR EXISTS (SELECT null
3293 FROM psp_distribution_lines_history pdlh
3294 , psp_summary_lines psl
3295 WHERE pdlh.summary_line_id = psl.summary_line_id
3296 AND distribution_date between :p_effort_start and :p_effort_end
3297 AND psl.person_id = paf.person_id
3298 AND psl.assignment_id = paf.assignment_id
3299 AND reversal_entry_flag IS NULL
3300 AND rownum=1))
3301 ) and request_id = :request_id';
3302
3303
3304 --fnd_file.put_line(fnd_file.log, 'ppg check is '||g_exec_string);
3305
3306 execute immediate g_exec_string using in p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_request_id;
3307
3308 END IF;
3309
3310 END IF;
3311
3312 ELSIF template_sel_criteria.array_sel_criteria(i)='GLA' THEN
3313
3314 OPEN gla_cursor;
3315
3316 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
3317 IF l_criteria_value1 is not null then
3318
3319 l_sql_string := l_criteria_value1 ||' between '|| '''' || l_criteria_value2 || '''' || ' and ' || '''' || l_criteria_value3 || '''' ;
3320
3321
3322 LOOP
3323
3324 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
3325 EXIT WHEN GLA_CURSOR%NOTFOUND;
3326
3327 g_exec_string:= l_sql_string
3328 || ' OR ' || l_criteria_value1 || ' between ' || ''''|| l_criteria_value2 || '''' ||
3329 ' and ' || ''''|| l_criteria_value3 || '''' ;
3330
3331 l_sql_string:= g_exec_string;
3332
3333 END LOOP;
3334
3335
3336 IF l_sql_string is not null then
3337
3338 g_exec_string := 'delete from psp_selected_persons_t where person_id
3339 in (select nvl(psl.person_id,0) from psp_summary_lines psl,
3340 psp_distribution_lines_history pdnh,
3341 psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
3342 gl_code_combinations gcc
3343 where gcc.code_combination_id= psl.gl_code_combination_id and
3344 psl.business_group_id = '|| p_business_group_id || ' and
3345 psl.set_of_books_id = ' || p_set_of_books_id || ' and
3346 psl.summary_line_id = pdnh.summary_line_id(+) and
3347 psl.summary_line_id = ppg.summary_line_id(+) and
3348 psl.summary_line_id = palh.summary_line_id(+) and
3349 psl.status_code='||''''||'A'||''''||' and
3350 ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
3351 pdnh.distribution_date between :p_effort_start and :p_effort_end
3352 and pdnh.reversal_entry_flag is null
3353 and psl.summary_line_id =pdnh.summary_line_id
3354 and pdnh.adjustment_batch_name is null
3355 ) OR
3356 ( psl.source_type= '||''''||'P'||''''||' and
3357 ppg.distribution_date between :p_effort_start and :p_effort_end and
3358 ppg.adjustment_batch_name is null and
3359 ppg.summary_line_id =psl.summary_line_id and
3360 ppg.reversal_entry_flag is null)
3361 OR (psl.source_type= '||''''||'A'||''''||' and
3362 palh.summary_line_id =psl.summary_line_id and
3363 palh.adjustment_batch_name is null and
3364 NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
3365 || ' and palh.distribution_date between :p_effort_start and :p_effort_end )) and
3366 gcc.code_combination_id= psl.gl_code_combination_id and
3367 gcc.code_combination_id in (select code_combination_id from gl_code_combinations
3368 where ' || l_sql_string
3369 || ' )) and request_id = :request_id';
3370
3371
3372 --fnd_file.put_line(fnd_file.log,'g_exec_string ===='||g_exec_string);
3373
3374 EXECUTE IMMEDIATE g_exec_string using IN p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_request_id;
3375
3376 END IF;
3377
3378 END IF;
3379
3380
3381 END IF;
3382
3383
3384 -- END IF;
3385
3386 END IF;
3387
3388
3389 END LOOP;
3390
3391 END;
3392
3393
3394 PROCEDURE APPLY_FF_FORMULA_EXCLUSION(p_request_id IN NUMBER, p_effort_start IN DATE, p_effort_end IN DATE) IS
3395
3396 TYPE v_line_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
3397 TYPE v_date_id IS TABLE OF DATE INDEX BY BINARY_INTEGER;
3398
3399 TYPE ls_criteria_rec is RECORD(
3400 l_person_id v_line_id,
3401 l_assignment_id v_line_id,
3402 l_request_id v_line_id ,
3403 l_start_date v_date_id,
3404 l_end_date v_date_id
3405 );
3406
3407 r_ls_criteria_rec ls_criteria_rec;
3408
3409
3410 TYPE ff_rec is RECORD(
3411 l_formula_id v_line_id
3412 );
3413
3414 r_ff_rec ff_rec;
3415
3416 l_input1 number;
3417 l_input2 number ;
3418 l_input3 number;
3419 l_input4 number;
3420 l_results varchar2(30) ;
3421 l_inputs ff_exec.inputs_t;
3422 l_outputs ff_exec.outputs_t;
3423 l_in_cnt number;
3424 l_out_cnt number;
3425 i number;
3426 j number;
3427 k number;
3428 tot_cnt number;
3429 local_ctr number:=0;
3430 l_cnt number:=0;
3431
3432 CURSOR select_everyone_csr is select person_id , assignment_id, p_request_id, p_effort_start, p_effort_end from psp_selected_persons_t where
3433 request_id =p_request_id;
3434
3435 CURSOR get_ff_for_template_csr is select criteria_value1 from psp_report_template_details_h where
3436 request_id = p_request_id and criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='FFE';
3437
3438 BEGIN
3439
3440 OPEN select_everyone_csr;
3441
3442 Fetch select_everyone_csr BULK COLLECT into
3443 r_ls_criteria_rec.l_person_id , r_ls_criteria_rec.l_assignment_id, r_ls_criteria_rec.l_request_id, r_ls_criteria_rec.l_start_date, r_ls_criteria_rec.l_end_date;
3444
3445 CLOSE select_everyone_csr;
3446
3447
3448 OPEN get_ff_for_template_csr;
3449 /*
3450
3451 select nvl(count(1), 0) into l_cnt from fnd_sessions where session_id = userenv('session_id');
3452
3453 if l_cnt=0 then
3454
3455 INSERT into fnd_sessions(session_id, effective_date) values (userenv('sessionid'), p_effort_start);
3456 end if;
3457 */
3458
3459
3460
3461 FETCH get_ff_for_template_csr BULK COLLECT into r_ff_rec.l_formula_id;
3462
3463 CLOSE get_ff_for_template_csr;
3464
3465 FOR j in 1..r_ff_rec.l_formula_id.count
3466
3467 LOOP
3468
3469 ff_exec.init_formula(r_ff_rec.l_formula_id(j), p_effort_start, l_inputs,l_outputs);
3470 tot_cnt := r_ls_criteria_rec.l_person_id.count;
3471
3472 FOR k IN 1..tot_cnt LOOP
3473
3474 FOR l_in_cnt in l_inputs.first..l_inputs.last loop
3475
3476 IF (l_inputs(l_in_cnt).name ='PERSON_ID') THEN
3477 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(r_ls_criteria_rec.l_person_id(k));
3478 hr_utility.trace('Input 1 :PERSON_ID=' ||l_inputs(l_in_cnt).value);
3479
3480
3481 ELSIF l_inputs(l_in_cnt).name='REQUEST_ID' THEN
3482 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(r_ls_criteria_rec.l_request_id(k));
3483
3484 hr_utility.trace( ' Input 2 :REQUEST_ID = '||l_inputs(l_in_cnt).value);
3485
3486 ELSIF l_inputs(l_in_cnt).name='START_DATE' THEN
3487 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(r_ls_criteria_rec.l_start_date(k));
3488 hr_utility.trace( ' Input 3 :START_DATE = '||l_inputs(l_in_cnt).value);
3489
3490 ELSIF l_inputs(l_in_cnt).name='END_DATE' THEN
3491 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(r_ls_criteria_rec.l_end_date(k));
3492 hr_utility.trace( ' Input 4 :END_DATE= '||l_inputs(l_in_cnt).value);
3493
3494 ELSIF l_inputs(l_in_cnt).name ='ASSIGNMENT_ID' THEN
3495 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(r_ls_criteria_rec.l_assignment_id(k));
3496 hr_utility.trace('Input 5 :ASSIGNMENT_ID=' ||l_inputs(l_in_cnt).value);
3497
3498 /*
3499 Commented out the below change as input 1 is person_id and addl. input is not required
3500
3501 Added for bug 4195678 by tbalacha
3502
3503 ELSIF l_inputs(l_in_cnt).name='PERSON_ID' THEN
3504
3505 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(r_ls_criteria_rec.l_person_id(k));
3506 else
3507 l_inputs(l_in_cnt).value :=fnd_number.number_to_canonical(l_input2);
3508
3509 */
3510 END IF;
3511
3512 END LOOP;
3513
3514 ff_exec.run_formula(l_inputs, l_outputs);
3515
3516 FOR l_out_cnt in l_outputs.first..l_outputs.last
3517
3518 LOOP
3519
3520 l_results:= l_outputs(l_out_cnt).value;
3521
3522
3523 IF (l_results='FALSE' or l_results = 0 )THEN -- introduced for bug 4195678
3524 DELETE FROM psp_selected_persons_t
3525 WHERE person_id = r_ls_criteria_rec.l_person_id(k)
3526 AND assignment_id = r_ls_criteria_rec.l_assignment_id(k);
3527
3528 END IF;
3529
3530 END LOOP;
3531
3532 END LOOP;
3533
3534 END LOOP;
3535
3536 END;
3537
3538 FUNCTION get_parameter_value( name in varchar2, parameter_list varchar2) return varchar2
3539 IS
3540
3541
3542 start_ptr NUMBER;
3543 end_ptr NUMBER;
3544 token_val pay_payroll_actions.legislative_parameters%type;
3545 par_value pay_payroll_actions.legislative_parameters%type;
3546
3547 BEGIN
3548
3549 token_val := name||'=';
3550 start_ptr := instr(parameter_list, token_val) +length(token_val);
3551
3552 end_ptr :=instr(parameter_list, ' ', start_ptr);
3553
3554 IF end_ptr=0 then
3555 end_ptr:=length(parameter_list) +1;
3556
3557 END IF;
3558
3559 IF instr(parameter_list, token_val) = 0 then
3560
3561 par_value:=NULL;
3562 ELSE
3563
3564 par_value:=substr(parameter_list, start_ptr, end_ptr-start_ptr);
3565
3566
3567 end if;
3568
3569 return par_value;
3570 END get_parameter_value;
3571
3572 PROCEDURE get_asg_lowest_cardinality (p_request_id IN NUMBER,
3573 p_effort_start IN DATE,
3574 p_effort_end IN DATE,
3575 p_business_group_id IN NUMBER,
3576 p_set_of_books_id IN NUMBER) IS
3577 l_criteria_value1 VARCHAR2(30);
3578 l_criteria_value2 VARCHAR2(30);
3579 l_criteria_value3 VARCHAR2(30);
3580 l_dyn_criteria VARCHAR2(30);
3581 l_atleast_one_criteria VARCHAR2(1);
3582 l_sql_string VARCHAR2(1000);
3583
3584 CURSOR get_lowest_cardinality_csr IS
3585 SELECT lookup_code FROM
3586 psp_selection_cardinality_gt WHERE total_count > 0 ORDER BY total_count asc;
3587
3588
3589 CURSOR get_zero_cardinality_csr IS
3590 SELECT lookup_code
3591 FROM psp_selection_cardinality_gt
3592 WHERE total_count=0;
3593
3594
3595 CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER) IS
3596 SELECT DISTINCT criteria_lookup_code
3597 FROM psp_report_template_details_h
3598 WHERE request_id = p_request_id
3599 AND include_exclude_flag = 'I'
3600 AND criteria_lookup_type = 'PSP_SELECTION_CRITERIA';
3601
3602 /* The below cursors would only be used only when no statis selection criteria have been chosen */
3603 CURSOR ppg_cursor IS
3604 SELECT criteria_value1,
3605 criteria_value2
3606 FROM psp_report_template_details_h
3607 WHERE request_id = p_request_id
3608 AND include_exclude_flag='I'
3609 AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
3610 AND criteria_lookup_code='PPG';
3611
3612 CURSOR gla_cursor IS
3613 SELECT criteria_value1,
3614 criteria_value2,
3615 criteria_value3
3616 FROM psp_report_template_details_h
3617 WHERE request_id = p_request_id
3618 AND include_exclude_flag='I'
3619 AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
3620 AND criteria_lookup_code ='GLA';
3621
3622 BEGIN
3623 OPEN get_selection_cardinality_csr(p_request_id);
3624 FETCH get_selection_cardinality_csr BULK COLLECT into template_rec.array_sel_criteria;
3625 CLOSE get_selection_cardinality_csr;
3626
3627 FOR i IN 1.. template_rec.array_sel_criteria.COUNT
3628 LOOP
3629 IF template_rec.array_sel_criteria(i) = 'PTY' THEN
3630 INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
3631 SELECT 'PTY', COUNT(DISTINCT assignment_id)
3632 FROM per_people_f ppf,
3633 per_assignments_f paf
3634 WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
3635 FROM psp_report_template_details_h prtd
3636 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3637 AND criteria_lookup_code ='PTY'
3638 AND include_exclude_flag='I'
3639 AND request_id = p_request_id)
3640 AND paf.person_id = ppf.person_id
3641 AND paf.assignment_type = 'E'
3642 AND ppf.effective_start_date <= p_effort_end
3643 AND ppf.effective_end_date >= p_effort_start
3644 AND paf.effective_start_date <= p_effort_end
3645 AND paf.effective_end_date >= p_effort_start;
3646 ELSIF template_rec.array_sel_criteria(i) ='EMP' THEN
3647 INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)
3648 SELECT 'EMP', COUNT(DISTINCT paf.assignment_id)
3649 FROM per_all_assignments_f paf
3650 WHERE paf.person_id IN (SELECT TO_NUMBER(criteria_value1)
3651 FROM psp_report_template_details_h prtd
3652 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3653 AND criteria_lookup_code='EMP'
3654 AND include_exclude_flag='I'
3655 AND request_id = p_request_id)
3656 AND paf.assignment_type = 'E'
3657 AND paf.effective_start_date <= p_effort_end
3658 AND paf.effective_end_date >= p_effort_start;
3659 ELSIF template_rec.array_sel_criteria(i) ='SUP' THEN
3660 INSERT INTO psp_selection_cardinality_gt
3661 (lookup_code, total_count)
3662 SELECT 'SUP', COUNT(DISTINCT assignment_id)
3663 FROM per_all_assignments_f paf
3664 WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
3665 FROM psp_report_template_details_h prtd
3666 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3667 AND criteria_lookup_code='SUP'
3668 AND request_id = p_request_id
3669 AND include_exclude_flag='I')
3670 AND paf.assignment_type = 'E'
3671 AND effective_start_date <= p_effort_end
3672 AND effective_end_date >= p_effort_start;
3673 ELSIF template_rec.array_sel_criteria(i) ='AWD' THEN
3674 INSERT INTO psp_selection_cardinality_gt
3675 (lookup_code, total_count)
3676 SELECT 'AWD', COUNT(DISTINCT psl.assignment_id)
3677 FROM psp_summary_lines psl,
3678 psp_report_template_details_h prtd ,
3679 per_time_periods ptp
3680 WHERE
3681 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3682 AND prtd.criteria_lookup_code='AWD'
3683 AND prtd.include_exclude_flag='I'
3684 AND prtd.request_id =p_request_id
3685 and psl.award_id = TO_NUMBER(prtd.criteria_value1)
3686 AND psl.business_group_id = p_business_group_id
3687 AND psl.set_of_books_id = p_set_of_books_id
3688 AND psl.status_code= 'A'
3689 AND ptp.time_period_id = psl.time_period_id
3690 AND (ptp.start_date <= p_effort_end
3691 AND ptp.end_date >= p_effort_start)
3692 AND (EXISTS (SELECT 1
3693 FROM psp_distribution_lines_history pdnh
3694 WHERE pdnh.summary_line_id = psl.summary_line_id
3695 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3696 AND pdnh.reversal_entry_flag IS NULL
3697 AND pdnh.adjustment_batch_name IS NULL )
3698 OR EXISTS (SELECT 1
3699 FROM psp_pre_gen_dist_lines_history ppg
3700 WHERE ppg.summary_line_id = psl.summary_line_id
3701 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
3702 AND ppg.adjustment_batch_name IS NULL
3703 AND ppg.reversal_entry_flag IS NULL)
3704 OR EXISTS (SELECT 1
3705 FROM psp_adjustment_lines_history palh
3706 WHERE palh.summary_line_id = psl.summary_line_id
3707 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
3708 AND palh.adjustment_batch_name IS NULL
3709 AND palh.reversal_entry_flag IS NULL
3710 AND NVL(palh.original_line_flag, 'N') ='N'));
3711 ELSIF template_rec.array_sel_criteria(i)='ATY' THEN
3712 INSERT INTO psp_selection_cardinality_gt
3713 (lookup_code, total_count)
3714 SELECT 'ATY', COUNT(DISTINCT psl.assignment_id)
3715 FROM psp_summary_lines psl,
3716 psp_report_template_details_h prtd ,
3717 gms_awards_all gaa,
3718 per_time_periods ptp
3719 WHERE psl.award_id = gaa.award_id
3720 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3721 AND prtd.criteria_lookup_code='ATY'
3722 AND prtd.include_exclude_flag='I'
3723 AND prtd.request_id = p_request_id
3724 AND gaa.type=prtd.criteria_value1
3725 AND psl.business_group_id = p_business_group_id
3726 AND psl.set_of_books_id = p_set_of_books_id
3727 AND psl.status_code= 'A'
3728 AND ptp.time_period_id = psl.time_period_id
3729 AND (ptp.start_date <= p_effort_end
3730 AND ptp.end_date >= p_effort_start)
3731 AND (EXISTS (SELECT 1
3732 FROM psp_distribution_lines_history pdnh
3733 WHERE pdnh.summary_line_id = psl.summary_line_id
3734 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3735 AND pdnh.reversal_entry_flag IS NULL
3736 AND pdnh.adjustment_batch_name IS NULL )
3737 OR EXISTS (SELECT 1
3738 FROM psp_pre_gen_dist_lines_history ppg
3739 WHERE ppg.summary_line_id = psl.summary_line_id
3740 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
3741 AND ppg.adjustment_batch_name IS NULL
3742 AND ppg.reversal_entry_flag IS NULL)
3743 OR EXISTS (SELECT 1
3744 FROM psp_adjustment_lines_history palh
3745 WHERE palh.summary_line_id = psl.summary_line_id
3746 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
3747 AND palh.adjustment_batch_name IS NULL
3748 AND palh.reversal_entry_flag IS NULL
3749 AND NVL(palh.original_line_flag, 'N') ='N'));
3750 ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
3751 INSERT INTO psp_selection_cardinality_gt
3752 (lookup_code, total_count)
3753 SELECT 'PRT', COUNT(DISTINCT psl.assignment_id)
3754 FROM psp_summary_lines psl,
3755 psp_report_template_details_h prtd ,
3756 pa_projects_all ppa ,
3757 per_time_periods ptp
3758 WHERE psl.project_id = ppa.project_id
3759 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3760 AND prtd.criteria_lookup_code='PRT'
3761 AND prtd.include_exclude_flag='I'
3762 AND prtd.request_id =p_request_id
3763 AND ppa.project_type=prtd.criteria_value1
3764 AND psl.business_group_id = p_business_group_id
3765 AND psl.set_of_books_id = p_set_of_books_id
3766 AND psl.status_code= 'A'
3767 AND ptp.time_period_id = psl.time_period_id
3768 AND (ptp.start_date <= p_effort_end
3769 AND ptp.end_date >= p_effort_start)
3770 AND (EXISTS (SELECT 1
3771 FROM psp_distribution_lines_history pdnh
3772 WHERE pdnh.summary_line_id = psl.summary_line_id
3773 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3774 AND pdnh.reversal_entry_flag IS NULL
3775 AND pdnh.adjustment_batch_name IS NULL )
3776 OR EXISTS (SELECT 1
3777 FROM psp_pre_gen_dist_lines_history ppg
3778 WHERE ppg.summary_line_id = psl.summary_line_id
3779 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
3780 AND ppg.adjustment_batch_name IS NULL AND
3781 ppg.reversal_entry_flag IS NULL)
3782 OR EXISTS (SELECT 1
3783 FROM psp_adjustment_lines_history palh
3784 WHERE palh.summary_line_id = psl.summary_line_id
3785 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
3786 AND palh.adjustment_batch_name IS NULL
3787 AND palh.reversal_entry_flag IS NULL AND
3788 NVL(palh.original_line_flag, 'N') ='N'));
3789 ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
3790 INSERT INTO psp_selection_cardinality_gt
3791 (lookup_code, total_count)
3792 SELECT 'PRJ', COUNT(DISTINCT psl.assignment_id)
3793 FROM psp_summary_lines psl,
3794 psp_report_template_details_h prtd ,
3795 per_time_periods ptp
3796 WHERE
3797 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3798 AND prtd.criteria_lookup_code='PRJ'
3799 AND prtd.include_exclude_flag='I'
3800 AND prtd.request_id =p_request_id
3801 and psl.project_id = TO_NUMBER(prtd.criteria_value1)
3802 AND psl.business_group_id = p_business_group_id
3803 AND psl.set_of_books_id = p_set_of_books_id
3804 AND psl.status_code= 'A'
3805 AND ptp.time_period_id = psl.time_period_id
3806 AND (ptp.start_date <= p_effort_end
3807 AND ptp.end_date >= p_effort_start)
3808 AND (EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
3809 WHERE pdnh.summary_line_id = psl.summary_line_id
3810 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3811 AND pdnh.reversal_entry_flag IS NULL
3812 AND pdnh.adjustment_batch_name IS NULL )
3813 OR EXISTS (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
3814 WHERE ppg.summary_line_id = psl.summary_line_id
3815 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
3816 AND ppg.adjustment_batch_name IS NULL
3817 AND ppg.reversal_entry_flag IS NULL)
3818 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
3819 WHERE palh.summary_line_id = psl.summary_line_id
3820 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
3821 AND palh.adjustment_batch_name IS NULL
3822 AND palh.reversal_entry_flag IS NULL
3823 AND NVL(palh.original_line_flag, 'N') ='N'));
3824 ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
3825 INSERT INTO psp_selection_cardinality_gt
3826 (lookup_code, total_count)
3827 SELECT 'PAY', COUNT(DISTINCT assignment_id)
3828 FROM per_assignments_f paf
3829 WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
3830 FROM psp_report_template_details_h prtd
3831 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3832 AND criteria_lookup_code='PAY'
3833 AND include_exclude_flag='I' AND request_id = p_request_id)
3834 AND paf.assignment_type = 'E'
3835 AND effective_start_date <= p_effort_end
3836 AND effective_end_date >= p_effort_start;
3837 ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
3838 INSERT INTO psp_selection_cardinality_gt
3839 (lookup_code, total_count)
3840 SELECT 'LOC', COUNT(DISTINCT assignment_id)
3841 FROM per_assignments_f paf
3842 WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
3843 FROM psp_report_template_details_h prtd
3844 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3845 AND criteria_lookup_code='LOC'
3846 AND include_exclude_flag='I'
3847 AND request_id = p_request_id)
3848 AND paf.assignment_type = 'E'
3849 AND effective_start_date <= p_effort_end
3850 AND effective_end_date >= p_effort_start;
3851 ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
3852 INSERT INTO psp_selection_cardinality_gt
3853 (lookup_code, total_count)
3854 SELECT 'ORG', COUNT(DISTINCT assignment_id)
3855 FROM per_assignments_f paf
3856 WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
3857 FROM psp_report_template_details_h prtd
3858 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3859 AND criteria_lookup_code='ORG'
3860 AND include_exclude_flag='I'
3861 AND request_id = p_request_id)
3862 AND paf.assignment_type = 'E'
3863 AND effective_start_date <= p_effort_end
3864 AND effective_end_date >= p_effort_start;
3865 ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
3866 INSERT INTO psp_selection_cardinality_gt
3867 (lookup_code, total_count)
3868 SELECT 'JOB', COUNT(DISTINCT assignment_id)
3869 FROM per_assignments_f paf
3870 WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
3871 FROM psp_report_template_details_h prtd
3872 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3873 AND criteria_lookup_code='JOB'
3874 AND include_exclude_flag='I'
3875 AND request_id=p_request_id)
3876 AND paf.assignment_type = 'E'
3877 AND effective_start_date <= p_effort_end
3878 AND effective_end_date >= p_effort_start;
3879 ELSIF template_rec.array_sel_criteria(i)='POS' THEN
3880 INSERT INTO psp_selection_cardinality_gt
3881 (lookup_code, total_count)
3882 SELECT 'POS', COUNT(DISTINCT assignment_id)
3883 FROM per_assignments_f paf
3884 WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
3885 FROM psp_report_template_details_h prtd
3886 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3887 AND criteria_lookup_code='POS'
3888 AND include_exclude_flag='I'
3889 AND request_id = p_request_id)
3890 AND paf.assignment_type = 'E'
3891 AND effective_start_date <= p_effort_end
3892 AND effective_end_date >= p_effort_start;
3893 ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
3894 INSERT INTO psp_selection_cardinality_gt
3895 (lookup_code, total_count)
3896 SELECT 'ASS', COUNT(DISTINCT assignment_id)
3897 FROM per_assignments_f paf
3898 WHERE assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
3899 FROM psp_report_template_details_h prtd
3900 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3901 AND criteria_lookup_code='ASS'
3902 AND include_exclude_flag='I'
3903 AND request_id = p_request_id)
3904 AND paf.assignment_type = 'E'
3905 AND effective_start_date <= p_effort_end
3906 AND effective_end_date >= p_effort_start;
3907 ELSIF template_rec.array_sel_criteria(i)='CST' THEN
3908 INSERT INTO psp_selection_cardinality_gt
3909 (lookup_code, total_count)
3910 SELECT 'CST', COUNT(DISTINCT paf.assignment_id)
3911 FROM per_assignments_f paf,
3912 pay_payrolls_f ppf
3913 WHERE ppf.payroll_id = paf.payroll_id
3914 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3915 FROM psp_report_template_details_h prtd
3916 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3917 AND criteria_lookup_code='CST'
3918 AND include_exclude_flag='I'
3919 AND request_id = p_request_id)
3920 AND paf.assignment_type = 'E'
3921 AND ppf.effective_start_date <= p_effort_end
3922 AND ppf.effective_end_date >= p_effort_start
3923 AND paf.effective_start_date <= p_effort_end
3924 AND paf.effective_end_date >= p_effort_start;
3925 ELSIF template_rec.array_sel_criteria(i) = 'AST' THEN
3926 INSERT INTO psp_selection_cardinality_gt
3927 (lookup_code, total_count)
3928 SELECT 'AST', COUNT(DISTINCT paf.assignment_id)
3929 FROM per_all_assignments_f paf,
3930 hr_assignment_sets has ,
3931 hr_assignment_set_amendments hasa
3932 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
3933 FROM psp_report_template_details_h prtd
3934 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3935 AND criteria_lookup_code='AST'
3936 AND include_exclude_flag='I'
3937 AND request_id =p_request_id)
3938 AND ( (paf.payroll_id = has.payroll_id
3939 AND paf.assignment_type = 'E'
3940 AND paf.effective_start_date <= p_effort_end
3941 AND paf.effective_end_date >= p_effort_start
3942 AND has.assignment_set_id = hasa.assignment_set_id)
3943 OR (paf.assignment_id = hasa.assignment_id
3944 AND paf.assignment_type = 'E'
3945 AND paf.effective_start_date <= p_effort_end
3946 AND paf.effective_end_date >= p_effort_start
3947 AND hasa.assignment_set_id=has.assignment_set_id
3948 AND include_or_exclude ='I'))
3949 AND NOT EXISTS (SELECT assignment_id
3950 FROM hr_assignment_set_amendments hasa
3951 WHERE hasa.assignment_id = paf.assignment_id
3952 AND hasa.include_or_exclude ='E'
3953 AND paf.assignment_type = 'E'
3954 AND paf.effective_start_date <= p_effort_end
3955 AND paf.effective_end_date >= p_effort_start);
3956 ELSIF template_rec.array_sel_criteria(i)='PPG' THEN
3957 l_dyn_criteria:='PPG';
3958 ELSIF template_rec.array_sel_criteria(i)='GLA' THEN
3959 l_dyn_criteria:='GLA';
3960 END IF;
3961 END LOOP;
3962
3963 -- Next find the selection criteria with lowest cardinality. Use it to prepare the initial list.
3964
3965 OPEN get_lowest_cardinality_csr;
3966 FETCH get_lowest_cardinality_csr into g_lookup_code;
3967 CLOSE get_lowest_cardinality_csr;
3968
3969 IF g_lookup_code is not null then
3970 l_atleast_one_criteria:='Y';
3971 hr_utility.trace( 'g_lookup_code -> '||g_lookup_code );
3972 ELSE
3973 OPEN get_zero_cardinality_csr;
3974 FETCH get_zero_cardinality_csr into g_lookup_code;
3975 CLOSE get_zero_cardinality_csr;
3976
3977 hr_utility.trace( ' Inside zero cardinality => g_lookup_code= '||g_lookup_code );
3978
3979 IF g_lookup_code is not null then
3980 l_atleast_one_criteria:='Y';
3981 END IF;
3982 END IF;
3983
3984 IF g_lookup_code IS NULL then
3985 BEGIN
3986 -- When no static selection criteria have been chosen, then invoke the dynamic selection criteria
3987 hr_utility.trace( ' Inside zero cardinality => g_lookup_code IS NULL');
3988
3989 IF l_dyn_criteria ='PPG' then
3990 l_atleast_one_criteria:='Y';
3991
3992 OPEN ppg_cursor;
3993 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
3994 IF l_criteria_value1 is not null then
3995 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
3996 LOOP
3997 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
3998 EXIT WHEN PPG_CURSOR%NOTFOUND;
3999
4000 g_exec_string:= l_sql_string|| ' OR '||l_criteria_value1 ||' = '||'''' ||
4001 l_criteria_value2|| '''';
4002 l_sql_string:=g_exec_string;
4003 END LOOP;
4004 CLOSE ppg_cursor;
4005
4006 IF l_sql_string IS NOT NULL THEN
4007 g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4008 SELECT '|| '''' || 'PPG' || ''''||' , COUNT(person_id)
4009 FROM per_assignments_f paf,
4010 pay_people_groups ppg
4011 WHERE paf.people_group_id= ppg.people_group_id
4012 AND paf.assignment_type = ''' || 'E' || '''
4013 AND paf.effective_end_date >= :p_effort_start
4014 AND paf.effective_start_date <= :p_effort_end
4015 AND ppg.people_group_id IN (SELECT people_group_id
4016 FROM pay_people_groups
4017 WHERE ' || l_sql_string || ')';
4018
4019 hr_utility.trace( ' g_exec_string = '||g_exec_string );
4020
4021 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_Start, p_effort_end;
4022 g_lookup_code := 'PPG';
4023 END IF;
4024 END IF;
4025 ELSIF l_dyn_criteria ='GLA' then
4026 l_atleast_one_criteria:='Y';
4027
4028 OPEN gla_cursor;
4029 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
4030 IF l_criteria_value1 is not null then
4031 l_sql_string := l_criteria_value1 ||' between '|| '''' || l_criteria_value2 || '''' ||
4032 ' AND ' || ''''||l_criteria_value3||'''' ;
4033 LOOP
4034 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
4035 EXIT WHEN GLA_CURSOR%NOTFOUND;
4036
4037 g_exec_string:= l_sql_string || ' OR ' || l_criteria_value1 || ' between ' ||
4038 ''''|| l_criteria_value2 || '''' ||
4039 ' AND ' || ''''|| l_criteria_value3 || '''' ;
4040 l_sql_string:=g_exec_string;
4041 END LOOP;
4042 CLOSE gla_cursor;
4043
4044 IF l_sql_string IS NOT NULL THEN
4045 g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4046 SELECT '|| '''' || 'GLA' || ''''|| ' , COUNT( DISTINCT psl.assignment_id)
4047 FROM psp_summary_lines psl,
4048 psp_distribution_lines_history pdnh,
4049 psp_pre_gen_dist_lines_history ppg,
4050 psp_adjustment_lines_history palh,
4051 gl_code_combinations gcc
4052 WHERE psl.business_group_id = '|| p_business_group_id || '
4053 AND psl.set_of_books_id =' || p_set_of_books_id || '
4054 AND gcc.code_combination_id= psl.gl_code_combination_id
4055 AND psl.summary_line_id = pdnh.summary_line_id(+)
4056 AND psl.summary_line_id = ppg.summary_line_id(+)
4057 AND psl.summary_line_id = palh.summary_line_id(+)
4058 AND psl.status_code='||''''||'A'||''''||'
4059 AND ( (psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
4060 AND pdnh.distribution_date between :p_effort_start AND :p_effort_end
4061 AND pdnh.summary_line_id = psl.summary_line_id
4062 AND pdnh.reversal_entry_flag IS NULL
4063 AND pdnh.adjustment_batch_name IS NULL)
4064 OR (psl.source_type='||''''||'P'||''''||'
4065 AND ppg.distribution_date BETWEEN :p_effort_start
4066 AND :p_effort_end
4067 AND ppg.summary_line_id = psl.summary_line_id
4068 AND ppg.adjustment_batch_name IS NULL
4069 AND ppg.reversal_entry_flag IS NULL)
4070 OR (psl.source_type='||''''||'A'||''''||'
4071 AND palh.summary_line_id = psl.summary_line_id
4072 AND palh.reversal_entry_flag IS NULL
4073 AND palh.adjustment_batch_name IS NULL
4074 AND NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''||
4075 ') = '||''''|| 'N' || '''' || '
4076 AND palh.distribution_date BETWEEN :p_effort_start
4077 AND :p_effort_end ))
4078 AND gcc.code_combination_id= psl.gl_code_combination_id
4079 AND gcc.code_combination_id IN (SELECT code_combination_id
4080 FROM gl_code_combinations
4081 WHERE ' || l_sql_string || ')';
4082
4083 hr_utility.trace( ' g_exec_string = '||g_exec_string );
4084 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_start, p_effort_end,
4085 p_effort_start, p_effort_end ,p_effort_start, p_effort_end;
4086 g_lookup_code :='GLA';
4087 END IF;
4088 END IF;
4089 END IF;
4090 END;
4091 END IF;
4092
4093 IF NVL(l_atleast_one_criteria, 'N')='N' then
4094 g_lookup_code:='ALL' ;
4095 END IF;
4096
4097 EXCEPTION
4098 WHEN OTHERS THEN
4099 fnd_file.put_line(fnd_file.log, ' EXCEPTION '||sqlerrm);
4100 END get_asg_lowest_cardinality;
4101
4102 PROCEDURE prepare_initial_asg_list (p_request_id IN NUMBER,
4103 p_effort_start IN DATE,
4104 p_effort_end IN DATE,
4105 p_business_group_id IN NUMBER,
4106 p_set_of_books_id IN NUMBER) IS
4107 PRAGMA AUTONOMOUS_TRANSACTION;
4108 CURSOR PPG_CURSOR IS
4109 SELECT criteria_value1, criteria_value2
4110 FROM psp_report_template_details_h
4111 WHERE request_id = p_request_id
4112 AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA' AND criteria_lookup_code='PPG'
4113 AND include_exclude_flag='I';
4114
4115
4116 CURSOR GLA_CURSOR IS
4117 SELECT criteria_value1 , criteria_value2, criteria_value3
4118 FROM psp_report_template_details_h
4119 WHERE request_id = p_request_id
4120 AND criteria_lookup_type ='PSP_SELECTION_CRITERIA' AND criteria_lookup_code ='GLA'
4121 AND include_exclude_flag='I';
4122
4123 l_criteria_value1 VARCHAR2(30);
4124 l_criteria_value2 VARCHAR2(30);
4125 l_criteria_value3 VARCHAR2(30);
4126 l_sql_string VARCHAR2(1000);
4127
4128 l_cnt NUMBER;
4129 BEGIN
4130 IF g_lookup_code = 'PTY' THEN
4131
4132 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4133 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4134 FROM per_people_f ppf,
4135 per_assignments_f paf,
4136 per_assignment_status_types past
4137 WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
4138 FROM psp_report_template_details_h prtd
4139 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4140 AND criteria_lookup_code='PTY'
4141 AND request_id = p_request_id
4142 AND include_exclude_flag='I')
4143 AND paf.assignment_type = 'E'
4144 AND paf.person_id = ppf.person_id
4145 AND ppf.effective_start_date <= p_effort_end
4146 AND ppf.effective_end_date >= p_effort_start
4147 AND paf.effective_start_date <= p_effort_end
4148 AND paf.effective_end_date >= p_effort_start
4149 AND paf.assignment_status_type_id = past.assignment_status_type_id
4150 AND ( past.per_system_status <> 'TERM_ASSIGN'
4151 OR EXISTS ( select null
4152 FROM psp_pre_gen_dist_lines_history
4153 WHERE distribution_date between p_effort_start and p_effort_end
4154 AND assignment_id = paf.assignment_id
4155 AND reversal_entry_flag IS NULL
4156 AND rownum=1 )
4157 OR EXISTS (SELECT null
4158 FROM psp_distribution_lines_history pdlh
4159 , psp_summary_lines psl
4160 WHERE pdlh.summary_line_id = psl.summary_line_id
4161 AND distribution_date between p_effort_start and p_effort_end
4162 AND psl.person_id = paf.person_id
4163 AND psl.assignment_id = paf.assignment_id
4164 AND reversal_entry_flag IS NULL
4165 AND rownum=1));
4166
4167
4168 ELSIF g_lookup_code ='EMP' THEN
4169 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4170 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4171 FROM per_all_people_f ppf,
4172 per_assignments_f paf,
4173 per_assignment_status_types past
4174 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4175 FROM psp_report_template_details_h prtd
4176 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4177 AND criteria_lookup_code='EMP'
4178 AND include_exclude_flag='I'
4179 AND request_id = p_request_id)
4180 AND paf.assignment_type = 'E'
4181 AND paf.person_id = ppf.person_id
4182 AND ppf.effective_start_date <= p_effort_end
4183 AND ppf.effective_end_date >= p_effort_start
4184 AND paf.effective_start_date <= p_effort_end
4185 AND paf.effective_end_date >= p_effort_start
4186 AND paf.assignment_status_type_id = past.assignment_status_type_id
4187 AND ( past.per_system_status <> 'TERM_ASSIGN'
4188 OR EXISTS ( select null
4189 FROM psp_pre_gen_dist_lines_history
4190 WHERE distribution_date between p_effort_start and p_effort_end
4191 AND assignment_id = paf.assignment_id
4192 AND reversal_entry_flag IS NULL
4193 AND rownum=1 )
4194 OR EXISTS (SELECT null
4195 FROM psp_distribution_lines_history pdlh
4196 , psp_summary_lines psl
4197 WHERE pdlh.summary_line_id = psl.summary_line_id
4198 AND distribution_date between p_effort_start and p_effort_end
4199 AND psl.person_id = paf.person_id
4200 AND psl.assignment_id = paf.assignment_id
4201 AND reversal_entry_flag IS NULL
4202 AND rownum=1));
4203
4204
4205 ELSIF g_lookup_code ='SUP' THEN
4206 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4207 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4208 FROM per_all_assignments_f paf,
4209 per_assignment_status_types past
4210 WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
4211 FROM psp_report_template_details_h prtd
4212 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4213 AND criteria_lookup_code='SUP'
4214 AND include_exclude_flag='I'
4215 AND request_id = p_request_id)
4216 AND paf.assignment_type = 'E'
4217 AND paf.effective_start_date <= p_effort_end
4218 AND paf.effective_end_date >= p_effort_start
4219 AND paf.assignment_status_type_id = past.assignment_status_type_id
4220 AND ( past.per_system_status <> 'TERM_ASSIGN'
4221 OR EXISTS ( select null
4222 FROM psp_pre_gen_dist_lines_history
4223 WHERE distribution_date between p_effort_start and p_effort_end
4224 AND assignment_id = paf.assignment_id
4225 AND reversal_entry_flag IS NULL
4226 AND rownum=1 )
4227 OR EXISTS (SELECT null
4228 FROM psp_distribution_lines_history pdlh
4229 , psp_summary_lines psl
4230 WHERE pdlh.summary_line_id = psl.summary_line_id
4231 AND distribution_date between p_effort_start and p_effort_end
4232 AND psl.person_id = paf.person_id
4233 AND psl.assignment_id = paf.assignment_id
4234 AND reversal_entry_flag IS NULL
4235 AND rownum=1));
4236
4237 ELSIF g_lookup_code='AWD' THEN
4238 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4239 SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4240 FROM psp_summary_lines psl,
4241 psp_report_template_details_h prtd,
4242 per_time_periods ptp
4243 WHERE
4244 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4245 AND prtd.criteria_lookup_code='AWD'
4246 AND prtd.include_exclude_flag='I'
4247 AND prtd.request_id =p_request_id
4248 and psl.award_id = TO_NUMBER(prtd.criteria_value1)
4249 AND psl.business_group_id = p_business_group_id
4250 AND psl.set_of_books_id = p_set_of_books_id
4251 AND psl.status_code= 'A'
4252 AND ptp.time_period_id = psl.time_period_id
4253 AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4254 AND ( EXISTS (SELECT 1
4255 FROM psp_distribution_lines_history pdnh
4256 WHERE pdnh.summary_line_id = psl.summary_line_id
4257 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4258 AND pdnh.reversal_entry_flag IS NULL
4259 AND pdnh.adjustment_batch_name IS NULL )
4260 OR EXISTS (SELECT 1
4261 FROM psp_pre_gen_dist_lines_history ppg
4262 WHERE ppg.summary_line_id = psl.summary_line_id
4263 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
4264 AND ppg.adjustment_batch_name IS NULL
4265 AND ppg.reversal_entry_flag IS NULL)
4266 OR EXISTS (SELECT 1
4267 FROM psp_adjustment_lines_history palh
4268 WHERE palh.summary_line_id = psl.summary_line_id
4269 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
4270 AND palh.adjustment_batch_name IS NULL
4271 AND palh.reversal_entry_flag IS NULL
4272 AND NVL(palh.original_line_flag, 'N') ='N'));
4273 ELSIF g_lookup_code ='ATY' THEN
4274 INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4275 SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4276 FROM psp_summary_lines psl,
4277 psp_report_template_details_h prtd,
4278 gms_awards_all gaa,
4279 per_time_periods ptp
4280 WHERE psl.award_id = gaa.award_id
4281 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4282 AND prtd.criteria_lookup_code='ATY'
4283 AND prtd.include_exclude_flag='I'
4284 AND prtd.request_id =p_request_id
4285 AND gaa.type=prtd.criteria_value1
4286 AND psl.business_group_id = p_business_group_id
4287 AND psl.set_of_books_id = p_set_of_books_id
4288 AND psl.status_code= 'A'
4289 AND ptp.time_period_id = psl.time_period_id
4290 AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4291 AND ( EXISTS (SELECT 1
4292 FROM psp_distribution_lines_history pdnh
4293 WHERE pdnh.summary_line_id = psl.summary_line_id
4294 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4295 AND pdnh.reversal_entry_flag IS NULL
4296 AND pdnh.adjustment_batch_name IS NULL )
4297 OR EXISTS (SELECT 1
4298 FROM psp_pre_gen_dist_lines_history ppg
4299 WHERE ppg.summary_line_id = psl.summary_line_id
4300 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
4301 AND ppg.adjustment_batch_name IS NULL
4302 AND ppg.reversal_entry_flag IS NULL)
4303 OR EXISTS (SELECT 1
4304 FROM psp_adjustment_lines_history palh
4305 WHERE palh.summary_line_id = psl.summary_line_id
4306 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
4307 AND palh.adjustment_batch_name IS NULL
4308 AND palh.reversal_entry_flag IS NULL
4309 AND NVL(palh.original_line_flag, 'N') ='N'));
4310
4311 ELSIF g_lookup_code ='PRT' THEN
4312 INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4313 SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4314 FROM psp_summary_lines psl,
4315 psp_report_template_details_h prtd,
4316 pa_projects_all ppa,
4317 per_time_periods ptp
4318 WHERE psl.project_id = ppa.project_id
4319 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4320 AND prtd.criteria_lookup_code='PRT'
4321 AND prtd.include_exclude_flag='I'
4322 AND prtd.request_id =p_request_id
4323 AND ppa.project_type=prtd.criteria_value1
4324 AND psl.business_group_id = p_business_group_id
4325 AND psl.set_of_books_id = p_set_of_books_id
4326 AND psl.status_code= 'A'
4327 AND ptp.time_period_id = psl.time_period_id
4328 AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4329 AND ( EXISTS (SELECT 1
4330 FROM psp_distribution_lines_history pdnh
4331 WHERE pdnh.summary_line_id = psl.summary_line_id
4332 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4333 AND pdnh.reversal_entry_flag IS NULL
4334 AND pdnh.adjustment_batch_name IS NULL )
4335 OR EXISTS (SELECT 1
4336 FROM psp_pre_gen_dist_lines_history ppg
4337 WHERE ppg.summary_line_id = psl.summary_line_id
4338 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
4339 AND ppg.adjustment_batch_name IS NULL
4340 AND ppg.reversal_entry_flag IS NULL)
4341 OR EXISTS (SELECT 1
4342 FROM psp_adjustment_lines_history palh
4343 WHERE palh.summary_line_id = psl.summary_line_id
4344 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
4345 AND palh.adjustment_batch_name IS NULL
4346 AND palh.reversal_entry_flag IS NULL
4347 AND NVL(palh.original_line_flag, 'N') ='N'));
4348 ELSIF g_lookup_code ='PRJ' THEN
4349 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4350 SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4351 FROM psp_summary_lines psl,
4352 psp_report_template_details_h prtd,
4353 per_time_periods ptp
4354 WHERE
4355 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4356 AND prtd.criteria_lookup_code='PRJ'
4357 AND prtd.include_exclude_flag='I'
4358 AND prtd.request_id =p_request_id
4359 and psl.project_id = TO_NUMBER(prtd.criteria_value1)
4360 AND psl.business_group_id = p_business_group_id
4361 AND psl.set_of_books_id = p_set_of_books_id
4362 AND psl.status_code= 'A'
4363 AND ptp.time_period_id = psl.time_period_id
4364 AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4365 AND ( EXISTS (SELECT 1
4366 FROM psp_distribution_lines_history pdnh
4367 WHERE pdnh.summary_line_id = psl.summary_line_id
4368 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4369 AND pdnh.reversal_entry_flag IS NULL
4370 AND pdnh.adjustment_batch_name IS NULL )
4371 OR EXISTS (SELECT 1
4372 FROM psp_pre_gen_dist_lines_history ppg
4373 WHERE ppg.summary_line_id = psl.summary_line_id
4374 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
4375 AND ppg.adjustment_batch_name IS NULL
4376 AND ppg.reversal_entry_flag IS NULL)
4377 OR EXISTS (SELECT 1
4378 FROM psp_adjustment_lines_history palh
4379 WHERE palh.summary_line_id = psl.summary_line_id
4380 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
4381 AND palh.adjustment_batch_name IS NULL
4382 AND palh.reversal_entry_flag IS NULL
4383 AND NVL(palh.original_line_flag, 'N') ='N'));
4384 ELSIF g_lookup_code ='PAY' THEN
4385
4386 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4387 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4388 FROM per_assignments_f paf,
4389 per_assignment_status_types past
4390 WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
4391 FROM psp_report_template_details_h prtd
4392 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4393 AND criteria_lookup_code='PAY'
4394 AND include_exclude_flag='I'
4395 AND request_id = p_request_id)
4396 AND paf.assignment_type = 'E'
4397 AND paf.effective_start_date <= p_effort_end
4398 AND paf.effective_end_date >= p_effort_start
4399 AND paf.assignment_status_type_id = past.assignment_status_type_id
4400 AND ( past.per_system_status <> 'TERM_ASSIGN'
4401 OR EXISTS ( select null
4402 FROM psp_pre_gen_dist_lines_history
4403 WHERE distribution_date between p_effort_start and p_effort_end
4404 AND assignment_id = paf.assignment_id
4405 AND reversal_entry_flag IS NULL
4406 AND rownum=1 )
4407 OR EXISTS (SELECT null
4408 FROM psp_distribution_lines_history pdlh
4409 , psp_summary_lines psl
4410 WHERE pdlh.summary_line_id = psl.summary_line_id
4411 AND distribution_date between p_effort_start and p_effort_end
4412 AND psl.person_id = paf.person_id
4413 AND psl.assignment_id = paf.assignment_id
4414 AND reversal_entry_flag IS NULL
4415 AND rownum=1));
4416
4417 ELSIF g_lookup_code ='LOC' THEN
4418
4419 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4420 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4421 FROM per_assignments_f paf,
4422 per_assignment_status_types past
4423 WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
4424 FROM psp_report_template_details_h prtd
4425 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4426 AND criteria_lookup_code='LOC'
4427 AND include_exclude_flag='I'
4428 AND request_id = p_request_id)
4429 AND paf.assignment_type = 'E'
4430 AND paf.effective_start_date <= p_effort_end
4431 AND paf.effective_end_date >= p_effort_start
4432 AND paf.assignment_status_type_id = past.assignment_status_type_id
4433 AND ( past.per_system_status <> 'TERM_ASSIGN'
4434 OR EXISTS ( select null
4435 FROM psp_pre_gen_dist_lines_history
4436 WHERE distribution_date between p_effort_start and p_effort_end
4437 AND assignment_id = paf.assignment_id
4438 AND reversal_entry_flag IS NULL
4439 AND rownum=1 )
4440 OR EXISTS (SELECT null
4441 FROM psp_distribution_lines_history pdlh
4442 , psp_summary_lines psl
4443 WHERE pdlh.summary_line_id = psl.summary_line_id
4444 AND distribution_date between p_effort_start and p_effort_end
4445 AND psl.person_id = paf.person_id
4446 AND psl.assignment_id = paf.assignment_id
4447 AND reversal_entry_flag IS NULL
4448 AND rownum=1));
4449
4450 ELSIF g_lookup_code ='ORG' THEN
4451 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4452 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4453 FROM per_assignments_f paf,
4454 per_assignment_status_types past
4455 WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
4456 FROM psp_report_template_details_h prtd
4457 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4458 AND criteria_lookup_code='ORG'
4459 AND request_id = p_request_id
4460 AND include_exclude_flag='I')
4461 AND paf.assignment_type = 'E'
4462 AND paf.effective_start_date <= p_effort_end
4463 AND paf.effective_end_date >= p_effort_start
4464 AND paf.assignment_status_type_id = past.assignment_status_type_id
4465 AND ( past.per_system_status <> 'TERM_ASSIGN'
4466 OR EXISTS ( select null
4467 FROM psp_pre_gen_dist_lines_history
4468 WHERE distribution_date between p_effort_start and p_effort_end
4469 AND assignment_id = paf.assignment_id
4470 AND reversal_entry_flag IS NULL
4471 AND rownum=1 )
4472 OR EXISTS (SELECT null
4473 FROM psp_distribution_lines_history pdlh
4474 , psp_summary_lines psl
4475 WHERE pdlh.summary_line_id = psl.summary_line_id
4476 AND distribution_date between p_effort_start and p_effort_end
4477 AND psl.person_id = paf.person_id
4478 AND psl.assignment_id = paf.assignment_id
4479 AND reversal_entry_flag IS NULL
4480 AND rownum=1));
4481
4482 ELSIF g_lookup_code='CST' THEN
4483 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4484 SELECT DISTINCT p_request_id, person_id, assignment_id
4485 FROM per_assignments_f paf,
4486 pay_payrolls_f ppf,
4487 per_assignment_status_types past
4488 WHERE ppf.payroll_id = paf.payroll_id
4489 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
4490 FROM psp_report_template_details_h prtd
4491 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4492 AND criteria_lookup_code='CST'
4493 AND include_exclude_flag='I'
4494 AND request_id = p_request_id)
4495 AND paf.assignment_type = 'E'
4496 AND ppf.effective_start_date <= p_effort_end
4497 AND ppf.effective_end_date >= p_effort_start
4498 AND paf.effective_start_date <= p_effort_end
4499 AND paf.effective_end_date >= p_effort_start
4500 AND paf.assignment_status_type_id = past.assignment_status_type_id
4501 AND ( past.per_system_status <> 'TERM_ASSIGN'
4502 OR EXISTS ( select null
4503 FROM psp_pre_gen_dist_lines_history
4504 WHERE distribution_date between p_effort_start and p_effort_end
4505 AND assignment_id = paf.assignment_id
4506 AND reversal_entry_flag IS NULL
4507 AND rownum=1 )
4508 OR EXISTS (SELECT null
4509 FROM psp_distribution_lines_history pdlh
4510 , psp_summary_lines psl
4511 WHERE pdlh.summary_line_id = psl.summary_line_id
4512 AND distribution_date between p_effort_start and p_effort_end
4513 AND psl.person_id = paf.person_id
4514 AND psl.assignment_id = paf.assignment_id
4515 AND reversal_entry_flag IS NULL
4516 AND rownum=1));
4517
4518 ELSIF g_lookup_code = 'AST' THEN
4519
4520 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4521 SELECT DISTINCT p_request_id, person_id, paf.assignment_id
4522 FROM per_all_assignments_f paf,
4523 hr_assignment_sets has,
4524 hr_assignment_set_amendments hasa,
4525 per_assignment_status_types past
4526 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
4527 FROM psp_report_template_details_h prtd
4528 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4529 AND criteria_lookup_code='AST'
4530 AND include_exclude_flag='I'
4531 AND request_id =p_request_id)
4532 AND ( (paf.payroll_id = has.payroll_id
4533 AND paf.assignment_type = 'E'
4534 AND paf.effective_start_date <= p_effort_end
4535 AND paf.effective_end_date >= p_effort_start
4536 AND has.assignment_set_id = hasa.assignment_set_id)
4537 OR (paf.assignment_id = hasa.assignment_id
4538 AND paf.assignment_type = 'E'
4539 AND paf.effective_start_date <= p_effort_end
4540 AND paf.effective_end_date >= p_effort_start
4541 AND hasa.assignment_set_id=has.assignment_set_id AND include_or_exclude ='I'))
4542 AND NOT EXISTS (SELECT assignment_id
4543 FROM hr_assignment_set_amendments hasa
4544 WHERE hasa.assignment_id = paf.assignment_id AND hasa.include_or_exclude ='E'
4545 AND paf.assignment_type = 'E'
4546 AND paf.effective_start_date <= p_effort_end
4547 AND paf.effective_end_date >= p_effort_start)
4548 AND paf.assignment_status_type_id = past.assignment_status_type_id
4549 AND ( past.per_system_status <> 'TERM_ASSIGN'
4550 OR EXISTS ( select null
4551 FROM psp_pre_gen_dist_lines_history
4552 WHERE distribution_date between p_effort_start and p_effort_end
4553 AND assignment_id = paf.assignment_id
4554 AND reversal_entry_flag IS NULL
4555 AND rownum=1 )
4556 OR EXISTS (SELECT null
4557 FROM psp_distribution_lines_history pdlh
4558 , psp_summary_lines psl
4559 WHERE pdlh.summary_line_id = psl.summary_line_id
4560 AND distribution_date between p_effort_start and p_effort_end
4561 AND psl.person_id = paf.person_id
4562 AND psl.assignment_id = paf.assignment_id
4563 AND reversal_entry_flag IS NULL
4564 AND rownum=1));
4565
4566 ELSIF g_lookup_code ='JOB' THEN
4567
4568 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4569 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4570 FROM per_assignments_f paf,
4571 per_assignment_status_types past
4572 WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
4573 FROM psp_report_template_details_h prtd
4574 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4575 AND criteria_lookup_code='JOB'
4576 AND include_exclude_flag='I'
4577 AND request_id = p_request_id)
4578 AND paf.assignment_type = 'E'
4579 AND paf.effective_start_date <= p_effort_end
4580 AND paf.effective_end_date >= p_effort_start
4581 AND paf.assignment_status_type_id = past.assignment_status_type_id
4582 AND ( past.per_system_status <> 'TERM_ASSIGN'
4583 OR EXISTS ( select null
4584 FROM psp_pre_gen_dist_lines_history
4585 WHERE distribution_date between p_effort_start and p_effort_end
4586 AND assignment_id = paf.assignment_id
4587 AND reversal_entry_flag IS NULL
4588 AND rownum=1 )
4589 OR EXISTS (SELECT null
4590 FROM psp_distribution_lines_history pdlh
4591 , psp_summary_lines psl
4592 WHERE pdlh.summary_line_id = psl.summary_line_id
4593 AND distribution_date between p_effort_start and p_effort_end
4594 AND psl.person_id = paf.person_id
4595 AND psl.assignment_id = paf.assignment_id
4596 AND reversal_entry_flag IS NULL
4597 AND rownum=1));
4598
4599 ELSIF g_lookup_code ='POS' THEN
4600 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4601 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4602 FROM per_assignments_f paf,
4603 per_assignment_status_types past
4604 WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
4605 FROM psp_report_template_details_h prtd
4606 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4607 AND criteria_lookup_code='POS'
4608 AND request_id = p_request_id
4609 AND include_exclude_flag='I')
4610 AND paf.assignment_type = 'E'
4611 AND paf.effective_start_date <= p_effort_end
4612 AND paf.effective_end_date >= p_effort_start
4613 AND paf.assignment_status_type_id = past.assignment_status_type_id
4614 AND ( past.per_system_status <> 'TERM_ASSIGN'
4615 OR EXISTS ( select null
4616 FROM psp_pre_gen_dist_lines_history
4617 WHERE distribution_date between p_effort_start and p_effort_end
4618 AND assignment_id = paf.assignment_id
4619 AND reversal_entry_flag IS NULL
4620 AND rownum=1 )
4621 OR EXISTS (SELECT null
4622 FROM psp_distribution_lines_history pdlh
4623 , psp_summary_lines psl
4624 WHERE pdlh.summary_line_id = psl.summary_line_id
4625 AND distribution_date between p_effort_start and p_effort_end
4626 AND psl.person_id = paf.person_id
4627 AND psl.assignment_id = paf.assignment_id
4628 AND reversal_entry_flag IS NULL
4629 AND rownum=1));
4630
4631 ELSIF g_lookup_code ='ASS' THEN
4632 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4633 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4634 FROM per_assignments_f paf,
4635 per_assignment_status_types past
4636 WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
4637 FROM psp_report_template_details_h prtd
4638 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4639 AND criteria_lookup_code='ASS'
4640 AND request_id = p_request_id
4641 AND include_exclude_flag='I')
4642 AND paf.assignment_type = 'E'
4643 AND paf.effective_start_date <= p_effort_end
4644 AND paf.effective_end_date >= p_effort_start
4645 AND paf.assignment_status_type_id = past.assignment_status_type_id
4646 AND ( past.per_system_status <> 'TERM_ASSIGN'
4647 OR EXISTS ( select null
4648 FROM psp_pre_gen_dist_lines_history
4649 WHERE distribution_date between p_effort_start and p_effort_end
4650 AND assignment_id = paf.assignment_id
4651 AND reversal_entry_flag IS NULL
4652 AND rownum=1 )
4653 OR EXISTS (SELECT null
4654 FROM psp_distribution_lines_history pdlh
4655 , psp_summary_lines psl
4656 WHERE pdlh.summary_line_id = psl.summary_line_id
4657 AND distribution_date between p_effort_start and p_effort_end
4658 AND psl.person_id = paf.person_id
4659 AND psl.assignment_id = paf.assignment_id
4660 AND reversal_entry_flag IS NULL
4661 AND rownum=1));
4662
4663 ELSIF g_lookup_code='PPG' THEN
4664 open ppg_cursor;
4665 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
4666 IF l_criteria_value1 IS NOT NULL THEN
4667 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
4668
4669 LOOP
4670 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
4671 EXIT WHEN PPG_CURSOR%NOTFOUND;
4672
4673 g_exec_string:= l_sql_string || ' OR '||l_criteria_value1 || ' = ' ||
4674 ''''||l_criteria_value2 || '''';
4675 l_sql_string:= g_exec_string;
4676
4677 END LOOP;
4678 CLOSE ppg_cursor;
4679
4680 IF l_sql_string IS NOT NULL THEN
4681 g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4682 SELECT :request_id , person_id, assignment_id
4683 FROM per_assignments_f paf,
4684 pay_people_groups ppg,
4685 per_assignment_status_types past
4686 WHERE paf.people_group_id= ppg.people_group_id
4687 AND paf.assignment_type = ''' || 'E' || '''
4688 AND paf.effective_end_date >= :p_effort_start
4689 AND paf.effective_start_date <= :p_effort_end
4690 AND ppg.people_group_id IN (SELECT people_group_id
4691 FROM pay_people_groups
4692 WHERE ' || l_sql_string || ')
4693 AND paf.assignment_status_type_id = past.assignment_status_type_id
4694 AND ( past.per_system_status <> ''' ||'TERM_ASSIGN' || '''
4695 OR EXISTS ( select null
4696 FROM psp_pre_gen_dist_lines_history
4697 WHERE distribution_date between :p_effort_start and :p_effort_end
4698 AND assignment_id = paf.assignment_id
4699 AND reversal_entry_flag IS NULL
4700 AND rownum=1 )
4701 OR EXISTS (SELECT null
4702 FROM psp_distribution_lines_history pdlh
4703 , psp_summary_lines psl
4704 WHERE pdlh.summary_line_id = psl.summary_line_id
4705 AND distribution_date between :p_effort_start and :p_effort_end
4706 AND psl.person_id = paf.person_id
4707 AND psl.assignment_id = paf.assignment_id
4708 AND reversal_entry_flag IS NULL
4709 AND rownum=1))';
4710
4711 EXECUTE IMMEDIATE g_exec_string USING IN p_request_id, p_effort_start, p_effort_end,
4712 p_effort_start, p_effort_end, p_effort_start, p_effort_end;
4713 END IF;
4714 END IF;
4715 ELSIF g_lookup_code ='GLA' THEN
4716 OPEN gla_cursor;
4717 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
4718
4719 IF l_criteria_value1 IS NOT NULL THEN
4720 l_sql_string := l_criteria_value1 ||' between '|| '''' || l_criteria_value2 ||''''||
4721 ' AND ' || ''''|| l_criteria_value3 || '''' ;
4722 LOOP
4723 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
4724 EXIT WHEN GLA_CURSOR%NOTFOUND;
4725
4726 g_exec_string:= l_sql_string || ' OR ' || l_criteria_value1 ||
4727 ' BETWEEN ' || ''''|| l_criteria_value2 || '''' ||
4728 ' AND ' || ''''|| l_criteria_value3 || '''' ;
4729 l_sql_string:=g_exec_string;
4730 END LOOP;
4731 CLOSE gla_cursor;
4732
4733 IF l_sql_string IS NOT NULL THEN
4734 g_exec_string := 'INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4735 SELECT :p_request_id, psl.person_id, psl.assignment_id
4736 FROM psp_summary_lines psl,
4737 psp_distribution_lines_history pdnh,
4738 psp_adjustment_lines_history palh,
4739 psp_pre_gen_dist_lines_history ppg,
4740 gl_code_combinations gcc
4741 WHERE psl.business_group_id = '|| p_business_group_id || '
4742 AND psl.set_of_books_id = ' || p_set_of_books_id ||'
4743 AND psl.summary_line_id = pdnh.summary_line_id(+)
4744 AND psl.summary_line_id = ppg.summary_line_id(+)
4745 AND psl.summary_line_id = palh.summary_line_id(+)
4746 AND psl.status_code= '||''''||'A'||''''||'
4747 AND ( (psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
4748 AND pdnh.distribution_date between :p_effort_start AND :p_effort_end
4749 AND pdnh.reversal_entry_flag IS NULL
4750 AND pdnh.summary_line_id = psl.summary_line_id
4751 AND pdnh.adjustment_batch_name IS NULL)
4752 OR (psl.source_type='||''''||'P'||''''||'
4753 AND ppg.distribution_date between :p_effort_start AND :p_effort_end
4754 AND ppg.summary_line_id = psl.summary_line_id
4755 AND ppg.adjustment_batch_name IS NULL
4756 AND ppg.reversal_entry_flag IS NULL)
4757 OR (psl.source_type='||''''||'A'||''''||'
4758 AND palh.adjustment_batch_name IS NULL
4759 AND palh.summary_line_id =psl.summary_line_id
4760 AND NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = ' ||
4761 ''''|| 'N' || '''' ||'
4762 AND palh.distribution_date between :p_effort_start AND :p_effort_end))
4763 AND gcc.code_combination_id= psl.gl_code_combination_id
4764 AND gcc.code_combination_id IN (SELECT code_combination_id
4765 FROM gl_code_combinations
4766 WHERE ' || l_sql_string || ')';
4767 EXECUTE IMMEDIATE g_exec_string USING IN p_request_id, p_effort_start,
4768 p_effort_end, p_effort_start, p_effort_end, p_effort_start, p_effort_end;
4769 END IF;
4770 END IF;
4771 ELSIF g_lookup_code='ALL' THEN
4772 INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4773 SELECT DISTINCT p_request_id, person_id, assignment_id
4774 FROM per_assignments_f
4775 WHERE assignment_type = 'E'
4776 AND business_group_id = p_business_group_id
4777 AND effective_start_date <= p_effort_end
4778 AND effective_end_date >= p_effort_start;
4779 END IF;
4780
4781 COMMIT;
4782 END prepare_initial_asg_list;
4783
4784 PROCEDURE prune_initial_asg_list (p_request_id IN NUMBER,
4785 p_effort_start IN DATE,
4786 p_effort_end IN DATE,
4787 p_business_group_id IN NUMBER,
4788 p_set_of_books_id IN NUMBER) IS
4789 CURSOR get_all_selection_criteria(p_request_id IN NUMBER) IS
4790 SELECT distinct criteria_lookup_code,
4791 include_exclude_flag
4792 FROM psp_report_template_details_h
4793 WHERE request_id = p_request_id
4794 AND criteria_lookup_type='PSP_SELECTION_CRITERIA'
4795 ORDER BY include_exclude_flag;
4796
4797 CURSOR PPG_CURSOR IS
4798 SELECT criteria_value1, criteria_value2
4799 FROM psp_report_template_details_h
4800 WHERE request_id = p_request_id
4801 AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
4802 AND criteria_lookup_code='PPG'
4803 AND include_exclude_flag='I';
4804
4805 CURSOR GLA_CURSOR IS
4806 SELECT criteria_value1 , criteria_value2, criteria_value3
4807 FROM psp_report_template_details_h
4808 WHERE request_id = p_request_id
4809 AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
4810 AND criteria_lookup_code ='GLA'
4811 AND include_exclude_flag='I';
4812
4813
4814 TYPE t_varchar_30_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
4815 TYPE t_varchar_1_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
4816
4817 l_criteria_value1 VARCHAR2(30);
4818 l_criteria_value2 VARCHAR2(30);
4819 l_criteria_value3 VARCHAR2(30);
4820 l_sql_string VARCHAR2(1000);
4821 BEGIN
4822 OPEN get_all_selection_criteria(p_request_id);
4823 FETCH get_all_selection_criteria BULK COLLECT INTO template_sel_criteria.array_sel_criteria,
4824 template_sel_criteria.array_inc_exc_flag;
4825 CLOSE get_all_selection_criteria;
4826
4827 FOR i IN 1..template_sel_criteria.array_sel_criteria.COUNT
4828 LOOP
4829 IF template_sel_criteria.array_inc_exc_flag(i) = 'I' THEN
4830 IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code THEN
4831 IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
4832 DELETE FROM psp_selected_persons_t pspt
4833 WHERE request_id = p_request_id
4834 /* Bug 5087294 : Performance fix replacing not in with not exists */
4835 -- AND person_id NOT IN (SELECT NVL( person_id, 0)
4836 AND NOT EXISTS ( SELECT 1
4837 FROM per_people_f ppf,
4838 psp_report_template_details_h prtd,
4839 per_assignments_f paf
4840 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4841 AND criteria_lookup_code='PTY'
4842 AND paf.person_id = ppf.person_id
4843 AND paf.assignment_type = 'E'
4844 AND paf.effective_start_date <= p_effort_end
4845 AND paf.effective_end_date >= p_effort_start
4846 and
4847 ppf.effective_start_date <= p_effort_end and
4848 ppf.effective_end_date >= p_effort_start
4849 AND include_exclude_flag='I'
4850 AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
4851 AND prtd.request_id = p_request_id
4852 AND ppf.person_id = pspt.person_id );
4853 ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
4854 DELETE FROM psp_selected_persons_t pspt
4855 WHERE request_id = p_request_id
4856 /* Bug 5087294 : Performance fix replacing not in with not exists */
4857 -- AND person_id NOT IN (SELECT NVL(person_id,0)
4858 AND NOT EXISTS ( SELECT 1
4859 FROM per_all_people_f ppf, per_assignments_f paf
4860 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4861 FROM psp_report_template_details_h prtd
4862 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4863 AND criteria_lookup_code='EMP'
4864 AND include_exclude_flag='I'
4865 AND prtd.request_id = p_request_id)
4866 AND paf.person_id = ppf.person_id
4867 AND paf.assignment_type = 'E'
4868 AND paf.effective_start_date <= p_effort_end
4869 AND paf.effective_end_date >= p_effort_start
4870 AND ppf.effective_start_date <= p_effort_end
4871 AND ppf.effective_end_date >= p_effort_start
4872 AND ppf.person_id = pspt.person_id );
4873 ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
4874 DELETE FROM psp_selected_persons_t pspt
4875 WHERE request_id = p_request_id
4876 /* Bug 5087294 : Performance fix replacing not in with not exists */
4877 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
4878 AND NOT EXISTS ( SELECT 1
4879 FROM per_all_assignments_f paf,
4880 psp_report_template_details_h prtd
4881 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4882 AND criteria_lookup_code='SUP'
4883 AND include_exclude_flag='I'
4884 AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
4885 AND prtd.request_id = p_request_id
4886 AND paf.assignment_type = 'E'
4887 AND paf.effective_start_date <= p_effort_end
4888 AND effective_end_date >= p_effort_start
4889 AND paf.assignment_id = pspt.assignment_id );
4890 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
4891 DELETE FROM psp_selected_persons_t
4892 WHERE request_id = p_request_id
4893 AND assignment_id NOT IN (SELECT psl.assignment_id
4894 FROM psp_summary_lines psl,
4895 psp_report_template_details_h prtd ,
4896 per_time_periods ptp
4897 WHERE
4898 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4899 AND prtd.criteria_lookup_code='AWD'
4900 AND prtd.include_exclude_flag='I'
4901 AND prtd.request_id =p_request_id
4902 and psl.award_id = TO_NUMBER(prtd.criteria_value1)
4903 AND psl.business_group_id = p_business_group_id
4904 AND psl.set_of_books_id = p_set_of_books_id
4905 AND psl.status_code= 'A'
4906 AND ptp.time_period_id = psl.time_period_id
4907 AND (ptp.start_date <= p_effort_end
4908 AND ptp.end_date >= p_effort_start)
4909 AND ( EXISTS (SELECT 1
4910 FROM psp_distribution_lines_history pdnh
4911 WHERE pdnh.summary_line_id = psl.summary_line_id
4912 AND pdnh.distribution_date BETWEEN p_effort_start
4913 AND p_effort_end
4914 AND pdnh.reversal_entry_flag IS NULL
4915 AND pdnh.adjustment_batch_name IS NULL)
4916 OR EXISTS (SELECT 1
4917 FROM psp_pre_gen_dist_lines_history ppg
4918 WHERE ppg.summary_line_id = psl.summary_line_id
4919 AND ppg.distribution_date BETWEEN p_effort_start
4920 AND p_effort_end
4921 AND ppg.adjustment_batch_name IS NULL
4922 AND ppg.reversal_entry_flag IS NULL)
4923 OR EXISTS (SELECT 1
4924 FROM psp_adjustment_lines_history palh
4925 WHERE palh.summary_line_id = psl.summary_line_id
4926 AND palh.distribution_date BETWEEN p_effort_start
4927 AND p_effort_end
4928 AND palh.adjustment_batch_name IS NULL
4929 AND palh.reversal_entry_flag IS NULL
4930 AND NVL(palh.original_line_flag, 'N') ='N')));
4931 ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
4932 DELETE FROM psp_selected_persons_t
4933 WHERE request_id = p_request_id
4934 AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
4935 FROM psp_summary_lines psl,
4936 psp_report_template_details_h prtd,
4937 gms_awards_all gaa,
4938 per_time_periods ptp
4939 WHERE psl.award_id = gaa.award_id
4940 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4941 AND prtd.criteria_lookup_code='ATY'
4942 AND prtd.include_exclude_flag='I'
4943 AND prtd.request_id =p_request_id
4944 AND gaa.type=prtd.criteria_value1
4945 AND psl.business_group_id = p_business_group_id
4946 AND psl.set_of_books_id = p_set_of_books_id
4947 AND psl.status_code= 'A'
4948 AND ptp.time_period_id = psl.time_period_id
4949 AND (ptp.start_date <= p_effort_end
4950 AND ptp.end_date >= p_effort_start)
4951 AND ( EXISTS (SELECT 1
4952 FROM psp_distribution_lines_history pdnh
4953 WHERE pdnh.summary_line_id = psl.summary_line_id
4954 AND pdnh.distribution_date BETWEEN p_effort_start
4955 AND p_effort_end
4956 AND pdnh.reversal_entry_flag IS NULL
4957 AND pdnh.adjustment_batch_name IS NULL)
4958 OR EXISTS (SELECT 1
4959 FROM psp_pre_gen_dist_lines_history ppg
4960 WHERE ppg.summary_line_id = psl.summary_line_id
4961 AND ppg.distribution_date BETWEEN p_effort_start
4962 AND p_effort_end
4963 AND ppg.adjustment_batch_name IS NULL
4964 AND ppg.reversal_entry_flag IS NULL)
4965 OR EXISTS (SELECT 1
4966 FROM psp_adjustment_lines_history palh
4967 WHERE palh.summary_line_id = psl.summary_line_id
4968 AND palh.distribution_date BETWEEN p_effort_start
4969 AND p_effort_end
4970 AND palh.adjustment_batch_name IS NULL
4971 AND palh.reversal_entry_flag IS NULL
4972 AND NVL(palh.original_line_flag, 'N') ='N')));
4973 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
4974 DELETE FROM psp_selected_persons_t
4975 WHERE request_id = p_request_id
4976 AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
4977 FROM psp_summary_lines psl,
4978 psp_report_template_details_h prtd,
4979 pa_projects_all ppa,
4980 per_time_periods ptp
4981 WHERE psl.project_id = ppa.project_id
4982 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4983 AND prtd.criteria_lookup_code='PRT'
4984 AND prtd.include_exclude_flag='I'
4985 AND prtd.request_id =p_request_id
4986 AND ppa.project_type=prtd.criteria_value1
4987 AND psl.business_group_id = p_business_group_id
4988 AND psl.set_of_books_id = p_set_of_books_id
4989 AND psl.status_code= 'A'
4990 AND ptp.time_period_id = psl.time_period_id
4991 AND (ptp.start_date <= p_effort_end
4992 AND ptp.end_date >= p_effort_start)
4993 AND ( EXISTS (SELECT 1
4994 FROM psp_distribution_lines_history pdnh
4995 WHERE pdnh.summary_line_id = psl.summary_line_id
4996 AND pdnh.distribution_date BETWEEN p_effort_start
4997 AND p_effort_end
4998 AND pdnh.reversal_entry_flag IS NULL
4999 AND pdnh.adjustment_batch_name IS NULL)
5000 OR EXISTS (SELECT 1
5001 FROM psp_pre_gen_dist_lines_history ppg
5002 WHERE ppg.summary_line_id = psl.summary_line_id
5003 AND ppg.distribution_date BETWEEN p_effort_start
5004 AND p_effort_end
5005 AND ppg.adjustment_batch_name IS NULL
5006 AND ppg.reversal_entry_flag IS NULL)
5007 OR EXISTS (SELECT 1
5008 FROM psp_adjustment_lines_history palh
5009 WHERE palh.summary_line_id = psl.summary_line_id
5010 AND palh.distribution_date BETWEEN p_effort_start
5011 AND p_effort_end
5012 AND palh.adjustment_batch_name IS NULL
5013 AND palh.reversal_entry_flag IS NULL
5014 AND NVL(palh.original_line_flag, 'N') ='N')));
5015 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
5016 DELETE FROM psp_selected_persons_t
5017 WHERE request_id = p_request_id
5018 AND assignment_id NOT IN (SELECT NVL(psl.assignment_id ,0)
5019 FROM psp_summary_lines psl,
5020 psp_report_template_details_h prtd ,
5021 per_time_periods ptp
5022 WHERE
5023 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5024 AND prtd.criteria_lookup_code='PRJ'
5025 AND prtd.include_exclude_flag='I'
5026 AND prtd.request_id =p_request_id
5027 and psl.project_id = TO_NUMBER(prtd.criteria_value1)
5028 AND psl.business_group_id = p_business_group_id
5029 AND psl.set_of_books_id = p_set_of_books_id
5030 AND psl.status_code= 'A'
5031 AND ptp.time_period_id = psl.time_period_id
5032 AND (ptp.start_date <= p_effort_end
5033 AND ptp.end_date >= p_effort_start)
5034 AND ( EXISTS (SELECT 1
5035 FROM psp_distribution_lines_history pdnh
5036 WHERE pdnh.summary_line_id = psl.summary_line_id
5037 AND pdnh.distribution_date BETWEEN p_effort_start
5038 AND p_effort_end
5039 AND pdnh.reversal_entry_flag IS NULL
5040 AND pdnh.adjustment_batch_name IS NULL )
5041 OR EXISTS (SELECT 1
5042 FROM psp_pre_gen_dist_lines_history ppg
5043 WHERE ppg.summary_line_id = psl.summary_line_id
5044 AND ppg.distribution_date BETWEEN p_effort_start
5045 AND p_effort_end
5046 AND ppg.adjustment_batch_name IS NULL
5047 AND ppg.reversal_entry_flag IS NULL)
5048 OR EXISTS (SELECT 1
5049 FROM psp_adjustment_lines_history palh
5050 WHERE palh.summary_line_id = psl.summary_line_id
5051 AND palh.distribution_date BETWEEN p_effort_start
5052 AND p_effort_end
5053 AND palh.adjustment_batch_name IS NULL
5054 AND palh.reversal_entry_flag IS NULL
5055 AND NVL(palh.original_line_flag, 'N') ='N')));
5056 ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5057 DELETE FROM psp_selected_persons_t pspt
5058 WHERE request_id = p_request_id
5059 /* Bug 5087294 : Performance fix replacing not in with not exists */
5060 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5061 AND NOT EXISTS ( SELECT 1
5062 FROM per_assignments_f paf,
5063 psp_report_template_details_h prtd
5064 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5065 AND criteria_lookup_code='PAY'
5066 AND include_exclude_flag='I'
5067 AND paf.assignment_type = 'E'
5068 and
5069 effective_start_date <= p_effort_end and
5070 effective_end_date >= p_effort_start
5071 AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
5072 AND prtd.request_id = p_request_id
5073 AND paf.assignment_id = pspt.assignment_id );
5074 ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5075 DELETE FROM psp_selected_persons_t pspt
5076 WHERE request_id = p_request_id
5077 /* Bug 5087294 : Performance fix replacing not in with not exists */
5078 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5079 AND NOT EXISTS ( SELECT 1
5080 FROM per_assignments_f paf ,
5081 psp_report_template_details_h prtd
5082 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5083 AND criteria_lookup_code='LOC'
5084 AND paf.assignment_type = 'E'
5085 and
5086 effective_start_date <= p_effort_end and
5087 effective_end_date >= p_effort_start
5088 AND include_exclude_flag='I'
5089 AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
5090 AND prtd.request_id = p_request_id
5091 AND paf.assignment_id = pspt.assignment_id);
5092 ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5093 /* Bug 5087294 : Performance fix replacing not in with not exists */
5094 DELETE FROM psp_selected_persons_t pspt
5095 WHERE request_id = p_request_id
5096 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5097 AND NOT EXISTS ( SELECT 1
5098 FROM per_assignments_f paf ,
5099 psp_report_template_details_h prtd
5100 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5101 AND criteria_lookup_code='ORG'
5102 AND include_exclude_flag='I'
5103 AND paf.assignment_type = 'E'
5104 and effective_start_date <= p_effort_end and
5105 effective_end_date >= p_effort_start
5106 AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
5107 AND prtd.request_id = p_request_id
5108 AND paf.assignment_id = pspt.assignment_id );
5109
5110 ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5111 DELETE FROM psp_selected_persons_t pspt
5112 WHERE request_id = p_request_id
5113 /* Bug 5087294 : Performance fix replacing not in with not exists */
5114 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5115 AND NOT EXISTS ( SELECT 1
5116 FROM per_assignments_f paf,
5117 pay_payrolls_f ppf
5118 WHERE ppf.payroll_id = paf.payroll_id
5119 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5120 FROM psp_report_template_details_h prtd
5121 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5122 AND criteria_lookup_code='CST'
5123 AND include_exclude_flag='I'
5124 AND request_id = p_request_id)
5125 AND paf.assignment_type = 'E'
5126 AND ppf.effective_start_date <= p_effort_end
5127 AND ppf.effective_end_date >= p_effort_start
5128 AND paf.effective_start_date <= p_effort_end
5129 AND paf.effective_end_date >= p_effort_start
5130 AND paf.assignment_id = pspt.assignment_id );
5131 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
5132 DELETE FROM psp_selected_persons_t
5133 WHERE request_id = p_request_id
5134 AND assignment_id NOT IN (SELECT NVL(paf.assignment_id,0)
5135 FROM per_all_assignments_f paf,
5136 hr_assignment_sets has,
5137 hr_assignment_set_amendments hasa
5138 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
5139 FROM psp_report_template_details_h prtd
5140 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5141 AND criteria_lookup_code='AST'
5142 AND include_exclude_flag='I'
5143 AND request_id =p_request_id)
5144 AND ( (paf.payroll_id = has.payroll_id
5145 AND paf.assignment_type = 'E'
5146 AND paf.effective_start_date <= p_effort_end
5147 AND paf.effective_end_date >= p_effort_start
5148 AND has.assignment_set_id = hasa.assignment_set_id)
5149 OR (paf.assignment_id = hasa.assignment_id
5150 AND paf.assignment_type = 'E'
5151 AND paf.effective_start_date <= p_effort_end
5152 AND paf.effective_end_date >= p_effort_start
5153 AND hasa.assignment_set_id=has.assignment_set_id
5154 AND include_or_exclude ='I'))
5155 AND NOT EXISTS (SELECT assignment_id
5156 FROM hr_assignment_set_amendments hasa
5157 WHERE hasa.assignment_id = paf.assignment_id
5158 AND paf.assignment_type = 'E'
5159 AND hasa.include_or_exclude ='E'
5160 AND paf.effective_start_date <= p_effort_end
5161 AND paf.effective_end_date >= p_effort_start));
5162 ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5163 DELETE FROM psp_selected_persons_t pspt
5164 WHERE request_id = p_request_id
5165 /* Bug 5087294 : Performance fix replacing not in with not exists */
5166 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5167 AND NOT EXISTS ( SELECT 1
5168 FROM per_assignments_f paf ,
5169 psp_report_template_details_h prtd
5170 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5171 AND criteria_lookup_code='JOB'
5172 AND include_exclude_flag='I'
5173 AND paf.assignment_type = 'E'
5174 and
5175 effective_start_date <= p_effort_end and
5176 effective_end_date >= p_effort_start
5177 AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
5178 AND prtd.request_id = p_request_id
5179 AND paf.assignment_id = pspt.assignment_id);
5180 ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5181 DELETE FROM psp_selected_persons_t pspt
5182 WHERE request_id = p_request_id
5183 /* Bug 5087294 : Performance fix replacing not in with not exists */
5184 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5185 AND NOT EXISTS ( SELECT 1
5186 FROM per_assignments_f paf ,
5187 psp_report_template_details_h prtd
5188 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5189 AND criteria_lookup_code='POS'
5190 AND paf.assignment_type = 'E'
5191 and
5192 effective_start_date <= p_effort_end and
5193 effective_end_date >= p_effort_start
5194 AND include_exclude_flag='I'
5195 AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
5196 AND prtd.request_id = p_request_id
5197 AND paf.assignment_id = pspt.assignment_id);
5198 ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5199 DELETE FROM psp_selected_persons_t pspt
5200 WHERE request_id = p_request_id
5201 /* Bug 5087294 : Performance fix replacing not in with not exists */
5202 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5203 AND NOT EXISTS ( SELECT 1
5204 FROM per_assignments_f paf ,
5205 psp_report_template_details_h prtd
5206 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5207 AND criteria_lookup_code='ASS'
5208 AND include_exclude_flag='I'
5209 AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
5210 AND prtd.request_id = p_request_id
5211 AND paf.assignment_type = 'E'
5212 AND effective_start_date <= p_effort_end
5213 AND effective_end_date >= p_effort_start
5214 AND paf.assignment_id = pspt.assignment_id);
5215 ELSIF template_sel_criteria.array_sel_criteria(i)='PPG' THEN
5216 OPEN ppg_cursor;
5217 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
5218 IF l_criteria_value1 IS NOT NULL THEN
5219 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
5220
5221 LOOP
5222 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
5223 EXIT WHEN PPG_CURSOR%NOTFOUND;
5224
5225 g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 ||
5226 ' = ' || '''' || l_criteria_value2||'''';
5227 l_sql_string:= g_exec_string;
5228 END LOOP;
5229 CLOSE ppg_cursor;
5230
5231 IF l_sql_string IS NOT NULL THEN
5232 g_exec_string := 'DELETE FROM psp_selected_persons_t sel
5233 WHERE request_id = :request_id
5234 AND NOT EXISTS (SELECT 1
5235 FROM per_assignments_f paf, pay_people_groups ppg
5236 WHERE paf.people_group_id= ppg.people_group_id
5237 AND paf.assignment_type = ''' || 'E' || '''
5238 AND paf.effective_end_date >= :p_effort_Start
5239 AND paf.effective_start_date <= :p_effort_end
5240 AND (' || l_sql_string || ')
5241 AND paf.assignment_id = sel.assignment_id )';
5242
5243 EXECUTE IMMEDIATE g_exec_string USING IN p_request_id,
5244 p_Effort_start, p_effort_end;
5245 END IF;
5246 END IF;
5247 ELSIF template_sel_criteria.array_sel_criteria(i)='GLA' THEN
5248 OPEN gla_cursor;
5249 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
5250 IF l_criteria_value1 IS NOT NULL THEN
5251 l_sql_string := l_criteria_value1 ||' BETWEEN '|| ''''||
5252 l_criteria_value2 || '''' || ' AND ' || '''' ||
5253 l_criteria_value3 || '''';
5254 LOOP
5255 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2,
5256 l_criteria_value3;
5257 EXIT WHEN GLA_CURSOR%NOTFOUND;
5258
5259 g_exec_string:= l_sql_string || ' OR ' || l_criteria_value1 ||
5260 ' BETWEEN ' || ''''|| l_criteria_value2 || '''' ||
5261 ' AND ' || ''''|| l_criteria_value3 || '''' ;
5262 l_sql_string:=g_exec_string;
5263 END LOOP;
5264 CLOSE gla_cursor;
5265
5266 IF l_sql_string IS NOT NULL THEN
5267 g_exec_string := 'DELETE FROM psp_selected_persons_t
5268 WHERE assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
5269 FROM psp_summary_lines psl, psp_distribution_lines_history pdnh,
5270 psp_adjustment_lines_history palh,
5271 psp_pre_gen_dist_lines_history ppg, gl_code_combinations gcc
5272 WHERE gcc.code_combination_id= psl.gl_code_combination_id
5273 AND psl.business_group_id = '|| p_business_group_id || '
5274 AND psl.set_of_books_id = ' || p_set_of_books_id || '
5275 AND psl.summary_line_id = pdnh.summary_line_id(+)
5276 AND psl.summary_line_id = ppg.summary_line_id(+)
5277 AND psl.summary_line_id = palh.summary_line_id(+)
5278 AND psl.status_code='||''''||'A'||''''||'
5279 AND ( (psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
5280 AND pdnh.distribution_date BETWEEN
5281 :p_effort_start AND :p_effort_end
5282 AND pdnh.reversal_entry_flag IS NULL
5283 AND psl.summary_line_id = pdnh.summary_line_id
5284 AND pdnh.adjustment_batch_name IS NULL)
5285 OR (psl.source_type='||''''||'P'||''''||'
5286 AND ppg.distribution_date BETWEEN :p_effort_start AND :p_effort_end
5287 AND ppg.adjustment_batch_name IS NULL
5288 AND ppg.summary_line_id =psl.summary_line_id
5289 AND ppg.reversal_entry_flag IS NULL)
5290 OR (psl.source_type='||''''||'A'||''''||'
5291 AND palh.summary_line_id =psl.summary_line_id
5292 AND palh.adjustment_batch_name IS NULL
5293 AND NVL(palh.original_line_flag, ' || ''''||
5294 'N' || ''''|| ') = '||''''|| 'N' || '''' || '
5295 AND palh.distribution_date BETWEEN
5296 :p_effort_start AND :p_effort_end))
5297 AND gcc.code_combination_id= psl.gl_code_combination_id
5298 AND gcc.code_combination_id IN (SELECT code_combination_id
5299 FROM gl_code_combinations
5300 WHERE ' || l_sql_string || ' ))
5301 AND request_id = :request_id';
5302
5303 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_start,
5304 p_effort_end , p_effort_start, p_effort_end,
5305 p_effort_start, p_effort_end, p_request_id;
5306 END IF;
5307 END IF;
5308 END IF;
5309 END IF;
5310 END IF;
5311 END LOOP;
5312 END prune_initial_asg_list;
5313
5314 PROCEDURE APPLY_ASG_EXCLUSION_CRITERIA (p_request_id IN NUMBER,
5315 p_effort_start IN DATE,
5316 p_effort_end IN DATE,
5317 p_business_group_id IN NUMBER,
5318 p_set_of_books_id IN NUMBER) IS
5319 CURSOR PPG_CURSOR IS
5320 SELECT criteria_value1, criteria_value2
5321 FROM psp_report_template_details_h
5322 WHERE request_id = p_request_id
5323 AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
5324 AND criteria_lookup_code='PPG'
5325 AND include_exclude_flag='E';
5326
5327 CURSOR GLA_CURSOR IS
5328 SELECT criteria_value1, criteria_value2, criteria_value3
5329 FROM psp_report_template_details_h
5330 WHERE request_id = p_request_id
5331 AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
5332 AND criteria_lookup_code ='GLA'
5333 AND include_exclude_flag='E';
5334
5335 TYPE t_varchar_30_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
5336 TYPE t_varchar_1_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
5337
5338 l_criteria_value1 VARCHAR2(30);
5339 l_criteria_value2 VARCHAR2(30);
5340 l_criteria_value3 VARCHAR2(30);
5341 l_sql_string VARCHAR2(1000);
5342 BEGIN
5343 FOR i IN 1..template_sel_criteria.array_sel_criteria.COUNT
5344 LOOP
5345 IF template_sel_criteria.array_inc_exc_flag(i) = 'E' THEN
5346 IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
5347 DELETE FROM psp_selected_persons_t
5348 WHERE request_id = p_request_id
5349 AND person_id IN (SELECT ppf.person_id
5350 FROM per_people_f ppf,
5351 psp_report_template_details_h prtd,
5352 per_assignments_f paf
5353 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5354 AND criteria_lookup_code='PTY'
5355 AND paf.person_id = ppf.person_id
5356 AND paf.assignment_type = 'E'
5357 AND paf.effective_start_date <= p_effort_end
5358 AND paf.effective_end_date >= p_effort_start
5359 and
5360 ppf.effective_start_date <= p_effort_end and
5361 ppf.effective_end_date >= p_effort_start
5362 AND include_exclude_flag='E'
5363 AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
5364 AND prtd.request_id = p_request_id);
5365 ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
5366 DELETE FROM psp_selected_persons_t
5367 WHERE request_id = p_request_id
5368 AND person_id IN (SELECT DISTINCT ppf.person_id
5369 FROM per_all_people_f ppf, per_assignments_f paf
5370 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
5371 FROM psp_report_template_details_h prtd
5372 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5373 AND criteria_lookup_code='EMP'
5374 AND include_exclude_flag='E'
5375 AND prtd.request_id = p_request_id)
5376 AND paf.person_id = ppf.person_id
5377 AND paf.assignment_type = 'E'
5378 AND paf.effective_start_date <= p_effort_end
5379 AND paf.effective_end_date >= p_effort_start
5380 AND ppf.effective_start_date <= p_effort_end
5381 AND ppf.effective_end_date >= p_effort_start);
5382 ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
5383 DELETE FROM psp_selected_persons_t
5384 WHERE request_id = p_request_id
5385 AND assignment_id IN (SELECT NVL(assignment_id,0)
5386 FROM per_all_assignments_f paf,
5387 psp_report_template_details_h prtd
5388 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5389 AND criteria_lookup_code='SUP'
5390 AND include_exclude_flag='E'
5391 AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
5392 AND prtd.request_id = p_request_id
5393 AND paf.assignment_type = 'E'
5394 AND paf.effective_start_date <= p_effort_end
5395 AND paf.effective_end_date >= p_effort_start);
5396 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
5397 DELETE FROM psp_selected_persons_t
5398 WHERE request_id = p_request_id
5399 AND assignment_id IN (SELECT psl.assignment_id
5400 FROM psp_summary_lines psl,
5401 psp_report_template_details_h prtd,
5402 per_time_periods ptp
5403 WHERE
5404 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5405 AND prtd.criteria_lookup_code='AWD'
5406 AND prtd.include_exclude_flag='E'
5407 AND prtd.request_id =p_request_id
5408 and psl.award_id = TO_NUMBER(prtd.criteria_value1)
5409 AND psl.business_group_id = p_business_group_id
5410 AND psl.set_of_books_id = p_set_of_books_id
5411 AND psl.status_code= 'A'
5412 AND ptp.time_period_id = psl.time_period_id
5413 AND (ptp.start_date <= p_effort_end
5414 AND ptp.end_date >= p_effort_start)
5415 AND ( EXISTS (SELECT 1
5416 FROM psp_distribution_lines_history pdnh
5417 WHERE pdnh.summary_line_id = psl.summary_line_id
5418 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5419 AND pdnh.reversal_entry_flag IS NULL
5420 AND pdnh.adjustment_batch_name IS NULL)
5421 OR EXISTS (SELECT 1
5422 FROM psp_pre_gen_dist_lines_history ppg
5423 WHERE ppg.summary_line_id = psl.summary_line_id
5424 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
5425 AND ppg.adjustment_batch_name IS NULL
5426 AND ppg.reversal_entry_flag IS NULL)
5427 OR EXISTS (SELECT 1
5428 FROM psp_adjustment_lines_history palh
5429 WHERE palh.summary_line_id = psl.summary_line_id
5430 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
5431 AND palh.adjustment_batch_name IS NULL
5432 AND palh.reversal_entry_flag IS NULL
5433 AND NVL(palh.original_line_flag, 'N') ='N')));
5434 ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
5435 DELETE FROM psp_selected_persons_t
5436 WHERE request_id = p_request_id
5437 AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5438 FROM psp_summary_lines psl,
5439 psp_report_template_details_h prtd,
5440 gms_awards_all gaa,
5441 per_time_periods ptp
5442 WHERE psl.award_id = gaa.award_id
5443 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5444 AND prtd.criteria_lookup_code='ATY'
5445 AND prtd.include_exclude_flag='E'
5446 AND prtd.request_id =p_request_id
5447 AND gaa.type=prtd.criteria_value1
5448 AND psl.business_group_id = p_business_group_id
5449 AND psl.set_of_books_id = p_set_of_books_id
5450 AND psl.status_code= 'A'
5451 AND ptp.time_period_id = psl.time_period_id
5452 AND (ptp.start_date <= p_effort_end
5453 AND ptp.end_date >= p_effort_start)
5454 AND ( EXISTS (SELECT 1
5455 FROM psp_distribution_lines_history pdnh
5456 WHERE pdnh.summary_line_id = psl.summary_line_id
5457 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5458 AND pdnh.reversal_entry_flag IS NULL
5459 AND pdnh.adjustment_batch_name IS NULL)
5460 OR EXISTS (SELECT 1
5461 FROM psp_pre_gen_dist_lines_history ppg
5462 WHERE ppg.summary_line_id = psl.summary_line_id
5463 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
5464 AND ppg.adjustment_batch_name IS NULL
5465 AND ppg.reversal_entry_flag IS NULL)
5466 OR EXISTS (SELECT 1
5467 FROM psp_adjustment_lines_history palh
5468 WHERE palh.summary_line_id = psl.summary_line_id
5469 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
5470 AND palh.adjustment_batch_name IS NULL
5471 AND palh.reversal_entry_flag IS NULL
5472 AND NVL(palh.original_line_flag, 'N') ='N')));
5473 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
5474 DELETE FROM psp_selected_persons_t
5475 WHERE request_id = p_request_id
5476 AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5477 FROM psp_summary_lines psl,
5478 psp_report_template_details_h prtd,
5479 pa_projects_all ppa,
5480 per_time_periods ptp
5481 WHERE psl.project_id = ppa.project_id
5482 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5483 AND prtd.criteria_lookup_code='PRT'
5484 AND prtd.include_exclude_flag='E'
5485 AND prtd.request_id =p_request_id
5486 AND ppa.project_type=prtd.criteria_value1
5487 AND psl.business_group_id = p_business_group_id
5488 AND psl.set_of_books_id = p_set_of_books_id
5489 AND psl.status_code= 'A'
5490 AND ptp.time_period_id = psl.time_period_id
5491 AND (ptp.start_date <= p_effort_end
5492 AND ptp.end_date >= p_effort_start)
5493 AND ( EXISTS (SELECT 1
5494 FROM psp_distribution_lines_history pdnh
5495 WHERE pdnh.summary_line_id = psl.summary_line_id
5496 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5497 AND pdnh.reversal_entry_flag IS NULL
5498 AND pdnh.adjustment_batch_name IS NULL)
5499 OR EXISTS (SELECT 1
5500 FROM psp_pre_gen_dist_lines_history ppg
5501 WHERE ppg.summary_line_id = psl.summary_line_id
5502 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
5503 AND ppg.adjustment_batch_name IS NULL
5504 AND ppg.reversal_entry_flag IS NULL)
5505 OR EXISTS (SELECT 1
5506 FROM psp_adjustment_lines_history palh
5507 WHERE palh.summary_line_id = psl.summary_line_id
5508 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
5509 AND palh.adjustment_batch_name IS NULL
5510 AND palh.reversal_entry_flag IS NULL
5511 AND NVL(palh.original_line_flag, 'N') ='N')));
5512 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
5513 DELETE FROM psp_selected_persons_t
5514 WHERE request_id = p_request_id
5515 AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5516 FROM psp_summary_lines psl,
5517 psp_report_template_details_h prtd,
5518 per_time_periods ptp
5519 WHERE
5520 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5521 AND prtd.criteria_lookup_code='PRJ'
5522 AND prtd.include_exclude_flag='E'
5523 AND prtd.request_id =p_request_id
5524 and psl.project_id = TO_NUMBER(prtd.criteria_value1)
5525 AND psl.business_group_id = p_business_group_id
5526 AND psl.set_of_books_id = p_set_of_books_id
5527 AND psl.status_code= 'A'
5528 AND ptp.time_period_id = psl.time_period_id
5529 AND (ptp.start_date <= p_effort_end
5530 AND ptp.end_date >= p_effort_start)
5531 AND ( EXISTS (SELECT 1
5532 FROM psp_distribution_lines_history pdnh
5533 WHERE pdnh.summary_line_id = psl.summary_line_id
5534 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5535 AND pdnh.reversal_entry_flag IS NULL
5536 AND pdnh.adjustment_batch_name IS NULL)
5537 OR EXISTS (SELECT 1
5538 FROM psp_pre_gen_dist_lines_history ppg
5539 WHERE ppg.summary_line_id = psl.summary_line_id
5540 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
5541 AND ppg.adjustment_batch_name IS NULL
5542 AND ppg.reversal_entry_flag IS NULL)
5543 OR EXISTS (SELECT 1
5544 FROM psp_adjustment_lines_history palh
5545 WHERE palh.summary_line_id = psl.summary_line_id
5546 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
5547 AND palh.adjustment_batch_name IS NULL
5548 AND palh.reversal_entry_flag IS NULL
5549 AND NVL(palh.original_line_flag, 'N') ='N')));
5550 ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5551 DELETE FROM psp_selected_persons_t
5552 WHERE request_id = p_request_id
5553 AND assignment_id IN (SELECT assignment_id
5554 FROM per_assignments_f paf,
5555 psp_report_template_details_h prtd
5556 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5557 AND criteria_lookup_code='PAY'
5558 AND include_exclude_flag='E'
5559 AND paf.assignment_type = 'E'
5560 and effective_start_date <= p_effort_end and
5561 effective_end_date >= p_effort_start
5562 AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
5563 AND prtd.request_id = p_request_id);
5564 ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5565 DELETE FROM psp_selected_persons_t
5566 WHERE request_id = p_request_id
5567 AND assignment_id IN (SELECT assignment_id
5568 FROM per_assignments_f paf,
5569 psp_report_template_details_h prtd
5570 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5571 AND criteria_lookup_code='LOC'
5572 AND include_exclude_flag='E'
5573 AND paf.assignment_type = 'E'
5574 and effective_start_date <= p_effort_end and
5575 effective_end_date >= p_effort_start
5576 AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
5577 AND prtd.request_id = p_request_id);
5578 ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5579 DELETE FROM psp_selected_persons_t
5580 WHERE request_id = p_request_id
5581 AND assignment_id IN (SELECT assignment_id
5582 FROM per_assignments_f paf,
5583 psp_report_template_details_h prtd
5584 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5585 AND criteria_lookup_code='ORG'
5586 AND include_exclude_flag='E'
5587 AND paf.assignment_type = 'E'
5588 and effective_start_date <= p_effort_end and
5589 effective_end_date >= p_effort_start
5590 AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
5591 AND prtd.request_id = p_request_id);
5592 ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5593 DELETE FROM psp_selected_persons_t
5594 WHERE request_id = p_request_id
5595 AND assignment_id IN (SELECT assignment_id
5596 FROM per_assignments_f paf,
5597 psp_report_template_details_h prtd
5598 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5599 AND criteria_lookup_code='JOB'
5600 AND include_exclude_flag='E'
5601 AND paf.assignment_type = 'E'
5602 and effective_start_date <= p_effort_end and
5603 effective_end_date >= p_effort_start
5604 AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
5605 AND prtd.request_id = p_request_id);
5606 ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5607 DELETE FROM psp_selected_persons_t
5608 WHERE request_id = p_request_id
5609 AND assignment_id IN (SELECT assignment_id
5610 FROM per_assignments_f paf,
5611 psp_report_template_details_h prtd
5612 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5613 AND criteria_lookup_code='POS'
5614 AND include_exclude_flag='E'
5615 AND paf.assignment_type = 'E'
5616 and effective_start_date <= p_effort_end and
5617 effective_end_date >= p_effort_start
5618 AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
5619 AND prtd.request_id = p_request_id);
5620 ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5621 DELETE FROM psp_selected_persons_t
5622 WHERE request_id = p_request_id
5623 AND assignment_id IN (SELECT assignment_id
5624 FROM per_assignments_f paf,
5625 psp_report_template_details_h prtd
5626 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5627 AND criteria_lookup_code='ASS'
5628 AND include_exclude_flag='E'
5629 AND paf.assignment_type = 'E'
5630 AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
5631 AND prtd.request_id = p_request_id
5632 AND effective_start_date <= p_effort_end
5633 AND effective_end_date >= p_effort_start);
5634 ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5635 DELETE FROM psp_selected_persons_t
5636 WHERE request_id = p_request_id
5637 AND assignment_id IN (SELECT NVL(assignment_id,0)
5638 FROM per_assignments_f paf,
5639 pay_payrolls_f ppf
5640 WHERE ppf.payroll_id = paf.payroll_id
5641 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5642 FROM psp_report_template_details_h prtd
5643 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5644 AND criteria_lookup_code='CST'
5645 AND include_exclude_flag='E'
5646 AND request_id = p_request_id)
5647 AND paf.assignment_type = 'E'
5648 AND ppf.effective_start_date <= p_effort_end
5649 AND ppf.effective_end_date >= p_effort_start
5650 AND paf.effective_start_date <= p_effort_end
5651 AND paf.effective_end_date >= p_effort_start);
5652 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
5653 DELETE FROM psp_selected_persons_t
5654 WHERE request_id = p_request_id
5655 AND assignment_id IN (SELECT NVL(paf.assignment_id,0)
5656 FROM per_all_assignments_f paf, hr_assignment_sets has,
5657 hr_assignment_set_amendments hasa
5658 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
5659 FROM psp_report_template_details_h prtd
5660 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5661 AND criteria_lookup_code='AST'
5662 AND include_exclude_flag='E'
5663 AND request_id =p_request_id)
5664 AND ( (paf.payroll_id = has.payroll_id
5665 AND paf.assignment_type = 'E'
5666 AND paf.effective_start_date <= p_effort_end
5667 AND paf.effective_end_date >= p_effort_start
5668 AND has.assignment_set_id = hasa.assignment_set_id)
5669 OR (paf.assignment_id = hasa.assignment_id
5670 AND paf.assignment_type = 'E'
5671 AND paf.effective_start_date <= p_effort_end
5672 AND paf.effective_end_date >= p_effort_start
5673 AND hasa.assignment_set_id=has.assignment_set_id
5674 AND include_or_exclude ='I'))
5675 AND NOT EXISTS (SELECT assignment_id
5676 FROM hr_assignment_set_amendments hasa
5677 WHERE hasa.assignment_id = paf.assignment_id
5678 AND paf.assignment_type = 'E'
5679 AND hasa.include_or_exclude ='E'
5680 AND paf.effective_start_date <= p_effort_end
5681 AND paf.effective_end_date >= p_effort_start));
5682 ELSIF template_sel_criteria.array_sel_criteria(i)='PPG' THEN
5683 OPEN ppg_cursor;
5684 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
5685 IF l_criteria_value1 IS NOT NULL THEN
5686 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 || '''' ;
5687
5688 LOOP
5689 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
5690 EXIT WHEN PPG_CURSOR%NOTFOUND;
5691
5692 g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 || ' = ' ||
5693 '''' || l_criteria_value2||'''';
5694 l_sql_string:= g_exec_string;
5695 END LOOP;
5696 CLOSE ppg_cursor;
5697
5698 IF l_sql_string IS NOT NULL THEN
5699 g_exec_string := 'DELETE FROM psp_selected_persons_t
5700 WHERE assignment_id IN (SELECT assignment_id
5701 FROM per_assignments_f paf,
5702 pay_people_groups ppg
5703 WHERE paf.people_group_id= ppg.people_group_id
5704 AND paf.assignment_type = ''' || 'E' || '''
5705 AND paf.effective_end_date >= :p_effort_Start
5706 AND paf.effective_start_date <= :p_effort_end
5707 AND ppg.people_group_id IN (SELECT people_group_id
5708 FROM pay_people_groups
5709 WHERE ' || l_sql_string || '))
5710 AND request_id = :request_id';
5711
5712 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_start, p_effort_end,
5713 p_request_id;
5714 END IF;
5715 END IF;
5716 ELSIF template_sel_criteria.array_sel_criteria(i)='GLA' THEN
5717 OPEN gla_cursor;
5718 FETCH gla_cursor INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
5719 IF l_criteria_value1 IS NOT NULL THEN
5720 l_sql_string := l_criteria_value1 ||' BETWEEN '|| '''' || l_criteria_value2 ||
5721 '''' || ' AND ' || '''' || l_criteria_value3 || '''' ;
5722 LOOP
5723 FETCH gla_cursor INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
5724 EXIT WHEN gla_cursor%NOTFOUND;
5725
5726 g_exec_string:= l_sql_string || ' OR ' || l_criteria_value1 ||
5727 ' BETWEEN ' || ''''|| l_criteria_value2 || '''' ||
5728 ' AND ' || ''''|| l_criteria_value3 || '''' ;
5729 l_sql_string:= g_exec_string;
5730 END LOOP;
5731 CLOSE gla_cursor;
5732
5733 IF l_sql_string IS NOT NULL THEN
5734 g_exec_string := 'DELETE FROM psp_selected_persons_t
5735 WHERE assignment_id IN (SELECT NVL(psl.assignment_id,0)
5736 FROM psp_summary_lines psl,
5737 psp_distribution_lines_history pdnh,
5738 psp_adjustment_lines_history palh,
5739 psp_pre_gen_dist_lines_history ppg,
5740 gl_code_combinations gcc
5741 WHERE gcc.code_combination_id= psl.gl_code_combination_id
5742 AND psl.business_group_id = '|| p_business_group_id || '
5743 AND psl.set_of_books_id = ' || p_set_of_books_id || '
5744 AND psl.summary_line_id = pdnh.summary_line_id(+)
5745 AND psl.summary_line_id = ppg.summary_line_id(+)
5746 AND psl.summary_line_id = palh.summary_line_id(+)
5747 AND psl.status_code='||''''||'A'||''''||'
5748 AND ( (psl.source_type IN ('||''''||'N'||''''||
5749 ' ,'|| ''''|| 'O'||''''||')
5750 AND pdnh.distribution_date BETWEEN
5751 :p_effort_start AND :p_effort_end
5752 AND pdnh.reversal_entry_flag IS NULL
5753 AND psl.summary_line_id =pdnh.summary_line_id
5754 AND pdnh.adjustment_batch_name IS NULL)
5755 OR (psl.source_type= '||''''||'P'||''''||'
5756 AND ppg.distribution_date BETWEEN
5757 :p_effort_start AND :p_effort_end
5758 AND ppg.adjustment_batch_name IS NULL
5759 AND ppg.summary_line_id =psl.summary_line_id
5760 AND ppg.reversal_entry_flag IS NULL)
5761 OR (psl.source_type= '||''''||'A'||''''||'
5762 AND palh.summary_line_id =psl.summary_line_id
5763 AND palh.adjustment_batch_name IS NULL
5764 AND NVL(palh.original_line_flag, ' ||
5765 ''''|| 'N' || ''''|| ') = '||''''||
5766 'N' || '''' || '
5767 AND palh.distribution_date BETWEEN
5768 :p_effort_start AND :p_effort_end))
5769 AND gcc.code_combination_id= psl.gl_code_combination_id
5770 AND gcc.code_combination_id IN (SELECT code_combination_id
5771 FROM gl_code_combinations
5772 WHERE ' || l_sql_string || '))
5773 AND request_id = :request_id';
5774
5775 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_start, p_effort_end,
5776 p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_request_id;
5777 END IF;
5778 END IF;
5779 END IF;
5780 END IF;
5781 END LOOP;
5782 END APPLY_ASG_EXCLUSION_CRITERIA;
5783
5784 END;
[Home] [Help]