[Home] [Help]
86: ***04-Aug-10 pmatamsr 115.54 9871560 Added LEADING and INDEX hints to rg_assignment_payroll_period cursor.
87: *** ------------------------------------------------------------------------+
88: */
89:
90: g_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
91: g_business_group_id hr_all_organization_units.organization_id%type;
92: g_prev_assignment_id number;
93: g_def_bal_populted varchar2(1);
94:
107: --
108: --------------------------------------------------------------------
109:
110: procedure range_code
111: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
112: ,p_sql out NOCOPY varchar2
113: ) is
114:
115: l_procedure varchar2(200) ;
126: -- Archive the payroll action level data and EIT defintions.
127: -- sql string to SELECT a range of assignments eligible for archival.
128: p_sql := ' select distinct p.person_id' ||
129: ' from per_people_f p,' ||
130: ' pay_payroll_actions pa' ||
131: ' where pa.payroll_action_id = :payroll_action_id' ||
132: ' and p.business_group_id = pa.business_group_id' ||
133: ' order by p.person_id';
134:
237: -- This procedure is used to set global contexts
238: --------------------------------------------------------------------+
239:
240: procedure initialization_code
241: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
242: is
243: l_procedure varchar2(200) ;
244:
245: /*Bug 4132149 - Modification begins here*/
248: -- Description : Fetches User Parameters from Legislative_paramters
249: -- column.
250: --------------------------------------------------------------------+
251:
252: CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
253: IS
254: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
255: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
256: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
268: pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
269: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
270: decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
271: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
272: FROM pay_payroll_actions ppa
273: WHERE ppa.payroll_action_id = c_payroll_action_id;
274:
275: --------------------------------------------------------------------+
276: -- Cursor : csr_period_date_earned
276: -- Cursor : csr_period_date_earned
277: -- Description : Fetches Date Earned for a given payroll
278: -- run.
279: --------------------------------------------------------------------+
280: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
281: IS
282: SELECT ppa.date_earned
283: FROM pay_payroll_actions ppa
284: WHERE
279: --------------------------------------------------------------------+
280: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
281: IS
282: SELECT ppa.date_earned
283: FROM pay_payroll_actions ppa
284: WHERE
285: ppa.payroll_action_id = c_payroll_action_id;
286:
287: /*Bug 4132149 - Modification ends here*/
381: -- Depending on the parameters passed,the appropriate cursor is used.
382: --------------------------------------------------------------------+
383:
384: procedure assignment_action_code
385: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
386: ,p_start_person in per_all_people_f.person_id%type
387: ,p_end_person in per_all_people_f.person_id%type
388: ,p_chunk in number
389: ) is
394: -- Start Date and End Date is specified
395: --------------------------------------------------------------------+
396:
397: cursor csr_assignment_org_period
398: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
399: ,c_start_person per_all_people_f.person_id%type
400: ,c_end_person per_all_people_f.person_id%type
401: ,c_employee_type per_all_people_f.current_employee_flag%type
402: ,c_business_group_id hr_all_organization_units.organization_id%type
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,
417: per_periods_of_service pps
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,
417: per_periods_of_service pps
418: -- ,per_assignment_status_types past
447: -- Start Date and End Date is specified
448: -- Usage : When Range Person is enabled
449: --------------------------------------------------------------------+
450: cursor rg_csr_assignment_org_period
451: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
452: ,c_chunk number
453: ,c_employee_type per_all_people_f.current_employee_flag%type
454: ,c_business_group_id hr_all_organization_units.organization_id%type
455: ,c_organization_id hr_all_organization_units.organization_id%type
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,
469: per_periods_of_service pps,
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,
469: per_periods_of_service pps,
470: pay_population_ranges ppr
501: -- and Period End Date is specified
502: --------------------------------------------------------------------+
503:
504: cursor csr_assignment_org_run
505: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
506: ,c_start_person per_all_people_f.person_id%type
507: ,c_end_person per_all_people_f.person_id%type
508: ,c_employee_type per_all_people_f.current_employee_flag%type
509: ,c_business_group_id hr_all_organization_units.organization_id%type
508: ,c_employee_type per_all_people_f.current_employee_flag%type
509: ,c_business_group_id hr_all_organization_units.organization_id%type
510: ,c_organization_id hr_all_organization_units.organization_id%type
511: ,c_period_end_date date
512: ,c_pact_id pay_payroll_actions.payroll_action_id%type
513: ) is
514: select paa.assignment_action_id,
515: paa.action_sequence,
516: paaf.assignment_id,
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,
524: per_periods_of_service pps
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,
524: per_periods_of_service pps
525: where ppa.payroll_action_id = c_payroll_action_id
549: -- and Period End Date is specified
550: -- Usage : When Range Person is enabled
551: --------------------------------------------------------------------+
552: cursor rg_csr_assignment_org_run
553: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
554: ,c_chunk number
555: ,c_employee_type per_all_people_f.current_employee_flag%type
556: ,c_business_group_id hr_all_organization_units.organization_id%type
557: ,c_organization_id hr_all_organization_units.organization_id%type
555: ,c_employee_type per_all_people_f.current_employee_flag%type
556: ,c_business_group_id hr_all_organization_units.organization_id%type
557: ,c_organization_id hr_all_organization_units.organization_id%type
558: ,c_period_end_date date
559: ,c_pact_id pay_payroll_actions.payroll_action_id%type
560: ) is
561: select paa.assignment_action_id,
562: paa.action_sequence,
563: paaf.assignment_id,
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,
571: per_periods_of_service pps,
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,
571: per_periods_of_service pps,
572: pay_population_ranges ppr
598: -- Start Date and End Date is specified
599: --------------------------------------------------------------------+
600:
601: cursor csr_assignment_legal_period
602: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
603: ,c_start_person per_all_people_f.person_id%type
604: ,c_end_person per_all_people_f.person_id%type
605: ,c_employee_type per_all_people_f.current_employee_flag%type
606: ,c_business_group_id hr_all_organization_units.organization_id%type
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
621: -- ,per_assignment_status_types past
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
621: -- ,per_assignment_status_types past
622: where ppa.payroll_action_id = c_payroll_action_id
649: -- Start Date and End Date is specified
650: -- Usage : When Range Person is enabled
651: --------------------------------------------------------------------+
652: cursor rg_csr_assignment_legal_period
653: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
654: ,c_chunk number
655: ,c_employee_type per_all_people_f.current_employee_flag%type
656: ,c_business_group_id hr_all_organization_units.organization_id%type
657: ,c_legal_employer hr_all_organization_units.organization_id%type
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,
671: pay_population_ranges ppr
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,
671: pay_population_ranges ppr
672: where ppa.payroll_action_id = c_payroll_action_id
700: --------------------------------------------------------------------+
701:
702:
703: cursor csr_assignment_legal_run
704: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
705: ,c_start_person per_all_people_f.person_id%type
706: ,c_end_person per_all_people_f.person_id%type
707: ,c_employee_type per_all_people_f.current_employee_flag%type
708: ,c_business_group_id hr_all_organization_units.organization_id%type
707: ,c_employee_type per_all_people_f.current_employee_flag%type
708: ,c_business_group_id hr_all_organization_units.organization_id%type
709: ,c_legal_employer hr_all_organization_units.organization_id%type
710: ,c_period_end_date date
711: ,c_pact_id pay_payroll_actions.payroll_action_id%type
712: ) is
713: select paa.assignment_action_id,
714: paa.action_sequence,
715: paaf.assignment_id,
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
723: where ppa.payroll_action_id = c_payroll_action_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
723: where ppa.payroll_action_id = c_payroll_action_id
724: and paa.assignment_id = paaf.assignment_id
746: -- Usage : When Range Person is enabled
747: --------------------------------------------------------------------+
748:
749: cursor rg_csr_assignment_legal_run
750: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
751: , c_chunk number
752: ,c_employee_type per_all_people_f.current_employee_flag%type
753: ,c_business_group_id hr_all_organization_units.organization_id%type
754: ,c_legal_employer hr_all_organization_units.organization_id%type
752: ,c_employee_type per_all_people_f.current_employee_flag%type
753: ,c_business_group_id hr_all_organization_units.organization_id%type
754: ,c_legal_employer hr_all_organization_units.organization_id%type
755: ,c_period_end_date date
756: ,c_pact_id pay_payroll_actions.payroll_action_id%type
757: ) is
758: select paa.assignment_action_id,
759: paa.action_sequence,
760: paaf.assignment_id,
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,
768: pay_population_ranges ppr
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,
768: pay_population_ranges ppr
769: where ppa.payroll_action_id = c_payroll_action_id
792: -- Date and End Date is specified
793: --------------------------------------------------------------------+
794:
795: cursor csr_assignment_payroll_period
796: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
797: ,c_start_person per_all_people_f.person_id%type
798: ,c_end_person per_all_people_f.person_id%type
799: ,c_employee_type per_all_people_f.current_employee_flag%type
800: ,c_business_group_id hr_all_organization_units.organization_id%type
797: ,c_start_person per_all_people_f.person_id%type
798: ,c_end_person per_all_people_f.person_id%type
799: ,c_employee_type per_all_people_f.current_employee_flag%type
800: ,c_business_group_id hr_all_organization_units.organization_id%type
801: ,c_payroll_id pay_payroll_actions.payroll_id%type
802: ,c_archive_start_date date
803: ,c_archive_end_date date
804: ) is
805: select paa.assignment_action_id,
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
815: -- per_assignment_status_types past
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
815: -- per_assignment_status_types past
816: where ppa.payroll_action_id = c_payroll_action_id
845: --------------------------------------------------------------------+
846: /* Bug 9871560 - Added LEADING and INDEX hints to improve the performance of the cursor query. */
847:
848: cursor rg_assignment_payroll_period
849: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
850: ,c_chunk NUMBER
851: ,c_employee_type per_all_people_f.current_employee_flag%type
852: ,c_business_group_id hr_all_organization_units.organization_id%type
853: ,c_payroll_id pay_payroll_actions.payroll_id%type
849: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
850: ,c_chunk NUMBER
851: ,c_employee_type per_all_people_f.current_employee_flag%type
852: ,c_business_group_id hr_all_organization_units.organization_id%type
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
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,
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
870: and ppr.payroll_action_id = ppa.payroll_action_id
871: and ppr.chunk_number = c_chunk
872: and paa.assignment_id = paaf.assignment_id
897: -- and Period End Date is specified
898: --------------------------------------------------------------------+
899:
900: cursor csr_assignment_payroll_run
901: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
902: ,c_start_person per_all_people_f.person_id%type
903: ,c_end_person per_all_people_f.person_id%type
904: ,c_employee_type per_all_people_f.current_employee_flag%type
905: ,c_business_group_id hr_all_organization_units.organization_id%type
902: ,c_start_person per_all_people_f.person_id%type
903: ,c_end_person per_all_people_f.person_id%type
904: ,c_employee_type per_all_people_f.current_employee_flag%type
905: ,c_business_group_id hr_all_organization_units.organization_id%type
906: ,c_payroll_id pay_payroll_actions.payroll_id%type
907: ,c_period_end_date date
908: ,c_pact_id pay_payroll_actions.payroll_action_id%type
909: ) is
910: select paa.assignment_action_id,
904: ,c_employee_type per_all_people_f.current_employee_flag%type
905: ,c_business_group_id hr_all_organization_units.organization_id%type
906: ,c_payroll_id pay_payroll_actions.payroll_id%type
907: ,c_period_end_date date
908: ,c_pact_id pay_payroll_actions.payroll_action_id%type
909: ) is
910: select paa.assignment_action_id,
911: paa.action_sequence,
912: paaf.assignment_id,
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
920: where ppa.payroll_action_id = c_payroll_action_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
920: where ppa.payroll_action_id = c_payroll_action_id
921: and paa.assignment_id = paaf.assignment_id
944: -- Usage : When Range Person is enabled
945: --------------------------------------------------------------------+
946:
947: cursor rg_csr_assignment_payroll_run
948: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
949: , c_chunk NUMBER
950: ,c_employee_type per_all_people_f.current_employee_flag%type
951: ,c_business_group_id hr_all_organization_units.organization_id%type
952: ,c_payroll_id pay_payroll_actions.payroll_id%type
948: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
949: , c_chunk NUMBER
950: ,c_employee_type per_all_people_f.current_employee_flag%type
951: ,c_business_group_id hr_all_organization_units.organization_id%type
952: ,c_payroll_id pay_payroll_actions.payroll_id%type
953: ,c_period_end_date date
954: ,c_pact_id pay_payroll_actions.payroll_action_id%type
955: ) is
956: select paa.assignment_action_id,
950: ,c_employee_type per_all_people_f.current_employee_flag%type
951: ,c_business_group_id hr_all_organization_units.organization_id%type
952: ,c_payroll_id pay_payroll_actions.payroll_id%type
953: ,c_period_end_date date
954: ,c_pact_id pay_payroll_actions.payroll_action_id%type
955: ) is
956: select paa.assignment_action_id,
957: paa.action_sequence,
958: paaf.assignment_id,
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,
966: pay_population_ranges ppr
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,
966: pay_population_ranges ppr
967: where ppa.payroll_action_id = c_payroll_action_id
991: -- Date and End Date is specified
992: --------------------------------------------------------------------+
993:
994: cursor csr_assignment_period
995: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
996: ,c_start_person per_all_people_f.person_id%type
997: ,c_end_person per_all_people_f.person_id%type
998: ,c_employee_type per_all_people_f.current_employee_flag%type
999: ,c_business_group_id hr_all_organization_units.organization_id%type
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
1014: -- ,per_assignment_status_types past
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
1014: -- ,per_assignment_status_types past
1015: where ppa.payroll_action_id = c_payroll_action_id
1041: -- Date and End Date is specified
1042: -- Usage : When Range Person is enabled
1043: --------------------------------------------------------------------+
1044: cursor rg_csr_assignment_period
1045: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1046: ,c_chunk NUMBER
1047: ,c_employee_type per_all_people_f.current_employee_flag%type
1048: ,c_business_group_id hr_all_organization_units.organization_id%type
1049: ,c_assignment_id per_all_assignments_f.assignment_id%type
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,
1063: pay_population_ranges ppr
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,
1063: pay_population_ranges ppr
1064: where ppa.payroll_action_id = c_payroll_action_id
1091: -- and Period End Date is specified
1092: --------------------------------------------------------------------+
1093:
1094: cursor csr_assignment_run
1095: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1096: ,c_start_person per_all_people_f.person_id%type
1097: ,c_end_person per_all_people_f.person_id%type
1098: ,c_employee_type per_all_people_f.current_employee_flag%type
1099: ,c_business_group_id hr_all_organization_units.organization_id%type
1098: ,c_employee_type per_all_people_f.current_employee_flag%type
1099: ,c_business_group_id hr_all_organization_units.organization_id%type
1100: ,c_assignment_id per_all_assignments_f.assignment_id%type
1101: ,c_period_end_date date
1102: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1103: ) is
1104: select paa.assignment_action_id,
1105: paa.action_sequence,
1106: paaf.assignment_id,
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
1114: where ppa.payroll_action_id = c_payroll_action_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
1114: where ppa.payroll_action_id = c_payroll_action_id
1115: and paa.assignment_id = paaf.assignment_id
1136: -- and Period End Date is specified
1137: -- Usage : When Range Person is enabled
1138: --------------------------------------------------------------------+
1139: cursor rg_csr_assignment_run
1140: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1141: , c_chunk NUMBER
1142: ,c_employee_type per_all_people_f.current_employee_flag%type
1143: ,c_business_group_id hr_all_organization_units.organization_id%type
1144: ,c_assignment_id per_all_assignments_f.assignment_id%type
1142: ,c_employee_type per_all_people_f.current_employee_flag%type
1143: ,c_business_group_id hr_all_organization_units.organization_id%type
1144: ,c_assignment_id per_all_assignments_f.assignment_id%type
1145: ,c_period_end_date date
1146: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1147: ) is
1148: select paa.assignment_action_id,
1149: paa.action_sequence,
1150: paaf.assignment_id,
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,
1158: pay_population_ranges ppr
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,
1158: pay_population_ranges ppr
1159: where ppa.payroll_action_id = c_payroll_action_id
1182: -- and End Date is specified
1183: --------------------------------------------------------------------+
1184:
1185: cursor csr_assignment_default_period
1186: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1187: ,c_start_person per_all_people_f.person_id%type
1188: ,c_end_person per_all_people_f.person_id%type
1189: ,c_employee_type per_all_people_f.current_employee_flag%type
1190: ,c_business_group_id hr_all_organization_units.organization_id%type
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
1204: -- ,per_assignment_status_types past
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
1204: -- ,per_assignment_status_types past
1205: where ppa.payroll_action_id = c_payroll_action_id
1230: -- and End Date is specified
1231: -- Usage : When Range Person is enabled
1232: --------------------------------------------------------------------+
1233: cursor rg_assignment_default_period
1234: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1235: ,c_chunk NUMBER
1236: ,c_employee_type per_all_people_f.current_employee_flag%type
1237: ,c_business_group_id hr_all_organization_units.organization_id%type
1238: ,c_archive_start_date date
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,
1251: pay_population_ranges ppr
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,
1251: pay_population_ranges ppr
1252: where ppa.payroll_action_id = c_payroll_action_id
1278: -- and Period End Date is specified
1279: --------------------------------------------------------------------+
1280:
1281: cursor csr_assignment_default_run
1282: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1283: ,c_start_person per_all_people_f.person_id%type
1284: ,c_end_person per_all_people_f.person_id%type
1285: ,c_employee_type per_all_people_f.current_employee_flag%type
1286: ,c_business_group_id hr_all_organization_units.organization_id%type
1284: ,c_end_person per_all_people_f.person_id%type
1285: ,c_employee_type per_all_people_f.current_employee_flag%type
1286: ,c_business_group_id hr_all_organization_units.organization_id%type
1287: ,c_period_end_date date
1288: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1289: ) is
1290: select paa.assignment_action_id,
1291: paa.action_sequence,
1292: paaf.assignment_id,
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
1300: where ppa.payroll_action_id = c_payroll_action_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
1300: where ppa.payroll_action_id = c_payroll_action_id
1301: and paa.assignment_id = paaf.assignment_id
1322: -- and Period End Date is specified
1323: -- Usage : When Range Person is enabled
1324: --------------------------------------------------------------------+
1325: cursor rg_csr_assignment_default_run
1326: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
1327: , c_chunk NUMBER
1328: ,c_employee_type per_all_people_f.current_employee_flag%type
1329: ,c_business_group_id hr_all_organization_units.organization_id%type
1330: ,c_period_end_date date
1327: , c_chunk NUMBER
1328: ,c_employee_type per_all_people_f.current_employee_flag%type
1329: ,c_business_group_id hr_all_organization_units.organization_id%type
1330: ,c_period_end_date date
1331: ,c_pact_id pay_payroll_actions.payroll_action_id%type
1332: ) is
1333: select paa.assignment_action_id,
1334: paa.action_sequence,
1335: paaf.assignment_id,
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,
1343: pay_population_ranges ppr
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,
1343: pay_population_ranges ppr
1344: where ppa.payroll_action_id = c_payroll_action_id
1365: -- Description : Fetches User Parameters from Legislative_paramters
1366: -- column.
1367: --------------------------------------------------------------------+
1368:
1369: CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1370: IS
1371: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
1372: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
1373: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
1385: pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
1386: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
1387: decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
1388: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
1389: FROM pay_payroll_actions ppa
1390: WHERE ppa.payroll_action_id = c_payroll_action_id;
1391:
1392: --------------------------------------------------------------------+
1393: -- Cursor : csr_period_date_earned
1394: -- Description : Fetches Date Earned for a given payroll
1395: -- run.
1396: --------------------------------------------------------------------+
1397: /*Bug#3662449 *********/
1398: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1399: IS
1400: SELECT ppa.date_earned
1401: FROM pay_payroll_actions ppa
1402: WHERE
1397: /*Bug#3662449 *********/
1398: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1399: IS
1400: SELECT ppa.date_earned
1401: FROM pay_payroll_actions ppa
1402: WHERE
1403: ppa.payroll_action_id = c_payroll_action_id;
1404:
1405:
2741: --------------------------------------------------------------------+
2742:
2743: procedure archive_code
2744: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2745: ,p_effective_date in pay_payroll_actions.effective_date%type
2746: ) is
2747:
2748:
2749:
2881: ,pay_input_values_f piv
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
2931: is
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
2951: is
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
3361:
3362:
3363:
3364: procedure spawn_archive_reports
3365: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
3366: is
3367: l_count number;
3368: ps_request_id NUMBER;
3369: l_print_style VARCHAR2(2);
3378: -- Description : Fetches User Parameters from Legislative_paramters
3379: -- column.
3380: --------------------------------------------------------------------+
3381:
3382: CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
3383: IS
3384: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
3385: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
3386: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
3399: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
3400: pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
3401: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions, /*Bug# 4142159*/
3402: pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters)p_output_type /* Bug# 6839263 */
3403: FROM pay_payroll_actions ppa
3404: WHERE ppa.payroll_action_id = c_payroll_action_id;
3405:
3406:
3407: cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
3408: SELECT printer,
3409: print_style,
3410: decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
3411: ,number_of_copies /* Bug 4116833 */
3412: FROM pay_payroll_actions pact,
3413: fnd_concurrent_requests fcr
3414: WHERE fcr.request_id = pact.request_id
3415: AND pact.payroll_action_id = p_payroll_action_id;
3416:
3572: (
3573: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
3574: p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
3575: p_run_result_id IN pay_run_results.run_result_ID%TYPE,
3576: p_effective_date IN pay_payroll_actions.effective_date%TYPE
3577: )
3578: RETURN NUMBER
3579: IS
3580:
3654: (
3655: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
3656: p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
3657: p_run_result_id IN pay_run_results.run_result_ID%TYPE,
3658: p_effective_date IN pay_payroll_actions.effective_date%TYPE
3659: )
3660: RETURN NUMBER
3661: IS
3662: