1 package pay_ie_paye_pkg AUTHID CURRENT_USER as
2 /* $Header: pyietax.pkh 120.13 2011/11/23 13:20:42 smeduri ship $ */
3 /*
4 **
5 ** Copyright (C) 1999 Oracle Corporation
6 ** All Rights Reserved
7 **
8 ** IE PAYE package header
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** -----------+--------+---------+-------------
15 ** 20 JUN 2001 jmhatre N/A Created
16 ** 18 OCT 2001 abhaduri N/A Changes due to SOE form requirement
17 added p_assess_basis and
18 p_certificate_issue_date as OUT parameters
19 to function get_paye_details for feeding
20 them to PAYE details
21 ** 05 DEC 2001 gpadmasa N/A Added dbdrv Commands
22 ** 11 FEB 2002 abhaduri N/A Added input parameters - Reduced Tax Credit,
23 Reduced Std Rate Cut Off and Benefit amt.
24 for P45 data archiving and display.
25 ** 26 JUN 2002 abhaduri N/A Added function get_calculated_period_values
26 for calculating tax credits and cut offs
27 according to user entered values and
28 period types.
29 ** 09-DEC-2002 smrobins N/A Added function get_pps_number, if no pps
30 number exists for the person record
31 linked to the assignment or if the tax
32 basis emergency no pps is specifically
33 selected for the assignment return 1.
34 ** 16-MAY-2003 nsugavan 2943335 Added function Valid_Work_incidents.
35 ** This would return true if the work incident
36 ** entered on the screen exists for the person
37 ** 04-jul-2003 asengar 3030621 Added two procedures insert_element_entry
38 ** and update_element_entry.
39 ** 30-JUL-2003 asengar 3030616 Added four functions get_weekly_tax_credit
40 ** get_weekly_std_rate_cut_off,get_monthly_tax_credit
41 ** get_monthly_std_rate_cut_off.
42 ** 09-FEB-2005 aashokan 4080773 Added a new procedure to create new tax record
43 ** if pay frequency is changed.
44 ** 10-FEB-2005 aashokan Modified dbdrv command.
45 ** 10-Feb-2005 vikgupta 4080773 Modified the proc update_paye_change_freq (included
46 ** P_DATETRACK_UPDATE_MODE)
47 ** 14-Feb-2005 skhandwa 4080773 Modified the proc update_paye_change_freq .
48 Passing all P45 items to hr_api.g_number. Also added
49 validation before Future Change.
50 ** 15-Feb-2005 vikgupta 4080773 Removed dbdrv checkfile for pyietaxd.sql
51 ** 22-Feb-2005 skhandwa 4080773 Added old_payroll_id variable handling
52 ** 15-Dec-2005 vikgupta 4878630 Modified the signature of update_paye_change_freq
53 ** for tax credit upload process.
54 ** 09-Jan-2006 vikgupta 5678929 Made decode_value_char, decode_value_date and
55 ** decode_value_number public
56 ** 19-Feb-2007 vikgupta SR 17140460.6, change the parameter passed to
57 ** update_paye_change_freq
58 ** 09-Apr-2007 rbhardwa 5867343 Modified code to include new functions get_paye_tax_basis,
59 ** get_diff_tax_basis and get_ie_exclude_tax_basis.
60 ** 05-May-2008 knadhan 6929566 Added new parameter to update_paye_change_frequency
61 ** 05-Dec-2008 rrajaman 7622221 Ireland Budget 2009 New formula function
62 ** 23-Dec-2008 rrajaman 7665572 get_age_date_paid for offset payroll support
63 ** 03-Dec-2009 rrajaman 9177545 added get_periods_between function.
64 ** 06-Sep-2010 vijranga 10078301 added get_ie_employer_info function.
65 ** 23-Sep-2010 vijranga 10078301 Removed gscc errors
66 ** 08-Nov-2010 vijranga 10254081 Added get_last_period function
67 ** 08-Nov-2011 rsahai 13359423 Proc update_paye_change_freq updated.
68 ** 23-Nov-2011 smeduri 13359545 added func get_usc_details
69 -------------------------------------------------------------------------------
70 */
71
72 Function get_old_payroll_id return number;
73 Procedure set_old_payroll_id(
74 p_old_payroll_id number
75 );
76 Procedure unset_old_payroll_id;
77
78
79 Function get_paye_tax_basis (p_assignment_id in number /* 5867343 */
80 ,p_payroll_action_id in number
81 ,p_tax_basis out nocopy varchar2)
82 return number;
83
84 Function get_diff_tax_basis(p_assignment_id in number /* 5867343 */
85 ,p_payroll_id in number
86 ,p_date_earned in date)
87 return number;
88
89
90 Function get_ie_exclude_tax_basis(p_assignment_id in number /* 5867343 */
91 ,p_payroll_id in number
92 ,p_date_earned in date)
93 return number;
94
95
96 Function get_paye_details( p_assignment_id in number
97 ,p_payroll_action_id in number
98 ,p_info_source out nocopy varchar2
99 ,p_tax_basis out nocopy varchar2
100 ,p_weekly_tax_credit out nocopy number
101 ,p_monthly_tax_credit out nocopy number
102 ,p_weekly_std_rate_cutoff out nocopy number
103 ,p_monthly_std_rate_cutoff out nocopy number
104 ,p_certificate_start_date out nocopy date
105 ,p_certificate_end_date out nocopy date
106 /*changes for SOE form requirements*/
107 ,p_assess_basis out nocopy varchar2
108 ,p_certificate_issue_date out nocopy date
109 /***********************************************/
110 ,p_reduced_tax_credit out nocopy number
111 ,p_reduced_std_rate_cutoff out nocopy number
112 ,p_benefit_amount out nocopy number)
113 return number;
114 -- 13359545
115 Function get_usc_details(p_assignment_id in number
116 ,p_payroll_action_id in number
117 ,p_tax_basis out nocopy varchar2
118 ,p_usc_tax_basis out nocopy varchar2
119 ,p_usc_rate_1 out nocopy number
120 ,p_usc_yrly_cutoff_1 out nocopy number
121 ,p_usc_mthly_cutoff_1 out nocopy number
122 ,p_usc_wkly_cutoff_1 out nocopy number
123 ,p_usc_rate_2 out nocopy number
124 ,p_usc_yrly_cutoff_2 out nocopy number
125 ,p_usc_mthly_cutoff_2 out nocopy number
126 ,p_usc_wkly_cutoff_2 out nocopy number
127 ,p_usc_rate_3 out nocopy number
128 ,p_usc_yrly_cutoff_3 out nocopy number
129 ,p_usc_mthly_cutoff_3 out nocopy number
130 ,p_usc_wkly_cutoff_3 out nocopy number
131 ,p_usc_rate_4 out nocopy number
132 ,p_usc_yrly_cutoff_4 out nocopy number
133 ,p_usc_mthly_cutoff_4 out nocopy number
134 ,p_usc_wkly_cutoff_4 out nocopy number
135 ,p_usc_rate_5 out nocopy number)
136 /*************************************************/
137 return number ;
138
139 Function get_payroll_details( p_payroll_id in number
140 ,p_payroll_action_id in number
141 ,p_period_num out nocopy number
142 ,p_payroll_type out nocopy varchar2)
143 return number;
144
145 Function get_calculated_period_values(p_period_type in varchar2,
146 p_period_ind in varchar2,
147 p_actual_value in number)
148 return number;
149
150 Function get_pps_number( p_assignment_id in number
151 ,p_payroll_action_id in number)
152 return number;
153 -- Bug 2943335 added function to see if work incident exist for the person
154 -- This would return true if the work incident
155 -- entered on the screen exists for the person
156 function Valid_Work_incidents
157 (p_assignment_id in number
158 ,p_date_earned in date
159 ,p_reference in varchar2)
160 return varchar2;
161
162 /* Added following two procedures for BUG 3030621 */
163 -- -------------------------------------------------------------------
164 -- procedure insert_element_entry
165 -- -------------------------------------------------------------------
166
167 procedure insert_element_entry
168 (
169 p_element_entry_id in number
170 );
171 --
172
173 -- -------------------------------------------------------------------
174 -- procedure update_element_entry
175 -- -------------------------------------------------------------------
176
177 procedure update_element_entry
178 (
179 p_element_entry_id in number
180 );
181 /* End of BUG 3030621 */
182 /*ADDED FOUR FUNCTIONS FOR BUG 3030616 */
183 function get_weekly_tax_credit
184 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
185 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
186 RETURN number;
187 --
188 function get_weekly_std_rate_cut_off
189 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
190 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
191 RETURN number;
192 --
193 function get_monthly_tax_credit
194 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
195 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
196 RETURN number;
197 --
198 function get_monthly_std_rate_cut_off
199 (p_assignment_id in pay_ie_paye_details_f.ASSIGNMENT_ID%TYPE,
200 p_tax_basis in pay_ie_paye_details_f.TAX_BASIS%TYPE)
201 RETURN number;
202 /* End of BUG 3030616 */
203
204 /*Bug 4080773*/
205 PROCEDURE update_paye_change_freq(p_assignment_id number
206 ,p_effective_date date
207 ,p_payroll_id number
208 ,P_DATETRACK_UPDATE_MODE VARCHAR2
209 ,p_tax_upload_flag varchar2 default 'X'
210 ,p_tax_basis varchar2 default null
211 ,p_cert_start_date date default null -- 17140460.6
212 ,p_cert_end_date date default null
213 ,p_weekly_tax_credit number default null
214 ,p_monthly_tax_credit number default null
215 ,p_weekly_std_rate_cut_off number default null
216 ,p_monthly_std_rate_cut_off number default null
217 ,p_tax_deducted_to_date number default null
218 ,p_pay_to_date number default null
219 ,p_cert_date date
220 --13359423
221 ,p_yrly_tax_cred in number default null
222 ,p_yrly_tax_rate_1 in number default null
223 ,p_yrly_tax_rate_2 in number default null
224 ,p_mthly_tax_rate_2 in number default null
225 ,p_wkly_tax_rate_2 in number default null
226 ,p_tax_rate_3 in number default null
227 ,p_yrly_tax_rate_3 in number default null
228 ,p_mthly_tax_rate_3 in number default null
229 ,p_wkly_tax_rate_3 in number default null
230 ,p_tax_rate_4 in number default null
231 ,p_yrly_tax_rate_4 in number default null
232 ,p_mthly_tax_rate_4 in number default null
233 ,p_wkly_tax_rate_4 in number default null
234 ,p_tax_rate_5 in number default null
235 ,p_in_exempt_usc in varchar2 default null
236 ,p_total_usc_pay_todate in number default null
237 ,p_total_usc_tax_todate in number default null
238 ,p_usc_rate_1 in number default null
239 ,p_usc_yrly_cutoff_1 in number default null
240 ,p_usc_mthly_cutoff_1 in number default null
241 ,p_usc_wkly_cutoff_1 in number default null
242 ,p_usc_rate_2 in number default null
243 ,p_usc_yrly_cutoff_2 in number default null
244 ,p_usc_mthly_cutoff_2 in number default null
245 ,p_usc_wkly_cutoff_2 in number default null
246 ,p_usc_rate_3 in number default null
247 ,p_usc_yrly_cutoff_3 in number default null
248 ,p_usc_mthly_cutoff_3 in number default null
249 ,p_usc_wkly_cutoff_3 in number default null
250 ,p_usc_rate_4 in number default null
251 ,p_usc_yrly_cutoff_4 in number default null
252 ,p_usc_mthly_cutoff_4 in number default null
253 ,p_usc_wkly_cutoff_4 in number default null
254 ,p_usc_rate_5 in number default null
255 ,p_usc_tax_basis in varchar2 default null
256 ,p_usc_info_source in varchar2 default null
257 --13359423
258 ); -- Bug 6929566
259
260 function decode_value_char(p_expression boolean,
261 p_true varchar2,
262 p_false varchar2) return varchar2;
263
264 function decode_value_date(p_expression boolean,
265 p_true date,
266 p_false date) return date;
267
268 function decode_value_number(p_expression boolean,
269 p_true number,
270 p_false number) return number;
271
272 FUNCTION get_age_payroll_period(p_assignment_id IN NUMBER
273 ,p_payroll_id IN NUMBER
274 ,p_date_earned IN DATE) RETURN NUMBER;
275
276 FUNCTION get_age_paid_year(p_assignment_id number,
277 p_payroll_action_id number) RETURN NUMBER;
278
279 FUNCTION get_periods_between(p_payroll_id number,
280 p_start_date date,
281 p_end_date date) RETURN NUMBER;
282
283 /* 10078301 fix */
284 Function get_ie_employer_info(p_tax_unit_id in number
285 , p_tax_dist_ref out nocopy varchar2
286 , p_employer_paye_ref out nocopy varchar2
287 , p_employer_trading_name out nocopy varchar2
288 , p_emp_tax_ref_contact out nocopy varchar2
289 , p_health_levy_refund out nocopy varchar2
290 ) return number;
291
292 /* 10254081 fix */
293 FUNCTION get_last_period( p_payroll_id in number
294 ,p_payroll_action_id in number
295 ,p_period_type in varchar2
296 ,p_last_period_num out nocopy number)
297 return number;
298
299 end;
300