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 300: g_masg_person_id per_all_assignments_f.person_id%TYPE;

296: g_payroll_tax_dist_tab g_tax_dist_table_typ;
297: g_payroll_permit_no_tab g_permit_no_table_typ;
298: -- variables:
299: -- 1st two are initialised by the archinit procedure
300: g_masg_person_id per_all_assignments_f.person_id%TYPE;
301: -- added g_masg_period_of_service_id to fix bug 3784871
302: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
303: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;
304: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;

Line 302: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;

298: -- variables:
299: -- 1st two are initialised by the archinit procedure
300: g_masg_person_id per_all_assignments_f.person_id%TYPE;
301: -- added g_masg_period_of_service_id to fix bug 3784871
302: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
303: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;
304: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;
305: g_masg_tax_ref_num VARCHAR2(10); -- 4011263: length 10 chars
306: g_max_gross_pay NUMBER(15) := NULL;

Line 303: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;

299: -- 1st two are initialised by the archinit procedure
300: g_masg_person_id per_all_assignments_f.person_id%TYPE;
301: -- added g_masg_period_of_service_id to fix bug 3784871
302: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
303: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;
304: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;
305: g_masg_tax_ref_num VARCHAR2(10); -- 4011263: length 10 chars
306: g_max_gross_pay NUMBER(15) := NULL;
307: g_primary_action pay_assignment_actions.assignment_action_id%TYPE;

Line 304: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;

300: g_masg_person_id per_all_assignments_f.person_id%TYPE;
301: -- added g_masg_period_of_service_id to fix bug 3784871
302: g_masg_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
303: g_masg_active_start per_all_assignments_f.effective_start_date%TYPE;
304: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;
305: g_masg_tax_ref_num VARCHAR2(10); -- 4011263: length 10 chars
306: g_max_gross_pay NUMBER(15) := NULL;
307: g_primary_action pay_assignment_actions.assignment_action_id%TYPE;
308: g_min_assignment_id per_all_assignments_f.assignment_id%TYPE;

Line 308: g_min_assignment_id per_all_assignments_f.assignment_id%TYPE;

304: g_masg_active_end per_all_assignments_f.effective_end_date%TYPE;
305: g_masg_tax_ref_num VARCHAR2(10); -- 4011263: length 10 chars
306: g_max_gross_pay NUMBER(15) := NULL;
307: g_primary_action pay_assignment_actions.assignment_action_id%TYPE;
308: g_min_assignment_id per_all_assignments_f.assignment_id%TYPE;
309: g_has_non_extracted_masgs BOOLEAN := FALSE;
310: g_num_actions binary_integer:=0;
311: --
312: ------------------------------- FUNCTIONS -----------------------------------

Line 745: l_min_active per_all_assignments_f.effective_start_date%TYPE;

741: FUNCTION get_agg_active_start(p_asg_id IN NUMBER,
742: p_tax_ref IN VARCHAR2,
743: p_proll_eff_date IN DATE)
744: RETURN DATE IS
745: l_min_active per_all_assignments_f.effective_start_date%TYPE;
746: l_person_id per_all_people_f.person_id%TYPE;
747: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
748: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;
749: l_term_and_xfer VARCHAR2(1);

Line 747: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;

743: p_proll_eff_date IN DATE)
744: RETURN DATE IS
745: l_min_active per_all_assignments_f.effective_start_date%TYPE;
746: l_person_id per_all_people_f.person_id%TYPE;
747: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
748: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;
749: l_term_and_xfer VARCHAR2(1);
750: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
751: l_another_active_asg_xfer VARCHAR2(1);

Line 748: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;

744: RETURN DATE IS
745: l_min_active per_all_assignments_f.effective_start_date%TYPE;
746: l_person_id per_all_people_f.person_id%TYPE;
747: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
748: l_new_min_active per_all_assignments_f.effective_start_date%TYPE;
749: l_term_and_xfer VARCHAR2(1);
750: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
751: l_another_active_asg_xfer VARCHAR2(1);
752: --

Line 757: FROM per_all_assignments_f paaf,

753: -- cursor to get max effective_start date on the given PAYE Ref
754: -- for given assignment on or before the given date
755: CURSOR get_first_active_start IS
756: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id
757: FROM per_all_assignments_f paaf,
758: per_assignment_status_types past,
759: pay_all_payrolls_f papf,
760: hr_soft_coding_keyflex flex
761: WHERE paaf.assignment_id = p_asg_id

