[Home] [Help]
408: paa.action_sequence,
409: paaf.assignment_id,
410: paa.tax_unit_id
411: from per_people_f pap,
412: per_assignments_f paaf,
413: pay_payroll_actions ppa,
414: pay_payroll_actions ppa1,
415: pay_assignment_actions paa,
416: hr_organization_units hou,
433: and hou.organization_id = c_organization_id
434: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
435: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --4161540
436: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
437: From per_assignments_f iipaf
438: WHERE iipaf.assignment_id = paaf.assignment_id
439: and iipaf.effective_end_date >= c_archive_start_date
440: and iipaf.effective_start_date <= c_archive_end_date)
441: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
460: paa.action_sequence,
461: paaf.assignment_id,
462: paa.tax_unit_id
463: from per_people_f pap,
464: per_assignments_f paaf,
465: pay_payroll_actions ppa,
466: pay_payroll_actions ppa1,
467: pay_assignment_actions paa,
468: hr_organization_units hou,
487: and hou.organization_id = c_organization_id
488: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
489: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --4161540
490: and paaf.effective_end_date = (select max(effective_end_date)
491: From per_assignments_f iipaf
492: WHERE iipaf.assignment_id = paaf.assignment_id
493: and iipaf.effective_end_date >= c_archive_start_date
494: and iipaf.effective_start_date <= c_archive_end_date)
495: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
515: paa.action_sequence,
516: paaf.assignment_id,
517: paa.tax_unit_id
518: from per_people_f pap,
519: per_assignments_f paaf,
520: pay_payroll_actions ppa,
521: pay_payroll_actions ppa1,
522: pay_assignment_actions paa,
523: hr_organization_units hou,
562: paa.action_sequence,
563: paaf.assignment_id,
564: paa.tax_unit_id
565: from per_people_f pap,
566: per_assignments_f paaf,
567: pay_payroll_actions ppa,
568: pay_payroll_actions ppa1,
569: pay_assignment_actions paa,
570: hr_organization_units hou,
612: paa.action_sequence,
613: paaf.assignment_id,
614: paa.tax_unit_id
615: from per_people_f pap,
616: per_assignments_f paaf,
617: pay_payroll_actions ppa,
618: pay_payroll_actions ppa1,
619: pay_assignment_actions paa,
620: per_periods_of_service pps
634: and paa.tax_unit_id = c_legal_employer
635: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
636: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --bug 4161540
637: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
638: From per_assignments_f iipaf
639: WHERE iipaf.assignment_id = paaf.assignment_id
640: and iipaf.effective_end_date >= c_archive_start_date
641: and iipaf.effective_start_date <= c_archive_end_date)
642: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
662: paa.action_sequence,
663: paaf.assignment_id,
664: paa.tax_unit_id
665: from per_people_f pap,
666: per_assignments_f paaf,
667: pay_payroll_actions ppa,
668: pay_payroll_actions ppa1,
669: pay_assignment_actions paa,
670: per_periods_of_service pps,
686: and paa.tax_unit_id = c_legal_employer
687: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
688: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --bug 4161540
689: and paaf.effective_end_date = (select max(effective_end_date)
690: From per_assignments_f iipaf
691: WHERE iipaf.assignment_id = paaf.assignment_id
692: and iipaf.effective_end_date >= c_archive_start_date
693: and iipaf.effective_start_date <= c_archive_end_date)
694: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
714: paa.action_sequence,
715: paaf.assignment_id,
716: paa.tax_unit_id
717: from per_people_f pap,
718: per_assignments_f paaf,
719: pay_payroll_actions ppa,
720: pay_payroll_actions ppa1,
721: pay_assignment_actions paa,
722: per_periods_of_service pps
759: paa.action_sequence,
760: paaf.assignment_id,
761: paa.tax_unit_id
762: from per_people_f pap,
763: per_assignments_f paaf,
764: pay_payroll_actions ppa,
765: pay_payroll_actions ppa1,
766: pay_assignment_actions paa,
767: per_periods_of_service pps,
806: paa.action_sequence,
807: paaf.assignment_id,
808: paa.tax_unit_id
809: from per_people_f pap,
810: per_assignments_f paaf,
811: pay_payroll_actions ppa,
812: pay_payroll_actions ppa1,
813: pay_assignment_actions paa,
814: per_periods_of_service pps
828: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
829: AND paaf.payroll_id = c_payroll_id /*Bug 4040688*/
830: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
831: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
832: From per_assignments_f iipaf
833: WHERE iipaf.assignment_id = paaf.assignment_id
834: and iipaf.effective_end_date >= c_archive_start_date
835: and iipaf.effective_start_date <= c_archive_end_date
836: AND iipaf.payroll_id IS NOT NULL) /*Bug 4688872*/
853: ,c_payroll_id pay_payroll_actions.payroll_id%type
854: ,c_archive_start_date date
855: ,c_archive_end_date date
856: ) is
857: select /*+ LEADING (ppa ppr pap paaf pps) INDEX(paaf PER_ASSIGNMENTS_F_N12)*/
858: paa.assignment_action_id,
859: paa.action_sequence,
860: paaf.assignment_id,
861: paa.tax_unit_id
861: paa.tax_unit_id
862: from pay_payroll_actions ppa,
863: pay_population_ranges ppr,
864: per_people_f pap,
865: per_assignments_f paaf,
866: per_periods_of_service pps,
867: pay_assignment_actions paa,
868: pay_payroll_actions ppa1
869: where ppa.payroll_action_id = c_payroll_action_id
883: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
884: AND paaf.payroll_id = c_payroll_id
885: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
886: and paaf.effective_end_date = (select max(effective_end_date)
887: From per_assignments_f iipaf
888: WHERE iipaf.assignment_id = paaf.assignment_id
889: and iipaf.effective_end_date >= c_archive_start_date
890: and iipaf.effective_start_date <= c_archive_end_date
891: AND iipaf.payroll_id IS NOT NULL)
911: paa.action_sequence,
912: paaf.assignment_id,
913: paa.tax_unit_id
914: from per_people_f pap,
915: per_assignments_f paaf,
916: pay_payroll_actions ppa,
917: pay_payroll_actions ppa1,
918: pay_assignment_actions paa,
919: per_periods_of_service pps
957: paa.action_sequence,
958: paaf.assignment_id,
959: paa.tax_unit_id
960: from per_people_f pap,
961: per_assignments_f paaf,
962: pay_payroll_actions ppa,
963: pay_payroll_actions ppa1,
964: pay_assignment_actions paa,
965: per_periods_of_service pps,
1005: paa.action_sequence,
1006: paaf.assignment_id,
1007: paa.tax_unit_id
1008: from per_people_f pap,
1009: per_assignments_f paaf,
1010: pay_payroll_actions ppa,
1011: pay_payroll_actions ppa1,
1012: pay_assignment_actions paa,
1013: per_periods_of_service pps
1027: and paa.assignment_id = c_assignment_id
1028: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1029: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
1030: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1031: From per_assignments_f iipaf
1032: WHERE iipaf.assignment_id = paaf.assignment_id
1033: and iipaf.effective_end_date >= c_archive_start_date
1034: and iipaf.effective_start_date <= c_archive_end_date)
1035: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1054: paa.action_sequence,
1055: paaf.assignment_id,
1056: paa.tax_unit_id
1057: from per_people_f pap,
1058: per_assignments_f paaf,
1059: pay_payroll_actions ppa,
1060: pay_payroll_actions ppa1,
1061: pay_assignment_actions paa,
1062: per_periods_of_service pps,
1078: and paa.assignment_id = c_assignment_id
1079: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1080: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
1081: and paaf.effective_end_date = (select max(effective_end_date)
1082: From per_assignments_f iipaf
1083: WHERE iipaf.assignment_id = paaf.assignment_id
1084: and iipaf.effective_end_date >= c_archive_start_date
1085: and iipaf.effective_start_date <= c_archive_end_date)
1086: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1105: paa.action_sequence,
1106: paaf.assignment_id,
1107: paa.tax_unit_id
1108: from per_people_f pap,
1109: per_assignments_f paaf,
1110: pay_payroll_actions ppa,
1111: pay_payroll_actions ppa1,
1112: pay_assignment_actions paa,
1113: per_periods_of_service pps
1149: paa.action_sequence,
1150: paaf.assignment_id,
1151: paa.tax_unit_id
1152: from per_people_f pap,
1153: per_assignments_f paaf,
1154: pay_payroll_actions ppa,
1155: pay_payroll_actions ppa1,
1156: pay_assignment_actions paa,
1157: per_periods_of_service pps,
1195: paa.action_sequence,
1196: paaf.assignment_id,
1197: paa.tax_unit_id
1198: from per_people_f pap,
1199: per_assignments_f paaf,
1200: pay_payroll_actions ppa,
1201: pay_payroll_actions ppa1,
1202: pay_assignment_actions paa,
1203: per_periods_of_service pps
1216: and ppa1.action_type in ('R','Q','I','B','V')
1217: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1218: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
1219: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1220: From per_assignments_f iipaf
1221: WHERE iipaf.assignment_id = paaf.assignment_id
1222: and iipaf.effective_end_date >= c_archive_start_date
1223: and iipaf.effective_start_date <= c_archive_end_date)
1224: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1242: paa.action_sequence,
1243: paaf.assignment_id,
1244: paa.tax_unit_id
1245: from per_people_f pap,
1246: per_assignments_f paaf,
1247: pay_payroll_actions ppa,
1248: pay_payroll_actions ppa1,
1249: pay_assignment_actions paa,
1250: per_periods_of_service pps,
1265: and ppa1.action_type in ('R','Q','I','B','V')
1266: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1267: and decode(pps.actual_termination_date,null,'Y',decode(sign(pps.actual_termination_date - (c_archive_end_date)),1,'Y','N')) LIKE c_employee_type --Bug 4161540
1268: and paaf.effective_end_date = (select max(effective_end_date)
1269: From per_assignments_f iipaf
1270: WHERE iipaf.assignment_id = paaf.assignment_id
1271: and iipaf.effective_end_date >= c_archive_start_date
1272: and iipaf.effective_start_date <= c_archive_end_date)
1273: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1291: paa.action_sequence,
1292: paaf.assignment_id,
1293: paa.tax_unit_id
1294: from per_people_f pap,
1295: per_assignments_f paaf,
1296: pay_payroll_actions ppa,
1297: pay_payroll_actions ppa1,
1298: pay_assignment_actions paa,
1299: per_periods_of_service pps
1334: paa.action_sequence,
1335: paaf.assignment_id,
1336: paa.tax_unit_id
1337: from per_people_f pap,
1338: per_assignments_f paaf,
1339: pay_payroll_actions ppa,
1340: pay_payroll_actions ppa1,
1341: pay_assignment_actions paa,
1342: per_periods_of_service pps,
2763: -- papf.payroll_name, /*Bug 4132525, Bug 4688872*/
2764: hsc.segment1 tax_unit_id, /*Bug 4040688*/
2765: hou1.NAME Legal_Employer /*Bug 4132525*/
2766: from per_people_f pap,
2767: per_assignments_f paa,
2768: per_grades_tl pgr, /* Bug 3953702*/
2769: per_periods_of_service pps,
2770: per_pay_bases ppb,
2771: per_pay_proposals pro,
2788: and pps.period_of_service_id = paa.period_of_service_id
2789: AND paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id /*Bug 4040688*/
2790: and paa.assignment_status_type_id = past.assignment_status_type_id
2791: and paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
2792: from per_assignments_f
2793: WHERE assignment_id = c_assignment_id
2794: and effective_end_date >= c_start_date
2795: and effective_start_date <= c_end_date)
2796: and c_end_date between pap.effective_start_date and pap.effective_end_date
2814: FROM per_all_assignments_f paaf,
2815: pay_payrolls_f pay
2816: WHERE paaf.assignment_id = c_assignment_id
2817: and paaf.effective_end_date = (select max(effective_end_date)
2818: From per_assignments_f iipaf
2819: WHERE iipaf.assignment_id = c_assignment_id
2820: and iipaf.effective_end_date >= c_start_date
2821: and iipaf.effective_start_date <= c_end_date
2822: AND iipaf.payroll_id IS NOT NULL)
2882: ,pay_element_classifications pec
2883: ,pay_assignment_actions paa
2884: ,pay_assignment_actions paa2 /*Bug 3935471*/
2885: ,pay_payroll_actions ppa
2886: ,per_assignments_f paaf
2887: ,pay_run_results prr
2888: ,pay_run_result_values prrv
2889: ,pay_element_classifications pec2
2890: ,pay_sub_classification_rules_f pscr
2932: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
2933: ,max(paa.action_sequence)
2934: from pay_assignment_actions paa
2935: , pay_payroll_actions ppa
2936: , per_assignments_f paf
2937: where paa.assignment_id = paf.assignment_id
2938: and paf.assignment_id = c_assignment_id
2939: and ppa.payroll_action_id = paa.payroll_action_id
2940: and ppa.effective_date between c_start_date and c_end_date
2952: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
2953: ,max(paa.action_sequence)
2954: from pay_assignment_actions paa
2955: , pay_payroll_actions ppa
2956: , per_assignments_f paf
2957: where paa.assignment_id = paf.assignment_id
2958: and paf.assignment_id = c_assignment_id
2959: and ppa.payroll_action_id = paa.payroll_action_id
2960: and ppa.payroll_action_id = c_payroll_action_id