[Home] [Help]
387: where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
388: and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
389:
390: cursor csr_stnd_cond(c_asg_id number) is
391: select FREQUENCY , NORMAL_HOURS from per_all_assignments_f where assignment_id = c_asg_id and
392: l_effective_date between effective_start_date and effective_end_date;
393:
394: cursor csr_asg_start_date(c_asg_id number) is
395: select max(effective_start_date) from per_all_assignments_f
391: select FREQUENCY , NORMAL_HOURS from per_all_assignments_f where assignment_id = c_asg_id and
392: l_effective_date between effective_start_date and effective_end_date;
393:
394: cursor csr_asg_start_date(c_asg_id number) is
395: select max(effective_start_date) from per_all_assignments_f
396: where assignment_id = c_asg_id
397: and assignment_type = 'E'
398: and effective_start_date <= g_effective_date;
399:
400: -- to get the agg flag.
401: cursor csr_agg_flag(c_asg_id number) is
402: SELECT trim(nvl(pap.per_information10,'N')) per_agg_flag
403: FROM per_all_people_f pap,
404: per_all_assignments_f asg
405: WHERE asg.assignment_id = c_asg_id
406: AND pap.person_id = asg.person_id
407: AND asg.business_group_id = g_business_group_id
408: AND l_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
409: AND l_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
410:
411: cursor csr_asg is
412: select paaf.assignment_id
413: from per_all_assignments_f paaf,
414: pay_payrolls_f pay,
415: hr_soft_coding_keyflex sck
416: where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =p_assignment_id)
417: and pay.payroll_id = paaf.payroll_id
412: select paaf.assignment_id
413: from per_all_assignments_f paaf,
414: pay_payrolls_f pay,
415: hr_soft_coding_keyflex sck
416: where paaf.person_id = (select distinct person_id from per_all_assignments_f where ASSIGNMENT_ID =p_assignment_id)
417: and pay.payroll_id = paaf.payroll_id
418: and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
419: and sck.SEGMENT1 = g_tax_ref
420: and paaf.EFFECTIVE_START_DATE =
418: and pay.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
419: and sck.SEGMENT1 = g_tax_ref
420: and paaf.EFFECTIVE_START_DATE =
421: ( select max(paaf1.effective_start_date)
422: from per_all_assignments_f paaf1
423: where paaf1.assignment_id = paaf.assignment_id
424: and paaf1.assignment_type = 'E'
425: and paaf1.effective_start_date <= l_effective_date
426: )
485: hr_utility.trace(SQLCODE || ' - ' || sqlerrm );
486: end;
487: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
488: l_sqlstr := 'select ' || l_column_name ||
489: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
490: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
491: begin
492: EXECUTE immediate l_sqlstr INTO l_hours_worked USING p_assignment_id,l_context_name;
493: exception
643: where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
644: and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
645:
646: cursor csr_asg_start_date is
647: select max(effective_start_date) from per_all_assignments_f
648: where assignment_id = p_assignment_id
649: and assignment_type = 'E'
650: and effective_start_date <= g_effective_date;
651:
712:
713: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
714:
715: l_sqlstr := 'select ' || l_column_name ||
716: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
717: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
718: begin
719: EXECUTE immediate l_sqlstr INTO l_periods_covered USING p_assignment_id,l_context_name;
720:
797: where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
798: and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
799:
800: cursor csr_asg_start_date is
801: select max(effective_start_date) from per_all_assignments_f
802: where assignment_id = p_assignment_id
803: and assignment_type = 'E'
804: and effective_start_date <= g_effective_date;
805:
866:
867: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
868:
869: l_sqlstr := 'select ' || l_column_name ||
870: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
871: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
872: begin
873: EXECUTE immediate l_sqlstr INTO l_irregular_payment USING p_assignment_id,l_context_name;
874:
953: where assignment_id = p_assignment_id and INFORMATION_TYPE = 'GB_PAY_RTI'
954: and AEI_INFORMATION_CATEGORY = 'GB_PAY_RTI';
955:
956: cursor csr_asg_start_date is
957: select max(effective_start_date) from per_all_assignments_f
958: where assignment_id = p_assignment_id
959: and assignment_type = 'E'
960: and effective_start_date <= g_effective_date;
961:
1022:
1023: elsif l_source_name = 'PER_ASSIGNMENTS' THEN
1024:
1025: l_sqlstr := 'select ' || l_column_name ||
1026: ' from per_all_assignments_f where assignment_id = :assignment_id and ASS_ATTRIBUTE_CATEGORY = :l_context_name'||
1027: ' and '||l_effective_date||' between effective_start_date and effective_end_date' ;
1028: begin
1029: EXECUTE immediate l_sqlstr INTO l_oneoff_payment USING p_assignment_id,l_context_name;
1030:
1255: SUBSTR(pap.sex,1,1) sex ,
1256: DECODE(pap.per_information10,'Y','Y',NULL) agg_paye_flag,
1257: DECODE(pap.per_information9,'Y','Y',NULL) multiple_asg_flag
1258: FROM pay_assignment_actions act,
1259: per_all_assignments_f paa,
1260: per_all_people_f pap
1261: WHERE act.assignment_action_id = p_assactid
1262: AND act.assignment_id = paa.assignment_id
1263: AND paa.person_id = pap.person_id
1264: AND p_end_date between pap.effective_start_date and pap.effective_end_date
1265: AND paa.effective_start_date =
1266: (
1267: SELECT MAX(paa2.effective_start_date)
1268: FROM per_all_assignments_f paa2
1269: WHERE paa2.assignment_id = paa.assignment_id
1270: AND paa2.assignment_type = 'E'
1271: AND paa2.effective_start_date <= p_end_date
1272: );
1414: CURSOR csr_get_asg_numbers
1415: IS
1416: SELECT paaf.assignment_number assignment_number,
1417: paaf_old.assignment_number old_assignment_number
1418: FROM per_all_assignments_f paaf,
1419: per_all_assignments_f paaf_old
1420: WHERE paaf.assignment_id = p_person_rec.assignment_id
1421: AND ((paaf_old.assignment_id = paaf.assignment_id
1422: AND paaf_old.effective_start_date < paaf.effective_start_date
1415: IS
1416: SELECT paaf.assignment_number assignment_number,
1417: paaf_old.assignment_number old_assignment_number
1418: FROM per_all_assignments_f paaf,
1419: per_all_assignments_f paaf_old
1420: WHERE paaf.assignment_id = p_person_rec.assignment_id
1421: AND ((paaf_old.assignment_id = paaf.assignment_id
1422: AND paaf_old.effective_start_date < paaf.effective_start_date
1423: AND paaf.effective_start_date > g_start_year
1507: SELECT trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
1508: trim(NVL(pap.per_information10,'N')) per_agg_flag,
1509: asg.assignment_number assignment_number
1510: FROM per_all_people_f pap,
1511: per_all_assignments_f asg,
1512: pay_assignment_actions paa
1513: WHERE paa.assignment_action_id = c_asg_act_id
1514: AND paa.assignment_id = asg.assignment_id
1515: AND pap.person_id = asg.person_id
1517: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
1518: AND pap.per_information_category = 'GB';
1519: /*AND asg.EFFECTIVE_START_DATE =
1520: (SELECT MAX(EFFECTIVE_START_DATE)
1521: FROM per_all_assignments_f paaf
1522: WHERE paaf.assignment_id = asg.assignment_id
1523: );*/
1524:
1525: --Below cursor fetches the period start and end dates of payrolls processed in the current prepayment
1550: -- Cursor to fetch Termination date
1551: cursor csr_terminated_date is
1552: select serv.actual_termination_date actual_termination_date
1553: from
1554: per_all_assignments_f asg,
1555: per_periods_of_service serv
1556: where asg.assignment_id = p_person_rec.assignment_id
1557: and asg.period_of_service_id = serv.period_of_service_id
1558: and p_end_date between asg.effective_start_date and asg.effective_end_date
1562: --Below cursor fetches the termination date of the current assignment
1563: CURSOR csr_get_term_asg_info (p_asg_id NUMBER)
1564: IS
1565: SELECT min(paaf.effective_start_date) min_active_start_date
1566: FROM per_all_assignments_f paaf,
1567: per_assignment_status_types past,
1568: pay_all_payrolls_f pay,
1569: hr_soft_coding_keyflex sck
1570: WHERE paaf.assignment_id = p_asg_id
2232: CURSOR csr_get_asg_numbers
2233: IS
2234: SELECT paaf.assignment_number assignment_number,
2235: paaf_old.assignment_number old_assignment_number
2236: FROM per_all_assignments_f paaf,
2237: per_all_assignments_f paaf_old
2238: WHERE paaf.assignment_id = p_person_rec.assignment_id
2239: AND ((paaf_old.assignment_id = paaf.assignment_id
2240: AND paaf_old.effective_start_date < paaf.effective_start_date
2233: IS
2234: SELECT paaf.assignment_number assignment_number,
2235: paaf_old.assignment_number old_assignment_number
2236: FROM per_all_assignments_f paaf,
2237: per_all_assignments_f paaf_old
2238: WHERE paaf.assignment_id = p_person_rec.assignment_id
2239: AND ((paaf_old.assignment_id = paaf.assignment_id
2240: AND paaf_old.effective_start_date < paaf.effective_start_date
2241: AND paaf.effective_start_date > g_start_year
2321: SELECT trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
2322: trim(NVL(pap.per_information10,'N')) per_agg_flag,
2323: asg.assignment_number assignment_number
2324: FROM per_all_people_f pap,
2325: per_all_assignments_f asg
2326: WHERE asg.assignment_id = c_asg_act_id
2327: AND pap.person_id = asg.person_id
2328: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2329: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
2358: -- Cursor to fetch Termination date
2359: cursor csr_terminated_date is
2360: select serv.actual_termination_date actual_termination_date
2361: from
2362: per_all_assignments_f asg,
2363: per_periods_of_service serv
2364: where asg.assignment_id = p_person_rec.assignment_id
2365: and asg.period_of_service_id = serv.period_of_service_id
2366: and p_end_date between asg.effective_start_date and asg.effective_end_date
2370: --Below cursor fetches the termination date of the current assignment
2371: CURSOR csr_get_term_asg_info (p_asg_id NUMBER)
2372: IS
2373: SELECT min(paaf.effective_start_date) min_active_start_date
2374: FROM per_all_assignments_f paaf,
2375: per_assignment_status_types past,
2376: pay_all_payrolls_f pay,
2377: hr_soft_coding_keyflex sck
2378: WHERE paaf.assignment_id = p_asg_id
2451: /*+ USE_NL(paa, pact, ptp) */
2452: to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
2453: || paa.assignment_action_id),16)) max_asg_act_id,
2454: MAX(pact.effective_date) effective_date
2455: FROM per_all_assignments_f paaf,
2456: pay_assignment_actions paa,
2457: pay_payroll_actions pact,
2458: per_time_periods ptp,
2459: pay_assignment_actions paa1, --Prepayments
3045: CURSOR csr_get_asg_numbers
3046: IS
3047: SELECT paaf.assignment_number assignment_number,
3048: paaf_old.assignment_number old_assignment_number
3049: FROM per_all_assignments_f paaf,
3050: per_all_assignments_f paaf_old
3051: WHERE paaf.assignment_id = l_asg_id
3052: AND ((paaf_old.assignment_id = paaf.assignment_id
3053: AND paaf_old.effective_start_date < paaf.effective_start_date
3046: IS
3047: SELECT paaf.assignment_number assignment_number,
3048: paaf_old.assignment_number old_assignment_number
3049: FROM per_all_assignments_f paaf,
3050: per_all_assignments_f paaf_old
3051: WHERE paaf.assignment_id = l_asg_id
3052: AND ((paaf_old.assignment_id = paaf.assignment_id
3053: AND paaf_old.effective_start_date < paaf.effective_start_date
3054: AND paaf.effective_start_date > g_start_year
3129: trim(NVL(pap.per_information10,'N')) per_agg_flag,
3130: asg.assignment_number assignment_number,
3131: asg.assignment_id assignment_id
3132: FROM per_all_people_f pap,
3133: per_all_assignments_f asg,
3134: pay_assignment_actions paa
3135: WHERE paa.assignment_action_id = c_asg_act_id
3136: AND paa.assignment_id = asg.assignment_id
3137: AND pap.person_id = asg.person_id
3139: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
3140: AND pap.per_information_category = 'GB';
3141: /*AND asg.EFFECTIVE_START_DATE =
3142: (SELECT MAX(EFFECTIVE_START_DATE)
3143: FROM per_all_assignments_f paaf
3144: WHERE paaf.assignment_id = asg.assignment_id
3145: );*/
3146:
3147: --Below cursor fetches the period start and end dates of payrolls processed in the current prepayment
3172: -- Cursor to fetch Termination date
3173: cursor csr_terminated_date is
3174: select serv.actual_termination_date actual_termination_date
3175: from
3176: per_all_assignments_f asg,
3177: per_periods_of_service serv
3178: where asg.assignment_id = l_asg_id
3179: and asg.period_of_service_id = serv.period_of_service_id
3180: and p_end_date between asg.effective_start_date and asg.effective_end_date
3184: --Below cursor fetches the termination date of the current assignment
3185: CURSOR csr_get_term_asg_info (p_asg_id NUMBER)
3186: IS
3187: SELECT min(paaf.effective_start_date) min_active_start_date
3188: FROM per_all_assignments_f paaf,
3189: per_assignment_status_types past,
3190: pay_all_payrolls_f pay,
3191: hr_soft_coding_keyflex sck
3192: WHERE paaf.assignment_id = p_asg_id
3779: return date is
3780: l_eff_date date;
3781: cursor csr_get_start_date is
3782: select paaf.effective_start_date from
3783: per_all_assignments_f paaf,
3784: pay_all_payrolls_f papf ,
3785: hr_soft_coding_keyflex flex,
3786: per_assignment_status_types past
3787: where paaf.assignment_id = asg_id and paaf.effective_end_date = eff_date - 1
3875:
3876: cursor csr_terminated_emp is
3877: select serv.actual_termination_date actual_termination_date
3878: from
3879: per_all_assignments_f asg,
3880: per_periods_of_service serv
3881: where asg.assignment_id = p_asg_id
3882: and serv.actual_termination_date is not null
3883: and asg.period_of_service_id = serv.period_of_service_id
3884: and p_payment_date between asg.effective_start_date and asg.effective_end_date;
3885:
3886: cursor csr_get_start_date is
3887: select paaf.effective_start_date,paaf.effective_end_date
3888: from per_all_assignments_f paaf
3889: where paaf.assignment_id = p_asg_id
3890: and p_payment_date between paaf.effective_start_date and paaf.effective_end_date;
3891:
3892: cursor csr_get_starter is
3972: IS
3973: SELECT trim(asg.primary_flag) asg_primary_flag,
3974: trim(pap.per_information10) per_agg_flag
3975: FROM per_all_people_f pap,
3976: per_all_assignments_f asg
3977: WHERE asg.assignment_id = c_asg_id
3978: AND pap.person_id = asg.person_id
3979: AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
3980: AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
4234: IS
4235: SELECT trim(asg.primary_flag) asg_primary_flag,
4236: trim(pap.per_information10) per_agg_flag
4237: FROM per_all_people_f pap,
4238: per_all_assignments_f asg
4239: WHERE asg.assignment_id = c_asg_id
4240: AND pap.person_id = asg.person_id
4241: AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4242: AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
4473: IS
4474: SELECT trim(asg.primary_flag) asg_primary_flag,
4475: trim(pap.per_information10) per_agg_flag
4476: FROM per_all_people_f pap,
4477: per_all_assignments_f asg
4478: WHERE asg.assignment_id = c_asg_id
4479: AND pap.person_id = asg.person_id
4480: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4481: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
5139: IS
5140: SELECT trim(asg.primary_flag) asg_primary_flag,
5141: trim(pap.per_information10) per_agg_flag
5142: FROM per_all_people_f pap,
5143: per_all_assignments_f asg
5144: WHERE asg.assignment_id = c_asg_id
5145: AND pap.person_id = asg.person_id
5146: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
5147: AND c_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
6111: fnd_file.put_line (fnd_file.LOG,'Error : Enter the First FPS date when First FPS Indicator is Yes.');
6112: RAISE first_fps_indicator_error;
6113: end if;
6114:
6115: sqlstr := 'select distinct PERSON_ID '|| 'from per_all_assignments_f paaf, '
6116: || 'pay_payroll_actions ppa '|| 'where ppa.payroll_action_id = :payroll_action_id '
6117: || 'and ppa.business_group_id = paaf.business_group_id '|| ' and paaf.payroll_id = '
6118: ||''''||l_payroll_id||''''|| ' order by person_id';
6119:
6157: l_exist NUMBER;
6158: l_first_fps VARCHAR2(1);
6159: l_first_fps_run_date date;
6160: l_first_fps_eff_date date;
6161: l_asg_number per_all_assignments_f.assignment_number%type;
6162: CURSOR csr_parameter_info
6163: IS
6164: SELECT to_number(pay_gb_eoy_archive.get_parameter(legislative_parameters, 'PAYROLL_ID')) payroll_id,
6165: SUBSTR(pay_gb_eoy_archive.get_parameter(legislative_parameters,'TAX_REF'),1,20) tax_ref,
6177: trim(nvl(pap.per_information10,'N')) per_agg_flag,
6178: trim(nvl(pap.per_information9,'N')) ni_agg_flag,
6179: asg.assignment_number
6180: FROM per_all_people_f pap,
6181: per_all_assignments_f asg
6182: WHERE asg.assignment_id = c_asg_id
6183: AND pap.person_id = asg.person_id
6184: AND asg.business_group_id = l_business_group_id
6185: AND c_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
6199: pay_payroll_actions appa2, --Prepayments
6200: pay_assignment_actions act, --Payroll Run
6201: pay_assignment_actions act1, --Prepayments
6202: pay_action_interlocks pai, --Prepayments
6203: per_all_assignments_f as1
6204: WHERE --ppa.payroll_action_id = pactid
6205: as1.person_id BETWEEN stperson AND endperson
6206: AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
6207: AND act.payroll_action_id = appa.payroll_action_id
6244: trim(paaf.primary_flag) asg_primary_flag,
6245: trim(pap.per_information10) per_agg_flag,
6246: pap.person_id
6247: from per_all_people_f pap,
6248: per_all_assignments_f paaf,
6249: per_assignment_status_types past --,
6250: --pay_payroll_actions paa
6251: where pap.person_id between stperson and endperson
6252: -- and paa.payroll_Action_id = p_prepay_id
6263: and pap2.effective_start_date <= p_prepayment_date
6264: )
6265: and paaf.effective_start_date =
6266: ( select max(asg2.effective_start_date)
6267: from per_all_assignments_f asg2
6268: where asg2.assignment_id = paaf.assignment_id
6269: and asg2.assignment_type = 'E'
6270: and asg2.effective_start_date <= p_prepayment_date
6271: )
6329: pay_payroll_actions appa2, --Prepayments
6330: pay_assignment_actions act, --Payroll Run
6331: pay_assignment_actions act1, --Prepayments
6332: pay_action_interlocks pai, --Prepayments
6333: per_all_assignments_f as1
6334: WHERE as1.person_id = p_person_id --45885
6335: AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
6336: AND act.payroll_action_id = appa.payroll_action_id
6337: AND act.source_action_id IS NULL
6365: pay_payroll_actions appa2, --Prepayments
6366: pay_assignment_actions act, --Payroll Run
6367: pay_assignment_actions act1, --Prepayments
6368: pay_action_interlocks pai, --Prepayments
6369: per_all_assignments_f as1
6370: WHERE as1.person_id = p_person_id --45885
6371: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
6372: AND act.payroll_action_id = appa.payroll_action_id
6373: AND act.source_action_id IS NULL
6384: AND appa2.payroll_action_id = p_pre_pact_id;
6385:
6386: l_person_id number;
6387: cursor csr_get_other_asg(c_asg_id number) is
6388: select paaf.assignment_id asg_id from per_all_assignments_f paaf where
6389: paaf.person_id = l_person_id
6390: and paaf.assignment_id <> c_asg_id
6391: and l_effective_date between paaf.effective_start_date and paaf.effective_end_date;
6392:
6404: /* ,max (pact.effective_date) effective_date
6405: ,max(paa1.assignment_action_id)*/
6406: FROM pay_assignment_actions paa
6407: ,pay_assignment_actions paa1
6408: ,per_all_assignments_f paaf
6409: ,pay_payroll_actions pact
6410: ,pay_payroll_actions pact1
6411: ,per_time_periods ptp
6412: ,pay_action_interlocks pai
6462: ,max (pact.effective_date) effective_date*/
6463: distinct max(paa1.assignment_action_id) max_pre_act_id
6464: FROM pay_assignment_actions paa
6465: ,pay_assignment_actions paa1
6466: ,per_all_assignments_f paaf
6467: ,pay_payroll_actions pact
6468: ,pay_payroll_actions pact1
6469: ,per_time_periods ptp
6470: ,pay_action_interlocks pai
6546: -- Cursor to fetch the RTI NI Reporting assignment for this person.
6547: cursor csr_get_ni_rpt_asg(c_asg_id number) is
6548: select
6549: paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
6550: from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei
6551: where paaf1.assignment_id = c_asg_id
6552: and paaf1.person_id = paaf2.person_id
6553: and paaf2.ASSIGNMENT_TYPE = 'E'
6554: and paei.assignment_id = paaf2.assignment_id
6595: CURSOR csr_aggr_asgs_processed(p_person_id NUMBER, p_pre_pay_id NUMBER, p_start_year DATE, p_end_year DATE)
6596: IS
6597: SELECT count(distinct paa1.assignment_id)
6598: FROM pay_assignment_actions paa,
6599: per_all_assignments_f paaf,
6600: pay_payroll_actions pact,
6601: per_time_periods ptp,
6602: pay_assignment_actions paa1, --Prepayments
6603: pay_payroll_actions ppa1, --Prepayments
6622: pay_payroll_actions appa2, --Prepayments
6623: pay_assignment_actions act, --Payroll Run
6624: pay_assignment_actions act1, --Prepayments
6625: pay_action_interlocks pai, --Prepayments
6626: per_all_assignments_f as1,
6627: pay_all_payrolls_f papf ,
6628: hr_soft_coding_keyflex flex
6629: WHERE as1.person_id = p_person_id --58105
6630: AND appa.action_type IN ('R','Q','V','B') -- Payroll Run or Quickpay Run
7935: CURSOR csr_asg_basic_details(c_asg_id NUMBER)
7936: IS
7937: SELECT asg.EFFECTIVE_START_DATE asg_eff_start_date,
7938: asg.person_id
7939: FROM per_all_assignments_f asg
7940: WHERE asg.assignment_id = c_asg_id
7941: AND g_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
7942:
7943: -- for ni only aggregation
8147: select min(paa2.assignment_action_id) asgactid
8148: from
8149: pay_assignment_actions paa1,
8150: pay_assignment_actions paa2,
8151: per_all_assignments_f asg
8152: where
8153: paa1.assignment_action_id = p_assactid
8154: and paa1.payroll_action_id = paa2.payroll_action_id
8155: and paa2.assignment_id = asg.assignment_id
8161: SELECT asg.assignment_id,
8162: asg.EFFECTIVE_START_DATE asg_eff_start_date,
8163: asg.person_id,
8164: asg.assignment_number
8165: FROM per_all_assignments_f asg,
8166: pay_assignment_actions paa,
8167: pay_payroll_actions ppa
8168: WHERE paa.assignment_action_id = c_asg_act_id
8169: AND paa.assignment_id = asg.assignment_id
8179: trim(NVL(pap.per_information9,'N')) per_ni_agg_flag,
8180: trim(NVL(pap.per_information10,'N')) per_paye_agg_flag,
8181: asg.payroll_id payroll_id
8182: FROM per_all_people_f pap,
8183: per_all_assignments_f asg,
8184: pay_assignment_actions paa
8185: WHERE paa.assignment_action_id = c_asg_act_id
8186: AND paa.assignment_id = asg.assignment_id
8187: AND pap.person_id = asg.person_id
8373: to_number(SUBSTR(MAX(lpad(paa.action_sequence,15,'0')
8374: || paa.assignment_action_id),16)) max_asg_act_id,
8375: MAX(pact.effective_date) effective_date
8376: FROM pay_assignment_actions paa,
8377: per_all_assignments_f paaf,
8378: pay_payroll_actions pact,
8379: per_time_periods ptp,
8380: pay_assignment_actions paa1, --Prepayments
8381: pay_payroll_actions ppa1, --Prepayments
8414: to_number (substr (max (lpad (paa.action_sequence ,15,'0')
8415: || paa.assignment_action_id),16)) max_asg_act_id
8416: ,max (pact.effective_date) effective_date
8417: FROM pay_assignment_actions paa
8418: ,per_all_assignments_f paaf
8419: ,pay_payroll_actions pact
8420: ,per_time_periods ptp
8421: WHERE paa.assignment_id = paaf.assignment_id
8422: AND paaf.person_id = p_person_id
8466: SELECT min (act.assignment_action_id)
8467: FROM pay_action_classifications class
8468: ,pay_payroll_actions pact
8469: ,pay_assignment_actions act
8470: ,per_all_assignments_f ass
8471: ,per_periods_of_service pos
8472: WHERE pos.person_id = p_person_id
8473: AND ass.period_of_service_id = pos.period_of_service_id
8474: AND act.assignment_id = ass.assignment_id
8512: -- Cursor to fetch the RTI NI Reporting assignment for this person.
8513: cursor csr_get_ni_rpt_asg(c_asg_id number) is
8514: select
8515: paei.assignment_id , fnd_date.canonical_to_date(paei.AEI_INFORMATION2)
8516: from per_all_assignments_f paaf1, per_all_assignments_f paaf2 ,per_assignment_extra_info paei
8517: where paaf1.assignment_id = c_asg_id
8518: and paaf1.person_id = paaf2.person_id
8519: and paaf2.ASSIGNMENT_TYPE = 'E'
8520: and paei.assignment_id = paaf2.assignment_id
8571: and get the YTD balance value for these action ids.
8572: Sum this with the current assignment NI YTD and then archive.
8573: ***/
8574: cursor csr_asg_act_oth_asgs(c_asg_id number) is
8575: select max(paa.assignment_action_id) asg_act_id from per_all_assignments_f paaf,
8576: per_all_assignments_f paaf1,
8577: pay_assignment_actions paa,
8578: pay_payroll_actions ppa
8579: where paaf.assignment_id = c_asg_id
8572: Sum this with the current assignment NI YTD and then archive.
8573: ***/
8574: cursor csr_asg_act_oth_asgs(c_asg_id number) is
8575: select max(paa.assignment_action_id) asg_act_id from per_all_assignments_f paaf,
8576: per_all_assignments_f paaf1,
8577: pay_assignment_actions paa,
8578: pay_payroll_actions ppa
8579: where paaf.assignment_id = c_asg_id
8580: and paaf1.person_id = paaf.person_id
8672:
8673:
8674: cursor csr_get_asg_no(p_asg_id number,p_effective_date date) is
8675: SELECT assignment_number
8676: FROM per_all_assignments_f
8677: WHERE assignment_id = p_asg_id
8678: AND p_effective_date BETWEEN effective_start_date
8679: AND effective_end_date
8680: ORDER BY effective_start_date;
8680: ORDER BY effective_start_date;
8681:
8682: cursor csr_get_latest_asg_no(p_asg_id number,p_effective_date date) is
8683: SELECT assignment_number
8684: FROM per_all_assignments_f
8685: WHERE assignment_id = p_asg_id
8686: ORDER BY effective_start_date;
8687:
8688: cursor csr_asg_act_processed_earlier(c_person_id number,
8718: pay_payroll_actions appa2, --Prepayments
8719: pay_assignment_actions act, --Payroll Run
8720: pay_assignment_actions act1, --Prepayments
8721: pay_action_interlocks pai, --Prepayments
8722: per_all_assignments_f as1
8723: WHERE as1.person_id = p_person_id --45885
8724: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
8725: AND act.payroll_action_id = appa.payroll_action_id
8726: AND act.source_action_id IS NULL
8745: pay_payroll_actions appa2, --Prepayments
8746: pay_assignment_actions act, --Payroll Run
8747: pay_assignment_actions act1, --Prepayments
8748: pay_action_interlocks pai, --Prepayments
8749: per_all_assignments_f as1
8750: WHERE as1.person_id = p_person_id --45885
8751: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
8752: AND act.payroll_action_id = appa.payroll_action_id
8753: AND act.source_action_id IS NULL
8771: pay_payroll_actions appa2, --Prepayments
8772: pay_assignment_actions act, --Payroll Run
8773: pay_assignment_actions act1, --Prepayments
8774: pay_action_interlocks pai, --Prepayments
8775: per_all_assignments_f as1
8776: WHERE
8777: ppa.payroll_action_id = g_payroll_action_id
8778: AND as1.person_id = c_person_id --BETWEEN stperson AND endperson
8779: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
8810: pay_payroll_actions appa2, --Prepayments
8811: pay_assignment_actions act, --Payroll Run
8812: pay_assignment_actions act1, --Prepayments
8813: pay_action_interlocks pai, --Prepayments
8814: per_all_assignments_f as1
8815: WHERE as1.person_id = p_person_id --45885
8816: AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
8817: AND act.payroll_action_id = appa.payroll_action_id
8818: AND act.source_action_id IS NULL
9054: CURSOR csr_aggr_asgs_processed(p_person_id NUMBER, p_pre_pay_id NUMBER, p_start_year DATE, p_end_year DATE)
9055: IS
9056: SELECT count(distinct paa1.assignment_id)
9057: FROM pay_assignment_actions paa,
9058: per_all_assignments_f paaf,
9059: pay_payroll_actions pact,
9060: per_time_periods ptp,
9061: pay_assignment_actions paa1, --Prepayments
9062: pay_payroll_actions ppa1, --Prepayments
9090: , '0')
9091: || paa.assignment_action_id), 16))
9092: , max (pact.effective_date) effective_date
9093:
9094: FROM per_all_assignments_f paaf,
9095: pay_assignment_actions paa
9096: , pay_payroll_actions pact
9097: , per_time_periods ptp
9098: WHERE paa.assignment_id = paaf.assignment_id
9106: BETWEEN g_start_year
9107: AND l_first_fps_eff_date
9108: AND paaf.effective_start_date =
9109: ( select max(asg2.effective_start_date)
9110: from per_all_assignments_f asg2
9111: where asg2.assignment_id = paaf.assignment_id
9112: and asg2.assignment_type = 'E'
9113: and asg2.effective_start_date <= l_prepayment_date
9114: );
9116: --Below cursor fetches the basic details of the current assignment
9117: CURSOR csr_asg_num(c_asg_act_id NUMBER, c_cur_last_effective_date date)
9118: IS
9119: SELECT asg.assignment_number
9120: FROM per_all_assignments_f asg,
9121: pay_assignment_actions paa
9122: WHERE paa.assignment_action_id = c_asg_act_id
9123: AND paa.assignment_id = asg.assignment_id
9124: AND c_cur_last_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
10190: -- should skip the aggregation logic for other assignments
10191: select max(paa.assignment_action_id) into l_asg_action_id
10192: from
10193: pay_assignment_actions paa,
10194: per_all_assignments_f paaf
10195: where
10196: paaf.person_id = l_person_id
10197: and paaf.assignment_id = paa.assignment_id
10198: and paa.payroll_action_id = g_payroll_action_id
12661: NVL(pap.employee_number,' ') employee_number,
12662: TO_CHAR(paa.EFFECTIVE_START_DATE,'DD-MON-RRRR') start_date,
12663: paa.EFFECTIVE_END_DATE end_date, --to_char(paa.EFFECTIVE_END_DATE,'DD-MON-RRRR') end_date
12664: pap.person_id
12665: FROM per_all_assignments_f paa,
12666: per_assignment_status_types past,
12667: per_all_people_f pap
12668: WHERE paa.person_id = pap.person_id
12669: AND paa.assignment_id = c_assignment_id
12676: and pap2.effective_start_date <= l_effective_date
12677: )
12678: and paa.effective_start_date =
12679: ( select max(asg2.effective_start_date)
12680: from per_all_assignments_f asg2
12681: where asg2.assignment_id = paa.assignment_id
12682: and asg2.assignment_type = 'E'
12683: and ASSIGNMENT_STATUS_TYPE_ID = paa.ASSIGNMENT_STATUS_TYPE_ID
12684: and asg2.effective_start_date <= l_effective_date
12840: IS
12841: SELECT COUNT(DISTINCT(paaf.person_id))
12842: FROM pay_payroll_actions ppa,
12843: pay_assignment_actions paa,
12844: per_all_assignments_f paaf
12845: WHERE ppa.payroll_action_id = pactid -- pact_id
12846: AND paa.payroll_action_id = ppa.payroll_action_id
12847: AND paa.action_status = 'C'
12848: AND paaf.assignment_id = paa.assignment_id;