1 PACKAGE pqp_gb_tp_pension_extracts AUTHID CURRENT_USER AS
2 -- /* $Header: pqpgbtp4.pkh 120.2 2008/03/11 16:38:57 dchindar noship $ */
3 --
4 -- Debug Variables.
5 --
6 g_proc_name VARCHAR2(61):= 'pqp_gb_tp_pension_extracts.';
7 g_nested_level NUMBER:= 0;
8 g_debug BOOLEAN := hr_utility.debug_enabled;
9 g_trace VARCHAR2(1) := NULL;
10 --
11 -- Global Varibales
12 --
13 g_business_group_id NUMBER:= NULL; -- IMPORTANT TO KEEP NULL
14 g_legislation_code VARCHAR2(10):= 'GB';
15 g_effective_date DATE;
16
17 g_extract_type fnd_lookups.lookup_code%type;
18 g_last_effective_date DATE;
19 g_next_effective_date DATE;
20 g_effective_run_date DATE;
21 g_extract_udt_name pay_user_tables.user_table_name%type;
22 g_criteria_location_code pay_user_column_instances_f.value%type;
23 g_lea_number VARCHAR2(3):=RPAD(' ',3,' ');
24 g_crossbg_enabled VARCHAR2(1) := 'N';
25 g_estb_number VARCHAR2(4):='0000';
26 g_originators_title VARCHAR2(16);
27 g_header_system_element ben_ext_rslt_dtl.val_01%type;
28
29 -- flag to check if there are more than one lea with the same lea numebr in tha same BG.
30 -- This flag will be set while setting the globals. and for the first valid assignment
31 -- warning msg will be displayed.
32 g_multi_lea_exist VARCHAR2(1) := 'N' ;
33 g_token_org_name VARCHAR2 (240) ; -- used to raise warning if more than one lea org
34 -- is defined with same lea Number.
35 -- Request ID of parent process which has generated this thread.
36 g_parent_request_id NUMBER := -1 ;
37
38 --flag to check if there are NO LOCATIONS for the given LEA.
39 -- This flag will be set while setting the globals.
40 -- and for the first valid assignment warning msg will be displayed.
41 -- Possible Values
42 -- 1. 'Y' -> Default
43 -- 2. 'N' -> Reported : No Location for LEA found in/across BGs and this Warning has been Reported
44 -- So don't check the Assignments further in the current thread.
45 g_warn_no_location VARCHAR2(3) := 'Y';
46
47 --
48 --
49 --
50 CURSOR csr_pqp_extract_attributes(p_ext_dfn_id IN NUMBER DEFAULT NULL) IS
51 SELECT eat.ext_dfn_type
52 ,udt.user_table_name
53 ,udt.user_table_id
54 FROM pqp_extract_attributes eat
55 ,pay_user_tables udt
56 WHERE eat.ext_dfn_id = nvl(p_ext_dfn_id, ben_ext_thread.g_ext_dfn_id)
57 AND udt.user_table_id(+) = eat.ext_user_table_id;
58 --
59 --
60 --
61 /* CURSOR csr_lea_details (
62 p_organization_id IN NUMBER
63 ) IS
64 SELECT org_information1 lea_number
65 ,org_information2 lea_name
66 ,nvl(org_information3,'N') CrossBG_Enabled
67 FROM hr_organization_information
68 WHERE organization_id = p_organization_id
69 AND org_information_context = 'PQP_GB_EDU_AUTH_LEA_INFO';*/
70
71 -- ENH1 : added p_lea_number as parameter to fetch only the details of required LEA.
72 -- Added organization_name in select list.
73 CURSOR csr_lea_details (
74 p_organization_id IN NUMBER
75 ,p_lea_number IN VARCHAR2 -- ENH1 : new parameter to fetch only the details of required LEA.
76 ) IS
77 SELECT hoi.org_information1 lea_number
78 ,hoi.org_information2 lea_name
79 ,nvl(org_information3,'N') CrossBG_Enabled
80 ,hou.name organization_name -- Added for Warning msg Token.
81 ,hou.organization_id organization_id -- Added for non-Lea orgs.
82 ,DECODE(hoi.organization_id
83 ,p_organization_id, 0
84 ,hoi.organization_id) orgidcol -- added to Order by the result so that
85 -- the LEA at BG level comes first.
86 FROM hr_organization_information hoi
87 ,hr_organization_units hou
88 WHERE hoi.org_information_context = 'PQP_GB_EDU_AUTH_LEA_INFO'
89 AND hou.business_group_id = p_organization_id
90 AND hoi.organization_id = hou.organization_id
91 AND (( p_lea_number IS NOT NULL
92 AND hoi.org_information1 = p_lea_number
93 )
94 OR
95 ( p_lea_number IS NULL
96 AND hoi.organization_id = p_organization_id
97 )) ORDER BY orgidcol ASC, CrossBG_Enabled DESC;
98
99 --
100 --
101 --
102 CURSOR csr_lea_details_by_loc (p_location_id IN NUMBER) IS
103 SELECT hoi.org_information1 lea_number
104 ,hoi.org_information2 lea_name
105 ,hoi.organization_id organization_id --Added for non-lea organizations.
106 FROM hr_organization_units_v org
107 ,hr_organization_information hoi
108 WHERE org.location_id = p_location_id
109 AND hoi.organization_id = org.organization_id
110 AND hoi.org_information_context = 'PQP_GB_EDU_AUTH_LEA_INFO';
111 --
112 --
113 --
114 --note on top to clarify what the Cursor is doing for lea/non-lea.
115 CURSOR csr_estb_details
116 (p_location_code VARCHAR2 DEFAULT NULL
117 ,p_location_id NUMBER DEFAULT NULL
118 ,p_lea_estb_yn VARCHAR2 DEFAULT NULL
119 ,p_estb_number VARCHAR2 DEFAULT NULL
120 ,p_estb_name VARCHAR2 DEFAULT NULL
121 ,p_estb_type VARCHAR2 DEFAULT NULL
122 ,p_business_group_id NUMBER DEFAULT NULL
123 ) IS
124 SELECT loc.business_group_id business_group_id
125 ,loc.location_id location_id
126 ,lei.lei_information1 lea_estb_yn
127 ,lpad(lei.lei_information2,4,'0') estb_number
128 ,lei.lei_information3 estb_name
129 ,lei.lei_information4 estb_type
130 ,lpad(lei.lei_information5,2,'0') school_number
131 ,lei.lei_information6 lea_number
132 FROM hr_location_extra_info lei
133 ,hr_locations_all loc
134 WHERE lei.information_type = 'PQP_GB_EDU_ESTB_INFO'
135 AND loc.business_group_id = NVL(p_business_group_id,g_business_group_id) -- Bug 2175986 NOT A BUG
136 AND loc.location_code = NVL(p_location_code,loc.location_code)
137 AND loc.location_id = NVL(p_location_id,loc.location_id)
138 AND loc.location_id = lei.location_id
139 AND (lei.lei_information1 IS NOT NULL
140 AND
141 (lei.lei_information1 = NVL(p_lea_estb_yn,lei.lei_information1)))
142 AND lpad(lei.lei_information2,4,'0') = NVL(lpad(p_estb_number,4,'0')
143 ,lpad(lei.lei_information2,4,'0'))
144 AND (lei.lei_information3 IS NOT NULL
145 AND
146 (lei.lei_information3 = NVL(p_estb_name,lei.lei_information3)))
147 AND (lei.lei_information4 IS NOT NULL
148 AND
149 (lei.lei_information4 = NVL(p_estb_type,lei.lei_information4)))
150 AND
151 (
152 ( -- This applies to Non-LEA Locations only
153 p_estb_number IS NOT NULL -- We know the loc code only when we call for non-lea
154 AND
155 (lei.lei_information6 IS NULL -- either lea num is null
156 OR
157 lei.lei_information6 = g_lea_number -- or the same as g_lea_number
158 )
159 ) -- This applies to Non-LEA Locations only
160 OR
161 ( -- This applies only to LEA Locations and LEA Report
162 p_estb_number is NULL
163 AND
164 ( lei.lei_information6 = g_lea_number --p_lea_number --LEA Number found at Location EIT
165 OR
166 ( lei.lei_information6 IS NULL -- LEA numebr in null at location EIT
167 AND
168 g_lea_number = (SELECT hoi.org_information1 --Find LEA Number at ORG level
169 FROM hr_organization_information hoi
170 WHERE hoi.org_information_context = 'PQP_GB_EDU_AUTH_LEA_INFO'
171 AND hoi.organization_id = NVL(p_business_group_id,g_business_group_id)
172 )
173 )
174 )
175 )-- This applies only to LEA Locations and LEA Report
176 ) ;
177
178
179 TYPE t_criteria_estbs_type IS TABLE OF csr_estb_details%ROWTYPE
180 INDEX BY BINARY_INTEGER;
181
182 g_criteria_estbs t_criteria_estbs_type;
183 --
184 --
185 --
186 CURSOR csr_event_group_details
187 (p_event_group_name VARCHAR2
188 ) IS
189 SELECT event_group_id
190 ,event_group_name
191 ,event_group_type
192 ,proration_type
193 FROM pay_event_groups
194 WHERE event_group_name = p_event_group_name
195 AND NVL(business_group_id,g_business_group_id) = g_business_group_id;
196 --
197 --
198 --
199 -- CURSOR csr_pqp_assignment_attributes
200 -- (p_assignment_id NUMBER
201 -- ,p_effective_date DATE DEFAULT NULL
202 -- ) IS
203 -- SELECT eaat.effective_start_date effective_start_date
204 -- ,eaat.effective_end_date effective_end_date
205 -- ,eaat.tp_is_teacher tp_is_teacher
206 -- ,eaat.tp_safeguarded_grade tp_safeguarded_grade
207 -- ,eaat.tp_elected_pension tp_elected_pension
208 -- ,DECODE(SIGN(paat.effective_end_date - eaat.effective_start_date)
209 -- ,-1,paat.effective_start_date
210 -- ,NULL) prev_effective_start_date
211 -- ,DECODE(SIGN(paat.effective_end_date - eaat.effective_start_date)
212 -- ,-1,paat.effective_end_date
213 -- ,NULL) prev_effective_end_date
214 -- ,DECODE(SIGN(paat.effective_end_date - eaat.effective_start_date)
215 -- ,-1,paat.tp_is_teacher
216 -- ,NULL) prev_tp_is_teacher
217 -- ,DECODE(SIGN(paat.effective_end_date - eaat.effective_start_date)
218 -- ,-1,paat.tp_safeguarded_grade
219 -- ,NULL) prev_tp_safeguarded_grade
220 -- ,DECODE(SIGN(paat.effective_end_date - eaat.effective_start_date)
221 -- ,-1,paat.tp_elected_pension
222 -- ,NULL) prev_tp_elected_pension
223 -- FROM pqp_assignment_attributes_f eaat -- effective aat
224 -- ,pqp_assignment_attributes_f paat -- previous aat
225 -- WHERE eaat.assignment_id = p_assignment_id
226 -- AND p_effective_date
227 -- BETWEEN eaat.effective_start_date
228 -- AND eaat.effective_end_date
229 -- AND paat.assignment_id = eaat.assignment_id
230 -- ORDER BY eaat.effective_start_date DESC
231 -- ,NVL(prev_effective_end_date
232 -- ,fnd_date.canonical_to_date('00010101 00:00:00')) DESC
233 -- ;
234
235 --
236 --
237 --
238 CURSOR csr_pqp_asg_attributes_dn -- down
239 (p_assignment_id NUMBER
240 ,p_effective_date DATE DEFAULT NULL
241 ) IS
242 SELECT eaat.assignment_attribute_id assignment_attribute_id
243 ,eaat.assignment_id assignment_id
244 ,eaat.effective_start_date effective_start_date
245 ,eaat.effective_end_date effective_end_date
246 ,eaat.tp_is_teacher tp_is_teacher
247 -- SSC: Added for head Teacher seconded location for salary scale calculation
248 ,eaat.tp_headteacher_grp_code tp_headteacher_grp_code
249 ,eaat.tp_safeguarded_grade tp_safeguarded_grade
250 ,eaat.tp_elected_pension tp_elected_pension
251 ,eaat.creation_date creation_date
252 -- Added for salary scale changes
253 ,eaat.tp_safeguarded_spinal_point_id tp_sf_spinal_point_id
254 FROM pqp_assignment_attributes_f eaat -- effective aat
255 WHERE eaat.assignment_id = p_assignment_id
256 AND ( -- retrieve the effective row
257 (NVL(p_effective_date,g_effective_date)
258 BETWEEN eaat.effective_start_date
259 AND eaat.effective_end_date
260 )
261 OR -- any previous rows
262 (eaat.effective_start_date < NVL(p_effective_date,g_effective_date)
263 )
264 )
265 ORDER BY eaat.effective_start_date DESC; -- effective first
266 --
267 --
268 --
269 CURSOR csr_pqp_asg_attributes_up -- up
270 (p_assignment_id NUMBER
271 ,p_effective_date DATE DEFAULT NULL
272 ) IS
273 SELECT eaat.assignment_attribute_id assignment_attribute_id
274 ,eaat.assignment_id assignment_id
275 ,eaat.effective_start_date effective_start_date
276 ,eaat.effective_end_date effective_end_date
277 ,eaat.tp_is_teacher tp_is_teacher
278 -- SSC: added for head Teacher seconded location for salary scale calculation
279 ,eaat.tp_headteacher_grp_code tp_headteacher_grp_code
280 ,eaat.tp_safeguarded_grade tp_safeguarded_grade
281 ,eaat.tp_elected_pension tp_elected_pension
282 ,eaat.creation_date creation_date
283 -- Added for salary scale changes
284 ,eaat.tp_safeguarded_spinal_point_id tp_sf_spinal_point_id
285 FROM pqp_assignment_attributes_f eaat -- effective aat
286 WHERE eaat.assignment_id = p_assignment_id
287 AND ( -- retrieve the effective row
288 (NVL(p_effective_date,g_effective_date)
289 BETWEEN eaat.effective_start_date
290 AND eaat.effective_end_date
291 )
292 OR -- any future rows
293 (eaat.effective_start_date > NVL(p_effective_date,g_effective_date)
294 )
295 )
296 ORDER BY eaat.effective_start_date ASC; -- effective first
297 --
298 --
299 --
300 CURSOR csr_ele_entry_exists
301 (c_assignment_id NUMBER
302 ,c_element_type_id NUMBER
303 ,c_effective_date DATE
304 )
305 IS
306 SELECT 'X'
307 FROM pay_element_entries_f pee
308 ,pay_element_links_f pel
309 WHERE pee.assignment_id = c_assignment_id
310 AND pee.entry_type = 'E'
311 AND pee.element_link_id = pel.element_link_id
312 AND c_effective_date BETWEEN pee.effective_start_date
313 AND pee.effective_end_date
314 AND pel.element_type_id = c_element_type_id
315 AND c_effective_date BETWEEN pel.effective_start_date
316 AND pel.effective_end_date;
317 --
318 --
319 --
320 CURSOR csr_get_spinal_point
321 (c_assignment_id NUMBER
322 ,c_effective_date DATE
323 )
324 IS
325 SELECT sp.spinal_point
326 FROM per_spinal_points sp
327 ,per_spinal_point_steps_f sps
328 ,per_spinal_point_placements_f spp
329 ,pay_grade_rules_f gr
330 WHERE spp.assignment_id = c_assignment_id
331 AND c_effective_date BETWEEN spp.effective_start_date
332 AND spp.effective_end_date
333 AND sps.step_id = spp.step_id
334 AND c_effective_date BETWEEN sps.effective_start_date
335 AND sps.effective_end_date
336 AND gr.grade_or_spinal_point_id = sps.spinal_point_id
337 AND gr.rate_type = 'SP'
338 AND c_effective_date BETWEEN gr.effective_start_date
339 AND gr.effective_end_date
340 AND sp.spinal_point_id = sps.spinal_point_id;
341 --
342 --
343 --
344 CURSOR csr_get_sf_spinal_point
345 (c_spinal_point_id NUMBER)
346 IS
347 SELECT spinal_point
348 FROM per_spinal_points
349 WHERE spinal_point_id = c_spinal_point_id;
350
351 --
352 --
353 --
354 CURSOR csr_get_eles_frm_rate
355 (c_effective_date DATE
356 ,c_rate_type VARCHAR2
357 )
358 IS
359 SELECT pet.element_type_id
360 FROM pay_element_type_extra_info eei
361 ,pay_element_types_f pet
362 ,hr_lookups hrl
363 WHERE hrl.lookup_type = 'PQP_RATE_TYPE'
364 AND UPPER(hrl.meaning) = UPPER(c_rate_type)
365 AND eei.eei_information1 = hrl.lookup_code
366 AND eei.information_type = 'PQP_UK_RATE_TYPE'
367 AND pet.element_type_id = eei.element_type_id
368 AND (
369 (
370 pet.business_group_id IS NOT NULL
371 AND pet.business_group_id = g_business_group_id
372 )
373 OR (
374 pet.legislation_code IS NOT NULL
375 AND pet.business_group_id IS NULL
376 )
377 OR (
378 pet.legislation_code IS NULL
379 AND pet.business_group_id IS NULL
380 )
381 )
382 AND c_effective_date BETWEEN pet.effective_start_date
383 AND pet.effective_end_date;
384
385 --
386 -- Added for salary scale changes
387 --
388 TYPE r_allowance_eles IS RECORD
389 (element_type_id NUMBER
390 ,salary_scale_code NUMBER
391 ,element_type_extra_info_id NUMBER -- RET : added for changes in
392 -- fetch_allow_eles_frm_udt for
393 -- retention allowance rate calculations
394 );
395
396 TYPE t_allowance_eles IS TABLE OF r_allowance_eles
397 INDEX BY BINARY_INTEGER;
398
399 g_tab_mng_aln_eles t_allowance_eles;
400 g_tab_ret_aln_eles t_allowance_eles;
401 g_tab_tlr_aln_eles t_allowance_eles;
402
403 --
404 --
405 --
406 g_asg_emp_cat_cd VARCHAR2(30);
407 g_ext_emp_cat_cd VARCHAR2(80);
408 FUNCTION get_translate_asg_emp_cat_code
409 (p_asg_emp_cat_cd VARCHAR2
410 ,p_effective_date DATE
411 ) RETURN VARCHAR2;
412 --
413 --
414 --
415 CURSOR csr_asg_details
416 (p_assignment_id NUMBER
417 ,p_effective_date DATE -- Effective Teaching Start Date
418 ) IS
419 SELECT asg.person_id person_id
420 ,asg.assignment_id assignment_id
421 ,asg.business_group_id business_group_id
422 ,asg.effective_start_date start_date
423 ,asg.effective_end_date effective_end_date
424 ,asg.creation_date creation_date
425 ,asg.location_id location_id
426 ,NVL(asg.employment_category,'FT') asg_emp_cat_cd
427 ,'F' ext_emp_cat_cd
428 ,'0000' estb_number
429 ,' ' tp_safeguarded_grade
430 ,asg.assignment_status_type_id status_type_id
431 ,' ' status_type
432 ,to_date('01/01/0001','dd/mm/yyyy') leaver_date
433 ,to_date('01/01/0001','dd/mm/yyyy') restarter_date
434 ,'Y' report_asg
435 ,asg.assignment_id secondary_assignment_id
436 ,asg.effective_start_date teacher_start_date
437 -- added for compatibility with tp4. csrasg_details.
438 ,0 tp_sf_spinal_point_id
439 FROM per_all_assignments_f asg
440 WHERE asg.assignment_id = p_assignment_id
441 AND ( ( p_effective_date BETWEEN asg.effective_start_date
442 AND asg.effective_end_date )
443 OR
444 ( asg.effective_end_date < p_effective_date )
445 )
446 ORDER BY asg.effective_start_date DESC; -- effective row first
447
448 TYPE t_ext_asg_details_type IS TABLE OF csr_asg_details%ROWTYPE
449 INDEX BY BINARY_INTEGER;
450
451 g_ext_asg_details t_ext_asg_details_type;
452 --
453 --
454 --
455 CURSOR csr_grade_definition_rowid
456 (p_assignment_id IN NUMBER
457 ,p_effective_date IN DATE
458 ) IS
459 SELECT pgd.ROWID
460 FROM per_grades pgr
461 ,per_grade_definitions pgd
462 ,per_all_assignments_f asg
463 WHERE pgr.grade_id = asg.grade_id
464 AND pgr.grade_definition_id = pgd.grade_definition_id
465 AND asg.assignment_id = p_assignment_id
466 AND p_effective_date
467 BETWEEN asg.effective_start_date
468 AND asg.effective_end_date;
469 --
470 --
471 --
472 CURSOR csr_membership_no
473 (p_person_id IN NUMBER
474 ,p_business_group_id IN NUMBER
475 ,p_effective_date IN DATE
476 ,p_memb_body_name IN VARCHAR2
477 ,p_memb_type IN VARCHAR2
478 ) IS
479
480 SELECT membership_number
481 FROM per_qualifications_v pq
482 WHERE pq.person_id = p_person_id
483 AND pq.business_group_id = p_business_group_id
484 AND p_effective_date
485 BETWEEN NVL(pq.start_date,p_effective_date)
486 AND NVL(pq.end_date,p_effective_date)
487 -- 4336613 : QUAL_FORM_CHG_3A : modified cursor to accomodate
488 -- qualifications form changes
489 AND
490 (
491 (p_memb_body_name IS NOT NULL
492 AND
493 pq.professional_body_name = p_memb_body_name
494 )
495 OR
496 (p_memb_body_name IS NULL
497 AND
498 pq.professional_body_name IS NULL
499 )
500 OR
501 (p_memb_body_name IS NOT NULL
502 AND
503 pq.professional_body_name IS NULL
504 )
505
506 )
507 AND pq.name = p_memb_type;
508
509
510 -- This cursor returns the lea_number from
511 -- pqp_ext_cross_person_records
512 -- a) M - Master Bg Id
513 CURSOR csr_lea_number
514 IS
515 SELECT lea_number
516 FROM pqp_ext_cross_person_records emd
517 WHERE emd.record_type = 'M'
518 AND emd.ext_dfn_id = ben_ext_thread.g_ext_dfn_id --ENH4
519 AND emd.request_id = g_parent_request_id ;
520 --
521 --
522 --
523 --
524 CURSOR csr_estb_details_by_loc
525 (p_location_code VARCHAR2
526 ,p_business_group_id NUMBER DEFAULT NULL
527 ) IS
528 SELECT loc.business_group_id business_group_id
529 ,loc.location_id location_id
530 ,lei.lei_information1 lea_estb_yn
531 ,lpad(lei.lei_information2,4,'0') estb_number
532 ,lei.lei_information3 estb_name
533 ,lei.lei_information4 estb_type
534 ,lpad(lei.lei_information5,2,'0') school_number
535 ,lei.lei_information6 lea_number
536 FROM hr_location_extra_info lei
537 ,hr_locations_all loc
538 WHERE loc.business_group_id = nvl(p_business_group_id,g_business_group_id)
539 AND loc.location_code = p_location_code
540 AND loc.location_id = lei.location_id
541 AND 'PQP_GB_EDU_ESTB_INFO'= lei.information_type ;
542
543 --
544 -- cursor gives the latest start date for a person
545 -- from the previous results.
546 --
547 CURSOR csr_prev_tp4_results
548 (p_person_id NUMBER
549 ,p_business_group_id NUMBER DEFAULT NULL
550 ) IS
551 SELECT MIN(to_date(rdtl.VAL_13, 'DDMMYY')) prev_start_date
552 FROM ben_ext_rslt rslt
553 ,ben_ext_rslt_dtl rdtl
554 ,ben_ext_rcd drcd
555 ,pqp_extract_attributes pqea
556 WHERE pqea.ext_dfn_type = g_extract_type
557 AND rslt.ext_dfn_id = pqea.ext_dfn_id
558 AND rslt.business_group_id = nvl(p_business_group_id,g_business_group_id) --BG ID
559 AND rslt.ext_stat_cd NOT IN
560 ('F' -- Job Failure
561 ,'R' -- Rejected By User
562 ,'X' -- Executing
563 )
564 AND rdtl.ext_rslt_id = rslt.ext_rslt_id
565 AND drcd.ext_rcd_id = rdtl.ext_rcd_id
566 AND drcd.rcd_type_cd = 'D' -- detail records only
567 AND EXISTS (SELECT 'Y'
568 FROM per_all_people_f per
569 WHERE per.person_id = p_person_id
570 AND per.national_identifier = rdtl.val_04
571 )
572 AND rdtl.val_09 = g_lea_number -- LEA Number
573 AND rslt.eff_dt <= g_effective_run_date ; -- Run_end_date
574
575 --
576 --
577 --
578 FUNCTION chk_tp4_is_teacher_new_starter
579 (p_business_group_id IN NUMBER -- context
580 ,p_effective_date IN DATE -- context
581 ,p_assignment_id IN NUMBER -- context
582 ) RETURN VARCHAR2 ; -- Y or N
583 --
584 --
585 --
586 FUNCTION get_lea_number
587 -- (p_trace IN VARCHAR2 DEFAULT 'N')
588 RETURN VARCHAR2;
589 --
590 --
591 --
592 FUNCTION get_estb_number
593 (p_assignment_id IN NUMBER
594 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
595 )
596 RETURN VARCHAR2;
597 --
598 --
599 --
600 FUNCTION get_originators_title
601 -- (p_trace IN VARCHAR2 DEFAULT 'N')
602 RETURN VARCHAR2;
603 --
604 --
605 --
606 FUNCTION get_header_system_element
607 -- (p_trace IN VARCHAR2 DEFAULT 'N')
608 RETURN VARCHAR2;
609 --
610 --
611 --
612 FUNCTION get_tp4_employment_category
613 (p_assignment_id IN NUMBER
614 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
615 )
616 RETURN VARCHAR2;
617 --
618 --
619 --
620 FUNCTION get_dflex_value
621 (p_value OUT NOCOPY VARCHAR2 -- return value
622 ,p_desc_flex_name IN VARCHAR2 -- Desc Flex Name
623 ,p_column_name IN VARCHAR2 -- Base Table Column Name
624 ,p_effective_date IN DATE DEFAULT NULL -- Defaults to session date
625 ,p_entity_key_name IN VARCHAR2 --
626 ,p_entity_key_value IN VARCHAR2 --
627 ,p_busnsgrp_id IN NUMBER DEFAULT NULL --
628 ,p_entity_busnsgrp_yn IN VARCHAR2 DEFAULT 'N' --
629 ,p_entity_eff_date_yn IN VARCHAR2 DEFAULT 'N' --
630 ) RETURN NUMBER;
631 --
632 --
633 --
634 FUNCTION get_dfee_reference_number
635 (p_assignment_id IN NUMBER
636 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
637 )
638 RETURN VARCHAR2;
639 --
640 --
641 --
642 FUNCTION get_tp4_start_date
643 (p_assignment_id IN NUMBER
644 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
645 )
646 RETURN VARCHAR2;
647 --
648 --
649 --
650 FUNCTION get_flex_segment_value
651 (p_entity_name IN VARCHAR2 -- name of the table holding the values
652 ,p_entity_rowid IN ROWID -- Row Id
653 ,p_segment_col_name IN VARCHAR2 -- Segment column name
654 )
655 RETURN VARCHAR2;
656 --
657 --
658 --
659 FUNCTION get_kflex_value
660 (p_context_id IN NUMBER -- Context Id
661 ,p_flexfield_name IN VARCHAR2 -- Flexfield Name
662 ,p_segment_name IN VARCHAR2 -- Flexfield Segment Name
663 ,p_effective_date IN DATE -- Effective Date
664 )
665 RETURN VARCHAR2;
666 --
667 --
668 --
669 FUNCTION get_tp4_salary_scale
670 (p_assignment_id IN NUMBER
671 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
672 )
673 RETURN VARCHAR2;
674 --
675 --
676 --
677 FUNCTION get_total_number_data_records
678 (p_type IN VARCHAR2 DEFAULT hr_api.g_varchar2
679 -- ,p_trace IN VARCHAR2 DEFAULT 'N'
680 )
681 RETURN VARCHAR2;
682 --
683 --
684 --
685 -- Added for Type 2
686 PROCEDURE set_extract_globals
687 (p_business_group_id IN NUMBER
688 ,p_effective_date IN DATE
689 ,p_assignment_id IN NUMBER
690 );
691 --
692 --
693 -- Added for Type 1
694 PROCEDURE set_run_effective_dates;
695
696 --
697 --
698 -- Added for Type 1
699 PROCEDURE set_pay_proc_events_to_process
700 (p_assignment_id IN NUMBER
701 ,p_status IN VARCHAR2 DEFAULT 'P'
702 ,p_start_date IN DATE DEFAULT NULL
703 ,p_end_date IN DATE DEFAULT NULL
704 );
705
706 --
707 -- Added for Type 1
708 --
709 FUNCTION get_extract_udt_info
710 (p_udt_column_name VARCHAR2
711 ,p_udt_row_name VARCHAR2
712 ,p_effective_date DATE DEFAULT NULL
713 ) RETURN VARCHAR2;
714
715
716 --
717 -- Added this procedure to the header as there was a GSCC
718 -- warning due to the use of DEFAULT values in body.
719 -- WARNING : This procedure is for private use inside the package body only.
720 --
721 PROCEDURE debug
722 (p_trace_message IN VARCHAR2
723 ,p_trace_location IN NUMBER DEFAULT NULL
724 );
725
726 --
727 -- Added this procedure to the header as there was a GSCC
728 -- warning due to the use of DEFAULT values in body.
729 -- WARNING : This procedure is for private use inside the package body only.
730 --
731 PROCEDURE debug_enter
732 (p_proc_name IN VARCHAR2 DEFAULT NULL
733 ,p_trace_on IN VARCHAR2 DEFAULT NULL
734 );
735 --
736 -- Added this procedure to the header as there was a GSCC
737 -- warning due to the use of DEFAULT values in body.
738 -- WARNING : This procedure is for private use inside the package body only.
739 --
740 PROCEDURE debug_exit
741 (p_proc_name IN VARCHAR2 DEFAULT NULL
742 ,p_trace_off IN VARCHAR2 DEFAULT NULL
743 );
744
745 --
746 -- Added this procedure to the header as there was a GSCC
747 -- warning due to the use of DEFAULT values in body.
748 -- WARNING : This procedure is for private use inside the package body only.
749 --
750 PROCEDURE fetch_criteria_establishments
751 (p_estb_details IN csr_estb_details%ROWTYPE);
752
753 --
754 -- Added for salary scale changes
755 --
756 FUNCTION get_udt_id (p_udt_name IN VARCHAR2
757 )
758 RETURN NUMBER;
759
760 FUNCTION get_allow_ele_info (p_assignment_id IN NUMBER
761 ,p_effective_date IN DATE
762 ,p_table_name IN VARCHAR2
763 ,p_row_name IN VARCHAR2
764 ,p_column_name IN VARCHAR2
765 )
766 RETURN NUMBER;
767
768 FUNCTION get_allow_rt_ele_info
769 (p_assignment_id IN NUMBER
770 ,p_effective_date IN DATE
771 ,p_table_name IN VARCHAR2
772 ,p_row_name IN VARCHAR2
773 ,p_column_name IN VARCHAR2
774 ,p_tab_aln_eles IN t_allowance_eles
775 )
776 RETURN t_allowance_eles ;
777
778 PROCEDURE fetch_allow_eles_frm_udt
779 (p_assignment_id IN NUMBER
780 ,p_effective_date IN DATE
781 );
782
783 FUNCTION assignment_has_a_starter_event
784 (p_business_group_id IN NUMBER
785 ,p_assignment_id IN NUMBER
786 ,p_pqp_asg_attributes OUT NOCOPY csr_pqp_asg_attributes_dn%ROWTYPE
787 ,p_asg_details OUT NOCOPY csr_asg_details%ROWTYPE
788 ,p_teacher_start_date OUT NOCOPY DATE
789 ) RETURN VARCHAR2 ;
790
791 -- The procedure checks the flag g_multi_lea_exist
792 -- to check if there are more than one lea with the same lea numebr in tha same BG.
793 -- This flag will be set while setting the globals. and for the first valid assignment
794 -- warning msg will be displayed.
795 -- Toggle the flag as soon as the first warning is raised.
796
797 PROCEDURE warn_if_multi_lea_exist (p_assignment_id IN NUMBER);
798
799 -- The procedure raises a warning if there is no Location defined for LEA
800 -- This will set the flag g_warn_no_location to 'N'
801 -- flag will be set while setting the globals.
802 -- and for the first assignment only warning msg will be displayed.
803 -- Reset the flag as soon as the first warning is raised.
804
805
806 PROCEDURE warn_if_no_loc_exist (p_assignment_id IN NUMBER);
807
808 PROCEDURE print_debug_asg(p_asg_detail IN csr_asg_details%ROWTYPE) ;
809 PROCEDURE print_debug_asg_atr_up(p_pqp_asg_attributes_up IN pqp_gb_t1_pension_extracts.csr_pqp_asg_attributes_up%ROWTYPE) ;
810 PROCEDURE print_debug_asg_atr(p_pqp_asg_attributes IN csr_pqp_asg_attributes_dn%ROWTYPE);
811 FUNCTION get_prev_tp4_result( p_person_id IN NUMBER )RETURN DATE;
812 FUNCTION get_allow_code_rt_ele_info (p_assignment_id IN NUMBER
813 ,p_effective_date IN DATE
814 ,p_table_name IN VARCHAR2
815 ,p_row_name IN VARCHAR2
816 ,p_column_name IN VARCHAR2
817 ,p_tab_aln_eles IN pqp_gb_t1_pension_extracts.t_allowance_eles
818 ,p_allowance_code IN VARCHAR2
819 )
820 RETURN pqp_gb_t1_pension_extracts.t_allowance_eles;
821
822
823 END pqp_gb_tp_pension_extracts;
[Home] [Help]