[Home] [Help]
PACKAGE BODY: APPS.IEX_XML_PKG
Source
1 PACKAGE BODY IEX_XML_pkg AS
2 /* $Header: iextxmlb.pls 120.9 2010/08/24 09:35:45 gnramasa ship $ */
3
4 PG_DEBUG NUMBER(2) ;
5
6 PROCEDURE insert_row(
7 px_rowid IN OUT NOCOPY VARCHAR2
8 , px_xml_request_id IN OUT NOCOPY NUMBER
9 , p_query_temp_id NUMBER
10 , p_status VARCHAR2
11 , p_document BLOB
12 , p_html_document BLOB
13 , p_xmldata CLOB
14 , p_method VARCHAR2
15 , p_destination VARCHAR2
16 , p_subject VARCHAR2
17 , p_object_type VARCHAR2
18 , p_object_id NUMBER
19 , p_resource_id NUMBER
20 , p_view_by VARCHAR2
21 , p_party_id NUMBER
22 , p_cust_account_id NUMBER
23 , p_cust_site_use_id NUMBER
24 , p_delinquency_id NUMBER
25 , p_last_update_date DATE
26 , p_last_updated_by NUMBER
27 , p_creation_date DATE
28 , p_created_by NUMBER
29 , p_last_update_login NUMBER
30 , p_object_version_number NUMBER
31 , p_request_id NUMBER
32 , p_worker_id NUMBER
33 , p_confirmation_mode VARCHAR2 -- added by gnramasa for bug 8489610 14-May-09
34 , p_conc_request_id NUMBER -- added by gnramasa for bug 8489610 14-May-09
35 , p_org_id number -- added for bug 9151851
36 , p_template_language VARCHAR2 -- added by gnramasa for bug 8489610 28-May-09
37 , p_template_territory VARCHAR2 -- added by gnramasa for bug 8489610 28-May-09
38 , p_document_type VARCHAR2 default NULL
39 , p_addt_query_id number --added for bug 9970624 gnramasa 4th Aug 10
40 , p_addt_xmldata CLOB --added for bug 9970624 gnramasa 4th Aug 10
41 , p_addt_status VARCHAR2 --added for bug 9970624 gnramasa 4th Aug 10
42 , p_addt_document BLOB --added for bug 9970624 gnramasa 4th Aug 10
43 , p_addt_html_document BLOB --added for bug 9970624 gnramasa 4th Aug 10
44 ) IS
45
46 CURSOR get_rowid IS
47 SELECT ROWID
48 FROM iex_xml_request_histories
49 WHERE xml_request_id = px_xml_request_id;
50 --
51 CURSOR get_seq_csr is
52 SELECT IEX_XML_REQUEST_HISTORIES_s.nextval
53 FROM sys.dual;
54
55 BEGIN
56 --
57 If (px_xml_request_id IS NULL) OR (px_xml_request_id = FND_API.G_MISS_NUM) then
58 OPEN get_seq_csr;
59 FETCH get_seq_csr INTO px_xml_request_id;
60 CLOSE get_seq_csr;
61 End If;
62 --dbms_output.put_line('id=' || px_xml_request_id);
63 --dbms_output.put_line('insert');
64 --
65 --Start adding for bug 8489610 by gnramasa 14-May-09
66 WriteLog( ' in xml_pkg org_id ' || p_org_id);
67 INSERT INTO IEX_XML_REQUEST_HISTORIES (
68 XML_REQUEST_ID
69 , QUERY_TEMP_ID
70 , STATUS
71 , DOCUMENT
72 , HTML_DOCUMENT
73 , XMLDATA
74 , METHOD
75 , DESTINATION
76 , SUBJECT
77 , OBJECT_TYPE
78 , OBJECT_ID
79 , RESOURCE_ID
80 , VIEW_BY
81 , PARTY_ID
82 , CUST_ACCOUNT_ID
83 , CUST_SITE_USE_ID
84 , DELINQUENCY_ID
85 , last_update_date
86 , last_updated_by
87 , creation_date
88 , created_by
89 , last_update_login
90 , object_version_number
91 , request_id
92 , worker_id
93 , confirmation_mode
94 , conc_request_id
95 , language
96 , territory
97 , org_id -- added for bug 9151851
98 , document_type
99 , addt_query_temp_id
100 , addt_xmldata
101 , addt_status
102 , addt_document
103 , addt_html_document
104
105 ) VALUES (
106 px_xml_request_id
107 , p_query_temp_id
108 , p_status
109 , p_document
110 , p_html_document
111 , p_xmldata
112 , p_method
113 , p_destination
114 , p_subject
115 , p_object_type
116 , p_object_id
117 , p_resource_id
118 , p_view_by
119 , p_party_id
120 , p_cust_account_id
121 , p_cust_site_use_id
122 , p_delinquency_id
123 , p_last_update_date
124 , p_last_updated_by
125 , p_creation_date
126 , p_created_by
127 , p_last_update_login
128 , p_object_version_number
129 , p_request_id
130 , p_worker_id
131 , p_confirmation_mode
132 , p_conc_request_id
133 , p_template_language
134 , p_template_territory
135 , p_org_id -- added for bug 9151851
136 , p_document_type
137 , p_addt_query_id
138 , p_addt_xmldata
139 , p_addt_status
140 , p_addt_document
141 , p_addt_html_document
142 );
143
144 OPEN get_rowid;
145 FETCH get_rowid INTO px_rowid;
146 IF (get_rowid%NOTFOUND) THEN
147 CLOSE get_rowid;
148 RAISE NO_DATA_FOUND;
149 END IF;
150
151 EXCEPTION
152 WHEN OTHERS THEN
153 --dbms_output.put_line('error' || SQLERRM);
154 WriteLog('iextxmlb.pls:insert_row:Exception errmsg='||SQLERRM);
155 END insert_row;
156
157
158
159 PROCEDURE delete_row(
160 p_xml_request_id NUMBER
161 ) IS
162 BEGIN
163 DELETE FROM iex_xml_request_histories
164 WHERE xml_request_id = p_xml_request_id;
165 IF (SQL%NOTFOUND) THEN
166 RAISE NO_DATA_FOUND;
167 END IF;
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 --dbms_output.put_line('error' || SQLERRM);
172 WriteLog('iextxmlb.pls:delete_row:Exception errmsg='||SQLERRM);
173 END delete_row;
174
175
176 PROCEDURE UPDATE_ROW (
177 p_xml_request_id NUMBER
178 , p_query_temp_id NUMBER
179 , p_status VARCHAR2
180 , p_document BLOB
181 , p_html_document BLOB
182 , p_xmldata CLOB
183 , p_method VARCHAR2
184 , p_destination VARCHAR2
185 , p_subject VARCHAR2
186 , p_object_type VARCHAR2
187 , p_object_id NUMBER
188 , p_resource_id NUMBER
189 , p_view_by VARCHAR2
190 , p_party_id NUMBER
191 , p_cust_account_id NUMBER
192 , p_cust_site_use_id NUMBER
193 , p_delinquency_id NUMBER
194 , p_last_update_date DATE
195 , p_last_updated_by NUMBER
196 , p_creation_date DATE
197 , p_created_by NUMBER
198 , p_last_update_login NUMBER
199 , p_object_version_number NUMBER
200 , p_request_id NUMBER
201 , p_worker_id NUMBER
202 , p_confirmation_mode VARCHAR2 -- added by gnramasa for bug 8489610 14-May-09
203 , p_conc_request_id NUMBER -- added by gnramasa for bug 8489610 14-May-09
204 , p_template_language VARCHAR2 -- added by gnramasa for bug 8489610 28-May-09
205 , p_template_territory VARCHAR2 -- added by gnramasa for bug 8489610 28-May-09
206 , p_document_type VARCHAR2 default NULL
207 , p_addt_query_id number --added for bug 9970624 gnramasa 4th Aug 10
208 , p_addt_xmldata CLOB --added for bug 9970624 gnramasa 4th Aug 10
209 , p_addt_status VARCHAR2 --added for bug 9970624 gnramasa 4th Aug 10
210 , p_addt_document BLOB --added for bug 9970624 gnramasa 4th Aug 10
211 , p_addt_html_document BLOB --added for bug 9970624 gnramasa 4th Aug 10
212 )
213 IS
214 cursor c_get_rec (in_request_id number) is
215 select rowid,
216 xml_request_id,
217 query_temp_id,
218 status,
219 document,
220 html_document,
221 xmldata,
222 object_type,
223 object_id,
224 resource_id,
225 method,
226 destination,
227 subject,
228 view_by,
229 party_id,
230 cust_account_id,
231 cust_site_use_id,
232 delinquency_id,
233 creation_date,
234 created_by,
235 last_update_date,
236 last_updated_by,
237 last_update_login,
238 object_version_number,
239 request_id,
240 worker_id,
241 confirmation_mode,
242 conc_request_id,
243 language,
244 territory,
245 document_type,
246 addt_query_temp_id,
247 addt_xmldata,
248 addt_status,
249 addt_document,
250 addt_html_document
251 from iex_xml_request_histories
252 where xml_request_id = in_request_id
253 for update nowait;
254 --
255 l_xml_request_id NUMBER;
256 l_query_temp_id NUMBER;
257 l_status VARCHAR2(40);
258 l_document BLOB;
259 l_html_document BLOB;
260 l_xmldata CLOB;
261 l_method VARCHAR2(10);
262 l_destination VARCHAR2(2000);
263 l_subject VARCHAR2(4000);
264 l_object_type VARCHAR2(100);
265 l_object_id NUMBER;
266 l_resource_id NUMBER;
267 --Start for bug 8791904 gnramasa 29th Aug 09
268 --l_view_by VARCHAR2(10);
269 l_view_by VARCHAR2(20);
270 --End for bug 8791904 gnramasa 29th Aug 09
271 l_party_id NUMBER;
272 l_cust_account_id NUMBER;
273 l_cust_site_use_id NUMBER;
274 l_delinquency_id NUMBER;
275 l_last_update_date DATE;
276 l_last_updated_by NUMBER;
277 l_creation_date DATE;
278 l_created_by NUMBER;
279 l_last_update_login NUMBER;
280 l_object_version_number NUMBER;
281 l_request_id NUMBER;
282 l_worker_id NUMBER;
283 l_rowid varchar2(2000);
284 l_confirmation_mode varchar2(10);
285 l_conc_request_id number;
286 l_language varchar2(10);
287 l_territory varchar2(10);
288 l_document_type varchar2(240);
289 l_addt_query_temp_id number;
290 l_addt_xmldata clob;
291 l_addt_status VARCHAR2(40);
292 l_addt_document BLOB;
293 l_addt_html_document BLOB;
294
295 BEGIN
296
297 open c_get_rec (p_xml_request_id);
298 fetch c_get_rec into
299 l_rowid,
300 l_xml_request_id,
301 l_query_temp_id,
302 l_status,
303 l_document,
304 l_html_document,
305 l_xmldata,
306 l_object_type,
307 l_object_id,
308 l_resource_id,
309 l_method,
310 l_destination,
311 l_subject,
312 l_view_by,
313 l_party_id,
314 l_cust_account_id,
315 l_cust_site_use_id,
316 l_delinquency_id,
317 l_creation_date,
318 l_created_by,
319 l_last_update_date,
320 l_last_updated_by,
321 l_last_update_login,
322 l_object_version_number,
323 l_request_id,
324 l_worker_id,
325 l_confirmation_mode,
326 l_conc_request_id,
327 l_language,
328 l_territory,
329 l_document_type,
330 l_addt_query_temp_id,
331 l_addt_xmldata,
332 l_addt_status,
333 l_addt_document,
334 l_addt_html_document;
335 CLOSE c_get_rec;
336
337 if (p_status is not null ) then
338 l_status := p_status;
339 end if;
340 if (p_addt_status is not null ) then
341 l_addt_status := p_addt_status;
342 end if;
343 if (p_document is not null ) then
344 l_document := p_document;
345 end if;
346 if (p_html_document is not null ) then
347 l_html_document := p_html_document;
348 end if;
349 if (p_xmldata is not null ) then
350 l_xmldata := p_xmldata;
351 end if;
352 if (p_method is not null ) then
353 l_method := p_method;
354 end if;
355 if (p_destination is not null ) then
356 l_destination := p_destination;
357 end if;
358 if (p_subject is not null) then
359 l_subject := p_subject;
360 end if;
361 if (p_resource_id is not null ) then
362 l_resource_id := p_resource_id;
363 end if;
364 if (p_last_update_date is not null ) then
365 l_last_update_date := p_last_update_date;
366 end if;
367 if (p_last_updated_by is not null ) then
368 l_last_updated_by := p_last_updated_by;
369 end if;
370 if (p_request_id is not null ) then
371 l_request_id := p_request_id;
372 end if;
373 if (p_worker_id is not null ) then
374 l_worker_id := p_worker_id;
375 end if;
376 if (p_confirmation_mode is not null ) then
377 l_confirmation_mode := p_confirmation_mode;
378 end if;
379 if (p_conc_request_id is not null ) then
380 l_conc_request_id := p_conc_request_id;
381 end if;
382 if (p_template_language is not null ) then
383 l_language := p_template_language;
384 end if;
385 if (p_template_territory is not null ) then
386 l_territory := p_template_territory;
387 end if;
388 if (p_document_type is not null ) then
389 l_document_type := p_document_type;
390 end if;
391 if (p_addt_xmldata is not null ) then
392 l_addt_xmldata := p_addt_xmldata;
393 end if;
394 if (p_addt_document is not null ) then
395 l_addt_document := p_addt_document;
396 end if;
397 if (p_addt_html_document is not null ) then
398 l_addt_html_document := p_addt_html_document;
399 end if;
400
401 UPDATE iex_xml_request_histories
402 SET
403 query_temp_id = l_query_temp_id
404 , status = l_status
405 , document = l_document
406 , html_document = l_html_document
407 , xmldata = l_xmldata
408 , method = l_method
409 , destination = l_destination
410 , subject = l_subject
411 , object_type = l_object_type
412 , object_id = l_object_id
413 , resource_id = l_resource_id
414 , view_by = l_view_by
415 , party_id = l_party_id
416 , cust_account_id = l_cust_account_id
417 , cust_site_use_id = l_cust_site_use_id
418 , delinquency_id = l_delinquency_id
419 , last_update_date = l_last_update_date
420 , last_updated_by = l_last_updated_by
421 , last_update_login = l_last_update_login
422 , creation_date = l_creation_date
423 , created_by = l_created_by
424 , object_version_number = l_object_version_number
425 , request_id = l_request_id
426 , worker_id = l_worker_id
427 , confirmation_mode = l_confirmation_mode
428 , conc_request_id = l_conc_request_id
429 , language = l_language
430 , territory = l_territory
431 , document_type = l_document_type
432 , addt_query_temp_id = l_addt_query_temp_id
433 , addt_xmldata = l_addt_xmldata
434 , addt_status = l_addt_status
435 , addt_document = l_addt_document
436 , addt_html_document = l_addt_html_document
437 WHERE xml_request_id = p_xml_request_id;
438
439 IF (SQL%NOTFOUND) THEN
440 RAISE NO_DATA_FOUND;
441 END IF;
442 --End adding for bug 8489610 by gnramasa 14-May-09
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 --dbms_output.put_line('error' || SQLERRM);
447 WriteLog('iextxmlb.pls:update_row:Exception errmsg='||SQLERRM);
448
449 END update_row;
450
451
452
453 Procedure WriteLog ( p_msg IN VARCHAR2)
454 IS
455 BEGIN
456 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
457 iex_debug_pub.LogMessage (p_msg);
458 END IF;
459
460 --dbms_output.put_line(p_msg);
461
462 END WriteLog;
463
464
465 BEGIN
466 PG_DEBUG := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
467
468
469 END iex_xml_pkg;