[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Dummy constants for use in update and lock operations |
+--------------------------------------------------------*/
AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
p_notes_rec.last_updated_by := AR_NUMBER_DUMMY;
p_notes_rec.last_update_date := AR_DATE_DUMMY;
p_notes_rec.last_update_login := AR_NUMBER_DUMMY;
SELECT customer_trx_id
INTO l_customer_trx_id
FROM ar_notes
WHERE customer_trx_id = p_customer_trx_id
FOR UPDATE OF customer_trx_id NOWAIT;
SELECT *
INTO p_notes_rec
FROM ar_notes
WHERE note_id = p_note_id
FOR UPDATE OF note_id NOWAIT;
| changed from when they were first selected in the form. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_note_id - identifies the row to lock |
| p_notes_rec - note record for comparison |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| 19-DEC-95 Shelley Eitzen Added Customer Call Id |
| |
+===========================================================================*/
PROCEDURE lock_compare_p( p_notes_rec IN ar_notes%rowtype,
p_note_id IN ar_notes.note_id%type) IS
l_new_note_rec ar_notes%rowtype;
SELECT *
INTO l_new_note_rec
FROM ar_notes n
WHERE n.note_id = p_note_id
AND
(
NVL(n.note_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_notes_rec.note_id,
AR_NUMBER_DUMMY, n.note_id,
p_notes_rec.note_id),
AR_NUMBER_DUMMY
)
AND
NVL(n.note_type, AR_TEXT_DUMMY) =
NVL(
DECODE(p_notes_rec.note_type,
AR_TEXT_DUMMY, n.note_type,
p_notes_rec.note_type),
AR_TEXT_DUMMY
)
AND
NVL(n.text, AR_TEXT_DUMMY) =
NVL(
DECODE(p_notes_rec.text,
AR_TEXT_DUMMY, n.text,
p_notes_rec.text),
AR_TEXT_DUMMY
)
AND
NVL(n.customer_trx_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_notes_rec.customer_trx_id,
AR_NUMBER_DUMMY, n.customer_trx_id,
p_notes_rec.customer_trx_id),
AR_NUMBER_DUMMY
)
AND
NVL(n.customer_call_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_notes_rec.customer_call_id,
AR_NUMBER_DUMMY, n.customer_call_id),
AR_NUMBER_DUMMY
)
AND
NVL(n.customer_call_topic_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_notes_rec.customer_call_topic_id,
AR_NUMBER_DUMMY, n.customer_call_topic_id),
AR_NUMBER_DUMMY
)
AND
NVL(n.call_action_id, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_notes_rec.call_action_id ,
AR_NUMBER_DUMMY, n.call_action_id),
AR_NUMBER_DUMMY
)
AND
NVL(n.created_by, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_notes_rec.created_by,
AR_NUMBER_DUMMY, n.created_by,
p_notes_rec.created_by),
AR_NUMBER_DUMMY
)
AND
NVL(TRUNC(n.creation_date), AR_DATE_DUMMY) =
NVL(
DECODE(TRUNC(p_notes_rec.creation_date),
AR_DATE_DUMMY, TRUNC(n.creation_date),
TRUNC(p_notes_rec.creation_date)),
AR_DATE_DUMMY
)
AND
NVL(n.last_updated_by, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_notes_rec.last_updated_by,
AR_NUMBER_DUMMY, n.last_updated_by,
p_notes_rec.last_updated_by),
AR_NUMBER_DUMMY
)
AND
NVL(TRUNC(n.last_update_date), AR_DATE_DUMMY) =
NVL(
DECODE(TRUNC(p_notes_rec.last_update_date),
AR_DATE_DUMMY, TRUNC(n.last_update_date),
TRUNC(p_notes_rec.last_update_date)),
AR_DATE_DUMMY
)
AND
NVL(n.last_update_login, AR_NUMBER_DUMMY) =
NVL(
DECODE(p_notes_rec.last_update_login,
AR_NUMBER_DUMMY, n.last_update_login,
p_notes_rec.last_update_login),
AR_NUMBER_DUMMY
)
)
FOR UPDATE OF note_id NOWAIT;
SELECT *
INTO p_notes_rec
FROM ar_notes
WHERE note_id = p_note_id;
| delete_f_ct_id |
| |
| DESCRIPTION |
| This procedure deletes the ar_notes row identified by the |
| p_customer_trx_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_customer_trx_id - identifies the row to delete |
| OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| |
+===========================================================================*/
procedure delete_f_ct_id( p_customer_trx_id IN ar_notes.customer_trx_id%type)
IS
BEGIN
arp_util.debug('arp_notes_pkg.delete_f_ct_id()+');
DELETE FROM ar_notes
WHERE customer_trx_id = p_customer_trx_id;
arp_util.debug('arp_notes_pkg.delete_f_ct_id()-');
arp_util.debug('EXCEPTION: arp_notes_pkg.delete_f_ct_id()');
| update_p |
| |
| DESCRIPTION |
| This procedure updates the ar_notes row identified by the |
| p_note_id parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_note_id - identifies the row to update |
| OUT: |
| None |
| IN OUT: |
| p_notes_rec - contains the new column values |
| |
| RETURNS : NONE |
| |
| NOTES |
| set_to_dummy must be called before the values in p_notes_rec are |
| changed and this function is called. |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| 19-DEC-95 Shelley Eitzen Added Customer Call Id |
| |
+===========================================================================*/
PROCEDURE update_p( p_notes_rec IN OUT NOCOPY ar_notes%rowtype,
p_note_id IN ar_notes.note_id%type) IS
BEGIN
arp_util.debug('arp_notes_pkg.update_p()+ ' ||
to_char(sysdate, 'HH:MI:SS'));
SELECT
DECODE(p_notes_rec.last_updated_by,
AR_NUMBER_DUMMY, pg_user_id,
p_notes_rec.last_updated_by),
DECODE(p_notes_rec.last_update_date,
AR_DATE_DUMMY, sysdate,
p_notes_rec.last_update_date),
DECODE(p_notes_rec.last_update_login,
AR_NUMBER_DUMMY, nvl(pg_conc_login_id,
pg_login_id),
p_notes_rec.last_update_login)
INTO p_notes_rec.last_updated_by,
p_notes_rec.last_update_date,
p_notes_rec.last_update_login
FROM DUAL;
UPDATE ar_notes
SET customer_trx_id =
DECODE(p_notes_rec.customer_trx_id,
AR_NUMBER_DUMMY, customer_trx_id,
p_notes_rec.customer_trx_id),
customer_call_id =
DECODE(p_notes_rec.customer_call_id,
AR_NUMBER_DUMMY, customer_call_id,
p_notes_rec.customer_call_id),
customer_call_topic_id =
DECODE(p_notes_rec.customer_call_topic_id,
AR_NUMBER_DUMMY, customer_call_topic_id,
p_notes_rec.customer_call_topic_id),
call_action_id =
DECODE(p_notes_rec.call_action_id,
AR_NUMBER_DUMMY, call_action_id,
p_notes_rec.call_action_id),
note_type =
DECODE(p_notes_rec.note_type,
AR_TEXT_DUMMY, note_type,
p_notes_rec.note_type),
text =
DECODE(p_notes_rec.text,
AR_TEXT_DUMMY, text,
p_notes_rec.text),
created_by =
DECODE(p_notes_rec.created_by,
AR_NUMBER_DUMMY, created_by,
p_notes_rec.created_by),
creation_date =
DECODE(p_notes_rec.creation_date,
AR_DATE_DUMMY, creation_date,
p_notes_rec.creation_date),
last_updated_by = p_notes_rec.last_updated_by,
last_update_date = p_notes_rec.last_update_date,
last_update_login = p_notes_rec.last_update_login
WHERE note_id = p_note_id;
arp_util.debug('arp_notes_pkg.update_p()- ' ||
to_char(sysdate, 'HH:MI:SS'));
arp_util.debug('EXCEPTION: arp_notes_pkg.update_p()');
| insert_p |
| |
| DESCRIPTION |
| This procedure inserts a row into ar_notes that contains the |
| column values specified in the p_notes_rec parameter. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN OUT: |
| p_notes_rec - contains the new column values |
| OUT: |
| p_note_id - unique ID of the new row |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| 19-DEC-95 Shelley Eitzen Added Customer Call Id |
| |
+===========================================================================*/
PROCEDURE insert_p(
p_notes_rec IN OUT NOCOPY ar_notes%rowtype
) IS
l_note_id ar_notes.note_id%type;
arp_util.debug('arp_notes_pkg.insert_p()+');
SELECT ar_notes_s.nextval
INTO l_note_id
FROM dual;
p_notes_rec.last_updated_by := pg_user_id;
p_notes_rec.last_update_date := sysdate;
p_notes_rec.last_update_login := NVL(pg_conc_login_id, pg_login_id);
arp_util.debug('p_notes_rec.last_updated_by : ' || to_char(p_notes_rec.last_updated_by));
arp_util.debug('p_notes_rec.last_update_date : ' || to_char(p_notes_rec.last_update_date));
arp_util.debug('p_notes_rec.last_update_login : ' || to_char(p_notes_rec.last_update_login));
| insert the record |
+------------------------------*/
INSERT INTO ar_notes
(
note_id,
note_type,
text,
customer_trx_id,
customer_call_id,
customer_call_topic_id,
call_action_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date
)
VALUES
(
l_note_id,
p_notes_rec.note_type,
p_notes_rec.text,
p_notes_rec.customer_trx_id,
p_notes_rec.customer_call_id,
p_notes_rec.customer_call_topic_id,
p_notes_rec.call_action_id,
p_notes_rec.last_updated_by,
p_notes_rec.last_update_date,
p_notes_rec.last_update_login,
p_notes_rec.created_by,
p_notes_rec.creation_date
);
arp_util.debug('arp_notes_pkg.insert_p()-');
arp_util.debug('EXCEPTION: arp_notes_pkg.insert_p()');
| Displays the values of all columns except last_update_date. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_note_id |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE display_note_p( p_note_id IN ar_notes.note_id%type ) IS
l_notes_rec ar_notes%rowtype;
| Displays the values of all columns except last_update_date. |
| |
| SCOPE - PUBLIC |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| ARGUMENTS : IN: |
| p_notes_rec |
| OUT: |
| IN/ OUT: |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| |
+===========================================================================*/
PROCEDURE display_note_rec ( p_notes_rec IN ar_notes%rowtype ) IS
BEGIN
arp_util.debug('arp_notes_pkg.display_note_rec()+');
| p_last_updated_by |
| p_last_update_date |
| p_last_update_login |
| p_created_by |
| p_creation_date |
| p_note_type |
| p_text |
| p_customer_call_id |
| p_customer_call_topic_id |
| p_call_action_id |
| p_customer_trx_id |
| |
| OUT: |
| None |
| IN/ OUT: |
| None |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| 19-DEC-95 Shelley Eitzen Added Customer Call Id |
| |
+===========================================================================*/
PROCEDURE lock_compare_cover(
p_note_id IN ar_notes.note_id%type,
p_last_updated_by IN ar_notes.last_updated_by%type,
p_last_update_date IN ar_notes.last_update_date%type,
p_last_update_login IN ar_notes.last_update_login%type,
p_created_by IN ar_notes.created_by%type,
p_creation_date IN ar_notes.creation_date%type,
p_note_type IN ar_notes.note_type%type,
p_text IN ar_notes.text%type,
p_customer_call_id IN ar_notes.customer_call_id%type,
p_customer_call_topic_id IN ar_notes.customer_call_topic_id%type,
p_call_action_id IN ar_notes.call_action_id%type,
p_customer_trx_id IN ar_notes.customer_trx_id%type ) IS
l_notes_rec ar_notes%rowtype;
l_notes_rec.last_updated_by := p_last_updated_by;
l_notes_rec.last_update_date := p_last_update_date;
l_notes_rec.last_update_login := p_last_update_login;
arp_util.debug('p_last_updated_by : ' || p_last_updated_by);
arp_util.debug('p_last_update_date : ' || p_last_update_date);
arp_util.debug('p_last_update_login : ' || p_last_update_login);
| insert_cover |
| |
| DESCRIPTION |
| Converts column parameters to a ar_notes record and |
| inserts the notes record. |
| |
| SCOPE - PUBLIC |
| |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| IN: |
| p_note_id |
| p_note_type |
| p_text |
| p_customer_call_id |
| p_customer_call_topic_id |
| p_call_action_id |
| |
| OUT: |
| p_customer_trx_id |
| IN/ OUT: |
| p_last_updated_by |
| p_last_update_date |
| p_last_update_login |
| p_created_by |
| p_creation_date |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| 19-DEC-95 Shelley Eitzen Added Customer Call ID |
| |
+===========================================================================*/
PROCEDURE insert_cover(
p_note_type IN ar_notes.note_type%type,
p_text IN ar_notes.text%type,
p_customer_call_id IN ar_notes.customer_call_id%type,
p_customer_call_topic_id IN ar_notes.customer_call_topic_id%type,
p_call_action_id IN ar_notes.call_action_id%type,
p_customer_trx_id IN ar_notes.customer_trx_id%type,
p_note_id OUT NOCOPY ar_notes.note_id%type,
p_last_updated_by IN OUT NOCOPY ar_notes.last_updated_by%type,
p_last_update_date IN OUT NOCOPY ar_notes.last_update_date%type,
p_last_update_login IN OUT NOCOPY ar_notes.last_update_login%type,
p_created_by IN OUT NOCOPY ar_notes.created_by%type,
p_creation_date IN OUT NOCOPY ar_notes.creation_date%type ) IS
l_notes_rec ar_notes%rowtype;
arp_util.debug('arp_notes_pkg.insert_cover()+');
l_notes_rec.last_updated_by := p_last_updated_by;
l_notes_rec.last_update_date := p_last_update_date;
l_notes_rec.last_update_login := p_last_update_login;
insert_p( l_notes_rec );
p_last_updated_by := l_notes_rec.last_updated_by;
p_last_update_date := l_notes_rec.last_update_date;
p_last_update_login := l_notes_rec.last_update_login;
arp_util.debug('arp_notes_pkg.insert_cover()-');
arp_util.debug('EXCEPTION : arp_notes_pkg.insert_cover()');
arp_util.debug('----- parameters for insert_cover() ' ||
'-----');
arp_util.debug('p_last_updated_by : ' || p_last_updated_by);
arp_util.debug('p_last_update_date : ' || p_last_update_date);
arp_util.debug('p_last_update_login : ' || p_last_update_login);
| update_cover |
| |
| DESCRIPTION |
| Converts column parameters to a notes record and |
| updates the notes record. |
| |
| SCOPE - PUBLIC |
| |
| |
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
| arp_util.debug |
| |
| IN: |
| p_note_id |
| p_created_by |
| p_creation_date |
| p_note_type |
| p_text |
| p_customer_call_id |
| p_customer_call_topic_id |
| p_call_action_id |
| p_customer_trx_id |
| |
| OUT: |
| None |
| IN/ OUT: |
| p_last_updated_by |
| p_last_update_date |
| p_last_update_login |
| |
| RETURNS : NONE |
| |
| NOTES |
| |
| MODIFICATION HISTORY |
| 16-DEC-95 Charlie Tomberg Created |
| 19-DEC-95 Shelley Eitzen Added Customer Call Id |
| |
+===========================================================================*/
PROCEDURE update_cover(
p_note_id IN ar_notes.note_id%type,
p_created_by IN ar_notes.created_by%type,
p_creation_date IN ar_notes.creation_date%type,
p_note_type IN ar_notes.note_type%type,
p_text IN ar_notes.text%type,
p_customer_call_id IN ar_notes.customer_call_id%type,
p_customer_call_topic_id IN ar_notes.customer_call_topic_id%type,
p_call_action_id IN ar_notes.call_action_id%type,
p_customer_trx_id IN ar_notes.customer_trx_id%type,
p_last_updated_by IN OUT NOCOPY ar_notes.last_updated_by%type,
p_last_update_date IN OUT NOCOPY ar_notes.last_update_date%type,
p_last_update_login IN OUT NOCOPY ar_notes.last_update_login%type ) IS
l_notes_rec ar_notes%rowtype;
arp_util.debug('arp_notes_pkg.update_cover()+');
l_notes_rec.last_updated_by := p_last_updated_by;
l_notes_rec.last_update_date := p_last_update_date;
l_notes_rec.last_update_login := p_last_update_login;
update_p( l_notes_rec, p_note_id);
p_last_updated_by := l_notes_rec.last_updated_by;
p_last_update_date := l_notes_rec.last_update_date;
p_last_update_login := l_notes_rec.last_update_login;
arp_util.debug('arp_notes_pkg.update_cover()-');
arp_util.debug('EXCEPTION : arp_notes_pkg.update_cover()');
arp_util.debug('----- parameters for update_cover() ' ||
'-----');
arp_util.debug('p_last_updated_by : ' || p_last_updated_by);
arp_util.debug('p_last_update_date : ' || p_last_update_date);
arp_util.debug('p_last_update_login : ' || p_last_update_login);