1 PACKAGE hr_us_ff_udf1 AS
2 /* $Header: pyusudf1.pkh 120.15.12010000.8 2008/08/11 12:49:18 pannapur ship $ */
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 =============================================================================================
73
74 */
75
76 --
77 TYPE neg_earn_rec IS RECORD
78 ( temp_earn number(15,2),
79 reduced_neg_earn number(15,2),
80 neg_earn_feed number(15,2));
81
82 TYPE neg_earn_tab IS TABLE OF neg_earn_rec
83 INDEX BY BINARY_INTEGER;
84
85 l_neg_earn_tab neg_earn_tab;
86
87 type jd_record is record (
88 Jurisdiction_code pay_us_emp_state_tax_rules_f.jurisdiction_code%type
89 ,percentage NUMBER
90 ,jd_type VARCHAR2(2)
91 ,hours NUMBER
92 ,wages_to_accrue_flag VARCHAR2(4)
93 ,tg_hours NUMBER
94 ,other_pay_hours NUMBER
95 );
96 --
97 -- JD_TYPE is added to deonte the Jurisdiction_Type
98 -- Following notation would be used for this
99 -- Residence -> RS
100 -- Work -> WK
101 -- Residence as well as Work -> RW
102 -- Residence as well as Tagged -> RT
103 -- Tagged Earnings -> TG
104 -- Informational Time -> IT
105 --
106 -- wages_to_accrue_flag
107 --
108 -- AIHW -> Accumulate Information Hours and Wage
109 -- AIHO -> Accumulate only Information Hours
110 -- IHNA -> Accumulation of Information Hours Not Applicable
111
112 type jurisdiction_table
113 is table of jd_record
114 index by BINARY_INTEGER;
115
116 type state_processed_table
117 is table of varchar2(1)
118 index by BINARY_INTEGER;
119
120 type county_processed_table
121 is table of varchar2(1)
122 index by BINARY_INTEGER;
123
124 type city_processed_table
125 is table of varchar2(1)
126 index by BINARY_INTEGER;
127
128 state_processed_tbl state_processed_table;
129 county_processed_tbl county_processed_table;
130 city_processed_tbl city_processed_table;
131
132 jurisdiction_codes_tbl jurisdiction_table;
133 jurisdiction_codes_tbl_stg jurisdiction_table;
134
135 res_jurisdiction_codes_tbl jurisdiction_table;
136
137 /* For threshold following details are defined */
138 jd_codes_tbl_city_stg jurisdiction_table;
139
140 type inform_hour_jd_record is record (
141 Jurisdiction_code pay_us_emp_state_tax_rules_f.jurisdiction_code%type
142 ,percentage NUMBER
143 ,hours NUMBER
144 ,wages_to_accrue_flag VARCHAR2(4)
145 ,calc_percent varchar2(10)
146 ,threshold_hours NUMBER);
147
148 type inform_hours_summary_table
149 is table of inform_hour_jd_record
150 index by BINARY_INTEGER;
151
152 jd_codes_tbl_state_stg inform_hours_summary_table;
153 jd_codes_tbl_state inform_hours_summary_table;
154 jd_codes_tbl_county_stg inform_hours_summary_table;
155 jd_codes_tbl_county inform_hours_summary_table;
156
157 -- This flag to be used in get_jd_percent to branch the code for deriving W4 percentage
158 -- or information hours percentage. This flag would be set to true when assignment is
159 -- configured for processing information hours element entries
160 --
161 g_use_it_flag VARCHAR2(1):= 'N';
162
163 FUNCTION calc_earning(p_template_earning number, -- Parameter
164 p_addl_asg_gre_itd number, -- Parameter
165 p_neg_earn_asg_gre_itd number) -- Parameter
166 RETURN NUMBER;
167
168 FUNCTION neg_earning RETURN NUMBER;
169
170 /*Function added for 6899939*/
171 FUNCTION get_prev_ptd_values(
172 p_assignment_action_id number, -- Context
173 p_tax_unit_id number, -- Context
174 p_jurisdiction_code varchar2, -- Context
175 p_fed_or_state varchar2, -- Parameter
176 p_regular_aggregate number, -- Parameter
177 calc_PRV_GRS OUT nocopy number, -- Paramter
178 calc_PRV_TAX OUT nocopy number )
179 RETURN NUMBER;
180
181 /*Function added for 7238809*/
182 FUNCTION get_prev_ptd_values(
183 p_assignment_action_id number, -- Context
184 p_tax_unit_id number, -- Context
185 p_jurisdiction_code varchar2, -- Context
186 p_fed_or_state varchar2, -- Parameter
187 p_regular_aggregate number, -- Parameter
188 calc_PRV_GRS OUT nocopy number, -- Paramter
189 calc_PRV_TAX OUT nocopy number,
190 p_get_regular_wage varchar2 )
191 RETURN NUMBER;
192
193 FUNCTION get_prev_ptd_values(
194 p_assignment_action_id number, -- Context
195 p_tax_unit_id number, -- Context
196 p_jurisdiction_code varchar2, -- Context
197 p_fed_or_state varchar2, -- Parameter
198 p_regular_aggregate number, -- Parameter
199 calc_PRV_GRS OUT nocopy number, -- Paramter
200 calc_PRV_TAX OUT nocopy number,
201 p_get_regular_wage varchar2, -- Paramter /*6899939*/
202 per_adr_geocode varchar2 ) -- Parameter /*7238809*/
203 RETURN NUMBER;
204
205 FUNCTION get_work_jurisdictions(
206 p_assignment_action_id number -- Formula Context
207 ,p_INITIALIZE in varchar2 -- Parameter
208 ,p_jurisdiction_code in out nocopy varchar2 -- Parameter
209 ,p_percentage out nocopy number -- Parameter
210 )
211 RETURN varchar2;
212
213 FUNCTION get_it_work_jurisdictions(p_assignment_action_id NUMBER
214 ,p_initialize IN VARCHAR2
215 ,p_jurisdiction_code IN OUT NOCOPY VARCHAR2
216 ,p_percentage OUT NOCOPY NUMBER
217 ,p_assignment_id IN NUMBER
218 ,p_date_paid IN DATE
219 ,p_date_earned IN DATE
220 ,p_time_period_id IN NUMBER
221 ,p_payroll_id IN NUMBER
222 ,p_business_group_id IN NUMBER
223 ,p_tax_unit_id IN NUMBER
224 )
225 RETURN VARCHAR2;
226
227 FUNCTION Jurisdiction_processed (
228 p_jurisdiction_code in varchar2 -- Paramter
229 ,p_jd_level in varchar -- Paramter
230 )
231 RETURN varchar2;
232
233 FUNCTION get_fed_prev_ptd_values(
234 p_assignment_action_id number, -- Context
235 p_tax_unit_id number, -- Context
236 p_fed_or_state varchar2, -- Parameter
237 p_regular_aggregate number, -- Parameter
238 calc_PRV_GRS OUT nocopy number, -- Parameter
239 calc_PRV_TAX OUT nocopy number) -- Parameter
240 RETURN NUMBER;
241
242 FUNCTION get_jd_percent(p_jurisdiction_code VARCHAR2 -- Parameter
243 ,p_jd_level VARCHAR2 -- Parameter
244 ,p_hours_to_accumulate OUT nocopy NUMBER -- Parameter
245 ,p_wages_to_accrue_flag OUT nocopy VARCHAR2 -- Parameter
246 )
247 RETURN number;
248
249 FUNCTION get_tax_jurisdiction(
250 p_assignment_id number -- Context
251 ,p_date_earned date -- Parameter
252 )
253 RETURN varchar2;
254 --
255 -- This function used to fetch the JD_TYPE set in the pl table for a given
256 -- jurisdiction
257 --
258 FUNCTION get_jurisdiction_type(p_jurisdiction_code varchar2 -- Parameter
259 )
260 RETURN varchar2;
261
262 --
263 -- This function is used to fetch the status of Employee. It is used for determining
264 -- whether executive weekly maximum should be applicable for a employee.
265 --
266 FUNCTION get_executive_status(p_assignment_id number,
267 p_date_earned date,
268 p_jurisdiction_code varchar2
269 )
270 RETURN varchar2;
271
272 FUNCTION get_wc_flag(p_assignment_id number,
273 p_date_earned date,
274 p_wc_flat_rate_period varchar2
275 )
276 RETURN varchar2;
277
278 --Function to return threshold informational hours for a given jurisdiction.
279 FUNCTION get_jd_level_threshold(p_tax_unit_id NUMBER
280 ,p_jurisdiction_code VARCHAR2
281 ,p_jd_level VARCHAR2)
282 RETURN NUMBER;
283
284 --Function to get balance value
285 FUNCTION get_jd_tax_balance(p_threshold_basis IN VARCHAR2
286 ,p_assignment_action_id IN NUMBER
287 ,p_jurisdiction_code IN VARCHAR2
288 ,p_tax_unit_id IN NUMBER
289 ,p_jurisdiction_level IN VARCHAR2
290 ,p_effective_date IN DATE
291 ,p_assignment_id IN NUMBER
292 ) RETURN NUMBER;
293
294 --Function to get Informational Hours logged by the assignment for
295 --each jurisdiction code in the pl table.
296 FUNCTION get_person_it_hours(p_person_id IN NUMBER
297 ,p_assignment_id IN NUMBER
298 ,p_jurisdiction_code IN VARCHAR2
299 ,p_jd_level IN VARCHAR2 --2,6,11
300 ,p_threshold_basis IN VARCHAR2 --YTD,RTD
301 ,p_effective_date IN DATE
302 ,p_end_date IN DATE) RETURN NUMBER;
303
304 --
305 -- This function would be used for fetching percentage to be used STATE and
306 -- COUNTY level percentage to be used for distributing wages over different
307 -- jurisdictions when assignment is configured to process information hours
308 --
309 FUNCTION get_it_jd_percent(p_jurisdiction_code VARCHAR2 -- parameter
310 ,p_jd_level VARCHAR2 -- parameter
311 ,p_hours_to_accumulate OUT nocopy NUMBER -- parameter
312 ,p_wages_to_accrue_flag OUT nocopy VARCHAR2 -- parameter
313 )
314 RETURN NUMBER;
315
316 FUNCTION across_calendar_years(p_payroll_action_id in number)
317 RETURN varchar2;
318
319 FUNCTION get_work_state (p_jurisdiction_code in varchar2)
320 RETURN varchar2;
321
322 --Function to return the SUI Wage Limits.
323 FUNCTION get_jit_data(p_jurisdiction_code IN VARCHAR2
324 ,p_date_earned IN DATE
325 ,p_jit_type IN VARCHAR2)
326 RETURN NUMBER;
327
328 FUNCTION get_rs_jd (p_assignment_id IN NUMBER,
329 p_date_earned IN DATE)
330 RETURN VARCHAR2;
331
332 FUNCTION get_wk_jd (p_assignment_id IN NUMBER,
333 p_date_earned IN DATE,
334 p_jurisdiction_code IN VARCHAR2)
335 RETURN VARCHAR2;
336
337 END hr_us_ff_udf1;
[Home] [Help]