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