Line 776: FROM per_all_assignments_f paaf,

772: -- never been active on the given PAYE Ref therefore get_first_active_start
773: -- will not be able to return first Active/Susp Status date
774: CURSOR get_first_start IS
775: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
776: FROM per_all_assignments_f paaf,
777: pay_all_payrolls_f papf,
778: hr_soft_coding_keyflex flex
779: WHERE paaf.assignment_id = p_asg_id
780: AND paaf.payroll_id = papf.payroll_id

Line 790: FROM per_all_assignments_f paaf,

786: -- Cursor to check whether the assignment was active on a different PAYE Ref
787: -- a day before it was transferred to another PAYE Ref
788: CURSOR is_term_and_xfer IS
789: SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
790: FROM per_all_assignments_f paaf,
791: per_assignment_status_types past,
792: pay_all_payrolls_f papf,
793: hr_soft_coding_keyflex flex
794: WHERE paaf.assignment_id = p_asg_id

Line 809: FROM per_all_assignments_f paaf1,

805: -- transferred from/to same PAYE Refs on the same day as the given
806: -- assignment but remained active before and after the transfer
807: CURSOR is_another_active_asg_xfer IS
808: SELECT 'Y'
809: FROM per_all_assignments_f paaf1,
810: per_assignment_status_types past1,
811: pay_all_payrolls_f papf1,
812: hr_soft_coding_keyflex flex1
813: WHERE paaf1.period_of_service_id = l_pos_id

Line 824: FROM per_all_assignments_f paaf2,

820: AND p_proll_eff_date BETWEEN papf1.effective_start_date and papf1.effective_end_date
821: AND papf1.soft_coding_keyflex_id = flex1.soft_coding_keyflex_id
822: AND flex1.segment1 = p_tax_ref
823: AND EXISTS ( SELECT 1
824: FROM per_all_assignments_f paaf2,
825: per_assignment_status_types past2,
826: pay_all_payrolls_f papf2,
827: hr_soft_coding_keyflex flex2
828: WHERE paaf2.assignment_id = paaf1.assignment_id

Line 842: FROM per_all_assignments_f paaf,

838: -- cursor to get first effective_start_date across all active or suspended
839: -- assignments of the person on a given tax ref as at a given date
840: CURSOR get_agg_min_start_date IS
841: SELECT min(paaf.effective_start_date) min_active
842: FROM per_all_assignments_f paaf,
843: per_assignment_status_types past,
844: pay_all_payrolls_f papf,
845: hr_soft_coding_keyflex flex
846: WHERE paaf.person_id = l_person_id

Line 945: l_min_active per_all_assignments_f.effective_start_date%TYPE;

941: FUNCTION get_agg_active_end(p_asg_id IN NUMBER,
942: p_tax_ref IN VARCHAR2,
943: p_proll_eff_date IN DATE)
944: RETURN DATE IS
945: l_min_active per_all_assignments_f.effective_start_date%TYPE;
946: l_max_active per_all_assignments_f.effective_start_date%TYPE;
947: l_person_id per_all_people_f.person_id%TYPE;
948: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
949: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;

Line 946: l_max_active per_all_assignments_f.effective_start_date%TYPE;

942: p_tax_ref IN VARCHAR2,
943: p_proll_eff_date IN DATE)
944: RETURN DATE IS
945: l_min_active per_all_assignments_f.effective_start_date%TYPE;
946: l_max_active per_all_assignments_f.effective_start_date%TYPE;
947: l_person_id per_all_people_f.person_id%TYPE;
948: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
949: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;
950: l_term_and_xfer VARCHAR2(1);

Line 948: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;

944: RETURN DATE IS
945: l_min_active per_all_assignments_f.effective_start_date%TYPE;
946: l_max_active per_all_assignments_f.effective_start_date%TYPE;
947: l_person_id per_all_people_f.person_id%TYPE;
948: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
949: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;
950: l_term_and_xfer VARCHAR2(1);
951: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
952: l_another_active_asg_xfer VARCHAR2(1);

Line 949: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;

