1 Package Body pqh_pte_bus as
2 /* $Header: pqpterhi.pkb 115.11 2002/12/12 23:13:54 sgoyal noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_pte_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_ptx_extra_info_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- ptx_extra_info_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_ptx_extra_info_id(p_ptx_extra_info_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_ptx_extra_info_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pqh_pte_shd.api_updating
47 (p_ptx_extra_info_id => p_ptx_extra_info_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_ptx_extra_info_id,hr_api.g_number)
52 <> pqh_pte_shd.g_old_rec.ptx_extra_info_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_pte_shd.constraint_error('PQH_PTX_EXTRA_INFO_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_ptx_extra_info_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_pte_shd.constraint_error('PQH_PTX_EXTRA_INFO_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_ptx_extra_info_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_position_transaction_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_ptx_extra_info_id PK
89 -- p_position_transaction_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_position_transaction_id (p_ptx_extra_info_id in number,
102 p_position_transaction_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_position_transaction_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_position_transactions a
112 where a.position_transaction_id = p_position_transaction_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_pte_shd.api_updating
119 (p_ptx_extra_info_id => p_ptx_extra_info_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_position_transaction_id,hr_api.g_number)
124 <> nvl(pqh_pte_shd.g_old_rec.position_transaction_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if position_transaction_id value exists in pqh_position_transactions table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in pqh_position_transactions
137 -- table.
138 --
139 pqh_pte_shd.constraint_error('PQH_PTX_EXTRA_INFO_FK2');
140 --
141 end if;
142 --
143 close c1;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_position_transaction_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_information_type >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure checks that a referenced foreign key actually exists
157 -- in the referenced table.
158 --
159 -- Pre-Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- p_ptx_extra_info_id PK
164 -- p_information_type ID of FK column
165 -- p_object_version_number object version number
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error raised.
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_information_type (p_ptx_extra_info_id in number,
177 p_information_type in varchar2,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_information_type';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from per_position_info_types a
187 where a.information_type = p_information_type;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := pqh_pte_shd.api_updating
194 (p_ptx_extra_info_id => p_ptx_extra_info_id,
195 p_object_version_number => p_object_version_number);
196 --
197 if (l_api_updating
198 and nvl(p_information_type,hr_api.g_varchar2)
199 <> nvl(pqh_pte_shd.g_old_rec.information_type,hr_api.g_varchar2)
200 or not l_api_updating) then
201 --
202 -- check if information_type value exists in per_position_info_types table
203 --
204 open c1;
205 --
206 fetch c1 into l_dummy;
207 if c1%notfound then
208 --
209 close c1;
210 --
211 -- raise error as FK does not relate to PK in pqh_ptx_info_types
212 -- table.
213 --
214 hr_utility.set_message(800, 'PQH_INVALID_POS_INFO_TYPE');
215 hr_utility.set_message_token('POS_INFO_TYPE',p_information_type );
216 hr_utility.raise_error;
217 -- pqh_pte_shd.constraint_error('PQH_PTX_EXTRA_INFO_FK1');
218 --
219 end if;
220 --
221 close c1;
222 --
223 end if;
224 --
225 hr_utility.set_location('Leaving:'||l_proc,10);
226 --
227 End chk_information_type;
228
229 --
230 -- ----------------------------------------------------------------------------
231 -- |---------------------------< insert_validate >----------------------------|
232 -- ----------------------------------------------------------------------------
233 Procedure insert_validate(p_rec in pqh_pte_shd.g_rec_type) is
234 --
235 l_proc varchar2(72) := g_package||'insert_validate';
236 --
237 l_ptx_rec pqh_position_transactions%rowtype;
238 --
239 cursor c_ptx(p_position_transaction_id number) is
240 select *
241 from pqh_position_transactions
242 where position_transaction_id = p_position_transaction_id;
243 Begin
244 hr_utility.set_location('Entering:'||l_proc, 5);
245 --
246 open c_ptx(p_rec.position_transaction_id);
247 fetch c_ptx into l_ptx_rec;
248 --
249 -- Validate Position Id
250 --
251 if c_ptx%notfound then
252 close c_ptx;
253 hr_utility.set_message(800, 'HR_INV_POSN_TRAN');
254 hr_utility.raise_error;
255 end if;
256 --
257 close c_ptx;
258 --
259 -- Call all supporting business operations
260 --
261 chk_ptx_extra_info_id
262 (p_ptx_extra_info_id => p_rec.ptx_extra_info_id,
263 p_object_version_number => p_rec.object_version_number);
264 --
265 chk_position_transaction_id
266 (p_ptx_extra_info_id => p_rec.ptx_extra_info_id,
267 p_position_transaction_id => p_rec.position_transaction_id,
268 p_object_version_number => p_rec.object_version_number);
269 --
270 chk_information_type
271 (p_ptx_extra_info_id => p_rec.ptx_extra_info_id,
272 p_information_type => p_rec.information_type,
273 p_object_version_number => p_rec.object_version_number);
274 --
275 --
276 --
277 -- Validate Seasonal Dates for PTX Extra Info
278 --
279 if (p_rec.information_type = 'PER_SEASONAL') then
280 if (nvl(l_ptx_rec.seasonal_flag,'N') = 'N' )then
281 -- Cannot add Seasonal dates to PTX Extra Info if seasonal_flag<>'Y'
282 hr_utility.set_message(800, 'HR_INV_POI_SEASONAL');
283 hr_utility.raise_error;
284 end if;
285 if (p_rec.information3 > p_rec.information4) then
286 -- Overlap end date should be later than overlap start date
287 hr_utility.set_message(800, 'HR_INV_POI_SEASONAL_DATES');
288 hr_utility.raise_error;
289 end if;
290 end if;
291 --
292 -- Validate Overlap Dates for PTX Extra Info
293 --
294 if (p_rec.information_type = 'PER_OVERLAP') then
295 if ( l_ptx_rec.overlap_period is null )then
296 -- Cannot add Overlap dates to PTX Extra Info if overlap_period is null
297 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP');
298 hr_utility.raise_error;
299 end if;
300 if (p_rec.information3 > p_rec.information4) then
301 -- Overlap end date should be later than overlap start date
302 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP_DATES');
303 hr_utility.raise_error;
304 end if;
305 end if;
306 --
307 -- Validate Reservation Info for PTX Extra Info
308 --
309 if (p_rec.information_type = 'PER_RESERVED') then
310 if (p_rec.information3 > p_rec.information4) then
311 -- Reservation end date should be later than reservation start date
312 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_DATES');
313 hr_utility.raise_error;
314 end if;
315 if (p_rec.information6 <= 0) then
316 -- Reservation end date should be later than reservation start date
317 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_FTE');
318 hr_utility.raise_error;
319 end if;
320 end if;
321 --
322 --
323 --
324 hr_utility.set_location(' Leaving:'||l_proc, 10);
325 End insert_validate;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |---------------------------< update_validate >----------------------------|
329 -- ----------------------------------------------------------------------------
330 Procedure update_validate(p_rec in pqh_pte_shd.g_rec_type) is
331 --
332 l_proc varchar2(72) := g_package||'update_validate';
333 --
334 Begin
335 hr_utility.set_location('Entering:'||l_proc, 5);
336 --
337 -- Call all supporting business operations
338 --
339 chk_ptx_extra_info_id
340 (p_ptx_extra_info_id => p_rec.ptx_extra_info_id,
341 p_object_version_number => p_rec.object_version_number);
342 --
343 chk_position_transaction_id
344 (p_ptx_extra_info_id => p_rec.ptx_extra_info_id,
345 p_position_transaction_id => p_rec.position_transaction_id,
346 p_object_version_number => p_rec.object_version_number);
347 --
348 chk_information_type
349 (p_ptx_extra_info_id => p_rec.ptx_extra_info_id,
350 p_information_type => p_rec.information_type,
351 p_object_version_number => p_rec.object_version_number);
352 --
353 --
354 --
355 -- Validate Seasonal Dates for PTX Extra Info
356 --
357 if (p_rec.information_type = 'PER_SEASONAL') then
358 if (p_rec.information3 > p_rec.information4) then
359 -- Overlap end date should be later than overlap start date
360 hr_utility.set_message(800, 'HR_INV_POI_SEASONAL_DATES');
361 hr_utility.raise_error;
362 end if;
363 end if;
364 --
365 -- Validate Overlap Dates for PTX Extra Info
366 --
367 if (p_rec.information_type = 'PER_OVERLAP') then
368 if (p_rec.information3 > p_rec.information4) then
369 -- Overlap end date should be later than overlap start date
370 hr_utility.set_message(800, 'HR_INV_POI_OVERLAP_DATES');
371 hr_utility.raise_error;
372 end if;
373 end if;
374 --
375 -- Validate Reservation Info for PTX Extra Info
376 --
377 if (p_rec.information_type = 'PER_RESERVED') then
378 if (p_rec.information3 > p_rec.information4) then
379 -- Reservation end date should be later than reservation start date
380 hr_utility.set_message(800, 'HR_INV_POI_RESERVED_DATES');
381 hr_utility.raise_error;
382 end if;
383 end if;
384 --
385 --
386 --
387 hr_utility.set_location(' Leaving:'||l_proc, 10);
388 End update_validate;
389 --
390 -- ----------------------------------------------------------------------------
391 -- |---------------------------< delete_validate >----------------------------|
392 -- ----------------------------------------------------------------------------
393 Procedure delete_validate(p_rec in pqh_pte_shd.g_rec_type) is
394 --
395 l_proc varchar2(72) := g_package||'delete_validate';
396 --
397 Begin
398 hr_utility.set_location('Entering:'||l_proc, 5);
399 --
400 -- Call all supporting business operations
401 --
402 hr_utility.set_location(' Leaving:'||l_proc, 10);
403 End delete_validate;
404 --
405 end pqh_pte_bus;
[Home] [Help]