[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT vehicle_allocation_id,
Vehicle_repository_id,
Usage_type,
effective_start_date,
effective_end_date,
private_use_flag -- Added for the bug 10088866
FROM pqp_vehicle_allocations_f va
WHERE va.assignment_id = p_assignment_id
AND ( (va.effective_start_date BETWEEN g_start_date AND g_end_date
OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
OR (g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
AND 'C' = (SELECT vr.vehicle_type
FROM pqp_vehicle_repository_f vr
WHERE vr.vehicle_repository_id =
va.vehicle_repository_id
AND vr.vehicle_ownership = 'C'
AND va.effective_start_date
BETWEEN vr.effective_start_date
AND vr.effective_end_date)
-- AND va.private_use_flag = 'Y' --Added for bug 9215471 -- Commented for 10088866
ORDER BY vehicle_allocation_id,
effective_start_date;
SELECT min(paaf.assignment_id) assignment_id
FROM pay_all_payrolls_f papf,
hr_soft_coding_keyflex sck,
per_all_assignments_f paaf,
per_all_assignments_f paaf2,
Pqp_vehicle_repository_f vr,
Pqp_vehicle_allocations_f va
WHERE paaf2.assignment_id = p_assignment_id
AND paaf2.person_id = paaf.person_id
AND p_eff_date
BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND papf.payroll_id = paaf.payroll_id
AND p_eff_date
BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.soft_coding_keyflex_id =
sck.soft_coding_keyflex_id
AND sck.segment1 = g_tax_ref
AND paaf.assignment_id = va.assignment_id
AND p_eff_date
BETWEEN va.effective_start_date
AND va.effective_end_date
AND va.usage_type = 'P'
AND va.vehicle_repository_id =
vr.vehicle_repository_id
AND p_eff_date
BETWEEN vr.effective_start_date
AND vr.effective_end_date
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C';
SELECT min(effective_start_Date) min_start_date,
max(effective_end_date) max_end_Date
FROM pqp_vehicle_allocations_f va
WHERE va.vehicle_allocation_id = p_vehicle_allocation_id;
SELECT va.vehicle_allocation_id,
vr.vehicle_repository_id
FROM Pqp_vehicle_repository_f vr,
Pqp_vehicle_allocations_f va
WHERE va.assignment_id = p_assignment_id
AND p_eff_date
BETWEEN va.effective_start_date
AND va.effective_end_date
AND va.usage_type = 'P'
AND va.vehicle_repository_id =
vr.vehicle_repository_id
AND p_eff_date
BETWEEN vr.effective_start_date
AND vr.effective_end_date
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C';
SELECT va.vehicle_allocation_id,
Va.vehicle_repository_id,
vr.make,
vr.model,
vr.engine_capacity_in_cc,
va.effective_end_date
FROM Pqp_vehicle_allocations_f va,
pqp_vehicle_repository_f vr
WHERE va.assignment_id = p_assignment_id
AND va.effective_end_date
BETWEEN (p_new_car_start_date - 30)
AND (p_new_car_start_date - 1)
AND va.usage_type = 'P'
AND va.effective_end_date =
(SELECT max(va2.effective_end_date)
FROM Pqp_vehicle_allocations_f va2
WHERE va2.assignment_id = p_assignment_id
AND va2.effective_end_date
BETWEEN (p_new_car_start_date - 30)
AND (p_new_car_start_date - 1)
AND va2.usage_type = 'P')
AND va.vehicle_repository_id = vr.vehicle_repository_id
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C'
AND va.effective_end_date
BETWEEN vr.effective_start_date
AND vr.effective_end_Date;
SELECT va.vehicle_allocation_id,
Va.vehicle_repository_id,
vr.make,
vr.model,
vr.engine_capacity_in_cc,
va.effective_end_date
FROM Pqp_vehicle_allocations_f va,
pqp_vehicle_repository_f vr
WHERE va.assignment_id = p_assignment_id
AND va.effective_start_date
BETWEEN (p_withdrawn_car_end_date + 1)
AND least((p_withdrawn_car_end_date + 30), g_end_date)
AND va.usage_type = 'P'
AND va.effective_start_date =
(SELECT min(va2.effective_start_date)
FROM Pqp_vehicle_allocations_f va2
WHERE va2.assignment_id = p_assignment_id
AND va2.effective_start_date
BETWEEN (p_withdrawn_car_end_date + 1)
AND least((p_withdrawn_car_end_date + 30), g_end_date)
AND va2.usage_type = 'P')
AND va.vehicle_repository_id = vr.vehicle_repository_id
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C'
AND va.effective_end_date
BETWEEN vr.effective_start_date
AND vr.effective_end_Date;
SELECT effective_start_date,
effective_end_date,
h1.description fuel_type
FROM pqp_vehicle_repository_f vr1,
hr_lookups h1
WHERE vr1.vehicle_repository_id = p_vehicle_repository_id
AND vr1.effective_start_date BETWEEN g_start_date AND g_end_date
AND vr1.fuel_type = h1.lookup_code
AND h1.lookup_type = 'PQP_FUEL_TYPE'
AND h1.enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1))
AND EXISTS (SELECT 1
FROM pqp_vehicle_repository_f vr2,
hr_lookups h2
WHERE vr2.vehicle_repository_id =
p_vehicle_repository_id
AND vr2.effective_end_date =
vr1.effective_start_date-1
AND vr2.fuel_type = h2.lookup_code
AND h2.lookup_type = 'PQP_FUEL_TYPE'
AND h2.enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN trunc(nvl(h2.start_date_active, sysdate-1)) AND trunc(nvl(h2.end_date_active,sysdate+1))
AND decode(h2.description,'B','A'
,'C','A'
,'D','D'
,'L','D'
,'E','E'
,'G','A'
,'H','A'
,'P','A') <> decode(h1.description,'B','A'
,'C','A'
,'D','D'
,'L','D'
,'E','E'
,'G','A'
,'H','A'
,'P','A')
)
--AND vr2.fuel_type <> vr1.fuel_type)
ORDER BY vr1.effective_start_date;
SELECT effective_start_date,
effective_end_date,
h1.description fuel_type
FROM pqp_vehicle_repository_f vr1,
hr_lookups h1
WHERE vr1.vehicle_repository_id = p_vehicle_repository_id
AND vr1.effective_start_date BETWEEN g_start_date AND g_end_date
AND vr1.fuel_type = h1.lookup_code
AND h1.lookup_type = 'PQP_FUEL_TYPE'
AND h1.enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1))
AND EXISTS (SELECT 1
FROM pqp_vehicle_repository_f vr2
WHERE vr2.vehicle_repository_id =
p_vehicle_repository_id
AND vr2.effective_end_date =
vr1.effective_start_date-1
AND vr2.fuel_type <> vr1.fuel_type)
ORDER BY vr1.effective_start_date;
SELECT va.vehicle_allocation_id,
va.vehicle_repository_id,
va.usage_type usage_type,
va.effective_start_date,
va.effective_end_date
FROM Pqp_vehicle_repository_f vr,
Pqp_vehicle_allocations_f va
WHERE va.assignment_id = p_assignment_id
AND p_eff_date BETWEEN va.effective_start_date AND va.effective_end_date
AND va.vehicle_repository_id = vr.vehicle_repository_id
AND p_eff_date BETWEEN vr.effective_start_date AND vr.effective_end_date
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C';
SELECT flex.segment1 tax_ref
FROM hr_soft_coding_keyflex flex,
per_assignments_f asg,
Pay_payrolls_f ppf
WHERE asg.assignment_id = p_assignment_id
AND p_eff_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.payroll_id = ppf.payroll_id
AND p_eff_date BETWEEN ppf.effective_start_Date and ppf.effective_end_date
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id;
SELECT max(va.effective_start_Date)
FROM pqp_vehicle_allocations_f va
WHERE va.vehicle_allocation_id = p_vehicle_aloc_id
and exists (select 1
from pqp_vehicle_allocations_f previous_va
where previous_va.vehicle_allocation_id = va.vehicle_allocation_id
and previous_va.effective_end_date+1 = va.effective_start_Date
and nvl(previous_va.PRIVATE_USE_FLAG,'N') <> va.private_use_flag)
and ((va.effective_start_date BETWEEN g_start_date AND g_end_date
OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
OR (g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) );
SELECT va.effective_start_Date,va.effective_end_Date,va.private_use_flag
FROM pqp_vehicle_allocations_f va
WHERE va.vehicle_allocation_id = p_vehicle_aloc_id
and exists (select 1
from pqp_vehicle_allocations_f previous_va
where previous_va.vehicle_allocation_id = va.vehicle_allocation_id
and previous_va.effective_end_date+1 = va.effective_start_Date
and nvl(previous_va.PRIVATE_USE_FLAG,'N') <> va.private_use_flag)
and ((va.effective_start_date BETWEEN g_start_date AND g_end_date
OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
OR (g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) );
select va.private_use_flag
from pqp_vehicle_allocations_f va
where va.vehicle_allocation_id = p_vehicle_aloc_id
and p_start_date BETWEEN va.effective_start_date AND va.effective_end_date;
SELECT 'Y' found_flag
FROM pay_action_information pai,
pay_assignment_actions act,
pay_payroll_actions ppa
WHERE ppa.report_type = 'P46_CAR_EDI'
AND ppa.report_qualifier='GB'
AND ppa.report_category ='EDI'
AND ppa.action_type = 'X'
AND g_business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = act.payroll_action_id
AND p_assignment_id = act.assignment_id
AND act.assignment_action_id = pai.action_context_id
AND 'AAP' = pai.action_context_type
AND 'GB P46 CAR EDI ALLOCATION' = pai.action_information_category
AND p_action_flag = pai.action_information1
AND to_char(p_vehicle_allocation_id) = pai.action_information2
AND fnd_date.date_to_canonical(p_allocation_start_date) = pai.action_information3;
SELECT 'Y' found_flag
FROM pay_action_information pai,
pay_assignment_actions act,
pay_payroll_actions ppa
WHERE ppa.report_type IN ('P46_CAR_EDI_V2','P46_CAR_EDI','P46_CAR_EDI_V3','P46_CAR_EDI_V4','P46_CAR_EDI_V5') --Bug 10095492: Added V4 --Bug 13400872: Added V5
AND ppa.report_qualifier='GB'
AND ppa.report_category ='EDI'
AND ppa.action_type = 'X'
AND g_business_group_id = ppa.business_group_id
AND ppa.payroll_action_id = act.payroll_action_id
AND p_assignment_id = act.assignment_id
AND act.assignment_action_id = pai.action_context_id
AND 'AAP' = pai.action_context_type
AND 'GB P46 CAR EDI ALLOCATION' = pai.action_information_category
AND p_action_flag = pai.action_information1
AND to_char(p_vehicle_allocation_id) = pai.action_information2
AND (fnd_date.date_to_canonical(p_allocation_start_date) = pai.action_information3
OR fnd_date.date_to_canonical(p_orig_allocation_start_date) = pai.action_information3); -- Modified for the bug 10088866
SELECT
substr(org.org_information3,1,36) employer_name,
substr(org.org_information4,1,60) employer_address_line,
substr(org.org_information2 ,1,40) tax_district_name,
organization_id,
ppa.effective_date
FROM
pay_payroll_actions ppa,
hr_organization_information org
WHERE ppa.payroll_action_id = pactid
AND org.org_information_context = 'Tax Details References'
AND NVL(org.org_information10,'UK') = 'UK'
AND org.organization_id = ppa.business_group_id
AND substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,'TAX_REF=') + 8,
instr(ppa.legislative_parameters||' ',' ',
instr(ppa.legislative_parameters,'TAX_REF=')+8)
- instr(ppa.legislative_parameters,'TAX_REF=') - 8)
= org.org_information1
AND ppa.report_type = 'P46_CAR_EDI'
AND report_qualifier = 'GB'
AND ppa.report_category = 'EDI';
sqlstr := 'select distinct person_id '||
'from per_people_f ppf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
sqlstr := 'select 1 '||
'/* ERROR - Employer Details Fetch failed with: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
SELECT
substr(org.org_information3,1,36) employer_name,
substr(org.org_information4,1,60) employer_address_line,
substr(org.org_information2 ,1,40) tax_district_name,
organization_id,
ppa.effective_date
FROM
pay_payroll_actions ppa,
hr_organization_information org
WHERE ppa.payroll_action_id = pactid
AND org.org_information_context = 'Tax Details References'
AND NVL(org.org_information10,'UK') = 'UK'
AND org.organization_id = ppa.business_group_id
AND substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,'TAX_REF=') + 8,
instr(ppa.legislative_parameters||' ',' ',
instr(ppa.legislative_parameters,'TAX_REF=')+8)
- instr(ppa.legislative_parameters,'TAX_REF=') - 8)
= org.org_information1
AND ppa.report_type = 'P46_CAR_EDI_V3'
AND report_qualifier = 'GB'
AND ppa.report_category = 'EDI';
sqlstr := 'select distinct person_id '||
'from per_people_f ppf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
sqlstr := 'select 1 '||
'/* ERROR - Employer Details Fetch failed with: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
SELECT
substr(org.org_information3,1,36) employer_name,
substr(org.org_information4,1,60) employer_address_line,
substr(org.org_information2 ,1,40) tax_district_name,
organization_id,
ppa.effective_date
FROM
pay_payroll_actions ppa,
hr_organization_information org
WHERE ppa.payroll_action_id = pactid
AND org.org_information_context = 'Tax Details References'
AND NVL(org.org_information10,'UK') = 'UK'
AND org.organization_id = ppa.business_group_id
AND substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,'TAX_REF=') + 8,
instr(ppa.legislative_parameters||' ',' ',
instr(ppa.legislative_parameters,'TAX_REF=')+8)
- instr(ppa.legislative_parameters,'TAX_REF=') - 8)
= org.org_information1
AND ppa.report_type = 'P46_CAR_EDI_V4'
AND report_qualifier = 'GB'
AND ppa.report_category = 'EDI';
sqlstr := 'select distinct person_id '||
'from per_people_f ppf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
sqlstr := 'select 1 '||
'/* ERROR - Employer Details Fetch failed with: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
SELECT
substr(org.org_information3,1,36) employer_name,
substr(org.org_information4,1,60) employer_address_line,
substr(org.org_information2 ,1,40) tax_district_name,
organization_id,
ppa.effective_date
FROM
pay_payroll_actions ppa,
hr_organization_information org
WHERE ppa.payroll_action_id = pactid
AND org.org_information_context = 'Tax Details References'
AND NVL(org.org_information10,'UK') = 'UK'
AND org.organization_id = ppa.business_group_id
AND substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,'TAX_REF=') + 8,
instr(ppa.legislative_parameters||' ',' ',
instr(ppa.legislative_parameters,'TAX_REF=')+8)
- instr(ppa.legislative_parameters,'TAX_REF=') - 8)
= org.org_information1
AND ppa.report_type = 'P46_CAR_EDI_V5'
AND report_qualifier = 'GB'
AND ppa.report_category = 'EDI';
sqlstr := 'select distinct person_id '||
'from per_people_f ppf, '||
'pay_payroll_actions ppa '||
'where ppa.payroll_action_id = :payroll_action_id '||
'and ppa.business_group_id = ppf.business_group_id '||
'order by ppf.person_id';
sqlstr := 'select 1 '||
'/* ERROR - Employer Details Fetch failed with: '||
sqlerrm(sqlcode)||' */ '||
'from dual where to_char(:payroll_action_id) = dummy';
SELECT to_number( pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'PAYROLL_ID')) payroll_id,
substr( pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'TAX_REF'),1,20) tax_ref,
start_date,
effective_date,
fnd_date.canonical_to_date(
pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'END_DATE')) end_date,
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT /* USE_INDEX(va,PQP_VEHICLE_ALLOCATIONS_F_N1)
*/
asg.assignment_id,
min(asg.effective_start_date) asg_min_start_date,
max(asg.effective_end_date) asg_max_end_date
FROM hr_soft_coding_keyflex flex,
per_all_assignments_f asg,
pay_payrolls_f ppf,
pqp_vehicle_allocations_f va,
pqp_vehicle_repository_f vr
WHERE asg.person_id BETWEEN stperson AND endperson
AND asg.business_group_id = g_business_group_id
AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
AND asg.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR ( asg.effective_start_date BETWEEN g_start_date AND g_end_Date
AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
AND va.assignment_id = asg.assignment_id
AND va.business_group_id = g_business_group_id
AND ( ( va.effective_start_date BETWEEN g_start_date AND g_end_date
OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
OR ( g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
AND vr.vehicle_repository_id = va.vehicle_repository_id
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C'
AND va.effective_start_date BETWEEN vr.effective_start_date AND vr.effective_end_date
GROUP by asg.assignment_id;
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
SELECT to_number( pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'PAYROLL_ID')) payroll_id,
substr( pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'TAX_REF'),1,20) tax_ref,
start_date,
effective_date,
fnd_date.canonical_to_date(
pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'END_DATE')) end_date,
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT /* USE_INDEX(va,PQP_VEHICLE_ALLOCATIONS_F_N1)
*/
asg.assignment_id,
min(asg.effective_start_date) asg_min_start_date,
max(asg.effective_end_date) asg_max_end_date
FROM hr_soft_coding_keyflex flex,
per_all_assignments_f asg,
pay_payrolls_f ppf,
pqp_vehicle_allocations_f va,
pqp_vehicle_repository_f vr
WHERE asg.person_id BETWEEN stperson AND endperson
AND asg.business_group_id = g_business_group_id
AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
AND asg.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR ( asg.effective_start_date BETWEEN g_start_date AND g_end_Date
AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
AND va.assignment_id = asg.assignment_id
AND va.business_group_id = g_business_group_id
AND ( ( va.effective_start_date BETWEEN g_start_date AND g_end_date
OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
OR ( g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
AND vr.vehicle_repository_id = va.vehicle_repository_id
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C'
AND va.effective_start_date BETWEEN vr.effective_start_date AND vr.effective_end_date
GROUP by asg.assignment_id;
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
SELECT to_number( pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'PAYROLL_ID')) payroll_id,
substr( pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'TAX_REF'),1,20) tax_ref,
start_date,
effective_date,
fnd_date.canonical_to_date(
pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'END_DATE')) end_date,
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT /* USE_INDEX(va,PQP_VEHICLE_ALLOCATIONS_F_N1)
*/
asg.assignment_id,
min(asg.effective_start_date) asg_min_start_date,
max(asg.effective_end_date) asg_max_end_date
FROM hr_soft_coding_keyflex flex,
per_all_assignments_f asg,
pay_payrolls_f ppf,
pqp_vehicle_allocations_f va,
pqp_vehicle_repository_f vr
WHERE asg.person_id BETWEEN stperson AND endperson
AND asg.business_group_id = g_business_group_id
AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
AND asg.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR ( asg.effective_start_date BETWEEN g_start_date AND g_end_Date
AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
AND va.assignment_id = asg.assignment_id
AND va.business_group_id = g_business_group_id
AND ( ( va.effective_start_date BETWEEN g_start_date AND g_end_date
OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
OR ( g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
AND vr.vehicle_repository_id = va.vehicle_repository_id
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C'
AND va.effective_start_date BETWEEN vr.effective_start_date AND vr.effective_end_date
GROUP by asg.assignment_id;
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
SELECT to_number( pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'PAYROLL_ID')) payroll_id,
substr( pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'TAX_REF'),1,20) tax_ref,
start_date,
effective_date,
fnd_date.canonical_to_date(
pay_gb_eoy_archive.get_parameter(
legislative_parameters,
'END_DATE')) end_date,
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = pactid;
SELECT /* USE_INDEX(va,PQP_VEHICLE_ALLOCATIONS_F_N1)
*/
asg.assignment_id,
min(asg.effective_start_date) asg_min_start_date,
max(asg.effective_end_date) asg_max_end_date
FROM hr_soft_coding_keyflex flex,
per_all_assignments_f asg,
pay_payrolls_f ppf,
pqp_vehicle_allocations_f va,
pqp_vehicle_repository_f vr
WHERE asg.person_id BETWEEN stperson AND endperson
AND asg.business_group_id = g_business_group_id
AND asg.payroll_id = nvl(g_payroll_id,asg.payroll_id)
AND asg.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR ( asg.effective_start_date BETWEEN g_start_date AND g_end_Date
AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
AND va.assignment_id = asg.assignment_id
AND va.business_group_id = g_business_group_id
AND ( ( va.effective_start_date BETWEEN g_start_date AND g_end_date
OR va.effective_end_date BETWEEN g_start_date AND g_end_date)
OR ( g_start_date BETWEEN va.effective_start_date AND va.effective_end_date
AND g_end_date BETWEEN va.effective_start_date AND va.effective_end_date) )
AND vr.vehicle_repository_id = va.vehicle_repository_id
AND vr.vehicle_ownership = 'C'
AND vr.vehicle_type = 'C'
AND va.effective_start_date BETWEEN vr.effective_start_date AND vr.effective_end_date
GROUP by asg.assignment_id;
SELECT pay_assignment_actions_s.nextval
INTO l_actid
FROM dual;
SELECT p.last_name
, p.title
, p.first_name
, p.middle_names
, p.person_id
, p.national_identifier
, p.date_of_birth --For Bug 6652235
, p.sex --For Bug 6652235
FROM per_people_f p, per_assignments_f a
WHERE a.assignment_id = p_assignment_id
AND l_eff_date between
a.effective_start_date and a.effective_end_date
AND a.person_id = p.person_id
AND l_eff_date between
p.effective_start_date and p.effective_end_date;
SELECT addr.address_line1,
addr.address_line2,
addr.address_line3,
addr.town_or_city,
substr(hr_general.decode_lookup('GB_COUNTY',
addr.region_1), 1, 35) region_1,
addr.country,
addr.postal_code
FROM per_addresses addr
WHERE addr.person_id = p_person_id
AND addr.primary_flag = 'Y'
AND l_eff_date BETWEEN addr.date_from
AND nvl(addr.date_to, fnd_date.canonical_to_date('4712/12/31'));
SELECT capital_contribution,
private_contribution,
fuel_benefit
FROM pqp_vehicle_allocations_f
WHERE vehicle_allocation_id = p_vehicle_allocation_id
AND l_eff_date between effective_start_date
and effective_end_Date;
SELECT registration_number,
vehicle_type,
vehicle_id_number,
make,
model,
initial_registration,
last_registration_renew_date,
engine_capacity_in_cc,
-- Included for the EOY Changes. Bug : 11075296
case p_version_type
WHEN 'P46_CAR_V4' then
decode(h1.description,'B','A'
,'C','A'
,'D','D'
,'L','D'
,'E','E'
,'G','A'
,'H','A'
,'P','A')
WHEN 'P46_CAR_V5' then
decode(h1.description,'B','A'
,'C','A'
,'D','D'
,'L','D'
,'E','E'
,'G','A'
,'H','A'
,'P','A')
else
h1.description
end as fuel_type,
currency_code,
list_price ,
accessory_value_at_startdate,
accessory_value_added_later,
market_value_classic_car,
fiscal_ratings,
fiscal_ratings_uom,
shared_vehicle,
vehicle_status,
taxation_method
FROM pqp_vehicle_repository_f,
hr_lookups h1
WHERE vehicle_repository_id = p_vehicle_repository_id
AND l_eff_date BETWEEN effective_start_date AND effective_end_Date
AND fuel_type = h1.lookup_code
AND h1.lookup_type = 'PQP_FUEL_TYPE'
AND h1.enabled_flag = 'Y'
AND trunc(sysdate) BETWEEN trunc(nvl(h1.start_date_active, sysdate-1)) AND trunc(nvl(h1.end_date_active,sysdate+1));
SELECT val_information10
FROM pqp_vehicle_allocations_f
WHERE vehicle_allocation_id = p_vehicle_allocation_id
AND l_eff_date between effective_start_date
and effective_end_Date;
SELECT asg.assignment_id,
min(asg.effective_start_date) asg_min_start_date,
max(asg.effective_end_date) asg_max_end_date
FROM hr_soft_coding_keyflex flex,
per_all_assignments_f asg,
Pay_payrolls_f ppf,
pay_assignment_actions act
WHERE act.assignment_action_id = p_assactid
AND act.assignment_id = asg.assignment_id
AND asg.payroll_id +0 = nvl(g_payroll_id,asg.payroll_id)
AND asg.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR (asg.effective_start_date BETWEEN g_start_date AND g_end_Date
AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
GROUP by asg.assignment_id;
SELECT asg.assignment_id,
min(asg.effective_start_date) asg_min_start_date,
max(asg.effective_end_date) asg_max_end_date
FROM hr_soft_coding_keyflex flex,
per_all_assignments_f asg,
Pay_payrolls_f ppf,
pay_assignment_actions act
WHERE act.assignment_action_id = p_assactid
AND act.assignment_id = asg.assignment_id
AND asg.payroll_id +0 = nvl(g_payroll_id,asg.payroll_id)
AND asg.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR (asg.effective_start_date BETWEEN g_start_date AND g_end_Date
AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
GROUP by asg.assignment_id;
SELECT asg.assignment_id,
min(asg.effective_start_date) asg_min_start_date,
max(asg.effective_end_date) asg_max_end_date
FROM hr_soft_coding_keyflex flex,
per_all_assignments_f asg,
Pay_payrolls_f ppf,
pay_assignment_actions act
WHERE act.assignment_action_id = p_assactid
AND act.assignment_id = asg.assignment_id
AND asg.payroll_id +0 = nvl(g_payroll_id,asg.payroll_id)
AND asg.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR (asg.effective_start_date BETWEEN g_start_date AND g_end_Date
AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
GROUP by asg.assignment_id;
SELECT asg.assignment_id,
min(asg.effective_start_date) asg_min_start_date,
max(asg.effective_end_date) asg_max_end_date
FROM hr_soft_coding_keyflex flex,
per_all_assignments_f asg,
Pay_payrolls_f ppf,
pay_assignment_actions act
WHERE act.assignment_action_id = p_assactid
AND act.assignment_id = asg.assignment_id
AND asg.payroll_id +0 = nvl(g_payroll_id,asg.payroll_id)
AND asg.payroll_id = ppf.payroll_id
AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
AND flex.segment1 = g_tax_ref
AND ( g_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR g_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
OR (asg.effective_start_date BETWEEN g_start_date AND g_end_Date
AND asg.effective_end_date BETWEEN g_start_date AND g_end_Date))
GROUP by asg.assignment_id;