[Home] [Help]
PACKAGE BODY: APPS.IEX_STRATEGY_DIAG_REPORT
Source
1 PACKAGE BODY IEX_STRATEGY_DIAG_REPORT AS
2 /* $Header: iexstdgb.pls 120.4 2011/05/04 08:34:37 pnaveenk noship $ */
3 /* This package is for strategy diagnostic report*/
4 l_seq_no number := 1;
5 PG_DEBUG NUMBER(2) ;
6 l_new_line VARCHAR2(1);
7 tempResult CLOB;
8
9
10 Procedure WriteLog ( p_msg IN VARCHAR2)
11 IS
12 BEGIN
13
14 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
15 iex_debug_pub.LogMessage (p_msg);
16 END IF;
17
18 END WriteLog;
19
20 FUNCTION format_string(p_string varchar2) return varchar2 IS
21
22 l_string varchar2(2000);
23 BEGIN
24
25 l_string := replace(p_string,'&','&'||'amp;');
26 l_string := replace(l_string,'<','&'||'lt;');
27 l_string := replace(l_string,'>','&'||'gt;');
28
29 RETURN l_string;
30
31 END format_string;
32
33 PROCEDURE PRINT_CLOB
34 (
35 lob_loc IN CLOB)
36 IS
37 /*-----------------------------------------------------------------------+
38 | Local Variable Declarations and initializations |
39 +-----------------------------------------------------------------------*/
40 l_api_name CONSTANT VARCHAR2(30) := 'PRINT_CLOB';
41 l_api_version CONSTANT NUMBER := 1.0;
42 c_endline CONSTANT VARCHAR2 (1) := '
43 ';
44 c_endline_len CONSTANT NUMBER := LENGTH (c_endline);
45 l_start NUMBER := 1;
46 l_end NUMBER;
47 l_one_line VARCHAR2 (7000);
48 l_charset VARCHAR2(100);
49 /*-----------------------------------------------------------------------+
50 | Cursor Declarations |
51 +-----------------------------------------------------------------------*/
52 BEGIN
53 -- LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
54 LOOP
55 l_end := DBMS_LOB.INSTR (lob_loc => lob_loc, pattern => c_endline, offset => l_start, nth => 1 );
56 --FND_FILE.put_line( FND_FILE.LOG,'l_end-->'||l_end);
57 IF (NVL (l_end, 0) < 1) THEN
58 EXIT;
59 END IF;
60 l_one_line := DBMS_LOB.SUBSTR (lob_loc => lob_loc, amount => l_end - l_start, offset => l_start );
61 --FND_FILE.put_line( FND_FILE.LOG,'l_one_line-->'||l_one_line);
62 --FND_FILE.put_line( FND_FILE.LOG,'c_endline_len-->'||c_endline_len);
63 l_start := l_end + c_endline_len;
64 --FND_FILE.put_line( FND_FILE.LOG,'l_start-->'||l_start);
65 --FND_FILE.put_line( FND_FILE.LOG,'32');
66 Fnd_File.PUT_line(Fnd_File.OUTPUT,l_one_line);
67 END LOOP;
68 END PRINT_CLOB;
69
70
71 Procedure get_resource (p_strategy_rec IN IEX_STRATEGY_PVT.STRATEGY_REC_TYPE,
72 p_work_item_template_id IN NUMBER,
73 x_resource_id OUT NOCOPY NUMBER,
74 x_resource_name OUT NOCOPY VARCHAR2)
75 is
76 l_resource_id number;
77 l_resource_name VARCHAR2(360);
78 l_Assignment_level varchar2(100);
79 l_competence_tab IEX_STRATEGY_WF.tab_of_comp_id;
80 l_index NUMBER :=1;
81 l_default_resource_id number;
82 bReturn Boolean;
83 l_party_id number;
84 l_cust_acct_id number;
85 l_cust_site_use_id number;
86
87 cursor c_get_competence_id (p_work_item_temp_id NUMBER) IS
88 SELECT competence_id
89 from iex_strategy_work_skills
90 where work_item_temp_id = p_work_item_temp_id;
91
92 cursor c_resource_name (p_resource_id number)
93 is
94 select source_name
95 from jtf_rs_resource_extns
96 where resource_id = p_resource_id;
97
98 begin
99 writelog('Begin get_resource');
100 l_Assignment_Level := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
101
102 l_default_resource_id := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
103 l_resource_id := l_default_resource_id;
104
105 l_party_id := p_strategy_rec.party_id;
106 l_cust_acct_id := p_strategy_rec.cust_account_id;
107 l_cust_site_use_id := p_strategy_rec.customer_site_use_id;
108
109 FOR c_rec IN c_get_competence_id(p_work_item_template_id)
110 LOOP
111 l_competence_tab(l_index) := c_rec.competence_id;
112 l_index := l_index +1;
113 END LOOP;
114
115 if p_strategy_rec.object_type = 'PARTY' then
116
117 if l_Assignment_Level = 'PARTY' then
118 IEX_STRATEGY_WF.get_resource(p_party_id => l_party_id,
119 p_competence_tab => l_competence_tab,
120 x_resource_id => l_resource_id);
121 end if;
122 elsif p_strategy_rec.object_type = 'IEX_ACCOUNT' then
123
124 if l_Assignment_Level = 'PARTY' then
125 IEX_STRATEGY_WF.get_resource(l_party_id,l_competence_tab,l_resource_id);
126 elsif l_Assignment_level = 'ACCOUNT' then
127 bReturn := IEX_STRATEGY_WF.get_account_resource(l_cust_acct_id, l_competence_tab, l_resource_id);
128 end if;
129
130 else
131 if l_Assignment_Level = 'PARTY' then
132 IEX_STRATEGY_WF.get_resource(l_party_id,l_competence_tab,l_resource_id);
133 elsif l_Assignment_level = 'ACCOUNT' then
134 bReturn := IEX_STRATEGY_WF.get_account_resource(l_cust_acct_id, l_competence_tab, l_resource_id);
135 else
136 bReturn := IEX_STRATEGY_WF.get_billto_resource(l_cust_site_use_id,l_competence_tab,l_resource_id);
137 end if;
138
139 end if;
140
141 if l_resource_id is null then
142 l_resource_id := l_default_resource_id;
143 end if;
144
145 x_resource_id := l_resource_id;
146
147 open c_resource_name (l_resource_id);
148 fetch c_resource_name into l_resource_name;
149 close c_resource_name;
150
151 x_resource_name := l_resource_name;
152
153 writelog('In get_resource raised Exception l_resource_name: ' || l_resource_name);
154 EXCEPTION
155 WHEN OTHERS THEN
156 writelog('In get_resource raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
157 fnd_file.put_line(FND_FILE.LOG, 'In get_resource raised Exception ' || ' sqlcode = ' || sqlcode || ' sqlerrm = ' || sqlerrm);
158 end get_resource;
159
160 Procedure gen_xml_header_data_strategy
161 is
162 l_api_version CONSTANT NUMBER := 1.0;
163 l_xml_header varchar2(4000);
164 l_xml_header_length number;
165 l_close_tag VARCHAR2(100);
166 l_report_date varchar2(30);
167 l_pro_sty_by_ou varchar2(30);
168 l_skip_def_sty_assign varchar2(30);
169 l_grace_preiod number;
170 l_resource_id number;
171 l_fulfilment_rs VARCHAR2(700);
172 l_terr_acc_level varchar2(100);
173 l_lookup_code varchar2(100);
174 l_encoding VARCHAR2(100);
175
176 cursor c_get_lookup_meaning (p_lookup_code varchar2)
177 is
178 select
179 meaning
180 from fnd_lookups
181 where lookup_type= 'YES_NO'
182 and lookup_code = p_lookup_code;
183
184 cursor c_resource_name (p_resource_id number)
185 is
186 select source_name
187 from jtf_rs_resource_extns
188 where resource_id = p_resource_id;
189
190 begin
191 writelog('Begin gen_xml_header_data_strategy');
192 FND_FILE.put_line( FND_FILE.LOG,'XML header data generation starts');
193
194 select to_char(sysdate, 'YYYY-MM-DD')
195 into l_report_date
196 from dual;
197 writelog('gen_xml_header_data_strategy: l_report_date: ' || l_report_date);
198
199 l_terr_acc_level := NVL(fnd_profile.value('IEX_ACCESS_LEVEL'), 'PARTY');
200 writelog('gen_xml_header_data_strategy: l_terr_acc_level: ' || l_terr_acc_level);
201
202 l_lookup_code := nvl(fnd_profile.value('IEX_PROC_STR_ORG'),'N');
203 writelog('gen_xml_header_data_strategy: l_lookup_code: ' || l_lookup_code);
204
205 open c_get_lookup_meaning (l_lookup_code);
206 fetch c_get_lookup_meaning into l_pro_sty_by_ou;
207 close c_get_lookup_meaning;
208
209 writelog('gen_xml_header_data_strategy: l_pro_sty_by_ou: '|| l_pro_sty_by_ou);
210
211 l_lookup_code := NVL(FND_PROFILE.VALUE('IEX_SKIP_DEFAULT_STRATEGY_ASSIGNMENT'), 'N');
212 open c_get_lookup_meaning (l_lookup_code);
213 fetch c_get_lookup_meaning into l_skip_def_sty_assign;
214 close c_get_lookup_meaning;
215 writelog('gen_xml_header_data_strategy: l_skip_def_sty_assign: '|| l_skip_def_sty_assign);
216
217 l_resource_id := NVL(fnd_profile.value('IEX_STRY_FULFILMENT_RESOURCE'), 0);
218 open c_resource_name (l_resource_id);
219 fetch c_resource_name into l_fulfilment_rs;
220 close c_resource_name;
221 writelog('gen_xml_header_data_strategy: l_fulfilment_rs: '|| l_fulfilment_rs);
222
223
224 l_grace_preiod := NVL(to_number(FND_PROFILE.VALUE('IEX_STRY_GRACE_PERIOD')), 0);
225
226 writelog('Start constructing the XML Header data');
227 l_new_line := '
228 ';
229 writelog('gen_xml_header_data_strategy: before format_string');
230 /*Get the special characters replaced */
231 l_report_date := format_string(l_report_date);
232 l_fulfilment_rs := format_string(l_fulfilment_rs);
233 writelog('gen_xml_header_data_strategy: after format_string');
234
235 /* Prepare the tag for the report heading */
236 --l_xml_header := '<?xml version="1.0" encoding="UTF-8"?>';
237 -- Instead of hard coding the value, pick the charcter set value from "ICX: Client IANA Encoding" profile.
238 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
239 l_xml_header := '<?xml version="1.0" encoding="'||l_encoding||'"?>';
240 l_xml_header := l_xml_header ||l_new_line||'<STRATEGYSET>';
241 l_xml_header := l_xml_header ||l_new_line||' <REPORT_DATE>'||l_report_date||'</REPORT_DATE>';
242 l_xml_header := l_xml_header ||l_new_line||' <STRATEGY_LEVEL>'|| l_StrategyLevelName ||'</STRATEGY_LEVEL>';
243 l_xml_header := l_xml_header ||l_new_line||' <TERR_ACCESS_LEVEL>'|| l_terr_acc_level ||'</TERR_ACCESS_LEVEL>';
244 -- l_xml_header := l_xml_header ||l_new_line||' <STRATEGY_MODE>' || p_strategy_mode ||'</STRATEGY_MODE>';
245 l_xml_header := l_xml_header ||l_new_line||' <CONC_REQUEST_ID>' || FND_GLOBAL.CONC_REQUEST_ID ||'</CONC_REQUEST_ID>';
246 l_xml_header := l_xml_header ||l_new_line||' <PROCESS_STY_BY_OU>' || l_pro_sty_by_ou ||'</PROCESS_STY_BY_OU>';
247 l_xml_header := l_xml_header ||l_new_line||' <SKIP_DEF_STY_ASSIGN>' || l_skip_def_sty_assign ||'</SKIP_DEF_STY_ASSIGN>';
248 l_xml_header := l_xml_header ||l_new_line||' <STY_DEFAULT_TEMPLATE>' || format_string(l_DefaultTempName) ||'</STY_DEFAULT_TEMPLATE>';
249 l_xml_header := l_xml_header ||l_new_line||' <DEFAULT_RESOURCE>' || format_string(l_SourceName) ||'</DEFAULT_RESOURCE>';
250 l_xml_header := l_xml_header ||l_new_line||' <FULFILMENT_RESOURCE>' || l_fulfilment_rs ||'</FULFILMENT_RESOURCE>';
251 l_xml_header := l_xml_header ||l_new_line||' <STY_GRACE_PERIOD>' || l_grace_preiod ||'</STY_GRACE_PERIOD>';
252 l_xml_header := l_xml_header ||l_new_line||'<ROWSET>';
253
254
255 l_xml_header_length := length(l_xml_header);
256 tempResult := l_xml_header;
257 writelog('Constructing the XML Header is success');
258
259 dbms_lob.createtemporary(tempResult,FALSE,DBMS_LOB.CALL);
260 dbms_lob.open(tempResult,dbms_lob.lob_readwrite);
261 dbms_lob.writeAppend(tempResult, length(l_xml_header), l_xml_header);
262
263 writelog('End gen_xml_header_data_strategy');
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 FND_FILE.put_line( FND_FILE.LOG,'err'||sqlerrm);
268 RAISE;
269 END gen_xml_header_data_strategy;
270
271
272 Procedure gen_xml_body_strategy (p_strategy_id IN NUMBER DEFAULT NULL,
273 p_strategy_rec IN IEX_STRATEGY_PVT.STRATEGY_REC_TYPE DEFAULT NULL,
274 p_strategy_status IN VARCHAR2)
275 is
276 l_api_version CONSTANT NUMBER := 1.0;
277 l_xml_body varchar2(4000);
278 l_party_id number;
279 l_cust_Account_id number;
280 l_customer_site_use_id number;
281 l_delinquency_id number;
282 l_score number;
283 l_new_score number;
284 l_strategy_name VARCHAR2(500);
285 l_creation_date VARCHAR2(100);
286 l_new_strategy_name VARCHAR2(500);
287 l_party_name VARCHAR2(700);
288 l_account_number VARCHAR2(100);
289 l_location VARCHAR2(100);
290 l_trx_number VARCHAR2(100);
291 l_first_work_item VARCHAR2(500);
292 l_work_item_temp_id number;
293 l_resource_id number;
294 l_resource_name VARCHAR2(700);
295 l_sty_workitem_st varchar2(500);
296
297 cursor c_strategy
298 is
299 select
300 sty.party_id,
301 sty.cust_Account_id,
302 sty.customer_site_use_id,
303 sty.delinquency_id,
304 sty.score_value,
305 tpl.strategy_name,
306 to_char(sty.creation_date , 'YYYY-MM-DD') creation_date,
307 stry_temp_wkitem.name,
308 iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',swi.status_code) STATUS_MEANING,
309 jtf.source_name
310 from iex_strategies sty,
311 iex_strategy_templates_tl tpl,
312 iex_strategy_work_items swi,
313 iex_stry_temp_work_items_vl stry_temp_wkitem,
314 jtf_rs_resource_extns jtf
315 where sty.strategy_id = p_strategy_id
316 and sty.strategy_template_id = tpl.strategy_temp_id
317 and tpl.language = userenv('LANG')
318 and sty.next_work_item_id = swi.work_item_id
319 and swi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
320 and stry_temp_wkitem.language = userenv('LANG')
321 and swi.resource_id = jtf.resource_id;
322
323 cursor c_strategy1
324 is
325 select
326 sty.party_id,
327 sty.cust_Account_id,
328 sty.customer_site_use_id,
329 sty.delinquency_id,
330 sty.score_value,
331 tpl.strategy_name,
332 TO_CHAR(sty.creation_date,'YYYY-MM-DD') creation_date
333 from iex_strategies sty,
334 iex_strategy_templates_tl tpl
335 where sty.strategy_id = p_strategy_id
336 and sty.strategy_template_id = tpl.strategy_temp_id
337 and tpl.language = userenv('LANG');
338
339 cursor c_strategy_name (l_sty_template_id number)
340 is
341 select
342 tpl.strategy_name
343 from iex_strategy_templates_tl tpl
344 where tpl.strategy_temp_id = l_sty_template_id
345 and tpl.language = userenv('LANG');
346
347 cursor c_first_work_item (l_sty_template_id number)
348 is
349 select stry_temp_wkitem.name,
350 stry_temp_wkitem.work_item_temp_id,
351 iex_utilities.get_lookup_meaning('IEX_STRATEGY_WORK_STATUS',(decode(stry_temp_wkitem.pre_execution_wait,0,'OPEN','PRE-WAIT'))) STATUS_MEANING
352 from iex_strategy_work_temp_xref xref
353 ,iex_stry_temp_work_items_vl stry_temp_wkitem
354 where xref.work_item_temp_id = stry_temp_wkitem.work_item_temp_id
355 and xref.strategy_temp_id = l_sty_template_id
356 and stry_temp_wkitem.language = userenv('LANG')
357 order by xref.work_item_order;
358
359 cursor c_party (p_party_id number)
360 is
361 select
362 party_name
363 from hz_parties
364 where party_id = p_party_id;
365
366 cursor c_account (p_cust_acct_id number)
367 is
368 select
369 p.party_name,
370 c.account_number
371 from hz_parties p,
372 hz_cust_accounts c
373 where c.cust_account_id = p_cust_acct_id
374 and c.party_id = p.party_id;
375
376 cursor c_billto (p_cust_site_use_id number)
377 is
378 select
379 p.party_name,
380 c.account_number,
381 site_uses.location
382 from hz_parties p,
383 hz_cust_accounts c,
384 hz_cust_acct_sites_all acct_sites,
385 hz_cust_site_uses_all site_uses
386 where site_uses.site_use_id = p_cust_site_use_id
387 and acct_sites.cust_acct_site_id = site_uses.cust_acct_site_id
388 and c.cust_account_id = acct_sites.cust_account_id
389 and p.party_id = c.party_id;
390
391 cursor c_delinquency (p_delinquency_id number)
392 is
393 select
394 p.party_name,
395 aps.trx_number TRANSACTION_NUMBER
396 from iex_delinquencies_all del,
397 ar_payment_schedules_all aps ,
398 hz_parties p
399 where del.delinquency_id = p_delinquency_id
400 and del.payment_Schedule_id = aps.payment_Schedule_id
401 and del.party_cust_id = p.party_id;
402
403 begin
404 writelog('Begin gen_xml_body_strategy');
405 writelog('gen_xml_body_strategy, p_strategy_id: ' || p_strategy_id);
406 writelog('gen_xml_body_strategy, p_strategy_status: ' || p_strategy_status);
407
408 if p_strategy_rec.strategy_template_id IS NOT NULL then
409 l_party_id := p_strategy_rec.party_id;
410 l_cust_Account_id := p_strategy_rec.cust_account_id;
411 l_customer_site_use_id := p_strategy_rec.customer_site_use_id;
412 l_delinquency_id := p_strategy_rec.delinquency_id;
413
414 open c_strategy_name (p_strategy_rec.strategy_template_id);
415 if p_strategy_status = 'RECREATE' then
416 fetch c_strategy_name into l_new_strategy_name;
417 l_new_score := p_strategy_rec.score_value;
418 else
419 fetch c_strategy_name into l_strategy_name;
420 l_score := p_strategy_rec.score_value;
421 end if;
422 close c_strategy_name;
423
424 open c_first_work_item (p_strategy_rec.strategy_template_id);
425 fetch c_first_work_item into l_first_work_item, l_work_item_temp_id,l_sty_workitem_st;
426 close c_first_work_item;
427
428 writelog('gen_xml_body_strategy, before get_resource');
429 get_resource(p_strategy_rec => p_strategy_rec,
430 p_work_item_template_id => l_work_item_temp_id,
431 x_resource_id => l_resource_id,
432 x_resource_name => l_resource_name);
433 writelog('gen_xml_body_strategy, after get_resource');
434 end if;
435
436 if p_strategy_id IS NOT NULL then
437 if p_strategy_status = 'RECREATE' then
438 open c_strategy1;
439 fetch c_strategy1 into l_party_id, l_cust_Account_id, l_customer_site_use_id, l_delinquency_id, l_score, l_strategy_name,l_creation_date;
440 close c_strategy1;
441 else
442 open c_strategy;
443 fetch c_strategy into l_party_id, l_cust_Account_id, l_customer_site_use_id,
444 l_delinquency_id, l_score, l_strategy_name,l_creation_date,l_first_work_item,l_sty_workitem_st,l_resource_name;
445 close c_strategy;
446 end if;
447 end if;
448
449 if l_StrategyLevelName = 'CUSTOMER' then
450 open c_party (l_party_id);
451 fetch c_party into l_party_name;
452 close c_party;
453 elsif l_StrategyLevelName = 'ACCOUNT' then
454 open c_account (l_cust_Account_id);
455 fetch c_account into l_party_name, l_account_number;
456 close c_account;
457 elsif l_StrategyLevelName = 'BILL_TO' then
458 open c_billto (l_customer_site_use_id);
459 fetch c_billto into l_party_name, l_account_number, l_location;
460 close c_billto;
461 else
462 open c_delinquency (l_delinquency_id);
463 fetch c_delinquency into l_party_name, l_trx_number;
464 close c_delinquency;
465 end if;
466
467 writelog('gen_xml_body_strategy, before format_string');
468 /*Get the special characters replaced */
469 l_party_name := format_string(l_party_name);
470 l_account_number := format_string(l_account_number);
471 l_location := format_string(l_location);
472 l_trx_number := format_string(l_trx_number);
473 l_strategy_name := format_string(l_strategy_name);
474 l_creation_date := format_string(TO_CHAR(p_strategy_rec.creation_date,'YYYY-MM-DD'));
475 l_new_strategy_name := format_string(l_new_strategy_name);
476 writelog('gen_xml_body_strategy, after format_string');
477 FND_FILE.put_line( FND_FILE.LOG,'creation_date'||l_creation_date);
478
479 --l_xml_body := l_xml_body ||l_new_line||'<ROW num="' || l_seq_no || '">';
480 l_xml_body := l_xml_body ||l_new_line||'<'|| p_strategy_status||' num="' || l_seq_no || '">';
481 l_xml_body := l_xml_body ||l_new_line||'<PARTY_ID> ' || l_party_id || '</PARTY_ID>';
482 l_xml_body := l_xml_body ||l_new_line||'<PARTY_NAME>' || l_party_name || '</PARTY_NAME>';
483 l_xml_body := l_xml_body ||l_new_line||'<ACCOUNT_NUMBER>' || l_account_number || '</ACCOUNT_NUMBER>';
484 l_xml_body := l_xml_body ||l_new_line||'<LOCATION>' || l_location || '</LOCATION>';
485 l_xml_body := l_xml_body ||l_new_line||'<CUST_ACCOUNT_ID> ' || l_cust_Account_id || '</CUST_ACCOUNT_ID>';
486 l_xml_body := l_xml_body ||l_new_line||'<CUST_SITE_USE_ID> ' || l_customer_site_use_id || '</CUST_SITE_USE_ID>';
487 l_xml_body := l_xml_body ||l_new_line||'<TRANSACTION_NUMBER> ' || l_trx_number || '</TRANSACTION_NUMBER>';
488 l_xml_body := l_xml_body ||l_new_line||'<DELINQUENCY_ID> ' || l_delinquency_id || '</DELINQUENCY_ID>';
489 l_xml_body := l_xml_body ||l_new_line||'<SCORE>' || l_score || '</SCORE>';
490 l_xml_body := l_xml_body ||l_new_line||'<STRATEGY_NAME>' || l_strategy_name || '</STRATEGY_NAME>';
491 l_xml_body := l_xml_body ||l_new_line||'<CREATION_DATE>' || l_creation_date || '</CREATION_DATE>';
492 l_xml_body := l_xml_body ||l_new_line||'<NEW_SCORE>' || l_new_score || '</NEW_SCORE>';
493 l_xml_body := l_xml_body ||l_new_line||'<NEW_STRATEGY_NAME>' || l_new_strategy_name || '</NEW_STRATEGY_NAME>';
494 l_xml_body := l_xml_body ||l_new_line||'<WORKITEM_NAME>' || l_first_work_item || '</WORKITEM_NAME>';
495 l_xml_body := l_xml_body ||l_new_line||'<WORKITEM_ASSIGNEE>' || l_resource_name ||'</WORKITEM_ASSIGNEE>';
496 l_xml_body := l_xml_body ||l_new_line||'<WORKITEM_STATUS>' || l_sty_workitem_st ||'</WORKITEM_STATUS>';
497 l_xml_body := l_xml_body ||l_new_line||'</'|| p_strategy_status ||'>';
498
499 writelog('gen_xml_body_strategy, end of constructing body text');
500
501 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
502 l_seq_no := l_seq_no + 1;
503
504 /*if p_strategy_status = 'CLOSE' then
505 l_no_closed_rec := l_no_closed_rec + 1;
506 elsif p_strategy_status = 'REOPEN' then
507 l_no_reopen_rec := l_no_reopen_rec + 1;
508 elsif p_strategy_status = 'RECREATE' then
509 l_no_reassign_rec := l_no_reassign_rec + 1;
510 elsif p_strategy_status = 'CREATE' then
511 l_no_new_rec := l_no_new_rec + 1;
512 end if;*/
513
514 writelog('End gen_xml_body_strategy');
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 FND_FILE.put_line( FND_FILE.LOG,'err'||sqlerrm);
519 writelog('in gen_xml_body_strategy, err: '||sqlerrm);
520 RAISE;
521 END gen_xml_body_strategy;
522
523 Procedure gen_xml_append_closetag_sty
524 is
525 l_api_version CONSTANT NUMBER := 1.0;
526 l_close_tag VARCHAR2(4000);
527
528 begin
529 writelog('Begin gen_xml_append_closetag_sty');
530 FND_FILE.put_line( FND_FILE.LOG,'XML append close tag generation starts');
531
532 l_close_tag := l_new_line||'</ROWSET>';
533 /* l_close_tag := l_close_tag ||l_new_line||'<NO_CLOSED_REC>' || l_no_closed_rec || '</NO_CLOSED_REC>';
534 l_close_tag := l_close_tag ||l_new_line||'<NO_REOPEN_REC>' || l_no_reopen_rec || '</NO_REOPEN_REC>';
535 l_close_tag := l_close_tag ||l_new_line||'<NO_REASSIGN_REC>' || l_no_reassign_rec || '</NO_REASSIGN_REC>';
536 l_close_tag := l_close_tag ||l_new_line||'<NO_NEW_REC>' || l_no_new_rec || '</NO_NEW_REC>'; */
537 l_close_tag := l_close_tag ||l_new_line||'</STRATEGYSET>'||l_new_line;
538
539 dbms_lob.writeAppend(tempResult, length(l_close_tag), l_close_tag);
540 FND_FILE.put_line( FND_FILE.LOG,'Appended close tag to XML data');
541 --Fnd_File.PUT_line(Fnd_File.OUTPUT,tempResult);
542 print_clob(lob_loc => tempResult);
543 FND_FILE.put_line( FND_FILE.LOG,'XML generation is success');
544 writelog('End gen_xml_append_closetag_sty');
545
546 EXCEPTION
547 WHEN OTHERS THEN
548 FND_FILE.put_line( FND_FILE.LOG,'err'||sqlerrm);
549 RAISE;
550 END gen_xml_append_closetag_sty;
551
552 PROCEDURE gen_xml_corpt_str_list
553 IS
554 l_api_version CONSTANT NUMBER := 1.0;
555 l_corrupted_tag VARCHAR2(4000);
556 l_strategy_rec IEX_STRATEGY_PVT.STRATEGY_REC_TYPE;
557
558 -- strategies in open or onhold status without work flow
559 CURSOR c_str_without_wf IS
560 select strategy_id
561 from iex_strategies str
562 where str.status_code IN ('OPEN','ONHOLD')
563 AND not exists ( select 1 from wf_items wf
564 where wf.item_key = str.strategy_id and wf.item_type='IEXSTRY');
565
566 -- strategies in open or onhold status without work items
567 CURSOR c_str_without_wi IS
568 select strategy_id
569 from iex_strategies str
570 where str.status_code in ('OPEN','ONHOLD')
571 and not exists ( select 1 from iex_strategy_work_items wi
572 where wi.strategy_id = str.strategy_id);
573
574 -- strategy work items of type automatic and open status without work flow
575 CURSOR c_strwi_witout_fulwf IS
576 select str.strategy_id
577 from iex_strategies str, iex_strategy_work_items wi , IEX_STRY_TEMP_WORK_ITEMS_B witemp
578 where str.strategy_id = wi.strategy_id
579 and wi.work_item_template_id = witemp.work_item_temp_id
580 and witemp.work_type='AUTOMATIC'
581 and wi.status_code = 'OPEN'
582 and not exists ( select 1 from wf_items wf
583 where wf.item_key = wi.work_item_id
584 and wf.item_type in ('IEXSTFFM','IEXSTCM'));
585
586 -- strategy work items in error check notify status
587 CURSOR c_str_inerror_notify IS
588 select str.strategy_id
589 from iex_strategies str, iex_strategy_work_items wi
590 where str.strategy_id = wi.strategy_id
591 and wi.status_code = 'INERROR_CHECK_NOTIFY'
592 and str.status_code in ('OPEN','ONHOLD');
593
594 -- work items of type automatic in open status for more than 2 days
595 CURSOR c_str_automatic_wait IS
596 select str.strategy_id
597 from iex_strategies str, iex_strategy_work_items wi , iex_stry_temp_work_items_b witemp
598 where str.strategy_id = wi.strategy_id
599 and wi.work_item_template_id = witemp.work_item_temp_id
600 and witemp.work_type = 'AUTOMATIC'
601 and wi.status_code in 'OPEN'
602 and trunc(wi.execute_start + 2 ) < trunc(sysdate);
603
604 -- work item templates of type automatic and category email or fax without xdo template
605 CURSOR c_witemp_without_xdo IS
606 select work_item_temp_id
607 from IEX_STRY_TEMP_WORK_ITEMS_B
608 where work_type = 'AUTOMATIC'
609 AND category_type IN ('EMAIL','FAX')
610 and xdo_template_id is null;
611
612 -- work item templates of type custom work flow without work flow name
613 CURSOR c_witemp_without_wf IS
614 select work_item_temp_id
615 from IEX_STRY_TEMP_WORK_ITEMS_B
616 where work_type='WORKFLOW'
617 and category_type='WORKFLOW' and workflow_item_type is null;
618
619 -- strategies in open or onhold status with work flow error no performer
620
621 CURSOR c_wi_no_performer IS
622 select b.strategy_id
623 from iex_strategy_work_items wi,
624 IEX_STRATEGIES b,
625 WF_ITEM_ACTIVITY_STATUSES_V wfia
626 where wi.work_item_id = wfia.item_key
627 and wfia.item_type='IEXSTFFM'
628 and wfia.activity_label = 'FULFILLMENT FAILED'
629 and wfia.error_name ='WFENG_NOTIFICATION_PERFORMER'
630 and wfia.activity_status_code='ERROR'
631 and wi.status_code ='INERROR_CHECK_NOTIFY'
632 and wi.STRATEGY_ID= b.STRATEGY_ID
633 and b.status_code in ('OPEN','ONHOLD');
634 -- strategies in open or onhold status with work flow error no role
635
636 CURSOR c_wi_no_role IS
637 select str.strategy_id
638 from iex_strategy_work_items wi,
639 WF_ITEM_ACTIVITY_STATUSES_V wfia,
640 iex_strategies str
641 where wi.work_item_id = wfia.item_key
642 and wfia.error_name = 'WFNTF_ROLE'
643 and wfia.activity_status_code='ERROR'
644 and wfia.item_type='IEXSTFFM'
645 and wfia.activity_label = 'FULFILLMENT FAILED'
646 and wi.status_code='INERROR_CHECK_NOTIFY'
647 and str.strategy_id = wi.strategy_id
648 and str.status_code in ('OPEN','ONHOLD');
649
650 -- strategy work items with IEXSTFFM work flow and work item template is not automatic
651 CURSOR c_wi_wf_not_auto IS
652 select str.strategy_id from
653 iex_strategies str,iex_strategy_work_items wi, iex_stry_temp_work_items_vl wit, wf_items wfi
654 where str.strategy_id = wi.strategy_id
655 and str.status_code in ('OPEN','ONHOLD')
656 and wi.work_item_template_id = wit.work_item_temp_id
657 and wit.work_type <> 'AUTOMATIC'
658 and wi.work_item_id = wfi.item_key
659 and wfi.item_type = 'IEXSTFFM';
660
661 -- automatic work items processed in sysdate
662 CURSOR c_auto_wi_processed IS
663 Select wi.work_item_id
664 from iex_strategy_work_items wi,iex_dunnings dunn ,iex_stry_temp_work_items_vl stry_temp_wkitem
665 where wi.last_update_date>(sysdate-1)
666 and dunn.object_id (+) = wi.work_item_id
667 and wi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
668 and exists
669 (select 1 from IEX_STRY_TEMP_WORK_ITEMS_B wt
670 where wt.work_item_temp_id=wi.work_item_template_id
671 and wt.xdo_template_id is not null);
672
673
674
675 CURSOR c_str_details ( p_strategy_id number) IS
676 SELECT STRATEGY_ID
677 , STATUS_CODE
678 , STRATEGY_TEMPLATE_ID
679 , DELINQUENCY_ID
680 , OBJECT_TYPE
681 , OBJECT_ID
682 , CUST_ACCOUNT_ID
683 , PARTY_ID
684 , SCORE_VALUE
685 , NEXT_WORK_ITEM_ID
686 , USER_WORK_ITEM_YN
687 , LAST_UPDATE_DATE
688 , LAST_UPDATED_BY
689 , LAST_UPDATE_LOGIN
690 , CREATION_DATE
691 , CREATED_BY
692 , OBJECT_VERSION_NUMBER
693 , REQUEST_ID
694 , PROGRAM_APPLICATION_ID
695 , PROGRAM_ID
696 , PROGRAM_UPDATE_DATE
697 , CHECKLIST_STRATEGY_ID
698 , CHECKLIST_YN
699 , STRATEGY_LEVEL
700 , JTF_OBJECT_TYPE
701 , JTF_OBJECT_ID
702 , CUSTOMER_SITE_USE_ID
703 , ORG_ID
704 FROM iex_strategies
705 WHERE strategy_id = p_strategy_id;
706
707 BEGIN
708 writelog('Begin gen_xml_corpt_str_list');
709 FND_FILE.put_line( FND_FILE.LOG,'XML append corrupted strategy list starts');
710
711 FOR i in c_str_without_wf
712 LOOP
713
714 OPEN c_str_details (i.strategy_id);
715 FETCH c_str_details INTO l_strategy_rec;
716 CLOSE c_str_details;
717
718 gen_xml_body_strategy (
719 p_strategy_rec => l_strategy_rec,
720 p_strategy_status => 'NOWF');
721 END LOOP;
722
723 FOR i IN c_str_without_wi
724 LOOP
725 OPEN c_str_details(i.strategy_id);
726 FETCH c_str_details INTO l_strategy_rec;
727 CLOSE c_str_details;
728
729 gen_xml_body_strategy (p_strategy_rec => l_strategy_rec,
730 p_strategy_status => 'NOWI');
731 END LOOP;
732
733 FOR i IN c_strwi_witout_fulwf
734 LOOP
735 OPEN c_str_details(i.strategy_id);
736 FETCH c_str_details INTO l_strategy_rec;
737 CLOSE c_str_details;
738
739 gen_xml_body_strategy (p_strategy_rec => l_strategy_rec,
740 p_strategy_status => 'NOFWF');
741 END LOOP;
742
743 FOR i IN c_str_inerror_notify
744 LOOP
745 OPEN c_str_details(i.strategy_id);
746 FETCH c_str_details INTO l_strategy_rec;
747 CLOSE c_str_details;
748
749 gen_xml_body_strategy (p_strategy_rec => l_strategy_rec,
750 p_strategy_status => 'ERRORWI');
751 END LOOP;
752
753 FOR i IN c_str_automatic_wait
754 LOOP
755 OPEN c_str_details(i.strategy_id);
756 FETCH c_str_details INTO l_strategy_rec;
757 CLOSE c_str_details;
758
759 gen_xml_body_strategy (p_strategy_rec => l_strategy_rec,
760 p_strategy_status => 'AWIWAIT');
761 END LOOP;
762
763 FOR i IN c_witemp_without_xdo
764 LOOP
765 gen_xml_body_wi (p_work_item_temp_id => i.work_item_temp_id,
766 p_wi_temp_status => 'WITNOXDO');
767 END LOOP;
768
769 FOR i IN c_witemp_without_wf
770 LOOP
771 gen_xml_body_wi (p_work_item_temp_id => i.work_item_temp_id,
772 p_wi_temp_status => 'WITNOWF');
773 END LOOP;
774
775 FOR i IN c_wi_no_performer
776 LOOP
777 OPEN c_str_details(i.strategy_id);
778 FETCH c_str_details INTO l_strategy_rec;
779 CLOSE c_str_details;
780 gen_xml_body_strategy( p_strategy_rec => l_strategy_rec,
781 p_strategy_status => 'WINOPERF');
782 END LOOP;
783
784 FOR i IN c_wi_no_role
785 LOOP
786 OPEN c_str_details(i.strategy_id);
787 FETCH c_str_details INTO l_strategy_rec;
788 CLOSE c_str_details;
789 gen_xml_body_strategy(p_strategy_rec => l_strategy_rec,
790 p_strategy_status => 'WINOROLE');
791 END LOOP;
792
793 FOR i IN c_wi_wf_not_auto
794 LOOP
795 OPEN c_str_details(i.strategy_id);
796 FETCH c_str_details INTO l_strategy_rec;
797 CLOSE c_str_details;
798 gen_xml_body_strategy( p_strategy_rec => l_strategy_rec,
799 p_strategy_status => 'WIWFNOAUTO');
800 END LOOP;
801
802 FOR i IN c_auto_wi_processed
803 LOOP
804 gen_xml_body_auto_wi (p_work_item_id => i.work_item_id,
805 p_wi_status => 'AUTOWIPROCESSED');
806 END LOOP;
807
808
809
810
811 writelog('End gen_xml_corpt_str_list');
812 FND_FILE.put_line( FND_FILE.LOG,'XML append corrupted strategy list end');
813 EXCEPTION
814 WHEN OTHERS THEN
815 FND_FILE.put_line( FND_FILE.LOG,'err'||sqlerrm);
816 RAISE;
817 END gen_xml_corpt_str_list;
818
819 PROCEDURE gen_xml_body_wi (p_work_item_temp_id NUMBER , p_wi_temp_status VARCHAR2)
820 IS
821 CURSOR c_wi_temp(p_wi_temp_id number) IS
822 select ist.work_item_temp_id , istv.name,to_char(ist.creation_date , 'YYYY-MM-DD') creation_date, ist.work_type,ist.category_type,ist.xdo_template_id,ist.workflow_item_type
823 from IEX_STRY_TEMP_WORK_ITEMS_B ist , IEX_STRY_TEMP_WORK_ITEMS_VL istv
824 where ist.work_item_temp_id = istv.work_item_temp_id
825 and ist.work_item_temp_id = p_wi_temp_id;
826
827 l_wi_temp_id NUMBER;
828 l_wi_temp_name VARCHAR2(100);
829 l_creation_date VARCHAR2(100);
830 l_work_type VARCHAR2(100);
831 l_category_type VARCHAR2(100);
832 l_xdo_temp_id NUMBER;
833 l_workflow_item_type VARCHAR2(100);
834 l_xml_body VARCHAR2(4000);
835
836 BEGIN
837 OPEN c_wi_temp(p_work_item_temp_id);
838 FETCH c_wi_temp INTO l_wi_temp_id,l_wi_temp_name,l_creation_date,l_work_type,l_category_type,l_xdo_temp_id,l_workflow_item_type;
839 CLOSE c_wi_temp;
840
841 writelog('gen_xml_body_wi, before format_string');
842 /*Get the special characters replaced */
843 l_wi_temp_id := format_string(l_wi_temp_id);
844 l_wi_temp_name := format_string(l_wi_temp_name);
845 l_work_type := format_string(l_work_type);
846 l_category_type := format_string(l_category_type);
847 l_xdo_temp_id := format_string(l_xdo_temp_id);
848 l_workflow_item_type := format_string(l_workflow_item_type);
849
850 writelog('gen_xml_body_wi, after format_string');
851
852 l_xml_body := l_xml_body ||l_new_line||'<'|| p_wi_temp_status||' num="' || l_seq_no || '">';
853 l_xml_body := l_xml_body ||l_new_line||'<WI_TEMP_ID> ' || l_wi_temp_id || '</WI_TEMP_ID>';
854 l_xml_body := l_xml_body ||l_new_line||'<WI_TEMP_NAME>' || l_wi_temp_name || '</WI_TEMP_NAME>';
855 l_xml_body := l_xml_body ||l_new_line||'<CREATION_DATE>' || l_creation_date || '</CREATION_DATE>';
856 l_xml_body := l_xml_body ||l_new_line||'<WI_TEMP_WORK_TYPE>' || l_work_type || '</WI_TEMP_WORK_TYPE>';
857 l_xml_body := l_xml_body ||l_new_line||'<WI_TEMP_CATEGORY>' || l_category_type || '</WI_TEMP_CATEGORY>';
858 l_xml_body := l_xml_body ||l_new_line||'<WI_XDO_TEMP_ID> ' || l_xdo_temp_id || '</WI_XDO_TEMP_ID>';
859 l_xml_body := l_xml_body ||l_new_line||'<WI_TEMP_WF_ITEM_TYPE> ' || l_workflow_item_type || '</WI_TEMP_WF_ITEM_TYPE>';
860 l_xml_body := l_xml_body ||l_new_line||'</'|| p_wi_temp_status ||'>';
861
862 writelog('gen_xml_body_wi, end of constructing body text');
863
864 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
865 l_seq_no := l_seq_no + 1;
866 END gen_xml_body_wi;
867
868
869 PROCEDURE gen_xml_body_auto_wi (p_work_item_id NUMBER ,p_wi_status VARCHAR2)
870 IS
871 CURSOR c_wi_temp(p_work_item_id number) IS
872 Select hzp.PARTY_NAME, acct.account_number, iexST.PARTY_ID, IEXST.CUST_ACCOUNT_ID,
873 TO_CHAR(wi.LAST_UPDATE_DATE, 'YYYY-MM-DD') Last_Update,
874 to_char(wi.creation_date , 'YYYY-MM-DD') creation_date,
875 wi.WORK_ITEM_ID,
876 wi.status_code,
877 dunn.delivery_status,
878 stytm.strategy_name strategy_name,
879 stry_temp_wkitem.name workitem_name
880 from iex_strategies iexst, iex_strategy_work_items wi,
881 hz_parties hzp, hz_cust_accounts acct, iex_dunnings dunn ,
882 iex_strategy_templates_vl stytm,
883 iex_stry_temp_work_items_vl stry_temp_wkitem
884 where wi.last_update_date>(sysdate-1)
885 and wi.strategy_id = iexst.strategy_id
886 AND hzp.party_id = iexst.party_id
887 and dunn.object_id (+) = wi.work_item_id
888 and iexst.cust_account_id = acct.cust_account_id (+)
889 and iexst.strategy_template_id = stytm.strategy_temp_id
890 and wi.work_item_template_id = stry_temp_wkitem.work_item_temp_id
891 and exists
892 (select 1 from IEX_STRY_TEMP_WORK_ITEMS_B wt
893 where wt.work_item_temp_id=wi.work_item_template_id
894 and wt.xdo_template_id is not null)
895 AND wi.work_item_id = p_work_item_id;
896
897 L_PARTY_NAME VARCHAR2(240);
898 l_account_number VARCHAR2(240);
899 l_party_id number;
900 l_cust_account_id number;
901 l_last_update_date VARCHAR2(100);
902 l_work_item_id number;
903 l_status_code VARCHAR2(240);
904 l_delivery_status VARCHAR2(240);
905 l_strategy_name VARCHAR2(240);
906 l_workitem_name VARCHAR2(240);
907 l_xml_body VARCHAR2(4000);
908 l_creation_date VARCHAR2(100);
909
910 BEGIN
911 OPEN c_wi_temp(p_work_item_id);
912 FETCH c_wi_temp INTO L_PARTY_NAME,l_account_number,l_party_id,l_cust_account_id,l_last_update_date,l_creation_date,
913 l_work_item_id,l_status_code,l_delivery_status,l_strategy_name,l_workitem_name;
914 CLOSE c_wi_temp;
915
916 writelog('gen_xml_body_wi, before format_string');
917 /*Get the special characters replaced */
918 L_PARTY_NAME := format_string(L_PARTY_NAME);
919 l_account_number := format_string(l_account_number);
920 l_party_id := format_string(l_party_id);
921 l_cust_account_id := format_string(l_cust_account_id);
922 l_last_update_date := format_string(l_last_update_date);
923 l_work_item_id := format_string(l_work_item_id);
924 l_status_code := format_string(l_status_code);
925 l_delivery_status := format_string(l_delivery_status);
926 l_strategy_name := format_string(l_strategy_name);
927 l_workitem_name := format_string(l_workitem_name);
928
929
930 writelog('gen_xml_body_wi, after format_string');
931
932 l_xml_body := l_xml_body ||l_new_line||'<'|| p_wi_status||' num="' || l_seq_no || '">';
933 l_xml_body := l_xml_body ||l_new_line||'<WI_PARTY_NAME> ' || L_PARTY_NAME || '</WI_PARTY_NAME>';
934 l_xml_body := l_xml_body ||l_new_line||'<WI_ACCOUNT_NUMBER>' || l_account_number || '</WI_ACCOUNT_NUMBER>';
935 l_xml_body := l_xml_body ||l_new_line||'<WI_PARTY_ID>' || l_party_id || '</WI_PARTY_ID>';
936 l_xml_body := l_xml_body ||l_new_line||'<WI_CUST_ACCOUNT_ID>' || l_cust_account_id || '</WI_CUST_ACCOUNT_ID>';
937 l_xml_body := l_xml_body ||l_new_line||'<WI_LAST_UPDATE_DATE> ' || l_last_update_date || '</WI_LAST_UPDATE_DATE>';
938 l_xml_body := l_xml_body ||l_new_line||'<CREATION_DATE> ' || l_creation_date || '</CREATION_DATE>';
939 l_xml_body := l_xml_body ||l_new_line||'<WI_ID> ' || l_work_item_id || '</WI_ID>';
940 l_xml_body := l_xml_body ||l_new_line||'<WI_STATUS_CODE> ' || l_status_code || '</WI_STATUS_CODE>';
941 l_xml_body := l_xml_body ||l_new_line||'<WI_DELIVERY_STATUS> ' || l_delivery_status || '</WI_DELIVERY_STATUS>';
942 l_xml_body := l_xml_body ||l_new_line||'<WI_STR_NAME>' || l_strategy_name || '</WI_STR_NAME>';
943 l_xml_body := l_xml_body ||l_new_line||'<WI_NAME>' || l_workitem_name || '</WI_NAME>';
944
945 l_xml_body := l_xml_body ||l_new_line||'</'|| p_wi_status ||'>';
946
947 writelog('gen_xml_body_wi, end of constructing body text');
948
949 dbms_lob.writeAppend(tempResult, length(l_xml_body), l_xml_body);
950 l_seq_no := l_seq_no + 1;
951 END gen_xml_body_auto_wi;
952
953 PROCEDURE MAIN( ERRBUF OUT NOCOPY VARCHAR2,
954 RETCODE OUT NOCOPY VARCHAR2)
955 IS
956 CURSOR c_system_strategy_level IS
957 select preference_value
958 from iex_app_preferences_b
959 where preference_name='COLLECTIONS STRATEGY LEVEL'
960 and enabled_flag='Y';
961
962
963 BEGIN
964
965 open c_system_strategy_level;
966 fetch c_system_strategy_level into l_StrategyLevelName;
967 close c_system_strategy_level;
968
969 gen_xml_header_data_strategy;
970 gen_xml_corpt_str_list;
971 gen_xml_append_closetag_sty;
972 END main;
973
974 END IEX_STRATEGY_DIAG_REPORT;