[Home] [Help]
6:
7: /* Bug 4036052
8: Initalize variables for Archive model -- Start */
9:
10: g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
11: g_business_group_id hr_all_organization_units.organization_id%type;
12: g_prev_assignment_id number;
13: g_def_bal_populted varchar2(1);
14:
435: * 4. spawn_archive_reports
436: */
437:
438: procedure range_code
439: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
440: ,p_sql out NOCOPY varchar2
441: ) is
442:
443: l_procedure varchar2(200) ;
454: -- Archive the payroll action level data and EIT defintions.
455: -- sql string to SELECT a range of assignments eligible for archival.
456: p_sql := ' select distinct p.person_id' ||
457: ' from per_people_f p,' ||
458: ' pay_payroll_actions pa' ||
459: ' where pa.payroll_action_id = :payroll_action_id' ||
460: ' and p.business_group_id = pa.business_group_id' ||
461: ' order by p.person_id';
462:
466:
467: end range_code;
468:
469: procedure initialization_code
470: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
471: is
472: l_procedure varchar2(200) ;
473:
474: /*Bug 4132149 - Modification begins here */
477: -- Description : Fetches User Parameters from Legislative_paramters
478: -- column.
479: --------------------------------------------------------------------+
480:
481: CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
482: IS
483: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
484: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
485: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
497: pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
498: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
499: decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
500: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
501: FROM pay_payroll_actions ppa
502: WHERE ppa.payroll_action_id = c_payroll_action_id;
503:
504: --------------------------------------------------------------------+
505: -- Cursor : csr_period_date_earned
505: -- Cursor : csr_period_date_earned
506: -- Description : Fetches Date Earned for a given payroll
507: -- run.
508: --------------------------------------------------------------------+
509: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
510: IS
511: SELECT ppa.date_earned
512: FROM pay_payroll_actions ppa
513: WHERE
508: --------------------------------------------------------------------+
509: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
510: IS
511: SELECT ppa.date_earned
512: FROM pay_payroll_actions ppa
513: WHERE
514: ppa.payroll_action_id = c_payroll_action_id;
515:
516: /*Bug 4132149 - Modification ends here */
609: -- Depending on the parameters passed,the appropriate cursor is used.
610: --------------------------------------------------------------------+
611:
612: procedure assignment_action_code
613: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
614: ,p_start_person in per_all_people_f.person_id%type
615: ,p_end_person in per_all_people_f.person_id%type
616: ,p_chunk in number
617: ) is
622: -- Start Date and End Date is specified
623: --------------------------------------------------------------------+
624:
625: cursor csr_assignment_org_period
626: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
627: ,c_start_person per_all_people_f.person_id%type
628: ,c_end_person per_all_people_f.person_id%type
629: ,c_employee_type per_all_people_f.current_employee_flag%type
630: ,c_business_group_id hr_all_organization_units.organization_id%type
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,
645: per_periods_of_service pps
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,
645: per_periods_of_service pps
646: where ppa.payroll_action_id = c_payroll_action_id
675: -- Start Date and End Date is specified
676: -- Usage : When Range Person is enabled
677: --------------------------------------------------------------------+
678: cursor rg_csr_assignment_org_period
679: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
680: ,c_chunk NUMBER
681: ,c_employee_type per_all_people_f.current_employee_flag%type
682: ,c_business_group_id hr_all_organization_units.organization_id%type
683: ,c_organization_id hr_all_organization_units.organization_id%type
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,
697: per_periods_of_service pps,
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,
697: per_periods_of_service pps,
698: pay_population_ranges ppr
729: -- and Period End Date is specified
730: --------------------------------------------------------------------+
731:
732: cursor csr_assignment_org_run
733: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
734: ,c_start_person per_all_people_f.person_id%type
735: ,c_end_person per_all_people_f.person_id%type
736: ,c_employee_type per_all_people_f.current_employee_flag%type
737: ,c_business_group_id hr_all_organization_units.organization_id%type
736: ,c_employee_type per_all_people_f.current_employee_flag%type
737: ,c_business_group_id hr_all_organization_units.organization_id%type
738: ,c_organization_id hr_all_organization_units.organization_id%type
739: ,c_period_end_date date
740: ,c_pact_id pay_payroll_actions.payroll_action_id%type
741: ) is
742: select paa.assignment_action_id,
743: paa.action_sequence,
744: paaf.assignment_id,
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,
752: per_periods_of_service pps
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,
752: per_periods_of_service pps
753: where ppa.payroll_action_id = c_payroll_action_id
778: -- and Period End Date is specified
779: -- Usage : When Range Person is enabled
780: --------------------------------------------------------------------+
781: cursor rg_csr_assignment_org_run
782: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
783: ,c_chunk NUMBER
784: ,c_employee_type per_all_people_f.current_employee_flag%type
785: ,c_business_group_id hr_all_organization_units.organization_id%type
786: ,c_organization_id hr_all_organization_units.organization_id%type
784: ,c_employee_type per_all_people_f.current_employee_flag%type
785: ,c_business_group_id hr_all_organization_units.organization_id%type
786: ,c_organization_id hr_all_organization_units.organization_id%type
787: ,c_period_end_date date
788: ,c_pact_id pay_payroll_actions.payroll_action_id%type
789: ) is
790: select paa.assignment_action_id,
791: paa.action_sequence,
792: paaf.assignment_id,
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,
800: per_periods_of_service pps,
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,
800: per_periods_of_service pps,
801: pay_population_ranges ppr
831: tax unit of the master wherever there is a master-child
832: relationship*/
833:
834: cursor csr_assignment_legal_period
835: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
836: ,c_start_person per_all_people_f.person_id%type
837: ,c_end_person per_all_people_f.person_id%type
838: ,c_employee_type per_all_people_f.current_employee_flag%type
839: ,c_business_group_id hr_all_organization_units.organization_id%type
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*/
856: per_periods_of_service pps
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*/
856: per_periods_of_service pps
857: where ppa.payroll_action_id = c_payroll_action_id
889: --------------------------------------------------------------------+
890: /* 14218576 - Added LEADING and INDEX Hints to the cursor to improve performance */
891:
892: cursor rg_csr_assignment_legal_period
893: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
894: ,c_chunk NUMBER
895: ,c_employee_type per_all_people_f.current_employee_flag%type
896: ,c_business_group_id hr_all_organization_units.organization_id%type
897: ,c_legal_employer hr_all_organization_units.organization_id%type
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,
914: per_periods_of_service pps,
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,
914: per_periods_of_service pps,
915: pay_population_ranges ppr
951: tax unit of the master wherever there is a master-child
952: relationship*/
953:
954: cursor csr_assignment_legal_run
955: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
956: ,c_start_person per_all_people_f.person_id%type
957: ,c_end_person per_all_people_f.person_id%type
958: ,c_employee_type per_all_people_f.current_employee_flag%type
959: ,c_business_group_id hr_all_organization_units.organization_id%type
958: ,c_employee_type per_all_people_f.current_employee_flag%type
959: ,c_business_group_id hr_all_organization_units.organization_id%type
960: ,c_legal_employer hr_all_organization_units.organization_id%type
961: ,c_period_end_date date
962: ,c_pact_id pay_payroll_actions.payroll_action_id%type
963: ) is
964: select paa.assignment_action_id,
965: paa.action_sequence,
966: paaf.assignment_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*/
976: per_periods_of_service pps
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*/
976: per_periods_of_service pps
977: where ppa.payroll_action_id = c_payroll_action_id
1003: -- and Period End Date is specified
1004: -- Usage : When Range Person is enabled
1005: --------------------------------------------------------------------+
1006: cursor rg_csr_assignment_legal_run
1007: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1008: ,c_chunk NUMBER
1009: ,c_employee_type per_all_people_f.current_employee_flag%type
1010: ,c_business_group_id hr_all_organization_units.organization_id%type
1011: ,c_legal_employer hr_all_organization_units.organization_id%type
1009: ,c_employee_type per_all_people_f.current_employee_flag%type
1010: ,c_business_group_id hr_all_organization_units.organization_id%type
1011: ,c_legal_employer hr_all_organization_units.organization_id%type
1012: ,c_period_end_date date
1013: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1014: ) is
1015: select paa.assignment_action_id,
1016: paa.action_sequence,
1017: paaf.assignment_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,
1027: per_periods_of_service pps,
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,
1027: per_periods_of_service pps,
1028: pay_population_ranges ppr
1056: -- Date and End Date is specified
1057: --------------------------------------------------------------------+
1058:
1059: cursor csr_assignment_payroll_period
1060: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1061: ,c_start_person per_all_people_f.person_id%type
1062: ,c_end_person per_all_people_f.person_id%type
1063: ,c_employee_type per_all_people_f.current_employee_flag%type
1064: ,c_business_group_id hr_all_organization_units.organization_id%type
1061: ,c_start_person per_all_people_f.person_id%type
1062: ,c_end_person per_all_people_f.person_id%type
1063: ,c_employee_type per_all_people_f.current_employee_flag%type
1064: ,c_business_group_id hr_all_organization_units.organization_id%type
1065: ,c_payroll_id pay_payroll_actions.payroll_id%type
1066: ,c_archive_start_date date
1067: ,c_archive_end_date date
1068: ) is
1069: select paa.assignment_action_id,
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
1079: where ppa.payroll_action_id = c_payroll_action_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
1079: where ppa.payroll_action_id = c_payroll_action_id
1080: and paa.assignment_id = paaf.assignment_id
1109: --------------------------------------------------------------------+
1110: /* Bug 9871560 - Added LEADING and INDEX hints to improve the performance of the cursor query. */
1111:
1112: cursor rg_assignment_payroll_period
1113: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1114: , c_chunk NUMBER
1115: ,c_employee_type per_all_people_f.current_employee_flag%type
1116: ,c_business_group_id hr_all_organization_units.organization_id%type
1117: ,c_payroll_id pay_payroll_actions.payroll_id%type
1113: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1114: , c_chunk NUMBER
1115: ,c_employee_type per_all_people_f.current_employee_flag%type
1116: ,c_business_group_id hr_all_organization_units.organization_id%type
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
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,
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
1134: and ppr.payroll_action_id = ppa.payroll_action_id
1135: and ppr.chunk_number = c_chunk
1136: and paa.assignment_id = paaf.assignment_id
1163: -- and Period End Date is specified
1164: --------------------------------------------------------------------+
1165:
1166: cursor csr_assignment_payroll_run
1167: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1168: ,c_start_person per_all_people_f.person_id%type
1169: ,c_end_person per_all_people_f.person_id%type
1170: ,c_employee_type per_all_people_f.current_employee_flag%type
1171: ,c_business_group_id hr_all_organization_units.organization_id%type
1168: ,c_start_person per_all_people_f.person_id%type
1169: ,c_end_person per_all_people_f.person_id%type
1170: ,c_employee_type per_all_people_f.current_employee_flag%type
1171: ,c_business_group_id hr_all_organization_units.organization_id%type
1172: ,c_payroll_id pay_payroll_actions.payroll_id%type
1173: ,c_period_end_date date
1174: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1175: ) is
1176: select paa.assignment_action_id,
1170: ,c_employee_type per_all_people_f.current_employee_flag%type
1171: ,c_business_group_id hr_all_organization_units.organization_id%type
1172: ,c_payroll_id pay_payroll_actions.payroll_id%type
1173: ,c_period_end_date date
1174: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1175: ) is
1176: select paa.assignment_action_id,
1177: paa.action_sequence,
1178: paaf.assignment_id,
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
1186: where ppa.payroll_action_id = c_payroll_action_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
1186: where ppa.payroll_action_id = c_payroll_action_id
1187: and paa.assignment_id = paaf.assignment_id
1209: -- and Period End Date is specified
1210: -- Usage : When Range Person is enabled
1211: --------------------------------------------------------------------+
1212: cursor rg_csr_assignment_payroll_run
1213: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1214: , c_chunk number
1215: ,c_employee_type per_all_people_f.current_employee_flag%type
1216: ,c_business_group_id hr_all_organization_units.organization_id%type
1217: ,c_payroll_id pay_payroll_actions.payroll_id%type
1213: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1214: , c_chunk number
1215: ,c_employee_type per_all_people_f.current_employee_flag%type
1216: ,c_business_group_id hr_all_organization_units.organization_id%type
1217: ,c_payroll_id pay_payroll_actions.payroll_id%type
1218: ,c_period_end_date date
1219: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1220: ) is
1221: select paa.assignment_action_id,
1215: ,c_employee_type per_all_people_f.current_employee_flag%type
1216: ,c_business_group_id hr_all_organization_units.organization_id%type
1217: ,c_payroll_id pay_payroll_actions.payroll_id%type
1218: ,c_period_end_date date
1219: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1220: ) is
1221: select paa.assignment_action_id,
1222: paa.action_sequence,
1223: paaf.assignment_id,
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,
1231: pay_population_ranges ppr
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,
1231: pay_population_ranges ppr
1232: where ppa.payroll_action_id = c_payroll_action_id
1257: -- Date and End Date is specified
1258: --------------------------------------------------------------------+
1259:
1260: cursor csr_assignment_period
1261: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1262: ,c_start_person per_all_people_f.person_id%type
1263: ,c_end_person per_all_people_f.person_id%type
1264: ,c_employee_type per_all_people_f.current_employee_flag%type
1265: ,c_business_group_id hr_all_organization_units.organization_id%type
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
1280: where ppa.payroll_action_id = c_payroll_action_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
1280: where ppa.payroll_action_id = c_payroll_action_id
1281: and paa.assignment_id = paaf.assignment_id
1307: -- Date and End Date is specified
1308: -- Usage : When Range Person is enabled
1309: --------------------------------------------------------------------+
1310: cursor rg_csr_assignment_period
1311: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1312: ,c_chunk number
1313: ,c_employee_type per_all_people_f.current_employee_flag%type
1314: ,c_business_group_id hr_all_organization_units.organization_id%type
1315: ,c_assignment_id per_all_assignments_f.assignment_id%type
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,
1329: pay_population_ranges ppr
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,
1329: pay_population_ranges ppr
1330: where ppa.payroll_action_id = c_payroll_action_id
1359: -- and Period End Date is specified
1360: --------------------------------------------------------------------+
1361:
1362: cursor csr_assignment_run
1363: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1364: ,c_start_person per_all_people_f.person_id%type
1365: ,c_end_person per_all_people_f.person_id%type
1366: ,c_employee_type per_all_people_f.current_employee_flag%type
1367: ,c_business_group_id hr_all_organization_units.organization_id%type
1366: ,c_employee_type per_all_people_f.current_employee_flag%type
1367: ,c_business_group_id hr_all_organization_units.organization_id%type
1368: ,c_assignment_id per_all_assignments_f.assignment_id%type
1369: ,c_period_end_date date
1370: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1371: ) is
1372: select paa.assignment_action_id,
1373: paa.action_sequence,
1374: paaf.assignment_id,
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
1382: where ppa.payroll_action_id = c_payroll_action_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
1382: where ppa.payroll_action_id = c_payroll_action_id
1383: and paa.assignment_id = paaf.assignment_id
1405: -- and Period End Date is specified
1406: -- Usage : When Range Person is enabled
1407: --------------------------------------------------------------------+
1408: cursor rg_csr_assignment_run
1409: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1410: , c_chunk number
1411: ,c_employee_type per_all_people_f.current_employee_flag%type
1412: ,c_business_group_id hr_all_organization_units.organization_id%type
1413: ,c_assignment_id per_all_assignments_f.assignment_id%type
1411: ,c_employee_type per_all_people_f.current_employee_flag%type
1412: ,c_business_group_id hr_all_organization_units.organization_id%type
1413: ,c_assignment_id per_all_assignments_f.assignment_id%type
1414: ,c_period_end_date date
1415: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1416: ) is
1417: select paa.assignment_action_id,
1418: paa.action_sequence,
1419: paaf.assignment_id,
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,
1427: pay_population_ranges ppr
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,
1427: pay_population_ranges ppr
1428: where ppa.payroll_action_id = c_payroll_action_id
1453: -- and End Date is specified
1454: --------------------------------------------------------------------+
1455:
1456: cursor csr_assignment_default_period
1457: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1458: ,c_start_person per_all_people_f.person_id%type
1459: ,c_end_person per_all_people_f.person_id%type
1460: ,c_employee_type per_all_people_f.current_employee_flag%type
1461: ,c_business_group_id hr_all_organization_units.organization_id%type
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
1475: where ppa.payroll_action_id = c_payroll_action_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
1475: where ppa.payroll_action_id = c_payroll_action_id
1476: and paa.assignment_id = paaf.assignment_id
1501: -- and End Date is specified
1502: -- Usage : When Range Person is enabled
1503: --------------------------------------------------------------------+
1504: cursor rg_assignment_default_period
1505: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1506: , c_chunk NUMBER
1507: ,c_employee_type per_all_people_f.current_employee_flag%type
1508: ,c_business_group_id hr_all_organization_units.organization_id%type
1509: ,c_archive_start_date date
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,
1522: pay_population_ranges ppr
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,
1522: pay_population_ranges ppr
1523: where ppa.payroll_action_id = c_payroll_action_id
1551: -- and Period End Date is specified
1552: --------------------------------------------------------------------+
1553:
1554: cursor csr_assignment_default_run
1555: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1556: ,c_start_person per_all_people_f.person_id%type
1557: ,c_end_person per_all_people_f.person_id%type
1558: ,c_employee_type per_all_people_f.current_employee_flag%type
1559: ,c_business_group_id hr_all_organization_units.organization_id%type
1557: ,c_end_person per_all_people_f.person_id%type
1558: ,c_employee_type per_all_people_f.current_employee_flag%type
1559: ,c_business_group_id hr_all_organization_units.organization_id%type
1560: ,c_period_end_date date
1561: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1562: ) is
1563: select paa.assignment_action_id,
1564: paa.action_sequence,
1565: paaf.assignment_id,
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
1573: where ppa.payroll_action_id = c_payroll_action_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
1573: where ppa.payroll_action_id = c_payroll_action_id
1574: and paa.assignment_id = paaf.assignment_id
1596: -- and Period End Date is specified
1597: -- Usage : When Range Person is enabled
1598: --------------------------------------------------------------------+
1599: cursor rg_csr_assignment_default_run
1600: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1601: , c_chunk number
1602: ,c_employee_type per_all_people_f.current_employee_flag%type
1603: ,c_business_group_id hr_all_organization_units.organization_id%type
1604: ,c_period_end_date date
1601: , c_chunk number
1602: ,c_employee_type per_all_people_f.current_employee_flag%type
1603: ,c_business_group_id hr_all_organization_units.organization_id%type
1604: ,c_period_end_date date
1605: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1606: ) is
1607: select paa.assignment_action_id,
1608: paa.action_sequence,
1609: paaf.assignment_id,
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,
1617: pay_population_ranges ppr
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,
1617: pay_population_ranges ppr
1618: where ppa.payroll_action_id = c_payroll_action_id
1640: -- Description : Fetches User Parameters from Legislative_paramters
1641: -- column.
1642: --------------------------------------------------------------------+
1643:
1644: CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1645: IS
1646: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
1647: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
1648: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
1660: pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
1661: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
1662: decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
1663: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
1664: FROM pay_payroll_actions ppa
1665: WHERE ppa.payroll_action_id = c_payroll_action_id;
1666:
1667: --------------------------------------------------------------------+
1668: -- Cursor : csr_period_date_earned
1669: -- Description : Fetches Date Earned for a given payroll
1670: -- run.
1671: --------------------------------------------------------------------+
1672: /*Bug#3662449 *********/
1673: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1674: IS
1675: SELECT ppa.date_earned
1676: FROM pay_payroll_actions ppa
1677: WHERE
1672: /*Bug#3662449 *********/
1673: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1674: IS
1675: SELECT ppa.date_earned
1676: FROM pay_payroll_actions ppa
1677: WHERE
1678: ppa.payroll_action_id = c_payroll_action_id;
1679:
1680:
3010: end assignment_action_code;
3011:
3012: procedure archive_code
3013: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
3014: ,p_effective_date in pay_payroll_actions.effective_date%type
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.
3085: is
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
3105: is
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
3370:
3371: end archive_code;
3372:
3373: procedure spawn_archive_reports
3374: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
3375: is
3376: l_count number;
3377: ps_request_id NUMBER;
3378: l_print_style VARCHAR2(2);
3386: -- Description : Fetches User Parameters from Legislative_paramters
3387: -- column.
3388: --------------------------------------------------------------------+
3389:
3390: CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
3391: IS
3392: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
3393: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
3394: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
3407: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
3408: pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
3409: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions, /*Bug# 4142159*/
3410: pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters) output_type /*Bug# 4947424*/
3411: FROM pay_payroll_actions ppa
3412: WHERE ppa.payroll_action_id = c_payroll_action_id;
3413:
3414:
3415: cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3416: SELECT printer,
3417: print_style,
3418: decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3419: ,number_of_copies /* Bug 4116833*/
3420: FROM pay_payroll_actions pact,
3421: fnd_concurrent_requests fcr
3422: WHERE fcr.request_id = pact.request_id
3423: AND pact.payroll_action_id = p_payroll_action_id;
3424: