1 PACKAGE pay_us_payroll_utils AUTHID CURRENT_USER AS
2 /* $Header: pyusutil.pkh 120.1.12020000.2 2012/07/05 03:56:57 amnaraya ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
20 Name : pay_us_payroll_utils
17 * *
18 ******************************************************************
19
21
22 Description : The package has all the common packages used in
23 US Payroll.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 24-APR-2003 ahanda 115.0 Created.
30 30-MAY-2003 vinaraya 115.1 2973119 Added code for city tax information
31 in populate_jit_information and
32 check for county and city tax in
33 get_tax_exists.
34 05-JUN-2003 vnatari 115.2 2938540 Overloaded function get_tax_exists.
35 18-jun-2003 sodhingr 115.3 3011003 Added the cursor c_get_defined_balance_id
36 06-AUG-2003 sshetty 115.4 Added a field dcp_limit to record type
37 fed_tax_info_rec.
38 29-AUG-2003 rsethupa 115.5 2527077 Added functions formated_header_string
39 and formated_data_string which will be
40 used by reports in HTML and CSV formats.
41 13-NOV-2003 tclewis 115.6 Added structure for STEIC to state
42 structure.
43 18-MAR-2004 sdahiya 115.7 Added p_legislation_code parameter to
44 check_balance_status.
45 26-APR-2004 ahanda 115.8 Added function get_parameter.
46 08-NOV-2004 ahanda 115.9 defaulted p_bold to 'N'
47 20-DEC-2004 schauhan 115.10 3892148 Added fucntion ssn_reporting_preferences.
48 12-JAN-2005 ahanda 115.11 3980866 Added check for FUTA at state level for
49 the mentioned bug.
50 19-APR-2005 ahanda 115.13 Added a new function get_min_action
51 to get the min assignment_action_id
52 for a given business_group, GRE, payroll
53 and dates.
54 06-MAR-2009 tclewis 115.14 Added SDI1_EE_wage_limit to the state
55 record.
56 29-FEB-2012 ybudamal 115.15 13715397 Added procedure update_state_or_local_wh_link.
57 *****************************************************************************/
58
59 /*****************************************************************************
60 Name : populate_jit_information
61 Purpose : This procedure populates a PL/SQL table with JIT information
62 Arguments :
63 Notes :
64 *****************************************************************************/
65 PROCEDURE populate_jit_information(
66 p_effective_date in date default sysdate
67 ,p_get_federal in varchar2 default 'N'
68 ,p_get_state in varchar2 default 'N'
69 ,p_get_county in varchar2 default 'N'
70 ,p_get_city in varchar2 default 'N'
71 ,p_jurisdiction_code in varchar2 default NULL);
72
73 /********************************************************************
74 ** Function : get_tax_exists
75 ** Arguments: p_jurisdiction_code
76 ** p_tax_type
77 ** Returns : Y/N
78 ** Purpose : This function has 2 parameters as input. The function
79 ** gets the effective_date from fnd_sessions. If the date
80 ** in fnd_sessions is not found, get the data as of sysdate.
81 *********************************************************************/
82 FUNCTION get_tax_exists (p_jurisdiction_code in varchar2 default '00-000-0000'
83 ,p_tax_type in varchar2)
84 RETURN varchar2;
85
86 /********************************************************************
87 ** Function : get_tax_exists
88 ** Arguments: p_jurisdiction_code
89 ** p_tax_type
90 ** p_effective_date
91 ** Returns : Y/N
92 ** Purpose : This function has 3 parameters as input. The function
93 ** gets the data as of the effective_date passed to it.
94 *********************************************************************/
95 FUNCTION get_tax_exists (p_jurisdiction_code in varchar2 default '00-000-0000'
96 ,p_tax_type in varchar2
97 ,p_effective_date in date)
98 RETURN varchar2;
99
100 /********************************************************************
101 ** Global PL/SQL Tables
102 *********************************************************************/
103 TYPE fed_tax_info_rec IS RECORD
104 (futa_wage NUMBER
105 ,futa_rate NUMBER
106 ,ss_ee_wage NUMBER
107 ,ss_ee_rate NUMBER
108 ,ss_er_wage NUMBER
109 ,ss_er_rate NUMBER
110 ,med_ee_rate NUMBER
111 ,med_er_rate NUMBER
112 ,p401_limit NUMBER
113 ,p403_limit NUMBER
114 ,p457_limit NUMBER
115 ,catchup_401k NUMBER
116 ,catchup_403b NUMBER
117 ,catchup_457 NUMBER
118 ,dcp_limit NUMBER
119 );
120
121 TYPE fed_tax_info_table IS TABLE OF
122 fed_tax_info_rec
123 INDEX BY BINARY_INTEGER;
124
125 TYPE state_tax_info_rec IS RECORD
126 ( sit_exists varchar2(1)
127 ,sui_ee_limit NUMBER
128 ,sui_er_limit NUMBER
129 ,sdi_ee_limit NUMBER
130 ,sdi_er_limit NUMBER
131 ,steic_exists VARCHAR2(1)
132 ,futa_rate NUMBER
136 TYPE state_tax_info_table IS TABLE OF
133 ,sdi1_ee_limit NUMBER
134 );
135
137 state_tax_info_rec
138 INDEX BY BINARY_INTEGER;
139
140 TYPE county_tax_info_rec IS RECORD
141 ( jurisdiction_code varchar2(11)
142 ,cnty_tax_exists varchar2(1)
143 ,cnty_head_tax_exists varchar2(1)
144 ,cnty_sd_tax_exists varchar2(1)
145 );
146
147 TYPE county_tax_info_table IS TABLE OF
148 county_tax_info_rec
149 INDEX BY BINARY_INTEGER;
150
151 /************************ Bug 2971119 Changes Start ***********************
152 ***** Added code to populate city information into pl/sql table **************
153 *****************************************************************************/
154
155 TYPE city_tax_info_rec IS RECORD
156 ( jurisdiction_code varchar2(11)
157 , city_tax_exists varchar2(1)
158 , city_head_tax_exists varchar2(1)
159 , city_sd_tax_exists varchar2(1)
160 );
161
162 TYPE city_tax_info_table IS TABLE OF
163 city_tax_info_rec
164 INDEX BY BINARY_INTEGER;
165
166 ltr_city_tax_info city_tax_info_table;
167
168 /********************** Bug 2973119 Changes End ***************************/
169
170 ltr_fed_tax_info fed_tax_info_table;
171 ltr_state_tax_info state_tax_info_table;
172 ltr_county_tax_info county_tax_info_table;
173
174 /*****************************************************************************
175 Name : check_balance_status
176 Purpose : Function should be used to identify whether the balances relevant
177 to partcular attribute are valid for use of BRA.
178 Arguments : 1. Start Date
179 2. Business Group Id
180 3. Atttribute Name
181 4. Legislation Code
182 Return : 'Y' for valid status and 'N' for invalid status of balance
183 Notes : It will used by group level reports (940,941,GRE Totals etc) to find
184 if all the balances related to a report are valid or not
185 *****************************************************************************/
186 FUNCTION check_balance_status(
187 p_start_date in date,
188 p_business_group_id in hr_organization_units.organization_id%type,
189 p_attribute_name in varchar2,
190 p_legislation_code in varchar2 default 'US')
191 RETURN VARCHAR2;
192
193 /*****************************************************************************
194 Name : c_get_defined_balance_id
195 Purpose : This is the cursor to get the defined balance id
196 and user entity name for given balance name and the dimension in
197 a business group. This is mainly used by year end archiver.
198 Arguments : 1. Balance Dimension
199 3. Business Group Id
200 Return : Balance name, defined balance id and user entity name
201 Notes : It will used by group level reports (940,941,GRE Totals etc) to find
202 if all the balances related to a report are valid or not
203 *****************************************************************************/
204 CURSOR c_get_defined_balance_id (
205 cp_balance_name in varchar2,
206 cp_balance_dimension in varchar2,
207 cp_business_group_id in number ) is
208 select pdb.defined_balance_id,fue.user_entity_name
209 from ff_user_entities fue,
210 pay_defined_balances pdb,
211 pay_balance_dimensions pbd,
212 pay_balance_types pbt
213 where pbt.balance_name in (cp_balance_name)
214 and pbd.database_item_suffix= cp_balance_dimension
215 and pbt.balance_type_id = pdb.balance_type_id
216 and pbd.balance_dimension_id = pdb.balance_dimension_id
217 and fue.creator_id = pdb.defined_balance_id
218 and fue.creator_type = 'B'
219 and ((pbt.legislation_code = 'US' and
220 pbt.business_group_id is null)
221 or (pbt.legislation_code is null and
222 pbt.business_group_id = cp_business_group_id))
223 and ((pbd.legislation_code ='US' and
224 pbd.business_group_id is null)
225 or (pbd.legislation_code is null and
226 pbd.business_group_id = cp_business_group_id)) ;
227
228 /************************************************************
229 ** Function : formated_header_string
230 ** Arguments: p_input_string
231 ** p_output_file_type
232 ** Returns : input string with the HTML Header tags
233 ** Purpose : This Function will be used by reports that are
234 ** displaying in HTML format. It returns the input
235 ** string with the HTML Header tags
236 ************************************************************/
237 FUNCTION formated_header_string
238 (p_input_string in VARCHAR2
239 ,p_output_file_type in VARCHAR2
240 )
241 RETURN VARCHAR2;
242
243 /***********************************************************
244 ** Function : formated_data_string
245 ** Arguments: p_input_string
246 ** p_output_file_type
247 ** p_bold
248 ** Returns : the formated input string based on the Output
249 ** format. If the format is CSV then the values are
250 ** returned seperated by comma (,). If the format is
251 ** HTML then the returned string has the HTML tags.
252 ** The parameter p_bold only works for the HTML
253 ** format.
254 ** Purpose : This Function will be used by reports that are
255 ** displaying in HTML/CSV format.
256 ************************************************************/
257 FUNCTION formated_data_string
258 (p_input_string in VARCHAR2
259 ,p_output_file_type in VARCHAR2
260 ,p_bold in VARCHAR2 default 'N'
261 )
262 RETURN VARCHAR2;
263
264 /**************************************************************************
265 ** Function : get_parameter
266 ** Arguments: p_param_name
267 ** p_parameter_list
268 ** Returns : the the value for the parameter p_param_name
269 ** from the p_parameter_list
270 ** This function is called to get the value entered
271 ** by the user which is stored in legislative
272 ** parameters. Both the name and list is passed to
273 ** the function.
274 **************************************************************************/
275 FUNCTION get_parameter(p_parameter_name in varchar2,
276 p_parameter_list in varchar2)
277 RETURN VARCHAR2;
278
279
280 /**************************************************************************
281 ** Function : ssn_reporting_preferences
282 ** Arguments: p_loc_id location Id,
283 ** p_org_id organization Id,
284 ** p_bg_id business group Id
285 ** Returns : The value for the parameter lv_display_ssn
286 **
287 ** This function is called is called from Check Writer,
288 ** Deposit Advice and Archive Check WRiter and Deposit
289 ** Advice Reports.It is supposed to return if we want to
290 ** show SSN on the output of these reoprts or not.
291 ** The Function checks the value set by the user at location
292 ** then organization and finally at BG level.It was added
293 ** for Bug 3892148.
294 **************************************************************************/
295 FUNCTION ssn_reporting_preferences(p_loc_id in number,
296 p_org_id in number,
297 p_bg_id in number)
298 RETURN VARCHAR2;
299
300
301 /**************************************************************************
302 ** Function: get_min_actions
303 ** Argument:
304 ** Description:
305 **************************************************************************/
306 FUNCTION get_min_action(p_business_group_id in number
307 ,p_start_date in date
308 ,p_end_date in date
309 ,p_tax_unit_id in number default null
310 ,p_payroll_id in number default null)
311 RETURN NUMBER;
312
313
314 /*****************************************************************************
315 Name : update_state_or_local_wh_link
316 Purpose : This procedure updates the state W4 withholding link and local
317 tax form link for the State if local link exists. Otherwise
318 creates a new local tax form link.
319 Arguments : p_state - Name of the State
320 p_state_or_local_tax_form - State/Local form to update
321 p_link - New Link to be used
322 Notes :
323 *****************************************************************************/
324
325 PROCEDURE update_state_or_local_wh_link(errbuf OUT NOCOPY VARCHAR2,
326 retcode OUT NOCOPY NUMBER,
327 p_state_or_local_tax_form IN VARCHAR2,
328 p_state IN VARCHAR2,
329 p_link IN VARCHAR2);
330
331 end pay_us_payroll_utils;
[Home] [Help]