945: l_min_active per_all_assignments_f.effective_start_date%TYPE;
946: l_max_active per_all_assignments_f.effective_start_date%TYPE;
947: l_person_id per_all_people_f.person_id%TYPE;
948: l_pos_id per_all_assignments_f.period_of_service_id%TYPE;
949: l_new_max_active per_all_assignments_f.effective_start_date%TYPE;
950: l_term_and_xfer VARCHAR2(1);
951: l_old_paye_ref hr_soft_coding_keyflex.segment1%TYPE;
952: l_another_active_asg_xfer VARCHAR2(1);
953: --

Line 958: FROM per_all_assignments_f paaf,

954: -- cursor to get max effective_end_date on the given PAYE Ref
955: -- for given assignment
956: CURSOR get_last_active_end IS
957: SELECT max(paaf.effective_end_date) last_end_date, max(person_id) person_id
958: FROM per_all_assignments_f paaf,
959: per_assignment_status_types past,
960: pay_all_payrolls_f papf,
961: hr_soft_coding_keyflex flex
962: WHERE paaf.assignment_id = p_asg_id

Line 977: FROM per_all_assignments_f paaf,

973: -- never been active on the given PAYE Ref therefore get_first_active_start
974: -- will not be able to return first Active/Susp Status date
975: CURSOR get_first_start IS
976: SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
977: FROM per_all_assignments_f paaf,
978: pay_all_payrolls_f papf,
979: hr_soft_coding_keyflex flex
980: WHERE paaf.assignment_id = p_asg_id
981: AND paaf.payroll_id = papf.payroll_id

Line 991: FROM per_all_assignments_f paaf,

987: -- Cursor to check whether the assignment was active on a different PAYE Ref
988: -- a day before it was transferred to another PAYE Ref
989: CURSOR is_term_and_xfer IS
990: SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
991: FROM per_all_assignments_f paaf,
992: per_assignment_status_types past,
993: pay_all_payrolls_f papf,
994: hr_soft_coding_keyflex flex
995: WHERE paaf.assignment_id = p_asg_id

Line 1010: FROM per_all_assignments_f paaf1,

1006: -- transferred from/to same PAYE Refs on the same day as the given
1007: -- assignment but remained active before and after the transfer
1008: CURSOR is_another_active_asg_xfer IS
1009: SELECT 'Y'
1010: FROM per_all_assignments_f paaf1,
1011: per_assignment_status_types past1,
1012: pay_all_payrolls_f papf1,
1013: hr_soft_coding_keyflex flex1
1014: WHERE paaf1.period_of_service_id = l_pos_id

Line 1025: FROM per_all_assignments_f paaf2,

