1 PACKAGE PAY_CN_DEDUCTIONS AUTHID CURRENT_USER AS
2 /* $Header: pycndedn.pkh 120.11.12020000.2 2012/07/06 09:32:56 vmaripal ship $ */
3
4 --------------------------------------------------------------------------
5 -- --
6 -- Name : GET_SPECIAL_TAX_METHOD --
7 -- Type : FUNCTION --
8 -- Access : Public --
9 -- Description : Function to return the tax method for Special --
10 -- payment types based on the tax area for China --
11 -- --
12 -- Parameters : --
13 -- IN : p_assignment_id NUMBER --
14 -- p_date_earned DATE --
15 -- p_tax_area VARCHAR2 --
16 -- p_special_payment_type VARCHAR2 --
17 -- OUT : N/A --
18 -- RETURN : VARCHAR2 --
19 --------------------------------------------------------------------------
20 FUNCTION get_special_tax_method ( p_assignment_id IN NUMBER
21 , p_date_earned IN DATE
22 , p_tax_area IN VARCHAR2
23 , p_special_payment_type IN VARCHAR2
24 )
25 RETURN VARCHAR2;
26
27 --------------------------------------------------------------------------
28 -- --
29 -- Name : GET_TERM_NET_ACCRUAL --
30 -- Type : FUNCTION --
31 -- Access : Public --
32 -- Description : Function to return the accrued leave for the given --
33 -- accrual plan --
34 -- --
35 -- Parameters : --
36 -- IN : p_assignment_id NUMBER --
37 -- p_payroll_id NUMBER --
38 -- p_business_group_id NUMBER --
39 -- p_calculation_date DATE --
40 -- p_plan_category VARCHAR2 --
41 -- OUT : p_message VARCHAR2 --
42 -- RETURN : NUMBER --
43 --------------------------------------------------------------------------
44 FUNCTION get_term_net_accrual ( p_assignment_id IN NUMBER
45 ,p_payroll_id IN NUMBER
46 ,p_business_group_id IN NUMBER
47 ,p_calculation_date IN DATE
48 ,p_plan_category IN VARCHAR2
49 ,p_message OUT NOCOPY VARCHAR2
50 )
51 RETURN NUMBER;
52
53 --------------------------------------------------------------------------
54 -- --
55 -- Name : GET_CONT_BASE_METHODS --
56 -- Type : FUNCTION --
57 -- Access : Public --
58 -- Description : Function to return the contribute method details --
59 -- --
60 -- Parameters : --
61 -- IN : p_business_group_id NUMBER --
62 -- p_contribution_area VARCHAR2 --
63 -- p_phf_si_type VARCHAR2 --
64 -- p_hukou_type VARCHAR2 --
65 -- p_effective_date DATE --
66 -- OUT : p_ee_cont_base_method VARCHAR2 --
67 -- p_er_cont_base_method VARCHAR2 --
68 -- p_low_limit_method VARCHAR2 --
69 -- p_low_limit_amount NUMBER --
70 -- p_high_limit_method VARCHAR2 --
71 -- p_high_limit_amount NUMBER --
72 -- p_switch_periodicity VARCHAR2 --
73 -- p_switch_month VARCHAR2 --
74 -- p_rounding_method VARCHAR2 --
75 -- p_lowest_avg_salary NUMBER --
76 -- p_average_salary NUMBER --
77 -- p_ee_fixed_amount NUMBER --
78 -- p_er_fixed_amount NUMBER --
79 -- RETURN : VARCHAR2 --
80 --------------------------------------------------------------------------
81 FUNCTION get_cont_base_methods
82 (p_business_group_id IN NUMBER
83 ,p_contribution_area IN VARCHAR2
84 ,p_phf_si_type IN VARCHAR2
85 ,p_hukou_type IN VARCHAR2
86 ,p_effective_date IN DATE
87 --
88 ,p_ee_cont_base_method OUT NOCOPY VARCHAR2
89 ,p_er_cont_base_method OUT NOCOPY VARCHAR2
90 ,p_low_limit_method OUT NOCOPY VARCHAR2
91 ,p_low_limit_amount OUT NOCOPY NUMBER
92 ,p_high_limit_method OUT NOCOPY VARCHAR2
93 ,p_high_limit_amount OUT NOCOPY NUMBER
94 ,p_switch_periodicity OUT NOCOPY VARCHAR2
95 ,p_switch_month OUT NOCOPY VARCHAR2
96 ,p_rounding_method OUT NOCOPY VARCHAR2
97 ,p_lowest_avg_salary OUT NOCOPY NUMBER
98 ,p_average_salary OUT NOCOPY NUMBER
99 ,p_ee_fixed_amount OUT NOCOPY NUMBER
100 ,p_er_fixed_amount OUT NOCOPY NUMBER
101 ,p_tax_thrhld_amount OUT NOCOPY NUMBER /* added for bug 6828199 */
102 )
103 RETURN VARCHAR2;
104
105 --------------------------------------------------------------------------
106 -- --
107 -- Name : GET_PHF_SI_RATES --
108 -- Type : FUNCTION --
109 -- Access : Public --
110 -- Description : Function to return the PHF/SI Rates from Org Level --
111 -- --
112 -- Parameters : --
113 -- IN : p_assignment_id IN NUMBER --
114 -- p_contribution_area VARCHAR2 --
115 -- p_hukou_type VARCHAR2 --
116 -- p_legal_employer_id NUMBER --
117 -- p_phf_si_type VARCHAR2 --
118 -- p_effective_date VARCHAR2 --
119 -- p_organization_id NUMBER --
120 -- OUT : p_ee_rate NUMBER --
121 -- p_er_rate NUMBER --
122 -- p_ee_percent_or_fixed VARCHAR2 --
123 -- p_er_percent_or_fixed VARCHAR2 --
124 -- p_ee_rounding_method VARCHAR2 --
125 -- p_er_rounding_method VARCHAR2 --
126 -- RETURN : VARCHAR2 --
127 --------------------------------------------------------------------------
128 FUNCTION get_phf_si_rates (p_assignment_id IN NUMBER
129 ,p_business_group_id IN NUMBER
130 ,p_contribution_area IN VARCHAR2
131 ,p_phf_si_type IN VARCHAR2
132 ,p_employer_id IN VARCHAR2
133 ,p_hukou_type IN VARCHAR2
134 ,p_effective_date IN DATE
135 --
136 ,p_ee_rate_type OUT NOCOPY VARCHAR2
137 ,p_er_rate_type OUT NOCOPY VARCHAR2
138 ,p_ee_rate OUT NOCOPY NUMBER
139 ,p_er_rate OUT NOCOPY NUMBER
140 ,p_ee_thrhld_rate OUT NOCOPY NUMBER /* for bug 6828199 */
141 ,p_er_thrhld_rate OUT NOCOPY NUMBER /* for bug 6828199 */
142 ,p_ee_rounding_method OUT NOCOPY VARCHAR2
143 ,p_er_rounding_method OUT NOCOPY VARCHAR2
144 )
145 RETURN VARCHAR2;
146
147 ----------------------------------------------------------------------------
148 -- --
149 -- Name : CALCULATE_CONTRIBUTION --
150 -- Type : Function --
151 -- Access : Public --
152 -- Description : Function to process the PHF/SI elements --
153 -- --
154 -- Parameters : --
155 -- IN : --
156 -- p_business_group_id NUMBER --
157 -- p_element_entry_id NUMBER --
158 -- p_assignment_action_id NUMBER --
159 -- p_assignment_id NUMBER --
160 -- p_date_earned DATE --
161 -- p_contribution_area VARCHAR2 --
162 -- p_phf_si_type VARCHAR2 --
163 -- p_hukuo_type VARCHAR2 --
164 -- p_employer_id VARCHAR2 --
165 -- p_proc_end_date DATE --
166 -- Bug 4522945:Extra input parameters added --
167 -- p_phf_si_earnings_asg_ptd NUMBER --
168 -- p_phf_si_earnings_asg_pyear NUMBER --
169 -- p_phf_si_earnings_asg_pmth NUMBER --
170 -- p_taxable_earnings_asg_er_ptd NUMBER --
171 -- p_ee_cont_base_asg_ltd NUMBER --
172 -- p_er_cont_base_asg_ltd NUMBER --
173 -- p_ee_deductions_asg_er_ptd NUMBER --
174 -- p_er_deductions_asg_er_ptd NUMBER --
175 -- p_undeducted_ee_asg_ltd NUMBER --
176 -- p_undeducted_er_asg_ltd NUMBER --
177 -- p_undeducted_ee_asg_er_ptd NUMBER --
178 -- p_undeducted_er_asg_er_ptd NUMBER --
179 -- Bug 4522945 Changes End --
180 -- IN/OUT : --
181 -- p_calculation_date DATE --
182 -- p_ee_cont_base_amount NUMBER --
183 -- p_er_cont_base_amount NUMBER --
184 -- OUT : --
185 -- p_ee_phf_si_amount NUMBER --
186 -- p_er_phf_si_amount NUMBER --
187 -- p_undeducted_ee_phf_si_amount NUMBER --
188 -- p_undeducted_er_phf_si_amount NUMBER --
189 -- p_new_ee_cont_base_amount NUMBER --
190 -- p_new_er_cont_base_amount NUMBER --
191 -- RETURN : VARCHAR2 --
192 ----------------------------------------------------------------------------
193 FUNCTION calculate_contribution(
194 p_business_group_id IN NUMBER
195 ,p_element_entry_id IN NUMBER
196 ,p_assignment_action_id IN NUMBER
197 ,p_assignment_id IN NUMBER
198 ,p_date_earned IN DATE
199 ,p_contribution_area IN VARCHAR2
200 ,p_phf_si_type IN VARCHAR2
201 ,p_hukou_type IN VARCHAR2
202 ,p_employer_id IN VARCHAR2
203 ,p_pay_proc_period_end_date IN DATE
204 --
205 ,p_phf_si_earnings_asg_ptd IN NUMBER
206 ,p_phf_si_earnings_asg_pyear IN NUMBER
210 ,p_er_cont_base_asg_ltd IN NUMBER
207 ,p_phf_si_earnings_asg_pmth IN NUMBER
208 ,p_taxable_earnings_asg_er_ptd IN NUMBER
209 ,p_ee_cont_base_asg_ltd IN NUMBER
211 ,p_ee_deductions_asg_er_ptd IN NUMBER
212 ,p_er_deductions_asg_er_ptd IN NUMBER
213 ,p_undeducted_ee_asg_ltd IN NUMBER
214 ,p_undeducted_er_asg_ltd IN NUMBER
215 ,p_undeducted_ee_asg_er_ptd IN NUMBER
216 ,p_undeducted_er_asg_er_ptd IN NUMBER
217 --
218 ,p_calculation_date IN OUT NOCOPY DATE
219 ,p_ee_cont_base_amount IN OUT NOCOPY NUMBER
220 ,p_er_cont_base_amount IN OUT NOCOPY NUMBER
221 --
222 ,p_ee_phf_si_amount OUT NOCOPY NUMBER
223 ,p_er_phf_si_amount OUT NOCOPY NUMBER
224 ,p_new_ee_cont_base_amount OUT NOCOPY NUMBER
225 ,p_new_er_cont_base_amount OUT NOCOPY NUMBER
226 ,p_undeducted_ee_phf_si_amount OUT NOCOPY NUMBER
227 ,p_undeducted_er_phf_si_amount OUT NOCOPY NUMBER
228 ,p_ee_hi_cont_type IN VARCHAR2
229 ,p_er_hi_cont_type IN VARCHAR2
230 ,p_ee_hi_cont_amt IN NUMBER
231 ,p_er_hi_cont_amt IN NUMBER
232 ,p_ee_hi_cont_base_meth IN VARCHAR2
233 ,p_er_hi_cont_base_meth IN VARCHAR2
234 ,p_ee_hi_cont_base_amount IN NUMBER
235 ,p_er_hi_cont_base_amount IN NUMBER
236 ,p_ee_taxable_cont OUT NOCOPY NUMBER
237 ,p_er_taxable_cont OUT NOCOPY NUMBER
238 ,p_lt_ee_taxable_cont_ptd IN NUMBER
239 ,p_lt_er_taxable_cont_ptd IN NUMBER
240 )
241 RETURN VARCHAR2;
242
243 --------------------------------------------------------------------------
244 -- --
245 -- Name : GET_PHF_SI_DEFERRED_AMOUNTS --
246 -- Type : Function --
247 -- Access : Public --
248 -- Description : Procedure to calculate the deferred contributions --
249 -- --
250 -- Parameters : --
251 -- IN : p_pay_proc_period_end_date DATE --
252 -- p_actual_probation_end_date DATE --
253 -- p_const_probation_end_date DATE --
254 -- p_defer_deductions VARCHAR2 --
255 -- p_deduct_in_probation_expiry VARCHAR2 --
256 -- p_taxable_earnings_asg_er_ptd NUMBER --
257 -- IN/OUT : p_ee_phf_si_amount NUMBER --
258 -- p_er_phf_si_amount NUMBER --
259 -- p_undeducted_ee_phf_ltd NUMBER --
260 -- p_undeducted_er_phf_ltd NUMBER --
261 -- RETURN : VARCHAR2 --
262 --------------------------------------------------------------------------
263 FUNCTION get_phf_si_deferred_amounts
264 (p_pay_proc_period_end_date IN DATE
265 ,p_actual_probation_end_date IN DATE
266 ,p_const_probation_end_date IN DATE
267 ,p_defer_deductions IN VARCHAR2
268 ,p_deduct_in_probation_expiry IN VARCHAR2
269 ,p_taxable_earnings_asg_er_ptd IN NUMBER
270 --
271 ,p_ee_phf_si_amount IN OUT NOCOPY NUMBER
272 ,p_er_phf_si_amount IN OUT NOCOPY NUMBER
273 ,p_undeducted_ee_phf_ltd IN OUT NOCOPY NUMBER
274 ,p_undeducted_er_phf_ltd IN OUT NOCOPY NUMBER
275 )
276 RETURN VARCHAR2;
277
278 --------------------------------------------------------------------------
279 -- --
280 -- Name : CHECK_CONT_BASE_SETUP --
281 -- Type : Procedure --
282 -- Access : Public --
283 -- Description : Procedure to Check the Contribution Base Setup --
284 -- --
285 -- Parameters : --
286 -- IN : p_organization_id NUMBER --
287 -- p_contribution_area VARCHAR2 --
288 -- p_phf_si_type VARCHAR2 --
289 -- p_hukou_type VARCHAR2 --
290 -- p_ee_cont_base_method VARCHAR2 --
291 -- p_er_cont_base_method VARCHAR2 --
292 -- p_low_limit_method VARCHAR2 --
293 -- p_low_limit_amount NUMBER --
294 -- p_high_limit_method VARCHAR2 --
295 -- p_high_limit_amount NUMBER --
296 -- p_switch_periodicity VARCHAR2 --
297 -- p_switch_month VARCHAR2 --
301 -- p_ee_fixed_amount NUMBER --
298 -- p_rounding_method VARCHAR2 --
299 -- p_lowest_avg_salary NUMBER --
300 -- p_average_salary NUMBER --
302 -- p_er_fixed_amount NUMBER --
303 -- p_effective_start_date DATE --
304 -- p_effective_end_date DATE --
305 -- IN/ OUT : --
306 -- OUT : p_message_name NOCOPY VARCHAR2 --
307 -- p_token_name NOCOPY hr_cn_api.char_tab_type --
308 -- p_token_value NOCOPY hr_cn_api.char_tab_type --
309 -- --
310 --------------------------------------------------------------------------
311 PROCEDURE check_cont_base_setup
312 (p_organization_id IN NUMBER
313 ,p_contribution_area IN VARCHAR2
314 ,p_phf_si_type IN VARCHAR2
315 ,p_hukou_type IN VARCHAR2
316 ,p_ee_cont_base_method IN VARCHAR2
317 ,p_er_cont_base_method IN VARCHAR2
318 ,p_low_limit_method IN VARCHAR2
319 ,p_low_limit_amount IN NUMBER
320 ,p_high_limit_method IN VARCHAR2
321 ,p_high_limit_amount IN NUMBER
322 ,p_switch_periodicity IN VARCHAR2
323 ,p_switch_month IN VARCHAR2
324 ,p_rounding_method IN VARCHAR2
325 ,p_lowest_avg_salary IN NUMBER
326 ,p_average_salary IN NUMBER
327 ,p_ee_fixed_amount IN NUMBER
328 ,p_er_fixed_amount IN NUMBER
329 ,p_effective_start_date IN DATE
330 ,p_effective_end_date IN DATE
331 ,p_message_name OUT NOCOPY VARCHAR2
332 ,p_token_name OUT NOCOPY hr_cn_api.char_tab_type
333 ,p_token_value OUT NOCOPY hr_cn_api.char_tab_type
334 );
335
336 --------------------------------------------------------------------------
337 -- --
338 -- Name : CHECK_PHF_SI_RATES_SETUP --
339 -- Type : Procedure --
340 -- Access : Public --
341 -- Description : Procedure to Check the Contribution Base Setup --
342 -- --
343 -- Parameters : --
344 -- IN : p_organization_id NUMBER --
345 -- p_contribution_area VARCHAR2 --
346 -- p_phf_si_type VARCHAR2 --
347 -- p_hukou_type VARCHAR2 --
348 -- p_effective_start_date DATE --
349 -- p_effective_end_date DATE --
350 -- IN/ OUT : --
351 -- OUT : p_message_name NOCOPY VARCHAR2 --
352 -- p_token_name NOCOPY hr_cn_api.char_tab_type --
353 -- p_token_value NOCOPY hr_cn_api.char_tab_type --
354 -- --
355 --------------------------------------------------------------------------
356 PROCEDURE check_phf_si_rates_setup
357 (p_organization_id IN NUMBER
358 ,p_contribution_area IN VARCHAR2
359 ,p_organization IN VARCHAR2
360 ,p_phf_si_type IN VARCHAR2
361 ,p_hukou_type IN VARCHAR2
362 ,p_effective_start_date IN DATE
363 ,p_effective_end_date IN DATE
364 ,p_message_name OUT NOCOPY VARCHAR2
365 ,p_token_name OUT NOCOPY hr_cn_api.char_tab_type
366 ,p_token_value OUT NOCOPY hr_cn_api.char_tab_type
367 );
368
369 --------------------------------------------------------------------------
370 -- --
371 -- Name : GET_PHF_SI_EARNINGS --
372 -- Type : Function --
373 -- Access : Public --
374 -- Description : Function to get PYEAR and PMTH values for PHF/SI Earnings --
375 -- Called from all 8 PHF/SI formulas --
376 -- Change History : --
377 --------------------------------------------------------------------------
378 -- Rev# Date Userid Description --
379 --------------------------------------------------------------------------
380 -- 1.0 31-MAR-09 dduvvuri 8328944 - Created this Function --
381 --------------------------------------------------------------------------
382
383 FUNCTION get_phf_si_earnings
384 ( p_business_group_id IN NUMBER
385 ,p_assignment_id IN NUMBER
386 ,p_date_earned IN DATE
387 ,p_pay_proc_period_end_date IN DATE
388 ,p_employer_id IN VARCHAR2
389 ,p_phf_si_earnings_pyear IN OUT NOCOPY NUMBER
390 ,p_phf_si_earnings_pmth IN OUT NOCOPY NUMBER
391 ,p_contribution_area IN VARCHAR2
392 ,p_phf_si_type IN VARCHAR2
393 ,p_hukou_type IN VARCHAR2
394 )
395 RETURN VARCHAR2;
399 -- Name : GET_YOS_SEV_PAY_TAX_RULE --
396
397 --------------------------------------------------------------------------
398 -- --
400 -- Type : Function --
401 -- Access : Public --
402 -- Description : Function to get Severance Pay Taxation rule from BG level --
403 -- Change History : --
404 --------------------------------------------------------------------------
405 -- Rev# Date Userid Description --
406 --------------------------------------------------------------------------
407 -- 1.0 24-aug-09 dduvvuri 8799060 - Created this Function --
408 --------------------------------------------------------------------------
409
410 FUNCTION get_yos_sev_pay_tax_rule (
411 p_date_earned IN DATE
412 , p_tax_area IN VARCHAR2
413 )
414 RETURN VARCHAR2;
415
416 --------------------------------------------------------------------------
417 -- --
418 -- Name : GET_ENTERPRISE_ANNUITY_TAX --
419 -- Type : Function --
420 -- Access : Public --
421 -- Description : Function to check if Tax needs to be calculated on --
422 -- Enterprise Annuity Employer contribution --
423 -- Change History : --
424 --------------------------------------------------------------------------
425 -- Rev# Date Userid Description --
426 --------------------------------------------------------------------------
427 -- 1.0 09-jan-12 prasrang 13372444 - Created this Function --
428 --------------------------------------------------------------------------
429
430 FUNCTION get_enterprise_annuity_tax (
431 p_date_paid IN DATE
432 , p_tax_area IN VARCHAR2
433 , p_le IN VARCHAR2
434 )
435 RETURN VARCHAR2;
436
437 END pay_cn_deductions;
438