eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ARP_CC_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 4


|  insert values into ar_customer_calls
+----------------------------------------------------------------------------*/
PROCEDURE insert_p (p_call_rec        IN  ar_customer_calls%rowtype,
                    p_customer_call_id  OUT NOCOPY ar_customer_calls.customer_call_id%type) IS

l_created_by number;

Line: 11


l_last_updated_by number;

Line: 12


l_last_update_login number;

Line: 13


l_last_update_date date;

Line: 19


l_last_update_login	:= FND_GLOBAL.LOGIN_ID;

Line: 20


l_last_update_date	:= sysdate;

Line: 21


l_last_updated_by	:= FND_GLOBAL.USER_ID;

Line: 27


SELECT ar_customer_calls_s.nextval
INTO p_customer_call_id
FROM DUAL;

Line: 32


|  insert the record           |
+-----------------------------*/

INSERT INTO ar_customer_calls
        (customer_call_id,
        customer_id,
        collector_id,
        call_date,
        site_use_id,
        status,
        promise_date,
        promise_amount,
        call_outcome,
        forecast_date,
        collection_forecast,
        contact_id,
        phone_id,
        fax_id,
        reason_code,
        currency_code,
        attribute_category,
        attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15,
        last_updated_by,
        last_update_date,
        last_update_login,
        created_by,
        creation_date,
        follow_up_date,
        follow_up_action,
        complete_flag,
        org_id)
VALUES
        (p_customer_call_id,
        p_call_rec.customer_id,
        p_call_rec.collector_id,
        p_call_rec.call_date,
        p_call_rec.site_use_id,
        p_call_rec.status,
        p_call_rec.promise_date,
        p_call_rec.promise_amount,
        p_call_rec.call_outcome,
        p_call_rec.forecast_date,
        p_call_rec.collection_forecast,
        p_call_rec.contact_id,
        p_call_rec.phone_id,
        p_call_rec.fax_id,
        p_call_rec.reason_code,
        p_call_rec.currency_code,
        p_call_rec.attribute_category,
        p_call_rec.attribute1,
        p_call_rec.attribute2,
        p_call_rec.attribute3,
        p_call_rec.attribute4,
        p_call_rec.attribute5,
        p_call_rec.attribute6,
        p_call_rec.attribute7,
        p_call_rec.attribute8,
        p_call_rec.attribute9,
        p_call_rec.attribute10,
        p_call_rec.attribute11,
        p_call_rec.attribute12,
        p_call_rec.attribute13,
        p_call_rec.attribute14,
        p_call_rec.attribute15,
        l_last_updated_by,
        l_last_update_date,
        l_last_update_login,
        l_created_by,
        l_creation_date,
        p_call_rec.follow_up_date,
        p_call_rec.follow_up_action,
        p_call_rec.complete_flag,
        arp_standard.sysparm.org_id);

Line: 124


END insert_p;

Line: 129


|  Insert values into ar_notes
+------------------------------------------------------------------*/

PROCEDURE insert_f_notes (
                    p_note_rec        IN  ar_notes%rowtype,
                    p_note_id  OUT NOCOPY ar_notes.note_id%type) IS

l_created_by number;

Line: 138


l_last_updated_by number;

Line: 139


l_last_update_login number;

Line: 140


l_last_update_date date;

Line: 146


l_last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 147


l_last_update_date      := sysdate;

Line: 148


l_last_updated_by       := FND_GLOBAL.USER_ID;

Line: 154


select ar_notes_s.nextval
into p_note_id
from dual;

Line: 159


|  insert the record            |
+------------------------------*/
insert into ar_notes (
  note_id,
  note_type,
  text,
  customer_call_id,
  customer_call_topic_id,
  call_action_id,
  last_updated_by,
  last_update_date,
  last_update_login,
  created_by,
  creation_date)
values (
  p_note_id,
  p_note_rec.note_type,
  p_note_rec.text,
  p_note_rec.customer_call_id,
  p_note_rec.customer_call_topic_id,
  p_note_rec.call_action_id,
  l_last_updated_by,
  l_last_update_date,
  l_last_update_login,
  l_created_by,
  l_creation_date);

Line: 191


END insert_f_notes;

Line: 196


|  Table handler to insert records in the ar_customer_call_topics table |
+----------------------------------------------------------------------*/
PROCEDURE insert_f_topics (p_topic_rec IN ar_customer_call_topics%rowtype,
                           p_topic_id OUT NOCOPY ar_customer_Call_topics.customer_call_topic_id%type) IS