1021: AND p_proll_eff_date BETWEEN papf1.effective_start_date and papf1.effective_end_date
1022: AND papf1.soft_coding_keyflex_id = flex1.soft_coding_keyflex_id
1023: AND flex1.segment1 = p_tax_ref
1024: AND EXISTS ( SELECT 1
1025: FROM per_all_assignments_f paaf2,
1026: per_assignment_status_types past2,
1027: pay_all_payrolls_f papf2,
1028: hr_soft_coding_keyflex flex2
1029: WHERE paaf2.assignment_id = paaf1.assignment_id

Line 1043: FROM per_all_assignments_f paaf,

1039: -- cursor to get latest effective_end_date across all active or suspended
1040: -- assignments of the person on a given tax ref as at a given date
1041: CURSOR get_agg_max_end_date IS
1042: SELECT max(paaf.effective_end_date) max_active
1043: FROM per_all_assignments_f paaf,
1044: per_assignment_status_types past,
1045: pay_all_payrolls_f papf,
1046: hr_soft_coding_keyflex flex
1047: WHERE paaf.person_id = l_person_id

Line 1796: FROM per_all_assignments_f paf

1792: p_start_year_eid number,
1793: p_payroll_end_year_eid number
1794: ) is
1795: SELECT 1 valid_asg
1796: FROM per_all_assignments_f paf
1797: WHERE paf.rowid = chartorowid(p_asg_rowid)
1798: AND paf.effective_end_date >= p_start_date
1799: AND paf.effective_start_date <= p_end_date
1800: AND NOT EXISTS (select 1

Line 1801: from per_all_assignments_f paf2

1797: WHERE paf.rowid = chartorowid(p_asg_rowid)
1798: AND paf.effective_end_date >= p_start_date
1799: AND paf.effective_start_date <= p_end_date
1800: AND NOT EXISTS (select 1
1801: from per_all_assignments_f paf2
1802: where paf2.assignment_id = paf.assignment_id
1803: AND paf2.effective_end_date > paf.effective_end_date
1804: AND paf2.effective_end_date >=
1805: fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str

Line 1856: FROM per_all_assignments_f asg,

1852: substr(ltrim(substr(org_information1,4,11),'/') ,1,10) tax_ref, -- 4011263
1853: decode(per.per_information9,'Y','Y',NULL) multiple_asg_flag,
1854: rowidtochar(asg.ROWID) charrowid,
1855: 'N' tax_ref_xfer
1856: FROM per_all_assignments_f asg,
1857: pay_all_payrolls_f ppf,
1858: hr_soft_coding_keyflex flex,
1859: hr_organization_information org,
1860: per_all_people_f per

Line 1937: ,per_all_assignments_f PASS

1933: rowidtochar(pass.rowid) charrowid,
1934: 'Y' tax_ref_xfer
1935: FROM
1936: per_all_people_f per
1937: ,per_all_assignments_f PASS
1938: ,per_all_assignments_f ASS
1939: ,pay_all_payrolls_f NROLL
1940: ,hr_soft_coding_keyflex FLEX
1941: ,pay_all_payrolls_f PROLL

Line 1938: ,per_all_assignments_f ASS

1934: 'Y' tax_ref_xfer
1935: FROM
1936: per_all_people_f per
1937: ,per_all_assignments_f PASS
1938: ,per_all_assignments_f ASS
1939: ,pay_all_payrolls_f NROLL
1940: ,hr_soft_coding_keyflex FLEX
1941: ,pay_all_payrolls_f PROLL
1942: ,hr_soft_coding_keyflex pflex

Line 2344: per_all_assignments_f asg1,

2340: CURSOR get_agg_non_retry_actions(p_asg_act_id NUMBER) IS
2341: SELECT act2.assignment_action_id, asg2.assignment_number, asg1.assignment_number retry_asg_number, pap.full_name, act2.action_status
2342: FROM pay_assignment_actions act1,
2343: pay_assignment_actions act2,
2344: per_all_assignments_f asg1,
2345: per_all_assignments_f asg2,
2346: per_all_people_f pap
2347: WHERE act1.assignment_action_id = p_asg_act_id
2348: AND act1.assignment_id = asg1.assignment_id

Line 2345: per_all_assignments_f asg2,

2341: SELECT act2.assignment_action_id, asg2.assignment_number, asg1.assignment_number retry_asg_number, pap.full_name, act2.action_status
2342: FROM pay_assignment_actions act1,
2343: pay_assignment_actions act2,
2344: per_all_assignments_f asg1,
2345: per_all_assignments_f asg2,
2346: per_all_people_f pap
2347: WHERE act1.assignment_action_id = p_asg_act_id
2348: AND act1.assignment_id = asg1.assignment_id
2349: AND asg1.person_id = pap.person_id

Line 3124: l_assignment_id per_all_assignments_f.assignment_id%TYPE;

3120: l_archive_item_id ff_archive_items.archive_item_id%TYPE;
3121: l_ovn NUMBER;
3122: l_some_warning BOOLEAN;
3123: --
3124: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
3125: l_effective_end_date DATE;
3126: l_adj_eff_end_date DATE;
3127: l_tax_ref_transfer VARCHAR(1);
3128: l_termination_type VARCHAR(1);

Line 3138: l_assignment_number per_all_assignments_f.assignment_number%TYPE;

3134: l_payroll_tax_ref VARCHAR2(10);
3135: l_payroll_tax_dist VARCHAR2(3);
3136: --
3137: l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
3138: l_assignment_number per_all_assignments_f.assignment_number%TYPE;
3139: l_person_id per_all_people_f.person_id%TYPE;
3140: l_organization_id hr_organization_units.organization_id%TYPE;
3141: l_location_id per_all_assignments_f.location_id%TYPE;
3142: l_people_group_id per_all_assignments_f.people_group_id%TYPE;

Line 3141: l_location_id per_all_assignments_f.location_id%TYPE;

3137: l_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
3138: l_assignment_number per_all_assignments_f.assignment_number%TYPE;
3139: l_person_id per_all_people_f.person_id%TYPE;
3140: l_organization_id hr_organization_units.organization_id%TYPE;
3141: l_location_id per_all_assignments_f.location_id%TYPE;
3142: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3143: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3144: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3145: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;

Line 3142: l_people_group_id per_all_assignments_f.people_group_id%TYPE;

3138: l_assignment_number per_all_assignments_f.assignment_number%TYPE;
3139: l_person_id per_all_people_f.person_id%TYPE;
3140: l_organization_id hr_organization_units.organization_id%TYPE;
3141: l_location_id per_all_assignments_f.location_id%TYPE;
3142: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3143: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3144: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3145: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3146: l_active_start per_all_assignments_f.effective_start_date%TYPE;

Line 3143: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;

3139: l_person_id per_all_people_f.person_id%TYPE;
3140: l_organization_id hr_organization_units.organization_id%TYPE;
3141: l_location_id per_all_assignments_f.location_id%TYPE;
3142: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3143: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3144: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3145: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3146: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3147: l_active_end per_all_assignments_f.effective_end_date%TYPE;

Line 3144: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;

3140: l_organization_id hr_organization_units.organization_id%TYPE;
3141: l_location_id per_all_assignments_f.location_id%TYPE;
3142: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3143: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3144: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3145: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3146: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3147: l_active_end per_all_assignments_f.effective_end_date%TYPE;
3148: --

Line 3145: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;

3141: l_location_id per_all_assignments_f.location_id%TYPE;
3142: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3143: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3144: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3145: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3146: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3147: l_active_end per_all_assignments_f.effective_end_date%TYPE;
3148: --
3149: l_effective_start_date DATE;

Line 3146: l_active_start per_all_assignments_f.effective_start_date%TYPE;

3142: l_people_group_id per_all_assignments_f.people_group_id%TYPE;
3143: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3144: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3145: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3146: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3147: l_active_end per_all_assignments_f.effective_end_date%TYPE;
3148: --
3149: l_effective_start_date DATE;
3150: l_final_process_date DATE;

Line 3147: l_active_end per_all_assignments_f.effective_end_date%TYPE;

3143: l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
3144: l_agg_active_start per_all_assignments_f.effective_start_date%TYPE;
3145: l_agg_active_end per_all_assignments_f.effective_end_date%TYPE;
3146: l_active_start per_all_assignments_f.effective_start_date%TYPE;
3147: l_active_end per_all_assignments_f.effective_end_date%TYPE;
3148: --
3149: l_effective_start_date DATE;
3150: l_final_process_date DATE;
3151: --

Line 3265: lv_assignment_id per_all_assignments_f.assignment_id%TYPE;

3261: l_nil_able NUMBER(15) :=0;
3262: -- End of Bug 6271548
3263:
3264: -- Modifications for the bug 8452959 Start
3265: lv_assignment_id per_all_assignments_f.assignment_id%TYPE;
3266: lv_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
3267: lv_count NUMBER;
3268: -- Modifications for the bug 8452959 End
3269:

Line 3291: FROM per_all_assignments_f paaf,

3287: WHERE act.assignment_action_id = p_asgactid;
3288: --
3289: CURSOR get_asg_active_range(p_asg_id NUMBER, p_tax_ref VARCHAR2) IS
3290: SELECT min(paaf.effective_start_date) min_active, max(paaf.effective_end_date) max_active
3291: FROM per_all_assignments_f paaf,
3292: per_assignment_status_types past,
3293: pay_all_payrolls_f papf,
3294: hr_soft_coding_keyflex flex
3295: WHERE paaf.assignment_id = p_asg_id

Line 3306: FROM per_all_assignments_f paaf,

3302: --
3303: -- Bug fix for 3699865 - this cursor will retrieve the final process date of the assignment
3304: cursor csr_asg_last_process_date(p_assid NUMBER) IS
3305: SELECT distinct greatest(ppos.final_process_date,ppos.last_standard_process_date)
3306: FROM per_all_assignments_f paaf,
3307: per_periods_of_service ppos
3308: WHERE paaf.assignment_id = p_assid
3309: AND paaf.period_of_service_id = ppos.period_of_service_id;
3310: --

Line 3319: FROM per_all_assignments_f ass

3315: ass.organization_id,
3316: ass.location_id,
3317: ass.people_group_id,
3318: ass.period_of_service_id
3319: FROM per_all_assignments_f ass
3320: WHERE ass.assignment_id = p_assid
3321: AND ass.effective_end_date = p_eff_end_date;
3322: --
3323: -- 1778139. The asg may have been terminated during the

Line 3335: FROM per_all_assignments_f ass

3331: ass.organization_id,
3332: ass.location_id,
3333: ass.people_group_id,
3334: ass.period_of_service_id
3335: FROM per_all_assignments_f ass
3336: WHERE ass.assignment_id = p_assid
3337: AND ass.effective_end_date < p_eff_end_date
3338: ORDER BY ass.effective_end_date desc;
3339:

Line 3353: FROM per_all_assignments_f ass

3349: ass.organization_id,
3350: ass.location_id,
3351: ass.people_group_id,
3352: ass.period_of_service_id
3353: FROM per_all_assignments_f ass
3354: WHERE ass.assignment_id = p_assid
3355: AND p_eff_end_date BETWEEN
3356: ass.effective_start_date AND ass.effective_end_date;
3357: --

Line 3361: FROM per_all_assignments_f ass

3357: --
3358: cursor csr_asg_start(p_asg_id NUMBER, p_asg_end DATE,
3359: p_start_year DATE, p_end_year DATE) IS
3360: SELECT max(ass.effective_start_date)
3361: FROM per_all_assignments_f ass
3362: ,pay_all_payrolls_f nroll
3363: ,hr_soft_coding_keyflex flex
3364: ,per_all_assignments_f pass
3365: ,pay_all_payrolls_f proll

Line 3364: ,per_all_assignments_f pass

3360: SELECT max(ass.effective_start_date)
3361: FROM per_all_assignments_f ass
3362: ,pay_all_payrolls_f nroll
3363: ,hr_soft_coding_keyflex flex
3364: ,per_all_assignments_f pass
3365: ,pay_all_payrolls_f proll
3366: ,hr_soft_coding_keyflex pflex
3367: WHERE ass.assignment_id = p_asg_id
3368: AND ass.effective_start_date < p_asg_end

Line 3473: per_all_assignments_f paf,

3469: AND result_value in ('DY', 'DN', 'DP', 'DR', 'PY')
3470: 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))
3471: FROM pay_payroll_Actions pact,
3472: pay_assignment_actions act,
3473: per_all_assignments_f paf,
3474: pay_run_results prr
3475: WHERE pact.payroll_Action_id = act.payroll_Action_id
3476: AND pact.effective_date BETWEEN g_start_year and g_end_year
3477: --AND act.action_status = 'C'

