1 package body pay_negbal_pkg as
2 /* $Header: pynegbal.pkb 115.9 2003/02/07 11:55:58 dsaxby ship $ */
3 --
4 /* Name : bal_db_item
5 Purpose : Given the name of a balance DB item as would be seen in a fast formula
6 it returns the defined_balance_id of the balance it represents.
7 Arguments :
8 Notes : A defined balance_id is required by the PLSQL balance function.
9 */
10
11 function bal_db_item
12 (
13 p_db_item_name varchar2
14 ) return number is
15
16 /* Get the defined_balance_id for the specified balance DB item. */
17
18 cursor csr_defined_balance is
19 select to_number(UE.creator_id)
20 from ff_user_entities UE,
21 ff_database_items DI
22 where DI.user_name = p_db_item_name
23 and UE.user_entity_id = DI.user_entity_id
24 and Ue.creator_type = 'B';
25
26 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
27
28 begin
29
30 open csr_defined_balance;
31 fetch csr_defined_balance into l_defined_balance_id;
32 if csr_defined_balance%notfound then
33 close csr_defined_balance;
34 raise hr_utility.hr_error;
35 else
36 close csr_defined_balance;
37 end if;
38
39 return (l_defined_balance_id);
40
41 end bal_db_item;
42 --
43 -- Name
44 -- check_residence_state
45 -- Purpose
46 -- This checks that the state of residence for the given assignment id
47 -- is the same as that passed in. Used
48 -- in this package to determine if a person has lived in the state of
49 -- MA. Such people need to be reported on SQWL for MA.
50 -- Arguments
51 -- Assignment Id
52 -- Period Start Date
53 -- Period End Date
54 -- State
55 --
56 FUNCTION check_residence_state (
57 p_assignment_id NUMBER,
58 p_period_start DATE,
59 p_period_end DATE,
60 p_state VARCHAR2,
61 p_effective_end_date DATE
62 ) RETURN BOOLEAN IS
63
64 l_resides_true VARCHAR2(1);
65 BEGIN
66
67 BEGIN
68 SELECT '1'
69 INTO l_resides_true
70 FROM dual
71 WHERE EXISTS (
72 SELECT '1'
73 FROM per_assignments_f paf,
74 per_addresses pad
75 WHERE paf.assignment_id = p_assignment_id AND
76 paf.person_id = pad.person_id AND
77 pad.date_from <= p_period_end AND
78 NVL(pad.date_to ,p_period_end) >= p_period_start AND
79 pad.region_2 = p_state AND
80 pad.primary_flag = 'Y');
81 EXCEPTION when no_data_found then
82 l_resides_true := '0';
83 END;
84
85 hr_utility.trace('l_resides_true =' || l_resides_true);
86
87 IF (l_resides_true = '1' AND
88 pay_balance_pkg.get_value(bal_db_item('GROSS_EARNINGS_PER_GRE_QTD'),
89 p_assignment_id, least(p_period_end, p_effective_end_date)) > 0) THEN
90
91 hr_utility.trace('Returning TRUE from check_residence_state');
92
93 RETURN TRUE;
94 ELSE
95 RETURN FALSE;
96 END IF;
97 END; -- check_residence_state
98 --
99 FUNCTION report_person_on_tape (
100 p_assignment_id NUMBER,
101 p_period_start DATE,
102 p_period_end DATE,
103 p_state VARCHAR2,
104 p_effective_end_date DATE,
105 p_1099R_ind VARCHAR2
106 ) RETURN BOOLEAN IS
107 l_ret_value BOOLEAN := FALSE;
108 l_resides_in_state BOOLEAN;
109 BEGIN
110 IF (p_state = 'MA' OR p_state = 'CA') THEN
111 l_resides_in_state := check_residence_state(p_assignment_id,
112 p_period_start,
113 p_period_end,
114 p_state,
115 p_effective_end_date);
116 l_ret_value := TRUE;
117 IF (p_state = 'CA') THEN
118 IF (p_1099R_ind = 'Y') THEN
119 l_ret_value := (pay_balance_pkg.get_value(
120 bal_db_item('SIT_WITHHELD_PER_JD_GRE_QTD') ,
121 p_assignment_id,
122 least(p_period_end, p_effective_end_date)) > 0 );
123 l_resides_in_state := l_ret_value;
124 hr_utility.trace('1099R_ind is Y');
125 ELSE
126 l_ret_value := l_resides_in_state;
127 END IF;
128 END IF;
129 l_ret_value := l_resides_in_state AND l_ret_value;
130 END IF;
131 return l_ret_value;
132 END; --report_person_on_tape
133 --
134 ----------------------------------- range_cursor ----------------------------------
135 procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
136 l_payroll_id number;
137 leg_param pay_payroll_actions.legislative_parameters%type;
138 l_state pay_us_states.state_abbrev%type;
139 --
140 begin
141 select legislative_parameters,
142 pay_negbal_pkg.get_parameter('TRANSFER_STATE',
143 ppa.legislative_parameters)
144 into leg_param,
145 l_state
146 from pay_payroll_actions ppa
147 where ppa.payroll_action_id = pactid;
148
149
150 /* Negative Balance Code */
151 sqlstr := 'SELECT distinct ASG.person_id
152 FROM per_assignments_f ASG,
153 hr_organization_units HOU,
154 pay_payrolls_f PPY,
155 pay_state_rules SR,
156 hr_organization_information HOI,
157 pay_us_asg_reporting puar,
158 pay_payroll_actions PPA
159 WHERE PPA.payroll_action_id = :payroll_action_id
160 AND SR.state_code = '''||l_state||'''
161 AND substr(SR.jurisdiction_code,1,2) = substr(puar.jurisdiction_code,1,2)
162 AND ASG.assignment_id = puar.assignment_id
163 AND puar.tax_unit_id = HOU.organization_id
164 AND ASG.business_group_id + 0 = PPA.business_group_id
165 AND ASG.assignment_type = ''E''
166 AND ASG.effective_start_date <= PPA.effective_date
167 AND ASG.effective_end_date >= PPA.start_date
168 AND ((not exists (
169 select ''x'' from hr_organization_information hoi2
170 where HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
171 and HOI2.org_information2 is not null
172 and HOI2.organization_id = hou.organization_id))
173 or ( '''||l_state||''' = ''CA'')
174 )
175 AND HOI.organization_id = puar.tax_unit_id
176 AND HOI.ORG_INFORMATION_CONTEXT = ''State Tax Rules''
177 AND HOI.ORG_INFORMATION1 = '''||l_state||'''
178 AND PPY.payroll_id = ASG.payroll_id
179 ORDER BY ASG.person_id';
180
181 end range_cursor;
182 ---------------------------------- action_creation ----------------------------------
183 --
184 procedure action_creation(pactid in number,
185 stperson in number,
186 endperson in number,
187 chunk in number) is
188
189 l_state pay_us_states.state_abbrev%type;
190 greid number;
191 lockedactid number;
192 lockingactid number;
193 assignid number;
194 num number;
195 prevperid number;
196 prevgreid number;
197 effdt date;
198 transmitter_code varchar2(240);
199 jd_code varchar2(12);
200 personid number;
201 l_qtr_start date;
202 l_qtr_end date;
203 l_year_start date;
204 l_year_end date;
205 l_period_start date;
206 l_period_end date;
207 l_defined_balance_id number;
208 l_value number :=0;
209 l_sui_exempt number;
210
211 CURSOR c_actions
212 ( pactid number,
213 stperson number,
214 endperson number ) is
215 SELECT paa.assignment_action_id locked_action_id,
216 asg.assignment_id assignment_id,
217 asg.person_id person_id,
218 paa.tax_unit_id tax_unit_id,
219 ppa.effective_date effective_end_date,
220 sr.jurisdiction_code jurisdiction_code
221 FROM hr_organization_information hoi,
222 pay_payroll_actions ppa,
223 pay_assignment_actions paa,
224 pay_state_rules sr,
225 per_assignments_f asg,
226 pay_payroll_actions ppa_arch
227 WHERE ppa_arch.payroll_action_id = pactid
228 AND asg.person_id between stperson and endperson
229 AND asg.business_group_id + 0 = ppa_arch.business_group_id
230 AND asg.assignment_type = 'E'
231 AND asg.effective_start_date <= l_period_end
232 AND asg.effective_end_date >= l_period_start
233 AND paa.assignment_id = asg.assignment_id
234 AND (paa.action_sequence,asg.person_id,paa.tax_unit_id)
235 in (select max(paa1.action_sequence),paf1.person_id, paa1.tax_unit_id
236 from pay_action_classifications pac,
237 pay_payroll_actions ppa1,
238 pay_assignment_actions paa1,
239 per_assignments_f paf1
240 where paf1.person_id = asg.person_id
241 AND paf1.business_group_id + 0 = ppa_arch.business_group_id
242 AND paf1.assignment_type = 'E'
243 AND paf1.effective_start_date <= l_period_end
244 AND paf1.effective_end_date >= l_period_start
245 and paa1.assignment_id = paf1.assignment_id
246 and paa1.tax_unit_id = paa.tax_unit_id
247 and ppa1.payroll_action_id = paa1.payroll_action_id
248 and ppa1.action_type = pac.action_type
249 and pac.classification_name = 'SEQUENCED'
250 and ppa1.effective_date between
251 l_period_start
252 and l_period_end
253 group by paf1.person_id, paa1.tax_unit_id)
254 AND ppa.payroll_action_id = paa.payroll_action_id
255 AND ppa.effective_date between l_period_start
256 and l_period_end
257 AND ppa.action_type in ('R','Q','V','B','I')
258 AND ppa.effective_date between asg.effective_start_date
259 and asg.effective_end_date
260 AND SR.state_code = l_state
261 AND hoi.organization_id = paa.tax_unit_id
262 AND hoi.org_information_context = 'State Tax Rules'
263 AND hoi.org_information1 = l_state
264 AND EXISTS (select '' from pay_us_asg_reporting puar
265 where asg.assignment_id = puar.assignment_id
266 and paa.tax_unit_id = puar.tax_unit_id
267 and substr(SR.jurisdiction_code ,1,2) =
268 substr(puar.jurisdiction_code,1,2));
269 -- Commented to improve the performance
270 -- ORDER BY paa.tax_unit_id,asg.person_id,asg.assignment_id
271 -- for update of asg.assignment_id;
272
273 CURSOR c_transmitter is
274 SELECT decode(l_state,'CA',null,hoi2.org_information2)
275 FROM hr_organization_information hoi2
276 WHERE hoi2.organization_id = greid
277 AND hoi2.org_information_context = '1099R Magnetic Report Rules';
278
279 --
280 --
281 begin
282 -- hr_utility.trace_on('Y','ORACLE');
283 hr_utility.set_location('pay_negbal_pkg.procngb',1);
284 select pay_negbal_pkg.get_parameter('TRANSFER_STATE',
285 ppa.legislative_parameters) state_abbrev,
286 ppa.start_date,
287 ppa.effective_date,
288 trunc(ppa.effective_date, 'Y'),
289 add_months(trunc(ppa.effective_date, 'Y'),12) - 1
290 into l_state,
291 l_qtr_start,
292 l_qtr_end,
293 l_year_start,
294 l_year_end
295 from pay_payroll_actions ppa
296 where ppa.payroll_action_id = pactid;
297
298 /* New York state settings NB. the difference is that the criteria for
299 selecting people in the 4th quarter is different to that used for the
300 first 3 quarters of the tax year. */
301
302 if l_state = 'NY' and to_char(l_qtr_end,'MM')= '12' then
303 /* Period is the last quarter of the year.*/
304 l_period_start := l_year_start;
305 l_period_end := l_year_end;
306 l_defined_balance_id := bal_db_item('SIT_GROSS_PER_JD_GRE_YTD');
307 else
308 /* Period is one of the first 3 quarters of tax year. */
309 l_period_start := l_qtr_start;
310 l_period_end := l_qtr_end;
311 l_defined_balance_id := bal_db_item('SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD');
312 end if;
313
314 open c_actions(pactid,stperson,endperson);
315 num := 0;
316 prevperid := -1;
317 prevgreid := -1;
318 l_value := 0;
319 loop
320 hr_utility.set_location('pay_negbal_pkg.procngb',2);
321 fetch c_actions into lockedactid,assignid,personid,
322 greid,effdt,jd_code;
323 if c_actions%found then num := num + 1; end if;
324 exit when c_actions%notfound;
325 --
326 begin
327 open c_transmitter;
328 fetch c_transmitter into transmitter_code;
329 if c_transmitter%notfound then
330 transmitter_code := 'N' ;
331 end if;
332 close c_transmitter;
333 exception
334 when others then
335 hr_utility.set_location('pay_negbal_pkg.procngb',22);
336 raise;
337 end;
338 -- we need to insert one action for each of the
339 -- assignments that we return from the cursor.
340 if personid = prevperid and greid = prevgreid then
341 null;
342 else
343 hr_utility.set_location('pay_negbal_pkg.procngb',3);
344 -- set up contexts required to test the balance.
345 pay_balance_pkg.set_context('TAX_UNIT_ID',greid);
349 into l_sui_exempt
346 pay_balance_pkg.set_context('JURISDICTION_CODE',jd_code);
347
348 select count(*)
350 from pay_us_emp_state_tax_rules_f ptax,
351 pay_us_states pst
352 where ptax.assignment_id = assignid
353 and ptax.effective_start_date <= l_qtr_end
354 and ptax.effective_end_date >= l_qtr_start
355 and pst.state_code = ptax.state_code
356 and pst.state_abbrev = l_state
357 and ptax.sui_exempt = 'Y'
358 and not exists ( select 'x'
359 from pay_us_emp_state_tax_rules_f ptax,
360 pay_us_states pst
361 where ptax.assignment_id = assignid
362 and ptax.effective_start_date <= l_qtr_end
363 and ptax.effective_end_date >= l_qtr_start
364 and pst.state_code = ptax.state_code
365 and pst.state_abbrev = l_state
366 and ptax.sui_exempt = 'N') ;
367 hr_utility.set_location('pay_negbal_pkg.procngb',4);
368 if l_sui_exempt = 0 then
369 hr_utility.set_location('pay_negbal_pkg.procngb',5);
370 if nvl(transmitter_code,'N') <> 'Y' then
371 hr_utility.set_location('pay_negbal_pkg.procngb',6);
372 l_value := pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id,
373 p_assignment_action_id => lockedactid);
374 else
375 l_value := 0;
376 end if;
377 end if;
378
379 if ((l_value > 0 ) OR
380 report_person_on_tape(assignid,l_period_start,l_period_end,
381 l_state, effdt,transmitter_code)) then
382
383 hr_utility.set_location('pay_negbal_pkg.procngb',7);
384 select pay_assignment_actions_s.nextval
385 into lockingactid
386 from dual;
387 -- insert the action record.
388 hr_utility.set_location('pay_negbal_pkg.procngb',8);
389 hr_nonrun_asact.insact(lockingactid,assignid,pactid,chunk,greid);
390 hr_utility.set_location('pay_negbal_pkg.procngb',9);
391 -- insert an interlock to this action
392 -- Bug fix 1850043
393 -- hr_nonrun_asact.insint(lockingactid,lockedactid);
394 hr_utility.set_location('pay_negbal_pkg.procngb',10);
395 end if;
396 end if;
397 prevperid := personid;
398 prevgreid := greid;
399 --
400 end loop;
401 hr_utility.set_location('pay_negbal_pkg.procngb',11);
402 close c_actions;
403 end action_creation;
404 ---------------------------------- sort_action ----------------------------------
405 procedure sort_action
406 (
407 payactid in varchar2, /* payroll action id */
408 sqlstr in out nocopy varchar2, /* string holding the sql statement */
409 len out number /* length of the sql string */
410 ) is
411 begin
412
413 hr_utility.set_location('pay_negbal_pkg.sort_action',1);
414 sqlstr := 'select paa.rowid
415 from pay_payroll_actions ppa,
416 pay_assignment_actions paa,
417 per_all_assignments_f paf, -- #1894165
418 hr_organization_units hou,
419 hr_organization_units hou1
420 where ppa.payroll_action_id = :pactid
421 and paa.payroll_action_id = ppa.payroll_action_id
422 and paf.assignment_id = paa.assignment_id
423 and paf.business_group_id + 0 = ppa.business_group_id
424 and paf.assignment_type = ''E''
425 and paf.effective_start_date = (select max(paf1.effective_start_date)
426 from per_all_assignments_f paf1 --# 1894165
427 where paf1.assignment_id = paf.assignment_id
428 and paf1.business_group_id + 0 = ppa.business_group_id
429 and paf1.assignment_type = ''E''
430 and paf1.effective_start_date <= ppa.effective_date
431 and paf1.effective_end_date >=
432 decode(pay_negbal_pkg.get_parameter
433 (''TRANSFER_STATE'',ppa.legislative_parameters),
434 ''NY'',
435 decode(to_char(ppa.effective_date,''Q''),
436 4, trunc(ppa.start_date, ''Y''), ppa.start_date
437 )
438 , ppa.start_date
439 )
440 )
441 and paa.tax_unit_id = hou.organization_id
442 and hou1.organization_id = nvl(paf.organization_id,paf.business_group_id) -- # 1894165
443 order by hou.name,hou1.name,paf.assignment_number
444 for update of paf.assignment_id';
445
446 len := length(sqlstr); -- return the length of the string.
447 hr_utility.set_location('pay_negbal_pkg.sort_action',2);
448 end sort_action;
449 --
450 ------------------------------ get_parameter -------------------------------
451 function get_parameter(name in varchar2,
452 parameter_list varchar2) return varchar2
453 is
454 start_ptr number;
455 end_ptr number;
456 token_val pay_payroll_actions.legislative_parameters%type;
457 par_value pay_payroll_actions.legislative_parameters%type;
458 begin
459 --
460 token_val := name||'=';
461 --
462 start_ptr := instr(parameter_list, token_val) + length(token_val);
463 end_ptr := instr(parameter_list, ' ',start_ptr);
464 --
465 /* if there is no spaces use then length of the string */
466 if end_ptr = 0 then
467 end_ptr := length(parameter_list)+1;
468 end if;
469 --
470 /* Did we find the token */
471 if instr(parameter_list, token_val) = 0 then
472 par_value := NULL;
473 else
474 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
475 end if;
476 --
477 return par_value;
478 --
479 end get_parameter;
480
481 end pay_negbal_pkg;
482
[Home] [Help]