l_created_by number;

Line: 203


l_last_updated_by number;

Line: 204


l_last_update_login number;

Line: 205


l_last_update_date date;

Line: 211


l_last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 212


l_last_update_date      := sysdate;

Line: 213


l_last_updated_by       := FND_GLOBAL.USER_ID;

Line: 219


select ar_customer_call_topics_s.nextval
into p_topic_id
from dual;

Line: 225


| insert the row         |
+-----------------------*/
INSERT INTO ar_customer_call_topics (
	customer_call_topic_id,
	last_updated_by,
	last_update_date,
	last_update_login,
	created_by,
	creation_date,
	customer_call_id,
	customer_id,
	collector_id,
	call_date,
	payment_schedule_id,
	customer_trx_id,
	customer_trx_line_id,
        cash_receipt_id,
	promise_date,
	promise_amount,
	follow_up_date,
	follow_up_action,
	follow_up_company_rep_id,
	call_outcome,
	forecast_date,
	collection_forecast,
	reason_code,
	site_use_id,
	contact_id,
	phone_id,
	attribute_category,
	attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15,
        complete_flag,
        org_id)
values (
	p_topic_id,
	l_last_updated_by,
        l_last_update_date,
        l_last_update_login,
        l_created_by,
        l_creation_date,
        p_topic_rec.customer_call_id,
        p_topic_rec.customer_id,
        p_topic_rec.collector_id,
        p_topic_rec.call_date,
        p_topic_rec.payment_schedule_id,
        p_topic_rec.customer_trx_id,
        p_topic_rec.customer_trx_line_id,
        p_topic_rec.cash_receipt_id,
        p_topic_rec.promise_date,
        p_topic_rec.promise_amount,
        p_topic_rec.follow_up_date,
        p_topic_rec.follow_up_action,
        p_topic_rec.follow_up_company_rep_id,
        p_topic_rec.call_outcome,
        p_topic_rec.forecast_date,
        p_topic_rec.collection_forecast,
        p_topic_rec.reason_code,
        p_topic_rec.site_use_id,
        p_topic_rec.contact_id,
        p_topic_rec.phone_id,
        p_topic_rec.attribute_category,
        p_topic_rec.attribute1,
        p_topic_rec.attribute2,
        p_topic_rec.attribute3,
        p_topic_rec.attribute4,
        p_topic_rec.attribute5,
        p_topic_rec.attribute6,
        p_topic_rec.attribute7,
        p_topic_rec.attribute8,
        p_topic_rec.attribute9,
        p_topic_rec.attribute10,
        p_topic_rec.attribute11,
        p_topic_rec.attribute12,
        p_topic_rec.attribute13,
        p_topic_rec.attribute14,
        p_topic_rec.attribute15,
        p_topic_rec.complete_flag,
        arp_standard.sysparm.org_id)
;

Line: 325


END insert_f_topics;

Line: 329


|  Insert row into ar_call_actions                               |
+---------------------------------------------------------------*/
PROCEDURE insert_f_actions (p_action_rec IN ar_call_actions%rowtype,
                            p_action_id OUT NOCOPY ar_call_actions.call_action_id%type) IS



l_created_by number;

Line: 338


l_last_updated_by number;

Line: 339


l_last_update_login number;

Line: 340


l_last_update_date date;

Line: 346


l_last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 347


l_last_update_date      := sysdate;

Line: 348


l_last_updated_by       := FND_GLOBAL.USER_ID;

Line: 353


select ar_call_actions_s.nextval
into p_action_id
from dual;

Line: 358


| insert the row            |
+--------------------------*/
insert into ar_call_actions (
	call_action_id,
	last_updated_by,
	last_update_date,
	last_update_login,
	created_by,
	creation_date,
	customer_call_id,
	customer_call_topic_id,
	action_code,
	action_amount,
	partial_invoice_amount_flag,
	complete_flag,
        action_date)
values (
	p_action_id,
	l_last_updated_by,
        l_last_update_date,
        l_last_update_login,
        l_created_by,
        l_creation_date,
	p_action_rec.customer_call_id,
        p_action_rec.customer_call_topic_id,
        p_action_rec.action_code,
        p_action_rec.action_amount,
        p_action_rec.partial_invoice_amount_flag,
        p_action_rec.complete_flag,
        p_action_rec.action_date);

Line: 390


END insert_f_actions;

Line: 397


