1 PACKAGE BODY ddr_webservices_pub AS
2 /* $Header: ddrpcwsb.pls 120.8.12010000.3 2010/03/03 04:18:48 vbhave ship $ */
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,
271 x_msg_data OUT NOCOPY VARCHAR2,
272 x_itm_ref_tbls OUT NOCOPY VARCHAR2,
273 x_itm_ref_joins OUT NOCOPY VARCHAR2,
274 x_itm_where_clus 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,
514 p_item_dim_lvl_cd,
515 p_item_lvl_val,
516 p_exp_item_level,
517 p_time_dim_lvl_cd,
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;
546 PROCEDURE ddr_fact_details_prc(p_api_version IN NUMBER,
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
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
597 IF l_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
598 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
599 RETURN;
600 END IF;
601 --write data to xml file based on dynamic query generated
602 write_fact_to_xml_file(l_query,p_fact_code,l_job_id,l_return_status,l_msg_count,l_msg_data);
603 --update job status to complete/error
604 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 ;
605 EXCEPTION
606 WHEN NO_DATA_FOUND THEN
607 l_return_status:=ddr_webservices_constants.g_ret_sts_error;
608 l_msg_count:=1;
609 l_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
610 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
611 WHEN OTHERS THEN
612 l_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
613 l_msg_count:=1;
614 l_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
615 UPDATE DDR_WS_JOB SET status=l_return_status,err_message=l_msg_data,end_date=sysdate WHERE job_id=p_job_id ;
616 END ddr_fact_details_prc;
617
618
619 PROCEDURE get_dyn_query(p_api_version IN NUMBER,
620 p_call_type IN VARCHAR2,
621 p_mfg_org_cd IN VARCHAR2,
622 p_org_cd IN VARCHAR2,
623 p_org_dim_lvl_cd IN VARCHAR2,
624 p_org_lvl_val IN VARCHAR2,
625 p_exp_org_level IN VARCHAR2,
626 p_loc_dim_lvl_cd IN VARCHAR2,
627 p_loc_lvl_val IN VARCHAR2,
628 p_exp_loc_level IN VARCHAR2,
629 p_item_dim_lvl_cd IN VARCHAR2,
630 p_item_lvl_val IN VARCHAR2,
631 p_exp_item_level IN VARCHAR2,
632 p_time_dim_lvl_cd IN VARCHAR2,
633 p_time_lvl_val IN VARCHAR2,
634 p_exp_time_level IN VARCHAR2,
635 p_fact_code IN VARCHAR2,
636 x_return_status OUT NOCOPY VARCHAR2,
637 x_msg_count OUT NOCOPY NUMBER,
638 x_msg_data OUT NOCOPY VARCHAR2,
639 x_dyn_query OUT NOCOPY VARCHAR2)
640 IS
641 l_query VARCHAR2(32767):=null;
642 l_fact_table_name VARCHAR2(30) :=null;
643 l_fact_cols VARCHAR2(32767):=null;
644 l_org_where_clus VARCHAR2(100) :=null;
645 l_loc_where_clus VARCHAR2(100) :=null;
646 l_itm_where_clus VARCHAR2(100) :=null;
647 l_time_where_clus VARCHAR2(100) :=null;
648 l_group_col VARCHAR2(120) :=null;
649 l_query_len NUMBER :=null;
650 l_itm_ref_tbls VARCHAR2(32767):=null;
651 l_itm_ref_joins VARCHAR2(32767):=null;
655 l_time_ref_joins VARCHAR2(32767):=null;
652 l_org_ref_tbls VARCHAR2(32767):=null;
653 l_org_ref_joins VARCHAR2(32767):=null;
654 l_time_ref_tbls VARCHAR2(32767):=null;
656 l_loc_ref_tbls VARCHAR2(32767):=null;
657 l_loc_ref_joins VARCHAR2(32767):=null;
658 --Bug 6880404 change start
659 l_oth_join_codn VARCHAR2(32767):=null;
660 --Bug 6880404 change end
661 BEGIN
662 --validate the input parameters
663 validate_input_params(p_api_version,
664 p_mfg_org_cd,
665 p_org_cd,
666 p_org_dim_lvl_cd,
667 p_org_lvl_val,
668 null,
669 p_loc_dim_lvl_cd,
670 p_loc_lvl_val,
671 null,
672 p_item_dim_lvl_cd,
673 p_item_lvl_val,
674 null,
675 p_time_dim_lvl_cd,
676 p_time_lvl_val,
677 p_exp_time_level,
678 p_fact_code,
679 x_return_status,
680 x_msg_count,
681 x_msg_data);
682 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
683 RETURN;
684 END IF;
685 -- get fact table name
686 get_fact_table(p_fact_code,x_return_status,x_msg_count,x_msg_data,l_fact_table_name);
687 IF p_call_type='A' THEN
688 -- get aggregated select fact column names
689 get_aggr_fact_colms(p_fact_code,x_return_status,x_msg_count,x_msg_data,l_fact_cols);
690 ELSIF p_call_type='D' THEN
691 -- get detailed select fact column names
692 get_detail_fact_colms(p_fact_code,x_return_status,x_msg_count,x_msg_data,l_fact_cols);
693 END IF;
694 --get the reference tables ,reference table joins and where clause for the item hierarchy
695 IF p_item_dim_lvl_cd IS NOT NULL OR p_exp_item_level IS NOT NULL THEN
696 get_itm_hrchy_clauses(p_item_dim_lvl_cd,
697 p_item_lvl_val,
698 p_exp_item_level,
699 p_fact_code,
700 x_return_status,
701 x_msg_count,
702 x_msg_data,
703 l_itm_ref_tbls,
704 l_itm_ref_joins,
705 l_itm_where_clus);
706 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
707 RETURN;
708 END IF;
709 END IF;
710 --get the reference tables ,reference table joins and where clause join for the organization hierarchy level
711 IF p_org_dim_lvl_cd IS NOT NULL OR p_exp_org_level IS NOT NULL THEN
712 get_org_hrchy_clauses(p_org_dim_lvl_cd,
713 p_org_lvl_val,
714 p_exp_org_level,
715 p_fact_code,
716 x_return_status,
717 x_msg_count,
718 x_msg_data,
719 l_org_ref_tbls,
720 l_org_ref_joins,
721 l_org_where_clus);
722 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
723 RETURN;
724 END IF;
725 END IF;
726 --get the reference tables ,reference table joins and where clause join for the time hierarchy level
727 IF p_time_dim_lvl_cd IS NOT NULL OR p_exp_time_level IS NOT NULL THEN
728 get_time_hrchy_clauses(p_time_dim_lvl_cd,
729 p_time_lvl_val,
730 p_exp_time_level,
731 p_fact_code,
732 p_org_cd,
733 x_return_status,
734 x_msg_count,
735 x_msg_data,
736 l_time_ref_tbls,
737 l_time_ref_joins,
738 l_time_where_clus);
739 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
740 RETURN;
741 END IF;
742 END IF;
743 --get the reference tables ,reference table joins and where clause join for the location hierarchy level
744 IF p_loc_dim_lvl_cd IS NOT NULL OR p_exp_loc_level IS NOT NULL THEN
745 get_loc_hrchy_clauses(p_loc_dim_lvl_cd,
746 p_loc_lvl_val,
747 p_exp_loc_level,
748 p_org_dim_lvl_cd,
749 p_fact_code,
750 x_return_status,
751 x_msg_count,
752 x_msg_data,
753 l_loc_ref_tbls,
754 l_loc_ref_joins,
755 l_loc_where_clus);
756 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
757 RETURN;
758 END IF;
759 END IF;
760 --Bug 6880404 change start
761 --get other join conditions
762 get_other_join_conditions(p_fact_code,
763 x_return_status,
764 x_msg_count,
765 x_msg_data,
766 l_oth_join_codn);
767 --Bug 6880404 change end
768 -- get group by columns
769 IF p_call_type='A' THEN
773 p_exp_time_level,
770 get_aggr_group_colms(p_exp_org_level,
771 p_exp_loc_level,
772 p_exp_item_level,
774 x_return_status,
775 x_msg_count,
776 x_msg_data,
777 l_group_col);
778 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
779 RETURN;
780 END IF;
781 END IF;
782
783 --dynamically building query
784 l_query := 'SELECT ' || l_fact_cols;
785 IF p_call_type='A' AND l_group_col IS NOT NULL THEN
786 l_query := l_query || ','|| l_group_col;
787 END IF;
788 --appending fact table to query string
789 l_query := l_query || ' FROM ' || l_fact_table_name || ' x';
790 --include the various reference tables in from clause
791 --Item Hierarchy reference tables
792 l_query := l_query || l_itm_ref_tbls;
793 --Organization Hierarchy reference tables
794 l_query := l_query || l_org_ref_tbls;
795 --Time Hierarchy reference tables
796 l_query := l_query || l_time_ref_tbls;
797 --location Hierarchy reference tables
798 l_query := l_query || l_loc_ref_tbls;
799 --appending organization code to query string
800 l_query := l_query || ' WHERE '|| ' x.MFG_ORG_CD ='''|| p_mfg_org_cd||'''';
801 l_query := l_query || ' AND '|| ' x.RTL_ORG_CD ='''|| p_org_cd||'''';
802 --Bug 6880404 change start
803 l_query := l_query || l_oth_join_codn;
804 --Bug 6880404 change end
805 -- ITEM Dimension Hierarchy joins
806 l_query := l_query || l_itm_ref_joins;
807 IF l_itm_where_clus IS NOT NULL THEN
808 l_query := l_query || ' AND ' || l_itm_where_clus || '';
809 END IF;
810 -- Organization Dimension Hierarchy joins
811 l_query := l_query || l_org_ref_joins;
812 IF l_org_where_clus IS NOT NULL THEN
813 l_query := l_query || ' AND '|| l_org_where_clus || '';
814 END IF;
815 -- Time Dimension Hierarchy joins
816 l_query := l_query || l_time_ref_joins;
817 IF l_time_where_clus IS NOT NULL THEN
818 l_query := l_query || ' AND '|| l_time_where_clus || '';
819 END IF;
820 -- Loaction Dimension Hierarchy joins
821 l_query := l_query ||l_loc_ref_joins;
822 IF l_loc_where_clus IS NOT NULL THEN
823 l_query := l_query || ' AND '|| l_loc_where_clus || '';
824 END IF;
825 IF p_call_type='A' THEN
826 --group by clause join
827 IF l_group_col IS NOT NULL THEN
828 l_query := l_query || ' GROUP BY '|| l_group_col;
829 END IF;
830 END IF;
831 SELECT LENGTH(l_query) into l_query_len from dual;
832 -- DBMS_OUTPUT.PUT_LINE('l_query_len='||l_query_len);
833 -- DBMS_OUTPUT.PUT_LINE('l_query='||l_query);
834 x_dyn_query:= l_query;
835 EXCEPTION
836 WHEN NO_DATA_FOUND THEN
837 x_return_status := ddr_webservices_constants.g_ret_sts_error;
838 x_msg_count := 1;
839 x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
840 WHEN OTHERS THEN
841 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
842 x_msg_count:=1;
843 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
844 END get_dyn_query;
845
846 PROCEDURE get_fact_table(p_fact_code IN VARCHAR2,
847 x_return_status OUT NOCOPY VARCHAR2,
848 x_msg_count OUT NOCOPY NUMBER,
849 x_msg_data OUT NOCOPY VARCHAR2,
850 x_fact_name OUT NOCOPY VARCHAR2)
851 IS
852 BEGIN
853 --case condition to determine name of the fact table
854 CASE p_fact_code
855 -- for MARKET ITEM SALES DAY
856 WHEN ddr_webservices_constants.g_misd_cd THEN
857 x_fact_name:= ddr_webservices_constants.g_misd_fact_tbl;
858 -- for PROMOTION PLAN
859 WHEN ddr_webservices_constants.g_pp_cd THEN
860 x_fact_name:= ddr_webservices_constants.g_pp_fact_tbl;
861 -- for RETAIL INVENTORY ITEM DAY
862 WHEN ddr_webservices_constants.g_riid_cd THEN
863 x_fact_name:= ddr_webservices_constants.g_riid_fact_tbl;
864 -- for RETAIL SALE RETURN ITEM DAY fact
865 WHEN ddr_webservices_constants.g_rsrid_cd THEN
866 x_fact_name:= ddr_webservices_constants.g_rsrid_fact_tbl;
867 -- for RETAILER ORDER ITEM DAY
868 WHEN ddr_webservices_constants.g_roid_cd THEN
869 x_fact_name:= ddr_webservices_constants.g_roid_fact_tbl;
870 -- for RETAILER SHIP ITEM DAY
871 WHEN ddr_webservices_constants.g_rsid_cd THEN
872 x_fact_name:= ddr_webservices_constants.g_rsid_fact_tbl;
873 -- for SALE FORECAST ITEM BY DAY
874 WHEN ddr_webservices_constants.g_sfid_cd THEN
875 x_fact_name:= ddr_webservices_constants.g_sfid_fact_tbl;
876 END CASE;
877 EXCEPTION
878 WHEN OTHERS THEN
879 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
880 x_msg_count:=1;
881 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
882 END get_fact_table;
883
884 PROCEDURE get_aggr_fact_colms(p_fact_code IN VARCHAR2,
885 x_return_status OUT NOCOPY VARCHAR2,
886 x_msg_count OUT NOCOPY NUMBER,
887 x_msg_data OUT NOCOPY VARCHAR2,
891 --case condition to identify aggregate column names from fact table
888 x_fact_cols OUT NOCOPY VARCHAR2)
889 IS
890 BEGIN
892 CASE p_fact_code
893 -- for MARKET ITEM SALES DAY
894 WHEN ddr_webservices_constants.g_misd_cd THEN
895 x_fact_cols:='sum(x.AVG_MMACV_SLS_RATE) AVG_MMACV_SLS_RATE,'
896 ||'sum(x.AVG_STORE_SELL_ITEM_QTY) AVG_STORE_SELL_ITEM_QTY,'
897 ||'sum(x.AVG_ACV_WGT_DSTRBTN_PCT) AVG_ACV_WGT_DSTRBTN_PCT,'
898 ||'sum(x.AVG_WGT_PRICE_RDCTN_PCT) AVG_WGT_PRICE_RDCTN_PCT,'
899 ||'sum(x.SLS_QTY) SLS_QTY,'
900 ||'sum(x.SLS_AMT) SLS_AMT,'
901 ||'sum(x.NRML_QTY) NRML_QTY,'
902 ||'sum(x.NRML_AMT) NRML_AMT,'
903 ||'sum(x.SLS_PRICE_CUT_QTY) SLS_PRICE_CUT_QTY,'
904 ||'sum(x.SLS_PRICE_CUT_AMT) SLS_PRICE_CUT_AMT,'
905 ||'sum(x.MAIN_AD_QTY) MAIN_AD_QTY,'
906 ||'sum(x.MAIN_AD_AMT) MAIN_AD_AMT';
907 --for PROMOTION PLAN
908 WHEN ddr_webservices_constants.g_pp_cd THEN
909 x_fact_cols:='sum(x.PRMTN_PRICE_AMT) PRMTN_PRICE_AMT';
910 -- for RETAIL INVENTORY ITEM DAY
911 WHEN ddr_webservices_constants.g_riid_cd THEN
912 x_fact_cols:= 'sum(x.ON_HAND_QTY) ON_HAND_QTY,'
913 ||'sum(x.RECVD_QTY) RECVD_QTY,'
914 ||'sum(x.IN_TRANSIT_QTY) IN_TRANSIT_QTY,'
915 ||'sum(x.BCK_ORDR_QTY) BCK_ORDR_QTY,'
916 ||'sum(x.QLTY_HOLD_QTY) QLTY_HOLD_QTY,'
917 ||'sum(x.ON_HAND_NET_COST_AMT) ON_HAND_NET_COST_AMT,'
918 ||'sum(x.RECVD_NET_COST_AMT) RECVD_NET_COST_AMT,'
919 ||'sum(x.IN_TRANSIT_NET_COST_AMT) IN_TRANSIT_NET_COST_AMT,'
920 ||'sum(x.BCKORDR_NET_COST_AMT) BCKORDR_NET_COST_AMT,'
921 ||'sum(x.QLTY_HOLD_NET_COST_AMT) QLTY_HOLD_NET_COST_AMT,'
922 ||'sum(x.ON_HAND_RTL_AMT) ON_HAND_RTL_AMT,'
923 ||'sum(x.RECVD_RTL_AMT) RECVD_RTL_AMT,'
924 ||'sum(x.IN_TRANSIT_RTL_AMT) IN_TRANSIT_RTL_AMT,'
925 ||'sum(x.BCKORDR_RTL_AMT) BCKORDR_RTL_AMT,'
926 ||'sum(x.QLTY_HOLD_RTL_AMT) QLTY_HOLD_RTL_AMT';
927 -- for RETAIL SALE RETURN ITEM DAY fact
928 WHEN ddr_webservices_constants.g_rsrid_cd THEN
929 x_fact_cols:='sum(x.SLS_QTY) SLS_QTY,'
930 ||'sum(x.SLS_AMT) SLS_AMT,'
931 ||'sum(x.SLS_COST_AMT) SLS_COST_AMT,'
932 ||'sum(x.RTRN_QTY) RTRN_QTY,'
933 ||'sum(x.RTRN_AMT) RTRN_AMT,'
934 ||'sum(x.RTRN_COST_AMT) RTRN_COST_AMT';
935 -- for RETAILER ORDER ITEM DAY
936 WHEN ddr_webservices_constants.g_roid_cd THEN
937 x_fact_cols:='sum(x.ORDR_QTY) ORDR_QTY,'
938 ||'sum(x.ORDR_AMT) ORDR_AMT';
939 -- for RETAILER SHIP ITEM DAY
940 WHEN ddr_webservices_constants.g_rsid_cd THEN
941 x_fact_cols:= 'sum(x.SHIP_QTY) SHIP_QTY,'
942 ||'sum(x.SHIP_AMT) SHIP_AMT';
943 -- for SALE FORECAST ITEM BY DAY
944 WHEN ddr_webservices_constants.g_sfid_cd THEN
945 x_fact_cols:= 'sum(x.FRCST_SLS_QTY) FRCST_SLS_QTY,'
946 ||'sum(x.FRCST_SLS_AMT) FRCST_SLS_AMT';
947 END CASE;
948 EXCEPTION
949 WHEN OTHERS THEN
950 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
951 x_msg_count:=1;
952 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
953 END get_aggr_fact_colms;
954
955 PROCEDURE get_detail_fact_colms(p_fact_code IN VARCHAR2,
956 x_return_status OUT NOCOPY VARCHAR2,
957 x_msg_count OUT NOCOPY NUMBER,
958 x_msg_data OUT NOCOPY VARCHAR2,
959 x_fact_cols OUT NOCOPY VARCHAR2)
960 IS
961 BEGIN
962 --case condition to identify aggregate column names from fact table
963 CASE p_fact_code
964 -- for MARKET ITEM SALES DAY
965 WHEN ddr_webservices_constants.g_misd_cd THEN
966 x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
967 ||'x.RTL_ORG_CD RTL_ORG_CD,'
968 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
969 ||'x.MKT_AREA_ID MKT_AREA_ID,'
970 ||'x.MKT_AREA_CD MKT_AREA_CD,'
971 ||'x.DAY_CD DAY_CD,'
972
973 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
974 ||'x.MKT_ITEM_ID MKT_ITEM_ID,'
975 ||'x.UOM_CD UOM_CD,'
976 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
977 ||'x.UOM_CD_ALT UOM_CD_ALT,'
978 ||'x.CRNCY_CD CRNCY_CD,'
979 ||'x.REC_CURR_DT REC_CURR_DT,'
980 ||'x.AVG_MMACV_SLS_RATE AVG_MMACV_SLS_RATE,'
981 ||'x.AVG_STORE_SELL_ITEM_QTY AVG_STORE_SELL_ITEM_QTY,'
982 ||'x.AVG_STORE_SELL_ITEM_QTY_PRMRY AVG_STORE_SELL_ITEM_QTY_PRMRY,'
983 ||'x.AVG_STORE_SELL_ITEM_QTY_ALT AVG_STORE_SELL_ITEM_QTY_ALT,'
984 ||'x.AVG_ACV_WGT_DSTRBTN_PCT AVG_ACV_WGT_DSTRBTN_PCT,'
985 ||'x.AVG_WGT_PRICE_RDCTN_PCT AVG_WGT_PRICE_RDCTN_PCT,'
986 ||'x.SLS_QTY SLS_QTY,'
987 ||'x.SLS_QTY_PRMRY SLS_QTY_PRMRY,'
988 ||'x.SLS_QTY_ALT SLS_QTY_ALT,'
989 ||'x.SLS_AMT SLS_AMT,'
990 ||'x.SLS_AMT_LCL SLS_AMT_LCL,'
991 ||'x.SLS_AMT_RPT SLS_AMT_RPT,'
992 ||'x.NRML_QTY NRML_QTY,'
993 ||'x.NRML_QTY_PRMRY NRML_QTY_PRMRY,'
994 ||'x.NRML_QTY_ALT NRML_QTY_ALT,'
995 ||'x.NRML_AMT NRML_AMT,'
996 ||'x.NRML_AMT_LCL NRML_AMT_LCL,'
997 ||'x.NRML_AMT_RPT NRML_AMT_RPT,'
998 ||'x.SLS_PRICE_CUT_QTY SLS_PRICE_CUT_QTY,'
999 ||'x.SLS_PRICE_CUT_QTY_PRMRY SLS_PRICE_CUT_QTY_PRMRY,'
1000 ||'x.SLS_PRICE_CUT_QTY_ALT SLS_PRICE_CUT_QTY_ALT,'
1001 ||'x.SLS_PRICE_CUT_AMT SLS_PRICE_CUT_AMT,'
1002 ||'x.SLS_PRICE_CUT_AMT_LCL SLS_PRICE_CUT_AMT_LCL,'
1003 ||'x.SLS_PRICE_CUT_AMT_RPT SLS_PRICE_CUT_AMT_RPT,'
1004 ||'x.MAIN_AD_QTY MAIN_AD_QTY,'
1005 ||'x.MAIN_AD_QTY_PRMRY MAIN_AD_QTY_PRMRY,'
1006 ||'x.MAIN_AD_QTY_ALT MAIN_AD_QTY_ALT,'
1010 --for PROMOTION PLAN
1007 ||'x.MAIN_AD_AMT MAIN_AD_AMT,'
1008 ||'x.MAIN_AD_AMT_LCL MAIN_AD_AMT_LCL,'
1009 ||'x.MAIN_AD_AMT_RPT MAIN_AD_AMT_RPT';
1011 --as there is a date range for promotion records, they would get selected for every day
1012 --therefore DISTINCT clause is added to ensure query doesn't return duplicates
1013 WHEN ddr_webservices_constants.g_pp_cd THEN
1014 x_fact_cols:='DISTINCT x.MFG_ORG_CD MFG_ORG_CD,'
1015 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1016 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1017
1018 ||'x.PRMTN_TYP PRMTN_TYP,'
1019 ||'x.PRMTN_FROM_DT PRMTN_FROM_DT,'
1020 ||'x.PRMTN_TO_DT PRMTN_TO_DT,'
1021 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1022 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1023 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1024 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1025 ||'x.CRNCY_CD CRNCY_CD,'
1026 ||'x.PRMTN_PRICE_AMT PRMTN_PRICE_AMT,'
1027 ||'x.PRMTN_PRICE_AMT_LCL PRMTN_PRICE_AMT_LCL,'
1028 ||'x.PRMTN_PRICE_AMT_RPT PRMTN_PRICE_AMT_RPT';
1029 -- for RETAIL INVENTORY ITEM DAY
1030 WHEN ddr_webservices_constants.g_riid_cd THEN
1031 x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1032 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1033 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1034
1035 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1036 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1037 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1038 ||'x.DAY_CD DAY_CD,'
1039 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1040 ||'x.INV_LOC_TYP_CD INV_LOC_TYP_CD,'
1041 ||'x.INV_LOC_ID INV_LOC_ID,'
1042 ||'x.INV_LOC_CD INV_LOC_CD,'
1043 ||'x.UOM_CD UOM_CD,'
1044 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
1045 ||'x.UOM_CD_ALT UOM_CD_ALT,'
1046 ||'x.CRNCY_CD CRNCY_CD,'
1047 ||'x.ON_HAND_QTY ON_HAND_QTY,'
1048 ||'x.ON_HAND_QTY_PRMRY ON_HAND_QTY_PRMRY,'
1049 ||'x.ON_HAND_QTY_ALT ON_HAND_QTY_ALT,'
1050 ||'x.RECVD_QTY RECVD_QTY,'
1051 ||'x.RECVD_QTY_PRMRY RECVD_QTY_PRMRY,'
1052 ||'x.RECVD_QTY_ALT RECVD_QTY_ALT,'
1053 ||'x.IN_TRANSIT_QTY IN_TRANSIT_QTY,'
1054 ||'x.IN_TRANSIT_QTY_PRMRY IN_TRANSIT_QTY_PRMRY,'
1055 ||'x.IN_TRANSIT_QTY_ALT IN_TRANSIT_QTY_ALT,'
1056 ||'x.BCK_ORDR_QTY BCK_ORDR_QTY,'
1057 ||'x.BCK_ORDR_QTY_PRMRY BCK_ORDR_QTY_PRMRY,'
1058 ||'x.BCK_ORDR_QTY_ALT BCK_ORDR_QTY_ALT,'
1059 ||'x.QLTY_HOLD_QTY QLTY_HOLD_QTY,'
1060 ||'x.QLTY_HOLD_QTY_PRMRY QLTY_HOLD_QTY_PRMRY,'
1061 ||'x.QLTY_HOLD_QTY_ALT QLTY_HOLD_QTY_ALT,'
1062 ||'x.ON_HAND_NET_COST_AMT ON_HAND_NET_COST_AMT,'
1063 ||'x.ON_HAND_NET_COST_AMT_LCL ON_HAND_NET_COST_AMT_LCL,'
1064 ||'x.ON_HAND_NET_COST_AMT_RPT ON_HAND_NET_COST_AMT_RPT,'
1065 ||'x.RECVD_NET_COST_AMT RECVD_NET_COST_AMT,'
1066 ||'x.RECVD_NET_COST_AMT_LCL RECVD_NET_COST_AMT_LCL,'
1067 ||'x.RECVD_NET_COST_AMT_RPT RECVD_NET_COST_AMT_RPT,'
1068 ||'x.IN_TRANSIT_NET_COST_AMT IN_TRANSIT_NET_COST_AMT,'
1069 ||'x.IN_TRANSIT_NET_COST_AMT_LCL IN_TRANSIT_NET_COST_AMT_LCL,'
1070 ||'x.IN_TRANSIT_NET_COST_AMT_RPT IN_TRANSIT_NET_COST_AMT_RPT,'
1071 ||'x.BCKORDR_NET_COST_AMT BCKORDR_NET_COST_AMT,'
1072 ||'x.BCKORDR_NET_COST_AMT_LCL BCKORDR_NET_COST_AMT_LCL,'
1073 ||'x.BCKORDR_NET_COST_AMT_RPT BCKORDR_NET_COST_AMT_RPT,'
1074 ||'x.QLTY_HOLD_NET_COST_AMT QLTY_HOLD_NET_COST_AMT,'
1075 ||'x.QLTY_HOLD_NET_COST_AMT_LCL QLTY_HOLD_NET_COST_AMT_LCL,'
1076 ||'x.QLTY_HOLD_NET_COST_AMT_RPT QLTY_HOLD_NET_COST_AMT_RPT,'
1077 ||'x.ON_HAND_RTL_AMT ON_HAND_RTL_AMT,'
1078 ||'x.ON_HAND_RTL_AMT_LCL ON_HAND_RTL_AMT_LCL,'
1079 ||'x.ON_HAND_RTL_AMT_RPT ON_HAND_RTL_AMT_RPT,'
1080 ||'x.RECVD_RTL_AMT RECVD_RTL_AMT,'
1081 ||'x.RECVD_RTL_AMT_LCL RECVD_RTL_AMT_LCL,'
1082 ||'x.RECVD_RTL_AMT_RPT RECVD_RTL_AMT_RPT,'
1083 ||'x.IN_TRANSIT_RTL_AMT IN_TRANSIT_RTL_AMT,'
1084 ||'x.IN_TRANSIT_RTL_AMT_LCL IN_TRANSIT_RTL_AMT_LCL,'
1085 ||'x.IN_TRANSIT_RTL_AMT_RPT IN_TRANSIT_RTL_AMT_RPT,'
1086 ||'x.BCKORDR_RTL_AMT BCKORDR_RTL_AMT,'
1087 ||'x.BCKORDR_RTL_AMT_LCL BCKORDR_RTL_AMT_LCL,'
1088 ||'x.BCKORDR_RTL_AMT_RPT BCKORDR_RTL_AMT_RPT,'
1089 ||'x.QLTY_HOLD_RTL_AMT QLTY_HOLD_RTL_AMT,'
1090 ||'x.QLTY_HOLD_RTL_AMT_LCL QLTY_HOLD_RTL_AMT_LCL,'
1091 ||'x.QLTY_HOLD_RTL_AMT_RPT QLTY_HOLD_RTL_AMT_RPT';
1092 -- for RETAIL SALE RETURN ITEM DAY fact
1093 WHEN ddr_webservices_constants.g_rsrid_cd THEN
1094 x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1095 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1096 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1097
1098 ||'x.DAY_CD DAY_CD,'
1099 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1100 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1101 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1102 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1103 ||'x.UOM_CD UOM_CD,'
1104 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
1105 ||'x.UOM_CD_ALT UOM_CD_ALT,'
1106 ||'x.CRNCY_CD CRNCY_CD,'
1107 ||'x.SLS_QTY SLS_QTY,'
1108 ||'x.SLS_QTY_PRMRY SLS_QTY_PRMRY,'
1109 ||'x.SLS_QTY_ALT SLS_QTY_ALT,'
1110 ||'x.SLS_AMT SLS_AMT,'
1111 ||'x.SLS_AMT_LCL SLS_AMT_LCL,'
1112 ||'x.SLS_AMT_RPT SLS_AMT_RPT,'
1113 ||'x.SLS_COST_AMT SLS_COST_AMT,'
1114 ||'x.SLS_COST_AMT_LCL SLS_COST_AMT_LCL,'
1115 ||'x.SLS_COST_AMT_RPT SLS_COST_AMT_RPT,'
1116 ||'x.RTRN_QTY RTRN_QTY,'
1117 ||'x.RTRN_QTY_PRMRY RTRN_QTY_PRMRY,'
1118 ||'x.RTRN_QTY_ALT RTRN_QTY_ALT,'
1119 ||'x.RTRN_AMT RTRN_AMT,'
1120 ||'x.RTRN_AMT_LCL RTRN_AMT_LCL,'
1121 ||'x.RTRN_AMT_RPT RTRN_AMT_RPT,'
1122 ||'x.RTRN_COST_AMT RTRN_COST_AMT,'
1123 ||'x.RTRN_COST_AMT_LCL RTRN_COST_AMT_LCL,'
1124 ||'x.RTRN_COST_AMT_RPT RTRN_COST_AMT_RPT';
1125 -- for RETAILER ORDER ITEM DAY
1126 WHEN ddr_webservices_constants.g_roid_cd THEN
1127 x_fact_cols:='x.MFG_ORG_CD MFG_ORG_CD,'
1128 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1129 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1130
1131 ||'x.DAY_CD DAY_CD,'
1132 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1133 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1134 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1135 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1136 ||'x.UOM_CD UOM_CD,'
1137 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
1138 ||'x.UOM_CD_ALT UOM_CD_ALT,'
1139 ||'x.CRNCY_CD CRNCY_CD,'
1140 ||'x.ORDR_QTY ORDR_QTY,'
1141 ||'x.ORDR_QTY_PRMRY ORDR_QTY_PRMRY,'
1142 ||'x.ORDR_QTY_ALT ORDR_QTY_ALT,'
1143 ||'x.ORDR_AMT ORDR_AMT,'
1144 ||'x.ORDR_AMT_LCL ORDR_AMT_LCL,'
1145 ||'x.ORDR_AMT_RPT ORDR_AMT_RPT';
1146 -- for RETAILER SHIP ITEM DAY
1147 WHEN ddr_webservices_constants.g_rsid_cd THEN
1148 x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1149 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1150 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1151
1152 ||'x.DAY_CD DAY_CD,'
1153 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1154 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1155 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1156 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1157 ||'x.UOM_CD UOM_CD,'
1158 ||'x.UOM_CD_PRMRY UOM_CD_PRMRY,'
1159 ||'x.UOM_CD_ALT UOM_CD_ALT,'
1160 ||'x.CRNCY_CD CRNCY_CD,'
1161 ||'x.SHIP_QTY SHIP_QTY,'
1162 ||'x.SHIP_QTY_PRMRY SHIP_QTY_PRMRY,'
1163 ||'x.SHIP_QTY_ALT SHIP_QTY_ALT,'
1164 ||'x.SHIP_AMT SHIP_AMT,'
1165 ||'x.SHIP_AMT_LCL SHIP_AMT_LCL,'
1166 ||'x.SHIP_AMT_RPT SHIP_AMT_RPT';
1167 -- for SALE FORECAST ITEM BY DAY
1168 WHEN ddr_webservices_constants.g_sfid_cd THEN
1169 x_fact_cols:= 'x.MFG_ORG_CD MFG_ORG_CD,'
1170 ||'x.RTL_ORG_CD RTL_ORG_CD,'
1171 ||'x.FRCST_NBR FRCST_NBR,'
1172 ||'x.FRCST_TYP FRCST_TYP,'
1173 ||'x.FRCST_VRSN FRCST_VRSN,'
1174 ||'x.ORG_BSNS_UNIT_ID ORG_BSNS_UNIT_ID,'
1175
1176 ||'x.DAY_CD DAY_CD,'
1177 ||'x.GLBL_ITEM_ID GLBL_ITEM_ID,'
1178 ||'x.RTL_SKU_ITEM_ID RTL_SKU_ITEM_ID,'
1179 ||'x.RTL_SKU_ITEM_NBR RTL_SKU_ITEM_NBR,'
1180 ||'x.GLBL_ITEM_ID_TYP GLBL_ITEM_ID_TYP,'
1181 ||'x.FRCST_SLS_UOM_CD FRCST_SLS_UOM_CD,'
1182 ||'x.FRCST_SLS_UOM_CD_PRMRY FRCST_SLS_UOM_CD_PRMRY,'
1183 ||'x.FRCST_SLS_UOM_CD_ALT FRCST_SLS_UOM_CD_ALT,'
1184 ||'x.CRNCY_CD CRNCY_CD,'
1185 ||'x.FRCST_SLS_QTY FRCST_SLS_QTY,'
1186 ||'x.FRCST_SLS_QTY_PRMRY FRCST_SLS_QTY_PRMRY,'
1187 ||'x.FRCST_SLS_QTY_ALT FRCST_SLS_QTY_ALT,'
1188 ||'x.FRCST_SLS_AMT FRCST_SLS_AMT,'
1189 ||'x.FRCST_SLS_AMT_LCL FRCST_SLS_AMT_LCL,'
1190 ||'x.FRCST_SLS_AMT_RPT FRCST_SLS_AMT_RPT';
1191 END CASE;
1192 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1193 EXCEPTION
1194 WHEN OTHERS THEN
1195 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1196 x_msg_count:=1;
1197 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1198 END get_detail_fact_colms;
1199
1200 PROCEDURE get_aggr_group_colms(p_exp_org_level IN VARCHAR2,
1201 p_exp_loc_level IN VARCHAR2,
1202 p_exp_item_level IN VARCHAR2,
1203 p_exp_time_level IN VARCHAR2,
1204 x_return_status OUT NOCOPY VARCHAR2,
1205 x_msg_count OUT NOCOPY NUMBER,
1206 x_msg_data OUT NOCOPY VARCHAR2,
1207 x_group_col OUT NOCOPY VARCHAR2)
1208 IS
1209 l_group_col VARCHAR2(500):=null;
1210 l_ref_join VARCHAR2(100):=null;
1211 l_lvl_rnk NUMBER:=null;
1212 l_hrchy_lvl_name VARCHAR2(50):=null;
1213 BEGIN
1214 --get aggregate columns for the Organization hierarchy based on given hierarchy level code
1215 IF p_exp_org_level IS NOT NULL THEN
1216 get_hrchy_lvl('ORGANIZATION',p_exp_org_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1217 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1218 RETURN;
1219 END IF;
1220 get_org_ref_join(l_lvl_rnk,l_group_col,x_return_status, x_msg_count,x_msg_data);
1221 END IF;
1222 --get aggregate columns for the location hierarchy based on given hierarchy level code
1223 IF p_exp_loc_level IS NOT NULL THEN
1224 get_hrchy_lvl('LOCATION',p_exp_loc_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1225 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1226 RETURN;
1227 END IF;
1228 get_loc_ref_join(l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1229 IF l_group_col IS NOT NUll THEN
1230 l_group_col :=l_group_col ||','||l_ref_join;
1231 ELSIF l_group_col IS NUll THEN
1232 l_group_col :=l_ref_join;
1233 END IF;
1234 END IF;
1235 --get aggregate columns for the Item hierarchy based on given hierarchy level code
1236 IF p_exp_item_level IS NOT NULL THEN
1237 get_hrchy_lvl('ITEM',p_exp_item_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1238 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1239 RETURN;
1240 END IF;
1241 get_item_ref_join(l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1242 IF l_group_col IS NOT NUll THEN
1243 l_group_col :=l_group_col ||','||l_ref_join;
1244 ELSIF l_group_col IS NUll THEN
1245 l_group_col :=l_ref_join;
1246 END IF;
1247 END IF;
1248 --get aggregate columns for the time hierarchy based on given hierarchy level code
1249 IF p_exp_time_level IS NOT NULL THEN
1250 SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE hrchy_lvl_cd=p_exp_time_level;
1251 get_hrchy_lvl(l_hrchy_lvl_name,p_exp_time_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1252 IF x_return_status<>ddr_webservices_constants.g_ret_sts_success THEN
1253 RETURN;
1254 END IF;
1255 get_time_ref_join(l_hrchy_lvl_name,l_lvl_rnk,l_ref_join,x_return_status, x_msg_count,x_msg_data);
1256 IF l_group_col IS NOT NUll THEN
1257 l_group_col :=l_group_col ||','||l_ref_join;
1258 ELSIF l_group_col IS NUll THEN
1259 l_group_col :=l_ref_join;
1260 END IF;
1261 END IF;
1262 x_group_col:= l_group_col;
1263 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1264 EXCEPTION
1265 WHEN NO_DATA_FOUND THEN
1266 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1267 x_msg_count:=1;
1268 x_msg_data:='No Data Found. Error code:'||sqlcode||' Error message:'||sqlerrm;
1269 WHEN OTHERS THEN
1270 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1271 x_msg_count:=1;
1272 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1273 END get_aggr_group_colms;
1274
1275 PROCEDURE write_fact_to_xml_file(p_query IN VARCHAR2,
1276 p_fact_code IN VARCHAR2,
1277 p_job_id IN NUMBER,
1278 x_return_status OUT NOCOPY VARCHAR2,
1279 x_msg_count OUT NOCOPY NUMBER,
1280 x_msg_data OUT NOCOPY VARCHAR2)
1281 IS
1282 l_cur NUMBER:=null;
1283 l_dtbl DBMS_SQL.desc_tab;
1284 l_cnt NUMBER;
1285 l_status NUMBER;
1286 l_val VARCHAR2(200);
1287 l_col_name VARCHAR2(200);
1288 l_xml_file UTL_FILE.file_type;
1289 l_fetch_ctn NUMBER:=0;
1290 l_fetch_rows NUMBER:=0;
1291 l_file_name VARCHAR2(30):=null;
1292 l_total_row_count NUMBER:=0;
1293 l_dir_name VARCHAR2(30):=null;
1294 l_pagination_row_count NUMBER:=null;
1295 l_max_row_count NUMBER:=null;
1296 l_fact VARCHAR2(32767):=null;
1297 l_file_id VARCHAR2(100):= NULL;
1298 l_max_rows EXCEPTION;
1299 BEGIN
1300 --get logical directory name from system variable table
1301 ddr_webservices_pub.get_sys_var_val('OUTPUT_DIR_PATH',x_return_status, x_msg_count,x_msg_data,l_dir_name);
1302 --get pagination row count from system variable table
1303 ddr_webservices_pub.get_sys_var_val('MAX_REC_PER_FILE',x_return_status, x_msg_count,x_msg_data,l_pagination_row_count);
1304 --get threashold value for the maximum records for which files can be created
1305 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);
1306 -- DBMS_OUTPUT.PUT_LINE('l_max_row_count='||l_max_row_count);
1307 l_pagination_row_count:=to_number(l_pagination_row_count);
1308 --get file id
1309 l_file_id :=get_ddr_ws_file_seq_nextval(x_return_status,x_msg_count,x_msg_data);
1310 --construct file name
1311 l_file_name := p_fact_code || '_'|| l_file_id || '.xml';
1312 --open file in write mode
1313 l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);
1314 --write xml header data in the xml file
1315 UTL_FILE.put_line(l_xml_file, '<?xml version="1.0" encoding="UTF-8"?>');
1316 --open cursor
1317 l_cur := dbms_sql.open_cursor;
1318 dbms_sql.parse(l_cur,p_query,dbms_sql.native);
1319 l_status := dbms_sql.execute(l_cur);
1320 --to column defination from the cursor
1321 dbms_sql.describe_columns(l_cur,l_cnt,l_dtbl);
1322 FOR i in 1..l_cnt LOOP
1323 dbms_sql.define_column(l_cur,i,l_val,30);
1324 END LOOP;
1325 UTL_FILE.put_line(l_xml_file, '<FACT_DATA>');
1326 l_fetch_rows:=dbms_sql.fetch_rows(l_cur);
1327 WHILE ( l_fetch_rows > 0 ) LOOP
1328 l_fetch_ctn:=l_fetch_ctn+l_fetch_rows;
1329 UTL_FILE.put_line(l_xml_file, '<FACT>');
1330 l_fact:=null;
1331 --write individual row in the xml file
1332 FOR i in 1..l_cnt loop
1333 l_col_name:= l_dtbl(i).col_name;
1334 dbms_sql.column_value(l_cur,i,l_val);
1335 -- l_fact:=l_fact||'<'||l_col_name||'>'||l_val||'</'||l_col_name||'>'||chr(10);
1339 END LOOP;
1336 -- Use of chr function not allowed by GSCC. The new line character is introduced using line edit
1337 l_fact:=l_fact||'<'||l_col_name||'>'||l_val||'</'||l_col_name||'>'||'
1338 '||NULL;
1340 -- UTL_FILE.put_line(l_xml_file, l_fact||chr(10)||'</FACT>');
1341 -- Use of chr function not allowed by GSCC. The new line character is introduced using line edit
1342 UTL_FILE.put_line(l_xml_file, l_fact||'
1343 '||'</FACT>');
1344 IF l_fetch_ctn = l_pagination_row_count THEN
1345 UTL_FILE.put_line(l_xml_file,'</FACT_DATA>');
1346 UTL_FILE.FCLOSE(l_xml_file);
1347 --update the job file metadata table with the file name
1348 BEGIN
1349 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,
1350 created_by, creation_date, last_updated_by,
1351 last_update_date, last_update_login)
1352 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,
1353 SYSDATE, 101);
1354 END;
1355 l_fetch_rows:=dbms_sql.fetch_rows(l_cur);
1356 IF(l_fetch_rows > 0) THEN
1357 IF l_total_row_count >=l_max_row_count THEN
1358 RAISE l_max_rows;
1359 END IF;
1360 l_file_id :=get_ddr_ws_file_seq_nextval(x_return_status,x_msg_count,x_msg_data);
1361 l_file_name := p_fact_code || '_'|| l_file_id || '.xml';
1362 l_xml_file := UTL_FILE.fopen(l_dir_name,l_file_name,ddr_webservices_constants.g_file_write_mode);
1363 UTL_FILE.put_line(l_xml_file, '<?xml version="1.0" encoding="UTF-8"?>');
1364 UTL_FILE.put_line(l_xml_file, '<FACT_DATA>');
1365 l_fetch_ctn:=0;
1366 END IF;
1367 ELSE
1368 l_fetch_rows:=dbms_sql.fetch_rows(l_cur);
1369 END IF;
1370 l_total_row_count:=l_total_row_count+1;
1371 END LOOP;
1372 -- DBMS_OUTPUT.PUT_LINE('l_total_row_count='||l_total_row_count);
1373 dbms_sql.close_cursor(l_cur);
1374 IF l_fetch_ctn <> l_pagination_row_count THEN
1375 UTL_FILE.put_line(l_xml_file, '</FACT_DATA>');
1376 UTL_FILE.FCLOSE(l_xml_file);
1377 BEGIN
1378 -- DBMS_OUTPUT.PUT_LINE('third, l_file_id='||l_file_id);
1379 INSERT INTO ddr_ws_job_file_dls
1380 (file_id, job_id, file_name, status, delete_flag,
1381 src_sys_idnt, src_sys_dt, crtd_by_dsr, last_updt_by_dsr,
1382 created_by, creation_date, last_updated_by,
1383 last_update_date, last_update_login
1384 )
1385 VALUES (l_file_id, p_job_id, l_file_name, ddr_webservices_constants.g_ret_sts_success, 'N',
1386 'ABC', SYSDATE, 'ABC', 'ABC',101, SYSDATE, 101,
1387 SYSDATE, 101);
1388 END;
1389 END IF;
1390 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1391 EXCEPTION
1392 WHEN l_max_rows THEN
1393 IF dbms_sql.is_open(l_cur) THEN
1394 dbms_sql.close_cursor(l_cur);
1395 END IF;
1396 IF UTL_FILE.is_open(l_xml_file) THEN
1397 UTL_FILE.fclose(l_xml_file);
1398 END IF;
1399 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1400 x_msg_count:=1;
1401 x_msg_data:='Program exceeded maximum row OUT NOCOPY put';
1402 WHEN UTL_FILE.INTERNAL_ERROR THEN
1403 IF dbms_sql.is_open(l_cur) THEN
1404 dbms_sql.close_cursor(l_cur);
1405 END IF;
1406 IF UTL_FILE.is_open(l_xml_file) THEN
1407 UTL_FILE.fclose(l_xml_file);
1408 END IF;
1409 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1410 x_msg_count:=1;
1411 x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1412 WHEN UTL_FILE.INVALID_OPERATION THEN
1413 IF dbms_sql.is_open(l_cur) THEN
1414 dbms_sql.close_cursor(l_cur);
1415 END IF;
1416 IF UTL_FILE.is_open(l_xml_file) THEN
1417 UTL_FILE.fclose(l_xml_file);
1418 END IF;
1419 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1420 x_msg_count:=1;
1421 x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1422 WHEN UTL_FILE.INVALID_PATH THEN
1423 IF dbms_sql.is_open(l_cur) THEN
1424 dbms_sql.close_cursor(l_cur);
1425 END IF;
1426 IF UTL_FILE.is_open(l_xml_file) THEN
1427 UTL_FILE.fclose(l_xml_file);
1428 END IF;
1429 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1430 x_msg_count:=1;
1431 x_msg_data:='Cannot open file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1432 WHEN UTL_FILE.WRITE_ERROR THEN
1433 IF dbms_sql.is_open(l_cur) THEN
1434 dbms_sql.close_cursor(l_cur);
1435 END IF;
1436 IF UTL_FILE.is_open(l_xml_file) THEN
1437 UTL_FILE.fclose(l_xml_file);
1438 END IF;
1439 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1440 x_msg_count:=1;
1441 x_msg_data:='Cannot write to file :' || l_file_name ||', write error; code:' || sqlcode ||',message:' || sqlerrm;
1442 WHEN OTHERS THEN
1443 IF dbms_sql.is_open(l_cur) THEN
1444 dbms_sql.close_cursor(l_cur);
1445 END IF;
1446 IF UTL_FILE.is_open(l_xml_file) THEN
1447 UTL_FILE.fclose(l_xml_file);
1448 END IF;
1449 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1450 x_msg_count:=1;
1454 PROCEDURE validate_input_params(p_api_version IN NUMBER,
1451 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1452 END write_fact_to_xml_file;
1453
1455 p_mfg_org_cd IN VARCHAR2,
1456 p_org_cd IN VARCHAR2,
1457 p_org_dim_lvl_cd IN VARCHAR2,
1458 p_org_lvl_val IN VARCHAR2,
1459 p_exp_org_level IN VARCHAR2,
1460 p_loc_dim_lvl_cd IN VARCHAR2,
1461 p_loc_lvl_val IN VARCHAR2,
1462 p_exp_loc_level IN VARCHAR2,
1463 p_item_dim_lvl_cd IN VARCHAR2,
1464 p_item_lvl_val IN VARCHAR2,
1465 p_exp_item_level IN VARCHAR2,
1466 p_time_dim_lvl_cd IN VARCHAR2,
1467 p_time_lvl_val IN VARCHAR2,
1468 p_exp_time_level IN VARCHAR2,
1469 p_fact_code IN VARCHAR2,
1470 x_return_status OUT NOCOPY VARCHAR2,
1471 x_msg_count OUT NOCOPY NUMBER,
1472 x_msg_data OUT NOCOPY VARCHAR2)
1473 IS
1474 l_api_ver EXCEPTION;
1475 l_fact_code_null EXCEPTION;
1476 l_mfg_code_null EXCEPTION;
1477 l_rtl_org_code_null EXCEPTION;
1478 l_pp_invld_aggr EXCEPTION;
1479 l_hrchy_cd VARCHAR2(50):=NULL;
1480 BEGIN
1481 IF p_api_version IS NULL THEN
1482 RAISE l_api_ver;
1483 END IF;
1484 IF p_api_version<>ddr_webservices_constants.g_api_version THEN
1485 RAISE l_api_ver;
1486 END IF;
1487 IF p_fact_code IS NULL THEN
1488 RAISE l_fact_code_null;
1489 END IF;
1490 IF p_mfg_org_cd IS NULL THEN
1491 RAISE l_mfg_code_null;
1492 END IF;
1493 IF p_org_cd IS NULL THEN
1494 RAISE l_rtl_org_code_null;
1495 END IF;
1496 IF p_fact_code = ddr_webservices_constants.g_pp_cd AND p_exp_time_level IS NOT NULL THEN
1497 RAISE l_pp_invld_aggr;
1498 END IF;
1499 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1500 EXCEPTION
1501 WHEN l_api_ver THEN
1502 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1503 x_msg_count:=1;
1504 x_msg_data:='API version number should not be null';
1505 WHEN l_fact_code_null THEN
1506 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1507 x_msg_count:=1;
1508 x_msg_data:='Fact code should not be null';
1509 WHEN l_mfg_code_null THEN
1510 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1511 x_msg_count:=1;
1512 x_msg_data:='Manufacturer Organization code should not be null';
1513 WHEN l_rtl_org_code_null THEN
1514 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1515 x_msg_count:=1;
1516 x_msg_data:='Retailer Organization code should not be null';
1517 WHEN l_pp_invld_aggr THEN
1518 x_return_status:=ddr_webservices_constants.g_ret_sts_error;
1519 x_msg_count:=1;
1520 x_msg_data:='Time based aggregation is not supported for Promotion Plan data';
1521 WHEN OTHERS THEN
1522 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1523 x_msg_count:=1;
1524 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1525 END validate_input_params;
1526
1527 PROCEDURE get_sys_var_val(p_sys_var IN VARCHAR2,
1528 x_return_status OUT NOCOPY VARCHAR2,
1529 x_msg_count OUT NOCOPY NUMBER,
1530 x_msg_data OUT NOCOPY VARCHAR2,
1531 x_sys_var_val OUT NOCOPY VARCHAR2)
1532 IS
1533 BEGIN
1534 SELECT lkup_name INTO x_sys_var_val FROM ddr_r_lkup_mst WHERE lkup_cd=p_sys_var;
1535 x_return_status:=ddr_webservices_constants.g_ret_sts_success;
1536 EXCEPTION
1537 WHEN NO_DATA_FOUND THEN
1538 x_return_status := ddr_webservices_constants.g_ret_sts_error;
1539 x_msg_count := 1;
1540 x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1541 WHEN OTHERS THEN
1542 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1543 x_msg_count:=1;
1544 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1545 END get_sys_var_val;
1546
1547 FUNCTION get_ddr_ws_file_seq_nextval(x_return_status OUT NOCOPY VARCHAR2,
1548 x_msg_count OUT NOCOPY NUMBER,
1549 x_msg_data OUT NOCOPY VARCHAR2) RETURN VARCHAR2
1550 IS
1551 l_next_val NUMBER:=null;
1552 BEGIN
1553 SELECT DDR_WS_FILE_SEQ.NEXTVAL INTO l_next_val FROM dual;
1554 RETURN to_char(l_next_val);
1555 EXCEPTION
1556 WHEN NO_DATA_FOUND THEN
1557 x_return_status := ddr_webservices_constants.g_ret_sts_error;
1558 x_msg_count := 1;
1559 x_msg_data := 'No Data Found. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1560 WHEN OTHERS THEN
1561 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1562 x_msg_count:=1;
1563 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1567 p_item_lvl_val IN VARCHAR2,
1564 END get_ddr_ws_file_seq_nextval;
1565
1566 PROCEDURE get_itm_hrchy_clauses(p_item_dim_lvl_cd IN VARCHAR2,
1568 p_exp_item_level IN VARCHAR2,
1569 p_fact_code IN VARCHAR2,
1570 x_return_status OUT NOCOPY VARCHAR2,
1571 x_msg_count OUT NOCOPY VARCHAR2,
1572 x_msg_data OUT NOCOPY VARCHAR2,
1573 x_itm_ref_tbls OUT NOCOPY VARCHAR2,
1574 x_itm_ref_joins OUT NOCOPY VARCHAR2,
1575 x_itm_where_clus OUT NOCOPY VARCHAR2)
1576 IS
1577 l_lvl_rnk NUMBER:=null;
1578 BEGIN
1579 -- CASE p_fact_code
1580 -- for RETAIL SALE RETURN ITEM DAY fact
1581 --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1582
1583 IF p_item_dim_lvl_cd IS NOT NULL THEN
1584 get_hrchy_lvl('ITEM',p_item_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1585 ELSIF p_exp_item_level IS NOT NULL THEN
1586 get_hrchy_lvl('ITEM',p_exp_item_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1587 END IF;
1588 IF l_lvl_rnk IS NULL THEN
1589 RETURN;
1590 END IF;
1591 IF l_lvl_rnk>=1 THEN
1592 x_itm_ref_tbls:=',DDR_R_MFG_SKU_ITEM ITMA';
1593 x_itm_ref_joins:='AND x.GLBL_ITEM_ID = ITMA.GLBL_ITEM_ID';
1594 END IF;
1595 IF l_lvl_rnk>=2 THEN
1596 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM ITMB';
1597 x_itm_ref_joins:=x_itm_ref_joins||' AND ITMA.MFG_ITEM_ID = ITMB.MFG_ITEM_ID';
1598 END IF;
1599 IF l_lvl_rnk>=3 THEN
1600 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM_SBC ITMC';
1601 x_itm_ref_joins:=x_itm_ref_joins||' AND ITMB.MFG_ITEM_SBC_ID = ITMC.MFG_ITEM_SBC_ID';
1602 END IF;
1603 IF l_lvl_rnk>=4 THEN
1604 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM_CLASS ITMD';
1605 x_itm_ref_joins:=x_itm_ref_joins||' AND ITMC.MFG_ITEM_CLASS_ID = ITMD.MFG_ITEM_CLASS_ID';
1606 END IF;
1607 IF l_lvl_rnk>=5 THEN
1608 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM_GRP ITME';
1609 x_itm_ref_joins:=x_itm_ref_joins||' AND ITMD.MFG_ITEM_GRP_ID = ITME.MFG_ITEM_GRP_ID';
1610 END IF;
1611 IF l_lvl_rnk>=6 THEN
1612 x_itm_ref_tbls:=x_itm_ref_tbls || ',DDR_R_MFG_ITEM_DIV ITMF';
1613 x_itm_ref_joins:=x_itm_ref_joins||' AND ITME.MFG_ITEM_DIV_ID = ITMF.MFG_ITEM_DIV_ID';
1614 END IF;
1615 IF p_item_dim_lvl_cd IS NOT NULL THEN
1616 get_item_ref_join(l_lvl_rnk,x_itm_where_clus,x_return_status,x_msg_count,x_msg_data);
1617 x_itm_where_clus:= x_itm_where_clus || '=''' || p_item_lvl_val||'''';
1618 END IF;
1619 -- END CASE;
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1623 x_msg_count:=1;
1624 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1625 END get_itm_hrchy_clauses;
1626
1627 PROCEDURE get_item_ref_join(p_lvl_rnk IN NUMBER,
1628 x_ref_join OUT NOCOPY VARCHAR2,
1629 x_return_status OUT NOCOPY VARCHAR2,
1630 x_msg_count OUT NOCOPY VARCHAR2,
1631 x_msg_data OUT NOCOPY VARCHAR2)
1632 IS
1633 BEGIN
1634 CASE p_lvl_rnk
1635 WHEN 1 THEN
1636 x_ref_join:=' ITMA.MFG_SKU_ITEM_NBR';
1637 WHEN 2 THEN
1638 x_ref_join:=' ITMB.MFG_ITEM_NBR ';
1639 WHEN 3 THEN
1640 x_ref_join:=' ITMC.MFG_SBC_CD ';
1641 WHEN 4 THEN
1642 x_ref_join:=' ITMD.MFG_CLASS_CD ';
1643 WHEN 5 THEN
1644 x_ref_join:=' ITME.MFG_GRP_CD ';
1645 WHEN 6 THEN
1646 x_ref_join:=' ITMF.MFG_DIV_CD ';
1647 END CASE;
1648 EXCEPTION
1649 WHEN OTHERS THEN
1650 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1651 x_msg_count:=1;
1652 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1653
1654 END get_item_ref_join;
1655
1656
1657 PROCEDURE get_org_hrchy_clauses(p_org_dim_lvl_cd IN VARCHAR2,
1658 p_org_lvl_val IN VARCHAR2,
1659 p_exp_org_level IN VARCHAR2,
1660 p_fact_code IN VARCHAR2,
1661 x_return_status OUT NOCOPY VARCHAR2,
1662 x_msg_count OUT NOCOPY VARCHAR2,
1663 x_msg_data OUT NOCOPY VARCHAR2,
1664 x_org_ref_tbls OUT NOCOPY VARCHAR2,
1665 x_org_ref_joins OUT NOCOPY VARCHAR2,
1666 x_org_where_clus OUT NOCOPY VARCHAR2)
1667 IS
1668 l_lvl_rnk NUMBER:=null;
1669 BEGIN
1670 -- CASE p_fact_code
1671 -- for RETAIL SALE RETURN ITEM DAY fact
1672 --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1673 IF p_org_dim_lvl_cd IS NOT NULL THEN
1674 get_hrchy_lvl('ORGANIZATION',p_org_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1675 ELSIF p_exp_org_level IS NOT NULL THEN
1676 get_hrchy_lvl('ORGANIZATION',p_exp_org_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1677 END IF;
1678 IF l_lvl_rnk IS NULL THEN
1679 RETURN;
1680 END IF;
1681 IF l_lvl_rnk>=1 THEN
1682 x_org_ref_tbls:=',DDR_R_ORG_BSNS_UNIT ORGA';
1686 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG_DSTRCT ORGB';
1683 x_org_ref_joins:=' AND X.ORG_BSNS_UNIT_ID = ORGA.ORG_BSNS_UNIT_ID';
1684 END IF;
1685 IF l_lvl_rnk>=2 THEN
1687 x_org_ref_joins:=x_org_ref_joins||' AND ORGA.ORG_DSTRCT_ID = ORGB.ORG_DSTRCT_ID';
1688 END IF;
1689 IF l_lvl_rnk>=3 THEN
1690 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG_RGN ORGC';
1691 x_org_ref_joins:=x_org_ref_joins||' AND ORGB.ORG_RGN_ID = ORGC.ORG_RGN_ID';
1692 END IF;
1693 IF l_lvl_rnk>=4 THEN
1694 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG_AREA ORGD';
1695 x_org_ref_joins:=x_org_ref_joins||' AND ORGC.ORG_AREA_ID = ORGD.ORG_AREA_ID';
1696 END IF;
1697 IF l_lvl_rnk>=5 THEN
1698 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG_CHAIN ORGE';
1699 x_org_ref_joins:=x_org_ref_joins||' AND ORGD.ORG_CHAIN_ID = ORGE.ORG_CHAIN_ID';
1700 END IF;
1701 IF l_lvl_rnk>=6 THEN
1702 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_ORG ORGF';
1703 --x_itm_ref_joins:=x_itm_ref_joins||' ';
1704 END IF;
1705 IF l_lvl_rnk>=7 THEN
1706 x_org_ref_tbls:=x_org_ref_tbls || ',DDR_R_RTL_CLSTR ORGG,DDR_R_RTL_CLSTR_RTL_ASSC ORGH';
1707 x_org_ref_joins:=x_org_ref_joins||' AND ORGE.ORG_CD = ORGH.RTL_ORG_CD';
1708 END IF;
1709 IF p_org_dim_lvl_cd IS NOT NULL THEN
1710 get_org_ref_join(l_lvl_rnk,x_org_where_clus,x_return_status,x_msg_count,x_msg_data);
1711 x_org_where_clus:= x_org_where_clus || '=''' || p_org_lvl_val||'''';
1712 END IF;
1713 -- END CASE;
1714 EXCEPTION
1715 WHEN OTHERS THEN
1716 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1717 x_msg_count:=1;
1718 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1719 END get_org_hrchy_clauses;
1720
1721 PROCEDURE get_org_ref_join(p_lvl_rnk IN NUMBER,
1722 x_ref_join OUT NOCOPY VARCHAR2,
1723 x_return_status OUT NOCOPY VARCHAR2,
1724 x_msg_count OUT NOCOPY VARCHAR2,
1725 x_msg_data OUT NOCOPY VARCHAR2)
1726 IS
1727 BEGIN
1728 CASE p_lvl_rnk
1729 --bug 6921259 change start
1730 WHEN 0 THEN
1731 x_ref_join:=' X.INV_LOC_CD ';
1732 --bug 6921259 change end
1733 WHEN 1 THEN
1734 x_ref_join:=' ORGA.BSNS_UNIT_CD ';
1735 WHEN 5 THEN
1736 x_ref_join:=' ORGE.CHAIN_CD ';
1737 WHEN 6 THEN
1738 x_ref_join:=' ORGF.ORG_CD ';
1739 WHEN 7 THEN
1740 x_ref_join:=' ORGG.CLSTR_CD ';
1741 END CASE;
1742 EXCEPTION
1743 WHEN OTHERS THEN
1744 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1745 x_msg_count:=1;
1746 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1747 END get_org_ref_join;
1748
1749 PROCEDURE get_time_hrchy_clauses(p_time_dim_lvl_cd IN VARCHAR2,
1750 p_time_lvl_val IN VARCHAR2,
1751 p_exp_time_level IN VARCHAR2,
1752 p_fact_code IN VARCHAR2,
1753 p_org_cd IN VARCHAR2,
1754 x_return_status OUT NOCOPY VARCHAR2,
1755 x_msg_count OUT NOCOPY VARCHAR2,
1756 x_msg_data OUT NOCOPY VARCHAR2,
1757 x_time_ref_tbls OUT NOCOPY VARCHAR2,
1758 x_time_ref_joins OUT NOCOPY VARCHAR2,
1759 x_time_where_clus OUT NOCOPY VARCHAR2)
1760 IS
1761 l_lvl_rnk NUMBER:=null;
1762 l_hrchy_lvl_name VARCHAR2(50):=null;
1763 BEGIN
1764 -- CASE p_fact_code
1765 -- for RETAIL SALE RETURN ITEM DAY fact
1766 --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1767 --to get the time hierarchy level name(GREGORIAN TIME OR BUSINESS TIME)
1768 IF p_time_dim_lvl_cd IS NOT NULL THEN
1769 SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE
1770 hrchy_lvl_cd=p_time_dim_lvl_cd;
1771 get_hrchy_lvl(l_hrchy_lvl_name,p_time_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1772 ELSIF p_exp_time_level IS NOT NULL THEN
1773 SELECT hrchy_lvl_name INTO l_hrchy_lvl_name FROM DDR_WS_METADATA WHERE
1774 hrchy_lvl_cd=p_exp_time_level;
1775 get_hrchy_lvl(l_hrchy_lvl_name,p_exp_time_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1776 END IF;
1777 IF l_lvl_rnk IS NULL THEN
1778 RETURN;
1779 END IF;
1780 CASE l_hrchy_lvl_name
1781 WHEN 'GREGORIAN TIME' THEN
1782 IF l_lvl_rnk>=1 THEN
1783 x_time_ref_tbls:=',DDR_R_DAY TDAY';
1784 IF p_fact_code = ddr_webservices_constants.g_pp_cd THEN
1785 x_time_ref_joins:=' AND TDAY.CLNDR_DT BETWEEN X.PRMTN_FROM_DT AND X.PRMTN_TO_DT';
1786 ELSE
1787 x_time_ref_joins:=' AND X.DAY_CD = TDAY.DAY_CD';
1788 END IF;
1789 END IF;
1790 IF l_lvl_rnk>=3 THEN
1791 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_CLNDR_MNTH TCA';
1792 x_time_ref_joins:=x_time_ref_joins||' AND TDAY.CLNDR_MNTH_ID = TCA.CLNDR_MNTH_ID';
1793 END IF;
1794 IF l_lvl_rnk>=5 THEN
1795 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_CLNDR_QTR TCB,DDR_R_CLNDR_YR TCC';
1796 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';
1797 END IF;
1798 IF p_time_dim_lvl_cd IS NOT NULL THEN
1799 get_time_ref_join(l_hrchy_lvl_name,l_lvl_rnk,x_time_where_clus,x_return_status,x_msg_count,x_msg_data);
1800 x_time_where_clus:= x_time_where_clus || '=''' ||
1801 p_time_lvl_val||'''';
1802 END IF;
1803 WHEN 'BUSINESS TIME' THEN
1804 IF l_lvl_rnk>=1 THEN
1805 x_time_ref_tbls:=',DDR_R_CLNDR TCLNDR,DDR_R_DAY TDAY,DDR_R_BASE_DAY TBA';
1806
1807 IF p_fact_code = ddr_webservices_constants.g_pp_cd THEN
1808 x_time_ref_joins:=' AND TDAY.CLNDR_DT BETWEEN X.PRMTN_FROM_DT AND X.PRMTN_TO_DT AND TDAY.DAY_CD = TBA.DAY_CD '
1809 ||' AND TBA.CLNDR_TYP=''BSNS'' AND TBA.CLNDR_CD=TCLNDR.CLNDR_CD AND TCLNDR.CLNDR_TYP=''BSNS'''
1810 ||' AND TCLNDR.ORG_CD='''||p_org_cd||'''';
1811 ELSE
1812 x_time_ref_joins:=' AND X.DAY_CD = TDAY.DAY_CD AND TDAY.DAY_CD = TBA.DAY_CD '
1813 ||' AND TBA.CLNDR_TYP=''BSNS'' AND TBA.CLNDR_CD=TCLNDR.CLNDR_CD AND TCLNDR.CLNDR_TYP=''BSNS'''
1814 ||' AND TCLNDR.ORG_CD='''||p_org_cd||'''';
1815 END IF;
1816 END IF;
1817 IF l_lvl_rnk>=2 THEN
1818 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_BSNS_WK TBB';
1819 x_time_ref_joins:=x_time_ref_joins||' AND TBA.WK_ID = TBB.BSNS_WK_ID';
1820 END IF;
1821 IF l_lvl_rnk>=3 THEN
1822 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_BSNS_MNTH TBC';
1823 x_time_ref_joins:=x_time_ref_joins||' AND TBB.BSNS_MNTH_ID = TBC.BSNS_MNTH_ID';
1824 END IF;
1825 IF l_lvl_rnk>=4 THEN
1826 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_BSNS_QTR TBD';
1827 x_time_ref_joins:=x_time_ref_joins||' AND TBC.BSNS_QTR_ID = TBD.BSNS_QTR_ID';
1828 END IF;
1829 IF l_lvl_rnk>=5 THEN
1830 x_time_ref_tbls:=x_time_ref_tbls || ',DDR_R_BSNS_YR TBE';
1831 x_time_ref_joins:=x_time_ref_joins||' AND TBD.BSNS_YR_ID = TBE.BSNS_YR_ID';
1832 END IF;
1833 IF p_time_dim_lvl_cd IS NOT NULL THEN
1834 get_time_ref_join(l_hrchy_lvl_name,l_lvl_rnk,x_time_where_clus,x_return_status,x_msg_count,x_msg_data);
1835 x_time_where_clus:= x_time_where_clus || '=''' ||
1836 p_time_lvl_val||'''';
1837 END IF;
1838
1839 END CASE;
1840 -- END CASE;
1841 EXCEPTION
1842 WHEN OTHERS THEN
1843 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1844 x_msg_count:=1;
1845 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1846 END get_time_hrchy_clauses;
1847
1848 PROCEDURE get_time_ref_join(p_hrchy_name IN VARCHAR2,
1849 p_lvl_rnk IN NUMBER,
1850 x_ref_join OUT NOCOPY VARCHAR2,
1854 IS
1851 x_return_status OUT NOCOPY VARCHAR2,
1852 x_msg_count OUT NOCOPY VARCHAR2,
1853 x_msg_data OUT NOCOPY VARCHAR2)
1855 BEGIN
1856 CASE p_hrchy_name
1857 WHEN 'GREGORIAN TIME' THEN
1858 CASE p_lvl_rnk
1859 WHEN 1 THEN
1860 x_ref_join:=' X.DAY_CD ';
1861 WHEN 3 THEN
1862 x_ref_join:=' TCA.MNTH_CD ';
1863 WHEN 5 THEN
1864 x_ref_join:=' TCC.YR_CD';
1865 END CASE;
1866 WHEN 'BUSINESS TIME' THEN
1867 CASE p_lvl_rnk
1868 WHEN 1 THEN
1869 x_ref_join:=' X.DAY_CD';
1870 WHEN 2 THEN
1871 x_ref_join:=' TBB.WK_CD';
1872 WHEN 3 THEN
1873 x_ref_join:=' TBC.MNTH_CD ';
1874 WHEN 4 THEN
1875 x_ref_join:=' TBD.QTR_CD ';
1876 WHEN 5 THEN
1877 --bug 6928308 change start
1878 x_ref_join:=' TBE.YR_CD ';
1879 --bug 6928308 change end
1880 END CASE;
1881 END CASE;
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1885 x_msg_count:=1;
1886 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1887 END get_time_ref_join;
1888
1889
1890 PROCEDURE get_loc_hrchy_clauses(p_loc_dim_lvl_cd IN VARCHAR2,
1891 p_loc_lvl_val IN VARCHAR2,
1892 p_exp_loc_level IN VARCHAR2,
1893 p_org_dim_lvl_cd IN VARCHAR2,
1894 p_fact_code IN VARCHAR2,
1895 x_return_status OUT NOCOPY VARCHAR2,
1896 x_msg_count OUT NOCOPY VARCHAR2,
1897 x_msg_data OUT NOCOPY VARCHAR2,
1898 x_loc_ref_tbls OUT NOCOPY VARCHAR2,
1899 x_loc_ref_joins OUT NOCOPY VARCHAR2,
1900 x_loc_where_clus OUT NOCOPY VARCHAR2)
1901 IS
1902 l_lvl_rnk NUMBER:=null;
1903 l_org_lvl_rnk NUMBER:=null;
1904 BEGIN
1905 -- CASE p_fact_code
1906 -- for RETAIL SALE RETURN ITEM DAY fact
1907 --WHEN ddr_webservices_constants.g_rsrid_cd THEN
1908 IF p_loc_dim_lvl_cd IS NOT NULL THEN
1909 get_hrchy_lvl('LOCATION',p_loc_dim_lvl_cd,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1910 ELSIF p_exp_loc_level IS NOT NULL THEN
1911 get_hrchy_lvl('LOCATION',p_exp_loc_level,l_lvl_rnk,x_return_status,x_msg_count,x_msg_data);
1912 END IF;
1913 IF l_lvl_rnk IS NULL THEN
1914 RETURN;
1915 END IF;
1916 IF l_lvl_rnk>=1 THEN
1917 --check if p_org_dim_lvl_cd is not specified, the include DSR_R_ORG_BSNS_UNIT ORGA in FROM clause
1918 IF p_org_dim_lvl_cd IS NULL THEN
1919 x_loc_ref_tbls:= ',DDR_R_ORG_BSNS_UNIT ORGA';
1920 x_loc_ref_joins:=' AND X.ORG_BSNS_UNIT_ID = ORGA.ORG_BSNS_UNIT_ID';
1921 END IF;
1922 x_loc_ref_tbls:= x_loc_ref_tbls|| ',DDR_R_ADDR_LOC LOCA,DDR_R_CITY LOCB';
1923 x_loc_ref_joins:=x_loc_ref_joins||' AND ORGA.ADDR_LOC_ID = LOCA.ADDR_LOC_ID(+) AND LOCA.CITY_CD = LOCB.CITY_CD(+)';
1924 END IF;
1925 IF l_lvl_rnk>=2 THEN
1926 x_loc_ref_tbls:=x_loc_ref_tbls || ',DDR_R_STATE LOCC';
1927 x_loc_ref_joins:=x_loc_ref_joins||' AND LOCB.STATE_CD = LOCC.STATE_CD(+)';
1928 END IF;
1929 IF l_lvl_rnk>=3 THEN
1930 x_loc_ref_tbls:=x_loc_ref_tbls || ',DDR_R_CNTRY LOCD';
1931 x_loc_ref_joins:=x_loc_ref_joins||' AND LOCC.CNTRY_CD = LOCD.CNTRY_CD(+)';
1932 END IF;
1933 IF p_loc_dim_lvl_cd IS NOT NULL THEN
1934 get_loc_ref_join(l_lvl_rnk,x_loc_where_clus,x_return_status,x_msg_count,x_msg_data);
1935 x_loc_where_clus:= x_loc_where_clus || '=''' || p_loc_lvl_val||'''';
1936 END IF;
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_hrchy_clauses;
1944
1945 PROCEDURE get_loc_ref_join(p_lvl_rnk IN NUMBER,
1946 x_ref_join OUT NOCOPY VARCHAR2,
1947 x_return_status OUT NOCOPY VARCHAR2,
1948 x_msg_count OUT NOCOPY VARCHAR2,
1949 x_msg_data OUT NOCOPY VARCHAR2)
1950 IS
1951 BEGIN
1952 CASE p_lvl_rnk
1953 WHEN 1 THEN
1954 x_ref_join:=' LOCB.CITY_CD ';
1955 WHEN 2 THEN
1956 x_ref_join:=' LOCC.STATE_CD ';
1957 WHEN 3 THEN
1958 x_ref_join:=' LOCD.CNTRY_CD ';
1959 END CASE;
1960 EXCEPTION
1961 WHEN OTHERS THEN
1962 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
1963 x_msg_count:=1;
1964 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:'||sqlerrm;
1965 END get_loc_ref_join;
1966
1967 PROCEDURE get_hrchy_lvl(p_hrchy_lvl_name IN VARCHAR2,
1968 p_hrchy_lvl_cd IN VARCHAR2,
1969 x_hrchy_lvl OUT NOCOPY NUMBER,
1970 x_return_status OUT NOCOPY VARCHAR2,
1971 x_msg_count OUT NOCOPY VARCHAR2,
1972 x_msg_data OUT NOCOPY VARCHAR2)
1973 IS
1974 l_hrchy_lvl NUMBER:=null;
1975 BEGIN
1976 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;
1980 WHEN NO_DATA_FOUND THEN
1977 -- 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);
1978 x_hrchy_lvl:=l_hrchy_lvl;
1979 EXCEPTION
1981 x_return_status := ddr_webservices_constants.g_ret_sts_error;
1982 x_msg_count := 1;
1983 x_msg_data := 'No data found for hierarchy code:'|| p_hrchy_lvl_cd ||'. Error Code' ||sqlcode||' Error message:'||sqlerrm;
1984 END get_hrchy_lvl;
1985
1986 --Bug 6880404 change start
1987 PROCEDURE get_other_join_conditions(p_fact_code VARCHAR2,
1988 x_return_status OUT NOCOPY VARCHAR2,
1989 x_msg_count OUT NOCOPY VARCHAR2,
1990 x_msg_data OUT NOCOPY VARCHAR2,
1991 x_oth_join_codn OUT NOCOPY VARCHAR2)
1992 IS
1993 l_join_cndn VARCHAR2(32767):=NULL;
1994 l_max_frcst_date DATE:=null;
1995 BEGIN
1996 --if the fact code is for forcest sales table, then join condition to fetch
1997 --the latest forcest version
1998 IF p_fact_code= ddr_webservices_constants.g_sfid_cd THEN
1999 --bug 6905930 change start
2000 l_join_cndn:= ' AND (x.frcst_vrsn,x.mfg_org_cd,x.rtl_org_cd,
2001 x.org_bsns_unit_id, x.day_cd, x.glbl_item_id, x.rtl_sku_item_id) IN(SELECT
2002 MAX(frcst_vrsn), mfg_org_cd, rtl_org_cd, org_bsns_unit_id, day_cd,
2003 glbl_item_id, rtl_sku_item_id FROM ddr_b_sls_frcst_item_day GROUP BY
2004 mfg_org_cd, rtl_org_cd, org_bsns_unit_id, day_cd, glbl_item_id,
2005 rtl_sku_item_id)';
2006 --bug 6905930 change end
2007 END IF;
2008 x_oth_join_codn := l_join_cndn;
2009 EXCEPTION
2010 WHEN NO_DATA_FOUND THEN
2011 x_return_status := ddr_webservices_constants.g_ret_sts_error;
2012 x_msg_count := 1;
2013 x_msg_data := 'No data found. Error Code' ||sqlcode||' Error
2014 message:'||sqlerrm;
2015 WHEN OTHERS THEN
2016 x_return_status:=ddr_webservices_constants.g_ret_sts_unexp_error;
2017 x_msg_count:=1;
2018 x_msg_data:='Unexpected Error. Error code:'||sqlcode||' Error message:
2019 '||sqlerrm;
2020 END get_other_join_conditions;
2021 --Bug 6880404 change end
2022
2023 END ddr_webservices_pub;
[Home] [Help]