76:
77: /* pay reg code */
78:
79: sqlstr := 'select distinct asg.person_id
80: from pay_assignment_actions act,
81: per_assignments_f asg,
82: pay_payroll_actions ppa2, /* run and quickpay payroll actions */
83: pay_assignment_actions act2, /* run and quickpay assignment actions */
84: pay_action_interlocks pai, /* interlocks table */
79: sqlstr := 'select distinct asg.person_id
80: from pay_assignment_actions act,
81: per_assignments_f asg,
82: pay_payroll_actions ppa2, /* run and quickpay payroll actions */
83: pay_assignment_actions act2, /* run and quickpay assignment actions */
84: pay_action_interlocks pai, /* interlocks table */
85: pay_payroll_actions ppa, /* PYUGEN information */
86: pay_payroll_actions pa1 /* Payroll Register information */
87: where ppa.payroll_action_id = :payroll_action_id
138: act.tax_unit_id,
139: ppa.action_type,
140: ppa.effective_date,
141: act.source_action_id
142: from pay_assignment_actions act,
143: per_assignments_f paf,
144: pay_payroll_actions ppa, /* pre-payments and reversals
145: payroll action id */
146: pay_payroll_actions ppa1, /* PYUGEN payroll action id */
181: )
182: and ( ( ppa.action_type in ('P','U') and
183: ( exists ( select 1
184: from pay_action_interlocks pai1
185: ,pay_assignment_actions paa1
186: ,pay_payroll_actions ppa2
187: where pai1.locking_action_id = act.assignment_action_id
188: and paa1.assignment_action_id = pai1.locked_action_id
189: and ppa2.payroll_action_id = paa1.payroll_action_id
214: and shortname = 'SEP_PAY';
215:
216: cursor c_get_map_flag(cp_prepay_action_id number) is
217: select ppf.multi_assignments_flag
218: from pay_assignment_actions paa,
219: pay_payroll_actions ppa,
220: pay_all_payrolls_f ppf
221: where assignment_action_id = cp_prepay_action_id
222: and ppa.payroll_action_id = paa.payroll_action_id
227: cursor c_child_pp_aaid(cp_prepay_action_id number
228: ,cp_assignment_id number
229: ,cp_tax_unit_id number) is
230: select paa.assignment_action_id
231: from pay_assignment_actions paa
232: where paa.source_action_id = cp_prepay_action_id
233: and paa.assignment_id = cp_assignment_id
234: and paa.tax_unit_id = cp_tax_unit_id;
235:
235:
236: cursor c_pp_aaid_for_sepchk(cp_source_action_id number) is
237: select paa.assignment_action_id
238: from pay_action_interlocks pai
239: ,pay_assignment_actions paa
240: ,pay_payroll_actions ppa
241: where pai.locked_action_id = cp_source_action_id
242: and paa.assignment_action_id = pai.locking_action_id
243: and paa.source_action_id is not null
248: cp_assignment_id number,
249: cp_tax_unit_id number,
250: cp_sepchk_run_tp_id number) is
251: select max(paa.assignment_action_id)
252: from pay_assignment_actions paa,
253: pay_action_interlocks pai,
254: pay_run_types_f prt,
255: pay_payroll_actions ppa
256: where pai.locking_action_id = cp_prepay_action_id
267: cursor c_taxgrp_max_run_aaid(cp_prepay_action_id number,
268: cp_assignment_id number,
269: cp_sepchk_run_tp_id number) is
270: select max(paa.assignment_action_id)
271: from pay_assignment_actions paa,
272: pay_action_interlocks pai,
273: pay_run_types_f prt,
274: pay_payroll_actions ppa
275: where pai.locking_action_id = cp_prepay_action_id
289: ****************************************************************/
290:
291: cursor c_sepchk_act_seq(cp_assignment_action_id number) is
292: select paa.action_sequence
293: from pay_assignment_actions paa
294: where paa.assignment_action_id = cp_assignment_action_id;
295:
296: cursor c_other_asg_for_sepchk(cp_prepay_asg_act_id number
297: ,cp_assignment_id number) is
306: ,cp_assignment_id number
307: ,cp_action_sequence number) is
308: select paa_run.action_sequence, paa_run.assignment_action_id
309: from pay_action_interlocks pai,
310: pay_assignment_actions paa_run,
311: pay_payroll_actions ppa_run,
312: pay_run_types_f prt
313: where pai.locking_action_id = cp_prepay_asg_act_id
314: and paa_run.assignment_action_id = pai.locked_action_id
570:
571: if ( ( ln_source_action_id <> prev_source_action_id ) or
572: ( ln_tax_unit_id <> prev_tax_unit_id ) ) then
573:
574: select pay_assignment_actions_s.nextval
575: into lockingactid
576: from dual;
577:
578: -- insert the action record.
591: lv_sep_check ||
592: nvl(lv_multi_asg_flag,'N') ||
593: to_char(ln_max_run_aa_id);
594:
595: -- update pay_assignment_actions serial_number with
596: -- runactid.
597:
598: update pay_assignment_actions
599: set serial_number = serialno
594:
595: -- update pay_assignment_actions serial_number with
596: -- runactid.
597:
598: update pay_assignment_actions
599: set serial_number = serialno
600: where assignment_action_id = lockingactid
601: and tax_unit_id = ln_tax_unit_id;
602:
701: end loop; -- c_payment_info
702: close c_payment_info;
703: else
704:
705: select pay_assignment_actions_s.nextval
706: into lockingactid
707: from dual;
708:
709: hr_utility.trace('B4 insact'||to_char(lockingactid) ||','||
719:
720: begin
721: --serialno := 'V'||to_char(runactid);
722: serialno := actiontype || 'NN' || to_char(lockedactid);
723: -- update pay_assignment_actions serial_number with runactid.
724: update pay_assignment_actions
725: set serial_number = serialno
726: where assignment_action_id = lockingactid
727: and tax_unit_id = greid;
720: begin
721: --serialno := 'V'||to_char(runactid);
722: serialno := actiontype || 'NN' || to_char(lockedactid);
723: -- update pay_assignment_actions serial_number with runactid.
724: update pay_assignment_actions
725: set serial_number = serialno
726: where assignment_action_id = lockingactid
727: and tax_unit_id = greid;
728: exception when others then
765: hr_all_organization_units hou1,
766: hr_locations_all loc,
767: per_all_people_f ppf,
768: per_all_assignments_f paf,
769: pay_assignment_actions paa1, /* PYUGEN assignment action */
770: pay_payroll_actions ppa1 /* PYUGEN payroll action id */
771: where ppa1.payroll_action_id = :pactid
772: and paa1.payroll_action_id = ppa1.payroll_action_id
773: and paa1.assignment_id = paf.assignment_id