[Home] [Help]
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
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
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,
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'),
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;
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.
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);
346: pay_balance_pkg.set_context('JURISDICTION_CODE',jd_code);
347:
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);
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);
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;
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.
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
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);
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;
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
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
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
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,
477: return par_value;
478: --
479: end get_parameter;
480:
481: end pay_negbal_pkg;
482: