[Home] [Help]
PACKAGE BODY: APPS.PER_INC_BUS
Source
1 Package Body per_inc_bus as
2 /* $Header: peincrhi.pkb 115.29 2003/08/31 00:49:48 kjagadee noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_inc_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_incident_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_incident_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select null
28 from per_work_incidents inc
29 ,per_people_f per
30 ,hr_organization_information hoi
31 where inc.incident_id = p_incident_id
32 and per.person_id = inc.person_id
33 and hoi.organization_id = per.business_group_id
34 and hoi.org_information_context||'' = 'Business Group Information';
35 --
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) := g_package||'set_security_group_id';
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'incident_id'
50 ,p_argument_value => p_incident_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id;
55 --
56 if csr_sec_grp%notfound then
57 --
58 close csr_sec_grp;
59 --
60 -- The primary key is invalid therefore we must error
61 --
62 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
63 fnd_message.raise_error;
64 --
65 end if;
66 close csr_sec_grp;
67 --
68 -- Set the security_group_id in CLIENT_INFO
69 --
70 hr_api.set_security_group_id
71 (p_security_group_id => l_security_group_id
72 );
73 --
74 hr_utility.set_location(' Leaving:'|| l_proc, 20);
75 --
76 end set_security_group_id;
77 --
78 -- ---------------------------------------------------------------------------
79 -- |---------------------< return_legislation_code >-------------------------|
80 -- ---------------------------------------------------------------------------
81 --
82 Function return_legislation_code
83 (p_incident_id in number
84 )
85 Return Varchar2 Is
86 --
87 -- Declare cursor
88 --
89 cursor csr_leg_code is
90 select pbg.legislation_code
91 from per_work_incidents inc,
92 per_people_f per,
93 per_business_groups pbg
94 where inc.incident_id = p_incident_id
95 and inc.person_id = per.person_id
96 and per.business_group_id = pbg.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 => 'incident_id'
112 ,p_argument_value => p_incident_id
113 );
114 --
115 if ( nvl(per_inc_bus.g_incident_id, hr_api.g_number) = p_incident_id) then
116 --
117 -- The legislation code has already been found with a previous
118 -- call to this function. Just return the value in the global
119 -- variable.
120 --
121 l_legislation_code := per_inc_bus.g_legislation_code;
122 hr_utility.set_location(l_proc, 20);
123 else
124 --
125 -- The ID is different to the last call to this function
126 -- or this is the first call to this function.
127 --
128 open csr_leg_code;
129 fetch csr_leg_code into l_legislation_code;
130 --
131 if csr_leg_code%notfound then
132 --
133 -- The primary key is invalid therefore we must error
134 --
135 close csr_leg_code;
136 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
137 fnd_message.raise_error;
138 end if;
139 hr_utility.set_location(l_proc,30);
140 --
141 -- Set the global variables so the values are
142 -- available for the next call to this function.
143 --
144 close csr_leg_code;
145 per_inc_bus.g_incident_id := p_incident_id;
146 per_inc_bus.g_legislation_code := l_legislation_code;
147 end if;
148 hr_utility.set_location(' Leaving:'|| l_proc, 40);
149 return l_legislation_code;
150 end return_legislation_code;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |-----------------------------< chk_ddf >----------------------------------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description:
157 -- Validates all the Developer Descriptive Flexfield values.
158 --
159 -- Prerequisites:
160 -- All other columns have been validated. Must be called as the
161 -- last step from insert_validate and update_validate.
162 --
163 -- In Arguments:
164 -- p_rec
165 --
166 -- Post Success:
167 -- If the Developer Descriptive Flexfield structure column and data values
168 -- are all valid this procedure will end normally and processing will
169 -- continue.
170 --
171 -- Post Failure:
172 -- If the Developer Descriptive Flexfield structure column value or any of
173 -- the data values are invalid then an application error is raised as
174 -- a PL/SQL exception.
175 --
176 -- Access Status:
177 -- Internal Row Handler Use Only.
178 --
179 -- ----------------------------------------------------------------------------
180 procedure chk_ddf
181 (p_rec in per_inc_shd.g_rec_type
182 ) is
183 --
184 l_proc varchar2(72) := g_package || 'chk_ddf';
185 --
186 begin
187 hr_utility.set_location('Entering:'||l_proc,10);
188 --
189 if ((p_rec.incident_id is not null) and (
190 nvl(per_inc_shd.g_old_rec.inc_information_category, hr_api.g_varchar2) <>
191 nvl(p_rec.inc_information_category, hr_api.g_varchar2) or
192 nvl(per_inc_shd.g_old_rec.inc_information1, hr_api.g_varchar2) <>
193 nvl(p_rec.inc_information1, hr_api.g_varchar2) or
194 nvl(per_inc_shd.g_old_rec.inc_information2, hr_api.g_varchar2) <>
195 nvl(p_rec.inc_information2, hr_api.g_varchar2) or
196 nvl(per_inc_shd.g_old_rec.inc_information3, hr_api.g_varchar2) <>
197 nvl(p_rec.inc_information3, hr_api.g_varchar2) or
198 nvl(per_inc_shd.g_old_rec.inc_information4, hr_api.g_varchar2) <>
199 nvl(p_rec.inc_information4, hr_api.g_varchar2) or
200 nvl(per_inc_shd.g_old_rec.inc_information5, hr_api.g_varchar2) <>
201 nvl(p_rec.inc_information5, hr_api.g_varchar2) or
202 nvl(per_inc_shd.g_old_rec.inc_information6, hr_api.g_varchar2) <>
203 nvl(p_rec.inc_information6, hr_api.g_varchar2) or
204 nvl(per_inc_shd.g_old_rec.inc_information7, hr_api.g_varchar2) <>
205 nvl(p_rec.inc_information7, hr_api.g_varchar2) or
206 nvl(per_inc_shd.g_old_rec.inc_information8, hr_api.g_varchar2) <>
207 nvl(p_rec.inc_information8, hr_api.g_varchar2) or
208 nvl(per_inc_shd.g_old_rec.inc_information9, hr_api.g_varchar2) <>
209 nvl(p_rec.inc_information9, hr_api.g_varchar2) or
210 nvl(per_inc_shd.g_old_rec.inc_information10, hr_api.g_varchar2) <>
211 nvl(p_rec.inc_information10, hr_api.g_varchar2) or
212 nvl(per_inc_shd.g_old_rec.inc_information11, hr_api.g_varchar2) <>
213 nvl(p_rec.inc_information11, hr_api.g_varchar2) or
214 nvl(per_inc_shd.g_old_rec.inc_information12, hr_api.g_varchar2) <>
215 nvl(p_rec.inc_information12, hr_api.g_varchar2) or
216 nvl(per_inc_shd.g_old_rec.inc_information13, hr_api.g_varchar2) <>
217 nvl(p_rec.inc_information13, hr_api.g_varchar2) or
218 nvl(per_inc_shd.g_old_rec.inc_information14, hr_api.g_varchar2) <>
219 nvl(p_rec.inc_information14, hr_api.g_varchar2) or
220 nvl(per_inc_shd.g_old_rec.inc_information15, hr_api.g_varchar2) <>
221 nvl(p_rec.inc_information15, hr_api.g_varchar2) or
222 nvl(per_inc_shd.g_old_rec.inc_information16, hr_api.g_varchar2) <>
223 nvl(p_rec.inc_information16, hr_api.g_varchar2) or
224 nvl(per_inc_shd.g_old_rec.inc_information17, hr_api.g_varchar2) <>
225 nvl(p_rec.inc_information17, hr_api.g_varchar2) or
226 nvl(per_inc_shd.g_old_rec.inc_information18, hr_api.g_varchar2) <>
227 nvl(p_rec.inc_information18, hr_api.g_varchar2) or
228 nvl(per_inc_shd.g_old_rec.inc_information19, hr_api.g_varchar2) <>
229 nvl(p_rec.inc_information19, hr_api.g_varchar2) or
230 nvl(per_inc_shd.g_old_rec.inc_information20, hr_api.g_varchar2) <>
231 nvl(p_rec.inc_information20, hr_api.g_varchar2) or
232 nvl(per_inc_shd.g_old_rec.inc_information21, hr_api.g_varchar2) <>
233 nvl(p_rec.inc_information21, hr_api.g_varchar2) or
234 nvl(per_inc_shd.g_old_rec.inc_information22, hr_api.g_varchar2) <>
235 nvl(p_rec.inc_information22, hr_api.g_varchar2) or
236 nvl(per_inc_shd.g_old_rec.inc_information23, hr_api.g_varchar2) <>
237 nvl(p_rec.inc_information23, hr_api.g_varchar2) or
238 nvl(per_inc_shd.g_old_rec.inc_information24, hr_api.g_varchar2) <>
239 nvl(p_rec.inc_information24, hr_api.g_varchar2) or
240 nvl(per_inc_shd.g_old_rec.inc_information25, hr_api.g_varchar2) <>
241 nvl(p_rec.inc_information25, hr_api.g_varchar2) or
242 nvl(per_inc_shd.g_old_rec.inc_information26, hr_api.g_varchar2) <>
243 nvl(p_rec.inc_information26, hr_api.g_varchar2) or
244 nvl(per_inc_shd.g_old_rec.inc_information27, hr_api.g_varchar2) <>
245 nvl(p_rec.inc_information27, hr_api.g_varchar2) or
246 nvl(per_inc_shd.g_old_rec.inc_information28, hr_api.g_varchar2) <>
247 nvl(p_rec.inc_information28, hr_api.g_varchar2) or
248 nvl(per_inc_shd.g_old_rec.inc_information29, hr_api.g_varchar2) <>
249 nvl(p_rec.inc_information29, hr_api.g_varchar2) or
250 nvl(per_inc_shd.g_old_rec.inc_information30, hr_api.g_varchar2) <>
251 nvl(p_rec.inc_information30, hr_api.g_varchar2) ))
252 or (p_rec.incident_id is null) then
253 --
254 -- Only execute the validation if absolutely necessary:
255 -- a) During update, the structure column value or any
256 -- of the attribute values have actually changed.
257 -- b) During insert.
258 --
259 hr_utility.set_location(' INC Cat:'||p_rec.inc_information_category,20);
260 hr_dflex_utility.ins_or_upd_descflex_attribs
261 (p_appl_short_name => 'PER'
262 ,p_descflex_name => 'Work Incident Developer DF'
263 ,p_attribute_category => p_rec.INC_INFORMATION_CATEGORY
264 ,p_attribute1_name => 'INC_INFORMATION1'
265 ,p_attribute1_value => p_rec.inc_information1
266 ,p_attribute2_name => 'INC_INFORMATION2'
267 ,p_attribute2_value => p_rec.inc_information2
268 ,p_attribute3_name => 'INC_INFORMATION3'
269 ,p_attribute3_value => p_rec.inc_information3
270 ,p_attribute4_name => 'INC_INFORMATION4'
271 ,p_attribute4_value => p_rec.inc_information4
272 ,p_attribute5_name => 'INC_INFORMATION5'
273 ,p_attribute5_value => p_rec.inc_information5
274 ,p_attribute6_name => 'INC_INFORMATION6'
275 ,p_attribute6_value => p_rec.inc_information6
276 ,p_attribute7_name => 'INC_INFORMATION7'
277 ,p_attribute7_value => p_rec.inc_information7
278 ,p_attribute8_name => 'INC_INFORMATION8'
279 ,p_attribute8_value => p_rec.inc_information8
280 ,p_attribute9_name => 'INC_INFORMATION9'
281 ,p_attribute9_value => p_rec.inc_information9
282 ,p_attribute10_name => 'INC_INFORMATION10'
283 ,p_attribute10_value => p_rec.inc_information10
284 ,p_attribute11_name => 'INC_INFORMATION11'
288 ,p_attribute13_name => 'INC_INFORMATION13'
285 ,p_attribute11_value => p_rec.inc_information11
286 ,p_attribute12_name => 'INC_INFORMATION12'
287 ,p_attribute12_value => p_rec.inc_information12
289 ,p_attribute13_value => p_rec.inc_information13
290 ,p_attribute14_name => 'INC_INFORMATION14'
291 ,p_attribute14_value => p_rec.inc_information14
292 ,p_attribute15_name => 'INC_INFORMATION15'
293 ,p_attribute15_value => p_rec.inc_information15
294 ,p_attribute16_name => 'INC_INFORMATION16'
295 ,p_attribute16_value => p_rec.inc_information16
296 ,p_attribute17_name => 'INC_INFORMATION17'
297 ,p_attribute17_value => p_rec.inc_information17
298 ,p_attribute18_name => 'INC_INFORMATION18'
299 ,p_attribute18_value => p_rec.inc_information18
300 ,p_attribute19_name => 'INC_INFORMATION19'
301 ,p_attribute19_value => p_rec.inc_information19
302 ,p_attribute20_name => 'INC_INFORMATION20'
303 ,p_attribute20_value => p_rec.inc_information20
304 ,p_attribute21_name => 'INC_INFORMATION21'
305 ,p_attribute21_value => p_rec.inc_information21
306 ,p_attribute22_name => 'INC_INFORMATION22'
307 ,p_attribute22_value => p_rec.inc_information22
308 ,p_attribute23_name => 'INC_INFORMATION23'
309 ,p_attribute23_value => p_rec.inc_information23
310 ,p_attribute24_name => 'INC_INFORMATION24'
311 ,p_attribute24_value => p_rec.inc_information24
312 ,p_attribute25_name => 'INC_INFORMATION25'
313 ,p_attribute25_value => p_rec.inc_information25
314 ,p_attribute26_name => 'INC_INFORMATION26'
315 ,p_attribute26_value => p_rec.inc_information26
316 ,p_attribute27_name => 'INC_INFORMATION27'
317 ,p_attribute27_value => p_rec.inc_information27
318 ,p_attribute28_name => 'INC_INFORMATION28'
319 ,p_attribute28_value => p_rec.inc_information28
320 ,p_attribute29_name => 'INC_INFORMATION29'
321 ,p_attribute29_value => p_rec.inc_information29
322 ,p_attribute30_name => 'INC_INFORMATION30'
323 ,p_attribute30_value => p_rec.inc_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 per_inc_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.incident_id is not null) and (
368 nvl(per_inc_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
369 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
370 nvl(per_inc_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
371 nvl(p_rec.attribute1, hr_api.g_varchar2) or
372 nvl(per_inc_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
373 nvl(p_rec.attribute2, hr_api.g_varchar2) or
374 nvl(per_inc_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
375 nvl(p_rec.attribute3, hr_api.g_varchar2) or
376 nvl(per_inc_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
377 nvl(p_rec.attribute4, hr_api.g_varchar2) or
378 nvl(per_inc_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
379 nvl(p_rec.attribute5, hr_api.g_varchar2) or
380 nvl(per_inc_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
381 nvl(p_rec.attribute6, hr_api.g_varchar2) or
382 nvl(per_inc_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
383 nvl(p_rec.attribute7, hr_api.g_varchar2) or
384 nvl(per_inc_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
385 nvl(p_rec.attribute8, hr_api.g_varchar2) or
386 nvl(per_inc_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
387 nvl(p_rec.attribute9, hr_api.g_varchar2) or
388 nvl(per_inc_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
389 nvl(p_rec.attribute10, hr_api.g_varchar2) or
390 nvl(per_inc_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
394 nvl(per_inc_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
391 nvl(p_rec.attribute11, hr_api.g_varchar2) or
392 nvl(per_inc_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
393 nvl(p_rec.attribute12, hr_api.g_varchar2) or
395 nvl(p_rec.attribute13, hr_api.g_varchar2) or
396 nvl(per_inc_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
397 nvl(p_rec.attribute14, hr_api.g_varchar2) or
398 nvl(per_inc_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
399 nvl(p_rec.attribute15, hr_api.g_varchar2) or
400 nvl(per_inc_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
401 nvl(p_rec.attribute16, hr_api.g_varchar2) or
402 nvl(per_inc_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
403 nvl(p_rec.attribute17, hr_api.g_varchar2) or
404 nvl(per_inc_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
405 nvl(p_rec.attribute18, hr_api.g_varchar2) or
406 nvl(per_inc_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
407 nvl(p_rec.attribute19, hr_api.g_varchar2) or
408 nvl(per_inc_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
409 nvl(p_rec.attribute20, hr_api.g_varchar2) or
410 nvl(per_inc_shd.g_old_rec.attribute21, hr_api.g_varchar2) <>
411 nvl(p_rec.attribute21, hr_api.g_varchar2) or
412 nvl(per_inc_shd.g_old_rec.attribute22, hr_api.g_varchar2) <>
413 nvl(p_rec.attribute22, hr_api.g_varchar2) or
414 nvl(per_inc_shd.g_old_rec.attribute23, hr_api.g_varchar2) <>
415 nvl(p_rec.attribute23, hr_api.g_varchar2) or
416 nvl(per_inc_shd.g_old_rec.attribute24, hr_api.g_varchar2) <>
417 nvl(p_rec.attribute24, hr_api.g_varchar2) or
418 nvl(per_inc_shd.g_old_rec.attribute25, hr_api.g_varchar2) <>
419 nvl(p_rec.attribute25, hr_api.g_varchar2) or
420 nvl(per_inc_shd.g_old_rec.attribute26, hr_api.g_varchar2) <>
421 nvl(p_rec.attribute26, hr_api.g_varchar2) or
422 nvl(per_inc_shd.g_old_rec.attribute27, hr_api.g_varchar2) <>
423 nvl(p_rec.attribute27, hr_api.g_varchar2) or
424 nvl(per_inc_shd.g_old_rec.attribute28, hr_api.g_varchar2) <>
425 nvl(p_rec.attribute28, hr_api.g_varchar2) or
426 nvl(per_inc_shd.g_old_rec.attribute29, hr_api.g_varchar2) <>
427 nvl(p_rec.attribute29, hr_api.g_varchar2) or
428 nvl(per_inc_shd.g_old_rec.attribute30, hr_api.g_varchar2) <>
429 nvl(p_rec.attribute30, hr_api.g_varchar2) ))
430 or (p_rec.incident_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 => 'PER'
439 ,p_descflex_name => 'PER_WORK_INCIDENTS'
440 ,p_attribute_category => p_rec.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
486 ,p_attribute23_value => p_rec.attribute23
483 ,p_attribute22_name => 'ATTRIBUTE22'
484 ,p_attribute22_value => p_rec.attribute22
485 ,p_attribute23_name => '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
499 ,p_attribute30_name => 'ATTRIBUTE30'
500 ,p_attribute30_value => p_rec.attribute30
501 );
502 end if;
503 --
504 hr_utility.set_location(' Leaving:'||l_proc,20);
505 end chk_df;
506 --
507 --
508 -- ----------------------------------------------------------------------------
509 -- |-----------------------< chk_non_updateable_args >------------------------|
510 -- ----------------------------------------------------------------------------
511 -- {Start Of Comments}
512 --
513 -- Description:
514 -- This procedure is used to ensure that non updateable attributes have
515 -- not been updated. If an attribute has been updated an error is generated.
516 --
517 -- Pre Conditions:
518 -- g_old_rec has been populated with details of the values currently in
519 -- the database.
520 --
521 -- In Arguments:
522 -- p_rec has been populated with the updated values the user would like the
523 -- record set to.
524 --
525 -- Post Success:
526 -- Processing continues if all the non updateable attributes have not
527 -- changed.
528 --
529 -- Post Failure:
530 -- An application error is raised if any of the non updatable attributes
531 -- have been altered.
532 --
533 -- {End Of Comments}
534 -- ----------------------------------------------------------------------------
535 Procedure chk_non_updateable_args
536 (p_effective_date in date
537 ,p_rec in per_inc_shd.g_rec_type ) IS
538 --
539 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
540 l_error EXCEPTION;
541 l_argument varchar2(30);
542 --
543 Begin
544 --
545 -- Only proceed with the validation if a row exists for the current
546 -- record in the HR Schema.
547 --
548 IF NOT per_inc_shd.api_updating
549 (p_incident_id => p_rec.incident_id
550 ,p_object_version_number => p_rec.object_version_number
551 ) THEN
552 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
553 fnd_message.set_token('PROCEDURE ', l_proc);
554 fnd_message.set_token('STEP ', '5');
555 fnd_message.raise_error;
556 END IF;
557 --
558 hr_utility.set_location(l_proc, 30);
559 --
560 if nvl(p_rec.person_id, hr_api.g_number) <>
561 nvl(per_inc_shd.g_old_rec.person_id
562 ,hr_api.g_number) then
563 l_argument := 'person_id';
564 raise l_error;
565 end if;
566 --
567 --
568 EXCEPTION
569 WHEN l_error THEN
570 hr_api.argument_changed_error
571 (p_api_name => l_proc
572 ,p_argument => l_argument);
573 WHEN OTHERS THEN
574 RAISE;
575 End chk_non_updateable_args;
576 --
577 --
578 -- ---------------------------------------------------------------------------
579 -- |----------------------------< chk_person_id >----------------------------|
580 -- ---------------------------------------------------------------------------
581 --
582 -- Desciption :
583 --
584 -- Validate that on insert PERSON_ID is not null and that
585 -- it exists in per_all_people_f on the effective_date.
586 --
587 --
588 -- Pre-conditions :
589 --
590 --
591 -- In Arguments :
592 -- p_incident_id
593 -- p_person_id
594 -- p_effective_date
595 --
596 -- Post Success :
597 -- Processing continues
598 --
599 -- Post Failure :
600 -- An application error will be raised and processing is
601 -- terminated
602 --
603 -- Access Status :
604 -- Internal Table Handler Use only.
605 --
606 -- {End of Comments}
607 --
608 -- ---------------------------------------------------------------------------
609 procedure chk_person_id
610 (p_incident_id in per_work_incidents.incident_id%TYPE,
611 p_person_id in per_work_incidents.person_id%TYPE,
612 p_effective_date in date
613 ) is
614 --
615 l_proc varchar2(72) := g_package||'chk_person_id';
616 l_dummy number;
617 --
618 cursor csr_person_id is
619 select null
620 from per_people_f per
621 where per.person_id = p_person_id
625 begin
622 and p_effective_date between per.effective_start_date
623 and per.effective_end_date;
624 --
626 hr_utility.set_location('Entering:'||l_proc, 1);
627 --
628 -- Check mandatory person_id is set
629 --
630 if p_person_id is null then
631 hr_utility.set_message(800, 'HR_52891_INC_PERSON_ID_NULL');
632 hr_utility.raise_error;
633 end if;
634 --
635 hr_utility.set_location(l_proc, 5);
636 --
637 --
638 --
639 -- Only proceed with validation if :
640 -- a) on insert (non-updateable param)
641 --
642 if (p_incident_id is null) then
643 --
644 hr_utility.set_location(l_proc, 10);
645 --
646 -- Check that the person_id is in the per_people_f view on the effective_date.
647 --
648 open csr_person_id;
649 fetch csr_person_id into l_dummy;
650 if csr_person_id%notfound then
651 close csr_person_id;
652 hr_utility.set_message(800, 'HR_52896_INC_FK_NOT_FOUND');
653 hr_utility.raise_error;
654 end if;
655 close csr_person_id;
656 end if;
657 --
658 hr_utility.set_location(' Leaving:'||l_proc, 15);
659 --
660 end chk_person_id;
661 --
662 --
663 -- ---------------------------------------------------------------------------
664 -- |----------------------------< chk_incident_date >-------------------------|
665 -- ---------------------------------------------------------------------------
666 --
667 -- Description :
668 --
669 -- Validate that INCIDENT_DATE is not null
670 --
671 -- Pre-conditions :
672 --
673 --
674 -- In Arguments :
675 -- p_incident_date
676 --
677 -- Post Success :
678 -- Processing continues
679 --
680 -- Post Failure :
681 -- An application error will be raised and processing is
682 -- terminated
683 --
684 -- Access Status :
685 -- Internal Table Handler Use only.
686 --
687 -- {End of Comments}
688 --
689 -- ---------------------------------------------------------------------------
690 procedure chk_incident_date
691 (p_incident_date in per_work_incidents.incident_date%TYPE
692 ) is
693 --
694 l_proc varchar2(72) := g_package||'chk_incident_date';
695 --
696 begin
697 hr_utility.set_location('Entering:'||l_proc, 1);
698 --
699 -- Check mandatory incident_date is set
700 --
701 if p_incident_date is null then
702 hr_utility.set_message(800, 'HR_52895_INC_INC_DATE_NULL');
703 hr_utility.raise_error;
704 end if;
705 --
706 hr_utility.set_location(' Leaving:'||l_proc, 5);
707 --
708 end chk_incident_date;
709 --
710 --
711 -- ---------------------------------------------------------------------------
712 -- |----------------------------< chk_incident_time >-------------------------|
713 -- ---------------------------------------------------------------------------
714 --
715 -- Desciption :
716 --
717 -- Validate that INCIDENT_TIME is valid time in 24HH:MI.
718 --
719 -- Pre-conditions :
720 --
721 --
722 -- In Arguments :
723 -- p_incident_time
724 --
725 -- Post Success :
726 -- Processing continues
727 --
728 -- Post Failure :
729 -- An application error will be raised and processing is
730 -- terminated
731 --
732 -- Access Status :
733 -- Internal Table Handler Use only.
734 --
735 -- {End of Comments}
736 --
737 -- ---------------------------------------------------------------------------
738 procedure chk_incident_time
739 (p_incident_time in per_work_incidents.incident_time%TYPE
740 ) is
741 --
742 l_proc varchar2(72) := g_package||'chk_incident_time';
743 l_value varchar2(60) := p_incident_time;
744 l_output varchar2(60) := p_incident_time;
745 l_rgeflg varchar2(1);
746 --
747 begin
748 hr_utility.set_location('Entering:'||l_proc, 1);
749 --
750 -- Check incident_time is in HH24:MI format
751 --
752 if p_incident_time is not null then
753 begin
754
755 hr_chkfmt.checkformat(l_value, 'TIMES', l_output, NULL, NULL, 'Y', l_rgeflg, NULL);
756
757 if p_incident_time <> l_output then
758 hr_utility.set_message(800, 'HR_289019_INC_INV_ITIME');
759 hr_utility.raise_error;
760 end if;
761
762 exception
763 when others then
764 hr_utility.set_message(801,'HR_7916_CHECK_FMT_HHMM');
765 hr_utility.set_message_token('ARG_NAME', 'incident_time');
766 hr_utility.set_message_token('ARG_VALUE', p_incident_time);
767 hr_utility.raise_error;
768 end;
769 end if;
770 --
771 hr_utility.set_location(' Leaving:'||l_proc, 5);
772 --
773 end chk_incident_time;
774 --
775 --
776 -- ---------------------------------------------------------------------------
777 -- |----------------------< get_osha_case_number >---------------------------|
778 -- ---------------------------------------------------------------------------
782 ,p_bg_id in number
779 --
780 Function get_osha_case_number
781 (p_date in date
783 )
784 Return Varchar2 Is
785 --
786 -- Declare cursor
787 --
788 cursor csr_num(p_year number,p_bg_id number) is
789 SELECT next_value
790 FROM per_us_osha_numbers
791 WHERE case_year = p_year
792 AND business_group_id = p_bg_id
793 FOR UPDATE OF next_value NOWAIT;
794 --
795 -- Declare local variables
796 --
797 l_case_num per_work_incidents.incident_reference%type;
798 l_next_num per_us_osha_numbers.next_value%type;
799 p_year number;
800 l_proc varchar2(72) := g_package||'get_osha_case_number';
801 --
802 Begin
803 --
804 hr_utility.set_location('Entering:'|| l_proc, 10);
805 p_year :=to_number(to_char(p_date,'YYYY'));
806 hr_utility.set_location('Year '||p_year||'-'|| l_proc, 11);
807 hr_utility.set_location('BGID '||p_bg_id||'-'|| l_proc, 12);
808 --
809 -- Get the next case number from cursor
810 --
811 open csr_num(p_year,p_bg_id);
812 fetch csr_num into l_next_num;
813 --
814 if csr_num%notfound then
815 --
816 close csr_num;
817 hr_utility.set_location(l_proc, 20);
818 fnd_message.set_name('PER','HR_289811_INC_RUN_CONC_OSHA');
819 fnd_message.raise_error;
820 --
821 else
822 --
823 UPDATE per_us_osha_numbers
824 SET next_value = next_value+1
825 WHERE CURRENT OF csr_num;
826 --
827 close csr_num;
828 --
829 l_case_num := substr(to_char(p_year),3,2)||'-'||l_next_num;
830 hr_utility.set_location('Case num '||l_case_num, 25);
831 hr_utility.set_location(' Leaving:'|| l_proc, 30);
832 return l_case_num;
833 --
834 end if;
835 hr_utility.set_location(' Leaving:'|| l_proc, 30);
836 --
837 end get_osha_case_number;
838 --
839 --
840 -- ---------------------------------------------------------------------------
841 -- |----------------------< chk_incident_reference >-------------------------|
842 -- ---------------------------------------------------------------------------
843 --
844 -- Desciption :
845 --
846 -- Validates that value for mandatory INCIDENT_REFERENCE has been supplied
847 -- and that it is unique (case insensitive).
848 --
849 -- Pre-conditions :
850 -- Format mask must be supplied.
851 --
852 -- In Arguments :
853 -- p_incident_reference
854 --
855 -- Post Success :
856 -- Processing continues
857 --
858 -- Post Failure :
859 -- An application error will be raised and processing is
860 -- terminated
861 --
862 -- Access Status :
863 -- Public Access.
864 --
865 -- {End of Comments}
866 --
867 -- ---------------------------------------------------------------------------
868 procedure chk_incident_reference
869 (p_incident_id in per_work_incidents.incident_id%TYPE
870 ,p_incident_reference in per_work_incidents.incident_reference%TYPE
871 ,p_object_version_number in per_work_incidents.object_version_number%TYPE) is
872 --
873 cursor csr_ref_unique is
874 select null
875 from per_work_incidents pwi
876 where upper(pwi.incident_reference) = upper(p_incident_reference);
877 --
878 l_proc varchar2(72) := g_package||'chk_incident_reference';
879 l_dummy varchar2(30);
880 l_api_updating boolean;
881
882 --
883 begin
884 hr_utility.set_location('Entering:'||l_proc, 1);
885 --
886 --
887 if p_incident_reference is not null then
888
889 -- This condition added to avoid : unique reference for US OSHA
890 --
891 IF (hr_api.get_legislation_context) <> 'US' then
892 --
893
894 l_api_updating := per_inc_shd.api_updating
895 (p_incident_id => p_incident_id
896 ,p_object_version_number => p_object_version_number);
897 hr_utility.set_location(l_proc, 30);
898 --
899 -- Check if the incident is being inserted or updated.
900 --
901 if ((l_api_updating and
902 nvl(per_inc_shd.g_old_rec.incident_reference, hr_api.g_varchar2)
903 <> nvl(p_incident_reference, hr_api.g_varchar2))
904 or (NOT l_api_updating))
905 then
906 --
907 hr_utility.set_location(l_proc, 10);
908 --
909 -- validate unique reference.#1296633
910 --
911 open csr_ref_unique;
912 fetch csr_ref_unique into l_dummy;
913 if csr_ref_unique%found then
914 close csr_ref_unique;
915 hr_utility.set_message(800, 'HR_52889_INC_REF_NOT_UNIQUE');
916 hr_utility.raise_error;
917 end if;
918 close csr_ref_unique;
919 end if;
920 --
921 END IF ;
922 --
923 else
924 hr_utility.set_message(800, 'HR_52890_INC_REF_NOT_NULL');
925 hr_utility.raise_error;
926 end if;
927 --
928 --
929 hr_utility.set_location(' Leaving:'||l_proc, 3);
930 --
931 end chk_incident_reference;
932 --
933 --
937 --
934 -- ---------------------------------------------------------------------------
935 -- |-------------------------< chk_incident_type >---------------------------|
936 -- ---------------------------------------------------------------------------
938 -- Description:
939 -- Validates that an incident type exists in view hr_leg_lookups
940 -- where lookup_type is 'INCIDENT_TYPE' and enabled_flag is 'Y' and
941 -- effective_date is between the active dates (if they are not null).
942 -- Incident type is mandatory.
943 --
944 -- Pre-conditions:
945 -- Effective_date must be valid.
946 --
947 -- In Arguments:
948 -- p_incident_id
949 -- p_incident_type
950 -- p_effective_date
951 --
952 -- Post Success:
953 -- If a row does exist in hr_leg_lookups for the given incident then
954 -- processing continues.
955 --
956 -- Post Failure:
957 -- If a row does not exist in hr_leg_lookups for the given incident code then
958 -- an application error will be raised and processing is terminated.
959 --
960 -- Access Status:
961 -- Internal Table Handler Use Only.
962 --
963 -- {End Of Comments}
964 -- ----------------------------------------------------------------------------
965 procedure chk_incident_type
966 (p_incident_id in per_work_incidents.incident_id%TYPE,
967 p_incident_type in per_work_incidents.incident_type%TYPE,
968 p_effective_date in date) is
969 --
970 l_proc varchar2(72) := g_package||'chk_incident_type';
971 --
972 begin
973 hr_utility.set_location('Entering:'|| l_proc, 1);
974 --
975 -- Check mandatory parameters have been set
976 --
977 hr_api.mandatory_arg_error
978 (p_api_name => l_proc
979 ,p_argument => 'effective_date'
980 ,p_argument_value => p_effective_date
981 );
982 --
983 --
984 if p_incident_type is not null then
985 --
986 hr_utility.set_location(l_proc, 5);
987 --
988 -- Only proceed with validation if :
989 -- a) Inserting or
990 -- b) The value for incident type has changed
991 --
992 if ((p_incident_id is null) or
993 ((p_incident_id is not null) and
994 (per_inc_shd.g_old_rec.incident_type <> p_incident_type))) then
995 --
996 -- This condition is added for US OSHA specific
997 --
998 IF (hr_api.get_legislation_context) = 'US' then
999 --
1000 if hr_api.not_exists_in_leg_lookups
1001 (p_effective_date => p_effective_date
1002 ,p_lookup_type => 'US_OSHA_CATEGORY'
1003 ,p_lookup_code => p_incident_type
1004 )
1005 then
1006 --
1007 hr_utility.set_message(800, 'HR_52897_INC_INC_TYPE_INV');
1008 hr_utility.raise_error;
1009 --
1010 end if;
1011 END IF;
1012 --
1013 -- This condition is for non US legislations
1014 --
1015 --
1016 IF (hr_api.get_legislation_context) <> 'US' then
1017 --
1018 if hr_api.not_exists_in_leg_lookups
1019 (p_effective_date => p_effective_date
1020 ,p_lookup_type => 'INCIDENT_TYPE'
1021 ,p_lookup_code => p_incident_type
1022 )
1023 then
1024 --
1025 hr_utility.set_message(800, 'HR_52897_INC_INC_TYPE_INV');
1026 hr_utility.raise_error;
1027 --
1028 end if;
1029 --
1030 END IF;
1031 --
1032 end if;
1033 --
1034 else
1035 hr_utility.set_location(l_proc, 15);
1036 hr_utility.set_message(800, 'HR_52898_INC_INC_TYPE_NULL');
1037 hr_utility.raise_error;
1038 end if;
1039 --
1040 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1041 --
1042 end chk_incident_type;
1043 --
1044 --
1045 -- ---------------------------------------------------------------------------
1046 -- |---------------------< chk_related_incident_id >-------------------------|
1047 -- ---------------------------------------------------------------------------
1048 --
1049 -- Desciption :
1050 --
1051 -- Validates related incident is for the current person and the incident selected
1052 -- has an incident date on or before the current incident date. The related incident
1053 -- must be different to the current incident record.
1054 --
1055 -- Pre-conditions :
1056 -- Format mask must be supplied.
1057 --
1058 -- In Arguments :
1059 -- p_incident_id
1060 -- p_related_incident_id
1061 -- p_person_id
1062 -- p_incident_date
1063 --
1064 -- Post Success :
1065 -- Processing continues
1066 --
1067 -- Post Failure :
1068 -- An application error will be raised and processing is
1069 -- terminated
1070 --
1071 -- Access Status :
1072 -- Public Access.
1073 --
1074 -- {End of Comments}
1075 --
1076 -- ---------------------------------------------------------------------------
1077 procedure chk_related_incident_id
1078 (p_incident_id in per_work_incidents.incident_id%TYPE
1082 --
1079 ,p_related_incident_id in per_work_incidents.related_incident_id%TYPE
1080 ,p_person_id in per_work_incidents.person_id%TYPE
1081 ,p_incident_date in per_work_incidents.incident_date%TYPE) is
1083 cursor csr_rel_inc is
1084 select null
1085 from per_work_incidents pwi
1086 where pwi.incident_id = p_related_incident_id
1087 and pwi.person_id = p_person_id
1088 and pwi.incident_date <= p_incident_date;
1089 --
1090 l_proc varchar2(72) := g_package||'chk_related_incident_id';
1091 --
1092 begin
1093 hr_utility.set_location('Entering:'||l_proc, 1);
1094 --
1095 --
1096 if ((p_incident_id is null) or
1097 ((p_incident_id is not null) and
1098 (per_inc_shd.g_old_rec.related_incident_id <> p_related_incident_id))) then
1099 --
1100 -- related inc cannot be current record
1101 if p_incident_id = p_related_incident_id then
1102 hr_utility.set_message(800, 'HR_289020_INC_INV_RELINC');
1103 hr_utility.raise_error;
1104 end if;
1105 --
1106 hr_utility.set_location(l_proc, 20);
1107 --
1108 open csr_rel_inc;
1109 if csr_rel_inc%notfound then
1110 close csr_rel_inc;
1111 -- related inc not found for the person
1112 -- before the current record's incident date.
1113 hr_utility.set_message(800, 'HR_289020_INC_INV_RELINC');
1114 hr_utility.raise_error;
1115 end if;
1116 close csr_rel_inc;
1117 --
1118 end if;
1119 --
1120 hr_utility.set_location(' Leaving:'||l_proc, 3);
1121 --
1122 end chk_related_incident_id;
1123 --
1124 --
1125 -- ---------------------------------------------------------------------------
1126 -- |-----------------------< chk_org_notified_date >-------------------------|
1127 -- ---------------------------------------------------------------------------
1128 --
1129 -- Desciption :
1130 --
1131 -- Validate that NOTIFIED_ORG_DATE does not pre-date INCIDENT_DATE.
1132 --
1133 -- Pre-conditions :
1134 --
1135 --
1136 -- In Arguments :
1137 -- p_org_notified_date
1138 -- p_incident_date
1139 --
1140 -- Post Success :
1141 -- Processing continues
1142 --
1143 -- Post Failure :
1144 -- An application error will be raised and processing is
1145 -- terminated
1146 --
1147 -- Access Status :
1148 -- Internal Table Handler Use only.
1149 --
1150 -- {End of Comments}
1151 --
1152 -- ---------------------------------------------------------------------------
1153 procedure chk_org_notified_date
1154 (p_org_notified_date in per_work_incidents.org_notified_date%TYPE
1155 ,p_incident_date in per_work_incidents.incident_date%TYPE
1156 ) is
1157 --
1158 l_proc varchar2(72) := g_package||'chk_org_notified_date';
1159 --
1160 begin
1161 hr_utility.set_location('Entering:'||l_proc, 1);
1162 --
1163 if p_org_notified_date is not null then
1164 if p_org_notified_date < p_incident_date then
1165 hr_utility.set_message(800, 'HR_289021_INC_INV_NODATE');
1166 hr_utility.raise_error;
1167 end if;
1168 end if;
1169 --
1170 hr_utility.set_location(' Leaving:'||l_proc, 5);
1171 --
1172 end chk_org_notified_date;
1173 --
1174 --
1175 -- ---------------------------------------------------------------------------
1176 -- |------------------------< chk_over_time_flag >--------------------------|
1177 -- ---------------------------------------------------------------------------
1178 --
1179 -- Description:
1180 -- Validates that OVER_TIME_FLAG is NULL or 'Y' or 'N'.
1181 --
1182 -- Pre-conditions:
1183 --
1184 -- In Arguments:
1185 -- p_over_time_flag
1186 --
1187 -- Post Success:
1188 -- Processing continues.
1189 --
1190 -- Post Failure:
1191 -- An application error will be raised and processing is terminated.
1192 --
1193 -- Access Status:
1194 -- Internal Table Handler Use Only.
1195 --
1196 -- {End Of Comments}
1197 -- ----------------------------------------------------------------------------
1198 procedure chk_over_time_flag
1199 (p_over_time_flag in per_work_incidents.over_time_flag%TYPE) is
1200 --
1201 l_proc varchar2(72) := g_package||'chk_over_time_flag';
1202 --
1203 begin
1204 hr_utility.set_location('Entering:'|| l_proc, 1);
1205 --
1206 if p_over_time_flag is not null then
1207 if p_over_time_flag not in ('Y','N') then
1208 hr_utility.set_message(800, 'HR_289028_INC_INV_OTF');
1209 hr_utility.raise_error;
1210 end if;
1211 end if;
1212 --
1213 hr_utility.set_location('Leaving:'|| l_proc, 5);
1214 --
1215 end chk_over_time_flag;
1216 -- ---------------------------------------------------------------------------
1217 -- |-------------------------< chk_at_work_flag >---------------------------|
1218 -- ---------------------------------------------------------------------------
1219 --
1220 -- Description:
1221 -- Validates that mandatory an at_work_flag is set, and exists in hr_leg_lookups
1222 -- for the lookup_type 'AT_WORK_FLAG'.
1223 --
1224 -- Pre-conditions:
1225 --
1226 -- In Arguments:
1227 -- p_incident_id
1228 -- p_at_work_flag
1232 -- Processing continues.
1229 -- p_effective_date
1230 --
1231 -- Post Success:
1233 --
1234 -- Post Failure:
1235 -- An application error will be raised and processing is terminated.
1236 --
1237 -- Access Status:
1238 -- Internal Table Handler Use Only.
1239 --
1240 -- {End Of Comments}
1241 -- ----------------------------------------------------------------------------
1242 procedure chk_at_work_flag
1243 (p_incident_id in per_work_incidents.incident_id%TYPE,
1244 p_at_work_flag in per_work_incidents.at_work_flag%TYPE,
1245 p_effective_date in date) is
1246 --
1247 l_proc varchar2(72) := g_package||'chk_at_work_flag';
1248 --
1249 begin
1250 hr_utility.set_location('Entering:'|| l_proc, 1);
1251 --
1252 -- Check mandatory parameters have been set
1253 --
1254 hr_api.mandatory_arg_error
1255 (p_api_name => l_proc
1256 ,p_argument => 'effective_date'
1257 ,p_argument_value => p_effective_date
1258 );
1259 --
1260 --
1261 -- The following IF conditon added to eliminate this check for US OSHA.
1262 --
1263 IF hr_api.get_legislation_context <> 'US' then
1264 --
1265 if p_at_work_flag is not null then
1266 --
1267 hr_utility.set_location(l_proc, 5);
1268 --
1269 -- Only proceed with validation if :
1270 -- a) Inserting or
1271 -- b) The value for at_work_flag has changed
1272 --
1273 if ((p_incident_id is null) or
1274 ((p_incident_id is not null) and
1275 (per_inc_shd.g_old_rec.at_work_flag <> p_at_work_flag))) then
1276 --
1277 if hr_api.not_exists_in_leg_lookups
1278 (p_effective_date => p_effective_date
1279 ,p_lookup_type => 'AT_WORK_FLAG'
1280 ,p_lookup_code => p_at_work_flag
1281 )
1282 then
1283 --
1284 hr_utility.set_message(800, 'HR_52899_INC_AWF_INV');
1285 hr_utility.raise_error;
1286 --
1287 end if;
1288 end if;
1289 else
1290 hr_utility.set_message(800, 'HR_52899_INC_AWF_INV');
1291 hr_utility.raise_error;
1292 end if;
1293 --
1294 END IF;
1295 --
1296 --
1297 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1298 --
1299 end chk_at_work_flag;
1300 --
1301 --
1302 -- ---------------------------------------------------------------------------
1303 -- |--------------------------< chk_hazard_type >---------------------------|
1304 -- ---------------------------------------------------------------------------
1305 --
1306 -- Description:
1307 -- Validates that value for HAZARD_TYPE exists in hr_leg_lookups for the
1308 -- lookup_type of 'HAZARD_TYPE'.
1309 --
1310 -- Pre-conditions:
1311 --
1312 -- In Arguments:
1313 -- p_incident_id
1314 -- p_hazard_type
1315 -- p_effective_date
1316 --
1317 -- Post Success:
1318 -- Processing continues.
1319 --
1320 -- Post Failure:
1321 -- An application error will be raised and processing is terminated.
1322 --
1323 -- Access Status:
1324 -- Internal Table Handler Use Only.
1325 --
1326 -- {End Of Comments}
1327 -- ----------------------------------------------------------------------------
1328 procedure chk_hazard_type
1329 (p_incident_id in per_work_incidents.incident_id%TYPE,
1330 p_hazard_type in per_work_incidents.at_work_flag%TYPE,
1331 p_effective_date in date) is
1332 --
1333 l_proc varchar2(72) := g_package||'chk_hazard_type';
1334 --
1335 begin
1336 hr_utility.set_location('Entering:'|| l_proc, 1);
1337 --
1338 -- Check mandatory parameters have been set
1339 --
1340 hr_api.mandatory_arg_error
1341 (p_api_name => l_proc
1342 ,p_argument => 'effective_date'
1343 ,p_argument_value => p_effective_date
1344 );
1345 --
1346 if p_hazard_type is not null then
1347 --
1348 hr_utility.set_location(l_proc, 5);
1349 --
1350 -- Only proceed with validation if :
1351 -- a) Inserting or
1352 -- b) The value for hazard_type has changed
1353 --
1354 if ((p_incident_id is null) or
1355 ((p_incident_id is not null) and
1356 (per_inc_shd.g_old_rec.hazard_type <> p_hazard_type))) then
1357 --
1358 if hr_api.not_exists_in_leg_lookups
1359 (p_effective_date => p_effective_date
1360 ,p_lookup_type => 'HAZARD_TYPE'
1361 ,p_lookup_code => p_hazard_type
1362 )
1363 then
1364 --
1365 hr_utility.set_message(800, 'HR_289022_INC_HAZ_TYPE_INV');
1366 hr_utility.raise_error;
1367 --
1368 end if;
1369 end if;
1370 end if;
1371 --
1372 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1373 --
1374 end chk_hazard_type;
1375 --
1376 --
1377 -- ---------------------------------------------------------------------------
1378 -- |--------------------------< chk_injury_type >---------------------------|
1379 -- ---------------------------------------------------------------------------
1380 --
1381 -- Description:
1385 -- Pre-conditions:
1382 -- Validates that value for INJURY_TYPE exists in hr_leg_lookups for the
1383 -- lookup_type of 'INJURY_TYPE'.
1384 --
1386 --
1387 -- In Arguments:
1388 -- p_incident_id
1389 -- p_injury_type
1390 -- p_effective_date
1391 --
1392 -- Post Success:
1393 -- Processing continues.
1394 --
1395 -- Post Failure:
1396 -- An application error will be raised and processing is terminated.
1397 --
1398 -- Access Status:
1399 -- Internal Table Handler Use Only.
1400 --
1401 -- {End Of Comments}
1402 -- ----------------------------------------------------------------------------
1403 procedure chk_injury_type
1404 (p_incident_id in per_work_incidents.incident_id%TYPE,
1405 p_injury_type in per_work_incidents.at_work_flag%TYPE,
1406 p_effective_date in date) is
1407 --
1408 l_proc varchar2(72) := g_package||'chk_injury_type';
1409 --
1410 begin
1411 hr_utility.set_location('Entering:'|| l_proc, 1);
1412 --
1413 -- Check mandatory parameters have been set
1414 --
1415 hr_api.mandatory_arg_error
1416 (p_api_name => l_proc
1417 ,p_argument => 'effective_date'
1418 ,p_argument_value => p_effective_date
1419 );
1420 --
1421 if p_injury_type is not null then
1422 --
1423 hr_utility.set_location(l_proc, 5);
1424 --
1425 -- Only proceed with validation if :
1426 -- a) Inserting or
1427 -- b) The value for injury_type has changed
1428 --
1429 if ((p_incident_id is null) or
1430 ((p_incident_id is not null) and
1431 (per_inc_shd.g_old_rec.injury_type <> p_injury_type))) then
1432 --
1433 if hr_api.not_exists_in_leg_lookups
1434 (p_effective_date => p_effective_date
1435 ,p_lookup_type => 'INJURY_TYPE'
1436 ,p_lookup_code => p_injury_type
1437 )
1438 then
1439 --
1440 hr_utility.set_message(800, 'HR_289023_INC_INJ_TYPE_INV');
1441 hr_utility.raise_error;
1442 --
1443 end if;
1444 end if;
1445 end if;
1446 --
1447 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1448 --
1449 end chk_injury_type;
1450 --
1451 --
1452 -- ---------------------------------------------------------------------------
1453 -- |--------------------------< chk_diease_type >---------------------------|
1454 -- ---------------------------------------------------------------------------
1455 --
1456 -- Description:
1457 -- Validates that value for DISEASE_TYPE exists in hr_leg_lookups for the
1458 -- lookup_type of 'DISEASE_TYPE'.
1459 --
1460 -- Pre-conditions:
1461 --
1462 -- In Arguments:
1463 -- p_incident_id
1464 -- p_disease_type
1465 -- p_effective_date
1466 --
1467 -- Post Success:
1468 -- Processing continues.
1469 --
1470 -- Post Failure:
1471 -- An application error will be raised and processing is terminated.
1472 --
1473 -- Access Status:
1474 -- Internal Table Handler Use Only.
1475 --
1476 -- {End Of Comments}
1477 -- ----------------------------------------------------------------------------
1478 procedure chk_disease_type
1479 (p_incident_id in per_work_incidents.incident_id%TYPE,
1480 p_disease_type in per_work_incidents.at_work_flag%TYPE,
1481 p_effective_date in date) is
1482 --
1483 l_proc varchar2(72) := g_package||'chk_disease_type';
1484 --
1485 begin
1486 hr_utility.set_location('Entering:'|| l_proc, 1);
1487 --
1488 -- Check mandatory parameters have been set
1489 --
1490 hr_api.mandatory_arg_error
1491 (p_api_name => l_proc
1492 ,p_argument => 'effective_date'
1493 ,p_argument_value => p_effective_date
1494 );
1495 --
1496 if p_disease_type is not null then
1497 --
1498 hr_utility.set_location(l_proc, 5);
1499 --
1500 -- Only proceed with validation if :
1501 -- a) Inserting or
1502 -- b) The value for disease_type has changed
1503 --
1504 if ((p_incident_id is null) or
1505 ((p_incident_id is not null) and
1506 (per_inc_shd.g_old_rec.disease_type <> p_disease_type))) then
1507 --
1508 if hr_api.not_exists_in_leg_lookups
1509 (p_effective_date => p_effective_date
1510 ,p_lookup_type => 'DISEASE_TYPE'
1511 ,p_lookup_code => p_disease_type
1512 )
1513 then
1514 --
1515 hr_utility.set_message(800, 'HR_289024_INC_DIS_TYPE_INV');
1516 hr_utility.raise_error;
1517 --
1518 end if;
1519 end if;
1520 end if;
1521 --
1522 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1523 --
1524 end chk_disease_type;
1525 --
1526 --
1527 --
1528 -- ---------------------------------------------------------------------------
1529 -- |--------------------------< chk_work_start_time >------------------------|
1530 -- ---------------------------------------------------------------------------
1531 --
1532 -- Desciption :
1533 --
1537 --
1534 -- Validate that WORK_START_TIME is valid time in 24HH:MI.
1535 --
1536 -- Pre-conditions :
1538 --
1539 -- In Arguments :
1540 -- p_work_start_time
1541 --
1542 -- Post Success :
1543 -- Processing continues
1544 --
1545 -- Post Failure :
1546 -- An application error will be raised and processing is
1547 -- terminated
1548 --
1549 -- Access Status :
1550 -- Internal Table Handler Use only.
1551 --
1552 -- {End of Comments}
1553 --
1554 -- ---------------------------------------------------------------------------
1555 procedure chk_work_start_time
1556 (p_work_start_time in per_work_incidents.work_start_time%TYPE
1557 ) is
1558 --
1559 l_proc varchar2(72) := g_package||'chk_work_start_time';
1560 l_value varchar2(60) := p_work_start_time;
1561 l_output varchar2(60) := p_work_start_time;
1562 l_rgeflg varchar2(1);
1563 --
1564 begin
1565 hr_utility.set_location('Entering:'||l_proc, 1);
1566 --
1567 -- Check work_start_time is in HH24:MI format
1568 --
1569 if p_work_start_time is not null then
1570 begin
1571
1572 hr_chkfmt.checkformat(l_value, 'TIMES', l_output, NULL, NULL, 'Y', l_rgeflg, NULL);
1573
1574 if p_work_start_time <> l_output then
1575 hr_utility.set_message(800, 'HR_289810_INC_INV_WKST_TIME');
1576 hr_utility.raise_error;
1577 end if;
1578
1579 exception
1580 when others then
1581 hr_utility.set_message(801,'HR_7916_CHECK_FMT_HHMM');
1582 hr_utility.set_message_token('ARG_NAME', 'work_start_time');
1583 hr_utility.set_message_token('ARG_VALUE', p_work_start_time);
1584 hr_utility.raise_error;
1585 end;
1586
1587 end if;
1588 --
1589 hr_utility.set_location(' Leaving:'||l_proc, 5);
1590 --
1591 end chk_work_start_time;
1592 --
1593 --
1594 -- ---------------------------------------------------------------------------
1595 -- |-------------------------< chk_date_of_death >---------------------------|
1596 -- ---------------------------------------------------------------------------
1597 --
1598 -- Desciption :
1599 --
1600 -- Validate that date_of_death does not pre-date incident_date.
1601 --
1602 -- Pre-conditions :
1603 --
1604 --
1605 -- In Arguments :
1606 -- p_incident_date
1607 -- p_date_of_death
1608 --
1609 -- Post Success :
1610 -- Processing continues
1611 --
1612 -- Post Failure :
1613 -- An application error will be raised and processing is
1614 -- terminated
1615 --
1616 -- Access Status :
1617 -- Externalised for public access.
1618 -- {End of Comments}
1619 --
1620 -- ---------------------------------------------------------------------------
1621 procedure chk_date_of_death
1622 (p_incident_date in per_work_incidents.incident_date%TYPE
1623 ,p_date_of_death in per_work_incidents.date_of_death%TYPE
1624 ) is
1625 --
1626 l_proc varchar2(72) := g_package||'chk_date_of_death';
1627 l_dummy varchar2(1);
1628 l_api_updating boolean;
1629 --
1630 begin
1631 --
1632 hr_utility.set_location('Entering:'||l_proc, 1);
1633 --
1634 -- date_of_death cannot pre-date incident_date.
1635 --
1636 if nvl(p_date_of_death, p_incident_date) < p_incident_date then
1637 hr_utility.set_message(800, 'HR_289809_INC_INV_DEATH_DATE');
1638 hr_utility.raise_error;
1639 end if;
1640 --
1641 hr_utility.set_location(' Leaving:'||l_proc, 5);
1642 --
1643 end chk_date_of_death;
1644 --
1645 --
1646 -- ---------------------------------------------------------------------------
1647 -- |-----------------------< chk_hospitalized_flag >------------------------|
1648 -- ---------------------------------------------------------------------------
1649 --
1650 -- Description:
1651 -- Validates that HOSPITALIZED_FLAG is NULL or 'Y' or 'N'.
1652 --
1653 -- Pre-conditions:
1654 --
1655 -- In Arguments:
1656 -- p_hospitalized_flag
1657 --
1658 -- Post Success:
1659 -- Processing continues.
1660 --
1661 -- Post Failure:
1662 -- An application error will be raised and processing is terminated.
1663 --
1664 -- Access Status:
1665 -- Internal Table Handler Use Only.
1666 --
1667 -- {End Of Comments}
1668 -- ----------------------------------------------------------------------------
1669 procedure chk_hospitalized_flag
1670 (p_hospitalized_flag in per_work_incidents.hospitalized_flag%TYPE) is
1671 --
1672 l_proc varchar2(72) := g_package||'chk_hospitalized_flag';
1673 --
1674 begin
1675 hr_utility.set_location('Entering:'|| l_proc, 1);
1676 --
1677 if p_hospitalized_flag is not null then
1678 if p_hospitalized_flag not in ('Y','N') then
1679 hr_utility.set_message(800, 'HR_289805_INC_INV_HOSP_FLAG');
1680 hr_utility.raise_error;
1681 end if;
1682 end if;
1683 --
1684 hr_utility.set_location('Leaving:'|| l_proc, 5);
1685 --
1686 end chk_hospitalized_flag;
1687 --
1688 --
1689 -- ---------------------------------------------------------------------------
1693 -- Description:
1690 -- |-------------------------< chk_privacy_issue >--------------------------|
1691 -- ---------------------------------------------------------------------------
1692 --
1694 -- Validates that PRIVACY_ISSUE is NULL or 'Y' or 'N'.
1695 --
1696 -- Pre-conditions:
1697 --
1698 -- In Arguments:
1699 -- p_privacy_issue
1700 --
1701 -- Post Success:
1702 -- Processing continues.
1703 --
1704 -- Post Failure:
1705 -- An application error will be raised and processing is terminated.
1706 --
1707 -- Access Status:
1708 -- Internal Table Handler Use Only.
1709 --
1710 -- {End Of Comments}
1711 -- ----------------------------------------------------------------------------
1712 procedure chk_privacy_issue
1713 (p_privacy_issue in per_work_incidents.privacy_issue%TYPE) is
1714 --
1715 l_proc varchar2(72) := g_package||'chk_privacy_issue';
1716 --
1717 begin
1718 hr_utility.set_location('Entering:'|| l_proc, 1);
1719 --
1720 if p_privacy_issue is not null then
1721 if p_privacy_issue not in ('Y','N') then
1722 hr_utility.set_message(800, 'HR_289806_INC_INV_PRIV_ISSUE');
1723 hr_utility.raise_error;
1724 end if;
1725 end if;
1726 --
1727 hr_utility.set_location('Leaving:'|| l_proc, 5);
1728 --
1729 end chk_privacy_issue;
1730 --
1731 --
1732 -- ---------------------------------------------------------------------------
1733 -- |-------------------------< chk_emergency_code >-------------------------|
1734 -- ---------------------------------------------------------------------------
1735 --
1736 -- Description:
1737 -- Validates that EMERGENCY_CODE is NULL or 'Y' or 'N'.
1738 --
1739 -- Pre-conditions:
1740 --
1741 -- In Arguments:
1742 -- p_emergency_code
1743 --
1744 -- Post Success:
1745 -- Processing continues.
1746 --
1747 -- Post Failure:
1748 -- An application error will be raised and processing is terminated.
1749 --
1750 -- Access Status:
1751 -- Internal Table Handler Use Only.
1752 --
1753 -- {End Of Comments}
1754 -- ----------------------------------------------------------------------------
1755 procedure chk_emergency_code
1756 (p_emergency_code in per_work_incidents.emergency_code%TYPE) is
1757 --
1758 l_proc varchar2(72) := g_package||'chk_emergency_code';
1759 --
1760 begin
1761 hr_utility.set_location('Entering:'|| l_proc, 1);
1762 --
1763 if p_emergency_code is not null then
1764 if p_emergency_code not in ('Y','N') then
1765 hr_utility.set_message(800, 'HR_289807_INC_INV_EMERG_CODE');
1766 hr_utility.raise_error;
1767 end if;
1768 end if;
1769 --
1770 hr_utility.set_location('Leaving:'|| l_proc, 5);
1771 --
1772 end chk_emergency_code;
1773 --
1774 --
1775 -- ---------------------------------------------------------------------------
1776 -- |--------------------< chk_person_reported_date_time >--------------------|
1777 -- ---------------------------------------------------------------------------
1778 --
1779 -- Desciption :
1780 --
1781 -- Validate that report_date does not pre-date incident_date and that
1782 -- person_reported_by exists in per_all_people_f on the report_date.
1783 -- Error if incident date and report date are the same
1784 -- and report time is earlier than incident time.
1785 --
1786 -- Pre-conditions :
1787 --
1788 --
1789 -- In Arguments :
1790 -- p_incident_id
1791 -- p_incident_date
1792 -- p_incident_time
1793 -- p_person_reported_by
1794 -- p_report_date
1795 -- p_report_time
1796 -- p_business_group_id
1797 -- p_object_version_number
1798 --
1799 -- Post Success :
1800 -- Processing continues
1801 --
1802 -- Post Failure :
1803 -- An application error will be raised and processing is
1804 -- terminated
1805 --
1806 -- Access Status :
1807 -- Externalised for public access.
1808 -- {End of Comments}
1809 --
1810 -- ---------------------------------------------------------------------------
1811 procedure chk_person_reported_date_time
1812 (p_incident_id in per_work_incidents.incident_id%TYPE
1813 ,p_incident_date in per_work_incidents.incident_date%TYPE
1814 ,p_incident_time in per_work_incidents.incident_time%TYPE
1815 ,p_person_reported_by in per_work_incidents.person_reported_by%TYPE
1816 ,p_report_date in per_work_incidents.report_date%TYPE
1817 ,p_report_time in per_work_incidents.report_time%TYPE
1818 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
1819 ,p_object_version_number in per_work_incidents.object_version_number%TYPE
1820 ) is
1821 --
1822 cursor c_person is
1823 select null
1824 from per_all_people_f paf
1825 where paf.person_id = p_person_reported_by
1826 and paf.business_group_id = p_business_group_id
1827 and nvl(p_report_date, paf.effective_start_date)
1828 between paf.effective_start_date and paf.effective_end_date;
1829
1830 --
1831 l_proc varchar2(72) := g_package||'chk_person_reported_date_time';
1832 l_dummy varchar2(1);
1833 l_api_updating boolean;
1834 --
1838 --
1835 begin
1836 --
1837 hr_utility.set_location('Entering:'||l_proc, 1);
1839 -- report_date cannot pre-date incident_date.
1840 --
1841 if nvl(p_report_date, p_incident_date) < p_incident_date then
1842 hr_utility.set_message(800, 'HR_289030_INC_INV_RDATE');
1843 hr_utility.raise_error;
1844 end if;
1845 --
1846 -- Error if incident date and report date are the same
1847 -- and report time is earlier than incident time.
1848 --
1849 if p_report_date = p_incident_date then
1850 if p_report_time is not null and p_incident_time is not null then
1851 if to_date(p_report_time,'HH24:MI') < to_date(p_incident_time,'HH24:MI') then
1852 hr_utility.set_message(800, 'HR_289032_INC_INV_IRTIME');
1853 hr_utility.raise_error;
1854 end if;
1855 end if;
1856 end if;
1857 --
1858 --
1859 hr_utility.set_location(l_proc, 3);
1860 --
1861 l_api_updating := per_inc_shd.api_updating
1862 (p_incident_id => p_incident_id
1863 ,p_object_version_number => p_object_version_number);
1864 --
1865 if p_person_reported_by is not null then
1866 if ((l_api_updating and
1867 (nvl(per_inc_shd.g_old_rec.person_reported_by, hr_api.g_number)
1868 <> nvl(p_person_reported_by, hr_api.g_number)
1869 or nvl(per_inc_shd.g_old_rec.report_date, hr_api.g_date)
1870 <> nvl(p_report_date, hr_api.g_date)))
1871 or (NOT l_api_updating))
1872 then
1873 --
1874 open c_person;
1875 fetch c_person into l_dummy;
1876 if c_person%notfound then
1877 close c_person;
1878 -- Person reported by does not exist on the report date
1879 hr_utility.set_message(800, 'HR_289029_INC_INV_PREPBY');
1880 hr_utility.raise_error;
1881 end if;
1882 close c_person;
1883 end if;
1884 end if;
1885 --
1886 hr_utility.set_location(' Leaving:'||l_proc, 5);
1887 --
1888 end chk_person_reported_date_time;
1889 --
1890 --
1891 -- ---------------------------------------------------------------------------
1892 -- |--------------------------< chk_report_method >-------------------------|
1893 -- ---------------------------------------------------------------------------
1894 --
1895 -- Description:
1896 -- Validates that REPORT_METHOD value exists in hr_lookups for the
1897 -- lookup_type of 'PER_CM_MTHD'.
1898 --
1899 -- Pre-conditions:
1900 --
1901 -- In Arguments:
1902 -- p_incident_id
1903 -- p_report_method
1904 -- p_effective_date
1905 --
1906 -- Post Success:
1907 -- Processing continues.
1908 --
1909 -- Post Failure:
1910 -- An application error will be raised and processing is terminated.
1911 --
1912 -- Access Status:
1913 -- Internal Table Handler Use Only.
1914 --
1915 -- {End Of Comments}
1916 -- ----------------------------------------------------------------------------
1917 procedure chk_report_method
1918 (p_incident_id in per_work_incidents.incident_id%TYPE,
1919 p_report_method in per_work_incidents.report_method%TYPE,
1920 p_effective_date in date) is
1921 --
1922 l_proc varchar2(72) := g_package||'chk_report_method';
1923 --
1924 begin
1925 hr_utility.set_location('Entering:'|| l_proc, 1);
1926 --
1927 -- Check mandatory parameters have been set
1928 --
1929 hr_api.mandatory_arg_error
1930 (p_api_name => l_proc
1931 ,p_argument => 'effective_date'
1932 ,p_argument_value => p_effective_date
1933 );
1934 --
1935 if p_report_method is not null then
1936 --
1937 hr_utility.set_location(l_proc, 5);
1938 --
1939 -- Only proceed with validation if :
1940 -- a) Inserting or
1941 -- b) The value for report_method has changed
1942 --
1943 if ((p_incident_id is null) or
1944 ((p_incident_id is not null) and
1945 (per_inc_shd.g_old_rec.report_method <> p_report_method))) then
1946 --
1947 if hr_api.not_exists_in_hr_lookups
1948 (p_effective_date => p_effective_date
1949 ,p_lookup_type => 'PER_CM_MTHD'
1950 ,p_lookup_code => p_report_method
1951 )
1952 then
1953 --
1954 hr_utility.set_message(800, 'HR_52391_PDM_INV_DLVRY_METHOD');
1955 hr_utility.raise_error;
1956 --
1957 end if;
1958 end if;
1959 end if;
1960 --
1961 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1962 --
1963 end chk_report_method;
1964 --
1965 -- ---------------------------------------------------------------------------
1966 -- |--------------------< chk_notified_hsrep_and_date >--------------------|
1967 -- ---------------------------------------------------------------------------
1968 --
1969 -- Desciption :
1970 --
1971 -- Validate that NOTIFIED_HSREP_DATE does not pre-date INCIDENT_DATE and that
1972 -- NOTIFIED_HSREP_ID exists in PER_ALL_PEOPLE_F.PERSON_ID on the NOTIFIED_HSREP_DATE
1973 -- and business group of the person with the incident.
1974 -- Ensure the NOTIFIED_HSREP_DATE is null if NOTIFIED_HSREP_ID is null.
1975 --
1976 -- Pre-conditions :
1977 --
1978 --
1982 -- p_notified_hsrep_id
1979 -- In Arguments :
1980 -- p_incident_id
1981 -- p_incident_date
1983 -- p_notified_hsrep_date
1984 --
1985 -- Post Success :
1986 -- Processing continues
1987 --
1988 -- Post Failure :
1989 -- An application error will be raised and processing is
1990 -- terminated
1991 --
1992 -- Access Status :
1993 -- Externalised for public access.
1994 -- {End of Comments}
1995 --
1996 -- ---------------------------------------------------------------------------
1997 procedure chk_notified_hsrep_and_date
1998 (p_incident_id in per_work_incidents.incident_id%TYPE
1999 ,p_incident_date in per_work_incidents.incident_date%TYPE
2000 ,p_notified_hsrep_id in per_work_incidents.notified_hsrep_id%TYPE
2001 ,p_notified_hsrep_date in per_work_incidents.notified_hsrep_date%TYPE
2002 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
2003 ,p_object_version_number in per_work_incidents.object_version_number%TYPE
2004 ) is
2005 --
2006 cursor c_person is
2007 select null
2008 from per_all_people_f paf
2009 where paf.person_id = p_notified_hsrep_id
2010 and paf.business_group_id = p_business_group_id
2011 and nvl(p_notified_hsrep_date, paf.effective_start_date)
2012 between paf.effective_start_date and paf.effective_end_date;
2013
2014 --
2015 l_proc varchar2(72) := g_package||'chk_notified_hsrep_and_date';
2016 l_dummy varchar2(1);
2017 l_api_updating boolean;
2018 --
2019 begin
2020 --
2021 hr_utility.set_location('Entering:'||l_proc, 1);
2022 --
2023 --
2024 hr_api.mandatory_arg_error
2025 (p_api_name => l_proc
2026 ,p_argument => 'incident_date'
2027 ,p_argument_value => p_incident_date
2028 );
2029 --
2030 -- hsrep_date cannot pre-date incident_date.
2031 --
2032 if nvl(p_notified_hsrep_date, p_incident_date) < p_incident_date then
2033 hr_utility.set_message(800, 'HR_289033_INC_INV_HSDATE');
2034 hr_utility.raise_error;
2035 end if;
2036 --
2037 l_api_updating := per_inc_shd.api_updating
2038 (p_incident_id => p_incident_id
2039 ,p_object_version_number => p_object_version_number);
2040 --
2041 hr_utility.set_location(l_proc, 5);
2042
2043 if p_notified_hsrep_id is not null then
2044 --
2045 if ((l_api_updating and
2046 (nvl(per_inc_shd.g_old_rec.notified_hsrep_id, hr_api.g_number)
2047 <> nvl(p_notified_hsrep_id, hr_api.g_number)
2048 or nvl(per_inc_shd.g_old_rec.notified_hsrep_date, hr_api.g_date)
2049 <> nvl(p_notified_hsrep_date, hr_api.g_date)))
2050 or (NOT l_api_updating))
2051 then
2052 open c_person;
2053 fetch c_person into l_dummy;
2054 if c_person%notfound then
2055 close c_person;
2056 -- hsrep by does not exist in papf on the report date
2057 hr_utility.set_message(800, 'HR_289034_INC_INV_HSREP');
2058 hr_utility.raise_error;
2059 end if;
2060 close c_person;
2061 end if;
2062 elsif p_notified_hsrep_date is not null then
2063 hr_utility.set_message(800, 'HR_289035_INC_HSDATE_NULL');
2064 hr_utility.raise_error;
2065 end if;
2066 --
2067 hr_utility.set_location(' Leaving:'||l_proc, 5);
2068 --
2069 end chk_notified_hsrep_and_date;
2070 --
2071 --
2072 -- ---------------------------------------------------------------------------
2073 -- |-----------------------< chk_notified_rep_org_date >---------------------|
2074 -- ---------------------------------------------------------------------------
2075 --
2076 -- Description :
2077 -- Validate that NOTIFIED_REP_ORG_ID is valid external organization for the
2078 -- org_class 'REPBODY' and business group on the NOTIFIED_HSREP_DATE.
2079 --
2080 -- Validate that NOTIFIED_HSREP_ID exists in PER_ALL_PEOPLE_F.PERSON_ID for
2081 -- the business_group on the NOTIFIED_HSREP_DATE. The NOTIFIED_HSREP_ID
2082 -- should have the role of representative body, for the NOTIFIED_REP_ORG_ID
2083 -- representative body on the NOTIFIED_HSREP_DATE.
2084 --
2085 -- Validate that NOTIFIED_REP_DATE does not pre-date INCIDENT_DATE, that
2086 -- NOTIFIED_REP_DATE is null if NOTIFIED_REP_ID is null and that
2087 -- NOTIFIED_REP_ID is null if NOTIFIED_REP_ORG_ID is null.
2088 --
2089 -- Pre-conditions :
2090 --
2091 --
2092 -- In Arguments :
2093 -- p_incident_id
2094 -- p_incident_date
2095 -- p_notified_rep_org_id
2096 -- p_notified_rep_id
2097 -- p_notified_rep_date
2098 --
2099 -- Post Success :
2100 -- Processing continues
2101 --
2102 -- Post Failure :
2103 -- An application error will be raised and processing is
2104 -- terminated
2105 --
2106 -- Access Status :
2107 -- Internal Table Handler Use only.
2108 -- {End of Comments}
2109 --
2110 -- ---------------------------------------------------------------------------
2111 procedure chk_notified_rep_org_date
2112 (p_incident_id in per_work_incidents.incident_id%TYPE
2113 ,p_incident_date in per_work_incidents.incident_date%TYPE
2114 ,p_notified_rep_org_id in per_work_incidents.notified_rep_org_id%TYPE
2118 ,p_object_version_number in per_work_incidents.object_version_number%TYPE
2115 ,p_notified_rep_id in per_work_incidents.notified_rep_id%TYPE
2116 ,p_notified_rep_date in per_work_incidents.notified_rep_date%TYPE
2117 ,p_business_group_id in per_all_people_f.business_group_id%TYPE
2119 ) is
2120 --
2121 -- get valid organizations with class of rep body.
2122 --
2123 cursor c_org is
2124 select null
2125 from hr_all_organization_units hou
2126 where hou.organization_id = p_notified_rep_org_id
2127 and hou.internal_external_flag = 'EXT'
2128 and hou.business_group_id = p_business_group_id
2129 and nvl(p_notified_rep_date, hou.date_from) between hou.date_from
2130 and nvl(hou.date_to,hr_general.end_of_time)
2131 and p_notified_rep_org_id in (select hoi.organization_id
2132 from hr_organization_information hoi
2133 where hoi.org_information_context = 'CLASS'
2134 and hoi.org_information1 = 'REPBODY'
2135 and hoi.org_information2 = 'Y');
2136 --
2137 -- get person with roles of rep_body representative
2138 -- on the report date.
2139 --
2140 cursor c_person is
2141 select null
2142 from per_all_people_f paf, per_roles rol
2143 where paf.person_id = p_notified_rep_id
2144 and paf.business_group_id = p_business_group_id
2145 and paf.person_id = rol.person_id
2146 and rol.organization_id = p_notified_rep_org_id
2147 and nvl(p_notified_rep_date, paf.effective_start_date)
2148 between paf.effective_start_date and paf.effective_end_date
2149 and nvl(p_notified_rep_date, rol.start_date)
2150 between rol.start_date and nvl(rol.end_date,hr_general.end_of_time);
2151 --
2152 --
2153 l_proc varchar2(72) := g_package||'chk_notified_rep_org_date';
2154 l_dummy varchar2(1);
2155 l_api_updating boolean;
2156 --
2157 begin
2158 --
2159 hr_utility.set_location('Entering:'||l_proc, 1);
2160 --
2161 hr_api.mandatory_arg_error
2162 (p_api_name => l_proc
2163 ,p_argument => 'incident_date'
2164 ,p_argument_value => p_incident_date
2165 );
2166 --
2167 if p_notified_rep_date is not null then
2168 --
2169 -- notified_rep_date cannot pre-date incident_date.
2170 --
2171 if p_notified_rep_date < p_incident_date then
2172 hr_utility.set_message(800, 'HR_289036_INC_INV_NRBDATE');
2173 hr_utility.raise_error;
2174 end if;
2175 --
2176 -- notified_rep_date must be null if notified_hsrep_id is null.
2177 --
2178 if p_notified_rep_id is null then
2179 hr_utility.set_message(800, 'HR_289037_INC_NRBDATE_NULL');
2180 hr_utility.raise_error;
2181 end if;
2182 end if;
2183 --
2184 if p_notified_rep_id is not null then
2185 --
2186 -- notified_rep_id must be null if notified_rep_org_id is null.
2187 --
2188 if p_notified_rep_org_id is null then
2189 hr_utility.set_message(800, 'HR_289038_INC_NREP_NULL');
2190 hr_utility.raise_error;
2191 end if;
2192 end if;
2193 --
2194 l_api_updating := per_inc_shd.api_updating
2195 (p_incident_id => p_incident_id
2196 ,p_object_version_number => p_object_version_number);
2197 --
2198 hr_utility.set_location(l_proc, 5);
2199 --
2200 -- Validate the rep_body org supplied.
2201 --
2202 if p_notified_rep_org_id is not null then
2203 if ((l_api_updating and
2204 nvl(per_inc_shd.g_old_rec.notified_rep_org_id, hr_api.g_number)
2205 <> nvl(p_notified_rep_org_id, hr_api.g_number))
2206 or (NOT l_api_updating))
2207 then
2208 open c_org;
2209 fetch c_org into l_dummy;
2210 if c_org%notfound then
2211 close c_org;
2212 -- repbody org does not exist, so error.
2213 hr_utility.set_message(800, 'HR_289039_INC_INV_REPBODY');
2214 hr_utility.raise_error;
2215 end if;
2216 close c_org;
2217 end if;
2218 end if;
2219 --
2220 hr_utility.set_location(l_proc, 15);
2221 --
2222 -- Validate the representative exists for the rep body.
2223 --
2224 if p_notified_rep_id is not null then
2225 if ((l_api_updating and
2226 nvl(per_inc_shd.g_old_rec.notified_rep_id, hr_api.g_number)
2227 <> nvl(p_notified_rep_id, hr_api.g_number))
2228 or (NOT l_api_updating))
2229 then
2230 open c_person;
2231 fetch c_person into l_dummy;
2232 if c_person%notfound then
2233 close c_person;
2234 -- repbody org does not exist, so error.
2235 hr_utility.set_message(800, 'HR_289040_INC_INV_REP');
2236 hr_utility.raise_error;
2237 end if;
2238 close c_person;
2239 end if;
2240 end if;
2241 --
2242 --
2243 hr_utility.set_location(l_proc,25);
2244 --
2245 end chk_notified_rep_org_date;
2246 --
2247 --
2248 -- ---------------------------------------------------------------------------
2249 -- |-------------------< chk_hospital_doctor_details >------------------------|
2250 -- ---------------------------------------------------------------------------
2251 --
2252 -- Description :
2253 --
2257 -- Pre-conditions :
2254 -- Validate that HOSPITAL_DETAILS and DOCTOR_NAME are null if
2255 -- treatment_received_flag is null.
2256 --
2258 --
2259 --
2260 -- In Arguments :
2261 -- p_treatment_received_flag
2262 -- p_hospital_details
2263 -- p_doctor_name
2264 --
2265 -- Post Success :
2266 -- Processing continues
2267 --
2268 -- Post Failure :
2269 -- An application error will be raised and processing is
2270 -- terminated
2271 --
2272 -- Access Status :
2273 -- Internal Table Handler Use only.
2274 --
2275 -- {End of Comments}
2276 --
2277 -- ---------------------------------------------------------------------------
2278 procedure chk_hospital_doctor_details
2279 (p_treatment_received_flag in per_work_incidents.treatment_received_flag%TYPE
2280 ,p_hospital_details in per_work_incidents.hospital_details%TYPE
2281 ,p_doctor_name in per_work_incidents.doctor_name%TYPE
2282 ) is
2283 --
2284 l_proc varchar2(72) := g_package||'chk_hospital_doctor_details';
2285 --
2286 begin
2287 hr_utility.set_location('Entering:'||l_proc, 1);
2288 --
2289 if p_treatment_received_flag is not null and p_treatment_received_flag not in ('Y','N') then
2290 hr_utility.set_message(800, 'HR_289027_INC_TREATMENT_INV');
2291 hr_utility.raise_error;
2292 end if;
2293 --
2294 -- Following IF (..) <> 'US' condition is added for OSHA localization.
2295 --
2296 IF (hr_api.get_legislation_context) <> 'US' then
2297 if p_treatment_received_flag <> 'Y' then
2298 if p_hospital_details is not null then
2299 hr_utility.set_message(800, 'HR_289025_INC_HOSP_NULL');
2300 hr_utility.raise_error;
2301 elsif p_doctor_name is not null then
2302 hr_utility.set_message(800, 'HR_289026_INC_DOC_NULL');
2303 hr_utility.raise_error;
2304 end if;
2305 end if;
2306 END IF;
2307 --
2308 hr_utility.set_location(' Leaving:'||l_proc, 5);
2309 --
2310 end chk_hospital_doctor_details;
2311 --
2312 --
2313 -- ---------------------------------------------------------------------------
2314 -- |----------------------------< chk_report_time >--------------------------|
2315 -- ---------------------------------------------------------------------------
2316 --
2317 -- Desciption :
2318 --
2319 -- Validate that REPORT_TIME is valid time in 24HH:MI.
2320 --
2321 -- Pre-conditions :
2322 --
2323 --
2324 -- In Arguments :
2325 -- p_incident_report
2326 --
2327 -- Post Success :
2328 -- Processing continues
2329 --
2330 -- Post Failure :
2331 -- An application error will be raised and processing is
2332 -- terminated
2333 --
2334 -- Access Status :
2335 -- Internal Table Handler Use only.
2336 --
2337 -- {End of Comments}
2338 --
2339 -- ---------------------------------------------------------------------------
2340 procedure chk_report_time
2341 (p_report_time in per_work_incidents.report_time%TYPE
2342 ) is
2343 --
2344 l_proc varchar2(72) := g_package||'chk_report_time';
2345 l_value varchar2(60) := p_report_time;
2346 l_output varchar2(60) := p_report_time;
2347 l_rgeflg varchar2(1);
2348 --
2349 begin
2350 hr_utility.set_location('Entering:'||l_proc, 1);
2351 --
2352 -- Check report_time is in HH24:MI format
2353 --
2354 if p_report_time is not null then
2355 begin
2356
2357 hr_chkfmt.checkformat(l_value, 'TIMES', l_output, NULL, NULL, 'Y', l_rgeflg, NULL);
2358
2359 if p_report_time <> l_output then
2360 hr_utility.set_message(800, 'HR_289031_INC_INV_RTIME');
2361 hr_utility.raise_error;
2362 end if;
2363
2364 exception
2365 when others then
2366 hr_utility.set_message(801,'HR_7916_CHECK_FMT_HHMM');
2367 hr_utility.set_message_token('ARG_NAME', 'report_time');
2368 hr_utility.set_message_token('ARG_VALUE', p_report_time);
2369 hr_utility.raise_error;
2370 end;
2371 end if;
2372 --
2373 hr_utility.set_location(' Leaving:'||l_proc, 5);
2374 --
2375 end chk_report_time;
2376 --
2377 -- ---------------------------------------------------------------------------
2378 -- |-------------------------< chk_assignment_id >----------------------------|
2379 -- ---------------------------------------------------------------------------
2380 --
2381 -- Description :
2382 --
2383 -- Validate that ASSIGNMENT_ID exsts for the person on the incident_date
2384 --
2385 --
2386 -- Pre-conditions :
2387 --
2388 --
2389 -- In Arguments :
2390 -- p_incident_id
2391 -- p_assignment_id
2392 -- p_person_id
2393 -- p_incident_date
2394 --
2395 -- Post Success :
2396 -- Processing continues
2397 --
2398 -- Post Failure :
2399 -- An application error will be raised and processing is
2400 -- terminated
2401 --
2402 -- Access Status :
2403 -- Internal Table Handler Use only.
2404 --
2405 -- {End of Comments}
2406 --
2407 -- ---------------------------------------------------------------------------
2408 procedure chk_assignment_id
2412 p_incident_date in per_work_incidents.incident_date%TYPE
2409 (p_incident_id in per_work_incidents.incident_id%TYPE,
2410 p_assignment_id in per_work_incidents.assignment_id%TYPE,
2411 p_person_id in per_work_incidents.person_id%TYPE,
2413 ) is
2414 --
2415 l_proc varchar2(72) := g_package||'chk_assignment_id';
2416 l_dummy number;
2417 --
2418 cursor csr_asg is
2419 select null
2420 from per_all_assignments_f asg
2421 where asg.assignment_id = p_assignment_id
2422 and asg.person_id = p_person_id
2423 and p_incident_date between asg.effective_start_date
2424 and asg.effective_end_date;
2425 --
2426 begin
2427 hr_utility.set_location('Entering:'||l_proc, 1);
2428 --
2429 hr_api.mandatory_arg_error
2430 (p_api_name => l_proc
2431 ,p_argument => 'person_id'
2432 ,p_argument_value => p_person_id
2433 );
2434 --
2435 hr_api.mandatory_arg_error
2436 (p_api_name => l_proc
2437 ,p_argument => 'incident_date'
2438 ,p_argument_value => p_incident_date
2439 );
2440 --
2441 if p_assignment_id is not null then
2442 --
2443 hr_utility.set_location(' Leaving:'||l_proc, 15);
2444 --
2445 if ((p_incident_id is null) or
2446 ((p_incident_id is not null) and
2447 ((per_inc_shd.g_old_rec.assignment_id <> p_assignment_id)
2448 or (per_inc_shd.g_old_rec.incident_date <> p_incident_date)))) then
2449 --
2450 -- Check that the assignment exists
2451 -- for the eprson on the incident_date.
2452 --
2453 open csr_asg;
2454 fetch csr_asg into l_dummy;
2455 if csr_asg%notfound then
2456 close csr_asg;
2457 hr_utility.set_message(800, 'HR_289041_INC_ASG_FK_NOT_FOUND');
2458 hr_utility.raise_error;
2459 end if;
2460 close csr_asg;
2461 end if;
2462 end if;
2463 --
2464 hr_utility.set_location(' Leaving:'||l_proc, 15);
2465 --
2466 end chk_assignment_id;
2467 --
2468 -- ---------------------------------------------------------------------------
2469 -- |----------------------< chk_compensation_details >-----------------------|
2470 -- ---------------------------------------------------------------------------
2471 --
2472 -- Description :
2473 --
2474 -- Validate that ASSIGNMENT_ID exsts for the person on the incident_date
2475 --
2476 --
2477 -- Pre-conditions :
2478 --
2479 --
2480 -- In Arguments :
2481 -- p_incident_id
2482 -- p_assignment_id
2483 -- p_person_id
2484 -- p_incident_date
2485 --
2486 -- Post Success :
2487 -- Processing continues
2488 --
2489 -- Post Failure :
2490 -- An application error will be raised and processing is
2491 -- terminated
2492 --
2493 -- Access Status :
2494 -- Internal Table Handler Use only.
2495 --
2496 -- {End of Comments}
2497 --
2498 -- ---------------------------------------------------------------------------
2499 procedure chk_compensation_details
2500 (p_incident_id in per_work_incidents.incident_id%TYPE,
2501 p_compensation_currency in per_work_incidents.compensation_currency%TYPE,
2502 p_compensation_amount in per_work_incidents.compensation_amount%TYPE,
2503 p_compensation_date in per_work_incidents.compensation_date%TYPE,
2504 p_incident_date in per_work_incidents.incident_date%TYPE
2505 ) is
2506 --
2507 l_proc varchar2(72) := g_package||'chk_compensation_details';
2508 l_dummy number;
2509 l_output varchar2(60);
2510 l_value varchar2(60);
2511 l_reg varchar2(60);
2512 --
2513 --
2514 begin
2515 hr_utility.set_location('Entering:'||l_proc, 1);
2516 --
2517 -- ensure both currency and amount are not null
2518 -- if either is not null.
2519 if (p_compensation_currency is null and p_compensation_amount is not null)
2520 or (p_compensation_currency is not null and p_compensation_amount is null) then
2521 hr_utility.set_message(800, 'HR_289043_INC_CURR_NULL');
2522 hr_utility.raise_error;
2523 end if;
2524 --
2525 -- compensation_date cannot pre-date incident_date.
2526 --
2527 if p_compensation_date < p_incident_date then
2528 hr_utility.set_message(800, 'HR_289045_INC_INV_CDATE');
2529 hr_utility.raise_error;
2530 end if;
2531 --
2532 hr_utility.set_location(l_proc, 10);
2533 --
2534 -- check currency_code and currency_amount are is valid.
2535 --
2536 if ((p_incident_id is null) or
2537 ((p_incident_id is not null) and
2538 ((per_inc_shd.g_old_rec.compensation_currency <> p_compensation_currency)
2539 or (per_inc_shd.g_old_rec.compensation_amount <> p_compensation_amount)))) then
2540 --
2541 begin
2542 --
2543 l_value := to_char(p_compensation_amount);
2544 l_output := to_char(p_compensation_amount);
2545 --
2546 hr_chkfmt.checkformat(l_value, 'MONEY', l_output, NULL, NULL, 'Y', l_reg, p_compensation_currency);
2547
2548 exception
2549 when others then
2550 hr_utility.set_message(801,'HR_7912_CHECK_FMT_MONEY');
2551 hr_utility.set_message_token('ARG_NAME', 'compensation amount');
2555 end if;
2552 hr_utility.set_message_token('ARG_VALUE', l_value);
2553 hr_utility.raise_error;
2554 end;
2556 --
2557 hr_utility.set_location(' Leaving:'||l_proc, 15);
2558 --
2559 end chk_compensation_details;
2560 --
2561 --
2562 -- ---------------------------------------------------------------------------
2563 -- |-------------------< chk_absence_exists >------------------------|
2564 -- ---------------------------------------------------------------------------
2565 --
2566 -- Description :
2567 --
2568 -- Validate that HOSPITAL_DETAILS and DOCTOR_NAME are null if
2569 -- treatment_received_flag is null.
2570 --
2571 -- Pre-conditions :
2572 --
2573 --
2574 -- In Arguments :
2575 -- p_treatment_received_flag
2576 -- p_hospital_details
2577 -- p_doctor_name
2578 --
2579 -- Post Success :
2580 -- Processing continues
2581 --
2582 -- Post Failure :
2583 -- An application error will be raised and processing is
2584 -- terminated
2585 --
2586 -- Access Status :
2587 -- Internal Table Handler Use only.
2588 --
2589 -- {End of Comments}
2590 --
2591 -- ---------------------------------------------------------------------------
2592 procedure chk_absence_exists_flag
2593 (p_absence_exists_flag in per_work_incidents.absence_exists_flag%TYPE
2594 ) is
2595 --
2596 l_proc varchar2(72) := g_package||'chk_absence_exists_flag';
2597 --
2598 begin
2599 hr_utility.set_location('Entering:'||l_proc, 1);
2600 --
2601 if p_absence_exists_flag is not null and p_absence_exists_flag not in ('Y','N') then
2602 hr_utility.set_message(800, 'HR_289042_INC_ABS_EXISTS_INV');
2603 hr_utility.raise_error;
2604 end if;
2605 --
2606 hr_utility.set_location(' Leaving:'||l_proc, 5);
2607 --
2608 end chk_absence_exists_flag;
2609 --
2610 --
2611 -- ---------------------------------------------------------------------------
2612 -- |----------------------------< chk_object_version_number >-----------------|
2613 -- ---------------------------------------------------------------------------
2614 --
2615 -- Desciption :
2616 --
2617 -- Checks that the OVN passed is not null on update and delete.
2618 --
2619 -- Pre-conditions :
2620 -- None.
2621 --
2622 -- In Arguments :
2623 -- p_object_version_number
2624 --
2625 -- Post Success :
2626 -- Processing continues
2627 --
2628 -- Post Failure :
2629 -- An application error will be raised and processing is
2630 -- terminated
2631 --
2632 -- Access Status :
2633 -- Internal Table Handler Use only.
2634 --
2635 -- {End of Comments}
2636 --
2637 -- ---------------------------------------------------------------------------
2638 procedure chk_object_version_number
2639 (
2640 p_object_version_number in per_phones.object_version_number%TYPE
2641 ) is
2642 --
2643 l_proc varchar2(72) := g_package||'chk_object_version_number';
2644 --
2645 begin
2646 hr_utility.set_location('Entering:'||l_proc, 1);
2647 --
2648 -- Check mandatory parameters have been set
2649 --
2650 hr_api.mandatory_arg_error
2651 (p_api_name => l_proc
2652 ,p_argument => 'object_version_number'
2653 ,p_argument_value => p_object_version_number
2654 );
2655 --
2656 hr_utility.set_location(' Leaving:'||l_proc, 3);
2657 --
2658 end chk_object_version_number;
2659 --
2660 --
2661 -- ----------------------------------------------------------------------------
2662 -- |---------------------------< insert_validate >----------------------------|
2663 -- ----------------------------------------------------------------------------
2664 Procedure insert_validate
2665 (p_effective_date in date
2666 ,p_rec in per_inc_shd.g_rec_type
2667 ) is
2668 --
2669 cursor csr_bg is
2670 select business_group_id
2671 from per_all_people_f
2672 where person_id = p_rec.person_id;
2673 --
2674 l_proc varchar2(72) := g_package||'insert_validate';
2675 l_bg_id per_all_people_f.business_group_id%TYPE;
2676 --
2677 Begin
2678 hr_utility.set_location('Entering:'||l_proc, 5);
2679 --
2680 -- Call all supporting business operations
2681 --
2682 -- Call parent person table's set_security_group_id function
2683 per_per_bus.set_security_group_id(p_person_id => to_number(p_rec.person_id));
2684 --
2685 -- Validate person id
2686 --
2687 per_inc_bus.chk_person_id
2688 (p_incident_id => p_rec.incident_id,
2689 p_person_id => p_rec.person_id,
2690 p_effective_date => p_effective_date);
2691 --
2692 -- Validate incident date
2693 --
2694 per_inc_bus.chk_incident_date
2695 (p_incident_date => p_rec.incident_date);
2696 --
2697 -- Validate incident time
2698 --
2699 per_inc_bus.chk_incident_time
2700 (p_incident_time => p_rec.incident_time);
2701 --
2702 -- Start of changes for additional columns
2703 --
2704 -- Validate hospitalized flag
2705 --
2706 per_inc_bus.chk_hospitalized_flag
2707 (p_hospitalized_flag => p_rec.hospitalized_flag);
2708
2709 --
2710 -- Validate privacy issue
2711 --
2712 per_inc_bus.chk_privacy_issue
2713 (p_privacy_issue => p_rec.privacy_issue);
2714 --
2718 (p_emergency_code => p_rec.emergency_code);
2715 -- Validate emergency_code
2716 --
2717 per_inc_bus.chk_emergency_code
2719 --
2720 -- Validate date of death
2721 --
2722 per_inc_bus.chk_date_of_death
2723 (p_incident_date => p_rec.incident_date
2724 ,p_date_of_death => p_rec.date_of_death);
2725 --
2726 -- Validate work start time
2727 --
2728 per_inc_bus.chk_work_start_time
2729 (p_work_start_time => p_rec.work_start_time);
2730
2731 --
2732 -- End of changes for additional columns
2733 --
2734 -- Validate incident reference
2735 --
2736 per_inc_bus.chk_incident_reference(p_incident_id => p_rec.incident_id,
2737 p_incident_reference => p_rec.incident_reference,
2738 p_object_version_number => p_rec.object_version_number);
2739 --
2740 -- Validate incident type
2741 --
2742 per_inc_bus.chk_incident_type(p_incident_id => p_rec.incident_id,
2743 p_incident_type => p_rec.incident_type,
2744 p_effective_date => p_effective_date);
2745 --
2746 -- Validate Over Time Flag
2747 --
2748 per_inc_bus.chk_over_time_flag
2749 (p_over_time_flag => p_rec.over_time_flag);
2750
2751 --
2752 -- Validate related incident
2753 --
2754 per_inc_bus.chk_related_incident_id(p_incident_id => p_rec.incident_id,
2755 p_related_incident_id => p_rec.related_incident_id,
2756 p_person_id => p_rec.person_id,
2757 p_incident_date => p_rec.incident_date);
2758 --
2759 -- Validate Org Notified Date
2760 --
2761 per_inc_bus.chk_org_notified_date(p_org_notified_date => p_rec.org_notified_date,
2762 p_incident_date => p_rec.incident_date);
2763 --
2764 -- Validate At Work Flag
2765 --
2766 per_inc_bus.chk_at_work_flag(p_incident_id => p_rec.incident_id,
2767 p_at_work_flag => p_rec.at_work_flag,
2768 p_effective_date => p_effective_date);
2769 --
2770 -- Validate Hazard Type
2771 --
2772 per_inc_bus.chk_hazard_type(p_incident_id => p_rec.incident_id,
2773 p_hazard_type => p_rec.hazard_type,
2774 p_effective_date => p_effective_date);
2775
2776 --
2777 -- Validate Injury Type
2778 --
2779 per_inc_bus.chk_injury_type(p_incident_id => p_rec.incident_id,
2780 p_injury_type => p_rec.injury_type,
2781 p_effective_date => p_effective_date);
2782
2783 --
2784 -- Validate Disease Type
2785 --
2786 per_inc_bus.chk_disease_type(p_incident_id => p_rec.incident_id,
2787 p_disease_type => p_rec.disease_type,
2788 p_effective_date => p_effective_date);
2789
2790 --
2791 -- Validate Hopsital Details and Doctor Name
2792 --
2793 per_inc_bus.chk_hospital_doctor_details
2794 (p_treatment_received_flag => p_rec.treatment_received_flag,
2795 p_hospital_details => p_rec.hospital_details,
2796 p_doctor_name => p_rec.doctor_name);
2797 --
2798 -- Validate report time
2799 --
2800 per_inc_bus.chk_report_time
2801 (p_report_time => p_rec.report_time);
2802 --
2803 -- Validate person reported by, report date and time
2804 --
2805 open csr_bg;
2806 fetch csr_bg into l_bg_id;
2807 close csr_bg;
2808 per_inc_bus.chk_person_reported_date_time
2809 (p_incident_id => p_rec.incident_id
2810 ,p_incident_date => p_rec.incident_date
2811 ,p_incident_time => p_rec.incident_time
2812 ,p_person_reported_by => p_rec.person_reported_by
2813 ,p_report_date => p_rec.report_date
2814 ,p_report_time => p_rec.report_time
2815 ,p_business_group_id => l_bg_id
2816 ,p_object_version_number => p_rec.object_version_number);
2817 --
2818 -- Validate report method
2819 --
2820 per_inc_bus.chk_report_method(p_incident_id => p_rec.incident_id,
2821 p_report_method => p_rec.report_method,
2822 p_effective_date => p_effective_date);
2823 --
2824 -- Validate notified_hsrep_id, notified_hsrep_date
2825 --
2826 per_inc_bus.chk_notified_hsrep_and_date
2827 (p_incident_id => p_rec.incident_id
2828 ,p_incident_date => p_rec.incident_date
2829 ,p_notified_hsrep_id => p_rec.notified_hsrep_id
2830 ,p_notified_hsrep_date => p_rec.notified_hsrep_date
2831 ,p_business_group_id => l_bg_id
2832 ,p_object_version_number => p_rec.object_version_number);
2833 --
2834 -- Validate notified_rep_org, notified_rep_id, notified_rep_date
2835 --
2836 per_inc_bus.chk_notified_rep_org_date
2837 (p_incident_id => p_rec.incident_id
2838 ,p_incident_date => p_rec.incident_date
2839 ,p_notified_rep_org_id => p_rec.notified_rep_org_id
2840 ,p_notified_rep_id => p_rec.notified_rep_id
2841 ,p_notified_rep_date => p_rec.notified_rep_date
2842 ,p_business_group_id => l_bg_id
2843 ,p_object_version_number => p_rec.object_version_number);
2844 --
2845 -- Validate compensation details
2846 --
2847 per_inc_bus.chk_compensation_details
2848 (p_incident_id => p_rec.incident_id,
2849 p_compensation_currency => p_rec.compensation_currency,
2850 p_compensation_amount => p_rec.compensation_amount,
2851 p_compensation_date => p_rec.compensation_date,
2852 p_incident_date => p_rec.incident_date);
2853 --
2854 -- Validate assignment_id
2855 --
2856 per_inc_bus.chk_assignment_id(p_incident_id => p_rec.incident_id
2857 ,p_assignment_id => p_rec.assignment_id
2858 ,p_person_id => p_rec.person_id
2859 ,p_incident_date => p_rec.incident_date);
2860 --
2861 -- Check absence exists flag
2862 --
2863 per_inc_bus.chk_absence_exists_flag
2867 --
2864 (p_absence_exists_flag => p_rec.absence_exists_flag);
2865 --
2866 -- Validate Developer Descriptive Flexfield
2868 per_inc_bus.chk_ddf(p_rec);
2869 --
2870 -- Validate Descriptive Flexfield
2871 --
2872 per_inc_bus.chk_df(p_rec);
2873 --
2874 hr_utility.set_location(' Leaving:'||l_proc, 10);
2875 End insert_validate;
2876 --
2877 -- ----------------------------------------------------------------------------
2878 -- |---------------------------< update_validate >----------------------------|
2879 -- ----------------------------------------------------------------------------
2880 Procedure update_validate
2881 (p_effective_date in date
2882 ,p_rec in per_inc_shd.g_rec_type
2883 ) is
2884 --
2885 cursor csr_bg is
2886 select business_group_id
2887 from per_all_people_f
2888 where person_id = p_rec.person_id;
2889 --
2890 l_bg_id per_all_people_f.business_group_id%TYPE;
2891 l_proc varchar2(72) := g_package||'update_validate';
2892 --
2893 Begin
2894 hr_utility.set_location('Entering:'||l_proc, 5);
2895 --
2896 -- Call all supporting business operations
2897 -- Call parent person table's set_security_group_id function
2898 per_per_bus.set_security_group_id(p_person_id => to_number(p_rec.person_id));
2899 --
2900 chk_non_updateable_args
2901 (p_effective_date => p_effective_date
2902 ,p_rec => p_rec);
2903 --
2904 -- Validate person id
2905 --
2906 per_inc_bus.chk_person_id
2907 (p_incident_id => p_rec.incident_id,
2908 p_person_id => p_rec.person_id,
2909 p_effective_date => p_effective_date);
2910 --
2911 -- Validate incident date
2912 --
2913 per_inc_bus.chk_incident_date
2914 (p_incident_date => p_rec.incident_date);
2915 --
2916 -- Validate incident time
2917 --
2918 per_inc_bus.chk_incident_time
2919 (p_incident_time => p_rec.incident_time);
2920 --
2921 -- Validate incident reference
2922 --
2923 per_inc_bus.chk_incident_reference(p_incident_id => p_rec.incident_id,
2924 p_incident_reference => p_rec.incident_reference,
2925 p_object_version_number => p_rec.object_version_number);
2926 --
2927 -- Validate incident type
2928 --
2929 per_inc_bus.chk_incident_type
2930 (p_incident_id => p_rec.incident_id,
2931 p_incident_type => p_rec.incident_type,
2932 p_effective_date => p_effective_date);
2933
2934 --
2935 -- Validate Over Time Flag
2936 --
2937 per_inc_bus.chk_over_time_flag
2938 (p_over_time_flag => p_rec.over_time_flag);
2939
2940 --
2941 -- Validate related incident
2942 --
2943 per_inc_bus.chk_related_incident_id(p_incident_id => p_rec.incident_id,
2944 p_related_incident_id => p_rec.related_incident_id,
2945 p_person_id => p_rec.person_id,
2946 p_incident_date => p_rec.incident_date);
2947 --
2948 -- Validate Org Notified Date
2949 --
2950 per_inc_bus.chk_org_notified_date(p_org_notified_date => p_rec.org_notified_date,
2951 p_incident_date => p_rec.incident_date);
2952 --
2953 -- Validate At Work flag
2954 --
2955 per_inc_bus.chk_at_work_flag(p_incident_id => p_rec.incident_id,
2956 p_at_work_flag => p_rec.at_work_flag,
2957 p_effective_date => p_effective_date);
2958 --
2959 -- Validate Hazard Type
2960 --
2961 per_inc_bus.chk_hazard_type(p_incident_id => p_rec.incident_id,
2962 p_hazard_type => p_rec.hazard_type,
2963 p_effective_date => p_effective_date);
2964 --
2965 -- Validate Injury Type
2966 --
2967 per_inc_bus.chk_injury_type(p_incident_id => p_rec.incident_id,
2968 p_injury_type => p_rec.injury_type,
2969 p_effective_date => p_effective_date);
2970 --
2971 -- Validate Disease Type
2972 --
2973 per_inc_bus.chk_disease_type(p_incident_id => p_rec.incident_id,
2974 p_disease_type => p_rec.disease_type,
2975 p_effective_date => p_effective_date);
2976 --
2977 -- Validate Hopsital Details and Doctor Name
2978 --
2979 per_inc_bus.chk_hospital_doctor_details
2980 (p_treatment_received_flag => p_rec.treatment_received_flag,
2981 p_hospital_details => p_rec.hospital_details,
2982 p_doctor_name => p_rec.doctor_name);
2983 --
2984 -- Validate report time
2985 --
2986 per_inc_bus.chk_report_time
2987 (p_report_time => p_rec.report_time);
2988 --
2989 -- Validate person reported by, report date and time
2990 --
2991 open csr_bg;
2992 fetch csr_bg into l_bg_id;
2993 close csr_bg;
2994 per_inc_bus.chk_person_reported_date_time
2995 (p_incident_id => p_rec.incident_id
2996 ,p_incident_date => p_rec.incident_date
2997 ,p_incident_time => p_rec.incident_time
2998 ,p_person_reported_by => p_rec.person_reported_by
2999 ,p_report_date => p_rec.report_date
3003 --
3000 ,p_report_time => p_rec.report_time
3001 ,p_business_group_id => l_bg_id
3002 ,p_object_version_number => p_rec.object_version_number);
3004 -- Validate report method
3005 --
3006 per_inc_bus.chk_report_method(p_incident_id => p_rec.incident_id,
3007 p_report_method => p_rec.report_method,
3008 p_effective_date => p_effective_date);
3009 --
3010 -- Validate notified_hsrep_id, notified_hsrep_date
3011 --
3012 per_inc_bus.chk_notified_hsrep_and_date
3013 (p_incident_id => p_rec.incident_id
3014 ,p_incident_date => p_rec.incident_date
3015 ,p_notified_hsrep_id => p_rec.notified_hsrep_id
3016 ,p_notified_hsrep_date => p_rec.notified_hsrep_date
3017 ,p_business_group_id => l_bg_id
3018 ,p_object_version_number => p_rec.object_version_number);
3019 --
3020 --
3021 -- Validate notified_rep_org, notified_rep_id, notified_rep_date
3022 --
3023 per_inc_bus.chk_notified_rep_org_date
3024 (p_incident_id => p_rec.incident_id
3025 ,p_incident_date => p_rec.incident_date
3026 ,p_notified_rep_org_id => p_rec.notified_rep_org_id
3027 ,p_notified_rep_id => p_rec.notified_rep_id
3028 ,p_notified_rep_date => p_rec.notified_rep_date
3029 ,p_business_group_id => l_bg_id
3030 ,p_object_version_number => p_rec.object_version_number);
3031 --
3032 -- Validate compensation details
3033 --
3034 per_inc_bus.chk_compensation_details
3035 (p_incident_id => p_rec.incident_id,
3036 p_compensation_currency => p_rec.compensation_currency,
3037 p_compensation_amount => p_rec.compensation_amount,
3038 p_compensation_date => p_rec.compensation_date,
3039 p_incident_date => p_rec.incident_date);
3040 --
3041 -- Validate assignment_id
3042 --
3043 per_inc_bus.chk_assignment_id(p_incident_id => p_rec.incident_id
3044 ,p_assignment_id => p_rec.assignment_id
3045 ,p_person_id => p_rec.person_id
3046 ,p_incident_date => p_rec.incident_date);
3047 --
3048 -- Check absence exists flag
3049 --
3050 per_inc_bus.chk_absence_exists_flag
3051 (p_absence_exists_flag => p_rec.absence_exists_flag);
3052 --
3053 -- Validate Developer Descriptive Flexfield
3054 --
3055 per_inc_bus.chk_ddf(p_rec);
3056 --
3057 -- Validate Descriptive Flexfield
3058 --
3059 per_inc_bus.chk_df(p_rec);
3060 --
3061 hr_utility.set_location(' Leaving:'||l_proc, 10);
3062 End update_validate;
3063 --
3064 -- ----------------------------------------------------------------------------
3065 -- |---------------------------< delete_validate >----------------------------|
3066 -- ----------------------------------------------------------------------------
3067 Procedure delete_validate
3068 (p_rec in per_inc_shd.g_rec_type
3069 ) is
3070 --
3071 l_proc varchar2(72) := g_package||'delete_validate';
3072 --
3073 Begin
3074 hr_utility.set_location('Entering:'||l_proc, 5);
3075 --
3076 -- Call all supporting business operations
3077 --
3078 -- Validate Object Version Number
3079 --
3080 chk_object_version_number
3081 (p_object_version_number => p_rec.object_version_number);
3082 --
3083 --
3084 hr_utility.set_location(' Leaving:'||l_proc, 10);
3085 End delete_validate;
3086 --
3087 --
3088 --******************************************************************************
3089 --* Returns the reference for the supplied incident_id
3090 --******************************************************************************
3091 function GET_INCIDENT_REFERENCE (p_incident_id in number) return varchar2 is
3092 --
3093 cursor csr_incident is
3094 select incident_reference
3095 from per_work_incidents pwi
3096 where pwi.incident_id = p_incident_id;
3097 --
3098 v_reference per_work_incidents.incident_reference%TYPE := null;
3099 --
3100 begin
3101 --
3102 -- Only open the cursor if the parameters are going to retrieve anything
3103 --
3104 if p_incident_id is not null then
3105 --
3106 open csr_incident;
3107 fetch csr_incident into v_reference;
3108 close csr_incident;
3109 --
3110 end if;
3111 --
3112 return v_reference;
3113 --
3114 end GET_INCIDENT_REFERENCE;
3115 --
3116 --
3117 --
3118 -- ----------------------------------------------------------------------------
3119 -- |-------------------------< create_osha_numbers >--------------------------|
3120 -- ----------------------------------------------------------------------------
3121 --
3122 Procedure create_osha_numbers(p_number_of_workers in number default 1,
3123 p_current_worker in number default 1) is
3124 --
3125 --cursor selects all US business groups, which are not yet have rows in
3126 --PER_US_OSHA_NUMBERS table.
3127 --
3128 cursor csr_us_bg is
3129 select business_group_id
3130 from hr_all_organization_units units
3131 ,hr_organization_information org
3132 where units.organization_id = org.organization_id
3133 --and units.type = 'BG'
3137 and not exists (select null from per_us_osha_numbers osha
3134 and mod(business_group_id,p_number_of_workers) = p_current_worker-1
3135 and org.org_information_context = 'Business Group Information'
3136 and org.org_information9 = 'US'
3138 where osha.business_group_id = units.business_group_id
3139 and osha.case_year = 1900
3140 );
3141
3142 --
3143 l_proc varchar2(72) := g_package||'delete_validate';
3144 --
3145
3146 Begin
3147 hr_utility.set_location('Entering:'||l_proc, 5);
3148 --
3149 --
3150 -- Loop for all the existing US business Groups
3151 --
3152 for i in csr_us_bg loop
3153 --
3154 -- Loop for all the years until End of Time (4712)
3155 --
3156 -- Changed to 2200 for now
3157 --
3158 for x in 1900 .. 2200 loop
3159 insert into per_us_osha_numbers(
3160 case_year,
3161 business_group_id,
3162 next_value,
3163 last_update_date,
3164 last_updated_by,
3165 last_update_login,
3166 created_by,
3167 creation_date)
3168 values (x,
3169 i.business_group_id,
3170 1,
3171 trunc(sysdate),
3172 -1,
3173 0,
3174 -1,
3175 trunc(sysdate)
3176 );
3177 --commented since inserting only 300 records.
3178 --if mod(x,600) = 0 then
3179 -- commit;
3180 --end if;
3181 --
3182 end loop; -- End of Time Loop (changed until 2200 only)
3183 commit;
3184 --
3185 end loop; -- Business Groups Loop
3186 --
3187 hr_utility.set_location(' Leaving:'||l_proc, 10);
3188 --
3189 End create_osha_numbers;
3190 --
3191 --
3192
3193 end per_inc_bus;