[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select prrv.result_value
from pay_run_result_values prrv,
pay_run_results prr
where prr.source_id = c_element_entry_id
and prr.element_type_id = g_ni_id
and prr.assignment_action_id = c_assignment_action_id
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = g_scon_input_id;
SELECT scon.screen_entry_value
FROM
pay_element_entry_values_f scon,
pay_element_entry_values_f cat
WHERE scon.element_entry_id = p_element_entry_id
AND cat.element_entry_id = p_element_entry_id
AND cat.effective_start_date = scon.effective_start_date
AND cat.effective_end_date = scon.effective_end_date
AND scon.input_value_id +0 = g_scon_input_id
AND cat.input_value_id +0 = g_category_input_id
AND scon.screen_entry_value IS NOT NULL
ORDER BY decode(cat.screen_entry_value,p_category,0,1),
ABS(p_effective_date - scon.effective_end_date);
SELECT element_type_id
INTO g_ni_id
FROM pay_element_types_f
WHERE element_name = 'NI'
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO g_category_input_id
FROM pay_input_values_f
WHERE name = 'Category'
AND element_type_id = g_ni_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO g_scon_input_id
FROM pay_input_values_f
WHERE name = 'SCON'
AND element_type_id = g_ni_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT fai.VALUE
INTO l_arch_value
FROM ff_archive_item_contexts aic1,
ff_archive_item_contexts aic2,
ff_archive_item_contexts aic3,
ff_archive_items fai
WHERE fai.context1 = p_action_id
AND fai.user_entity_id = p_user_entity_id
AND aic1.archive_item_id = fai.archive_item_id
AND aic1.sequence_no = 1
AND aic1.context = p_context_value1
AND aic2.archive_item_id = fai.archive_item_id
AND aic2.sequence_no = 2
AND aic2.context = p_context_value2
AND aic3.archive_item_id = fai.archive_item_id
AND aic3.sequence_no = 3
AND aic3.context = p_context_value3;
SELECT fai.VALUE
INTO l_arch_value
FROM ff_archive_items fai,
ff_archive_item_contexts aic1,
ff_archive_item_contexts aic2
WHERE fai.context1 = p_action_id
AND fai.user_entity_id = p_user_entity_id
AND aic1.archive_item_id = fai.archive_item_id
AND aic1.sequence_no = 1
AND aic1.context = p_context_value1
AND aic2.archive_item_id = fai.archive_item_id
AND aic2.sequence_no = 2
AND aic2.context = p_context_value2;
SELECT fai.VALUE
INTO l_arch_value
FROM ff_archive_item_contexts aic1,
ff_archive_items fai
WHERE fai.context1 = p_action_id
AND fai.user_entity_id = p_user_entity_id
AND aic1.archive_item_id = fai.archive_item_id
AND aic1.sequence_no = 1
AND aic1.context = p_context_value1;
SELECT fai.VALUE
INTO l_arch_value
FROM ff_archive_items fai
WHERE fai.context1 = p_action_id
AND fai.user_entity_id = p_user_entity_id;
SELECT fue.user_entity_id
INTO l_user_entity_id
FROM ff_user_entities fue
WHERE fue.user_entity_name = p_user_entity_name
AND fue.legislation_code= 'GB';
select to_number(faic.context) payroll_id
from ff_archive_items fai,
ff_archive_item_contexts faic
where fai.context1 = c_payroll_action_id
and fai.user_entity_id = c_user_entity_id
and fai.archive_item_id = faic.archive_item_id
and faic.sequence_no = 1;
SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = papf.payroll_id
AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_tax_ref
AND paaf.effective_start_date <= p_proll_eff_date;
SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
FROM per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.assignment_id = p_asg_id
AND paaf.payroll_id = papf.payroll_id
AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_tax_ref
AND paaf.effective_start_date <= p_proll_eff_date;
SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = papf.payroll_id
AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 <> p_tax_ref
AND l_min_active-1 BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT 'Y'
FROM per_all_assignments_f paaf1,
per_assignment_status_types past1,
pay_all_payrolls_f papf1,
hr_soft_coding_keyflex flex1
WHERE paaf1.period_of_service_id = l_pos_id
AND paaf1.assignment_id <> p_asg_id
AND l_min_active BETWEEN paaf1.effective_start_date
AND paaf1.effective_end_date
AND paaf1.assignment_status_type_id = past1.assignment_status_type_id
AND past1.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf1.payroll_id = papf1.payroll_id
AND p_proll_eff_date BETWEEN papf1.effective_start_date and papf1.effective_end_date
AND papf1.soft_coding_keyflex_id = flex1.soft_coding_keyflex_id
AND flex1.segment1 = p_tax_ref
AND EXISTS ( SELECT 1
FROM per_all_assignments_f paaf2,
per_assignment_status_types past2,
pay_all_payrolls_f papf2,
hr_soft_coding_keyflex flex2
WHERE paaf2.assignment_id = paaf1.assignment_id
AND l_min_active-1 BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date
AND paaf2.assignment_status_type_id = past2.assignment_status_type_id
AND past2.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf2.payroll_id = papf2.payroll_id
AND p_proll_eff_date BETWEEN papf2.effective_start_date and papf2.effective_end_date
AND papf2.soft_coding_keyflex_id = flex2.soft_coding_keyflex_id
AND flex2.segment1 = l_old_paye_ref);
SELECT min(paaf.effective_start_date) min_active
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.person_id = l_person_id
AND (l_min_active-1) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = papf.payroll_id
AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_tax_ref;
SELECT max(paaf.effective_end_date) last_end_date, max(person_id) person_id
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = papf.payroll_id
AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_tax_ref
AND paaf.effective_start_date <= p_proll_eff_date;
SELECT max(paaf.effective_start_date) first_st_date, max(person_id) person_id, max(period_of_service_id) pos_id
FROM per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.assignment_id = p_asg_id
AND paaf.payroll_id = papf.payroll_id
AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_tax_ref
AND paaf.effective_start_date <= p_proll_eff_date;
SELECT 'Y' term_and_xfer, flex.segment1 old_paye_ref
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = papf.payroll_id
AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 <> p_tax_ref
AND l_min_active-1 BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT 'Y'
FROM per_all_assignments_f paaf1,
per_assignment_status_types past1,
pay_all_payrolls_f papf1,
hr_soft_coding_keyflex flex1
WHERE paaf1.period_of_service_id = l_pos_id
AND paaf1.assignment_id <> p_asg_id
AND l_min_active BETWEEN paaf1.effective_start_date
AND paaf1.effective_end_date
AND paaf1.assignment_status_type_id = past1.assignment_status_type_id
AND past1.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf1.payroll_id = papf1.payroll_id
AND p_proll_eff_date BETWEEN papf1.effective_start_date and papf1.effective_end_date
AND papf1.soft_coding_keyflex_id = flex1.soft_coding_keyflex_id
AND flex1.segment1 = p_tax_ref
AND EXISTS ( SELECT 1
FROM per_all_assignments_f paaf2,
per_assignment_status_types past2,
pay_all_payrolls_f papf2,
hr_soft_coding_keyflex flex2
WHERE paaf2.assignment_id = paaf1.assignment_id
AND l_min_active-1 BETWEEN paaf2.effective_start_date
AND paaf2.effective_end_date
AND paaf2.assignment_status_type_id = past2.assignment_status_type_id
AND past2.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf2.payroll_id = papf2.payroll_id
AND p_proll_eff_date BETWEEN papf2.effective_start_date and papf2.effective_end_date
AND papf2.soft_coding_keyflex_id = flex2.soft_coding_keyflex_id
AND flex2.segment1 = l_old_paye_ref);
SELECT max(paaf.effective_end_date) max_active
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.person_id = l_person_id
AND (l_max_active+1) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = papf.payroll_id
AND p_proll_eff_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_tax_ref;
SELECT
to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy')
-- add_months(to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy'),12)
-- End of BUG 5671777-5
start_year,
effective_date end_year,
business_group_id,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'PERMIT'),1,12) permit,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TAX_REF'),1,3) tax_dist,
substr(ltrim(substr(pay_gb_eoy_archive.get_parameter(
legislative_parameters,'TAX_REF'),4,11),'/'),1,10) tax_ref, -- 4011263: tax ref can be 10 chars long
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TEST'),1,1) test_indicator,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'UNIQUE_TEST_ID'),1,8) unique_test_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT
p.payroll_id payroll_id,
substr(flex.segment10,1,12) permit_number,
p.payroll_name payroll_name,
substr(flex.segment1,1,3) tax_district_reference,
substr(ltrim(substr(org_information1,4,11),'/') ,1,10) tax_reference,
flex.segment1 emp_paye_ref,
substr(org.org_information2 ,1,40) tax_district_name,
substr(ltrim(org.org_information3),1,36) employers_name, -- 4011263: added ltrim
substr(ltrim(org.org_information4),1,60) employers_address_line, -- 4011263: added ltrim
substr(nvl(flex.segment14,org.org_information7),1,9) econ
/* Start 4011263
,
flex.segment11 * 100 smp_recovered,
flex.segment12 * 100 smp_compensation,
flex.segment13 * 100 ssp_recovered,
flex.segment15 * 100 sap_recovered,
flex.segment16 * 100 sap_compensation,
flex.segment17 * 100 spp_recovered,
flex.segment18 * 100 spp_compensation
End 4011263 */
FROM pay_all_payrolls_f p,
hr_soft_coding_keyflex flex,
hr_organization_information org
WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND org.org_information_context = 'Tax Details References'
AND org.org_information1 = flex.segment1
AND NVL(org.org_information10,'UK') = 'UK'
AND flex.segment10 IS NOT NULL
AND p.business_group_id = p_bg_id
AND org.organization_id = p_bg_id
AND p_end_year BETWEEN p.effective_start_date
AND p.effective_end_date;
SELECT flex.segment1
FROM pay_all_payrolls_f p,
hr_soft_coding_keyflex flex
WHERE p.soft_coding_keyflex_id = flex.soft_coding_keyflex_id(+)
AND p.payroll_id = p_payroll_id
AND p.effective_start_date <= hr_gbbal.span_end(p_end_year)
AND p.effective_end_date >= hr_gbbal.span_start(p_end_year)
AND nvl(flex.segment1, 'XYZ') <> nvl(p_paye_ref, 'ABC');
SELECT
min(start_date) start_year,
max(end_date) end_year,
max(period_type) period_type,
max(period_num) max_period_number
FROM per_time_periods ptp
WHERE ptp.payroll_id = p_payroll_id
AND ptp.regular_payment_date BETWEEN p_start_year
AND p_end_year;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'GB'
AND business_group_id IS NULL;
sqlstr := 'SELECT DISTINCT person_id
FROM per_all_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
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'PERMIT'),1,12) permit,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TAX_REF'),1,3) tax_dist,
substr(ltrim(substr(pay_gb_eoy_archive.get_parameter(
legislative_parameters,'TAX_REF'),4,11),'/'),1,10) tax_ref, --4011263
effective_date end_year,
business_group_id,
ltrim(substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'ASG_SET'),1,80)) asg_set
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'GB'
AND business_group_id IS NULL;
select context_id
from ff_contexts
where context_name = c_context_name;
SELECT 1 valid_asg
FROM per_all_assignments_f paf
WHERE paf.rowid = chartorowid(p_asg_rowid)
AND paf.effective_end_date >= p_start_date
AND paf.effective_start_date <= p_end_date
AND NOT EXISTS (select 1
from per_all_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
AND paf2.effective_end_date > paf.effective_end_date
AND paf2.effective_end_date >=
fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str
(pactid, p_start_year_eid,to_char(paf2.payroll_id)))
AND paf2.effective_start_date <=
fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str
(pactid, p_payroll_end_year_eid, to_char(paf2.payroll_id))));
SELECT /*+ ORDERED INDEX (asg PER_ASSIGNMENTS_F_N12,
ppf PAY_PAYROLLS_F_PK,
flex HR_SOFT_CODING_KEYFLEX_PK,
org HR_ORGANIZATION_INFORMATIO_FK1,
per PER_PEOPLE_F_PK)
USE_NL(asg,ppf,flex,org,per) */
asg.assignment_id,
asg.effective_start_date,
asg.effective_end_date,
asg.person_id,
asg.period_of_service_id, -- added for bug 3784871
pay_gb_eoy_archive.get_agg_active_start(asg.assignment_id, flex.segment1, p_end_date) agg_active_start,
pay_gb_eoy_archive.get_agg_active_end(asg.assignment_id, flex.segment1, p_end_date) agg_active_end,
asg.payroll_id,
substr(ltrim(substr(org_information1,4,11),'/') ,1,10) tax_ref, -- 4011263
decode(per.per_information9,'Y','Y',NULL) multiple_asg_flag,
rowidtochar(asg.ROWID) charrowid,
'N' tax_ref_xfer
FROM per_all_assignments_f asg,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex flex,
hr_organization_information org,
per_all_people_f per
WHERE asg.person_id BETWEEN stperson AND endperson
AND asg.business_group_id +0 = p_bg_id
AND asg.effective_end_date >= p_min_start_year_date
AND asg.effective_start_date <= p_max_end_year_date
AND asg.payroll_id = ppf.payroll_id
AND asg.period_of_service_id is not null
AND asg.ASSIGNMENT_TYPE <> 'A' -- Bug : 12804623
AND p_end_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND org.organization_id +0 = p_bg_id
AND org.org_information_context =
'Tax Details References'||decode(flex.segment1,'','','')
AND org.org_information1 = flex.segment1
AND nvl(org.org_information10,'UK') = 'UK'
AND nvl(p_permit,substr(flex.segment10,1,12)) =
substr(flex.segment10,1,12)
AND nvl(p_tax_dist_ref, substr(flex.segment1,1,3)) =
substr(flex.segment1,1,3)
AND nvl(p_tax_ref, substr(ltrim(substr(org_information1,4,11),'/') ,1,10))
= substr(ltrim(substr(org_information1,4,11),'/') ,1,10)
AND per.person_id = asg.person_id
AND p_end_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND (p_asg_set_id IS NULL -- don't check for assignment set in this case
OR EXISTS (SELECT 1 FROM hr_assignment_sets has1
WHERE has1.assignment_set_id = p_asg_set_id
AND has1.business_group_id = asg.business_group_id
AND nvl(has1.payroll_id, asg.payroll_id) = asg.payroll_id
AND (NOT EXISTS (SELECT 1 -- chk no amendmts
FROM hr_assignment_set_amendments hasa1
WHERE hasa1.assignment_set_id =
has1.assignment_set_id)
OR EXISTS (SELECT 1 -- chk include amendmts
FROM hr_assignment_set_amendments hasa2
WHERE hasa2.assignment_set_id =
has1.assignment_set_id
AND hasa2.assignment_id = asg.assignment_id
AND nvl(hasa2.include_or_exclude,'I') = 'I')
OR (NOT EXISTS (SELECT 1 --chk no exlude amendmts
FROM hr_assignment_set_amendments hasa3
WHERE hasa3.assignment_set_id =
has1.assignment_set_id
AND hasa3.assignment_id = asg.assignment_id
AND nvl(hasa3.include_or_exclude,'I') = 'E')
AND NOT EXISTS (SELECT 1 --and chk no Inc amendmts
FROM hr_assignment_set_amendments hasa4
WHERE hasa4.assignment_set_id =
has1.assignment_set_id
AND nvl(hasa4.include_or_exclude,'I') = 'I') ) -- end checking exclude amendmts
) -- done checking amendments
) -- done asg set check when not null
) -- end of asg set check
UNION
SELECT /*+ ORDERED INDEX (PASS PER_ASSIGNMENTS_F_N12,
ASS PER_ASSIGNMENTS_F_PK,
NROLL PAY_PAYROLLS_F_PK,
FLEX HR_SOFT_CODING_KEYFLEX_PK,
PROLL PAY_PAYROLLS_F_PK,
pflex HR_SOFT_CODING_KEYFLEX_PK,
per PER_PEOPLE_F_PK)
USE_NL(PASS,ASS,NROLL,FLEX,PROLL,pflex,per) */
pass.assignment_id,
pass.effective_start_date,
pass.effective_end_date,
pass.person_id,
pass.period_of_service_id, -- added for bug 3784871
pay_gb_eoy_archive.get_agg_active_start(pass.assignment_id, pflex.segment1, p_end_date) agg_active_start,
pay_gb_eoy_archive.get_agg_active_end(pass.assignment_id, pflex.segment1, p_end_date) agg_active_end,
pass.payroll_id,
substr(ltrim(substr(pflex.segment1,4,11),'/') ,1,10) tax_ref, -- 4011263
decode(per.per_information9,'Y','Y',NULL) multiple_asg_flag,
rowidtochar(pass.rowid) charrowid,
'Y' tax_ref_xfer
FROM
per_all_people_f per
,per_all_assignments_f PASS
,per_all_assignments_f ASS
,pay_all_payrolls_f NROLL
,hr_soft_coding_keyflex FLEX
,pay_all_payrolls_f PROLL
,hr_soft_coding_keyflex pflex
WHERE NROLL.payroll_id = ASS.payroll_id
AND ASS.effective_start_date between
NROLL.effective_start_date and NROLL.effective_end_date
AND NROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
AND ASS.assignment_id = PASS.assignment_id
AND ASS.period_of_service_id is not null
AND PASS.effective_end_date = (ASS.effective_start_date - 1)
AND PASS.ASSIGNMENT_TYPE <> 'A' -- Bug : 12804623
AND PROLL.payroll_id = PASS.payroll_id
AND PER.person_id BETWEEN stperson AND endperson
AND pass.business_group_id +0 = p_bg_id
AND pass.effective_end_date >= p_min_start_year_date
AND pass.effective_start_date <= p_max_end_year_date
AND ASS.effective_start_date between
PROLL.effective_start_date AND PROLL.effective_end_date
AND PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
AND ASS.payroll_id <> PASS.payroll_id
AND FLEX.segment1 <> PFLEX.segment1
AND nvl(p_permit,substr(pflex.segment10,1,12)) =
substr(pflex.segment10,1,12)
AND nvl(p_tax_dist_ref, substr(pflex.segment1,1,3)) =
substr(pflex.segment1,1,3)
AND nvl(p_tax_ref, substr(ltrim(substr(pflex.segment1,4,11),'/') ,1,10))
= substr(ltrim(substr(pflex.segment1,4,11),'/') ,1,10)
AND per.person_id = pass.person_id
AND p_end_date BETWEEN per.effective_start_date
AND per.effective_end_date
AND (p_asg_set_id IS NULL -- don't check for assignment set in this case
OR EXISTS (SELECT 1 FROM hr_assignment_sets has1
WHERE has1.assignment_set_id = p_asg_set_id
AND has1.business_group_id = pass.business_group_id
AND nvl(has1.payroll_id, pass.payroll_id) = pass.payroll_id
AND (NOT EXISTS (SELECT 1 -- chk no amendmts
FROM hr_assignment_set_amendments hasa1
WHERE hasa1.assignment_set_id =
has1.assignment_set_id)
OR EXISTS (SELECT 1 -- chk include amendmts
FROM hr_assignment_set_amendments hasa2
WHERE hasa2.assignment_set_id =
has1.assignment_set_id
AND hasa2.assignment_id = pass.assignment_id
AND nvl(hasa2.include_or_exclude,'I') = 'I')
OR (NOT EXISTS (SELECT 1 --chk no exlude amendmts
FROM hr_assignment_set_amendments hasa3
WHERE hasa3.assignment_set_id =
has1.assignment_set_id
AND hasa3.assignment_id = pass.assignment_id
AND nvl(hasa3.include_or_exclude,'I') = 'E')
AND NOT EXISTS (SELECT 1 --and chk no Inc amendmts
FROM hr_assignment_set_amendments hasa4
WHERE hasa4.assignment_set_id =
has1.assignment_set_id
AND nvl(hasa4.include_or_exclude,'I') = 'I')
) -- end checking exclude amendmts
) -- done checking amendments
) -- done asg set check when not null
) -- end of asg set check
ORDER BY 4,5,6,7,8,1,3 desc;
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
SELECT defined_balance_id
FROM pay_defined_balances db,
pay_balance_types b,
pay_balance_dimensions d
WHERE b.balance_name = p_balance_name
AND d.dimension_name = p_dimension_name
AND db.balance_type_id = b.balance_type_id
AND db.balance_dimension_id = d.balance_dimension_id;
SELECT
to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy')
-- add_months(to_date('06/04/'||to_char(start_date,'YYYY'),'dd/mm/yyyy'),12)
-- End of BUG 5671777-5
start_year,
effective_date end_year,
business_group_id,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'PERMIT'),1,12) permit,
substr(pay_gb_eoy_archive.get_parameter(legislative_parameters,
'TAX_REF'),1,3) tax_dist,
substr(ltrim(substr(pay_gb_eoy_archive.get_parameter(
legislative_parameters,'TAX_REF'),4,11),'/'),1,10) tax_ref --4011263
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = p_entity_name
AND legislation_code = 'GB'
AND business_group_id IS NULL;
SELECT ptpt.number_per_fiscal_year
FROM per_time_period_types ptpt
WHERE p_period_type = ptpt.period_type;
SELECT
to_number(aic.context) payroll_id,
fnd_date.canonical_to_date(fai.VALUE) start_year,
fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str(fai.context1,
g_payroll_end_year_eid,
aic.context)) end_year,
pay_gb_eoy_archive.get_arch_str(fai.context1,g_payroll_period_type_eid,
aic.context) period_type,
to_number(pay_gb_eoy_archive.get_arch_str(fai.context1,
g_max_period_number_eid, aic.context)) max_period_number,
pay_gb_eoy_archive.get_arch_str(fai.context1,g_tax_ref_eid,
aic.context) tax_ref,
pay_gb_eoy_archive.get_arch_str(fai.context1,g_tax_dist_ref_eid,
aic.context) tax_dist
FROM ff_archive_item_contexts aic, /* payrolls */
ff_archive_items fai, /* X_START_YEAR */
ff_user_entities fue,
pay_payroll_actions pact
WHERE pact.report_type = 'EOY'
AND pact.report_qualifier = 'GB'
AND pact.action_type = 'X'
AND pact.payroll_action_id = fai.context1
AND fue.user_entity_name = 'X_START_YEAR'
AND fue.legislation_code = 'GB'
AND fue.business_group_id IS NULL
AND fue.user_entity_id = fai.user_entity_id
AND aic.archive_item_id = fai.archive_item_id
AND aic.sequence_no = 1
AND pact.payroll_action_id = p_pactid;
SELECT act.assignment_action_id, act.action_status
FROM pay_assignment_actions act
WHERE act.payroll_action_id = p_payroll_action_id
AND act.action_status = 'M';
SELECT act2.assignment_action_id, asg2.assignment_number, asg1.assignment_number retry_asg_number, pap.full_name, act2.action_status
FROM pay_assignment_actions act1,
pay_assignment_actions act2,
per_all_assignments_f asg1,
per_all_assignments_f asg2,
per_all_people_f pap
WHERE act1.assignment_action_id = p_asg_act_id
AND act1.assignment_id = asg1.assignment_id
AND asg1.person_id = pap.person_id
AND g_end_year between pap.effective_start_date and pap.effective_end_date
AND pap.person_id = asg2.person_id
AND asg2.assignment_id = act2.assignment_id
AND act2.payroll_action_id = act1.payroll_action_id
AND asg2.assignment_id <> asg1.assignment_id
AND (pap.per_information10 = 'Y' -- Agg PAYE
OR pap.per_information9 = 'Y') -- NI Muti Asg
AND act2.action_status <> 'M';
SELECT element_type_id
INTO g_paye_details_id
FROM pay_element_types_f
WHERE element_name = 'PAYE Details'
AND g_end_year BETWEEN effective_start_date AND effective_end_date;
SELECT element_type_id
INTO g_paye_element_id
FROM pay_element_types_f
WHERE element_name = 'PAYE'
AND g_end_year BETWEEN effective_start_date AND effective_end_date;
SELECT element_type_id
INTO g_ni_id
FROM pay_element_types_f
WHERE element_name = 'NI'
AND g_end_year BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO g_category_input_id
FROM pay_input_values_f
WHERE name = 'Category'
AND element_type_id = g_ni_id
AND g_end_year BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO g_process_type_id
FROM pay_input_values_f
WHERE name = 'Process Type'
AND element_type_id = g_ni_id
AND g_end_year BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO g_scon_input_id
FROM pay_input_values_f
WHERE name = 'SCON'
AND element_type_id = g_ni_id
AND g_end_year BETWEEN effective_start_date AND effective_end_date;
SELECT act.assignment_id,
fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str
(act.assignment_action_id,
g_effective_end_date_eid)) end_date,
nvl(pay_gb_eoy_archive.get_arch_str(act.assignment_action_id,
g_tax_ref_transfer_eid),'N') tax_ref_transfer,
nvl(fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str
(act.assignment_action_id,
g_agg_active_start_eid)), hr_api.g_sot) agg_active_start,
nvl(fnd_date.canonical_to_date(pay_gb_eoy_archive.get_arch_str
(act.assignment_action_id,
g_agg_active_end_eid)), hr_api.g_eot) agg_active_end
FROM pay_assignment_actions act
WHERE act.assignment_action_id = p_asgactid;
SELECT min(paaf.effective_start_date) min_active, max(paaf.effective_end_date) max_active
FROM per_all_assignments_f paaf,
per_assignment_status_types past,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
WHERE paaf.assignment_id = p_asg_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND paaf.payroll_id = papf.payroll_id
AND paaf.effective_start_date BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = p_tax_ref;
SELECT distinct greatest(ppos.final_process_date,ppos.last_standard_process_date)
FROM per_all_assignments_f paaf,
per_periods_of_service ppos
WHERE paaf.assignment_id = p_assid
AND paaf.period_of_service_id = ppos.period_of_service_id;
SELECT ass.payroll_id,
ass.assignment_number,
ass.person_id,
ass.organization_id,
ass.location_id,
ass.people_group_id,
ass.period_of_service_id
FROM per_all_assignments_f ass
WHERE ass.assignment_id = p_assid
AND ass.effective_end_date = p_eff_end_date;
SELECT ass.effective_end_date,
ass.payroll_id,
ass.assignment_number,
ass.person_id,
ass.organization_id,
ass.location_id,
ass.people_group_id,
ass.period_of_service_id
FROM per_all_assignments_f ass
WHERE ass.assignment_id = p_assid
AND ass.effective_end_date < p_eff_end_date
ORDER BY ass.effective_end_date desc;
SELECT ass.effective_end_date,
ass.payroll_id,
ass.assignment_number,
ass.person_id,
ass.organization_id,
ass.location_id,
ass.people_group_id,
ass.period_of_service_id
FROM per_all_assignments_f ass
WHERE ass.assignment_id = p_assid
AND p_eff_end_date BETWEEN
ass.effective_start_date AND ass.effective_end_date;
SELECT max(ass.effective_start_date)
FROM per_all_assignments_f ass
,pay_all_payrolls_f nroll
,hr_soft_coding_keyflex flex
,per_all_assignments_f pass
,pay_all_payrolls_f proll
,hr_soft_coding_keyflex pflex
WHERE ass.assignment_id = p_asg_id
AND ass.effective_start_date < p_asg_end
AND nroll.payroll_id = ass.payroll_id
AND ass.effective_start_date BETWEEN
nroll.effective_start_date AND nroll.effective_end_date
AND nroll.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND ass.assignment_id = pass.assignment_id
AND pass.effective_end_date = (ass.effective_start_date - 1)
AND pass.effective_end_date >=
fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
(g_payroll_action_id, 'X_START_YEAR', to_char(pass.payroll_id)))
AND pass.effective_start_date <=
fnd_date.canonical_to_date(pay_gb_eoy_archive.get_cached_value
(g_payroll_action_id, 'X_END_YEAR', to_char(pass.payroll_id)))
AND proll.payroll_id = pass.payroll_id
AND pass.payroll_id <> ass.payroll_id
AND ass.effective_start_date BETWEEN
proll.effective_start_date AND proll.effective_end_date
AND proll.soft_coding_keyflex_id = pflex.soft_coding_keyflex_id
AND flex.segment1 <> pflex.segment1;
SELECT actual_termination_date , last_standard_process_date, 'L' termination_type
FROM per_periods_of_service pos
WHERE pos.period_of_service_id = p_service_id
AND pos.actual_termination_date IS NOT NULL
AND pos.actual_termination_date
<= least(p_asg_end,g_end_year);
SELECT start_date
FROM per_time_periods ptp
WHERE payroll_id = p_payroll_id
AND regular_payment_date BETWEEN g_start_year AND g_end_year
AND period_num = l_payroll_max_period_number;
SELECT /*+ USE_NL(paa, pact, ptp) */
to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16)),
max(pact.effective_date) effective_date
FROM pay_assignment_actions paa,
pay_payroll_actions pact,
per_time_periods ptp
WHERE paa.assignment_id = p_asgid
AND paa.payroll_action_id = pact.payroll_action_id
AND pact.time_period_id = ptp.time_period_id
AND pact.action_type IN ('Q','R','B','I','V')
--AND paa.action_status = 'C'
AND paa.action_status in ('C','S') --Modified for the bug 10066755
-- Added decode below for 4318185
AND pact.effective_date <= decode(p_tax_ref_xfer, 'Y', p_asg_end, pact.effective_date)
AND ptp.regular_payment_date
BETWEEN nvl(p_asg_start, p_start_year) AND p_end_year;
SELECT pact.action_type
from pay_payroll_actions pact,
pay_assignment_actions act
where act.assignment_action_id = p_assignment_action_id
and act.payroll_action_id = pact.payroll_action_id;
SELECT substr(last_name, 1,35) last_name,
substr(first_name, 1,35) first_name,
substr(middle_names,1,35) middle_names,
date_of_birth, title,
substr(expense_check_send_to_address,1,1) expense_check_send_to_address,
substr(national_identifier,1,9) national_identifier,
substr(sex,1,1) sex ,
decode(substr(per_information4,1,1),'Y','P',' ') pensioner_indicator,
decode(per_information10,'Y','Y',NULL) agg_paye_flag,
decode(per_information9,'Y','Y',NULL) multiple_asg_flag -- MII
FROM per_all_people_f per
WHERE per.person_id = p_person_id
AND g_end_year BETWEEN per.effective_start_date
AND per.effective_end_date;
SELECT 'D'
FROM dual
WHERE EXISTS (SELECT '1'
FROM per_all_people_f per
WHERE p_person_id = per.person_id
AND per.effective_start_date <= g_end_year
AND per.effective_end_date >= g_start_year
AND substr(per_information2,1,1) = 'Y')
AND EXISTS (SELECT '1'
FROM pay_run_result_values prrv
WHERE input_value_id = g_process_type_id
AND result_value in ('DY', 'DN', 'DP', 'DR', 'PY')
AND run_result_id = (SELECT to_number(substr(max(lpad(to_char(act.action_sequence),15,'0')|| lpad(to_char(prr.run_result_id),19,'0')),16))
FROM pay_payroll_Actions pact,
pay_assignment_actions act,
per_all_assignments_f paf,
pay_run_results prr
WHERE pact.payroll_Action_id = act.payroll_Action_id
AND pact.effective_date BETWEEN g_start_year and g_end_year
--AND act.action_status = 'C'
AND act.action_status in ('C','S') --Modified for the bug 10066755
AND act.assignment_id = paf.assignment_id
AND paf.person_id = p_person_id
AND paf.effective_start_date <= g_end_year
AND paf.effective_end_date >= g_start_year
AND act.assignment_action_id = prr.assignment_action_id
AND prr.element_type_id = g_ni_id
AND pact.action_type IN ('Q', 'R', 'B', 'I')
AND prr.status in ('P', 'PA')));
SELECT ltrim(rtrim(pad.address_line1)) address_line1,
ltrim(rtrim(pad.address_line2)) address_line2,
ltrim(rtrim(pad.address_line3)) address_line3,
ltrim(rtrim(pad.town_or_city)) town_or_city,
substr(l.meaning,1,27) county,
substr(pad.postal_code,1,8),
country
FROM per_addresses pad,
hr_lookups l
WHERE pad.person_id = p_person_id
AND pad.primary_flag = 'Y'
AND l.lookup_type(+) = 'GB_COUNTY'
AND l.lookup_code(+) = pad.region_1
AND sysdate BETWEEN nvl(pad.date_from, sysdate)
AND nvl(pad.date_to, sysdate);
SELECT substr(ftt.territory_short_name, 1, 35) country -- 4011263
FROM fnd_territories_tl ftt
WHERE ftt.territory_code = p_country_code
AND ftt.language = userenv('LANG');
SELECT /*+ RULE
substr(bal.balance_name,4,1) cat_code,
substr(hr_general.decode_lookup('GB_SCON',
decode(substr(bal.balance_name,4,1),
'F',nvl(max(decode(ev_cat.screen_entry_value,
'F',ev_scon.screen_entry_value)),
pay_gb_eoy_archive.get_nearest_scon(
max(ev_scon.element_entry_id), cp_l_asg_id,
'F',max(pact.effective_date))),
'G',nvl(max(decode(ev_cat.screen_entry_value,
'G',ev_scon.screen_entry_value)),
pay_gb_eoy_archive.get_nearest_scon(
max(ev_scon.element_entry_id), cp_l_asg_id,
'G',max(pact.effective_date))),
'S',nvl(max(decode(ev_cat.screen_entry_value,
'S',ev_scon.screen_entry_value)),
pay_gb_eoy_archive.get_nearest_scon(
max(ev_scon.element_entry_id), cp_l_asg_id,
'S',max(pact.effective_date))),
NULL)),1,9) scon,
100*nvl(sum(decode(substr(bal.balance_name,6),'Able',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
able,
100*nvl(sum(decode(substr(bal.balance_name,6),'Total',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
total,
100*nvl(sum(decode(substr(bal.balance_name,6),'Employee',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
employee,
--
-- Bug Fix 678573 Start
--
100*nvl(sum(decode(substr(bal.balance_name,6),'Employer',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
employer,
--
-- Bug Fix 678573 End
--
100*nvl(sum(decode(substr(bal.balance_name,6),'Able ET',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
able_et,
100*nvl(sum(decode(substr(bal.balance_name,6),'Able LEL',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
able_lel,
100*nvl(sum(decode(substr(bal.balance_name,6),'Able UEL',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
able_uel,
-- 8357870 begin
100*nvl(sum(decode(substr(bal.balance_name,6),'Able UAP',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
able_uap,
-- 8357870 end
--EOY 07/08 Begin
100*nvl(sum(decode(substr(bal.balance_name,6),'Able AUEL',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
able_auel,
-- EOY 07/08 End
100*nvl(sum(decode(substr(bal.balance_name,6),'Ers Rebate',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
ers_rebate,
-- Note Ees Rebate only for F category, but zero
-- retrieved in all other cases.
100*nvl(sum(decode(substr(bal.balance_name,6),'Ees Rebate',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
ees_rebate,
100*nvl(sum(decode(substr(bal.balance_name,6),'Rebate to Employee',
fnd_number.canonical_to_number(target.result_value) * feed.scale,0)),0)
rebate_emp
FROM pay_balance_feeds_f feed
,pay_balance_types bal
,pay_run_result_values target
,pay_run_results rr
,pay_element_entry_values_f ev_scon
,pay_element_entry_values_f ev_cat
,pay_element_entries_f e_ni
,pay_element_links_f el_ni
,pay_payroll_actions pact
,pay_assignment_actions assact
,pay_payroll_actions bact
,per_time_periods bptp
,per_time_periods pptp
,pay_assignment_actions bal_assact
WHERE bal_assact.assignment_action_id = cp_l_asg_id
AND bal_assact.payroll_action_id = bact.payroll_action_id
AND feed.balance_type_id = bal.balance_type_id
AND bal.balance_name LIKE 'NI%'
AND substr(bal.balance_name,4,1) IN ('F','G','S')
AND feed.input_value_id = target.input_value_id
AND target.run_result_id = rr.run_result_id
AND nvl(target.result_value,'0') <> '0'
AND rr.assignment_action_id = assact.assignment_action_id
AND e_ni.assignment_id = bal_assact.assignment_id
AND ev_scon.input_value_id +
decode(ev_scon.element_entry_id,NULL,0,0) = cp_scon_inp_val
AND ev_scon.element_entry_id = e_ni.element_entry_id
AND ev_cat.input_value_id +
decode(ev_cat.element_entry_id,NULL,0,0) = cp_cat_inp_val
AND ev_cat.element_entry_id = e_ni.element_entry_id
AND el_ni.element_link_id = e_ni.element_link_id
AND el_ni.element_type_id = cp_element_type
AND pact.effective_date BETWEEN
e_ni.effective_start_date AND e_ni.effective_end_date
AND pact.effective_date BETWEEN
el_ni.effective_start_date AND el_ni.effective_end_date
AND pact.effective_date BETWEEN
ev_scon.effective_start_date AND ev_scon.effective_end_date
AND pact.effective_date BETWEEN
ev_cat.effective_start_date AND ev_cat.effective_end_date
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 bptp.time_period_id = bact.time_period_id
AND pptp.time_period_id = pact.time_period_id
AND pptp.regular_payment_date >= -- fin year start
( to_date('06-04-' || to_char( to_number(
to_char( bptp.regular_payment_date,'YYYY'))
+ decode(sign( bptp.regular_payment_date - to_date('06-04-'
|| to_char(bptp.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
-1,-1,0)),'DD-MM-YYYY'))
AND pact.effective_date >=
--find the latest td payroll transfer date - compare each of the
-- assignment rows with its predecessor looking for the payroll
-- that had a different tax district at that date
( SELECT nvl(max(ass.effective_start_date),
to_date('01-01-0001','DD-MM-YYYY'))
FROM per_all_assignments_f ass
,pay_all_payrolls_f nroll
,hr_soft_coding_keyflex flex
,per_all_assignments_f pass -- previous assignment
,pay_all_payrolls_f proll
,hr_soft_coding_keyflex pflex
WHERE ass.assignment_id = bal_assact.assignment_id
AND nroll.payroll_id = ass.payroll_id
AND ass.effective_start_date BETWEEN
nroll.effective_start_date AND nroll.effective_end_date
AND nroll.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND ass.assignment_id = pass.assignment_id
AND pass.effective_end_date = (ass.effective_start_date - 1)
AND ass.effective_start_date <= bact.effective_date
AND proll.payroll_id = pass.payroll_id
AND ass.effective_start_date BETWEEN
proll.effective_start_date AND proll.effective_end_date
AND proll.soft_coding_keyflex_id = pflex.soft_coding_keyflex_id
AND ass.payroll_id <> pass.payroll_id
AND flex.segment1 <> pflex.segment1)
AND assact.action_sequence <= bal_assact.action_sequence
AND assact.assignment_id = bal_assact.assignment_id
GROUP BY ev_scon.screen_entry_value, substr(bal.balance_name,4,1)
ORDER BY ev_scon.screen_entry_value, substr(bal.balance_name,4,1);
SELECT v.screen_entry_value ni_cat,
substr(hr_general.decode_lookup('GB_SCON',scon.screen_entry_value)
,1,9) ni_scon
FROM pay_element_entries_f e,
pay_element_entry_values_f v,
pay_element_entry_values_f scon,
pay_element_links_f link
WHERE e.assignment_id = p_asgid
AND v.input_value_id + 0 = g_category_input_id
AND v.effective_start_date = scon.effective_start_date
AND v.effective_end_date = scon.effective_end_date
AND v.element_entry_id = scon.element_entry_id
AND scon.input_value_id + 0 = g_scon_input_id
AND link.element_type_id = g_ni_id
AND e.element_link_id = link.element_link_id
AND e.element_entry_id = v.element_entry_id
AND least(l_asg_end,p_end_year)
BETWEEN link.effective_start_date AND link.effective_end_date
AND least(l_asg_end,p_end_year)
BETWEEN e.effective_start_date AND e.effective_end_date
AND least(l_asg_end,p_end_year)
BETWEEN v.effective_start_date AND v.effective_end_date
AND least(l_asg_end,p_end_year)
BETWEEN scon.effective_start_date AND scon.effective_end_date; */
SELECT to_number(substr(max(source_type||lpad(to_char(run_result_id), 19, '0')),2)) -- gets indirect results if present else gets entry results
FROM pay_run_results r
WHERE r.element_type_id = g_paye_details_id
AND r.status IN ('P', 'PA')
AND r.assignment_action_id = p_last_asg_action_id;
SELECT to_number(substr(max(source_type||lpad(to_char(run_result_id), 19, '0')),2)) -- gets indirect results if present else gets entry results
FROM pay_run_results r
WHERE r.element_type_id = g_paye_element_id
AND r.status IN ('P', 'PA', 'O') -- add overridden for SR 4310794.996
AND r.assignment_action_id = p_last_asg_action_id;
SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
pact PAY_PAYROLL_ACTIONS_PK,
r2 PAY_RUN_RESULTS_N50)
USE_NL(assact2, pact, r2) */
to_number(substr(max(lpad(to_char(assact2.action_sequence),15,'0')
||r2.source_type||
lpad(to_char(r2.run_result_id),19,'0')),17)) rr_id,
fnd_date.canonical_to_date(substr(max(lpad(to_char(assact2.action_sequence),15,'0')||
fnd_date.date_to_canonical(pact.effective_date)),16)) eff_date
FROM pay_assignment_actions assact2,
pay_payroll_actions pact,
pay_run_results r2
WHERE assact2.assignment_id = p_assignment_id
AND r2.element_type_id+0 = g_paye_details_id
AND r2.assignment_action_id = assact2.assignment_action_id
AND r2.status IN ('P', 'PA')
AND pact.payroll_action_id = assact2.payroll_action_id
AND pact.action_type IN ( 'Q','R','B','I')
--AND assact2.action_status = 'C'
AND assact2.action_status in ('C','S') --Modified for the bug 10066755
AND pact.effective_date BETWEEN
g_start_year AND g_end_year
/* Bug 4278570 fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_arch_str(
g_payroll_action_id,
g_payroll_start_year_eid,
to_char(pact.payroll_id)))
AND fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_arch_str(
g_payroll_action_id,
g_payroll_end_year_eid,
to_char(pact.payroll_id)))
*/
AND pact.effective_date <= p_asg_last_eff_date
AND NOT EXISTS(
SELECT '1'
FROM pay_action_interlocks pai,
pay_assignment_actions assact3,
pay_payroll_actions pact3
WHERE pai.locked_action_id = assact2.assignment_action_id
AND pai.locking_action_id = assact3.assignment_action_id
AND pact3.payroll_action_id = assact3.payroll_action_id
AND pact3.action_type = 'V'
AND assact3.action_status = 'C');
SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
pact PAY_PAYROLL_ACTIONS_PK,
r2 PAY_RUN_RESULTS_N50)
USE_NL(assact2, pact, r2) */
to_number(substr(max(lpad(to_char(assact2.action_sequence),15,'0')
||r2.source_type||
lpad(to_char(r2.run_result_id),19,'0')),17)) rr_id,
fnd_date.canonical_to_date(substr(max(lpad(to_char(assact2.action_sequence),15,'0')||
fnd_date.date_to_canonical(pact.effective_date)),16)) eff_date
-- to_number(substr(max(lpad(assact2.action_sequence,15,'0')||
-- r2.run_result_id),16))
FROM pay_assignment_actions assact2,
pay_payroll_actions pact,
pay_run_results r2
WHERE assact2.assignment_id = p_assignment_id
AND r2.element_type_id+0 = g_paye_element_id
AND r2.assignment_action_id = assact2.assignment_action_id
AND r2.status IN ('P', 'PA', 'O') -- add overridden for SR 4310794.996
AND pact.payroll_action_id = assact2.payroll_action_id
AND pact.action_type IN ( 'Q','R','B','I')
--AND assact2.action_status = 'C'
AND assact2.action_status in ('C','S') --Modified for the bug 10066755
AND pact.effective_date BETWEEN
g_start_year AND g_end_year
/* Bug 4278570 fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_arch_str(
g_payroll_action_id,
g_payroll_start_year_eid,
to_char(pact.payroll_id)))
AND fnd_date.canonical_to_date
(pay_gb_eoy_archive.get_arch_str(
g_payroll_action_id,
g_payroll_end_year_eid,
to_char(pact.payroll_id)))
*/
AND pact.effective_date <= p_asg_last_eff_date
AND NOT EXISTS(
SELECT '1'
FROM pay_action_interlocks pai,
pay_assignment_actions assact3,
pay_payroll_actions pact3
WHERE pai.locked_action_id = assact2.assignment_action_id
AND pai.locking_action_id = assact3.assignment_action_id
AND pact3.payroll_action_id = assact3.payroll_action_id
AND pact3.action_type = 'V'
AND assact3.action_status = 'C');
p_update_recurring VARCHAR2) IS
SELECT max(decode(iv.name,'Tax Code',screen_entry_value,NULL)) tax_code,
max(decode(iv.name,'Tax Basis',screen_entry_value,NULL)) tax_basis,
100 * max(decode(iv.name,'Pay Previous',
fnd_number.canonical_to_number(screen_entry_value),NULL))
pay_previous,
100 * max(decode(iv.name,'Tax Previous',
fnd_number.canonical_to_number(screen_entry_value),NULL))
tax_previous
FROM pay_element_entries_f e,
pay_element_entry_values_f v,
pay_input_values_f iv,
pay_element_links_f link
WHERE e.assignment_id = p_assignment_id
AND link.element_type_id = g_paye_details_id
AND e.element_link_id = link.element_link_id
AND e.element_entry_id = v.element_entry_id
AND iv.input_value_id = v.input_value_id
AND (e.updating_action_id IS NOT NULL OR p_update_recurring = 'N')
AND least(p_asg_end,p_end_year)
BETWEEN link.effective_start_date AND link.effective_end_date
AND least(p_asg_end,p_end_year)
BETWEEN e.effective_start_date AND e.effective_end_date
AND least(p_asg_end,p_end_year)
BETWEEN iv.effective_start_date AND iv.effective_end_date
AND least(p_asg_end,p_end_year)
BETWEEN v.effective_start_date AND v.effective_end_date;
SELECT max(decode(name,'Tax Code',result_value,NULL)) tax_code,
max(decode(name,'Tax Basis',result_value,NULL)) tax_basis,
100 * to_number(max(decode(name,'Pay Previous',
fnd_number.canonical_to_number(result_value),NULL)))
pay_previous,
100 * to_number(max(decode(name,'Tax Previous',
fnd_number.canonical_to_number(result_value),NULL)))
tax_previous
FROM pay_input_values_f v,
pay_run_result_values rrv
WHERE rrv.run_result_id = p_tax_run_result_id
AND v.input_value_id = rrv.input_value_id
AND v.element_type_id = g_paye_details_id;
SELECT max(decode(name,'Tax Code',result_value,NULL)) tax_code,
max(decode(name,'Tax Basis',result_value,NULL)) tax_basis,
100 * to_number(max(decode(name,'Pay Previous',
fnd_number.canonical_to_number(result_value),NULL)))
pay_previous,
100 * to_number(max(decode(name,'Tax Previous',
fnd_number.canonical_to_number(result_value),NULL)))
tax_previous
FROM pay_input_values_f v,
pay_run_result_values rrv
WHERE rrv.run_result_id = p_tax_run_result_id
AND v.input_value_id = rrv.input_value_id
AND v.element_type_id = g_paye_element_id;
SELECT 1
FROM per_all_assignments_f asg
WHERE asg.person_id = p_person_id
AND asg.effective_start_date < p_year_end
AND asg.effective_end_date >= p_year_start
AND asg.payroll_id <> p_payroll_id
AND p_tax_ref = pay_gb_eoy_archive.get_cached_value(
g_payroll_action_id,'X_TAX_REFERENCE_NUMBER',
to_char(asg.payroll_id))
AND g_permit_number <> nvl(pay_gb_eoy_archive.get_cached_value(
g_payroll_action_id,'X_PERMIT_NUMBER',
to_char(asg.payroll_id)),'?');
SELECT ppa.effective_date
FROM ff_user_entities fue,
ff_archive_items fai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE fue.user_entity_name = 'X_P45_FINAL_PAYMENT_ACTION'
AND fue.user_entity_id = fai.user_entity_id
AND fai.context1 = c_asg_action_id
AND fai.value = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id;
select count(*)
from per_all_assignments_f paaf,
pay_all_payrolls_f papf,
hr_soft_coding_keyflex flex
where paaf.assignment_id = lv_assignment_id
and paaf.payroll_id = lv_payroll_id
and paaf.effective_start_date < l_active_start
and paaf.payroll_id = papf.payroll_id
and papf.effective_start_date < g_start_year
and papf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
and flex.segment1 = lv_tax_ref;
hr_utility.trace('Assignment has been updated or terminated');
hr_utility.trace('ASSIGNMENT UPDATED: '||to_char(l_assignment_id));
'The Assignment has been updated during this process';
END IF; -- tax code null on update recurring PAYE Details entry
END IF; -- tax code null on update recurring PAYE Details entry
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = c_user_entity_name;
INSERT INTO pay_report_format_items_f
(report_type,
report_qualifier,
report_category,
user_entity_id,
effective_start_date,
effective_end_date,
archive_type,
updatable_flag,
display_sequence)
SELECT
'EOY',
'GB',
'F',
l_user_entity_id,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
p_archive_type,
'N',
NULL
FROM sys.dual WHERE NOT EXISTS
(SELECT 1 FROM pay_report_format_items_f
WHERE report_type = 'EOY'
AND report_qualifier = 'GB'
AND user_entity_id = l_user_entity_id
AND report_category = 'F');
INSERT INTO pay_report_format_items_f
(report_type,
report_qualifier,
report_category,
user_entity_id,
effective_start_date,
effective_end_date,
archive_type,
updatable_flag,
display_sequence)
SELECT
'EOY',
'GB',
'P',
l_user_entity_id,
to_date('01/01/0001','DD/MM/YYYY'),
to_date('31/12/4712','DD/MM/YYYY'),
p_archive_type,
'N',
NULL
FROM sys.dual WHERE NOT EXISTS
(SELECT 1 FROM pay_report_format_items_f
WHERE report_type = 'EOY'
AND report_qualifier = 'GB'
AND user_entity_id = l_user_entity_id
AND report_category = 'P');