[Home] [Help]
PACKAGE BODY: APPS.IEX_DELINQUENCY_PUB
Source
1 PACKAGE BODY IEX_DELINQUENCY_PUB AS
2 /* $Header: iexpdelb.pls 120.25 2011/08/02 12:31:35 sunagesh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_DELINQUENCY_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpdelb.pls';
6 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
7
8 --
9 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
10 --
11 --G_Batch_Size NUMBER := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '1000'));
12 G_Batch_Size NUMBER := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
13 --
14 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
15 --
16
17 -- CONSTANTS FOR FINAL VALUES. (Delinquency statuses)
18 vf_delinquent CONSTANT varchar2(30) := 'DELINQUENT';
19 vf_predelinquent CONSTANT varchar2(30) := 'PREDELINQUENT';
20 vf_current CONSTANT varchar2(30) := 'CURRENT';
21
22 l_api_version_number CONSTANT NUMBER := 1.0;
23
24 v_line varchar2(100) ;
25 PG_DEBUG NUMBER ;
26
27 /*
28 || Overview: Clean up delinquency_buffers table
29 ||
30 || Parameter: None
31 ||
32 || Source Tables: None
33 ||
34 || Target Tables: IEX_DEL_BUFFERS
35 ||
36 || Creation date: 03/15/02 3:29:PM
37 ||
38 || Major Modifications: when who what
39 || 03/15/02 3:29:PM raverma created
40 */
41 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
42 --Added this function for bug#7133605 by schekuri on 17-Jun-2008 -- Added by PNAVEENK
43 FUNCTION isRefreshProgramsRunning RETURN BOOLEAN IS
44 CURSOR C1 IS
45 select request_id
46 from AR_CONC_PROCESS_REQUESTS
47 where CONCURRENT_PROGRAM_NAME in ('ARSUMREF','IEX_POPULATE_UWQ_SUM');
48 l_request_id number;
49 BEGIN
50
51 OPEN C1;
52
53 FETCH C1 INTO l_request_id;
54
55 IF C1%NOTFOUND THEN
56 return false;
57 ELSE
58 return true;
59 END IF;
60
61 CLOSE C1;
62
63 END isRefreshProgramsRunning;
64 --End PNAVEENK
65 procedure CLEAR_DEL_BUFFERS(ERRBUF OUT NOCOPY VARCHAR2,
66 RETCODE OUT NOCOPY VARCHAR2)
67
68 is
69 begin
70
71 RETCODE := 0;
72 --SAVEPOINT CLEAN_DEL_BUFFERS_PVT;
73 RETCODE := FND_API.G_RET_STS_SUCCESS;
74 -- IF PG_DEBUG < 10 THEN
75 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
76 IEX_DEBUG_PUB.logMessage('CLEAR_DEL_BUFFERS: ' || 'IEX_DEL_PUB: cleaning del buffers');
77 END IF;
78 --
79 -- Begin - 01/25/2005 - Andre Araujo - This will cause the temp tables space to blow up changing it...
80 --
81 CLEAR_BUFFERS2(-1);
82 --Delete
83 -- from IEX_DEL_BUFFERS;
84 --
85 -- End - 01/25/2005 - Andre Araujo - This will cause the temp tables space to blow up changing it...
86 --
87
88 COMMIT;
89
90 Exception
91 When others then
92 -- IF PG_DEBUG < 10 THEN
93 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
94 IEX_DEBUG_PUB.logMessage('CLEAR_DEL_BUFFERS: ' || 'IEX_DEL_PUB: cleaning failed due to ' || sqlerrm);
95 END IF;
96 RETCODE := -1;
97 ERRBUF := sqlerrm;
98
99 end CLEAR_DEL_BUFFERS;
100
101 /* This procedure will take a tbl of delinquencies and close them.
102 If the p_validate = 'Y', then the procedure will attempt to
103 validate ALL closures of delinquencies before close
104 if p_validate = 'N' then the procedure will close all delinquencies
105 without any validations.
106
107 Logic:
108 1. Call IEX_PAYMENT_BATCH_PUB.Close_Inv_Promises
109 2. Call Close Dunnings
110 3. Update IEX_DELIQUENCIES_ALL table
111 */
112 PROCEDURE Close_Delinquencies(p_api_version IN NUMBER,
113 p_init_msg_list IN VARCHAR2 ,
114 p_payments_tbl IN IEX_PAYMENTS_BATCH_PUB.CL_INV_TBL_TYPE,
115 p_security_check IN VARCHAR2,
116 x_return_status OUT NOCOPY VARCHAR2,
117 x_msg_count OUT NOCOPY NUMBER,
118 x_msg_data OUT NOCOPY VARCHAR2)
119 IS
120 l_return_status VARCHAR2(1);
121 --l_api_name VARCHAR2(50) := 'Close_Delinquencies';
122 l_api_version NUMBER := 1.0;
123 l_msg_count NUMBER;
124 l_msg_data VARCHAR2(4000);
125
126 l_num_payments NUMBER;
127 l_del_tbl IEX_DUNNING_PUB.DELID_NUMLIST;
128 l_del_id number;
129 nCount number;
130 l_delinquency_tbl IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE;
131
132 i NUMBER;
133 j NUMBER;
134
135 BEGIN
136 NULL;
137 /*
138 -- Standard Start of API savepoint
139 SAVEPOINT Close_Delinquencies_PVT;
140
141 -- Initialize API return status to SUCCESS
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143
144
145 -- Standard call to check for call compatibility.
146 IF NOT FND_API.Compatible_API_Call (l_api_version,
147 p_api_version,
148 l_api_name,
149 G_PKG_NAME)
150 THEN
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
152 END IF;
153
154 -- Initialize message list if p_init_msg_list is set to TRUE.
155 IF FND_API.to_Boolean(p_init_msg_list)
156 THEN
157 FND_MSG_PUB.initialize;
158 END IF;
159
160 -- Debug Message
161 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'PVT: ' || l_api_name || ' start');
162
163 --
164 -- API body
165 --
166
167 l_num_payments := p_payments_tbl.count;
168
169 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Calling Process Inv Payments');
170 IEX_PROMISES_BATCH_PUB.CLOSE_PROMISES(P_API_VERSION => l_api_version,
171 P_INIT_MSG_LIST => FND_API.G_FALSE,
172 P_COMMIT => FND_API.G_TRUE,
173 P_VALIDATION_LEVEL => NULL,
174 X_RETURN_STATUS => l_return_status,
175 X_MSG_COUNT => l_msg_count,
176 X_MSG_DATA => l_msg_data,
177 P_PAYMENTS_TBL => p_payments_tbl);
178
179 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Process Inv Payments returns ' || l_return_status);
180
181 IF l_return_status <> 'S' THEN
182 NULL;
183 -- log error
184 END IF;
185
186 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'pay count is ' || l_num_payments);
187
188 -- get all delinquency IDs to be closed
189 IF l_num_payments >= 1 THEN
190 FOR i in 1..l_num_payments LOOP
191
192 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'before open del cursor');
193 select delinquency_id into l_del_id
194 from iex_delinquencies
195 where payment_schedule_id = p_payments_tbl(i);
196
197 l_del_tbl(i) := l_del_id;
198
199 END LOOP;
200 ELSE
201 NULL;
202 END IF;
203
204 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'callling close dunnings');
205
206 -- here we will call crystal's API to Close_Dunnings
207 IEX_DUNNING_PUB.Close_Dunning(p_api_version => l_api_version
208 ,p_init_msg_list => FND_API.G_FALSE
209 ,p_commit => FND_API.G_TRUE
210 ,p_delinquencies_tbl => l_del_tbl
211 ,p_security_check => 'N'
212 ,x_return_status => l_return_status
213 ,x_msg_count => l_msg_count
214 ,x_msg_data => l_msg_data);
215
216 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Close dunning returns ' || l_return_status);
217
218 IF l_return_status <> 'S' THEN
219 NULL;
220 -- log error
221 END IF;
222
223 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'before update');
224
225 -- now update the delinquencies table
226 nCount := l_del_tbl.count;
227
228 if nCount >= 1 then
229 FORALL j in 1..nCount
230 UPDATE IEX_DELINQUENCIES_ALL
231 SET STATUS='CLOSE',
232 DUNN_YN='N',
233 LAST_UPDATE_DATE=sysdate
234 WHERE DELINQUENCY_ID = l_del_tbl(j);
235
236 end if;
237
238 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'after update');
239 COMMIT;
240
241 x_return_status := l_return_status;
242
243 --
244 -- End of API body
245 --
246
247 -- Debug Message
248 IEX_DEBUG_PUB.LogMessage(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'PVT: ' || l_api_name || ' end');
249
250 -- Standard call to get message count and if count is 1, get message info.
251 FND_MSG_PUB.Count_And_Get
252 ( p_count => x_msg_count,
253 p_data => x_msg_data
254 );
255
256 EXCEPTION
257 WHEN FND_API.G_EXC_ERROR THEN
258 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
259 P_API_NAME => L_API_NAME
260 ,P_PKG_NAME => G_PKG_NAME
261 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
262 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
263 ,X_MSG_COUNT => X_MSG_COUNT
264 ,X_MSG_DATA => X_MSG_DATA
265 ,X_RETURN_STATUS => X_RETURN_STATUS);
266
267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
268 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
269 P_API_NAME => L_API_NAME
270 ,P_PKG_NAME => G_PKG_NAME
271 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
272 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
273 ,X_MSG_COUNT => X_MSG_COUNT
274 ,X_MSG_DATA => X_MSG_DATA
275 ,X_RETURN_STATUS => X_RETURN_STATUS);
276
277 WHEN OTHERS THEN
278 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
279 P_API_NAME => L_API_NAME
280 ,P_PKG_NAME => G_PKG_NAME
281 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
282 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
283 ,X_MSG_COUNT => X_MSG_COUNT
284 ,X_MSG_DATA => X_MSG_DATA
285 ,X_RETURN_STATUS => X_RETURN_STATUS);
286 */
287 END Close_Delinquencies;
288
289
290 /** -jsanju 09/15/05
291 * obsolete the concurrent pgm and raise the event
292 * for every concurrent request launced to determine delinquencies.
293 *Raises an event for every concurrent request
294 *parameters passed to the event, so that other team can subscribe to that
295 *REQUEST_ID -- request id of the concurrent program which created/update the
296 --delinquencies
297 *NOOFDELCREATED -number of delinquencies created
298 *NOOFDELUPDATED -number of delinquencies updated
299 **/
300
301 PROCEDURE RAISE_EVENT(
302 P_REQUEST_ID IN NUMBER,
303 p_del_create_count IN NUMBER,
304 p_del_update_count IN NUMBER,
305 X_Return_Status OUT NOCOPY VARCHAR2,
306 X_Msg_Count OUT NOCOPY NUMBER,
307 X_Msg_Data OUT NOCOPY VARCHAR2) IS
308
309
310
311 l_parameter_list wf_parameter_list_t;
312 l_key VARCHAR2(240);
313 l_seq NUMBER;
314 l_event_name varchar2(240) := 'oracle.apps.iex.delinquency.create';
315 l_evt_ctr NUMBER ;
316
317 l_request_id NUMBER;
318 l_del_create_count NUMBER;
319 l_del_update_count NUMBER;
320 l_return_status VARCHAR2(1);
321 BEGIN
322
323 -- Initialize API return status to SUCCESS
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326 l_request_id :=p_request_id;
327 l_del_create_count := p_del_create_count ;
328 l_del_update_count := p_del_update_count ;
329 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
330 IEX_DEBUG_PUB.logMessage( 'Start Raise Delinquency Event Concurrent program');
331 IEX_DEBUG_PUB.logMessage('Program Run Date:'||SYSDATE);
332 END IF;
333
334
335 select iex_del_wf_s.nextval INTO l_seq from dual;
336 l_key := l_event_name ||'-'||l_request_id || '-'||l_seq;
337 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
338 IEX_DEBUG_PUB.logMessage(' EVENT KEY ' ||l_key );
339 IEX_DEBUG_PUB.logMessage(
340 ' request_id =' ||l_request_id
341 ||'No of Del Created = ' ||l_del_create_count
342 ||'No of Del Updated =' ||l_del_update_count
343 );
344
345 END IF;
346
347
348 wf_event.AddParameterToList('REQUEST_ID',
349 to_char(l_request_id),
350 l_parameter_list);
351 wf_event.AddParameterToList('NOOFDELCREATED',
352 to_char(l_del_create_count),
353 l_parameter_list);
354 wf_event.AddParameterToList('NOOFDELUPDATED',
355 to_char(l_del_update_count),
356 l_parameter_list);
357
358 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
359 IEX_DEBUG_PUB.logMessage(' Before Launching Event ');
360 END IF;
361
362 wf_event.raise(p_event_name => l_event_name
363 ,p_event_key => l_key
364 ,p_parameters => l_parameter_list);
365
366 COMMIT ;
367
368 l_parameter_list.DELETE;
369
370 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
371 IEX_DEBUG_PUB.logMessage( 'End Raise Delinquency Event program');
372 END IF;
373
374 -- Standard call to get message count and if count is 1, get message info.
375 FND_MSG_PUB.Count_And_Get
376 ( p_count => x_msg_count,
377 p_data => x_msg_data
378 );
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
383 IEX_DEBUG_PUB.logMessage('Raise Delinquency Event Concurrent program raised exception '
384 || sqlerrm);
385 END IF;
386 x_msg_count := 1 ;
387 x_msg_data := sqlerrm ;
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
390 p_data => x_msg_data);
391
392 END RAISE_EVENT;
393
394
395
396 /*------------------------------------------------------------------------
397 ------------------------------------------------------------------------
398 11.5.7 Modified Delinquency Creation Process
399 ------------------------------------------------------------------------
400 ------------------------------------------------------------------------ */
401 PROCEDURE MANAGE_DELINQUENCIES (ERRBUF OUT NOCOPY VARCHAR2,
402 RETCODE OUT NOCOPY VARCHAR2,
403 p_request_id IN Number)
404 IS
405
406 -- All Delinquency Declarations
407 vt_del_id IEX_UTILITIES.t_del_id;
408 vt_del_status t_buf_status;
409 vt_buf_status t_buf_status;
410 vt_pmt_schd_id IEX_UTILITIES.t_numbers ;
411 vt_case_id IEX_UTILITIES.t_numbers ;
412 vt_contract_id IEX_UTILITIES.t_numbers ;
413 vt_wf_del_id IEX_UTILITIES.t_del_id;
414
415 v_today date;
416 v_object IEX_DEL_BUFFERS.SCORE_OBJECT_CODE%TYPE;
417 v_score Number;
418
419 -- 0 Indicates non existence of range and 1 indicates the existence
420 v_score_range Number := 0 ;
421
422 v_org_id iex_delinquencies_all.org_id%TYPE;
423 v_user_id Number;
424 v_count Number := 1;
425
426 -- Debug Variables. Remove after unit testing
427 i number;
428
429
430 -- standard Stuff
431 l_return_status VARCHAR2(10);
432 l_msg_count NUMBER;
433 l_msg_data VARCHAR2(1000);
434
435 l_source_module VARCHAR2(100) ;
436
437 -- Added for bug fix 3090360
438 l_enable_business_events varchar2(10) ;
439 l_business_event_req_id Number ;
440
441 l_del_insert_count Number := 0 ;
442 l_del_update_count Number := 0 ;
443
444 /***************************************************************
445 Debug Declarations
446 ****************************************************************/
447 v_debug_level Number := 20 ;
448
449 CURSOR dbg_test
450 is
451 SELECT
452 HZCA.cust_Account_id,
453 ARPS.customer_trx_id,
454 IDB.score_object_id,
455 IDS.del_status
456 FROM HZ_CUST_ACCOUNTS HZCA,
457 IEX_DEL_BUFFERS IDB,
458 AR_PAYMENT_SCHEDULES ARPS,
459 IEX_DEL_STATUSES IDS
460 WHERE
461 NOT EXISTS
462 (Select 1
463 from iex_delinquencies
464 where payment_schedule_id = idb.score_object_id)
465 AND NOT EXISTS
466 (select 1
467 from dual
468 where IDS.del_status = vf_current)
469 AND HZCA.cust_account_id = ARPS.customer_id
470 AND ARPS.payment_schedule_id = IDB.score_object_id
471 AND IDB.score_value between
472 IDS.score_value_low and IDS.score_value_high
473 AND IDB.score_id = IDS.score_id
474 AND IDB.request_id = p_request_id
475 ORDER By IDB.score_object_id;
476
477 dbg_cust Number;
478 dbg_trx Number;
479 dbg_object_id Number;
480 dbg_status varchar2(30);
481
482 --
483 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
484 --
485 Cursor c_scores(p_request_id NUMBER, p_delinquent varchar2, p_predelinquent varchar2) is
486 SELECT
487 id.delinquency_id,
488 ids.del_status buf_status,
489 id.status del_status,
490 id.payment_schedule_id
491 FROM iex_delinquencies id,
492 iex_del_buffers idb,
493 iex_del_statuses ids
494 where NOT EXISTS
495 (select 1
496 from dual
497 where id.status = ids.del_status)
498 and idb.score_id = ids.score_id
499 and idb.score_value between ids.score_value_low
500 and ids.score_value_high
501 and idb.score_object_id = id.payment_schedule_id
502 and idb.request_id = p_request_id;
503
504 --
505 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
506 --
507 --Bug5237039. Fix By LKKUMAR on 28-Aug-2006. Start.
508 CURSOR c_cust_account_id_1 IS
509 SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS,
510 AR_SYSTEM_PARAMETERS ARP
511 WHERE ARS.REFERENCE_1 IS Null
512 AND ARS.ORG_ID = ARP.ORG_ID
513 AND EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
514 IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
515 AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID
516 AND ARS.ORG_ID = IED.ORG_ID);
517
518 CURSOR c_cust_account_id_n IS
519 SELECT DISTINCT CUST_ACCOUNT_ID FROM AR_TRX_BAL_SUMMARY ARS,
520 AR_SYSTEM_PARAMETERS ARP
521 WHERE ARS.REFERENCE_1 = 1
522 AND ARS.ORG_ID = ARP.ORG_ID
523 AND NOT EXISTS (SELECT 1 FROM IEX_DELINQUENCIES_ALL IED WHERE
524 IED.STATUS IN ('DELINQUENT', 'PREDELINQUENT')
525 AND ARS.CUST_ACCOUNT_ID = IED.CUST_ACCOUNT_ID
526 AND ARS.ORG_ID = IED.ORG_ID);
527
528 TYPE cust_account_id_list_1 is TABLE of NUMBER INDEX BY BINARY_INTEGER;
529 TYPE cust_account_id_list_n is TABLE of NUMBER INDEX BY BINARY_INTEGER;
530
531 l_cust_account_id_1 cust_account_id_list_1;
532 l_cust_account_id_n cust_account_id_list_n;
533 --Bug5237039. Fix By LKKUMAR on 28-Aug-2006. End.
534
535
536 --jsanju 09/19/05 , set concurrent status to 'WARNING if business event fails'
537 request_status BOOLEAN;
538 x_errbuf varchar2(240);
539 x_retcode varchar2(240);
540 l_noof_active_busi_events number; --added for bug 10037776 sunagesh 01.08.11
541 BEGIN
542
543
544
545 l_source_module := 'IEX_SCORE_NEW_PVT' ;
546 l_enable_business_events := FND_PROFILE.VALUE('IEX_ENABLE_CUST_STATUS_EVENT') ;
547
548 -- IF PG_DEBUG < 10 THEN
549 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
550 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
551 END IF;
552 -- IF PG_DEBUG < 10 THEN
553 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
554 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES Start <<----------');
555 END IF;
556 -- IF PG_DEBUG < 10 THEN
557 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
558 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
559 END IF;
560 -- IF PG_DEBUG < 10 THEN
561 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
562 IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
563 END IF;
564
565
566 RETCODE := 0 ;
567 SAVEPOINT del_sp ;
568
569 -- Perform Updates First
570 -- Get all the Existing Delinquency Statuses that are Pre-Delinquent
571 -- A Direct Update can be performed over them.
572
573 -- Selects all the existing delinquencies for a particular scoring Engine
574 -- that are having the Status PREDELINQUENT or the same status as before.
575 -- This way All the newly found statuses can be updated without any
576 -- verifications.
577
578 Begin
579 -- IF PG_DEBUG < 10 THEN
580 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
581 IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'finding bridge ' || p_request_id);
582 END IF;
583 SELECT score_object_code,
584 score_id
585 INTO v_object,
586 v_score
587 FROM iex_del_buffers
588 WHERE request_id = p_request_id
589 AND rownum = 1 ;
590
591 -- IF PG_DEBUG < 10 THEN
592 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
593 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
594 END IF;
595 -- IF PG_DEBUG < 10 THEN
596 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
597 IEX_DEBUG_PUB.LOGMESSAGE
598 ('MANAGE_DELINQUENCIES: ' || 'Object >> ' || v_object || ' Score Id >> ' || v_score);
599 END IF;
600 -- IF PG_DEBUG < 10 THEN
601 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
602 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
603 END IF;
604 Exception
605 WHEN NO_DATA_FOUND then
606 ERRBUF := ' No Data Found on IEX_DEL_BUFFERS Table for the Passed Request Id...> '
607 || to_char(p_Request_id) ;
608 RETCODE := 1;
609 -- IF PG_DEBUG < 10 THEN
610 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
611 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
612 END IF;
613 ROLLBACK TO del_sp;
614
615 -- IF PG_DEBUG < 10 THEN
616 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
617 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
618 END IF;
619 -- IF PG_DEBUG < 10 THEN
620 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
621 IEX_DEBUG_PUB.LOGMESSAGE
622 ('---------->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
623 END IF;
624 -- IF PG_DEBUG < 10 THEN
625 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
626 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
627 END IF;
628 return ;
629 End ;
630
631 -- IF PG_DEBUG < 10 THEN
632 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
633 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Score Id = ' || v_score || ' Object Code = ' || v_object);
634 END IF;
635
636 Select --fnd_profile.value('ORG_ID'), --Commneted for MOAC
637 fnd_profile.value('USER_ID'),
638 fnd_profile.value('IEX_DEBUG_LEVEL'),
639 sysdate
640 into --v_org_id, --Commneted for MOAC
641 v_user_id,
642 v_debug_level,
643 v_today
644 From dual ;
645
646 Select count(1)
647 into v_score_range
648 from iex_del_statuses
649 where score_id = v_score ;
650
651 -- IF PG_DEBUG < 10 THEN
652 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
653 IEX_DEBUG_PUB.LOGMESSAGE
654 ('MANAGE_DELINQUENCIES: ' || 'Org Id = ' || v_org_id || ' Score Range Count = ' || v_score_range);
655 END IF;
656 -- IF PG_DEBUG < 10 THEN
657 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
658 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Debug Level = ' || to_char(v_debug_level));
659 END IF;
660 -- IF PG_DEBUG < 10 THEN
661 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
662 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
663 END IF;
664
665 -- Everything works only when Score Range is Defined.
666 If v_score_range > 0 then
667 If v_object = 'IEX_INVOICES' then
668
669 -- Selects All the matching rows between buffer and Delinquencies table
670 -- except when the statuses are same or when buffer status is PREDELINQUENT
671 -- and delinquency table status is DELINQUENT.
672 --
673 -- Begin - 01/24/2005 - Andre Araujo - This memory schema uses up all memory available for the session, changing it to chunks
674 --
675 open c_scores(p_request_id, vf_delinquent, vf_predelinquent);
676 LOOP
677 FETCH c_scores
678 BULK COLLECT INTO
679 vt_del_id,
680 vt_buf_status,
681 vt_del_status,
682 vt_pmt_schd_id
683 LIMIT G_Batch_Size;
684
685 BEGIN
686
687 /* 01/24/2005 - Andre Araujo - This memory schema uses up all memory available for the session, changing it to chunks
688 SELECT
689 id.delinquency_id,
690 ids.del_status buf_status,
691 id.status del_status,
692 id.payment_schedule_id
693 BULK COLLECT INTO
694 vt_del_id,
695 vt_buf_status,
696 vt_del_status,
697 vt_pmt_schd_id
698 FROM iex_delinquencies id,
699 iex_del_buffers idb,
700 iex_del_statuses ids
701 where NOT EXISTS
702 (select 1
703 from dual
704 -- Begin - Andre Araujo - 12/21/2004 - Remove the pre-del 2 del constraint bug#4072687
705 --where (id.status = vf_delinquent
706 -- and ids.del_status = vf_predelinquent)
707 -- OR id.status = ids.del_status)
708 where id.status = ids.del_status)
709 -- End - Andre Araujo - 12/21/2004 - Remove the pre-del 2 del constraint bug#4072687
710 and idb.score_id = ids.score_id
711 and idb.score_value between ids.score_value_low
712 and ids.score_value_high
713 and idb.score_object_id = id.payment_schedule_id
714 and idb.request_id = p_request_id;
715
716 */
717 --
718 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
719 --
720
721
722 /* ================= Debug Message ====================*/
723 -- IF PG_DEBUG < 10 THEN
724 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
725 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Row Count after Update Select ');
726 END IF;
727 -- IF PG_DEBUG < 10 THEN
728 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
729 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
730 END IF;
731
732 if v_debug_level <= 10 then
733 for i in 1..vt_del_id.count
734 LOOP
735 -- IF PG_DEBUG < 10 THEN
736 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
737 IEX_DEBUG_PUB.LOGMESSAGE
738 ('MANAGE_DELINQUENCIES: ' || ' Pmt Schd Id = ' || to_char(vt_pmt_schd_id(i))||
739 ' Delinquency Id = ' || to_char(vt_del_id(i)) ||
740 ' Buf Status = ' || vt_buf_status(i) ||
741 'Del Status = ' || vt_del_status(i));
742 END IF;
743 END LOOP ;
744 End If ;
745 -- IF PG_DEBUG < 10 THEN
746 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
747 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
748 END IF;
749 /*================= Debug Message ====================== */
750
751 Exception
752 WHEN OTHERS then
753 ERRBUF := ' FIRST SELECT - Error Code = ' || SQLCODE ||
754 ' Error Msg ' || SQLERRM ;
755 RETCODE := -1;
756 -- IF PG_DEBUG < 10 THEN
757 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
758 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
759 END IF;
760 ROLLBACK TO del_sp;
761
762 -- IF PG_DEBUG < 10 THEN
763 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
764 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
765 END IF;
766
767
768 --
769 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
770 --
771 CLEAR_BUFFERS2(p_request_id);
772 --DELETE FROM IEX_DEL_BUFFERS
773 --WHERE request_id = p_request_id;
774
775 --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF);
776 --
777 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
778 --
779
780
781 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
782 -- IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF);
783 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
784
785 -- IF PG_DEBUG < 10 THEN
786 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
787 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> '||to_char(SQL%ROWCOUNT));
788 END IF;
789 -- IF PG_DEBUG < 10 THEN
790 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
791 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
792 END IF;
793 -- IF PG_DEBUG < 10 THEN
794 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
795 IEX_DEBUG_PUB.LOGMESSAGE
796 ('--->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
797 END IF;
798 -- IF PG_DEBUG < 10 THEN
799 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
800 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
801 END IF;
802
803 Commit;
804 return;
805 End;
806
807 Begin
808 -- Once all the Existing PreDelinquent Rows are found then Update them
809 -- UPDATE Phase 1
810 FORALL v_count in 1..vt_del_id.count
811 UPDATE IEX_DELINQUENCIES
812 SET status = vt_buf_status(v_count),
813 last_update_date = v_today,
814 last_updated_by = v_user_id,
815 dunn_yn = decode(vt_buf_status(v_count), vf_current, 'N'),
816 object_version_number = object_version_number + 1,
817 request_id = p_request_id
818 WHERE delinquency_id = vt_del_id(v_count);
819
820 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Delinquencies Updated..>> '|| vt_del_id.count) ;
821 l_del_update_count := vt_del_id.count ;
822
823 /* ================= Debug Message ================== */
824 -- IF PG_DEBUG < 10 THEN
825 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
826 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
827 END IF;
828 if v_debug_level <= 10 then
829 FOR i in 1..vt_del_id.count
830 LOOP
831 -- IF PG_DEBUG < 10 THEN
832 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
833 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' ||
834 to_char(vt_del_id(i)) || ' is ' ||
835 to_char(SQL%BULK_ROWCOUNT(i)));
836 END IF;
837 END LOOP ;
838 End If ;
839 -- IF PG_DEBUG < 10 THEN
840 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
841 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
842 END IF;
843 /* ================= Debug Message =========================== */
844
845 Exception
846 WHEN OTHERS then
847
848 ERRBUF := 'INVOICE - Matching Delinquencies Update --> Error Code '
849 || SQLCODE || ' Error Mesg ' || SQLERRM ;
850 RETCODE := -1;
851 -- IF PG_DEBUG < 10 THEN
852 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
853 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
854 END IF;
855 ROLLBACK TO del_sp ;
856
857 -- IF PG_DEBUG < 10 THEN
858 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
859 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
860 END IF;
861
862 --
863 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
864 --
865
866 CLEAR_BUFFERS2(p_request_id);
867
868 --DELETE FROM IEX_DEL_BUFFERS
869 --WHERE request_id = p_request_id ;
870
871 --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
872 --
873 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
874 --
875
876 -- IF PG_DEBUG < 10 THEN
877 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
878 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleted Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
879 END IF;
880 -- IF PG_DEBUG < 10 THEN
881 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
882 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
883 END IF;
884 -- IF PG_DEBUG < 10 THEN
885 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
886 IEX_DEBUG_PUB.LOGMESSAGE('--->> PROCEDURE MANAGE_DELINQUENCIES End (returned) <<--------');
887 END IF;
888 -- IF PG_DEBUG < 10 THEN
889 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
890 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
891 END IF;
892
893 Commit;
894 return;
895 End;
896
897
898 /* ================= Debug Message ===========================*/
899 Begin
900 if v_debug_level < 11 then
901 Open dbg_test ;
902
903 -- IF PG_DEBUG < 10 THEN
904 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
905 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------- Insert Candidate Rows ----------');
906 END IF;
907 -- IF PG_DEBUG < 10 THEN
908 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
909 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '----cust----pmt schd----status----');
910 END IF;
911
912 LOOP
913 FETCH dbg_test
914 into
915 dbg_cust ,
916 dbg_trx ,
917 dbg_object_id ,
918 dbg_status ;
919
920 EXIT WHEN dbg_test%NOTFOUND ;
921
922 -- IF PG_DEBUG < 10 THEN
923 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
924 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || to_char(dbg_cust) || ' ---- ' ||
925 to_char(dbg_object_id) || ' ---- ' ||dbg_status);
926 END IF;
927 END LOOP ;
928
929 CLOSE dbg_test ;
930 End If ;
931 Exception
932 when others then
933 close dbg_test ;
934 End ;
935 /* ================= Debug Message =========================*/
936
937
938 -- clchang updated 04/18/2003 for BILL_TO
939 -- get customer_site_use_id from ar_payment_schedules
940
941 Begin
942 -- Simple Insert for Payment Schedule Id
943 INSERT INTO IEX_DELINQUENCIES_ALL
944 ( DELINQUENCY_ID ,
945 LAST_UPDATE_DATE ,
946 LAST_UPDATED_BY ,
947 CREATION_DATE ,
948 CREATED_BY ,
949 OBJECT_VERSION_NUMBER ,
950 DUNN_YN ,
951 PARTY_CUST_ID ,
952 CUST_ACCOUNT_ID ,
953 CUSTOMER_SITE_USE_ID , -- added by clchang for bill_to
954 TRANSACTION_ID ,
955 PAYMENT_SCHEDULE_ID ,
956 STATUS ,
957 ORG_ID ,
958 SOURCE_PROGRAM_NAME ,
959 SCORE_ID ,
960 SCORE_VALUE ,
961 REQUEST_ID )
962 SELECT
963 IEX_DELINQUENCIES_S.NEXTVAL ,
964 v_today,
965 v_user_id,
966 v_today,
967 v_user_id,
968 1 ,
969 'Y' ,
970 HZCA.party_id ,
971 HZCA.cust_Account_id ,
972 ARPS.customer_site_use_id , -- added by clchang for bill_to
973 ARPS.customer_trx_id ,
974 IDB.score_object_id ,
975 IDS.del_status ,
976 -- v_org_id ,
977 --jsanju for bug 3581105
978 --get payment schedule org ID
979 ARPS.org_id,
980 l_source_module ,
981 IDB.score_id ,
982 IDB.score_value ,
983 p_Request_id
984 FROM HZ_CUST_ACCOUNTS HZCA ,
985 IEX_DEL_BUFFERS IDB ,
986 AR_PAYMENT_SCHEDULES ARPS ,
987 IEX_DEL_STATUSES IDS
988 WHERE
989 NOT EXISTS
990 (Select 1
991 from iex_delinquencies_all --added by barathsr for bug#7366451 10-Oct-08
992 --iex_delinquencies
993 where payment_schedule_id = idb.score_object_id)
994 -- start bug 9794774 sunagesh 11 jul 2011
995 /* AND NOT EXISTS
996 (select 1
997 from dual
998 where IDS.del_status = vf_current) */
999 AND IDS.del_status <> vf_current
1000 -- end bug 9794774 sunagesh 11 jul 2011
1001 AND HZCA.cust_account_id = ARPS.customer_id
1002 AND ARPS.payment_schedule_id = IDB.score_object_id
1003 AND IDB.score_value between
1004 IDS.score_value_low and IDS.score_value_high
1005 AND IDB.score_id = IDS.score_id
1006 AND IDB.request_id = p_request_id ;
1007
1008
1009 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Delinquencies Created..>> '|| SQL%ROWCOUNT) ;
1010 l_del_insert_count := SQL%ROWCOUNT ;
1011
1012 /* ================= Debug Message ==================== */
1013 -- IF PG_DEBUG < 10 THEN
1014 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1015 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1016 END IF;
1017 -- IF PG_DEBUG < 10 THEN
1018 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1019 IEX_DEBUG_PUB.LOGMESSAGE
1020 ('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1021 END IF;
1022 -- IF PG_DEBUG < 10 THEN
1023 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1024 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1025 END IF;
1026 /* ================= Debug Message ==================== */
1027
1028 Exception
1029 WHEN OTHERS then
1030 ERRBUF := 'INSERT - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1031 -- IF PG_DEBUG < 10 THEN
1032 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1033 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1034 END IF;
1035 RETCODE := -1 ;
1036 ROLLBACK TO del_sp ;
1037
1038 -- IF PG_DEBUG < 10 THEN
1039 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1040 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1041 END IF;
1042
1043 --
1044 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1045 --
1046
1047 CLEAR_BUFFERS2(p_request_id);
1048
1049 --DELETE FROM IEX_DEL_BUFFERS
1050 --WHERE request_id = p_request_id ;
1051 --
1052 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1053 --
1054
1055 -- IF PG_DEBUG < 10 THEN
1056 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1057 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' ||
1058 to_char(SQL%ROWCOUNT));
1059 END IF;
1060 Commit;
1061 return;
1062 End;
1063
1064 --
1065 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1066 --
1067 EXIT WHEN c_scores%NOTFOUND;
1068 END LOOP;
1069 close c_scores;
1070 --
1071 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1072 --
1073
1074 --ELSE
1075 elsif v_object = 'IEX_CONTRACTS' then
1076
1077 begin
1078
1079 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1080 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_CONTRACTS: ' || 'Starting Contract..... ');
1081 END IF;
1082
1083 SELECT ico.object_id, ids.del_status, ico.delinquency_status
1084 BULK COLLECT INTO vt_contract_id, vt_buf_status, vt_del_status
1085 FROM iex_case_objects ico,
1086 iex_del_buffers idb,
1087 iex_del_statuses ids
1088 WHERE idb.score_id = ids.score_id
1089 AND idb.score_value BETWEEN ids.score_value_low and ids.score_value_high
1090 AND idb.score_object_id = ico.object_id
1091 AND ico.object_code = 'CONTRACTS'
1092 AND idb.request_id = p_request_id;
1093 null;
1094
1095 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1096 IEX_DEBUG_PUB.LOGMESSAGE
1097 ('MANAGE_DELINQUENCIES: ' || 'CONTRACT - Row Count after Update Select ' || to_char(vt_contract_id.COUNT));
1098 END IF;
1099
1100 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1101 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1102 END IF;
1103
1104 if v_debug_level < 11 then
1105 for i in 1..vt_del_id.count
1106 LOOP
1107
1108 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1109 IEX_DEBUG_PUB.LOGMESSAGE
1110 ('MANAGE_DELINQUENCIES: ' || ' CONTRACT Id = ' || to_char(vt_contract_id(i))||
1111 ' Delinquency Id = ' || to_char(vt_del_id(i)) ||
1112 ' Buf Status = ' || vt_buf_status(i) ||
1113 'Del Status = ' || vt_del_status(i));
1114 END IF;
1115 END LOOP ;
1116 End If ;
1117
1118 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1119 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1120 END IF;
1121
1122 EXCEPTION
1123 WHEN OTHERS THEN
1124 ERRBUF := 'CONTRACT - Matching Delinquencies Select -->' || SQLCODE || ' Error Msg ' || SQLERRM ;
1125
1126 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1127 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1128 END IF;
1129 RETCODE := -1 ;
1130
1131 ROLLBACK TO del_sp ;
1132
1133 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1134 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1135 END IF;
1136
1137 Commit;
1138 return;
1139 end;
1140
1141 begin
1142
1143 FORALL v_count in 1..vt_contract_id.count
1144 UPDATE IEX_CASE_OBJECTS
1145 SET delinquency_status = vt_buf_status(v_count),
1146 last_update_date = v_today,
1147 last_updated_by = v_user_id,
1148 object_version_number = object_version_number + 1,
1149 request_id = p_request_id
1150 WHERE object_id = vt_contract_id(v_count);
1151
1152 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Contracts Updated..>> '|| vt_contract_id.count) ;
1153 l_del_update_count := vt_del_id.count ;
1154
1155 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1156 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1157 END IF;
1158 if v_debug_level < 11 then
1159 FOR i in 1..vt_del_id.count
1160 LOOP
1161
1162 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1163 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' || to_char(vt_del_id(i)) || ' is ' ||
1164 to_char(SQL%BULK_ROWCOUNT(i)));
1165 END IF;
1166 END LOOP ;
1167 End If ;
1168
1169 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1170 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1171 END IF;
1172
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 ERRBUF := 'CONTRACT Updating... - Matching Delinquencies Update --> ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1176
1177 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1178 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1179 END IF;
1180 RETCODE := -1;
1181 ROLLBACK TO del_sp;
1182
1183 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1184 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1185 END IF;
1186 CLEAR_BUFFERS2(p_request_id);
1187 Commit;
1188 return;
1189 end;
1190
1191 elsif v_object = 'IEX_CASES' then
1192 /* *******************************************************************
1193 HANDLING FOR CASE
1194 ******************************************************************* */
1195 BEGIN
1196
1197 -- IF PG_DEBUG < 10 THEN
1198 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1199 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Starting Cases ');
1200 END IF;
1201
1202 -- Selects All the matching rows between buffer and Delinquencies table
1203 -- except when the statuses are same or when buffer status is PREDELINQUENT
1204 -- and delinquency table status is DELINQUENT.
1205 /* 6785378
1206 SELECT
1207 id.delinquency_id,
1208 ids.del_status buf_status,
1209 id.status del_status,
1210 id.case_id
1211 BULK COLLECT INTO
1212 vt_del_id,
1213 vt_buf_status,
1214 vt_del_status,
1215 vt_case_id
1216 FROM iex_delinquencies id,
1217 iex_del_buffers idb,
1218 iex_del_statuses ids
1219 where
1220 NOT EXISTS
1221 (select 1
1222 from dual
1223 where (id.status = vf_delinquent and ids.del_status = vf_predelinquent)
1224 OR id.status = ids.del_status)
1225 and idb.score_id = ids.score_id
1226 and idb.score_value between ids.score_value_low
1227 and ids.score_value_high
1228 and idb.score_object_id = id.case_id
1229 and idb.request_id = p_request_id ;
1230 */
1231 SELECT ic.cas_id, ids.del_status, ic.status_code
1232 BULK COLLECT INTO vt_case_id, vt_buf_status, vt_del_status
1233 FROM iex_cases_all_b ic,
1234 iex_del_buffers idb,
1235 iex_del_statuses ids
1236 WHERE idb.score_id = ids.score_id
1237 AND idb.score_value BETWEEN ids.score_value_low and ids.score_value_high
1238 AND idb.score_object_id = ic.cas_id
1239 AND idb.request_id = p_request_id;
1240 /* ================= Debug Message ====================*/
1241
1242 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1243 IEX_DEBUG_PUB.LOGMESSAGE
1244 -- ('MANAGE_DELINQUENCIES: ' || 'CASE - Row Count after Update Select ' || to_char(vt_del_id.COUNT));
1245 ('MANAGE_DELINQUENCIES: ' || 'CASE - Row Count after Update Select ' || to_char(vt_case_id.COUNT));
1246 END IF;
1247
1248 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1249 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1250 END IF;
1251
1252 if v_debug_level < 11 then
1253 for i in 1..vt_del_id.count
1254 LOOP
1255
1256 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1257 IEX_DEBUG_PUB.LOGMESSAGE
1258 ('MANAGE_DELINQUENCIES: ' || ' Case Id = ' || to_char(vt_case_id(i))||
1259 ' Delinquency Id = ' || to_char(vt_del_id(i)) ||
1260 ' Buf Status = ' || vt_buf_status(i) ||
1261 'Del Status = ' || vt_del_status(i));
1262 END IF;
1263 END LOOP ;
1264 End If ;
1265
1266 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1267 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1268 END IF;
1269 /*================= Debug Message ====================== */
1270
1271 EXCEPTION
1272 WHEN OTHERS THEN
1273 ERRBUF := 'CASE - Matching Delinquencies Select -->' || SQLCODE || ' Error Msg ' || SQLERRM ;
1274
1275 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1276 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1277 END IF;
1278 RETCODE := -1 ;
1279
1280 ROLLBACK TO del_sp ;
1281
1282 --
1283 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1284 --
1285
1286 CLEAR_BUFFERS2(p_request_id);
1287
1288 --DELETE FROM IEX_DEL_BUFFERS
1289 --WHERE request_id = p_request_id ;
1290 --
1291 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1292 --
1293
1294 -- IF PG_DEBUG < 10 THEN
1295 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1296 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1297 END IF;
1298
1299 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1300 -- IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
1301 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1302
1303 Commit;
1304 return;
1305 END;
1306
1307 BEGIN
1308 -- Once all the Existing PreDelinquent Rows are found then
1309 -- Update them
1310 /* 6785378
1311 FORALL v_count in 1..vt_del_id.count
1312 UPDATE IEX_DELINQUENCIES
1313 SET status = vt_buf_status(v_count),
1314 last_update_date = v_today,
1315 last_updated_by = v_user_id,
1316 dunn_yn = decode(vt_buf_status(v_count), vf_current, 'N'),
1317 object_version_number = object_version_number + 1,
1318 request_id = p_request_id
1319 WHERE delinquency_id = vt_del_id(v_count);
1320 */
1321 FORALL v_count in 1..vt_case_id.count
1322 UPDATE IEX_CASES_ALL_B
1323 SET status_code = vt_buf_status(v_count),
1324 last_update_date = v_today,
1325 last_updated_by = v_user_id,
1326 object_version_number = object_version_number + 1,
1327 request_id = p_request_id
1328 WHERE cas_id = vt_case_id(v_count);
1329
1330
1331 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Delinquencies Updated..>> '|| vt_del_id.count) ;
1332 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Cases Updated..>> '|| vt_case_id.count) ; -- 6785378
1333 l_del_update_count := vt_del_id.count ;
1334
1335 /* ================= Debug Message =========================*/
1336 -- IF PG_DEBUG < 10 THEN
1337 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1338 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1339 END IF;
1340 if v_debug_level < 11 then
1341 FOR i in 1..vt_del_id.count
1342 LOOP
1343 -- IF PG_DEBUG < 10 THEN
1344 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1345 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' ||
1346 to_char(vt_del_id(i)) || ' is ' ||
1347 to_char(SQL%BULK_ROWCOUNT(i)));
1348 END IF;
1349 END LOOP ;
1350 End If ;
1351 -- IF PG_DEBUG < 10 THEN
1352 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1353 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1354 END IF;
1355 /* ================= Debug Message =========================== */
1356
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 ERRBUF := 'CASE - Matching Delinquencies Update --> ' ||
1360 SQLCODE || ' Error Msg ' || SQLERRM ;
1361 -- IF PG_DEBUG < 10 THEN
1362 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1363 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1364 END IF;
1365 RETCODE := -1;
1366 ROLLBACK TO del_sp;
1367
1368 -- IF PG_DEBUG < 10 THEN
1369 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1370 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1371 END IF;
1372
1373 --
1374 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1375 --
1376
1377 CLEAR_BUFFERS2(p_request_id);
1378
1379 --DELETE FROM IEX_DEL_BUFFERS
1380 --WHERE request_id = p_request_id ;
1381 --
1382 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1383 --
1384
1385 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1386 --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
1387 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1388 Commit;
1389 return;
1390 END;
1391
1392
1393 /* 6785378
1394 BEGIN
1395
1396 -- clchang updated 04/18/2003 for BILL_TO
1397 -- get cust_account_id and customer_site_use_id
1398 -- by OKL, column_name = 'CUSTOMER_ACCOUNT' and 'BILL_TO_ADDRESS_ID'
1399 -- will be not null;
1400
1401 -- Simple Insert for Payment Schedule Id
1402 INSERT INTO IEX_DELINQUENCIES_ALL
1403 ( DELINQUENCY_ID,
1404 LAST_UPDATE_DATE,
1405 LAST_UPDATED_BY,
1406 CREATION_DATE,
1407 CREATED_BY,
1408 OBJECT_VERSION_NUMBER,
1409 DUNN_YN,
1410 PARTY_CUST_ID,
1411 CUST_ACCOUNT_ID,
1412 CUSTOMER_SITE_USE_ID, -- added by clchang for BILL_TO
1413 CASE_ID,
1414 STATUS,
1415 ORG_ID,
1416 SOURCE_PROGRAM_NAME,
1417 SCORE_ID ,
1418 SCORE_VALUE ,
1419 REQUEST_ID )
1420 SELECT
1421 IEX_DELINQUENCIES_S.NEXTVAL,
1422 v_today ,
1423 v_user_id ,
1424 v_today ,
1425 v_user_id ,
1426 1 ,
1427 'Y' ,
1428 ICV.party_id ,
1429 ICD.column_value,
1430 ICD2.column_value,
1431 IDB.score_object_id ,
1432 IDS.del_status ,
1433 --v_org_id ,
1434 ICV.org_id, --Modified for MOAC
1435 l_source_module,
1436 IDB.Score_id,
1437 IDB.score_value ,
1438 p_REQUEST_ID
1439 FROM IEX_DEL_BUFFERS IDB,
1440 IEX_CASES_VL ICV,
1441 IEX_CASE_DEFINITIONS ICD,
1442 IEX_CASES_VL ICV2,
1443 IEX_CASE_DEFINITIONS ICD2,
1444 IEX_DEL_STATUSES IDS
1445 WHERE
1446 NOT EXISTS
1447 (Select 1
1448 from iex_delinquencies
1449 where case_id = idb.score_object_id)
1450 AND NOT EXISTS
1451 (select 1
1452 from dual
1453 where IDS.del_status = vf_current)
1454 AND ICV.cas_id = IDB.score_object_id
1455 AND IDB.score_value between
1456 IDS.score_value_low and IDS.score_value_high
1457 AND IDB.score_id = IDS.score_id
1458 AND IDB.request_id = p_request_id
1459 AND ICV.cas_id = ICD.cas_id
1460 AND ICD.column_name = 'CUSTOMER_ACCOUNT'
1461 AND ICV2.cas_id = ICV.cas_id
1462 AND ICV2.cas_id = ICD2.cas_id
1463 AND ICD2.column_name = 'BILL_TO_ADDRESS_ID';
1464
1465
1466 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Delinquencies Created..>> '|| SQL%ROWCOUNT) ;
1467 l_del_insert_count := SQL%ROWCOUNT ;
1468
1469 -- ================= Debug Message ========================
1470 -- IF PG_DEBUG < 10 THEN
1471 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1472 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1473 END IF;
1474 -- IF PG_DEBUG < 10 THEN
1475 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1476 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1477 END IF;
1478 IF PG_DeBUG < 10 THEN
1479 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1480 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1481 END IF;
1482 END IF;
1483 -- ================= Debug Message ===========================
1484
1485 Exception
1486 WHEN OTHERS then
1487 ERRBUF := 'INSERT - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1488 -- IF PG_DEBUG < 10 THEN
1489 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1490 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1491 END IF;
1492 RETCODE := -1 ;
1493 ROLLBACK TO del_sp ;
1494
1495 -- IF PG_DEBUG < 10 THEN
1496 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1497 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'CASE INSERT >> Deleting Buffer Table after RollBack due to Error');
1498 END IF;
1499
1500 --
1501 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1502 --
1503
1504 CLEAR_BUFFERS2(p_request_id);
1505
1506 --DELETE FROM IEX_DEL_BUFFERS
1507 --WHERE request_id = p_request_id ;
1508 --
1509 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1510 --
1511
1512 -- IF PG_DEBUG < 10 THEN
1513 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1514 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1515 END IF;
1516 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1517 --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
1518 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1519 Commit;
1520 return;
1521 End;
1522 */ -- 6785378
1523
1524
1525 End If;
1526 End If;
1527
1528
1529 -- ______________________ CALLING THE WORKFLOW __________________________
1530 -- IF PG_DEBUG < 10 THEN
1531 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1532 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Calling the Work Flow........ ');
1533 END IF;
1534 -- IF PG_DEBUG < 10 THEN
1535 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1536 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Delinquency Table Count After Mangement Process ' || to_char(vt_del_id.count));
1537 END IF;
1538
1539 -- Filter out NOCOPY the delinquency ids that are not the Workflow Candidates.
1540 if vt_del_id.count > 0 then
1541
1542 for ct in 1..vt_del_id.COUNT
1543 LOOP
1544 if vt_del_status(ct) IN (vf_delinquent, vf_predelinquent)
1545 AND (vt_buf_status(ct) = vf_current) THEN
1546 -- F PG_DEBUG < 10 THEN
1547 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1548 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Del Id for Workflow >> ' || to_char(vt_del_id(ct)));
1549 END IF;
1550 if vt_wf_del_id.COUNT = 0 then
1551 vt_wf_del_id(1) := vt_del_id(ct) ;
1552 else
1553 vt_wf_del_id(vt_wf_del_id.LAST + 1) := vt_del_id(ct) ;
1554 end If ;
1555 End If ;
1556 END LOOP ;
1557 End If ;
1558
1559 -- ================= Debug Message ===========================
1560 -- IF PG_DEBUG < 10 THEN
1561 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1562 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '------------------------------------------------------------');
1563 END IF;
1564 -- IF PG_DEBUG < 10 THEN
1565 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1566 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of WorlFlow Candidate Rows --> ' || to_char(vt_wf_del_id.COUNT));
1567 END IF;
1568
1569 -- Launch the Workflow and Close Promises only when required
1570 if vt_wf_del_id.COUNT > 0 then
1571
1572 FOR p in 1..vt_wf_del_id.COUNT
1573 LOOP
1574 -- IF PG_DEBUG < 10 THEN
1575 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1576 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Delinquency Id --> ' || to_char(vt_wf_del_id(p)));
1577 END IF;
1578 END LOOP ;
1579 -- IF PG_DEBUG < 10 THEN
1580 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1581 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '------------------------------------------------------------');
1582 END IF;
1583 /* ================= Debug Message =========================== */
1584
1585
1586 -- Launch the WorkFlow
1587 IEX_WF_DEL_CUR_STATUS_NOTE_PUB.START_WORKFLOW
1588 (p_api_version => 1.0,
1589 p_init_msg_list => FND_API.G_FALSE,
1590 p_commit => FND_API.G_FALSE,
1591 p_delinquency_ids => vt_wf_del_id,
1592 x_return_status => l_return_status,
1593 x_msg_count => l_msg_count,
1594 x_msg_data => l_msg_data);
1595
1596 -- IF PG_DEBUG < 10 THEN
1597 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1598 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'WorlFlow Status --> ' || l_return_status);
1599 END IF;
1600
1601 -- Close the Promises
1602 IEX_PROMISES_BATCH_PUB.CLOSE_PROMISES
1603 (p_api_version => 1.0,
1604 p_init_msg_list => FND_API.G_FALSE,
1605 p_commit => FND_API.G_FALSE,
1606 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1607 x_return_status => l_return_status,
1608 x_msg_count => l_msg_count,
1609 x_msg_data => l_msg_data ,
1610 p_delinq_tbl => vt_wf_del_id );
1611
1612 -- IF PG_DEBUG < 10 THEN
1613 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1614 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Close Promises Status --> ' || l_return_status);
1615 END IF;
1616
1617 End If ;
1618
1619 -- IF PG_DEBUG < 10 THEN
1620 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1621 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table');
1622 END IF;
1623 --
1624 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1625 --
1626 CLEAR_BUFFERS2(p_request_id);
1627 --DELETE FROM IEX_DEL_BUFFERS WHERE request_id = p_request_id ;
1628 --
1629 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1630 --
1631 -- IF PG_DEBUG < 10 THEN
1632 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1633 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleted Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1634 END IF;
1635
1636 if not isRefreshProgramsRunning then -- Added for bug#7133605 by schekuri on 17-Jun-2008 --Added by PNAVEENK
1637 --Bug5237039. Fix by LKKUMAR on 25-May-2006. Start
1638 FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting to update ar_trx_bal_summary....'); --Added by PNAVEENK
1639 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1640 IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = 1...');
1641 END IF;
1642 -- start bug 10037776 sunagesh 01.08.11
1643 /* call refresh_summary_incr, only when the business events are enabled.
1644 i.e When business event is enabled, changes will get reflect in iex_dln_uwq_summary table and there is no need to run populate uwq summary cp
1645 in incremental mode */
1646 -- end bug 10037776 sunagesh 01.08.11
1647 --if vt_del_id.count > 0 then -- 6785378
1648 BEGIN
1649 -- start bug 10037776 sunagesh 01.11.08
1650 /* OPEN c_cust_account_id_1;
1651 LOOP
1652 FETCH c_cust_account_id_1 BULK COLLECT INTO
1653 l_cust_account_id_1 LIMIT G_BATCH_SIZE;
1654 IF l_cust_account_id_1.count = 0 THEN
1655 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1656 IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1...');
1657 END IF;
1658 CLOSE c_cust_account_id_1;
1659 EXIT;
1660 ELSE
1661 FORALL I IN l_cust_account_id_1.first..l_cust_account_id_1.last
1662 UPDATE AR_TRX_BAL_SUMMARY ARS
1663 SET REFERENCE_1 = 1
1664 WHERE CUST_ACCOUNT_ID = l_cust_account_id_1(I);
1665
1666 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1667 IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
1668 END IF;
1669 END IF;
1670 END LOOP;*/
1671 select count(we.name)
1672 into l_noof_active_busi_events
1673 from wf_event_subscriptions wes,
1674 wf_events we,
1675 fnd_application fa
1676 where we.guid = wes.event_filter_guid
1677 and we.name like 'oracle.apps.ar.%'
1678 and fa.application_id=695
1679 and WES.owner_tag = fa.application_short_name
1680 and wes.status='ENABLED'
1681 and we.status='ENABLED';
1682 -- end bug 10037776 sunagesh 01.08.11
1683
1684 EXCEPTION WHEN OTHERS THEN
1685 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1686 -- start bug 10037776 sunagesh 01.08.11
1687 -- IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
1688 IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while getting Active business events count');
1689 -- end bug 10037776 sunagesh 01.08.11
1690 END IF;
1691 END;
1692
1693 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1694 IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = Null...');
1695 END IF;
1696 -- start bug 10037776 sunagesh 01.08.11
1697 IEX_DEBUG_PUB.LOGMESSAGE('Number of active business events: ' || l_noof_active_busi_events);
1698 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number of active business events: ' || l_noof_active_busi_events) ;
1699
1700 if l_noof_active_busi_events > 0 then
1701
1702 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1703 IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = 1...');
1704 END IF;
1705 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating AR_TRX_BAL_SUMMARY table with reference_1 = 1...') ;
1706 BEGIN
1707 OPEN c_cust_account_id_1;
1708 LOOP
1709 FETCH c_cust_account_id_1 BULK COLLECT INTO
1710 l_cust_account_id_1 LIMIT G_BATCH_SIZE;
1711 IF l_cust_account_id_1.count = 0 THEN
1712 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1713 IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1...');
1714 END IF;
1715 CLOSE c_cust_account_id_1;
1716 EXIT;
1717 ELSE
1718 FORALL I IN l_cust_account_id_1.first..l_cust_account_id_1.last
1719 UPDATE AR_TRX_BAL_SUMMARY ARS
1720 SET REFERENCE_1 = '1'
1721 WHERE CUST_ACCOUNT_ID = l_cust_account_id_1(I)
1722 AND ORG_ID = FND_PROFILE.VALUE('ORG_ID');
1723 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1724 IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
1725 END IF;
1726 END IF;
1727 END LOOP;
1728 EXCEPTION WHEN OTHERS THEN
1729 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1730 IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
1731 END IF;
1732 END;
1733
1734 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1735 IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = Null...');
1736 END IF;
1737 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating AR_TRX_BAL_SUMMARY table with reference_1 = Null...') ;
1738 -- end bug 10037776 sunagesh 01.08.11
1739 BEGIN
1740 OPEN c_cust_account_id_n;
1741 LOOP
1742 FETCH c_cust_account_id_n BULK COLLECT INTO
1743 l_cust_account_id_n LIMIT G_BATCH_SIZE;
1744 IF l_cust_account_id_n.count = 0 THEN
1745 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1746 IEX_DEBUG_PUB.LOGMESSAGE('Exit after Update ar_trx_bal_summary on complete with reference_1 = Null...');
1747 END IF;
1748 CLOSE c_cust_account_id_n;
1749 EXIT;
1750 ELSE
1751 FORALL I IN l_cust_account_id_n.first..l_cust_account_id_n.last
1752 UPDATE AR_TRX_BAL_SUMMARY ARS
1753 SET REFERENCE_1 = Null
1754 WHERE CUST_ACCOUNT_ID = l_cust_account_id_n(I);
1755 FND_FILE.PUT_LINE(FND_FILE.LOG,SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = NULL');
1756 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1757 IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');
1758 END IF;
1759 END IF;
1760 END LOOP;
1761 EXCEPTION WHEN OTHERS THEN
1762 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1763 IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = Null');
1764 END IF;
1765 END;
1766 --Bug5237039. Fix by LKKUMAR on 25-May-2006. End.
1767 -- end if; -- 6785378
1768 COMMIT;
1769 -- Start Bug 5874874 gnramasa 25-Apr-2007
1770
1771 -- if vt_del_id.count > 0 then -- 6785378
1772 -- start bug 10037776 sunagesh 01.08.11
1773 IEX_DEBUG_PUB.LOGMESSAGE('Business events are enabled, so calling iex_uwq_pop_sum_tbl_pvt.refresh_summary_incr');
1774 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Business events are enabled, so calling iex_uwq_pop_sum_tbl_pvt.refresh_summary_incr') ;
1775 -- end bug 10037776 sunagesh 01.08.11
1776 iex_uwq_pop_sum_tbl_pvt.refresh_summary_incr
1777 (x_errbuf,
1778 x_retcode,
1779 NULL,
1780 'DLN');
1781 -- End Bug 5874874 gnramasa 25-Apr-2007
1782 -- end if; -- 6785378
1783 end if;----added for bug 10037776 sunagesh 01.08.11
1784 COMMIT; -- added for bug 10037776 sunagesh 01.08.11
1785 end if; --End if isRefreshProgramsRunning -- Added by PNAVEENK
1786
1787
1788 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling the Dunning Closing Process.... ') ;
1789 CLOSE_DUNNINGS(RETCODE, ERRBUF, 'ALL') ;
1790 FND_FILE.PUT_LINE(FND_FILE.LOG, '******* Dunning Closing Process Result *******') ;
1791 FND_FILE.PUT_LINE(FND_FILE.LOG, ' RETCODE >>> ' || RETCODE) ;
1792 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ERRBUF >>> ' || ERRBUF) ;
1793
1794
1795
1796 if l_del_insert_count > 0 OR l_del_update_count > 0 then
1797 if l_enable_business_events = 'Y' then
1798 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Business Events Processing Enabled... ' ) ;
1799
1800 --jsanju 09/15/05 , logic has changed, raise event directly for the request id
1801 ---do not call the concurrent program. There will be one event for every scoring engine
1802 -- request and not for every del created or updated.
1803 --set concurrent status to warning if event is not raised.
1804 /* l_business_event_req_id :=
1805 FND_REQUEST.SUBMIT_REQUEST(
1806 APPLICATION => 'IEX',
1807 PROGRAM => 'IEX:RAISE_DEL_CREATE_EVENT' ,
1808 DESCRIPTION => 'Business Event when Delinquencies are Created or Closed',
1809 START_TIME => sysdate,
1810 SUB_REQUEST => false,
1811 ARGUMENT1 => p_Request_id); */
1812
1813
1814 RAISE_EVENT(
1815 P_REQUEST_ID =>p_request_id,
1816 p_del_create_count =>l_del_insert_count,
1817 p_del_update_count =>l_del_update_count,
1818 x_return_status => l_return_status,
1819 x_msg_count => l_msg_count,
1820 x_msg_data => l_msg_data );
1821
1822
1823 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1824 if l_return_Status = FND_API.G_RET_STS_SUCCESS THEN
1825 fnd_file.put_line(fnd_file.log,'Business Event raised Successfully');
1826 else
1827 fnd_file.put_line(fnd_file.log,'Business Event not raised ');
1828 request_status := fnd_concurrent.set_completion_status('WARNING'
1829 , 'Business Event Not Raised');
1830 end if;
1831 END IF ;
1832
1833 else
1834 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Business Events Processing Disabled... ' ) ;
1835 End If ;
1836 End If ;
1837
1838 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1839 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1840 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES End <<----------');
1841 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1842 IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1843 END IF;
1844
1845 EXCEPTION
1846 WHEN Others then
1847 RETCODE := -1 ;
1848 ERRBUF := 'MANAGE_DELINQUENCIES >> WHEN OTHERS - ERROR - ' || SQLCODE || ' ' || SQLERRM ;
1849 -- IF PG_DEBUG < 10 THEN
1850 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1851 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1852 END IF;
1853 ROLLBACK TO del_sp ;
1854
1855 -- IF PG_DEBUG < 10 THEN
1856 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1857 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1858 END IF;
1859
1860 --
1861 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1862 --
1863 CLEAR_BUFFERS2(p_request_id);
1864 --DELETE FROM IEX_DEL_BUFFERS WHERE request_id = p_request_id ;
1865 --
1866 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1867 --
1868
1869 -- IF PG_DEBUG < 10 THEN
1870 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1871 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1872 END IF;
1873
1874 Commit;
1875 END MANAGE_DELINQUENCIES;
1876
1877 /*------------------------------------------------------------------------
1878 11.5.7 Independent Delinquency Creation Process
1879 ------------------------------------------------------------------------ */
1880 PROCEDURE Create_Ind_Delinquency
1881 ( p_api_version IN NUMBER ,
1882 p_init_msg_list IN VARCHAR2,
1883 p_commit IN VARCHAR2,
1884 p_validation_level IN NUMBER ,
1885 x_return_status OUT NOCOPY VARCHAR2 ,
1886 x_msg_count OUT NOCOPY NUMBER ,
1887 x_msg_data OUT NOCOPY VARCHAR2 ,
1888 p_source_module IN VARCHAR2 ,
1889 p_party_id IN Number ,
1890 p_object_code IN Varchar2 ,
1891 p_object_id_tbl IN IEX_UTILITIES.t_numbers,
1892 x_del_id_tbl OUT NOCOPY IEX_UTILITIES.t_numbers)
1893 IS
1894 v_first Number := 0 ;
1895 v_last Number := 0 ;
1896
1897 v_org_id Number ;
1898 v_today Date ;
1899 v_user_id Number ;
1900 v_count Number := 1 ;
1901
1902 l_api_name varchar2(50);
1903
1904 v_error_msg varchar2(200);
1905 --Begin Bug 6446848 08-Dec-2008 barathsr
1906 l_deln_id number;
1907 l_pay_sch_id number;
1908 l_org_id number;
1909 l_cust_acct_id number; --29/12
1910 l_cust_site_use_id number;--29/12
1911 --End Bug 6446848 08-Dec-2008 barathsr
1912 l_cust_trx_id number;--Added for Bug 8517550 14-May-2009 barathsr
1913
1914 Begin
1915
1916 -- clchang 10/28/04 fixed gscc warning
1917 l_api_name := 'Create_Ind_Delinquency';
1918
1919
1920 if p_object_id_tbl.COUNT > 0 then
1921 v_first := p_object_id_tbl.FIRST ;
1922 v_last := p_object_id_tbl.LAST ;
1923 End If ;
1924
1925
1926 -- Standard Start of API savepoint
1927 SAVEPOINT IEX_IND_DEL ;
1928
1929 -- Standard call to check for call compatibility.
1930 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
1931 p_api_version,
1932 l_api_name,
1933 G_PKG_NAME)
1934 THEN
1935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1936 END IF;
1937
1938 -- Initialize message list if p_init_msg_list is set to TRUE.
1939 IF FND_API.to_Boolean(p_init_msg_list) THEN
1940 FND_MSG_PUB.initialize;
1941 END IF;
1942
1943 -- Initialize API return status to SUCCESS
1944 x_return_status := FND_API.G_RET_STS_SUCCESS;
1945
1946 if x_del_id_tbl.COUNT > 0 then
1947 x_del_id_tbl.DELETE ;
1948 End IF ;
1949
1950 Select
1951 --fnd_profile.value('ORG_ID'), --Commneted for MOAC
1952 NVL(fnd_profile.value('USER_ID'), -1),
1953 sysdate
1954 into
1955 --v_org_id , --Commneted for MOAC
1956 v_user_id ,
1957 v_today
1958 From dual ;
1959
1960 -- Loop Through the Table of Cases and Create Delinquencies.
1961 IF p_object_code = 'IEX_CASE' then
1962 FOR cnt IN v_first..v_last
1963 LOOP
1964
1965 Select IEX_DELINQUENCIES_S.NEXTVAL
1966 into x_del_id_tbl(v_count)
1967 From dual ;
1968
1969 INSERT INTO IEX_DELINQUENCIES_ALL
1970 (DELINQUENCY_ID ,
1971 LAST_UPDATE_DATE ,
1972 LAST_UPDATED_BY ,
1973 CREATION_DATE ,
1974 CREATED_BY ,
1975 OBJECT_VERSION_NUMBER,
1976 DUNN_YN ,
1977 PARTY_CUST_ID ,
1978 CUST_ACCOUNT_ID ,
1979 CASE_ID ,
1980 STATUS ,
1981 ORG_ID ,
1982 SOURCE_PROGRAM_NAME )
1983 VALUES
1984 (x_del_id_tbl(v_count) ,
1985 v_today ,
1986 v_user_id ,
1987 v_today ,
1988 v_user_id ,
1989 1 ,
1990 'N' ,
1991 p_party_id ,
1992 NULL ,
1993 p_object_id_tbl(cnt),
1994 vf_delinquent ,
1995 v_org_id ,
1996 p_source_module ) ;
1997
1998 v_count := v_count + 1 ;
1999 END LOOP ;
2000 --Begin Bug 6446848 08-Dec-2008 barathsr
2001 else
2002
2003 IEX_DEBUG_PUB.LOGMESSAGE('In for Current invoice insertion....');
2004
2005 select IEX_DELINQUENCIES_S.NEXTVAL
2006 into l_deln_id
2007 from dual;
2008
2009 if p_object_id_tbl.count = 1 then
2010 for i in v_first..v_last loop
2011
2012 begin--29/12
2013 select org_id,customer_id,customer_site_use_id,customer_trx_id--Added for Bug 8517550 14-May-2009 barathsr
2014 into l_org_id,l_cust_acct_id,l_cust_site_use_id,l_cust_trx_id --29/12
2015 from ar_payment_schedules_all
2016 where payment_schedule_id= p_object_id_tbl(i);
2017
2018 IEX_DEBUG_PUB.LOGMESSAGE('Org_id--->'||l_org_id);
2019 IEX_DEBUG_PUB.LOGMESSAGE('Cust_account_id-->'||l_cust_acct_id);
2020 IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_site_use_id);
2021 IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_trx_id);
2022
2023 INSERT INTO IEX_DELINQUENCIES_ALL
2024 (DELINQUENCY_ID ,
2025 LAST_UPDATE_DATE ,
2026 LAST_UPDATED_BY ,
2027 CREATION_DATE ,
2028 CREATED_BY ,
2029 OBJECT_VERSION_NUMBER,
2030 DUNN_YN ,
2031 PARTY_CUST_ID ,
2032 CUST_ACCOUNT_ID ,
2033 transaction_id, --Added for Bug 8517550 14-May-2009 barathsr
2034 payment_schedule_id,
2035 STATUS ,
2036 ORG_ID ,
2037 SOURCE_PROGRAM_NAME,
2038 CUSTOMER_SITE_USE_ID)
2039 VALUES
2040 (l_deln_id,
2041 v_today ,
2042 v_user_id ,
2043 v_today ,
2044 v_user_id ,
2045 1 ,
2046 'N' ,
2047 p_party_id ,
2048 l_cust_acct_id, --29/12
2049 l_cust_trx_id, --Added for Bug 8517550 14-May-2009 barathsr
2050 p_object_id_tbl(i),
2051 vf_current,
2052 l_org_id ,
2053 p_source_module,
2054 l_cust_site_use_id) ; --29/12
2055 exception
2056 when others then --29/12
2057 IEX_DEBUG_PUB.LOGMESSAGE('Error in CURRENT invoice selection/insertion activity.....');
2058 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2059 end;--29/12
2060 end loop;
2061 end if;
2062
2063 x_del_id_tbl(1):=l_deln_id;
2064
2065 --End Bug 6446848 08-Dec-2008 barathsr
2066 END IF ;
2067
2068 -- Standard check for p_commit
2069 IF FND_API.to_Boolean(p_commit) THEN
2070 COMMIT WORK;
2071 ELSE
2072 ROLLBACK TO IEX_IND_DEL ;
2073 END IF;
2074
2075 -- Debug Message
2076 IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
2077 IEX_DEBUG_PUB.LogMessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2078 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2079
2080 EXCEPTION
2081 WHEN FND_API.G_EXC_ERROR THEN
2082 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2083 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: Create_Ind_Delinquency: Expected Error ' || sqlerrm);
2084 END IF;
2085 RAISE FND_API.G_EXC_ERROR;
2086 ROLLBACK TO IEX_IND_DEL;
2087
2088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2089 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2090 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: Create_Ind_Delinquency: Unexpected Error ' || sqlerrm);
2091 END IF;
2092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2093 ROLLBACK TO IEX_IND_DEL;
2094
2095 WHEN OTHERS THEN
2096 v_error_msg := SQLCODE || ' Error Msg ' || SQLERRM ;
2097 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2098 IEX_DEBUG_PUB.LOGMESSAGE(v_error_msg) ;
2099 END IF;
2100 ROLLBACK TO IEX_IND_DEL;
2101 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2102 --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(-9999, 'CREATE_IND_DELINQUENCIES', v_error_msg) ;
2103 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2104 COMMIT ;
2105 End ;
2106
2107
2108 /*
2109 || Overview: will update the delinquency header table once the
2110 || scoring engine for delinquencies is run
2111 ||
2112 || Parameter: p_request_id => request_id of score engine run
2113 ||
2114 || Source Tables: IEX_DEL_BUFFERS
2115 ||
2116 || Target Tables: IEX_DELINQUENCIES_ALL
2117 ||
2118 || Creation date: 03/19/02 10:04:AM
2119 ||
2120 || Major Modifications: when who what
2121 || 03/19/02 10:04:AM raverma created
2122 */
2123 procedure SCORE_DELINQUENCIES (ERRBUF OUT NOCOPY VARCHAR2,
2124 RETCODE OUT NOCOPY VARCHAR2,
2125 p_request_id Number) IS
2126
2127 type t_ids is table of number
2128 index by binary_integer;
2129
2130 v_score_objects t_ids;
2131 v_score_values t_ids;
2132 l_score_id number;
2133 nCount number;
2134
2135 cursor c_scores(p_request_id in number)
2136 is
2137 select score_object_id,
2138 score_value
2139 from iex_del_buffers
2140 where request_id = p_request_id;
2141 BEGIN
2142
2143 RETCODE := 0;
2144
2145 -- IF PG_DEBUG < 10 THEN
2146 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2147 iex_debug_pub.logMessage('IEX_DELINQUENCY_PUB.SCORE_DELINQUENCIES');
2148 END IF;
2149 -- IF PG_DEBUG < 10 THEN
2150 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2151 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'StartTime: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
2152 END IF;
2153 -- get the score id for the engine
2154 Select score_id into l_score_id
2155 from iex_del_buffers
2156 Where request_id = p_request_id and
2157 rownum = 1;
2158
2159 -- IF PG_DEBUG < 10 THEN
2160 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2161 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'ScoreID is ' || l_score_id);
2162 END IF;
2163 -- IF PG_DEBUG < 10 THEN
2164 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2165 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'bridge value is ' || p_request_id);
2166 END IF;
2167
2168 -- now get the data
2169 open c_scores(p_request_id);
2170 LOOP
2171 FETCH c_scores
2172 BULK COLLECT INTO
2173 v_score_objects,
2174 v_score_values
2175 LIMIT G_Batch_Size;
2176
2177 nCount := v_score_objects.count;
2178
2179 for i in 1..nCount
2180 loop
2181 -- IF PG_DEBUG < 10 THEN
2182 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2183 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || v_score_objects(i));
2184 END IF;
2185 -- IF PG_DEBUG < 10 THEN
2186 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2187 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || v_score_values(i));
2188 END IF;
2189 end loop;
2190
2191 -- IF PG_DEBUG < 10 THEN
2192 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2193 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'Count is ' || nCount);
2194 END IF;
2195 FORALL r in 1..nCount
2196 UPDATE IEX_DELINQUENCIES_ALL
2197 SET SCORE_ID = l_score_id,
2198 Score_value = v_score_values(r),
2199 last_update_date = sysdate,
2200 request_id = FND_GLOBAL.CONC_REQUEST_ID
2201 WHERE DELINQUENCY_ID = v_score_objects(r);
2202
2203 EXIT WHEN c_scores%NOTFOUND;
2204 END LOOP;
2205 close c_scores;
2206 /*
2207 SELECT score_object_id,
2208 score_value
2209 BULK COLLECT INTO
2210 LIMIT NVL(FND_PROFILE.VALUE('IEX_BATCH_SIZE'), 1000)
2211 FROM iex_del_buffers
2212 WHERE request_id = p_request_id;
2213 */
2214 -- IF PG_DEBUG < 10 THEN
2215 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2216 IEX_DEBUG_PUB.logMessage('SCORE_DELINQUENCIES: ' || 'Deleting from buffers');
2217 END IF;
2218 --
2219 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2220 --
2221 CLEAR_BUFFERS2(p_request_id);
2222 --Delete From IEX_DEL_BUFFERS
2223 -- Where request_id = p_request_id;
2224 --
2225 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2226 --
2227
2228 -- IF PG_DEBUG < 10 THEN
2229 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2230 iex_debug_pub.logMessage('IEX_DELINQUENCY_PUB.SCORE_DELINQUENCIES');
2231 END IF;
2232 -- IF PG_DEBUG < 10 THEN
2233 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2234 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'ENDTime: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
2235 END IF;
2236 Exception
2237 When NO_DATA_FOUND then
2238 -- IF PG_DEBUG < 10 THEN
2239 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2240 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'no data found ' || sqlerrm);
2241 END IF;
2242 RETCODE := -1;
2243 ERRBUF := sqlerrm;
2244 When others then
2245 -- IF PG_DEBUG < 10 THEN
2246 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2247 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'other error ' || sqlerrm);
2248 END IF;
2249 RETCODE := -1;
2250 ERRBUF := sqlerrm;
2251
2252 END SCORE_DELINQUENCIES;
2253
2254 /*********************
2255 Set UWQ status for promises
2256 ***********************/
2257 PROCEDURE SHOW_IN_UWQ(
2258 P_API_VERSION IN NUMBER,
2259 P_INIT_MSG_LIST IN VARCHAR2,
2260 P_COMMIT IN VARCHAR2,
2261 P_VALIDATION_LEVEL IN NUMBER,
2262 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2263 X_MSG_COUNT OUT NOCOPY NUMBER,
2264 X_MSG_DATA OUT NOCOPY VARCHAR2,
2265 P_DELINQUENCY_ID_TBL IN DBMS_SQL.NUMBER_TABLE,
2266 P_UWQ_STATUS IN VARCHAR2,
2267 P_NO_DAYS IN NUMBER)
2268 IS
2269 l_api_name CONSTANT VARCHAR2(30) := 'SHOW_IN_UWQ';
2270 l_api_version CONSTANT NUMBER := 1.0;
2271 l_return_status varchar2(10);
2272 l_msg_count number;
2273 l_msg_data varchar2(200);
2274
2275 l_validation_item varchar2(100);
2276 l_days NUMBER;
2277 l_set_status_date DATE;
2278 l_status varchar2(20);
2279 nCount number;
2280
2281 Type refCur is Ref Cursor;
2282 l_cursor refCur;
2283 l_SQL VARCHAR2(10000);
2284 l_broken_promises DBMS_SQL.NUMBER_TABLE;
2285 i number;
2286 j number;
2287 l_uwq_active_date date;
2288 l_uwq_complete_date date;
2289 l_level VARCHAR2(80);
2290
2291 CURSOR c_get_level IS
2292 SELECT PREFERENCE_VALUE FROM IEX_APP_PREFERENCES_VL WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL';
2293
2294 begin
2295
2296 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2297 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': start');
2298 END IF;
2299
2300 -- Standard start of API savepoint
2301 SAVEPOINT SHOW_IN_UWQ_PVT;
2302
2303 -- Standard call to check for call compatibility
2304 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2306 END IF;
2307
2308 -- Initialize message list if p_init_msg_list is set to TRUE
2309 IF FND_API.To_Boolean(p_init_msg_list) THEN
2310 FND_MSG_PUB.initialize;
2311 END IF;
2312
2313 -- Initialize API return status to success
2314 l_return_status := FND_API.G_RET_STS_SUCCESS;
2315
2316 -- START OF BODY OF API
2317
2318 -- validating uwq status
2319 l_validation_item := 'P_UWQ_STATUS';
2320 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2321 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new uwq status: ' || P_UWQ_STATUS);
2322 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2323 END IF;
2324 if P_UWQ_STATUS is null then
2325 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2326 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2327 END IF;
2328 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2329 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2330 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2331 FND_MSG_PUB.Add;
2332 RAISE FND_API.G_EXC_ERROR;
2333 end if;
2334
2335 -- validating table of promises
2336 l_validation_item := 'P_DELINQUENCY_ID_TBL';
2337 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2338 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': count of P_DELINQUENCY_ID_TBL: ' || P_DELINQUENCY_ID_TBL.count);
2339 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2340 END IF;
2341 if P_DELINQUENCY_ID_TBL.count = 0 then
2342 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2343 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2344 END IF;
2345 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2346 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2347 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2348 FND_MSG_PUB.Add;
2349 RAISE FND_API.G_EXC_ERROR;
2350 end if;
2351
2352 -- validating p_days
2353 l_validation_item := 'P_NO_DAYS';
2354 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2355 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': P_NO_DAYS: ' || P_NO_DAYS);
2356 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2357 END IF;
2358 if P_NO_DAYS is not null and P_NO_DAYS < 0 then
2359 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2360 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2361 END IF;
2362 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2363 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2364 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2365 FND_MSG_PUB.Add;
2366 RAISE FND_API.G_EXC_ERROR;
2367 end if;
2368
2369 -- set number of days
2370 if P_NO_DAYS is null then
2371 l_days := to_number(nvl(fnd_profile.value('IEX_UWQ_DEFAULT_PENDING_DAYS'), '0'));
2372 else
2373 l_days := P_NO_DAYS;
2374 end if;
2375 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2376 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': number of days: ' || l_days);
2377 END IF;
2378 l_set_status_date := sysdate + l_days;
2379 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2380 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': set status date: ' || l_set_status_date);
2381 END IF;
2382
2383 -- check for status
2384 if P_UWQ_STATUS = 'ACTIVE' then
2385 l_uwq_active_date := NULL;
2386 l_uwq_complete_date := NULL;
2387 elsif P_UWQ_STATUS = 'PENDING' then
2388 l_uwq_active_date := l_set_status_date;
2389 l_uwq_complete_date := NULL;
2390 elsif P_UWQ_STATUS = 'COMPLETE' then
2391 l_uwq_active_date := NULL;
2392 l_uwq_complete_date := sysdate;
2393 end if;
2394
2395 nCount := p_delinquency_id_tbl.count;
2396 if nCount > 0 then
2397 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2398 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_active_date: ' || l_uwq_active_date);
2399 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_complete_date: ' || l_uwq_complete_date);
2400 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': updating promise details...');
2401 END IF;
2402 FORALL i in 1..nCount
2403 update iex_delinquencies_all
2404 set UWQ_STATUS = P_UWQ_STATUS,
2405 UWQ_ACTIVE_DATE = l_uwq_active_date,
2406 UWQ_COMPLETE_DATE = l_uwq_complete_date,
2407 last_update_date = sysdate,
2408 last_updated_by = G_USER_ID
2409 where
2410 delinquency_id = p_delinquency_id_tbl(i);
2411
2412 -- start of fix for bug 5874874 gnramasa 25-Apr-07
2413 OPEN c_get_level;
2414 FETCH c_get_level INTO l_level;
2415 CLOSE c_get_level;
2416 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Strateg Level = ' || l_level);
2417
2418 IF l_level = 'CUSTOMER' THEN
2419
2420 FORALL i in 1..nCount
2421 update IEX_DLN_UWQ_SUMMARY sum
2422 set
2423 sum.active_delinquencies =
2424 (SELECT 1
2425 FROM dual
2426 WHERE EXISTS
2427 (SELECT 1
2428 FROM iex_delinquencies_all
2429 WHERE party_cust_id = sum.party_id
2430 AND status IN('DELINQUENT', 'PREDELINQUENT')
2431 AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
2432 AND uwq_status = 'PENDING'))
2433 )
2434 ),
2435 sum.complete_delinquencies =
2436 (SELECT 1
2437 FROM dual
2438 WHERE EXISTS
2439 (SELECT 1
2440 FROM iex_delinquencies_all
2441 WHERE party_cust_id = sum.party_id
2442 AND status IN('DELINQUENT', 'PREDELINQUENT')
2443 AND(uwq_status = 'COMPLETE'
2444 AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2445 ),
2446 sum.pending_delinquencies =
2447 (SELECT 1
2448 FROM dual
2449 WHERE EXISTS
2450 (SELECT 1
2451 FROM iex_delinquencies_all
2452 WHERE party_cust_id = sum.party_id
2453 AND status IN('DELINQUENT', 'PREDELINQUENT')
2454 AND(uwq_status = 'PENDING'
2455 AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
2456 )
2457 WHERE sum.party_id = (select party_cust_id
2458 from iex_delinquencies_all
2459 where delinquency_id = p_delinquency_id_tbl(i));
2460
2461 ELSIF l_level = 'ACCOUNT' THEN
2462
2463 FORALL i in 1..nCount
2464 update IEX_DLN_UWQ_SUMMARY sum
2465 set
2466 sum.active_delinquencies =
2467 (SELECT 1
2468 FROM dual
2469 WHERE EXISTS
2470 (SELECT 1
2471 FROM iex_delinquencies_all
2472 WHERE party_cust_id = party_id
2473 AND status IN('DELINQUENT', 'PREDELINQUENT')
2474 AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
2475 AND uwq_status = 'PENDING'))
2476 )
2477 ),
2478 sum.complete_delinquencies =
2479 (SELECT 1
2480 FROM dual
2481 WHERE EXISTS
2482 (SELECT 1
2483 FROM iex_delinquencies_all
2484 WHERE cust_account_id = sum.cust_account_id
2485 AND status IN('DELINQUENT', 'PREDELINQUENT')
2486 AND(uwq_status = 'COMPLETE'
2487 AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2488 ),
2489 sum.pending_delinquencies =
2490 (SELECT 1
2491 FROM dual
2492 WHERE EXISTS
2493 (SELECT 1
2494 FROM iex_delinquencies_all
2495 WHERE cust_account_id = sum.cust_account_id
2496 AND status IN('DELINQUENT', 'PREDELINQUENT')
2497 AND(uwq_status = 'PENDING'
2498 AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
2499 )
2500 WHERE sum.cust_account_id = (select cust_account_id
2501 from iex_delinquencies_all
2502 where delinquency_id = p_delinquency_id_tbl(i));
2503
2504 ELSIF l_level = 'BILL_TO' THEN
2505
2506 FORALL i in 1..nCount
2507 update IEX_DLN_UWQ_SUMMARY sum
2508 set
2509 sum.active_delinquencies =
2510 (SELECT 1
2511 FROM dual
2512 WHERE EXISTS
2513 (SELECT 1
2514 FROM iex_delinquencies_all
2515 WHERE customer_site_use_id = sum.site_use_id
2516 AND status IN('DELINQUENT', 'PREDELINQUENT')
2517 AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
2518 AND uwq_status = 'PENDING'))
2519 )
2520 ),
2521 sum.complete_delinquencies =
2522 (SELECT 1
2523 FROM dual
2524 WHERE EXISTS
2525 (SELECT 1
2526 FROM iex_delinquencies_all
2527 WHERE customer_site_use_id = sum.site_use_id
2528 AND status IN('DELINQUENT', 'PREDELINQUENT')
2529 AND(uwq_status = 'COMPLETE'
2530 AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2531 ),
2532 sum.pending_delinquencies =
2533 (SELECT 1
2534 FROM dual
2535 WHERE EXISTS
2536 (SELECT 1
2537 FROM iex_delinquencies_all
2538 WHERE customer_site_use_id = sum.site_use_id
2539 AND status IN('DELINQUENT', 'PREDELINQUENT')
2540 AND(uwq_status = 'PENDING'
2541 AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
2542 )
2543 WHERE sum.site_use_id = (select customer_site_use_id
2544 from iex_delinquencies_all
2545 where delinquency_id = p_delinquency_id_tbl(i));
2546
2547 END IF;
2548
2549 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Updated ' || SQL%ROWCOUNT || ' rows in IEX_DLN_UWQ_SUMMARY');
2550 -- end of fix for bug 5874874 gnramasa 25-Apr-07
2551
2552 else
2553 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2554 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');
2555 END IF;
2556 end if;
2557
2558 -- END OF BODY OF API
2559
2560 -- Standard check of p_commit.
2561 IF FND_API.To_Boolean( p_commit ) THEN
2562 COMMIT WORK;
2563 END IF;
2564
2565 x_return_status := l_return_status;
2566 -- Standard call to get message count and if count is 1, get message info
2567 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2568 p_count => x_msg_count,
2569 p_data => x_msg_data);
2570
2571 EXCEPTION
2572 WHEN FND_API.G_EXC_ERROR THEN
2573 ROLLBACK TO SHOW_IN_UWQ_PVT;
2574 x_return_status := FND_API.G_RET_STS_ERROR;
2575 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2576 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2577 ROLLBACK TO SHOW_IN_UWQ_PVT;
2578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2579 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2580 WHEN OTHERS THEN
2581 ROLLBACK TO SHOW_IN_UWQ_PVT;
2582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2583 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2584 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2585 END IF;
2586 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2587 end;
2588
2589 /*------------------------------------------------------------------------
2590 CLOSE Dunnings Process
2591 This process closes all the dunnings that are open for the delinquencies
2592 that are in CURRENT status. This makes the Dunning, Delinquency records
2593 consistant. Dunning level passed as parameter decides which dunning level
2594 to run. (ACCOUNT, CUSTOMER, DELINQUENCY. ALL). ALL performs the closing
2595 for all the three dunning levels.
2596
2597 clchang updated 04/18/2003 for BILL_TO.
2598 in 11.5.10, one more level BILL_TO for dunning level.
2599
2600 --jsanju 08/04/05 for bug#4505461
2601 --change SQL stmts
2602 ------------------------------------------------------------------------ */
2603 PROCEDURE CLOSE_DUNNINGS(ERRBUF OUT NOCOPY VARCHAR2,
2604 RETCODE OUT NOCOPY VARCHAR2,
2605 DUNNING_LEVEL Varchar2)
2606 IS
2607 BEGIN
2608 SAVEPOINT close_dunn ;
2609 RETCODE := 0 ;
2610
2611 -- IF PG_DEBUG < 10 THEN
2612 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2613 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2614 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS Start <<----------');
2615 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2616 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2617 END IF;
2618
2619 if dunning_level in ('ALL', 'DELINQUENCY') then
2620 Begin
2621 UPDATE iex_dunnings idun
2622 set status = 'CLOSE'
2623 where idun.dunning_level = 'DELINQUENCY'
2624 and idun.status = 'OPEN'
2625 and EXISTS
2626 (select delinquency_id
2627 from iex_delinquencies id
2628 where status = 'CURRENT'
2629 and id.delinquency_id = dunning_object_id) ;
2630
2631 FND_FILE.PUT_LINE(FND_FILE.LOG, ' <<< DELINQUENCY >>>
2632 Number Of Dunnings Closed..>> '|| SQL%ROWCOUNT) ;
2633 Exception
2634 WHEN OTHERS then
2635 ERRBUF := 'CLOSE DUNNINGS << DELINQUENCY >> Error Code = '
2636 || SQLCODE || ' Error Msg ' || SQLERRM ;
2637 -- IF PG_DEBUG < 10 THEN
2638 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2639 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Delinquency : ' || ERRBUF);
2640 END IF;
2641 RETCODE := -1 ;
2642 ROLLBACK TO close_dunn ;
2643
2644 -- IF PG_DEBUG < 10 THEN
2645 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2646 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2647 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2648 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2649 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2650 END IF;
2651 End ;
2652 end if ;
2653
2654
2655 -- clchang updated 04/18/2003 for BILL_TO level
2656
2657 if dunning_level in ('ALL', 'BILL_TO') then
2658 Begin
2659
2660
2661 UPDATE iex_dunnings idun
2662 set status = 'CLOSE'
2663 where idun.dunning_level = 'BILL_TO'
2664 and idun.status = 'OPEN'
2665 and dunning_object_id IN
2666 (select DISTINCT id.customer_site_use_id
2667 from iex_delinquencies id
2668 where NOT EXISTS
2669 (SELECT customer_site_use_id
2670 FROM IEX_DELINQUENCIES id2
2671 where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
2672 and id2.customer_site_use_id = id.customer_site_use_id)) ;
2673
2674
2675
2676
2677
2678
2679
2680 FND_FILE.PUT_LINE(FND_FILE.LOG, ' <<< BILL_TO >>>
2681 Number Of Dunnings Closed..>> '|| SQL%ROWCOUNT) ;
2682 Exception
2683 WHEN OTHERS then
2684 ERRBUF := 'CLOSE DUNNINGS << BILL_TO >> Error Code = '
2685 || SQLCODE || ' Error Msg ' || SQLERRM ;
2686 -- IF PG_DEBUG < 10 THEN
2687 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2688 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Bill To : ' || ERRBUF);
2689 END IF;
2690 RETCODE := -1 ;
2691 ROLLBACK TO close_dunn ;
2692
2693 -- IF PG_DEBUG < 10 THEN
2694 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2695 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2696 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2697 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2698 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2699 END IF;
2700 End ;
2701 end if ;
2702 -- clchang updated 04/18/2003 for BILL_TO level -- end
2703
2704 if dunning_level in ('ALL', 'ACCOUNT') then
2705 Begin
2706 UPDATE iex_dunnings idun
2707 set status = 'CLOSE'
2708 where idun.dunning_level = 'ACCOUNT'
2709 and idun.status = 'OPEN'
2710 and dunning_object_id IN
2711 (select DISTINCT id.cust_account_id
2712 from iex_delinquencies id
2713 where NOT EXISTS
2714 (SELECT CUST_ACCOUNT_ID
2715 FROM IEX_DELINQUENCIES id2
2716 where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
2717 and id2.cust_account_id = id.cust_account_id)) ;
2718
2719 FND_FILE.PUT_LINE(FND_FILE.LOG, ' <<< ACCOUNT >>>
2720 Number Of Dunnings Closed..>> '|| SQL%ROWCOUNT) ;
2721 Exception
2722 WHEN OTHERS then
2723 ERRBUF := 'CLOSE DUNNINGS << ACCOUNT >> Error Code = '
2724 || SQLCODE || ' Error Msg ' || SQLERRM ;
2725 -- IF PG_DEBUG < 10 THEN
2726 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2727 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Account : ' || ERRBUF);
2728 END IF;
2729 RETCODE := -1 ;
2730 ROLLBACK TO close_dunn ;
2731
2732 -- IF PG_DEBUG < 10 THEN
2733 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2734 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2735 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2736 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2737 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2738 END IF;
2739 End ;
2740 end if ;
2741
2742 if dunning_level in ('ALL', 'CUSTOMER') then
2743 Begin
2744
2745 -- BEGIN jsanju for 4505461 -change sql stmt
2746
2747 /*
2748 UPDATE iex_dunnings idun
2749 set status = 'CLOSE'
2750 where idun.dunning_level = 'CUSTOMER'
2751 and idun.status = 'OPEN'
2752 and dunning_object_id IN
2753 (select DISTINCT id.party_cust_id
2754 from iex_delinquencies id
2755 where NOT EXISTS
2756 (SELECT PARTY_CUST_ID
2757 FROM IEX_DELINQUENCIES id2
2758 where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
2759 and id2.party_cust_id = id.party_cust_id)) ;
2760 */
2761 UPDATE IEX_DUNNINGS IDUN
2762 SET STATUS = 'CLOSE'
2763 WHERE IDUN.DUNNING_LEVEL = 'CUSTOMER'
2764 AND IDUN.STATUS = 'OPEN'
2765 and not exists (SELECT 'x'
2766 FROM IEX_DELINQUENCIES ID
2767 where ID.PARTY_CUST_ID = idun.DUNNING_OBJECT_ID
2768 and ID.STATUS IN ('PREDELINQUENT', 'DELINQUENT'));
2769
2770 --END jsanju for 4505461 -change sql stmt
2771
2772
2773 FND_FILE.PUT_LINE(FND_FILE.LOG, ' <<< CUSTOMER >>>
2774 Number Of Dunnings Closed..>> '|| SQL%ROWCOUNT) ;
2775 Exception
2776 WHEN OTHERS then
2777 ERRBUF := 'CLOSE DUNNINGS << CUSTOMER >> Error Code = '
2778 || SQLCODE || ' Error Msg ' || SQLERRM ;
2779 -- IF PG_DEBUG < 10 THEN
2780 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2781 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Customer : ' || ERRBUF);
2782 END IF;
2783 RETCODE := -1 ;
2784 ROLLBACK TO close_dunn ;
2785
2786 -- IF PG_DEBUG < 10 THEN
2787 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2788 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2789 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2790 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2791 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2792 END IF;
2793 End ;
2794 end if ;
2795
2796 Commit;
2797
2798 -- IF PG_DEBUG < 10 THEN
2799 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2800 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2801 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End NORMAL <<----------');
2802 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2803 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2804 END IF;
2805
2806 Exception
2807 WHEN OTHERS then
2808 ERRBUF := 'CLOSE DUNNINGS - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
2809 -- IF PG_DEBUG < 10 THEN
2810 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2811 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || ERRBUF);
2812 END IF;
2813 RETCODE := -1 ;
2814 ROLLBACK TO close_dunn ;
2815
2816 -- IF PG_DEBUG < 10 THEN
2817 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2818 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2819 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2820 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2821 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2822 END IF;
2823 END ;
2824
2825 --
2826 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2827 --
2828 /*
2829 || Overview: Clean up delinquency_buffers table this will use the batch size profile and do one request or all table
2830 ||
2831 || Parameter: P_REQUEST is the request Id we need to delete, if it is -1 we delete the whole table
2832 ||
2833 || Source Tables: None
2834 ||
2835 || Target Tables: IEX_DEL_BUFFERS
2836 ||
2837 || Creation date: 01/25/05 3:29:PM
2838 ||
2839 || Major Modifications: when who what
2840 || 01/25/05 acaraujo created
2841 */
2842 PROCEDURE CLEAR_BUFFERS2(P_REQUEST IN NUMBER) IS
2843
2844 iCount number;
2845 iSize number;
2846 i number;
2847
2848 --Begin base bug 6902192 barathsr 13-Nov-2008
2849 --For big customers delete from IEX_DEL_BUFFERS process takes hours
2850
2851 l_del_count number;
2852 l_truncate_table VARCHAR2(60);
2853
2854 l_out_status varchar2(100);
2855 l_out_industry varchar2(100);
2856 l_out_oracle_schema varchar2(100);
2857 x boolean;
2858
2859 Begin
2860
2861 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: cleaning delinquency buffers +');
2862 FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX_DELINQUENCY_PUB: cleaning delinquency buffers +');
2863
2864 select count(1) into l_del_count
2865 from fnd_conc_req_summary_v
2866 where program_application_id = 695 and
2867 program_short_name in ('IEXDLMGB', 'IEX_SCORE_OBJECTS') and
2868 phase_code in ('P', 'R')
2869 and status_code <> 'Q'; -- changed for bug 9251590
2870
2871 IEX_DEBUG_PUB.logMessage('Running IEXDLMGB count = ' || l_del_count);
2872 FND_FILE.PUT_LINE(FND_FILE.LOG,'Running IEXDLMGB count = ' || l_del_count);
2873
2874 if (((p_request = -1) and (l_del_count = 0)) OR ((p_request <> -1) and (l_del_count <= 2))) then
2875 if ((p_request = -1) and (l_del_count = 0)) then
2876 IEX_DEBUG_PUB.logMessage('IEX: Clear Delinquency Buffers Table cp is running and IEX: Scoring Engine Harness cp is not running, so truncating IEX_DEL_BUFFERS table...');
2877 FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX: Clear Delinquency Buffers Table cp is running and IEX: Scoring Engine Harness cp is not running, so truncating IEX_DEL_BUFFERS table...');
2878 else
2879 IEX_DEBUG_PUB.logMessage('This is the last running instance of IEXDLMGB - truncating IEX_DEL_BUFFERS table...');
2880 FND_FILE.PUT_LINE(FND_FILE.LOG,'This is the last running instance of IEXDLMGB - truncating IEX_DEL_BUFFERS table...');
2881 end if;
2882 --select OWNER || '.' || TABLE_NAME into l_truncate_table from sys.all_tables where table_name = 'IEX_DEL_BUFFERS';
2883 --EXECUTE IMMEDIATE 'truncate table ' || l_truncate_table;
2884 x := fnd_installation.get_app_info ('FND',l_out_status,l_out_industry,l_out_oracle_schema);
2885
2886 IEX_DEBUG_PUB.logMessage('IEX Schema:'||l_out_oracle_schema);
2887
2888 IEX_DEBUG_PUB.LogMessage('Truncating IEX_DEL_BUFFERS');
2889
2890 ad_ddl.do_ddl( l_out_oracle_schema,
2891 'IEX',
2892 AD_DDL.TRUNCATE_TABLE,
2893 'TRUNCATE TABLE IEX_DEL_BUFFERS',
2894 'IEX_DEL_BUFFERS' );
2895
2896 IEX_DEBUG_PUB.LogMessage('Truncated IEX_DEL_BUFFERS');
2897 IEX_DEBUG_PUB.logMessage('Done');
2898 else
2899 IEX_DEBUG_PUB.logMessage('There are other running instances of IEXDLMGB - quiting');
2900 FND_FILE.PUT_LINE(FND_FILE.LOG,'There are other running instances of IEXDLMGB - quiting');
2901 end if;
2902
2903 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: cleaning delinquency buffers -');
2904 FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX_DELINQUENCY_PUB: cleaning delinquency buffers -');
2905
2906 Exception
2907 When others then
2908 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: CLEAR_BUFFERS2: cleaning failed due to ' || sqlcode || ' ' || sqlerrm);
2909 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_DELINQUENCY_PUB: CLEAR_BUFFERS2: cleaning failed due to ' || sqlcode || ' ' || sqlerrm);
2910
2911
2912 End CLEAR_BUFFERS2;
2913
2914 -- End base bug 6902192 barathsr 13-Nov-2008
2915 --
2916 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2917 --
2918
2919 -- clchang added 10/28/04 to fix gscc warning
2920 BEGIN
2921
2922 G_USER_ID := FND_GLOBAL.User_Id;
2923 G_Batch_Size := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '1000'));
2924 v_line := '--------------------------------------------------------------' ;
2925 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2926
2927 END IEX_DELINQUENCY_PUB;