Line 3641: FROM per_all_assignments_f ass

3637: -- assignment rows with its predecessor looking for the payroll
3638: -- that had a different tax district at that date
3639: ( SELECT nvl(max(ass.effective_start_date),
3640: to_date('01-01-0001','DD-MM-YYYY'))
3641: FROM per_all_assignments_f ass
3642: ,pay_all_payrolls_f nroll
3643: ,hr_soft_coding_keyflex flex
3644: ,per_all_assignments_f pass -- previous assignment
3645: ,pay_all_payrolls_f proll

Line 3644: ,per_all_assignments_f pass -- previous assignment

3640: to_date('01-01-0001','DD-MM-YYYY'))
3641: FROM per_all_assignments_f ass
3642: ,pay_all_payrolls_f nroll
3643: ,hr_soft_coding_keyflex flex
3644: ,per_all_assignments_f pass -- previous assignment
3645: ,pay_all_payrolls_f proll
3646: ,hr_soft_coding_keyflex pflex
3647: WHERE ass.assignment_id = bal_assact.assignment_id
3648: AND nroll.payroll_id = ass.payroll_id

Line 3877: FROM per_all_assignments_f asg

3873: p_tax_ref VARCHAR2) IS
3874: -- fetch any asg for the person within the same tax reference
3875: -- but on a payroll with a different (or null) permit.
3876: SELECT 1
3877: FROM per_all_assignments_f asg
3878: WHERE asg.person_id = p_person_id
3879: AND asg.effective_start_date < p_year_end
3880: AND asg.effective_end_date >= p_year_start
3881: AND asg.payroll_id <> p_payroll_id

Line 3916: from per_all_assignments_f paaf,

3912: lv_payroll_id number,
3913: lv_tax_ref varchar)
3914: is
3915: select count(*)
3916: from per_all_assignments_f paaf,
3917: pay_all_payrolls_f papf,
3918: hr_soft_coding_keyflex flex
3919: where paaf.assignment_id = lv_assignment_id
3920: and paaf.payroll_id = lv_payroll_id