[Home] [Help]
PACKAGE BODY: APPS.IEX_SEND_XML_PVT
Source
1 PACKAGE BODY IEX_SEND_XML_PVT as
2 /* $Header: iexvxmlb.pls 120.6.12010000.2 2008/08/06 09:05:40 schekuri 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 Send_COPY(
22 p_Api_Version_Number IN NUMBER,
23 p_Init_Msg_List IN VARCHAR2 ,
24 p_Commit IN VARCHAR2 ,
25 p_resend IN VARCHAR2 ,
26 p_request_id IN NUMBER ,
27 p_user_id IN NUMBER,
28 p_party_id IN NUMBER,
29 p_subject IN VARCHAR2,
30 p_bind_tbl IN IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL,
31 p_template_id IN NUMBER,
32 p_resource_id IN NUMBER,
33 p_query_id IN NUMBER,
34 p_method IN VARCHAR2,
35 p_dest IN VARCHAR2,
36 p_level IN VARCHAR2,
37 p_source_id IN NUMBER,
38 p_object_type IN VARCHAR2,
39 p_object_id IN NUMBER,
40 X_Request_ID OUT NOCOPY NUMBER,
41 X_Return_Status OUT NOCOPY VARCHAR2,
42 X_Msg_Count OUT NOCOPY NUMBER,
43 X_Msg_Data OUT NOCOPY VARCHAR2
44 )
45 IS
46
47 CURSOR C_GET_DEL (IN_DEL_ID NUMBER) IS
48 SELECT cust_account_id, customer_site_use_id
49 FROM IEX_DELINQUENCIES
50 WHERE delinquency_ID = in_del_ID;
51 --
52 CURSOR C_GET_TEMPCODE (IN_TEMP_ID NUMBER) IS
53 SELECT template_code,
54 default_language,
55 default_territory
56 FROM XDO_TEMPLATES_VL
57 WHERE template_id = in_TEMP_ID
58 --AND Application_id = 695;
59 AND Application_short_name = 'IEX';
60 --
61 l_api_name CONSTANT VARCHAR2(30) := 'IEXVXMLB';
62 l_api_version NUMBER := 1.0;
63 l_commit VARCHAR2(5) ;
64 --
65 l_template_id NUMBER;
66 l_tempcode VARCHAR2(80);
67 l_lang VARCHAR2(6);
68 l_terr VARCHAR2(6);
69 l_query_id NUMBER;
70 l_method VARCHAR2(30);
71 l_email VARCHAR2(1000) ;
72 l_printer VARCHAR2(1000) ;
73 l_fax VARCHAR2(1000) ;
74 --
75 l_party_id NUMBER ;
76 l_account_id NUMBER ;
77 l_site_id NUMBER ;
78 l_delinquency_id NUMBER ;
79 l_user_id NUMBER ;
80 --
81 l_request_id NUMBER ;
82 l_rowid VARCHAR2(2000) ;
83 l_msg_count NUMBER ;
84 l_msg_data VARCHAR2(1000);
85 l_return_status VARCHAR2(1000);
86 l_status VARCHAR2(100);
87 l_curr_lang VARCHAR2(100);
88 l_submit_request_id NUMBER ;
89 --l_content_xml VARCHAR2(32767);
90 l_content_xml CLOB;
91 l_doc BLOB;
92
93 l_msg VARCHAR2(1000);
94
95 uphase VARCHAR2(255);
96 dphase VARCHAR2(255);
97 ustatus VARCHAR2(255);
98 dstatus VARCHAR2(255);
99 l_bool BOOLEAN;
100 message VARCHAR2(32000);
101 l_bind_cnt number;
102 l_bind_name varchar2(150);
103 l_bind_val varchar2(240);
104 l_loc_lang varchar2(10);
105 l_loc_terr varchar2(10);
106 l_iso_lang varchar2(10);
107 l_iso_lang1 varchar2(10);
108 l_templ_lang varchar2(10);
109 l_templ_terr varchar2(10);
110 l_templ_terr1 varchar2(10);
111 l_no_terr number;
112
113 BEGIN
114
115 -- Standard Start of API savepoint
116 SAVEPOINT SEND_COPY_PUB;
117
118 l_party_id := p_party_id;
119 l_user_id := p_user_id;
120 l_msg := 'iexvxmlb.pls:SEND_COPY:';
121
122 WriteLog(l_msg || 'Start...');
123
124
125 -- Standard call to check for call compatibility.
126 IF NOT FND_API.Compatible_API_Call ( l_api_version,
127 p_api_version_number,
128 l_api_name,
129 G_PKG_NAME )
130 THEN
131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
132 END IF;
133
134 -- Initialize message list IF p_init_msg_list is set to TRUE.
135 IF FND_API.to_Boolean( p_init_msg_list )
136 THEN
137 FND_MSG_PUB.initialize;
138 END IF;
139
140
141 -- Initialize API return status to SUCCESS
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143
144 --
145 -- API body
146 --
147
148 WriteLog(l_msg || ' p_party_id = ' || p_party_id );
149 WriteLog(l_msg || ' p_resource_id = ' || p_resource_id );
150 WriteLog(l_msg || ' p_level= ' || p_level );
151 WriteLog(l_msg || ' p_source_id= ' || p_source_id );
152 WriteLog(l_msg || ' p_object_type= ' || p_object_type );
153 WriteLog(l_msg || ' p_object_id= ' || p_object_id );
154 WriteLog(l_msg || ' p_dest = ' || p_dest);
155 IF (p_level = 'CUSTOMER') then
156 null;
157 ELSIF (p_level = 'ACCOUNT') then
158 l_account_id := p_source_id;
159 ELSIF (p_level = 'BILL_TO') then
160 l_site_id := p_source_id;
161 ELSIF (p_level = 'DELINQUENCY') then
162 l_delinquency_id := p_source_id;
163 if (l_delinquency_id is not null) then
164 Open C_Get_Del(l_delinquency_id);
165 Fetch C_Get_Del into l_account_id, l_site_id;
166 Close C_Get_Del;
167 end if;
168 END IF;
169 WriteLog(l_msg || ' l_account_id = ' || l_account_id );
170 WriteLog(l_msg || ' l_site_id = ' || l_site_id );
171 WriteLog(l_msg || ' l_delinquency_id = ' || l_delinquency_id );
172
173 -- ******************************************************************
174 -- Get Request Id first
175 -- ******************************************************************
176
177 -- The output request_id must be passed to all subsequent calls made
178 -- for this request.
179
180 -- ******************************************************************
181 -- Generate XML DATA
182 -- ******************************************************************
183
184 WriteLog(l_msg || ' p_resend=' || p_resend);
185
186 if p_request_id is not null then
187
188 -- retrieve xml data from table
189 WriteLog(l_msg || ' Retrieve XML Data...' );
190 RetrieveXmlData (
191 p_request_id => p_request_id,
192 x_return_status => l_return_status,
193 x_msg_count => l_msg_count,
194 x_msg_data => l_msg_data,
195 x_xml => l_content_xml );
196 WriteLog(l_msg || ' End Retrieve XML Data...' );
197 --l_request_id := p_request_id;
198
199 else
200
201 WriteLog(l_msg || ' GetXmlData...' );
202
203 GetXmlData (
204 p_party_id => l_party_id,
205 p_bind_tbl => p_bind_tbl,
206 p_resource_id => p_resource_id,
207 p_query_id => p_query_id,
208 x_return_status => l_return_status,
209 x_msg_count => l_msg_count,
210 x_msg_data => l_msg_data,
211 x_xml => l_content_xml );
212
213 WriteLog(l_msg || ' End GetXmlData...' );
214 end if;
215
216 WriteLog(l_msg || ' GetXmlData status=' || l_return_status );
217 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
218 WriteLog(l_msg || ' error to get XML data');
219 --
220 x_msg_data := l_msg_data;
221 x_msg_count := l_msg_count;
222 --
223 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_XMLDATA');
224 FND_MSG_PUB.Add;
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 RAISE FND_API.G_EXC_ERROR;
227 END IF;
228
229 --WriteLog(l_msg || ' xml= ' || l_content_xml );
230 l_request_id := null;
231 l_doc := empty_blob();
232
233 WriteLog(l_msg || ' insert_row' );
234
235 -- Insert Table with XML
236 IEX_XML_PKG.insert_row (
237 px_rowid => l_rowid
238 ,px_xml_request_id => l_request_id
239 ,p_query_temp_id => p_query_id
240 ,p_status => 'XMLDATA'
241 ,p_document => l_doc
242 ,p_xmldata => l_content_xml
243 ,p_method => p_method
244 ,p_destination => p_dest
245 ,p_object_type => p_object_type
246 ,p_object_id => p_object_id
247 ,p_resource_id => p_resource_id
248 ,p_view_by => p_level
249 ,p_party_id => l_party_id
250 ,p_cust_account_id => l_account_id
251 ,p_cust_site_use_id => l_site_id
252 ,p_delinquency_id => l_delinquency_id
253 ,p_last_update_date => sysdate
254 ,p_last_updated_by => l_user_id
255 ,p_creation_date => sysdate
256 ,p_created_by => l_user_id
257 ,p_last_update_login => l_user_id
258 ,p_object_version_number => l_user_id
259 );
260
261 COMMIT;
262
263 WriteLog(l_msg || 'l_request_id = ' || l_request_id);
264 x_request_id := l_request_id;
265
266
267
268 -- ******************************************************************
269 -- Delivery
270 -- ******************************************************************
271
272 WriteLog(l_msg || ' Get Template Code' );
273
274 OPEN C_GET_TEMPCODE (p_template_id);
275 FETCH C_GET_TEMPCODE INTO l_tempcode,
276 l_lang,
277 l_terr;
278
279 IF (C_GET_TEMPCODE%NOTFOUND) THEN
280 --FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
281 --FND_MESSAGE.Set_Token ('INFO', 'Cannot find xdo template code');
282 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_XDOTEMP');
283 FND_MSG_PUB.Add;
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 RAISE FND_API.G_EXC_ERROR;
286 END IF;
287 CLOSE C_GET_TEMPCODE;
288 WriteLog(l_msg || ' Template Code = ' || l_tempcode);
289 WriteLog(l_msg || ' Default Lang = ' || l_lang);
290 WriteLog(l_msg || ' Default Terr = ' || l_terr);
291
292 WriteLog(l_msg || ' Delivery...' );
293
294 /*
295 if l_lang is not null then
296 select iso_language
297 into l_iso_lang1
298 from fnd_languages
299 where language_code= upper(l_lang);
300
301 WriteLog(l_msg || 'l_iso_lang1 = '||l_iso_lang1 );
302
303 select count(distinct territory)
304 into l_no_terr
305 from xdo_lobs
306 where application_short_name='IEX' and upper(language)=l_iso_lang1;
307
308 WriteLog(l_msg || 'l_no_terr = '||l_no_terr );
309
310 l_lang := l_iso_lang1;
311
312 --if no of territory is >1 then send territory as '00' else the default territory for that template.
313 if l_no_terr = 1 then
314 select distinct territory
315 into l_templ_terr1
316 from xdo_lobs
317 where application_short_name='IEX' and upper(language)=l_iso_lang1;
318 else
319 l_templ_terr1 := '00';
320 end if;
321 WriteLog(l_msg || 'l_templ_terr1 = '||l_templ_terr1 );
322 end if;
323 l_terr := l_templ_terr1;
324 WriteLog(l_msg || ' Default Lang = ' || l_lang);
325 WriteLog(l_msg || ' Default Terr = ' || l_terr);
326 */
327
328 --l_templ_lang := l_lang;
329 --l_templ_terr := l_terr;
330 -- run java concorrent program to send xml data
331 if nvl(fnd_profile.value('IEX_USE_CUST_LANG_DUNN_LETTER'),'N') = 'N' then
332 WriteLog(l_msg || ' Send dunning with currently logged in lang...' );
333 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
334 APPLICATION => 'IEX',
335 PROGRAM => 'IEXXMLGEN',
336 DESCRIPTION => 'Oracle Collections Delivery XML Process',
337 START_TIME => sysdate,
338 SUB_REQUEST => false,
339 ARGUMENT1 => l_request_id,
340 ARGUMENT2 => p_method,
341 ARGUMENT3 => p_dest,
342 ARGUMENT4 => p_subject,
343 ARGUMENT5 => l_tempcode,
344 ARGUMENT6 => null, -- Send dunning letter by using the current logged in language
345 ARGUMENT7 => null);
346 else
347 WriteLog(l_msg || ' Send dunning with the language from the site' );
348 l_bind_cnt := p_bind_tbl.count;
349
350 for j in 1..l_bind_cnt
351 loop
352 l_bind_name := upper(p_bind_tbl(j).Key_name);
353
354 if l_bind_name ='LOCATION_ID' then
355 l_bind_val := p_bind_tbl(j).Key_Value;
356 WriteLog(l_msg || 'bind_name = '||l_bind_name || ' value=' || l_bind_val );
357 EXIT;
358 end if;
359 end loop;
360
361 if l_bind_val is not null then
362 select language
363 into l_loc_lang
364 from hz_locations
365 where location_id= to_number(l_bind_val);
366
367 WriteLog(l_msg || 'l_loc_lang = '||l_loc_lang );
368
369 if l_loc_lang is not null then
370 select iso_language
371 into l_iso_lang
372 from fnd_languages
373 where language_code= upper(l_loc_lang);
374
375 WriteLog(l_msg || 'l_iso_lang = '||l_iso_lang );
376
377 select count(distinct territory)
378 into l_no_terr
379 from xdo_lobs
380 where application_short_name='IEX' and upper(language)=l_iso_lang;
381
382 WriteLog(l_msg || 'l_no_terr = '||l_no_terr );
383
384 l_templ_lang := l_iso_lang;
385
386 --if no of territory is >1 then send territory as '00' else the default territory for that template.
387 if l_no_terr = 1 then
388 select distinct territory
389 into l_templ_terr
390 from xdo_lobs
391 where application_short_name='IEX' and upper(language)=l_iso_lang;
392 else
393 l_templ_terr := '00';
394 end if;
395 WriteLog(l_msg || 'l_templ_terr = '||l_templ_terr );
396 end if;
397 end if;
398
399 l_submit_request_id := FND_REQUEST.SUBMIT_REQUEST(
400 APPLICATION => 'IEX',
401 PROGRAM => 'IEXXMLGEN',
402 DESCRIPTION => 'Oracle Collections Delivery XML Process',
403 START_TIME => sysdate,
404 SUB_REQUEST => false,
405 ARGUMENT1 => l_request_id,
406 ARGUMENT2 => p_method,
407 ARGUMENT3 => p_dest,
408 ARGUMENT4 => p_subject,
409 ARGUMENT5 => l_tempcode,
410 ARGUMENT6 => lower(l_templ_lang),
411 ARGUMENT7 => upper(l_templ_terr));
412 end if; --profile value
413 COMMIT;
414
415 WriteLog(l_msg || ' delivery xml : concurrent request id= ' || l_submit_request_id );
416
417 /**** dont wait the concurrent program
418
419 --the main process should wait till the spawned process is over.
420 IF (l_submit_request_id IS NOT NULL AND l_submit_request_id <> 0) THEN
421 LOOP
422 WriteLog(l_msg ||
423 'Start Time of the xml Process IEXXMLGEN : ' ||
424 to_char (sysdate, 'dd/mon/yyyy :HH:MI:SS'));
425 l_bool := FND_CONCURRENT.wait_for_request(
426 request_id =>l_submit_request_id,
427 interval =>30,
428 max_wait =>144000,
429 phase =>uphase,
430 status =>ustatus,
431 dev_phase =>dphase,
432 dev_status =>dstatus,
433 message =>message);
434
435 IF dphase = 'COMPLETE'
436 --and dstatus = 'NORMAL'
437 --the possible values are NORMAL/ERROR/WARNING/CANCELLED/TERMINATED
438 THEN
439 WriteLog(l_msg || 'End Time of the xml Process IEXXMLGEN : '||
440 to_char (sysdate, 'dd/mon/yyyy :HH:MI:SS'));
441 EXIT;
442 END If; --dphase
443 END LOOP;
444 --
445 ELSE
446
447 WriteLog(l_msg || ' Error to delivery xml...' );
448 --FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
449 --FND_MESSAGE.Set_Token ('INFO', 'error to delivery xml');
450 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_DELIVERY');
451 FND_MSG_PUB.Add;
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 RAISE FND_API.G_EXC_ERROR;
454
455 END IF;
456 WriteLog(l_msg || ' delivery xml : concurrent request id= ' || l_submit_request_id );
457
458
459 -- Update Table with Status
460 IEX_XML_PKG.update_row (
461 p_xml_request_id => l_request_id
462 ,p_status => l_return_status
463 --,p_document => l_doc
464 );
465 ******/
466
467 --x_request_id := l_request_id;
468
469 --
470 -- END of API body
471 --
472
473 -- Standard check for p_commit
474 /* IF FND_API.to_Boolean( p_commit )
475 THEN
476 COMMIT WORK;
477 END IF;
478 */
479 COMMIT WORK;
480
481 -- Standard call to get message count and IF count is 1, get message info.
482 FND_MSG_PUB.Count_And_Get
483 ( p_count => x_msg_count,
484 p_data => x_msg_data );
485
486 EXCEPTION
487 WHEN FND_API.G_EXC_ERROR THEN
488 COMMIT WORK;
489 -- ROLLBACK TO SEND_COPY_PUB;
490 WriteLog(l_msg || ' Exc_Error:'|| SQLERRM);
491 x_return_status := FND_API.G_RET_STS_ERROR;
492 -- Update Table with Status
493 l_status := 'FAILURE';
494 IEX_XML_PKG.update_row (
495 p_xml_request_id => l_request_id
496 ,p_status => l_status
497 );
498 FND_MSG_PUB.Count_And_Get
499 ( p_count => x_msg_count,
500 p_data => x_msg_data
501 );
502
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504 COMMIT WORK;
505 -- ROLLBACK TO SEND_COPY_PUB;
506 WriteLog(l_msg || ' UnExc_Error:'|| SQLERRM);
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508 -- Update Table with Status
509 l_status := 'FAILURE';
510 IEX_XML_PKG.update_row (
511 p_xml_request_id => l_request_id
512 ,p_status => l_status
513 );
514 FND_MSG_PUB.Count_And_Get
515 ( p_count => x_msg_count,
516 p_data => x_msg_data
517 );
518
519 WHEN OTHERS THEN
520 COMMIT WORK;
521 -- ROLLBACK TO SEND_COPY_PUB;
522 WriteLog(l_msg || ' Other:'|| SQLERRM);
523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
524 -- Update Table with Status
525 l_status := 'FAILURE';
526 IEX_XML_PKG.update_row (
527 p_xml_request_id => l_request_id
528 ,p_status => l_status
529 );
530 FND_MSG_PUB.Count_And_Get
531 ( p_count => x_msg_count,
532 p_data => x_msg_data
533 );
534
535 END Send_COPY;
536
537
538 /*
539 Overview: This function is to retrieve the existing xml data from
540 iex_xml_request_histories table by the xml_request_id.
541 */
542 procedure RetrieveXmlData
543 (
544 p_request_id IN number
545 , x_return_status OUT NOCOPY varchar2
546 , x_msg_count OUT NOCOPY NUMBER
547 , x_msg_data OUT NOCOPY VARCHAR2
548 , x_xml OUT NOCOPY clob
549 )
550 IS
551 CURSOR C_GET_XML (IN_REQUEST_ID NUMBER) IS
552 SELECT xmldata
553 FROM IEX_XML_REQUEST_HISTORIES
554 WHERE xml_request_id = in_request_id;
555
556 l_xml CLOB;
557
558 BEGIN
559
560 WriteLog('begin RetrieveXmlData()');
561 WriteLog('RetrieveXmlData: p_request_id = '||p_request_id);
562
563 if (p_request_id is null) then
564 FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
565 FND_MESSAGE.Set_Token ('INFO', 'No Request_Id');
566 FND_MSG_PUB.Add;
567 x_return_status := FND_API.G_RET_STS_ERROR;
568 RAISE FND_API.G_EXC_ERROR;
569 end if;
570
571 OPEN C_GET_XML (p_request_id);
572 FETCH C_GET_XML INTO l_xml;
573
574 IF (C_GET_XML%NOTFOUND) THEN
575 FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
576 FND_MESSAGE.Set_Token ('INFO', 'Cannot find xmldata');
577 FND_MSG_PUB.Add;
578 x_return_status := FND_API.G_RET_STS_ERROR;
579 RAISE FND_API.G_EXC_ERROR;
580 END IF;
581 CLOSE C_GET_XML;
582
583 x_xml := l_xml;
584 WriteLog('get XmlData');
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586 FND_MSG_PUB.Count_And_Get
587 ( p_count => x_msg_count,
588 p_data => x_msg_data );
589
590
591 EXCEPTION
592
593 WHEN FND_API.G_EXC_ERROR THEN
594 WriteLog('RetrieveXmlData: Exc_Error:'|| SQLERRM);
595 x_return_status := FND_API.G_RET_STS_ERROR;
596 FND_MSG_PUB.Count_And_Get
597 ( p_count => x_msg_count,
598 p_data => x_msg_data);
599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
600 WriteLog('RetrieveXmlData: Exc_Error:'|| SQLERRM);
601 x_return_status := FND_API.G_RET_STS_ERROR;
602 FND_MSG_PUB.Count_And_Get
603 ( p_count => x_msg_count,
604 p_data => x_msg_data);
605 when others then
606 WriteLog('RetrieveXmlData: Exc_Error:'|| SQLERRM);
607 x_return_status := FND_API.G_RET_STS_ERROR;
608 FND_MSG_PUB.Count_And_Get
609 ( p_count => x_msg_count,
610 p_data => x_msg_data);
611
612 END RetrieveXmlData;
613
614
615 /*
616 Overview: This function is to get the xml data from a query which is defined by the dunning letter template.
617 Parameter: p_party_id : party_id
618 Return: clob contains the result of the query
619 creation date: 08/25/2004
620 author: ctlee
621 Note: test only
622 */
623 procedure GetXmlData
624 (
625 p_party_id IN number
626 , p_resource_id IN number
627 , p_bind_tbl IN IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL
628 , p_query_id IN number
629 , x_return_status OUT NOCOPY varchar2
630 , x_msg_count OUT NOCOPY NUMBER
631 , x_msg_data OUT NOCOPY VARCHAR2
632 , x_xml OUT NOCOPY clob
633 )
634 IS
635 CURSOR C_GET_QUERY (IN_QUERY_ID NUMBER) IS
636 --Bug5370344. Use the New column.
637 -- SELECT upper(statement)
638 SELECT UPPER(ADDITIONAL_QUERY), UPPER(STATEMENT)
639 FROM IEX_XML_QUERIES
640 WHERE query_id = in_query_id
641 and trunc(sysdate) between trunc(nvl(start_date, sysdate)) and
642 trunc(nvl(end_date, sysdate))
643 and enabled_flag = 'Y';
644
645 --Bug5370344 l_query Varchar2(4000);
646 l_query clob;
647 l_query_stmt varchar2(4000);
648 l_new_query clob;
649 qry_string Varchar2(4000);
650 qryCtx DBMS_XMLQuery.ctxType;
651 result CLOB;
652 xmlstr varchar2(32767);
653 line varchar2(4000);
654
655 l_bind_name varchar2(150);
656 l_bind_type varchar2(25);
657 l_bind_val varchar2(240);
658 l_cnt number;
659 l_found number;
660 l_bind_cnt number;
661 l_bind_found number;
662
663 len number;
664 l_start number;
665 l_end number;
666 sMsg varchar2(250);
667
668 v_cursor NUMBER;
669 v_numrows NUMBER;
670
671 --Begin Bug#6743267 24-Jul-2008 barathsr
672 l_dataHdr_clob clob;
673 l_dataHdrTag Varchar2(100);
674 l_dataHeader varchar2(1000) := '';
675 l_dataHdrQry varchar2(1000) := '';
676 l_rowSetName varchar2(1000) := '';
677 TYPE refCur IS REF CURSOR;
678 xml_element refCur;
679 --End Bug#6743267 24-Jul-2008 barathsr
680
681 /* begin bug 4732366 - ctlee - use set bind variables 11/28/2005 */
682 -- l_bind_rec IEX_DUNNING_PVT.FULFILLMENT_BIND_REC;
683 -- l_bind_tbl IEX_DUNNING_PVT.FULFILLMENT_BIND_TBL;
684 l_temp_index integer;
685
686 l_temp_s_first varchar2(4000);
687 l_temp_s_second varchar2(4000);
688
689 TYPE VAR_COUNT_REC IS RECORD(
690 VAR_COUNT integer);
691
692 TYPE VAR_COUNT_tbl IS TABLE OF VAR_COUNT_REC INDEX BY binary_integer;
693 l_var_count_rec var_count_rec;
694 l_var_count_tbl var_count_tbl;
695 l_bind_count integer;
696 /* end bug 4732366 - ctlee - use set bind variables 11/28/2005 */
697
698 BEGIN
699
700 WriteLog('begin test GetXmlData()');
701 WriteLog('GetXmlData: p_query_id = ' || p_query_id);
702
703 OPEN C_GET_QUERY (p_query_id);
704 FETCH C_GET_QUERY INTO l_query, l_query_stmt;
705
706 IF (C_GET_QUERY%NOTFOUND) THEN
707 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_QUERY');
708 FND_MSG_PUB.Add;
709 x_return_status := FND_API.G_RET_STS_ERROR;
710 RAISE FND_API.G_EXC_ERROR;
711 END IF;
712 CLOSE C_GET_QUERY;
713
714 -- begin to check the query and bind var are matched or not;
715 WriteLog('GetXmlData: chk the bind var and query');
716
717 -- replace bind var by l_bind_tbl
718 l_bind_cnt := p_bind_tbl.count;
719 l_cnt := 0;
720 WriteLog('GetXmlData: l_bind_cnt = '||l_bind_cnt);
721 if (l_query is not null) then
722 l_new_query := l_query;
723 else
724 l_new_query := l_query_stmt;
725 end if;
726
727 -- l_new_query := l_query;
728 for j in 1..l_bind_cnt
729 loop
730 l_cnt := 0;
731 l_bind_name := ':' || upper(p_bind_tbl(j).Key_name);
732 l_bind_type := p_bind_tbl(j).Key_Type;
733 l_bind_val := p_bind_tbl(j).Key_Value;
734
735 if l_bind_val is null then
736 l_bind_val := 'null';
737 end if;
738
739 -- chk how many bind var in the query
740 l_bind_found := instr( l_new_query, l_bind_name);
741
742 --Begin Bug#6743267 24-Jul-2008 barathsr
743 IF (l_bind_found > 0) THEN
744 l_dataHeader := l_dataHeader||'XMLElement("'||upper(p_bind_tbl(j).Key_name)||'",'''||l_bind_val||''')||';
745 END IF;
746 --End Bug#6743267 24-Jul-2008 barathsr
747 WHILE (l_bind_found > 0)
748 LOOP
749 EXIT when l_bind_found = 0;
750 --
751 l_cnt := l_cnt + 1;
752 l_new_query := replace (l_new_query, l_bind_name, l_bind_val);
753 l_bind_found := instr( l_new_query, l_bind_name);
754 END LOOP;
755
756 WriteLog('GetXmlData: replace bind_name = '||l_bind_name || ' with val=' || l_bind_val ||'; cnt=' || l_cnt);
757 l_var_count_rec.var_count := l_cnt;
758 l_var_count_tbl(j) := l_var_count_rec;
759
760 end loop;
761
762 --Begin Bug#6743267 24-Jul-2008 barathsr
763 l_dataHeader := substr(l_dataHeader,0,length(l_dataHeader)-2);
764 --End Bug#6743267 24-Jul-2008 barathsr
765
766 /* begin bug 4732366 - ctlee - use set bind variables 11/28/2005 */
767 -- resource_id is added to the l_bind_tbl
768 -- and replace :resource_id
769 l_cnt := 0;
770 l_bind_found := instr( l_new_query, ':RESOURCE_ID');
771 WHILE (l_bind_found > 0)
772 LOOP
773 EXIT when l_bind_found = 0;
774 --
775 l_cnt := l_cnt + 1;
776 l_new_query := replace (l_new_query, ':RESOURCE_ID', p_resource_id);
777 l_bind_found := instr( l_new_query, ':RESOURCE_ID');
778 END LOOP;
779 WriteLog('GetXmlData: resource_id : l_cnt = '||l_cnt || ' with ' || p_resource_id);
780 WriteLog('GetXmlData: after replace');
781
782 /* end bug 4732366 - ctlee - use set bind variables 11/28/2005 */
783
784 /* begin bug 4732366 - ctlee - use set bind variables 12/28/2005 */
785 -- using bind variable, no check here
786 -- if the replaced query still has ':', cannot execute query;
787 l_found := instr(l_new_query, ':');
788 if (l_found > 0) then
789 WriteLog('GetXmlData: l_found=' || l_found);
790 WriteLog('GetXmlData: var='||substr(l_new_query, l_found, 3));
791 -- cannot execute the query; the bind variables are not enough;
792 WriteLog('GetXmlData: bind var and query are not matched');
793 --FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
794 --FND_MESSAGE.Set_Token ('INFO', 'Bind Variables are not enough for query_id: ' || p_query_id);
795 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_BINDVAR');
796 FND_MSG_PUB.Add;
797 x_return_status := FND_API.G_RET_STS_ERROR;
798 RAISE FND_API.G_EXC_ERROR;
799 end if;
800 WriteLog('GetXmlData: bind var and query are matched');
801
802 WriteLog('GetXmlData: end to chk the bind var and query');
803 -- end to check the query and bind var are matched or not;
804
805
806 /* end bug 4732366 - ctlee - use set bind variables 11/28/2005 */
807
808 WriteLog('GetXmlData: l_new_query=' || l_new_query);
809 len := length(l_new_query)/100;
810 WriteLog('GetXmlData: l_new_query len=' || len);
811 /**
812 for i in 1..len loop
813 l_start := 100 * (i-1) + 1;
814 l_end := 100 * i;
815 sMsg := substr(l_new_query, l_start, l_end);
816 dbms_output.put_line(sMsg);
817 end loop;
818 **/
819
820 /* begin bug 4732366 - ctlee - use set bind variables 12/28/2005 */
821 -- no check of the query
822 -- execute the query to see if any records returned.
823 BEGIN
824 WriteLog('GetXmlData: execute query');
825 v_cursor := DBMS_SQL.OPEN_CURSOR;
826 DBMS_SQL.parse( v_cursor, l_new_query, 1);
827 v_numrows := DBMS_SQL.EXECUTE( v_cursor);
828 v_numrows := DBMS_SQL.FETCH_ROWS( v_cursor);
829 DBMS_SQL.CLOSE_CURSOR( v_cursor);
830 EXCEPTION
831 when others then
832 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_CANT_EXEC_QRY');
833 FND_MESSAGE.Set_Token ('ID', p_query_id);
834 FND_MSG_PUB.Add;
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 RAISE FND_API.G_EXC_ERROR;
837 END;
838 if (v_numrows > 0 ) then
839 WriteLog('GetXmlData: execute query, numrows > 0');
840 else
841 WriteLog('GetXmlData: execute query, no rows');
842 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_DATA');
843 FND_MSG_PUB.Add;
844 x_return_status := FND_API.G_RET_STS_ERROR;
845 RAISE FND_API.G_EXC_ERROR;
846 end if;
847
848 /* end bug 4732366 - ctlee - use set bind variables 11/28/2005 */
849
850 WriteLog('GetXmlData: calling DBMS_XMLQuery api');
851 --qryCtx := DBMS_XMLQuery.newContext(l_query);
852 qryCtx := DBMS_XMLQuery.newContext(l_new_query);
853
854 DBMS_XMLQuery.setRowTag(qryCtx, 'COLLECTION');
855 -- set the rowset header to null
856 DBMS_XMLQuery.setRowSetTag(qryCtx, 'COLLECTIONSET');
857
858 --Begin Bug#6743267 24-Jul-2008 barathsr
859 l_rowSetName := '<COLLECTIONSET>';
860 --End Bug#6743267 24-Jul-2008 barathsr
861
862 /***
863 --Set bind values
864 --DBMS_XMLQuery.setBindValue(qryCtx, 'PARTY_ID', p_party_id);
865 WriteLog('GetXmlData: set bind values');
866 l_bind_cnt := l_bind_tbl.count;
867 l_cnt := 0;
868 WriteLog('GetXmlData: l_bind_cnt = '||l_bind_cnt);
869 for j in 1..l_bind_cnt
870 loop
871 l_cnt := 0;
872 l_bind_name := ':' || upper(p_bind_tbl(j).Key_name);
873 l_bind_type := p_bind_tbl(j).Key_Type;
874 l_bind_val := p_bind_tbl(j).Key_Value;
875 -- chk how many bind var in the query
876 l_bind_found := instr( l_query, l_bind_name);
877 WHILE (l_bind_found > 0)
878 LOOP
879 EXIT when l_bind_found = 0;
880 --
881 l_cnt := l_cnt + 1;
882 DBMS_XMLQuery.setBindValue(qryCtx, l_bind_name, l_bind_val);
883 l_bind_found := instr( l_query, l_bind_name);
884 END LOOP;
885 WriteLog('GetXmlData: set bind_name = '||l_bind_name || ' with val=' || l_bind_val ||'; cnt=' || l_cnt);
886 end loop;
887
888 l_cnt := 0;
889 l_bind_found := instr( l_query, ':RESOURCE_ID');
890 WHILE (l_bind_found > 0)
891 LOOP
892 EXIT when l_bind_found = 0;
893 --
894 l_cnt := l_cnt + 1;
895 DBMS_XMLQuery.setBindValue(qryCtx, 'RESOURCE_ID', p_resource_id);
896 l_bind_found := instr( l_query, ':RESOURCE_ID');
897 END LOOP;
898 WriteLog('GetXmlData: resource_id : l_cnt = '||l_cnt);
899 ***/
900
901 /* begin bug 4732366 - ctlee - use set bind variables 12/28/2005 */
902 /* WriteLog('GetXmlData: set bind values');
903 l_bind_cnt := p_bind_tbl.count;
904 --l_cnt := 0;
905 WriteLog('GetXmlData: l_bind_cnt = '||l_bind_cnt);
906 for j in 1..l_bind_cnt
907 loop
908 --l_cnt := 0;
909 l_bind_name := upper(p_bind_tbl(j).Key_name);
910 l_bind_type := p_bind_tbl(j).Key_Type;
911 l_bind_val := p_bind_tbl(j).Key_Value;
912 -- l_bind_count := l_var_count_tbl(j).var_count;
913 -- chk how many bind var in the query
914 -- l_bind_found := instr( l_query, l_bind_name);
915 -- WHILE (l_bind_found > 0)
916 WHILE (l_bind_count > 0)
917 LOOP
918 EXIT when l_bind_count = 0;
919 --
920 --l_cnt := l_cnt + 1;
921 DBMS_XMLQuery.setBindValue(qryCtx, l_bind_name||l_bind_count, l_bind_val);
922 WriteLog('GetXmlData: set bind_name = '||l_bind_name || l_bind_count || ' with val=' || l_bind_val );
923 -- l_bind_found := instr( l_query, l_bind_name);
924 l_bind_count := l_bind_count - 1;
925
926 END LOOP;
927
928 end loop; */
929
930 /* end bug 4732366 - ctlee - use set bind variables 11/28/2005 */
931
932
933 --Begin Bug#6743267 24-Jul-2008 barathsr
934 -- Create the XML DataHeader
935 l_dataHdrQry := 'SELECT '||l_dataHeader||' FROM DUAL';
936
937 OPEN xml_element FOR l_dataHdrQry;
938 FETCH xml_element INTO l_dataHdr_clob;
939 CLOSE xml_element;
940
941 WriteLog('The XML DataHeader is '||l_dataHdr_clob);
942 --End Bug#6743267 24-Jul-2008 barathsr
943
944 -- now get the result
945 WriteLog('GetXmlData: getXml');
946 result := DBMS_XMLQuery.getXml(qryCtx);
947 WriteLog('GetXmlData: get result');
948 --Begin Bug#6743267 24-Jul-2008 barathsr
949 -- RowSet and Row
950 result := replace(result,l_rowSetName,l_rowSetName||''||l_dataHdr_clob);
951 --End Bug#6743267 24-Jul-2008 barathsr
952 x_xml := result;
953
954 if (result is null) then
955 WriteLog('GetXmlData: no result');
956 --FND_MESSAGE.Set_Name('IEX', 'API_FAIL_SEND_FFM');
957 --FND_MESSAGE.Set_Token ('INFO', 'No XML result');
958 FND_MESSAGE.Set_Name('IEX', 'IEX_SEND_FAILED_NO_DATA');
959 FND_MSG_PUB.Add;
960 x_return_status := FND_API.G_RET_STS_ERROR;
961 RAISE FND_API.G_EXC_ERROR;
962 end if;
963
964 --close context
965 WriteLog('GetXmlData: close context ');
966 DBMS_XMLQuery.closeContext(qryCtx);
967
968
969 WriteLog('GetXmlData: end GetXmlData()');
970
971 x_return_status := FND_API.G_RET_STS_SUCCESS;
972 FND_MSG_PUB.Count_And_Get
973 ( p_count => x_msg_count,
974 p_data => x_msg_data );
975
976
977 EXCEPTION
978
979 WHEN FND_API.G_EXC_ERROR THEN
980 WriteLog('GetXmlData: Exc_Error:'|| SQLERRM);
981 x_return_status := FND_API.G_RET_STS_ERROR;
982 FND_MSG_PUB.Count_And_Get
983 ( p_count => x_msg_count,
984 p_data => x_msg_data);
985 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
986 WriteLog('GetXmlData: Exc_Error:'|| SQLERRM);
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 FND_MSG_PUB.Count_And_Get
989 ( p_count => x_msg_count,
990 p_data => x_msg_data);
991 when others then
992 WriteLog('GetXmlData: Exc_Error:'|| SQLERRM);
993 x_return_status := FND_API.G_RET_STS_ERROR;
994 FND_MSG_PUB.Count_And_Get
995 ( p_count => x_msg_count,
996 p_data => x_msg_data);
997 End GetXmlData;
998
999
1000
1001 /*
1002 Overview: This function is to retrieve the existing xml data from
1003 iex_xml_request_histories table by the xml_request_id.
1004 */
1005 function getCurrDeliveryMethod return varchar2
1006 IS
1007 CURSOR C_GET_SETUP IS
1008 SELECT NVL(PREFERENCE_VALUE, 'FFM')
1009 FROM IEX_APP_PREFERENCES_VL
1010 WHERE PREFERENCE_NAME= 'COLLECTIONS DELIVERY METHOD';
1011
1012 l_dmethod VARCHAR2(10);
1013
1014 BEGIN
1015
1016 WriteLog('begin getCurrDeliveryMethod()');
1017
1018 OPEN C_GET_SETUP;
1019 FETCH C_GET_SETUP INTO l_dmethod;
1020
1021 IF (C_GET_SETUP%NOTFOUND) THEN
1022 l_dmethod := '';
1023 END IF;
1024 CLOSE C_GET_SETUP;
1025
1026 WriteLog('get Delivery Method');
1027 return l_dmethod;
1028
1029 END getCurrDeliveryMethod;
1030
1031
1032
1033 Procedure WriteLog ( p_msg IN VARCHAR2)
1034 IS
1035 BEGIN
1036 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1037 iex_debug_pub.LogMessage (p_msg);
1038 END IF;
1039
1040 --dbms_output.put_line(p_msg);
1041
1042 END WriteLog;
1043
1044
1045
1046
1047 BEGIN
1048 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1049
1050 END IEX_SEND_XML_PVT;