[Home] [Help]
PACKAGE BODY: APPS.IEX_DUNNING_PUB
Source
1 PACKAGE BODY IEX_DUNNING_PUB AS
2 /* $Header: iexpdunb.pls 120.11.12010000.3 2008/09/01 09:54:40 gnramasa ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_DUNNING_PUB';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpdunb.pls';
7
8 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
9 PG_DEBUG NUMBER ;
10
11 Procedure WriteLog ( p_msg IN VARCHAR2)
12 IS
13 BEGIN
14
15 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
16 iex_debug_pub.LogMessage (p_msg);
17 END IF;
18
19 END WriteLog;
20
21
22
23 Procedure Create_AG_DN_XREF
24 (p_api_version IN NUMBER := 1.0,
25 p_init_msg_list IN VARCHAR2 ,
26 p_commit IN VARCHAR2 ,
27 P_AG_DN_XREF_TBL IN IEX_DUNNING_PUB.AG_DN_XREF_TBL_TYPE ,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2,
31 x_AG_DN_XREF_ID_TBL OUT NOCOPY IEX_DUNNING_PUB.AG_DN_XREF_ID_TBL_TYPE)
32
33 IS
34 l_api_name CONSTANT VARCHAR2(30) := 'Create_AG_DN_XREF';
35 l_api_version_number CONSTANT NUMBER := 1.0;
36 l_return_status VARCHAR2(1);
37 l_msg_count NUMBER;
38 l_msg_data VARCHAR2(32767);
39 errmsg VARCHAR2(32767);
40 l_AG_DN_XREF_rec IEX_DUNNING_PUB.AG_DN_XREF_REC_TYPE;
41 x_ag_dn_xref_id NUMBER;
42 l_AG_DN_XREF_ID_TBL IEX_DUNNING_PUB.AG_DN_XREF_ID_TBL_TYPE;
43
44
45 BEGIN
46 -- Standard Start of API savepoint
47 SAVEPOINT CREATE_AG_DN_PUB;
48
49 -- Standard call to check for call compatibility.
50 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
51 p_api_version,
52 l_api_name,
53 G_PKG_NAME)
54 THEN
55 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
56 END IF;
57
58
59 -- Initialize message list if p_init_msg_list is set to TRUE.
60 IF FND_API.to_Boolean( p_init_msg_list )
61 THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64
65 -- Debug Message
66 -- added by gnramasa for bug 5661324 14-Mar-07
67 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - CreateAgDn: start ');
68
69
70 -- Initialize API return status to SUCCESS
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 --
74 -- API body
75 --
76
77 for i in 1..p_ag_dn_xref_tbl.count
78 loop
79 l_ag_dn_xref_rec := p_ag_dn_xref_tbl(i);
80
81 IEX_DUNNING_PVT.Create_AG_DN_XREF(
82 p_api_version => p_api_version
83 , p_init_msg_list => p_init_msg_list
84 , p_commit => p_commit
85 , p_ag_dn_xref_rec => l_ag_dn_xref_rec
86 , x_ag_dn_xref_id => x_ag_dn_Xref_id
87 , x_return_status => x_return_status
88 , x_msg_count => x_msg_count
89 , x_msg_data => x_msg_data
90 );
91
92 IF x_return_status = FND_API.G_RET_STS_ERROR then
93 raise FND_API.G_EXC_ERROR;
94 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
95 raise FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97
98 l_ag_dn_xref_id_tbl(i) := x_ag_dn_xref_id;
99
100 END LOOP;
101
102 x_ag_dn_xref_id_tbl := l_ag_dn_xref_id_tbl;
103
104
105 --
106 -- End of API body
107 --
108
109 -- Standard check for p_commit
110 IF FND_API.to_Boolean( p_commit )
111 THEN
112 COMMIT WORK;
113 END IF;
114
115 -- Debug Message
116 -- added by gnramasa for bug 5661324 14-Mar-07
117 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - CreateAgDn: End ');
118
119 -- Standard call to get message count and if count is 1, get message info.
120 FND_MSG_PUB.Count_And_Get
121 ( p_count => x_msg_count,
122 p_data => x_msg_data
123 );
124
125 EXCEPTION
126 WHEN FND_API.G_EXC_ERROR THEN
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 ROLLBACK TO Create_Ag_Dn_PUB;
129 -- added by gnramasa for bug 5661324 14-Mar-07
130 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
131 FND_MSG_PUB.Count_And_Get
132 ( p_count => x_msg_count,
133 p_data => x_msg_data );
134
135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
137 ROLLBACK TO Create_Ag_Dn_PUB;
138 -- added by gnramasa for bug 5661324 14-Mar-07
139 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
140 FND_MSG_PUB.Count_And_Get
141 ( p_count => x_msg_count,
142 p_data => x_msg_data );
143
144 WHEN OTHERS THEN
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
146 ROLLBACK TO Create_Ag_Dn_PUB;
147 -- added by gnramasa for bug 5661324 14-Mar-07
148 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
149 WriteLog('iexpdunb:CreateAgDn:Exc Exception');
150 FND_MSG_PUB.Count_And_Get
151 ( p_count => x_msg_count,
152 p_data => x_msg_data );
153
154 END CREATE_AG_DN_XREF;
155
156
157
158 Procedure Update_AG_DN_XREF
159 (p_api_version IN NUMBER := 1.0,
160 p_init_msg_list IN VARCHAR2 ,
161 p_commit IN VARCHAR2 ,
162 P_AG_DN_XREF_TBL IN IEX_DUNNING_PUB.AG_DN_XREF_TBL_TYPE ,
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_msg_count OUT NOCOPY NUMBER,
165 x_msg_data OUT NOCOPY VARCHAR2)
166 IS
167 l_api_name CONSTANT VARCHAR2(30) := 'Update_AG_DN_XREF';
168 l_api_version_number CONSTANT NUMBER := 1.0;
169 l_return_status VARCHAR2(1);
170 l_msg_count NUMBER;
171 l_msg_data VARCHAR2(32767);
172 errmsg VARCHAR2(32767);
173 l_AG_DN_XREF_rec IEX_DUNNING_PUB.AG_DN_XREF_REC_TYPE ;
174
175 BEGIN
176
177 -- Standard Start of API savepoint
178 SAVEPOINT UPDATE_AG_DN_PUB;
179
180
181 -- Initialize message list if p_init_msg_list is set to TRUE.
182 IF FND_API.to_Boolean( p_init_msg_list )
183 THEN
184 FND_MSG_PUB.initialize;
185 END IF;
186
187 -- Debug Message
188 -- added by gnramasa for bug 5661324 14-Mar-07
189 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn: Start ');
190
191
192 -- Initialize API return status to SUCCESS
193 x_return_status := FND_API.G_RET_STS_SUCCESS;
194
195 --
196 -- Api body
197 --
198
199
200 for i in 1..p_ag_dn_xref_tbl.count
201 loop
202 l_ag_dn_xref_rec := p_ag_dn_xref_tbl(i);
203
204 IEX_DUNNING_PVT.Update_AG_DN_XREF(
205 p_api_version => p_api_version
206 , p_init_msg_list => p_init_msg_list
207 , p_commit => p_commit
208 , p_ag_dn_xref_rec => l_ag_dn_xref_rec
209 , p_ag_dn_xref_id => l_ag_dn_xref_rec.ag_dn_Xref_id
210 , x_return_status => x_return_status
211 , x_msg_count => x_msg_count
212 , x_msg_data => x_msg_data
213 );
214
215 IF x_return_status = FND_API.G_RET_STS_ERROR then
216 raise FND_API.G_EXC_ERROR;
217 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
218 raise FND_API.G_EXC_UNEXPECTED_ERROR;
219 END IF;
220 END LOOP;
221
222 --
223 -- End of API body.
224 --
225
226 -- Standard check for p_commit
227 IF FND_API.to_Boolean( p_commit )
228 THEN
229 COMMIT WORK;
230 END IF;
231
232 -- Debug Message
233 -- Changed by gnramasa for bug 5661324 14-Mar-07
234 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn: end ');
235
236 -- Standard call to get message count and if count is 1, get message info.
237 FND_MSG_PUB.Count_And_Get
238 ( p_count => x_msg_count,
239 p_data => x_msg_data );
240
241 EXCEPTION
242 WHEN FND_API.G_EXC_ERROR THEN
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 ROLLBACK TO Update_Ag_Dn_PUB;
245 -- Changed by gnramasa for bug 5661324 14-Mar-07
246 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
247 errmsg := SQLERRM;
248 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
249 FND_MSG_PUB.Count_And_Get
250 ( p_count => x_msg_count,
251 p_data => x_msg_data );
252
253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 ROLLBACK TO Update_Ag_Dn_PUB;
256 -- Changed by gnramasa for bug 5661324 14-Mar-07
257 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
258 errmsg := SQLERRM;
259 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
260 FND_MSG_PUB.Count_And_Get
261 ( p_count => x_msg_count,
262 p_data => x_msg_data );
263
264 WHEN OTHERS THEN
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266 ROLLBACK TO Update_Ag_Dn_PUB;
267 -- Changed by gnramasa for bug 5661324 14-Mar-07
268 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
269 errmsg := SQLERRM;
270 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
271 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
272 FND_MSG_PUB.Count_And_Get
273 ( p_count => x_msg_count,
274 p_data => x_msg_data );
275
276 END Update_AG_DN_XREF;
277
278
279
280 Procedure Delete_AG_DN_XREF
281 (p_api_version IN NUMBER := 1.0,
282 p_init_msg_list IN VARCHAR2 ,
283 p_commit IN VARCHAR2 ,
284 P_AG_DN_XREF_ID IN NUMBER,
285 x_return_status OUT NOCOPY VARCHAR2,
286 x_msg_count OUT NOCOPY NUMBER,
287 x_msg_data OUT NOCOPY VARCHAR2)
288
289 IS
290
291 l_AG_DN_XREF_id NUMBER ;
292 l_api_name CONSTANT VARCHAR2(30) := 'Delete_AG_DN_XREF';
293 l_api_version_number CONSTANT NUMBER := 1.0;
294 l_return_status VARCHAR2(1);
295 l_msg_count NUMBER;
296 l_msg_data VARCHAR2(32767);
297 errmsg VARCHAR2(32767);
298
299 BEGIN
300 -- Standard Start of API savepoint
301 SAVEPOINT DELETE_AG_DN_PUB;
302
303
304 -- Initialize message list if p_init_msg_list is set to TRUE.
305 IF FND_API.to_Boolean( p_init_msg_list )
306 THEN
307 FND_MSG_PUB.initialize;
308 END IF;
309
310 -- Debug Message
311 -- Changed by gnramasa for bug 5661324 14-Mar-07
312 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start ');
313
314
315 -- Initialize API return status to SUCCESS
316 x_return_status := FND_API.G_RET_STS_SUCCESS;
317
318 --
319 -- Api body
320 --
321 IEX_DUNNING_PVT.Delete_AG_DN_XREF(
322 p_api_version => p_api_version
323 , p_init_msg_list => p_init_msg_list
324 , p_commit => p_commit
325 , p_AG_DN_XREF_id => p_AG_DN_XREF_id
326 , x_return_status => x_return_status
327 , x_msg_count => x_msg_count
328 , x_msg_data => x_msg_data
329 );
330
331 IF x_return_status = FND_API.G_RET_STS_ERROR then
332 raise FND_API.G_EXC_ERROR;
333 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
334 raise FND_API.G_EXC_UNEXPECTED_ERROR;
335 END IF;
336
337 --
338 -- End of API body
339 --
340
341 -- Standard check for p_commit
342 IF FND_API.to_Boolean( p_commit )
343 THEN
344 COMMIT WORK;
345 END IF;
346
347 -- Debug Message
348 -- Changed by gnramasa for bug 5661324 14-Mar-07
349 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End ');
350
351 FND_MSG_PUB.Count_And_Get
352 ( p_count => x_msg_count,
353 p_data => x_msg_data );
354
355 EXCEPTION
356 WHEN FND_API.G_EXC_ERROR THEN
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 ROLLBACK TO Delete_Ag_Dn_PUB;
359 -- Changed by gnramasa for bug 5661324 14-Mar-07
360 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
361 FND_MSG_PUB.Count_And_Get
362 ( p_count => x_msg_count,
363 p_data => x_msg_data );
364
365 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 ROLLBACK TO Delete_Ag_Dn_PUB;
368 -- Changed by gnramasa for bug 5661324 14-Mar-07
369 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
370 FND_MSG_PUB.Count_And_Get
371 ( p_count => x_msg_count,
372 p_data => x_msg_data );
373
374 WHEN OTHERS THEN
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376 ROLLBACK TO Delete_Ag_Dn_PUB;
377 -- Changed by gnramasa for bug 5661324 14-Mar-07
378 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
379 FND_MSG_PUB.Count_And_Get
380 ( p_count => x_msg_count,
381 p_data => x_msg_data );
382
383 END Delete_AG_DN_XREF;
384
385
386
387
388 /*=================================================
389 * clchang added new level 'BILL_TO' in 11.5.10.
390 *=================================================*/
391 Procedure Send_Dunning
392 (p_api_version IN NUMBER := 1.0,
393 p_init_msg_list IN VARCHAR2 ,
394 p_commit IN VARCHAR2 ,
395 p_running_level IN VARCHAR2 ,
396 p_previous_request_id IN NUMBER, -- added by gnramasa for bug 5661324 14-Mar-07
397 p_dunning_plan_id in number,
398 x_return_status OUT NOCOPY VARCHAR2,
399 x_msg_count OUT NOCOPY NUMBER,
400 x_msg_data OUT NOCOPY VARCHAR2)
401 IS
402 CURSOR C_GET_DEL (p_dunning_plan_id number) IS
403 SELECT delinquency_ID,
404 Party_cust_id,
405 cust_account_id,
406 customer_site_use_id,
407 score_value
408 FROM IEX_DELINQUENCIES
409 , iex_dunning_plans_vl
410 WHERE STATUS in ('DELINQUENT', 'PREDELINQUENT')
411 and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id
412 and iex_delinquencies.score_id = iex_dunning_plans_vl.score_id;
413 --AND DUNN_YN = 'Y';
414 --
415 -- Changed by gnramasa for bug 5661324 14-Mar-07
416 /* CURSOR C_GET_CUSTOMER (p_dunning_plan_id number) IS
417 SELECT distinct party_cust_id
418 FROM IEX_DELINQUENCIES
419 , iex_dunning_plans_vl
420 WHERE STATUS in ('DELINQUENT', 'PREDELINQUENT')
421 and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id
422 -- begin bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
423 -- and iex_delinquencies.score_id = iex_dunning_plans_vl.score_id
424 -- end bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
425 --AND DUNN_YN = 'Y'
426 order by party_cust_id;
427 --
428 CURSOR C_GET_CUSTOMER_DEL( in_party_id NUMBER, p_dunning_plan_id number) IS
429 SELECT party_cust_id, cust_account_id, customer_site_use_id, delinquency_ID
430 FROM IEX_DELINQUENCIES
431 , iex_dunning_plans_vl
432 WHERE STATUS in ('DELINQUENT', 'PREDELINQUENT')
433 AND party_cust_id = in_party_id
434 and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id
435 -- begin bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
436 -- and iex_delinquencies.score_id = iex_dunning_plans_vl.score_id
437 -- end bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
438 --AND DUNN_YN = 'Y'
439 ORDER BY cust_account_id, delinquency_id;
440 --
441 CURSOR C_GET_ACCOUNT (p_dunning_plan_id number) IS
442 SELECT distinct cust_account_id
443 FROM IEX_DELINQUENCIES
444 , iex_dunning_plans_vl
445 WHERE STATUS in ('DELINQUENT', 'PREDELINQUENT')
446 and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id
447 -- begin bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
448 -- and iex_delinquencies.score_id = iex_dunning_plans_vl.score_id
449 -- end bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
450 --AND DUNN_YN = 'Y'
451 ORDER BY cust_account_id;
452 --
453 CURSOR C_GET_ACCOUNT_DEL(IN_ACCOUNT_ID NUMBER, p_dunning_plan_id number) IS
454 SELECT party_cust_id, cust_account_id, customer_site_use_id, delinquency_ID
455 FROM IEX_DELINQUENCIES
456 , iex_dunning_plans_vl
457 WHERE STATUS in ('DELINQUENT', 'PREDELINQUENT')
458 --AND DUNN_YN = 'Y'
459 AND cust_account_id = in_account_id
460 and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id
461 -- begin bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
462 -- and iex_delinquencies.score_id = iex_dunning_plans_vl.score_id
463 -- end bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
464 ORDER BY cust_account_id, delinquency_id;
465 --
466 CURSOR C_GET_SITE (p_dunning_plan_id number) IS
467 SELECT distinct customer_site_use_id
468 FROM IEX_DELINQUENCIES
469 , iex_dunning_plans_vl
470 WHERE STATUS in ('DELINQUENT', 'PREDELINQUENT')
471 and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id
472 -- begin bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
473 -- and iex_delinquencies.score_id = iex_dunning_plans_vl.score_id
474 -- end bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
475 ORDER BY customer_site_use_id;
476 --
477 CURSOR C_GET_SITE_DEL(IN_SITE_ID NUMBER, p_dunning_plan_id number) IS
478 SELECT party_cust_id, cust_account_id, customer_site_use_id,delinquency_ID
479 FROM IEX_DELINQUENCIES
480 , iex_dunning_plans_vl
481 WHERE STATUS in ('DELINQUENT', 'PREDELINQUENT')
482 AND customer_site_use_id = in_site_id
483 and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id
484 -- begin bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
485 -- and iex_delinquencies.score_id = iex_dunning_plans_vl.score_id
486 -- end bug 4914799 ctlee 12/30/2005, party/account/site level dunning does not have score in delinquency
487 ORDER BY customer_site_use_id, delinquency_id;
488 */
489 --
490 CURSOR C_GET_BUCKET (p_dunning_plan_id number) IS
491 select aging_bucket_id from iex_dunning_plans_vl
492 where dunning_plan_id = p_dunning_plan_id;
493 -- CURSOR C_GET_BUCKET IS
494 -- SELECT preference_value
495 -- FROM IEX_APP_PREFERENCES_VL
496 --WHERE upper(PREFERENCE_NAME) = 'COLLECTIONS AGING BUCKET';
497 -- WHERE upper(PREFERENCE_NAME) = 'DUNNING PLAN AGING BUCKET';
498 --
499 l_api_name CONSTANT VARCHAR2(30) := 'Send_Dunning';
500 l_api_version_number CONSTANT NUMBER := 1.0;
501 l_return_status VARCHAR2(10);
502 l_msg_count NUMBER;
503 l_msg_data VARCHAR2(32767);
504 l_del_id NUMBER;
505 l_party_id NUMBER;
506 l_account_id NUMBER;
507 l_customer_site_use_id NUMBER;
508 l_score NUMBER;
509 l_delinquencies_tbl IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE;
510 l_delinquency_rec IEX_DELINQUENCY_PUB.DELINQUENCY_REC_TYPE;
511 l_bucket VARCHAR2(100);
512 nIdx NUMBER;
513 nDelIdx NUMBER;
514 errmsg VARCHAR2(32767);
515 l_error NUMBER := 0;
516
517 l_repeat boolean := true;
518
519 -- added by gnramasa for bug 5661324 14-Mar-07
520 l_object_id NUMBER;
521 i number;
522 Type refCur is Ref Cursor;
523 sql_cur refCur;
524 sql_cur2 refCur;
525 vPLSQL VARCHAR2(2000);
526 vPLSQL2 VARCHAR2(5000);
527 vSelectColumn varchar2(25);
528
529 cursor c_scoring_engine(p_dunning_plan_id number) is
530 select sc.score_id
531 ,sc.score_name
532 from iex_dunning_plans_vl d
533 ,iex_scores sc
534 where d.dunning_plan_id = p_dunning_plan_id
535 and sc.score_id = d.score_id;
536
537 l_score_engine_id number;
538 l_score_engine_name varchar2(60);
539 --Start bug 7197038 gnramasa 9th july 08
540 cursor c_filter_object(p_dunning_plan_id number) is
541 select iof.select_column, iof.entity_name
542 from IEX_OBJECT_FILTERS iof,iex_dunning_plans_vl ipd, IEX_SCORES isc
543 where ipd.dunning_plan_id = p_dunning_plan_id
544 and ipd.score_id=isc.score_id
545 and isc.score_id=iof.object_id;
546
547 l_select_column varchar2(50);
548 l_entity_name varchar2(50);
549
550 BEGIN
551 -- Standard Start of API savepoint
552 SAVEPOINT SEND_DUNNING_PUB;
553
554 -- Changed by gnramasa for bug 5661324 14-Mar-07
555 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - start ');
556 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - running_level = '||p_running_level);
557 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_previous_request_id '||p_previous_request_id);
558 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - dunning_plan_id '||p_dunning_plan_id);
559
560 -- Initialize message list if p_init_msg_list is set to TRUE.
561 IF FND_API.to_Boolean( p_init_msg_list )
562 THEN
563 FND_MSG_PUB.initialize;
564 END IF;
565
566 -- Debug Message
567
568 if (p_dunning_plan_id is null)
569 THEN
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572 -- Initialize API return status to SUCCESS
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574
575 --
576 -- Api body
577 --
578
579 Open C_Get_BUCKET (p_dunning_plan_id);
580 Fetch C_Get_Bucket into l_bucket;
581 If ( C_GET_Bucket%NOTFOUND ) Then
582 -- Changed by gnramasa for bug 5661324 14-Mar-07
583 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - NO Bucket');
584 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Bucket');
585 x_return_status := FND_API.G_RET_STS_ERROR;
586 RAISE FND_API.G_EXC_ERROR;
587 end if;
588 CLOSE C_GET_Bucket;
589 -- Changed by gnramasa for bug 5661324 14-Mar-07
590 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Bucket='||l_bucket);
591 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current Bucket='||l_bucket);
592 --
593 -- Commented by gnramasa for bug 5661324 14-Mar-07
594 /*
595 IF (l_error = 0) THEN
596
597 WriteLog('iexpdunb:SEND_DUNN: open Cursor');
598 nIdx := 0;
599 nDelIdx := 0;
600 --
601 if (p_running_level = 'CUSTOMER') then
602 --
603 Open C_Get_CUSTOMER(p_dunning_plan_id);
604 LOOP
605 Fetch C_Get_CUSTOMER
606 into l_party_id;
607
608 If ( C_GET_CUSTOMER%NOTFOUND ) Then
609 if (nIdx = 0) then
610 WriteLog('iexpdunb:SEND_DUNN: NO PARTY');
611 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Party');
612 end if;
613 WriteLog('iexpdunb:SEND_DUNN:NODATA');
614 --WriteLog('***iexpdunb:SEND_DUNN:PARTY_ID='||l_party_id);
615 exit;
616 else
617 nIdx := nIdx + 1;
618 WriteLog('***iexpdunb:SEND_DUNN:nIdx='||nIdx);
619 WriteLog('***iexpdunb:SEND_DUNN:PARTY_ID='||l_party_id);
620 FND_FILE.PUT_LINE(FND_FILE.LOG, '*****Party Id='||l_party_id||'*****');
621 nDelIdx := 0;
622 Open C_Get_CUSTOMER_DEL(l_party_id, p_dunning_plan_id);
623 LOOP
624 Fetch C_Get_CUSTOMER_DEL
625 into l_party_id, l_account_id, l_customer_site_use_id, l_del_id;
626 --
627 If ( C_GET_CUSTOMER_DEL%NOTFOUND ) Then
628 if (nDelIdx = 0) then
629 WriteLog('iexpdunb:SEND_DUNN: NO DEL');
630 end if;
631 WriteLog('iexpdunb:SEND_DUNN:NODEL-END');
632 exit;
633 else
634 nDelIdx := nDelIdx + 1;
635 WriteLog('***iexpdunb:SEND_DUNN:delid='||l_del_id);
636 l_delinquency_rec.delinquency_id := l_del_id;
637 l_delinquency_rec.party_cust_id := l_party_id;
638 l_delinquency_rec.cust_account_id := l_account_id;
639 l_delinquency_rec.customer_site_use_id := l_customer_site_use_id;
640 l_delinquencies_Tbl(nDelIdx) := l_delinquency_rec;
641
642 --clchang updated to fix the gscc warning 10/28/04
643 --l_delinquency_rec := IEX_DELINQUENCY_PUB.G_MISS_DELINQUENCY_REC; -- clear rec
644 l_delinquency_rec := null; -- clear rec
645 --WriteLog('***iexpdunb:SEND_DUNN:save data');
646 end if;
647 END LOOP;
648 Close C_Get_CUSTOMER_DEL;
649
650 -- init return msg for each customer
651 l_return_status := FND_API.G_RET_STS_SUCCESS;
652 l_msg_count := 0;
653 l_msg_data := '';
654
655 WriteLog('***iexpdunb:SEND_DUNN:delCnt='||nDelIdx);
656 WriteLog('***iexpdunb:SEND_DUNN:Call PVT');
657 IEX_DUNNING_PVT.Send_Level_Dunning(
658 p_api_version => p_api_version
659 , p_init_msg_list => p_init_msg_list
660 , p_commit => p_commit
661 , p_running_level => p_running_level
662 , p_dunning_plan_id => p_dunning_plan_id
663 , p_delinquencies_tbl => l_delinquencies_tbl
664 , x_return_status => l_return_status
665 , x_msg_count => l_msg_count
666 , x_msg_data => l_msg_data
667 );
668
669 WriteLog('***iexpdunb:SEND_DUNN:PVT status='||l_return_status);
670
671 IF l_return_status = 'SKIP' then
672 WriteLog('iexpdunb:SEND_DUNN:skip this party');
673 l_return_status := FND_API.G_RET_STS_SUCCESS;
674 elsif l_return_status = 'X' then
675
676 if l_repeat then
677 WriteLog('get scoring engine');
678 open c_scoring_engine(p_dunning_plan_id);
679 fetch c_scoring_engine into l_score_engine_id, l_score_engine_name;
680 close c_scoring_engine;
681 FND_FILE.PUT_LINE(FND_FILE.LOG,'Validate correct scoring engine was run for this dunning plan.');
682 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine Name: ' || l_score_engine_name);
683 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine ID: ' || l_score_engine_id);
684 l_repeat := false;
685 else
686 l_return_status := FND_API.G_RET_STS_SUCCESS;
687 end if;
688
689 ELSIF l_return_status = FND_API.G_RET_STS_ERROR then
690 raise FND_API.G_EXC_ERROR;
691 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
692 raise FND_API.G_EXC_UNEXPECTED_ERROR;
693 END IF;
694
695 END IF;
696
697 END LOOP;
698 Close C_Get_CUSTOMER;
699 --WriteLog('iexpdunb:SEND_DUNN:Close Cursor');
700 --
701 --
702 elsif (p_running_level = 'ACCOUNT') then
703
704 Open C_Get_ACCOUNT(p_dunning_plan_id);
705 LOOP
706 Fetch C_Get_ACCOUNT
707 into l_account_id;
708
709 If ( C_GET_ACCOUNT%NOTFOUND ) Then
710 if (nIdx = 0) then
711 WriteLog('iexpdunb:SEND_DUNN: NO ACCOUNT');
712 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Account');
713 --msg
714 -- Begin - Andre Araujo - 09/30/2004- Remove obsolete logging
715 */
716 /*
717 IEX_CONC_REQUEST_MSG_PKG.Log_Error(
718 P_Concurrent_Request_ID => FND_GLOBAL.CONC_REQUEST_ID,
719 P_Procedure_name => 'IEX_DUNNING_PUB.SEND_DUNNING',
720 P_MESSAGE => 'NO Delinquencies');
721 */
722 -- End - Andre Araujo - 09/30/2004- Remove obsolete logging
723 /*
724 end if;
725 WriteLog('iexpdunb:SEND_DUNN:NODATA');
726 exit;
727 else
728 nIdx := nIdx + 1;
729 WriteLog('***iexpdunb:SEND_DUNN:nIdx='||nIdx);
730 WriteLog('***iexpdunb:SEND_DUNN:ACCOUNT_ID='||l_account_id);
731 FND_FILE.PUT_LINE(FND_FILE.LOG, '*****Account Id='||l_account_id||'*****');
732 nDelIdx := 0;
733 Open C_Get_ACCOUNT_DEL(l_account_id, p_dunning_plan_id);
734 LOOP
735 Fetch C_Get_ACCOUNT_DEL
736 into l_party_id, l_account_id, l_customer_site_use_id,l_del_id;
737 --
738 If ( C_GET_ACCOUNT_DEL%NOTFOUND ) Then
739 if (nDelIdx = 0) then
740 WriteLog('iexpdunb:SEND_DUNN: NO DEL');
741 end if;
742 WriteLog('iexpdunb:SEND_DUNN:NODEL-END');
743 exit;
744 else
745 nDelIdx := nDelIdx + 1;
746 --WriteLog('***iexpdunb:SEND_DUNN:nDelIdx='||nDelIdx);
747 --WriteLog('***iexpdunb:SEND_DUNN:partyid='||l_party_id);
748 --WriteLog('***iexpdunb:SEND_DUNN:accntid='||l_account_id);
749 WriteLog('***iexpdunb:SEND_DUNN:delid='||l_del_id);
750 l_delinquency_rec.delinquency_id := l_del_id;
751 l_delinquency_rec.party_cust_id := l_party_id;
752 l_delinquency_rec.cust_account_id := l_account_id;
753 l_delinquency_rec.customer_site_use_id := l_customer_site_use_id;
754 l_delinquencies_Tbl(nDelIdx) := l_delinquency_rec;
755 --clchang updated to fix the gscc warning 10/28/04
756 --l_delinquency_rec := IEX_DELINQUENCY_PUB.G_MISS_DELINQUENCY_REC; -- clear rec
757 l_delinquency_rec := null; -- clear rec
758 end if;
759 END LOOP;
760 Close C_Get_ACCOUNT_DEL;
761
762 -- init return msg for each account
763 l_return_status := FND_API.G_RET_STS_SUCCESS;
764 l_msg_count := 0;
765 l_msg_data := '';
766
767 WriteLog('***iexpdunb:SEND_DUNN:delCnt='||nDelIdx);
768 WriteLog('***iexpdunb:SEND_DUNN:Call PVT');
769 IEX_DUNNING_PVT.Send_Level_Dunning(
770 p_api_version => p_api_version
771 , p_init_msg_list => p_init_msg_list
772 , p_commit => p_commit
773 , p_running_level => p_running_level
774 , p_dunning_plan_id => p_dunning_plan_id
775 , p_delinquencies_tbl => l_delinquencies_tbl
776 , x_return_status => l_return_status
777 , x_msg_count => l_msg_count
778 , x_msg_data => l_msg_data
779 );
780
781 WriteLog('***iexpdunb:SEND_DUNN:PVT status='||l_return_status);
782
783 IF l_return_status = 'SKIP' then
784 WriteLog('iexpdunb:SEND_DUNN:skip this account');
785 l_return_status := FND_API.G_RET_STS_SUCCESS;
786 elsif l_return_status = 'X' then
787
788 if l_repeat then
789 WriteLog('get scoring engine');
790 open c_scoring_engine(p_dunning_plan_id);
791 fetch c_scoring_engine into l_score_engine_id, l_score_engine_name;
792 close c_scoring_engine;
793 FND_FILE.PUT_LINE(FND_FILE.LOG,'Validate correct scoring engine was run for this dunning plan.');
794 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine Name: ' || l_score_engine_name);
795 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine ID: ' || l_score_engine_id);
796 l_repeat := false;
797 else
798 l_return_status := FND_API.G_RET_STS_SUCCESS;
799 end if;
800 ELSIF l_return_status = FND_API.G_RET_STS_ERROR then
801 raise FND_API.G_EXC_ERROR;
802 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
803 raise FND_API.G_EXC_UNEXPECTED_ERROR;
804 END IF;
805 END IF;
806
807 END LOOP;
808 Close C_Get_ACCOUNT;
809 --WriteLog('iexpdunb:SEND_DUNN:Close Cursor');
810 --
811 -- clchang added for new level 'BILL_TO' (11.5.10)
812 elsif (p_running_level = 'BILL_TO') then
813
814 Open C_Get_SITE(p_dunning_plan_id);
815 LOOP
816 Fetch C_Get_SITE
817 into l_customer_site_use_id;
818
819 If ( C_GET_SITE%NOTFOUND ) Then
820 if (nIdx = 0) then
821 WriteLog('iexpdunb:SEND_DUNN: NO BILL TO');
822 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Bill To');
823 end if;
824 WriteLog('iexpdunb:SEND_DUNN:NODATA');
825 exit;
826 else
827 nIdx := nIdx + 1;
828 WriteLog('***iexpdunb:SEND_DUNN:nIdx='||nIdx);
829 WriteLog('***iexpdunb:SEND_DUNN:Customer_Site_use_id='||l_customer_site_use_id);
830 FND_FILE.PUT_LINE(FND_FILE.LOG, '*****customer_site_use_id ='||l_customer_site_use_id||'*****');
831 nDelIdx := 0;
832 Open C_Get_SITE_DEL(l_customer_site_use_id, p_dunning_plan_id);
833 LOOP
834 Fetch C_Get_SITE_DEL
835 into l_party_id, l_account_id, l_customer_site_use_id,l_del_id;
836 --
837 If ( C_GET_SITE_DEL%NOTFOUND ) Then
838 if (nDelIdx = 0) then
839 WriteLog('iexpdunb:SEND_DUNN: NO DEL');
840 end if;
841 WriteLog('iexpdunb:SEND_DUNN:NODEL-END');
842 exit;
843 else
844 nDelIdx := nDelIdx + 1;
845 --WriteLog('***iexpdunb:SEND_DUNN:nDelIdx='||nDelIdx);
846 --WriteLog('***iexpdunb:SEND_DUNN:partyid='||l_party_id);
847 --WriteLog('***iexpdunb:SEND_DUNN:accntid='||l_account_id);
848 WriteLog('***iexpdunb:SEND_DUNN:delid='||l_del_id);
849 l_delinquency_rec.delinquency_id := l_del_id;
850 l_delinquency_rec.party_cust_id := l_party_id;
851 l_delinquency_rec.cust_account_id := l_account_id;
852 l_delinquency_rec.customer_site_use_id := l_customer_site_use_id;
853 l_delinquencies_Tbl(nDelIdx) := l_delinquency_rec;
854 --l_delinquency_rec := IEX_DELINQUENCY_PUB.G_MISS_DELINQUENCY_REC; -- clear rec
855 l_delinquency_rec := null; -- clear rec
856 end if;
857 END LOOP;
858 Close C_Get_SITE_DEL;
859
860 -- init return msg for each account
861 l_return_status := FND_API.G_RET_STS_SUCCESS;
862 l_msg_count := 0;
863 l_msg_data := '';
864
865 WriteLog('***iexpdunb:SEND_DUNN:delCnt='||nDelIdx);
866 WriteLog('***iexpdunb:SEND_DUNN:Call PVT');
867 IEX_DUNNING_PVT.Send_Level_Dunning(
868 p_api_version => p_api_version
869 , p_init_msg_list => p_init_msg_list
870 , p_commit => p_commit
871 , p_running_level => p_running_level
872 , p_dunning_plan_id => p_dunning_plan_id
873 , p_delinquencies_tbl => l_delinquencies_tbl
874 , x_return_status => l_return_status
875 , x_msg_count => l_msg_count
876 , x_msg_data => l_msg_data
877 );
878
879 WriteLog('***iexpdunb:SEND_DUNN:PVT status='||l_return_status);
880
881 IF l_return_status = 'SKIP' then
882 WriteLog('iexpdunb:SEND_DUNN:skip this site');
883 l_return_status := FND_API.G_RET_STS_SUCCESS;
884 elsif l_return_status = 'X' then
885
886 if l_repeat then
887 WriteLog('get scoring engine');
888 open c_scoring_engine(p_dunning_plan_id);
889 fetch c_scoring_engine into l_score_engine_id, l_score_engine_name;
890 close c_scoring_engine;
891 FND_FILE.PUT_LINE(FND_FILE.LOG,'Validate correct scoring engine was run for this dunning plan.');
892 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine Name: ' || l_score_engine_name);
893 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine ID: ' || l_score_engine_id);
894 l_repeat := false;
895 else
896 l_return_status := FND_API.G_RET_STS_SUCCESS;
897 end if;
898 ELSIF l_return_status = FND_API.G_RET_STS_ERROR then
899 raise FND_API.G_EXC_ERROR;
900 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
901 raise FND_API.G_EXC_UNEXPECTED_ERROR;
902 END IF;
903 END IF;
904
905 END LOOP;
906 Close C_Get_SITE;
907 --WriteLog('iexpdunb:SEND_DUNN:Close Cursor');
908 --
909 -- end of adding for BILL_TO
910 --
911 else
912 --
913 Open C_Get_DEL(p_dunning_plan_id);
914 LOOP
915 Fetch C_Get_DEL
916 into l_del_id, l_party_id, l_account_id, l_customer_site_use_id,l_score;
917
918 If ( C_GET_DEL%NOTFOUND ) Then
919 if (nIdx = 0) then
920 WriteLog('iexpdunb:SEND_DUNN: NO Del');
921 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Delinquency');
922 x_return_status := FND_API.G_RET_STS_ERROR;
923 end if;
924 exit;
925 else
926 nIdx := nIdx + 1;
927 WriteLog('***iexpdunb:SEND_DUNN:nIdx='||nIdx);
928 WriteLog('***iexpdunb:SEND_DUNN:delid='||l_del_id);
929 l_delinquency_rec.delinquency_id := l_del_id;
930 l_delinquency_rec.party_cust_id := l_party_id;
931 l_delinquency_rec.cust_account_id := l_account_id;
932 l_delinquency_rec.customer_site_use_id := l_customer_site_use_id;
933 l_delinquency_rec.score_value := l_score;
934 l_delinquencies_Tbl(nIdx) := l_delinquency_rec;
935 --l_delinquency_rec := IEX_DELINQUENCY_PUB.G_MISS_DELINQUENCY_REC; -- clear rec
936 l_delinquency_rec := null; -- clear rec
937 end if;
938
939 END LOOP;
940 Close C_Get_DEL;
941 WriteLog('iexpdunb:SEND_DUNN:Close Cursor');
942
943 WriteLog('iexpdunb:SEND_DUNN:Call Pvt');
944
945 IEX_DUNNING_PVT.Send_Dunning(
946 p_api_version => p_api_version
947 , p_init_msg_list => p_init_msg_list
948 , p_commit => p_commit
949 , p_dunning_plan_id => p_dunning_plan_id
950 , p_delinquencies_tbl => l_delinquencies_tbl
951 , x_return_status => l_return_status
952 , x_msg_count => l_msg_count
953 , x_msg_data => l_msg_data
954 );
955
956 WriteLog('iexpdunb:SEND_DUNN:Afer Call Pvt: Status='||l_return_status);
957
958 IF l_return_status = FND_API.G_RET_STS_ERROR then
959 raise FND_API.G_EXC_ERROR;
960 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
961 raise FND_API.G_EXC_UNEXPECTED_ERROR;
962 END IF;
963
964 end if; -- end of if (p_running_level);
965
966 END IF; -- end of if (l_error)
967 */ -- Up to here
968
969 -- added by gnramasa for bug 5661324 14-Mar-07
970 -- next get all the IDs we need to fill into array to pass to send_level_dunning OR send dunning
971 Open c_filter_object (p_dunning_plan_id);
972 Fetch c_filter_object into l_select_column,l_entity_name;
973 If ( c_filter_object%NOTFOUND ) Then
974 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - NO filter object');
975 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No filter object');
976 x_return_status := FND_API.G_RET_STS_ERROR;
977 RAISE FND_API.G_EXC_ERROR;
978 end if;
979 CLOSE c_filter_object;
980 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_select_column: '|| l_select_column);
981 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_entity_name: '||l_entity_name);
982 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_select_column: '|| l_select_column);
983 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_entity_name: '||l_entity_name);
984
985 if p_running_level = 'CUSTOMER' then
986 vSelectColumn := 'party_cust_id';
987 vPLSQL2 := ' select ' ||
988 ' par_site.party_id ' ||
989 ' ,acct_site.cust_account_id ' ||
990 ' ,site_use.site_use_id ' ||
991 ' ,decode(site_use.site_use_code, ' ||
992 ' ''DUN'', 1, ' ||
993 ' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
994 'from HZ_CUST_SITE_USES site_use ' ||
995 ' ,HZ_CUST_ACCT_SITES acct_site ' ||
996 ' ,hz_party_sites par_site ' ||
997 ' ,iex_dunning_plans_vl ' ||
998 'where ' ||
999 ' par_site.party_id = :1 and ' ||
1000 ' par_site.status = ''A'' and ' ||
1001 ' par_site.party_site_id = acct_site.party_site_id and ' ||
1002 ' acct_site.status = ''A'' and ' ||
1003 ' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
1004 ' site_use.status = ''A'' and ' ||
1005 ' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
1006 ' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = par_site.party_id) ' ||
1007 ' order by Display_Order ';
1008 elsif p_running_level = 'ACCOUNT' then
1009 vSelectColumn := 'cust_account_id';
1010 vPLSQL2 := ' select ' ||
1011 ' par_site.party_id ' ||
1012 ' ,acct_site.cust_account_id ' ||
1013 ' ,site_use.site_use_id ' ||
1014 ' ,decode(site_use.site_use_code, ' ||
1015 ' ''DUN'', 1, ' ||
1016 ' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
1017 'from HZ_CUST_SITE_USES site_use ' ||
1018 ' ,HZ_CUST_ACCT_SITES acct_site ' ||
1019 ' ,hz_party_sites par_site ' ||
1020 ' ,iex_dunning_plans_vl ' ||
1021 'where ' ||
1022 ' acct_site.cust_account_id = :1 and ' ||
1023 ' par_site.status = ''A'' and ' ||
1024 ' par_site.party_site_id = acct_site.party_site_id and ' ||
1025 ' acct_site.status = ''A'' and ' ||
1026 ' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
1027 ' site_use.status = ''A'' and ' ||
1028 ' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
1029 ' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = acct_site.cust_account_id) ' ||
1030 ' order by Display_Order ';
1031 elsif p_running_level = 'BILL_TO' then
1032 vSelectColumn := 'customer_site_use_id';
1033 vPLSQL2 := ' select ' ||
1034 ' par_site.party_id ' ||
1035 ' ,acct_site.cust_account_id ' ||
1036 ' ,site_use.site_use_id ' ||
1037 ' ,decode(site_use.site_use_code, ' ||
1038 ' ''DUN'', 1, ' ||
1039 ' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
1040 'from HZ_CUST_SITE_USES site_use ' ||
1041 ' ,HZ_CUST_ACCT_SITES acct_site ' ||
1042 ' ,hz_party_sites par_site ' ||
1043 ' ,iex_dunning_plans_vl ' ||
1044 'where ' ||
1045 ' site_use.site_use_id = :1 and ' ||
1046 ' par_site.status = ''A'' and ' ||
1047 ' par_site.party_site_id = acct_site.party_site_id and ' ||
1048 ' acct_site.status = ''A'' and ' ||
1049 ' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
1050 ' site_use.status = ''A'' and ' ||
1051 ' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
1052 ' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = site_use.site_use_id) ' ||
1053 ' order by Display_Order ';
1054
1055 else -- we are running at delinquency level
1056 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Delinquency Level');
1057 vSelectColumn := 'delinquency_id';
1058 vPLSQL2 := 'SELECT party_cust_id ' ||
1059 ' ,cust_account_id ' ||
1060 ' ,customer_site_use_id ' ||
1061 ' ,delinquency_ID ' ||
1062 ' FROM IEX_DELINQUENCIES del' ||
1063 ' ,iex_dunning_plans_vl ' ||
1064 ' WHERE STATUS in (''DELINQUENT'', ''PREDELINQUENT'') ' ||
1065 ' AND DELINQUENCY_ID = :1 ' ||
1066 ' AND iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
1067 ' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = del.DELINQUENCY_ID) ';
1068
1069 --'ORDER BY ' || vSelectColumn || ' ,delinquency_id';
1070 end if;
1071
1072 -- fetch the party/account/site_use/delinquency from iex_delinquencies table
1073 if p_previous_request_id is null then
1074 vPLSQL := ' SELECT distinct ' || vSelectColumn ||
1075 ' FROM IEX_DELINQUENCIES ' ||
1076 ', IEX_DUNNING_PLANS_VL '||
1077 ' WHERE STATUS in (''DELINQUENT'', ''PREDELINQUENT'') ' ||
1078 ' AND iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ||
1079 ' order by ' || vSelectColumn;
1080 else
1081 vPLSQL := ' SELECT distinct object_id ' ||
1082 ' FROM IEX_DUNNINGS ' ||
1083 ' WHERE DELIVERY_STATUS = ''ERROR'' ' ||
1084 ' AND STATUS = ''OPEN'' ' ||
1085 ' AND REQUEST_ID = :1 ' ||
1086 ' AND DUNNING_LEVEL = :2 ';
1087 end if;
1088
1089 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL ' || vPLSQL);
1090 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL2 ' || vPLSQL2);
1091 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - open Cursor');
1092
1093 if p_previous_request_id is null then
1094 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - no bind ');
1095 open sql_cur for vPLSQL using p_dunning_plan_id;
1096
1097 else
1098 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - binding with ' || p_previous_request_id || ' and ' || p_running_level);
1099 open sql_cur for vPLSQL using p_previous_request_id, p_running_level;
1100
1101 end if;
1102
1103 if p_running_level <> 'DELINQUENCY' then
1104
1105 LOOP
1106 fetch sql_cur into l_object_id;
1107 exit when sql_cur%NOTFOUND;
1108 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - fetched ' || l_object_id);
1109 --open sql_cur2 for vPLSQL2 using l_object_id,p_dunning_plan_id,l_select_column,l_entity_name,l_select_column;
1110 open sql_cur2 for vPLSQL2 using l_object_id,p_dunning_plan_id;
1111 fetch sql_cur2 into l_party_id, l_account_id, l_customer_site_use_id, l_del_id;
1112 if sql_cur2%FOUND then
1113 l_delinquencies_Tbl(1).party_cust_id := l_party_id;
1114 l_delinquencies_Tbl(1).cust_account_id := l_account_id;
1115 l_delinquencies_Tbl(1).customer_site_use_id := l_customer_site_use_id;
1116 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_party_id ' || l_delinquencies_Tbl(1).party_cust_id);
1117 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_account_id ' || l_delinquencies_Tbl(1).cust_account_id);
1118 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_customer_site_use_id ' || l_delinquencies_Tbl(1).customer_site_use_id);
1119 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - calling send_level_dunning');
1120
1121 IEX_DUNNING_PVT.Send_Level_Dunning(p_api_version => p_api_version
1122 ,p_init_msg_list => p_init_msg_list
1123 ,p_commit => p_commit
1124 ,p_running_level => p_running_level
1125 ,p_dunning_plan_id => p_dunning_plan_id
1126 ,p_delinquencies_tbl => l_delinquencies_tbl
1127 ,p_previous_request_id => p_previous_request_id
1128 ,x_return_status => l_return_status
1129 ,x_msg_count => l_msg_count
1130 ,x_msg_data => l_msg_data);
1131 IF l_return_status = 'SKIP' then
1132 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - skip this account');
1133 l_return_status := FND_API.G_RET_STS_SUCCESS;
1134
1135 elsif l_return_status = 'X' then
1136 if l_repeat then
1137 WriteLog('get scoring engine');
1138 open c_scoring_engine(p_dunning_plan_id);
1139 fetch c_scoring_engine into l_score_engine_id, l_score_engine_name;
1140 close c_scoring_engine;
1141 FND_FILE.PUT_LINE(FND_FILE.LOG,'Validate correct scoring engine was run for this dunning plan.');
1142 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine Name: ' || l_score_engine_name);
1143 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine ID: ' || l_score_engine_id);
1144 l_repeat := false;
1145 else
1146 l_return_status := FND_API.G_RET_STS_SUCCESS;
1147 end if;
1148
1149 ELSIF l_return_status = FND_API.G_RET_STS_ERROR then
1150 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - send_level_Failed - CONTINUE');
1151 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1152 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - send_level_Failed - CONTINUE');
1153 END IF;
1154 else
1155 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_object_id: '||l_object_id || ' does not exist in filter object :' ||l_entity_name || ' so skipping');
1156 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_object_id: '||l_object_id || ' does not exist in filter object :' ||l_entity_name || ' so skipping');
1157 end if;
1158 close sql_cur2 ;
1159 end loop; -- sql_cur
1160
1161 else -- we are running at delinquency level
1162
1163 i := 0;
1164 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - fetching delinquencies');
1165 Open C_Get_DEL (p_dunning_plan_id);
1166 LOOP
1167 Fetch C_Get_DEL into l_del_id, l_party_id, l_account_id, l_customer_site_use_id, l_score;
1168
1169 --If ( C_GET_DEL%NOTFOUND ) Then
1170 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Delinquency');
1171 -- x_return_status := FND_API.G_RET_STS_ERROR;
1172 --end if;
1173
1174 exit when C_GET_DEL%NOTFOUND;
1175 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_del_id' || l_del_id);
1176 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_party_id ' || l_party_id);
1177 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_account_id ' || l_account_id);
1178 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_customer_site_use_id ' || l_customer_site_use_id);
1179 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_score ' || l_score);
1180
1181 i := i + 1;
1182 l_delinquencies_Tbl(i).delinquency_id := l_del_id;
1183 l_delinquencies_Tbl(i).party_cust_id := l_party_id;
1184 l_delinquencies_Tbl(i).cust_account_id := l_account_id;
1185 l_delinquencies_Tbl(i).customer_site_use_id := l_customer_site_use_id;
1186 l_delinquencies_Tbl(i).score_value := l_score;
1187
1188 END LOOP;
1189 Close C_Get_DEL;
1190
1191 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - fetched ' || l_delinquencies_Tbl.count || ' delinquencies');
1192 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Delinquency Level');
1193 IEX_DUNNING_PVT.Send_Dunning(p_api_version => p_api_version
1194 ,p_init_msg_list => p_init_msg_list
1195 ,p_commit => p_commit
1196 ,p_dunning_plan_id => p_dunning_plan_id
1197 ,p_delinquencies_tbl => l_delinquencies_tbl
1198 ,p_previous_request_id => p_previous_request_id
1199 ,x_return_status => l_return_status
1200 ,x_msg_count => l_msg_count
1201 ,x_msg_data => l_msg_data);
1202 end if;
1203 -- up to here gnramasa
1204
1205
1206 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' status='||l_return_status);
1207 -- Start bug 5924158 05/06/07 by gnramasa
1208 /* x_return_status := l_return_status;
1209 IF l_return_status = FND_API.G_RET_STS_ERROR then
1210 raise FND_API.G_EXC_ERROR;
1211 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1212 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1213 END IF;
1214 */
1215 -- End bug 5924158 05/06/07 by gnramasa
1216 --
1217 -- End of API body
1218 --
1219
1220 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - commit');
1221 -- Standard check for p_commit
1222 IF FND_API.to_Boolean( p_commit )
1223 THEN
1224 COMMIT WORK;
1225 END IF;
1226
1227 -- Debug Message
1228 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - return status='||x_return_status);
1229 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - END');
1230
1231 FND_MSG_PUB.Count_And_Get
1232 ( p_count => x_msg_count,
1233 p_data => x_msg_data );
1234
1235 EXCEPTION
1236 WHEN FND_API.G_EXC_ERROR THEN
1237 x_return_status := FND_API.G_RET_STS_ERROR;
1238 ROLLBACK TO Send_DUNNING_PUB;
1239 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1240 FND_MSG_PUB.Count_And_Get
1241 ( p_count => x_msg_count,
1242 p_data => x_msg_data );
1243
1244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1246 ROLLBACK TO Send_DUNNING_PUB;
1247 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1248 FND_MSG_PUB.Count_And_Get
1249 ( p_count => x_msg_count,
1250 p_data => x_msg_data );
1251
1252 WHEN OTHERS THEN
1253 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1254 ROLLBACK TO Send_DUNNING_PUB;
1255 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1256 FND_MSG_PUB.Count_And_Get
1257 ( p_count => x_msg_count,
1258 p_data => x_msg_data );
1259
1260 END Send_Dunning;
1261
1262 Procedure Daily_Dunning
1263 (p_api_version IN NUMBER := 1.0,
1264 p_init_msg_list IN VARCHAR2 ,
1265 p_commit IN VARCHAR2 ,
1266 --p_dunning_tbl IN IEX_DUNNING_PUB.DUNNING_TBL_TYPE,
1267 p_running_level IN VARCHAR2 ,
1268 x_return_status OUT NOCOPY VARCHAR2,
1269 x_msg_count OUT NOCOPY NUMBER,
1270 x_msg_data OUT NOCOPY VARCHAR2)
1271 IS
1272 l_api_name CONSTANT VARCHAR2(30) := 'Daily_Dunning';
1273 l_api_version_number CONSTANT NUMBER := 1.0;
1274 l_return_status VARCHAR2(1);
1275 l_msg_count NUMBER;
1276 l_msg_data VARCHAR2(32767);
1277 errmsg VARCHAR2(32767);
1278
1279 BEGIN
1280 -- Standard Start of API savepoint
1281 SAVEPOINT DAILY_DUNNING_PUB;
1282
1283 -- Initialize message list if p_init_msg_list is set to TRUE.
1284 IF FND_API.to_Boolean( p_init_msg_list )
1285 THEN
1286 FND_MSG_PUB.initialize;
1287 END IF;
1288
1289 -- Debug Message
1290 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start');
1291 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - running_level='||p_running_level);
1292
1293 -- Initialize API return status to SUCCESS
1294 x_return_status := FND_API.G_RET_STS_SUCCESS;
1295
1296 --
1297 -- Api body
1298 --
1299
1300 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Calling PVT');
1301
1302 IEX_DUNNING_PVT.Daily_Dunning(
1303 p_api_version => p_api_version
1304 , p_init_msg_list => p_init_msg_list
1305 , p_commit => p_commit
1306 , p_running_level => p_running_level
1307 --, p_dunning_tbl => p_dunning_tbl
1308 , x_return_status => x_return_status
1309 , x_msg_count => x_msg_count
1310 , x_msg_data => x_msg_data
1311 );
1312
1313 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - return status='||x_return_status);
1314
1315 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1316 IF x_return_status = FND_API.G_RET_STS_ERROR then
1317 raise FND_API.G_EXC_ERROR;
1318 else
1319 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1320 END IF;
1321 END IF;
1322
1323 --
1324 -- End of API body
1325 --
1326
1327 -- Standard check for p_commit
1328 IF FND_API.to_Boolean( p_commit )
1329 THEN
1330 COMMIT WORK;
1331 END IF;
1332
1333 -- Debug Message
1334 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - end ');
1335
1336 FND_MSG_PUB.Count_And_Get
1337 ( p_count => x_msg_count,
1338 p_data => x_msg_data );
1339
1340 EXCEPTION
1341 WHEN FND_API.G_EXC_ERROR THEN
1342 x_return_status := FND_API.G_RET_STS_ERROR;
1343 ROLLBACK TO DAILY_DUNNING_PUB;
1344 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1345 FND_MSG_PUB.Count_And_Get
1346 ( p_count => x_msg_count,
1347 p_data => x_msg_data );
1348
1349 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1351 ROLLBACK TO DAILY_DUNNING_PUB;
1352 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1353 FND_MSG_PUB.Count_And_Get
1354 ( p_count => x_msg_count,
1355 p_data => x_msg_data );
1356
1357 WHEN OTHERS THEN
1358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1359 ROLLBACK TO DAILY_DUNNING_PUB;
1360 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1361 FND_MSG_PUB.Count_And_Get
1362 ( p_count => x_msg_count,
1363 p_data => x_msg_data );
1364
1365 END Daily_Dunning;
1366
1367
1368
1369 PROCEDURE CALLBACK_CONCUR(
1370 ERRBUF OUT NOCOPY VARCHAR2,
1371 RETCODE OUT NOCOPY VARCHAR2,
1372 P_ORG_ID IN NUMBER DEFAULT NULL) --Added for MOAC
1373 is
1374 CURSOR C_GET_LEVEL IS
1375 SELECT preference_value
1376 FROM IEX_APP_PREFERENCES_VL
1377 WHERE upper(PREFERENCE_NAME) = 'COLLECTIONS DUNNING LEVEL';
1378 --
1379 l_api_version NUMBER := 1.0;
1380 l_msg_data VARCHAR2(4000) default NULL;
1381 l_msg_count NUMBER;
1382 l_default_rs_id number := 0;
1383 l_running_level VARCHAR2(20);
1384 l_error NUMBER := 0;
1385 errmsg VARCHAR2(4000) default NULL;
1386 l_api_name varchar2(25);
1387
1388 BEGIN
1389
1390 l_api_name := 'callback_concur';
1391 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Callback_Concur');
1392 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - starting CALLBACK_CONCUR');
1393
1394 --Start MOAC
1395 mo_global.init('IEX');
1396 IF p_org_id IS NULL THEN
1397 mo_global.set_policy_context('M',NULL);
1398 ELSE
1399 mo_global.set_policy_context('S',p_org_id);
1400 END IF;
1401 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Operating Unit: '|| nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All'));
1402 --End MOAC
1403
1404 l_default_rs_id := fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE');
1405 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Default Resource Id = '||l_default_rs_id);
1406 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - default_rs_id='||l_default_rs_id);
1407
1408 if (l_default_rs_id = 0 or l_default_rs_id is null) then
1409 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - CALLBACK_CONCUR:no rs_id');
1410 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE');
1411 FND_MESSAGE.Set_Token ('PROFILE', 'IEX_STRY_DEFAULT_RESOURCE', FALSE);
1412 FND_MSG_PUB.Add;
1413
1414 FND_MSG_PUB.Count_And_Get
1415 ( p_count => l_msg_count,
1416 p_data => l_msg_data
1417 );
1418
1419 FND_FILE.PUT_LINE(FND_FILE.LOG, 'no default resource_id');
1420 errbuf := l_msg_data;
1421 retcode := '2'; --FND_API.G_RET_STS_ERROR;
1422 --retcode := FND_API.G_RET_STS_ERROR;
1423 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
1424
1425 for i in 1..l_msg_count loop
1426 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
1427 p_encoded => 'F');
1428 FND_FILE.PUT_LINE(FND_FILE.LOG, errmsg);
1429 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
1430 end loop;
1431
1432 --
1433 else
1434 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - rs_id='||l_default_rs_id);
1435 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Daily_Dunning');
1436
1437 OPEN C_Get_Level ;
1438 FETCH C_Get_Level INTO l_running_level;
1439
1440 IF (C_Get_Level%NOTFOUND)
1441 THEN
1442 l_error := 1;
1443 END IF;
1444 CLOSE C_GET_LEVEL;
1445
1446 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - running level='||l_running_level);
1447 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Dunning Running Level = '||l_running_level);
1448 IF (l_running_level is null or l_error = 1) then
1449 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - no running level');
1450 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Dunning Running Level');
1451 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_VALUE');
1452 FND_MESSAGE.Set_Token('COLUMN', 'COLLECTIONS DUNNING LEVEL', FALSE);
1453 FND_MSG_PUB.Add;
1454
1455 FND_MSG_PUB.Count_And_Get
1456 ( p_count => l_msg_count,
1457 p_data => l_msg_data
1458 );
1459
1460 errbuf := l_msg_data;
1461 retcode := '2'; --FND_API.G_RET_STS_ERROR;
1462 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
1463
1464 for i in 1..l_msg_count loop
1465 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
1466 p_encoded => 'F');
1467 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
1468 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calback_Concur errmsg =' ||errmsg);
1469 end loop;
1470 --
1471 else
1472 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>Process Dunning Callbacks');
1473 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - starting Daily_Dunning');
1474 IEX_DUNNING_PUB.Daily_Dunning(
1475 p_api_version => l_api_version
1476 , p_init_msg_list => FND_API.G_TRUE
1477 , p_commit => FND_API.G_TRUE
1478 , p_running_level => l_running_level
1479 , x_return_status => RETCODE
1480 , x_msg_count => l_msg_count
1481 , x_msg_data => ERRBUF
1482 );
1483
1484 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Daily_Dunning status='||retcode);
1485 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>End of Process Dunning Callbacks');
1486 end if;
1487 --
1488 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - ProcessPromiseCallbacks');
1489 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>Process Promise Callbacks');
1490
1491 IEX_PROMISES_BATCH_PUB.PROCESS_PROMISE_CALLBACKS(
1492 p_api_version => l_api_version
1493 , p_init_msg_list => FND_API.G_TRUE
1494 , p_commit => FND_API.G_TRUE
1495 , x_return_status => RETCODE
1496 , x_msg_count => ERRBUF
1497 , x_msg_data => l_msg_data);
1498
1499 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - ProcessPromiseCallbacks status='||retcode);
1500 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End ProcessPromiseCallbacks');
1501 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>End of Process Promise Callbacks');
1502
1503 end if;
1504
1505 EXCEPTION
1506 WHEN OTHERS THEN
1507 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Exception');
1508 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Callback_Concur Exception');
1509 errbuf := SQLERRM;
1510 retcode := '2';
1511 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
1512 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Callback_Concur errbuf:'||errbuf);
1513
1514 END CALLBACK_CONCUR;
1515
1516
1517
1518 PROCEDURE SEND_DUNNING_CONCUR(
1519 ERRBUF OUT NOCOPY VARCHAR2,
1520 RETCODE OUT NOCOPY VARCHAR2,
1521 DUNNING_PLAN_ID IN NUMBER,
1522 P_PREVIOUS_REQUEST_ID IN NUMBER)
1523 is
1524 CURSOR C_GET_LEVEL (iex_dunning_plan_id number) IS
1525 select dunning_level from iex_dunning_plans_vl
1526 where dunning_plan_id = iex_dunning_plan_id;
1527 -- 12.0 ctlee, get it from the dunning_plan
1528 -- SELECT preference_value
1529 -- FROM IEX_APP_PREFERENCES_VL
1530 -- WHERE upper(PREFERENCE_NAME) = 'COLLECTIONS DUNNING LEVEL';
1531 --
1532 CURSOR C_GET_BUCKET (iex_dunning_plan_id number) IS
1533 select aging_bucket_id from iex_dunning_plans_vl
1534 where dunning_plan_id = iex_dunning_plan_id;
1535 -- 12.0 ctlee, get it from the dunning_plan
1536 -- SELECT preference_value
1537 -- FROM IEX_APP_PREFERENCES_VL
1538 --WHERE upper(PREFERENCE_NAME) = 'COLLECTIONS AGING BUCKET';
1539 -- WHERE upper(PREFERENCE_NAME) = 'DUNNING PLAN AGING BUCKET';
1540 --
1541 l_api_version NUMBER := 1.0;
1542 l_msg_count NUMBER ;
1543 l_msg_data VARCHAR2(4000) default NULL;
1544 l_running_level VARCHAR2(20);
1545 l_bucket VARCHAR2(100);
1546 l_error NUMBER := 0;
1547 errmsg VARCHAR2(4000) default NULL;
1548 l_api_name varchar2(25);
1549
1550 BEGIN
1551
1552 l_api_name := 'SEND_DUNNING_CONCUR';
1553 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SEND_DUNNING_CONCUR dunning_plan_id = ' || dunning_plan_id);
1554 FND_FILE.PUT_LINE(FND_FILE.LOG, 'RE-RERUN REQUEST: ' || p_previous_request_id);
1555 WriteLog('iexpdunb:starting SEND_DUNNING_CONCUR; dunning_plan_id = ' || dunning_plan_id);
1556 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - start');
1557 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - rerunning request ' || p_previous_request_id);
1558
1559 --Start MOAC
1560 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org Id:'|| mo_global.get_current_org_id);
1561 --End MOAC
1562
1563 retcode := FND_API.G_RET_STS_SUCCESS;
1564 --
1565 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'chk bucket');
1566 OPEN C_Get_Level (dunning_plan_id);
1567 FETCH C_Get_Level INTO l_running_level;
1568
1569 IF (C_Get_Level%NOTFOUND)
1570 THEN
1571 l_error := 1;
1572 END IF;
1573 CLOSE C_GET_LEVEL;
1574
1575 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'running level='||l_running_level);
1576 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - running level='||l_running_level);
1577
1578 if (l_running_level is null or l_error = 1) then
1579 -- IF PG_DEBUG < 10 THEN
1580 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1581 iex_debug_pub.LogMessage('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - no running level');
1582 END IF;
1583 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_VALUE');
1584 FND_MESSAGE.Set_Token('COLUMN', 'COLLECTIONS DUNNING LEVEL', FALSE);
1585 FND_MSG_PUB.Add;
1586
1587 FND_MSG_PUB.Count_And_Get
1588 ( p_count => l_msg_count,
1589 p_data => l_msg_data
1590 );
1591
1592 FND_FILE.PUT_LINE(FND_FILE.LOG, 'no running level');
1593 errbuf := l_msg_data;
1594 retcode := '2';
1595 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
1596
1597 for i in 1..l_msg_count loop
1598 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
1599 p_encoded => 'F');
1600 FND_FILE.PUT_LINE(FND_FILE.LOG, errmsg);
1601 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
1602 end loop;
1603 --
1604 else
1605
1606 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'running level='||l_running_level);
1607 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'chk bucket');
1608 OPEN C_Get_Bucket (dunning_plan_id) ;
1609 FETCH C_Get_Bucket INTO l_bucket;
1610
1611 IF (C_Get_Bucket%NOTFOUND) THEN
1612 l_error := 1;
1613 END IF;
1614 CLOSE C_GET_Bucket;
1615
1616 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Aging Bucket='||l_bucket);
1617 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - bucket='||l_bucket);
1618
1619 IF (l_bucket is null or l_error = 1) then
1620 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - no bucket');
1621 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_VALUE');
1622 --FND_MESSAGE.Set_Token('COLUMN', 'COLLECTIONS AGING BUCKET', FALSE);
1623 FND_MESSAGE.Set_Token('COLUMN', 'DUNNING PLAN AGING BUCKET', FALSE);
1624 FND_MSG_PUB.Add;
1625
1626 FND_MSG_PUB.Count_And_Get
1627 ( p_count => l_msg_count,
1628 p_data => l_msg_data
1629 );
1630
1631 FND_FILE.PUT_LINE(FND_FILE.LOG, 'no bucket');
1632 errbuf := l_msg_data;
1633 retcode := '2';
1634 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
1635
1636 for i in 1..l_msg_count loop
1637 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
1638 p_encoded => 'F');
1639 FND_FILE.PUT_LINE(FND_FILE.LOG, errmsg);
1640 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
1641 end loop;
1642
1643 --
1644 ELSE
1645 -- Now, RunningLevel and Bucket are not null;
1646
1647 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Dunning Running Level='||l_running_level);
1648 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Dunning Aging Bucket='||l_bucket);
1649 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Send_Dunning');
1650 -- IF PG_DEBUG < 10 THEN
1651 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1652 iex_debug_pub.LogMessage(G_PKG_NAME || ' ' || l_api_name || ' - runninglevel='||l_running_level);
1653 iex_debug_pub.LogMessage(G_PKG_NAME || ' ' || l_api_name || ' - bucket='||l_bucket);
1654 iex_debug_pub.LogMessage(G_PKG_NAME || ' ' || l_api_name || ' - starting Send_Dunning');
1655 END IF;
1656 IEX_DUNNING_PUB.SEND_DUNNING(
1657 p_api_version => l_api_version
1658 , p_init_msg_list => FND_API.G_TRUE
1659 , p_commit => FND_API.G_TRUE
1660 , p_running_level => l_running_level
1661 , p_previous_request_id => p_previous_request_id
1662 , p_dunning_plan_id => dunning_plan_id
1663 , x_return_status => RETCODE
1664 , x_msg_count => l_msg_count
1665 , x_msg_data => ERRBUF
1666 );
1667
1668 FND_FILE.PUT_LINE(FND_FILE.LOG, 'return_status='||retcode);
1669 -- IF PG_DEBUG < 10 THEN
1670 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1671 iex_debug_pub.LogMessage(G_PKG_NAME || ' ' || l_api_name || ' - status='||retcode);
1672 END IF;
1673
1674 IF (retcode <> FND_API.G_RET_STS_SUCCESS) THEN
1675 FND_MSG_PUB.Count_And_Get
1676 ( p_count => l_msg_count,
1677 p_data => l_msg_data
1678 );
1679
1680 errbuf := l_msg_data;
1681 retcode := '2';
1682 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
1683
1684 for i in 1..l_msg_count loop
1685 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
1686 p_encoded => 'F');
1687 FND_FILE.PUT_LINE(FND_FILE.LOG, errmsg);
1688 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
1689 end loop;
1690 END IF;
1691
1692 END IF; -- END OF CHK Bucket
1693
1694 End if; -- END of Chk RunningLevel
1695
1696 EXCEPTION
1697 WHEN OTHERS THEN
1698 retcode := '2'; --FND_API.G_RET_STS_UNEXP_ERROR;
1699 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||SQLERRM);
1700 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
1701
1702 END SEND_DUNNING_CONCUR;
1703
1704
1705 --clchang 10/28/04 added to fix the gscc warning
1706 BEGIN
1707
1708 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1709
1710
1711 END IEX_DUNNING_PUB;