[Home] [Help]
133: ,ppa.effective_date
134: ,ppa.report_type
135: ,ppa.report_qualifier
136: ,ppa.business_group_id
137: from pay_payroll_actions ppa
138: where payroll_action_id = cp_payroll_action_id;
139:
140: ld_start_date date;
141: ld_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
258: and ppa.action_type = 'X'
259: and ppa.report_type IN ('W-2C PAPER','W2C_XML')
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
274: and ppa1.effective_date between cpd_start_date and cpd_end_date
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
286: and ppa1.effective_date between cpd_start_date and cpd_end_date
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
350: and paa.assignment_id = cp_assignment_id
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(''' ||
441: to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
442: and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
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
459: and ppa1.effective_date between to_date(''' ||
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
472: and ppa1.effective_date between to_date(''' ||
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
518: and ppa.action_type = 'X'
519: and ppa.report_type IN ('W-2C PAPER','W2C_XML')
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
535: and ppa1.effective_date between cp_start_date and cp_end_date
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
547: and ppa1.effective_date between cp_start_date and cp_end_date
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
563: and ppa1.payroll_action_id = paa1.payroll_action_id
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
563: and ppa1.payroll_action_id = paa1.payroll_action_id
564: and ppa1.effective_date between cp_start_date and cp_end_date
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
596: and ppa.action_type = 'X'
597: and ppa.report_type IN ('W-2C PAPER','W2C_XML')
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
613: and ppa1.effective_date between cp_start_date
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
626: and ppa1.effective_date between cp_start_date and cp_end_date
631: and ppa1.action_status = 'C'
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'
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
667: and ppa.action_type = 'X'
668: and ppa.report_type IN ('W-2C PAPER','W2C_XML')
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
685: and ppa1.effective_date between cpd_start_date and cpd_end_date
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
697: and ppa1.effective_date between cpd_start_date and cpd_end_date
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
739: and ppa.action_type = 'X'
740: and ppa.report_type IN ('W-2C PAPER','W2C_XML')
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
756: and ppa1.effective_date between cp_start_date
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
770: and ppa1.effective_date between cp_start_date
785: select ppa.report_type locked_report_type,
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