[Home] [Help]
PACKAGE BODY: APPS.ARP_CC_PKG
Source
1 PACKAGE BODY arp_cc_pkg AS
2 /* $Header: ARCIECCB.pls 120.2 2005/06/14 18:50:21 vcrisost ship $ */
3 /*-----------------------------------------------------------------------------
4 | insert values into ar_customer_calls
5 +----------------------------------------------------------------------------*/
6 PROCEDURE insert_p (p_call_rec IN ar_customer_calls%rowtype,
7 p_customer_call_id OUT NOCOPY ar_customer_calls.customer_call_id%type) IS
8
9 l_created_by number;
10 l_creation_date date;
11 l_last_updated_by number;
12 l_last_update_login number;
13 l_last_update_date date;
14
15 BEGIN
16
17 l_created_by := FND_GLOBAL.USER_ID;
18 l_creation_date := sysdate;
19 l_last_update_login := FND_GLOBAL.LOGIN_ID;
20 l_last_update_date := sysdate;
21 l_last_updated_by := FND_GLOBAL.USER_ID;
22
23 /*-----------------------------+
24 | get the unique identifier |
25 +-----------------------------*/
26 p_customer_call_id :='';
27 SELECT ar_customer_calls_s.nextval
28 INTO p_customer_call_id
29 FROM DUAL;
30
31 /*-----------------------------+
32 | insert the record |
33 +-----------------------------*/
34
35 INSERT INTO ar_customer_calls
36 (customer_call_id,
37 customer_id,
38 collector_id,
39 call_date,
40 site_use_id,
41 status,
42 promise_date,
43 promise_amount,
44 call_outcome,
45 forecast_date,
46 collection_forecast,
47 contact_id,
48 phone_id,
49 fax_id,
50 reason_code,
51 currency_code,
52 attribute_category,
53 attribute1,
54 attribute2,
55 attribute3,
56 attribute4,
57 attribute5,
58 attribute6,
59 attribute7,
60 attribute8,
61 attribute9,
62 attribute10,
63 attribute11,
64 attribute12,
65 attribute13,
66 attribute14,
67 attribute15,
68 last_updated_by,
69 last_update_date,
70 last_update_login,
71 created_by,
72 creation_date,
73 follow_up_date,
74 follow_up_action,
75 complete_flag,
76 org_id)
77 VALUES
78 (p_customer_call_id,
79 p_call_rec.customer_id,
80 p_call_rec.collector_id,
81 p_call_rec.call_date,
82 p_call_rec.site_use_id,
83 p_call_rec.status,
84 p_call_rec.promise_date,
85 p_call_rec.promise_amount,
86 p_call_rec.call_outcome,
87 p_call_rec.forecast_date,
88 p_call_rec.collection_forecast,
89 p_call_rec.contact_id,
90 p_call_rec.phone_id,
91 p_call_rec.fax_id,
92 p_call_rec.reason_code,
93 p_call_rec.currency_code,
94 p_call_rec.attribute_category,
95 p_call_rec.attribute1,
96 p_call_rec.attribute2,
97 p_call_rec.attribute3,
98 p_call_rec.attribute4,
99 p_call_rec.attribute5,
100 p_call_rec.attribute6,
101 p_call_rec.attribute7,
102 p_call_rec.attribute8,
103 p_call_rec.attribute9,
104 p_call_rec.attribute10,
105 p_call_rec.attribute11,
106 p_call_rec.attribute12,
107 p_call_rec.attribute13,
108 p_call_rec.attribute14,
109 p_call_rec.attribute15,
110 l_last_updated_by,
111 l_last_update_date,
112 l_last_update_login,
113 l_created_by,
114 l_creation_date,
115 p_call_rec.follow_up_date,
116 p_call_rec.follow_up_action,
117 p_call_rec.complete_flag,
118 arp_standard.sysparm.org_id);
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 RAISE;
123
124 END insert_p;
125
126
127
128 /*------------------------------------------------------------------
129 | Insert values into ar_notes
130 +------------------------------------------------------------------*/
131
132 PROCEDURE insert_f_notes (
133 p_note_rec IN ar_notes%rowtype,
134 p_note_id OUT NOCOPY ar_notes.note_id%type) IS
135
136 l_created_by number;
137 l_creation_date date;
138 l_last_updated_by number;
139 l_last_update_login number;
140 l_last_update_date date;
141
142 BEGIN
143
144 l_created_by := FND_GLOBAL.USER_ID;
145 l_creation_date := sysdate;
146 l_last_update_login := FND_GLOBAL.LOGIN_ID;
147 l_last_update_date := sysdate;
148 l_last_updated_by := FND_GLOBAL.USER_ID;
149
150
151 /*------------------------------+
152 | get the unique id |
153 +------------------------------*/
154 select ar_notes_s.nextval
155 into p_note_id
156 from dual;
157
158 /*------------------------------+
159 | insert the record |
160 +------------------------------*/
161 insert into ar_notes (
162 note_id,
163 note_type,
164 text,
165 customer_call_id,
166 customer_call_topic_id,
167 call_action_id,
168 last_updated_by,
169 last_update_date,
170 last_update_login,
171 created_by,
172 creation_date)
173 values (
174 p_note_id,
175 p_note_rec.note_type,
176 p_note_rec.text,
177 p_note_rec.customer_call_id,
178 p_note_rec.customer_call_topic_id,
179 p_note_rec.call_action_id,
180 l_last_updated_by,
181 l_last_update_date,
182 l_last_update_login,
183 l_created_by,
184 l_creation_date);
185
186
187 EXCEPTION
188 WHEN OTHERS THEN
189 RAISE;
190
191 END insert_f_notes;
192
193
194
195 /* ---------------------------------------------------------------------+
196 | Table handler to insert records in the ar_customer_call_topics table |
197 +----------------------------------------------------------------------*/
198 PROCEDURE insert_f_topics (p_topic_rec IN ar_customer_call_topics%rowtype,
199 p_topic_id OUT NOCOPY ar_customer_Call_topics.customer_call_topic_id%type) IS
200
201 l_created_by number;
202 l_creation_date date;
203 l_last_updated_by number;
204 l_last_update_login number;
205 l_last_update_date date;
206
207 BEGIN
208
209 l_created_by := FND_GLOBAL.USER_ID;
210 l_creation_date := sysdate;
211 l_last_update_login := FND_GLOBAL.LOGIN_ID;
212 l_last_update_date := sysdate;
213 l_last_updated_by := FND_GLOBAL.USER_ID;
214
215
216 /*-----------------------+
217 | get the unique id |
218 +-----------------------*/
219 select ar_customer_call_topics_s.nextval
220 into p_topic_id
221 from dual;
222
223
224 /*-----------------------+
225 | insert the row |
226 +-----------------------*/
227 INSERT INTO ar_customer_call_topics (
228 customer_call_topic_id,
229 last_updated_by,
230 last_update_date,
231 last_update_login,
232 created_by,
233 creation_date,
234 customer_call_id,
235 customer_id,
236 collector_id,
237 call_date,
238 payment_schedule_id,
239 customer_trx_id,
240 customer_trx_line_id,
241 cash_receipt_id,
242 promise_date,
243 promise_amount,
244 follow_up_date,
245 follow_up_action,
246 follow_up_company_rep_id,
247 call_outcome,
248 forecast_date,
249 collection_forecast,
250 reason_code,
251 site_use_id,
252 contact_id,
253 phone_id,
254 attribute_category,
255 attribute1,
256 attribute2,
257 attribute3,
258 attribute4,
259 attribute5,
260 attribute6,
261 attribute7,
262 attribute8,
263 attribute9,
264 attribute10,
265 attribute11,
266 attribute12,
267 attribute13,
268 attribute14,
269 attribute15,
270 complete_flag,
271 org_id)
272 values (
273 p_topic_id,
274 l_last_updated_by,
275 l_last_update_date,
276 l_last_update_login,
277 l_created_by,
278 l_creation_date,
279 p_topic_rec.customer_call_id,
280 p_topic_rec.customer_id,
281 p_topic_rec.collector_id,
282 p_topic_rec.call_date,
283 p_topic_rec.payment_schedule_id,
284 p_topic_rec.customer_trx_id,
285 p_topic_rec.customer_trx_line_id,
286 p_topic_rec.cash_receipt_id,
287 p_topic_rec.promise_date,
288 p_topic_rec.promise_amount,
289 p_topic_rec.follow_up_date,
290 p_topic_rec.follow_up_action,
291 p_topic_rec.follow_up_company_rep_id,
292 p_topic_rec.call_outcome,
293 p_topic_rec.forecast_date,
294 p_topic_rec.collection_forecast,
295 p_topic_rec.reason_code,
296 p_topic_rec.site_use_id,
297 p_topic_rec.contact_id,
298 p_topic_rec.phone_id,
299 p_topic_rec.attribute_category,
300 p_topic_rec.attribute1,
301 p_topic_rec.attribute2,
302 p_topic_rec.attribute3,
303 p_topic_rec.attribute4,
304 p_topic_rec.attribute5,
305 p_topic_rec.attribute6,
306 p_topic_rec.attribute7,
307 p_topic_rec.attribute8,
308 p_topic_rec.attribute9,
309 p_topic_rec.attribute10,
310 p_topic_rec.attribute11,
311 p_topic_rec.attribute12,
312 p_topic_rec.attribute13,
313 p_topic_rec.attribute14,
314 p_topic_rec.attribute15,
315 p_topic_rec.complete_flag,
316 arp_standard.sysparm.org_id)
317 ;
318
319
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 RAISE;
324
325 END insert_f_topics;
326
327
328 /*---------------------------------------------------------------+
329 | Insert row into ar_call_actions |
330 +---------------------------------------------------------------*/
331 PROCEDURE insert_f_actions (p_action_rec IN ar_call_actions%rowtype,
332 p_action_id OUT NOCOPY ar_call_actions.call_action_id%type) IS
333
334
335
336 l_created_by number;
337 l_creation_date date;
338 l_last_updated_by number;
339 l_last_update_login number;
340 l_last_update_date date;
341
342 BEGIN
343
344 l_created_by := FND_GLOBAL.USER_ID;
345 l_creation_date := sysdate;
346 l_last_update_login := FND_GLOBAL.LOGIN_ID;
347 l_last_update_date := sysdate;
348 l_last_updated_by := FND_GLOBAL.USER_ID;
349
350 /*--------------------------+
351 | get unique identifier |
352 +--------------------------*/
353 select ar_call_actions_s.nextval
354 into p_action_id
355 from dual;
356
357 /*--------------------------+
358 | insert the row |
359 +--------------------------*/
360 insert into ar_call_actions (
361 call_action_id,
362 last_updated_by,
363 last_update_date,
364 last_update_login,
365 created_by,
366 creation_date,
367 customer_call_id,
368 customer_call_topic_id,
369 action_code,
370 action_amount,
371 partial_invoice_amount_flag,
372 complete_flag,
373 action_date)
374 values (
375 p_action_id,
376 l_last_updated_by,
377 l_last_update_date,
378 l_last_update_login,
379 l_created_by,
380 l_creation_date,
381 p_action_rec.customer_call_id,
382 p_action_rec.customer_call_topic_id,
383 p_action_rec.action_code,
384 p_action_rec.action_amount,
385 p_action_rec.partial_invoice_amount_flag,
386 p_action_rec.complete_flag,
387 p_action_rec.action_date);
388
389
390 END insert_f_actions;
391
392
393
394
395
396
397 PROCEDURE insert_f_notifications (p_notification_rec IN ar_action_notifications
398 %rowtype) IS
399 l_notif_id ar_action_notifications.action_notification_id%type;
400
401 l_created_by number;
402 l_creation_date date;
403 l_last_updated_by number;
404 l_last_update_login number;
405 l_last_update_date date;
406
407 BEGIN
408
409 l_created_by := FND_GLOBAL.USER_ID;
410 l_creation_date := sysdate;
411 l_last_update_login := FND_GLOBAL.LOGIN_ID;
412 l_last_update_date := sysdate;
413 l_last_updated_by := FND_GLOBAL.USER_ID;
414
415
416 /*------------------------+
417 | insert the unique id |
418 +-------------------------*/
419 select ar_action_notifications_s.nextval
420 into l_notif_id
421 from dual;
422
423 /*------------------------+\
424 | insert the row |
425 +-------------------------*/
426
427 insert into ar_action_notifications
428 (action_notification_id,
429 last_updated_by,
430 last_update_date,
431 last_update_login,
432 created_by,
433 creation_date,
434 call_action_id,
435 employee_id)
436 values
437 ( l_notif_id,
438 l_last_updated_by,
439 l_last_update_date,
440 l_last_update_login,
441 l_created_by,
442 l_creation_date,
443 p_notification_rec.call_action_id,
444 p_notification_rec.employee_id);
445
446 EXCEPTION
447 WHEN OTHERS THEN
448 RAISE;
449
450
451 END insert_f_notifications;
452
453
454
455
456 /*------------------------------------------------+
457 | Update status of Customer Call |
458 +------------------------------------------------*/
459 PROCEDURE update_p (p_call_rec IN ar_customer_calls%rowtype, p_rowid IN VARCHAR2) IS
460
461 l_last_updated_by number;
462 l_last_update_login number;
463 l_last_update_date date;
464
465 BEGIN
466
467 l_last_update_login := FND_GLOBAL.LOGIN_ID;
468 l_last_update_date := sysdate;
469 l_last_updated_by := FND_GLOBAL.USER_ID;
470
471
472 update ar_customer_calls
473 set status = p_call_rec.status,
474 last_update_login = l_last_update_login,
475 last_update_date = l_last_update_date,
476 last_updated_by = l_last_updated_by,
477 complete_flag = p_call_rec.complete_flag
478 where rowid = p_rowid;
479
480 END update_p;
481
482
483
484 /*------------------------------------------+
485 | Update complete flag in AR_CUSTOMER_CALL_TOPICS |
486 +------------------------------------------------*/
487 PROCEDURE update_f_topics (p_topic_rec ar_customer_call_topics%rowtype, p_rowid IN rowid) IS
488
489 cursor c1 is
490 select customer_call_id
491 from ar_customer_calls
492 where customer_call_id = p_topic_rec.customer_call_id
493 for update of customer_call_id NOWAIT;
494
495 l_last_updated_by number;
496 l_last_update_login number;
497 l_last_update_date date;
498
499 BEGIN
500
501 l_last_update_login := FND_GLOBAL.LOGIN_ID;
502 l_last_update_date := sysdate;
503 l_last_updated_by := FND_GLOBAL.USER_ID;
504
505 open c1;
506
507 update ar_customer_call_topics
508 set complete_flag = p_topic_rec.complete_flag,
512 where rowid = p_rowid;
509 last_updated_by = l_last_updated_by,
510 last_update_date = l_last_update_date,
511 last_update_login = l_last_update_login
513
514 close c1;
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 RAISE;
519
520 END update_f_topics;
521
522 /*----------------------------------------+
523 | Update complete flag in AR_CALL_ACTIONS |
524 +----------------------------------------*/
525 PROCEDURE update_f_actions (p_action_rec ar_call_actions%rowtype, p_rowid IN rowid) IS
526
527 cursor c1 is
528 select customer_call_id
529 from ar_customer_calls
530 where customer_call_id = p_action_rec.customer_call_id
531 for update of customer_call_id NOWAIT;
532
533 cursor c2 is
534 select customer_call_topic_id
535 from ar_customer_call_topics
536 where customer_call_topic_id = p_action_rec.customer_call_topic_id
537 for update of customer_call_topic_id NOWAIT;
538
539 l_last_updated_by number;
540 l_last_update_login number;
541 l_last_update_date date;
542
543 BEGIN
544
545 l_last_update_login := FND_GLOBAL.LOGIN_ID;
546 l_last_update_date := sysdate;
547 l_last_updated_by := FND_GLOBAL.USER_ID;
548
549 open c1;
550 open c2;
551
552 update ar_call_actions
553 set complete_flag = p_action_rec.complete_flag,
554 last_updated_by = l_last_updated_by,
555 last_update_date = l_last_update_date,
556 last_update_login = l_last_update_login
557 where rowid = p_rowid;
558
559 close c1;
560 close c2;
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 RAISE;
565
566 END update_f_actions;
567
568
569 FUNCTION check_dunning RETURN BOOLEAN IS
570 l_id number;
571
572 BEGIN
573
574 Select call_action_id
575 into l_id
576 from ar_call_actions
577 where call_action_id in (select call_action_id
578 from ar_call_actions
579 where customer_call_id = 1049
580 and action_code = 'XDUNNING')
581 or customer_call_topic_id in (select aca.customer_call_topic_id
582 from ar_call_actions aca, ar_customer_call_topics cct
583 where action_code = 'XDUNNING'
584 and aca.customer_call_topic_id = cct.customer_call_topic_id
585 and cct.customer_call_id = 1049);
586
587 return true;
588
589 EXCEPTION
590 WHEN NO_DATA_FOUND THEN
591 return false;
592 WHEN TOO_MANY_ROWS THEN
593 return true;
594 WHEN OTHERS THEN
595 RAISE;
596
597 END check_dunning;
598
599
600 END arp_cc_pkg;