[Home] [Help]
PACKAGE BODY: APPS.IEX_STRATEGY_WORK_PUB
Source
1 package body IEX_STRATEGY_WORK_PUB as
2 --$Header: iexpstmb.pls 120.19.12010000.1 2008/07/29 10:03:18 appldev 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
50 --jsanju 04/09 -- fulfillment resource id
51 l_resource_id NUMBER;
52
53 cursor c_getuserid(l_resource_id NUMBER) is
54 select user_id from jtf_rs_resource_extns
55 where resource_id =l_resource_id;
56
57 l_user_id NUMBER;
58 -- ctlee, add for create dunning
59 l_unique_fulfillment VARCHAR2(1);
60 -- l_delinquency_id NUMBER;
61 l_callback_flag VARCHAR2(1);
62 l_callback_date DATE;
63 -- l_template_id NUMBER;
64 l_campaign_sched_id NUMBER;
65 l_DUNNING_rec IEX_DUNNING_PUB.DUNNING_REC_TYPE;
66 l_DUNNING_id NUMBER;
67 l_DUNNING_rec_upd IEX_DUNNING_PUB.DUNNING_REC_TYPE;
68 l_dunning_method varchar2(2000);
69
70 l_DUNNING_rec_upd_old IEX_DUNNING_PUB.DUNNING_REC_TYPE;
71 cursor c_get_dunning(p_workitem_id number) is
72 select a.dunning_id from iex_dunnings a
73 where a.object_id = p_workitem_id
74 and a.object_type = 'IEX_STRATEGY'
75 and a.status <> 'CLOSE';
76
77 l_DefaultStrategyLevel varchar2(20);
78 l_org_id NUMBER ;
79
80 -- xdo check
81 l_curr_dmethod varchar2(10);
82 l_assign_resource_id number;
83 cursor c_get_assign_resource(l_strategy_id number, l_workitem_id number) is
84 -- begin bug 4930376 ctlee - performance 01/09/2006 -- sql id 14771818
85 SELECT
86 wkitem.resource_id ASSIGNED_TO
87 from
88 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
89 , wf_item_types_tl item, jtf_rs_resource_extns res
90 WHERE
91 wkitem.work_item_template_id = stry_temp_wkitem_b.work_item_temp_id
92 and stry_temp_wkitem_b.work_item_temp_id =stry_temp_wkitem_tl.work_item_temp_id
93 and stry_temp_wkitem_tl.LANGUAGE = userenv('LANG')
94 and stry_temp_wkitem_b.WORKFLOW_ITEM_TYPE = item.name(+)
95 and item.language(+) = userenv('LANG')
96 and wkitem.resource_id = res.resource_id(+)
97 and wkitem.strategy_id = l_strategy_id
98 and wkitem.work_item_id = l_workitem_id;
99 -- select a.assigned_to from iex_work_item_bali_v a
100 -- where a.strategy_id = l_strategy_id
101 -- and a.wkitem_id = l_workitem_id;
102 -- end bug 4930376 ctlee - performance 01/09/2006
103
104 l_contact_destination varchar2(240); -- bug 3955222
105 l_contact_party_id number; -- bug 3955222
106 Begin
107 -- initialize variables
108 l_resource_id := fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE');
109 --Bug#4679639 schekuri 20-OCT-2005
110 --Value of profile ORG_ID should not be used for getting org_id
111 --l_org_id := fnd_profile.value('ORG_ID');
112 l_org_id := mo_global.get_current_org_id;
113
114
115 -- IF PG_DEBUG < 10 THEN
116 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
117 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, funcmode = ' || funcmode);
118 END IF;
119
120 if funcmode <> 'RUN' then
121 result := wf_engine.eng_null;
122 return;
123 end if;
124
125 -- IF PG_DEBUG < 10 THEN
126 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
127 iex_debug_pub.logmessage ('send_mail: ' || 'itemtype = ' || itemtype);
128 END IF;
129 -- IF PG_DEBUG < 10 THEN
130 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
131 iex_debug_pub.logmessage ('send_mail: ' || 'itemtkey = ' || itemkey);
132 END IF;
133 l_party_id := wf_engine.GetItemAttrNumber(
134 itemtype => itemtype,
135 itemkey => itemkey,
136 aname => 'PARTY_ID');
137
138 if (l_party_id <> 0) then
139
140 -- IF PG_DEBUG < 10 THEN
141 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
142 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, after PARTY_ID ='|| l_PARTY_ID );
143 END IF;
144
145 l_count := l_count +1;
146 l_fulfillment_bind_tbl(l_count).key_name := 'party_id';
147 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
148 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_party_id);
149 else -- party_id could not be null
150 result := 'COMPLETE:'||'N';
151 return;
152 end if;
153 l_strategy_id := wf_engine.GetItemAttrNumber(
154 itemtype => itemtype,
155 itemkey => itemkey,
156 aname => 'STRATEGY_ID');
157 if (l_strategy_id <> 0) then
158 -- IF PG_DEBUG < 10 THEN
159 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
160 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, strategy_id ' );
161 END IF;
162 l_count := l_count +1;
163 l_fulfillment_bind_tbl(l_count).key_name := 'strategy_id';
164 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
165 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_strategy_id);
166 end if;
167
168 l_delinquency_id := wf_engine.GetItemAttrNumber(
169 itemtype => itemtype,
170 itemkey => itemkey,
171 aname => 'DELINQUENCY_ID');
172 if (l_delinquency_id <> 0) then
173 -- IF PG_DEBUG < 10 THEN
174 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
175 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, delinquency_id ' );
176 END IF;
177 l_count := l_count +1;
178 l_fulfillment_bind_tbl(l_count).key_name := 'delinquency_id';
179 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
180 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_delinquency_id);
181 end if;
182
183 l_cust_account_id := wf_engine.GetItemAttrNumber(
184 itemtype => itemtype,
185 itemkey => itemkey,
186 aname => 'CUST_ACCOUNT_ID');
187 if (l_cust_account_id <> 0) then
188 -- IF PG_DEBUG < 10 THEN
189 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
190 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, cust_account_id ' );
191 END IF;
192 l_count := l_count +1;
193 l_fulfillment_bind_tbl(l_count).key_name := 'cust_account_id';
194 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
195 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_cust_account_id);
196
197 -- ctlee for xdo template using ACCOUNT_ID matching the query
198 l_count := l_count +1;
199 l_fulfillment_bind_tbl(l_count).key_name := 'account_id';
200 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
201 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_cust_account_id);
202
203 end if;
204
205
206 l_overdue_amount := wf_engine.GetItemAttrNumber(
207 itemtype => itemtype,
208 itemkey => itemkey,
209 aname => 'OVERDUE_AMOUNT');
210 if (l_overdue_amount <> 0) then
211 -- IF PG_DEBUG < 10 THEN
212 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
213 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, overdue_amount ');
214 END IF;
215 l_count := l_count +1;
216 l_fulfillment_bind_tbl(l_count).key_name := 'overdue_amount';
217 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
218 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_overdue_amount);
219 end if;
220
221
222 /* pass org_id instead
223 l_delinquency_status := wf_engine.GetItemAttrText(
224 itemtype => itemtype,
225 itemkey => itemkey,
226 aname => 'DELINQUENCY_STATUS');
227 if (l_delinquency_status is not null) then
228 -- IF PG_DEBUG < 10 THEN
229 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
230 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, delinquency_status ' );
231 END IF;
232 l_count := l_count +1;
233 l_fulfillment_bind_tbl(l_count).key_name := 'delinquency_status';
234 l_fulfillment_bind_tbl(l_count).key_type := 'VARCHAR2';
235 l_fulfillment_bind_tbl(l_count).key_value := l_delinquency_status;
236 end if;
237 */
238
239 if (l_org_id is not null) then
240 -- IF PG_DEBUG < 10 THEN
241 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
242 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, org_id ' );
243 END IF;
244 l_count := l_count +1;
245 l_fulfillment_bind_tbl(l_count).key_name := 'org_id';
246 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
247 l_fulfillment_bind_tbl(l_count).key_value := l_org_id;
248 end if;
249
250 l_aging_bucket_line_id := wf_engine.GetItemAttrNumber(
251 itemtype => itemtype,
252 itemkey => itemkey,
253 aname => 'AGING_BUCKET_LINE_ID');
254 /* not pass the l_aging_bucket_line_id - pass customer_site_use_id instead
255 if (l_aging_bucket_line_id <> 0) then
256 --IF PG_DEBUG < 10 THEN
257 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
258 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, aging_bucket_line_id ' );
259 END IF;
260 l_count := l_count +1;
261 l_fulfillment_bind_tbl(l_count).key_name := 'aging_bucket_line_id';
262 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
263 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_aging_bucket_line_id);
264 end if;
265 */
266
267 l_customer_site_use_id := wf_engine.GetItemAttrNumber(
268 itemtype => itemtype,
269 itemkey => itemkey,
270 aname => 'CUSTOMER_SITE_USE_ID');
271 if (l_customer_site_use_id <> 0) then
272 -- IF PG_DEBUG < 10 THEN
273 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
274 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, customer_site_use_id ' );
275 END IF;
276 l_count := l_count +1;
277 l_fulfillment_bind_tbl(l_count).key_name := 'customer_site_use_id';
278 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
279 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_customer_site_use_id);
280
281 end if;
282
283 l_payment_schedule_id := wf_engine.GetItemAttrNumber(
284 itemtype => itemtype,
285 itemkey => itemkey,
286 aname => 'PAYMENT_SCHEDULE_ID');
287 if (l_payment_schedule_id <> 0) then
288 -- IF PG_DEBUG < 10 THEN
289 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
290 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, payment_schedule_id ');
291 END IF;
292 l_count := l_count +1;
293 l_fulfillment_bind_tbl(l_count).key_name := 'payment_schedule_id';
294 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
295 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_payment_schedule_id);
296 end if;
297
298 l_template_id := wf_engine.GetItemAttrNumber(
299 itemtype => itemtype,
300 itemkey => itemkey,
301 aname => 'TEMPLATE_ID');
302 l_xdo_template_id := wf_engine.GetItemAttrNumber(
303 itemtype => itemtype,
304 itemkey => itemkey,
305 aname => 'XDO_TEMPLATE_ID');
306 if (l_template_id <> 0) then
307 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
308 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, template_id ' );
309 END IF;
310 end if;
311
312 if (l_xdo_template_id <> 0) then
313 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
314 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, xdo_template_id ' );
315 END IF;
316 end if;
317 /*
318 l_count := l_count +1;
319 l_fulfillment_bind_tbl(l_count).key_name := 'template_id';
320 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
321 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_template_id);
322 else -- template_id could not be null
323 result := 'COMPLETE:'||'N';
324 return;
325 end if;
326 */
327
328
329
330
331 l_workitem_id := wf_engine.GetItemAttrNumber(
332 itemtype => itemtype,
333 itemkey => itemkey,
334 aname => 'WORKITEM_ID');
335 if (l_workitem_id <> 0) then
336 -- IF PG_DEBUG < 10 THEN
337 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
338 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, workitem_id ' );
339 END IF;
340 /*
341 l_count := l_count +1;
342 l_fulfillment_bind_tbl(l_count).key_name := 'workitem_id';
343 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
344 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_workitem_id);
345 */
346 else -- workitem_id could not be null
347 result := 'COMPLETE:'||'N';
348 return;
349 end if;
350
351 -- IF PG_DEBUG < 10 THEN
352 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
353 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, l_count ='|| l_count );
354 END IF;
355
356 -- ctlee - check the hz_customer_profiles.dunning_letter
357 if ( iex_utilities.DunningProfileCheck (
358 p_party_id => l_party_id
359 , p_cust_account_id => l_cust_account_id
360 , p_site_use_id => l_customer_site_use_id
361 , p_delinquency_id => l_delinquency_id ) = 'N'
362 ) then
363 result := 'COMPLETE:' || 'Y'; -- Bug #6679939 bibeura 11-Dec-2007 Changed from 'N' to 'Y'
364 begin
365 -- Bug #6679939 bibeura 11-Dec-2007 Value for parameter "avalue" is changed in the following calls
366 wf_engine.SetItemAttrText(itemtype => itemtype,
367 itemkey => itemkey,
368 aname => 'ERROR_MESSAGE',
369 avalue => 'The customer is excluded from dunning in the customer profile');
370 wf_engine.SetItemAttrText(itemtype => itemtype,
371 itemkey => itemkey,
372 aname => 'RETURN_STATUS',
373 avalue => 'S');
374 wf_engine.SetItemAttrText(itemtype => itemtype,
375 itemkey => itemkey,
376 aname => 'WK_STATUS',
377 avalue => 'SKIP');
378 EXCEPTION
379 WHEN OTHERS THEN
380 NULL;
381 END;
382 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
383 iex_debug_pub.logmessage ('send_mail: ' || ' check fail dunning profile check ' );
384 END IF;
385 return;
386 end if;
387
388
389 -- ctlee - check the hz_customer_profiles_amt min_dunning_invoice_amount and min_dunning_amount
390 if ( iex_utilities.DunningMinAmountCheck (
391 p_cust_account_id => l_cust_account_id
392 , p_site_use_id => l_customer_site_use_id) = 'N'
393 ) then
394 result := 'COMPLETE:' || 'Y'; -- Bug #6679939 bibeura 11-Dec-2007 Changed from 'N' to 'Y'
395 begin
396 -- Bug #6679939 bibeura 11-Dec-2007 Value for parameter "avalue" is changed in the following calls
397 wf_engine.SetItemAttrText(itemtype => itemtype,
398 itemkey => itemkey,
399 aname => 'ERROR_MESSAGE',
400 avalue => 'The dunning amount does not exceed the minimum dunning amount in the customer profile');
401 wf_engine.SetItemAttrText(itemtype => itemtype,
402 itemkey => itemkey,
403 aname => 'RETURN_STATUS',
404 avalue => 'S');
405 wf_engine.SetItemAttrText(itemtype => itemtype,
406 itemkey => itemkey,
407 aname => 'WK_STATUS',
408 avalue => 'SKIP');
409 EXCEPTION
410 WHEN OTHERS THEN
411 NULL;
412 END;
413 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
414 iex_debug_pub.logmessage ('send_mail: ' || ' check fail dunning min amount check ' );
415 END IF;
416 return;
417
418 end if;
419
420 --jsanju 04/09 fulfilment user only to send
421 OPEN c_getuserid(l_resource_id) ;
422 fetch c_getuserid INTO l_user_id;
423 CLOSE c_getuserid;
424
425
426 --ctlee 12/17/04 xdo - resource id in the xdo query
427 OPEN c_get_assign_resource(l_strategy_id, l_workitem_id) ;
428 fetch c_get_assign_resource INTO l_assign_resource_id;
429 CLOSE c_get_assign_resource;
430
431 -- ctlee - 7/15 fulfillment fax method available
432 begin
433 select upper(b.category_type)
434 into l_dunning_method
435 from iex_strategy_work_items a, IEX_STRY_TEMP_WORK_ITEMS_VL b
436 where a.work_item_template_id = b.work_item_temp_id
437 and a.work_item_id = l_workitem_id
438 and b.work_type = 'AUTOMATIC';
439 l_dunning_rec.dunning_method := l_dunning_method; -- default
440 exception
441 when others then
442 l_dunning_method := 'EMAIL';
443 l_dunning_rec.dunning_method := l_dunning_method; -- default
444 end;
445
446 -- IF PG_DEBUG < 10 THEN
447 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
448 iex_debug_pub.logmessage ('send_mail: ' || ' after fulfil method');
449 END IF;
450
451 -- ctlee - 5/20 create dunning record; 11/21/2002 remove checking profile error when update a non-existing record
452 -- l_unique_fulfillment := nvl(fnd_profile.value('IEX_STRY_UNIQUE_FULFILMENT'), 'N');
453 -- if (l_unique_fulfillment = 'Y') then
454 l_dunning_rec.delinquency_id := l_delinquency_id;
455 l_dunning_rec.callback_yn := ''; -- l_callback_flag;
456 l_dunning_rec.callback_date := ''; -- l_callback_date;
457 l_dunning_rec.status := 'OPEN';
458
459 l_dunning_rec.template_id:= l_template_id;
460 -- ctlee xdo template id
461 l_dunning_rec.xml_template_id:= l_template_id;
462
463 l_dunning_rec.object_type:= 'IEX_STRATEGY';
464 --l_dunning_rec.dunning_method:= 'EMAIL';
465 l_dunning_rec.object_id:= l_workitem_id;
466
467 -- set dunning_object_id and dunnint_level
468 begin
469 select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 40, 'DELINQUENCY', 'DELINQUENCY')
470 into l_DefaultStrategyLevel
471 from iex_strategies
472 where strategy_id = l_strategy_id;
473
474 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
475 iex_debug_pub.logmessage('Default StrategyLevel ' || l_DefaultStrategyLevel);
476 END IF;
477 EXCEPTION
478 WHEN OTHERS THEN
479 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
480 iex_debug_pub.logmessage('Strategy Level Rised Exception ');
481 END IF;
482 l_DefaultStrategyLevel := 'DELINQUENCY';
483 END;
484 IF l_DefaultStrategyLevel = 'CUSTOMER' THEN
485 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
486 l_dunning_rec.dunning_object_id:= l_party_id;
487 elsif l_DefaultStrategyLevel = 'ACCOUNT' THEN
488 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
489 l_dunning_rec.dunning_object_id:= l_cust_account_id;
490 elsif l_DefaultStrategyLevel = 'BILL_TO' THEN
491 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
492 l_dunning_rec.dunning_object_id:= l_customer_site_use_id;
493 else
494 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
495 l_dunning_rec.dunning_object_id:= l_delinquency_id;
496 end if;
497
498 begin
499 select campaign_sched_id into l_campaign_sched_id from iex_delinquencies
500 where delinquency_id = l_delinquency_id;
501 l_dunning_rec.campaign_sched_id := l_campaign_sched_id;
502 exception
503 when others then
504 l_dunning_rec.campaign_sched_id := null;
505 end;
506
507 -- close all the open dunning record before created
508 FOR d_rec in c_get_dunning(l_workitem_id)
509 LOOP
510 begin
511 l_dunning_rec_upd_old.dunning_id := d_rec.dunning_id;
512 l_dunning_rec_upd_old.last_update_date := sysdate;
513 l_dunning_rec_upd_old.callback_yn := 'N';
514 l_dunning_rec_upd_old.status := 'SKIP';
515 -- l_dunning_rec_upd_old.object_type:= 'IEX_STRATEGY';
516 -- l_dunning_rec_upd_old.object_id:= l_workitem_id;
517
518 IEX_DUNNING_PVT.Update_DUNNING(
519 p_api_version => 1.0
520 , p_init_msg_list => FND_API.G_FALSE
521 , p_commit => FND_API.G_FALSE
522 , p_dunning_rec => l_dunning_rec_upd_old
523 , x_return_status => l_return_status
524 , x_msg_count => l_msg_count
525 , x_msg_data => l_msg_data
526 );
527 exception
528 when others then
529 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
530 iex_debug_pub.logmessage ('close dunning before fulfillment exception');
531 END IF;
532 end;
533 END LOOP;
534
535 -- IF PG_DEBUG < 10 THEN
536 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
537 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, before create dunning ' );
538 END IF;
539 -- Start Added by gnramasa for bug 5661324 14-Mar-07
540 l_dunning_rec.template_id:= l_template_id;
541 l_dunning_rec.xml_template_id:= l_xdo_template_id;
542 -- End Added by gnramasa for bug 5661324 14-Mar-07
543 IEX_DUNNING_PVT.CREATE_DUNNING(
544 p_api_version => 1.0
545 , p_init_msg_list => FND_API.G_FALSE
546 , p_commit => FND_API.G_FALSE
547 , p_dunning_rec => l_dunning_rec
548 , x_dunning_id => l_dunning_id
549 , x_return_status => l_return_status
550 , x_msg_count => l_msg_count
551 , x_msg_data => l_msg_data);
552 -- end if;
553 -- Start Added by gnramasa for bug 5661324 14-Mar-07
554 l_count := l_count +1;
555 l_fulfillment_bind_tbl(l_count).key_name := 'DUNNING_ID';
556 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
557 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_dunning_id);
558 -- End Added by gnramasa for bug 5661324 14-Mar-07
559 -- IF PG_DEBUG < 10 THEN
560 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
561 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, before send_fulfillment ' );
562 END IF;
563 -- call fulfilment function with multiple bind variables.
564 -- ctlee - 6/18 fulfillment printer method available
565 l_curr_dmethod := iex_send_xml_pvt.getCurrDeliveryMethod();
566 if (l_curr_dmethod = 'FFM') then
567 iex_dunning_pvt.send_fulfillment(
568 p_api_version => 1.0,
569 p_init_msg_list => FND_API.G_TRUE,
570 p_commit => FND_API.G_TRUE,
571 p_FULFILLMENT_BIND_TBL => l_fulfillment_bind_tbl,
572 p_template_id => l_template_id,
573 p_method => l_dunning_method,
574 p_party_id => l_party_id,
575 p_user_id => l_user_id ,
576 x_return_status => l_return_status,
577 x_msg_count => l_msg_count,
578 x_msg_data => l_msg_data,
579 x_REQUEST_ID => l_request_id,
580 x_contact_destination => l_contact_destination, -- bug 3955222
581 x_contact_party_id => l_contact_party_id); -- bug 3955222
582 else
583 iex_dunning_pvt.send_xml(
584 p_api_version => 1.0,
585 p_init_msg_list => FND_API.G_TRUE,
586 p_commit => FND_API.G_TRUE,
587 p_resend => 'N',
588 p_request_id => null,
589 p_FULFILLMENT_BIND_TBL => l_fulfillment_bind_tbl,
590 p_template_id => l_xdo_template_id,
591 p_method => l_dunning_method,
592 p_user_id => l_user_id,
593 p_email => null,
594 p_party_id => l_party_id,
595 p_level => l_dunning_rec.dunning_level, -- strategy level
596 p_resource_id => l_assign_resource_id, --Bug5233002. Fix By LKKUMAR.
597 p_object_code => l_dunning_rec.object_type, -- 'IEX_STRATEGY'
598 p_source_id => l_dunning_rec.dunning_object_id, -- used by iex_send_xml_pvt.send_copy
599 p_object_id => l_dunning_rec.dunning_object_id, -- party/account/billto/del id
600 x_return_status => l_return_status,
601 x_msg_count => l_msg_count,
602 x_msg_data => l_msg_data,
603 x_REQUEST_ID => l_request_id,
604 x_contact_destination => l_contact_destination, -- bug 3955222
605 x_contact_party_id => l_contact_party_id); -- bug 3955222
606 end if;
607 -- Start Change by gnramasa for bug 5661324 14-Mar-07
608 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
609 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, after send_fulfillment ' );
610 iex_debug_pub.logmessage ('send_mail: ' || 'request_id =>'|| l_request_id);
611 iex_debug_pub.logmessage ('send_mail: ' || 'return_status =>'|| l_return_status);
612 iex_debug_pub.logmessage ('send_mail: ' || 'msg_count =>' || l_msg_count);
613 iex_debug_pub.logmessage ('send_mail: ' || 'msg_data =>' || l_msg_data);
614 END IF;
615
616 --- share a request id between xdo and ffm
617 begin
618 wf_engine.SetItemAttrNumber(itemtype => itemtype,
619 itemkey => itemkey,
620 aname => 'REQUEST_ID',
621 avalue => l_request_id);
622
623 wf_engine.SetItemAttrText(itemtype => itemtype,
624 itemkey => itemkey,
625 aname => 'ERROR_MESSAGE',
626 avalue => l_msg_data);
627 wf_engine.SetItemAttrText(itemtype => itemtype,
628 itemkey => itemkey,
629 aname => 'RETURN_STATUS',
630 avalue => l_return_status);
631 EXCEPTION
632 WHEN OTHERS THEN
633 NULL;
634 END;
635 -- return to workflow
636 if (l_request_id is null OR l_return_status <> 'S') then
637 wf_engine.SetItemAttrText(itemtype => itemtype,
638 itemkey => itemkey,
639 aname => 'WK_STATUS',
640 avalue => 'INERROR');
641
642 all_message := null;
643 FOR l_index IN 1..l_msg_count LOOP
644 my_message := FND_MSG_PUB.Get(p_msg_index => l_index,
645 p_encoded => 'F');
646 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
647 iex_debug_pub.logmessage ('send_mail: ' || my_message);
648 END IF;
649 if all_message is null then
650 all_message := my_message;
651 else
652 all_message := all_message || '; ' || chr(0) || my_message;
653 end if;
654 END LOOP;
655 iex_debug_pub.logmessage ('all_message: ' || all_message);
656
657 wf_engine.SetItemAttrText(itemtype => itemtype,
658 itemkey => itemkey,
659 aname => 'ERROR_MESSAGE',
660 avalue => all_message);
661 result := 'COMPLETE:' || 'N';
662 iex_stry_utl_pub.update_work_item(
663 p_api_version => 1.0,
664 p_commit => FND_API.G_TRUE,
665 p_init_msg_list => FND_API.G_TRUE,
666 p_work_item_id => l_workitem_id,
667 p_status => 'INERROR_CHECK_NOTIFY',
668 x_return_status => l_return_status,
669 x_msg_count => l_msg_count,
670 x_msg_data => l_msg_data
671 );
672 else
673 wf_engine.SetItemAttrText(itemtype => itemtype,
674 itemkey => itemkey,
675 aname => 'WK_STATUS',
676 avalue => 'COMPLETE');
677 wf_engine.SetItemAttrText(itemtype => itemtype,
678 itemkey => itemkey,
679 aname => 'ERROR_MESSAGE',
680 avalue => null);
681 result := 'COMPLETE:'||'Y';
682 end if;
683 l_dunning_rec_upd.dunning_id := l_dunning_id;
684 l_dunning_rec_upd.last_update_date := sysdate;
685 l_dunning_rec_upd.callback_yn := 'N';
686 l_dunning_rec_upd.status := 'CLOSE';
687 -- ctlee xdo template id
688 if (l_curr_dmethod = 'FFM') then
689 l_dunning_rec_upd.ffm_request_id := l_request_id;
690 else
691 l_dunning_rec_upd.xml_request_id := l_request_id;
692 end if;
693 l_dunning_rec_upd.contact_destination := l_contact_destination; -- bug 3955222
694 l_dunning_rec_upd.contact_party_id := l_contact_party_id; -- bug 3955222
695
696 IEX_DUNNING_PVT.Update_DUNNING(
697 p_api_version => 1.0
698 , p_init_msg_list => FND_API.G_FALSE
699 , p_commit => FND_API.G_FALSE
700 , p_dunning_rec => l_dunning_rec_upd
701 , x_return_status => l_return_status
702 , x_msg_count => l_msg_count
703 , x_msg_data => l_msg_data
704 );
705
706 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
707 iex_debug_pub.logmessage ('update dunning l_status =>' || l_return_status);
708 iex_debug_pub.logmessage ('send_mail: ' || 'result =>' || result);
709 END IF;
710
711 exception
712 when others then
713 wf_core.context('IEX_STRATEGY_WORK',' send_mail ',itemtype,
714 itemkey,to_char(actid),funcmode);
715 raise;
716
717 end send_mail;
718
719 procedure get_username
720 ( p_resource_id IN NUMBER,
721 x_username OUT NOCOPY VARCHAR2 ) IS
722 cursor c_getname(p_resource_id NUMBER) is
723 Select user_name
724 from jtf_rs_resource_extns
725 where resource_id =p_resource_id;
726
727 BEGIN
728 -- IF PG_DEBUG < 10 THEN
729 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
730 iex_debug_pub.logmessage ('**** BEGIN get_username ************');
731 END IF;
732 OPEN c_getname(p_resource_id);
733 FETCH c_getname INTO x_username;
734 CLOSE c_getname;
735 -- IF PG_DEBUG < 10 THEN
736 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
737 iex_debug_pub.logmessage ('**** END get_username ************');
738 END IF;
739 END get_username;
740 -- End Change by gnramasa for bug 5661324 14-Mar-07
741 -----populate execution_times---------------------------------
742 --set execution wait period
743 --populate to fulfillment workflow wait
744
745 procedure populate_fulfillment_wait
746 (
747 p_delinquency_id IN NUMBER,
748 p_work_item_id IN NUMBER,
749 itemtype IN varchar2,
750 itemkey IN varchar2
751 ) IS
752
753
754 cursor c_get_del(p_delinquency_id number) is
755 select a.party_id, a.party_type, a.party_name,
756 a.person_first_name, a.person_last_name,
757 b.cust_account_id, b.status, b.payment_schedule_id,
758 b.aging_bucket_line_id, b.customer_site_use_id
759 from iex_delinquencies b, hz_parties a
760 where a.party_id(+) = b.party_cust_id
761 and b.delinquency_id = p_delinquency_id;
762
763 cursor c_get_party(p_work_item_id number) is
764 select a.party_id, a.party_type, a.party_name,
765 a.person_first_name, a.person_last_name, s.cust_account_id, s.customer_site_use_id
766 from hz_parties a, iex_strategy_work_items w, iex_strategies s
767 where a.party_id = s.party_id and s.strategy_id = w.strategy_id and w.work_item_id = p_work_item_id;
768
769 -- bug 4930376 ctlee sql id 14771930, use _all performance
770 cursor c_get_payment(p_delinquency_id number) is
771 select a.amount_due_remaining
772 from ar_payment_schedules_all a, iex_delinquencies b
773 where a.payment_schedule_id(+) = b.payment_schedule_id
774 and b.delinquency_id = p_delinquency_id;
775
776 cursor c_get_witem_temp(p_work_item_id NUMBER) is
777 select a.post_execution_wait, a.execution_time_uom, a.schedule_wait, a.schedule_uom
778 from IEX_STRY_TEMP_WORK_ITEMS_VL a, IEX_STRATEGY_WORK_ITEMS b
779 where b.work_item_template_id = a.work_item_temp_id
780 and b.work_item_id = p_work_item_id;
781
782 l_fulfillment_wait date;
783 l_fulfillment_schedule date;
784 l_strategy_level number ;
785 l_resource_id NUMBER;
786 l_username VARCHAR2(120);
787 --Begin bug#5502077 schekuri 02-May-2007
788 l_strategy_id NUMBER;
789 l_SkipFlag NUMBER;
790 --End bug#5502077 schekuri 02-May-2007
791 BEGIN
792 -- IF PG_DEBUG < 10 THEN
793 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
794 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'DEL ID = ' ||p_delinquency_id);
795 END IF;
796
797 -- IF PG_DEBUG < 10 THEN
798 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
799 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'work item id = ' ||p_work_item_id);
800 END IF;
801 begin
802 select a.strategy_level,b.resource_id,a.strategy_id --Added strategy_id for bug#5502077 schekuri 02-May-2007
803 into l_strategy_level,l_resource_id,l_strategy_id
804 from iex_strategies a, iex_strategy_work_items b
805 where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
806 if l_strategy_level is null then
807 l_strategy_level := 40;
808 end if;
809 EXCEPTION WHEN OTHERS THEN
810 l_strategy_level := 40; -- default to delinquency level
811 end;
812
813 -- get user name from jtf_rs_resource_extns
814 if (l_resource_id is not null) THEN
815 get_username( p_resource_id =>l_resource_id,
816 x_username =>l_username);
817 else
818 l_username := 'SYSADMIN';
819 end if;
820
821
822 wf_engine.SetItemAttrText(itemtype => itemtype,
823 itemkey => itemkey,
824 aname => 'NOTIFICATION_USERNAME',
825 avalue => l_username);
826
827 -- IF PG_DEBUG < 10 THEN
828 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
829 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'strategy_level = ' ||l_strategy_level);
830 END IF;
831
832 if l_strategy_level = 10 or l_strategy_level = 20 or l_strategy_level = 30 then
833 FOR party_rec in c_get_party(p_work_item_id)
834 LOOP
835 -- IF PG_DEBUG < 10 THEN
836 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
837 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'INSIDE THE LOOP ' ||party_rec.party_id);
838 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'INSIDE THE LOOP ' ||party_rec.cust_account_id);
839 END IF;
840 wf_engine.SetItemAttrNumber(itemtype => itemtype,
841 itemkey => itemkey,
842 aname => 'PARTY_ID',
843 avalue => party_rec.party_id);
844 wf_engine.SetItemAttrText(itemtype => itemtype,
845 itemkey => itemkey,
846 aname => 'PARTY_TYPE',
847 avalue => party_rec.party_type);
848 wf_engine.SetItemAttrText(itemtype => itemtype,
849 itemkey => itemkey,
850 aname => 'PARTY_NAME',
851 avalue => party_rec.party_name);
852 wf_engine.SetItemAttrText(itemtype => itemtype,
853 itemkey => itemkey,
854 aname => 'FIRST_NAME',
855 avalue => party_rec.person_first_name);
856 wf_engine.SetItemAttrText(itemtype => itemtype,
857 itemkey => itemkey,
858 aname => 'LAST_NAME',
859 avalue => party_rec.person_last_name);
860 wf_engine.SetItemAttrNumber(itemtype => itemtype,
861 itemkey => itemkey,
862 aname => 'CUST_ACCOUNT_ID',
863 avalue => party_rec.cust_account_id);
864 wf_engine.SetItemAttrNumber(itemtype => itemtype,
865 itemkey => itemkey,
866 aname => 'CUSTOMER_SITE_USE_ID',
867 avalue => party_rec.customer_site_use_id);
868 exit;
869 END LOOP;
870 else
871 FOR d_rec in c_get_del(p_delinquency_id)
872 LOOP
873 -- IF PG_DEBUG < 10 THEN
874 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
875 iex_debug_pub.logmessage('populate_fulfillment_wait: ' || 'INSIDE THE LOOP ' ||d_rec.party_id);
876 END IF;
877 wf_engine.SetItemAttrNumber(itemtype => itemtype,
878 itemkey => itemkey,
879 aname => 'PARTY_ID',
880 avalue => d_rec.party_id);
881 wf_engine.SetItemAttrText(itemtype => itemtype,
882 itemkey => itemkey,
883 aname => 'PARTY_TYPE',
884 avalue => d_rec.party_type);
885 wf_engine.SetItemAttrText(itemtype => itemtype,
886 itemkey => itemkey,
887 aname => 'PARTY_NAME',
888 avalue => d_rec.party_name);
889 wf_engine.SetItemAttrText(itemtype => itemtype,
890 itemkey => itemkey,
891 aname => 'FIRST_NAME',
892 avalue => d_rec.person_first_name);
893 wf_engine.SetItemAttrText(itemtype => itemtype,
894 itemkey => itemkey,
895 aname => 'LAST_NAME',
896 avalue => d_rec.person_last_name);
897 wf_engine.SetItemAttrNumber(itemtype => itemtype,
898 itemkey => itemkey,
899 aname => 'CUST_ACCOUNT_ID',
900 avalue => d_rec.cust_account_id);
901 wf_engine.SetItemAttrText(itemtype => itemtype,
902 itemkey => itemkey,
903 aname => 'DELINQUENCY_STATUS',
904 avalue => d_rec.status);
905 wf_engine.SetItemAttrNumber(itemtype => itemtype,
906 itemkey => itemkey,
907 aname => 'AGING_BUCKET_LINE_ID',
908 avalue => d_rec.aging_bucket_LINE_id);
909 wf_engine.SetItemAttrNumber(itemtype => itemtype,
910 itemkey => itemkey,
911 aname => 'PAYMENT_SCHEDULE_ID',
912 avalue => d_rec.payment_schedule_id);
913 wf_engine.SetItemAttrNumber(itemtype => itemtype,
914 itemkey => itemkey,
915 aname => 'CUSTOMER_SITE_USE_ID',
916 avalue => d_rec.customer_site_use_id);
917 exit;
918 END LOOP;
919 FOR p_rec in c_get_payment(p_delinquency_id)
920 LOOP
921 wf_engine.SetItemAttrNumber(itemtype => itemtype,
922 itemkey => itemkey,
923 aname => 'OVERDUE_AMOUNT',
924 avalue => p_rec.amount_due_remaining);
925 exit;
926 END LOOP;
927 end if;
928
929
930
931 FOR c_rec in c_get_witem_temp(p_work_item_id)
932 LOOP
933 --Begin bug#5502077 schekuri 02-May-2007
934 --If the Strategy workflow contains RESET_WORK_ITEM_STATUS activity
935 --skip the SCHEDULE_WAIT. Since it already waits at pre-wait node in
936 --main Strategy workflow there is no need to wait at WAIT node in Fulfillment workflow.
937 l_SkipFlag := 0;
938
939 if (l_strategy_id is not null) then
940
941 BEGIN
942
943 select ceil(wfi.BEGIN_DATE - wfa.begin_Date) into l_SkipFlag
944 from WF_ITEMS wfi, WF_ACTIVITIES wfa
945 WHERE wfi.ITEM_TYPE = 'IEXSTRY'
946 and wfa.version = (select min(wa.version) from wf_activities wa
947 where wa.item_type=wfa.item_type
948 and wa.name=wfa.name)
949 and wfi.item_key = l_Strategy_id
950 and wfa.item_type = wfi.item_type AND
951 wfa.name = 'RESET_WORK_ITEM_STATUS' ;
952
953 EXCEPTION
954 WHEN OTHERS THEN NULL;
955 END;
956
957 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
958 iex_debug_pub.logmessage ('populate_fulfillment_wait: check for skip ' || ' SKIP FLAG = ' ||
959 l_SkipFlag);
960 END IF;
961
962 end if;
963
964 IF (l_SkipFlag > 0) THEN
965 l_fulfillment_schedule := SYSDATE;
966 ELSE
967
968 l_fulfillment_schedule:=IEX_STRY_UTL_PUB.get_date
969 (p_date =>SYSDATE,
970 l_UOM =>c_rec.schedule_uom,
971 l_UNIT =>c_rec.schedule_wait);
972 END IF;
973
974 /*l_fulfillment_schedule:=IEX_STRY_UTL_PUB.get_date
975 (p_date =>SYSDATE,
976 l_UOM =>c_rec.schedule_uom,
977 l_UNIT =>c_rec.schedule_wait);*/
978 --End bug#5502077 schekuri 02-May-2007
979 -- IF PG_DEBUG < 10 THEN
980 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
981 iex_debug_pub.logmessage ('populate_fulfillment_wait: ' || ' SCHEDULE TIME WAIT = ' ||
982 to_char(l_fulfillment_schedule, 'ss:mi:hh24 mm/dd/yyyy'));
983 END IF;
984
985
986 --set execution wait attribute
987 wf_engine.SetItemAttrDate(itemtype => itemtype,
988 itemkey => itemkey,
989 aname => 'SCHEDULE_TIME',
990 avalue => l_fulfillment_schedule);
991
992 --begin bug#5502077 schekuri 30-Apr-2007
993 --since there is post wait in the main strategy workflow there is no need to wait here
994 /*l_fulfillment_wait:=IEX_STRY_UTL_PUB.get_date
995 (p_date =>l_fulfillment_schedule,
996 l_UOM =>c_rec.execution_time_uom,
997 l_UNIT =>c_rec.post_execution_wait);*/
998 l_fulfillment_wait:= sysdate;
999 --end bug#5502077 schekuri 30-Apr-2007
1000
1001 -- IF PG_DEBUG < 10 THEN
1002 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1003 iex_debug_pub.logmessage ('populate_fulfillment_wait: ' || ' EXECUTION TIME WAIT = ' ||
1004 to_char(l_fulfillment_wait, 'ss:mi:hh24 mm/dd/yyyy'));
1005 END IF;
1006
1007
1008 --set execution wait attribute
1009 wf_engine.SetItemAttrDate(itemtype => itemtype,
1010 itemkey => itemkey,
1011 aname => 'EXECUTION_TIME',
1012 avalue => l_fulfillment_wait);
1013
1014 exit;
1015 END LOOP;
1016 EXCEPTION WHEN OTHERS THEN
1017 null;
1018 END populate_fulfillment_wait;
1019
1020 /**
1021 * setup the workflow which call the mailer thru fulfilment
1022 **/
1023 procedure strategy_mailer(
1024 p_api_version IN NUMBER,
1025 p_init_msg_list IN VARCHAR2,
1026 p_commit IN VARCHAR2,
1027 p_strategy_mailer_rec IN STRATEGY_MAILER_REC_TYPE,
1028 x_return_status OUT NOCOPY VARCHAR2,
1029 x_msg_count OUT NOCOPY NUMBER,
1030 x_msg_data OUT NOCOPY VARCHAR2)
1031 IS
1032 l_itemtype varchar2(80);
1033 l_itemkey varchar2(80);
1034 l_workflowprocess varchar2(80);
1035 l_result varchar2(80);
1036
1037 l_error_msg VARCHAR2(2000);
1038 l_return_status VARCHAR2(20);
1039 l_msg_count NUMBER;
1040 l_msg_data VARCHAR2(2000);
1041 l_api_name VARCHAR2(100) ;
1042 l_api_version_number CONSTANT NUMBER := 1.0;
1043
1044 begin
1045 -- initialize variables
1046 l_api_name := 'STRATEGY_MAILER';
1047
1048
1049 --IF PG_DEBUG < 10 THEN
1050 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1051 iex_debug_pub.logmessage ('in strategy_mailer');
1052 END IF;
1053 -- Standard Start of API savepoint
1054 -- SAVEPOINT STRATEGY_MAILER; -- Standard call to check for call compatibility.
1055 --IF PG_DEBUG < 10 THEN
1056 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1057 iex_debug_pub.logmessage ('in strategy_mailer 1');
1058 END IF;
1059 IF NOT FND_API.Compatible_API_Call ( l_api_version_number, p_api_version, l_api_name, G_PKG_NAME) THEN
1060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1061 END IF;
1062 --IF PG_DEBUG < 10 THEN
1063 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1064 iex_debug_pub.logmessage ('in strategy_mailer 2');
1065 END IF;
1066 -- Initialize message list IF p_init_msg_list is set to TRUE.
1067 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1068 FND_MSG_PUB.initialize;
1069 END IF;
1070 --IF PG_DEBUG < 10 THEN
1071 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1072 iex_debug_pub.logmessage ('in strategy_mailer 3');
1073 END IF;
1074
1075
1076 -- Initialize API return status to SUCCESS
1077 x_return_status := FND_API.G_RET_STS_SUCCESS;
1078 l_itemtype := 'IEXSTFFM';
1079 l_workflowprocess := 'IEXSTFFM';
1080 l_itemkey := p_strategy_mailer_rec.workitem_id;
1081 --IF PG_DEBUG < 10 THEN
1082 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1083 iex_debug_pub.logmessage ('in strategy_mailer 4');
1084 END IF;
1085
1086 wf_engine.createprocess ( itemtype => l_itemtype,
1087 itemkey => l_itemkey,
1088 process => l_workflowprocess);
1089 --IF PG_DEBUG < 10 THEN
1090 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1091 iex_debug_pub.logmessage ('in strategy_mailer 5');
1092 END IF;
1093
1094
1095
1096 if (p_strategy_mailer_rec.strategy_id is not null) then
1097 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1098 itemkey => l_itemkey,
1099 aname => 'STRATEGY_ID',
1100 avalue => p_strategy_mailer_rec.strategy_id);
1101 end if;
1102 if (p_strategy_mailer_rec.delinquency_id is not null) then
1103 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1104 itemkey => l_itemkey,
1105 aname => 'DELINQUENCY_ID',
1106 avalue => p_strategy_mailer_rec.delinquency_id);
1107 end if;
1108 if (p_strategy_mailer_rec.template_id is not null) then
1109 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1110 itemkey => l_itemkey,
1111 aname => 'TEMPLATE_ID',
1112 avalue => p_strategy_mailer_rec.template_id);
1113 end if;
1114 if (p_strategy_mailer_rec.xdo_template_id is not null) then
1115 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1116 itemkey => l_itemkey,
1117 aname => 'XDO_TEMPLATE_ID',
1118 avalue => p_strategy_mailer_rec.xdo_template_id);
1119 end if;
1120 if (p_strategy_mailer_rec.workitem_id is not null) then
1121 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1122 itemkey => l_itemkey,
1123 aname => 'WORKITEM_ID',
1124 avalue => p_strategy_mailer_rec.workitem_id);
1125 end if;
1126
1127 if (p_strategy_mailer_rec.user_id is not null) then
1128 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1129 itemkey => l_itemkey,
1130 aname => 'USER_ID',
1131 avalue => p_strategy_mailer_rec.user_id);
1132 end if;
1133
1134 if (p_strategy_mailer_rec.resp_id is not null) then
1135 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1136 itemkey => l_itemkey,
1137 aname => 'RESP_ID',
1138 avalue => p_strategy_mailer_rec.resp_id);
1139 end if;
1140
1141 if (p_strategy_mailer_rec.resp_appl_id is not null) then
1142 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1143 itemkey => l_itemkey,
1144 aname => 'RESP_APPL_ID',
1145 avalue => p_strategy_mailer_rec.resp_appl_id);
1146 end if;
1147
1148
1149
1150 populate_fulfillment_wait (
1151 p_delinquency_id => p_strategy_mailer_rec.delinquency_id,
1152 p_work_item_id => p_strategy_mailer_rec.workitem_id,
1153 itemtype => l_itemtype,
1154 itemkey => l_itemkey
1155 );
1156
1157 -- IF PG_DEBUG < 10 THEN
1158 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1159 iex_debug_pub.logmessage ('strategy_mailer: ' || ' before start workflow process');
1160 END IF;
1161
1162 wf_engine.startprocess(itemtype => l_itemtype, itemkey => l_itemkey);
1163 -- IF PG_DEBUG < 10 THEN
1164 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1165 iex_debug_pub.logmessage ('strategy_mailer: ' || ' after start workflow process');
1166 END IF;
1167 wf_engine.ItemStatus(
1168 itemtype => l_itemType,
1169 itemkey => l_itemKey,
1170 status => l_return_status,
1171 result => l_result);
1172
1173 if (l_return_status in ('COMPLETE', 'ACTIVE')) THEN
1174 x_return_status := 'S';
1175 else
1176 x_return_status := 'F';
1177 end if;
1178 -- IF PG_DEBUG < 10 THEN
1179 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1180 iex_debug_pub.logmessage ('strategy_mailer: ' || ' workflow return status = ' || l_return_status);
1181 END IF;
1182
1183
1184 EXCEPTION
1185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1186 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1187 iex_debug_pub.logmessage('UNEXPECTED ERROR. PUB: ' || l_api_name || ' end');
1188 iex_debug_pub.logmessage('PUB: ' || l_api_name || ' end');
1189 iex_debug_pub.logmessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1190 END IF;
1191
1192 WHEN OTHERS THEN
1193 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1194 iex_debug_pub.logmessage('UNHANDLED WORKFLOW EXCEPTION. Strategy ID ' || p_strategy_mailer_rec.strategy_id);
1195 iex_debug_pub.logmessage('PUB: ' || l_api_name || ' end');
1196 iex_debug_pub.logmessage('End time:' || TO_CHAR(SYSDATE, 'HH24:MI:SSSSS'));
1197 END IF;
1198
1199 end strategy_mailer;
1200
1201
1202
1203 procedure wf_send_signal(
1204 itemtype in varchar2,
1205 itemkey in varchar2,
1206 actid in number,
1207 funcmode in varchar2,
1208 result out NOCOPY varchar2)
1209 IS
1210 l_work_item_id number;
1211 l_strategy_id number;
1212 l_wk_status varchar2(20);
1213 l_return_status VARCHAR2(20);
1214 l_msg_count NUMBER;
1215 l_msg_data VARCHAR2(2000);
1216 exc EXCEPTION;
1217 l_error VARCHAR2(32767);
1218 begin
1219 if funcmode <> 'RUN' then
1220 result := wf_engine.eng_null;
1221 return;
1222 end if;
1223
1224 l_work_item_id := wf_engine.GetItemAttrNumber(
1225 itemtype => itemtype,
1226 itemkey => itemkey,
1227 aname => 'WORKITEM_ID');
1228 l_strategy_id := wf_engine.GetItemAttrNumber(
1229 itemtype => itemtype,
1230 itemkey => itemkey,
1231 aname => 'STRATEGY_ID');
1232 if (l_work_item_id is not null) then
1233 --04/04 jsanju
1234 -- do not update the send signal will update
1235 --04/16/02 -- update it here and send signal if successful
1236 --05/20/02 -- update it work item status
1237 l_wk_status := wf_engine.GetItemAttrText(
1238 itemtype => itemtype,
1239 itemkey => itemkey,
1240 aname => 'WK_STATUS');
1241
1242 iex_stry_utl_pub.update_work_item(
1243 p_api_version => 1.0,
1244 p_commit => FND_API.G_TRUE,
1245 p_init_msg_list => FND_API.G_TRUE,
1246 p_work_item_id => l_work_item_id,
1247 p_status => l_wk_status,
1248 x_return_status => l_return_status,
1249 x_msg_count => l_msg_count,
1250 x_msg_data => l_msg_data
1251 );
1252
1253 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1254 --04/04 -jsanju
1255 -- add new parameter to send signal
1256
1257 iex_strategy_wf.send_signal(
1258 process => 'IEXSTRY' ,
1259 strategy_id => l_strategy_id,
1260 status => l_wk_status,
1261 work_item_id => l_work_item_id,
1262 signal_source => 'FULFILLMENT');
1263 else
1264 RAISE EXC;
1265
1266 end if;-- if update successful
1267
1268 end if;
1269 result := wf_engine.eng_completed;
1270
1271 EXCEPTION
1272 WHEN EXC THEN
1273 --pass the error message
1274 -- get error message and pass
1275 iex_strategy_wf.Get_Messages(l_msg_count,l_error);
1276 -- IF PG_DEBUG < 10 THEN
1277 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1278 iex_debug_pub.logmessage('wf_send_signal: ' || 'error message is ' || l_error);
1279 END IF;
1280 wf_core.context('IEX_STRATEGY_WORK_PUB','wf_send_signal',itemtype,
1281 itemkey,to_char(actid),funcmode,l_error);
1282 raise;
1283
1284 WHEN OTHERS THEN
1285
1286 wf_core.context('IEX_STRATEGY_WORK_PUB','wf_send_signal',itemtype,
1287 itemkey,to_char(actid),funcmode);
1288 raise;
1289
1290
1291 end wf_send_signal;
1292
1293
1294
1295
1296 procedure check_dunning(
1297 itemtype in varchar2,
1298 itemkey in varchar2,
1299 actid in number,
1300 funcmode in varchar2,
1301 result out NOCOPY varchar2)
1302 IS
1303 l_return_status VARCHAR2(20);
1304 l_msg_count NUMBER;
1305 l_msg_data VARCHAR2(2000);
1306 l_error VARCHAR2(32767);
1307 l_delinquency_id NUMBER;
1308 l_cust_account_id NUMBER;
1309 l_customer_site_use_id NUMBER;
1310 l_count NUMBER := 0;
1311
1312 l_user_id NUMBER;
1313 l_resp_id NUMBER;
1314 l_resp_appl_id NUMBER;
1315
1316 l_work_item_id number;
1317 l_strategy_id number;
1318 l_party_id number;
1319 -- l_cust_account_id number;
1320 l_strategy_level varchar2(20);
1321 l_unique_fulfillment VARCHAR2(1);
1322 begin
1323
1324 if funcmode <> 'RUN' then
1325 result := 'COMPLETE:' || 'N';
1326 return;
1327 end if;
1328 result := 'COMPLETE:' || 'N';
1329
1330 l_unique_fulfillment := nvl(fnd_profile.value('IEX_STRY_UNIQUE_FULFILMENT'), 'N');
1331 /*
1332 if (l_unique_fulfillment = 'N') then
1333 result := 'COMPLETE:' || 'Y';
1334 return;
1335 end if;
1336 */
1337
1338 l_user_id := wf_engine.GetItemAttrNumber(
1339 itemtype => itemtype,
1340 itemkey => itemkey,
1341 aname => 'USER_ID');
1342
1343 l_resp_id := wf_engine.GetItemAttrNumber(
1344 itemtype => itemtype,
1345 itemkey => itemkey,
1346 aname => 'RESP_ID');
1347
1348 l_resp_appl_id := wf_engine.GetItemAttrNumber(
1349 itemtype => itemtype,
1350 itemkey => itemkey,
1351 aname => 'RESP_APPL_ID');
1352
1353 l_delinquency_id := wf_engine.GetItemAttrNumber(
1354 itemtype => itemtype,
1355 itemkey => itemkey,
1356 aname => 'DELINQUENCY_ID');
1357 l_work_item_id := wf_engine.GetItemAttrNumber(
1358 itemtype => itemtype,
1359 itemkey => itemkey,
1360 aname => 'WORKITEM_ID');
1361 l_strategy_id := wf_engine.GetItemAttrNumber(
1362 itemtype => itemtype,
1363 itemkey => itemkey,
1364 aname => 'STRATEGY_ID');
1365 l_party_id := wf_engine.GetItemAttrNumber(
1366 itemtype => itemtype,
1367 itemkey => itemkey,
1368 aname => 'PARTY_ID');
1369 l_cust_account_id := wf_engine.GetItemAttrNumber(
1370 itemtype => itemtype,
1371 itemkey => itemkey,
1372 aname => 'CUST_ACCOUNT_ID');
1373 l_customer_site_use_id := wf_engine.GetItemAttrNumber(
1374 itemtype => itemtype,
1375 itemkey => itemkey,
1376 aname => 'CUSTOMER_SITE_USE_ID');
1377 -- IF PG_DEBUG < 10 THEN
1378 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1379 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, delinquency_id = ' || l_delinquency_id);
1380 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, party_id = ' || l_party_id);
1381 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, cust_account_id = ' || l_cust_account_id);
1382 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, strategy_id = ' || l_strategy_id);
1383 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, work_item_id = ' || l_work_item_id);
1384 iex_debug_pub.logmessage ('check_dunning: ' || ' check dunning, customer_site_use_id = ' || l_customer_site_use_id);
1385 END IF;
1386
1387 -- IF PG_DEBUG < 10 THEN
1388 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1389 iex_debug_pub.logmessage ('check_dunning: ' || 'USER_ID' || l_user_id || ' RESP_ID ' || l_resp_id);
1390 END IF;
1391 -- IF PG_DEBUG < 10 THEN
1392 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1393 iex_debug_pub.logmessage ('check_dunning: ' || 'RESP_APPL_ID' ||l_resp_appl_id);
1394 END IF;
1395 --set the session
1396 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
1397
1398 select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 'DELINQUENCY') into l_strategy_level
1399 from iex_strategies where strategy_id = l_strategy_id;
1400 -- if l_delinquency_id is not null then
1401 if l_strategy_level = 'CUSTOMER' then
1402 begin
1403 -- IF PG_DEBUG < 10 THEN
1404 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1405 iex_debug_pub.logmessage ('check_dunning: level 10' || ' check dunning, party_id = ' || l_party_id);
1406 END IF;
1407 if l_party_id is not null then
1408 select count(*) into l_count from iex_dunnings where dunning_id in (
1409 select dun.dunning_id from iex_dunnings dun
1410 where dun.dunning_object_id = l_party_id
1411 and dun.dunning_level = l_strategy_level
1412 and dun.status = 'CLOSE'
1413 and trunc(sysdate) = trunc(dun.creation_date) );
1414 -- IF PG_DEBUG < 10 THEN
1415 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1416 iex_debug_pub.logmessage ('check_dunning: level 10' || ' check dunning, l_count = ' || l_count);
1417 END IF;
1418 if l_count > 0 and l_unique_fulfillment = 'Y' then
1419 wf_engine.SetItemAttrText(itemtype => itemtype,
1420 itemkey => itemkey,
1421 aname => 'WK_STATUS',
1422 avalue => 'CANCELLED');
1423 result := 'COMPLETE:' || 'Y';
1424 end if;
1425 end if;
1426 exception
1427 when others then
1428 result := 'COMPLETE:' || 'N';
1429 end;
1430 elsif l_strategy_level = 'ACCOUNT' then
1431 begin
1432 -- IF PG_DEBUG < 10 THEN
1433 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1434 iex_debug_pub.logmessage ('check_dunning: level 20' || ' check dunning, cust_account_id = ' || l_cust_account_id);
1435 END IF;
1436 if l_cust_account_id is not null then
1437 select count(*) into l_count from iex_dunnings where dunning_id in (
1438 select dun.dunning_id from iex_dunnings dun
1439 where dun.dunning_object_id = l_cust_account_id
1440 and dun.dunning_level = l_strategy_level
1441 and dun.status = 'CLOSE'
1442 and trunc(sysdate) = trunc(dun.creation_date) );
1443 -- IF PG_DEBUG < 10 THEN
1444 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1445 iex_debug_pub.logmessage ('check_dunning: level 20' || ' check dunning, l_count = ' || l_count);
1446 END IF;
1447 if l_count > 0 and l_unique_fulfillment = 'Y' then
1448 wf_engine.SetItemAttrText(itemtype => itemtype,
1449 itemkey => itemkey,
1450 aname => 'WK_STATUS',
1451 avalue => 'CANCELLED');
1452 result := 'COMPLETE:' || 'Y';
1453 end if;
1454 end if;
1455 exception
1456 when others then
1457 result := 'COMPLETE:' || 'N';
1458 end;
1459 elsif l_strategy_level = 'BILL_TO' then
1460 begin
1461 -- IF PG_DEBUG < 10 THEN
1462 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1463 iex_debug_pub.logmessage ('check_dunning: level 30' || ' check dunning, customer_site_use_id = ' || l_customer_site_use_id);
1464 END IF;
1465 if l_customer_site_use_id is not null then
1466 select count(*) into l_count from iex_dunnings where dunning_id in (
1467 select dun.dunning_id from iex_dunnings dun
1468 where dun.dunning_object_id = l_customer_site_use_id
1469 and dun.dunning_level = l_strategy_level
1470 and dun.status = 'CLOSE'
1471 and trunc(sysdate) = trunc(dun.creation_date) );
1472 -- IF PG_DEBUG < 10 THEN
1473 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1474 iex_debug_pub.logmessage ('check_dunning: level 30' || ' check dunning, l_count = ' || l_count);
1475 END IF;
1476 if l_count > 0 and l_unique_fulfillment = 'Y' then
1477 wf_engine.SetItemAttrText(itemtype => itemtype,
1478 itemkey => itemkey,
1479 aname => 'WK_STATUS',
1480 avalue => 'CANCELLED');
1481 result := 'COMPLETE:' || 'Y';
1482 end if;
1483 end if;
1484 exception
1485 when others then
1486 result := 'COMPLETE:' || 'N';
1487 end;
1488 else -- default level 40
1489 begin
1490 select cust_account_id into l_cust_account_id from iex_delinquencies
1491 where delinquency_id = l_delinquency_id;
1492 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1493 iex_debug_pub.logmessage ('check_dunning: 40 ' || ' check dunning, delinquency_id = ' || l_delinquency_id);
1494 END IF;
1495 if l_cust_account_id is not null then
1496 -- begin bug #4230209 03/09/2005 by ctlee, multiple letters were sent in the same day even unique = 'Y'
1497 -- select count(*) into l_count from iex_dunnings where dunning_id in (
1498 -- select dun.dunning_id from iex_delinquencies del, iex_dunnings dun
1499 -- where del.cust_account_id = l_cust_account_id
1500 -- and del.delinquency_id = dun.delinquency_id
1501 -- and dun.status = 'CLOSE'
1502 -- and trunc(sysdate) = trunc(dun.creation_date) );
1503 select count(*) into l_count from iex_dunnings where dunning_id in (
1504 select dun.dunning_id from iex_delinquencies del, iex_dunnings dun
1505 where del.cust_account_id = l_cust_account_id
1506 and ((del.delinquency_id = dun.delinquency_id and dun.status = 'CLOSE') or
1507 (del.delinquency_id = dun.delinquency_id and dun.status = 'OPEN' and
1508 del.delinquency_id <> l_delinquency_id )
1509 )
1510 and trunc(sysdate) = trunc(dun.creation_date) );
1511 -- end bug #4230209 03/09/2005 by ctlee, multiple letters were sent in the same day even unique = 'Y'
1512 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1513 iex_debug_pub.logmessage ('check_dunning: 40' || ' check dunning, l_count = ' || l_count);
1514 END IF;
1515 if l_count > 0 and l_unique_fulfillment = 'Y' then
1516 wf_engine.SetItemAttrText(itemtype => itemtype,
1517 itemkey => itemkey,
1518 aname => 'WK_STATUS',
1519 avalue => 'CANCELLED');
1520 result := 'COMPLETE:' || 'Y';
1521 end if;
1522 end if;
1523 exception
1524 when others then
1525 result := 'COMPLETE:' || 'N';
1526 end;
1527 end if;
1528 EXCEPTION
1529 WHEN OTHERS THEN
1530 result := 'COMPLETE:' || 'N';
1531 wf_core.context('IEX_STRATEGY_WORK_PUB','check_dunning',itemtype,
1532 itemkey,to_char(actid),funcmode);
1533 raise;
1534 end check_dunning;
1535
1536 /* resend fulfillment by clicked the button */
1537
1538 procedure resend_fulfillment(
1539 p_work_item_id IN NUMBER
1540 , x_status out NOCOPY varchar2
1541 , x_error_message out NOCOPY varchar2
1542 , x_request_id out NOCOPY number)
1543 IS
1544 p_delinquency_id NUMBER;
1545
1546 l_party_id number;
1547 l_strategy_id number;
1548 l_delinquency_id number;
1549 l_party_type varchar2(80);
1550 l_party_name varchar2(240);
1551 l_first_name varchar2(80);
1552 l_last_name varchar2(80);
1553 l_cust_account_id number;
1554 l_customer_site_use_id number;
1555 l_overdue_amount number;
1556 l_status varchar2(30);
1557 l_payment_schedule_id number;
1558 l_template_id number;
1559 -- xdo check
1560 l_xdo_template_id number;
1561 l_workitem_id number;
1562 l_aging_bucket_line_id number;
1563 l_fulfil_temp_id number;
1564 l_xdo_temp_id number;
1565
1566 l_fulfillment_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
1567 l_count NUMBER := 0;
1568 l_return_status VARCHAR2(20);
1569 l_msg_count NUMBER;
1570 l_msg_data VARCHAR2(2000);
1571 l_request_id NUMBER;
1572 my_message VARCHAR2(2000);
1573
1574 --jsanju 04/09 -- fulfillment resource id
1575 l_resource_id NUMBER;
1576
1577 cursor c_getuserid(l_resource_id NUMBER) is
1578 select user_id from jtf_rs_resource_extns
1579 where resource_id =l_resource_id;
1580
1581 l_user_id NUMBER;
1582 -- ctlee, add for create dunning
1583 l_unique_fulfillment VARCHAR2(1);
1584 -- l_delinquency_id NUMBER;
1585 l_callback_flag VARCHAR2(1);
1586 l_callback_date DATE;
1587 -- l_template_id NUMBER;
1588 l_campaign_sched_id NUMBER;
1589 l_DUNNING_rec IEX_DUNNING_PUB.DUNNING_REC_TYPE;
1590 l_DUNNING_id NUMBER;
1591 l_DUNNING_rec_upd IEX_DUNNING_PUB.DUNNING_REC_TYPE;
1592 l_dunning_method varchar2(2000);
1593
1594 l_DUNNING_rec_upd_old IEX_DUNNING_PUB.DUNNING_REC_TYPE;
1595 cursor c_get_dunning(p_workitem_id number) is
1596 select a.dunning_id from iex_dunnings a
1597 where a.object_id = p_workitem_id
1598 and a.object_type = 'WORK_ITEM'
1599 and a.status <> 'CLOSE';
1600
1601
1602 cursor c_get_del(p_delinquency_id number) is
1603 select a.party_id, a.party_type, a.party_name,
1604 a.person_first_name, a.person_last_name,
1605 b.cust_account_id, b.status, b.payment_schedule_id,
1606 b.aging_bucket_line_id, b.customer_site_use_id
1607 from iex_delinquencies b, hz_parties a
1608 where a.party_id(+) = b.party_cust_id
1609 and b.delinquency_id = p_delinquency_id;
1610
1611 -- bug 4930376 ctlee sql id 14772154, use _all performance
1612 cursor c_get_payment(p_delinquency_id number) is
1613 select a.amount_due_remaining
1614 from ar_payment_schedules_all a, iex_delinquencies b
1615 where a.payment_schedule_id(+) = b.payment_schedule_id
1616 and b.delinquency_id = p_delinquency_id;
1617
1618 cursor c_get_witem_temp(p_work_item_id NUMBER) is
1619 select delinquency_id, a.strategy_id
1620 from iex_strategies a, iex_strategy_work_items b
1621 where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
1622
1623 -- ctlee using xdo template id
1624 cursor c_get_xdo_template(p_work_item_id NUMBER) is
1625 select a.xdo_template_id from IEX_STRY_TEMP_WORK_ITEMS_VL a, iex_strategy_work_items b
1626 where a.work_item_temp_id = b.work_item_template_id and work_item_id = p_work_item_id;
1627
1628 cursor c_get_fulfillment_template(p_work_item_id NUMBER) is
1629 select fulfil_temp_id from IEX_STRY_TEMP_WORK_ITEMS_VL a, iex_strategy_work_items b
1630 where a.work_item_temp_id = b.work_item_template_id and work_item_id = p_work_item_id;
1631
1632 cursor c_get_party(p_work_item_id number) is
1633 select a.party_id, a.party_type, a.party_name,
1634 a.person_first_name, a.person_last_name, s.cust_account_id, s.customer_site_use_id
1635 from hz_parties a, iex_strategy_work_items w, iex_strategies s
1636 where a.party_id = s.party_id and s.strategy_id = w.strategy_id and w.work_item_id = p_work_item_id;
1637
1638 l_strategy_level number ;
1639 l_DefaultStrategyLevel varchar2(20);
1640
1641 -- xdo check
1642 l_curr_dmethod varchar2(10);
1643 l_assign_resource_id number;
1644 cursor c_get_assign_resource(l_strategy_id number, l_workitem_id number) is
1645 -- begin bug 4930376 ctlee - performance 01/09/2006 -- sql id 14772213
1646 SELECT
1647 wkitem.resource_id ASSIGNED_TO
1648 from
1649 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
1650 , wf_item_types_tl item, jtf_rs_resource_extns res
1651 WHERE
1652 wkitem.work_item_template_id = stry_temp_wkitem_b.work_item_temp_id
1653 and stry_temp_wkitem_b.work_item_temp_id =stry_temp_wkitem_tl.work_item_temp_id
1654 and stry_temp_wkitem_tl.LANGUAGE = userenv('LANG')
1655 and stry_temp_wkitem_b.WORKFLOW_ITEM_TYPE = item.name(+)
1656 and item.language(+) = userenv('LANG')
1657 and wkitem.resource_id = res.resource_id(+)
1658 and wkitem.strategy_id = l_strategy_id
1659 and wkitem.work_item_id = l_workitem_id;
1660 -- select a.assigned_to from iex_work_item_bali_v a
1661 -- where a.strategy_id = l_strategy_id
1662 -- and a.wkitem_id = l_workitem_id;
1663 -- end bug 4930376 ctlee - performance 01/09/2006
1664
1665 l_contact_destination varchar2(240); -- bug 3955222
1666 l_contact_party_id number; -- bug 3955222
1667 BEGIN
1668
1669 l_resource_id := fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE');
1670 x_status := 'F';
1671 x_request_id := 0;
1672 l_workitem_id := p_work_item_id;
1673 FOR c_rec in c_get_witem_temp(p_work_item_id)
1674 LOOP
1675 l_delinquency_id := c_rec.delinquency_id;
1676 l_strategy_id := c_rec.strategy_id;
1677 exit;
1678 END LOOP;
1679
1680 -- IF PG_DEBUG < 10 THEN
1681 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1682 iex_debug_pub.logmessage('resend_fulfillment: ' || 'DEL ID ' ||l_delinquency_id);
1683 END IF;
1684
1685 FOR x_rec in c_get_xdo_template(p_work_item_id)
1686 LOOP
1687 -- xdo template id
1688 l_xdo_temp_id := x_rec.xdo_template_id;
1689 l_xdo_template_id := l_xdo_temp_id;
1690 exit;
1691 END LOOP;
1692
1693 FOR f_rec in c_get_fulfillment_template(p_work_item_id)
1694 LOOP
1695 l_fulfil_temp_id := f_rec.fulfil_temp_id;
1696 l_template_id := l_fulfil_temp_id;
1697 exit;
1698 END LOOP;
1699 begin
1700 select a.strategy_level into l_strategy_level from iex_strategies a, iex_strategy_work_items b
1701 where a.strategy_id = b.strategy_id and b.work_item_id = p_work_item_id;
1702 if l_strategy_level is null then
1703 l_strategy_level := 40;
1704 end if;
1705 EXCEPTION WHEN OTHERS THEN
1706 l_strategy_level := 40; -- default to delinquency level
1707 end;
1708 -- IF PG_DEBUG < 10 THEN
1709 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1710 iex_debug_pub.logmessage('resend_fulfillment: ' || 'strategy_level = ' ||l_strategy_level);
1711 END IF;
1712
1713 if l_strategy_level = 10 or l_strategy_level = 20 or l_strategy_level = 30 then
1714 FOR party_rec in c_get_party(p_work_item_id)
1715 LOOP
1716 l_party_id := party_rec.party_id;
1717 l_party_type := party_rec.party_type;
1718 l_party_name := party_rec.party_name;
1719 l_first_name := party_rec.person_first_name;
1720 l_last_name := party_rec.person_last_name;
1721 l_cust_account_id := party_rec.cust_account_id;
1722 l_customer_site_use_id := party_rec.customer_site_use_id;
1723 -- l_status := party_rec.status;
1724 -- l_aging_bucket_LINE_id := party_rec.aging_bucket_LINE_id;
1725 -- l_payment_schedule_id := party_rec.payment_schedule_id;
1726 exit;
1727 END LOOP;
1728 else
1729 FOR d_rec in c_get_del(l_delinquency_id)
1730 LOOP
1731 -- IF PG_DEBUG < 10 THEN
1732 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1733 iex_debug_pub.logmessage('resend_fulfillment: ' || 'INSIDE THE LOOP ' ||d_rec.party_id);
1734 END IF;
1735 l_party_id := d_rec.party_id;
1736 l_party_type := d_rec.party_type;
1737 l_party_name := d_rec.party_name;
1738 l_first_name := d_rec.person_first_name;
1739 l_last_name := d_rec.person_last_name;
1740 l_cust_account_id := d_rec.cust_account_id;
1741 l_status := d_rec.status;
1742 l_aging_bucket_LINE_id := d_rec.aging_bucket_LINE_id;
1743 l_payment_schedule_id := d_rec.payment_schedule_id;
1744 l_customer_site_use_id := d_rec.customer_site_use_id;
1745 exit;
1746 END LOOP;
1747
1748 FOR p_rec in c_get_payment(l_delinquency_id)
1749 LOOP
1750 l_overdue_amount := p_rec.amount_due_remaining;
1751 exit;
1752 END LOOP;
1753 end if;
1754
1755
1756 if (l_party_id <> 0) then
1757
1758 -- IF PG_DEBUG < 10 THEN
1759 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1760 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, after PARTY_ID ='|| l_PARTY_ID );
1761 END IF;
1762
1763 l_count := l_count +1;
1764 l_fulfillment_bind_tbl(l_count).key_name := 'party_id';
1765 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1766 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_party_id);
1767 else -- party_id could not be null
1768 x_error_message := 'No party id';
1769 return;
1770 end if;
1771
1772 if (l_strategy_id <> 0) then
1773 -- IF PG_DEBUG < 10 THEN
1774 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1775 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, strategy_id ' );
1776 END IF;
1777 l_count := l_count +1;
1778 l_fulfillment_bind_tbl(l_count).key_name := 'strategy_id';
1779 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1780 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_strategy_id);
1781 end if;
1782
1783
1784 if (l_delinquency_id <> 0) then
1785 -- IF PG_DEBUG < 10 THEN
1786 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1787 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, delinquency_id ' );
1788 END IF;
1789 l_count := l_count +1;
1790 l_fulfillment_bind_tbl(l_count).key_name := 'delinquency_id';
1791 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1792 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_delinquency_id);
1793 end if;
1794
1795 if (l_cust_account_id <> 0) then
1796 -- IF PG_DEBUG < 10 THEN
1797 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1798 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, cust_account_id ' );
1799 END IF;
1800 l_count := l_count +1;
1801 l_fulfillment_bind_tbl(l_count).key_name := 'cust_account_id';
1802 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1803 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_cust_account_id);
1804
1805 -- ctlee for xdo template using ACCOUNT_ID matching the query
1806 l_count := l_count +1;
1807 l_fulfillment_bind_tbl(l_count).key_name := 'account_id';
1808 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1809 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_cust_account_id);
1810 end if;
1811
1812 if (l_customer_site_use_id <> 0) then
1813 -- IF PG_DEBUG < 10 THEN
1814 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1815 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, customer_site_use_id ' );
1816 END IF;
1817 l_count := l_count +1;
1818 l_fulfillment_bind_tbl(l_count).key_name := 'customer_site_use_id';
1819 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1820 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_customer_site_use_id);
1821
1822 end if;
1823
1824 if l_strategy_level = 40 then
1825 if (l_overdue_amount <> 0) then
1826 -- IF PG_DEBUG < 10 THEN
1827 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1828 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, overdue_amount ');
1829 END IF;
1830 l_count := l_count +1;
1831 l_fulfillment_bind_tbl(l_count).key_name := 'overdue_amount';
1832 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1833 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_overdue_amount);
1834 end if;
1835
1836
1837 if (l_status is not null) then
1838 -- IF PG_DEBUG < 10 THEN
1839 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1840 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, delinquency_status ' );
1841 END IF;
1842 l_count := l_count +1;
1843 l_fulfillment_bind_tbl(l_count).key_name := 'delinquency_status';
1844 l_fulfillment_bind_tbl(l_count).key_type := 'VARCHAR2';
1845 l_fulfillment_bind_tbl(l_count).key_value := l_status;
1846 end if;
1847
1848 /*
1849 if (l_aging_bucket_line_id <> 0) then
1850 -- IF PG_DEBUG < 10 THEN
1851 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1852 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, aging_bucket_line_id ' );
1853 END IF;
1854 l_count := l_count +1;
1855 l_fulfillment_bind_tbl(l_count).key_name := 'aging_bucket_line_id';
1856 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1857 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_aging_bucket_line_id);
1858 end if;
1859 */
1860
1861
1862 if (l_payment_schedule_id <> 0) then
1863 -- IF PG_DEBUG < 10 THEN
1864 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1865 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, payment_schedule_id ');
1866 END IF;
1867 l_count := l_count +1;
1868 l_fulfillment_bind_tbl(l_count).key_name := 'payment_schedule_id';
1869 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1870 l_fulfillment_bind_tbl(l_count).key_value := to_char(l_payment_schedule_id);
1871 end if;
1872
1873 end if; -- only if strategy_level = 40
1874
1875 l_curr_dmethod := iex_send_xml_pvt.getCurrDeliveryMethod();
1876 if (l_curr_dmethod = 'FFM') then
1877 if (l_fulfil_temp_id <> 0) then
1878 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1879 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, template_id ' );
1880 END IF;
1881 else -- template_id could not be null
1882 x_error_message := 'No fulfillment template id';
1883 return;
1884 end if;
1885 else
1886 if (l_xdo_template_id <> 0) then
1887 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1888 iex_debug_pub.logmessage ('send_mail: ' || ' in send mail, xdo_template_id ' );
1889 END IF;
1890 else -- template_id could not be null
1891 x_error_message := 'No xdo template id';
1892 return;
1893 end if;
1894 end if;
1895
1896
1897 if (p_work_item_id <> 0) then
1898 -- IF PG_DEBUG < 10 THEN
1899 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1900 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, workitem_id ' );
1901 END IF;
1902 /*
1903 l_count := l_count +1;
1904 l_fulfillment_bind_tbl(l_count).key_name := 'workitem_id';
1905 l_fulfillment_bind_tbl(l_count).key_type := 'NUMBER';
1906 l_fulfillment_bind_tbl(l_count).key_value := to_char(p_work_item_id);
1907 */
1908 else -- workitem_id could not be null
1909 x_error_message := 'No workitem id';
1910 return;
1911 end if;
1912
1913 -- IF PG_DEBUG < 10 THEN
1914 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1915 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, l_count ='|| l_count );
1916 END IF;
1917
1918
1919 -- ctlee - check the hz_customer_profiles.dunning_letter
1920 if ( iex_utilities.DunningProfileCheck (
1921 p_party_id => l_party_id
1922 , p_cust_account_id => l_cust_account_id
1923 , p_site_use_id => l_customer_site_use_id
1924 , p_delinquency_id => l_delinquency_id ) = 'N'
1925 ) then
1926 x_error_message := 'Customer profile has set the dunning flag to NO';
1927 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1928 iex_debug_pub.logmessage ('resend_fulfillment: ' || x_error_message);
1929 END IF;
1930 return;
1931 end if;
1932
1933
1934 -- ctlee - check the hz_customer_profiles_amt min_dunning_invoice_amount and min_dunning_amount
1935 if ( iex_utilities.DunningMinAmountCheck (
1936 p_cust_account_id => l_cust_account_id
1937 , p_site_use_id => l_customer_site_use_id) = 'N'
1938 ) then
1939 x_error_message := 'The dunning amount does not exceed the minimum dunning amount in customer profile';
1940 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1941 iex_debug_pub.logmessage ('resend_fulfillment: ' || x_error_message);
1942 END IF;
1943 return;
1944
1945 end if;
1946
1947 --jsanju 04/09 fulfilment user
1948 OPEN c_getuserid(l_resource_id) ;
1949 fetch c_getuserid INTO l_user_id;
1950 CLOSE c_getuserid;
1951
1952 --ctlee 12/17/04 xdo - resource id in the xdo query
1953 OPEN c_get_assign_resource(l_strategy_id, l_workitem_id) ;
1954 fetch c_get_assign_resource INTO l_assign_resource_id;
1955 CLOSE c_get_assign_resource;
1956
1957 -- ctlee - 7/15 fulfillment fax method available
1958 begin
1959 select upper(b.category_type)
1960 into l_dunning_method
1961 from iex_strategy_work_items a, IEX_STRY_TEMP_WORK_ITEMS_VL b
1962 where a.work_item_template_id = b.work_item_temp_id
1963 and a.work_item_id = l_workitem_id
1964 and b.work_type = 'AUTOMATIC';
1965 l_dunning_rec.dunning_method := l_dunning_method; -- default
1966 exception
1967 when others then
1968 l_dunning_method := 'EMAIL';
1969 l_dunning_rec.dunning_method := l_dunning_method; -- default
1970 end;
1971
1972 -- IF PG_DEBUG < 10 THEN
1973 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1974 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' after fulfil method');
1975 END IF;
1976
1977 -- ctlee - 5/20 create dunning record
1978 -- l_unique_fulfillment := nvl(fnd_profile.value('IEX_STRY_UNIQUE_FULFILMENT'), 'N');
1979 -- if (l_unique_fulfillment = 'Y') then
1980 l_dunning_rec.delinquency_id := l_delinquency_id;
1981 l_dunning_rec.callback_yn := ''; -- l_callback_flag;
1982 l_dunning_rec.callback_date := ''; -- l_callback_date;
1983 l_dunning_rec.status := 'OPEN';
1984
1985 l_dunning_rec.template_id:= l_template_id;
1986 -- ctlee xdo template id
1987 l_dunning_rec.xml_template_id:= l_xdo_template_id;
1988
1989 l_dunning_rec.object_type:= 'IEX_STRATEGY';
1990 --l_dunning_rec.dunning_method:= 'EMAIL';
1991 l_dunning_rec.object_id:= l_workitem_id;
1992
1993 -- set dunning_object_id and dunnint_level
1994 begin
1995 select decode(strategy_level, 10, 'CUSTOMER', 20, 'ACCOUNT', 30, 'BILL_TO', 40, 'DELINQUENCY', 'DELINQUENCY')
1996 into l_DefaultStrategyLevel
1997 from iex_strategies
1998 where strategy_id = l_strategy_id;
1999
2000 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2001 iex_debug_pub.logmessage('resend_fulfillment Default StrategyLevel ' || l_DefaultStrategyLevel);
2002 END IF;
2003 EXCEPTION
2004 WHEN OTHERS THEN
2005 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2006 iex_debug_pub.logmessage('Strategy Level Rised Exception ');
2007 END IF;
2008 l_DefaultStrategyLevel := 'DELINQUENCY';
2009 END;
2010 IF l_DefaultStrategyLevel = 'CUSTOMER' THEN
2011 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
2012 l_dunning_rec.dunning_object_id:= l_party_id;
2013 elsif l_DefaultStrategyLevel = 'ACCOUNT' THEN
2014 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
2015 l_dunning_rec.dunning_object_id:= l_cust_account_id;
2016 elsif l_DefaultStrategyLevel = 'BILL_TO' THEN
2017 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
2018 l_dunning_rec.dunning_object_id:= l_customer_site_use_id;
2019 else
2020 l_dunning_rec.dunning_level:= l_DefaultStrategyLevel;
2021 l_dunning_rec.dunning_object_id:= l_delinquency_id;
2022 end if;
2023
2024 begin
2025 select campaign_sched_id into l_campaign_sched_id from iex_delinquencies_all
2026 where delinquency_id = l_delinquency_id;
2027 l_dunning_rec.campaign_sched_id := l_campaign_sched_id;
2028 exception
2029 when others then
2030 l_dunning_rec.campaign_sched_id := null;
2031 end;
2032
2033 -- close all the open dunning record before created
2034 FOR d_rec in c_get_dunning(l_workitem_id)
2035 LOOP
2036 begin
2037 l_dunning_rec_upd_old.dunning_id := d_rec.dunning_id;
2038 l_dunning_rec_upd_old.last_update_date := sysdate;
2039 l_dunning_rec_upd_old.callback_yn := 'N';
2040 l_dunning_rec_upd_old.status := 'SKIP';
2041 -- l_dunning_rec_upd_old.object_type:= 'WORK_ITEM';
2042 -- l_dunning_rec_upd_old.object_id:= l_workitem_id;
2043
2044 IEX_DUNNING_PVT.Update_DUNNING(
2045 p_api_version => 1.0
2046 , p_init_msg_list => FND_API.G_FALSE
2047 , p_commit => FND_API.G_FALSE
2048 , p_dunning_rec => l_dunning_rec_upd_old
2049 , x_return_status => l_return_status
2050 , x_msg_count => l_msg_count
2051 , x_msg_data => l_msg_data
2052 );
2053 exception
2054 when others then
2055 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2056 iex_debug_pub.logmessage ('close dunning before fulfillment exception');
2057 END IF;
2058 end;
2059 END LOOP;
2060
2061 -- ctlee - 6/18 fulfillment printer method available
2062 -- IF PG_DEBUG < 10 THEN
2063 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2064 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, before create dunning ' );
2065 END IF;
2066 IEX_DUNNING_PVT.CREATE_DUNNING(
2067 p_api_version => 1.0
2068 , p_init_msg_list => FND_API.G_FALSE
2069 , p_commit => FND_API.G_FALSE
2070 , p_dunning_rec => l_dunning_rec
2071 , x_dunning_id => l_dunning_id
2072 , x_return_status => l_return_status
2073 , x_msg_count => l_msg_count
2074 , x_msg_data => l_msg_data);
2075 -- end if;
2076
2077 --IF PG_DEBUG < 10 THEN
2078 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2079 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, before send_fulfillment ' );
2080 END IF;
2081
2082
2083 -- call fulfilment function with multiple bind variables.
2084 if (l_curr_dmethod = 'FFM') then
2085 iex_dunning_pvt.send_fulfillment(
2086 p_api_version => 1.0,
2087 p_init_msg_list => FND_API.G_TRUE,
2088 p_commit => FND_API.G_TRUE,
2089 p_FULFILLMENT_BIND_TBL => l_fulfillment_bind_tbl,
2090 p_template_id => l_template_id,
2091 p_method => l_dunning_method,
2092 p_party_id => l_party_id,
2093 p_user_id => l_user_id ,
2094 x_return_status => l_return_status,
2095 x_msg_count => l_msg_count,
2096 x_msg_data => l_msg_data,
2097 x_REQUEST_ID => l_request_id,
2098 x_contact_destination => l_contact_destination, -- bug 3955222
2099 x_contact_party_id => l_contact_party_id); -- bug 3955222
2100 else
2101 -- for now, resend to 'N' without request_id; to send the old one 'Y' with request id
2102 iex_dunning_pvt.send_xml(
2103 p_api_version => 1.0,
2104 p_init_msg_list => FND_API.G_TRUE,
2105 p_commit => FND_API.G_TRUE,
2106 p_resend => 'N',
2107 p_request_id => null,
2108 p_FULFILLMENT_BIND_TBL => l_fulfillment_bind_tbl,
2109 p_template_id => l_xdo_template_id,
2110 p_method => l_dunning_method,
2111 p_user_id => l_user_id,
2112 p_email => null,
2113 p_party_id => l_party_id,
2114 p_level => l_dunning_rec.dunning_level, -- strategy level
2115 p_source_id => l_dunning_rec.dunning_object_id, -- changed by gnramasa bug 5661324 14-Mar-07
2116 p_object_code => l_dunning_rec.object_type, -- 'IEX_STRATEGY'
2117 p_object_id => l_dunning_rec.dunning_object_id, -- party/account/billto/del id
2118 x_return_status => l_return_status,
2119 x_msg_count => l_msg_count,
2120 x_msg_data => l_msg_data,
2121 x_REQUEST_ID => l_request_id,
2122 x_contact_destination => l_contact_destination, -- bug 3955222
2123 x_contact_party_id => l_contact_party_id); -- bug 3955222
2124 end if;
2125 --- share a request id between xdo and ffm
2126
2127
2128 -- IEX_DEBUG_PUB.setDebugFileDir(P_FILEDIR => '/sqlcom/log', P_FILENAME =>'james.IEX');
2129 --IEX_DEBUG_PUB.setDebugFileDir(P_FILEDIR => '/sqlcom/log');
2130
2131
2132 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2133 iex_debug_pub.logmessage ('resend_fulfillment: ' || ' in send mail, after send_fulfillment ' );
2134 END IF;
2135
2136 -- return to workflow
2137 if (l_request_id is null OR l_return_status <> 'S') then
2138 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2139 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'request_id =>'|| l_request_id);
2140 END IF;
2141 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2142 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'return_status =>'|| l_return_status);
2143 END IF;
2144 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2145 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'msg_count =>' || l_msg_count);
2146 END IF;
2147
2148 -- IF PG_DEBUG < 10 THEN
2149 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2150 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'msg_data =>');
2151 END IF;
2152 FOR l_index IN 1..l_msg_count LOOP
2153 my_message := FND_MSG_PUB.Get(p_msg_index => l_index,
2154 p_encoded => 'F');
2155 -- IF PG_DEBUG < 10 THEN
2156 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2157 iex_debug_pub.logmessage ('resend_fulfillment: ' || my_message);
2158 END IF;
2159 END LOOP;
2160 x_error_message := my_message;
2161 else
2162 -- IF PG_DEBUG < 10 THEN
2163 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2164 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'request_id =>'|| l_request_id);
2165 END IF;
2166 -- IF PG_DEBUG < 10 THEN
2167 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2168 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'return_status =>'|| l_return_status);
2169 END IF;
2170 -- IF PG_DEBUG < 10 THEN
2171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2172 iex_debug_pub.logmessage ('resend_fulfillment: ' || 'msg_count =>' || l_msg_count);
2173 END IF;
2174
2175 x_status := 'S';
2176 x_request_id := l_request_id;
2177
2178 l_dunning_rec_upd.dunning_id := l_dunning_id;
2179 l_dunning_rec_upd.last_update_date := sysdate;
2180 l_dunning_rec_upd.callback_yn := 'N';
2181 l_dunning_rec_upd.status := 'CLOSE';
2182 -- ctlee xdo template id
2183 if (l_curr_dmethod = 'FFM') then
2184 l_dunning_rec_upd.ffm_request_id := l_request_id;
2185 else
2186 l_dunning_rec_upd.xml_request_id := l_request_id;
2187 end if;
2188 l_dunning_rec_upd.contact_destination := l_contact_destination; -- bug 3955222
2189 l_dunning_rec_upd.contact_party_id := l_contact_party_id; -- bug 3955222
2190
2191 IEX_DUNNING_PVT.Update_DUNNING(
2192 p_api_version => 1.0
2193 , p_init_msg_list => FND_API.G_FALSE
2194 , p_commit => FND_API.G_FALSE
2195 , p_dunning_rec => l_dunning_rec_upd
2196 , x_return_status => l_return_status
2197 , x_msg_count => l_msg_count
2198 , x_msg_data => l_msg_data
2199 );
2200 end if;
2201
2202 return;
2203 EXCEPTION WHEN OTHERS THEN
2204 null;
2205 END resend_fulfillment;
2206
2207 --Start schekuri Bug#4506922 Date:02-Dec-2005
2208 --added for the function WAIT_ON_HOLD_SIGNAL in workflow IEXSTFFM
2209 procedure wait_on_hold_signal(
2210 itemtype in varchar2,
2211 itemkey in varchar2,
2212 actid in number,
2213 funcmode in varchar2,
2214 result out nocopy varchar2) IS
2215
2216 l_work_item_temp_id NUMBER;
2217 l_result VARCHAR2(1);
2218 l_value VARCHAR2(300);
2219
2220 BEGIN
2221 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2222 iex_debug_pub.logmessage ('**** START wait_on_hold_signal ************');
2223 END IF;
2224 if funcmode <> wf_engine.eng_run then
2225 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2226 iex_debug_pub.logmessage('SECOND TIME FUNCMODE' ||funcmode);
2227 END IF;
2228 result := wf_engine.eng_null;
2229 return;
2230 end if;
2231
2232
2233
2234 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2235 iex_debug_pub.logmessage('FUNCMODE' ||funcmode);
2236 END IF;
2237 /* l_value :=wf_engine.GetActivityLabel(actid);
2238 wf_engine.SetItemAttrText(itemtype => itemtype,
2239 itemkey => itemkey,
2240 aname => 'ACTIVITY_NAME',
2241 avalue => l_value);
2242 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2243 iex_debug_pub.logMessage('ACTIVITYNAME' ||l_value);
2244 END IF;*/
2245
2246
2247 result := wf_engine.eng_notified||':'||wf_engine.eng_null||
2248 ':'||wf_engine.eng_null;
2249 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2250 iex_debug_pub.logmessage ('**** END wait_on_hold_signal ************');
2251 END IF;
2252 exception
2253 when others then
2254 result := wf_engine.eng_completed ||':'||wf_no;
2255 wf_core.context('IEX_STRATEGY_WORK_PUB','wait_on_hold_signal',itemtype,
2256 itemkey,to_char(actid),funcmode);
2257 raise;
2258
2259 END wait_on_hold_signal;
2260
2261 --end schekuri Bug#4506922 Date:02-Dec-2005
2262
2263 begin
2264 -- initialize variables
2265 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2266
2267 --begin schekuri Bug#4506922 Date:02-Dec-2005
2268 wf_yes := 'Y';
2269 wf_no := 'N';
2270 --end schekuri Bug#4506922 Date:02-Dec-2005
2271
2272 end IEX_STRATEGY_WORK_PUB;
2273