[Home] [Help]
PACKAGE BODY: APPS.ARP_TBAT_PKG
Source
1 PACKAGE BODY ARP_TBAT_PKG AS
2 /* $Header: ARTIBATB.pls 120.6 2005/04/14 22:44:45 hyu ship $ */
3
4 /*--------------------------------------------------------+
5 | Dummy constants for use in update and lock operations |
6 +--------------------------------------------------------*/
7
8 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
9 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
10 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
11 AR_FLAG_DUMMY CONSTANT VARCHAR2(10) := '~';
12
13 /*---------------------------------------------------------------+
14 | Package global variables to hold the parsed update cursors. |
15 | This allows the cursors to be reused without being reparsed. |
16 +---------------------------------------------------------------*/
17
18 pg_cursor1 integer := '';
19 pg_cursor2 integer := '';
20
21 /*-------------------------------------+
22 | WHO column values from FND_GLOBAL |
23 +-------------------------------------*/
24
25 pg_user_id number;
26 pg_conc_login_id number;
27 pg_login_id number;
28 pg_prog_appl_id number;
29 pg_conc_program_id number;
30
31 /*===========================================================================+
32 | PROCEDURE |
33 | display_batch_rec |
34 | |
35 | DESCRIPTION |
36 | Displays the values of all columns except creation_date and |
37 | last_update_date. |
38 | |
39 | SCOPE - PRIVATE |
40 | |
41 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
42 | arp_util.debug |
43 | |
44 | ARGUMENTS : IN: |
45 | p_batch_rec |
46 | OUT: |
47 | None |
48 | IN/ OUT: |
49 | None |
50 | |
51 | RETURNS : NONE |
52 | |
53 | NOTES |
54 | |
55 | MODIFICATION HISTORY |
56 | 19-JUL-95 Charlie Tomberg Created |
57 | |
58 | 20-MAR-2000 J Rautiainen Added BR project related columns |
59 | ISSUE_DATE, MATURITY_DATE, |
60 | SPECIAL_INSTRUCTIONS, BATCH_PROCESS_STATUS |
61 | and SELECTION_CRITERIA_ID into table handlers|
62 | |
63 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column |
64 | purged_children_flag and |
65 | request_id |
66 | into the table handlers. |
67 | |
68 +===========================================================================*/
69
70 PROCEDURE display_batch_rec( p_batch_rec ra_batches%rowtype )
71 IS
72
73
74 BEGIN
75
76 arp_util.debug('arp_tbat_pgk.display_batch_rec()+');
77
78
79 arp_util.debug('************** Dump of ra_batches record **************');
80 arp_util.debug('batch_id: ' || p_batch_rec.batch_id);
81 arp_util.debug('last_updated_by: ' || p_batch_rec.last_updated_by);
82 arp_util.debug('created_by: ' || p_batch_rec.created_by);
83 arp_util.debug('last_update_login: ' || p_batch_rec.last_update_login);
84 arp_util.debug('program_id: ' || p_batch_rec.program_id);
85 arp_util.debug('set_of_books_id: ' || p_batch_rec.set_of_books_id);
86 arp_util.debug('name: ' || p_batch_rec.name);
87 arp_util.debug('batch_source_id: ' || p_batch_rec.batch_source_id);
88 arp_util.debug('batch_date: ' || p_batch_rec.batch_date);
89 arp_util.debug('gl_date: ' || p_batch_rec.gl_date);
90 arp_util.debug('status: ' || p_batch_rec.status);
91 arp_util.debug('type: ' || p_batch_rec.type);
92 arp_util.debug('control_count: ' || p_batch_rec.control_count);
93 arp_util.debug('control_amount: ' || p_batch_rec.control_amount);
94 arp_util.debug('comments: ' || p_batch_rec.comments);
95 arp_util.debug('currency_code: ' || p_batch_rec.currency_code);
96 arp_util.debug('exchange_rate_type: ' || p_batch_rec.exchange_rate_type);
97 arp_util.debug('exchange_date: ' || p_batch_rec.exchange_date);
98 arp_util.debug('exchange_rate: ' || p_batch_rec.exchange_rate);
99 arp_util.debug('purged_children_flag:'|| p_batch_rec.purged_children_flag);
100 arp_util.debug('attribute_category: ' || p_batch_rec.attribute_category);
101 arp_util.debug('attribute1: ' || p_batch_rec.attribute1);
102 arp_util.debug('attribute2: ' || p_batch_rec.attribute2);
103 arp_util.debug('attribute3: ' || p_batch_rec.attribute3);
104 arp_util.debug('attribute4: ' || p_batch_rec.attribute4);
105 arp_util.debug('attribute5: ' || p_batch_rec.attribute5);
106 arp_util.debug('attribute6: ' || p_batch_rec.attribute6);
107 arp_util.debug('attribute7: ' || p_batch_rec.attribute7);
108 arp_util.debug('attribute8: ' || p_batch_rec.attribute8);
109 arp_util.debug('attribute9: ' || p_batch_rec.attribute9);
110 arp_util.debug('attribute10: ' || p_batch_rec.attribute10);
111 arp_util.debug('attribute11: ' || p_batch_rec.attribute11);
112 arp_util.debug('attribute12: ' || p_batch_rec.attribute12);
113 arp_util.debug('attribute13: ' || p_batch_rec.attribute13);
114 arp_util.debug('attribute14: ' || p_batch_rec.attribute14);
115 arp_util.debug('attribute15: ' || p_batch_rec.attribute15);
116 arp_util.debug('program_application_id: ' || p_batch_rec.program_application_id);
117 arp_util.debug('issue_date: ' || p_batch_rec.issue_date);
118 arp_util.debug('maturity_date: ' || p_batch_rec.maturity_date);
119 arp_util.debug('special_instructions: ' || p_batch_rec.special_instructions);
120 arp_util.debug('batch_process_status: ' || p_batch_rec.batch_process_status);
121 arp_util.debug('selection_criteria_id: ' || p_batch_rec.selection_criteria_id);
122 arp_util.debug('request_id: ' || p_batch_rec.request_id);
123 arp_util.debug('************** End ra_batches record **************');
124
125 arp_util.debug('arp_tbat_pgk.display_batch_rec()-');
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 arp_util.debug('EXCEPTION: arp_tbat_pgk.display_batch_rec()');
130 RAISE;
131
132 END;
133
134
135 /*===========================================================================+
136 | PROCEDURE |
137 | display_batch |
138 | |
139 | DESCRIPTION |
140 | Selects and displays the values of all columns except creation_date |
141 | and last_update_date. |
142 | |
143 | SCOPE - PRIVATE |
144 | |
145 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
146 | arp_util.debug |
147 | |
148 | ARGUMENTS : IN: |
149 | p_batch_id |
150 | OUT: |
151 | None |
152 | IN/ OUT: |
153 | None |
154 | |
155 | RETURNS : NONE |
156 | |
157 | NOTES |
158 | |
159 | MODIFICATION HISTORY |
160 | 13-JUL-95 Charlie Tomberg Created |
161 | |
162 +===========================================================================*/
163
164 PROCEDURE display_batch( p_batch_id IN ra_batches.batch_id%type)
165 IS
166
167 l_batch_rec ra_batches%rowtype;
168
169 BEGIN
170
171 arp_util.debug('arp_tbat_pgk.display_batch()+');
172
173 arp_tbat_pkg.fetch_p(l_batch_rec, p_batch_id);
174
175 arp_tbat_pkg.display_batch_rec (l_batch_rec);
176
177 arp_util.debug('arp_tbat_pgk.display_batch()-');
178
179 EXCEPTION
180 WHEN OTHERS THEN
181 arp_util.debug('EXCEPTION: arp_tbat_pgk.display_batch()');
182 RAISE;
183
184 END;
185
186
187 /*===========================================================================+
188 | PROCEDURE |
189 | bind_batch_variables |
190 | |
191 | DESCRIPTION |
192 | Binds variables from the batch record variable to the bind variables |
193 | in the dynamic SQL update statement. |
194 | |
195 | SCOPE - PRIVATE |
196 | |
197 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
198 | dbms_sql.bind_variable |
199 | arp_util.debug |
200 | |
201 | ARGUMENTS : IN: |
202 | p_update_cursor - ID of the update cursor |
203 | p_batch_rec - ra_batches record |
204 | OUT: |
205 | None |
206 | |
207 | RETURNS : NONE |
208 | |
209 | NOTES |
210 | |
211 | MODIFICATION HISTORY |
212 | 06-JUN-95 Charlie Tomberg Created |
213 | |
214 | 20-MAR-2000 J Rautiainen Added BR project related columns |
215 | ISSUE_DATE, MATURITY_DATE, |
216 | SPECIAL_INSTRUCTIONS, BATCH_PROCESS_STATUS |
217 | and SELECTION_CRITERIA_ID into table handlers|
218 | |
219 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column |
220 | purged_children_flag and |
221 | request_id |
222 | into the table handlers. |
223 | |
224 +===========================================================================*/
225
226
227 PROCEDURE bind_batch_variables(p_update_cursor IN integer,
228 p_batch_rec IN ra_batches%rowtype) IS
229
230 BEGIN
231
232 arp_util.debug('arp_tbat_pkg.bind_batch_variables()+');
233
234 /*------------------+
235 | Dummy constants |
236 +------------------*/
237
238 dbms_sql.bind_variable(p_update_cursor, ':ar_text_dummy',
239 AR_TEXT_DUMMY);
240 dbms_sql.bind_variable(p_update_cursor, ':ar_number_dummy',
241 AR_NUMBER_DUMMY);
242 dbms_sql.bind_variable(p_update_cursor, ':ar_date_dummy',
243 AR_DATE_DUMMY);
244 dbms_sql.bind_variable(p_update_cursor, ':ar_flag_dummy',
245 AR_FLAG_DUMMY);
246
247 /*------------------+
248 | WHO variables |
249 +------------------*/
250
251 dbms_sql.bind_variable(p_update_cursor, ':pg_user_id',
252 pg_user_id);
253
254 dbms_sql.bind_variable(p_update_cursor, ':pg_login_id',
255 pg_login_id);
256
257 dbms_sql.bind_variable(p_update_cursor, ':pg_conc_login_id',
258 pg_conc_login_id);
259
260
261 /*----------------------------------------------+
262 | Bind variables for all columns in the table |
263 +----------------------------------------------*/
264
265 dbms_sql.bind_variable(p_update_cursor, ':batch_id',
266 p_batch_rec.batch_id);
267 dbms_sql.bind_variable(p_update_cursor, ':last_update_date',
268 p_batch_rec.last_update_date);
269 dbms_sql.bind_variable(p_update_cursor, ':last_updated_by',
270 p_batch_rec.last_updated_by);
271 dbms_sql.bind_variable(p_update_cursor, ':creation_date',
272 p_batch_rec.creation_date);
273 dbms_sql.bind_variable(p_update_cursor, ':created_by',
274 p_batch_rec.created_by);
275 dbms_sql.bind_variable(p_update_cursor, ':last_update_login',
276 p_batch_rec.last_update_login);
277 dbms_sql.bind_variable(p_update_cursor, ':program_application_id',
278 p_batch_rec.program_application_id);
279 dbms_sql.bind_variable(p_update_cursor, ':program_id',
280 p_batch_rec.program_id);
281 dbms_sql.bind_variable(p_update_cursor, ':program_update_date',
282 p_batch_rec.program_update_date);
283 dbms_sql.bind_variable(p_update_cursor, ':set_of_books_id',
284 p_batch_rec.set_of_books_id);
285 dbms_sql.bind_variable(p_update_cursor, ':name',
286 p_batch_rec.name);
287 dbms_sql.bind_variable(p_update_cursor, ':batch_source_id',
288 p_batch_rec.batch_source_id);
289 dbms_sql.bind_variable(p_update_cursor, ':batch_date',
290 p_batch_rec.batch_date);
291 dbms_sql.bind_variable(p_update_cursor, ':gl_date',
292 p_batch_rec.gl_date);
293 dbms_sql.bind_variable(p_update_cursor, ':status',
294 p_batch_rec.status);
295 dbms_sql.bind_variable(p_update_cursor, ':type',
296 p_batch_rec.type);
297 dbms_sql.bind_variable(p_update_cursor, ':control_count',
298 p_batch_rec.control_count);
299 dbms_sql.bind_variable(p_update_cursor, ':control_amount',
300 p_batch_rec.control_amount);
301 dbms_sql.bind_variable(p_update_cursor, ':comments',
302 p_batch_rec.comments);
303 dbms_sql.bind_variable(p_update_cursor, ':currency_code',
304 p_batch_rec.currency_code);
305 dbms_sql.bind_variable(p_update_cursor, ':exchange_rate_type',
306 p_batch_rec.exchange_rate_type);
307 dbms_sql.bind_variable(p_update_cursor, ':exchange_date',
308 p_batch_rec.exchange_date);
309 dbms_sql.bind_variable(p_update_cursor, ':exchange_rate',
310 p_batch_rec.exchange_rate);
311 dbms_sql.bind_variable(p_update_cursor, ':purged_children_flag',
312 p_batch_rec.purged_children_flag);
313 dbms_sql.bind_variable(p_update_cursor, ':attribute_category',
314 p_batch_rec.attribute_category);
315 dbms_sql.bind_variable(p_update_cursor, ':attribute1',
316 p_batch_rec.attribute1);
317 dbms_sql.bind_variable(p_update_cursor, ':attribute2',
318 p_batch_rec.attribute2);
319 dbms_sql.bind_variable(p_update_cursor, ':attribute3',
320 p_batch_rec.attribute3);
321 dbms_sql.bind_variable(p_update_cursor, ':attribute4',
322 p_batch_rec.attribute4);
323 dbms_sql.bind_variable(p_update_cursor, ':attribute5',
324 p_batch_rec.attribute5);
325 dbms_sql.bind_variable(p_update_cursor, ':attribute6',
326 p_batch_rec.attribute6);
327 dbms_sql.bind_variable(p_update_cursor, ':attribute7',
328 p_batch_rec.attribute7);
329 dbms_sql.bind_variable(p_update_cursor, ':attribute8',
330 p_batch_rec.attribute8);
331 dbms_sql.bind_variable(p_update_cursor, ':attribute9',
332 p_batch_rec.attribute9);
333 dbms_sql.bind_variable(p_update_cursor, ':attribute10',
334 p_batch_rec.attribute10);
335 dbms_sql.bind_variable(p_update_cursor, ':attribute11',
336 p_batch_rec.attribute11);
337 dbms_sql.bind_variable(p_update_cursor, ':attribute12',
338 p_batch_rec.attribute12);
339 dbms_sql.bind_variable(p_update_cursor, ':attribute13',
340 p_batch_rec.attribute13);
341 dbms_sql.bind_variable(p_update_cursor, ':attribute14',
342 p_batch_rec.attribute14);
343 dbms_sql.bind_variable(p_update_cursor, ':attribute15',
344 p_batch_rec.attribute15);
345
346 dbms_sql.bind_variable(p_update_cursor, ':issue_date',
347 p_batch_rec.issue_date);
348 dbms_sql.bind_variable(p_update_cursor, ':maturity_date',
349 p_batch_rec.maturity_date);
350 dbms_sql.bind_variable(p_update_cursor, ':special_instructions',
351 p_batch_rec.special_instructions);
352 dbms_sql.bind_variable(p_update_cursor, ':batch_process_status',
353 p_batch_rec.batch_process_status);
354 dbms_sql.bind_variable(p_update_cursor, ':selection_criteria_id',
355 p_batch_rec.selection_criteria_id);
356 dbms_sql.bind_variable(p_update_cursor, ':request_id',
357 p_batch_rec.request_id);
358
359 arp_util.debug('arp_tbat_pkg.bind_batch_variables()-');
360
361 EXCEPTION
362 WHEN OTHERS THEN
363 arp_util.debug('EXCEPTION: arp_tbat_pkg.bind_batch_variables()');
364 RAISE;
365
366 END;
367
368
369 /*===========================================================================+
370 | PROCEDURE |
371 | construct_batch_update_stmt |
372 | |
373 | DESCRIPTION |
374 | Copies the text of the dynamic SQL update statement into the |
375 | out NOCOPY paramater. The update statement does not contain a where clause |
376 | since this is the dynamic part that is added later. |
377 | |
378 | SCOPE - PRIVATE |
379 | |
380 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
381 | arp_util.debug |
382 | |
383 | ARGUMENTS : IN: |
384 | None. |
385 | OUT: |
386 | update_text - text of the update statement |
387 | |
388 | RETURNS : NONE |
389 | |
390 | NOTES |
391 | This statement only updates columns in the batch record that do not |
392 | contain the dummy values that indicate that they should not be changed.|
393 | |
394 | MODIFICATION HISTORY |
395 | 06-JUN-95 Charlie Tomberg Created |
396 | |
397 | 20-MAR-2000 J Rautiainen Added BR project related columns |
398 | ISSUE_DATE, MATURITY_DATE, |
399 | SPECIAL_INSTRUCTIONS, BATCH_PROCESS_STATUS |
400 | and SELECTION_CRITERIA_ID into table handlers|
401 | |
402 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column |
403 | purged_children_flag and |
404 | request_id |
405 | into the table handlers. |
406 | |
407 +===========================================================================*/
408
409 PROCEDURE construct_batch_update_stmt( update_text OUT NOCOPY varchar2) IS
410
411 BEGIN
412 arp_util.debug('arp_tbat_pkg.construct_batch_update_stmt()+');
413
414 update_text :=
415 'update ra_batches
416 SET batch_id =
417 DECODE(:batch_id,
418 :ar_number_dummy, batch_id,
419 :batch_id),
420 last_update_date =
421 DECODE(:last_update_date,
422 :ar_date_dummy, sysdate,
423 :last_update_date),
424 last_updated_by =
425 DECODE(:last_updated_by,
426 :ar_number_dummy, :pg_user_id,
427 :last_updated_by),
428 creation_date =
429 DECODE(:creation_date,
430 :ar_date_dummy, creation_date,
431 :creation_date),
432 created_by =
433 DECODE(:created_by,
434 :ar_number_dummy, created_by,
435 :created_by),
436 last_update_login =
437 DECODE(:last_update_login,
438 :ar_number_dummy, nvl(:pg_conc_login_id,
439 :pg_login_id),
440 :last_update_login),
441 program_application_id =
442 DECODE(:program_application_id,
443 :ar_number_dummy, program_application_id,
444 :program_application_id),
445 program_id =
446 DECODE(:program_id,
447 :ar_number_dummy, program_id,
448 :program_id),
449 program_update_date =
450 DECODE(:program_update_date,
451 :ar_date_dummy, program_update_date,
452 :program_update_date),
453 set_of_books_id =
454 DECODE(:set_of_books_id,
455 :ar_number_dummy, set_of_books_id,
456 :set_of_books_id),
457 name =
458 DECODE(:name,
459 :ar_text_dummy, name,
460 :name),
461 batch_source_id =
462 DECODE(:batch_source_id,
463 :ar_number_dummy, batch_source_id,
464 :batch_source_id),
465 batch_date =
466 DECODE(:batch_date,
467 :ar_date_dummy, batch_date,
468 :batch_date),
469 gl_date =
470 DECODE(:gl_date,
471 :ar_date_dummy, gl_date,
472 :gl_date),
473 status =
474 DECODE(:status,
475 :ar_text_dummy, status,
476 :status),
477 type =
478 DECODE(:type,
479 :ar_text_dummy, type,
480 :type),
481 control_count =
482 DECODE(:control_count,
483 :ar_number_dummy, control_count,
484 :control_count),
485 control_amount =
486 DECODE(:control_amount,
487 :ar_number_dummy, control_amount,
488 :control_amount),
489 comments =
490 DECODE(:comments,
491 :ar_text_dummy, comments,
492 :comments),
493 currency_code =
494 DECODE(:currency_code,
495 :ar_text_dummy, currency_code,
496 :currency_code),
497 exchange_rate_type =
498 DECODE(:exchange_rate_type,
499 :ar_text_dummy, exchange_rate_type,
500 :exchange_rate_type),
501 exchange_date =
502 DECODE(:exchange_date,
503 :ar_date_dummy, exchange_date,
504 :exchange_date),
505 exchange_rate =
506 DECODE(:exchange_rate,
507 :ar_number_dummy, exchange_rate,
508 :exchange_rate),
509 purged_children_flag =
510 DECODE(:purged_children_flag,
511 :ar_flag_dummy, purged_children_flag,
512 :purged_children_flag),
513 attribute_category =
514 DECODE(:attribute_category,
515 :ar_text_dummy, attribute_category,
516 :attribute_category),
517 attribute1 =
518 DECODE(:attribute1,
519 :ar_text_dummy, attribute1,
520 :attribute1),
521 attribute2 =
522 DECODE(:attribute2,
523 :ar_text_dummy, attribute2,
524 :attribute2),
525 attribute3 =
526 DECODE(:attribute3,
527 :ar_text_dummy, attribute3,
528 :attribute3),
529 attribute4 =
530 DECODE(:attribute4,
531 :ar_text_dummy, attribute4,
532 :attribute4),
533 attribute5 =
534 DECODE(:attribute5,
535 :ar_text_dummy, attribute5,
536 :attribute5),
537 attribute6 =
538 DECODE(:attribute6,
539 :ar_text_dummy, attribute6,
540 :attribute6),
541 attribute7 =
542 DECODE(:attribute7,
543 :ar_text_dummy, attribute7,
544 :attribute7),
545 attribute8 =
546 DECODE(:attribute8,
547 :ar_text_dummy, attribute8,
548 :attribute8),
549 attribute9 =
550 DECODE(:attribute9,
551 :ar_text_dummy, attribute9,
552 :attribute9),
553 attribute10 =
554 DECODE(:attribute10,
555 :ar_text_dummy, attribute10,
556 :attribute10),
557 attribute11 =
558 DECODE(:attribute11,
559 :ar_text_dummy, attribute11,
560 :attribute11),
561 attribute12 =
562 DECODE(:attribute12,
563 :ar_text_dummy, attribute12,
564 :attribute12),
565 attribute13 =
566 DECODE(:attribute13,
567 :ar_text_dummy, attribute13,
568 :attribute13),
569 attribute14 =
570 DECODE(:attribute14,
571 :ar_text_dummy, attribute14,
572 :attribute14),
573 attribute15 =
574 DECODE(:attribute15,
575 :ar_text_dummy, attribute15,
576 :attribute15),
577 issue_date =
578 DECODE(:issue_date,
579 :ar_date_dummy, issue_date,
580 :issue_date),
581 maturity_date =
582 DECODE(:maturity_date,
583 :ar_date_dummy, maturity_date,
584 :maturity_date),
585 special_instructions =
586 DECODE(:special_instructions,
587 :ar_text_dummy, special_instructions,
588 :special_instructions),
589 batch_process_status =
590 DECODE(:batch_process_status,
591 :ar_text_dummy, batch_process_status,
592 :batch_process_status),
593 selection_criteria_id =
594 DECODE(:selection_criteria_id,
595 :ar_number_dummy, selection_criteria_id,
596 :selection_criteria_id),
597 request_id =
598 DECODE(:request_id,
599 :ar_number_dummy, request_id,
600 :request_id) ';
601
602 arp_util.debug('arp_tbat_pkg.construct_batch_update_stmt()-');
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 arp_util.debug('EXCEPTION: arp_tbat_pkg.construct_batch_update_stmt()');
607 RAISE;
608
609 END;
610
611 /*===========================================================================+
612 | PROCEDURE |
613 | generic_update |
614 | |
615 | DESCRIPTION |
616 | This procedure Updates records in ra_batches identified by the where |
617 | clause that is passed in as a parameter. Only those columns in |
618 | the batch record parameter that do not contain the special dummy values|
619 | are updated. |
620 | |
621 | SCOPE - PRIVATE |
622 | |
623 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
624 | arp_util.debug |
625 | dbms_sql.open_cursor |
626 | dbms_sql.parse |
627 | dbms_sql.execute |
628 | dbms_sql.close_cursor |
629 | |
630 | ARGUMENTS : IN: |
631 | p_update_cursor - identifies the cursor to use |
632 | p_where_clause - identifies which rows to update |
633 | p_where1 - value to bind into where clause |
634 | p_batch_rec - contains the new batch values |
635 | OUT: |
636 | None |
637 | |
638 | RETURNS : NONE |
639 | |
640 | NOTES |
641 | |
642 | MODIFICATION HISTORY |
643 | 06-JUN-95 Charlie Tomberg Created |
644 | |
645 +===========================================================================*/
646
647 PROCEDURE generic_update(p_update_cursor IN OUT NOCOPY integer,
648 p_where_clause IN varchar2,
649 p_where1 IN number,
650 p_batch_rec IN ra_batches%rowtype) IS
651
652 l_count number;
653 l_update_statement varchar2(10000);
654 l_ra_batch_key_value_list gl_ca_utility_pkg.r_key_value_arr;
655 ra_batch_array dbms_sql.number_table;
656
657 BEGIN
658 arp_util.debug('arp_tbat_pkg.generic_update()+');
659
660 /*--------------------------------------------------------------+
661 | If this update statement has not already been parsed, |
662 | construct the statement and parse it. |
663 | Otherwise, use the already parsed statement and rebind its |
664 | variables. |
665 +--------------------------------------------------------------*/
666
667 IF (p_update_cursor is null)
668 THEN
669
670 p_update_cursor := dbms_sql.open_cursor;
671
672 /*---------------------------------+
673 | Construct the update statement |
674 +---------------------------------*/
675
676 arp_tbat_pkg.construct_batch_update_stmt(l_update_statement);
677
678 l_update_statement := l_update_statement || p_where_clause;
679
680 /* add on mrc variables for bulk collect */
681 l_update_statement := l_update_statement ||
682 ' RETURNING batch_id INTO :ra_batch_key_value ';
683
684
685 /*-----------------------------------------------+
686 | Parse, bind, execute and close the statement |
687 +-----------------------------------------------*/
688
689 dbms_sql.parse(p_update_cursor,
690 l_update_statement,
691 dbms_sql.v7);
692
693 /*---------------------------+
694 | Bind output variable |
695 +---------------------------*/
696 dbms_sql.bind_array(p_update_cursor,':ra_batch_key_value',
697 ra_batch_array);
698
699
700 END IF;
701
702 arp_tbat_pkg.bind_batch_variables(p_update_cursor, p_batch_rec);
703
704 /*-----------------------------------------+
705 | Bind the variables in the where clause |
706 +-----------------------------------------*/
707
708 dbms_sql.bind_variable(p_update_cursor, ':where_1',
709 p_where1);
710
711 l_count := dbms_sql.execute(p_update_cursor);
712
713 arp_util.debug( to_char(l_count) || ' rows updated');
714
715 /*------------------------------------------+
716 | get RETURNING COLUMN into OUT NOCOPY bind array |
717 +------------------------------------------*/
718
719 dbms_sql.variable_value( p_update_cursor, ':ra_batch_key_value',
720 ra_batch_array);
721
722
723 /*------------------------------------------------------------+
724 | Raise the NO_DATA_FOUND exception if no rows were updated |
725 +------------------------------------------------------------*/
726
727 IF (l_count = 0)
728 THEN RAISE NO_DATA_FOUND;
729 END IF;
730
731 --{BUG#4301323
732 -- FOR I in ra_batch_array.FIRST..ra_batch_array.LAST LOOP
733 /*---------------------------------------------+
734 | call mrc engine to update RA_MC_BATCHES |
735 +---------------------------------------------*/
736 -- ar_mrc_engine.maintain_mrc_data(
737 -- p_event_mode => 'UPDATE',
738 -- p_table_name => 'RA_BATCHES',
739 -- p_mode => 'SINGLE',
740 -- p_key_value => ra_batch_array(I));
741 -- END LOOP;
742 --}
743
744 arp_util.debug('arp_tbat_pkg.generic_update()-');
745
746 EXCEPTION
747 WHEN OTHERS THEN
748 arp_util.debug('EXCEPTION: arp_tbat_pkg.generic_update()');
749 arp_util.debug(l_update_statement);
750 arp_util.debug('Error at character: ' ||
751 to_char(dbms_sql.last_error_position));
752 RAISE;
753 END;
754
755
756 /*===========================================================================+
757 | PROCEDURE |
758 | set_to_dummy |
759 | |
760 | DESCRIPTION |
761 | This procedure initializes all columns in the parameter batch record |
762 | to the appropriate dummy value for its datatype. |
763 | |
764 | The dummy values are defined in the following package level constants: |
765 | AR_TEXT_DUMMY |
766 | AR_NUMBER_DUMMY |
767 | AR_DATE_DUMMY |
768 | |
769 | SCOPE - PUBLIC |
770 | |
771 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
772 | arp_util.debug |
773 | |
774 | ARGUMENTS : IN: |
775 | None |
776 | OUT: |
777 | p_batch_rec - The record to initialize |
778 | |
779 | RETURNS : NONE |
780 | |
781 | NOTES |
782 | |
783 | MODIFICATION HISTORY |
784 | 06-JUN-95 Charlie Tomberg Created |
785 | |
786 | 20-MAR-2000 J Rautiainen Added BR project related columns |
787 | ISSUE_DATE, MATURITY_DATE, |
788 | SPECIAL_INSTRUCTIONS, BATCH_PROCESS_STATUS |
789 | and SELECTION_CRITERIA_ID into table handlers|
790 | |
791 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column |
792 | purged_children_flag and |
793 | request_id |
794 | into the table handlers. |
795 | |
796 +===========================================================================*/
797
798 PROCEDURE set_to_dummy( p_batch_rec OUT NOCOPY ra_batches%rowtype) IS
799
800 BEGIN
801
802 arp_util.debug('arp_tbat_pkg.set_to_dummy()+');
803
804 p_batch_rec.batch_id := AR_NUMBER_DUMMY;
805 p_batch_rec.last_update_date := AR_DATE_DUMMY;
806 p_batch_rec.last_updated_by := AR_NUMBER_DUMMY;
807 p_batch_rec.creation_date := AR_DATE_DUMMY;
808 p_batch_rec.created_by := AR_NUMBER_DUMMY;
809 p_batch_rec.last_update_login := AR_NUMBER_DUMMY;
810 p_batch_rec.program_application_id := AR_NUMBER_DUMMY;
811 p_batch_rec.program_id := AR_NUMBER_DUMMY;
812 p_batch_rec.program_update_date := AR_DATE_DUMMY;
813 p_batch_rec.set_of_books_id := AR_NUMBER_DUMMY;
814 p_batch_rec.name := AR_TEXT_DUMMY;
815 p_batch_rec.batch_source_id := AR_NUMBER_DUMMY;
816 p_batch_rec.batch_date := AR_DATE_DUMMY;
817 p_batch_rec.gl_date := AR_DATE_DUMMY;
818 p_batch_rec.status := AR_TEXT_DUMMY;
819 p_batch_rec.type := AR_TEXT_DUMMY;
820 p_batch_rec.control_count := AR_NUMBER_DUMMY;
821 p_batch_rec.control_amount := AR_NUMBER_DUMMY;
822 p_batch_rec.comments := AR_TEXT_DUMMY;
823 p_batch_rec.currency_code := AR_TEXT_DUMMY;
824 p_batch_rec.exchange_rate_type := AR_TEXT_DUMMY;
825 p_batch_rec.exchange_date := AR_DATE_DUMMY;
826 p_batch_rec.exchange_rate := AR_NUMBER_DUMMY;
827 p_batch_rec.purged_children_flag := AR_FLAG_DUMMY;
828 p_batch_rec.attribute_category := AR_TEXT_DUMMY;
829 p_batch_rec.attribute1 := AR_TEXT_DUMMY;
830 p_batch_rec.attribute2 := AR_TEXT_DUMMY;
831 p_batch_rec.attribute3 := AR_TEXT_DUMMY;
832 p_batch_rec.attribute4 := AR_TEXT_DUMMY;
833 p_batch_rec.attribute5 := AR_TEXT_DUMMY;
834 p_batch_rec.attribute6 := AR_TEXT_DUMMY;
835 p_batch_rec.attribute7 := AR_TEXT_DUMMY;
836 p_batch_rec.attribute8 := AR_TEXT_DUMMY;
837 p_batch_rec.attribute9 := AR_TEXT_DUMMY;
838 p_batch_rec.attribute10 := AR_TEXT_DUMMY;
839 p_batch_rec.attribute11 := AR_TEXT_DUMMY;
840 p_batch_rec.attribute12 := AR_TEXT_DUMMY;
841 p_batch_rec.attribute13 := AR_TEXT_DUMMY;
842 p_batch_rec.attribute14 := AR_TEXT_DUMMY;
843 p_batch_rec.attribute15 := AR_TEXT_DUMMY;
844 p_batch_rec.issue_date := AR_DATE_DUMMY;
845 p_batch_rec.maturity_date := AR_DATE_DUMMY;
846 p_batch_rec.special_instructions := AR_TEXT_DUMMY;
847 p_batch_rec.batch_process_status := AR_TEXT_DUMMY;
848 p_batch_rec.selection_criteria_id := AR_NUMBER_DUMMY;
849 p_batch_rec.request_id := AR_NUMBER_DUMMY;
850
851 arp_util.debug('arp_tbat_pkg.set_to_dummy()-');
852
853 EXCEPTION
854 WHEN OTHERS THEN
855 arp_util.debug('EXCEPTION: arp_tbat_pkg.set_to_dummy()');
856 RAISE;
857
858 END;
859
860 /*===========================================================================+
861 | PROCEDURE |
862 | fetch_p |
863 | |
864 | DESCRIPTION |
865 | This procedure fetches a single row from ra_batches into a variable |
866 | specified as a parameter based on the table's primary key, batch_id. |
867 | |
868 | SCOPE - PUBLIC |
869 | |
870 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
871 | arp_util.debug |
872 | |
873 | ARGUMENTS : IN: |
874 | p_batch_id - identifies the record to fetch |
875 | OUT: |
876 | p_batch_rec - contains the fetched record |
877 | |
878 | RETURNS : NONE |
879 | |
880 | NOTES |
881 | |
882 | MODIFICATION HISTORY |
883 | 06-JUN-95 Charlie Tomberg Created |
884 | |
885 +===========================================================================*/
886
887 PROCEDURE fetch_p( p_batch_rec OUT NOCOPY ra_batches%rowtype,
888 p_batch_id IN ra_batches.batch_id%type ) IS
889
890 BEGIN
891 arp_util.debug('arp_tbat_pkg.fetch_p()+');
892
893 SELECT *
894 INTO p_batch_rec
895 FROM ra_batches
896 WHERE batch_id = p_batch_id;
897
898 arp_util.debug('arp_tbat_pkg.fetch_p()-');
899
900 EXCEPTION
901 WHEN OTHERS THEN
902 arp_util.debug('EXCEPTION: arp_tbat_pkg.fetch_p' );
903 RAISE;
904 END;
905
906 /*===========================================================================+
907 | PROCEDURE |
908 | lock_p |
909 | |
910 | DESCRIPTION |
911 | This procedure locks the ra_batches row identified by the p_batch_id |
912 | parameter. |
913 | |
914 | SCOPE - PUBLIC |
915 | |
916 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
917 | arp_util.debug |
918 | |
919 | ARGUMENTS : IN: |
920 | p_batch_id - identifies the row to lock |
921 | OUT: |
922 | None |
923 | |
924 | RETURNS : NONE |
925 | |
926 | NOTES |
927 | |
928 | MODIFICATION HISTORY |
929 | 06-JUN-95 Charlie Tomberg Created |
930 | |
931 +===========================================================================*/
932
933 PROCEDURE lock_p( p_batch_id IN ra_batches.batch_id%type ) IS
934
935 l_batch_id ra_batches.batch_id%type;
936
937 BEGIN
938 arp_util.debug('arp_tbat_pkg.lock_p()+');
939
940 SELECT batch_id
941 INTO l_batch_id
942 FROM ra_batches
943 WHERE batch_id = p_batch_id
944 FOR UPDATE OF batch_id NOWAIT;
945
946 arp_util.debug('arp_tbat_pkg.lock_p()-');
947
948 EXCEPTION
949 WHEN OTHERS THEN
950 arp_util.debug( 'EXCEPTION: arp_tbat_pkg.lock_p' );
951 RAISE;
952 END;
953
954 /*===========================================================================+
955 | PROCEDURE |
956 | lock_fetch_p |
957 | |
958 | DESCRIPTION |
959 | This procedure locks the ra_batches row identified by the p_batch_id |
960 | parameter and populates the p_batch_rec parameter with the row that |
961 | was locked. |
962 | |
963 | SCOPE - PUBLIC |
964 | |
965 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
966 | arp_util.debug |
967 | |
968 | ARGUMENTS : IN: |
969 | p_batch_id - identifies the row to lock |
970 | OUT: |
971 | p_batch_rec - contains the locked row |
972 | |
973 | RETURNS : NONE |
974 | |
975 | NOTES |
976 | |
977 | MODIFICATION HISTORY |
978 | 06-JUN-95 Charlie Tomberg Created |
979 | |
980 +===========================================================================*/
981
982 PROCEDURE lock_fetch_p( p_batch_rec IN OUT NOCOPY ra_batches%rowtype,
983 p_batch_id IN ra_batches.batch_id%type ) IS
984
985 BEGIN
986 arp_util.debug('arp_tbat_pkg.lock_fetch_p()+');
987
988 SELECT *
989 INTO p_batch_rec
990 FROM ra_batches
991 WHERE batch_id = p_batch_id
992 FOR UPDATE OF batch_id NOWAIT;
993
994 arp_util.debug('arp_tbat_pkg.lock_fetch_p()-');
995
996 EXCEPTION
997 WHEN OTHERS THEN
998 arp_util.debug( 'EXCEPTION: arp_tbat_pkg.lock_fetch_p' );
999 RAISE;
1000 END;
1001
1002 /*===========================================================================+
1003 | PROCEDURE |
1004 | lock_compare_p |
1005 | |
1006 | DESCRIPTION |
1007 | This procedure locks the ra_batches row identified by the p_batch_id |
1008 | parameter only if no columns in that row have changed from when they |
1009 | were first selected in the form. |
1010 | |
1011 | SCOPE - PUBLIC |
1012 | |
1013 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1014 | arp_util.debug |
1015 | |
1016 | ARGUMENTS : IN: |
1017 | p_batch_id - identifies the row to lock |
1018 | p_batch_rec - batch record for comparison |
1019 | OUT: |
1020 | None |
1021 | |
1022 | RETURNS : NONE |
1023 | |
1024 | NOTES |
1025 | |
1026 | MODIFICATION HISTORY |
1027 | 06-JUN-95 Charlie Tomberg Created |
1028 | |
1029 | 20-MAR-2000 J Rautiainen Added BR project related columns |
1030 | ISSUE_DATE, MATURITY_DATE, |
1031 | SPECIAL_INSTRUCTIONS, BATCH_PROCESS_STATUS |
1032 | and SELECTION_CRITERIA_ID into table handlers|
1033 | |
1034 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added columns |
1035 | purged_children_flag and |
1036 | request_id |
1037 | into the table handlers. |
1038 | |
1039 | |
1040 +===========================================================================*/
1041
1042 PROCEDURE lock_compare_p( p_batch_rec IN ra_batches%rowtype,
1043 p_batch_id IN ra_batches.batch_id%type ) IS
1044
1045 l_new_batch_rec ra_batches%rowtype;
1046
1047 BEGIN
1048 arp_util.debug('arp_tbat_pkg.lock_compare_p()+');
1049
1050 SELECT *
1051 INTO l_new_batch_rec
1052 FROM ra_batches tbat
1053 WHERE tbat.batch_id = p_batch_id
1054 AND NOT
1055 (
1056 NVL(tbat.name, AR_TEXT_DUMMY) <>
1057 NVL(
1058 DECODE(p_batch_rec.name,
1059 AR_TEXT_DUMMY, tbat.name,
1060 p_batch_rec.name),
1061 AR_TEXT_DUMMY
1062 )
1063 OR
1064 NVL(tbat.batch_id, AR_NUMBER_DUMMY) <>
1065 NVL(
1066 DECODE(p_batch_rec.batch_id,
1067 AR_NUMBER_DUMMY, tbat.batch_id,
1068 p_batch_rec.batch_id),
1069 AR_NUMBER_DUMMY
1070 )
1071 OR
1072 NVL(tbat.last_update_date, AR_DATE_DUMMY) <>
1073 NVL(
1074 DECODE(p_batch_rec.last_update_date,
1075 AR_DATE_DUMMY, tbat.last_update_date,
1076 p_batch_rec.last_update_date),
1077 AR_DATE_DUMMY
1078 )
1079 OR
1080 NVL(tbat.last_updated_by, AR_NUMBER_DUMMY) <>
1081 NVL(
1082 DECODE(p_batch_rec.last_updated_by,
1083 AR_NUMBER_DUMMY, tbat.last_updated_by,
1084 p_batch_rec.last_updated_by),
1085 AR_NUMBER_DUMMY
1086 )
1087 OR
1088 NVL(tbat.creation_date, AR_DATE_DUMMY) <>
1089 NVL(
1090 DECODE(p_batch_rec.creation_date,
1091 AR_DATE_DUMMY, tbat.creation_date,
1092 p_batch_rec.creation_date),
1093 AR_DATE_DUMMY
1094 )
1095 OR
1096 NVL(tbat.created_by, AR_NUMBER_DUMMY) <>
1097 NVL(
1098 DECODE(p_batch_rec.created_by,
1099 AR_NUMBER_DUMMY, tbat.created_by,
1100 p_batch_rec.created_by),
1101 AR_NUMBER_DUMMY
1102 )
1103 OR
1104 NVL(tbat.last_update_login, AR_NUMBER_DUMMY) <>
1105 NVL(
1106 DECODE(p_batch_rec.last_update_login,
1107 AR_NUMBER_DUMMY, tbat.last_update_login,
1108 p_batch_rec.last_update_login),
1109 AR_NUMBER_DUMMY
1110 )
1111 OR
1112 NVL(tbat.program_application_id, AR_NUMBER_DUMMY) <>
1113 NVL(
1114 DECODE(p_batch_rec.program_application_id,
1115 AR_NUMBER_DUMMY, tbat.program_application_id,
1116 p_batch_rec.program_application_id),
1117 AR_NUMBER_DUMMY
1118 )
1119 OR
1120 NVL(tbat.program_id, AR_NUMBER_DUMMY) <>
1121 NVL(
1122 DECODE(p_batch_rec.program_id,
1123 AR_NUMBER_DUMMY, tbat.program_id,
1124 p_batch_rec.program_id),
1125 AR_NUMBER_DUMMY
1126 )
1127 OR
1128 NVL(tbat.program_update_date, AR_DATE_DUMMY) <>
1129 NVL(
1130 DECODE(p_batch_rec.program_update_date,
1131 AR_DATE_DUMMY, tbat.program_update_date,
1132 p_batch_rec.program_update_date),
1133 AR_DATE_DUMMY
1134 )
1135 OR
1136 NVL(tbat.set_of_books_id, AR_NUMBER_DUMMY) <>
1137 NVL(
1138 DECODE(p_batch_rec.set_of_books_id,
1139 AR_NUMBER_DUMMY, tbat.set_of_books_id,
1140 p_batch_rec.set_of_books_id),
1141 AR_NUMBER_DUMMY
1142 )
1143 OR
1144 NVL(tbat.batch_source_id, AR_NUMBER_DUMMY) <>
1145 NVL(
1146 DECODE(p_batch_rec.batch_source_id,
1147 AR_NUMBER_DUMMY, tbat.batch_source_id,
1148 p_batch_rec.batch_source_id),
1149 AR_NUMBER_DUMMY
1150 )
1151 OR
1152 NVL(trunc(tbat.batch_date), AR_DATE_DUMMY) <>
1153 NVL(
1154 DECODE(p_batch_rec.batch_date,
1155 AR_DATE_DUMMY, trunc(tbat.batch_date),
1156 p_batch_rec.batch_date),
1157 AR_DATE_DUMMY
1158 )
1159 OR
1160 NVL(trunc(tbat.gl_date), AR_DATE_DUMMY) <>
1161 NVL(
1162 DECODE(p_batch_rec.gl_date,
1163 AR_DATE_DUMMY, trunc(tbat.gl_date),
1164 p_batch_rec.gl_date),
1165 AR_DATE_DUMMY
1166 )
1167 OR
1168 NVL(tbat.status, AR_TEXT_DUMMY) <>
1169 NVL(
1170 DECODE(p_batch_rec.status,
1171 AR_TEXT_DUMMY, tbat.status,
1172 p_batch_rec.status),
1173 AR_TEXT_DUMMY
1174 )
1175 OR
1176 NVL(tbat.type, AR_TEXT_DUMMY) <>
1177 NVL(
1178 DECODE(p_batch_rec.type,
1179 AR_TEXT_DUMMY, tbat.type,
1180 p_batch_rec.type),
1181 AR_TEXT_DUMMY
1182 )
1183 OR
1184 NVL(tbat.control_count, AR_NUMBER_DUMMY) <>
1185 NVL(
1186 DECODE(p_batch_rec.control_count,
1187 AR_NUMBER_DUMMY, tbat.control_count,
1188 p_batch_rec.control_count),
1189 AR_NUMBER_DUMMY
1190 )
1191 OR
1192 NVL(tbat.control_amount, AR_NUMBER_DUMMY) <>
1193 NVL(
1194 DECODE(p_batch_rec.control_amount,
1195 AR_NUMBER_DUMMY, tbat.control_amount,
1196 p_batch_rec.control_amount),
1197 AR_NUMBER_DUMMY
1198 )
1199 OR
1200 NVL(tbat.comments, AR_TEXT_DUMMY) <>
1201 NVL(
1202 DECODE(p_batch_rec.comments,
1203 AR_TEXT_DUMMY, tbat.comments,
1204 p_batch_rec.comments),
1205 AR_TEXT_DUMMY
1206 )
1207 OR
1208 NVL(tbat.currency_code, AR_TEXT_DUMMY) <>
1209 NVL(
1210 DECODE(p_batch_rec.currency_code,
1211 AR_TEXT_DUMMY, tbat.currency_code,
1212 p_batch_rec.currency_code),
1213 AR_TEXT_DUMMY
1214 )
1215 OR
1216 NVL(tbat.exchange_rate_type, AR_TEXT_DUMMY) <>
1217 NVL(
1218 DECODE(p_batch_rec.exchange_rate_type,
1219 AR_TEXT_DUMMY, tbat.exchange_rate_type,
1220 p_batch_rec.exchange_rate_type),
1221 AR_TEXT_DUMMY
1222 )
1223 OR
1224 NVL(tbat.exchange_date, AR_DATE_DUMMY) <>
1225 NVL(
1226 DECODE(p_batch_rec.exchange_date,
1227 AR_DATE_DUMMY, tbat.exchange_date,
1228 p_batch_rec.exchange_date),
1229 AR_DATE_DUMMY
1230 )
1231 OR
1232 NVL(tbat.exchange_rate, AR_NUMBER_DUMMY) <>
1233 NVL(
1234 DECODE(p_batch_rec.exchange_rate,
1235 AR_NUMBER_DUMMY, tbat.exchange_rate,
1236 p_batch_rec.exchange_rate),
1237 AR_NUMBER_DUMMY
1238 )
1239 OR
1240 NVL(tbat.purged_children_flag, AR_FLAG_DUMMY) <>
1241 NVL(
1242 DECODE(p_batch_rec.purged_children_flag,
1243 AR_FLAG_DUMMY, tbat.purged_children_flag,
1244 p_batch_rec.purged_children_flag),
1245 AR_FLAG_DUMMY
1246 )
1247 OR
1248 NVL(tbat.attribute_category, AR_TEXT_DUMMY) <>
1249 NVL(
1250 DECODE(p_batch_rec.attribute_category,
1251 AR_TEXT_DUMMY, tbat.attribute_category,
1252 p_batch_rec.attribute_category),
1253 AR_TEXT_DUMMY
1254 )
1255 OR
1256 NVL(tbat.attribute1, AR_TEXT_DUMMY) <>
1257 NVL(
1258 DECODE(p_batch_rec.attribute1,
1259 AR_TEXT_DUMMY, tbat.attribute1,
1260 p_batch_rec.attribute1),
1261 AR_TEXT_DUMMY
1262 )
1263 OR
1264 NVL(tbat.attribute2, AR_TEXT_DUMMY) <>
1265 NVL(
1266 DECODE(p_batch_rec.attribute2,
1267 AR_TEXT_DUMMY, tbat.attribute2,
1268 p_batch_rec.attribute2),
1269 AR_TEXT_DUMMY
1270 )
1271 OR
1272 NVL(tbat.attribute3, AR_TEXT_DUMMY) <>
1273 NVL(
1274 DECODE(p_batch_rec.attribute3,
1275 AR_TEXT_DUMMY, tbat.attribute3,
1276 p_batch_rec.attribute3),
1277 AR_TEXT_DUMMY
1278 )
1279 OR
1280 NVL(tbat.attribute4, AR_TEXT_DUMMY) <>
1281 NVL(
1282 DECODE(p_batch_rec.attribute4,
1283 AR_TEXT_DUMMY, tbat.attribute4,
1284 p_batch_rec.attribute4),
1285 AR_TEXT_DUMMY
1286 )
1287 OR
1288 NVL(tbat.attribute5, AR_TEXT_DUMMY) <>
1289 NVL(
1290 DECODE(p_batch_rec.attribute5,
1291 AR_TEXT_DUMMY, tbat.attribute5,
1292 p_batch_rec.attribute5),
1293 AR_TEXT_DUMMY
1294 )
1295 OR
1296 NVL(tbat.attribute6, AR_TEXT_DUMMY) <>
1297 NVL(
1298 DECODE(p_batch_rec.attribute6,
1299 AR_TEXT_DUMMY, tbat.attribute6,
1300 p_batch_rec.attribute6),
1301 AR_TEXT_DUMMY
1302 )
1303 OR
1304 NVL(tbat.attribute7, AR_TEXT_DUMMY) <>
1305 NVL(
1306 DECODE(p_batch_rec.attribute7,
1307 AR_TEXT_DUMMY, tbat.attribute7,
1308 p_batch_rec.attribute7),
1309 AR_TEXT_DUMMY
1310 )
1311 OR
1312 NVL(tbat.attribute8, AR_TEXT_DUMMY) <>
1313 NVL(
1314 DECODE(p_batch_rec.attribute8,
1315 AR_TEXT_DUMMY, tbat.attribute8,
1316 p_batch_rec.attribute8),
1317 AR_TEXT_DUMMY
1318 )
1319 OR
1320 NVL(tbat.attribute9, AR_TEXT_DUMMY) <>
1321 NVL(
1322 DECODE(p_batch_rec.attribute9,
1323 AR_TEXT_DUMMY, tbat.attribute9,
1324 p_batch_rec.attribute9),
1325 AR_TEXT_DUMMY
1326 )
1327 OR
1328 NVL(tbat.attribute10, AR_TEXT_DUMMY) <>
1329 NVL(
1330 DECODE(p_batch_rec.attribute10,
1331 AR_TEXT_DUMMY, tbat.attribute10,
1332 p_batch_rec.attribute10),
1333 AR_TEXT_DUMMY
1334 )
1335 OR
1336 NVL(tbat.attribute11, AR_TEXT_DUMMY) <>
1337 NVL(
1338 DECODE(p_batch_rec.attribute11,
1339 AR_TEXT_DUMMY, tbat.attribute11,
1340 p_batch_rec.attribute11),
1341 AR_TEXT_DUMMY
1342 )
1343 OR
1344 NVL(tbat.attribute12, AR_TEXT_DUMMY) <>
1345 NVL(
1346 DECODE(p_batch_rec.attribute12,
1347 AR_TEXT_DUMMY, tbat.attribute12,
1348 p_batch_rec.attribute12),
1349 AR_TEXT_DUMMY
1350 )
1351 OR
1352 NVL(tbat.attribute13, AR_TEXT_DUMMY) <>
1353 NVL(
1354 DECODE(p_batch_rec.attribute13,
1355 AR_TEXT_DUMMY, tbat.attribute13,
1356 p_batch_rec.attribute13),
1357 AR_TEXT_DUMMY
1358 )
1359 OR
1360 NVL(tbat.attribute14, AR_TEXT_DUMMY) <>
1361 NVL(
1362 DECODE(p_batch_rec.attribute14,
1363 AR_TEXT_DUMMY, tbat.attribute14,
1364 p_batch_rec.attribute14),
1365 AR_TEXT_DUMMY
1366 )
1367 OR
1368 NVL(tbat.attribute15, AR_TEXT_DUMMY) <>
1369 NVL(
1370 DECODE(p_batch_rec.attribute15,
1371 AR_TEXT_DUMMY, tbat.attribute15,
1372 p_batch_rec.attribute15),
1373 AR_TEXT_DUMMY
1374 )
1375 OR
1376 NVL(tbat.issue_date, AR_DATE_DUMMY) <>
1377 NVL(
1378 DECODE(p_batch_rec.issue_date,
1379 AR_DATE_DUMMY, tbat.issue_date,
1380 p_batch_rec.issue_date),
1381 AR_DATE_DUMMY
1382 )
1383 OR
1384 NVL(tbat.maturity_date, AR_DATE_DUMMY) <>
1385 NVL(
1386 DECODE(p_batch_rec.maturity_date,
1387 AR_DATE_DUMMY, tbat.maturity_date,
1388 p_batch_rec.maturity_date),
1389 AR_DATE_DUMMY
1390 )
1391 OR
1392 NVL(tbat.special_instructions, AR_TEXT_DUMMY) <>
1393 NVL(
1394 DECODE(p_batch_rec.special_instructions,
1395 AR_TEXT_DUMMY, tbat.special_instructions,
1396 p_batch_rec.special_instructions),
1397 AR_TEXT_DUMMY
1398 )
1399 OR
1400 NVL(tbat.batch_process_status, AR_TEXT_DUMMY) <>
1401 NVL(
1402 DECODE(p_batch_rec.batch_process_status,
1403 AR_TEXT_DUMMY, tbat.batch_process_status,
1404 p_batch_rec.batch_process_status),
1405 AR_TEXT_DUMMY
1406 )
1407 OR
1408 NVL(tbat.selection_criteria_id, AR_NUMBER_DUMMY) <>
1409 NVL(
1410 DECODE(p_batch_rec.selection_criteria_id,
1411 AR_NUMBER_DUMMY, tbat.selection_criteria_id,
1412 p_batch_rec.selection_criteria_id),
1413 AR_NUMBER_DUMMY
1414 )
1415 OR
1416 NVL(tbat.request_id, AR_NUMBER_DUMMY) <>
1417 NVL(
1418 DECODE(p_batch_rec.request_id,
1419 AR_NUMBER_DUMMY, tbat.request_id,
1420 p_batch_rec.request_id),
1421 AR_NUMBER_DUMMY
1422 )
1423 )
1424 FOR UPDATE OF batch_id NOWAIT;
1425
1426 arp_util.debug('arp_tbat_pkg.lock_compare_p()-');
1427
1428 EXCEPTION
1429 WHEN OTHERS THEN
1430 arp_util.debug( 'EXCEPTION: arp_tbat_pkg.lock_compare_p' );
1431 RAISE;
1432 END;
1433
1434 /*===========================================================================+
1435 | PROCEDURE |
1436 | lock_compare_cover |
1437 | |
1438 | DESCRIPTION |
1439 | Cover for calling the batch table handler lock_compare_p |
1440 | |
1441 | SCOPE - PRIVATE |
1442 | |
1443 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1444 | arp_util.debug |
1445 | |
1446 | ARGUMENTS : IN: |
1447 | p_form_name |
1448 | p_form_version |
1449 | p_batch_id |
1450 | p_name |
1451 | p_batch_source_id |
1452 | p_batch_date |
1453 | p_gl_date |
1454 | p_status |
1455 | p_type |
1456 | p_currency_code |
1457 | p_exchange_rate_type |
1458 | p_exchange_date |
1459 | p_exchange_rate |
1460 | p_control_count |
1461 | p_control_amount |
1462 | p_comments |
1463 | p_set_of_books_id |
1464 | p_purged_children_flag |
1465 | p_attribute_category |
1466 | p_attribute1 - 15 |
1467 | OUT: |
1468 | None |
1469 | IN OUT: |
1470 | None |
1471 | |
1472 | RETURNS : NONE |
1473 | |
1474 | NOTES |
1475 | |
1476 | MODIFICATION HISTORY |
1477 | 10-NOV-95 Subash C Created |
1478 | |
1479 +===========================================================================*/
1480
1481 PROCEDURE lock_compare_cover(
1482 p_form_name IN varchar2,
1483 p_form_version IN number,
1484 p_batch_id IN ra_batches.batch_id%type,
1485 p_name IN ra_batches.name%type,
1486 p_batch_source_id IN ra_batches.batch_source_id%type,
1487 p_batch_date IN ra_batches.batch_date%type,
1488 p_gl_date IN ra_batches.gl_date%type,
1489 p_status IN ra_batches.status%type,
1490 p_type IN ra_batches.type%type,
1491 p_currency_code IN ra_batches.currency_code%type,
1492 p_exchange_rate_type IN ra_batches.exchange_rate_type%type,
1493 p_exchange_date IN ra_batches.exchange_date%type,
1494 p_exchange_rate IN ra_batches.exchange_rate%type,
1495 p_control_count IN ra_batches.control_count%type,
1496 p_control_amount IN ra_batches.control_amount%type,
1497 p_comments IN ra_batches.comments%type,
1498 p_set_of_books_id IN ra_batches.set_of_books_id%type,
1499 p_purged_children_flag IN ra_batches.purged_children_flag%type,
1500 p_attribute_category IN ra_batches.attribute_category%type,
1501 p_attribute1 IN ra_batches.attribute1%type,
1502 p_attribute2 IN ra_batches.attribute2%type,
1503 p_attribute3 IN ra_batches.attribute3%type,
1504 p_attribute4 IN ra_batches.attribute4%type,
1505 p_attribute5 IN ra_batches.attribute5%type,
1506 p_attribute6 IN ra_batches.attribute6%type,
1507 p_attribute7 IN ra_batches.attribute7%type,
1508 p_attribute8 IN ra_batches.attribute8%type,
1509 p_attribute9 IN ra_batches.attribute9%type,
1510 p_attribute10 IN ra_batches.attribute10%type,
1511 p_attribute11 IN ra_batches.attribute11%type,
1512 p_attribute12 IN ra_batches.attribute12%type,
1513 p_attribute13 IN ra_batches.attribute13%type,
1514 p_attribute14 IN ra_batches.attribute14%type,
1515 p_attribute15 IN ra_batches.attribute15%type)
1516 IS
1517 l_batch_rec ra_batches%rowtype;
1518 BEGIN
1519 arp_util.debug('arp_tbat_pkg.lock_compare_cover()+');
1520
1521 arp_tbat_pkg.set_to_dummy(l_batch_rec);
1522
1523 l_batch_rec.batch_id := p_batch_id;
1524 l_batch_rec.name := p_name;
1525 l_batch_rec.batch_source_id := p_batch_source_id;
1526 l_batch_rec.batch_date := trunc(p_batch_date);
1527 l_batch_rec.gl_date := trunc(p_gl_date);
1528 l_batch_rec.status := p_status;
1529 l_batch_rec.type := p_type;
1530 l_batch_rec.currency_code := p_currency_code;
1531 l_batch_rec.exchange_rate_type := p_exchange_rate_type;
1532 l_batch_rec.exchange_date := p_exchange_date;
1533 l_batch_rec.exchange_rate := p_exchange_rate;
1534 l_batch_rec.control_count := p_control_count;
1535 l_batch_rec.control_amount := p_control_amount;
1536 l_batch_rec.comments := p_comments;
1537 l_batch_rec.set_of_books_id := p_set_of_books_id;
1538 l_batch_rec.purged_children_flag := p_purged_children_flag;
1539 l_batch_rec.attribute_category := p_attribute_category;
1540 l_batch_rec.attribute1 := p_attribute1;
1541 l_batch_rec.attribute2 := p_attribute2;
1542 l_batch_rec.attribute3 := p_attribute3;
1543 l_batch_rec.attribute4 := p_attribute4;
1544 l_batch_rec.attribute5 := p_attribute5;
1545 l_batch_rec.attribute6 := p_attribute6;
1546 l_batch_rec.attribute7 := p_attribute7;
1547 l_batch_rec.attribute8 := p_attribute8;
1548 l_batch_rec.attribute9 := p_attribute9;
1549 l_batch_rec.attribute10 := p_attribute10;
1550 l_batch_rec.attribute11 := p_attribute11;
1551 l_batch_rec.attribute12 := p_attribute12;
1552 l_batch_rec.attribute13 := p_attribute13;
1553 l_batch_rec.attribute14 := p_attribute14;
1554 l_batch_rec.attribute15 := p_attribute15;
1555
1556
1557 arp_tbat_pkg.lock_compare_p(l_batch_rec,
1558 p_batch_id);
1559
1560 arp_util.debug('arp_tbat_pkg.lock_compare_cover()-');
1561
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 arp_util.debug('EXCEPTION : arp_tbat_pkg.lock_compare_cover');
1565 arp_util.debug('p_batch_id : '||p_batch_id);
1566 arp_util.debug('p_name : '||p_name);
1567 arp_util.debug('p_batch_source_id : '||p_batch_source_id);
1568 arp_util.debug('p_batch_date : '||p_batch_date);
1569 arp_util.debug('p_gl_date : '||p_gl_date);
1570 arp_util.debug('p_status : '||p_status);
1571 arp_util.debug('p_type : '||p_type);
1572 arp_util.debug('p_currency_code : '||p_currency_code);
1573 arp_util.debug('p_exchange_rate_type : '||p_exchange_rate_type);
1574 arp_util.debug('p_exchange_date : '||p_exchange_date);
1575 arp_util.debug('p_exchange_rate : '||p_exchange_rate);
1576 arp_util.debug('p_control_count : '||p_control_count);
1577 arp_util.debug('p_control_amount : '||p_control_amount);
1578 arp_util.debug('p_comments : '||p_comments);
1579 arp_util.debug('p_set_of_books_id : '||p_set_of_books_id);
1580 arp_util.debug('p_purged_children_flag : '||p_purged_children_flag);
1581 arp_util.debug('p_attribute_category : '||p_attribute_category);
1582 arp_util.debug('p_attribute1 : '||p_attribute1);
1583 arp_util.debug('p_attribute2 : '||p_attribute2);
1584 arp_util.debug('p_attribute3 : '||p_attribute3);
1585 arp_util.debug('p_attribute4 : '||p_attribute4);
1586 arp_util.debug('p_attribute5 : '||p_attribute5);
1587 arp_util.debug('p_attribute6 : '||p_attribute6);
1588 arp_util.debug('p_attribute7 : '||p_attribute7);
1589 arp_util.debug('p_attribute8 : '||p_attribute8);
1590 arp_util.debug('p_attribute9 : '||p_attribute9);
1591 arp_util.debug('p_attribute10 : '||p_attribute10);
1592 arp_util.debug('p_attribute11 : '||p_attribute11);
1593 arp_util.debug('p_attribute12 : '||p_attribute12);
1594 arp_util.debug('p_attribute13 : '||p_attribute13);
1595 arp_util.debug('p_attribute14 : '||p_attribute14);
1596 arp_util.debug('p_attribute15 : '||p_attribute15);
1597
1598 RAISE;
1599 END;
1600
1601 /*===========================================================================+
1602 | PROCEDURE |
1603 | delete_p |
1604 | |
1605 | DESCRIPTION |
1606 | This procedure deletes the ra_batches row identified by the p_batch_id |
1607 | parameter. |
1608 | |
1609 | SCOPE - PUBLIC |
1610 | |
1611 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1612 | arp_util.debug |
1613 | |
1614 | ARGUMENTS : IN: |
1615 | p_batch_id - identifies the row to delete |
1616 | OUT: |
1617 | None |
1618 | |
1619 | RETURNS : NONE |
1620 | |
1621 | NOTES |
1622 | |
1623 | MODIFICATION HISTORY |
1624 | 06-JUN-95 Charlie Tomberg Created |
1625 | |
1626 +===========================================================================*/
1627
1628 procedure delete_p( p_batch_id IN ra_batches.batch_id%type) IS
1629
1630 l_count number;
1631
1632 BEGIN
1633
1634 arp_util.debug('arp_tbat_pkg.delete_p()+');
1635
1636 /*-----------------------------------------------------+
1637 | Determine the number of transactions in the batch. |
1638 | The procedure only deletes batches that do not |
1639 | contain any transactions |
1640 +-----------------------------------------------------*/
1641
1642 SELECT count(*)
1643 INTO l_count
1644 FROM ra_customer_trx
1645 WHERE batch_id = p_batch_id;
1646
1647 IF (l_count = 0)
1648 THEN
1649 delete FROM ra_batches
1650 where batch_id = p_batch_id;
1651 /*---------------------------------+
1652 | Calling central MRC library |
1653 | for MRC integration |
1654 +----------------------------------*/
1655 --{BUG#4301323
1656 -- ar_mrc_engine.maintain_mrc_data(
1657 -- p_event_mode => 'DELETE',
1658 -- p_table_name => 'RA_BATCHES',
1659 -- p_mode => 'SINGLE',
1660 -- p_key_value => p_batch_id );
1661 --}
1662 ELSE
1663 fnd_message.set_name('AR', '250');
1664 app_exception.raise_exception;
1665 END IF;
1666
1667 arp_util.debug('arp_tbat_pkg.delete_p()-');
1668
1669 EXCEPTION
1670 WHEN OTHERS THEN
1671 arp_util.debug('EXCEPTION: arp_tbat_pkg.delete_p()');
1672 RAISE;
1673
1674 END;
1675
1676 /*===========================================================================+
1677 | PROCEDURE |
1678 | delete_f_bs_id |
1679 | |
1680 | DESCRIPTION |
1681 | This procedure deletes the ra_batches rows identified by the |
1682 | p_batch_source_id parameter. |
1683 | |
1684 | SCOPE - PUBLIC |
1685 | |
1686 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1687 | arp_util.debug |
1688 | |
1689 | ARGUMENTS : IN: |
1690 | p_batch_source_id - identifies the rows to delete |
1691 | OUT: |
1692 | None |
1693 | |
1694 | RETURNS : NONE |
1695 | |
1696 | NOTES |
1697 | |
1698 | MODIFICATION HISTORY |
1699 | 06-JUN-95 Charlie Tomberg Created |
1700 | |
1701 +===========================================================================*/
1702
1703 procedure delete_f_bs_id( p_batch_source_id IN
1704 ra_batches.batch_source_id%type) IS
1705
1706 l_count number;
1707
1708 BEGIN
1709
1710 arp_util.debug('arp_tbat_pkg.delete_f_bs_id()+');
1711
1712 /*-----------------------------------------------------+
1713 | Determine the number of transactions in the batch. |
1714 | The procedure only deletes batches that do not |
1715 | contain any transactions |
1716 +-----------------------------------------------------*/
1717
1718 SELECT count(*)
1719 INTO l_count
1720 FROM ra_customer_trx
1721 WHERE batch_source_id = p_batch_source_id;
1722
1723 IF (l_count = 0)
1724 THEN
1725 DELETE FROM ra_batches
1726 WHERE batch_id = p_batch_source_id;
1727 /*---------------------------------+
1728 | Calling central MRC library |
1729 | for MRC integration |
1730 +----------------------------------*/
1731 --{BUG4301323
1732 -- ar_mrc_engine.maintain_mrc_data(
1733 -- p_event_mode => 'DELETE',
1734 -- p_table_name => 'RA_BATCHES',
1735 -- p_mode => 'SINGLE',
1736 -- p_key_value => p_batch_source_id );
1737 --}
1738 ELSE
1739 fnd_message.set_name('AR', '250');
1740 app_exception.raise_exception;
1741 END IF;
1742
1743 arp_util.debug('arp_tbat_pkg.delete_f_bs_id()-');
1744
1745 EXCEPTION
1746 WHEN OTHERS THEN
1747
1748 arp_util.debug('EXCEPTION: arp_tbat_pkg.delete_f_bs_id()');
1749 RAISE;
1750
1751 END;
1752
1753 /*===========================================================================+
1754 | PROCEDURE |
1755 | update_p |
1756 | |
1757 | DESCRIPTION |
1758 | This procedure updates the ra_batches row identified by the p_batch_id |
1759 | parameter. |
1760 | |
1761 | SCOPE - PUBLIC |
1762 | |
1763 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1764 | arp_util.debug |
1765 | |
1766 | ARGUMENTS : IN: |
1767 | p_batch_id - identifies the row to update |
1768 | p_batch_rec - contains the new column values |
1769 | OUT: |
1770 | None |
1771 | |
1772 | RETURNS : NONE |
1773 | |
1774 | NOTES |
1775 | set_to_dummy must be called before the values in p_batch_rec are |
1776 | changed and this function is called. |
1777 | |
1778 | MODIFICATION HISTORY |
1779 | 06-JUN-95 Charlie Tomberg Created |
1780 | |
1781 +===========================================================================*/
1782
1783 PROCEDURE update_p( p_batch_rec IN ra_batches%rowtype,
1784 p_batch_id IN ra_batches.batch_id%type) IS
1785
1786
1787 BEGIN
1788
1789 arp_util.debug('arp_tbat_pkg.update_p()+');
1790
1791 arp_tbat_pkg.generic_update( pg_cursor1,
1792 ' WHERE batch_id = :where_1',
1793 p_batch_id,
1794 p_batch_rec);
1795
1796 arp_util.debug('arp_tbat_pkg.update_p()-');
1797
1798
1799 EXCEPTION
1800 WHEN OTHERS THEN
1801 arp_util.debug('EXCEPTION: arp_tbat_pkg.update_p()');
1802 RAISE;
1803 END;
1804
1805 /*===========================================================================+
1806 | PROCEDURE |
1807 | update_f_bs_id |
1808 | |
1809 | DESCRIPTION |
1810 | This procedure updates the ra_batches rows that are contained in a |
1811 | particular batch source. |
1812 | |
1813 | SCOPE - PUBLIC |
1814 | |
1815 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1816 | arp_util.debug |
1817 | |
1818 | ARGUMENTS : IN: |
1819 | p_batch_source_id - identifies the rows to delete |
1820 | p_batch_rec - contains the new column values |
1821 | OUT: |
1822 | None |
1823 | |
1824 | RETURNS : NONE |
1825 | |
1826 | NOTES |
1827 | set_to_dummy must be called before the values in p_batch_rec are |
1828 | changed and this function is called. |
1829 | |
1830 | MODIFICATION HISTORY |
1831 | 06-JUN-95 Charlie Tomberg Created |
1832 | |
1833 +===========================================================================*/
1834
1835 PROCEDURE update_f_bs_id( p_batch_rec IN ra_batches%rowtype,
1836 p_batch_source_id
1837 IN ra_batch_sources.batch_source_id%type) IS
1838
1839
1840 BEGIN
1841
1842 arp_util.debug('arp_tbat_pkg.update_f_bs_id()+');
1843
1844 arp_tbat_pkg.generic_update( pg_cursor2,
1845 ' WHERE batch_source_id = :where_1',
1846 p_batch_source_id,
1847 p_batch_rec);
1848
1849 arp_util.debug('arp_tbat_pkg.update_f_bs_id()-');
1850
1851
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 arp_util.debug('EXCEPTION: arp_tbat_pkg.update_f_bs_id()');
1855 RAISE;
1856 END;
1857
1858 /*===========================================================================+
1859 | PROCEDURE |
1860 | insert_p |
1861 | |
1862 | DESCRIPTION |
1863 | This procedure inserts a row into ra_batches that contains the column |
1864 | values specified in the p_batch_rec parameter. |
1865 | |
1866 | SCOPE - PUBLIC |
1867 | |
1868 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1869 | arp_util.debug |
1870 | arp_global.set_of_books_id |
1871 | |
1872 | ARGUMENTS : IN: |
1873 | p_batch_rec - contains the new column values |
1874 | OUT: |
1875 | p_batch_id - unique ID of the new row |
1876 | |
1877 | RETURNS : NONE |
1878 | |
1879 | NOTES |
1880 | |
1881 | MODIFICATION HISTORY |
1882 | 06-JUN-95 Charlie Tomberg Created |
1883 | |
1884 | 20-MAR-2000 J Rautiainen Added BR project related columns |
1885 | ISSUE_DATE, MATURITY_DATE, |
1886 | SPECIAL_INSTRUCTIONS, BATCH_PROCESS_STATUS |
1887 | and SELECTION_CRITERIA_ID into table handlers|
1888 | |
1889 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column |
1890 | purged_children_flag and |
1891 | request_id |
1892 | into the table handlers. |
1893 | |
1894 +===========================================================================*/
1895
1896 PROCEDURE insert_p(
1897 p_batch_rec IN ra_batches%rowtype,
1898 p_batch_id OUT NOCOPY ra_batches.batch_id%type,
1899 p_name OUT NOCOPY ra_batches.name%type
1900 ) IS
1901
1902
1903 l_batch_id ra_batches.batch_id%type;
1904 l_batch_name ra_batches.name%type;
1905 l_ra_batches_value_list gl_ca_utility_pkg.r_key_value_arr; /* MRC */
1906
1907 BEGIN
1908
1909 arp_util.debug('arp_tbat_pkg.insert_p()+');
1910
1911 p_batch_id := '';
1912
1913 /*---------------------------*
1914 | Get the unique identifier |
1915 *---------------------------*/
1916
1917 SELECT RA_BATCHES_S.NEXTVAL
1918 INTO l_batch_id
1919 FROM DUAL;
1920
1921 /*-----------------------------------------------------------------*
1922 | Get the batch name if the source uses automatic batch numbering |
1923 *-----------------------------------------------------------------*/
1924
1925 IF (p_batch_rec.name is null)
1926 THEN
1927 SELECT to_char(last_batch_num + 1)
1928 INTO l_batch_name
1929 FROM ra_batch_sources
1930 WHERE batch_source_id = p_batch_rec.batch_source_id
1931 AND auto_batch_numbering_flag = 'Y'
1932 FOR UPDATE OF last_batch_num NOWAIT;
1933
1934 UPDATE ra_batch_sources
1935 SET last_batch_num = l_batch_name
1936 WHERE batch_source_id = p_batch_rec.batch_source_id;
1937 ELSE
1938 l_batch_name := p_batch_rec.name;
1939 END IF;
1940
1941 p_name := l_batch_name;
1942
1943 /*-------------------*
1944 | Insert the record |
1945 *-------------------*/
1946
1947 INSERT INTO ra_batches
1948 (
1949 batch_id,
1950 last_update_date,
1951 last_updated_by,
1952 creation_date,
1953 created_by,
1954 last_update_login,
1955 program_application_id,
1956 program_id,
1957 program_update_date,
1958 set_of_books_id,
1959 name,
1960 batch_source_id,
1961 batch_date,
1962 gl_date,
1963 status,
1964 type,
1965 control_count,
1966 control_amount,
1967 comments,
1968 currency_code,
1969 exchange_rate_type,
1970 exchange_date,
1971 exchange_rate,
1972 purged_children_flag,
1973 attribute_category,
1974 attribute1,
1975 attribute2,
1976 attribute3,
1977 attribute4,
1978 attribute5,
1979 attribute6,
1980 attribute7,
1981 attribute8,
1982 attribute9,
1983 attribute10,
1984 attribute11,
1985 attribute12,
1986 attribute13,
1987 attribute14,
1988 attribute15,
1989 issue_date,
1990 maturity_date,
1991 special_instructions,
1992 batch_process_status,
1993 selection_criteria_id,
1994 request_id
1995 ,org_id
1996 )
1997 VALUES
1998 (
1999 l_batch_id, /* batch_id */
2000 sysdate, /* last_update_date */
2001 pg_user_id, /* last_updated_by */
2002 sysdate, /* creation_date */
2003 pg_user_id, /* created_by */
2004 nvl(pg_conc_login_id,
2005 pg_login_id), /* last_update_login */
2006 pg_prog_appl_id, /* program_application_id */
2007 pg_conc_program_id, /* program_id */
2008 sysdate, /* program_update_date */
2009 arp_global.set_of_books_id, /* set_of_books_id */
2010 l_batch_name, /* name */
2011 p_batch_rec.batch_source_id, /* batch_source_id */
2012 p_batch_rec.batch_date, /* batch_date */
2013 p_batch_rec.gl_date, /* gl_date */
2014 p_batch_rec.status, /* status */
2015 p_batch_rec.type, /* type */
2016 p_batch_rec.control_count, /* control_count */
2017 p_batch_rec.control_amount, /* control_amount */
2018 p_batch_rec.comments, /* comments */
2019 p_batch_rec.currency_code, /* currency_code */
2020 p_batch_rec.exchange_rate_type, /* exchange_rate_type */
2021 p_batch_rec.exchange_date, /* exchange_date */
2022 p_batch_rec.exchange_rate, /* exchange_rate */
2023 p_batch_rec.purged_children_flag,/*purged_children_flag*/
2024 p_batch_rec.attribute_category, /* attribute_category */
2025 p_batch_rec.attribute1, /* attribute1 */
2026 p_batch_rec.attribute2, /* attribute2 */
2027 p_batch_rec.attribute3, /* attribute3 */
2028 p_batch_rec.attribute4, /* attribute4 */
2029 p_batch_rec.attribute5, /* attribute5 */
2030 p_batch_rec.attribute6, /* attribute6 */
2031 p_batch_rec.attribute7, /* attribute7 */
2032 p_batch_rec.attribute8, /* attribute8 */
2033 p_batch_rec.attribute9, /* attribute9 */
2034 p_batch_rec.attribute10, /* attribute10 */
2035 p_batch_rec.attribute11, /* attribute11 */
2036 p_batch_rec.attribute12, /* attribute12 */
2037 p_batch_rec.attribute13, /* attribute13 */
2038 p_batch_rec.attribute14, /* attribute14 */
2039 p_batch_rec.attribute15, /* attribute15 */
2040 p_batch_rec.issue_date,
2041 p_batch_rec.maturity_date,
2042 p_batch_rec.special_instructions,
2043 p_batch_rec.batch_process_status,
2044 p_batch_rec.selection_criteria_id,
2045 p_batch_rec.request_id /*request_id*/
2046 ,arp_standard.sysparm.org_id /* SSA changes anuj */
2047 );
2048
2049 p_batch_id := l_batch_id;
2050
2051 /*---------------------------------+
2052 | Calling central MRC library |
2053 | for MRC integration |
2054 +----------------------------------*/
2055 --{BUG4301323
2056 -- ar_mrc_engine.maintain_mrc_data(
2057 -- p_event_mode => 'INSERT',
2058 -- p_table_name => 'RA_BATCHES',
2059 -- p_mode => 'SINGLE',
2060 -- p_key_value => l_batch_id);
2061 --}
2062 arp_util.debug('arp_tbat_pkg.insert_p()-');
2063
2064 EXCEPTION
2065 WHEN OTHERS THEN
2066 arp_util.debug('EXCEPTION: arp_tbat_pkg.insert_p()');
2067 RAISE;
2068 END;
2069
2070
2071 /*---------------------------------------------+
2072 | Package initialization section. |
2073 | Sets WHO column variables for later use. |
2074 +---------------------------------------------*/
2075
2076 BEGIN
2077
2078 pg_user_id := fnd_global.user_id;
2079 pg_conc_login_id := fnd_global.conc_login_id;
2080 pg_login_id := fnd_global.login_id;
2081 pg_prog_appl_id := fnd_global.prog_appl_id;
2082 pg_conc_program_id := fnd_global.conc_program_id;
2083
2084
2085 END ARP_TBAT_PKG;