[Home] [Help]
PACKAGE BODY: APPS.DDR_EMD_UTIL
Source
1 PACKAGE BODY ddr_emd_util AS
2 /* $Header: ddremdub.pls 120.11.12020000.3 2012/08/04 02:31:58 gglover ship $ */
3
4 get_avg_sls_id1 ddr_b_rtl_sl_rtn_itm_day.org_bsns_unit_id%TYPE;
5 get_avg_sls_id2 ddr_b_rtl_sl_rtn_itm_day.mfg_sku_item_id%TYPE;
6 get_avg_sls_id3 ddr_b_rtl_sl_rtn_itm_day.rtl_sku_item_id%TYPE;
7 get_avg_sls_wk_strt_dt DATE;
8 get_avg_sls_avg_sls NUMBER;
9 get_sls_threshold_max_exp_sls NUMBER;
10
11 FUNCTION get_user RETURN VARCHAR2
12 IS
13 v_sql VARCHAR2(1000);
14 v_user VARCHAR2(100);
15
16 BEGIN
17 v_sql := 'select user from dual';
18 EXECUTE IMMEDIATE v_sql
19 INTO v_user;
20
21 RETURN v_user;
22 END get_user;
23
24
25 PROCEDURE create_exception(p_mfg_org_cd IN VARCHAR2
26 , p_rtl_org_cd IN VARCHAR2
27 , p_excptn_type IN VARCHAR2
28 , p_excptn_src_code IN VARCHAR2
29 , p_excptn_date IN VARCHAR2
30 , p_org_bsns_unit_id IN VARCHAR2
31 , p_mfg_sku_item_id IN VARCHAR2
32 , p_rtl_sku_item_id IN VARCHAR2
33 , p_user_id IN VARCHAR2
34 , p_excptn_qty IN VARCHAR2 DEFAULT NULL
35 , p_excptn_amt IN VARCHAR2 DEFAULT NULL) IS
36 BEGIN
37 INSERT INTO DDR_B_EXCPTN_ITEM_DAY
38 (MFG_ORG_CD,
39 RTL_ORG_CD,
40 ORG_BSNS_UNIT_ID,
41 DAY_CD,
42 MFG_SKU_ITEM_ID,
43 RTL_SKU_ITEM_ID,
44 EXCPTN_TYP,
45 EXCPTN_QTY,
46 EXCPTN_AMT,
47 EXCPTN_SRC_CD,
48 CRTD_BY_DSR,
49 LAST_UPDT_BY_DSR,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN)
55 VALUES
56 (p_mfg_org_cd,
57 p_rtl_org_cd,
58 p_org_bsns_unit_id,
59 to_char(to_date(p_excptn_date, 'YYYY-MM-DD'), 'YYYYMMDD'),
60 p_mfg_sku_item_id,
61 p_rtl_sku_item_id,
62 p_excptn_type,
63 p_excptn_qty,
64 p_excptn_amt,
65 p_excptn_src_code,
66 p_user_id,
67 p_user_id,
68 -1,
69 SYSDATE,
70 -1,
71 SYSDATE,
72 -1);
73 -- Bug#9275960 - Commented out Commit for every row inserted.
74 -- COMMIT;
75 END create_exception;
76
77 PROCEDURE delete_exception(p_excptn_type IN VARCHAR2
78 , p_excptn_src_code IN VARCHAR2
79 , p_date_offset IN NUMBER DEFAULT 0) IS
80 BEGIN
81 DELETE FROM DDR_B_EXCPTN_ITEM_DAY
82 WHERE EXCPTN_TYP = p_excptn_type
83 AND EXCPTN_SRC_CD = p_excptn_src_code
84 AND DAY_CD >= to_char(SYSDATE - p_date_offset, 'YYYYMMDD');
85
86 -- Bug#9275960 - Commented out Commit.
87 -- COMMIT;
88 END delete_exception;
89
90 PROCEDURE delete_all_exceptions(p_end_date IN DATE
91 ,p_excptn_type IN VARCHAR2 DEFAULT NULL
92 ,p_excptn_src_code IN VARCHAR2 DEFAULT NULL
93 ,x_return_status OUT NOCOPY VARCHAR2
94 ,x_msg OUT NOCOPY VARCHAR2
95 ) IS
96 BEGIN
97 x_return_status := ddr_emd_util.success;
98
99 DELETE FROM ddr_b_excptn_item_day
100 WHERE excptn_typ = NVL(p_excptn_type,excptn_typ)
101 AND excptn_src_cd = NVL(p_excptn_src_code,excptn_src_cd)
102 AND day_cd <= TO_CHAR(p_end_date, 'YYYYMMDD');
103 COMMIT;
104 EXCEPTION
105 WHEN OTHERS THEN
106 x_return_status := ddr_emd_util.error;
107 x_msg := 'Failed in ddr_emd_util.delete_all_exceptions '||SQLERRM;
108 END delete_all_exceptions;
109
110
111 PROCEDURE delete_all_exceptions_wrp(
112 p_end_date IN DATE ,
113 p_excptn_type IN VARCHAR2 DEFAULT NULL ,
114 P_EXCPTN_SRC_CODE IN VARCHAR2 DEFAULT NULL
115 ) IS
116 X_RETURN_STATUS VARCHAR2(1000);
117 X_MSG VARCHAR2(1000);
118 BEGIN
119 DDR_EMD_UTIL.DELETE_ALL_EXCEPTIONS(P_END_DATE=>P_END_DATE,
120 P_EXCPTN_TYPE => P_EXCPTN_TYPE,
121 P_EXCPTN_SRC_CODE => P_EXCPTN_SRC_CODE,
122 X_RETURN_STATUS => X_RETURN_STATUS,
123 X_MSG => X_MSG
124 );
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 x_return_status := ddr_emd_util.error;
129 X_MSG := 'Failed in ddr_emd_util.delete_all_exceptions '||SQLERRM;
130 END delete_all_exceptions_wrp;
131
132
133 PROCEDURE populate_new_items(p_date DATE DEFAULT SYSDATE
134 ,p_bsns_unit_id NUMBER DEFAULT NULL
135 ,p_mfg_sku_item_id NUMBER DEFAULT NULL
136 ,p_rtl_org_cd VARCHAR2 DEFAULT NULL
137 ,p_complete_refresh BOOLEAN DEFAULT TRUE
138 ,x_return_status OUT NOCOPY VARCHAR2
139 ,x_msg OUT NOCOPY VARCHAR2
140 ) IS
141
142 v_org_cd_tbl ddr_emd_util.varchar_tbl;
143 v_bsns_unit_id_tbl ddr_emd_util.number_tbl;
144 v_mfg_sku_item_id_tbl ddr_emd_util.number_tbl;
145 v_rtl_sku_item_id_tbl ddr_emd_util.number_tbl;
146 v_new_item_strt_dt_tbl ddr_emd_util.date_tbl;
147 v_new_item_end_dt_tbl ddr_emd_util.date_tbl;
148 v_first_sl_date_tbl ddr_emd_util.date_tbl;
149 v_user VARCHAR2(100);
150
151 v_rec_count NUMBER:= 0;
152
153 CURSOR new_items IS
154 SELECT *
155 FROM ddr_r_item_bsns_unt_assc
156 WHERE eff_from_dt > TRUNC(p_date) - ddr_emd_util.new_item_period
157 AND rtl_org_cd = NVL(p_rtl_org_cd,rtl_org_cd)
158 AND rtl_bsns_unit_id = NVL(p_bsns_unit_id,rtl_bsns_unit_id)
159 AND mfg_sku_item_id = NVL(p_mfg_sku_item_id,mfg_sku_item_id)
160 --AND eff_to_dt IS NULL;
161 AND eff_to_dt = to_date('01012400','MMDDYYYY');
162
163 CURSOR rtl_sku(p_org_cd VARCHAR2
164 ,p_glbl_item_id VARCHAR2
165 ,p_glbl_item_id_typ VARCHAR2
166 ) IS
167 SELECT MAX(rtl_sku_item_id) rtl_sku_item_id -- This is temporary workaround. This is a bug
168 FROM ddr_r_rtl_sku_item
169 WHERE rtl_org_cd = p_org_cd
170 AND glbl_item_id = p_glbl_item_id
171 AND glbl_item_id_typ = p_glbl_item_id_typ;
172
173 CURSOR sls_rec (p_org_bsns_unit_id NUMBER
174 ,p_mfg_sku_item_id NUMBER
175 ,p_rtl_sku_item_id NUMBER
176 ,p_from_day_cd VARCHAR2) IS
177 SELECT MIN(day_cd) first_sl_date
178 FROM ddr_b_rtl_sl_rtn_itm_day
179 WHERE org_bsns_unit_id = p_org_bsns_unit_id
180 AND mfg_sku_item_id = p_mfg_sku_item_id
181 AND rtl_sku_item_id = p_rtl_sku_item_id
182 AND day_cd >= p_from_day_cd
183 AND sls_qty_prmry > 0;
184
185 BEGIN
186 v_user := get_user;
187
188 -- Truncate the table
189 IF p_complete_refresh THEN --{
190 IF v_user = 'APPS' THEN --{
191 EXECUTE IMMEDIATE 'TRUNCATE TABLE ddr.ddr_b_new_item_no_sls';
192 ELSE
193 EXECUTE IMMEDIATE 'TRUNCATE TABLE ddr_b_new_item_no_sls';
194 END IF; --}
195 END IF; --}
196 -- Fetch new items and check first sale
197
198 FOR rec IN new_items LOOP --{
199 FOR rec1 IN rtl_sku(rec.rtl_org_cd
200 ,rec.glbl_item_id
201 ,rec.glbl_item_id_typ) LOOP --{
202
203 v_rec_count := v_rec_count + 1;
204 v_org_cd_tbl(v_rec_count) := rec.rtl_org_cd;
205 v_bsns_unit_id_tbl(v_rec_count) := rec.rtl_bsns_unit_id;
206 v_mfg_sku_item_id_tbl(v_rec_count) := rec.mfg_sku_item_id;
207 v_rtl_sku_item_id_tbl(v_rec_count) := rec1.rtl_sku_item_id;
208 v_new_item_strt_dt_tbl(v_rec_count) := TRUNC(rec.eff_from_dt);
209 v_new_item_end_dt_tbl(v_rec_count) := v_new_item_strt_dt_tbl(v_rec_count)+ddr_emd_util.new_item_period;
210 v_first_sl_date_tbl(v_rec_count) := NULL;
211
212 FOR rec2 IN sls_rec(rec.rtl_bsns_unit_id
213 ,rec.mfg_sku_item_id
214 ,rec1.rtl_sku_item_id
215 ,TO_CHAR(rec.eff_from_dt,'YYYYMMDD')) LOOP --{
216 v_first_sl_date_tbl(v_rec_count) := TO_DATE(rec2.first_sl_date,'YYYYMMDD');
217 END LOOP; --}
218 IF MOD(v_rec_count,ddr_emd_util.c_batch_size) = 0 THEN --{
219 FORALL i IN 1..v_rec_count
220 INSERT INTO ddr_b_new_item_no_sls(rtl_org_cd
221 ,rtl_bsns_unit_id
222 ,mfg_sku_item_id
223 ,rtl_sku_item_id
224 ,new_item_srt_dt
225 ,new_item_end_dt
226 ,frst_sl_dt
227 ) VALUES
228 (v_org_cd_tbl(i)
229 ,v_bsns_unit_id_tbl(i)
230 ,v_mfg_sku_item_id_tbl(i)
231 ,v_rtl_sku_item_id_tbl(i)
232 ,v_new_item_strt_dt_tbl(i)
233 ,v_new_item_end_dt_tbl(i)
234 ,v_first_sl_date_tbl(i)
235 );
236 COMMIT;
237 v_org_cd_tbl.DELETE;
238 v_bsns_unit_id_tbl.DELETE;
239 v_mfg_sku_item_id_tbl.DELETE;
240 v_rtl_sku_item_id_tbl.DELETE;
241 v_new_item_strt_dt_tbl.DELETE;
242 v_new_item_end_dt_tbl.DELETE;
243 v_first_sl_date_tbl.DELETE;
244 v_rec_count := 0;
245 END IF; --}
246 END LOOP; --}
247 END LOOP; --}
248
249 -- Insert the last batch rows
250 FORALL i IN 1..v_rec_count
251 INSERT INTO ddr_b_new_item_no_sls(rtl_org_cd
252 ,rtl_bsns_unit_id
253 ,mfg_sku_item_id
254 ,rtl_sku_item_id
255 ,new_item_srt_dt
256 ,new_item_end_dt
257 ,frst_sl_dt
258 ) VALUES
259 (v_org_cd_tbl(i)
260 ,v_bsns_unit_id_tbl(i)
261 ,v_mfg_sku_item_id_tbl(i)
262 ,v_rtl_sku_item_id_tbl(i)
263 ,v_new_item_strt_dt_tbl(i)
264 ,v_new_item_end_dt_tbl(i)
265 ,v_first_sl_date_tbl(i)
266 );
267 COMMIT;
268 EXCEPTION
269 WHEN OTHERS THEN
270 x_return_status := ddr_emd_util.error;
271 x_msg := 'Failed in populate_new_items'||' :'||SQLERRM;
272 END populate_new_items;
273
274
275 PROCEDURE get_exp_sls(p_sls_rec ddr_b_rtl_sl_rtn_itm_day%ROWTYPE
276 ,p_exp_sls OUT NOCOPY NUMBER
277 ,x_return_status OUT NOCOPY VARCHAR2
278 ,x_msg OUT NOCOPY VARCHAR2
279 ) IS
280
281 v_day DATE := NULL;
282
283 CURSOR c1 IS
284 SELECT frcst_sls_qty_prmry
285 FROM ddr_b_sls_frcst_item_day
286 WHERE day_cd = p_sls_rec.day_cd
287 AND org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
288 AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
289 AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id
290 AND frcst_typ = 'TOTAL'
291 AND frcst_purp = 'SALES'
292 ORDER BY frcst_vrsn desc;
293
294 CURSOR c2 IS
295 SELECT median(sls_qty_prmry) median_sales
296 FROM ddr_b_rtl_sl_rtn_itm_day
297 WHERE (day_cd = TO_CHAR(v_day-7,'YYYYMMDD')
298 OR day_cd = TO_CHAR(v_day-14,'YYYYMMDD')
299 OR day_cd = TO_CHAR(v_day-21,'YYYYMMDD')
300 OR day_cd = TO_CHAR(v_day-28,'YYYYMMDD')
301 OR day_cd = TO_CHAR(v_day-35,'YYYYMMDD')
302 )
303 AND org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
304 AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
305 AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id;
306
307 CURSOR c3 IS
308 SELECT median(sls_qty_prmry) median_sales
309 FROM ddr_b_rtl_sl_rtn_itm_day
310 WHERE day_cd > TO_CHAR(v_day-180,'YYYYMMDD')
311 AND day_cd < TO_CHAR(v_day,'YYYYMMDD')
312 AND org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
313 AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
314 AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id
315 AND prmtn_flag = 'Y';
316 BEGIN
317
318 x_return_status := ddr_emd_util.success;
319 p_exp_sls := NULL;
320
321 v_day := TO_DATE(p_sls_rec.day_cd,'YYYYMMDD');
322
323 OPEN c1;
324 FETCH c1 INTO p_exp_sls;
325 CLOSE c1;
326
327 IF p_exp_sls IS NOT NULL THEN --{
328 RETURN;
329 END IF; --{
330
331 IF p_sls_rec.prmtn_flag = 'N' THEN --{
332 FOR rec IN c2 LOOP --{
333 p_exp_sls := rec.median_sales;
334 END LOOP; --}
335 ELSE --}{
336 FOR rec IN c3 LOOP --{
337 p_exp_sls := rec.median_sales;
338 END LOOP; --}
339 END IF; --}
340 EXCEPTION
341 WHEN OTHERS THEN
342 x_return_status := ddr_emd_util.error;
343 x_msg := 'Failed in get_exp_sls'||' :'||SQLERRM;
344 END get_exp_sls;
345
346 PROCEDURE get_avg_sls(p_sls_rec ddr_b_rtl_sl_rtn_itm_day%ROWTYPE
347 ,p_avg_sls OUT NOCOPY NUMBER
348 ,x_return_status OUT NOCOPY VARCHAR2
349 ,x_msg OUT NOCOPY VARCHAR2
350 ) IS
351
352 v_wk_strt_dt DATE:= NULL;
353
354 BEGIN
355 x_return_status := ddr_emd_util.success;
356 p_avg_sls := NULL;
357
358 IF p_sls_rec.prmtn_flag = 'N' THEN --{
359 p_avg_sls := NULL;
360 RETURN;
361 END IF; --}
362
363 SELECT wk_strt_dt
364 INTO v_wk_strt_dt
365 FROM ddr_r_base_day_dn_mv
366 WHERE day_cd = p_sls_rec.day_cd
367 AND clndr_cd = mfg_org_cd||'-BSNS';
368
369 IF (p_sls_rec.org_bsns_unit_id = get_avg_sls_id1
370 AND p_sls_rec.mfg_sku_item_id = get_avg_sls_id2
374 p_avg_sls := get_avg_sls_avg_sls;
371 AND p_sls_rec.rtl_sku_item_id = get_avg_sls_id3) THEN --{
372
373 IF v_wk_strt_dt = get_avg_sls_wk_strt_dt THEN --{
375 RETURN;
376 END IF; --}
377 END IF; --}
378
379 SELECT AVG(sls_qty_prmry)
380 INTO p_avg_sls
381 FROM ddr_b_rtl_sl_rtn_itm_day
382 WHERE day_cd BETWEEN TO_CHAR(v_wk_strt_dt-28,'YYYYMMDD')
383 AND TO_CHAR(v_wk_strt_dt,'YYYYMMDD')
384 AND org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
385 AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
386 AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id;
387
388 get_avg_sls_id1 := p_sls_rec.org_bsns_unit_id;
389 get_avg_sls_id2 := p_sls_rec.mfg_sku_item_id;
390 get_avg_sls_id3 := p_sls_rec.rtl_sku_item_id;
391 get_avg_sls_wk_strt_dt := v_wk_strt_dt;
392 get_avg_sls_avg_sls := p_avg_sls;
393
394 EXCEPTION
395 WHEN OTHERS THEN
396 x_return_status := ddr_emd_util.error;
397 x_msg := 'Failed in get_avg_sls'||' :'||SQLERRM;
398 END get_avg_sls;
399
400 PROCEDURE get_prmtn_price(p_sls_rec ddr_b_rtl_sl_rtn_itm_day%ROWTYPE
401 ,p_prmtn_price OUT NOCOPY NUMBER
402 ,x_return_status OUT NOCOPY VARCHAR2
403 ,x_msg OUT NOCOPY VARCHAR2
404 ) IS
405 CURSOR c1 IS
406 SELECT MIN(prmtn_price_amt_rpt) min_prmtn_price
407 FROM ddr_b_prmtn_pln
408 WHERE org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
409 AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
410 AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id
411 AND TO_DATE(p_sls_rec.day_cd,'YYYYMMDD') BETWEEN prmtn_from_dt AND prmtn_to_dt;
412
413 BEGIN
414
415 x_return_status := ddr_emd_util.success;
416 p_prmtn_price := NULL;
417
418 IF p_sls_rec.prmtn_flag = 'N' THEN --{
419 RETURN;
420 END IF; --}
421
422 FOR rec IN c1 LOOP --{
423 p_prmtn_price := rec.min_prmtn_price;
424 END LOOP; --}
425 EXCEPTION
426 WHEN OTHERS THEN
427 x_return_status := ddr_emd_util.error;
428 x_msg := 'Failed in get_prmtn_price'||' :'||SQLERRM;
429 END get_prmtn_price;
430
431 PROCEDURE get_sls_threshold(p_exp_sls NUMBER
432 ,p_sls_threshold OUT NOCOPY NUMBER
433 ,x_return_status OUT NOCOPY VARCHAR2
434 ,x_msg OUT NOCOPY VARCHAR2
435 ) IS
436 v_exp_sls NUMBER;
437 BEGIN
438 x_return_status := ddr_emd_util.success;
439 p_sls_threshold := NULL;
440 v_exp_sls := p_exp_sls;
441
442 IF get_sls_threshold_max_exp_sls IS NULL THEN --{
443 SELECT MAX(expctd_sls)
444 INTO get_sls_threshold_max_exp_sls
445 FROM ddr_r_excptn_sls_thrshld;
446 END IF;
447
448 IF v_exp_sls IS NULL THEN --{
449 RETURN;
450 END IF; --}
451
452 IF v_exp_sls > get_sls_threshold_max_exp_sls THEN --{
453 v_exp_sls := get_sls_threshold_max_exp_sls;
454 END IF; --}
455
456 IF v_exp_sls < 1 THEN --{
457 v_exp_sls := 1;
458 END IF; --}
459
460 SELECT min_thrshld_actl_sls
461 INTO p_sls_threshold
462 FROM ddr_r_excptn_sls_thrshld
463 WHERE expctd_sls = ROUND(v_exp_sls);
464 EXCEPTION
465 WHEN OTHERS THEN
466 x_return_status := ddr_emd_util.error;
467 x_msg := 'Failed in get_sls_threshold'||' :'||SQLERRM;
468 END get_sls_threshold;
469
470 -- This procedure populates ddr_b_sl_rtl_addtnl_msrs table
471 -- measure3 => expected sales for the day
472 -- measure4 => avg price for last four weeks (used to calculate promotion lift)
473 -- measure5 => promotion price for the promo day
474 -- measure6 => Actual sales threshold (Poisson distribution number)
475
476 PROCEDURE populate_addtnl_sl_msrs_pvt(p_start_date DATE
477 ,p_end_date DATE
478 ,p_bsns_unit_id NUMBER DEFAULT NULL
479 ,p_mfg_sku_item_id NUMBER DEFAULT NULL
480 ,p_rtl_sku_item_id NUMBER DEFAULT NULL
481 ,p_rtl_org_cd VARCHAR2 DEFAULT NULL
482 ,p_populate_m1 VARCHAR2 DEFAULT NULL
483 ,p_populate_m2 VARCHAR2 DEFAULT NULL
484 ,p_populate_m3 VARCHAR2 DEFAULT NULL
485 ,p_populate_m4 VARCHAR2 DEFAULT NULL
486 ,p_populate_m5 VARCHAR2 DEFAULT NULL
487 ,p_populate_m6 VARCHAR2 DEFAULT NULL
488 ,p_populate_m7 VARCHAR2 DEFAULT NULL
489 ,p_populate_m8 VARCHAR2 DEFAULT NULL
490 ,p_populate_m9 VARCHAR2 DEFAULT NULL
491 ,p_populate_m10 VARCHAR2 DEFAULT NULL
492 ,p_complete_refresh BOOLEAN DEFAULT TRUE
493 ,x_return_status OUT NOCOPY VARCHAR2
494 ,x_msg OUT NOCOPY VARCHAR2
495 ) IS
496
497 v_org_cd_tbl ddr_emd_util.varchar_tbl;
498 v_bsns_unit_id_tbl ddr_emd_util.number_tbl;
499 v_mfg_sku_item_id_tbl ddr_emd_util.number_tbl;
500 v_rtl_sku_item_id_tbl ddr_emd_util.number_tbl;
501 v_day_cd_tbl ddr_emd_util.varchar_tbl;
502
503 v_m1_tbl ddr_emd_util.date_tbl;
504 v_m2_tbl ddr_emd_util.date_tbl;
505 v_m3_tbl ddr_emd_util.number_tbl;
506 v_m4_tbl ddr_emd_util.number_tbl;
510 v_m8_tbl ddr_emd_util.number_tbl;
507 v_m5_tbl ddr_emd_util.number_tbl;
508 v_m6_tbl ddr_emd_util.number_tbl;
509 v_m7_tbl ddr_emd_util.number_tbl;
511 v_m9_tbl ddr_emd_util.number_tbl;
512 v_m10_tbl ddr_emd_util.number_tbl;
513
514 v_rec_count NUMBER:= 0;
515 v_user VARCHAR2(100);
516
517 sls_rec ddr_b_rtl_sl_rtn_itm_day%ROWTYPE;
518
519 CURSOR sales_records IS
520 SELECT *
521 FROM ddr_b_rtl_sl_rtn_itm_day
522 WHERE day_cd > TO_CHAR((p_start_date-1),'YYYYMMDD')
523 AND day_cd < TO_CHAR((p_end_date+1),'YYYYMMDD')
524 AND rtl_org_cd = NVL(p_rtl_org_cd,rtl_org_cd)
525 AND org_bsns_unit_id = NVL(p_bsns_unit_id,org_bsns_unit_id)
526 AND mfg_sku_item_id = NVL(p_mfg_sku_item_id,mfg_sku_item_id)
527 AND rtl_sku_item_id = NVL(p_rtl_sku_item_id,rtl_sku_item_id)
528 ORDER BY org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,day_cd asc;
529
530 BEGIN
531 v_user := get_user;
532
533 x_return_status := NULL;
534 -- Truncate the table
535 IF p_complete_refresh THEN --{
536 IF v_user = 'APPS' THEN --{
537 EXECUTE IMMEDIATE 'TRUNCATE TABLE ddr.ddr_b_rtl_sl_addtnl_msrs';
538 ELSE
539 EXECUTE IMMEDIATE 'TRUNCATE TABLE ddr_b_rtl_sl_addtnl_msrs';
540 END IF; --}
541 END IF; --}
542
543 -- Start processing sales records
544 --OPEN sales_records;
545 --LOOP --{
546 -- FETCH sales_records INTO sls_rec;
547
548 --EXIT WHEN sales_records%NOTFOUND;
549
550 FOR sls_rec IN sales_records LOOP --{
551
552 v_rec_count := v_rec_count+1;
553
554 v_org_cd_tbl(v_rec_count) := sls_rec.rtl_org_cd;
555 v_bsns_unit_id_tbl(v_rec_count) := sls_rec.org_bsns_unit_id;
556 v_mfg_sku_item_id_tbl(v_rec_count) := sls_rec.mfg_sku_item_id;
557 v_rtl_sku_item_id_tbl(v_rec_count) := sls_rec.rtl_sku_item_id;
558 v_day_cd_tbl(v_rec_count) := sls_rec.day_cd;
559
560 -- Populate measure1 --{
561 IF p_populate_m1 = ddr_emd_util.c_populate_custom THEN --{
562 /*
563 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
564 ,ddr_emd_util.m1
565 ,v_m1_tbl(v_rec_count)
566 ,x_return_status
567 ,x_msg
568 );
569 */
570 NULL;
571 ELSIF p_populate_m1 = ddr_emd_util.c_populate_standard THEN --}{
572 NULL;
573 ELSE --}{
574 v_m1_tbl(v_rec_count):= NULL;
575 END IF; --}
576 IF x_return_status = ddr_emd_util.error THEN --{
577 RETURN;
578 END IF; --}
579 -- Populate measure1 --}
580
581 -- Populate measure2 --{
582 IF p_populate_m2 = ddr_emd_util.c_populate_custom THEN --{
583 /*
584 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
585 ,ddr_emd_util.m2
586 ,v_m2_tbl(v_rec_count)
587 ,x_return_status
588 ,x_msg
589 );
590 */
591 NULL;
592 ELSIF p_populate_m2 = ddr_emd_util.c_populate_standard THEN --}{
593 NULL;
594 ELSE --}{
595 v_m2_tbl(v_rec_count):= NULL;
596 END IF; --}
597 IF x_return_status = ddr_emd_util.error THEN --{
598 RETURN;
599 END IF; --}
600 -- Populate measure2 --}
601
602 -- Populate measure3 --{
603 IF p_populate_m3 = ddr_emd_util.c_populate_custom THEN --{
604 /*
605 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
606 ,ddr_emd_util.m3
607 ,v_m3_tbl(v_rec_count)
608 ,x_return_status
609 ,x_msg
610 );
611 */
612 NULL;
613 ELSIF p_populate_m3 = ddr_emd_util.c_populate_standard THEN --}{
614 get_exp_sls(sls_rec
615 ,v_m3_tbl(v_rec_count)
616 ,x_return_status
617 ,x_msg
618 );
619 ELSE --}{
620 v_m3_tbl(v_rec_count):= NULL;
621 END IF; --}
622 IF x_return_status = ddr_emd_util.error THEN --{
623 RETURN;
624 END IF; --}
625 -- Populate measure3 --}
626
627 -- Populate measure4 --{
628 IF p_populate_m4 = ddr_emd_util.c_populate_custom THEN --{
629 /*
630 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
631 ,ddr_emd_util.m4
632 ,v_m4_tbl(v_rec_count)
633 ,x_return_status
634 ,x_msg
635 );
636 */
637 NULL;
638 ELSIF p_populate_m4 = ddr_emd_util.c_populate_standard THEN --}{
639 get_avg_sls(sls_rec
640 ,v_m4_tbl(v_rec_count)
641 ,x_return_status
642 ,x_msg
643 );
644 ELSE --}{
645 v_m4_tbl(v_rec_count):= NULL;
646 END IF; --}
647 IF x_return_status = ddr_emd_util.error THEN --{
648 RETURN;
649 END IF; --}
650 -- Populate measure4 --}
651
652 -- Populate measure5 --{
653 IF p_populate_m5 = ddr_emd_util.c_populate_custom THEN --{
654 /*
658 ,x_return_status
655 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
656 ,ddr_emd_util.m5
657 ,v_m5_tbl(v_rec_count)
659 ,x_msg
660 );
661 */
662 NULL;
663 ELSIF p_populate_m5 = ddr_emd_util.c_populate_standard THEN --}{
664 get_prmtn_price(sls_rec
665 ,v_m5_tbl(v_rec_count)
666 ,x_return_status
667 ,x_msg
668 );
669 IF x_return_status = ddr_emd_util.error THEN --{
670 NULL;
671 END IF; --}
672 ELSE --}{
673 v_m5_tbl(v_rec_count):= NULL;
674 END IF; --}
675 IF x_return_status = ddr_emd_util.error THEN --{
676 RETURN;
677 END IF; --}
678 -- Populate measure5 --}
679
680 -- Populate measure6 --{
681 IF p_populate_m6 = ddr_emd_util.c_populate_custom THEN --{
682 /*
683 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
684 ,ddr_emd_util.m6
685 ,v_m6_tbl(v_rec_count)
686 ,x_return_status
687 ,x_msg
688 );
689 */
690 NULL;
691 ELSIF p_populate_m6 = ddr_emd_util.c_populate_standard THEN --}{
692 get_sls_threshold(v_m3_tbl(v_rec_count)
693 ,v_m6_tbl(v_rec_count)
694 ,x_return_status
695 ,x_msg
696 );
697 ELSE --}{
698 v_m6_tbl(v_rec_count):= NULL;
699 END IF; --}
700 IF x_return_status = ddr_emd_util.error THEN --{
701 RETURN;
702 END IF; --}
703 -- Populate measure6 --}
704
705 -- Populate measure7 --{
706 IF p_populate_m7 = ddr_emd_util.c_populate_custom THEN --{
707 /*
708 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
709 ,ddr_emd_util.m7
710 ,v_m7_tbl(v_rec_count)
711 ,x_return_status
712 ,x_msg
713 );
714 */
715 NULL;
716 ELSIF p_populate_m7 = ddr_emd_util.c_populate_standard THEN --}{
717 NULL;
718 ELSE --}{
719 v_m7_tbl(v_rec_count):= NULL;
720 END IF; --}
721 IF x_return_status = ddr_emd_util.error THEN --{
722 RETURN;
723 END IF; --}
724 -- Populate measure7 --}
725
726 -- Populate measure8 --{
727 IF p_populate_m8 = ddr_emd_util.c_populate_custom THEN --{
728 /*
729 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
730 ,ddr_emd_util.m8
731 ,v_m8_tbl(v_rec_count)
732 ,x_return_status
733 ,x_msg
734 );
735 */
736 NULL;
737 ELSIF p_populate_m8 = ddr_emd_util.c_populate_standard THEN --}{
738 NULL;
739 ELSE --}{
740 v_m8_tbl(v_rec_count):= NULL;
741 END IF; --}
742 IF x_return_status = ddr_emd_util.error THEN --{
743 RETURN;
744 END IF; --}
745 -- Populate measure8 --}
746
747 -- Populate measure9 --{
748 IF p_populate_m9 = ddr_emd_util.c_populate_custom THEN --{
749 /*
750 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
751 ,ddr_emd_util.m9
752 ,v_m9_tbl(v_rec_count)
753 ,x_return_status
754 ,x_msg
755 );
756 */
757 NULL;
758 ELSIF p_populate_m9 = ddr_emd_util.c_populate_standard THEN --}{
759 NULL;
760 ELSE --}{
761 v_m9_tbl(v_rec_count):= NULL;
762 END IF; --}
763 IF x_return_status = ddr_emd_util.error THEN --{
764 RETURN;
765 END IF; --}
766 -- Populate measure9 --}
767
768 -- Populate measure10 --{
769 IF p_populate_m10 = ddr_emd_util.c_populate_custom THEN --{
770 /*
771 ddr_custom_pkg.populate_addtnl_sl_msrs(sls_rec
772 ,ddr_emd_util.m10
773 ,v_m10_tbl(v_rec_count)
774 ,x_return_status
775 ,x_msg
776 );
777 */
778 NULL;
779 ELSIF p_populate_m10 = ddr_emd_util.c_populate_standard THEN --}{
780 NULL;
781 ELSE --}{
782 v_m10_tbl(v_rec_count):= NULL;
783 END IF; --}
784 IF x_return_status = ddr_emd_util.error THEN --{
785 RETURN;
786 END IF; --}
787 -- Populate measure10 --}
788
789 IF MOD(v_rec_count,ddr_emd_util.c_batch_size) = 0 THEN --{
790 FORALL i IN 1..v_rec_count
791 INSERT INTO ddr_b_rtl_sl_addtnl_msrs(rtl_org_cd
792 ,org_bsns_unit_id
793 ,mfg_sku_item_id
794 ,rtl_sku_item_id
795 ,day_cd
799 ,measure4
796 ,measure1
797 ,measure2
798 ,measure3
800 ,measure5
801 ,measure6
802 ,measure7
803 ,measure8
804 ,measure9
805 ,measure10
806 ) VALUES
807 (v_org_cd_tbl(i)
808 ,v_bsns_unit_id_tbl(i)
809 ,v_mfg_sku_item_id_tbl(i)
810 ,v_rtl_sku_item_id_tbl(i)
811 ,v_day_cd_tbl(i)
812 ,v_m1_tbl(i)
813 ,v_m2_tbl(i)
814 ,v_m3_tbl(i)
815 ,v_m4_tbl(i)
816 ,v_m5_tbl(i)
817 ,v_m6_tbl(i)
818 ,v_m7_tbl(i)
819 ,v_m8_tbl(i)
820 ,v_m9_tbl(i)
821 ,v_m10_tbl(i)
822 );
823 COMMIT;
824 v_org_cd_tbl.DELETE;
825 v_bsns_unit_id_tbl.DELETE;
826 v_mfg_sku_item_id_tbl.DELETE;
827 v_rtl_sku_item_id_tbl.DELETE;
828 v_day_cd_tbl.DELETE;
829 v_m1_tbl.DELETE;
830 v_m2_tbl.DELETE;
831 v_m3_tbl.DELETE;
832 v_m4_tbl.DELETE;
833 v_m5_tbl.DELETE;
834 v_m6_tbl.DELETE;
835 v_m7_tbl.DELETE;
836 v_m8_tbl.DELETE;
837 v_m9_tbl.DELETE;
838 v_m10_tbl.DELETE;
839
840 v_rec_count := 0;
841 END IF; --}
842
843 END LOOP; --}
844
845 --CLOSE sales_records;
846
847 FORALL i IN 1..v_rec_count
848 INSERT INTO ddr_b_rtl_sl_addtnl_msrs(rtl_org_cd
849 ,org_bsns_unit_id
850 ,mfg_sku_item_id
851 ,rtl_sku_item_id
852 ,day_cd
853 ,measure1
854 ,measure2
855 ,measure3
856 ,measure4
857 ,measure5
858 ,measure6
859 ,measure7
860 ,measure8
861 ,measure9
862 ,measure10
863 ) VALUES
864 (v_org_cd_tbl(i)
865 ,v_bsns_unit_id_tbl(i)
866 ,v_mfg_sku_item_id_tbl(i)
867 ,v_rtl_sku_item_id_tbl(i)
868 ,v_day_cd_tbl(i)
869 ,v_m1_tbl(i)
870 ,v_m2_tbl(i)
871 ,v_m3_tbl(i)
872 ,v_m4_tbl(i)
873 ,v_m5_tbl(i)
874 ,v_m6_tbl(i)
875 ,v_m7_tbl(i)
876 ,v_m8_tbl(i)
877 ,v_m9_tbl(i)
878 ,v_m10_tbl(i)
879 );
880 COMMIT;
881
882 END populate_addtnl_sl_msrs_pvt;
883
884 PROCEDURE calc_exception_measures(p_date_offset IN NUMBER
885 ,p_bsns_unit_cd IN VARCHAR2 DEFAULT NULL
886 ,p_rtl_org_cd IN VARCHAR2 DEFAULT NULL
887 ,p_calc_ifpl_excptn IN BOOLEAN
888 ,p_calc_oosim_excptn IN BOOLEAN
889 ,p_calc_npisales_excptn IN BOOLEAN
890 ,x_return_status OUT NOCOPY VARCHAR2
891 ,x_msg OUT NOCOPY VARCHAR2
892 ) IS
893 v_bsns_unit_id ddr_r_org_bsns_unit.org_bsns_unit_id%TYPE;
894 v_calc_ifpl_excptn VARCHAR(1):= NULL;
895 v_calc_oosim_excptn VARCHAR(1):= NULL;
896
897 BEGIN
898 IF p_bsns_unit_cd IS NOT NULL THEN --{
899 BEGIN
900 SELECT org_bsns_unit_id
901 INTO v_bsns_unit_id
902 FROM ddr_r_org_bsns_unit
903 WHERE org_cd = NVL(p_rtl_org_cd,org_cd)
904 AND bsns_unit_cd = p_bsns_unit_cd
905 --AND eff_to_dt IS NULL;
906 AND eff_to_dt = to_date('01012400','MMDDYYYY');
907 EXCEPTION
908 WHEN TOO_MANY_ROWS THEN
909 x_return_status := ddr_emd_util.error;
910 x_msg := 'org_cd and bsns_unit_cd combination not unique';
911 RETURN;
912 END;
913 END IF; --}
914 IF p_calc_npisales_excptn THEN --{
915 populate_new_items(p_date=>TRUNC(SYSDATE)
916 ,p_bsns_unit_id=>v_bsns_unit_id
917 ,p_mfg_sku_item_id=>NULL
918 ,p_rtl_org_cd=>p_rtl_org_cd
919 ,p_complete_refresh=>TRUE
923 IF x_return_status = ddr_emd_util.error THEN --{
920 ,x_return_status=>x_return_status
921 ,x_msg=>x_msg
922 );
924 RETURN;
925 END IF;
926 END IF; --}
927
928
929 IF p_calc_ifpl_excptn THEN --{
930 v_calc_ifpl_excptn := ddr_emd_util.c_populate_standard;
931 END IF;
932 IF p_calc_oosim_excptn THEN --{
933 v_calc_oosim_excptn := ddr_emd_util.c_populate_standard;
934 END IF;
935
936 populate_addtnl_sl_msrs_pvt(p_start_date=>TRUNC(SYSDATE - p_date_offset)
937 ,p_end_date=>SYSDATE
938 ,p_bsns_unit_id=>v_bsns_unit_id
939 ,p_mfg_sku_item_id=>NULL
940 ,p_rtl_sku_item_id=>NULL
941 ,p_rtl_org_cd=>p_rtl_org_cd
942 ,p_populate_m1=>NULL
943 ,p_populate_m2=>NULL
944 ,p_populate_m3=>v_calc_oosim_excptn
945 ,p_populate_m4=>v_calc_ifpl_excptn
946 ,p_populate_m5=>NULL
947 ,p_populate_m6=>v_calc_oosim_excptn
948 ,p_populate_m7=>NULL
949 ,p_populate_m8=>NULL
950 ,p_populate_m9=>NULL
951 ,p_populate_m10=>NULL
952 ,p_complete_refresh=>TRUE
953 ,x_return_status=>x_return_status
954 ,x_msg=>x_msg
955 );
956 x_return_status := NVL(x_return_status,ddr_emd_util.success);
957 EXCEPTION
958 WHEN OTHERS THEN
959 x_return_status := NVL(x_return_status,ddr_emd_util.error);
960 x_msg := NVL(x_msg,SQLERRM); --'Exception raised while processing the request');
961 END calc_exception_measures;
962
963
964 PROCEDURE calc_exception_measures_wrp(
965 p_date_offset IN NUMBER ,
966 p_bsns_unit_cd IN VARCHAR2 DEFAULT NULL ,
967 P_RTL_ORG_CD IN VARCHAR2 DEFAULT NULL ,
968 P_CALC_IFPL_EXCPTN IN VARCHAR2 DEFAULT 'Y' ,
969 p_calc_oosim_excptn IN VARCHAR2 DEFAULT 'Y' ,
970 p_calc_npisales_excptn IN VARCHAR2 DEFAULT 'Y' )
971 IS
972 x_return_status varchar2(1000);
973 X_MSG VARCHAR2(1000);
974 B_CALC_OOSIM_EXCPTN BOOLEAN;
975 B_CALC_IFPL_EXCPTN BOOLEAN;
976 B_CALC_NPISALES_EXCPTN BOOLEAN;
977 V_CALC_OOSIM_EXCPTN VARCHAR2(10);
978 V_CALC_IFPL_EXCPTN VARCHAR2(10);
979 V_CALC_NPISALES_EXCPTN VARCHAR2(10);
980 BEGIN
981 IF (P_CALC_IFPL_EXCPTN='Y') THEN
982 B_CALC_IFPL_EXCPTN :=TRUE;
983 V_CALC_IFPL_EXCPTN :='TRUE';
984 ELSE
985 B_CALC_IFPL_EXCPTN:=FALSE;
986 V_CALC_IFPL_EXCPTN:='FALSE';
987 END IF;
988 IF (p_calc_oosim_excptn='Y') THEN
989 B_CALC_OOSIM_EXCPTN :=TRUE;
990 V_CALC_OOSIM_EXCPTN :='TRUE';
991 ELSE
992 B_CALC_OOSIM_EXCPTN:=FALSE;
993 V_CALC_OOSIM_EXCPTN:='FALSE';
994 END IF;
995 IF (p_calc_npisales_excptn='Y') THEN
996 B_CALC_NPISALES_EXCPTN :=TRUE;
997 V_CALC_NPISALES_EXCPTN :='TRUE';
998 ELSE
999 B_CALC_NPISALES_EXCPTN:=FALSE;
1000 V_CALC_NPISALES_EXCPTN:='FALSE';
1001 END IF;
1002 ddr_emd_util.calc_exception_measures(p_date_offset => p_date_offset ,
1003 P_BSNS_UNIT_CD => P_BSNS_UNIT_CD ,
1004 P_RTL_ORG_CD => P_RTL_ORG_CD ,
1005 P_CALC_IFPL_EXCPTN =>B_CALC_IFPL_EXCPTN ,
1006 P_CALC_OOSIM_EXCPTN =>B_CALC_OOSIM_EXCPTN ,
1007 P_CALC_NPISALES_EXCPTN => B_CALC_NPISALES_EXCPTN ,
1008 X_RETURN_STATUS =>X_RETURN_STATUS ,
1009 X_MSG => X_MSG ) ;
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 x_return_status := NVL(x_return_status,ddr_emd_util.error);
1013 X_MSG := NVL(X_MSG,SQLERRM); --'Exception raised while processing the request');
1014 END calc_exception_measures_wrp;
1015
1016 END ddr_emd_util;