[Home] [Help]
PACKAGE BODY: APPS.IEX_SEND_XML_PVT
Source
1 PACKAGE BODY IEX_SEND_XML_PVT as
2 /* $Header: iexvxmlb.pls 120.40.12020000.6 2013/03/15 18:09:12 snuthala ship $ */
3 -- Start of Comments
4 -- Package name : IEX_SEND_XML_PVT
5 -- Purpose : Generate XML Data and Delivery by XML Publisher
6 -- NOTE :
7 -- History :
8 -- 11/08/2004 CLCHANG Created.
9 -- END of Comments
10
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_SEND_XML_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvxmlb.pls';
14
15 -- Validation
16 -- **************************
17 PG_DEBUG NUMBER ;
18
19 -- **************************
20
21 PROCEDURE fetch_lang_terr_of_loc ( p_bind_tbl IN IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL,
22 p_template_code IN VARCHAR2, --Added for bug 8649857 gnramasa 3rd July 09
23 x_tmpl_lang OUT NOCOPY VARCHAR2,
24 x_tmpl_terr OUT NOCOPY VARCHAR2)
25 IS
26 l_bind_cnt number;
27 l_bind_name varchar2(150);
28 l_bind_val varchar2(240);
29 l_loc_lang varchar2(10);
30 l_loc_terr varchar2(10);
31 l_iso_lang varchar2(10);
32 l_iso_lang1 varchar2(10);
33 l_templ_lang varchar2(10);
34 l_templ_terr varchar2(10);
35 l_templ_terr1 varchar2(10);
36 l_no_terr number;
37 l_msg VARCHAR2(1000);
38 l_templ_code varchar2(100); --Added for bug 8649857 gnramasa 3rd July 09
39
40 BEGIN
41 l_msg := 'iexvxmlb.pls:FETCH_LANG_TERR_OF_LOC:';
42 WriteLog(l_msg || ' BEGIN' );
43
44 l_bind_cnt := p_bind_tbl.count;
45 --Start adding for bug 8649857 gnramasa 3rd July 09
46 l_templ_code := p_template_code;
47
48 for j in 1..l_bind_cnt
49 loop
50 l_bind_name := upper(p_bind_tbl(j).Key_name);
51
52 if l_bind_name ='LOCATION_ID' then
53 l_bind_val := p_bind_tbl(j).Key_Value;
54 WriteLog(l_msg || 'bind_name = '||l_bind_name || ' value=' || l_bind_val );
55 EXIT;
56 end if;
57 end loop;
58
59 if l_bind_val is not null then
60 select language
61 into l_loc_lang
62 from hz_locations
63 where location_id= to_number(l_bind_val);
64
65 WriteLog(l_msg || 'l_loc_lang = '||l_loc_lang );
66
67 if l_loc_lang is not null then
68 select iso_language
69 into l_iso_lang
70 from fnd_languages
71 where language_code= upper(l_loc_lang);
72
73 WriteLog(l_msg || 'l_iso_lang = '||l_iso_lang );
74
75 select count(distinct territory)
76 into l_no_terr
77 from xdo_lobs
78 where application_short_name='IEX' and upper(language)=l_iso_lang
79 and territory <> '00'
80 and lob_code = l_templ_code;
81
82 WriteLog(l_msg || 'l_no_terr = '||l_no_terr );
83
84 l_templ_lang := l_iso_lang;
85
86 --if no of territory is >1 then send territory as '00' else the default territory for that template.
87 if l_no_terr = 1 then
88 select distinct territory
89 into l_templ_terr
90 from xdo_lobs
91 where application_short_name='IEX' and upper(language)=l_iso_lang
92 and territory <> '00'
93 and lob_code = l_templ_code;
94
95 --End adding for bug 8649857 gnramasa 3rd July 09
96
97 else
98 l_templ_terr := '00';
99 end if;
100 WriteLog(l_msg || 'l_templ_terr = '||l_templ_terr );
101 end if;
102 end if;
103 x_tmpl_lang := l_templ_lang;
104 x_tmpl_terr := l_templ_terr;
105 WriteLog(l_msg || ', l_templ_lang :' ||l_templ_lang );
106 WriteLog(l_msg || ', l_templ_terr :' ||l_templ_terr );
107 WriteLog(l_msg || ' END' );
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 WriteLog(l_msg || ' Procedure fetch_lang_terr_of_loc, in when Other exception:'|| SQLERRM);
112
113 END fetch_lang_terr_of_loc;
114
115 PROCEDURE Send_COPY(
116 p_Api_Version_Number IN NUMBER,
117 p_Init_Msg_List IN VARCHAR2 ,
118 p_Commit IN VARCHAR2 ,
119 p_resend IN VARCHAR2 ,
120 p_request_id IN NUMBER ,
121 p_user_id IN NUMBER,
122 p_party_id IN NUMBER,
123 p_subject IN VARCHAR2,
124 p_bind_tbl IN IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL,
125 p_template_id IN NUMBER,
126 p_resource_id IN NUMBER,
127 p_query_id IN NUMBER,
128 p_method IN VARCHAR2,
129 p_dest IN VARCHAR2,
130 p_level IN VARCHAR2,
131 p_source_id IN NUMBER,
132 p_object_type IN VARCHAR2,
133 p_object_id IN NUMBER,
134 p_dunning_mode IN VARCHAR2, -- added by gnramasa for bug 8489610 14-May-09
135 p_parent_request_id IN NUMBER,
136 p_org_id in number, -- added for bug 9151851
137 p_addt_template_id IN NUMBER DEFAULT NULL, --added for bug 9970624 gnramasa 4th Aug 10
138 p_addt_query_id IN NUMBER DEFAULT NULL, --added for bug 9970624 gnramasa 4th Aug 10
139 X_Request_ID OUT NOCOPY NUMBER,
140 X_Return_Status OUT NOCOPY VARCHAR2,
141 X_Msg_Count OUT NOCOPY NUMBER,
142 X_Msg_Data OUT NOCOPY VARCHAR2
143 )
144 IS
145
146 CURSOR C_GET_DEL (IN_DEL_ID NUMBER) IS
147 SELECT cust_account_id, customer_site_use_id
148 FROM IEX_DELINQUENCIES
149 WHERE delinquency_ID = in_del_ID;
150 --
151 --Start Adding for bug 8649857 gnramasa 3rd July 09
152 CURSOR C_GET_TEMPCODE (IN_TEMP_ID NUMBER) IS
153 SELECT template_code
154 FROM XDO_TEMPLATES_VL
155 WHERE template_id = in_TEMP_ID
156 --AND Application_id = 695;
157 AND Application_short_name = 'IEX';
158
159 --Start Adding for bug 8845762 snuthala 9/14/2009
160 --Start adding for bug 10158389 gnramasa 4th Oct 10
161 CURSOR C_GET_QUERY_TEMP_ID (P_QUERY_ID NUMBER, p_temp_id number) IS
162 SELECT xref.query_temp_id
163 FROM iex_query_temp_xref xref
164 WHERE xref.query_id = P_QUERY_ID
165 AND xref.template_id = p_temp_id;
166 -- end adding for bug 10158389 gnramasa 4th Oct 10
167 -- end Adding for bug 8845762 snuthala 9/14/2009
168
169 --Start Adding for bug 16387319 snuthala 6/MAR/2013
170 CURSOR C_GET_CUST_ACCOUNT_ID(P_SITE_USE_ID Number) IS
171 select cust.cust_account_id
172 from hz_cust_accounts cust,
173 hz_cust_acct_sites_all acc_site,
174 hz_cust_site_uses_all site_use
175 where site_use.site_use_id = p_site_use_id and
176 site_use.cust_acct_site_id = acc_site.cust_acct_site_id and
177 acc_site.cust_account_id = cust.cust_account_id;
178 --End Adding for bug 16387319 snuthala 6/MAR/2013
179
180 CURSOR C_GET_PARENT_DUNNING_ID(p_dunning_id number) is
181 select PARENT_DUNNING_ID
182 from IEX_DUNNINGS
183 where DUNNING_ID = p_dunning_id;
184
185
186 l_api_name CONSTANT VARCHAR2(30) := 'IEXVXMLB';
187 l_api_version NUMBER := 1.0;
188 l_commit VARCHAR2(5) ;
189 --
190 l_template_id NUMBER;
191 l_tempcode VARCHAR2(80);
192 l_addt_tempcode VARCHAR2(80);
193 l_lang VARCHAR2(6);
194 l_terr VARCHAR2(6);
195 l_query_id NUMBER;
196 l_method VARCHAR2(30);
197 l_email VARCHAR2(1000) ;
198 l_printer VARCHAR2(1000) ;
199 l_fax VARCHAR2(1000) ;
200 --
201 l_party_id NUMBER ;
202 l_account_id NUMBER ;
203 l_site_id NUMBER ;
204 l_delinquency_id NUMBER ;
205 l_user_id NUMBER ;
206 --
207 l_request_id NUMBER ;
208 l_rowid VARCHAR2(2000) ;
209 l_msg_count NUMBER ;
210 l_msg_data VARCHAR2(1000);
211 l_return_status VARCHAR2(1000);
212 l_status VARCHAR2(100);
213 l_curr_lang VARCHAR2(100);
214 l_submit_request_id NUMBER ;
215 --l_content_xml VARCHAR2(32767);
216 l_content_xml CLOB;
217 l_addt_content_xml CLOB;
218 l_doc BLOB;
219
220 l_msg VARCHAR2(1000);
221 l_query_temp_id VARCHAR2(100); -- Added for bug#8845762 by SNUTHALA on 9/14/2009
222 l_addt_query_id VARCHAR2(100);
223
224 uphase VARCHAR2(255);
225 dphase VARCHAR2(255);
226 ustatus VARCHAR2(255);
227 dstatus VARCHAR2(255);
228 l_bool BOOLEAN;
229 message VARCHAR2(32000);
230
231 l_templ_lang varchar2(10);
232 l_templ_terr varchar2(10);
233
234 l_object_type varchar2(30); -- Added for bug#8445620 by PNAVEENK on 21-4-2009
235 l_con_req_id number;
236 l_dunn_rec IEX_DUNNING_PUB.DUNNING_REC_TYPE;
237 l_return_status1 VARCHAR2(10);
238 l_return_status2 VARCHAR2(10);
239 l_msg_count1 NUMBER;
240 l_msg_data1 VARCHAR2(1000);
241 l_addt_status varchar2(10);
242 l_dunn_dunning_id NUMBER;
243 l_no_printable_invoice number;
244 l_parent_dunning_id number;
245
246 cursor c_number_printable_invoice (p_dunn_dunning_id number) is
247 select count(idt.cust_trx_id)
248 from iex_dunning_transactions idt,
249 ra_customer_trx trx
250 where idt.dunning_id = p_dunn_dunning_id
251 and idt.cust_trx_id is not null
252 and trx.customer_trx_id = idt.cust_trx_id
253 and trx.printing_option = 'PRI';
254
255 cursor c_get_document_type (p_xml_req_id number) is
256 SELECT document_type
257 FROM iex_xml_request_histories
258 WHERE xml_request_id = p_xml_req_id;
259
260 l_document_type varchar2(240);
261
262 BEGIN
263
264 -- Standard Start of API savepoint
265 SAVEPOINT SEND_COPY_PUB;
266
267 l_party_id := p_party_id;
268 l_user_id := p_user_id;
269 l_msg := 'iexvxmlb.pls:SEND_COPY:';
270
271 WriteLog(l_msg || 'Start...');
272
273
274 -- Standard call to check for call compatibility.
275 IF NOT FND_API.Compatible_API_Call ( l_api_version,
276 p_api_version_number,
277 l_api_name,
278 G_PKG_NAME )
279 THEN
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281 END IF;
282
283 -- Initialize message list IF p_init_msg_list is set to TRUE.
284 IF FND_API.to_Boolean( p_init_msg_list )
285 THEN
286 FND_MSG_PUB.initialize;
287 END IF;
288
289
290 -- Initialize API return status to SUCCESS
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292
293 --
294 -- API body
295 --
296
297 WriteLog(l_msg || ' p_party_id = ' || p_party_id );
298 WriteLog(l_msg || ' p_resource_id = ' || p_resource_id );
299 WriteLog(l_msg || ' p_level= ' || p_level );
300 WriteLog(l_msg || ' p_source_id= ' || p_source_id );
301 WriteLog(l_msg || ' p_object_type= ' || p_object_type );
302 WriteLog(l_msg || ' p_object_id= ' || p_object_id );
303 WriteLog(l_msg || ' p_dest = ' || p_dest);
304 WriteLog(l_msg || ' p_dunning_mode = ' || p_dunning_mode);
305 WriteLog(' p_org_id ' || p_org_id);
306 IF (p_level = 'CUSTOMER') then
307 null;
308 ELSIF (p_level = 'ACCOUNT') then
309 l_account_id := p_source_id;
310 ELSIF (p_level = 'BILL_TO') then
311 l_site_id := p_source_id;
312 --Start Adding for bug 16387319 snuthala 6/MAR/2013
313 if (l_site_id is not null) then
314 Open C_GET_CUST_ACCOUNT_ID(l_site_id);
315 Fetch C_GET_CUST_ACCOUNT_ID into l_account_id;
316 Close C_GET_CUST_ACCOUNT_ID;
317 end if;
318 --End Adding for bug 16387319 snuthala 6/MAR/2013
319 ELSIF (p_level = 'DELINQUENCY') then
320 l_delinquency_id := p_source_id;
321 if (l_delinquency_id is not null) then
322 Open C_Get_Del(l_delinquency_id);
323 Fetch C_Get_Del into l_account_id, l_site_id;
324 Close C_Get_Del;
325 end if;
326 END IF;
327 WriteLog(l_msg || ' l_account_id = ' || l_account_id );
328 WriteLog(l_msg || ' l_site_id = ' || l_site_id );
329 WriteLog(l_msg || ' l_delinquency_id = ' || l_delinquency_id );
330
331 -- ******************************************************************
332 -- Get Request Id first
333 -- ******************************************************************
334
335 -- The output request_id must be passed to all subsequent calls made
336 -- for this request.
337
338 -- ******************************************************************
339 -- Generate XML DATA
340 -- ******************************************************************
341
342 WriteLog(l_msg || ' p_resend=' || p_resend);
343
344 if p_request_id is not null then
345
346 -- retrieve xml data from table
347 WriteLog(l_msg || ' Retrieve XML Data...' );
348 RetrieveXmlData (
349 p_request_id => p_request_id,
350 x_return_status => l_return_status,
351 x_msg_count => l_msg_count,
352 x_msg_data => l_msg_data,
353 x_xml => l_content_xml,
354 x_addt_xml => l_addt_content_xml);
355 WriteLog(l_msg || ' End Retrieve XML Data...' );
356 --l_request_id := p_request_id;
357
358 if nvl(length(l_addt_content_xml),0) > 0 then
359 l_addt_status := 'XMLDATA';
360 WriteLog(l_msg || ' Set l_addt_status as: ' ||l_addt_status );
361 end if;
362
363 open c_get_document_type (p_request_id);
364 fetch c_get_document_type into l_document_type;
365 close c_get_document_type;
366 WriteLog(l_msg || ' Set l_document_type as: ' ||l_document_type );
367
368 else
369
370 WriteLog(l_msg || ' GetXmlData...' );
371
372 GetXmlData (
373 p_party_id => l_party_id,
374 p_bind_tbl => p_bind_tbl,
375 p_resource_id => p_resource_id,
376 p_query_id => p_query_id,
377 x_return_status => l_return_status,
378 x_msg_count => l_msg_count,
379 x_msg_data => l_msg_data,
380 x_xml => l_content_xml);
381
382 WriteLog(l_msg || ' GetXmlData status=' || l_return_status );
383 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
384 WriteLog(l_msg || ' error to get XML data');
385 --
386 x_msg_data := l_msg_data;
387 x_msg_count := l_msg_count;
388 --
389 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_XMLDATA');
390 FND_MSG_PUB.Add;
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 RAISE FND_API.G_EXC_ERROR;
393 END IF;
394
395 if p_addt_query_id is not null then
396 for i in 1..p_bind_tbl.count loop
397 if p_bind_tbl(i).key_name = 'DUNNING_ID' then
398 l_dunn_dunning_id := to_number(p_bind_tbl(i).KEY_VALUE);
399 exit;
400 end if;
401 end loop;
402 WriteLog(l_msg || ' GetAddtXmlData l_dunn_dunning_id=' || l_dunn_dunning_id );
403
404 open c_number_printable_invoice (l_dunn_dunning_id);
405 fetch c_number_printable_invoice into l_no_printable_invoice;
406 close c_number_printable_invoice;
407 WriteLog(l_msg || ' GetAddtXmlData l_no_printable_invoice=' || l_no_printable_invoice );
408
409 if l_no_printable_invoice >0 then
410 WriteLog(l_msg || ' GetXmlData for additional query' );
411 GetXmlData (
412 p_party_id => l_party_id,
413 p_bind_tbl => p_bind_tbl,
414 p_resource_id => p_resource_id,
415 p_query_id => p_addt_query_id,
416 p_is_this_addt_query => 'Y',
417 x_return_status => l_return_status2,
418 x_msg_count => l_msg_count,
419 x_msg_data => l_msg_data,
420 x_xml => l_addt_content_xml);
421
422 WriteLog(l_msg || ' GetXmlData status for additional query =' || l_return_status2 );
423 IF l_return_status2 <> FND_API.G_RET_STS_SUCCESS THEN
424 WriteLog(l_msg || ' error to get XML data for additional query');
425
426 x_msg_data := l_msg_data;
427 x_msg_count := l_msg_count;
428
429 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAIL_NOXMLDATA_ADDQRY');
430 FND_MSG_PUB.Add;
431 x_return_status := FND_API.G_RET_STS_ERROR;
432 RAISE FND_API.G_EXC_ERROR;
433 END IF;
434 --pass addt status as XMLDATA only when addt query executed successfully.
435 l_addt_status := 'XMLDATA';
436 else
437 WriteLog(l_msg || ' none of the Invoice have printing_option as PRI for dunning id: '|| l_dunn_dunning_id || ' , so no need to generate xmldata for additional query');
438 FND_FILE.PUT_LINE(FND_FILE.LOG,'None of the Invoice have printing_option as PRI for dunning id: '|| l_dunn_dunning_id || ' , so no need to generate xmldata for additional query');
439 end if; --if l_no_printable_invoice >0 then
440
441 end if; --if p_addt_query_id is not null then
442
443 WriteLog(l_msg || ' End GetXmlData...' );
444 end if;
445
446 --WriteLog(l_msg || ' xml= ' || l_content_xml );
447 l_request_id := null;
448 l_doc := empty_blob();
449
450
451 WriteLog(l_msg || ' Get Template Code' );
452
453 OPEN C_GET_TEMPCODE (p_template_id);
454 FETCH C_GET_TEMPCODE INTO l_tempcode;
455
456 IF (C_GET_TEMPCODE%NOTFOUND) THEN
457 --FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
458 --FND_MESSAGE.Set_Token ('INFO', 'Cannot find xdo template code');
459 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_XDOTEMP');
460 FND_MSG_PUB.Add;
461 x_return_status := FND_API.G_RET_STS_ERROR;
462 RAISE FND_API.G_EXC_ERROR;
463 END IF;
464 CLOSE C_GET_TEMPCODE;
465 WriteLog(l_msg || ' Template Code = ' || l_tempcode);
466
467 WriteLog(l_msg || ' Get Additional Template Code' );
468 WriteLog(l_msg || ' p_addt_template_id = ' || p_addt_template_id);
469
470 if p_addt_template_id is not null then
471 OPEN C_GET_TEMPCODE (p_addt_template_id);
472 FETCH C_GET_TEMPCODE INTO l_addt_tempcode;
473
474 IF (C_GET_TEMPCODE%NOTFOUND) THEN
475 --FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
476 --FND_MESSAGE.Set_Token ('INFO', 'Cannot find xdo template code');
477 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAIL_NOXDOTEMP_ADDQRY');
478 FND_MSG_PUB.Add;
479 x_return_status := FND_API.G_RET_STS_ERROR;
480
481 l_dunn_rec.ADDT_DELIVERY_STATUS := 'IEX_SEND_FAIL_NOXDOTEMP_ADDQRY';
482
483 if l_dunn_rec.ADDT_DELIVERY_STATUS is not null then
484
485 for i in 1..p_bind_tbl.count loop
486 if p_bind_tbl(i).key_name = 'DUNNING_ID' then
487 l_dunn_rec.DUNNING_ID := to_number(p_bind_tbl(i).KEY_VALUE);
488 exit;
489 end if;
490 end loop;
491
492 if l_dunn_rec.DUNNING_ID is not null then
493 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateRow');
494
495 IEX_DUNNING_PVT.Update_DUNNING(
496 p_api_version => 1.0
497 , p_init_msg_list => FND_API.G_FALSE
498 , p_commit => FND_API.G_TRUE
499 , p_dunning_rec => l_dunn_rec
500 , x_return_status => l_return_status1
501 , x_msg_count => l_msg_count1
502 , x_msg_data => l_msg_data1);
503
504 WriteLog(G_PKG_NAME || ' ' || l_api_name || ' - UpdateDunning status='|| l_return_status1);
505 end if;
506
507 end if;
508
509 RAISE FND_API.G_EXC_ERROR;
510 END IF;
511 CLOSE C_GET_TEMPCODE;
512 WriteLog(l_msg || ' Additional Template Code = ' || l_addt_tempcode);
513 end if;
514
515 if nvl(fnd_profile.value('IEX_USE_CUST_LANG_DUNN_LETTER'),'N') = 'N' then
516 l_templ_lang := null;
517 l_templ_terr := null;
518 else
519 --call fetch_lang_terr_of_loc to find the lang and territory of a location.
520 fetch_lang_terr_of_loc ( p_bind_tbl => p_bind_tbl,
521 p_template_code => l_tempcode,
522 x_tmpl_lang => l_templ_lang,
523 x_tmpl_terr => l_templ_terr
524 );
525
526 end if; --nvl(fnd_profile.value('IEX_USE_CUST_LANG_DUNN_LETTER'),'N') = 'N' then
527 --End adding for bug 8649857 gnramasa 3rd July 09
528
529 WriteLog('Template will be processed with language, l_templ_lang :' || l_templ_lang );
530 WriteLog('Template will be processed with territory, l_templ_terr :' || l_templ_terr );
531
532 if p_parent_request_id is not null then
533 l_con_req_id := p_parent_request_id;
534 else
535 l_con_req_id := FND_GLOBAL.Conc_Request_Id;
536 end if;
537
538 -- Added for bug#8845762 by SNUTHALA on 9/14/2009
539 --Start adding for bug 10158389 gnramasa 4th Oct 10
540 if (p_query_id is not null) then
541 Open C_GET_QUERY_TEMP_ID(p_query_id, p_template_id);
542 Fetch C_GET_QUERY_TEMP_ID into l_query_temp_id;
543 Close C_GET_QUERY_TEMP_ID;
544 end if;
545 WriteLog('Query temp id '|| l_query_temp_id);
546
547 if (p_addt_query_id is not null) then
548 Open C_GET_QUERY_TEMP_ID(p_addt_query_id, p_addt_template_id);
549 Fetch C_GET_QUERY_TEMP_ID into l_addt_query_id;
550 Close C_GET_QUERY_TEMP_ID;
551 end if;
552 WriteLog('Additional Query temp id '|| l_addt_query_id);
553 --End adding for bug 10158389 gnramasa 4th Oct 10
554
555 WriteLog(l_msg || ' insert_row' );
556 WriteLog(' before insert xml org_id ' || p_org_id);
557 -- Insert Table with XML
558 IEX_XML_PKG.insert_row (
559 px_rowid => l_rowid
560 ,px_xml_request_id => l_request_id
561 ,p_query_temp_id => l_query_temp_id -- Added for bug#8845762 by SNUTHALA on 9/14/2009
562 ,p_status => 'XMLDATA'
563 ,p_document => l_doc
564 ,p_html_document => l_doc
565 ,p_xmldata => l_content_xml
566 ,p_method => p_method
567 ,p_destination => p_dest
568 ,p_subject => p_subject
569 ,p_object_type => p_object_type
570 ,p_object_id => p_object_id
571 ,p_resource_id => p_resource_id
572 ,p_view_by => p_level
573 ,p_party_id => l_party_id
574 ,p_cust_account_id => l_account_id
575 ,p_cust_site_use_id => l_site_id
576 ,p_delinquency_id => l_delinquency_id
577 ,p_last_update_date => sysdate
578 ,p_last_updated_by => l_user_id
579 ,p_creation_date => sysdate
580 ,p_created_by => l_user_id
581 ,p_last_update_login => l_user_id
582 ,p_object_version_number => l_user_id
583 ,p_request_id => -1
584 ,p_worker_id => -1
585 ,p_confirmation_mode => null -- added by gnramasa for bug 8489610 14-May-09
586 ,p_conc_request_id => l_con_req_id --FND_GLOBAL.Conc_Request_Id -- added by gnramasa for bug 8489610 14-May-09
587 ,p_org_id => p_org_id -- added for bug 9151851
588 ,p_template_language => lower(l_templ_lang) -- added by gnramasa for bug 8489610 28-May-09
589 ,p_template_territory => upper(l_templ_terr) -- added by gnramasa for bug 8489610 28-May-09
590 ,p_document_type => l_document_type
591 ,p_addt_query_id => l_addt_query_id
592 ,p_addt_xmldata => l_addt_content_xml
593 ,p_addt_status => l_addt_status
594 ,p_addt_document => l_doc
595 ,p_addt_html_document => l_doc
596 );
597
598 COMMIT;
599
600 WriteLog(l_msg || 'l_request_id = ' || l_request_id);
601 x_request_id := l_request_id;
602
603
604 for i in 1..p_bind_tbl.count loop
605 WriteLog(l_msg || 'key_name = ' || p_bind_tbl(i).key_name);
606 WriteLog(l_msg || 'Value = ' || p_bind_tbl(i).key_value);
607 if p_bind_tbl(i).key_name = 'DUNNING_ID' then
608 l_dunn_dunning_id := to_number(p_bind_tbl(i).key_value);
609 exit;
610 end if;
611 end loop;
612
613 WriteLog(l_msg || 'l_dunn_dunning_id = ' || l_dunn_dunning_id);
614
615 Open C_GET_PARENT_DUNNING_ID(l_dunn_dunning_id);
616 Fetch C_GET_PARENT_DUNNING_ID into l_parent_dunning_id;
617 Close C_GET_PARENT_DUNNING_ID;
618
619 -- ******************************************************************
620 -- Delivery
621 -- ******************************************************************
622
623 WriteLog(l_msg || ' Delivery...' );
624
625 --Start adding for bug 8489610 by gnramasa 14-May-09
626 --Don't span the IEXXMLGEN at any time for IEX: Send dunning cp. Irrespective of the IEX_DELIVER_DUNNING_LETTERS profile value,
627 --Span only bulk xml manager at the end i.e in iexpdunb.pls
628
629 WriteLog(l_msg || ' Delivery... ' || fnd_profile.value('IEX_DELIVER_DUNNING_LETTERS'));
630 WriteLog(l_msg || ' Delivery... p_object_type ' || p_object_type);
631 WriteLog(l_msg || ' Delivery... l_parent_dunning_id ' || l_parent_dunning_id );
632
633
634 IF (((fnd_profile.value('IEX_DELIVER_DUNNING_LETTERS')='IMMEDIATE') or
635 (p_object_type not in ('PARTY' , 'IEX_ACCOUNT' , 'IEX_BILLTO' , 'IEX_DELINQUENCY','IEX_STRATEGY')) or
636 (l_parent_dunning_id IS not null)) AND -- and FND_GLOBAL.Conc_Request_Id = -1)) changed the condition to fix 16433137 SNUTHALA MAR/08/2013
637 (p_dunning_mode IS NULL)) then
638 --start added by snuthala 7442795 added if condition such that request will be submitted only if profile value is IMMEDIATE or its called from IEXRCALL
639
640 if nvl(fnd_profile.value('IEX_USE_CUST_LANG_DUNN_LETTER'),'N') = 'N' then
641
642 WriteLog(l_msg || ' Send dunning with currently logged in lang...' );
643
644 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
645 APPLICATION => 'IEX',
646 PROGRAM => 'IEXXMLGEN',
647 DESCRIPTION => 'Oracle Collections Delivery XML Process',
648 START_TIME => sysdate,
649 SUB_REQUEST => false,
650 ARGUMENT1 => l_request_id,
651 ARGUMENT2 => p_method,
652 ARGUMENT3 => p_dest,
653 ARGUMENT4 => p_subject,
654 ARGUMENT5 => l_tempcode,
655 ARGUMENT6 => null, -- Send dunning letter by using the current logged in language
656 ARGUMENT7 => null);
657 else
658 WriteLog(l_msg || ' Send dunning with the language from the site' );
659
660 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
661 APPLICATION => 'IEX',
662 PROGRAM => 'IEXXMLGEN',
663 DESCRIPTION => 'Oracle Collections Delivery XML Process',
664 START_TIME => sysdate,
665 SUB_REQUEST => false,
666 ARGUMENT1 => l_request_id,
667 ARGUMENT2 => p_method,
668 ARGUMENT3 => p_dest,
669 ARGUMENT4 => p_subject,
670 ARGUMENT5 => l_tempcode,
671 ARGUMENT6 => lower(l_templ_lang),
672 ARGUMENT7 => upper(l_templ_terr));
673 end if; --profile value lang
674 end if; --if (FND_GLOBAL.Conc_Request_Id = -1) or (fnd_profile.value('IEX_DELIVER_DUNNING_LETTERS')='IMMEDIATE') then
675 --End adding for bug 8489610 by gnramasa 14-May-09
676
677 COMMIT;
678
679 WriteLog(l_msg || ' delivery xml : concurrent request id= ' || l_submit_request_id );
680 x_request_id := l_request_id;
681
682 COMMIT WORK;
683
684 -- Standard call to get message count and IF count is 1, get message info.
685 FND_MSG_PUB.Count_And_Get
686 ( p_count => x_msg_count,
687 p_data => x_msg_data );
688
689 EXCEPTION
690 WHEN FND_API.G_EXC_ERROR THEN
691 COMMIT WORK;
692 -- ROLLBACK TO SEND_COPY_PUB;
693 WriteLog(l_msg || ' Exc_Error:'|| SQLERRM);
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 -- Update Table with Status
696 l_status := 'FAILURE';
697 IEX_XML_PKG.update_row (
698 p_xml_request_id => l_request_id
699 ,p_status => l_status
700 );
701 FND_MSG_PUB.Count_And_Get
702 ( p_count => x_msg_count,
703 p_data => x_msg_data
704 );
705
706 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
707 COMMIT WORK;
708 -- ROLLBACK TO SEND_COPY_PUB;
709 WriteLog(l_msg || ' UnExc_Error:'|| SQLERRM);
710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711 -- Update Table with Status
712 l_status := 'FAILURE';
713 IEX_XML_PKG.update_row (
714 p_xml_request_id => l_request_id
715 ,p_status => l_status
716 );
717 FND_MSG_PUB.Count_And_Get
718 ( p_count => x_msg_count,
719 p_data => x_msg_data
720 );
721
722 WHEN OTHERS THEN
723 COMMIT WORK;
724 -- ROLLBACK TO SEND_COPY_PUB;
725 WriteLog(l_msg || ' Other:'|| SQLERRM);
726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
727 -- Update Table with Status
728 l_status := 'FAILURE';
729 IEX_XML_PKG.update_row (
730 p_xml_request_id => l_request_id
731 ,p_status => l_status
732 );
733 FND_MSG_PUB.Count_And_Get
734 ( p_count => x_msg_count,
735 p_data => x_msg_data
736 );
737
738 END Send_COPY;
739
740
741 /*
742 Overview: This function is to retrieve the existing xml data from
743 iex_xml_request_histories table by the xml_request_id.
744 */
745 procedure RetrieveXmlData
746 (
747 p_request_id IN number
748 , x_return_status OUT NOCOPY varchar2
749 , x_msg_count OUT NOCOPY NUMBER
750 , x_msg_data OUT NOCOPY VARCHAR2
751 , x_xml OUT NOCOPY clob
752 , x_addt_xml OUT NOCOPY clob --added for bug 9970624 gnramasa 4th Aug 10
753 )
754 IS
755 CURSOR C_GET_XML (IN_REQUEST_ID NUMBER) IS
756 SELECT xmldata, addt_xmldata
757 FROM IEX_XML_REQUEST_HISTORIES
758 WHERE xml_request_id = in_request_id;
759
760 l_xml CLOB;
761 l_addt_xml CLOB;
762
763 BEGIN
764
765 WriteLog('begin RetrieveXmlData()');
766 WriteLog('RetrieveXmlData: p_request_id = '||p_request_id);
767
768 if (p_request_id is null) then
769 FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
770 FND_MESSAGE.Set_Token ('INFO', 'No Request_Id');
771 FND_MSG_PUB.Add;
772 x_return_status := FND_API.G_RET_STS_ERROR;
773 RAISE FND_API.G_EXC_ERROR;
774 end if;
775
776 OPEN C_GET_XML (p_request_id);
777 FETCH C_GET_XML INTO l_xml, l_addt_xml;
778
779 IF (C_GET_XML%NOTFOUND) THEN
780 FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
781 FND_MESSAGE.Set_Token ('INFO', 'Cannot find xmldata');
782 FND_MSG_PUB.Add;
783 x_return_status := FND_API.G_RET_STS_ERROR;
784 RAISE FND_API.G_EXC_ERROR;
785 END IF;
786 CLOSE C_GET_XML;
787
788 x_xml := l_xml;
789 x_addt_xml := l_addt_xml;
790 WriteLog('get XmlData');
791 x_return_status := FND_API.G_RET_STS_SUCCESS;
792 FND_MSG_PUB.Count_And_Get
793 ( p_count => x_msg_count,
794 p_data => x_msg_data );
795
796
797 EXCEPTION
798
799 WHEN FND_API.G_EXC_ERROR THEN
800 WriteLog('RetrieveXmlData: Exc_Error:'|| SQLERRM);
801 x_return_status := FND_API.G_RET_STS_ERROR;
802 FND_MSG_PUB.Count_And_Get
803 ( p_count => x_msg_count,
804 p_data => x_msg_data);
805 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
806 WriteLog('RetrieveXmlData: Exc_Error:'|| SQLERRM);
807 x_return_status := FND_API.G_RET_STS_ERROR;
808 FND_MSG_PUB.Count_And_Get
809 ( p_count => x_msg_count,
810 p_data => x_msg_data);
811 when others then
812 WriteLog('RetrieveXmlData: Exc_Error:'|| SQLERRM);
813 x_return_status := FND_API.G_RET_STS_ERROR;
814 FND_MSG_PUB.Count_And_Get
815 ( p_count => x_msg_count,
816 p_data => x_msg_data);
817
818 END RetrieveXmlData;
819
820
821 /*
822 Overview: This function is to get the xml data from a query which is defined by the dunning letter template.
823 Parameter: p_party_id : party_id
824 Return: clob contains the result of the query
825 creation date: 08/25/2004
826 author: ctlee
827 Note: test only
828 */
829 procedure GetXmlData
830 (
831 p_party_id IN number
832 , p_resource_id IN number
833 , p_bind_tbl IN IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL
834 , p_query_id IN number
835 , p_is_this_addt_query IN VARCHAR --added for bug 9970624 gnramasa 4th Aug 10
836 , x_return_status OUT NOCOPY varchar2
837 , x_msg_count OUT NOCOPY NUMBER
838 , x_msg_data OUT NOCOPY VARCHAR2
839 , x_xml OUT NOCOPY clob
840 )
841 IS
842 CURSOR C_GET_QUERY (IN_QUERY_ID NUMBER) IS
843 --Bug5370344. Use the New column.
844 -- SELECT upper(statement)
845 SELECT UPPER(ADDITIONAL_QUERY), UPPER(STATEMENT)
846 FROM IEX_XML_QUERIES
847 WHERE query_id = in_query_id
848 and trunc(sysdate) between trunc(nvl(start_date, sysdate)) and
849 trunc(nvl(end_date, sysdate))
850 and enabled_flag = 'Y';
851
852 --Bug5370344 l_query Varchar2(4000);
853 l_query clob;
854 l_query_stmt varchar2(4000);
855 l_new_query clob;
856 l_chk_query clob; -- for bind variable matching validation without affecting perform bug# 14172720
857 qry_string Varchar2(4000);
858 --qryCtx DBMS_XMLQuery.ctxType; -- bug# 14172720 : To get rid of error 'In or Out parameters missing at Index...' replacing DBMS_XMLQuery with DBMS_XMLGen
859 qryCtx DBMS_XMLGen.ctxHandle; -- bug# 14172720
860 result CLOB;
861 xmlstr varchar2(32767);
862 line varchar2(4000);
863
864 l_bind_name varchar2(150);
865 l_bind_name2 varchar2(150); -- bug# 14172720
866 l_bind_type varchar2(25);
867 l_bind_val varchar2(240);
868 l_cnt number;
869 l_found number;
870 l_bind_cnt number;
871 l_bind_found number;
872 l_bind_found1 number;
873
874 len number;
875 l_start number;
876 l_end number;
877 sMsg varchar2(250);
878
879 v_cursor NUMBER;
880 v_numrows NUMBER;
881
882 --Begin Bug#6743267 24-Jul-2008 barathsr
883 l_dataHdr_clob clob;
884 l_dataHdrTag Varchar2(100);
885 l_dataHeader varchar2(1000) := '';
886 l_dataHdrQry varchar2(1000) := '';
887 l_rowSetName varchar2(1000) := '';
888 TYPE refCur IS REF CURSOR;
889 xml_element refCur;
890 --End Bug#6743267 24-Jul-2008 barathsr
891
892 /* begin bug 4732366 - ctlee - use set bind variables 11/28/2005 */
893 -- l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;
894 -- l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
895 l_temp_index integer;
896
897 l_temp_s_first varchar2(4000);
898 l_temp_s_second varchar2(4000);
899
900 TYPE VAR_COUNT_REC IS RECORD(
901 VAR_COUNT integer);
902
903 TYPE VAR_COUNT_tbl IS TABLE OF VAR_COUNT_REC INDEX BY binary_integer;
904 l_var_count_rec var_count_rec;
905 l_var_count_tbl var_count_tbl;
906 l_bind_count integer;
907 /* end bug 4732366 - ctlee - use set bind variables 11/28/2005 */
908 l_is_this_addt_query varchar2(10);
909 l_dunn_rec IEX_DUNNING_PUB.DUNNING_REC_TYPE;
910 l_return_status1 VARCHAR2(10);
911 l_msg_count1 NUMBER;
912 l_msg_data1 VARCHAR2(1000);
913 l_addt_delivery_status varchar2(50);
914
915 BEGIN
916
917 WriteLog('begin test GetXmlData()');
918 WriteLog('GetXmlData: p_query_id = ' || p_query_id);
919 WriteLog('GetXmlData: p_is_this_addt_query = ' || p_is_this_addt_query);
920
921 if p_is_this_addt_query is not null then
922 l_is_this_addt_query := p_is_this_addt_query;
923 else
924 l_is_this_addt_query := 'N';
925 end if;
926 WriteLog('GetXmlData: l_is_this_addt_query = ' || l_is_this_addt_query);
927
928 OPEN C_GET_QUERY (p_query_id);
929 FETCH C_GET_QUERY INTO l_query, l_query_stmt;
930
931 IF (C_GET_QUERY%NOTFOUND) THEN
932 if l_is_this_addt_query = 'Y' then
933 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NOQUERY_ADDQRY');
934 l_addt_delivery_status := 'IEX_SEND_FAILED_NOQUERY_ADDQRY';
935 else
936 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_QUERY');
937 end if;
938 FND_MSG_PUB.Add;
939 x_return_status := FND_API.G_RET_STS_ERROR;
940 RAISE FND_API.G_EXC_ERROR;
941 END IF;
942 CLOSE C_GET_QUERY;
943
944 -- begin to check the query and bind var are matched or not;
945 WriteLog('GetXmlData: chk the bind var and query');
946
947 -- replace bind var by l_bind_tbl
948 l_bind_cnt := p_bind_tbl.count;
949 l_cnt := 0;
950 WriteLog('GetXmlData: l_bind_cnt = '||l_bind_cnt);
951
952 if nvl(length(l_query),0) > 0 then --Added for Bug 10401991 02-Feb-2011 barathsr
953 l_new_query := l_query;
954 else
955 l_new_query := l_query_stmt;
956 end if;
957
958 WriteLog('GetXmlData: calling DBMS_XMLQuery api');
959 qryCtx := DBMS_XMLGen.newContext(l_new_query); -- bug# 14172720
960 l_chk_query := l_new_query; -- bug# 14172720
961
962
963 dbms_xmlgen.clearbindvalues (qryCtx);
964
965 for j in 1..l_bind_cnt
966 loop
967 l_cnt := 0;
968 l_bind_name := ':' || upper(p_bind_tbl(j).Key_name);
969 l_bind_name2 := upper(p_bind_tbl(j).Key_name); -- bug# 14172720
970 l_bind_type := p_bind_tbl(j).Key_Type;
971 l_bind_val := p_bind_tbl(j).Key_Value;
972
973 if l_bind_val is null then
974 -- l_bind_val := 'null'; Changed for bug 14603156 snuthala 18/sep/2012
975 l_bind_val := null;
976 end if;
977
978 /*if UPPER(l_bind_type) = 'DATE' THEN -- bug# 14172720
979 l_bind_val := '''' || l_bind_val || '''';
980 end if; */
981
982 l_bind_found := instr( l_new_query, l_bind_name);
983
984 WriteLog('GetXmlData:replace bind_name = '||l_bind_name || ' with val=' || l_bind_val ||'; cnt=' || l_cnt);
985
986 if l_bind_found > 0 then
987 l_dataHeader := l_dataHeader||'XMLElement("'||upper(p_bind_tbl(j).Key_name)||'",'''||l_bind_val||''').getclobval()||';
988 DBMS_XMLGen.setBindValue(qryCtx,l_bind_name2,l_bind_val); -- bug# 14172720
989 WriteLog('GetXmlData:replace bind_name2 = '||l_bind_name2 || ' with val=' || l_bind_val ||'; cnt=' || l_cnt); -- bug# 14172720
990 end if;
991
992 l_chk_query := replace(l_chk_query, l_bind_name, l_bind_name2); -- bug# 14172720
993
994 l_var_count_rec.var_count := l_cnt;
995 l_var_count_tbl(j) := l_var_count_rec;
996
997 end loop;
998
999 l_dataHeader := substr(l_dataHeader,0,length(l_dataHeader)-2);
1000
1001 l_cnt := 0;
1002 l_bind_found := instr( l_new_query, ':RESOURCE_ID');
1003 if l_bind_found > 0 then
1004 --DBMS_XMLQuery.setBindValue(qryCtx, 'RESOURCE_ID', p_resource_id); -- bug# 14172720
1005 DBMS_XMLGen.setBindValue(qryCtx, 'RESOURCE_ID', p_resource_id);
1006 l_chk_query := replace(l_chk_query, ':RESOURCE_ID', 'RESOURCE_ID'); -- bug# 14172720
1007 end if;
1008
1009 WriteLog('GetXmlData: resource_id : l_cnt = '||l_cnt || ' with ' || p_resource_id);
1010 WriteLog('GetXmlData: after replace');
1011
1012 -- relplace l_new_query with l_chk_query for bind variable matching validation without affecting perform bug# 14172720
1013 l_found := instr(l_chk_query, ':');
1014 if (l_found > 0) then
1015 WriteLog('GetXmlData: l_found=' || l_found);
1016 WriteLog('GetXmlData: var='||substr(l_chk_query, l_found, 12));
1017 -- cannot execute the query; the bind variables are not enough;
1018 WriteLog('GetXmlData: bind var and query are not matched');
1019 --FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
1020 --FND_MESSAGE.Set_Token ('INFO', 'Bind Variables are not enough for query_id: ' || p_query_id);
1021 if l_is_this_addt_query = 'Y' then
1022 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAIL_NOBINDVAR_ADDQRY');
1023 l_addt_delivery_status := 'IEX_SEND_FAIL_NOBINDVAR_ADDQRY';
1024 else
1025 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_BINDVAR');
1026 end if;
1027 FND_MSG_PUB.Add;
1028 x_return_status := FND_API.G_RET_STS_ERROR;
1029 RAISE FND_API.G_EXC_ERROR;
1030 end if;
1031
1032 WriteLog('GetXmlData: bind var and query are matched');
1033 WriteLog('GetXmlData: end to chk the bind var and query');
1034 -- end to check the query and bind var are matched or not;
1035
1036
1037 /* end bug 4732366 - ctlee - use set bind variables 11/28/2005 */
1038
1039 WriteLog('GetXmlData: l_new_query=' || l_new_query);
1040 len := length(l_new_query)/100;
1041 WriteLog('GetXmlData: l_new_query len=' || len);
1042 /**
1043 for i in 1..len loop
1044 l_start := 100 * (i-1) + 1;
1045 l_end := 100 * i;
1046 sMsg := substr(l_new_query, l_start, l_end);
1047 dbms_output.put_line(sMsg);
1048 end loop;
1049 **/
1050
1051 -- begin bug 4732366 - ctlee - use set bind variables 12/28/2005
1052 -- no check of the query
1053 -- execute the query to see if any records returned.
1054
1055 /* commented out by bug# 14172720
1056 BEGIN
1057 WriteLog('GetXmlData: execute query');
1058 v_cursor := DBMS_SQL.OPEN_CURSOR;
1059 DBMS_SQL.parse( v_cursor, l_new_query, 1);
1060 v_numrows := DBMS_SQL.EXECUTE( v_cursor);
1061 v_numrows := DBMS_SQL.FETCH_ROWS( v_cursor);
1062 DBMS_SQL.CLOSE_CURSOR( v_cursor);
1063 EXCEPTION
1064 when others then
1065 if l_is_this_addt_query = 'Y' then
1066 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAIL_CANTEXEQY_ADDQRY');
1067 l_addt_delivery_status := 'IEX_SEND_FAIL_CANTEXEQY_ADDQRY';
1068 else
1069 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_CANT_EXEC_QRY');
1070 end if;
1071 FND_MESSAGE.Set_Token ('ID', p_query_id);
1072 FND_MSG_PUB.Add;
1073 x_return_status := FND_API.G_RET_STS_ERROR;
1074 RAISE FND_API.G_EXC_ERROR;
1075 END;
1076
1077 if (v_numrows > 0 ) then
1078 WriteLog('GetXmlData: execute query, numrows > 0');
1079 else
1080 WriteLog('GetXmlData: execute query, no rows');
1081 if l_is_this_addt_query = 'Y' then
1082 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_DATA_ADDQRY');
1083 l_addt_delivery_status := 'IEX_SEND_FAILED_NO_DATA_ADDQRY';
1084 else
1085 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_DATA');
1086 end if;
1087 FND_MSG_PUB.Add;
1088 x_return_status := FND_API.G_RET_STS_ERROR;
1089 RAISE FND_API.G_EXC_ERROR;
1090 end if;
1091 */
1092 -- end bug 4732366 - ctlee - use set bind variables 11/28/2005
1093
1094 WriteLog('GetXmlData: calling DBMS_XMLQuery api');
1095 --qryCtx := DBMS_XMLQuery.newContext(l_query);
1096
1097 --****DBMS_XMLQuery.setRowTag(qryCtx, 'COLLECTION');
1098 -- set the rowset header to null
1099 --****DBMS_XMLQuery.setRowSetTag(qryCtx, 'COLLECTIONSET');
1100
1101 --Begin Bug#6743267 24-Jul-2008 barathsr
1102 --****l_rowSetName := '<COLLECTIONSET>';
1103 --End Bug#6743267 24-Jul-2008 barathsr
1104
1105 /***
1106 --Set bind values
1107 --DBMS_XMLQuery.setBindValue(qryCtx, 'PARTY_ID', p_party_id);
1108 WriteLog('GetXmlData: set bind values');
1109 l_bind_cnt := l_bind_tbl.count;
1110 l_cnt := 0;
1111 WriteLog('GetXmlData: l_bind_cnt = '||l_bind_cnt);
1112 for j in 1..l_bind_cnt
1113 loop
1114 l_cnt := 0;
1115 l_bind_name := ':' || upper(p_bind_tbl(j).Key_name);
1116 l_bind_type := p_bind_tbl(j).Key_Type;
1117 l_bind_val := p_bind_tbl(j).Key_Value;
1118 -- chk how many bind var in the query
1119 l_bind_found := instr( l_query, l_bind_name);
1120 WHILE (l_bind_found > 0)
1121 LOOP
1122 EXIT when l_bind_found = 0;
1123 --
1124 l_cnt := l_cnt + 1;
1125 DBMS_XMLQuery.setBindValue(qryCtx, l_bind_name, l_bind_val);
1126 l_bind_found := instr( l_query, l_bind_name);
1127 END LOOP;
1128 WriteLog('GetXmlData: set bind_name = '||l_bind_name || ' with val=' || l_bind_val ||'; cnt=' || l_cnt);
1129 end loop;
1130
1131 l_cnt := 0;
1132 l_bind_found := instr( l_query, ':RESOURCE_ID');
1133 WHILE (l_bind_found > 0)
1134 LOOP
1135 EXIT when l_bind_found = 0;
1136 --
1137 l_cnt := l_cnt + 1;
1138 DBMS_XMLQuery.setBindValue(qryCtx, 'RESOURCE_ID', p_resource_id);
1139 l_bind_found := instr( l_query, ':RESOURCE_ID');
1140 END LOOP;
1141 WriteLog('GetXmlData: resource_id : l_cnt = '||l_cnt);
1142 ***/
1143
1144 /* begin bug 4732366 - ctlee - use set bind variables 12/28/2005 */
1145 /* WriteLog('GetXmlData: set bind values');
1146 l_bind_cnt := p_bind_tbl.count;
1147 --l_cnt := 0;
1148 WriteLog('GetXmlData: l_bind_cnt = '||l_bind_cnt);
1149 for j in 1..l_bind_cnt
1150 loop
1151 --l_cnt := 0;
1152 l_bind_name := upper(p_bind_tbl(j).Key_name);
1153 l_bind_type := p_bind_tbl(j).Key_Type;
1154 l_bind_val := p_bind_tbl(j).Key_Value;
1155 -- l_bind_count := l_var_count_tbl(j).var_count;
1156 -- chk how many bind var in the query
1157 -- l_bind_found := instr( l_query, l_bind_name);
1158 -- WHILE (l_bind_found > 0)
1159 WHILE (l_bind_count > 0)
1160 LOOP
1161 EXIT when l_bind_count = 0;
1162 --
1163 --l_cnt := l_cnt + 1;
1164 DBMS_XMLQuery.setBindValue(qryCtx, l_bind_name||l_bind_count, l_bind_val);
1165 WriteLog('GetXmlData: set bind_name = '||l_bind_name || l_bind_count || ' with val=' || l_bind_val );
1166 -- l_bind_found := instr( l_query, l_bind_name);
1167 l_bind_count := l_bind_count - 1;
1168
1169 END LOOP;
1170
1171 end loop; */
1172
1173 /* end bug 4732366 - ctlee - use set bind variables 11/28/2005 */
1174
1175
1176 --Begin Bug#6743267 24-Jul-2008 barathsr
1177 -- Create the XML DataHeader
1178 l_dataHdrQry := 'SELECT '||l_dataHeader||' FROM DUAL';
1179
1180 OPEN xml_element FOR l_dataHdrQry;
1181 FETCH xml_element INTO l_dataHdr_clob;
1182 CLOSE xml_element;
1183
1184 WriteLog('The XML DataHeader is '||l_dataHdr_clob);
1185
1186
1187 --End Bug#6743267 24-Jul-2008 barathsr
1188 --DBMS_XMLQuery.setRowTag(qryCtx, 'COLLECTION'); -- bug# 14172720
1189 DBMS_XMLGen.setRowTag(qryCtx, 'COLLECTION'); -- bug# 14172720
1190 -- set the rowset header to null
1191 --DBMS_XMLQuery.setRowSetTag(qryCtx, 'COLLECTIONSET'); -- bug# 14172720
1192 DBMS_XMLGen.setRowSetTag(qryCtx, 'COLLECTIONSET'); -- bug# 14172720
1193 --Begin Bug#6743267 24-Jul-2008 barathsr
1194 l_rowSetName := '<COLLECTIONSET>';
1195 --End Bug#6743267 24-Jul-2008 barathsr
1196
1197
1198
1199 -- now get the result
1200 WriteLog('GetXmlData: getXml');
1201 --result := DBMS_XMLQuery.getXml(qryCtx); -- bug# 14172720
1202 result := DBMS_XMLGen.getXml(qryCtx); -- bug# 14172720
1203 WriteLog('GetXmlData: get result');
1204 --Begin Bug#6743267 24-Jul-2008 barathsr
1205 -- RowSet and Row
1206 result := replace(result,l_rowSetName,l_rowSetName||''||l_dataHdr_clob);
1207 --End Bug#6743267 24-Jul-2008 barathsr
1208 x_xml := result;
1209
1210 if (result is null) then
1211 WriteLog('GetXmlData: no result');
1212 --FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
1213 --FND_MESSAGE.Set_Token ('INFO', 'No XML result');
1214 if l_is_this_addt_query = 'Y' then
1215 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_DATA_ADDQRY');
1216 l_addt_delivery_status := 'IEX_SEND_FAILED_NO_DATA_ADDQRY';
1217 else
1218 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_DATA');
1219 end if;
1220 FND_MSG_PUB.Add;
1221 x_return_status := FND_API.G_RET_STS_ERROR;
1222 RAISE FND_API.G_EXC_ERROR;
1223 end if;
1224
1225 --close context
1226 WriteLog('GetXmlData: close context ');
1227 --DBMS_XMLQuery.closeContext(qryCtx); -- bug# 14172720
1228 dbms_xmlgen.clearbindvalues (qryCtx); -- bug# 14172720
1229 DBMS_XMLGen.closeContext(qryCtx); -- bug# 14172720
1230
1231
1232 WriteLog('GetXmlData: end GetXmlData()');
1233
1234 x_return_status := FND_API.G_RET_STS_SUCCESS;
1235 FND_MSG_PUB.Count_And_Get
1236 ( p_count => x_msg_count,
1237 p_data => x_msg_data );
1238
1239
1240 EXCEPTION
1241
1242 WHEN FND_API.G_EXC_ERROR THEN
1243 WriteLog('GetXmlData: Exc_Error:'|| SQLERRM);
1244
1245 if l_is_this_addt_query = 'Y' then
1246 l_dunn_rec.ADDT_DELIVERY_STATUS := l_addt_delivery_status;
1247 WriteLog('GetXmlData: l_dunn_rec.ADDT_DELIVERY_STATUS:'|| l_dunn_rec.ADDT_DELIVERY_STATUS);
1248
1249 if l_dunn_rec.ADDT_DELIVERY_STATUS is not null then
1250
1251 for i in 1..p_bind_tbl.count loop
1252 if p_bind_tbl(i).key_name = 'DUNNING_ID' then
1253 l_dunn_rec.DUNNING_ID := to_number(p_bind_tbl(i).KEY_VALUE);
1254 exit;
1255 end if;
1256 end loop;
1257 WriteLog('GetXmlData: l_dunn_rec.DUNNING_ID:'|| l_dunn_rec.DUNNING_ID);
1258
1259 if l_dunn_rec.DUNNING_ID is not null then
1260 WriteLog('GetXmlData: Update dunning Row');
1261
1262 IEX_DUNNING_PVT.Update_DUNNING(
1263 p_api_version => 1.0
1264 , p_init_msg_list => FND_API.G_FALSE
1265 , p_commit => FND_API.G_TRUE
1266 , p_dunning_rec => l_dunn_rec
1267 , x_return_status => l_return_status1
1268 , x_msg_count => l_msg_count1
1269 , x_msg_data => l_msg_data1);
1270
1271 WriteLog('GetXmlData: - UpdateDunning status='|| l_return_status1);
1272 end if;
1273 end if;
1274 end if;
1275
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277 FND_MSG_PUB.Count_And_Get
1278 ( p_count => x_msg_count,
1279 p_data => x_msg_data);
1280
1281 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1282 WriteLog('GetXmlData: Exc_Error:'|| SQLERRM);
1283 x_return_status := FND_API.G_RET_STS_ERROR;
1284 FND_MSG_PUB.Count_And_Get
1285 ( p_count => x_msg_count,
1286 p_data => x_msg_data);
1287 when others then
1288 WriteLog('GetXmlData: Exc_Error:'|| SQLERRM);
1289 x_return_status := FND_API.G_RET_STS_ERROR;
1290 FND_MSG_PUB.Count_And_Get
1291 ( p_count => x_msg_count,
1292 p_data => x_msg_data);
1293 End GetXmlData;
1294
1295
1296
1297 /*
1298 Overview: This function is to retrieve the existing xml data from
1299 iex_xml_request_histories table by the xml_request_id.
1300 */
1301 function getCurrDeliveryMethod return varchar2
1302 IS
1303 CURSOR C_GET_SETUP IS
1304 SELECT NVL(PREFERENCE_VALUE, 'FFM')
1305 FROM IEX_APP_PREFERENCES_VL
1306 WHERE PREFERENCE_NAME= 'COLLECTIONS DELIVERY METHOD';
1307
1308 l_dmethod VARCHAR2(10);
1309
1310 BEGIN
1311
1312 WriteLog('begin getCurrDeliveryMethod()');
1313
1314 OPEN C_GET_SETUP;
1315 FETCH C_GET_SETUP INTO l_dmethod;
1316
1317 IF (C_GET_SETUP%NOTFOUND) THEN
1318 l_dmethod := '';
1319 END IF;
1320 CLOSE C_GET_SETUP;
1321
1322 WriteLog('get Delivery Method');
1323 return l_dmethod;
1324
1325 END getCurrDeliveryMethod;
1326
1327
1328
1329 Procedure WriteLog ( p_msg IN VARCHAR2)
1330 IS
1331 BEGIN
1332 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1333 iex_debug_pub.LogMessage (p_msg);
1334 END IF;
1335
1336 --dbms_output.put_line(p_msg);
1337
1338 END WriteLog;
1339
1340
1341
1342
1343 BEGIN
1344 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1345
1346 END IEX_SEND_XML_PVT;