1 Package Body ota_nhs_bus as
2 /* $Header: otnhsrhi.pkb 120.1 2005/09/30 05:00:04 ssur noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_nhs_bus.'; -- Global package name
9 g_nota_history_id number default null;
10 g_legislation_code varchar2(150) default null;
11 -- ---------------------------------------------------------------------------
12 -- |----------------------< set_security_group_id >--------------------------|
13 -- ---------------------------------------------------------------------------
14 --
15 Procedure set_security_group_id
16 (p_nota_history_id in number
17 ) is
18 --
19 -- Declare cursor
20 --
21 cursor csr_sec_grp is
22 select pbg.security_group_id
23 from per_business_groups pbg
24 , ota_notrng_histories nth
25 where nth.nota_history_id = p_nota_history_id
26 and pbg.business_group_id = nth.business_group_id;
27 --
28 -- Declare local variables
29 --
30 l_security_group_id number;
31 l_proc varchar2(72) := g_package||'set_security_group_id';
32 --
33 begin
34 --
35 hr_utility.set_location('Entering:'|| l_proc, 10);
36 --
37 -- Ensure that all the mandatory parameter are not null
38 --
39 hr_api.mandatory_arg_error
40 (p_api_name => l_proc
41 ,p_argument => 'nota_history_id'
42 ,p_argument_value => p_nota_history_id
43 );
44 --
45 open csr_sec_grp;
46 fetch csr_sec_grp into l_security_group_id;
47 --
48 if csr_sec_grp%notfound then
49 --
50 close csr_sec_grp;
51 --
52 -- The primary key is invalid therefore we must error
53 --
54 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
55 fnd_message.raise_error;
56 --
57 end if;
58 close csr_sec_grp;
59 --
60 -- Set the security_group_id in CLIENT_INFO
61 --
62 hr_api.set_security_group_id
63 (p_security_group_id => l_security_group_id
64 );
65 --
66 hr_utility.set_location(' Leaving:'|| l_proc, 20);
67 --
68 end set_security_group_id;
69
70 --
71 -- ---------------------------------------------------------------------------
72 -- |---------------------< return_legislation_code >-------------------------|
73 -- ---------------------------------------------------------------------------
74 --
75 Function return_legislation_code
76 (p_nota_history_id in number
77 )
78 Return Varchar2 Is
79 --
80 -- Declare cursor
81 --
82 cursor csr_leg_code is
83 select pbg.legislation_code
84 from per_business_groups pbg
85 , ota_notrng_histories nth
86 where nth.nota_history_id = p_nota_history_id
87 and pbg.business_group_id = nth.business_group_id;
88 --
89 -- Declare local variables
90 --
91 l_legislation_code varchar2(150);
92 l_proc varchar2(72) := g_package||'return_legislation_code';
93 --
94 Begin
95 --
96 hr_utility.set_location('Entering:'|| l_proc, 10);
97 --
98 -- Ensure that all the mandatory parameter are not null
99 --
100 hr_api.mandatory_arg_error
101 (p_api_name => l_proc
102 ,p_argument => 'nota_history_id'
103 ,p_argument_value => p_nota_history_id
104 );
105 --
106 if ( nvl(ota_nhs_bus.g_nota_history_id, hr_api.g_number)
107 = p_nota_history_id) then
108 --
109 -- The legislation code has already been found with a previous
110 -- call to this function. Just return the value in the global
111 -- variable.
112 --
113 l_legislation_code := ota_nhs_bus.g_legislation_code;
114 hr_utility.set_location(l_proc, 20);
115 else
116 --
117 -- The ID is different to the last call to this function
118 -- or this is the first call to this function.
119 --
120 open csr_leg_code;
121 fetch csr_leg_code into l_legislation_code;
122 --
123 if csr_leg_code%notfound then
124 --
125 -- The primary key is invalid therefore we must error
126 --
127 close csr_leg_code;
128 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
129 fnd_message.raise_error;
130 end if;
131 hr_utility.set_location(l_proc,30);
132 --
133 -- Set the global variables so the values are
134 -- available for the next call to this function.
135 --
136 close csr_leg_code;
137 ota_nhs_bus.g_nota_history_id := p_nota_history_id;
138 ota_nhs_bus.g_legislation_code := l_legislation_code;
139 end if;
140 hr_utility.set_location(' Leaving:'|| l_proc, 40);
141 return l_legislation_code;
142 end return_legislation_code;
143 --
144 -- ----------------------------------------------------------------------------
145 -- |-----------------------------< chk_df >----------------------------------|
146 -- ----------------------------------------------------------------------------
147 --
148 -- Description:
149 -- Validates all the Descriptive Flexfield values.
150 --
151 -- Prerequisites:
152 -- All other columns have been validated. Must be called as the
153 -- last step from insert_validate and update_validate.
154 --
155 -- In Arguments:
156 -- p_rec
157 --
158 -- Post Success:
159 -- If the Descriptive Flexfield structure column and data values
160 -- are all valid this procedure will end normally and processing will
161 -- continue.
162 --
163 -- Post Failure:
164 -- If the Descriptive Flexfield structure column value or any of
165 -- the data values are invalid then an application error is raised as
166 -- a PL/SQL exception.
167 --
168 -- Access Status:
169 -- Internal Row Handler Use Only.
170 --
171 -- ----------------------------------------------------------------------------
172 procedure chk_df
173 (p_rec in ota_nhs_shd.g_rec_type
174 ) is
175 --
176 l_proc varchar2(72) := g_package || 'chk_df';
177 --
178 begin
179 hr_utility.set_location('Entering:'||l_proc,10);
180 --
181 if ((p_rec.nota_history_id is not null) and (
182 nvl(ota_nhs_shd.g_old_rec.nth_information_category, hr_api.g_varchar2) <>
183 nvl(p_rec.nth_information_category, hr_api.g_varchar2) or
184 nvl(ota_nhs_shd.g_old_rec.nth_information1, hr_api.g_varchar2) <>
185 nvl(p_rec.nth_information1, hr_api.g_varchar2) or
186 nvl(ota_nhs_shd.g_old_rec.nth_information2, hr_api.g_varchar2) <>
187 nvl(p_rec.nth_information2, hr_api.g_varchar2) or
188 nvl(ota_nhs_shd.g_old_rec.nth_information3, hr_api.g_varchar2) <>
189 nvl(p_rec.nth_information3, hr_api.g_varchar2) or
190 nvl(ota_nhs_shd.g_old_rec.nth_information4, hr_api.g_varchar2) <>
191 nvl(p_rec.nth_information4, hr_api.g_varchar2) or
192 nvl(ota_nhs_shd.g_old_rec.nth_information5, hr_api.g_varchar2) <>
193 nvl(p_rec.nth_information5, hr_api.g_varchar2) or
194 nvl(ota_nhs_shd.g_old_rec.nth_information6, hr_api.g_varchar2) <>
195 nvl(p_rec.nth_information6, hr_api.g_varchar2) or
196 nvl(ota_nhs_shd.g_old_rec.nth_information7, hr_api.g_varchar2) <>
197 nvl(p_rec.nth_information7, hr_api.g_varchar2) or
198 nvl(ota_nhs_shd.g_old_rec.nth_information8, hr_api.g_varchar2) <>
199 nvl(p_rec.nth_information8, hr_api.g_varchar2) or
200 nvl(ota_nhs_shd.g_old_rec.nth_information9, hr_api.g_varchar2) <>
201 nvl(p_rec.nth_information9, hr_api.g_varchar2) or
202 nvl(ota_nhs_shd.g_old_rec.nth_information10, hr_api.g_varchar2) <>
203 nvl(p_rec.nth_information10, hr_api.g_varchar2) or
204 nvl(ota_nhs_shd.g_old_rec.nth_information11, hr_api.g_varchar2) <>
205 nvl(p_rec.nth_information11, hr_api.g_varchar2) or
206 nvl(ota_nhs_shd.g_old_rec.nth_information12, hr_api.g_varchar2) <>
207 nvl(p_rec.nth_information12, hr_api.g_varchar2) or
208 nvl(ota_nhs_shd.g_old_rec.nth_information13, hr_api.g_varchar2) <>
209 nvl(p_rec.nth_information13, hr_api.g_varchar2) or
210 nvl(ota_nhs_shd.g_old_rec.nth_information14, hr_api.g_varchar2) <>
211 nvl(p_rec.nth_information14, hr_api.g_varchar2) or
212 nvl(ota_nhs_shd.g_old_rec.nth_information15, hr_api.g_varchar2) <>
213 nvl(p_rec.nth_information15, hr_api.g_varchar2) or
214 nvl(ota_nhs_shd.g_old_rec.nth_information16, hr_api.g_varchar2) <>
215 nvl(p_rec.nth_information16, hr_api.g_varchar2) or
216 nvl(ota_nhs_shd.g_old_rec.nth_information17, hr_api.g_varchar2) <>
217 nvl(p_rec.nth_information17, hr_api.g_varchar2) or
218 nvl(ota_nhs_shd.g_old_rec.nth_information18, hr_api.g_varchar2) <>
219 nvl(p_rec.nth_information18, hr_api.g_varchar2) or
220 nvl(ota_nhs_shd.g_old_rec.nth_information19, hr_api.g_varchar2) <>
221 nvl(p_rec.nth_information19, hr_api.g_varchar2) or
222 nvl(ota_nhs_shd.g_old_rec.nth_information20, hr_api.g_varchar2) <>
223 nvl(p_rec.nth_information20, hr_api.g_varchar2) )
224 -- or (p_rec.nota_history_id is not null) ) then Bug 2483317
225 or (p_rec.nota_history_id is null) ) then
226
227 --
228 -- Only execute the validation if absolutely necessary:
229 -- a) During update, the structure column value or any
230 -- of the attribute values have actually changed.
231 -- b) During insert.
232 --
233 hr_dflex_utility.ins_or_upd_descflex_attribs
234 (p_appl_short_name => 'OTA'
235 ,p_descflex_name => 'OTA_NOTRNG_HISTORIES'
236 ,p_attribute_category => p_rec.NTH_INFORMATION_CATEGORY
237 ,p_attribute1_name => 'NTH_INFORMATION1'
238 ,p_attribute1_value => p_rec.nth_information1
239 ,p_attribute2_name => 'NTH_INFORMATION2'
240 ,p_attribute2_value => p_rec.nth_information2
241 ,p_attribute3_name => 'NTH_INFORMATION3'
242 ,p_attribute3_value => p_rec.nth_information3
243 ,p_attribute4_name => 'NTH_INFORMATION4'
244 ,p_attribute4_value => p_rec.nth_information4
245 ,p_attribute5_name => 'NTH_INFORMATION5'
246 ,p_attribute5_value => p_rec.nth_information5
247 ,p_attribute6_name => 'NTH_INFORMATION6'
248 ,p_attribute6_value => p_rec.nth_information6
249 ,p_attribute7_name => 'NTH_INFORMATION7'
250 ,p_attribute7_value => p_rec.nth_information7
251 ,p_attribute8_name => 'NTH_INFORMATION8'
252 ,p_attribute8_value => p_rec.nth_information8
253 ,p_attribute9_name => 'NTH_INFORMATION9'
254 ,p_attribute9_value => p_rec.nth_information9
255 ,p_attribute10_name => 'NTH_INFORMATION10'
256 ,p_attribute10_value => p_rec.nth_information10
257 ,p_attribute11_name => 'NTH_INFORMATION11'
258 ,p_attribute11_value => p_rec.nth_information11
259 ,p_attribute12_name => 'NTH_INFORMATION12'
260 ,p_attribute12_value => p_rec.nth_information12
261 ,p_attribute13_name => 'NTH_INFORMATION13'
262 ,p_attribute13_value => p_rec.nth_information13
263 ,p_attribute14_name => 'NTH_INFORMATION14'
264 ,p_attribute14_value => p_rec.nth_information14
265 ,p_attribute15_name => 'NTH_INFORMATION15'
266 ,p_attribute15_value => p_rec.nth_information15
267 ,p_attribute16_name => 'NTH_INFORMATION16'
268 ,p_attribute16_value => p_rec.nth_information16
269 ,p_attribute17_name => 'NTH_INFORMATION17'
270 ,p_attribute17_value => p_rec.nth_information17
271 ,p_attribute18_name => 'NTH_INFORMATION18'
272 ,p_attribute18_value => p_rec.nth_information18
273 ,p_attribute19_name => 'NTH_INFORMATION19'
274 ,p_attribute19_value => p_rec.nth_information19
275 ,p_attribute20_name => 'NTH_INFORMATION20'
276 ,p_attribute20_value => p_rec.nth_information20
277 );
278 end if;
279 --
280 hr_utility.set_location(' Leaving:'||l_proc,20);
281 end chk_df;
282 --
283 -- ----------------------------------------------------------------------------
284 -- |-----------------------< chk_non_updateable_args >------------------------|
285 -- ----------------------------------------------------------------------------
286 -- {Start Of Comments}
287 --
288 -- Description:
289 -- This procedure is used to ensure that non updateable attributes have
290 -- not been updated. If an attribute has been updated an error is generated.
291 --
292 -- Pre Conditions:
293 -- g_old_rec has been populated with details of the values currently in
294 -- the database.
295 --
296 -- In Arguments:
297 -- p_rec has been populated with the updated values the user would like the
298 -- record set to.
299 --
300 -- Post Success:
301 -- Processing continues if all the non updateable attributes have not
302 -- changed.
303 --
304 -- Post Failure:
305 -- An application error is raised if any of the non updatable attributes
306 -- have been altered.
307 --
308 -- {End Of Comments}
309 -- ----------------------------------------------------------------------------
310 Procedure chk_non_updateable_args
311 (p_effective_date in date
312 ,p_rec in ota_nhs_shd.g_rec_type
313 ) IS
314 --
315 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
316 l_error EXCEPTION;
317 l_argument varchar2(30);
318 --
319 Begin
320 --
321 -- Only proceed with the validation if a row exists for the current
322 -- record in the HR Schema.
323 --
324 IF NOT ota_nhs_shd.api_updating
325 (p_nota_history_id => p_rec.nota_history_id
326 ,p_object_version_number => p_rec.object_version_number
327 ) THEN
328 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
329 fnd_message.set_token('PROCEDURE ', l_proc);
330 fnd_message.set_token('STEP ', '5');
331 fnd_message.raise_error;
332 END IF;
333
334 if nvl(p_rec.business_group_id,hr_api.g_number) <>
335 nvl(ota_nhs_shd.g_old_rec.business_group_id,hr_api.g_number)
336 then
337 l_argument := 'business_group_id';
338 raise l_error;
339 end if;
340 if nvl(p_rec.customer_id,hr_api.g_number) <>
341 nvl(ota_nhs_shd.g_old_rec.customer_id,hr_api.g_number)
342 then
343 l_argument := 'customer_id';
344 raise l_error;
345 end if;
346 if nvl(p_rec.organization_id,hr_api.g_number) <>
347 nvl(ota_nhs_shd.g_old_rec.organization_id,hr_api.g_number)
348 then
349 l_argument := 'organization_id';
350 raise l_error;
351 end if;
352 if nvl(p_rec.person_id,hr_api.g_number) <>
353 nvl(ota_nhs_shd.g_old_rec.person_id,hr_api.g_number)
354 then
355 l_argument := 'person_id';
356 raise l_error;
357 end if;
358
359 EXCEPTION
360 WHEN l_error THEN
361 hr_api.argument_changed_error
362 (p_api_name => l_proc
363 ,p_argument => l_argument);
364 WHEN OTHERS THEN
365 RAISE;
366 End chk_non_updateable_args;
367
368 -- ----------------------------------------------------------------------------
369 -- |---------------------------< chk_organization_id >-----------------------|
370 -- ----------------------------------------------------------------------------
371 Procedure chk_organization_id
372 (p_nota_history_id in number
373 ,p_organization_id in number
374 ,p_business_group_id in number
375 ,p_effective_date in date) is
376
377 --
378 l_proc varchar2(72) := g_package||'chk_organization_id';
379 l_single_business_group_id ota_notrng_histories.business_group_id%type;
380 l_exists varchar2(1);
381 l_cross_business_group varchar2(1);
382 l_business_group_id ota_events.business_group_id%type ;
383
384 --
385 -- cursor to check if oganization id is valid.
386 --
387 cursor csr_organization is
388 select null
389 from hr_all_organization_units
390 where organization_id = p_organization_id and
391 business_group_id = p_business_group_id;
392
393 cursor csr_organization_cross is
394 select null
395 from hr_all_organization_units
396 where organization_id = p_organization_id ;
397
398
399 Begin
400 hr_utility.set_location('Entering:'||l_proc, 5);
401 ota_nhs_bus.get_profile_value(l_cross_business_group,
402 l_business_group_id );
403
404 if (((p_nota_history_id is not null) and
405 nvl(ota_nhs_shd.g_old_rec.organization_id,hr_api.g_number) <>
406 nvl(p_organization_id,hr_api.g_number))
407 or (p_nota_history_id is null)) then
408 --
409 hr_utility.set_location('Entering:'||l_proc, 10);
410 if (p_organization_id is not null) then
411 hr_utility.set_location('Entering:'||l_proc, 15);
412 If l_business_group_id is not null then
413 open csr_organization_cross;
414 fetch csr_organization_cross into l_exists;
415 if csr_organization_cross%notfound then
416 close csr_organization_cross;
417 fnd_message.set_name('OTA','OTA_13268_TFH_INVALID_ORG');
418 fnd_message.raise_error;
419 end if;
420 close csr_organization_cross;
421 hr_utility.set_location('Entering:'||l_proc, 20);
422
423 else
424 open csr_organization;
425 fetch csr_organization into l_exists;
426 if csr_organization%notfound then
427 close csr_organization;
428 fnd_message.set_name('OTA','OTA_13268_TFH_INVALID_ORG');
429 fnd_message.raise_error;
430 end if;
431 close csr_organization;
432 hr_utility.set_location('Entering:'||l_proc, 25);
433 end if;
434 end if;
435 end if;
436 hr_utility.set_location('Entering:'||l_proc, 30);
437 end chk_organization_id;
438
439
440 -- ----------------------------------------------------------------------------
441 -- |---------------------------< chk_customer_id >---------------------------|
442 -- ----------------------------------------------------------------------------
443 Procedure chk_customer_id
444 (p_nota_history_id in number
445 ,p_customer_id in number
446 ,p_effective_date in date) is
447
448 --
449 l_proc varchar2(72) := g_package||'chk_customer_id';
450 l_exists varchar2(1);
451
452 --
453 -- cursor to check is person id is belong to customer.
454 --
455 cursor csr_customer is
456 Select null
457 From HZ_PARTIES party,
458 HZ_CUST_ACCOUNTS cust_acct
459 Where CUST_ACCT.party_id = PARTY.party_id
460 and CUST_ACCT.cust_account_id=p_customer_id;
461
462 Begin
463 hr_utility.set_location('Entering:'||l_proc, 5);
464
465 if (((p_nota_history_id is not null) and
466 nvl(ota_nhs_shd.g_old_rec.customer_id,hr_api.g_number) <>
467 nvl(p_customer_id,hr_api.g_number))
468 or (p_nota_history_id is null)) then
469 --
470 hr_utility.set_location('Entering:'||l_proc, 10);
471 if (p_customer_id is not null) then
472 hr_utility.set_location('Entering:'||l_proc, 15);
473 open csr_customer;
474 fetch csr_customer into l_exists;
475 if csr_customer%notfound then
476 close csr_customer;
477 fnd_message.set_name('OTA','OTA_13321_TFH_CUSTOMER_NAME');
478 fnd_message.raise_error;
479 end if;
480 close csr_customer;
481 hr_utility.set_location('Entering:'||l_proc, 20);
482 end if;
483 end if;
484 hr_utility.set_location('Entering:'||l_proc, 30);
485 end chk_customer_id;
486
487 -- ----------------------------------------------------------------------------
488 -- |---------------------------< chk_person_id >----------------------------|
489 -- ----------------------------------------------------------------------------
490 Procedure chk_person_id
491 (p_nota_history_id in number
492 ,p_customer_id in number
493 ,p_organization_id in number
494 ,p_person_id in number
495 ,p_business_group_id in number
496 ,p_effective_date in date) is
497
498 --
499 l_proc varchar2(72) := g_package||'chk_person_id';
500 l_exists varchar2(1);
501 l_cross_business_group varchar2(1) ;
502 l_business_group_id ota_events.business_group_id%type ;
503
504 --
505 -- cursor to check is person id is belong to customer.
506 --
507 cursor cus_contact is
508 Select null
509 From HZ_CUST_ACCOUNT_ROLES acct_role,
510 HZ_RELATIONSHIPS rel,
511 HZ_CUST_ACCOUNTS role_acct
512 where acct_role.party_id = rel.party_id
513 and acct_role.role_type = 'CONTACT'
514 and rel.subject_table_name = 'HZ_PARTIES'
515 and rel.object_table_name = 'HZ_PARTIES'
516 and acct_role.cust_account_id = role_acct.cust_account_id
517 and role_acct.party_id = rel.object_id
518 and ACCT_ROLE.cust_account_role_id=p_person_id
519 and ACCT_ROLE.cust_account_id=p_customer_id;
520
521 --
522 -- cursor to check is person id is belong to an organization.
523 --
524 cursor org_person is
525 select null
526 from per_all_people_f per
527 where per.business_group_id = p_business_group_id and
528 per.person_id = p_person_id and
529 rownum=1;
530 /* Bug 2356572
531 and
532 p_effective_date between per.effective_start_date and
533 per.effective_end_date ;
534 */
535 /* For Globalization */
536 cursor org_person_cross is
537 select null
538 from per_all_people_f per
539 where per.person_id = p_person_id
540 and rownum=1;
541 /*Bug 2356572
542 and
543 p_effective_date between per.effective_start_date and
544 per.effective_end_date ;
545 */
546 Begin
547 hr_utility.set_location('Entering:'||l_proc, 5);
548
549 ota_nhs_bus.get_profile_value(l_cross_business_group,
550 l_business_group_id );
551
552
553 if (((p_nota_history_id is not null) and
554 nvl(ota_nhs_shd.g_old_rec.person_id,hr_api.g_number) <>
555 nvl(p_person_id,hr_api.g_number))
556 or (p_nota_history_id is null)) then
557 --
558 hr_utility.set_location('Entering:'||l_proc, 10);
559 if p_person_id is not null then
560 if p_customer_id is null and p_organization_id is null then
561 fnd_message.set_name('OTA','OTA_13884_NHS_PERSON_INVALID');
562 fnd_message.raise_error;
563 end if;
564 end if;
565 if (p_customer_id is not null) then
566 if p_person_id is not null then
567 hr_utility.set_location('Entering:'||l_proc, 15);
568 open cus_contact;
569 fetch cus_contact into l_exists;
570 if cus_contact%notfound then
571 close cus_contact;
572 fnd_message.set_name('OTA','OTA_13884_NHS_PERSON_INVALID');
573 fnd_message.raise_error;
574 end if;
575 close cus_contact;
576 hr_utility.set_location('Entering:'||l_proc, 20);
577 end if;
578 elsif (p_organization_id is not null) then
579 if p_person_id is not null then
580 hr_utility.set_location('Entering:'||l_proc, 30);
581 If l_business_group_id is not null then
582 open org_person_cross;
583 fetch org_person_cross into l_exists;
584 if org_person_cross%notfound then
585 close org_person_cross;
586 fnd_message.set_name('OTA','OTA_13884_NHS_PERSON_INVALID');
587 fnd_message.raise_error;
588 end if;
589 close org_person_cross;
590
591 else
592 open org_person;
593 fetch org_person into l_exists;
594 if org_person%notfound then
595 close org_person;
596 fnd_message.set_name('OTA','OTA_13884_NHS_PERSON_INVALID');
597 fnd_message.raise_error;
598 end if;
599 close org_person;
600 end if;
601 hr_utility.set_location('Entering:'||l_proc, 40);
602 end if;
603 end if;
604 end if;
605 hr_utility.set_location(' Leaving:'||l_proc, 50);
606 End chk_person_id;
607
608
609 -- ----------------------------------------------------------------------------
610 -- |---------------------------< chk_contact_id >----------------------------|
611 -- ----------------------------------------------------------------------------
612 Procedure chk_contact_id
613 (p_nota_history_id in number
614 ,p_customer_id in number
615 ,p_organization_id in number
616 ,p_contact_id in number
617 ,p_effective_date in date) is
618
619 --
620 l_proc varchar2(72) := g_package||'chk_contact_id';
621 l_exists varchar2(1);
622
623 --
624 -- cursor to check is person id is belong to customer.
625 --
626 cursor cus_contact is
627 Select null
628 From HZ_CUST_ACCOUNT_ROLES acct_role,
629 HZ_RELATIONSHIPS rel,
630 HZ_CUST_ACCOUNTS role_acct
631 where acct_role.party_id = rel.party_id
632 and acct_role.role_type = 'CONTACT'
633 and rel.subject_table_name = 'HZ_PARTIES'
634 and rel.object_table_name = 'HZ_PARTIES'
635 and acct_role.cust_account_id = role_acct.cust_account_id
636 and role_acct.party_id = rel.object_id
637 and ACCT_ROLE.cust_account_role_id=p_contact_id
638 and ACCT_ROLE.cust_account_id=p_customer_id;
639
640 --
641 -- cursor to check is person id is belong to an organization.
642 --
643 cursor org_person is
644 select null
645 from per_all_people_f
646 where person_id = p_contact_id and
647 p_effective_date between effective_start_date and
648 effective_end_date ;
649
650 Begin
651 hr_utility.set_location('Entering:'||l_proc, 5);
652
653 if (((p_nota_history_id is not null) and
654 nvl(ota_nhs_shd.g_old_rec.contact_id,hr_api.g_number) <>
655 nvl(p_contact_id,hr_api.g_number))
656 or (p_nota_history_id is null)) then
657 --
658 hr_utility.set_location('Entering:'||l_proc, 10);
659
660 if (p_customer_id is not null) then
661 if p_contact_id is not null then
662 hr_utility.set_location('Entering:'||l_proc, 15);
663 open cus_contact;
664 fetch cus_contact into l_exists;
665 if cus_contact%notfound then
666 close cus_contact;
667 fnd_message.set_name('OTA','OTA_13283_TFH_CUSTOMER_CONTACT');
668 fnd_message.raise_error;
669 end if;
670 close cus_contact;
671 hr_utility.set_location('Entering:'||l_proc, 20);
672 end if;
673 elsif (p_organization_id is not null) then
674 if p_contact_id is not null then
675 hr_utility.set_location('Entering:'||l_proc, 30);
676 open org_person;
677 fetch org_person into l_exists;
678 if org_person%notfound then
679 close org_person;
680 fnd_message.set_name('HR','HR_51889_APR_PERSON_NOT_EXIST');
681 fnd_message.raise_error;
682 end if;
683 close org_person;
684 hr_utility.set_location('Entering:'||l_proc, 40);
685 end if;
686 end if;
687 end if;
688 hr_utility.set_location(' Leaving:'||l_proc, 50);
689 End chk_contact_id;
690
691
692 -- ----------------------------------------------------------------------------
693 -- |---------------------------< chk_status >----------------------------|
694 -- ----------------------------------------------------------------------------
695 Procedure chk_status
696 (p_nota_history_id in number
697 ,p_status in varchar2
698 ,p_effective_date in date) is
699
700 --
701 l_proc varchar2(72) := g_package||'chk_status';
702 l_api_updating boolean;
703
704 begin
705 hr_utility.set_location(' Leaving:'||l_proc, 10);
706 --
707 -- check mandatory parameters has been set
708 --
709 hr_api.mandatory_arg_error
710 (p_api_name => l_proc
711 ,p_argument => 'effective_date'
712 ,p_argument_value =>p_effective_date);
713
714
715 if (((p_nota_history_id is not null) and
716 nvl(ota_nhs_shd.g_old_rec.status,hr_api.g_varchar2) <>
717 nvl(p_status,hr_api.g_varchar2))
718 or
719 (p_nota_history_id is null)) then
720
721 hr_utility.set_location(' Leaving:'||l_proc, 20);
722 --
723 -- if status is not null then
724 -- check if the status value exists in hr_lookups
725 -- where lookup_type is 'OTA_TRAINING_STATUSES'
726 --
727 if p_status is not null then
728 if hr_api.not_exists_in_hr_lookups -- Bug 2478551
729 (p_effective_date => p_effective_date
730 ,p_lookup_type => 'OTA_TRAINING_STATUSES'
731 ,p_lookup_code => p_status) then
732 fnd_message.set_name('OTA','OTA_13880_NHS_STATUS_INVALID');
733 fnd_message.raise_error;
734 end if;
735 hr_utility.set_location(' Leaving:'||l_proc, 30);
736
737 end if;
738
739 end if;
740 hr_utility.set_location(' Leaving:'||l_proc, 40);
741
742 end chk_status;
743 -- ----------------------------------------------------------------------------
744 -- |---------------------------< chk_type >----------------------------|
745 -- ----------------------------------------------------------------------------
746 Procedure chk_type
747 (p_nota_history_id in number
748 ,p_type in varchar2
749 ,p_effective_date in date) is
750
751 --
752 l_proc varchar2(72) := g_package||'chk_type';
753 l_api_updating boolean;
754
755 begin
756 hr_utility.set_location(' Leaving:'||l_proc, 10);
757 --
758 -- check mandatory parameters has been set
759 --
760 hr_api.mandatory_arg_error
761 (p_api_name => l_proc
762 ,p_argument => 'effective_date'
763 ,p_argument_value => p_effective_date);
764
765 if (((p_nota_history_id is not null) and
766 nvl(ota_nhs_shd.g_old_rec.type,hr_api.g_varchar2) <>
767 nvl(p_type,hr_api.g_varchar2))
768 or
769 (p_nota_history_id is null)) then
770
771 hr_utility.set_location(' Leaving:'||l_proc, 20);
772 --
773 -- if status is not null then
774 -- check if the status value exists in hr_lookups
775 -- where lookup_type is 'OTA_TRAINING_TYPES'
776 --
777 if p_type is not null then
778 if hr_api.not_exists_in_hr_lookups -- Bug 2478551
779 (p_effective_date => p_effective_date
780 ,p_lookup_type => 'OTA_TRAINING_TYPES'
781 ,p_lookup_code => p_type) then
782 fnd_message.set_name('OTA','OTA_13879_NHS_TYPE_INVALID');
783 fnd_message.raise_error;
784 end if;
785 hr_utility.set_location(' Leaving:'||l_proc, 30);
786
787 end if;
788
789 end if;
790 hr_utility.set_location(' Leaving:'||l_proc, 40);
791
792 end chk_type;
793
794
795 -- ----------------------------------------------------------------------------
796 -- |---------------------------< chk_duration_unit >------------------------|
797 -- ----------------------------------------------------------------------------
798 Procedure chk_duration_unit
799 (p_nota_history_id in number
800 ,p_duration_units in varchar2
801 ,p_effective_date in date) is
802
803 --
804 l_proc varchar2(72) := g_package||'chk_duration_units';
805 l_api_updating boolean;
806
807 begin
808 hr_utility.set_location(' Leaving:'||l_proc, 10);
809 --
810 -- check mandatory parameters has been set
811 --
812 hr_api.mandatory_arg_error
813 (p_api_name => l_proc
814 ,p_argument => 'effective_date'
815 ,p_argument_value => p_effective_date);
816
817
818 if (((p_nota_history_id is not null) and
819 nvl(ota_nhs_shd.g_old_rec.duration_units,hr_api.g_varchar2) <>
820 nvl(p_duration_units,hr_api.g_varchar2))
821 or
822 (p_nota_history_id is null)) then
823
824 hr_utility.set_location(' Leaving:'||l_proc, 20);
825 --
826 -- if status is not null then
827 -- check if the status value exists in hr_lookups
828 -- where lookup_type is 'FREQUENCY'
829 --
830 if p_duration_units is not null then
831 if hr_api.not_exists_in_hr_lookups -- Bug 2478551
832 (p_effective_date => p_effective_date
833 ,p_lookup_type => 'OTA_DURATION_UNITS'
834 ,p_lookup_code => p_duration_units) then
835 fnd_message.set_name('OTA','OTA_13882_NHS_DURATION_INVALID');
836 fnd_message.raise_error;
837 end if;
838 hr_utility.set_location(' Leaving:'||l_proc, 30);
839
840 end if;
841
842 end if;
843 hr_utility.set_location(' Leaving:'||l_proc, 40);
844
845 end chk_duration_unit;
846
847 -- ----------------------------------------------------------------------------
848 -- |---------------------------< chk_comb_duration >-------------------------|
849 -- ----------------------------------------------------------------------------
850 Procedure chk_comb_duration
851 (p_nota_history_id in number
852 ,p_duration in number
853 ,p_duration_units in varchar2
854 ,p_effective_date in date) is
855
856 --
857 l_proc varchar2(72) := g_package ||'chk_comb_duration';
858 l_api_updating boolean;
859
860 begin
861 hr_utility.set_location(' Leaving:'||l_proc, 10);
862 if (p_duration is not null and
863 p_duration_units is null ) or
864 (p_duration is null and
865 p_duration_units is not null ) then
866 fnd_message.set_name('OTA','OTA_13881_NHS_COMB_INVALID');
867 fnd_message.raise_error;
868
869 end if;
870 hr_utility.set_location(' Leaving:'||l_proc, 20);
871
872 end chk_comb_duration;
873
874 -- |---------------------------< get_profile_value >------------------------|
875 -- ----------------------------------------------------------------------------
876 -- ----------------------------------------------------------------------------
877 -- {Start Of Comments}
878 --
879 -- Description:
880 -- Get Cross Business Group profile and Single Business Group profile
881 --
882 -- Prerequisites:
883 -- None
884 --
885 -- In Arguments:
886 -- None
887 --
888 -- out Arguments:
889 -- p_cross_business_group
890 -- p_single_busines_group_id
891 --
892 -- Post Success:
893 -- No error.
894 --
895 -- Post Failure:
896 -- An error is raised if the only one has value.
897 --
898 -- Access Status:
899 -- Internal Development Use Only.
900 --
901 -- {End Of Comments}
902 -- ---------------------------------------------------------------------------
903 Procedure get_profile_value
904 (p_cross_business_group out nocopy varchar2
905 ,p_single_business_group_id out nocopy varchar2)
906 IS
907
908 BEGIN
909 p_cross_business_group := FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP');
910 p_single_business_group_id := FND_PROFILE.VALUE('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
911 END get_profile_value;
912
913
914 --
915 -- ----------------------------------------------------------------------------
916 -- |---------------------------< insert_validate >----------------------------|
917 -- ----------------------------------------------------------------------------
918 Procedure insert_validate(p_effective_date in date,
919 p_rec in ota_nhs_shd.g_rec_type) is
920 --
921 l_proc varchar2(72) := g_package||'insert_validate';
922 --
923 Begin
924 hr_utility.set_location('Entering:'||l_proc, 5);
925 --
926 -- Call all supporting business operations
927 --
928 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
929 --
930
931 chk_organization_id(p_rec.nota_history_id
932 ,p_rec.organization_id
933 ,p_rec.business_group_id
934 ,p_effective_date);
935
936 chk_customer_id(p_rec.nota_history_id
937 ,p_rec.customer_id
938 ,p_effective_date);
939
940 chk_person_id (p_rec.nota_history_id
941 ,p_rec.customer_id
942 ,p_rec.organization_id
943 ,p_rec.person_id
944 ,p_rec.business_group_id
945 ,p_effective_date);
946
947 chk_contact_id (p_rec.nota_history_id
948 ,p_rec.customer_id
949 ,p_rec.organization_id
950 ,p_rec.contact_id
951 ,p_effective_date);
952
953 chk_status(p_rec.nota_history_id
954 ,p_rec.status
955 ,p_effective_date);
956
957 chk_type(p_rec.nota_history_id
958 ,p_rec.type
959 ,p_effective_date);
960
961 chk_duration_unit(p_rec.nota_history_id
962 ,p_rec.duration_units
963 ,p_effective_date);
964
965 chk_comb_duration(p_rec.nota_history_id
966 ,p_rec.duration
967 ,p_rec.duration_units
968 ,p_effective_date);
969
970
971 chk_df(p_rec);
972
973 hr_utility.set_location(' Leaving:'||l_proc, 10);
974 End insert_validate;
975 --
976 -- ----------------------------------------------------------------------------
977 -- |---------------------------< update_validate >----------------------------|
978 -- ----------------------------------------------------------------------------
979 Procedure update_validate(p_effective_date in date,
980 p_rec in ota_nhs_shd.g_rec_type) is
981 --
982 l_proc varchar2(72) := g_package||'update_validate';
983 --
984 Begin
985 hr_utility.set_location('Entering:'||l_proc, 5);
986 --
987 -- Call all supporting business operations
988 --
989 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
990 --
991
992 chk_non_updateable_args
993 (p_effective_date => p_effective_date
994 ,p_rec => p_rec
995 );
996
997 chk_organization_id(p_rec.nota_history_id
998 ,p_rec.organization_id
999 ,p_rec.business_group_id
1000 ,p_effective_date);
1001
1002 chk_customer_id(p_rec.nota_history_id
1003 ,p_rec.customer_id
1004 ,p_effective_date);
1005
1006 chk_person_id(p_rec.nota_history_id
1007 ,p_rec.customer_id
1008 ,p_rec.organization_id
1009 ,p_rec.person_id
1010 ,p_rec.business_group_id
1011 ,p_effective_date);
1012
1013 chk_contact_id(p_rec.nota_history_id
1014 ,p_rec.customer_id
1015 ,p_rec.organization_id
1016 ,p_rec.contact_id
1017 ,p_effective_date);
1018
1019 chk_status(p_rec.nota_history_id
1020 ,p_rec.status
1021 ,p_effective_date);
1022
1023 chk_type(p_rec.nota_history_id
1024 ,p_rec.type
1025 ,p_effective_date);
1026
1027 chk_duration_unit(p_rec.nota_history_id
1028 ,p_rec.duration_units
1029 ,p_effective_date);
1030
1031 chk_comb_duration(p_rec.nota_history_id
1032 ,p_rec.duration
1033 ,p_rec.duration_units
1034 ,p_effective_date);
1035
1036
1037 --
1038 chk_df(p_rec);
1039
1040 hr_utility.set_location(' Leaving:'||l_proc, 10);
1041 End update_validate;
1042 --
1043 -- ----------------------------------------------------------------------------
1044 -- |---------------------------< delete_validate >----------------------------|
1045 -- ----------------------------------------------------------------------------
1046 Procedure delete_validate(p_rec in ota_nhs_shd.g_rec_type) is
1047 --
1048 l_proc varchar2(72) := g_package||'delete_validate';
1049 --
1050 Begin
1051 hr_utility.set_location('Entering:'||l_proc, 5);
1052 --
1053 -- Call all supporting business operations
1054 --
1055 hr_utility.set_location(' Leaving:'||l_proc, 10);
1056 End delete_validate;
1057 --
1058 end ota_nhs_bus;
[Home] [Help]