1. 销售核对
1.1. 运行程序
打开PLSQL软件-文件-新建-命令窗口
set serveroutput on;
exec rms.cmx_sale_check_sql_p;
--查询数据
SELECT * FROM rms.V_CMX_SALE_CHECK_ALL;
原创2025/8/14大约 1 分钟
打开PLSQL软件-文件-新建-命令窗口
set serveroutput on;
exec rms.cmx_sale_check_sql_p;
--查询数据
SELECT * FROM rms.V_CMX_SALE_CHECK_ALL;
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
select o.pos_no,t.tender_type_id,t.tender_type_desc, sum((case when o.value_sign='N' then -nvl(o.real_value,o.value_) else nvl(o.real_value,o.value_) end)) sale
from h_t_order o,h_t_order_pay p,mom_tender t
where o.id=p.o_id
and p.tender_type_id=t.tender_type_id
and o.belong_date between 20230101 and 20231231
group by o.pos_no,t.tender_type_id,t.tender_type_desc