[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org.org_information1 employers_ref_no, org.org_information2 tax_office_name,
org.org_information8 tax_office_phone_no, org.org_information3 employer_name,
org.org_information4 employer_address
FROM pay_payrolls_f ppf, hr_soft_coding_keyflex flex, hr_organization_information org
WHERE ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND fnd_date.canonical_to_date(v_benefit_end_date)
BETWEEN NVL(flex.start_date_active, fnd_date.canonical_to_date(v_benefit_end_date) )
AND NVL(flex.end_date_active, fnd_date.canonical_to_date(v_benefit_end_date) )
AND fnd_date.canonical_to_date(v_benefit_end_date)
BETWEEN NVL(ppf.effective_start_date, fnd_date.canonical_to_date(v_benefit_end_date) )
AND NVL(ppf.effective_end_date, fnd_date.canonical_to_date(v_benefit_end_date) )
AND ppf.business_group_id = org.organization_id AND org.org_information1 = flex.segment1
AND org.org_information_context = 'Tax Details References' AND ppf.payroll_id = v_payroll_id;
SELECT SUBSTR(
legislative_parameters,
INSTR(legislative_parameters, p_token) + (LENGTH(p_token) + 1),
(DECODE(
INSTR(legislative_parameters, ' ', INSTR(legislative_parameters, p_token) ),
0, DECODE(INSTR(legislative_parameters, p_token), 0, .5, LENGTH(legislative_parameters) ),
INSTR(legislative_parameters, ' ', INSTR(legislative_parameters, p_token) )
- (INSTR(legislative_parameters, p_token) + (LENGTH(p_token) + 1) ) ) ) ),
business_group_id, start_date, effective_date -- this will be the benefit end date
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
select 1
from per_all_assignments_f asg,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex flex
where asg.assignment_id = p_assignment_id
and asg.payroll_id = pay.payroll_id
and pay.soft_coding_keyflex_id + 0 = flex.soft_coding_keyflex_id
and (p_tax_ref is null
or
flex.segment1 = p_tax_ref)
and (fnd_date.canonical_to_date(p_end_date) between asg.effective_start_date and asg.effective_end_date
or
( asg.effective_end_date = (select max(paa2.effective_end_date)
from per_assignments_f paa2
where paa2.assignment_id = p_assignment_id)
and asg.effective_end_date < fnd_date.canonical_to_date(p_end_date)));
SELECT user_table_id
INTO l_table_id
FROM pay_user_tables
WHERE UPPER(user_table_name) = UPPER(p_table_name) AND business_group_id IS NULL AND legislation_code = 'GB';
SELECT r.row_low_range_or_name NAME, r.effective_start_date row_start_date,
r.effective_end_date row_end_date, cinst.effective_start_date col_inst_start_date,
cinst.effective_end_date col_inst_end_date, cinst.VALUE VALUE, ff.formula_id formula_id,
ff.effective_start_date ff_start_date, ff.effective_end_date ff_end_date
FROM pay_user_column_instances_f cinst,
pay_user_columns c,
pay_user_rows_f r,
pay_user_tables tab,
ff_formulas_f ff
WHERE tab.user_table_id = p_table_id AND c.user_table_id = tab.user_table_id
AND NVL(c.business_group_id, p_bus_group_id) = p_bus_group_id AND NVL(c.legislation_code, 'GB') =
'GB'
AND UPPER(c.user_column_name) = UPPER(p_col_name) AND cinst.user_column_id = c.user_column_id
AND r.user_table_id = tab.user_table_id
-- and l_effective_date between R.effective_start_date and R.effective_end_date
AND NVL(r.business_group_id, p_bus_group_id) = p_bus_group_id AND NVL(r.legislation_code, 'GB') =
'GB'
AND cinst.user_row_id = r.user_row_id
-- and l_effective_date between CINST.effective_start_date and CINST.effective_end_date
AND NVL(cinst.business_group_id, p_bus_group_id) = p_bus_group_id
AND NVL(cinst.legislation_code, 'GB') = 'GB' AND formula_name = cinst.VALUE
ORDER BY r.row_low_range_or_name,
r.effective_start_date,
r.effective_end_date,
cinst.effective_start_date,
cinst.effective_end_date,
ff.effective_start_date,
ff.effective_end_date;
'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
AND ppf.person_id = ' || l_person_id || ' ORDER BY ppf.person_id';
'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';
select distinct flex.soft_coding_keyflex_id flex_id
from hr_soft_coding_keyflex flex,
--bug 6278134 added join conditions with ppf
pay_all_payrolls_f ppf
where flex.segment1 = nvl(l_tax_reference,flex.segment1)
AND ppf.soft_coding_keyflex_id + 0 = flex.soft_coding_keyflex_id
and ppf.payroll_id = nvl(l_payroll_id,ppf.payroll_id)
-- bug 7122883 added join condition with business_group_id
and ppf.business_group_id=l_business_group_id
-- Bug 6278134: Added effective date condition
and fnd_date.canonical_to_date(l_benefit_end_date) between ppf.effective_start_date and ppf.effective_end_date; */
select count(*)
from hr_assignment_set_amendments
where assignment_set_id = p_asg_set_id;
select payroll_id
from hr_assignment_sets
where assignment_set_id = p_asg_set_id;
select distinct include_or_exclude
from hr_assignment_set_amendments
where assignment_set_id = p_asg_set_id;
select
distinct
paa.assignment_id,
paa.person_id,
UPPER('GB_'|| pec.classification_name) classification_name,
pet.element_name element_name,
pet.element_type_id element_type_id,
peev.element_entry_id element_entry_id,
peev.effective_start_date effective_start_date
from per_all_assignments_f paa,
pay_all_payrolls_f ppf,
pay_element_classifications pec,
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where paa.person_id between stperson AND endperson
and (fnd_date.canonical_to_date(l_benefit_end_date)
between paa.effective_start_date AND paa.effective_end_date
or
paa.effective_end_date > fnd_date.canonical_to_date(l_benefit_start_date))
and paa.payroll_id = ppf.payroll_id
-- bug 7122883 added join condition with business_group_id
and ppf.business_group_id=l_business_group_id
and least(fnd_date.canonical_to_date(l_benefit_end_date),paa.effective_end_date)
between ppf.effective_start_date and ppf.effective_end_date
and (l_payroll_id is null or ppf.payroll_id = l_payroll_id)
and (l_consolidation_set_id is null or ppf.consolidation_set_id = l_consolidation_set_id)
-- bug 7122883 removed the below join
-- and ppf.soft_coding_keyflex_id + 0 = p_flex_id
--bug 7122883 added the below join so as to fetch all the records in that tax refernce
and (l_tax_reference is null or
ppf.soft_coding_keyflex_id + 0 in (select distinct flex.soft_coding_keyflex_id flex_id
from hr_soft_coding_keyflex flex where flex.segment1 = l_tax_reference))
and pec.legislation_code = 'GB'
and pec.classification_name like 'EXTERNAL REPORTING%'
and pet.classification_id = pec.classification_id
and pet.element_type_id = piv.element_type_id
and (piv.name = 'Benefit Start Date' or piv.name = 'Benefit End Date')
and pee.assignment_id = paa.assignment_id
and pee.element_type_id = pet.element_type_id
and pee.element_entry_id = peev.element_entry_id
and peev.input_value_id = piv.input_value_id
and peev.screen_entry_value between l_benefit_start_date and l_benefit_end_date
and exists (select 1
from hr_assignment_sets has,
hr_assignment_set_amendments hasa
where has.assignment_set_id = l_assignment_set_id
and has.business_group_id = paa.business_group_id
and nvl(has.payroll_id, paa.payroll_id) = paa.payroll_id
and hasa.assignment_set_id = has.assignment_set_id
and hasa.assignment_id = paa.assignment_id
and hasa.include_or_exclude = 'I')
--bug 6278134 removed exclude assignment set conditon
/*and not exists (select 1
from hr_assignment_sets has,
hr_assignment_set_amendments hasa
where has.assignment_set_id = l_assignment_set_id
and has.business_group_id = paa.business_group_id
and nvl(has.payroll_id, paa.payroll_id) = paa.payroll_id
and hasa.assignment_set_id = has.assignment_set_id
and hasa.assignment_id = paa.assignment_id
and hasa.include_or_exclude = 'E')*/
order by paa.assignment_id;
select
distinct
paa.assignment_id,
paa.person_id,
UPPER('GB_'|| pec.classification_name) classification_name,
pet.element_name element_name,
pet.element_type_id element_type_id,
peev.element_entry_id element_entry_id,
peev.effective_start_date effective_start_date
from per_all_assignments_f paa,
pay_all_payrolls_f ppf,
pay_element_classifications pec,
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where paa.person_id between stperson AND endperson
and (fnd_date.canonical_to_date(l_benefit_end_date)
between paa.effective_start_date AND paa.effective_end_date
or
paa.effective_end_date > fnd_date.canonical_to_date(l_benefit_start_date))
and paa.payroll_id = ppf.payroll_id
-- bug 7122883 added join condition with business_group_id
and ppf.business_group_id=l_business_group_id
and least(fnd_date.canonical_to_date(l_benefit_end_date),paa.effective_end_date)
between ppf.effective_start_date and ppf.effective_end_date
and (l_payroll_id is null or ppf.payroll_id = l_payroll_id)
and (l_consolidation_set_id is null or ppf.consolidation_set_id = l_consolidation_set_id)
-- bug 7122883 removed the below join
-- and ppf.soft_coding_keyflex_id + 0 = p_flex_id
--bug 7122883 added the below join so as to fetch all the records in that tax refernce
and (l_tax_reference is null or
ppf.soft_coding_keyflex_id + 0 in (select distinct flex.soft_coding_keyflex_id flex_id
from hr_soft_coding_keyflex flex where flex.segment1 = l_tax_reference))
and pec.legislation_code = 'GB'
and pec.classification_name like 'EXTERNAL REPORTING%'
and pet.classification_id = pec.classification_id
and pet.element_type_id = piv.element_type_id
and (piv.name = 'Benefit Start Date' or piv.name = 'Benefit End Date')
and pee.assignment_id = paa.assignment_id
and pee.element_type_id = pet.element_type_id
and pee.element_entry_id = peev.element_entry_id
and peev.input_value_id = piv.input_value_id
and peev.screen_entry_value between l_benefit_start_date and l_benefit_end_date
and not exists (select 1
from hr_assignment_sets has,
hr_assignment_set_amendments hasa
where has.assignment_set_id = l_assignment_set_id
and has.business_group_id = paa.business_group_id
and nvl(has.payroll_id, paa.payroll_id) = paa.payroll_id
and hasa.assignment_set_id = has.assignment_set_id
and hasa.assignment_id = paa.assignment_id
and hasa.include_or_exclude = 'E')
order by paa.assignment_id;
select /*+ ORDERED INDEX(paa PER_ASSIGNMENTS_F_N12,
ppf PAY_PAYROLLS_F_PK)
USE_NL(paa,ppf,pec,pet,piv,pee,peev) */
distinct
paa.assignment_id,
paa.person_id,
UPPER('GB_'|| pec.classification_name) classification_name,
pet.element_name element_name,
pet.element_type_id element_type_id,
peev.element_entry_id element_entry_id,
peev.effective_start_date effective_start_date
from per_all_assignments_f paa,
pay_all_payrolls_f ppf,
pay_element_classifications pec,
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where paa.person_id between stperson AND endperson
and (fnd_date.canonical_to_date(l_benefit_end_date)
between paa.effective_start_date AND paa.effective_end_date
or
paa.effective_end_date > fnd_date.canonical_to_date(l_benefit_start_date))
and paa.payroll_id = ppf.payroll_id
-- bug 7122883 added join condition with business_group_id
and ppf.business_group_id=l_business_group_id
and least(fnd_date.canonical_to_date(l_benefit_end_date),paa.effective_end_date)
between ppf.effective_start_date and ppf.effective_end_date
and (l_payroll_id is null or ppf.payroll_id = l_payroll_id)
and (l_consolidation_set_id is null or ppf.consolidation_set_id = l_consolidation_set_id)
-- bug 7122883 removed the below join
-- and ppf.soft_coding_keyflex_id + 0 = p_flex_id
--bug 7122883 added the below join so as to fetch all the records in that tax refernce
and (l_tax_reference is null or
ppf.soft_coding_keyflex_id + 0 in (select distinct flex.soft_coding_keyflex_id flex_id
from hr_soft_coding_keyflex flex where flex.segment1 = l_tax_reference))
and pec.legislation_code = 'GB'
and pec.classification_name like 'EXTERNAL REPORTING%'
and pet.classification_id = pec.classification_id
and pet.element_type_id = piv.element_type_id
and (piv.name = 'Benefit Start Date' or piv.name = 'Benefit End Date')
and pee.assignment_id = paa.assignment_id
and pee.element_type_id = pet.element_type_id
and pee.element_entry_id = peev.element_entry_id
and peev.input_value_id = piv.input_value_id
and peev.screen_entry_value between l_benefit_start_date and l_benefit_end_date
order by paa.assignment_id;
select SUBSTR(l_leg_param,INSTR(l_leg_param, p_token) + (LENGTH(p_token) + 1),
(DECODE(INSTR(l_leg_param, ' ', INSTR(l_leg_param, p_token)),
0, DECODE(INSTR(l_leg_param, p_token), 0, .5, LENGTH(l_leg_param)),
INSTR(l_leg_param, ' ', INSTR(l_leg_param, p_token))
- (INSTR(l_leg_param, p_token) + (LENGTH(p_token) + 1)))))
into l_ret
from dual;
select legislative_parameters,
business_group_id,
fnd_date.date_to_canonical(start_date),
fnd_date.date_to_canonical(effective_date)
into l_leg_param, l_business_group_id, l_benefit_start_date, l_benefit_end_date
from pay_payroll_actions
where payroll_action_id = pactid;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM DUAL;
SELECT pap.last_name || ' ' || pap.first_name,
paa.payroll_id,
NVL(pap.per_information2, 'N'),
pap.first_name,
pap.middle_names,
pap.last_name,
nvl(paa.ASSIGNMENT_NUMBER,pap.employee_number),
pap.person_id,
pap.national_identifier,
pap.sex,
pap.date_of_birth
FROM per_all_assignments_f paa,
per_all_people_f pap,
per_periods_of_service pps,
pay_all_payrolls_f pay,
hr_soft_coding_keyflex flex
WHERE paa.person_id = pap.person_id
AND pps.PERIOD_OF_SERVICE_ID(+) = paa.PERIOD_OF_SERVICE_ID
AND least(nvl(pps.ACTUAL_TERMINATION_DATE,fnd_date.canonical_to_date(g_param_benefit_end_date)),
fnd_date.canonical_to_date(g_param_benefit_end_date))
BETWEEN pap.effective_start_date AND pap.effective_end_date
AND paa.assignment_id = p_assignment_id
AND paa.payroll_id = pay.payroll_id
AND least(fnd_date.canonical_to_date(g_param_benefit_end_date), paa.effective_end_date)
between pay.effective_start_date and pay.effective_end_date
AND pay.soft_coding_keyflex_id + 0 = flex.soft_coding_keyflex_id
AND (p_tax_ref is null
OR
flex.segment1 = p_tax_ref)
AND (fnd_date.canonical_to_date(g_param_benefit_end_date) between paa.effective_start_date AND paa.effective_end_date
OR
(
paa.effective_end_date = (select max(paa2.effective_end_date)
from per_assignments_f paa2
where paa2.assignment_id = p_assignment_id)
and paa.effective_end_date < fnd_date.canonical_to_date(g_param_benefit_end_date))
);
SELECT paa.assignment_action_id, pai_person.action_information1, pai_comp.action_information6,
pai_comp.action_information7
FROM pay_action_information pai_comp,
pay_action_information pai_person,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = g_pactid
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai_comp.action_context_id = paa.assignment_action_id
AND pai_comp.action_information_category = 'EMEA PAYROLL INFO'
AND pai_person.action_context_id = paa.assignment_action_id
AND pai_person.action_information_category = 'ADDRESS DETAILS'
AND pai_person.action_information14 = 'Employee Address'
AND pai_person.action_information1 = p_person_id
AND pai_comp.action_information6 = p_emp_ref
AND pai_comp.action_information7 = p_emp_name;
SELECT DISTINCT pet.element_type_id element_type_id, peev.element_entry_id element_entry_id,
pet.element_name element_name, peev.effective_start_date effective_start_date, paa.person_id,
paa.assignment_id, UPPER('GB_'|| pec.classification_name) classification_name
FROM pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv,
pay_element_entry_values_f peev,
pay_element_entries_f pee,
per_assignments_f paa,
pay_assignment_actions paac
WHERE pet.classification_id = pec.classification_id AND
pet.element_type_id = piv.element_type_id AND
piv.input_value_id = peev.input_value_id AND
pee.element_entry_id = peev.element_entry_id AND
pee.assignment_id = paac.assignment_id AND
paa.assignment_id = paac.assignment_id AND
paac.assignment_action_id = v_assactid AND
pec.classification_name LIKE 'EXTERNAL REPORTING%' AND
(piv.NAME = 'Benefit Start Date' OR piv.NAME = 'Benefit End Date') AND
pee.assignment_id = paa.assignment_id AND
peev.screen_entry_value BETWEEN v_benefit_start_date AND v_benefit_end_date
ORDER BY pet.element_type_id, peev.element_entry_id, peev.effective_start_date;
SELECT peev.screen_entry_value, UPPER(TRANSLATE(piv.NAME, ' ', '_') ) NAME
FROM pay_input_values_f piv, pay_element_entry_values_f peev
WHERE piv.input_value_id = peev.input_value_id AND
piv.element_type_id = v_element_type_id AND
peev.element_entry_id = v_element_entry_id AND
peev.effective_start_date = v_effective_start_date;
SELECT application_column_name, UPPER(TRANSLATE(end_user_column_name, ' ', '_') ) NAME
FROM fnd_descr_flex_col_usage_vl
WHERE application_id = g_application_id AND
descriptive_flexfield_name = 'Element Entry Developer DF'AND
descriptive_flex_context_code = v_classification_name AND
(enabled_flag IS NULL OR enabled_flag ='Y');
SELECT application_column_name application_column_name, UPPER(TRANSLATE(end_user_column_name, ' ', '_') ) NAME
FROM fnd_descr_flex_col_usage_vl
WHERE application_id = g_application_id AND
descriptive_flexfield_name = 'Action Information DF' AND
descriptive_flex_context_code = v_element_name AND
(enabled_flag IS NULL OR enabled_flag = 'Y');
SELECT UPPER(TRANSLATE(flex_act.end_user_column_name, ' ', '_') ) NAME
FROM fnd_descr_flex_col_usage_vl flex_act
WHERE flex_act.application_id = g_application_id AND
flex_act.descriptive_flexfield_name = 'Action Information DF' AND
flex_act.descriptive_flex_context_code = v_element_name AND
(flex_act.enabled_flag IS NULL OR flex_act.enabled_flag = 'Y') and
not exists ( select /*+ no_unnest */ 1
from
fnd_descr_flex_col_usage_vl flex_ele
where
flex_ele.application_id = g_application_id AND
flex_ele.descriptive_flexfield_name = 'Element Entry Developer DF' AND
flex_ele.descriptive_flex_context_code = v_classification_name AND
(flex_ele.enabled_flag IS NULL OR flex_ele.enabled_flag ='Y') AND
flex_ele.end_user_column_name = flex_act.end_user_column_name ) AND
not Exists (
SELECT /*+ no_unnest */ 1
FROM pay_input_values_f piv,
pay_element_entry_values_f peev
WHERE piv.input_value_id = peev.input_value_id AND
piv.element_type_id = v_element_type_id AND
peev.element_entry_id = v_element_entry_id AND
peev.effective_start_date = v_effective_start_date AND
UPPER(TRANSLATE(substr(piv.NAME,1,30),' ', '_') ) =
UPPER(TRANSLATE(flex_act.end_user_column_name, ' ', '_')));
select to_number(global_value)
from ff_globals_f
where global_name = p_name
and legislation_code = 'GB'
and p_date between effective_start_date and effective_end_date;
select ACTUAL_TERMINATION_DATE
from per_periods_of_service pps,
per_assignments_f paf
where paf.PERIOD_OF_SERVICE_ID = pps.PERIOD_OF_SERVICE_ID
and paf.assignment_id =p_assignment_id;
select max(effective_end_date)
from per_all_assignments_f
where assignment_id = p_assignment_id;
PROCEDURE insert_sum_records(p_assactid NUMBER)
IS
BEGIN
if to_number(g_param_rep_run) < 2005
then
pay_action_information_api.create_action_information(
p_action_information_id => l_action_info_id,
p_action_context_id => p_assactid,
p_action_context_type => 'AAP',
p_object_version_number => l_ovn,
p_effective_date => fnd_date.canonical_to_date(g_param_benefit_end_date),
p_source_id => NULL,
p_source_text => NULL,
p_action_information_category => 'GB P11D ASSIGNMENT RESULTA',
p_action_information1 => per_formula_functions.get_text('A_DESC'),
p_action_information2 => per_formula_functions.get_number('A_COST'),
p_action_information3 => per_formula_functions.get_number('A_AMG'),
p_action_information4 => per_formula_functions.get_number('A_CE'),
p_action_information5 => per_formula_functions.get_text('B_DESC'),
p_action_information6 => per_formula_functions.get_number('B_CE'),
p_action_information7 => per_formula_functions.get_number('B_TNP'),
p_action_information8 => per_formula_functions.get_number('C_COST'),
p_action_information9 => per_formula_functions.get_number('C_AMG'),
p_action_information10 => per_formula_functions.get_number('C_CE'),
p_action_information11 => per_formula_functions.get_number('D_CE'),
p_action_information12 => calculate_amap_ce ,
p_action_information13 => per_formula_functions.get_number('F_TCCE'),
p_action_information14 => per_formula_functions.get_number('F_TFCE'),
p_action_information15 => per_formula_functions.get_number('G_CE'),
p_action_information16 => per_formula_functions.get_number('I_COST'),
p_action_information17 => per_formula_functions.get_number('I_AMG'),
p_action_information18 => per_formula_functions.get_number('I_CE'),
p_action_information19 => per_formula_functions.get_number('J_CE'),
p_action_information20 => per_formula_functions.get_number('K_COST'),
p_action_information21 => per_formula_functions.get_number('K_AMG'),
p_action_information22 => per_formula_functions.get_number('K_CE'),
p_action_information23 => per_formula_functions.get_text('L_DESC'),
p_action_information24 => per_formula_functions.get_number('L_COST'),
p_action_information25 => per_formula_functions.get_number('L_AMG'),
p_action_information26 => per_formula_functions.get_number('L_CE'),
p_action_information27 => per_formula_functions.get_text('M_SHARES'),
p_action_information28 => per_formula_functions.get_number('H_CE1'),
p_action_information29 => per_formula_functions.get_number('H_COUNT'),
p_action_information30 => per_formula_functions.get_number('F_COUNT') );
This procedure updates the global variable g_updated_flag value
to 'Y' if any of the multiple assignments are updated with the
summed up value of all the assignments till now.
*****************************************************************/
PROCEDURE update_flag_var (p_ass_act_id IN NUMBER)
IS
l_payroll_action_id NUMBER(15);
l_updated VARCHAR2(10) := 'N';
select paa.payroll_action_id, paaf.person_id
from pay_assignment_actions paa,
per_all_assignments_f paaf
where paa.assignment_action_id = p_ass_act_id
and paa.assignment_id = paaf.assignment_id;
cursor c_get_updated_status (c_person_id in number,
c_payroll_action_id in number) is
select 'Y'
from dual
where exists (select 'X'
from pay_action_information pai,
pay_assignment_actions paa,
per_all_assignments_f paaf
where paaf.person_id = c_person_id
and paaf.assignment_id = paa.assignment_id
and paa.payroll_action_id = c_payroll_action_id
and paa.assignment_action_id = pai.action_context_id
and pai.action_information24 = 'Y'
);
hr_utility.TRACE('Entering update_flag_var procedure');
open c_get_updated_status (l_person_id, l_payroll_action_id);
fetch c_get_updated_status into l_updated;
hr_utility.TRACE('Value of l_updated: '||l_updated);
close c_get_updated_status;
if l_updated = 'Y' then
hr_utility.TRACE('Inside if condition');
g_updated_flag := 'Y';
hr_utility.TRACE('Leaving update_flag_var procedure');
END update_flag_var;
SELECT action_information1, action_information2, action_information3, action_information4,
action_information5, action_information6, action_information7, action_information8,
action_information9, action_information10, action_information11, action_information12,
action_information13, action_information14, action_information15, action_information16,
action_information17, action_information18, action_information19, action_information20,
action_information21, action_information22, action_information23, action_information24,
action_information25, action_information26, action_information27, action_information28,
action_information29, action_information30
INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
l_col5_val, l_col6_val, l_col7_val, l_col8_val,
l_col9_val, l_col10_val, l_col11_val, l_col12_val,
l_col13_val, l_col14_val, l_col15_val, l_col16_val,
l_col17_val, l_col18_val, l_col19_val, l_col20_val,
l_col21_val, l_col22_val, l_col23_val, l_col24_val,
l_col25_val, l_col26_val, l_col27_val, l_col28_val,
l_col29_val, l_col30_val
FROM pay_action_information
WHERE action_context_id = p_assignment_action_id AND
action_information_category = 'GB P11D ASSIGNMENT RESULTA'
AND action_context_type = 'AAP';
SELECT action_information1, action_information2, action_information3, action_information4,
action_information5, action_information6, action_information7, action_information8,
action_information9, action_information10, action_information11, action_information12,
action_information13, action_information14, action_information15, action_information16,
action_information17, action_information18, action_information19, action_information20,
action_information21, action_information22, action_information23, action_information24,
action_information25, action_information26, action_information27, action_information28,
action_information29, action_information30
INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
l_col5_val, l_col6_val, l_col7_val, l_col8_val,
l_col9_val, l_col10_val, l_col11_val, l_col12_val,
l_col13_val, l_col14_val, l_col15_val, l_col16_val,
l_col17_val, l_col18_val, l_col19_val, l_col20_val,
l_col21_val, l_col22_val, l_col23_val, l_col24_val,
l_col25_val, l_col26_val, l_col27_val, l_col28_val,
l_col29_val, l_col30_val
FROM pay_action_information
WHERE action_context_id = p_assignment_action_id AND
action_information_category ='GB P11D ASSIGNMENT RESULTB'
AND action_context_type = 'AAP';
SELECT action_information1, action_information2, action_information3, action_information4,
action_information5, action_information6, action_information7, action_information8,
action_information9, action_information10, action_information11, action_information12,
action_information13, action_information14, action_information15, action_information16,
action_information17, action_information18, action_information19, action_information20,
action_information21, action_information22, action_information23, action_information24,
action_information25, action_information26, action_information27, action_information28,
action_information29, action_information30
INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
l_col5_val, l_col6_val, l_col7_val, l_col8_val,
l_col9_val, l_col10_val, l_col11_val, l_col12_val,
l_col13_val, l_col14_val, l_col15_val, l_col16_val,
l_col17_val, l_col18_val, l_col19_val, l_col20_val,
l_col21_val, l_col22_val, l_col23_val, l_col24_val,
l_col25_val, l_col26_val, l_col27_val, l_col28_val,
l_col29_val, l_col30_val
FROM pay_action_information
WHERE action_context_id = p_assignment_action_id AND
action_information_category ='GB P11D ASSIGNMENT RESULTC'
AND action_context_type = 'AAP';
update_flag_var(p_assignment_action_id);
SELECT action_information1, action_information2, action_information3, action_information4,
action_information5, action_information6, action_information7, action_information8,
action_information9, action_information10, action_information11, action_information12,
action_information13, action_information14, action_information15, action_information16,
action_information17, action_information18, action_information19, action_information20,
action_information21, action_information22, action_information23, action_information24,
action_information25, action_information26, action_information28, action_information29,
action_information30
INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
l_col5_val, l_col6_val, l_col7_val, l_col8_val,
l_col9_val, l_col10_val, l_col11_val, l_col12_val,
l_col13_val, l_col14_val, l_col15_val, l_col16_val,
l_col17_val, l_col18_val, l_col19_val, l_col20_val,
l_col21_val, l_col22_val, l_col23_val, l_col24_val,
l_col25_val, l_col26_val, l_col28_val, l_col29_val,
l_col30_val
FROM pay_action_information
WHERE action_context_id = p_assignment_action_id AND
action_information_category = 'GB P11D ASSIGNMENT RESULTA'
AND action_context_type = 'AAP';
SELECT action_information1, action_information2, action_information3, action_information4,
action_information5, action_information6, action_information7, action_information8,
action_information9, action_information10, action_information11, action_information12,
action_information13, action_information14, action_information15, action_information16,
action_information17, action_information18, action_information19, action_information20,
action_information21, action_information22, action_information23, action_information24,
action_information25, action_information26, action_information27, action_information28,
action_information29, action_information30
INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
l_col5_val, l_col6_val, l_col7_val, l_col8_val,
l_col9_val, l_col10_val, l_col11_val, l_col12_val,
l_col13_val, l_col14_val, l_col15_val, l_col16_val,
l_col17_val, l_col18_val, l_col19_val, l_col20_val,
l_col21_val, l_col22_val, l_col23_val, l_col24_val,
l_col25_val, l_col26_val, l_col27_val, l_col28_val,
l_col29_val, l_col30_val
FROM pay_action_information
WHERE action_context_id = p_assignment_action_id AND
action_information_category ='GB P11D ASSIGNMENT RESULTB'
AND action_context_type = 'AAP';
SELECT action_information1, action_information2, action_information3, action_information4,
action_information5, action_information6, action_information7, action_information8,
action_information9, action_information12,
action_information13, action_information14, action_information15, action_information16,
action_information17, action_information18, action_information19, action_information20,
action_information21, action_information22, action_information23
INTO l_col1_val, l_col2_val, l_col3_val, l_col4_val,
l_col5_val, l_col6_val, l_col7_val, l_col8_val,
l_col9_val, l_col12_val,
l_col13_val, l_col14_val, l_col15_val, l_col16_val,
l_col17_val, l_col18_val, l_col19_val, l_col20_val,
l_col21_val, l_col22_val, l_col23_val
FROM pay_action_information
WHERE action_context_id = p_assignment_action_id AND
action_information_category ='GB P11D ASSIGNMENT RESULTC'
AND action_context_type = 'AAP';
PROCEDURE update_value_act_info_id(p_action_info_id NUMBER,
p_action_info_category VARCHAR2,
p_ovn IN OUT nocopy NUMBER)
IS
BEGIN
/* The code below can be removed when do P11D for year 05/06 */
if to_number(g_param_rep_run) < 2005
then
IF p_action_info_category = 'GB P11D ASSIGNMENT RESULTA'
THEN
pay_action_information_api.update_action_information(
p_action_information_id => p_action_info_id,
p_object_version_number => p_ovn,
p_action_information1 => per_formula_functions.get_text('A_DESC'),
p_action_information2 => per_formula_functions.get_number('A_COST'),
p_action_information3 => per_formula_functions.get_number('A_AMG'),
p_action_information4 => per_formula_functions.get_number('A_CE'),
p_action_information5 => per_formula_functions.get_text('B_DESC'),
p_action_information6 => per_formula_functions.get_number('B_CE'),
p_action_information7 => per_formula_functions.get_number('B_TNP'),
p_action_information8 => per_formula_functions.get_number('C_COST'),
p_action_information9 => per_formula_functions.get_number('C_AMG'),
p_action_information10 => per_formula_functions.get_number('C_CE'),
p_action_information11 => per_formula_functions.get_number('D_CE'),
p_action_information12 => calculate_amap_ce ,
p_action_information13 => per_formula_functions.get_number('F_TCCE'),
p_action_information14 => per_formula_functions.get_number('F_TFCE'),
p_action_information15 => per_formula_functions.get_number('G_CE'),
p_action_information16 => per_formula_functions.get_number('I_COST'),
p_action_information17 => per_formula_functions.get_number('I_AMG'),
p_action_information18 => per_formula_functions.get_number('I_CE'),
p_action_information19 => per_formula_functions.get_number('J_CE'),
p_action_information20 => per_formula_functions.get_number('K_COST'),
p_action_information21 => per_formula_functions.get_number('K_AMG'),
p_action_information22 => per_formula_functions.get_number('K_CE'),
p_action_information23 => per_formula_functions.get_text('L_DESC'),
p_action_information24 => per_formula_functions.get_number('L_COST'),
p_action_information25 => per_formula_functions.get_number('L_AMG'),
p_action_information26 => per_formula_functions.get_number('L_CE'),
p_action_information27 => per_formula_functions.get_text('M_SHARES'),
p_action_information28 => per_formula_functions.get_number('H_CE1'),
p_action_information29 => per_formula_functions.get_number('H_COUNT'),
p_action_information30 => per_formula_functions.get_number('F_COUNT') );
pay_action_information_api.update_action_information(
p_action_information_id => p_action_info_id,
p_object_version_number => p_ovn,
p_action_information1 => per_formula_functions.get_text('N_DESC'),
p_action_information2 => per_formula_functions.get_number('N_COST'),
p_action_information3 => per_formula_functions.get_number('N_AMG'),
p_action_information4 => per_formula_functions.get_number('N_CE'),
p_action_information5 => per_formula_functions.get_text('NA_DESC'),
p_action_information6 => per_formula_functions.get_number('NA_COST'),
p_action_information7 => per_formula_functions.get_number('NA_AMG'),
p_action_information8 => per_formula_functions.get_number('NA_CE'),
p_action_information9 => per_formula_functions.get_number('N_TAXPAID'),
p_action_information10 => per_formula_functions.get_number('O1_COST'),
p_action_information11 => per_formula_functions.get_number('O1_AMG'),
p_action_information12 => per_formula_functions.get_number('O1_CE'),
p_action_information13 => per_formula_functions.get_number('O2_COST'),
p_action_information14 => per_formula_functions.get_number('O2_AMG'),
p_action_information15 => per_formula_functions.get_number('O2_CE'),
p_action_information16 => per_formula_functions.get_text('O_TOI'),
p_action_information17 => per_formula_functions.get_number('O3_COST'),
p_action_information18 => per_formula_functions.get_number('O3_AMG'),
p_action_information19 => per_formula_functions.get_number('O3_CE'),
p_action_information20 => per_formula_functions.get_number('O4_COST'),
p_action_information21 => per_formula_functions.get_number('O4_AMG'),
p_action_information22 => per_formula_functions.get_number('O4_CE'),
p_action_information23 => per_formula_functions.get_number('O5_COST'),
p_action_information24 => per_formula_functions.get_number('O5_AMG'),
p_action_information25 => per_formula_functions.get_number('O5_CE'),
p_action_information26 => per_formula_functions.get_text('O6_DESC'),
p_action_information27 => per_formula_functions.get_number('O6_COST'),
p_action_information28 => per_formula_functions.get_number('O6_AMG'),
p_action_information29 => per_formula_functions.get_number('O6_CE') );
pay_action_information_api.update_action_information(
p_action_information_id => p_action_info_id,
p_object_version_number => p_ovn,
p_action_information1 => per_formula_functions.get_number('C_BUS_MILES'),
p_action_information2 => per_formula_functions.get_number('M_BUS_MILES'),
p_action_information3 => per_formula_functions.get_number('B_BUS_MILES'),
p_action_information4 => per_formula_functions.get_number('C_RATE1'),
p_action_information5 => per_formula_functions.get_number('C_RATE2'),
p_action_information6 => per_formula_functions.get_number('M_RATE1'),
p_action_information7 => per_formula_functions.get_number('M_RATE2'),
p_action_information8 => per_formula_functions.get_number('B_RATE1'),
p_action_information9 => per_formula_functions.get_number('B_RATE2'),
p_action_information10 => per_formula_functions.get_text('DT_FREE_FUEL_WITHDRAWN'),
p_action_information11 => per_formula_functions.get_text('FREE_FUEL_REINSTATED'),
p_action_information12 => per_formula_functions.get_number('C_MILEAGE_PAYMENTS'),
p_action_information13 => per_formula_functions.get_number('B_MILEAGE_PAYMENTS'),
p_action_information14 => per_formula_functions.get_number('M_MILEAGE_PAYMENTS'),
p_action_information15 => per_formula_functions.get_number('MARORS_COUNT'),
p_action_information16 => per_formula_functions.get_number('C_TAX_DEDUCTED'),
p_action_information17 => per_formula_functions.get_number('B_TAX_DEDUCTED'),
p_action_information18 => per_formula_functions.get_number('M_TAX_DEDUCTED'),
p_action_information19 => per_formula_functions.get_number('PASSENGER_PAYMENTS'),
p_action_information20 => per_formula_functions.get_number('PASSENGER_BUS_MILES'),
p_action_information21 => per_formula_functions.get_number('PASSENGER_BUS_MILE_AMT'),
p_action_information22 => per_formula_functions.get_number('MILEAGE_ALLOWANCE_RELIEF'),
p_action_information23 => per_formula_functions.get_number('INT_MAX_AMT_OUTSTANDING')
);
pay_action_information_api.update_action_information(
p_action_information_id => p_action_info_id,
p_object_version_number => p_ovn,
p_action_information1 => per_formula_functions.get_text('A_DESC'),
p_action_information2 => per_formula_functions.get_number('A_COST'),
p_action_information3 => per_formula_functions.get_number('A_AMG'),
p_action_information4 => per_formula_functions.get_number('A_CE'),
p_action_information5 => per_formula_functions.get_text('B_DESC'),
p_action_information6 => per_formula_functions.get_number('B_CE'),
p_action_information7 => per_formula_functions.get_number('B_TNP'),
p_action_information8 => per_formula_functions.get_number('C_COST'),
p_action_information9 => per_formula_functions.get_number('C_AMG'),
p_action_information10 => per_formula_functions.get_number('C_CE'),
p_action_information11 => per_formula_functions.get_number('D_CE'),
p_action_information12 => calculate_amap_ce ,
p_action_information13 => per_formula_functions.get_number('F_TCCE'),
p_action_information14 => per_formula_functions.get_number('F_TFCE'),
p_action_information15 => per_formula_functions.get_number('G_CE'),
p_action_information16 => per_formula_functions.get_number('I_COST'),
p_action_information17 => per_formula_functions.get_number('I_AMG'),
p_action_information18 => per_formula_functions.get_number('I_CE'),
p_action_information19 => per_formula_functions.get_number('J_CE'),
p_action_information20 => per_formula_functions.get_number('K_COST'),
p_action_information21 => per_formula_functions.get_number('K_AMG'),
p_action_information22 => per_formula_functions.get_number('K_CE'),
p_action_information23 => per_formula_functions.get_text('L_DESC'),
p_action_information24 => per_formula_functions.get_number('L_COST'),
p_action_information25 => per_formula_functions.get_number('L_AMG'),
p_action_information26 => per_formula_functions.get_number('L_CE'),
p_action_information27 => null,
p_action_information28 => per_formula_functions.get_number('H_CE1'),
p_action_information29 => per_formula_functions.get_number('H_COUNT'),
p_action_information30 => per_formula_functions.get_number('F_COUNT') );
pay_action_information_api.update_action_information(
p_action_information_id => p_action_info_id,
p_object_version_number => p_ovn,
p_action_information1 => per_formula_functions.get_text('M_DESC'),
p_action_information2 => per_formula_functions.get_number('M_COST'),
p_action_information3 => per_formula_functions.get_number('M_AMG'),
p_action_information4 => per_formula_functions.get_number('M_CE'),
p_action_information5 => per_formula_functions.get_text('MA_DESC'),
p_action_information6 => per_formula_functions.get_number('MA_COST'),
p_action_information7 => per_formula_functions.get_number('MA_AMG'),
p_action_information8 => per_formula_functions.get_number('MA_CE'),
p_action_information9 => per_formula_functions.get_number('M_TAXPAID'),
p_action_information10 => per_formula_functions.get_number('N1_COST'),
p_action_information11 => per_formula_functions.get_number('N1_AMG'),
p_action_information12 => per_formula_functions.get_number('N1_CE'),
p_action_information13 => per_formula_functions.get_number('N2_COST'),
p_action_information14 => per_formula_functions.get_number('N2_AMG'),
p_action_information15 => per_formula_functions.get_number('N2_CE'),
p_action_information16 => per_formula_functions.get_text('N_TOI'),
p_action_information17 => per_formula_functions.get_number('N3_COST'),
p_action_information18 => per_formula_functions.get_number('N3_AMG'),
p_action_information19 => per_formula_functions.get_number('N3_CE'),
p_action_information20 => per_formula_functions.get_number('N4_COST'),
p_action_information21 => per_formula_functions.get_number('N4_AMG'),
p_action_information22 => per_formula_functions.get_number('N4_CE'),
p_action_information23 => per_formula_functions.get_number('N5_COST'),
p_action_information24 => per_formula_functions.get_number('N5_AMG'),
p_action_information25 => per_formula_functions.get_number('N5_CE'),
p_action_information26 => per_formula_functions.get_text('N6_DESC'),
p_action_information27 => per_formula_functions.get_number('N6_COST'),
p_action_information28 => per_formula_functions.get_number('N6_AMG'),
p_action_information29 => per_formula_functions.get_number('N6_CE')
,p_action_information30 => per_formula_functions.get_number('G_FCE'));
pay_action_information_api.update_action_information(
p_action_information_id => p_action_info_id,
p_object_version_number => p_ovn,
p_action_information1 => per_formula_functions.get_number('C_BUS_MILES'),
p_action_information2 => per_formula_functions.get_number('M_BUS_MILES'),
p_action_information3 => per_formula_functions.get_number('B_BUS_MILES'),
p_action_information4 => per_formula_functions.get_number('C_RATE1'),
p_action_information5 => per_formula_functions.get_number('C_RATE2'),
p_action_information6 => per_formula_functions.get_number('M_RATE1'),
p_action_information7 => per_formula_functions.get_number('M_RATE2'),
p_action_information8 => per_formula_functions.get_number('B_RATE1'),
p_action_information9 => per_formula_functions.get_number('B_RATE2'),
p_action_information10 => null,
p_action_information11 => null,
p_action_information12 => per_formula_functions.get_number('C_MILEAGE_PAYMENTS'),
p_action_information13 => per_formula_functions.get_number('B_MILEAGE_PAYMENTS'),
p_action_information14 => per_formula_functions.get_number('M_MILEAGE_PAYMENTS'),
p_action_information15 => per_formula_functions.get_number('MARORS_COUNT'),
p_action_information16 => per_formula_functions.get_number('C_TAX_DEDUCTED'),
p_action_information17 => per_formula_functions.get_number('B_TAX_DEDUCTED'),
p_action_information18 => per_formula_functions.get_number('M_TAX_DEDUCTED'),
p_action_information19 => per_formula_functions.get_number('PASSENGER_PAYMENTS'),
p_action_information20 => per_formula_functions.get_number('PASSENGER_BUS_MILES'),
p_action_information21 => per_formula_functions.get_number('PASSENGER_BUS_MILE_AMT'),
p_action_information22 => per_formula_functions.get_number('MILEAGE_ALLOWANCE_RELIEF'),
p_action_information23 => per_formula_functions.get_number('INT_MAX_AMT_OUTSTANDING'),
p_action_information24 => 'Y' --Updating the below parameter value for the bug fix 8864717.
);
PROCEDURE update_values(p_assignment_action_id NUMBER)
IS
l_ovn NUMBER;
SELECT action_information_id, object_version_number
INTO l_action_info_id, p_ovn
FROM pay_action_information
WHERE action_context_id = p_assignment_action_id AND
action_information_category = p_action_info_category;
update_value_act_info_id(l_action_info_id, 'GB P11D ASSIGNMENT RESULTA', l_ovn);
update_value_act_info_id(l_action_info_id, 'GB P11D ASSIGNMENT RESULTB', l_ovn);
update_value_act_info_id(l_action_info_id, 'GB P11D ASSIGNMENT RESULTC', l_ovn);
select ppf.national_identifier
from per_assignments_f paf,
per_all_people_f ppf,
per_periods_of_service pps
where ppf.person_id = paf.person_id
and paf.assignment_id = p_assid
and paf.period_of_service_id = pps.period_of_service_id(+)
and p_eff_date between paf.effective_start_date and paf.effective_end_date
and least(nvl(pps.actual_termination_date,
fnd_date.canonical_to_date(g_param_benefit_end_date)),
fnd_date.canonical_to_date(g_param_benefit_end_date))
between ppf.effective_start_date and ppf.effective_end_date;
select hr_gb_utility.ni_validate(l_nat_number,p_eff_date)
into l_var_value
from dual;
update pay_action_information
set action_information4 = null
where action_information_category = 'EMPLOYEE DETAILS'
and action_context_type = 'AAP'
and action_context_id = p_assactid
and assignment_id = p_assid;
select NVL(UPPER(pai_person.action_information5), ' '), -- addr line 1
NVL(UPPER(pai_person.action_information6), ' '), -- addr line 2
NVL(UPPER(pai_person.action_information7), ' '), -- addr line 3
NVL(UPPER(pai_person.action_information8), ' '), -- addr line 4
NVL(UPPER(hl.meaning), ' ') -- addr line 5
from pay_action_information pai_person,
hr_lookups hl
where pai_person.action_context_id = p_assignment_action_id
and pai_person.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_information14 = 'Employee Address'
and pai_person.action_context_type = 'AAP'
and hl.lookup_type(+) = 'GB_COUNTY'
and hl.lookup_code(+) = pai_person.action_information9;
select p1.full_name,
p1.first_name,
p1.last_name,
p1.middle_names
from per_all_assignments_f a,
per_all_people_f p,
per_all_people_f p1
where a.assignment_id = p_assignment_id
and p.person_id = a.person_id
and p1.person_id = a.person_id
and p_asg_end_date between p.effective_start_date and p.effective_end_date
and p_ben_end_date between p1.effective_start_date and p1.effective_end_date
and (nvl(p1.first_name,' ') <> nvl(p.first_name,' ')
or
nvl(p1.last_name,' ') <> nvl(p.last_name,' ')
or
nvl(p1.middle_names,' ') <> nvl(p.middle_names,' ') );
select a1.address_line1,
a1.address_line2,
a1.address_line3,
a1.town_or_city,
a1.region_1,
a1.region_2,
a1.region_3,
a1.postal_code,
a1.country
from pay_action_information a,
per_addresses a1,
per_all_assignments_f per
where per.assignment_id = p_assignment_id
and p_asg_end_date between per.effective_start_date and per.effective_end_date
and a1.person_id = per.person_id
/*
and a1.date_from = (select max(a2.date_from)
from per_addresses a2
where a2.primary_flag = 'Y'
and a2.person_id = a1.person_id)
*/
and sysdate between a1.date_from and nvl(a1.date_to, hr_general.end_of_time)
and a1.primary_flag = 'Y'
and per.assignment_id = a.assignment_id
and a.action_information_category = 'ADDRESS DETAILS'
and a.action_context_type = 'AAP'
and a.action_context_id = p_assignment_act_id
and (nvl(a.action_information5,' ') <> nvl(a1.address_line1,' ') or
nvl(a.action_information6,' ') <> nvl(a1.address_line2,' ') or
nvl(a.action_information7,' ') <> nvl(a1.address_line3,' ') or
nvl(a.action_information8,' ') <> nvl(a1.town_or_city,' ') or
nvl(a.action_information9,' ') <> nvl(a1.region_1,' ') or
nvl(a.action_information10,' ')<> nvl(a1.region_2,' ') or
nvl(a.action_information11,' ')<> nvl(a1.region_3,' ') or
nvl(a.action_information12,' ')<> nvl(a1.postal_code,' ') or
nvl(a.action_information13,' ')<> nvl(a1.country,' '));
update pay_action_information
set action_information1 = l_fu_name
where action_information_category = 'EMPLOYEE DETAILS'
and action_context_type = 'AAP'
and action_context_id = p_assignment_act_id;
update pay_action_information
set action_information6 = l_fi_name,
action_information7 = l_m_names,
action_information8 = l_l_name
where action_information_category = 'GB EMPLOYEE DETAILS'
and action_context_type = 'AAP'
and action_context_id = p_assignment_act_id;
update pay_action_information
set action_information5 = l_addr1,
action_information6 = l_addr2,
action_information7 = l_addr3,
action_information8 = l_toc,
action_information9 = l_reg1,
action_information10= l_reg2,
action_information11= l_reg3,
action_information12= l_poc,
action_information13= l_country
where action_information_category = 'ADDRESS DETAILS'
and action_context_type = 'AAP'
and action_context_id = p_assignment_act_id
and assignment_id = p_assignment_id;
l_sql_stmt := 'Select ' || entry_flex_values.application_column_name || ' from ';
hr_utility.trace('Inserting summed records ');
insert_sum_records(p_assactid);
hr_utility.trace('Calling Update values..');
hr_utility.trace('Update values ' || l_processed_assign_actions(l_tab_counter) );
update_values(l_processed_assign_actions(l_tab_counter) );
SELECT pai.action_information7 action_information7, COUNT(1) temp_num
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_emp
WHERE ppa.payroll_action_id = pactid
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_information_category = 'EMEA PAYROLL INFO'
AND pai.action_context_type = 'AAP'
AND pai_emp.action_context_id = paa.assignment_action_id
AND pai_emp.action_information_category = 'EMPLOYEE DETAILS'
AND pai_emp.action_context_type = 'AAP'
AND (SUBSTR(pai_emp.action_information4, 1, 2) = 'TN'
OR
pai_emp.action_information4 IS NULL)
GROUP BY pai.action_information7;
SELECT pai.action_information7 action_information7, COUNT(1) tot_num
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai
WHERE ppa.payroll_action_id = pactid
AND paa.payroll_action_id = ppa.payroll_action_id
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_information_category = 'EMEA PAYROLL INFO'
AND pai.action_context_type = 'AAP'
AND pai.action_information7 = p_employer_name
GROUP BY pai.action_information7;
SELECT COUNT(DISTINCT pai_ben.action_information10)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_information pai_ben
WHERE ppa.payroll_action_id = pactid
AND ppa.payroll_action_id = paa .payroll_action_id
AND pai_ben.action_context_id = paa.assignment_action_id
AND pai_ben.action_information_category = 'EXPENSES PAYMENTS'
AND pai_ben.action_context_type = 'AAP'
HAVING COUNT(DISTINCT pai_ben.action_information10) > 1;
SELECT pai_comp.action_information7 employers_name,
SUM(DECODE(
pai.action_information_category,
'ASSETS TRANSFERRED', pai.action_information9,
'LIVING ACCOMMODATION', pai.action_information10,
'CAR AND CAR FUEL 2003_04',NVL(pai.action_information10, 0) + NVL(pai.action_information11, 0),
'VANS 2002_03', NVL(pai.action_information15, 0),
'INT FREE AND LOW INT LOANS', pai.action_information11,
'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
'RELOCATION EXPENSES', pai.action_information5,
'SERVICES SUPPLIED', pai.action_information7,
'ASSETS AT EMP DISPOSAL', pai.action_information9,
'OTHER ITEMS', pai.action_information9,
-- 'EXPENSES PAYMENTS', pai.action_information8,
'0') ) p11db_value
FROM pay_action_information pai_comp,
pay_action_information pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid
AND ppa.payroll_action_id = paa .payroll_action_id
AND pai_comp.action_context_id = paa.assignment_action_id
AND pai_comp.action_information_category = 'EMEA PAYROLL INFO'
AND pai.action_context_id = paa.assignment_action_id
GROUP BY pai_comp.action_information7;
select ppa.action_information6,
ppa.action_information7,
ppa.action_information8,
ppa.action_information9,
ppa.action_information10,
ppa.action_information11,
ppa.action_information12
from pay_assignment_actions paa,
pay_action_information ppa
where paa.payroll_action_id = pactid
and paa.assignment_action_id = ppa.action_context_id
and ppa.action_information_category = 'GB EMPLOYEE DETAILS'
and ppa.action_context_type = 'AAP'
order by ppa.action_information12;
select action_information7
from pay_assignment_actions paa,
pay_action_information pai
where paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'EMEA PAYROLL INFO'
and pai.action_context_type = 'AAP'
group by action_information7;
select * from (
select /*+ ORDERED use_nl(paf,paa,pai,pai_a,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2)
use_index(pai_a,pay_action_information_n2)*/
pai.action_information_category name,
sum(decode(pai.action_information_category,
'ASSETS TRANSFERRED', pai.action_information9,
'PAYMENTS MADE FOR EMP', pai.action_information7,
'VOUCHERS OR CREDIT CARDS', pai.action_information11,
'LIVING ACCOMMODATION', pai.action_information10, --Changed for bug 8204969
'MILEAGE ALLOWANCE AND PPAYMENT', pai_a.action_information12,
'CAR AND CAR FUEL 2003_04', pai.action_information10,
'VANS 2002_03',pai.action_information15,
'VANS 2005', pai.action_information15,
'VANS 2007', pai.action_information14,
'INT FREE AND LOW INT LOANS', pai.action_information11,
'PVT MED TREATMENT OR INSURANCE', pai.action_information7,
'RELOCATION EXPENSES', pai.action_information5,
'SERVICES SUPPLIED', pai.action_information7,
'ASSETS AT EMP DISPOSAL', pai.action_information9,
'OTHER ITEMS', pai.action_information9,
'OTHER ITEMS NON 1A', pai.action_information9,
'EXPENSES PAYMENTS', pai.action_information8)) total,
decode(pai.action_information_category,
'ASSETS TRANSFERRED', 'A',
'PAYMENTS MADE FOR EMP', 'B',
'VOUCHERS OR CREDIT CARDS', 'C',
'LIVING ACCOMMODATION', 'D',
'MILEAGE ALLOWANCE AND PPAYMENT', 'E',
'CAR AND CAR FUEL 2003_04', 'F',
'VANS 2005', 'G',
'VANS 2007', 'O',
'VANS 2002_03', 'G',
'INT FREE AND LOW INT LOANS', 'H',
'PVT MED TREATMENT OR INSURANCE', 'I',
'RELOCATION EXPENSES', 'J',
'SERVICES SUPPLIED', 'K',
'ASSETS AT EMP DISPOSAL', 'L',
'OTHER ITEMS', 'M',
'OTHER ITEMS NON 1A', 'M',
'EXPENSES PAYMENTS', 'N') cat,
count(*) no_of_entries
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_a,
pay_action_information pai_person
where paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = pai.action_information_category
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_person.action_information9 = p_emp_name --p_employer_name
and pai_person.action_context_type = 'AAP'
and pai_a.action_context_id = paa.assignment_action_id
and pai_a.action_context_type = 'AAP'
and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
group by pai.action_information_category)
where cat in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O')
order by cat;
select sum(decode(pai.action_information_category,
'INT FREE AND LOW INT LOANS', pai.action_information11)) total
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_a,
pay_action_information pai_person
where paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_person.action_information9 = p_emp_name --p_employer_name
and pai_person.action_context_type = 'AAP'
and pai_a.action_context_id = paa.assignment_action_id
and pai_a.action_context_type = 'AAP'
and pai_a.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
and not exists (select 1
from pay_action_information pai_max
where pai_max.action_context_id = paa.assignment_action_id
and nvl(pai_max.ACTION_INFORMATION23,0) < 5000
and pai_max.action_context_type = 'AAP'
and pai_max.action_information_category = 'GB P11D ASSIGNMENT RESULTC');
select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
-- modified for Bug 12314320
sum(nonclassA) from
(select
max(pai.action_information12) as nonclassA --Modified for the bug 11727875
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person,
pay_action_information pai_add
where paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'GB P11D ASSIGNMENT RESULTA'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_person.action_information9 = p_emp_name --p_employer_name
and pai_add.action_context_id = paa.assignment_action_id
and pai_add.action_information_category = 'ADDRESS DETAILS'
and pai_person.action_context_type = 'AAP' group by pai_add.action_information1);
select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
sum(pai.action_information11) -- Cash Equivalent For Fuel
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'CAR AND CAR FUEL 2003_04'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_person.action_information9 = p_emp_name --p_employer_name
and pai_person.action_context_type = 'AAP';
select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
sum(pai.action_information30) -- Cash Equivalent For Fuel
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'VANS 2007'
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_person.action_information9 = p_emp_name --p_employer_name
and pai_person.action_context_type = 'AAP';
select count(*)
from (select /*+ ORDERED use_nl(paf,paa,pai,pai_person)
use_index(pai_person,pay_action_information_n2)
use_index(pai,pay_action_information_n2) */
pai_person.action_information10 -- Person id
from pay_assignment_actions paa,
pay_action_information pai,
pay_action_information pai_person
where paa.payroll_action_id = p_pact_id
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = p_category
and pai_person.action_context_id = paa.assignment_action_id
and pai_person.action_information_category = 'GB EMPLOYEE DETAILS'
and pai_person.action_information9 = p_emp_name --p_employer_name
and pai_person.action_context_type = 'AAP'
group by pai_person.action_information10);
select distinct
pap.full_name,
paf.assignment_number,
pml.message_level,
substr(pml.line_text,instr(pml.line_text,':') + 2) line_text,
substr(pml.line_text,6,instr(pml.line_text,':') - 6) element_name,
pml.line_sequence
from pay_payroll_actions pay,
pay_assignment_actions paa,
per_all_assignments_f paf,
per_all_people_f pap,
per_periods_of_service pos,
pay_message_lines pml
where pay.payroll_action_id = p_pact_id
and paa.payroll_action_id = pay.payroll_action_id
and pml.source_id = paa.assignment_action_id
and pml.message_level = p_msg_typ
and pml.source_type = 'A'
and substr(line_text,1,5) = 'P11D '
and substr(line_text,6,5) <> 'Error'
and paf.assignment_id = paa.assignment_id
and pap.person_id = paf.person_id
and pos.period_of_service_id(+) = paf.period_of_service_id
and nvl(pos.actual_termination_date, pay.effective_date) between
pap.effective_start_date and pap.effective_end_date
order by paf.assignment_number, element_name, pml.line_sequence;
select length(l_msg) into l_msg_length from dual;
select instr(l_msg,' ',1,l_count) into l_temp from dual;
select substr(l_msg,1,l_pos), substr(l_msg,l_pos + 1) into l_out, l_msg from dual;
select length(l_msg) into l_msg_length from dual;