[Home] [Help]
726: and papf.business_group_id = pasf.business_group_id;
727:
728: CURSOR cur_payroll_assg_action is
729: select paa.assignment_action_id pay_assg_act_id
730: from pay_assignment_actions paa,
731: pay_payroll_actions ppa
732: where paa.assignment_id in (select assignment_id
733: from per_all_assignments_f
734: where person_id = p_person_id
762: AND pdb.legislation_code='IE';
763:
764: CURSOR payroll_asg_action is
765: select max(paa.assignment_action_id)
766: from pay_assignment_actions paa,
767: pay_payroll_actions ppa
768: where paa.assignment_id in (select assignment_id
769: from per_all_assignments_f
770: where person_id = p_person_id
841: pac.CONTEXT_VALUE,
842: null,
843: null))
844: FROM pay_action_contexts pac,
845: pay_assignment_actions pas,
846: pay_payroll_actions ppa
847: WHERE substr(pac.Context_Value,1,4) = p_context_value
848: AND pac.assignment_id in (select papf.assignment_id
849: from per_all_assignments_f papf
858: and pas.action_sequence <= l_current_sequence;
859:
860: CURSOR cur_get_prev_run_seq is
861: select paa.action_sequence
862: from pay_assignment_actions paa,
863: pay_payroll_actions ppa,
864: pay_action_interlocks pai,
865: pay_assignment_actions paa1
866: where paa1.source_action_id = p_last_p45_act
861: select paa.action_sequence
862: from pay_assignment_actions paa,
863: pay_payroll_actions ppa,
864: pay_action_interlocks pai,
865: pay_assignment_actions paa1
866: where paa1.source_action_id = p_last_p45_act
867: and pai.locking_action_id = paa1.assignment_action_id
868: and pai.locked_action_id = paa.assignment_action_id
869: and paa.assignment_id in (select papf.assignment_id
875: and ppa.action_type in ('R','Q','I','B','V');
876:
877: CURSOR cur_get_curr_run_seq is
878: select action_sequence
879: from pay_assignment_actions ppa
880: where assignment_action_id = p_payroll_child_actid;
881:
882: l_class_weeks number;
883: -- end for bug 5591812
1277: select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0) weeks
1278: from pay_run_result_values TARGET
1279: , pay_balance_feeds_f FEED
1280: , pay_run_results RR
1281: , pay_assignment_actions ASSACT
1282: , pay_assignment_actions BAL_ASSACT
1283: , pay_payroll_actions PACT
1284: , pay_payroll_actions BACT
1285: , per_time_periods PPTP
1278: from pay_run_result_values TARGET
1279: , pay_balance_feeds_f FEED
1280: , pay_run_results RR
1281: , pay_assignment_actions ASSACT
1282: , pay_assignment_actions BAL_ASSACT
1283: , pay_payroll_actions PACT
1284: , pay_payroll_actions BACT
1285: , per_time_periods PPTP
1286: , per_time_periods BPTP
1322: union all
1323: select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0) weeks
1324: from pay_run_result_values TARGET
1325: , pay_run_results RR
1326: , pay_assignment_actions ASSACT
1327: , pay_balance_feeds_f FEED
1328: where ASSACT.assignment_action_id in (select min(assignment_action_id) from
1329: pay_assignment_actions where assignment_id = p_assignment_id)
1330: and FEED.balance_type_id +0 = p_balance_type_id
1325: , pay_run_results RR
1326: , pay_assignment_actions ASSACT
1327: , pay_balance_feeds_f FEED
1328: where ASSACT.assignment_action_id in (select min(assignment_action_id) from
1329: pay_assignment_actions where assignment_id = p_assignment_id)
1330: and FEED.balance_type_id +0 = p_balance_type_id
1331: and FEED.input_value_id = TARGET.input_value_id
1332: and nvl(TARGET.result_value,'0') <> '0'
1333: and TARGET.run_result_id = RR.run_result_id
1340: -- payroll runs having prsi contribution class as A
1341: -- in the same tax year for which the archive is run
1342: /*CURSOR Cur_Act_Contexts IS
1343: SELECT pac.Context_ID,pac.Context_Value,pac.Assignment_action_id
1344: FROM pay_action_contexts pac,pay_assignment_actions pas,
1345: pay_payroll_actions ppa,pay_payroll_actions appa
1346: WHERE pac.Context_Value = 'IE_A'
1347: AND pac.assignment_id = p_assignment_id
1348: AND pas.assignment_action_id = pac.assignment_action_id
1364: -- for bug 5383808, to get the action_sequence of run locked by
1365: -- the previous P45 archive.
1366: CURSOR cur_get_prev_run_seq is
1367: select paa.action_sequence
1368: from pay_assignment_actions paa,
1369: pay_payroll_actions ppa,
1370: pay_action_interlocks pai,
1371: pay_assignment_actions paa1
1372: where paa1.source_action_id = p_last_p45_action
1367: select paa.action_sequence
1368: from pay_assignment_actions paa,
1369: pay_payroll_actions ppa,
1370: pay_action_interlocks pai,
1371: pay_assignment_actions paa1
1372: where paa1.source_action_id = p_last_p45_action
1373: and pai.locking_action_id = paa1.assignment_action_id
1374: and pai.locked_action_id = paa.assignment_action_id
1375: and paa.assignment_id in (select papf.assignment_id
1383: -- for bug 5383808, get the action_sequence of run locked by
1384: -- current p45 archive.
1385: CURSOR cur_get_curr_run_seq is
1386: select action_sequence
1387: from pay_assignment_actions ppa
1388: where assignment_action_id = p_source_id;
1389:
1390: --bug 5383808. IF previous P45 exists fetch the sum of PRSI insurable
1391: -- weeks for class between the current run action sequence locked by P45
1399: pac.CONTEXT_VALUE,
1400: null,
1401: null))
1402: FROM pay_action_contexts pac,
1403: pay_assignment_actions pas,
1404: pay_payroll_actions ppa
1405: WHERE pac.Context_Value like 'IE_A%'
1406: AND pac.assignment_id in (select papf.assignment_id
1407: from per_all_assignments_f papf
1854: -- cursor to fetch source_id from the last p45 action
1855: CURSOR get_last_source_id is
1856: select source_id from
1857: pay_action_information pai,
1858: pay_assignment_actions paa
1859: where paa.assignment_action_id = p_last_p45_action
1860: and paa.assignment_action_id = pai.action_context_id
1861: and pai.action_information_category='EMEA BALANCES';
1862:
1967: from per_periods_of_service ppos
1968: where ppos.person_id = p_person_id
1969: and ppos.period_of_service_id = (select max(paf.period_of_service_id)
1970: from per_all_assignments_f paf,
1971: pay_assignment_actions paa,
1972: pay_action_interlocks pai
1973: where pai.locking_action_id = p_action_context_id
1974: and pai.locked_action_id = paa.assignment_action_id
1975: and paa.action_status = 'C'
2007:
2008:
2009: cursor cur_supp_run is
2010: select act_inf.action_information3
2011: from pay_assignment_actions paa_run,
2012: pay_action_interlocks pai,
2013: pay_assignment_actions paa,
2014: pay_payroll_actions ppa,
2015: pay_action_information act_inf
2009: cursor cur_supp_run is
2010: select act_inf.action_information3
2011: from pay_assignment_actions paa_run,
2012: pay_action_interlocks pai,
2013: pay_assignment_actions paa,
2014: pay_payroll_actions ppa,
2015: pay_action_information act_inf
2016: where ppa.payroll_action_id = paa.payroll_action_id
2017: and ppa.report_type = 'P45'
2141: from per_periods_of_service ppos
2142: where ppos.person_id = p_person_id
2143: and ppos.period_of_service_id = (select max(paf.period_of_service_id)
2144: from per_all_assignments_f paf,
2145: pay_assignment_actions paa,
2146: pay_action_interlocks pai
2147: where pai.locking_action_id = p_action_context_id
2148: and pai.locked_action_id = paa.assignment_action_id
2149: and paa.action_status = 'C'
2227: select result_value
2228: from pay_run_result_values prr,
2229: pay_run_results pr,
2230: pay_input_values_f piv,
2231: pay_assignment_actions pas,
2232: pay_payroll_actions ppa
2233: where pas.assignment_id in (select assignment_id
2234: from per_all_assignments_f
2235: where person_id = p_person_id)
2247: /*
2248: cursor cur_supp_run is
2249: select count(*)
2250: from pay_action_information pai,
2251: pay_assignment_Actions paa
2252: where paa.assignment_action_id = pai.action_context_id
2253: and pai.action_context_type = 'AAP'
2254: and pai.action_information_category = 'IE P45 INFORMATION'
2255: and paa.tax_unit_id = g_paye_ref
2262: */
2263:
2264: /* cursor cur_supp_run is
2265: select act_inf.action_information3
2266: from pay_assignment_actions paa_run,
2267: pay_action_interlocks pai,
2268: pay_assignment_actions paa,
2269: pay_payroll_actions ppa,
2270: pay_action_information act_inf
2264: /* cursor cur_supp_run is
2265: select act_inf.action_information3
2266: from pay_assignment_actions paa_run,
2267: pay_action_interlocks pai,
2268: pay_assignment_actions paa,
2269: pay_payroll_actions ppa,
2270: pay_action_information act_inf
2271: where ppa.payroll_action_id = paa.payroll_action_id
2272: and ppa.report_type = 'P45'
2576: -- AND org.org_information_id = g_tax_dis_ref
2577: -- AND org.org_information2 = g_paye_ref
2578: AND org.organization_id = g_paye_ref
2579: AND exists (SELECT NULL
2580: FROM pay_assignment_actions paa,
2581: pay_action_interlocks pai,
2582: pay_assignment_actions paa_arc
2583: WHERE pai.locked_action_id = paa.assignment_action_id
2584: AND pai.locking_action_id = paa_arc.assignment_action_id
2578: AND org.organization_id = g_paye_ref
2579: AND exists (SELECT NULL
2580: FROM pay_assignment_actions paa,
2581: pay_action_interlocks pai,
2582: pay_assignment_actions paa_arc
2583: WHERE pai.locked_action_id = paa.assignment_action_id
2584: AND pai.locking_action_id = paa_arc.assignment_action_id
2585: AND paa_arc.payroll_action_id = p_pact_id
2586: AND paa.payroll_action_id = pact.payroll_action_id
2638: /*
2639: CURSOR csr_all_payroll_info(p_pact_id NUMBER) IS
2640: SELECT pact.payroll_action_id payroll_action_id,
2641: pact.effective_date effective_date
2642: FROM pay_assignment_actions paa,
2643: pay_action_interlocks pai,
2644: pay_assignment_actions paa_arc,
2645: pay_payroll_actions pact
2646: WHERE pai.locked_action_id = paa.assignment_action_id
2640: SELECT pact.payroll_action_id payroll_action_id,
2641: pact.effective_date effective_date
2642: FROM pay_assignment_actions paa,
2643: pay_action_interlocks pai,
2644: pay_assignment_actions paa_arc,
2645: pay_payroll_actions pact
2646: WHERE pai.locked_action_id = paa.assignment_action_id
2647: AND pai.locking_action_id = paa_arc.assignment_action_id
2648: AND paa_arc.payroll_action_id = p_pact_id
2929: as1.assignment_number works_number,
2930: as1.period_of_service_id period_of_service_id
2931: FROM --per_periods_of_service ppos,
2932: per_all_assignments_f as1,
2933: pay_assignment_actions act,
2934: pay_payroll_actions appa,
2935: pay_action_interlocks pai,
2936: pay_assignment_actions act1,
2937: pay_payroll_actions appa2
2932: per_all_assignments_f as1,
2933: pay_assignment_actions act,
2934: pay_payroll_actions appa,
2935: pay_action_interlocks pai,
2936: pay_assignment_actions act1,
2937: pay_payroll_actions appa2
2938: WHERE /*appa.consolidation_set_id = p_consolidation_id*/
2939: act.tax_unit_id = p_paye_ref
2940: AND appa.effective_date BETWEEN g_archive_start_date AND g_archive_end_date
2989: AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
2990: AND appa2.payroll_action_id = (SELECT /*+ USE_NL(ACT2 APPA4)*/
2991: max(appa4.payroll_action_id)
2992: FROM /*pay_pre_payments ppp, --Bug 4193738 --Bug 4468864*/
2993: pay_assignment_actions act2,
2994: pay_payroll_actions appa4
2995: WHERE /*ppp.assignment_action_id=act2.assignment_action_id
2996: AND*/ act2.assignment_id = act.assignment_id
2997: AND act2.action_status = 'C'
3000: AND appa4.effective_date BETWEEN g_archive_start_date AND g_archive_end_date)
3001: -- bug 5597735, change the not exists clause.
3002: -- refer bug 5233518 for more details.
3003: AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
3004: from pay_assignment_actions act3,
3005: pay_payroll_actions appa3,
3006: pay_action_interlocks pai, --bug 4208273
3007: pay_assignment_actions act2, --bug 4208273
3008: pay_payroll_actions appa4 --bug 4208273
3003: AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
3004: from pay_assignment_actions act3,
3005: pay_payroll_actions appa3,
3006: pay_action_interlocks pai, --bug 4208273
3007: pay_assignment_actions act2, --bug 4208273
3008: pay_payroll_actions appa4 --bug 4208273
3009: where pai.locked_action_id= act3.assignment_action_id
3010: and pai.locking_action_id=act2.assignment_action_id
3011: and act3.action_sequence >= act1.action_sequence --bug 4193738
3118: IF (l_person_id <> csr_rec.person_id) THEN
3119:
3120: hr_utility.set_location('Different Person '|| csr_rec.person_id ,22);
3121:
3122: SELECT pay_assignment_actions_s.NEXTVAL
3123: INTO l_actid
3124: FROM dual;
3125:
3126: -- CREATE THE ARCHIVE ASSIGNMENT ACTION FOR THE MASTER ASSIGNMENT ACTION
3168: FROM pay_action_interlocks pre,
3169: pay_action_interlocks pay,
3170: pay_payroll_actions paa,
3171: pay_payroll_actions ppaa,
3172: pay_assignment_actions assact,
3173: pay_assignment_actions passact,
3174: per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
3175: WHERE pre.locked_action_id = pay.locking_action_id
3176: AND pre.locking_action_id = p_locking_action_id
3169: pay_action_interlocks pay,
3170: pay_payroll_actions paa,
3171: pay_payroll_actions ppaa,
3172: pay_assignment_actions assact,
3173: pay_assignment_actions passact,
3174: per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
3175: WHERE pre.locked_action_id = pay.locking_action_id
3176: AND pre.locking_action_id = p_locking_action_id
3177: AND pre.locked_action_id = passact.assignment_action_id
3190: -- Changed the cursor cur_child_pay_action
3191: /*CURSOR cur_child_pay_action(p_assignment_id NUMBER,
3192: p_date_earned DATE)is
3193: SELECT max(paa.assignment_action_id)
3194: FROM pay_assignment_actions paa,
3195: pay_payroll_actions ppa
3196: where paa.assignment_id = p_assignment_id
3197: AND paa.payroll_action_id = ppa.payroll_action_id
3198: AND ppa.date_earned =p_date_earned
3205: p_lat_act_seq IN NUMBER) is
3206: SELECT /*+ USE_NL(paa, ppa) */
3207: fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3208: paa.assignment_action_id),16))
3209: FROM pay_assignment_actions paa,
3210: pay_payroll_actions ppa
3211: WHERE paa.payroll_action_id = ppa.payroll_action_id
3212: AND paa.assignment_id in (select assignment_id
3213: from per_all_assignments_f
3225: p_person_id NUMBER
3226: ) IS
3227: SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
3228: FROM pay_payroll_actions ppa_p45,
3229: pay_assignment_actions p45_src,
3230: pay_action_information pai_p45,
3231: pay_assignment_actions paa_src
3232: WHERE ppa_p45.action_type = 'X'
3233: AND ppa_p45.report_type = 'P45'
3227: SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
3228: FROM pay_payroll_actions ppa_p45,
3229: pay_assignment_actions p45_src,
3230: pay_action_information pai_p45,
3231: pay_assignment_actions paa_src
3232: WHERE ppa_p45.action_type = 'X'
3233: AND ppa_p45.report_type = 'P45'
3234: AND ppa_p45.report_qualifier = 'IE'
3235: AND ppa_p45.payroll_action_id <> p_pact_id
3247: CURSOR cur_get_last_p45(p_person_id NUMBER,p_termination_date DATE,p_pact NUMBER) IS
3248: SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3249: paa.assignment_action_id),16))
3250: FROM pay_payroll_actions ppa,
3251: pay_assignment_actions paa,
3252: pay_action_information pai
3253: WHERE paa.assignment_action_id = pai.action_context_id
3254: AND pai.action_information_category = 'IE P45 INFORMATION'
3255: AND pai.action_context_type = 'AAP'
3264: AND paa.action_status = 'C'
3265: AND pai.action_information8 = to_char(p_person_id);
3266:
3267: -- cursor to fetch Payroll action of Last P45 to pass to get_arc_bal_value 5005788
3268: CURSOR cur_get_p45_pact(p_p45_aact pay_assignment_actions.assignment_action_id%TYPE) IS
3269: SELECT paa.payroll_action_id
3270: FROM pay_assignment_actions paa
3271: WHERE paa.assignment_action_id = p_p45_aact;
3272:
3266:
3267: -- cursor to fetch Payroll action of Last P45 to pass to get_arc_bal_value 5005788
3268: CURSOR cur_get_p45_pact(p_p45_aact pay_assignment_actions.assignment_action_id%TYPE) IS
3269: SELECT paa.payroll_action_id
3270: FROM pay_assignment_actions paa
3271: WHERE paa.assignment_action_id = p_p45_aact;
3272:
3273: -- cursor to retrieve payroll id
3274: CURSOR cur_assgn_payroll(p_assignment_id NUMBER,
3319:
3320: -- retrieve the chunk number for the current assignment action
3321: SELECT paa.chunk_number,paa.assignment_id
3322: INTO l_chunk_number,l_assignment_id
3323: FROM pay_assignment_actions paa
3324: WHERE paa.assignment_action_id = p_assactid;
3325:
3326: l_action_context_id := p_assactid;
3327: l_record_count := 0;
3397: IF (l_record_count = 0 AND csr_rec.assignment_id = l_assignment_id)
3398: THEN
3399: -- Create child P45 action to lock the child payroll process child action
3400: -- To avoid data corruption 4468864
3401: SELECT pay_assignment_actions_s.NEXTVAL
3402: INTO l_actid
3403: FROM dual;
3404:
3405: hr_nonrun_asact.insact(
3572: AND pai1.action_information_category = 'EMEA BALANCE DEFINITION'
3573: AND substr(pai1.action_information4, 1,50) = p_balance_name
3574: AND pai1.action_context_id = p_payroll_action_id;
3575:
3576: CURSOR csr_get_arc_bal_value(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
3577: ,p_def_bal_id pay_action_information.action_information1%TYPE) IS
3578: SELECT to_number(pai2.action_information4) balance_value
3579: FROM pay_action_information pai2
3580: WHERE pai2.action_context_id = p_assignment_action_id
3661: FROM pay_action_information pai_ed
3662: ,pay_action_information pai_iep45
3663: ,pay_action_information pai_ieed
3664: ,pay_action_information pai_epif
3665: ,pay_assignment_actions paa
3666: ,pay_action_interlocks pai_arc
3667: ,pay_assignment_actions paa_payroll
3668: ,per_time_periods ptp
3669: ,pay_ie_p45_address_details emp_address
3663: ,pay_action_information pai_ieed
3664: ,pay_action_information pai_epif
3665: ,pay_assignment_actions paa
3666: ,pay_action_interlocks pai_arc
3667: ,pay_assignment_actions paa_payroll
3668: ,per_time_periods ptp
3669: ,pay_ie_p45_address_details emp_address
3670: ,pay_ie_p45_employee_details emp_details
3671: ,pay_ie_p45_soc_ben_details emp_soc
3709: ,pai_iep45.action_information4 emergency_tax,
3710: to_date(substr(pai_iep45.action_information7, 1,30),'DD/MM/RRRR') date_paid --Bug 3991416
3711: FROM pay_action_information pai_ed
3712: ,pay_action_information pai_iep45
3713: ,pay_assignment_actions paa
3714: ,per_time_periods ptp
3715: WHERE paa.payroll_action_id = c_p45_arch_id
3716: AND paa.assignment_action_id = pai_iep45.action_context_id
3717: AND pai_iep45.action_context_type ='AAP'
3722: AND pai_ed.action_information_category = 'EMPLOYEE DETAILS'
3723: AND paa.assignment_id = NVL(p_assignment_id,paa.assignment_id)
3724: ORDER BY pai_iep45.action_information2;
3725: --
3726: CURSOR cur_p45_employer_no (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3727: SELECT pai_epif.action_information6 employer_number
3728: FROM pay_assignment_actions paa
3729: ,pay_action_interlocks pai_arc
3730: ,pay_assignment_actions paa_payroll
3724: ORDER BY pai_iep45.action_information2;
3725: --
3726: CURSOR cur_p45_employer_no (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3727: SELECT pai_epif.action_information6 employer_number
3728: FROM pay_assignment_actions paa
3729: ,pay_action_interlocks pai_arc
3730: ,pay_assignment_actions paa_payroll
3731: ,pay_action_information pai_epif
3732: WHERE paa.assignment_action_id = p_assignment_action_id
3726: CURSOR cur_p45_employer_no (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3727: SELECT pai_epif.action_information6 employer_number
3728: FROM pay_assignment_actions paa
3729: ,pay_action_interlocks pai_arc
3730: ,pay_assignment_actions paa_payroll
3731: ,pay_action_information pai_epif
3732: WHERE paa.assignment_action_id = p_assignment_action_id
3733: AND paa.assignment_action_id = pai_arc.locking_action_id
3734: AND paa_payroll.assignment_action_id = pai_arc.locked_action_id
3736: AND pai_epif.action_context_type ='PA'
3737: AND pai_epif.action_information_category = 'EMEA PAYROLL INFO'
3738: AND pai_epif.action_information1 = paa_payroll.payroll_action_id;
3739: --
3740: CURSOR cur_p45_ie_emp_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3741: SELECT (round(to_number(substr(nvl(pai_ieed.action_information26,'0'), 1,30)),2)*100) taxcredit
3742: ,(round(to_number(substr(nvl(pai_ieed.action_information27,'0'), 1,30)),2)*100) cutoff
3743: ,substr(pai_ieed.action_information22, 1,30) prsi_class
3744: FROM pay_action_information pai_ieed
3747: AND pai_ieed.action_information_category = 'IE EMPLOYEE DETAILS';
3748: cur_p45_ie_emp_details_rec cur_p45_ie_emp_details%ROWTYPE;
3749: --
3750: --6615117
3751: CURSOR cur_p45_emp_soc_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3752: SELECT (round(to_number(nvl(emp_soc.disability_benefit,'0')),2)*100) benefit
3753: ,(round(to_number(nvl(emp_soc.red_tax_credit,'0')),2)*100) taxcreditreduction
3754: ,(round(to_number(nvl(emp_soc.red_std_cut_off,'0')),2)*100) cutoffreduction
3755: ,emp_soc.non_cummulative_basis noncumulative
3757: WHERE emp_soc.assignment_action_id = p_assignment_action_id;
3758: cur_p45_emp_soc_details_rec cur_p45_emp_soc_details%ROWTYPE;
3759: --
3760: --6615117
3761: CURSOR cur_p45_emp_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3762: SELECT emp_details.pps_no ppsn
3763: ,emp_details.last_name surname
3764: ,emp_details.first_name firstname
3765: ,emp_details.works_no works
3770: FROM pay_ie_p45_employee_details emp_details
3771: WHERE emp_details.assignment_action_id = p_assignment_action_id;
3772: cur_p45_emp_details_rec cur_p45_emp_details%ROWTYPE;
3773: --
3774: CURSOR cur_p45_emp_address (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3775: SELECT emp_address.address1 address1
3776: ,emp_address.address2 address2
3777: ,emp_address.address3 address3
3778: FROM pay_ie_p45_address_details emp_address
3778: FROM pay_ie_p45_address_details emp_address
3779: WHERE emp_address.assignment_action_id = p_assignment_action_id;
3780: cur_p45_emp_address_rec cur_p45_emp_address%ROWTYPE;
3781: --
3782: CURSOR cur_p45_paye_prsi_details (p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
3783: SELECT (round(to_number(emp_paye.total_tax),2)*100) totaltax
3784: ,(round(to_number(emp_paye.total_pay),2)*100) totalpay
3785: ,(round(to_number(emp_paye.this_tax),2)*100) thistax
3786: ,(round(to_number(emp_paye.this_pay),2)*100) thispay
3815: --
3816: CURSOR cur_p30_start_date(
3817: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
3818: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
3819: FROM pay_assignment_actions paa_p30,
3820: pay_action_interlocks pai_p30,
3821: pay_assignment_actions paa_arc,
3822: pay_payroll_actions ppa_arc
3823: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
3817: c_p30_data_lock_process pay_payroll_actions.payroll_action_id%TYPE) IS
3818: SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
3819: FROM pay_assignment_actions paa_p30,
3820: pay_action_interlocks pai_p30,
3821: pay_assignment_actions paa_arc,
3822: pay_payroll_actions ppa_arc
3823: WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
3824: AND paa_p30.assignment_action_id = pai_p30.locking_action_id
3825: AND paa_arc.assignment_action_id = pai_p30.locked_action_id