[Home] [Help]
823: c_effective_end_date in date,
824: c_tax_reference in varchar2,
825: c_agg_active_start in date,
826: c_agg_active_end in date) is
827: select /*+ ORDERED index(a PER_ASSIGNMENTS_F_N12) use_nl(paa ppa pay flex) */
828: -- fnd_number.canonical_to_number(substr(max(
829: fnd_number.canonical_to_number(substr((
830: lpad(paa.action_sequence,15,'0')||
831: paa.assignment_action_id),16)) assignment_action_id
1225: ass.ASSIGNMENT_ID
1226: from hr_organization_information oi,
1227: pay_payrolls_f roll,
1228: hr_soft_coding_keyflex flex,
1229: per_assignments_f ass,
1230: fnd_sessions sess
1231: where oi.ORG_INFORMATION_CONTEXT = 'Tax Details References'
1232: and roll.business_group_id + 0 = oi.organization_id
1233: /* normally P45 is for leaver so pick up data on the assignment_end_date */
2786:
2787: l_addline1 per_addresses.address_line1%TYPE := address_line1;
2788: l_addline2 per_addresses.address_line2%TYPE:= address_line2;
2789: l_addline3 per_addresses.address_line3%TYPE:=address_line3 ;
2790: l_assignment_number per_assignments_f.assignment_number%TYPE:=assignment_number ;
2791: l_county per_addresses.region_1%type:= county;
2792: l_first_name per_people_f.first_name%TYPE:= first_name;
2793: l_middle_name per_people_f.middle_names%TYPE:= middle_name;
2794: l_last_name per_people_f.last_name%TYPE:= last_name;
3121: l_archive_item_id ff_archive_items.archive_item_id%TYPE;
3122: l_ovn NUMBER;
3123: l_some_warning BOOLEAN;
3124: --
3125: l_assignment_id per_assignments_f.assignment_id%TYPE;
3126: l_termination_date DATE;
3127: l_tax_ref_transfer VARCHAR2(1);
3128: l_transfer_date DATE:=NULL;
3129: --
3126: l_termination_date DATE;
3127: l_tax_ref_transfer VARCHAR2(1);
3128: l_transfer_date DATE:=NULL;
3129: --
3130: l_assignment_number per_assignments_f.assignment_number%TYPE;
3131: l_person_id per_people_f.person_id%TYPE;
3132: l_asg_effective_end_date DATE;
3133: l_deceased_flag VARCHAR2(1);
3134: l_agg_paye_flag VARCHAR2(1);
3140: l_title per_people_f.title%TYPE;
3141: l_date_of_birth per_people_f.date_of_birth%TYPE;
3142: l_sex per_people_f.sex%TYPE;
3143: l_ni_number per_people_f.national_identifier%TYPE;
3144: l_payroll_id per_assignments_f.payroll_id%TYPE;
3145: --
3146: l_address_line1 per_addresses.address_line1%TYPE;
3147: l_address_line2 per_addresses.address_line2%TYPE;
3148: l_address_line3 per_addresses.address_line3%TYPE;
4312: act.assignment_action_id archive_action,
4313: act.assignment_id
4314: FROM pay_payroll_actions pact,
4315: pay_assignment_actions act,
4316: per_assignments_f paf,
4317: pay_action_interlocks pai,
4318: ff_archive_items fai
4319: WHERE pact.report_type ='P45'
4320: AND pact.report_qualifier ='GB'
4350: /* restrict to one row per asg. */
4351: -- Comment out this code as it will be replace by distinct
4352: --AND paf.effective_start_date =
4353: -- (SELECT max(paf2.effective_start_date)
4354: -- FROM per_assignments_f paf2
4355: -- WHERE paf2.assignment_id = paf.assignment_id)
4356: /* commnet out this code and replace by the code below */
4357: --AND NOT EXISTS (SELECT 1
4358: -- FROM pay_action_interlocks pai
4375: act.assignment_action_id archive_action,
4376: act.assignment_id
4377: from pay_payroll_actions pact,
4378: pay_population_ranges ppr,
4379: per_assignments_f paf,
4380: pay_assignment_actions act,
4381: ff_archive_items fai
4382: WHERE pact.report_type ='P45'
4383: AND pact.report_qualifier ='GB'
4415: /* restrict to one row per asg. */
4416: /* Comment out the code for performance fix */
4417: --AND paf.effective_start_date =
4418: -- (SELECT max(paf2.effective_start_date)
4419: -- FROM per_assignments_f paf2
4420: -- WHERE paf2.assignment_id = paf.assignment_id)
4421: AND NOT EXISTS (SELECT 1
4422: FROM pay_action_interlocks pai
4423: WHERE pai.locked_action_id = act.assignment_action_id);