1 PACKAGE BODY pay_us_sqwl_udf as
2 /* $Header: pyussqut.pkb 115.9 2003/11/20 20:09:53 tmehra noship $ */
3 /* +======================================================================+
4 REM | Copyright (c) 1997 Oracle Corporation |
5 REM | Redwood Shores, California, USA |
6 REM | All rights reserved. |
7 REM +======================================================================+
8 REM SQL Script File Name : pyussqut.pkb
9 REM Description : Package and procedure to build sql for payroll
10 REM processes.
11 REM Package Name : pay_us_sqwl_udf
12 REM Purpose : Using the transfer_date and A_EMP_PER_HIRE_DATE,
13 REM this function will determine if the hire date is
14 REM within the quarter defined by the transfer_date.
15 REM Arguments : 1. A_EMP_PER_HIRE_DATE,
16 REM 2. transfer_date.
17 REM Notes : The following value is returned, qtr_hire_flag.
18 REM This flag will contain the value 'Y' if the hire
19 REM date is within the quarter and a value of 'N' if
20 REM the hire date is outside the quarter.
21 REM
22 REM Change List:
23 REM ------------
24 REM
25 REM Name Date Version Bug Text
26 REM ------------ ---------- ------- ------- ------------------------------
27 REM M Doody 16-FEB-2001 115.0 Initial Version
28 REM
29 REM tmehra 17-SEP-2001 115.1 Added 'get_gre_wage_plan_code'
30 REM function.
31 REM tmehra 15-OCT-2001 115.2 Added 'get_asg_wage_plan_code'
32 REM function.
33 REM tmehra 06-DEC-2001 115.3 Made GSCC compliant
34 REM tmehra 07-MAY-2003 115.4 Added new validation for
35 REM california sqwl as a new
36 REM new segment has been introduced
37 REM for the info type.
38 REM tmehra 22-MAY-2003 115.5 Added validation for duplicate
39 REM Wage Plan entered for the Same
40 REM GRE and for the same state.
41 REM Also added the check to trigger
42 REM this validation only for the
43 REM PAY_US_STATE_WAGE_PLAN_INFO
44 REM context.
45 REM tmehra 28-MAY-2003 115.6 2971577 Fixed the Message Token -
46 REM changed 'atleast' to 'at least'
47 REM tmehra 26-AUG-2003 115.7 2219097 Added two new functions for the
48 REM US W2 enhancements for Govt
49 REM employer.
50 REM - get_employment_code
51 REM - chk_govt_employer
52 REM tmehra 12-NOV-2003 115.8 3189039 Modified the chk_for_default_wp
53 REM to execute only for California.
54 REM tmehra 15-NOV-2003 115.9 2219097 Added a new functions for the
55 REM US W2 enhancements for Govt
56 REM employer.
57 REM - get_archived_emp_code
58 REM ========================================================================
59
60 CREATE OR REPLACE PACKAGE BODY pay_us_sqwl_udf as
61 */
62 FUNCTION get_qtr_hire_flag
63 (
64 p_emp_per_hire_date in DATE,
65 p_transfer_date in DATE
66 )
67 RETURN VARCHAR2 is
68 qtr_hire_flag VARCHAR2(1) := 'N';
69
70 BEGIN
71
72 IF (
73 p_emp_per_hire_date > (trunc(p_transfer_date, 'Q') - 1)
74 AND
75 p_emp_per_hire_date < (round(p_transfer_date, 'Q') )
76 )
77 THEN
78 qtr_hire_flag := 'Y';
79 END IF;
80
81 RETURN (qtr_hire_flag);
82
83 END get_qtr_hire_flag;
84
85
86 ----
87 ---- A new function to return Gre Level Wage Plan Code
88 ---- For Single Wage Plan Code SQWL Format for 'CA'
89 ----
90 FUNCTION get_gre_wage_plan_code
91 (
92 p_tax_unit_id in number,
93 p_transfer_state in varchar
94 )
95 RETURN VARCHAR2 is
96
97 l_wage_plan_code VARCHAR2(1) := ' ';
98
99 CURSOR c_gre_wage_plan IS
100 SELECT hoi.org_information3 wage_plan
101 FROM hr_organization_information hoi
102 WHERE hoi.org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
103 AND hoi.organization_id = p_tax_unit_id
104 AND hoi.org_information1 = p_transfer_state;
105
106 BEGIN
107
108 FOR i IN c_gre_wage_plan
109 LOOP
110 l_wage_plan_code := i.wage_plan;
111 END LOOP;
112
113 RETURN l_wage_plan_code;
114
115 END get_gre_wage_plan_code;
116
117 ----
118 ---- A new function to return Asg Level Wage Plan Code
119 ---- For Single Wage Plan Code SQWL Format for 'CA'
120 ----
121 FUNCTION get_asg_wage_plan_code
122 (
123 p_assignment_id in number,
124 p_transfer_state in varchar
125 )
126 RETURN VARCHAR2 is
127
128 l_wage_plan_code VARCHAR2(1) := ' ';
129
130 CURSOR c_asg_wage_plan IS
131 SELECT DISTINCT aei_information3 wage_plan
132 FROM per_assignment_extra_info paei
133 WHERE paei.assignment_id = p_assignment_id
134 AND paei.aei_information1 = p_transfer_state
135 AND paei.information_type = 'PAY_US_ASG_STATE_WAGE_PLAN_CD';
136
137 BEGIN
138
139 FOR i IN c_asg_wage_plan
140 LOOP
141 l_wage_plan_code := i.wage_plan;
142 END LOOP;
143
144 RETURN l_wage_plan_code;
145
146 END get_asg_wage_plan_code;
147
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_for_default_wp > ----------------------------|
150 -- ----------------------------------------------------------------------------
151 -- Description:
152 -- Verify that only one wage plan is designated as default and
153 -- that at least one wage plan is designated as default
154 -- Added for US Payroll specific situations.
155 --
156 -- Pre Conditions:
157 --
158 --
159 -- In Parameters:
160 -- organization_id, information_context, org_information1, org_information2
161 -- org_information3, org_information4
162 --
163 -- Post Success:
164 -- Processing continues.
165 --
166 -- Post Failure:
167 -- An application error will be raised and processing is terminated.
168 --
169 -- Access Status:
170 -- Internal Table Handler Use Only.
171 -- ----------------------------------------------------------------------------
172 PROCEDURE chk_for_default_wp ( p_organization_id number,
173 p_org_information_context varchar2,
174 p_org_information1 varchar2
175 ) IS
176
177 --
178 l_proc varchar2(100) := 'pay_us_sqwl_udf.chk_for_default_wp';
179
180 l_count number := 0;
181 --
182 CURSOR c1 (p_organization_id number,
183 p_information_context varchar2,
184 p_org_information1 varchar2
185 )IS
186 SELECT count(*) ct
187 FROM hr_organization_information
188 WHERE organization_id = p_organization_id
189 AND org_information_context = p_org_information_context
190 AND org_information1 = p_org_information1
191 AND org_information4 = 'Y';
192 --
193 CURSOR c2(p_organization_id number) IS
194 SELECT count(*) ct
195 FROM (select distinct
196 a.organization_id,
197 a.org_information1,
198 a.org_information3
199 FROM hr_organization_information a
200 WHERE org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO') b
201 WHERE b.organization_id = p_organization_id
202 AND 1 < ( SELECT count(*)
203 FROM hr_organization_information orgi
204 WHERE organization_id = p_organization_id
205 AND org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
206 AND org_information1 = b.org_information1
207 AND org_information3 = b.org_information3);
208 --
209
210 BEGIN
211 --
212 hr_utility.set_location('Entering:'||l_proc, 10);
213 --
214
215 IF p_org_information_context = 'PAY_US_STATE_WAGE_PLAN_INFO'
216 AND p_org_information1 = 'CA' THEN
217
218 l_count := 0;
219
220 FOR c1_rec IN c1 (p_organization_id,
221 p_org_information_context,
222 p_org_information1) LOOP
223
224 l_count := c1_rec.ct;
225
226
227 END LOOP;
228
229 hr_utility.set_location(l_proc, 20);
230
231
232 --
233 -- raise error if the count > 1 or count = 0
234 --
235
236 IF l_count <> 1 THEN
237
238 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
239 hr_utility.set_message_token('FORMAT',' with at least 1 and only 1 marked as default');
240
241 hr_utility.raise_error;
242
243 END IF;
244
245
246 l_count := 0;
247
248 FOR c2_rec IN c2 (p_organization_id) LOOP
249
250 l_count := c2_rec.ct;
251
252
253 END LOOP;
254
255 hr_utility.set_location(l_proc, 20);
256
257
258 --
259 -- raise error if the count > 1 or count = 0
260 --
261
262 IF l_count > 0 THEN
263
264 hr_utility.set_message(801, 'PAY_7024_USERTAB_BAD_ROW_VALUE');
265 hr_utility.set_message_token('FORMAT',' with unique tax type and state code');
266
267 hr_utility.raise_error;
268
269 END IF;
270
271
272 END IF;
273
274 --
275 hr_utility.set_location(' Leaving:'||l_proc, 50);
276 --
277 END chk_for_default_wp;
278 --
279
280 -- ----------------------------------------------------------------------------
281 -- |-----------------------< chk_govt_employer > ----------------------------|
282 -- ----------------------------------------------------------------------------
283 -- Description:
284 -- Verify if the employee/employer is a US government employee/employer
285 -- Added for US Payroll W2 specific situations.
286 --
287 -- Pre Conditions:
288 --
289 --
290 -- In Parameters:
291 -- tax_unit_id, assignment_action_id, assignment_id
292 --
293 --
294 -- Post Success:
295 -- Returns Yes/No
296 --
297 --
298 -- Access Status:
299 -- Internal Table Handler Use Only.
300 -- ----------------------------------------------------------------------------
301 FUNCTION chk_govt_employer ( p_tax_unit_id number DEFAULT NULL,
302 p_assignment_action_id number DEFAULT NULL
303 ) RETURN BOOLEAN IS
304
305 --
306 l_proc varchar2(100) := 'pay_us_sqwl_udf.chk_govt_employer';
307 l_tax_unit_id number;
308 l_yes_no boolean := FALSE;
309 --
310 CURSOR c_get_tax_unit_id IS
311 SELECT tax_unit_id
312 FROM pay_assignment_actions
313 WHERE assignment_action_id = p_assignment_action_id;
314
315
316 CURSOR c_chk_govt_employer IS
317 SELECT target.ORG_INFORMATION8 yes_no
318 FROM hr_organization_information target
319 WHERE target.organization_id = l_tax_unit_id
320 AND target.org_information_context = 'Federal Tax Rules';
321
322 --
323
324 BEGIN
325 --
326 hr_utility.set_location('Entering:'||l_proc, 10);
327 --
328
329 IF p_tax_unit_id IS NOT NULL THEN
330 l_tax_unit_id := p_tax_unit_id;
331 ELSE
332
333 FOR c_rec IN c_get_tax_unit_id
334 LOOP
335 l_tax_unit_id := c_rec.tax_unit_id;
336 END LOOP;
337 END IF;
338
339
340 FOR c_rec IN c_chk_govt_employer
341 LOOP
342 IF c_rec.yes_no = 'Y' THEN
343 l_yes_no := TRUE;
344 ELSE
345 l_yes_no := FALSE;
346 END IF;
347 END LOOP;
348 --
349 hr_utility.set_location(' Leaving:'||l_proc, 50);
350 --
351
352 RETURN l_yes_no;
353
354 END chk_govt_employer;
355
356 -- ----------------------------------------------------------------------------
357 -- |-----------------------< chk_employment_code > ----------------------------|
358 -- ----------------------------------------------------------------------------
359 -- Description:
360 -- Returns the employment code 'Q' or 'R' based on medicare and SS withheld.
361 -- Added for US Payroll W2 specific situations.
362 --
363 -- Pre Conditions:
364 --
365 --
366 -- In Parameters:
367 -- Medicare Wages, SS wages
368 --
369 --
370 -- Post Success:
371 -- Returns 'Q' or 'R'
372 --
373 --
374 -- ----------------------------------------------------------------------------
375 FUNCTION get_employment_code ( p_medicare_wh number DEFAULT NULL,
376 p_ss_wh number DEFAULT NULL
377 ) RETURN varchar2 IS
378
379 --
380 l_proc varchar2(100) := 'pay_us_sqwl_udf.get_employement_code';
381 l_tax_unit_id number;
382 l_code varchar2(1);
383 --
384 --
385
386 BEGIN
387 --
388 hr_utility.set_location('Entering:'||l_proc, 10);
389 --
390
391 IF p_ss_wh = 0 and p_medicare_wh > 0 THEN
392 l_code := 'Q';
393 ELSE
394 l_code := 'R';
395 END IF;
396
397 --
398 hr_utility.set_location(' Leaving:'||l_proc, 50);
399 --
400
401 RETURN l_code;
402
403 END get_employment_code;
404
405 -- ----------------------------------------------------------------------------
406 -- |-----------------------< get_archived_emp_code >---------------------------
407 -- ----------------------------------------------------------------------------
408 -- Description:
409 -- Returns the archived employment code 'Q' or 'R' for the passed assignment
410 -- action_id.
411 -- Added for US Payroll W2 specific situations.
412 --
413 -- Pre Conditions:
414 -- If no archived value is found, default value of 'R' is returned. This is
415 -- done to support the employees whose data was archived before these
416 -- changes.
417 --
418 --
419 -- In Parameters:
420 -- p_assignment_action_id
421 --
422 --
423 -- Post Success:
424 -- Returns 'Q' or 'R'
425 --
426 --
427 -- ----------------------------------------------------------------------------
428 FUNCTION get_archived_emp_code ( p_assignment_action_id number DEFAULT NULL
429 ) RETURN varchar2 IS
430
431 --
432 l_proc varchar2(100) := 'pay_us_sqwl_udf.get_archived_emp_code';
433 l_code varchar2(1);
434 l_ue_id NUMBER;
435 --
436 --
437 CURSOR c_get_user_entity_id IS
438 SELECT user_entity_id
439 FROM ff_user_entities
440 WHERE user_entity_name = 'A_ASG_GRE_EMPLOYMENT_TYPE_CODE';
441
442
443 CURSOR c_get_archived_emp_code (p_user_entity_id NUMBER) IS
444 SELECT arch.value
445 FROM ff_archive_items arch
446 WHERE arch.user_entity_id = p_user_entity_id
447 AND arch.context1 = p_assignment_action_id;
448
449 BEGIN
450 --
451 hr_utility.set_location('Entering:'||l_proc, 10);
452 --
453
454
455 -- Get the user entity id for A_ASG_GRE_EMPLOYMENT_TYPE_CODE
456
457 FOR c_rec IN c_get_user_entity_id
458 LOOP
459
460 l_ue_id := c_rec.user_entity_id;
461
462 END LOOP;
463
464
465 -- Get the archived emp code for the passed assignment_action
466
467 l_code := 'R';
468
469 FOR c_rec IN c_get_archived_emp_code(l_ue_id)
470 LOOP
471
472 l_code := c_rec.value;
473
474 END LOOP;
475
476 --
477 hr_utility.set_location(' Leaving:'||l_proc, 50);
478 --
479
480 RETURN l_code;
481
482 END get_archived_emp_code;
483
484 END pay_us_sqwl_udf;
[Home] [Help]