1 PACKAGE BODY PQP_PL_VEHICLE_ALLOCATIONS AS
2 /* $Header: pqplvalp.pkb 120.1 2006/09/13 13:13:29 mseshadr noship $ */
3
4 PROCEDURE PL_VALIDATE_ALLOCATION(p_vehicle_repository_id in NUMBER
5 ,p_effective_date in DATE
6 ,p_val_information2 in varchar2
7 ,p_val_information3 in NUMBER
8 ) is
9 cursor csr_veh_type is
10 select pvr.vehicle_type,pvr.vehicle_ownership
11 from
12 pqp_vehicle_repository_f pvr
13 where
14 pvr.vehicle_repository_id = p_vehicle_repository_id
15 and p_effective_date between pvr.effective_start_date and pvr.effective_end_date;
16
17 cursor csr_capacity is select ci.value from pay_user_column_instances_f ci
18 where ci.legislation_code = 'PL'
19 and ci.user_column_instance_id = p_val_information2
20 and p_effective_date between ci.effective_start_date and ci.effective_end_date;
21
22 l_vehicle_type pqp_vehicle_repository_f.vehicle_type%type;
23 l_vehicle_ownership pqp_vehicle_repository_f.vehicle_ownership%type;
24 l_mileage_value number;
25 l_proc varchar2(72); -- Variable used when data is uploaded directly by api
26
27 BEGIN
28
29
30 open csr_capacity;
31 fetch csr_capacity into l_mileage_value;
32 close csr_capacity;
33 open csr_veh_type;
34 fetch csr_veh_type into l_vehicle_type,l_vehicle_ownership;
35 close csr_veh_type;
36
37 If l_vehicle_ownership = 'PL_PC' and l_vehicle_type <> 'PL_T' then
38
39 If p_val_information2 is null then
40 hr_utility.set_message(800,'HR_375879_PL_MILEAGE_LMT_LAW');
41 hr_utility.raise_error;
42 End if;
43
44 If p_val_information3 is null then
45 hr_utility.set_message(800,'HR_375880_PL_MILEAGE_LMT_EMP');
46 hr_utility.raise_error;
47 End if;
48 If p_val_information3 < l_mileage_value then
49 hr_utility.set_message(800,'HR_375881_PL_EMP_LIMIT');
50 hr_utility.raise_error;
51 End if;
52 End if;
53 If l_vehicle_type = 'PL_T' then
54 -- Check for Vehicle type Truck
55 If p_val_information2 is not null or p_val_information3 is not null then
56 -- Check the value is null for Truck
57 hr_utility.set_message(800,'HR_375838_VAL_PL_TRUCK');
58 --Monthly mileage limit is not applicable to trucks. Ensure that you do not specify a monthly mileage limit for a truck.
59 hr_utility.raise_error;
60 End if;
61 End if;
62 END PL_VALIDATE_ALLOCATION;
63
64 PROCEDURE CREATE_PL_VEHICLE_ALLOCATION(p_assignment_id in NUMBER
65 ,p_effective_date in DATE
66 ,p_business_group_id in NUMBER
67 ,p_vehicle_repository_id in NUMBER
68 ,p_val_information_category in varchar2
69 ,p_val_information1 in varchar2
70 ,p_val_information2 in varchar2
71 ,p_val_information3 in varchar2
72 ,p_val_information4 in varchar2
73 ,p_val_information5 in varchar2
74 ,p_val_information6 in varchar2
75 ,p_val_information7 in varchar2
76 ,p_val_information8 in varchar2
77 ,p_val_information9 in varchar2
78 ,p_val_information10 in varchar2
79 ,p_val_information11 in varchar2
80 ,p_val_information12 in varchar2
81 ,p_val_information13 in varchar2
82 ,p_val_information14 in varchar2
83 ,p_val_information15 in varchar2
84 ,p_val_information16 in varchar2
85 ,p_val_information17 in varchar2
86 ,p_val_information18 in varchar2
87 ,p_val_information19 in varchar2
88 ,p_val_information20 in varchar2) IS
89
90 Begin
91
92 /* Added for GSI Bug 5472781 */
93 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
94 hr_utility.trace('PL not installed.Leaving CREATE_PL_VEHICLE_ALLOCATION');
95 return;
96 END IF;
97
98 If p_val_information_category = 'PL' then
99
100 PL_VALIDATE_ALLOCATION(p_vehicle_repository_id,p_effective_date,p_val_information2,p_val_information3);
101
102 End If;
103
104
105 END CREATE_PL_VEHICLE_ALLOCATION;
106
107 PROCEDURE UPDATE_PL_VEHICLE_ALLOCATION(p_effective_date in DATE
108 ,p_vehicle_allocation_id in NUMBER
109 ,p_assignment_id in NUMBER
110 ,p_business_group_id in NUMBER
111 ,p_vehicle_repository_id in NUMBER
112 ,p_val_information_category in varchar2
113 ,p_val_information1 in varchar2
114 ,p_val_information2 in varchar2
115 ,p_val_information3 in varchar2
116 ,p_val_information4 in varchar2
117 ,p_val_information5 in varchar2
118 ,p_val_information6 in varchar2
119 ,p_val_information7 in varchar2
120 ,p_val_information8 in varchar2
121 ,p_val_information9 in varchar2
122 ,p_val_information10 in varchar2
123 ,p_val_information11 in varchar2
124 ,p_val_information12 in varchar2
125 ,p_val_information13 in varchar2
126 ,p_val_information14 in varchar2
127 ,p_val_information15 in varchar2
128 ,p_val_information16 in varchar2
129 ,p_val_information17 in varchar2
130 ,p_val_information18 in varchar2
131 ,p_val_information19 in varchar2
132 ,p_val_information20 in varchar2) IS
133
134 BEGIN
135 /* Added for GSI Bug 5472781 */
136 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
137 hr_utility.trace('PL not installed.Leaving UPDATE_PL_VEHICLE_ALLOCATION');
138 return;
139 END IF;
140
141 If p_val_information_category = 'PL' then
142 PL_VALIDATE_ALLOCATION(p_vehicle_repository_id,p_effective_date,p_val_information2,p_val_information3);
143 End if;
144
145 END UPDATE_PL_VEHICLE_ALLOCATION;
146
147
148 PROCEDURE DELETE_PL_VEHICLE_ALLOCATION(P_VALIDATE in BOOLEAN
149 ,P_EFFECTIVE_DATE in DATE
150 ,P_DATETRACK_MODE in VARCHAR2
151 ,P_VEHICLE_ALLOCATION_ID in NUMBER
152 ,P_OBJECT_VERSION_NUMBER in NUMBER) IS
153
154 cursor csr_element_entry is select peef.element_entry_id, peef.assignment_id, peef.object_version_number from
155 pay_element_types_f petf,
156 pay_element_entry_values_f peevf,
157 pay_input_values_f pivf,
158 pay_element_entries_f peef,
159 pqp_vehicle_allocations_f pvaf,
160 per_all_assignments_f paaf
161 where
162 peevf.element_entry_id = peef.element_entry_id and
163 peef.element_type_id = petf.element_type_id and
164 petf.element_type_id = peef.element_type_id and
165 peef.assignment_id = pvaf.assignment_id and
166 paaf.assignment_id = pvaf.assignment_id and
167 pivf.element_type_id = petf.element_type_id and
168 pivf.input_value_id = peevf.input_value_id and
169 petf.element_name = 'Vehicle Mileage Expense Information' and
170 pivf.name = 'Vehicle Allocation' and
171 pvaf.vehicle_allocation_id = P_VEHICLE_ALLOCATION_ID and
172 P_EFFECTIVE_DATE between peevf.EFFECTIVE_START_DATE and peevf.EFFECTIVE_END_DATE and
173 P_EFFECTIVE_DATE between peef.EFFECTIVE_START_DATE and peef.EFFECTIVE_END_DATE and
174 P_EFFECTIVE_DATE between petf.effective_start_date and petf.effective_end_date and
175 P_EFFECTIVE_DATE between pivf.effective_start_date and pivf.effective_end_date and
176 P_EFFECTIVE_DATE between pvaf.effective_start_date and pvaf.effective_end_date and
177 P_EFFECTIVE_DATE between paaf.effective_start_date and paaf.effective_end_date;
178
179
180 cursor csr_pay_period(p_assignment_id number) is
181 SELECT max(ptp.end_date)
182 FROM per_time_periods ptp,
183 per_all_assignments_f paa
184 where ptp.payroll_id = paa.payroll_id and
185 paa.assignment_id = p_assignment_id;
186
187 l_element_entry_id pay_element_entries_f.element_entry_id%type;
188 l_assignment_id per_all_assignments_f.assignment_id%type;
189 l_period_end_date per_time_periods.end_date%type;
190 l_object_version_number pay_element_entries_f.object_version_number%TYPE;
191
192 l_effective_start_date date;
193 l_effective_end_date date;
194 l_delete_warning boolean;
195
196 BEGIN
197
198 /* Added for GSI Bug 5472781 */
199 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
200 hr_utility.trace('PL not installed.Leaving DELETE_PL_VEHICLE_ALLOCATION');
201 return;
202 END IF;
203
204 open csr_element_entry;
205 fetch csr_element_entry into l_element_entry_id, l_assignment_id, l_object_version_number;
206 close csr_element_entry;
207
208 open csr_pay_period(l_assignment_id);
209 fetch csr_pay_period into l_period_end_date;
210 close csr_pay_period;
211
212 If l_element_entry_id is not null then
213 if p_effective_date > l_period_end_date then
214
215 pay_element_entry_api.delete_element_entry (
216 p_validate => FALSE,
217 p_datetrack_delete_mode => P_DATETRACK_MODE,
218 p_effective_date => l_period_end_date,
219 p_element_entry_id => l_element_entry_id,
220 p_object_version_number => l_object_version_number,
221 p_effective_start_date => l_effective_start_date,
222 p_effective_end_date => l_effective_end_date,
223 p_delete_warning => l_delete_warning
224 );
225
226 else
227
228 pay_element_entry_api.delete_element_entry (
229 p_validate => FALSE,
230 p_datetrack_delete_mode => P_DATETRACK_MODE,
231 p_effective_date => p_effective_date,
232 p_element_entry_id => l_element_entry_id,
233 p_object_version_number => l_object_version_number,
234 p_effective_start_date => l_effective_start_date,
235 p_effective_end_date => l_effective_end_date,
236 p_delete_warning => l_delete_warning
237 );
238 end if;
239 End if;
240 END DELETE_PL_VEHICLE_ALLOCATION;
241
242 END PQP_PL_VEHICLE_ALLOCATIONS;
[Home] [Help]