eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_GB_EOY_ARCHIVE dependencies on PER_ALL_ASSIGNMENTS_F

Line 276: g_masg_person_id per_all_assignments_f.person_id%TYPE;

272: g_payroll_tax_dist_tab g_tax_dist_table_typ;
273: g_payroll_permit_no_tab g_permit_no_table_typ;
274: -- variables:
275: -- 1st two are initialised by the archinit procedure
276: g_masg_person_id per_all_assignments_f.person_id%TYPE;
277: -- added g_masg_period_of_service_id to fix bug 3784871
278: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
279: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;
280: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;

Line 278: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;

274: -- variables:
275: -- 1st two are initialised by the archinit procedure
276: g_masg_person_id per_all_assignments_f.person_id%TYPE;
277: -- added g_masg_period_of_service_id to fix bug 3784871
278: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
279: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;
280: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;
281: g_masg_tax_ref_num VARCHAR2(10); -- 4011263: length 10 chars
282: g_max_gross_pay NUMBER(15) := NULL;

Line 279: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;

275: -- 1st two are initialised by the archinit procedure
276: g_masg_person_id per_all_assignments_f.person_id%TYPE;
277: -- added g_masg_period_of_service_id to fix bug 3784871
278: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
279: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;
280: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;
281: g_masg_tax_ref_num VARCHAR2(10); -- 4011263: length 10 chars
282: g_max_gross_pay NUMBER(15) := NULL;
283: g_primary_action pay_assignment_actions.assignment_action_id%TYPE;

Line 280: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;

276: g_masg_person_id per_all_assignments_f.person_id%TYPE;
277: -- added g_masg_period_of_service_id to fix bug 3784871
278: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
279: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;
280: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;
281: g_masg_tax_ref_num VARCHAR2(10); -- 4011263: length 10 chars
282: g_max_gross_pay NUMBER(15) := NULL;
283: g_primary_action pay_assignment_actions.assignment_action_id%TYPE;
284: g_min_assignment_id per_all_assignments_f.assignment_id%TYPE;

Line 284: g_min_assignment_id per_all_assignments_f.assignment_id%TYPE;

280: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;
281: g_masg_tax_ref_num VARCHAR2(10); -- 4011263: length 10 chars
282: g_max_gross_pay NUMBER(15) := NULL;
283: g_primary_action pay_assignment_actions.assignment_action_id%TYPE;
284: g_min_assignment_id per_all_assignments_f.assignment_id%TYPE;
285: g_has_non_extracted_masgs BOOLEAN := FALSE;
286: g_num_actions binary_integer:=0;
287: --
288: ------------------------------- FUNCTIONS -----------------------------------

Line 721: l_min_active per_all_assignments_f.effective_start_date%TYPE;

717: FUNCTION get_agg_active_start(p_asg_id IN NUMBER,
718: p_tax_ref IN VARCHAR2,
719: p_proll_eff_date IN DATE)
720: RETURN DATE IS
721: l_min_active per_all_assignments_f.effective_start_date%TYPE;
722: l_person_id per_all_people_f.person_id%TYPE;
723: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
724: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;
725: l_term_and_xfer VARCHAR2(1);

Line 723: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;

719: p_proll_eff_date IN DATE)
720: RETURN DATE IS
721: l_min_active per_all_assignments_f.effective_start_date%TYPE;
722: l_person_id per_all_people_f.person_id%TYPE;
723: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
724: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;
725: l_term_and_xfer VARCHAR2(1);
726: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
727: l_another_active_asg_xfer VARCHAR2(1);

Line 724: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;

720: RETURN DATE IS
721: l_min_active per_all_assignments_f.effective_start_date%TYPE;
722: l_person_id per_all_people_f.person_id%TYPE;
723: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
724: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;
725: l_term_and_xfer VARCHAR2(1);
726: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
727: l_another_active_asg_xfer VARCHAR2(1);
728: --

Line 733: FROM per_all_assignments_f paaf,

