[Home] [Help]
926: and papf.business_group_id = pasf.business_group_id;
927:
928: CURSOR cur_payroll_assg_action is
929: select paa.assignment_action_id pay_assg_act_id
930: from pay_assignment_actions paa,
931: pay_payroll_actions ppa
932: where paa.assignment_id in (select assignment_id
933: from per_all_assignments_f
934: where person_id = p_person_id
962: AND pdb.legislation_code='IE';
963:
964: CURSOR payroll_asg_action is
965: select max(paa.assignment_action_id)
966: from pay_assignment_actions paa,
967: pay_payroll_actions ppa
968: where paa.assignment_id in (select assignment_id
969: from per_all_assignments_f
970: where person_id = p_person_id
1054: pac.CONTEXT_VALUE,
1055: null,
1056: null))
1057: FROM pay_action_contexts pac,
1058: pay_assignment_actions pas,
1059: pay_payroll_actions ppa
1060: WHERE substr(pac.Context_Value,1,4) = p_context_value
1061: AND pac.assignment_id in (select papf.assignment_id
1062: from per_all_assignments_f papf
1071: and pas.action_sequence <= l_current_sequence;
1072:
1073: CURSOR cur_get_prev_run_seq is
1074: select paa.action_sequence
1075: from pay_assignment_actions paa,
1076: pay_payroll_actions ppa,
1077: pay_action_interlocks pai,
1078: pay_assignment_actions paa1
1079: where paa1.source_action_id = p_last_p45_act
1074: select paa.action_sequence
1075: from pay_assignment_actions paa,
1076: pay_payroll_actions ppa,
1077: pay_action_interlocks pai,
1078: pay_assignment_actions paa1
1079: where paa1.source_action_id = p_last_p45_act
1080: and pai.locking_action_id = paa1.assignment_action_id
1081: and pai.locked_action_id = paa.assignment_action_id
1082: and paa.assignment_id in (select papf.assignment_id
1088: and ppa.action_type in ('R','Q','I','B','V');
1089:
1090: CURSOR cur_get_curr_run_seq is
1091: select action_sequence
1092: from pay_assignment_actions ppa
1093: where assignment_action_id = p_payroll_child_actid;
1094:
1095: l_class_weeks number;
1096: -- end for bug 5591812
1541: select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0) weeks
1542: from pay_run_result_values TARGET
1543: , pay_balance_feeds_f FEED
1544: , pay_run_results RR
1545: , pay_assignment_actions ASSACT
1546: , pay_assignment_actions BAL_ASSACT
1547: , pay_payroll_actions PACT
1548: , pay_payroll_actions BACT
1549: , per_time_periods PPTP
1542: from pay_run_result_values TARGET
1543: , pay_balance_feeds_f FEED
1544: , pay_run_results RR
1545: , pay_assignment_actions ASSACT
1546: , pay_assignment_actions BAL_ASSACT
1547: , pay_payroll_actions PACT
1548: , pay_payroll_actions BACT
1549: , per_time_periods PPTP
1550: , per_time_periods BPTP
1586: union all
1587: select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0) weeks
1588: from pay_run_result_values TARGET
1589: , pay_run_results RR
1590: , pay_assignment_actions ASSACT
1591: , pay_balance_feeds_f FEED
1592: where ASSACT.assignment_action_id in (select min(assignment_action_id) from
1593: pay_assignment_actions where assignment_id = p_assignment_id)
1594: and FEED.balance_type_id +0 = p_balance_type_id
1589: , pay_run_results RR
1590: , pay_assignment_actions ASSACT
1591: , pay_balance_feeds_f FEED
1592: where ASSACT.assignment_action_id in (select min(assignment_action_id) from
1593: pay_assignment_actions where assignment_id = p_assignment_id)
1594: and FEED.balance_type_id +0 = p_balance_type_id
1595: and FEED.input_value_id = TARGET.input_value_id
1596: and nvl(TARGET.result_value,'0') <> '0'
1597: and TARGET.run_result_id = RR.run_result_id
1604: -- payroll runs having prsi contribution class as A
1605: -- in the same tax year for which the archive is run
1606: /*CURSOR Cur_Act_Contexts IS
1607: SELECT pac.Context_ID,pac.Context_Value,pac.Assignment_action_id
1608: FROM pay_action_contexts pac,pay_assignment_actions pas,
1609: pay_payroll_actions ppa,pay_payroll_actions appa
1610: WHERE pac.Context_Value = 'IE_A'
1611: AND pac.assignment_id = p_assignment_id
1612: AND pas.assignment_action_id = pac.assignment_action_id
1628: -- for bug 5383808, to get the action_sequence of run locked by
1629: -- the previous P45 archive.
1630: CURSOR cur_get_prev_run_seq is
1631: select paa.action_sequence
1632: from pay_assignment_actions paa,
1633: pay_payroll_actions ppa,
1634: pay_action_interlocks pai,
1635: pay_assignment_actions paa1
1636: where paa1.source_action_id = p_last_p45_action
1631: select paa.action_sequence
1632: from pay_assignment_actions paa,
1633: pay_payroll_actions ppa,
1634: pay_action_interlocks pai,
1635: pay_assignment_actions paa1
1636: where paa1.source_action_id = p_last_p45_action
1637: and pai.locking_action_id = paa1.assignment_action_id
1638: and pai.locked_action_id = paa.assignment_action_id
1639: and paa.assignment_id in (select papf.assignment_id
1647: -- for bug 5383808, get the action_sequence of run locked by
1648: -- current p45 archive.
1649: CURSOR cur_get_curr_run_seq is
1650: select action_sequence
1651: from pay_assignment_actions ppa
1652: where assignment_action_id = p_source_id;
1653:
1654: --bug 5383808. IF previous P45 exists fetch the sum of PRSI insurable
1655: -- weeks for class between the current run action sequence locked by P45
1663: pac.CONTEXT_VALUE,
1664: null,
1665: null))
1666: FROM pay_action_contexts pac,
1667: pay_assignment_actions pas,
1668: pay_payroll_actions ppa
1669: WHERE pac.Context_Value like 'IE_A%'
1670: AND pac.assignment_id in (select papf.assignment_id
1671: from per_all_assignments_f papf
1726: pac.CONTEXT_VALUE,
1727: null,
1728: null))
1729: FROM pay_action_contexts pac,
1730: pay_assignment_actions pas,
1731: pay_payroll_actions ppa
1732: WHERE pac.Context_Value like 'IE_A%'
1733: AND pac.assignment_id in (select paaf.assignment_id
1734: from per_all_assignments_f paaf, per_assignment_extra_info paei
2411: -- cursor to fetch source_id from the last p45 action
2412: CURSOR get_last_source_id is
2413: select source_id from
2414: pay_action_information pai,
2415: pay_assignment_actions paa
2416: where paa.assignment_action_id = p_last_p45_action
2417: and paa.assignment_action_id = pai.action_context_id
2418: and pai.action_information_category='EMEA BALANCES';
2419:
2634: from per_periods_of_service ppos
2635: where ppos.person_id = p_person_id
2636: and ppos.period_of_service_id = (select max(paf.period_of_service_id)
2637: from per_all_assignments_f paf,
2638: pay_assignment_actions paa,
2639: pay_action_interlocks pai
2640: where pai.locking_action_id = p_action_context_id
2641: and pai.locked_action_id = paa.assignment_action_id
2642: and paa.action_status IN ('C','S') --10225372
2674:
2675:
2676: cursor cur_supp_run is
2677: select act_inf.action_information3
2678: from pay_assignment_actions paa_run,
2679: pay_action_interlocks pai,
2680: pay_assignment_actions paa,
2681: pay_payroll_actions ppa,
2682: pay_action_information act_inf
2676: cursor cur_supp_run is
2677: select act_inf.action_information3
2678: from pay_assignment_actions paa_run,
2679: pay_action_interlocks pai,
2680: pay_assignment_actions paa,
2681: pay_payroll_actions ppa,
2682: pay_action_information act_inf
2683: where ppa.payroll_action_id = paa.payroll_action_id
2684: and ppa.report_type = 'P45'
2808: from per_periods_of_service ppos
2809: where ppos.person_id = p_person_id
2810: and ppos.period_of_service_id = (select max(paf.period_of_service_id)
2811: from per_all_assignments_f paf,
2812: pay_assignment_actions paa,
2813: pay_action_interlocks pai
2814: where pai.locking_action_id = p_action_context_id
2815: and pai.locked_action_id = paa.assignment_action_id
2816: and paa.action_status IN ('C','S') --10225372
2894: select result_value
2895: from pay_run_result_values prr,
2896: pay_run_results pr,
2897: pay_input_values_f piv,
2898: pay_assignment_actions pas,
2899: pay_payroll_actions ppa
2900: where pas.assignment_id in (select assignment_id
2901: from per_all_assignments_f
2902: where person_id = p_person_id)
2914: /*
2915: cursor cur_supp_run is
2916: select count(*)
2917: from pay_action_information pai,
2918: pay_assignment_Actions paa
2919: where paa.assignment_action_id = pai.action_context_id
2920: and pai.action_context_type = 'AAP'
2921: and pai.action_information_category = 'IE P45 INFORMATION'
2922: and paa.tax_unit_id = g_paye_ref
2929: */
2930:
2931: /* cursor cur_supp_run is
2932: select act_inf.action_information3
2933: from pay_assignment_actions paa_run,
2934: pay_action_interlocks pai,
2935: pay_assignment_actions paa,
2936: pay_payroll_actions ppa,
2937: pay_action_information act_inf
2931: /* cursor cur_supp_run is
2932: select act_inf.action_information3
2933: from pay_assignment_actions paa_run,
2934: pay_action_interlocks pai,
2935: pay_assignment_actions paa,
2936: pay_payroll_actions ppa,
2937: pay_action_information act_inf
2938: where ppa.payroll_action_id = paa.payroll_action_id
2939: and ppa.report_type = 'P45'
3282: -- AND org.org_information_id = g_tax_dis_ref
3283: -- AND org.org_information2 = g_paye_ref
3284: AND org.organization_id = g_paye_ref
3285: AND exists (SELECT NULL
3286: FROM pay_assignment_actions paa,
3287: pay_action_interlocks pai,
3288: pay_assignment_actions paa_arc
3289: WHERE pai.locked_action_id = paa.assignment_action_id
3290: AND pai.locking_action_id = paa_arc.assignment_action_id
3284: AND org.organization_id = g_paye_ref
3285: AND exists (SELECT NULL
3286: FROM pay_assignment_actions paa,
3287: pay_action_interlocks pai,
3288: pay_assignment_actions paa_arc
3289: WHERE pai.locked_action_id = paa.assignment_action_id
3290: AND pai.locking_action_id = paa_arc.assignment_action_id
3291: AND paa_arc.payroll_action_id = p_pact_id
3292: AND paa.payroll_action_id = pact.payroll_action_id
3344: /*
3345: CURSOR csr_all_payroll_info(p_pact_id NUMBER) IS
3346: SELECT pact.payroll_action_id payroll_action_id,
3347: pact.effective_date effective_date
3348: FROM pay_assignment_actions paa,
3349: pay_action_interlocks pai,
3350: pay_assignment_actions paa_arc,
3351: pay_payroll_actions pact
3352: WHERE pai.locked_action_id = paa.assignment_action_id
3346: SELECT pact.payroll_action_id payroll_action_id,
3347: pact.effective_date effective_date
3348: FROM pay_assignment_actions paa,
3349: pay_action_interlocks pai,
3350: pay_assignment_actions paa_arc,
3351: pay_payroll_actions pact
3352: WHERE pai.locked_action_id = paa.assignment_action_id
3353: AND pai.locking_action_id = paa_arc.assignment_action_id
3354: AND paa_arc.payroll_action_id = p_pact_id
3637: as1.assignment_number works_number,
3638: as1.period_of_service_id period_of_service_id
3639: FROM --per_periods_of_service ppos,
3640: per_all_assignments_f as1,
3641: pay_assignment_actions act,
3642: pay_payroll_actions appa,
3643: pay_action_interlocks pai,
3644: pay_assignment_actions act1,
3645: pay_payroll_actions appa2
3640: per_all_assignments_f as1,
3641: pay_assignment_actions act,
3642: pay_payroll_actions appa,
3643: pay_action_interlocks pai,
3644: pay_assignment_actions act1,
3645: pay_payroll_actions appa2
3646: WHERE /*appa.consolidation_set_id = p_consolidation_id*/
3647: act.tax_unit_id = p_paye_ref
3648: AND appa.effective_date BETWEEN g_archive_start_date AND g_archive_end_date
3697: AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
3698: AND appa2.payroll_action_id = (SELECT /*+ USE_NL(ACT2 APPA4)*/
3699: max(appa4.payroll_action_id)
3700: FROM /*pay_pre_payments ppp, --Bug 4193738 --Bug 4468864*/
3701: pay_assignment_actions act2,
3702: pay_payroll_actions appa4
3703: WHERE /*ppp.assignment_action_id=act2.assignment_action_id
3704: AND*/ act2.assignment_id = act.assignment_id
3705: AND act2.action_status IN ('C','S') --10225372
3708: AND appa4.effective_date BETWEEN g_archive_start_date AND g_archive_end_date)
3709: -- bug 5597735, change the not exists clause.
3710: -- refer bug 5233518 for more details.
3711: AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
3712: from pay_assignment_actions act3,
3713: pay_payroll_actions appa3,
3714: pay_action_interlocks pai, --bug 4208273
3715: pay_assignment_actions act2, --bug 4208273
3716: pay_payroll_actions appa4 --bug 4208273
3711: AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
3712: from pay_assignment_actions act3,
3713: pay_payroll_actions appa3,
3714: pay_action_interlocks pai, --bug 4208273
3715: pay_assignment_actions act2, --bug 4208273
3716: pay_payroll_actions appa4 --bug 4208273
3717: where pai.locked_action_id= act3.assignment_action_id
3718: and pai.locking_action_id=act2.assignment_action_id
3719: and act3.action_sequence >= act1.action_sequence --bug 4193738
3887: THEN
3888:
3889: hr_utility.set_location('Different Person '|| csr_rec.person_id ,22);
3890:
3891: SELECT pay_assignment_actions_s.NEXTVAL
3892: INTO l_actid
3893: FROM dual;
3894:
3895: -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
3937: FROM pay_action_interlocks pre,
3938: pay_action_interlocks pay,
3939: pay_payroll_actions paa,
3940: pay_payroll_actions ppaa,
3941: pay_assignment_actions assact,
3942: pay_assignment_actions passact,
3943: per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
3944: WHERE pre.locked_action_id = pay.locking_action_id
3945: AND pre.locking_action_id = p_locking_action_id
3938: pay_action_interlocks pay,
3939: pay_payroll_actions paa,
3940: pay_payroll_actions ppaa,
3941: pay_assignment_actions assact,
3942: pay_assignment_actions passact,
3943: per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
3944: WHERE pre.locked_action_id = pay.locking_action_id
3945: AND pre.locking_action_id = p_locking_action_id
3946: AND pre.locked_action_id = passact.assignment_action_id
3959: -- Changed the cursor cur_child_pay_action
3960: /*CURSOR cur_child_pay_action(p_assignment_id NUMBER,
3961: p_date_earned DATE)is
3962: SELECT max(paa.assignment_action_id)
3963: FROM pay_assignment_actions paa,
3964: pay_payroll_actions ppa
3965: where paa.assignment_id = p_assignment_id
3966: AND paa.payroll_action_id = ppa.payroll_action_id
3967: AND ppa.date_earned =p_date_earned
3974: p_lat_act_seq IN NUMBER) is
3975: SELECT /*+ USE_NL(paa, ppa) */
3976: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3977: paa.assignment_action_id),16))
3978: FROM pay_assignment_actions paa,
3979: pay_payroll_actions ppa
3980: WHERE paa.payroll_action_id = ppa.payroll_action_id
3981: AND paa.assignment_id in (select assignment_id
3982: from per_all_assignments_f
3994: p_person_id NUMBER
3995: ) IS
3996: SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
3997: FROM pay_payroll_actions ppa_p45,
3998: pay_assignment_actions p45_src,
3999: pay_action_information pai_p45,
4000: pay_assignment_actions paa_src
4001: WHERE ppa_p45.action_type = 'X'
4002: AND ppa_p45.report_type = 'P45'
3996: SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
3997: FROM pay_payroll_actions ppa_p45,
3998: pay_assignment_actions p45_src,
3999: pay_action_information pai_p45,
4000: pay_assignment_actions paa_src
4001: WHERE ppa_p45.action_type = 'X'
4002: AND ppa_p45.report_type = 'P45'
4003: AND ppa_p45.report_qualifier = 'IE'
4004: AND ppa_p45.payroll_action_id <> p_pact_id
4017: CURSOR cur_get_last_p45(p_person_id NUMBER,p_termination_date DATE,p_pact NUMBER, c_assignment_id NUMBER) IS
4018: SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
4019: paa.assignment_action_id),16))
4020: FROM pay_payroll_actions ppa,
4021: pay_assignment_actions paa,
4022: pay_action_information pai
4023: WHERE paa.assignment_action_id = pai.action_context_id
4024: AND pai.action_information_category = 'IE P45 INFORMATION'
4025: AND pai.action_context_type = 'AAP'
4036: -- AND paa.assignment_id=c_assignment_id
4037: ; /* knadhan QA */
4038:
4039: -- cursor to fetch Payroll action of Last P45 to pass to get_arc_bal_value 5005788
4040: CURSOR cur_get_p45_pact(p_p45_aact pay_assignment_actions.assignment_action_id%TYPE) IS
4041: SELECT paa.payroll_action_id
4042: FROM pay_assignment_actions paa
4043: WHERE paa.assignment_action_id = p_p45_aact;
4044:
4038:
4039: -- cursor to fetch Payroll action of Last P45 to pass to get_arc_bal_value 5005788
4040: CURSOR cur_get_p45_pact(p_p45_aact pay_assignment_actions.assignment_action_id%TYPE) IS
4041: SELECT paa.payroll_action_id
4042: FROM pay_assignment_actions paa
4043: WHERE paa.assignment_action_id = p_p45_aact;
4044:
4045: -- cursor to retrieve payroll id
4046: CURSOR cur_assgn_payroll(p_assignment_id NUMBER,
4068: c_ppsn_override per_assignment_extra_info.aei_information1%type) is
4069: SELECT /*+ USE_NL(paa, ppa) */
4070: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
4071: paa.assignment_action_id),16))
4072: FROM pay_assignment_actions paa,
4073: pay_payroll_actions ppa
4074: WHERE paa.payroll_action_id = ppa.payroll_action_id
4075: AND paa.assignment_id in (select paaf.assignment_id
4076: from per_all_assignments_f paaf, per_assignment_extra_info paei
4130:
4131: -- retrieve the chunk number for the current assignment action
4132: SELECT paa.chunk_number,paa.assignment_id
4133: INTO l_chunk_number,l_assignment_id
4134: FROM pay_assignment_actions paa
4135: WHERE paa.assignment_action_id = p_assactid;
4136:
4137: l_action_context_id := p_assactid;
4138: l_record_count := 0;
4229: IF (l_record_count = 0 AND csr_rec.assignment_id = l_assignment_id)
4230: THEN
4231: -- Create child P45 action to lock the child payroll process child action
4232: -- To avoid data corruption 4468864
4233: SELECT pay_assignment_actions_s.NEXTVAL
4234: INTO l_actid
4235: FROM dual;
4236:
4237: hr_nonrun_asact.insact(
4469: -- AND pai1.action_information7 = c_ppsn_flag
4470: -- and ((nvl(pai1.action_information7,'N')='N') or (pai1.action_information7='Y'))
4471: and ((nvl(pai1.action_information7,'N')='N' and c_ppsn_flag='N') or (nvl(pai1.action_information7,'N')='Y' and c_ppsn_flag='Y') ) ;
4472:
4473: CURSOR csr_get_arc_bal_value(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
4474: ,p_def_bal_id pay_action_information.action_information1%TYPE) IS
4475: SELECT to_number(pai2.action_information4) balance_value
4476: FROM pay_action_information pai2
4477: WHERE pai2.action_context_id = p_assignment_action_id
4479: AND pai2.action_context_type = 'AAP'
4480: AND pai2.action_information1 = p_def_bal_id;
4481:
4482: /* 7291676 */
4483: CURSOR csr_check_override_present (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
4484: SELECT paei.aei_information1
4485: FROM per_assignment_extra_info paei,
4486: pay_assignment_actions paa
4487: WHERE paa.assignment_action_id=p_assignment_action_id
4482: /* 7291676 */
4483: CURSOR csr_check_override_present (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
4484: SELECT paei.aei_information1
4485: FROM per_assignment_extra_info paei,
4486: pay_assignment_actions paa
4487: WHERE paa.assignment_action_id=p_assignment_action_id
4488: and paei.assignment_id=paa.assignment_id
4489: and paei.aei_information_category = 'IE_ASG_OVERRIDE';
4490:
4584: FROM pay_action_information pai_ed
4585: ,pay_action_information pai_iep45
4586: ,pay_action_information pai_ieed
4587: ,pay_action_information pai_epif
4588: ,pay_assignment_actions paa
4589: ,pay_action_interlocks pai_arc
4590: ,pay_assignment_actions paa_payroll
4591: ,per_time_periods ptp
4592: ,pay_ie_p45_address_details emp_address
4586: ,pay_action_information pai_ieed
4587: ,pay_action_information pai_epif
4588: ,pay_assignment_actions paa
4589: ,pay_action_interlocks pai_arc
4590: ,pay_assignment_actions paa_payroll
4591: ,per_time_periods ptp
4592: ,pay_ie_p45_address_details emp_address
4593: ,pay_ie_p45_employee_details emp_details
4594: ,pay_ie_p45_soc_ben_details emp_soc
4634: ,pai_iep45.action_information8 person_id -- 7291676
4635: ,pai_iep45.action_information9 main_p45_date_paid -- 7291676
4636: FROM pay_action_information pai_ed
4637: ,pay_action_information pai_iep45
4638: ,pay_assignment_actions paa
4639: ,per_time_periods ptp
4640: WHERE paa.payroll_action_id = c_p45_arch_id
4641: AND paa.assignment_action_id = pai_iep45.action_context_id
4642: AND pai_iep45.action_context_type ='AAP'
4647: AND pai_ed.action_information_category = 'EMPLOYEE DETAILS'
4648: AND paa.assignment_id = NVL(p_assignment_id,paa.assignment_id)
4649: ORDER BY pai_iep45.action_information2;
4650: --
4651: CURSOR cur_p45_employer_no (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
4652: SELECT pai_epif.action_information6 employer_number
4653: FROM pay_assignment_actions paa
4654: ,pay_action_interlocks pai_arc
4655: ,pay_assignment_actions paa_payroll
4649: ORDER BY pai_iep45.action_information2;
4650: --
4651: CURSOR cur_p45_employer_no (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
4652: SELECT pai_epif.action_information6 employer_number
4653: FROM pay_assignment_actions paa
4654: ,pay_action_interlocks pai_arc
4655: ,pay_assignment_actions paa_payroll
4656: ,pay_action_information pai_epif
4657: WHERE paa.assignment_action_id = p_assignment_action_id
4651: CURSOR cur_p45_employer_no (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
4652: SELECT pai_epif.action_information6 employer_number
4653: FROM pay_assignment_actions paa
4654: ,pay_action_interlocks pai_arc
4655: ,pay_assignment_actions paa_payroll
4656: ,pay_action_information pai_epif
4657: WHERE paa.assignment_action_id = p_assignment_action_id
4658: AND paa.assignment_action_id = pai_arc.locking_action_id
4659: AND paa_payroll.assignment_action_id = pai_arc.locked_action_id
4661: AND pai_epif.action_context_type ='PA'
4662: AND pai_epif.action_information_category = 'EMEA PAYROLL INFO'
4663: AND pai_epif.action_information1 = paa_payroll.payroll_action_id;
4664: --
4665: CURSOR cur_p45_ie_emp_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
4666: SELECT (round(to_number(substr(nvl(pai_ieed.action_information26,'0'), 1,30)),2)*100) taxcredit
4667: ,(round(to_number(substr(nvl(pai_ieed.action_information27,'0'), 1,30)),2)*100) cutoff
4668: ,substr(pai_ieed.action_information22, 1,30) prsi_class
4669: ,(round(to_number(substr(nvl(pai_ieed.action_information20,'0'), 1,30)),2)*100) usccutoff1 -- BUG 13359530
4674: AND pai_ieed.action_information_category = 'IE EMPLOYEE DETAILS';
4675: cur_p45_ie_emp_details_rec cur_p45_ie_emp_details%ROWTYPE;
4676: --
4677: --6615117
4678: CURSOR cur_p45_emp_soc_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
4679: SELECT (round(to_number(nvl(emp_soc.disability_benefit,'0')),2)*100) benefit
4680: ,(round(to_number(nvl(emp_soc.red_tax_credit,'0')),2)*100) taxcreditreduction
4681: ,(round(to_number(nvl(emp_soc.red_std_cut_off,'0')),2)*100) cutoffreduction
4682: ,emp_soc.non_cummulative_basis noncumulative
4684: WHERE emp_soc.assignment_action_id = p_assignment_action_id;
4685: cur_p45_emp_soc_details_rec cur_p45_emp_soc_details%ROWTYPE;
4686: --
4687: --6615117
4688: CURSOR cur_p45_emp_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
4689: SELECT emp_details.pps_no ppsn
4690: ,emp_details.last_name surname
4691: ,emp_details.first_name firstname
4692: ,emp_details.works_no works
4702: FROM pay_ie_p45_employee_details emp_details
4703: WHERE emp_details.assignment_action_id = p_assignment_action_id;
4704: cur_p45_emp_details_rec cur_p45_emp_details%ROWTYPE;
4705: --
4706: CURSOR cur_p45_emp_address (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
4707: SELECT emp_address.address1 address1
4708: ,emp_address.address2 address2
4709: ,emp_address.address3 address3
4710: FROM pay_ie_p45_address_details emp_address
4710: FROM pay_ie_p45_address_details emp_address
4711: WHERE emp_address.assignment_action_id = p_assignment_action_id;
4712: cur_p45_emp_address_rec cur_p45_emp_address%ROWTYPE;
4713: --
4714: CURSOR cur_p45_paye_prsi_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
4715: SELECT (round(to_number(emp_paye.total_tax),2)*100) totaltax
4716: ,(round(to_number(emp_paye.total_pay),2)*100) totalpay
4717: ,(round(to_number(emp_paye.this_tax),2)*100) thistax
4718: ,(round(to_number(emp_paye.this_pay),2)*100) thispay
4753: --
4754: CURSOR cur_p30_start_date(
4755: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
4756: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
4757: FROM pay_assignment_actions paa_p30,
4758: pay_action_interlocks pai_p30,
4759: pay_assignment_actions paa_arc,
4760: pay_payroll_actions ppa_arc
4761: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
4755: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
4756: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
4757: FROM pay_assignment_actions paa_p30,
4758: pay_action_interlocks pai_p30,
4759: pay_assignment_actions paa_arc,
4760: pay_payroll_actions ppa_arc
4761: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
4762: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
4763: AND paa_arc.assignment_action_id = pai_p30.locked_action_id
4779:
4780:
4781: /* 7291676 */
4782: /* to check whether the termination date of the assignment is after 2009 */
4783: CURSOR cur_service_leave_year(c_person_id per_all_people_f.person_id%type,c_action_context_id pay_assignment_actions.assignment_action_id%type) IS
4784: select 'Y'
4785: from per_periods_of_service ppos
4786: where ppos.person_id = c_person_id
4787: and ppos.period_of_service_id = (select max(paf.period_of_service_id)
4785: from per_periods_of_service ppos
4786: where ppos.person_id = c_person_id
4787: and ppos.period_of_service_id = (select max(paf.period_of_service_id)
4788: from per_all_assignments_f paf,
4789: pay_assignment_actions paa,
4790: pay_action_interlocks pai
4791: where pai.locking_action_id = c_action_context_id
4792: and pai.locked_action_id = paa.assignment_action_id
4793: and paa.action_status IN ('C','S') --10225372