1 PACKAGE BODY pay_exa_ins AS
2 /* $Header: pyexarhi.pkb 115.13 2003/09/26 06:48:50 tvankayl ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_exa_ins.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< insert_dml >------------------------------|
12 -- ----------------------------------------------------------------------------
13 -- {Start Of Comments}
14 --
15 -- Description:
16 -- This procedure controls the actual dml insert logic. The functions of this
17 -- procedure are as follows:
18 -- 1) Initialise the object_version_number to 1 if the object_version_number
19 -- is defined as an attribute for this entity.
20 -- 2) To set and unset the g_api_dml status as required (as we are about to
21 -- perform dml).
22 -- 3) To insert the row into the schema.
23 -- 4) To trap any constraint violations that may have occurred.
24 -- 5) To raise any other errors.
25 --
26 -- Pre Conditions:
27 -- This is an internal private procedure which must be called from the ins
28 -- procedure and must have all mandatory arguments set (except the
29 -- object_version_number which is initialised within this procedure).
30 --
31 -- In Arguments:
32 -- A Pl/Sql record structre.
33 --
34 -- Post Success:
35 -- The specified row will be inserted into the schema.
36 --
37 -- Post Failure:
38 -- On the insert dml failure it is important to note that we always reset the
39 -- g_api_dml status to false.
40 -- If a check, unique or parent integrity constraint violation is raised the
41 -- constraint_error procedure will be called.
42 -- If any other error is reported, the error will be raised after the
43 -- g_api_dml status is reset.
44 --
45 -- Developer Implementation Notes:
46 -- None.
47 --
48 -- Access Status:
49 -- Internal Table Handler Use Only.
50 --
51 -- {End Of Comments}
52 -- ----------------------------------------------------------------------------
53 procedure insert_dml(
54 p_rec in out nocopy pay_exa_shd.g_rec_type,
55 p_business_group_id in number
56 ) is
57 --
58 l_proc varchar2(72) := g_package||'insert_dml';
59 --
60 begin
61 hr_utility.set_location('Entering:'||l_proc, 5);
62 --
63 pay_exa_shd.g_api_dml := true; -- set the api dml status
64
65 --
66 -- insert_validate(), chk_territory_code(), pay_exa_shd.api_updating()
67 -- has been called,
68 -- this selects a row into g_old_rec and returns true if the PK value
69 -- passed in matches a row on the db,
70 -- nb. no locking is done
71 --
72 pay_exa_shd.lck
73 (p_rec.external_account_id,
74 p_rec.object_version_number);
75 --
76 -- only do U if values have changed
77 --
78 if ( nvl(p_rec.territory_code, hr_api.g_varchar2) <>
79 nvl(pay_exa_shd.g_old_rec.territory_code, hr_api.g_varchar2) )
80 or
81 ( nvl(p_rec.prenote_date, hr_api.g_date) <>
82 nvl(pay_exa_shd.g_old_rec.prenote_date, hr_api.g_date) ) then
83 hr_utility.trace('| doing update on combination table');
84 --
85 -- fresh combination record
86 --
87 if ( pay_exa_shd.g_old_rec.territory_code is null ) then
88 hr_utility.trace('| updating territory_code');
89 --
90 UPDATE PAY_EXTERNAL_ACCOUNTS
91 SET territory_code = p_rec.territory_code
92 WHERE external_account_id = p_rec.external_account_id
93 ;
94 end if;
95 ------------------------------------------------------------------------
96 -- bug2307154 changes for prenote_date
97 ------------------------------------------------------------------------
98 --
99 -- Check for defaulted prenote_date.
100 --
101 if p_rec.prenote_date = hr_api.g_date then
102 hr_utility.trace('| not updating prenote_date (default passed in)');
103 --
104 -- No change to be made: existing combination's date is not updated,
105 -- and fresh combination must have date clear (for prenotification to
106 -- take place).
107 --
108 null;
109 elsif ( nvl(p_rec.prenote_date, hr_api.g_date) <>
110 nvl(pay_exa_shd.g_old_rec.prenote_date, hr_api.g_date) ) then
111 hr_utility.trace('| updating prenote_date');
112 --
113 UPDATE PAY_EXTERNAL_ACCOUNTS
114 SET prenote_date = p_rec.prenote_date
115 WHERE external_account_id = p_rec.external_account_id
116 ;
117 end if;
118 --
119 -- U has occurred, increment object version number
120 --
121 UPDATE PAY_EXTERNAL_ACCOUNTS
122 SET object_version_number = nvl(object_version_number, 0) + 1
123 WHERE external_account_id = p_rec.external_account_id
124 ;
125 end if;
126 --
127 pay_exa_shd.g_api_dml := false; -- unset the api dml status
128 --
129 hr_utility.set_location(' Leaving:'||l_proc, 10);
130 Exception
131 When hr_api.check_integrity_violated Then
132 -- A check constraint has been violated
133 pay_exa_shd.g_api_dml := false; -- Unset the api dml status
134 pay_exa_shd.constraint_error
135 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
136 When hr_api.parent_integrity_violated Then
137 -- Parent integrity has been violated
138 pay_exa_shd.g_api_dml := false; -- Unset the api dml status
139 pay_exa_shd.constraint_error
140 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
141 When hr_api.unique_integrity_violated Then
142 -- Unique integrity has been violated
143 pay_exa_shd.g_api_dml := false; -- Unset the api dml status
144 pay_exa_shd.constraint_error
145 (p_constraint_name => hr_api.strip_constraint_name(SQLERRM));
146 When Others Then
147 pay_exa_shd.g_api_dml := false; -- Unset the api dml status
148 Raise;
149 end insert_dml;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------------------------------< pre_insert >------------------------------|
153 -- ----------------------------------------------------------------------------
154 -- {Start Of Comments}
155 --
156 -- Description:
157 -- This private procedure contains any processing which is required before
158 -- the insert dml. Presently, if the entity has a corresponding primary
159 -- key which is maintained by an associating sequence, the primary key for
160 -- the entity will be populated with the next sequence value in
161 -- preparation for the insert dml.
162 --
163 -- Pre Conditions:
164 -- This is an internal procedure which is called from the ins procedure.
165 --
166 -- In Arguments:
167 -- A Pl/Sql record structre.
168 --
169 -- Post Success:
170 -- Processing continues.
171 --
172 -- Post Failure:
173 -- If an error has occurred, an error message and exception will be raised
174 -- but not handled.
175 --
176 -- Developer Implementation Notes:
177 -- Any pre-processing required before the insert dml is issued should be
178 -- coded within this procedure. As stated above, a good example is the
179 -- generation of a primary key number via a corresponding sequence.
180 -- It is important to note that any 3rd party maintenance should be reviewed
181 -- before placing in this procedure.
182 --
183 -- Access Status:
184 -- Internal Table Handler Use Only.
185 --
186 -- {End Of Comments}
187 -- ----------------------------------------------------------------------------
188 procedure pre_insert(
189 p_rec in out nocopy pay_exa_shd.g_rec_type
190 ,p_business_group_id in number
191 ) is
192 --
193 l_proc varchar2(72) := g_package||'pre_insert';
194 --
195 Begin
196 hr_utility.set_location('Entering:'||l_proc, 5);
197 hr_utility.set_location(' Leaving:'||l_proc, 10);
198 End pre_insert;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |-----------------------------< post_insert >------------------------------|
202 -- ----------------------------------------------------------------------------
203 -- {Start Of Comments}
204 --
205 -- Description:
206 -- This private procedure contains any processing which is required after the
207 -- insert dml.
208 --
209 -- Pre Conditions:
210 -- This is an internal procedure which is called from the ins procedure.
211 --
212 -- In Arguments:
213 -- A Pl/Sql record structre.
214 --
215 -- Post Success:
216 -- Processing continues.
217 --
218 -- Post Failure:
219 -- If an error has occurred, an error message and exception will be raised
220 -- but not handled.
221 --
222 -- Developer Implementation Notes:
223 -- Any post-processing required after the insert dml is issued should be
224 -- coded within this procedure. It is important to note that any 3rd party
225 -- maintenance should be reviewed before placing in this procedure.
226 --
227 -- Access Status:
228 -- Internal Table Handler Use Only.
229 --
230 -- {End Of Comments}
231 -- ----------------------------------------------------------------------------
232 procedure post_insert(
233 p_rec in pay_exa_shd.g_rec_type
234 ,p_business_group_id in number
235 ) is
236 --
237 l_proc varchar2(72) := g_package||'post_insert';
238 --
239 begin
240 hr_utility.set_location('Entering:'||l_proc, 5);
241 --
242 hr_utility.set_location(' Leaving:'||l_proc, 10);
243 end post_insert;
244 --
245 -- ----------------------------------------------------------------------------
246 -- |---------------------------------< ins >----------------------------------|
247 -- ----------------------------------------------------------------------------
248 procedure ins(
249 p_rec in out nocopy pay_exa_shd.g_rec_type
250 ,p_business_group_id in number
251 ,p_validate in boolean default false
252 ) is
253 --
254 l_proc varchar2(72) := g_package||'ins';
255 --
256 begin
257 hr_utility.set_location('Entering:'||l_proc, 5);
258 --
259 -- determine if the business process is to be validated
260 --
261 If p_validate then
262 --
263 -- issue the savepoint
264 --
265 SAVEPOINT ins_pay_exa;
266 End If;
267
268 --
269 -- call the supporting insert validate operations
270 --
271 pay_exa_bus.insert_validate(p_rec, p_business_group_id);
272
273 --
274 -- call the supporting pre-insert operation
275 --
276 pre_insert(p_rec, p_business_group_id);
277
278 --
279 -- insert the row
280 --
281 insert_dml(p_rec, p_business_group_id);
282
283 --
284 -- call the supporting post-insert operation
285 --
286 post_insert(p_rec, p_business_group_id);
287
288 --
289 -- if we are validating then raise the Validate_Enabled exception
290 --
291 If p_validate then
292 Raise HR_Api.Validate_Enabled;
293 End If;
294 --
295 hr_utility.set_location(' Leaving:'||l_proc, 10);
296 Exception
297 When HR_Api.Validate_Enabled Then
298 --
299 -- As the Validate_Enabled exception has been raised
300 -- we must rollback to the savepoint
301 --
302 ROLLBACK TO ins_pay_exa;
303 end ins;
304 --
305 -- ----------------------------------------------------------------------------
306 -- |---------------------------------< ins >----------------------------------|
307 -- ----------------------------------------------------------------------------
308 procedure ins(
309 p_business_group_id in number
310 ,p_external_account_id in number
311 ,p_territory_code in varchar2 default null
312 ,p_prenote_date in date default null
313 ,p_segment1 in varchar2 default null
314 ,p_segment2 in varchar2 default null
315 ,p_segment3 in varchar2 default null
316 ,p_segment4 in varchar2 default null
317 ,p_segment5 in varchar2 default null
318 ,p_segment6 in varchar2 default null
319 ,p_segment7 in varchar2 default null
320 ,p_segment8 in varchar2 default null
321 ,p_segment9 in varchar2 default null
322 ,p_segment10 in varchar2 default null
323 ,p_segment11 in varchar2 default null
324 ,p_segment12 in varchar2 default null
325 ,p_segment13 in varchar2 default null
326 ,p_segment14 in varchar2 default null
327 ,p_segment15 in varchar2 default null
328 ,p_segment16 in varchar2 default null
329 ,p_segment17 in varchar2 default null
330 ,p_segment18 in varchar2 default null
331 ,p_segment19 in varchar2 default null
332 ,p_segment20 in varchar2 default null
333 ,p_segment21 in varchar2 default null
334 ,p_segment22 in varchar2 default null
335 ,p_segment23 in varchar2 default null
336 ,p_segment24 in varchar2 default null
337 ,p_segment25 in varchar2 default null
338 ,p_segment26 in varchar2 default null
339 ,p_segment27 in varchar2 default null
340 ,p_segment28 in varchar2 default null
341 ,p_segment29 in varchar2 default null
342 ,p_segment30 in varchar2 default null
343 ,p_object_version_number out nocopy number
344 ,p_validate in boolean default false
345 ) is
346 --
347 l_rec pay_exa_shd.g_rec_type;
348 l_proc varchar2(72) := g_package||'ins';
349 --
350 cursor csr_ovn(p_external_account_id number) is
351 SELECT pea.object_version_number
352 FROM PAY_EXTERNAL_ACCOUNTS pea
353 WHERE pea.external_account_id = p_external_account_id
354 ;
355 --
356 begin
357 hr_utility.set_location('Entering:'||l_proc, 5);
358 --
359 -- call conversion function to turn arguments into the p_rec structure
360 --
361 l_rec :=
362 pay_exa_shd.convert_args
363 (
364 p_external_account_id,
365 p_territory_code,
366 p_prenote_date,
367 --
368 -- do need to maintain these columns, set by aol api
369 --
370 null, -- id_flex_num
371 null, -- summary_flag
372 null, -- enabled_flag
373 null, -- start_date_active
374 null, -- end_date_active
375 --
376 p_segment1,
377 p_segment2,
378 p_segment3,
379 p_segment4,
380 p_segment5,
381 p_segment6,
382 p_segment7,
383 p_segment8,
384 p_segment9,
385 p_segment10,
386 p_segment11,
387 p_segment12,
388 p_segment13,
389 p_segment14,
390 p_segment15,
391 p_segment16,
392 p_segment17,
393 p_segment18,
394 p_segment19,
395 p_segment20,
396 p_segment21,
397 p_segment22,
398 p_segment23,
399 p_segment24,
400 p_segment25,
401 p_segment26,
402 p_segment27,
403 p_segment28,
404 p_segment29,
405 p_segment30,
406 null
407 );
408 --
409 -- having converted the arguments into the pay_exa_rec plsql record
410 -- structure we call the corresponding record business process
411 --
412 ins(
413 p_rec => l_rec,
414 p_business_group_id => p_business_group_id,
415 p_validate => p_validate);
416 --
417 -- as the primary key argument(s) are specified as an OUT's we
418 -- must set these values
419 --
420 -- object version number may have changed,
421 -- select the latest value and pass as out paramter
422 --
423 open csr_ovn(l_rec.external_account_id);
424 fetch csr_ovn into p_object_version_number;
425 close csr_ovn;
426 --
427 hr_utility.set_location(' Leaving:'||l_proc, 10);
428 end ins;
429 --
430 -- ----------------------------------------------------------------------------
431 -- |-------------------------------< ins_or_sel >-----------------------------|
432 -- ----------------------------------------------------------------------------
433 procedure ins_or_sel(
434 p_segment1 in varchar2 default null
435 ,p_segment2 in varchar2 default null
436 ,p_segment3 in varchar2 default null
437 ,p_segment4 in varchar2 default null
438 ,p_segment5 in varchar2 default null
439 ,p_segment6 in varchar2 default null
440 ,p_segment7 in varchar2 default null
441 ,p_segment8 in varchar2 default null
442 ,p_segment9 in varchar2 default null
443 ,p_segment10 in varchar2 default null
444 ,p_segment11 in varchar2 default null
445 ,p_segment12 in varchar2 default null
446 ,p_segment13 in varchar2 default null
447 ,p_segment14 in varchar2 default null
448 ,p_segment15 in varchar2 default null
449 ,p_segment16 in varchar2 default null
450 ,p_segment17 in varchar2 default null
451 ,p_segment18 in varchar2 default null
452 ,p_segment19 in varchar2 default null
453 ,p_segment20 in varchar2 default null
454 ,p_segment21 in varchar2 default null
455 ,p_segment22 in varchar2 default null
456 ,p_segment23 in varchar2 default null
457 ,p_segment24 in varchar2 default null
458 ,p_segment25 in varchar2 default null
459 ,p_segment26 in varchar2 default null
460 ,p_segment27 in varchar2 default null
461 ,p_segment28 in varchar2 default null
462 ,p_segment29 in varchar2 default null
463 ,p_segment30 in varchar2 default null
464 ,p_concat_segments in varchar2 default null
465 ,p_business_group_id in number
466 -- make territory_code code a mandatory parameter on I interface
467 ,p_territory_code in varchar2
468 ,p_prenote_date in date default null
469 ,p_external_account_id out nocopy number
470 ,p_object_version_number out nocopy number
471 ,p_validate in boolean default false
472 ) is
473 --
474 l_external_account_id pay_external_accounts.external_account_id%type;
475 l_proc varchar2(72) := g_package||'ins_or_sel';
476 l_object_version_number pay_external_accounts.object_version_number%type;
477 l_prenote_date pay_external_accounts.prenote_date%type;
478 --
479 l_concat_segments_out varchar2(4600);
480 --
481 begin
482 hr_utility.set_location('***** Entering:' || l_proc || ' *****', 5);
483 --
484 -- stub - do we need to validate prenote_date,
485 -- could be null ?
486 --
487 -- territory_code code must be specified as its value may be
488 -- placed on a fresh combination record
489 --
490 hr_api.mandatory_arg_error(
491 p_api_name => l_proc,
492 p_argument => 'territory_code',
493 p_argument_value => p_territory_code
494 );
495 --
496 -- call wrapper,
497 -- generates formatted msg upon segement validation failure,
498 -- do not need to deal with out parameters on failure as this is
499 -- only an internal call,
500 -- out paramters used by ins_or_sel() are explicitly set to null
501 -- on failure
502 --
503 pay_exa_shd.keyflex_comb(
504 p_dml_mode => 'INSERT',
505 p_business_group_id => p_business_group_id,
506 p_appl_short_name => 'PAY',
507 p_territory_code => p_territory_code,
508 p_flex_code => 'BANK',
509 p_segment1 => p_segment1,
510 p_segment2 => p_segment2,
511 p_segment3 => p_segment3,
512 p_segment4 => p_segment4,
513 p_segment5 => p_segment5,
514 p_segment6 => p_segment6,
515 p_segment7 => p_segment7,
516 p_segment8 => p_segment8,
517 p_segment9 => p_segment9,
518 p_segment10 => p_segment10,
519 p_segment11 => p_segment11,
520 p_segment12 => p_segment12,
521 p_segment13 => p_segment13,
522 p_segment14 => p_segment14,
523 p_segment15 => p_segment15,
524 p_segment16 => p_segment16,
525 p_segment17 => p_segment17,
526 p_segment18 => p_segment18,
527 p_segment19 => p_segment19,
528 p_segment20 => p_segment20,
529 p_segment21 => p_segment21,
530 p_segment22 => p_segment22,
531 p_segment23 => p_segment23,
532 p_segment24 => p_segment24,
533 p_segment25 => p_segment25,
534 p_segment26 => p_segment26,
535 p_segment27 => p_segment27,
536 p_segment28 => p_segment28,
537 p_segment29 => p_segment29,
538 p_segment30 => p_segment30,
539 p_concat_segments_in => p_concat_segments,
540 p_ccid => l_external_account_id,
541 p_concat_segments_out => l_concat_segments_out
542 );
543 --
544 -- I interface is now actually doing an U,
545 -- set territory_code, prenote_date on corresponding external
546 -- account row,
547 -- all parameters are required to generate p_rec structure
548 --
549 pay_exa_ins.ins(
550 p_business_group_id => p_business_group_id,
551 p_external_account_id => l_external_account_id,
552 p_territory_code => p_territory_code,
553 p_prenote_date => p_prenote_date,
554 p_segment1 => p_segment1,
555 p_segment2 => p_segment2,
556 p_segment3 => p_segment3,
557 p_segment4 => p_segment4,
558 p_segment5 => p_segment5,
559 p_segment6 => p_segment6,
560 p_segment7 => p_segment7,
561 p_segment8 => p_segment8,
562 p_segment9 => p_segment9,
563 p_segment10 => p_segment10,
564 p_segment11 => p_segment11,
565 p_segment12 => p_segment12,
566 p_segment13 => p_segment13,
567 p_segment14 => p_segment14,
568 p_segment15 => p_segment15,
569 p_segment16 => p_segment16,
570 p_segment17 => p_segment17,
571 p_segment18 => p_segment18,
572 p_segment19 => p_segment19,
573 p_segment20 => p_segment20,
574 p_segment21 => p_segment21,
575 p_segment22 => p_segment22,
576 p_segment23 => p_segment23,
577 p_segment24 => p_segment24,
578 p_segment25 => p_segment25,
579 p_segment26 => p_segment26,
580 p_segment27 => p_segment27,
581 p_segment28 => p_segment28,
582 p_segment29 => p_segment29,
583 p_segment30 => p_segment30,
584 p_object_version_number => l_object_version_number,
585 p_validate => p_validate
586 );
587 --
588 -- set out arguments
589 --
590 p_object_version_number := l_object_version_number;
591 p_external_account_id := l_external_account_id;
592 --
593 -- explicitly set out arguments to null if in validate only mode
594 --
595 if p_validate then
596 p_external_account_id := null;
597 p_object_version_number := null;
598 end if;
599 --
600 hr_utility.set_location('***** Leaving:' || l_proc || ' *****', 100);
601 end ins_or_sel;
602 --
603 END pay_exa_ins;
[Home] [Help]