1 PACKAGE PQP_GB_PSI_FUNCTIONS AUTHID CURRENT_USER AS
2 -- /* $Header: pqpgbpsifunc.pkh 120.12.12010000.5 2008/08/21 12:11:17 namgoyal 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 g_retro_event_date_reported BOOLEAN := TRUE;
243
244 -- ----------------------------------------------------------------------------
245 -- |------------------------< init_st_end_date_glob --------------------------|
246 -- ----------------------------------------------------------------------------
247 PROCEDURE init_st_end_date_glob;
248
249 -- ----------------------------------------------------------------------------
250 -- |---------------------< get_start_end_date >------------------------|
251 -- ----------------------------------------------------------------------------
252 FUNCTION get_start_end_date
253 (
254 p_assignment_id NUMBER
255 ,p_business_group_id NUMBER
256 ,p_effective_date DATE
257 ,p_start_date OUT NOCOPY DATE
258 ,p_end_date OUT NOCOPY DATE
259 )RETURN NUMBER;
260
261 -- ----------------------------------------------------------------------------
262 -- |-------------------------< get_contract_type >-----------------------------|
263 -- ----------------------------------------------------------------------------
264 FUNCTION get_contract_type
265 (
266 p_assignment_id NUMBER
267 ,p_business_group_id NUMBER
268 ,p_effective_date IN DATE
269 ,p_contract_type OUT NOCOPY VARCHAR2
270 ) RETURN NUMBER;
271
272 ------------------------------------------------------------------------------
273 --|-------------------------< get_notional_pay >-----------------------|
274 -- ----------------------------------------------------------------------------
275 FUNCTION get_notional_pay
276 (
277 p_assignment_id IN NUMBER
278 ,p_business_group_id IN NUMBER
279 ,p_effective_date IN DATE
280 ,p_name IN VARCHAR2
281 ,p_rt_element IN VARCHAR2
282 ,p_rate IN OUT NOCOPY NUMBER
283 ,p_custom_function IN VARCHAR2 DEFAULT NULL
284 ,p_allowance_code IN VARCHAR2 DEFAULT NULL
285 ,p_allowance_pet_id IN NUMBER DEFAULT NULL
286 ) RETURN NUMBER;
287
288 -- ----------------------------------------------------------------------------
289 -- |------------------------< get_actual_pay >--------------------------|
290 -- ----------------------------------------------------------------------------
291 FUNCTION get_actual_pay
292 (
293 p_assignment_id IN NUMBER
294 ,p_notional_pay IN NUMBER
295 ,p_effective_date IN DATE
296 ,p_output OUT NOCOPY VARCHAR2
297 )RETURN NUMBER;
298
299 ------------------------------------------------
300 --
301 -- For fetching person details for person_id
302 --
303 CURSOR csr_get_person_dtl
304 ( p_business_group_id NUMBER
305 ,p_effective_date DATE
306 ,p_person_id NUMBER
307 ) IS
308 select *
309 from per_all_people_f papf
310 where papf.business_group_id = p_business_group_id
311 and papf.person_id = p_person_id
312 and p_effective_date
313 between papf.effective_start_date
314 and papf.effective_end_date;
315
316
317 --
318 -- For fetching assignment details
319 --
320
321 -- old
322 /*
323 CURSOR csr_get_assignment_dtl
324 ( p_business_group_id NUMBER
325 ,p_effective_date DATE
326 ,p_assignment_id NUMBER
327 ) IS
328 select *
329 from per_all_assignments_f paaf
330 where paaf.business_group_id = p_business_group_id
331 and paaf.assignment_id = p_assignment_id
332 and paaf.assignment_type = 'E'
333 and paaf.employment_category IS NOT NULL
334 and p_effective_date
335 between paaf.effective_start_date
336 and paaf.effective_end_date;
337 */
338
339 -- new
340 -- 1) for periodic : till final_process_date
341 CURSOR csr_get_assignment_dtl_per
342 ( p_business_group_id NUMBER
343 ,p_effective_date DATE
344 ,p_assignment_id NUMBER
345 ) IS
346 select paaf.*
347 from per_all_assignments_f paaf --, per_periods_of_service PPS
348 where paaf.business_group_id = p_business_group_id
349 and paaf.assignment_id = p_assignment_id
350 and paaf.assignment_type = 'E'
351 -- and pps.person_id = paaf.person_id
352 -- and pps.business_group_id = p_business_group_id
353 and paaf.employment_category IS NOT NULL
354 and p_effective_date
355 between paaf.effective_start_date
356 and paaf.effective_end_date;
357 -- and p_effective_date
358 -- between pps.date_start
359 -- and NVL(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY'));
360
361
362 -- 2) for cutover : till actual_termination_date
363 CURSOR csr_get_assignment_dtl_cut
364 ( p_business_group_id NUMBER
365 ,p_effective_date DATE
366 ,p_assignment_id NUMBER
367 ) IS
368 select paaf.*
369 from per_all_assignments_f paaf, per_periods_of_service PPS
370 where paaf.business_group_id = p_business_group_id
371 and paaf.assignment_id = p_assignment_id
372 and paaf.assignment_type = 'E'
373 and pps.person_id = paaf.person_id
374 and pps.business_group_id = p_business_group_id
375 and paaf.employment_category IS NOT NULL
376 and p_effective_date
377 between paaf.effective_start_date
378 and paaf.effective_end_date
379 and p_effective_date
380 between pps.date_start
381 and NVL(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'));
382
383
384
385 --
386 -- Indicator whether element_type_id of a pension scheme
387 -- is present for this assignment or not.
388 -- Being used for checking if person is in a particular scheme
389 --
390 CURSOR csr_partnership_scheme_flag
391 (p_business_group_id NUMBER
392 ,p_effective_date DATE
393 ,p_assignment_id NUMBER
394 ,p_element_type_id NUMBER
395 )
396 IS
397 select 'Y' from dual
398 where
399 (select pee.element_type_id
400 from pay_element_entries_f pee ,pay_element_links_f pel
401 where pee.element_link_id = pel.element_link_id
402 and pel.business_group_id = p_business_group_id
403 and pee.assignment_id = p_assignment_id
404 and pee.element_type_id = p_element_type_id
405 and p_effective_date between
406 pee.effective_start_date and pee.effective_end_date
407 and rownum = 1
408 ) IS NOT NULL;
409
410 /*
411 --
412 -- fetch penserver assignment category code
413 --
414 CURSOR csr_assignment_category
415 (p_business_group_id NUMBER
416 ,p_effective_date DATE
417 ,p_assignment_id NUMBER
418 )
419 IS
420 select pcv.pcv_information2
421 from per_all_assignments_f paaf, PQP_CONFIGURATION_VALUES pcv
422 where paaf.assignment_id = p_assignment_id
423 and pcv.pcv_information1 = paaf.employment_category
424 and paaf.business_group_id = p_business_group_id
425 and pcv_information_category = 'PQP_GB_PENSERVER_EMPLYMT_TYPE'
426 and p_effective_date
427 between paaf.effective_start_date
428 and paaf.effective_end_date;
429 */
430
431
432 --
433 -- penserver last_hire_date indicator
434 -- returns a 'Y' if the person has been employed
435 -- more than 3 months ago from the current date
436 CURSOR csr_last_hire_date_indicator
437 (p_business_group_id NUMBER
438 ,p_effective_date DATE
439 ,p_person_id NUMBER
440 )
441 IS
442
443 select 'Y'
444 from dual
445 where
446 (select PPS.DATE_START -- DECODE(PER.CURRENT_EMPLOYEE_FLAG,'Y',PPS.DATE_START,NULL)
447 from per_all_people_f PER, per_periods_of_service PPS
448 where per.person_id = p_person_id
449 and pps.person_id = p_person_id
450 and p_effective_date
451 between per.effective_start_date
452 and NVL(per.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
453 and p_effective_date
454 between pps.date_start
455 and NVL(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY'))
456 )
457 <=
458 (
459 select add_months(p_effective_date,-3) from dual
460 );
461
462 FUNCTION check_debug
463 (p_business_group_id IN VARCHAR2 -- context
464 )
465 RETURN boolean;
466
467 -------------debug
468 PROCEDURE DEBUG(
469 p_trace_message IN VARCHAR2
470 ,p_trace_location IN NUMBER DEFAULT NULL
471 );
472
473 -------------debug_enter
474 PROCEDURE debug_enter(
475 p_proc_name IN VARCHAR2 DEFAULT NULL
476 ,p_trace_on IN VARCHAR2 DEFAULT NULL
477 );
478
479 -------------debug_exit
480 PROCEDURE debug_exit(
481 p_proc_name IN VARCHAR2 DEFAULT NULL
482 ,p_trace_off IN VARCHAR2 DEFAULT NULL
483 );
484
485 PROCEDURE debug_others(
486 p_proc_name IN VARCHAR2
487 ,p_proc_step IN NUMBER DEFAULT NULL
488 );
489
490
491 -- ----------------------------------------------------------------------------
492 -- |------------------------< check_employee_eligibility >-------------------|
493 -- ----------------------------------------------------------------------------
494 FUNCTION check_employee_eligibility
495 (p_business_group_id IN NUMBER
496 ,p_assignment_id IN NUMBER
497 ,p_effective_date IN DATE
498 ,p_chg_value OUT NOCOPY VARCHAR2 -- the scheme name entered.
499 ) RETURN VARCHAR2; -- Y or N
500
501 FUNCTION chk_penserver_basic_criteria
502 (p_business_group_id IN NUMBER
503 ,p_effective_date IN DATE
504 ,p_assignment_id IN NUMBER
505 ,p_person_dtl OUT NOCOPY per_all_people_f%rowtype
506 ,p_assignment_dtl OUT NOCOPY per_all_assignments_f%rowtype
507 ) RETURN VARCHAR2;
508
509 /*
510 PROCEDURE get_config_type_values
511 (
512 p_configuration_type IN VARCHAR2
513 ,p_business_group_id IN NUMBER
514 ,p_legislation_code IN VARCHAR2
515 ,p_tab_config_values OUT NOCOPY t_config_values
516 );
517 */
518
519 PROCEDURE set_shared_globals
520 (p_business_group_id IN NUMBER
521 ,p_paypoint OUT NOCOPY VARCHAR2
522 ,p_cutover_date OUT NOCOPY VARCHAR2
523 ,p_ext_dfn_id OUT NOCOPY NUMBER
524 );
525
526
527 --
528 -- function to store error/warnings into collection
529 --
530 PROCEDURE store_extract_exceptions
531 (p_extract_type IN VARCHAR2 -- global/interface name
532 ,p_error_number IN NUMBER
533 ,p_error_text IN VARCHAR2
534 ,p_token1 IN VARCHAR2 DEFAULT NULL
535 ,p_token2 IN VARCHAR2 DEFAULT NULL
536 ,p_token3 IN VARCHAR2 DEFAULT NULL
537 ,p_token4 IN VARCHAR2 DEFAULT NULL
538 ,p_error_warning_flag IN VARCHAR2 -- E (error) / W(warning)
539 );
540
541 --
542 -- function to raise error/warnings that are stored in collection
543 --
544
545 PROCEDURE raise_extract_exceptions
546 (p_extract_type IN VARCHAR2 DEFAULT 'DE'
547 );
548
549 --
550 -- function to return altkey of a person assignment
551 --
552
553 FUNCTION altkey
554 --(p_assignment_number IN VARCHAR2 -- context
555 --,p_paypoint IN VARCHAR2 -- context
556 --)
557 RETURN VARCHAR2;
558
559 --
560 -- function to return paypoint of a person assignment
561 --
562 FUNCTION paypoint
563 (p_business_group_id IN VARCHAR2 -- context
564 )
565 RETURN VARCHAR2;
566
567 --
568 -- function to return employer_code of a person assignment
569 --
570 FUNCTION employer_code
571 (p_business_group_id NUMBER
572 ,p_effective_date DATE
573 ,p_assignment_id NUMBER
574 ) RETURN VARCHAR2;
575
576 -- =============================================================================
577 -- ~ PQP_Penserver_Extract: This is called by the conc. program
578 -- ~ to run Penserver extracts and is basically a
579 -- ~ wrapper around the benefits conc. program Extract Process.
580 -- This function will launch new concurrent requests for each extract submission
581 -- =============================================================================
582
583 PROCEDURE PQP_Penserver_Extract
584 (errbuf OUT NOCOPY VARCHAR2
585 ,retcode OUT NOCOPY VARCHAR2
586 ,p_benefit_action_id IN NUMBER
587 ,p_business_group_id IN NUMBER
588 ,p_execution_mode IN VARCHAR2 -- GEN/DBG/SET
589 ,p_execution_mode_type IN VARCHAR2
590 ,p_extract_type IN VARCHAR2 -- CUT/PED/CODE
591 ,p_dfn_name IN VARCHAR2 -- ALL/BDI/ADI/SDI/EDI/ACI/BCI/GCI/LCI
592 ,p_start_date IN VARCHAR2
593 ,p_eff_date IN VARCHAR2
594 ,p_submit_request_y_n IN VARCHAR2 default 'N'
595 ,p_concurrent_request_id IN NUMBER DEFAULT NULL
596 ,p_year_end_close IN VARCHAR2 default 'N' -- /* Nuvos Changes */
597 ,p_short_time_hours_single IN VARCHAR2 default 'INCLUDE' -- For Bug 7010282
598 );
599
600
601
602 -- =============================================================================
603 -- This procedure gets control totals information
604 -- =============================================================================
605 PROCEDURE Get_Penserver_CntrlTtl_Process
606 (errbuf OUT NOCOPY VARCHAR2
607 ,retcode OUT NOCOPY VARCHAR2
608 ,p_extract_type IN VARCHAR2 DEFAULT NULL
609 ,p_parent_request_id IN NUMBER DEFAULT NULL
610 ,p_parent_selected IN VARCHAR2 DEFAULT NULL
611 ,p_ext_bdi_rslt_id IN NUMBER DEFAULT NULL
612 ,p_ext_adi_rslt_id IN NUMBER DEFAULT NULL
613 ,p_ext_sehi_rslt_id IN NUMBER DEFAULT NULL
614 ,p_ext_sahi_rslt_id IN NUMBER DEFAULT NULL
615 ,p_ext_ehi_rslt_id IN NUMBER DEFAULT NULL
616 ,p_ext_ahi_rslt_id IN NUMBER DEFAULT NULL
617 ,p_ext_bhi_rslt_id IN NUMBER DEFAULT NULL
618 ,p_ext_wps_rslt_id IN NUMBER DEFAULT NULL
619 ,p_ext_pthi_rslt_id IN NUMBER DEFAULT NULL
620 ,p_ext_sthi_rslt_id IN NUMBER DEFAULT NULL
621 ,p_ext_sthai_rslt_id IN NUMBER DEFAULT NULL
622 ,p_business_group_id IN NUMBER
623 ,p_year_end_close IN VARCHAR2 default 'N' -- /* Nuvos Changes */
624 );
625
626 -- =============================================================================
627 -- Record : rec_cntrl_tot
628 -- to get total controls information
629 -- =============================================================================
630 TYPE rec_cntrl_tot IS RECORD
631 ( pay_point VARCHAR2(6)
632 ,file_extract_date VARCHAR2(16)
633 ,seq_num VARCHAR2(3)
634 ,basic_cnt VARCHAR2(10)
635 ,serv_hist_cnt VARCHAR2(10)
636 ,earn_hist_cnt VARCHAR2(10)
637 ,earn_hist_tot_WPS VARCHAR2(16)
638 ,sal_hist_cnt VARCHAR2(10)
639 ,sal_hist_tot_national_pay VARCHAR2(16)
640 ,allw_hist_rec_cnt VARCHAR2(10)
641 ,allw_hist_tot_allw_rate VARCHAR2(16)
642 ,bonus_hist_rec_cnt VARCHAR2(10)
643 ,bonus_hist_tot_bonus_amt VARCHAR2(16)
644 ,WPS_contrbt_hist_rec_cnt VARCHAR2(10)
645 ,WPS_contrbt_hist_tot_perc VARCHAR2(16)
646 ,AVC_hist_rec_cnt VARCHAR2(10)
647 ,EECONT_tot VARCHAR2(16)
648 ,other_benef_rec_cnt VARCHAR2(10)
649 ,PUP_tot VARCHAR2(16)
650 ,prt_tm_hr_hist_rec_cnt VARCHAR2(10)
651 ,prt_tm_hr_hist_tot_pthrs VARCHAR2(16)
652 ,srt_tm_hr_hist_sing_rec_cnt VARCHAR2(10)
653 ,srt_tm_hr_hist_sing_tot_hr_var VARCHAR2(16)
654 ,srt_tm_hr_hist_accu_rec_cnt VARCHAR2(10)
655 ,srt_tm_hr_hist_accu_tot_hr_var VARCHAR2(16)
656 ,event_det_tot_rec VARCHAR2(10)
657 ,event_det_tot_amt VARCHAR2(16)
658 ,remarks_interface_tot_rec VARCHAR2(10)
659 ,addr_data_tot_rec VARCHAR2(10)
660 ,benef_det_tot_rec VARCHAR2(10)
661 ,pay_hist_cnt VARCHAR2(10) -- For Nuvos changes
662 ,pay_hist_tot_EARN VARCHAR2(16)
663 ,pay_hist_tot_DEDS VARCHAR2(16)
664 ,year_end_close VARCHAR2(4)
665 ,pay_per_end_date VARCHAR2(10)
666 );
667 TYPE t_cntrl_tot IS TABLE OF rec_cntrl_tot
668 INDEX BY BINARY_INTEGER;
669
670 -- =============================================================================
671 -- Record : rec_allowance_codes
672 -- to get allowance code information
673 -- =============================================================================
674 TYPE rec_allowance_codes IS RECORD
675 ( pay_point VARCHAR2(6)
676 ,allowance_code VARCHAR2(20)
677 ,allowance_descr VARCHAR2(60)
678 ,pension_flag VARCHAR2(1)
679 ,industrial_flag VARCHAR2(1)
680 ,spread_bonus_flag VARCHAR2(1)
681 ,filler1 VARCHAR2(16)
682 ,basic_pay_reckonable VARCHAR2(1)
683 ,pre_75_reckonable VARCHAR2(1)
684 ,filler2 VARCHAR2(79)
685 );
686 TYPE t_allowance_codes IS TABLE OF rec_allowance_codes
687 INDEX BY BINARY_INTEGER;
688
689 -- =============================================================================
690 -- Record : rec_bonus_codes
691 -- to get bonus code information
692 -- =============================================================================
693 TYPE rec_bonus_codes IS RECORD
694 ( pay_point VARCHAR2(6)
695 ,bonus_code VARCHAR2(20)
696 ,bonus_descr VARCHAR2(60)
697 ,pension_flag VARCHAR2(1)
698 ,industrial_flag VARCHAR2(1)
699 ,filler1 VARCHAR2(16)
700 ,basic_pay_reckonable VARCHAR2(1)
701 ,pre_75_reckonable VARCHAR2(1)
702 ,filler2 VARCHAR2(86)
703 );
704 TYPE t_bonus_codes IS TABLE OF rec_bonus_codes
705 INDEX BY BINARY_INTEGER;
706
707 -- =============================================================================
708 -- Cursor - csr_get_extra_allow_information
709 -- Information type is passed as parameter
710 -- =============================================================================
711 CURSOR csr_get_extra_allow_info
712 (p_from_date IN DATE
713 ,p_to_date IN DATE) IS
714 SELECT element_name,
715 eei_information2 code,
716 eei_information3 description,
717 eei_information4 pension_flag,
718 eei_information5 industrial_flag,
719 eei_information6 spread_bonus_flag,
720 eei_information7 basic_pay_reckonable,
721 eei_information8 pre_75_reckonable
722 FROM pay_element_type_extra_info petei,
723 pay_element_types_f petf
724 WHERE information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO'
725 AND petei.element_type_id = petf.element_type_id
726 AND petei.eei_information2 IS NOT NULL
727 AND ((p_from_date BETWEEN petf.effective_start_date AND petf.effective_end_date)
728 OR
729 (p_to_date BETWEEN petf.effective_start_date AND petf.effective_end_date));
730
731
732 -- =============================================================================
733 -- Cursor - csr_get_extra_bonus_information
734 -- Information type is passed as parameter
735 -- =============================================================================
736 CURSOR csr_get_extra_bonus_info
737 ( p_from_date IN DATE
738 ,p_to_date IN DATE) IS
739 SELECT element_name,
740 eei_information2 code,
741 eei_information3 description,
742 eei_information4 pension_flag,
743 eei_information5 industrial_flag,
744 eei_information6 basic_pay_reckonable,
745 eei_information7 pre_75_reckonable
746 FROM pay_element_type_extra_info petei,
747 pay_element_types_f petf
748 WHERE information_type = 'PQP_GB_PENSERV_BONUS_INFO'
749 AND petei.element_type_id = petf.element_type_id
750 AND petei.eei_information2 IS NOT NULL
751 AND ((p_from_date BETWEEN petf.effective_start_date AND petf.effective_end_date)
752 OR
753 (p_to_date BETWEEN petf.effective_start_date AND petf.effective_end_date));
754
755
756 -- =============================================================================
757 -- Cursor - csr_debug_enable_mode
758 -- =============================================================================
759 CURSOR csr_debug_enable_mode IS
760 SELECT * -- argument3
761 FROM fnd_concurrent_requests
762 WHERE request_id =
763 (SELECT req.parent_request_id
764 FROM fnd_concurrent_requests req, fnd_concurrent_programs con
765 WHERE request_id = fnd_global.conc_request_id
766 AND con.concurrent_program_id = req.concurrent_program_id);
767
768
769 -- =============================================================================
770 -- Cursor - csr_debug_enable_mode_parent
771 -- =============================================================================
772 CURSOR csr_debug_enable_mode_parent IS
773 SELECT * -- req.parent_request_id
774 FROM fnd_concurrent_requests req
775 WHERE request_id = fnd_global.conc_request_id;
776
777
778 -- =============================================================================
779 -- Cursor - csr_get_elements_of_info_type
780 -- Information type is passed as parameter
781 -- Input Value is mandatory
782 -- =============================================================================
783 CURSOR csr_get_elements_of_info_type
784 (c_information_type IN VARCHAR2
785 ,c_input_value IN VARCHAR2 DEFAULT 'PAY VALUE'
786 ) IS
787 SELECT distinct(petei.element_type_id)
788 ,pet.element_name
789 ,pet.processing_type
790 ,piv.input_value_id
791 ,petei.eei_information1
792 ,petei.eei_information2
793 ,petei.eei_information3
794 ,petei.eei_information4
795 ,petei.eei_information5
796 ,petei.eei_information6
797 ,petei.eei_information7
798 ,petei.eei_information8
799 ,petei.eei_information9
800 ,petei.eei_information10
801 ,pet.retro_summ_ele_id -- retro element type ID -- 115.33 (1)
802 FROM pay_element_type_extra_info petei
803 ,pay_element_types_f pet
804 ,pay_input_values_f piv
805 ,pay_input_values_f piv2
806 WHERE petei.information_type = c_information_type
807 AND pet.element_type_id = petei.element_type_id
808 AND piv.element_type_id = pet.element_type_id
809 AND piv2.element_type_id = pet.element_type_id
810 AND UPPER(piv2.NAME) = UPPER(c_input_value)
811 AND UPPER(piv.NAME) = 'PAY VALUE' ;
812
813
814 -- =============================================================================
815 -- Cursor - csr_get_elements_of_info_type
816 -- Information type is passed as parameter
817 -- no restriction on Input value
818 -- =============================================================================
819 CURSOR csr_ele_info_type_no_inp_val
820 (c_information_type IN VARCHAR2
821 ) IS
822 SELECT distinct(petei.element_type_id)
823 ,pet.element_name
824 ,pet.processing_type
825 ,pet.element_type_id ele_type_id
826 ,petei.eei_information1
827 ,petei.eei_information2
828 ,petei.eei_information3
829 ,petei.eei_information4
830 ,petei.eei_information5
831 ,petei.eei_information6
832 ,petei.eei_information7
833 ,petei.eei_information8
834 ,petei.eei_information9
835 ,petei.eei_information10
836 ,pet.retro_summ_ele_id -- retro element type ID -- 115.33 (1)
837 FROM pay_element_type_extra_info petei
838 ,pay_element_types_f pet
839 WHERE petei.information_type = c_information_type
840 AND pet.element_type_id = petei.element_type_id;
841
842
843 -- =============================================================================
844 -- Cursor - csr_get_elements_of_info_type
845 -- Information type is passed as parameter
846 -- =============================================================================
847 CURSOR csr_get_element_type_id(c_element_entry_id IN NUMBER) IS
848 SELECT element_type_id
849 FROM pay_element_entries_f
850 WHERE element_entry_id = c_element_entry_id
851 AND rownum=1;
852
853
854 -- =============================================================================
855 -- Used to maintain element information
856 -- =============================================================================
857
858 TYPE t_elements_of_info_type is Table OF csr_get_elements_of_info_type%rowtype
859 INDEX BY BINARY_INTEGER;
860
861 g_elements_of_info_type t_elements_of_info_type;
862
863
864 -- =============================================================================
865 -- Used to maintain element_entry_id and element_type_id collection
866 -- =============================================================================
867
868 TYPE r_elements_processed IS RECORD
869 (element_type_id NUMBER
870 ,inclusion_flag VARCHAR2(1) -- Y/N
871 );
872 TYPE t_elements_processed is Table OF r_elements_processed
873 INDEX BY BINARY_INTEGER;
874
875 g_elements_processed t_elements_processed;
876
877
878
879 CURSOR csr_get_asg_act_id
880 ( p_assignment_id NUMBER
881 ,p_date_earned DATE
882 )
883 IS
884 SELECT /*+ ordered use_nl(PAA PPA)
885 index (PAA PAY_ASSIGNMENT_ACTIONS_N1)
886 index (PAA PAY_ASSIGNMENT_ACTIONS_N51)
887 index (PPA PAY_PAYROLL_ACTIONS_PK)*/
888 paa.assignment_action_id -- max(paa.assignment_action_id)
889 -- no longer using max, now pick all assignment runs
890 FROM pay_assignment_actions paa
891 ,pay_payroll_actions ppa
892 WHERE paa.assignment_id = p_assignment_id
893 AND ppa.action_status = 'C'
894 AND paa.action_status = 'C'
895 AND paa.payroll_action_id = ppa.payroll_action_id
896 AND ppa.date_earned = p_date_earned
897 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
898 AND paa.source_action_id IS NOT NULL -- pick the assignment_action_id which has run_results
899 order by assignment_action_id desc ; -- pick from highest, descending
900
901 -- Added as part of 115.33 (2)
902 CURSOR csr_get_asg_act_id_retro
903 ( p_assignment_id NUMBER
904 ,p_date_earned DATE
905 )IS
906
907 SELECT paa.assignment_action_id -- min(paa.assignment_action_id)
908 -- ppa.date_earned
909 FROM pay_assignment_actions paa
910 ,pay_payroll_actions ppa
911 WHERE paa.assignment_id = p_assignment_id
912 AND ppa.action_status = 'C'
913 AND paa.action_status = 'C'
914 AND paa.payroll_action_id = ppa.payroll_action_id
915 AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
916 AND ppa.date_earned = p_date_earned
917 AND paa.source_action_id IS NULL
918 ORDER BY assignment_action_id desc ; -- pick from highest, descending
919 -- ORDER BY assignment_action_id ;
920
921
922 -- get all assignment_action_id, source_action_id
923 CURSOR csr_get_all_asg_act_id
924 ( p_assignment_id NUMBER
925 ,p_date_earned DATE
926 ,p_element_entry_id NUMBER
927 )
928 IS
929 SELECT paa.assignment_action_id, paa.source_action_id
930 FROM pay_assignment_actions paa
931 ,pay_payroll_actions ppa
932 ,pay_run_results prr
933 WHERE paa.assignment_id = p_assignment_id
934 AND ppa.action_status = 'C'
935 AND paa.action_status = 'C'
936 AND paa.payroll_action_id = ppa.payroll_action_id
937 AND paa.assignment_action_id = prr.assignment_action_id
938 AND prr.element_entry_id = p_element_entry_id
939 AND ppa.date_earned = p_date_earned
940 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
941 AND paa.source_action_id IS NOT NULL -- pick the assignment_action_id which has run_results
942 order by assignment_action_id desc ; -- pick from highest, descending
943
944
945
946 -- get run retult details of element and its indirect elements
947 CURSOR csr_run_rslt_indirect_ele
948 (p_source_id NUMBER
949 ,p_asg_act_id NUMBER
950 )
951 IS
952 SELECT prr.*
953 FROM pay_run_results prr
954 WHERE prr.assignment_action_id = p_asg_act_id
955 AND prr.source_id = p_source_id;
956
957
958 -- fetch elements which are of this source assignment action and element id
959 -- basically fetch future retro payments
960
961 CURSOR csr_retro_ele
962 (p_assignment_id NUMBER
963 ,p_source_id NUMBER
964 ,p_source_asg_action_id NUMBER
965 ,p_effective_date DATE
966 )
967 IS
968 SELECT *
969 -- element_entry_id, effective_start_date, effective_end_date, creator_type,
970 -- ,entry_type, creator_id, source_id, source_asg_action_id, source_start_date,
971 -- ,source_end_date, element_type_id
972 FROM pay_element_entries_f
973 WHERE source_asg_action_id = p_source_asg_action_id
974 AND source_id = p_source_id
975 AND assignment_id = p_assignment_id
976 AND effective_end_date <= p_effective_date;
977
978
979
980 CURSOR csr_get_next_payroll_date
981 (p_assignment_id NUMBER
982 ,p_effective_date DATE
983 )
984 IS
985 SELECT min(ptp.end_date) next_payroll_date
986 FROM per_time_periods ptp
987 ,per_all_assignments_f paaf
988 WHERE ptp.payroll_id = paaf.payroll_id
989 AND paaf.assignment_id = p_assignment_id
990 AND ptp.end_date >= p_effective_date ;
991
992
993 CURSOR csr_get_run_result_value
994 (--p_element_type_id NUMBER
995 p_element_entry_id NUMBER
996 ,p_input_value_id NUMBER
997 ,p_asg_act_id NUMBER
998 )
999 IS
1000 SELECT to_number(prrv.result_value) result,
1001 prrv.run_result_id -- to be used as ee.source_id for retro elements
1002 FROM pay_run_result_values prrv
1003 ,pay_run_results prr
1004 WHERE prrv.run_result_id = prr.run_result_id
1005 AND prr.assignment_action_id = p_asg_act_id
1006 -- AND prr.element_type_id = p_element_type_id
1007 AND prr.source_id = p_element_entry_id
1008 AND prrv.input_value_id = p_input_value_id ;
1009
1010
1011
1012 -- Added as part of 115.33 (3)
1013 -- this cursor will look into future payrolls and fetch the retro payments
1014 -- which were earned in this month (of whose assignment_action_id is being passed as param)
1015 CURSOR csr_get_retro_run_value
1016 (p_assignment_action_id NUMBER
1017 ,p_effective_date DATE
1018 )
1019 IS
1020 select /*+ ORDERED USE_NL(BAL_ASSACT BACT ASSACT PACT EE RR RRV)
1021 INDEX (BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK )
1022 INDEX (BACT PAY_PAYROLL_ACTIONS_PK)
1023 INDEX (ASSACT PAY_ASSIGNMENT_ACTIONS_N51 )
1024 INDEX (ASSACT PAY_ASSIGNMENT_ACTIONS_N1 )
1025 INDEX (PACT PAY_PAYROLL_ACTIONS_PK)
1026 INDEX (EE PAY_ELEMENT_ENTRIES_F_N50)
1027 INDEX (RR PAY_RUN_RESULTS_N51)
1028 INDEX (RRV PAY_RUN_RESULT_VALUES_N50)
1029 */
1030 RRV.input_value_id,
1031 RRV.result_value,
1032 BACT.effective_date,
1033 EE.element_entry_id,
1034 EE.element_type_id,
1035 EE.effective_start_date,
1036 EE.effective_end_date,
1037 EE.source_id ee_source_id, -- this is run_result_id of parent element, of which this ele is a retro
1038 RR.source_id rr_source_id,
1039 RR.status,
1040 RR.source_type
1041 from pay_assignment_actions BAL_ASSACT,
1042 pay_payroll_actions BACT,
1043 pay_assignment_actions ASSACT,
1044 pay_payroll_actions PACT,
1045 pay_element_entries_f EE,
1046 pay_run_results RR,
1047 pay_run_result_values RRV
1048 where BAL_ASSACT.assignment_action_id = p_assignment_action_id
1049 and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
1050 and BACT.action_type <> 'V'
1051 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
1052 and ASSACT.action_sequence > BAL_ASSACT.action_sequence
1053 and PACT.payroll_action_id = ASSACT.payroll_action_id
1054 and PACT.action_type = 'L'
1055 and BACT.effective_date
1056 between nvl(PACT.start_date,
1057 BACT.effective_date) and PACT.effective_date
1058 and EE.assignment_id = ASSACT.assignment_id
1059 and PACT.effective_date
1060 between EE.effective_start_date and EE.effective_end_date
1061 and EE.creator_id = ASSACT.assignment_action_id
1062 and EE.creator_type in ('RR', 'EE', 'NR', 'PR')
1063 and EE.effective_end_date <= p_effective_date -- pick retro payment before run date
1064 and EE.source_asg_action_id = BAL_ASSACT.assignment_action_id
1065 and RR.source_id = EE.element_entry_id
1066 and RR.status in ('P', 'PA')
1067
1068 and RR.source_type in ('E', 'I')
1069 and RRV.run_result_id = RR.run_result_id
1070 and nvl(RRV.result_value, '0') <> '0'
1071 and not exists(
1072 select null
1073 from pay_run_results VRR
1074 where VRR.source_id = RR.run_result_id
1075 and VRR.source_type in ('R', 'V'));
1076
1077
1078 -- added by kkarri
1079 -- this is added to fetch the UK Rate Types element attribution.
1080 TYPE r_ele_attribution IS RECORD
1081 (
1082 from_time_dimension fnd_lookups.lookup_code%TYPE
1083 ,pay_source_value fnd_lookups.lookup_code%TYPE
1084 ,qualifier pay_element_types_f.element_name%TYPE
1085 ,fte fnd_lookups.lookup_code%TYPE
1086 ,termtime fnd_lookups.lookup_code%TYPE
1087 ,calc_type fnd_lookups.lookup_code%TYPE
1088 ,calc_value fnd_lookups.lookup_code%TYPE
1089 ,input_value fnd_lookups.lookup_code%TYPE
1090 ,link_to_assign fnd_lookups.lookup_code%TYPE
1091 ,term_time_yes_no fnd_lookups.lookup_code%TYPE
1092 ,sum_multiple_entries_yn fnd_lookups.lookup_code%TYPE
1093 ,lookup_input_values_yn fnd_lookups.lookup_code%TYPE
1094 ,column_name_source_type pay_element_type_extra_info.eei_information16%TYPE
1095 ,column_name_source_name pay_element_type_extra_info.eei_information17%TYPE
1096 ,row_name_source_type pay_element_type_extra_info.eei_information18%TYPE
1097 ,row_name_source_name pay_element_type_extra_info.eei_information19%TYPE
1098 );
1099
1100 TYPE t_ele_attribution IS TABLE OF r_ele_attribution
1101 INDEX BY BINARY_INTEGER;
1102
1103 g_ele_attribution t_ele_attribution;
1104
1105 --
1106 -- function to check for special characters in a string
1107 -- if not a-z,A-Z,0-9, return false
1108 --
1109 function is_alphanumeric
1110 (p_string in varchar2
1111 ) Return Boolean;
1112
1113 --
1114 -- function to check for special characters in a string
1115 -- if not a-z,A-Z,0-9, or a space, return false
1116 --
1117 function is_alphanumeric_space_allowed
1118 (p_string in varchar2
1119 ) Return Boolean;
1120
1121 --
1122 -- function to check for special characters in a string
1123 -- if not 0-9, return false
1124 --
1125 function is_numeric
1126 (p_string in varchar2
1127 ) Return Boolean;
1128
1129 /*
1130 --
1131 -- GET_CURRENT_EXTRACT_RESULT
1132 --
1133 -- Returns the ext_rslt_id for the current extract process
1134 -- if one is running, else returns -1
1135 --
1136 FUNCTION get_current_extract_result RETURN NUMBER;
1137
1138 */
1139
1140
1141 --
1142 -- GET_CURRENT_EXTRACT_PERSON
1143 --
1144 -- Returns the person id associated with the given assignment.
1145 -- If none is found,it returns NULL. This may arise if the
1146 -- user calls this from a header/trailer record, where
1147 -- a dummy context of assignment_id = -1 is passed.
1148 --
1149 --
1150 FUNCTION get_current_extract_person
1151 (p_assignment_id NUMBER -- context
1152 ) RETURN NUMBER;
1153
1154 -- =============================================================================
1155 -- Cursor to get the extract dfn id
1156 -- =============================================================================
1157
1158 CURSOR csr_ext_dfn_id(c_extract_name IN VARCHAR2) IS
1159 SELECT dfn.ext_dfn_id
1160 FROM ben_ext_dfn dfn
1161 WHERE dfn.name = c_extract_name;
1162
1163
1164 -- =============================================================================
1165 -- Get the benefit action details
1166 -- =============================================================================
1167 Cursor csr_ben (c_ext_dfn_id in number
1168 ,c_ext_rslt_id in number
1169 ,c_business_group_id in number) is
1170 select ben.pgm_id
1171 ,ben.pl_id
1172 ,ben.benefit_action_id
1173 ,ben.business_group_id
1174 ,ben.process_date
1175 ,ben.request_id
1176 from ben_benefit_actions ben
1177 where ben.pl_id = c_ext_rslt_id
1178 and ben.pgm_id = c_ext_dfn_id
1179 and ben.business_group_id = c_business_group_id;
1180
1181 -- =============================================================================
1182 -- Cursor to fetch the last successful approved run date
1183 -- =============================================================================
1184 CURSOR csr_get_run_date(c_ext_dfn_id IN NUMBER
1185 ,c_business_group_id IN NUMBER)
1186 IS
1187 SELECT least(trunc(run_strt_dt),eff_dt),output_name -- MAX(eff_dt)
1188 FROM ben_ext_rslt
1189 WHERE ext_dfn_id = c_ext_dfn_id
1190 AND business_group_id = c_business_group_id
1191 AND ext_stat_cd = 'A'
1192 order by eff_dt desc;
1193
1194
1195 --
1196 -- Error and warning raising functions to be called from raise_data_errors
1197 --
1198
1199 FUNCTION raise_extract_warning
1200 (p_assignment_id IN NUMBER DEFAULT g_assignment_id -- context
1201 ,p_error_text IN VARCHAR2
1202 ,p_error_number IN NUMBER DEFAULT NULL
1203 ,p_token1 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1204 ,p_token2 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1205 ,p_token3 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1206 ,p_token4 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1207 ) RETURN NUMBER;
1208
1209 FUNCTION raise_extract_error
1210 (p_business_group_id IN NUMBER DEFAULT g_business_group_id -- context
1211 ,p_assignment_id IN NUMBER DEFAULT g_assignment_id -- context
1212 ,p_error_text IN VARCHAR2
1213 ,p_error_number IN NUMBER DEFAULT NULL
1214 ,p_token1 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1215 ,p_token2 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1216 ,p_token3 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1217 ,p_token4 IN VARCHAR2 DEFAULT NULL --added to pass tokens to messages.
1218 ) RETURN NUMBER;
1219
1220
1221 FUNCTION include_event
1222 (p_actual_date IN DATE
1223 ,p_effective_date IN DATE
1224 ,p_run_from_cutover_date IN VARCHAR2 DEFAULT 'N'
1225 )
1226 RETURN VARCHAR2;
1227
1228 PROCEDURE process_retro_event
1229 (
1230 p_include VARCHAR2 DEFAULT 'Y'
1231 );
1232
1233 FUNCTION chk_is_employee_a_leaver
1234 (
1235 p_assignment_id NUMBER
1236 ,p_effective_date DATE
1237 ,p_leaver_date OUT NOCOPY DATE
1238 ) RETURN VARCHAR2;
1239
1240
1241 FUNCTION get_ext_rslt_frm_req
1242 (p_request_id IN NUMBER
1243 ,p_business_group_id IN NUMBER
1244 ) RETURN NUMBER;
1245
1246 PROCEDURE get_elements_of_info_type
1247 (p_information_type IN VARCHAR2
1248 ,p_input_value IN VARCHAR2 DEFAULT 'PAY VALUE'
1249 ,p_input_value_mandatory_yn IN VARCHAR2 DEFAULT 'Y'
1250 );
1251
1252
1253 PROCEDURE check_if_element_qualifies
1254 (p_element_entry_id IN NUMBER
1255 ,p_element_type_id OUT NOCOPY NUMBER
1256 ,p_include OUT NOCOPY VARCHAR2 -- Y/N
1257 ,p_extract_type IN VARCHAR2 DEFAULT 'PERIODIC'
1258 ,p_element_type_id_from_crit IN NUMBER DEFAULT NULL
1259 );
1260
1261
1262 FUNCTION calc_payment_by_run_rslt
1263 (p_assignment_id IN NUMBER
1264 ,p_element_entry_id IN NUMBER
1265 ,p_element_type_id IN NUMBER
1266 ,p_date_earned IN DATE
1267 ) RETURN NUMBER;
1268
1269
1270 FUNCTION get_element_payment
1271 (p_assignment_id IN NUMBER
1272 ,p_element_entry_id IN NUMBER
1273 ,p_element_type_id IN NUMBER
1274 ,p_effective_date IN DATE
1275 ) RETURN NUMBER;
1276
1277
1278
1279 FUNCTION get_element_payment_balance
1280 (p_assignment_id IN NUMBER
1281 ,p_element_entry_id IN NUMBER
1282 ,p_element_type_id IN NUMBER
1283 ,p_balance_type_id IN NUMBER
1284 ,p_effective_date IN DATE
1285 ) RETURN NUMBER;
1286
1287
1288
1289 FUNCTION ele_entry_inp_val_cut_crit
1290 (
1291 p_ext_pay_input_value IN VARCHAR2
1292 ,p_ext_pay_element_type IN VARCHAR2
1293 ,p_ext_pay_element_entry IN VARCHAR2
1294 ,p_output OUT NOCOPY VARCHAR2
1295 )RETURN VARCHAR2;
1296
1297 FUNCTION ele_entry_inp_val_per_crit
1298 (
1299 p_ext_pay_input_value IN VARCHAR2
1300 ,p_ext_pay_element_type IN VARCHAR2
1301 ,p_ext_pay_element_entry IN VARCHAR2
1302 ,p_output OUT NOCOPY VARCHAR2
1303 )RETURN VARCHAR2;
1304
1305
1306 FUNCTION check_employee_pension_scheme
1307 (p_business_group_id IN NUMBER
1308 ,p_effective_date IN DATE
1309 ,p_assignment_id IN NUMBER
1310 ,p_psi_pension_scheme IN VARCHAR2
1311 ,p_pension_element_type_id OUT NOCOPY NUMBER
1312 ) RETURN VARCHAR2;
1313
1314 FUNCTION is_today_sal_start RETURN VARCHAR2;
1315 FUNCTION is_today_sal_end RETURN VARCHAR2;
1316
1317 FUNCTION get_dated_table_name
1318 (
1319 p_dated_table_id NUMBER
1320 )RETURN VARCHAR2;
1321
1322 Procedure exclude_errored_people
1323 (p_business_group_id in number
1324 );
1325
1326 Procedure common_post_process
1327 (p_business_group_id in number
1328 );
1329
1330 FUNCTION get_first_retro_event_date
1331 (
1332 p_assignment_id IN NUMBER
1333 ,p_retro_event_date OUT NOCOPY DATE
1334 )RETURN NUMBER;
1335
1336 -- ----------------------------------------------------------------------------
1337 -- |-----------------------< is_proper_claim_date >--------------------------|
1338 -- Description:
1339 -- ----------------------------------------------------------------------------
1340 FUNCTION is_proper_claim_date
1341 (
1342 p_claim_date IN DATE
1343 ,p_element_name IN VARCHAR2
1344 ,p_element_entry_id IN NUMBER
1345 ,p_assg_start_date IN DATE
1346 )RETURN BOOLEAN;
1347
1348
1349 -- ----------------------------------------------------------------------------
1350 -- |------------------------< get_rate_usr_func_name >--------------------------|
1351 -- ----------------------------------------------------------------------------
1352 PROCEDURE get_rate_usr_func_name
1353 (
1354 p_business_group_id NUMBER
1355 ,p_legislation_code VARCHAR2
1356 ,p_interface_name VARCHAR2 -- expected to be SALARY / ALLOWANCE
1357 ,p_rate_name OUT NOCOPY VARCHAR2
1358 ,p_rate_code OUT NOCOPY VARCHAR2
1359 ,p_usr_rate_function OUT NOCOPY VARCHAR2
1360 ,p_sal_ele_fte_attr OUT NOCOPY VARCHAR2
1361 );
1362
1363 ----------------------------------------------------------------------------
1364 -- |------------------------< get_fte_value >--------------------------|
1365 -- ----------------------------------------------------------------------------
1366 FUNCTION get_fte_value
1367 (
1368 p_assignment_id NUMBER
1369 ,p_effective_date DATE
1370 )RETURN NUMBER;
1371
1372 ----------------------------------------------------------------------------
1373 -- |------------------------< get_element_attribution >--------------------------|
1374 -- ----------------------------------------------------------------------------
1375 PROCEDURE get_element_attribution
1376 (
1377 p_element_name VARCHAR2
1378 ,p_ele_attribution OUT NOCOPY r_ele_attribution
1379 );
1380
1381 END PQP_GB_PSI_FUNCTIONS;
[Home] [Help]