1 PACKAGE pqp_gb_cpx_extract_functions
2 -- /* $Header: pqpgbcpx.pkh 120.4.12010000.3 2008/08/05 14:02:04 ubhat ship $ */
3 AS
4
5 --
6 -- Debug Variables.
7 --
8 g_proc_name VARCHAR2 (61)
9 := 'pqp_gb_cpx_extract_functions.';
10 g_nested_level NUMBER := 0;
11 g_trace VARCHAR2 (1) := NULL;
12 g_next_effective_date DATE;
13
14 --
15 -- Global Variables
16 --
17 g_business_group_id NUMBER := NULL;
18 g_legislation_code VARCHAR2 (10) := 'GB';
19 g_effective_date DATE;
20 g_extract_type pqp_extract_attributes.ext_dfn_type%TYPE;
21 g_extract_udt_name pay_user_tables.user_table_name%TYPE;
22 g_effective_start_date DATE;
23 g_effective_end_date DATE;
24 g_header_system_element ben_ext_rslt_dtl.val_01%TYPE;
25 g_initial_ext_date DATE;
26 g_pension_bal_name pay_balance_types.balance_name%TYPE;
27 g_pension_ele_name pay_element_types_f.element_name%TYPE;
28 g_emp_cont_iv_name pay_input_values_f.NAME%TYPE;
29 g_superann_refno_iv_name pay_input_values_f.NAME%TYPE;
30 g_superann_sal_bal_name pay_balance_types.balance_name%TYPE;
31 g_additional_cont_bal_name pay_balance_types.balance_name%TYPE;
32 g_buyback_cont_bal_name pay_balance_types.balance_name%TYPE;
33
34 -- Bug Fix 4721921
35 g_ni_ele_type_id NUMBER;
36 g_ni_cat_iv_id NUMBER;
37 g_ni_pen_iv_id NUMBER;
38 g_index NUMBER;
39
40 TYPE t_number IS TABLE OF NUMBER
41 INDEX BY BINARY_INTEGER;
42
43 TYPE t_varchar2 IS TABLE OF VARCHAR2 (200)
44 INDEX BY BINARY_INTEGER;
45
46 TYPE r_ele_entry_details IS RECORD (
47 element_type_id NUMBER,
48 element_entry_id NUMBER,
49 effective_start_date DATE,
50 effective_end_date DATE,
51 assignment_id NUMBER);
52
53 TYPE t_ele_entry_details IS TABLE OF r_ele_entry_details
54 INDEX BY BINARY_INTEGER;
55
56 -- Holds the element entry details for an assignment
57
58 g_ele_entry_details t_ele_entry_details;
59 g_pen_ele_details t_ele_entry_details;
60
61 TYPE r_ni_ele_details IS RECORD (
62 category pay_ni_element_entries_v.category%TYPE,
63 user_table_id NUMBER,
64 user_row_id NUMBER);
65
66 TYPE t_ni_ele_details IS TABLE OF r_ni_ele_details
67 INDEX BY BINARY_INTEGER;
68
69 -- Holds the NI element details
70 g_ni_ele_details t_ni_ele_details;
71 -- Holds the eligible secondary assignment id's
72
73 g_secondary_asg_ids t_number;
74
75
76 --
77 -- Cursor Definitions
78 --
79
80 -- Cursor to get extract details from PQP_EXTRACT_ATTRIBUTES table
81
82 CURSOR csr_pqp_extract_attributes
83 IS
84 SELECT eat.ext_dfn_type, udt.user_table_name
85 FROM pqp_extract_attributes eat, pay_user_tables udt
86 WHERE eat.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
87 AND udt.user_table_id(+) = eat.ext_user_table_id;
88
89 -- Cursor to get balance type id for a balance
90
91 CURSOR csr_get_pay_bal_id (c_balance_name VARCHAR2)
92 IS
93 SELECT balance_type_id
94 FROM pay_balance_types
95 WHERE balance_name = c_balance_name
96 AND ( (business_group_id IS NULL AND legislation_code IS NULL)
97 OR ( business_group_id IS NULL
98 AND legislation_code = g_legislation_code
99 )
100 OR (business_group_id = g_business_group_id)
101 );
102
103 g_additional_cont_bal_id pay_balance_types.balance_type_id%TYPE;
104 g_pension_bal_id pay_balance_types.balance_type_id%TYPE;
105 g_superann_sal_bal_id pay_balance_types.balance_type_id%TYPE;
106 g_buyback_cont_bal_id pay_balance_types.balance_type_id%TYPE;
107
108 -- Cursor to get element type id from an element name
109
110 CURSOR csr_get_pay_ele_id (c_element_name VARCHAR2, c_effective_date DATE)
111 IS
112 SELECT element_type_id
113 FROM pay_element_types_f
114 WHERE element_name = c_element_name
115 AND ( (business_group_id IS NULL AND legislation_code IS NULL)
116 OR ( business_group_id IS NULL
117 AND legislation_code = g_legislation_code
118 )
119 OR (business_group_id = g_business_group_id)
120 )
121 AND ( c_effective_date BETWEEN effective_start_date
122 AND effective_end_date
123 OR effective_start_date < c_effective_date
124 );
125
126 -- Cursor to get input value ids from balance
127
128 CURSOR csr_get_pay_iv_ids_from_bal (
129 c_balance_type_id NUMBER,
130 c_effective_start_date DATE,
131 c_effective_end_date DATE
132 )
133 IS
134 SELECT input_value_id
135 FROM pay_balance_feeds_f pbf
136 WHERE pbf.balance_type_id = c_balance_type_id
137 AND ( pbf.effective_start_date BETWEEN c_effective_start_date
138 AND c_effective_end_date
139 OR pbf.effective_end_date BETWEEN c_effective_start_date
140 AND c_effective_end_date
141 OR c_effective_start_date BETWEEN pbf.effective_start_date
142 AND pbf.effective_end_date
143 OR c_effective_end_date BETWEEN pbf.effective_start_date
144 AND pbf.effective_end_date
145 );
146
147 -- Cursor to get element type ids from Balance/Input values
148
149 CURSOR csr_get_pay_ele_ids_from_bal (c_input_value_id NUMBER)
150 IS
151 SELECT pet.element_type_id element_type_id
152 FROM pay_element_types_f pet, pay_input_values_f piv
153 WHERE pet.element_type_id = piv.element_type_id
154 AND ( ( pet.business_group_id IS NULL
155 AND pet.legislation_code IS NULL
156 )
157 OR ( pet.business_group_id IS NULL
158 AND pet.legislation_code = g_legislation_code
159 )
160 OR (pet.business_group_id = g_business_group_id)
161 )
162 AND piv.input_value_id = c_input_value_id;
163
164 TYPE t_ele_ids_from_bal IS TABLE OF csr_get_pay_ele_ids_from_bal%ROWTYPE
165 INDEX BY BINARY_INTEGER;
166
167 -- Holds the pension element ID's
168
169 g_pension_ele_ids t_ele_ids_from_bal;
170
171 -- Cursor to get input value ids for a given element type id
172 -- and input value name
173
174 CURSOR csr_get_pay_iv_id (
175 c_element_type_id NUMBER,
176 c_input_value_name VARCHAR2,
177 c_effective_date DATE
178 )
179 IS
180 SELECT input_value_id
181 FROM pay_input_values_f
182 WHERE element_type_id = c_element_type_id
183 AND NAME = c_input_value_name
184 AND ( (business_group_id IS NULL AND legislation_code IS NULL)
185 OR ( business_group_id IS NULL
186 AND legislation_code = g_legislation_code
187 )
188 OR (business_group_id = g_business_group_id)
189 )
190 AND ( c_effective_date BETWEEN effective_start_date
191 AND effective_end_date
192 OR effective_start_date < c_effective_date
193 );
194
195 -- Cursor to get element entries information for Starters
196
197 CURSOR csr_get_starters_eet_info (
198 c_assignment_id NUMBER,
199 c_effective_start_date DATE,
200 c_effective_end_date DATE
201 )
202 IS
203 SELECT pet.element_type_id, pee.element_entry_id,
204 pee.effective_start_date, pee.effective_end_date
205 FROM pay_element_entries_f pee,
206 pay_element_links_f pel,
207 pay_element_classifications pec,
208 pay_element_types_f pet
209 WHERE pee.assignment_id = c_assignment_id
210 AND pee.entry_type = 'E'
211 AND pee.creation_date BETWEEN c_effective_start_date
212 AND c_effective_end_date
213 AND pel.element_link_id = pee.element_link_id
214 AND pel.element_type_id = pet.element_type_id
215 AND ( ( pet.business_group_id IS NULL
216 AND pet.legislation_code IS NULL
217 )
218 OR ( pet.business_group_id IS NULL
219 AND pet.legislation_code = g_legislation_code
220 )
221 OR (pet.business_group_id = g_business_group_id)
222 )
223 AND pee.effective_start_date BETWEEN pet.effective_start_date
224 AND pet.effective_end_date
225 AND pee.effective_start_date BETWEEN pel.effective_start_date
226 AND pel.effective_end_date
227 AND pet.classification_id = pec.classification_id
228 -- Added to improve performance
229 AND pec.classification_name = 'Pre Tax Deductions'
230 AND pec.legislation_code = g_legislation_code
231 ORDER BY pee.effective_start_date DESC;
232
233 -- Cursor to get element entries information
234
235 CURSOR csr_get_eet_info (
236 c_assignment_id NUMBER,
237 c_effective_start_date DATE,
238 c_effective_end_date DATE
239 )
240 IS
241 SELECT pet.element_type_id, pee.element_entry_id,
242 pee.effective_start_date, pee.effective_end_date
243 FROM pay_element_entries_f pee,
244 pay_element_links_f pel,
245 pay_element_classifications pec,
246 pay_element_types_f pet
247 WHERE pee.assignment_id = c_assignment_id
248 AND pee.entry_type = 'E'
249 AND ( pee.effective_start_date BETWEEN c_effective_start_date
250 AND c_effective_end_date
251 OR pee.effective_end_date BETWEEN c_effective_start_date
252 AND c_effective_end_date
253 OR c_effective_start_date BETWEEN pee.effective_start_date
254 AND pee.effective_end_date
255 OR c_effective_end_date BETWEEN pee.effective_start_date
256 AND pee.effective_end_date
257 )
258 AND pee.effective_start_date BETWEEN pet.effective_start_date
259 AND pet.effective_end_date
260 AND pee.effective_start_date BETWEEN pel.effective_start_date
261 AND pel.effective_end_date
262 AND pel.element_link_id = pee.element_link_id
263 AND pel.element_type_id = pet.element_type_id
264 AND ( ( pet.business_group_id IS NULL
265 AND pet.legislation_code IS NULL
266 )
267 OR ( pet.business_group_id IS NULL
268 AND pet.legislation_code = g_legislation_code
269 )
270 OR (pet.business_group_id = g_business_group_id)
271 )
272 AND pet.classification_id = pec.classification_id
273 -- Added to improve performance
274 AND pec.classification_name = 'Pre Tax Deductions'
275 AND pec.legislation_code = g_legislation_code
276 ORDER BY pee.effective_start_date DESC;
277
278 -- Cursor to get multiple assignment info for a primary
279 -- assignment
280
281 CURSOR csr_get_multiple_assignments (c_assignment_id NUMBER)
282 IS
283 SELECT DISTINCT (pef2.assignment_id) assignment_id
284 FROM per_assignments_f pef, per_assignments_f pef2
285 WHERE pef.assignment_id = c_assignment_id
286 AND pef2.person_id = pef.person_id
287 AND pef2.assignment_id <> pef.assignment_id;
288
289 -- Cursor to get element entry value information
290
291 CURSOR csr_get_ele_entry_value (
292 c_element_entry_id NUMBER,
293 c_input_value_id NUMBER,
294 c_effective_start_date DATE,
295 c_effective_end_date DATE
296 )
297 IS
298 SELECT screen_entry_value
299 FROM pay_element_entry_values_f
300 WHERE element_entry_id = c_element_entry_id
301 AND input_value_id = c_input_value_id
302 AND effective_start_date = c_effective_start_date
303 AND effective_end_date = c_effective_end_date;
304
305 -- Bug Fix 5101756
306 -- Cursor to retrieve end_dates from per_time_periods
307 CURSOR csr_get_end_date (
308 c_assignment_id NUMBER,
309 c_effective_start_date DATE,
310 c_effective_end_date DATE
311 )
312 IS
313 SELECT DISTINCT (ptp.end_date) end_date
314 FROM per_time_periods ptp,
315 pay_payroll_actions ppa,
316 pay_assignment_actions paa
317 WHERE ptp.time_period_id = ppa.time_period_id
318 AND ppa.effective_date BETWEEN ptp.start_date
319 AND ptp.end_date
320 AND ppa.payroll_action_id = paa.payroll_action_id
321 -- AND ppa.effective_date BETWEEN c_effective_start_date
322 -- AND c_effective_end_date
323 AND (ptp.start_date BETWEEN c_effective_start_date
324 AND c_effective_end_date OR
325 ptp.end_date BETWEEN c_effective_start_date
326 AND c_effective_end_date OR
327 c_effective_start_date BETWEEN ptp.start_date
328 AND ptp.end_date OR
329 c_effective_end_date BETWEEN ptp.start_date
330 AND ptp.end_date)
331 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
332 AND NVL (ppa.business_group_id, g_business_group_id) =
333 g_business_group_id
334 AND paa.assignment_id = c_assignment_id
335 ORDER BY ptp.end_date;
336
337 -- Cursor to retrieve the min effective start date
338 -- from NI element entry for a given assignment id and
339 -- category
340
341 CURSOR csr_get_ele_ent_min_start_dt (
342 c_assignment_id NUMBER,
343 c_category VARCHAR2
344 )
345 IS
346 SELECT MIN (effective_start_date)
347 FROM pay_ni_element_entries_v
348 WHERE assignment_id = c_assignment_id
349 AND category = c_category;
350
351 -- Cursor to get user_table_id
352 CURSOR csr_get_udt_id (c_user_table_name VARCHAR2)
353 IS
354 SELECT user_table_id
355 FROM pay_user_tables
356 WHERE user_table_name = c_user_table_name
357 AND ( ( business_group_id IS NULL
358 AND legislation_code = g_legislation_code
359 )
360 OR ( business_group_id IS NOT NULL
361 AND business_group_id = g_business_group_id
362 )
363 );
364
365 -- Cursor to get user_column_id
366 CURSOR csr_get_user_column_id (
367 c_user_table_id NUMBER,
368 c_user_col_name VARCHAR2
369 )
370 IS
371 SELECT user_column_id
372 FROM pay_user_columns
373 WHERE user_table_id = c_user_table_id
374 AND user_column_name = c_user_col_name
375 AND ( (business_group_id IS NULL AND legislation_code IS NULL)
376 OR ( business_group_id IS NULL
377 AND legislation_code = g_legislation_code
378 )
379 OR (business_group_id = g_business_group_id)
380 );
381
382 -- Cursor to get user_row_id
383 CURSOR csr_get_user_row_id (
384 c_user_table_id NUMBER,
385 c_user_row_name VARCHAR2,
386 c_effective_date DATE
387 )
388 IS
389 SELECT user_row_id
390 FROM pay_user_rows_f
391 WHERE user_table_id = c_user_table_id
392 AND row_low_range_or_name = c_user_row_name
393 AND c_effective_date BETWEEN effective_start_date
394 AND effective_end_date
395 AND ( (business_group_id IS NULL AND legislation_code IS NULL)
396 OR ( business_group_id IS NULL
397 AND legislation_code = g_legislation_code
398 )
399 OR (business_group_id = g_business_group_id)
400 );
401
402 -- Cursor to retrieve element type id's for NI
403 -- elements from UDT
404
405 -- CURSOR csr_get_NI_ele_ids
406 -- (c_user_table_id NUMBER)
407 -- IS
408 -- SELECT pet.element_type_id
409 -- ,pet.element_name
410 -- FROM pay_user_rows_f pur
411 -- ,pay_element_types_f pet
412 -- WHERE pet.element_name = pur.row_low_range_or_name
413 -- AND NVL(pet.business_group_id, g_business_group_id)
414 -- = g_business_group_id
415 -- AND pur.user_table_id = c_user_table_id
416 -- AND NVL(pur.business_group_id, g_business_group_id)
417 -- = g_business_group_id;
418
419 -- Cursor to retrieve the NI element id's from
420 -- the UDT
421
422 CURSOR csr_get_ni_ele_ids_from_udt (
423 c_user_table_id NUMBER,
424 c_user_column_id NUMBER,
425 c_effective_date DATE
426 )
427 IS
428 SELECT pur.user_row_id
429 ,SUBSTR(pur.row_low_range_or_name, 4, 1) category
430 FROM pay_user_column_instances_f puci,
431 pay_user_rows_f pur
432 WHERE pur.user_table_id = c_user_table_id
433 AND pur.user_row_id = puci.user_row_id
434 AND puci.user_column_id = c_user_column_id
435 AND puci.business_group_id = g_business_group_id
436 AND (c_effective_date BETWEEN puci.effective_start_date
437 AND puci.effective_end_date
438 );
439
440 TYPE t_ni_ele_ids IS TABLE OF csr_get_ni_ele_ids_from_udt%ROWTYPE
441 INDEX BY BINARY_INTEGER;
442
443 -- Holds the NI Contracted out element details
444
445 g_ni_cont_out_ele_ids t_ni_ele_ids;
446
447 -- Cursor to retrieve the latest NI element
448 -- assigned to the employee from the NI list
449 -- available from the UDT
450
451 -- CURSOR csr_get_NI_ele_entry_info
452 -- (c_assignment_id NUMBER
453 -- ,c_element_type_id NUMBER
454 -- )
455 -- IS
456 -- SELECT pee.element_entry_id
457 -- ,pee.effective_start_date
458 -- ,pee.effective_end_date
459 -- FROM pay_element_entries_f pee
460 -- ,pay_element_links_f pel
461 -- WHERE pee.assignment_id = c_assignment_id
462 -- AND pee.element_link_id = pel.element_link_id
463 -- AND pel.element_type_id = c_element_type_id
464 -- AND pee.effective_start_date =
465 -- (SELECT MAX(effective_start_date)
466 -- FROM pay_element_entries
467 -- WHERE element_link_id = pee.element_link_id
468 -- AND assignment_id = c_assignment_id
469 -- );
470
471
472 -- Cursor to get NI element names from the UDT
473
474 CURSOR csr_get_ni_ele_name (c_user_table_id NUMBER)
475 IS
476 SELECT pur.user_row_id, pur.row_low_range_or_name
477 FROM pay_user_rows_f pur
478 WHERE pur.user_table_id = c_user_table_id
479 AND ( ( pur.business_group_id IS NULL
480 AND pur.legislation_code IS NULL
481 )
482 OR ( pur.business_group_id IS NULL
483 AND pur.legislation_code = g_legislation_code
484 )
485 OR (pur.business_group_id = g_business_group_id)
486 );
487
488 -- Cursor to get the active or most recent NI
489 -- element assigned to employee
490 -- Bug Fix 4721921 Modify cursor
491
492 -- CURSOR csr_get_asg_ni_ele_info (
493 -- c_assignment_id NUMBER,
494 -- c_effective_date DATE
495 -- )
496 -- IS
497 -- SELECT category
498 -- FROM pay_ni_element_entries_v
499 -- WHERE assignment_id = c_assignment_id
500 -- AND ( c_effective_date BETWEEN effective_start_date
501 -- AND effective_end_date
502 -- OR c_effective_date > effective_start_date
503 -- )
504 -- ORDER BY effective_start_date DESC;
505
506
507 -- AND pet.element_name = pur.row_low_range_or_name
508 -- AND NVL (pet.business_group_id, g_business_group_id) =
509 -- g_business_group_id
510 -- AND pur.user_table_id = c_user_table_id
511 -- AND NVL (pur.business_group_id, g_business_group_id) =
512 -- g_business_group_id
513
514 CURSOR csr_get_asg_ni_ele_info (
515 c_assignment_id NUMBER,
516 c_element_type_id NUMBER,
517 c_effective_date DATE
518 )
519 IS
520 SELECT pee.element_entry_id, pee.effective_start_date
521 ,pee.effective_end_date
522 FROM pay_element_entries_f pee, pay_element_links_f pel
523 WHERE pee.assignment_id = c_assignment_id
524 AND pee.entry_type = 'E'
525 AND pee.element_link_id = pel.element_link_id
526 AND c_effective_date BETWEEN pee.effective_start_date
527 AND pee.effective_end_date
528 AND pel.element_type_id = c_element_type_id
529 AND c_effective_date BETWEEN pel.effective_start_date
530 AND pel.effective_end_date
531 ORDER BY pee.effective_start_date DESC;
532
533
534 -- Cursor to get employment category from assignment table
535
536 CURSOR csr_get_asg_employment_cat (
537 c_assignment_id NUMBER,
538 c_effective_date DATE
539 )
540 IS
541 SELECT employment_category
542 FROM per_all_assignments_f
543 WHERE assignment_id = c_assignment_id
544 AND c_effective_date BETWEEN effective_start_date
545 AND effective_end_date
546 ORDER BY effective_start_date DESC;
547
548 -- CURSOR to get person_id and assignment_number
549 CURSOR csr_get_asg_details (c_assignment_id NUMBER, c_effective_date DATE)
550 IS
551 SELECT person_id, assignment_number, employee_category
552 FROM per_all_assignments_f
553 WHERE assignment_id = c_assignment_id
554 AND c_effective_date BETWEEN effective_start_date
555 AND effective_end_date
556 ORDER BY effective_start_date DESC;
557
558 TYPE t_asg_details IS TABLE OF csr_get_asg_details%ROWTYPE
559 INDEX BY BINARY_INTEGER;
560
561 -- Holds the assignment details
562
563 g_asg_details t_asg_details;
564
565 -- CURSOR to get marital status from person table
566
567 CURSOR csr_get_marital_status (c_person_id NUMBER, c_effective_date DATE)
568 IS
569 SELECT marital_status
570 FROM per_people_f pep
571 WHERE pep.person_id = c_person_id
572 AND c_effective_date BETWEEN pep.effective_start_date
573 AND pep.effective_end_date
574 ORDER BY pep.effective_start_date DESC;
575
576 -- CURSOR to get spouses details
577
578 CURSOR csr_get_spouses_details (c_person_id NUMBER, c_effective_date DATE)
579 IS
580 SELECT pep.date_of_birth, pep.first_name, pep.middle_names
581 FROM per_people_f pep, per_contact_relationships pcr
582 WHERE pcr.person_id = c_person_id
583 AND pcr.contact_type = 'S'
584 AND c_effective_date BETWEEN NVL (
585 pcr.date_start,
586 c_effective_date
587 )
588 AND NVL (pcr.date_end, c_effective_date)
589 AND pep.person_id = pcr.contact_person_id
590 AND c_effective_date BETWEEN pep.effective_start_date
591 AND pep.effective_end_date
592 ORDER BY pep.effective_start_date DESC;
593
594 -- Cursor to get udt col name information
595
596 CURSOR csr_get_user_col_name (
597 c_user_table_id NUMBER,
598 c_user_row_id NUMBER,
599 c_effective_date DATE
600 )
601 IS
602 SELECT puc.user_column_name
603 FROM pay_user_columns puc, pay_user_column_instances_f puci
604 WHERE puci.user_row_id = c_user_row_id
605 AND puci.user_column_id = puc.user_column_id
606 AND puc.user_table_id = c_user_table_id
607 AND puci.business_group_id = g_business_group_id
608 AND c_effective_date BETWEEN puci.effective_start_date
609 AND puci.effective_end_date;
610
611 -- Cursor to get the run result value sum for an element
612 CURSOR csr_get_rresult_value (
613 c_assignment_action_id NUMBER,
614 c_element_type_id NUMBER,
615 c_input_value_id NUMBER
616 )
617 IS
618 SELECT NVL (SUM (result_value), 0) result_value
619 FROM pay_run_result_values target,
620 pay_run_results rr,
621 pay_payroll_actions pact,
622 pay_assignment_actions assact,
623 pay_payroll_actions bact,
624 pay_assignment_actions bal_assact
625 WHERE bal_assact.assignment_action_id = c_assignment_action_id
626 AND bal_assact.payroll_action_id = bact.payroll_action_id
627 AND NVL (target.result_value, '0') <> '0'
628 AND target.run_result_id = rr.run_result_id
629 AND target.input_value_id = c_input_value_id
630 AND rr.assignment_action_id = assact.assignment_action_id
631 AND rr.element_type_id = c_element_type_id
632 AND assact.payroll_action_id = pact.payroll_action_id
633 AND rr.status IN ('P', 'PA')
634 AND pact.time_period_id = bact.time_period_id
635 AND assact.action_sequence <= bal_assact.action_sequence
636 AND assact.assignment_id = bal_assact.assignment_id;
637
638 -- Cursor to get the translated code from the translation UDT
639 CURSOR csr_get_udt_translated_code (
640 c_user_table_id NUMBER,
641 c_effective_date DATE,
642 c_asg_user_col_id NUMBER,
643 c_ext_user_col_id NUMBER,
644 c_value VARCHAR2
645 )
646 IS
647 SELECT extv.VALUE ext_value
648 FROM pay_user_rows_f urws,
649 pay_user_column_instances_f asgv,
650 pay_user_column_instances_f extv
651 WHERE urws.user_table_id = c_user_table_id
652 AND c_effective_date BETWEEN urws.effective_start_date
653 AND urws.effective_end_date
654 AND asgv.user_column_id = c_asg_user_col_id
655 AND c_effective_date BETWEEN asgv.effective_start_date
656 AND asgv.effective_end_date
657 AND extv.user_column_id = c_ext_user_col_id
658 AND c_effective_date BETWEEN extv.effective_start_date
659 AND extv.effective_end_date
660 AND asgv.user_row_id = urws.user_row_id
661 AND extv.user_row_id = asgv.user_row_id
662 AND asgv.VALUE = c_value;
663
664 --
665 -- Added for Hour Change report
666 --
667
668 -- Cursor to get FTE value
669 CURSOR csr_get_fte_value (c_assignment_id NUMBER, c_effective_date DATE)
670 IS
671 SELECT VALUE fte
672 FROM per_assignment_budget_values_f
673 WHERE assignment_id = c_assignment_id
674 AND unit = 'FTE'
675 AND c_effective_date BETWEEN effective_start_date
676 AND effective_end_date
677 ORDER BY effective_start_date DESC;
678
679 --
680 -- Cursor to fetch the record if of the details record, but not the hidden one
681 -- WARNING : This works only if there is one displayed detail record.
682 -- Do we need to raise an error if there are 2 diplayed detail records??
683 -- If yes, then Fetch ... , check .. and raise error
684 -- Alternatively, modify the cursor to return the required id by querying on name.
685 CURSOR csr_ext_rcd_id(p_hide_flag IN VARCHAR2
686 ,p_rcd_type_cd IN VARCHAR2
687 ) IS
688 SELECT rcd.ext_rcd_id
689 FROM ben_ext_rcd rcd
690 ,ben_ext_rcd_in_file RinF
691 ,ben_ext_dfn dfn
692 WHERE dfn.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
693 AND RinF.ext_file_id = dfn.ext_file_id
694 AND RinF.hide_flag = p_hide_flag
695 AND RinF.ext_rcd_id = rcd.ext_rcd_id
696 AND rcd.rcd_type_cd = p_rcd_type_cd;
697
698 --
699 -- Procedures and Functions
700 --
701
702 -- FUNCTIONS (Private)
703
704 -- Function set_periodic_run_dates
705 FUNCTION set_periodic_run_dates (
706 p_error_number OUT NOCOPY NUMBER,
707 p_error_text OUT NOCOPY VARCHAR2
708 )
709 RETURN NUMBER;
710
711
712 -- Function Get Input Value Id
713 FUNCTION get_input_value_id (
714 p_element_type_id IN NUMBER,
715 p_input_value_name IN VARCHAR2,
716 p_effective_date IN DATE
717 )
718 RETURN NUMBER;
719
720
721 -- Function Get Pay Balance ID From Name
722
723 FUNCTION get_pay_bal_id (p_balance_name IN VARCHAR2)
724 RETURN NUMBER;
725
726
727 -- Function Fetch_CPX_UDT_details
728 FUNCTION fetch_cpx_udt_details (
729 p_error_number OUT NOCOPY NUMBER,
730 p_error_text OUT NOCOPY VARCHAR2
731 )
732 RETURN NUMBER;
733
734
735 -- Function set_extract_globals
736 FUNCTION set_extract_globals (
737 p_assignment_id IN NUMBER,
738 p_business_group_id IN NUMBER,
739 p_effective_date IN DATE,
740 p_error_number OUT NOCOPY NUMBER,
741 p_error_text OUT NOCOPY VARCHAR2
742 )
743 RETURN NUMBER;
744
745
746 -- Function chk_is_employee_a_starter
747 FUNCTION chk_is_employee_a_starter (
748 p_assignment_id IN NUMBER,
749 p_effective_start_date IN DATE,
750 p_effective_end_date IN DATE
751 )
752 RETURN VARCHAR2;
753
754
755 -- Function get_ele_entry_value
756 FUNCTION get_ele_entry_value (
757 p_element_entry_id IN NUMBER,
758 p_input_value_id IN NUMBER,
759 p_effective_start_date IN DATE,
760 p_effective_end_date IN DATE
761 )
762 RETURN VARCHAR2;
763
764
765 -- Function Get Pay Element Ids From Balance
766 FUNCTION get_pay_ele_ids_from_bal (
767 p_balance_type_id IN NUMBER,
768 p_effective_start_date IN DATE,
769 p_effective_end_date IN DATE,
770 p_tab_ele_ids OUT NOCOPY t_ele_ids_from_bal,
771 p_error_number OUT NOCOPY NUMBER,
772 p_error_text OUT NOCOPY VARCHAR2
773 )
774 RETURN NUMBER;
775
776
777 -- Function get_udt_id
778 FUNCTION get_udt_id (p_udt_name IN VARCHAR2)
779 RETURN NUMBER;
780
781
782 -- Function get_user_column_name
783 FUNCTION get_user_column_name (
784 p_user_table_id IN NUMBER,
785 p_user_row_id IN NUMBER,
786 p_effective_date IN DATE
787 )
788 RETURN t_varchar2;
789
790
791 -- Function get_user_column_id
792 FUNCTION get_user_column_id (
793 p_user_table_id IN NUMBER,
794 p_user_col_name IN VARCHAR2
795 )
796 RETURN NUMBER;
797
798
799 -- Function get_NI_cont_out_ele_details
800 FUNCTION get_ni_cont_out_ele_details (
801 p_error_number OUT NOCOPY NUMBER,
802 p_error_text OUT NOCOPY VARCHAR2
803 )
804 RETURN NUMBER;
805
806
807 -- Function get_asg_employment_cat
808 FUNCTION get_asg_employment_cat (
809 p_assignment_id IN NUMBER,
810 p_effective_date IN DATE
811 )
812 RETURN VARCHAR2;
813
814
815 -- Function get_part_time_indicator
816 FUNCTION get_part_time_indicator (
817 p_assignment_id IN NUMBER,
818 p_effective_date IN DATE
819 )
820 RETURN VARCHAR2;
821
822
823 -- Function get_marital_status
824 FUNCTION get_marital_status (
825 p_assignment_id IN NUMBER,
826 p_effective_date IN DATE
827 )
828 RETURN VARCHAR2;
829
830
831 -- Function get_NI_indicator
832 FUNCTION get_ni_indicator (
833 p_assignment_id IN NUMBER,
834 p_effective_date IN DATE
835 )
836 RETURN VARCHAR2;
837
838
839 -- Function get_asg_bal_value
840 FUNCTION get_asg_bal_value (
841 p_assignment_id IN NUMBER,
842 p_balance_type_id IN NUMBER,
843 p_effective_start_date IN DATE,
844 p_effective_end_date IN DATE
845 )
846 RETURN NUMBER;
847
848
849 -- Function get_person_bal_value
850 FUNCTION get_person_bal_value (
851 p_assignment_id IN NUMBER,
852 p_balance_type_id IN NUMBER,
853 p_effective_start_date IN DATE,
854 p_effective_end_date IN DATE
855 )
856 RETURN NUMBER;
857
858
859 -- Function get_remuneration_from_bal
860 FUNCTION get_remuneration_from_bal (
861 p_assignment_id IN NUMBER,
862 p_balance_type_id IN NUMBER,
863 p_effective_start_date IN DATE,
864 p_effective_end_date IN DATE
865 )
866 RETURN VARCHAR2;
867
868
869 -- Function get_balance_value
870 FUNCTION get_balance_value (
871 p_assignment_id IN NUMBER,
872 p_balance_type_id IN NUMBER,
873 p_effective_start_date IN DATE,
874 p_effective_end_date IN DATE
875 )
876 RETURN NUMBER;
877
878
879 -- Added for Annual
880 --
881 -- Function chk_is_employee_a_member
882 FUNCTION chk_is_employee_a_member (
883 p_assignment_id IN NUMBER,
884 p_effective_start_date IN DATE,
885 p_effective_end_date IN DATE
886 )
887 RETURN VARCHAR2;
888
889
890 -- Function get_latest_action_id
891 FUNCTION get_latest_action_id (
892 p_assignment_id IN NUMBER,
893 p_effective_date IN DATE
894 )
895 RETURN NUMBER;
896
897
898 -- Function get_asg_ele_rresult_value
899 FUNCTION get_asg_ele_rresult_value (
900 p_assignment_id IN NUMBER,
901 p_element_type_id IN NUMBER,
902 p_input_value_id IN NUMBER,
903 p_effective_start_date IN DATE,
904 p_effective_end_date IN DATE
905 )
906 RETURN NUMBER;
907
908
909 -- Function get_person_ele_rresult_value
910 FUNCTION get_person_ele_rresult_value (
911 p_assignment_id IN NUMBER,
912 p_element_type_id IN NUMBER,
913 p_input_value_id IN NUMBER,
914 p_effective_start_date IN DATE,
915 p_effective_end_date IN DATE
916 )
917 RETURN NUMBER;
918
919
920 -- Function get_udt_translated_code
921 FUNCTION get_udt_translated_code (
922 p_user_table_name IN VARCHAR2,
923 p_effective_date IN DATE,
924 p_asg_user_col_name IN VARCHAR2,
925 p_ext_user_col_name IN VARCHAR2,
926 p_value IN VARCHAR2
927 )
928 RETURN VARCHAR2;
929
930
931 --
932 -- Added for Hour Change
933 --
934
935 -- Function get_fte_value
936 FUNCTION get_fte_value (p_assignment_id IN NUMBER, p_effective_date IN DATE)
937 RETURN NUMBER;
938
939
940 -- To be included as Formula functions (public)
941
942 -- Function chk_employee_qual_for_starters
943 FUNCTION chk_employee_qual_for_starters (
944 p_business_group_id IN NUMBER -- Context
945 ,
946 p_effective_date IN DATE -- Context
947 ,
948 p_assignment_id IN NUMBER -- Context
949 ,
950 p_error_number OUT NOCOPY NUMBER,
951 p_error_text OUT NOCOPY VARCHAR2
952 )
953 RETURN VARCHAR2;
954
955
956 -- Function get_superannuation_ref_no
957 FUNCTION get_superannuation_ref_no (p_assignment_id IN NUMBER -- Context
958 )
959 RETURN VARCHAR2;
960
961
962 -- Functio get_emp_cont_rate
963 FUNCTION get_emp_cont_rate (p_assignment_id IN NUMBER -- Context
964 )
965 RETURN VARCHAR2;
966
967
968 -- Function get_scheme_number
969 FUNCTION get_scheme_number (
970 p_assignment_id IN NUMBER -- Context
971 ,
972 p_scheme_number OUT NOCOPY VARCHAR2,
973 p_error_number OUT NOCOPY NUMBER,
974 p_error_text OUT NOCOPY VARCHAR2
975 )
976 RETURN NUMBER;
977
978
979 -- Function get_employer_reference_number
980 FUNCTION get_employer_reference_number (
981 p_assignment_id IN NUMBER -- Context
982 ,
983 p_employer_ref_no OUT NOCOPY VARCHAR2,
984 p_error_number OUT NOCOPY NUMBER,
985 p_error_text OUT NOCOPY VARCHAR2
986 )
987 RETURN NUMBER;
988
989
990 -- Function get_date_joined_pens_fund
991 FUNCTION get_date_joined_pens_fund (
992 p_assignment_id IN NUMBER -- Context
993 ,
994 p_dt_joined_pens OUT NOCOPY DATE,
995 p_error_number OUT NOCOPY NUMBER,
996 p_error_text OUT NOCOPY VARCHAR2
997 )
998 RETURN NUMBER;
999
1000
1001 -- Function get_date_contracted_out
1002 FUNCTION get_date_contracted_out (
1003 p_assignment_id IN NUMBER -- Context
1004 ,
1005 p_dt_cont_out OUT NOCOPY DATE,
1006 p_error_number OUT NOCOPY NUMBER,
1007 p_error_text OUT NOCOPY VARCHAR2
1008 )
1009 RETURN NUMBER;
1010
1011
1012 -- Function get_employment_number
1013 FUNCTION get_employment_number (p_assignment_id IN NUMBER)
1014 RETURN VARCHAR2;
1015
1016
1017 -- Function get_employee_category
1018 FUNCTION get_employee_category (p_assignment_id IN NUMBER)
1019 RETURN VARCHAR2;
1020
1021
1022 -- Function get_system_data_element
1023 FUNCTION get_system_data_element
1024 RETURN VARCHAR2;
1025
1026
1027 -- Function get_STARTERS_part_time_ind
1028 FUNCTION get_starters_part_time_ind (p_assignment_id IN NUMBER -- Context
1029 )
1030 RETURN VARCHAR2;
1031
1032
1033 -- Function get_CPX_part_time_ind
1034 FUNCTION get_cpx_part_time_ind (p_assignment_id IN NUMBER -- Context
1035 )
1036 RETURN VARCHAR2;
1037
1038
1039 -- Function get_STARTERS_marital_status
1040 FUNCTION get_starters_marital_status (p_assignment_id IN NUMBER -- Context
1041 )
1042 RETURN VARCHAR2;
1043
1044
1045 -- Function get_CPX_marital_status
1046 FUNCTION get_cpx_marital_status (p_assignment_id IN NUMBER -- Context
1047 )
1048 RETURN VARCHAR2;
1049
1050
1051 -- Function get_spouses_date_of_birth
1052 FUNCTION get_spouses_date_of_birth (p_assignment_id IN NUMBER -- Context
1053 )
1054 RETURN DATE;
1055
1056
1057 -- Function get_spouses_initials
1058 FUNCTION get_spouses_initials (p_assignment_id IN NUMBER -- Context
1059 )
1060 RETURN VARCHAR2;
1061
1062
1063 -- Function get_STARTERS_NI_indicator
1064 FUNCTION get_starters_ni_indicator (p_assignment_id IN NUMBER -- Context
1065 )
1066 RETURN VARCHAR2;
1067
1068
1069 -- Function get_CPX_NI_indicator
1070 FUNCTION get_cpx_ni_indicator (p_assignment_id IN NUMBER -- Context
1071 )
1072 RETURN VARCHAR2;
1073
1074
1075 -- Function get_actual_remuneration
1076 FUNCTION get_actual_remuneration (p_assignment_id IN NUMBER -- Context
1077 )
1078 RETURN VARCHAR2;
1079
1080
1081 -- Function get_pensionable_remuneration
1082 FUNCTION get_pensionable_remuneration (p_assignment_id IN NUMBER -- Context
1083 )
1084 RETURN VARCHAR2;
1085
1086
1087 -- Function get_total_number_data_records
1088 FUNCTION get_total_number_data_records (p_type IN VARCHAR2 DEFAULT NULL)
1089 RETURN VARCHAR2;
1090
1091
1092 -- Function get_data_element_total_value
1093 FUNCTION get_data_element_total_value (p_val_seq IN NUMBER)
1094 RETURN VARCHAR2;
1095
1096
1097 --
1098 -- Added for Annual
1099 --
1100
1101 -- Function chk_employee_qual_for_annual
1102 FUNCTION chk_employee_qual_for_annual (
1103 p_business_group_id IN NUMBER -- context
1104 ,
1105 p_effective_date IN DATE -- context
1106 ,
1107 p_assignment_id IN NUMBER -- context
1108 ,
1109 p_error_number OUT NOCOPY NUMBER,
1110 p_error_text OUT NOCOPY VARCHAR2
1111 )
1112 RETURN VARCHAR2;
1113
1114
1115 -- Function get_member_contributions
1116 FUNCTION get_member_contributions (p_assignment_id IN NUMBER)
1117 RETURN VARCHAR2;
1118
1119
1120 -- Function get_NI_earnings
1121 FUNCTION get_ni_earnings (p_assignment_id IN NUMBER)
1122 RETURN VARCHAR2;
1123
1124
1125 -- Function get_additional_contributions
1126 FUNCTION get_additional_contributions (p_assignment_id IN NUMBER)
1127 RETURN VARCHAR2;
1128
1129
1130 -- Function get_buyback_contributions
1131 FUNCTION get_buyback_contributions (p_assignment_id IN NUMBER)
1132 RETURN VARCHAR2;
1133
1134
1135 --
1136 -- Added for Hour Change
1137 --
1138
1139 -- Function chk_employee_qual_for_pthrch
1140 FUNCTION chk_employee_qual_for_pthrch (
1141 p_business_group_id IN NUMBER -- context
1142 ,
1143 p_effective_date IN DATE -- context
1144 ,
1145 p_assignment_id IN NUMBER -- context
1146 ,
1147 p_error_number OUT NOCOPY NUMBER,
1148 p_error_text OUT NOCOPY VARCHAR2
1149 )
1150 RETURN VARCHAR2;
1151
1152
1153 -- Function get_part_time_percent
1154 FUNCTION get_part_time_percent (p_assignment_id IN NUMBER)
1155 RETURN VARCHAR2;
1156
1157
1158 -- PROCEDURES (Private)
1159
1160 -- Procedure debug
1161 PROCEDURE DEBUG (
1162 p_trace_message IN VARCHAR2,
1163 p_trace_location IN NUMBER DEFAULT NULL
1164 );
1165
1166
1167 -- Procedure debug_enter
1168 PROCEDURE debug_enter (
1169 p_proc_name IN VARCHAR2 DEFAULT NULL,
1170 p_trace_on IN VARCHAR2 DEFAULT NULL
1171 );
1172
1173
1174 -- Procedure debug_exit
1175 PROCEDURE debug_exit (
1176 p_proc_name IN VARCHAR2 DEFAULT NULL,
1177 p_trace_off IN VARCHAR2 DEFAULT NULL
1178 );
1179
1180
1181 -- Procedure set_annual_run_dates
1182 PROCEDURE set_annual_run_dates;
1183
1184
1185 -- Procedure get_all_sec_assignments
1186 PROCEDURE get_all_sec_assignments (
1187 p_assignment_id IN NUMBER,
1188 p_secondary_asg_ids OUT NOCOPY t_number
1189 );
1190
1191
1192 -- Procedure get_eligible_sec_assignments
1193 PROCEDURE get_eligible_sec_assignments (
1194 p_assignment_id IN NUMBER,
1195 p_secondary_asg_ids OUT NOCOPY t_number
1196 );
1197
1198
1199 -- Procedure set_assignment_details
1200 PROCEDURE set_assignment_details (
1201 p_assignment_id IN NUMBER,
1202 p_effective_date IN DATE
1203 );
1204
1205
1206 -- Procedure get_NI_element_details
1207 PROCEDURE get_ni_element_details;
1208 END pqp_gb_cpx_extract_functions;
[Home] [Help]