1 PACKAGE PQP_GB_PSI_FUNCTIONS AUTHID CURRENT_USER AS
2 -- /* $Header: pqpgbpsifunc.pkh 120.22.12020000.1 2012/06/29 05:12:28 appldev ship $ */
3
4 --
5 -- Debug Variables.
6 --
7 g_legislation_code per_business_groups.legislation_code%TYPE := 'GB';
8 g_debug BOOLEAN := hr_utility.debug_enabled;
9 g_effective_date DATE;
10
11 g_proc_name VARCHAR2(61):= 'PQP_GB_PSI_FUNCTIONS.';
12 g_nested_level NUMBER:= pqp_utilities.g_nested_level;
13 g_count NUMBER := 0;
14
15 --
16 -- Global Varibales
17 --
18
19 hr_application_error EXCEPTION;
20 PRAGMA EXCEPTION_INIT(hr_application_error, -20001);
21
22
23 g_debug_timestamps BOOLEAN := FALSE;
24 g_debug_entry_exits_only BOOLEAN := FALSE;
25
26
27 g_business_group_id NUMBER := NULL; -- IMPORTANT TO KEEP NULL
28 g_business_group_id_backup NUMBER := NULL; -- IMPORTANT TO KEEP NULL
29 g_person_id NUMBER := NULL;
30 g_paypoint VARCHAR2(5) := NULL;
31 g_current_run VARCHAR2(10):= NULL;
32 g_cutover_date DATE;
33 g_altkey VARCHAR2(12):= NULL;
34 g_assignment_id NUMBER := NULL;
35 g_assignment_number VARCHAR2(30):= NULL;
36 g_debug_flag VARCHAR2(1);
37 g_debug_enable_mode VARCHAR2(10) := NULL;
38 g_extract_type VARCHAR2(10) := NULL;
39 g_dfn_name VARCHAR2(10) := NULL;
40 g_output_name VARCHAR2(110) := NULL;
41 g_sequence_number VARCHAR2(4);
42 g_prev_event_dtl_rec ben_ext_person.t_detailed_output_tab_rec;
43
44
45 g_prev_assignment_id NUMBER := NULL;
46 g_prev_effective_date DATE;
47 g_prev_inclusion_flag VARCHAR2(1) := 'Y';
48 g_person_dtl per_all_people_f%ROWTYPE;
49 g_assignment_dtl per_all_assignments_f%ROWTYPE;
50
51 -- 115.44.11511.1
52 -- For Penserver Performance
53 -- SE code benxthrd.pkb will use this variables to build the dynamic sql
54
55 g_caller VARCHAR2(10);
56 g_last_app_date DATE;
57
58 --115.48: Bug 7291713
59 /*
60 --115.47
61 g_bas_eff_date VARCHAR2(30);
62 */
63
64 -- added by kkarri
65 -- this contains the value of the pension scheme entered in the
66 -- assignment flexfield. this value is set in basic criteria
67 -- after calling check_employee_eligibility
68 g_pension_scheme VARCHAR2(80);
69
70 g_asg_membership_col VARCHAR2(20);
71 g_asg_membership_context VARCHAR2(80);
72 ------------------------------------------
73 -- Added for reprocess logic
74 TYPE t_date IS TABLE OF DATE
75 INDEX BY BINARY_INTEGER;
76 g_min_effective_date t_date;
77 g_min_eff_date_exists VARCHAR2(10);
78 g_effective_start_date DATE;
79 g_effective_end_date DATE;
80
81 g_salary_ended_today VARCHAR2(1) := 'N';
82 g_allowance_has_end_dated VARCHAR2(1);
83 g_is_terminated VARCHAR2(1) := 'N';
84 g_curr_element_type_id NUMBER;
85 g_curr_element_entry_id NUMBER;
86
87 -- Concurrent program
88 g_wait_interval NUMBER := 60; -- seconds
89 g_max_wait NUMBER := 0; -- Meaning no time out
90 g_reference_extract VARCHAR2(30);
91
92
93 -- =============================================================================
94 -- Used to maintain the penser extract defination names
95 -- =============================================================================
96 TYPE r_ext_dfn_names IS RECORD
97 (extract_name VARCHAR2(160)
98 ,extract_code VARCHAR2(10)
99 );
100
101 TYPE t_ext_dfn_names is Table OF r_ext_dfn_names
102 INDEX BY BINARY_INTEGER;
103
104 g_code_ext_names t_ext_dfn_names;
105 g_cutover_ext_names t_ext_dfn_names;
106 g_periodic_ext_names t_ext_dfn_names;
107
108
109 -- =============================================================================
110 -- Used to maintain the penser extract process details
111 -- =============================================================================
112
113 TYPE r_ext_dtls IS RECORD
114 (extract_name VARCHAR2(160)
115 ,extract_code VARCHAR2(10)
116 ,short_name VARCHAR2(80)
117 ,request_id VARCHAR2(10)
118 ,extract_rslt_id NUMBER
119 );
120
121 TYPE t_ext_dtls is Table OF r_ext_dtls
122 INDEX BY BINARY_INTEGER;
123
124 g_ext_dtls t_ext_dtls;
125
126
127
128 TYPE r_error_dtl IS RECORD
129 (
130 extract_type VARCHAR2(30)
131 ,error_number NUMBER
132 ,error_text VARCHAR2(32000)
133 ,token1 VARCHAR2(32000)
134 ,token2 VARCHAR2(32000)
135 ,token3 VARCHAR2(32000)
136 ,token4 VARCHAR2(32000)
137 ,assignment_id NUMBER
138 ,ext_rslt_id NUMBER
139 );
140
141 TYPE t_error_collection IS TABLE OF r_error_dtl
142 INDEX BY BINARY_INTEGER;
143
144 g_errors t_error_collection;
145 g_warnings t_error_collection;
146
147 TYPE t_varchar2 IS TABLE OF VARCHAR2(100)
148 INDEX BY BINARY_INTEGER;
149
150 g_employer_code t_varchar2;
151
152 ---------------
153
154 -- moved to pqp_utilities
155 /*
156 TYPE r_config_values IS RECORD (
157 configuration_value_id NUMBER,
158 pcv_information1 pqp_configuration_values.pcv_information1%TYPE,
159 pcv_information2 pqp_configuration_values.pcv_information2%TYPE,
160 pcv_information3 pqp_configuration_values.pcv_information3%TYPE,
161 pcv_information4 pqp_configuration_values.pcv_information4%TYPE,
162 pcv_information5 pqp_configuration_values.pcv_information5%TYPE,
163 pcv_information6 pqp_configuration_values.pcv_information6%TYPE,
164 pcv_information7 pqp_configuration_values.pcv_information7%TYPE,
165 pcv_information8 pqp_configuration_values.pcv_information8%TYPE,
166 pcv_information9 pqp_configuration_values.pcv_information9%TYPE,
167 pcv_information10 pqp_configuration_values.pcv_information10%TYPE,
168 pcv_information11 pqp_configuration_values.pcv_information11%TYPE,
169 pcv_information12 pqp_configuration_values.pcv_information12%TYPE,
170 pcv_information13 pqp_configuration_values.pcv_information13%TYPE,
171 pcv_information14 pqp_configuration_values.pcv_information14%TYPE,
172 pcv_information15 pqp_configuration_values.pcv_information15%TYPE,
173 pcv_information16 pqp_configuration_values.pcv_information16%TYPE,
174 pcv_information17 pqp_configuration_values.pcv_information17%TYPE,
175 pcv_information18 pqp_configuration_values.pcv_information18%TYPE,
176 pcv_information19 pqp_configuration_values.pcv_information19%TYPE,
177 pcv_information20 pqp_configuration_values.pcv_information20%TYPE
178 );
179
180 TYPE t_config_values IS TABLE OF r_config_values
181 INDEX BY BINARY_INTEGER;
182
183
184 g_assign_category_mapping t_config_values;
185 */
186
187 g_assign_category_mapping pqp_utilities.t_config_values;
188 g_pension_scheme_mapping pqp_utilities.t_config_values;
189
190
191 ---------------added by kkarri----------------
192 c_highest_date CONSTANT DATE := hr_api.g_eot;
193
194 --g_penserver_contract_type VARCHAR2(1);
195 --g_contract_type_effective_date DATE;
196 --g_contract_type VARCHAR2(30);
197
198 g_pay_proc_evt_tab ben_ext_person.t_detailed_output_table;
199
200 g_salary_ele_end_date DATE := hr_api.g_eot;
201 -- this is used to makr that current event date has a salary element end event
202 g_non_salary_ele_end_date DATE := hr_api.g_eot;
203 -- this is used to mark that there is no salary element end event on current event date
204
205 g_sal_chg_event_exists VARCHAR2(1);
206 -- this is used to mark that there is a salary change event on current date.
207
208 g_salary_started VARCHAR2(1);
209 -- this is mark that the salary has started and further events will be processed
210 g_salary_ended VARCHAR2(1);
211 -- this is mark that the salary has ended and no further event wud be processed
212 g_salary_start_date DATE; --
213
214 g_salary_end_date DATE;--
215
216 TYPE t_varchar30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
217 g_dated_tables t_varchar30;
218
219 CURSOR csr_assignment_status
220 (
221 p_assignment_status_type_id NUMBER
222 )
223 IS
224 SELECT DECODE(pay_system_status,'D','DO NOT PROCESS','P','PROCESS')
225 ,per_system_status
226 FROM per_assignment_status_types
227 WHERE ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
228 AND primary_flag = 'P';
229
230 -- cursor to check if the change is on FTE
231 CURSOR csr_is_fte_abv
232 (
233 p_assignment_budget_value_id NUMBER
234 )
235 IS
236 SELECT 'Y'
237 FROM PER_ASSIGNMENT_BUDGET_VALUES_F
238 WHERE assignment_budget_value_id = p_assignment_budget_value_id
239 AND UNIT = 'FTE'
240 AND ROWNUM = 1 ;
241
242 --Bug 7611963: Cusor to fetch element end date.
243 Cursor csr_get_ele_end_date(p_element_entry_id IN NUMBER)
244 Is
245 Select max(effective_end_date)
246 From pay_element_entries_f
247 Where element_entry_id = p_element_entry_id;
248
249 g_retro_event_date_reported BOOLEAN := TRUE;
250
251 -- ----------------------------------------------------------------------------
252 -- |------------------------< init_st_end_date_glob --------------------------|
253 -- ----------------------------------------------------------------------------
254 PROCEDURE init_st_end_date_glob;
255
256 -- ----------------------------------------------------------------------------
257 -- |---------------------< get_start_end_date >------------------------|
258 -- ----------------------------------------------------------------------------
259 FUNCTION get_start_end_date
260 (
261 p_assignment_id NUMBER
262 ,p_business_group_id NUMBER
263 ,p_effective_date DATE
264 ,p_start_date OUT NOCOPY DATE
265 ,p_end_date OUT NOCOPY DATE
266 )RETURN NUMBER;
267
268 -- ----------------------------------------------------------------------------
269 -- |-------------------------< get_contract_type >-----------------------------|
270 -- ----------------------------------------------------------------------------
271 FUNCTION get_contract_type
272 (
273 p_assignment_id NUMBER
274 ,p_business_group_id NUMBER
275 ,p_effective_date IN DATE
276 ,p_contract_type OUT NOCOPY VARCHAR2
277 ) RETURN NUMBER;
278
279 ------------------------------------------------------------------------------
280 --|-------------------------< get_notional_pay >-----------------------|
281 -- ----------------------------------------------------------------------------
282 FUNCTION get_notional_pay
283 (
284 p_assignment_id IN NUMBER
285 ,p_business_group_id IN NUMBER
286 ,p_effective_date IN DATE
287 ,p_name IN VARCHAR2
288 ,p_rt_element IN VARCHAR2
289 ,p_rate IN OUT NOCOPY NUMBER
290 ,p_custom_function IN VARCHAR2 DEFAULT NULL
291 ,p_allowance_code IN VARCHAR2 DEFAULT NULL
292 ,p_allowance_pet_id IN NUMBER DEFAULT NULL
293 ) RETURN NUMBER;
294
295 -- ----------------------------------------------------------------------------
296 -- |------------------------< get_actual_pay >--------------------------|
297 -- ----------------------------------------------------------------------------
298 FUNCTION get_actual_pay
299 (
300 p_assignment_id IN NUMBER
301 ,p_notional_pay IN NUMBER
302 ,p_effective_date IN DATE
303 ,p_output OUT NOCOPY VARCHAR2
304 )RETURN NUMBER;
305
306 ------------------------------------------------
307 --
308 -- For fetching person details for person_id
309 --
310 CURSOR csr_get_person_dtl
311 ( p_business_group_id NUMBER
312 ,p_effective_date DATE
313 ,p_person_id NUMBER
314 ) IS
315 select *
316 from per_all_people_f papf
317 where papf.business_group_id = p_business_group_id
318 and papf.person_id = p_person_id
319 and p_effective_date
320 between papf.effective_start_date
321 and papf.effective_end_date;
322
323
324 --
325 -- For fetching assignment details
326 --
327
328 -- old
329 /*
330 CURSOR csr_get_assignment_dtl
331 ( p_business_group_id NUMBER
332 ,p_effective_date DATE
333 ,p_assignment_id NUMBER
334 ) IS
335 select *
336 from per_all_assignments_f paaf
337 where paaf.business_group_id = p_business_group_id
338 and paaf.assignment_id = p_assignment_id
339 and paaf.assignment_type = 'E'
340 and paaf.employment_category IS NOT NULL
341 and p_effective_date
342 between paaf.effective_start_date
343 and paaf.effective_end_date;
344 */
345
346 -- new
347 -- 1) for periodic : till final_process_date
348 CURSOR csr_get_assignment_dtl_per
349 ( p_business_group_id NUMBER
350 ,p_effective_date DATE
351 ,p_assignment_id NUMBER
352 ) IS
353 select paaf.*
354 from per_all_assignments_f paaf --, per_periods_of_service PPS
355 where paaf.business_group_id = p_business_group_id
356 and paaf.assignment_id = p_assignment_id
357 and paaf.assignment_type = 'E'
358 -- and pps.person_id = paaf.person_id
359 -- and pps.business_group_id = p_business_group_id
360 and paaf.employment_category IS NOT NULL
361 and p_effective_date
362 between paaf.effective_start_date
363 and paaf.effective_end_date;
364 -- and p_effective_date
365 -- between pps.date_start
366 -- and NVL(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY'));
367
368
369 -- 2) for cutover : till actual_termination_date
370 CURSOR csr_get_assignment_dtl_cut
371 ( p_business_group_id NUMBER
372 ,p_effective_date DATE
373 ,p_assignment_id NUMBER
374 ) IS
375 select paaf.*
376 from per_all_assignments_f paaf, per_periods_of_service PPS
377 where paaf.business_group_id = p_business_group_id
378 and paaf.assignment_id = p_assignment_id
379 and paaf.assignment_type = 'E'
380 and pps.person_id = paaf.person_id
381 and pps.business_group_id = p_business_group_id
382 and paaf.employment_category IS NOT NULL
383 and p_effective_date
384 between paaf.effective_start_date
385 and paaf.effective_end_date
386 and p_effective_date
387 between pps.date_start
388 and NVL(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'));
389
390
391
392 --
393 -- Indicator whether element_type_id of a pension scheme
394 -- is present for this assignment or not.
395 -- Being used for checking if person is in a particular scheme
396 --
397 CURSOR csr_partnership_scheme_flag
398 (p_business_group_id NUMBER
399 ,p_effective_date DATE
400 ,p_assignment_id NUMBER
401 ,p_element_type_id NUMBER
402 )
403 IS
404 select 'Y' from dual
405 where
406 (select pee.element_type_id
407 from pay_element_entries_f pee ,pay_element_links_f pel
408 where pee.element_link_id = pel.element_link_id
409 and pel.business_group_id = p_business_group_id
410 and pee.assignment_id = p_assignment_id
411 and pee.element_type_id = p_element_type_id
412 and p_effective_date between
413 pee.effective_start_date and pee.effective_end_date
414 and rownum = 1
415 ) IS NOT NULL;
416
417 /*
418 --
419 -- fetch penserver assignment category code
420 --
421 CURSOR csr_assignment_category
422 (p_business_group_id NUMBER
423 ,p_effective_date DATE
424 ,p_assignment_id NUMBER
425 )
426 IS
427 select pcv.pcv_information2
428 from per_all_assignments_f paaf, PQP_CONFIGURATION_VALUES pcv
429 where paaf.assignment_id = p_assignment_id
430 and pcv.pcv_information1 = paaf.employment_category
431 and paaf.business_group_id = p_business_group_id
432 and pcv_information_category = 'PQP_GB_PENSERVER_EMPLYMT_TYPE'
433 and p_effective_date
434 between paaf.effective_start_date
435 and paaf.effective_end_date;
436 */
437
438
439 --
440 -- penserver last_hire_date indicator
441 -- returns a 'Y' if the person has been employed
442 -- more than 3 months ago from the current date
443 CURSOR csr_last_hire_date_indicator
444 (p_business_group_id NUMBER
445 ,p_effective_date DATE
446 ,p_person_id NUMBER
447 )
448 IS
449
450 select 'Y'
451 from dual
452 where
453 (select PPS.DATE_START -- DECODE(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,NULL)
454 from per_all_people_f PER, per_periods_of_service PPS
455 where per.person_id = p_person_id
456 and pps.person_id = p_person_id
457 and p_effective_date
458 between per.effective_start_date
459 and NVL(per.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
460 and p_effective_date
461 between pps.date_start
462 and NVL(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY'))
463 )
464 <=
465 (
466 select add_months(p_effective_date,-3) from dual
467 );
468
469 FUNCTION check_debug
470 (p_business_group_id IN VARCHAR2 -- context
471 )
472 RETURN boolean;
473
474 -------------debug
475 PROCEDURE DEBUG(
476 p_trace_message IN VARCHAR2
477 ,p_trace_location IN NUMBER DEFAULT NULL
478 );
479
480 -------------debug_enter
481 PROCEDURE debug_enter(
482 p_proc_name IN VARCHAR2 DEFAULT NULL
483 ,p_trace_on IN VARCHAR2 DEFAULT NULL
484 );
485
486 -------------debug_exit
487 PROCEDURE debug_exit(
488 p_proc_name IN VARCHAR2 DEFAULT NULL
489 ,p_trace_off IN VARCHAR2 DEFAULT NULL
490 );
491
492 PROCEDURE debug_others(
493 p_proc_name IN VARCHAR2
494 ,p_proc_step IN NUMBER DEFAULT NULL
495 );
496
497
498 -- ----------------------------------------------------------------------------
499 -- |------------------------< check_employee_eligibility >-------------------|
500 -- ----------------------------------------------------------------------------
501 FUNCTION check_employee_eligibility
502 (p_business_group_id IN NUMBER
503 ,p_assignment_id IN NUMBER
504 ,p_effective_date IN DATE
505 ,p_chg_value OUT NOCOPY VARCHAR2 -- the scheme name entered.
506 ) RETURN VARCHAR2; -- Y or N
507
508 FUNCTION chk_penserver_basic_criteria
509 (p_business_group_id IN NUMBER
510 ,p_effective_date IN DATE
511 ,p_assignment_id IN NUMBER
512 ,p_person_dtl OUT NOCOPY per_all_people_f%rowtype
513 ,p_assignment_dtl OUT NOCOPY per_all_assignments_f%rowtype
514 ) RETURN VARCHAR2;
515
516 /*
517 PROCEDURE get_config_type_values
518 (
519 p_configuration_type IN VARCHAR2
520 ,p_business_group_id IN NUMBER
521 ,p_legislation_code IN VARCHAR2
522 ,p_tab_config_values OUT NOCOPY t_config_values
523 );
524 */
525
526 PROCEDURE set_shared_globals
527 (p_business_group_id IN NUMBER
528 ,p_paypoint OUT NOCOPY VARCHAR2
529 ,p_cutover_date OUT NOCOPY VARCHAR2
530 ,p_ext_dfn_id OUT NOCOPY NUMBER
531 );
532
533
534 --
535 -- function to store error/warnings into collection
536 --
537 PROCEDURE store_extract_exceptions
538 (p_extract_type IN VARCHAR2 -- global/interface name
539 ,p_error_number IN NUMBER
540 ,p_error_text IN VARCHAR2
541 ,p_token1 IN VARCHAR2 DEFAULT NULL
542 ,p_token2 IN VARCHAR2 DEFAULT NULL
543 ,p_token3 IN VARCHAR2 DEFAULT NULL
544 ,p_token4 IN VARCHAR2 DEFAULT NULL
545 ,p_error_warning_flag IN VARCHAR2 -- E (error) / W(warning)
546 );
547
548 --
549 -- function to raise error/warnings that are stored in collection
550 --
551
552 PROCEDURE raise_extract_exceptions
553 (p_extract_type IN VARCHAR2 DEFAULT 'DE'
554 );
555
556 --
557 -- function to return altkey of a person assignment
558 --
559
560 FUNCTION altkey
561 --(p_assignment_number IN VARCHAR2 -- context
562 --,p_paypoint IN VARCHAR2 -- context
563 --)
564 RETURN VARCHAR2;
565
566 --
567 -- function to return paypoint of a person assignment
568 --
569 FUNCTION paypoint
570 (p_business_group_id IN VARCHAR2 -- context
571 )
572 RETURN VARCHAR2;
573
574 --
575 -- function to return employer_code of a person assignment
576 --
577 FUNCTION employer_code
578 (p_business_group_id NUMBER
579 ,p_effective_date DATE
580 ,p_assignment_id NUMBER
581 ) RETURN VARCHAR2;
582
583 -- =============================================================================
584 -- ~ PQP_Penserver_Extract: This is called by the conc. program
585 -- ~ to run Penserver extracts and is basically a
586 -- ~ wrapper around the benefits conc. program Extract Process.
587 -- This function will launch new concurrent requests for each extract submission
588 -- =============================================================================
589
590 PROCEDURE PQP_Penserver_Extract
591 (errbuf OUT NOCOPY VARCHAR2
592 ,retcode OUT NOCOPY VARCHAR2
593 ,p_benefit_action_id IN NUMBER
594 ,p_business_group_id IN NUMBER
595 ,p_execution_mode IN VARCHAR2 -- GEN/DBG/SET
596 ,p_execution_mode_type IN VARCHAR2
597 ,p_extract_type IN VARCHAR2 -- CUT/PED/CODE
598 ,p_dfn_name IN VARCHAR2 -- ALL/BDI/ADI/SDI/EDI/ACI/BCI/GCI/LCI
599 ,p_start_date IN VARCHAR2
600 ,p_eff_date IN VARCHAR2
601 ,p_submit_request_y_n IN VARCHAR2 default 'N'
602 ,p_concurrent_request_id IN NUMBER DEFAULT NULL
603 ,p_year_end_close IN VARCHAR2 default 'N' -- /* Nuvos Changes */
604 ,p_short_time_hours_single IN VARCHAR2 default 'INCLUDE' -- For Bug 7010282
605 );
606
607
608
609 -- =============================================================================
610 -- This procedure gets control totals information
611 -- =============================================================================
612 PROCEDURE Get_Penserver_CntrlTtl_Process
613 (errbuf OUT NOCOPY VARCHAR2
614 ,retcode OUT NOCOPY VARCHAR2
615 ,p_extract_type IN VARCHAR2 DEFAULT NULL
616 ,p_parent_request_id IN NUMBER DEFAULT NULL
617 ,p_parent_selected IN VARCHAR2 DEFAULT NULL
618 ,p_ext_bdi_rslt_id IN NUMBER DEFAULT NULL
619 ,p_ext_adi_rslt_id IN NUMBER DEFAULT NULL
620 ,p_ext_sehi_rslt_id IN NUMBER DEFAULT NULL
621 ,p_ext_sahi_rslt_id IN NUMBER DEFAULT NULL
622 ,p_ext_ehi_rslt_id IN NUMBER DEFAULT NULL
623 ,p_ext_ahi_rslt_id IN NUMBER DEFAULT NULL
624 ,p_ext_bhi_rslt_id IN NUMBER DEFAULT NULL
625 ,p_ext_wps_rslt_id IN NUMBER DEFAULT NULL
626 ,p_ext_pthi_rslt_id IN NUMBER DEFAULT NULL
627 ,p_ext_sthi_rslt_id IN NUMBER DEFAULT NULL
628 ,p_ext_sthai_rslt_id IN NUMBER DEFAULT NULL
629 ,p_business_group_id IN NUMBER
630 ,p_year_end_close IN VARCHAR2 default 'N' -- /* Nuvos Changes */
631 );
632
633 -- =============================================================================
634 -- Record : rec_cntrl_tot
635 -- to get total controls information
636 -- =============================================================================
637 TYPE rec_cntrl_tot IS RECORD
638 ( pay_point VARCHAR2(6)
639 ,file_extract_date VARCHAR2(16)
640 ,seq_num VARCHAR2(3)
641 ,basic_cnt VARCHAR2(10)
642 ,serv_hist_cnt VARCHAR2(10)
643 ,earn_hist_cnt VARCHAR2(10)
644 ,earn_hist_tot_WPS VARCHAR2(16)
645 ,sal_hist_cnt VARCHAR2(10)
646 ,sal_hist_tot_national_pay VARCHAR2(16)
647 ,allw_hist_rec_cnt VARCHAR2(10)
648 ,allw_hist_tot_allw_rate VARCHAR2(16)
649 ,bonus_hist_rec_cnt VARCHAR2(10)
650 ,bonus_hist_tot_bonus_amt VARCHAR2(16)
651 ,WPS_contrbt_hist_rec_cnt VARCHAR2(10)
652 ,WPS_contrbt_hist_tot_perc VARCHAR2(16)
653 ,AVC_hist_rec_cnt VARCHAR2(10)
654 ,EECONT_tot VARCHAR2(16)
655 ,other_benef_rec_cnt VARCHAR2(10)
656 ,PUP_tot VARCHAR2(16)
657 ,prt_tm_hr_hist_rec_cnt VARCHAR2(10)
658 ,prt_tm_hr_hist_tot_pthrs VARCHAR2(16)
659 ,srt_tm_hr_hist_sing_rec_cnt VARCHAR2(10)
660 ,srt_tm_hr_hist_sing_tot_hr_var VARCHAR2(16)
661 ,srt_tm_hr_hist_accu_rec_cnt VARCHAR2(10)
662 ,srt_tm_hr_hist_accu_tot_hr_var VARCHAR2(16)
663 ,event_det_tot_rec VARCHAR2(10)
664 ,event_det_tot_amt VARCHAR2(16)
665 ,remarks_interface_tot_rec VARCHAR2(10)
666 ,addr_data_tot_rec VARCHAR2(10)
667 ,benef_det_tot_rec VARCHAR2(10)
668 ,pay_hist_cnt VARCHAR2(10) -- For Nuvos changes
669 ,pay_hist_tot_EARN VARCHAR2(16)
670 ,pay_hist_tot_DEDS VARCHAR2(16)
671 ,year_end_close VARCHAR2(4)
672 ,pay_per_end_date VARCHAR2(10)
673 );
674 TYPE t_cntrl_tot IS TABLE OF rec_cntrl_tot
675 INDEX BY BINARY_INTEGER;
676
677 -- =============================================================================
678 -- Record : rec_allowance_codes
679 -- to get allowance code information
680 -- =============================================================================
681 TYPE rec_allowance_codes IS RECORD
682 ( pay_point VARCHAR2(6)
683 ,allowance_code VARCHAR2(20)
684 ,allowance_descr VARCHAR2(60)
685 ,pension_flag VARCHAR2(1)
686 ,industrial_flag VARCHAR2(1)
687 ,spread_bonus_flag VARCHAR2(1)
688 ,filler1 VARCHAR2(16)
689 ,basic_pay_reckonable VARCHAR2(1)
690 ,pre_75_reckonable VARCHAR2(1)
691 ,filler2 VARCHAR2(79)
692 );
693 TYPE t_allowance_codes IS TABLE OF rec_allowance_codes
694 INDEX BY BINARY_INTEGER;
695
696 -- =============================================================================
697 -- Record : rec_bonus_codes
698 -- to get bonus code information
699 -- =============================================================================
700 TYPE rec_bonus_codes IS RECORD
701 ( pay_point VARCHAR2(6)
702 ,bonus_code VARCHAR2(20)
703 ,bonus_descr VARCHAR2(60)
704 ,pension_flag VARCHAR2(1)
705 ,industrial_flag VARCHAR2(1)
706 ,filler1 VARCHAR2(16)
707 ,basic_pay_reckonable VARCHAR2(1)
708 ,pre_75_reckonable VARCHAR2(1)
709 ,filler2 VARCHAR2(86)
710 );
711 TYPE t_bonus_codes IS TABLE OF rec_bonus_codes
712 INDEX BY BINARY_INTEGER;
713
714 -- =============================================================================
715 -- Cursor - csr_get_extra_allow_information
716 -- Information type is passed as parameter
717 -- =============================================================================
718 CURSOR csr_get_extra_allow_info
719 (p_from_date IN DATE
720 ,p_to_date IN DATE) IS
721 SELECT element_name,
722 eei_information2 code,
723 eei_information3 description,
724 eei_information4 pension_flag,
725 eei_information5 industrial_flag,
726 eei_information6 spread_bonus_flag,
727 eei_information7 basic_pay_reckonable,
728 eei_information8 pre_75_reckonable
729 FROM pay_element_type_extra_info petei,
730 pay_element_types_f petf
731 WHERE information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO'
732 AND petei.element_type_id = petf.element_type_id
733 AND petei.eei_information2 IS NOT NULL
734 AND ((p_from_date BETWEEN petf.effective_start_date AND petf.effective_end_date)
735 OR
736 (p_to_date BETWEEN petf.effective_start_date AND petf.effective_end_date));
737
738
739 -- =============================================================================
740 -- Cursor - csr_get_extra_bonus_information
741 -- Information type is passed as parameter
742 -- =============================================================================
743 CURSOR csr_get_extra_bonus_info
744 ( p_from_date IN DATE
745 ,p_to_date IN DATE) IS
746 SELECT element_name,
747 eei_information2 code,
748 eei_information3 description,
749 eei_information4 pension_flag,
750 eei_information5 industrial_flag,
751 eei_information6 basic_pay_reckonable,
752 eei_information7 pre_75_reckonable
753 FROM pay_element_type_extra_info petei,
754 pay_element_types_f petf
755 WHERE information_type = 'PQP_GB_PENSERV_BONUS_INFO'
756 AND petei.element_type_id = petf.element_type_id
757 AND petei.eei_information2 IS NOT NULL
758 AND ((p_from_date BETWEEN petf.effective_start_date AND petf.effective_end_date)
759 OR
760 (p_to_date BETWEEN petf.effective_start_date AND petf.effective_end_date));
761
762
763 -- =============================================================================
764 -- Cursor - csr_debug_enable_mode
765 -- =============================================================================
766 CURSOR csr_debug_enable_mode IS
767 SELECT * -- argument3
768 FROM fnd_concurrent_requests
769 WHERE request_id =
770 (SELECT req.parent_request_id
771 FROM fnd_concurrent_requests req, fnd_concurrent_programs con
772 WHERE request_id = fnd_global.conc_request_id
773 AND con.concurrent_program_id = req.concurrent_program_id);
774
775
776 -- =============================================================================
777 -- Cursor - csr_debug_enable_mode_parent
778 -- =============================================================================
779 CURSOR csr_debug_enable_mode_parent IS
780 SELECT * -- req.parent_request_id
781 FROM fnd_concurrent_requests req
782 WHERE request_id = fnd_global.conc_request_id;
783
784
785 -- =============================================================================
786 -- Cursor - csr_get_elements_of_info_type
787 -- Information type is passed as parameter
788 -- Input Value is mandatory
789 -- =============================================================================
790 CURSOR csr_get_elements_of_info_type
791 (c_information_type IN VARCHAR2
792 ,c_input_value IN VARCHAR2 DEFAULT 'PAY VALUE'
793 ) IS
794 SELECT distinct(petei.element_type_id)
795 ,pet.element_name
796 ,pet.processing_type
797 ,piv.input_value_id
798 ,petei.eei_information1
799 ,petei.eei_information2
800 ,petei.eei_information3
801 ,petei.eei_information4
802 ,petei.eei_information5
803 ,petei.eei_information6
804 ,petei.eei_information7
805 ,petei.eei_information8
806 ,petei.eei_information9
807 ,petei.eei_information10
808 ,pet.retro_summ_ele_id -- retro element type ID -- 115.33 (1)
809 FROM pay_element_type_extra_info petei
810 ,pay_element_types_f pet
811 ,pay_input_values_f piv
812 ,pay_input_values_f piv2
813 WHERE petei.information_type = c_information_type
814 AND pet.element_type_id = petei.element_type_id
815 AND piv.element_type_id = pet.element_type_id
816 AND piv2.element_type_id = pet.element_type_id
817 AND UPPER(piv2.NAME) = UPPER(c_input_value)
818 AND UPPER(piv.NAME) = 'PAY VALUE' ;
819
820
821 -- =============================================================================
822 -- Cursor - csr_get_elements_of_info_type
823 -- Information type is passed as parameter
824 -- no restriction on Input value
825 -- =============================================================================
826 CURSOR csr_ele_info_type_no_inp_val
827 (c_information_type IN VARCHAR2
828 ) IS
829 SELECT distinct(petei.element_type_id)
830 ,pet.element_name
831 ,pet.processing_type
832 ,pet.element_type_id ele_type_id
833 ,petei.eei_information1
834 ,petei.eei_information2
835 ,petei.eei_information3
836 ,petei.eei_information4
837 ,petei.eei_information5
838 ,petei.eei_information6
839 ,petei.eei_information7
840 ,petei.eei_information8
841 ,petei.eei_information9
842 ,petei.eei_information10
843 ,pet.retro_summ_ele_id -- retro element type ID -- 115.33 (1)
844 FROM pay_element_type_extra_info petei
845 ,pay_element_types_f pet
846 WHERE petei.information_type = c_information_type
847 AND pet.element_type_id = petei.element_type_id;
848
849
850 -- =============================================================================
851 -- Cursor - csr_get_elements_of_info_type
852 -- Information type is passed as parameter
853 -- =============================================================================
854 CURSOR csr_get_element_type_id(c_element_entry_id IN NUMBER) IS
855 SELECT element_type_id
856 FROM pay_element_entries_f
857 WHERE element_entry_id = c_element_entry_id
858 AND rownum=1;
859
860
861 -- =============================================================================
862 -- Used to maintain element information
863 -- =============================================================================
864
865 TYPE t_elements_of_info_type is Table OF csr_get_elements_of_info_type%rowtype
866 INDEX BY BINARY_INTEGER;
867
868 g_elements_of_info_type t_elements_of_info_type;
869
870
871 -- =============================================================================
872 -- Used to maintain element_entry_id and element_type_id collection
873 -- =============================================================================
874
875 TYPE r_elements_processed IS RECORD
876 (element_type_id NUMBER
877 ,inclusion_flag VARCHAR2(1) -- Y/N
878 );
879 TYPE t_elements_processed is Table OF r_elements_processed
880 INDEX BY BINARY_INTEGER;
881
882 g_elements_processed t_elements_processed;
883
884
885
886 CURSOR csr_get_asg_act_id
887 ( p_assignment_id NUMBER
888 ,p_date_earned DATE
889 )
890 IS
891 SELECT /*+ ordered use_nl(PAA PPA)
892 index (PAA PAY_ASSIGNMENT_ACTIONS_N1)
893 index (PAA PAY_ASSIGNMENT_ACTIONS_N51)
894 index (PPA PAY_PAYROLL_ACTIONS_PK)*/
895 paa.assignment_action_id -- max(paa.assignment_action_id)
896 -- no longer using max, now pick all assignment runs
897 FROM pay_assignment_actions paa
898 ,pay_payroll_actions ppa
899 WHERE paa.assignment_id = p_assignment_id
900 AND ppa.action_status = 'C'
901 AND paa.action_status = 'C'
902 AND paa.payroll_action_id = ppa.payroll_action_id
903 AND ppa.date_earned = p_date_earned
904 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
905 AND paa.source_action_id IS NOT NULL -- pick the assignment_action_id which has run_results
906 order by assignment_action_id desc ; -- pick from highest, descending
907
908 -- Added as part of 115.33 (2)
909 CURSOR csr_get_asg_act_id_retro
910 ( p_assignment_id NUMBER
911 ,p_date_earned DATE
912 )IS
913
914 SELECT paa.assignment_action_id -- min(paa.assignment_action_id)
915 -- ppa.date_earned
916 FROM pay_assignment_actions paa
917 ,pay_payroll_actions ppa
918 WHERE paa.assignment_id = p_assignment_id
919 AND ppa.action_status = 'C'
920 AND paa.action_status = 'C'
921 AND paa.payroll_action_id = ppa.payroll_action_id
922 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
923 AND ppa.date_earned = p_date_earned
924 AND paa.source_action_id IS NULL
925 ORDER BY assignment_action_id desc ; -- pick from highest, descending
926 -- ORDER BY assignment_action_id ;
927
928
929 -- get all assignment_action_id, source_action_id
930 CURSOR csr_get_all_asg_act_id
931 ( p_assignment_id NUMBER
932 ,p_date_earned DATE
933 ,p_element_entry_id NUMBER
934 )
935 IS
936 SELECT paa.assignment_action_id, paa.source_action_id
937 FROM pay_assignment_actions paa
938 ,pay_payroll_actions ppa
939 ,pay_run_results prr
940 WHERE paa.assignment_id = p_assignment_id
941 AND ppa.action_status = 'C'
942 AND paa.action_status = 'C'
943 AND paa.payroll_action_id = ppa.payroll_action_id
944 AND paa.assignment_action_id = prr.assignment_action_id
945 AND prr.element_entry_id = p_element_entry_id
946 AND ppa.date_earned = p_date_earned
947 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
948 AND paa.source_action_id IS NOT NULL -- pick the assignment_action_id which has run_results
949 order by assignment_action_id desc ; -- pick from highest, descending
950
951
952
953 -- get run retult details of element and its indirect elements
954 CURSOR csr_run_rslt_indirect_ele
955 (p_source_id NUMBER
956 ,p_asg_act_id NUMBER
957 )
958 IS
959 SELECT prr.*
960 FROM pay_run_results prr
961 WHERE prr.assignment_action_id = p_asg_act_id
962 AND prr.source_id = p_source_id;
963
964
965 -- fetch elements which are of this source assignment action and element id
966 -- basically fetch future retro payments
967 -- For Bug 8652303
968 -- The retro entries are not picked up if the retro entry has the source id as
969 -- the element entry id of the original bonus element.
970
971 CURSOR csr_retro_ele
972 (p_assignment_id NUMBER
973 ,p_source_id NUMBER
974 ,p_source_asg_action_id NUMBER
975 ,p_effective_date DATE
976 ,p_ele_entry_id NUMBER
977 )
978 IS
979 SELECT *
980 -- element_entry_id, effective_start_date, effective_end_date, creator_type,
981 -- ,entry_type, creator_id, source_id, source_asg_action_id, source_start_date,
982 -- ,source_end_date, element_type_id
983 FROM pay_element_entries_f
984 WHERE source_asg_action_id = p_source_asg_action_id
985 AND (source_id = p_source_id OR source_id = p_ele_entry_id)
986 AND assignment_id = p_assignment_id
987 AND effective_end_date <= p_effective_date;
988
989
990 -- For Bug 9150874
991 -- The retro entries are not picked up if the retro entry has the source id as
992 -- the element entry id of the original bonus element and the original bonus element
993 -- didnot get processed in the Payroll run.
994
995 CURSOR csr_retro_ele_check
996 (p_assignment_id NUMBER
997 ,p_effective_end_date DATE
998 ,p_effective_start_date DATE
999 ,p_ele_entry_id NUMBER
1000 )
1001 IS
1002 SELECT * FROM pay_element_entries_f
1003 WHERE source_id = p_ele_entry_id
1004 AND assignment_id = p_assignment_id
1005 AND effective_end_date <= p_effective_end_date
1006 AND effective_start_date >= p_effective_start_date;
1007
1008
1009 CURSOR csr_get_next_payroll_date
1010 (p_assignment_id NUMBER
1011 ,p_effective_date DATE
1012 )
1013 IS
1014 SELECT min(ptp.end_date) next_payroll_date
1015 FROM per_time_periods ptp
1016 ,per_all_assignments_f paaf
1017 WHERE ptp.payroll_id = paaf.payroll_id
1018 AND paaf.assignment_id = p_assignment_id
1019 AND ptp.end_date >= p_effective_date ;
1020
1021
1022 CURSOR csr_get_run_result_value
1023 (--p_element_type_id NUMBER
1024 p_element_entry_id NUMBER
1025 ,p_input_value_id NUMBER
1026 ,p_asg_act_id NUMBER
1027 )
1028 IS
1029 SELECT to_number(prrv.result_value) result,
1030 prrv.run_result_id -- to be used as ee.source_id for retro elements
1031 FROM pay_run_result_values prrv
1032 ,pay_run_results prr
1033 WHERE prrv.run_result_id = prr.run_result_id
1034 AND prr.assignment_action_id = p_asg_act_id
1035 -- AND prr.element_type_id = p_element_type_id
1036 AND prr.source_id = p_element_entry_id
1037 AND prrv.input_value_id = p_input_value_id ;
1038
1039
1040
1041 -- Added as part of 115.33 (3)
1042 -- this cursor will look into future payrolls and fetch the retro payments
1043 -- which were earned in this month (of whose assignment_action_id is being passed as param)
1044 CURSOR csr_get_retro_run_value
1045 (p_assignment_action_id NUMBER
1046 ,p_effective_date DATE
1047 )
1048 IS
1049 select /*+ ORDERED USE_NL(BAL_ASSACT BACT ASSACT PACT EE RR RRV)
1050 INDEX (BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK )
1051 INDEX (BACT PAY_PAYROLL_ACTIONS_PK)
1052 INDEX (ASSACT PAY_ASSIGNMENT_ACTIONS_N51 )
1053 INDEX (ASSACT PAY_ASSIGNMENT_ACTIONS_N1 )
1054 INDEX (PACT PAY_PAYROLL_ACTIONS_PK)
1055 INDEX (EE PAY_ELEMENT_ENTRIES_F_N50)
1056 INDEX (RR PAY_RUN_RESULTS_N51)
1057 INDEX (RRV PAY_RUN_RESULT_VALUES_N50)
1058 */
1059 RRV.input_value_id,
1060 RRV.result_value,
1061 BACT.effective_date,
1062 EE.element_entry_id,
1063 EE.element_type_id,
1064 EE.effective_start_date,
1065 EE.effective_end_date,
1066 EE.source_id ee_source_id, -- this is run_result_id of parent element, of which this ele is a retro
1067 RR.source_id rr_source_id,
1068 RR.status,
1069 RR.source_type
1070 from pay_assignment_actions BAL_ASSACT,
1071 pay_payroll_actions BACT,
1072 pay_assignment_actions ASSACT,
1073 pay_payroll_actions PACT,
1074 pay_element_entries_f EE,
1075 pay_run_results RR,
1076 pay_run_result_values RRV
1077 where BAL_ASSACT.assignment_action_id = p_assignment_action_id
1078 and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
1079 and BACT.action_type <> 'V'
1080 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
1081 and ASSACT.action_sequence > BAL_ASSACT.action_sequence
1082 and PACT.payroll_action_id = ASSACT.payroll_action_id
1083 and PACT.action_type = 'L'
1084 and BACT.effective_date
1085 between nvl(PACT.start_date,
1086 BACT.effective_date) and PACT.effective_date
1087 and EE.assignment_id = ASSACT.assignment_id
1088 and PACT.effective_date
1089 between EE.effective_start_date and EE.effective_end_date
1090 and EE.creator_id = ASSACT.assignment_action_id
1091 and EE.creator_type in ('RR', 'EE', 'NR', 'PR')
1092 and EE.effective_end_date <= p_effective_date -- pick retro payment before run date
1093 and EE.source_asg_action_id = BAL_ASSACT.assignment_action_id
1094 and RR.source_id = EE.element_entry_id
1095 and RR.status in ('P', 'PA')
1096
1097 and RR.source_type in ('E', 'I')
1098 and RRV.run_result_id = RR.run_result_id
1099 and nvl(RRV.result_value, '0') <> '0'
1100 and not exists(
1101 select null
1102 from pay_run_results VRR
1103 where VRR.source_id = RR.run_result_id
1104 and VRR.source_type in ('R', 'V'));
1105
1106
1107 -- added by kkarri
1108 -- this is added to fetch the UK Rate Types element attribution.
1109 TYPE r_ele_attribution IS RECORD
1110 (
1111 from_time_dimension fnd_lookups.lookup_code%TYPE
1112 ,pay_source_value fnd_lookups.lookup_code%TYPE
1113 ,qualifier pay_element_types_f.element_name%TYPE
1114 ,fte fnd_lookups.lookup_code%TYPE
1115 ,termtime fnd_lookups.lookup_code%TYPE
1116 ,calc_type fnd_lookups.lookup_code%TYPE
1117 ,calc_value fnd_lookups.lookup_code%TYPE
1118 ,input_value fnd_lookups.lookup_code%TYPE
1119 ,link_to_assign fnd_lookups.lookup_code%TYPE
1120 ,term_time_yes_no fnd_lookups.lookup_code%TYPE
1121 ,sum_multiple_entries_yn fnd_lookups.lookup_code%TYPE
1122 ,lookup_input_values_yn fnd_lookups.lookup_code%TYPE
1123 ,column_name_source_type pay_element_type_extra_info.eei_information16%TYPE
1124 ,column_name_source_name pay_element_type_extra_info.eei_information17%TYPE
1125 ,row_name_source_type pay_element_type_extra_info.eei_information18%TYPE
1126 ,row_name_source_name pay_element_type_extra_info.eei_information19%TYPE
1127 );
1128
1129 TYPE t_ele_attribution IS TABLE OF r_ele_attribution
1130 INDEX BY BINARY_INTEGER;
1131
1132 g_ele_attribution t_ele_attribution;
1133
1134 --
1135 -- function to check for special characters in a string
1136 -- if not a-z,A-Z,0-9, return false
1137 --
1138 function is_alphanumeric
1139 (p_string in varchar2
1140 ) Return Boolean;
1141
1142 --
1143 -- function to check for special characters in a string
1144 -- if not a-z,A-Z,0-9, or a space, return false
1145 --
1146 function is_alphanumeric_space_allowed
1147 (p_string in varchar2
1148 ) Return Boolean;
1149
1150 --
1151 -- function to check for special characters in a string
1152 -- if not 0-9, return false
1153 --
1154 function is_numeric
1155 (p_string in varchar2
1156 ) Return Boolean;
1157
1158 /*
1159 --
1160 -- GET_CURRENT_EXTRACT_RESULT
1161 --
1162 -- Returns the ext_rslt_id for the current extract process
1163 -- if one is running, else returns -1
1164 --
1165 FUNCTION get_current_extract_result RETURN NUMBER;
1166
1167 */
1168
1169
1170 --
1171 -- GET_CURRENT_EXTRACT_PERSON
1172 --
1173 -- Returns the person id associated with the given assignment.
1174 -- If none is found,it returns NULL. This may arise if the
1175 -- user calls this from a header/trailer record, where
1176 -- a dummy context of assignment_id = -1 is passed.
1177 --
1178 --
1179 FUNCTION get_current_extract_person
1180 (p_assignment_id NUMBER -- context
1181 ) RETURN NUMBER;
1182
1183 -- =============================================================================
1184 -- Cursor to get the extract dfn id
1185 -- =============================================================================
1186
1187 CURSOR csr_ext_dfn_id(c_extract_name IN VARCHAR2) IS
1188 SELECT dfn.ext_dfn_id
1189 FROM ben_ext_dfn dfn
1190 WHERE dfn.name = c_extract_name;
1191
1192
1193 -- =============================================================================
1194 -- Get the benefit action details
1195 -- =============================================================================
1196 Cursor csr_ben (c_ext_dfn_id in number
1197 ,c_ext_rslt_id in number
1198 ,c_business_group_id in number) is
1199 select ben.pgm_id
1200 ,ben.pl_id
1201 ,ben.benefit_action_id
1202 ,ben.business_group_id
1203 ,ben.process_date
1204 ,ben.request_id
1205 from ben_benefit_actions ben
1206 where ben.pl_id = c_ext_rslt_id
1207 and ben.pgm_id = c_ext_dfn_id
1208 and ben.business_group_id = c_business_group_id;
1209
1210 -- =============================================================================
1211 -- Cursor to fetch the last successful approved run date
1212 -- =============================================================================
1213 CURSOR csr_get_run_date(c_ext_dfn_id IN NUMBER
1214 ,c_business_group_id IN NUMBER)
1215 IS
1216 SELECT least(trunc(run_strt_dt),eff_dt),output_name -- MAX(eff_dt)
1217 FROM ben_ext_rslt
1218 WHERE ext_dfn_id = c_ext_dfn_id
1219 AND business_group_id = c_business_group_id
1220 AND ext_stat_cd = 'A'
1221 order by eff_dt desc;
1222
1223
1224 --
1225 -- Error and warning raising functions to be called from raise_data_errors
1226 --
1227
1228 FUNCTION raise_extract_warning
1229 (p_assignment_id IN NUMBER DEFAULT g_assignment_id -- context
1230 ,p_error_text IN VARCHAR2
1231 ,p_error_number IN NUMBER DEFAULT NULL
1232 ,p_token1 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1233 ,p_token2 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1234 ,p_token3 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1235 ,p_token4 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1236 ) RETURN NUMBER;
1237
1238 FUNCTION raise_extract_error
1239 (p_business_group_id IN NUMBER DEFAULT g_business_group_id -- context
1240 ,p_assignment_id IN NUMBER DEFAULT g_assignment_id -- context
1241 ,p_error_text IN VARCHAR2
1242 ,p_error_number IN NUMBER DEFAULT NULL
1243 ,p_token1 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1244 ,p_token2 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1245 ,p_token3 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1246 ,p_token4 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1247 ) RETURN NUMBER;
1248
1249
1250 FUNCTION include_event
1251 (p_actual_date IN DATE
1252 ,p_effective_date IN DATE
1253 ,p_run_from_cutover_date IN VARCHAR2 DEFAULT 'N'
1254 )
1255 RETURN VARCHAR2;
1256
1257 PROCEDURE process_retro_event
1258 (
1259 p_include VARCHAR2 DEFAULT 'Y'
1260 );
1261
1262 FUNCTION chk_is_employee_a_leaver
1263 (
1264 p_assignment_id NUMBER
1265 ,p_effective_date DATE
1266 ,p_leaver_date OUT NOCOPY DATE
1267 ) RETURN VARCHAR2;
1268
1269
1270 FUNCTION get_ext_rslt_frm_req
1271 (p_request_id IN NUMBER
1272 ,p_business_group_id IN NUMBER
1273 ) RETURN NUMBER;
1274
1275 PROCEDURE get_elements_of_info_type
1276 (p_information_type IN VARCHAR2
1277 ,p_input_value IN VARCHAR2 DEFAULT 'PAY VALUE'
1278 ,p_input_value_mandatory_yn IN VARCHAR2 DEFAULT 'Y'
1279 );
1280
1281
1282 PROCEDURE check_if_element_qualifies
1283 (p_element_entry_id IN NUMBER
1284 ,p_element_type_id OUT NOCOPY NUMBER
1285 ,p_include OUT NOCOPY VARCHAR2 -- Y/N
1286 ,p_extract_type IN VARCHAR2 DEFAULT 'PERIODIC'
1287 ,p_element_type_id_from_crit IN NUMBER DEFAULT NULL
1288 );
1289
1290
1291 FUNCTION calc_payment_by_run_rslt
1292 (p_assignment_id IN NUMBER
1293 ,p_element_entry_id IN NUMBER
1294 ,p_element_type_id IN NUMBER
1295 ,p_date_earned IN DATE
1296 ) RETURN NUMBER;
1297
1298
1299 FUNCTION get_element_payment
1300 (p_assignment_id IN NUMBER
1301 ,p_element_entry_id IN NUMBER
1302 ,p_element_type_id IN NUMBER
1303 ,p_effective_date IN DATE
1304 ) RETURN NUMBER;
1305
1306
1307
1308 FUNCTION get_element_payment_balance
1309 (p_assignment_id IN NUMBER
1310 ,p_element_entry_id IN NUMBER
1311 ,p_element_type_id IN NUMBER
1312 ,p_balance_type_id IN NUMBER
1313 ,p_effective_date IN DATE
1314 ) RETURN NUMBER;
1315
1316
1317
1318 FUNCTION ele_entry_inp_val_cut_crit
1319 (
1320 p_ext_pay_input_value IN VARCHAR2
1321 ,p_ext_pay_element_type IN VARCHAR2
1322 ,p_ext_pay_element_entry IN VARCHAR2
1323 ,p_output OUT NOCOPY VARCHAR2
1324 )RETURN VARCHAR2;
1325
1326 FUNCTION ele_entry_inp_val_per_crit
1327 (
1328 p_ext_pay_input_value IN VARCHAR2
1329 ,p_ext_pay_element_type IN VARCHAR2
1330 ,p_ext_pay_element_entry IN VARCHAR2
1331 ,p_output OUT NOCOPY VARCHAR2
1332 )RETURN VARCHAR2;
1333
1334
1335 FUNCTION check_employee_pension_scheme
1336 (p_business_group_id IN NUMBER
1337 ,p_effective_date IN DATE
1338 ,p_assignment_id IN NUMBER
1339 ,p_psi_pension_scheme IN VARCHAR2
1340 ,p_pension_element_type_id OUT NOCOPY NUMBER
1341 ) RETURN VARCHAR2;
1342
1343 FUNCTION is_today_sal_start RETURN VARCHAR2;
1344 FUNCTION is_today_sal_end RETURN VARCHAR2;
1345
1346 FUNCTION get_dated_table_name
1347 (
1348 p_dated_table_id NUMBER
1349 )RETURN VARCHAR2;
1350
1351 Procedure exclude_errored_people
1352 (p_business_group_id in number
1353 );
1354
1355 Procedure common_post_process
1356 (p_business_group_id in number
1357 );
1358
1359 FUNCTION get_first_retro_event_date
1360 (
1361 p_assignment_id IN NUMBER
1362 ,p_retro_event_date OUT NOCOPY DATE
1363 )RETURN NUMBER;
1364 /* For bug 8359083
1365 -- ----------------------------------------------------------------------------
1366 -- |-----------------------< get_penserver_date >--------------------------|
1367 -- Description: This function will fetch the least effective_date for each assignment
1368 -- from where the events needs to be processed for reporting
1369 -- ----------------------------------------------------------------------------
1370
1371 FUNCTION get_penserver_date
1372 (p_assignment_id IN NUMBER
1373 ,p_business_group_id IN NUMBER
1374 ,p_lapp_date IN date
1375 ,p_end_date IN DATE
1376 ) RETURN date; */
1377
1378 -- ----------------------------------------------------------------------------
1379 -- |-----------------------< is_proper_claim_date >--------------------------|
1380 -- Description:
1381 -- ----------------------------------------------------------------------------
1382 FUNCTION is_proper_claim_date
1383 (
1384 p_claim_date IN DATE
1385 ,p_element_name IN VARCHAR2
1386 ,p_element_entry_id IN NUMBER
1387 ,p_assg_start_date IN DATE
1388 )RETURN BOOLEAN;
1389
1390
1391 -- ----------------------------------------------------------------------------
1392 -- |------------------------< get_rate_usr_func_name >--------------------------|
1393 -- ----------------------------------------------------------------------------
1394 PROCEDURE get_rate_usr_func_name
1395 (
1396 p_business_group_id NUMBER
1397 ,p_legislation_code VARCHAR2
1398 ,p_interface_name VARCHAR2 -- expected to be SALARY / ALLOWANCE
1399 ,p_rate_name OUT NOCOPY VARCHAR2
1400 ,p_rate_code OUT NOCOPY VARCHAR2
1401 ,p_usr_rate_function OUT NOCOPY VARCHAR2
1402 ,p_sal_ele_fte_attr OUT NOCOPY VARCHAR2
1403 );
1404
1405 ----------------------------------------------------------------------------
1406 -- |------------------------< get_fte_value >--------------------------|
1407 -- ----------------------------------------------------------------------------
1408 FUNCTION get_fte_value
1409 (
1410 p_assignment_id NUMBER
1411 ,p_effective_date DATE
1412 )RETURN NUMBER;
1413
1414 ----------------------------------------------------------------------------
1415 -- |------------------------< get_element_attribution >--------------------------|
1416 -- ----------------------------------------------------------------------------
1417 PROCEDURE get_element_attribution
1418 (
1419 p_element_name VARCHAR2
1420 ,p_ele_attribution OUT NOCOPY r_ele_attribution
1421 );
1422
1423 END PQP_GB_PSI_FUNCTIONS;
[Home] [Help]