[Home] [Help]
149: ,cp_end_date in date
150: )
151: IS
152: select paa.assignment_Action_id
153: from pay_assignment_actions paa,
154: per_all_assignments_f paf,
155: pay_payroll_actions ppa
156: where ppa.business_group_id = cp_business_group_id
157: and ppa.effective_date between cp_start_date and cp_end_date
164: and paf.effective_end_date >= ppa.start_date
165: and paf.assignment_type = 'E'
166: and not exists
167: (select 'x' from pay_Action_interlocks pai,
168: pay_assignment_actions paa1,
169: pay_payroll_actions ppa1
170: where pai.locked_action_id = paa.assignment_action_id
171: and paa1.assignment_action_id = pai.locking_action_id
172: and ppa1.payroll_action_id = paa1.payroll_action_id
176: and ppa1.report_category = 'RT'
177: and ppa1.action_status = 'C')
178: and not exists
179: (select 'x' from pay_Action_interlocks pai,
180: pay_assignment_actions paa1,
181: pay_payroll_actions ppa1
182: where pai.locked_action_id = paa.assignment_action_id
183: and paa1.assignment_action_id = pai.locking_action_id
184: and ppa1.payroll_action_id = paa1.payroll_action_id
278: if lv_report_type = 'MARK_W2C_PAPER' then
279: hr_utility.set_location(gv_package || '.mark_w2c_range_cursor', 30);
280: lv_sql_string :=
281: 'select distinct paf.person_id
282: from pay_assignment_actions paa,
283: per_all_assignments_f paf,
284: pay_payroll_actions ppa
285: where ppa.business_group_id = '|| ln_business_group_id || '
286: and ppa.effective_date between to_date(''' ||
296: and paf.assignment_type = ''E''
297: and :payroll_action_id is not null
298: and not exists
299: (select ''x'' from pay_Action_interlocks pai,
300: pay_assignment_actions paa1,
301: pay_payroll_actions ppa1
302: where pai.locked_action_id = paa.assignment_action_id
303: and paa1.assignment_action_id = pai.locking_action_id
304: and ppa1.payroll_action_id = paa1.payroll_action_id
309: and ppa1.report_type = ''MARK_W2C_PAPER''
310: and ppa1.action_status = ''C'')
311: and not exists
312: (select ''x'' from pay_Action_interlocks pai,
313: pay_assignment_actions paa1,
314: pay_payroll_actions ppa1
315: where pai.locked_action_id = paa.assignment_action_id
316: and paa1.assignment_action_id = pai.locking_action_id
317: and ppa1.payroll_action_id = paa1.payroll_action_id
353: select paa.assignment_id,
354: paa.tax_unit_id,
355: paf.person_id,
356: paa.assignment_Action_id
357: from pay_assignment_actions paa,
358: per_all_assignments_f paf,
359: pay_payroll_actions ppa
360: where ppa.business_group_id = cp_business_group_id
361: and ppa.effective_date between cp_start_date and cp_end_date
370: and paf.person_id between cp_start_person_id
371: and cp_end_person_id
372: and not exists
373: (select 'x' from pay_Action_interlocks pai,
374: pay_assignment_actions paa1,
375: pay_payroll_actions ppa1
376: where pai.locked_action_id = paa.assignment_action_id
377: and paa1.assignment_action_id = pai.locking_action_id
378: and ppa1.payroll_action_id = paa1.payroll_action_id
382: and ppa1.report_category = 'RT'
383: and ppa1.action_status = 'C')
384: and not exists
385: (select 'x' from pay_Action_interlocks pai,
386: pay_assignment_actions paa1,
387: pay_payroll_actions ppa1
388: where pai.locked_action_id = paa.assignment_action_id
389: and paa1.assignment_action_id = pai.locking_action_id
390: and ppa1.payroll_action_id = paa1.payroll_action_id
439:
440: cursor get_paper_details (cp_assignment_action_id in number) is
441: select creation_date
442: from pay_payroll_actions ppa,
443: pay_assignment_actions paa
444: where paa.assignment_action_id = cp_assignment_action_id
445: and ppa.payroll_action_id = paa.payroll_action_id;
446:
447:
481:
482: select sysdate into lv_sysdate from dual;
483:
484:
485: select pay_assignment_actions_s.nextval
486: into ln_w2c_asg_action
487: from dual;
488: hr_utility.set_location(gv_package || '.action_creation', 20);
489: hr_utility.trace('New w2c Action = ' || to_char(ln_w2c_asg_action));
487: from dual;
488: hr_utility.set_location(gv_package || '.action_creation', 20);
489: hr_utility.trace('New w2c Action = ' || to_char(ln_w2c_asg_action));
490:
491: /* Insert into pay_assignment_actions. */
492: hr_utility.trace('Creating Assignment Action');
493:
494: hr_nonrun_asact.insact(ln_w2c_asg_action
495: ,lp_assignment_id
503: table */
504:
505: hr_utility.set_location(gv_package || '.action_creation', 30);
506: hr_utility.trace('updating asg action');
507: update pay_assignment_actions aa
508: set aa.serial_number = lp_person_id
509: where aa.assignment_action_id = ln_w2c_asg_action;
510:
511: /* Interlock the w2c report action with current mark w2c action */