1 PACKAGE pqp_gb_t1_pension_extracts AS
2 -- /* $Header: pqpgbtp1.pkh 120.5.12010000.6 2008/10/16 11:54:42 dchindar ship $ */
3 --
4 -- Debug Variables.
5 --
6 g_proc_name VARCHAR2(61):= 'pqp_gb_t1_pension_extracts.';
7 g_nested_level NUMBER:= 0;
8 --
9 -- Global Varibales
10 --
11 g_business_group_id NUMBER:= NULL; -- IMPORTANT TO KEEP NULL
12 g_master_bg_id NUMBER:= NULL;
13 g_legislation_code VARCHAR2(10):= 'GB';
14 g_effective_date DATE;
15
16 g_extract_type VARCHAR2(30);
17 g_last_effective_date DATE;
18 g_next_effective_date DATE;
19 g_effective_run_date DATE;
20
21 -- Introduced for Type 1 only
22 g_pension_year_start_date DATE;
23 g_pension_year_end_date DATE;
24
25 g_extract_udt_name VARCHAR2(80);
26 g_criteria_location_code VARCHAR2(20);
27 g_lea_number VARCHAR2(3):=RPAD(' ',3,' ');
28 g_crossbg_enabled VARCHAR2(1) := 'N';
29 g_cross_per_enabled VARCHAR2(1);
30 g_estb_number VARCHAR2(4):='0000';
31 g_originators_title VARCHAR2(16);
32 g_header_system_element VARCHAR2(200);
33
34 g_reporting_mode VARCHAR2(10);
35 g_trace VARCHAR2(1) := NULL;
36
37 g_oth_rate_type pay_user_column_instances_f.value%type;
38 g_sal_rate_type pay_user_column_instances_f.value%type;
39 g_sf_rate_type pay_user_column_instances_f.value%type;
40 g_lon_rate_type pay_user_column_instances_f.value%type;
41 g_asg_emp_cat_cd per_all_assignments_f.employment_category%TYPE;
42 g_ext_emp_cat_cd per_all_assignments_f.employment_category%TYPE;
43 g_ext_emp_wrkp_cd per_all_assignments_f.employment_category%TYPE;
44 -- g_abs_bal_type_id pay_balance_types.balance_type_id%type;
45 -- g_sal_bal_type_id pay_balance_types.balance_type_id%type;
46 g_primary_assignment_id per_all_assignments_f.assignment_id%TYPE;
47 g_equal_sal_rate VARCHAR2(1);
48 -- Added for bugfix 3073562:GAP1:GAP2
49 g_multiperson_mode VARCHAR2(1) := 'N';
50
51 -- Added for bugfix 3073562:GAP9b
52 g_supply_asg_count NUMBER;
53
54 -- Added for bugfix 3641851:ENH6
55 g_part_time_asg_count NUMBER;
56
57 -- Added for bugfix 3803760:TERMASG
58 g_asg_count NUMBER;
59
60 -- Added for bugfix 3803760:FTSUPPLY
61 g_override_ft_asg_id per_all_assignments_f.assignment_id%TYPE;
62
63 --added for raising warning for FT asg.
64 g_person_count NUMBER := 0;
65
66 g_teach_asg_count NUMBER;
67
68
69 --Added to check if person has been reported earlier : PERIODIC Report
70 -- PER_LVR change
71 -- this global not required now
72 -- coz, we need to check each leaver event in the results.
73 --g_person_already_reported VARCHAR2(1) := NULL ;
74
75 -- PER_LVR : Person LEaver changes
76 -- new date variable to keep track of the latest start date
77 -- associated with a person record,
78 -- after which there is no person leaver event
79 g_latest_start_date DATE;
80
81 --TERM_LSP:BUG :4135481 -- added a global to check for terminated employees
82 g_terminated_person VARCHAR2(1) := 'N';
83
84
85 -- RETRO:BUG: 4135481
86 -- defined balance id for the designaetd Balance type
87 --g_def_bal_id NUMBER := NULL ;
88
89 -- RETRO:BUG: 4135481
90 -- used for raising a warning for a person
91 -- if there are prorated/retro payments found
92 -- over thesame line of Service.
93 g_raise_retro_warning VARCHAR2(1):= 'N';
94
95 --CALC_PT_SAL_OPTIONS: BUG : 4135481
96 -- Two new rows are now seeded in the UDT for the role of switches
97 -- 1. "Part Time Salary Paid - Enable Date Earned Mode"
98 -- 2. "Part Time Salary Paid - Enable Calendar Day Proration"
99 -- First switch is for enabling / disabling the new logic for calculating part
100 -- time salary (based on date earned) or revert back to previous logic (date paid).
101 -- The second switch is for enabling / disabling calendar averaging, in case NO
102 -- matching proration events are found.
103
104 -- The following globals will be used to provide additional options for part
105 -- time salary computation methods in calc_part_time_sal function
106
107 g_calc_sal_new VARCHAR2(1):= NULL; -- use old/new method
108 g_proration VARCHAR2(1):= NULL; -- enable proration
109 g_calendar_avg VARCHAR2(1):= NULL; -- use calendar averaging
110 g_date_work_mode VARCHAR2(1):= NULL; -- date worked mode
111
112 g_supp_teacher VARCHAR2(1) := 'N';
113 -- Bug 3889646
114 TYPE asg_salary_rate_type IS RECORD
115 ( salary_rate NUMBER
116 , eff_start_date DATE
117 , eff_end_date DATE
118 , fte NUMBER );
119 TYPE t_asg_salary_rate_type IS TABLE OF asg_salary_rate_type INDEX BY BINARY_INTEGER;
120
121 -- 4336613 : no longer in use,replaced by local variables
122 -- g_asg_sal_rate t_asg_salary_rate_type ;
123
124 -- Increased size of event_type from 8K to 24K on 25/04/2002 as more events are now being
125 -- logged then previously planned
126 -- Bugfix 3073562:GAP10
127 -- Renamed new_value column to new_ext_emp_cat_cd
128 -- Added new column new_est_number
129 TYPE stored_events_type IS RECORD
130 (event_date DATE
131 ,event_type VARCHAR2(24000)
132 ,assignment_id per_all_assignments_f.assignment_id%TYPE
133 ,new_ext_emp_cat_cd ben_ext_rslt_dtl.val_01%TYPE
134 -- Bugfix 3470242:BUG1 : Now storing location_id as estb_number can
135 -- always be sought using location id
136 -- ,new_estb_number ben_ext_rslt_dtl.val_01%TYPE
137 ,new_location_id per_all_assignments_f.location_id%TYPE
138 ,pt_asg_count_change NUMBER
139 -- Added for bugfix 3803760:TERMASG
140 ,asg_count_change NUMBER
141 );
142
143
144 TYPE t_asg_events_type IS TABLE OF stored_events_type
145 INDEX BY BINARY_INTEGER;
146
147
148 TYPE leaver_dates_type IS RECORD
149 (start_date DATE
150 ,leaver_date DATE
151 ,restarter_date DATE
152 ,assignment_id per_all_assignments_f.assignment_id%TYPE
153 );
154
155 TYPE t_leaver_dates_type IS TABLE OF leaver_dates_type
156 INDEX BY BINARY_INTEGER;
157
158
159 -- 4336613 : PERF_ENHANC_3A : Performance Enhancements
160 -- this table of records will be used in recalc_data_elements to store
161 -- details corresponding of assignment IDs. Instead of calling parttime and FT
162 -- salary function multiple times, this data collection will be used
163 TYPE asg_recalc_details IS RECORD
164 ( assignment_id per_all_assignments_f.assignment_id%TYPE
165 , eff_start_date DATE
166 , eff_end_date DATE
167 , effective_status VARCHAR2(1)
168 , part_time_sal_paid NUMBER
169 , full_time_sal_rate NUMBER );
170 TYPE t_asg_recalc_details IS TABLE OF asg_recalc_details INDEX BY BINARY_INTEGER;
171
172 g_asg_recalc_details t_asg_recalc_details ;
173
174
175 -- this global will hold the events for a person (including secondary assignments)
176 -- it will be reset to null after the person has been processed by the ext criteria
177 g_asg_events t_asg_events_type;
178
179 -- 8iComp Changes: IMORTANT NOTE
180 -- Removing the following definition for Table Of Table datastructure
181 -- as Oracle 8i does not support this.
182 -- Later as Oracle 9i becomes the minimum pre requisite for Apps
183 -- we can move back to this logic
184 -- till then we will use a common table for keeping Leaver-restarter dates
185 -- for all the assignmets together.
186
187 -- The new solution is not as performant as the older one.
188
189 -- MULT-LR new type Added for storing all the leaevr/restarter events for a person
190 -- TYPE t_asg_leaver_events_table IS TABLE OF t_leaver_dates_type
191 -- INDEX BY BINARY_INTEGER;
192
193 -- MULT-LR new variable to store the events.
194 -- g_asg_leaver_events_table t_asg_leaver_events_table ;
195
196 -- 8iComp changes
197 -- This global will hold all the leaver restarter dates for all
198 -- the assignments for a person.
199 -- this will be used in place of g_asg_leaver_events_table
200 -- as this table of tables is not Oracle 8i compliant.
201 -- some time in the future we may revert back to the
202 -- Table of Table solution.
203 g_per_asg_leaver_dates t_leaver_dates_type;
204
205 -- Added the following global variable for extended criteria
206 g_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
207
208 -- this global will hold the set of leaver and restarter dates of the primary assignment.
209 -- PS : this global can only hold one primary asg at a time and is used inside multiple
210 -- service lines functionality, but it should not be refered to elsewhere
211 g_primary_leaver_dates t_leaver_dates_type;
212
213
214 -- this global will hold the set of leaver and restarter dates of the secondary assignment.
215 -- PS : this global can only hold one secodary asg at a time and is used inside multiple
216 -- service lines functionality, but it should not be refered to elsewhere
217 g_sec_leaver_dates t_leaver_dates_type;
218
219
220 --
221 -- Global Cursors
222 --
223
224 --
225 -- Effective assignment attributes
226 --
227 /* Uncomment if needed
228 CURSOR csr_pqp_asg_attributes -- effective
229 (p_assignment_id NUMBER
230 ,p_effective_date DATE DEFAULT NULL
231 ) IS
232 SELECT eaat.assignment_attribute_id assignment_attribute_id
233 ,eaat.assignment_id assignment_id
234 ,eaat.effective_start_date effective_start_date
235 ,eaat.effective_end_date effective_end_date
236 ,eaat.tp_is_teacher tp_is_teacher
237 ,eaat.tp_safeguarded_grade tp_safeguarded_grade
238 ,eaat.tp_safeguarded_rate_type tp_safeguarded_rate_type
239 ,eaat.tp_safeguarded_rate_id tp_safeguarded_rate_id
240 ,eaat.tp_safeguarded_spinal_point_id tp_safeguarded_spinal_point_id
241 ,eaat.tp_elected_pension tp_elected_pension
242 ,eaat.tp_fast_track tp_fast_track
243 ,eaat.creation_date creation_date
244 FROM pqp_assignment_attributes_f eaat -- effective aat
245 WHERE eaat.assignment_id = p_assignment_id
246 AND ( -- retrieve the effective row
247 (NVL(p_effective_date,g_effective_date)
248 BETWEEN eaat.effective_start_date
249 AND eaat.effective_end_date
250 )
251 )
252 ORDER BY eaat.effective_start_date; -- effective first
253 */
254 TYPE t_leaver_asgs_type IS TABLE OF per_all_assignments_f.assignment_id%TYPE INDEX BY BINARY_INTEGER;
255
256 --
257 -- Secondary Assignments which are Effective and future
258 --
259 CURSOR csr_sec_assignments
260 (p_primary_assignment_id NUMBER
261 ,p_person_id NUMBER
262 ,p_effective_date DATE
263 ) IS
264 SELECT DISTINCT asg.person_id person_id
265 ,asg.assignment_id assignment_id
266 ,asg.business_group_id business_group_id
267 ,DECODE(asg.business_group_id
268 ,g_business_group_id, 0
269 ,asg.business_group_id) bizgrpcol
270 FROM per_all_assignments_f asg, per_assignment_status_types pss
271 WHERE asg.person_id = p_person_id
272 AND asg.assignment_id <> p_primary_assignment_id
273 AND asg.assignment_type = 'E' --only employee assignments
274 AND pss.assignment_status_type_id = asg.assignment_status_type_id
275 AND pss.per_system_status NOT IN ('TERM_ASSIGN','SUSP_ASSIGN','END')
276 AND ((p_effective_date BETWEEN asg.effective_start_date
277 AND asg.effective_end_date
278 )
279 OR
280 ( -- Must have started on or after pension year start date
281 asg.effective_start_date >= p_effective_date
282 AND
283 -- must have started within the reporting period
284 asg.effective_start_date <= g_effective_run_date
285 )
286 )
287 UNION
288 SELECT DISTINCT per.person_id person_id
289 ,asg.assignment_id assignment_id
290 ,asg.business_group_id business_group_id
291 ,DECODE(asg.business_group_id
292 ,g_business_group_id, 0
293 ,asg.business_group_id) bizgrpcol
294 FROM per_all_people_f per, per_all_assignments_f asg
295 ,per_assignment_status_types pss
296 WHERE per.person_id <> p_person_id
297 AND ((p_effective_date BETWEEN per.effective_start_date
298 AND per.effective_end_date)
299 -- ENH3: Cross Person Reporting.
300 -- Person record may be starting in between a report period.
301 OR
302 ( -- Must have started on or after pension year start date
303 per.effective_start_date >= p_effective_date
304 AND
305 -- must have started within the reporting period
306 per.effective_start_date <= g_effective_run_date
307 )
308 )
309 AND g_cross_per_enabled = 'Y' -- Cross Person is enabled
310 AND (g_crossbg_enabled = 'Y' -- get CrossBG multiple per recs
311 OR
312 (g_crossbg_enabled = 'N' -- get multiple per recs only in this BG
313 AND
314 per.business_group_id = g_business_group_id
315 )
316 )
317 AND national_identifier IN
318 (SELECT national_identifier
319 FROM per_all_people_f per2
320 WHERE per2.person_id = p_person_id
321 AND ((p_effective_date BETWEEN per2.effective_start_date
322 AND per2.effective_end_date)
323 -- ENH3: Cross Person Reporting.
324 -- Person record may be starting in between a report period.
325 OR
326 ( -- Must have started on or after pension year start date
327 per2.effective_start_date >= p_effective_date
328 AND
329 -- must have started within the reporting period
330 per2.effective_start_date <= g_effective_run_date
331 )
332 )
333 )
334 AND asg.person_id = per.person_id
335 AND asg.assignment_type = 'E' --only employee assignments
336 AND pss.assignment_status_type_id = asg.assignment_status_type_id
337 AND pss.per_system_status NOT IN ('TERM_ASSIGN','SUSP_ASSIGN','END')
338 AND ((p_effective_date BETWEEN asg.effective_start_date
339 AND asg.effective_end_date
340 )
341 OR
342 ( -- Must have started on or after pension year start date
343 asg.effective_start_date >= p_effective_date
344 AND
345 -- must have started within the reporting period
346 asg.effective_start_date <= g_effective_run_date
347 )
348 )
349 ORDER BY bizgrpcol ASC, person_id;
350
351 -- Added a copy of the above cursor to pick up just the secondary assignments
352 -- that are effective
353 --
354 -- Secondary Assignments which are Effective
355 --
356 CURSOR csr_eff_sec_assignments
357 (p_primary_assignment_id NUMBER
358 ,p_person_id NUMBER
359 ,p_effective_date DATE
360 ) IS
361 SELECT DISTINCT asg.person_id person_id
362 ,asg.assignment_id assignment_id
363 ,asg.business_group_id business_group_id
364 ,DECODE(asg.business_group_id
365 ,g_business_group_id, 0
366 ,asg.business_group_id) bizgrpcol
367 FROM per_all_assignments_f asg
368 WHERE asg.person_id = p_person_id
369 AND asg.assignment_id <> p_primary_assignment_id
370 AND p_effective_date BETWEEN asg.effective_start_date
371 AND asg.effective_end_date
372 UNION
373 SELECT DISTINCT per.person_id person_id
374 ,asg.assignment_id assignment_id
375 ,asg.business_group_id business_group_id
376 ,DECODE(asg.business_group_id
377 ,g_business_group_id, 0
378 ,asg.business_group_id) bizgrpcol
379 FROM per_all_people_f per, per_all_assignments_f asg
380 WHERE per.person_id <> p_person_id
381 AND p_effective_date BETWEEN per.effective_start_date
382 AND per.effective_end_date
383 AND g_cross_per_enabled = 'Y' -- Cross Person is enabled
384 AND (g_crossbg_enabled = 'Y' -- get CrossBG multiple per recs
385 OR
386 (g_crossbg_enabled = 'N' -- get multiple per recs only in this BG
387 AND
388 per.business_group_id = g_business_group_id
389 )
390 )
391 AND national_identifier =
392 (SELECT national_identifier
393 FROM per_all_people_f per2
394 WHERE person_id = p_person_id
395 AND p_effective_date BETWEEN per2.effective_start_date
396 AND per2.effective_end_date
397 )
398 AND asg.person_id = per.person_id
399 AND p_effective_date BETWEEN asg.effective_start_date
400 AND asg.effective_end_date
401 ORDER BY bizgrpcol ASC, person_id;
402
403 /* SELECT DISTINCT asg.assignment_id assignment_id
404 -- ,asg.effective_start_date start_date
405 -- ,asg.effective_end_date effective_end_date
406 FROM per_all_assignments_f asg
407 WHERE asg.person_id = p_person_id
408 AND asg.assignment_id <> p_primary_assignment_id
409 AND ( ( nvl(p_effective_date,g_pension_year_start_date)
410 BETWEEN asg.effective_start_date
411 AND asg.effective_end_date )
412 OR
413 ( -- Must have started on or after pension year start date
414 asg.effective_start_date >= nvl(p_effective_date,g_pension_year_start_date)
415 AND
416 -- must have started within the reporting period
417 asg.effective_start_date <= g_effective_run_date
418 )
419 )
420 -- ORDER BY asg.effective_start_date ASC
421 ; -- effective first then future rows
422 */
423 TYPE t_sec_asgs_type IS TABLE OF csr_sec_assignments%ROWTYPE
424 INDEX BY BINARY_INTEGER;
425
426 -- Added for bugfix 3803760:FTSUPPLY
427 g_tab_sec_asgs t_sec_asgs_type;
428
429 --
430 -- Effective and future assignment details
431 --
432 -- Bugfix 3073562:GAP1:GAP2
433 -- 1 Added default null to p_effective_date
434 -- 2 Added business_group_id to select list
435 -- Bugfix 3073562:GAP6
436 -- 1 Added report_asg and secondary_assignment_id to select list
437 -- Bugfix 3641851:CBF1
438 -- Added teacher_start_date to select list
439 CURSOR csr_asg_details_up -- effective first then future rows
440 (p_assignment_id NUMBER
441 ,p_effective_date DATE DEFAULT NULL -- Effective Teaching Start Date
442 ) IS
443 SELECT asg.person_id person_id
444 ,asg.assignment_id assignment_id
445 ,asg.business_group_id business_group_id
446 ,asg.effective_start_date start_date
447 ,asg.effective_end_date effective_end_date
448 ,asg.creation_date creation_date
449 ,asg.location_id location_id
450 ,NVL(asg.employment_category,'FT') asg_emp_cat_cd
451 ,'F' ext_emp_cat_cd
452 ,'0000' estb_number
453 ,' ' tp_safeguarded_grade
454 ,asg.assignment_status_type_id status_type_id
455 ,' ' status_type
456 ,to_date('01/01/0001','dd/mm/yyyy') leaver_date
457 ,to_date('01/01/0001','dd/mm/yyyy') restarter_date
458 ,'Y' report_asg
459 ,asg.assignment_id secondary_assignment_id
460 ,asg.effective_start_date teacher_start_date
461 -- added for compatibility with tp4. csr_asg_details.
462 ,0 tp_sf_spinal_point_id
463 FROM per_all_assignments_f asg
464
465 WHERE asg.assignment_id = p_assignment_id
466 AND ( ( nvl(p_effective_date,g_pension_year_start_date)
467 BETWEEN asg.effective_start_date
468 AND asg.effective_end_date )
469 OR
470 ( asg.effective_start_date > nvl(p_effective_date,g_pension_year_start_date) )
471 )
472 ORDER BY asg.effective_start_date ASC; -- effective first then future rows
473
474 --
475 -- Effective and history of assignment details
476 --
477 -- Bugfix 3073562:GAP1:GAP2
478 -- 1 Added default null to p_effective_date
479 -- 2 Added business_group_id to select list
480 -- Bugfix 3073562:GAP6
481 -- 1 Added report_asg and secondary_assignment_id to select list
482 -- Bugfix 3641851:CBF1
483 -- Added teacher_start_date to select list
484 CURSOR csr_asg_details_dn -- effective first then history rows
485 (p_assignment_id NUMBER
486 ,p_effective_date DATE
487 ) IS
488 SELECT asg.person_id person_id
489 ,asg.assignment_id assignment_id
490 ,asg.business_group_id business_group_id
491 ,asg.effective_start_date start_date
492 ,asg.effective_end_date effective_end_date
493 ,asg.creation_date creation_date
494 ,asg.location_id location_id
495 ,NVL(asg.employment_category,'FT') asg_emp_cat_cd
496 ,'F' ext_emp_cat_cd
497 ,'0000' estb_number
498 ,' ' tp_safeguarded_grade
499 ,asg.assignment_status_type_id status_type_id
500 ,' ' status_type
501 ,to_date('01/01/0001','dd/mm/yyyy') leaver_date
502 ,to_date('01/01/0001','dd/mm/yyyy') restarter_date
503 ,'Y' report_asg
504 ,asg.assignment_id secondary_assignment_id
505 ,asg.effective_start_date teacher_start_date
506 -- added for compatibility with tp4. csrasg_details.
507 ,0 tp_sf_spinal_point_id
508 FROM per_all_assignments_f asg
509 WHERE asg.assignment_id = p_assignment_id
510 AND ( ( nvl(p_effective_date,g_pension_year_start_date)
511 BETWEEN asg.effective_start_date
512 AND asg.effective_end_date )
513 OR
514 ( asg.effective_end_date < nvl(p_effective_date,g_effective_run_date) )
515 )
516 ORDER BY asg.effective_start_date DESC; -- effective first then history rows
517
518 TYPE t_ext_asg_details_type IS TABLE OF csr_asg_details_dn%ROWTYPE
519 INDEX BY BINARY_INTEGER;
520
521 g_ext_asg_details t_ext_asg_details_type;
522
523
524
525 --
526 -- csr_pqp_asg_attributes_up
527 --
528 -- Bugfix 2551059, added column tp_safeguarded_grade_id
529 --
530 CURSOR csr_pqp_asg_attributes_up -- up
531 (p_assignment_id NUMBER
532 ,p_effective_date DATE DEFAULT NULL
533 ) IS
534 SELECT eaat.assignment_attribute_id assignment_attribute_id
535 ,eaat.assignment_id assignment_id
536 ,eaat.effective_start_date effective_start_date
537 ,eaat.effective_end_date effective_end_date
538 ,eaat.tp_is_teacher tp_is_teacher
539 ,eaat.tp_safeguarded_grade tp_safeguarded_grade
540 ,eaat.tp_safeguarded_grade_id tp_safeguarded_grade_id
541 ,eaat.tp_safeguarded_rate_type tp_safeguarded_rate_type
542 ,eaat.tp_safeguarded_rate_id tp_safeguarded_rate_id
543 ,eaat.tp_safeguarded_spinal_point_id tp_safeguarded_spinal_point_id
544 ,eaat.tp_elected_pension tp_elected_pension
545 ,eaat.tp_fast_track tp_fast_track
546 ,eaat.creation_date creation_date
547 FROM pqp_assignment_attributes_f eaat -- effective aat
548 WHERE eaat.assignment_id = p_assignment_id
549 AND ( -- retrieve the effective row
550 (NVL(p_effective_date,g_pension_year_start_date)
551 BETWEEN eaat.effective_start_date
552 AND eaat.effective_end_date
553 )
554 OR -- any future rows
555 (eaat.effective_start_date > NVL(p_effective_date,g_effective_date)
556 )
557 )
558 ORDER BY eaat.effective_start_date ASC; -- effective first
559
560 TYPE t_ext_asg_attributes_type IS TABLE OF csr_pqp_asg_attributes_up%ROWTYPE
561 INDEX BY BINARY_INTEGER;
562
563 g_ext_asg_attributes t_ext_asg_attributes_type;
564
565 --
566 Type t_udt_element_rec Is Record
567 ( allowance_code varchar2(1)
568 ,element_name varchar2(80)
569 ,input_value_name varchar2(30)
570 );
571 --
572 Type t_udt_tab Is Table of t_udt_element_rec Index by binary_integer;
573 --
574 g_udt_element_LondAll t_udt_tab;
575 g_udt_element_SpcAll t_udt_tab;
576 --
577 -- added for 5743209
578 TYPE r_allowance_eles IS RECORD
579 (element_type_id NUMBER
580 ,salary_scale_code VARCHAR2(1)
581 ,element_type_extra_info_id NUMBER -- RET : added for changes in
582 -- fetch_allow_eles_frm_udt for
583 -- retention allowance rate calculations
584 );
585
586 TYPE t_allowance_eles IS TABLE OF r_allowance_eles
587 INDEX BY BINARY_INTEGER;
588
589 g_tab_lon_aln_eles t_allowance_eles;
590 g_tab_spl_aln_eles t_allowance_eles;
591
592 g_spl_all_grd_src varchar2(1);
593 g_lon_all_grd_src varchar2(1);
594
595
596 Type t_udt_rec Is record (
597 column_name pay_user_columns.user_column_name%TYPE,
598 row_name pay_user_rows_f.row_low_range_or_name%TYPE,
599 matrix_value pay_user_column_instances_f.value%TYPE,
600 start_date date,
601 end_date date);
602 --
603
604 Type t_udt_array is table of t_udt_rec Index by Binary_Integer;
605 g_udt_rec t_udt_array;
606
607 --
608 -- c_multiper Gets multi persons records for the same NI no.
609 --
610 CURSOR c_multiper
611 (p_person_id NUMBER
612 ,p_effective_start_date DATE
613 ,p_effective_end_date DATE
614 ,p_assignment_id NUMBER DEFAULT NULL
615 ) IS
616 SELECT per.person_id
617 ,per.national_identifier
618 ,per.business_group_id
619 ,asg.assignment_id assignment_id
620 ,NVL(asg.employment_category,'FT') asg_emp_cat_cd
621 FROM per_all_people_f per, per_all_assignments_f asg
622 WHERE per.person_id = p_person_id
623 AND asg.assignment_id <> p_assignment_id
624 AND asg.assignment_type ='E'
625 --AND p_effective_date BETWEEN per.effective_start_date
626 -- AND per.effective_end_date
627 AND (
628 (per.effective_start_date BETWEEN p_effective_start_date
629 AND p_effective_end_date
630 ) OR
631 ( p_effective_start_date BETWEEN per.effective_start_date
632 AND per.effective_end_date
633 )
634 )
635 AND (g_crossbg_enabled = 'Y' -- get CrossBG multiple per recs
636 OR
637 (g_crossbg_enabled = 'N' -- get multiple per recs only in this BG
638 AND
639 per.business_group_id = g_business_group_id
640 )
641 )
642 AND asg.person_id = per.person_id
643 --AND p_effective_date BETWEEN asg.effective_start_date
644 -- AND asg.effective_end_date
645 AND (
646 (asg.effective_start_date BETWEEN p_effective_start_date
647 AND p_effective_end_date
648 ) OR
649 ( p_effective_start_date BETWEEN asg.effective_start_date
650 AND asg.effective_end_date
651 )
652 )
653 UNION
654 SELECT per.person_id
655 ,per.national_identifier
656 ,per.business_group_id
657 ,asg.assignment_id assignment_id
658 ,NVL(asg.employment_category,'FT') asg_emp_cat_cd
659 FROM per_all_people_f per, per_all_assignments_f asg
660 WHERE per.person_id <> p_person_id
661 -- AND p_effective_date BETWEEN per.effective_start_date
662 -- AND per.effective_end_date
663 AND (
664 (per.effective_start_date BETWEEN p_effective_start_date
665 AND p_effective_end_date
666 ) OR
667 ( p_effective_start_date BETWEEN per.effective_start_date
668 AND per.effective_end_date
669 )
670 )
671 AND (g_crossbg_enabled = 'Y' -- get CrossBG multiple per recs
672 OR
673 (g_crossbg_enabled = 'N' -- get multiple per recs only in this BG
674 AND
675 per.business_group_id = g_business_group_id
676 )
677 )
678 AND national_identifier IN -- changed from = to IN as the query
679 -- return multiple records, in case there are
680 -- date track updates on person record.
681 (SELECT national_identifier
682 FROM per_all_people_f per2
683 WHERE person_id = p_person_id
684 --AND p_effective_date BETWEEN per2.effective_start_date
685 -- AND per2.effective_end_date
686 AND (
687 (per2.effective_start_date BETWEEN p_effective_start_date
688 AND p_effective_end_date
689 ) OR
690 ( p_effective_start_date BETWEEN per2.effective_start_date
691 AND per2.effective_end_date
692 )
693 )
694 )
695 AND asg.person_id = per.person_id
696 AND asg.assignment_type ='E'
697 -- AND p_effective_date BETWEEN asg.effective_start_date
698 -- AND asg.effective_end_date ;
699 AND (
700 (asg.effective_start_date BETWEEN p_effective_start_date
701 AND p_effective_end_date
702 ) OR
703 ( p_effective_start_date BETWEEN asg.effective_start_date
704 AND asg.effective_end_date
705 )
706 ) ;
707
708 TYPE typ_multiper IS TABLE OF c_multiper%ROWTYPE
709 INDEX BY BINARY_INTEGER;
710
711
712
713
714 --
715 -- Global values
716 Type t_number is table of number
717 index by binary_integer;
718
719 Type t_varchar is table of varchar2(2000)
720 index by binary_integer;
721
722 g_other_allowance t_number;
723 g_annual_rate t_number;
724 g_abs_bal_type_id t_number;
725 g_sal_bal_type_id t_number;
726 g_cl_bal_type_id t_number;
727 -- 4336613 : OSLA_3A : OSLA balance type id
728 g_osla_bal_type_id t_number;
729 g_osla_cl_bal_type_id t_number;
730 -- 4336613 : changed to a table of numbers, indexed by balance type ids
731 g_def_bal_id t_number;
732
733 -- Cursor to retrieve rate_id
734
735 cursor csr_ele_rate_id (c_rate_name varchar2
736 ,c_rate_type varchar2) is
737 select rate_id
738 from pay_rates
739 where upper(name) = upper(c_rate_name)
740 and rate_type = decode(c_rate_type,'GR','G',c_rate_type);
741
742 -- Cursor to retrieve rate_id from pqp_assignment_attributes_f
743
744 cursor csr_paa_rate_id (c_assignment_id number
745 ,c_effective_date date) is
746 select tp_safeguarded_grade
747 ,tp_safeguarded_rate_id
748 -- added safeguarded rate type column for new logic for
749 -- calculating the safeguarded salary scale check.
750 ,tp_safeguarded_rate_type
751 ,assignment_attribute_id
752 from pqp_assignment_attributes_f
753 where assignment_id = c_assignment_id
754 and c_effective_date between effective_start_date
755 and effective_end_date;
756
757 -- Cursor to retrieve grade rate value
758
759 -- Bugfix : 2551059, Date : 20/09/2002
760 -- Changed cursor to join with tp_safeguarded_grade_id
761 -- Also, effectiveness check on paa was missing, added it.
762 cursor csr_grade_rate (c_attribute_id number
763 ,c_effective_date date) is
764 select to_number(pgr.value)
765 from pqp_assignment_attributes_f paa
766 ,pay_grade_rules_f pgr
767 where paa.assignment_attribute_id = c_attribute_id
768 and pgr.grade_or_spinal_point_id = paa.tp_safeguarded_grade_id
769 and pgr.rate_id = paa.tp_safeguarded_rate_id
770 and c_effective_date between pgr.effective_start_date
771 and pgr.effective_end_date
772 and c_effective_date between paa.effective_start_date
773 and paa.effective_end_date;
774
775 -- Cursor to retrieve scale rate value
776
777 -- Idendified prob during Bugfix : 2551059, Date : 20/09/2002
778 -- Changed cursor, added effectiveness check on paa, it was missing
779 cursor csr_scale_rate (c_attribute_id number
780 ,c_effective_date date) is
781 select to_number(pgr.value)
782 from pqp_assignment_attributes_f paa
783 ,pay_grade_rules_f pgr
784 where paa.assignment_attribute_id = c_attribute_id
785 and pgr.rate_id = paa.tp_safeguarded_rate_id
786 and pgr.grade_or_spinal_point_id = paa.tp_safeguarded_spinal_point_id
787 and c_effective_date between pgr.effective_start_date
788 and pgr.effective_end_date
789 and c_effective_date between paa.effective_start_date
790 and paa.effective_end_date;
791
792 -- Cursor to retrieve element attribution info
793
794 cursor csr_element_set (c_name varchar2
795 ,c_eff_date DATE
796 ,c_business_group_id NUMBER DEFAULT NULL
797 ) is
798 select eei2.element_type_extra_info_id
799 ,eei1.element_type_id
800 from pay_element_type_extra_info eei1
801 ,pay_element_type_extra_info eei2
802 ,hr_lookups hrl
803 ,pay_element_types_f petf
804 where hrl.lookup_type = 'PQP_RATE_TYPE'
805 and hrl.meaning = c_name
806 and eei1.eei_information1 = hrl.lookup_code
807 and eei1.information_type = 'PQP_UK_RATE_TYPE'
808 and eei1.element_type_id = eei2.element_type_id
809 and eei2.information_type = 'PQP_UK_ELEMENT_ATTRIBUTION'
810 and petf.element_type_id = eei1.element_type_id
811 and c_eff_date BETWEEN petf.effective_start_date
812 AND petf.effective_end_date
813 and (
814 (petf.business_group_id IS NOT NULL
815 AND
816 petf.business_group_id = nvl(c_business_group_id, g_business_group_id)
817 )
818 OR
819 (petf.business_group_id IS NULL
820 AND
821 petf.legislation_code = g_legislation_code
822 )
823 OR
824 (petf.business_group_id IS NULL
825 AND
826 petf.legislation_code IS NULL
827 )
828 );
829
830 -- Cursor to retrieve end_dates from per_time_periods
831
832 Cursor csr_get_end_date
833 (c_assignment_id number
834 ,c_effective_start_date date
835 ,c_effective_end_date date) is
836 select distinct(ptp.end_date) end_date
837 from per_time_periods ptp
838 ,pay_payroll_actions ppa
839 ,pay_assignment_actions paa
840 where ptp.time_period_id = ppa.time_period_id
841 and ppa.payroll_action_id = paa.payroll_action_id
842 and ppa.effective_date between c_effective_start_date
843 and c_effective_end_date
844 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
845 and paa.assignment_id = c_assignment_id
846 order by ptp.end_date;
847
848 Cursor csr_get_pre_end_date
849 (c_assignment_id number
850 ,c_effective_start_date date
851 ,c_effective_end_date date) is
852 select distinct(ptp.end_date) end_date
853 from per_time_periods ptp
854 ,pay_payroll_actions ppa
855 ,pay_assignment_actions paa
856 where ptp.time_period_id = ppa.time_period_id
857 and ppa.payroll_action_id = paa.payroll_action_id
858 and ppa.effective_date between c_effective_start_date
859 and c_effective_end_date
860 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
861 and paa.assignment_id = c_assignment_id
862 and c_effective_start_date between ptp.start_date and ptp.end_date
863 order by ptp.end_date;
864
865 -- Cursor to get balance type id for a balance
866
867 Cursor csr_get_pay_bal_id
868 (c_balance_name varchar2
869 ,c_business_group_id number
870 ) is
871 select balance_type_id, legislation_code -- 4336613 : added leg_code
872 from pay_balance_types
873 where balance_name = c_balance_name
874 and (
875 (business_group_id IS NOT NULL AND
876 business_group_id = NVL(c_business_group_id, g_business_group_id)
877 )
878 OR
879 (business_group_id IS NULL AND
880 legislation_code = g_legislation_code
881 )
882 OR
883 (business_group_id IS NULL AND
884 legislation_code IS NULL
885 )
886 );
887
888 -- Cursor to get element type ids from balance
889
890 Cursor csr_get_pay_ele_ids_from_bal
891 (c_balance_type_id number
892 ,c_effective_date date
893 ,c_business_group_id number
894 ) is
895 select pet.element_type_id element_type_id
896 ,piv.input_value_id input_value_id --Vibhor : PTS
897 from pay_element_types_f pet
898 ,pay_input_values_f piv
899 ,pay_balance_feeds_f pbf
900 where pet.element_type_id = piv.element_type_id
901 and pet.business_group_id = NVL(c_business_group_id, g_business_group_id)
902 and piv.input_value_id = pbf.input_value_id
903 and pbf.balance_type_id = c_balance_type_id
904 and ((c_effective_date between pbf.effective_start_date
905 and pbf.effective_end_date)
906 or
907 c_effective_date <= pbf.effective_end_date
908 );
909
910 type t_ele_ids_from_bal is table of csr_get_pay_ele_ids_from_bal%rowtype
911 index by binary_integer;
912
913 g_tab_abs_ele_ids t_ele_ids_from_bal;
914 -- Bug 3015917 : Adding this as we need to cache PET Ids for Sal Balance
915 g_tab_sal_ele_ids t_ele_ids_from_bal;
916
917 --4336613 : OSLA_3A : added for OSLA information
918 g_tab_osla_ele_ids t_ele_ids_from_bal;
919
920 g_tab_cl_ele_ids t_ele_ids_from_bal;
921
922 g_tab_osla_cl_ele_ids t_ele_ids_from_bal;
923 -- Cursor to get element entries information
924
925 -- As element type id is available as a new column in
926 -- pay_element_entries_f table from HR_FP G onwards
927 -- we can fetch element type id from this table directly
928 -- so modified this cursor as a part of Bug fix 3163458
929
930 Cursor csr_get_eet_info
931 (c_assignment_id number
932 ,c_effective_start_date date
933 ,c_effective_end_date date
934 ) is
935 select pel.element_type_id
936 ,pee.element_entry_id
937 from pay_element_entries_f pee
938 ,pay_element_links_f pel
939 where pee.assignment_id = c_assignment_id
940 and (pee.effective_start_date between c_effective_start_date
941 and c_effective_end_date
942 or
943 pee.effective_end_date between c_effective_start_date
944 and c_effective_end_date
945 or
946 c_effective_start_date between pee.effective_start_date
947 and pee.effective_end_date
948 or
949 c_effective_end_date between pee.effective_start_date
950 and pee.effective_end_date
951 )
952 and pel.element_link_id = pee.element_link_id
953 order by pee.effective_start_date;
954
955
956 -- Cursor to retrieve input value id
957
958 Cursor csr_get_iv_info
959 (c_element_type_id number
960 ,c_input_value_name varchar2
961 ) is
962 select input_value_id
963 from pay_input_values_f
964 where element_type_id = c_element_type_id
965 and name = c_input_value_name;
966
967 -- Cursor to get value from element entry values table
968
969 Cursor csr_get_eev_info
970 (c_element_entry_id number
971 ,c_input_value_id number
972 ,c_effective_start_date date
973 ,c_effective_end_date date
974 ) is
975 select screen_entry_value
976 ,effective_start_date
977 ,effective_end_date
978 from pay_element_entry_values_f pee
979 where pee.element_entry_id = c_element_entry_id
980 and pee.input_value_id = c_input_value_id
981 and (pee.effective_start_date between c_effective_start_date
982 and c_effective_end_date
983 or
984 pee.effective_end_date between c_effective_start_date
985 and c_effective_end_date
986 or
987 c_effective_start_date between pee.effective_start_date
988 and pee.effective_end_date
989 or
990 c_effective_end_date between pee.effective_start_date
991 and pee.effective_end_date
992 )
993 order by pee.effective_start_date;
994
995 -- Cursor to get eev info for date
996
997 Cursor csr_get_eev_info_date
998 (c_element_entry_id number
999 ,c_input_value_id number
1000 ,c_effective_start_date date
1001 ,c_effective_end_date date
1002 ) is
1003 select screen_entry_value
1004 ,effective_start_date
1005 ,effective_end_date
1006 from pay_element_entry_values_f pee
1007 where pee.element_entry_id = c_element_entry_id
1008 and pee.input_value_id = c_input_value_id
1009 and pee.effective_start_date = c_effective_start_date
1010 and pee.effective_end_date = c_effective_end_date;
1011
1012 --
1013 -- csr_element_entries
1014 --
1015
1016 CURSOR csr_element_entries(p_assignment_id IN NUMBER
1017 ,p_effective_date IN DATE
1018 ,p_element_type_id IN NUMBER ) IS
1019 SELECT pee.element_entry_id
1020 FROM pay_element_entries_f pee
1021 ,pay_element_links_f pel
1022 WHERE pee.assignment_id = p_assignment_id
1023 AND pel.element_link_id = pee.element_link_id
1024 AND pel.element_type_id = p_element_type_id
1025 AND ((p_effective_date BETWEEN pee.effective_start_date
1026 AND pee.effective_end_date
1027 )
1028 OR
1029 (pee.effective_start_date BETWEEN p_effective_date
1030 AND g_effective_run_date
1031 )
1032 )
1033 AND ((p_effective_date BETWEEN pel.effective_start_date
1034 AND pel.effective_end_date
1035
1036 )
1037 OR
1038 (pel.effective_start_date BETWEEN p_effective_date
1039 AND g_effective_run_date
1040 )
1041 );
1042
1043 -- Cursor to fetch the record if of the details record, but not the hidden one
1044 -- WARNING : This works only if there is one displayed detail record.
1045 -- Do we need to raise an error if there are 2 diplayed detail records??
1046 -- If yes, then Fetch ... , check .. and raise error
1047 -- Alternatively, modify the cursor to return the required id by querying on name.
1048 CURSOR csr_ext_rcd_id(p_hide_flag IN VARCHAR2
1049 ,p_rcd_type_cd IN VARCHAR2
1050 ) IS
1051 SELECT rcd.ext_rcd_id
1052 FROM ben_ext_rcd rcd
1053 ,ben_ext_rcd_in_file RinF
1054 ,ben_ext_dfn dfn
1055 WHERE dfn.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
1056 AND RinF.ext_file_id = dfn.ext_file_id
1057 AND RinF.hide_flag = p_hide_flag
1058 AND RinF.ext_rcd_id = rcd.ext_rcd_id
1059 AND rcd.rcd_type_cd = p_rcd_type_cd;
1060
1061 -- Cursor to fetch the details record results
1062 CURSOR csr_rslt_dtl(p_person_id IN NUMBER
1063 ,p_ext_rslt_id IN NUMBER
1064 ) IS
1065 SELECT *
1066 FROM ben_ext_rslt_dtl dtl
1067 WHERE dtl.ext_rslt_id = p_ext_rslt_id
1068 AND dtl.person_id = p_person_id
1069 AND dtl.ext_rcd_id = g_ext_dtl_rcd_id;
1070
1071 -- Cursor to check the new line of service.
1072 CURSOR csr_chk_los_change(p_prev_new_rec IN csr_rslt_dtl%ROWTYPE
1073 ,p_new_rec IN csr_rslt_dtl%ROWTYPE
1074 ) IS
1075 SELECT 0
1076 FROM dual
1077 WHERE (p_prev_new_rec.val_10 = p_new_rec.val_10 OR (p_prev_new_rec.val_10 is null and p_new_rec.val_10 is null))
1078 AND (p_prev_new_rec.val_11 = p_new_rec.val_11 OR (p_prev_new_rec.val_11 is null and p_new_rec.val_11 is null))
1079 AND (p_prev_new_rec.val_12 = p_new_rec.val_12 OR (p_prev_new_rec.val_12 is null and p_new_rec.val_12 is null))
1080 AND (p_prev_new_rec.val_15 = p_new_rec.val_15 OR (p_prev_new_rec.val_15 is null and p_new_rec.val_15 is null))
1081 AND (p_prev_new_rec.val_17 = p_new_rec.val_17 OR (p_prev_new_rec.val_17 is null and p_new_rec.val_17 is null))
1082 AND (p_prev_new_rec.val_20 = p_new_rec.val_20 OR (p_prev_new_rec.val_20 is null and p_new_rec.val_20 is null))
1083 AND (p_prev_new_rec.val_21 = p_new_rec.val_21 OR (p_prev_new_rec.val_21 is null and p_new_rec.val_21 is null))
1084 AND (p_prev_new_rec.val_22 = p_new_rec.val_22 OR (p_prev_new_rec.val_22 is null and p_new_rec.val_22 is null))
1085 AND (p_prev_new_rec.val_23 = p_new_rec.val_23 OR (p_prev_new_rec.val_23 is null and p_new_rec.val_23 is null))
1086 AND (p_prev_new_rec.val_24 = p_new_rec.val_24 OR (p_prev_new_rec.val_24 is null and p_new_rec.val_24 is null))
1087 AND (p_prev_new_rec.val_27 = p_new_rec.val_27 OR (p_prev_new_rec.val_27 is null and p_new_rec.val_27 is null));
1088
1089 -- This cursor returns multiple person data and master BG data
1090 -- for cross BG reporting
1091 -- If p_record_type :
1092 -- a) M - Master Bg Id
1093 -- b) X - Cross BG reporting National Identifier Data
1094 CURSOR csr_multiproc_data(p_record_type VARCHAR2
1095 ,p_national_identifier VARCHAR2 DEFAULT NULL
1096 -- Bugfix 3671727:ENH1:ENH2 Added p_lea_number and
1097 -- p_ext_dfn_id param
1098 ,p_lea_number VARCHAR2 DEFAULT NULL
1099 ,p_ext_dfn_id NUMBER DEFAULT NULL
1100 ) IS
1101 SELECT *
1102 FROM pqp_ext_cross_person_records emd
1103 WHERE emd.record_type = p_record_type
1104 AND (p_national_identifier IS NULL
1105 OR
1106 (p_national_identifier IS NOT NULL
1107 AND
1108 emd.national_identifier = p_national_identifier
1109 )
1110 )
1111 AND emd.ext_dfn_id = nvl(p_ext_dfn_id, ben_ext_thread.g_ext_dfn_id) --ENH3
1112 AND emd.lea_number = nvl(p_lea_number, g_lea_number); --ENH3
1113
1114
1115 -- This cursor returns all BGs which have the p_lea_number
1116 /*CURSOR csr_all_business_groups(p_lea_number IN VARCHAR2
1117 ,p_business_group_id IN NUMBER DEFAULT NULL
1118 ) IS
1119 SELECT hoi1.organization_id business_group_id -- this is not BG id...
1120 ,hoi1.org_information1 lea_number
1121 ,hoi1.org_information2 lea_name
1122 ,nvl(hoi1.org_information3,'N') CrossBG_Enabled
1123 ,0 Request_Id
1124 ,' ' Status
1125 FROM hr_organization_information hoi1
1126 WHERE hoi1.organization_id <> nvl(p_business_group_id, g_business_group_id)
1127 AND hoi1.org_information_context = 'PQP_GB_EDU_AUTH_LEA_INFO'
1128 AND hoi1.org_information1 = p_lea_number
1129 AND nvl(hoi1.org_information3,'N') = 'Y' -- Enabled for CrossBG reporting
1130 AND EXISTS
1131 (SELECT 1
1132 FROM hr_organization_information hoi2
1133 WHERE hoi2.org_information_context='CLASS'
1134 AND hoi2.organization_id = hoi1.organization_id
1135 AND hoi2.org_information1 = 'HR_BG' -- is a BG
1136 AND hoi2.org_information2 = 'Y' -- Enabled
1137 );*/
1138
1139 -- This cursor returns all BGs which have the p_lea_number
1140 -- ENH1 : Multiple LEAs with in a BG.
1141 -- Changed as now the education authority can be at
1142 -- org level, and need not be an HR_BG.
1143 CURSOR csr_all_business_groups(p_lea_number IN VARCHAR2
1144 ,p_business_group_id IN NUMBER DEFAULT NULL
1145 ) IS
1146 -- removing the lea numebr and lea name from the select clause
1147 -- as these are not used any where in the code and
1148 -- and we need a distinct list of BG's...
1149 SELECT DISTINCT hou.business_group_id business_group_id
1150 -- ,hoi1.org_information1 lea_number
1151 -- ,hoi1.org_information2 lea_name
1152 ,nvl(hoi1.org_information3,'N') CrossBG_Enabled
1153 ,0 Request_Id --used in ext process
1154 ,' ' Status --used in ext process
1155 FROM hr_organization_information hoi1
1156 ,hr_organization_units hou --added this to join org's with the respective BG.
1157 WHERE hoi1.organization_id = hou.organization_id
1158 AND hou.business_group_id <> nvl(p_business_group_id, g_business_group_id)
1159 AND hoi1.org_information_context = 'PQP_GB_EDU_AUTH_LEA_INFO'
1160 AND hoi1.org_information1 = p_lea_number
1161 AND nvl(hoi1.org_information3,'N')= 'Y'; -- Enabled for CrossBG reporting
1162
1163 TYPE t_all_bgs_type IS TABLE OF csr_all_business_groups%ROWTYPE
1164 INDEX BY BINARY_INTEGER;
1165
1166 g_lea_business_groups t_all_bgs_type;
1167
1168
1169 Cursor Get_Matrix_Value ( c_user_table_name in varchar
1170 ,c_user_column_name in varchar
1171 ,c_user_row_name in varchar
1172 ,c_effective_date in date
1173 ,c_business_group_id in number DEFAULT NULL
1174 ) Is
1175 select put.user_table_name
1176 ,puc.user_column_name
1177 ,pur.row_low_range_or_name
1178 ,pci.value
1179 ,pci.user_column_instance_id
1180 from pay_user_tables put
1181 ,pay_user_columns puc
1182 ,pay_user_rows_f pur
1183 ,pay_user_column_instances_f pci
1184 where put.user_table_name = c_user_table_name
1185 and puc.user_table_id = put.user_table_id
1186 and puc.user_column_name = c_user_column_name
1187 and pur.row_low_range_or_name = c_user_row_name
1188 and pur.user_table_id = put.user_table_id
1189 and pci.user_column_id = puc.user_column_id
1190 and pci.user_row_id = pur.user_row_id
1191 and Trunc(c_effective_date) between pur.effective_start_date
1192 and pur.effective_end_date
1193 and Trunc(c_effective_date) between pci.effective_start_date
1194 and pci.effective_end_date
1195 and ((pci.business_group_id is null and pci.legislation_code is null)
1196 or (pci.legislation_code is not null
1197 and pci.legislation_code = 'GB')
1198 or (pci.business_group_id is not null
1199 and pci.business_group_id = NVL(c_business_group_id, g_business_group_id))
1200 )
1201 order by put.user_table_name, puc.user_column_name, pur.display_sequence;
1202
1203 --
1204 -- Cursor to get details for a given request id
1205 --
1206 CURSOR csr_request_dets(p_request_id IN NUMBER DEFAULT NULL) IS
1207 SELECT req.parent_request_id
1208 ,req.concurrent_program_id
1209 ,con.concurrent_program_name
1210 FROM fnd_concurrent_requests req, fnd_concurrent_programs con
1211 WHERE request_id = nvl(p_request_id, fnd_global.conc_request_id)
1212 AND con.concurrent_program_id = req.concurrent_program_id;
1213
1214 -- PTS: BUG 4135481: Added for Part Time salary Paid changes
1215 -- The cursor gets the assignment action id
1216 -- for the assignment for the Date Earned
1217 CURSOR csr_get_asg_act_id
1218 ( p_assignment_id NUMBER
1219 ,p_date_earned DATE
1220 )
1221 IS
1222 SELECT fnd_number.canonical_to_number(substr(min(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
1223 --paa.assignment_action_id
1224 FROM pay_assignment_actions paa
1225 ,pay_payroll_actions ppa
1226 WHERE paa.assignment_id = p_assignment_id
1227 AND ppa.action_status = 'C'
1228 AND paa.action_status = 'C'
1229 AND paa.payroll_action_id = ppa.payroll_action_id
1230 AND ppa.date_earned = p_date_earned
1231 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
1232 AND (paa.source_action_id IS NOT NULL OR ppa.action_type in ('B','I'));
1233 -- AND paa.source_action_id IS NULL ;
1234 -- Following are the codes - meanings
1235 -- B-Balance adjustment, I-Balance Initialization,
1236 -- Q-QuickPay Run, R-Run, V-Reversal
1237
1238
1239
1240 -- PTS: BUG 4135481: Added for Part Time salary Paid changes
1241 -- Get run Result values for the
1242 -- prorated payments for the assignment
1243 CURSOR csr_get_run_result_value
1244 ( p_start_date DATE
1245 ,p_end_date DATE
1246 ,p_element_type_id NUMBER
1247 ,p_input_value_id NUMBER
1248 ,p_asg_act_id NUMBER
1249 )
1250 IS
1251 SELECT to_number(prrv.result_value) result
1252 ,prr.start_date
1253 ,prr.end_date
1254 FROM pay_run_result_values prrv
1255 ,pay_run_results prr
1256 WHERE prrv.run_result_id = prr.run_result_id
1257 AND prr.assignment_action_id = p_asg_act_id
1258 AND prr.start_date >= p_start_date
1259 AND prr.end_date <= p_end_date
1260 AND prr.element_type_id = p_element_type_id
1261 AND prrv.input_value_id = p_input_value_id ;
1262
1263
1264 -- Get run Result values for the
1265 -- payments for the assignment
1266 CURSOR csr_get_run_results
1267 ( p_start_date DATE
1268 ,p_end_date DATE
1269 ,p_asg_act_id NUMBER
1270 ,p_balance_type_id NUMBER
1271 )
1272 IS
1273 SELECT fnd_number.canonical_to_number(TARGET.result_value) result
1274 ,nvl(RR.start_date,p_start_date) start_date
1275 ,nvl(RR.end_date,p_end_date) end_date
1276 ,FEED.scale scale
1277 ,RR.run_result_id
1278 FROM pay_assignment_actions BAL_ASSACT
1279 ,pay_payroll_actions BACT
1280 ,pay_assignment_actions ASSACT
1281 ,pay_payroll_actions PACT
1282 ,pay_run_results RR
1283 ,pay_run_result_values TARGET
1284 ,pay_balance_feeds_f FEED
1285 ,per_time_periods PTP
1286 WHERE BAL_ASSACT.assignment_action_id = p_asg_act_id
1287 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
1288 and FEED.balance_type_id = p_balance_type_id + DECODE(TARGET.input_value_id,null,0,0) -- 10510987
1289 and FEED.input_value_id = TARGET.input_value_id
1290 and nvl(TARGET.result_value,'0') <> '0'
1291 and TARGET.run_result_id = RR.run_result_id
1292 and RR.assignment_action_id = ASSACT.assignment_action_id
1293 and ASSACT.payroll_action_id = PACT.payroll_action_id
1294 and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date
1295 and PACT.action_type <> 'V'
1296 and RR.status in ('P','PA')
1297 and ASSACT.action_sequence >= BAL_ASSACT.action_sequence
1298 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
1299 and PACT.time_period_id = ptp.time_period_id
1300 and nvl(RR.start_date,ptp.start_date) >= p_start_date
1301 and nvl(RR.end_date,ptp.end_date) <= p_end_date
1302 and FEED.effective_start_date < p_end_date
1303 and p_start_date < FEED.effective_end_date;
1304
1305 -- PTS:BUG 4135481: Added for Part Time salary Paid changes
1306 -- get the previous payroll period payroll perios date to
1307 -- get the starting point of the next payroll period.
1308
1309 CURSOR csr_get_previous_payroll_date
1310 (p_assignment_id NUMBER
1311 ,p_effective_start_date DATE
1312 )
1313 IS
1314 SELECT max(ptp.end_date) previous_payroll_date
1315 FROM per_time_periods ptp
1316 ,per_all_assignments_f paaf
1317 WHERE ptp.payroll_id = paaf.payroll_id
1318 AND paaf.assignment_id = p_assignment_id
1319 AND ptp.end_date <= p_effective_start_date ;
1320
1321
1322 -- PTS: BUG 4135481: Added for Part Time salary Paid changes
1323 -- get the next payroll period payroll date to
1324 -- get the date_earned.
1325 CURSOR csr_get_next_payroll_date
1326 (p_assignment_id NUMBER
1327 ,p_effective_start_date DATE
1328 )
1329 IS
1330 SELECT min(ptp.end_date) next_payroll_date
1331 FROM per_time_periods ptp
1332 ,per_all_assignments_f paaf
1333 WHERE ptp.payroll_id = paaf.payroll_id
1334 AND paaf.assignment_id = p_assignment_id
1335 AND ptp.end_date >= p_effective_start_date ;
1336
1337 -- TERM_LSP:BUG 4135481: added a cursor to fetch
1338 -- Last Standard Process Date and Final Close Date
1339 -- for terminated employees
1340
1341 CURSOR csr_get_termination_details
1342 (p_assignment_id NUMBER
1343 ,p_effective_end_date DATE
1344 ,p_business_group_id NUMBER
1345 )
1346 IS
1347 SELECT paa.assignment_id assignment_id
1348 ,pps.date_start start_date
1349 ,pps.actual_termination_date actual_termination_date
1350 ,pps.last_standard_process_date last_standard_process_date
1351 ,pps.final_process_date final_process_date
1352 FROM per_periods_of_service_v pps
1353 ,per_all_assignments_f paa
1354 WHERE paa.person_id = pps.person_id
1355 AND paa.assignment_id = p_assignment_id
1356 AND paa.effective_end_date = pps.actual_termination_date
1357 AND pps.date_start <= p_effective_end_date
1358 AND pps.business_group_id = nvl(p_business_group_id,g_business_group_id)
1359 -- following condn no longer mandatory as LSP date and Final Close Date can be left Null.
1360 --AND pps.actual_termination_date <> pps.last_standard_process_date
1361 AND pps.actual_termination_date = p_effective_end_date
1362 ORDER BY pps.date_start DESC;
1363
1364 --
1365 -- RETRO:BUG: 4135481
1366 -- cursor gets the defined balance id
1367 CURSOR csr_get_defined_balance_id
1368 ( p_balance_type_id NUMBER
1369 ,p_dimension_name VARCHAR2
1370 ,p_business_group_id NUMBER DEFAULT NULL
1371 )IS
1372
1373 SELECT defined_balance_id
1374 FROM pay_defined_balances pdb
1375 ,pay_balance_dimensions pbd
1376 WHERE pdb.balance_type_id = p_balance_type_id
1377 AND dimension_name = p_dimension_name --'_RGT_ASG_RETROELE_RUN'
1378 AND pbd.balance_dimension_id = pdb.balance_dimension_id
1379 AND (
1380 ( pdb.business_group_id IS NOT NULL AND
1381 pdb.business_group_id = NVL(p_business_group_id, g_business_group_id)
1382 )
1383 OR
1384 ( pdb.business_group_id IS NULL AND
1385 pdb.legislation_code = g_legislation_code
1386 )
1387 OR
1388 ( pdb.business_group_id IS NULL AND
1389 pdb.legislation_code IS NULL
1390 )
1391 )
1392 AND (
1393 ( pbd.business_group_id IS NOT NULL AND
1394 pbd.business_group_id = NVL(p_business_group_id, g_business_group_id)
1395 )
1396 OR
1397 ( pbd.business_group_id IS NULL AND
1398 pbd.legislation_code = g_legislation_code
1399 )
1400 OR
1401 ( pbd.business_group_id IS NULL AND
1402 pbd.legislation_code IS NULL
1403 )
1404 ) ;
1405
1406 -- RETRO:BUG: 4135481
1407 -- Get the assignment action id for master assignment actions
1408 CURSOR csr_get_asg_act_id_retro
1409 ( p_assignment_id NUMBER
1410 ,p_effective_start_date DATE
1411 ,p_effective_end_date DATE
1412 )IS
1413
1414 SELECT fnd_number.canonical_to_number(substr(min(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
1415 ,ppa.date_earned
1416 FROM pay_assignment_actions paa
1417 ,pay_payroll_actions ppa
1418 WHERE paa.assignment_id = p_assignment_id
1419 AND ppa.action_status = 'C'
1420 AND paa.action_status = 'C'
1421 AND paa.payroll_action_id = ppa.payroll_action_id
1422 AND ppa.action_type IN ('R', 'Q', 'I', 'B')
1423 AND ppa.date_earned BETWEEN p_effective_start_date
1424 AND p_effective_end_date
1425 AND (paa.source_action_id IS NOT NULL OR ppa.action_type in ('B','I'))
1426 GROUP BY ppa.date_earned
1427 ORDER BY fnd_number.canonical_to_number(substr(min(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) ;
1428
1429 -- Get the assignment action id for child assignment actions
1430 CURSOR csr_get_asg_act_id_dw
1431 ( p_assignment_id NUMBER
1432 ,p_effective_start_date DATE
1433 ,p_effective_end_date DATE
1434 )
1435 IS
1436 SELECT fnd_number.canonical_to_number(substr(min(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
1437 ,ppa.date_earned
1438 FROM pay_assignment_actions paa
1439 ,pay_payroll_actions ppa
1440 WHERE paa.assignment_id = p_assignment_id
1441 AND ppa.action_status = 'C'
1442 AND paa.action_status = 'C'
1443 AND paa.payroll_action_id = ppa.payroll_action_id
1444 AND ppa.action_type IN ('R', 'Q', 'I', 'B')
1445 AND ppa.date_earned >= p_effective_start_date
1446 AND (paa.source_action_id IS NOT NULL OR ppa.action_type in ('B','I'))
1447 GROUP BY ppa.date_earned
1448 ORDER BY ppa.date_earned ;
1449
1450 -- RETRO:BUG: 4135481
1451 -- The following cursor gets the date earned
1452 -- for any retro entries for an assignment
1453 -- over a period
1454 CURSOR csr_get_date_earned_retro
1455 (p_assignment_id NUMBER
1456 ,p_start_date DATE
1457 ,p_end_date DATE
1458 )
1459 IS
1460 SELECT peef.element_entry_id
1461 ,peef.element_type_id
1462 ,peef.effective_start_date
1463 ,peef.effective_end_date
1464 ,peef.creator_type
1465 ,peef.source_start_date
1466 ,peef.source_end_date
1467 ,ppa.date_earned
1468 FROM pay_entry_process_details perd
1469 ,pay_assignment_actions paa
1470 ,pay_payroll_actions ppa
1471 ,pay_element_entries_f peef
1472 WHERE peef.assignment_id = p_assignment_id
1473 AND perd.element_entry_id = peef.element_entry_id
1474 AND perd.source_asg_action_id = paa.assignment_action_id
1475 AND paa.payroll_action_id = ppa.payroll_action_id
1476 AND peef.creator_type IN ('RR', 'EE') -- Retro entries
1477 AND ppa.date_earned BETWEEN p_start_date
1478 AND p_end_date ;
1479
1480 --
1481 -- Functions and Procedures
1482 --
1483 Procedure Get_Elements_Frm_UDT (p_assignment_id IN NUMBER
1484 );
1485
1486 --
1487 Procedure Get_Udt_Data ( p_udt_name in varchar2
1488 ,p_effective_date in date );
1489
1490 --
1491 -- Added param business_group_id
1492
1493 Function Get_Udt_Value( p_table_name in varchar2 Default Null
1494 ,p_column_name in varchar2
1495 ,p_row_name in varchar2
1496 ,p_effective_date in date Default Null
1497 ,p_business_group_id in NUMBER DEFAULT NULL)
1498 Return varchar2;
1499
1500 --
1501 function process_element (p_assignment_id in number
1502 ,p_calculation_date in date
1503 ,p_rate_name in varchar2
1504 ,p_rate_type in varchar2
1505 ,p_from_time_dim in varchar2
1506 ,p_to_time_dim in varchar2
1507 ,p_fte in varchar2
1508 ,p_term_time_yes_no in varchar2
1509 )
1510 return number;
1511
1512 --
1513 function rates_history (p_assignment_id in number
1514 ,p_calculation_date in date
1515 ,p_rate_type_name in varchar2
1516 ,p_fte in varchar2
1517 ,p_to_time_dim in varchar2
1518 ,p_safeguarded_yn in varchar2
1519 ,p_rate in out nocopy number)
1520 return number;
1521
1522 --
1523 function calc_annual_sal_rate (p_assignment_id in number
1524 ,p_calculation_date in date
1525 ,p_safeguarded_yn in varchar2
1526 ,p_fte in varchar2
1527 ,p_to_time_dim in varchar2
1528 ,p_rate in out nocopy number
1529 ,p_effective_start_date in date
1530 ,p_effective_end_date in date
1531 )
1532 return number;
1533
1534 --
1535 function get_safeguarded_info (p_assignment_id in number
1536 ,p_effective_date in date
1537 )
1538 return varchar2;
1539
1540 --
1541 function get_annual_sal_rate_date (p_assignment_id in number
1542 ,p_effective_start_date in date
1543 ,p_effective_end_date in date
1544 ,p_rate in out nocopy number
1545 )
1546 return number;
1547
1548 --
1549 function calc_part_time_sal (p_assignment_id in number
1550 ,p_effective_start_date in date
1551 ,p_effective_end_date in date
1552 ,p_business_group_id in number
1553 -- 4336613 : OSLA_3A : new params
1554 ,p_sal_bal_type_id IN NUMBER DEFAULT NULL
1555 ,p_cl_bal_type_id IN NUMBER DEFAULT NULL
1556 ,p_tab_bal_ele_ids IN t_ele_ids_from_bal DEFAULT g_tab_sal_ele_ids
1557 )
1558 return number;
1559
1560 --
1561 function get_part_time_sal_date (p_assignment_id in number
1562 ,p_effective_start_date in date
1563 ,p_effective_end_date in date
1564 )
1565 return number;
1566
1567 --
1568 function calc_days_worked (p_assignment_id in number
1569 ,p_effective_start_date in date
1570 ,p_effective_end_date in date
1571 ,p_annual_sal_rate in number
1572 )
1573 return number;
1574
1575 --
1576 function get_pay_bal_id
1577 (p_balance_name IN VARCHAR2
1578 ,p_business_group_id IN NUMBER
1579 ,p_legislation_code OUT NOCOPY VARCHAR2 -- bug 4336613 : new parameter added
1580 )
1581 return number;
1582
1583 --
1584 procedure get_pay_ele_ids_from_bal
1585 (p_assignment_id in number
1586 ,p_balance_type_id in number
1587 ,p_effective_date in date
1588 ,p_error_text in varchar2
1589 ,p_error_number in number
1590 ,p_business_group_id in number
1591 ,p_tab_ele_ids out nocopy t_ele_ids_from_bal
1592 ,p_token in varchar2 default null
1593 );
1594
1595 --
1596 procedure get_eev_info (p_element_entry_id in number
1597 ,p_input_value_id in number
1598 ,p_effective_start_date in date
1599 ,p_effective_end_date in date
1600 ,p_tab_eev_info out nocopy csr_get_eev_info_date%rowtype
1601 );
1602
1603 --
1604 function get_days_absent (p_element_type_id in number
1605 ,p_element_entry_id in number
1606 ,p_effective_start_date in date
1607 ,p_effective_end_date in date
1608 )
1609 return number;
1610
1611 --
1612 function get_eet_info (p_assignment_id in number
1613 ,p_tab_ele_ids in t_ele_ids_from_bal
1614 ,p_effective_start_date in date
1615 ,p_effective_end_date in date
1616 )
1617 return number;
1618
1619 --
1620 function get_ft_days_excluded (p_assignment_id in number
1621 ,p_effective_start_date in date
1622 ,p_effective_end_date in date
1623 )
1624 return number;
1625
1626 --
1627 function get_pt_days_excluded (p_assignment_id in number
1628 ,p_effective_start_date in date
1629 ,p_effective_end_date in date
1630 ,p_days out nocopy number
1631 )
1632 return number;
1633
1634 --
1635 -- Added a new param p_emp_cat_cd
1636
1637 function get_days_excluded_date (p_assignment_id in number
1638 ,p_effective_start_date in date
1639 ,p_effective_end_date in date
1640 ,p_emp_cat_cd in varchar2 default null
1641 ,p_days out nocopy number
1642 )
1643 return number;
1644
1645 --
1646 Function Get_Translate_Asg_Emp_Cat_Code (p_asg_emp_cat_cd in varchar2
1647 ,p_effective_date in Date
1648 ,p_udt_column_name in varchar2
1649 ,p_business_group_id IN NUMBER
1650 ) Return Varchar2;
1651
1652 --
1653 Function Get_Special_ClassRule (p_assignment_id in number
1654 ,p_effective_date in date)
1655 Return varchar2 ;
1656
1657 --
1658 Function Get_Allowance_Code (p_assignment_id in number
1659 ,p_effective_date in date
1660 ,p_allowance_type in varchar2 )
1661 Return varchar2;
1662
1663 --
1664 Function Get_Grade_Fasttrack_Info (p_assignment_id in number
1665 ,p_effective_date in date)
1666 Return char;
1667
1668 --
1669 -- Criteria for Type 1 Periodic Leavers
1670 --
1671 FUNCTION chk_tp1_criteria_periodic
1672 (p_business_group_id IN NUMBER -- context
1673 ,p_effective_date IN DATE -- context
1674 ,p_assignment_id IN NUMBER -- context
1675 )
1676 RETURN VARCHAR2; -- Y or N
1677
1678 --
1679 -- Criteria for Type 1 Annual
1680 --
1681 FUNCTION chk_tp1_criteria_annual
1682 (p_business_group_id IN NUMBER -- context
1683 ,p_effective_date IN DATE -- context
1684 ,p_assignment_id IN NUMBER -- context
1685 )
1686 RETURN VARCHAR2; -- Y or N
1687 --
1688 -- Start Date
1689 --
1690 FUNCTION get_tp1_start_date
1691 (p_assignment_id IN NUMBER
1692 )
1693 RETURN VARCHAR2;
1694 --
1695 -- End Date
1696 --
1697 FUNCTION get_tp1_end_date
1698 (p_assignment_id IN NUMBER
1699 )
1700 RETURN VARCHAR2;
1701 --
1702 -- Withdrawal Confirmation
1703 --
1704 FUNCTION get_tp1_withdrawal_conf
1705 (p_assignment_id IN NUMBER
1706 )
1707 RETURN VARCHAR2;
1708 --
1709 -- Days Excluded
1710 --
1711 FUNCTION get_tp1_days_excluded
1712 (p_assignment_id in number
1713 ,p_days_excluded out nocopy varchar2
1714 )
1715 RETURN number;
1716
1717 --
1718 -- Annual Full-time Salary Rate
1719 --
1720 FUNCTION get_tp1_annual_ft_sal_rate
1721 (p_assignment_id in number
1722 ,p_annual_rate out nocopy varchar2
1723 )
1724 RETURN number;
1725 --
1726 -- Part-time Salary Paid
1727 --
1728 FUNCTION get_tp1_pt_sal_paid
1729 (p_assignment_id IN NUMBER
1730 ,p_part_time_sal out nocopy VARCHAR2
1731 )
1732 RETURN number;
1733 --
1734 -- Career Indicator
1735 --
1736 FUNCTION get_tp1_career_indicator
1737 (p_assignment_id IN NUMBER
1738 )
1739 RETURN VARCHAR2;
1740 --
1741 -- London Allowance
1742 --
1743 FUNCTION get_tp1_london_allowance
1744 (p_assignment_id IN NUMBER
1745 )
1746 RETURN VARCHAR2;
1747 --
1748 -- Special Priority Allowance
1749 --
1750 FUNCTION get_tp1_sp_allowance
1751 (p_assignment_id IN NUMBER
1752 )
1753 RETURN VARCHAR2;
1754 --
1755 -- Special Class Addition (Part-time indicator)
1756 --
1757 FUNCTION get_tp1_pt_contract_indicator
1758 (p_assignment_id IN NUMBER
1759 )
1760 RETURN VARCHAR2;
1761 --
1762 -- Other Allowances
1763 --
1764 FUNCTION get_tp1_other_allowances
1765 (p_assignment_id IN NUMBER
1766 )
1767 RETURN VARCHAR2;
1768 --
1769 -- Record Serial Number
1770 --
1771 FUNCTION get_tp1_record_serial_number
1772 (p_assignment_id IN NUMBER
1773 )
1774 RETURN VARCHAR2;
1775 --
1776 -- set_pay_proc_events_to_process
1777 --
1778 PROCEDURE set_pay_proc_events_to_process
1779 (p_assignment_id IN NUMBER
1780 ,p_status IN VARCHAR2 DEFAULT 'P'
1781 ,p_start_date IN DATE DEFAULT NULL
1782 ,p_end_date IN DATE DEFAULT NULL
1783 );
1784 --
1785 -- set_pay_proc_events_to_process
1786 -- Overloaded procedure, this one has an extra parameter p_element_entry_id
1787 --
1788 PROCEDURE set_pay_proc_events_to_process
1789 (p_assignment_id IN NUMBER
1790 ,p_element_entry_id IN NUMBER
1791 ,p_status IN VARCHAR2 DEFAULT 'P'
1792 ,p_start_date IN DATE DEFAULT NULL
1793 ,p_end_date IN DATE DEFAULT NULL
1794 );
1795
1796 --
1797 -- Extended Criteria to generate new lines of service
1798 --
1799 FUNCTION create_service_lines
1800 (p_assignment_id IN NUMBER -- context
1801 ) RETURN VARCHAR2;
1802 --
1803 -- type1_post_proc_rule
1804 --
1805 FUNCTION type1_post_proc_rule
1806 (p_ext_rslt_id IN ben_ext_rslt_dtl.ext_rslt_id%TYPE
1807 ) RETURN VARCHAR2;
1808
1809 --
1810 -- Added this procedure to the header as there was a GSCC
1811 -- warning due to the use of DEFAULT values in body.
1812 -- WARNING : This procedure is for private use inside the package body only.
1813 --
1814 PROCEDURE debug
1815 (p_trace_message IN VARCHAR2
1816 ,p_trace_location IN NUMBER DEFAULT NULL
1817 );
1818
1819 --
1820 -- Added this procedure to the header as there was a GSCC
1821 -- warning due to the use of DEFAULT values in body.
1822 -- WARNING : This procedure is for private use inside the package body only.
1823 --
1824 PROCEDURE debug_enter
1825 (p_proc_name IN VARCHAR2 DEFAULT NULL
1826 ,p_trace_on IN VARCHAR2 DEFAULT NULL
1827 );
1828
1829 --
1830 -- Added this procedure to the header as there was a GSCC
1831 -- warning due to the use of DEFAULT values in body.
1832 -- WARNING : This procedure is for private use inside the package body only.
1833 --
1834 PROCEDURE debug_exit
1835 (p_proc_name IN VARCHAR2 DEFAULT NULL
1836 ,p_trace_off IN VARCHAR2 DEFAULT NULL
1837 );
1838
1839 --
1840 -- Added this function to the header as there was a GSCC
1841 -- warning due to the use of DEFAULT values in body.
1842 -- WARNING : This function is for private use inside the package body only.
1843 --
1844 FUNCTION get_events(p_event_group IN VARCHAR2
1845 ,p_assignment_id IN NUMBER
1846 ,p_element_entry_id IN NUMBER DEFAULT NULL
1847 ,p_business_group_id IN NUMBER DEFAULT NULL
1848 ,p_start_date IN DATE
1849 ,p_end_date IN DATE
1850 ,t_proration_dates OUT NOCOPY pay_interpreter_pkg.t_proration_dates_table_type
1851 ,t_proration_changes OUT NOCOPY pay_interpreter_pkg.t_proration_type_table_type
1852 ) RETURN NUMBER;
1853
1854 --
1855 -- Added this procedure to the header as there was a GSCC
1856 -- warning due to the use of DEFAULT values in body.
1857 -- WARNING : This procedure is for private use inside the package body only.
1858 --
1859 PROCEDURE set_pay_process_events(p_grade_id IN NUMBER
1860 ,p_status IN VARCHAR2
1861 ,p_start_date IN DATE DEFAULT NULL
1862 ,p_end_date IN DATE DEFAULT NULL
1863 );
1864
1865 --
1866 -- chk_grd_change_affects_asg
1867 --
1868 -- Bug 3015917 : This new function is used to chk if a grade rule
1869 -- change event affects the assignment. This function is called
1870 -- from the event qualifier : GB Grade Rule Change
1871 --
1872 FUNCTION chk_grd_change_affects_asg
1873 (p_assignment_id IN NUMBER
1874 ,p_grade_rule_id IN NUMBER
1875 ,p_effective_date IN DATE
1876 ) RETURN BOOLEAN;
1877
1878 --
1879 -- chk_report_assignment - overloaded
1880 --
1881 FUNCTION chk_report_assignment
1882 (p_assignment_id IN NUMBER
1883 -- Bugfix 3641851:CBF1 : Added new parameter effective date
1884 ,p_effective_date IN DATE DEFAULT NULL
1885 ,p_secondary_assignment_id OUT NOCOPY NUMBER
1886 ) RETURN VARCHAR2;
1887
1888 --
1889 -- chk_report_assignment - overloaded
1890 --
1891 FUNCTION chk_report_assignment
1892 (p_assignment_id IN NUMBER
1893 -- Bugfix 3641851:CBF1 : Added new parameter effective date
1894 ,p_effective_date IN DATE DEFAULT NULL
1895 ,p_report_assignment OUT NOCOPY VARCHAR2
1896 ) RETURN NUMBER;
1897
1898 -- This procedure will find all BGs which have the same
1899 -- LEA number and have been enabled for cross BG reporting
1900 -- and store them in global collection
1901 PROCEDURE store_cross_bg_details ;
1902
1903 --
1904 -- chk_report_person
1905 --
1906 FUNCTION chk_report_person
1907 (p_business_group_id IN NUMBER -- context
1908 ,p_effective_date IN DATE -- context
1909 ,p_assignment_id IN NUMBER -- context
1910 ) RETURN BOOLEAN ;
1911
1912 --
1913 -- Check if the teacher's is a leaver
1914 --
1915 FUNCTION chk_is_teacher_a_leaver
1916 (p_business_group_id IN NUMBER
1917 ,p_effective_start_date IN DATE
1918 ,p_effective_end_date IN DATE
1919 ,p_assignment_id IN NUMBER
1920 ,p_leaver_date OUT NOCOPY DATE
1921 ) RETURN VARCHAR2 ;-- Y or N
1922
1923 --
1924 -- Check if the leaver teacher is also a re-starter
1925 --
1926 FUNCTION chk_is_leaver_a_restarter
1927 (p_business_group_id IN NUMBER
1928 ,p_effective_start_date IN DATE
1929 ,p_effective_end_date IN DATE
1930 ,p_assignment_id IN NUMBER
1931 ,p_restarter_date OUT NOCOPY DATE
1932 ) RETURN VARCHAR2 ;-- Y or N
1933
1934 --
1935 -- Check if the assignment satisfies the basic criteria
1936 --
1937 FUNCTION chk_has_tchr_elected_pension
1938 (p_business_group_id IN NUMBER -- context
1939 ,p_effective_date IN DATE -- context
1940 ,p_assignment_id IN NUMBER -- context
1941 ,p_asg_details OUT NOCOPY csr_asg_details_up%ROWTYPE
1942 ,p_asg_attributes OUT NOCOPY csr_pqp_asg_attributes_up%ROWTYPE
1943 ) RETURN VARCHAR2 ;-- Y or N
1944
1945 PROCEDURE reset_proc_status;
1946
1947 PROCEDURE warn_anthr_tchr_asg (p_assignment_id IN NUMBER) ;
1948
1949 PROCEDURE store_leaver_restarter_dates (p_assignment_id IN NUMBER ) ;
1950
1951 FUNCTION get_all_secondary_asgs
1952 (p_primary_assignment_id IN NUMBER
1953 ,p_effective_date IN DATE
1954 ) RETURN t_sec_asgs_type ;
1955
1956 PROCEDURE sort_stored_events ;
1957
1958 FUNCTION chk_has_teacher_been_reported
1959 (p_person_id IN NUMBER
1960 ,p_leaver_date IN DATE
1961 ) RETURN VARCHAR2 ;
1962
1963 -- RET:BUG 4135481: Added for Retention Allowance changes.
1964 FUNCTION get_tp1_retention_allow_rate
1965 (p_assignment_id in number
1966 ,p_ret_allow out nocopy varchar2
1967 )
1968 RETURN NUMBER;
1969
1970 -- RET:BUG 4135481:Added for Retention Allowance changes.
1971 FUNCTION calc_tp1_retention_allow_rate
1972 (p_assignment_id in number
1973 ,p_effective_start_date in date
1974 ,p_effective_end_date in date
1975 ,p_rate in out nocopy number
1976 )
1977 RETURN NUMBER;
1978
1979
1980 -- 4336613 : OSLA_3A : new function to compute grossed up OSLA payments
1981 function get_grossed_osla_payments (p_assignment_id in number
1982 ,p_effective_start_date in date
1983 ,p_effective_end_date in date
1984 ,p_business_group_id in number
1985 )
1986 return number;
1987
1988 CURSOR csr_get_dw_value(p_bal_type_id NUMBER,
1989 p_assignment_action_id NUMBER,
1990 p_start_date DATE,
1991 p_end_date DATE
1992 ) IS
1993 SELECT /*+ ORDERED */nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
1994 /* Assignment with Date worked for Run */
1995 FROM pay_assignment_actions BAL_ASSACT
1996 ,pay_payroll_actions BACT
1997 ,pay_assignment_actions ASSACT
1998 ,pay_payroll_actions PACT
1999 ,pay_run_results RR
2000 ,pay_input_values_f process_iv
2001 ,pay_run_result_values process
2002 ,pay_run_result_values TARGET
2003 ,pay_balance_feeds_f FEED
2004 where BAL_ASSACT.assignment_action_id = p_assignment_action_id
2005 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
2006 and FEED.balance_type_id = p_bal_type_id + DECODE(TARGET.input_value_id,null,0,0)
2007 and FEED.input_value_id = TARGET.input_value_id
2008 and nvl(TARGET.result_value,'0') <> '0'
2009 and TARGET.run_result_id = RR.run_result_id
2010 and RR.assignment_action_id = ASSACT.assignment_action_id
2011 and ASSACT.payroll_action_id = PACT.payroll_action_id
2012 and PACT.effective_date between
2013 FEED.effective_start_date and FEED.effective_end_date
2014 and PACT.action_type <> 'V'
2015 and RR.status in ('P','PA')
2016 and ASSACT.action_sequence >= BAL_ASSACT.action_sequence
2017 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
2018 and process.run_result_id = RR.run_result_id
2019 and process.input_value_id = process_iv.input_value_id
2020 and process_iv.name = 'Date Worked'
2021 and PACT.effective_date between
2022 process_iv.effective_start_date and process_iv.effective_end_date
2023 and fnd_date.canonical_to_date(process.result_value) between
2024 p_start_date and p_end_date;
2025
2026 CURSOR csr_get_supp_ded(p_bal_type_id NUMBER,
2027 p_assignment_id NUMBER,
2028 p_start_date DATE,
2029 p_end_date DATE
2030 ) IS
2031 SELECT /*+ ORDERED */nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
2032 /* Assignment with Date worked for Run */
2033 FROM pay_assignment_actions ASSACT
2034 ,pay_payroll_actions PACT
2035 ,pay_run_results RR
2036 ,pay_input_values_f process_iv
2037 ,pay_run_result_values process
2038 ,pay_run_result_values TARGET
2039 ,pay_balance_feeds_f FEED
2040 where FEED.balance_type_id = p_bal_type_id + DECODE(TARGET.input_value_id,null,0,0)
2041 and FEED.input_value_id = TARGET.input_value_id
2042 and nvl(TARGET.result_value,'0') <> '0'
2043 and TARGET.run_result_id = RR.run_result_id
2044 and RR.assignment_action_id = ASSACT.assignment_action_id
2045 and ASSACT.payroll_action_id = PACT.payroll_action_id
2046 and PACT.effective_date between
2047 FEED.effective_start_date and FEED.effective_end_date
2048 and PACT.action_type <> 'V'
2049 and RR.status in ('P','PA')
2050 and PACT.action_type in ('R','Q','I','B')
2051 and ASSACT.assignment_id = p_assignment_id
2052 and PACT.date_earned between p_start_date and p_end_date
2053 and process.run_result_id = RR.run_result_id
2054 and process.input_value_id = process_iv.input_value_id
2055 and process_iv.name = 'Date Worked'
2056 and PACT.effective_date between
2057 process_iv.effective_start_date and process_iv.effective_end_date
2058 and fnd_date.canonical_to_date(process.result_value) not between
2059 p_start_date and p_end_date;
2060
2061 CURSOR csr_is_supp_claim(p_run_result_id NUMBER, -- changed for bug 7278398
2062 p_start_date DATE,
2063 p_end_date DATE
2064 ) IS
2065 SELECT /*+ ORDERED */ 'N'
2066 FROM pay_run_results prr,
2067 pay_element_types_f pet,
2068 pay_input_values_f process_iv,
2069 pay_run_result_values process
2070 WHERE prr.run_result_id = p_run_result_id
2071 and prr.run_result_id = process.run_result_id
2072 and prr.element_type_id = pet.element_type_id
2073 and pet.element_type_id = process_iv.element_type_id
2074 AND process_iv.name = 'Date Worked'
2075 AND process_iv.input_value_id = process.input_value_id
2076 and (process_iv.effective_start_date between p_start_date
2077 and p_end_date
2078 or
2079 process_iv.effective_end_date between p_start_date
2080 and p_end_date
2081 or
2082 p_start_date between process_iv.effective_start_date
2083 and process_iv.effective_end_date
2084 or
2085 p_end_date between process_iv.effective_start_date
2086 and process_iv.effective_end_date
2087 )
2088 and process_iv.effective_start_date between pet.effective_start_date and pet.effective_end_date
2089 AND fnd_date.canonical_to_date(process.result_value) not between
2090 p_start_date and p_end_date;
2091
2092
2093 -- added for 5743209
2094 PROCEDURE fetch_allow_eles_frm_udt
2095 (p_assignment_id IN NUMBER
2096 ,p_effective_date IN DATE
2097 );
2098 Function Get_Allowance_Code_New ( p_assignment_id in number
2099 ,p_effective_date in date
2100 ,p_allowance_type in varchar2 ) Return varchar2;
2101
2102
2103
2104 END pqp_gb_t1_pension_extracts;
[Home] [Help]