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