[Home] [Help]
PACKAGE BODY: APPS.IEX_STRATEGY_WORK_PUB
Source
1 package body IEX_STRATEGY_WORK_PUB as
2 --$Header: iexpstmb.pls 120.33.12020000.11 2013/03/15 17:53:10 snuthala ship $
3 ----------- procedure check_work_items_completed ------------------------------
4 /**
5 * check to see if there are any pending
6 * work items to be processed
7 **/
8 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
9 PG_DEBUG NUMBER ;
10
11 --begin schekuri Bug#4506922 Date:02-Dec-2005
12 wf_yes varchar2(1) ;
13 wf_no varchar2(1) ;
14 --end schekuri Bug#4506922 Date:02-Dec-2005
15
16 procedure send_mail(
17 itemtype in varchar2,
18 itemkey in varchar2,
19 actid in number,
20 funcmode in varchar2,
21 result out NOCOPY varchar2)
22 IS
23 l_party_id number;
24 l_strategy_id number;
25 l_delinquency_id number;
26 l_party_type varchar2(80);
27 l_party_name varchar2(240);
28 l_first_name varchar2(80);
29 l_last_name varchar2(80);
30 l_cust_account_id number;
31 l_customer_site_use_id number;
32 l_overdue_amount number;
33 l_delinquency_status varchar2(30);
34 l_payment_schedule_id number;
35 l_template_id number;
36 l_xdo_template_id number;
37 l_workitem_id number;
38 l_execution_time date;
39 l_aging_bucket_line_id number;
40
41 l_fulfillment_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
42 l_count NUMBER := 0;
43 l_return_status VARCHAR2(20);
44 l_msg_count NUMBER;
45 l_msg_data VARCHAR2(2000);
46 l_request_id NUMBER;
47 my_message VARCHAR2(2000);
48 all_message VARCHAR2(4000);
49 l_del_tbl IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE;
50
51 --jsanju 04/09 -- fulfillment resource id
52 l_resource_id NUMBER;
53
54 cursor c_getuserid(l_resource_id NUMBER) is
55 select user_id from jtf_rs_resource_extns
56 where resource_id =l_resource_id;
57
58 l_user_id NUMBER;
59 -- ctlee, add for create dunning
60 l_unique_fulfillment VARCHAR2(1);
61 -- l_delinquency_id NUMBER;
62 l_callback_flag VARCHAR2(1);
63 l_callback_date DATE;
64 -- l_template_id NUMBER;
65 l_campaign_sched_id NUMBER;
66 l_DUNNING_rec IEX_DUNNING_PUB.DUNNING_REC_TYPE;
67 l_DUNNING_id NUMBER;
68 l_DUNNING_rec_upd IEX_DUNNING_PUB.DUNNING_REC_TYPE;
69 l_dunning_method varchar2(2000);
70
71 l_DUNNING_rec_upd_old IEX_DUNNING_PUB.DUNNING_REC_TYPE;
72 cursor c_get_dunning(p_workitem_id number) is
73 select a.dunning_id from iex_dunnings a
74 where a.object_id = p_workitem_id
75 and a.object_type = 'IEX_STRATEGY'
76 and a.status <> 'CLOSE';
77
78 l_DefaultStrategyLevel varchar2(20);
79 l_org_id NUMBER ; --Added for Bug 10401991 20-Jan-2011 barathsr
80
81 -- xdo check
82 l_curr_dmethod varchar2(10);
83 l_assign_resource_id number;
84 cursor c_get_assign_resource(l_strategy_id number, l_workitem_id number) is
85 -- begin bug 4930376 ctlee - performance 01/09/2006 -- sql id 14771818
86 SELECT
87 wkitem.resource_id ASSIGNED_TO
88 from
89 iex_strategy_work_items wkitem, iex_stry_temp_work_items_b stry_temp_wkitem_b, iex_stry_temp_work_items_tl stry_temp_wkitem_tl
90 , wf_item_types_tl item, jtf_rs_resource_extns res
91 WHERE
92 wkitem.work_item_template_id = stry_temp_wkitem_b.work_item_temp_id
93 and stry_temp_wkitem_b.work_item_temp_id =stry_temp_wkitem_tl.work_item_temp_id
94 and stry_temp_wkitem_tl.LANGUAGE = userenv('LANG')
95 and stry_temp_wkitem_b.WORKFLOW_ITEM_TYPE = item.name(+)
96 and item.language(+) = userenv('LANG')
97 and wkitem.resource_id = res.resource_id(+)
98 and wkitem.strategy_id = l_strategy_id
99 and wkitem.work_item_id = l_workitem_id;
100 -- select a.assigned_to from iex_work_item_bali_v a
101 -- where a.strategy_id = l_strategy_id
102 -- and a.wkitem_id = l_workitem_id;
103 -- end bug 4930376 ctlee - performance 01/09/2006
104
105 l_contact_destination varchar2(240); -- bug 3955222
106 l_contact_party_id number; -- bug 3955222
107
108 --Added for Bug 10401991 20-Jan-2011 barathsr
109 cursor c_get_org_id ( p_strategy_id number) is
110 select org_id from iex_strategies where strategy_id = p_strategy_id;
111
112 v_org_id number; --Added for Bug 10401991 20-Jan-2011 barathsr
113 l_turnoff_coll_on_bankru varchar2(10);
114 l_no_of_bankruptcy number;
115
116 cursor c_no_of_bankruptcy (p_par_id number)
117 is
118 select nvl(count(*),0) from iex_bankruptcies
119 where party_id = p_par_id
120 and (disposition_code in ('GRANTED','NEGOTIATION')
121 OR (disposition_code is NULL));
122
123 l_include_disputed_items varchar2(1); -- bug 14772139
124 l_dun_yn varchar2(1);
125
126 Begin
127 -- initialize variables
128 l_resource_id := fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE');
129 --Bug#4679639 schekuri 20-OCT-2005
130 --Value of profile ORG_ID should not be used for getting org_id
131 --l_org_id := fnd_profile.value('ORG_ID');
132 l_org_id := mo_global.get_current_org_id;
133
134
135 -- IF PG_DEBUG < 10 THEN
136 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
137 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, funcmode = ' || funcmode);
138 END IF;
139
140 if funcmode <> 'RUN' then
141 result := wf_engine.eng_null;
142 return;
143 end if;
144
145 -- IF PG_DEBUG < 10 THEN
146 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
147 iex_debug_pub.logmessage ('send_mail: ' || 'itemtype = ' || itemtype);
148 END IF;
149 -- IF PG_DEBUG < 10 THEN
150 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
151 iex_debug_pub.logmessage ('send_mail: ' || 'itemtkey = ' || itemkey);
152 END IF;
153 l_party_id := wf_engine.GetItemAttrNumber(
154 itemtype => itemtype,
155 itemkey => itemkey,
156 aname => 'PARTY_ID');
157
158 if (l_party_id <> 0) then
159
160 -- IF PG_DEBUG < 10 THEN
161 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
162 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, after PARTY_ID ='|| l_PARTY_ID );
163 END IF;
164
165 l_count := l_count +1;
166 l_fulfillment_bind_tbl(l_count).key_name := 'party_id';
167 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
168 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_party_id);
169 else -- party_id could not be null
170 result := 'COMPLETE:'||'N';
171 return;
172 end if;
173 l_strategy_id := wf_engine.GetItemAttrNumber(
174 itemtype => itemtype,
175 itemkey => itemkey,
176 aname => 'STRATEGY_ID');
177 if (l_strategy_id <> 0) then
178 -- IF PG_DEBUG < 10 THEN
179 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
180 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, strategy_id ' );
181 END IF;
182 l_count := l_count +1;
183 l_fulfillment_bind_tbl(l_count).key_name := 'strategy_id';
184 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
185 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_strategy_id);
186 end if;
187
188 l_delinquency_id := wf_engine.GetItemAttrNumber(
189 itemtype => itemtype,
190 itemkey => itemkey,
191 aname => 'DELINQUENCY_ID');
192 if (l_delinquency_id <> 0) then
193 -- IF PG_DEBUG < 10 THEN
194 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
195 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, delinquency_id ' );
196 END IF;
197 l_count := l_count +1;
198 l_fulfillment_bind_tbl(l_count).key_name := 'delinquency_id';
199 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
200 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_delinquency_id);
201 end if;
202
203 l_cust_account_id := wf_engine.GetItemAttrNumber(
204 itemtype => itemtype,
205 itemkey => itemkey,
206 aname => 'CUST_ACCOUNT_ID');
207 if (l_cust_account_id <> 0) then
208 -- IF PG_DEBUG < 10 THEN
209 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
210 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, cust_account_id ' );
211 END IF;
212 l_count := l_count +1;
213 l_fulfillment_bind_tbl(l_count).key_name := 'cust_account_id';
214 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
215 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_cust_account_id);
216
217 -- ctlee for xdo template using ACCOUNT_ID matching the query
218 l_count := l_count +1;
219 l_fulfillment_bind_tbl(l_count).key_name := 'account_id';
220 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
221 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_cust_account_id);
222
223 end if;
224
225
226 l_overdue_amount := wf_engine.GetItemAttrNumber(
227 itemtype => itemtype,
228 itemkey => itemkey,
229 aname => 'OVERDUE_AMOUNT');
230 if (l_overdue_amount <> 0) then
231 -- IF PG_DEBUG < 10 THEN
232 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
233 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, overdue_amount ');
234 END IF;
235 l_count := l_count +1;
236 l_fulfillment_bind_tbl(l_count).key_name := 'overdue_amount';
237 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
238 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_overdue_amount);
239 end if;
240
241
242 /* pass org_id instead
243 l_delinquency_status := wf_engine.GetItemAttrText(
244 itemtype => itemtype,
245 itemkey => itemkey,
246 aname => 'DELINQUENCY_STATUS');
247 if (l_delinquency_status is not null) then
248 -- IF PG_DEBUG < 10 THEN
249 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
250 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, delinquency_status ' );
251 END IF;
252 l_count := l_count +1;
253 l_fulfillment_bind_tbl(l_count).key_name := 'delinquency_status';
254 l_fulfillment_bind_tbl(l_count).key_type := 'VARCHAR2';
255 l_fulfillment_bind_tbl(l_count).key_value := l_delinquency_status;
256 end if;
257 */
258
259
260 --End Bug 10401991 20-Jan-2011 barathsr
261 l_aging_bucket_line_id := wf_engine.GetItemAttrNumber(
262 itemtype => itemtype,
263 itemkey => itemkey,
264 aname => 'AGING_BUCKET_LINE_ID');
265 /* not pass the l_aging_bucket_line_id - pass customer_site_use_id instead
266 if (l_aging_bucket_line_id <> 0) then
267 --IF PG_DEBUG < 10 THEN
268 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
269 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, aging_bucket_line_id ' );
270 END IF;
271 l_count := l_count +1;
272 l_fulfillment_bind_tbl(l_count).key_name := 'aging_bucket_line_id';
273 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
274 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_aging_bucket_line_id);
275 end if;
276 */
277
278 l_customer_site_use_id := wf_engine.GetItemAttrNumber(
279 itemtype => itemtype,
280 itemkey => itemkey,
281 aname => 'CUSTOMER_SITE_USE_ID');
282 if (l_customer_site_use_id <> 0) then
283 -- IF PG_DEBUG < 10 THEN
284 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
285 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, customer_site_use_id ' );
286 END IF;
287 l_count := l_count +1;
288 l_fulfillment_bind_tbl(l_count).key_name := 'customer_site_use_id';
289 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
290 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_customer_site_use_id);
291
292 end if;
293
294 -- start for bug 9151851 PNAVEENK
295 open c_get_org_id(l_strategy_id);
296 fetch c_get_org_id into v_org_id;
297 close c_get_org_id;
298 -- end
299
300 if (v_org_id is not null) then
301 -- IF PG_DEBUG < 10 THEN
302 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
303 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, org_id ' );
304 END IF;
305 --Begin Bug 10401991 20-Jan-2011 barathsr
306 l_count := l_count +1;
307 l_fulfillment_bind_tbl(l_count).key_name := 'org_id';
308 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
309 l_fulfillment_bind_tbl(l_count).key_value := v_org_id;
310 ELSE
311
312 -- bug 12363991 PNAVEENK
313 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
314 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, v_org_id is null ' );
315 END IF;
316 IF l_delinquency_id > 0 THEN
317 select org_id
318 into v_org_id
319 from iex_delinquencies_all
320 where delinquency_id=l_delinquency_id
321 AND status = 'DELINQUENT'
322 and org_id is not null
323 and rownum<=1;
324 ELSE IF l_customer_site_use_id >0 THEN
325 select org_id
326 into v_org_id
327 from iex_delinquencies_all
328 where customer_site_use_id=l_customer_site_use_id
329 AND status = 'DELINQUENT'
330 and org_id is not null
331 and rownum<=1;
332 ELSE IF l_cust_account_id > 0 THEN
333 select org_id
334 into v_org_id
335 from iex_delinquencies_all
336 where cust_account_id=l_cust_account_id
337 AND status = 'DELINQUENT'
338 and org_id is not null
339 and rownum<=1;
340 ELSE IF l_party_id > 0 THEN
341 select org_id
342 into v_org_id
343 from iex_delinquencies_all
344 where party_cust_id= l_party_id
345 AND status = 'DELINQUENT'
346 and org_id is not null
347 and rownum<=1;
348 END IF;
349 END IF;
350 END IF;
351 END IF;
352 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
353 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, v_org_id '||v_org_id );
354 END IF;
355 -- bug 12363991
356 l_count := l_count +1;
357 l_fulfillment_bind_tbl(l_count).key_name := 'org_id';
358 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
359 l_fulfillment_bind_tbl(l_count).key_value := v_org_id;
360 end if;
361
362 l_payment_schedule_id := wf_engine.GetItemAttrNumber(
363 itemtype => itemtype,
364 itemkey => itemkey,
365 aname => 'PAYMENT_SCHEDULE_ID');
366 if (l_payment_schedule_id <> 0) then
367 -- IF PG_DEBUG < 10 THEN
368 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
369 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, payment_schedule_id ');
370 END IF;
371 l_count := l_count +1;
372 l_fulfillment_bind_tbl(l_count).key_name := 'payment_schedule_id';
373 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
374 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_payment_schedule_id);
375 end if;
376
377 l_template_id := wf_engine.GetItemAttrNumber(
378 itemtype => itemtype,
379 itemkey => itemkey,
380 aname => 'TEMPLATE_ID');
381 l_xdo_template_id := wf_engine.GetItemAttrNumber(
382 itemtype => itemtype,
383 itemkey => itemkey,
384 aname => 'XDO_TEMPLATE_ID');
385 if (l_template_id <> 0) then
386 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
387 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, template_id ' );
388 END IF;
389 end if;
390
391 if (l_xdo_template_id <> 0) then
392 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
393 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, xdo_template_id ' );
394 END IF;
395 end if;
396 /*
397 l_count := l_count +1;
398 l_fulfillment_bind_tbl(l_count).key_name := 'template_id';
399 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
400 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_template_id);
401 else -- template_id could not be null
402 result := 'COMPLETE:'||'N';
403 return;
404 end if;
405 */
406
407
408
409
410 l_workitem_id := wf_engine.GetItemAttrNumber(
411 itemtype => itemtype,
412 itemkey => itemkey,
413 aname => 'WORKITEM_ID');
414 if (l_workitem_id <> 0) then
415 -- IF PG_DEBUG < 10 THEN
416 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
417 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, workitem_id => '||l_workitem_id );
418 END IF;
419 /*
420 l_count := l_count +1;
421 l_fulfillment_bind_tbl(l_count).key_name := 'workitem_id';
422 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
423 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_workitem_id);
424 */
425 else -- workitem_id could not be null
426 result := 'COMPLETE:'||'N';
427 return;
428 end if;
429
430 -- IF PG_DEBUG < 10 THEN
431 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
432 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, l_count ='|| l_count );
433 END IF;
434
435 -- ctlee - check the hz_customer_profiles.dunning_letter
436 if ( iex_utilities.DunningProfileCheck (
437 p_party_id => l_party_id
438 , p_cust_account_id => l_cust_account_id
439 , p_site_use_id => l_customer_site_use_id
440 , p_delinquency_id => l_delinquency_id ) = 'N'
441 ) then
442 result := 'COMPLETE:' || 'Y'; -- Bug #6679939 bibeura 11-Dec-2007 Changed from 'N' to 'Y'
443 begin
444 -- Bug #6679939 bibeura 11-Dec-2007 Value for parameter "avalue" is changed in the following calls
445 wf_engine.SetItemAttrText(itemtype => itemtype,
446 itemkey => itemkey,
447 aname => 'ERROR_MESSAGE',
448 avalue => 'The customer is excluded from dunning in the customer profile');
449 wf_engine.SetItemAttrText(itemtype => itemtype,
450 itemkey => itemkey,
451 aname => 'RETURN_STATUS',
452 avalue => 'S');
453 wf_engine.SetItemAttrText(itemtype => itemtype,
454 itemkey => itemkey,
455 aname => 'WK_STATUS',
456 avalue => 'SKIP');
457 EXCEPTION
458 WHEN OTHERS THEN
459 NULL;
460 END;
461 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
462 iex_debug_pub.logmessage ('send_mail: ' || ' check fail dunning profile check ' );
463 END IF;
464 return;
465 end if;
466
467 -- start of bug 15933013
468 -- ctlee - check the hz_customer_profiles_amt min_dunning_invoice_amount and min_dunning_amount
469 /* if ( iex_utilities.DunningMinAmountCheck (
470 p_cust_account_id => l_cust_account_id
471 , p_site_use_id => l_customer_site_use_id
472 , p_org_id => v_org_id)
473 = 'N' --Added for Bug 10401991 20-Jan-2011 barathsr*/
474 select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 40, 'DELINQUENCY', 'DELINQUENCY')
475 into l_DefaultStrategyLevel
476 from iex_strategies
477 where strategy_id = l_strategy_id;
478
479 l_dun_yn := iex_utilities.DunningMinAmountCheck(
480 p_cust_account_id => l_cust_account_id
481 , p_site_use_id => l_customer_site_use_id
482 , p_delinquency_id => l_delinquency_id
483 , p_org_id => v_org_id
484 , p_dun_disputed_items => l_include_disputed_items
485 , p_running_level => l_DefaultStrategyLevel
486 );
487 if l_dun_yn = 'N' then -- added for bug 15933013
488 result := 'COMPLETE:' || 'Y'; -- Bug #6679939 bibeura 11-Dec-2007 Changed from 'N' to 'Y'
489 begin
490 -- Bug #6679939 bibeura 11-Dec-2007 Value for parameter "avalue" is changed in the following calls
491 wf_engine.SetItemAttrText(itemtype => itemtype,
492 itemkey => itemkey,
493 aname => 'ERROR_MESSAGE',
494 avalue => 'The dunning amount does not exceed the minimum dunning amount in the customer profile');
495 wf_engine.SetItemAttrText(itemtype => itemtype,
496 itemkey => itemkey,
497 aname => 'RETURN_STATUS',
498 avalue => 'S');
499 wf_engine.SetItemAttrText(itemtype => itemtype,
500 itemkey => itemkey,
501 aname => 'WK_STATUS',
502 avalue => 'SKIP');
503 EXCEPTION
504 WHEN OTHERS THEN
505 NULL;
506 END;
507 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
508 iex_debug_pub.logmessage ('send_mail: ' || ' check fail dunning min amount check ' );
509 END IF;
510 return;
511 elsif l_dun_yn = 'E' then -- start for bug 15933013
512 result := 'COMPLETE:' || 'Y';
513 begin
514 wf_engine.SetItemAttrText(itemtype => itemtype,
515 itemkey => itemkey,
516 aname => 'ERROR_MESSAGE',
517 avalue => 'When strategy level is Account,value of profile "IEX: Minimum Dunning Amount Profile Check" can''t be "Bill To" or "Bill To and Account". ');
518 wf_engine.SetItemAttrText(itemtype => itemtype,
519 itemkey => itemkey,
520 aname => 'RETURN_STATUS',
521 avalue => 'S');
522 wf_engine.SetItemAttrText(itemtype => itemtype,
523 itemkey => itemkey,
524 aname => 'WK_STATUS',
525 avalue => 'SKIP');
526 EXCEPTION
527 WHEN OTHERS THEN
528 NULL;
529 END;
530 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
531 iex_debug_pub.logmessage ('send_mail: ' || 'When strategy level is Account,value of profile "IEX: Minimum Dunning Amount Profile Check" can''t be "Bill To" or "Bill To and Account".' );
532 END IF;
533 return; --end for bug 15933013
534 end if;
535
536 l_turnoff_coll_on_bankru := nvl(fnd_profile.value('IEX_TURNOFF_COLLECT_BANKRUPTCY'),'N');
537 iex_debug_pub.logmessage ('send_mail: ' || ' - l_turnoff_coll_on_bankru: ' || l_turnoff_coll_on_bankru);
538
539
540 if l_turnoff_coll_on_bankru = 'Y' then
541 open c_no_of_bankruptcy (l_party_id);
542 fetch c_no_of_bankruptcy into l_no_of_bankruptcy;
543 close c_no_of_bankruptcy;
544 end if;
545 iex_debug_pub.logmessage ('send_mail: ' || ' - l_no_of_bankruptcy: ' || l_no_of_bankruptcy);
546 if (l_turnoff_coll_on_bankru = 'Y' and l_no_of_bankruptcy >0 ) then
547 result := 'COMPLETE:' || 'Y';
548 begin
549 wf_engine.SetItemAttrText(itemtype => itemtype,
550 itemkey => itemkey,
551 aname => 'ERROR_MESSAGE',
552 avalue => 'Profile IEX: Turn Off Collections Activity for Bankruptcy is Yes and bankruptcy record is exist, so will skip send dunning');
553 wf_engine.SetItemAttrText(itemtype => itemtype,
554 itemkey => itemkey,
555 aname => 'RETURN_STATUS',
556 avalue => 'S');
557 wf_engine.SetItemAttrText(itemtype => itemtype,
558 itemkey => itemkey,
559 aname => 'WK_STATUS',
560 avalue => 'SKIP');
561 EXCEPTION
562 WHEN OTHERS THEN
563 NULL;
564 END;
565 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
566 iex_debug_pub.logmessage ('send_mail: ' || ' bankruptcy exist for this customer, so will skip send dunning ' );
567 END IF;
568 return;
569
570 end if;
571
572 --jsanju 04/09 fulfilment user only to send
573 OPEN c_getuserid(l_resource_id) ;
574 fetch c_getuserid INTO l_user_id;
575 CLOSE c_getuserid;
576
577
578 --ctlee 12/17/04 xdo - resource id in the xdo query
579 OPEN c_get_assign_resource(l_strategy_id, l_workitem_id) ;
580 fetch c_get_assign_resource INTO l_assign_resource_id;
581 CLOSE c_get_assign_resource;
582
583
584 -- ctlee - 7/15 fulfillment fax method available
585 begin
586
587 select upper(b.category_type),
588 nvl(include_disputed_items,'N') -- bug 14772139
589 into l_dunning_method,
590 l_include_disputed_items -- bug 14772139
591 from iex_strategy_work_items a, IEX_STRY_TEMP_WORK_ITEMS_VL b
592 where a.work_item_template_id = b.work_item_temp_id
593 and a.work_item_id = l_workitem_id
594 --and b.work_type = 'AUTOMATIC';-- bug 14772139
595 and b.work_type in ('AUTOMATIC','WORKFLOW'); -- bug 14772139
596
597 l_dunning_rec.dunning_method := l_dunning_method; -- default
598
599 exception
600 when others then
601 l_dunning_method := 'EMAIL';
602 l_dunning_rec.dunning_method := l_dunning_method; -- default
603 l_include_disputed_items := 'Y'; -- bug 14772139
604 end;
605 -- start for bug 9151851 PNAVEENK
606 l_dunning_rec.org_id := v_org_id;
607 -- end
608 -- IF PG_DEBUG < 10 THEN
609 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
610 iex_debug_pub.logmessage ('send_mail: ' || ' after fulfil method');
611 END IF;
612 iex_debug_pub.logmessage(' Org_id value ' || l_dunning_rec.org_id);
613 -- ctlee - 5/20 create dunning record; 11/21/2002 remove checking profile error when update a non-existing record
614 -- l_unique_fulfillment := nvl(fnd_profile.value('IEX_STRY_UNIQUE_FULFILMENT'), 'N');
615 -- if (l_unique_fulfillment = 'Y') then
616 l_dunning_rec.delinquency_id := l_delinquency_id;
617 l_dunning_rec.callback_yn := ''; -- l_callback_flag;
618 l_dunning_rec.callback_date := ''; -- l_callback_date;
619 l_dunning_rec.status := 'OPEN';
620
621 l_dunning_rec.template_id:= l_template_id;
622 -- ctlee xdo template id
623 l_dunning_rec.xml_template_id:= l_template_id;
624
625 l_dunning_rec.object_type:= 'IEX_STRATEGY';
626 --l_dunning_rec.dunning_method:= 'EMAIL';
627 l_dunning_rec.object_id:= l_workitem_id;
628
629 -- set dunning_object_id and dunnint_level
630 begin
631 select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 40, 'DELINQUENCY', 'DELINQUENCY')
632 into l_DefaultStrategyLevel
633 from iex_strategies
634 where strategy_id = l_strategy_id;
635
636 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
637 iex_debug_pub.logmessage('Default StrategyLevel ' || l_DefaultStrategyLevel);
638 END IF;
639 EXCEPTION
640 WHEN OTHERS THEN
641 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
642 iex_debug_pub.logmessage('Strategy Level Rised Exception ');
643 END IF;
644 l_DefaultStrategyLevel := 'DELINQUENCY';
645 END;
646 IF l_DefaultStrategyLevel = 'CUSTOMER' THEN
647 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
648 l_dunning_rec.dunning_object_id:= l_party_id;
649 l_del_tbl(1).party_cust_id := l_party_id;
650 l_del_tbl(1).cust_account_id := 0;
651 l_del_tbl(1).customer_site_use_id := 0;
652
653 elsif l_DefaultStrategyLevel = 'ACCOUNT' THEN
654 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
655 l_dunning_rec.dunning_object_id:= l_cust_account_id;
656 l_del_tbl(1).party_cust_id := l_party_id;
657 l_del_tbl(1).cust_account_id := l_cust_account_id;
658 l_del_tbl(1).customer_site_use_id := 0;
659 elsif l_DefaultStrategyLevel = 'BILL_TO' THEN
660 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
661 l_dunning_rec.dunning_object_id:= l_customer_site_use_id;
662 l_del_tbl(1).party_cust_id := l_party_id;
663 l_del_tbl(1).cust_account_id := l_cust_account_id;
664 l_del_tbl(1).customer_site_use_id := l_customer_site_use_id;
665 else
666 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
667 l_dunning_rec.dunning_object_id:= l_delinquency_id;
668 l_del_tbl(1).party_cust_id := l_party_id;
669 l_del_tbl(1).cust_account_id := l_cust_account_id;
670 l_del_tbl(1).customer_site_use_id := l_customer_site_use_id;
671 l_del_tbl(1).delinquency_id := l_delinquency_id;
672 end if;
673
674 begin
675 select campaign_sched_id into l_campaign_sched_id from iex_delinquencies_all
676 where delinquency_id = l_delinquency_id;
677 l_dunning_rec.campaign_sched_id := l_campaign_sched_id;
678 exception
679 when others then
680 l_dunning_rec.campaign_sched_id := null;
681 end;
682
683 -- close all the open dunning record before created
684 FOR d_rec in c_get_dunning(l_workitem_id)
685 LOOP
686 begin
687 l_dunning_rec_upd_old.dunning_id := d_rec.dunning_id;
688 l_dunning_rec_upd_old.last_update_date := sysdate;
689 l_dunning_rec_upd_old.callback_yn := 'N';
690 l_dunning_rec_upd_old.status := 'SKIP';
691 -- l_dunning_rec_upd_old.object_type:= 'IEX_STRATEGY';
692 -- l_dunning_rec_upd_old.object_id:= l_workitem_id;
693
694 IEX_DUNNING_PVT.Update_DUNNING(
695 p_api_version => 1.0
696 , p_init_msg_list => FND_API.G_FALSE
697 , p_commit => FND_API.G_FALSE
698 , p_dunning_rec => l_dunning_rec_upd_old
699 , x_return_status => l_return_status
700 , x_msg_count => l_msg_count
701 , x_msg_data => l_msg_data
702 );
703 exception
704 when others then
705 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
706 iex_debug_pub.logmessage ('close dunning before fulfillment exception');
707 END IF;
708 end;
709 END LOOP;
710
711 -- IF PG_DEBUG < 10 THEN
712 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
713 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, before create dunning ' );
714 END IF;
715 -- Start Added by gnramasa for bug 5661324 14-Mar-07
716 l_dunning_rec.template_id:= l_template_id;
717 l_dunning_rec.xml_template_id:= l_xdo_template_id;
718
719 iex_debug_pub.logmessage(' Org_id value ' || l_dunning_rec.org_id);
720 iex_debug_pub.logmessage ('send_mail: ' || ' WorkItem Id = '||l_workitem_id );
721 -- End Added by gnramasa for bug 5661324 14-Mar-07
722 IEX_DUNNING_PVT.CREATE_DUNNING(
723 p_api_version => 1.0
724 , p_init_msg_list => FND_API.G_FALSE
725 , p_commit => FND_API.G_FALSE
726 , p_dunning_rec => l_dunning_rec
727 , x_dunning_id => l_dunning_id
728 , x_return_status => l_return_status
729 , x_msg_count => l_msg_count
730 , x_msg_data => l_msg_data);
731
732 -- Added call IEX_DUNNING_PVT.INSERT_DUNNING_TRANSACTION to fix 14256867
733 IEX_DUNNING_PVT.INSERT_DUNNING_TRANSACTION(
734 p_api_version => 1.0
735 , p_init_msg_list => FND_API.G_FALSE
736 , p_commit => FND_API.G_FALSE
737 , p_delinquencies_tbl => l_del_tbl
738 , p_ag_dn_xref_id => 0
739 , p_dunning_id => l_dunning_id
740 , p_correspondence_date => sysdate
741 , p_running_level => l_DefaultStrategyLevel
742 , p_grace_days => 0
743 , p_include_dispute_items => l_include_disputed_items -- 'Y' bug 14772139
744 , x_return_status => l_return_status
745 , x_msg_count => l_msg_count
746 , x_msg_data => l_msg_data
747 , p_workitem_id => l_workitem_id); -- bug 14772139
748
749 -- end if;
750 -- Start Added by gnramasa for bug 5661324 14-Mar-07
751
752 l_count := l_count +1;
753 l_fulfillment_bind_tbl(l_count).key_name := 'DUNNING_ID';
754 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
755 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_dunning_id);
756 -- End Added by gnramasa for bug 5661324 14-Mar-07
757 -- IF PG_DEBUG < 10 THEN
758 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
759 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, before send_fulfillment ' );
760 END IF;
761 -- call fulfilment function with multiple bind variables.
762 -- ctlee - 6/18 fulfillment printer method available
763 l_curr_dmethod := iex_send_xml_pvt.getCurrDeliveryMethod();
764 if (l_curr_dmethod = 'FFM') then
765 iex_dunning_pvt.send_fulfillment(
766 p_api_version => 1.0,
767 p_init_msg_list => FND_API.G_TRUE,
768 p_commit => FND_API.G_TRUE,
769 p_FULFILLMENT_BIND_TBL => l_fulfillment_bind_tbl,
770 p_template_id => l_template_id,
771 p_method => l_dunning_method,
772 p_party_id => l_party_id,
773 p_user_id => l_user_id ,
774 x_return_status => l_return_status,
775 x_msg_count => l_msg_count,
776 x_msg_data => l_msg_data,
777 x_REQUEST_ID => l_request_id,
778 x_contact_destination => l_contact_destination, -- bug 3955222
779 x_contact_party_id => l_contact_party_id); -- bug 3955222
780 else
781 --Added for bug#8490070 by SNUTHALA on 29-May-2009
782 BEGIN
783 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
784 iex_debug_pub.logmessage ('send_mail: cancelling all xml requests for this workitem: ' || SQLERRM );
785 END IF;
786 update iex_xml_request_histories
787 set status='CANCELLED'
788 where object_type='IEX_STRATEGY'
789 and status<>'CANCELLED'
790 and xml_request_id in (select xml_request_id
791 from iex_dunnings
792 where object_type='IEX_STRATEGY'
793 and object_id=l_workitem_id);
794 EXCEPTION
795 WHEN NO_DATA_FOUND THEN
796 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
797 iex_debug_pub.logmessage ('send_mail: ' || ' no active previous xml request exists for this workitem ' );
798 END IF;
799 WHEN OTHERS THEN
800 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
801 iex_debug_pub.logmessage ('send_mail: error while checking for previous active xml requests for this workitem: ' || SQLERRM );
802 END IF;
803 END;
804 iex_dunning_pvt.send_xml(
805 p_api_version => 1.0,
806 p_init_msg_list => FND_API.G_TRUE,
807 p_commit => FND_API.G_TRUE,
808 p_resend => 'N',
809 p_request_id => null,
810 p_FULFILLMENT_BIND_TBL => l_fulfillment_bind_tbl,
811 p_template_id => l_xdo_template_id,
812 p_method => l_dunning_method,
813 p_user_id => l_user_id,
814 p_email => null,
815 p_party_id => l_party_id,
816 p_level => l_dunning_rec.dunning_level, -- strategy level
817 p_resource_id => l_assign_resource_id, --Bug5233002. Fix By LKKUMAR.
818 p_object_code => l_dunning_rec.object_type, -- 'IEX_STRATEGY'
819 p_source_id => l_dunning_rec.dunning_object_id, -- used by iex_send_xml_pvt.send_copy
820 p_object_id => l_workitem_id, -- changed for bug#8403051 by PNAVEENK on 3-4-2009 l_dunning_rec.dunning_object_id, -- party/account/billto/del id
821 p_org_id => l_dunning_rec.org_id, -- changed for bug 9151851 PNAVEENK
822 x_return_status => l_return_status,
823 x_msg_count => l_msg_count,
824 x_msg_data => l_msg_data,
825 x_REQUEST_ID => l_request_id,
826 x_contact_destination => l_contact_destination, -- bug 3955222
827 x_contact_party_id => l_contact_party_id); -- bug 3955222
828 end if;
829 -- Start Change by gnramasa for bug 5661324 14-Mar-07
830 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
831 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, after send_fulfillment ' );
832 iex_debug_pub.logmessage ('send_mail: ' || 'request_id =>'|| l_request_id);
833 iex_debug_pub.logmessage ('send_mail: ' || 'return_status =>'|| l_return_status);
834 iex_debug_pub.logmessage ('send_mail: ' || 'msg_count =>' || l_msg_count);
835 iex_debug_pub.logmessage ('send_mail: ' || 'msg_data =>' || l_msg_data);
836 END IF;
837
838 --- share a request id between xdo and ffm
839 begin
840 wf_engine.SetItemAttrNumber(itemtype => itemtype,
841 itemkey => itemkey,
842 aname => 'REQUEST_ID',
843 avalue => l_request_id);
844
845 wf_engine.SetItemAttrText(itemtype => itemtype,
846 itemkey => itemkey,
847 aname => 'ERROR_MESSAGE',
848 avalue => l_msg_data);
849 wf_engine.SetItemAttrText(itemtype => itemtype,
850 itemkey => itemkey,
851 aname => 'RETURN_STATUS',
852 avalue => l_return_status);
853 EXCEPTION
854 WHEN OTHERS THEN
855 NULL;
856 END;
857 -- return to workflow
858 if (l_request_id is null OR l_return_status <> 'S') then
859 wf_engine.SetItemAttrText(itemtype => itemtype,
860 itemkey => itemkey,
861 aname => 'WK_STATUS',
862 avalue => 'INERROR');
863
864 all_message := null;
865 FOR l_index IN 1..l_msg_count LOOP
866 my_message := FND_MSG_PUB.Get(p_msg_index => l_index,
867 p_encoded => 'F');
868 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
869 iex_debug_pub.logmessage ('send_mail: ' || my_message);
870 END IF;
871 if all_message is null then
872 all_message := my_message;
873 else
874 all_message := all_message || '; ' || chr(0) || my_message;
875 end if;
876 END LOOP;
877 iex_debug_pub.logmessage ('all_message: ' || all_message);
878
879 wf_engine.SetItemAttrText(itemtype => itemtype,
880 itemkey => itemkey,
881 aname => 'ERROR_MESSAGE',
882 avalue => all_message);
883 result := 'COMPLETE:' || 'N';
884 iex_stry_utl_pub.update_work_item(
885 p_api_version => 1.0,
886 p_commit => FND_API.G_TRUE,
887 p_init_msg_list => FND_API.G_TRUE,
888 p_work_item_id => l_workitem_id,
889 p_status => 'INERROR_CHECK_NOTIFY',
890 x_return_status => l_return_status,
891 x_msg_count => l_msg_count,
892 x_msg_data => l_msg_data
893 );
894 else
895 wf_engine.SetItemAttrText(itemtype => itemtype,
896 itemkey => itemkey,
897 aname => 'WK_STATUS',
898 avalue => 'COMPLETE');
899 wf_engine.SetItemAttrText(itemtype => itemtype,
900 itemkey => itemkey,
901 aname => 'ERROR_MESSAGE',
902 avalue => null);
903 result := 'COMPLETE:'||'Y';
904 wf_engine.SetItemAttrDate(itemtype => itemtype,
905 itemkey => itemkey,
906 aname => 'DELIVERY_WAIT_TIME',
907 avalue => sysdate+nvl(fnd_profile.value('IEX_DELIVERY_WAIT_DAYS'),0));
908
909 end if;
910 l_dunning_rec_upd.dunning_id := l_dunning_id;
911 l_dunning_rec_upd.last_update_date := sysdate;
912 l_dunning_rec_upd.callback_yn := 'N';
913 l_dunning_rec_upd.status := 'CLOSE';
914 -- ctlee xdo template id
915 if (l_curr_dmethod = 'FFM') then
916 l_dunning_rec_upd.ffm_request_id := l_request_id;
917 else
918 l_dunning_rec_upd.xml_request_id := l_request_id;
919 end if;
920 l_dunning_rec_upd.contact_destination := l_contact_destination; -- bug 3955222
921 l_dunning_rec_upd.contact_party_id := l_contact_party_id; -- bug 3955222
922
923 IEX_DUNNING_PVT.Update_DUNNING(
924 p_api_version => 1.0
925 , p_init_msg_list => FND_API.G_FALSE
926 , p_commit => FND_API.G_FALSE
927 , p_dunning_rec => l_dunning_rec_upd
928 , x_return_status => l_return_status
929 , x_msg_count => l_msg_count
930 , x_msg_data => l_msg_data
931 );
932
933 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
934 iex_debug_pub.logmessage ('update dunning l_status =>' || l_return_status);
935 iex_debug_pub.logmessage ('send_mail: ' || 'result =>' || result);
936 END IF;
937
938 exception
939 when others then
940 wf_core.context('IEX_STRATEGY_WORK',' send_mail ',itemtype,
941 itemkey,to_char(actid),funcmode);
942 raise;
943
944 end send_mail;
945
946 procedure get_username
947 ( p_resource_id IN NUMBER,
948 x_username OUT NOCOPY VARCHAR2 ) IS
949 cursor c_getname(p_resource_id NUMBER) is
950 Select user_name
951 from jtf_rs_resource_extns
952 where resource_id =p_resource_id;
953
954 BEGIN
955 -- IF PG_DEBUG < 10 THEN
956 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
957 iex_debug_pub.logmessage ('**** BEGIN get_username ************');
958 END IF;
959 OPEN c_getname(p_resource_id);
960 FETCH c_getname INTO x_username;
961 CLOSE c_getname;
962 -- IF PG_DEBUG < 10 THEN
963 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
964 iex_debug_pub.logmessage ('**** END get_username ************');
965 END IF;
966 END get_username;
967 -- End Change by gnramasa for bug 5661324 14-Mar-07
968 -----populate execution_times---------------------------------
969 --set execution wait period
970 --populate to fulfillment workflow wait
971
972 procedure populate_fulfillment_wait
973 (
974 p_delinquency_id IN NUMBER,
975 p_work_item_id IN NUMBER,
976 itemtype IN varchar2,
977 itemkey IN varchar2
978 ) IS
979
980
981 cursor c_get_del(p_delinquency_id number) is
982 select a.party_id, a.party_type, a.party_name,
983 a.person_first_name, a.person_last_name,
984 b.cust_account_id, b.status, b.payment_schedule_id,
985 b.aging_bucket_line_id, b.customer_site_use_id
986 from iex_delinquencies_all b, hz_parties a
987 where a.party_id(+) = b.party_cust_id
988 and b.delinquency_id = p_delinquency_id;
989
990 cursor c_get_party(p_work_item_id number) is
991 select a.party_id, a.party_type, a.party_name,
992 a.person_first_name, a.person_last_name, s.cust_account_id, s.customer_site_use_id
993 from hz_parties a, iex_strategy_work_items w, iex_strategies s
994 where a.party_id = s.party_id and s.strategy_id = w.strategy_id and w.work_item_id = p_work_item_id;
995
996 -- bug 4930376 ctlee sql id 14771930, use _all performance
997 cursor c_get_payment(p_delinquency_id number) is
998 select a.amount_due_remaining
999 from ar_payment_schedules_all a, iex_delinquencies_all b
1000 where a.payment_schedule_id(+) = b.payment_schedule_id
1001 and b.delinquency_id = p_delinquency_id;
1002
1003 cursor c_get_witem_temp(p_work_item_id NUMBER) is
1004 select a.post_execution_wait, a.execution_time_uom, a.schedule_wait, a.schedule_uom
1005 from IEX_STRY_TEMP_WORK_ITEMS_VL a, IEX_STRATEGY_WORK_ITEMS b
1006 where b.work_item_template_id = a.work_item_temp_id
1007 and b.work_item_id = p_work_item_id;
1008
1009 l_fulfillment_wait date;
1010 l_fulfillment_schedule date;
1011 l_strategy_level number ;
1012 l_resource_id NUMBER;
1013 l_username VARCHAR2(120);
1014 --Begin bug#5502077 schekuri 02-May-2007
1015 l_strategy_id NUMBER;
1016 l_SkipFlag NUMBER;
1017 --End bug#5502077 schekuri 02-May-2007
1018 BEGIN
1019 -- IF PG_DEBUG < 10 THEN
1020 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1021 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'DEL ID = ' ||p_delinquency_id);
1022 END IF;
1023
1024 -- IF PG_DEBUG < 10 THEN
1025 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1026 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'work item id = ' ||p_work_item_id);
1027 END IF;
1028 begin
1029 select a.strategy_level,b.resource_id,a.strategy_id --Added strategy_id for bug#5502077 schekuri 02-May-2007
1030 into l_strategy_level,l_resource_id,l_strategy_id
1031 from iex_strategies a, iex_strategy_work_items b
1032 where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
1033 if l_strategy_level is null then
1034 l_strategy_level := 40;
1035 end if;
1036 EXCEPTION WHEN OTHERS THEN
1037 l_strategy_level := 40; -- default to delinquency level
1038 end;
1039
1040 -- get user name from jtf_rs_resource_extns
1041 if (l_resource_id is not null) THEN
1042 get_username( p_resource_id =>l_resource_id,
1043 x_username =>l_username);
1044 else
1045 l_username := 'SYSADMIN';
1046 end if;
1047
1048
1049 wf_engine.SetItemAttrText(itemtype => itemtype,
1050 itemkey => itemkey,
1051 aname => 'NOTIFICATION_USERNAME',
1052 avalue => l_username);
1053
1054 -- IF PG_DEBUG < 10 THEN
1055 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1056 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'strategy_level = ' ||l_strategy_level);
1057 END IF;
1058
1059 if l_strategy_level = 10 or l_strategy_level = 20 or l_strategy_level = 30 then
1060 FOR party_rec in c_get_party(p_work_item_id)
1061 LOOP
1062 -- IF PG_DEBUG < 10 THEN
1063 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1064 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'INSIDE THE LOOP ' ||party_rec.party_id);
1065 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'INSIDE THE LOOP ' ||party_rec.cust_account_id);
1066 END IF;
1067 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1068 itemkey => itemkey,
1069 aname => 'PARTY_ID',
1070 avalue => party_rec.party_id);
1071 wf_engine.SetItemAttrText(itemtype => itemtype,
1072 itemkey => itemkey,
1073 aname => 'PARTY_TYPE',
1074 avalue => party_rec.party_type);
1075 wf_engine.SetItemAttrText(itemtype => itemtype,
1076 itemkey => itemkey,
1077 aname => 'PARTY_NAME',
1078 avalue => party_rec.party_name);
1079 wf_engine.SetItemAttrText(itemtype => itemtype,
1080 itemkey => itemkey,
1081 aname => 'FIRST_NAME',
1082 avalue => party_rec.person_first_name);
1083 wf_engine.SetItemAttrText(itemtype => itemtype,
1084 itemkey => itemkey,
1085 aname => 'LAST_NAME',
1086 avalue => party_rec.person_last_name);
1087 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1088 itemkey => itemkey,
1089 aname => 'CUST_ACCOUNT_ID',
1090 avalue => party_rec.cust_account_id);
1091 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1092 itemkey => itemkey,
1093 aname => 'CUSTOMER_SITE_USE_ID',
1094 avalue => party_rec.customer_site_use_id);
1095 exit;
1096 END LOOP;
1097 else
1098 FOR d_rec in c_get_del(p_delinquency_id)
1099 LOOP
1100 -- IF PG_DEBUG < 10 THEN
1101 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1102 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'INSIDE THE LOOP ' ||d_rec.party_id);
1103 END IF;
1104 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1105 itemkey => itemkey,
1106 aname => 'PARTY_ID',
1107 avalue => d_rec.party_id);
1108 wf_engine.SetItemAttrText(itemtype => itemtype,
1109 itemkey => itemkey,
1110 aname => 'PARTY_TYPE',
1111 avalue => d_rec.party_type);
1112 wf_engine.SetItemAttrText(itemtype => itemtype,
1113 itemkey => itemkey,
1114 aname => 'PARTY_NAME',
1115 avalue => d_rec.party_name);
1116 wf_engine.SetItemAttrText(itemtype => itemtype,
1117 itemkey => itemkey,
1118 aname => 'FIRST_NAME',
1119 avalue => d_rec.person_first_name);
1120 wf_engine.SetItemAttrText(itemtype => itemtype,
1121 itemkey => itemkey,
1122 aname => 'LAST_NAME',
1123 avalue => d_rec.person_last_name);
1124 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1125 itemkey => itemkey,
1126 aname => 'CUST_ACCOUNT_ID',
1127 avalue => d_rec.cust_account_id);
1128 wf_engine.SetItemAttrText(itemtype => itemtype,
1129 itemkey => itemkey,
1130 aname => 'DELINQUENCY_STATUS',
1131 avalue => d_rec.status);
1132 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1133 itemkey => itemkey,
1134 aname => 'AGING_BUCKET_LINE_ID',
1135 avalue => d_rec.aging_bucket_LINE_id);
1136 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1137 itemkey => itemkey,
1138 aname => 'PAYMENT_SCHEDULE_ID',
1139 avalue => d_rec.payment_schedule_id);
1140 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1141 itemkey => itemkey,
1142 aname => 'CUSTOMER_SITE_USE_ID',
1143 avalue => d_rec.customer_site_use_id);
1144 exit;
1145 END LOOP;
1146 FOR p_rec in c_get_payment(p_delinquency_id)
1147 LOOP
1148 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1149 itemkey => itemkey,
1150 aname => 'OVERDUE_AMOUNT',
1151 avalue => p_rec.amount_due_remaining);
1152 exit;
1153 END LOOP;
1154 end if;
1155
1156
1157
1158 FOR c_rec in c_get_witem_temp(p_work_item_id)
1159 LOOP
1160 --Begin bug#5502077 schekuri 02-May-2007
1161 --If the Strategy workflow contains RESET_WORK_ITEM_STATUS activity
1162 --skip the SCHEDULE_WAIT. Since it already waits at pre-wait node in
1163 --main Strategy workflow there is no need to wait at WAIT node in Fulfillment workflow.
1164 l_SkipFlag := 0;
1165
1166 if (l_strategy_id is not null) then
1167
1168 BEGIN
1169
1170 select ceil(wfi.BEGIN_DATE - wfa.begin_Date) into l_SkipFlag
1171 from WF_ITEMS wfi, WF_ACTIVITIES wfa
1172 WHERE wfi.ITEM_TYPE = 'IEXSTRY'
1173 and wfa.version = (select min(wa.version) from wf_activities wa
1174 where wa.item_type=wfa.item_type
1175 and wa.name=wfa.name)
1176 and wfi.item_key = l_Strategy_id
1177 and wfa.item_type = wfi.item_type AND
1178 wfa.name = 'RESET_WORK_ITEM_STATUS' ;
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN NULL;
1182 END;
1183
1184 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1185 iex_debug_pub.logmessage ('populate_fulfillment_wait: check for skip ' || ' SKIP FLAG = ' ||
1186 l_SkipFlag);
1187 END IF;
1188
1189 end if;
1190
1191 IF (l_SkipFlag > 0) THEN
1192 l_fulfillment_schedule := SYSDATE;
1193 ELSE
1194
1195 l_fulfillment_schedule:=IEX_STRY_UTL_PUB.get_date
1196 (p_date =>SYSDATE,
1197 l_UOM =>c_rec.schedule_uom,
1198 l_UNIT =>c_rec.schedule_wait);
1199 END IF;
1200
1201 /*l_fulfillment_schedule:=IEX_STRY_UTL_PUB.get_date
1202 (p_date =>SYSDATE,
1203 l_UOM =>c_rec.schedule_uom,
1204 l_UNIT =>c_rec.schedule_wait);*/
1205 --End bug#5502077 schekuri 02-May-2007
1206 -- IF PG_DEBUG < 10 THEN
1207 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1208 iex_debug_pub.logmessage ('populate_fulfillment_wait: ' || ' SCHEDULE TIME WAIT = ' ||
1209 to_char(l_fulfillment_schedule, 'ss:mi:hh24 mm/dd/yyyy'));
1210 END IF;
1211
1212
1213 --set execution wait attribute
1214 wf_engine.SetItemAttrDate(itemtype => itemtype,
1215 itemkey => itemkey,
1216 aname => 'SCHEDULE_TIME',
1217 avalue => l_fulfillment_schedule);
1218
1219 --begin bug#5502077 schekuri 30-Apr-2007
1220 --since there is post wait in the main strategy workflow there is no need to wait here
1221 /*l_fulfillment_wait:=IEX_STRY_UTL_PUB.get_date
1222 (p_date =>l_fulfillment_schedule,
1223 l_UOM =>c_rec.execution_time_uom,
1224 l_UNIT =>c_rec.post_execution_wait);*/
1225 l_fulfillment_wait:= sysdate;
1226 --end bug#5502077 schekuri 30-Apr-2007
1227
1228 -- IF PG_DEBUG < 10 THEN
1229 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1230 iex_debug_pub.logmessage ('populate_fulfillment_wait: ' || ' EXECUTION TIME WAIT = ' ||
1231 to_char(l_fulfillment_wait, 'ss:mi:hh24 mm/dd/yyyy'));
1232 END IF;
1233
1234
1235 --set execution wait attribute
1236 wf_engine.SetItemAttrDate(itemtype => itemtype,
1237 itemkey => itemkey,
1238 aname => 'EXECUTION_TIME',
1239 avalue => l_fulfillment_wait);
1240
1241 exit;
1242 END LOOP;
1243 EXCEPTION WHEN OTHERS THEN
1244 null;
1245 END populate_fulfillment_wait;
1246
1247 /**
1248 * setup the workflow which call the mailer thru fulfilment
1249 **/
1250 procedure strategy_mailer(
1251 p_api_version IN NUMBER,
1252 p_init_msg_list IN VARCHAR2,
1253 p_commit IN VARCHAR2,
1254 p_strategy_mailer_rec IN STRATEGY_MAILER_REC_TYPE,
1255 x_return_status OUT NOCOPY VARCHAR2,
1256 x_msg_count OUT NOCOPY NUMBER,
1257 x_msg_data OUT NOCOPY VARCHAR2)
1258 IS
1259 l_itemtype varchar2(80);
1260 l_itemkey varchar2(80);
1261 l_workflowprocess varchar2(80);
1262 l_result varchar2(80);
1263
1264 l_error_msg VARCHAR2(2000);
1265 l_return_status VARCHAR2(20);
1266 l_msg_count NUMBER;
1267 l_msg_data VARCHAR2(2000);
1268 l_api_name VARCHAR2(100) ;
1269 l_api_version_number CONSTANT NUMBER := 1.0;
1270
1271 -- start for bug 9668367 PNAVEENK
1272 l_party_id number;
1273
1274 cursor c_party_id(p_strategy_id number) is
1275 select party_id from iex_strategies
1276 where strategy_id = p_strategy_id;
1277 -- end for bug 9668367
1278
1279
1280 begin
1281 -- initialize variables
1282 l_api_name := 'STRATEGY_MAILER';
1283
1284
1285 --IF PG_DEBUG < 10 THEN
1286 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1287 iex_debug_pub.logmessage ('in strategy_mailer');
1288 END IF;
1289 -- Standard Start of API savepoint
1290 -- SAVEPOINT STRATEGY_MAILER; -- Standard call to check for call compatibility.
1291 --IF PG_DEBUG < 10 THEN
1292 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1293 iex_debug_pub.logmessage ('in strategy_mailer 1');
1294 END IF;
1295 IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version, l_api_name, G_PKG_NAME) THEN
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 END IF;
1298 --IF PG_DEBUG < 10 THEN
1299 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1300 iex_debug_pub.logmessage ('in strategy_mailer 2');
1301 END IF;
1302 -- Initialize message list IF p_init_msg_list is set to TRUE.
1303 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1304 FND_MSG_PUB.initialize;
1305 END IF;
1306 --IF PG_DEBUG < 10 THEN
1307 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1308 iex_debug_pub.logmessage ('in strategy_mailer 3');
1309 END IF;
1310
1311
1312 -- Initialize API return status to SUCCESS
1313 x_return_status := FND_API.G_RET_STS_SUCCESS;
1314 l_itemtype := 'IEXSTFFM';
1315 l_workflowprocess := 'IEXSTFFM';
1316 l_itemkey := p_strategy_mailer_rec.workitem_id;
1317 --IF PG_DEBUG < 10 THEN
1318 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1319 iex_debug_pub.logmessage ('in strategy_mailer 4');
1320 END IF;
1321
1322 wf_engine.createprocess ( itemtype => l_itemtype,
1323 itemkey => l_itemkey,
1324 process => l_workflowprocess);
1325 --IF PG_DEBUG < 10 THEN
1326 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1327 iex_debug_pub.logmessage ('in strategy_mailer 5');
1328 END IF;
1329
1330 -- start for bug 9668367
1331 open c_party_id(p_strategy_mailer_rec.strategy_id);
1332 fetch c_party_id into l_party_id;
1333 close c_party_id;
1334
1335 if (l_party_id is not null) then
1336 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1337 itemkey => l_itemkey,
1338 aname => 'PARTY_ID',
1339 avalue => l_party_id);
1340 end if;
1341 -- end for bug 9668367
1342
1343
1344 if (p_strategy_mailer_rec.strategy_id is not null) then
1345 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1346 itemkey => l_itemkey,
1347 aname => 'STRATEGY_ID',
1348 avalue => p_strategy_mailer_rec.strategy_id);
1349 end if;
1350 if (p_strategy_mailer_rec.delinquency_id is not null) then
1351 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1352 itemkey => l_itemkey,
1353 aname => 'DELINQUENCY_ID',
1354 avalue => p_strategy_mailer_rec.delinquency_id);
1355 end if;
1356 if (p_strategy_mailer_rec.template_id is not null) then
1357 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1358 itemkey => l_itemkey,
1359 aname => 'TEMPLATE_ID',
1360 avalue => p_strategy_mailer_rec.template_id);
1361 end if;
1362 if (p_strategy_mailer_rec.xdo_template_id is not null) then
1363 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1364 itemkey => l_itemkey,
1365 aname => 'XDO_TEMPLATE_ID',
1366 avalue => p_strategy_mailer_rec.xdo_template_id);
1367 end if;
1368 if (p_strategy_mailer_rec.workitem_id is not null) then
1369 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1370 itemkey => l_itemkey,
1371 aname => 'WORKITEM_ID',
1372 avalue => p_strategy_mailer_rec.workitem_id);
1373 end if;
1374
1375 if (p_strategy_mailer_rec.user_id is not null) then
1376 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1377 itemkey => l_itemkey,
1378 aname => 'USER_ID',
1379 avalue => p_strategy_mailer_rec.user_id);
1380 end if;
1381
1382 if (p_strategy_mailer_rec.resp_id is not null) then
1383 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1384 itemkey => l_itemkey,
1385 aname => 'RESP_ID',
1386 avalue => p_strategy_mailer_rec.resp_id);
1387 end if;
1388
1389 if (p_strategy_mailer_rec.resp_appl_id is not null) then
1390 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1391 itemkey => l_itemkey,
1392 aname => 'RESP_APPL_ID',
1393 avalue => p_strategy_mailer_rec.resp_appl_id);
1394 end if;
1395
1396
1397
1398 populate_fulfillment_wait (
1399 p_delinquency_id => p_strategy_mailer_rec.delinquency_id,
1400 p_work_item_id => p_strategy_mailer_rec.workitem_id,
1401 itemtype => l_itemtype,
1402 itemkey => l_itemkey
1403 );
1404
1405 -- IF PG_DEBUG < 10 THEN
1406 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1407 iex_debug_pub.logmessage ('strategy_mailer: ' || ' before start workflow process');
1408 END IF;
1409
1410 wf_engine.startprocess(itemtype => l_itemtype, itemkey => l_itemkey);
1411 -- IF PG_DEBUG < 10 THEN
1412 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1413 iex_debug_pub.logmessage ('strategy_mailer: ' || ' after start workflow process');
1414 END IF;
1415 wf_engine.ItemStatus(
1416 itemtype => l_itemType,
1417 itemkey => l_itemKey,
1418 status => l_return_status,
1419 result => l_result);
1420
1421 if (l_return_status in ('COMPLETE', 'ACTIVE')) THEN
1422 x_return_status := 'S';
1423 else
1424 x_return_status := 'F';
1425 end if;
1426 -- IF PG_DEBUG < 10 THEN
1427 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1428 iex_debug_pub.logmessage ('strategy_mailer: ' || ' workflow return status = ' || l_return_status);
1429 END IF;
1430
1431
1432 EXCEPTION
1433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1434 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1435 iex_debug_pub.logmessage('UNEXPECTED ERROR. PUB: ' || l_api_name || ' end');
1436 iex_debug_pub.logmessage('PUB: ' || l_api_name || ' end');
1437 iex_debug_pub.logmessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1438 END IF;
1439
1440 WHEN OTHERS THEN
1441 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1442 iex_debug_pub.logmessage('UNHANDLED WORKFLOW EXCEPTION. Strategy ID ' || p_strategy_mailer_rec.strategy_id);
1443 iex_debug_pub.logmessage('PUB: ' || l_api_name || ' end');
1444 iex_debug_pub.logmessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1445 END IF;
1446
1447 end strategy_mailer;
1448
1449
1450
1451 procedure wf_send_signal(
1452 itemtype in varchar2,
1453 itemkey in varchar2,
1454 actid in number,
1455 funcmode in varchar2,
1456 result out NOCOPY varchar2)
1457 IS
1458 l_work_item_id number;
1459 l_strategy_id number;
1460 l_wk_status varchar2(20);
1461 l_return_status VARCHAR2(20);
1462 l_msg_count NUMBER;
1463 l_msg_data VARCHAR2(2000);
1464 exc EXCEPTION;
1465 l_error VARCHAR2(32767);
1466 begin
1467 if funcmode <> 'RUN' then
1468 result := wf_engine.eng_null;
1469 return;
1470 end if;
1471
1472 l_work_item_id := wf_engine.GetItemAttrNumber(
1473 itemtype => itemtype,
1474 itemkey => itemkey,
1475 aname => 'WORKITEM_ID');
1476 l_strategy_id := wf_engine.GetItemAttrNumber(
1477 itemtype => itemtype,
1478 itemkey => itemkey,
1479 aname => 'STRATEGY_ID');
1480 if (l_work_item_id is not null) then
1481 --04/04 jsanju
1482 -- do not update the send signal will update
1483 --04/16/02 -- update it here and send signal if successful
1484 --05/20/02 -- update it work item status
1485 l_wk_status := wf_engine.GetItemAttrText(
1486 itemtype => itemtype,
1487 itemkey => itemkey,
1488 aname => 'WK_STATUS');
1489
1490 iex_stry_utl_pub.update_work_item(
1491 p_api_version => 1.0,
1492 p_commit => FND_API.G_TRUE,
1493 p_init_msg_list => FND_API.G_TRUE,
1494 p_work_item_id => l_work_item_id,
1495 p_status => l_wk_status,
1496 x_return_status => l_return_status,
1497 x_msg_count => l_msg_count,
1498 x_msg_data => l_msg_data
1499 );
1500
1501 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1502 --04/04 -jsanju
1503 -- add new parameter to send signal
1504
1505 iex_strategy_wf.send_signal(
1506 process => 'IEXSTRY' ,
1507 strategy_id => l_strategy_id,
1508 status => l_wk_status,
1509 work_item_id => l_work_item_id,
1510 signal_source => 'FULFILLMENT');
1511 else
1512 RAISE EXC;
1513
1514 end if;-- if update successful
1515
1516 end if;
1517 result := wf_engine.eng_completed;
1518
1519 EXCEPTION
1520 WHEN EXC THEN
1521 --pass the error message
1522 -- get error message and pass
1523 iex_strategy_wf.Get_Messages(l_msg_count,l_error);
1524 -- IF PG_DEBUG < 10 THEN
1525 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1526 iex_debug_pub.logmessage('wf_send_signal: ' || 'error message is ' || l_error);
1527 END IF;
1528 wf_core.context('IEX_STRATEGY_WORK_PUB','wf_send_signal',itemtype,
1529 itemkey,to_char(actid),funcmode,l_error);
1530 raise;
1531
1532 WHEN OTHERS THEN
1533
1534 wf_core.context('IEX_STRATEGY_WORK_PUB','wf_send_signal',itemtype,
1535 itemkey,to_char(actid),funcmode);
1536 raise;
1537
1538
1539 end wf_send_signal;
1540
1541
1542
1543
1544 procedure check_dunning(
1545 itemtype in varchar2,
1546 itemkey in varchar2,
1547 actid in number,
1548 funcmode in varchar2,
1549 result out NOCOPY varchar2)
1550 IS
1551 l_return_status VARCHAR2(20);
1552 l_msg_count NUMBER;
1553 l_msg_data VARCHAR2(2000);
1554 l_error VARCHAR2(32767);
1555 l_delinquency_id NUMBER;
1556 l_cust_account_id NUMBER;
1557 l_customer_site_use_id NUMBER;
1558 l_count NUMBER := 0;
1559
1560 l_user_id NUMBER;
1561 l_resp_id NUMBER;
1562 l_resp_appl_id NUMBER;
1563
1564 l_work_item_id number;
1565 l_strategy_id number;
1566 l_party_id number;
1567 -- l_cust_account_id number;
1568 l_strategy_level varchar2(20);
1569 l_unique_fulfillment VARCHAR2(1);
1570 begin
1571
1572 if funcmode <> 'RUN' then
1573 result := 'COMPLETE:' || 'N';
1574 return;
1575 end if;
1576 result := 'COMPLETE:' || 'N';
1577
1578 l_unique_fulfillment := nvl(fnd_profile.value('IEX_STRY_UNIQUE_FULFILMENT'), 'N');
1579 /*
1580 if (l_unique_fulfillment = 'N') then
1581 result := 'COMPLETE:' || 'Y';
1582 return;
1583 end if;
1584 */
1585
1586 l_user_id := wf_engine.GetItemAttrNumber(
1587 itemtype => itemtype,
1588 itemkey => itemkey,
1589 aname => 'USER_ID');
1590
1591 l_resp_id := wf_engine.GetItemAttrNumber(
1592 itemtype => itemtype,
1593 itemkey => itemkey,
1594 aname => 'RESP_ID');
1595
1596 l_resp_appl_id := wf_engine.GetItemAttrNumber(
1597 itemtype => itemtype,
1598 itemkey => itemkey,
1599 aname => 'RESP_APPL_ID');
1600
1601 l_delinquency_id := wf_engine.GetItemAttrNumber(
1602 itemtype => itemtype,
1603 itemkey => itemkey,
1604 aname => 'DELINQUENCY_ID');
1605 l_work_item_id := wf_engine.GetItemAttrNumber(
1606 itemtype => itemtype,
1607 itemkey => itemkey,
1608 aname => 'WORKITEM_ID');
1609 l_strategy_id := wf_engine.GetItemAttrNumber(
1610 itemtype => itemtype,
1611 itemkey => itemkey,
1612 aname => 'STRATEGY_ID');
1613 l_party_id := wf_engine.GetItemAttrNumber(
1614 itemtype => itemtype,
1615 itemkey => itemkey,
1616 aname => 'PARTY_ID');
1617 l_cust_account_id := wf_engine.GetItemAttrNumber(
1618 itemtype => itemtype,
1619 itemkey => itemkey,
1620 aname => 'CUST_ACCOUNT_ID');
1621 l_customer_site_use_id := wf_engine.GetItemAttrNumber(
1622 itemtype => itemtype,
1623 itemkey => itemkey,
1624 aname => 'CUSTOMER_SITE_USE_ID');
1625 -- IF PG_DEBUG < 10 THEN
1626 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1627 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, delinquency_id = ' || l_delinquency_id);
1628 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, party_id = ' || l_party_id);
1629 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, cust_account_id = ' || l_cust_account_id);
1630 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, strategy_id = ' || l_strategy_id);
1631 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, work_item_id = ' || l_work_item_id);
1632 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, customer_site_use_id = ' || l_customer_site_use_id);
1633 END IF;
1634
1635 -- IF PG_DEBUG < 10 THEN
1636 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1637 iex_debug_pub.logmessage ('check_dunning: ' || 'USER_ID' || l_user_id || ' RESP_ID ' || l_resp_id);
1638 END IF;
1639 -- IF PG_DEBUG < 10 THEN
1640 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1641 iex_debug_pub.logmessage ('check_dunning: ' || 'RESP_APPL_ID' ||l_resp_appl_id);
1642 END IF;
1643 --set the session
1644 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
1645
1646 select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 'DELINQUENCY') into l_strategy_level
1647 from iex_strategies where strategy_id = l_strategy_id;
1648 -- if l_delinquency_id is not null then
1649 if l_strategy_level = 'CUSTOMER' then
1650 begin
1651 -- IF PG_DEBUG < 10 THEN
1652 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1653 iex_debug_pub.logmessage ('check_dunning: level 10' || ' check dunning, party_id = ' || l_party_id);
1654 END IF;
1655 if l_party_id is not null then
1656 select count(*) into l_count from iex_dunnings where dunning_id in (
1657 select dun.dunning_id from iex_dunnings dun
1658 where dun.dunning_object_id = l_party_id
1659 and dun.dunning_level = l_strategy_level
1660 and dun.status = 'CLOSE'
1661 and trunc(sysdate) = trunc(dun.creation_date) );
1662 -- IF PG_DEBUG < 10 THEN
1663 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1664 iex_debug_pub.logmessage ('check_dunning: level 10' || ' check dunning, l_count = ' || l_count);
1665 END IF;
1666 if l_count > 0 and l_unique_fulfillment = 'Y' then
1667 wf_engine.SetItemAttrText(itemtype => itemtype,
1668 itemkey => itemkey,
1669 aname => 'WK_STATUS',
1670 avalue => 'CANCELLED');
1671 result := 'COMPLETE:' || 'Y';
1672 end if;
1673 end if;
1674 exception
1675 when others then
1676 result := 'COMPLETE:' || 'N';
1677 end;
1678 elsif l_strategy_level = 'ACCOUNT' then
1679 begin
1680 -- IF PG_DEBUG < 10 THEN
1681 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1682 iex_debug_pub.logmessage ('check_dunning: level 20' || ' check dunning, cust_account_id = ' || l_cust_account_id);
1683 END IF;
1684 if l_cust_account_id is not null then
1685 select count(*) into l_count from iex_dunnings where dunning_id in (
1686 select dun.dunning_id from iex_dunnings dun
1687 where dun.dunning_object_id = l_cust_account_id
1688 and dun.dunning_level = l_strategy_level
1689 and dun.status = 'CLOSE'
1690 and trunc(sysdate) = trunc(dun.creation_date) );
1691 -- IF PG_DEBUG < 10 THEN
1692 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1693 iex_debug_pub.logmessage ('check_dunning: level 20' || ' check dunning, l_count = ' || l_count);
1694 END IF;
1695 if l_count > 0 and l_unique_fulfillment = 'Y' then
1696 wf_engine.SetItemAttrText(itemtype => itemtype,
1697 itemkey => itemkey,
1698 aname => 'WK_STATUS',
1699 avalue => 'CANCELLED');
1700 result := 'COMPLETE:' || 'Y';
1701 end if;
1702 end if;
1703 exception
1704 when others then
1705 result := 'COMPLETE:' || 'N';
1706 end;
1707 elsif l_strategy_level = 'BILL_TO' then
1708 begin
1709 -- IF PG_DEBUG < 10 THEN
1710 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1711 iex_debug_pub.logmessage ('check_dunning: level 30' || ' check dunning, customer_site_use_id = ' || l_customer_site_use_id);
1712 END IF;
1713 if l_customer_site_use_id is not null then
1714 select count(*) into l_count from iex_dunnings where dunning_id in (
1715 select dun.dunning_id from iex_dunnings dun
1716 where dun.dunning_object_id = l_customer_site_use_id
1717 and dun.dunning_level = l_strategy_level
1718 and dun.status = 'CLOSE'
1719 and trunc(sysdate) = trunc(dun.creation_date) );
1720 -- IF PG_DEBUG < 10 THEN
1721 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1722 iex_debug_pub.logmessage ('check_dunning: level 30' || ' check dunning, l_count = ' || l_count);
1723 END IF;
1724 if l_count > 0 and l_unique_fulfillment = 'Y' then
1725 wf_engine.SetItemAttrText(itemtype => itemtype,
1726 itemkey => itemkey,
1727 aname => 'WK_STATUS',
1728 avalue => 'CANCELLED');
1729 result := 'COMPLETE:' || 'Y';
1730 end if;
1731 end if;
1732 exception
1733 when others then
1734 result := 'COMPLETE:' || 'N';
1735 end;
1736 else -- default level 40
1737 begin
1738 select cust_account_id into l_cust_account_id from iex_delinquencies_all
1739 where delinquency_id = l_delinquency_id;
1740 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1741 iex_debug_pub.logmessage ('check_dunning: 40 ' || ' check dunning, delinquency_id = ' || l_delinquency_id);
1742 END IF;
1743 if l_cust_account_id is not null then
1744 -- begin bug #4230209 03/09/2005 by ctlee, multiple letters were sent in the same day even unique = 'Y'
1745 -- select count(*) into l_count from iex_dunnings where dunning_id in (
1746 -- select dun.dunning_id from iex_delinquencies del, iex_dunnings dun
1747 -- where del.cust_account_id = l_cust_account_id
1748 -- and del.delinquency_id = dun.delinquency_id
1749 -- and dun.status = 'CLOSE'
1750 -- and trunc(sysdate) = trunc(dun.creation_date) );
1751 select count(*) into l_count from iex_dunnings where dunning_id in (
1752 select dun.dunning_id from iex_delinquencies_all del, iex_dunnings dun
1753 where del.cust_account_id = l_cust_account_id
1754 and ((del.delinquency_id = dun.delinquency_id and dun.status = 'CLOSE') or
1755 (del.delinquency_id = dun.delinquency_id and dun.status = 'OPEN' and
1756 del.delinquency_id <> l_delinquency_id )
1757 )
1758 and trunc(sysdate) = trunc(dun.creation_date) );
1759 -- end bug #4230209 03/09/2005 by ctlee, multiple letters were sent in the same day even unique = 'Y'
1760 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1761 iex_debug_pub.logmessage ('check_dunning: 40' || ' check dunning, l_count = ' || l_count);
1762 END IF;
1763 if l_count > 0 and l_unique_fulfillment = 'Y' then
1764 wf_engine.SetItemAttrText(itemtype => itemtype,
1765 itemkey => itemkey,
1766 aname => 'WK_STATUS',
1767 avalue => 'CANCELLED');
1768 result := 'COMPLETE:' || 'Y';
1769 end if;
1770 end if;
1771 exception
1772 when others then
1773 result := 'COMPLETE:' || 'N';
1774 end;
1775 end if;
1776 EXCEPTION
1777 WHEN OTHERS THEN
1778 result := 'COMPLETE:' || 'N';
1779 wf_core.context('IEX_STRATEGY_WORK_PUB','check_dunning',itemtype,
1780 itemkey,to_char(actid),funcmode);
1781 raise;
1782 end check_dunning;
1783
1784 /* resend fulfillment by clicked the button */
1785
1786 procedure resend_fulfillment(
1787 p_work_item_id IN NUMBER
1788 , x_status out NOCOPY varchar2
1789 , x_error_message out NOCOPY varchar2
1790 , x_request_id out NOCOPY number)
1791 IS
1792 p_delinquency_id NUMBER;
1793
1794 l_party_id number;
1795 l_strategy_id number;
1796 l_delinquency_id number;
1797 l_party_type varchar2(80);
1798 l_party_name varchar2(240);
1799 l_first_name varchar2(80);
1800 l_last_name varchar2(80);
1801 l_cust_account_id number;
1802 l_customer_site_use_id number;
1803 l_overdue_amount number;
1804 l_status varchar2(30);
1805 l_payment_schedule_id number;
1806 l_template_id number;
1807 -- xdo check
1808 l_xdo_template_id number;
1809 l_workitem_id number;
1810 l_aging_bucket_line_id number;
1811 l_fulfil_temp_id number;
1812 l_xdo_temp_id number;
1813
1814 l_fulfillment_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
1815 l_count NUMBER := 0;
1816 l_return_status VARCHAR2(20);
1817 l_msg_count NUMBER;
1818 l_msg_data VARCHAR2(2000);
1819 l_request_id NUMBER;
1820 my_message VARCHAR2(2000);
1821
1822 --jsanju 04/09 -- fulfillment resource id
1823 l_resource_id NUMBER;
1824 l_del_tbl IEX_DELINQUENCY_PUB.DELINQUENCY_TBL_TYPE;
1825 cursor c_getuserid(l_resource_id NUMBER) is
1826 select user_id from jtf_rs_resource_extns
1827 where resource_id =l_resource_id;
1828
1829 l_user_id NUMBER;
1830 -- ctlee, add for create dunning
1831 l_unique_fulfillment VARCHAR2(1);
1832 -- l_delinquency_id NUMBER;
1833 l_callback_flag VARCHAR2(1);
1834 l_callback_date DATE;
1835 -- l_template_id NUMBER;
1836 l_campaign_sched_id NUMBER;
1837 l_DUNNING_rec IEX_DUNNING_PUB.DUNNING_REC_TYPE;
1838 l_DUNNING_id NUMBER;
1839 l_DUNNING_rec_upd IEX_DUNNING_PUB.DUNNING_REC_TYPE;
1840 l_dunning_method varchar2(2000);
1841
1842 l_DUNNING_rec_upd_old IEX_DUNNING_PUB.DUNNING_REC_TYPE;
1843 cursor c_get_dunning(p_workitem_id number) is
1844 select a.dunning_id from iex_dunnings a
1845 where a.object_id = p_workitem_id
1846 and a.object_type = 'WORK_ITEM'
1847 and a.status <> 'CLOSE';
1848
1849
1850 cursor c_get_del(p_delinquency_id number) is
1851 select a.party_id, a.party_type, a.party_name,
1852 a.person_first_name, a.person_last_name,
1853 b.cust_account_id, b.status, b.payment_schedule_id,
1854 b.aging_bucket_line_id, b.customer_site_use_id
1855 from iex_delinquencies_all b, hz_parties a
1856 where a.party_id(+) = b.party_cust_id
1857 and b.delinquency_id = p_delinquency_id;
1858
1859 -- bug 4930376 ctlee sql id 14772154, use _all performance
1860 cursor c_get_payment(p_delinquency_id number) is
1861 select a.amount_due_remaining
1862 from ar_payment_schedules_all a, iex_delinquencies_all b
1863 where a.payment_schedule_id(+) = b.payment_schedule_id
1864 and b.delinquency_id = p_delinquency_id;
1865
1866 cursor c_get_witem_temp(p_work_item_id NUMBER) is
1867 select delinquency_id, a.strategy_id
1868 from iex_strategies a, iex_strategy_work_items b
1869 where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
1870
1871 -- ctlee using xdo template id
1872 cursor c_get_xdo_template(p_work_item_id NUMBER) is
1873 select a.xdo_template_id from IEX_STRY_TEMP_WORK_ITEMS_VL a, iex_strategy_work_items b
1874 where a.work_item_temp_id = b.work_item_template_id and work_item_id = p_work_item_id;
1875
1876 cursor c_get_fulfillment_template(p_work_item_id NUMBER) is
1877 select fulfil_temp_id from IEX_STRY_TEMP_WORK_ITEMS_VL a, iex_strategy_work_items b
1878 where a.work_item_temp_id = b.work_item_template_id and work_item_id = p_work_item_id;
1879
1880 cursor c_get_party(p_work_item_id number) is
1881 select a.party_id, a.party_type, a.party_name,
1882 a.person_first_name, a.person_last_name, s.cust_account_id, s.customer_site_use_id
1883 from hz_parties a, iex_strategy_work_items w, iex_strategies s
1884 where a.party_id = s.party_id and s.strategy_id = w.strategy_id and w.work_item_id = p_work_item_id;
1885
1886 l_strategy_level number ;
1887 l_DefaultStrategyLevel varchar2(20);
1888
1889 -- xdo check
1890 l_curr_dmethod varchar2(10);
1891 l_assign_resource_id number;
1892 cursor c_get_assign_resource(l_strategy_id number, l_workitem_id number) is
1893 -- begin bug 4930376 ctlee - performance 01/09/2006 -- sql id 14772213
1894 SELECT
1895 wkitem.resource_id ASSIGNED_TO
1896 from
1897 iex_strategy_work_items wkitem, iex_stry_temp_work_items_b stry_temp_wkitem_b, iex_stry_temp_work_items_tl stry_temp_wkitem_tl
1898 , wf_item_types_tl item, jtf_rs_resource_extns res
1899 WHERE
1900 wkitem.work_item_template_id = stry_temp_wkitem_b.work_item_temp_id
1901 and stry_temp_wkitem_b.work_item_temp_id =stry_temp_wkitem_tl.work_item_temp_id
1902 and stry_temp_wkitem_tl.LANGUAGE = userenv('LANG')
1903 and stry_temp_wkitem_b.WORKFLOW_ITEM_TYPE = item.name(+)
1904 and item.language(+) = userenv('LANG')
1905 and wkitem.resource_id = res.resource_id(+)
1906 and wkitem.strategy_id = l_strategy_id
1907 and wkitem.work_item_id = l_workitem_id;
1908 -- select a.assigned_to from iex_work_item_bali_v a
1909 -- where a.strategy_id = l_strategy_id
1910 -- and a.wkitem_id = l_workitem_id;
1911 -- end bug 4930376 ctlee - performance 01/09/2006
1912
1913 -- start for bug 9151851
1914 cursor c_get_org_id ( p_strategy_id number) is
1915 select org_id from iex_strategies where strategy_id = p_strategy_id;
1916 -- end
1917 l_contact_destination varchar2(240); -- bug 3955222
1918 l_contact_party_id number; -- bug 3955222
1919 v_org_id number; -- added for bug 9151851
1920
1921 l_include_disputed_items varchar2(1); -- bug 14772139
1922 l_dun_yn varchar2(1);
1923 l_parent_dunning_id number; -- added to fix 16433137 SNUTHALA MAR/08/2013
1924
1925 BEGIN
1926
1927 l_resource_id := fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE');
1928 x_status := 'F';
1929 x_request_id := 0;
1930 l_workitem_id := p_work_item_id;
1931 FOR c_rec in c_get_witem_temp(p_work_item_id)
1932 LOOP
1933 l_delinquency_id := c_rec.delinquency_id;
1934 l_strategy_id := c_rec.strategy_id;
1935 exit;
1936 END LOOP;
1937
1938 -- IF PG_DEBUG < 10 THEN
1939 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1940 iex_debug_pub.logmessage('resend_fulfillment: ' || 'DEL ID ' ||l_delinquency_id);
1941 END IF;
1942
1943 FOR x_rec in c_get_xdo_template(p_work_item_id)
1944 LOOP
1945 -- xdo template id
1946 l_xdo_temp_id := x_rec.xdo_template_id;
1947 l_xdo_template_id := l_xdo_temp_id;
1948 exit;
1949 END LOOP;
1950
1951 FOR f_rec in c_get_fulfillment_template(p_work_item_id)
1952 LOOP
1953 l_fulfil_temp_id := f_rec.fulfil_temp_id;
1954 l_template_id := l_fulfil_temp_id;
1955 exit;
1956 END LOOP;
1957 begin
1958 select a.strategy_level into l_strategy_level from iex_strategies a, iex_strategy_work_items b
1959 where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
1960 if l_strategy_level is null then
1961 l_strategy_level := 40;
1962 end if;
1963 EXCEPTION WHEN OTHERS THEN
1964 l_strategy_level := 40; -- default to delinquency level
1965 end;
1966 -- IF PG_DEBUG < 10 THEN
1967 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1968 iex_debug_pub.logmessage('resend_fulfillment: ' || 'strategy_level = ' ||l_strategy_level);
1969 END IF;
1970
1971 if l_strategy_level = 10 or l_strategy_level = 20 or l_strategy_level = 30 then
1972 FOR party_rec in c_get_party(p_work_item_id)
1973 LOOP
1974 l_party_id := party_rec.party_id;
1975 l_party_type := party_rec.party_type;
1976 l_party_name := party_rec.party_name;
1977 l_first_name := party_rec.person_first_name;
1978 l_last_name := party_rec.person_last_name;
1979 l_cust_account_id := party_rec.cust_account_id;
1980 l_customer_site_use_id := party_rec.customer_site_use_id;
1981 -- l_status := party_rec.status;
1982 -- l_aging_bucket_LINE_id := party_rec.aging_bucket_LINE_id;
1983 -- l_payment_schedule_id := party_rec.payment_schedule_id;
1984 exit;
1985 END LOOP;
1986 else
1987 FOR d_rec in c_get_del(l_delinquency_id)
1988 LOOP
1989 -- IF PG_DEBUG < 10 THEN
1990 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1991 iex_debug_pub.logmessage('resend_fulfillment: ' || 'INSIDE THE LOOP ' ||d_rec.party_id);
1992 END IF;
1993 l_party_id := d_rec.party_id;
1994 l_party_type := d_rec.party_type;
1995 l_party_name := d_rec.party_name;
1996 l_first_name := d_rec.person_first_name;
1997 l_last_name := d_rec.person_last_name;
1998 l_cust_account_id := d_rec.cust_account_id;
1999 l_status := d_rec.status;
2000 l_aging_bucket_LINE_id := d_rec.aging_bucket_LINE_id;
2001 l_payment_schedule_id := d_rec.payment_schedule_id;
2002 l_customer_site_use_id := d_rec.customer_site_use_id;
2003 exit;
2004 END LOOP;
2005
2006 FOR p_rec in c_get_payment(l_delinquency_id)
2007 LOOP
2008 l_overdue_amount := p_rec.amount_due_remaining;
2009 exit;
2010 END LOOP;
2011 end if;
2012
2013
2014 if (l_party_id <> 0) then
2015
2016 -- IF PG_DEBUG < 10 THEN
2017 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2018 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, after PARTY_ID ='|| l_PARTY_ID );
2019 END IF;
2020
2021 l_count := l_count +1;
2022 l_fulfillment_bind_tbl(l_count).key_name := 'party_id';
2023 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2024 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_party_id);
2025 else -- party_id could not be null
2026 x_error_message := 'No party id';
2027 return;
2028 end if;
2029
2030 if (l_strategy_id <> 0) then
2031 -- IF PG_DEBUG < 10 THEN
2032 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2033 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, strategy_id ' );
2034 END IF;
2035 l_count := l_count +1;
2036 l_fulfillment_bind_tbl(l_count).key_name := 'strategy_id';
2037 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2038 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_strategy_id);
2039 end if;
2040
2041
2042 if (l_delinquency_id <> 0) then
2043 -- IF PG_DEBUG < 10 THEN
2044 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2045 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, delinquency_id ' );
2046 END IF;
2047 l_count := l_count +1;
2048 l_fulfillment_bind_tbl(l_count).key_name := 'delinquency_id';
2049 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2050 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_delinquency_id);
2051 end if;
2052
2053 if (l_cust_account_id <> 0) then
2054 -- IF PG_DEBUG < 10 THEN
2055 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2056 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, cust_account_id ' );
2057 END IF;
2058 l_count := l_count +1;
2059 l_fulfillment_bind_tbl(l_count).key_name := 'cust_account_id';
2060 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2061 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_cust_account_id);
2062
2063 -- ctlee for xdo template using ACCOUNT_ID matching the query
2064 l_count := l_count +1;
2065 l_fulfillment_bind_tbl(l_count).key_name := 'account_id';
2066 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2067 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_cust_account_id);
2068 end if;
2069
2070 if (l_customer_site_use_id <> 0) then
2071 -- IF PG_DEBUG < 10 THEN
2072 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2073 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, customer_site_use_id ' );
2074 END IF;
2075 l_count := l_count +1;
2076 l_fulfillment_bind_tbl(l_count).key_name := 'customer_site_use_id';
2077 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2078 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_customer_site_use_id);
2079
2080 end if;
2081
2082 -- start for bug 9151851
2083 open c_get_org_id ( l_strategy_id);
2084 fetch c_get_org_id into v_org_id;
2085 close c_get_org_id;
2086 -- end
2087 --Begin Bug 10401991 20-Jan-2011 barathsr
2088 if (v_org_id is not null) then
2089 l_count := l_count +1;
2090 l_fulfillment_bind_tbl(l_count).key_name := 'org_id';
2091 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2092 l_fulfillment_bind_tbl(l_count).key_value := v_org_id;
2093 else
2094 --begin bug 7425517 by sunagesh on 21-nov-11
2095 IF l_delinquency_id > 0 THEN
2096 select org_id
2097 into v_org_id
2098 from iex_delinquencies_all
2099 where delinquency_id=l_delinquency_id
2100 AND status = 'DELINQUENT'
2101 and org_id is not null
2102 and rownum<=1;
2103 ELSE IF l_customer_site_use_id >0 THEN
2104 select org_id
2105 into v_org_id
2106 from iex_delinquencies_all
2107 where customer_site_use_id=l_customer_site_use_id
2108 AND status = 'DELINQUENT'
2109 and org_id is not null
2110 and rownum<=1;
2111 ELSE IF l_cust_account_id > 0 THEN
2112 select org_id
2113 into v_org_id
2114 from iex_delinquencies_all
2115 where cust_account_id=l_cust_account_id
2116 AND status = 'DELINQUENT'
2117 and org_id is not null
2118 and rownum<=1;
2119 ELSE IF l_party_id > 0 THEN
2120 select org_id
2121 into v_org_id
2122 from iex_delinquencies_all
2123 where party_cust_id= l_party_id
2124 AND status = 'DELINQUENT'
2125 and org_id is not null
2126 and rownum<=1;
2127 END IF;
2128 END IF;
2129 END IF;
2130 END IF;
2131 --end bug 7425517 by sunagesh on 21-nov-11
2132 /*select org_id
2133 into v_org_id
2134 from iex_delinquencies_all
2135 where cust_account_id=l_cust_account_id
2136 and org_id is not null
2137 and rownum<=1;*/
2138 l_count := l_count +1;
2139 l_fulfillment_bind_tbl(l_count).key_name := 'org_id';
2140 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2141 l_fulfillment_bind_tbl(l_count).key_value := v_org_id;
2142 end if;
2143 --End Bug 10401991 20-Jan-2011 barathsr
2144
2145
2146 if l_strategy_level = 40 then
2147 if (l_overdue_amount <> 0) then
2148 -- IF PG_DEBUG < 10 THEN
2149 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2150 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, overdue_amount ');
2151 END IF;
2152 l_count := l_count +1;
2153 l_fulfillment_bind_tbl(l_count).key_name := 'overdue_amount';
2154 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2155 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_overdue_amount);
2156 end if;
2157
2158
2159 if (l_status is not null) then
2160 -- IF PG_DEBUG < 10 THEN
2161 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2162 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, delinquency_status ' );
2163 END IF;
2164 l_count := l_count +1;
2165 l_fulfillment_bind_tbl(l_count).key_name := 'delinquency_status';
2166 l_fulfillment_bind_tbl(l_count).key_type := 'VARCHAR2';
2167 l_fulfillment_bind_tbl(l_count).key_value := l_status;
2168 end if;
2169
2170 /*
2171 if (l_aging_bucket_line_id <> 0) then
2172 -- IF PG_DEBUG < 10 THEN
2173 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2174 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, aging_bucket_line_id ' );
2175 END IF;
2176 l_count := l_count +1;
2177 l_fulfillment_bind_tbl(l_count).key_name := 'aging_bucket_line_id';
2178 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2179 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_aging_bucket_line_id);
2180 end if;
2181 */
2182
2183
2184 if (l_payment_schedule_id <> 0) then
2185 -- IF PG_DEBUG < 10 THEN
2186 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2187 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, payment_schedule_id ');
2188 END IF;
2189 l_count := l_count +1;
2190 l_fulfillment_bind_tbl(l_count).key_name := 'payment_schedule_id';
2191 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2192 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_payment_schedule_id);
2193 end if;
2194
2195 end if; -- only if strategy_level = 40
2196
2197 l_curr_dmethod := iex_send_xml_pvt.getCurrDeliveryMethod();
2198 if (l_curr_dmethod = 'FFM') then
2199 if (l_fulfil_temp_id <> 0) then
2200 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2201 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, template_id ' );
2202 END IF;
2203 else -- template_id could not be null
2204 x_error_message := 'No fulfillment template id';
2205 return;
2206 end if;
2207 else
2208 if (l_xdo_template_id <> 0) then
2209 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2210 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, xdo_template_id ' );
2211 END IF;
2212 else -- template_id could not be null
2213 x_error_message := 'No xdo template id';
2214 return;
2215 end if;
2216 end if;
2217
2218
2219 if (p_work_item_id <> 0) then
2220 -- IF PG_DEBUG < 10 THEN
2221 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2222 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, workitem_id ' );
2223 END IF;
2224 /*
2225 l_count := l_count +1;
2226 l_fulfillment_bind_tbl(l_count).key_name := 'workitem_id';
2227 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2228 l_fulfillment_bind_tbl(l_count).key_value := to_char(p_work_item_id);
2229 */
2230 else -- workitem_id could not be null
2231 x_error_message := 'No workitem id';
2232 return;
2233 end if;
2234
2235 -- IF PG_DEBUG < 10 THEN
2236 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2237 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, l_count ='|| l_count );
2238 END IF;
2239
2240
2241 -- ctlee - check the hz_customer_profiles.dunning_letter
2242 if ( iex_utilities.DunningProfileCheck (
2243 p_party_id => l_party_id
2244 , p_cust_account_id => l_cust_account_id
2245 , p_site_use_id => l_customer_site_use_id
2246 , p_delinquency_id => l_delinquency_id ) = 'N'
2247 ) then
2248 x_error_message := 'Customer profile has set the dunning flag to NO';
2249 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2250 iex_debug_pub.logmessage ('resend_fulfillment: ' || x_error_message);
2251 END IF;
2252 return;
2253 end if;
2254
2255 --start of bug 15933013
2256 -- ctlee - check the hz_customer_profiles_amt min_dunning_invoice_amount and min_dunning_amount
2257 /*if ( iex_utilities.DunningMinAmountCheck (
2258 p_cust_account_id => l_cust_account_id
2259 , p_site_use_id => l_customer_site_use_id
2260 , p_org_id => v_org_id) = 'N' --Added for Bug 10401991 20-Jan-2011 barathsr
2261 ) */
2262 select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 40, 'DELINQUENCY', 'DELINQUENCY')
2263 into l_DefaultStrategyLevel
2264 from iex_strategies
2265 where strategy_id = l_strategy_id;
2266
2267 l_dun_yn := iex_utilities.DunningMinAmountCheck(
2268 p_cust_account_id => l_cust_account_id
2269 , p_site_use_id => l_customer_site_use_id
2270 , p_delinquency_id => l_delinquency_id
2271 , p_org_id => v_org_id
2272 , p_dun_disputed_items => l_include_disputed_items
2273 , p_running_level => l_DefaultStrategyLevel
2274 );
2275 if l_dun_yn = 'N' then
2276 x_error_message := 'The dunning amount does not exceed the minimum dunning amount in customer profile';
2277 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2278 iex_debug_pub.logmessage ('resend_fulfillment: ' || x_error_message);
2279 END IF;
2280 return;
2281 elsif l_dun_yn = 'E' then
2282 x_error_message := 'When strategy level is Account,value of profile "IEX: Minimum Dunning Amount Profile Check" can''t be "Bill To" or "Bill To and Account". ';
2283 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2284 iex_debug_pub.logmessage ('resend_fulfillment: ' || x_error_message);
2285 END IF;
2286 return;
2287 end if;
2288
2289 --jsanju 04/09 fulfilment user
2290 OPEN c_getuserid(l_resource_id) ;
2291 fetch c_getuserid INTO l_user_id;
2292 CLOSE c_getuserid;
2293
2294 --ctlee 12/17/04 xdo - resource id in the xdo query
2295 OPEN c_get_assign_resource(l_strategy_id, l_workitem_id) ;
2296 fetch c_get_assign_resource INTO l_assign_resource_id;
2297 CLOSE c_get_assign_resource;
2298
2299
2300
2301 -- ctlee - 7/15 fulfillment fax method available
2302 begin
2303
2304 select upper(b.category_type),
2305 nvl(include_disputed_items,'N') -- bug 14772139
2306 into l_dunning_method,
2307 l_include_disputed_items -- bug 14772139
2308 from iex_strategy_work_items a, IEX_STRY_TEMP_WORK_ITEMS_VL b
2309 where a.work_item_template_id = b.work_item_temp_id
2310 and a.work_item_id = l_workitem_id
2311 --and b.work_type = 'AUTOMATIC';-- bug 14772139
2312 and b.work_type in ('AUTOMATIC','WORKFLOW'); -- bug 14772139
2313
2314 l_dunning_rec.dunning_method := l_dunning_method; -- default
2315
2316 exception
2317 when others then
2318 l_dunning_method := 'EMAIL';
2319 l_dunning_rec.dunning_method := l_dunning_method; -- default
2320 l_include_disputed_items := 'Y'; -- bug 14772139
2321 end;
2322
2323 -- IF PG_DEBUG < 10 THEN
2324 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2325 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' after fulfil method');
2326 END IF;
2327
2328 select max(dunning_id) into l_parent_dunning_id from iex_dunnings where object_id = p_work_item_id; -- added to fix 16433137 SNUTHALA MAR/08/2013
2329 -- ctlee - 5/20 create dunning record
2330 -- l_unique_fulfillment := nvl(fnd_profile.value('IEX_STRY_UNIQUE_FULFILMENT'), 'N');
2331 -- if (l_unique_fulfillment = 'Y') then
2332 l_dunning_rec.delinquency_id := l_delinquency_id;
2333 l_dunning_rec.callback_yn := ''; -- l_callback_flag;
2334 l_dunning_rec.callback_date := ''; -- l_callback_date;
2335 l_dunning_rec.status := 'OPEN';
2336
2337 l_dunning_rec.template_id:= l_template_id;
2338 -- ctlee xdo template id
2339 l_dunning_rec.xml_template_id:= l_xdo_template_id;
2340
2341 l_dunning_rec.object_type:= 'IEX_STRATEGY';
2342 --l_dunning_rec.dunning_method:= 'EMAIL';
2343 l_dunning_rec.object_id:= l_workitem_id;
2344 l_dunning_rec.org_id := v_org_id;
2345 l_dunning_rec.parent_dunning_id := l_parent_dunning_id; -- added to fix 16433137 SNUTHALA MAR/08/2013
2346 -- set dunning_object_id and dunnint_level
2347 begin
2348 select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 40, 'DELINQUENCY', 'DELINQUENCY')
2349 into l_DefaultStrategyLevel
2350 from iex_strategies
2351 where strategy_id = l_strategy_id;
2352
2353 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2354 iex_debug_pub.logmessage('resend_fulfillment Default StrategyLevel ' || l_DefaultStrategyLevel);
2355 END IF;
2356 EXCEPTION
2357 WHEN OTHERS THEN
2358 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2359 iex_debug_pub.logmessage('Strategy Level Rised Exception ');
2360 END IF;
2361 l_DefaultStrategyLevel := 'DELINQUENCY';
2362 END;
2363 IF l_DefaultStrategyLevel = 'CUSTOMER' THEN
2364 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
2365 l_dunning_rec.dunning_object_id:= l_party_id;
2366 l_del_tbl(1).party_cust_id := l_party_id;
2367 l_del_tbl(1).cust_account_id := 0;
2368 l_del_tbl(1).customer_site_use_id := 0;
2369 elsif l_DefaultStrategyLevel = 'ACCOUNT' THEN
2370 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
2371 l_dunning_rec.dunning_object_id:= l_cust_account_id;
2372 l_del_tbl(1).party_cust_id := l_party_id;
2373 l_del_tbl(1).cust_account_id := l_cust_account_id;
2374 l_del_tbl(1).customer_site_use_id := 0;
2375 elsif l_DefaultStrategyLevel = 'BILL_TO' THEN
2376 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
2377 l_dunning_rec.dunning_object_id:= l_customer_site_use_id;
2378 l_del_tbl(1).party_cust_id := l_party_id;
2379 l_del_tbl(1).cust_account_id := l_cust_account_id;
2380 l_del_tbl(1).customer_site_use_id := l_customer_site_use_id;
2381 else
2382 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
2383 l_dunning_rec.dunning_object_id:= l_delinquency_id;
2384 l_del_tbl(1).party_cust_id := l_party_id;
2385 l_del_tbl(1).cust_account_id := l_cust_account_id;
2386 l_del_tbl(1).customer_site_use_id := l_customer_site_use_id;
2387 l_del_tbl(1).delinquency_id := l_delinquency_id;
2388 end if;
2389
2390 begin
2391 select campaign_sched_id into l_campaign_sched_id from iex_delinquencies_all
2392 where delinquency_id = l_delinquency_id;
2393 l_dunning_rec.campaign_sched_id := l_campaign_sched_id;
2394 exception
2395 when others then
2396 l_dunning_rec.campaign_sched_id := null;
2397 end;
2398
2399 -- close all the open dunning record before created
2400 FOR d_rec in c_get_dunning(l_workitem_id)
2401 LOOP
2402 begin
2403 l_dunning_rec_upd_old.dunning_id := d_rec.dunning_id;
2404 l_dunning_rec_upd_old.last_update_date := sysdate;
2405 l_dunning_rec_upd_old.callback_yn := 'N';
2406 l_dunning_rec_upd_old.status := 'SKIP';
2407 -- l_dunning_rec_upd_old.object_type:= 'WORK_ITEM';
2408 -- l_dunning_rec_upd_old.object_id:= l_workitem_id;
2409
2410 IEX_DUNNING_PVT.Update_DUNNING(
2411 p_api_version => 1.0
2412 , p_init_msg_list => FND_API.G_FALSE
2413 , p_commit => FND_API.G_FALSE
2414 , p_dunning_rec => l_dunning_rec_upd_old
2415 , x_return_status => l_return_status
2416 , x_msg_count => l_msg_count
2417 , x_msg_data => l_msg_data
2418 );
2419 exception
2420 when others then
2421 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2422 iex_debug_pub.logmessage ('close dunning before fulfillment exception');
2423 END IF;
2424 end;
2425 END LOOP;
2426
2427 -- ctlee - 6/18 fulfillment printer method available
2428 -- IF PG_DEBUG < 10 THEN
2429 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2430 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, before create dunning ' );
2431 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' WorkItem Id = '||l_workitem_id );
2432 END IF;
2433 IEX_DUNNING_PVT.CREATE_DUNNING(
2434 p_api_version => 1.0
2435 , p_init_msg_list => FND_API.G_FALSE
2436 , p_commit => FND_API.G_FALSE
2437 , p_dunning_rec => l_dunning_rec
2438 , x_dunning_id => l_dunning_id
2439 , x_return_status => l_return_status
2440 , x_msg_count => l_msg_count
2441 , x_msg_data => l_msg_data);
2442
2443 -- Added call IEX_DUNNING_PVT.INSERT_DUNNING_TRANSACTION to fix 14256867
2444 IEX_DUNNING_PVT.INSERT_DUNNING_TRANSACTION(
2445 p_api_version => 1.0
2446 , p_init_msg_list => FND_API.G_FALSE
2447 , p_commit => FND_API.G_FALSE
2448 , p_delinquencies_tbl => l_del_tbl
2449 , p_ag_dn_xref_id => 0
2450 , p_dunning_id => l_dunning_id
2451 , p_correspondence_date => sysdate
2452 , p_running_level => l_DefaultStrategyLevel
2453 , p_grace_days => 0
2454 , p_include_dispute_items => l_include_disputed_items -- 'Y' bug 14772139
2455 , x_return_status => l_return_status
2456 , x_msg_count => l_msg_count
2457 , x_msg_data => l_msg_data
2458 , p_workitem_id => l_workitem_id); -- bug 14772139
2459
2460
2461 l_count := l_count +1;
2462 l_fulfillment_bind_tbl(l_count).key_name := 'DUNNING_ID';
2463 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
2464 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_dunning_id);
2465
2466 -- end if;
2467
2468 --IF PG_DEBUG < 10 THEN
2469 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2470 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, before send_fulfillment ' );
2471 END IF;
2472
2473
2474 -- call fulfilment function with multiple bind variables.
2475 if (l_curr_dmethod = 'FFM') then
2476 iex_dunning_pvt.send_fulfillment(
2477 p_api_version => 1.0,
2478 p_init_msg_list => FND_API.G_TRUE,
2479 p_commit => FND_API.G_TRUE,
2480 p_FULFILLMENT_BIND_TBL => l_fulfillment_bind_tbl,
2481 p_template_id => l_template_id,
2482 p_method => l_dunning_method,
2483 p_party_id => l_party_id,
2484 p_user_id => l_user_id ,
2485 x_return_status => l_return_status,
2486 x_msg_count => l_msg_count,
2487 x_msg_data => l_msg_data,
2488 x_REQUEST_ID => l_request_id,
2489 x_contact_destination => l_contact_destination, -- bug 3955222
2490 x_contact_party_id => l_contact_party_id); -- bug 3955222
2491 else
2492 -- for now, resend to 'N' without request_id; to send the old one 'Y' with request id
2493 iex_dunning_pvt.send_xml(
2494 p_api_version => 1.0,
2495 p_init_msg_list => FND_API.G_TRUE,
2496 p_commit => FND_API.G_TRUE,
2497 p_resend => 'N',
2498 p_request_id => null,
2499 p_FULFILLMENT_BIND_TBL => l_fulfillment_bind_tbl,
2500 p_template_id => l_xdo_template_id,
2501 p_method => l_dunning_method,
2502 p_user_id => l_user_id,
2503 p_email => null,
2504 p_party_id => l_party_id,
2505 p_level => l_dunning_rec.dunning_level, -- strategy level
2506 p_source_id => l_dunning_rec.dunning_object_id, -- changed by gnramasa bug 5661324 14-Mar-07
2507 p_object_code => l_dunning_rec.object_type, -- 'IEX_STRATEGY'
2508 p_object_id => l_workitem_id, -- changed for bug#8403051 by PNAVEENK on 3-4-2009 l_dunning_rec.dunning_object_id, -- party/account/billto/del id
2509 p_resource_id => l_assign_resource_id, --Added for bug 7502980 05-Jan-2009 barathsr
2510 p_org_id => l_dunning_rec.org_id, -- added for bug 9151851
2511 x_return_status => l_return_status,
2512 x_msg_count => l_msg_count,
2513 x_msg_data => l_msg_data,
2514 x_REQUEST_ID => l_request_id,
2515 x_contact_destination => l_contact_destination, -- bug 3955222
2516 x_contact_party_id => l_contact_party_id); -- bug 3955222
2517 end if;
2518 --- share a request id between xdo and ffm
2519
2520
2521 -- IEX_DEBUG_PUB.setDebugFileDir(P_FILEDIR => '/sqlcom/log', P_FILENAME =>'james.IEX');
2522 --IEX_DEBUG_PUB.setDebugFileDir(P_FILEDIR => '/sqlcom/log');
2523
2524
2525 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2526 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, after send_fulfillment ' );
2527 END IF;
2528
2529 -- return to workflow
2530 if (l_request_id is null OR l_return_status <> 'S') then
2531 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2532 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'request_id =>'|| l_request_id);
2533 END IF;
2534 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2535 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'return_status =>'|| l_return_status);
2536 END IF;
2537 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2538 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'msg_count =>' || l_msg_count);
2539 END IF;
2540
2541 -- IF PG_DEBUG < 10 THEN
2542 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2543 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'msg_data =>');
2544 END IF;
2545 FOR l_index IN 1..l_msg_count LOOP
2546 my_message := FND_MSG_PUB.Get(p_msg_index => l_index,
2547 p_encoded => 'F');
2548 -- IF PG_DEBUG < 10 THEN
2549 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2550 iex_debug_pub.logmessage ('resend_fulfillment: ' || my_message);
2551 END IF;
2552 END LOOP;
2553 x_error_message := my_message;
2554 else
2555 -- IF PG_DEBUG < 10 THEN
2556 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2557 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'request_id =>'|| l_request_id);
2558 END IF;
2559 -- IF PG_DEBUG < 10 THEN
2560 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2561 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'return_status =>'|| l_return_status);
2562 END IF;
2563 -- IF PG_DEBUG < 10 THEN
2564 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2565 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'msg_count =>' || l_msg_count);
2566 END IF;
2567
2568 x_status := 'S';
2569 x_request_id := l_request_id;
2570
2571 l_dunning_rec_upd.dunning_id := l_dunning_id;
2572 l_dunning_rec_upd.last_update_date := sysdate;
2573 l_dunning_rec_upd.callback_yn := 'N';
2574 l_dunning_rec_upd.status := 'CLOSE';
2575 -- ctlee xdo template id
2576 if (l_curr_dmethod = 'FFM') then
2577 l_dunning_rec_upd.ffm_request_id := l_request_id;
2578 else
2579 l_dunning_rec_upd.xml_request_id := l_request_id;
2580 end if;
2581 l_dunning_rec_upd.contact_destination := l_contact_destination; -- bug 3955222
2582 l_dunning_rec_upd.contact_party_id := l_contact_party_id; -- bug 3955222
2583
2584 IEX_DUNNING_PVT.Update_DUNNING(
2585 p_api_version => 1.0
2586 , p_init_msg_list => FND_API.G_FALSE
2587 , p_commit => FND_API.G_TRUE
2588 , p_dunning_rec => l_dunning_rec_upd
2589 , x_return_status => l_return_status
2590 , x_msg_count => l_msg_count
2591 , x_msg_data => l_msg_data
2592 );
2593 end if;
2594 COMMIT work;
2595 return;
2596 EXCEPTION WHEN OTHERS THEN
2597 null;
2598 END resend_fulfillment;
2599
2600 --Start schekuri Bug#4506922 Date:02-Dec-2005
2601 --added for the function WAIT_ON_HOLD_SIGNAL in workflow IEXSTFFM
2602 procedure wait_on_hold_signal(
2603 itemtype in varchar2,
2604 itemkey in varchar2,
2605 actid in number,
2606 funcmode in varchar2,
2607 result out nocopy varchar2) IS
2608
2609 l_work_item_temp_id NUMBER;
2610 l_result VARCHAR2(1);
2611 l_value VARCHAR2(300);
2612
2613 BEGIN
2614 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2615 iex_debug_pub.logmessage ('**** START wait_on_hold_signal ************');
2616 END IF;
2617 if funcmode <> wf_engine.eng_run then
2618 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2619 iex_debug_pub.logmessage('SECOND TIME FUNCMODE' ||funcmode);
2620 END IF;
2621 result := wf_engine.eng_null;
2622 return;
2623 end if;
2624
2625
2626
2627 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2628 iex_debug_pub.logmessage('FUNCMODE' ||funcmode);
2629 END IF;
2630 /* l_value :=wf_engine.GetActivityLabel(actid);
2631 wf_engine.SetItemAttrText(itemtype => itemtype,
2632 itemkey => itemkey,
2633 aname => 'ACTIVITY_NAME',
2634 avalue => l_value);
2635 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2636 iex_debug_pub.logMessage('ACTIVITYNAME' ||l_value);
2637 END IF;*/
2638
2639
2640 result := wf_engine.eng_notified||':'||wf_engine.eng_null||
2641 ':'||wf_engine.eng_null;
2642 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2643 iex_debug_pub.logmessage ('**** END wait_on_hold_signal ************');
2644 END IF;
2645 exception
2646 when others then
2647 result := wf_engine.eng_completed ||':'||wf_no;
2648 wf_core.context('IEX_STRATEGY_WORK_PUB','wait_on_hold_signal',itemtype,
2649 itemkey,to_char(actid),funcmode);
2650 raise;
2651
2652 END wait_on_hold_signal;
2653
2654 --end schekuri Bug#4506922 Date:02-Dec-2005
2655
2656
2657 procedure wait_delivery_signal(
2658 itemtype in varchar2,
2659 itemkey in varchar2,
2660 actid in number,
2661 funcmode in varchar2,
2662 result out nocopy varchar2) IS
2663
2664 l_work_item_temp_id NUMBER;
2665 l_result VARCHAR2(1);
2666 l_value VARCHAR2(300);
2667
2668 BEGIN
2669 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2670 iex_debug_pub.logmessage ('**** START wait_delivery_signal ************');
2671 END IF;
2672 if funcmode <> wf_engine.eng_run then
2673 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2674 iex_debug_pub.logmessage('SECOND TIME FUNCMODE' ||funcmode);
2675 END IF;
2676 result := wf_engine.eng_null;
2677 return;
2678 end if;
2679
2680
2681
2682 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2683 iex_debug_pub.logmessage('FUNCMODE' ||funcmode);
2684 END IF;
2685 l_value :=wf_engine.GetActivityLabel(actid);
2686 wf_engine.SetItemAttrText(itemtype => itemtype,
2687 itemkey => itemkey,
2688 aname => 'ACTIVITY_NAME',
2689 avalue => l_value);
2690 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2691 iex_debug_pub.logMessage('ACTIVITYNAME' ||l_value);
2692 END IF;
2693
2694
2695 result := wf_engine.eng_notified||':'||wf_engine.eng_null||
2696 ':'||wf_engine.eng_null;
2697 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2698 iex_debug_pub.logmessage ('**** END wait_delivery_signal ************');
2699 END IF;
2700 exception
2701 when others then
2702 result := wf_engine.eng_completed ||':'||wf_no;
2703 wf_core.context('IEX_STRATEGY_WORK_PUB','wait_delivery_signal',itemtype,
2704 itemkey,to_char(actid),funcmode);
2705 raise;
2706
2707 END wait_delivery_signal;
2708
2709 procedure cal_delivery_wait(
2710 itemtype in varchar2,
2711 itemkey in varchar2,
2712 actid in number,
2713 funcmode in varchar2,
2714 result out nocopy varchar2) IS
2715
2716 l_schedule date;
2717 l_wait_days number;
2718 l_return VARCHAR2(1);
2719 l_value VARCHAR2(300);
2720 l_workitem_status varchar2(300);
2721
2722 BEGIN
2723 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2724 iex_debug_pub.logmessage ('**** START cal_pre_wait ************');
2725 END IF;
2726 if funcmode <> 'RUN' then
2727 result := wf_engine.eng_null;
2728 return;
2729 end if;
2730
2731 l_wait_days:=nvl(fnd_profile.value('IEX_DELIVERY_WAIT_DAYS'),0);
2732
2733 l_workitem_status := wf_engine.GetItemAttrText(Itemtype => itemtype,
2734 Itemkey => itemkey,
2735 aname => 'WK_STATUS');
2736
2737 l_value :=wf_engine.GetActivityLabel(actid);
2738 wf_engine.SetItemAttrText(itemtype => itemtype,
2739 itemkey => itemkey,
2740 aname => 'ACTIVITY_NAME',
2741 avalue => l_value);
2742
2743 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2744 iex_debug_pub.logMessage('Number of days to wait after submitting delivery request = ' ||l_wait_days);
2745 END IF;
2746
2747 if (l_wait_days = 0 or l_workitem_status = 'SKIP') then
2748 l_return := wf_no;
2749 else
2750 l_schedule:= sysdate+l_wait_days;
2751 wf_engine.SetItemAttrDate(itemtype => itemtype,
2752 itemkey => itemkey,
2753 aname => 'DELIVERY_WAIT_TIME',
2754 avalue => l_schedule);
2755 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2756 iex_debug_pub.logMessage('Letter Delivery wait time = ' || to_char(l_schedule, 'hh24:mi:ss mm/dd/yyyy'));
2757 END IF;
2758 l_return := wf_yes;
2759 END IF;
2760
2761
2762 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2763 iex_debug_pub.logMessage('Collections cal_delivery_wait result = ' ||l_return);
2764 END IF;
2765
2766 result := wf_engine.eng_completed ||':'||l_return;
2767
2768 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2769 iex_debug_pub.logmessage ('Collections **** END cal_delivery_wait ************');
2770 END IF;
2771 exception
2772 when others then
2773 result := wf_engine.eng_completed ||':'||wf_no;
2774 wf_core.context('IEX_STRATEGY_WORK_PUB','cal_delivery_wait',itemtype,
2775 itemkey,to_char(actid),funcmode);
2776 raise;
2777
2778
2779
2780 END cal_delivery_wait;
2781
2782 procedure send_delivery_signal(
2783 p_xml_request_id IN NUMBER,
2784 p_status IN varchar2,
2785 x_error_message out NOCOPY varchar2) is
2786 l_activity_label varchar2(500);
2787 l_work_item_id number;
2788 l_return_status VARCHAR2(20);
2789 l_msg_count NUMBER;
2790 l_msg_data VARCHAR2(2000);
2791
2792 begin
2793 iex_debug_pub.logmessage('Collections **** BEGIN send_delivery_signal ************');
2794
2795 IF NVL(fnd_profile.value('IEX_DELIVERY_WAIT_DAYS'),0)=0 THEN
2796 iex_debug_pub.logmessage('Collections **** send_delivery_signal : Value of profile IEX: Workitem waiting time in days to get delivery status is 0 ************');
2797 return;
2798 END IF;
2799
2800 BEGIN
2801 select dun.object_id
2802 into l_work_item_id
2803 from iex_dunnings dun,
2804 iex_strategy_work_items wi
2805 where dun.object_id=wi.work_item_id
2806 and dun.object_type='IEX_STRATEGY'
2807 and wi.status_code in ('OPEN','INERROR_CHECK_NOTIFY')
2808 and dun.xml_request_id=p_xml_request_id;
2809 l_activity_label := wf_engine.GetItemAttrText(itemtype => 'IEXSTFFM',
2810 itemkey => l_work_item_id,
2811 aname => 'ACTIVITY_NAME');
2812 EXCEPTION
2813 WHEN NO_DATA_FOUND THEN
2814 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.SEND_DELIVERY_SIGNAL:Unable to find work item id corresponding to xml request id '||p_xml_request_id);
2815 RETURN;
2816 WHEN OTHERS THEN
2817 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.SEND_DELIVERY_SIGNAL:Exception'|| SQLERRM ||' while finding work item id corresponding to xml request id '||p_xml_request_id);
2818 RETURN;
2819 END;
2820 iex_debug_pub.logmessage('Activity label='||l_activity_label);
2821 IF l_activity_label = 'IEXSTFFM:WAIT_DELIVERY_SIGNAL' and l_work_item_id is not null then
2822 IF p_status = 'SUCCESS' THEN
2823 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.SEND_DELIVERY_SIGNAL:Sending Success Signal');
2824 wf_engine.CompleteActivity(itemtype => 'IEXSTFFM',
2825 itemkey => l_work_item_id,
2826 activity =>l_activity_label,
2827 result =>'#DEFAULT');
2828 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.SEND_DELIVERY_SIGNAL:Sent Success Signal');
2829 ELSE
2830 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.SEND_DELIVERY_SIGNAL:Sending Failure Signal');
2831 wf_engine.SetItemAttrText(itemtype => 'IEXSTFFM',
2832 itemkey =>l_work_item_id,
2833 aname => 'WK_STATUS',
2834 avalue => 'INERROR');
2835
2836 iex_stry_utl_pub.update_work_item(
2837 p_api_version => 1.0,
2838 p_commit => FND_API.G_TRUE,
2839 p_init_msg_list => FND_API.G_TRUE,
2840 p_work_item_id => l_work_item_id,
2841 p_status => 'INERROR_CHECK_NOTIFY',
2842 x_return_status => l_return_status,
2843 x_msg_count => l_msg_count,
2844 x_msg_data => l_msg_data
2845 );
2846
2847 wf_engine.CompleteActivity(itemtype => 'IEXSTFFM',
2848 itemkey => l_work_item_id,
2849 activity =>l_activity_label,
2850 result =>'#TIMEOUT');
2851 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.SEND_DELIVERY_SIGNAL:Sent Failure Signal');
2852 END IF;
2853 END IF;
2854 COMMIT;
2855 iex_debug_pub.logmessage('Collections **** END send_delivery_signal ************');
2856 EXCEPTION
2857 WHEN OTHERS THEN
2858 x_error_message:='Exception:'||SQLERRM;
2859 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.SEND_DELIVERY_SIGNAL:::Exception:::'||SQLERRM);
2860 END SEND_DELIVERY_SIGNAL;
2861
2862
2863 PROCEDURE auto_retry_notifications(p_from_date in date,
2864 x_error_message out NOCOPY varchar2) IS
2865 cursor c_error_workitems(l_from_date date) is
2866 select wi.work_item_id work_item_id
2867 from iex_dunnings dun,
2868 iex_strategy_work_items wi,
2869 iex_xml_request_histories xrh
2870 where dun.object_id=wi.work_item_id
2871 and xrh.object_type='IEX_STRATEGY'
2872 and xrh.creation_date>=nvl(l_from_date,xrh.creation_date)
2873 and dun.xml_request_id=xrh.xml_request_id
2874 and xrh.status not in ('SUCCESSFUL','SUCCESSFUL WITH WARNINGS','OPEN','CANCELLED')
2875 and wi.status_code in ('INERROR_CHECK_NOTIFY');
2876
2877 cursor c_notification(p_context varchar2) is
2878 select notification_id from wf_notifications
2879 where message_type='IEXSTFFM'
2880 and MESSAGE_NAME='SEND FAILER MESSAGE'
2881 and status='OPEN'
2882 AND context like p_context; -- 'IEXSTFFM:14515%' ;
2883
2884 BEGIN
2885 iex_debug_pub.logmessage('Collections **** BEGIN auto_retry_notifications ************');
2886
2887 IF NVL(fnd_profile.value('IEX_DELIVERY_WAIT_DAYS'),0)=0 THEN
2888 iex_debug_pub.logmessage('Collections **** auto_retry_notifications : Value of profile IEX: Workitem waiting time in days to get delivery status is 0 ************');
2889 return;
2890 END IF;
2891
2892 for rec_error_wi in c_error_workitems(p_from_date) loop
2893 iex_debug_pub.logmessage('Collections **** Before retrying notifications for work item :'||rec_error_wi.work_item_id||'************');
2894 for rec_notif in c_notification('IEXSTFFM:'||rec_error_wi.work_item_id||':%') loop
2895 begin
2896 wf_notification.setattrtext ( nid => rec_notif.notification_id
2897 , aname => 'RESULT'
2898 , avalue => 'RETRY' );
2899
2900 WF_NOTIFICATION.respond(nid =>rec_notif.notification_id,
2901 respond_comment=>'Response sent by IEX: Bulk XML Delivery Manager cp');
2902 exception
2903 when others then
2904 iex_debug_pub.logmessage('Collections **** Erro auto retrying notifications with id :'||rec_notif.notification_id||'************');
2905 end;
2906 end loop;
2907 iex_debug_pub.logmessage('Collections **** After retrying notifications for work item :'||rec_error_wi.work_item_id||'************');
2908 commit;
2909 end loop;
2910
2911 iex_debug_pub.logmessage('Collections **** END auto_retry_notifications ************');
2912 EXCEPTION
2913 WHEN OTHERS THEN
2914 x_error_message:='Exception:'||SQLERRM;
2915 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.auto_retry_notifications:::Exception:::'||SQLERRM);
2916 END auto_retry_notifications;
2917
2918 procedure delivery_failed(
2919 itemtype in varchar2,
2920 itemkey in varchar2,
2921 actid in number,
2922 funcmode in varchar2,
2923 result out nocopy varchar2) is
2924 l_work_item_id number;
2925 l_return_status VARCHAR2(20);
2926 l_msg_count NUMBER;
2927 l_msg_data VARCHAR2(2000);
2928 l_failure_reason varchar2(2000);
2929
2930 BEGIN
2931 if funcmode <> 'RUN' then
2932 result := wf_engine.eng_null;
2933 return;
2934 end if;
2935
2936 l_work_item_id := wf_engine.GetItemAttrNumber(
2937 itemtype => itemtype,
2938 itemkey => itemkey,
2939 aname => 'WORKITEM_ID');
2940 if l_work_item_id is not null then
2941 begin
2942 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.DELIVERY_FAILED:updating status of xml request for work item '||l_work_item_id);
2943 update iex_xml_request_histories
2944 set status='OTHER PROCESSING FAILURE',
2945 failure_reason='Request status timed out'
2946 where object_type='IEX_STRATEGY'
2947 --and status<>'CANCELLED'
2948 and status in ('IN PROCESS','XMLDATA','XMLDOC')
2949 and xml_request_id in (select xml_request_id
2950 from iex_dunnings
2951 where object_type='IEX_STRATEGY'
2952 and object_id=l_work_item_id);
2953
2954 wf_engine.SetItemAttrText(itemtype => 'IEXSTFFM',
2955 itemkey =>l_work_item_id,
2956 aname => 'WK_STATUS',
2957 avalue => 'INERROR');
2958
2959 iex_stry_utl_pub.update_work_item(
2960 p_api_version => 1.0,
2961 p_commit => FND_API.G_TRUE,
2962 p_init_msg_list => FND_API.G_TRUE,
2963 p_work_item_id => l_work_item_id,
2964 p_status => 'INERROR_CHECK_NOTIFY',
2965 x_return_status => l_return_status,
2966 x_msg_count => l_msg_count,
2967 x_msg_data => l_msg_data
2968 );
2969
2970 result := wf_engine.eng_completed;
2971
2972 -- Start for the bug#8435665 by PNAVEENK on 15-May-2009
2973 select failure_reason into l_failure_reason from iex_xml_request_histories where xml_request_id = (select max(xml_request_id) from
2974 iex_dunnings
2975 where object_type='IEX_STRATEGY'
2976 and object_id=l_work_item_id)
2977 and failure_reason is not null;
2978 if l_failure_reason is not null then
2979
2980 wf_engine.SetItemAttrText(itemtype => 'IEXSTFFM',
2981 itemkey =>l_work_item_id,
2982 aname => 'FAILURE_REASON',
2983 avalue => l_failure_reason);
2984 end if;
2985 -- End for the bug#8435665
2986
2987 exception
2988 when others then
2989 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.DELIVERY_FAILED:exception while updating status of xml request'||SQLERRM);
2990 end;
2991
2992 end if;
2993 EXCEPTION
2994 WHEN OTHERS THEN
2995 iex_debug_pub.logmessage('IEX_STRATEGY_WORK_PUB.DELIVERY_FAILED:exception'||SQLERRM);
2996 result := wf_engine.eng_completed ||':'||NULL;
2997 wf_core.context('IEX_STRATEGY_WORK_PUB','delivery_failed',itemtype,
2998 itemkey,to_char(actid),funcmode);
2999 raise;
3000 END delivery_failed;
3001
3002
3003
3004 begin
3005 -- initialize variables
3006 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3007
3008 --begin schekuri Bug#4506922 Date:02-Dec-2005
3009 wf_yes := 'Y';
3010 wf_no := 'N';
3011 --end schekuri Bug#4506922 Date:02-Dec-2005
3012
3013 end IEX_STRATEGY_WORK_PUB;