[Home] [Help]
PACKAGE BODY: APPS.IEX_DELINQUENCY_PUB
Source
1 PACKAGE BODY IEX_DELINQUENCY_PUB AS
2 /* $Header: iexpdelb.pls 120.14.12010000.5 2009/01/19 11:16:43 barathsr 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
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 AND NOT EXISTS
995 (select 1
996 from dual
997 where IDS.del_status = vf_current)
998 AND HZCA.cust_account_id = ARPS.customer_id
999 AND ARPS.payment_schedule_id = IDB.score_object_id
1000 AND IDB.score_value between
1001 IDS.score_value_low and IDS.score_value_high
1002 AND IDB.score_id = IDS.score_id
1003 AND IDB.request_id = p_request_id ;
1004
1005
1006 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Delinquencies Created..>> '|| SQL%ROWCOUNT) ;
1007 l_del_insert_count := SQL%ROWCOUNT ;
1008
1009 /* ================= Debug Message ==================== */
1010 -- IF PG_DEBUG < 10 THEN
1011 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1012 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1013 END IF;
1014 -- IF PG_DEBUG < 10 THEN
1015 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1016 IEX_DEBUG_PUB.LOGMESSAGE
1017 ('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1018 END IF;
1019 -- IF PG_DEBUG < 10 THEN
1020 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1021 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1022 END IF;
1023 /* ================= Debug Message ==================== */
1024
1025 Exception
1026 WHEN OTHERS then
1027 ERRBUF := 'INSERT - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1028 -- IF PG_DEBUG < 10 THEN
1029 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1030 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1031 END IF;
1032 RETCODE := -1 ;
1033 ROLLBACK TO del_sp ;
1034
1035 -- IF PG_DEBUG < 10 THEN
1036 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1037 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1038 END IF;
1039
1040 --
1041 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1042 --
1043
1044 CLEAR_BUFFERS2(p_request_id);
1045
1046 --DELETE FROM IEX_DEL_BUFFERS
1047 --WHERE request_id = p_request_id ;
1048 --
1049 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1050 --
1051
1052 -- IF PG_DEBUG < 10 THEN
1053 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1054 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' ||
1055 to_char(SQL%ROWCOUNT));
1056 END IF;
1057 Commit;
1058 return;
1059 End;
1060
1061 --
1062 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1063 --
1064 EXIT WHEN c_scores%NOTFOUND;
1065 END LOOP;
1066 close c_scores;
1067 --
1068 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1069 --
1070
1071 --ELSE
1072 elsif v_object = 'IEX_CONTRACTS' then
1073
1074 begin
1075
1076 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1077 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_CONTRACTS: ' || 'Starting Contract..... ');
1078 END IF;
1079
1080 SELECT ico.object_id, ids.del_status, ico.delinquency_status
1081 BULK COLLECT INTO vt_contract_id, vt_buf_status, vt_del_status
1082 FROM iex_case_objects ico,
1083 iex_del_buffers idb,
1084 iex_del_statuses ids
1085 WHERE idb.score_id = ids.score_id
1086 AND idb.score_value BETWEEN ids.score_value_low and ids.score_value_high
1087 AND idb.score_object_id = ico.object_id
1088 AND ico.object_code = 'CONTRACTS'
1089 AND idb.request_id = p_request_id;
1090 null;
1091
1092 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1093 IEX_DEBUG_PUB.LOGMESSAGE
1094 ('MANAGE_DELINQUENCIES: ' || 'CONTRACT - Row Count after Update Select ' || to_char(vt_contract_id.COUNT));
1095 END IF;
1096
1097 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1098 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1099 END IF;
1100
1101 if v_debug_level < 11 then
1102 for i in 1..vt_del_id.count
1103 LOOP
1104
1105 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1106 IEX_DEBUG_PUB.LOGMESSAGE
1107 ('MANAGE_DELINQUENCIES: ' || ' CONTRACT Id = ' || to_char(vt_contract_id(i))||
1108 ' Delinquency Id = ' || to_char(vt_del_id(i)) ||
1109 ' Buf Status = ' || vt_buf_status(i) ||
1110 'Del Status = ' || vt_del_status(i));
1111 END IF;
1112 END LOOP ;
1113 End If ;
1114
1115 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1116 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1117 END IF;
1118
1119 EXCEPTION
1120 WHEN OTHERS THEN
1121 ERRBUF := 'CONTRACT - Matching Delinquencies Select -->' || SQLCODE || ' Error Msg ' || SQLERRM ;
1122
1123 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1124 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1125 END IF;
1126 RETCODE := -1 ;
1127
1128 ROLLBACK TO del_sp ;
1129
1130 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1131 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1132 END IF;
1133
1134 Commit;
1135 return;
1136 end;
1137
1138 begin
1139
1140 FORALL v_count in 1..vt_contract_id.count
1141 UPDATE IEX_CASE_OBJECTS
1142 SET delinquency_status = vt_buf_status(v_count),
1143 last_update_date = v_today,
1144 last_updated_by = v_user_id,
1145 object_version_number = object_version_number + 1,
1146 request_id = p_request_id
1147 WHERE object_id = vt_contract_id(v_count);
1148
1149 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Contracts Updated..>> '|| vt_contract_id.count) ;
1150 l_del_update_count := vt_del_id.count ;
1151
1152 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1153 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1154 END IF;
1155 if v_debug_level < 11 then
1156 FOR i in 1..vt_del_id.count
1157 LOOP
1158
1159 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1160 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' || to_char(vt_del_id(i)) || ' is ' ||
1161 to_char(SQL%BULK_ROWCOUNT(i)));
1162 END IF;
1163 END LOOP ;
1164 End If ;
1165
1166 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1167 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1168 END IF;
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 ERRBUF := 'CONTRACT Updating... - Matching Delinquencies Update --> ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1173
1174 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1175 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1176 END IF;
1177 RETCODE := -1;
1178 ROLLBACK TO del_sp;
1179
1180 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1181 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1182 END IF;
1183 CLEAR_BUFFERS2(p_request_id);
1184 Commit;
1185 return;
1186 end;
1187
1188 elsif v_object = 'IEX_CASES' then
1189 /* *******************************************************************
1190 HANDLING FOR CASE
1191 ******************************************************************* */
1192 BEGIN
1193
1194 -- IF PG_DEBUG < 10 THEN
1195 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1196 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Starting Cases ');
1197 END IF;
1198
1199 -- Selects All the matching rows between buffer and Delinquencies table
1200 -- except when the statuses are same or when buffer status is PREDELINQUENT
1201 -- and delinquency table status is DELINQUENT.
1202 /* 6785378
1203 SELECT
1204 id.delinquency_id,
1205 ids.del_status buf_status,
1206 id.status del_status,
1207 id.case_id
1208 BULK COLLECT INTO
1209 vt_del_id,
1210 vt_buf_status,
1211 vt_del_status,
1212 vt_case_id
1213 FROM iex_delinquencies id,
1214 iex_del_buffers idb,
1215 iex_del_statuses ids
1216 where
1217 NOT EXISTS
1218 (select 1
1219 from dual
1220 where (id.status = vf_delinquent and ids.del_status = vf_predelinquent)
1221 OR id.status = ids.del_status)
1222 and idb.score_id = ids.score_id
1223 and idb.score_value between ids.score_value_low
1224 and ids.score_value_high
1225 and idb.score_object_id = id.case_id
1226 and idb.request_id = p_request_id ;
1227 */
1228 SELECT ic.cas_id, ids.del_status, ic.status_code
1229 BULK COLLECT INTO vt_case_id, vt_buf_status, vt_del_status
1230 FROM iex_cases_all_b ic,
1231 iex_del_buffers idb,
1232 iex_del_statuses ids
1233 WHERE idb.score_id = ids.score_id
1234 AND idb.score_value BETWEEN ids.score_value_low and ids.score_value_high
1235 AND idb.score_object_id = ic.cas_id
1236 AND idb.request_id = p_request_id;
1237 /* ================= Debug Message ====================*/
1238
1239 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1240 IEX_DEBUG_PUB.LOGMESSAGE
1241 -- ('MANAGE_DELINQUENCIES: ' || 'CASE - Row Count after Update Select ' || to_char(vt_del_id.COUNT));
1242 ('MANAGE_DELINQUENCIES: ' || 'CASE - Row Count after Update Select ' || to_char(vt_case_id.COUNT));
1243 END IF;
1244
1245 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1246 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1247 END IF;
1248
1249 if v_debug_level < 11 then
1250 for i in 1..vt_del_id.count
1251 LOOP
1252
1253 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1254 IEX_DEBUG_PUB.LOGMESSAGE
1255 ('MANAGE_DELINQUENCIES: ' || ' Case Id = ' || to_char(vt_case_id(i))||
1256 ' Delinquency Id = ' || to_char(vt_del_id(i)) ||
1257 ' Buf Status = ' || vt_buf_status(i) ||
1258 'Del Status = ' || vt_del_status(i));
1259 END IF;
1260 END LOOP ;
1261 End If ;
1262
1263 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1264 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1265 END IF;
1266 /*================= Debug Message ====================== */
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 ERRBUF := 'CASE - Matching Delinquencies Select -->' || SQLCODE || ' Error Msg ' || SQLERRM ;
1271
1272 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1273 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1274 END IF;
1275 RETCODE := -1 ;
1276
1277 ROLLBACK TO del_sp ;
1278
1279 --
1280 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1281 --
1282
1283 CLEAR_BUFFERS2(p_request_id);
1284
1285 --DELETE FROM IEX_DEL_BUFFERS
1286 --WHERE request_id = p_request_id ;
1287 --
1288 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1289 --
1290
1291 -- IF PG_DEBUG < 10 THEN
1292 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1293 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1294 END IF;
1295
1296 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1297 -- IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
1298 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1299
1300 Commit;
1301 return;
1302 END;
1303
1304 BEGIN
1305 -- Once all the Existing PreDelinquent Rows are found then
1306 -- Update them
1307 /* 6785378
1308 FORALL v_count in 1..vt_del_id.count
1309 UPDATE IEX_DELINQUENCIES
1310 SET status = vt_buf_status(v_count),
1311 last_update_date = v_today,
1312 last_updated_by = v_user_id,
1313 dunn_yn = decode(vt_buf_status(v_count), vf_current, 'N'),
1314 object_version_number = object_version_number + 1,
1315 request_id = p_request_id
1316 WHERE delinquency_id = vt_del_id(v_count);
1317 */
1318 FORALL v_count in 1..vt_case_id.count
1319 UPDATE IEX_CASES_ALL_B
1320 SET status_code = vt_buf_status(v_count),
1321 last_update_date = v_today,
1322 last_updated_by = v_user_id,
1323 object_version_number = object_version_number + 1,
1324 request_id = p_request_id
1325 WHERE cas_id = vt_case_id(v_count);
1326
1327
1328 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Delinquencies Updated..>> '|| vt_del_id.count) ;
1329 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Cases Updated..>> '|| vt_case_id.count) ; -- 6785378
1330 l_del_update_count := vt_del_id.count ;
1331
1332 /* ================= Debug Message =========================*/
1333 -- IF PG_DEBUG < 10 THEN
1334 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1335 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1336 END IF;
1337 if v_debug_level < 11 then
1338 FOR i in 1..vt_del_id.count
1339 LOOP
1340 -- IF PG_DEBUG < 10 THEN
1341 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1342 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Rows Updated for ' ||
1343 to_char(vt_del_id(i)) || ' is ' ||
1344 to_char(SQL%BULK_ROWCOUNT(i)));
1345 END IF;
1346 END LOOP ;
1347 End If ;
1348 -- IF PG_DEBUG < 10 THEN
1349 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1350 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1351 END IF;
1352 /* ================= Debug Message =========================== */
1353
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 ERRBUF := 'CASE - Matching Delinquencies Update --> ' ||
1357 SQLCODE || ' Error Msg ' || SQLERRM ;
1358 -- IF PG_DEBUG < 10 THEN
1359 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1360 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1361 END IF;
1362 RETCODE := -1;
1363 ROLLBACK TO del_sp;
1364
1365 -- IF PG_DEBUG < 10 THEN
1366 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1367 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1368 END IF;
1369
1370 --
1371 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1372 --
1373
1374 CLEAR_BUFFERS2(p_request_id);
1375
1376 --DELETE FROM IEX_DEL_BUFFERS
1377 --WHERE request_id = p_request_id ;
1378 --
1379 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1380 --
1381
1382 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1383 --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
1384 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1385 Commit;
1386 return;
1387 END;
1388
1389
1390 /* 6785378
1391 BEGIN
1392
1393 -- clchang updated 04/18/2003 for BILL_TO
1394 -- get cust_account_id and customer_site_use_id
1395 -- by OKL, column_name = 'CUSTOMER_ACCOUNT' and 'BILL_TO_ADDRESS_ID'
1396 -- will be not null;
1397
1398 -- Simple Insert for Payment Schedule Id
1399 INSERT INTO IEX_DELINQUENCIES_ALL
1400 ( DELINQUENCY_ID,
1401 LAST_UPDATE_DATE,
1402 LAST_UPDATED_BY,
1403 CREATION_DATE,
1404 CREATED_BY,
1405 OBJECT_VERSION_NUMBER,
1406 DUNN_YN,
1407 PARTY_CUST_ID,
1408 CUST_ACCOUNT_ID,
1409 CUSTOMER_SITE_USE_ID, -- added by clchang for BILL_TO
1410 CASE_ID,
1411 STATUS,
1412 ORG_ID,
1413 SOURCE_PROGRAM_NAME,
1414 SCORE_ID ,
1415 SCORE_VALUE ,
1416 REQUEST_ID )
1417 SELECT
1418 IEX_DELINQUENCIES_S.NEXTVAL,
1419 v_today ,
1420 v_user_id ,
1421 v_today ,
1422 v_user_id ,
1423 1 ,
1424 'Y' ,
1425 ICV.party_id ,
1426 ICD.column_value,
1427 ICD2.column_value,
1428 IDB.score_object_id ,
1429 IDS.del_status ,
1430 --v_org_id ,
1431 ICV.org_id, --Modified for MOAC
1432 l_source_module,
1433 IDB.Score_id,
1434 IDB.score_value ,
1435 p_REQUEST_ID
1436 FROM IEX_DEL_BUFFERS IDB,
1437 IEX_CASES_VL ICV,
1438 IEX_CASE_DEFINITIONS ICD,
1439 IEX_CASES_VL ICV2,
1440 IEX_CASE_DEFINITIONS ICD2,
1441 IEX_DEL_STATUSES IDS
1442 WHERE
1443 NOT EXISTS
1444 (Select 1
1445 from iex_delinquencies
1446 where case_id = idb.score_object_id)
1447 AND NOT EXISTS
1448 (select 1
1449 from dual
1450 where IDS.del_status = vf_current)
1451 AND ICV.cas_id = IDB.score_object_id
1452 AND IDB.score_value between
1453 IDS.score_value_low and IDS.score_value_high
1454 AND IDB.score_id = IDS.score_id
1455 AND IDB.request_id = p_request_id
1456 AND ICV.cas_id = ICD.cas_id
1457 AND ICD.column_name = 'CUSTOMER_ACCOUNT'
1458 AND ICV2.cas_id = ICV.cas_id
1459 AND ICV2.cas_id = ICD2.cas_id
1460 AND ICD2.column_name = 'BILL_TO_ADDRESS_ID';
1461
1462
1463 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Number Of Delinquencies Created..>> '|| SQL%ROWCOUNT) ;
1464 l_del_insert_count := SQL%ROWCOUNT ;
1465
1466 -- ================= Debug Message ========================
1467 -- IF PG_DEBUG < 10 THEN
1468 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1469 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1470 END IF;
1471 -- IF PG_DEBUG < 10 THEN
1472 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1473 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of Rows Inserted --> ' || to_char(SQL%ROWCOUNT));
1474 END IF;
1475 IF PG_DeBUG < 10 THEN
1476 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1477 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || v_line);
1478 END IF;
1479 END IF;
1480 -- ================= Debug Message ===========================
1481
1482 Exception
1483 WHEN OTHERS then
1484 ERRBUF := 'INSERT - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
1485 -- IF PG_DEBUG < 10 THEN
1486 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1487 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1488 END IF;
1489 RETCODE := -1 ;
1490 ROLLBACK TO del_sp ;
1491
1492 -- IF PG_DEBUG < 10 THEN
1493 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1494 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'CASE INSERT >> Deleting Buffer Table after RollBack due to Error');
1495 END IF;
1496
1497 --
1498 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1499 --
1500
1501 CLEAR_BUFFERS2(p_request_id);
1502
1503 --DELETE FROM IEX_DEL_BUFFERS
1504 --WHERE request_id = p_request_id ;
1505 --
1506 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1507 --
1508
1509 -- IF PG_DEBUG < 10 THEN
1510 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1511 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1512 END IF;
1513 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
1514 --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(p_request_id, 'MANAGE_DELINQUENCIES',ERRBUF) ;
1515 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
1516 Commit;
1517 return;
1518 End;
1519 */ -- 6785378
1520
1521
1522 End If;
1523 End If;
1524
1525
1526 -- ______________________ CALLING THE WORKFLOW __________________________
1527 -- IF PG_DEBUG < 10 THEN
1528 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1529 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Calling the Work Flow........ ');
1530 END IF;
1531 -- IF PG_DEBUG < 10 THEN
1532 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1533 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Delinquency Table Count After Mangement Process ' || to_char(vt_del_id.count));
1534 END IF;
1535
1536 -- Filter out NOCOPY the delinquency ids that are not the Workflow Candidates.
1537 if vt_del_id.count > 0 then
1538
1539 for ct in 1..vt_del_id.COUNT
1540 LOOP
1541 if vt_del_status(ct) IN (vf_delinquent, vf_predelinquent)
1542 AND (vt_buf_status(ct) = vf_current) THEN
1543 -- F PG_DEBUG < 10 THEN
1544 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1545 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Del Id for Workflow >> ' || to_char(vt_del_id(ct)));
1546 END IF;
1547 if vt_wf_del_id.COUNT = 0 then
1548 vt_wf_del_id(1) := vt_del_id(ct) ;
1549 else
1550 vt_wf_del_id(vt_wf_del_id.LAST + 1) := vt_del_id(ct) ;
1551 end If ;
1552 End If ;
1553 END LOOP ;
1554 End If ;
1555
1556 -- ================= Debug Message ===========================
1557 -- IF PG_DEBUG < 10 THEN
1558 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1559 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '------------------------------------------------------------');
1560 END IF;
1561 -- IF PG_DEBUG < 10 THEN
1562 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1563 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Number of WorlFlow Candidate Rows --> ' || to_char(vt_wf_del_id.COUNT));
1564 END IF;
1565
1566 -- Launch the Workflow and Close Promises only when required
1567 if vt_wf_del_id.COUNT > 0 then
1568
1569 FOR p in 1..vt_wf_del_id.COUNT
1570 LOOP
1571 -- IF PG_DEBUG < 10 THEN
1572 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1573 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Delinquency Id --> ' || to_char(vt_wf_del_id(p)));
1574 END IF;
1575 END LOOP ;
1576 -- IF PG_DEBUG < 10 THEN
1577 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1578 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '------------------------------------------------------------');
1579 END IF;
1580 /* ================= Debug Message =========================== */
1581
1582
1583 -- Launch the WorkFlow
1584 IEX_WF_DEL_CUR_STATUS_NOTE_PUB.START_WORKFLOW
1585 (p_api_version => 1.0,
1586 p_init_msg_list => FND_API.G_FALSE,
1587 p_commit => FND_API.G_FALSE,
1588 p_delinquency_ids => vt_wf_del_id,
1589 x_return_status => l_return_status,
1590 x_msg_count => l_msg_count,
1591 x_msg_data => l_msg_data);
1592
1593 -- IF PG_DEBUG < 10 THEN
1594 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1595 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'WorlFlow Status --> ' || l_return_status);
1596 END IF;
1597
1598 -- Close the Promises
1599 IEX_PROMISES_BATCH_PUB.CLOSE_PROMISES
1600 (p_api_version => 1.0,
1601 p_init_msg_list => FND_API.G_FALSE,
1602 p_commit => FND_API.G_FALSE,
1603 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1604 x_return_status => l_return_status,
1605 x_msg_count => l_msg_count,
1606 x_msg_data => l_msg_data ,
1607 p_delinq_tbl => vt_wf_del_id );
1608
1609 -- IF PG_DEBUG < 10 THEN
1610 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1611 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Close Promises Status --> ' || l_return_status);
1612 END IF;
1613
1614 End If ;
1615
1616 -- IF PG_DEBUG < 10 THEN
1617 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1618 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table');
1619 END IF;
1620 --
1621 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1622 --
1623 CLEAR_BUFFERS2(p_request_id);
1624 --DELETE FROM IEX_DEL_BUFFERS WHERE request_id = p_request_id ;
1625 --
1626 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1627 --
1628 -- IF PG_DEBUG < 10 THEN
1629 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1630 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleted Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1631 END IF;
1632
1633 if not isRefreshProgramsRunning then -- Added for bug#7133605 by schekuri on 17-Jun-2008 --Added by PNAVEENK
1634 --Bug5237039. Fix by LKKUMAR on 25-May-2006. Start
1635 FND_FILE.PUT_LINE(FND_FILE.LOG,'Starting to update ar_trx_bal_summary....'); --Added by PNAVEENK
1636 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1637 IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = 1...');
1638 END IF;
1639
1640 if vt_del_id.count > 0 then -- 6785378
1641 BEGIN
1642 OPEN c_cust_account_id_1;
1643 LOOP
1644 FETCH c_cust_account_id_1 BULK COLLECT INTO
1645 l_cust_account_id_1 LIMIT G_BATCH_SIZE;
1646 IF l_cust_account_id_1.count = 0 THEN
1647 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1648 IEX_DEBUG_PUB.LOGMESSAGE('Exit after Updating ar_trx_bal_summary with reference_1 = 1...');
1649 END IF;
1650 CLOSE c_cust_account_id_1;
1651 EXIT;
1652 ELSE
1653 FORALL I IN l_cust_account_id_1.first..l_cust_account_id_1.last
1654 UPDATE AR_TRX_BAL_SUMMARY ARS
1655 SET REFERENCE_1 = 1
1656 WHERE CUST_ACCOUNT_ID = l_cust_account_id_1(I);
1657
1658 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1659 IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = 1');
1660 END IF;
1661 END IF;
1662 END LOOP;
1663 EXCEPTION WHEN OTHERS THEN
1664 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1665 IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
1666 END IF;
1667 END;
1668
1669 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1670 IEX_DEBUG_PUB.LOGMESSAGE('Starting to update ar_trx_bal_summary with reference_1 = Null...');
1671 END IF;
1672 BEGIN
1673 OPEN c_cust_account_id_n;
1674 LOOP
1675 FETCH c_cust_account_id_n BULK COLLECT INTO
1676 l_cust_account_id_n LIMIT G_BATCH_SIZE;
1677 IF l_cust_account_id_n.count = 0 THEN
1678 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1679 IEX_DEBUG_PUB.LOGMESSAGE('Exit after Update ar_trx_bal_summary on complete with reference_1 = Null...');
1680 END IF;
1681 CLOSE c_cust_account_id_n;
1682 EXIT;
1683 ELSE
1684 FORALL I IN l_cust_account_id_n.first..l_cust_account_id_n.last
1685 UPDATE AR_TRX_BAL_SUMMARY ARS
1686 SET REFERENCE_1 = Null
1687 WHERE CUST_ACCOUNT_ID = l_cust_account_id_n(I);
1688 FND_FILE.PUT_LINE(FND_FILE.LOG,SQL%ROWCOUNT || ' Rows updated in ar_trx_bal_summary with reference_1 = NULL');
1689 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1690 IEX_DEBUG_PUB.LOGMESSAGE(SQL%ROWCOUNT || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');
1691 END IF;
1692 END IF;
1693 END LOOP;
1694 EXCEPTION WHEN OTHERS THEN
1695 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1696 IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = Null');
1697 END IF;
1698 END;
1699 --Bug5237039. Fix by LKKUMAR on 25-May-2006. End.
1700 end if; -- 6785378
1701
1702 COMMIT;
1703 -- Start Bug 5874874 gnramasa 25-Apr-2007
1704
1705 if vt_del_id.count > 0 then -- 6785378
1706 iex_uwq_pop_sum_tbl_pvt.refresh_summary_incr
1707 (x_errbuf,
1708 x_retcode,
1709 NULL,
1710 'DLN');
1711 -- End Bug 5874874 gnramasa 25-Apr-2007
1712 end if; -- 6785378
1713 end if; --End if isRefreshProgramsRunning -- Added by PNAVEENK
1714
1715
1716 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling the Dunning Closing Process.... ') ;
1717 CLOSE_DUNNINGS(RETCODE, ERRBUF, 'ALL') ;
1718 FND_FILE.PUT_LINE(FND_FILE.LOG, '******* Dunning Closing Process Result *******') ;
1719 FND_FILE.PUT_LINE(FND_FILE.LOG, ' RETCODE >>> ' || RETCODE) ;
1720 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ERRBUF >>> ' || ERRBUF) ;
1721
1722
1723
1724 if l_del_insert_count > 0 OR l_del_update_count > 0 then
1725 if l_enable_business_events = 'Y' then
1726 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Business Events Processing Enabled... ' ) ;
1727
1728 --jsanju 09/15/05 , logic has changed, raise event directly for the request id
1729 ---do not call the concurrent program. There will be one event for every scoring engine
1730 -- request and not for every del created or updated.
1731 --set concurrent status to warning if event is not raised.
1732 /* l_business_event_req_id :=
1733 FND_REQUEST.SUBMIT_REQUEST(
1734 APPLICATION => 'IEX',
1735 PROGRAM => 'IEX:RAISE_DEL_CREATE_EVENT' ,
1736 DESCRIPTION => 'Business Event when Delinquencies are Created or Closed',
1737 START_TIME => sysdate,
1738 SUB_REQUEST => false,
1739 ARGUMENT1 => p_Request_id); */
1740
1741
1742 RAISE_EVENT(
1743 P_REQUEST_ID =>p_request_id,
1744 p_del_create_count =>l_del_insert_count,
1745 p_del_update_count =>l_del_update_count,
1746 x_return_status => l_return_status,
1747 x_msg_count => l_msg_count,
1748 x_msg_data => l_msg_data );
1749
1750
1751 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1752 if l_return_Status = FND_API.G_RET_STS_SUCCESS THEN
1753 fnd_file.put_line(fnd_file.log,'Business Event raised Successfully');
1754 else
1755 fnd_file.put_line(fnd_file.log,'Business Event not raised ');
1756 request_status := fnd_concurrent.set_completion_status('WARNING'
1757 , 'Business Event Not Raised');
1758 end if;
1759 END IF ;
1760
1761 else
1762 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Business Events Processing Disabled... ' ) ;
1763 End If ;
1764 End If ;
1765
1766 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1767 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1768 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE MANAGE_DELINQUENCIES End <<----------');
1769 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || '--------------------------------------------------------------');
1770 IEX_DEBUG_PUB.logMessage('MANAGE_DELINQUENCIES: ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1771 END IF;
1772
1773 EXCEPTION
1774 WHEN Others then
1775 RETCODE := -1 ;
1776 ERRBUF := 'MANAGE_DELINQUENCIES >> WHEN OTHERS - ERROR - ' || SQLCODE || ' ' || SQLERRM ;
1777 -- IF PG_DEBUG < 10 THEN
1778 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1779 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || ERRBUF);
1780 END IF;
1781 ROLLBACK TO del_sp ;
1782
1783 -- IF PG_DEBUG < 10 THEN
1784 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1785 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deleting Buffer Table after RollBack due to Error');
1786 END IF;
1787
1788 --
1789 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1790 --
1791 CLEAR_BUFFERS2(p_request_id);
1792 --DELETE FROM IEX_DEL_BUFFERS WHERE request_id = p_request_id ;
1793 --
1794 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
1795 --
1796
1797 -- IF PG_DEBUG < 10 THEN
1798 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1799 IEX_DEBUG_PUB.LOGMESSAGE('MANAGE_DELINQUENCIES: ' || 'Deletd Buffer Row Count >> ' || to_char(SQL%ROWCOUNT));
1800 END IF;
1801
1802 Commit;
1803 END MANAGE_DELINQUENCIES;
1804
1805 /*------------------------------------------------------------------------
1806 11.5.7 Independent Delinquency Creation Process
1807 ------------------------------------------------------------------------ */
1808 PROCEDURE Create_Ind_Delinquency
1809 ( p_api_version IN NUMBER ,
1810 p_init_msg_list IN VARCHAR2,
1811 p_commit IN VARCHAR2,
1812 p_validation_level IN NUMBER ,
1813 x_return_status OUT NOCOPY VARCHAR2 ,
1814 x_msg_count OUT NOCOPY NUMBER ,
1815 x_msg_data OUT NOCOPY VARCHAR2 ,
1816 p_source_module IN VARCHAR2 ,
1817 p_party_id IN Number ,
1818 p_object_code IN Varchar2 ,
1819 p_object_id_tbl IN IEX_UTILITIES.t_numbers,
1820 x_del_id_tbl OUT NOCOPY IEX_UTILITIES.t_numbers)
1821 IS
1822 v_first Number := 0 ;
1823 v_last Number := 0 ;
1824
1825 v_org_id Number ;
1826 v_today Date ;
1827 v_user_id Number ;
1828 v_count Number := 1 ;
1829
1830 l_api_name varchar2(50);
1831
1832 v_error_msg varchar2(200);
1833 --Begin Bug 6446848 08-Dec-2008 barathsr
1834 l_deln_id number;
1835 l_pay_sch_id number;
1836 l_org_id number;
1837 l_cust_acct_id number; --29/12
1838 l_cust_site_use_id number;--29/12
1839 --End Bug 6446848 08-Dec-2008 barathsr
1840
1841 Begin
1842
1843 -- clchang 10/28/04 fixed gscc warning
1844 l_api_name := 'Create_Ind_Delinquency';
1845
1846
1847 if p_object_id_tbl.COUNT > 0 then
1848 v_first := p_object_id_tbl.FIRST ;
1849 v_last := p_object_id_tbl.LAST ;
1850 End If ;
1851
1852
1853 -- Standard Start of API savepoint
1854 SAVEPOINT IEX_IND_DEL ;
1855
1856 -- Standard call to check for call compatibility.
1857 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
1858 p_api_version,
1859 l_api_name,
1860 G_PKG_NAME)
1861 THEN
1862 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1863 END IF;
1864
1865 -- Initialize message list if p_init_msg_list is set to TRUE.
1866 IF FND_API.to_Boolean(p_init_msg_list) THEN
1867 FND_MSG_PUB.initialize;
1868 END IF;
1869
1870 -- Initialize API return status to SUCCESS
1871 x_return_status := FND_API.G_RET_STS_SUCCESS;
1872
1873 if x_del_id_tbl.COUNT > 0 then
1874 x_del_id_tbl.DELETE ;
1875 End IF ;
1876
1877 Select
1878 --fnd_profile.value('ORG_ID'), --Commneted for MOAC
1879 NVL(fnd_profile.value('USER_ID'), -1),
1880 sysdate
1881 into
1882 --v_org_id , --Commneted for MOAC
1883 v_user_id ,
1884 v_today
1885 From dual ;
1886
1887 -- Loop Through the Table of Cases and Create Delinquencies.
1888 IF p_object_code = 'IEX_CASE' then
1889 FOR cnt IN v_first..v_last
1890 LOOP
1891
1892 Select IEX_DELINQUENCIES_S.NEXTVAL
1893 into x_del_id_tbl(v_count)
1894 From dual ;
1895
1896 INSERT INTO IEX_DELINQUENCIES_ALL
1897 (DELINQUENCY_ID ,
1898 LAST_UPDATE_DATE ,
1899 LAST_UPDATED_BY ,
1900 CREATION_DATE ,
1901 CREATED_BY ,
1902 OBJECT_VERSION_NUMBER,
1903 DUNN_YN ,
1904 PARTY_CUST_ID ,
1905 CUST_ACCOUNT_ID ,
1906 CASE_ID ,
1907 STATUS ,
1908 ORG_ID ,
1909 SOURCE_PROGRAM_NAME )
1910 VALUES
1911 (x_del_id_tbl(v_count) ,
1912 v_today ,
1913 v_user_id ,
1914 v_today ,
1915 v_user_id ,
1916 1 ,
1917 'N' ,
1918 p_party_id ,
1919 NULL ,
1920 p_object_id_tbl(cnt),
1921 vf_delinquent ,
1922 v_org_id ,
1923 p_source_module ) ;
1924
1925 v_count := v_count + 1 ;
1926 END LOOP ;
1927 --Begin Bug 6446848 08-Dec-2008 barathsr
1928 else
1929
1930 IEX_DEBUG_PUB.LOGMESSAGE('In for Current invoice insertion....');
1931
1932 select IEX_DELINQUENCIES_S.NEXTVAL
1933 into l_deln_id
1934 from dual;
1935
1936 if p_object_id_tbl.count = 1 then
1937 for i in v_first..v_last loop
1938
1939 begin--29/12
1940 select org_id,customer_id,customer_site_use_id
1941 into l_org_id,l_cust_acct_id,l_cust_site_use_id --29/12
1942 from ar_payment_schedules_all
1943 where payment_schedule_id= p_object_id_tbl(i);
1944
1945 IEX_DEBUG_PUB.LOGMESSAGE('Org_id--->'||l_org_id);
1946 IEX_DEBUG_PUB.LOGMESSAGE('Cust_account_id-->'||l_cust_acct_id);
1947 IEX_DEBUG_PUB.LOGMESSAGE('Customer_site_use_id-->'||l_cust_site_use_id);
1948 INSERT INTO IEX_DELINQUENCIES_ALL
1949 (DELINQUENCY_ID ,
1950 LAST_UPDATE_DATE ,
1951 LAST_UPDATED_BY ,
1952 CREATION_DATE ,
1953 CREATED_BY ,
1954 OBJECT_VERSION_NUMBER,
1955 DUNN_YN ,
1956 PARTY_CUST_ID ,
1957 CUST_ACCOUNT_ID ,
1958 payment_schedule_id,
1959 STATUS ,
1960 ORG_ID ,
1961 SOURCE_PROGRAM_NAME,
1962 CUSTOMER_SITE_USE_ID)
1963 VALUES
1964 (l_deln_id,
1965 v_today ,
1966 v_user_id ,
1967 v_today ,
1968 v_user_id ,
1969 1 ,
1970 'N' ,
1971 p_party_id ,
1972 l_cust_acct_id, --29/12
1973 p_object_id_tbl(i),
1974 vf_current,
1975 l_org_id ,
1976 p_source_module,
1977 l_cust_site_use_id) ; --29/12
1978 exception
1979 when others then --29/12
1980 IEX_DEBUG_PUB.LOGMESSAGE('Error in CURRENT invoice selection/insertion activity.....');
1981 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1982 end;--29/12
1983 end loop;
1984 end if;
1985
1986 x_del_id_tbl(1):=l_deln_id;
1987
1988 --End Bug 6446848 08-Dec-2008 barathsr
1989 END IF ;
1990
1991 -- Standard check for p_commit
1992 IF FND_API.to_Boolean(p_commit) THEN
1993 COMMIT WORK;
1994 ELSE
1995 ROLLBACK TO IEX_IND_DEL ;
1996 END IF;
1997
1998 -- Debug Message
1999 IEX_DEBUG_PUB.LogMessage('PUB: ' || l_api_name || ' end');
2000 IEX_DEBUG_PUB.LogMessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2001 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2002
2003 EXCEPTION
2004 WHEN FND_API.G_EXC_ERROR THEN
2005 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2006 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: Create_Ind_Delinquency: Expected Error ' || sqlerrm);
2007 END IF;
2008 RAISE FND_API.G_EXC_ERROR;
2009 ROLLBACK TO IEX_IND_DEL;
2010
2011 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2012 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2013 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: Create_Ind_Delinquency: Unexpected Error ' || sqlerrm);
2014 END IF;
2015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2016 ROLLBACK TO IEX_IND_DEL;
2017
2018 WHEN OTHERS THEN
2019 v_error_msg := SQLCODE || ' Error Msg ' || SQLERRM ;
2020 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2021 IEX_DEBUG_PUB.LOGMESSAGE(v_error_msg) ;
2022 END IF;
2023 ROLLBACK TO IEX_IND_DEL;
2024 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
2025 --IEX_CONC_REQUEST_MSG_PKG.LOG_ERROR(-9999, 'CREATE_IND_DELINQUENCIES', v_error_msg) ;
2026 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
2027 COMMIT ;
2028 End ;
2029
2030
2031 /*
2032 || Overview: will update the delinquency header table once the
2033 || scoring engine for delinquencies is run
2034 ||
2035 || Parameter: p_request_id => request_id of score engine run
2036 ||
2037 || Source Tables: IEX_DEL_BUFFERS
2038 ||
2039 || Target Tables: IEX_DELINQUENCIES_ALL
2040 ||
2041 || Creation date: 03/19/02 10:04:AM
2042 ||
2043 || Major Modifications: when who what
2044 || 03/19/02 10:04:AM raverma created
2045 */
2046 procedure SCORE_DELINQUENCIES (ERRBUF OUT NOCOPY VARCHAR2,
2047 RETCODE OUT NOCOPY VARCHAR2,
2048 p_request_id Number) IS
2049
2050 type t_ids is table of number
2051 index by binary_integer;
2052
2053 v_score_objects t_ids;
2054 v_score_values t_ids;
2055 l_score_id number;
2056 nCount number;
2057
2058 cursor c_scores(p_request_id in number)
2059 is
2060 select score_object_id,
2061 score_value
2062 from iex_del_buffers
2063 where request_id = p_request_id;
2064 BEGIN
2065
2066 RETCODE := 0;
2067
2068 -- IF PG_DEBUG < 10 THEN
2069 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2070 iex_debug_pub.logMessage('IEX_DELINQUENCY_PUB.SCORE_DELINQUENCIES');
2071 END IF;
2072 -- IF PG_DEBUG < 10 THEN
2073 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2074 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'StartTime: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
2075 END IF;
2076 -- get the score id for the engine
2077 Select score_id into l_score_id
2078 from iex_del_buffers
2079 Where request_id = p_request_id and
2080 rownum = 1;
2081
2082 -- IF PG_DEBUG < 10 THEN
2083 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2084 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'ScoreID is ' || l_score_id);
2085 END IF;
2086 -- IF PG_DEBUG < 10 THEN
2087 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2088 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'bridge value is ' || p_request_id);
2089 END IF;
2090
2091 -- now get the data
2092 open c_scores(p_request_id);
2093 LOOP
2094 FETCH c_scores
2095 BULK COLLECT INTO
2096 v_score_objects,
2097 v_score_values
2098 LIMIT G_Batch_Size;
2099
2100 nCount := v_score_objects.count;
2101
2102 for i in 1..nCount
2103 loop
2104 -- IF PG_DEBUG < 10 THEN
2105 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2106 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || v_score_objects(i));
2107 END IF;
2108 -- IF PG_DEBUG < 10 THEN
2109 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2110 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || v_score_values(i));
2111 END IF;
2112 end loop;
2113
2114 -- IF PG_DEBUG < 10 THEN
2115 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2116 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'Count is ' || nCount);
2117 END IF;
2118 FORALL r in 1..nCount
2119 UPDATE IEX_DELINQUENCIES_ALL
2120 SET SCORE_ID = l_score_id,
2121 Score_value = v_score_values(r),
2122 last_update_date = sysdate,
2123 request_id = FND_GLOBAL.CONC_REQUEST_ID
2124 WHERE DELINQUENCY_ID = v_score_objects(r);
2125
2126 EXIT WHEN c_scores%NOTFOUND;
2127 END LOOP;
2128 close c_scores;
2129 /*
2130 SELECT score_object_id,
2131 score_value
2132 BULK COLLECT INTO
2133 LIMIT NVL(FND_PROFILE.VALUE('IEX_BATCH_SIZE'), 1000)
2134 FROM iex_del_buffers
2135 WHERE request_id = p_request_id;
2136 */
2137 -- IF PG_DEBUG < 10 THEN
2138 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2139 IEX_DEBUG_PUB.logMessage('SCORE_DELINQUENCIES: ' || 'Deleting from buffers');
2140 END IF;
2141 --
2142 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2143 --
2144 CLEAR_BUFFERS2(p_request_id);
2145 --Delete From IEX_DEL_BUFFERS
2146 -- Where request_id = p_request_id;
2147 --
2148 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2149 --
2150
2151 -- IF PG_DEBUG < 10 THEN
2152 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2153 iex_debug_pub.logMessage('IEX_DELINQUENCY_PUB.SCORE_DELINQUENCIES');
2154 END IF;
2155 -- IF PG_DEBUG < 10 THEN
2156 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2157 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'ENDTime: ' || to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
2158 END IF;
2159 Exception
2160 When NO_DATA_FOUND then
2161 -- IF PG_DEBUG < 10 THEN
2162 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2163 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'no data found ' || sqlerrm);
2164 END IF;
2165 RETCODE := -1;
2166 ERRBUF := sqlerrm;
2167 When others then
2168 -- IF PG_DEBUG < 10 THEN
2169 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2170 iex_debug_pub.logMessage('SCORE_DELINQUENCIES: ' || 'other error ' || sqlerrm);
2171 END IF;
2172 RETCODE := -1;
2173 ERRBUF := sqlerrm;
2174
2175 END SCORE_DELINQUENCIES;
2176
2177 /*********************
2178 Set UWQ status for promises
2179 ***********************/
2180 PROCEDURE SHOW_IN_UWQ(
2181 P_API_VERSION IN NUMBER,
2182 P_INIT_MSG_LIST IN VARCHAR2,
2183 P_COMMIT IN VARCHAR2,
2184 P_VALIDATION_LEVEL IN NUMBER,
2185 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2186 X_MSG_COUNT OUT NOCOPY NUMBER,
2187 X_MSG_DATA OUT NOCOPY VARCHAR2,
2188 P_DELINQUENCY_ID_TBL IN DBMS_SQL.NUMBER_TABLE,
2189 P_UWQ_STATUS IN VARCHAR2,
2190 P_NO_DAYS IN NUMBER)
2191 IS
2192 l_api_name CONSTANT VARCHAR2(30) := 'SHOW_IN_UWQ';
2193 l_api_version CONSTANT NUMBER := 1.0;
2194 l_return_status varchar2(10);
2195 l_msg_count number;
2196 l_msg_data varchar2(200);
2197
2198 l_validation_item varchar2(100);
2199 l_days NUMBER;
2200 l_set_status_date DATE;
2201 l_status varchar2(20);
2202 nCount number;
2203
2204 Type refCur is Ref Cursor;
2205 l_cursor refCur;
2206 l_SQL VARCHAR2(10000);
2207 l_broken_promises DBMS_SQL.NUMBER_TABLE;
2208 i number;
2209 j number;
2210 l_uwq_active_date date;
2211 l_uwq_complete_date date;
2212 l_level VARCHAR2(80);
2213
2214 CURSOR c_get_level IS
2215 SELECT PREFERENCE_VALUE FROM IEX_APP_PREFERENCES_VL WHERE PREFERENCE_NAME = 'COLLECTIONS STRATEGY LEVEL';
2216
2217 begin
2218
2219 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2220 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': start');
2221 END IF;
2222
2223 -- Standard start of API savepoint
2224 SAVEPOINT SHOW_IN_UWQ_PVT;
2225
2226 -- Standard call to check for call compatibility
2227 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2229 END IF;
2230
2231 -- Initialize message list if p_init_msg_list is set to TRUE
2232 IF FND_API.To_Boolean(p_init_msg_list) THEN
2233 FND_MSG_PUB.initialize;
2234 END IF;
2235
2236 -- Initialize API return status to success
2237 l_return_status := FND_API.G_RET_STS_SUCCESS;
2238
2239 -- START OF BODY OF API
2240
2241 -- validating uwq status
2242 l_validation_item := 'P_UWQ_STATUS';
2243 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2244 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new uwq status: ' || P_UWQ_STATUS);
2245 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2246 END IF;
2247 if P_UWQ_STATUS is null then
2248 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2249 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2250 END IF;
2251 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2252 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2253 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2254 FND_MSG_PUB.Add;
2255 RAISE FND_API.G_EXC_ERROR;
2256 end if;
2257
2258 -- validating table of promises
2259 l_validation_item := 'P_DELINQUENCY_ID_TBL';
2260 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2261 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': count of P_DELINQUENCY_ID_TBL: ' || P_DELINQUENCY_ID_TBL.count);
2262 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2263 END IF;
2264 if P_DELINQUENCY_ID_TBL.count = 0 then
2265 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2266 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2267 END IF;
2268 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2269 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2270 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2271 FND_MSG_PUB.Add;
2272 RAISE FND_API.G_EXC_ERROR;
2273 end if;
2274
2275 -- validating p_days
2276 l_validation_item := 'P_NO_DAYS';
2277 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2278 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': P_NO_DAYS: ' || P_NO_DAYS);
2279 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
2280 END IF;
2281 if P_NO_DAYS is not null and P_NO_DAYS < 0 then
2282 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2283 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
2284 END IF;
2285 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
2286 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
2287 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
2288 FND_MSG_PUB.Add;
2289 RAISE FND_API.G_EXC_ERROR;
2290 end if;
2291
2292 -- set number of days
2293 if P_NO_DAYS is null then
2294 l_days := to_number(nvl(fnd_profile.value('IEX_UWQ_DEFAULT_PENDING_DAYS'), '0'));
2295 else
2296 l_days := P_NO_DAYS;
2297 end if;
2298 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2299 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': number of days: ' || l_days);
2300 END IF;
2301 l_set_status_date := sysdate + l_days;
2302 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2303 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': set status date: ' || l_set_status_date);
2304 END IF;
2305
2306 -- check for status
2307 if P_UWQ_STATUS = 'ACTIVE' then
2308 l_uwq_active_date := NULL;
2309 l_uwq_complete_date := NULL;
2310 elsif P_UWQ_STATUS = 'PENDING' then
2311 l_uwq_active_date := l_set_status_date;
2312 l_uwq_complete_date := NULL;
2313 elsif P_UWQ_STATUS = 'COMPLETE' then
2314 l_uwq_active_date := NULL;
2315 l_uwq_complete_date := sysdate;
2316 end if;
2317
2318 nCount := p_delinquency_id_tbl.count;
2319 if nCount > 0 then
2320 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2321 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_active_date: ' || l_uwq_active_date);
2322 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_complete_date: ' || l_uwq_complete_date);
2323 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': updating promise details...');
2324 END IF;
2325 FORALL i in 1..nCount
2326 update iex_delinquencies_all
2327 set UWQ_STATUS = P_UWQ_STATUS,
2328 UWQ_ACTIVE_DATE = l_uwq_active_date,
2329 UWQ_COMPLETE_DATE = l_uwq_complete_date,
2330 last_update_date = sysdate,
2331 last_updated_by = G_USER_ID
2332 where
2333 delinquency_id = p_delinquency_id_tbl(i);
2334
2335 -- start of fix for bug 5874874 gnramasa 25-Apr-07
2336 OPEN c_get_level;
2337 FETCH c_get_level INTO l_level;
2338 CLOSE c_get_level;
2339 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Strateg Level = ' || l_level);
2340
2341 IF l_level = 'CUSTOMER' THEN
2342
2343 FORALL i in 1..nCount
2344 update IEX_DLN_UWQ_SUMMARY sum
2345 set
2346 sum.active_delinquencies =
2347 (SELECT 1
2348 FROM dual
2349 WHERE EXISTS
2350 (SELECT 1
2351 FROM iex_delinquencies_all
2352 WHERE party_cust_id = sum.party_id
2353 AND status IN('DELINQUENT', 'PREDELINQUENT')
2354 AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
2355 AND uwq_status = 'PENDING'))
2356 )
2357 ),
2358 sum.complete_delinquencies =
2359 (SELECT 1
2360 FROM dual
2361 WHERE EXISTS
2362 (SELECT 1
2363 FROM iex_delinquencies_all
2364 WHERE party_cust_id = sum.party_id
2365 AND status IN('DELINQUENT', 'PREDELINQUENT')
2366 AND(uwq_status = 'COMPLETE'
2367 AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2368 ),
2369 sum.pending_delinquencies =
2370 (SELECT 1
2371 FROM dual
2372 WHERE EXISTS
2373 (SELECT 1
2374 FROM iex_delinquencies_all
2375 WHERE party_cust_id = sum.party_id
2376 AND status IN('DELINQUENT', 'PREDELINQUENT')
2377 AND(uwq_status = 'PENDING'
2378 AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
2379 )
2380 WHERE sum.party_id = (select party_cust_id
2381 from iex_delinquencies_all
2382 where delinquency_id = p_delinquency_id_tbl(i));
2383
2384 ELSIF l_level = 'ACCOUNT' THEN
2385
2386 FORALL i in 1..nCount
2387 update IEX_DLN_UWQ_SUMMARY sum
2388 set
2389 sum.active_delinquencies =
2390 (SELECT 1
2391 FROM dual
2392 WHERE EXISTS
2393 (SELECT 1
2394 FROM iex_delinquencies_all
2395 WHERE party_cust_id = party_id
2396 AND status IN('DELINQUENT', 'PREDELINQUENT')
2397 AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
2398 AND uwq_status = 'PENDING'))
2399 )
2400 ),
2401 sum.complete_delinquencies =
2402 (SELECT 1
2403 FROM dual
2404 WHERE EXISTS
2405 (SELECT 1
2406 FROM iex_delinquencies_all
2407 WHERE cust_account_id = sum.cust_account_id
2408 AND status IN('DELINQUENT', 'PREDELINQUENT')
2409 AND(uwq_status = 'COMPLETE'
2410 AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2411 ),
2412 sum.pending_delinquencies =
2413 (SELECT 1
2414 FROM dual
2415 WHERE EXISTS
2416 (SELECT 1
2417 FROM iex_delinquencies_all
2418 WHERE cust_account_id = sum.cust_account_id
2419 AND status IN('DELINQUENT', 'PREDELINQUENT')
2420 AND(uwq_status = 'PENDING'
2421 AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
2422 )
2423 WHERE sum.cust_account_id = (select cust_account_id
2424 from iex_delinquencies_all
2425 where delinquency_id = p_delinquency_id_tbl(i));
2426
2427 ELSIF l_level = 'BILL_TO' THEN
2428
2429 FORALL i in 1..nCount
2430 update IEX_DLN_UWQ_SUMMARY sum
2431 set
2432 sum.active_delinquencies =
2433 (SELECT 1
2434 FROM dual
2435 WHERE EXISTS
2436 (SELECT 1
2437 FROM iex_delinquencies_all
2438 WHERE customer_site_use_id = sum.site_use_id
2439 AND status IN('DELINQUENT', 'PREDELINQUENT')
2440 AND(uwq_status IS NULL OR uwq_status = 'ACTIVE' OR(TRUNC(uwq_active_date) <= TRUNC(sysdate)
2441 AND uwq_status = 'PENDING'))
2442 )
2443 ),
2444 sum.complete_delinquencies =
2445 (SELECT 1
2446 FROM dual
2447 WHERE EXISTS
2448 (SELECT 1
2449 FROM iex_delinquencies_all
2450 WHERE customer_site_use_id = sum.site_use_id
2451 AND status IN('DELINQUENT', 'PREDELINQUENT')
2452 AND(uwq_status = 'COMPLETE'
2453 AND(TRUNC(uwq_complete_date) + fnd_profile.VALUE('IEX_UWQ_COMPLETION_DAYS') > TRUNC(sysdate))))
2454 ),
2455 sum.pending_delinquencies =
2456 (SELECT 1
2457 FROM dual
2458 WHERE EXISTS
2459 (SELECT 1
2460 FROM iex_delinquencies_all
2461 WHERE customer_site_use_id = sum.site_use_id
2462 AND status IN('DELINQUENT', 'PREDELINQUENT')
2463 AND(uwq_status = 'PENDING'
2464 AND(TRUNC(uwq_active_date) > TRUNC(sysdate))))
2465 )
2466 WHERE sum.site_use_id = (select customer_site_use_id
2467 from iex_delinquencies_all
2468 where delinquency_id = p_delinquency_id_tbl(i));
2469
2470 END IF;
2471
2472 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Updated ' || SQL%ROWCOUNT || ' rows in IEX_DLN_UWQ_SUMMARY');
2473 -- end of fix for bug 5874874 gnramasa 25-Apr-07
2474
2475 else
2476 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2477 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');
2478 END IF;
2479 end if;
2480
2481 -- END OF BODY OF API
2482
2483 -- Standard check of p_commit.
2484 IF FND_API.To_Boolean( p_commit ) THEN
2485 COMMIT WORK;
2486 END IF;
2487
2488 x_return_status := l_return_status;
2489 -- Standard call to get message count and if count is 1, get message info
2490 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2491 p_count => x_msg_count,
2492 p_data => x_msg_data);
2493
2494 EXCEPTION
2495 WHEN FND_API.G_EXC_ERROR THEN
2496 ROLLBACK TO SHOW_IN_UWQ_PVT;
2497 x_return_status := FND_API.G_RET_STS_ERROR;
2498 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2499 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2500 ROLLBACK TO SHOW_IN_UWQ_PVT;
2501 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2502 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2503 WHEN OTHERS THEN
2504 ROLLBACK TO SHOW_IN_UWQ_PVT;
2505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2506 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2507 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2508 END IF;
2509 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2510 end;
2511
2512 /*------------------------------------------------------------------------
2513 CLOSE Dunnings Process
2514 This process closes all the dunnings that are open for the delinquencies
2515 that are in CURRENT status. This makes the Dunning, Delinquency records
2516 consistant. Dunning level passed as parameter decides which dunning level
2517 to run. (ACCOUNT, CUSTOMER, DELINQUENCY. ALL). ALL performs the closing
2518 for all the three dunning levels.
2519
2520 clchang updated 04/18/2003 for BILL_TO.
2521 in 11.5.10, one more level BILL_TO for dunning level.
2522
2523 --jsanju 08/04/05 for bug#4505461
2524 --change SQL stmts
2525 ------------------------------------------------------------------------ */
2526 PROCEDURE CLOSE_DUNNINGS(ERRBUF OUT NOCOPY VARCHAR2,
2527 RETCODE OUT NOCOPY VARCHAR2,
2528 DUNNING_LEVEL Varchar2)
2529 IS
2530 BEGIN
2531 SAVEPOINT close_dunn ;
2532 RETCODE := 0 ;
2533
2534 -- IF PG_DEBUG < 10 THEN
2535 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2536 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2537 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS Start <<----------');
2538 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'Start time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2539 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2540 END IF;
2541
2542 if dunning_level in ('ALL', 'DELINQUENCY') then
2543 Begin
2544 UPDATE iex_dunnings idun
2545 set status = 'CLOSE'
2546 where idun.dunning_level = 'DELINQUENCY'
2547 and idun.status = 'OPEN'
2548 and EXISTS
2549 (select delinquency_id
2550 from iex_delinquencies id
2551 where status = 'CURRENT'
2552 and id.delinquency_id = dunning_object_id) ;
2553
2554 FND_FILE.PUT_LINE(FND_FILE.LOG, ' <<< DELINQUENCY >>>
2555 Number Of Dunnings Closed..>> '|| SQL%ROWCOUNT) ;
2556 Exception
2557 WHEN OTHERS then
2558 ERRBUF := 'CLOSE DUNNINGS << DELINQUENCY >> Error Code = '
2559 || SQLCODE || ' Error Msg ' || SQLERRM ;
2560 -- IF PG_DEBUG < 10 THEN
2561 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2562 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Delinquency : ' || ERRBUF);
2563 END IF;
2564 RETCODE := -1 ;
2565 ROLLBACK TO close_dunn ;
2566
2567 -- IF PG_DEBUG < 10 THEN
2568 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2569 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2570 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2571 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2572 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2573 END IF;
2574 End ;
2575 end if ;
2576
2577
2578 -- clchang updated 04/18/2003 for BILL_TO level
2579
2580 if dunning_level in ('ALL', 'BILL_TO') then
2581 Begin
2582
2583
2584 UPDATE iex_dunnings idun
2585 set status = 'CLOSE'
2586 where idun.dunning_level = 'BILL_TO'
2587 and idun.status = 'OPEN'
2588 and dunning_object_id IN
2589 (select DISTINCT id.customer_site_use_id
2590 from iex_delinquencies id
2591 where NOT EXISTS
2592 (SELECT customer_site_use_id
2593 FROM IEX_DELINQUENCIES id2
2594 where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
2595 and id2.customer_site_use_id = id.customer_site_use_id)) ;
2596
2597
2598
2599
2600
2601
2602
2603 FND_FILE.PUT_LINE(FND_FILE.LOG, ' <<< BILL_TO >>>
2604 Number Of Dunnings Closed..>> '|| SQL%ROWCOUNT) ;
2605 Exception
2606 WHEN OTHERS then
2607 ERRBUF := 'CLOSE DUNNINGS << BILL_TO >> Error Code = '
2608 || SQLCODE || ' Error Msg ' || SQLERRM ;
2609 -- IF PG_DEBUG < 10 THEN
2610 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2611 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Bill To : ' || ERRBUF);
2612 END IF;
2613 RETCODE := -1 ;
2614 ROLLBACK TO close_dunn ;
2615
2616 -- IF PG_DEBUG < 10 THEN
2617 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2618 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2619 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2620 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2621 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2622 END IF;
2623 End ;
2624 end if ;
2625 -- clchang updated 04/18/2003 for BILL_TO level -- end
2626
2627 if dunning_level in ('ALL', 'ACCOUNT') then
2628 Begin
2629 UPDATE iex_dunnings idun
2630 set status = 'CLOSE'
2631 where idun.dunning_level = 'ACCOUNT'
2632 and idun.status = 'OPEN'
2633 and dunning_object_id IN
2634 (select DISTINCT id.cust_account_id
2635 from iex_delinquencies id
2636 where NOT EXISTS
2637 (SELECT CUST_ACCOUNT_ID
2638 FROM IEX_DELINQUENCIES id2
2639 where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
2640 and id2.cust_account_id = id.cust_account_id)) ;
2641
2642 FND_FILE.PUT_LINE(FND_FILE.LOG, ' <<< ACCOUNT >>>
2643 Number Of Dunnings Closed..>> '|| SQL%ROWCOUNT) ;
2644 Exception
2645 WHEN OTHERS then
2646 ERRBUF := 'CLOSE DUNNINGS << ACCOUNT >> Error Code = '
2647 || SQLCODE || ' Error Msg ' || SQLERRM ;
2648 -- IF PG_DEBUG < 10 THEN
2649 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2650 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Account : ' || ERRBUF);
2651 END IF;
2652 RETCODE := -1 ;
2653 ROLLBACK TO close_dunn ;
2654
2655 -- IF PG_DEBUG < 10 THEN
2656 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2657 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2658 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2659 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2660 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2661 END IF;
2662 End ;
2663 end if ;
2664
2665 if dunning_level in ('ALL', 'CUSTOMER') then
2666 Begin
2667
2668 -- BEGIN jsanju for 4505461 -change sql stmt
2669
2670 /*
2671 UPDATE iex_dunnings idun
2672 set status = 'CLOSE'
2673 where idun.dunning_level = 'CUSTOMER'
2674 and idun.status = 'OPEN'
2675 and dunning_object_id IN
2676 (select DISTINCT id.party_cust_id
2677 from iex_delinquencies id
2678 where NOT EXISTS
2679 (SELECT PARTY_CUST_ID
2680 FROM IEX_DELINQUENCIES id2
2681 where id2.status IN ('PREDELINQUENT', 'DELINQUENT')
2682 and id2.party_cust_id = id.party_cust_id)) ;
2683 */
2684 UPDATE IEX_DUNNINGS IDUN
2685 SET STATUS = 'CLOSE'
2686 WHERE IDUN.DUNNING_LEVEL = 'CUSTOMER'
2687 AND IDUN.STATUS = 'OPEN'
2688 and not exists (SELECT 'x'
2689 FROM IEX_DELINQUENCIES ID
2690 where ID.PARTY_CUST_ID = idun.DUNNING_OBJECT_ID
2691 and ID.STATUS IN ('PREDELINQUENT', 'DELINQUENT'));
2692
2693 --END jsanju for 4505461 -change sql stmt
2694
2695
2696 FND_FILE.PUT_LINE(FND_FILE.LOG, ' <<< CUSTOMER >>>
2697 Number Of Dunnings Closed..>> '|| SQL%ROWCOUNT) ;
2698 Exception
2699 WHEN OTHERS then
2700 ERRBUF := 'CLOSE DUNNINGS << CUSTOMER >> Error Code = '
2701 || SQLCODE || ' Error Msg ' || SQLERRM ;
2702 -- IF PG_DEBUG < 10 THEN
2703 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2704 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS for Customer : ' || ERRBUF);
2705 END IF;
2706 RETCODE := -1 ;
2707 ROLLBACK TO close_dunn ;
2708
2709 -- IF PG_DEBUG < 10 THEN
2710 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2711 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2712 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2713 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2714 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2715 END IF;
2716 End ;
2717 end if ;
2718
2719 Commit;
2720
2721 -- IF PG_DEBUG < 10 THEN
2722 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2723 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2724 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End NORMAL <<----------');
2725 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || v_line);
2726 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2727 END IF;
2728
2729 Exception
2730 WHEN OTHERS then
2731 ERRBUF := 'CLOSE DUNNINGS - Error Code = ' || SQLCODE || ' Error Msg ' || SQLERRM ;
2732 -- IF PG_DEBUG < 10 THEN
2733 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2734 IEX_DEBUG_PUB.LOGMESSAGE('CLOSE_DUNNINGS : ' || ERRBUF);
2735 END IF;
2736 RETCODE := -1 ;
2737 ROLLBACK TO close_dunn ;
2738
2739 -- IF PG_DEBUG < 10 THEN
2740 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2741 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2742 IEX_DEBUG_PUB.LOGMESSAGE('---------->> PROCEDURE CLOSE_DUNNINGS End ERROR <<----------');
2743 IEX_DEBUG_PUB.logMessage('CLOSE_DUNNINGS : ' || 'End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
2744 IEX_DEBUG_PUB.LOGMESSAGE(v_line);
2745 END IF;
2746 END ;
2747
2748 --
2749 -- Begin - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2750 --
2751 /*
2752 || Overview: Clean up delinquency_buffers table this will use the batch size profile and do one request or all table
2753 ||
2754 || Parameter: P_REQUEST is the request Id we need to delete, if it is -1 we delete the whole table
2755 ||
2756 || Source Tables: None
2757 ||
2758 || Target Tables: IEX_DEL_BUFFERS
2759 ||
2760 || Creation date: 01/25/05 3:29:PM
2761 ||
2762 || Major Modifications: when who what
2763 || 01/25/05 acaraujo created
2764 */
2765 PROCEDURE CLEAR_BUFFERS2(P_REQUEST IN NUMBER) IS
2766
2767 iCount number;
2768 iSize number;
2769 i number;
2770
2771 --Begin base bug 6902192 barathsr 13-Nov-2008
2772 --For big customers delete from IEX_DEL_BUFFERS process takes hours
2773
2774 l_del_count number;
2775 l_truncate_table VARCHAR2(60);
2776
2777 Begin
2778
2779 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: cleaning delinquency buffers +');
2780 FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX_DELINQUENCY_PUB: cleaning delinquency buffers +');
2781
2782 select count(1) into l_del_count
2783 from fnd_conc_req_summary_v
2784 where program_application_id = 695 and
2785 program_short_name in ('IEXDLMGB', 'IEX_SCORE_OBJECTS') and
2786 phase_code in ('P', 'R');
2787
2788 IEX_DEBUG_PUB.logMessage('Running IEXDLMGB count = ' || l_del_count);
2789 FND_FILE.PUT_LINE(FND_FILE.LOG,'Running IEXDLMGB count = ' || l_del_count);
2790
2791 if (((p_request = -1) and (l_del_count = 0)) OR ((p_request <> -1) and (l_del_count <= 2))) then
2792 if ((p_request = -1) and (l_del_count = 0)) then
2793 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...');
2794 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...');
2795 else
2796 IEX_DEBUG_PUB.logMessage('This is the last running instance of IEXDLMGB - truncating IEX_DEL_BUFFERS table...');
2797 FND_FILE.PUT_LINE(FND_FILE.LOG,'This is the last running instance of IEXDLMGB - truncating IEX_DEL_BUFFERS table...');
2798 end if;
2799 select OWNER || '.' || TABLE_NAME into l_truncate_table from sys.all_tables where table_name = 'IEX_DEL_BUFFERS';
2800 EXECUTE IMMEDIATE 'truncate table ' || l_truncate_table;
2801 IEX_DEBUG_PUB.logMessage('Done');
2802 else
2803 IEX_DEBUG_PUB.logMessage('There are other running instances of IEXDLMGB - quiting');
2804 FND_FILE.PUT_LINE(FND_FILE.LOG,'There are other running instances of IEXDLMGB - quiting');
2805 end if;
2806
2807 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: cleaning delinquency buffers -');
2808 FND_FILE.PUT_LINE(FND_FILE.LOG,'IEX_DELINQUENCY_PUB: cleaning delinquency buffers -');
2809
2810 Exception
2811 When others then
2812 IEX_DEBUG_PUB.logMessage('IEX_DELINQUENCY_PUB: CLEAR_BUFFERS2: cleaning failed due to ' || sqlcode || ' ' || sqlerrm);
2813 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IEX_DELINQUENCY_PUB: CLEAR_BUFFERS2: cleaning failed due to ' || sqlcode || ' ' || sqlerrm);
2814
2815
2816 End CLEAR_BUFFERS2;
2817
2818 -- End base bug 6902192 barathsr 13-Nov-2008
2819 --
2820 -- End - 01/24/2005 - Andre Araujo - This procedure uses a memory schema uses up all memory available for the session, changing it to chunks
2821 --
2822
2823 -- clchang added 10/28/04 to fix gscc warning
2824 BEGIN
2825
2826 G_USER_ID := FND_GLOBAL.User_Id;
2827 G_Batch_Size := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '1000'));
2828 v_line := '--------------------------------------------------------------' ;
2829 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2830
2831 END IEX_DELINQUENCY_PUB;