[Home] [Help]
PACKAGE BODY: APPS.IEX_BUS_EVENT_SUB_PVT
Source
1 PACKAGE BODY IEX_BUS_EVENT_SUB_PVT AS
2 /* $Header: iexbsubb.pls 120.0.12000000.1 2007/05/02 07:07:35 appldev noship $*/
3
4 pg_debug NUMBER := nvl(to_number(fnd_profile.value('IEX_DEBUG_LEVEL')),20);
5
6 FUNCTION isRefreshProgramsRunning RETURN BOOLEAN IS
7 CURSOR C1 IS
8 select request_id
9 from AR_CONC_PROCESS_REQUESTS
10 where CONCURRENT_PROGRAM_NAME in ('ARSUMREF','IEX_POPULATE_UWQ_SUM');
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 isRefreshProgramsRunning;
27
28 --Function For Transactions Events. Passes trx_id
29
30 FUNCTION SYNC_SUMMARY
31 ( p_subscription_guid In RAW
32 , p_event IN OUT NOCOPY WF_EVENT_T
33 )
34 RETURN VARCHAR2 IS
35
36
37 i INTEGER;
38 l_key VARCHAR2(240) := p_event.GetEventKey();
39 l_payment_schedule_id NUMBER(15);
40 l_customer_trx_id NUMBER;
41 l_org_id NUMBER;
42 l_user_id NUMBER;
43 l_resp_id NUMBER;
44 l_application_id NUMBER;
45 l_security_gr_id NUMBER;
46 x_return_status VARCHAR2(30);
47
48
49
50
51 BEGIN
52
53 IF pg_debug <=10
54 THEN
55 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.Synch_Summary Started ' );
56 END IF;
57
58 l_customer_trx_id := p_event.GetValueForParameter('CUSTOMER_TRX_ID');
59 l_org_id := p_event.GetValueForParameter('ORG_ID');
60 l_user_id := p_event.GetValueForParameter('USER_ID');
61 l_resp_id := p_event.GetValueForParameter('RESP_ID');
62 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
63 l_security_gr_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
64
65
66 SAVEPOINT Event;
67 IF pg_debug <=10
68 THEN
69 iex_debug_pub.LogMessage ('l_customer_trx_id= '||l_customer_trx_id);
70 iex_debug_pub.LogMessage ('l_org_id= '||l_org_id);
71 iex_debug_pub.LogMessage ('l_user_id= '||l_user_id);
72 iex_debug_pub.LogMessage ('l_resp_id= '||l_resp_id);
73 iex_debug_pub.LogMessage ('l_application_id= '||l_application_id);
74 iex_debug_pub.LogMessage ('l_security_gr_id= '||l_security_gr_id);
75 null;
76 END IF;
77
78 --
79 --set the application context.
80 --
81 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
82
83 IF NOT isRefreshProgramsRunning THEN
84 x_return_status := UPDATE_SUMMARY(l_customer_trx_id,l_org_id ,'INV');
85 ELSE
86 IF pg_debug <=10
87 THEN
88 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.Synch_Summary Skipped ' );
89 END IF;
90 END IF;
91
92 IF pg_debug <=10
93 THEN
94 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.Synch_Summary Finished ' );
95 END IF;
96
97 Return 'SUCCESS';
98 EXCEPTION
99 WHEN OTHERS THEN
100 ROLLBACK TO Event;
101
102 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
103 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
104 FND_MSG_PUB.ADD;
105
106 WF_CORE.CONTEXT('IEX_BUS_EVENT_SUB_PVT', 'SYNC_SUMMARY', 'IEX SUMMARY', NULL);
107 WF_EVENT.setErrorInfo(p_event, 'ERROR');
108
109 RETURN 'ERROR';
110 END SYNC_SUMMARY;
111
112 --Function For Receipts Events. Passes Payment_schedule_id
113 FUNCTION SYNC_CASHRECEIPT
114 ( p_subscription_guid In RAW
115 , p_event IN OUT NOCOPY WF_EVENT_T
116 ) RETURN VARCHAR2 IS
117 l_payment_schedule_id NUMBER;
118 l_org_id NUMBER;
119 l_user_id NUMBER;
120 l_resp_id NUMBER;
121 l_application_id NUMBER;
122 l_cash_receipt_id NUMBER;
123 l_receipt_date DATE ;
124 l_receipt_amount NUMBER;
125 l_receipt_number VARCHAR2(30);
126 l_customer_id NUMBER;
127 l_customer_site_use_id NUMBER;
128 l_currency_code VARCHAR2(30);
129 l_cust_account_id ar_payment_schedules.customer_id%type;
130 l_security_gr_id NUMBER;
131
132 x_return_status VARCHAR2(30);
133 BEGIN
134
135 IF pg_debug <=10
136 THEN
137 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_CASHRECEIPT Started ' );
138 END IF;
139
140 l_payment_schedule_id := p_event.GetValueForParameter('PAYMENT_SCHEDULE_ID');
141 l_org_id := p_event.GetValueForParameter('ORG_ID');
142 l_user_id := p_event.GetValueForParameter('USER_ID');
143 l_resp_id := p_event.GetValueForParameter('RESP_ID');
144 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
145 l_security_gr_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
146 l_cust_account_id := p_event.GetValueForParameter('CUST_ACCOUNT_ID');
147
148 --
149 --set the application context.
150 --
151 IF pg_debug <=10
152 THEN
153 iex_debug_pub.LogMessage ('IEX SYNC_CASHRECEIPT Pmt Sch. Id: ' || l_payment_schedule_id);
154 iex_debug_pub.LogMessage ('IEX SYNC_CASHRECEIPT cust acct Id: ' || l_cust_account_id);
155 iex_debug_pub.LogMessage ('IEX SYNC_CASHRECEIPT Org Id :' || l_org_id);
156 iex_debug_pub.LogMessage ('IEX SYNC_CASHRECEIPT User Id : ' || l_user_id);
157 iex_debug_pub.LogMessage ('IEX SYNC_CASHRECEIPT Resp Id : ' || l_resp_id);
158 iex_debug_pub.LogMessage ('IEX SYNC_CASHRECEIPT Appl Id: ' || l_application_id );
159 iex_debug_pub.LogMessage ('IEX SYNC_CASHRECEIPT Grp Id : ' || l_security_gr_id);
160 END IF;
161
162 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
163
164 IF NOT isRefreshProgramsRunning THEN
165 x_return_status := UPDATE_SUMMARY(l_payment_schedule_id,l_org_id ,'REC');
166 ELSE
167 IF pg_debug <=10
168 THEN
169 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_CASHRECEIPT Skipped ' );
170 END IF;
171 END IF;
172
173 IF pg_debug <=10
174 THEN
175 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_CASHRECEIPT Finished ' );
176 END IF;
177
178
179 Return 'SUCCESS';
180
181 EXCEPTION
182 WHEN OTHERS THEN
183
184 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
185 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
186 FND_MSG_PUB.ADD;
187
188 WF_CORE.CONTEXT('IEX_BUS_EVENT_SUB_PVT', 'SYNC_SUMMARY', p_event.getEventName(), p_subscription_guid);
189 WF_EVENT.setErrorInfo(p_event, 'ERROR');
190
191 RETURN 'ERROR';
192
193 END SYNC_CASHRECEIPT;
194
195 --Function For Credit Memo and Cash Apply. Both these events pass receivables_application_id
196 FUNCTION SYNC_CM
197 ( p_subscription_guid In RAW
198 , p_event IN OUT NOCOPY WF_EVENT_T
199 ) RETURN VARCHAR2 IS
200
201 l_org_id NUMBER;
202 l_user_id NUMBER;
203 l_resp_id NUMBER;
204 l_application_id NUMBER;
205 l_security_gr_id NUMBER;
206 l_receivable_application_id NUMBER;
207 x_return_status VARCHAR2(30);
208 BEGIN
209
210 IF pg_debug <=10
211 THEN
212 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_CM Started ' );
213 END IF;
214
215 l_receivable_application_id :=
216 p_event.GetValueForParameter('RECEIVABLE_APPLICATION_ID');
217 l_org_id := p_event.GetValueForParameter('ORG_ID');
218 l_user_id := p_event.GetValueForParameter('USER_ID');
219 l_resp_id := p_event.GetValueForParameter('RESP_ID');
220 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
221 l_security_gr_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
222 --
223 --set the application context.
224 --
225 IF pg_debug <=10
226 THEN
227 iex_debug_pub.LogMessage ('IEX SYNC_CM RECEIVABLE_APPLICATION_ID: ' || l_receivable_application_id);
228 iex_debug_pub.LogMessage ('IEX SYNC_CM Org Id :' || l_org_id);
229 iex_debug_pub.LogMessage ('IEX SYNC_CM User Id : ' || l_user_id);
230 iex_debug_pub.LogMessage ('IEX SYNC_CM Resp Id : ' || l_resp_id);
231 iex_debug_pub.LogMessage ('IEX SYNC_CM Appl Id: ' || l_application_id );
232 iex_debug_pub.LogMessage ('IEX SYNC_CM Grp Id : ' || l_security_gr_id);
233 END IF;
234 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
235
236 IF NOT isRefreshProgramsRunning THEN
237 x_return_status := UPDATE_SUMMARY(l_receivable_application_id,l_org_id ,'CM');
238 ELSE
239 IF pg_debug <=10
240 THEN
241 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_CM Skipped ' );
242 END IF;
243 END IF;
244
245
246 IF pg_debug <=10
247 THEN
248 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_CM Finished ' );
249 END IF;
250 Return 'SUCCESS';
251
252 EXCEPTION
253 WHEN OTHERS THEN
254
255 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
256 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
257 FND_MSG_PUB.ADD;
258
259 WF_CORE.CONTEXT('IEX_BUS_EVENT_SUB_PVT', 'SYNC_SUMMARY', p_event.getEventName(), p_subscription_guid);
260 WF_EVENT.setErrorInfo(p_event, 'ERROR');
261
262 RETURN 'ERROR';
263
264 END SYNC_CM;
265
266 --Function For Adjustment Events. Passes adjustment_id
267
268 FUNCTION SYNC_ADJ
269 ( p_subscription_guid In RAW
270 , p_event IN OUT NOCOPY WF_EVENT_T
271 )
272 RETURN VARCHAR2 IS
273 l_adjustment_id NUMBER;
274 l_org_id NUMBER;
275 l_user_id NUMBER;
276 l_resp_id NUMBER;
277 l_application_id NUMBER;
278 l_security_gr_id NUMBER;
279 x_return_status VARCHAR2(30);
280
281 BEGIN
282
283 IF pg_debug <=10
284 THEN
285 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_ADJ Started ' );
286 END IF;
287 l_adjustment_id := p_event.GetValueForParameter('ADJUSTMENT_ID');
288 l_org_id := p_event.GetValueForParameter('ORG_ID');
289 l_user_id := p_event.GetValueForParameter('USER_ID');
290 l_resp_id := p_event.GetValueForParameter('RESP_ID');
291 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
292 l_security_gr_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
293
294 IF pg_debug <=10
295 THEN
296 iex_debug_pub.LogMessage ('IEX SYNC_ADJ ADJUSTMENT_ID: ' || l_adjustment_id);
297 iex_debug_pub.LogMessage ('IEX SYNC_CM Org Id :' || l_org_id);
298 iex_debug_pub.LogMessage ('IEX SYNC_CM User Id : ' || l_user_id);
299 iex_debug_pub.LogMessage ('IEX SYNC_CM Resp Id : ' || l_resp_id);
300 iex_debug_pub.LogMessage ('IEX SYNC_CM Appl Id: ' || l_application_id );
301 iex_debug_pub.LogMessage ('IEX SYNC_CM Grp Id : ' || l_security_gr_id);
302 END IF;
303 --
304 --set the application context.
305 --
306 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
307
308 IF NOT isRefreshProgramsRunning THEN
309 x_return_status := UPDATE_SUMMARY(l_adjustment_id,l_org_id ,'ADJ');
310 ELSE
311 IF pg_debug <=10
312 THEN
313 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_ADJ Skipped ' );
314 END IF;
315 END IF;
316
317 IF pg_debug <=10
318 THEN
319 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_ADJ Finished ' );
320 END IF;
321
322 Return 'SUCCESS';
323
324 EXCEPTION
325 WHEN OTHERS THEN
326
327 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
328 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
329 FND_MSG_PUB.ADD;
330
331 WF_CORE.CONTEXT('IEX_BUS_EVENT_SUB_PVT', 'SYNC_SUMMARY', p_event.getEventName(), p_subscription_guid);
332 WF_EVENT.setErrorInfo(p_event, 'ERROR');
333
334 RETURN 'ERROR';
335
336 END SYNC_ADJ;
337
338
339 --Function For Auto Adjustments. Passes request_id
340 FUNCTION SYNC_AUTOADJ
341 ( p_subscription_guid In RAW
342 , p_event IN OUT NOCOPY WF_EVENT_T
343 )
344 RETURN VARCHAR2 IS
345 l_org_id NUMBER;
346 l_user_id NUMBER;
347 l_resp_id NUMBER;
348 l_application_id NUMBER;
349 l_security_gr_id NUMBER;
350 l_request_id NUMBER;
351 x_return_status VARCHAR2(30);
352 BEGIN
353 IF pg_debug <=10
354 THEN
355 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOADJ Started ' );
356 END IF;
357
358 l_request_id := p_event.GetValueForParameter('REQUEST_ID');
359 l_org_id := p_event.GetValueForParameter('ORG_ID');
360 l_user_id := p_event.GetValueForParameter('USER_ID');
361 l_resp_id := p_event.GetValueForParameter('RESP_ID');
362 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
363 --
364 --set the application context.
365 --
366
367 IF pg_debug <=10
368 THEN
369 iex_debug_pub.LogMessage ('IEX Auto Receipts Request Id: ' || l_request_id);
370 iex_debug_pub.LogMessage ('IEX Auto Receipts Org Id :' || l_org_id);
371 iex_debug_pub.LogMessage ('IEX Auto Receipts User Id : ' || l_user_id);
372 iex_debug_pub.LogMessage ('IEX Auto Receipts Resp Id : ' || l_resp_id);
373 iex_debug_pub.LogMessage ('IEX Auto Receipts Appl Id: ' || l_application_id );
374 iex_debug_pub.LogMessage ('IEX Auto Receipts Grp Id : ' || l_security_gr_id);
375 END IF;
376
377 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
378 IF NOT isRefreshProgramsRunning THEN
379 x_return_status := UPDATE_SUMMARY(l_request_id,l_org_id ,'AUTOADJ');
380 ELSE
381 IF pg_debug <=10
382 THEN
383 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOADJ Skipped ' );
384 END IF;
385 END IF;
386
390 END IF;
387 IF pg_debug <=10
388 THEN
389 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOADJ Finished ' );
391
392
393 Return 'SUCCESS';
394
395 EXCEPTION
396 WHEN OTHERS THEN
397
398 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
399 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
400 FND_MSG_PUB.ADD;
401
402 WF_CORE.CONTEXT('IEX_BUS_EVENT_SUB_PVT', 'SYNC_ADJ', p_event.getEventName(), p_subscription_guid);
403 WF_EVENT.setErrorInfo(p_event, 'ERROR');
404
405 RETURN 'ERROR';
406
407 END SYNC_AUTOADJ;
408
409
410 --Function For AutoReceipts. Passes request_id
411 FUNCTION SYNC_AUTOREC
412 ( p_subscription_guid In RAW
413 , p_event IN OUT NOCOPY WF_EVENT_T
414 )
415 RETURN VARCHAR2 IS
416 l_request_id NUMBER;
417 l_org_id NUMBER;
418 l_user_id NUMBER;
419 l_resp_id NUMBER;
420 l_application_id NUMBER;
421 l_security_gr_id NUMBER;
422 x_return_status VARCHAR2(30);
423 BEGIN
424
425 IF pg_debug <=10
426 THEN
427 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOREC Started ' );
428 END IF;
429
430
431 l_request_id := p_event.GetValueForParameter('REQUEST_ID');
432 l_org_id := p_event.GetValueForParameter('ORG_ID');
433 l_user_id := p_event.GetValueForParameter('USER_ID');
434 l_resp_id := p_event.GetValueForParameter('RESP_ID');
435 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
436 l_security_gr_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
437 --
438 --set the application context.
439 --
440 IF pg_debug <=10
441 THEN
442 iex_debug_pub.LogMessage ('IEX Auto Receipts Request Id: ' || l_request_id);
443 iex_debug_pub.LogMessage ('IEX Auto Receipts Org Id :' || l_org_id);
444 iex_debug_pub.LogMessage ('IEX Auto Receipts User Id : ' || l_user_id);
445 iex_debug_pub.LogMessage ('IEX Auto Receipts Resp Id : ' || l_resp_id);
446 iex_debug_pub.LogMessage ('IEX Auto Receipts Appl Id: ' || l_application_id );
447 iex_debug_pub.LogMessage ('IEX Auto Receipts Grp Id : ' || l_security_gr_id);
448 END IF;
449
450
451
452 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
453 IF NOT isRefreshProgramsRunning THEN
454 x_return_status := UPDATE_SUMMARY(l_request_id,l_org_id ,'AUTOREC');
455 ELSE
456 IF pg_debug <=10
457 THEN
458 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOREC Skipped ' );
459 END IF;
460 END IF;
461
462 IF pg_debug <=10
463 THEN
464 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOREC Finished ' );
465 END IF;
466
467 Return 'SUCCESS';
468
469 EXCEPTION
470 WHEN OTHERS THEN
471
472 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
473 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
474 FND_MSG_PUB.ADD;
475
476 WF_CORE.CONTEXT('IEX_BUS_EVENT_SUB_PVT', 'SYNC_RECEIPTS', p_event.getEventName(), p_subscription_guid);
477 WF_EVENT.setErrorInfo(p_event, 'ERROR');
478
479 RETURN 'ERROR';
480
481 END SYNC_AUTOREC;
482
483
484 --Function For AutoInvoices. Passes request_id
485 FUNCTION SYNC_AUTOINV
486 ( p_subscription_guid In RAW
487 , p_event IN OUT NOCOPY WF_EVENT_T
488 )
489 RETURN VARCHAR2 IS
490 l_request_id NUMBER;
491 l_org_id NUMBER;
492 l_user_id NUMBER;
493 l_resp_id NUMBER;
494 l_application_id NUMBER;
495 l_security_gr_id NUMBER;
496 x_return_status VARCHAR2(30);
497 BEGIN
498 IF pg_debug <=10
499 THEN
500 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOINV Started ' );
501 END IF;
502
503 l_request_id := p_event.GetValueForParameter('REQUEST_ID');
504 l_org_id := p_event.GetValueForParameter('ORG_ID');
505 l_user_id := p_event.GetValueForParameter('USER_ID');
506 l_resp_id := p_event.GetValueForParameter('RESP_ID');
507 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
508 l_security_gr_id := p_event.GetValueForParameter('SECURITY_GROUP_ID');
509 --
510 --set the application context.
511 --
512 IF pg_debug <=10
513 THEN
514 iex_debug_pub.LogMessage ('IEX Auto Receipts Request Id: ' || l_request_id);
515 iex_debug_pub.LogMessage ('IEX Auto Receipts Org Id :' || l_org_id);
516 iex_debug_pub.LogMessage ('IEX Auto Receipts User Id : ' || l_user_id);
517 iex_debug_pub.LogMessage ('IEX Auto Receipts Resp Id : ' || l_resp_id);
518 iex_debug_pub.LogMessage ('IEX Auto Receipts Appl Id: ' || l_application_id );
519 iex_debug_pub.LogMessage ('IEX Auto Receipts Grp Id : ' || l_security_gr_id);
520 END IF;
521 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
522 IF NOT isRefreshProgramsRunning THEN
523 x_return_status := UPDATE_SUMMARY(l_request_id,l_org_id ,'AUTOINV');
524 ELSE
525 IF pg_debug <=10
526 THEN
527 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOINV Skipped ' );
528 END IF;
529 END IF;
530
531 IF pg_debug <=10
532 THEN
533 iex_debug_pub.LogMessage('IEX_BUS_EVENT_SUB_PVT.SYNC_AUTOINV Finished ' );
534 END IF;
535
536 Return 'SUCCESS';
537
538 EXCEPTION
539 WHEN OTHERS THEN
540
544
541 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
542 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
543 FND_MSG_PUB.ADD;
545 WF_CORE.CONTEXT('IEX_BUS_EVENT_SUB_PVT', 'SYNC_INVOICES', p_event.getEventName(), p_subscription_guid);
546 WF_EVENT.setErrorInfo(p_event, 'ERROR');
547
548 RETURN 'ERROR';
549
550 END SYNC_AUTOINV;
551
552 --Function to Synchronize IEX and AR.
553
554 FUNCTION UPDATE_SUMMARY(
555 id_val IN NUMBER
556 ,l_org_id IN NUMBER
557 ,trx_type IN VARCHAR2
558 )
559 RETURN VARCHAR2 IS
560 l_customer_trx_id NUMBER;
561 TYPE ps_tab_type IS TABLE OF ar_payment_schedules%rowtype
562 INDEX BY BINARY_INTEGER;
563 l_ps_tab ps_tab_type;
564 l_trx_summary_hist AR_TRX_SUMMARY_HIST%rowtype;
565
566 CURSOR select_ps (cust_trx_id IN NUMBER) IS
567 SELECT * from ar_payment_schedules
568 WHERE customer_trx_id = cust_trx_id;
569
570 CURSOR select_ps_rec (l_payment_schedule_id IN NUMBER) IS
571 SELECT * from ar_payment_schedules
572 WHERE payment_schedule_id = l_payment_schedule_id;
573
574 CURSOR select_ps_cm (p_ra_id IN NUMBER ) IS
575 SELECT trx_ps.*
576 FROM ar_payment_schedules trx_ps,
577 ar_receivable_applications ra,
578 ar_payment_schedules rcpt_ps
579 WHERE ra.receivable_application_id = p_ra_id
580 AND ra.status in ('APP')
581 AND ra.payment_schedule_id = rcpt_ps.payment_schedule_id
582 AND ra.applied_payment_schedule_id = trx_ps.payment_schedule_id;
583
584 CURSOR select_ps_adj (p_adj_id IN NUMBER) IS
585 SELECT ps.*
586 FROM ar_adjustments adj,
587 ar_payment_schedules ps
588 WHERE adj.payment_schedule_id = ps.payment_schedule_id
589 and adj.adjustment_id = p_adj_id ;
590
591 CURSOR select_ps_auto_adj (p_req_id IN NUMBER) IS
592 SELECT ps.*
593 FROM ar_adjustments adj,
594 ar_payment_schedules ps
595 WHERE adj.request_id = p_req_id
596 and adj.payment_schedule_id = ps.payment_schedule_id;
597
598
599 CURSOR select_ps_auto_rec (p_request_id IN NUMBER) IS
600 SELECT ps.*
601 FROM ar_receivable_applications ra,
602 ar_payment_schedules ps
603 WHERE ra.request_id = p_request_id
604 AND ra.status IN('APP','UNAPP')
605 AND ps.payment_schedule_id = ra.applied_payment_schedule_id;
606
607 CURSOR select_ps_auto_inv (p_request_id IN NUMBER) IS
608 SELECT ps.*
609 FROM ra_customer_trx trx,
610 ar_payment_schedules ps
611 WHERE
612 trx.customer_trx_id = ps.customer_trx_id
613 AND trx.request_id = p_request_id;
614
615 CURSOR select_pref IS SELECT PREFERENCE_VALUE FROM IEX_APP_PREFERENCES_VL
616 WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL';
617
618 CURSOR update_iex_sum_billto(p_cust_account_id IN NUMBER,
619 p_site_use_id IN NUMBER,
620 p_org_id IN NUMBER) IS
621 SELECT
622 max(gl.CURRENCY_CODE) currency,
623 SUM(trx_summ.op_invoices_count) op_invoices_count,
624 SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
625 SUM(trx_summ.op_deposits_count) op_deposits_count,
626 SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
627 SUM(trx_summ.op_chargeback_count) op_chargeback_count,
628 SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
629 SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
630 SUM(trx_summ.disputed_inv_count) disputed_inv_count,
631 SUM(TRX_SUMM.PAST_DUE_INV_INST_COUNT) PAST_DUE_INV_INST_COUNT,
632 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
633 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
634 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
635 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
636 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
637 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
638 (SELECT SUM(b.acctd_amount_due_remaining)
639 FROM iex_delinquencies_all a,
640 ar_payment_schedules_all b
641 WHERE a.customer_site_use_id = trx_summ.site_use_id
642 AND a.payment_schedule_id = b.payment_schedule_id
643 AND b.status = 'OP'
644 AND a.status IN('DELINQUENT', 'PREDELINQUENT')) past_due_inv_value,
645 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
646 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
647 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
648 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
649 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
650 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
651 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
652 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
653 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
654 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
655 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
656 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
657 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
658 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
662 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
659 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
660 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
661 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
663 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
664 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
665 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
666 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
667 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
668 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
669 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
670 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
671 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
672 MAX(trx_summ.last_payment_date) last_payment_date, --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
673 MAX(iex_uwq_view_pkg.get_last_payment_amount(0, 0, trx_summ.site_use_id)) last_payment_amount,
674 max(gl.CURRENCY_CODE) last_payment_amount_curr,
675 MAX(iex_uwq_view_pkg.get_last_payment_number(0, 0, trx_summ.site_use_id)) last_payment_number,
676 trx_summ.site_use_id,
677 trx_summ.org_id
678 FROM AR_TRX_BAL_SUMMARY trx_summ,
679 GL_SETS_OF_BOOKS gl,
680 AR_SYSTEM_PARAMETERS_all sys
681 WHERE
682 gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
683 AND sys.org_id = trx_summ.org_id
684 AND trx_summ.cust_account_id = p_cust_account_id
685 AND trx_summ.site_use_id = p_site_use_id
686 AND trx_summ.org_id = p_org_id
687 GROUP BY trx_summ.site_use_id, trx_summ.org_id;
688
689 CURSOR update_iex_sum_acc(p_cust_account_id IN NUMBER,
690 p_org_id IN NUMBER) IS
691 SELECT
692 max(gl.CURRENCY_CODE) currency,
693 SUM(trx_summ.op_invoices_count) op_invoices_count,
694 SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
695 SUM(trx_summ.op_deposits_count) op_deposits_count,
696 SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
697 SUM(trx_summ.op_chargeback_count) op_chargeback_count,
698 SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
699 SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
700 SUM(trx_summ.disputed_inv_count) disputed_inv_count,
701 SUM(TRX_SUMM.PAST_DUE_INV_INST_COUNT) PAST_DUE_INV_INST_COUNT,
702 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
703 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
704 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
705 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
706 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
707 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
708 (SELECT SUM(b.acctd_amount_due_remaining)
709 FROM iex_delinquencies_all a,
710 ar_payment_schedules_all b
711 WHERE a.cust_account_id = trx_summ.cust_account_id
712 AND a.payment_schedule_id = b.payment_schedule_id
713 AND b.status = 'OP'
714 AND a.status IN('DELINQUENT', 'PREDELINQUENT')) past_due_inv_value,
715 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
716 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
717 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
718 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
719 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
720 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
721 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
722 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
723 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
724 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
725 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
726 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
727 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
728 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
729 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
730 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
731 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
732 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
733 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
734 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
735 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
736 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
740 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
737 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
738 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
739 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
741 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
742 MAX(trx_summ.last_payment_date) last_payment_date, --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
743 MAX(iex_uwq_view_pkg.get_last_payment_amount(0, trx_summ.cust_account_id, 0)) last_payment_amount,
744 max(gl.CURRENCY_CODE) last_payment_amount_curr,
745 MAX(iex_uwq_view_pkg.get_last_payment_number(0, trx_summ.cust_account_id, 0)) last_payment_number,
746 TRX_SUMM.cust_account_id,
747 TRX_SUMM.org_id
748 FROM AR_TRX_BAL_SUMMARY TRX_SUMM,
749 GL_SETS_OF_BOOKS gl,
750 AR_SYSTEM_PARAMETERS_all sys
751 WHERE
752 gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
753 AND sys.org_id = trx_summ.org_id
754 AND TRX_SUMM.cust_account_id = p_cust_account_id
755 AND TRX_SUMM.org_id = p_org_id
756 GROUP BY TRX_SUMM.cust_account_id,TRX_SUMM.org_id;
757
758
759 CURSOR update_iex_sum_cu(p_cust_account_id IN NUMBER,
760 p_org_id IN NUMBER) IS
761 SELECT
762 max(gl.CURRENCY_CODE) currency,
763 SUM(trx_summ.op_invoices_count) op_invoices_count,
764 SUM(trx_summ.op_debit_memos_count) op_debit_memos_count,
765 SUM(trx_summ.op_deposits_count) op_deposits_count,
766 SUM(trx_summ.op_bills_receivables_count) op_bills_receivables_count,
767 SUM(trx_summ.op_chargeback_count) op_chargeback_count,
768 SUM(trx_summ.op_credit_memos_count) op_credit_memos_count,
769 SUM(trx_summ.unresolved_cash_count) unresolved_cash_count,
770 SUM(trx_summ.disputed_inv_count) disputed_inv_count,
771 SUM(TRX_SUMM.PAST_DUE_INV_INST_COUNT) PAST_DUE_INV_INST_COUNT,
772 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.best_current_receivables,
773 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
774 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.best_current_receivables))) best_current_receivables,
775 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_invoices_value,
776 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
777 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_invoices_value))) op_invoices_value,
778 (SELECT SUM(b.acctd_amount_due_remaining)
779 FROM iex_delinquencies_all a,
780 ar_payment_schedules_all b
781 WHERE a.party_cust_id = party.party_id
782 AND a.payment_schedule_id = b.payment_schedule_id
783 AND b.status = 'OP'
784 AND a.status IN('DELINQUENT', 'PREDELINQUENT')) past_due_inv_value,
785 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_debit_memos_value,
786 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
787 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_debit_memos_value))) op_debit_memos_value,
788 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_deposits_value,
789 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
790 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_deposits_value))) op_deposits_value,
791 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_bills_receivables_value,
792 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
793 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_bills_receivables_value))) op_bills_receivables_value,
794 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_chargeback_value,
795 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
796 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_chargeback_value))) op_chargeback_value,
797 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.op_credit_memos_value,
798 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
799 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.op_credit_memos_value))) op_credit_memos_value,
800 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.unresolved_cash_value,
801 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
802 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.unresolved_cash_value))) unresolved_cash_value,
803 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.receipts_at_risk_value,
804 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
805 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.receipts_at_risk_value))) receipts_at_risk_value,
806 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.inv_amt_in_dispute,
807 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
808 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.inv_amt_in_dispute))) inv_amt_in_dispute,
809 SUM(decode(trx_summ.currency, gl.CURRENCY_CODE, trx_summ.pending_adj_value,
810 gl_currency_api.convert_amount_sql(trx_summ.currency, gl.CURRENCY_CODE, sysdate,
811 iex_utilities.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', ''), trx_summ.pending_adj_value))) pending_adj_value,
812 MAX(trx_summ.last_payment_date) last_payment_date, --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
813 MAX(iex_uwq_view_pkg.get_last_payment_amount(party.party_id, 0, 0)) last_payment_amount,
814 max(gl.CURRENCY_CODE) last_payment_amount_curr,
818 FROM AR_TRX_BAL_SUMMARY trx_summ, hz_cust_accounts acc,hz_parties party,
815 MAX(iex_uwq_view_pkg.get_last_payment_number(party.party_id, 0, 0)) last_payment_number,
816 party.party_id party_id,
817 trx_summ.org_id org_id
819 GL_SETS_OF_BOOKS gl,
820 AR_SYSTEM_PARAMETERS_all sys
821 WHERE
822 gl.SET_OF_BOOKS_ID = sys.SET_OF_BOOKS_ID
823 AND sys.org_id = trx_summ.org_id
824 AND trx_summ.cust_account_id = acc.cust_account_id
825 AND party.party_id = acc.party_id
826 AND trx_summ.cust_account_id = p_cust_account_id
827 AND trx_summ.org_id = p_org_id
828 GROUP BY party.party_id,trx_summ.org_id;
829
830 L_OP_INVOICES_COUNT NUMBER;
831 L_OP_DEBIT_MEMOS_COUNT NUMBER;
832 L_OP_DEPOSITS_COUNT NUMBER;
833 L_OP_BILLS_RECEIVABLES_COUNT NUMBER;
834 L_OP_CHARGEBACK_COUNT NUMBER;
835 L_OP_CREDIT_MEMOS_COUNT NUMBER;
836 L_UNRESOLVED_CASH_COUNT NUMBER;
837 L_DISPUTED_INV_COUNT NUMBER;
838 L_BEST_CURRENT_RECEIVABLES NUMBER;
839 L_OP_INVOICES_VALUE NUMBER;
840 L_OP_DEBIT_MEMOS_VALUE NUMBER;
841 L_OP_DEPOSITS_VALUE NUMBER;
842 L_OP_BILLS_RECEIVABLES_VALUE NUMBER;
843 L_OP_CHARGEBACK_VALUE NUMBER;
844 L_OP_CREDIT_MEMOS_VALUE NUMBER;
845 L_UNRESOLVED_CASH_VALUE NUMBER;
846 L_RECEIPTS_AT_RISK_VALUE NUMBER;
847 L_INV_AMT_IN_DISPUTE NUMBER;
848 L_PENDING_ADJ_VALUE NUMBER;
849 L_PAST_DUE_INV_VALUE NUMBER;
850 L_PAST_DUE_INV_INST_COUNT NUMBER;
851 i NUMBER;
852 l_pref_value VARCHAR2(30);
853 l_ps_exists BOOLEAN;
854 l_cash VARCHAR2(240);
855
856 BEGIN
857
858 OPEN select_pref;
859 FETCH select_pref INTO l_pref_value;
860 CLOSE select_pref;
861
862 l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');
863 -- Invoice Events Start.
864
865 IF (trx_type = 'INV') THEN
866 IF pg_debug <=10
867 THEN
868 iex_debug_pub.LogMessage ('IEX Update Summary started for Invoice Event with id ' || id_val);
869 END IF;
870 OPEN select_ps (id_val); --id_val Contains Transaction ID .
871 i := 1;
872 LOOP
873 FETCH select_ps INTO l_ps_tab(i);
874
875 IF select_ps%NOTFOUND THEN
876 IF i = 0 THEN
877 l_ps_exists := FALSE;
878 ELSE
879 l_ps_exists := TRUE;
880 END IF;
881
882 EXIT;
883 END IF;
884 i := i + 1;
885 END LOOP;
886 CLOSE select_ps;
887
888 --Receipts.
889 ELSIF (trx_type = 'REC') THEN
890 IF pg_debug <=10
891 THEN
892 iex_debug_pub.LogMessage ('IEX Update Summary started for Receipt Event with id ' || id_val);
893 END IF;
894 OPEN select_ps_rec (id_val); --id_val Contains Payment Schedule ID .
895 i := 1;
896 LOOP
897 FETCH select_ps_rec INTO l_ps_tab(i);
898
899 IF select_ps_rec%NOTFOUND THEN
900 IF i = 0 THEN
901 l_ps_exists := FALSE;
902 ELSE
903 l_ps_exists := TRUE;
904 END IF;
905
906 EXIT;
907 END IF;
908 i := i + 1;
909 END LOOP;
910 CLOSE select_ps_rec;
911
912 ELSIF (trx_type = 'CM') THEN
913 IF pg_debug <=10
914 THEN
915 iex_debug_pub.LogMessage ('IEX Update Summary started for credit memo Event with id ' || id_val);
916 END IF;
917 OPEN select_ps_cm (id_val); --id_val Contains Transaction ID .
918 i := 1;
919 LOOP
920 FETCH select_ps_cm INTO l_ps_tab(i);
921
922 IF select_ps_cm%NOTFOUND THEN
923 IF i = 0 THEN
924 l_ps_exists := FALSE;
925 ELSE
926 l_ps_exists := TRUE;
927 END IF;
928
929 EXIT;
930 END IF;
931 i := i + 1;
932 END LOOP;
933 CLOSE select_ps_cm;
934
935 ELSIF (trx_type = 'ADJ') THEN
936 IF pg_debug <=10
937 THEN
938 iex_debug_pub.LogMessage ('IEX Update Summary started for Adjustment Event with id ' || id_val);
939 END IF;
940 OPEN select_ps_adj (id_val); --id_val Contains Adjustment ID
941 i := 1;
942 LOOP
943 FETCH select_ps_adj INTO l_ps_tab(i);
944
945 IF select_ps_adj%NOTFOUND THEN
946 IF i = 0 THEN
947 l_ps_exists := FALSE;
948 ELSE
949 l_ps_exists := TRUE;
950 END IF;
951
952 EXIT;
953 END IF;
954 i := i + 1;
955 END LOOP;
956 CLOSE select_ps_adj;
957
958 ELSIF (trx_type = 'AUTOADJ') THEN
959 IF pg_debug <=10
960 THEN
961 iex_debug_pub.LogMessage ('IEX Update Summary started for Auto Adjustment Event with id ' || id_val);
962 END IF;
963 OPEN select_ps_auto_adj (id_val); --id_val Contains Request ID
964 i := 1;
965 LOOP
966 FETCH select_ps_auto_adj INTO l_ps_tab(i);
967
968 IF select_ps_auto_adj%NOTFOUND THEN
969 IF i = 0 THEN
970 l_ps_exists := FALSE;
971 ELSE
972 l_ps_exists := TRUE;
973 END IF;
974
975 EXIT;
976 END IF;
977 i := i + 1;
978 END LOOP;
979 CLOSE select_ps_auto_adj;
980
981 ELSIF (trx_type = 'AUTOREC') THEN
982 IF pg_debug <=10
983 THEN
984 iex_debug_pub.LogMessage ('IEX Update Summary started for Auto Receipt Event with id ' || id_val);
985 END IF;
986 OPEN select_ps_auto_rec (id_val); --id_val Contains Request ID
990
987 i := 1;
988 LOOP
989 FETCH select_ps_auto_rec INTO l_ps_tab(i);
991 IF select_ps_auto_rec%NOTFOUND THEN
992 IF i = 0 THEN
993 l_ps_exists := FALSE;
994 ELSE
995 l_ps_exists := TRUE;
996 END IF;
997
998 EXIT;
999 END IF;
1000 i := i + 1;
1001 END LOOP;
1002 CLOSE select_ps_auto_rec;
1003
1004 ELSIF (trx_type = 'AUTOINV') THEN
1005 IF pg_debug <=10
1006 THEN
1007 iex_debug_pub.LogMessage ('IEX Update Summary started for Auto Invoice Event with id ' || id_val);
1008 END IF;
1009 OPEN select_ps_auto_inv (id_val); --id_val Contains Request ID
1010 i := 1;
1011 LOOP
1012 FETCH select_ps_auto_inv INTO l_ps_tab(i);
1013
1014 IF select_ps_auto_inv%NOTFOUND THEN
1015 IF i = 0 THEN
1016 l_ps_exists := FALSE;
1017 ELSE
1018 l_ps_exists := TRUE;
1019 END IF;
1020
1021 EXIT;
1022 END IF;
1023 i := i + 1;
1024 END LOOP;
1025 CLOSE select_ps_auto_inv;
1026
1027
1028 END IF;
1029
1030
1031 IF l_ps_exists THEN
1032 IF pg_debug <=10
1033 THEN
1034 iex_debug_pub.LogMessage ('IEX Update Summary l_ps_exists , Starting...' );
1035 END IF;
1036 FOR j in 1..l_ps_tab.COUNT
1037 LOOP
1038
1039 --Update for Strategy Level Bill To.
1040 IF (l_pref_value = 'BILL_TO') THEN
1041
1042 FOR upd_billto in update_iex_sum_billto
1043 (l_ps_tab(j).customer_id,l_ps_tab(j).customer_site_use_id,
1044 l_org_id)
1045 LOOP
1046 --Synchronize ar and iex summary tables
1047
1048 UPDATE iex_dln_uwq_summary
1049 SET
1050 OP_INVOICES_COUNT = upd_billto.OP_INVOICES_COUNT,
1051 OP_DEBIT_MEMOS_COUNT = upd_billto.OP_DEBIT_MEMOS_COUNT,
1052 OP_DEPOSITS_COUNT = upd_billto.OP_DEPOSITS_COUNT,
1053 OP_BILLS_RECEIVABLES_COUNT = upd_billto.OP_BILLS_RECEIVABLES_COUNT,
1054 OP_CHARGEBACK_COUNT = upd_billto.OP_CHARGEBACK_COUNT,
1055 OP_CREDIT_MEMOS_COUNT = upd_billto.OP_CREDIT_MEMOS_COUNT,
1056 UNRESOLVED_CASH_COUNT = upd_billto.UNRESOLVED_CASH_COUNT,
1057 DISPUTED_INV_COUNT = upd_billto.DISPUTED_INV_COUNT,
1058 BEST_CURRENT_RECEIVABLES = upd_billto.BEST_CURRENT_RECEIVABLES,
1059 OP_INVOICES_VALUE = upd_billto.OP_INVOICES_VALUE,
1060 OP_DEBIT_MEMOS_VALUE = upd_billto.OP_DEBIT_MEMOS_VALUE,
1061 OP_DEPOSITS_VALUE = upd_billto.OP_DEPOSITS_VALUE,
1062 OP_BILLS_RECEIVABLES_VALUE = upd_billto.OP_BILLS_RECEIVABLES_VALUE,
1063 OP_CHARGEBACK_VALUE = upd_billto.OP_CHARGEBACK_VALUE,
1064 OP_CREDIT_MEMOS_VALUE = upd_billto.OP_CREDIT_MEMOS_VALUE,
1065 UNRESOLVED_CASH_VALUE = upd_billto.UNRESOLVED_CASH_VALUE,
1066 RECEIPTS_AT_RISK_VALUE = upd_billto.RECEIPTS_AT_RISK_VALUE,
1067 INV_AMT_IN_DISPUTE = upd_billto.INV_AMT_IN_DISPUTE,
1068 PENDING_ADJ_VALUE = upd_billto.PENDING_ADJ_VALUE,
1069 PAST_DUE_INV_VALUE = upd_billto.PAST_DUE_INV_VALUE,
1070 PAST_DUE_INV_INST_COUNT = upd_billto.PAST_DUE_INV_INST_COUNT,
1071 LAST_PAYMENT_DATE = upd_billto.LAST_PAYMENT_DATE, --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
1072 LAST_PAYMENT_AMOUNT = upd_billto.LAST_PAYMENT_AMOUNT,
1073 LAST_PAYMENT_AMOUNT_CURR = upd_billto.LAST_PAYMENT_AMOUNT_CURR,
1074 LAST_PAYMENT_NUMBER = upd_billto.LAST_PAYMENT_NUMBER,
1075 LAST_UPDATE_DATE = SYSDATE,
1076 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
1077 WHERE cust_account_id = l_ps_tab(j).customer_id
1078 AND site_use_id = l_ps_tab(j).customer_site_use_id
1079 AND org_id = l_org_id;
1080 END LOOP;
1081
1082 ELSIF (l_pref_value = 'ACCOUNT') THEN --Update for Strategy Level Account
1083 FOR upd_acc in update_iex_sum_acc
1084 (l_ps_tab(j).customer_id,l_org_id)
1085 LOOP
1086 --Synchronize ar and iex summary tables
1087
1088 UPDATE iex_dln_uwq_summary
1089 SET
1090 OP_INVOICES_COUNT = upd_acc.OP_INVOICES_COUNT,
1091 OP_DEBIT_MEMOS_COUNT = upd_acc.OP_DEBIT_MEMOS_COUNT,
1092 OP_DEPOSITS_COUNT = upd_acc.OP_DEPOSITS_COUNT,
1093 OP_BILLS_RECEIVABLES_COUNT = upd_acc.OP_BILLS_RECEIVABLES_COUNT,
1094 OP_CHARGEBACK_COUNT = upd_acc.OP_CHARGEBACK_COUNT,
1095 OP_CREDIT_MEMOS_COUNT = upd_acc.OP_CREDIT_MEMOS_COUNT,
1096 UNRESOLVED_CASH_COUNT = upd_acc.UNRESOLVED_CASH_COUNT,
1097 DISPUTED_INV_COUNT = upd_acc.DISPUTED_INV_COUNT,
1098 BEST_CURRENT_RECEIVABLES = upd_acc.BEST_CURRENT_RECEIVABLES,
1099 OP_INVOICES_VALUE = upd_acc.OP_INVOICES_VALUE,
1100 OP_DEBIT_MEMOS_VALUE = upd_acc.OP_DEBIT_MEMOS_VALUE,
1101 OP_DEPOSITS_VALUE = upd_acc.OP_DEPOSITS_VALUE,
1102 OP_BILLS_RECEIVABLES_VALUE = upd_acc.OP_BILLS_RECEIVABLES_VALUE,
1103 OP_CHARGEBACK_VALUE = upd_acc.OP_CHARGEBACK_VALUE,
1104 OP_CREDIT_MEMOS_VALUE = upd_acc.OP_CREDIT_MEMOS_VALUE,
1105 UNRESOLVED_CASH_VALUE = upd_acc.UNRESOLVED_CASH_VALUE,
1106 RECEIPTS_AT_RISK_VALUE = upd_acc.RECEIPTS_AT_RISK_VALUE,
1107 INV_AMT_IN_DISPUTE = upd_acc.INV_AMT_IN_DISPUTE,
1108 PENDING_ADJ_VALUE = upd_acc.PENDING_ADJ_VALUE,
1109 PAST_DUE_INV_VALUE = upd_acc.PAST_DUE_INV_VALUE,
1110 PAST_DUE_INV_INST_COUNT = upd_acc.PAST_DUE_INV_INST_COUNT,
1111 LAST_PAYMENT_DATE = upd_acc.LAST_PAYMENT_DATE, --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
1112 LAST_PAYMENT_AMOUNT = upd_acc.LAST_PAYMENT_AMOUNT,
1113 LAST_PAYMENT_AMOUNT_CURR = upd_acc.LAST_PAYMENT_AMOUNT_CURR,
1117 WHERE cust_account_id = l_ps_tab(j).customer_id
1114 LAST_PAYMENT_NUMBER = upd_acc.LAST_PAYMENT_NUMBER,
1115 LAST_UPDATE_DATE = SYSDATE,
1116 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
1118 AND org_id = l_org_id;
1119 END LOOP;
1120
1121 ELSE --Update for Strategy Level Customer
1122 FOR upd_cu in update_iex_sum_cu
1123 (l_ps_tab(j).customer_id,l_org_id)
1124 LOOP
1125 --Synchronize ar and iex summary tables
1126
1127 UPDATE iex_dln_uwq_summary
1128 SET
1129 OP_INVOICES_COUNT = upd_cu.OP_INVOICES_COUNT,
1130 OP_DEBIT_MEMOS_COUNT = upd_cu.OP_DEBIT_MEMOS_COUNT,
1131 OP_DEPOSITS_COUNT = upd_cu.OP_DEPOSITS_COUNT,
1132 OP_BILLS_RECEIVABLES_COUNT = upd_cu.OP_BILLS_RECEIVABLES_COUNT,
1133 OP_CHARGEBACK_COUNT = upd_cu.OP_CHARGEBACK_COUNT,
1134 OP_CREDIT_MEMOS_COUNT = upd_cu.OP_CREDIT_MEMOS_COUNT,
1135 UNRESOLVED_CASH_COUNT = upd_cu.UNRESOLVED_CASH_COUNT,
1136 DISPUTED_INV_COUNT = upd_cu.DISPUTED_INV_COUNT,
1137 BEST_CURRENT_RECEIVABLES = upd_cu.BEST_CURRENT_RECEIVABLES,
1138 OP_INVOICES_VALUE = upd_cu.OP_INVOICES_VALUE,
1139 OP_DEBIT_MEMOS_VALUE = upd_cu.OP_DEBIT_MEMOS_VALUE,
1140 OP_DEPOSITS_VALUE = upd_cu.OP_DEPOSITS_VALUE,
1141 OP_BILLS_RECEIVABLES_VALUE = upd_cu.OP_BILLS_RECEIVABLES_VALUE,
1142 OP_CHARGEBACK_VALUE = upd_cu.OP_CHARGEBACK_VALUE,
1143 OP_CREDIT_MEMOS_VALUE = upd_cu.OP_CREDIT_MEMOS_VALUE,
1144 UNRESOLVED_CASH_VALUE = upd_cu.UNRESOLVED_CASH_VALUE,
1145 RECEIPTS_AT_RISK_VALUE = upd_cu.RECEIPTS_AT_RISK_VALUE,
1146 INV_AMT_IN_DISPUTE = upd_cu.INV_AMT_IN_DISPUTE,
1147 PENDING_ADJ_VALUE = upd_cu.PENDING_ADJ_VALUE,
1148 PAST_DUE_INV_VALUE = upd_cu.PAST_DUE_INV_VALUE,
1149 PAST_DUE_INV_INST_COUNT = upd_cu.PAST_DUE_INV_INST_COUNT,
1150 LAST_PAYMENT_DATE = upd_cu.LAST_PAYMENT_DATE, --Added last payment columns for bug#5938261 by schekuri on 19-Mar-2007
1151 LAST_PAYMENT_AMOUNT = upd_cu.LAST_PAYMENT_AMOUNT,
1152 LAST_PAYMENT_AMOUNT_CURR = upd_cu.LAST_PAYMENT_AMOUNT_CURR,
1153 LAST_PAYMENT_NUMBER = upd_cu.LAST_PAYMENT_NUMBER,
1154 LAST_UPDATE_DATE = SYSDATE,
1155 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
1156 WHERE party_id = upd_cu.party_id
1157 AND org_id = l_org_id;
1158 END LOOP;
1159 END IF;
1160
1161 END LOOP;
1162
1163 ELSE --l_ps_exists if no payment schedule exits for the given customer_trx_id
1164 --then we do not update the summary table.
1165 null;
1166 END IF; --l_ps_exists
1167 IF pg_debug <=10
1168 THEN
1169 iex_debug_pub.LogMessage ('IEX Update Summary updated ' || sql%rowcount || ' Rows' );
1170 END IF;
1171 COMMIT;
1172 RETURN 'SUCCESS';
1173
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 ROLLBACK TO Event;
1177
1178 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
1179 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
1180 FND_MSG_PUB.ADD;
1181
1182 WF_CORE.CONTEXT('IEX_BUS_EVENT_SUB_PVT', 'SYNC_SUMMARY', 'IEX SUMMARY', NULL);
1183
1184 RETURN 'ERROR';
1185 END UPDATE_SUMMARY;
1186
1187
1188
1189 END IEX_BUS_EVENT_SUB_PVT; -- Package spec