RMS 脚本

1. 销售核对

1.1. 运行程序

打开PLSQL软件-文件-新建-命令窗口

set serveroutput on;
exec rms.cmx_sale_check_sql_p;
--查询数据
SELECT * FROM rms.V_CMX_SALE_CHECK_ALL;

1.2. 查询销售差异

SELECT s.STORE 门店,
       s.store_name 门店名,
       nvl(T1.SALE_AMOUNT, 0) ERP销售金额,
       nvl(t2.pos_sale, 0) pos销售金额,
       nvl(t2.pos_sale, 0) - nvl(T1.SALE_AMOUNT, 0) 差异金额
  FROM (SELECT H.STORE,
               SUM(ROUND((NVL(I.QTY * I.UNIT_RETAIL, 0) -
                         (SELECT NVL(SUM(NVL(D.UNIT_DISCOUNT_AMT * D.QTY, 0)),
                                      0)
                             FROM SA_TRAN_DISC D
                            WHERE D.TRAN_SEQ_NO = I.TRAN_SEQ_NO
                              AND D.ITEM_SEQ_NO = I.ITEM_SEQ_NO)),
                         2)) SALE_AMOUNT
          FROM SA_TRAN_ITEM I, SA_TRAN_HEAD H, SA_STORE_DAY D
         WHERE H.STORE_DAY_SEQ_NO = D.STORE_DAY_SEQ_NO
           AND H.TRAN_SEQ_NO = I.TRAN_SEQ_NO
           AND D.BUSINESS_DATE = get_vdate -- 销售日期
           AND H.TRAN_TYPE <> 'DCLOSE'
         GROUP BY H.STORE) T1,
       (select t.store, sum(t.amount_tax) pos_sale
          from cmx.cmx_if_sale_real_time@RMS_NEWRA t
         group by t.store) t2,
       store s
 WHERE s.store in (
                   100017,
                   100035,
                   100182,
                   100218,
                   100239,
                   100013,
                   100301,
                   100302,
                   100303,
                   100304,
                   100025,
                   100028,
                   100027,
                   100185,
                   100025,
                   100027,
                   100028)
   and s.store = T1.STORE(+)
   and s.store = T2.store(+)
 ORDER BY t1.STORE;

2. 商品下发

insert into cmx_pub_item_master select im.item,'','','',get_vdate,im.status,'N','','','','','',user,sysdate,sysdate
from item_master im
where im.item_level >= im.tran_level and im.status = 'A' and
      not exists(select 1 from cmx_pub_item_master t where t.item=im.item)
      and im.item in (803111225);
/
insert into cmx_pub_uda_item select t.item,t.uda_id,'','LV','','','','','','U','N','','','','','', user,sysdate,sysdate
from uda_item_lov t,item_master im
where im.item = t.item  and im.item_level = im.tran_level
      and t.item in (803111225) ;
/

insert into cmx_pub_uda_item select t.item,t.uda_id,'','FF','','','','','','U','N','','','','','', user,sysdate,sysdate
from uda_item_ff t,item_master im
where im.item = t.item  and
      im.item_level = im.tran_level
   and t.item in (803111225) ;
/
insert into cmx_pub_item_loc_barcode select il.item,il.loc,t.barcode,'N','A','','','','',user,sysdate,sysdate
from item_loc il, (select im.item_parent item,im.item_number_type,im.item barcode,'ST' source_type,im.primary_ref_item_ind from item_master im where im.item_level > im.tran_level union all select t.item,t.barcode_type,t.bar_code,'CUST' source_type,'N' from cmx_item_barcode t) t
where il.item = t.item  and exists (select 1 from cmx_item_loc_barcode c where c.item = il.item and c.loc = il.loc and c.barcode=t.barcode) and not exists(select 1 from cmx_pub_item_loc_barcode s  where s.item = il.item and s.loc = il.loc and s.barcode=t.barcode) and il.status <> 'D'
   and t.item in (803111225);
/
insert into cmx_pub_item_loc select il.item,il.loc_type,il.loc, (select pl.physical_loc from v_cmx_physical_loc pl where pl.loc_type = il.loc_type and pl.loc = il.loc and rownum = 1), 'N','U','','','','','',user,sysdate,sysdate from item_loc il, item_master im where il.item = im.item and im.item_level = im.tran_level
   and im.item in (803111225);
/