729: -- cursor to get max effective_start date on the given PAYE Ref
730: -- for given assignment on or before the given date
731: CURSOR get_first_active_start IS
732: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id
733: FROM per_all_assignments_f paaf,
734: per_assignment_status_types past,
735: pay_all_payrolls_f papf,
736: hr_soft_coding_keyflex flex
737: WHERE paaf.assignment_id = p_asg_id

Line 752: FROM per_all_assignments_f paaf,

748: -- never been active on the given PAYE Ref therefore get_first_active_start
749: -- will not be able to return first Active/Susp Status date
750: CURSOR get_first_start IS
751: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
752: FROM per_all_assignments_f paaf,
753: pay_all_payrolls_f papf,
754: hr_soft_coding_keyflex flex
755: WHERE paaf.assignment_id = p_asg_id
756: AND paaf.payroll_id = papf.payroll_id

Line 766: FROM per_all_assignments_f paaf,

762: -- Cursor to check whether the assignment was active on a different PAYE Ref
763: -- a day before it was transferred to another PAYE Ref
764: CURSOR is_term_and_xfer IS
765: SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
766: FROM per_all_assignments_f paaf,
767: per_assignment_status_types past,
768: pay_all_payrolls_f papf,
769: hr_soft_coding_keyflex flex
770: WHERE paaf.assignment_id = p_asg_id

Line 785: FROM per_all_assignments_f paaf1,

781: -- transferred from/to same PAYE Refs on the same day as the given
782: -- assignment but remained active before and after the transfer
783: CURSOR is_another_active_asg_xfer IS
784: SELECT 'Y'
785: FROM per_all_assignments_f paaf1,
786: per_assignment_status_types past1,
787: pay_all_payrolls_f papf1,
788: hr_soft_coding_keyflex flex1
789: WHERE paaf1.period_of_service_id = l_pos_id

Line 800: FROM per_all_assignments_f paaf2,

796: AND p_proll_eff_date BETWEEN papf1.effective_start_date and papf1.effective_end_date
797: AND papf1.soft_coding_keyflex_id = flex1.soft_coding_keyflex_id
798: AND flex1.segment1 = p_tax_ref
799: AND EXISTS ( SELECT 1
800: FROM per_all_assignments_f paaf2,
801: per_assignment_status_types past2,
802: pay_all_payrolls_f papf2,
803: hr_soft_coding_keyflex flex2
804: WHERE paaf2.assignment_id = paaf1.assignment_id

Line 818: FROM per_all_assignments_f paaf,

814: -- cursor to get first effective_start_date across all active or suspended
815: -- assignments of the person on a given tax ref as at a given date
816: CURSOR get_agg_min_start_date IS
817: SELECT min(paaf.effective_start_date) min_active
818: FROM per_all_assignments_f paaf,
819: per_assignment_status_types past,
820: pay_all_payrolls_f papf,
821: hr_soft_coding_keyflex flex
822: WHERE paaf.person_id = l_person_id

Line 921: l_min_active per_all_assignments_f.effective_start_date%TYPE;

917: FUNCTION get_agg_active_end(p_asg_id IN NUMBER,
918: p_tax_ref IN VARCHAR2,
919: p_proll_eff_date IN DATE)
920: RETURN DATE IS
921: l_min_active per_all_assignments_f.effective_start_date%TYPE;
922: l_max_active per_all_assignments_f.effective_start_date%TYPE;
923: l_person_id per_all_people_f.person_id%TYPE;
924: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
925: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;

Line 922: l_max_active per_all_assignments_f.effective_start_date%TYPE;

918: p_tax_ref IN VARCHAR2,
919: p_proll_eff_date IN DATE)
920: RETURN DATE IS
921: l_min_active per_all_assignments_f.effective_start_date%TYPE;
922: l_max_active per_all_assignments_f.effective_start_date%TYPE;
923: l_person_id per_all_people_f.person_id%TYPE;
924: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
925: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;
926: l_term_and_xfer VARCHAR2(1);

Line 924: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;

920: RETURN DATE IS
921: l_min_active per_all_assignments_f.effective_start_date%TYPE;
922: l_max_active per_all_assignments_f.effective_start_date%TYPE;
923: l_person_id per_all_people_f.person_id%TYPE;
924: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
925: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;
926: l_term_and_xfer VARCHAR2(1);
927: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
928: l_another_active_asg_xfer VARCHAR2(1);

