[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:
321: * 4. spawn_archive_reports
322: */
323:
324: procedure range_code
325: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
326: ,p_sql out NOCOPY varchar2
327: ) is
328:
329: l_procedure varchar2(200) ;
340: -- Archive the payroll action level data and EIT defintions.
341: -- sql string to SELECT a range of assignments eligible for archival.
342: p_sql := ' select distinct p.person_id' ||
343: ' from per_people_f p,' ||
344: ' pay_payroll_actions pa' ||
345: ' where pa.payroll_action_id = :payroll_action_id' ||
346: ' and p.business_group_id = pa.business_group_id' ||
347: ' order by p.person_id';
348:
352:
353: end range_code;
354:
355: procedure initialization_code
356: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
357: is
358: l_procedure varchar2(200) ;
359:
360: /*Bug 4132149 - Modification begins here */
363: -- Description : Fetches User Parameters from Legislative_paramters
364: -- column.
365: --------------------------------------------------------------------+
366:
367: CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
368: IS
369: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
370: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
371: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
383: pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
384: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
385: decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
386: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
387: FROM pay_payroll_actions ppa
388: WHERE ppa.payroll_action_id = c_payroll_action_id;
389:
390: --------------------------------------------------------------------+
391: -- Cursor : csr_period_date_earned
391: -- Cursor : csr_period_date_earned
392: -- Description : Fetches Date Earned for a given payroll
393: -- run.
394: --------------------------------------------------------------------+
395: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
396: IS
397: SELECT ppa.date_earned
398: FROM pay_payroll_actions ppa
399: WHERE
394: --------------------------------------------------------------------+
395: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
396: IS
397: SELECT ppa.date_earned
398: FROM pay_payroll_actions ppa
399: WHERE
400: ppa.payroll_action_id = c_payroll_action_id;
401:
402: /*Bug 4132149 - Modification ends here */
495: -- Depending on the parameters passed,the appropriate cursor is used.
496: --------------------------------------------------------------------+
497:
498: procedure assignment_action_code
499: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type
500: ,p_start_person in per_all_people_f.person_id%type
501: ,p_end_person in per_all_people_f.person_id%type
502: ,p_chunk in number
503: ) is
508: -- Start Date and End Date is specified
509: --------------------------------------------------------------------+
510:
511: cursor csr_assignment_org_period
512: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
513: ,c_start_person per_all_people_f.person_id%type
514: ,c_end_person per_all_people_f.person_id%type
515: ,c_employee_type per_all_people_f.current_employee_flag%type
516: ,c_business_group_id hr_all_organization_units.organization_id%type
523: paaf.assignment_id,
524: paa.tax_unit_id
525: from per_people_f pap,
526: per_assignments_f paaf,
527: pay_payroll_actions ppa,
528: pay_payroll_actions ppa1,
529: pay_assignment_actions paa,
530: hr_organization_units hou,
531: per_periods_of_service pps
524: paa.tax_unit_id
525: from per_people_f pap,
526: per_assignments_f paaf,
527: pay_payroll_actions ppa,
528: pay_payroll_actions ppa1,
529: pay_assignment_actions paa,
530: hr_organization_units hou,
531: per_periods_of_service pps
532: where ppa.payroll_action_id = c_payroll_action_id
561: -- and Period End Date is specified
562: --------------------------------------------------------------------+
563:
564: cursor csr_assignment_org_run
565: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
566: ,c_start_person per_all_people_f.person_id%type
567: ,c_end_person per_all_people_f.person_id%type
568: ,c_employee_type per_all_people_f.current_employee_flag%type
569: ,c_business_group_id hr_all_organization_units.organization_id%type
568: ,c_employee_type per_all_people_f.current_employee_flag%type
569: ,c_business_group_id hr_all_organization_units.organization_id%type
570: ,c_organization_id hr_all_organization_units.organization_id%type
571: ,c_period_end_date date
572: ,c_pact_id pay_payroll_actions.payroll_action_id%type
573: ) is
574: select paa.assignment_action_id,
575: paa.action_sequence,
576: paaf.assignment_id,
576: paaf.assignment_id,
577: paa.tax_unit_id
578: from per_people_f pap,
579: per_assignments_f paaf,
580: pay_payroll_actions ppa,
581: pay_payroll_actions ppa1,
582: pay_assignment_actions paa,
583: hr_organization_units hou,
584: per_periods_of_service pps
577: paa.tax_unit_id
578: from per_people_f pap,
579: per_assignments_f paaf,
580: pay_payroll_actions ppa,
581: pay_payroll_actions ppa1,
582: pay_assignment_actions paa,
583: hr_organization_units hou,
584: per_periods_of_service pps
585: where ppa.payroll_action_id = c_payroll_action_id
612: tax unit of the master wherever there is a master-child
613: relationship*/
614:
615: cursor csr_assignment_legal_period
616: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
617: ,c_start_person per_all_people_f.person_id%type
618: ,c_end_person per_all_people_f.person_id%type
619: ,c_employee_type per_all_people_f.current_employee_flag%type
620: ,c_business_group_id hr_all_organization_units.organization_id%type
629: paa.source_action_id master_action_id, /*Bug# 3935471*/
630: paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
631: from per_people_f pap,
632: per_assignments_f paaf,
633: pay_payroll_actions ppa,
634: pay_payroll_actions ppa1,
635: pay_assignment_actions paa,
636: pay_assignment_actions paa2, /*Bug# 3935471*/
637: per_periods_of_service pps
630: paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
631: from per_people_f pap,
632: per_assignments_f paaf,
633: pay_payroll_actions ppa,
634: pay_payroll_actions ppa1,
635: pay_assignment_actions paa,
636: pay_assignment_actions paa2, /*Bug# 3935471*/
637: per_periods_of_service pps
638: where ppa.payroll_action_id = c_payroll_action_id
671: tax unit of the master wherever there is a master-child
672: relationship*/
673:
674: cursor csr_assignment_legal_run
675: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
676: ,c_start_person per_all_people_f.person_id%type
677: ,c_end_person per_all_people_f.person_id%type
678: ,c_employee_type per_all_people_f.current_employee_flag%type
679: ,c_business_group_id hr_all_organization_units.organization_id%type
678: ,c_employee_type per_all_people_f.current_employee_flag%type
679: ,c_business_group_id hr_all_organization_units.organization_id%type
680: ,c_legal_employer hr_all_organization_units.organization_id%type
681: ,c_period_end_date date
682: ,c_pact_id pay_payroll_actions.payroll_action_id%type
683: ) is
684: select paa.assignment_action_id,
685: paa.action_sequence,
686: paaf.assignment_id,
688: paa.source_action_id master_action_id, /*Bug# 3935471*/
689: paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
690: from per_people_f pap,
691: per_assignments_f paaf,
692: pay_payroll_actions ppa,
693: pay_payroll_actions ppa1,
694: pay_assignment_actions paa,
695: pay_assignment_actions paa2, /*Bug# 3935471*/
696: per_periods_of_service pps
689: paa2.tax_unit_id master_tax_unit_id /*Bug# 3935471*/
690: from per_people_f pap,
691: per_assignments_f paaf,
692: pay_payroll_actions ppa,
693: pay_payroll_actions ppa1,
694: pay_assignment_actions paa,
695: pay_assignment_actions paa2, /*Bug# 3935471*/
696: per_periods_of_service pps
697: where ppa.payroll_action_id = c_payroll_action_id
722: -- Date and End Date is specified
723: --------------------------------------------------------------------+
724:
725: cursor csr_assignment_payroll_period
726: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
727: ,c_start_person per_all_people_f.person_id%type
728: ,c_end_person per_all_people_f.person_id%type
729: ,c_employee_type per_all_people_f.current_employee_flag%type
730: ,c_business_group_id hr_all_organization_units.organization_id%type
727: ,c_start_person per_all_people_f.person_id%type
728: ,c_end_person per_all_people_f.person_id%type
729: ,c_employee_type per_all_people_f.current_employee_flag%type
730: ,c_business_group_id hr_all_organization_units.organization_id%type
731: ,c_payroll_id pay_payroll_actions.payroll_id%type
732: ,c_archive_start_date date
733: ,c_archive_end_date date
734: ) is
735: select paa.assignment_action_id,
737: paaf.assignment_id,
738: paa.tax_unit_id
739: from per_people_f pap,
740: per_assignments_f paaf,
741: pay_payroll_actions ppa,
742: pay_payroll_actions ppa1,
743: pay_assignment_actions paa,
744: per_periods_of_service pps
745: where ppa.payroll_action_id = c_payroll_action_id
738: paa.tax_unit_id
739: from per_people_f pap,
740: per_assignments_f paaf,
741: pay_payroll_actions ppa,
742: pay_payroll_actions ppa1,
743: pay_assignment_actions paa,
744: per_periods_of_service pps
745: where ppa.payroll_action_id = c_payroll_action_id
746: and paa.assignment_id = paaf.assignment_id
772: -- and Period End Date is specified
773: --------------------------------------------------------------------+
774:
775: cursor csr_assignment_payroll_run
776: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
777: ,c_start_person per_all_people_f.person_id%type
778: ,c_end_person per_all_people_f.person_id%type
779: ,c_employee_type per_all_people_f.current_employee_flag%type
780: ,c_business_group_id hr_all_organization_units.organization_id%type
777: ,c_start_person per_all_people_f.person_id%type
778: ,c_end_person per_all_people_f.person_id%type
779: ,c_employee_type per_all_people_f.current_employee_flag%type
780: ,c_business_group_id hr_all_organization_units.organization_id%type
781: ,c_payroll_id pay_payroll_actions.payroll_id%type
782: ,c_period_end_date date
783: ,c_pact_id pay_payroll_actions.payroll_action_id%type
784: ) is
785: select paa.assignment_action_id,
779: ,c_employee_type per_all_people_f.current_employee_flag%type
780: ,c_business_group_id hr_all_organization_units.organization_id%type
781: ,c_payroll_id pay_payroll_actions.payroll_id%type
782: ,c_period_end_date date
783: ,c_pact_id pay_payroll_actions.payroll_action_id%type
784: ) is
785: select paa.assignment_action_id,
786: paa.action_sequence,
787: paaf.assignment_id,
787: paaf.assignment_id,
788: paa.tax_unit_id
789: from per_people_f pap,
790: per_assignments_f paaf,
791: pay_payroll_actions ppa,
792: pay_payroll_actions ppa1,
793: pay_assignment_actions paa,
794: per_periods_of_service pps
795: where ppa.payroll_action_id = c_payroll_action_id
788: paa.tax_unit_id
789: from per_people_f pap,
790: per_assignments_f paaf,
791: pay_payroll_actions ppa,
792: pay_payroll_actions ppa1,
793: pay_assignment_actions paa,
794: per_periods_of_service pps
795: where ppa.payroll_action_id = c_payroll_action_id
796: and paa.assignment_id = paaf.assignment_id
818: -- Date and End Date is specified
819: --------------------------------------------------------------------+
820:
821: cursor csr_assignment_period
822: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
823: ,c_start_person per_all_people_f.person_id%type
824: ,c_end_person per_all_people_f.person_id%type
825: ,c_employee_type per_all_people_f.current_employee_flag%type
826: ,c_business_group_id hr_all_organization_units.organization_id%type
833: paaf.assignment_id,
834: paa.tax_unit_id
835: from per_people_f pap,
836: per_assignments_f paaf,
837: pay_payroll_actions ppa,
838: pay_payroll_actions ppa1,
839: pay_assignment_actions paa,
840: per_periods_of_service pps
841: where ppa.payroll_action_id = c_payroll_action_id
834: paa.tax_unit_id
835: from per_people_f pap,
836: per_assignments_f paaf,
837: pay_payroll_actions ppa,
838: pay_payroll_actions ppa1,
839: pay_assignment_actions paa,
840: per_periods_of_service pps
841: where ppa.payroll_action_id = c_payroll_action_id
842: and paa.assignment_id = paaf.assignment_id
867: -- and Period End Date is specified
868: --------------------------------------------------------------------+
869:
870: cursor csr_assignment_run
871: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
872: ,c_start_person per_all_people_f.person_id%type
873: ,c_end_person per_all_people_f.person_id%type
874: ,c_employee_type per_all_people_f.current_employee_flag%type
875: ,c_business_group_id hr_all_organization_units.organization_id%type
874: ,c_employee_type per_all_people_f.current_employee_flag%type
875: ,c_business_group_id hr_all_organization_units.organization_id%type
876: ,c_assignment_id per_all_assignments_f.assignment_id%type
877: ,c_period_end_date date
878: ,c_pact_id pay_payroll_actions.payroll_action_id%type
879: ) is
880: select paa.assignment_action_id,
881: paa.action_sequence,
882: paaf.assignment_id,
882: paaf.assignment_id,
883: paa.tax_unit_id
884: from per_people_f pap,
885: per_assignments_f paaf,
886: pay_payroll_actions ppa,
887: pay_payroll_actions ppa1,
888: pay_assignment_actions paa,
889: per_periods_of_service pps
890: where ppa.payroll_action_id = c_payroll_action_id
883: paa.tax_unit_id
884: from per_people_f pap,
885: per_assignments_f paaf,
886: pay_payroll_actions ppa,
887: pay_payroll_actions ppa1,
888: pay_assignment_actions paa,
889: per_periods_of_service pps
890: where ppa.payroll_action_id = c_payroll_action_id
891: and paa.assignment_id = paaf.assignment_id
912: -- and End Date is specified
913: --------------------------------------------------------------------+
914:
915: cursor csr_assignment_default_period
916: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
917: ,c_start_person per_all_people_f.person_id%type
918: ,c_end_person per_all_people_f.person_id%type
919: ,c_employee_type per_all_people_f.current_employee_flag%type
920: ,c_business_group_id hr_all_organization_units.organization_id%type
926: paaf.assignment_id,
927: paa.tax_unit_id
928: from per_people_f pap,
929: per_assignments_f paaf,
930: pay_payroll_actions ppa,
931: pay_payroll_actions ppa1,
932: pay_assignment_actions paa,
933: per_periods_of_service pps
934: where ppa.payroll_action_id = c_payroll_action_id
927: paa.tax_unit_id
928: from per_people_f pap,
929: per_assignments_f paaf,
930: pay_payroll_actions ppa,
931: pay_payroll_actions ppa1,
932: pay_assignment_actions paa,
933: per_periods_of_service pps
934: where ppa.payroll_action_id = c_payroll_action_id
935: and paa.assignment_id = paaf.assignment_id
959: -- and Period End Date is specified
960: --------------------------------------------------------------------+
961:
962: cursor csr_assignment_default_run
963: (c_payroll_action_id pay_payroll_actions.payroll_action_id%type
964: ,c_start_person per_all_people_f.person_id%type
965: ,c_end_person per_all_people_f.person_id%type
966: ,c_employee_type per_all_people_f.current_employee_flag%type
967: ,c_business_group_id hr_all_organization_units.organization_id%type
965: ,c_end_person per_all_people_f.person_id%type
966: ,c_employee_type per_all_people_f.current_employee_flag%type
967: ,c_business_group_id hr_all_organization_units.organization_id%type
968: ,c_period_end_date date
969: ,c_pact_id pay_payroll_actions.payroll_action_id%type
970: ) is
971: select paa.assignment_action_id,
972: paa.action_sequence,
973: paaf.assignment_id,
973: paaf.assignment_id,
974: paa.tax_unit_id
975: from per_people_f pap,
976: per_assignments_f paaf,
977: pay_payroll_actions ppa,
978: pay_payroll_actions ppa1,
979: pay_assignment_actions paa,
980: per_periods_of_service pps
981: where ppa.payroll_action_id = c_payroll_action_id
974: paa.tax_unit_id
975: from per_people_f pap,
976: per_assignments_f paaf,
977: pay_payroll_actions ppa,
978: pay_payroll_actions ppa1,
979: pay_assignment_actions paa,
980: per_periods_of_service pps
981: where ppa.payroll_action_id = c_payroll_action_id
982: and paa.assignment_id = paaf.assignment_id
1002: -- Description : Fetches User Parameters from Legislative_paramters
1003: -- column.
1004: --------------------------------------------------------------------+
1005:
1006: CURSOR csr_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1007: IS
1008: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
1009: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
1010: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
1022: pay_core_utils.get_parameter('ZERO_REC',legislative_parameters) zero_records,
1023: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
1024: decode(pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters),'C','Y','T','N','%') employee_type,
1025: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions /*Bug# 4142159*/
1026: FROM pay_payroll_actions ppa
1027: WHERE ppa.payroll_action_id = c_payroll_action_id;
1028:
1029: --------------------------------------------------------------------+
1030: -- Cursor : csr_period_date_earned
1031: -- Description : Fetches Date Earned for a given payroll
1032: -- run.
1033: --------------------------------------------------------------------+
1034: /*Bug#3662449 *********/
1035: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1036: IS
1037: SELECT ppa.date_earned
1038: FROM pay_payroll_actions ppa
1039: WHERE
1034: /*Bug#3662449 *********/
1035: CURSOR csr_period_date_earned(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
1036: IS
1037: SELECT ppa.date_earned
1038: FROM pay_payroll_actions ppa
1039: WHERE
1040: ppa.payroll_action_id = c_payroll_action_id;
1041:
1042:
1736: end assignment_action_code;
1737:
1738: procedure archive_code
1739: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1740: ,p_effective_date in pay_payroll_actions.effective_date%type
1741: ) is
1742:
1743: /* Bug 3873942
1744: Cursor c_employee_details - Added join with table per_grades_tl to fetch and archive the grade name.
1811: is
1812: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1813: ,max(paa.action_sequence)
1814: from pay_assignment_actions paa
1815: , pay_payroll_actions ppa
1816: , per_assignments_f paf
1817: where paa.assignment_id = paf.assignment_id
1818: and paf.assignment_id = c_assignment_id
1819: and ppa.payroll_action_id = paa.payroll_action_id
1831: is
1832: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1833: ,max(paa.action_sequence)
1834: from pay_assignment_actions paa
1835: , pay_payroll_actions ppa
1836: , per_assignments_f paf
1837: where paa.assignment_id = paf.assignment_id
1838: and paf.assignment_id = c_assignment_id
1839: and ppa.payroll_action_id = paa.payroll_action_id
2096:
2097: end archive_code;
2098:
2099: procedure spawn_archive_reports
2100: (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
2101: is
2102: l_count number;
2103: ps_request_id NUMBER;
2104: l_print_style VARCHAR2(2);
2112: -- Description : Fetches User Parameters from Legislative_paramters
2113: -- column.
2114: --------------------------------------------------------------------+
2115:
2116: CURSOR csr_report_params(c_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
2117: IS
2118: SELECT pay_core_utils.get_parameter('PAY',legislative_parameters) payroll_id,
2119: pay_core_utils.get_parameter('ORG',legislative_parameters) org_id,
2120: pay_core_utils.get_parameter('BG',legislative_parameters) business_group_id,
2133: pay_core_utils.get_parameter('NEG_REC',legislative_parameters) negative_records,
2134: pay_core_utils.get_parameter('EMP_TYPE',legislative_parameters) employee_type,
2135: pay_core_utils.get_parameter('DEL_ACT',legislative_parameters) delete_actions, /*Bug# 4142159*/
2136: pay_core_utils.get_parameter('OUTPUT_TYPE',legislative_parameters) output_type /*Bug# 5174524*/
2137: FROM pay_payroll_actions ppa
2138: WHERE ppa.payroll_action_id = c_payroll_action_id;
2139:
2140:
2141: cursor csr_get_print_options(p_payroll_action_id NUMBER) IS
2142: SELECT printer,
2143: print_style,
2144: decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
2145: ,number_of_copies /* Bug 4116833*/
2146: FROM pay_payroll_actions pact,
2147: fnd_concurrent_requests fcr
2148: WHERE fcr.request_id = pact.request_id
2149: AND pact.payroll_action_id = p_payroll_action_id;
2150: