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