1 PACKAGE hr_us_ff_udf1 AUTHID CURRENT_USER AS
2 /* $Header: pyusudf1.pkh 120.28 2012/01/05 07:22:58 emunisek noship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1993 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : hr_us_ff_udf1
11 Filename : pyusudf1.pkh
12 Change List
13 -----------
14 Date Name Vers Bug No Description
15 ---- ---- ---- ------ -----------
16 19-AUG-02 TCLEWIS 115.0 Created
17 10-Oct-02 EKIM 115.2 2522002 Added functions
18 neg_earning, calc_earning
19 06-Aug-03 VMEHTA 115.4 Corrected the definition
20 (parameters) for
21 get_prev_ptd_values
22 15-APR-03 RMONGE 115.6 3562306 Added decimal places
23 to neg_earn_rec definition
24 for temp_earn,
25 reduced_neg_earn,
26 neg_earn_feed to 15,2
27 30-APR-04 TCLEWIS 115.7 Added functions
28 get_work_jurisdictions
29 and
30 Jurisdiction_processed
31 07-JUL-04 TCLEWIS 115.10 Changed plsql tables to be
32 indexed by binary integer.
33 version 115.7 was leap
34 frogged to 115.9, so
35 implementd 116.7 changes.
36
37 02-AUG-04 TCLEWIS 115.11 Added GET_JD_PERCENT.
38 09-APR-05 PPANDA 115.12 421122 New column jd_type added to record
39 type jd_record . This column will
40 denote types of jurisdiction associated
41 with the assignment.
42 JD_TYPE is added to deonte the Jurisdiction_Type
43 Following notation would be used for this
44 Residence -> RS
45 Work -> WK
46 Residence as well as Work -> RW
47 Residence as well as Tagged -> RT
48 Tagged Earnings -> TG
49
50 A new function get_jurisdiction_type
51 added to fetch the value jurisdiction type
52 12-JUN-05 SCHAUHAN 115.13 4194339 Added Function get_executive_status.
53 20-AUG-05 SAIKRISH 115.14 4532107 Added get_it_work_jurisdictions,
54 get_jd_level_threshold,get_th_assignment for
55 Consultant Taxation.
56 13-SEP-05 SAIKRISH 115.15 4532107 Added p_assignment_id parameter for
57 get_jd_tax_balance.
58 15-SEP-05 SAIKRISH 115.20 4532107 Changed spec for get_jd_tax_balance
59 30-SEP-05 SAIKRISH 115.21 4638194 Added Person_id to get_person_it_hours
60 03-APR-06 PPANDA 115.22 4715851 Few session variables were defined to fix the
61 Enhanced tax interface issue on local tax.
62 02-NOV-06 SSOURESR 115.24 Removed the variables from update 115.22
63 23-JAN-07 SAIKRISH 115.25 5722893 Added new function get_jit_data.
64 07-MAR-07 SAIKRISH 115.26 Added new function get_rs_jd,get_wk_jd
65 10-MAR-08 jdevasah 115.76 2122611 Added new function get_wc_flag.
66 10-APR-08 sjawid 115.77 6899939 Added new function parameter p_get_regular_wage to
67 get_prev_ptd_values
68 09-May-08 Pannapur 115.31 5972214 Added new function get_max_perc
69 13-May-08 Pannapur 115.32 Reverted get_max_perc
70 08-Aug-08 Pannapur 115.33 7238809 Added new function parameter per_adr_geocode to
71 get_prev_ptd_values
72 19-Dec-08 emunisek 115.34 5972214 Added new function coloradocity_ht_collectornot
73 14-May-09 emunisek 115.36 8406097 Added new parameters p_payroll_action_id number,
74 p_monthly_gross to coloradocity_ht_collectornot
75 function
76 18-Aug-11 emunisek 115.37 12583094 Added new functions count_remaining_pay_periods
77 and fnd_canonical_to_date.
78 23-Aug-11 tclewis 115.38 Added code for PA Act 32. New function
79 GET_PSD_JD_CODE.
80 05-Jan-12 emunisek 115.40 12618403 Added new function get_ht_withheld_per_jd_month
81 =============================================================================================
82
83 */
84
85 --
86 TYPE neg_earn_rec IS RECORD
87 ( temp_earn number(15,2),
88 reduced_neg_earn number(15,2),
89 neg_earn_feed number(15,2));
90
91 TYPE neg_earn_tab IS TABLE OF neg_earn_rec
92 INDEX BY BINARY_INTEGER;
93
94 l_neg_earn_tab neg_earn_tab;
95
96 type jd_record is record (
97 Jurisdiction_code pay_us_emp_state_tax_rules_f.jurisdiction_code%type
98 ,percentage NUMBER
99 ,jd_type VARCHAR2(2)
100 ,hours NUMBER
101 ,wages_to_accrue_flag VARCHAR2(4)
102 ,tg_hours NUMBER
103 ,other_pay_hours NUMBER
104 );
105 --
106 -- JD_TYPE is added to deonte the Jurisdiction_Type
107 -- Following notation would be used for this
108 -- Residence -> RS
109 -- Work -> WK
110 -- Residence as well as Work -> RW
111 -- Residence as well as Tagged -> RT
112 -- Tagged Earnings -> TG
113 -- Informational Time -> IT
114 --
115 -- wages_to_accrue_flag
116 --
117 -- AIHW -> Accumulate Information Hours and Wage
118 -- AIHO -> Accumulate only Information Hours
119 -- IHNA -> Accumulation of Information Hours Not Applicable
120
121 type jurisdiction_table
122 is table of jd_record
123 index by BINARY_INTEGER;
124
125 type state_processed_table
126 is table of varchar2(1)
127 index by BINARY_INTEGER;
128
129 type county_processed_table
130 is table of varchar2(1)
131 index by BINARY_INTEGER;
132
133 type city_processed_table
134 is table of varchar2(1)
135 index by BINARY_INTEGER;
136
137 state_processed_tbl state_processed_table;
138 county_processed_tbl county_processed_table;
139 city_processed_tbl city_processed_table;
140
141 jurisdiction_codes_tbl jurisdiction_table;
142 jurisdiction_codes_tbl_stg jurisdiction_table;
143
144 res_jurisdiction_codes_tbl jurisdiction_table;
145
146 /* For threshold following details are defined */
147 jd_codes_tbl_city_stg jurisdiction_table;
148
149 type inform_hour_jd_record is record (
150 Jurisdiction_code pay_us_emp_state_tax_rules_f.jurisdiction_code%type
151 ,percentage NUMBER
152 ,hours NUMBER
153 ,wages_to_accrue_flag VARCHAR2(4)
154 ,calc_percent varchar2(10)
155 ,threshold_hours NUMBER);
156
157 type inform_hours_summary_table
158 is table of inform_hour_jd_record
159 index by BINARY_INTEGER;
160
161 jd_codes_tbl_state_stg inform_hours_summary_table;
162 jd_codes_tbl_state inform_hours_summary_table;
163 jd_codes_tbl_county_stg inform_hours_summary_table;
164 jd_codes_tbl_county inform_hours_summary_table;
165
166 -- This flag to be used in get_jd_percent to branch the code for deriving W4 percentage
167 -- or information hours percentage. This flag would be set to true when assignment is
168 -- configured for processing information hours element entries
169 --
170 g_use_it_flag VARCHAR2(1):= 'N';
171
172 FUNCTION calc_earning(p_template_earning number, -- Parameter
173 p_addl_asg_gre_itd number, -- Parameter
174 p_neg_earn_asg_gre_itd number) -- Parameter
175 RETURN NUMBER;
176
177 FUNCTION neg_earning RETURN NUMBER;
178
179 /*Function added for 6899939*/
180 FUNCTION get_prev_ptd_values(
181 p_assignment_action_id number, -- Context
182 p_tax_unit_id number, -- Context
183 p_jurisdiction_code varchar2, -- Context
184 p_fed_or_state varchar2, -- Parameter
185 p_regular_aggregate number, -- Parameter
186 calc_PRV_GRS OUT nocopy number, -- Paramter
187 calc_PRV_TAX OUT nocopy number )
188 RETURN NUMBER;
189
190 /*Function added for 7238809*/
191 FUNCTION get_prev_ptd_values(
192 p_assignment_action_id number, -- Context
193 p_tax_unit_id number, -- Context
194 p_jurisdiction_code varchar2, -- Context
195 p_fed_or_state varchar2, -- Parameter
196 p_regular_aggregate number, -- Parameter
197 calc_PRV_GRS OUT nocopy number, -- Paramter
198 calc_PRV_TAX OUT nocopy number,
199 p_get_regular_wage varchar2 )
200 RETURN NUMBER;
201
202 FUNCTION get_prev_ptd_values(
203 p_assignment_action_id number, -- Context
204 p_tax_unit_id number, -- Context
205 p_jurisdiction_code varchar2, -- Context
206 p_fed_or_state varchar2, -- Parameter
207 p_regular_aggregate number, -- Parameter
208 calc_PRV_GRS OUT nocopy number, -- Paramter
209 calc_PRV_TAX OUT nocopy number,
210 p_get_regular_wage varchar2, -- Paramter /*6899939*/
211 per_adr_geocode varchar2 ) -- Parameter /*7238809*/
212 RETURN NUMBER;
213
214 FUNCTION get_work_jurisdictions(
215 p_assignment_action_id number -- Formula Context
216 ,p_INITIALIZE in varchar2 -- Parameter
217 ,p_jurisdiction_code in out nocopy varchar2 -- Parameter
218 ,p_percentage out nocopy number -- Parameter
219 )
220 RETURN varchar2;
221
222 FUNCTION get_it_work_jurisdictions(p_assignment_action_id NUMBER
223 ,p_initialize IN VARCHAR2
224 ,p_jurisdiction_code IN OUT NOCOPY VARCHAR2
225 ,p_percentage OUT NOCOPY NUMBER
226 ,p_assignment_id IN NUMBER
227 ,p_date_paid IN DATE
228 ,p_date_earned IN DATE
229 ,p_time_period_id IN NUMBER
230 ,p_payroll_id IN NUMBER
231 ,p_business_group_id IN NUMBER
232 ,p_tax_unit_id IN NUMBER
233 )
234 RETURN VARCHAR2;
235
236 FUNCTION Jurisdiction_processed (
237 p_jurisdiction_code in varchar2 -- Paramter
238 ,p_jd_level in varchar -- Paramter
239 )
240 RETURN varchar2;
241
242 FUNCTION get_fed_prev_ptd_values(
243 p_assignment_action_id number, -- Context
244 p_tax_unit_id number, -- Context
245 p_fed_or_state varchar2, -- Parameter
246 p_regular_aggregate number, -- Parameter
247 calc_PRV_GRS OUT nocopy number, -- Parameter
248 calc_PRV_TAX OUT nocopy number) -- Parameter
249 RETURN NUMBER;
250
251 FUNCTION get_jd_percent(p_jurisdiction_code VARCHAR2 -- Parameter
252 ,p_jd_level VARCHAR2 -- Parameter
253 ,p_hours_to_accumulate OUT nocopy NUMBER -- Parameter
254 ,p_wages_to_accrue_flag OUT nocopy VARCHAR2 -- Parameter
255 )
256 RETURN number;
257
258 FUNCTION get_tax_jurisdiction(
259 p_assignment_id number -- Context
260 ,p_date_earned date -- Parameter
261 )
262 RETURN varchar2;
263 --
264 -- This function used to fetch the JD_TYPE set in the pl table for a given
265 -- jurisdiction
266 --
267 FUNCTION get_jurisdiction_type(p_jurisdiction_code varchar2 -- Parameter
268 )
269 RETURN varchar2;
270
271 --
272 -- This function is used to fetch the status of Employee. It is used for determining
273 -- whether executive weekly maximum should be applicable for a employee.
274 --
275 FUNCTION get_executive_status(p_assignment_id number,
276 p_date_earned date,
277 p_jurisdiction_code varchar2
278 )
279 RETURN varchar2;
280
281 FUNCTION get_wc_flag(p_assignment_id number,
282 p_date_earned date,
283 p_wc_flat_rate_period varchar2
284 )
285 RETURN varchar2;
286
287 --Function to return threshold informational hours for a given jurisdiction.
288 FUNCTION get_jd_level_threshold(p_tax_unit_id NUMBER
289 ,p_jurisdiction_code VARCHAR2
290 ,p_jd_level VARCHAR2)
291 RETURN NUMBER;
292
293 --Function to get balance value
294 FUNCTION get_jd_tax_balance(p_threshold_basis IN VARCHAR2
295 ,p_assignment_action_id IN NUMBER
296 ,p_jurisdiction_code IN VARCHAR2
297 ,p_tax_unit_id IN NUMBER
298 ,p_jurisdiction_level IN VARCHAR2
299 ,p_effective_date IN DATE
300 ,p_assignment_id IN NUMBER
301 ) RETURN NUMBER;
302
303 --Function to get Informational Hours logged by the assignment for
304 --each jurisdiction code in the pl table.
305 FUNCTION get_person_it_hours(p_person_id IN NUMBER
306 ,p_assignment_id IN NUMBER
307 ,p_jurisdiction_code IN VARCHAR2
308 ,p_jd_level IN VARCHAR2 --2,6,11
309 ,p_threshold_basis IN VARCHAR2 --YTD,RTD
310 ,p_effective_date IN DATE
311 ,p_end_date IN DATE) RETURN NUMBER;
312
313 --
314 -- This function would be used for fetching percentage to be used STATE and
315 -- COUNTY level percentage to be used for distributing wages over different
316 -- jurisdictions when assignment is configured to process information hours
317 --
318 FUNCTION get_it_jd_percent(p_jurisdiction_code VARCHAR2 -- parameter
319 ,p_jd_level VARCHAR2 -- parameter
320 ,p_hours_to_accumulate OUT nocopy NUMBER -- parameter
321 ,p_wages_to_accrue_flag OUT nocopy VARCHAR2 -- parameter
322 )
323 RETURN NUMBER;
324
325 FUNCTION across_calendar_years(p_payroll_action_id in number)
326 RETURN varchar2;
327
328 FUNCTION get_work_state (p_jurisdiction_code in varchar2)
329 RETURN varchar2;
330
331 --Function to return the SUI Wage Limits.
332 FUNCTION get_jit_data(p_jurisdiction_code IN VARCHAR2
333 ,p_date_earned IN DATE
334 ,p_jit_type IN VARCHAR2)
335 RETURN NUMBER;
336
337 FUNCTION get_rs_jd (p_assignment_id IN NUMBER,
338 p_date_earned IN DATE)
339 RETURN VARCHAR2;
340
341 FUNCTION get_wk_jd (p_assignment_id IN NUMBER,
342 p_date_earned IN DATE,
343 p_jurisdiction_code IN VARCHAR2)
344 RETURN VARCHAR2;
345
346 --Function to check if head tax can be deducted or not for the given Colorado City
347 FUNCTION coloradocity_ht_collectornot(p_assignment_id number, --Context
348 p_date_earned date, --Context
349 p_payroll_action_id number, --Context Added for bug#8406097
350 p_jurisdiction_code VARCHAR2, --parameter
351 p_prim_jurisdiction_code VARCHAR2, --parameter
352 p_monthly_gross NUMBER) --parameter Added for bug#8406097
356
353 RETURN NUMBER;
354
355 /*Added for Bug#12583094*/
357 --Function to give the number of pay periods left in the given year based on the p_logical_hire_date.
358
359 FUNCTION count_remaining_pay_periods(p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE, --Context
360 p_logical_hire_date IN DATE ) --Parameter
361 RETURN NUMBER;
362
363 FUNCTION GET_PSD_JD_CODE
364 ( p_assignment_id IN number --Context
365 ,p_tax_unit_id IN number --context
366 ,p_date_paid IN date --context
367 ,p_loc_addr_geocode IN varchar2 --parameter
368 ,p_per_addr_geocode IN varchar2 --parameter
369 )
370 RETURN VARCHAR2;
371
372 --Wrapper on fnd_date.canonical_to_date for US Localization.
373
374 FUNCTION fnd_canonical_to_date(p_input VARCHAR2)
375 RETURN DATE;
376
377 /*End Bug#12583094*/
378
379 /*Function created for Bug#12618403*/
380
381 FUNCTION get_ht_withheld_per_jd_month( p_assignment_action_id IN number --Context
382 ,p_jurisdiction_code IN varchar2) --Context
383 RETURN NUMBER;
384
385 /*End Bug#12618403*/
386
387 END hr_us_ff_udf1;
[Home] [Help]