[Home] [Help]
PACKAGE BODY: APPS.PQH_ACC_BUS
Source
1 Package Body pqh_acc_bus as
2 /* $Header: pqaccrhi.pkb 115.4 2004/03/15 23:54:54 svorugan noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_acc_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_accommodation_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_accommodation_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , pqh_accommodations_f acc
31 where acc.accommodation_id = p_accommodation_id
32 and pbg.business_group_id = acc.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'accommodation_id'
48 ,p_argument_value => p_accommodation_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 => nvl(p_associated_column1,'ACCOMMODATION_ID')
64 );
65 --
66 else
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 end if;
75 --
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_accommodation_id in number
86 )
87 Return Varchar2 Is
88 --
89 -- Declare cursor
90 --
91 cursor csr_leg_code is
92 select pbg.legislation_code
93 from per_business_groups pbg
94 , pqh_accommodations_f acc
95 where acc.accommodation_id = p_accommodation_id
96 and pbg.business_group_id = acc.business_group_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'accommodation_id'
112 ,p_argument_value => p_accommodation_id
113 );
114 --
115 if ( nvl(pqh_acc_bus.g_accommodation_id, hr_api.g_number)
116 = p_accommodation_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := pqh_acc_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
145 close csr_leg_code;
146 pqh_acc_bus.g_accommodation_id := p_accommodation_id;
147 pqh_acc_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------------< chk_ddf >----------------------------------|
155 -- ----------------------------------------------------------------------------
156 --
157 -- Description:
158 -- Validates all the Developer Descriptive Flexfield values.
159 --
160 -- Prerequisites:
161 -- All other columns have been validated. Must be called as the
162 -- last step from insert_validate and update_validate.
163 --
164 -- In Arguments:
165 -- p_rec
166 --
167 -- Post Success:
168 -- If the Developer Descriptive Flexfield structure column and data values
169 -- are all valid this procedure will end normally and processing will
170 -- continue.
171 --
172 -- Post Failure:
173 -- If the Developer Descriptive Flexfield structure column value or any of
174 -- the data values are invalid then an application error is raised as
175 -- a PL/SQL exception.
176 --
177 -- Access Status:
178 -- Internal Row Handler Use Only.
179 --
180 -- ----------------------------------------------------------------------------
181 procedure chk_ddf
182 (p_rec in pqh_acc_shd.g_rec_type
183 ) is
184 --
185 l_proc varchar2(72) := g_package || 'chk_ddf';
186 --
187 begin
188 hr_utility.set_location('Entering:'||l_proc,10);
189 --
190 if ((p_rec.accommodation_id is not null) and (
191 nvl(pqh_acc_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
192 nvl(p_rec.information_category, hr_api.g_varchar2) or
193 nvl(pqh_acc_shd.g_old_rec.information1, hr_api.g_varchar2) <>
194 nvl(p_rec.information1, hr_api.g_varchar2) or
195 nvl(pqh_acc_shd.g_old_rec.information2, hr_api.g_varchar2) <>
196 nvl(p_rec.information2, hr_api.g_varchar2) or
197 nvl(pqh_acc_shd.g_old_rec.information3, hr_api.g_varchar2) <>
198 nvl(p_rec.information3, hr_api.g_varchar2) or
199 nvl(pqh_acc_shd.g_old_rec.information4, hr_api.g_varchar2) <>
200 nvl(p_rec.information4, hr_api.g_varchar2) or
201 nvl(pqh_acc_shd.g_old_rec.information5, hr_api.g_varchar2) <>
202 nvl(p_rec.information5, hr_api.g_varchar2) or
203 nvl(pqh_acc_shd.g_old_rec.information6, hr_api.g_varchar2) <>
204 nvl(p_rec.information6, hr_api.g_varchar2) or
205 nvl(pqh_acc_shd.g_old_rec.information7, hr_api.g_varchar2) <>
206 nvl(p_rec.information7, hr_api.g_varchar2) or
207 nvl(pqh_acc_shd.g_old_rec.information8, hr_api.g_varchar2) <>
208 nvl(p_rec.information8, hr_api.g_varchar2) or
209 nvl(pqh_acc_shd.g_old_rec.information9, hr_api.g_varchar2) <>
210 nvl(p_rec.information9, hr_api.g_varchar2) or
211 nvl(pqh_acc_shd.g_old_rec.information10, hr_api.g_varchar2) <>
212 nvl(p_rec.information10, hr_api.g_varchar2) or
213 nvl(pqh_acc_shd.g_old_rec.information11, hr_api.g_varchar2) <>
214 nvl(p_rec.information11, hr_api.g_varchar2) or
215 nvl(pqh_acc_shd.g_old_rec.information12, hr_api.g_varchar2) <>
216 nvl(p_rec.information12, hr_api.g_varchar2) or
217 nvl(pqh_acc_shd.g_old_rec.information13, hr_api.g_varchar2) <>
218 nvl(p_rec.information13, hr_api.g_varchar2) or
219 nvl(pqh_acc_shd.g_old_rec.information14, hr_api.g_varchar2) <>
220 nvl(p_rec.information14, hr_api.g_varchar2) or
221 nvl(pqh_acc_shd.g_old_rec.information15, hr_api.g_varchar2) <>
222 nvl(p_rec.information15, hr_api.g_varchar2) or
223 nvl(pqh_acc_shd.g_old_rec.information16, hr_api.g_varchar2) <>
224 nvl(p_rec.information16, hr_api.g_varchar2) or
225 nvl(pqh_acc_shd.g_old_rec.information17, hr_api.g_varchar2) <>
226 nvl(p_rec.information17, hr_api.g_varchar2) or
227 nvl(pqh_acc_shd.g_old_rec.information18, hr_api.g_varchar2) <>
228 nvl(p_rec.information18, hr_api.g_varchar2) or
229 nvl(pqh_acc_shd.g_old_rec.information19, hr_api.g_varchar2) <>
230 nvl(p_rec.information19, hr_api.g_varchar2) or
231 nvl(pqh_acc_shd.g_old_rec.information20, hr_api.g_varchar2) <>
232 nvl(p_rec.information20, hr_api.g_varchar2) or
233 nvl(pqh_acc_shd.g_old_rec.information21, hr_api.g_varchar2) <>
234 nvl(p_rec.information21, hr_api.g_varchar2) or
235 nvl(pqh_acc_shd.g_old_rec.information22, hr_api.g_varchar2) <>
236 nvl(p_rec.information22, hr_api.g_varchar2) or
237 nvl(pqh_acc_shd.g_old_rec.information23, hr_api.g_varchar2) <>
238 nvl(p_rec.information23, hr_api.g_varchar2) or
239 nvl(pqh_acc_shd.g_old_rec.information24, hr_api.g_varchar2) <>
240 nvl(p_rec.information24, hr_api.g_varchar2) or
241 nvl(pqh_acc_shd.g_old_rec.information25, hr_api.g_varchar2) <>
242 nvl(p_rec.information25, hr_api.g_varchar2) or
243 nvl(pqh_acc_shd.g_old_rec.information26, hr_api.g_varchar2) <>
244 nvl(p_rec.information26, hr_api.g_varchar2) or
245 nvl(pqh_acc_shd.g_old_rec.information27, hr_api.g_varchar2) <>
246 nvl(p_rec.information27, hr_api.g_varchar2) or
247 nvl(pqh_acc_shd.g_old_rec.information28, hr_api.g_varchar2) <>
248 nvl(p_rec.information28, hr_api.g_varchar2) or
249 nvl(pqh_acc_shd.g_old_rec.information29, hr_api.g_varchar2) <>
250 nvl(p_rec.information29, hr_api.g_varchar2) or
251 nvl(pqh_acc_shd.g_old_rec.information30, hr_api.g_varchar2) <>
252 nvl(p_rec.information30, hr_api.g_varchar2) ))
253 or (p_rec.accommodation_id is null) then
254 --
255 -- Only execute the validation if absolutely necessary:
256 -- a) During update, the structure column value or any
257 -- of the attribute values have actually changed.
258 -- b) During insert.
259 --
260 hr_dflex_utility.ins_or_upd_descflex_attribs
261 (p_appl_short_name => 'PQH'
262 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
263 ,p_attribute_category => 'INFORMATION_CATEGORY'
264 ,p_attribute1_name => 'INFORMATION1'
265 ,p_attribute1_value => p_rec.information1
266 ,p_attribute2_name => 'INFORMATION2'
267 ,p_attribute2_value => p_rec.information2
268 ,p_attribute3_name => 'INFORMATION3'
269 ,p_attribute3_value => p_rec.information3
270 ,p_attribute4_name => 'INFORMATION4'
271 ,p_attribute4_value => p_rec.information4
272 ,p_attribute5_name => 'INFORMATION5'
273 ,p_attribute5_value => p_rec.information5
274 ,p_attribute6_name => 'INFORMATION6'
275 ,p_attribute6_value => p_rec.information6
276 ,p_attribute7_name => 'INFORMATION7'
277 ,p_attribute7_value => p_rec.information7
278 ,p_attribute8_name => 'INFORMATION8'
279 ,p_attribute8_value => p_rec.information8
280 ,p_attribute9_name => 'INFORMATION9'
281 ,p_attribute9_value => p_rec.information9
282 ,p_attribute10_name => 'INFORMATION10'
283 ,p_attribute10_value => p_rec.information10
284 ,p_attribute11_name => 'INFORMATION11'
285 ,p_attribute11_value => p_rec.information11
286 ,p_attribute12_name => 'INFORMATION12'
287 ,p_attribute12_value => p_rec.information12
288 ,p_attribute13_name => 'INFORMATION13'
289 ,p_attribute13_value => p_rec.information13
290 ,p_attribute14_name => 'INFORMATION14'
291 ,p_attribute14_value => p_rec.information14
292 ,p_attribute15_name => 'INFORMATION15'
293 ,p_attribute15_value => p_rec.information15
294 ,p_attribute16_name => 'INFORMATION16'
295 ,p_attribute16_value => p_rec.information16
296 ,p_attribute17_name => 'INFORMATION17'
297 ,p_attribute17_value => p_rec.information17
298 ,p_attribute18_name => 'INFORMATION18'
299 ,p_attribute18_value => p_rec.information18
300 ,p_attribute19_name => 'INFORMATION19'
301 ,p_attribute19_value => p_rec.information19
302 ,p_attribute20_name => 'INFORMATION20'
303 ,p_attribute20_value => p_rec.information20
304 ,p_attribute21_name => 'INFORMATION21'
305 ,p_attribute21_value => p_rec.information21
306 ,p_attribute22_name => 'INFORMATION22'
307 ,p_attribute22_value => p_rec.information22
308 ,p_attribute23_name => 'INFORMATION23'
309 ,p_attribute23_value => p_rec.information23
310 ,p_attribute24_name => 'INFORMATION24'
311 ,p_attribute24_value => p_rec.information24
312 ,p_attribute25_name => 'INFORMATION25'
313 ,p_attribute25_value => p_rec.information25
314 ,p_attribute26_name => 'INFORMATION26'
315 ,p_attribute26_value => p_rec.information26
316 ,p_attribute27_name => 'INFORMATION27'
317 ,p_attribute27_value => p_rec.information27
318 ,p_attribute28_name => 'INFORMATION28'
319 ,p_attribute28_value => p_rec.information28
320 ,p_attribute29_name => 'INFORMATION29'
321 ,p_attribute29_value => p_rec.information29
322 ,p_attribute30_name => 'INFORMATION30'
323 ,p_attribute30_value => p_rec.information30
324 );
325 end if;
326 --
327 hr_utility.set_location(' Leaving:'||l_proc,20);
328 end chk_ddf;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |------------------------------< chk_df >----------------------------------|
332 -- ----------------------------------------------------------------------------
333 --
334 -- Description:
335 -- Validates all the Descriptive Flexfield values.
336 --
337 -- Prerequisites:
338 -- All other columns have been validated. Must be called as the
339 -- last step from insert_validate and update_validate.
340 --
341 -- In Arguments:
342 -- p_rec
343 --
344 -- Post Success:
345 -- If the Descriptive Flexfield structure column and data values are
346 -- all valid this procedure will end normally and processing will
347 -- continue.
348 --
349 -- Post Failure:
350 -- If the Descriptive Flexfield structure column value or any of
351 -- the data values are invalid then an application error is raised as
352 -- a PL/SQL exception.
353 --
354 -- Access Status:
355 -- Internal Row Handler Use Only.
356 --
357 -- ----------------------------------------------------------------------------
358 procedure chk_df
359 (p_rec in pqh_acc_shd.g_rec_type
360 ) is
361 --
362 l_proc varchar2(72) := g_package || 'chk_df';
363 --
364 begin
365 hr_utility.set_location('Entering:'||l_proc,10);
366 --
367 if ((p_rec.accommodation_id is not null) and (
368 nvl(pqh_acc_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
369 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
370 nvl(pqh_acc_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
371 nvl(p_rec.attribute1, hr_api.g_varchar2) or
372 nvl(pqh_acc_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
373 nvl(p_rec.attribute2, hr_api.g_varchar2) or
374 nvl(pqh_acc_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
375 nvl(p_rec.attribute3, hr_api.g_varchar2) or
376 nvl(pqh_acc_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
377 nvl(p_rec.attribute4, hr_api.g_varchar2) or
378 nvl(pqh_acc_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
379 nvl(p_rec.attribute5, hr_api.g_varchar2) or
380 nvl(pqh_acc_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
381 nvl(p_rec.attribute6, hr_api.g_varchar2) or
382 nvl(pqh_acc_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
383 nvl(p_rec.attribute7, hr_api.g_varchar2) or
384 nvl(pqh_acc_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
385 nvl(p_rec.attribute8, hr_api.g_varchar2) or
386 nvl(pqh_acc_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
387 nvl(p_rec.attribute9, hr_api.g_varchar2) or
388 nvl(pqh_acc_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
389 nvl(p_rec.attribute10, hr_api.g_varchar2) or
390 nvl(pqh_acc_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
391 nvl(p_rec.attribute11, hr_api.g_varchar2) or
392 nvl(pqh_acc_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
393 nvl(p_rec.attribute12, hr_api.g_varchar2) or
394 nvl(pqh_acc_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
395 nvl(p_rec.attribute13, hr_api.g_varchar2) or
396 nvl(pqh_acc_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
397 nvl(p_rec.attribute14, hr_api.g_varchar2) or
398 nvl(pqh_acc_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
399 nvl(p_rec.attribute15, hr_api.g_varchar2) or
400 nvl(pqh_acc_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
401 nvl(p_rec.attribute16, hr_api.g_varchar2) or
402 nvl(pqh_acc_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
403 nvl(p_rec.attribute17, hr_api.g_varchar2) or
404 nvl(pqh_acc_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
408 nvl(pqh_acc_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
405 nvl(p_rec.attribute18, hr_api.g_varchar2) or
406 nvl(pqh_acc_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
407 nvl(p_rec.attribute19, hr_api.g_varchar2) or
409 nvl(p_rec.attribute20, hr_api.g_varchar2) or
410 nvl(pqh_acc_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
411 nvl(p_rec.attribute21, hr_api.g_varchar2) or
412 nvl(pqh_acc_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
413 nvl(p_rec.attribute22, hr_api.g_varchar2) or
414 nvl(pqh_acc_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
415 nvl(p_rec.attribute23, hr_api.g_varchar2) or
416 nvl(pqh_acc_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
417 nvl(p_rec.attribute24, hr_api.g_varchar2) or
418 nvl(pqh_acc_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
419 nvl(p_rec.attribute25, hr_api.g_varchar2) or
420 nvl(pqh_acc_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
421 nvl(p_rec.attribute26, hr_api.g_varchar2) or
422 nvl(pqh_acc_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
423 nvl(p_rec.attribute27, hr_api.g_varchar2) or
424 nvl(pqh_acc_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
425 nvl(p_rec.attribute28, hr_api.g_varchar2) or
426 nvl(pqh_acc_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
427 nvl(p_rec.attribute29, hr_api.g_varchar2) or
428 nvl(pqh_acc_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
429 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
430 or (p_rec.accommodation_id is null) then
431 --
432 -- Only execute the validation if absolutely necessary:
433 -- a) During update, the structure column value or any
434 -- of the attribute values have actually changed.
435 -- b) During insert.
436 --
437 hr_dflex_utility.ins_or_upd_descflex_attribs
438 (p_appl_short_name => 'PQH'
439 ,p_descflex_name => 'EDIT_HERE: Enter descflex name'
440 ,p_attribute_category => 'ATTRIBUTE_CATEGORY'
441 ,p_attribute1_name => 'ATTRIBUTE1'
442 ,p_attribute1_value => p_rec.attribute1
443 ,p_attribute2_name => 'ATTRIBUTE2'
444 ,p_attribute2_value => p_rec.attribute2
445 ,p_attribute3_name => 'ATTRIBUTE3'
446 ,p_attribute3_value => p_rec.attribute3
447 ,p_attribute4_name => 'ATTRIBUTE4'
448 ,p_attribute4_value => p_rec.attribute4
449 ,p_attribute5_name => 'ATTRIBUTE5'
450 ,p_attribute5_value => p_rec.attribute5
451 ,p_attribute6_name => 'ATTRIBUTE6'
452 ,p_attribute6_value => p_rec.attribute6
453 ,p_attribute7_name => 'ATTRIBUTE7'
454 ,p_attribute7_value => p_rec.attribute7
455 ,p_attribute8_name => 'ATTRIBUTE8'
456 ,p_attribute8_value => p_rec.attribute8
457 ,p_attribute9_name => 'ATTRIBUTE9'
458 ,p_attribute9_value => p_rec.attribute9
459 ,p_attribute10_name => 'ATTRIBUTE10'
460 ,p_attribute10_value => p_rec.attribute10
461 ,p_attribute11_name => 'ATTRIBUTE11'
462 ,p_attribute11_value => p_rec.attribute11
463 ,p_attribute12_name => 'ATTRIBUTE12'
464 ,p_attribute12_value => p_rec.attribute12
465 ,p_attribute13_name => 'ATTRIBUTE13'
466 ,p_attribute13_value => p_rec.attribute13
467 ,p_attribute14_name => 'ATTRIBUTE14'
468 ,p_attribute14_value => p_rec.attribute14
469 ,p_attribute15_name => 'ATTRIBUTE15'
470 ,p_attribute15_value => p_rec.attribute15
471 ,p_attribute16_name => 'ATTRIBUTE16'
472 ,p_attribute16_value => p_rec.attribute16
473 ,p_attribute17_name => 'ATTRIBUTE17'
474 ,p_attribute17_value => p_rec.attribute17
475 ,p_attribute18_name => 'ATTRIBUTE18'
476 ,p_attribute18_value => p_rec.attribute18
477 ,p_attribute19_name => 'ATTRIBUTE19'
478 ,p_attribute19_value => p_rec.attribute19
479 ,p_attribute20_name => 'ATTRIBUTE20'
480 ,p_attribute20_value => p_rec.attribute20
481 ,p_attribute21_name => 'ATTRIBUTE21'
482 ,p_attribute21_value => p_rec.attribute21
483 ,p_attribute22_name => 'ATTRIBUTE22'
484 ,p_attribute22_value => p_rec.attribute22
485 ,p_attribute23_name => 'ATTRIBUTE23'
486 ,p_attribute23_value => p_rec.attribute23
487 ,p_attribute24_name => 'ATTRIBUTE24'
488 ,p_attribute24_value => p_rec.attribute24
489 ,p_attribute25_name => 'ATTRIBUTE25'
490 ,p_attribute25_value => p_rec.attribute25
491 ,p_attribute26_name => 'ATTRIBUTE26'
492 ,p_attribute26_value => p_rec.attribute26
493 ,p_attribute27_name => 'ATTRIBUTE27'
494 ,p_attribute27_value => p_rec.attribute27
495 ,p_attribute28_name => 'ATTRIBUTE28'
496 ,p_attribute28_value => p_rec.attribute28
497 ,p_attribute29_name => 'ATTRIBUTE29'
498 ,p_attribute29_value => p_rec.attribute29
502 end if;
499 ,p_attribute30_name => 'ATTRIBUTE30'
500 ,p_attribute30_value => p_rec.attribute30
501 );
503 --
504 hr_utility.set_location(' Leaving:'||l_proc,20);
505 end chk_df;
506 --
507 -- ----------------------------------------------------------------------------
508 -- |-----------------------< chk_non_updateable_args >------------------------|
509 -- ----------------------------------------------------------------------------
510 -- {Start Of Comments}
511 --
512 -- Description:
513 -- This procedure is used to ensure that non updateable attributes have
514 -- not been updated. If an attribute has been updated an error is generated.
515 --
516 -- Pre Conditions:
517 -- g_old_rec has been populated with details of the values currently in
518 -- the database.
519 --
520 -- In Arguments:
521 -- p_rec has been populated with the updated values the user would like the
522 -- record set to.
523 --
524 -- Post Success:
525 -- Processing continues if all the non updateable attributes have not
526 -- changed.
527 --
528 -- Post Failure:
529 -- An application error is raised if any of the non updatable attributes
530 -- have been altered.
531 --
532 -- {End Of Comments}
533 -- ----------------------------------------------------------------------------
534 Procedure chk_non_updateable_args
535 (p_effective_date in date
536 ,p_rec in pqh_acc_shd.g_rec_type
537 ) IS
538 --
539 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
540 --
541 Begin
542 --
543 -- Only proceed with the validation if a row exists for the current
544 -- record in the HR Schema.
545 --
546 IF NOT pqh_acc_shd.api_updating
547 (p_accommodation_id => p_rec.accommodation_id
548 ,p_effective_date => p_effective_date
549 ,p_object_version_number => p_rec.object_version_number
550 ) THEN
551 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
552 fnd_message.set_token('PROCEDURE ', l_proc);
553 fnd_message.set_token('STEP ', '5');
554 fnd_message.raise_error;
555 END IF;
556 --
557 -- EDIT_HERE: Add checks to ensure non-updateable args have
558 -- not been updated.
559 --
560 End chk_non_updateable_args;
561 --
562 -- ----------------------------------------------------------------------------
563 -- |--------------------------< dt_update_validate >--------------------------|
564 -- ----------------------------------------------------------------------------
565 -- {Start Of Comments}
566 --
567 -- Description:
568 -- This procedure is used for referential integrity of datetracked
569 -- parent entities when a datetrack update operation is taking place
570 -- and where there is no cascading of update defined for this entity.
571 --
572 -- Prerequisites:
573 -- This procedure is called from the update_validate.
574 --
575 -- In Parameters:
576 --
577 -- Post Success:
578 -- Processing continues.
579 --
580 -- Post Failure:
581 --
582 -- Developer Implementation Notes:
583 -- This procedure should not need maintenance unless the HR Schema model
584 -- changes.
585 --
586 -- Access Status:
587 -- Internal Row Handler Use Only.
588 --
589 -- {End Of Comments}
590 -- ----------------------------------------------------------------------------
591 Procedure dt_update_validate
592 (p_datetrack_mode in varchar2
593 ,p_validation_start_date in date
594 ,p_validation_end_date in date
595 ) Is
596 --
597 l_proc varchar2(72) := g_package||'dt_update_validate';
598 --
599 Begin
600 --
601 -- Ensure that the p_datetrack_mode argument is not null
602 --
603 hr_api.mandatory_arg_error
604 (p_api_name => l_proc
605 ,p_argument => 'datetrack_mode'
606 ,p_argument_value => p_datetrack_mode
607 );
608 --
609 -- Mode will be valid, as this is checked at the start of the upd.
610 --
611 -- Ensure the arguments are not null
612 --
613 hr_api.mandatory_arg_error
614 (p_api_name => l_proc
615 ,p_argument => 'validation_start_date'
616 ,p_argument_value => p_validation_start_date
617 );
618 --
619 hr_api.mandatory_arg_error
620 (p_api_name => l_proc
621 ,p_argument => 'validation_end_date'
622 ,p_argument_value => p_validation_end_date
623 );
624 --
625 --
626 --
627 Exception
628 When Others Then
629 --
630 -- An unhandled or unexpected error has occurred which
631 -- we must report
632 --
633 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
634 fnd_message.set_token('PROCEDURE', l_proc);
635 fnd_message.set_token('STEP','15');
636 fnd_message.raise_error;
637 End dt_update_validate;
638 --
639 -- ----------------------------------------------------------------------------
640 -- |--------------------------< dt_delete_validate >--------------------------|
641 -- ----------------------------------------------------------------------------
642 -- {Start Of Comments}
643 --
644 -- Description:
645 -- This procedure is used for referential integrity of datetracked
649 -- datetracked child rows exist between the validation start and end
646 -- child entities when either a datetrack DELETE or ZAP is in operation
647 -- and where there is no cascading of delete defined for this entity.
648 -- For the datetrack mode of DELETE or ZAP we must ensure that no
650 -- dates.
651 --
652 -- Prerequisites:
653 -- This procedure is called from the delete_validate.
654 --
655 -- In Parameters:
656 --
657 -- Post Success:
658 -- Processing continues.
659 --
660 -- Post Failure:
661 -- If a row exists by determining the returning Boolean value from the
662 -- generic dt_api.rows_exist function then we must supply an error via
663 -- the use of the local exception handler l_rows_exist.
664 --
665 -- Developer Implementation Notes:
666 -- This procedure should not need maintenance unless the HR Schema model
667 -- changes.
668 --
669 -- Access Status:
670 -- Internal Row Handler Use Only.
671 --
672 -- {End Of Comments}
673 -- ----------------------------------------------------------------------------
674 Procedure dt_delete_validate
675 (p_accommodation_id in number
676 ,p_datetrack_mode in varchar2
677 ,p_validation_start_date in date
678 ,p_validation_end_date in date
679 ) Is
680 --
681 l_proc varchar2(72) := g_package||'dt_delete_validate';
682 --
683 Begin
684 --
685 -- Ensure that the p_datetrack_mode argument is not null
686 --
687 hr_api.mandatory_arg_error
688 (p_api_name => l_proc
689 ,p_argument => 'datetrack_mode'
690 ,p_argument_value => p_datetrack_mode
691 );
692 --
693 -- Only perform the validation if the datetrack mode is either
694 -- DELETE or ZAP
695 --
696 If (p_datetrack_mode = hr_api.g_delete or
697 p_datetrack_mode = hr_api.g_zap) then
698 --
699 --
700 -- Ensure the arguments are not null
701 --
702 hr_api.mandatory_arg_error
703 (p_api_name => l_proc
704 ,p_argument => 'validation_start_date'
705 ,p_argument_value => p_validation_start_date
706 );
707 --
708 hr_api.mandatory_arg_error
709 (p_api_name => l_proc
710 ,p_argument => 'validation_end_date'
711 ,p_argument_value => p_validation_end_date
712 );
713 --
714 hr_api.mandatory_arg_error
715 (p_api_name => l_proc
716 ,p_argument => 'accommodation_id'
717 ,p_argument_value => p_accommodation_id
718 );
719 --
720 If (dt_api.rows_exist
721 (p_base_table_name => 'pqh_assign_accommodations_f'
722 ,p_base_key_column => 'accommodation_id'
723 ,p_base_key_value => p_accommodation_id
724 ,p_from_date => p_validation_start_date
725 ,p_to_date => p_validation_end_date
726 )) Then
727 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
728 fnd_message.set_token('TABLE_NAME','pqh_assign_accommodations_f');
729 hr_multi_message.add;
730 End If;
731 --
732 End If;
733 --
734 Exception
735 When Others Then
736 --
737 -- An unhandled or unexpected error has occurred which
738 -- we must report
739 --
740 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
741 fnd_message.set_token('PROCEDURE', l_proc);
742 fnd_message.set_token('STEP','15');
743 fnd_message.raise_error;
744 --
745 End dt_delete_validate;
746 --
747 -- Added following two check procedures for bug fix 3500435
748 procedure chk_unique_name_ins(p_accommodation_name varchar2, p_accommodation_id number,p_business_group_id number)
749 IS
750 --
751 Cursor csr_unique_name is
752 Select null
753 from pqh_accommodations_f
754 where accommodation_name = p_accommodation_name
755 and business_group_id = p_business_group_id;
756 --
757 l_value varchar2(10);
758 Begin
759 --
760 Open csr_unique_name;
761 --
762 Fetch csr_unique_name into l_value;
763 If csr_unique_name%FOUND then
764 fnd_message.set_name('PQH','PQH_ACC_NAME_UNIQUE');
765 fnd_message.set_token('NAME',p_accommodation_name);
766 hr_multi_message.add;
767 End if;
768 --
769 Close csr_unique_name;
770 --
771 End chk_unique_name_ins;
772 --
773 --
774 procedure chk_unique_name_upd(p_accommodation_name varchar2, p_accommodation_id number,p_business_group_id number)
775 IS
776 --
777 Cursor csr_unique_name is
778 Select null
779 from pqh_accommodations_f
780 where accommodation_name = p_accommodation_name
781 and business_group_id = p_business_group_id
782 and accommodation_id <> p_accommodation_id;
783 --
784 l_value varchar2(10);
785 Begin
786 --
787 Open csr_unique_name;
788 --
789 Fetch csr_unique_name into l_value;
790 If csr_unique_name%FOUND then
791 fnd_message.set_name('PQH','PQH_ACC_NAME_UNIQUE');
792 fnd_message.set_token('NAME',p_accommodation_name);
793 hr_multi_message.add;
794 End if;
795 --
796 Close csr_unique_name;
797 --
798 End chk_unique_name_upd;
799 --
800 -- ----------------------------------------------------------------------------
804 (p_rec in pqh_acc_shd.g_rec_type
801 -- |---------------------------< insert_validate >----------------------------|
802 -- ----------------------------------------------------------------------------
803 Procedure insert_validate
805 ,p_effective_date in date
806 ,p_datetrack_mode in varchar2
807 ,p_validation_start_date in date
808 ,p_validation_end_date in date
809 ) is
810 --
811 l_proc varchar2(72) := g_package||'insert_validate';
812 --
813 Begin
814 hr_utility.set_location('Entering:'||l_proc, 5);
815 --
816 -- Call all supporting business operations
817 --
818 hr_api.validate_bus_grp_id
819 (p_business_group_id => p_rec.business_group_id
820 ,p_associated_column1 => pqh_acc_shd.g_tab_nam
821 || '.BUSINESS_GROUP_ID');
822 --
823 -- After validating the set of important attributes,
824 -- if Multiple Message detection is enabled and at least
825 -- one error has been found then abort further validation.
826 --
827 chk_unique_name_ins(p_rec.accommodation_name,p_rec.accommodation_id,p_rec.business_group_id);
828
829 hr_multi_message.end_validation_set;
830 --
831 -- Validate Dependent Attributes
832 --
833 --
834 -- pqh_acc_bus.chk_ddf(p_rec);
835 --
836 -- pqh_acc_bus.chk_df(p_rec);
837 --
838 hr_utility.set_location(' Leaving:'||l_proc, 10);
839 End insert_validate;
840 --
841 -- ----------------------------------------------------------------------------
842 -- |---------------------------< update_validate >----------------------------|
843 -- ----------------------------------------------------------------------------
844 Procedure update_validate
845 (p_rec in pqh_acc_shd.g_rec_type
846 ,p_effective_date in date
847 ,p_datetrack_mode in varchar2
848 ,p_validation_start_date in date
849 ,p_validation_end_date in date
850 ) is
851 --
852 l_proc varchar2(72) := g_package||'update_validate';
853 --
854 Begin
855 hr_utility.set_location('Entering:'||l_proc, 5);
856 --
857 -- Call all supporting business operations
858 --
859 hr_api.validate_bus_grp_id
860 (p_business_group_id => p_rec.business_group_id
861 ,p_associated_column1 => pqh_acc_shd.g_tab_nam
862 || '.BUSINESS_GROUP_ID');
863 --
864 -- After validating the set of important attributes,
865 -- if Multiple Message detection is enabled and at least
866 -- one error has been found then abort further validation.
867 --
868 chk_unique_name_upd(p_rec.accommodation_name,p_rec.accommodation_id,p_rec.business_group_id);
869
870 hr_multi_message.end_validation_set;
871 --
872 -- Validate Dependent Attributes
873 --
874 -- Call the datetrack update integrity operation
875 --
876 dt_update_validate
877 (p_datetrack_mode => p_datetrack_mode
878 ,p_validation_start_date => p_validation_start_date
879 ,p_validation_end_date => p_validation_end_date
880 );
881 --
882 chk_non_updateable_args
883 (p_effective_date => p_effective_date
884 ,p_rec => p_rec
885 );
886 --
887 --
888 -- pqh_acc_bus.chk_ddf(p_rec);
889 --
890 -- pqh_acc_bus.chk_df(p_rec);
891 --
892 hr_utility.set_location(' Leaving:'||l_proc, 10);
893 End update_validate;
894 --
895 -- ----------------------------------------------------------------------------
896 -- |---------------------------< delete_validate >----------------------------|
897 -- ----------------------------------------------------------------------------
898 Procedure delete_validate
899 (p_rec in pqh_acc_shd.g_rec_type
900 ,p_effective_date in date
901 ,p_datetrack_mode in varchar2
902 ,p_validation_start_date in date
903 ,p_validation_end_date in date
904 ) is
905 --
906 l_proc varchar2(72) := g_package||'delete_validate';
907 --
908 Begin
909 hr_utility.set_location('Entering:'||l_proc, 5);
910 --
911 -- Call all supporting business operations
912 --
913 dt_delete_validate
914 (p_datetrack_mode => p_datetrack_mode
915 ,p_validation_start_date => p_validation_start_date
916 ,p_validation_end_date => p_validation_end_date
917 ,p_accommodation_id => p_rec.accommodation_id
918 );
919 --
920 hr_utility.set_location(' Leaving:'||l_proc, 10);
921 End delete_validate;
922 --
923 end pqh_acc_bus;