1 Package Body ota_tfl_api_ins as
2 /* $Header: ottfl01t.pkb 120.0 2005/05/29 07:41:43 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tfl_api_ins.'; -- Global package name
9 --
13 g_finance_line_id_i number default null;
10 -- The following global variables are only to be used by
11 -- the set_base_key_value and pre_insert procedures.
12 --
14
15 -- ----------------------------------------------------------------------------
16 -- |------------------------< set_base_key_value >----------------------------|
17 -- ----------------------------------------------------------------------------
18 procedure set_base_key_value
19 (finance_line_id in number) is
20 --
21 l_proc varchar2(72) := g_package||'set_base_key_value';
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 10);
25 --
26 ota_tfl_api_ins.g_finance_line_id_i := finance_line_id;
27 --
28 hr_utility.set_location(' Leaving:'||l_proc, 20);
29 End set_base_key_value;
30
31 --
32 -- ----------------------------------------------------------------------------
33 -- |------------------------------< insert_dml >------------------------------|
34 -- ----------------------------------------------------------------------------
35 -- {Start Of Comments}
36 --
37 -- Description:
38 -- This procedure controls the actual dml insert logic. The functions of this
39 -- procedure are as follows:
40 -- 1) Initialise the object_version_number to 1 if the object_version_number
41 -- is defined as an attribute for this entity.
42 -- 2) To set and unset the g_api_dml status as required (as we are about to
43 -- perform dml).
44 -- 3) To insert the row into the schema.
45 -- 4) To trap any constraint violations that may have occurred.
46 -- 5) To raise any other errors.
47 --
48 -- Pre Conditions:
49 -- This is an internal private procedure which must be called from the ins
50 -- procedure and must have all mandatory arguments set (except the
51 -- object_version_number which is initialised within this procedure).
52 --
53 -- In Arguments:
54 -- A Pl/Sql record structre.
55 --
56 -- Post Success:
57 -- The specified row will be inserted into the schema.
58 --
59 -- Post Failure:
60 -- On the insert dml failure it is important to note that we always reset the
61 -- g_api_dml status to false.
62 -- If a check, unique or parent integrity constraint violation is raised the
63 -- constraint_error procedure will be called.
64 -- If any other error is reported, the error will be raised after the
65 -- g_api_dml status is reset.
66 --
67 -- Developer Implementation Notes:
68 -- None.
69 --
70 -- Access Status:
71 -- Internal Development Use Only.
72 --
73 -- {End Of Comments}
74 -- ----------------------------------------------------------------------------
75 Procedure insert_dml(p_rec in out nocopy ota_tfl_api_shd.g_rec_type) is
76 --
77 l_proc varchar2(72) := g_package||'insert_dml';
78 --
79 Begin
80 hr_utility.set_location('Entering:'||l_proc, 5);
81 p_rec.object_version_number := 1; -- Initialise the object version
82 --
83 ota_tfl_api_shd.g_api_dml := true; -- Set the api dml status
84 --
85 -- Insert the row into: ota_finance_lines
86 --
87 insert into ota_finance_lines
88 ( finance_line_id,
89 finance_header_id,
90 cancelled_flag,
91 date_raised,
92 line_type,
93 object_version_number,
94 sequence_number,
95 transfer_status,
96 comments,
97 currency_code,
98 money_amount,
99 standard_amount,
100 trans_information_category,
101 trans_information1,
102 trans_information10,
103 trans_information11,
104 trans_information12,
105 trans_information13,
106 trans_information14,
107 trans_information15,
108 trans_information16,
109 trans_information17,
110 trans_information18,
111 trans_information19,
112 trans_information2,
113 trans_information20,
114 trans_information3,
115 trans_information4,
116 trans_information5,
117 trans_information6,
118 trans_information7,
119 trans_information8,
120 trans_information9,
121 transfer_date,
122 transfer_message,
123 unitary_amount,
124 booking_deal_id,
125 booking_id,
126 resource_allocation_id,
127 resource_booking_id,
128 tfl_information_category,
129 tfl_information1,
130 tfl_information2,
131 tfl_information3,
132 tfl_information4,
133 tfl_information5,
134 tfl_information6,
135 tfl_information7,
136 tfl_information8,
137 tfl_information9,
138 tfl_information10,
139 tfl_information11,
140 tfl_information12,
141 tfl_information13,
142 tfl_information14,
143 tfl_information15,
144 tfl_information16,
145 tfl_information17,
146 tfl_information18,
147 tfl_information19,
148 tfl_information20
149 )
150 Values
151 ( p_rec.finance_line_id,
152 p_rec.finance_header_id,
153 p_rec.cancelled_flag,
154 p_rec.date_raised,
155 p_rec.line_type,
156 p_rec.object_version_number,
157 p_rec.sequence_number,
158 p_rec.transfer_status,
159 p_rec.comments,
160 p_rec.currency_code,
161 p_rec.money_amount,
162 p_rec.standard_amount,
163 p_rec.trans_information_category,
164 p_rec.trans_information1,
165 p_rec.trans_information10,
166 p_rec.trans_information11,
167 p_rec.trans_information12,
168 p_rec.trans_information13,
169 p_rec.trans_information14,
170 p_rec.trans_information15,
171 p_rec.trans_information16,
172 p_rec.trans_information17,
173 p_rec.trans_information18,
177 p_rec.trans_information3,
174 p_rec.trans_information19,
175 p_rec.trans_information2,
176 p_rec.trans_information20,
178 p_rec.trans_information4,
179 p_rec.trans_information5,
180 p_rec.trans_information6,
181 p_rec.trans_information7,
182 p_rec.trans_information8,
183 p_rec.trans_information9,
184 p_rec.transfer_date,
185 p_rec.transfer_message,
186 p_rec.unitary_amount,
187 p_rec.booking_deal_id,
188 p_rec.booking_id,
189 p_rec.resource_allocation_id,
190 p_rec.resource_booking_id,
191 p_rec.tfl_information_category,
192 p_rec.tfl_information1,
193 p_rec.tfl_information2,
194 p_rec.tfl_information3,
195 p_rec.tfl_information4,
196 p_rec.tfl_information5,
197 p_rec.tfl_information6,
198 p_rec.tfl_information7,
199 p_rec.tfl_information8,
200 p_rec.tfl_information9,
201 p_rec.tfl_information10,
202 p_rec.tfl_information11,
203 p_rec.tfl_information12,
204 p_rec.tfl_information13,
205 p_rec.tfl_information14,
206 p_rec.tfl_information15,
207 p_rec.tfl_information16,
208 p_rec.tfl_information17,
209 p_rec.tfl_information18,
210 p_rec.tfl_information19,
211 p_rec.tfl_information20
212 );
213 --
214 ota_tfl_api_shd.g_api_dml := false; -- Unset the api dml status
215 --
216 hr_utility.set_location(' Leaving:'||l_proc, 10);
217 Exception
218 When hr_api.check_integrity_violated Then
219 -- A check constraint has been violated
220 ota_tfl_api_shd.g_api_dml := false; -- Unset the api dml status
221 ota_tfl_api_shd.constraint_error
222 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
223 When hr_api.parent_integrity_violated Then
224 -- Parent integrity has been violated
225 ota_tfl_api_shd.g_api_dml := false; -- Unset the api dml status
226 ota_tfl_api_shd.constraint_error
227 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
228 When hr_api.unique_integrity_violated Then
229 -- Unique integrity has been violated
230 ota_tfl_api_shd.g_api_dml := false; -- Unset the api dml status
231 ota_tfl_api_shd.constraint_error
232 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
233 When Others Then
234 ota_tfl_api_shd.g_api_dml := false; -- Unset the api dml status
235 Raise;
236 End insert_dml;
237 --
238 -- ----------------------------------------------------------------------------
239 -- |------------------------------< pre_insert >------------------------------|
240 -- ----------------------------------------------------------------------------
241 -- {Start Of Comments}
242 --
243 -- Description:
244 -- This private procedure contains any processing which is required before
245 -- the insert dml. Presently, if the entity has a corresponding primary
246 -- key which is maintained by an associating sequence, the primary key for
247 -- the entity will be populated with the next sequence value in
248 -- preparation for the insert dml.
249 --
250 -- Pre Conditions:
251 -- This is an internal procedure which is called from the ins procedure.
252 --
253 -- In Arguments:
254 -- A Pl/Sql record structre.
255 --
256 -- Post Success:
257 -- Processing continues.
258 --
259 -- Post Failure:
260 -- If an error has occurred, an error message and exception will be raised
261 -- but not handled.
262 --
263 -- Developer Implementation Notes:
264 -- Any pre-processing required before the insert dml is issued should be
265 -- coded within this procedure. As stated above, a good example is the
266 -- generation of a primary key number via a corresponding sequence.
267 -- It is important to note that any 3rd party maintenance should be reviewed
268 -- before placing in this procedure.
269 --
270 -- Access Status:
271 -- Internal Development Use Only.
272 --
273 -- {End Of Comments}
274 -- ----------------------------------------------------------------------------
275 Procedure pre_insert(p_rec in out nocopy ota_tfl_api_shd.g_rec_type) is
276 --
277 l_proc varchar2(72) := g_package||'pre_insert';
278 --
279 Cursor C_Sel1 is select ota_finance_lines_s.nextval from sys.dual;
280 --
281
282 --
283 Cursor C_Sel2 is
284 Select null
285 from ota_finance_lines
286 where finance_line_id =
287 ota_tfl_api_ins.g_finance_line_id_i;
288 --
289
290 l_exists varchar2(1);
291
292 Begin
293 hr_utility.set_location('Entering:'||l_proc, 5);
294 --
295 If (ota_tfl_api_ins.g_finance_line_id_i is not null) Then
296 --
297 -- Verify registered primary key values not already in use
298 --
299 Open C_Sel2;
300 Fetch C_Sel2 into l_exists;
301 If C_Sel2%found Then
302 Close C_Sel2;
303 --
304 -- The primary key values are already in use.
305 --
306 fnd_message.set_name('PER','PER_289391_KEY_ALREADY_USED');
307 fnd_message.set_token('TABLE_NAME','ota_finance_lines');
308 fnd_message.raise_error;
309 End If;
310 Close C_Sel2;
311 --
312 -- Use registered key values and clear globals
313 --
314 p_rec.finance_line_id :=
315 ota_tfl_api_ins.g_finance_line_id_i;
316 ota_tfl_api_ins.g_finance_line_id_i := null;
317 Else
318 --
319 -- No registerd key values, so select the next sequence number
320 --
321 --
322 -- Select the next sequence number
323 --
324
325 Open C_Sel1;
329 --
326 Fetch C_Sel1 Into p_rec.finance_line_id;
327 Close C_Sel1;
328 End if;
330 hr_utility.set_location(' Leaving:'||l_proc, 10);
331 End pre_insert;
332
333 -- ----------------------------------------------------------------------------
334 -- |-----------------------------< post_insert >------------------------------|
335 -- ----------------------------------------------------------------------------
336 -- {Start Of Comments}
337 --
338 -- Description:
339 -- This private procedure contains any processing which is required after the
340 -- insert dml.
341 --
342 -- Pre Conditions:
343 -- This is an internal procedure which is called from the ins procedure.
344 --
345 -- In Arguments:
346 -- A Pl/Sql record structre.
347 --
348 -- Post Success:
349 -- Processing continues.
350 --
351 -- Post Failure:
352 -- If an error has occurred, an error message and exception will be raised
353 -- but not handled.
354 --
355 -- Developer Implementation Notes:
356 -- Any post-processing required after the insert dml is issued should be
357 -- coded within this procedure. It is important to note that any 3rd party
358 -- maintenance should be reviewed before placing in this procedure.
359 --
360 -- Access Status:
361 -- Internal Development Use Only.
362 --
363 -- {End Of Comments}
364 -- ----------------------------------------------------------------------------
365 Procedure post_insert(p_rec in ota_tfl_api_shd.g_rec_type) is
366 --
367 l_proc varchar2(72) := g_package||'post_insert';
368 --
369 Begin
370 hr_utility.set_location('Entering:'||l_proc, 5);
371 --
372 hr_utility.set_location(' Leaving:'||l_proc, 10);
373 End post_insert;
374 --
375 -- ----------------------------------------------------------------------------
376 -- |---------------------------------< ins >----------------------------------|
377 -- ----------------------------------------------------------------------------
378 Procedure ins
379 (
380 p_rec in out nocopy ota_tfl_api_shd.g_rec_type,
381 p_validate in boolean default false,
382 p_transaction_type in varchar2
383 ) is
384 --
385 l_proc varchar2(72) := g_package||'ins';
386 --
387 Begin
388 hr_utility.set_location('Entering:'||l_proc, 5);
389 --
390 -- Determine if the business process is to be validated.
391 --
392 If p_validate then
393 --
394 -- Issue the savepoint.
395 --
396 SAVEPOINT ins_ota_tfl_api;
397 End If;
398 --
399 -- Call the supporting insert validate operations
400 --
401 ota_tfl_api_bus.insert_validate( p_rec, p_transaction_type);
402 --
403 -- Call the supporting pre-insert operation
404 --
405 pre_insert(p_rec);
406 --
407 -- Insert the row
408 --
409 insert_dml(p_rec);
410 --
411 -- Call the supporting post-insert operation
412 --
413 post_insert(p_rec);
414 --
415 -- If we are validating then raise the Validate_Enabled exception
416 --
417 If p_validate then
418 Raise HR_Api.Validate_Enabled;
419 End If;
420 --
421 hr_utility.set_location(' Leaving:'||l_proc, 10);
422 Exception
423 When HR_Api.Validate_Enabled Then
424 --
425 -- As the Validate_Enabled exception has been raised
426 -- we must rollback to the savepoint
427 --
428 ROLLBACK TO ins_ota_tfl_api;
429 end ins;
430 --
431 -- ----------------------------------------------------------------------------
432 -- |---------------------------------< ins >----------------------------------|
433 -- ----------------------------------------------------------------------------
434 Procedure ins
435 (
436 p_finance_line_id out nocopy number,
437 p_finance_header_id in number default null,
438 p_cancelled_flag in varchar2,
439 p_date_raised in out nocopy date,
440 p_line_type in varchar2,
441 p_object_version_number out nocopy number,
442 p_sequence_number in out nocopy number,
443 p_transfer_status in varchar2,
444 p_comments in varchar2 default null,
445 p_currency_code in varchar2 default null,
446 p_money_amount in number default null,
447 p_standard_amount in number default null,
448 p_trans_information_category in varchar2 default null,
449 p_trans_information1 in varchar2 default null,
450 p_trans_information10 in varchar2 default null,
451 p_trans_information11 in varchar2 default null,
452 p_trans_information12 in varchar2 default null,
453 p_trans_information13 in varchar2 default null,
454 p_trans_information14 in varchar2 default null,
455 p_trans_information15 in varchar2 default null,
456 p_trans_information16 in varchar2 default null,
457 p_trans_information17 in varchar2 default null,
458 p_trans_information18 in varchar2 default null,
459 p_trans_information19 in varchar2 default null,
460 p_trans_information2 in varchar2 default null,
461 p_trans_information20 in varchar2 default null,
462 p_trans_information3 in varchar2 default null,
463 p_trans_information4 in varchar2 default null,
464 p_trans_information5 in varchar2 default null,
465 p_trans_information6 in varchar2 default null,
466 p_trans_information7 in varchar2 default null,
467 p_trans_information8 in varchar2 default null,
468 p_trans_information9 in varchar2 default null,
469 p_transfer_date in date default null,
470 p_transfer_message in varchar2 default null,
471 p_unitary_amount in number default null,
472 p_booking_deal_id in number default null,
473 p_booking_id in number default null,
474 p_resource_allocation_id in number default null,
475 p_resource_booking_id in number default null,
476 p_tfl_information_category in varchar2 default null,
477 p_tfl_information1 in varchar2 default null,
478 p_tfl_information2 in varchar2 default null,
479 p_tfl_information3 in varchar2 default null,
480 p_tfl_information4 in varchar2 default null,
481 p_tfl_information5 in varchar2 default null,
482 p_tfl_information6 in varchar2 default null,
483 p_tfl_information7 in varchar2 default null,
484 p_tfl_information8 in varchar2 default null,
485 p_tfl_information9 in varchar2 default null,
486 p_tfl_information10 in varchar2 default null,
487 p_tfl_information11 in varchar2 default null,
488 p_tfl_information12 in varchar2 default null,
489 p_tfl_information13 in varchar2 default null,
490 p_tfl_information14 in varchar2 default null,
491 p_tfl_information15 in varchar2 default null,
492 p_tfl_information16 in varchar2 default null,
493 p_tfl_information17 in varchar2 default null,
494 p_tfl_information18 in varchar2 default null,
495 p_tfl_information19 in varchar2 default null,
496 p_tfl_information20 in varchar2 default null,
497 p_validate in boolean default false,
498 p_transaction_type in varchar2
499 ) is
500 --
501 l_rec ota_tfl_api_shd.g_rec_type;
502 l_proc varchar2(72) := g_package||'ins';
503 --
504 Begin
505 hr_utility.set_location('Entering:'||l_proc, 5);
506 --
507 -- Call conversion function to turn arguments into the
508 -- p_rec structure.
509 --
510 l_rec :=
511 ota_tfl_api_shd.convert_args
512 (
513 null,
514 p_finance_header_id,
515 p_cancelled_flag,
516 p_date_raised,
517 p_line_type,
518 null,
519 p_sequence_number,
520 p_transfer_status,
521 p_comments,
522 p_currency_code,
523 p_money_amount,
524 p_standard_amount,
525 p_trans_information_category,
526 p_trans_information1,
527 p_trans_information10,
528 p_trans_information11,
529 p_trans_information12,
530 p_trans_information13,
531 p_trans_information14,
532 p_trans_information15,
533 p_trans_information16,
534 p_trans_information17,
535 p_trans_information18,
536 p_trans_information19,
537 p_trans_information2,
538 p_trans_information20,
539 p_trans_information3,
540 p_trans_information4,
541 p_trans_information5,
542 p_trans_information6,
543 p_trans_information7,
544 p_trans_information8,
545 p_trans_information9,
546 p_transfer_date,
547 p_transfer_message,
548 p_unitary_amount,
549 p_booking_deal_id,
550 p_booking_id,
551 p_resource_allocation_id,
552 p_resource_booking_id,
553 p_tfl_information_category,
554 p_tfl_information1,
555 p_tfl_information2,
556 p_tfl_information3,
557 p_tfl_information4,
558 p_tfl_information5,
559 p_tfl_information6,
560 p_tfl_information7,
561 p_tfl_information8,
562 p_tfl_information9,
563 p_tfl_information10,
564 p_tfl_information11,
565 p_tfl_information12,
566 p_tfl_information13,
567 p_tfl_information14,
568 p_tfl_information15,
569 p_tfl_information16,
570 p_tfl_information17,
571 p_tfl_information18,
572 p_tfl_information19,
573 p_tfl_information20
574 );
575 --
576 -- Having converted the arguments into the ota_tfl_api_rec
577 -- plsql record structure we call the corresponding record business process.
578 --
579 ins( l_rec, p_validate, p_transaction_type);
580 --
581 -- As the primary key argument(s)
582 -- are specified as an OUT's we must set these values.
583 --
584 p_finance_line_id := l_rec.finance_line_id;
585 p_sequence_number := l_rec.sequence_number;
586 p_object_version_number := l_rec.object_version_number;
587 --
588 hr_utility.set_location(' Leaving:'||l_proc, 10);
589 End ins;
590 --
591 end ota_tfl_api_ins;