[Home] [Help]
126: ,ppa.effective_date
127: ,ppa.report_type
128: ,ppa.report_qualifier
129: ,ppa.business_group_id
130: from pay_payroll_actions ppa
131: where payroll_action_id = cp_payroll_action_id;
132:
133: ld_start_date date;
134: ld_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
251: and ppa.action_type = 'X'
252: and ppa.report_type = 'W-2C PAPER'
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
267: and ppa1.effective_date between cpd_start_date and cpd_end_date
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
279: and ppa1.effective_date between cpd_start_date and cpd_end_date
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
343: and paa.assignment_id = cp_assignment_id
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(''' ||
434: to_char(ld_start_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
435: and to_date(''' || to_char(ld_end_date, 'dd-mon-yyyy') || ''',''dd-mon-yyyy'')
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
452: and ppa1.effective_date between to_date(''' ||
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
465: and ppa1.effective_date between to_date(''' ||
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
510: and ppa.action_type = 'X'
511: and ppa.report_type = 'W-2C PAPER'
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
527: and ppa1.effective_date between cp_start_date and cp_end_date
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
539: and ppa1.effective_date between cp_start_date and cp_end_date
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
555: and ppa1.payroll_action_id = paa1.payroll_action_id
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
555: and ppa1.payroll_action_id = paa1.payroll_action_id
556: and ppa1.effective_date between cp_start_date and cp_end_date
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
588: and ppa.action_type = 'X'
589: and ppa.report_type = 'W-2C PAPER'
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
605: and ppa1.effective_date between cp_start_date
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
618: and ppa1.effective_date between cp_start_date and cp_end_date
623: and ppa1.action_status = 'C'
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'
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
656: and ppa.action_type = 'X'
657: and ppa.report_type = 'W-2C PAPER'
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
674: and ppa1.effective_date between cpd_start_date and cpd_end_date
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
686: and ppa1.effective_date between cpd_start_date and cpd_end_date
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
728: and ppa.action_type = 'X'
729: and ppa.report_type = 'W-2C PAPER'
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
745: and ppa1.effective_date between cp_start_date
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
759: and ppa1.effective_date between cp_start_date
774: select ppa.report_type locked_report_type,
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