[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT paa.assignment_id
FROM per_all_assignments_f paa
WHERE paa.person_id =(SELECT paa1.person_id
FROM per_all_assignments_f paa1
WHERE paa1.assignment_id=p_assignment_id
AND paa1.business_group_id=p_business_group_id
AND p_effective_date BETWEEN paa1.effective_start_date
AND paa1.effective_end_date)
AND NOT EXISTS (SELECT 'X'
FROM pqp_vehicle_allocations_f pva
WHERE pva.vehicle_repository_id=p_vehicle_repository_id
AND p_effective_date BETWEEN pva.effective_start_date
AND pva.effective_end_date
and pva.assignment_id =paa.assignment_id
and pva.business_group_id=paa.business_group_id)
AND p_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
procedure update_veh_multi_alloc
(p_validate in boolean
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_vehicle_allocation_id in number
,p_object_version_number in out nocopy number
,p_assignment_id in number
,p_business_group_id in number
,p_vehicle_repository_id in number
,p_across_assignments in varchar2
,p_usage_type in varchar2
,p_capital_contribution in number
,p_private_contribution in number
,p_default_vehicle in varchar2
,p_fuel_card in varchar2
,p_fuel_card_number in varchar2
,p_calculation_method in varchar2
,p_rates_table_id in number
,p_element_type_id in number
,p_private_use_flag in varchar2
,p_insurance_number in varchar2
,p_insurance_expiry_date in date
,p_val_attribute_category in varchar2
,p_val_attribute1 in varchar2
,p_val_attribute2 in varchar2
,p_val_attribute3 in varchar2
,p_val_attribute4 in varchar2
,p_val_attribute5 in varchar2
,p_val_attribute6 in varchar2
,p_val_attribute7 in varchar2
,p_val_attribute8 in varchar2
,p_val_attribute9 in varchar2
,p_val_attribute10 in varchar2
,p_val_attribute11 in varchar2
,p_val_attribute12 in varchar2
,p_val_attribute13 in varchar2
,p_val_attribute14 in varchar2
,p_val_attribute15 in varchar2
,p_val_attribute16 in varchar2
,p_val_attribute17 in varchar2
,p_val_attribute18 in varchar2
,p_val_attribute19 in varchar2
,p_val_attribute20 in varchar2
,p_val_information_category in varchar2
,p_val_information1 in varchar2
,p_val_information2 in varchar2
,p_val_information3 in varchar2
,p_val_information4 in varchar2
,p_val_information5 in varchar2
,p_val_information6 in varchar2
,p_val_information7 in varchar2
,p_val_information8 in varchar2
,p_val_information9 in varchar2
,p_val_information10 in varchar2
,p_val_information11 in varchar2
,p_val_information12 in varchar2
,p_val_information13 in varchar2
,p_val_information14 in varchar2
,p_val_information15 in varchar2
,p_val_information16 in varchar2
,p_val_information17 in varchar2
,p_val_information18 in varchar2
,p_val_information19 in varchar2
,p_val_information20 in varchar2
,p_fuel_benefit in varchar2
,p_sliding_rates_info in varchar2
)
IS
CURSOR c_get_asg
IS
SELECT paa.assignment_id
,pva.object_version_number ovn
,pva.vehicle_allocation_id
FROM per_all_assignments_f paa
,pqp_vehicle_allocations_f pva
WHERE paa.person_id =(SELECT paa1.person_id
FROM per_all_assignments_f paa1
WHERE paa1.assignment_id=p_assignment_id
AND paa1.business_group_id=p_business_group_id
AND p_effective_date BETWEEN paa1.effective_start_date
AND paa1.effective_end_date)
AND paa.assignment_id=pva.assignment_id
AND paa.business_group_id=pva.business_group_id
AND paa.business_group_id=p_business_group_id
AND p_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
-- fix for bug 7025352
AND p_effective_date BETWEEN pva.effective_start_date
AND pva.effective_end_date
AND pva.vehicle_repository_id=p_vehicle_repository_id
AND paa.assignment_id<>p_assignment_id;
END update_veh_multi_alloc;
procedure delete_veh_multi_alloc
(p_validate in boolean
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_vehicle_allocation_id in number
)
IS
CURSOR c_get_asg
IS
SELECT paa.assignment_id
,pva.object_version_number ovn
FROM per_all_assignments_f paa
,pqp_vehicle_allocations_f pva
WHERE EXISTS (SELECT paa1.person_id
FROM per_all_assignments_f paa1
,pqp_vehicle_allocations_f pva1
WHERE pva1.vehicle_allocation_id=p_vehicle_allocation_id
AND pva1.vehicle_repository_id=pva.vehicle_repository_id
AND paa1.assignment_id=pva1.assignment_id
AND paa1.business_group_id=pva1.business_group_id
AND p_effective_date BETWEEN paa1.effective_start_date
AND paa1.effective_end_date
AND p_effective_date BETWEEN pva1.effective_start_date
AND pva1.effective_end_date)
AND paa.assignment_id=pva.assignment_id
AND paa.business_group_id=pva.business_group_id
AND p_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date;
END delete_veh_multi_alloc;