[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token))
- (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT meaning
FROM hr_lookups
WHERE lookup_type=p_lookup_type
AND lookup_code=p_lookup_code;
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = p_balance
AND pbd.database_item_suffix = p_dimension
AND pdb.legislation_code = 'IE';
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT pet.element_type_id,
piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'IE'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
select nvl(monthly_tax_credit,0)
,nvl(weekly_tax_credit,0)
,nvl(monthly_std_rate_cut_off,0)
,nvl(weekly_std_rate_cut_off,0)
from pay_ie_paye_details_f pipd
where assignment_id = p_assignment_id
and p_date_earned between
effective_start_date and effective_end_date
and info_source in ('IE_P45','IE_ELECTRONIC','IE_CERT_TAX_CREDITS')
and tax_basis not in ('IE_EMERGENCY','IE_EMERGENCY_NO_PPS'); --Bug No. 4016508
select fgf.global_value
from ff_globals_f fgf
where fgf.global_name = p_global_name
and fgf.legislation_code ='IE'
and p_date_earned between
fgf.effective_start_date and fgf.effective_end_date;*/
select ptp.period_type
from per_time_periods ptp
where time_period_id = p_time_period_id;
select papf.date_of_birth,papf.first_name, papf.last_name
from per_all_people_f papf,
per_all_assignments_f pasf
where pasf.assignment_id = p_assignment_id
and p_date_earned between
pasf.effective_start_date and pasf.effective_end_date
and pasf.business_group_id = p_bg_id
and papf.person_id = pasf.person_id
and p_date_earned between
papf.effective_start_date and papf.effective_end_date
and papf.business_group_id = pasf.business_group_id;
select paa.assignment_action_id pay_assg_act_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id in (select assignment_id
from per_all_assignments_f
where person_id = p_person_id
)
and paa.tax_unit_id = g_paye_ref
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and paa.action_sequence > p_last_act_seq
and to_char(ppa.effective_date,'YYYY') = to_char(p_date_earned, 'YYYY')
and paa.action_status = 'C'
and paa.source_action_id is not null
--Bug 4724788
order by paa.assignment_action_id;
SELECT pdb.defined_balance_id
FROM
pay_balance_dimensions pbd
,pay_balance_types pbt
,pay_defined_balances pdb
WHERE
pbd.dimension_name = p_dimension_name
AND pbd.business_group_id is null
AND pbd.legislation_code='IE'
AND pbt.balance_name = bal_name
AND pbt.business_group_id is null
AND pbt.legislation_code='IE'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id= pbd.balance_dimension_id
AND pdb.business_group_id is null
AND pdb.legislation_code='IE';
select max(paa.assignment_action_id)
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id in (select assignment_id
from per_all_assignments_f
where person_id = p_person_id
)
and paa.tax_unit_id = g_paye_ref
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
and to_char(ppa.effective_date,'YYYY') = to_char(p_date_earned, 'YYYY')
and paa.action_status = 'C'
and paa.source_action_id is not null;
select act_inf.action_information11
from pay_action_information act_inf
where act_inf.action_context_id = p_assactid
and act_inf.action_information_category = 'EMPLOYEE DETAILS'
and act_inf.action_context_type = 'AAP';
select act_inf.action_information30
from pay_action_information act_inf
where act_inf.action_context_id = p_last_p45_act
and act_inf.action_information_category = 'IE EMPLOYEE DETAILS'
and act_inf.action_context_type = 'AAP';
select tax_basis
,weekly_tax_credit
,weekly_std_rate_cut_off
,monthly_tax_credit
,monthly_std_rate_cut_off
from pay_ie_paye_details_f
where assignment_id = p_assignment_id
and p_termination_date between effective_start_date and effective_end_date;
select number_per_fiscal_year
from per_time_period_types tpt
where period_type = l_period_type;
SELECT context_id
FROM ff_contexts
WHERE context_name = 'SOURCE_TEXT';
SELECT sum(PAY_BALANCE_PKG.GET_VALUE(l_defined_bal_id, -- changes made
pac.ASSIGNMENT_ACTION_ID,
g_paye_ref,
null,
pac.CONTEXT_ID,
pac.CONTEXT_VALUE,
null,
null))
FROM pay_action_contexts pac,
pay_assignment_actions pas,
pay_payroll_actions ppa
WHERE substr(pac.Context_Value,1,4) = p_context_value
AND pac.assignment_id in (select papf.assignment_id
from per_all_assignments_f papf
where papf.person_id = p_person_id
)
AND pas.tax_unit_id = g_paye_ref
AND pas.assignment_action_id = pac.assignment_action_id
AND ppa.payroll_action_id = pas.payroll_action_id
AND ppa.effective_date between to_date('01-01-' || to_char(p_effective_date,'YYYY'),'DD-MM-YYYY') --Bug fix 4108423
AND g_archive_end_date
and pas.action_sequence > l_prev_sequence
and pas.action_sequence <= l_current_sequence;
select paa.action_sequence
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks pai,
pay_assignment_actions paa1
where paa1.source_action_id = p_last_p45_act
and pai.locking_action_id = paa1.assignment_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id in (select papf.assignment_id
from per_all_assignments_f papf
where papf.person_id = p_person_id
)
and paa.tax_unit_id = g_paye_ref
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q','I','B','V');
select action_sequence
from pay_assignment_actions ppa
where assignment_action_id = p_payroll_child_actid;
select balance_type_id
from pay_defined_balances
where defined_balance_id = p_defined_bal_id;
select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0) weeks
from pay_run_result_values TARGET
, pay_balance_feeds_f FEED
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_assignment_actions BAL_ASSACT
, pay_payroll_actions PACT
, pay_payroll_actions BACT
, per_time_periods PPTP
, per_time_periods BPTP
, pay_run_results PROCESS_RR
, pay_run_result_values PROCESS
, pay_input_values_f PROCESS_IV
, pay_action_contexts ACX_PROCESS_ID
, ff_contexts CON_PROCESS_ID
where BAL_ASSACT.assignment_action_id = p_source_id
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.balance_type_id +0 = p_balance_type_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0'
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between FEED.effective_start_date and FEED.effective_end_date
and RR.status in ('P','PA')
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id
and BPTP.payroll_id = BACT.payroll_id
and BACT.date_earned between BPTP.start_date and BPTP.end_date
and PPTP.payroll_id = PACT.payroll_id
and PACT.date_earned between PPTP.start_date and PPTP.end_date
and ASSACT.assignment_action_id = ACX_PROCESS_ID.assignment_action_id
and ACX_PROCESS_ID.context_id = CON_PROCESS_ID.context_id
and CON_PROCESS_ID.context_name = 'SOURCE_TEXT'
and PROCESS.result_value = ACX_PROCESS_ID.context_value
and PROCESS.run_result_id = PROCESS_RR.run_result_id
and PROCESS_RR.assignment_action_id = ASSACT.assignment_action_id
and PROCESS_RR.status in ('P','PA')
and PROCESS.input_value_id = PROCESS_IV.input_value_id
and PROCESS_IV.name = 'Contribution_Class'
and PACT.effective_date between PROCESS_IV.effective_start_date and PROCESS_IV.effective_end_date
and PACT.effective_date > to_date(to_char(PACT.effective_date, 'YYYY')||'01/01','YYYY/MM/DD')
and ACX_PROCESS_ID.context_value like 'IE_A%'
and PPTP.regular_payment_date >= trunc(BPTP.regular_payment_date,'Y')
and RR.entry_type <>'B' -- Bug 3079945 start
union all
select nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0) weeks
from pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_balance_feeds_f FEED
where ASSACT.assignment_action_id in (select min(assignment_action_id) from
pay_assignment_actions where assignment_id = p_assignment_id)
and FEED.balance_type_id +0 = p_balance_type_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0'
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and RR.entry_type = 'B';*/
SELECT pac.Context_ID,pac.Context_Value,pac.Assignment_action_id
FROM pay_action_contexts pac,pay_assignment_actions pas,
pay_payroll_actions ppa,pay_payroll_actions appa
WHERE pac.Context_Value = 'IE_A'
AND pac.assignment_id = p_assignment_id
AND pas.assignment_action_id = pac.assignment_action_id
AND ppa.payroll_action_id = pas.payroll_action_id
And appa.payroll_action_id = g_archive_pact
AND to_char(appa.date_earned,'YYYY') = to_char(ppa.date_earned,'YYYY')
AND pac.assignment_action_id = (SELECT MAX(assignment_action_id)
FROM pay_action_contexts
WHERE Context_Value = 'IE_A'
AND assignment_id = p_assignment_id);
select paa.action_sequence
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_interlocks pai,
pay_assignment_actions paa1
where paa1.source_action_id = p_last_p45_action
and pai.locking_action_id = paa1.assignment_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id in (select papf.assignment_id
from per_all_assignments_f papf
where papf.person_id = p_person_id
)
and paa.tax_unit_id = g_paye_ref
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q','I','B','V');
select action_sequence
from pay_assignment_actions ppa
where assignment_action_id = p_source_id;
SELECT sum(PAY_BALANCE_PKG.GET_VALUE(l_defined_bal_id, -- changes made
pac.ASSIGNMENT_ACTION_ID,
g_paye_ref,
null,
pac.CONTEXT_ID,
pac.CONTEXT_VALUE,
null,
null))
FROM pay_action_contexts pac,
pay_assignment_actions pas,
pay_payroll_actions ppa
WHERE pac.Context_Value like 'IE_A%'
AND pac.assignment_id in (select papf.assignment_id
from per_all_assignments_f papf
where papf.person_id = p_person_id
)
AND pas.tax_unit_id = g_paye_ref
AND pas.assignment_action_id = pac.assignment_action_id
AND ppa.payroll_action_id = pas.payroll_action_id
/*AND ppa.date_earned between to_date('01-01-' || to_char(g_archive_start_date ,'YYYY'),'DD-MM-YYYY') --Bug Fix 3986250*/
AND ppa.effective_date between to_date('01-01-' || to_char(p_effective_date,'YYYY'),'DD-MM-YYYY') --Bug fix 4108423
AND g_archive_end_date
and pas.action_sequence > l_prev_sequence
and pas.action_sequence <= l_current_sequence;
select defined_balance_id
from pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pbt.balance_name = p_balance_name
and pbt.balance_type_id = pdb.balance_type_id
and pbd.database_item_suffix = '_ASG_PAYE_REF_PRSI_RUN'
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbt.legislation_code = 'IE'
and pbd.legislation_code = 'IE';
SELECT to_number(pai.action_information4) balance_value
FROM pay_action_information pai
WHERE pai.action_context_id = p_last_p45_action
AND pai.action_information_category = 'EMEA BALANCES'
AND pai.action_information1 = p_defined_bal_id;
select balance_type_id
from pay_defined_balances
where defined_balance_id = p_defined_bal_id;
SELECT to_number(pai.action_information4) balance_value
FROM pay_action_information pai
WHERE pai.action_context_id = p_action_context_id
AND pai.action_information_category = 'EMEA BALANCES'
AND pai.action_information1 = p_def_bal_id;
select source_id from
pay_action_information pai,
pay_assignment_actions paa
where paa.assignment_action_id = p_last_p45_action
and paa.assignment_action_id = pai.action_context_id
and pai.action_information_category='EMEA BALANCES';
select decode(ppos.leaving_reason, 'D','Y','N'),
ppos.actual_termination_date
from per_periods_of_service ppos
where ppos.person_id = p_person_id
and ppos.period_of_service_id = (select max(paf.period_of_service_id)
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = p_action_context_id
and pai.locked_action_id = paa.assignment_action_id
and paa.action_status = 'C'
and paa.assignment_id = paf.assignment_id
);
SELECT max(paaf.effective_end_date)
FROM per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
WHERE paaf.person_id = p_person_id
AND paaf.payroll_id = papf.payroll_id
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(g_paye_ref)
AND paaf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
AND paaf.effective_end_date between g_archive_start_date and g_archive_end_date;
SELECT max(effective_end_date)
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND paaf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
);
select act_inf.action_information3
from pay_assignment_actions paa_run,
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_information act_inf
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type = 'P45'
and ppa.report_qualifier = 'IE'
and ppa.action_type = 'X'
and paa.assignment_action_id = act_inf.action_context_id
and act_inf.action_information_category = 'IE P45 INFORMATION'
and act_inf.action_context_type = 'AAP'
and ppa.payroll_action_id <> g_archive_pact
and paa.assignment_action_id = pai.locking_action_id
and paa.source_action_id is NULL
and pai.locked_action_id = paa_run.assignment_action_id
and paa_run.assignment_id = p_assignment_id
and paa_run.action_status = 'C'
and paa.action_status = 'C';
select decode(ppos.leaving_reason, 'D','Y','N'),
ppos.actual_termination_date
from per_periods_of_service ppos
where ppos.person_id = p_person_id
and ppos.period_of_service_id = (select max(paf.period_of_service_id)
from per_all_assignments_f paf,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = p_action_context_id
and pai.locked_action_id = paa.assignment_action_id
and paa.action_status = 'C'
and paa.assignment_id = paf.assignment_id
);
SELECT decode(ppos.leaving_reason, 'D','Y','N'),
ppos.actual_termination_date
FROM per_periods_of_service ppos,
per_all_assignments_f paf
WHERE paf.assignment_id = p_assignment_id
AND ppos.period_of_service_id = paf.period_of_service_id;
SELECT max(paaf.effective_end_date)
FROM per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
WHERE paaf.person_id = p_person_id
AND paaf.payroll_id = papf.payroll_id
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(g_paye_ref)
AND paaf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
AND paaf.effective_end_date between g_archive_start_date and g_archive_end_date;
SELECT max(effective_end_date)
FROM per_all_assignments_f paaf
WHERE paaf.assignment_id = p_assignment_id
AND paaf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
);
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND UPPER(pbt.balance_name) = UPPER(l_balance_name)
AND pbd.database_item_suffix = '_PER_PAYE_REF_YTD';
select ptp.period_num,
ptpt.number_per_fiscal_year,
ptpr.periods_per_period,
ptp.start_date,
ptp.end_date
from per_all_assignments_f paf,
per_time_periods ptp,
per_time_period_types ptpt,
per_time_period_rules ptpr
where paf.assignment_id = p_assignment_id
and p_date_earned between paf.effective_start_date
and paf.effective_end_date
and paf.payroll_id = ptp.payroll_id
and p_date_earned between ptp.start_date and ptp.end_date
and ptp.period_type = ptpt.period_type
and ptpt.number_per_fiscal_year = ptpr.number_per_fiscal_year;
select calculation_option
from pay_ie_social_benefits_f psb
where psb.assignment_id = p_assignment_id; */
select result_value
from pay_run_result_values prr,
pay_run_results pr,
pay_input_values_f piv,
pay_assignment_actions pas,
pay_payroll_actions ppa
where pas.assignment_id in (select assignment_id
from per_all_assignments_f
where person_id = p_person_id)
and pas.tax_unit_id = g_paye_ref
and pas.payroll_action_id = ppa.payroll_action_id
and to_char(ppa.effective_date,'YYYY') = to_char(p_date_earned,'YYYY')
and pr.assignment_action_id = pas.assignment_action_id
and pr.run_result_id = prr.run_result_id
and prr.input_value_id = piv.input_value_id
and pr.element_type_id = piv.element_type_id
and piv.input_value_id = g_tax_basis_id
and piv.business_group_id IS NULL
and piv.legislation_code = 'IE'
and result_value not in ('IE_CUMULATIVE', 'C','IE_EXEMPTION');
select count(*)
from pay_action_information pai,
pay_assignment_Actions paa
where paa.assignment_action_id = pai.action_context_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'IE P45 INFORMATION'
and paa.tax_unit_id = g_paye_ref
and paa.assignment_id in ( select assignment_id
from per_all_assignments_f
where person_id = p_person_id
)
and to_date(pai.action_information3) = l_termination_date;
select act_inf.action_information3
from pay_assignment_actions paa_run,
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_action_information act_inf
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type = 'P45'
and ppa.report_qualifier = 'IE'
and ppa.action_type = 'X'
and paa.assignment_action_id = act_inf.action_context_id
and act_inf.action_information_category = 'IE P45 INFORMATION'
and act_inf.action_context_type = 'AAP'
and ppa.payroll_action_id <> g_archive_pact
and paa.assignment_action_id = pai.locking_action_id
and paa.source_action_id is NULL
and pai.locked_action_id = paa_run.assignment_action_id
and paa_run.assignment_id = p_assignment_id
and paa_run.action_status = 'C'
and paa.action_status = 'C'; */
select 'N'
from pay_ie_paye_details_f
where assignment_id=p_assignment_id
and l_termination_date between effective_start_date and effective_end_date
and tax_basis not in ('IE_EMERGENCY','IE_EMERGENCY_NO_PPS');
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = p_pact_id
AND pai.action_context_type = 'PA'
AND rownum = 1
);
SELECT pet.element_type_id,
piv.input_value_id
FROM pay_input_values_f piv,
pay_element_types_f pet
WHERE piv.element_type_id = pet.element_type_id
AND pet.legislation_code = 'IE'
AND pet.element_name = p_element_name
AND piv.name = p_value_name;
SELECT pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date,
pact.date_earned date_earned,
pact.payroll_id payroll_id,
org.org_information1 tax_details_ref_no,
org.org_information2 employer_paye_ref_no,
hrl.address_line_1 employer_tax_addr1,
hrl.address_line_2 employer_tax_addr2,
hrl.address_line_3 employer_tax_addr3,
hrl.telephone_number_1 employer_tax_ref_phone
--
FROM pay_all_payrolls_f ppf,
pay_payroll_actions pact,
hr_organization_information org,
hr_soft_coding_keyflex flex,
hr_organization_units hou,
hr_locations_all hrl
WHERE org.org_information_context = 'IE_EMPLOYER_INFO' -- for migration changes 4369280
AND ppf.business_group_id = hou.business_group_id
AND org.organization_id = hou.organization_id
AND hou.location_id = hrl.location_id(+)
/*
org.org_information_context = 'IE_ORG_INFORMATION'
AND ppf.business_group_id = org.organization_id
*/
AND pact.payroll_id = ppf.payroll_id
AND pact.effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
-- AND pact.payroll_id = NVL(p_payroll_id,pact.payroll_id)
-- AND ppf.consolidation_set_id = p_consolidation_id
AND pact.effective_date BETWEEN
p_start_date AND nvl(p_end_date,to_date('31-12-4712','dd-mm-rrrr'))
AND (pact.action_type = 'P' OR
pact.action_type = 'U')
AND pact.action_status = 'C'
--Added for bug fix 3567562, to filter payroll information based on PAYE reference
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND org.organization_id = flex.segment4
/*
AND org.org_information1 = flex.segment1
AND org.org_information2 = flex.segment3
*/
-- AND org.org_information_id = g_tax_dis_ref
-- AND org.org_information2 = g_paye_ref
AND org.organization_id = g_paye_ref
AND exists (SELECT NULL
FROM pay_assignment_actions paa,
pay_action_interlocks pai,
pay_assignment_actions paa_arc
WHERE pai.locked_action_id = paa.assignment_action_id
AND pai.locking_action_id = paa_arc.assignment_action_id
AND paa_arc.payroll_action_id = p_pact_id
AND paa.payroll_action_id = pact.payroll_action_id
);
SELECT
hrl.address_line_1 employer_tax_addr1,
hrl.address_line_2 employer_tax_addr2,
hrl.address_line_3 employer_tax_addr3,
org_info.org_information4 employer_tax_contact,
hrl.telephone_number_1 employer_tax_ref_phone,
org_all.name employer_tax_rep_name,
org_all.business_group_id business_group_id
--
FROM hr_all_organization_units org_all
,hr_organization_information org_info
-- ,pay_consolidation_sets pcs
,hr_locations_all hrl
WHERE /*pcs.consolidation_set_id = c_consolidation_set
AND org_all.organization_id = pcs.business_group_id
AND org_info.organization_id = org_all.organization_id
AND org_info.org_information_context = 'IE_ORG_INFORMATION'
AND org_all.business_group_id = pcs.business_group_id
AND*/ org_info.organization_id = org_all.organization_id
AND org_info.org_information_context = 'IE_EMPLOYER_INFO' --for migration changes 4369280
AND org_all.location_id = hrl.location_id (+)
--Added new condition for bug fix 3567562 to filter record based on PAYE reference and Tax District Reference
-- AND org_info.org_information1 = g_tax_dis_ref
-- AND org_info.org_information2 = g_paye_ref ;
SELECT DISTINCT paf.organization_id
FROM per_all_assignments_f paf
WHERE paf.payroll_id = cp_payroll_id
AND cp_effective_date between paf.effective_start_date
AND paf.effective_end_date
AND NOT EXISTS (
SELECT NULL
FROM pay_action_information pai
WHERE pai.action_context_id = cp_payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'ADDRESS DETAILS'
AND pai.action_information1 = paf.organization_id
AND pai.action_information14 = 'Employer Address');
SELECT pact.payroll_action_id payroll_action_id,
pact.effective_date effective_date
FROM pay_assignment_actions paa,
pay_action_interlocks pai,
pay_assignment_actions paa_arc,
pay_payroll_actions pact
WHERE pai.locked_action_id = paa.assignment_action_id
AND pai.locking_action_id = paa_arc.assignment_action_id
AND paa_arc.payroll_action_id = p_pact_id
AND paa.payroll_action_id = pact.payroll_action_id
AND (pact.action_type = 'P' OR
pact.action_type = 'U')
AND pact.action_status = 'C';
sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
sqlstr := 'SELECT DISTINCT person_id
FROM per_people_f ppf,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id +0= ppf.business_group_id
ORDER BY ppf.person_id';
sqlstr:='select 1 from dual where to_char(:payroll_action_id) = dummy';
SELECT as1.person_id person_id,
act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id,
as1.assignment_number works_number,
as1.period_of_service_id period_of_service_id
FROM --per_periods_of_service ppos,
per_all_assignments_f as1,
pay_assignment_actions act,
pay_payroll_actions appa,
pay_action_interlocks pai,
pay_assignment_actions act1,
pay_payroll_actions appa2
WHERE /*appa.consolidation_set_id = p_consolidation_id*/
act.tax_unit_id = p_paye_ref
AND appa.effective_date BETWEEN g_archive_start_date AND g_archive_end_date
AND as1.person_id BETWEEN stperson AND endperson
/* Assignment End Date should be between archive start date and end date */
AND as1.effective_end_date between g_archive_start_date AND g_archive_end_date
AND (as1.effective_end_date = (select max(effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = as1.assignment_id
/* changed the cursor to handle case where 2 user defined assignment status exist mapping to
same per_system_status (5073577) */
and paf1.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
)
AND as1.effective_end_date <> to_date('31-12-4712','DD-MM-YYYY')
)
AND (as1.payroll_id in (select b.payroll_id -- 5059862
from per_assignments_f a,per_assignments_f b
where a.payroll_id = l_payroll_id
and a.person_id = b.person_id
and a.period_of_Service_id = b.period_of_Service_id
and a.period_of_Service_id = as1.period_of_Service_id -- 5758951
and a.person_id = as1.person_id
and a.effective_start_date <= g_archive_end_date
-- and a.effective_end_date >= trunc(g_archive_end_date,'Y') -- bug 6144761
-- 5758951
and a.effective_end_date = (select max(effective_end_date)
from per_all_assignments_f paf1
where paf1.assignment_id = a.assignment_id
and paf1.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
)
)
OR l_payroll_id is null)
--
AND appa.action_type IN ('R','Q') -- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL
AND as1.assignment_id = act.assignment_id
AND act.action_status = 'C'
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C'
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U') -- Prepayments or Quickpay Prepayments
AND appa2.payroll_action_id = (SELECT /*+ USE_NL(ACT2 APPA4)*/
max(appa4.payroll_action_id)
FROM /*pay_pre_payments ppp, --Bug 4193738 --Bug 4468864*/
pay_assignment_actions act2,
pay_payroll_actions appa4
WHERE /*ppp.assignment_action_id=act2.assignment_action_id
AND*/ act2.assignment_id = act.assignment_id
AND act2.action_status = 'C'
AND appa4.payroll_action_id = act2.payroll_action_id
AND appa4.action_type in ('P','U')
AND appa4.effective_date BETWEEN g_archive_start_date AND g_archive_end_date)
-- bug 5597735, change the not exists clause.
-- refer bug 5233518 for more details.
AND NOT EXISTS (SELECT /*+ ORDERED use_nl(appa3)*/ null
from pay_assignment_actions act3,
pay_payroll_actions appa3,
pay_action_interlocks pai, --bug 4208273
pay_assignment_actions act2, --bug 4208273
pay_payroll_actions appa4 --bug 4208273
where pai.locked_action_id= act3.assignment_action_id
and pai.locking_action_id=act2.assignment_action_id
and act3.action_sequence >= act1.action_sequence --bug 4193738
and act3.assignment_id in (select distinct paaf.assignment_id
from per_all_assignments_f paaf
where paaf.person_id = as1.person_id
)
and act3.tax_unit_id = act1.tax_unit_id
and act3.action_status = 'C'
and act2.action_status = 'C'
and act3.payroll_action_id=appa4.payroll_action_id
and appa4.action_type in ('P','U')
and act2.payroll_action_id = appa3.payroll_action_id
and appa3.action_type = 'X'
and appa3.report_type = 'P45')
/* check person does not hold employment with the employer between start of year and archive end date */
AND NOT EXISTS (
SELECT MIN(paf.effective_start_date),MAX(paf.effective_end_date)
FROM per_all_assignments_f paf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex scl
WHERE paf.person_id = as1.person_id
AND paf.payroll_id = papf.payroll_id
/* changed the cursor to handle case where 2 user defined assignment status exist mapping to
same per_system_status (5073577) */
AND paf.assignment_status_type_id in
(SELECT ast.assignment_status_type_id
FROM per_assignment_status_types ast
WHERE ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
)
AND g_archive_end_date between papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment4 = to_char(p_paye_ref)
group by paf.assignment_id
having min(paf.effective_start_date) <= g_archive_end_date
and max(paf.effective_end_date) > g_archive_end_date
)
ORDER BY as1.person_id,as1.assignment_number,act.assignment_id
FOR UPDATE OF as1.assignment_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pre.locked_action_id pre_assignment_action_id,
pay.locked_action_id master_assignment_action_id,
assact.assignment_id assignment_id,
assact.payroll_action_id pay_payroll_action_id,
paa.effective_date effective_date,
ppaa.effective_date pre_effective_date,
paa.date_earned date_earned,
ptp.time_period_id time_period_id
FROM pay_action_interlocks pre,
pay_action_interlocks pay,
pay_payroll_actions paa,
pay_payroll_actions ppaa,
pay_assignment_actions assact,
pay_assignment_actions passact,
per_time_periods ptp -- Added to retrieve correct time_period_id 4906850
WHERE pre.locked_action_id = pay.locking_action_id
AND pre.locking_action_id = p_locking_action_id
AND pre.locked_action_id = passact.assignment_action_id
AND passact.payroll_action_id = ppaa.payroll_action_id
AND ppaa.action_type IN ('P','U')
AND pay.locked_action_id = assact.assignment_action_id
AND assact.payroll_action_id = paa.payroll_action_id
AND assact.source_action_id IS NULL
AND ptp.payroll_id = paa.payroll_id
AND paa.date_earned between ptp.start_date and ptp.end_date
--
ORDER BY pay.locked_action_id DESC;
SELECT max(paa.assignment_action_id)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.date_earned =p_date_earned
AND ppa.action_type in ('R','Q')
AND paa.action_status = 'C'
AND paa.source_action_id is not null;*/
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id in (select assignment_id
from per_all_assignments_f
where person_id = p_person_id
)
AND paa.tax_unit_id = g_paye_ref
AND (paa.source_action_id is not null or ppa.action_type in ('I','V','B'))
AND ppa.effective_date between trunc(p_effective_date,'Y') and p_effective_date
AND paa.action_sequence > p_lat_act_seq
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
AND paa.action_status = 'C';
SELECT max(lpad(paa_src.action_sequence,15,'0')|| paa_src.assignment_action_id)
FROM pay_payroll_actions ppa_p45,
pay_assignment_actions p45_src,
pay_action_information pai_p45,
pay_assignment_actions paa_src
WHERE ppa_p45.action_type = 'X'
AND ppa_p45.report_type = 'P45'
AND ppa_p45.report_qualifier = 'IE'
AND ppa_p45.payroll_action_id <> p_pact_id
AND ppa_p45.payroll_action_id = p45_src.payroll_action_id
AND p45_src.assignment_action_id = pai_p45.action_context_id
AND pai_p45.action_context_type = 'AAP'
AND pai_p45.action_information_category = 'IE P45 INFORMATION'
AND pai_p45.source_id = paa_src.assignment_action_id
AND p45_src.action_status = 'C'
AND paa_src.tax_unit_id = g_paye_ref
AND p45_src.tax_unit_id = g_paye_ref
AND pai_p45.action_information8 = to_char(p_person_id);
SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
WHERE paa.assignment_action_id = pai.action_context_id
AND pai.action_information_category = 'IE P45 INFORMATION'
AND pai.action_context_type = 'AAP'
AND paa.tax_unit_id = g_paye_ref
AND pai.action_information3 between trunc(p_termination_date,'Y') and p_termination_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.report_type = 'P45'
AND ppa.report_category = 'ARCHIVE'
AND ppa.report_qualifier = 'IE'
AND ppa.effective_date between trunc(g_archive_end_date,'Y') and g_archive_end_date
AND paa.payroll_action_id <> p_pact
AND paa.action_status = 'C'
AND pai.action_information8 = to_char(p_person_id);
SELECT paa.payroll_action_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_p45_aact;
SELECT payroll_id,person_id,period_of_service_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND p_date_earned
BETWEEN effective_start_date AND effective_end_date;
SELECT paa.chunk_number,paa.assignment_id
INTO l_chunk_number,l_assignment_id
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assactid;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pai1.action_information2
FROM pay_action_information pai1
WHERE pai1.action_context_type = 'PA'
AND pai1.action_information_category = 'EMEA BALANCE DEFINITION'
AND substr(pai1.action_information4, 1,50) = p_balance_name
AND pai1.action_context_id = p_payroll_action_id;
SELECT to_number(pai2.action_information4) balance_value
FROM pay_action_information pai2
WHERE pai2.action_context_id = p_assignment_action_id
AND pai2.action_information_category = 'EMEA BALANCES'
AND pai2.action_context_type = 'AAP'
AND pai2.action_information1 = p_def_bal_id;
/*SELECT paa.assignment_id assignment_id
,paa.assignment_action_id
,pai_iep45.action_information2 supp_flag
,emp_details.pps_no ppsn
,emp_details.last_name surname
,emp_details.first_name firstname
,emp_details.works_no works
,emp_details.deceased deceased
,to_char(emp_details.date_of_birth,'dd/mm/rrrr') dob
,emp_address.address1 address1
,emp_address.address2 address2
,emp_address.address3 address3
,to_char(emp_details.date_of_commencement,'dd/mm/rrrr') start1
,to_char(emp_details.date_of_leaving,'dd/mm/rrrr') end1
,decode(ptp.period_type,'Lunar Month','W',decode(instr(ptp.period_type,'Week'),0,'M','W')) freq
,to_number(substr(pai_iep45.action_information5, 1,30)) period
,(round(to_number(substr(pai_ieed.action_information26, 1,30)),2)*100) taxcredit
,(round(to_number(substr(pai_ieed.action_information27, 1,30)),2)*100) cutoff
,pai_iep45.action_information4 emergency_tax
,substr(pai_ieed.action_information22, 1,30) prsi_class
,(round(to_number(emp_paye.total_tax),2)*100) totaltax
,(round(to_number(emp_paye.total_pay),2)*100) totalpay
,(round(to_number(emp_paye.this_tax),2)*100) thistax
,(round(to_number(emp_paye.this_pay),2)*100) thispay
,(round(to_number(emp_paye.lump_sum),2)*100) lumpsum
,(round(to_number(emp_prsi.total_prsi),2)*100) totalprsi
,(round(to_number(emp_prsi.total_employee_prsi),2)*100) employeeprsi
,emp_prsi.insurable_weeks totalweeks
,emp_prsi.class_a_insurable_weeks totalaweeks
,(round(to_number(emp_soc.disability_benefit),2)*100) benefit
,(round(to_number(emp_soc.red_tax_credit),2)*100) taxcreditreduction
,(round(to_number(emp_soc.red_std_cut_off),2)*100) cutoffreduction
,emp_soc.non_cummulative_basis noncumulative
,pai_epif.action_information6 employer_number
,(round(to_number(emp_supp.total_tax),2)*100) supp_totaltax
,(round(to_number(emp_supp.total_pay),2)*100) supp_totalpay
,(round(to_number(emp_supp.lump_sum),2)*100) supp_lumpsum
,(round(to_number(emp_supp.total_prsi),2)*100) supp_totalprsi
,(round(to_number(emp_supp.total_employee_prsi),2)*100) supp_employeeprsi
,emp_supp.insurable_weeks supp_totalweeks
FROM pay_action_information pai_ed
,pay_action_information pai_iep45
,pay_action_information pai_ieed
,pay_action_information pai_epif
,pay_assignment_actions paa
,pay_action_interlocks pai_arc
,pay_assignment_actions paa_payroll
,per_time_periods ptp
,pay_ie_p45_address_details emp_address
,pay_ie_p45_employee_details emp_details
,pay_ie_p45_soc_ben_details emp_soc
,pay_ie_p45_prsi_details emp_prsi
,pay_ie_p45_paye_details emp_paye
,pay_ie_p45_supp_details emp_supp
WHERE paa.payroll_action_id = c_p45_arch_id
AND paa.assignment_action_id = pai_arc.locking_action_id
AND paa_payroll.assignment_action_id = pai_arc.locked_action_id
AND paa.assignment_action_id = pai_iep45.action_context_id
AND pai_iep45.action_context_type ='AAP'
AND pai_iep45.action_information_category = 'IE P45 INFORMATION'
AND paa.assignment_action_id = pai_ed.action_context_id
AND pai_ed.action_context_type ='AAP'
AND pai_ed.action_information_category = 'EMPLOYEE DETAILS'
AND ptp.time_period_id = pai_ed.action_information16
AND paa.assignment_action_id = pai_ieed.action_context_ID
AND pai_ieed.action_context_type ='AAP'
AND pai_ieed.action_information_category = 'IE EMPLOYEE DETAILS'
AND paa.payroll_action_id = pai_epif.action_context_ID (+)
AND pai_epif.action_context_type (+) ='PA'
AND pai_epif.action_information_category (+)= 'EMEA PAYROLL INFO'
AND pai_epif.action_information1 = paa_payroll.payroll_action_id
AND emp_address.assignment_action_id = paa.assignment_action_id
AND emp_details.assignment_action_id = paa.assignment_action_id
AND emp_soc.assignment_action_id = paa.assignment_action_id
AND emp_prsi.assignment_action_id = paa.assignment_action_id
AND emp_paye.assignment_action_id = paa.assignment_action_id
AND emp_supp.assignment_id (+) = paa.assignment_id
AND paa.assignment_id = NVL(p_assignment_id,paa.assignment_id)
ORDER BY pai_iep45.action_information2;
SELECT paa.assignment_id assignment_id
,paa.assignment_action_id
,pai_iep45.action_information2 supp_flag
,decode(ptp.period_type,'Lunar Month','W',decode(instr(ptp.period_type,'Week'),0,'M','W')) freq
,to_number(substr(pai_iep45.action_information5, 1,30)) period
,pai_iep45.action_information4 emergency_tax,
to_date(substr(pai_iep45.action_information7, 1,30),'DD/MM/RRRR') date_paid --Bug 3991416
FROM pay_action_information pai_ed
,pay_action_information pai_iep45
,pay_assignment_actions paa
,per_time_periods ptp
WHERE paa.payroll_action_id = c_p45_arch_id
AND paa.assignment_action_id = pai_iep45.action_context_id
AND pai_iep45.action_context_type ='AAP'
AND pai_iep45.action_information_category = 'IE P45 INFORMATION'
AND ptp.time_period_id = pai_ed.action_information16
AND paa.assignment_action_id = pai_ed.action_context_ID
AND pai_ed.action_context_type ='AAP'
AND pai_ed.action_information_category = 'EMPLOYEE DETAILS'
AND paa.assignment_id = NVL(p_assignment_id,paa.assignment_id)
ORDER BY pai_iep45.action_information2;
SELECT pai_epif.action_information6 employer_number
FROM pay_assignment_actions paa
,pay_action_interlocks pai_arc
,pay_assignment_actions paa_payroll
,pay_action_information pai_epif
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.assignment_action_id = pai_arc.locking_action_id
AND paa_payroll.assignment_action_id = pai_arc.locked_action_id
AND paa.payroll_action_id = pai_epif.action_context_ID
AND pai_epif.action_context_type ='PA'
AND pai_epif.action_information_category = 'EMEA PAYROLL INFO'
AND pai_epif.action_information1 = paa_payroll.payroll_action_id;
SELECT (round(to_number(substr(nvl(pai_ieed.action_information26,'0'), 1,30)),2)*100) taxcredit
,(round(to_number(substr(nvl(pai_ieed.action_information27,'0'), 1,30)),2)*100) cutoff
,substr(pai_ieed.action_information22, 1,30) prsi_class
FROM pay_action_information pai_ieed
WHERE pai_ieed.action_context_ID = p_assignment_action_id
AND pai_ieed.action_context_type = 'AAP'
AND pai_ieed.action_information_category = 'IE EMPLOYEE DETAILS';
SELECT (round(to_number(nvl(emp_soc.disability_benefit,'0')),2)*100) benefit
,(round(to_number(nvl(emp_soc.red_tax_credit,'0')),2)*100) taxcreditreduction
,(round(to_number(nvl(emp_soc.red_std_cut_off,'0')),2)*100) cutoffreduction
,emp_soc.non_cummulative_basis noncumulative
FROM pay_ie_p45_soc_ben_details emp_soc
WHERE emp_soc.assignment_action_id = p_assignment_action_id;
SELECT emp_details.pps_no ppsn
,emp_details.last_name surname
,emp_details.first_name firstname
,emp_details.works_no works
,emp_details.deceased deceased
,to_char(emp_details.date_of_birth,'dd/mm/rrrr') dob
,to_char(emp_details.date_of_commencement,'dd/mm/rrrr') start1
,to_char(emp_details.date_of_leaving,'dd/mm/rrrr') end1
FROM pay_ie_p45_employee_details emp_details
WHERE emp_details.assignment_action_id = p_assignment_action_id;
SELECT emp_address.address1 address1
,emp_address.address2 address2
,emp_address.address3 address3
FROM pay_ie_p45_address_details emp_address
WHERE emp_address.assignment_action_id = p_assignment_action_id;
SELECT (round(to_number(emp_paye.total_tax),2)*100) totaltax
,(round(to_number(emp_paye.total_pay),2)*100) totalpay
,(round(to_number(emp_paye.this_tax),2)*100) thistax
,(round(to_number(emp_paye.this_pay),2)*100) thispay
,(round(to_number(emp_paye.lump_sum),2)*100) lumpsum
,(round(to_number(emp_prsi.total_employer_prsi),2)*100) employerprsi -- Bug 5005788
,(round(to_number(emp_prsi.total_employee_prsi),2)*100) employeeprsi
,emp_prsi.insurable_weeks totalweeks
,emp_prsi.class_a_insurable_weeks totalaweeks
FROM pay_ie_p45_prsi_details emp_prsi
,pay_ie_p45_paye_details emp_paye
WHERE emp_prsi.assignment_action_id = p_assignment_action_id
AND emp_paye.assignment_action_id = p_assignment_action_id;
SELECT (round(to_number(emp_supp.total_tax),2)*100) supp_totaltax
,(round(to_number(emp_supp.total_pay),2)*100) supp_totalpay
,(round(to_number(emp_supp.lump_sum),2)*100) supp_lumpsum
,(round(to_number(emp_supp.total_employer_prsi),2)*100) supp_totalprsi -- Bug 5005788
,(round(to_number(emp_supp.total_employee_prsi),2)*100) supp_employeeprsi
,emp_supp.insurable_weeks supp_totalweeks
,emp_supp.supp_insurable_classA_weeks supp_classA_weeks -- Bug 5015438
FROM pay_ie_p45_supp_details emp_supp
WHERE emp_supp.assignment_id = p_assignment_id
AND emp_supp.date_paid =p_date_earned
AND emp_supp.pay_period =p_period
AND emp_supp.period_frequency = decode(p_freq,'M','Monthly','Weekly');
SELECT to_char(MIN(ppa_arc.start_date),'DD/MM/RRRR') start_date
FROM pay_assignment_actions paa_p30,
pay_action_interlocks pai_p30,
pay_assignment_actions paa_arc,
pay_payroll_actions ppa_arc
WHERE paa_p30.payroll_Action_id = c_p30_data_lock_process
AND paa_p30.assignment_action_id = pai_p30.locking_action_id
AND paa_arc.assignment_action_id = pai_p30.locked_action_id
AND ppa_arc.payroll_action_id = paa_arc.payroll_action_id;
SELECT substr(pai.action_information5,1,30) employer_tax_addr1
,substr(pai.action_information6,1,30) employer_tax_addr2
,substr(pai.action_information7,1,30) employer_tax_addr3
,substr(pai.action_information26,1,30) employer_tax_contact
,substr(pai.action_information27,1,12) employer_tax_ref_phone
,substr(pai.action_information28,1,30) employer_tax_rep_name
FROM pay_action_information pai
WHERE pai.action_context_id = c_payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'ADDRESS DETAILS'
AND pai.action_information14 = 'IE Employer Tax Address';