[Home] [Help]
242: cpd_start_date date,
243: cpd_end_date date)
244: IS
245: select paa.assignment_action_id
246: from pay_assignment_actions paa,
247: per_all_assignments_f paf,
248: pay_payroll_actions ppa
249: where ppa.business_group_id = cpn_business_group_id
250: and ppa.effective_date between cpd_start_date and cpd_end_date
258: and paf.assignment_type = 'E'
259: and not exists
260: (select 'x'
261: from pay_Action_interlocks pai,
262: pay_assignment_actions paa1,
263: pay_payroll_actions ppa1
264: where pai.locked_action_id = paa.assignment_action_id
265: and paa1.assignment_action_id = pai.locking_action_id
266: and ppa1.payroll_action_id = paa1.payroll_action_id
270: and ppa1.action_status = 'C')
271: and not exists
272: (select 'x'
273: from pay_Action_interlocks pai,
274: pay_assignment_actions paa1,
275: pay_payroll_actions ppa1
276: where pai.locked_action_id = paa.assignment_action_id
277: and paa1.assignment_action_id = pai.locking_action_id
278: and ppa1.payroll_action_id = paa1.payroll_action_id
334: ,cp_w2c_tax_unit_id in number
335: ,cp_assignment_id in number) is
336: select ppa.payroll_action_id,
337: paa.assignment_action_id
338: from pay_assignment_actions paa,
339: pay_payroll_actions ppa
340: where ppa.payroll_action_id = paa.payroll_action_id
341: and ppa.report_type = 'YREND'
342: and ppa.effective_date = cp_w2c_eff_date
425: if (lv_report_type = 'W2C' and lv_report_qualifier = 'FED') then
426: hr_utility.set_location(gv_package || '.w2c_mag_range_cursor', 40);
427: lv_sql_string :=
428: 'select distinct paf.person_id
429: from pay_assignment_actions paa,
430: per_all_assignments_f paf,
431: pay_payroll_actions ppa
432: where ppa.business_group_id = '|| ln_business_group_id || '
433: and ppa.effective_date between to_date(''' ||
443: and paf.assignment_type = ''E''
444: and :payroll_action_id is not null
445: and not exists
446: (select ''x'' from pay_Action_interlocks pai,
447: pay_assignment_actions paa1,
448: pay_payroll_actions ppa1
449: where pai.locked_action_id = paa.assignment_action_id
450: and paa1.assignment_action_id = pai.locking_action_id
451: and ppa1.payroll_action_id = paa1.payroll_action_id
456: and ppa1.report_type = ''MARK_W2C_PAPER''
457: and ppa1.action_status = ''C'')
458: and not exists
459: (select ''x'' from pay_Action_interlocks pai,
460: pay_assignment_actions paa1,
461: pay_payroll_actions ppa1
462: where pai.locked_action_id = paa.assignment_action_id
463: and paa1.assignment_action_id = pai.locking_action_id
464: and ppa1.payroll_action_id = paa1.payroll_action_id
501: )
502: IS
503: select distinct paa.assignment_id,
504: paf.person_id
505: from pay_assignment_actions paa,
506: per_all_assignments_f paf,
507: pay_payroll_actions ppa
508: where ppa.business_group_id = cp_business_group_id
509: and ppa.effective_date between cp_start_date and cp_end_date
518: and paf.person_id between cp_start_person_id
519: and cp_end_person_id
520: and not exists
521: (select 'x' from pay_Action_interlocks pai,
522: pay_assignment_actions paa1,
523: pay_payroll_actions ppa1
524: where pai.locked_action_id = paa.assignment_action_id
525: and paa1.assignment_action_id = pai.locking_action_id
526: and ppa1.payroll_action_id = paa1.payroll_action_id
530: and ppa1.report_category = 'RT'
531: and ppa1.action_status = 'C')
532: and not exists
533: (select 'x' from pay_Action_interlocks pai,
534: pay_assignment_actions paa1,
535: pay_payroll_actions ppa1
536: where pai.locked_action_id = paa.assignment_action_id
537: and paa1.assignment_action_id = pai.locking_action_id
538: and ppa1.payroll_action_id = paa1.payroll_action_id
545: )
546: and exists
547: (select 'x'
548: from pay_Action_interlocks pai,
549: pay_assignment_actions paa1,
550: pay_assignment_actions paa2,
551: pay_payroll_actions ppa1,
552: pay_payroll_actions ppa2
553: where paa2.assignment_Action_id = pai.locked_action_id
546: and exists
547: (select 'x'
548: from pay_Action_interlocks pai,
549: pay_assignment_actions paa1,
550: pay_assignment_actions paa2,
551: pay_payroll_actions ppa1,
552: pay_payroll_actions ppa2
553: where paa2.assignment_Action_id = pai.locked_action_id
554: and paa1.assignment_action_id = pai.locking_action_id
579: paf.person_id,
580: paa.assignment_Action_id, -- Maximum Assignment Action_ID
581: to_number(substr(paa.serial_number,1,15)) w2c_pp_asg_actid,
582: to_number(substr(paa.serial_number,16,30)) w2c_pp_locked_actid
583: from pay_assignment_actions paa,
584: per_all_assignments_f paf,
585: pay_payroll_actions ppa
586: where ppa.business_group_id = cp_business_group_id
587: and ppa.effective_date between cp_start_date and cp_end_date
596: and paf.person_id between cp_start_person_id
597: and cp_end_person_id
598: and not exists
599: (select 'x' from pay_Action_interlocks pai,
600: pay_assignment_actions paa1,
601: pay_payroll_actions ppa1
602: where pai.locked_action_id = paa.assignment_action_id
603: and paa1.assignment_action_id = pai.locking_action_id
604: and ppa1.payroll_action_id = paa1.payroll_action_id
609: and ppa1.report_category = 'RT'
610: and ppa1.action_status = 'C')
611: and not exists
612: (select 'x' from pay_Action_interlocks pai,
613: pay_assignment_actions paa1,
614: pay_payroll_actions ppa1
615: where pai.locked_action_id = paa.assignment_action_id
616: and paa1.assignment_action_id = pai.locking_action_id
617: and ppa1.payroll_action_id = paa1.payroll_action_id
624: )
625: and paa.assignment_Action_id =
626: ( SELECT max(paa1.assignment_action_id)
627: FROM pay_payroll_actions ppa1,
628: pay_assignment_actions paa1
629: WHERE ppa1.payroll_action_id = paa1.payroll_Action_id
630: and ppa1.report_type = 'W-2C PAPER'
631: and ppa1.action_status = 'C'
632: and ppa1.effective_date between cp_start_date and cp_end_date
647: cpn_assignment_id number,
648: cpn_max_asgn_action_id number)
649: IS
650: select paa.assignment_action_id
651: from pay_assignment_actions paa,
652: per_all_assignments_f paf,
653: pay_payroll_actions ppa
654: where ppa.business_group_id = cpn_business_group_id
655: and ppa.effective_date between cpd_start_date and cpd_end_date
665: and paf.assignment_type = 'E'
666: and not exists
667: (select 'x'
668: from pay_Action_interlocks pai,
669: pay_assignment_actions paa1,
670: pay_payroll_actions ppa1
671: where pai.locked_action_id = paa.assignment_action_id
672: and paa1.assignment_action_id = pai.locking_action_id
673: and ppa1.payroll_action_id = paa1.payroll_action_id
677: and ppa1.action_status = 'C')
678: and not exists
679: (select 'x'
680: from pay_Action_interlocks pai,
681: pay_assignment_actions paa1,
682: pay_payroll_actions ppa1
683: where pai.locked_action_id = paa.assignment_action_id
684: and paa1.assignment_action_id = pai.locking_action_id
685: and ppa1.payroll_action_id = paa1.payroll_action_id
719: --ppa.report_type,
720: paa.assignment_Action_id,
721: ppa.payroll_action_id,
722: to_number(substr(paa.serial_number,1,15)) w2c_pp_asg_actid
723: from pay_assignment_actions paa,
724: per_all_assignments_f paf,
725: pay_payroll_actions ppa
726: where ppa.business_group_id = cp_business_group_id
727: and ppa.effective_date between cp_start_date and cp_end_date
736: and paf.person_id = cp_person_id
737: and paa.assignment_Action_id < cp_w2c_paper_action_id
738: and exists ((select 'x'
739: from pay_Action_interlocks pai,
740: pay_assignment_actions paa1,
741: pay_payroll_actions ppa1
742: where pai.locked_action_id = paa.assignment_action_id
743: and paa1.assignment_action_id = pai.locking_action_id
744: and ppa1.payroll_action_id = paa1.payroll_action_id
750: and ppa1.action_status = 'C')
751: UNION ALL
752: (select 'x'
753: from pay_Action_interlocks pai,
754: pay_assignment_actions paa1,
755: pay_payroll_actions ppa1
756: where pai.locked_action_id = paa.assignment_action_id
757: and paa1.assignment_action_id = pai.locking_action_id
758: and ppa1.payroll_action_id = paa1.payroll_action_id
775: ppa.payroll_action_id locked_paction_id,
776: paa.assignment_action_id locked_action_id,
777: paa.serial_number serial_number
778: from pay_payroll_actions ppa,
779: pay_assignment_actions paa,
780: pay_action_interlocks pai
781: where pai.locking_action_id = cp_locking_action
782: and paa.assignment_action_id = pai.locked_action_id
783: and ppa.payroll_action_id = paa.payroll_action_id;
839: ln_last_w2cp_pactid NUMBER;
840: ln_last_w2cp_asg_actid NUMBER;
841: ln_last_w2cpp_asg_action_id NUMBER;
842:
843: ln_serial_number pay_assignment_actions.serial_number%TYPE;
844: ln_notlocked_asgn_actid number;
845:
846: BEGIN
847: hr_utility.set_location(gv_package || '.action_creation', 10);
918: hr_utility.trace('Originally Reported Assignment_Action_ID '||
919: to_char(ln_orig_reported_asg_action));
920:
921: /* Create an assignment action for this person */
922: select pay_assignment_actions_s.nextval
923: into ln_w2c_asg_action
924: from dual;
925: hr_utility.set_location(gv_package || '.action_creation', 120);
926: hr_utility.trace('New w2c Action = ' || to_char(ln_w2c_asg_action));
924: from dual;
925: hr_utility.set_location(gv_package || '.action_creation', 120);
926: hr_utility.trace('New w2c Action = ' || to_char(ln_w2c_asg_action));
927:
928: /* Insert into pay_assignment_actions. */
929: hr_utility.trace('Creating Assignment Action');
930:
931: hr_nonrun_asact.insact(ln_w2c_asg_action
932: ,lp_assignment_id
946: ** of the last two archive processes
947: *************************************************************/
948: ln_serial_number := lpad(ln_corrected_asg_action,15,0)||
949: lpad(ln_orig_reported_asg_action,15,0);
950: update pay_assignment_actions aa
951: set aa.serial_number = ln_serial_number
952: where aa.assignment_action_id = ln_w2c_asg_action;
953: hr_utility.set_location(gv_package || '.action_creation', 140);
954: