1 PACKAGE BODY ddr_ref_util_pkg AS
2 /* $Header: ddrurefb.pls 120.2 2008/02/19 04:23:25 vbhave noship $ */
3
4 FUNCTION GET_ERR_MSG (p_err_no VARCHAR2 DEFAULT NULL)
5 RETURN varchar2 as
6 l_err_msg VARCHAR2(40);
7 begin
8 if p_err_no is not null then
9 select err_name into l_err_msg from ddr_l_err where err_cd = p_err_no;
10 return (l_err_msg);
11 else
12 return(null);
13 end if;
14 end GET_ERR_MSG;
15
16 PROCEDURE TRUNC_INTERFACE (p_table_parameters IN VARCHAR2 DEFAULT NULL)
17 IS
18
19 n number;
20 text varchar2(500);
21 cut_text varchar2(100);
22 p_loading_mode varchar2(40);
23 Begin
24 select param_val into p_loading_mode from ddr_l_sys_param where param_cd = 'RUN_TYP_REF';
25 text:=p_table_parameters;
26 If
27 text is not null
28 Then
29 Loop
30 n:=0;
31 n:= Instr(text,',');
32 If
33 n<>0
34 Then
35 cut_text:= Substr(text,1,n-1);
36 Else
37 cut_text:= text;
38 End If;
39
40 If p_loading_mode = 'P' THEN
41 EXECUTE IMMEDIATE 'TRUNCATE TABLE '|| 'DDR.' || CUT_TEXT;
42 END IF;
43 Exit when n=0;
44 text:= Substr(text,n+1);
45 End Loop;
46 End If;
47 End TRUNC_INTERFACE;
48
49 FUNCTION CHECK_MFG_ITEM_HCHY (p_hchy_cd VARCHAR2, p_hchy_level VARCHAR2)
50 RETURN VARCHAR2
51 as
52 l_mfg_item_lvl NUMBER;
53 l_return VARCHAR2(30);
54
55 CURSOR cur_cmpny IS
56 SELECT 1
57 FROM DDR_R_MFG_ITEM_CMPNY
58 WHERE MFG_CMPNY_CD = p_hchy_cd;
59
60 CURSOR cur_div IS
61 SELECT 1
62 FROM DDR_R_MFG_ITEM_DIV
63 WHERE MFG_DIV_CD = p_hchy_cd
64 AND EFF_TO_DT IS NULL;
65
66 CURSOR cur_grp IS
67 SELECT 1
68 FROM DDR_R_MFG_ITEM_GRP
69 WHERE MFG_GRP_CD = p_hchy_cd
70 AND EFF_TO_DT IS NULL;
71
72 CURSOR cur_class IS
73 SELECT 1
74 FROM DDR_R_MFG_ITEM_CLASS
75 WHERE MFG_CLASS_CD = p_hchy_cd
76 AND EFF_TO_DT IS NULL;
77
78 CURSOR cur_sbc IS
79 SELECT 1
80 FROM DDR_R_MFG_ITEM_SBC
81 WHERE MFG_SBC_CD = p_hchy_cd
82 AND EFF_TO_DT IS NULL;
83
84 CURSOR cur_item IS
85 SELECT 1
86 FROM DDR_R_MFG_ITEM
87 WHERE MFG_ITEM_NBR = p_hchy_cd
88 AND EFF_TO_DT IS NULL;
89
90 CURSOR cur_sku IS
91 SELECT 1
92 FROM DDR_R_MFG_SKU_ITEM
93 WHERE MFG_SKU_ITEM_NBR = p_hchy_cd
94 AND EFF_TO_DT IS NULL;
95
96 Begin
97 IF p_hchy_level IS NULL OR p_hchy_cd IS NULL
98 THEN
99 l_return := 'DSR-1001';
100 RETURN (l_return);
101 END IF;
102
103 l_mfg_item_lvl := -1;
104 IF UPPER(p_hchy_level) IN ('COMPANY','DIVISION','GROUP','CLASS','SUBCLASS','ITEM','SKU','NA') THEN
105
106 IF p_hchy_level='COMPANY'
107 THEN
108 OPEN cur_cmpny;
109 FETCH cur_cmpny INTO l_mfg_item_lvl;
110 CLOSE cur_cmpny;
111 ELSIF p_hchy_level='DIVISION'
112 THEN
113 OPEN cur_div;
114 FETCH cur_div INTO l_mfg_item_lvl;
115 CLOSE cur_div;
116 ELSIF p_hchy_level='GROUP'
117 THEN
118 OPEN cur_grp;
119 FETCH cur_grp INTO l_mfg_item_lvl;
120 CLOSE cur_grp;
121 ELSIF p_hchy_level='CLASS'
122 THEN
123 OPEN cur_class;
124 FETCH cur_class INTO l_mfg_item_lvl;
125 CLOSE cur_class;
126 ELSIF p_hchy_level='SUBCLASS'
127 THEN
128 OPEN cur_sbc;
129 FETCH cur_sbc INTO l_mfg_item_lvl;
130 CLOSE cur_sbc;
131 ELSIF p_hchy_level='ITEM'
132 THEN
133 OPEN cur_item;
134 FETCH cur_item INTO l_mfg_item_lvl;
135 CLOSE cur_item;
136 ELSIF p_hchy_level='SKU'
137 THEN
138 OPEN cur_sku;
139 FETCH cur_sku INTO l_mfg_item_lvl;
140 CLOSE cur_sku;
141 ELSIF p_hchy_level='NA'
142 THEN
143 IF p_hchy_cd ='-1'
144 THEN
145 l_mfg_item_lvl := 1;
146 ELSE
147 l_mfg_item_lvl := -1;
148 END IF;
149 END IF;
150
151 IF l_mfg_item_lvl='-1'
152 THEN
153 l_return := 'DSR-1002';
154 END IF;
155
156 ELSE l_return := 'DSR-1002';
157 END IF;
158
159 RETURN(l_return);
160 End CHECK_MFG_ITEM_HCHY;
161
162 FUNCTION CHECK_ORG_HCHY (p_hchy_cd VARCHAR2, p_hchy_level VARCHAR2)
163 RETURN VARCHAR2
164 as
165 l_org_lvl NUMBER;
166 l_return VARCHAR2(30);
167
168 CURSOR cur_chain IS
169 SELECT 1
170 FROM DDR_R_ORG_CHAIN
171 WHERE CHAIN_CD = p_hchy_cd
172 AND EFF_TO_DT IS NULL;
173
174 CURSOR cur_org IS
175 SELECT 1
176 FROM DDR_R_ORG
177 WHERE ORG_CD = p_hchy_cd;
178
179 CURSOR cur_area IS
180 SELECT 1
181 FROM DDR_R_ORG_AREA
182 WHERE AREA_CD = p_hchy_cd
183 AND EFF_TO_DT IS NULL;
184
185 CURSOR cur_rgn IS
186 SELECT 1
187 FROM DDR_R_ORG_RGN
188 WHERE RGN_CD = p_hchy_cd
189 AND EFF_TO_DT IS NULL;
190
191 CURSOR cur_dstrct IS
192 SELECT 1
193 FROM DDR_R_ORG_DSTRCT
194 WHERE DSTRCT_CD = p_hchy_cd
195 AND EFF_TO_DT IS NULL;
196
197 CURSOR cur_bsns_unit IS
198 SELECT 1
199 FROM DDR_R_ORG_BSNS_UNIT
200 WHERE BSNS_UNIT_CD = p_hchy_cd
201 AND EFF_TO_DT IS NULL;
202 Begin
203 IF p_hchy_level IS NULL OR p_hchy_cd IS NULL
204 THEN
205 l_return := 'DSR-1001';
206 RETURN l_return;
207 END IF;
208
209 l_org_lvl := -1;
210 IF UPPER(p_hchy_level) IN ('ORGANIZATION','CHAIN','AREA','REGION','DISTRICT','BU','NA')
211 THEN
212 IF p_hchy_level='ORGANIZATION'
213 THEN
214 OPEN cur_org;
215 FETCH cur_org INTO l_org_lvl;
216 CLOSE cur_org;
217 ELSIF p_hchy_level='CHAIN'
218 THEN
219 OPEN cur_chain;
220 FETCH cur_chain INTO l_org_lvl;
221 CLOSE cur_chain;
222 ELSIF p_hchy_level='AREA'
223 THEN
224 OPEN cur_area;
225 FETCH cur_area INTO l_org_lvl;
226 CLOSE cur_area;
227 ELSIF p_hchy_level='REGION'
228 THEN
229 OPEN cur_rgn;
230 FETCH cur_rgn INTO l_org_lvl;
231 CLOSE cur_rgn;
232 ELSIF p_hchy_level='DISTRICT'
233 THEN
234 OPEN cur_dstrct;
235 FETCH cur_dstrct INTO l_org_lvl;
236 CLOSE cur_dstrct;
237 ELSIF p_hchy_level='BU'
238 THEN
239 OPEN cur_bsns_unit;
240 FETCH cur_bsns_unit INTO l_org_lvl;
241 CLOSE cur_bsns_unit;
242 ELSIF p_hchy_level='NA'
243 THEN
244 IF p_hchy_cd ='-1'
245 THEN
246 l_org_lvl := 1;
247 ELSE
248 l_org_lvl := -1;
249 END IF;
250 END IF;
251
252 IF l_org_lvl ='-1'
253 THEN
254 l_return := 'DSR-1002';
255 END IF;
256 ELSE l_return := 'DSR-1002';
257 END IF;
258
259 RETURN(l_return);
260 End CHECK_ORG_HCHY;
261
262 FUNCTION CHECK_RTL_ITEM_HCHY (p_hchy_cd VARCHAR2, p_hchy_level VARCHAR2)
263 RETURN VARCHAR2
264 as
265 l_rtl_item_lvl NUMBER;
266 l_return VARCHAR2(30);
267
268 CURSOR cur_cmpny IS
269 SELECT 1
270 FROM DDR_R_RTL_ITEM_CMPNY
271 WHERE RTL_CMPNY_CD = p_hchy_cd;
272
273 CURSOR cur_div IS
274 SELECT 1
275 FROM DDR_R_RTL_ITEM_DIV
276 WHERE RTL_DIV_CD = p_hchy_cd
277 AND EFF_TO_DT IS NULL;
278
279 CURSOR cur_grp IS
280 SELECT 1
281 FROM DDR_R_RTL_ITEM_GRP
282 WHERE RTL_GRP_CD = p_hchy_cd
283 AND EFF_TO_DT IS NULL;
284
285 CURSOR cur_dept IS
286 SELECT 1
287 FROM DDR_R_RTL_ITEM_DEPT
288 WHERE RTL_DEPT_CD = p_hchy_cd
289 AND EFF_TO_DT IS NULL;
290
291 CURSOR cur_class IS
292 SELECT 1
293 FROM DDR_R_RTL_ITEM_CLASS
294 WHERE RTL_CLASS_CD = p_hchy_cd
295 AND EFF_TO_DT IS NULL;
296
297 CURSOR cur_sbc IS
298 SELECT 1
299 FROM DDR_R_RTL_ITEM_SBC
300 WHERE RTL_SBC_CD = p_hchy_cd
301 AND EFF_TO_DT IS NULL;
302
303 CURSOR cur_item IS
304 SELECT 1
305 FROM DDR_R_RTL_ITEM
306 WHERE RTL_ITEM_NBR = p_hchy_cd
307 AND EFF_TO_DT IS NULL;
308
309 CURSOR cur_sku IS
310 SELECT 1
311 FROM DDR_R_RTL_SKU_ITEM
312 WHERE RTL_SKU_ITEM_NBR = p_hchy_cd
313 AND EFF_TO_DT IS NULL;
314
315 Begin
316 IF p_hchy_level IS NULL OR p_hchy_cd IS NULL
317 THEN
318 l_return := 'DSR-1001';
319 RETURN (l_return);
320 END IF;
321
322 l_rtl_item_lvl := -1;
323 IF UPPER(p_hchy_level) IN ('COMPANY','DIVISION','GROUP','DEPARTMENT','CLASS','SUBCLASS','ITEM','SKU','NA') THEN
324
325 IF p_hchy_level='COMPANY'
326 THEN
327 OPEN cur_cmpny;
328 FETCH cur_cmpny INTO l_rtl_item_lvl;
329 CLOSE cur_cmpny;
330 ELSIF p_hchy_level='DIVISION'
331 THEN
332 OPEN cur_div;
333 FETCH cur_div INTO l_rtl_item_lvl;
334 CLOSE cur_div;
335 ELSIF p_hchy_level='GROUP'
336 THEN
337 OPEN cur_grp;
338 FETCH cur_grp INTO l_rtl_item_lvl;
339 CLOSE cur_grp;
340 ELSIF p_hchy_level='DEPARTMENT'
341 THEN
342 OPEN cur_dept;
343 FETCH cur_dept INTO l_rtl_item_lvl;
344 CLOSE cur_dept;
345 ELSIF p_hchy_level='CLASS'
346 THEN
347 OPEN cur_class;
348 FETCH cur_class INTO l_rtl_item_lvl;
349 CLOSE cur_class;
350 ELSIF p_hchy_level='SUBCLASS'
351 THEN
352 OPEN cur_sbc;
353 FETCH cur_sbc INTO l_rtl_item_lvl;
354 CLOSE cur_sbc;
355 ELSIF p_hchy_level='ITEM'
356 THEN
357 OPEN cur_item;
358 FETCH cur_item INTO l_rtl_item_lvl;
359 CLOSE cur_item;
360 ELSIF p_hchy_level='SKU'
361 THEN
362 OPEN cur_sku;
363 FETCH cur_sku INTO l_rtl_item_lvl;
364 CLOSE cur_sku;
365 ELSIF p_hchy_level='NA'
366 THEN
367 IF p_hchy_cd ='-1'
368 THEN
369 l_rtl_item_lvl := 1;
370 ELSE
371 l_rtl_item_lvl := -1;
372 END IF;
373 END IF;
374
375 IF l_rtl_item_lvl='-1'
376 THEN
377 l_return := 'DSR-1002';
378 END IF;
379
380 ELSE l_return := 'DSR-1002';
381 END IF;
382
383 RETURN(l_return);
384 End CHECK_RTL_ITEM_HCHY;
385
386 FUNCTION CHECK_TIME_HCHY (p_hchy_cd VARCHAR2, p_hchy_level VARCHAR2)
387 RETURN VARCHAR2
388 as
389 l_time_lvl NUMBER;
390 l_return VARCHAR2(30);
391
392 CURSOR cur_yr IS
393 SELECT 1
394 FROM DDR_R_BSNS_YR
395 WHERE YR_CD = p_hchy_cd;
396
397 CURSOR cur_qtr IS
398 SELECT 1
399 FROM DDR_R_BSNS_QTR
400 WHERE QTR_CD = p_hchy_cd;
401
402 CURSOR cur_mnth IS
403 SELECT 1
404 FROM DDR_R_BSNS_MNTH
405 WHERE MNTH_CD = p_hchy_cd;
406
407 CURSOR cur_wk IS
408 SELECT 1
409 FROM DDR_R_BSNS_WK
410 WHERE WK_CD = p_hchy_cd;
411
412 CURSOR cur_day IS
413 SELECT 1
414 FROM DDR_R_DAY
415 WHERE DAY_CD = p_hchy_cd;
416
417 Begin
418 IF p_hchy_level IS NULL OR p_hchy_cd IS NULL
419 THEN
420 l_return := 'DSR-1001';
421 RETURN (l_return);
422 END IF;
423
424 l_time_lvl := -1;
425 IF UPPER(p_hchy_level) IN ('YEAR','QUARTER','MONTH','WEEK','DAY','NA') THEN
426
427 IF p_hchy_level='YEAR'
428 THEN
429 OPEN cur_yr;
430 FETCH cur_yr INTO l_time_lvl;
431 CLOSE cur_yr;
432 ELSIF p_hchy_level='QUARTER'
433 THEN
434 OPEN cur_qtr;
435 FETCH cur_qtr INTO l_time_lvl;
436 CLOSE cur_qtr;
437 ELSIF p_hchy_level='MONTH'
438 THEN
439 OPEN cur_mnth;
440 FETCH cur_mnth INTO l_time_lvl;
441 CLOSE cur_mnth;
442 ELSIF p_hchy_level='WEEK'
443 THEN
444 OPEN cur_wk;
445 FETCH cur_wk INTO l_time_lvl;
446 CLOSE cur_wk;
447 ELSIF p_hchy_level='DAY'
448 THEN
449 OPEN cur_day;
450 FETCH cur_day INTO l_time_lvl;
451 CLOSE cur_day;
452 ELSIF p_hchy_level='NA'
453 THEN
454 IF p_hchy_cd ='-1'
455 THEN
456 l_time_lvl := 1;
457 ELSE
458 l_time_lvl := -1;
459 END IF;
460 END IF;
461
462 IF l_time_lvl ='-1'
463 THEN
464 l_return := 'DSR-1002';
465 END IF;
466
467 ELSE l_return := 'DSR-1002';
468 END IF;
469
470 RETURN(l_return);
471 End CHECK_TIME_HCHY;
472
473 END ddr_ref_util_pkg;
[Home] [Help]