[Home] [Help]
262: where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
263: and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
264:
265: cursor csr_stnd_cond(c_asg_id number) is
266: select FREQUENCY , NORMAL_HOURS from per_all_assignments_f where assignment_id = c_asg_id and
267: l_effective_date between effective_start_date and effective_end_date;
268:
269: cursor csr_asg_start_date(c_asg_id number) is
270: select max(effective_start_date) from per_all_assignments_f
266: select FREQUENCY , NORMAL_HOURS from per_all_assignments_f where assignment_id = c_asg_id and
267: l_effective_date between effective_start_date and effective_end_date;
268:
269: cursor csr_asg_start_date(c_asg_id number) is
270: select max(effective_start_date) from per_all_assignments_f
271: where assignment_id = c_asg_id
272: and assignment_type = 'E'
273: and effective_start_date <= g_effective_date;
274:
275: -- to get the agg flag.
276: cursor csr_agg_flag(c_asg_id number) is
277: SELECT trim(nvl(pap.per_information10,'N')) per_agg_flag
278: FROM per_all_people_f pap,
279: per_all_assignments_f asg
280: WHERE asg.assignment_id = c_asg_id
281: AND pap.person_id = asg.person_id
282: AND asg.business_group_id = g_business_group_id
283: AND l_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
284: AND l_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
285:
286: cursor csr_asg is
287: select paaf.assignment_id
288: from per_all_assignments_f paaf,
289: pay_payrolls_f pay,
290: hr_soft_coding_keyflex sck
291: where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =p_assignment_id)
292: and pay.payroll_id = paaf.payroll_id
287: select paaf.assignment_id
288: from per_all_assignments_f paaf,
289: pay_payrolls_f pay,
290: hr_soft_coding_keyflex sck
291: where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =p_assignment_id)
292: and pay.payroll_id = paaf.payroll_id
293: and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
294: and sck.SEGMENT1 = g_tax_ref
295: and paaf.EFFECTIVE_START_DATE =
293: and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
294: and sck.SEGMENT1 = g_tax_ref
295: and paaf.EFFECTIVE_START_DATE =
296: ( select max(paaf1.effective_start_date)
297: from per_all_assignments_f paaf1
298: where paaf1.assignment_id = paaf.assignment_id
299: and paaf1.assignment_type = 'E'
300: and paaf1.effective_start_date <= l_effective_date
301: )
360: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
361: end;
362: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
363: l_sqlstr := 'select ' || l_column_name ||
364: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
365: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
366: begin
367: EXECUTE immediate l_sqlstr INTO l_hours_worked USING p_assignment_id,l_context_name;
368: exception
518: where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
519: and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
520:
521: cursor csr_asg_start_date is
522: select max(effective_start_date) from per_all_assignments_f
523: where assignment_id = p_assignment_id
524: and assignment_type = 'E'
525: and effective_start_date <= g_effective_date;
526:
587:
588: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
589:
590: l_sqlstr := 'select ' || l_column_name ||
591: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
592: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
593: begin
594: EXECUTE immediate l_sqlstr INTO l_periods_covered USING p_assignment_id,l_context_name;
595:
672: where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
673: and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
674:
675: cursor csr_asg_start_date is
676: select max(effective_start_date) from per_all_assignments_f
677: where assignment_id = p_assignment_id
678: and assignment_type = 'E'
679: and effective_start_date <= g_effective_date;
680:
741:
742: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
743:
744: l_sqlstr := 'select ' || l_column_name ||
745: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
746: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
747: begin
748: EXECUTE immediate l_sqlstr INTO l_irregular_payment USING p_assignment_id,l_context_name;
749:
828: where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
829: and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
830:
831: cursor csr_asg_start_date is
832: select max(effective_start_date) from per_all_assignments_f
833: where assignment_id = p_assignment_id
834: and assignment_type = 'E'
835: and effective_start_date <= g_effective_date;
836:
897:
898: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
899:
900: l_sqlstr := 'select ' || l_column_name ||
901: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
902: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
903: begin
904: EXECUTE immediate l_sqlstr INTO l_oneoff_payment USING p_assignment_id,l_context_name;
905:
977: l_effective_date date;
978:
979:
980: cursor csr_asg_start_date is
981: select max(effective_start_date) from per_all_assignments_f
982: where assignment_id = p_assignment_id
983: and assignment_type = 'E'
984: and effective_start_date <= g_effective_date;
985:
1039:
1040: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
1041:
1042: l_sqlstr := 'select ' || l_column_name ||
1043: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
1044: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
1045: begin
1046: EXECUTE immediate l_sqlstr INTO l_pay_non_indiv_ind USING p_assignment_id,l_context_name;
1047:
1119: l_effective_date date;
1120:
1121:
1122: cursor csr_asg_start_date is
1123: select max(effective_start_date) from per_all_assignments_f
1124: where assignment_id = p_assignment_id
1125: and assignment_type = 'E'
1126: and effective_start_date <= g_effective_date;
1127:
1181:
1182: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
1183:
1184: l_sqlstr := 'select ' || l_column_name ||
1185: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
1186: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
1187: begin
1188: EXECUTE immediate l_sqlstr INTO l_onstrike_ind USING p_assignment_id,l_context_name;
1189:
1260: l_frequency VARCHAR2(20);
1261: l_effective_date date;
1262:
1263: cursor csr_asg_start_date is
1264: select max(effective_start_date) from per_all_assignments_f
1265: where assignment_id = p_assignment_id
1266: and assignment_type = 'E'
1267: and effective_start_date <= g_effective_date;
1268:
1322:
1323: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
1324:
1325: l_sqlstr := 'select ' || l_column_name ||
1326: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
1327: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
1328: begin
1329: EXECUTE immediate l_sqlstr INTO l_unpaid_absence_ind USING p_assignment_id,l_context_name;
1330:
1765: SUBSTR(pap.sex,1,1) sex ,
1766: DECODE(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
1767: DECODE(pap.per_information9,'Y','Y',NULL) multiple_asg_flag
1768: FROM pay_assignment_actions act,
1769: per_all_assignments_f paa,
1770: per_all_people_f pap
1771: WHERE act.assignment_action_id = p_assactid
1772: AND act.assignment_id = paa.assignment_id
1773: AND paa.person_id = pap.person_id
1774: AND p_end_date between pap.effective_start_date and pap.effective_end_date
1775: AND paa.effective_start_date =
1776: (
1777: SELECT MAX(paa2.effective_start_date)
1778: FROM per_all_assignments_f paa2
1779: WHERE paa2.assignment_id = paa.assignment_id
1780: AND paa2.assignment_type = 'E'
1781: AND paa2.effective_start_date <= p_end_date
1782: );
1924: CURSOR csr_get_asg_numbers
1925: IS
1926: SELECT paaf.assignment_number assignment_number,
1927: paaf_old.assignment_number old_assignment_number
1928: FROM per_all_assignments_f paaf,
1929: per_all_assignments_f paaf_old
1930: WHERE paaf.assignment_id = p_person_rec.assignment_id
1931: AND ((paaf_old.assignment_id = paaf.assignment_id
1932: AND paaf_old.effective_start_date < paaf.effective_start_date
1925: IS
1926: SELECT paaf.assignment_number assignment_number,
1927: paaf_old.assignment_number old_assignment_number
1928: FROM per_all_assignments_f paaf,
1929: per_all_assignments_f paaf_old
1930: WHERE paaf.assignment_id = p_person_rec.assignment_id
1931: AND ((paaf_old.assignment_id = paaf.assignment_id
1932: AND paaf_old.effective_start_date < paaf.effective_start_date
1933: AND paaf.effective_start_date > g_start_year
2017: SELECT trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
2018: trim(NVL(pap.per_information10,'N')) per_agg_flag,
2019: asg.assignment_number assignment_number
2020: FROM per_all_people_f pap,
2021: per_all_assignments_f asg,
2022: pay_assignment_actions paa
2023: WHERE paa.assignment_action_id = c_asg_act_id
2024: AND paa.assignment_id = asg.assignment_id
2025: AND pap.person_id = asg.person_id
2027: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
2028: AND pap.per_information_category = 'GB';
2029: /*AND asg.EFFECTIVE_START_DATE =
2030: (SELECT MAX(EFFECTIVE_START_DATE)
2031: FROM per_all_assignments_f paaf
2032: WHERE paaf.assignment_id = asg.assignment_id
2033: );*/
2034:
2035: --Below cursor fetches the period start and end dates of payrolls processed in the current prepayment
2060: -- Cursor to fetch Termination date
2061: cursor csr_terminated_date is
2062: select serv.actual_termination_date actual_termination_date
2063: from
2064: per_all_assignments_f asg,
2065: per_periods_of_service serv
2066: where asg.assignment_id = p_person_rec.assignment_id
2067: and asg.period_of_service_id = serv.period_of_service_id
2068: and p_end_date between asg.effective_start_date and asg.effective_end_date
2072: --Below cursor fetches the termination date of the current assignment
2073: CURSOR csr_get_term_asg_info (p_asg_id NUMBER)
2074: IS
2075: SELECT min(paaf.effective_start_date) min_active_start_date
2076: FROM per_all_assignments_f paaf,
2077: per_assignment_status_types past
2078: WHERE paaf.assignment_id = p_asg_id
2079: AND paaf.assignment_status_type_id = past.assignment_status_type_id
2080: AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
2710: CURSOR csr_get_asg_numbers
2711: IS
2712: SELECT paaf.assignment_number assignment_number,
2713: paaf_old.assignment_number old_assignment_number
2714: FROM per_all_assignments_f paaf,
2715: per_all_assignments_f paaf_old
2716: WHERE paaf.assignment_id = p_person_rec.assignment_id
2717: AND ((paaf_old.assignment_id = paaf.assignment_id
2718: AND paaf_old.effective_start_date < paaf.effective_start_date
2711: IS
2712: SELECT paaf.assignment_number assignment_number,
2713: paaf_old.assignment_number old_assignment_number
2714: FROM per_all_assignments_f paaf,
2715: per_all_assignments_f paaf_old
2716: WHERE paaf.assignment_id = p_person_rec.assignment_id
2717: AND ((paaf_old.assignment_id = paaf.assignment_id
2718: AND paaf_old.effective_start_date < paaf.effective_start_date
2719: AND paaf.effective_start_date > g_start_year
2799: SELECT trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
2800: trim(NVL(pap.per_information10,'N')) per_agg_flag,
2801: asg.assignment_number assignment_number
2802: FROM per_all_people_f pap,
2803: per_all_assignments_f asg
2804: WHERE asg.assignment_id = c_asg_act_id
2805: AND pap.person_id = asg.person_id
2806: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2807: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
2836: -- Cursor to fetch Termination date
2837: cursor csr_terminated_date is
2838: select serv.actual_termination_date actual_termination_date
2839: from
2840: per_all_assignments_f asg,
2841: per_periods_of_service serv
2842: where asg.assignment_id = p_person_rec.assignment_id
2843: and asg.period_of_service_id = serv.period_of_service_id
2844: and p_end_date between asg.effective_start_date and asg.effective_end_date
2848: --Below cursor fetches the termination date of the current assignment
2849: CURSOR csr_get_term_asg_info (p_asg_id NUMBER)
2850: IS
2851: SELECT min(paaf.effective_start_date) min_active_start_date
2852: FROM per_all_assignments_f paaf,
2853: per_assignment_status_types past
2854: WHERE paaf.assignment_id = p_asg_id
2855: AND paaf.assignment_status_type_id = past.assignment_status_type_id
2856: AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
2924: /*+ USE_NL(paa, pact, ptp) */
2925: to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
2926: || paa.assignment_action_id),16)) max_asg_act_id,
2927: MAX(pact.effective_date) effective_date
2928: FROM per_all_assignments_f paaf,
2929: pay_assignment_actions paa,
2930: pay_payroll_actions pact,
2931: per_time_periods ptp,
2932: pay_assignment_actions paa1, --Prepayments
3512: CURSOR csr_get_asg_numbers
3513: IS
3514: SELECT paaf.assignment_number assignment_number,
3515: paaf_old.assignment_number old_assignment_number
3516: FROM per_all_assignments_f paaf,
3517: per_all_assignments_f paaf_old
3518: WHERE paaf.assignment_id = l_asg_id
3519: AND ((paaf_old.assignment_id = paaf.assignment_id
3520: AND paaf_old.effective_start_date < paaf.effective_start_date
3513: IS
3514: SELECT paaf.assignment_number assignment_number,
3515: paaf_old.assignment_number old_assignment_number
3516: FROM per_all_assignments_f paaf,
3517: per_all_assignments_f paaf_old
3518: WHERE paaf.assignment_id = l_asg_id
3519: AND ((paaf_old.assignment_id = paaf.assignment_id
3520: AND paaf_old.effective_start_date < paaf.effective_start_date
3521: AND paaf.effective_start_date > g_start_year
3596: trim(NVL(pap.per_information10,'N')) per_agg_flag,
3597: asg.assignment_number assignment_number,
3598: asg.assignment_id assignment_id
3599: FROM per_all_people_f pap,
3600: per_all_assignments_f asg,
3601: pay_assignment_actions paa
3602: WHERE paa.assignment_action_id = c_asg_act_id
3603: AND paa.assignment_id = asg.assignment_id
3604: AND pap.person_id = asg.person_id
3606: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
3607: AND pap.per_information_category = 'GB';
3608: /*AND asg.EFFECTIVE_START_DATE =
3609: (SELECT MAX(EFFECTIVE_START_DATE)
3610: FROM per_all_assignments_f paaf
3611: WHERE paaf.assignment_id = asg.assignment_id
3612: );*/
3613:
3614: --Below cursor fetches the period start and end dates of payrolls processed in the current prepayment
3639: -- Cursor to fetch Termination date
3640: cursor csr_terminated_date is
3641: select serv.actual_termination_date actual_termination_date
3642: from
3643: per_all_assignments_f asg,
3644: per_periods_of_service serv
3645: where asg.assignment_id = l_asg_id
3646: and asg.period_of_service_id = serv.period_of_service_id
3647: and p_end_date between asg.effective_start_date and asg.effective_end_date
3651: --Below cursor fetches the termination date of the current assignment
3652: CURSOR csr_get_term_asg_info (p_asg_id NUMBER)
3653: IS
3654: SELECT min(paaf.effective_start_date) min_active_start_date
3655: FROM per_all_assignments_f paaf,
3656: per_assignment_status_types past
3657: WHERE paaf.assignment_id = p_asg_id
3658: AND paaf.assignment_status_type_id = past.assignment_status_type_id
3659: AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
4351: return date is
4352: l_eff_date date;
4353: cursor csr_get_start_date is
4354: select paaf.effective_start_date from
4355: per_all_assignments_f paaf,
4356: pay_all_payrolls_f papf ,
4357: hr_soft_coding_keyflex flex,
4358: per_assignment_status_types past
4359: where paaf.assignment_id = asg_id and paaf.effective_end_date = eff_date - 1
4450:
4451: cursor csr_terminated_emp is
4452: select serv.actual_termination_date actual_termination_date
4453: from
4454: per_all_assignments_f asg,
4455: per_periods_of_service serv
4456: where asg.assignment_id = p_asg_id
4457: and serv.actual_termination_date is not null
4458: and asg.period_of_service_id = serv.period_of_service_id
4460: and actual_termination_date <= sysdate + 30 ;
4461:
4462: cursor csr_get_start_date is
4463: select paaf.effective_start_date,paaf.effective_end_date
4464: from per_all_assignments_f paaf
4465: where paaf.assignment_id = p_asg_id
4466: and p_payment_date between paaf.effective_start_date and paaf.effective_end_date;
4467:
4468: cursor csr_get_starter is
4548: IS
4549: SELECT trim(asg.primary_flag) asg_primary_flag,
4550: trim(pap.per_information10) per_agg_flag
4551: FROM per_all_people_f pap,
4552: per_all_assignments_f asg
4553: WHERE asg.assignment_id = c_asg_id
4554: AND pap.person_id = asg.person_id
4555: AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4556: AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
4821: IS
4822: SELECT trim(asg.primary_flag) asg_primary_flag,
4823: trim(pap.per_information10) per_agg_flag
4824: FROM per_all_people_f pap,
4825: per_all_assignments_f asg
4826: WHERE asg.assignment_id = c_asg_id
4827: AND pap.person_id = asg.person_id
4828: AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4829: AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
5071: IS
5072: SELECT trim(asg.primary_flag) asg_primary_flag,
5073: trim(pap.per_information10) per_agg_flag
5074: FROM per_all_people_f pap,
5075: per_all_assignments_f asg
5076: WHERE asg.assignment_id = c_asg_id
5077: AND pap.person_id = asg.person_id
5078: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
5079: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
5753: IS
5754: SELECT trim(asg.primary_flag) asg_primary_flag,
5755: trim(pap.per_information10) per_agg_flag
5756: FROM per_all_people_f pap,
5757: per_all_assignments_f asg
5758: WHERE asg.assignment_id = c_asg_id
5759: AND pap.person_id = asg.person_id
5760: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
5761: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
6780: fnd_file.put_line (fnd_file.output,'Error : Date Scheme Ceased should be within Tax Year.');
6781: RAISE date_scheme_error;
6782: end if;
6783:
6784: sqlstr := 'select distinct PERSON_ID '|| 'from per_all_assignments_f paaf, '
6785: || 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '
6786: || 'and ppa.business_group_id = paaf.business_group_id '|| ' and paaf.payroll_id = '
6787: ||''''||l_payroll_id||''''|| ' order by person_id';
6788:
6828: l_exist NUMBER;
6829: l_first_fps VARCHAR2(1);
6830: l_first_fps_run_date date;
6831: l_first_fps_eff_date date;
6832: l_asg_number per_all_assignments_f.assignment_number%type;
6833: CURSOR csr_parameter_info
6834: IS
6835: SELECT to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
6836: SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
6848: trim(nvl(pap.per_information10,'N')) per_agg_flag,
6849: trim(nvl(pap.per_information9,'N')) ni_agg_flag,
6850: asg.assignment_number
6851: FROM per_all_people_f pap,
6852: per_all_assignments_f asg
6853: WHERE asg.assignment_id = c_asg_id
6854: AND pap.person_id = asg.person_id
6855: AND asg.business_group_id = l_business_group_id
6856: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
6870: pay_payroll_actions appa2, --Prepayments
6871: pay_assignment_actions act, --Payroll Run
6872: pay_assignment_actions act1, --Prepayments
6873: pay_action_interlocks pai, --Prepayments
6874: per_all_assignments_f as1
6875: WHERE ppa.payroll_action_id = pactid
6876: AND as1.person_id BETWEEN stperson AND endperson
6877: AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
6878: AND act.payroll_action_id = appa.payroll_action_id
6913: trim(paaf.primary_flag) asg_primary_flag,
6914: trim(pap.per_information10) per_agg_flag,
6915: pap.person_id
6916: from per_all_people_f pap,
6917: per_all_assignments_f paaf,
6918: per_assignment_status_types past,
6919: pay_payroll_actions paa
6920: where pap.person_id between stperson and endperson
6921: and paa.payroll_Action_id = p_prepay_id
6932: and pap2.effective_start_date <= p_prepayment_date
6933: )
6934: and paaf.effective_start_date =
6935: ( select max(asg2.effective_start_date)
6936: from per_all_assignments_f asg2
6937: where asg2.assignment_id = paaf.assignment_id
6938: and asg2.assignment_type = 'E'
6939: and asg2.effective_start_date <= p_prepayment_date
6940: )
6998: pay_payroll_actions appa2, --Prepayments
6999: pay_assignment_actions act, --Payroll Run
7000: pay_assignment_actions act1, --Prepayments
7001: pay_action_interlocks pai, --Prepayments
7002: per_all_assignments_f as1
7003: WHERE as1.person_id = p_person_id --45885
7004: AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
7005: AND act.payroll_action_id = appa.payroll_action_id
7006: AND act.source_action_id IS NULL
7033: pay_payroll_actions appa2, --Prepayments
7034: pay_assignment_actions act, --Payroll Run
7035: pay_assignment_actions act1, --Prepayments
7036: pay_action_interlocks pai, --Prepayments
7037: per_all_assignments_f as1
7038: WHERE as1.person_id = p_person_id --45885
7039: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
7040: AND act.payroll_action_id = appa.payroll_action_id
7041: AND act.source_action_id IS NULL
7052: AND appa2.payroll_action_id = p_pre_pact_id;
7053:
7054: l_person_id number;
7055: cursor csr_get_other_asg(c_asg_id number) is
7056: select paaf.assignment_id asg_id from per_all_assignments_f paaf where
7057: paaf.person_id = l_person_id
7058: and paaf.assignment_id <> c_asg_id
7059: and l_effective_date between paaf.effective_start_date and paaf.effective_end_date;
7060:
7072: /* ,max (pact.effective_date) effective_date
7073: ,max(paa1.assignment_action_id)*/
7074: FROM pay_assignment_actions paa
7075: ,pay_assignment_actions paa1
7076: ,per_all_assignments_f paaf
7077: ,pay_payroll_actions pact
7078: ,pay_payroll_actions pact1
7079: ,per_time_periods ptp
7080: ,pay_action_interlocks pai
7130: ,max (pact.effective_date) effective_date*/
7131: distinct max(paa1.assignment_action_id) max_pre_act_id
7132: FROM pay_assignment_actions paa
7133: ,pay_assignment_actions paa1
7134: ,per_all_assignments_f paaf
7135: ,pay_payroll_actions pact
7136: ,pay_payroll_actions pact1
7137: ,per_time_periods ptp
7138: ,pay_action_interlocks pai
7214: -- Cursor to fetch the RTI NI Reporting assignment for this person.
7215: cursor csr_get_ni_rpt_asg(c_asg_id number) is
7216: select
7217: paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
7218: from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei
7219: where paaf1.assignment_id = c_asg_id
7220: and paaf1.person_id = paaf2.person_id
7221: and paaf2.ASSIGNMENT_TYPE = 'E'
7222:
7264: CURSOR csr_aggr_asgs_processed(p_person_id NUMBER, p_pre_pay_id NUMBER, p_start_year DATE, p_end_year DATE)
7265: IS
7266: SELECT count(distinct paa1.assignment_id)
7267: FROM pay_assignment_actions paa,
7268: per_all_assignments_f paaf,
7269: pay_payroll_actions pact,
7270: per_time_periods ptp,
7271: pay_assignment_actions paa1, --Prepayments
7272: pay_payroll_actions ppa1, --Prepayments
7291: pay_payroll_actions appa2, --Prepayments
7292: pay_assignment_actions act, --Payroll Run
7293: pay_assignment_actions act1, --Prepayments
7294: pay_action_interlocks pai, --Prepayments
7295: per_all_assignments_f as1,
7296: pay_all_payrolls_f papf ,
7297: hr_soft_coding_keyflex flex
7298: WHERE as1.person_id = p_person_id --58105
7299: AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
8567: CURSOR csr_asg_basic_details(c_asg_id NUMBER)
8568: IS
8569: SELECT asg.EFFECTIVE_START_DATE asg_eff_start_date,
8570: asg.person_id
8571: FROM per_all_assignments_f asg
8572: WHERE asg.assignment_id = c_asg_id
8573: AND g_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
8574:
8575: -- for ni only aggregation
8781: select min(paa2.assignment_action_id) asgactid
8782: from
8783: pay_assignment_actions paa1,
8784: pay_assignment_actions paa2,
8785: per_all_assignments_f asg
8786: where
8787: paa1.assignment_action_id = p_assactid
8788: and paa1.payroll_action_id = paa2.payroll_action_id
8789: and paa2.assignment_id = asg.assignment_id
8795: SELECT asg.assignment_id,
8796: asg.EFFECTIVE_START_DATE asg_eff_start_date,
8797: asg.person_id,
8798: asg.assignment_number
8799: FROM per_all_assignments_f asg,
8800: pay_assignment_actions paa,
8801: pay_payroll_actions ppa
8802: WHERE paa.assignment_action_id = c_asg_act_id
8803: AND paa.assignment_id = asg.assignment_id
8813: trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
8814: trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
8815: asg.payroll_id payroll_id
8816: FROM per_all_people_f pap,
8817: per_all_assignments_f asg,
8818: pay_assignment_actions paa
8819: WHERE paa.assignment_action_id = c_asg_act_id
8820: AND paa.assignment_id = asg.assignment_id
8821: AND pap.person_id = asg.person_id
9007: to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
9008: || paa.assignment_action_id),16)) max_asg_act_id,
9009: MAX(pact.effective_date) effective_date
9010: FROM pay_assignment_actions paa,
9011: per_all_assignments_f paaf,
9012: pay_payroll_actions pact,
9013: per_time_periods ptp,
9014: pay_assignment_actions paa1, --Prepayments
9015: pay_payroll_actions ppa1, --Prepayments
9048: to_number (substr (max (lpad (paa.action_sequence ,15,'0')
9049: || paa.assignment_action_id),16)) max_asg_act_id
9050: ,max (pact.effective_date) effective_date
9051: FROM pay_assignment_actions paa
9052: ,per_all_assignments_f paaf
9053: ,pay_payroll_actions pact
9054: ,per_time_periods ptp
9055: WHERE paa.assignment_id = paaf.assignment_id
9056: AND paaf.person_id = p_person_id
9100: SELECT min (act.assignment_action_id)
9101: FROM pay_action_classifications class
9102: ,pay_payroll_actions pact
9103: ,pay_assignment_actions act
9104: ,per_all_assignments_f ass
9105: ,per_periods_of_service pos
9106: WHERE pos.person_id = p_person_id
9107: AND ass.period_of_service_id = pos.period_of_service_id
9108: AND act.assignment_id = ass.assignment_id
9146: -- Cursor to fetch the RTI NI Reporting assignment for this person.
9147: cursor csr_get_ni_rpt_asg(c_asg_id number) is
9148: select
9149: paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
9150: from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei
9151: where paaf1.assignment_id = c_asg_id
9152: and paaf1.person_id = paaf2.person_id
9153: and paaf2.ASSIGNMENT_TYPE = 'E'
9154:
9206: and get the YTD balance value for these action ids.
9207: Sum this with the current assignment NI YTD and then archive.
9208: ***/
9209: cursor csr_asg_act_oth_asgs(c_asg_id number) is
9210: select max(paa.assignment_action_id) asg_act_id from per_all_assignments_f paaf,
9211: per_all_assignments_f paaf1,
9212: pay_assignment_actions paa,
9213: pay_payroll_actions ppa
9214: where paaf.assignment_id = c_asg_id
9207: Sum this with the current assignment NI YTD and then archive.
9208: ***/
9209: cursor csr_asg_act_oth_asgs(c_asg_id number) is
9210: select max(paa.assignment_action_id) asg_act_id from per_all_assignments_f paaf,
9211: per_all_assignments_f paaf1,
9212: pay_assignment_actions paa,
9213: pay_payroll_actions ppa
9214: where paaf.assignment_id = c_asg_id
9215: and paaf1.person_id = paaf.person_id
9307: AND g_end_year;
9308:
9309: cursor csr_get_asg_no(p_asg_id number,p_effective_date date) is
9310: SELECT assignment_number
9311: FROM per_all_assignments_f
9312: WHERE assignment_id = p_asg_id
9313: AND p_effective_date BETWEEN effective_start_date
9314: AND effective_end_date
9315: ORDER BY effective_start_date;
9315: ORDER BY effective_start_date;
9316:
9317: cursor csr_get_latest_asg_no(p_asg_id number,p_effective_date date) is
9318: SELECT assignment_number
9319: FROM per_all_assignments_f
9320: WHERE assignment_id = p_asg_id
9321: ORDER BY effective_start_date;
9322:
9323: cursor csr_asg_act_processed_earlier(c_person_id number,
9353: pay_payroll_actions appa2, --Prepayments
9354: pay_assignment_actions act, --Payroll Run
9355: pay_assignment_actions act1, --Prepayments
9356: pay_action_interlocks pai, --Prepayments
9357: per_all_assignments_f as1
9358: WHERE as1.person_id = p_person_id --45885
9359: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
9360: AND act.payroll_action_id = appa.payroll_action_id
9361: AND act.source_action_id IS NULL
9380: pay_payroll_actions appa2, --Prepayments
9381: pay_assignment_actions act, --Payroll Run
9382: pay_assignment_actions act1, --Prepayments
9383: pay_action_interlocks pai, --Prepayments
9384: per_all_assignments_f as1
9385: WHERE as1.person_id = p_person_id --45885
9386: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
9387: AND act.payroll_action_id = appa.payroll_action_id
9388: AND act.source_action_id IS NULL
9406: pay_payroll_actions appa2, --Prepayments
9407: pay_assignment_actions act, --Payroll Run
9408: pay_assignment_actions act1, --Prepayments
9409: pay_action_interlocks pai, --Prepayments
9410: per_all_assignments_f as1
9411: WHERE
9412: ppa.payroll_action_id = g_payroll_action_id
9413: AND as1.person_id = c_person_id --BETWEEN stperson AND endperson
9414: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
9445: pay_payroll_actions appa2, --Prepayments
9446: pay_assignment_actions act, --Payroll Run
9447: pay_assignment_actions act1, --Prepayments
9448: pay_action_interlocks pai, --Prepayments
9449: per_all_assignments_f as1
9450: WHERE as1.person_id = p_person_id --45885
9451: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
9452: AND act.payroll_action_id = appa.payroll_action_id
9453: AND act.source_action_id IS NULL
9690: CURSOR csr_aggr_asgs_processed(p_person_id NUMBER, p_pre_pay_id NUMBER, p_start_year DATE, p_end_year DATE)
9691: IS
9692: SELECT count(distinct paa1.assignment_id)
9693: FROM pay_assignment_actions paa,
9694: per_all_assignments_f paaf,
9695: pay_payroll_actions pact,
9696: per_time_periods ptp,
9697: pay_assignment_actions paa1, --Prepayments
9698: pay_payroll_actions ppa1, --Prepayments
9726: , '0')
9727: || paa.assignment_action_id), 16))
9728: , max (pact.effective_date) effective_date
9729:
9730: FROM per_all_assignments_f paaf,
9731: pay_assignment_actions paa
9732: , pay_payroll_actions pact
9733: , per_time_periods ptp
9734: WHERE paa.assignment_id = paaf.assignment_id
9742: BETWEEN g_start_year
9743: AND l_first_fps_eff_date
9744: AND paaf.effective_start_date =
9745: ( select max(asg2.effective_start_date)
9746: from per_all_assignments_f asg2
9747: where asg2.assignment_id = paaf.assignment_id
9748: and asg2.assignment_type = 'E'
9749: and asg2.effective_start_date <= l_prepayment_date
9750: );
9752: --Below cursor fetches the basic details of the current assignment
9753: CURSOR csr_asg_num(c_asg_act_id NUMBER, c_cur_last_effective_date date)
9754: IS
9755: SELECT asg.assignment_number
9756: FROM per_all_assignments_f asg,
9757: pay_assignment_actions paa
9758: WHERE paa.assignment_action_id = c_asg_act_id
9759: AND paa.assignment_id = asg.assignment_id
9760: AND c_cur_last_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
10868: -- should skip the aggregation logic for other assignments
10869: select max(paa.assignment_action_id) into l_asg_action_id
10870: from
10871: pay_assignment_actions paa,
10872: per_all_assignments_f paaf
10873: where
10874: paaf.person_id = l_person_id
10875: and paaf.assignment_id = paa.assignment_id
10876: and paa.payroll_action_id = g_payroll_action_id
13416: NVL(pap.employee_number,' ') employee_number,
13417: TO_CHAR(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
13418: paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
13419: pap.person_id
13420: FROM per_all_assignments_f paa,
13421: per_assignment_status_types past,
13422: per_all_people_f pap
13423: WHERE paa.person_id = pap.person_id
13424: AND paa.assignment_id = c_assignment_id
13431: and pap2.effective_start_date <= l_effective_date
13432: )
13433: and paa.effective_start_date =
13434: ( select max(asg2.effective_start_date)
13435: from per_all_assignments_f asg2
13436: where asg2.assignment_id = paa.assignment_id
13437: and asg2.assignment_type = 'E'
13438: and ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
13439: and asg2.effective_start_date <= l_effective_date
13595: IS
13596: SELECT COUNT(DISTINCT(paaf.person_id))
13597: FROM pay_payroll_actions ppa,
13598: pay_assignment_actions paa,
13599: per_all_assignments_f paaf
13600: WHERE ppa.payroll_action_id = pactid -- pact_id
13601: AND paa.payroll_action_id = ppa.payroll_action_id
13602: AND paa.action_status = 'C'
13603: AND paaf.assignment_id = paa.assignment_id;