[Home] [Help]
PACKAGE BODY: APPS.DDR_FACT_WEBSERVICES_PUB
Source
1 PACKAGE BODY ddr_fact_webservices_pub AS
2 /* $Header: ddrpfwsb.pls 120.4 2008/03/18 05:50:19 vkohli noship $ */
3
4 -- Start of comments
5 -- API name : invoke_aggr_prc
6 -- Type : Public
7 -- Pre-reqs : None.
8 -- Function : to invoke the procedure to write the data to xml file
9 -- Parameters :
10 -- IN :p_api_version IN NUMBER Required
11 -- p_mfg_org_cd IN VARCHAR2 Required
12 -- Manufaturer organization code
13 -- p_org_dim_lvl_cd IN VARCHAR2
14 -- Identifies the organization hierarchy level code
15 -- p_org_cd IN VARCHAR2
16 -- Organization hierarchy level code value
17 -- p_loc_dim_lvl_cd IN VARCHAR2
18 -- Identifies the location hierarchy level code
19 -- p_loc_cd IN VARCHAR2
20 -- Location hierarchy level code
21 -- p_item_dim_lvl_cd IN VARCHAR2
22 -- p_item_cd IN VARCHAR2
23 -- p_time_dim_lvl_cd IN VARCHAR2
24 -- p_time_cd IN VARCHAR2
25 -- p_fact_code IN VARCHAR2
26 -- p_attribute1 IN VARCHAR2
27 -- p_attribute2 IN VARCHAR2
28 -- p_attribute3 IN VARCHAR2
29 -- p_attribute4 IN VARCHAR2
30 -- p_attribute5 IN VARCHAR2
31 -- OUT x_job_id OUT NOCOPY VARCHAR2
32 -- Version : Current version 1.0
33 -- Initial version 1.0
34 -- End of comments
35 PROCEDURE invoke_aggr_prc (
36 p_api_version IN NUMBER,
37 p_mfg_org_cd IN VARCHAR2,
38 p_org_cd IN VARCHAR2,
39 p_org_dim_lvl_cd IN VARCHAR2,
40 p_org_lvl_val IN VARCHAR2,
41 p_exp_org_level IN VARCHAR2,
42 p_loc_dim_lvl_cd IN VARCHAR2,
43 p_loc_lvl_val IN VARCHAR2,
44 p_exp_loc_level IN VARCHAR2,
45 p_item_dim_lvl_cd IN VARCHAR2,
46 p_item_lvl_val IN VARCHAR2,
47 p_exp_item_level IN VARCHAR2,
48 p_time_dim_lvl_cd IN VARCHAR2,
49 p_time_lvl_val IN VARCHAR2,
50 p_exp_time_level IN VARCHAR2,
51 p_fact_code IN VARCHAR2,
52 p_attribute1 IN VARCHAR2,
53 p_attribute2 IN VARCHAR2,
54 p_attribute3 IN VARCHAR2,
55 p_attribute4 IN VARCHAR2,
56 p_attribute5 IN VARCHAR2,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2,
60 x_job_id OUT NOCOPY VARCHAR2
61 );
62
63 PROCEDURE invoke_detail_prc (
64 p_api_version IN NUMBER,
65 p_mfg_org_cd IN VARCHAR2,
66 p_org_cd IN VARCHAR2,
67 p_org_dim_lvl_cd IN VARCHAR2,
68 p_org_lvl_val IN VARCHAR2,
69 p_loc_dim_lvl_cd IN VARCHAR2,
70 p_loc_lvl_val IN VARCHAR2,
71 p_item_dim_lvl_cd IN VARCHAR2,
72 p_item_lvl_val IN VARCHAR2,
73 p_time_dim_lvl_cd IN VARCHAR2,
74 p_time_lvl_val IN VARCHAR2,
75 p_fact_code IN VARCHAR2,
76 p_attribute1 IN VARCHAR2,
77 p_attribute2 IN VARCHAR2,
78 p_attribute3 IN VARCHAR2,
79 p_attribute4 IN VARCHAR2,
80 p_attribute5 IN VARCHAR2,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_count OUT NOCOPY NUMBER,
83 x_msg_data OUT NOCOPY VARCHAR2,
84 x_job_id OUT NOCOPY VARCHAR2
85 );
86
87 FUNCTION get_ddr_ws_job_id_seq_nextval
88 RETURN NUMBER;
89
90 PROCEDURE getAgrSalesReturnItem (
91 p_api_version IN NUMBER,
92 p_mfg_org_cd IN VARCHAR2,
93 p_org_cd IN VARCHAR2,
94 p_org_dim_lvl_cd IN VARCHAR2,
95 p_org_lvl_val IN VARCHAR2,
96 p_exp_org_level IN VARCHAR2,
97 p_loc_dim_lvl_cd IN VARCHAR2,
98 p_loc_lvl_val IN VARCHAR2,
99 p_exp_loc_level IN VARCHAR2,
100 p_item_dim_lvl_cd IN VARCHAR2,
101 p_item_lvl_val IN VARCHAR2,
102 p_exp_item_level IN VARCHAR2,
103 p_time_dim_lvl_cd IN VARCHAR2,
104 p_time_lvl_val IN VARCHAR2,
105 p_exp_time_level IN VARCHAR2,
106 p_attribute1 IN VARCHAR2,
107 p_attribute2 IN VARCHAR2,
108 p_attribute3 IN VARCHAR2,
109 p_attribute4 IN VARCHAR2,
110 p_attribute5 IN VARCHAR2,
111 x_return_status OUT NOCOPY VARCHAR2,
112 x_msg_count OUT NOCOPY NUMBER,
113 x_msg_data OUT NOCOPY VARCHAR2,
114 x_job_id OUT NOCOPY VARCHAR2
115 )
116 AS
117 BEGIN
118 invoke_aggr_prc (p_api_version,
119 p_mfg_org_cd,
120 p_org_cd,
121 p_org_dim_lvl_cd,
122 p_org_lvl_val,
123 p_exp_org_level,
124 p_loc_dim_lvl_cd,
125 p_loc_lvl_val,
126 p_exp_loc_level,
127 p_item_dim_lvl_cd,
128 p_item_lvl_val,
129 p_exp_item_level,
130 p_time_dim_lvl_cd,
131 p_time_lvl_val,
132 p_exp_time_level,
133 ddr_webservices_constants.g_rsrid_cd,
134 p_attribute1,
135 p_attribute2,
136 p_attribute3,
137 p_attribute4,
138 p_attribute5,
139 x_return_status,
145
140 x_msg_count,
141 x_msg_data,
142 x_job_id
143 );
144 END getAgrSalesReturnItem;
146
147 PROCEDURE getAgrMrktItemSales (
148 p_api_version IN NUMBER,
149 p_mfg_org_cd IN VARCHAR2,
150 p_org_cd IN VARCHAR2,
151 p_org_dim_lvl_cd IN VARCHAR2,
152 p_org_lvl_val IN VARCHAR2,
153 p_exp_org_level IN VARCHAR2,
154 p_loc_dim_lvl_cd IN VARCHAR2,
155 p_loc_lvl_val IN VARCHAR2,
156 p_exp_loc_level IN VARCHAR2,
157 p_item_dim_lvl_cd IN VARCHAR2,
158 p_item_lvl_val IN VARCHAR2,
159 p_exp_item_level IN VARCHAR2,
160 p_time_dim_lvl_cd IN VARCHAR2,
161 p_time_lvl_val IN VARCHAR2,
162 p_exp_time_level IN VARCHAR2,
163 p_attribute1 IN VARCHAR2,
164 p_attribute2 IN VARCHAR2,
165 p_attribute3 IN VARCHAR2,
166 p_attribute4 IN VARCHAR2,
167 p_attribute5 IN VARCHAR2,
168 x_return_status OUT NOCOPY VARCHAR2,
169 x_msg_count OUT NOCOPY NUMBER,
170 x_msg_data OUT NOCOPY VARCHAR2,
171 x_job_id OUT NOCOPY VARCHAR2
172 )
173 AS
174 BEGIN
175 invoke_aggr_prc (p_api_version,
176 p_mfg_org_cd,
177 p_org_cd,
178 p_org_dim_lvl_cd,
179 p_org_lvl_val,
183 p_exp_loc_level,
180 p_exp_org_level,
181 p_loc_dim_lvl_cd,
182 p_loc_lvl_val,
184 p_item_dim_lvl_cd,
185 p_item_lvl_val,
186 p_exp_item_level,
187 p_time_dim_lvl_cd,
188 p_time_lvl_val,
189 p_exp_time_level,
190 ddr_webservices_constants.g_misd_cd,
191 p_attribute1,
192 p_attribute2,
193 p_attribute3,
194 p_attribute4,
195 p_attribute5,
196 x_return_status,
197 x_msg_count,
198 x_msg_data,
199 x_job_id
200 );
201 END getAgrMrktItemSales;
202
203
204 PROCEDURE getAgrPrmtinPlan (
205 p_api_version IN NUMBER,
206 p_mfg_org_cd IN VARCHAR2,
207 p_org_cd IN VARCHAR2,
208 p_org_dim_lvl_cd IN VARCHAR2,
209 p_org_lvl_val IN VARCHAR2,
210 p_exp_org_level IN VARCHAR2,
211 p_loc_dim_lvl_cd IN VARCHAR2,
212 p_loc_lvl_val IN VARCHAR2,
213 p_exp_loc_level IN VARCHAR2,
214 p_item_dim_lvl_cd IN VARCHAR2,
215 p_item_lvl_val IN VARCHAR2,
216 p_exp_item_level IN VARCHAR2,
217 p_time_dim_lvl_cd IN VARCHAR2,
218 p_time_lvl_val IN VARCHAR2,
219 p_exp_time_level IN VARCHAR2,
220 p_attribute1 IN VARCHAR2,
221 p_attribute2 IN VARCHAR2,
222 p_attribute3 IN VARCHAR2,
223 p_attribute4 IN VARCHAR2,
224 p_attribute5 IN VARCHAR2,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_msg_data OUT NOCOPY VARCHAR2,
228 x_job_id OUT NOCOPY VARCHAR2
229 )
230 AS
231 BEGIN
232 invoke_aggr_prc (p_api_version,
233 p_mfg_org_cd,
234 p_org_cd,
235 p_org_dim_lvl_cd,
236 p_org_lvl_val,
237 p_exp_org_level,
238 p_loc_dim_lvl_cd,
239 p_loc_lvl_val,
240 p_exp_loc_level,
241 p_item_dim_lvl_cd,
242 p_item_lvl_val,
243 p_exp_item_level,
244 p_time_dim_lvl_cd,
245 p_time_lvl_val,
246 p_exp_time_level,
247 ddr_webservices_constants.g_pp_cd,
248 p_attribute1,
249 p_attribute2,
250 p_attribute3,
251 p_attribute4,
252 p_attribute5,
253 x_return_status,
254 x_msg_count,
255 x_msg_data,
256 x_job_id
257 );
258 END getAgrPrmtinPlan;
259
260 PROCEDURE getAgrRtlInvItmDat (
261 p_api_version IN NUMBER,
262 p_mfg_org_cd IN VARCHAR2,
263 p_org_cd IN VARCHAR2,
264 p_org_dim_lvl_cd IN VARCHAR2,
265 p_org_lvl_val IN VARCHAR2,
266 p_exp_org_level IN VARCHAR2,
267 p_loc_dim_lvl_cd IN VARCHAR2,
268 p_loc_lvl_val IN VARCHAR2,
269 p_exp_loc_level IN VARCHAR2,
270 p_item_dim_lvl_cd IN VARCHAR2,
271 p_item_lvl_val IN VARCHAR2,
272 p_exp_item_level IN VARCHAR2,
273 p_time_dim_lvl_cd IN VARCHAR2,
274 p_time_lvl_val IN VARCHAR2,
275 p_exp_time_level IN VARCHAR2,
276 p_attribute1 IN VARCHAR2,
277 p_attribute2 IN VARCHAR2,
278 p_attribute3 IN VARCHAR2,
279 p_attribute4 IN VARCHAR2,
280 p_attribute5 IN VARCHAR2,
281 x_return_status OUT NOCOPY VARCHAR2,
282 x_msg_count OUT NOCOPY NUMBER,
283 x_msg_data OUT NOCOPY VARCHAR2,
284 x_job_id OUT NOCOPY VARCHAR2
285 )
286 AS
287 BEGIN
288 invoke_aggr_prc (p_api_version,
289 p_mfg_org_cd,
290 p_org_cd,
291 p_org_dim_lvl_cd,
292 p_org_lvl_val,
293 p_exp_org_level,
294 p_loc_dim_lvl_cd,
295 p_loc_lvl_val,
296 p_exp_loc_level,
297 p_item_dim_lvl_cd,
298 p_item_lvl_val,
299 p_exp_item_level,
300 p_time_dim_lvl_cd,
301 p_time_lvl_val,
302 p_exp_time_level,
303 ddr_webservices_constants.g_riid_cd,
304 p_attribute1,
305 p_attribute2,
306 p_attribute3,
307 p_attribute4,
308 p_attribute5,
309 x_return_status,
313 );
310 x_msg_count,
311 x_msg_data,
312 x_job_id
314 END getAgrRtlInvItmDat;
315
316
317 PROCEDURE getAgrRetailerOrderItm (
318 p_api_version IN NUMBER,
319 p_mfg_org_cd IN VARCHAR2,
320 p_org_cd IN VARCHAR2,
321 p_org_dim_lvl_cd IN VARCHAR2,
322 p_org_lvl_val IN VARCHAR2,
323 p_exp_org_level IN VARCHAR2,
324 p_loc_dim_lvl_cd IN VARCHAR2,
325 p_loc_lvl_val IN VARCHAR2,
326 p_exp_loc_level IN VARCHAR2,
327 p_item_dim_lvl_cd IN VARCHAR2,
328 p_item_lvl_val IN VARCHAR2,
329 p_exp_item_level IN VARCHAR2,
330 p_time_dim_lvl_cd IN VARCHAR2,
331 p_time_lvl_val IN VARCHAR2,
332 p_exp_time_level IN VARCHAR2,
333 p_attribute1 IN VARCHAR2,
334 p_attribute2 IN VARCHAR2,
335 p_attribute3 IN VARCHAR2,
336 p_attribute4 IN VARCHAR2,
337 p_attribute5 IN VARCHAR2,
338 x_return_status OUT NOCOPY VARCHAR2,
339 x_msg_count OUT NOCOPY NUMBER,
340 x_msg_data OUT NOCOPY VARCHAR2,
341 x_job_id OUT NOCOPY VARCHAR2
342 )
343 AS
344 BEGIN
345 invoke_aggr_prc (p_api_version,
346 p_mfg_org_cd,
347 p_org_cd,
348 p_org_dim_lvl_cd,
349 p_org_lvl_val,
350 p_exp_org_level,
351 p_loc_dim_lvl_cd,
352 p_loc_lvl_val,
353 p_exp_loc_level,
354 p_item_dim_lvl_cd,
355 p_item_lvl_val,
356 p_exp_item_level,
357 p_time_dim_lvl_cd,
358 p_time_lvl_val,
359 p_exp_time_level,
360 ddr_webservices_constants.g_roid_cd,
361 p_attribute1,
362 p_attribute2,
363 p_attribute3,
364 p_attribute4,
365 p_attribute5,
366 x_return_status,
367 x_msg_count,
368 x_msg_data,
369 x_job_id
370 );
371 END getAgrRetailerOrderItm;
372
373 PROCEDURE getAgrSaleFrecastItm (
374 p_api_version IN NUMBER,
375 p_mfg_org_cd IN VARCHAR2,
376 p_org_cd IN VARCHAR2,
377 p_org_dim_lvl_cd IN VARCHAR2,
378 p_org_lvl_val IN VARCHAR2,
379 p_exp_org_level IN VARCHAR2,
380 p_loc_dim_lvl_cd IN VARCHAR2,
381 p_loc_lvl_val IN VARCHAR2,
382 p_exp_loc_level IN VARCHAR2,
383 p_item_dim_lvl_cd IN VARCHAR2,
384 p_item_lvl_val IN VARCHAR2,
385 p_exp_item_level IN VARCHAR2,
386 p_time_dim_lvl_cd IN VARCHAR2,
387 p_time_lvl_val IN VARCHAR2,
388 p_exp_time_level IN VARCHAR2,
389 p_attribute1 IN VARCHAR2,
390 p_attribute2 IN VARCHAR2,
391 p_attribute3 IN VARCHAR2,
392 p_attribute4 IN VARCHAR2,
393 p_attribute5 IN VARCHAR2,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2,
397 x_job_id OUT NOCOPY VARCHAR2
398 )
399 AS
400 BEGIN
401 invoke_aggr_prc (p_api_version,
402 p_mfg_org_cd,
403 p_org_cd,
404 p_org_dim_lvl_cd,
405 p_org_lvl_val,
406 p_exp_org_level,
407 p_loc_dim_lvl_cd,
408 p_loc_lvl_val,
409 p_exp_loc_level,
410 p_item_dim_lvl_cd,
411 p_item_lvl_val,
412 p_exp_item_level,
413 p_time_dim_lvl_cd,
414 p_time_lvl_val,
415 p_exp_time_level,
416 ddr_webservices_constants.g_sfid_cd,
417 p_attribute1,
418 p_attribute2,
419 p_attribute3,
420 p_attribute4,
421 p_attribute5,
422 x_return_status,
423 x_msg_count,
424 x_msg_data,
425 x_job_id
426 );
427 END getAgrSaleFrecastItm;
428
429 PROCEDURE getAgrRetailerShipItem (
430 p_api_version IN NUMBER,
431 p_mfg_org_cd IN VARCHAR2,
432 p_org_cd IN VARCHAR2,
433 p_org_dim_lvl_cd IN VARCHAR2,
434 p_org_lvl_val IN VARCHAR2,
435 p_exp_org_level IN VARCHAR2,
436 p_loc_dim_lvl_cd IN VARCHAR2,
437 p_loc_lvl_val IN VARCHAR2,
438 p_exp_loc_level IN VARCHAR2,
442 p_time_dim_lvl_cd IN VARCHAR2,
439 p_item_dim_lvl_cd IN VARCHAR2,
440 p_item_lvl_val IN VARCHAR2,
441 p_exp_item_level IN VARCHAR2,
443 p_time_lvl_val IN VARCHAR2,
444 p_exp_time_level IN VARCHAR2,
445 p_attribute1 IN VARCHAR2,
446 p_attribute2 IN VARCHAR2,
447 p_attribute3 IN VARCHAR2,
448 p_attribute4 IN VARCHAR2,
449 p_attribute5 IN VARCHAR2,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_msg_data OUT NOCOPY VARCHAR2,
453 x_job_id OUT NOCOPY VARCHAR2
454 )
455 AS
456 BEGIN
457 invoke_aggr_prc (p_api_version,
458 p_mfg_org_cd,
459 p_org_cd,
460 p_org_dim_lvl_cd,
461 p_org_lvl_val,
462 p_exp_org_level,
463 p_loc_dim_lvl_cd,
464 p_loc_lvl_val,
465 p_exp_loc_level,
466 p_item_dim_lvl_cd,
467 p_item_lvl_val,
468 p_exp_item_level,
469 p_time_dim_lvl_cd,
470 p_time_lvl_val,
471 p_exp_time_level,
472 ddr_webservices_constants.g_rsid_cd,
473 p_attribute1,
474 p_attribute2,
475 p_attribute3,
476 p_attribute4,
477 p_attribute5,
478 x_return_status,
479 x_msg_count,
480 x_msg_data,
481 x_job_id
482 );
483 END getAgrRetailerShipItem;
484
485 PROCEDURE getSalesReturnItem (
486 p_api_version IN NUMBER,
487 p_mfg_org_cd IN VARCHAR2,
488 p_org_cd IN VARCHAR2,
489 p_org_dim_lvl_cd IN VARCHAR2,
490 p_org_lvl_val IN VARCHAR2,
491 p_loc_dim_lvl_cd IN VARCHAR2,
492 p_loc_lvl_val IN VARCHAR2,
493 p_item_dim_lvl_cd IN VARCHAR2,
494 p_item_lvl_val IN VARCHAR2,
495 p_time_dim_lvl_cd IN VARCHAR2,
496 p_time_lvl_val IN VARCHAR2,
497 p_attribute1 IN VARCHAR2,
498 p_attribute2 IN VARCHAR2,
499 p_attribute3 IN VARCHAR2,
500 p_attribute4 IN VARCHAR2,
501 p_attribute5 IN VARCHAR2,
502 x_return_status OUT NOCOPY VARCHAR2,
503 x_msg_count OUT NOCOPY NUMBER,
504 x_msg_data OUT NOCOPY VARCHAR2,
505 x_job_id OUT NOCOPY VARCHAR2
506 )
507 AS
508 BEGIN
509 -- DBMS_OUT NOCOPY PUT.put_line (' Inside getSalesReturnItem');
510 invoke_detail_prc (p_api_version,
511 p_mfg_org_cd,
512 p_org_cd,
513 p_org_dim_lvl_cd,
514 p_org_lvl_val,
515 p_loc_dim_lvl_cd,
516 p_loc_lvl_val,
517 p_item_dim_lvl_cd,
518 p_item_lvl_val,
519 p_time_dim_lvl_cd,
520 p_time_lvl_val,
521 ddr_webservices_constants.g_rsrid_cd,
522 p_attribute1,
523 p_attribute2,
524 p_attribute3,
525 p_attribute4,
526 p_attribute5,
527 x_return_status,
528 x_msg_count,
529 x_msg_data,
530 x_job_id
531 );
532 -- DBMS_OUT NOCOPY PUT.put_line (' x_return_status ' || x_return_status);
533 -- x_return_status := x_return_status;
534 -- x_msg_count := x_msg_count;
535 -- x_msg_data := x_msg_data ;
536 END getSalesReturnItem;
537
538 PROCEDURE getMrktItemSales (
539 p_api_version IN NUMBER,
540 p_mfg_org_cd IN VARCHAR2,
541 p_org_cd IN VARCHAR2,
542 p_org_dim_lvl_cd IN VARCHAR2,
543 p_org_lvl_val IN VARCHAR2,
544 p_loc_dim_lvl_cd IN VARCHAR2,
545 p_loc_lvl_val IN VARCHAR2,
546 p_item_dim_lvl_cd IN VARCHAR2,
547 p_item_lvl_val IN VARCHAR2,
548 p_time_dim_lvl_cd IN VARCHAR2,
549 p_time_lvl_val IN VARCHAR2,
550 p_attribute1 IN VARCHAR2,
551 p_attribute2 IN VARCHAR2,
552 p_attribute3 IN VARCHAR2,
553 p_attribute4 IN VARCHAR2,
554 p_attribute5 IN VARCHAR2,
555 x_return_status OUT NOCOPY VARCHAR2,
556 x_msg_count OUT NOCOPY NUMBER,
557 x_msg_data OUT NOCOPY VARCHAR2,
558 x_job_id OUT NOCOPY VARCHAR2
559 )
560 AS
561 BEGIN
562 invoke_detail_prc (p_api_version,
563 p_mfg_org_cd,
564 p_org_cd,
568 p_loc_lvl_val,
565 p_org_dim_lvl_cd,
566 p_org_lvl_val,
567 p_loc_dim_lvl_cd,
569 p_item_dim_lvl_cd,
570 p_item_lvl_val,
571 p_time_dim_lvl_cd,
572 p_time_lvl_val,
573 ddr_webservices_constants.g_misd_cd,
574 p_attribute1,
575 p_attribute2,
576 p_attribute3,
577 p_attribute4,
578 p_attribute5,
579 x_return_status,
580 x_msg_count,
581 x_msg_data,
582 x_job_id
583 );
584 END getMrktItemSales;
585
586 PROCEDURE getPrmtinPlan (
587 p_api_version IN NUMBER,
591 p_org_lvl_val IN VARCHAR2,
588 p_mfg_org_cd IN VARCHAR2,
589 p_org_cd IN VARCHAR2,
590 p_org_dim_lvl_cd IN VARCHAR2,
592 p_loc_dim_lvl_cd IN VARCHAR2,
593 p_loc_lvl_val IN VARCHAR2,
594 p_item_dim_lvl_cd IN VARCHAR2,
595 p_item_lvl_val IN VARCHAR2,
596 p_time_dim_lvl_cd IN VARCHAR2,
597 p_time_lvl_val IN VARCHAR2,
598 p_attribute1 IN VARCHAR2,
599 p_attribute2 IN VARCHAR2,
600 p_attribute3 IN VARCHAR2,
601 p_attribute4 IN VARCHAR2,
602 p_attribute5 IN VARCHAR2,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_msg_count OUT NOCOPY NUMBER,
605 x_msg_data OUT NOCOPY VARCHAR2,
606 x_job_id OUT NOCOPY VARCHAR2
607 )
608 AS
609 BEGIN
610 invoke_detail_prc (p_api_version,
611 p_mfg_org_cd,
612 p_org_cd,
613 p_org_dim_lvl_cd,
614 p_org_lvl_val,
615 p_loc_dim_lvl_cd,
616 p_loc_lvl_val,
617 p_item_dim_lvl_cd,
618 p_item_lvl_val,
619 p_time_dim_lvl_cd,
620 p_time_lvl_val,
621 ddr_webservices_constants.g_pp_cd,
622 p_attribute1,
623 p_attribute2,
624 p_attribute3,
625 p_attribute4,
626 p_attribute5,
627 x_return_status,
628 x_msg_count,
629 x_msg_data,
630 x_job_id
631 );
632 END getPrmtinPlan;
633
634 PROCEDURE getRtlInvItmDat (
635 p_api_version IN NUMBER,
636 p_mfg_org_cd IN VARCHAR2,
637 p_org_cd IN VARCHAR2,
638 p_org_dim_lvl_cd IN VARCHAR2,
639 p_org_lvl_val IN VARCHAR2,
640 p_loc_dim_lvl_cd IN VARCHAR2,
641 p_loc_lvl_val IN VARCHAR2,
642 p_item_dim_lvl_cd IN VARCHAR2,
643 p_item_lvl_val IN VARCHAR2,
644 p_time_dim_lvl_cd IN VARCHAR2,
645 p_time_lvl_val IN VARCHAR2,
646 p_attribute1 IN VARCHAR2,
647 p_attribute2 IN VARCHAR2,
648 p_attribute3 IN VARCHAR2,
649 p_attribute4 IN VARCHAR2,
650 p_attribute5 IN VARCHAR2,
651 x_return_status OUT NOCOPY VARCHAR2,
655 )
652 x_msg_count OUT NOCOPY NUMBER,
653 x_msg_data OUT NOCOPY VARCHAR2,
654 x_job_id OUT NOCOPY VARCHAR2
656 AS
657 BEGIN
658 invoke_detail_prc (p_api_version,
659 p_mfg_org_cd,
660 p_org_cd,
661 p_org_dim_lvl_cd,
662 p_org_lvl_val,
663 p_loc_dim_lvl_cd,
664 p_loc_lvl_val,
665 p_item_dim_lvl_cd,
666 p_item_lvl_val,
667 p_time_dim_lvl_cd,
668 p_time_lvl_val,
669 ddr_webservices_constants.g_riid_cd,
670 p_attribute1,
671 p_attribute2,
672 p_attribute3,
673 p_attribute4,
674 p_attribute5,
675 x_return_status,
676 x_msg_count,
677 x_msg_data,
678 x_job_id
679 );
680 END getRtlInvItmDat;
681
682 PROCEDURE getRetailerOrderItm (
683 p_api_version IN NUMBER,
684 p_mfg_org_cd IN VARCHAR2,
685 p_org_cd IN VARCHAR2,
686 p_org_dim_lvl_cd IN VARCHAR2,
687 p_org_lvl_val IN VARCHAR2,
688 p_loc_dim_lvl_cd IN VARCHAR2,
689 p_loc_lvl_val IN VARCHAR2,
690 p_item_dim_lvl_cd IN VARCHAR2,
691 p_item_lvl_val IN VARCHAR2,
692 p_time_dim_lvl_cd IN VARCHAR2,
693 p_time_lvl_val IN VARCHAR2,
694 p_attribute1 IN VARCHAR2,
695 p_attribute2 IN VARCHAR2,
696 p_attribute3 IN VARCHAR2,
697 p_attribute4 IN VARCHAR2,
698 p_attribute5 IN VARCHAR2,
699 x_return_status OUT NOCOPY VARCHAR2,
700 x_msg_count OUT NOCOPY NUMBER,
701 x_msg_data OUT NOCOPY VARCHAR2,
702 x_job_id OUT NOCOPY VARCHAR2
703 )
704 AS
705 BEGIN
706 invoke_detail_prc (p_api_version,
707 p_mfg_org_cd,
708 p_org_cd,
709 p_org_dim_lvl_cd,
710 p_org_lvl_val,
711 p_loc_dim_lvl_cd,
712 p_loc_lvl_val,
713 p_item_dim_lvl_cd,
714 p_item_lvl_val,
715 p_time_dim_lvl_cd,
716 p_time_lvl_val,
717 ddr_webservices_constants.g_roid_cd,
718 p_attribute1,
719 p_attribute2,
720 p_attribute3,
721 p_attribute4,
722 p_attribute5,
723 x_return_status,
724 x_msg_count,
725 x_msg_data,
726 x_job_id
727 );
728 END getRetailerOrderItm;
729
730 PROCEDURE getSaleFrecastItm (
731 p_api_version IN NUMBER,
732 p_mfg_org_cd IN VARCHAR2,
733 p_org_cd IN VARCHAR2,
734 p_org_dim_lvl_cd IN VARCHAR2,
735 p_org_lvl_val IN VARCHAR2,
736 p_loc_dim_lvl_cd IN VARCHAR2,
737 p_loc_lvl_val IN VARCHAR2,
738 p_item_dim_lvl_cd IN VARCHAR2,
739 p_item_lvl_val IN VARCHAR2,
740 p_time_dim_lvl_cd IN VARCHAR2,
741 p_time_lvl_val IN VARCHAR2,
742 p_attribute1 IN VARCHAR2,
743 p_attribute2 IN VARCHAR2,
744 p_attribute3 IN VARCHAR2,
745 p_attribute4 IN VARCHAR2,
746 p_attribute5 IN VARCHAR2,
747 x_return_status OUT NOCOPY VARCHAR2,
748 x_msg_count OUT NOCOPY NUMBER,
749 x_msg_data OUT NOCOPY VARCHAR2,
750 x_job_id OUT NOCOPY VARCHAR2
751 )
752 AS
753 BEGIN
754 invoke_detail_prc (p_api_version,
755 p_mfg_org_cd,
756 p_org_cd,
757 p_org_dim_lvl_cd,
758 p_org_lvl_val,
759 p_loc_dim_lvl_cd,
760 p_loc_lvl_val,
761 p_item_dim_lvl_cd,
762 p_item_lvl_val,
763 p_time_dim_lvl_cd,
764 p_time_lvl_val,
765 ddr_webservices_constants.g_sfid_cd,
766 p_attribute1,
767 p_attribute2,
768 p_attribute3,
769 p_attribute4,
770 p_attribute5,
771 x_return_status,
772 x_msg_count,
773 x_msg_data,
774 x_job_id
775 );
776 END getSaleFrecastItm;
777
778 PROCEDURE getRetailerShipItem (
779 p_api_version IN NUMBER,
780 p_mfg_org_cd IN VARCHAR2,
781 p_org_cd IN VARCHAR2,
782 p_org_dim_lvl_cd IN VARCHAR2,
786 p_item_dim_lvl_cd IN VARCHAR2,
783 p_org_lvl_val IN VARCHAR2,
784 p_loc_dim_lvl_cd IN VARCHAR2,
785 p_loc_lvl_val IN VARCHAR2,
787 p_item_lvl_val IN VARCHAR2,
788 p_time_dim_lvl_cd IN VARCHAR2,
789 p_time_lvl_val IN VARCHAR2,
790 p_attribute1 IN VARCHAR2,
791 p_attribute2 IN VARCHAR2,
792 p_attribute3 IN VARCHAR2,
793 p_attribute4 IN VARCHAR2,
794 p_attribute5 IN VARCHAR2,
795 x_return_status OUT NOCOPY VARCHAR2,
796 x_msg_count OUT NOCOPY NUMBER,
797 x_msg_data OUT NOCOPY VARCHAR2,
798 x_job_id OUT NOCOPY VARCHAR2
799 )
800 AS
801 BEGIN
802 invoke_detail_prc (p_api_version,
803 p_mfg_org_cd,
804 p_org_cd,
805 p_org_dim_lvl_cd,
806 p_org_lvl_val,
807 p_loc_dim_lvl_cd,
808 p_loc_lvl_val,
809 p_item_dim_lvl_cd,
810 p_item_lvl_val,
811 p_time_dim_lvl_cd,
812 p_time_lvl_val,
813 ddr_webservices_constants.g_rsid_cd,
814 p_attribute1,
815 p_attribute2,
816 p_attribute3,
817 p_attribute4,
818 p_attribute5,
819 x_return_status,
820 x_msg_count,
821 x_msg_data,
822 x_job_id
823 );
824 END getRetailerShipItem;
825
826 PROCEDURE invoke_aggr_prc (
827 p_api_version IN NUMBER,
828 p_mfg_org_cd IN VARCHAR2,
829 p_org_cd IN VARCHAR2,
830 p_org_dim_lvl_cd IN VARCHAR2,
831 p_org_lvl_val IN VARCHAR2,
832 p_exp_org_level IN VARCHAR2,
833 p_loc_dim_lvl_cd IN VARCHAR2,
834 p_loc_lvl_val IN VARCHAR2,
835 p_exp_loc_level IN VARCHAR2,
836 p_item_dim_lvl_cd IN VARCHAR2,
837 p_item_lvl_val IN VARCHAR2,
838 p_exp_item_level IN VARCHAR2,
839 p_time_dim_lvl_cd IN VARCHAR2,
840 p_time_lvl_val IN VARCHAR2,
841 p_exp_time_level IN VARCHAR2,
842 p_fact_code IN VARCHAR2,
843 p_attribute1 IN VARCHAR2,
844 p_attribute2 IN VARCHAR2,
845 p_attribute3 IN VARCHAR2,
846 p_attribute4 IN VARCHAR2,
847 p_attribute5 IN VARCHAR2,
848 x_return_status OUT NOCOPY VARCHAR2,
849 x_msg_count OUT NOCOPY NUMBER,
850 x_msg_data OUT NOCOPY VARCHAR2,
851 x_job_id OUT NOCOPY VARCHAR2
852 )
853 IS
854 l_job_id NUMBER := NULL;
855 l_proc_str VARCHAR2 (1000) := NULL;
856 BEGIN
857
858 --get the job id from sequence
859 l_job_id := get_ddr_ws_job_id_seq_nextval;
860
861 INSERT INTO ddr_ws_job(job_id, status, SRC_SYS_IDNT,SRC_SYS_DT, CRTD_BY_DSR, LAST_UPDT_BY_DSR, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
862 VALUES (l_job_id, ddr_webservices_constants.g_ret_sts_initialize,'-1', sysdate, '-1', '-1', -1,sysdate, -1, sysdate,-1);
863
864
865 --triggere the DBMS_SCHEDULER to run the job which writes data to xml file
866 l_proc_str := 'BEGIN ddr_webservices_pub.ddr_fact_aggr_prc(';
867 l_proc_str := l_proc_str || p_api_version || ', ';
868 l_proc_str := l_proc_str || '''' || l_job_id || ''', ';
869 l_proc_str := l_proc_str || '''' || p_mfg_org_cd || ''', ';
870 l_proc_str := l_proc_str || '''' || p_org_cd || ''', ';
871 l_proc_str := l_proc_str || '''' || p_org_dim_lvl_cd || ''', ';
872 l_proc_str := l_proc_str || '''' || p_org_lvl_val || ''', ';
873 l_proc_str := l_proc_str || '''' || p_exp_org_level || ''', ';
874 l_proc_str := l_proc_str || '''' || p_loc_dim_lvl_cd || ''', ';
875 l_proc_str := l_proc_str || '''' || p_loc_lvl_val || ''', ';
876 l_proc_str := l_proc_str || '''' || p_exp_loc_level || ''', ';
877 l_proc_str := l_proc_str || '''' || p_item_dim_lvl_cd || ''', ';
878 l_proc_str := l_proc_str || '''' || p_item_lvl_val || ''', ';
879 l_proc_str := l_proc_str || '''' || p_exp_item_level || ''', ';
880 l_proc_str := l_proc_str || '''' || p_time_dim_lvl_cd || ''', ';
881 l_proc_str := l_proc_str || '''' || p_time_lvl_val || ''', ';
882 l_proc_str := l_proc_str || '''' || p_exp_time_level || ''', ';
883 l_proc_str := l_proc_str || '''' || p_fact_code || ''', ';
884 l_proc_str := l_proc_str || '''' || p_attribute1 || ''', ';
885 l_proc_str := l_proc_str || '''' || p_attribute2 || ''', ';
886 l_proc_str := l_proc_str || '''' || p_attribute3 || ''', ';
887 l_proc_str := l_proc_str || '''' || p_attribute4 || ''', ';
888 l_proc_str := l_proc_str || '''' || p_attribute5 || ''' ); END;';
889
890
891 dbms_scheduler.create_job (job_name => 'DDR_AGGR_WS_JOB_' || l_job_id,
892 job_type => 'PLSQL_BLOCK',
893 job_action => l_proc_str,
894 start_date => NULL,
895 repeat_interval => NULL,
896 auto_drop => TRUE,
900 WHEN NO_DATA_FOUND THEN
897 enabled => TRUE);
898 x_job_id := TO_CHAR (l_job_id);
899 EXCEPTION
901 x_return_status := ddr_webservices_constants.g_ret_sts_error;
902 x_msg_count := 1;
903 x_msg_data := 'NO DATA FOUND'||sqlcode||' Error message:'||sqlerrm;
904 WHEN OTHERS THEN
905 x_return_status := ddr_webservices_constants.g_ret_sts_unexp_error;
906 x_msg_count := 1;
907 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
908 END invoke_aggr_prc;
909
910 PROCEDURE invoke_detail_prc (
911 p_api_version IN NUMBER,
912 p_mfg_org_cd IN VARCHAR2,
913 p_org_cd IN VARCHAR2,
914 p_org_dim_lvl_cd IN VARCHAR2,
915 p_org_lvl_val IN VARCHAR2,
916 p_loc_dim_lvl_cd IN VARCHAR2,
917 p_loc_lvl_val IN VARCHAR2,
918 p_item_dim_lvl_cd IN VARCHAR2,
919 p_item_lvl_val IN VARCHAR2,
920 p_time_dim_lvl_cd IN VARCHAR2,
921 p_time_lvl_val IN VARCHAR2,
922 p_fact_code IN VARCHAR2,
923 p_attribute1 IN VARCHAR2,
924 p_attribute2 IN VARCHAR2,
925 p_attribute3 IN VARCHAR2,
926 p_attribute4 IN VARCHAR2,
927 p_attribute5 IN VARCHAR2,
928 x_return_status OUT NOCOPY VARCHAR2,
929 x_msg_count OUT NOCOPY NUMBER,
930 x_msg_data OUT NOCOPY VARCHAR2,
931 x_job_id OUT NOCOPY VARCHAR2
932 )
933 IS
934 l_job_id NUMBER := NULL;
935 l_proc_str VARCHAR2 (32767) := NULL;
936 BEGIN
937 l_job_id := get_ddr_ws_job_id_seq_nextval;
938
939 INSERT INTO ddr_ws_job(job_id, status, SRC_SYS_IDNT,SRC_SYS_DT, CRTD_BY_DSR, LAST_UPDT_BY_DSR, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN)
940 VALUES (l_job_id, ddr_webservices_constants.g_ret_sts_initialize,'-1', sysdate, '-1', '-1', -1,sysdate, -1, sysdate,-1);
941
942 --triggere the DBMS_SCHEDULER to run the job which writes data to xml file
943 l_proc_str := 'BEGIN ddr_webservices_pub.ddr_fact_details_prc (';
944 l_proc_str := l_proc_str || p_api_version|| ', ';
945 l_proc_str := l_proc_str || '''' || l_job_id || ''', ';
946 l_proc_str := l_proc_str || '''' || p_mfg_org_cd || ''', ';
947 l_proc_str := l_proc_str || '''' || p_org_cd || ''', ';
948 l_proc_str := l_proc_str || '''' || p_org_dim_lvl_cd || ''', ';
949 l_proc_str := l_proc_str || '''' || p_org_lvl_val || ''', ';
950 l_proc_str := l_proc_str || '''' || p_loc_dim_lvl_cd || ''', ';
951 l_proc_str := l_proc_str || '''' || p_loc_lvl_val || ''', ';
952 l_proc_str := l_proc_str || '''' || p_item_dim_lvl_cd || ''', ';
953 l_proc_str := l_proc_str || '''' || p_item_lvl_val || ''', ';
954 l_proc_str := l_proc_str || '''' || p_time_dim_lvl_cd || ''', ';
955 l_proc_str := l_proc_str || '''' || p_time_lvl_val || ''', ';
956 l_proc_str := l_proc_str || '''' || p_fact_code || ''', ';
957 l_proc_str := l_proc_str || '''' || p_attribute1 || ''', ';
958 l_proc_str := l_proc_str || '''' || p_attribute2 || ''', ';
959 l_proc_str := l_proc_str || '''' || p_attribute3 || ''', ';
960 l_proc_str := l_proc_str || '''' || p_attribute4 || ''', ';
961 l_proc_str := l_proc_str || '''' || p_attribute5 || ''' ); END;';
962
963
964 dbms_scheduler.create_job (job_name => 'DDR_DETAIL_WS_JOB_' || l_job_id,
965 job_type => 'PLSQL_BLOCK',
966 job_action => l_proc_str,
967 start_date => NULL,
968 repeat_interval => NULL,
969 auto_drop => TRUE,
970 enabled => TRUE);
971 x_job_id := TO_CHAR (l_job_id);
972 EXCEPTION
973 WHEN NO_DATA_FOUND THEN
974 x_return_status := ddr_webservices_constants.g_ret_sts_error;
975 x_msg_count := 1;
976 x_msg_data := 'NO DATA FOUND'||sqlcode||' Error message:'||sqlerrm;
977 WHEN OTHERS THEN
978 x_return_status := ddr_webservices_constants.g_ret_sts_unexp_error;
979 x_msg_count := 1;
980 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
981 END invoke_detail_prc;
982
983 FUNCTION get_ddr_ws_job_id_seq_nextval
984 RETURN NUMBER
985 IS
986 l_next_val NUMBER := NULL;
987 BEGIN
988 SELECT ddr_ws_job_seq.NEXTVAL
989 INTO l_next_val
990 FROM DUAL;
991
992 RETURN l_next_val;
993 END get_ddr_ws_job_id_seq_nextval;
994
995 PROCEDURE getFileName (
996 p_api_version IN NUMBER,
997 x_job_id IN VARCHAR2,
998 p_mfg_org_cd IN VARCHAR2,
999 x_return_status OUT NOCOPY VARCHAR2,
1000 x_msg_count OUT NOCOPY NUMBER,
1001 x_msg_data OUT NOCOPY VARCHAR2,
1002 x_file_path OUT NOCOPY VARCHAR2
1003 )
1004 IS
1005 l_file_path VARCHAR2 (32767) := NULL;
1006 l_job_status VARCHAR2 (10) := NULL;
1007 l_job_error VARCHAR2 (100) := NULL;
1008 l_dir_path VARCHAR2 (250) := NULL;
1009 --Bug 6880404 change start
1010 l_xml_file_path VARCHAR2 (250) := NULL;
1011 --Bug 6880404 change end
1012 l_dir_name VARCHAR2 (250) := NULL;
1016 CURSOR file_name_cur (job_id_in NUMBER) IS
1013 l_api_ver EXCEPTION;
1014 l_job_id EXCEPTION;
1015 l_mfg_code_null EXCEPTION;
1017 SELECT file_name FROM ddr_ws_job_file_dls WHERE job_id = job_id_in;
1018 file_name_rec file_name_cur%ROWTYPE;
1019 BEGIN
1020 IF p_api_version IS NULL THEN
1021 RAISE l_api_ver;
1022 END IF;
1023 IF x_job_id IS NULL THEN
1024 RAISE l_job_id;
1025 END IF;
1026 IF p_mfg_org_cd IS NULL THEN
1027 RAISE l_mfg_code_null;
1028 END IF;
1029 SELECT status,ERR_MESSAGE INTO l_job_status,l_job_error FROM ddr_ws_job WHERE job_id = x_job_id;
1030 IF l_job_status = ddr_webservices_constants.g_ret_sts_error THEN
1031 x_return_status := ddr_webservices_constants.g_ret_sts_error;
1032 x_msg_count := 1;
1033 x_msg_data := l_job_error;
1034 x_file_path:=l_job_error;
1035 ELSIF l_job_status = ddr_webservices_constants.g_ret_sts_unexp_error THEN
1036 x_return_status := ddr_webservices_constants.g_ret_sts_unexp_error;
1037 x_msg_count := 1;
1038 x_msg_data := l_job_error;
1039 x_file_path:=l_job_error;
1040 ELSIF l_job_status = ddr_webservices_constants.g_ret_sts_initialize THEN
1041 x_return_status := ddr_webservices_constants.g_ret_sts_initialize;
1042 x_msg_count := 1;
1043 x_msg_data := l_job_error;
1044 x_file_path:='Initialized';
1045 ELSIF l_job_status = ddr_webservices_constants.g_ret_sts_running THEN
1046 x_return_status := ddr_webservices_constants.g_ret_sts_running;
1047 x_msg_count := 1;
1048 x_msg_data := l_job_error;
1049 x_file_path:='Running';
1050 ELSIF l_job_status = ddr_webservices_constants.g_ret_sts_success THEN
1051 --get the path to the directory where xml files are stored
1052 --Bug 6880404 change start
1053 ddr_webservices_pub.get_sys_var_val('DDR_WS_FILE_PATH',x_return_status, x_msg_count,x_msg_data,l_xml_file_path);
1054 --Bug 6880404 change start
1055 --get logical directory name from system variable table
1056 --ddr_webservices_pub.get_sys_var_val('OUTPUT_DIR_PATH',x_return_status, x_msg_count,x_msg_data,l_dir_name);
1057 --l_dir_path := l_db_ip_address || '\'||l_dir_name||'\';
1058 l_dir_path := l_xml_file_path;
1059 OPEN file_name_cur (x_job_id);
1060
1061 LOOP
1062 FETCH file_name_cur INTO file_name_rec;
1063
1064 EXIT WHEN file_name_cur%NOTFOUND;
1065 --l_file_path := l_file_path || '<FILE_PATH>'||l_dir_path||file_name_rec.file_name||'</FILE_PATH>'||chr(10);
1066 -- use of chr function is not allowed by GSCC. The following code uses newline inserted in the string edit
1067 l_file_path := l_file_path || '<FILE_PATH>'||l_dir_path||file_name_rec.file_name||'</FILE_PATH>'||'
1068 '||NULL;
1069 END LOOP;
1070 x_file_path := l_file_path;
1071 CLOSE file_name_cur;
1072 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1073 x_msg_count:=null;
1074 x_msg_data:=null;
1075 END IF;
1076 EXCEPTION
1077 WHEN l_api_ver THEN
1078 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1079 x_msg_count:=1;
1080 x_msg_data:='API version number should not be null';
1081 WHEN l_job_id THEN
1082 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1083 x_msg_count:=1;
1084 x_msg_data:='Job id should not be null';
1085 WHEN l_mfg_code_null THEN
1086 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1087 x_msg_count:=1;
1088 x_msg_data:='Manufacturer Organization code should not be null';
1089 WHEN NO_DATA_FOUND THEN
1090 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1091 x_msg_count:=1;
1092 x_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
1093 WHEN OTHERS THEN
1094 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1095 x_msg_count:=1;
1096 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1097 END getFileName;
1098 END;