[Home] [Help]
PACKAGE BODY: APPS.ARP_NOTES_PKG
Source
4 /*--------------------------------------------------------+
1 PACKAGE BODY ARP_NOTES_PKG AS
2 /* $Header: ARTINOTB.pls 115.7 2004/02/10 09:02:01 ksankara ship $ */
3
5 | Dummy constants for use in update and lock operations |
6 +--------------------------------------------------------*/
7
8 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
9 AR_FLAG_DUMMY CONSTANT VARCHAR2(10) := '~';
10 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
11 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
12
13 /*-------------------------------------+
14 | WHO column values from FND_GLOBAL |
15 +-------------------------------------*/
16
17 pg_user_id number;
18 pg_conc_login_id number;
19 pg_login_id number;
20 pg_prog_appl_id number;
21 pg_conc_program_id number;
22
23
24
25 /*===========================================================================+
26 | FUNCTION |
27 | get_number_dummy |
28 | |
29 | DESCRIPTION |
30 | This procedure returns the value of the AR_NUMBER DUMMY constant. |
31 | |
32 | SCOPE - PUBLIC |
33 | |
34 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
35 | arp_util.debug |
36 | |
37 | ARGUMENTS : IN: |
38 | None |
39 | OUT: |
40 | None |
41 | |
42 | RETURNS : value of AR_NUMBER_DUMMY |
43 | |
44 | NOTES |
45 | |
46 | MODIFICATION HISTORY |
47 | 16-DEC-95 Charlie Tomberg Created |
48 | |
49 +===========================================================================*/
50
51 FUNCTION get_number_dummy(p_null IN NUMBER DEFAULT null) RETURN number IS
52
53 BEGIN
54
55 arp_util.debug('arp_notes_pkg.get_number_dummy()+');
56
57 arp_util.debug('arp_notes_pkg.get_number_dummy()-');
58
59 return(AR_NUMBER_DUMMY);
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 arp_util.debug('EXCEPTION: arp_notes_pkg.get_number_dummy()');
64 RAISE;
65
66 END;
67
68 /*===========================================================================+
69 | PROCEDURE |
70 | set_to_dummy |
71 | |
72 | DESCRIPTION |
73 | This procedure initializes all columns in the parameter trx record |
74 | to the appropriate dummy value for its datatype. |
75 | |
76 | The dummy values are defined in the following package level constants: |
77 | AR_TEXT_DUMMY |
78 | AR_FLAG_DUMMY |
79 | AR_NUMBER_DUMMY |
80 | AR_DATE_DUMMY |
81 | |
82 | SCOPE - PUBLIC |
83 | |
84 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
85 | arp_util.debug |
86 | |
87 | ARGUMENTS : IN: |
88 | None |
89 | OUT: |
90 | p_notes_rec - The record to initialize |
91 | |
92 | RETURNS : NONE |
93 | |
94 | NOTES |
95 | |
96 | MODIFICATION HISTORY |
97 | 16-DEC-95 Charlie Tomberg Created |
98 | 19-DEC-95 Shelley Eitzen Added Customer Call Id
99 | |
100 +===========================================================================*/
101
102 PROCEDURE set_to_dummy( p_notes_rec OUT NOCOPY ar_notes%rowtype) IS
103
104 BEGIN
105
106 arp_util.debug('arp_notes_pkg.set_to_dummy()+');
107
108 p_notes_rec.note_id := AR_NUMBER_DUMMY;
109 p_notes_rec.customer_trx_id := AR_NUMBER_DUMMY;
110 p_notes_rec.customer_call_id := AR_NUMBER_DUMMY;
111 p_notes_rec.customer_call_topic_id := AR_NUMBER_DUMMY;
112 p_notes_rec.call_action_id := AR_NUMBER_DUMMY;
113 -- p_notes_rec.note_date := AR_DATE_DUMMY;
114 p_notes_rec.note_type := AR_TEXT_DUMMY;
115 p_notes_rec.text := AR_TEXT_DUMMY;
116 p_notes_rec.last_updated_by := AR_NUMBER_DUMMY;
117 p_notes_rec.last_update_date := AR_DATE_DUMMY;
118 p_notes_rec.last_update_login := AR_NUMBER_DUMMY;
119 p_notes_rec.creation_date := AR_DATE_DUMMY;
120 p_notes_rec.created_by := AR_NUMBER_DUMMY;
121
122
123 arp_util.debug('arp_notes_pkg.set_to_dummy()-');
124
125 EXCEPTION
126 WHEN OTHERS THEN
127 arp_util.debug('EXCEPTION: arp_notes_pkg.set_to_dummy()');
128 RAISE;
129
130 END;
131
132
133 /*===========================================================================+
134 | PROCEDURE |
135 | lock_f_ct_id |
139 | p_customer_trx_id parameter. |
136 | |
137 | DESCRIPTION |
138 | This procedure locks the ar_notes rows identified by the |
140 | |
141 | SCOPE - PUBLIC |
142 | |
143 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
144 | arp_util.debug |
145 | |
146 | ARGUMENTS : IN: |
147 | p_customer_trx_id - identifies the rows to lock |
148 | OUT: |
149 | None |
150 | |
151 | RETURNS : NONE |
152 | |
153 | NOTES |
154 | |
155 | MODIFICATION HISTORY |
156 | 16-DEC-95 Charlie Tomberg Created |
157 | |
158 +===========================================================================*/
159
160 PROCEDURE lock_f_ct_id( p_customer_trx_id IN ar_notes.customer_trx_id%type )
161 IS
162
163 l_customer_trx_id ar_notes.customer_trx_id%type;
164
165 BEGIN
166 arp_util.debug('arp_notes_pkg.lock_p()+');
167
168
169 SELECT customer_trx_id
170 INTO l_customer_trx_id
171 FROM ar_notes
172 WHERE customer_trx_id = p_customer_trx_id
173 FOR UPDATE OF customer_trx_id NOWAIT;
174
175 arp_util.debug('arp_notes_pkg.lock_p()-');
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 arp_util.debug( 'EXCEPTION: arp_notes_pkg.lock_p' );
180 RAISE;
181 END;
182
183 /*===========================================================================+
184 | PROCEDURE |
185 | lock_fetch_p |
186 | |
187 | DESCRIPTION |
188 | This procedure locks the ar_notes row identified by the |
189 | p_note_id parameter and populates the p_notes_rec parameter with |
190 | the row that was locked. |
191 | |
192 | SCOPE - PUBLIC |
193 | |
194 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
195 | arp_util.debug |
196 | |
197 | ARGUMENTS : IN: |
198 | p_noteid - identifies the row to lock |
199 | OUT: |
200 | p_notes_rec - contains the locked row |
201 | |
202 | RETURNS : NONE |
203 | |
204 | NOTES |
205 | |
206 | MODIFICATION HISTORY |
207 | 16-DEC-95 Charlie Tomberg Created |
208 | |
209 +===========================================================================*/
210
211 PROCEDURE lock_fetch_p( p_notes_rec IN OUT NOCOPY ar_notes%rowtype,
212 p_note_id IN ar_notes.note_id%type) IS
213
214 BEGIN
215 arp_util.debug('arp_notes_pkg.lock_fetch_p()+');
216
217 SELECT *
218 INTO p_notes_rec
219 FROM ar_notes
220 WHERE note_id = p_note_id
221 FOR UPDATE OF note_id NOWAIT;
222
223 arp_util.debug('arp_notes_pkg.lock_fetch_p()-');
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 arp_util.debug( 'EXCEPTION: arp_notes_pkg.lock_fetch_p' );
228 RAISE;
229 END;
230
231 /*===========================================================================+
232 | PROCEDURE |
233 | lock_compare_p |
234 | |
235 | DESCRIPTION |
236 | This procedure locks the ar_notes row identified by the |
237 | p_note_id parameter only if no columns in that row have |
238 | changed from when they were first selected in the form. |
239 | |
240 | SCOPE - PUBLIC |
241 | |
242 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
243 | arp_util.debug |
244 | |
245 | ARGUMENTS : IN: |
246 | p_note_id - identifies the row to lock |
247 | p_notes_rec - note record for comparison |
248 | OUT: |
249 | None |
250 | |
251 | RETURNS : NONE |
252 | |
253 | NOTES |
257 | 19-DEC-95 Shelley Eitzen Added Customer Call Id |
254 | |
255 | MODIFICATION HISTORY |
256 | 16-DEC-95 Charlie Tomberg Created |
258 | |
259 +===========================================================================*/
260
261 PROCEDURE lock_compare_p( p_notes_rec IN ar_notes%rowtype,
262 p_note_id IN ar_notes.note_id%type) IS
263
264 l_new_note_rec ar_notes%rowtype;
265
266 BEGIN
267 arp_util.debug('arp_notes_pkg.lock_compare_p()+');
268
269 SELECT *
270 INTO l_new_note_rec
271 FROM ar_notes n
272 WHERE n.note_id = p_note_id
273 AND
274 (
275 NVL(n.note_id, AR_NUMBER_DUMMY) =
276 NVL(
277 DECODE(p_notes_rec.note_id,
278 AR_NUMBER_DUMMY, n.note_id,
279 p_notes_rec.note_id),
280 AR_NUMBER_DUMMY
281 )
282 AND
283 NVL(n.note_type, AR_TEXT_DUMMY) =
284 NVL(
285 DECODE(p_notes_rec.note_type,
286 AR_TEXT_DUMMY, n.note_type,
287 p_notes_rec.note_type),
288 AR_TEXT_DUMMY
289 )
290 AND
291 NVL(n.text, AR_TEXT_DUMMY) =
292 NVL(
293 DECODE(p_notes_rec.text,
294 AR_TEXT_DUMMY, n.text,
295 p_notes_rec.text),
296 AR_TEXT_DUMMY
297 )
298 AND
299 NVL(n.customer_trx_id, AR_NUMBER_DUMMY) =
300 NVL(
301 DECODE(p_notes_rec.customer_trx_id,
302 AR_NUMBER_DUMMY, n.customer_trx_id,
303 p_notes_rec.customer_trx_id),
304 AR_NUMBER_DUMMY
305 )
306 AND
307 NVL(n.customer_call_id, AR_NUMBER_DUMMY) =
308 NVL(
309 DECODE(p_notes_rec.customer_call_id,
310 AR_NUMBER_DUMMY, n.customer_call_id),
311 AR_NUMBER_DUMMY
312 )
313 AND
314 NVL(n.customer_call_topic_id, AR_NUMBER_DUMMY) =
315 NVL(
316 DECODE(p_notes_rec.customer_call_topic_id,
317 AR_NUMBER_DUMMY, n.customer_call_topic_id),
318 AR_NUMBER_DUMMY
319 )
320 AND
321 NVL(n.call_action_id, AR_NUMBER_DUMMY) =
322 NVL(
323 DECODE(p_notes_rec.call_action_id ,
324 AR_NUMBER_DUMMY, n.call_action_id),
325 AR_NUMBER_DUMMY
326 )
327 AND
328 NVL(n.created_by, AR_NUMBER_DUMMY) =
329 NVL(
330 DECODE(p_notes_rec.created_by,
331 AR_NUMBER_DUMMY, n.created_by,
332 p_notes_rec.created_by),
333 AR_NUMBER_DUMMY
334 )
335 AND
336 NVL(TRUNC(n.creation_date), AR_DATE_DUMMY) =
337 NVL(
338 DECODE(TRUNC(p_notes_rec.creation_date),
339 AR_DATE_DUMMY, TRUNC(n.creation_date),
340 TRUNC(p_notes_rec.creation_date)),
341 AR_DATE_DUMMY
342 )
343 AND
344 NVL(n.last_updated_by, AR_NUMBER_DUMMY) =
345 NVL(
346 DECODE(p_notes_rec.last_updated_by,
347 AR_NUMBER_DUMMY, n.last_updated_by,
348 p_notes_rec.last_updated_by),
349 AR_NUMBER_DUMMY
350 )
351 AND
352 NVL(TRUNC(n.last_update_date), AR_DATE_DUMMY) =
353 NVL(
354 DECODE(TRUNC(p_notes_rec.last_update_date),
355 AR_DATE_DUMMY, TRUNC(n.last_update_date),
356 TRUNC(p_notes_rec.last_update_date)),
357 AR_DATE_DUMMY
358 )
359 AND
360 NVL(n.last_update_login, AR_NUMBER_DUMMY) =
361 NVL(
362 DECODE(p_notes_rec.last_update_login,
363 AR_NUMBER_DUMMY, n.last_update_login,
364 p_notes_rec.last_update_login),
365 AR_NUMBER_DUMMY
366 )
367 )
368 FOR UPDATE OF note_id NOWAIT;
369
370 arp_util.debug('arp_notes_pkg.lock_compare_p()-');
371
372 EXCEPTION
373 WHEN NO_DATA_FOUND THEN
374 arp_util.debug(
375 'EXCEPTION: arp_notes_pkg.lock_compare_p NO_DATA_FOUND' );
376
377 arp_util.debug('');
378 arp_util.debug('============= Old Record =============');
379 display_note_p(p_note_id);
380 arp_util.debug('');
381 arp_util.debug('============= New Record =============');
382 display_note_rec(p_notes_rec);
383
384 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
385 APP_EXCEPTION.Raise_Exception;
386 WHEN OTHERS THEN
387 arp_util.debug( 'EXCEPTION: arp_notes_pkg.lock_compare_p' );
388 RAISE;
389 END;
390
391
392 /*===========================================================================+
393 | PROCEDURE |
394 | fetch_p |
395 | |
396 | DESCRIPTION |
397 | This procedure fetches a single row from ar_notes into a |
398 | variable specified as a parameter based on the table's primary key, |
399 | p_note_id. |
400 | |
401 | SCOPE - PUBLIC |
402 | |
403 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
404 | arp_util.debug |
405 | |
409 | p_notes_rec - contains the fetched record |
406 | ARGUMENTS : IN: |
407 | p_note_id - identifies the record to fetch |
408 | OUT: |
410 | |
411 | RETURNS : NONE |
412 | |
413 | NOTES |
414 | |
415 | MODIFICATION HISTORY |
416 | 16-DEC-95 Charlie Tomberg Created |
417 | |
418 +===========================================================================*/
419
420 PROCEDURE fetch_p( p_notes_rec OUT NOCOPY ar_notes%rowtype,
421 p_note_id IN ar_notes.note_id%type) IS
422
423 BEGIN
424 arp_util.debug('arp_notes_pkg.fetch_p()+');
425
426 SELECT *
427 INTO p_notes_rec
428 FROM ar_notes
429 WHERE note_id = p_note_id;
430
431 arp_util.debug('arp_notes_pkg.fetch_p()-');
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 arp_util.debug( 'EXCEPTION: arp_notes_pkg.fetch_p' );
436 RAISE;
437 END;
438
439 /*===========================================================================+
440 | PROCEDURE |
441 | delete_f_ct_id |
442 | |
443 | DESCRIPTION |
444 | This procedure deletes the ar_notes row identified by the |
445 | p_customer_trx_id parameter. |
446 | |
447 | SCOPE - PUBLIC |
448 | |
449 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
450 | arp_util.debug |
451 | |
452 | ARGUMENTS : IN: |
453 | p_customer_trx_id - identifies the row to delete |
454 | OUT: |
455 | None |
456 | |
457 | RETURNS : NONE |
458 | |
459 | NOTES |
460 | |
461 | MODIFICATION HISTORY |
462 | 16-DEC-95 Charlie Tomberg Created |
463 | |
464 +===========================================================================*/
465
466 procedure delete_f_ct_id( p_customer_trx_id IN ar_notes.customer_trx_id%type)
467 IS
468
469
470 BEGIN
471
472
473 arp_util.debug('arp_notes_pkg.delete_f_ct_id()+');
474
475 DELETE FROM ar_notes
476 WHERE customer_trx_id = p_customer_trx_id;
477
478 arp_util.debug('arp_notes_pkg.delete_f_ct_id()-');
479
480 EXCEPTION
481 WHEN OTHERS THEN
482 arp_util.debug('EXCEPTION: arp_notes_pkg.delete_f_ct_id()');
483
484 RAISE;
485
486 END;
487
488 /*===========================================================================+
489 | PROCEDURE |
490 | update_p |
491 | |
492 | DESCRIPTION |
493 | This procedure updates the ar_notes row identified by the |
494 | p_note_id parameter. |
495 | |
496 | SCOPE - PUBLIC |
497 | |
498 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
499 | arp_util.debug |
500 | |
501 | ARGUMENTS : IN: |
502 | p_note_id - identifies the row to update |
503 | OUT: |
504 | None |
505 | IN OUT: |
506 | p_notes_rec - contains the new column values |
507 | |
508 | RETURNS : NONE |
509 | |
510 | NOTES |
511 | set_to_dummy must be called before the values in p_notes_rec are |
512 | changed and this function is called. |
513 | |
514 | MODIFICATION HISTORY |
515 | 16-DEC-95 Charlie Tomberg Created |
516 | 19-DEC-95 Shelley Eitzen Added Customer Call Id |
517 | |
518 +===========================================================================*/
519
520 PROCEDURE update_p( p_notes_rec IN OUT NOCOPY ar_notes%rowtype,
521 p_note_id IN ar_notes.note_id%type) IS
522
523
524 BEGIN
525
526 arp_util.debug('arp_notes_pkg.update_p()+ ' ||
527 to_char(sysdate, 'HH:MI:SS'));
528
529 SELECT
530 DECODE(p_notes_rec.last_updated_by,
531 AR_NUMBER_DUMMY, pg_user_id,
532 p_notes_rec.last_updated_by),
533 DECODE(p_notes_rec.last_update_date,
534 AR_DATE_DUMMY, sysdate,
535 p_notes_rec.last_update_date),
536 DECODE(p_notes_rec.last_update_login,
537 AR_NUMBER_DUMMY, nvl(pg_conc_login_id,
538 pg_login_id),
539 p_notes_rec.last_update_login)
540 INTO p_notes_rec.last_updated_by,
541 p_notes_rec.last_update_date,
542 p_notes_rec.last_update_login
543 FROM DUAL;
544
545 UPDATE ar_notes
546 SET customer_trx_id =
547 DECODE(p_notes_rec.customer_trx_id,
548 AR_NUMBER_DUMMY, customer_trx_id,
549 p_notes_rec.customer_trx_id),
550 customer_call_id =
551 DECODE(p_notes_rec.customer_call_id,
552 AR_NUMBER_DUMMY, customer_call_id,
553 p_notes_rec.customer_call_id),
554 customer_call_topic_id =
555 DECODE(p_notes_rec.customer_call_topic_id,
556 AR_NUMBER_DUMMY, customer_call_topic_id,
557 p_notes_rec.customer_call_topic_id),
558 call_action_id =
559 DECODE(p_notes_rec.call_action_id,
560 AR_NUMBER_DUMMY, call_action_id,
561 p_notes_rec.call_action_id),
562 note_type =
563 DECODE(p_notes_rec.note_type,
564 AR_TEXT_DUMMY, note_type,
565 p_notes_rec.note_type),
566 text =
567 DECODE(p_notes_rec.text,
568 AR_TEXT_DUMMY, text,
569 p_notes_rec.text),
570 created_by =
571 DECODE(p_notes_rec.created_by,
572 AR_NUMBER_DUMMY, created_by,
573 p_notes_rec.created_by),
574 creation_date =
575 DECODE(p_notes_rec.creation_date,
576 AR_DATE_DUMMY, creation_date,
577 p_notes_rec.creation_date),
578 last_updated_by = p_notes_rec.last_updated_by,
579 last_update_date = p_notes_rec.last_update_date,
580 last_update_login = p_notes_rec.last_update_login
581 WHERE note_id = p_note_id;
582
583
584
585 arp_util.debug('arp_notes_pkg.update_p()- ' ||
586 to_char(sysdate, 'HH:MI:SS'));
587
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 arp_util.debug('EXCEPTION: arp_notes_pkg.update_p()');
592 RAISE;
593 END;
594
595
596
597 /*===========================================================================+
598 | PROCEDURE |
599 | insert_p |
600 | |
601 | DESCRIPTION |
602 | This procedure inserts a row into ar_notes that contains the |
603 | column values specified in the p_notes_rec parameter. |
604 | |
605 | SCOPE - PUBLIC |
606 | |
607 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
608 | arp_util.debug |
609 | |
610 | ARGUMENTS : IN OUT: |
611 | p_notes_rec - contains the new column values |
612 | OUT: |
613 | p_note_id - unique ID of the new row |
614 | |
615 | RETURNS : NONE |
616 | |
617 | NOTES |
618 | |
619 | MODIFICATION HISTORY |
620 | 16-DEC-95 Charlie Tomberg Created |
621 | 19-DEC-95 Shelley Eitzen Added Customer Call Id |
622 | |
623 +===========================================================================*/
624
625 PROCEDURE insert_p(
626 p_notes_rec IN OUT NOCOPY ar_notes%rowtype
627 ) IS
628
629
630 l_note_id ar_notes.note_id%type;
631
632 BEGIN
633
634 arp_util.debug('arp_notes_pkg.insert_p()+');
635
636 /*------------------------------+
637 | get the unique id |
638 +------------------------------*/
639
640 SELECT ar_notes_s.nextval
641 INTO l_note_id
642 FROM dual;
643
647
644 /*---------------------------------+
645 | Populate the output parameters |
646 +---------------------------------*/
648 p_notes_rec.note_id := l_note_id;
649 p_notes_rec.last_updated_by := pg_user_id;
650 p_notes_rec.last_update_date := sysdate;
651 p_notes_rec.last_update_login := NVL(pg_conc_login_id, pg_login_id);
652 p_notes_rec.created_by := pg_user_id;
653 p_notes_rec.creation_date := NVL(p_notes_rec.creation_date, sysdate);
654
655
656 arp_util.debug('l_note_id : ' || to_char(l_note_id));
657 arp_util.debug('p_notes_rec.note_type : ' || p_notes_rec.note_type);
658 /* 3206020 */
659 /* arp_util.debug('p_notes_rec.text : ' || p_notes_rec.text); */
660 arp_util.debug('p_notes_rec.customer_trx_id : ' ||
661 to_char(p_notes_rec.customer_trx_id));
662 arp_util.debug('p_notes_rec.customer_call_id : ' || to_char(p_notes_rec.customer_call_id));
663 arp_util.debug('p_notes_rec.customer_call_topic_id : ' || to_char(p_notes_rec.customer_call_topic_id));
664 arp_util.debug('p_notes_rec.call_action_id : ' || to_char(p_notes_rec.call_action_id));
665 arp_util.debug('p_notes_rec.last_updated_by : ' || to_char(p_notes_rec.last_updated_by));
666 arp_util.debug('p_notes_rec.last_update_date : ' || to_char(p_notes_rec.last_update_date));
670
667 arp_util.debug('p_notes_rec.last_update_login : ' || to_char(p_notes_rec.last_update_login));
668 arp_util.debug('p_notes_rec.created_by : ' || to_char(p_notes_rec.created_by));
669 arp_util.debug('p_notes_rec.creation_date : ' || to_char(p_notes_rec.creation_date));
671
672 /*------------------------------+
673 | insert the record |
674 +------------------------------*/
675 INSERT INTO ar_notes
676 (
677 note_id,
678 note_type,
679 text,
680 customer_trx_id,
681 customer_call_id,
682 customer_call_topic_id,
683 call_action_id,
684 last_updated_by,
685 last_update_date,
686 last_update_login,
687 created_by,
688 creation_date
689 )
690 VALUES
691 (
692 l_note_id,
693 p_notes_rec.note_type,
694 p_notes_rec.text,
695 p_notes_rec.customer_trx_id,
696 p_notes_rec.customer_call_id,
697 p_notes_rec.customer_call_topic_id,
698 p_notes_rec.call_action_id,
699 p_notes_rec.last_updated_by,
700 p_notes_rec.last_update_date,
701 p_notes_rec.last_update_login,
702 p_notes_rec.created_by,
703 p_notes_rec.creation_date
704 );
705
706
707 arp_util.debug('arp_notes_pkg.insert_p()-');
708
709 EXCEPTION
710 WHEN OTHERS THEN
711 arp_util.debug('EXCEPTION: arp_notes_pkg.insert_p()');
712 RAISE;
713 END;
714
715 /*===========================================================================+
716 | PROCEDURE |
717 | display_note_p |
718 | |
719 | DESCRIPTION |
720 | Displays the values of all columns except last_update_date. |
721 | |
722 | SCOPE - PUBLIC |
723 | |
724 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
725 | arp_util.debug |
726 | |
727 | ARGUMENTS : IN: |
728 | p_note_id |
729 | OUT: |
730 | IN/ OUT: |
731 | |
732 | RETURNS : NONE |
733 | |
734 | NOTES |
735 | |
736 | MODIFICATION HISTORY |
737 | 16-DEC-95 Charlie Tomberg Created |
738 | |
739 +===========================================================================*/
740
741 PROCEDURE display_note_p( p_note_id IN ar_notes.note_id%type ) IS
742
743
744 l_notes_rec ar_notes%rowtype;
745
746 BEGIN
747
748 arp_util.debug('arp_notes_pkg.display_note_p()+');
749
750 arp_notes_pkg.fetch_p(l_notes_rec, p_note_id);
751
752 arp_notes_pkg.display_note_rec(l_notes_rec);
753
754 arp_util.debug('arp_notes_pkg.display_note_p()-');
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 arp_util.debug('EXCEPTION: arp_notes_pkg.display_note_p()');
759 RAISE;
760
761 END;
762
763 /*===========================================================================+
764 | PROCEDURE |
765 | display_note_rec |
766 | |
767 | DESCRIPTION |
768 | Displays the values of all columns except last_update_date. |
769 | |
770 | SCOPE - PUBLIC |
771 | |
772 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
773 | arp_util.debug |
774 | |
775 | ARGUMENTS : IN: |
776 | p_notes_rec |
777 | OUT: |
778 | IN/ OUT: |
779 | |
780 | RETURNS : NONE |
781 | |
782 | NOTES |
783 | |
784 | MODIFICATION HISTORY |
785 | 16-DEC-95 Charlie Tomberg Created |
786 | |
787 +===========================================================================*/
788
789 PROCEDURE display_note_rec ( p_notes_rec IN ar_notes%rowtype ) IS
790
791 BEGIN
792
793 arp_util.debug('arp_notes_pkg.display_note_rec()+');
794
795 arp_util.debug('************ Dump of ar_notes record ************');
796 arp_util.debug('customer_trx_id: ' || p_notes_rec.customer_trx_id);
797
798 arp_util.debug('note_id : ' || p_notes_rec.note_id);
799 arp_util.debug('creation_date : ' || p_notes_rec.creation_date);
800 arp_util.debug('note_type : ' || p_notes_rec.note_type);
801 /* Bug 3206020 */
802 /*arp_util.debug('text : ' || p_notes_rec.text); */
803 arp_util.debug('customer_call_id : ' || p_notes_rec.customer_call_id );
804 arp_util.debug('customer_call_topic_id : ' ||
805 p_notes_rec.customer_call_topic_id );
809 arp_util.debug('************* End ar_notes record *************');
806 arp_util.debug('call_action_id : ' ||p_notes_rec.call_action_id );
807 arp_util.debug('customer_trx_id : ' ||p_notes_rec.customer_trx_id);
808
810
811 arp_util.debug('arp_notes_pkg.display_note_rec()-');
812
813 EXCEPTION
814 WHEN OTHERS THEN
815 arp_util.debug('EXCEPTION: arp_notes_pkg.display_note_rec()');
816 RAISE;
817
818 END;
819
820
821 /*===========================================================================+
822 | PROCEDURE |
823 | lock_compare_cover |
824 | |
825 | DESCRIPTION |
826 | Converts column parameters to a ar_notes record and locks the notes |
827 | record. |
828 | |
829 | SCOPE - PUBLIC |
830 | |
831 | |
832 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
833 | arp_util.debug |
834 | |
835 | IN: |
836 | p_note_id |
837 | p_last_updated_by |
838 | p_last_update_date |
839 | p_last_update_login |
840 | p_created_by |
841 | p_creation_date |
842 | p_note_type |
843 | p_text |
844 | p_customer_call_id |
845 | p_customer_call_topic_id |
846 | p_call_action_id |
847 | p_customer_trx_id |
848 | |
849 | OUT: |
850 | None |
851 | IN/ OUT: |
852 | None |
853 | |
854 | RETURNS : NONE |
855 | |
856 | NOTES |
857 | |
858 | MODIFICATION HISTORY |
859 | 16-DEC-95 Charlie Tomberg Created |
860 | 19-DEC-95 Shelley Eitzen Added Customer Call Id |
861 | |
862 +===========================================================================*/
863
864 PROCEDURE lock_compare_cover(
865 p_note_id IN ar_notes.note_id%type,
866 p_last_updated_by IN ar_notes.last_updated_by%type,
867 p_last_update_date IN ar_notes.last_update_date%type,
868 p_last_update_login IN ar_notes.last_update_login%type,
869 p_created_by IN ar_notes.created_by%type,
870 p_creation_date IN ar_notes.creation_date%type,
871 p_note_type IN ar_notes.note_type%type,
872 p_text IN ar_notes.text%type,
873 p_customer_call_id IN ar_notes.customer_call_id%type,
874 p_customer_call_topic_id IN ar_notes.customer_call_topic_id%type,
875 p_call_action_id IN ar_notes.call_action_id%type,
876 p_customer_trx_id IN ar_notes.customer_trx_id%type ) IS
877
878
879 l_notes_rec ar_notes%rowtype;
880
881 BEGIN
882 arp_util.debug('arp_notes_pkg.lock_compare_cover()+');
883
887 +------------------------------------------------*/
884 /*------------------------------------------------+
885 | Populate the header record with the values |
886 | passed in as parameters. |
888
889 arp_notes_pkg.set_to_dummy(l_notes_rec);
890
891 l_notes_rec.note_id := p_note_id;
892 l_notes_rec.last_updated_by := p_last_updated_by;
893 l_notes_rec.last_update_date := p_last_update_date;
894 l_notes_rec.last_update_login := p_last_update_login;
895 l_notes_rec.created_by := p_created_by;
896 l_notes_rec.creation_date := p_creation_date;
897 l_notes_rec.note_type := p_note_type;
898 l_notes_rec.text := p_text;
899 l_notes_rec.customer_call_id := p_customer_call_id;
900 l_notes_rec.customer_call_topic_id := p_customer_call_topic_id;
901 l_notes_rec.call_action_id := p_call_action_id;
902 l_notes_rec.customer_trx_id := p_customer_trx_id;
903
904
905 /*-----------------------------------------+
906 | Call the standard header table handler |
907 +-----------------------------------------*/
908
909 lock_compare_p( l_notes_rec, p_note_id);
910
911 arp_util.debug('arp_notes_pkg.lock_compare_cover()-');
912
913 EXCEPTION
914 WHEN OTHERS THEN
915 arp_util.debug('EXCEPTION : arp_notes_pkg.lock_compare_cover()');
916
917 arp_util.debug('----- parameters for lock_compare_cover() ' ||
918 '-----');
919 arp_util.debug('p_note_id : ' || p_note_id);
920 arp_util.debug('p_last_updated_by : ' || p_last_updated_by);
921 arp_util.debug('p_last_update_date : ' || p_last_update_date);
922 arp_util.debug('p_last_update_login : ' || p_last_update_login);
923 arp_util.debug('p_created_by : ' || p_created_by);
924 arp_util.debug('p_creation_date : ' || p_creation_date);
928 arp_util.debug('p_customer_call_id : ' || p_customer_call_id);
925 arp_util.debug('p_note_type : ' || p_note_type);
926 /* Bg 3206020 */
927 /* arp_util.debug('p_text : ' || p_text); */
929 arp_util.debug('p_customer_call_topic_id : ' || p_customer_call_topic_id);
930 arp_util.debug('p_call_action_id : ' || p_call_action_id);
931 arp_util.debug('p_customer_trx_id : ' || p_customer_trx_id);
932
933 RAISE;
934
935 END;
936
937
938 /*===========================================================================+
939 | PROCEDURE |
940 | insert_cover |
941 | |
942 | DESCRIPTION |
943 | Converts column parameters to a ar_notes record and |
944 | inserts the notes record. |
945 | |
946 | SCOPE - PUBLIC |
947 | |
948 | |
949 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
950 | arp_util.debug |
951 | |
952 | IN: |
953 | p_note_id |
954 | p_note_type |
955 | p_text |
956 | p_customer_call_id |
957 | p_customer_call_topic_id |
958 | p_call_action_id |
959 | |
960 | OUT: |
961 | p_customer_trx_id |
962 | IN/ OUT: |
963 | p_last_updated_by |
964 | p_last_update_date |
965 | p_last_update_login |
966 | p_created_by |
967 | p_creation_date |
968 | |
969 | RETURNS : NONE |
970 | |
971 | NOTES |
972 | |
973 | MODIFICATION HISTORY |
974 | 16-DEC-95 Charlie Tomberg Created |
975 | 19-DEC-95 Shelley Eitzen Added Customer Call ID |
976 | |
977 +===========================================================================*/
978
979 PROCEDURE insert_cover(
980 p_note_type IN ar_notes.note_type%type,
981 p_text IN ar_notes.text%type,
982 p_customer_call_id IN ar_notes.customer_call_id%type,
983 p_customer_call_topic_id IN ar_notes.customer_call_topic_id%type,
984 p_call_action_id IN ar_notes.call_action_id%type,
985 p_customer_trx_id IN ar_notes.customer_trx_id%type,
986 p_note_id OUT NOCOPY ar_notes.note_id%type,
987 p_last_updated_by IN OUT NOCOPY ar_notes.last_updated_by%type,
988 p_last_update_date IN OUT NOCOPY ar_notes.last_update_date%type,
989 p_last_update_login IN OUT NOCOPY ar_notes.last_update_login%type,
990 p_created_by IN OUT NOCOPY ar_notes.created_by%type,
991 p_creation_date IN OUT NOCOPY ar_notes.creation_date%type ) IS
992
993
994 l_notes_rec ar_notes%rowtype;
995
996 BEGIN
997 arp_util.debug('arp_notes_pkg.insert_cover()+');
998
999 /*------------------------------------------------+
1000 | Populate the header record with the values |
1001 | passed in as parameters. |
1002 +------------------------------------------------*/
1003
1004 l_notes_rec.last_updated_by := p_last_updated_by;
1005 l_notes_rec.last_update_date := p_last_update_date;
1006 l_notes_rec.last_update_login := p_last_update_login;
1007 l_notes_rec.created_by := p_created_by;
1008 l_notes_rec.creation_date := p_creation_date;
1009 l_notes_rec.note_type := p_note_type;
1010 l_notes_rec.text := p_text;
1011 l_notes_rec.customer_call_id := p_customer_call_id;
1012 l_notes_rec.customer_call_topic_id := p_customer_call_topic_id;
1013 l_notes_rec.call_action_id := p_call_action_id;
1014 l_notes_rec.customer_trx_id := p_customer_trx_id;
1015
1016
1017 /*-----------------------------------------+
1018 | Call the standard header table handler |
1019 +-----------------------------------------*/
1020
1021 insert_p( l_notes_rec );
1022
1023 /*-------------------------------+
1024 | Populate the out NOCOPY parameters |
1025 +-------------------------------*/
1026
1027 /* Bug 2306546 : ON-LOCK trigger error.Assigned the required value to p_note_id
1028 Previously it was not returning the value to out NOCOPY parameter p_note_id,
1029 Which is called in ARXTWMAI.pld*/
1030 p_note_id := l_notes_rec.note_id;
1031 p_last_updated_by := l_notes_rec.last_updated_by;
1032 p_last_update_date := l_notes_rec.last_update_date;
1033 p_last_update_login := l_notes_rec.last_update_login;
1034 p_created_by := l_notes_rec.created_by;
1035 p_creation_date := l_notes_rec.creation_date;
1036
1037 arp_util.debug('arp_notes_pkg.insert_cover()-');
1038
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 arp_util.debug('EXCEPTION : arp_notes_pkg.insert_cover()');
1042
1043 arp_util.debug('----- parameters for insert_cover() ' ||
1044 '-----');
1045 arp_util.debug('p_last_updated_by : ' || p_last_updated_by);
1046 arp_util.debug('p_last_update_date : ' || p_last_update_date);
1047 arp_util.debug('p_last_update_login : ' || p_last_update_login);
1048 arp_util.debug('p_created_by : ' || p_created_by);
1049 arp_util.debug('p_creation_date : ' || p_creation_date);
1050 arp_util.debug('p_note_type : ' || p_note_type);
1051 /* Bug 3206020 */
1052 /* arp_util.debug('p_text : ' || p_text); */
1053 arp_util.debug('p_customer_call_id : ' || p_customer_call_id);
1054 arp_util.debug('p_customer_call_topic_id : ' || p_customer_call_topic_id);
1055 arp_util.debug('p_call_action_id : ' || p_call_action_id);
1056 arp_util.debug('p_customer_trx_id : ' || p_customer_trx_id);
1057
1058 RAISE;
1059
1060 END;
1061
1062 /*===========================================================================+
1063 | PROCEDURE |
1064 | update_cover |
1065 | |
1066 | DESCRIPTION |
1067 | Converts column parameters to a notes record and |
1068 | updates the notes record. |
1069 | |
1070 | SCOPE - PUBLIC |
1071 | |
1072 | |
1073 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1074 | arp_util.debug |
1075 | |
1076 | IN: |
1077 | p_note_id |
1078 | p_created_by |
1079 | p_creation_date |
1080 | p_note_type |
1081 | p_text |
1082 | p_customer_call_id |
1083 | p_customer_call_topic_id |
1084 | p_call_action_id |
1085 | p_customer_trx_id |
1086 | |
1087 | OUT: |
1088 | None |
1089 | IN/ OUT: |
1090 | p_last_updated_by |
1091 | p_last_update_date |
1092 | p_last_update_login |
1093 | |
1094 | RETURNS : NONE |
1095 | |
1096 | NOTES |
1097 | |
1098 | MODIFICATION HISTORY |
1099 | 16-DEC-95 Charlie Tomberg Created |
1100 | 19-DEC-95 Shelley Eitzen Added Customer Call Id |
1101 | |
1102 +===========================================================================*/
1103
1104
1105 PROCEDURE update_cover(
1106 p_note_id IN ar_notes.note_id%type,
1107 p_created_by IN ar_notes.created_by%type,
1108 p_creation_date IN ar_notes.creation_date%type,
1109 p_note_type IN ar_notes.note_type%type,
1110 p_text IN ar_notes.text%type,
1111 p_customer_call_id IN ar_notes.customer_call_id%type,
1112 p_customer_call_topic_id IN ar_notes.customer_call_topic_id%type,
1113 p_call_action_id IN ar_notes.call_action_id%type,
1114 p_customer_trx_id IN ar_notes.customer_trx_id%type,
1115 p_last_updated_by IN OUT NOCOPY ar_notes.last_updated_by%type,
1116 p_last_update_date IN OUT NOCOPY ar_notes.last_update_date%type,
1117 p_last_update_login IN OUT NOCOPY ar_notes.last_update_login%type ) IS
1118
1119
1120 l_notes_rec ar_notes%rowtype;
1121
1122 BEGIN
1126 | Populate the header record with the values |
1123 arp_util.debug('arp_notes_pkg.update_cover()+');
1124
1125 /*------------------------------------------------+
1127 | passed in as parameters. |
1128 +------------------------------------------------*/
1129
1130
1131 l_notes_rec.note_id := p_note_id;
1132 l_notes_rec.last_updated_by := p_last_updated_by;
1133 l_notes_rec.last_update_date := p_last_update_date;
1134 l_notes_rec.last_update_login := p_last_update_login;
1135 l_notes_rec.created_by := p_created_by;
1136 l_notes_rec.creation_date := p_creation_date;
1137 l_notes_rec.note_type := p_note_type;
1138 l_notes_rec.text := p_text;
1139 l_notes_rec.customer_call_id := p_customer_call_id;
1140 l_notes_rec.customer_call_topic_id := p_customer_call_topic_id;
1141 l_notes_rec.call_action_id := p_call_action_id;
1142 l_notes_rec.customer_trx_id := p_customer_trx_id;
1143
1144
1145 /*-----------------------------------------+
1146 | Call the standard header table handler |
1147 +-----------------------------------------*/
1148
1149 update_p( l_notes_rec, p_note_id);
1150
1151 /*-------------------------------+
1152 | Populate the out NOCOPY parameters |
1153 +-------------------------------*/
1154
1155 p_last_updated_by := l_notes_rec.last_updated_by;
1156 p_last_update_date := l_notes_rec.last_update_date;
1157 p_last_update_login := l_notes_rec.last_update_login;
1158
1159 arp_util.debug('arp_notes_pkg.update_cover()-');
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 arp_util.debug('EXCEPTION : arp_notes_pkg.update_cover()');
1164
1165 arp_util.debug('----- parameters for update_cover() ' ||
1166 '-----');
1167 arp_util.debug('p_note_id : ' || p_note_id);
1168 arp_util.debug('p_last_updated_by : ' || p_last_updated_by);
1169 arp_util.debug('p_last_update_date : ' || p_last_update_date);
1170 arp_util.debug('p_last_update_login : ' || p_last_update_login);
1171 arp_util.debug('p_created_by : ' || p_created_by);
1172 arp_util.debug('p_creation_date : ' || p_creation_date);
1173 arp_util.debug('p_note_type : ' || p_note_type);
1174 /* Bug 3206020 */
1175 /* arp_util.debug('p_text : ' || p_text); */
1176 arp_util.debug('p_customer_call_id : ' || p_customer_call_id);
1177 arp_util.debug('p_customer_call_topic_id : ' || p_customer_call_topic_id);
1178 arp_util.debug('p_call_action_id : ' || p_call_action_id);
1179 arp_util.debug('p_customer_trx_id : ' || p_customer_trx_id);
1180
1181 RAISE;
1182
1183 END;
1184
1185 /*---------------------------------------------+
1186 | Package initialization section. |
1187 | Sets WHO column variables for later use. |
1188 +---------------------------------------------*/
1189
1190 BEGIN
1191
1192 pg_user_id := fnd_global.user_id;
1193 pg_conc_login_id := fnd_global.conc_login_id;
1194 pg_login_id := fnd_global.login_id;
1195 pg_prog_appl_id := fnd_global.prog_appl_id;
1196 pg_conc_program_id := fnd_global.conc_program_id;
1197
1198
1199 END ARP_NOTES_PKG;