[Home] [Help]
636: paa.action_sequence,
637: paaf.assignment_id,
638: paa.tax_unit_id
639: from per_people_f pap,
640: per_assignments_f paaf,
641: pay_payroll_actions ppa,
642: pay_payroll_actions ppa1,
643: pay_assignment_actions paa,
644: hr_organization_units hou,
661: and hou.organization_id = c_organization_id
662: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
663: 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
664: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
665: From per_assignments_f iipaf
666: WHERE iipaf.assignment_id = paaf.assignment_id
667: and iipaf.effective_end_date >= c_archive_start_date
668: and iipaf.effective_start_date <= c_archive_end_date)
669: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
688: paa.action_sequence,
689: paaf.assignment_id,
690: paa.tax_unit_id
691: from per_people_f pap,
692: per_assignments_f paaf,
693: pay_payroll_actions ppa,
694: pay_payroll_actions ppa1,
695: pay_assignment_actions paa,
696: hr_organization_units hou,
716: and hou.organization_id = c_organization_id
717: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
718: 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
719: and paaf.effective_end_date = (select max(effective_end_date)
720: From per_assignments_f iipaf
721: WHERE iipaf.assignment_id = paaf.assignment_id
722: and iipaf.effective_end_date >= c_archive_start_date
723: and iipaf.effective_start_date <= c_archive_end_date)
724: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
743: paa.action_sequence,
744: paaf.assignment_id,
745: paa.tax_unit_id
746: from per_people_f pap,
747: per_assignments_f paaf,
748: pay_payroll_actions ppa,
749: pay_payroll_actions ppa1,
750: pay_assignment_actions paa,
751: hr_organization_units hou,
791: paa.action_sequence,
792: paaf.assignment_id,
793: paa.tax_unit_id
794: from per_people_f pap,
795: per_assignments_f paaf,
796: pay_payroll_actions ppa,
797: pay_payroll_actions ppa1,
798: pay_assignment_actions paa,
799: hr_organization_units hou,
847: paa.tax_unit_id,
848: paa.source_action_id master_action_id, /*Bug# 3935471*/
849: paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
850: from per_people_f pap,
851: per_assignments_f paaf,
852: pay_payroll_actions ppa,
853: pay_payroll_actions ppa1,
854: pay_assignment_actions paa,
855: pay_assignment_actions paa2, /*Bug# 3935471*/
873: and paa.tax_unit_id = c_legal_employer
874: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
875: 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
876: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
877: From per_assignments_f iipaf
878: WHERE iipaf.assignment_id = paaf.assignment_id
879: and iipaf.effective_end_date >= c_archive_start_date
880: and iipaf.effective_start_date <= c_archive_end_date)
881: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
897: ,c_legal_employer hr_all_organization_units.organization_id%type
898: ,c_archive_start_date date
899: ,c_archive_end_date date
900: ) is
901: select /*+ LEADING (ppa ppr pap paaf pps) INDEX(paaf PER_ASSIGNMENTS_F_N12) */
902: paa.assignment_action_id,
903: paa.action_sequence,
904: paaf.assignment_id,
905: paa.tax_unit_id,
905: paa.tax_unit_id,
906: paa.source_action_id master_action_id,
907: paa2.tax_unit_id master_tax_unit_id
908: from per_people_f pap,
909: per_assignments_f paaf,
910: pay_payroll_actions ppa,
911: pay_payroll_actions ppa1,
912: pay_assignment_actions paa,
913: pay_assignment_actions paa2,
934: and paa.tax_unit_id = c_legal_employer
935: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
936: 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
937: and paaf.effective_end_date = (select max(effective_end_date)
938: From per_assignments_f iipaf
939: WHERE iipaf.assignment_id = paaf.assignment_id
940: and iipaf.effective_end_date >= c_archive_start_date
941: and iipaf.effective_start_date <= c_archive_end_date)
942: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id, paa.source_action_id, paa2.tax_unit_id; /*Bug# 3935471*/
967: paa.tax_unit_id,
968: paa.source_action_id master_action_id, /*Bug# 3935471*/
969: paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
970: from per_people_f pap,
971: per_assignments_f paaf,
972: pay_payroll_actions ppa,
973: pay_payroll_actions ppa1,
974: pay_assignment_actions paa,
975: pay_assignment_actions paa2, /*Bug# 3935471*/
1018: paa.tax_unit_id,
1019: paa.source_action_id master_action_id,
1020: paa2.tax_unit_id master_tax_unit_id
1021: from per_people_f pap,
1022: per_assignments_f paaf,
1023: pay_payroll_actions ppa,
1024: pay_payroll_actions ppa1,
1025: pay_assignment_actions paa,
1026: pay_assignment_actions paa2,
1070: paa.action_sequence,
1071: paaf.assignment_id,
1072: paa.tax_unit_id
1073: from per_people_f pap,
1074: per_assignments_f paaf,
1075: pay_payroll_actions ppa,
1076: pay_payroll_actions ppa1,
1077: pay_assignment_actions paa,
1078: per_periods_of_service pps
1092: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1093: AND paaf.payroll_id = c_payroll_id /*Bug 4040688*/
1094: 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
1095: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1096: From per_assignments_f iipaf
1097: WHERE iipaf.assignment_id = paaf.assignment_id
1098: and iipaf.effective_end_date >= c_archive_start_date
1099: and iipaf.effective_start_date <= c_archive_end_date
1100: AND iipaf.payroll_id IS NOT NULL) /*Bug#4688800*/
1117: ,c_payroll_id pay_payroll_actions.payroll_id%type
1118: ,c_archive_start_date date
1119: ,c_archive_end_date date
1120: ) is
1121: select /*+ LEADING (ppa ppr pap paaf pps) INDEX(paaf PER_ASSIGNMENTS_F_N12)*/
1122: paa.assignment_action_id,
1123: paa.action_sequence,
1124: paaf.assignment_id,
1125: paa.tax_unit_id
1125: paa.tax_unit_id
1126: from pay_payroll_actions ppa,
1127: pay_population_ranges ppr,
1128: per_people_f pap,
1129: per_assignments_f paaf,
1130: per_periods_of_service pps,
1131: pay_assignment_actions paa,
1132: pay_payroll_actions ppa1
1133: where ppa.payroll_action_id = c_payroll_action_id
1148: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1149: AND paaf.payroll_id = c_payroll_id
1150: 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
1151: and paaf.effective_end_date = (select max(effective_end_date)
1152: From per_assignments_f iipaf
1153: WHERE iipaf.assignment_id = paaf.assignment_id
1154: and iipaf.effective_end_date >= c_archive_start_date
1155: and iipaf.effective_start_date <= c_archive_end_date
1156: AND iipaf.payroll_id IS NOT NULL)
1177: paa.action_sequence,
1178: paaf.assignment_id,
1179: paa.tax_unit_id
1180: from per_people_f pap,
1181: per_assignments_f paaf,
1182: pay_payroll_actions ppa,
1183: pay_payroll_actions ppa1,
1184: pay_assignment_actions paa,
1185: per_periods_of_service pps
1222: paa.action_sequence,
1223: paaf.assignment_id,
1224: paa.tax_unit_id
1225: from per_people_f pap,
1226: per_assignments_f paaf,
1227: pay_payroll_actions ppa,
1228: pay_payroll_actions ppa1,
1229: pay_assignment_actions paa,
1230: per_periods_of_service pps,
1271: paa.action_sequence,
1272: paaf.assignment_id,
1273: paa.tax_unit_id
1274: from per_people_f pap,
1275: per_assignments_f paaf,
1276: pay_payroll_actions ppa,
1277: pay_payroll_actions ppa1,
1278: pay_assignment_actions paa,
1279: per_periods_of_service pps
1293: and paa.assignment_id = c_assignment_id
1294: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1295: 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
1296: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1297: From per_assignments_f iipaf
1298: WHERE iipaf.assignment_id = paaf.assignment_id
1299: and iipaf.effective_end_date >= c_archive_start_date
1300: and iipaf.effective_start_date <= c_archive_end_date)
1301: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1320: paa.action_sequence,
1321: paaf.assignment_id,
1322: paa.tax_unit_id
1323: from per_people_f pap,
1324: per_assignments_f paaf,
1325: pay_payroll_actions ppa,
1326: pay_payroll_actions ppa1,
1327: pay_assignment_actions paa,
1328: per_periods_of_service pps,
1345: and paa.assignment_id = c_assignment_id
1346: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1347: 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
1348: and paaf.effective_end_date = (select max(effective_end_date)
1349: From per_assignments_f iipaf
1350: WHERE iipaf.assignment_id = paaf.assignment_id
1351: and iipaf.effective_end_date >= c_archive_start_date
1352: and iipaf.effective_start_date <= c_archive_end_date)
1353: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1373: paa.action_sequence,
1374: paaf.assignment_id,
1375: paa.tax_unit_id
1376: from per_people_f pap,
1377: per_assignments_f paaf,
1378: pay_payroll_actions ppa,
1379: pay_payroll_actions ppa1,
1380: pay_assignment_actions paa,
1381: per_periods_of_service pps
1418: paa.action_sequence,
1419: paaf.assignment_id,
1420: paa.tax_unit_id
1421: from per_people_f pap,
1422: per_assignments_f paaf,
1423: pay_payroll_actions ppa,
1424: pay_payroll_actions ppa1,
1425: pay_assignment_actions paa,
1426: per_periods_of_service pps,
1466: paa.action_sequence,
1467: paaf.assignment_id,
1468: paa.tax_unit_id
1469: from per_people_f pap,
1470: per_assignments_f paaf,
1471: pay_payroll_actions ppa,
1472: pay_payroll_actions ppa1,
1473: pay_assignment_actions paa,
1474: per_periods_of_service pps
1487: and paa.source_action_id is null
1488: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1489: 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
1490: and paaf.effective_end_date = (select max(effective_end_date) --Bug# 3538810
1491: From per_assignments_f iipaf
1492: WHERE iipaf.assignment_id = paaf.assignment_id
1493: and iipaf.effective_end_date >= c_archive_start_date
1494: and iipaf.effective_start_date <= c_archive_end_date)
1495: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1513: paa.action_sequence,
1514: paaf.assignment_id,
1515: paa.tax_unit_id
1516: from per_people_f pap,
1517: per_assignments_f paaf,
1518: pay_payroll_actions ppa,
1519: pay_payroll_actions ppa1,
1520: pay_assignment_actions paa,
1521: per_periods_of_service pps,
1537: and paa.source_action_id is null
1538: and ppa1.effective_date between c_archive_start_date and c_archive_end_date
1539: 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
1540: and paaf.effective_end_date = (select max(effective_end_date)
1541: From per_assignments_f iipaf
1542: WHERE iipaf.assignment_id = paaf.assignment_id
1543: and iipaf.effective_end_date >= c_archive_start_date
1544: and iipaf.effective_start_date <= c_archive_end_date)
1545: order by paaf.assignment_id, paa.assignment_action_id, paa.tax_unit_id;
1564: paa.action_sequence,
1565: paaf.assignment_id,
1566: paa.tax_unit_id
1567: from per_people_f pap,
1568: per_assignments_f paaf,
1569: pay_payroll_actions ppa,
1570: pay_payroll_actions ppa1,
1571: pay_assignment_actions paa,
1572: per_periods_of_service pps
1608: paa.action_sequence,
1609: paaf.assignment_id,
1610: paa.tax_unit_id
1611: from per_people_f pap,
1612: per_assignments_f paaf,
1613: pay_payroll_actions ppa,
1614: pay_payroll_actions ppa1,
1615: pay_assignment_actions paa,
1616: per_periods_of_service pps,
3015: ) is
3016:
3017: /* Bug 3873942
3018: Cursor c_employee_details - Added join with table per_grades_tl to fetch and archive the grade name.
3019: Outer Join introduced between per_grades_tl and per_assignments_f based on grade_id.
3020: */
3021:
3022: cursor c_employee_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,
3023: c_assignment_id number,c_end_date date, c_start_date date) /*Bug#3662449 c_start_date parameter added*/
3031: -- papf.payroll_name, /*Bug 4132525, Bug 4688800*/
3032: hsc.segment1 tax_unit_id, /*Bug 4040688*/
3033: hou1.NAME Legal_Employer /*Bug 4132525*/
3034: from per_people_f pap,
3035: per_assignments_f paa,
3036: hr_soft_coding_keyflex hsc, /*Bug 4040688*/
3037: hr_all_organization_units hou, /*Bug 4132525*/
3038: hr_all_organization_units hou1 /*Bug 4132525*/
3039: -- pay_payrolls_f papf /*Bug 4132525, Bug 4688800*/
3045: AND hou1.organization_id = hsc.segment1 /*Bug 4132525*/
3046: -- AND papf.payroll_id = paa.payroll_id /*Bug 4132525, Bug 4688800*/
3047: -- AND c_end_date BETWEEN papf.effective_start_date AND papf.effective_end_date /*Bug 4132525, Bug 4688800*/
3048: and paa.effective_end_date = ( select max(effective_end_date) /*Bug#3662449 sub query added*/
3049: from per_assignments_f
3050: WHERE assignment_id = c_assignment_id
3051: and effective_end_date >= c_start_date
3052: and effective_start_date <= c_end_date)
3053: and c_end_date between pap.effective_start_date and pap.effective_end_date;
3061: FROM per_all_assignments_f paaf,
3062: pay_payrolls_f pay
3063: WHERE paaf.assignment_id = c_assignment_id
3064: and paaf.effective_end_date = (select max(effective_end_date)
3065: From per_assignments_f iipaf
3066: WHERE iipaf.assignment_id = c_assignment_id
3067: and iipaf.effective_end_date >= c_start_date
3068: and iipaf.effective_start_date <= c_end_date
3069: AND iipaf.payroll_id IS NOT NULL)
3086: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
3087: ,max(paa.action_sequence)
3088: from pay_assignment_actions paa
3089: , pay_payroll_actions ppa
3090: , per_assignments_f paf
3091: where paa.assignment_id = paf.assignment_id
3092: and paf.assignment_id = c_assignment_id
3093: and ppa.payroll_action_id = paa.payroll_action_id
3094: and ppa.effective_date between c_start_date and c_end_date
3106: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
3107: ,max(paa.action_sequence)
3108: from pay_assignment_actions paa
3109: , pay_payroll_actions ppa
3110: , per_assignments_f paf
3111: where paa.assignment_id = paf.assignment_id
3112: and paf.assignment_id = c_assignment_id
3113: and ppa.payroll_action_id = paa.payroll_action_id
3114: and ppa.payroll_action_id = c_payroll_action_id