促销信息

1. 当前有效促销

select item.ngroup 品类组,
       item.primary_supp 供应商编码,
       d.item_id 商品码,
       item.name 品名,
       item.barcode 条码,
       a.promo_id 促销编号,
       a.attribute2 促销级别,
       substr(a.start_date, 1, 8) 开始日期,
       substr(a.end_date, 1, 8) 结束日期,
       decode(jj_price, null, item.price, jj_price) 正常售价,
       item.DM_price 促销售价,
       decode(change_type, 2, change_amount, null) 固定价,
       (case
         when change_type = 2 then
          null
         else
          decode(Qual_Type, 1, '满额', 2, '满量', 0, '全部')
       end) 满减方式,
       (case
         when change_type != 2 then
          Qual_Value
         else
          null
       end) 满减条件,
       (case
         when change_type = 1 then
          change_amount
         else
          null
       end) 满减金额,
       (case
         when change_type = 0 then
          change_percent / 100
         else
          null
       end) 满减百分比,
       a.dm_number as dm_number
  from bhg.MOM_PRMPC_TPDTL a,
       bhg.MOM_PRMPC_TPGRP b,
       bhg.MOM_PRMPC_TGLST c,
       bhg.MOM_PRMPC_TLITM d,
       bhg.MOM_PRMPC_TPDSC ti,
       v_item_price        item
 where a.promo_comp_detail_id = b.PRMPC_ID
   and b.group_id = c.group_id
   and c.list_id = d.list_id
   and d.list_id = ti.list_id
   and d.item_id = item.id
   and sysdate <= to_date(a.end_date, 'yyyy-mm-dd hh24:mi:ss')
   and a.deleted = 0
   and d.deleted = 0
   and item.id=116738672

2. 复杂促销信息

select a.promo_name,
       d.list_id,
       d.item_id,
       to_date(a.start_date, 'yyyy-mm-dd hh24:mi:ss') start_date,
       to_date(a.end_date, 'yyyy-mm-dd hh24:mi:ss') end_date,
       case
         when a.dm_number = 0 then
          null
         else
          dm_number
       end dm_number,
       a.promo_comp_detail_id refno,
       a.attribute2 distype_code,
       a.member_promotion_indicator
  from MOM_PRMPC_TPDTL a,
       MOM_PRMPC_TPGRP b,
       MOM_PRMPC_TGLST c,
       MOM_PRMPC_TLITM d
 where a.promo_comp_detail_id = b.PRMPC_ID
   and b.group_id = c.group_id
   and c.list_id = d.list_id
   and a.attribute2 not in (615)
   and a.deleted = 0
   and to_date(a.end_date, 'yyyy-mm-dd hh24:mi:ss') >= sysdate
   and d.deleted = 0

3. 促销未执行

相关信息

检查MOM_PROMOPRIOR：促销定义表