[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_sql := 'select user from dual';
INSERT INTO DDR_B_EXCPTN_ITEM_DAY
(MFG_ORG_CD,
RTL_ORG_CD,
ORG_BSNS_UNIT_ID,
DAY_CD,
MFG_SKU_ITEM_ID,
RTL_SKU_ITEM_ID,
EXCPTN_TYP,
EXCPTN_QTY,
EXCPTN_AMT,
EXCPTN_SRC_CD,
CRTD_BY_DSR,
LAST_UPDT_BY_DSR,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES
(p_mfg_org_cd,
p_rtl_org_cd,
p_org_bsns_unit_id,
to_char(to_date(p_excptn_date, 'YYYY-MM-DD'), 'YYYYMMDD'),
p_mfg_sku_item_id,
p_rtl_sku_item_id,
p_excptn_type,
p_excptn_qty,
p_excptn_amt,
p_excptn_src_code,
p_user_id,
p_user_id,
-1,
SYSDATE,
-1,
SYSDATE,
-1);
PROCEDURE delete_exception(p_excptn_type IN VARCHAR2
, p_excptn_src_code IN VARCHAR2
, p_date_offset IN NUMBER DEFAULT 0) IS
BEGIN
DELETE FROM DDR_B_EXCPTN_ITEM_DAY
WHERE EXCPTN_TYP = p_excptn_type
AND EXCPTN_SRC_CD = p_excptn_src_code
AND DAY_CD >= to_char(SYSDATE - p_date_offset, 'YYYYMMDD');
END delete_exception;
PROCEDURE delete_all_exceptions(p_end_date IN DATE
,p_excptn_type IN VARCHAR2 DEFAULT NULL
,p_excptn_src_code IN VARCHAR2 DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg OUT NOCOPY VARCHAR2
) IS
BEGIN
x_return_status := ddr_emd_util.success;
DELETE FROM ddr_b_excptn_item_day
WHERE excptn_typ = NVL(p_excptn_type,excptn_typ)
AND excptn_src_cd = NVL(p_excptn_src_code,excptn_src_cd)
AND day_cd <= TO_CHAR(p_end_date, 'YYYYMMDD');
x_msg := 'Failed in ddr_emd_util.delete_all_exceptions '||SQLERRM;
END delete_all_exceptions;
PROCEDURE delete_all_exceptions_wrp(
p_end_date IN DATE ,
p_excptn_type IN VARCHAR2 DEFAULT NULL ,
P_EXCPTN_SRC_CODE IN VARCHAR2 DEFAULT NULL
) IS
X_RETURN_STATUS VARCHAR2(1000);
DDR_EMD_UTIL.DELETE_ALL_EXCEPTIONS(P_END_DATE=>P_END_DATE,
P_EXCPTN_TYPE => P_EXCPTN_TYPE,
P_EXCPTN_SRC_CODE => P_EXCPTN_SRC_CODE,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG => X_MSG
);
X_MSG := 'Failed in ddr_emd_util.delete_all_exceptions '||SQLERRM;
END delete_all_exceptions_wrp;
SELECT *
FROM ddr_r_item_bsns_unt_assc
WHERE eff_from_dt > TRUNC(p_date) - ddr_emd_util.new_item_period
AND rtl_org_cd = NVL(p_rtl_org_cd,rtl_org_cd)
AND rtl_bsns_unit_id = NVL(p_bsns_unit_id,rtl_bsns_unit_id)
AND mfg_sku_item_id = NVL(p_mfg_sku_item_id,mfg_sku_item_id)
--AND eff_to_dt IS NULL;
SELECT MAX(rtl_sku_item_id) rtl_sku_item_id -- This is temporary workaround. This is a bug
FROM ddr_r_rtl_sku_item
WHERE rtl_org_cd = p_org_cd
AND glbl_item_id = p_glbl_item_id
AND glbl_item_id_typ = p_glbl_item_id_typ;
SELECT MIN(day_cd) first_sl_date
FROM ddr_b_rtl_sl_rtn_itm_day
WHERE org_bsns_unit_id = p_org_bsns_unit_id
AND mfg_sku_item_id = p_mfg_sku_item_id
AND rtl_sku_item_id = p_rtl_sku_item_id
AND day_cd >= p_from_day_cd
AND sls_qty_prmry > 0;
INSERT INTO ddr_b_new_item_no_sls(rtl_org_cd
,rtl_bsns_unit_id
,mfg_sku_item_id
,rtl_sku_item_id
,new_item_srt_dt
,new_item_end_dt
,frst_sl_dt
) VALUES
(v_org_cd_tbl(i)
,v_bsns_unit_id_tbl(i)
,v_mfg_sku_item_id_tbl(i)
,v_rtl_sku_item_id_tbl(i)
,v_new_item_strt_dt_tbl(i)
,v_new_item_end_dt_tbl(i)
,v_first_sl_date_tbl(i)
);
v_org_cd_tbl.DELETE;
v_bsns_unit_id_tbl.DELETE;
v_mfg_sku_item_id_tbl.DELETE;
v_rtl_sku_item_id_tbl.DELETE;
v_new_item_strt_dt_tbl.DELETE;
v_new_item_end_dt_tbl.DELETE;
v_first_sl_date_tbl.DELETE;
INSERT INTO ddr_b_new_item_no_sls(rtl_org_cd
,rtl_bsns_unit_id
,mfg_sku_item_id
,rtl_sku_item_id
,new_item_srt_dt
,new_item_end_dt
,frst_sl_dt
) VALUES
(v_org_cd_tbl(i)
,v_bsns_unit_id_tbl(i)
,v_mfg_sku_item_id_tbl(i)
,v_rtl_sku_item_id_tbl(i)
,v_new_item_strt_dt_tbl(i)
,v_new_item_end_dt_tbl(i)
,v_first_sl_date_tbl(i)
);
SELECT frcst_sls_qty_prmry
FROM ddr_b_sls_frcst_item_day
WHERE day_cd = p_sls_rec.day_cd
AND org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id
AND frcst_typ = 'TOTAL'
AND frcst_purp = 'SALES'
ORDER BY frcst_vrsn desc;
SELECT median(sls_qty_prmry) median_sales
FROM ddr_b_rtl_sl_rtn_itm_day
WHERE (day_cd = TO_CHAR(v_day-7,'YYYYMMDD')
OR day_cd = TO_CHAR(v_day-14,'YYYYMMDD')
OR day_cd = TO_CHAR(v_day-21,'YYYYMMDD')
OR day_cd = TO_CHAR(v_day-28,'YYYYMMDD')
OR day_cd = TO_CHAR(v_day-35,'YYYYMMDD')
)
AND org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id;
SELECT median(sls_qty_prmry) median_sales
FROM ddr_b_rtl_sl_rtn_itm_day
WHERE day_cd > TO_CHAR(v_day-180,'YYYYMMDD')
AND day_cd < TO_CHAR(v_day,'YYYYMMDD')
AND org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id
AND prmtn_flag = 'Y';
SELECT wk_strt_dt
INTO v_wk_strt_dt
FROM ddr_r_base_day_dn_mv
WHERE day_cd = p_sls_rec.day_cd
AND clndr_cd = mfg_org_cd||'-BSNS';
SELECT AVG(sls_qty_prmry)
INTO p_avg_sls
FROM ddr_b_rtl_sl_rtn_itm_day
WHERE day_cd BETWEEN TO_CHAR(v_wk_strt_dt-28,'YYYYMMDD')
AND TO_CHAR(v_wk_strt_dt,'YYYYMMDD')
AND org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id;
SELECT MIN(prmtn_price_amt_rpt) min_prmtn_price
FROM ddr_b_prmtn_pln
WHERE org_bsns_unit_id = p_sls_rec.org_bsns_unit_id
AND mfg_sku_item_id = p_sls_rec.mfg_sku_item_id
AND rtl_sku_item_id = p_sls_rec.rtl_sku_item_id
AND TO_DATE(p_sls_rec.day_cd,'YYYYMMDD') BETWEEN prmtn_from_dt AND prmtn_to_dt;
SELECT MAX(expctd_sls)
INTO get_sls_threshold_max_exp_sls
FROM ddr_r_excptn_sls_thrshld;
SELECT min_thrshld_actl_sls
INTO p_sls_threshold
FROM ddr_r_excptn_sls_thrshld
WHERE expctd_sls = ROUND(v_exp_sls);
SELECT *
FROM ddr_b_rtl_sl_rtn_itm_day
WHERE day_cd > TO_CHAR((p_start_date-1),'YYYYMMDD')
AND day_cd < TO_CHAR((p_end_date+1),'YYYYMMDD')
AND rtl_org_cd = NVL(p_rtl_org_cd,rtl_org_cd)
AND org_bsns_unit_id = NVL(p_bsns_unit_id,org_bsns_unit_id)
AND mfg_sku_item_id = NVL(p_mfg_sku_item_id,mfg_sku_item_id)
AND rtl_sku_item_id = NVL(p_rtl_sku_item_id,rtl_sku_item_id)
ORDER BY org_bsns_unit_id,mfg_sku_item_id,rtl_sku_item_id,day_cd asc;
INSERT INTO ddr_b_rtl_sl_addtnl_msrs(rtl_org_cd
,org_bsns_unit_id
,mfg_sku_item_id
,rtl_sku_item_id
,day_cd
,measure1
,measure2
,measure3
,measure4
,measure5
,measure6
,measure7
,measure8
,measure9
,measure10
) VALUES
(v_org_cd_tbl(i)
,v_bsns_unit_id_tbl(i)
,v_mfg_sku_item_id_tbl(i)
,v_rtl_sku_item_id_tbl(i)
,v_day_cd_tbl(i)
,v_m1_tbl(i)
,v_m2_tbl(i)
,v_m3_tbl(i)
,v_m4_tbl(i)
,v_m5_tbl(i)
,v_m6_tbl(i)
,v_m7_tbl(i)
,v_m8_tbl(i)
,v_m9_tbl(i)
,v_m10_tbl(i)
);
v_org_cd_tbl.DELETE;
v_bsns_unit_id_tbl.DELETE;
v_mfg_sku_item_id_tbl.DELETE;
v_rtl_sku_item_id_tbl.DELETE;
v_day_cd_tbl.DELETE;
v_m1_tbl.DELETE;
v_m2_tbl.DELETE;
v_m3_tbl.DELETE;
v_m4_tbl.DELETE;
v_m5_tbl.DELETE;
v_m6_tbl.DELETE;
v_m7_tbl.DELETE;
v_m8_tbl.DELETE;
v_m9_tbl.DELETE;
v_m10_tbl.DELETE;
INSERT INTO ddr_b_rtl_sl_addtnl_msrs(rtl_org_cd
,org_bsns_unit_id
,mfg_sku_item_id
,rtl_sku_item_id
,day_cd
,measure1
,measure2
,measure3
,measure4
,measure5
,measure6
,measure7
,measure8
,measure9
,measure10
) VALUES
(v_org_cd_tbl(i)
,v_bsns_unit_id_tbl(i)
,v_mfg_sku_item_id_tbl(i)
,v_rtl_sku_item_id_tbl(i)
,v_day_cd_tbl(i)
,v_m1_tbl(i)
,v_m2_tbl(i)
,v_m3_tbl(i)
,v_m4_tbl(i)
,v_m5_tbl(i)
,v_m6_tbl(i)
,v_m7_tbl(i)
,v_m8_tbl(i)
,v_m9_tbl(i)
,v_m10_tbl(i)
);
SELECT org_bsns_unit_id
INTO v_bsns_unit_id
FROM ddr_r_org_bsns_unit
WHERE org_cd = NVL(p_rtl_org_cd,org_cd)
AND bsns_unit_cd = p_bsns_unit_cd
--AND eff_to_dt IS NULL;