[Home] [Help]
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;
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;
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:
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
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
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')
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
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
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
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
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
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)