1 PACKAGE pay_us_payroll_utils AS
2 /* $Header: pyusutil.pkh 120.0.12000000.1 2007/01/18 03:09:25 appldev noship $ */
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. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_payroll_utils
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
55 *****************************************************************************/
56
57 /*****************************************************************************
58 Name : populate_jit_information
59 Purpose : This procedure populates a PL/SQL table with JIT information
60 Arguments :
61 Notes :
62 *****************************************************************************/
63 PROCEDURE populate_jit_information(
64 p_effective_date in date default sysdate
65 ,p_get_federal in varchar2 default 'N'
66 ,p_get_state in varchar2 default 'N'
67 ,p_get_county in varchar2 default 'N'
68 ,p_get_city in varchar2 default 'N'
69 ,p_jurisdiction_code in varchar2 default NULL);
70
71 /********************************************************************
72 ** Function : get_tax_exists
73 ** Arguments: p_jurisdiction_code
74 ** p_tax_type
75 ** Returns : Y/N
76 ** Purpose : This function has 2 parameters as input. The function
77 ** gets the effective_date from fnd_sessions. If the date
78 ** in fnd_sessions is not found, get the data as of sysdate.
79 *********************************************************************/
80 FUNCTION get_tax_exists (p_jurisdiction_code in varchar2 default '00-000-0000'
81 ,p_tax_type in varchar2)
82 RETURN varchar2;
83
84 /********************************************************************
85 ** Function : get_tax_exists
86 ** Arguments: p_jurisdiction_code
87 ** p_tax_type
88 ** p_effective_date
89 ** Returns : Y/N
90 ** Purpose : This function has 3 parameters as input. The function
91 ** gets the data as of the effective_date passed to it.
92 *********************************************************************/
93 FUNCTION get_tax_exists (p_jurisdiction_code in varchar2 default '00-000-0000'
94 ,p_tax_type in varchar2
95 ,p_effective_date in date)
96 RETURN varchar2;
97
98 /********************************************************************
99 ** Global PL/SQL Tables
100 *********************************************************************/
101 TYPE fed_tax_info_rec IS RECORD
102 (futa_wage NUMBER
103 ,futa_rate NUMBER
104 ,ss_ee_wage NUMBER
105 ,ss_ee_rate NUMBER
106 ,ss_er_wage NUMBER
107 ,ss_er_rate NUMBER
108 ,med_ee_rate NUMBER
109 ,med_er_rate NUMBER
110 ,p401_limit NUMBER
111 ,p403_limit NUMBER
112 ,p457_limit NUMBER
113 ,catchup_401k NUMBER
114 ,catchup_403b NUMBER
115 ,catchup_457 NUMBER
116 ,dcp_limit NUMBER
117 );
118
119 TYPE fed_tax_info_table IS TABLE OF
120 fed_tax_info_rec
121 INDEX BY BINARY_INTEGER;
122
123 TYPE state_tax_info_rec IS RECORD
124 ( sit_exists varchar2(1)
125 ,sui_ee_limit NUMBER
126 ,sui_er_limit NUMBER
127 ,sdi_ee_limit NUMBER
128 ,sdi_er_limit NUMBER
129 ,steic_exists VARCHAR2(1)
130 ,futa_rate NUMBER
131 );
132
133 TYPE state_tax_info_table IS TABLE OF
134 state_tax_info_rec
135 INDEX BY BINARY_INTEGER;
136
137 TYPE county_tax_info_rec IS RECORD
138 ( jurisdiction_code varchar2(11)
139 ,cnty_tax_exists varchar2(1)
140 ,cnty_head_tax_exists varchar2(1)
141 ,cnty_sd_tax_exists varchar2(1)
142 );
143
144 TYPE county_tax_info_table IS TABLE OF
145 county_tax_info_rec
146 INDEX BY BINARY_INTEGER;
147
148 /************************ Bug 2971119 Changes Start ***********************
149 ***** Added code to populate city information into pl/sql table **************
150 *****************************************************************************/
151
152 TYPE city_tax_info_rec IS RECORD
153 ( jurisdiction_code varchar2(11)
154 , city_tax_exists varchar2(1)
155 , city_head_tax_exists varchar2(1)
156 , city_sd_tax_exists varchar2(1)
157 );
158
159 TYPE city_tax_info_table IS TABLE OF
160 city_tax_info_rec
161 INDEX BY BINARY_INTEGER;
162
163 ltr_city_tax_info city_tax_info_table;
164
165 /********************** Bug 2973119 Changes End ***************************/
166
167 ltr_fed_tax_info fed_tax_info_table;
168 ltr_state_tax_info state_tax_info_table;
169 ltr_county_tax_info county_tax_info_table;
170
171 /*****************************************************************************
172 Name : check_balance_status
173 Purpose : Function should be used to identify whether the balances relevant
174 to partcular attribute are valid for use of BRA.
175 Arguments : 1. Start Date
176 2. Business Group Id
177 3. Atttribute Name
178 4. Legislation Code
179 Return : 'Y' for valid status and 'N' for invalid status of balance
180 Notes : It will used by group level reports (940,941,GRE Totals etc) to find
181 if all the balances related to a report are valid or not
182 *****************************************************************************/
183 FUNCTION check_balance_status(
184 p_start_date in date,
185 p_business_group_id in hr_organization_units.organization_id%type,
186 p_attribute_name in varchar2,
187 p_legislation_code in varchar2 default 'US')
188 RETURN VARCHAR2;
189
190 /*****************************************************************************
191 Name : c_get_defined_balance_id
192 Purpose : This is the cursor to get the defined balance id
193 and user entity name for given balance name and the dimension in
194 a business group. This is mainly used by year end archiver.
195 Arguments : 1. Balance Dimension
196 3. Business Group Id
197 Return : Balance name, defined balance id and user entity name
198 Notes : It will used by group level reports (940,941,GRE Totals etc) to find
199 if all the balances related to a report are valid or not
200 *****************************************************************************/
201 CURSOR c_get_defined_balance_id (
202 cp_balance_name in varchar2,
203 cp_balance_dimension in varchar2,
204 cp_business_group_id in number ) is
205 select pdb.defined_balance_id,fue.user_entity_name
206 from ff_user_entities fue,
207 pay_defined_balances pdb,
208 pay_balance_dimensions pbd,
209 pay_balance_types pbt
210 where pbt.balance_name in (cp_balance_name)
211 and pbd.database_item_suffix= cp_balance_dimension
212 and pbt.balance_type_id = pdb.balance_type_id
213 and pbd.balance_dimension_id = pdb.balance_dimension_id
214 and fue.creator_id = pdb.defined_balance_id
215 and fue.creator_type = 'B'
216 and ((pbt.legislation_code = 'US' and
217 pbt.business_group_id is null)
218 or (pbt.legislation_code is null and
219 pbt.business_group_id = cp_business_group_id))
220 and ((pbd.legislation_code ='US' and
221 pbd.business_group_id is null)
222 or (pbd.legislation_code is null and
223 pbd.business_group_id = cp_business_group_id)) ;
224
225 /************************************************************
226 ** Function : formated_header_string
227 ** Arguments: p_input_string
228 ** p_output_file_type
229 ** Returns : input string with the HTML Header tags
230 ** Purpose : This Function will be used by reports that are
231 ** displaying in HTML format. It returns the input
232 ** string with the HTML Header tags
233 ************************************************************/
234 FUNCTION formated_header_string
235 (p_input_string in VARCHAR2
236 ,p_output_file_type in VARCHAR2
237 )
238 RETURN VARCHAR2;
239
240 /***********************************************************
241 ** Function : formated_data_string
242 ** Arguments: p_input_string
243 ** p_output_file_type
244 ** p_bold
245 ** Returns : the formated input string based on the Output
246 ** format. If the format is CSV then the values are
247 ** returned seperated by comma (,). If the format is
248 ** HTML then the returned string has the HTML tags.
249 ** The parameter p_bold only works for the HTML
250 ** format.
251 ** Purpose : This Function will be used by reports that are
252 ** displaying in HTML/CSV format.
253 ************************************************************/
254 FUNCTION formated_data_string
255 (p_input_string in VARCHAR2
256 ,p_output_file_type in VARCHAR2
257 ,p_bold in VARCHAR2 default 'N'
258 )
259 RETURN VARCHAR2;
260
261 /**************************************************************************
262 ** Function : get_parameter
263 ** Arguments: p_param_name
264 ** p_parameter_list
265 ** Returns : the the value for the parameter p_param_name
266 ** from the p_parameter_list
267 ** This function is called to get the value entered
268 ** by the user which is stored in legislative
269 ** parameters. Both the name and list is passed to
270 ** the function.
271 **************************************************************************/
272 FUNCTION get_parameter(p_parameter_name in varchar2,
273 p_parameter_list in varchar2)
274 RETURN VARCHAR2;
275
276
277 /**************************************************************************
278 ** Function : ssn_reporting_preferences
279 ** Arguments: p_loc_id location Id,
280 ** p_org_id organization Id,
281 ** p_bg_id business group Id
282 ** Returns : The value for the parameter lv_display_ssn
283 **
284 ** This function is called is called from Check Writer,
285 ** Deposit Advice and Archive Check WRiter and Deposit
286 ** Advice Reports.It is supposed to return if we want to
287 ** show SSN on the output of these reoprts or not.
288 ** The Function checks the value set by the user at location
289 ** then organization and finally at BG level.It was added
290 ** for Bug 3892148.
291 **************************************************************************/
292 FUNCTION ssn_reporting_preferences(p_loc_id in number,
293 p_org_id in number,
294 p_bg_id in number)
295 RETURN VARCHAR2;
296
297
298 /**************************************************************************
299 ** Function: get_min_actions
300 ** Argument:
301 ** Description:
302 **************************************************************************/
303 FUNCTION get_min_action(p_business_group_id in number
304 ,p_start_date in date
305 ,p_end_date in date
306 ,p_tax_unit_id in number default null
307 ,p_payroll_id in number default null)
308 RETURN NUMBER;
309
310 end pay_us_payroll_utils;
[Home] [Help]