[Home] [Help]
PACKAGE BODY: APPS.AR_BUS_EVENT_COVER
Source
1 PACKAGE BODY AR_BUS_EVENT_COVER AS
2 /* $Header: ARBEPKGB.pls 120.17 2007/10/17 15:30:47 mraymond ship $*/
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6 FUNCTION isRefreshProgramRunning RETURN BOOLEAN IS
7 CURSOR C1 IS
8 select request_id
9 from AR_CONC_PROCESS_REQUESTS
10 where CONCURRENT_PROGRAM_NAME = 'ARSUMREF';
11 l_request_id number;
12 BEGIN
13
14 OPEN C1;
15
16 FETCH C1 INTO l_request_id;
17
18 IF C1%NOTFOUND THEN
19 return false;
20 ELSE
21 return true;
22 END IF;
23
24 CLOSE C1;
25
26 END isRefreshProgramRunning;
27
28 PROCEDURE insert_events_hist (p_be_name VARCHAR2,
29 p_event_key VARCHAR2,
30 p_ps_id NUMBER,
31 p_ctx_id NUMBER,
32 p_cr_id NUMBER,
33 p_ra_id NUMBER,
34 p_adj_id NUMBER,
35 p_hist_id NUMBER,
36 p_req_id NUMBER
37 )
38 IS
39
40 BEGIN
41
42 INSERT INTO AR_SUM_REF_EVENT_HIST
43 (business_event_name,
44 event_key,
45 payment_schedule_id,
46 customer_trx_id,
47 cash_receipt_id,
48 receivable_application_id,
49 adjustment_id,
50 history_id,
51 request_id,
52 last_update_date,
53 last_update_by,
54 creation_date,
55 created_by,
56 last_update_login)
57 VALUES (p_be_name,
58 p_event_key,
59 p_ps_id,
60 p_ctx_id,
61 p_cr_id,
62 p_ra_id,
63 p_adj_id,
64 p_hist_id,
65 p_req_id,
66 sysdate,
67 FND_GLOBAL.user_id,
68 sysdate,
69 FND_GLOBAL.user_id,
70 FND_GLOBAL.login_id);
71
72 END insert_events_hist;
73
74 PROCEDURE p_insert_trx_sum_hist(p_trx_sum_hist_rec IN AR_TRX_SUMMARY_HIST%rowtype,
75 p_history_id OUT NOCOPY NUMBER,
76 p_trx_type IN VARCHAR2,
77 p_event_type IN VARCHAR2 DEFAULT NULL)
78 IS
79 l_event_name VARCHAR2(100);
80 l_previous_history_id NUMBER;
81 l_history_id NUMBER;
82 CURSOR get_prev_hist_id (p_ps_id IN NUMBER) IS
83 SELECT max(history_id)
84 FROM AR_TRX_SUMMARY_HIST
85 WHERE payment_schedule_id = p_ps_id;
86
87 BEGIN
88 OPEN get_prev_hist_id (p_trx_sum_hist_rec.payment_schedule_id);
89
90 FETCH get_prev_hist_id INTO l_previous_history_id;
91
92 CLOSE get_prev_hist_id;
93
94
95 IF p_trx_type = 'INV' AND
96 p_event_type = 'INCOMPLETE_TRX' THEN
97
98 l_event_name := 'oracle.apps.ar.transaction.Invoice.incomplete';
99 ELSIF p_trx_type = 'INV' AND
100 p_event_type = 'MODIFY_TRX' THEN
101
102 l_event_name := 'oracle.apps.ar.transaction.Invoice.modify';
103 ELSIF p_trx_type = 'CM' AND
104 p_event_type = 'INCOMPLETE_TRX' THEN
105
106 l_event_name := 'oracle.apps.ar.transaction.CreditMemo.incomplete';
107 ELSIF p_trx_type = 'CM' AND
108 p_event_type = 'MODIFY_TRX' THEN
109
110 l_event_name := 'oracle.apps.ar.transaction.CreditMemo.modify';
111 ELSIF p_trx_type = 'DM' AND
112 p_event_type = 'INCOMPLETE_TRX' THEN
113
114 l_event_name := 'oracle.apps.ar.transaction.DebitMemo.incomplete';
115 ELSIF p_trx_type = 'DM' AND
116 p_event_type = 'MODIFY_TRX' THEN
117
118 l_event_name := 'oracle.apps.ar.transaction.DebitMemo.modify';
119 ELSIF p_trx_type = 'DEP' AND
120 p_event_type = 'INCOMPLETE_TRX' THEN
121
122 l_event_name := 'oracle.apps.ar.transaction.Deposit.incomplete';
123 ELSIF p_trx_type = 'DEP' AND
124 p_event_type = 'MODIFY_TRX' THEN
125
126 l_event_name := 'oracle.apps.ar.transaction.Deposit.modify';
127 ELSIF p_trx_type = 'BR' AND
128 p_event_type = 'INCOMPLETE_TRX' THEN
129
130 l_event_name := 'oracle.apps.ar.transaction.BillsReceivables.incomplete';
131 ELSIF p_trx_type = 'BR' AND
132 p_event_type = 'MODIFY_TRX' THEN
133
134 l_event_name := 'oracle.apps.ar.transaction.BillsReceivables.modify';
135 ELSIF p_trx_type = 'GUAR' AND
136 p_event_type = 'INCOMPLETE_TRX' THEN
137
138 l_event_name := 'oracle.apps.ar.transaction.Guarantee.incomplete';
139 ELSIF p_trx_type = 'GUAR' AND
140 p_event_type = 'MODIFY_TRX' THEN
141
142 l_event_name := 'oracle.apps.ar.transaction.Guarantee.modify';
143 ELSIF p_trx_type = 'CB' AND
144 p_event_type = 'MODIFY_TRX' THEN
145
146 l_event_name := 'oracle.apps.ar.transaction.ChargeBack.modify';
147 ELSIF p_trx_type = 'PMT' AND
148 p_event_type = 'MODIFY_PMT' THEN
149
150 l_event_name := 'oracle.apps.ar.receipts.CashReceipt.modify';
151 ELSIF p_trx_type = 'PMT' AND
152 p_event_type = 'DM_REVERSE_PMT' THEN
153
154 l_event_name := 'oracle.apps.ar.receipts.CashReceipt.DebitMemoReverse';
155 ELSIF p_trx_type = 'PMT' AND
156 p_event_type = 'REVERSE_PMT' THEN
157
158 l_event_name := 'oracle.apps.ar.receipts.CashReceipt.reverse';
159 ELSIF p_trx_type = 'PMT' AND
160 p_event_type = 'DELETE_PMT' THEN
161
162 l_event_name := 'oracle.apps.ar.receipts.CashReceipt.Delete';
163 END IF;
164
165
166 SELECT AR_TRX_SUMMARY_HIST_S.nextval
167 INTO l_history_id
168 FROM dual;
169
170 INSERT INTO AR_TRX_SUMMARY_HIST
171 (history_id,
172 last_update_date,
173 last_updated_by,
174 creation_date,
175 created_by,
176 last_update_login,
177 customer_trx_id,
178 cash_receipt_id,
179 payment_schedule_id,
180 currency_code,
181 previous_history_id,
182 due_date,
183 amount_in_dispute,
184 amount_due_original,
185 amount_due_remaining,
186 amount_adjusted,
187 complete_flag,
188 customer_id,
189 site_use_id,
190 trx_date,
191 installments,
192 event_name)
193 VALUES
194 (l_history_id,
195 sysdate,
196 fnd_global.user_id,
197 sysdate,
198 fnd_global.user_id,
199 fnd_global.login_id,
200 p_trx_sum_hist_rec.customer_trx_id,
201 p_trx_sum_hist_rec.cash_receipt_id,
202 p_trx_sum_hist_rec.payment_schedule_id,
203 p_trx_sum_hist_rec.currency_code,
204 l_previous_history_id,
205 p_trx_sum_hist_rec.due_date,
206 p_trx_sum_hist_rec.amount_in_dispute,
207 p_trx_sum_hist_rec.amount_due_original,
208 p_trx_sum_hist_rec.amount_due_remaining,
209 p_trx_sum_hist_rec.amount_adjusted,
210 p_trx_sum_hist_rec.complete_flag,
211 p_trx_sum_hist_rec.customer_id,
212 p_trx_sum_hist_rec.site_use_id,
213 p_trx_sum_hist_rec.trx_date,
214 p_trx_sum_hist_rec.installments,
215 l_event_name
216 );
217 p_history_id := l_history_id;
218 END;
219
220
221 PROCEDURE Raise_Trx_Creation_Event
222 ( p_doc_type IN VARCHAR2,
223 p_customer_trx_id IN NUMBER,
224 p_prev_cust_old_state IN prev_cust_old_state_tab
225 ) IS
226 l_list WF_PARAMETER_LIST_T;
227 l_param WF_PARAMETER_T;
228 l_key VARCHAR2(240);
229 l_exist VARCHAR2(1);
230 l_event_name VARCHAR2(50);
231 l_prev_ps_op_status NUMBER;
232 l_prev_ps_cl_status NUMBER;
233 CURSOR cm_prev_ctx (p_ctx_id IN NUMBER) IS
234 select prev_trx_ps.status,
235 prev_trx_ps.payment_schedule_id,
236 prev_trx_ps.amount_applied,
237 prev_trx_ps.class,
238 prev_trx_ps.amount_credited,
239 prev_trx_ps.due_date
240 from ar_payment_schedules prev_trx_ps,
241 ra_customer_trx ctx
242 where ctx.customer_trx_id = p_ctx_id
243 and ctx.previous_customer_trx_id = prev_trx_ps.customer_trx_id
244 ;
245
246 l_prev_trx_op_count NUMBER;
247 l_cm_op_trx_count NUMBER;
248 l_cm_cl_trx_count NUMBER;
249 l_prev_trx_app_amt NUMBER;
250 l_past_due_inv_amt NUMBER;
251 l_past_due_inv_count NUMBER;
252
253 BEGIN
254 IF p_doc_type = 'INV' THEN
255 l_event_name := 'oracle.apps.ar.transaction.Invoice.complete';
256 ELSIF p_doc_type = 'CM' THEN
257 l_event_name := 'oracle.apps.ar.transaction.CreditMemo.complete';
258 ELSIF p_doc_type = 'DEP' THEN
259 l_event_name := 'oracle.apps.ar.transaction.Deposit.complete';
260 ELSIF p_doc_type = 'DM' THEN
261 l_event_name := 'oracle.apps.ar.transaction.DebitMemo.complete';
262 ELSIF p_doc_type = 'CB' THEN
263 l_event_name := 'oracle.apps.ar.transaction.ChargeBack.create';
264 ELSIF p_doc_type = 'GUAR' THEN
265 l_event_name := 'oracle.apps.ar.transaction.Guarantee.complete';
266 ELSIF p_doc_type = 'BR' THEN
267 l_event_name := 'oracle.apps.ar.transaction.BillsReceivables.complete';
268 END IF;
269
270 --Get the item key
271 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
272 p_customer_trx_id );
273
274 IF (isRefreshProgramRunning) THEN
275
276
277 insert_events_hist (p_be_name => l_event_name,
278 p_event_key => l_key,
279 p_ps_id => null,
280 p_ctx_id => p_customer_trx_id,
281 p_cr_id => null,
282 p_ra_id => null,
283 p_adj_id => null,
284 p_hist_id => null,
285 p_req_id => null);
286 ELSE
287
288 -- initialization of object variables
289 l_list := WF_PARAMETER_LIST_T();
290
291 -- Add Context values to the list
292 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
293
294 -- add more parameters to the parameters list
295 wf_event.AddParameterToList(p_name => 'CUSTOMER_TRX_ID',
296 p_value => p_customer_trx_id,
297 p_parameterlist => l_list);
298
299
300 IF p_doc_type = 'CM' THEN
301 l_cm_cl_trx_count := 0;
302 l_cm_op_trx_count := 0;
303 l_prev_trx_app_amt := 0;
304 l_past_due_inv_amt := 0;
305 l_past_due_inv_count := 0;
306
307 FOR i in cm_prev_ctx(p_customer_trx_id) LOOP
308
309
310 --check if the status of this PS has changed.
311 IF p_prev_cust_old_state(i.payment_schedule_id).status = 'OP' AND
312 i.status = 'CL' THEN
313 l_cm_cl_trx_count := l_cm_cl_trx_count + 1;
314 l_prev_trx_app_amt := l_prev_trx_app_amt + nvl(i.amount_applied,0);
315
316 IF i.due_date < sysdate and i.class = 'INV' THEN
317 l_past_due_inv_count := l_past_due_inv_count - 1;
318 END IF;
319 ELSIF p_prev_cust_old_state(i.payment_schedule_id).status = 'CL' AND
320 i.status = 'OP' THEN
321 l_cm_op_trx_count := l_cm_op_trx_count + 1;
322 l_prev_trx_app_amt := l_prev_trx_app_amt - nvl(i.amount_applied,0);
323
324 IF i.due_date < sysdate and i.class = 'INV' THEN
325 l_past_due_inv_count := l_past_due_inv_count + 1;
326 END IF;
327 END IF;
328
329 IF i.due_date < sysdate and i.class = 'INV' THEN
330 l_past_due_inv_amt := l_past_due_inv_amt +
331 ( nvl(p_prev_cust_old_state(i.payment_schedule_id).amount_credited,0) -
332 nvl(i.amount_credited,0));
333 END IF;
334
335 END LOOP;
336
337
338 l_prev_trx_op_count := l_cm_op_trx_count - l_cm_cl_trx_count;
339
340 -- add more parameters to the parameters list
341 wf_event.AddParameterToList(p_name => 'PREV_TRX_OP_COUNT',
342 p_value => l_prev_trx_op_count,
343 p_parameterlist => l_list);
344
345 wf_event.AddParameterToList(p_name => 'PREV_TRX_APP_AMT',
346 p_value => l_prev_trx_app_amt,
347 p_parameterlist => l_list);
348
349 wf_event.AddParameterToList(p_name => 'PAST_DUE_INV_AMT',
350 p_value => l_past_due_inv_amt,
351 p_parameterlist => l_list);
352 wf_event.AddParameterToList(p_name => 'PAST_DUE_INV_COUNT',
353 p_value => l_past_due_inv_count,
354 p_parameterlist => l_list);
355
356 END IF;
357
358
359
360
361 -- Raise Event
362 AR_CMGT_EVENT_PKG.raise_event(
363 p_event_name => l_event_name,
364 p_event_key => l_key,
365 p_parameters => l_list );
366
367 l_list.DELETE;
368 END IF;
369
370 EXCEPTION
371 WHEN others THEN
372 IF PG_DEBUG in ('Y', 'C') THEN
373 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
374 END IF;
375
376 END Raise_Trx_Creation_Event;
377
378 PROCEDURE Raise_Trx_Incomplete_Event
379 ( p_doc_type IN VARCHAR2,
380 p_customer_trx_id IN NUMBER,
381 p_ps_id IN NUMBER,
382 p_history_id IN NUMBER,
383 p_prev_cust_old_state IN PREV_CUST_OLD_STATE_TAB
384 ) IS
385 l_list WF_PARAMETER_LIST_T;
386 l_param WF_PARAMETER_T;
387 l_key VARCHAR2(240);
388 l_exist VARCHAR2(1);
389 l_event_name VARCHAR2(50);
390
391 l_prev_ps_op_status NUMBER;
392 l_prev_ps_cl_status NUMBER;
393 CURSOR cm_prev_ctx (p_ctx_id IN NUMBER) IS
394 select prev_trx_ps.status,
395 prev_trx_ps.payment_schedule_id,
396 prev_trx_ps.amount_applied,
397 prev_trx_ps.class,
398 prev_trx_ps.amount_credited,
399 prev_trx_ps.due_date,
400 ctx.previous_customer_trx_id
401 from ar_payment_schedules prev_trx_ps,
402 ra_customer_trx ctx
403 where ctx.customer_trx_id = p_ctx_id
404 and ctx.previous_customer_trx_id = prev_trx_ps.customer_trx_id
405 ;
406
407 l_prev_trx_op_count NUMBER;
408 l_cm_op_trx_count NUMBER;
409 l_cm_cl_trx_count NUMBER;
410 l_prev_trx_app_amt NUMBER;
411 l_past_due_inv_amt NUMBER;
412 l_past_due_inv_count NUMBER;
413 l_prev_trx_type VARCHAR2(10);
414 l_prev_ctx_id NUMBER;
415 BEGIN
416
417 IF p_doc_type = 'INV' THEN
418 l_event_name := 'oracle.apps.ar.transaction.Invoice.incomplete';
419 ELSIF p_doc_type = 'CM' THEN
420 l_event_name := 'oracle.apps.ar.transaction.CreditMemo.incomplete';
421 ELSIF p_doc_type = 'DEP' THEN
422 l_event_name := 'oracle.apps.ar.transaction.Deposit.incomplete';
423 ELSIF p_doc_type = 'DM' THEN
424 l_event_name := 'oracle.apps.ar.transaction.DebitMemo.incomplete';
425 ELSIF p_doc_type = 'GUAR' THEN
426 l_event_name := 'oracle.apps.ar.transaction.Guarantee.incomplete';
430
427 ELSIF p_doc_type = 'BR' THEN
428 l_event_name := 'oracle.apps.ar.transaction.BillsReceivables.incomplete';
429 END IF;
431 --Get the item key
432 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
433 p_customer_trx_id );
434
435 IF (isRefreshProgramRunning) THEN
436
437 insert_events_hist (p_be_name => l_event_name,
438 p_event_key => l_key,
439 p_ps_id => p_ps_id,
440 p_ctx_id => p_customer_trx_id,
441 p_cr_id => null,
442 p_ra_id => null,
443 p_adj_id => null,
444 p_hist_id => p_history_id,
445 p_req_id => null);
446 ELSE
447
448 -- initialization of object variables
449 l_list := WF_PARAMETER_LIST_T();
450
451 -- Add Context values to the list
452 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
453
454 -- add more parameters to the parameters list
455 wf_event.AddParameterToList(p_name => 'CUSTOMER_TRX_ID',
456 p_value => p_customer_trx_id,
457 p_parameterlist => l_list);
458
459 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
460 p_value => p_ps_id,
461 p_parameterlist => l_list);
462
463 wf_event.AddParameterToList(p_name => 'HISTORY_ID',
464 p_value => p_history_id,
465 p_parameterlist => l_list);
466 IF p_doc_type = 'CM' THEN
467 l_cm_cl_trx_count := 0;
468 l_cm_op_trx_count := 0;
469 l_prev_trx_app_amt := 0;
470 l_past_due_inv_amt := 0;
471 l_past_due_inv_count := 0;
472
473 FOR i in cm_prev_ctx(p_customer_trx_id) LOOP
474
475 l_prev_trx_type := i.class;
476 l_prev_ctx_id := i.previous_customer_trx_id;
477
478 --check if the status of this PS has changed.
479 IF p_prev_cust_old_state(i.payment_schedule_id).status = 'OP' AND
480 i.status = 'CL' THEN
481 l_cm_cl_trx_count := l_cm_cl_trx_count + 1;
482 l_prev_trx_app_amt := l_prev_trx_app_amt + nvl(i.amount_applied,0);
483
484 IF i.due_date < sysdate and i.class = 'INV' THEN
485 l_past_due_inv_count := l_past_due_inv_count - 1;
486 END IF;
487 ELSIF p_prev_cust_old_state(i.payment_schedule_id).status = 'CL' AND
488 i.status = 'OP' THEN
489 l_cm_op_trx_count := l_cm_op_trx_count + 1;
490 l_prev_trx_app_amt := l_prev_trx_app_amt - nvl(i.amount_applied,0);
491
492 IF i.due_date < sysdate and i.class = 'INV' THEN
493 l_past_due_inv_count := l_past_due_inv_count + 1;
494 END IF;
495 END IF;
496
497 IF i.due_date < sysdate and i.class = 'INV' THEN
498 l_past_due_inv_amt := l_past_due_inv_amt +
499 ( nvl(p_prev_cust_old_state(i.payment_schedule_id).amount_credited,0) -
500 nvl(i.amount_credited,0));
501 END IF;
502
503 END LOOP;
504
505
506 l_prev_trx_op_count := l_cm_op_trx_count - l_cm_cl_trx_count;
507
508 -- add more parameters to the parameters list
509 wf_event.AddParameterToList(p_name => 'PREV_TRX_OP_COUNT',
510 p_value => l_prev_trx_op_count,
511 p_parameterlist => l_list);
512
513 wf_event.AddParameterToList(p_name => 'PREV_TRX_APP_AMT',
514 p_value => l_prev_trx_app_amt,
515 p_parameterlist => l_list);
516
517 wf_event.AddParameterToList(p_name => 'PAST_DUE_INV_AMT',
518 p_value => l_past_due_inv_amt,
519 p_parameterlist => l_list);
520
521 wf_event.AddParameterToList(p_name => 'PAST_DUE_INV_COUNT',
522 p_value => l_past_due_inv_count,
523 p_parameterlist => l_list);
524
525 wf_event.AddParameterToList(p_name => 'PREV_TRX_TYPE',
526 p_value => l_prev_trx_type,
527 p_parameterlist => l_list);
528
529 wf_event.AddParameterToList(p_name => 'PREV_TRX_ID',
530 p_value => l_prev_ctx_id,
531 p_parameterlist => l_list);
532
533 END IF;
534
535
536 -- Raise Event
537 AR_CMGT_EVENT_PKG.raise_event(
538 p_event_name => l_event_name,
539 p_event_key => l_key,
540 p_parameters => l_list );
541
542 l_list.DELETE;
543 END IF;
544
545 EXCEPTION
546 WHEN others THEN
547 IF PG_DEBUG in ('Y', 'C') THEN
548 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
549 END IF;
550
551 END Raise_Trx_Incomplete_Event;
552
553 --
554 -- This has to be raised per payment_schedule_id modified
555 --
556
557 PROCEDURE Raise_Trx_Modify_Event
558 ( p_payment_schedule_id IN NUMBER,
559 p_doc_type IN VARCHAR2,
560 p_history_id IN NUMBER
561 ) IS
565 l_exist VARCHAR2(1);
562 l_list WF_PARAMETER_LIST_T;
563 l_param WF_PARAMETER_T;
564 l_key VARCHAR2(240);
566 l_event_name VARCHAR2(50);
567 BEGIN
568
569 IF p_doc_type = 'INV' THEN
570 l_event_name := 'oracle.apps.ar.transaction.Invoice.modify';
571 ELSIF p_doc_type = 'CM' THEN
572 l_event_name := 'oracle.apps.ar.transaction.CreditMemo.modify';
573 ELSIF p_doc_type = 'DM' THEN
574 l_event_name := 'oracle.apps.ar.transaction.DebitMemo.modify';
575 ELSIF p_doc_type = 'DEP' THEN
576 l_event_name := 'oracle.apps.ar.transaction.Deposit.modify';
577 ELSIF p_doc_type = 'CB' THEN
578 l_event_name := 'oracle.apps.ar.transaction.ChargeBack.modify';
579 ELSIF p_doc_type = 'GUAR' THEN
580 l_event_name := 'oracle.apps.ar.transaction.Guarantee.modify';
581 END IF;
582
583 --Get the item key
584 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
585 p_payment_schedule_id );
586
587 IF (isRefreshProgramRunning) THEN
588
589 insert_events_hist (p_be_name => l_event_name,
590 p_event_key => l_key,
591 p_ps_id => p_payment_schedule_id,
592 p_ctx_id => null,
593 p_cr_id => null,
594 p_ra_id => null,
595 p_adj_id => null,
596 p_hist_id => p_history_id,
597 p_req_id => null);
598 ELSE
599
600 -- initialization of object variables
601 l_list := WF_PARAMETER_LIST_T();
602
603 -- Add Context values to the list
604 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
605
606 -- add more parameters to the parameters list
607 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
608 p_value => p_payment_schedule_id,
609 p_parameterlist => l_list);
610
611 wf_event.AddParameterToList(p_name => 'HISTORY_ID',
612 p_value => p_history_id,
613 p_parameterlist => l_list);
614
615 -- Raise Event
616 AR_CMGT_EVENT_PKG.raise_event(
617 p_event_name => l_event_name,
618 p_event_key => l_key,
619 p_parameters => l_list );
620
621 l_list.DELETE;
622 END IF;
623
624 EXCEPTION
625 WHEN others THEN
626 IF PG_DEBUG in ('Y', 'C') THEN
627 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
628 END IF;
629
630 END Raise_Trx_Modify_Event;
631
632 PROCEDURE Raise_Rcpt_Creation_Event
633 ( p_payment_schedule_id IN NUMBER
634 ) IS
635 l_list WF_PARAMETER_LIST_T;
636 l_param WF_PARAMETER_T;
637 l_key VARCHAR2(240);
638 l_exist VARCHAR2(1);
639 l_event_name VARCHAR2(50) := 'oracle.apps.ar.receipts.CashReceipt.create';
640
641 l_cust_account_id ar_payment_schedules.customer_id%type;
642 -- bug 3979914.
643 CURSOR getCustomerC IS
644 SELECT nvl(customer_id, -99) cust_account_id
645 FROM ar_payment_schedules
646 WHERE payment_schedule_id = p_payment_schedule_id;
647
648 BEGIN
649 IF PG_DEBUG in ('Y', 'C') THEN
650 arp_util.debug('Raise_Rcpt_Creation_Event (+)');
651 END IF;
652
653 --Get the item key
654 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
655 p_payment_schedule_id);
656 IF (isRefreshProgramRunning) THEN
657
658 insert_events_hist (p_be_name => l_event_name,
659 p_event_key => l_key,
660 p_ps_id => p_payment_schedule_id,
661 p_ctx_id => null,
662 p_cr_id => null,
663 p_ra_id => null,
664 p_adj_id => null,
665 p_hist_id => null,
666 p_req_id => null);
667 ELSE
668
669 -- initialization of object variables
670 l_list := WF_PARAMETER_LIST_T();
671
672 -- Add Context values to the list
673 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
674
675 -- Get Customer Id
676 OPEN getCustomerC;
677 FETCH getCustomerC INTO l_cust_account_id;
678 CLOSE getCustomerC;
679 -- add more parameters to the parameters list
680 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
681 p_value => p_payment_schedule_id,
682 p_parameterlist => l_list);
683
684 wf_event.AddParameterToList(p_name => 'CUST_ACCOUNT_ID',
685 p_value => l_cust_account_id,
686 p_parameterlist => l_list);
687
688 -- Raise Event
689 AR_CMGT_EVENT_PKG.raise_event(
690 p_event_name => l_event_name,
691 p_event_key => l_key,
695 END IF;
692 p_parameters => l_list );
693
694 l_list.DELETE;
696 IF PG_DEBUG in ('Y', 'C') THEN
697 arp_util.debug('Raise_Rcpt_Creation_Event (-)');
698 END IF;
699
700 EXCEPTION
701 WHEN others THEN
702 IF PG_DEBUG in ('Y', 'C') THEN
703 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
704 END IF;
705
706 END Raise_Rcpt_Creation_Event;
707
708 PROCEDURE Raise_Rcpt_Modify_Event
709 ( p_cash_receipt_id IN NUMBER,
710 p_payment_schedule_id IN NUMBER,
711 p_history_id IN NUMBER
712 )
713 IS
714 l_list WF_PARAMETER_LIST_T;
715 l_param WF_PARAMETER_T;
716 l_key VARCHAR2(240);
717 l_exist VARCHAR2(1);
718 l_event_name VARCHAR2(50)
719 := 'oracle.apps.ar.receipts.CashReceipt.modify';
720 BEGIN
721
722 --Get the item key
723 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
724 p_payment_schedule_id );
725
726 IF (isRefreshProgramRunning) THEN
727
728 insert_events_hist (p_be_name => l_event_name,
729 p_event_key => l_key,
730 p_ps_id => p_payment_schedule_id,
731 p_ctx_id => null,
732 p_cr_id => p_cash_receipt_id,
733 p_ra_id => null,
734 p_adj_id => null,
735 p_hist_id => p_history_id,
736 p_req_id => null);
737 ELSE
738
739 -- initialization of object variables
740 l_list := WF_PARAMETER_LIST_T();
741
742 -- Add Context values to the list
743 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
744
745 -- add more parameters to the parameters list
746 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
747 p_value => p_payment_schedule_id,
748 p_parameterlist => l_list);
749
750 wf_event.AddParameterToList(p_name => 'CASH_RECEIPT_ID',
751 p_value => p_cash_receipt_id,
752 p_parameterlist => l_list);
753
754 wf_event.AddParameterToList(p_name => 'HISTORY_ID',
755 p_value => p_history_id,
756 p_parameterlist => l_list);
757
758 -- Raise Event
759 AR_CMGT_EVENT_PKG.raise_event(
760 p_event_name => l_event_name,
761 p_event_key => l_key,
762 p_parameters => l_list );
763
764 l_list.DELETE;
765 END IF;
766
767 EXCEPTION
768 WHEN others THEN
769 IF PG_DEBUG in ('Y', 'C') THEN
770 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
771 END IF;
772
773 END Raise_Rcpt_Modify_Event;
774
775 PROCEDURE Raise_Rcpt_Reverse_Event
776 (p_cash_receipt_id IN NUMBER,
777 p_payment_schedule_id IN NUMBER,
778 p_history_id IN NUMBER
779 )
780 IS
781 l_list WF_PARAMETER_LIST_T;
782 l_param WF_PARAMETER_T;
783 l_key VARCHAR2(240);
784 l_exist VARCHAR2(1);
785 l_event_name VARCHAR2(50)
786 := 'oracle.apps.ar.receipts.CashReceipt.reverse';
787 BEGIN
788
789 --Get the item key
790 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
791 p_payment_schedule_id );
792
793 IF (isRefreshProgramRunning) THEN
794
795 insert_events_hist (p_be_name => l_event_name,
796 p_event_key => l_key,
797 p_ps_id => p_payment_schedule_id,
798 p_ctx_id => null,
799 p_cr_id => p_cash_receipt_id,
800 p_ra_id => null,
801 p_adj_id => null,
802 p_hist_id => p_history_id,
803 p_req_id => null);
804 ELSE
805
806 -- initialization of object variables
807 l_list := WF_PARAMETER_LIST_T();
808
809 -- Add Context values to the list
810 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
811
812 -- add more parameters to the parameters list
813 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
814 p_value => p_payment_schedule_id,
815 p_parameterlist => l_list);
816
817 wf_event.AddParameterToList(p_name => 'CASH_RECEIPT_ID',
818 p_value => p_cash_receipt_id,
819 p_parameterlist => l_list);
820
821 wf_event.AddParameterToList(p_name => 'HISTORY_ID',
822 p_value => p_history_id,
823 p_parameterlist => l_list);
824
825 -- Raise Event
826 AR_CMGT_EVENT_PKG.raise_event(
830
827 p_event_name => l_event_name,
828 p_event_key => l_key,
829 p_parameters => l_list );
831 l_list.DELETE;
832 END IF;
833
834 EXCEPTION
835 WHEN others THEN
836 IF PG_DEBUG in ('Y', 'C') THEN
837 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
838 END IF;
839
840 END Raise_Rcpt_Reverse_Event;
841
842 PROCEDURE Raise_Rcpt_DMReverse_Event
843 (p_cash_receipt_id IN NUMBER,
844 p_payment_schedule_id IN NUMBER,
845 p_history_id IN NUMBER
846 )
847 IS
848 l_list WF_PARAMETER_LIST_T;
849 l_param WF_PARAMETER_T;
850 l_key VARCHAR2(240);
851 l_exist VARCHAR2(1);
852 l_event_name VARCHAR2(100)
853 := 'oracle.apps.ar.receipts.CashReceipt.DebitMemoReverse';
854 BEGIN
855
856 --Get the item key
857 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
858 p_payment_schedule_id );
859
860 IF (isRefreshProgramRunning) THEN
861
862 insert_events_hist (p_be_name => l_event_name,
863 p_event_key => l_key,
864 p_ps_id => p_payment_schedule_id,
865 p_ctx_id => null,
866 p_cr_id => p_cash_receipt_id,
867 p_ra_id => null,
868 p_adj_id => null,
869 p_hist_id => p_history_id,
870 p_req_id => null);
871 ELSE
872
873 -- initialization of object variables
874 l_list := WF_PARAMETER_LIST_T();
875
876 -- Add Context values to the list
877 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
878
879 -- add more parameters to the parameters list
880 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
881 p_value => p_payment_schedule_id,
882 p_parameterlist => l_list);
883
884 wf_event.AddParameterToList(p_name => 'CASH_RECEIPT_ID',
885 p_value => p_cash_receipt_id,
886 p_parameterlist => l_list);
887
888 wf_event.AddParameterToList(p_name => 'HISTORY_ID',
889 p_value => p_history_id,
890 p_parameterlist => l_list);
891
892 -- Raise Event
893 AR_CMGT_EVENT_PKG.raise_event(
894 p_event_name => l_event_name,
895 p_event_key => l_key,
896 p_parameters => l_list );
897
898 l_list.DELETE;
899 END IF;
900
901 EXCEPTION
902 WHEN others THEN
903 IF PG_DEBUG in ('Y', 'C') THEN
904 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
905 END IF;
906
907 END Raise_Rcpt_DMReverse_Event;
908
909 PROCEDURE Raise_Rcpt_Confirm_Event
910 ( p_rec_appln_id IN NUMBER,
911 p_cash_receipt_id IN NUMBER)
912 IS
913
914 BEGIN
915
916 null;
917 END Raise_Rcpt_Confirm_Event;
918
919 PROCEDURE Raise_Rcpt_UnConfirm_Event
920 ( p_rec_appln_id IN NUMBER,
921 p_cash_receipt_id IN NUMBER)
922 IS
923
924 BEGIN
925
926 null;
927 END Raise_Rcpt_UnConfirm_Event;
928
929 PROCEDURE Raise_CR_Apply_Event
930 (p_receivable_application_id IN NUMBER --pass in the rec_app_id of the APP rec
931 ) IS
932 l_list WF_PARAMETER_LIST_T;
933 l_param WF_PARAMETER_T;
934 l_key VARCHAR2(240);
935 l_exist VARCHAR2(1);
936 l_event_name VARCHAR2(50)
937 := 'oracle.apps.ar.applications.CashApp.apply';
938 CURSOR get_ps_status(ra_id IN NUMBER) IS
939 select inv_ps.status trx_ps_status,
940 rcpt_ps.status rcpt_ps_status,
941 to_char(inv_ps.due_date , 'J') due_date,
942 inv_ps.amount_applied
943 from ar_receivable_applications ra,
944 ar_payment_schedules inv_ps,
945 ar_payment_schedules rcpt_ps
946 where ra.receivable_application_id = ra_id
947 and ra.applied_payment_schedule_id = inv_ps.payment_schedule_id
948 and ra.payment_schedule_id = rcpt_ps.payment_schedule_id;
949 l_trx_ps_status VARCHAR2(10);
950 l_rcpt_ps_status VARCHAR2(10);
951 l_due_date VARCHAR2(20);
952 l_trx_amt_applied NUMBER;
953 BEGIN
954
955 --Get the item key
956 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
957 p_receivable_application_id );
958
959 IF (isRefreshProgramRunning) THEN
960
961 insert_events_hist (p_be_name => l_event_name,
962 p_event_key => l_key,
963 p_ps_id => null,
964 p_ctx_id => null,
965 p_cr_id => null,
966 p_ra_id => p_receivable_application_id,
967 p_adj_id => null,
971 OPEN get_ps_status(p_receivable_application_id);
968 p_hist_id => null,
969 p_req_id => null);
970 ELSE
972 FETCH get_ps_status INTO l_trx_ps_status, l_rcpt_ps_status, l_due_date,
973 l_trx_amt_applied;
974 CLOSE get_ps_status;
975
976 -- initialization of object variables
977 l_list := WF_PARAMETER_LIST_T();
978
979 -- Add Context values to the list
980 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
981
982 -- add more parameters to the parameters list
983 wf_event.AddParameterToList(p_name => 'RECEIVABLE_APPLICATION_ID',
984 p_value => p_receivable_application_id,
985 p_parameterlist => l_list);
986 wf_event.AddParameterToList(p_name => 'TRX_PS_STATUS',
987 p_value => l_trx_ps_status,
988 p_parameterlist => l_list);
989 wf_event.AddParameterToList(p_name => 'RCPT_PS_STATUS',
990 p_value => l_rcpt_ps_status,
991 p_parameterlist => l_list);
992 wf_event.AddParameterToList(p_name => 'TRX_DUE_DATE',
993 p_value => l_due_date,
994 p_parameterlist => l_list);
995 wf_event.AddParameterToList(p_name => 'TRX_APP_AMT',
996 p_value => l_trx_amt_applied,
997 p_parameterlist => l_list);
998
999
1000 -- Raise Event
1001 AR_CMGT_EVENT_PKG.raise_event(
1002 p_event_name => l_event_name,
1003 p_event_key => l_key,
1004 p_parameters => l_list );
1005
1006 l_list.DELETE;
1007 END IF;
1008
1009 EXCEPTION
1010 WHEN others THEN
1011 IF PG_DEBUG in ('Y', 'C') THEN
1012 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1013 END IF;
1014
1015 END Raise_CR_Apply_Event;
1016
1017 PROCEDURE Raise_CR_UnApply_Event
1018 (p_receivable_application_id IN NUMBER --pass in the rec_app_id of the APP rec
1019 ) IS
1020 l_list WF_PARAMETER_LIST_T;
1021 l_param WF_PARAMETER_T;
1022 l_key VARCHAR2(240);
1023 l_exist VARCHAR2(1);
1024 l_event_name VARCHAR2(50)
1025 := 'oracle.apps.ar.applications.CashApp.unapply';
1026 CURSOR get_ps_status(ra_id IN NUMBER) IS
1027 select inv_ps.status trx_ps_status,
1028 rcpt_ps.status rcpt_ps_status,
1029 to_char(inv_ps.due_date , 'J') due_date,
1030 inv_ps.amount_applied,
1031 ra.amount_applied,
1032 ra.amount_applied_from,
1033 inv_ps.amount_due_remaining,
1034 ra.earned_discount_taken,
1035 ra.unearned_discount_taken
1036 from ar_receivable_applications ra,
1037 ar_payment_schedules inv_ps,
1038 ar_payment_schedules rcpt_ps
1039 where ra.receivable_application_id = ra_id
1040 and ra.applied_payment_schedule_id = inv_ps.payment_schedule_id
1041 and ra.payment_schedule_id = rcpt_ps.payment_schedule_id;
1042 l_old_trx_ps_status VARCHAR2(10);
1043 l_new_trx_ps_status VARCHAR2(10);
1044 l_old_rcpt_ps_status VARCHAR2(10);
1045 l_new_rcpt_ps_status VARCHAR2(10);
1046 l_due_date VARCHAR2(20);
1047 l_trx_amt_applied NUMBER;
1048 l_trx_adr NUMBER;
1049 l_rcpt_adr NUMBER;
1050 l_amount_applied NUMBER;
1051 l_amount_applied_from NUMBER;
1052 l_earned_discount_taken NUMBER;
1053 l_unearned_discount_taken NUMBER;
1054 l_tot_amt_applied NUMBER;
1055 BEGIN
1056 IF PG_DEBUG in ('Y', 'C') THEN
1057 arp_standard.debug('Raise_CR_UnApply_Event(+)');
1058 END IF;
1059 --Get the item key
1060 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
1061 p_receivable_application_id );
1062
1063 IF (isRefreshProgramRunning) THEN
1064
1065 insert_events_hist (p_be_name => l_event_name,
1066 p_event_key => l_key,
1067 p_ps_id => null,
1068 p_ctx_id => null,
1069 p_cr_id => null,
1070 p_ra_id => p_receivable_application_id,
1071 p_adj_id => null,
1072 p_hist_id => null,
1073 p_req_id => null);
1074 ELSE
1075 OPEN get_ps_status(p_receivable_application_id);
1076 FETCH get_ps_status INTO l_new_trx_ps_status, l_new_rcpt_ps_status, l_due_date,
1077 l_tot_amt_applied, l_amount_applied, l_amount_applied_from,l_trx_adr,
1078 l_earned_discount_taken, l_unearned_discount_taken;
1079
1080
1081 /* If the amount applied and the discounts on the application
1082 that is being reversed adds up to the new amount_due_remaining
1083 on the trx payment schedule that means that the trx PS was
1084 closed prior to the application reversal.
1085
1086 The idea here is to capure the trx PS status prior to the unapply
1087 and pass that to the business event as parameter.
1088 */
1092 + nvl(l_earned_discount_taken,0)
1089 IF l_new_trx_ps_status = 'OP' THEN
1090 IF ( nvl(l_trx_adr,0) +
1091 ( nvl(l_amount_applied,0)
1093 + nvl(l_unearned_discount_taken,0)
1094 )
1095 ) = 0 THEN
1096 l_old_trx_ps_status := 'CL';
1097
1098 ELSE
1099 l_old_trx_ps_status := 'OP';
1100 END IF;
1101 ELSIF l_new_trx_ps_status = 'CL' THEN
1102 l_old_trx_ps_status := 'OP';
1103 END IF;
1104
1105 IF l_new_rcpt_ps_status = 'OP'
1106 THEN
1107 IF( nvl(l_rcpt_adr,0) +
1108 nvl(l_amount_applied_from,l_amount_applied)
1109 ) = 0 THEN
1110 l_old_rcpt_ps_status := 'CL';
1111 ELSE
1112 l_old_rcpt_ps_status := 'OP';
1113 END IF;
1114 ELSIF l_new_rcpt_ps_status = 'CL' THEN
1115 l_old_rcpt_ps_status := 'OP';
1116 END IF;
1117
1118 CLOSE get_ps_status;
1119
1120 -- initialization of object variables
1121 l_list := WF_PARAMETER_LIST_T();
1122
1123 -- Add Context values to the list
1124 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1125
1126 -- add more parameters to the parameters list
1127 wf_event.AddParameterToList(p_name => 'RECEIVABLE_APPLICATION_ID',
1128 p_value => p_receivable_application_id,
1129 p_parameterlist => l_list);
1130 wf_event.AddParameterToList(p_name => 'OLD_TRX_PS_STATUS',
1131 p_value => l_old_trx_ps_status,
1132 p_parameterlist => l_list);
1133 wf_event.AddParameterToList(p_name => 'NEW_TRX_PS_STATUS',
1134 p_value => l_new_trx_ps_status,
1135 p_parameterlist => l_list);
1136 wf_event.AddParameterToList(p_name => 'OLD_RCPT_PS_STATUS',
1137 p_value => l_old_rcpt_ps_status,
1138 p_parameterlist => l_list);
1139 wf_event.AddParameterToList(p_name => 'NEW_RCPT_PS_STATUS',
1140 p_value => l_new_rcpt_ps_status,
1141 p_parameterlist => l_list);
1142 wf_event.AddParameterToList(p_name => 'TRX_DUE_DATE',
1143 p_value => l_due_date,
1144 p_parameterlist => l_list);
1145 wf_event.AddParameterToList(p_name => 'TRX_APP_AMT',
1146 p_value =>l_tot_amt_applied,
1147 p_parameterlist => l_list);
1148 wf_event.AddParameterToList(p_name => 'AMT_DUE_REMAINING',
1149 p_value =>l_trx_adr,
1150 p_parameterlist => l_list);
1151 wf_event.AddParameterToList(p_name => 'OLD_AMT_DUE_REMAINING',
1152 p_value => ( nvl(l_trx_adr,0) +
1153 ( nvl(l_amount_applied,0)
1154 + nvl(l_earned_discount_taken,0)
1155 + nvl(l_unearned_discount_taken,0)
1156 )),
1157 p_parameterlist => l_list);
1158
1159
1160 -- Raise Event
1161 AR_CMGT_EVENT_PKG.raise_event(
1162 p_event_name => l_event_name,
1163 p_event_key => l_key,
1164 p_parameters => l_list );
1165
1166 l_list.DELETE;
1167 END IF;
1168
1169 IF PG_DEBUG in ('Y', 'C') THEN
1170 arp_standard.debug('Raise_CR_UnApply_Event(-)');
1171 END IF;
1172
1173 EXCEPTION
1174 WHEN others THEN
1175 IF PG_DEBUG in ('Y', 'C') THEN
1176 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1177 END IF;
1178
1179 END Raise_CR_UnApply_Event;
1180
1181 PROCEDURE Raise_CM_Apply_Event
1182 (p_receivable_application_id IN NUMBER ,--pass in the rec_app_id of the APP rec
1183 p_app_ps_status IN VARCHAR2 DEFAULT NULL
1184 ) IS
1185 l_list WF_PARAMETER_LIST_T;
1186 l_param WF_PARAMETER_T;
1187 l_key VARCHAR2(240);
1188 l_exist VARCHAR2(1);
1189 l_event_name VARCHAR2(50)
1190 := 'oracle.apps.ar.applications.CreditMemoApp.apply';
1191 CURSOR get_ps_status(ra_id IN NUMBER) IS
1192 select inv_ps.status trx_ps_status,
1193 cm_ps.status cm_ps_status,
1194 to_char(inv_ps.due_date , 'J') due_date,
1195 inv_ps.amount_applied
1196 from ar_receivable_applications ra,
1197 ar_payment_schedules inv_ps,
1198 ar_payment_schedules cm_ps
1199 where ra.receivable_application_id = ra_id
1200 and ra.applied_payment_schedule_id = inv_ps.payment_schedule_id
1201 and ra.payment_schedule_id = cm_ps.payment_schedule_id;
1202
1203 l_trx_ps_status VARCHAR2(10);
1204 l_cm_ps_status VARCHAR2(10);
1205 l_due_date VARCHAR2(20);
1206 l_trx_amt_applied NUMBER;
1207
1208 BEGIN
1209
1210 --Get the item key
1211 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1212 p_receivable_application_id);
1213
1214 IF (isRefreshProgramRunning) THEN
1215
1216 insert_events_hist (p_be_name => l_event_name,
1220 p_cr_id => null,
1217 p_event_key => l_key,
1218 p_ps_id => null,
1219 p_ctx_id => null,
1221 p_ra_id => p_receivable_application_id,
1222 p_adj_id => null,
1223 p_hist_id => null,
1224 p_req_id => null);
1225 ELSE
1226
1227 OPEN get_ps_status(p_receivable_application_id);
1228 FETCH get_ps_status INTO l_trx_ps_status, l_cm_ps_status, l_due_date,
1229 l_trx_amt_applied;
1230 CLOSE get_ps_status;
1231
1232 -- initialization of object variables
1233 l_list := WF_PARAMETER_LIST_T();
1234
1235 -- Add Context values to the list
1236 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1237
1238 -- add more parameters to the parameters list
1239 wf_event.AddParameterToList(p_name => 'RECEIVABLE_APPLICATION_ID',
1240 p_value => p_receivable_application_id,
1241 p_parameterlist => l_list);
1242 wf_event.AddParameterToList(p_name => 'TRX_PS_STATUS',
1243 p_value => l_trx_ps_status,
1244 p_parameterlist => l_list);
1245 wf_event.AddParameterToList(p_name => 'CM_PS_STATUS',
1246 p_value => l_cm_ps_status,
1247 p_parameterlist => l_list);
1248 wf_event.AddParameterToList(p_name => 'TRX_DUE_DATE',
1249 p_value => l_due_date,
1250 p_parameterlist => l_list);
1251 wf_event.AddParameterToList(p_name => 'TRX_APP_AMT',
1252 p_value => l_trx_amt_applied,
1253 p_parameterlist => l_list);
1254
1255 -- Raise Event
1256 AR_CMGT_EVENT_PKG.raise_event(
1257 p_event_name => l_event_name,
1258 p_event_key => l_key,
1259 p_parameters => l_list );
1260
1261 l_list.DELETE;
1262 END IF;
1263
1264 EXCEPTION
1265 WHEN others THEN
1266 IF PG_DEBUG in ('Y', 'C') THEN
1267 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1268 END IF;
1269
1270 END Raise_CM_Apply_Event;
1271
1272 PROCEDURE Raise_CM_UnApply_Event
1273 (p_receivable_application_id IN NUMBER --pass in the rec_app_id of the APP rec
1274 ) IS
1275 l_list WF_PARAMETER_LIST_T;
1276 l_param WF_PARAMETER_T;
1277 l_key VARCHAR2(240);
1278 l_exist VARCHAR2(1);
1279 l_event_name VARCHAR2(50)
1280 := 'oracle.apps.ar.applications.CreditMemoApp.unapply';
1281 CURSOR get_ps_status(ra_id IN NUMBER) IS
1282 select inv_ps.status trx_ps_status,
1283 cm_ps.status cm_ps_status,
1284 to_char(inv_ps.due_date , 'J') due_date,
1285 inv_ps.amount_applied
1286 from ar_receivable_applications ra,
1287 ar_payment_schedules inv_ps,
1288 ar_payment_schedules cm_ps
1289 where ra.receivable_application_id = ra_id
1290 and ra.applied_payment_schedule_id = inv_ps.payment_schedule_id
1291 and ra.payment_schedule_id = cm_ps.payment_schedule_id;
1292
1293 l_trx_ps_status VARCHAR2(10);
1294 l_cm_ps_status VARCHAR2(10);
1295 l_due_date VARCHAR2(20);
1296 l_trx_amt_applied NUMBER;
1297
1298 BEGIN
1299
1300 --Get the item key
1301 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1302 p_receivable_application_id);
1303
1304 IF (isRefreshProgramRunning) THEN
1305
1306 insert_events_hist (p_be_name => l_event_name,
1307 p_event_key => l_key,
1308 p_ps_id => null,
1309 p_ctx_id => null,
1310 p_cr_id => null,
1311 p_ra_id => p_receivable_application_id,
1312 p_adj_id => null,
1313 p_hist_id => null,
1314 p_req_id => null);
1315 ELSE
1316
1317 OPEN get_ps_status(p_receivable_application_id);
1318 FETCH get_ps_status INTO l_trx_ps_status, l_cm_ps_status, l_due_date,
1319 l_trx_amt_applied;
1320 CLOSE get_ps_status;
1321
1322 -- initialization of object variables
1323 l_list := WF_PARAMETER_LIST_T();
1324
1325 -- Add Context values to the list
1326 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1327
1328 -- add more parameters to the parameters list
1329 wf_event.AddParameterToList(p_name => 'RECEIVABLE_APPLICATION_ID',
1330 p_value => p_receivable_application_id,
1331 p_parameterlist => l_list);
1332 wf_event.AddParameterToList(p_name => 'TRX_PS_STATUS',
1333 p_value => l_trx_ps_status,
1334 p_parameterlist => l_list);
1335 wf_event.AddParameterToList(p_name => 'CM_PS_STATUS',
1336 p_value => l_cm_ps_status,
1337 p_parameterlist => l_list);
1341 wf_event.AddParameterToList(p_name => 'TRX_APP_AMT',
1338 wf_event.AddParameterToList(p_name => 'TRX_DUE_DATE',
1339 p_value => l_due_date,
1340 p_parameterlist => l_list);
1342 p_value => l_trx_amt_applied,
1343 p_parameterlist => l_list);
1344
1345 -- Raise Event
1346 AR_CMGT_EVENT_PKG.raise_event(
1347 p_event_name => l_event_name,
1348 p_event_key => l_key,
1349 p_parameters => l_list );
1350
1351 l_list.DELETE;
1352 END IF;
1353
1354 EXCEPTION
1355 WHEN others THEN
1356 IF PG_DEBUG in ('Y', 'C') THEN
1357 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1358 END IF;
1359
1360 END Raise_CM_UnApply_Event;
1361
1362 PROCEDURE Raise_Adj_Create_Event
1363 (p_adjustment_id IN NUMBER,
1364 p_app_ps_status IN VARCHAR2 DEFAULT NULL,
1365 p_adj_status IN VARCHAR2 DEFAULT NULL)
1366 IS
1367 l_list WF_PARAMETER_LIST_T;
1368 l_param WF_PARAMETER_T;
1369 l_key VARCHAR2(240);
1370 l_exist VARCHAR2(1);
1371 l_event_name VARCHAR2(50)
1372 := 'oracle.apps.ar.adjustments.Adjustment.create';
1373 BEGIN
1374
1375 --Get the item key
1376 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1377 p_adjustment_id);
1378
1379 IF (isRefreshProgramRunning) THEN
1380
1381 insert_events_hist (p_be_name => l_event_name,
1382 p_event_key => l_key,
1383 p_ps_id => null,
1384 p_ctx_id => null,
1385 p_cr_id => null,
1386 p_ra_id => null,
1387 p_adj_id => p_adjustment_id,
1388 p_hist_id => null,
1389 p_req_id => null);
1390 ELSE
1391
1392 -- initialization of object variables
1393 l_list := WF_PARAMETER_LIST_T();
1394
1395 -- Add Context values to the list
1396 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1397
1398 -- add more parameters to the parameters list
1399 wf_event.AddParameterToList(p_name => 'ADJUSTMENT_ID',
1400 p_value => p_adjustment_id,
1401 p_parameterlist => l_list);
1402 wf_event.AddParameterToList(p_name => 'APPLIED_PS_STATUS',
1403 p_value => p_app_ps_status,
1404 p_parameterlist => l_list);
1405
1406 wf_event.AddParameterToList(p_name => 'ADJ_STATUS',
1407 p_value => p_adj_status,
1408 p_parameterlist => l_list);
1409
1410
1411 -- Raise Event
1412 AR_CMGT_EVENT_PKG.raise_event(
1413 p_event_name => l_event_name,
1414 p_event_key => l_key,
1415 p_parameters => l_list );
1416
1417 l_list.DELETE;
1418 END IF;
1419
1420 EXCEPTION
1421 WHEN others THEN
1422 IF PG_DEBUG in ('Y', 'C') THEN
1423 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1424 END IF;
1425
1426 END Raise_Adj_Create_Event;
1427
1428 PROCEDURE Raise_Adj_Approve_Event
1429 (p_adjustment_id IN NUMBER,
1430 p_approval_actn_hist_id IN NUMBER,
1431 p_app_ps_status IN VARCHAR2 DEFAULT NULL)
1432 IS
1433 l_list WF_PARAMETER_LIST_T;
1434 l_param WF_PARAMETER_T;
1435 l_key VARCHAR2(240);
1436 l_exist VARCHAR2(1);
1437 l_event_name VARCHAR2(50)
1438 := 'oracle.apps.ar.adjustments.Adjustment.approve';
1439 BEGIN
1440
1441 --Get the item key
1442 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1443 p_adjustment_id);
1444 IF (isRefreshProgramRunning) THEN
1445
1446 insert_events_hist (p_be_name => l_event_name,
1447 p_event_key => l_key,
1448 p_ps_id => null,
1449 p_ctx_id => null,
1450 p_cr_id => null,
1451 p_ra_id => null,
1452 p_adj_id => p_adjustment_id,
1453 p_hist_id => p_approval_actn_hist_id,
1454 p_req_id => null);
1455 ELSE
1456
1457 -- initialization of object variables
1458 l_list := WF_PARAMETER_LIST_T();
1459
1460 -- Add Context values to the list
1461 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1462
1463 -- add more parameters to the parameters list
1464 wf_event.AddParameterToList(p_name => 'ADJUSTMENT_ID',
1465 p_value => p_adjustment_id,
1466 p_parameterlist => l_list);
1467
1468 wf_event.AddParameterToList(p_name => 'APPROVAL_ACTN_HIST_ID',
1472 p_value => p_app_ps_status,
1469 p_value => p_approval_actn_hist_id,
1470 p_parameterlist => l_list);
1471 wf_event.AddParameterToList(p_name => 'APPLIED_PS_STATUS',
1473 p_parameterlist => l_list);
1474
1475 -- Raise Event
1476 AR_CMGT_EVENT_PKG.raise_event(
1477 p_event_name => l_event_name,
1478 p_event_key => l_key,
1479 p_parameters => l_list );
1480
1481 l_list.DELETE;
1482 END IF;
1483
1484 EXCEPTION
1485 WHEN others THEN
1486 IF PG_DEBUG in ('Y', 'C') THEN
1487 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1488 END IF;
1489
1490 END Raise_Adj_Approve_Event;
1491
1492 PROCEDURE Raise_AutoInv_Run_Event
1493 ( p_request_id IN NUMBER)
1494 IS
1495 l_list WF_PARAMETER_LIST_T;
1496 l_param WF_PARAMETER_T;
1497 l_key VARCHAR2(240);
1498 l_exist VARCHAR2(1);
1499 l_event_name VARCHAR2(50) := 'oracle.apps.ar.batch.AutoInvoice.run';
1500 BEGIN
1501
1502 --Get the item key
1503 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1504 p_request_id);
1505 IF (isRefreshProgramRunning) THEN
1506
1507 insert_events_hist (p_be_name => l_event_name,
1508 p_event_key => l_key,
1509 p_ps_id => null,
1510 p_ctx_id => null,
1511 p_cr_id => null,
1512 p_ra_id => null,
1513 p_adj_id => null,
1514 p_hist_id => null,
1515 p_req_id => p_request_id);
1516 ELSE
1517
1518 -- initialization of object variables
1519 l_list := WF_PARAMETER_LIST_T();
1520
1521 -- Add Context values to the list
1522 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1523
1524 -- add more parameters to the parameters list
1525 wf_event.AddParameterToList(p_name => 'REQUEST_ID',
1526 p_value => p_request_id,
1527 p_parameterlist => l_list);
1528
1529 -- Raise Event
1530 AR_CMGT_EVENT_PKG.raise_event(
1531 p_event_name => l_event_name,
1532 p_event_key => l_key,
1533 p_parameters => l_list );
1534 l_list.DELETE;
1535 END IF;
1536
1537 EXCEPTION
1538 WHEN others THEN
1539 IF PG_DEBUG in ('Y', 'C') THEN
1540 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1541 END IF;
1542
1543 END Raise_AutoInv_Run_Event;
1544
1545 PROCEDURE Raise_AutoRec_Run_Event
1546 ( p_request_id IN NUMBER,
1547 p_req_confirmation IN VARCHAR2)
1548 IS
1549 l_list WF_PARAMETER_LIST_T;
1550 l_param WF_PARAMETER_T;
1551 l_key VARCHAR2(240);
1552 l_exist VARCHAR2(1);
1553 l_event_name VARCHAR2(50) := 'oracle.apps.ar.batch.AutoReceipts.run';
1554
1555 BEGIN
1556
1557 --We raise the bisiness event only in case the confirmation is
1558 --not required for the batch. In this case the PS of the receipt would
1559 --get created and the PS of the invoice would get updated upon the
1560 --Auto Receipt Run with approval.
1561 IF p_req_confirmation = 'Y' THEN
1562 --Get the item key
1563 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name,
1564 p_request_id );
1565
1566 IF (isRefreshProgramRunning) THEN
1567
1568 insert_events_hist (p_be_name => l_event_name,
1569 p_event_key=> l_key,
1570 p_ps_id => null,
1571 p_ctx_id => null,
1572 p_cr_id => null,
1573 p_ra_id => null,
1574 p_adj_id => null,
1575 p_hist_id => null,
1576 p_req_id => p_request_id);
1577 ELSE
1578
1579 -- initialization of object variables
1580 l_list := WF_PARAMETER_LIST_T();
1581
1582 -- Add Context values to the list
1583 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1584
1585 -- add more parameters to the parameters list
1586 wf_event.AddParameterToList(p_name => 'REQUEST_ID',
1587 p_value => p_request_id,
1588 p_parameterlist => l_list);
1589
1590 -- Raise Event
1591 AR_CMGT_EVENT_PKG.raise_event(
1592 p_event_name => l_event_name,
1593 p_event_key => l_key,
1594 p_parameters => l_list );
1595 l_list.DELETE;
1596 END IF;
1597
1598 END IF;
1599
1600 EXCEPTION
1601 WHEN others THEN
1602 IF PG_DEBUG in ('Y', 'C') THEN
1603 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1604 END IF;
1605
1606 END Raise_AutoRec_Run_Event;
1607
1608 PROCEDURE Raise_PostBatch_Run_Event
1609 ( p_request_id IN NUMBER)
1610 IS
1611 l_list WF_PARAMETER_LIST_T;
1615 l_event_name VARCHAR2(50) := 'oracle.apps.ar.batch.QuickCash.PostBatch';
1612 l_param WF_PARAMETER_T;
1613 l_key VARCHAR2(240);
1614 l_exist VARCHAR2(1);
1616 BEGIN
1617
1618 IF p_request_id is not null THEN
1619 --Get the item key
1620 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1621 p_request_id);
1622
1623 IF (isRefreshProgramRunning) THEN
1624
1625 insert_events_hist (p_be_name => l_event_name,
1626 p_event_key=> l_key,
1627 p_ps_id => null,
1628 p_ctx_id => null,
1629 p_cr_id => null,
1630 p_ra_id => null,
1631 p_adj_id => null,
1632 p_hist_id => null,
1633 p_req_id => p_request_id);
1634 ELSE
1635
1636 -- initialization of object variables
1637 l_list := WF_PARAMETER_LIST_T();
1638
1639 -- Add Context values to the list
1640 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1641
1642 -- add more parameters to the parameters list
1643 wf_event.AddParameterToList(p_name => 'REQUEST_ID',
1644 p_value => p_request_id,
1645 p_parameterlist => l_list);
1646
1647 -- Raise Event
1648 AR_CMGT_EVENT_PKG.raise_event(
1649 p_event_name => l_event_name,
1650 p_event_key => l_key,
1651 p_parameters => l_list );
1652 l_list.DELETE;
1653 END IF;
1654 END IF;
1655
1656 EXCEPTION
1657 WHEN others THEN
1658 IF PG_DEBUG in ('Y', 'C') THEN
1659 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1660 END IF;
1661
1662 END Raise_PostBatch_Run_Event;
1663
1664 PROCEDURE Raise_AutoAdj_Run_Event
1665 ( p_request_id IN NUMBER) IS
1666 l_list WF_PARAMETER_LIST_T;
1667 l_param WF_PARAMETER_T;
1668 l_key VARCHAR2(240);
1669 l_exist VARCHAR2(1);
1670 l_event_name VARCHAR2(50) := 'oracle.apps.ar.batch.AutoAdjustments.run';
1671 BEGIN
1672
1673 --
1674 -- We do not need to create a history record in AR_TRX_SUMMARY_HIST for the
1675 -- adjustments done against the transactions because of fllowing reasons:
1676 -- 1) The adjustment history is stored in AR_ADJUSTMENTS table
1677 -- 2) AR Subscription of no other core event would look at the adjustment.
1678
1679 IF p_request_id is not null THEN
1680 --Get the item key
1681 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1682 p_request_id);
1683
1684 IF (isRefreshProgramRunning) THEN
1685
1686 insert_events_hist (p_be_name => l_event_name,
1687 p_event_key=> l_key,
1688 p_ps_id => null,
1689 p_ctx_id => null,
1690 p_cr_id => null,
1691 p_ra_id => null,
1692 p_adj_id => null,
1693 p_hist_id => null,
1694 p_req_id => p_request_id);
1695 ELSE
1696
1697 -- initialization of object variables
1698 l_list := WF_PARAMETER_LIST_T();
1699
1700 -- Add Context values to the list
1701 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1702
1703 -- add more parameters to the parameters list
1704 wf_event.AddParameterToList(p_name => 'REQUEST_ID',
1705 p_value => p_request_id,
1706 p_parameterlist => l_list);
1707
1708 -- Raise Event
1709 AR_CMGT_EVENT_PKG.raise_event(
1710 p_event_name => l_event_name,
1711 p_event_key => l_key,
1712 p_parameters => l_list );
1713 l_list.DELETE;
1714
1715 END IF;
1716 END IF;
1717
1718 EXCEPTION
1719 WHEN others THEN
1720 IF PG_DEBUG in ('Y', 'C') THEN
1721 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1722 END IF;
1723
1724 END Raise_AutoAdj_Run_Event;
1725
1726 PROCEDURE Raise_CopyInv_Run_Event
1727 ( p_request_id IN NUMBER) IS
1728 l_list WF_PARAMETER_LIST_T;
1729 l_param WF_PARAMETER_T;
1730 l_key VARCHAR2(240);
1731 l_exist VARCHAR2(1);
1732 l_event_name VARCHAR2(50) := 'oracle.apps.ar.batch.CopyInvoices.run';
1733 BEGIN
1734
1735 IF p_request_id is not null THEN
1736 --Get the item key
1737 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1738 p_request_id);
1739 IF (isRefreshProgramRunning) THEN
1740
1741 insert_events_hist (p_be_name => l_event_name,
1742 p_event_key=> l_key,
1743 p_ps_id => null,
1744 p_ctx_id => null,
1745 p_cr_id => null,
1746 p_ra_id => null,
1747 p_adj_id => null,
1748 p_hist_id => null,
1752 -- initialization of object variables
1749 p_req_id => p_request_id);
1750 ELSE
1751
1753 l_list := WF_PARAMETER_LIST_T();
1754
1755 -- Add Context values to the list
1756 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1757
1758 -- add more parameters to the parameters list
1759 wf_event.AddParameterToList(p_name => 'REQUEST_ID',
1760 p_value => p_request_id,
1761 p_parameterlist => l_list);
1762
1763 -- Raise Event
1764 AR_CMGT_EVENT_PKG.raise_event(
1765 p_event_name => l_event_name,
1766 p_event_key => l_key,
1767 p_parameters => l_list );
1768 l_list.DELETE;
1769 END IF;
1770
1771 END IF;
1772
1773 EXCEPTION
1774 WHEN others THEN
1775 IF PG_DEBUG in ('Y', 'C') THEN
1776 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1777 END IF;
1778
1779 END Raise_CopyInv_Run_Event;
1780
1781 PROCEDURE Raise_Rcpt_Deletion_Event
1782 ( p_payment_schedule_id IN NUMBER,
1783 p_receipt_number IN ar_cash_receipts.receipt_number%type,
1784 p_receipt_date IN ar_cash_receipts.receipt_date%type
1785 ) IS
1786 l_list WF_PARAMETER_LIST_T;
1787 l_param WF_PARAMETER_T;
1788 l_key VARCHAR2(240);
1789 l_exist VARCHAR2(1);
1790 l_event_name VARCHAR2(50) := 'oracle.apps.ar.receipts.CashReceipt.Delete';
1791
1792 BEGIN
1793 IF PG_DEBUG in ('Y', 'C') THEN
1794 arp_util.debug('Raise_Rcpt_Deletion_Event (+)');
1795 END IF;
1796 --Get the item key
1797 l_key := AR_CMGT_EVENT_PKG.item_key( l_event_name ,
1798 p_payment_schedule_id);
1799 IF (isRefreshProgramRunning) THEN
1800
1801 insert_events_hist (p_be_name => l_event_name,
1802 p_event_key => l_key,
1803 p_ps_id => p_payment_schedule_id,
1804 p_ctx_id => null,
1805 p_cr_id => null,
1806 p_ra_id => null,
1807 p_adj_id => null,
1808 p_hist_id => null,
1809 p_req_id => null);
1810 ELSE
1811
1812 -- initialization of object variables
1813 l_list := WF_PARAMETER_LIST_T();
1814
1815 -- Add Context values to the list
1816 ar_cmgt_event_pkg.AddParamEnvToList(l_list);
1817
1818 -- add more parameters to the parameters list
1819 wf_event.AddParameterToList(p_name => 'PAYMENT_SCHEDULE_ID',
1820 p_value => p_payment_schedule_id,
1821 p_parameterlist => l_list);
1822
1823 wf_event.AddParameterToList(p_name => 'DELETION_DATE',
1824 p_value => trunc(sysdate),
1825 p_parameterlist => l_list);
1826
1827 wf_event.AddParameterToList(p_name => 'RECEIPT_NUMBER',
1828 p_value => p_receipt_number,
1829 p_parameterlist => l_list);
1830
1831 wf_event.AddParameterToList(p_name => 'RECEIPT_DATE',
1832 p_value => p_receipt_date,
1833 p_parameterlist => l_list);
1834 -- Raise Event
1835 AR_CMGT_EVENT_PKG.raise_event(
1836 p_event_name => l_event_name,
1837 p_event_key => l_key,
1838 p_parameters => l_list );
1839
1840 l_list.DELETE;
1841 END IF;
1842 IF PG_DEBUG in ('Y', 'C') THEN
1843 arp_util.debug('Raise_Rcpt_Deletion_Event (-)');
1844 END IF;
1845
1846 EXCEPTION
1847 WHEN others THEN
1848 IF PG_DEBUG in ('Y', 'C') THEN
1849 arp_util.debug('ERR RAISING EVENT: '||l_event_name);
1850 END IF;
1851
1852 END;
1853
1854 END AR_BUS_EVENT_COVER; -- Package spec