Line 925: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;

921: l_min_active per_all_assignments_f.effective_start_date%TYPE;
922: l_max_active per_all_assignments_f.effective_start_date%TYPE;
923: l_person_id per_all_people_f.person_id%TYPE;
924: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
925: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;
926: l_term_and_xfer VARCHAR2(1);
927: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
928: l_another_active_asg_xfer VARCHAR2(1);
929: --

Line 934: FROM per_all_assignments_f paaf,

930: -- cursor to get max effective_end_date on the given PAYE Ref
931: -- for given assignment
932: CURSOR get_last_active_end IS
933: SELECT max(paaf.effective_end_date) last_end_date, max(person_id) person_id
934: FROM per_all_assignments_f paaf,
935: per_assignment_status_types past,
936: pay_all_payrolls_f papf,
937: hr_soft_coding_keyflex flex
938: WHERE paaf.assignment_id = p_asg_id

Line 953: FROM per_all_assignments_f paaf,

949: -- never been active on the given PAYE Ref therefore get_first_active_start
950: -- will not be able to return first Active/Susp Status date
951: CURSOR get_first_start IS
952: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
953: FROM per_all_assignments_f paaf,
954: pay_all_payrolls_f papf,
955: hr_soft_coding_keyflex flex
956: WHERE paaf.assignment_id = p_asg_id
957: AND paaf.payroll_id = papf.payroll_id

Line 967: FROM per_all_assignments_f paaf,

963: -- Cursor to check whether the assignment was active on a different PAYE Ref
964: -- a day before it was transferred to another PAYE Ref
965: CURSOR is_term_and_xfer IS
966: SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
967: FROM per_all_assignments_f paaf,
968: per_assignment_status_types past,
969: pay_all_payrolls_f papf,
970: hr_soft_coding_keyflex flex
971: WHERE paaf.assignment_id = p_asg_id

Line 986: FROM per_all_assignments_f paaf1,

982: -- transferred from/to same PAYE Refs on the same day as the given
983: -- assignment but remained active before and after the transfer
984: CURSOR is_another_active_asg_xfer IS
985: SELECT 'Y'
986: FROM per_all_assignments_f paaf1,
987: per_assignment_status_types past1,
988: pay_all_payrolls_f papf1,
989: hr_soft_coding_keyflex flex1
990: WHERE paaf1.period_of_service_id = l_pos_id

Line 1001: FROM per_all_assignments_f paaf2,

