1 PACKAGE BODY ddr_webservices_pub AS
2 /* $Header: ddrpcwsb.pls 120.8 2008/04/03 09:55:49 vbhave noship $ */
3
4 -- Start of comments
5 -- API name : get_dyn_query
6 -- Type: Private
7 -- Pre-reqs: None.
8 -- Function: to get the dynamic query
9 -- Parameters:
10 -- IN :
11 -- p_api_version IN NUMBER Required
12 -- p_mfg_org_cd IN VARCHAR2Required
13 -- Manufaturer organization code
14 -- p_org_dim_lvl_cd IN VARCHAR2
15 -- Identifies the organization hierarchy level code
16 -- p_org_lvl_val IN VARCHAR2
17 -- Organization hierarchy level code value
18 -- p_exp_org_level IN VARCHAR2
19 -- expected aggregation level of organization hierarchy
20 -- p_loc_dim_lvl_cd IN VARCHAR2
21 -- Identifies the location hierarchy level code
22 -- p_loc_lvl_val IN VARCHAR2
23 -- Location hierarchy level code
24 -- p_exp_loc_level IN VARCHAR2
25 -- Expected aggregation level of location hierarchy
26 -- p_item_dim_lvl_cd IN VARCHAR2
27 -- p_item_lvl_val IN VARCHAR2
28 -- p_exp_item_level IN VARCHAR2
29 -- p_time_dim_lvl_cd IN VARCHAR2
30 -- p_time_lvl_val IN VARCHAR2
31 -- p_exp_time_level IN VARCHAR2
32 -- Version: Current version1.0
33 -- Initial version 1.0
34 -- End of comments
35
36 PROCEDURE get_dyn_query(p_api_version IN NUMBER,
37 p_call_type IN VARCHAR2,
38 p_mfg_org_cd IN VARCHAR2,
39 p_org_cd IN VARCHAR2,
40 p_org_dim_lvl_cd IN VARCHAR2,
41 p_org_lvl_val IN VARCHAR2,
42 p_exp_org_level IN VARCHAR2,
43 p_loc_dim_lvl_cd IN VARCHAR2,
44 p_loc_lvl_val IN VARCHAR2,
45 p_exp_loc_level IN VARCHAR2,
46 p_item_dim_lvl_cd IN VARCHAR2,
47 p_item_lvl_val IN VARCHAR2,
48 p_exp_item_level IN VARCHAR2,
49 p_time_dim_lvl_cd IN VARCHAR2,
50 p_time_lvl_val IN VARCHAR2,
51 p_exp_time_level IN VARCHAR2,
52 p_fact_code IN VARCHAR2,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2,
56 x_dyn_query OUT NOCOPY VARCHAR2);
57
58 -- Start of comments
59 -- API name : get_fact_table
60 -- Type: Private
61 -- Pre-reqs: None.
62 -- Function: to get the name of the fact table
63 -- Parameters:
64 -- IN :
65 -- p_fact_code IN VARCHAR2 Required
66 -- DDR fact code
67 -- OUT NOCOPY :
68 -- fact table name(VARCHAR2)
69 -- Version: Current version1.0
70 -- Initial version 1.0
71 -- End of comments
72 PROCEDURE get_fact_table(p_fact_code IN VARCHAR2,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_count OUT NOCOPY NUMBER,
75 x_msg_data OUT NOCOPY VARCHAR2,
76 x_fact_name OUT NOCOPY VARCHAR2);
77
78 -- Start of comments
79 -- API name : get_aggr_fact_colms
80 -- Type: Private
81 -- Pre-reqs: None.
82 -- Function: to get the fact columns on which aggregate function to be performed.
83 -- Parameters:
84 -- IN :
85 -- p_fact_code IN VARCHAR2 Required
86 -- DDR fact code
87 -- OUT NOCOPY :
88 -- fact columns on which aggregate function to be performed(VARCHAR2)
89 -- Version: Current version1.0
90 -- Initial version 1.0
91 -- End of comments
92 PROCEDURE get_aggr_fact_colms(p_fact_code IN VARCHAR2,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_msg_count OUT NOCOPY NUMBER,
95 x_msg_data OUT NOCOPY VARCHAR2,
96 x_fact_cols OUT NOCOPY VARCHAR2);
97
98 -- Start of comments
99 -- API name : get_detail_fact_colms
100 -- Type: Private
101 -- Pre-reqs: None.
102 -- Function: to get the fact column names.
103 -- Parameters:
104 -- IN :
105 -- p_fact_code IN VARCHAR2Required
106 -- DDR fact code
107 -- OUT NOCOPY :
108 -- fact columns on which aggregate function to be performed(VARCHAR2)
109 -- Version: Current version1.0
110 -- Initial version 1.0
111 -- End of comments
112 PROCEDURE get_detail_fact_colms(p_fact_code IN VARCHAR2,
113 x_return_status OUT NOCOPY VARCHAR2,
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2,
116 x_fact_cols OUT NOCOPY VARCHAR2);
117
118 -- Start of comments
119 -- API name : get_aggr_group_colms
120 -- Type: Private
121 -- Pre-reqs: None.
122 -- Function: to get the name of the columns on which group by clause to be performed
123 -- Parameters:
124 -- IN :
125 -- p_exp_org_level IN VARCHAR2 Required
126 -- Expected organization dimension level code passed by downstream application
127 -- p_exp_loc_level IN VARCHAR2 Required
128 -- Expected location dimension level code passed by downstream application
129 -- p_exp_item_level IN VARCHAR2 Required
130 -- Expected item dimension level code passed by downstream application
131 -- p_exp_time_level IN VARCHAR2 Required
132 -- Expected time dimension level code passed by downstream application
133 -- OUT NOCOPY :
134 -- name of the columns on which group by clause to be performed (VARCHAR2)
135 -- Version: Current version1.0
136 -- Initial version 1.0
137 -- End of comments
138 PROCEDURE get_aggr_group_colms(p_exp_org_level IN VARCHAR2,
139 p_exp_loc_level IN VARCHAR2,
140 p_exp_item_level IN VARCHAR2,
141 p_exp_time_level IN VARCHAR2,
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_count OUT NOCOPY NUMBER,
144 x_msg_data OUT NOCOPY VARCHAR2,
145 x_group_col OUT NOCOPY VARCHAR2);
146
147 -- Start of comments
148 -- API name : write_fact_to_xml_file
149 -- Type: Private
150 -- Pre-reqs: None.
151 -- Function: to write the selected fact data to the xml file
152 -- Parameters:
153 -- IN :
154 -- p_query
155 -- dynamic query to fetch the data
156 -- p_fact_code
157 -- Fact code
158 -- OUT NOCOPY :
159 -- p_job_id
160 -- Job Id
161 -- Version: Current version1.0
162 -- Initial version 1.0
163 -- End of comments
164 PROCEDURE write_fact_to_xml_file(p_query IN VARCHAR2,
165 p_fact_code IN VARCHAR2,
166 p_job_id IN NUMBER,
167 x_return_status OUT NOCOPY VARCHAR2,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2);
170
171 -- Start of comments
172 -- API name : validate_input_params
173 -- Type: Private
174 -- Pre-reqs: None.
175 -- Function: to validate the input parameters
176 -- Parameters:
177 -- IN :
178 -- p_api_version IN NUMBER Required
179 -- p_mfg_org_cd IN VARCHAR2 Required
180 -- Manufaturer organization code
181 -- p_org_dim_lvl_cd IN VARCHAR2
182 -- Identifies the organization hierarchy level code
183 -- p_org_lvl_val IN VARCHAR2
184 -- Organization hierarchy level code value
185 -- p_exp_org_level IN VARCHAR2
186 -- expected aggregation level of organization hierarchy
187 -- p_loc_dim_lvl_cd IN VARCHAR2
188 -- Identifies the location hierarchy level code
189 -- p_loc_lvl_val IN VARCHAR2
190 -- Location hierarchy level code
191 -- p_exp_loc_level IN VARCHAR2
192 -- Expected aggregation level of location hierarchy
193 -- p_item_dim_lvl_cd IN VARCHAR2
194 -- p_item_lvl_val IN VARCHAR2
195 -- p_exp_item_level IN VARCHAR2
196 -- p_time_dim_lvl_cd IN VARCHAR2
197 -- p_time_lvl_val IN VARCHAR2
198 -- p_exp_time_level IN VARCHAR2
199 -- p_fact_code IN VARCHAR2
200 -- p_attribute1 IN VARCHAR2
201 -- p_attribute2 IN VARCHAR2
202 -- p_attribute3 IN VARCHAR2
203 -- p_attribute4 IN VARCHAR2
204 -- p_attribute5 IN VARCHAR2values are ''A' for Aggregate and ''D'' for detail
205 -- OUT NOCOPY :
206 -- fact table name(VARCHAR2)
207 -- Version: Current version1.0
208 -- Initial version 1.0
209 -- End of comments
210
211 PROCEDURE validate_input_params(p_api_version IN NUMBER,
212 p_mfg_org_cd IN VARCHAR2,
213 p_org_cd IN VARCHAR2,
214 p_org_dim_lvl_cd IN VARCHAR2,
215 p_org_lvl_val IN VARCHAR2,
216 p_exp_org_level IN VARCHAR2,
217 p_loc_dim_lvl_cd IN VARCHAR2,
218 p_loc_lvl_val IN VARCHAR2,
219 p_exp_loc_level IN VARCHAR2,
220 p_item_dim_lvl_cd IN VARCHAR2,
221 p_item_lvl_val IN VARCHAR2,
222 p_exp_item_level IN VARCHAR2,
223 p_time_dim_lvl_cd IN VARCHAR2,
224 p_time_lvl_val IN VARCHAR2,
225 p_exp_time_level IN VARCHAR2,
226 p_fact_code IN VARCHAR2,
227 x_return_status OUT NOCOPY VARCHAR2,
228 x_msg_count OUT NOCOPY NUMBER,
229 x_msg_data OUT NOCOPY VARCHAR2);
230
231 -- Start of comments
232 -- API name : get_ddr_ws_file_seq_nextval
233 -- Type: Private
234 -- Pre-reqs: None.
235 -- Function: to get the file id from sequence
236 -- Parameters:
237 -- IN :
238 --
239 -- OUT NOCOPY :
240 -- file ID sequence number
241 -- Version: Current version1.0
242 -- Initial version 1.0
243 -- End of comments
244 FUNCTION get_ddr_ws_file_seq_nextval(x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2) RETURN VARCHAR2;
247
248
249 -- Start of comments
250 -- API name : get_itm_hrchy_clauses
251 -- Type: Private
252 -- Pre-reqs: None.
253 -- Function: to get the item hierarchy clauses
254 -- Parameters:
255 -- IN :
256 -- p_item_dim_lvl_cd
257 -- item dimensions hiertarchy level code
258 -- p_item_lvl_val
259 -- item dimension hierarchy level code value
260 -- OUT NOCOPY :
261 --
262 -- Version: Current version1.0
263 -- Initial version 1.0
264 -- End of comments
265 PROCEDURE get_itm_hrchy_clauses(p_item_dim_lvl_cd IN VARCHAR2,
266 p_item_lvl_val IN VARCHAR2,
267 p_exp_item_level IN VARCHAR2,
268 p_fact_code IN VARCHAR2,
269 x_return_status OUT NOCOPY VARCHAR2,
270 x_msg_count OUT NOCOPY VARCHAR2,
274 x_itm_where_clus OUT NOCOPY VARCHAR2);
271 x_msg_data OUT NOCOPY VARCHAR2,
272 x_itm_ref_tbls OUT NOCOPY VARCHAR2,
273 x_itm_ref_joins OUT NOCOPY VARCHAR2,
275
276 -- Start of comments
277 -- API name : get_item_ref_join
278 -- Type: Private
279 -- Pre-reqs: None.
280 -- Function: to get the reference joiins for the item hierarchy
281 -- Parameters:
282 -- IN :
283 -- p_sys_var
284 -- system variable name
285 -- OUT NOCOPY :
286 -- '
287 -- Version: Current version1.0
288 -- Initial version 1.0
289 -- End of comments
290 PROCEDURE get_item_ref_join(p_lvl_rnk IN NUMBER,
291 x_ref_join OUT NOCOPY VARCHAR2,
292 x_return_status OUT NOCOPY VARCHAR2,
293 x_msg_count OUT NOCOPY VARCHAR2,
294 x_msg_data OUT NOCOPY VARCHAR2);
295
296 -- Start of comments
297 -- API name : get_org_hrchy_clauses
298 -- Type: Private
299 -- Pre-reqs: None.
300 -- Function: to get the organization hierarchy clauses
301 -- Parameters:
302 -- IN :
303 -- p_sys_var
304 -- system variable name
305 -- OUT NOCOPY :
306 -- '
307 -- Version: Current version1.0
308 -- Initial version 1.0
309 -- End of comments
310 PROCEDURE get_org_hrchy_clauses(p_org_dim_lvl_cd IN VARCHAR2,
311 p_org_lvl_val IN VARCHAR2,
312 p_exp_org_level IN VARCHAR2,
313 p_fact_code IN VARCHAR2,
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY VARCHAR2,
316 x_msg_data OUT NOCOPY VARCHAR2,
317 x_org_ref_tbls OUT NOCOPY VARCHAR2,
318 x_org_ref_joins OUT NOCOPY VARCHAR2,
319 x_org_where_clus OUT NOCOPY VARCHAR2);
320
321 -- Start of comments
322 -- API name : get_org_ref_join
323 -- Type: Private
324 -- Pre-reqs: None.
325 -- Function: to get the reference joiins for the organization hierarchy
326 -- Parameters:
327 -- IN :
328 -- p_sys_var
329 -- system variable name
330 -- OUT NOCOPY :
331 -- '
332 -- Version: Current version1.0
333 -- Initial version 1.0
334 PROCEDURE get_org_ref_join(p_lvl_rnk IN NUMBER,
335 x_ref_join OUT NOCOPY VARCHAR2,
336 x_return_status OUT NOCOPY VARCHAR2,
337 x_msg_count OUT NOCOPY VARCHAR2,
338 x_msg_data OUT NOCOPY VARCHAR2);
339
340 -- Start of comments
341 -- API name : get_itm_hrchy_clauses
342 -- Type: Private
343 -- Pre-reqs: None.
344 -- Function: to get the time hierarchy clauses
345 -- Parameters:
346 -- IN :
347 -- p_sys_var
348 -- system variable name
349 -- OUT NOCOPY :
350 -- '
351 -- Version: Current version1.0
352 -- Initial version 1.0
353 -- End of comments
354 PROCEDURE get_time_hrchy_clauses(p_time_dim_lvl_cd IN VARCHAR2,
355 p_time_lvl_val IN VARCHAR2,
356 p_exp_time_level IN VARCHAR2,
357 p_fact_code IN VARCHAR2,
358 p_org_cd IN VARCHAR2,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY VARCHAR2,
361 x_msg_data OUT NOCOPY VARCHAR2,
362 x_time_ref_tbls OUT NOCOPY VARCHAR2,
363 x_time_ref_joins OUT NOCOPY VARCHAR2,
364 x_time_where_clus OUT NOCOPY VARCHAR2);
365
366 -- Start of comments
367 -- API name : get_time_ref_join
368 -- Type: Private
369 -- Pre-reqs: None.
370 -- Function: to get the reference joiins for the time hierarchy
371 -- Parameters:
372 -- IN :
373 -- p_sys_var
374 -- system variable name
375 -- OUT NOCOPY :
376 -- '
377 -- Version: Current version1.0
378 -- Initial version 1.0
379 PROCEDURE get_time_ref_join(p_hrchy_name IN VARCHAR2,
380 p_lvl_rnk IN NUMBER,
381 x_ref_join OUT NOCOPY VARCHAR2,
382 x_return_status OUT NOCOPY VARCHAR2,
383 x_msg_count OUT NOCOPY VARCHAR2,
384 x_msg_data OUT NOCOPY VARCHAR2);
385
386 -- Start of comments
387 -- API name : get_itm_hrchy_clauses
388 -- Type: Private
389 -- Pre-reqs: None.
390 -- Function: to get the location hierarchy clauses
391 -- Parameters:
392 -- IN :
393 -- p_sys_var
394 -- system variable name
395 -- OUT NOCOPY :
396 -- '
397 -- Version: Current version1.0
398 -- Initial version 1.0
399 -- End of comments
400 PROCEDURE get_loc_hrchy_clauses(p_loc_dim_lvl_cd IN VARCHAR2,
401 p_loc_lvl_val IN VARCHAR2,
402 p_exp_loc_level IN VARCHAR2,
403 p_org_dim_lvl_cd IN VARCHAR2,
404 p_fact_code IN VARCHAR2,
405 x_return_status OUT NOCOPY VARCHAR2,
406 x_msg_count OUT NOCOPY VARCHAR2,
407 x_msg_data OUT NOCOPY VARCHAR2,
408 x_loc_ref_tbls OUT NOCOPY VARCHAR2,
409 x_loc_ref_joins OUT NOCOPY VARCHAR2,
410 x_loc_where_clus OUT NOCOPY VARCHAR2);
411
412 -- Start of comments
413 -- API name : get_loc_ref_join
414 -- Type: Private
415 -- Pre-reqs: None.
416 -- Function: to get the reference joiins for the location hierarchy
417 -- Parameters:
418 -- IN :
419 -- p_sys_var
420 -- system variable name
421 -- OUT NOCOPY :
422 -- '
423 -- Version: Current version1.0
424 -- Initial version 1.0
425 PROCEDURE get_loc_ref_join(p_lvl_rnk IN NUMBER,
426 x_ref_join OUT NOCOPY VARCHAR2,
427 x_return_status OUT NOCOPY VARCHAR2,
428 x_msg_count OUT NOCOPY VARCHAR2,
429 x_msg_data OUT NOCOPY VARCHAR2);
430
431 -- Start of comments
432 -- API name : get_hrchy_lvl
433 -- Type: Private
434 -- Pre-reqs: None.
435 -- Function: to get the hierarchy level for the given hierarchy code from metadata table
436 -- Parameters:
437 -- IN :
438 -- p_sys_var
439 -- system variable name
440 -- OUT NOCOPY :
441 -- '
442 -- Version: Current version1.0
443 -- Initial version 1.0
444 PROCEDURE get_hrchy_lvl(p_hrchy_lvl_name IN VARCHAR2,
445 p_hrchy_lvl_cd IN VARCHAR2,
446 x_hrchy_lvl OUT NOCOPY NUMBER,
447 x_return_status OUT NOCOPY VARCHAR2,
448 x_msg_count OUT NOCOPY VARCHAR2,
449 x_msg_data OUT NOCOPY VARCHAR2);
450 --Bug 6880404 change start
451 --Start of comments
452 -- API name : get_other_join_conditions
453 -- Type: Private
454 -- Pre-reqs: None.
455 -- Function: to get any other join conditions related
456 -- Parameters:
457 -- IN :
458 -- p_sys_var
459 -- system variable name
460 -- OUT NOCOPY :
461 -- '
462 -- Version: Current version1.0
463 -- Initial version 1.0
464 PROCEDURE get_other_join_conditions(p_fact_code IN VARCHAR2,
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_msg_count OUT NOCOPY VARCHAR2,
467 x_msg_data OUT NOCOPY VARCHAR2,
468 x_oth_join_codn OUT NOCOPY VARCHAR2);
469 --Bug 6880404 change end
470
471 PROCEDURE ddr_fact_aggr_prc(p_api_version IN NUMBER,
472 p_job_id IN NUMBER,
473 p_mfg_org_cd IN VARCHAR2,
474 p_org_cd IN VARCHAR2,
475 p_org_dim_lvl_cd IN VARCHAR2,
476 p_org_lvl_val IN VARCHAR2,
477 p_exp_org_level IN VARCHAR2,
478 p_loc_dim_lvl_cd IN VARCHAR2,
479 p_loc_lvl_val IN VARCHAR2,
480 p_exp_loc_level IN VARCHAR2,
481 p_item_dim_lvl_cd IN VARCHAR2,
482 p_item_lvl_val IN VARCHAR2,
483 p_exp_item_level IN VARCHAR2,
484 p_time_dim_lvl_cd IN VARCHAR2,
485 p_time_lvl_val IN VARCHAR2,
486 p_exp_time_level IN VARCHAR2,
487 p_fact_code IN VARCHAR2,
488 p_attribute1 IN VARCHAR2,
489 p_attribute2 IN VARCHAR2,
490 p_attribute3 IN VARCHAR2,
491 p_attribute4 IN VARCHAR2,
492 p_attribute5 IN VARCHAR2)
493 AS
494 l_query VARCHAR2(32767):=null;
495 l_return_status VARCHAR2(30):=null;
496 l_msg_count NUMBER:=null;
497 l_msg_data VARCHAR2(250):=null;
498 l_job_id NUMBER:=null;
499 BEGIN
500 l_job_id:=p_job_id;
501 --update job status to Running
502 UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;
503 --build dynamic query
504 get_dyn_query(p_api_version,
505 'A',
506 p_mfg_org_cd,
507 p_org_cd,
508 p_org_dim_lvl_cd,
509 p_org_lvl_val,
510 p_exp_org_level,
511 p_loc_dim_lvl_cd,
512 p_loc_lvl_val,
513 p_exp_loc_level,
517 p_time_dim_lvl_cd,
514 p_item_dim_lvl_cd,
515 p_item_lvl_val,
516 p_exp_item_level,
518 p_time_lvl_val,
519 p_exp_time_level,
520 p_fact_code,
521 l_return_status,
522 l_msg_count,
523 l_msg_data,
524 l_query);
525 IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
526 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
527 RETURN;
528 END IF;
529 --write data to xml file based on dynamic query generated
530 write_fact_to_xml_file(l_query,p_fact_code,l_job_id,l_return_status,l_msg_count,l_msg_data);
531 --update job status to complete/error
532 UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
533 EXCEPTION
534 WHEN NO_DATA_FOUND THEN
535 l_return_status:=ddr_webservices_constants.g_ret_sts_error;
536 l_msg_count:=1;
537 l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
538 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
539 WHEN OTHERS THEN
540 l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
541 l_msg_count:=1;
542 l_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
543 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
544 END ddr_fact_aggr_prc;
545
546 PROCEDURE ddr_fact_details_prc(p_api_version IN NUMBER,
547 p_job_id IN NUMBER,
548 p_mfg_org_cd IN VARCHAR2,
549 p_org_cd IN VARCHAR2,
550 p_org_dim_lvl_cd IN VARCHAR2,
551 p_org_lvl_val IN VARCHAR2,
552 p_loc_dim_lvl_cd IN VARCHAR2,
553 p_loc_lvl_val IN VARCHAR2,
554 p_item_dim_lvl_cd IN VARCHAR2,
555 p_item_lvl_val IN VARCHAR2,
556 p_time_dim_lvl_cd IN VARCHAR2,
557 p_time_lvl_val IN VARCHAR2,
558 p_fact_code IN VARCHAR2,
559 p_attribute1 IN VARCHAR2,
560 p_attribute2 IN VARCHAR2,
561 p_attribute3 IN VARCHAR2,
562 p_attribute4 IN VARCHAR2,
563 p_attribute5 IN VARCHAR2)
564 AS
565 l_query VARCHAR2(32767):=null;
566 l_return_status VARCHAR2(30):=null;
567 l_msg_count NUMBER:=null;
568 l_msg_data VARCHAR2(250):=null;
569 l_job_id NUMBER:=null;
570 BEGIN
571 l_job_id:=p_job_id;
572 --update job status to Running
573 UPDATE DDR_WS_JOB set status= ddr_webservices_constants.g_ret_sts_running,start_date=sysdate where job_id=p_job_id ;
574 --build dynamic query
575 get_dyn_query(p_api_version,
576 'D',
577 p_mfg_org_cd,
578 p_org_cd,
579 p_org_dim_lvl_cd,
580 p_org_lvl_val,
581 null,
582 p_loc_dim_lvl_cd,
583 p_loc_lvl_val,
584 null,
585 p_item_dim_lvl_cd,
586 p_item_lvl_val,
587 null,
588 p_time_dim_lvl_cd,
589 p_time_lvl_val,
590 null,
591 p_fact_code,
592 l_return_status,
593 l_msg_count,
594 l_msg_data,
595 l_query);
596 IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
597 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
598 RETURN;
599 END IF;
600 --write data to xml file based on dynamic query generated
601 write_fact_to_xml_file(l_query,p_fact_code,l_job_id,l_return_status,l_msg_count,l_msg_data);
602 --update job status to complete/error
603 UPDATE DDR_WS_JOB SET status=ddr_webservices_constants.g_ret_sts_success,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
604 EXCEPTION
605 WHEN NO_DATA_FOUND THEN
606 l_return_status:=ddr_webservices_constants.g_ret_sts_error;
607 l_msg_count:=1;
608 l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
609 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
610 WHEN OTHERS THEN
611 l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
612 l_msg_count:=1;
613 l_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
614 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
615 END ddr_fact_details_prc;
616
617
618 PROCEDURE get_dyn_query(p_api_version IN NUMBER,
619 p_call_type IN VARCHAR2,
620 p_mfg_org_cd IN VARCHAR2,
621 p_org_cd IN VARCHAR2,
622 p_org_dim_lvl_cd IN VARCHAR2,
623 p_org_lvl_val IN VARCHAR2,
624 p_exp_org_level IN VARCHAR2,
625 p_loc_dim_lvl_cd IN VARCHAR2,
626 p_loc_lvl_val IN VARCHAR2,
627 p_exp_loc_level IN VARCHAR2,
628 p_item_dim_lvl_cd IN VARCHAR2,
629 p_item_lvl_val IN VARCHAR2,
630 p_exp_item_level IN VARCHAR2,
631 p_time_dim_lvl_cd IN VARCHAR2,
632 p_time_lvl_val IN VARCHAR2,
633 p_exp_time_level IN VARCHAR2,
634 p_fact_code IN VARCHAR2,
635 x_return_status OUT NOCOPY VARCHAR2,
636 x_msg_count OUT NOCOPY NUMBER,
637 x_msg_data OUT NOCOPY VARCHAR2,
638 x_dyn_query OUT NOCOPY VARCHAR2)
639 IS
640 l_query VARCHAR2(32767):=null;
641 l_fact_table_name VARCHAR2(30) :=null;
642 l_fact_cols VARCHAR2(32767):=null;
643 l_org_where_clus VARCHAR2(100) :=null;
644 l_loc_where_clus VARCHAR2(100) :=null;
645 l_itm_where_clus VARCHAR2(100) :=null;
646 l_time_where_clus VARCHAR2(100) :=null;
647 l_group_col VARCHAR2(120) :=null;
648 l_query_len NUMBER :=null;
649 l_itm_ref_tbls VARCHAR2(32767):=null;
650 l_itm_ref_joins VARCHAR2(32767):=null;
651 l_org_ref_tbls VARCHAR2(32767):=null;
652 l_org_ref_joins VARCHAR2(32767):=null;
653 l_time_ref_tbls VARCHAR2(32767):=null;
654 l_time_ref_joins VARCHAR2(32767):=null;
655 l_loc_ref_tbls VARCHAR2(32767):=null;
656 l_loc_ref_joins VARCHAR2(32767):=null;
657 --Bug 6880404 change start
658 l_oth_join_codn VARCHAR2(32767):=null;
659 --Bug 6880404 change end
660 BEGIN
661 --validate the input parameters
662 validate_input_params(p_api_version,
663 p_mfg_org_cd,
664 p_org_cd,
665 p_org_dim_lvl_cd,
666 p_org_lvl_val,
667 null,
668 p_loc_dim_lvl_cd,
669 p_loc_lvl_val,
670 null,
671 p_item_dim_lvl_cd,
672 p_item_lvl_val,
673 null,
674 p_time_dim_lvl_cd,
675 p_time_lvl_val,
676 null,
677 p_fact_code,
678 x_return_status,
679 x_msg_count,
680 x_msg_data);
681 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
682 RETURN;
683 END IF;
684 -- get fact table name
685 get_fact_table(p_fact_code,x_return_status,x_msg_count,x_msg_data,l_fact_table_name);
686 IF p_call_type='A' THEN
687 -- get aggregated select fact column names
688 get_aggr_fact_colms(p_fact_code,x_return_status,x_msg_count,x_msg_data,l_fact_cols);
689 ELSIF p_call_type='D' THEN
690 -- get detailed select fact column names
691 get_detail_fact_colms(p_fact_code,x_return_status,x_msg_count,x_msg_data,l_fact_cols);
692 END IF;
693 --get the reference tables ,reference table joins and where clause for the item hierarchy
694 IF p_item_dim_lvl_cd IS NOT NULL OR p_exp_item_level IS NOT NULL THEN
695 get_itm_hrchy_clauses(p_item_dim_lvl_cd,
696 p_item_lvl_val,
697 p_exp_item_level,
698 p_fact_code,
699 x_return_status,
700 x_msg_count,
701 x_msg_data,
702 l_itm_ref_tbls,
703 l_itm_ref_joins,
704 l_itm_where_clus);
705 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
706 RETURN;
707 END IF;
708 END IF;
709 --get the reference tables ,reference table joins and where clause join for the organization hierarchy level
710 IF p_org_dim_lvl_cd IS NOT NULL OR p_exp_org_level IS NOT NULL THEN
711 get_org_hrchy_clauses(p_org_dim_lvl_cd,
712 p_org_lvl_val,
713 p_exp_org_level,
714 p_fact_code,
715 x_return_status,
716 x_msg_count,
717 x_msg_data,
718 l_org_ref_tbls,
719 l_org_ref_joins,
720 l_org_where_clus);
721 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
722 RETURN;
723 END IF;
724 END IF;
725 --get the reference tables ,reference table joins and where clause join for the time hierarchy level
726 IF p_time_dim_lvl_cd IS NOT NULL OR p_exp_time_level IS NOT NULL THEN
727 get_time_hrchy_clauses(p_time_dim_lvl_cd,
728 p_time_lvl_val,
729 p_exp_time_level,
730 p_fact_code,
731 p_org_cd,
732 x_return_status,
733 x_msg_count,
734 x_msg_data,
735 l_time_ref_tbls,
736 l_time_ref_joins,
737 l_time_where_clus);
738 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
739 RETURN;
740 END IF;
741 END IF;
742 --get the reference tables ,reference table joins and where clause join for the location hierarchy level
743 IF p_loc_dim_lvl_cd IS NOT NULL OR p_exp_loc_level IS NOT NULL THEN
744 get_loc_hrchy_clauses(p_loc_dim_lvl_cd,
745 p_loc_lvl_val,
746 p_exp_loc_level,
747 p_org_dim_lvl_cd,
748 p_fact_code,
749 x_return_status,
750 x_msg_count,
751 x_msg_data,
752 l_loc_ref_tbls,
753 l_loc_ref_joins,
754 l_loc_where_clus);
755 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
756 RETURN;
757 END IF;
758 END IF;
759 --Bug 6880404 change start
760 --get other join conditions
761 get_other_join_conditions(p_fact_code,
762 x_return_status,
763 x_msg_count,
764 x_msg_data,
765 l_oth_join_codn);
766 --Bug 6880404 change end
767 -- get group by columns
768 IF p_call_type='A' THEN
769 get_aggr_group_colms(p_exp_org_level,
770 p_exp_loc_level,
771 p_exp_item_level,
772 p_exp_time_level,
773 x_return_status,
774 x_msg_count,
775 x_msg_data,
776 l_group_col);
777 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
778 RETURN;
779 END IF;
780 END IF;
781
782 --dynamically building query
783 l_query := 'SELECT ' || l_fact_cols;
784 IF p_call_type='A' AND l_group_col IS NOT NULL THEN
785 l_query := l_query || ','|| l_group_col;
786 END IF;
787 --appending fact table to query string
788 l_query := l_query || ' FROM ' || l_fact_table_name || ' x';
789 --include the various reference tables in from clause
790 --Item Hierarchy reference tables
791 l_query := l_query || l_itm_ref_tbls;
792 --Organization Hierarchy reference tables
793 l_query := l_query || l_org_ref_tbls;
794 --Time Hierarchy reference tables
795 l_query := l_query || l_time_ref_tbls;
796 --location Hierarchy reference tables
797 l_query := l_query || l_loc_ref_tbls;
798 --appending organization code to query string
799 l_query := l_query || ' WHERE '|| ' x.MFG_ORG_CD ='''|| p_mfg_org_cd||'''';
800 l_query := l_query || ' AND '|| ' x.RTL_ORG_CD ='''|| p_org_cd||'''';
801 --Bug 6880404 change start
802 l_query := l_query || l_oth_join_codn;
803 --Bug 6880404 change end
804 -- ITEM Dimension Hierarchy joins
805 l_query := l_query || l_itm_ref_joins;
806 IF l_itm_where_clus IS NOT NULL THEN
807 l_query := l_query || ' AND ' || l_itm_where_clus || '';
808 END IF;
809 -- Organization Dimension Hierarchy joins
810 l_query := l_query || l_org_ref_joins;
811 IF l_org_where_clus IS NOT NULL THEN
812 l_query := l_query || ' AND '|| l_org_where_clus || '';
813 END IF;
814 -- Time Dimension Hierarchy joins
815 l_query := l_query || l_time_ref_joins;
816 IF l_time_where_clus IS NOT NULL THEN
817 l_query := l_query || ' AND '|| l_time_where_clus || '';
818 END IF;
819 -- Loaction Dimension Hierarchy joins
820 l_query := l_query ||l_loc_ref_joins;
821 IF l_loc_where_clus IS NOT NULL THEN
822 l_query := l_query || ' AND '|| l_loc_where_clus || '';
823 END IF;
824 IF p_call_type='A' THEN
825 --group by clause join
826 IF l_group_col IS NOT NULL THEN
827 l_query := l_query || ' GROUP BY '|| l_group_col;
828 END IF;
829 END IF;
830 SELECT LENGTH(l_query) into l_query_len from dual;
831 -- DBMS_OUTPUT.PUT_LINE('l_query_len='||l_query_len);
832 -- DBMS_OUTPUT.PUT_LINE('l_query='||l_query);
833 x_dyn_query:= l_query;
834 EXCEPTION
835 WHEN NO_DATA_FOUND THEN
836 x_return_status := ddr_webservices_constants.g_ret_sts_error;
837 x_msg_count := 1;
838 x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
839 WHEN OTHERS THEN
840 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
841 x_msg_count:=1;
842 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
843 END get_dyn_query;
844
845 PROCEDURE get_fact_table(p_fact_code IN VARCHAR2,
846 x_return_status OUT NOCOPY VARCHAR2,
847 x_msg_count OUT NOCOPY NUMBER,
848 x_msg_data OUT NOCOPY VARCHAR2,
849 x_fact_name OUT NOCOPY VARCHAR2)
850 IS
851 BEGIN
852 --case condition to determine name of the fact table
853 CASE p_fact_code
854 -- for MARKET ITEM SALES DAY
855 WHEN ddr_webservices_constants.g_misd_cd THEN
859 x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
856 x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
857 -- for PROMOTION PLAN
858 WHEN ddr_webservices_constants.g_pp_cd THEN
860 -- for RETAIL INVENTORY ITEM DAY
861 WHEN ddr_webservices_constants.g_riid_cd THEN
862 x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
863 -- for RETAIL SALE RETURN ITEM DAY fact
864 WHEN ddr_webservices_constants.g_rsrid_cd THEN
865 x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
866 -- for RETAILER ORDER ITEM DAY
867 WHEN ddr_webservices_constants.g_roid_cd THEN
868 x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
869 -- for RETAILER SHIP ITEM DAY
870 WHEN ddr_webservices_constants.g_rsid_cd THEN
871 x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
872 -- for SALE FORECAST ITEM BY DAY
873 WHEN ddr_webservices_constants.g_sfid_cd THEN
874 x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
875 END CASE;
876 EXCEPTION
877 WHEN OTHERS THEN
878 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
879 x_msg_count:=1;
880 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
881 END get_fact_table;
882
883 PROCEDURE get_aggr_fact_colms(p_fact_code IN VARCHAR2,
884 x_return_status OUT NOCOPY VARCHAR2,
885 x_msg_count OUT NOCOPY NUMBER,
886 x_msg_data OUT NOCOPY VARCHAR2,
887 x_fact_cols OUT NOCOPY VARCHAR2)
888 IS
889 BEGIN
890 --case condition to identify aggregate column names from fact table
891 CASE p_fact_code
892 -- for MARKET ITEM SALES DAY
893 WHEN ddr_webservices_constants.g_misd_cd THEN
894 x_fact_cols:='sum(x.AVG_MMACV_SLS_RATE) AVG_MMACV_SLS_RATE,'
895 ||'sum(x.AVG_STORE_SELL_ITEM_QTY) AVG_STORE_SELL_ITEM_QTY,'
896 ||'sum(x.AVG_ACV_WGT_DSTRBTN_PCT) AVG_ACV_WGT_DSTRBTN_PCT,'
897 ||'sum(x.AVG_WGT_PRICE_RDCTN_PCT) AVG_WGT_PRICE_RDCTN_PCT,'
898 ||'sum(x.SLS_QTY) SLS_QTY,'
899 ||'sum(x.SLS_AMT) SLS_AMT,'
900 ||'sum(x.NRML_QTY) NRML_QTY,'
901 ||'sum(x.NRML_AMT) NRML_AMT,'
902 ||'sum(x.SLS_PRICE_CUT_QTY) SLS_PRICE_CUT_QTY,'
903 ||'sum(x.SLS_PRICE_CUT_AMT) SLS_PRICE_CUT_AMT,'
904 ||'sum(x.MAIN_AD_QTY) MAIN_AD_QTY,'
905 ||'sum(x.MAIN_AD_AMT) MAIN_AD_AMT';
906 --for PROMOTION PLAN
907 WHEN ddr_webservices_constants.g_pp_cd THEN
908 x_fact_cols:='sum(x.PRMTN_PRICE_AMT) PRMTN_PRICE_AMT';
909 -- for RETAIL INVENTORY ITEM DAY
910 WHEN ddr_webservices_constants.g_riid_cd THEN
911 x_fact_cols:= 'sum(x.ON_HAND_QTY) ON_HAND_QTY,'
912 ||'sum(x.RECVD_QTY) RECVD_QTY,'
913 ||'sum(x.IN_TRANSIT_QTY) IN_TRANSIT_QTY,'
914 ||'sum(x.BCK_ORDR_QTY) BCK_ORDR_QTY,'
915 ||'sum(x.QLTY_HOLD_QTY) QLTY_HOLD_QTY,'
916 ||'sum(x.ON_HAND_NET_COST_AMT) ON_HAND_NET_COST_AMT,'
917 ||'sum(x.RECVD_NET_COST_AMT) RECVD_NET_COST_AMT,'
918 ||'sum(x.IN_TRANSIT_NET_COST_AMT) IN_TRANSIT_NET_COST_AMT,'
919 ||'sum(x.BCKORDR_NET_COST_AMT) BCKORDR_NET_COST_AMT,'
920 ||'sum(x.QLTY_HOLD_NET_COST_AMT) QLTY_HOLD_NET_COST_AMT,'
921 ||'sum(x.ON_HAND_RTL_AMT) ON_HAND_RTL_AMT,'
922 ||'sum(x.RECVD_RTL_AMT) RECVD_RTL_AMT,'
923 ||'sum(x.IN_TRANSIT_RTL_AMT) IN_TRANSIT_RTL_AMT,'
924 ||'sum(x.BCKORDR_RTL_AMT) BCKORDR_RTL_AMT,'
925 ||'sum(x.QLTY_HOLD_RTL_AMT) QLTY_HOLD_RTL_AMT';
926 -- for RETAIL SALE RETURN ITEM DAY fact
927 WHEN ddr_webservices_constants.g_rsrid_cd THEN
928 x_fact_cols:='sum(x.SLS_QTY) SLS_QTY,'
929 ||'sum(x.SLS_AMT) SLS_AMT,'
930 ||'sum(x.SLS_COST_AMT) SLS_COST_AMT,'
931 ||'sum(x.RTRN_QTY) RTRN_QTY,'
932 ||'sum(x.RTRN_AMT) RTRN_AMT,'
933 ||'sum(x.RTRN_COST_AMT) RTRN_COST_AMT';
934 -- for RETAILER ORDER ITEM DAY
935 WHEN ddr_webservices_constants.g_roid_cd THEN
936 x_fact_cols:='sum(x.ORDR_QTY) ORDR_QTY,'
937 ||'sum(x.ORDR_AMT) ORDR_AMT';
938 -- for RETAILER SHIP ITEM DAY
939 WHEN ddr_webservices_constants.g_rsid_cd THEN
940 x_fact_cols:= 'sum(x.SHIP_QTY) SHIP_QTY,'
941 ||'sum(x.SHIP_AMT) SHIP_AMT';
942 -- for SALE FORECAST ITEM BY DAY
943 WHEN ddr_webservices_constants.g_sfid_cd THEN
944 x_fact_cols:= 'sum(x.FRCST_SLS_QTY) FRCST_SLS_QTY,'
945 ||'sum(x.FRCST_SLS_AMT) FRCST_SLS_AMT';
946 END CASE;
947 EXCEPTION
948 WHEN OTHERS THEN
949 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
950 x_msg_count:=1;
951 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
952 END get_aggr_fact_colms;
953
954 PROCEDURE get_detail_fact_colms(p_fact_code IN VARCHAR2,
955 x_return_status OUT NOCOPY VARCHAR2,
956 x_msg_count OUT NOCOPY NUMBER,
957 x_msg_data OUT NOCOPY VARCHAR2,
958 x_fact_cols OUT NOCOPY VARCHAR2)
959 IS
960 BEGIN
961 --case condition to identify aggregate column names from fact table
962 CASE p_fact_code
963 -- for MARKET ITEM SALES DAY
964 WHEN ddr_webservices_constants.g_misd_cd THEN
965 x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
966 ||'x.RTL_ORG_CD RTL_ORG_CD,'
967 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
968 ||'x.MKT_AREA_ID MKT_AREA_ID,'
969 ||'x.MKT_AREA_CD MKT_AREA_CD,'
970 ||'x.DAY_CD DAY_CD,'
971 ||'x.BSNS_UNIT_CD BSNS_UNIT_CD,'
972 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
973 ||'x.MKT_ITEM_ID MKT_ITEM_ID,'
974 ||'x.UOM_CD UOM_CD,'
975 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
976 ||'x.UOM_CD_ALT UOM_CD_ALT,'
977 ||'x.CRNCY_CD CRNCY_CD,'
978 ||'x.REC_CURR_DT REC_CURR_DT,'
979 ||'x.AVG_MMACV_SLS_RATE AVG_MMACV_SLS_RATE,'
980 ||'x.AVG_STORE_SELL_ITEM_QTY AVG_STORE_SELL_ITEM_QTY,'
984 ||'x.AVG_WGT_PRICE_RDCTN_PCT AVG_WGT_PRICE_RDCTN_PCT,'
981 ||'x.AVG_STORE_SELL_ITEM_QTY_PRMRY AVG_STORE_SELL_ITEM_QTY_PRMRY,'
982 ||'x.AVG_STORE_SELL_ITEM_QTY_ALT AVG_STORE_SELL_ITEM_QTY_ALT,'
983 ||'x.AVG_ACV_WGT_DSTRBTN_PCT AVG_ACV_WGT_DSTRBTN_PCT,'
985 ||'x.SLS_QTY SLS_QTY,'
986 ||'x.SLS_QTY_PRMRY SLS_QTY_PRMRY,'
987 ||'x.SLS_QTY_ALT SLS_QTY_ALT,'
988 ||'x.SLS_AMT SLS_AMT,'
989 ||'x.SLS_AMT_LCL SLS_AMT_LCL,'
990 ||'x.SLS_AMT_RPT SLS_AMT_RPT,'
991 ||'x.NRML_QTY NRML_QTY,'
992 ||'x.NRML_QTY_PRMRY NRML_QTY_PRMRY,'
993 ||'x.NRML_QTY_ALT NRML_QTY_ALT,'
994 ||'x.NRML_AMT NRML_AMT,'
995 ||'x.NRML_AMT_LCL NRML_AMT_LCL,'
996 ||'x.NRML_AMT_RPT NRML_AMT_RPT,'
997 ||'x.SLS_PRICE_CUT_QTY SLS_PRICE_CUT_QTY,'
998 ||'x.SLS_PRICE_CUT_QTY_PRMRY SLS_PRICE_CUT_QTY_PRMRY,'
999 ||'x.SLS_PRICE_CUT_QTY_ALT SLS_PRICE_CUT_QTY_ALT,'
1000 ||'x.SLS_PRICE_CUT_AMT SLS_PRICE_CUT_AMT,'
1001 ||'x.SLS_PRICE_CUT_AMT_LCL SLS_PRICE_CUT_AMT_LCL,'
1002 ||'x.SLS_PRICE_CUT_AMT_RPT SLS_PRICE_CUT_AMT_RPT,'
1003 ||'x.MAIN_AD_QTY MAIN_AD_QTY,'
1004 ||'x.MAIN_AD_QTY_PRMRY MAIN_AD_QTY_PRMRY,'
1005 ||'x.MAIN_AD_QTY_ALT MAIN_AD_QTY_ALT,'
1006 ||'x.MAIN_AD_AMT MAIN_AD_AMT,'
1007 ||'x.MAIN_AD_AMT_LCL MAIN_AD_AMT_LCL,'
1008 ||'x.MAIN_AD_AMT_RPT MAIN_AD_AMT_RPT';
1009 --for PROMOTION PLAN
1010 WHEN ddr_webservices_constants.g_pp_cd THEN
1011 x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1012 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1013 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1014 ||'x.RTL_BSNS_UNIT_CD RTL_BSNS_UNIT_CD,'
1015 ||'x.PRMTN_TYP PRMTN_TYP,'
1016 ||'x.PRMTN_FROM_DT PRMTN_FROM_DT,'
1017 ||'x.PRMTN_TO_DT PRMTN_TO_DT,'
1018 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1019 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1020 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1021 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1022 ||'x.CRNCY_CD CRNCY_CD,'
1023 ||'x.PRMTN_PRICE_AMT PRMTN_PRICE_AMT,'
1024 ||'x.PRMTN_PRICE_AMT_LCL PRMTN_PRICE_AMT_LCL,'
1025 ||'x.PRMTN_PRICE_AMT_RPT PRMTN_PRICE_AMT_RPT';
1026 -- for RETAIL INVENTORY ITEM DAY
1027 WHEN ddr_webservices_constants.g_riid_cd THEN
1028 x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1029 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1030 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1031 ||'x.RTL_BSNS_UNIT_CD RTL_BSNS_UNIT_CD,'
1032 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1033 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1034 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1035 ||'x.DAY_CD DAY_CD,'
1036 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1037 ||'x.INV_LOC_TYP_CD INV_LOC_TYP_CD,'
1038 ||'x.INV_LOC_ID INV_LOC_ID,'
1039 ||'x.INV_LOC_CD INV_LOC_CD,'
1040 ||'x.UOM_CD UOM_CD,'
1041 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
1042 ||'x.UOM_CD_ALT UOM_CD_ALT,'
1043 ||'x.CRNCY_CD CRNCY_CD,'
1044 ||'x.ON_HAND_QTY ON_HAND_QTY,'
1045 ||'x.ON_HAND_QTY_PRMRY ON_HAND_QTY_PRMRY,'
1046 ||'x.ON_HAND_QTY_ALT ON_HAND_QTY_ALT,'
1047 ||'x.RECVD_QTY RECVD_QTY,'
1048 ||'x.RECVD_QTY_PRMRY RECVD_QTY_PRMRY,'
1049 ||'x.RECVD_QTY_ALT RECVD_QTY_ALT,'
1050 ||'x.IN_TRANSIT_QTY IN_TRANSIT_QTY,'
1051 ||'x.IN_TRANSIT_QTY_PRMRY IN_TRANSIT_QTY_PRMRY,'
1052 ||'x.IN_TRANSIT_QTY_ALT IN_TRANSIT_QTY_ALT,'
1053 ||'x.BCK_ORDR_QTY BCK_ORDR_QTY,'
1054 ||'x.BCK_ORDR_QTY_PRMRY BCK_ORDR_QTY_PRMRY,'
1055 ||'x.BCK_ORDR_QTY_ALT BCK_ORDR_QTY_ALT,'
1056 ||'x.QLTY_HOLD_QTY QLTY_HOLD_QTY,'
1057 ||'x.QLTY_HOLD_QTY_PRMRY QLTY_HOLD_QTY_PRMRY,'
1058 ||'x.QLTY_HOLD_QTY_ALT QLTY_HOLD_QTY_ALT,'
1059 ||'x.ON_HAND_NET_COST_AMT ON_HAND_NET_COST_AMT,'
1060 ||'x.ON_HAND_NET_COST_AMT_LCL ON_HAND_NET_COST_AMT_LCL,'
1061 ||'x.ON_HAND_NET_COST_AMT_RPT ON_HAND_NET_COST_AMT_RPT,'
1062 ||'x.RECVD_NET_COST_AMT RECVD_NET_COST_AMT,'
1063 ||'x.RECVD_NET_COST_AMT_LCL RECVD_NET_COST_AMT_LCL,'
1064 ||'x.RECVD_NET_COST_AMT_RPT RECVD_NET_COST_AMT_RPT,'
1065 ||'x.IN_TRANSIT_NET_COST_AMT IN_TRANSIT_NET_COST_AMT,'
1066 ||'x.IN_TRANSIT_NET_COST_AMT_LCL IN_TRANSIT_NET_COST_AMT_LCL,'
1067 ||'x.IN_TRANSIT_NET_COST_AMT_RPT IN_TRANSIT_NET_COST_AMT_RPT,'
1068 ||'x.BCKORDR_NET_COST_AMT BCKORDR_NET_COST_AMT,'
1069 ||'x.BCKORDR_NET_COST_AMT_LCL BCKORDR_NET_COST_AMT_LCL,'
1070 ||'x.BCKORDR_NET_COST_AMT_RPT BCKORDR_NET_COST_AMT_RPT,'
1071 ||'x.QLTY_HOLD_NET_COST_AMT QLTY_HOLD_NET_COST_AMT,'
1072 ||'x.QLTY_HOLD_NET_COST_AMT_LCL QLTY_HOLD_NET_COST_AMT_LCL,'
1073 ||'x.QLTY_HOLD_NET_COST_AMT_RPT QLTY_HOLD_NET_COST_AMT_RPT,'
1074 ||'x.ON_HAND_RTL_AMT ON_HAND_RTL_AMT,'
1075 ||'x.ON_HAND_RTL_AMT_LCL ON_HAND_RTL_AMT_LCL,'
1076 ||'x.ON_HAND_RTL_AMT_RPT ON_HAND_RTL_AMT_RPT,'
1077 ||'x.RECVD_RTL_AMT RECVD_RTL_AMT,'
1078 ||'x.RECVD_RTL_AMT_LCL RECVD_RTL_AMT_LCL,'
1079 ||'x.RECVD_RTL_AMT_RPT RECVD_RTL_AMT_RPT,'
1080 ||'x.IN_TRANSIT_RTL_AMT IN_TRANSIT_RTL_AMT,'
1081 ||'x.IN_TRANSIT_RTL_AMT_LCL IN_TRANSIT_RTL_AMT_LCL,'
1082 ||'x.IN_TRANSIT_RTL_AMT_RPT IN_TRANSIT_RTL_AMT_RPT,'
1083 ||'x.BCKORDR_RTL_AMT BCKORDR_RTL_AMT,'
1084 ||'x.BCKORDR_RTL_AMT_LCL BCKORDR_RTL_AMT_LCL,'
1085 ||'x.BCKORDR_RTL_AMT_RPT BCKORDR_RTL_AMT_RPT,'
1086 ||'x.QLTY_HOLD_RTL_AMT QLTY_HOLD_RTL_AMT,'
1087 ||'x.QLTY_HOLD_RTL_AMT_LCL QLTY_HOLD_RTL_AMT_LCL,'
1088 ||'x.QLTY_HOLD_RTL_AMT_RPT QLTY_HOLD_RTL_AMT_RPT';
1089 -- for RETAIL SALE RETURN ITEM DAY fact
1090 WHEN ddr_webservices_constants.g_rsrid_cd THEN
1091 x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1092 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1093 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1094 ||'x.BSNS_UNIT_CD BSNS_UNIT_CD,'
1095 ||'x.DAY_CD DAY_CD,'
1096 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1097 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1098 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1099 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1100 ||'x.UOM_CD UOM_CD,'
1101 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
1102 ||'x.UOM_CD_ALT UOM_CD_ALT,'
1103 ||'x.CRNCY_CD CRNCY_CD,'
1104 ||'x.SLS_QTY SLS_QTY,'
1105 ||'x.SLS_QTY_PRMRY SLS_QTY_PRMRY,'
1109 ||'x.SLS_AMT_RPT SLS_AMT_RPT,'
1106 ||'x.SLS_QTY_ALT SLS_QTY_ALT,'
1107 ||'x.SLS_AMT SLS_AMT,'
1108 ||'x.SLS_AMT_LCL SLS_AMT_LCL,'
1110 ||'x.SLS_COST_AMT SLS_COST_AMT,'
1111 ||'x.SLS_COST_AMT_LCL SLS_COST_AMT_LCL,'
1112 ||'x.SLS_COST_AMT_RPT SLS_COST_AMT_RPT,'
1113 ||'x.RTRN_QTY RTRN_QTY,'
1114 ||'x.RTRN_QTY_PRMRY RTRN_QTY_PRMRY,'
1115 ||'x.RTRN_QTY_ALT RTRN_QTY_ALT,'
1116 ||'x.RTRN_AMT RTRN_AMT,'
1117 ||'x.RTRN_AMT_LCL RTRN_AMT_LCL,'
1118 ||'x.RTRN_AMT_RPT RTRN_AMT_RPT,'
1119 ||'x.RTRN_COST_AMT RTRN_COST_AMT,'
1120 ||'x.RTRN_COST_AMT_LCL RTRN_COST_AMT_LCL,'
1121 ||'x.RTRN_COST_AMT_RPT RTRN_COST_AMT_RPT';
1122 -- for RETAILER ORDER ITEM DAY
1123 WHEN ddr_webservices_constants.g_roid_cd THEN
1124 x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1125 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1126 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1127 ||'x.RTL_BSNS_UNIT_CD RTL_BSNS_UNIT_CD,'
1128 ||'x.DAY_CD DAY_CD,'
1129 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1130 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1131 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1132 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1133 ||'x.UOM_CD UOM_CD,'
1134 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
1135 ||'x.UOM_CD_ALT UOM_CD_ALT,'
1136 ||'x.CRNCY_CD CRNCY_CD,'
1137 ||'x.ORDR_QTY ORDR_QTY,'
1138 ||'x.ORDR_QTY_PRMRY ORDR_QTY_PRMRY,'
1139 ||'x.ORDR_QTY_ALT ORDR_QTY_ALT,'
1140 ||'x.ORDR_AMT ORDR_AMT,'
1141 ||'x.ORDR_AMT_LCL ORDR_AMT_LCL,'
1142 ||'x.ORDR_AMT_RPT ORDR_AMT_RPT';
1143 -- for RETAILER SHIP ITEM DAY
1144 WHEN ddr_webservices_constants.g_rsid_cd THEN
1145 x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1146 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1147 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1148 ||'x.RTL_BSNS_UNIT_CD RTL_BSNS_UNIT_CD,'
1149 ||'x.DAY_CD DAY_CD,'
1150 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1151 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1152 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1153 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1154 ||'x.UOM_CD UOM_CD,'
1155 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
1156 ||'x.UOM_CD_ALT UOM_CD_ALT,'
1157 ||'x.CRNCY_CD CRNCY_CD,'
1158 ||'x.SHIP_QTY SHIP_QTY,'
1159 ||'x.SHIP_QTY_PRMRY SHIP_QTY_PRMRY,'
1160 ||'x.SHIP_QTY_ALT SHIP_QTY_ALT,'
1161 ||'x.SHIP_AMT SHIP_AMT,'
1162 ||'x.SHIP_AMT_LCL SHIP_AMT_LCL,'
1163 ||'x.SHIP_AMT_RPT SHIP_AMT_RPT';
1164 -- for SALE FORECAST ITEM BY DAY
1165 WHEN ddr_webservices_constants.g_sfid_cd THEN
1166 x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1167 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1168 ||'x.FRCST_NBR FRCST_NBR,'
1169 ||'x.FRCST_TYP FRCST_TYP,'
1170 ||'x.FRCST_VRSN FRCST_VRSN,'
1171 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1172 ||'x.BSNS_UNIT_CD BSNS_UNIT_CD,'
1173 ||'x.DAY_CD DAY_CD,'
1174 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1175 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1176 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1177 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1178 ||'x.FRCST_SLS_UOM_CD FRCST_SLS_UOM_CD,'
1179 ||'x.FRCST_SLS_UOM_CD_PRMRY FRCST_SLS_UOM_CD_PRMRY,'
1180 ||'x.FRCST_SLS_UOM_CD_ALT FRCST_SLS_UOM_CD_ALT,'
1181 ||'x.CRNCY_CD CRNCY_CD,'
1182 ||'x.FRCST_SLS_QTY FRCST_SLS_QTY,'
1183 ||'x.FRCST_SLS_QTY_PRMRY FRCST_SLS_QTY_PRMRY,'
1184 ||'x.FRCST_SLS_QTY_ALT FRCST_SLS_QTY_ALT,'
1185 ||'x.FRCST_SLS_AMT FRCST_SLS_AMT,'
1186 ||'x.FRCST_SLS_AMT_LCL FRCST_SLS_AMT_LCL,'
1187 ||'x.FRCST_SLS_AMT_RPT FRCST_SLS_AMT_RPT';
1188 END CASE;
1189 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1190 EXCEPTION
1191 WHEN OTHERS THEN
1192 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1193 x_msg_count:=1;
1194 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1195 END get_detail_fact_colms;
1196
1197 PROCEDURE get_aggr_group_colms(p_exp_org_level IN VARCHAR2,
1198 p_exp_loc_level IN VARCHAR2,
1199 p_exp_item_level IN VARCHAR2,
1200 p_exp_time_level IN VARCHAR2,
1201 x_return_status OUT NOCOPY VARCHAR2,
1202 x_msg_count OUT NOCOPY NUMBER,
1203 x_msg_data OUT NOCOPY VARCHAR2,
1204 x_group_col OUT NOCOPY VARCHAR2)
1205 IS
1206 l_group_col VARCHAR2(500):=null;
1207 l_ref_join VARCHAR2(100):=null;
1208 l_lvl_rnk NUMBER:=null;
1209 l_hrchy_lvl_name VARCHAR2(50):=null;
1210 BEGIN
1211 --get aggregate columns for the Organization hierarchy based on given hierarchy level code
1212 IF p_exp_org_level IS NOT NULL THEN
1213 get_hrchy_lvl('ORGANIZATION',p_exp_org_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1214 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1215 RETURN;
1216 END IF;
1217 get_org_ref_join(l_lvl_rnk,l_group_col,x_return_status, x_msg_count,x_msg_data);
1218 END IF;
1219 --get aggregate columns for the location hierarchy based on given hierarchy level code
1220 IF p_exp_loc_level IS NOT NULL THEN
1221 get_hrchy_lvl('LOCATION',p_exp_loc_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1222 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1223 RETURN;
1224 END IF;
1225 get_loc_ref_join(l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1226 IF l_group_col IS NOT NUll THEN
1227 l_group_col :=l_group_col ||','||l_ref_join;
1228 ELSIF l_group_col IS NUll THEN
1229 l_group_col :=l_ref_join;
1230 END IF;
1231 END IF;
1232 --get aggregate columns for the Item hierarchy based on given hierarchy level code
1233 IF p_exp_item_level IS NOT NULL THEN
1237 END IF;
1234 get_hrchy_lvl('ITEM',p_exp_item_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1235 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1236 RETURN;
1238 get_item_ref_join(l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1239 IF l_group_col IS NOT NUll THEN
1240 l_group_col :=l_group_col ||','||l_ref_join;
1241 ELSIF l_group_col IS NUll THEN
1242 l_group_col :=l_ref_join;
1243 END IF;
1244 END IF;
1245 --get aggregate columns for the time hierarchy based on given hierarchy level code
1246 IF p_exp_time_level IS NOT NULL THEN
1247 SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE hrchy_lvl_cd=p_exp_time_level;
1248 get_hrchy_lvl(l_hrchy_lvl_name,p_exp_time_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1249 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1250 RETURN;
1251 END IF;
1252 get_time_ref_join(l_hrchy_lvl_name,l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1253 IF l_group_col IS NOT NUll THEN
1254 l_group_col :=l_group_col ||','||l_ref_join;
1255 ELSIF l_group_col IS NUll THEN
1256 l_group_col :=l_ref_join;
1257 END IF;
1258 END IF;
1259 x_group_col:= l_group_col;
1260 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1261 EXCEPTION
1262 WHEN NO_DATA_FOUND THEN
1263 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1264 x_msg_count:=1;
1265 x_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
1266 WHEN OTHERS THEN
1267 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1268 x_msg_count:=1;
1269 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1270 END get_aggr_group_colms;
1271
1272 PROCEDURE write_fact_to_xml_file(p_query IN VARCHAR2,
1273 p_fact_code IN VARCHAR2,
1274 p_job_id IN NUMBER,
1275 x_return_status OUT NOCOPY VARCHAR2,
1276 x_msg_count OUT NOCOPY NUMBER,
1277 x_msg_data OUT NOCOPY VARCHAR2)
1278 IS
1279 l_cur NUMBER:=null;
1280 l_dtbl DBMS_SQL.desc_tab;
1281 l_cnt NUMBER;
1282 l_status NUMBER;
1283 l_val VARCHAR2(200);
1284 l_col_name VARCHAR2(200);
1285 l_xml_file UTL_FILE.file_type;
1286 l_fetch_ctn NUMBER:=0;
1287 l_fetch_rows NUMBER:=0;
1288 l_file_name VARCHAR2(30):=null;
1289 l_total_row_count NUMBER:=0;
1290 l_dir_name VARCHAR2(30):=null;
1291 l_pagination_row_count NUMBER:=null;
1292 l_max_row_count NUMBER:=null;
1293 l_fact VARCHAR2(32767):=null;
1294 l_file_id VARCHAR2(100):= NULL;
1295 l_max_rows EXCEPTION;
1296 BEGIN
1297 --get logical directory name from system variable table
1298 ddr_webservices_pub.get_sys_var_val('OUTPUT_DIR_PATH',x_return_status, x_msg_count,x_msg_data,l_dir_name);
1299 --get pagination row count from system variable table
1300 ddr_webservices_pub.get_sys_var_val('MAX_REC_PER_FILE',x_return_status, x_msg_count,x_msg_data,l_pagination_row_count);
1301 --get threashold value for the maximum records for which files can be created
1302 ddr_webservices_pub.get_sys_var_val('MAX_OUT NOCOPY PUT_RECORDS',x_return_status, x_msg_count,x_msg_data,l_max_row_count);
1303 -- DBMS_OUTPUT.PUT_LINE('l_max_row_count='||l_max_row_count);
1304 l_pagination_row_count:=to_number(l_pagination_row_count);
1305 --get file id
1306 l_file_id :=get_ddr_ws_file_seq_nextval(x_return_status,x_msg_count,x_msg_data);
1307 --construct file name
1308 l_file_name := p_fact_code || '_'|| l_file_id || '.xml';
1309 --open file in write mode
1313 --open cursor
1310 l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);
1311 --write xml header data in the xml file
1312 UTL_FILE.put_line(l_xml_file, '<?xml version="1.0" encoding="UTF-8"?>');
1314 l_cur := dbms_sql.open_cursor;
1315 dbms_sql.parse(l_cur,p_query,dbms_sql.native);
1316 l_status := dbms_sql.execute(l_cur);
1317 --to column defination from the cursor
1318 dbms_sql.describe_columns(l_cur,l_cnt,l_dtbl);
1319 FOR i in 1..l_cnt LOOP
1320 dbms_sql.define_column(l_cur,i,l_val,30);
1321 END LOOP;
1322 UTL_FILE.put_line(l_xml_file, '<FACT_DATA>');
1323 l_fetch_rows:=dbms_sql.fetch_rows(l_cur);
1324 WHILE ( l_fetch_rows > 0 ) LOOP
1325 l_fetch_ctn:=l_fetch_ctn+l_fetch_rows;
1326 UTL_FILE.put_line(l_xml_file, '<FACT>');
1327 l_fact:=null;
1328 --write individual row in the xml file
1329 FOR i in 1..l_cnt loop
1330 l_col_name:= l_dtbl(i).col_name;
1331 dbms_sql.column_value(l_cur,i,l_val);
1332 -- l_fact:=l_fact||'<'||l_col_name||'>'||l_val||'</'||l_col_name||'>'||chr(10);
1333 -- Use of chr function not allowed by GSCC. The new line character is introduced using line edit
1334 l_fact:=l_fact||'<'||l_col_name||'>'||l_val||'</'||l_col_name||'>'||'
1335 '||NULL;
1336 END LOOP;
1337 -- UTL_FILE.put_line(l_xml_file, l_fact||chr(10)||'</FACT>');
1338 -- Use of chr function not allowed by GSCC. The new line character is introduced using line edit
1339 UTL_FILE.put_line(l_xml_file, l_fact||'
1340 '||'</FACT>');
1341 IF l_fetch_ctn = l_pagination_row_count THEN
1342 UTL_FILE.put_line(l_xml_file,'</FACT_DATA>');
1343 UTL_FILE.FCLOSE(l_xml_file);
1344 --update the job file metadata table with the file name
1345 BEGIN
1346 INSERT INTO ddr_ws_job_file_dls(file_id, job_id, file_name, status, delete_flag,src_sys_idnt, src_sys_dt, crtd_by_dsr, last_updt_by_dsr,
1347 created_by, creation_date, last_updated_by,
1348 last_update_date, last_update_login)
1349 VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N','ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1350 SYSDATE, 101);
1351 END;
1352 l_fetch_rows:=dbms_sql.fetch_rows(l_cur);
1353 IF(l_fetch_rows > 0) THEN
1354 IF l_total_row_count >=l_max_row_count THEN
1355 RAISE l_max_rows;
1356 END IF;
1357 l_file_id :=get_ddr_ws_file_seq_nextval(x_return_status,x_msg_count,x_msg_data);
1358 l_file_name := p_fact_code || '_'|| l_file_id || '.xml';
1359 l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);
1360 UTL_FILE.put_line(l_xml_file, '<?xml version="1.0" encoding="UTF-8"?>');
1361 UTL_FILE.put_line(l_xml_file, '<FACT_DATA>');
1362 l_fetch_ctn:=0;
1363 END IF;
1364 ELSE
1365 l_fetch_rows:=dbms_sql.fetch_rows(l_cur);
1366 END IF;
1367 l_total_row_count:=l_total_row_count+1;
1368 END LOOP;
1369 -- DBMS_OUTPUT.PUT_LINE('l_total_row_count='||l_total_row_count);
1370 dbms_sql.close_cursor(l_cur);
1371 IF l_fetch_ctn <> l_pagination_row_count THEN
1372 UTL_FILE.put_line(l_xml_file, '</FACT_DATA>');
1373 UTL_FILE.FCLOSE(l_xml_file);
1374 BEGIN
1375 -- DBMS_OUTPUT.PUT_LINE('third, l_file_id='||l_file_id);
1376 INSERT INTO ddr_ws_job_file_dls
1377 (file_id, job_id, file_name, status, delete_flag,
1378 src_sys_idnt, src_sys_dt, crtd_by_dsr, last_updt_by_dsr,
1379 created_by, creation_date, last_updated_by,
1380 last_update_date, last_update_login
1381 )
1385 END;
1382 VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N',
1383 'ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1384 SYSDATE, 101);
1386 END IF;
1387 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1388 EXCEPTION
1389 WHEN l_max_rows THEN
1390 IF dbms_sql.is_open(l_cur) THEN
1391 dbms_sql.close_cursor(l_cur);
1392 END IF;
1393 IF UTL_FILE.is_open(l_xml_file) THEN
1394 UTL_FILE.fclose(l_xml_file);
1395 END IF;
1396 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1397 x_msg_count:=1;
1398 x_msg_data:='Program exceeded maximum row OUT NOCOPY put';
1399 WHEN UTL_FILE.INTERNAL_ERROR THEN
1400 IF dbms_sql.is_open(l_cur) THEN
1401 dbms_sql.close_cursor(l_cur);
1402 END IF;
1403 IF UTL_FILE.is_open(l_xml_file) THEN
1404 UTL_FILE.fclose(l_xml_file);
1405 END IF;
1406 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1407 x_msg_count:=1;
1408 x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1409 WHEN UTL_FILE.INVALID_OPERATION THEN
1410 IF dbms_sql.is_open(l_cur) THEN
1411 dbms_sql.close_cursor(l_cur);
1412 END IF;
1413 IF UTL_FILE.is_open(l_xml_file) THEN
1414 UTL_FILE.fclose(l_xml_file);
1415 END IF;
1416 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1417 x_msg_count:=1;
1418 x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1419 WHEN UTL_FILE.INVALID_PATH THEN
1420 IF dbms_sql.is_open(l_cur) THEN
1421 dbms_sql.close_cursor(l_cur);
1422 END IF;
1423 IF UTL_FILE.is_open(l_xml_file) THEN
1424 UTL_FILE.fclose(l_xml_file);
1425 END IF;
1426 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1427 x_msg_count:=1;
1428 x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1429 WHEN UTL_FILE.WRITE_ERROR THEN
1430 IF dbms_sql.is_open(l_cur) THEN
1431 dbms_sql.close_cursor(l_cur);
1432 END IF;
1433 IF UTL_FILE.is_open(l_xml_file) THEN
1434 UTL_FILE.fclose(l_xml_file);
1435 END IF;
1436 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1437 x_msg_count:=1;
1438 x_msg_data:='Cannot write to file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1439 WHEN OTHERS THEN
1440 IF dbms_sql.is_open(l_cur) THEN
1441 dbms_sql.close_cursor(l_cur);
1442 END IF;
1443 IF UTL_FILE.is_open(l_xml_file) THEN
1444 UTL_FILE.fclose(l_xml_file);
1445 END IF;
1446 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1447 x_msg_count:=1;
1448 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1449 END write_fact_to_xml_file;
1450
1451 PROCEDURE validate_input_params(p_api_version IN NUMBER,
1452 p_mfg_org_cd IN VARCHAR2,
1453 p_org_cd IN VARCHAR2,
1454 p_org_dim_lvl_cd IN VARCHAR2,
1455 p_org_lvl_val IN VARCHAR2,
1456 p_exp_org_level IN VARCHAR2,
1457 p_loc_dim_lvl_cd IN VARCHAR2,
1458 p_loc_lvl_val IN VARCHAR2,
1459 p_exp_loc_level IN VARCHAR2,
1460 p_item_dim_lvl_cd IN VARCHAR2,
1461 p_item_lvl_val IN VARCHAR2,
1462 p_exp_item_level IN VARCHAR2,
1463 p_time_dim_lvl_cd IN VARCHAR2,
1464 p_time_lvl_val IN VARCHAR2,
1465 p_exp_time_level IN VARCHAR2,
1466 p_fact_code IN VARCHAR2,
1467 x_return_status OUT NOCOPY VARCHAR2,
1468 x_msg_count OUT NOCOPY NUMBER,
1469 x_msg_data OUT NOCOPY VARCHAR2)
1470 IS
1471 l_api_ver EXCEPTION;
1472 l_fact_code_null EXCEPTION;
1473 l_mfg_code_null EXCEPTION;
1474 l_rtl_org_code_null EXCEPTION;
1475 l_hrchy_cd VARCHAR2(50):=NULL;
1476 BEGIN
1477 IF p_api_version IS NULL THEN
1478 RAISE l_api_ver;
1479 END IF;
1480 IF p_api_version<>ddr_webservices_constants.g_api_version THEN
1481 RAISE l_api_ver;
1482 END IF;
1483 IF p_fact_code IS NULL THEN
1484 RAISE l_fact_code_null;
1485 END IF;
1486 IF p_mfg_org_cd IS NULL THEN
1487 RAISE l_mfg_code_null;
1488 END IF;
1489 IF p_org_cd IS NULL THEN
1490 RAISE l_rtl_org_code_null;
1491 END IF;
1492 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1493 EXCEPTION
1494 WHEN l_api_ver THEN
1495 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1496 x_msg_count:=1;
1497 x_msg_data:='API version number should not be null';
1498 WHEN l_fact_code_null THEN
1499 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1500 x_msg_count:=1;
1501 x_msg_data:='Fact code should not be null';
1502 WHEN l_mfg_code_null THEN
1503 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1504 x_msg_count:=1;
1505 x_msg_data:='Manufacturer Organization code should not be null';
1506 WHEN l_rtl_org_code_null THEN
1507 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1508 x_msg_count:=1;
1509 x_msg_data:='Retailer Organization code should not be null';
1510 WHEN OTHERS THEN
1511 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1512 x_msg_count:=1;
1513 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1514 END validate_input_params;
1515
1516 PROCEDURE get_sys_var_val(p_sys_var IN VARCHAR2,
1517 x_return_status OUT NOCOPY VARCHAR2,
1518 x_msg_count OUT NOCOPY NUMBER,
1519 x_msg_data OUT NOCOPY VARCHAR2,
1520 x_sys_var_val OUT NOCOPY VARCHAR2)
1521 IS
1522 BEGIN
1523 SELECT lkup_name INTO x_sys_var_val FROM ddr_r_lkup_mst WHERE lkup_cd=p_sys_var;
1524 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1525 EXCEPTION
1526 WHEN NO_DATA_FOUND THEN
1527 x_return_status := ddr_webservices_constants.g_ret_sts_error;
1528 x_msg_count := 1;
1529 x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1530 WHEN OTHERS THEN
1531 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1532 x_msg_count:=1;
1533 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1534 END get_sys_var_val;
1535
1536 FUNCTION get_ddr_ws_file_seq_nextval(x_return_status OUT NOCOPY VARCHAR2,
1537 x_msg_count OUT NOCOPY NUMBER,
1538 x_msg_data OUT NOCOPY VARCHAR2) RETURN VARCHAR2
1539 IS
1540 l_next_val NUMBER:=null;
1541 BEGIN
1542 SELECT DDR_WS_FILE_SEQ.NEXTVAL INTO l_next_val FROM dual;
1543 RETURN to_char(l_next_val);
1544 EXCEPTION
1545 WHEN NO_DATA_FOUND THEN
1546 x_return_status := ddr_webservices_constants.g_ret_sts_error;
1547 x_msg_count := 1;
1548 x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1549 WHEN OTHERS THEN
1550 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1551 x_msg_count:=1;
1552 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1553 END get_ddr_ws_file_seq_nextval;
1554
1555 PROCEDURE get_itm_hrchy_clauses(p_item_dim_lvl_cd IN VARCHAR2,
1556 p_item_lvl_val IN VARCHAR2,
1557 p_exp_item_level IN VARCHAR2,
1558 p_fact_code IN VARCHAR2,
1559 x_return_status OUT NOCOPY VARCHAR2,
1560 x_msg_count OUT NOCOPY VARCHAR2,
1561 x_msg_data OUT NOCOPY VARCHAR2,
1562 x_itm_ref_tbls OUT NOCOPY VARCHAR2,
1563 x_itm_ref_joins OUT NOCOPY VARCHAR2,
1564 x_itm_where_clus OUT NOCOPY VARCHAR2)
1565 IS
1566 l_lvl_rnk NUMBER:=null;
1567 BEGIN
1568 -- CASE p_fact_code
1569 -- for RETAIL SALE RETURN ITEM DAY fact
1570 --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1571
1572 IF p_item_dim_lvl_cd IS NOT NULL THEN
1573 get_hrchy_lvl('ITEM',p_item_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1574 ELSIF p_exp_item_level IS NOT NULL THEN
1575 get_hrchy_lvl('ITEM',p_exp_item_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1576 END IF;
1577 IF l_lvl_rnk IS NULL THEN
1578 RETURN;
1579 END IF;
1580 IF l_lvl_rnk>=1 THEN
1581 x_itm_ref_tbls:=',DDR_R_MFG_SKU_ITEM ITMA';
1582 x_itm_ref_joins:='AND x.GLBL_ITEM_ID = ITMA.GLBL_ITEM_ID';
1583 END IF;
1584 IF l_lvl_rnk>=2 THEN
1585 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM ITMB';
1586 x_itm_ref_joins:=x_itm_ref_joins||' AND ITMA.MFG_ITEM_ID = ITMB.MFG_ITEM_ID';
1587 END IF;
1588 IF l_lvl_rnk>=3 THEN
1589 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM_SBC ITMC';
1590 x_itm_ref_joins:=x_itm_ref_joins||' AND ITMB.MFG_ITEM_SBC_ID = ITMC.MFG_ITEM_SBC_ID';
1591 END IF;
1592 IF l_lvl_rnk>=4 THEN
1593 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM_CLASS ITMD';
1594 x_itm_ref_joins:=x_itm_ref_joins||' AND ITMC.MFG_ITEM_CLASS_ID = ITMD.MFG_ITEM_CLASS_ID';
1595 END IF;
1596 IF l_lvl_rnk>=5 THEN
1597 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM_GRP ITME';
1598 x_itm_ref_joins:=x_itm_ref_joins||' AND ITMD.MFG_ITEM_GRP_ID = ITME.MFG_ITEM_GRP_ID';
1599 END IF;
1600 IF l_lvl_rnk>=6 THEN
1601 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM_DIV ITMF';
1602 x_itm_ref_joins:=x_itm_ref_joins||' AND ITME.MFG_ITEM_DIV_ID = ITMF.MFG_ITEM_DIV_ID';
1603 END IF;
1604 IF p_item_dim_lvl_cd IS NOT NULL THEN
1605 get_item_ref_join(l_lvl_rnk,x_itm_where_clus,x_return_status,x_msg_count,x_msg_data);
1606 x_itm_where_clus:= x_itm_where_clus || '=''' || p_item_lvl_val||'''';
1607 END IF;
1608 -- END CASE;
1609 EXCEPTION
1610 WHEN OTHERS THEN
1611 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1612 x_msg_count:=1;
1613 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1614 END get_itm_hrchy_clauses;
1615
1616 PROCEDURE get_item_ref_join(p_lvl_rnk IN NUMBER,
1617 x_ref_join OUT NOCOPY VARCHAR2,
1618 x_return_status OUT NOCOPY VARCHAR2,
1619 x_msg_count OUT NOCOPY VARCHAR2,
1620 x_msg_data OUT NOCOPY VARCHAR2)
1621 IS
1622 BEGIN
1623 CASE p_lvl_rnk
1624 WHEN 1 THEN
1625 x_ref_join:=' ITMA.MFG_SKU_ITEM_NBR';
1626 WHEN 2 THEN
1627 x_ref_join:=' ITMB.MFG_ITEM_NBR ';
1628 WHEN 3 THEN
1629 x_ref_join:=' ITMC.MFG_SBC_CD ';
1630 WHEN 4 THEN
1631 x_ref_join:=' ITMD.MFG_CLASS_CD ';
1632 WHEN 5 THEN
1633 x_ref_join:=' ITME.MFG_GRP_CD ';
1634 WHEN 6 THEN
1635 x_ref_join:=' ITMF.MFG_DIV_CD ';
1636 END CASE;
1637 EXCEPTION
1638 WHEN OTHERS THEN
1639 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1640 x_msg_count:=1;
1641 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1642
1643 END get_item_ref_join;
1644
1645
1646 PROCEDURE get_org_hrchy_clauses(p_org_dim_lvl_cd IN VARCHAR2,
1647 p_org_lvl_val IN VARCHAR2,
1648 p_exp_org_level IN VARCHAR2,
1649 p_fact_code IN VARCHAR2,
1650 x_return_status OUT NOCOPY VARCHAR2,
1651 x_msg_count OUT NOCOPY VARCHAR2,
1652 x_msg_data OUT NOCOPY VARCHAR2,
1653 x_org_ref_tbls OUT NOCOPY VARCHAR2,
1654 x_org_ref_joins OUT NOCOPY VARCHAR2,
1655 x_org_where_clus OUT NOCOPY VARCHAR2)
1656 IS
1657 l_lvl_rnk NUMBER:=null;
1658 BEGIN
1659 -- CASE p_fact_code
1660 -- for RETAIL SALE RETURN ITEM DAY fact
1661 --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1662 IF p_org_dim_lvl_cd IS NOT NULL THEN
1663 get_hrchy_lvl('ORGANIZATION',p_org_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1664 ELSIF p_exp_org_level IS NOT NULL THEN
1665 get_hrchy_lvl('ORGANIZATION',p_exp_org_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1666 END IF;
1667 IF l_lvl_rnk IS NULL THEN
1668 RETURN;
1669 END IF;
1670 IF l_lvl_rnk>=1 THEN
1671 x_org_ref_tbls:=',DDR_R_ORG_BSNS_UNIT ORGA';
1672 x_org_ref_joins:=' AND X.ORG_BSNS_UNIT_ID = ORGA.ORG_BSNS_UNIT_ID';
1673 END IF;
1674 IF l_lvl_rnk>=2 THEN
1675 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG_DSTRCT ORGB';
1676 x_org_ref_joins:=x_org_ref_joins||' AND ORGA.ORG_DSTRCT_ID = ORGB.ORG_DSTRCT_ID';
1677 END IF;
1678 IF l_lvl_rnk>=3 THEN
1679 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG_RGN ORGC';
1680 x_org_ref_joins:=x_org_ref_joins||' AND ORGB.ORG_RGN_ID = ORGC.ORG_RGN_ID';
1681 END IF;
1682 IF l_lvl_rnk>=4 THEN
1683 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG_AREA ORGD';
1684 x_org_ref_joins:=x_org_ref_joins||' AND ORGC.ORG_AREA_ID = ORGD.ORG_AREA_ID';
1685 END IF;
1686 IF l_lvl_rnk>=5 THEN
1687 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG_CHAIN ORGE';
1688 x_org_ref_joins:=x_org_ref_joins||' AND ORGD.ORG_CHAIN_ID = ORGE.ORG_CHAIN_ID';
1689 END IF;
1690 IF l_lvl_rnk>=6 THEN
1691 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG ORGF';
1692 --x_itm_ref_joins:=x_itm_ref_joins||' ';
1693 END IF;
1694 IF l_lvl_rnk>=7 THEN
1695 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_RTL_CLSTR ORGG,DDR_R_RTL_CLSTR_RTL_ASSC ORGH';
1696 x_org_ref_joins:=x_org_ref_joins||' AND ORGE.ORG_CD = ORGH.RTL_ORG_CD';
1697 END IF;
1698 IF p_org_dim_lvl_cd IS NOT NULL THEN
1699 get_org_ref_join(l_lvl_rnk,x_org_where_clus,x_return_status,x_msg_count,x_msg_data);
1703 EXCEPTION
1700 x_org_where_clus:= x_org_where_clus || '=''' || p_org_lvl_val||'''';
1701 END IF;
1702 -- END CASE;
1704 WHEN OTHERS THEN
1705 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1706 x_msg_count:=1;
1707 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1708 END get_org_hrchy_clauses;
1709
1710 PROCEDURE get_org_ref_join(p_lvl_rnk IN NUMBER,
1711 x_ref_join OUT NOCOPY VARCHAR2,
1712 x_return_status OUT NOCOPY VARCHAR2,
1713 x_msg_count OUT NOCOPY VARCHAR2,
1714 x_msg_data OUT NOCOPY VARCHAR2)
1715 IS
1716 BEGIN
1717 CASE p_lvl_rnk
1718 --bug 6921259 change start
1719 WHEN 0 THEN
1720 x_ref_join:=' X.INV_LOC_CD ';
1721 --bug 6921259 change end
1722 WHEN 1 THEN
1723 x_ref_join:=' ORGA.BSNS_UNIT_CD ';
1724 WHEN 5 THEN
1725 x_ref_join:=' ORGE.CHAIN_CD ';
1726 WHEN 6 THEN
1727 x_ref_join:=' ORGF.ORG_CD ';
1728 WHEN 7 THEN
1729 x_ref_join:=' ORGG.CLSTR_CD ';
1730 END CASE;
1731 EXCEPTION
1732 WHEN OTHERS THEN
1733 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1734 x_msg_count:=1;
1735 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1736 END get_org_ref_join;
1737
1738 PROCEDURE get_time_hrchy_clauses(p_time_dim_lvl_cd IN VARCHAR2,
1739 p_time_lvl_val IN VARCHAR2,
1740 p_exp_time_level IN VARCHAR2,
1741 p_fact_code IN VARCHAR2,
1742 p_org_cd IN VARCHAR2,
1743 x_return_status OUT NOCOPY VARCHAR2,
1744 x_msg_count OUT NOCOPY VARCHAR2,
1745 x_msg_data OUT NOCOPY VARCHAR2,
1746 x_time_ref_tbls OUT NOCOPY VARCHAR2,
1747 x_time_ref_joins OUT NOCOPY VARCHAR2,
1748 x_time_where_clus OUT NOCOPY VARCHAR2)
1749 IS
1750 l_lvl_rnk NUMBER:=null;
1751 l_hrchy_lvl_name VARCHAR2(50):=null;
1752 BEGIN
1753 -- CASE p_fact_code
1754 -- for RETAIL SALE RETURN ITEM DAY fact
1755 --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1756 --to get the time hierarchy level name(GREGORIAN TIME OR BUSINESS TIME)
1757 IF p_time_dim_lvl_cd IS NOT NULL THEN
1758 SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE
1759 hrchy_lvl_cd=p_time_dim_lvl_cd;
1760 get_hrchy_lvl(l_hrchy_lvl_name,p_time_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1761 ELSIF p_exp_time_level IS NOT NULL THEN
1762 SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE
1763 hrchy_lvl_cd=p_exp_time_level;
1764 get_hrchy_lvl(l_hrchy_lvl_name,p_exp_time_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1765 END IF;
1766 IF l_lvl_rnk IS NULL THEN
1767 RETURN;
1768 END IF;
1769 CASE l_hrchy_lvl_name
1770 WHEN 'GREGORIAN TIME' THEN
1771 IF l_lvl_rnk>=1 THEN
1772 x_time_ref_tbls:=',DDR_R_DAY TDAY';
1773 x_time_ref_joins:=' AND X.DAY_CD = TDAY.DAY_CD';
1774 END IF;
1775 IF l_lvl_rnk>=3 THEN
1776 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_CLNDR_MNTH TCA';
1777 x_time_ref_joins:=x_time_ref_joins||' AND TDAY.CLNDR_MNTH_ID = TCA.CLNDR_MNTH_ID';
1778 END IF;
1779 IF l_lvl_rnk>=5 THEN
1780 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_CLNDR_QTR TCB,DDR_R_CLNDR_YR TCC';
1781 x_time_ref_joins:=x_time_ref_joins||' AND TCA.CLNDR_QTR_ID=TCB.CLNDR_QTR_ID AND TCB.CLNDR_YR_ID = TCC.CLNDR_YR_ID';
1782 END IF;
1783 IF p_time_dim_lvl_cd IS NOT NULL THEN
1784 get_time_ref_join(l_hrchy_lvl_name,l_lvl_rnk,x_time_where_clus,x_return_status,x_msg_count,x_msg_data);
1785 x_time_where_clus:= x_time_where_clus || '=''' ||
1786 p_time_lvl_val||'''';
1787 END IF;
1788 WHEN 'BUSINESS TIME' THEN
1789 IF l_lvl_rnk>=1 THEN
1790 x_time_ref_tbls:=',DDR_R_CLNDR TCLNDR,DDR_R_DAY TDAY,DDR_R_BASE_DAY TBA';
1791 x_time_ref_joins:=' AND X.DAY_CD = TDAY.DAY_CD AND TDAY.DAY_CD = TBA.DAY_CD '
1792 ||' AND TBA.CLNDR_TYP=''BSNS'' AND TBA.CLNDR_CD=TCLNDR.CLNDR_CD AND TCLNDR.CLNDR_TYP=''BSNS'''
1793 ||' AND TCLNDR.ORG_CD='''||p_org_cd||'''';
1794 END IF;
1795 IF l_lvl_rnk>=2 THEN
1796 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_BSNS_WK TBB';
1797 x_time_ref_joins:=x_time_ref_joins||' AND TBA.WK_ID = TBB.BSNS_WK_ID';
1798 END IF;
1799 IF l_lvl_rnk>=3 THEN
1800 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_BSNS_MNTH TBC';
1801 x_time_ref_joins:=x_time_ref_joins||' AND TBB.BSNS_MNTH_ID = TBC.BSNS_MNTH_ID';
1802 END IF;
1803 IF l_lvl_rnk>=4 THEN
1804 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_BSNS_QTR TBD';
1805 x_time_ref_joins:=x_time_ref_joins||' AND TBC.BSNS_QTR_ID = TBD.BSNS_QTR_ID';
1806 END IF;
1807 IF l_lvl_rnk>=5 THEN
1808 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_BSNS_YR TBE';
1809 x_time_ref_joins:=x_time_ref_joins||' AND TBD.BSNS_YR_ID = TBE.BSNS_YR_ID';
1810 END IF;
1811 IF p_time_dim_lvl_cd IS NOT NULL THEN
1812 get_time_ref_join(l_hrchy_lvl_name,l_lvl_rnk,x_time_where_clus,x_return_status,x_msg_count,x_msg_data);
1813 x_time_where_clus:= x_time_where_clus || '=''' ||
1814 p_time_lvl_val||'''';
1815 END IF;
1816
1820 WHEN OTHERS THEN
1817 END CASE;
1818 -- END CASE;
1819 EXCEPTION
1821 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1822 x_msg_count:=1;
1823 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1824 END get_time_hrchy_clauses;
1825
1826 PROCEDURE get_time_ref_join(p_hrchy_name IN VARCHAR2,
1827 p_lvl_rnk IN NUMBER,
1828 x_ref_join OUT NOCOPY VARCHAR2,
1829 x_return_status OUT NOCOPY VARCHAR2,
1830 x_msg_count OUT NOCOPY VARCHAR2,
1831 x_msg_data OUT NOCOPY VARCHAR2)
1832 IS
1833 BEGIN
1834 CASE p_hrchy_name
1835 WHEN 'GREGORIAN TIME' THEN
1836 CASE p_lvl_rnk
1837 WHEN 1 THEN
1838 x_ref_join:=' X.DAY_CD ';
1839 WHEN 3 THEN
1840 x_ref_join:=' TCA.MNTH_CD ';
1841 WHEN 5 THEN
1842 x_ref_join:=' TCC.YR_CD';
1843 END CASE;
1844 WHEN 'BUSINESS TIME' THEN
1845 CASE p_lvl_rnk
1846 WHEN 1 THEN
1847 x_ref_join:=' X.DAY_CD';
1848 WHEN 2 THEN
1849 x_ref_join:=' TBB.WK_CD';
1850 WHEN 3 THEN
1851 x_ref_join:=' TBC.MNTH_CD ';
1852 WHEN 4 THEN
1853 x_ref_join:=' TBD.QTR_CD ';
1854 WHEN 5 THEN
1855 --bug 6928308 change start
1856 x_ref_join:=' TBE.YR_CD ';
1857 --bug 6928308 change end
1858 END CASE;
1859 END CASE;
1860 EXCEPTION
1861 WHEN OTHERS THEN
1862 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1863 x_msg_count:=1;
1864 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1865 END get_time_ref_join;
1866
1867
1868 PROCEDURE get_loc_hrchy_clauses(p_loc_dim_lvl_cd IN VARCHAR2,
1869 p_loc_lvl_val IN VARCHAR2,
1870 p_exp_loc_level IN VARCHAR2,
1871 p_org_dim_lvl_cd IN VARCHAR2,
1872 p_fact_code IN VARCHAR2,
1873 x_return_status OUT NOCOPY VARCHAR2,
1874 x_msg_count OUT NOCOPY VARCHAR2,
1875 x_msg_data OUT NOCOPY VARCHAR2,
1876 x_loc_ref_tbls OUT NOCOPY VARCHAR2,
1877 x_loc_ref_joins OUT NOCOPY VARCHAR2,
1878 x_loc_where_clus OUT NOCOPY VARCHAR2)
1879 IS
1880 l_lvl_rnk NUMBER:=null;
1881 l_org_lvl_rnk NUMBER:=null;
1882 BEGIN
1883 -- CASE p_fact_code
1884 -- for RETAIL SALE RETURN ITEM DAY fact
1885 --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1886 IF p_loc_dim_lvl_cd IS NOT NULL THEN
1887 get_hrchy_lvl('LOCATION',p_loc_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1888 ELSIF p_exp_loc_level IS NOT NULL THEN
1889 get_hrchy_lvl('LOCATION',p_exp_loc_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1890 END IF;
1891 IF l_lvl_rnk IS NULL THEN
1892 RETURN;
1893 END IF;
1894 IF l_lvl_rnk>=1 THEN
1895 --check if p_org_dim_lvl_cd is not specified, the include DSR_R_ORG_BSNS_UNIT ORGA in FROM clause
1896 IF p_org_dim_lvl_cd IS NULL THEN
1897 x_loc_ref_tbls:= ',DDR_R_ORG_BSNS_UNIT ORGA';
1898 x_loc_ref_joins:=' AND X.ORG_BSNS_UNIT_ID = ORGA.ORG_BSNS_UNIT_ID';
1899 END IF;
1900 x_loc_ref_tbls:= x_loc_ref_tbls|| ',DDR_R_ADDR_LOC LOCA,DDR_R_CITY LOCB';
1901 x_loc_ref_joins:=x_loc_ref_joins||' AND ORGA.ADDR_LOC_ID = LOCA.ADDR_LOC_ID(+) AND LOCA.CITY_CD = LOCB.CITY_CD(+)';
1902 END IF;
1903 IF l_lvl_rnk>=2 THEN
1904 x_loc_ref_tbls:=x_loc_ref_tbls || ',DDR_R_STATE LOCC';
1905 x_loc_ref_joins:=x_loc_ref_joins||' AND LOCB.STATE_CD = LOCC.STATE_CD(+)';
1906 END IF;
1907 IF l_lvl_rnk>=3 THEN
1908 x_loc_ref_tbls:=x_loc_ref_tbls || ',DDR_R_CNTRY LOCD';
1909 x_loc_ref_joins:=x_loc_ref_joins||' AND LOCC.CNTRY_CD = LOCD.CNTRY_CD(+)';
1910 END IF;
1911 IF p_loc_dim_lvl_cd IS NOT NULL THEN
1912 get_loc_ref_join(l_lvl_rnk,x_loc_where_clus,x_return_status,x_msg_count,x_msg_data);
1913 x_loc_where_clus:= x_loc_where_clus || '=''' || p_loc_lvl_val||'''';
1914 END IF;
1915 -- END CASE;
1916 EXCEPTION
1917 WHEN OTHERS THEN
1918 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1919 x_msg_count:=1;
1920 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1921 END get_loc_hrchy_clauses;
1922
1923 PROCEDURE get_loc_ref_join(p_lvl_rnk IN NUMBER,
1924 x_ref_join OUT NOCOPY VARCHAR2,
1925 x_return_status OUT NOCOPY VARCHAR2,
1926 x_msg_count OUT NOCOPY VARCHAR2,
1927 x_msg_data OUT NOCOPY VARCHAR2)
1928 IS
1929 BEGIN
1930 CASE p_lvl_rnk
1931 WHEN 1 THEN
1932 x_ref_join:=' LOCB.CITY_CD ';
1933 WHEN 2 THEN
1934 x_ref_join:=' LOCC.STATE_CD ';
1935 WHEN 3 THEN
1936 x_ref_join:=' LOCD.CNTRY_CD ';
1937 END CASE;
1938 EXCEPTION
1939 WHEN OTHERS THEN
1940 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1941 x_msg_count:=1;
1942 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1943 END get_loc_ref_join;
1944
1945 PROCEDURE get_hrchy_lvl(p_hrchy_lvl_name IN VARCHAR2,
1946 p_hrchy_lvl_cd IN VARCHAR2,
1947 x_hrchy_lvl OUT NOCOPY NUMBER,
1948 x_return_status OUT NOCOPY VARCHAR2,
1949 x_msg_count OUT NOCOPY VARCHAR2,
1950 x_msg_data OUT NOCOPY VARCHAR2)
1951 IS
1952 l_hrchy_lvl NUMBER:=null;
1953 BEGIN
1954 SELECT lvl_rnk INTO l_hrchy_lvl FROM DDR_WS_METADATA WHERE HRCHY_LVL_CD=p_hrchy_lvl_cd AND hrchy_lvl_name=p_hrchy_lvl_name;
1955 -- DBMS_OUTPUT.PUT_LINE('p_hrchy_lvl_name='||p_hrchy_lvl_name||' ,p_hrchy_lvl_cd='||p_hrchy_lvl_cd||' ,x_hrchy_lvl='||l_hrchy_lvl);
1956 x_hrchy_lvl:=l_hrchy_lvl;
1957 EXCEPTION
1958 WHEN NO_DATA_FOUND THEN
1959 x_return_status := ddr_webservices_constants.g_ret_sts_error;
1960 x_msg_count := 1;
1961 x_msg_data := 'No data found for hierarchy code:'|| p_hrchy_lvl_cd ||'. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1962 END get_hrchy_lvl;
1963
1964 --Bug 6880404 change start
1965 PROCEDURE get_other_join_conditions(p_fact_code VARCHAR2,
1966 x_return_status OUT NOCOPY VARCHAR2,
1967 x_msg_count OUT NOCOPY VARCHAR2,
1968 x_msg_data OUT NOCOPY VARCHAR2,
1969 x_oth_join_codn OUT NOCOPY VARCHAR2)
1970 IS
1971 l_join_cndn VARCHAR2(32767):=NULL;
1972 l_max_frcst_date DATE:=null;
1973 BEGIN
1974 --if the fact code is for forcest sales table, then join condition to fetch
1975 --the latest forcest version
1976 IF p_fact_code= ddr_webservices_constants.g_sfid_cd THEN
1977 --bug 6905930 change start
1978 l_join_cndn:= ' AND (x.frcst_vrsn,x.mfg_org_cd,x.rtl_org_cd,
1979 x.org_bsns_unit_id, x.day_cd, x.glbl_item_id, x.rtl_sku_item_id) IN(SELECT
1980 MAX(frcst_vrsn), mfg_org_cd, rtl_org_cd, org_bsns_unit_id, day_cd,
1981 glbl_item_id, rtl_sku_item_id FROM ddr_b_sls_frcst_item_day GROUP BY
1982 mfg_org_cd, rtl_org_cd, org_bsns_unit_id, day_cd, glbl_item_id,
1983 rtl_sku_item_id)';
1984 --bug 6905930 change end
1985 END IF;
1986 x_oth_join_codn := l_join_cndn;
1987 EXCEPTION
1988 WHEN NO_DATA_FOUND THEN
1989 x_return_status := ddr_webservices_constants.g_ret_sts_error;
1990 x_msg_count := 1;
1991 x_msg_data := 'No data found. Error Code' ||sqlcode||' Error
1992 message:'||sqlerrm;
1993 WHEN OTHERS THEN
1994 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1995 x_msg_count:=1;
1996 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:
1997 '||sqlerrm;
1998 END get_other_join_conditions;
1999 --Bug 6880404 change end
2000
2001 END ddr_webservices_pub;
[Home] [Help]