1 package pay_kr_ff_functions_pkg AUTHID CURRENT_USER as
2 /* $Header: pykrfffc.pkh 120.35.12020000.5 2012/12/23 18:44:21 mdubasi ship $ */
3 --------------------------------------------------------------------------------
4 function get_legislative_parameter(
5 p_payroll_action_id in number,
6 p_parameter_name in varchar2,
7 p_default_value in varchar2 default 'NULL',
8 p_flash_cache in varchar2 default 'N') return varchar2;
9 --------------------------------------------------------------------------------
10 function set_message_name(
11 p_application_short_name in varchar2,
12 p_message_name in varchar2) return number;
13 --------------------------------------------------------------------------------
14 function set_message_token(
15 p_token_name in varchar2,
16 p_token_value in varchar2) return number;
17 --------------------------------------------------------------------------------
18 function get_message return varchar2;
19 --------------------------------------------------------------------------------
20 procedure ni_component(
21 p_national_identifier in varchar2,
22 p_sex out NOCOPY varchar2,
23 p_date_of_birth out NOCOPY date);
24 --------------------------------------------------------------------------------
25 function ni_sex(p_national_identifier in varchar2) return varchar2;
26 --------------------------------------------------------------------------------
30 function ni_nationality(p_national_identifier in varchar2) return varchar2;
27 function ni_date_of_birth(p_national_identifier in varchar2) return date;
28 --------------------------------------------------------------------------------
29 -- Bug 3172960
31 --------------------------------------------------------------------------------
32 -- Bug 3172960
33 function ni_nationality(p_assignment_id in number,
34 p_effective_date in date) return varchar2;
35 --------------------------------------------------------------------------------
36 function eoy_age(
37 p_date_of_birth in date,
38 p_effective_date in date) return number;
39 --------------------------------------------------------------------------------
40 function dpnt_spouse_flag(p_contact_type in varchar2,
41 p_kr_cont_type in varchar2) return varchar2;
42 --------------------------------------------------------------------------------
43 function aged_dpnt_flag(
44 p_contact_type in varchar2,
45 p_kr_cont_type in varchar2, -- Bug 7661820
46 p_national_identifier in varchar2,
47 p_effective_date in date) return varchar2;
48 --------------------------------------------------------------------------------
49 function adult_dpnt_flag(
50 p_contact_type in varchar2,
51 p_kr_cont_type in varchar2, -- Bug 7661820
52 p_national_identifier in varchar2,
53 p_effective_date in date,
54 p_disabled_flag in varchar2,
55 p_age_exception_flag in varchar2) return varchar2;
56 --------------------------------------------------------------------------------
57 function underaged_dpnt_flag(
58 p_contact_type in varchar2,
59 p_kr_cont_type in varchar2, -- Bug 7661820
60 p_national_identifier in varchar2,
61 p_effective_date in date) return varchar2;
62 --------------------------------------------------------------------------------
63 function aged_flag(
64 p_national_identifier in varchar2,
65 p_effective_date in date) return varchar2;
66 --------------------------------------------------------------------------------
67 function super_aged_flag(
68 p_national_identifier in varchar2,
69 p_effective_date in date) return varchar2;
70 --------------------------------------------------------------------------------
71 function disabled_flag(
72 p_person_id in number,
73 p_effective_date in date) return varchar2;
74 --------------------------------------------------------------------------------
75 function child_flag(
76 p_kr_contact_type in varchar2,
77 p_contact_type in varchar2,
78 p_national_identifier in varchar2,
79 p_effective_date in date) return varchar2;
80
81 function single_parent_child_flag(
82 p_kr_contact_type in varchar2,
83 p_contact_type in varchar2,
84 p_national_identifier in varchar2,
85 p_effective_date in date) return varchar2;
86 --------------------------------------------------------------------------------
87 -- National Pension Exception Reason (Formula Function)
88 -- Bug 2815425
89 --------------------------------------------------------------------------------
90 function get_np_exception_flag (
91 p_date_earned IN DATE
92 ,p_business_group_id IN NUMBER
93 ,p_assignment_id IN NUMBER ) return varchar2;
94 --------------------------------------------------------------------------------
95 /* Bug 6784288 */
96 function addtl_child_flag(
97 p_contact_type in varchar2,
98 p_national_identifier in varchar2,
99 p_cont_information4 in varchar2, -- Bug 7615517
100 p_cont_information11 in varchar2, -- Bug 7615517
101 p_cont_information15 in varchar2, -- Bug 7661820
102 p_effective_date in date) return varchar2;
103 function get_dependent_info(
104 p_assignment_id in number,
105 p_date_earned in date,
106 p_non_resident_flag in varchar2,
107 p_dpnt_spouse_flag out NOCOPY varchar2,
108 p_num_of_aged_dpnts out NOCOPY number,
109 p_num_of_adult_dpnts out NOCOPY number,
110 p_num_of_underaged_dpnts out NOCOPY number,
111 p_num_of_dpnts out NOCOPY number,
112 p_num_of_ageds out NOCOPY number,
113 p_num_of_disableds out NOCOPY number,
114 p_female_ee_flag out NOCOPY varchar2,
115 p_num_of_children out NOCOPY number) return number;
116 --------------------------------------------------------------------------------
117 /* Bug 6705170 : Function get_dependent_info() has been overloaded
118 to fetch the New Born/Adopted Child count */
119 --------------------------------------------------------------------------------
120 function get_dependent_info(
121 p_assignment_id in number,
122 p_date_earned in date,
123 p_non_resident_flag in varchar2,
124 p_dpnt_spouse_flag out NOCOPY varchar2,
125 p_num_of_aged_dpnts out NOCOPY number,
126 p_num_of_adult_dpnts out NOCOPY number,
127 p_num_of_underaged_dpnts out NOCOPY number,
128 p_num_of_dpnts out NOCOPY number,
129 p_num_of_ageds out NOCOPY number,
130 p_num_of_disableds out NOCOPY number,
131 p_female_ee_flag out NOCOPY varchar2,
132 p_num_of_children out NOCOPY number,
133 p_num_of_super_ageds out NOCOPY number,
134 p_num_of_new_born_adopted out NOCOPY number,
135 p_num_of_addtl_child out NOCOPY number) return number; /* Bug 6784288 */
136 --------------------------------------------------------------------------------
137 -- Bug 3172960
138 function get_dependent_info(
139 p_assignment_id in number,
140 p_date_earned in date,
141 p_non_resident_flag in varchar2,
142 p_dpnt_spouse_flag out NOCOPY varchar2,
143 p_num_of_aged_dpnts out NOCOPY number,
144 p_num_of_adult_dpnts out NOCOPY number,
145 p_num_of_underaged_dpnts out NOCOPY number,
146 p_num_of_dpnts out NOCOPY number,
150 p_num_of_children out NOCOPY number,
147 p_num_of_ageds out NOCOPY number,
148 p_num_of_disableds out NOCOPY number,
149 p_female_ee_flag out NOCOPY varchar2,
151 p_num_of_super_ageds out NOCOPY number,
152 p_num_of_addtl_child out NOCOPY number) return number; /* Bug 6784288 */
153 --------------------------------------------------------------------------------
154 -- Bug 3172960
155 function get_dependent_info(
156 p_assignment_id in number,
157 p_date_earned in date,
158 p_non_resident_flag in varchar2,
159 p_dpnt_spouse_flag out NOCOPY varchar2,
160 p_num_of_aged_dpnts out NOCOPY number,
161 p_num_of_adult_dpnts out NOCOPY number,
162 p_num_of_underaged_dpnts out NOCOPY number,
163 p_num_of_dpnts out NOCOPY number,
164 p_num_of_ageds out NOCOPY number,
165 p_num_of_disableds out NOCOPY number,
166 p_female_ee_flag out NOCOPY varchar2,
167 p_num_of_children out NOCOPY number,
168 p_num_of_super_ageds out NOCOPY number) return number;
169 --------------------------------------------------------------------------------
170 -- Bug 9737699
171 function get_dependent_info(
172 p_assignment_id in number,
173 p_date_earned in date,
174 p_non_resident_flag in varchar2,
175 p_dpnt_spouse_flag out NOCOPY varchar2,
176 p_num_of_aged_dpnts out NOCOPY number,
177 p_num_of_adult_dpnts out NOCOPY number,
178 p_num_of_underaged_dpnts out NOCOPY number,
179 p_num_of_dpnts out NOCOPY number,
180 p_num_of_ageds out NOCOPY number,
181 p_num_of_disableds out NOCOPY number,
182 p_female_ee_flag out NOCOPY varchar2,
183 p_num_of_children out NOCOPY number,
184 p_num_of_super_ageds out NOCOPY number,
185 p_num_of_addtl_child out NOCOPY number,
186 p_single_parent_flag out NOCOPY varchar2,
187 p_house_holder_flag out NOCOPY varchar2,
188 p_tot_num_dpnts out NOCOPY number) return number ;
189
190 function get_dependent_info(
191 p_assignment_id in number,
192 p_date_earned in date,
193 p_non_resident_flag in varchar2,
194 p_dpnt_spouse_flag out NOCOPY varchar2,
195 p_num_of_aged_dpnts out NOCOPY number,
196 p_num_of_adult_dpnts out NOCOPY number,
197 p_num_of_underaged_dpnts out NOCOPY number,
198 p_num_of_dpnts out NOCOPY number,
199 p_num_of_ageds out NOCOPY number,
200 p_num_of_disableds out NOCOPY number,
201 p_female_ee_flag out NOCOPY varchar2,
202 p_num_of_children out NOCOPY number,
203 p_num_of_super_ageds out NOCOPY number,
204 p_num_of_new_born_adopted out NOCOPY number,
205 p_num_of_addtl_child out NOCOPY number,
206 p_single_parent_flag out NOCOPY varchar2) return number ;
207
208 function get_dependent_info(
209 p_assignment_id in number,
210 p_date_earned in date,
211 p_non_resident_flag in varchar2,
212 p_dpnt_spouse_flag out NOCOPY varchar2,
213 p_num_of_aged_dpnts out NOCOPY number,
214 p_num_of_adult_dpnts out NOCOPY number,
215 p_num_of_underaged_dpnts out NOCOPY number,
216 p_num_of_dpnts out NOCOPY number,
217 p_num_of_ageds out NOCOPY number,
218 p_num_of_disableds out NOCOPY number,
219 p_female_ee_flag out NOCOPY varchar2,
220 p_num_of_children out NOCOPY number,
221 p_num_of_super_ageds out NOCOPY number,
222 p_num_of_new_born_adopted out NOCOPY number,
223 p_num_of_addtl_child out NOCOPY number,
224 p_single_parent_flag out NOCOPY varchar2,
225 p_house_holder_flag out NOCOPY varchar2,
226 p_tot_num_dpnts out NOCOPY number) return number ;
227
228 function get_prev_sep_pay_details( p_assignment_id in number,
229 p_date_earned in date,
230 p_prev_stat_sep_total out NOCOPY number,
231 p_prev_nst_sep_total out NOCOPY number,
232 p_prev_stat_sep_calc_amt out NOCOPY number,
233 p_prev_nst_sep_calc_amt out NOCOPY number) return number;
234 --------------------------------------------------------------------------------
235 -- Employment Insurance Exception Reasons in payroll deduction
236 -- Bug 2833174
237 --------------------------------------------------------------------------------
238 function get_ei_loss_exception_codes(
239 p_date_earned in date
240 ,p_business_group_id in number
241 ,p_assignment_id in number
242 ,p_loss_ineligible_flag out nocopy varchar2
243 ,p_exception_flag out nocopy varchar2
244 ,p_exception_type out nocopy varchar2
245 ,p_overlapped_ex_flag out nocopy varchar2
246 ) return number;
247 --------------------------------------------------------------------------------
248 -- Bug 4674552
249 function is_exempted_dependent(
250 p_cont_type in per_contact_relationships.contact_type%type,
251 p_kr_cont_typ in per_contact_relationships.cont_information11%type, -- Bug 7661820
252 p_ni in per_people_f.national_identifier%type,
253 p_itax_dpnt_flag in per_contact_relationships.cont_information2%type,
254 p_addl_tax_exem_flag in per_contact_relationships.cont_information3%type,
255 p_addl_disabled_flag in per_contact_relationships.cont_information4%type,
256 p_addl_exem_flag_child in per_contact_relationships.cont_information7%type,
257 p_age_ckh_exp_flag in per_contact_relationships.cont_information8%type,
258 p_eff_date in pay_payroll_actions.effective_date%type,
259 p_ins_prem_exem_incl_flag in per_contact_relationships.cont_information10%type, -- Bug 4931542
260 p_med_exp_exem_incl_flag in per_contact_relationships.cont_information12%type, -- Bug 4931542
261 p_edu_exp_exem_incl_flag in per_contact_relationships.cont_information13%type, -- Bug 4931542
265 --------------------------------------------------------------------------------
262 p_card_exp_exem_incl_flag in per_contact_relationships.cont_information14%type, -- Bug 4931542
263 p_contact_extra_info_id in per_contact_extra_info_f.contact_extra_info_id%type -- Bug 5879106
264 ) return varchar2 ;
266 function dpnt_eligible_for_basic_exem(
267 p_cont_type in per_contact_relationships.contact_type%type,
268 p_kr_cont_typ in per_contact_relationships.cont_information11%type, -- Bug 7661820
269 p_ni in per_people_f.national_identifier%type,
270 p_itax_dpnt_flag in per_contact_relationships.cont_information2%type,
271 p_addl_disabled_flag in per_contact_relationships.cont_information4%type,
272 p_age_ckh_exp_flag in per_contact_relationships.cont_information8%type,
273 p_eff_date in pay_payroll_actions.effective_date%type
274 ) return varchar2 ;
275 --------------------------------------------------------------------------------
276 function dpnt_addl_child_exempted(
277 p_addl_child_exem in varchar2,
278 p_ni in varchar2,
279 p_eff_date in date
280 ) return varchar2;
281 --------------------------------------------------------------------------------
282 -- procedure get_double_exem_amt
283 procedure get_double_exem_amt(p_assignment_id in per_assignments_f.assignment_id%type,
284 p_effective_year in varchar2,
285 p_double_exm_amt out nocopy number);
286 --
287 -----------------------------------------------------------------------------------
288 -- Bug 6849941: New Validation Checks for Credit Card Fields on the Income Tax Form
289 -----------------------------------------------------------------------------------
290 Function enable_credit_card(
291 p_person_id in number,
292 p_contact_person_id in number,
293 p_contact_relationship_id in number,
294 p_date_earned in date) return varchar2;
295 --
296 -----------------------------------------------------------------------------------
297 -- Bug 7164589: Long Term Treatment Insurance Premium
298 -- Bug 7228788: Added a new input parameter to the function for the Input Value Name
299 -----------------------------------------------------------------------------------
300 FUNCTION get_long_term_ins_skip_flag(
301 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
302 ,p_input_value_name in varchar2
303 ) RETURN VARCHAR2;
304 --
305 ----------------------------------------------------------------------------------------------------
306 -- Bug 7361372: FUNCTION chk_id_format() checks if the argument1 is in the same format as argument2.
307 -- If not then an error is raised. Else the same string as argument1 is returned.
308 ----------------------------------------------------------------------------------------------------
309 FUNCTION chk_id_format(
310 p_chk_string IN VARCHAR2,
311 p_format_string IN VARCHAR2) RETURN VARCHAR2;
312 ------------------------------------------------------------------------------------
313 -- Bug 7142612: Validation Checks for Donation Fields on the Income Tax Form
314 -----------------------------------------------------------------------------------
315 Function enable_donation_fields(
316 p_person_id in number,
317 p_contact_person_id in number,
318 p_contact_relationship_id in number,
319 p_date_earned in date) return varchar2;
320 --
321 -----------------------------------------------------------------------------------
322 -- Bug 7526435 FUNCTION validate_bus_reg_num() checks the validation logic for provider reg.
323 -- no. of medical service provider and returns false if reg.no validation fails
324 ------------------------------------------------------------------------------------------------
325 FUNCTION validate_bus_reg_num(
326 p_national_identifier IN VARCHAR2) RETURN VARCHAR2;
327 --
328 ------------------------------------------------------------------------------------------------
329 -- Bug 7676136: Function to get the Value of the Globals
330 ------------------------------------------------------------------------------------------------
331 function get_globalvalue(
332 p_glbvar in varchar2,
333 p_process_date in date) return number;
334 --
335 ------------------------------------------------------------------------------------------------
336 ------------------------------------------------------------------------------------------------
337 -- Bug 8341054: Gets the Flag for Input Value of an Element
338 ------------------------------------------------------------------------------------------------
339 FUNCTION get_element_input_value(
340 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
341 ,p_input_value_name in varchar2
342 ,p_element_name in varchar2
343 ) RETURN VARCHAR2;
344 --
345 ------------------------------------------------------------------------------------------------
346 -- Bug 8341054: Gets the Flag for Input Value of an Element
347 ------------------------------------------------------------------------------------------------
348 FUNCTION get_element_input_value_y(
349 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
350 ,p_input_value_name in varchar2
351 ,p_element_name in varchar2
352 ) RETURN VARCHAR2;
353
354 ------------------------------------------------------------------------------------------------
355 -- Bug 8466662: Gets the Run Result Value for an Input Value of Type Money
356 ------------------------------------------------------------------------------------------------
357 FUNCTION get_element_rr_value(
358 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
359 ,p_input_value_name in varchar2
360 ,p_element_name in varchar2
364 -- Bug 8466662: Gets the Run Result Value for an Input Value of Type Date
361 ) RETURN number;
362 --
363 ------------------------------------------------------------------------------------------------
365 ------------------------------------------------------------------------------------------------
366 FUNCTION get_element_rr_date_value(
367 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
368 ,p_input_value_name in varchar2
369 ,p_element_name in varchar2
370 ) RETURN date;
371 --
372 ------------------------------------------------------------------------------------------------
373 -- Bug 8466662: This function will be called from the TAX formula to fetch the individual
374 -- Calculated Taxes. Based on the value for the input p_class it will return
375 -- the calculated tax values
376 -- (p_class = 1 => individual calculated tax for each working place irrespective
377 -- of their eligiblity for the Post tax deduction.
378 -- p_class = 2 => individual calculated tax values for all the eligible working
379 -- places.
380 -----------------------------------------------------------------------------------------------
381 function SepPayPostTax( p_assignment_id in number,
382 p_business_group_id in number,
383 p_date_earned in date,
384 p_assignment_action_id in number,
385 p_total_taxable_earnings in number, -- TOTAL_TAXABLE_EARNINGS_ASG_RUN
386 p_nst_taxable_earnings in number, -- SP_SEP_ALW_ASG_RUN
387 p_wkpd_int_sep_pay in number,
388 p_sep_pay_income_exem_rate in number,
389 p_class in number,
390 p_sep_cal_mode in varchar2,
391 p_sep_lump_sum_amount in number,
392 p_emp_eligibility_flag in varchar2,
393 p_st_emp_hire_date in date,
394 p_st_emp_leaving_date in date,
395 p_nst_emp_hire_date in date,
396 p_nst_emp_leaving_date in date,
397 p_sep_max_post_tax_deduc in number,
398 p_amount_expected in number,
399 p_personal_contribution in number,
400 p_pension_exemption in number,
401 p_principal_interest in number,
402 p_nst_amount_expected in number,
403 p_prev_sep_lump_sum_amt in number,
404 p_nst_sep_calc_tax out NOCOPY number,
405 p_sep_calc_tax out NOCOPY number,
406 p_st_max_lim out NOCOPY number,
407 p_nst_max_lim out NOCOPY number
408 ) return number;
409 -----------------------------------------------------------------------------------------------
410 -- Bug 8466662: This function simulates the Statutory Separation Pay Process and returns the
411 -- Statutory Calculated Tax value.
412 ------------------------------------------------------------------------------------------------
413 function SepPayTaxCalc(
414 p_service_period in number,
415 p_overlap_period in number,
416 p_taxable_earnings in number,
417 p_sep_taxable_earnings in number,
418 p_receivable_sep_pay in number,
419 p_sep_tax_conversion_reqd in varchar2,
420 p_business_group_id in number,
421 p_effective_date in date,
422 p_sep_pay_income_exem_rate in number) return number;
423 -----------------------------------------------------------------------------------------------
424 -- Bug 8466662: This procedure simulates the Non-Statutory Separation Pay Process and returns the
425 -- Statutory and Non-Statutory Calculated Tax values.
426 ------------------------------------------------------------------------------------------------
427 Procedure NonStatTaxCalc(
428 p_service_period in number,
429 p_nst_service_period in number,
430 p_st_overlap_period in number,
431 p_nst_overlap_period in number,
432 p_sep_taxable_earnings in number,
433 p_taxable_earnings in number,
434 p_nst_taxable_earnings in number,
435 p_wkpd_int_sep_pay in number,
436 p_sep_tax_conversion_reqd in varchar2,
437 p_receivable_sep_pay in number,
438 p_effective_date in date,
439 p_business_group_id in number,
440 p_sep_pay_income_exem_rate in number,
441 l_nst_sep_calc_tax out NOCOPY number,
442 l_sep_calc_tax out NOCOPY number,
443 p_nst_receivable_sep_pay in number,
444 p_nst_sep_taxable_earnings in number);
445 ------------------------------------------------------------------------------------------------
446 -- Bug 8644512:This function returns the correct lookup code as per NTS guidelines depending
447 -- on the year
448 ------------------------------------------------------------------------------------------------
449 function get_cont_lookup_code (p_lookup_code in varchar2,
450 p_target_year in number) return varchar2;
451 ------------------------------------------------------------------------------------------------
452 -- Bug 9079450: Function to return the lookup meaning for the dependent education expense region
453 ------------------------------------------------------------------------------------------------
454 function decode_lookup(
458 -- Bug 10082074: Function to return Children under the age of 20
455 p_effective_date in varchar2,
456 p_code in varchar2) return varchar2;
457 ------------------------------------------------------------------------------------------------
459 ------------------------------------------------------------------------------------------------
460 function underaged_children_flag(
461 p_contact_type in varchar2,
462 p_kr_cont_type in varchar2,
463 p_national_identifier in varchar2,
464 p_effective_date in date) return varchar2;
465 ------------------------------------------------------------------------------------------------
466 -- Bug 9393732
467 ------------------------------------------------------------------------------------------------
468 function display_yea_info(p_assignment_id in number,
469 p_effective_date in date) return varchar2;
470 ------------------------------------------------------------------------------------------------
471 -- Bug 11867156
472 ------------------------------------------------------------------------------------------------
473 function get_avg_taxable_earnings(p_taxable_earnings in number,
474 p_addtl_child_exem_govt_flag in varchar2,
475 p_foreign_fixed_tax_rate in varchar2 ) return number;
476 -- Bug 13990960
477 ---------------------------------------------------------------------------------------------
478 function get_kr_address_line1(p_postal_code_id in varchar2) return varchar2;
479
480 -- Bug 14754822
481 function tax_reduction_calc(p_assignment_id in number,
482 p_information_type in varchar2,
483 p_hire_date in date,
484 p_term_date in date,
485 p_pay_proc_period_date in date,
486 p_calc_tax in number,
487 p_taxable_earnings in number,
488 p_eligible_tax_reduc_amnt in number) return number;
489 end pay_kr_ff_functions_pkg;
[Home] [Help]