997: AND p_proll_eff_date BETWEEN papf1.effective_start_date and papf1.effective_end_date
998: AND papf1.soft_coding_keyflex_id = flex1.soft_coding_keyflex_id
999: AND flex1.segment1 = p_tax_ref
1000: AND EXISTS ( SELECT 1
1001: FROM per_all_assignments_f paaf2,
1002: per_assignment_status_types past2,
1003: pay_all_payrolls_f papf2,
1004: hr_soft_coding_keyflex flex2
1005: WHERE paaf2.assignment_id = paaf1.assignment_id

Line 1019: FROM per_all_assignments_f paaf,

1015: -- cursor to get latest effective_end_date across all active or suspended
1016: -- assignments of the person on a given tax ref as at a given date
1017: CURSOR get_agg_max_end_date IS
1018: SELECT max(paaf.effective_end_date) max_active
1019: FROM per_all_assignments_f paaf,
1020: per_assignment_status_types past,
1021: pay_all_payrolls_f papf,
1022: hr_soft_coding_keyflex flex
1023: WHERE paaf.person_id = l_person_id

Line 1749: FROM per_all_assignments_f paf

1745: p_start_year_eid number,
1746: p_payroll_end_year_eid number
1747: ) is
1748: SELECT 1 valid_asg
1749: FROM per_all_assignments_f paf
1750: WHERE paf.rowid = chartorowid(p_asg_rowid)
1751: AND paf.effective_end_date >= p_start_date
1752: AND paf.effective_start_date <= p_end_date
1753: AND NOT EXISTS (select 1

Line 1754: from per_all_assignments_f paf2

1750: WHERE paf.rowid = chartorowid(p_asg_rowid)
1751: AND paf.effective_end_date >= p_start_date
1752: AND paf.effective_start_date <= p_end_date
1753: AND NOT EXISTS (select 1
1754: from per_all_assignments_f paf2
1755: where paf2.assignment_id = paf.assignment_id
1756: AND paf2.effective_end_date > paf.effective_end_date
1757: AND paf2.effective_end_date >=
1758: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str

Line 1809: FROM per_all_assignments_f asg,

1805: substr(ltrim(substr(org_information1,4,11),'/') ,1,10) tax_ref, -- 4011263
1806: decode(per.per_information9,'Y','Y',NULL) multiple_asg_flag,
1807: rowidtochar(asg.ROWID) charrowid,
1808: 'N' tax_ref_xfer
1809: FROM per_all_assignments_f asg,
1810: pay_all_payrolls_f ppf,
1811: hr_soft_coding_keyflex flex,
1812: hr_organization_information org,
1813: per_all_people_f per

Line 1889: ,per_all_assignments_f PASS

1885: rowidtochar(pass.rowid) charrowid,
1886: 'Y' tax_ref_xfer
1887: FROM
1888: per_all_people_f per
1889: ,per_all_assignments_f PASS
1890: ,per_all_assignments_f ASS
1891: ,pay_all_payrolls_f NROLL
1892: ,hr_soft_coding_keyflex FLEX
1893: ,pay_all_payrolls_f PROLL

Line 1890: ,per_all_assignments_f ASS

1886: 'Y' tax_ref_xfer
1887: FROM
1888: per_all_people_f per
1889: ,per_all_assignments_f PASS
1890: ,per_all_assignments_f ASS
1891: ,pay_all_payrolls_f NROLL
1892: ,hr_soft_coding_keyflex FLEX
1893: ,pay_all_payrolls_f PROLL
1894: ,hr_soft_coding_keyflex pflex

Line 2295: per_all_assignments_f asg1,

2291: CURSOR get_agg_non_retry_actions(p_asg_act_id NUMBER) IS
2292: SELECT act2.assignment_action_id, asg2.assignment_number, asg1.assignment_number retry_asg_number, pap.full_name, act2.action_status
2293: FROM pay_assignment_actions act1,
2294: pay_assignment_actions act2,
2295: per_all_assignments_f asg1,
2296: per_all_assignments_f asg2,
2297: per_all_people_f pap
2298: WHERE act1.assignment_action_id = p_asg_act_id
2299: AND act1.assignment_id = asg1.assignment_id

Line 2296: per_all_assignments_f asg2,

2292: SELECT act2.assignment_action_id, asg2.assignment_number, asg1.assignment_number retry_asg_number, pap.full_name, act2.action_status
2293: FROM pay_assignment_actions act1,
2294: pay_assignment_actions act2,
2295: per_all_assignments_f asg1,
2296: per_all_assignments_f asg2,
2297: per_all_people_f pap
2298: WHERE act1.assignment_action_id = p_asg_act_id
2299: AND act1.assignment_id = asg1.assignment_id
2300: AND asg1.person_id = pap.person_id

Line 3009: l_assignment_id per_all_assignments_f.assignment_id%TYPE;

3005: l_archive_item_id ff_archive_items.archive_item_id%TYPE;
3006: l_ovn NUMBER;
3007: l_some_warning BOOLEAN;
3008: --
3009: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3010: l_effective_end_date DATE;
3011: l_adj_eff_end_date DATE;
3012: l_tax_ref_transfer VARCHAR(1);
3013: l_termination_type VARCHAR(1);

Line 3023: l_assignment_number per_all_assignments_f.assignment_number%TYPE;

3019: l_payroll_tax_ref VARCHAR2(10);
3020: l_payroll_tax_dist VARCHAR2(3);
3021: --
3022: l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
3023: l_assignment_number per_all_assignments_f.assignment_number%TYPE;
3024: l_person_id per_all_people_f.person_id%TYPE;
3025: l_organization_id hr_organization_units.organization_id%TYPE;
3026: l_location_id per_all_assignments_f.location_id%TYPE;
3027: l_people_group_id per_all_assignments_f.people_group_id%TYPE;

Line 3026: l_location_id per_all_assignments_f.location_id%TYPE;

3022: l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
3023: l_assignment_number per_all_assignments_f.assignment_number%TYPE;
3024: l_person_id per_all_people_f.person_id%TYPE;
3025: l_organization_id hr_organization_units.organization_id%TYPE;
3026: l_location_id per_all_assignments_f.location_id%TYPE;
3027: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3028: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3029: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3030: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;

Line 3027: l_people_group_id per_all_assignments_f.people_group_id%TYPE;

3023: l_assignment_number per_all_assignments_f.assignment_number%TYPE;
3024: l_person_id per_all_people_f.person_id%TYPE;
3025: l_organization_id hr_organization_units.organization_id%TYPE;
3026: l_location_id per_all_assignments_f.location_id%TYPE;
3027: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3028: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3029: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3030: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3031: l_active_start per_all_assignments_f.effective_start_date%TYPE;

Line 3028: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;

3024: l_person_id per_all_people_f.person_id%TYPE;
3025: l_organization_id hr_organization_units.organization_id%TYPE;
3026: l_location_id per_all_assignments_f.location_id%TYPE;
3027: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3028: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3029: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3030: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3031: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3032: l_active_end per_all_assignments_f.effective_end_date%TYPE;

Line 3029: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;

3025: l_organization_id hr_organization_units.organization_id%TYPE;
3026: l_location_id per_all_assignments_f.location_id%TYPE;
3027: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3028: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3029: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3030: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3031: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3032: l_active_end per_all_assignments_f.effective_end_date%TYPE;
3033: --

Line 3030: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;

3026: l_location_id per_all_assignments_f.location_id%TYPE;
3027: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3028: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3029: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3030: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3031: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3032: l_active_end per_all_assignments_f.effective_end_date%TYPE;
3033: --
3034: l_effective_start_date DATE;

Line 3031: l_active_start per_all_assignments_f.effective_start_date%TYPE;

3027: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3028: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3029: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3030: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3031: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3032: l_active_end per_all_assignments_f.effective_end_date%TYPE;
3033: --
3034: l_effective_start_date DATE;
3035: l_final_process_date DATE;

Line 3032: l_active_end per_all_assignments_f.effective_end_date%TYPE;

3028: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3029: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3030: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3031: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3032: l_active_end per_all_assignments_f.effective_end_date%TYPE;
3033: --
3034: l_effective_start_date DATE;
3035: l_final_process_date DATE;
3036: --

Line 3164: FROM per_all_assignments_f paaf,

3160: WHERE act.assignment_action_id = p_asgactid;
3161: --
3162: CURSOR get_asg_active_range(p_asg_id NUMBER, p_tax_ref VARCHAR2) IS
3163: SELECT min(paaf.effective_start_date) min_active, max(paaf.effective_end_date) max_active
3164: FROM per_all_assignments_f paaf,
3165: per_assignment_status_types past,
3166: pay_all_payrolls_f papf,
3167: hr_soft_coding_keyflex flex
3168: WHERE paaf.assignment_id = p_asg_id

Line 3179: FROM per_all_assignments_f paaf,

3175: --
3176: -- Bug fix for 3699865 - this cursor will retrieve the final process date of the assignment
3177: cursor csr_asg_last_process_date(p_assid NUMBER) IS
3178: SELECT distinct greatest(ppos.final_process_date,ppos.last_standard_process_date)
3179: FROM per_all_assignments_f paaf,
3180: per_periods_of_service ppos
3181: WHERE paaf.assignment_id = p_assid
3182: AND paaf.period_of_service_id = ppos.period_of_service_id;
3183: --

Line 3192: FROM per_all_assignments_f ass

3188: ass.organization_id,
3189: ass.location_id,
3190: ass.people_group_id,
3191: ass.period_of_service_id
3192: FROM per_all_assignments_f ass
3193: WHERE ass.assignment_id = p_assid
3194: AND ass.effective_end_date = p_eff_end_date;
3195: --
3196: -- 1778139. The asg may have been terminated during the

Line 3208: FROM per_all_assignments_f ass

3204: ass.organization_id,
3205: ass.location_id,
3206: ass.people_group_id,
3207: ass.period_of_service_id
3208: FROM per_all_assignments_f ass
3209: WHERE ass.assignment_id = p_assid
3210: AND ass.effective_end_date < p_eff_end_date
3211: ORDER BY ass.effective_end_date desc;
3212:

Line 3226: FROM per_all_assignments_f ass

3222: ass.organization_id,
3223: ass.location_id,
3224: ass.people_group_id,
3225: ass.period_of_service_id
3226: FROM per_all_assignments_f ass
3227: WHERE ass.assignment_id = p_assid
3228: AND p_eff_end_date BETWEEN
3229: ass.effective_start_date AND ass.effective_end_date;
3230: --

Line 3234: FROM per_all_assignments_f ass

3230: --
3231: cursor csr_asg_start(p_asg_id NUMBER, p_asg_end DATE,
3232: p_start_year DATE, p_end_year DATE) IS
3233: SELECT max(ass.effective_start_date)
3234: FROM per_all_assignments_f ass
3235: ,pay_all_payrolls_f nroll
3236: ,hr_soft_coding_keyflex flex
3237: ,per_all_assignments_f pass
3238: ,pay_all_payrolls_f proll

Line 3237: ,per_all_assignments_f pass

3233: SELECT max(ass.effective_start_date)
3234: FROM per_all_assignments_f ass
3235: ,pay_all_payrolls_f nroll
3236: ,hr_soft_coding_keyflex flex
3237: ,per_all_assignments_f pass
3238: ,pay_all_payrolls_f proll
3239: ,hr_soft_coding_keyflex pflex
3240: WHERE ass.assignment_id = p_asg_id
3241: AND ass.effective_start_date < p_asg_end

Line 3345: per_all_assignments_f paf,

3341: AND result_value in ('DY', 'DN', 'DP', 'DR', 'PY')
3342: AND run_result_id = (SELECT to_number(substr(max(lpad(to_char(act.action_sequence),15,'0')|| lpad(to_char(prr.run_result_id),19,'0')),16))
3343: FROM pay_payroll_Actions pact,
3344: pay_assignment_actions act,
3345: per_all_assignments_f paf,
3346: pay_run_results prr
3347: WHERE pact.payroll_Action_id = act.payroll_Action_id
3348: AND pact.effective_date BETWEEN g_start_year and g_end_year
3349: AND act.action_status = 'C'

Line 3506: FROM per_all_assignments_f ass

3502: /* assignment rows with its predecessor looking for the payroll */
3503: /* that had a different tax district at that date */
3504: ( SELECT nvl(max(ass.effective_start_date),
3505: to_date('01-01-0001','DD-MM-YYYY'))
3506: FROM per_all_assignments_f ass
3507: ,pay_all_payrolls_f nroll
3508: ,hr_soft_coding_keyflex flex
3509: ,per_all_assignments_f pass /* previous assignment */
3510: ,pay_all_payrolls_f proll

Line 3509: ,per_all_assignments_f pass /* previous assignment */

3505: to_date('01-01-0001','DD-MM-YYYY'))
3506: FROM per_all_assignments_f ass
3507: ,pay_all_payrolls_f nroll
3508: ,hr_soft_coding_keyflex flex
3509: ,per_all_assignments_f pass /* previous assignment */
3510: ,pay_all_payrolls_f proll
3511: ,hr_soft_coding_keyflex pflex
3512: WHERE ass.assignment_id = bal_assact.assignment_id
3513: AND nroll.payroll_id = ass.payroll_id

Line 3736: FROM per_all_assignments_f asg

3732: p_tax_ref VARCHAR2) IS
3733: -- fetch any asg for the person within the same tax reference
3734: -- but on a payroll with a different (or null) permit.
3735: SELECT 1
3736: FROM per_all_assignments_f asg
3737: WHERE asg.person_id = p_person_id
3738: AND asg.effective_start_date < p_year_end
3739: AND asg.effective_end_date >= p_year_start
3740: AND asg.payroll_id <> p_payroll_id