[Home] [Help]
PACKAGE BODY: APPS.ARP_CMA_PKG
Source
1 PACKAGE BODY ARP_CMA_PKG AS
2 /* $Header: ARTICMAB.pls 115.6 2003/04/11 21:12:44 mraymond ship $ */
3
4 /*--------------------------------------------------------+
5 | Dummy constants for use in update and lock operations |
6 +--------------------------------------------------------*/
7
8 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
9 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
10
11 /*---------------------------------------------------------------+
12 | Package global variables to hold the parsed update cursors. |
13 | This allows the cursors to be reused without being reparsed. |
14 +---------------------------------------------------------------*/
15
16 pg_cursor1 integer := '';
17 pg_cursor2 integer := '';
18 pg_cursor3 integer := '';
19 pg_cursor4 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 /*===========================================================================+
33 | FUNCTION |
34 | compare_cma_records |
35 | |
36 | DESCRIPTION |
37 | This function compares two ar_credit_memo_amounts records to |
38 | determine if any columns in the two records are different. If a |
39 | given column in the p_old_cma_rec record contains the dummy |
40 | constant, that column is not used in the comparison. |
41 | |
42 | SCOPE - PRIVATE |
43 | |
44 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
45 | arp_util.debug |
46 | |
47 | ARGUMENTS : IN: |
48 | p_old_cma_rec - first cma record |
49 | p_new_cma_rec - second cma record |
50 | OUT: |
51 | None |
52 | |
53 | RETURNS : TRUE if all columns in the cma records are the same, |
54 | FALSE if any column in the cma records are different. |
55 | |
56 | NOTES |
57 | |
58 | MODIFICATION HISTORY |
59 | 27-JUN-95 Charlie Tomberg Created |
60 | |
61 +===========================================================================*/
62
63 FUNCTION compare_cma_records( p_old_cma_rec IN
64 ar_credit_memo_amounts%rowtype,
65 p_new_cma_rec IN
66 ar_credit_memo_amounts%rowtype)
67 RETURN BOOLEAN IS
68 l_result boolean;
69 l_sql_result varchar2(2);
70
71 BEGIN
72 arp_util.debug('arp_cma_pkg.compare_cma_records()+');
73
74
75 select DECODE(max(dummy),
76 '', 'N',
77 'Y')
78 INTO l_sql_result
79 FROM dual
80 WHERE
81 (
82 p_old_cma_rec.credit_memo_amount_id ||
83 p_old_cma_rec.last_updated_by ||
84 p_old_cma_rec.last_update_date ||
85 p_old_cma_rec.last_update_login ||
86 p_old_cma_rec.created_by ||
87 p_old_cma_rec.creation_date ||
88 p_old_cma_rec.customer_trx_line_id ||
89 p_old_cma_rec.gl_date ||
90 p_old_cma_rec.amount ||
91 p_old_cma_rec.program_application_id ||
92 p_old_cma_rec.program_id ||
93 p_old_cma_rec.program_update_date ||
94 p_old_cma_rec.request_id
95 =
96 DECODE(p_old_cma_rec.credit_memo_amount_id,
97 AR_NUMBER_DUMMY, p_old_cma_rec.credit_memo_amount_id,
98 p_new_cma_rec.credit_memo_amount_id) ||
99 DECODE(p_old_cma_rec.last_updated_by,
100 AR_NUMBER_DUMMY, p_old_cma_rec.last_updated_by,
101 p_new_cma_rec.last_updated_by) ||
102 DECODE(p_old_cma_rec.last_update_date,
103 AR_DATE_DUMMY, p_old_cma_rec.last_update_date,
104 p_new_cma_rec.last_update_date) ||
105 DECODE(p_old_cma_rec.last_update_login,
106 AR_NUMBER_DUMMY, p_old_cma_rec.last_update_login,
107 p_new_cma_rec.last_update_login) ||
108 DECODE(p_old_cma_rec.created_by,
109 AR_NUMBER_DUMMY, p_old_cma_rec.created_by,
110 p_new_cma_rec.created_by) ||
111 DECODE(p_old_cma_rec.creation_date,
112 AR_DATE_DUMMY, p_old_cma_rec.creation_date,
113 p_new_cma_rec.creation_date) ||
114 DECODE(p_old_cma_rec.customer_trx_line_id,
115 AR_NUMBER_DUMMY, p_old_cma_rec.customer_trx_line_id,
116 p_new_cma_rec.customer_trx_line_id) ||
117 DECODE(p_old_cma_rec.gl_date,
118 AR_DATE_DUMMY, p_old_cma_rec.gl_date,
119 p_new_cma_rec.gl_date) ||
120 DECODE(p_old_cma_rec.amount,
121 AR_NUMBER_DUMMY, p_old_cma_rec.amount,
122 p_new_cma_rec.amount) ||
123 DECODE(p_old_cma_rec.program_application_id,
124 AR_NUMBER_DUMMY, p_old_cma_rec.program_application_id,
125 p_new_cma_rec.program_application_id) ||
126 DECODE(p_old_cma_rec.program_id,
127 AR_NUMBER_DUMMY, p_old_cma_rec.program_id,
128 p_new_cma_rec.program_id) ||
129 DECODE(p_old_cma_rec.program_update_date,
130 AR_DATE_DUMMY, p_old_cma_rec.program_update_date,
131 p_new_cma_rec.program_update_date) ||
132 DECODE(p_old_cma_rec.request_id,
133 AR_NUMBER_DUMMY, p_old_cma_rec.request_id,
134 p_new_cma_rec.request_id)
135 );
136
137
138 if (l_sql_result = 'N')
139 then l_result := FALSE;
140 arp_util.debug(
141 'arp_cma_pkg.compare_cma_records(): result: FALSE');
142 else l_result := TRUE;
143 arp_util.debug(
144 'arp_cma_pkg.compare_cma_records(): result: TRUE');
145 END IF;
146
147 arp_util.debug('arp_cma_pkg.compare_cma_records()-');
148
149 return(l_result);
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 arp_util.debug( 'EXCEPTION: arp_cma_pkg.compare_cma_records' );
154 RAISE;
155 END;
156
157 /*===========================================================================+
158 | PROCEDURE |
159 | bind_cma_variables |
160 | |
161 | DESCRIPTION |
162 | Binds variables from the record variable to the bind variables |
163 | in the dynamic SQL update statement. |
164 | |
165 | SCOPE - PRIVATE |
166 | |
167 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
168 | dbms_sql.bind_variable |
169 | arp_util.debug |
170 | |
171 | ARGUMENTS : IN: |
172 | p_update_cursor - ID of the update cursor |
173 | p_cma_rec - ar_credit_memo_amounts record |
174 | OUT: |
175 | None |
176 | |
177 | RETURNS : NONE |
178 | |
179 | NOTES |
180 | |
181 | MODIFICATION HISTORY |
182 | 27-JUN-95 Charlie Tomberg Created |
183 | |
184 +===========================================================================*/
185
186
187 PROCEDURE bind_cma_variables(p_update_cursor IN integer,
188 p_cma_rec IN ar_credit_memo_amounts%rowtype)
189 IS
190
191 BEGIN
192
193 arp_util.debug('arp_cma_pkg.bind_cma_variables()+');
194
195 /*------------------+
196 | Dummy constants |
197 +------------------*/
198
199 dbms_sql.bind_variable(p_update_cursor, ':ar_number_dummy',
200 AR_NUMBER_DUMMY);
201
202 dbms_sql.bind_variable(p_update_cursor, ':ar_date_dummy',
203 AR_DATE_DUMMY);
204
205 /*------------------+
206 | WHO variables |
207 +------------------*/
208
209 dbms_sql.bind_variable(p_update_cursor, ':pg_user_id',
210 pg_user_id);
211
212 dbms_sql.bind_variable(p_update_cursor, ':pg_login_id',
213 pg_login_id);
214
215 dbms_sql.bind_variable(p_update_cursor, ':pg_conc_login_id',
216 pg_conc_login_id);
217
218
219 /*----------------------------------------------+
220 | Bind variables for all columns in the table |
221 +----------------------------------------------*/
222
223
224 dbms_sql.bind_variable(p_update_cursor, ':credit_memo_amount_id',
225 p_cma_rec.credit_memo_amount_id);
226
227 dbms_sql.bind_variable(p_update_cursor, ':credit_memo_amount_id',
228 p_cma_rec.credit_memo_amount_id);
229
230 dbms_sql.bind_variable(p_update_cursor, ':last_updated_by',
231 p_cma_rec.last_updated_by);
232
233 dbms_sql.bind_variable(p_update_cursor, ':last_update_date',
234 p_cma_rec.last_update_date);
235
236 dbms_sql.bind_variable(p_update_cursor, ':last_update_login',
237 p_cma_rec.last_update_login);
238
239 dbms_sql.bind_variable(p_update_cursor, ':created_by',
240 p_cma_rec.created_by);
241
242 dbms_sql.bind_variable(p_update_cursor, ':creation_date',
243 p_cma_rec.creation_date);
244
245 dbms_sql.bind_variable(p_update_cursor, ':customer_trx_line_id',
246 p_cma_rec.customer_trx_line_id);
247
248 dbms_sql.bind_variable(p_update_cursor, ':gl_date',
249 p_cma_rec.gl_date);
250
251 dbms_sql.bind_variable(p_update_cursor, ':amount',
252 p_cma_rec.amount);
253
254 dbms_sql.bind_variable(p_update_cursor, ':program_application_id',
255 p_cma_rec.program_application_id);
256
257 dbms_sql.bind_variable(p_update_cursor, ':program_id',
258 p_cma_rec.program_id);
259
260 dbms_sql.bind_variable(p_update_cursor, ':program_update_date',
261 p_cma_rec.program_update_date);
262
263 dbms_sql.bind_variable(p_update_cursor, ':request_id',
264 p_cma_rec.request_id);
265
266
267 arp_util.debug('arp_cma_pkg.bind_cma_variables()-');
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 arp_util.debug('EXCEPTION: arp_cma_pkg.bind_cma_variables()');
272 RAISE;
273
274 END;
275
276
277 /*===========================================================================+
278 | PROCEDURE |
279 | construct_cma_update_stmt |
280 | |
281 | DESCRIPTION |
282 | Copies the text of the dynamic SQL update statement into the |
283 | out NOCOPY paramater. The update statement does not contain a where clause |
284 | since this is the dynamic part that is added later. |
285 | |
286 | SCOPE - PRIVATE |
287 | |
288 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
289 | arp_util.debug |
290 | |
291 | ARGUMENTS : IN: |
292 | None. |
293 | OUT: |
294 | update_text - text of the update statement |
295 | |
296 | RETURNS : NONE |
297 | |
298 | NOTES |
299 | This statement only updates columns in the cma record that do not |
300 | contain the dummy values that indicate that they should not be changed.|
301 | |
302 | MODIFICATION HISTORY |
303 | 27-JUN-95 Charlie Tomberg Created |
304 | |
305 +===========================================================================*/
306
307 PROCEDURE construct_cma_update_stmt( update_text OUT NOCOPY varchar2) IS
308
309 BEGIN
310 arp_util.debug('arp_cma_pkg.construct_cma_update_stmt()+');
311
312 update_text :=
313 'UPDATE ar_credit_memo_amounts
314 SET credit_memo_amount_id =
315 DECODE(:credit_memo_amount_id,
316 :ar_number_dummy, credit_memo_amount_id,
317 :credit_memo_amount_id),
318 last_updated_by =
319 DECODE(:last_updated_by,
320 :ar_number_dummy, :pg_user_id,
321 :last_updated_by),
322 last_update_date =
323 DECODE(:last_update_date,
324 :ar_date_dummy, sysdate,
325 :last_update_date),
326 last_update_login =
327 DECODE(:last_update_login,
328 :ar_number_dummy, nvl(:pg_conc_login_id,
332 DECODE(:created_by,
329 :pg_login_id),
330 :last_update_login),
331 created_by =
333 :ar_number_dummy, created_by,
334 :created_by),
335 creation_date =
336 DECODE(:creation_date,
337 :ar_date_dummy, creation_date,
338 :creation_date),
339 customer_trx_line_id =
340 DECODE(:customer_trx_line_id,
341 :ar_number_dummy, customer_trx_line_id,
342 :customer_trx_line_id),
343 gl_date =
344 DECODE(:gl_date,
345 :ar_date_dummy, gl_date,
346 :gl_date),
347 amount =
348 DECODE(:amount,
349 :ar_number_dummy, amount,
350 :amount),
351 program_application_id =
352 DECODE(:program_application_id,
353 :ar_number_dummy, program_application_id,
354 :program_application_id),
355 program_id =
356 DECODE(:program_id,
357 :ar_number_dummy, program_id,
358 :program_id),
359 program_update_date =
360 DECODE(:program_update_date,
361 :ar_date_dummy, program_update_date,
362 :program_update_date),
363 request_id =
364 DECODE(:request_id,
365 :ar_number_dummy, request_id,
366 :request_id)';
367
368 arp_util.debug('arp_cma_pkg.construct_cma_update_stmt()-');
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 arp_util.debug('EXCEPTION: arp_cma_pkg.construct_cma_update_stmt()');
373 RAISE;
374
375 END;
376
377 /*===========================================================================+
378 | PROCEDURE |
379 | generic_update |
380 | |
381 | DESCRIPTION |
382 | This procedure Updates records in ar_credit_memo_amounts |
383 | identified by the where clause that is passed in as a parameter. Only |
384 | those columns in the cma record parameter that do not contain the |
385 | special dummy values are updated. |
386 | |
387 | SCOPE - PRIVATE |
388 | |
389 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
390 | arp_util.debug |
391 | dbms_sql.open_cursor |
392 | dbms_sql.parse |
393 | dbms_sql.execute |
394 | dbms_sql.close_cursor |
395 | |
396 | ARGUMENTS : IN: |
397 | p_update_cursor - identifies the cursor to use |
398 | p_where_clause - identifies which rows to update |
399 | p_where1 - value to bind into where clause |
400 | p_cma_rec - contains the new cma values |
401 | OUT: |
402 | None |
403 | |
404 | RETURNS : NONE |
405 | |
406 | NOTES |
407 | |
408 | MODIFICATION HISTORY |
409 | 27-JUN-95 Charlie Tomberg Created |
410 | |
411 +===========================================================================*/
412
413 PROCEDURE generic_update(p_update_cursor IN OUT NOCOPY integer,
414 p_where_clause IN varchar2,
415 p_where1 IN number,
416 p_cma_rec IN ar_credit_memo_amounts%rowtype) IS
417
418 l_count number;
419 l_update_statement varchar2(25000);
420
421 BEGIN
422 arp_util.debug('arp_cma_pkg.generic_update()+');
423
424 /*--------------------------------------------------------------+
425 | If this update statement has not already been parsed, |
426 | construct the statement and parse it. |
427 | Otherwise, use the already parsed statement and rebind its |
428 | variables. |
429 +--------------------------------------------------------------*/
430
431 IF (p_update_cursor IS NULL)
432 THEN
433
434 p_update_cursor := dbms_sql.open_cursor;
435
439
436 /*---------------------------------+
437 | Construct the update statement |
438 +---------------------------------*/
440 arp_cma_pkg.construct_cma_update_stmt(l_update_statement);
441
442 l_update_statement := l_update_statement || p_where_clause;
443
444 /*-----------------------------------------------+
445 | Parse, bind, execute and close the statement |
446 +-----------------------------------------------*/
447
448 dbms_sql.parse(p_update_cursor,
449 l_update_statement,
450 dbms_sql.v7);
451
452 END IF;
453
454 arp_cma_pkg.bind_cma_variables(p_update_cursor, p_cma_rec);
455
456 /*-----------------------------------------+
457 | Bind the variables in the where clause |
458 +-----------------------------------------*/
459
460 dbms_sql.bind_variable(p_update_cursor, ':where_1',
461 p_where1);
462
463 l_count := dbms_sql.execute(p_update_cursor);
464
465 arp_util.debug( to_char(l_count) || ' rows updated');
466
467
468 /*------------------------------------------------------------+
469 | Raise the NO_DATA_FOUND exception if no rows were updated |
470 +------------------------------------------------------------*/
471
472 IF (l_count = 0)
473 THEN RAISE NO_DATA_FOUND;
474 END IF;
475
476
477 arp_util.debug('arp_cma_pkg.generic_update()-');
478
479 EXCEPTION
480 WHEN OTHERS THEN
481 arp_util.debug('EXCEPTION: arp_cma_pkg.generic_update()');
482 arp_util.debug(l_update_statement);
483 arp_util.debug('Error at character: ' ||
484 to_char(dbms_sql.last_error_position));
485 RAISE;
486 END;
487
488 /*===========================================================================+
489 | PROCEDURE |
490 | set_to_dummy |
491 | |
492 | DESCRIPTION |
493 | This procedure initializes all columns in the parameter cma record |
494 | to the appropriate dummy value for its datatype. |
495 | |
496 | The dummy values are defined in the following package level constants: |
497 | AR_NUMBER_DUMMY |
498 | AR_DATE_DUMMY |
499 | |
500 | SCOPE - PUBLIC |
501 | |
502 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
503 | arp_util.debug |
504 | |
505 | ARGUMENTS : IN: |
506 | None |
507 | OUT: |
508 | p_cma_rec - The record to initialize |
509 | |
510 | RETURNS : NONE |
511 | |
512 | NOTES |
513 | |
514 | MODIFICATION HISTORY |
515 | 27-JUN-95 Charlie Tomberg Created |
516 | |
517 +===========================================================================*/
518
519 PROCEDURE set_to_dummy( p_cma_rec OUT NOCOPY ar_credit_memo_amounts%rowtype) IS
520
521 BEGIN
522
523 arp_util.debug('arp_cma_pkg.set_to_dummy()+');
524
525 p_cma_rec.credit_memo_amount_id := AR_NUMBER_DUMMY;
526 p_cma_rec.last_updated_by := AR_NUMBER_DUMMY;
527 p_cma_rec.last_update_date := AR_DATE_DUMMY;
528 p_cma_rec.last_update_login := AR_NUMBER_DUMMY;
529 p_cma_rec.created_by := AR_NUMBER_DUMMY;
530 p_cma_rec.creation_date := AR_DATE_DUMMY;
531 p_cma_rec.customer_trx_line_id := AR_NUMBER_DUMMY;
532 p_cma_rec.gl_date := AR_DATE_DUMMY;
533 p_cma_rec.amount := AR_NUMBER_DUMMY;
534 p_cma_rec.program_application_id := AR_NUMBER_DUMMY;
535 p_cma_rec.program_id := AR_NUMBER_DUMMY;
536 p_cma_rec.program_update_date := AR_DATE_DUMMY;
537 p_cma_rec.request_id := AR_NUMBER_DUMMY;
538
539 arp_util.debug('arp_cma_pkg.set_to_dummy()-');
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 arp_util.debug('EXCEPTION: arp_cma_pkg.set_to_dummy()');
544 RAISE;
545
546 END;
547
548
549 /*===========================================================================+
550 | PROCEDURE |
551 | lock_p |
552 | |
553 | DESCRIPTION |
557 | SCOPE - PUBLIC |
554 | This procedure locks the ar_credit_memo_amounts row identified by |
555 | p_credit_memo_amount_id parameter. |
556 | |
558 | |
559 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
560 | arp_util.debug |
561 | |
562 | ARGUMENTS : IN: |
563 | p_credit_memo_amount_id - identifies the row to lock |
564 | OUT: |
565 | None |
566 | |
567 | RETURNS : NONE |
568 | |
569 | NOTES |
570 | |
571 | MODIFICATION HISTORY |
572 | 27-JUN-95 Charlie Tomberg Created |
573 | |
574 +===========================================================================*/
575
576 PROCEDURE lock_p( p_credit_memo_amount_id
577 IN ar_credit_memo_amounts.credit_memo_amount_id%type
578 )
579 IS
580
581 l_credit_memo_amount_id
582 ar_credit_memo_amounts.credit_memo_amount_id%type;
583
584 BEGIN
585 arp_util.debug('arp_cma_pkg.lock_p()+');
586
587
588 SELECT credit_memo_amount_id
589 INTO l_credit_memo_amount_id
590 FROM ar_credit_memo_amounts
591 WHERE credit_memo_amount_id = p_credit_memo_amount_id
592 FOR UPDATE OF credit_memo_amount_id NOWAIT;
593
594 arp_util.debug('arp_cma_pkg.lock_p()-');
595
596 EXCEPTION
597 WHEN OTHERS THEN
598 arp_util.debug( 'EXCEPTION: arp_cma_pkg.lock_p' );
599 RAISE;
600 END;
601
602 /*===========================================================================+
603 | PROCEDURE |
604 | lock_f_ctl_id |
605 | |
606 | DESCRIPTION |
607 | This procedure locks the ar_credit_memo_amounts rows identified by |
608 | p_customer_trx_line_id parameter. |
609 | |
610 | SCOPE - PUBLIC |
611 | |
612 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
613 | arp_util.debug |
614 | |
615 | ARGUMENTS : IN: |
616 | p_customer_trx_line_id - identifies the rows to lock |
617 | OUT: |
618 | None |
619 | |
620 | RETURNS : NONE |
621 | |
622 | NOTES |
623 | |
624 | MODIFICATION HISTORY |
625 | 27-JUN-95 Charlie Tomberg Created |
626 | |
627 +===========================================================================*/
628
629 PROCEDURE lock_f_ctl_id( p_customer_trx_line_id
630 IN ra_customer_trx_lines.customer_trx_line_id%type)
631 IS
632
633 CURSOR lock_c IS
634 SELECT credit_memo_amount_id
635 FROM ar_credit_memo_amounts
636 WHERE customer_trx_line_id = p_customer_trx_line_id
637 FOR UPDATE OF credit_memo_amount_id NOWAIT;
638
639 BEGIN
640 arp_util.debug('arp_cma_pkg.lock_f_ctl_id()+');
641
642 OPEN lock_c;
643 CLOSE lock_c;
644
645 arp_util.debug('arp_cma_pkg.lock_f_ctl_id()-');
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 arp_util.debug( 'EXCEPTION: arp_cma_pkg.lock_f_ctl_id' );
650 RAISE;
651 END;
652
653 /*===========================================================================+
654 | PROCEDURE |
655 | lock_fetch_p |
656 | |
657 | DESCRIPTION |
661 | |
658 | This procedure locks the ar_credit_memo_amounts row identified |
659 | by the p_credit_memo_amount_id parameter and populates the |
660 | p_cma_rec parameter with the row that was locked. |
662 | SCOPE - PUBLIC |
663 | |
664 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
665 | arp_util.debug |
666 | |
667 | ARGUMENTS : IN: |
668 | p_credit_memo_amount_id - identifies the row to lock |
669 | OUT: |
670 | p_cma_rec - contains the locked row |
671 | |
672 | RETURNS : NONE |
673 | |
674 | NOTES |
675 | |
676 | MODIFICATION HISTORY |
677 | 27-JUN-95 Charlie Tomberg Created |
678 | |
679 +===========================================================================*/
680
681 PROCEDURE lock_fetch_p( p_cma_rec IN OUT NOCOPY ar_credit_memo_amounts%rowtype,
682 p_credit_memo_amount_id IN
683 ar_credit_memo_amounts.credit_memo_amount_id%type) IS
684
685 BEGIN
686 arp_util.debug('arp_cma_pkg.lock_fetch_p()+');
687
688 SELECT *
689 INTO p_cma_rec
690 FROM ar_credit_memo_amounts
691 WHERE credit_memo_amount_id = p_credit_memo_amount_id
692 FOR UPDATE OF credit_memo_amount_id NOWAIT;
693
694 arp_util.debug('arp_cma_pkg.lock_fetch_p()-');
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 arp_util.debug( 'EXCEPTION: arp_cma_pkg.lock_fetch_p' );
699 RAISE;
700 END;
701
702 /*===========================================================================+
703 | PROCEDURE |
704 | lock_compare_p |
705 | |
706 | DESCRIPTION |
707 | This procedure locks the ar_credit_memo_amounts row identified |
708 | by the p_credit_memo_amount_id parameter only if no columns in |
709 | that row have changed from when they were first selected in the form. |
710 | |
711 | SCOPE - PUBLIC |
712 | |
713 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
714 | arp_util.debug |
715 | |
716 | ARGUMENTS : IN: |
717 | p_credit_memo_amount_id - identifies the row to lock |
718 | p_cma_rec - cma record for comparison |
719 | OUT: |
720 | None |
721 | |
722 | RETURNS : NONE |
723 | |
724 | NOTES |
725 | |
726 | MODIFICATION HISTORY |
727 | 27-JUN-95 Charlie Tomberg Created |
728 | |
729 +===========================================================================*/
730
731 PROCEDURE lock_compare_p( p_cma_rec IN ar_credit_memo_amounts%rowtype,
732 p_credit_memo_amount_id IN
733 ar_credit_memo_amounts.credit_memo_amount_id%type) IS
734
735 l_new_cma_rec ar_credit_memo_amounts%rowtype;
736
737 BEGIN
738 arp_util.debug('arp_cma_pkg.lock_compare_p()+');
739
740 SELECT *
741 INTO l_new_cma_rec
742 FROM ar_credit_memo_amounts cma
743 WHERE cma.credit_memo_amount_id = p_credit_memo_amount_id
744 AND
745 (
746 NVL(cma.credit_memo_amount_id, AR_NUMBER_DUMMY) =
747 NVL(
748 DECODE(p_cma_rec.credit_memo_amount_id,
749 AR_NUMBER_DUMMY, cma.credit_memo_amount_id,
750 p_cma_rec.credit_memo_amount_id),
751 AR_NUMBER_DUMMY
752 )
753 AND
754 NVL(cma.last_updated_by, AR_NUMBER_DUMMY) =
758 p_cma_rec.last_updated_by),
755 NVL(
756 DECODE(p_cma_rec.last_updated_by,
757 AR_NUMBER_DUMMY, cma.last_updated_by,
759 AR_NUMBER_DUMMY
760 )
761 AND
762 NVL(cma.last_update_date, AR_DATE_DUMMY) =
763 NVL(
764 DECODE(p_cma_rec.last_update_date,
765 AR_DATE_DUMMY, cma.last_update_date,
766 p_cma_rec.last_update_date),
767 AR_DATE_DUMMY
768 )
769 AND
770 NVL(cma.last_update_login, AR_NUMBER_DUMMY) =
771 NVL(
772 DECODE(p_cma_rec.last_update_login,
773 AR_NUMBER_DUMMY, cma.last_update_login,
774 p_cma_rec.last_update_login),
775 AR_NUMBER_DUMMY
776 )
777 AND
778 NVL(cma.created_by, AR_NUMBER_DUMMY) =
779 NVL(
780 DECODE(p_cma_rec.created_by,
781 AR_NUMBER_DUMMY, cma.created_by,
782 p_cma_rec.created_by),
783 AR_NUMBER_DUMMY
784 )
785 AND
786 NVL(cma.creation_date, AR_DATE_DUMMY) =
787 NVL(
788 DECODE(p_cma_rec.creation_date,
789 AR_DATE_DUMMY, cma.creation_date,
790 p_cma_rec.creation_date),
791 AR_DATE_DUMMY
792 )
793 AND
794 NVL(cma.customer_trx_line_id, AR_NUMBER_DUMMY) =
795 NVL(
796 DECODE(p_cma_rec.customer_trx_line_id,
797 AR_NUMBER_DUMMY, cma.customer_trx_line_id,
798 p_cma_rec.customer_trx_line_id),
799 AR_NUMBER_DUMMY
800 )
801 AND
802 NVL(cma.gl_date, AR_DATE_DUMMY) =
803 NVL(
804 DECODE(p_cma_rec.gl_date,
805 AR_DATE_DUMMY, cma.gl_date,
806 p_cma_rec.gl_date),
807 AR_DATE_DUMMY
808 )
809 AND
810 NVL(cma.amount, AR_NUMBER_DUMMY) =
811 NVL(
812 DECODE(p_cma_rec.amount,
813 AR_NUMBER_DUMMY, cma.amount,
814 p_cma_rec.amount),
815 AR_NUMBER_DUMMY
816 )
817 AND
818 NVL(cma.program_application_id, AR_NUMBER_DUMMY) =
819 NVL(
820 DECODE(p_cma_rec.program_application_id,
821 AR_NUMBER_DUMMY, cma.program_application_id,
822 p_cma_rec.program_application_id),
823 AR_NUMBER_DUMMY
824 )
825 AND
826 NVL(cma.program_id, AR_NUMBER_DUMMY) =
827 NVL(
828 DECODE(p_cma_rec.program_id,
829 AR_NUMBER_DUMMY, cma.program_id,
830 p_cma_rec.program_id),
831 AR_NUMBER_DUMMY
832 )
833 AND
834 NVL(cma.program_update_date, AR_DATE_DUMMY) =
835 NVL(
836 DECODE(p_cma_rec.program_update_date,
837 AR_DATE_DUMMY, cma.program_update_date,
838 p_cma_rec.program_update_date),
839 AR_DATE_DUMMY
840 )
841 AND
842 NVL(cma.request_id, AR_NUMBER_DUMMY) =
843 NVL(
844 DECODE(p_cma_rec.request_id,
845 AR_NUMBER_DUMMY, cma.request_id,
846 p_cma_rec.request_id),
847 AR_NUMBER_DUMMY
848 )
849 )
850 FOR UPDATE OF credit_memo_amount_id NOWAIT;
851
852 arp_util.debug('arp_cma_pkg.lock_compare_p()-');
853
854 EXCEPTION
855 WHEN OTHERS THEN
856 arp_util.debug( 'EXCEPTION: arp_cma_pkg.lock_compare_p' );
857 RAISE;
858 END;
859
860 /*===========================================================================+
861 | PROCEDURE |
862 | fetch_p |
863 | |
864 | DESCRIPTION |
865 | This procedure fetches a single row from ar_credit_memo_amounts |
866 | into a variable specified as a parameter based on the table's primary |
867 | key, credit_memo_amount_id |
868 | |
869 | SCOPE - PUBLIC |
870 | |
871 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
872 | arp_util.debug |
873 | |
877 | p_cma_rec - contains the fetched record |
874 | ARGUMENTS : IN: |
875 | p_credit_memo_amount_id - identifies the record to fetch |
876 | OUT: |
878 | |
879 | RETURNS : NONE |
880 | |
881 | NOTES |
882 | |
883 | MODIFICATION HISTORY |
884 | 27-JUN-95 Charlie Tomberg Created |
885 | |
886 +===========================================================================*/
887
888 PROCEDURE fetch_p( p_cma_rec OUT NOCOPY ar_credit_memo_amounts%rowtype,
889 p_credit_memo_amount_id IN
890 ar_credit_memo_amounts.credit_memo_amount_id%type)
891 IS
892
893 BEGIN
894 arp_util.debug('arp_cma_pkg.fetch_p()+');
895
896 SELECT *
897 INTO p_cma_rec
898 FROM ar_credit_memo_amounts
899 WHERE credit_memo_amount_id = p_credit_memo_amount_id;
900
901 arp_util.debug('arp_cma_pkg.fetch_p()-');
902
903 EXCEPTION
904 WHEN OTHERS THEN
905 arp_util.debug( 'EXCEPTION: arp_cma_pkg.fetch_p' );
906 RAISE;
907 END;
908
909 /*===========================================================================+
910 | PROCEDURE |
911 | delete_p |
912 | |
913 | DESCRIPTION |
914 | This procedure deletes the ar_credit_memo_amounts row identified |
915 | by the p_credit_memo_amount_id parameter. |
916 | |
917 | SCOPE - PUBLIC |
918 | |
919 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
920 | arp_util.debug |
921 | |
922 | ARGUMENTS : IN: |
923 | p_credit_memo_amount_id - identifies the rows to delete |
924 | OUT: |
925 | None |
926 | |
927 | RETURNS : NONE |
928 | |
929 | NOTES |
930 | |
931 | MODIFICATION HISTORY |
932 | 27-JUN-95 Charlie Tomberg Created |
933 | |
934 +===========================================================================*/
935
936 procedure delete_p( p_credit_memo_amount_id
937 IN ar_credit_memo_amounts.credit_memo_amount_id%type)
938 IS
939
940
941 BEGIN
942
943
944 arp_util.debug('arp_cma_pkg.delete_p()+');
945
946 DELETE FROM ar_credit_memo_amounts
947 WHERE credit_memo_amount_id = p_credit_memo_amount_id;
948
949 arp_util.debug('arp_cma_pkg.delete_p()-');
950
951 EXCEPTION
952 WHEN OTHERS THEN
953 arp_util.debug('EXCEPTION: arp_cma_pkg.delete_p()');
954
955 RAISE;
956
957 END;
958
959 /*===========================================================================+
960 | PROCEDURE |
961 | delete_f_ctl_id |
962 | |
963 | DESCRIPTION |
964 | This procedure deletes the ar_credit_memo_amounts rows identified |
965 | by the p_customer_trx_line_id parameter. |
966 | |
967 | SCOPE - PUBLIC |
968 | |
969 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
970 | arp_util.debug |
971 | |
972 | ARGUMENTS : IN: |
973 | p_customer_trx_line_id - identifies the rows to delete |
974 | OUT: |
975 | None |
979 | NOTES |
976 | |
977 | RETURNS : NONE |
978 | |
980 | |
981 | MODIFICATION HISTORY |
982 | 27-JUN-95 Charlie Tomberg Created |
983 | |
984 +===========================================================================*/
985
986 procedure delete_f_ctl_id( p_customer_trx_line_id
987 IN ra_customer_trx_lines.customer_trx_line_id%type)
988 IS
989
990
991 BEGIN
992
993
994 arp_util.debug('arp_cma_pkg.delete_f_ctl_id()+');
995
996 DELETE FROM ar_credit_memo_amounts
997 WHERE customer_trx_line_id = p_customer_trx_line_id;
998
999 arp_util.debug('arp_cma_pkg.delete_f_ctl_id()-');
1000
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 arp_util.debug('EXCEPTION: arp_cma_pkg.delete_f_ctl_id()');
1004
1005 RAISE;
1006
1007 END;
1008
1009 /*===========================================================================+
1010 | PROCEDURE |
1011 | delete_f_ct_id |
1012 | |
1013 | DESCRIPTION |
1014 | This procedure deletes the ar_credit_memo_amounts rows identified |
1015 | by the p_customer_trx_id parameter. |
1016 | |
1017 | SCOPE - PUBLIC |
1018 | |
1019 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1020 | arp_util.debug |
1021 | |
1022 | ARGUMENTS : IN: |
1023 | p_customer_trx_id - identifies the transactions
1024 | for which we then delete all CMA rows
1025 | OUT: |
1026 | None |
1027 | |
1028 | RETURNS : NONE |
1029 | |
1030 | NOTES |
1031 | |
1032 | MODIFICATION HISTORY |
1033 | 11-APR-2003 M RAYMOND Created |
1034 | |
1035 +===========================================================================*/
1036
1037 procedure delete_f_ct_id( p_customer_trx_id
1038 IN ra_customer_trx.customer_trx_id%type)
1039 IS
1040
1041
1042 BEGIN
1043
1044 arp_util.debug('arp_cma_pkg.delete_f_ct_id()+');
1045
1046 DELETE FROM ar_credit_memo_amounts
1047 WHERE customer_trx_line_id IN
1048 (SELECT customer_trx_line_id
1049 FROM ra_customer_trx_lines
1050 WHERE customer_trx_id = p_customer_trx_id);
1051
1052 arp_util.debug('arp_cma_pkg.delete_f_ct_id()-');
1053
1054 EXCEPTION
1055 WHEN OTHERS THEN
1056 arp_util.debug('EXCEPTION: arp_cma_pkg.delete_f_ct_id()');
1057
1058 RAISE;
1059
1060 END;
1061
1062 /*===========================================================================+
1063 | PROCEDURE |
1064 | update_p |
1065 | |
1066 | DESCRIPTION |
1067 | This procedure updates the ar_credit_memo_amounts row identified |
1068 | by the p_credit_memo_amount_id parameter. |
1069 | |
1070 | SCOPE - PUBLIC |
1071 | |
1072 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1073 | arp_util.debug |
1074 | |
1075 | ARGUMENTS : IN: |
1076 | p_credit_memo_amount_id - identifies the row to update |
1077 | p_cma_rec - contains the new column values |
1078 | OUT: |
1079 | None |
1080 | |
1081 | RETURNS : NONE |
1082 | |
1083 | NOTES |
1084 | set_to_dummy must be called before the values in p_cma_rec are |
1085 | changed and this function is called. |
1086 | |
1087 | MODIFICATION HISTORY |
1088 | 27-JUN-95 Charlie Tomberg Created |
1089 | |
1090 +===========================================================================*/
1091
1092 PROCEDURE update_p( p_cma_rec IN ar_credit_memo_amounts%rowtype,
1093 p_credit_memo_amount_id IN
1094 ar_credit_memo_amounts.credit_memo_amount_id%type)
1095 IS
1096
1097
1098 BEGIN
1099
1100 arp_util.debug('arp_cma_pkg.update_p()+ ' ||
1101 to_char(sysdate, 'HH:MI:SS'));
1102
1103 arp_cma_pkg.generic_update( pg_cursor1,
1104 ' WHERE credit_memo_amount_id = :where_1',
1105 p_credit_memo_amount_id,
1106 p_cma_rec);
1107
1108 arp_util.debug('arp_cma_pkg.update_p()- ' ||
1109 to_char(sysdate, 'HH:MI:SS'));
1110
1111
1112 EXCEPTION
1113 WHEN OTHERS THEN
1114 arp_util.debug('EXCEPTION: arp_cma_pkg.update_p()');
1115 RAISE;
1116 END;
1120 | update_f_ctl_id |
1117
1118 /*===========================================================================+
1119 | PROCEDURE |
1121 | |
1122 | DESCRIPTION |
1123 | This procedure updates the ar_credit_memo_amounts rows identified |
1124 | by the p_customer_trx_line_id parameter. |
1125 | |
1126 | SCOPE - PUBLIC |
1127 | |
1128 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1129 | arp_util.debug |
1130 | |
1131 | ARGUMENTS : IN: |
1132 | p_customer_trx_line_id - identifies the rows to update |
1133 | p_cma_rec - contains the new column values |
1134 | OUT: |
1135 | None |
1136 | |
1137 | RETURNS : NONE |
1138 | |
1139 | NOTES |
1140 | set_to_dummy must be called before the values in p_cma_rec are |
1141 | changed and this function is called. |
1142 | |
1143 | MODIFICATION HISTORY |
1144 | 27-JUN-95 Charlie Tomberg Created |
1145 | |
1146 +===========================================================================*/
1147
1148 PROCEDURE update_f_ctl_id( p_cma_rec IN ar_credit_memo_amounts%rowtype,
1149 p_customer_trx_line_id IN
1150 ra_customer_trx_lines.customer_trx_line_id%type)
1151 IS
1152
1153
1154 BEGIN
1155
1156 arp_util.debug('arp_cma_pkg.update_f_ctl_id()+ ' ||
1157 to_char(sysdate, 'HH:MI:SS'));
1158
1159 arp_cma_pkg.generic_update( pg_cursor3,
1160 ' WHERE customer_trx_line_id = :where_1',
1161 p_customer_trx_line_id,
1162 p_cma_rec);
1163
1164 arp_util.debug('arp_cma_pkg.update_f_ctl_id()- ' ||
1165 to_char(sysdate, 'HH:MI:SS'));
1166
1167
1168 EXCEPTION
1169 WHEN OTHERS THEN
1170 arp_util.debug('EXCEPTION: arp_cma_pkg.update_f_ctl_id()');
1171 RAISE;
1172 END;
1173
1174
1175 /*===========================================================================+
1176 | PROCEDURE |
1177 | insert_p |
1178 | |
1179 | DESCRIPTION |
1180 | This procedure inserts a row into ar_credit_memo_amounts that |
1181 | contains the column values specified in the p_cma_rec parameter. |
1182 | |
1183 | SCOPE - PUBLIC |
1184 | |
1185 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1186 | arp_util.debug |
1187 | |
1188 | ARGUMENTS : IN: |
1189 | p_cma_rec - contains the new column values |
1190 | OUT: |
1191 | p_credit_memo_amount_id - unique ID of the new row |
1192 | |
1193 | RETURNS : NONE |
1194 | |
1195 | NOTES |
1196 | |
1197 | MODIFICATION HISTORY |
1198 | 27-JUN-95 Charlie Tomberg Created |
1199 | |
1200 +===========================================================================*/
1201
1202 PROCEDURE insert_p(
1203 p_cma_rec IN ar_credit_memo_amounts%rowtype,
1204 p_credit_memo_amount_id
1205 OUT NOCOPY ar_credit_memo_amounts.credit_memo_amount_id%type
1206
1207 ) IS
1208
1209
1210 l_credit_memo_amount_id
1211 ar_credit_memo_amounts.credit_memo_amount_id%type;
1212
1213
1214 BEGIN
1215
1216 arp_util.debug('arp_cma_pkg.insert_p()+');
1217
1218 p_credit_memo_amount_id := '';
1219
1220 /*---------------------------*
1221 | Get the unique identifier |
1222 *---------------------------*/
1223
1224 SELECT AR_CREDIT_MEMO_AMOUNTS_S.NEXTVAL
1225 INTO l_credit_memo_amount_id
1226 FROM DUAL;
1227
1228
1229 /*-------------------*
1230 | Insert the record |
1231 *-------------------*/
1232
1233 INSERT INTO ar_credit_memo_amounts
1234 (
1235 credit_memo_amount_id,
1236 customer_trx_line_id,
1237 gl_date,
1238 amount,
1239 last_updated_by,
1240 last_update_date,
1241 last_update_login,
1242 created_by,
1243 creation_date,
1244 program_application_id,
1245 program_id,
1246 program_update_date,
1247 request_id
1248 )
1249 VALUES
1250 (
1251 l_credit_memo_amount_id,
1252 p_cma_rec.customer_trx_line_id,
1253 p_cma_rec.gl_date,
1254 p_cma_rec.amount,
1255 pg_user_id, /* last_updated_by */
1256 sysdate, /* last_update_date */
1257 nvl(pg_conc_login_id,
1258 pg_login_id), /* last_update_login */
1259 pg_user_id, /* created_by */
1260 sysdate, /* creation_date */
1261 pg_prog_appl_id, /* program_application_id */
1262 pg_conc_program_id, /* program_id */
1263 sysdate, /* program_update_date */
1264 p_cma_rec.request_id
1265 );
1266
1267
1268
1269 p_credit_memo_amount_id := l_credit_memo_amount_id;
1270
1271 arp_util.debug('arp_cma_pkg.insert_p()-');
1272
1273 EXCEPTION
1274 WHEN OTHERS THEN
1275 arp_util.debug('EXCEPTION: arp_cma_pkg.insert_p()');
1276 RAISE;
1277 END;
1278
1279
1280 /*---------------------------------------------+
1281 | Package initialization section. |
1282 | Sets WHO column variables for later use. |
1283 +---------------------------------------------*/
1284
1285 BEGIN
1286
1287 pg_user_id := fnd_global.user_id;
1288 pg_conc_login_id := fnd_global.conc_login_id;
1289 pg_login_id := fnd_global.login_id;
1290 pg_prog_appl_id := fnd_global.prog_appl_id;
1291 pg_conc_program_id := fnd_global.conc_program_id;
1292
1293
1294 END ARP_CMA_PKG;