PROCEDURE insert_f_notifications (p_notification_rec IN ar_action_notifications
%rowtype) IS
l_notif_id ar_action_notifications.action_notification_id%type;

Line: 403


l_last_updated_by number;

Line: 404


l_last_update_login number;

Line: 405


l_last_update_date date;

Line: 411


l_last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 412


l_last_update_date      := sysdate;

Line: 413


l_last_updated_by       := FND_GLOBAL.USER_ID;

Line: 417


| insert the unique id    |
+-------------------------*/
select ar_action_notifications_s.nextval
into l_notif_id
from dual;

Line: 424


| insert the row          |
+-------------------------*/

insert into ar_action_notifications
(action_notification_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
call_action_id,
employee_id)
values
( l_notif_id,
l_last_updated_by,
l_last_update_date,
l_last_update_login,
l_created_by,
l_creation_date,
p_notification_rec.call_action_id,
p_notification_rec.employee_id);

Line: 451


END insert_f_notifications;

Line: 457


| Update status of Customer Call                  |
+------------------------------------------------*/
PROCEDURE update_p (p_call_rec IN ar_customer_calls%rowtype, p_rowid IN VARCHAR2) IS

l_last_updated_by number;

Line: 462


l_last_update_login number;

Line: 463


l_last_update_date date;

Line: 467


l_last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 468


l_last_update_date      := sysdate;

Line: 469


l_last_updated_by       := FND_GLOBAL.USER_ID;

Line: 472


  update ar_customer_calls
  set    status            = p_call_rec.status,
         last_update_login = l_last_update_login,
         last_update_date  = l_last_update_date,
         last_updated_by   = l_last_updated_by,
         complete_flag     = p_call_rec.complete_flag
  where  rowid = p_rowid;

Line: 480


END update_p;

Line: 485


| Update complete flag in AR_CUSTOMER_CALL_TOPICS |
+------------------------------------------------*/
PROCEDURE update_f_topics (p_topic_rec ar_customer_call_topics%rowtype, p_rowid IN rowid) IS

cursor c1 is
select customer_call_id
from ar_customer_calls
where customer_call_id = p_topic_rec.customer_call_id
for update of customer_call_id NOWAIT;

Line: 495


l_last_updated_by number;

Line: 496


l_last_update_login number;

Line: 497


l_last_update_date date;

Line: 501


l_last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 502


l_last_update_date      := sysdate;

Line: 503


l_last_updated_by       := FND_GLOBAL.USER_ID;

Line: 507


  update ar_customer_call_topics
  set complete_flag = p_topic_rec.complete_flag,
  last_updated_by = l_last_updated_by,
  last_update_date = l_last_update_date,
  last_update_login = l_last_update_login
  where rowid = p_rowid;

Line: 520


END update_f_topics;

Line: 523


| Update complete flag in AR_CALL_ACTIONS |
+----------------------------------------*/
PROCEDURE update_f_actions (p_action_rec ar_call_actions%rowtype, p_rowid IN rowid) IS

cursor c1 is
select customer_call_id
from ar_customer_calls
where customer_call_id = p_action_rec.customer_call_id
for update of customer_call_id NOWAIT;

Line: 534


select customer_call_topic_id
from ar_customer_call_topics
where customer_call_topic_id = p_action_rec.customer_call_topic_id
for update of customer_call_topic_id NOWAIT;

Line: 539


l_last_updated_by number;

Line: 540


l_last_update_login number;

Line: 541


l_last_update_date date;

Line: 545


l_last_update_login     := FND_GLOBAL.LOGIN_ID;

Line: 546


l_last_update_date      := sysdate;

Line: 547


l_last_updated_by       := FND_GLOBAL.USER_ID;

Line: 552


  update ar_call_actions
  set complete_flag = p_action_rec.complete_flag,
  last_updated_by = l_last_updated_by,
  last_update_date = l_last_update_date,
  last_update_login = l_last_update_login
  where rowid = p_rowid;

Line: 566


END update_f_actions;

Line: 574


Select call_action_id
into l_id
from ar_call_actions
where call_action_id in (select call_action_id
                             from ar_call_actions
                             where customer_call_id = 1049
                             and action_code = 'XDUNNING')
or customer_call_topic_id in (select aca.customer_call_topic_id
                         from ar_call_actions aca, ar_customer_call_topics cct
                         where action_code = 'XDUNNING'
                         and aca.customer_call_topic_id = cct.customer_call_topic_id
                         and cct.customer_call_id = 1049);