[Home] [Help]
249: cpd_start_date date,
250: cpd_end_date date)
251: IS
252: select paa.assignment_action_id
253: from pay_assignment_actions paa,
254: per_all_assignments_f paf,
255: pay_payroll_actions ppa
256: where ppa.business_group_id = cpn_business_group_id
257: and ppa.effective_date between cpd_start_date and cpd_end_date
265: and paf.assignment_type = 'E'
266: and not exists
267: (select 'x'
268: from pay_Action_interlocks pai,
269: pay_assignment_actions paa1,
270: pay_payroll_actions ppa1
271: where pai.locked_action_id = paa.assignment_action_id
272: and paa1.assignment_action_id = pai.locking_action_id
273: and ppa1.payroll_action_id = paa1.payroll_action_id
277: and ppa1.action_status = 'C')
278: and not exists
279: (select 'x'
280: from pay_Action_interlocks pai,
281: pay_assignment_actions paa1,
282: pay_payroll_actions ppa1
283: where pai.locked_action_id = paa.assignment_action_id
284: and paa1.assignment_action_id = pai.locking_action_id
285: and ppa1.payroll_action_id = paa1.payroll_action_id
341: ,cp_w2c_tax_unit_id in number
342: ,cp_assignment_id in number) is
343: select ppa.payroll_action_id,
344: paa.assignment_action_id
345: from pay_assignment_actions paa,
346: pay_payroll_actions ppa
347: where ppa.payroll_action_id = paa.payroll_action_id
348: and ppa.report_type = 'YREND'
349: and ppa.effective_date = cp_w2c_eff_date
432: if (lv_report_type = 'W2C' and lv_report_qualifier = 'FED') then
433: hr_utility.set_location(gv_package || '.w2c_mag_range_cursor', 40);
434: lv_sql_string :=
435: 'select distinct paf.person_id
436: from pay_assignment_actions paa,
437: per_all_assignments_f paf,
438: pay_payroll_actions ppa
439: where ppa.business_group_id = '|| ln_business_group_id || '
440: and ppa.effective_date between to_date(''' ||
450: and paf.assignment_type = ''E''
451: and :payroll_action_id is not null
452: and not exists
453: (select ''x'' from pay_Action_interlocks pai,
454: pay_assignment_actions paa1,
455: pay_payroll_actions ppa1
456: where pai.locked_action_id = paa.assignment_action_id
457: and paa1.assignment_action_id = pai.locking_action_id
458: and ppa1.payroll_action_id = paa1.payroll_action_id
463: and ppa1.report_type = ''MARK_W2C_PAPER''
464: and ppa1.action_status = ''C'')
465: and not exists
466: (select ''x'' from pay_Action_interlocks pai,
467: pay_assignment_actions paa1,
468: pay_payroll_actions ppa1
469: where pai.locked_action_id = paa.assignment_action_id
470: and paa1.assignment_action_id = pai.locking_action_id
471: and ppa1.payroll_action_id = paa1.payroll_action_id
509: )
510: IS
511: select distinct paa.assignment_id,
512: paf.person_id
513: from pay_assignment_actions paa,
514: per_all_assignments_f paf,
515: pay_payroll_actions ppa
516: where ppa.business_group_id = cp_business_group_id
517: and ppa.effective_date between cp_start_date and cp_end_date
526: and paf.person_id between cp_start_person_id
527: and cp_end_person_id
528: and not exists
529: (select 'x' from pay_Action_interlocks pai,
530: pay_assignment_actions paa1,
531: pay_payroll_actions ppa1
532: where pai.locked_action_id = paa.assignment_action_id
533: and paa1.assignment_action_id = pai.locking_action_id
534: and ppa1.payroll_action_id = paa1.payroll_action_id
538: and ppa1.report_category = 'RT'
539: and ppa1.action_status = 'C')
540: and not exists
541: (select 'x' from pay_Action_interlocks pai,
542: pay_assignment_actions paa1,
543: pay_payroll_actions ppa1
544: where pai.locked_action_id = paa.assignment_action_id
545: and paa1.assignment_action_id = pai.locking_action_id
546: and ppa1.payroll_action_id = paa1.payroll_action_id
553: )
554: and exists
555: (select 'x'
556: from pay_Action_interlocks pai,
557: pay_assignment_actions paa1,
558: pay_assignment_actions paa2,
559: pay_payroll_actions ppa1,
560: pay_payroll_actions ppa2
561: where paa2.assignment_Action_id = pai.locked_action_id
554: and exists
555: (select 'x'
556: from pay_Action_interlocks pai,
557: pay_assignment_actions paa1,
558: pay_assignment_actions paa2,
559: pay_payroll_actions ppa1,
560: pay_payroll_actions ppa2
561: where paa2.assignment_Action_id = pai.locked_action_id
562: and paa1.assignment_action_id = pai.locking_action_id
587: paf.person_id,
588: paa.assignment_Action_id, -- Maximum Assignment Action_ID
589: to_number(substr(paa.serial_number,1,15)) w2c_pp_asg_actid,
590: to_number(substr(paa.serial_number,16,30)) w2c_pp_locked_actid
591: from pay_assignment_actions paa,
592: per_all_assignments_f paf,
593: pay_payroll_actions ppa
594: where ppa.business_group_id = cp_business_group_id
595: and ppa.effective_date between cp_start_date and cp_end_date
604: and paf.person_id between cp_start_person_id
605: and cp_end_person_id
606: and not exists
607: (select 'x' from pay_Action_interlocks pai,
608: pay_assignment_actions paa1,
609: pay_payroll_actions ppa1
610: where pai.locked_action_id = paa.assignment_action_id
611: and paa1.assignment_action_id = pai.locking_action_id
612: and ppa1.payroll_action_id = paa1.payroll_action_id
617: and ppa1.report_category = 'RT'
618: and ppa1.action_status = 'C')
619: and not exists
620: (select 'x' from pay_Action_interlocks pai,
621: pay_assignment_actions paa1,
622: pay_payroll_actions ppa1
623: where pai.locked_action_id = paa.assignment_action_id
624: and paa1.assignment_action_id = pai.locking_action_id
625: and ppa1.payroll_action_id = paa1.payroll_action_id
632: )
633: and paa.assignment_Action_id =
634: ( SELECT max(paa1.assignment_action_id)
635: FROM pay_payroll_actions ppa1,
636: pay_assignment_actions paa1
637: WHERE ppa1.payroll_action_id = paa1.payroll_Action_id
638: and ppa1.report_type IN ('W-2C PAPER','W2C_XML')
639: and ppa1.action_status = 'C'
640: and ppa1.effective_date between cp_start_date and cp_end_date
658: IS
659: --bug 7504239
660: select distinct paa.assignment_action_id
661: --bug 7504239
662: from pay_assignment_actions paa,
663: per_all_assignments_f paf,
664: pay_payroll_actions ppa
665: where ppa.business_group_id = cpn_business_group_id
666: and ppa.effective_date between cpd_start_date and cpd_end_date
676: and paf.assignment_type = 'E'
677: and not exists
678: (select 'x'
679: from pay_Action_interlocks pai,
680: pay_assignment_actions paa1,
681: pay_payroll_actions ppa1
682: where pai.locked_action_id = paa.assignment_action_id
683: and paa1.assignment_action_id = pai.locking_action_id
684: and ppa1.payroll_action_id = paa1.payroll_action_id
688: and ppa1.action_status = 'C')
689: and not exists
690: (select 'x'
691: from pay_Action_interlocks pai,
692: pay_assignment_actions paa1,
693: pay_payroll_actions ppa1
694: where pai.locked_action_id = paa.assignment_action_id
695: and paa1.assignment_action_id = pai.locking_action_id
696: and ppa1.payroll_action_id = paa1.payroll_action_id
730: --ppa.report_type,
731: paa.assignment_Action_id,
732: ppa.payroll_action_id,
733: to_number(substr(paa.serial_number,1,15)) w2c_pp_asg_actid
734: from pay_assignment_actions paa,
735: per_all_assignments_f paf,
736: pay_payroll_actions ppa
737: where ppa.business_group_id = cp_business_group_id
738: and ppa.effective_date between cp_start_date and cp_end_date
747: and paf.person_id = cp_person_id
748: and paa.assignment_Action_id < cp_w2c_paper_action_id
749: and exists ((select 'x'
750: from pay_Action_interlocks pai,
751: pay_assignment_actions paa1,
752: pay_payroll_actions ppa1
753: where pai.locked_action_id = paa.assignment_action_id
754: and paa1.assignment_action_id = pai.locking_action_id
755: and ppa1.payroll_action_id = paa1.payroll_action_id
761: and ppa1.action_status = 'C')
762: UNION ALL
763: (select 'x'
764: from pay_Action_interlocks pai,
765: pay_assignment_actions paa1,
766: pay_payroll_actions ppa1
767: where pai.locked_action_id = paa.assignment_action_id
768: and paa1.assignment_action_id = pai.locking_action_id
769: and ppa1.payroll_action_id = paa1.payroll_action_id
786: ppa.payroll_action_id locked_paction_id,
787: paa.assignment_action_id locked_action_id,
788: paa.serial_number serial_number
789: from pay_payroll_actions ppa,
790: pay_assignment_actions paa,
791: pay_action_interlocks pai
792: where pai.locking_action_id = cp_locking_action
793: and paa.assignment_action_id = pai.locked_action_id
794: and ppa.payroll_action_id = paa.payroll_action_id;
850: ln_last_w2cp_pactid NUMBER;
851: ln_last_w2cp_asg_actid NUMBER;
852: ln_last_w2cpp_asg_action_id NUMBER;
853:
854: ln_serial_number pay_assignment_actions.serial_number%TYPE;
855: ln_notlocked_asgn_actid number;
856:
857: BEGIN
858: hr_utility.set_location(gv_package || '.action_creation', 10);
929: hr_utility.trace('Originally Reported Assignment_Action_ID '||
930: to_char(ln_orig_reported_asg_action));
931:
932: /* Create an assignment action for this person */
933: select pay_assignment_actions_s.nextval
934: into ln_w2c_asg_action
935: from dual;
936: hr_utility.set_location(gv_package || '.action_creation', 120);
937: hr_utility.trace('New w2c Action = ' || to_char(ln_w2c_asg_action));
935: from dual;
936: hr_utility.set_location(gv_package || '.action_creation', 120);
937: hr_utility.trace('New w2c Action = ' || to_char(ln_w2c_asg_action));
938:
939: /* Insert into pay_assignment_actions. */
940: hr_utility.trace('Creating Assignment Action');
941:
942: hr_nonrun_asact.insact(ln_w2c_asg_action
943: ,lp_assignment_id
957: ** of the last two archive processes
958: *************************************************************/
959: ln_serial_number := lpad(ln_corrected_asg_action,15,0)||
960: lpad(ln_orig_reported_asg_action,15,0);
961: update pay_assignment_actions aa
962: set aa.serial_number = ln_serial_number
963: where aa.assignment_action_id = ln_w2c_asg_action;
964: hr_utility.set_location(gv_package || '.action_creation', 140);
965: