[Home] [Help]
PACKAGE BODY: APPS.OTA_NHS_INS
Source
1 Package Body ota_nhs_ins as
2 /* $Header: otnhsrhi.pkb 120.2 2011/06/22 09:15:20 smahanka ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_nhs_ins.'; -- Global package name
9 --
10 --
11 -- The following global variables are only to be used by
12 -- the set_base_key_value and pre_insert procedures.
13 --
14 g_nota_history_id_i number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |------------------------< set_base_key_value >----------------------------|
18 -- ----------------------------------------------------------------------------
19 procedure set_base_key_value
20 (p_nota_history_id in number) is
21 --
22 l_proc varchar2(72) := g_package||'set_base_key_value';
23 --
24 Begin
25 hr_utility.set_location('Entering:'||l_proc, 10);
26 --
27 ota_nhs_ins.g_nota_history_id_i := p_nota_history_id;
28 --
29 hr_utility.set_location(' Leaving:'||l_proc, 20);
30 End set_base_key_value;
31 --
32 --
33
34 -- ----------------------------------------------------------------------------
35 -- |------------------------------< insert_dml >------------------------------|
36 -- ----------------------------------------------------------------------------
37 -- {Start Of Comments}
38 --
39 -- Description:
40 -- This procedure controls the actual dml insert logic. The processing of
41 -- this procedure are as follows:
42 -- 1) Initialise the object_version_number to 1 if the object_version_number
43 -- is defined as an attribute for this entity.
44 -- 2) To set and unset the g_api_dml status as required (as we are about to
45 -- perform dml).
46 -- 3) To insert the row into the schema.
47 -- 4) To trap any constraint violations that may have occurred.
48 -- 5) To raise any other errors.
49 --
50 -- Prerequisites:
51 -- This is an internal private procedure which must be called from the ins
52 -- procedure and must have all mandatory attributes set (except the
53 -- object_version_number which is initialised within this procedure).
54 --
55 -- In Parameters:
56 -- A Pl/Sql record structre.
57 --
58 -- Post Success:
59 -- The specified row will be inserted into the schema.
60 --
61 -- Post Failure:
62 -- On the insert dml failure it is important to note that we always reset the
63 -- g_api_dml status to false.
64 -- If a check, unique or parent integrity constraint violation is raised the
65 -- constraint_error procedure will be called.
66 -- If any other error is reported, the error will be raised after the
67 -- g_api_dml status is reset.
68 --
69 -- Developer Implementation Notes:
70 -- None.
71 --
72 -- Access Status:
73 -- Internal Row Handler Use Only.
74 --
75 -- {End Of Comments}
76 -- ----------------------------------------------------------------------------
77 Procedure insert_dml(p_rec in out nocopy ota_nhs_shd.g_rec_type) is
78 --
79 l_proc varchar2(72) := g_package||'insert_dml';
80 --
81 Begin
82 hr_utility.set_location('Entering:'||l_proc, 5);
83 p_rec.object_version_number := 1; -- Initialise the object version
84 --
85 ota_nhs_shd.g_api_dml := true; -- Set the api dml status
86 --
87 -- Insert the row into: ota_notrng_histories
88 --
89 insert into ota_notrng_histories
90 ( nota_history_id,
91 person_id,
92 contact_id,
93 trng_title,
94 provider,
95 type,
96 centre,
97 completion_date,
98 award,
99 rating,
100 duration,
101 duration_units,
102 activity_version_id,
103 status,
104 verified_by_id,
105 nth_information_category,
106 nth_information1,
107 nth_information2,
108 nth_information3,
109 nth_information4,
110 nth_information5,
111 nth_information6,
112 nth_information7,
113 nth_information8,
114 nth_information9,
115 nth_information10,
116 nth_information11,
117 nth_information12,
118 nth_information13,
119 nth_information15,
120 nth_information16,
121 nth_information17,
122 nth_information18,
123 nth_information19,
124 nth_information20,
125 org_id,
126 object_version_number,
127 business_group_id,
128 nth_information14,
129 customer_id,
130 organization_id
131 )
132 Values
133 ( p_rec.nota_history_id,
134 p_rec.person_id,
135 p_rec.contact_id,
136 p_rec.trng_title,
137 p_rec.provider,
138 p_rec.type,
139 p_rec.centre,
140 p_rec.completion_date,
141 p_rec.award,
142 p_rec.rating,
143 p_rec.duration,
144 p_rec.duration_units,
145 p_rec.activity_version_id,
146 p_rec.status,
147 p_rec.verified_by_id,
148 p_rec.nth_information_category,
149 p_rec.nth_information1,
150 p_rec.nth_information2,
151 p_rec.nth_information3,
152 p_rec.nth_information4,
153 p_rec.nth_information5,
154 p_rec.nth_information6,
155 p_rec.nth_information7,
156 p_rec.nth_information8,
157 p_rec.nth_information9,
158 p_rec.nth_information10,
159 p_rec.nth_information11,
160 p_rec.nth_information12,
161 p_rec.nth_information13,
162 p_rec.nth_information15,
163 p_rec.nth_information16,
164 p_rec.nth_information17,
165 p_rec.nth_information18,
166 p_rec.nth_information19,
167 p_rec.nth_information20,
168 p_rec.org_id,
169 p_rec.object_version_number,
170 p_rec.business_group_id,
171 p_rec.nth_information14,
172 p_rec.customer_id,
173 p_rec.organization_id
174 );
175 --
176 ota_nhs_shd.g_api_dml := false; -- Unset the api dml status
177 --
178 hr_utility.set_location(' Leaving:'||l_proc, 10);
179 Exception
180 When hr_api.check_integrity_violated Then
181 -- A check constraint has been violated
182 ota_nhs_shd.g_api_dml := false; -- Unset the api dml status
183 ota_nhs_shd.constraint_error
184 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
185 When hr_api.parent_integrity_violated Then
186 -- Parent integrity has been violated
187 ota_nhs_shd.g_api_dml := false; -- Unset the api dml status
188 ota_nhs_shd.constraint_error
189 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
190 When hr_api.unique_integrity_violated Then
191 -- Unique integrity has been violated
192 ota_nhs_shd.g_api_dml := false; -- Unset the api dml status
193 ota_nhs_shd.constraint_error
194 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
195 When Others Then
196 ota_nhs_shd.g_api_dml := false; -- Unset the api dml status
197 Raise;
198 End insert_dml;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |------------------------------< pre_insert >------------------------------|
202 -- ----------------------------------------------------------------------------
203 -- {Start Of Comments}
204 --
205 -- Description:
206 -- This private procedure contains any processing which is required before
207 -- the insert dml. Presently, if the entity has a corresponding primary
208 -- key which is maintained by an associating sequence, the primary key for
209 -- the entity will be populated with the next sequence value in
210 -- preparation for the insert dml.
211 --
212 -- Prerequisites:
213 -- This is an internal procedure which is called from the ins procedure.
214 --
215 -- In Parameters:
216 -- A Pl/Sql record structre.
217 --
218 -- Post Success:
219 -- Processing continues.
220 --
221 -- Post Failure:
222 -- If an error has occurred, an error message and exception will be raised
223 -- but not handled.
224 --
225 -- Developer Implementation Notes:
226 -- Any pre-processing required before the insert dml is issued should be
227 -- coded within this procedure. As stated above, a good example is the
228 -- generation of a primary key number via a corresponding sequence.
229 -- It is important to note that any 3rd party maintenance should be reviewed
230 -- before placing in this procedure.
231 --
232 -- Access Status:
233 -- Internal Row Handler Use Only.
234 --
235 -- {End Of Comments}
236 -- ----------------------------------------------------------------------------
237 Procedure pre_insert(p_rec in out nocopy ota_nhs_shd.g_rec_type) is
238 --
239 Cursor C_Sel1 is select ota_notrng_histories_s.nextval from sys.dual;
240 --
241 Cursor C_Sel2 is
242 Select null
243 from ota_notrng_histories
244 where nota_history_id =
245 ota_nhs_ins.g_nota_history_id_i;
246 --
247 l_exists varchar2(1);
248 l_proc varchar2(72) := g_package||'pre_insert';
249 --
250 Begin
251 hr_utility.set_location('Entering:'||l_proc, 5);
252 --
253 If (ota_nhs_ins.g_nota_history_id_i is not null) Then
254 --
255 -- Verify registered primary key values not already in use
256 --
257 Open C_Sel2;
258 Fetch C_Sel2 into l_exists;
259 If C_Sel2%found Then
260 Close C_Sel2;
261 --
262 -- The primary key values are already in use.
263 --
264 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
265 fnd_message.set_token('TABLE_NAME','ota_notrng_histories');
266 fnd_message.raise_error;
267 End If;
268 Close C_Sel2;
269 --
270 -- Use registered key values and clear globals
271 --
272 p_rec.nota_history_id :=
273 ota_nhs_ins.g_nota_history_id_i;
274 ota_nhs_ins.g_nota_history_id_i := null;
275 Else
276 --
277 -- No registerd key values, so select the next sequence number
278 --
279 -- Select the next sequence number
280 --
281 Open C_Sel1;
282 Fetch C_Sel1 Into p_rec.nota_history_id;
283 Close C_Sel1;
284 --
285 End If;
286 --
287 hr_utility.set_location(' Leaving:'||l_proc, 10);
288 End pre_insert;
289 --
290 -- ----------------------------------------------------------------------------
291 -- |-----------------------------< post_insert >------------------------------|
292 -- ----------------------------------------------------------------------------
293 -- {Start Of Comments}
294 --
295 -- Description:
296 -- This private procedure contains any processing which is required after the
297 -- insert dml.
298 --
299 -- Prerequisites:
300 -- This is an internal procedure which is called from the ins procedure.
301 --
302 -- In Parameters:
303 -- A Pl/Sql record structre.
304 --
305 -- Post Success:
306 -- Processing continues.
307 --
308 -- Post Failure:
309 -- If an error has occurred, an error message and exception will be raised
310 -- but not handled.
311 --
312 -- Developer Implementation Notes:
313 -- Any post-processing required after the insert dml is issued should be
314 -- coded within this procedure. It is important to note that any 3rd party
315 -- maintenance should be reviewed before placing in this procedure.
316 --
317 -- Access Status:
318 -- Internal Row Handler Use Only.
319 --
320 -- {End Of Comments}
321 -- ----------------------------------------------------------------------------
322 Procedure post_insert(p_effective_date in date,
323 p_rec in ota_nhs_shd.g_rec_type) is
324 --
325 l_proc varchar2(72) := g_package||'post_insert';
326 --
327 Begin
328 hr_utility.set_location('Entering:'||l_proc, 5);
329 --
330 begin
331 --
332 ota_nhs_rki.after_insert
333 (p_effective_date => p_effective_date
334 ,p_nota_history_id
335 => p_rec.nota_history_id
336 ,p_person_id
337 => p_rec.person_id
338 ,p_contact_id
339 => p_rec.contact_id
340 ,p_trng_title
341 => p_rec.trng_title
342 ,p_provider
343 => p_rec.provider
344 ,p_type
345 => p_rec.type
346 ,p_centre
347 => p_rec.centre
348 ,p_completion_date
349 => p_rec.completion_date
350 ,p_award
351 => p_rec.award
352 ,p_rating
353 => p_rec.rating
354 ,p_duration
355 => p_rec.duration
356 ,p_duration_units
357 => p_rec.duration_units
358 ,p_activity_version_id
359 => p_rec.activity_version_id
360 ,p_status
361 => p_rec.status
362 ,p_verified_by_id
363 => p_rec.verified_by_id
364 ,p_nth_information_category
365 => p_rec.nth_information_category
366 ,p_nth_information1
367 => p_rec.nth_information1
368 ,p_nth_information2
369 => p_rec.nth_information2
370 ,p_nth_information3
371 => p_rec.nth_information3
372 ,p_nth_information4
373 => p_rec.nth_information4
374 ,p_nth_information5
375 => p_rec.nth_information5
376 ,p_nth_information6
377 => p_rec.nth_information6
378 ,p_nth_information7
379 => p_rec.nth_information7
380 ,p_nth_information8
381 => p_rec.nth_information8
382 ,p_nth_information9
383 => p_rec.nth_information9
384 ,p_nth_information10
385 => p_rec.nth_information10
386 ,p_nth_information11
387 => p_rec.nth_information11
388 ,p_nth_information12
389 => p_rec.nth_information12
390 ,p_nth_information13
391 => p_rec.nth_information13
392 ,p_nth_information15
393 => p_rec.nth_information15
394 ,p_nth_information16
395 => p_rec.nth_information16
396 ,p_nth_information17
397 => p_rec.nth_information17
398 ,p_nth_information18
399 => p_rec.nth_information18
400 ,p_nth_information19
401 => p_rec.nth_information19
402 ,p_nth_information20
403 => p_rec.nth_information20
404 ,p_org_id
405 => p_rec.org_id
406 ,p_object_version_number
407 => p_rec.object_version_number
408 ,p_business_group_id
409 => p_rec.business_group_id
410 ,p_nth_information14
411 => p_rec.nth_information14
412 ,p_customer_id
413 => p_rec.customer_id
414 ,p_organization_id
415 => p_rec.organization_id
416 );
417 --
418 exception
419 --
420 when hr_api.cannot_find_prog_unit then
421 --
422 hr_api.cannot_find_prog_unit_error
423 (p_module_name => 'OTA_NOTRNG_HISTORIES'
424 ,p_hook_type => 'AI');
425 --
426 end;
427
428 hr_utility.set_location(' Leaving:'||l_proc, 10);
429 End post_insert;
430 --
431 -- ----------------------------------------------------------------------------
432 -- |---------------------------------< ins >----------------------------------|
433 -- ----------------------------------------------------------------------------
434 Procedure ins
435 (p_effective_date in date,
436 p_rec in out nocopy ota_nhs_shd.g_rec_type
437 ) is
438 --
439 l_proc varchar2(72) := g_package||'ins';
440 --
441 Begin
442 hr_utility.set_location('Entering:'||l_proc, 5);
443 --
444 -- Call the supporting insert validate operations
445 --
446 ota_nhs_bus.insert_validate(p_effective_date ,
447 p_rec);
448 --
449 -- Call the supporting pre-insert operation
450 --
451 pre_insert(p_rec);
452 --
453 -- Insert the row
454 --
455 insert_dml(p_rec);
456 --
457 -- Call the supporting post-insert operation
458 --
459 post_insert(p_effective_date,
460 p_rec);
461 end ins;
462 --
463 -- ----------------------------------------------------------------------------
464 -- |---------------------------------< ins >----------------------------------|
465 -- ----------------------------------------------------------------------------
466 Procedure ins
467 (p_effective_date in date ,
468 p_nota_history_id out nocopy number,
469 p_person_id in number,
470 p_contact_id in number default null,
471 p_trng_title in varchar2,
472 p_provider in varchar2 default null,
473 p_type in varchar2 default null,
474 p_centre in varchar2 default null,
475 p_completion_date in date,
476 p_award in varchar2 default null,
477 p_rating in varchar2 default null,
478 p_duration in number default null,
479 p_duration_units in varchar2 default null,
480 p_activity_version_id in number default null,
481 p_status in varchar2,
482 p_verified_by_id in number default null,
483 p_nth_information_category in varchar2 default null,
484 p_nth_information1 in varchar2 default null,
485 p_nth_information2 in varchar2 default null,
486 p_nth_information3 in varchar2 default null,
487 p_nth_information4 in varchar2 default null,
488 p_nth_information5 in varchar2 default null,
489 p_nth_information6 in varchar2 default null,
490 p_nth_information7 in varchar2 default null,
491 p_nth_information8 in varchar2 default null,
492 p_nth_information9 in varchar2 default null,
493 p_nth_information10 in varchar2 default null,
494 p_nth_information11 in varchar2 default null,
495 p_nth_information12 in varchar2 default null,
496 p_nth_information13 in varchar2 default null,
497 p_nth_information15 in varchar2 default null,
498 p_nth_information16 in varchar2 default null,
499 p_nth_information17 in varchar2 default null,
500 p_nth_information18 in varchar2 default null,
501 p_nth_information19 in varchar2 default null,
502 p_nth_information20 in varchar2 default null,
503 p_org_id in number default null,
504 p_object_version_number out nocopy number,
505 p_business_group_id in number,
506 p_nth_information14 in varchar2 default null,
507 p_customer_id in number default null,
508 p_organization_id in number default null
509 ) is
510 --
511 l_rec ota_nhs_shd.g_rec_type;
512 l_proc varchar2(72) := g_package||'ins';
513 --
514 Begin
515 hr_utility.set_location('Entering:'||l_proc, 5);
516 --
517 -- Call conversion function to turn arguments into the
518 -- p_rec structure.
519 --
520 l_rec :=
521 ota_nhs_shd.convert_args
522 (
523 null,
524 p_person_id,
525 p_contact_id,
526 p_trng_title,
527 p_provider,
528 p_type,
529 p_centre,
530 p_completion_date,
531 p_award,
532 p_rating,
533 p_duration,
534 p_duration_units,
535 p_activity_version_id,
536 p_status,
537 p_verified_by_id,
538 p_nth_information_category,
539 p_nth_information1,
540 p_nth_information2,
541 p_nth_information3,
542 p_nth_information4,
543 p_nth_information5,
544 p_nth_information6,
545 p_nth_information7,
546 p_nth_information8,
547 p_nth_information9,
548 p_nth_information10,
549 p_nth_information11,
550 p_nth_information12,
551 p_nth_information13,
552 p_nth_information15,
553 p_nth_information16,
554 p_nth_information17,
555 p_nth_information18,
556 p_nth_information19,
557 p_nth_information20,
558 p_org_id,
559 null,
560 p_business_group_id,
561 p_nth_information14,
562 p_customer_id,
563 p_organization_id
564 );
565 --
566 -- Having converted the arguments into the ota_nhs_rec
567 -- plsql record structure we call the corresponding record business process.
568 --
569 ins(p_effective_date ,
570 l_rec);
571 --
572 -- As the primary key argument(s)
573 -- are specified as an OUT's we must set these values.
574 --
575 p_nota_history_id := l_rec.nota_history_id;
576 p_object_version_number := l_rec.object_version_number;
577 --
578 hr_utility.set_location(' Leaving:'||l_proc, 10);
579 End ins;
580 --
581 end ota_nhs_ins;