1 Package Body irc_ido_bus as
2 /* $Header: iridorhi.pkb 120.5.12010000.2 2008/09/26 13:55:20 pvelugul ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' irc_ido_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_document_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_document_id in number
22 ) is
23 --
24 --
25 -- Declare local variables
26 --
27 l_proc varchar2(72) := g_package||'set_security_group_id';
28 --
29 begin
30 --
31 hr_utility.set_location('Entering:'|| l_proc, 10);
32 --
33 -- Ensure that all the mandatory parameter are not null
34 --
35 hr_api.mandatory_arg_error
36 (p_api_name => l_proc
37 ,p_argument => 'document_id'
38 ,p_argument_value => p_document_id
39 );
40 --
41 --
42 hr_utility.set_location(' Leaving:'|| l_proc, 20);
43 --
44 end set_security_group_id;
45 --
46 -- ---------------------------------------------------------------------------
47 -- |---------------------< return_legislation_code >-------------------------|
48 -- ---------------------------------------------------------------------------
49 --
50 Function return_legislation_code
51 (p_document_id in number
52 )
53 Return Varchar2 Is
54 --
55 -- Declare cursor
56 --
57 -- Declare local variables
58 --
59 l_legislation_code varchar2(100);
60 l_proc varchar2(72) := g_package||'return_legislation_code';
61 --
62 Begin
63 --
64 hr_utility.set_location('Entering:'|| l_proc, 10);
65 --
66 -- Ensure that all the mandatory parameter are not null
67 --
68 hr_api.mandatory_arg_error
69 (p_api_name => l_proc
70 ,p_argument => 'document_id'
71 ,p_argument_value => p_document_id
72 );
73 --
74 hr_utility.set_location(' Leaving:'|| l_proc, 40);
75 return l_legislation_code;
76 end return_legislation_code;
77 -- ---------------------------------------------------------------------------
78 -- |--------------------------< chk_party_id >-------------------------------|
79 -- ---------------------------------------------------------------------------
80 --
81 -- {Start Of Comments}
82 --
83 -- Description:
84 -- This process validates the party_id exists in HZ_PARTIES, on insert.
85 --
86 -- Pre Conditions:
87 -- None.
88 --
89 -- In Parameters:
90 -- p_party_id number
91 --
92 -- Post Success:
93 -- Processing continues.
94 --
95 -- Post Failure:
96 -- An application error will be raised for the following faliure conditions:
97 -- 1: p_party_id does not exist in HZ_PARTIES
98 --
99 -- Access Status:
100 -- Internal Table Handler Use Only.
101 Procedure chk_party_id(p_party_id in number) is
102 --
103 l_proc varchar2(72) := g_package||'chk_party_id';
104 --
105 l_party_id hz_parties.party_id%type ;
106 cursor csr_valid_party is
107 select hp.party_id
108 from hz_parties hp
109 where hp.party_id = p_party_id;
110 begin
111 hr_utility.set_location('Entering: '|| l_proc, 1);
112 --
113 -- Check the party_id being passed exists on HZ_PARTIES table.
114 --
115 open csr_valid_party;
116 fetch csr_valid_party into l_party_id;
117 if csr_valid_party%notfound then
118 close csr_valid_party;
119 hr_utility.set_message(800,'IRC_289477_RTM_INV_PARTY_ID');
120 hr_utility.raise_error;
121 end if;
122 close csr_valid_party;
123 --
124 hr_utility.set_location('Exiting: '|| l_proc, 2);
125 end chk_party_id;
126 -- ---------------------------------------------------------------------------
127 -- |--------------------------< chk_person_id >-------------------------------|
128 -- ---------------------------------------------------------------------------
129 --
130 -- {Start Of Comments}
131 --
132 -- Description:
133 -- This procedure is used to ensure that mandatory parameters have been set.
134 -- If the person id is not found in per_all_people_f an error is generated.
135 --
136 -- Pre Conditions:
137 -- None.
138 --
139 -- In Parameters:
140 -- p_person_id
141 -- p_party_id
142 -- p_effective_date
143 --
144 -- Post Success:
145 -- Processing continues.
146 --
147 -- Post Failure:
148 -- An application error will be raised for the following faliure conditions:
149 -- 1: p_rec.p_person_id does not exist in PER_ALL_PEOPLE_F
150 --
151 -- Access Status:
152 -- Internal Table Handler Use Only.
153 Procedure chk_person_id
154 (p_person_id in irc_documents.person_id%type
155 ,p_party_id in out nocopy irc_documents.party_id%type
156 ,p_effective_date date) is
157 --
158 l_proc varchar2(72) := g_package || 'chk_person_id';
159 l_party_id irc_documents.party_id%type;
160 l_var varchar2(30);
161 --
162 --
163 -- Cursor to check that the person_id exists in PER_ALL_PEOPLE_F.
164 --
165 cursor csr_person_id is
166 select per.party_id
167 from per_all_people_f per
168 where per.person_id = p_person_id
169 and p_effective_date between effective_start_date and effective_end_date;
170 Begin
171 --
172 hr_utility.set_location(' Entering:'||l_proc,10);
173 --
174 -- Check if the person_id exists in PER_ALL_PEOPLE_F.
175 --
176 open csr_person_id;
177 fetch csr_person_id into l_party_id;
178 hr_utility.set_location(l_proc, 30);
179 if csr_person_id%notfound then
180 close csr_person_id;
181 fnd_message.set_name('PER','IRC_412249_DOC_NO_PERSON');
182 fnd_message.raise_error;
183 end if;
184 close csr_person_id;
185 if p_party_id is not null then
186 if p_party_id<>l_party_id then
187 fnd_message.set_name('PER','IRC_412033_RTM_INV_PARTY_ID');
188 fnd_message.raise_error;
189 end if;
190 else
191 p_party_id:=l_party_id;
192 end if;
193 --
194 End chk_person_id;
195 --
196 -- ---------------------------------------------------------------------------
197 -- |--------------------------< chk_monthly_doc_upload_count >-----------------------|
198 -- ---------------------------------------------------------------------------
199 --
200 -- {Start Of Comments}
201 --
202 -- Description:
203 -- This procedure is used to ensure that the number of documents that
204 -- can be inserted in the last month is limited to the value
205 -- specified by profile, IRC_MONTHLY_DOC_UPLOAD_COUNT.
206 --
207 -- Pre Conditions:
208 -- None.
209 --
210 -- In Parameters:
211 -- p_person_id
212 -- p_effective_date
213 --
214 -- Post Success:
215 -- Processing continues.
216 --
217 -- Post Failure:
218 -- An application error will be raised if the
219 -- number of document rows inserted for p_person_id exceeds
220 -- the value given by profile, IRC_MONTHLY_DOC_UPLOAD_COUNT
221 -- in the last month
222 --
223 -- Access Status:
224 -- Internal Table Handler Use Only.
225 Procedure chk_monthly_doc_upload_count
226 (p_person_id in irc_documents.person_id%type
227 ,p_effective_date date) is
228 --
229 l_proc varchar2(72) := g_package || 'chk_monthly_doc_upload_count';
230 l_count number;
231 l_max_count number;
232 --
233 --
234 -- Cursor to check that document upload count not exceeded in last month
235 --
236 cursor csr_doc_upload_count is
237 select count(*)
238 from irc_documents doc
239 where doc.person_id = p_person_id
240 and doc.end_date is null
241 and months_between(p_effective_date,doc.creation_date) <= 1;
242 Begin
243 --
244 hr_utility.set_location(' Entering:'||l_proc,10);
245 --
246 --
247 open csr_doc_upload_count;
248 fetch csr_doc_upload_count into l_count;
249 close csr_doc_upload_count;
250 hr_utility.set_location(l_proc, 20);
251 l_max_count := to_number(fnd_profile.value('IRC_MONTHLY_DOC_UPLOAD_COUNT'));
252 if l_count >= l_max_count then
253 hr_utility.set_location(l_proc, 30);
254 fnd_message.set_name('PER','IRC_MAX_DOC_UPLOADS_EXCEEDED');
255 fnd_message.raise_error;
256 end if;
257 hr_utility.set_location('Leaving'||l_proc, 40);
258 --
259 End chk_monthly_doc_upload_count;
260 --
261 -- ---------------------------------------------------------------------------
262 -- |--------------------------< chk_total_doc_upload_count >-----------------|
263 -- ---------------------------------------------------------------------------
264 --
265 -- {Start Of Comments}
266 --
267 -- Description:
268 -- This procedure is used to ensure that the number of documents that
269 -- can be inserted for a person is limited to the value
270 -- specified by profile, IRC_TOTAL_DOC_UPLOAD_COUNT.
271 --
272 -- Pre Conditions:
273 -- None.
274 --
275 -- In Parameters:
276 -- p_person_id
277 --
278 -- Post Success:
279 -- Processing continues.
280 --
281 -- Post Failure:
282 -- An application error will be raised if the
283 -- number of document rows inserted for p_person_id exceeds
284 -- the value given by profile, IRC_TOTAL_DOC_UPLOAD_COUNT
285 --
286 -- Access Status:
287 -- Internal Table Handler Use Only.
288 Procedure chk_total_doc_upload_count
289 (p_person_id in irc_documents.person_id%type ) is
290 --
291 l_proc varchar2(72) := g_package || 'chk_total_doc_upload_count';
292 l_count number;
293 l_max_count number;
294 --
295 --
296 -- Cursor to check that document upload count not exceeded in last month
297 --
298 cursor csr_doc_upload_count is
299 select count(*)
300 from irc_documents doc
301 where doc.person_id = p_person_id
302 and doc.end_date is null;
303 Begin
304 --
305 hr_utility.set_location(' Entering:'||l_proc,10);
306 --
307 --
308 open csr_doc_upload_count;
309 fetch csr_doc_upload_count into l_count;
310 close csr_doc_upload_count;
311 hr_utility.set_location(l_proc, 20);
312 l_max_count := to_number(fnd_profile.value('IRC_TOTAL_DOC_UPLOAD_COUNT'));
313 if l_count >= l_max_count then
314 hr_utility.set_location(l_proc, 30);
315 fnd_message.set_name('PER','IRC_TOT_DOC_UPLOADS_EXCEEDED');
316 fnd_message.raise_error;
317 end if;
318 hr_utility.set_location('Leaving'||l_proc, 40);
319 --
320 End chk_total_doc_upload_count;
321 --
322 -- ---------------------------------------------------------------------------
323 -- |----------------------------< chk_type >---------------------------------|
324 -- ---------------------------------------------------------------------------
325 --
326 -- {Start Of Comments}
327 --
328 -- Description:
329 -- This process validates the document type exists in the lookup
330 -- IRC_DOCUMENT_TYPE
331 --
332 -- Pre Conditions:
333 -- None.
334 --
335 -- In Parameters:
336 -- type varchar2(30) document type
337 -- document_id number(15) PK of IRC_DOCUMENTS
338 -- object_version_number number(9) version of row
339 -- effective_date date date record effective
340 --
341 -- Post Success:
342 -- Processing continues.
343 --
344 -- Post Failure:
345 -- An application error will be raised for the following faliure conditions:
346 -- 1: p_type does not exist in lookup IRC_DOCUMENT_TYPE
347 --
348 -- Access Status:
349 -- Internal Table Handler Use Only.
350 Procedure chk_type(p_type in varchar2,
351 p_document_id in number,
352 p_effective_date in date,
353 p_object_version_number in number) is
354 --
355 l_proc varchar2(72) := g_package||'chk_type';
356 l_api_updating boolean;
357 --
358 begin
359 hr_utility.set_location('Entering: '|| l_proc, 1);
360 l_api_updating := irc_ido_shd.api_updating
361 (p_document_id => p_document_id,
362 p_object_version_number => p_object_version_number);
363 --
364 if (l_api_updating
365 and nvl(p_type,hr_api.g_varchar2)
366 <> nvl(irc_ido_shd.g_old_rec.type,hr_api.g_varchar2)
367 or not l_api_updating) then
368 --
369 -- check if value of type falls within lookup.
370 --
371 if hr_api.not_exists_in_hr_lookups(p_lookup_type => 'IRC_DOCUMENT_TYPE',
372 p_lookup_code => p_type,
373 p_effective_date => p_effective_date)
374 then
375 --
376 -- raise error as does not exist as lookup
377 --
378 hr_utility.set_location('Leaving: '|| l_proc, 3);
379 hr_utility.set_message(800,'IRC_412089_NO_SUCH_DOC_TYPE');
380 hr_utility.raise_error;
381 end if;
382 end if;
383 hr_utility.set_location('Leaving: '|| l_proc, 2);
384 end chk_type;
385
386 --
387 -- ----------------------------------------------------------------------------
388 -- |-----------------------< chk_non_updateable_args >------------------------|
389 -- ----------------------------------------------------------------------------
390 -- {Start Of Comments}
391 --
392 -- Description:
393 -- This procedure is used to ensure that non updateable attributes have
394 -- not been updated. If an attribute has been updated an error is generated.
395 --
396 -- Pre Conditions:
397 -- g_old_rec has been populated with details of the values currently in
398 -- the database.
399 --
400 -- In Arguments:
401 -- p_rec has been populated with the updated values the user would like the
402 -- record set to.
403 --
404 -- Post Success:
405 -- Processing continues if all the non updateable attributes have not
406 -- changed.
407 --
408 -- Post Failure:
409 -- An application error is raised if any of the non updatable attributes
410 -- have been altered.
411 --
412 -- {End Of Comments}
413 -- ----------------------------------------------------------------------------
414 Procedure chk_non_updateable_args
415 (p_effective_date in date
416 ,p_rec in irc_ido_shd.g_rec_type
417 ) IS
418 --
419 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
420 l_error EXCEPTION;
421 l_argument varchar2(30);
422 --
423 Begin
424 --
425 -- Only proceed with the validation if a row exists for the current
426 -- record in the HR Schema.
427 --
428 IF NOT irc_ido_shd.api_updating
429 (p_document_id => p_rec.document_id
430 ,p_object_version_number => p_rec.object_version_number
431 ) THEN
432 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
433 fnd_message.set_token('PROCEDURE ', l_proc);
434 fnd_message.set_token('STEP ', '5');
435 fnd_message.raise_error;
436 END IF;
437 --
438 --
439 EXCEPTION
440 WHEN l_error THEN
441 hr_api.argument_changed_error
442 (p_api_name => l_proc
443 ,p_argument => l_argument);
444 WHEN OTHERS THEN
445 RAISE;
446 End chk_non_updateable_args;
447 --
448 -- ----------------------------------------------------------------------------
449 -- |---------------------------< insert_validate >----------------------------|
450 -- ----------------------------------------------------------------------------
451 Procedure insert_validate
452 (p_effective_date in date
453 ,p_rec in out nocopy irc_ido_shd.g_rec_type
454 ) is
455 --
456 l_proc varchar2(72) := g_package||'insert_validate';
457 --
458 Begin
459 hr_utility.set_location('Entering:'||l_proc, 5);
460 --
461 -- Call all supporting business operations
462 --
463 chk_person_id
464 (p_person_id => p_rec.person_id
465 ,p_party_id => p_rec.party_id
466 ,p_effective_date => p_effective_date
467 );
468 --
469 chk_type
470 (p_type => p_rec.type,
471 p_document_id => p_rec.document_id,
472 p_effective_date => p_effective_date,
473 p_object_version_number => p_rec.object_version_number);
474 --
475 -- chk_total_doc_upload_count
476 -- (p_person_id => p_rec.person_id
477 -- );
478 --
479 -- chk_monthly_doc_upload_count
480 -- (p_person_id => p_rec.person_id
481 -- ,p_effective_date => p_effective_date
482 -- );
483 hr_utility.set_location(' Leaving:'||l_proc, 10);
484 End insert_validate;
485 --
486 -- ----------------------------------------------------------------------------
487 -- |---------------------------< update_validate >----------------------------|
488 -- ----------------------------------------------------------------------------
489 Procedure update_validate
490 (p_effective_date in date
491 ,p_rec in irc_ido_shd.g_rec_type
492 ) is
493 --
494 l_proc varchar2(72) := g_package||'update_validate';
495 --
496 Begin
497 hr_utility.set_location('Entering:'||l_proc, 5);
498 --
499 -- Call all supporting business operations
500 --
501 chk_type
502 (p_type => p_rec.type,
503 p_document_id => p_rec.document_id,
504 p_effective_date => p_effective_date,
505 p_object_version_number => p_rec.object_version_number);
506 --
507 hr_utility.set_location(' Leaving:'||l_proc, 10);
508 End update_validate;
509 --
510 -- ----------------------------------------------------------------------------
511 -- |---------------------------< delete_validate >----------------------------|
512 -- ----------------------------------------------------------------------------
513 Procedure delete_validate
514 (p_rec in irc_ido_shd.g_rec_type
515 ) is
516 --
517 l_proc varchar2(72) := g_package||'delete_validate';
518 --
519 Begin
520 hr_utility.set_location('Entering:'||l_proc, 5);
521 --
522 -- Call all supporting business operations
523 --
524 hr_utility.set_location(' Leaving:'||l_proc, 10);
525 End delete_validate;
526 --
527 end irc_ido_bus;