[Home] [Help]
PACKAGE BODY: APPS.IEX_DUNNING_PUB
Source
1 PACKAGE BODY IEX_DUNNING_PUB AS
2 /* $Header: iexpdunb.pls 120.52.12020000.15 2013/02/20 19:46:25 ehuh ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_DUNNING_PUB';
6 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpdunb.pls';
7 G_Batch_Size NUMBER := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
8
9 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
10 PG_DEBUG NUMBER ;
11
12 Procedure WriteLog ( p_msg IN VARCHAR2)
13 IS
14 BEGIN
15
16 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
17 iex_debug_pub.LogMessage (p_msg);
18 END IF;
19
20 END WriteLog;
21
22
23
24 Procedure Create_AG_DN_XREF
25 (p_api_version IN NUMBER := 1.0,
26 p_init_msg_list IN VARCHAR2 ,
27 p_commit IN VARCHAR2 ,
28 P_AG_DN_XREF_TBL IN IEX_DUNNING_PUB.AG_DN_XREF_TBL_TYPE ,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 x_AG_DN_XREF_ID_TBL OUT NOCOPY IEX_DUNNING_PUB.AG_DN_XREF_ID_TBL_TYPE)
33
34 IS
35 l_api_name CONSTANT VARCHAR2(30) := 'Create_AG_DN_XREF';
36 l_api_version_number CONSTANT NUMBER := 1.0;
37 l_return_status VARCHAR2(1);
38 l_msg_count NUMBER;
39 l_msg_data VARCHAR2(32767);
40 errmsg VARCHAR2(32767);
41 l_AG_DN_XREF_rec IEX_DUNNING_PUB.AG_DN_XREF_REC_TYPE;
42 x_ag_dn_xref_id NUMBER;
43 l_AG_DN_XREF_ID_TBL IEX_DUNNING_PUB.AG_DN_XREF_ID_TBL_TYPE;
44
45
46 BEGIN
47 -- Standard Start of API savepoint
48 SAVEPOINT CREATE_AG_DN_PUB;
49
50 -- Standard call to check for call compatibility.
51 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
52 p_api_version,
53 l_api_name,
54 G_PKG_NAME)
55 THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58
59
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 IF FND_API.to_Boolean( p_init_msg_list )
62 THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66 -- Debug Message
67 -- added by gnramasa for bug 5661324 14-Mar-07
68 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - CreateAgDn: start ');
69
70
71 -- Initialize API return status to SUCCESS
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 --
75 -- API body
76 --
77
78 for i in 1..p_ag_dn_xref_tbl.count
79 loop
80 l_ag_dn_xref_rec := p_ag_dn_xref_tbl(i);
81
82 IEX_DUNNING_PVT.Create_AG_DN_XREF(
83 p_api_version => p_api_version
84 , p_init_msg_list => p_init_msg_list
85 , p_commit => p_commit
86 , p_ag_dn_xref_rec => l_ag_dn_xref_rec
87 , x_ag_dn_xref_id => x_ag_dn_Xref_id
88 , x_return_status => x_return_status
89 , x_msg_count => x_msg_count
90 , x_msg_data => x_msg_data
91 );
92
93 IF x_return_status = FND_API.G_RET_STS_ERROR then
94 raise FND_API.G_EXC_ERROR;
95 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
96 raise FND_API.G_EXC_UNEXPECTED_ERROR;
97 END IF;
98
99 l_ag_dn_xref_id_tbl(i) := x_ag_dn_xref_id;
100
101 END LOOP;
102
103 x_ag_dn_xref_id_tbl := l_ag_dn_xref_id_tbl;
104
105
106 --
107 -- End of API body
108 --
109
110 -- Standard check for p_commit
111 IF FND_API.to_Boolean( p_commit )
112 THEN
113 COMMIT WORK;
114 END IF;
115
116 -- Debug Message
117 -- added by gnramasa for bug 5661324 14-Mar-07
118 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - CreateAgDn: End ');
119
120 -- Standard call to get message count and if count is 1, get message info.
121 FND_MSG_PUB.Count_And_Get
122 ( p_count => x_msg_count,
123 p_data => x_msg_data
124 );
125
126 EXCEPTION
127 WHEN FND_API.G_EXC_ERROR THEN
128 x_return_status := FND_API.G_RET_STS_ERROR;
129 ROLLBACK TO Create_Ag_Dn_PUB;
130 -- added by gnramasa for bug 5661324 14-Mar-07
131 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
132 FND_MSG_PUB.Count_And_Get
133 ( p_count => x_msg_count,
134 p_data => x_msg_data );
135
136 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138 ROLLBACK TO Create_Ag_Dn_PUB;
139 -- added by gnramasa for bug 5661324 14-Mar-07
140 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
141 FND_MSG_PUB.Count_And_Get
142 ( p_count => x_msg_count,
143 p_data => x_msg_data );
144
145 WHEN OTHERS THEN
146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147 ROLLBACK TO Create_Ag_Dn_PUB;
148 -- added by gnramasa for bug 5661324 14-Mar-07
149 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
150 WriteLog('iexpdunb:CreateAgDn:Exc Exception');
151 FND_MSG_PUB.Count_And_Get
152 ( p_count => x_msg_count,
153 p_data => x_msg_data );
154
155 END CREATE_AG_DN_XREF;
156
157
158
159 Procedure Update_AG_DN_XREF
160 (p_api_version IN NUMBER := 1.0,
161 p_init_msg_list IN VARCHAR2 ,
162 p_commit IN VARCHAR2 ,
163 P_AG_DN_XREF_TBL IN IEX_DUNNING_PUB.AG_DN_XREF_TBL_TYPE ,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2)
167 IS
168 l_api_name CONSTANT VARCHAR2(30) := 'Update_AG_DN_XREF';
169 l_api_version_number CONSTANT NUMBER := 1.0;
170 l_return_status VARCHAR2(1);
171 l_msg_count NUMBER;
172 l_msg_data VARCHAR2(32767);
173 errmsg VARCHAR2(32767);
174 l_AG_DN_XREF_rec IEX_DUNNING_PUB.AG_DN_XREF_REC_TYPE ;
175
176 BEGIN
177
178 -- Standard Start of API savepoint
179 SAVEPOINT UPDATE_AG_DN_PUB;
180
181
182 -- Initialize message list if p_init_msg_list is set to TRUE.
183 IF FND_API.to_Boolean( p_init_msg_list )
184 THEN
185 FND_MSG_PUB.initialize;
186 END IF;
187
188 -- Debug Message
189 -- added by gnramasa for bug 5661324 14-Mar-07
190 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn: Start ');
191
192
193 -- Initialize API return status to SUCCESS
194 x_return_status := FND_API.G_RET_STS_SUCCESS;
195
196 --
197 -- Api body
198 --
199
200
201 for i in 1..p_ag_dn_xref_tbl.count
202 loop
203 l_ag_dn_xref_rec := p_ag_dn_xref_tbl(i);
204
205 IEX_DUNNING_PVT.Update_AG_DN_XREF(
206 p_api_version => p_api_version
207 , p_init_msg_list => p_init_msg_list
208 , p_commit => p_commit
209 , p_ag_dn_xref_rec => l_ag_dn_xref_rec
210 , p_ag_dn_xref_id => l_ag_dn_xref_rec.ag_dn_Xref_id
211 , x_return_status => x_return_status
212 , x_msg_count => x_msg_count
213 , x_msg_data => x_msg_data
214 );
215
216 IF x_return_status = FND_API.G_RET_STS_ERROR then
217 raise FND_API.G_EXC_ERROR;
218 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
219 raise FND_API.G_EXC_UNEXPECTED_ERROR;
220 END IF;
221 END LOOP;
222
223 --
224 -- End of API body.
225 --
226
227 -- Standard check for p_commit
228 IF FND_API.to_Boolean( p_commit )
229 THEN
230 COMMIT WORK;
231 END IF;
232
233 -- Debug Message
234 -- Changed by gnramasa for bug 5661324 14-Mar-07
235 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn: end ');
236
237 -- Standard call to get message count and if count is 1, get message info.
238 FND_MSG_PUB.Count_And_Get
239 ( p_count => x_msg_count,
240 p_data => x_msg_data );
241
242 EXCEPTION
243 WHEN FND_API.G_EXC_ERROR THEN
244 x_return_status := FND_API.G_RET_STS_ERROR;
245 ROLLBACK TO Update_Ag_Dn_PUB;
246 -- Changed by gnramasa for bug 5661324 14-Mar-07
247 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
248 errmsg := SQLERRM;
249 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
250 FND_MSG_PUB.Count_And_Get
251 ( p_count => x_msg_count,
252 p_data => x_msg_data );
253
254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256 ROLLBACK TO Update_Ag_Dn_PUB;
257 -- Changed by gnramasa for bug 5661324 14-Mar-07
258 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
259 errmsg := SQLERRM;
260 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
261 FND_MSG_PUB.Count_And_Get
262 ( p_count => x_msg_count,
263 p_data => x_msg_data );
264
265 WHEN OTHERS THEN
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
267 ROLLBACK TO Update_Ag_Dn_PUB;
268 -- Changed by gnramasa for bug 5661324 14-Mar-07
269 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
270 errmsg := SQLERRM;
271 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:error='||errmsg);
272 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateAgDn:Exc Exception');
273 FND_MSG_PUB.Count_And_Get
274 ( p_count => x_msg_count,
275 p_data => x_msg_data );
276
277 END Update_AG_DN_XREF;
278
279
280
281 Procedure Delete_AG_DN_XREF
282 (p_api_version IN NUMBER := 1.0,
283 p_init_msg_list IN VARCHAR2 ,
284 p_commit IN VARCHAR2 ,
285 P_AG_DN_XREF_ID IN NUMBER,
286 x_return_status OUT NOCOPY VARCHAR2,
287 x_msg_count OUT NOCOPY NUMBER,
288 x_msg_data OUT NOCOPY VARCHAR2)
289
290 IS
291
292 l_AG_DN_XREF_id NUMBER ;
293 l_api_name CONSTANT VARCHAR2(30) := 'Delete_AG_DN_XREF';
294 l_api_version_number CONSTANT NUMBER := 1.0;
295 l_return_status VARCHAR2(1);
296 l_msg_count NUMBER;
297 l_msg_data VARCHAR2(32767);
298 errmsg VARCHAR2(32767);
299
300 BEGIN
301 -- Standard Start of API savepoint
302 SAVEPOINT DELETE_AG_DN_PUB;
303
304
305 -- Initialize message list if p_init_msg_list is set to TRUE.
306 IF FND_API.to_Boolean( p_init_msg_list )
307 THEN
308 FND_MSG_PUB.initialize;
309 END IF;
310
311 -- Debug Message
312 -- Changed by gnramasa for bug 5661324 14-Mar-07
313 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start ');
314
315
316 -- Initialize API return status to SUCCESS
317 x_return_status := FND_API.G_RET_STS_SUCCESS;
318
319 --
320 -- Api body
321 --
322 IEX_DUNNING_PVT.Delete_AG_DN_XREF(
323 p_api_version => p_api_version
324 , p_init_msg_list => p_init_msg_list
325 , p_commit => p_commit
326 , p_AG_DN_XREF_id => p_AG_DN_XREF_id
327 , x_return_status => x_return_status
328 , x_msg_count => x_msg_count
329 , x_msg_data => x_msg_data
330 );
331
332 IF x_return_status = FND_API.G_RET_STS_ERROR then
333 raise FND_API.G_EXC_ERROR;
334 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
335 raise FND_API.G_EXC_UNEXPECTED_ERROR;
336 END IF;
337
338 --
339 -- End of API body
340 --
341
342 -- Standard check for p_commit
343 IF FND_API.to_Boolean( p_commit )
344 THEN
345 COMMIT WORK;
346 END IF;
347
348 -- Debug Message
349 -- Changed by gnramasa for bug 5661324 14-Mar-07
350 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End ');
351
352 FND_MSG_PUB.Count_And_Get
353 ( p_count => x_msg_count,
354 p_data => x_msg_data );
355
356 EXCEPTION
357 WHEN FND_API.G_EXC_ERROR THEN
358 x_return_status := FND_API.G_RET_STS_ERROR;
359 ROLLBACK TO Delete_Ag_Dn_PUB;
360 -- Changed by gnramasa for bug 5661324 14-Mar-07
361 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
362 FND_MSG_PUB.Count_And_Get
363 ( p_count => x_msg_count,
364 p_data => x_msg_data );
365
366 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
368 ROLLBACK TO Delete_Ag_Dn_PUB;
369 -- Changed by gnramasa for bug 5661324 14-Mar-07
370 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
371 FND_MSG_PUB.Count_And_Get
372 ( p_count => x_msg_count,
373 p_data => x_msg_data );
374
375 WHEN OTHERS THEN
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 ROLLBACK TO Delete_Ag_Dn_PUB;
378 -- Changed by gnramasa for bug 5661324 14-Mar-07
379 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
380 FND_MSG_PUB.Count_And_Get
381 ( p_count => x_msg_count,
382 p_data => x_msg_data );
383
384 END Delete_AG_DN_XREF;
385
386
387 Procedure custom_where_clause
388 (p_running_level IN VARCHAR2,
389 p_customer_name_low IN VARCHAR2,
390 p_customer_name_high IN VARCHAR2,
391 p_account_number_low IN VARCHAR2,
392 p_account_number_high IN VARCHAR2,
393 p_billto_location_low IN VARCHAR2,
394 p_billto_location_high IN VARCHAR2,
395 p_custom_select OUT NOCOPY VARCHAR2)
396 IS
397 l_custom_select varchar2(2000);
398 l_api_name varchar2(50) := 'custom_where_clause';
399 BEGIN
400
401 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - start ');
402 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_running_level : '||p_running_level);
403 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_customer_name_low : '||p_customer_name_low);
404 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_customer_name_high : '||p_customer_name_high);
405 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_account_number_low : '||p_account_number_low);
406 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_account_number_high : '||p_account_number_high);
407 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_billto_location_low : '||p_billto_location_low);
408 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_billto_location_high : '||p_billto_location_high);
409
410
411
412 if p_running_level <> 'DELINQUENCY' then
413 l_custom_select := ' SELECT p.party_name ' ||
414 ' From hz_cust_acct_sites_all acct_sites, ' ||
415 ' hz_party_sites party_site, ' ||
416 ' hz_cust_accounts ca, ' ||
417 ' hz_cust_site_uses_all site_uses, ' ||
418 ' hz_parties p ' ||
419 ' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
420 ' AND acct_sites.party_site_id = party_site.party_site_id ' ||
421 ' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
422 --' AND site_uses.site_use_code = ''BILL_TO'' ' || -- Bills Receivables
423 ' AND (site_uses.site_use_code = ''BILL_TO'' or site_uses.site_use_code = ''DRAWEE'') ' || -- Bills Receivables
424 ' AND ca.party_id = p.party_id ';
425 else
426 l_custom_select := 'SELECT p.party_name ' ||
427 ' From hz_cust_acct_sites_all acct_sites, ' ||
428 ' hz_party_sites party_site, ' ||
429 ' hz_cust_accounts ca, ' ||
430 ' hz_cust_site_uses_all site_uses, ' ||
431 ' hz_parties p,' ||
432 ' iex_delinquencies_all delin ' ||
433 ' WHERE acct_sites.cust_account_id = ca.cust_account_id ' ||
434 ' AND acct_sites.party_site_id = party_site.party_site_id ' ||
435 ' AND acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id ' ||
436 ' AND site_uses.site_use_code = ''BILL_TO'' ' ||
437 ' AND ca.party_id = p.party_id ' ||
438 ' AND delin.customer_site_use_id = site_uses.site_use_id ';
439 end if;
440
441 -- start for bug 9232261 PNAVEENK
442 if p_customer_name_low IS NOT NULL then
443 l_custom_select := l_custom_select || ' AND upper(p.party_name) >= upper(''' || replace(p_customer_name_low,'''','''''') || ''') ';
444 end if;
445
446 if p_customer_name_high IS NOT NULL then
447 l_custom_select := l_custom_select || ' AND upper(p.party_name) <= upper(''' || replace(p_customer_name_high,'''','''''') || ''') ';
448 end if;
449
450 if p_account_number_low IS NOT NULL then
451 l_custom_select := l_custom_select || ' AND upper(ca.account_number) >= upper(''' || replace(p_account_number_low,'''','''''') || ''') ';
452 end if;
453
454 if p_account_number_high IS NOT NULL then
455 l_custom_select := l_custom_select || ' AND upper(ca.account_number) <= upper(''' || replace(p_account_number_high,'''','''''') || ''') ';
456 end if;
457
458 if p_billto_location_low IS NOT NULL then
459 l_custom_select := l_custom_select || ' AND upper(site_uses.location) >= upper(''' || replace(p_billto_location_low,'''','''''') || ''') ';
460 end if;
461
462 if p_billto_location_high IS NOT NULL then
463 l_custom_select := l_custom_select || ' AND upper(site_uses.location) <= upper(''' || replace(p_billto_location_high,'''','''''') || ''') ';
464 end if;
465 -- end for bug 9232261
466
467 if p_running_level = 'CUSTOMER' then
468 l_custom_select := l_custom_select || ' AND p.party_id ';
469 elsif p_running_level = 'ACCOUNT' then
470 l_custom_select := l_custom_select || ' AND ca.cust_account_id ';
471 elsif p_running_level = 'BILL_TO' then
472 l_custom_select := l_custom_select || ' AND site_uses.site_use_id ';
473 else
474 l_custom_select := l_custom_select || ' AND delin.delinquency_id ';
475 end if;
476
477 p_custom_select := l_custom_select;
478 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_custom_select : '||l_custom_select);
479 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End ');
480
481 END custom_where_clause;
482
483 /*=================================================
484 * clchang added new level 'BILL_TO' in 11.5.10.
485 *=================================================*/
486 Procedure Send_Dunning
487 (p_api_version IN NUMBER := 1.0,
488 p_init_msg_list IN VARCHAR2 ,
489 p_commit IN VARCHAR2 ,
490 p_running_level IN VARCHAR2 ,
491 p_parent_request_id IN NUMBER, -- added by gnramasa for bug 5661324 14-Mar-07
492 p_dunning_plan_id in number,
493 p_correspondence_date IN DATE,
494 p_dunning_mode IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
495 p_process_err_rec_only IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
496 p_no_of_workers IN number := 1, -- added by gnramasa for bug 8489610 14-May-09
497 p_single_staged_letter IN VARCHAR2 DEFAULT 'N',-- added by gnramasa for bug stageddunning 28-Dec-09
498 p_customer_name_low IN VARCHAR2,
499 p_customer_name_high IN VARCHAR2,
500 p_account_number_low IN VARCHAR2,
501 p_account_number_high IN VARCHAR2,
502 p_billto_location_low IN VARCHAR2,
503 p_billto_location_high IN VARCHAR2,
504 p_order_output_by IN VARCHAR2,
505 x_return_status OUT NOCOPY VARCHAR2,
506 x_msg_count OUT NOCOPY NUMBER,
507 x_msg_data OUT NOCOPY VARCHAR2) IS
508
509 CURSOR C_GET_BUCKET (p_dunning_plan_id number) IS
510 select aging_bucket_id from iex_dunning_plans_vl
511 where dunning_plan_id = p_dunning_plan_id;
512
513 --
514 l_api_name CONSTANT VARCHAR2(30) := 'Send_Dunning';
515 l_api_version_number CONSTANT NUMBER := 1.0;
516 l_return_status VARCHAR2(10);
517 l_msg_count NUMBER;
518 l_msg_data VARCHAR2(32767);
519 l_del_id NUMBER;
520 l_party_id NUMBER;
521 l_account_id NUMBER;
522 l_customer_site_use_id NUMBER;
523 l_score NUMBER;
524 l_delinquencies_tbl IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE;
525 l_delinquency_rec IEX_DELINQUENCY_PUB.DELINQUENCY_REC_TYPE;
526 l_bucket VARCHAR2(100);
527 nIdx NUMBER;
528 nDelIdx NUMBER;
529 errmsg VARCHAR2(32767);
530 l_error NUMBER := 0;
531 l_dunning_letter_set_id number;--Added for Bug 11656175 01-Mar-2011 barathsr
532
533 l_repeat boolean := true;
534
535 -- added by gnramasa for bug 5661324 14-Mar-07
536 l_object_id NUMBER;
537 i number;
538 Type refCur is Ref Cursor;
539 sql_cur refCur;
540 sql_cur2 refCur;
541 sql_cur3 refCur;
542 sql_cur4 refCur;
543 sql_cur5 refCur;
544 sql_cur6 refCur;
545 vPLSQL VARCHAR2(2000);
546 vPLSQL2 VARCHAR2(5000);
547 vPLSQL3 VARCHAR2(5000);
548 vPLSQL4 VARCHAR2(5000);
549 vPLSQL5 VARCHAR2(5000);
550 vPLSQL6 VARCHAR2(5000);
551 vSelectColumn varchar2(25);
552
553 cursor c_scoring_engine(p_dunning_plan_id number) is
554 select sc.score_id
555 ,sc.score_name
556 from iex_dunning_plans_vl d
557 ,iex_scores sc
558 where d.dunning_plan_id = p_dunning_plan_id
559 and sc.score_id = d.score_id;
560
561 l_score_engine_id number;
562 l_score_engine_name varchar2(60);
563 --Start bug 7197038 gnramasa 9th july 08
564 cursor c_filter_object(p_dunning_plan_id number) is
565 select iof.select_column, iof.entity_name
566 from IEX_OBJECT_FILTERS iof,iex_dunning_plans_vl ipd, IEX_SCORES isc
567 where ipd.dunning_plan_id = p_dunning_plan_id
568 and ipd.score_id=isc.score_id
569 and isc.score_id=iof.object_id
570 and object_filter_type = 'IEXSCORE';
571
572 l_select_column varchar2(50);
573 l_entity_name varchar2(50);
574
575 --Start adding for bug 8489610 by gnramasa 14-May-09
576 l_req_id number;
577
578 l_dunning_rec_upd IEX_DUNNING_PUB.DUNNING_REC_TYPE;
579 l_submit_request_id NUMBER;
580 l_xml_request_id NUMBER;
581 l_conf_mode varchar2(10);
582 l_status varchar2(10);
583 l_no_of_workers number;
584
585 --End adding for bug 8489610 by gnramasa 14-May-09
586 l_custom_select varchar2(2000);
587 l_dunning_id number;
588 l_dunning_object_id number;
589 l_dunning_level varchar2(20);
590 l_atleast_one_rec boolean;
591 l_no_init_successful_rec number;
592 l_no_final_successful_rec number;
593 l_no_init_successful_inv_rec number;
594 l_no_final_successful_inv_rec number;
595 l_diff_bw_init_fi_su_rec number;
596 l_diff_bw_init_fi_su_inv_rec number;
597 l_confirmation_mode varchar2(15);
598 l_req_mode varchar2(15);
599 l_no_rec_conf number;
600 l_no_err_dunn_rec number;
601 l_con_update_re_st boolean;
602 l_update_cp_as_err boolean := FALSE;
603 l_err_message varchar2(200);
604 l_con_proc_mode varchar2(10);
605 l_no_of_rec_prc number;
606 l_no_of_succ_rec number;
607 l_no_of_fail_rec number;
608 l_no_of_rec_prc_bylastrun number;
609 l_no_of_succ_rec_bylastrun number;
610 l_no_of_fail_rec_bylastrun number;
611 l_process_err_rec_only varchar2(3);
612 l_dunning_type varchar2(20);
613 l_ag_dn_xref_id number;
614 l_dunn_invoice_ct NUMBER := 0;
615 l_object_type varchar2(20);
616
617 cursor c_req_dunn_mode (p_req_id number) is
618 select dunning_mode
619 from iex_dunnings
620 where request_id = p_req_id;
621
622 cursor c_req_is_confirmed (p_req_id number) is
623 select count(1)
624 from iex_dunnings
625 where request_id = p_req_id
626 and confirmation_mode = 'CONFIRMED';
627
628 cursor c_no_err_dunn_rec (p_req_id number) is
629 select count(1)
630 from iex_dunnings id
631 where id.request_id = p_req_id
632 and id.delivery_status is not null
633 and id.object_type <> 'IEX_INVOICES'
634 and id.dunning_id = (select max(d.dunning_id) from iex_dunnings d
635 where d.dunning_object_id = id.dunning_object_id
636 and d.dunning_level = id.dunning_level and d.request_id = id.request_id
637 and d.object_type <> 'IEX_INVOICES');
638
639 cursor c_no_success_dunn_rec (p_req_id number) is
640 select count(1)
641 from iex_dunnings id
642 where id.request_id = p_req_id
643 and id.delivery_status is null
644 and id.object_type <> 'IEX_INVOICES'
645 and id.dunning_id = (select max(d.dunning_id) from iex_dunnings d
646 where d.dunning_object_id = id.dunning_object_id
647 and d.dunning_level = id.dunning_level and d.request_id = id.request_id
648 and d.object_type <> 'IEX_INVOICES');
649
650 cursor c_no_success_inv_rec (p_req_id number) is
651 select count(1)
652 from iex_dunnings id
653 where id.request_id = p_req_id
654 and id.delivery_status is null
655 and id.object_type = 'IEX_INVOICES'
656 and id.dunning_id = (select max(d.dunning_id) from iex_dunnings d
657 where d.dunning_object_id = id.dunning_object_id
658 and d.dunning_level = id.dunning_level and d.request_id = id.request_id
659 and d.object_type = 'IEX_INVOICES');
660
661 cursor c_get_invoice_ct (p_conc_req_id number) is
662 select count(idt.cust_trx_id)
663 from iex_dunning_transactions idt,
664 iex_dunnings dunn,
665 iex_ag_dn_xref xref,
666 ra_customer_trx trx
667 where idt.dunning_id = dunn.dunning_id
668 and dunn.request_id = p_conc_req_id
669 and dunn.ag_dn_xref_id = xref.ag_dn_xref_id
670 and xref.invoice_copies = 'Y'
671 and idt.cust_trx_id is not null
672 and trx.customer_trx_id = idt.cust_trx_id
673 and trx.printing_option = 'PRI';
674
675 cursor c_get_inv_count_in_errmode (p_conc_req_id number, p_max_dunn_trx_id number) is
676 select count(idt.cust_trx_id)
677 from iex_dunning_transactions idt,
678 iex_dunnings dunn,
679 iex_ag_dn_xref xref,
680 ra_customer_trx trx
681 where idt.dunning_id = dunn.dunning_id
682 and dunn.request_id = p_conc_req_id
683 and dunn.ag_dn_xref_id = xref.ag_dn_xref_id
684 and xref.invoice_copies = 'Y'
685 and idt.cust_trx_id is not null
686 and idt.dunning_trx_id > p_max_dunn_trx_id
687 and trx.customer_trx_id = idt.cust_trx_id
688 and trx.printing_option = 'PRI';
689
690 cursor c_max_dunning_trx_id (p_conc_req_id number) is
691 select max(idt.dunning_trx_id)
692 from iex_dunning_transactions idt,
693 iex_dunnings dunn
694 where idt.dunning_id = dunn.dunning_id
695 and dunn.request_id = p_conc_req_id;
696
697 cursor c_object_type (p_req_id number) is
698 select object_type
699 from iex_dunnings
700 where request_id = p_req_id
701 order by dunning_id;
702
703 l_payment_schedule_id number;
704 l_stage_number number;
705 l_max_dunning_trx_id number;
706 t_cnt number := 0;
707
708 BEGIN
709 -- Standard Start of API savepoint
710 SAVEPOINT SEND_DUNNING_PUB;
711
712 -- Changed by gnramasa for bug 5661324 14-Mar-07
713 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - start ');
714 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - running_level = '||p_running_level);
715 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_parent_request_id '||p_parent_request_id);
716 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - dunning_plan_id '||p_dunning_plan_id);
717
718 -- Initialize message list if p_init_msg_list is set to TRUE.
719 IF FND_API.to_Boolean( p_init_msg_list )
720 THEN
721 FND_MSG_PUB.initialize;
722 END IF;
723
724 -- Debug Message
725
726 if (p_dunning_plan_id is null)
727 THEN
728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
729 END IF;
730 -- Initialize API return status to SUCCESS
731 x_return_status := FND_API.G_RET_STS_SUCCESS;
732
733 select nvl(dunning_type,'DAYS_OVERDUE'),dunning_letter_set_id --Added for Bug 11656175 01-Mar-2011 barathsr
734 into l_dunning_type,l_dunning_letter_set_id
735 from IEX_DUNNING_PLANS_B
736 where dunning_plan_id = p_dunning_plan_id;
737 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_dunning_type ' || l_dunning_type);
738
739
740
741 if l_dunning_type <> 'STAGED_DUNNING' then
742 Open C_Get_BUCKET (p_dunning_plan_id);
743 Fetch C_Get_Bucket into l_bucket;
744 If ( C_GET_Bucket%NOTFOUND ) Then
745 -- Changed by gnramasa for bug 5661324 14-Mar-07
746 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - NO Bucket');
747 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Bucket');
748 x_return_status := FND_API.G_RET_STS_ERROR;
749 RAISE FND_API.G_EXC_ERROR;
750 end if;
751 CLOSE C_GET_Bucket;
752 -- Changed by gnramasa for bug 5661324 14-Mar-07
753 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Bucket='||l_bucket);
754 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Current Bucket='||l_bucket);
755 end if;
756
757 --
758 -- added by gnramasa for bug 5661324 14-Mar-07
759 -- next get all the IDs we need to fill into array to pass to send_level_dunning OR send dunning
760 Open c_filter_object (p_dunning_plan_id);
761 Fetch c_filter_object into l_select_column,l_entity_name;
762 If ( c_filter_object%NOTFOUND ) Then
763 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - NO filter object');
764 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No filter object');
765 x_return_status := FND_API.G_RET_STS_ERROR;
766 RAISE FND_API.G_EXC_ERROR;
767 end if;
768 CLOSE c_filter_object;
769 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_select_column: '|| l_select_column);
770 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_entity_name: '||l_entity_name);
771 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_select_column: '|| l_select_column);
772 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_entity_name: '||l_entity_name);
773
774 if (p_customer_name_low IS NOT NULL OR p_customer_name_high IS NOT NULL OR p_account_number_low IS NOT NULL OR
775 p_account_number_high IS NOT NULL OR p_billto_location_low IS NOT NULL OR p_billto_location_high IS NOT NULL) then
776 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Calling custom_where_clause ');
777 --Call the procedure custom_where_clause to construct the SQL based on the cp input parameters.
778 custom_where_clause
779 (p_running_level => p_running_level,
780 p_customer_name_low => p_customer_name_low,
781 p_customer_name_high => p_customer_name_high,
782 p_account_number_low => p_account_number_low,
783 p_account_number_high => p_account_number_high,
784 p_billto_location_low => p_billto_location_low,
785 p_billto_location_high => p_billto_location_high,
786 p_custom_select => l_custom_select);
787
788 WriteLog(G_PKG_NAME || ' ' || l_api_name || 'After call custom_where_clause :' || l_custom_select);
789 end if;
790
791 if p_running_level = 'CUSTOMER' then
792 vSelectColumn := 'party_cust_id';
793 vPLSQL2 := ' select ' ||
794 ' par_site.party_id ' ||
795 ' ,acct_site.cust_account_id ' ||
796 ' ,site_use.site_use_id ' ||
797 ' ,decode(site_use.site_use_code, ' ||
798 ' ''DUN'', 1, ' ||
799 ' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
800 'from HZ_CUST_SITE_USES site_use ' ||
801 ' ,HZ_CUST_ACCT_SITES acct_site ' ||
802 ' ,hz_party_sites par_site ' ||
803 ' ,iex_dunning_plans_vl ' ||
804 'where ' ||
805 ' par_site.party_id = :1 and ' ||
806 ' par_site.status = ''A'' and ' ||
807 ' par_site.party_site_id = acct_site.party_site_id and ' ||
808 ' acct_site.status = ''A'' and ' ||
809 ' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
810 ' site_use.status = ''A'' and ' ||
811 ' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
812 ' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = par_site.party_id) ' ;
813 if l_custom_select IS NOT NULL then
814 vPLSQL2 := vPLSQL2 || ' and exists ( ' || l_custom_select ||' = par_site.party_id) ' ;
815 end if;
816 vPLSQL2 := vPLSQL2 || ' order by Display_Order ';
817
818 elsif p_running_level = 'ACCOUNT' then
819 vSelectColumn := 'cust_account_id';
820 vPLSQL2 := ' select ' ||
821 ' par_site.party_id ' ||
822 ' ,acct_site.cust_account_id ' ||
823 ' ,site_use.site_use_id ' ||
824 ' ,decode(site_use.site_use_code, ' ||
825 ' ''DUN'', 1, ' ||
826 ' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
827 'from HZ_CUST_SITE_USES site_use ' ||
828 ' ,HZ_CUST_ACCT_SITES acct_site ' ||
829 ' ,hz_party_sites par_site ' ||
830 ' ,iex_dunning_plans_vl ' ||
831 'where ' ||
832 ' acct_site.cust_account_id = :1 and ' ||
833 ' par_site.status = ''A'' and ' ||
834 ' par_site.party_site_id = acct_site.party_site_id and ' ||
835 ' acct_site.status = ''A'' and ' ||
836 ' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
837 ' site_use.status = ''A'' and ' ||
838 ' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
839 ' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = acct_site.cust_account_id) ';
840 if l_custom_select IS NOT NULL then
841 vPLSQL2 := vPLSQL2 || ' and exists ( ' || l_custom_select ||' = acct_site.cust_account_id) ';
842 end if;
843 --Begin Bug 11656175 01-Mar-2011 barathsr
844 if l_dunning_letter_set_id is not null then
845 vPLSQL2 := vPLSQL2 || ' and exists (select 1 from hz_customer_profiles prof where prof.cust_account_id = acct_site.cust_account_id '||
846 ' and prof.site_use_id is null and prof.dunning_letter_set_id = '||l_dunning_letter_set_id||')';
847 end if;
848 --End Bug 11656175 01-Mar-2011 barathsr
849 vPLSQL2 := vPLSQL2 || ' order by Display_Order ';
850
851 FND_FILE.PUT_LINE(FND_FILE.LOG, 'acct_query--> '||vPLSQL2);
852
853 elsif p_running_level = 'BILL_TO' then
854 vSelectColumn := 'customer_site_use_id';
855 vPLSQL2 := ' select ' ||
856 ' par_site.party_id ' ||
857 ' ,acct_site.cust_account_id ' ||
858 ' ,site_use.site_use_id ' ||
859 ' ,decode(site_use.site_use_code, ' ||
860 ' ''DUN'', 1, ' ||
861 ' ''BILL_TO'', decode(site_use.primary_flag, ''Y'', 2, 3)) Display_Order ' ||
862 'from HZ_CUST_SITE_USES site_use ' ||
863 ' ,HZ_CUST_ACCT_SITES acct_site ' ||
864 ' ,hz_party_sites par_site ' ||
865 ' ,iex_dunning_plans_vl ' ||
866 'where ' ||
867 ' site_use.site_use_id = :1 and ' ||
868 ' par_site.status = ''A'' and ' ||
869 ' par_site.party_site_id = acct_site.party_site_id and ' ||
870 ' acct_site.status = ''A'' and ' ||
871 ' acct_site.cust_acct_site_id = site_use.cust_acct_site_id and ' ||
872 ' site_use.status = ''A'' and ' ||
873 ' iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id and' ||
874 ' exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = site_use.site_use_id) ';
875 if l_custom_select IS NOT NULL then
876 vPLSQL2 := vPLSQL2 || ' and exists ( ' || l_custom_select ||' = site_use.site_use_id) ';
877 end if;
878 --Begin Bug 11656175 01-Mar-2011 barathsr
879 if l_dunning_letter_set_id is not null then
880 vPLSQL2 := vPLSQL2 || ' and exists (select 1 from hz_customer_profiles prof where prof.cust_account_id = acct_site.cust_account_id '||
881 ' and prof.site_use_id = site_use.site_use_id and prof.dunning_letter_set_id = '||l_dunning_letter_set_id||')';
882 end if;
883 --End Bug 11656175 01-Mar-2011 barathsr
884 vPLSQL2 := vPLSQL2 || ' order by Display_Order ';
885
886 FND_FILE.PUT_LINE(FND_FILE.LOG, 'acct_query--> '||vPLSQL2);
887
888 else -- we are running at delinquency level
889 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Delinquency Level - after fix 14797202..');
890 vSelectColumn := 'delinquency_id';
891 vPLSQL2 := 'SELECT delinquency_ID ' ||
892 ' ,party_cust_id ' ||
893 ' ,cust_account_id ' ||
894 ' ,customer_site_use_id ' ||
895 --' ,score_value ' ||
896 ' FROM IEX_DELINQUENCIES del' ||
897 ' ,iex_dunning_plans_vl ' ||
898 ' WHERE STATUS in (''DELINQUENT'', ''PREDELINQUENT'') ' ||
899 ' AND DELINQUENCY_ID = :1 ' ||
900 ' AND iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ;
901 --||
902 --' AND del.score_id = iex_dunning_plans_vl.score_id' ;
903 --' AND exists (select ' || l_select_column || ' from ' || l_entity_name || ' where '|| l_select_column ||' = del.DELINQUENCY_ID) ';
904
905 -- bug 14797201.... begin...
906 if l_select_column = 'PAYMENT_SCHEDULE_ID' then
907 vPLSQL2 := vPLSQL2 || ' AND exists (select 1 from ' || l_entity_name ||' len where len.PAYMENT_SCHEDULE_ID = nvl(del.PAYMENT_SCHEDULE_ID,0)) ';
908
909 elsif l_select_column = 'DELINQUENCY_ID' then
910 vPLSQL2 := vPLSQL2 ||' AND exists (select 1 from ' || l_entity_name || ' len where len.delinquency_id = del.DELINQUENCY_ID) ' ;
911
912 end if;
913 -- bug 14797201.... end...
914
915 if l_custom_select IS NOT NULL then
916 vPLSQL2 := vPLSQL2 || ' and exists ( ' || l_custom_select ||' = del.DELINQUENCY_ID) ';
917 end if;
918
919
920 --Begin Bug 11656175 01-Mar-2011 barathsr
921 if l_dunning_letter_set_id is not null then
922 vPLSQL2 := vPLSQL2 || ' and exists (select 1 from hz_customer_profiles prof where prof.cust_account_id = del.cust_account_id '||
923 ' and prof.site_use_id = del.customer_site_use_id and prof.dunning_letter_set_id = '||l_dunning_letter_set_id||')';
924 end if;
925 --End Bug 11656175 01-Mar-2011 barathsr
926 --'ORDER BY ' || vSelectColumn || ' ,delinquency_id';
927 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Delinquency level acct_query--> : vPLSQL2 = '||vPLSQL2); -- bug 14797201
928 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Delinquency level acct_query : vPLSQL2 = ' || vPLSQL2); -- bug 14797201
929 end if;
930
931 -- Begin Bills Receivables
932 begin
933 select count(*) into t_cnt from iex_delinquencies , iex_dunning_plans_vl
934 where (status = DECODE(include_current,'Y','CURRENT','') or status in ('PREDELINQUENT','DELINQUENT'))
935 and exists (select 1 from hz_cust_site_uses_all site_uses where site_use_code = 'BILL_TO' and site_uses.site_use_id = customer_site_use_id)
936 and iex_dunning_plans_vl.dunning_plan_id = p_dunning_plan_id;
937
938 exception
939 when others then null;
940 end;
941 -- end Bills Receivables
942
943 -- fetch the party/account/site_use/delinquency from iex_delinquencies table
944 if p_parent_request_id is null then
945 /*
946 vPLSQL := ' SELECT distinct ' || vSelectColumn ||
947 ' FROM IEX_DELINQUENCIES , IEX_DUNNING_PLANS_VL '||
948 ' where (status = DECODE(include_current,''Y'',''CURRENT'','' '') or status in (''PREDELINQUENT'',''DELINQUENT'')) ' || -- Bills Receivables
949 ' and iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ||
950 ' order by ' || vSelectColumn;
951 */
952 /* bug 16303059 start...
953 if t_cnt > 0 then
954 vPLSQL := ' SELECT distinct ' || vSelectColumn ||
955 ' FROM IEX_DELINQUENCIES , IEX_DUNNING_PLANS_VL '||
956 ' where (status = DECODE(include_current,''Y'',''CURRENT'','' '') or status in (''PREDELINQUENT'',''DELINQUENT'')) ' || -- Bills Receivables
957 ' and exists (select 1 from hz_cust_site_uses_all site_uses where site_use_code = ''BILL_TO'' and site_uses.site_use_id = customer_site_use_id) ' ||
958 -- ' WHERE STATUS in (''DELINQUENT'', ''PREDELINQUENT'') ' || Bills Receivables
959 ' and iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ||
960 ' order by ' || vSelectColumn;
961 else
962 bug 16303059 end... */
963 vPLSQL := ' SELECT distinct ' || vSelectColumn ||
964 ' FROM IEX_DELINQUENCIES , IEX_DUNNING_PLANS_VL '||
965 ' where (status = DECODE(include_current,''Y'',''CURRENT'','' '') or status in (''PREDELINQUENT'',''DELINQUENT'')) '|| -- Bills Receivables
966 ' and exists (select 1 from hz_cust_site_uses_all site_uses where (site_use_code = ''DRAWEE'' or site_use_code = ''BILL_TO'') and site_uses.site_use_id = customer_site_use_id) ' ||
967 ' and iex_dunning_plans_vl.dunning_plan_id = :p_dunning_plan_id ' ||
968 ' order by ' || vSelectColumn;
969 -- end if;
970 else
971 vPLSQL := ' SELECT distinct object_id ' ||
972 ' FROM IEX_DUNNINGS ID ' ||
973 --' WHERE DELIVERY_STATUS = ''ERROR'' ' ||
974 ' WHERE DELIVERY_STATUS IS NOT NULL ' ||
975 --' AND STATUS = ''OPEN'' ' ||
976 ' AND REQUEST_ID = :1 ' ||
977 ' AND DUNNING_LEVEL = :2 ' ||
978 ' AND ID.object_type <> ''IEX_INVOICES'' ' ||
979 ' AND dunning_id = (select max(d.dunning_id) from iex_dunnings d ' ||
980 ' where d.dunning_object_id = id.dunning_object_id ' ||
981 ' and d.dunning_level=id.dunning_level and d.request_id = id.request_id ' ||
982 ' and d.object_type <> ''IEX_INVOICES'' )';
983 end if;
984
985 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL ' || vPLSQL);
986 FND_FILE.PUT_LINE(FND_FILE.LOG, 'acct_query--> '||vPLSQL);
987 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL2 ' || vPLSQL2);
988 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - open Cursor');
989
990 if p_parent_request_id is null then
991 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - no bind ');
992 open sql_cur for vPLSQL using p_dunning_plan_id;
993
994 else
995 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - binding with ' || p_parent_request_id || ' and ' || p_running_level);
996 open sql_cur for vPLSQL using p_parent_request_id, p_running_level;
997
998 end if;
999
1000 if p_process_err_rec_only = 'Y' then
1001 open c_no_err_dunn_rec(p_parent_request_id);
1002 fetch c_no_err_dunn_rec into l_no_of_rec_prc_bylastrun;
1003 close c_no_err_dunn_rec;
1004 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_no_of_rec_prc_bylastrun: '||l_no_of_rec_prc_bylastrun);
1005
1006 open c_max_dunning_trx_id (p_parent_request_id);
1007 fetch c_max_dunning_trx_id into l_max_dunning_trx_id;
1008 close c_max_dunning_trx_id;
1009 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_max_dunning_trx_id: '||l_max_dunning_trx_id);
1010 end if;
1011
1012 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Deciding the request id ');
1013 if p_parent_request_id is not null then
1014 l_req_id := p_parent_request_id;
1015 else
1016 l_req_id := FND_GLOBAL.Conc_Request_Id;
1017 end if;
1018 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_req_id: '||l_req_id);
1019
1020 open c_no_success_dunn_rec(l_req_id);
1021 fetch c_no_success_dunn_rec into l_no_init_successful_rec;
1022 close c_no_success_dunn_rec;
1023 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_no_init_successful_rec : '|| l_no_init_successful_rec);
1024
1025 open c_no_success_inv_rec(l_req_id);
1026 fetch c_no_success_inv_rec into l_no_init_successful_inv_rec;
1027 close c_no_success_inv_rec;
1028 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_no_init_successful_inv_rec : '|| l_no_init_successful_inv_rec);
1029
1030 open c_req_dunn_mode(l_req_id);
1031 fetch c_req_dunn_mode into l_req_mode;
1032 close c_req_dunn_mode;
1033
1034 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_req_mode: '||l_req_mode);
1035
1036 if (l_req_mode = 'DRAFT') and (p_parent_request_id IS NOT NULL) and (p_dunning_mode = 'FINAL') then
1037 l_confirmation_mode := 'CONFIRM';
1038 else
1039 l_confirmation_mode := NULL;
1040 end if;
1041 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_confirmation_mode: '||l_confirmation_mode);
1042
1043 if l_req_mode = 'FINAL' and p_dunning_mode = 'DRAFT' then
1044 l_con_proc_mode := 'FINALDRAFT';
1045 l_update_cp_as_err := TRUE;
1046 goto end_loop;
1047 end if;
1048
1049 open c_req_is_confirmed(l_req_id);
1050 fetch c_req_is_confirmed into l_no_rec_conf;
1051 close c_req_is_confirmed;
1052
1053 if l_req_mode = 'DRAFT' and p_dunning_mode = 'DRAFT' and l_no_rec_conf > 0 then
1054 l_con_proc_mode := 'DRAFTDRAFT';
1055 l_update_cp_as_err := TRUE;
1056 goto end_loop;
1057 end if;
1058 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_no_rec_conf: '||l_no_rec_conf);
1059
1060 --Start adding for bug 8489610 by gnramasa 14-May-09
1061
1062 if (p_parent_request_id is NULL OR p_process_err_rec_only = 'Y') then
1063 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' p_running_level : ' || p_running_level);
1064 if p_running_level <> 'DELINQUENCY' then
1065
1066 LOOP
1067 fetch sql_cur into l_object_id;
1068 exit when sql_cur%NOTFOUND;
1069 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - fetched ' || l_object_id);
1070 --open sql_cur2 for vPLSQL2 using l_object_id,p_dunning_plan_id,l_select_column,l_entity_name,l_select_column;
1071 open sql_cur2 for vPLSQL2 using l_object_id,p_dunning_plan_id;
1072 fetch sql_cur2 into l_party_id, l_account_id, l_customer_site_use_id, l_del_id;
1073 if sql_cur2%FOUND then
1074 --l_atleast_one_rec := TRUE;
1075 l_delinquencies_Tbl(1).party_cust_id := l_party_id;
1076 l_delinquencies_Tbl(1).cust_account_id := l_account_id;
1077 l_delinquencies_Tbl(1).customer_site_use_id := l_customer_site_use_id;
1078 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_party_id ' || l_delinquencies_Tbl(1).party_cust_id);
1079 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_account_id ' || l_delinquencies_Tbl(1).cust_account_id);
1080 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_customer_site_use_id ' || l_delinquencies_Tbl(1).customer_site_use_id);
1081
1082 --if l_confirmation_mode is NULL then
1083
1084 if l_dunning_type = 'STAGED_DUNNING' then
1085 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - calling Send_Level_Staged_Dunning');
1086 IEX_DUNNING_PVT.Send_Level_Staged_Dunning(p_api_version => p_api_version
1087 ,p_init_msg_list => p_init_msg_list
1088 ,p_commit => p_commit
1089 ,p_running_level => p_running_level
1090 ,p_dunning_plan_id => p_dunning_plan_id
1091 ,p_correspondence_date => p_correspondence_date
1092 ,p_delinquencies_tbl => l_delinquencies_tbl
1093 ,p_parent_request_id => p_parent_request_id
1094 ,p_dunning_mode => p_dunning_mode
1095 ,p_single_staged_letter => p_single_staged_letter -- added by gnramasa for bug stageddunning 28-Dec-09
1096 ,p_confirmation_mode => l_confirmation_mode
1097 ,x_return_status => l_return_status
1098 ,x_msg_count => l_msg_count
1099 ,x_msg_data => l_msg_data);
1100 else
1101 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - calling send_level_dunning');
1102 --Begin bug#14173985 schekuri 14-Jun-2012
1103 iex_dunning_pvt.g_included_current_invs := 'N';
1104 iex_dunning_pvt.g_included_unapplied_rec := 'N';
1105 --End bug#14173985 schekuri 14-Jun-2012
1106 IEX_DUNNING_PVT.Send_Level_Dunning(p_api_version => p_api_version
1107 ,p_init_msg_list => p_init_msg_list
1108 ,p_commit => p_commit
1109 ,p_running_level => p_running_level
1110 ,p_dunning_plan_id => p_dunning_plan_id
1111 ,p_delinquencies_tbl => l_delinquencies_tbl
1112 ,p_parent_request_id => p_parent_request_id
1113 ,p_dunning_mode => p_dunning_mode
1114 ,p_confirmation_mode => l_confirmation_mode
1115 ,p_correspondence_date => p_correspondence_date
1116 ,x_return_status => l_return_status
1117 ,x_msg_count => l_msg_count
1118 ,x_msg_data => l_msg_data);
1119 end if;
1120
1121 IF l_return_status = 'SKIP' then
1122 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - skip this account');
1123 l_return_status := FND_API.G_RET_STS_SUCCESS;
1124
1125 elsif l_return_status = 'X' then
1126 if l_repeat then
1127 WriteLog('get scoring engine');
1128 open c_scoring_engine(p_dunning_plan_id);
1129 fetch c_scoring_engine into l_score_engine_id, l_score_engine_name;
1130 close c_scoring_engine;
1131 FND_FILE.PUT_LINE(FND_FILE.LOG,'Validate correct scoring engine was run for this dunning plan.');
1132 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine Name: ' || l_score_engine_name);
1133 FND_FILE.PUT_LINE(FND_FILE.LOG,'Scoring Engine ID: ' || l_score_engine_id);
1134 l_repeat := false;
1135 else
1136 l_return_status := FND_API.G_RET_STS_SUCCESS;
1137 end if;
1138
1139 ELSIF l_return_status = FND_API.G_RET_STS_ERROR then
1140 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - send_level_Failed - CONTINUE');
1141 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1142 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - send_level_Failed - CONTINUE');
1143 END IF;
1144
1145 --end if; --if l_confirmation_mode is NULL then
1146 else
1147 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_object_id: '||l_object_id || ' does not exist in filter object :' ||l_entity_name || ' so skipping');
1148 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_object_id: '||l_object_id || ' does not exist in filter object :' ||l_entity_name || ' so skipping');
1149 end if;
1150 close sql_cur2 ;
1151 end loop; -- sql_cur
1152 close sql_cur;
1153
1154 else -- we are running at delinquency level
1155
1156 i := 0;
1157 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - fetching delinquencies');
1158 /*
1159 Open C_Get_DEL (p_dunning_plan_id);
1160 LOOP
1161 Fetch C_Get_DEL into l_del_id, l_party_id, l_account_id, l_customer_site_use_id, l_score;
1162
1163 --If ( C_GET_DEL%NOTFOUND ) Then
1164 -- FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Delinquency');
1165 -- x_return_status := FND_API.G_RET_STS_ERROR;
1166 --end if;
1167
1168 exit when C_GET_DEL%NOTFOUND;
1169 */
1170 LOOP
1171 fetch sql_cur into l_object_id;
1172 exit when sql_cur%NOTFOUND;
1173 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - fetched ' || l_object_id);
1174 open sql_cur2 for vPLSQL2 using l_object_id,p_dunning_plan_id;
1175 --fetch sql_cur2 into l_del_id, l_party_id, l_account_id, l_customer_site_use_id, l_score;
1176 fetch sql_cur2 into l_del_id, l_party_id, l_account_id, l_customer_site_use_id;
1177 if SQL_CUR2%FOUND then
1178 --l_atleast_one_rec := TRUE;
1179 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_del_id' || l_del_id);
1180 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_party_id ' || l_party_id);
1181 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_account_id ' || l_account_id);
1182 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_customer_site_use_id ' || l_customer_site_use_id);
1183 --WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_score ' || l_score);
1184
1185 i := i + 1;
1186 l_delinquencies_Tbl(i).delinquency_id := l_del_id;
1187 l_delinquencies_Tbl(i).party_cust_id := l_party_id;
1188 l_delinquencies_Tbl(i).cust_account_id := l_account_id;
1189 l_delinquencies_Tbl(i).customer_site_use_id := l_customer_site_use_id;
1190 --l_delinquencies_Tbl(i).score_value := l_score;
1191 end if;
1192
1193 Close sql_cur2;
1194 end loop; -- sql_cur
1195 close sql_cur;
1196
1197 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - fetched ' || l_delinquencies_Tbl.count || ' delinquencies');
1198 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Delinquency Level');
1199
1200 --if l_confirmation_mode is NULL then
1201 if l_dunning_type = 'STAGED_DUNNING' then
1202 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - calling Send_Staged_Dunning');
1203 IEX_DUNNING_PVT.Send_Staged_Dunning(p_api_version => p_api_version
1204 ,p_init_msg_list => p_init_msg_list
1205 ,p_commit => p_commit
1206 ,p_dunning_plan_id => p_dunning_plan_id
1207 ,p_correspondence_date => p_correspondence_date
1208 ,p_delinquencies_tbl => l_delinquencies_tbl
1209 ,p_parent_request_id => p_parent_request_id
1210 ,p_dunning_mode => p_dunning_mode
1211 ,p_single_staged_letter => p_single_staged_letter -- added by gnramasa for bug stageddunning 28-Dec-09
1212 ,p_confirmation_mode => l_confirmation_mode
1213 ,x_return_status => l_return_status
1214 ,x_msg_count => l_msg_count
1215 ,x_msg_data => l_msg_data);
1216 else
1217 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - calling Send_Dunning');
1218 IEX_DUNNING_PVT.Send_Dunning(p_api_version => p_api_version
1219 ,p_init_msg_list => p_init_msg_list
1220 ,p_commit => p_commit
1221 ,p_dunning_plan_id => p_dunning_plan_id
1222 ,p_delinquencies_tbl => l_delinquencies_tbl
1223 ,p_parent_request_id => p_parent_request_id
1224 ,p_dunning_mode => p_dunning_mode
1225 ,p_confirmation_mode => l_confirmation_mode
1226 , p_correspondence_date => p_correspondence_date
1227 ,x_return_status => l_return_status
1228 ,x_msg_count => l_msg_count
1229 ,x_msg_data => l_msg_data);
1230
1231 end if;
1232 --end if; --if l_confirmation_mode is NULL then
1233 end if; --if p_running_level <> 'DELINQUENCY'
1234 end if; --if (p_parent_request_id is NULL OR p_process_err_rec_only = 'Y')
1235
1236 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' checking whether to span the IEX: Bulk xml manager or not');
1237 if (p_dunning_mode = 'FINAL' and l_confirmation_mode is not null) then
1238
1239 vPLSQL3 := 'select dun.dunning_id, dun.dunning_object_id, dun.dunning_level, dun.ag_dn_xref_id ' ||
1240 'from iex_dunnings dun ' ||
1241 'where dun.request_id = :1 ' ||
1242 ' and dun.delivery_status IS NULL ' ||
1243 ' and dun.confirmation_mode is null ';
1244 if l_custom_select IS NOT NULL then
1245 --Start for bug 9818696 gnramasa 16th June 10
1246 --vPLSQL3 := vPLSQL3 || ' and exists ( ' || l_custom_select ||' = dun.object_id) ';
1247 vPLSQL3 := vPLSQL3 || ' and exists ( ' || l_custom_select ||' = dun.dunning_object_id) ';
1248 end if;
1249
1250 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL3 ' || vPLSQL3);
1251 open sql_cur3 for vPLSQL3 using p_parent_request_id;
1252
1253 LOOP
1254 fetch sql_cur3 into l_dunning_id, l_dunning_object_id, l_dunning_level, l_ag_dn_xref_id;
1255 exit when sql_cur3%NOTFOUND;
1256 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_dunning_id :' || l_dunning_id);
1257 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_dunning_object_id :' || l_dunning_object_id);
1258 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_dunning_level :' || l_dunning_level);
1259 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_ag_dn_xref_id :' || l_ag_dn_xref_id);
1260
1261 --for c_dunn_req in c_dunning_ids(p_parent_request_id) loop
1262 -- l_dunning_rec_upd.dunning_id := c_dunn_req.dunning_id;
1263 l_dunning_rec_upd.dunning_id := l_dunning_id;
1264
1265 if l_confirmation_mode = 'CONFIRM' then
1266 l_dunning_rec_upd.confirmation_mode := 'CONFIRMED';
1267 if (l_dunning_level = 'CUSTOMER') then
1268 l_delinquencies_Tbl(1).party_cust_id := l_dunning_object_id;
1269 elsif (l_dunning_level = 'ACCOUNT') THEN
1270 l_delinquencies_Tbl(1).cust_account_id := l_dunning_object_id;
1271 elsif (l_dunning_level = 'BILL_TO') THEN
1272 l_delinquencies_Tbl(1).customer_site_use_id := l_dunning_object_id;
1273 else
1274 l_delinquencies_Tbl(1).delinquency_id := l_dunning_object_id;
1275 end if;
1276
1277 if l_dunning_type = 'STAGED_DUNNING' then
1278 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - calling iex_dunning_pvt.Close_Staged_Dunning');
1279 iex_dunning_pvt.Close_Staged_Dunning(
1280 p_api_version => p_api_version
1281 , p_init_msg_list => p_init_msg_list
1282 , p_commit => p_commit
1283 , p_delinquencies_tbl => l_delinquencies_Tbl
1284 , p_ag_dn_xref_id => l_ag_dn_xref_id
1285 , p_running_level => p_running_level
1286 --, p_status => l_status
1287 , x_return_status => l_return_status
1288 , x_msg_count => x_msg_count
1289 , x_msg_data => x_msg_data);
1290 else
1291 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - CloseDunning');
1292
1293 iex_dunning_pvt.Close_DUNNING(
1294 p_api_version => p_api_version
1295 , p_init_msg_list => p_init_msg_list
1296 , p_commit => p_commit
1297 , p_delinquencies_tbl => l_delinquencies_Tbl
1298 --, p_dunning_id => l_dunning_id --c_dunn_req.dunning_id
1299 --, p_status => 'CLOSE'
1300 , p_running_level => p_running_level
1301 , x_return_status => l_return_status
1302 , x_msg_count => x_msg_count
1303 , x_msg_data => x_msg_data);
1304 end if;
1305
1306 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - CloseDunning status='|| l_return_status);
1307
1308 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1309 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Cannot Close Dunning');
1310 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cannot Close Dunning');
1311 l_return_status := FND_API.G_RET_STS_ERROR;
1312 END IF;
1313
1314 --elsif l_confirmation_mode = 'REJECT' then
1315 -- l_dunning_rec_upd.confirmation_mode := 'REJECTED';
1316 -- l_dunning_rec_upd.status := 'CLOSE';
1317 end if;
1318
1319 l_dunning_rec_upd.status := 'OPEN';
1320
1321 IEX_DUNNING_PVT.Update_DUNNING(
1322 p_api_version => 1.0
1323 , p_init_msg_list => FND_API.G_FALSE
1324 , p_commit => FND_API.G_TRUE
1325 , p_dunning_rec => l_dunning_rec_upd
1326 , x_return_status => l_return_status
1327 , x_msg_count => l_msg_count
1328 , x_msg_data => l_msg_data
1329 );
1330 end loop;
1331 close sql_cur3;
1332
1333 vPLSQL4 := 'select xml.xml_request_id ' ||
1334 'from iex_dunnings dun, iex_xml_request_histories xml ' ||
1335 'where xml.conc_request_id = :1 ' ||
1336 ' and xml.xml_request_id = dun.xml_request_id ' ||
1337 ' and xml.confirmation_mode is null ';
1338 if l_custom_select IS NOT NULL then
1339 --vPLSQL4 := vPLSQL4 || ' and exists ( ' || l_custom_select ||' = dun.object_id) ';
1340 vPLSQL4 := vPLSQL4 || ' and exists ( ' || l_custom_select ||' = dun.dunning_object_id) ';
1341 end if;
1342
1343
1344 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL4 ' || vPLSQL4);
1345 open sql_cur4 for vPLSQL4 using p_parent_request_id;
1346
1347 LOOP
1348 fetch sql_cur4 into l_xml_request_id;
1349 exit when sql_cur4%NOTFOUND;
1350 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_xml_request_id :' || l_xml_request_id);
1351 --l_atleast_one_rec := TRUE;
1352
1353 --for c_xml_req in c_xml_request_ids(p_parent_request_id) loop
1354 -- l_xml_request_id := c_xml_req.xml_request_id;
1355 if l_confirmation_mode = 'CONFIRM' then
1356 l_conf_mode := 'CONFIRMED';
1357 --elsif l_confirmation_mode = 'REJECT' then
1358 -- l_conf_mode := 'REJECTED';
1359 -- l_status := 'CANCELLED';
1360 end if;
1361
1362 IEX_XML_PKG.update_row(
1363 p_xml_request_id => l_xml_request_id
1364 , p_status => l_status
1365 , p_confirmation_mode => l_conf_mode
1366 );
1367 end loop;
1368 close sql_cur4;
1369
1370 if l_dunning_type = 'STAGED_DUNNING' then
1371 vPLSQL6 := 'select xml.xml_request_id ' ||
1372 'from iex_dunnings dun, iex_xml_request_histories xml ' ||
1373 'where xml.conc_request_id = :1 ' ||
1374 ' and xml.xml_request_id = dun.xml_request_id ' ||
1375 ' and xml.confirmation_mode is null ' ||
1376 ' and dun.object_type = ''INX_INVOICES'' ';
1377 if l_custom_select IS NOT NULL then
1378 --vPLSQL6 := vPLSQL6 || ' and exists ( ' || l_custom_select ||' = dun.object_id) ';
1379 vPLSQL6 := vPLSQL6 || ' and exists ( ' || l_custom_select ||' = dun.dunning_object_id) ';
1380 end if;
1381
1382
1383 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL6 ' || vPLSQL6);
1384 open sql_cur6 for vPLSQL6 using p_parent_request_id;
1385
1386 LOOP
1387 fetch sql_cur6 into l_xml_request_id;
1388 exit when sql_cur6%NOTFOUND;
1389 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_xml_request_id :' || l_xml_request_id);
1390 if l_confirmation_mode = 'CONFIRM' then
1391 l_conf_mode := 'CONFIRMED';
1392 end if;
1393
1394 IEX_XML_PKG.update_row(
1395 p_xml_request_id => l_xml_request_id
1396 , p_status => l_status
1397 , p_confirmation_mode => l_conf_mode
1398 );
1399 end loop;
1400 close sql_cur6;
1401
1402 end if; --if l_dunning_type = 'STAGED_DUNNING' then
1403
1404 end if; --if (p_dunning_mode = 'FINAL' and l_confirmation_mode is not null) then
1405
1406 if p_dunning_mode = 'FINAL' and l_dunning_type = 'STAGED_DUNNING' then
1407 vPLSQL5 := 'select iet.payment_schedule_id, iet.stage_number ' ||
1408 'from iex_dunnings dun, iex_dunning_transactions iet ' ||
1409 'where dun.request_id = :1 ' ||
1410 ' and dun.dunning_id = iet.dunning_id ' ||
1411 ' and dun.delivery_status is null ' ||
1412 ' and iet.cust_trx_id is not null ' ||
1413 ' and iet.stage_number is not null ' ||
1414 ' and dun.object_type <> ''INX_INVOICES'' ';
1415 if l_custom_select IS NOT NULL then
1416 --vPLSQL5 := vPLSQL5 || ' and exists ( ' || l_custom_select ||' = dun.object_id) ';
1417 vPLSQL5 := vPLSQL5 || ' and exists ( ' || l_custom_select ||' = dun.dunning_object_id) ';
1418 --End for bug 9818696 gnramasa 16th June 10
1419 end if;
1420 vPLSQL5 := vPLSQL5 || ' order by iet.dunning_trx_id ';
1421
1422
1423 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - vPLSQL5 ' || vPLSQL5);
1424 open sql_cur5 for vPLSQL5 using l_req_id;
1425
1426 LOOP
1427 fetch sql_cur5 into l_payment_schedule_id, l_stage_number;
1428 exit when sql_cur5%NOTFOUND;
1429 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_payment_schedule_id :' || l_payment_schedule_id);
1430 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_stage_number :' || l_stage_number);
1431
1432 update iex_delinquencies_all
1433 set staged_dunning_level = l_stage_number
1434 where payment_schedule_id = l_payment_schedule_id;
1435 end loop;
1436 close sql_cur5;
1437 commit;
1438 end if;
1439
1440 --if (p_dunning_mode = 'DRAFT') or (p_dunning_mode = 'FINAL' and l_confirmation_mode ='CONFIRM')
1441 -- or (p_dunning_mode='FINAL' and l_confirmation_mode is NULL) then
1442
1443 open c_no_success_dunn_rec(l_req_id);
1444 fetch c_no_success_dunn_rec into l_no_final_successful_rec;
1445 close c_no_success_dunn_rec;
1446 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_no_final_successful_rec : '|| l_no_final_successful_rec);
1447
1448 if ((l_req_mode = 'DRAFT') and (p_parent_request_id IS NOT NULL) and (p_dunning_mode = 'FINAL') and (p_process_err_rec_only <> 'Y')) then
1449 WriteLog(G_PKG_NAME || ' ' || l_api_name || 'in DRAFT records delivery mode');
1450 l_diff_bw_init_fi_su_rec := l_no_init_successful_rec;
1451 else
1452 l_diff_bw_init_fi_su_rec := l_no_final_successful_rec - l_no_init_successful_rec;
1453 end if;
1454 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_diff_bw_init_fi_su_rec : '|| l_diff_bw_init_fi_su_rec);
1455
1456 l_no_of_workers := nvl(p_no_of_workers,1);
1457 --if not (p_dunning_mode = 'FINAL' and l_confirmation_mode is not null) then
1458
1459 if ( l_diff_bw_init_fi_su_rec > 0) then
1460
1461 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Atleast one dunning record processed successfully. So spawning the IEX: XML bulk manager for delivery');
1462 FND_FILE.put_line( FND_FILE.LOG,'Atleast one dunning record processed successfully. So spawning the IEX: XML bulk manager for delivery');
1463
1464 open c_object_type (l_req_id);
1465 fetch c_object_type into l_object_type;
1466 close c_object_type;
1467
1468 --span bulk xml delivery
1469 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
1470 APPLICATION => 'IEX',
1471 PROGRAM => 'IEX_BULK_XML_DELIVERY',
1472 DESCRIPTION => 'Oracle Collections Delivery XML Process',
1473 START_TIME => sysdate,
1474 SUB_REQUEST => false,
1475 ARGUMENT1 => l_no_of_workers,
1476 ARGUMENT2 => null,
1477 ARGUMENT3 => null,
1478 ARGUMENT4 => l_req_id,
1479 ARGUMENT5 => p_dunning_mode,
1480 ARGUMENT6 => null
1481
1482 --commented for bug 9970624 gnramasa 4th Aug 10
1483 ,ARGUMENT7 => ''
1484 ,ARGUMENT8 => 'N'
1485 ,ARGUMENT9 => p_order_output_by);
1486
1487 WriteLog(G_PKG_NAME || ' ' || l_api_name || 'Submitted the Bulk XML delivery, l_submit_request_id='||l_submit_request_id);
1488 else
1489 WriteLog(G_PKG_NAME || ' ' || l_api_name || 'Not even one dunning record processed successfully. So no need to spawn the IEX: XML bulk manager for delivery');
1490 FND_FILE.put_line( FND_FILE.LOG,'');
1491 FND_FILE.put_line( FND_FILE.LOG,'*******************************************************************************************************************************');
1492 FND_FILE.put_line( FND_FILE.LOG,'* WARNING: Not even one dunning record processed successfully. So no need to spawn the IEX: XML bulk manager for delivery *');
1493 FND_FILE.put_line( FND_FILE.LOG,'*******************************************************************************************************************************');
1494 FND_FILE.put_line( FND_FILE.LOG,'');
1495 end if;
1496
1497 --end if;
1498 --end if;
1499
1500 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' status='||l_return_status);
1501
1502 --Start bug 9970624 gnramasa 4th Aug 10
1503 --Span only single bulk xml delivery cp to send dunning output and invoice copy
1504 /*
1505 if l_dunning_type = 'STAGED_DUNNING' then
1506 if p_process_err_rec_only = 'Y' then
1507 open c_get_inv_count_in_errmode (l_req_id, l_max_dunning_trx_id);
1508 fetch c_get_inv_count_in_errmode into l_dunn_invoice_ct;
1509 close c_get_inv_count_in_errmode;
1510 else
1511 open c_get_invoice_ct (l_req_id) ;
1512 fetch c_get_invoice_ct into l_dunn_invoice_ct;
1513 close c_get_invoice_ct;
1514 end if;
1515
1516 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Dunning invoices count = '||l_dunn_invoice_ct);
1517
1518 IF (l_dunn_invoice_ct is null or l_dunn_invoice_ct = 0) then
1519 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - No stage dunning invoice');
1520 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No stage dunning invoice');
1521 goto end_loop;
1522 ELSE
1523 -- Now, COLLECTIONS STAGE DUNNING INVOICE is not null;
1524
1525 FND_FILE.PUT_LINE(FND_FILE.LOG, 'COLLECTIONS STAGE DUNNING INVOICE='||l_dunn_invoice_ct);
1526 IF (p_parent_request_id is NULL OR p_process_err_rec_only = 'Y') THEN
1527 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start calling IEX_DUNNING_PVT.stage_dunning_inv_copy');
1528 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start stage_dunning_inv_copy');
1529
1530 IEX_DUNNING_PVT.stage_dunning_inv_copy(
1531 p_api_version => 1.0
1532 , p_init_msg_list => FND_API.G_FALSE
1533 , p_commit => FND_API.G_TRUE
1534 , p_no_of_workers => l_no_of_workers
1535 , p_process_err_rec_only => p_process_err_rec_only
1536 , p_request_id => l_req_id
1537 , p_dunning_mode => p_dunning_mode
1538 , p_confirmation_mode => l_confirmation_mode
1539 , p_running_level => p_running_level
1540 , p_correspondence_date => p_correspondence_date
1541 , p_max_dunning_trx_id => l_max_dunning_trx_id
1542 , x_return_status => l_return_status
1543 , x_msg_count => l_msg_count
1544 , x_msg_data => l_msg_data
1545 );
1546
1547 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Return status of IEX_DUNNING_PVT.stage_dunning_inv_copy='||l_return_status);
1548 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Return status of IEX_DUNNING_PVT.stage_dunning_inv_copy='||l_return_status);
1549
1550 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1551 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Cannot create invoice copy');
1552 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cannot create invoice copy');
1553 l_return_status := FND_API.G_RET_STS_ERROR;
1554 END IF;
1555 END IF;
1556 END IF; --IF (l_dunn_invoice_ct is null or l_dunn_invoice_ct = 0) then
1557
1558 open c_no_success_inv_rec(l_req_id);
1559 fetch c_no_success_inv_rec into l_no_final_successful_inv_rec;
1560 close c_no_success_inv_rec;
1561 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_no_final_successful_inv_rec : '|| l_no_final_successful_inv_rec);
1562
1563 if ((l_req_mode = 'DRAFT') and (p_parent_request_id IS NOT NULL) and (p_dunning_mode = 'FINAL') and (p_process_err_rec_only <> 'Y')) then
1564 WriteLog(G_PKG_NAME || ' ' || l_api_name || 'in DRAFT records Invoice delivery mode');
1565 l_diff_bw_init_fi_su_inv_rec := l_no_init_successful_inv_rec;
1566 else
1567 l_diff_bw_init_fi_su_inv_rec := l_no_final_successful_inv_rec - l_no_init_successful_inv_rec;
1568 end if;
1569 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_diff_bw_init_fi_su_inv_rec : '|| l_diff_bw_init_fi_su_inv_rec);
1570
1571 if ( l_diff_bw_init_fi_su_inv_rec > 0) then
1572 WriteLog(G_PKG_NAME || ' ' || l_api_name || 'Atleast one invoice is satisfied to send invoice copy. So spawning the IEX: XML bulk manager for delivery');
1573 FND_FILE.put_line( FND_FILE.LOG,'Atleast one invoice is satisfied to send invoice copy. So spawning the IEX: XML bulk manager for delivery');
1574
1575 l_no_of_workers := nvl(p_no_of_workers,1);
1576 --span bulk xml delivery
1577 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
1578 APPLICATION => 'IEX',
1579 PROGRAM => 'IEX_BULK_XML_DELIVERY',
1580 DESCRIPTION => 'Oracle Collections Staged Dunning Invoice Copy',
1581 START_TIME => sysdate,
1582 SUB_REQUEST => false,
1583 ARGUMENT1 => l_no_of_workers,
1584 ARGUMENT2 => null,
1585 ARGUMENT3 => null,
1586 ARGUMENT4 => l_req_id,
1587 ARGUMENT5 => p_dunning_mode,
1588 ARGUMENT6 => null,
1589 ARGUMENT7 => 'IEX_INVOICES');
1590
1591 WriteLog(G_PKG_NAME || ' ' || l_api_name || 'Submitted the Bulk XML delivery, l_submit_request_id='||l_submit_request_id);
1592 end if;
1593
1594 end if; --if l_dunning_type = 'STAGED_DUNNING' then
1595 */
1596 --End bug 9970624 gnramasa 4th Aug 10
1597
1598 --*/
1599 -- Start bug 5924158 05/06/07 by gnramasa
1600 /* x_return_status := l_return_status;
1601 IF l_return_status = FND_API.G_RET_STS_ERROR then
1602 raise FND_API.G_EXC_ERROR;
1603 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1604 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1605 END IF;
1606 */
1607 -- End bug 5924158 05/06/07 by gnramasa
1608 --
1609 -- End of API body
1610 --
1611
1612 <<end_loop>>
1613 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - commit');
1614 -- Standard check for p_commit
1615 IF FND_API.to_Boolean( p_commit )
1616 THEN
1617 COMMIT WORK;
1618 END IF;
1619
1620
1621 if p_process_err_rec_only = 'Y' then
1622 open c_no_err_dunn_rec(p_parent_request_id);
1623 fetch c_no_err_dunn_rec into l_no_of_fail_rec_bylastrun;
1624 close c_no_err_dunn_rec;
1625 l_no_of_succ_rec_bylastrun := (l_no_of_rec_prc_bylastrun - l_no_of_fail_rec_bylastrun);
1626 end if;
1627
1628 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - before alling iex_dunning_pvt.gen_xml_data_dunning');
1629
1630 if l_con_proc_mode in ('FINALDRAFT','DRAFTDRAFT') then
1631 l_process_err_rec_only := 'N';
1632 else
1633 l_process_err_rec_only := p_process_err_rec_only;
1634 end if;
1635
1636 iex_dunning_pvt.gen_xml_data_dunning( p_request_id => l_req_id,
1637 p_running_level => p_running_level,
1638 p_dunning_plan_id => p_dunning_plan_id,
1639 p_dunning_mode => p_dunning_mode,
1640 p_confirmation_mode => l_confirmation_mode,
1641 p_process_err_rec_only => l_process_err_rec_only,
1642 p_no_of_rec_prc_bylastrun => l_no_of_rec_prc_bylastrun,
1643 p_no_of_succ_rec_bylastrun => l_no_of_succ_rec_bylastrun,
1644 p_no_of_fail_rec_bylastrun => l_no_of_fail_rec_bylastrun,
1645 p_org_id_coll_method => null, -- Added by snuthala for bug 10221334 21/10/2010
1646 x_no_of_rec_prc => l_no_of_rec_prc,
1647 x_no_of_succ_rec => l_no_of_succ_rec,
1648 x_no_of_fail_rec => l_no_of_fail_rec
1649 );
1650
1651 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - After alling iex_dunning_pvt.gen_xml_data_dunning');
1652 --End adding for bug 8489610 by gnramasa 14-May-09
1653
1654 --Set the iex: send dunning cp to WARNING status if at least one dunning record is failed to process
1655 open c_no_err_dunn_rec (l_req_id);
1656 fetch c_no_err_dunn_rec into l_no_err_dunn_rec;
1657 close c_no_err_dunn_rec;
1658
1659 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_no_err_dunn_rec: '||l_no_err_dunn_rec);
1660
1661 if l_update_cp_as_err then
1662 if l_con_proc_mode = 'FINALDRAFT' then
1663 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - You can''t run this request id: ' || p_parent_request_id || ' in PREVIEW mode, because request has been created in direct FINAL mode.');
1664 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Run this in FINAL mode with process errors Y to process the errored records.');
1665 FND_FILE.put_line( FND_FILE.LOG,'');
1666 FND_FILE.put_line( FND_FILE.LOG,'***************************************************************************************************************************');
1667 FND_FILE.PUT_LINE( FND_FILE.LOG,'* ERROR: You can''t run this request id: ' || p_parent_request_id || ' in PREVIEW mode, because request has been created in direct FINAL mode. *');
1668 FND_FILE.PUT_LINE( FND_FILE.LOG,'* Run this in FINAL mode with process errors Y to process the errored records. *');
1669 FND_FILE.put_line( FND_FILE.LOG,'***************************************************************************************************************************');
1670 FND_FILE.put_line( FND_FILE.LOG,'');
1671 l_err_message := 'ERROR: You can''t run this request id: ' || p_parent_request_id || ' in PREVIEW mode, because request has been created in direct FINAL mode. Run this in FINAL mode with process errors Y to process the errored records.';
1672 elsif l_con_proc_mode = 'DRAFTDRAFT' then
1673 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - You can''t run this request id: ' || p_parent_request_id || ' in PREVIEW mode, as this request has been already submitted in FINAL mode.');
1674 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Run this in FINAL mode with process errors Y to process the errored records.');
1675 FND_FILE.put_line( FND_FILE.LOG,'');
1676 FND_FILE.put_line( FND_FILE.LOG,'*******************************************************************************************************************************');
1677 FND_FILE.PUT_LINE( FND_FILE.LOG,'* ERROR: You can''t run this request id: ' || p_parent_request_id || ' in PREVIEW mode, as this request has been already submitted in FINAL mode. *');
1678 FND_FILE.PUT_LINE( FND_FILE.LOG,'* Run this in FINAL mode with process errors Y to process the errored records. *');
1679 FND_FILE.put_line( FND_FILE.LOG,'*******************************************************************************************************************************');
1680 FND_FILE.put_line( FND_FILE.LOG,'');
1681 l_err_message := 'ERROR: You can''t run this request id: ' || p_parent_request_id || ' in PREVIEW mode, as this request has been already submitted in FINAL mode. Run this in FINAL mode with process errors Y to process the errored records.';
1682 end if;
1683 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'ERROR',
1684 message => l_err_message);
1685 goto proc_end;
1686 elsif l_no_err_dunn_rec > 0 then
1687 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Set the iex: send dunning cp to WARNING status as at least one dunning record is failed to process');
1688 FND_FILE.put_line( FND_FILE.LOG,'');
1689 FND_FILE.put_line( FND_FILE.LOG, '**********************************************************************************************************');
1690 FND_FILE.put_line( FND_FILE.LOG, '* WARNING: Concurrent program ended in WARNING, as at least one dunning record is failed to process *');
1691 FND_FILE.put_line( FND_FILE.LOG, '**********************************************************************************************************');
1692 FND_FILE.put_line( FND_FILE.LOG,'');
1693 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
1694 message => 'At least one dunning record is failed to process');
1695 end if;
1696
1697 FND_FILE.put_line( FND_FILE.LOG,'');
1698 FND_FILE.put_line( FND_FILE.LOG, '*************************************************************************');
1699 FND_FILE.put_line( FND_FILE.LOG, '* Number of record(s) processed : ' || l_no_of_rec_prc || ' *');
1700 FND_FILE.put_line( FND_FILE.LOG, '* Number of successful record(s) : ' || l_no_of_succ_rec || ' *');
1701 FND_FILE.put_line( FND_FILE.LOG, '* Number of failed record(s) : ' || l_no_of_fail_rec || ' *');
1702 FND_FILE.put_line( FND_FILE.LOG, '*************************************************************************');
1703 FND_FILE.put_line( FND_FILE.LOG,'');
1704
1705 if p_process_err_rec_only = 'Y' then
1706 FND_FILE.put_line( FND_FILE.LOG,'');
1707 FND_FILE.put_line( FND_FILE.LOG, '*************************************************************************');
1708 FND_FILE.put_line( FND_FILE.LOG, '* In Error mode: *');
1709 FND_FILE.put_line( FND_FILE.LOG, '* -------------- *');
1710 FND_FILE.put_line( FND_FILE.LOG, '* Number of errored record(s) processed by this run : ' || l_no_of_rec_prc_bylastrun || ' *');
1711 FND_FILE.put_line( FND_FILE.LOG, '* Number of successful record(s) by this run : ' || l_no_of_succ_rec_bylastrun || ' *');
1712 FND_FILE.put_line( FND_FILE.LOG, '* Number of failed record(s) by this run : ' || l_no_of_fail_rec_bylastrun || ' *');
1713 FND_FILE.put_line( FND_FILE.LOG, '*************************************************************************');
1714 FND_FILE.put_line( FND_FILE.LOG,'');
1715 end if;
1716
1717 <<proc_end>>
1718 -- Debug Message
1719 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - return status='||x_return_status);
1720 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - END');
1721
1722 FND_MSG_PUB.Count_And_Get
1723 ( p_count => x_msg_count,
1724 p_data => x_msg_data );
1725
1726 EXCEPTION
1727 WHEN FND_API.G_EXC_ERROR THEN
1728 x_return_status := FND_API.G_RET_STS_ERROR;
1729 ROLLBACK TO Send_DUNNING_PUB;
1730 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1731 FND_MSG_PUB.Count_And_Get
1732 ( p_count => x_msg_count,
1733 p_data => x_msg_data );
1734
1735 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1736 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1737 ROLLBACK TO Send_DUNNING_PUB;
1738 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1739 FND_MSG_PUB.Count_And_Get
1740 ( p_count => x_msg_count,
1741 p_data => x_msg_data );
1742
1743 WHEN OTHERS THEN
1744 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1745 ROLLBACK TO Send_DUNNING_PUB;
1746 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1747 FND_MSG_PUB.Count_And_Get
1748 ( p_count => x_msg_count,
1749 p_data => x_msg_data );
1750
1751 END Send_Dunning;
1752
1753 Procedure Daily_Dunning
1754 (p_api_version IN NUMBER := 1.0,
1755 p_init_msg_list IN VARCHAR2 ,
1756 p_commit IN VARCHAR2 ,
1757 --p_dunning_tbl IN IEX_DUNNING_PUB.DUNNING_TBL_TYPE,
1758 p_running_level IN VARCHAR2 ,
1759 x_return_status OUT NOCOPY VARCHAR2,
1760 x_msg_count OUT NOCOPY NUMBER,
1761 x_msg_data OUT NOCOPY VARCHAR2)
1762 IS
1763 l_api_name CONSTANT VARCHAR2(30) := 'Daily_Dunning';
1764 l_api_version_number CONSTANT NUMBER := 1.0;
1765 l_return_status VARCHAR2(1);
1766 l_msg_count NUMBER;
1767 l_msg_data VARCHAR2(32767);
1768 errmsg VARCHAR2(32767);
1769
1770 BEGIN
1771 -- Standard Start of API savepoint
1772 SAVEPOINT DAILY_DUNNING_PUB;
1773
1774 -- Initialize message list if p_init_msg_list is set to TRUE.
1775 IF FND_API.to_Boolean( p_init_msg_list )
1776 THEN
1777 FND_MSG_PUB.initialize;
1778 END IF;
1779
1780 -- Debug Message
1781 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Start');
1782 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - running_level='||p_running_level);
1783
1784 -- Initialize API return status to SUCCESS
1785 x_return_status := FND_API.G_RET_STS_SUCCESS;
1786
1787 --
1788 -- Api body
1789 --
1790
1791 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Calling PVT');
1792
1793 IEX_DUNNING_PVT.Daily_Dunning(
1794 p_api_version => p_api_version
1795 , p_init_msg_list => p_init_msg_list
1796 , p_commit => p_commit
1797 , p_running_level => p_running_level
1798 --, p_dunning_tbl => p_dunning_tbl
1799 , x_return_status => x_return_status
1800 , x_msg_count => x_msg_count
1801 , x_msg_data => x_msg_data
1802 );
1803
1804 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - return status='||x_return_status);
1805
1806 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1807 IF x_return_status = FND_API.G_RET_STS_ERROR then
1808 raise FND_API.G_EXC_ERROR;
1809 else
1810 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1811 END IF;
1812 END IF;
1813
1814 --
1815 -- End of API body
1816 --
1817
1818 -- Standard check for p_commit
1819 IF FND_API.to_Boolean( p_commit )
1820 THEN
1821 COMMIT WORK;
1822 END IF;
1823
1824 -- Debug Message
1825 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - end ');
1826
1827 FND_MSG_PUB.Count_And_Get
1828 ( p_count => x_msg_count,
1829 p_data => x_msg_data );
1830
1831 EXCEPTION
1832 WHEN FND_API.G_EXC_ERROR THEN
1833 x_return_status := FND_API.G_RET_STS_ERROR;
1834 ROLLBACK TO DAILY_DUNNING_PUB;
1835 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1836 FND_MSG_PUB.Count_And_Get
1837 ( p_count => x_msg_count,
1838 p_data => x_msg_data );
1839
1840 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1842 ROLLBACK TO DAILY_DUNNING_PUB;
1843 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1844 FND_MSG_PUB.Count_And_Get
1845 ( p_count => x_msg_count,
1846 p_data => x_msg_data );
1847
1848 WHEN OTHERS THEN
1849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1850 ROLLBACK TO DAILY_DUNNING_PUB;
1851 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - error='||SQLERRM);
1852 FND_MSG_PUB.Count_And_Get
1853 ( p_count => x_msg_count,
1854 p_data => x_msg_data );
1855
1856 END Daily_Dunning;
1857
1858
1859
1860 PROCEDURE CALLBACK_CONCUR(
1861 ERRBUF OUT NOCOPY VARCHAR2,
1862 RETCODE OUT NOCOPY VARCHAR2,
1863 P_ORG_ID IN NUMBER DEFAULT NULL) --Added for MOAC
1864 is
1865 CURSOR C_GET_LEVEL IS
1866 SELECT preference_value
1867 FROM IEX_APP_PREFERENCES_VL
1868 WHERE upper(PREFERENCE_NAME) = 'COLLECTIONS DUNNING LEVEL';
1869 --
1870 l_api_version NUMBER := 1.0;
1871 l_msg_data VARCHAR2(4000) default NULL;
1872 l_msg_count NUMBER;
1873 l_default_rs_id number := 0;
1874 l_running_level VARCHAR2(20);
1875 l_error NUMBER := 0;
1876 errmsg VARCHAR2(4000) default NULL;
1877 l_api_name varchar2(25);
1878
1879 BEGIN
1880
1881 l_api_name := 'callback_concur';
1882 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Callback_Concur');
1883 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - starting CALLBACK_CONCUR');
1884
1885 --Start MOAC
1886 mo_global.init('IEX');
1887 IF p_org_id IS NULL THEN
1888 mo_global.set_policy_context('M',NULL);
1889 ELSE
1890 mo_global.set_policy_context('S',p_org_id);
1891 END IF;
1892 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Operating Unit: '|| nvl(mo_global.get_ou_name(mo_global.get_current_org_id), 'All'));
1893 --End MOAC
1894
1895 l_default_rs_id := fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE');
1896 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Default Resource Id = '||l_default_rs_id);
1897 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - default_rs_id='||l_default_rs_id);
1898
1899 if (l_default_rs_id = 0 or l_default_rs_id is null) then
1900 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - CALLBACK_CONCUR:no rs_id');
1901 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE');
1902 FND_MESSAGE.Set_Token ('PROFILE', 'IEX_STRY_DEFAULT_RESOURCE', FALSE);
1903 FND_MSG_PUB.Add;
1904
1905 FND_MSG_PUB.Count_And_Get
1906 ( p_count => l_msg_count,
1907 p_data => l_msg_data
1908 );
1909
1910 FND_FILE.PUT_LINE(FND_FILE.LOG, 'no default resource_id');
1911 errbuf := l_msg_data;
1912 retcode := '2'; --FND_API.G_RET_STS_ERROR;
1913 --retcode := FND_API.G_RET_STS_ERROR;
1914 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
1915
1916 for i in 1..l_msg_count loop
1917 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
1918 p_encoded => 'F');
1919 FND_FILE.PUT_LINE(FND_FILE.LOG, errmsg);
1920 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
1921 end loop;
1922
1923 --
1924 else
1925 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - rs_id='||l_default_rs_id);
1926 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Daily_Dunning');
1927
1928 OPEN C_Get_Level ;
1929 FETCH C_Get_Level INTO l_running_level;
1930
1931 IF (C_Get_Level%NOTFOUND)
1932 THEN
1933 l_error := 1;
1934 END IF;
1935 CLOSE C_GET_LEVEL;
1936
1937 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - running level='||l_running_level);
1938 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Dunning Running Level = '||l_running_level);
1939 IF (l_running_level is null or l_error = 1) then
1940 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - no running level');
1941 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Dunning Running Level');
1942 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_VALUE');
1943 FND_MESSAGE.Set_Token('COLUMN', 'COLLECTIONS DUNNING LEVEL', FALSE);
1944 FND_MSG_PUB.Add;
1945
1946 FND_MSG_PUB.Count_And_Get
1947 ( p_count => l_msg_count,
1948 p_data => l_msg_data
1949 );
1950
1951 errbuf := l_msg_data;
1952 retcode := '2'; --FND_API.G_RET_STS_ERROR;
1953 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
1954
1955 for i in 1..l_msg_count loop
1956 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
1957 p_encoded => 'F');
1958 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
1959 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calback_Concur errmsg =' ||errmsg);
1960 end loop;
1961 --
1962 else
1963 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>Process Dunning Callbacks');
1964 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - starting Daily_Dunning');
1965 IEX_DUNNING_PUB.Daily_Dunning(
1966 p_api_version => l_api_version
1967 , p_init_msg_list => FND_API.G_TRUE
1968 , p_commit => FND_API.G_TRUE
1969 , p_running_level => l_running_level
1970 , x_return_status => RETCODE
1971 , x_msg_count => l_msg_count
1972 , x_msg_data => ERRBUF
1973 );
1974
1975 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Daily_Dunning status='||retcode);
1976 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>End of Process Dunning Callbacks');
1977 end if;
1978 --
1979 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - ProcessPromiseCallbacks');
1980 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>Process Promise Callbacks');
1981
1982 IEX_PROMISES_BATCH_PUB.PROCESS_PROMISE_CALLBACKS(
1983 p_api_version => l_api_version
1984 , p_init_msg_list => FND_API.G_TRUE
1985 , p_commit => FND_API.G_TRUE
1986 , x_return_status => RETCODE
1987 , x_msg_count => ERRBUF
1988 , x_msg_data => l_msg_data);
1989
1990 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - ProcessPromiseCallbacks status='||retcode);
1991 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End ProcessPromiseCallbacks');
1992 FND_FILE.PUT_LINE(FND_FILE.LOG, '>>>>>End of Process Promise Callbacks');
1993
1994 end if;
1995
1996 EXCEPTION
1997 WHEN OTHERS THEN
1998 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Exception');
1999 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Callback_Concur Exception');
2000 errbuf := SQLERRM;
2001 retcode := '2';
2002 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
2003 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Callback_Concur errbuf:'||errbuf);
2004
2005 END CALLBACK_CONCUR;
2006
2007
2008
2009 PROCEDURE SEND_DUNNING_CONCUR(
2010 ERRBUF OUT NOCOPY VARCHAR2,
2011 RETCODE OUT NOCOPY VARCHAR2,
2012 DUNNING_PLAN_ID IN NUMBER,
2013 p_staged_dunning_dummy IN VARCHAR2,
2014 p_correspondence_date IN VARCHAR2,
2015 p_parent_request_id IN NUMBER,
2016 p_dunning_mode IN VARCHAR2 DEFAULT 'FINAL', -- added by gnramasa for bug 8489610 14-May-09
2017 p_process_err_dummy IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
2018 p_process_err_rec_only IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
2019 p_no_of_workers IN number := 1, -- added by gnramasa for bug 8489610 14-May-09
2020 p_single_staged_letter IN VARCHAR2 DEFAULT 'N', -- added by gnramasa for bug stageddunning 28-Dec-09
2021 p_coll_bus_level_dummy IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2022 p_customer_name_low IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2023 p_customer_name_high IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2024 --p_account_number_dummy IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2025 p_account_number_low IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2026 p_account_number_high IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2027 p_billto_location_dummy IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2028 p_billto_location_low IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2029 p_billto_location_high IN VARCHAR2, -- added by gnramasa for bug 8489610 28-May-09
2030 p_order_output_by IN VARCHAR2) -- added by snuthala for bug 14541439 06-Feb-13
2031 is
2032 CURSOR C_GET_LEVEL (iex_dunning_plan_id number) IS
2033 select dunning_level from iex_dunning_plans_vl
2034 where dunning_plan_id = iex_dunning_plan_id;
2035 -- 12.0 ctlee, get it from the dunning_plan
2036 -- SELECT preference_value
2037 -- FROM IEX_APP_PREFERENCES_VL
2038 -- WHERE upper(PREFERENCE_NAME) = 'COLLECTIONS DUNNING LEVEL';
2039 --
2040 CURSOR C_GET_BUCKET (iex_dunning_plan_id number) IS
2041 select aging_bucket_id from iex_dunning_plans_vl
2042 where dunning_plan_id = iex_dunning_plan_id;
2043 -- 12.0 ctlee, get it from the dunning_plan
2044 -- SELECT preference_value
2045 -- FROM IEX_APP_PREFERENCES_VL
2046 --WHERE upper(PREFERENCE_NAME) = 'COLLECTIONS AGING BUCKET';
2047 -- WHERE upper(PREFERENCE_NAME) = 'DUNNING PLAN AGING BUCKET';
2048 --
2049 l_api_version NUMBER := 1.0;
2050 l_msg_count NUMBER ;
2051 l_msg_data VARCHAR2(4000) default NULL;
2052 l_running_level VARCHAR2(20);
2053 l_bucket VARCHAR2(100);
2054 l_error NUMBER := 0;
2055 errmsg VARCHAR2(4000) default NULL;
2056 l_api_name varchar2(25);
2057 l_correspondence_date date;
2058 l_dunning_type varchar2(20);
2059 --start added by snuthala for bug 10221334 21/10/2010
2060 l_con_update_re_st boolean;
2061
2062
2063 cursor c_dunning_type (p_dunn_plan_id number) is
2064 select nvl(dunning_type,'DAYS_OVERDUE')
2065 from IEX_DUNNING_PLANS_B
2066 where dunning_plan_id = p_dunn_plan_id;
2067
2068
2069
2070 l_define_ou_running_level varchar2(1);
2071 l_collections_method varchar2(30);
2072 l_org_id_coll_method varchar2(30);
2073 l_org_id number;
2074 l_req_id number;
2075 l_confirmation_mode varchar2(30);
2076 l_req_mode varchar2(15);
2077 l_num_zero number :=0;
2078 l_business_level VARCHAR2(20);
2079
2080 cursor c_req_dunn_mode (p_req_id number) is
2081 select dunning_mode
2082 from iex_dunnings
2083 where request_id = p_req_id;
2084
2085 cursor c_ou_running_level is
2086 select define_ou_running_level
2087 from iex_questionnaire_items;
2088
2089 cursor c_collections_method is
2090 select collections_methods
2091 from iex_questionnaire_items;
2092
2093 cursor c_business_level is
2094 select business_level
2095 from iex_questionnaire_items;
2096
2097 cursor c_org_id_coll_method(p_org_id number) is
2098 select collections_method
2099 from iex_app_preferences_b where
2100 org_id = p_org_id
2101 and enabled_flag ='Y';
2102
2103 --end added by snuthala for bug 10221334 21/10/2010
2104 l_cust_prof_chk varchar2(30); -- added for bug 15933013
2105 BEGIN
2106
2107 l_api_name := 'SEND_DUNNING_CONCUR';
2108
2109 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SEND_DUNNING_CONCUR dunning_plan_id = ' || dunning_plan_id);
2110 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CORRESPONDENCE DATE : ' || p_correspondence_date);
2111 FND_FILE.PUT_LINE(FND_FILE.LOG, 'PARENT REQUEST ID : ' || p_parent_request_id);
2112 --Start adding for bug 8489610 by gnramasa 14-May-09
2113 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DUNNING MODE : ' || p_dunning_mode);
2114 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SINGLE STAGED LETTER : ' || p_single_staged_letter);
2115 FND_FILE.PUT_LINE(FND_FILE.LOG, 'PROCESS ERRORED RECORD ONLY : ' || p_process_err_rec_only);
2116 FND_FILE.PUT_LINE(FND_FILE.LOG, 'NUMBER OF WORKERS : ' || p_no_of_workers);
2117 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CUSTOMER NAME LOW : ' || p_customer_name_low);
2118 FND_FILE.PUT_LINE(FND_FILE.LOG, 'CUSTOMER NAME HIGH : ' || p_customer_name_high);
2119 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ACCOUNT NUMBER LOW : ' || p_account_number_low);
2120 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ACCOUNT NUMBER HIGH : ' || p_account_number_high);
2121 FND_FILE.PUT_LINE(FND_FILE.LOG, 'BILLTO LOCATION LOW : ' || p_billto_location_low);
2122 FND_FILE.PUT_LINE(FND_FILE.LOG, 'BILLTO LOCATION HIGH : ' || p_billto_location_high);
2123 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Order Output Letters By : ' || p_order_output_by);
2124 WriteLog('iexpdunb:starting SEND_DUNNING_CONCUR; dunning_plan_id : ' || dunning_plan_id);
2125 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - start');
2126 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - correspondence date :' || p_correspondence_date);
2127 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - parent request id :' || p_parent_request_id);
2128 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - dunning mode :' || p_dunning_mode);
2129 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - single staged letter :' || p_single_staged_letter);
2130 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - process errored rec only :' || p_process_err_rec_only);
2131 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_no_of_workers :' || p_no_of_workers);
2132 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_customer_name_low :' || p_customer_name_low);
2133 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_customer_name_high :' || p_customer_name_high);
2134 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_account_number_low :' || p_account_number_low);
2135 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_account_number_high :' || p_account_number_high);
2136 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_billto_location_low :' || p_billto_location_low);
2137 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - p_billto_location_high :' || p_billto_location_high);
2138 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Order Output Letters By :' || p_order_output_by);
2139 --End adding for bug 8489610 by gnramasa 14-May-09
2140
2141 --Start MOAC
2142 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org Id:'|| mo_global.get_current_org_id);
2143 --End MOAC
2144 -- start added by snuthala for bug 10221334 21/10/2010
2145 OPEN C_Get_Level (dunning_plan_id);
2146 FETCH C_Get_Level INTO l_running_level;
2147 close C_Get_Level;
2148 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_running_level : '|| l_running_level);
2149
2150 OPEN c_business_level ;
2151 FETCH c_business_level INTO l_business_level;
2152 close c_business_level;
2153 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_business_level : '|| l_business_level);
2154
2155 OPEN c_ou_running_level;
2156 FETCH c_ou_running_level INTO l_define_ou_running_level;
2157 close c_ou_running_level;
2158
2159 FND_FILE.PUT_LINE(FND_FILE.LOG, 'define_ou_running_level : '|| l_define_ou_running_level);
2160
2161 if l_running_level <> l_business_level then
2162 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Dunning Plan level is '||l_running_level ||' does not match the current business level '||l_business_level);
2163 l_org_id_coll_method := 'MISMATCH'; -- This is to end send dunning cp from running if Dunning Plan level does not match the current business level
2164 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Concurrent program failed to run as Dunning Plan level is '||l_running_level ||' does not match the current business level '||l_business_level);
2165 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2166
2167 message => 'Concurrent program failed to run as Dunning Plan level is '||l_running_level ||' does not match the current business level '||l_business_level);
2168 iex_dunning_pvt.gen_xml_data_dunning(p_request_id => l_req_id,
2169 p_running_level => l_running_level,
2170 p_dunning_plan_id => DUNNING_PLAN_ID,
2171 p_dunning_mode => p_dunning_mode,
2172 p_confirmation_mode => l_confirmation_mode,
2173 p_process_err_rec_only => p_process_err_rec_only,
2174 p_no_of_rec_prc_bylastrun => l_num_zero,
2175 p_no_of_succ_rec_bylastrun => l_num_zero,
2176 p_no_of_fail_rec_bylastrun => l_num_zero,
2177 p_org_id_coll_method => l_org_id_coll_method,
2178 x_no_of_rec_prc => l_num_zero,
2179 x_no_of_succ_rec => l_num_zero,
2180 x_no_of_fail_rec => l_num_zero
2181 );
2182 return;
2183 end if;
2184 OPEN c_collections_method;
2185 FETCH c_collections_method INTO l_collections_method;
2186 close c_collections_method;
2187
2188 FND_FILE.PUT_LINE(FND_FILE.LOG, 'collections_methods : '|| l_collections_method);
2189 l_org_id := mo_global.get_current_org_id;
2190
2191 if l_collections_method = 'STRATEGIES' then
2192 l_org_id_coll_method := 'ERROR'; -- This is to end send dunning cp from running if collections method is strategies
2193 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Concurrent program failed to run as collections method is set up as strategies');
2194 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2195
2196 message => 'Concurrent program failed to run as collections method is set up as strategies');
2197
2198 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Deciding the request id ');
2199 if p_parent_request_id is not null then
2200 l_req_id := p_parent_request_id;
2201 else
2202 l_req_id := FND_GLOBAL.Conc_Request_Id;
2203 end if;
2204 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_req_id: '||l_req_id);
2205
2206
2207 open c_req_dunn_mode(l_req_id);
2208 fetch c_req_dunn_mode into l_req_mode;
2209 close c_req_dunn_mode;
2210
2211 if (l_req_mode = 'DRAFT') and (p_parent_request_id IS NOT NULL) and (p_dunning_mode = 'FINAL') then
2212 l_confirmation_mode := 'CONFIRM';
2213 else
2214 l_confirmation_mode := NULL;
2215 end if;
2216
2217
2218
2219 iex_dunning_pvt.gen_xml_data_dunning(p_request_id => l_req_id,
2220 p_running_level => l_running_level,
2221 p_dunning_plan_id => DUNNING_PLAN_ID,
2222 p_dunning_mode => p_dunning_mode,
2223 p_confirmation_mode => l_confirmation_mode,
2224 p_process_err_rec_only => p_process_err_rec_only,
2225 p_no_of_rec_prc_bylastrun => l_num_zero,
2226 p_no_of_succ_rec_bylastrun => l_num_zero,
2227 p_no_of_fail_rec_bylastrun => l_num_zero,
2228 p_org_id_coll_method => l_org_id_coll_method,
2229 x_no_of_rec_prc => l_num_zero,
2230 x_no_of_succ_rec => l_num_zero,
2231 x_no_of_fail_rec => l_num_zero
2232 );
2233 return;
2234 elsif l_define_ou_running_level = 'Y' and l_collections_method = 'DUN_STR' then
2235 OPEN C_org_id_coll_method (l_org_id);
2236 FETCH C_org_id_coll_method INTO l_org_id_coll_method;
2237 if (C_org_id_coll_method%NOTFOUND) then
2238 WriteLog(G_PKG_NAME || ' ' || l_api_name || 'C_org_id_coll_method%NOTFOUND ');
2239 l_org_id_coll_method := 'Unregistered';
2240 FND_FILE.PUT_LINE(FND_FILE.LOG, 'This opearting unit is not registered.Please check setup');
2241 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2242
2243 message => 'This opearting unit is not registered.Please check setup');
2244 end if;
2245 close C_org_id_coll_method;
2246
2247 if l_org_id_coll_method <> 'DUNNING' then
2248 FND_FILE.PUT_LINE(FND_FILE.LOG, 'This opearting unit is not setup for dunning.Please check setup');
2249 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2250
2251 message => 'Opearting Unit is Not Setup for Dunning please check Setup');
2252
2253 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Deciding the request id ');
2254 if p_parent_request_id is not null then
2255 l_req_id := p_parent_request_id;
2256 else
2257 l_req_id := FND_GLOBAL.Conc_Request_Id;
2258 end if;
2259 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_req_id: '||l_req_id);
2260
2261
2262 open c_req_dunn_mode(l_req_id);
2263 fetch c_req_dunn_mode into l_req_mode;
2264 close c_req_dunn_mode;
2265
2266 if (l_req_mode = 'DRAFT') and (p_parent_request_id IS NOT NULL) and (p_dunning_mode = 'FINAL') then
2267 l_confirmation_mode := 'CONFIRM';
2268 else
2269 l_confirmation_mode := NULL;
2270 end if;
2271
2272
2273
2274 iex_dunning_pvt.gen_xml_data_dunning(p_request_id => l_req_id,
2275 p_running_level => l_running_level,
2276 p_dunning_plan_id => DUNNING_PLAN_ID,
2277 p_dunning_mode => p_dunning_mode,
2278 p_confirmation_mode => l_confirmation_mode,
2279 p_process_err_rec_only => p_process_err_rec_only,
2280 p_no_of_rec_prc_bylastrun => l_num_zero,
2281 p_no_of_succ_rec_bylastrun => l_num_zero,
2282 p_no_of_fail_rec_bylastrun => l_num_zero,
2283 p_org_id_coll_method => l_org_id_coll_method,
2284 x_no_of_rec_prc => l_num_zero,
2285 x_no_of_succ_rec => l_num_zero,
2286 x_no_of_fail_rec => l_num_zero
2287 );
2288 return;
2289 end if;
2290 end if;
2291 -- End added by snuthala for bug 10221334 21/10/2010
2292
2293 retcode := FND_API.G_RET_STS_SUCCESS;
2294 --
2295 open c_dunning_type (dunning_plan_id);
2296 fetch c_dunning_type into l_dunning_type;
2297 close c_dunning_type;
2298 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_dunning_type ' || l_dunning_type);
2299
2300 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'chk bucket');
2301 OPEN C_Get_Level (dunning_plan_id);
2302 FETCH C_Get_Level INTO l_running_level;
2303
2304 IF (C_Get_Level%NOTFOUND)
2305 THEN
2306 l_error := 1;
2307 END IF;
2308 CLOSE C_GET_LEVEL;
2309
2310 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'running level='||l_running_level);
2311 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - running level='||l_running_level);
2312 --start bug 15933013
2313 l_cust_prof_chk := fnd_profile.value('IEX_DUNNING_CUST_PROF_CHECK');
2314 if l_cust_prof_chk is NULL then
2315 if l_running_level = 'DELINQUENCY' then
2316 l_cust_prof_chk := 'BILL_TO';
2317 else
2318 l_cust_prof_chk := l_running_level;
2319 end if;
2320 end if;
2321
2322 if (l_dunning_type= 'DAYS_OVERDUE' and l_running_level='ACCOUNT')
2323 and (l_cust_prof_chk = 'BILL_TO' or l_cust_prof_chk = 'BILLTOACCOUNT') then
2324 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Profile - IEX: Minimum Dunning Amount Profile Check= '||l_cust_prof_chk);
2325 FND_FILE.PUT_LINE(FND_FILE.LOG, ' When Dunning level is Account, value of profile "IEX: Minimum Dunning Amount Profile Check" can''t be "Bill To" or "Bill To and Account". ');
2326 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'ERROR',
2327 message => ' When Dunning level is Account, value of profile "IEX: Minimum Dunning Amount Profile Check" can''t be "Bill To" or "Bill To and Account". ');
2328 retcode := FND_API.G_RET_STS_ERROR;
2329 Return;
2330 end if;
2331 --end bug 15933013
2332 if (l_running_level is null or l_error = 1) then
2333 -- IF PG_DEBUG < 10 THEN
2334 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2335 iex_debug_pub.LogMessage('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - no running level');
2336 END IF;
2337 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_VALUE');
2338 FND_MESSAGE.Set_Token('COLUMN', 'COLLECTIONS DUNNING LEVEL', FALSE);
2339 FND_MSG_PUB.Add;
2340
2341 FND_MSG_PUB.Count_And_Get
2342 ( p_count => l_msg_count,
2343 p_data => l_msg_data
2344 );
2345
2346 FND_FILE.PUT_LINE(FND_FILE.LOG, 'no running level');
2347 errbuf := l_msg_data;
2348 retcode := '2';
2349 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
2350
2351 for i in 1..l_msg_count loop
2352 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
2353 p_encoded => 'F');
2354 FND_FILE.PUT_LINE(FND_FILE.LOG, errmsg);
2355 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
2356 end loop;
2357 --
2358 else
2359
2360 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'running level='||l_running_level);
2361 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'chk bucket');
2362 OPEN C_Get_Bucket (dunning_plan_id) ;
2363 FETCH C_Get_Bucket INTO l_bucket;
2364
2365 IF (C_Get_Bucket%NOTFOUND) THEN
2366 l_error := 1;
2367 END IF;
2368 CLOSE C_GET_Bucket;
2369
2370 --FND_FILE.PUT_LINE(FND_FILE.LOG, 'Aging Bucket='||l_bucket);
2371 WriteLog('Send_Dunning: ' || G_PKG_NAME || ' ' || l_api_name || ' - bucket='||l_bucket);
2372
2373 IF (l_bucket is null or l_error = 1) and (l_dunning_type <> 'STAGED_DUNNING') then
2374 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - no bucket');
2375 FND_MESSAGE.Set_Name('IEX', 'IEX_NO_VALUE');
2376 --FND_MESSAGE.Set_Token('COLUMN', 'COLLECTIONS AGING BUCKET', FALSE);
2377 FND_MESSAGE.Set_Token('COLUMN', 'DUNNING PLAN AGING BUCKET', FALSE);
2378 FND_MSG_PUB.Add;
2379
2380 FND_MSG_PUB.Count_And_Get
2381 ( p_count => l_msg_count,
2382 p_data => l_msg_data
2383 );
2384
2385 FND_FILE.PUT_LINE(FND_FILE.LOG, 'no bucket');
2386 errbuf := l_msg_data;
2387 retcode := '2';
2388 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
2389
2390 for i in 1..l_msg_count loop
2391 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
2392 p_encoded => 'F');
2393 FND_FILE.PUT_LINE(FND_FILE.LOG, errmsg);
2394 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
2395 end loop;
2396
2397 --
2398 ELSE
2399 -- Now, RunningLevel and Bucket are not null;
2400
2401 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Dunning Running Level='||l_running_level);
2402 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Dunning Aging Bucket='||l_bucket);
2403 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Send_Dunning');
2404 -- IF PG_DEBUG < 10 THEN
2405 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2406 iex_debug_pub.LogMessage(G_PKG_NAME || ' ' || l_api_name || ' - runninglevel='||l_running_level);
2407 iex_debug_pub.LogMessage(G_PKG_NAME || ' ' || l_api_name || ' - bucket='||l_bucket);
2408 iex_debug_pub.LogMessage(G_PKG_NAME || ' ' || l_api_name || ' - starting Send_Dunning');
2409 END IF;
2410 --l_correspondence_date := nvl(p_correspondence_date, trunc(sysdate));
2411 l_correspondence_date := trunc(nvl(to_date(substr(p_correspondence_date, 1, 10), 'YYYY/MM/DD'),to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD')));
2412
2413 IEX_DUNNING_PUB.SEND_DUNNING(
2414 p_api_version => l_api_version
2415 , p_init_msg_list => FND_API.G_TRUE
2416 , p_commit => FND_API.G_TRUE
2417 , p_running_level => l_running_level
2418 , p_parent_request_id => p_parent_request_id
2419 , p_dunning_plan_id => dunning_plan_id
2420 , p_correspondence_date => l_correspondence_date
2421 , p_dunning_mode => p_dunning_mode -- added by gnramasa for bug 8489610 14-May-09
2422 , p_process_err_rec_only => nvl(p_process_err_rec_only, 'N')
2423 , p_no_of_workers => p_no_of_workers -- added by gnramasa for bug 8489610 14-May-09
2424 , p_single_staged_letter => nvl(p_single_staged_letter,'N') -- added by gnramasa for bug stageddunning 28-Dec-09
2425 , p_customer_name_low => p_customer_name_low
2426 , p_customer_name_high => p_customer_name_high
2427 , p_account_number_low => p_account_number_low
2428 , p_account_number_high => p_account_number_high
2429 , p_billto_location_low => p_billto_location_low
2430 , p_billto_location_high => p_billto_location_high
2431 , p_order_output_by => p_order_output_by
2432 , x_return_status => RETCODE
2433 , x_msg_count => l_msg_count
2434 , x_msg_data => ERRBUF
2435 );
2436
2437 FND_FILE.PUT_LINE(FND_FILE.LOG, 'return_status='||retcode);
2438 -- IF PG_DEBUG < 10 THEN
2439 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2440 iex_debug_pub.LogMessage(G_PKG_NAME || ' ' || l_api_name || ' - status='||retcode);
2441 END IF;
2442
2443 IF (retcode <> FND_API.G_RET_STS_SUCCESS) THEN
2444 FND_MSG_PUB.Count_And_Get
2445 ( p_count => l_msg_count,
2446 p_data => l_msg_data
2447 );
2448
2449 errbuf := l_msg_data;
2450 retcode := '2';
2451 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||errbuf);
2452
2453 for i in 1..l_msg_count loop
2454 errmsg := FND_MSG_PUB.Get(p_msg_index => i,
2455 p_encoded => 'F');
2456 FND_FILE.PUT_LINE(FND_FILE.LOG, errmsg);
2457 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errmsg='||errmsg);
2458 end loop;
2459 END IF;
2460
2461 END IF; -- END OF CHK Bucket
2462
2463 End if; -- END of Chk RunningLevel
2464
2465 EXCEPTION
2466 WHEN OTHERS THEN
2467 retcode := '2'; --FND_API.G_RET_STS_UNEXP_ERROR;
2468 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||SQLERRM);
2469 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
2470
2471 END SEND_DUNNING_CONCUR;
2472
2473 --Start for bug 9582646 gnramasa 5th May 10
2474 PROCEDURE STG_DUNNING_MIG_CONCUR(
2475 ERRBUF OUT NOCOPY VARCHAR2,
2476 RETCODE OUT NOCOPY VARCHAR2,
2477 p_migration_mode IN VARCHAR2 DEFAULT 'FINAL')
2478 is
2479
2480 l_api_version NUMBER := 1.0;
2481 l_msg_count NUMBER ;
2482 l_msg_data VARCHAR2(4000) default NULL;
2483 l_running_level VARCHAR2(20);
2484 l_bucket VARCHAR2(100);
2485 l_error NUMBER := 0;
2486 errmsg VARCHAR2(4000) default NULL;
2487 l_api_name varchar2(25);
2488 l_dunning_plan_id number;
2489 l_ag_dn_xref_id number;
2490 l_include_current varchar2(3) := 'N';
2491 l_template_id number;
2492 TYPE number_list is TABLE of NUMBER INDEX BY BINARY_INTEGER;
2493 l_staged_dunning_level number_list;
2494 l_payment_schedule_id number_list;
2495 l_return BOOLEAN;
2496 l_dunning_letter_set_id number;
2497 l_migrated_data varchar2(10);
2498 l_business_level varchar2(20);
2499 l_score_id number;
2500 l_output_string varchar2(1000);
2501 l_no_updated_rows number := 0;
2502 l_migration_mode varchar2(20);
2503 l_rec_exists varchar2(1) := 'N';
2504 l_con_update_re_st boolean;
2505
2506 cursor c_dunning_letter_sets is
2507 select dunning_letter_set_id,
2508 name,
2509 description,
2510 status,
2511 grace_days,
2512 dun_disputed_items,
2513 include_unused_payments_flag,
2514 dunning_type,
2515 creation_date,
2516 created_by,
2517 last_update_date,
2518 last_updated_by,
2519 last_update_login
2520 from ar_dunning_letter_sets
2521 where dunning_type = 'STAGED_DUNNING';
2522
2523 CURSOR c_fnd_languages IS
2524 SELECT language_code
2525 FROM fnd_languages
2526 WHERE installed_flag IN ('B','I');
2527
2528 cursor c_migrated_dunn_letter_sets is
2529 select dnb.dunning_plan_id dunning_plan_id,
2530 dnb.dunning_level dunning_level,
2531 dntl.name name,
2532 dntl.description description,
2533 dnb.mig_dunning_letter_set_id mig_dunning_letter_set_id
2534 from iex_dunning_plans_b dnb,
2535 iex_dunning_plans_tl dntl
2536 where dnb.dunning_plan_id = dntl.dunning_plan_id
2537 and dntl.language = userenv('LANG')
2538 and dnb.dunning_type = 'STAGED_DUNNING'
2539 and dnb.mig_dunning_letter_set_id is not null
2540 and dnb.enabled_flag = 'Y'
2541 and dnb.end_date is null
2542 order by dnb.dunning_plan_id;
2543
2544 cursor c_dunning_letter_set_lines (p_dunn_letter_set_id number) is
2545 select dunning_letter_set_id,
2546 dunning_line_num,
2547 last_update_date,
2548 last_updated_by,
2549 last_update_login,
2550 created_by,
2551 creation_date,
2552 dunning_letter_id,
2553 include_current,
2554 invoice_copies,
2555 range_of_dunning_level_from,
2556 range_of_dunning_level_to,
2557 min_days_between_dunning
2558 from AR_DUNNING_LETTER_SET_LINES
2559 where dunning_letter_set_id = p_dunn_letter_set_id;
2560
2561 cursor c_template_id is
2562 select template_id
2563 from xdo_templates_vl
2564 where template_code = 'IEXSTGDN';
2565
2566 cursor c_staged_dunning_level is
2567 select ar.staged_dunning_level,
2568 ar.payment_schedule_id
2569 from iex_delinquencies_all iex,
2570 ar_payment_schedules_all ar
2571 where iex.payment_schedule_id = ar.payment_schedule_id
2572 and ar.staged_dunning_level is not null
2573 and iex.staged_dunning_level is null
2574 and iex.status in ('DELINQUENT','PREDELINQUENT')
2575 and ar.status = 'OP';
2576
2577 cursor c_business_level is
2578 select business_level
2579 from iex_questionnaire_items
2580 where questionnaire_item_id = 1;
2581
2582 BEGIN
2583
2584 l_api_name := 'STG_DUNNING_MIG_CONCUR';
2585 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - start');
2586 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SEND_DUNNING_CONCUR p_migration_mode = ' || p_migration_mode);
2587 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' p_migration_mode : ' || p_migration_mode);
2588 l_migration_mode := nvl(p_migration_mode,'FINAL');
2589
2590 l_migrated_data := nvl(fnd_profile.value('AR_DUNNING_TO_IEXDUNNING_MIGRATED'), 'Y');
2591
2592 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SEND_DUNNING_CONCUR l_migrated_data = ' || l_migrated_data);
2593
2594 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_migrated_data : ' || l_migrated_data);
2595
2596 open c_business_level;
2597 fetch c_business_level into l_business_level;
2598 close c_business_level;
2599
2600 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SEND_DUNNING_CONCUR l_business_level = ' || l_business_level);
2601
2602 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' l_business_level : ' || l_business_level);
2603
2604 if l_business_level = 'CUSTOMER' then
2605 l_score_id := 1;
2606 elsif l_business_level = 'ACCOUNT' then
2607 l_score_id := 6;
2608 elsif l_business_level = 'BILL_TO' then
2609 l_score_id := 7;
2610 elsif l_business_level = 'DELINQUENCY' then
2611 l_score_id := 2;
2612 end if;
2613
2614 retcode := FND_API.G_RET_STS_SUCCESS;
2615
2616 open c_template_id;
2617 fetch c_template_id into l_template_id;
2618 close c_template_id;
2619
2620 if l_migration_mode = 'FINAL' then
2621 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - CP submitted in FINAL mode. So records will be migrated.');
2622 if l_migrated_data = 'N' then
2623
2624 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2625 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2626 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Profile ''IEX: AR Dunning to IEX Dunning Migrated?'' value is No. So data will get migrated. *');
2627 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2628
2629 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - End dating the previously migrated dunning letter sets.');
2630 UPDATE IEX_DUNNING_PLANS_B
2631 SET LAST_UPDATE_DATE = sysdate,
2632 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2633 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID,
2634 END_DATE = nvl(END_DATE,sysdate),
2635 ENABLED_FLAG = 'N'
2636 WHERE MIG_DUNNING_LETTER_SET_ID is not null
2637 AND (ENABLED_FLAG = 'Y' OR END_DATE IS NULL);
2638
2639 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2640 FND_FILE.PUT_LINE(FND_FILE.LOG, '1. End dated the previously migrated dunning letter sets.');
2641
2642 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2643 FND_FILE.PUT_LINE(FND_FILE.LOG, '2. Following Dunning letter sets are migrated as Dunning plans from AR to Advanced Collections');
2644 begin
2645
2646 for dunn_letter_set_rec in c_dunning_letter_sets
2647 loop
2648 l_rec_exists := 'Y'; --at least one record exists
2649 l_dunning_letter_set_id := dunn_letter_set_rec.dunning_letter_set_id;
2650 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_dunning_letter_set_id= ' || l_dunning_letter_set_id);
2651 --Start bug 9725204 gnramasa 19th May 10
2652 --l_dunning_plan_id := IEX_DUNNING_PLANS_S.nextval;
2653 select IEX_DUNNING_PLANS_S.nextval
2654 into l_dunning_plan_id
2655 from dual;
2656 --End bug 9725204 gnramasa 19th May 10
2657 l_include_current := 'N';
2658 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_dunning_plan_id= ' || l_dunning_plan_id);
2659
2660 INSERT INTO IEX_DUNNING_PLANS_B
2661 (DUNNING_PLAN_ID,
2662 START_DATE,
2663 ENABLED_FLAG,
2664 AGING_BUCKET_ID,
2665 SCORE_ID,
2666 DUNNING_LEVEL,
2667 OBJECT_VERSION_NUMBER,
2668 CREATION_DATE,
2669 CREATED_BY,
2670 LAST_UPDATE_DATE,
2671 LAST_UPDATED_BY,
2672 LAST_UPDATE_LOGIN,
2673 DUNNING_TYPE,
2674 DUN_DISPUTED_ITEMS,
2675 GRACE_DAYS,
2676 INCLUDE_UNUSED_PAYMENTS_FLAG,
2677 DUNNING_LETTER_SET_ID,
2678 MIG_DUNNING_LETTER_SET_ID)
2679 VALUES
2680 (l_dunning_plan_id,
2681 sysdate,
2682 decode(dunn_letter_set_rec.status,'A','Y','N'),
2683 null,
2684 l_score_id,
2685 l_business_level,
2686 1,
2687 dunn_letter_set_rec.creation_date,
2688 dunn_letter_set_rec.created_by,
2689 dunn_letter_set_rec.last_update_date,
2690 dunn_letter_set_rec.last_updated_by,
2691 dunn_letter_set_rec.last_update_login,
2692 dunn_letter_set_rec.dunning_type,
2693 dunn_letter_set_rec.dun_disputed_items,
2694 dunn_letter_set_rec.grace_days,
2695 dunn_letter_set_rec.include_unused_payments_flag,
2696 dunn_letter_set_rec.dunning_letter_set_id,
2697 dunn_letter_set_rec.dunning_letter_set_id);
2698 FOR fnd_languages IN c_fnd_languages loop
2699 INSERT INTO IEX_DUNNING_PLANS_TL
2700 (DUNNING_PLAN_ID,
2701 NAME,
2702 DESCRIPTION,
2703 LANGUAGE,
2704 SOURCE_LANG,
2705 CREATED_BY,
2706 CREATION_DATE,
2707 LAST_UPDATE_DATE,
2708 LAST_UPDATED_BY,
2709 LAST_UPDATE_LOGIN)
2710 VALUES
2711 (l_dunning_plan_id,
2712 dunn_letter_set_rec.name,
2713 dunn_letter_set_rec.description,
2714 fnd_languages.language_code, --'US',
2715 'US',
2716 dunn_letter_set_rec.created_by,
2717 dunn_letter_set_rec.creation_date,
2718 dunn_letter_set_rec.last_update_date,
2719 dunn_letter_set_rec.last_updated_by,
2720 dunn_letter_set_rec.last_update_login);
2721 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Language code ' || fnd_languages.language_code);
2722 END LOOP;
2723 l_output_string := ' Dunning_plan_id : ' || rpad(l_dunning_plan_id, 20, ' ') || ' Name: ' || rpad(dunn_letter_set_rec.name, 30, ' ') || ' Description: ' || rpad(dunn_letter_set_rec.description, 50, ' ');
2724 FND_FILE.PUT_LINE(FND_FILE.LOG, l_output_string);
2725
2726 for dunn_letter_set_lines_rec in c_dunning_letter_set_lines (l_dunning_letter_set_id)
2727 loop
2728 --Start bug 9725204 gnramasa 19th May 10
2729 --l_ag_dn_xref_id := IEX_AG_DN_XREF_S.nextval;
2730 select IEX_AG_DN_XREF_S.nextval
2731 into l_ag_dn_xref_id
2732 from dual;
2733 --End bug 9725204 gnramasa 19th May 10
2734 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - l_ag_dn_xref_id= ' || l_ag_dn_xref_id);
2735
2736 INSERT INTO IEX_AG_DN_XREF
2737 (AG_DN_XREF_ID,
2738 LAST_UPDATE_DATE,
2739 LAST_UPDATED_BY,
2740 LAST_UPDATE_LOGIN,
2741 CREATED_BY,
2742 CREATION_DATE,
2743 OBJECT_VERSION_NUMBER,
2744 CALLBACK_FLAG,
2745 CALLBACK_DAYS,
2746 FM_METHOD,
2747 SCORE_RANGE_LOW,
2748 SCORE_RANGE_HIGH,
2749 TEMPLATE_ID,
2750 DUNNING_LEVEL,
2751 XDO_TEMPLATE_ID,
2752 DUNNING_PLAN_ID,
2753 INVOICE_COPIES,
2754 MIN_DAYS_BETWEEN_DUNNING,
2755 RANGE_OF_DUNNING_LEVEL_FROM,
2756 RANGE_OF_DUNNING_LEVEL_TO)
2757 VALUES
2758 (l_ag_dn_xref_id,
2759 dunn_letter_set_lines_rec.last_update_date,
2760 dunn_letter_set_lines_rec.last_updated_by,
2761 dunn_letter_set_lines_rec.last_update_login,
2762 dunn_letter_set_lines_rec.created_by,
2763 dunn_letter_set_lines_rec.creation_date,
2764 1,
2765 'N',
2766 null,
2767 'PRINTER',
2768 1,
2769 100,
2770 l_template_id,
2771 l_business_level,
2772 l_template_id,
2773 l_dunning_plan_id,
2774 dunn_letter_set_lines_rec.invoice_copies,
2775 dunn_letter_set_lines_rec.min_days_between_dunning,
2776 dunn_letter_set_lines_rec.range_of_dunning_level_from,
2777 dunn_letter_set_lines_rec.range_of_dunning_level_to);
2778
2779 if (dunn_letter_set_lines_rec.include_current = 'Y' and l_include_current <> 'Y') then
2780 l_include_current := 'Y';
2781 end if;
2782 end loop;
2783
2784 UPDATE IEX_DUNNING_PLANS_B
2785 SET INCLUDE_CURRENT = l_include_current
2786 WHERE
2787 DUNNING_PLAN_ID = l_dunning_plan_id;
2788
2789 end loop;
2790
2791
2792 exception
2793 WHEN OTHERS THEN
2794 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||SQLERRM);
2795 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
2796 end ;
2797 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2798
2799 l_staged_dunning_level.delete;
2800 l_payment_schedule_id.delete;
2801 --Copy the Staged level of an Invoice from AR_PAYMENT_SCHEDULES_ALL table to IEX_DELINQUENCIES_ALL table.
2802 --It will update only the records that have staged_dunning_level as NULL
2803 BEGIN
2804 OPEN c_staged_dunning_level;
2805 LOOP
2806 FETCH c_staged_dunning_level BULK COLLECT INTO
2807 l_staged_dunning_level,
2808 l_payment_schedule_id
2809 LIMIT G_BATCH_SIZE;
2810 IF l_staged_dunning_level.count = 0 THEN
2811
2812 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - No of rows updated in iex_delinquencies_all is: ' || l_no_updated_rows);
2813 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Exit after Updating iex_delinquencies_all staged_dunning_level...');
2814 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2815 FND_FILE.PUT_LINE(FND_FILE.LOG, '3. Copied Transaction''s stage level from AR to Advanced Collections. No of rows updated in iex_delinquencies_all table is: ' || l_no_updated_rows);
2816 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2817 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Used the following SQL to copy the Transaction''s stage level from AR to Advanced Collections.');
2818 FND_FILE.PUT_LINE(FND_FILE.LOG, ' UPDATE IEX_DELINQUENCIES_ALL IEX ');
2819 FND_FILE.PUT_LINE(FND_FILE.LOG, ' SET STAGED_DUNNING_LEVEL = l_staged_dunning_level (I) ');
2820 FND_FILE.PUT_LINE(FND_FILE.LOG, ' WHERE PAYMENT_SCHEDULE_ID = l_payment_schedule_id(I); ');
2821 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2822
2823 CLOSE c_staged_dunning_level;
2824 EXIT;
2825 ELSE
2826 FORALL I IN l_staged_dunning_level.first..l_staged_dunning_level.last
2827 UPDATE IEX_DELINQUENCIES_ALL IEX
2828 SET STAGED_DUNNING_LEVEL = l_staged_dunning_level (I)
2829 WHERE PAYMENT_SCHEDULE_ID = l_payment_schedule_id(I);
2830
2831 l_no_updated_rows := l_no_updated_rows + l_staged_dunning_level.count;
2832
2833 l_staged_dunning_level.delete;
2834 l_payment_schedule_id.delete;
2835
2836 commit;
2837
2838 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' Rows updated in iex_delinquencies_all staged_dunning_level...');
2839 FND_FILE.PUT_LINE(FND_FILE.LOG, '3. Copied Transactions stage level from AR to Advanced Collections');
2840 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2841
2842 END IF;
2843 END LOOP;
2844 EXCEPTION
2845 WHEN OTHERS THEN
2846 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||SQLERRM);
2847 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
2848 END;
2849
2850 --Update dunning_type as 'DAYS_OVERDUE' for existing records in IEX_DUNNING_PLANS_B table.
2851 update iex_dunning_plans_b
2852 set dunning_type = 'DAYS_OVERDUE'
2853 where dunning_type is null
2854 and aging_bucket_id is not null;
2855 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Updated dunning_type as DAYS_OVERDUE for existing records in IEX_DUNNING_PLANS_B table.');
2856
2857 --Set the Profile 'IEX: IPP Printer Name' as 'NOPRINT'.
2858 l_return := fnd_profile.save(x_name => 'IEX_PRT_IPP_PRINTER_NAME',
2859 x_value => 'NOPRINT',
2860 x_level_name => 'SITE',
2861 x_level_value => null,
2862 x_level_value_app_id => '',
2863 x_level_value2 => null);
2864 if l_return then
2865 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Profile IEX: IPP Printer Name value updated with NOPRINT ');
2866 else
2867 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Profile IEX: IPP Printer Name updated failed ');
2868 end if;
2869
2870 --Set the Profile IEX: AR Dunning to IEX Dunning Migrated? as 'Y' i.e Yes.
2871 l_return := fnd_profile.save(x_name => 'AR_DUNNING_TO_IEXDUNNING_MIGRATED',
2872 x_value => 'Y',
2873 x_level_name => 'SITE',
2874 x_level_value => null,
2875 x_level_value_app_id => '',
2876 x_level_value2 => null);
2877 if l_return then
2878 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Profile IEX: AR Dunning to IEX Dunning Migrated? value updated with Y ');
2879 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2880 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Dunning letter sets are migrated successfully and assigned Profile ''IEX: AR Dunning to IEX Dunning Migrated?'' with value Yes. *');
2881 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2882 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2883 else
2884 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - Profile IEX: AR Dunning to IEX Dunning Migrated? updated failed ');
2885 end if;
2886
2887 else
2888 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2889 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2890 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Profile ''IEX: AR Dunning to IEX Dunning Migrated?'' value is Yes. So data will not get migrated. *');
2891 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2892 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2893 FND_FILE.PUT_LINE(FND_FILE.LOG, '1. Following Dunning letter sets are already migrated as Dunning plans from AR to Advanced Collections');
2894
2895 for v_c_migrated_dunn_letter_sets in c_migrated_dunn_letter_sets
2896 loop
2897 l_rec_exists := 'Y'; --at least one record exists
2898 l_output_string := ' Dunning_plan_id : ' || rpad(v_c_migrated_dunn_letter_sets.dunning_plan_id, 12, ' ')|| ' Business level: ' || rpad(v_c_migrated_dunn_letter_sets.dunning_level, 11, ' ');
2899 l_output_string := l_output_string || ' Name: ' || rpad(v_c_migrated_dunn_letter_sets.name, 30, ' ') || ' Description: ' || rpad(v_c_migrated_dunn_letter_sets.description, 50, ' ') ;
2900 l_output_string := l_output_string || ' Dunning_letter_set_id: ' || rpad(v_c_migrated_dunn_letter_sets.mig_dunning_letter_set_id, 20, ' ');
2901 FND_FILE.PUT_LINE(FND_FILE.LOG, l_output_string);
2902 end loop;
2903
2904 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2905 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2906 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Profile IEX: AR Dunning to IEX Dunning Migrated? value is Yes. So data will not get migrated. *');
2907 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Set Profile value as No at Site level and run the concurrent program again to re-migrate the dunning letter sets. *');
2908 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2909 end if;
2910 else --DRAFT mode
2911 if l_migrated_data = 'N' then
2912 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2913 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2914 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Profile ''IEX: AR Dunning to IEX Dunning Migrated?'' value is No. *');
2915 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2916 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2917 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2918 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Concurrent program has been submitted in DRAFT mode. So data will not get migrated. *');
2919 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Following dunning letter sets will get migrated by running the concurrent program in FINAL mode. *');
2920 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2921
2922 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2923 --FND_FILE.PUT_LINE(FND_FILE.LOG, '1. Following Dunning letter sets will be picked for data migration.');
2924
2925 for v_c_dunning_letter_sets in c_dunning_letter_sets
2926 loop
2927 l_rec_exists := 'Y'; --at least one record exists
2928 l_output_string := ' Dunning_letter_set_id: ' || rpad(v_c_dunning_letter_sets.dunning_letter_set_id, 12, ' ') || ' Name: ' || rpad(v_c_dunning_letter_sets.name, 30, ' ') ;
2929 l_output_string := l_output_string || ' Description: ' || rpad(v_c_dunning_letter_sets.description, 50, ' ');
2930 FND_FILE.PUT_LINE(FND_FILE.LOG, l_output_string);
2931 end loop;
2932 else
2933 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2934 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2935 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Profile ''IEX: AR Dunning to IEX Dunning Migrated?'' value is Yes. *');
2936 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2937 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2938 FND_FILE.PUT_LINE(FND_FILE.LOG, '1. Following Dunning letter sets are already migrated as Dunning plans from AR to Advanced Collections');
2939
2940 for v_c_migrated_dunn_letter_sets in c_migrated_dunn_letter_sets
2941 loop
2942 l_rec_exists := 'Y'; --at least one record exists
2943 l_output_string := ' Dunning_plan_id : ' || rpad(v_c_migrated_dunn_letter_sets.dunning_plan_id, 12, ' ')|| ' Business level: ' || rpad(v_c_migrated_dunn_letter_sets.dunning_level, 11, ' ') || ' Name: ';
2944 l_output_string := l_output_string || rpad(v_c_migrated_dunn_letter_sets.name, 30, ' ') || ' Description: ' || rpad(v_c_migrated_dunn_letter_sets.description, 50, ' ') || ' Dunning_letter_set_id: ' ;
2945 l_output_string := l_output_string || rpad(v_c_migrated_dunn_letter_sets.mig_dunning_letter_set_id, 20, ' ');
2946 FND_FILE.PUT_LINE(FND_FILE.LOG, l_output_string);
2947 end loop;
2948
2949 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2950 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2951 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Concurrent program has been submitted in DRAFT mode. *');
2952 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Profile IEX: AR Dunning to IEX Dunning Migrated? value is Yes. So data will not get migrated. *');
2953 FND_FILE.PUT_LINE(FND_FILE.LOG, '* Set Profile value as No at Site level and run the concurrent program again in DRAFT mode to see, what are the records will get migrated. *');
2954 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2955 end if;
2956 end if; --if l_migration_mode = 'FINAL' then
2957 if l_rec_exists = 'N' then
2958 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2959 FND_FILE.PUT_LINE(FND_FILE.LOG, '* NO RECORDS FOUND *');
2960 FND_FILE.PUT_LINE(FND_FILE.LOG, '*********************************************************************************************************************************************');
2961 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
2962 end if;
2963
2964 if l_migrated_data = 'Y' then
2965 l_con_update_re_st := fnd_concurrent.set_completion_status (status => 'WARNING',
2966 message => 'Set Profile IEX: AR Dunning to IEX Dunning Migrated? value to No and then run the cp.');
2967 end if;
2968
2969 EXCEPTION
2970 WHEN OTHERS THEN
2971 retcode := '2'; --FND_API.G_RET_STS_UNEXP_ERROR;
2972 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - errbuf='||SQLERRM);
2973 FND_FILE.PUT_LINE(FND_FILE.LOG, errbuf);
2974
2975 END STG_DUNNING_MIG_CONCUR;
2976 --End for bug 9582646 gnramasa 5th May 10
2977
2978 --clchang 10/28/04 added to fix the gscc warning
2979 BEGIN
2980
2981 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2982
2983
2984 END IEX_DUNNING_PUB;