数据逻辑
2026/1/23原创大约 3 分钟约 897 字
相关信息
按【HS.HANSHOW】这个视图读取数据，从 TYPE 字段读取，TYPE=2 为促销，TYPE=3 为会员
1. 字段说明表
| 字段 | 说明 | 备注 |
|---|---|---|
| VIP_PRICE | 会员价 | TYPE=3 |
| VIPSPEC_START_DATE | 会员开始日期 | |
| VIPSPEC_END_DATE | 会员结束日期 | |
| PRO_PRICE | 促销价格 | TYPE=2 |
| SPEC_START_DATE | 促销开始日期 | |
| SPEC_END_DATE | 促销结束日期 | |
| TYPE | 类型 | |
| YL1 | 复杂促销 | 当不为 1 时，显示复杂促销信息，复杂促销信息底部为红色 |
| YL4 | 是否缺货 | 0：缺货，1：不缺货 |
脚本
代码展示
CREATE OR REPLACE VIEW HS.HANSHOW_NEW AS
SELECT A.BRANCH_NO,
A.BRANCH_NAME,
A.ITEM_NO,
A.ITEM_SUBNO,
A.ITEM_NAME,
A.UNIT_NO,
A.PRODUCT_AREA,
A.ITEM_SIZE,
A.GRADE,
CASE
WHEN A.BARTYPENAME IN('生鲜折扣', '出清') THEN A.SALE_PRICE
/*商品为615固定价促销时，取商品最小零售价，如最小零售价为空，取商品当前原件代替，其他取商品原价*/
WHEN A.BARTYPENAME NOT IN('生鲜折扣', '出清') AND A.DISTYPE_CODE = '615' AND
A.UNIT_RETAIL IS NULL THEN A.PRO_PRICE
WHEN A.BARTYPENAME NOT IN('生鲜折扣', '出清') AND A.DISTYPE_CODE = '615' AND
A.UNIT_RETAIL - A.PRO_PRICE > 0 THEN
A.SALE_PRICE
WHEN A.BARTYPENAME NOT IN('生鲜折扣', '出清') AND A.DISTYPE_CODE = '615' AND
A.UNIT_RETAIL - A.PRO_PRICE <= 0 THEN
A.PRO_PRICE
ELSE
A.SALE_PRICE
END SALE_PRICE,
A.PRO_PRICE,
A.VIP_PRICE,
A.VIPSPEC_START_DATE,
A.VIPSPEC_END_DATE,
A.SPEC_START_DATE,
A.SPEC_END_DATE,
A.QRCODE,
CASE
WHEN (DISTYPE_CODE IS NULL AND A.BJ_JJBJ IS NULL) OR
A.BARTYPENAME IN ('生鲜折扣', '出清') OR
((A.DISTYPE_CODE = '615' AND A.BJ_JJBJ IS NULL AND
A.UNIT_RETAIL - A.PRO_PRICE <= 0) OR
(A.DISTYPE_CODE = '615' AND A.BJ_JJBJ IS NULL AND A.UNIT_RETAIL IS NULL))
THEN
1
/*当商品为615固定价促销时，判断最小零售价减去促销售价，结果大于0则取最小零售价，结果小于0则改为绿价签,当最小零售价为空时,用商品当前原价来判断*/
WHEN (DISTYPE_CODE IN ('617', '619') AND (yl1 ='1') AND A.BJ_JJBJ IS NULL OR
(DISTYPE_CODE = '615' AND
A.UNIT_RETAIL - A.PRO_PRICE > 0)) THEN
2
WHEN DISTYPE_CODE IN ('616', '618', '620') AND A.BJ_JJBJ IS NULL THEN
3
WHEN A.BJ_JJBJ = '1' THEN
1
ELSE
4
END TYPE,
NVL(A.YL1,'') YL1,
A.YL2,
A.YL3,
A.YL4,
A.YL5,
A.CATCHWEIGHT
FROM (SELECT MS.STORE AS BRANCH_NO,
MS.STORE_NAME AS BRANCH_NAME,
VP.BARTYPENAME,
DECODE(VP.BARTYPENAME,
'生鲜折扣',
VP.ID || 'C',
'出清',
VP.ID || 'C',
VP.ID) AS ITEM_NO,
--TO_CHAR(WMSYS.WM_CONCAT(VP.BARCODE)) AS ITEM_SUBNO,
vp.barcode AS ITEM_SUBNO,
--LISTAGG(vp.barcode, ',') WITHIN GROUP (ORDER BY vp.BARTYPE DESC) AS ITEM_SUBNO,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(VP.NAME,
'/kg',
''),
'/KG',
''),
'/公斤',
''),
'/500g',
''),
'/500G',
''),
'/500克',
''),
'/千克',
''),
'公斤',
'') AS ITEM_NAME,
VP.UNIT AS UNIT_NO,
VP.PRODUCTIONCITY AS PRODUCT_AREA,
'见包装' ITEM_SIZE,
'合格' GRADE,
VP.PRICE AS SALE_PRICE,
BM.UNIT_RETAIL,
CASE
WHEN DISTYPE_CODE IN ('615', '617', '619') AND
VP.BJ_JJBJ IS NULL THEN
(CASE
WHEN DM.CHANGE_TYPE IS NULL THEN
VP.PRICE
WHEN DM.CHANGE_TYPE = 0 THEN
VP.PRICE * (100 + DM.CHANGE_PERCENT) / 100
WHEN DM.CHANGE_TYPE = 1 THEN
VP.PRICE + DM.CHANGE_AMOUNT
ELSE
DM.CHANGE_AMOUNT
END)
ELSE
NULL
END PRO_PRICE,
CASE
WHEN DISTYPE_CODE IN ('616', '618', '620') AND
VP.BJ_JJBJ IS NULL THEN
(CASE
WHEN DM.CHANGE_TYPE IS NULL THEN
VP.PRICE
WHEN DM.CHANGE_TYPE = 0 THEN
VP.PRICE * (100 + DM.CHANGE_PERCENT) / 100
WHEN DM.CHANGE_TYPE = 1 THEN
VP.PRICE + DM.CHANGE_AMOUNT
ELSE
DM.CHANGE_AMOUNT
END)
ELSE
NULL
END VIP_PRICE,
CASE
WHEN DISTYPE_CODE IN ('616', '618', '620') AND
VP.BJ_JJBJ IS NULL THEN
DM.START_DATE
ELSE
NULL
END VIPSPEC_START_DATE,
CASE
WHEN DISTYPE_CODE IN ('616', '618', '620') AND
VP.BJ_JJBJ IS NULL THEN
DM.END_DATE
ELSE
NULL
END VIPSPEC_END_DATE,
CASE
WHEN DISTYPE_CODE IN ('615', '617', '619') AND
VP.BJ_JJBJ IS NULL THEN
DM.START_DATE
ELSE
NULL
END SPEC_START_DATE,
CASE
WHEN DISTYPE_CODE IN ('615', '617', '619') AND
VP.BJ_JJBJ IS NULL THEN
DM.END_DATE
ELSE
NULL
END SPEC_END_DATE,
NULL QRCODE,
DISTYPE_CODE,
VP.BJ_JJBJ,
P.PROMO_NAME YL1,
DM.START_DATE YL2,
DM.END_DATE YL3,
--decode(ii.best_selling_id,'否','N','Y' ) YL4,
/*decode(VP.CATCHWEIGHT,0,1,1，1,0 ) YL4,*/
CASE
WHEN iss.is_show IS NOT NULL AND iss.is_show = 0 THEN 1
WHEN (vp.CATCHWEIGHT =2 AND ii. operate_mode='经销') THEN 0 ELSE 1
END YL4,
NULL YL5,
VP.CATCHWEIGHT
FROM BHG.V_SPBARCODE VP
LEFT JOIN BHG.MOM_STORE MS
ON 1 = 1
LEFT JOIN BHG.V_ZK_DM DM
ON DM.ITEM_ID = VP.ID
AND SYSDATE BETWEEN START_DATE AND END_DATE
LEFT JOIN HS.PROMOTION_INFO P
ON P.ITEM_ID = VP.ID
LEFT JOIN BHG.BHG_ITEM_MAXDATE BM
ON VP.ID = BM.ITEM_NO
left join hs.item_information ii
on vp.ID=ii.item
LEFT JOIN bhg.item_stock_show iss ON vp.ID=iss.item
/* left join bhg.item_pro_price_no ipp
on vp.ID=ipp.item */
GROUP BY VP.BARTYPENAME,
P.PROMO_NAME,
MS.STORE,
MS.STORE_NAME,
DECODE(VP.BARTYPENAME,
'生鲜折扣',
VP.ID || 'C',
'出清',
VP.ID || 'C',
VP.ID),
vp.barcode,
VP.NAME,
VP.UNIT,
VP.PRODUCTIONCITY,
VP.PRICE,
DISTYPE_CODE,
BJ_JJBJ,
CHANGE_TYPE,
CHANGE_PERCENT,
CHANGE_AMOUNT,
START_DATE,
END_DATE,
BM.UNIT_RETAIL,
VP.CATCHWEIGHT,CASE
WHEN iss.is_show IS NOT NULL AND iss.is_show = 0 THEN 1
WHEN (vp.CATCHWEIGHT =2 AND ii. operate_mode='经销') THEN 0 ELSE 1
END) A
;