1 PACKAGE BODY PSP_TEMPLATE_SELECTION AS
2 /* $Header: PSPTPLSLB.pls 120.23 2012/03/08 08:30:44 pnshukla ship $*/
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(60); --- Bug 8257434
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(60); -- Bug 8257434
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
1808 --Bug 8222520
1809
1810
1811 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
1812 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
1813 FROM per_all_people_f ppf,
1814 per_assignments_f paf,
1815 per_assignment_status_types past
1816 WHERE ppf.person_id IN (select person_id from per_people_f where
1817 business_group_id = p_business_group_id and
1818 effective_start_date <= p_effort_end and
1819 effective_end_date >= p_effort_start)
1820 AND paf.assignment_type = 'E'
1821 AND paf.person_id = ppf.person_id
1822 AND ppf.effective_start_date <= p_effort_end
1823 AND ppf.effective_end_date >= p_effort_start
1824 AND paf.effective_start_date <= p_effort_end
1825 AND paf.effective_end_date >= p_effort_start
1826 AND paf.assignment_status_type_id = past.assignment_status_type_id
1827 AND ( past.per_system_status <> 'TERM_ASSIGN'
1828 OR EXISTS ( select null
1829 FROM psp_pre_gen_dist_lines_history
1830 WHERE distribution_date between p_effort_start and p_effort_end
1831 AND assignment_id = paf.assignment_id
1832 AND reversal_entry_flag IS NULL
1833 AND rownum=1 )
1834 OR EXISTS (SELECT null
1835 FROM psp_distribution_lines_history pdlh
1836 , psp_summary_lines psl
1837 WHERE pdlh.summary_line_id = psl.summary_line_id
1838 AND distribution_date between p_effort_start and p_effort_end
1839 AND psl.person_id = paf.person_id
1840 AND psl.assignment_id = paf.assignment_id
1841 AND reversal_entry_flag IS NULL
1842 AND rownum=1));
1843
1844
1845 END IF;
1846
1847
1848 COMMIT;
1849 END;
1850
1851
1852
1853 PROCEDURE prune_initial_person_list(p_request_id IN NUMBER , p_effort_start IN DATE, p_effort_end IN DATE,
1854 p_business_group_id IN NUMBER, p_Set_of_books_id IN NUMBER)
1855 IS
1856
1857
1858 CURSOR get_all_selection_criteria(p_request_id IN NUMBER) is
1859 SELECT distinct criteria_lookup_code,
1860 include_exclude_flag from
1861 psp_report_template_details_h where request_id = p_request_id and
1862 criteria_lookup_type='PSP_SELECTION_CRITERIA' ORDER BY include_exclude_flag;
1863
1864
1865 CURSOR PPG_CURSOR IS
1866 select criteria_value1, criteria_value2 from
1867 psp_report_template_details_h where request_id = p_request_id and
1868 criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG' and include_exclude_flag='I';
1869
1870
1871 CURSOR GLA_CURSOR IS
1872 select criteria_value1 , criteria_value2, criteria_value3 from
1873 psp_report_template_details_h where request_id = p_request_id and
1874 criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA' and include_exclude_flag='I';
1875
1876
1877 type t_varchar_30_type is TABLE Of VARCHAR2(30) INDEX BY BINARY_INTEGER;
1878 type t_varchar_1_type is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
1879
1880 l_criteria_value1 varchar2(30);
1881
1882 l_criteria_value2 varchar2(60); -- Bug 8257434
1883
1884
1885 l_criteria_value3 varchar2(30);
1886
1887
1888
1889 l_sql_string varchar2(1000);
1890
1891
1892 i number;
1893
1894 BEGIN
1895 open get_all_selection_criteria(p_request_id);
1896 fetch get_all_selection_criteria BULK COLLECT into template_sel_criteria.array_sel_criteria,
1897 template_sel_criteria.array_inc_exc_flag;
1898
1899 close get_all_selection_criteria;
1900
1901
1902
1903 for i in 1..template_sel_criteria.array_sel_criteria.count
1904
1905 LOOP
1906
1907 IF template_sel_criteria.array_inc_exc_flag(i) = 'I' THEN
1908
1909
1910 IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code then
1911 IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
1912
1913 DELETE FROM psp_selected_persons_t pspt
1914 WHERE request_id = p_request_id
1915 /* Bug 5087294 : Performance fix replacing not in with not exists */
1916 -- AND person_id NOT IN (SELECT NVL( person_id, 0)
1917 AND NOT EXISTS ( SELECT 1
1918 FROM per_people_f ppf,
1919 psp_report_template_details_h prtd,
1920 per_assignments_f paf,
1921 per_assignment_status_types past
1922 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
1923 AND criteria_lookup_code='PTY'
1924 AND paf.person_id = ppf.person_id
1925 AND paf.assignment_type = 'E'
1926 AND paf.effective_start_date <= p_effort_end
1927 AND paf.effective_end_date >= p_effort_start
1928 AND ppf.effective_start_date <= p_effort_end
1929 AND ppf.effective_end_date >= p_effort_start
1930 AND include_exclude_flag='I'
1931 AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
1932 AND prtd.request_id = p_request_id
1933 AND ppf.person_id = pspt.person_id
1934 AND paf.assignment_status_type_id = past.assignment_status_type_id
1935 AND ( past.per_system_status <> 'TERM_ASSIGN'
1936 OR EXISTS ( select null
1937 FROM psp_pre_gen_dist_lines_history
1938 WHERE distribution_date between p_effort_start and p_effort_end
1939 AND assignment_id = paf.assignment_id
1940 AND reversal_entry_flag IS NULL
1941 AND rownum=1 )
1942 OR EXISTS (SELECT null
1943 FROM psp_distribution_lines_history pdlh
1944 , psp_summary_lines psl
1945 WHERE pdlh.summary_line_id = psl.summary_line_id
1946 AND distribution_date between p_effort_start and p_effort_end
1947 AND psl.person_id = paf.person_id
1948 AND psl.assignment_id = paf.assignment_id
1949 AND reversal_entry_flag IS NULL
1950 AND rownum=1)));
1951
1952 ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
1953
1954 DELETE FROM psp_selected_persons_t pspt
1955 WHERE request_id = p_request_id
1956 /* Bug 5087294 : Performance fix replacing not in with not exists */
1957 -- AND person_id NOT IN (SELECT NVL(person_id,0)
1958 AND NOT EXISTS ( SELECT 1
1959 FROM per_all_people_f ppf,
1960 per_assignments_f paf,
1961 per_assignment_status_types past
1962 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
1963 FROM psp_report_template_details_h prtd
1964 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
1965 AND criteria_lookup_code='EMP'
1966 AND include_exclude_flag='I'
1967 AND prtd.request_id = p_request_id)
1968 AND paf.person_id = ppf.person_id
1969 AND paf.assignment_type = 'E'
1970 AND paf.effective_start_date <= p_effort_end
1971 AND paf.effective_end_date >= p_effort_start
1972 AND ppf.effective_start_date <= p_effort_end
1973 AND ppf.effective_end_date >= p_effort_start
1974 AND ppf.person_id = pspt.person_id
1975 AND paf.assignment_status_type_id = past.assignment_status_type_id
1976 AND ( past.per_system_status <> 'TERM_ASSIGN'
1977 OR EXISTS ( select null
1978 FROM psp_pre_gen_dist_lines_history
1979 WHERE distribution_date between p_effort_start and p_effort_end
1980 AND assignment_id = paf.assignment_id
1981 AND reversal_entry_flag IS NULL
1982 AND rownum=1 )
1983 OR EXISTS (SELECT null
1984 FROM psp_distribution_lines_history pdlh
1985 , psp_summary_lines psl
1986 WHERE pdlh.summary_line_id = psl.summary_line_id
1987 AND distribution_date between p_effort_start and p_effort_end
1988 AND psl.person_id = paf.person_id
1989 AND psl.assignment_id = paf.assignment_id
1990 AND reversal_entry_flag IS NULL
1991 AND rownum=1)));
1992
1993
1994 ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
1995
1996 DELETE FROM psp_selected_persons_t pspt
1997 WHERE request_id = p_request_id
1998 /* Bug 5087294 : Performance fix replacing not in with not exists */
1999 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2000 AND NOT EXISTS ( SELECT 1
2001 FROM per_all_assignments_f paf,
2002 psp_report_template_details_h prtd,
2003 per_assignment_status_types past
2004 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2005 AND criteria_lookup_code='SUP'
2006 AND include_exclude_flag='I'
2007 AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
2008 AND prtd.request_id = p_request_id
2009 AND paf.assignment_type = 'E'
2010 AND paf.effective_start_date <= p_effort_end
2011 AND effective_end_date >= p_effort_start
2012 AND paf.person_id = pspt.person_id
2013 AND paf.assignment_status_type_id = past.assignment_status_type_id
2014 AND ( past.per_system_status <> 'TERM_ASSIGN'
2015 OR EXISTS ( select null
2016 FROM psp_pre_gen_dist_lines_history
2017 WHERE distribution_date between p_effort_start and p_effort_end
2018 AND assignment_id = paf.assignment_id
2019 AND reversal_entry_flag IS NULL
2020 AND rownum=1 )
2021 OR EXISTS (SELECT null
2022 FROM psp_distribution_lines_history pdlh
2023 , psp_summary_lines psl
2024 WHERE pdlh.summary_line_id = psl.summary_line_id
2025 AND distribution_date between p_effort_start and p_effort_end
2026 AND psl.person_id = paf.person_id
2027 AND psl.assignment_id = paf.assignment_id
2028 AND reversal_entry_flag IS NULL
2029 AND rownum=1)));
2030
2031
2032 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
2033
2034
2035 --- replaced original query for performance issues -- 4429787
2036 delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2037 select psl.person_id
2038 FROM psp_summary_lines psl,
2039 psp_report_template_details_h prtd ,
2040 per_time_periods ptp
2041 WHERE
2042 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2043 prtd.criteria_lookup_code='AWD' AND
2044 prtd.include_exclude_flag='I' AND
2045 prtd.request_id =p_request_id AND
2046 psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
2047 psl.business_group_id = p_business_group_id AND
2048 psl.set_of_books_id = p_set_of_books_id AND
2049 psl.status_code= 'A' AND
2050 ptp.time_period_id = psl.time_period_id AND
2051 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2052 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2053 WHERE pdnh.summary_line_id = psl.summary_line_id
2054 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2055 AND pdnh.reversal_entry_flag IS NULL
2056 AND pdnh.adjustment_batch_name IS NULL )
2057 OR EXISTS
2058 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2059 WHERE ppg.summary_line_id = psl.summary_line_id
2060 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2061 AND ppg.adjustment_batch_name IS NULL AND
2062 ppg.reversal_entry_flag IS NULL)
2063 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2064 WHERE palh.summary_line_id = psl.summary_line_id
2065 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2066 AND palh.adjustment_batch_name IS NULL
2067 AND palh.reversal_entry_flag IS NULL
2068 AND NVL(palh.original_line_flag, 'N') ='N')));
2069
2070 ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' then
2071
2072
2073 --- replaced non-performant delete with this for 4429787
2074 DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2075 SELECT NVL(psl.person_id,0)
2076 FROM psp_summary_lines psl,
2077 psp_report_template_details_h prtd ,
2078 gms_awards_all gaa ,
2079 per_time_periods ptp
2080 WHERE psl.award_id = gaa.award_id AND
2081 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2082 prtd.criteria_lookup_code='ATY' AND
2083 prtd.include_exclude_flag='I' AND
2084 prtd.request_id =p_request_id AND
2085 gaa.type=prtd.criteria_value1 AND
2086 psl.business_group_id = p_business_group_id AND
2087 psl.set_of_books_id = p_set_of_books_id AND
2088 psl.status_code= 'A' AND
2089 ptp.time_period_id = psl.time_period_id AND
2090 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2091 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2092 WHERE pdnh.summary_line_id = psl.summary_line_id
2093 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2094 AND pdnh.reversal_entry_flag IS NULL
2095 AND pdnh.adjustment_batch_name IS NULL )
2096 OR EXISTS
2097 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2098 WHERE ppg.summary_line_id = psl.summary_line_id
2099 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2100 AND ppg.adjustment_batch_name IS NULL AND
2101 ppg.reversal_entry_flag IS NULL)
2102 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2103 WHERE palh.summary_line_id = psl.summary_line_id
2104 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2105 AND palh.adjustment_batch_name IS NULL
2106 AND palh.reversal_entry_flag IS NULL AND
2107 NVL(palh.original_line_flag, 'N') ='N')));
2108
2109 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
2110
2111 --- replaced non-performant delete with this for 4429787
2112 DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id NOT IN (
2113 SELECT NVL(psl.person_id,0)
2114 FROM psp_summary_lines psl,
2115 psp_report_template_details_h prtd ,
2116 pa_projects_all ppa ,
2117 per_time_periods ptp
2118 WHERE psl.project_id = ppa.project_id AND
2119 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2120 prtd.criteria_lookup_code='PRT' AND
2121 prtd.include_exclude_flag='I' AND
2122 prtd.request_id =p_request_id AND
2123 ppa.project_type=prtd.criteria_value1 AND
2124 psl.business_group_id = p_business_group_id AND
2125 psl.set_of_books_id = p_set_of_books_id AND
2126 psl.status_code= 'A' AND
2127 ptp.time_period_id = psl.time_period_id AND
2128 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2129 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2130 WHERE pdnh.summary_line_id = psl.summary_line_id
2131 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2132 AND pdnh.reversal_entry_flag IS NULL
2133 AND pdnh.adjustment_batch_name IS NULL )
2134 OR EXISTS
2135 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2136 WHERE ppg.summary_line_id = psl.summary_line_id
2137 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2138 AND ppg.adjustment_batch_name IS NULL AND
2139 ppg.reversal_entry_flag IS NULL)
2140 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2141 WHERE palh.summary_line_id = psl.summary_line_id
2142 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2143 AND palh.adjustment_batch_name IS NULL
2144 AND palh.reversal_entry_flag IS NULL AND
2145 NVL(palh.original_line_flag, 'N') ='N')));
2146
2147 elsif template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
2148
2149 --- replaced non-performant insert with this for 4429787
2150 delete from psp_selected_persons_t where request_id = p_request_id AND person_id not in (
2151 select nvl( psl.person_id ,0)
2152 FROM psp_summary_lines psl,
2153 psp_report_template_details_h prtd ,
2154 per_time_periods ptp
2155 WHERE
2156 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2157 prtd.criteria_lookup_code='PRJ' AND
2158 prtd.include_exclude_flag='I' AND
2159 prtd.request_id =p_request_id AND
2160 psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
2161 psl.business_group_id = p_business_group_id AND
2162 psl.set_of_books_id = p_set_of_books_id AND
2163 psl.status_code= 'A' AND
2164 ptp.time_period_id = psl.time_period_id AND
2165 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2166 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2167 WHERE pdnh.summary_line_id = psl.summary_line_id
2168 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2169 AND pdnh.reversal_entry_flag IS NULL
2170 AND pdnh.adjustment_batch_name IS NULL )
2171 OR EXISTS
2172 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2173 WHERE ppg.summary_line_id = psl.summary_line_id
2174 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2175 AND ppg.adjustment_batch_name IS NULL AND
2176 ppg.reversal_entry_flag IS NULL)
2177 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2178 WHERE palh.summary_line_id = psl.summary_line_id
2179 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2180 AND palh.adjustment_batch_name IS NULL
2181 AND palh.reversal_entry_flag IS NULL AND
2182 NVL(palh.original_line_flag, 'N') ='N')));
2183
2184 elsif template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
2185
2186 DELETE FROM psp_selected_persons_t pspt
2187 WHERE request_id = p_request_id
2188 /* Bug 5087294 : Performance fix replacing not in with not exists */
2189 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2190 AND NOT EXISTS ( SELECT 1
2191 FROM per_assignments_f paf,
2192 psp_report_template_details_h prtd,
2193 per_assignment_status_types past
2194 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2195 AND criteria_lookup_code='PAY'
2196 AND include_exclude_flag='I'
2197 AND paf.assignment_type = 'E'
2198 AND effective_start_date <= p_effort_end
2199 AND effective_end_date >= p_effort_start
2200 AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
2201 AND prtd.request_id = p_request_id
2202 AND paf.person_id = pspt.person_id
2203 AND paf.assignment_status_type_id = past.assignment_status_type_id
2204 AND ( past.per_system_status <> 'TERM_ASSIGN'
2205 OR EXISTS ( select null
2206 FROM psp_pre_gen_dist_lines_history
2207 WHERE distribution_date between p_effort_start and p_effort_end
2208 AND assignment_id = paf.assignment_id
2209 AND reversal_entry_flag IS NULL
2210 AND rownum=1 )
2211 OR EXISTS (SELECT null
2212 FROM psp_distribution_lines_history pdlh
2213 , psp_summary_lines psl
2214 WHERE pdlh.summary_line_id = psl.summary_line_id
2215 AND distribution_date between p_effort_start and p_effort_end
2216 AND psl.person_id = paf.person_id
2217 AND psl.assignment_id = paf.assignment_id
2218 AND reversal_entry_flag IS NULL
2219 AND rownum=1)));
2220
2221
2222
2223 elsif template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
2224
2225 DELETE FROM psp_selected_persons_t pspt
2226 WHERE request_id = p_request_id
2227 /* Bug 5087294 : Performance fix replacing not in with not exists */
2228 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2229 AND NOT EXISTS ( SELECT 1
2230 FROM per_assignments_f paf ,
2231 psp_report_template_details_h prtd,
2232 per_assignment_status_types past
2233 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2234 AND criteria_lookup_code='LOC'
2235 AND paf.assignment_type = 'E'
2236 AND effective_start_date <= p_effort_end
2237 AND effective_end_date >= p_effort_start
2238 AND include_exclude_flag='I'
2239 AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
2240 AND prtd.request_id = p_request_id
2241 AND paf.person_id = pspt.person_id
2242 AND paf.assignment_status_type_id = past.assignment_status_type_id
2243 AND ( past.per_system_status <> 'TERM_ASSIGN'
2244 OR EXISTS ( select null
2245 FROM psp_pre_gen_dist_lines_history
2246 WHERE distribution_date between p_effort_start and p_effort_end
2247 AND assignment_id = paf.assignment_id
2248 AND reversal_entry_flag IS NULL
2249 AND rownum=1 )
2250 OR EXISTS (SELECT null
2251 FROM psp_distribution_lines_history pdlh
2252 , psp_summary_lines psl
2253 WHERE pdlh.summary_line_id = psl.summary_line_id
2254 AND distribution_date between p_effort_start and p_effort_end
2255 AND psl.person_id = paf.person_id
2256 AND psl.assignment_id = paf.assignment_id
2257 AND reversal_entry_flag IS NULL
2258 AND rownum=1)));
2259
2260 elsif template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
2261
2262 DELETE FROM psp_selected_persons_t pspt
2263 WHERE request_id = p_request_id
2264 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2265 AND NOT EXISTS ( SELECT 1
2266 FROM per_assignments_f paf ,
2267 psp_report_template_details_h prtd,
2268 per_assignment_status_types past
2269 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2270 AND criteria_lookup_code='ORG'
2271 AND include_exclude_flag='I'
2272 AND paf.assignment_type = 'E'
2273 AND effective_start_date <= p_effort_end
2274 AND effective_end_date >= p_effort_start
2275 AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
2276 AND prtd.request_id = p_request_id
2277 AND paf.person_id = pspt.person_id
2278 AND paf.assignment_status_type_id = past.assignment_status_type_id
2279 AND ( past.per_system_status <> 'TERM_ASSIGN'
2280 OR EXISTS ( select null
2281 FROM psp_pre_gen_dist_lines_history
2282 WHERE distribution_date between p_effort_start and p_effort_end
2283 AND assignment_id = paf.assignment_id
2284 AND reversal_entry_flag IS NULL
2285 AND rownum=1 )
2286 OR EXISTS (SELECT null
2287 FROM psp_distribution_lines_history pdlh
2288 , psp_summary_lines psl
2289 WHERE pdlh.summary_line_id = psl.summary_line_id
2290 AND distribution_date between p_effort_start and p_effort_end
2291 AND psl.person_id = paf.person_id
2292 AND psl.assignment_id = paf.assignment_id
2293 AND reversal_entry_flag IS NULL
2294 AND rownum=1)));
2295
2296
2297 elsif template_sel_criteria.array_sel_criteria(i)='CST' THEN
2298 DELETE FROM psp_selected_persons_t pspt
2299 WHERE request_id = p_request_id
2300 /* Bug 5087294 : Performance fix replacing not in with not exists */
2301 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2302 AND NOT EXISTS ( SELECT 1
2303 FROM per_assignments_f paf,
2304 pay_payrolls_f ppf,
2305 per_assignment_status_types past
2306 WHERE ppf.payroll_id = paf.payroll_id
2307 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
2308 FROM psp_report_template_details_h prtd
2309 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2310 AND criteria_lookup_code='CST'
2311 AND include_exclude_flag='I'
2312 AND request_id = p_request_id)
2313 AND paf.assignment_type = 'E'
2314 AND ppf.effective_start_date <= p_effort_end
2315 AND ppf.effective_end_date >= p_effort_start
2316 AND paf.effective_start_date <= p_effort_end
2317 AND paf.effective_end_date >= p_effort_start
2318 AND paf.person_id = pspt.person_id
2319 AND paf.assignment_status_type_id = past.assignment_status_type_id
2320 AND ( past.per_system_status <> 'TERM_ASSIGN'
2321 OR EXISTS ( select null
2322 FROM psp_pre_gen_dist_lines_history
2323 WHERE distribution_date between p_effort_start and p_effort_end
2324 AND assignment_id = paf.assignment_id
2325 AND reversal_entry_flag IS NULL
2326 AND rownum=1 )
2327 OR EXISTS (SELECT null
2328 FROM psp_distribution_lines_history pdlh
2329 , psp_summary_lines psl
2330 WHERE pdlh.summary_line_id = psl.summary_line_id
2331 AND distribution_date between p_effort_start and p_effort_end
2332 AND psl.person_id = paf.person_id
2333 AND psl.assignment_id = paf.assignment_id
2334 AND reversal_entry_flag IS NULL
2335 AND rownum=1)));
2336
2337
2338 elsif template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
2339
2340 DELETE FROM psp_selected_persons_t
2341 WHERE request_id = p_request_id
2342 AND person_id NOT IN (SELECT NVL(paf.person_id,0)
2343 FROM per_all_assignments_f paf,
2344 hr_assignment_sets has,
2345 hr_assignment_set_amendments hasa,
2346 per_assignment_status_types past
2347 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
2348 FROM psp_report_template_details_h prtd
2349 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2350 AND criteria_lookup_code='AST'
2351 AND include_exclude_flag='I'
2352 AND request_id =p_request_id)
2353 AND ( (paf.payroll_id = has.payroll_id
2354 AND paf.assignment_type = 'E'
2355 AND paf.effective_start_date <= p_effort_end
2356 AND paf.effective_end_date >= p_effort_start
2357 AND has.assignment_set_id = hasa.assignment_set_id)
2358 OR (paf.assignment_id = hasa.assignment_id
2359 AND paf.assignment_type = 'E'
2360 AND paf.effective_start_date <= p_effort_end
2361 AND paf.effective_end_date >= p_effort_start
2362 AND hasa.assignment_set_id=has.assignment_set_id
2363 AND include_or_exclude ='I'))
2364 AND NOT EXISTS (SELECT assignment_id
2365 FROM hr_assignment_set_amendments hasa
2366 WHERE hasa.assignment_id = paf.assignment_id
2367 AND paf.assignment_type = 'E'
2368 AND hasa.include_or_exclude ='E'
2369 AND paf.effective_start_date <= p_effort_end
2370 AND paf.effective_end_date >= p_effort_start)
2371 AND paf.assignment_status_type_id = past.assignment_status_type_id
2372 AND ( past.per_system_status <> 'TERM_ASSIGN'
2373 OR EXISTS ( select null
2374 FROM psp_pre_gen_dist_lines_history
2375 WHERE distribution_date between p_effort_start and p_effort_end
2376 AND assignment_id = paf.assignment_id
2377 AND reversal_entry_flag IS NULL
2378 AND rownum=1 )
2379 OR EXISTS (SELECT null
2380 FROM psp_distribution_lines_history pdlh
2381 , psp_summary_lines psl
2382 WHERE pdlh.summary_line_id = psl.summary_line_id
2383 AND distribution_date between p_effort_start and p_effort_end
2384 AND psl.person_id = paf.person_id
2385 AND psl.assignment_id = paf.assignment_id
2386 AND reversal_entry_flag IS NULL
2387 AND rownum=1)));
2388
2389
2390 elsif template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
2391
2392 DELETE FROM psp_selected_persons_t pspt
2393 WHERE request_id = p_request_id
2394 /* Bug 5087294 : Performance fix replacing not in with not exists */
2395 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2396 AND NOT EXISTS ( SELECT 1
2397 FROM per_assignments_f paf ,
2398 psp_report_template_details_h prtd,
2399 per_assignment_status_types past
2400 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2401 AND criteria_lookup_code='JOB'
2402 AND include_exclude_flag='I'
2403 AND paf.assignment_type = 'E'
2404 AND effective_start_date <= p_effort_end
2405 AND effective_end_date >= p_effort_start
2406 AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
2407 AND prtd.request_id = p_request_id
2408 AND paf.person_id = pspt.person_id
2409 AND paf.assignment_status_type_id = past.assignment_status_type_id
2410 AND ( past.per_system_status <> 'TERM_ASSIGN'
2411 OR EXISTS ( select null
2412 FROM psp_pre_gen_dist_lines_history
2413 WHERE distribution_date between p_effort_start and p_effort_end
2414 AND assignment_id = paf.assignment_id
2415 AND reversal_entry_flag IS NULL
2416 AND rownum=1 )
2417 OR EXISTS (SELECT null
2418 FROM psp_distribution_lines_history pdlh
2419 , psp_summary_lines psl
2420 WHERE pdlh.summary_line_id = psl.summary_line_id
2421 AND distribution_date between p_effort_start and p_effort_end
2422 AND psl.person_id = paf.person_id
2423 AND psl.assignment_id = paf.assignment_id
2424 AND reversal_entry_flag IS NULL
2425 AND rownum=1)));
2426
2427 elsif template_sel_criteria.array_sel_criteria(i) ='POS' THEN
2428
2429 DELETE FROM psp_selected_persons_t pspt
2430 WHERE request_id = p_request_id
2431 /* Bug 5087294 : Performance fix replacing not in with not exists */
2432 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2433 AND NOT EXISTS ( SELECT 1
2434 FROM per_assignments_f paf ,
2435 psp_report_template_details_h prtd,
2436 per_assignment_status_types past
2437 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2438 AND criteria_lookup_code='POS'
2439 AND paf.assignment_type = 'E'
2440 and
2441 effective_start_date <= p_effort_end and
2442 effective_end_date >= p_effort_start
2443 AND include_exclude_flag='I'
2444 AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
2445 AND prtd.request_id = p_request_id
2446 AND paf.person_id = pspt.person_id
2447 AND paf.assignment_status_type_id = past.assignment_status_type_id
2448 AND ( past.per_system_status <> 'TERM_ASSIGN'
2449 OR EXISTS ( select null
2450 FROM psp_pre_gen_dist_lines_history
2451 WHERE distribution_date between p_effort_start and p_effort_end
2452 AND assignment_id = paf.assignment_id
2453 AND reversal_entry_flag IS NULL
2454 AND rownum=1 )
2455 OR EXISTS (SELECT null
2456 FROM psp_distribution_lines_history pdlh
2457 , psp_summary_lines psl
2458 WHERE pdlh.summary_line_id = psl.summary_line_id
2459 AND distribution_date between p_effort_start and p_effort_end
2460 AND psl.person_id = paf.person_id
2461 AND psl.assignment_id = paf.assignment_id
2462 AND reversal_entry_flag IS NULL
2463 AND rownum=1)));
2464
2465 elsif template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
2466
2467
2468 DELETE FROM psp_selected_persons_t pspt
2469 WHERE request_id = p_request_id
2470 /* Bug 5087294 : Performance fix replacing not in with not exists */
2471 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
2472 AND NOT EXISTS ( SELECT 1
2473 FROM per_assignments_f paf ,
2474 psp_report_template_details_h prtd,
2475 per_assignment_status_types past
2476 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2477 AND criteria_lookup_code='ASS'
2478 AND include_exclude_flag='I'
2479 AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
2480 AND prtd.request_id = p_request_id
2481 AND paf.assignment_type = 'E'
2482 AND effective_start_date <= p_effort_end
2483 AND effective_end_date >= p_effort_start
2484 AND paf.person_id = pspt.person_id
2485 AND paf.assignment_status_type_id = past.assignment_status_type_id
2486 AND ( past.per_system_status <> 'TERM_ASSIGN'
2487 OR EXISTS ( select null
2488 FROM psp_pre_gen_dist_lines_history
2489 WHERE distribution_date between p_effort_start and p_effort_end
2490 AND assignment_id = paf.assignment_id
2491 AND reversal_entry_flag IS NULL
2492 AND rownum=1 )
2493 OR EXISTS (SELECT null
2494 FROM psp_distribution_lines_history pdlh
2495 , psp_summary_lines psl
2496 WHERE pdlh.summary_line_id = psl.summary_line_id
2497 AND distribution_date between p_effort_start and p_effort_end
2498 AND psl.person_id = paf.person_id
2499 AND psl.assignment_id = paf.assignment_id
2500 AND reversal_entry_flag IS NULL
2501 AND rownum=1)));
2502
2503 /*
2504 elsif template_rec.array_sel_criteria(i)='EST' then
2505
2506 */
2507
2508 elsif template_sel_criteria.array_sel_criteria(i)='PPG' THEN
2509
2510 OPEN ppg_cursor;
2511
2512 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
2513 IF l_criteria_value1 is not null then
2514
2515
2516 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
2517
2518 -- g_exec_string:= l_sql_string;
2519
2520 LOOP
2521
2522 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
2523 EXIT WHEN PPG_CURSOR%NOTFOUND;
2524
2525 /*
2526 select l_sql_string
2527 || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 ||''''
2528 into g_exec_string from psp_report_template_details_h ;
2529
2530
2531 */
2532
2533
2534 g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 || ' = ' || '''' || l_criteria_value2||'''';
2535
2536
2537 l_sql_string:= g_exec_string;
2538
2539 END LOOP;
2540
2541 close ppg_cursor;
2542
2543
2544 if l_sql_string is not null then
2545 --- replaced non-performant delete stmnt for 4429787
2546
2547 g_exec_string := 'delete from psp_selected_persons_t sel where request_id = :request_id
2548 AND NOT EXISTS
2549 (SELECT 1 FROM per_assignments_f paf, pay_people_groups ppg, per_assignment_status_types past
2550 WHERE paf.people_group_id= ppg.people_group_id
2551 AND paf.assignment_type = ''' || 'E' || '''
2552 AND paf.effective_end_date >= :p_effort_Start AND
2553 paf.effective_start_date <= :p_effort_end
2554 AND (' || l_sql_string || ')
2555 AND paf.person_id = sel.person_id
2556 AND paf.assignment_status_type_id = past.assignment_status_type_id
2557 AND ( past.per_system_status <> ''' || 'TERM_ASSIGN' || '''
2558 OR EXISTS ( select null
2559 FROM psp_pre_gen_dist_lines_history
2560 WHERE distribution_date between :p_effort_start and :p_effort_end
2561 AND assignment_id = paf.assignment_id
2562 AND reversal_entry_flag IS NULL
2563 AND rownum=1 )
2564 OR EXISTS (SELECT null
2565 FROM psp_distribution_lines_history pdlh
2566 , psp_summary_lines psl
2567 WHERE pdlh.summary_line_id = psl.summary_line_id
2568 AND distribution_date between :p_effort_start and :p_effort_end
2569 AND psl.person_id = paf.person_id
2570 AND psl.assignment_id = paf.assignment_id
2571 AND reversal_entry_flag IS NULL
2572 AND rownum=1)))';
2573
2574
2575 --fnd_file.put_line(fnd_file.log , ' g_exec_string 3 is '||g_exec_string);
2576
2577 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;
2578 end if;
2579 end if;
2580
2581 elsif template_sel_criteria.array_sel_criteria(i)='GLA' then
2582
2583
2584 OPEN gla_cursor;
2585
2586 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
2587 IF l_criteria_value1 is not null then
2588
2589 l_sql_string := l_criteria_value1 ||' between '|| ''''|| l_criteria_value2 || '''' || ' and ' || '''' || l_criteria_value3 || '''';
2590 -- g_exec_string := l_sql_string;
2591
2592
2593 LOOP
2594
2595 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
2596 EXIT WHEN GLA_CURSOR%NOTFOUND;
2597
2598 g_exec_string:= l_sql_string
2599 || ' OR ' || l_criteria_value1 || ' between ' || ''''|| l_criteria_value2 || '''' ||
2600 ' and ' || ''''|| l_criteria_value3 || '''' ;
2601 l_sql_string:=g_exec_string;
2602
2603 END LOOP;
2604 close gla_cursor;
2605
2606 -- l_sql_string:= g_exec_string;
2607
2608 if l_sql_string is not null then
2609
2610 g_exec_string := 'delete from psp_selected_persons_t where person_id
2611 not in (select nvl(psl.person_id,0) from psp_summary_lines psl,
2612 psp_distribution_lines_history pdnh,
2613 psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
2614 gl_code_combinations gcc
2615 where gcc.code_combination_id= psl.gl_code_combination_id and
2616 psl.business_group_id = '|| p_business_group_id || ' and
2617 psl.set_of_books_id = ' || p_set_of_books_id || ' and
2618 psl.summary_line_id = pdnh.summary_line_id(+) and
2619 psl.summary_line_id = ppg.summary_line_id(+) and
2620 psl.summary_line_id = palh.summary_line_id(+) and
2621 psl.status_code='||''''||'A'||''''||' and
2622 ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
2623 pdnh.distribution_date between :p_effort_start and :p_effort_end
2624 and pdnh.reversal_entry_flag is null
2625 and psl.summary_line_id = pdnh.summary_line_id
2626 and pdnh.adjustment_batch_name is null
2627 ) OR
2628 ( psl.source_type='||''''||'P'||''''||' and
2629 ppg.distribution_date between :p_effort_start and :p_effort_end and
2630 ppg.adjustment_batch_name is null and
2631 ppg.summary_line_id =psl.summary_line_id and
2632 ppg.reversal_entry_flag is null)
2633 OR (psl.source_type='||''''||'A'||''''||' and
2634 palh.summary_line_id =psl.summary_line_id and
2635 palh.adjustment_batch_name is null and
2636 NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
2637 || ' and palh.distribution_date between :p_effort_start and :p_effort_end )) and
2638 gcc.code_combination_id= psl.gl_code_combination_id and
2639 gcc.code_combination_id in (select code_combination_id from gl_code_combinations
2640 where ' || l_sql_string
2641 || ' )) and request_id = :request_id';
2642
2643 -- fnd_file.put_line(fnd_file.log , ' g_exec_string 3 is '||g_exec_string);
2644
2645 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;
2646
2647 end if;
2648 end if;
2649
2650
2651 end if;
2652
2653
2654 END IF;
2655
2656 END IF;
2657 END LOOP;
2658
2659 END;
2660
2661
2662 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
2663
2664 CURSOR PPG_CURSOR IS
2665 select criteria_value1, criteria_value2 from
2666 psp_report_template_details_h where request_id = p_request_id and
2667 criteria_lookup_type= 'PSP_SELECTION_CRITERIA' and criteria_lookup_code='PPG'
2668 and include_exclude_flag='E';
2669
2670
2671 CURSOR GLA_CURSOR IS
2672 select criteria_value1 , criteria_value2, criteria_value3 from
2673 psp_report_template_details_h where request_id = p_request_id and
2674 criteria_lookup_type ='PSP_SELECTION_CRITERIA' and criteria_lookup_code ='GLA'
2675 and include_exclude_flag='E';
2676
2677
2678 type t_varchar_30_type is TABLE Of VARCHAR2(30) INDEX BY BINARY_INTEGER;
2679 type t_varchar_1_type is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER;
2680
2681 l_criteria_value1 varchar2(30);
2682
2683 l_criteria_value2 varchar2(60); -- Bug 8257434
2684
2685
2686 l_criteria_value3 varchar2(30);
2687
2688 l_sql_string varchar2(1000);
2689
2690
2691 i number;
2692
2693 BEGIN
2694 for i in 1..template_sel_criteria.array_sel_criteria.count
2695
2696 LOOP
2697
2698 IF template_sel_criteria.array_inc_exc_flag(i) = 'E' THEN
2699
2700
2701 -- IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code THEN
2702
2703 IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
2704
2705 DELETE FROM psp_selected_persons_t
2706 WHERE request_id = p_request_id
2707 AND person_id IN (SELECT ppf.person_id
2708 FROM per_people_f ppf,
2709 psp_report_template_details_h prtd,
2710 per_assignments_f paf,
2711 per_assignment_status_types past
2712 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2713 AND criteria_lookup_code='PTY'
2714 AND paf.person_id = ppf.person_id
2715 AND paf.assignment_type = 'E'
2716 AND paf.effective_start_date <= p_effort_end
2717 AND paf.effective_end_date >= p_effort_start
2718 AND ppf.effective_start_date <= p_effort_end
2719 AND ppf.effective_end_date >= p_effort_start
2720 AND include_exclude_flag='E'
2721 AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
2722 AND prtd.request_id = p_request_id
2723 AND paf.assignment_status_type_id = past.assignment_status_type_id
2724 AND ( past.per_system_status <> 'TERM_ASSIGN'
2725 OR EXISTS ( select null
2726 FROM psp_pre_gen_dist_lines_history
2727 WHERE distribution_date between p_effort_start and p_effort_end
2728 AND assignment_id = paf.assignment_id
2729 AND reversal_entry_flag IS NULL
2730 AND rownum=1 )
2731 OR EXISTS (SELECT null
2732 FROM psp_distribution_lines_history pdlh
2733 , psp_summary_lines psl
2734 WHERE pdlh.summary_line_id = psl.summary_line_id
2735 AND distribution_date between p_effort_start and p_effort_end
2736 AND psl.person_id = paf.person_id
2737 AND psl.assignment_id = paf.assignment_id
2738 AND reversal_entry_flag IS NULL
2739 AND rownum=1)));
2740
2741
2742 ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
2743
2744 DELETE FROM psp_selected_persons_t
2745 WHERE request_id = p_request_id
2746 AND person_id IN (SELECT DISTINCT ppf.person_id
2747 FROM per_all_people_f ppf,
2748 per_assignments_f paf,
2749 per_assignment_status_types past
2750 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
2751 FROM psp_report_template_details_h prtd
2752 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
2753 AND criteria_lookup_code='EMP'
2754 AND include_exclude_flag='E'
2755 AND prtd.request_id = p_request_id)
2756 AND paf.person_id = ppf.person_id
2757 AND paf.assignment_type = 'E'
2758 AND paf.effective_start_date <= p_effort_end
2759 AND paf.effective_end_date >= p_effort_start
2760 AND ppf.effective_start_date <= p_effort_end
2761 AND ppf.effective_end_date >= p_effort_start
2762 AND paf.assignment_status_type_id = past.assignment_status_type_id
2763 AND ( past.per_system_status <> 'TERM_ASSIGN'
2764 OR EXISTS ( select null
2765 FROM psp_pre_gen_dist_lines_history
2766 WHERE distribution_date between p_effort_start and p_effort_end
2767 AND assignment_id = paf.assignment_id
2768 AND reversal_entry_flag IS NULL
2769 AND rownum=1 )
2770 OR EXISTS (SELECT null
2771 FROM psp_distribution_lines_history pdlh
2772 , psp_summary_lines psl
2773 WHERE pdlh.summary_line_id = psl.summary_line_id
2774 AND distribution_date between p_effort_start and p_effort_end
2775 AND psl.person_id = paf.person_id
2776 AND psl.assignment_id = paf.assignment_id
2777 AND reversal_entry_flag IS NULL
2778 AND rownum=1)));
2779
2780
2781 ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
2782
2783 DELETE FROM psp_selected_persons_t
2784 WHERE request_id = p_request_id
2785 AND person_id IN (SELECT NVL(person_id,0)
2786 FROM per_all_assignments_f paf,
2787 psp_report_template_details_h prtd,
2788 per_assignment_status_types past
2789 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2790 AND criteria_lookup_code='SUP'
2791 AND include_exclude_flag='E'
2792 AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
2793 AND prtd.request_id = p_request_id
2794 AND paf.assignment_type = 'E'
2795 AND paf.effective_start_date <= p_effort_end
2796 AND paf.effective_end_date >= p_effort_start
2797 AND paf.assignment_status_type_id = past.assignment_status_type_id
2798 AND ( past.per_system_status <> 'TERM_ASSIGN'
2799 OR EXISTS ( select null
2800 FROM psp_pre_gen_dist_lines_history
2801 WHERE distribution_date between p_effort_start and p_effort_end
2802 AND assignment_id = paf.assignment_id
2803 AND reversal_entry_flag IS NULL
2804 AND rownum=1 )
2805 OR EXISTS (SELECT null
2806 FROM psp_distribution_lines_history pdlh
2807 , psp_summary_lines psl
2808 WHERE pdlh.summary_line_id = psl.summary_line_id
2809 AND distribution_date between p_effort_start and p_effort_end
2810 AND psl.person_id = paf.person_id
2811 AND psl.assignment_id = paf.assignment_id
2812 AND reversal_entry_flag IS NULL
2813 AND rownum=1)));
2814
2815
2816 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
2817
2818 --- replaced original query for performance issues -- 4429787
2819 delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2820 select psl.person_id
2821 FROM psp_summary_lines psl,
2822 psp_report_template_details_h prtd ,
2823 per_time_periods ptp
2824 WHERE
2825 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2826 prtd.criteria_lookup_code='AWD' AND
2827 prtd.include_exclude_flag='E' AND
2828 prtd.request_id =p_request_id AND
2829 psl.award_id = TO_NUMBER(prtd.criteria_value1) AND
2830 psl.business_group_id = p_business_group_id AND
2831 psl.set_of_books_id = p_set_of_books_id AND
2832 psl.status_code= 'A' AND
2833 ptp.time_period_id = psl.time_period_id AND
2834 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2835 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2836 WHERE pdnh.summary_line_id = psl.summary_line_id
2837 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2838 AND pdnh.reversal_entry_flag IS NULL
2839 AND pdnh.adjustment_batch_name IS NULL )
2840 OR EXISTS
2841 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2842 WHERE ppg.summary_line_id = psl.summary_line_id
2843 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2844 AND ppg.adjustment_batch_name IS NULL AND
2845 ppg.reversal_entry_flag IS NULL)
2846 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2847 WHERE palh.summary_line_id = psl.summary_line_id
2848 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2849 AND palh.adjustment_batch_name IS NULL
2850 AND palh.reversal_entry_flag IS NULL
2851 AND NVL(palh.original_line_flag, 'N') ='N')));
2852
2853 ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
2854
2855 --- replaced non-performant delete with this for 4429787
2856 DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2857 SELECT NVL(psl.person_id,0)
2858 FROM psp_summary_lines psl,
2859 psp_report_template_details_h prtd ,
2860 gms_awards_all gaa ,
2861 per_time_periods ptp
2862 WHERE psl.award_id = gaa.award_id AND
2863 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2864 prtd.criteria_lookup_code='ATY' AND
2865 prtd.include_exclude_flag='E' AND
2866 prtd.request_id =p_request_id AND
2867 gaa.type=prtd.criteria_value1 AND
2868 psl.business_group_id = p_business_group_id AND
2869 psl.set_of_books_id = p_set_of_books_id AND
2870 psl.status_code= 'A' AND
2871 ptp.time_period_id = psl.time_period_id AND
2872 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2873 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2874 WHERE pdnh.summary_line_id = psl.summary_line_id
2875 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2876 AND pdnh.reversal_entry_flag IS NULL
2877 AND pdnh.adjustment_batch_name IS NULL )
2878 OR EXISTS
2879 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2880 WHERE ppg.summary_line_id = psl.summary_line_id
2881 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2882 AND ppg.adjustment_batch_name IS NULL AND
2883 ppg.reversal_entry_flag IS NULL)
2884 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2885 WHERE palh.summary_line_id = psl.summary_line_id
2886 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2887 AND palh.adjustment_batch_name IS NULL
2888 AND palh.reversal_entry_flag IS NULL AND
2889 NVL(palh.original_line_flag, 'N') ='N')));
2890
2891 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
2892
2893 --- replaced non-performant delete with this for 4429787
2894 DELETE FROM psp_selected_persons_t WHERE request_id = p_request_id AND person_id IN (
2895 SELECT NVL(psl.person_id,0)
2896 FROM psp_summary_lines psl,
2897 psp_report_template_details_h prtd ,
2898 pa_projects_all ppa ,
2899 per_time_periods ptp
2900 WHERE psl.project_id = ppa.project_id AND
2901 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2902 prtd.criteria_lookup_code='PRT' AND
2903 prtd.include_exclude_flag='E' AND
2904 prtd.request_id =p_request_id AND
2905 ppa.project_type=prtd.criteria_value1 AND
2906 psl.business_group_id = p_business_group_id AND
2907 psl.set_of_books_id = p_set_of_books_id AND
2908 psl.status_code= 'A' AND
2909 ptp.time_period_id = psl.time_period_id AND
2910 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2911 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2912 WHERE pdnh.summary_line_id = psl.summary_line_id
2913 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2914 AND pdnh.reversal_entry_flag IS NULL
2915 AND pdnh.adjustment_batch_name IS NULL )
2916 OR EXISTS
2917 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2918 WHERE ppg.summary_line_id = psl.summary_line_id
2919 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2920 AND ppg.adjustment_batch_name IS NULL AND
2921 ppg.reversal_entry_flag IS NULL)
2922 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2923 WHERE palh.summary_line_id = psl.summary_line_id
2924 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2925 AND palh.adjustment_batch_name IS NULL
2926 AND palh.reversal_entry_flag IS NULL AND
2927 NVL(palh.original_line_flag, 'N') ='N')));
2928
2929 elsif template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
2930
2931 --- replaced non-performant insert with this for 4429787
2932 delete from psp_selected_persons_t where request_id = p_request_id AND person_id in (
2933 select nvl( psl.person_id ,0)
2934 FROM psp_summary_lines psl,
2935 psp_report_template_details_h prtd ,
2936 per_time_periods ptp
2937 WHERE
2938 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA' AND
2939 prtd.criteria_lookup_code='PRJ' AND
2940 prtd.include_exclude_flag='E' AND
2941 prtd.request_id =p_request_id AND
2942 psl.project_id = TO_NUMBER(prtd.criteria_value1) AND
2943 psl.business_group_id = p_business_group_id AND
2944 psl.set_of_books_id = p_set_of_books_id AND
2945 psl.status_code= 'A' AND
2946 ptp.time_period_id = psl.time_period_id AND
2947 (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start) AND
2948 ( EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
2949 WHERE pdnh.summary_line_id = psl.summary_line_id
2950 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
2951 AND pdnh.reversal_entry_flag IS NULL
2952 AND pdnh.adjustment_batch_name IS NULL )
2953 OR EXISTS
2954 (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
2955 WHERE ppg.summary_line_id = psl.summary_line_id
2956 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
2957 AND ppg.adjustment_batch_name IS NULL AND
2958 ppg.reversal_entry_flag IS NULL)
2959 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
2960 WHERE palh.summary_line_id = psl.summary_line_id
2961 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
2962 AND palh.adjustment_batch_name IS NULL
2963 AND palh.reversal_entry_flag IS NULL AND
2964 NVL(palh.original_line_flag, 'N') ='N')));
2965
2966
2967 elsif template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
2968
2969 DELETE FROM psp_selected_persons_t
2970 WHERE request_id = p_request_id
2971 AND person_id IN (SELECT person_id
2972 FROM per_assignments_f paf,
2973 psp_report_template_details_h prtd,
2974 per_assignment_status_types past
2975 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
2976 AND criteria_lookup_code='PAY'
2977 AND include_exclude_flag='E'
2978 AND paf.assignment_type = 'E'
2979 AND effective_start_date <= p_effort_end
2980 AND effective_end_date >= p_effort_start
2981 AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
2982 AND prtd.request_id = p_request_id
2983 AND paf.assignment_status_type_id = past.assignment_status_type_id
2984 AND ( past.per_system_status <> 'TERM_ASSIGN'
2985 OR EXISTS ( select null
2986 FROM psp_pre_gen_dist_lines_history
2987 WHERE distribution_date between p_effort_start and p_effort_end
2988 AND assignment_id = paf.assignment_id
2989 AND reversal_entry_flag IS NULL
2990 AND rownum=1 )
2991 OR EXISTS (SELECT null
2992 FROM psp_distribution_lines_history pdlh
2993 , psp_summary_lines psl
2994 WHERE pdlh.summary_line_id = psl.summary_line_id
2995 AND distribution_date between p_effort_start and p_effort_end
2996 AND psl.person_id = paf.person_id
2997 AND psl.assignment_id = paf.assignment_id
2998 AND reversal_entry_flag IS NULL
2999 AND rownum=1)));
3000
3001
3002 elsif template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
3003
3004 DELETE FROM psp_selected_persons_t
3005 WHERE request_id = p_request_id
3006 AND person_id IN (SELECT person_id
3007 FROM per_assignments_f paf,
3008 psp_report_template_details_h prtd,
3009 per_assignment_status_types past
3010 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3011 AND criteria_lookup_code='LOC'
3012 AND include_exclude_flag='E'
3013 AND paf.assignment_type = 'E'
3014 AND effective_start_date <= p_effort_end
3015 AND effective_end_date >= p_effort_start
3016 AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
3017 AND prtd.request_id = p_request_id
3018 AND paf.assignment_status_type_id = past.assignment_status_type_id
3019 AND ( past.per_system_status <> 'TERM_ASSIGN'
3020 OR EXISTS ( select null
3021 FROM psp_pre_gen_dist_lines_history
3022 WHERE distribution_date between p_effort_start and p_effort_end
3023 AND assignment_id = paf.assignment_id
3024 AND reversal_entry_flag IS NULL
3025 AND rownum=1 )
3026 OR EXISTS (SELECT null
3027 FROM psp_distribution_lines_history pdlh
3028 , psp_summary_lines psl
3029 WHERE pdlh.summary_line_id = psl.summary_line_id
3030 AND distribution_date between p_effort_start and p_effort_end
3031 AND psl.person_id = paf.person_id
3032 AND psl.assignment_id = paf.assignment_id
3033 AND reversal_entry_flag IS NULL
3034 AND rownum=1)));
3035
3036
3037 elsif template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
3038
3039 DELETE FROM psp_selected_persons_t
3040 WHERE request_id = p_request_id
3041 AND person_id IN (SELECT person_id
3042 FROM per_assignments_f paf,
3043 psp_report_template_details_h prtd,
3044 per_assignment_status_types past
3045 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3046 AND criteria_lookup_code='ORG'
3047 AND include_exclude_flag='E'
3048 AND paf.assignment_type = 'E'
3049 AND effective_start_date <= p_effort_end
3050 AND effective_end_date >= p_effort_start
3051 AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
3052 AND prtd.request_id = p_request_id
3053 AND paf.assignment_status_type_id = past.assignment_status_type_id
3054 AND ( past.per_system_status <> 'TERM_ASSIGN'
3055 OR EXISTS ( select null
3056 FROM psp_pre_gen_dist_lines_history
3057 WHERE distribution_date between p_effort_start and p_effort_end
3058 AND assignment_id = paf.assignment_id
3059 AND reversal_entry_flag IS NULL
3060 AND rownum=1 )
3061 OR EXISTS (SELECT null
3062 FROM psp_distribution_lines_history pdlh
3063 , psp_summary_lines psl
3064 WHERE pdlh.summary_line_id = psl.summary_line_id
3065 AND distribution_date between p_effort_start and p_effort_end
3066 AND psl.person_id = paf.person_id
3067 AND psl.assignment_id = paf.assignment_id
3068 AND reversal_entry_flag IS NULL
3069 AND rownum=1)));
3070
3071 /*
3072
3073 elsif template_rec.array_sel_criteria(i)='CST' then
3074 */
3075
3076 elsif template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
3077
3078 DELETE FROM psp_selected_persons_t
3079 WHERE request_id = p_request_id
3080 AND person_id IN (SELECT person_id
3081 FROM per_assignments_f paf,
3082 psp_report_template_details_h prtd,
3083 per_assignment_status_types past
3084 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3085 AND criteria_lookup_code='JOB'
3086 AND include_exclude_flag='E'
3087 AND paf.assignment_type = 'E'
3088 AND effective_start_date <= p_effort_end
3089 AND effective_end_date >= p_effort_start
3090 AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
3091 AND prtd.request_id = p_request_id
3092 AND paf.assignment_status_type_id = past.assignment_status_type_id
3093 AND ( past.per_system_status <> 'TERM_ASSIGN'
3094 OR EXISTS ( select null
3095 FROM psp_pre_gen_dist_lines_history
3096 WHERE distribution_date between p_effort_start and p_effort_end
3097 AND assignment_id = paf.assignment_id
3098 AND reversal_entry_flag IS NULL
3099 AND rownum=1 )
3100 OR EXISTS (SELECT null
3101 FROM psp_distribution_lines_history pdlh
3102 , psp_summary_lines psl
3103 WHERE pdlh.summary_line_id = psl.summary_line_id
3104 AND distribution_date between p_effort_start and p_effort_end
3105 AND psl.person_id = paf.person_id
3106 AND psl.assignment_id = paf.assignment_id
3107 AND reversal_entry_flag IS NULL
3108 AND rownum=1)));
3109
3110
3111 elsif template_sel_criteria.array_sel_criteria(i) ='POS' THEN
3112
3113 DELETE FROM psp_selected_persons_t
3114 WHERE request_id = p_request_id
3115 AND person_id IN (SELECT person_id
3116 FROM per_assignments_f paf,
3117 psp_report_template_details_h prtd,
3118 per_assignment_status_types past
3119 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3120 AND criteria_lookup_code='POS'
3121 AND include_exclude_flag='E'
3122 AND paf.assignment_type = 'E'
3123 AND effective_start_date <= p_effort_end
3124 AND effective_end_date >= p_effort_start
3125 AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
3126 AND prtd.request_id = p_request_id
3127 AND paf.assignment_status_type_id = past.assignment_status_type_id
3128 AND ( past.per_system_status <> 'TERM_ASSIGN'
3129 OR EXISTS ( select null
3130 FROM psp_pre_gen_dist_lines_history
3131 WHERE distribution_date between p_effort_start and p_effort_end
3132 AND assignment_id = paf.assignment_id
3133 AND reversal_entry_flag IS NULL
3134 AND rownum=1 )
3135 OR EXISTS (SELECT null
3136 FROM psp_distribution_lines_history pdlh
3137 , psp_summary_lines psl
3138 WHERE pdlh.summary_line_id = psl.summary_line_id
3139 AND distribution_date between p_effort_start and p_effort_end
3140 AND psl.person_id = paf.person_id
3141 AND psl.assignment_id = paf.assignment_id
3142 AND reversal_entry_flag IS NULL
3143 AND rownum=1)));
3144
3145 elsif template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
3146
3147 DELETE FROM psp_selected_persons_t
3148 WHERE request_id = p_request_id
3149 AND person_id IN (SELECT person_id
3150 FROM per_assignments_f paf,
3151 psp_report_template_details_h prtd,
3152 per_assignment_status_types past
3153 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3154 AND criteria_lookup_code='ASS'
3155 AND include_exclude_flag='E'
3156 AND paf.assignment_type = 'E'
3157 AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
3158 AND prtd.request_id = p_request_id
3159 AND effective_start_date <= p_effort_end
3160 AND effective_end_date >= p_effort_start
3161 AND paf.assignment_status_type_id = past.assignment_status_type_id
3162 AND ( past.per_system_status <> 'TERM_ASSIGN'
3163 OR EXISTS ( select null
3164 FROM psp_pre_gen_dist_lines_history
3165 WHERE distribution_date between p_effort_start and p_effort_end
3166 AND assignment_id = paf.assignment_id
3167 AND reversal_entry_flag IS NULL
3168 AND rownum=1 )
3169 OR EXISTS (SELECT null
3170 FROM psp_distribution_lines_history pdlh
3171 , psp_summary_lines psl
3172 WHERE pdlh.summary_line_id = psl.summary_line_id
3173 AND distribution_date between p_effort_start and p_effort_end
3174 AND psl.person_id = paf.person_id
3175 AND psl.assignment_id = paf.assignment_id
3176 AND reversal_entry_flag IS NULL
3177 AND rownum=1)));
3178
3179
3180 elsif template_sel_criteria.array_sel_criteria(i)='CST' THEN
3181
3182 DELETE FROM psp_selected_persons_t
3183 WHERE request_id = p_request_id
3184 AND person_id IN (SELECT NVL(person_id,0)
3185 FROM per_assignments_f paf,
3186 pay_payrolls_f ppf,
3187 per_assignment_status_types past
3188 WHERE ppf.payroll_id = paf.payroll_id
3189 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3190 FROM psp_report_template_details_h prtd
3191 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3192 AND criteria_lookup_code='CST'
3193 AND include_exclude_flag='E'
3194 AND request_id = p_request_id)
3195 AND paf.assignment_type = 'E'
3196 AND ppf.effective_start_date <= p_effort_end
3197 AND ppf.effective_end_date >= p_effort_start
3198 AND paf.effective_start_date <= p_effort_end
3199 AND paf.effective_end_date >= p_effort_start
3200 AND paf.assignment_status_type_id = past.assignment_status_type_id
3201 AND ( past.per_system_status <> 'TERM_ASSIGN'
3202 OR EXISTS ( select null
3203 FROM psp_pre_gen_dist_lines_history
3204 WHERE distribution_date between p_effort_start and p_effort_end
3205 AND assignment_id = paf.assignment_id
3206 AND reversal_entry_flag IS NULL
3207 AND rownum=1 )
3208 OR EXISTS (SELECT null
3209 FROM psp_distribution_lines_history pdlh
3210 , psp_summary_lines psl
3211 WHERE pdlh.summary_line_id = psl.summary_line_id
3212 AND distribution_date between p_effort_start and p_effort_end
3213 AND psl.person_id = paf.person_id
3214 AND psl.assignment_id = paf.assignment_id
3215 AND reversal_entry_flag IS NULL
3216 AND rownum=1)));
3217
3218
3219 elsif template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
3220
3221
3222 DELETE FROM psp_selected_persons_t
3223 WHERE request_id = p_request_id
3224 AND person_id IN (SELECT NVL(paf.person_id,0)
3225 FROM per_all_assignments_f paf,
3226 hr_assignment_sets has,
3227 hr_assignment_set_amendments hasa,
3228 per_assignment_status_types past
3229 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
3230 FROM psp_report_template_details_h prtd
3231 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3232 AND criteria_lookup_code='AST'
3233 AND include_exclude_flag='E'
3234 AND request_id =p_request_id)
3235 AND ( (paf.payroll_id = has.payroll_id
3236 AND paf.assignment_type = 'E'
3237 AND paf.effective_start_date <= p_effort_end
3238 AND paf.effective_end_date >= p_effort_start
3239 AND has.assignment_set_id = hasa.assignment_set_id)
3240 OR (paf.assignment_id = hasa.assignment_id
3241 AND paf.assignment_type = 'E'
3242 AND paf.effective_start_date <= p_effort_end
3243 AND paf.effective_end_date >= p_effort_start
3244 AND hasa.assignment_set_id=has.assignment_set_id
3245 AND include_or_exclude ='I'))
3246 AND NOT EXISTS (SELECT assignment_id
3247 FROM hr_assignment_set_amendments hasa
3248 WHERE hasa.assignment_id = paf.assignment_id
3249 AND paf.assignment_type = 'E'
3250 AND hasa.include_or_exclude ='E'
3251 AND paf.effective_start_date <= p_effort_end
3252 AND paf.effective_end_date >= p_effort_start)
3253 AND paf.assignment_status_type_id = past.assignment_status_type_id
3254 AND ( past.per_system_status <> 'TERM_ASSIGN'
3255 OR EXISTS ( select null
3256 FROM psp_pre_gen_dist_lines_history
3257 WHERE distribution_date between p_effort_start and p_effort_end
3258 AND assignment_id = paf.assignment_id
3259 AND reversal_entry_flag IS NULL
3260 AND rownum=1 )
3261 OR EXISTS (SELECT null
3262 FROM psp_distribution_lines_history pdlh
3263 , psp_summary_lines psl
3264 WHERE pdlh.summary_line_id = psl.summary_line_id
3265 AND distribution_date between p_effort_start and p_effort_end
3266 AND psl.person_id = paf.person_id
3267 AND psl.assignment_id = paf.assignment_id
3268 AND reversal_entry_flag IS NULL
3269 AND rownum=1)));
3270
3271
3272
3273
3274
3275
3276 elsif template_sel_criteria.array_sel_criteria(i)='PPG' THEN
3277
3278 OPEN ppg_cursor;
3279
3280 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
3281 IF l_criteria_value1 is not null then
3282
3283 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 || '''' ;
3284 --g_exec_string := l_sql_string;
3285
3286
3287 LOOP
3288
3289 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
3290 EXIT WHEN PPG_CURSOR%NOTFOUND;
3291
3292 g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 || ' = ' || '''' || l_criteria_value2||'''';
3293 /*
3294 select l_sql_string
3295 || ' OR ' || l_criteria_value1 || ' = ' || ''''|| l_criteria_value2 || ''''
3296 into g_exec_string from psp_report_template_details_h ;
3297
3298 */
3299 l_sql_string:= g_exec_string;
3300
3301 END LOOP;
3302
3303
3304 IF l_sql_string is not null then
3305
3306 g_exec_string := 'delete from psp_selected_persons_t where person_id
3307 in (select person_id from per_assignments_f paf,
3308 pay_people_groups ppg , per_assignment_status_types past
3309 where paf.people_group_id= ppg.people_group_id
3310 AND paf.assignment_type = ''' || 'E' || '''
3311 and paf.effective_end_date >= :p_effort_Start and
3312 paf.effective_start_date <= :p_effort_end
3313 and
3314 ppg.people_group_id in (select people_group_id from pay_people_groups
3315 where ' || l_sql_string
3316 || ' )
3317 AND paf.assignment_status_type_id = past.assignment_status_type_id
3318 AND ( past.per_system_status <> ''' || 'TERM_ASSIGN' || '''
3319 OR EXISTS ( select null
3320 FROM psp_pre_gen_dist_lines_history
3321 WHERE distribution_date between :p_effort_start and :p_effort_end
3322 AND assignment_id = paf.assignment_id
3323 AND reversal_entry_flag IS NULL
3324 AND rownum=1 )
3325 OR EXISTS (SELECT null
3326 FROM psp_distribution_lines_history pdlh
3327 , psp_summary_lines psl
3328 WHERE pdlh.summary_line_id = psl.summary_line_id
3329 AND distribution_date between :p_effort_start and :p_effort_end
3330 AND psl.person_id = paf.person_id
3331 AND psl.assignment_id = paf.assignment_id
3332 AND reversal_entry_flag IS NULL
3333 AND rownum=1))
3334 ) and request_id = :request_id';
3335
3336
3337 --fnd_file.put_line(fnd_file.log, 'ppg check is '||g_exec_string);
3338
3339 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;
3340
3341 END IF;
3342
3343 END IF;
3344
3345 ELSIF template_sel_criteria.array_sel_criteria(i)='GLA' THEN
3346
3347 OPEN gla_cursor;
3348
3349 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
3350 IF l_criteria_value1 is not null then
3351
3352 l_sql_string := l_criteria_value1 ||' between '|| '''' || l_criteria_value2 || '''' || ' and ' || '''' || l_criteria_value3 || '''' ;
3353
3354
3355 LOOP
3356
3357 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
3358 EXIT WHEN GLA_CURSOR%NOTFOUND;
3359
3360 g_exec_string:= l_sql_string
3361 || ' OR ' || l_criteria_value1 || ' between ' || ''''|| l_criteria_value2 || '''' ||
3362 ' and ' || ''''|| l_criteria_value3 || '''' ;
3363
3364 l_sql_string:= g_exec_string;
3365
3366 END LOOP;
3367
3368
3369 IF l_sql_string is not null then
3370
3371 g_exec_string := 'delete from psp_selected_persons_t where person_id
3372 in (select nvl(psl.person_id,0) from psp_summary_lines psl,
3373 psp_distribution_lines_history pdnh,
3374 psp_adjustment_lines_history palh, psp_pre_gen_dist_lines_history ppg,
3375 gl_code_combinations gcc
3376 where gcc.code_combination_id= psl.gl_code_combination_id and
3377 psl.business_group_id = '|| p_business_group_id || ' and
3378 psl.set_of_books_id = ' || p_set_of_books_id || ' and
3379 psl.summary_line_id = pdnh.summary_line_id(+) and
3380 psl.summary_line_id = ppg.summary_line_id(+) and
3381 psl.summary_line_id = palh.summary_line_id(+) and
3382 psl.status_code='||''''||'A'||''''||' and
3383 ((psl.source_type in ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||') and
3384 pdnh.distribution_date between :p_effort_start and :p_effort_end
3385 and pdnh.reversal_entry_flag is null
3386 and psl.summary_line_id =pdnh.summary_line_id
3387 and pdnh.adjustment_batch_name is null
3388 ) OR
3389 ( psl.source_type= '||''''||'P'||''''||' and
3390 ppg.distribution_date between :p_effort_start and :p_effort_end and
3391 ppg.adjustment_batch_name is null and
3392 ppg.summary_line_id =psl.summary_line_id and
3393 ppg.reversal_entry_flag is null)
3394 OR (psl.source_type= '||''''||'A'||''''||' and
3395 palh.summary_line_id =psl.summary_line_id and
3396 palh.adjustment_batch_name is null and
3397 NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = '||''''|| 'N' || ''''
3398 || ' and palh.distribution_date between :p_effort_start and :p_effort_end )) and
3399 gcc.code_combination_id= psl.gl_code_combination_id and
3400 gcc.code_combination_id in (select code_combination_id from gl_code_combinations
3401 where ' || l_sql_string
3402 || ' )) and request_id = :request_id';
3403
3404
3405 --fnd_file.put_line(fnd_file.log,'g_exec_string ===='||g_exec_string);
3406
3407 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;
3408
3409 END IF;
3410
3411 END IF;
3412
3413
3414 END IF;
3415
3416
3417 -- END IF;
3418
3419 END IF;
3420
3421
3422 END LOOP;
3423
3424 END;
3425
3426
3427 PROCEDURE APPLY_FF_FORMULA_EXCLUSION(p_request_id IN NUMBER, p_effort_start IN DATE, p_effort_end IN DATE) IS
3428
3429 TYPE v_line_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
3430 TYPE v_date_id IS TABLE OF DATE INDEX BY BINARY_INTEGER;
3431
3432 TYPE ls_criteria_rec is RECORD(
3433 l_person_id v_line_id,
3434 l_assignment_id v_line_id,
3435 l_request_id v_line_id ,
3436 l_start_date v_date_id,
3437 l_end_date v_date_id
3438 );
3439
3440 r_ls_criteria_rec ls_criteria_rec;
3441
3442
3443 TYPE ff_rec is RECORD(
3444 l_formula_id v_line_id
3445 );
3446
3447 r_ff_rec ff_rec;
3448
3449 l_input1 number;
3450 l_input2 number ;
3451 l_input3 number;
3452 l_input4 number;
3453 l_results varchar2(30) ;
3454 l_inputs ff_exec.inputs_t;
3455 l_outputs ff_exec.outputs_t;
3456 l_in_cnt number;
3457 l_out_cnt number;
3458 i number;
3459 j number;
3460 k number;
3461 tot_cnt number;
3462 local_ctr number:=0;
3463 l_cnt number:=0;
3464
3465 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
3466 request_id =p_request_id;
3467
3468 CURSOR get_ff_for_template_csr is select criteria_value1 from psp_report_template_details_h where
3469 request_id = p_request_id and criteria_lookup_type='PSP_SELECTION_CRITERIA' and criteria_lookup_code='FFE';
3470
3471 BEGIN
3472
3473 OPEN select_everyone_csr;
3474
3475 Fetch select_everyone_csr BULK COLLECT into
3476 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;
3477
3478 CLOSE select_everyone_csr;
3479
3480
3481 OPEN get_ff_for_template_csr;
3482 /*
3483
3484 select nvl(count(1), 0) into l_cnt from fnd_sessions where session_id = userenv('session_id');
3485
3486 if l_cnt=0 then
3487
3488 INSERT into fnd_sessions(session_id, effective_date) values (userenv('sessionid'), p_effort_start);
3489 end if;
3490 */
3491
3492
3493
3494 FETCH get_ff_for_template_csr BULK COLLECT into r_ff_rec.l_formula_id;
3495
3496 CLOSE get_ff_for_template_csr;
3497
3498 FOR j in 1..r_ff_rec.l_formula_id.count
3499
3500 LOOP
3501
3502 ff_exec.init_formula(r_ff_rec.l_formula_id(j), p_effort_start, l_inputs,l_outputs);
3503 tot_cnt := r_ls_criteria_rec.l_person_id.count;
3504
3505 FOR k IN 1..tot_cnt LOOP
3506
3507 FOR l_in_cnt in l_inputs.first..l_inputs.last loop
3508
3509 IF (l_inputs(l_in_cnt).name ='PERSON_ID') THEN
3510 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(r_ls_criteria_rec.l_person_id(k));
3511 hr_utility.trace('Input 1 :PERSON_ID=' ||l_inputs(l_in_cnt).value);
3512
3513
3514 ELSIF l_inputs(l_in_cnt).name='REQUEST_ID' THEN
3515 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(r_ls_criteria_rec.l_request_id(k));
3516
3517 hr_utility.trace( ' Input 2 :REQUEST_ID = '||l_inputs(l_in_cnt).value);
3518
3519 ELSIF l_inputs(l_in_cnt).name='START_DATE' THEN
3520 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(r_ls_criteria_rec.l_start_date(k));
3521 hr_utility.trace( ' Input 3 :START_DATE = '||l_inputs(l_in_cnt).value);
3522
3523 ELSIF l_inputs(l_in_cnt).name='END_DATE' THEN
3524 l_inputs(l_in_cnt).value := fnd_date.date_to_canonical(r_ls_criteria_rec.l_end_date(k));
3525 hr_utility.trace( ' Input 4 :END_DATE= '||l_inputs(l_in_cnt).value);
3526
3527 ELSIF l_inputs(l_in_cnt).name ='ASSIGNMENT_ID' THEN
3528 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(r_ls_criteria_rec.l_assignment_id(k));
3529 hr_utility.trace('Input 5 :ASSIGNMENT_ID=' ||l_inputs(l_in_cnt).value);
3530
3531 /*
3532 Commented out the below change as input 1 is person_id and addl. input is not required
3533
3534 Added for bug 4195678 by tbalacha
3535
3536 ELSIF l_inputs(l_in_cnt).name='PERSON_ID' THEN
3537
3538 l_inputs(l_in_cnt).value := fnd_number.number_to_canonical(r_ls_criteria_rec.l_person_id(k));
3539 else
3540 l_inputs(l_in_cnt).value :=fnd_number.number_to_canonical(l_input2);
3541
3542 */
3543 END IF;
3544
3545 END LOOP;
3546
3547 ff_exec.run_formula(l_inputs, l_outputs);
3548
3549 FOR l_out_cnt in l_outputs.first..l_outputs.last
3550
3551 LOOP
3552
3553 l_results:= l_outputs(l_out_cnt).value;
3554
3555
3556 IF (l_results='FALSE' or l_results = 0 )THEN -- introduced for bug 4195678
3557 DELETE FROM psp_selected_persons_t
3558 WHERE person_id = r_ls_criteria_rec.l_person_id(k)
3559 AND assignment_id = r_ls_criteria_rec.l_assignment_id(k);
3560
3561 END IF;
3562
3563 END LOOP;
3564
3565 END LOOP;
3566
3567 END LOOP;
3568
3569 END;
3570
3571 FUNCTION get_parameter_value( name in varchar2, parameter_list varchar2) return varchar2
3572 IS
3573
3574
3575 start_ptr NUMBER;
3576 end_ptr NUMBER;
3577 token_val pay_payroll_actions.legislative_parameters%type;
3578 par_value pay_payroll_actions.legislative_parameters%type;
3579
3580 BEGIN
3581
3582 token_val := name||'=';
3583 start_ptr := instr(parameter_list, token_val) +length(token_val);
3584
3585 end_ptr :=instr(parameter_list, ' ', start_ptr);
3586
3587 IF end_ptr=0 then
3588 end_ptr:=length(parameter_list) +1;
3589
3590 END IF;
3591
3592 IF instr(parameter_list, token_val) = 0 then
3593
3594 par_value:=NULL;
3595 ELSE
3596
3597 par_value:=substr(parameter_list, start_ptr, end_ptr-start_ptr);
3598
3599
3600 end if;
3601
3602 return par_value;
3603 END get_parameter_value;
3604
3605 PROCEDURE get_asg_lowest_cardinality (p_request_id IN NUMBER,
3606 p_effort_start IN DATE,
3607 p_effort_end IN DATE,
3608 p_business_group_id IN NUMBER,
3609 p_set_of_books_id IN NUMBER) IS
3610 l_criteria_value1 VARCHAR2(30);
3611 l_criteria_value2 VARCHAR2(60); -- Bug 8257434
3612 l_criteria_value3 VARCHAR2(30);
3613 l_dyn_criteria VARCHAR2(30);
3614 l_atleast_one_criteria VARCHAR2(1);
3615 l_sql_string VARCHAR2(1000);
3616
3617 CURSOR get_lowest_cardinality_csr IS
3618 SELECT lookup_code FROM
3619 psp_selection_cardinality_gt WHERE total_count > 0 ORDER BY total_count asc;
3620
3621
3622 CURSOR get_zero_cardinality_csr IS
3623 SELECT lookup_code
3624 FROM psp_selection_cardinality_gt
3625 WHERE total_count=0;
3626
3627
3628 CURSOR get_selection_cardinality_csr(p_request_id IN NUMBER) IS
3629 SELECT DISTINCT criteria_lookup_code
3630 FROM psp_report_template_details_h
3631 WHERE request_id = p_request_id
3632 AND include_exclude_flag = 'I'
3633 AND criteria_lookup_type = 'PSP_SELECTION_CRITERIA';
3634
3635 /* The below cursors would only be used only when no statis selection criteria have been chosen */
3636 CURSOR ppg_cursor IS
3637 SELECT criteria_value1,
3638 criteria_value2
3639 FROM psp_report_template_details_h
3640 WHERE request_id = p_request_id
3641 AND include_exclude_flag='I'
3642 AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
3643 AND criteria_lookup_code='PPG';
3644
3645 CURSOR gla_cursor IS
3646 SELECT criteria_value1,
3647 criteria_value2,
3648 criteria_value3
3649 FROM psp_report_template_details_h
3650 WHERE request_id = p_request_id
3651 AND include_exclude_flag='I'
3652 AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
3653 AND criteria_lookup_code ='GLA';
3654
3655 BEGIN
3656 OPEN get_selection_cardinality_csr(p_request_id);
3657 FETCH get_selection_cardinality_csr BULK COLLECT into template_rec.array_sel_criteria;
3658 CLOSE get_selection_cardinality_csr;
3659
3660 FOR i IN 1.. template_rec.array_sel_criteria.COUNT
3661 LOOP
3662 IF template_rec.array_sel_criteria(i) = 'PTY' THEN
3663 INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
3664 SELECT 'PTY', COUNT(DISTINCT assignment_id)
3665 FROM per_people_f ppf,
3666 per_assignments_f paf
3667 WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
3668 FROM psp_report_template_details_h prtd
3669 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3670 AND criteria_lookup_code ='PTY'
3671 AND include_exclude_flag='I'
3672 AND request_id = p_request_id)
3673 AND paf.person_id = ppf.person_id
3674 AND paf.assignment_type = 'E'
3675 AND ppf.effective_start_date <= p_effort_end
3676 AND ppf.effective_end_date >= p_effort_start
3677 AND paf.effective_start_date <= p_effort_end
3678 AND paf.effective_end_date >= p_effort_start;
3679 ELSIF template_rec.array_sel_criteria(i) ='EMP' THEN
3680 INSERT INTO psp_selection_cardinality_gt (lookup_code, total_count)
3681 SELECT 'EMP', COUNT(DISTINCT paf.assignment_id)
3682 FROM per_all_assignments_f paf
3683 WHERE paf.person_id IN (SELECT TO_NUMBER(criteria_value1)
3684 FROM psp_report_template_details_h prtd
3685 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3686 AND criteria_lookup_code='EMP'
3687 AND include_exclude_flag='I'
3688 AND request_id = p_request_id)
3689 AND paf.assignment_type = 'E'
3690 AND paf.effective_start_date <= p_effort_end
3691 AND paf.effective_end_date >= p_effort_start;
3692 ELSIF template_rec.array_sel_criteria(i) ='SUP' THEN
3693 INSERT INTO psp_selection_cardinality_gt
3694 (lookup_code, total_count)
3695 SELECT 'SUP', COUNT(DISTINCT assignment_id)
3696 FROM per_all_assignments_f paf
3697 WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
3698 FROM psp_report_template_details_h prtd
3699 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3700 AND criteria_lookup_code='SUP'
3701 AND request_id = p_request_id
3702 AND include_exclude_flag='I')
3703 AND paf.assignment_type = 'E'
3704 AND effective_start_date <= p_effort_end
3705 AND effective_end_date >= p_effort_start;
3706 ELSIF template_rec.array_sel_criteria(i) ='AWD' THEN
3707 INSERT INTO psp_selection_cardinality_gt
3708 (lookup_code, total_count)
3709 SELECT 'AWD', COUNT(DISTINCT psl.assignment_id)
3710 FROM psp_summary_lines psl,
3711 psp_report_template_details_h prtd ,
3712 per_time_periods ptp
3713 WHERE
3714 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3715 AND prtd.criteria_lookup_code='AWD'
3716 AND prtd.include_exclude_flag='I'
3717 AND prtd.request_id =p_request_id
3718 and psl.award_id = TO_NUMBER(prtd.criteria_value1)
3719 AND psl.business_group_id = p_business_group_id
3720 AND psl.set_of_books_id = p_set_of_books_id
3721 AND psl.status_code= 'A'
3722 AND ptp.time_period_id = psl.time_period_id
3723 AND (ptp.start_date <= p_effort_end
3724 AND ptp.end_date >= p_effort_start)
3725 AND (EXISTS (SELECT 1
3726 FROM psp_distribution_lines_history pdnh
3727 WHERE pdnh.summary_line_id = psl.summary_line_id
3728 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3729 AND pdnh.reversal_entry_flag IS NULL
3730 AND pdnh.adjustment_batch_name IS NULL )
3731 OR EXISTS (SELECT 1
3732 FROM psp_pre_gen_dist_lines_history ppg
3733 WHERE ppg.summary_line_id = psl.summary_line_id
3734 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
3735 AND ppg.adjustment_batch_name IS NULL
3736 AND ppg.reversal_entry_flag IS NULL)
3737 OR EXISTS (SELECT 1
3738 FROM psp_adjustment_lines_history palh
3739 WHERE palh.summary_line_id = psl.summary_line_id
3740 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
3741 AND palh.adjustment_batch_name IS NULL
3742 AND palh.reversal_entry_flag IS NULL
3743 AND NVL(palh.original_line_flag, 'N') ='N'));
3744 ELSIF template_rec.array_sel_criteria(i)='ATY' THEN
3745 INSERT INTO psp_selection_cardinality_gt
3746 (lookup_code, total_count)
3747 SELECT 'ATY', COUNT(DISTINCT psl.assignment_id)
3748 FROM psp_summary_lines psl,
3749 psp_report_template_details_h prtd ,
3750 gms_awards_all gaa,
3751 per_time_periods ptp
3752 WHERE psl.award_id = gaa.award_id
3753 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3754 AND prtd.criteria_lookup_code='ATY'
3755 AND prtd.include_exclude_flag='I'
3756 AND prtd.request_id = p_request_id
3757 AND gaa.type=prtd.criteria_value1
3758 AND psl.business_group_id = p_business_group_id
3759 AND psl.set_of_books_id = p_set_of_books_id
3760 AND psl.status_code= 'A'
3761 AND ptp.time_period_id = psl.time_period_id
3762 AND (ptp.start_date <= p_effort_end
3763 AND ptp.end_date >= p_effort_start)
3764 AND (EXISTS (SELECT 1
3765 FROM psp_distribution_lines_history pdnh
3766 WHERE pdnh.summary_line_id = psl.summary_line_id
3767 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3768 AND pdnh.reversal_entry_flag IS NULL
3769 AND pdnh.adjustment_batch_name IS NULL )
3770 OR EXISTS (SELECT 1
3771 FROM psp_pre_gen_dist_lines_history ppg
3772 WHERE ppg.summary_line_id = psl.summary_line_id
3773 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
3774 AND ppg.adjustment_batch_name IS NULL
3775 AND ppg.reversal_entry_flag IS NULL)
3776 OR EXISTS (SELECT 1
3777 FROM psp_adjustment_lines_history palh
3778 WHERE palh.summary_line_id = psl.summary_line_id
3779 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
3780 AND palh.adjustment_batch_name IS NULL
3781 AND palh.reversal_entry_flag IS NULL
3782 AND NVL(palh.original_line_flag, 'N') ='N'));
3783 ELSIF template_rec.array_sel_criteria(i)='PRT' THEN
3784 INSERT INTO psp_selection_cardinality_gt
3785 (lookup_code, total_count)
3786 SELECT 'PRT', COUNT(DISTINCT psl.assignment_id)
3787 FROM psp_summary_lines psl,
3788 psp_report_template_details_h prtd ,
3789 pa_projects_all ppa ,
3790 per_time_periods ptp
3791 WHERE psl.project_id = ppa.project_id
3792 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3793 AND prtd.criteria_lookup_code='PRT'
3794 AND prtd.include_exclude_flag='I'
3795 AND prtd.request_id =p_request_id
3796 AND ppa.project_type=prtd.criteria_value1
3797 AND psl.business_group_id = p_business_group_id
3798 AND psl.set_of_books_id = p_set_of_books_id
3799 AND psl.status_code= 'A'
3800 AND ptp.time_period_id = psl.time_period_id
3801 AND (ptp.start_date <= p_effort_end
3802 AND ptp.end_date >= p_effort_start)
3803 AND (EXISTS (SELECT 1
3804 FROM psp_distribution_lines_history pdnh
3805 WHERE pdnh.summary_line_id = psl.summary_line_id
3806 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3807 AND pdnh.reversal_entry_flag IS NULL
3808 AND pdnh.adjustment_batch_name IS NULL )
3809 OR EXISTS (SELECT 1
3810 FROM psp_pre_gen_dist_lines_history ppg
3811 WHERE ppg.summary_line_id = psl.summary_line_id
3812 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
3813 AND ppg.adjustment_batch_name IS NULL AND
3814 ppg.reversal_entry_flag IS NULL)
3815 OR EXISTS (SELECT 1
3816 FROM psp_adjustment_lines_history palh
3817 WHERE palh.summary_line_id = psl.summary_line_id
3818 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
3819 AND palh.adjustment_batch_name IS NULL
3820 AND palh.reversal_entry_flag IS NULL AND
3821 NVL(palh.original_line_flag, 'N') ='N'));
3822 ELSIF template_rec.array_sel_criteria(i)='PRJ' THEN
3823 INSERT INTO psp_selection_cardinality_gt
3824 (lookup_code, total_count)
3825 SELECT 'PRJ', COUNT(DISTINCT psl.assignment_id)
3826 FROM psp_summary_lines psl,
3827 psp_report_template_details_h prtd ,
3828 per_time_periods ptp
3829 WHERE
3830 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
3831 AND prtd.criteria_lookup_code='PRJ'
3832 AND prtd.include_exclude_flag='I'
3833 AND prtd.request_id =p_request_id
3834 and psl.project_id = TO_NUMBER(prtd.criteria_value1)
3835 AND psl.business_group_id = p_business_group_id
3836 AND psl.set_of_books_id = p_set_of_books_id
3837 AND psl.status_code= 'A'
3838 AND ptp.time_period_id = psl.time_period_id
3839 AND (ptp.start_date <= p_effort_end
3840 AND ptp.end_date >= p_effort_start)
3841 AND (EXISTS (SELECT 1 FROM psp_distribution_lines_history pdnh
3842 WHERE pdnh.summary_line_id = psl.summary_line_id
3843 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
3844 AND pdnh.reversal_entry_flag IS NULL
3845 AND pdnh.adjustment_batch_name IS NULL )
3846 OR EXISTS (SELECT 1 FROM psp_pre_gen_dist_lines_history ppg
3847 WHERE ppg.summary_line_id = psl.summary_line_id
3848 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
3849 AND ppg.adjustment_batch_name IS NULL
3850 AND ppg.reversal_entry_flag IS NULL)
3851 OR EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh
3852 WHERE palh.summary_line_id = psl.summary_line_id
3853 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
3854 AND palh.adjustment_batch_name IS NULL
3855 AND palh.reversal_entry_flag IS NULL
3856 AND NVL(palh.original_line_flag, 'N') ='N'));
3857 ELSIF template_rec.array_sel_criteria(i)='PAY' THEN
3858 INSERT INTO psp_selection_cardinality_gt
3859 (lookup_code, total_count)
3860 SELECT 'PAY', COUNT(DISTINCT assignment_id)
3861 FROM per_assignments_f paf
3862 WHERE payroll_id IN (SELECT TO_NUMBER(criteria_value1)
3863 FROM psp_report_template_details_h prtd
3864 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3865 AND criteria_lookup_code='PAY'
3866 AND include_exclude_flag='I' AND request_id = p_request_id)
3867 AND paf.assignment_type = 'E'
3868 AND effective_start_date <= p_effort_end
3869 AND effective_end_date >= p_effort_start;
3870 ELSIF template_rec.array_sel_criteria(i)='LOC' THEN
3871 INSERT INTO psp_selection_cardinality_gt
3872 (lookup_code, total_count)
3873 SELECT 'LOC', COUNT(DISTINCT assignment_id)
3874 FROM per_assignments_f paf
3875 WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
3876 FROM psp_report_template_details_h prtd
3877 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3878 AND criteria_lookup_code='LOC'
3879 AND include_exclude_flag='I'
3880 AND request_id = p_request_id)
3881 AND paf.assignment_type = 'E'
3882 AND effective_start_date <= p_effort_end
3883 AND effective_end_date >= p_effort_start;
3884 ELSIF template_rec.array_sel_criteria(i)='ORG' THEN
3885 INSERT INTO psp_selection_cardinality_gt
3886 (lookup_code, total_count)
3887 SELECT 'ORG', COUNT(DISTINCT assignment_id)
3888 FROM per_assignments_f paf
3889 WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
3890 FROM psp_report_template_details_h prtd
3891 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3892 AND criteria_lookup_code='ORG'
3893 AND include_exclude_flag='I'
3894 AND request_id = p_request_id)
3895 AND paf.assignment_type = 'E'
3896 AND effective_start_date <= p_effort_end
3897 AND effective_end_date >= p_effort_start;
3898 ELSIF template_rec.array_sel_criteria(i)='JOB' THEN
3899 INSERT INTO psp_selection_cardinality_gt
3900 (lookup_code, total_count)
3901 SELECT 'JOB', COUNT(DISTINCT assignment_id)
3902 FROM per_assignments_f paf
3903 WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
3904 FROM psp_report_template_details_h prtd
3905 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3906 AND criteria_lookup_code='JOB'
3907 AND include_exclude_flag='I'
3908 AND request_id=p_request_id)
3909 AND paf.assignment_type = 'E'
3910 AND effective_start_date <= p_effort_end
3911 AND effective_end_date >= p_effort_start;
3912 ELSIF template_rec.array_sel_criteria(i)='POS' THEN
3913 INSERT INTO psp_selection_cardinality_gt
3914 (lookup_code, total_count)
3915 SELECT 'POS', COUNT(DISTINCT assignment_id)
3916 FROM per_assignments_f paf
3917 WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
3918 FROM psp_report_template_details_h prtd
3919 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3920 AND criteria_lookup_code='POS'
3921 AND include_exclude_flag='I'
3922 AND request_id = p_request_id)
3923 AND paf.assignment_type = 'E'
3924 AND effective_start_date <= p_effort_end
3925 AND effective_end_date >= p_effort_start;
3926 ELSIF template_rec.array_sel_criteria(i)='ASS' THEN
3927 INSERT INTO psp_selection_cardinality_gt
3928 (lookup_code, total_count)
3929 SELECT 'ASS', COUNT(DISTINCT assignment_id)
3930 FROM per_assignments_f paf
3931 WHERE assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
3932 FROM psp_report_template_details_h prtd
3933 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3934 AND criteria_lookup_code='ASS'
3935 AND include_exclude_flag='I'
3936 AND request_id = p_request_id)
3937 AND paf.assignment_type = 'E'
3938 AND effective_start_date <= p_effort_end
3939 AND effective_end_date >= p_effort_start;
3940 ELSIF template_rec.array_sel_criteria(i)='CST' THEN
3941 INSERT INTO psp_selection_cardinality_gt
3942 (lookup_code, total_count)
3943 SELECT 'CST', COUNT(DISTINCT paf.assignment_id)
3944 FROM per_assignments_f paf,
3945 pay_payrolls_f ppf
3946 WHERE ppf.payroll_id = paf.payroll_id
3947 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
3948 FROM psp_report_template_details_h prtd
3949 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3950 AND criteria_lookup_code='CST'
3951 AND include_exclude_flag='I'
3952 AND request_id = p_request_id)
3953 AND paf.assignment_type = 'E'
3954 AND ppf.effective_start_date <= p_effort_end
3955 AND ppf.effective_end_date >= p_effort_start
3956 AND paf.effective_start_date <= p_effort_end
3957 AND paf.effective_end_date >= p_effort_start;
3958 ELSIF template_rec.array_sel_criteria(i) = 'AST' THEN
3959 INSERT INTO psp_selection_cardinality_gt
3960 (lookup_code, total_count)
3961 SELECT 'AST', COUNT(DISTINCT paf.assignment_id)
3962 FROM per_all_assignments_f paf,
3963 hr_assignment_sets has ,
3964 hr_assignment_set_amendments hasa
3965 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
3966 FROM psp_report_template_details_h prtd
3967 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
3968 AND criteria_lookup_code='AST'
3969 AND include_exclude_flag='I'
3970 AND request_id =p_request_id)
3971 AND ( (paf.payroll_id = has.payroll_id
3972 AND paf.assignment_type = 'E'
3973 AND paf.effective_start_date <= p_effort_end
3974 AND paf.effective_end_date >= p_effort_start
3975 AND has.assignment_set_id = hasa.assignment_set_id)
3976 OR (paf.assignment_id = hasa.assignment_id
3977 AND paf.assignment_type = 'E'
3978 AND paf.effective_start_date <= p_effort_end
3979 AND paf.effective_end_date >= p_effort_start
3980 AND hasa.assignment_set_id=has.assignment_set_id
3981 AND include_or_exclude ='I'))
3982 AND NOT EXISTS (SELECT assignment_id
3983 FROM hr_assignment_set_amendments hasa
3984 WHERE hasa.assignment_id = paf.assignment_id
3985 AND hasa.include_or_exclude ='E'
3986 AND paf.assignment_type = 'E'
3987 AND paf.effective_start_date <= p_effort_end
3988 AND paf.effective_end_date >= p_effort_start);
3989 ELSIF template_rec.array_sel_criteria(i)='PPG' THEN
3990 l_dyn_criteria:='PPG';
3991 ELSIF template_rec.array_sel_criteria(i)='GLA' THEN
3992 l_dyn_criteria:='GLA';
3993 END IF;
3994 END LOOP;
3995
3996 -- Next find the selection criteria with lowest cardinality. Use it to prepare the initial list.
3997
3998 OPEN get_lowest_cardinality_csr;
3999 FETCH get_lowest_cardinality_csr into g_lookup_code;
4000 CLOSE get_lowest_cardinality_csr;
4001
4002 IF g_lookup_code is not null then
4003 l_atleast_one_criteria:='Y';
4004 hr_utility.trace( 'g_lookup_code -> '||g_lookup_code );
4005 ELSE
4006 OPEN get_zero_cardinality_csr;
4007 FETCH get_zero_cardinality_csr into g_lookup_code;
4008 CLOSE get_zero_cardinality_csr;
4009
4010 hr_utility.trace( ' Inside zero cardinality => g_lookup_code= '||g_lookup_code );
4011
4012 IF g_lookup_code is not null then
4013 l_atleast_one_criteria:='Y';
4014 END IF;
4015 END IF;
4016
4017 IF g_lookup_code IS NULL then
4018 BEGIN
4019 -- When no static selection criteria have been chosen, then invoke the dynamic selection criteria
4020 hr_utility.trace( ' Inside zero cardinality => g_lookup_code IS NULL');
4021
4022 IF l_dyn_criteria ='PPG' then
4023 l_atleast_one_criteria:='Y';
4024
4025 OPEN ppg_cursor;
4026 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
4027 IF l_criteria_value1 is not null then
4028 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
4029 LOOP
4030 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
4031 EXIT WHEN PPG_CURSOR%NOTFOUND;
4032
4033 g_exec_string:= l_sql_string|| ' OR '||l_criteria_value1 ||' = '||'''' ||
4034 l_criteria_value2|| '''';
4035 l_sql_string:=g_exec_string;
4036 END LOOP;
4037 CLOSE ppg_cursor;
4038
4039 IF l_sql_string IS NOT NULL THEN
4040 g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4041 SELECT '|| '''' || 'PPG' || ''''||' , COUNT(person_id)
4042 FROM per_assignments_f paf,
4043 pay_people_groups ppg
4044 WHERE paf.people_group_id= ppg.people_group_id
4045 AND paf.assignment_type = ''' || 'E' || '''
4046 AND paf.effective_end_date >= :p_effort_start
4047 AND paf.effective_start_date <= :p_effort_end
4048 AND ppg.people_group_id IN (SELECT people_group_id
4049 FROM pay_people_groups
4050 WHERE ' || l_sql_string || ')';
4051
4052 hr_utility.trace( ' g_exec_string = '||g_exec_string );
4053
4054 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_Start, p_effort_end;
4055 g_lookup_code := 'PPG';
4056 END IF;
4057 END IF;
4058 ELSIF l_dyn_criteria ='GLA' then
4059 l_atleast_one_criteria:='Y';
4060
4061 OPEN gla_cursor;
4062 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
4063 IF l_criteria_value1 is not null then
4064 l_sql_string := l_criteria_value1 ||' between '|| '''' || l_criteria_value2 || '''' ||
4065 ' AND ' || ''''||l_criteria_value3||'''' ;
4066 LOOP
4067 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
4068 EXIT WHEN GLA_CURSOR%NOTFOUND;
4069
4070 g_exec_string:= l_sql_string || ' OR ' || l_criteria_value1 || ' between ' ||
4071 ''''|| l_criteria_value2 || '''' ||
4072 ' AND ' || ''''|| l_criteria_value3 || '''' ;
4073 l_sql_string:=g_exec_string;
4074 END LOOP;
4075 CLOSE gla_cursor;
4076
4077 IF l_sql_string IS NOT NULL THEN
4078 g_exec_string := 'INSERT INTO psp_selection_cardinality_gt(lookup_code, total_count)
4079 SELECT '|| '''' || 'GLA' || ''''|| ' , COUNT( DISTINCT psl.assignment_id)
4080 FROM psp_summary_lines psl,
4081 psp_distribution_lines_history pdnh,
4082 psp_pre_gen_dist_lines_history ppg,
4083 psp_adjustment_lines_history palh,
4084 gl_code_combinations gcc
4085 WHERE psl.business_group_id = '|| p_business_group_id || '
4086 AND psl.set_of_books_id =' || p_set_of_books_id || '
4087 AND gcc.code_combination_id= psl.gl_code_combination_id
4088 AND psl.summary_line_id = pdnh.summary_line_id(+)
4089 AND psl.summary_line_id = ppg.summary_line_id(+)
4090 AND psl.summary_line_id = palh.summary_line_id(+)
4091 AND psl.status_code='||''''||'A'||''''||'
4092 AND ( (psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
4093 AND pdnh.distribution_date between :p_effort_start AND :p_effort_end
4094 AND pdnh.summary_line_id = psl.summary_line_id
4095 AND pdnh.reversal_entry_flag IS NULL
4096 AND pdnh.adjustment_batch_name IS NULL)
4097 OR (psl.source_type='||''''||'P'||''''||'
4098 AND ppg.distribution_date BETWEEN :p_effort_start
4099 AND :p_effort_end
4100 AND ppg.summary_line_id = psl.summary_line_id
4101 AND ppg.adjustment_batch_name IS NULL
4102 AND ppg.reversal_entry_flag IS NULL)
4103 OR (psl.source_type='||''''||'A'||''''||'
4104 AND palh.summary_line_id = psl.summary_line_id
4105 AND palh.reversal_entry_flag IS NULL
4106 AND palh.adjustment_batch_name IS NULL
4107 AND NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''||
4108 ') = '||''''|| 'N' || '''' || '
4109 AND palh.distribution_date BETWEEN :p_effort_start
4110 AND :p_effort_end ))
4111 AND gcc.code_combination_id= psl.gl_code_combination_id
4112 AND gcc.code_combination_id IN (SELECT code_combination_id
4113 FROM gl_code_combinations
4114 WHERE ' || l_sql_string || ')';
4115
4116 hr_utility.trace( ' g_exec_string = '||g_exec_string );
4117 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_start, p_effort_end,
4118 p_effort_start, p_effort_end ,p_effort_start, p_effort_end;
4119 g_lookup_code :='GLA';
4120 END IF;
4121 END IF;
4122 END IF;
4123 END;
4124 END IF;
4125
4126 IF NVL(l_atleast_one_criteria, 'N')='N' then
4127 g_lookup_code:='ALL' ;
4128 END IF;
4129
4130 EXCEPTION
4131 WHEN OTHERS THEN
4132 fnd_file.put_line(fnd_file.log, ' EXCEPTION '||sqlerrm);
4133 END get_asg_lowest_cardinality;
4134
4135 PROCEDURE prepare_initial_asg_list (p_request_id IN NUMBER,
4136 p_effort_start IN DATE,
4137 p_effort_end IN DATE,
4138 p_business_group_id IN NUMBER,
4139 p_set_of_books_id IN NUMBER) IS
4140 PRAGMA AUTONOMOUS_TRANSACTION;
4141 CURSOR PPG_CURSOR IS
4142 SELECT criteria_value1, criteria_value2
4143 FROM psp_report_template_details_h
4144 WHERE request_id = p_request_id
4145 AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA' AND criteria_lookup_code='PPG'
4146 AND include_exclude_flag='I';
4147
4148
4149 CURSOR GLA_CURSOR IS
4150 SELECT criteria_value1 , criteria_value2, criteria_value3
4151 FROM psp_report_template_details_h
4152 WHERE request_id = p_request_id
4153 AND criteria_lookup_type ='PSP_SELECTION_CRITERIA' AND criteria_lookup_code ='GLA'
4154 AND include_exclude_flag='I';
4155
4156 l_criteria_value1 VARCHAR2(30);
4157 l_criteria_value2 VARCHAR2(60); -- Bug 8257434
4158 l_criteria_value3 VARCHAR2(30);
4159 l_sql_string VARCHAR2(1000);
4160
4161 l_cnt NUMBER;
4162 BEGIN
4163 IF g_lookup_code = 'PTY' THEN
4164
4165 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4166 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4167 FROM per_people_f ppf,
4168 per_assignments_f paf,
4169 per_assignment_status_types past
4170 WHERE person_type_id IN (SELECT TO_NUMBER(criteria_value1)
4171 FROM psp_report_template_details_h prtd
4172 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4173 AND criteria_lookup_code='PTY'
4174 AND request_id = p_request_id
4175 AND include_exclude_flag='I')
4176 AND paf.assignment_type = 'E'
4177 AND paf.person_id = ppf.person_id
4178 AND ppf.effective_start_date <= p_effort_end
4179 AND ppf.effective_end_date >= p_effort_start
4180 AND paf.effective_start_date <= p_effort_end
4181 AND paf.effective_end_date >= p_effort_start
4182 AND paf.assignment_status_type_id = past.assignment_status_type_id
4183 AND ( past.per_system_status <> 'TERM_ASSIGN'
4184 OR EXISTS ( select null
4185 FROM psp_pre_gen_dist_lines_history
4186 WHERE distribution_date between p_effort_start and p_effort_end
4187 AND assignment_id = paf.assignment_id
4188 AND reversal_entry_flag IS NULL
4189 AND rownum=1 )
4190 OR EXISTS (SELECT null
4191 FROM psp_distribution_lines_history pdlh
4192 , psp_summary_lines psl
4193 WHERE pdlh.summary_line_id = psl.summary_line_id
4194 AND distribution_date between p_effort_start and p_effort_end
4195 AND psl.person_id = paf.person_id
4196 AND psl.assignment_id = paf.assignment_id
4197 AND reversal_entry_flag IS NULL
4198 AND rownum=1));
4199
4200
4201 ELSIF g_lookup_code ='EMP' THEN
4202 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4203 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4204 FROM per_all_people_f ppf,
4205 per_assignments_f paf,
4206 per_assignment_status_types past
4207 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4208 FROM psp_report_template_details_h prtd
4209 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4210 AND criteria_lookup_code='EMP'
4211 AND include_exclude_flag='I'
4212 AND request_id = p_request_id)
4213 AND paf.assignment_type = 'E'
4214 AND paf.person_id = ppf.person_id
4215 AND ppf.effective_start_date <= p_effort_end
4216 AND ppf.effective_end_date >= p_effort_start
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
4238 ELSIF g_lookup_code ='SUP' THEN
4239 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4240 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4241 FROM per_all_assignments_f paf,
4242 per_assignment_status_types past
4243 WHERE supervisor_id IN (SELECT TO_NUMBER(criteria_value1)
4244 FROM psp_report_template_details_h prtd
4245 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4246 AND criteria_lookup_code='SUP'
4247 AND include_exclude_flag='I'
4248 AND request_id = p_request_id)
4249 AND paf.assignment_type = 'E'
4250 AND paf.effective_start_date <= p_effort_end
4251 AND paf.effective_end_date >= p_effort_start
4252 AND paf.assignment_status_type_id = past.assignment_status_type_id
4253 AND ( past.per_system_status <> 'TERM_ASSIGN'
4254 OR EXISTS ( select null
4255 FROM psp_pre_gen_dist_lines_history
4256 WHERE distribution_date between p_effort_start and p_effort_end
4257 AND assignment_id = paf.assignment_id
4258 AND reversal_entry_flag IS NULL
4259 AND rownum=1 )
4260 OR EXISTS (SELECT null
4261 FROM psp_distribution_lines_history pdlh
4262 , psp_summary_lines psl
4263 WHERE pdlh.summary_line_id = psl.summary_line_id
4264 AND distribution_date between p_effort_start and p_effort_end
4265 AND psl.person_id = paf.person_id
4266 AND psl.assignment_id = paf.assignment_id
4267 AND reversal_entry_flag IS NULL
4268 AND rownum=1));
4269
4270 ELSIF g_lookup_code='AWD' THEN
4271 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4272 SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4273 FROM psp_summary_lines psl,
4274 psp_report_template_details_h prtd,
4275 per_time_periods ptp
4276 WHERE
4277 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4278 AND prtd.criteria_lookup_code='AWD'
4279 AND prtd.include_exclude_flag='I'
4280 AND prtd.request_id =p_request_id
4281 and psl.award_id = TO_NUMBER(prtd.criteria_value1)
4282 AND psl.business_group_id = p_business_group_id
4283 AND psl.set_of_books_id = p_set_of_books_id
4284 AND psl.status_code= 'A'
4285 AND ptp.time_period_id = psl.time_period_id
4286 AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4287 AND ( EXISTS (SELECT 1
4288 FROM psp_distribution_lines_history pdnh
4289 WHERE pdnh.summary_line_id = psl.summary_line_id
4290 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4291 AND pdnh.reversal_entry_flag IS NULL
4292 AND pdnh.adjustment_batch_name IS NULL )
4293 OR EXISTS (SELECT 1
4294 FROM psp_pre_gen_dist_lines_history ppg
4295 WHERE ppg.summary_line_id = psl.summary_line_id
4296 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
4297 AND ppg.adjustment_batch_name IS NULL
4298 AND ppg.reversal_entry_flag IS NULL)
4299 OR EXISTS (SELECT 1
4300 FROM psp_adjustment_lines_history palh
4301 WHERE palh.summary_line_id = psl.summary_line_id
4302 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
4303 AND palh.adjustment_batch_name IS NULL
4304 AND palh.reversal_entry_flag IS NULL
4305 AND NVL(palh.original_line_flag, 'N') ='N'));
4306 ELSIF g_lookup_code ='ATY' THEN
4307 INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4308 SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4309 FROM psp_summary_lines psl,
4310 psp_report_template_details_h prtd,
4311 gms_awards_all gaa,
4312 per_time_periods ptp
4313 WHERE psl.award_id = gaa.award_id
4314 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4315 AND prtd.criteria_lookup_code='ATY'
4316 AND prtd.include_exclude_flag='I'
4317 AND prtd.request_id =p_request_id
4318 AND gaa.type=prtd.criteria_value1
4319 AND psl.business_group_id = p_business_group_id
4320 AND psl.set_of_books_id = p_set_of_books_id
4321 AND psl.status_code= 'A'
4322 AND ptp.time_period_id = psl.time_period_id
4323 AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4324 AND ( EXISTS (SELECT 1
4325 FROM psp_distribution_lines_history pdnh
4326 WHERE pdnh.summary_line_id = psl.summary_line_id
4327 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4328 AND pdnh.reversal_entry_flag IS NULL
4329 AND pdnh.adjustment_batch_name IS NULL )
4330 OR EXISTS (SELECT 1
4331 FROM psp_pre_gen_dist_lines_history ppg
4332 WHERE ppg.summary_line_id = psl.summary_line_id
4333 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
4334 AND ppg.adjustment_batch_name IS NULL
4335 AND ppg.reversal_entry_flag IS NULL)
4336 OR EXISTS (SELECT 1
4337 FROM psp_adjustment_lines_history palh
4338 WHERE palh.summary_line_id = psl.summary_line_id
4339 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
4340 AND palh.adjustment_batch_name IS NULL
4341 AND palh.reversal_entry_flag IS NULL
4342 AND NVL(palh.original_line_flag, 'N') ='N'));
4343
4344 ELSIF g_lookup_code ='PRT' THEN
4345 INSERT INTO psp_selected_persons_t (request_id , person_id, assignment_id)
4346 SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4347 FROM psp_summary_lines psl,
4348 psp_report_template_details_h prtd,
4349 pa_projects_all ppa,
4350 per_time_periods ptp
4351 WHERE psl.project_id = ppa.project_id
4352 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4353 AND prtd.criteria_lookup_code='PRT'
4354 AND prtd.include_exclude_flag='I'
4355 AND prtd.request_id =p_request_id
4356 AND ppa.project_type=prtd.criteria_value1
4357 AND psl.business_group_id = p_business_group_id
4358 AND psl.set_of_books_id = p_set_of_books_id
4359 AND psl.status_code= 'A'
4360 AND ptp.time_period_id = psl.time_period_id
4361 AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4362 AND ( EXISTS (SELECT 1
4363 FROM psp_distribution_lines_history pdnh
4364 WHERE pdnh.summary_line_id = psl.summary_line_id
4365 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4366 AND pdnh.reversal_entry_flag IS NULL
4367 AND pdnh.adjustment_batch_name IS NULL )
4368 OR EXISTS (SELECT 1
4369 FROM psp_pre_gen_dist_lines_history ppg
4370 WHERE ppg.summary_line_id = psl.summary_line_id
4371 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
4372 AND ppg.adjustment_batch_name IS NULL
4373 AND ppg.reversal_entry_flag IS NULL)
4374 OR EXISTS (SELECT 1
4375 FROM psp_adjustment_lines_history palh
4376 WHERE palh.summary_line_id = psl.summary_line_id
4377 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
4378 AND palh.adjustment_batch_name IS NULL
4379 AND palh.reversal_entry_flag IS NULL
4380 AND NVL(palh.original_line_flag, 'N') ='N'));
4381 ELSIF g_lookup_code ='PRJ' THEN
4382 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4383 SELECT DISTINCT p_request_id, psl.person_id, psl.assignment_id
4384 FROM psp_summary_lines psl,
4385 psp_report_template_details_h prtd,
4386 per_time_periods ptp
4387 WHERE
4388 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4389 AND prtd.criteria_lookup_code='PRJ'
4390 AND prtd.include_exclude_flag='I'
4391 AND prtd.request_id =p_request_id
4392 and psl.project_id = TO_NUMBER(prtd.criteria_value1)
4393 AND psl.business_group_id = p_business_group_id
4394 AND psl.set_of_books_id = p_set_of_books_id
4395 AND psl.status_code= 'A'
4396 AND ptp.time_period_id = psl.time_period_id
4397 AND (ptp.start_date <= p_effort_end AND ptp.end_date >= p_effort_start)
4398 AND ( EXISTS (SELECT 1
4399 FROM psp_distribution_lines_history pdnh
4400 WHERE pdnh.summary_line_id = psl.summary_line_id
4401 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
4402 AND pdnh.reversal_entry_flag IS NULL
4403 AND pdnh.adjustment_batch_name IS NULL )
4404 OR EXISTS (SELECT 1
4405 FROM psp_pre_gen_dist_lines_history ppg
4406 WHERE ppg.summary_line_id = psl.summary_line_id
4407 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
4408 AND ppg.adjustment_batch_name IS NULL
4409 AND ppg.reversal_entry_flag IS NULL)
4410 OR EXISTS (SELECT 1
4411 FROM psp_adjustment_lines_history palh
4412 WHERE palh.summary_line_id = psl.summary_line_id
4413 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
4414 AND palh.adjustment_batch_name IS NULL
4415 AND palh.reversal_entry_flag IS NULL
4416 AND NVL(palh.original_line_flag, 'N') ='N'));
4417 ELSIF g_lookup_code ='PAY' 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 payroll_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='PAY'
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 ='LOC' THEN
4451
4452 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4453 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4454 FROM per_assignments_f paf,
4455 per_assignment_status_types past
4456 WHERE location_id IN (SELECT TO_NUMBER(criteria_value1)
4457 FROM psp_report_template_details_h prtd
4458 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4459 AND criteria_lookup_code='LOC'
4460 AND include_exclude_flag='I'
4461 AND request_id = p_request_id)
4462 AND paf.assignment_type = 'E'
4463 AND paf.effective_start_date <= p_effort_end
4464 AND paf.effective_end_date >= p_effort_start
4465 AND paf.assignment_status_type_id = past.assignment_status_type_id
4466 AND ( past.per_system_status <> 'TERM_ASSIGN'
4467 OR EXISTS ( select null
4468 FROM psp_pre_gen_dist_lines_history
4469 WHERE distribution_date between p_effort_start and p_effort_end
4470 AND assignment_id = paf.assignment_id
4471 AND reversal_entry_flag IS NULL
4472 AND rownum=1 )
4473 OR EXISTS (SELECT null
4474 FROM psp_distribution_lines_history pdlh
4475 , psp_summary_lines psl
4476 WHERE pdlh.summary_line_id = psl.summary_line_id
4477 AND distribution_date between p_effort_start and p_effort_end
4478 AND psl.person_id = paf.person_id
4479 AND psl.assignment_id = paf.assignment_id
4480 AND reversal_entry_flag IS NULL
4481 AND rownum=1));
4482
4483 ELSIF g_lookup_code ='ORG' THEN
4484 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4485 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4486 FROM per_assignments_f paf,
4487 per_assignment_status_types past
4488 WHERE organization_id IN (SELECT TO_NUMBER(criteria_value1)
4489 FROM psp_report_template_details_h prtd
4490 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4491 AND criteria_lookup_code='ORG'
4492 AND request_id = p_request_id
4493 AND include_exclude_flag='I')
4494 AND paf.assignment_type = 'E'
4495 AND paf.effective_start_date <= p_effort_end
4496 AND paf.effective_end_date >= p_effort_start
4497 AND paf.assignment_status_type_id = past.assignment_status_type_id
4498 AND ( past.per_system_status <> 'TERM_ASSIGN'
4499 OR EXISTS ( select null
4500 FROM psp_pre_gen_dist_lines_history
4501 WHERE distribution_date between p_effort_start and p_effort_end
4502 AND assignment_id = paf.assignment_id
4503 AND reversal_entry_flag IS NULL
4504 AND rownum=1 )
4505 OR EXISTS (SELECT null
4506 FROM psp_distribution_lines_history pdlh
4507 , psp_summary_lines psl
4508 WHERE pdlh.summary_line_id = psl.summary_line_id
4509 AND distribution_date between p_effort_start and p_effort_end
4510 AND psl.person_id = paf.person_id
4511 AND psl.assignment_id = paf.assignment_id
4512 AND reversal_entry_flag IS NULL
4513 AND rownum=1));
4514
4515 ELSIF g_lookup_code='CST' THEN
4516 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4517 SELECT DISTINCT p_request_id, person_id, assignment_id
4518 FROM per_assignments_f paf,
4519 pay_payrolls_f ppf,
4520 per_assignment_status_types past
4521 WHERE ppf.payroll_id = paf.payroll_id
4522 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
4523 FROM psp_report_template_details_h prtd
4524 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4525 AND criteria_lookup_code='CST'
4526 AND include_exclude_flag='I'
4527 AND request_id = p_request_id)
4528 AND paf.assignment_type = 'E'
4529 AND ppf.effective_start_date <= p_effort_end
4530 AND ppf.effective_end_date >= p_effort_start
4531 AND paf.effective_start_date <= p_effort_end
4532 AND paf.effective_end_date >= p_effort_start
4533 AND paf.assignment_status_type_id = past.assignment_status_type_id
4534 AND ( past.per_system_status <> 'TERM_ASSIGN'
4535 OR EXISTS ( select null
4536 FROM psp_pre_gen_dist_lines_history
4537 WHERE distribution_date between p_effort_start and p_effort_end
4538 AND assignment_id = paf.assignment_id
4539 AND reversal_entry_flag IS NULL
4540 AND rownum=1 )
4541 OR EXISTS (SELECT null
4542 FROM psp_distribution_lines_history pdlh
4543 , psp_summary_lines psl
4544 WHERE pdlh.summary_line_id = psl.summary_line_id
4545 AND distribution_date between p_effort_start and p_effort_end
4546 AND psl.person_id = paf.person_id
4547 AND psl.assignment_id = paf.assignment_id
4548 AND reversal_entry_flag IS NULL
4549 AND rownum=1));
4550
4551 ELSIF g_lookup_code = 'AST' THEN
4552
4553 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4554 SELECT DISTINCT p_request_id, person_id, paf.assignment_id
4555 FROM per_all_assignments_f paf,
4556 hr_assignment_sets has,
4557 hr_assignment_set_amendments hasa,
4558 per_assignment_status_types past
4559 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
4560 FROM psp_report_template_details_h prtd
4561 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4562 AND criteria_lookup_code='AST'
4563 AND include_exclude_flag='I'
4564 AND request_id =p_request_id)
4565 AND ( (paf.payroll_id = has.payroll_id
4566 AND paf.assignment_type = 'E'
4567 AND paf.effective_start_date <= p_effort_end
4568 AND paf.effective_end_date >= p_effort_start
4569 AND has.assignment_set_id = hasa.assignment_set_id)
4570 OR (paf.assignment_id = hasa.assignment_id
4571 AND paf.assignment_type = 'E'
4572 AND paf.effective_start_date <= p_effort_end
4573 AND paf.effective_end_date >= p_effort_start
4574 AND hasa.assignment_set_id=has.assignment_set_id AND include_or_exclude ='I'))
4575 AND NOT EXISTS (SELECT assignment_id
4576 FROM hr_assignment_set_amendments hasa
4577 WHERE hasa.assignment_id = paf.assignment_id AND hasa.include_or_exclude ='E'
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 ='JOB' THEN
4600
4601 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4602 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4603 FROM per_assignments_f paf,
4604 per_assignment_status_types past
4605 WHERE job_id IN (SELECT TO_NUMBER(criteria_value1)
4606 FROM psp_report_template_details_h prtd
4607 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4608 AND criteria_lookup_code='JOB'
4609 AND include_exclude_flag='I'
4610 AND request_id = p_request_id)
4611 AND paf.assignment_type = 'E'
4612 AND paf.effective_start_date <= p_effort_end
4613 AND paf.effective_end_date >= p_effort_start
4614 AND paf.assignment_status_type_id = past.assignment_status_type_id
4615 AND ( past.per_system_status <> 'TERM_ASSIGN'
4616 OR EXISTS ( select null
4617 FROM psp_pre_gen_dist_lines_history
4618 WHERE distribution_date between p_effort_start and p_effort_end
4619 AND assignment_id = paf.assignment_id
4620 AND reversal_entry_flag IS NULL
4621 AND rownum=1 )
4622 OR EXISTS (SELECT null
4623 FROM psp_distribution_lines_history pdlh
4624 , psp_summary_lines psl
4625 WHERE pdlh.summary_line_id = psl.summary_line_id
4626 AND distribution_date between p_effort_start and p_effort_end
4627 AND psl.person_id = paf.person_id
4628 AND psl.assignment_id = paf.assignment_id
4629 AND reversal_entry_flag IS NULL
4630 AND rownum=1));
4631
4632 ELSIF g_lookup_code ='POS' THEN
4633 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4634 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4635 FROM per_assignments_f paf,
4636 per_assignment_status_types past
4637 WHERE position_id IN (SELECT TO_NUMBER(criteria_value1)
4638 FROM psp_report_template_details_h prtd
4639 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4640 AND criteria_lookup_code='POS'
4641 AND request_id = p_request_id
4642 AND include_exclude_flag='I')
4643 AND paf.assignment_type = 'E'
4644 AND paf.effective_start_date <= p_effort_end
4645 AND paf.effective_end_date >= p_effort_start
4646 AND paf.assignment_status_type_id = past.assignment_status_type_id
4647 AND ( past.per_system_status <> 'TERM_ASSIGN'
4648 OR EXISTS ( select null
4649 FROM psp_pre_gen_dist_lines_history
4650 WHERE distribution_date between p_effort_start and p_effort_end
4651 AND assignment_id = paf.assignment_id
4652 AND reversal_entry_flag IS NULL
4653 AND rownum=1 )
4654 OR EXISTS (SELECT null
4655 FROM psp_distribution_lines_history pdlh
4656 , psp_summary_lines psl
4657 WHERE pdlh.summary_line_id = psl.summary_line_id
4658 AND distribution_date between p_effort_start and p_effort_end
4659 AND psl.person_id = paf.person_id
4660 AND psl.assignment_id = paf.assignment_id
4661 AND reversal_entry_flag IS NULL
4662 AND rownum=1));
4663
4664 ELSIF g_lookup_code ='ASS' THEN
4665 INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4666 SELECT DISTINCT p_request_id, paf.person_id, paf.assignment_id
4667 FROM per_assignments_f paf,
4668 per_assignment_status_types past
4669 WHERE paf.assignment_status_type_id IN (SELECT TO_NUMBER(criteria_value1)
4670 FROM psp_report_template_details_h prtd
4671 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4672 AND criteria_lookup_code='ASS'
4673 AND request_id = p_request_id
4674 AND include_exclude_flag='I')
4675 AND paf.assignment_type = 'E'
4676 AND paf.effective_start_date <= p_effort_end
4677 AND paf.effective_end_date >= p_effort_start
4678 AND paf.assignment_status_type_id = past.assignment_status_type_id
4679 AND ( past.per_system_status <> 'TERM_ASSIGN'
4680 OR EXISTS ( select null
4681 FROM psp_pre_gen_dist_lines_history
4682 WHERE distribution_date between p_effort_start and p_effort_end
4683 AND assignment_id = paf.assignment_id
4684 AND reversal_entry_flag IS NULL
4685 AND rownum=1 )
4686 OR EXISTS (SELECT null
4687 FROM psp_distribution_lines_history pdlh
4688 , psp_summary_lines psl
4689 WHERE pdlh.summary_line_id = psl.summary_line_id
4690 AND distribution_date between p_effort_start and p_effort_end
4691 AND psl.person_id = paf.person_id
4692 AND psl.assignment_id = paf.assignment_id
4693 AND reversal_entry_flag IS NULL
4694 AND rownum=1));
4695
4696 ELSIF g_lookup_code='PPG' THEN
4697 open ppg_cursor;
4698 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
4699 IF l_criteria_value1 IS NOT NULL THEN
4700 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
4701
4702 LOOP
4703 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
4704 EXIT WHEN PPG_CURSOR%NOTFOUND;
4705
4706 g_exec_string:= l_sql_string || ' OR '||l_criteria_value1 || ' = ' ||
4707 ''''||l_criteria_value2 || '''';
4708 l_sql_string:= g_exec_string;
4709
4710 END LOOP;
4711 CLOSE ppg_cursor;
4712
4713 IF l_sql_string IS NOT NULL THEN
4714 g_exec_string := 'INSERT INTO psp_selected_persons_t (request_id, person_id, assignment_id)
4715 SELECT :request_id , person_id, assignment_id
4716 FROM per_assignments_f paf,
4717 pay_people_groups ppg,
4718 per_assignment_status_types past
4719 WHERE paf.people_group_id= ppg.people_group_id
4720 AND paf.assignment_type = ''' || 'E' || '''
4721 AND paf.effective_end_date >= :p_effort_start
4722 AND paf.effective_start_date <= :p_effort_end
4723 AND ppg.people_group_id IN (SELECT people_group_id
4724 FROM pay_people_groups
4725 WHERE ' || l_sql_string || ')
4726 AND paf.assignment_status_type_id = past.assignment_status_type_id
4727 AND ( past.per_system_status <> ''' ||'TERM_ASSIGN' || '''
4728 OR EXISTS ( select null
4729 FROM psp_pre_gen_dist_lines_history
4730 WHERE distribution_date between :p_effort_start and :p_effort_end
4731 AND assignment_id = paf.assignment_id
4732 AND reversal_entry_flag IS NULL
4733 AND rownum=1 )
4734 OR EXISTS (SELECT null
4735 FROM psp_distribution_lines_history pdlh
4736 , psp_summary_lines psl
4737 WHERE pdlh.summary_line_id = psl.summary_line_id
4738 AND distribution_date between :p_effort_start and :p_effort_end
4739 AND psl.person_id = paf.person_id
4740 AND psl.assignment_id = paf.assignment_id
4741 AND reversal_entry_flag IS NULL
4742 AND rownum=1))';
4743
4744 EXECUTE IMMEDIATE g_exec_string USING IN p_request_id, p_effort_start, p_effort_end,
4745 p_effort_start, p_effort_end, p_effort_start, p_effort_end;
4746 END IF;
4747 END IF;
4748 ELSIF g_lookup_code ='GLA' THEN
4749 OPEN gla_cursor;
4750 FETCH GLA_CURSOR into l_criteria_value1, l_criteria_value2, l_criteria_value3;
4751
4752 IF l_criteria_value1 IS NOT NULL THEN
4753 l_sql_string := l_criteria_value1 ||' between '|| '''' || l_criteria_value2 ||''''||
4754 ' AND ' || ''''|| l_criteria_value3 || '''' ;
4755 LOOP
4756 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
4757 EXIT WHEN GLA_CURSOR%NOTFOUND;
4758
4759 g_exec_string:= l_sql_string || ' OR ' || l_criteria_value1 ||
4760 ' BETWEEN ' || ''''|| l_criteria_value2 || '''' ||
4761 ' AND ' || ''''|| l_criteria_value3 || '''' ;
4762 l_sql_string:=g_exec_string;
4763 END LOOP;
4764 CLOSE gla_cursor;
4765
4766 IF l_sql_string IS NOT NULL THEN
4767 g_exec_string := 'INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4768 SELECT :p_request_id, psl.person_id, psl.assignment_id
4769 FROM psp_summary_lines psl,
4770 psp_distribution_lines_history pdnh,
4771 psp_adjustment_lines_history palh,
4772 psp_pre_gen_dist_lines_history ppg,
4773 gl_code_combinations gcc
4774 WHERE psl.business_group_id = '|| p_business_group_id || '
4775 AND psl.set_of_books_id = ' || p_set_of_books_id ||'
4776 AND psl.summary_line_id = pdnh.summary_line_id(+)
4777 AND psl.summary_line_id = ppg.summary_line_id(+)
4778 AND psl.summary_line_id = palh.summary_line_id(+)
4779 AND psl.status_code= '||''''||'A'||''''||'
4780 AND ( (psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
4781 AND pdnh.distribution_date between :p_effort_start AND :p_effort_end
4782 AND pdnh.reversal_entry_flag IS NULL
4783 AND pdnh.summary_line_id = psl.summary_line_id
4784 AND pdnh.adjustment_batch_name IS NULL)
4785 OR (psl.source_type='||''''||'P'||''''||'
4786 AND ppg.distribution_date between :p_effort_start AND :p_effort_end
4787 AND ppg.summary_line_id = psl.summary_line_id
4788 AND ppg.adjustment_batch_name IS NULL
4789 AND ppg.reversal_entry_flag IS NULL)
4790 OR (psl.source_type='||''''||'A'||''''||'
4791 AND palh.adjustment_batch_name IS NULL
4792 AND palh.summary_line_id =psl.summary_line_id
4793 AND NVL(palh.original_line_flag, ' || ''''|| 'N' || ''''|| ') = ' ||
4794 ''''|| 'N' || '''' ||'
4795 AND palh.distribution_date between :p_effort_start AND :p_effort_end))
4796 AND gcc.code_combination_id= psl.gl_code_combination_id
4797 AND gcc.code_combination_id IN (SELECT code_combination_id
4798 FROM gl_code_combinations
4799 WHERE ' || l_sql_string || ')';
4800 EXECUTE IMMEDIATE g_exec_string USING IN p_request_id, p_effort_start,
4801 p_effort_end, p_effort_start, p_effort_end, p_effort_start, p_effort_end;
4802 END IF;
4803 END IF;
4804 ELSIF g_lookup_code='ALL' THEN
4805 INSERT INTO psp_selected_persons_t(request_id, person_id, assignment_id)
4806 SELECT DISTINCT p_request_id, person_id, assignment_id
4807 FROM per_assignments_f
4808 WHERE assignment_type = 'E'
4809 AND business_group_id = p_business_group_id
4810 AND effective_start_date <= p_effort_end
4811 AND effective_end_date >= p_effort_start;
4812 END IF;
4813
4814 COMMIT;
4815 END prepare_initial_asg_list;
4816
4817 PROCEDURE prune_initial_asg_list (p_request_id IN NUMBER,
4818 p_effort_start IN DATE,
4819 p_effort_end IN DATE,
4820 p_business_group_id IN NUMBER,
4821 p_set_of_books_id IN NUMBER) IS
4822 CURSOR get_all_selection_criteria(p_request_id IN NUMBER) IS
4823 SELECT distinct criteria_lookup_code,
4824 include_exclude_flag
4825 FROM psp_report_template_details_h
4826 WHERE request_id = p_request_id
4827 AND criteria_lookup_type='PSP_SELECTION_CRITERIA'
4828 ORDER BY include_exclude_flag;
4829
4830 CURSOR PPG_CURSOR IS
4831 SELECT criteria_value1, criteria_value2
4832 FROM psp_report_template_details_h
4833 WHERE request_id = p_request_id
4834 AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
4835 AND criteria_lookup_code='PPG'
4836 AND include_exclude_flag='I';
4837
4838 CURSOR GLA_CURSOR IS
4839 SELECT criteria_value1 , criteria_value2, criteria_value3
4840 FROM psp_report_template_details_h
4841 WHERE request_id = p_request_id
4842 AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
4843 AND criteria_lookup_code ='GLA'
4844 AND include_exclude_flag='I';
4845
4846
4847 TYPE t_varchar_30_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
4848 TYPE t_varchar_1_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
4849
4850 l_criteria_value1 VARCHAR2(30);
4851 l_criteria_value2 VARCHAR2(60); -- Bug 8257434
4852 l_criteria_value3 VARCHAR2(30);
4853 l_sql_string VARCHAR2(1000);
4854 BEGIN
4855 OPEN get_all_selection_criteria(p_request_id);
4856 FETCH get_all_selection_criteria BULK COLLECT INTO template_sel_criteria.array_sel_criteria,
4857 template_sel_criteria.array_inc_exc_flag;
4858 CLOSE get_all_selection_criteria;
4859
4860 FOR i IN 1..template_sel_criteria.array_sel_criteria.COUNT
4861 LOOP
4862 IF template_sel_criteria.array_inc_exc_flag(i) = 'I' THEN
4863 IF template_sel_criteria.array_sel_criteria(i) <> g_lookup_code THEN
4864 IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
4865 DELETE FROM psp_selected_persons_t pspt
4866 WHERE request_id = p_request_id
4867 /* Bug 5087294 : Performance fix replacing not in with not exists */
4868 -- AND person_id NOT IN (SELECT NVL( person_id, 0)
4869 AND NOT EXISTS ( SELECT 1
4870 FROM per_people_f ppf,
4871 psp_report_template_details_h prtd,
4872 per_assignments_f paf
4873 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4874 AND criteria_lookup_code='PTY'
4875 AND paf.person_id = ppf.person_id
4876 AND paf.assignment_type = 'E'
4877 AND paf.effective_start_date <= p_effort_end
4878 AND paf.effective_end_date >= p_effort_start
4879 and
4880 ppf.effective_start_date <= p_effort_end and
4881 ppf.effective_end_date >= p_effort_start
4882 AND include_exclude_flag='I'
4883 AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
4884 AND prtd.request_id = p_request_id
4885 AND ppf.person_id = pspt.person_id );
4886 ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
4887 DELETE FROM psp_selected_persons_t pspt
4888 WHERE request_id = p_request_id
4889 /* Bug 5087294 : Performance fix replacing not in with not exists */
4890 -- AND person_id NOT IN (SELECT NVL(person_id,0)
4891 AND NOT EXISTS ( SELECT 1
4892 FROM per_all_people_f ppf, per_assignments_f paf
4893 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
4894 FROM psp_report_template_details_h prtd
4895 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4896 AND criteria_lookup_code='EMP'
4897 AND include_exclude_flag='I'
4898 AND prtd.request_id = p_request_id)
4899 AND paf.person_id = ppf.person_id
4900 AND paf.assignment_type = 'E'
4901 AND paf.effective_start_date <= p_effort_end
4902 AND paf.effective_end_date >= p_effort_start
4903 AND ppf.effective_start_date <= p_effort_end
4904 AND ppf.effective_end_date >= p_effort_start
4905 AND ppf.person_id = pspt.person_id );
4906 ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
4907 DELETE FROM psp_selected_persons_t pspt
4908 WHERE request_id = p_request_id
4909 /* Bug 5087294 : Performance fix replacing not in with not exists */
4910 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
4911 AND NOT EXISTS ( SELECT 1
4912 FROM per_all_assignments_f paf,
4913 psp_report_template_details_h prtd
4914 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
4915 AND criteria_lookup_code='SUP'
4916 AND include_exclude_flag='I'
4917 AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
4918 AND prtd.request_id = p_request_id
4919 AND paf.assignment_type = 'E'
4920 AND paf.effective_start_date <= p_effort_end
4921 AND effective_end_date >= p_effort_start
4922 AND paf.assignment_id = pspt.assignment_id );
4923 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
4924 DELETE FROM psp_selected_persons_t
4925 WHERE request_id = p_request_id
4926 AND assignment_id NOT IN (SELECT psl.assignment_id
4927 FROM psp_summary_lines psl,
4928 psp_report_template_details_h prtd ,
4929 per_time_periods ptp
4930 WHERE
4931 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4932 AND prtd.criteria_lookup_code='AWD'
4933 AND prtd.include_exclude_flag='I'
4934 AND prtd.request_id =p_request_id
4935 and psl.award_id = TO_NUMBER(prtd.criteria_value1)
4936 AND psl.business_group_id = p_business_group_id
4937 AND psl.set_of_books_id = p_set_of_books_id
4938 AND psl.status_code= 'A'
4939 AND ptp.time_period_id = psl.time_period_id
4940 AND (ptp.start_date <= p_effort_end
4941 AND ptp.end_date >= p_effort_start)
4942 AND ( EXISTS (SELECT 1
4943 FROM psp_distribution_lines_history pdnh
4944 WHERE pdnh.summary_line_id = psl.summary_line_id
4945 AND pdnh.distribution_date BETWEEN p_effort_start
4946 AND p_effort_end
4947 AND pdnh.reversal_entry_flag IS NULL
4948 AND pdnh.adjustment_batch_name IS NULL)
4949 OR EXISTS (SELECT 1
4950 FROM psp_pre_gen_dist_lines_history ppg
4951 WHERE ppg.summary_line_id = psl.summary_line_id
4952 AND ppg.distribution_date BETWEEN p_effort_start
4953 AND p_effort_end
4954 AND ppg.adjustment_batch_name IS NULL
4955 AND ppg.reversal_entry_flag IS NULL)
4956 OR EXISTS (SELECT 1
4957 FROM psp_adjustment_lines_history palh
4958 WHERE palh.summary_line_id = psl.summary_line_id
4959 AND palh.distribution_date BETWEEN p_effort_start
4960 AND p_effort_end
4961 AND palh.adjustment_batch_name IS NULL
4962 AND palh.reversal_entry_flag IS NULL
4963 AND NVL(palh.original_line_flag, 'N') ='N')));
4964 ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
4965 DELETE FROM psp_selected_persons_t
4966 WHERE request_id = p_request_id
4967 AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
4968 FROM psp_summary_lines psl,
4969 psp_report_template_details_h prtd,
4970 gms_awards_all gaa,
4971 per_time_periods ptp
4972 WHERE psl.award_id = gaa.award_id
4973 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
4974 AND prtd.criteria_lookup_code='ATY'
4975 AND prtd.include_exclude_flag='I'
4976 AND prtd.request_id =p_request_id
4977 AND gaa.type=prtd.criteria_value1
4978 AND psl.business_group_id = p_business_group_id
4979 AND psl.set_of_books_id = p_set_of_books_id
4980 AND psl.status_code= 'A'
4981 AND ptp.time_period_id = psl.time_period_id
4982 AND (ptp.start_date <= p_effort_end
4983 AND ptp.end_date >= p_effort_start)
4984 AND ( EXISTS (SELECT 1
4985 FROM psp_distribution_lines_history pdnh
4986 WHERE pdnh.summary_line_id = psl.summary_line_id
4987 AND pdnh.distribution_date BETWEEN p_effort_start
4988 AND p_effort_end
4989 AND pdnh.reversal_entry_flag IS NULL
4990 AND pdnh.adjustment_batch_name IS NULL)
4991 OR EXISTS (SELECT 1
4992 FROM psp_pre_gen_dist_lines_history ppg
4993 WHERE ppg.summary_line_id = psl.summary_line_id
4994 AND ppg.distribution_date BETWEEN p_effort_start
4995 AND p_effort_end
4996 AND ppg.adjustment_batch_name IS NULL
4997 AND ppg.reversal_entry_flag IS NULL)
4998 OR EXISTS (SELECT 1
4999 FROM psp_adjustment_lines_history palh
5000 WHERE palh.summary_line_id = psl.summary_line_id
5001 AND palh.distribution_date BETWEEN p_effort_start
5002 AND p_effort_end
5003 AND palh.adjustment_batch_name IS NULL
5004 AND palh.reversal_entry_flag IS NULL
5005 AND NVL(palh.original_line_flag, 'N') ='N')));
5006 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
5007 DELETE FROM psp_selected_persons_t
5008 WHERE request_id = p_request_id
5009 AND assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
5010 FROM psp_summary_lines psl,
5011 psp_report_template_details_h prtd,
5012 pa_projects_all ppa,
5013 per_time_periods ptp
5014 WHERE psl.project_id = ppa.project_id
5015 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5016 AND prtd.criteria_lookup_code='PRT'
5017 AND prtd.include_exclude_flag='I'
5018 AND prtd.request_id =p_request_id
5019 AND ppa.project_type=prtd.criteria_value1
5020 AND psl.business_group_id = p_business_group_id
5021 AND psl.set_of_books_id = p_set_of_books_id
5022 AND psl.status_code= 'A'
5023 AND ptp.time_period_id = psl.time_period_id
5024 AND (ptp.start_date <= p_effort_end
5025 AND ptp.end_date >= p_effort_start)
5026 AND ( EXISTS (SELECT 1
5027 FROM psp_distribution_lines_history pdnh
5028 WHERE pdnh.summary_line_id = psl.summary_line_id
5029 AND pdnh.distribution_date BETWEEN p_effort_start
5030 AND p_effort_end
5031 AND pdnh.reversal_entry_flag IS NULL
5032 AND pdnh.adjustment_batch_name IS NULL)
5033 OR EXISTS (SELECT 1
5034 FROM psp_pre_gen_dist_lines_history ppg
5035 WHERE ppg.summary_line_id = psl.summary_line_id
5036 AND ppg.distribution_date BETWEEN p_effort_start
5037 AND p_effort_end
5038 AND ppg.adjustment_batch_name IS NULL
5039 AND ppg.reversal_entry_flag IS NULL)
5040 OR EXISTS (SELECT 1
5041 FROM psp_adjustment_lines_history palh
5042 WHERE palh.summary_line_id = psl.summary_line_id
5043 AND palh.distribution_date BETWEEN p_effort_start
5044 AND p_effort_end
5045 AND palh.adjustment_batch_name IS NULL
5046 AND palh.reversal_entry_flag IS NULL
5047 AND NVL(palh.original_line_flag, 'N') ='N')));
5048 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
5049 DELETE FROM psp_selected_persons_t
5050 WHERE request_id = p_request_id
5051 AND assignment_id NOT IN (SELECT NVL(psl.assignment_id ,0)
5052 FROM psp_summary_lines psl,
5053 psp_report_template_details_h prtd ,
5054 per_time_periods ptp
5055 WHERE
5056 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5057 AND prtd.criteria_lookup_code='PRJ'
5058 AND prtd.include_exclude_flag='I'
5059 AND prtd.request_id =p_request_id
5060 and psl.project_id = TO_NUMBER(prtd.criteria_value1)
5061 AND psl.business_group_id = p_business_group_id
5062 AND psl.set_of_books_id = p_set_of_books_id
5063 AND psl.status_code= 'A'
5064 AND ptp.time_period_id = psl.time_period_id
5065 AND (ptp.start_date <= p_effort_end
5066 AND ptp.end_date >= p_effort_start)
5067 AND ( EXISTS (SELECT 1
5068 FROM psp_distribution_lines_history pdnh
5069 WHERE pdnh.summary_line_id = psl.summary_line_id
5070 AND pdnh.distribution_date BETWEEN p_effort_start
5071 AND p_effort_end
5072 AND pdnh.reversal_entry_flag IS NULL
5073 AND pdnh.adjustment_batch_name IS NULL )
5074 OR EXISTS (SELECT 1
5075 FROM psp_pre_gen_dist_lines_history ppg
5076 WHERE ppg.summary_line_id = psl.summary_line_id
5077 AND ppg.distribution_date BETWEEN p_effort_start
5078 AND p_effort_end
5079 AND ppg.adjustment_batch_name IS NULL
5080 AND ppg.reversal_entry_flag IS NULL)
5081 OR EXISTS (SELECT 1
5082 FROM psp_adjustment_lines_history palh
5083 WHERE palh.summary_line_id = psl.summary_line_id
5084 AND palh.distribution_date BETWEEN p_effort_start
5085 AND p_effort_end
5086 AND palh.adjustment_batch_name IS NULL
5087 AND palh.reversal_entry_flag IS NULL
5088 AND NVL(palh.original_line_flag, 'N') ='N')));
5089 ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5090 DELETE FROM psp_selected_persons_t pspt
5091 WHERE request_id = p_request_id
5092 /* Bug 5087294 : Performance fix replacing not in with not exists */
5093 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5094 AND NOT EXISTS ( SELECT 1
5095 FROM per_assignments_f paf,
5096 psp_report_template_details_h prtd
5097 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5098 AND criteria_lookup_code='PAY'
5099 AND include_exclude_flag='I'
5100 AND paf.assignment_type = 'E'
5101 and
5102 effective_start_date <= p_effort_end and
5103 effective_end_date >= p_effort_start
5104 AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
5105 AND prtd.request_id = p_request_id
5106 AND paf.assignment_id = pspt.assignment_id );
5107 ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5108 DELETE FROM psp_selected_persons_t pspt
5109 WHERE request_id = p_request_id
5110 /* Bug 5087294 : Performance fix replacing not in with not exists */
5111 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5112 AND NOT EXISTS ( SELECT 1
5113 FROM per_assignments_f paf ,
5114 psp_report_template_details_h prtd
5115 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5116 AND criteria_lookup_code='LOC'
5117 AND paf.assignment_type = 'E'
5118 and
5119 effective_start_date <= p_effort_end and
5120 effective_end_date >= p_effort_start
5121 AND include_exclude_flag='I'
5122 AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
5123 AND prtd.request_id = p_request_id
5124 AND paf.assignment_id = pspt.assignment_id);
5125 ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5126 /* Bug 5087294 : Performance fix replacing not in with not exists */
5127 DELETE FROM psp_selected_persons_t pspt
5128 WHERE request_id = p_request_id
5129 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5130 AND NOT EXISTS ( SELECT 1
5131 FROM per_assignments_f paf ,
5132 psp_report_template_details_h prtd
5133 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5134 AND criteria_lookup_code='ORG'
5135 AND include_exclude_flag='I'
5136 AND paf.assignment_type = 'E'
5137 and effective_start_date <= p_effort_end and
5138 effective_end_date >= p_effort_start
5139 AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
5140 AND prtd.request_id = p_request_id
5141 AND paf.assignment_id = pspt.assignment_id );
5142
5143 ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5144 DELETE FROM psp_selected_persons_t pspt
5145 WHERE request_id = p_request_id
5146 /* Bug 5087294 : Performance fix replacing not in with not exists */
5147 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5148 AND NOT EXISTS ( SELECT 1
5149 FROM per_assignments_f paf,
5150 pay_payrolls_f ppf
5151 WHERE ppf.payroll_id = paf.payroll_id
5152 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5153 FROM psp_report_template_details_h prtd
5154 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5155 AND criteria_lookup_code='CST'
5156 AND include_exclude_flag='I'
5157 AND request_id = p_request_id)
5158 AND paf.assignment_type = 'E'
5159 AND ppf.effective_start_date <= p_effort_end
5160 AND ppf.effective_end_date >= p_effort_start
5161 AND paf.effective_start_date <= p_effort_end
5162 AND paf.effective_end_date >= p_effort_start
5163 AND paf.assignment_id = pspt.assignment_id );
5164 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
5165 DELETE FROM psp_selected_persons_t
5166 WHERE request_id = p_request_id
5167 AND assignment_id NOT IN (SELECT NVL(paf.assignment_id,0)
5168 FROM per_all_assignments_f paf,
5169 hr_assignment_sets has,
5170 hr_assignment_set_amendments hasa
5171 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
5172 FROM psp_report_template_details_h prtd
5173 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5174 AND criteria_lookup_code='AST'
5175 AND include_exclude_flag='I'
5176 AND request_id =p_request_id)
5177 AND ( (paf.payroll_id = has.payroll_id
5178 AND paf.assignment_type = 'E'
5179 AND paf.effective_start_date <= p_effort_end
5180 AND paf.effective_end_date >= p_effort_start
5181 AND has.assignment_set_id = hasa.assignment_set_id)
5182 OR (paf.assignment_id = hasa.assignment_id
5183 AND paf.assignment_type = 'E'
5184 AND paf.effective_start_date <= p_effort_end
5185 AND paf.effective_end_date >= p_effort_start
5186 AND hasa.assignment_set_id=has.assignment_set_id
5187 AND include_or_exclude ='I'))
5188 AND NOT EXISTS (SELECT assignment_id
5189 FROM hr_assignment_set_amendments hasa
5190 WHERE hasa.assignment_id = paf.assignment_id
5191 AND paf.assignment_type = 'E'
5192 AND hasa.include_or_exclude ='E'
5193 AND paf.effective_start_date <= p_effort_end
5194 AND paf.effective_end_date >= p_effort_start));
5195 ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5196 DELETE FROM psp_selected_persons_t pspt
5197 WHERE request_id = p_request_id
5198 /* Bug 5087294 : Performance fix replacing not in with not exists */
5199 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5200 AND NOT EXISTS ( SELECT 1
5201 FROM per_assignments_f paf ,
5202 psp_report_template_details_h prtd
5203 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5204 AND criteria_lookup_code='JOB'
5205 AND include_exclude_flag='I'
5206 AND paf.assignment_type = 'E'
5207 and
5208 effective_start_date <= p_effort_end and
5209 effective_end_date >= p_effort_start
5210 AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
5211 AND prtd.request_id = p_request_id
5212 AND paf.assignment_id = pspt.assignment_id);
5213 ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5214 DELETE FROM psp_selected_persons_t pspt
5215 WHERE request_id = p_request_id
5216 /* Bug 5087294 : Performance fix replacing not in with not exists */
5217 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5218 AND NOT EXISTS ( SELECT 1
5219 FROM per_assignments_f paf ,
5220 psp_report_template_details_h prtd
5221 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5222 AND criteria_lookup_code='POS'
5223 AND paf.assignment_type = 'E'
5224 and
5225 effective_start_date <= p_effort_end and
5226 effective_end_date >= p_effort_start
5227 AND include_exclude_flag='I'
5228 AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
5229 AND prtd.request_id = p_request_id
5230 AND paf.assignment_id = pspt.assignment_id);
5231 ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5232 DELETE FROM psp_selected_persons_t pspt
5233 WHERE request_id = p_request_id
5234 /* Bug 5087294 : Performance fix replacing not in with not exists */
5235 -- AND assignment_id NOT IN (SELECT NVL(assignment_id,0)
5236 AND NOT EXISTS ( SELECT 1
5237 FROM per_assignments_f paf ,
5238 psp_report_template_details_h prtd
5239 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5240 AND criteria_lookup_code='ASS'
5241 AND include_exclude_flag='I'
5242 AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
5243 AND prtd.request_id = p_request_id
5244 AND paf.assignment_type = 'E'
5245 AND effective_start_date <= p_effort_end
5246 AND effective_end_date >= p_effort_start
5247 AND paf.assignment_id = pspt.assignment_id);
5248 ELSIF template_sel_criteria.array_sel_criteria(i)='PPG' THEN
5249 OPEN ppg_cursor;
5250 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
5251 IF l_criteria_value1 IS NOT NULL THEN
5252 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 ||'''' ;
5253
5254 LOOP
5255 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
5256 EXIT WHEN PPG_CURSOR%NOTFOUND;
5257
5258 g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 ||
5259 ' = ' || '''' || l_criteria_value2||'''';
5260 l_sql_string:= g_exec_string;
5261 END LOOP;
5262 CLOSE ppg_cursor;
5263
5264 IF l_sql_string IS NOT NULL THEN
5265 g_exec_string := 'DELETE FROM psp_selected_persons_t sel
5266 WHERE request_id = :request_id
5267 AND NOT EXISTS (SELECT 1
5268 FROM per_assignments_f paf, pay_people_groups ppg
5269 WHERE paf.people_group_id= ppg.people_group_id
5270 AND paf.assignment_type = ''' || 'E' || '''
5271 AND paf.effective_end_date >= :p_effort_Start
5272 AND paf.effective_start_date <= :p_effort_end
5273 AND (' || l_sql_string || ')
5274 AND paf.assignment_id = sel.assignment_id )';
5275
5276 EXECUTE IMMEDIATE g_exec_string USING IN p_request_id,
5277 p_Effort_start, p_effort_end;
5278 END IF;
5279 END IF;
5280 ELSIF template_sel_criteria.array_sel_criteria(i)='GLA' THEN
5281 OPEN gla_cursor;
5282 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
5283 IF l_criteria_value1 IS NOT NULL THEN
5284 l_sql_string := l_criteria_value1 ||' BETWEEN '|| ''''||
5285 l_criteria_value2 || '''' || ' AND ' || '''' ||
5286 l_criteria_value3 || '''';
5287 LOOP
5288 FETCH GLA_CURSOR INTO l_criteria_value1, l_criteria_value2,
5289 l_criteria_value3;
5290 EXIT WHEN GLA_CURSOR%NOTFOUND;
5291
5292 g_exec_string:= l_sql_string || ' OR ' || l_criteria_value1 ||
5293 ' BETWEEN ' || ''''|| l_criteria_value2 || '''' ||
5294 ' AND ' || ''''|| l_criteria_value3 || '''' ;
5295 l_sql_string:=g_exec_string;
5296 END LOOP;
5297 CLOSE gla_cursor;
5298
5299 IF l_sql_string IS NOT NULL THEN
5300 g_exec_string := 'DELETE FROM psp_selected_persons_t
5301 WHERE assignment_id NOT IN (SELECT NVL(psl.assignment_id,0)
5302 FROM psp_summary_lines psl, psp_distribution_lines_history pdnh,
5303 psp_adjustment_lines_history palh,
5304 psp_pre_gen_dist_lines_history ppg, gl_code_combinations gcc
5305 WHERE gcc.code_combination_id= psl.gl_code_combination_id
5306 AND psl.business_group_id = '|| p_business_group_id || '
5307 AND psl.set_of_books_id = ' || p_set_of_books_id || '
5308 AND psl.summary_line_id = pdnh.summary_line_id(+)
5309 AND psl.summary_line_id = ppg.summary_line_id(+)
5310 AND psl.summary_line_id = palh.summary_line_id(+)
5311 AND psl.status_code='||''''||'A'||''''||'
5312 AND ( (psl.source_type IN ('||''''||'N'||''''||' ,'|| ''''|| 'O'||''''||')
5313 AND pdnh.distribution_date BETWEEN
5314 :p_effort_start AND :p_effort_end
5315 AND pdnh.reversal_entry_flag IS NULL
5316 AND psl.summary_line_id = pdnh.summary_line_id
5317 AND pdnh.adjustment_batch_name IS NULL)
5318 OR (psl.source_type='||''''||'P'||''''||'
5319 AND ppg.distribution_date BETWEEN :p_effort_start AND :p_effort_end
5320 AND ppg.adjustment_batch_name IS NULL
5321 AND ppg.summary_line_id =psl.summary_line_id
5322 AND ppg.reversal_entry_flag IS NULL)
5323 OR (psl.source_type='||''''||'A'||''''||'
5324 AND palh.summary_line_id =psl.summary_line_id
5325 AND palh.adjustment_batch_name IS NULL
5326 AND NVL(palh.original_line_flag, ' || ''''||
5327 'N' || ''''|| ') = '||''''|| 'N' || '''' || '
5328 AND palh.distribution_date BETWEEN
5329 :p_effort_start AND :p_effort_end))
5330 AND gcc.code_combination_id= psl.gl_code_combination_id
5331 AND gcc.code_combination_id IN (SELECT code_combination_id
5332 FROM gl_code_combinations
5333 WHERE ' || l_sql_string || ' ))
5334 AND request_id = :request_id';
5335
5336 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_start,
5337 p_effort_end , p_effort_start, p_effort_end,
5338 p_effort_start, p_effort_end, p_request_id;
5339 END IF;
5340 END IF;
5341 END IF;
5342 END IF;
5343 END IF;
5344 END LOOP;
5345 END prune_initial_asg_list;
5346
5347 PROCEDURE APPLY_ASG_EXCLUSION_CRITERIA (p_request_id IN NUMBER,
5348 p_effort_start IN DATE,
5349 p_effort_end IN DATE,
5350 p_business_group_id IN NUMBER,
5351 p_set_of_books_id IN NUMBER) IS
5352 CURSOR PPG_CURSOR IS
5353 SELECT criteria_value1, criteria_value2
5354 FROM psp_report_template_details_h
5355 WHERE request_id = p_request_id
5356 AND criteria_lookup_type= 'PSP_SELECTION_CRITERIA'
5357 AND criteria_lookup_code='PPG'
5358 AND include_exclude_flag='E';
5359
5360 CURSOR GLA_CURSOR IS
5361 SELECT criteria_value1, criteria_value2, criteria_value3
5362 FROM psp_report_template_details_h
5363 WHERE request_id = p_request_id
5364 AND criteria_lookup_type ='PSP_SELECTION_CRITERIA'
5365 AND criteria_lookup_code ='GLA'
5366 AND include_exclude_flag='E';
5367
5368 TYPE t_varchar_30_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
5369 TYPE t_varchar_1_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
5370
5371 l_criteria_value1 VARCHAR2(30);
5372 l_criteria_value2 VARCHAR2(60); -- Bug 8257434
5373 l_criteria_value3 VARCHAR2(30);
5374 l_sql_string VARCHAR2(1000);
5375 BEGIN
5376 FOR i IN 1..template_sel_criteria.array_sel_criteria.COUNT
5377 LOOP
5378 IF template_sel_criteria.array_inc_exc_flag(i) = 'E' THEN
5379 IF template_sel_criteria.array_sel_criteria(i) = 'PTY' THEN
5380 DELETE FROM psp_selected_persons_t
5381 WHERE request_id = p_request_id
5382 AND person_id IN (SELECT ppf.person_id
5383 FROM per_people_f ppf,
5384 psp_report_template_details_h prtd,
5385 per_assignments_f paf
5386 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5387 AND criteria_lookup_code='PTY'
5388 AND paf.person_id = ppf.person_id
5389 AND paf.assignment_type = 'E'
5390 AND paf.effective_start_date <= p_effort_end
5391 AND paf.effective_end_date >= p_effort_start
5392 and
5393 ppf.effective_start_date <= p_effort_end and
5394 ppf.effective_end_date >= p_effort_start
5395 AND include_exclude_flag='E'
5396 AND ppf.person_type_id = TO_NUMBER(prtd.criteria_value1)
5397 AND prtd.request_id = p_request_id);
5398 ELSIF template_sel_criteria.array_sel_criteria(i) = 'EMP' THEN
5399 DELETE FROM psp_selected_persons_t
5400 WHERE request_id = p_request_id
5401 AND person_id IN (SELECT DISTINCT ppf.person_id
5402 FROM per_all_people_f ppf, per_assignments_f paf
5403 WHERE ppf.person_id IN (SELECT TO_NUMBER(criteria_value1)
5404 FROM psp_report_template_details_h prtd
5405 WHERE prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5406 AND criteria_lookup_code='EMP'
5407 AND include_exclude_flag='E'
5408 AND prtd.request_id = p_request_id)
5409 AND paf.person_id = ppf.person_id
5410 AND paf.assignment_type = 'E'
5411 AND paf.effective_start_date <= p_effort_end
5412 AND paf.effective_end_date >= p_effort_start
5413 AND ppf.effective_start_date <= p_effort_end
5414 AND ppf.effective_end_date >= p_effort_start);
5415 ELSIF template_sel_criteria.array_sel_criteria(i) ='SUP' THEN
5416 DELETE FROM psp_selected_persons_t
5417 WHERE request_id = p_request_id
5418 AND assignment_id IN (SELECT NVL(assignment_id,0)
5419 FROM per_all_assignments_f paf,
5420 psp_report_template_details_h prtd
5421 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5422 AND criteria_lookup_code='SUP'
5423 AND include_exclude_flag='E'
5424 AND paf.supervisor_id = TO_NUMBER(prtd.criteria_value1)
5425 AND prtd.request_id = p_request_id
5426 AND paf.assignment_type = 'E'
5427 AND paf.effective_start_date <= p_effort_end
5428 AND paf.effective_end_date >= p_effort_start);
5429 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AWD' THEN
5430 DELETE FROM psp_selected_persons_t
5431 WHERE request_id = p_request_id
5432 AND assignment_id IN (SELECT psl.assignment_id
5433 FROM psp_summary_lines psl,
5434 psp_report_template_details_h prtd,
5435 per_time_periods ptp
5436 WHERE
5437 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5438 AND prtd.criteria_lookup_code='AWD'
5439 AND prtd.include_exclude_flag='E'
5440 AND prtd.request_id =p_request_id
5441 and psl.award_id = TO_NUMBER(prtd.criteria_value1)
5442 AND psl.business_group_id = p_business_group_id
5443 AND psl.set_of_books_id = p_set_of_books_id
5444 AND psl.status_code= 'A'
5445 AND ptp.time_period_id = psl.time_period_id
5446 AND (ptp.start_date <= p_effort_end
5447 AND ptp.end_date >= p_effort_start)
5448 AND ( EXISTS (SELECT 1
5449 FROM psp_distribution_lines_history pdnh
5450 WHERE pdnh.summary_line_id = psl.summary_line_id
5451 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5452 AND pdnh.reversal_entry_flag IS NULL
5453 AND pdnh.adjustment_batch_name IS NULL)
5454 OR EXISTS (SELECT 1
5455 FROM psp_pre_gen_dist_lines_history ppg
5456 WHERE ppg.summary_line_id = psl.summary_line_id
5457 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
5458 AND ppg.adjustment_batch_name IS NULL
5459 AND ppg.reversal_entry_flag IS NULL)
5460 OR EXISTS (SELECT 1
5461 FROM psp_adjustment_lines_history palh
5462 WHERE palh.summary_line_id = psl.summary_line_id
5463 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
5464 AND palh.adjustment_batch_name IS NULL
5465 AND palh.reversal_entry_flag IS NULL
5466 AND NVL(palh.original_line_flag, 'N') ='N')));
5467 ELSIF template_sel_criteria.array_sel_criteria(i) = 'ATY' THEN
5468 DELETE FROM psp_selected_persons_t
5469 WHERE request_id = p_request_id
5470 AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5471 FROM psp_summary_lines psl,
5472 psp_report_template_details_h prtd,
5473 gms_awards_all gaa,
5474 per_time_periods ptp
5475 WHERE psl.award_id = gaa.award_id
5476 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5477 AND prtd.criteria_lookup_code='ATY'
5478 AND prtd.include_exclude_flag='E'
5479 AND prtd.request_id =p_request_id
5480 AND gaa.type=prtd.criteria_value1
5481 AND psl.business_group_id = p_business_group_id
5482 AND psl.set_of_books_id = p_set_of_books_id
5483 AND psl.status_code= 'A'
5484 AND ptp.time_period_id = psl.time_period_id
5485 AND (ptp.start_date <= p_effort_end
5486 AND ptp.end_date >= p_effort_start)
5487 AND ( EXISTS (SELECT 1
5488 FROM psp_distribution_lines_history pdnh
5489 WHERE pdnh.summary_line_id = psl.summary_line_id
5490 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5491 AND pdnh.reversal_entry_flag IS NULL
5492 AND pdnh.adjustment_batch_name IS NULL)
5493 OR EXISTS (SELECT 1
5494 FROM psp_pre_gen_dist_lines_history ppg
5495 WHERE ppg.summary_line_id = psl.summary_line_id
5496 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
5497 AND ppg.adjustment_batch_name IS NULL
5498 AND ppg.reversal_entry_flag IS NULL)
5499 OR EXISTS (SELECT 1
5500 FROM psp_adjustment_lines_history palh
5501 WHERE palh.summary_line_id = psl.summary_line_id
5502 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
5503 AND palh.adjustment_batch_name IS NULL
5504 AND palh.reversal_entry_flag IS NULL
5505 AND NVL(palh.original_line_flag, 'N') ='N')));
5506 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRT' THEN
5507 DELETE FROM psp_selected_persons_t
5508 WHERE request_id = p_request_id
5509 AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5510 FROM psp_summary_lines psl,
5511 psp_report_template_details_h prtd,
5512 pa_projects_all ppa,
5513 per_time_periods ptp
5514 WHERE psl.project_id = ppa.project_id
5515 AND prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5516 AND prtd.criteria_lookup_code='PRT'
5517 AND prtd.include_exclude_flag='E'
5518 AND prtd.request_id =p_request_id
5519 AND ppa.project_type=prtd.criteria_value1
5520 AND psl.business_group_id = p_business_group_id
5521 AND psl.set_of_books_id = p_set_of_books_id
5522 AND psl.status_code= 'A'
5523 AND ptp.time_period_id = psl.time_period_id
5524 AND (ptp.start_date <= p_effort_end
5525 AND ptp.end_date >= p_effort_start)
5526 AND ( EXISTS (SELECT 1
5527 FROM psp_distribution_lines_history pdnh
5528 WHERE pdnh.summary_line_id = psl.summary_line_id
5529 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5530 AND pdnh.reversal_entry_flag IS NULL
5531 AND pdnh.adjustment_batch_name IS NULL)
5532 OR EXISTS (SELECT 1
5533 FROM psp_pre_gen_dist_lines_history ppg
5534 WHERE ppg.summary_line_id = psl.summary_line_id
5535 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
5536 AND ppg.adjustment_batch_name IS NULL
5537 AND ppg.reversal_entry_flag IS NULL)
5538 OR EXISTS (SELECT 1
5539 FROM psp_adjustment_lines_history palh
5540 WHERE palh.summary_line_id = psl.summary_line_id
5541 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
5542 AND palh.adjustment_batch_name IS NULL
5543 AND palh.reversal_entry_flag IS NULL
5544 AND NVL(palh.original_line_flag, 'N') ='N')));
5545 ELSIF template_sel_criteria.array_sel_criteria(i) ='PRJ' THEN
5546 DELETE FROM psp_selected_persons_t
5547 WHERE request_id = p_request_id
5548 AND assignment_id IN (SELECT NVL(psl.assignment_id,0)
5549 FROM psp_summary_lines psl,
5550 psp_report_template_details_h prtd,
5551 per_time_periods ptp
5552 WHERE
5553 prtd.criteria_lookup_type='PSP_SELECTION_CRITERIA'
5554 AND prtd.criteria_lookup_code='PRJ'
5555 AND prtd.include_exclude_flag='E'
5556 AND prtd.request_id =p_request_id
5557 and psl.project_id = TO_NUMBER(prtd.criteria_value1)
5558 AND psl.business_group_id = p_business_group_id
5559 AND psl.set_of_books_id = p_set_of_books_id
5560 AND psl.status_code= 'A'
5561 AND ptp.time_period_id = psl.time_period_id
5562 AND (ptp.start_date <= p_effort_end
5563 AND ptp.end_date >= p_effort_start)
5564 AND ( EXISTS (SELECT 1
5565 FROM psp_distribution_lines_history pdnh
5566 WHERE pdnh.summary_line_id = psl.summary_line_id
5567 AND pdnh.distribution_date BETWEEN p_effort_start AND p_effort_end
5568 AND pdnh.reversal_entry_flag IS NULL
5569 AND pdnh.adjustment_batch_name IS NULL)
5570 OR EXISTS (SELECT 1
5571 FROM psp_pre_gen_dist_lines_history ppg
5572 WHERE ppg.summary_line_id = psl.summary_line_id
5573 AND ppg.distribution_date BETWEEN p_effort_start AND p_effort_end
5574 AND ppg.adjustment_batch_name IS NULL
5575 AND ppg.reversal_entry_flag IS NULL)
5576 OR EXISTS (SELECT 1
5577 FROM psp_adjustment_lines_history palh
5578 WHERE palh.summary_line_id = psl.summary_line_id
5579 AND palh.distribution_date BETWEEN p_effort_start AND p_effort_end
5580 AND palh.adjustment_batch_name IS NULL
5581 AND palh.reversal_entry_flag IS NULL
5582 AND NVL(palh.original_line_flag, 'N') ='N')));
5583 ELSIF template_sel_criteria.array_sel_criteria(i) ='PAY' THEN
5584 DELETE FROM psp_selected_persons_t
5585 WHERE request_id = p_request_id
5586 AND assignment_id IN (SELECT assignment_id
5587 FROM per_assignments_f paf,
5588 psp_report_template_details_h prtd
5589 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5590 AND criteria_lookup_code='PAY'
5591 AND include_exclude_flag='E'
5592 AND paf.assignment_type = 'E'
5593 and effective_start_date <= p_effort_end and
5594 effective_end_date >= p_effort_start
5595 AND paf.payroll_id = TO_NUMBER(prtd.criteria_value1)
5596 AND prtd.request_id = p_request_id);
5597 ELSIF template_sel_criteria.array_sel_criteria(i) ='LOC' THEN
5598 DELETE FROM psp_selected_persons_t
5599 WHERE request_id = p_request_id
5600 AND assignment_id IN (SELECT assignment_id
5601 FROM per_assignments_f paf,
5602 psp_report_template_details_h prtd
5603 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5604 AND criteria_lookup_code='LOC'
5605 AND include_exclude_flag='E'
5606 AND paf.assignment_type = 'E'
5607 and effective_start_date <= p_effort_end and
5608 effective_end_date >= p_effort_start
5609 AND paf.location_id = TO_NUMBER(prtd.criteria_value1)
5610 AND prtd.request_id = p_request_id);
5611 ELSIF template_sel_criteria.array_sel_criteria(i) ='ORG' THEN
5612 DELETE FROM psp_selected_persons_t
5613 WHERE request_id = p_request_id
5614 AND assignment_id IN (SELECT assignment_id
5615 FROM per_assignments_f paf,
5616 psp_report_template_details_h prtd
5617 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5618 AND criteria_lookup_code='ORG'
5619 AND include_exclude_flag='E'
5620 AND paf.assignment_type = 'E'
5621 and effective_start_date <= p_effort_end and
5622 effective_end_date >= p_effort_start
5623 AND paf.organization_id = TO_NUMBER(prtd.criteria_value1)
5624 AND prtd.request_id = p_request_id);
5625 ELSIF template_sel_criteria.array_sel_criteria(i) = 'JOB' THEN
5626 DELETE FROM psp_selected_persons_t
5627 WHERE request_id = p_request_id
5628 AND assignment_id IN (SELECT assignment_id
5629 FROM per_assignments_f paf,
5630 psp_report_template_details_h prtd
5631 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5632 AND criteria_lookup_code='JOB'
5633 AND include_exclude_flag='E'
5634 AND paf.assignment_type = 'E'
5635 and effective_start_date <= p_effort_end and
5636 effective_end_date >= p_effort_start
5637 AND paf.job_id = TO_NUMBER(prtd.criteria_value1)
5638 AND prtd.request_id = p_request_id);
5639 ELSIF template_sel_criteria.array_sel_criteria(i) ='POS' THEN
5640 DELETE FROM psp_selected_persons_t
5641 WHERE request_id = p_request_id
5642 AND assignment_id IN (SELECT assignment_id
5643 FROM per_assignments_f paf,
5644 psp_report_template_details_h prtd
5645 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5646 AND criteria_lookup_code='POS'
5647 AND include_exclude_flag='E'
5648 AND paf.assignment_type = 'E'
5649 and effective_start_date <= p_effort_end and
5650 effective_end_date >= p_effort_start
5651 AND paf.position_id = TO_NUMBER(prtd.criteria_value1)
5652 AND prtd.request_id = p_request_id);
5653 ELSIF template_sel_criteria.array_sel_criteria(i) ='ASS' THEN
5654 DELETE FROM psp_selected_persons_t
5655 WHERE request_id = p_request_id
5656 AND assignment_id IN (SELECT assignment_id
5657 FROM per_assignments_f paf,
5658 psp_report_template_details_h prtd
5659 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5660 AND criteria_lookup_code='ASS'
5661 AND include_exclude_flag='E'
5662 AND paf.assignment_type = 'E'
5663 AND paf.assignment_status_type_id = TO_NUMBER(prtd.criteria_value1)
5664 AND prtd.request_id = p_request_id
5665 AND effective_start_date <= p_effort_end
5666 AND effective_end_date >= p_effort_start);
5667 ELSIF template_sel_criteria.array_sel_criteria(i)='CST' THEN
5668 DELETE FROM psp_selected_persons_t
5669 WHERE request_id = p_request_id
5670 AND assignment_id IN (SELECT NVL(assignment_id,0)
5671 FROM per_assignments_f paf,
5672 pay_payrolls_f ppf
5673 WHERE ppf.payroll_id = paf.payroll_id
5674 AND ppf.consolidation_set_id IN (SELECT TO_NUMBER(criteria_value1)
5675 FROM psp_report_template_details_h prtd
5676 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5677 AND criteria_lookup_code='CST'
5678 AND include_exclude_flag='E'
5679 AND request_id = p_request_id)
5680 AND paf.assignment_type = 'E'
5681 AND ppf.effective_start_date <= p_effort_end
5682 AND ppf.effective_end_date >= p_effort_start
5683 AND paf.effective_start_date <= p_effort_end
5684 AND paf.effective_end_date >= p_effort_start);
5685 ELSIF template_sel_criteria.array_sel_criteria(i) = 'AST' THEN
5686 DELETE FROM psp_selected_persons_t
5687 WHERE request_id = p_request_id
5688 AND assignment_id IN (SELECT NVL(paf.assignment_id,0)
5689 FROM per_all_assignments_f paf, hr_assignment_sets has,
5690 hr_assignment_set_amendments hasa
5691 WHERE has.assignment_set_id IN (SELECT TO_NUMBER(criteria_value1)
5692 FROM psp_report_template_details_h prtd
5693 WHERE criteria_lookup_type='PSP_SELECTION_CRITERIA'
5694 AND criteria_lookup_code='AST'
5695 AND include_exclude_flag='E'
5696 AND request_id =p_request_id)
5697 AND ( (paf.payroll_id = has.payroll_id
5698 AND paf.assignment_type = 'E'
5699 AND paf.effective_start_date <= p_effort_end
5700 AND paf.effective_end_date >= p_effort_start
5701 AND has.assignment_set_id = hasa.assignment_set_id)
5702 OR (paf.assignment_id = hasa.assignment_id
5703 AND paf.assignment_type = 'E'
5704 AND paf.effective_start_date <= p_effort_end
5705 AND paf.effective_end_date >= p_effort_start
5706 AND hasa.assignment_set_id=has.assignment_set_id
5707 AND include_or_exclude ='I'))
5708 AND NOT EXISTS (SELECT assignment_id
5709 FROM hr_assignment_set_amendments hasa
5710 WHERE hasa.assignment_id = paf.assignment_id
5711 AND paf.assignment_type = 'E'
5712 AND hasa.include_or_exclude ='E'
5713 AND paf.effective_start_date <= p_effort_end
5714 AND paf.effective_end_date >= p_effort_start));
5715 ELSIF template_sel_criteria.array_sel_criteria(i)='PPG' THEN
5716 OPEN ppg_cursor;
5717 FETCH PPG_CURSOR into l_criteria_value1, l_criteria_value2;
5718 IF l_criteria_value1 IS NOT NULL THEN
5719 l_sql_string := l_criteria_value1 ||' = '||''''|| l_criteria_value2 || '''' ;
5720
5721 LOOP
5722 FETCH PPG_CURSOR INTO l_criteria_value1, l_criteria_value2;
5723 EXIT WHEN PPG_CURSOR%NOTFOUND;
5724
5725 g_exec_string := l_sql_string || ' OR '|| l_criteria_value1 || ' = ' ||
5726 '''' || l_criteria_value2||'''';
5727 l_sql_string:= g_exec_string;
5728 END LOOP;
5729 CLOSE ppg_cursor;
5730
5731 IF l_sql_string IS NOT NULL THEN
5732 g_exec_string := 'DELETE FROM psp_selected_persons_t
5733 WHERE assignment_id IN (SELECT assignment_id
5734 FROM per_assignments_f paf,
5735 pay_people_groups ppg
5736 WHERE paf.people_group_id= ppg.people_group_id
5737 AND paf.assignment_type = ''' || 'E' || '''
5738 AND paf.effective_end_date >= :p_effort_Start
5739 AND paf.effective_start_date <= :p_effort_end
5740 AND ppg.people_group_id IN (SELECT people_group_id
5741 FROM pay_people_groups
5742 WHERE ' || l_sql_string || '))
5743 AND request_id = :request_id';
5744
5745 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_start, p_effort_end,
5746 p_request_id;
5747 END IF;
5748 END IF;
5749 ELSIF template_sel_criteria.array_sel_criteria(i)='GLA' THEN
5750 OPEN gla_cursor;
5751 FETCH gla_cursor INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
5752 IF l_criteria_value1 IS NOT NULL THEN
5753 l_sql_string := l_criteria_value1 ||' BETWEEN '|| '''' || l_criteria_value2 ||
5754 '''' || ' AND ' || '''' || l_criteria_value3 || '''' ;
5755 LOOP
5756 FETCH gla_cursor INTO l_criteria_value1, l_criteria_value2, l_criteria_value3;
5757 EXIT WHEN gla_cursor%NOTFOUND;
5758
5759 g_exec_string:= l_sql_string || ' OR ' || l_criteria_value1 ||
5760 ' BETWEEN ' || ''''|| l_criteria_value2 || '''' ||
5761 ' AND ' || ''''|| l_criteria_value3 || '''' ;
5762 l_sql_string:= g_exec_string;
5763 END LOOP;
5764 CLOSE gla_cursor;
5765
5766 IF l_sql_string IS NOT NULL THEN
5767 g_exec_string := 'DELETE FROM psp_selected_persons_t
5768 WHERE assignment_id IN (SELECT NVL(psl.assignment_id,0)
5769 FROM psp_summary_lines psl,
5770 psp_distribution_lines_history pdnh,
5771 psp_adjustment_lines_history palh,
5772 psp_pre_gen_dist_lines_history ppg,
5773 gl_code_combinations gcc
5774 WHERE gcc.code_combination_id= psl.gl_code_combination_id
5775 AND psl.business_group_id = '|| p_business_group_id || '
5776 AND psl.set_of_books_id = ' || p_set_of_books_id || '
5777 AND psl.summary_line_id = pdnh.summary_line_id(+)
5778 AND psl.summary_line_id = ppg.summary_line_id(+)
5779 AND psl.summary_line_id = palh.summary_line_id(+)
5780 AND psl.status_code='||''''||'A'||''''||'
5781 AND ( (psl.source_type IN ('||''''||'N'||''''||
5782 ' ,'|| ''''|| 'O'||''''||')
5783 AND pdnh.distribution_date BETWEEN
5784 :p_effort_start AND :p_effort_end
5785 AND pdnh.reversal_entry_flag IS NULL
5786 AND psl.summary_line_id =pdnh.summary_line_id
5787 AND pdnh.adjustment_batch_name IS NULL)
5788 OR (psl.source_type= '||''''||'P'||''''||'
5789 AND ppg.distribution_date BETWEEN
5790 :p_effort_start AND :p_effort_end
5791 AND ppg.adjustment_batch_name IS NULL
5792 AND ppg.summary_line_id =psl.summary_line_id
5793 AND ppg.reversal_entry_flag IS NULL)
5794 OR (psl.source_type= '||''''||'A'||''''||'
5795 AND palh.summary_line_id =psl.summary_line_id
5796 AND palh.adjustment_batch_name IS NULL
5797 AND NVL(palh.original_line_flag, ' ||
5798 ''''|| 'N' || ''''|| ') = '||''''||
5799 'N' || '''' || '
5800 AND palh.distribution_date BETWEEN
5801 :p_effort_start AND :p_effort_end))
5802 AND gcc.code_combination_id= psl.gl_code_combination_id
5803 AND gcc.code_combination_id IN (SELECT code_combination_id
5804 FROM gl_code_combinations
5805 WHERE ' || l_sql_string || '))
5806 AND request_id = :request_id';
5807
5808 EXECUTE IMMEDIATE g_exec_string USING IN p_effort_start, p_effort_end,
5809 p_effort_start, p_effort_end, p_effort_start, p_effort_end, p_request_id;
5810 END IF;
5811 END IF;
5812 END IF;
5813 END IF;
5814 END LOOP;
5815 END APPLY_ASG_EXCLUSION_CRITERIA;
5816
5817 END;
[Home] [Help]