厂家调取销售数据语句

wbv123

select
CASE
  WHEN TO_CHAR(a.accdate, 'dd') >= 26
  THEN TO_CHAR(ADD_MONTHS(a.accdate, 1), 'yyyy')
  ELSE TO_CHAR(a.accdate, 'yyyy')
END AS year,  -- 年

CASE
  WHEN TO_CHAR(a.accdate, 'dd') >= 26
  THEN TO_CHAR(ADD_MONTHS(a.accdate, 1), 'mm')
  ELSE TO_CHAR(a.accdate, 'mm')
END AS month
,  --月
'AC202503141051' AS channel_erpno,  -- 商业编码
    'XXXXXXX司' AS channel_name,  -- 商业名称
    c.warename AS item_name, -- 产品名称
    c.warespec AS productsize, -- 产品规格
    b.makeno AS lotno, -- 批号

    (b.wareqty + b.minqty / b.stdtomin) * b.times AS qty, -- 数量

    b.stdprice AS price, -- 价格

    to_char(a.accdate,'yyyy-MM-dd') AS saleto_date, -- 销售日期

    f_get_orgname( a.busno) AS saleto_name, -- 销往单位
    g.factoryname AS factory -- 生产厂家

FROM
    XXXX.t_sale_h a
    JOIN XXXX.t_sale_d b ON a.saleno = b.saleno
    JOIN XXXX.t_ware c ON a.compid = c.compid AND b.wareid = c.wareid

    LEFT JOIN XXXX.t_factory g ON g.factoryid = c.factoryid
    LEFT JOIN XXXX.t_store_i i ON i.wareid = b.wareid AND i.batid = b.batid
WHERE
    a.accdate >= SYSDATE - 60
    AND  b.wareqty <> 0
   AND (g.factoryname  like '%XX%' or g.factoryname  like '%XXXX%'
    or g.factoryname  like '%XXXX%'  or g.factoryname  like '%XXXX%'
    or g.factoryname  like '%XXXX%'  or g.factoryname  like '%XXXX%')

wbv123

select
'AC20XXXX051' AS channel_erpno,  -- 商业编码
    'XXXX任公司' AS channel_name,  -- 商业名称
    c.warename AS item_name, -- 产品名称
    c.warespec AS productsize, -- 产品规格
    b.makeno AS lotno, -- 批号
   
    (b.wareqty + b.minqty / b.stdtomin) * b.times AS qty, -- 数量

    b.stdprice AS price, -- 价格
   
    a.accdate AS saleto_date, -- 销售日期
   
    f_get_orgname( a.busno) AS saleto_name, -- 销往单位
    g.factoryname AS factory -- 生产厂家

FROM
    XX.t_sale_h a
    JOIN XX.t_sale_d b ON a.saleno = b.saleno
    JOIN XX.t_ware c ON a.compid = c.compid AND b.wareid = c.wareid
   
    LEFT JOIN XX.t_factory g ON g.factoryid = c.factoryid
    LEFT JOIN XX.t_store_i i ON i.wareid = b.wareid AND i.batid = b.batid
WHERE
    a.accdate >= SYSDATE - 60
    AND  b.wareqty <> 0
   AND (g.factoryname  like '%XXX%' or g.factoryname  like '%XXX%'
    or g.factoryname  like '%XXX%'  or g.factoryname  like '%XXX%'
    or g.factoryname  like 'XXX%'  or g.factoryname  like '%XXX%');

wbv123

库存

select trunc(sysdate-1) as 库存时间,
       w.factoryname as 生产厂家,
       w.warecode as 产品代码,
       w.warename as 产品名称,
       w.warespec as 产品规格,
       ''  as  剂型,
       w.wareunit as 单位,
       i.makeno as 批号,
       d.wareqty as 数量,
       ''  as 供应商名称,
       par_CompanyCode           AS 商业代码,
       par_CompanyName           AS 商业名称
  from XXXX.t_store_d d
       join XXXX.t_store_i i on d.compid=i.compid and d.wareid=i.wareid and d.batid=i.batid
       join XXXX.v_ware w on w.compid=d.compid and w.wareid=d.wareid
where d.compid=2
   and d.wareqty <> 0
      and busno='2000'
   and ((w.warename like '%XXX%' and w.factoryname like '%XXX%')
or (w.warename like '%XXX%' and w.factoryname like '%XXX%')
or (w.warename like '%甲磺酸溴%' and w.factoryname like '%Doppel%')
or (w.warename like '%XXX%' and w.factoryname like '%德国%')
or (w.warename like '%XXX%' and w.factoryname like '%德国%')
or (w.warename like '%XXX%'and w.factoryname like '%法国百科达%')
)

wbv123

采购

select
trunc(c.execdate) as 入库时间,
a.factoryname as 生产厂家,
g.vencuscode as 供应商代码,
g.vencusname as 供应商名称,
a.warecode as 产品代码,
a.warename as 产品名称,
a.warespec as 产品规格,
''         as 剂型,
a.wareunit as 单位,
d.makeno   as 批号,
''         as 电子监管码,
d.wareqty  as 数量,
d.purprice as 含税单价,
d.purprice * d.wareqty  as 含税金额,
''         as 进货类型,
par_CompanyCode       AS 采购方代码,
par_CompanyName       AS 采购方名称
from XXXX.t_accept_h c
join XXXX.t_accept_d d on c.acceptno = d.acceptno
join XXXX.v_ware a on a.wareid = d.wareid and a.compid=c.compid
left join XXXX.t_vencus g on c.vencusno=g.vencusno and g.compid=c.compid
where c.status = 1
and c.compid=2
and c.execdate > sysdate - 60
and  d.wareqty <>0
AND （ (a.warename like '%xxxx%' and a.factoryname like '%xxx%')
or (a.warename like '%xxxx%' and a.factoryname like '%丹麦灵北%')
or (a.warename like '%xxx%' and a.factoryname like '%Doppel%')
or (a.warename like '%xxx%' and a.factoryname like '%德国%')
or (a.warename like '%酸胶囊%' and a.factoryname like '%德国%')
or (a.warename like '%xxx散%'and a.factoryname like '%法国百科达%')
)

wbv123

配送   
select
CASE
  WHEN TO_CHAR(a.execdate, 'dd') >= 26
  THEN TO_CHAR(ADD_MONTHS(a.execdate, 1), 'yyyy')
  ELSE TO_CHAR(a.execdate, 'yyyy')
END AS year,  -- 年
CASE
  WHEN TO_CHAR(a.execdate, 'dd') >= 26
  THEN TO_CHAR(ADD_MONTHS(a.execdate, 1), 'mm')
  ELSE TO_CHAR(a.execdate, 'mm')
END AS month,  --月
'AC202503141051' AS channel_erpno,  -- 商业编码
    '111111公司' AS channel_name,  -- 商业名称
    w.warename AS item_name, -- 产品名称
    w.warespec AS productsize, -- 产品规格
    b.makeno AS lotno, -- 批号
   b.wareqty AS qty, -- 数量
    b.distprice AS price, -- 价格
    to_char(a.execdate,'yyyy-MM-dd') AS saleto_date, -- 销售日期
    f_get_busnoname(a.objbusno) AS saleto_name, -- 销往单位
    w.factoryname as 生产厂家,
b.wareid as item_no,s.address as 门店地址
from t_dist_h  a left  join  YBCloud.med_store  g on a.objbusno=g.busno
left join s_busi s  on a.OBJBUSNO=s.busno ,t_dist_d  b
left join t_ware w on  b.wareid = w.wareid
where a.distno=b.distno  and a.billcode in ('DIS','ADD') and a.status=1
and    a.execdate >= SYSDATE - 60
    AND  b.wareqty <> 0
and   (w.factoryname   like  '%邛1111%'   or  w.factoryname   like  '%辽宁1111%');  

wbv123

select
CASE
  WHEN TO_CHAR(a.execdate, 'dd') >= 26
  THEN TO_CHAR(ADD_MONTHS(a.execdate, 1), 'yyyy')
  ELSE TO_CHAR(a.execdate, 'yyyy')
END AS year,  -- 年
CASE
  WHEN TO_CHAR(a.execdate, 'dd') >= 26
  THEN TO_CHAR(ADD_MONTHS(a.execdate, 1), 'mm')
  ELSE TO_CHAR(a.execdate, 'mm')
END AS month,  --月
'AC202503141051' AS channel_erpno,  -- 商业编码
    '1111111111111111公司' AS channel_name,  -- 商业名称
    w.warename AS item_name, -- 产品名称
    w.warespec AS productsize, -- 产品规格
    b.makeno AS lotno, -- 批号
   b.wareqty AS qty, -- 数量
    b.distprice AS price, -- 价格
    to_char(a.execdate,'yyyy-MM-dd') AS saleto_date, -- 销售日期
    f_get_busnoname(a.objbusno) AS saleto_name, -- 销往单位
    s.address as 门店地址 ,
    w.factoryname as 生产厂家,
b.wareid as item_no
from t_dist_h  a left  join  YBCloud.med_store  g on a.objbusno=g.busno
left join s_busi s  on a.OBJBUSNO=s.busno ,t_dist_d  b
left join t_ware w on  b.wareid = w.wareid
where a.distno=b.distno  and a.billcode in ('DIS','ADD') and a.status=1
and    a.execdate >= SYSDATE - 60
    AND  b.wareqty <> 0
and   (w.factoryname   like  '%111银%'   or  w.factoryname   like  '%111达%');  

wbv123

select
CASE
  WHEN TO_CHAR(a.execdate, 'dd') >= 26
  THEN TO_CHAR(ADD_MONTHS(a.execdate, 1), 'yyyy')
  ELSE TO_CHAR(a.execdate, 'yyyy')
END AS year,  -- 年
CASE
  WHEN TO_CHAR(a.execdate, 'dd') >= 26
  THEN TO_CHAR(ADD_MONTHS(a.execdate, 1), 'mm')
  ELSE TO_CHAR(a.execdate, 'mm')
END AS month,  --月
'AC202503141051' AS channel_erpno,  -- 商业编码
    '11111公司' AS channel_name,  -- 商业名称
    w.warename AS item_name, -- 产品名称
    w.warespec AS productsize, -- 产品规格
    b.makeno AS lotno, -- 批号
   b.wareqty AS qty, -- 数量
    b.distprice AS price, -- 价格
    to_char(a.execdate,'yyyy-MM-dd') AS saleto_date, -- 销售日期
    f_get_busnoname(a.objbusno) AS saleto_name, -- 销往单位
    s.address as 门店地址 ,
    w.factoryname as 生产厂家,
b.wareid as item_no
from t_dist_h  a left  join  YBCloud.med_store  g on a.objbusno=g.busno
left join s_busi s  on a.OBJBUSNO=s.busno ,t_dist_d  b
left join t_ware w on  b.wareid = w.wareid
where a.distno=b.distno  and a.billcode in ('DIS','ADD') and a.status=1
and    a.execdate >= SYSDATE - 60
    AND  b.wareqty <> 0
and   (w.factoryname  like '%1111%' or w.factoryname  like '%辽宁111%'
    or w.factoryname  like '%保定1111%'  or w.factoryname  like '%1111银%'
    or w.factoryname  like '%丹1111%'  or w.factoryname  like '%1111红%');

wbv123

select  w.*,b.*
from  t_dist_h a,t_dist_d  b ,v_ware w   
where  a.distno=b.distno and b.wareid=w.wareid
and  a.compid=w.compid and a.status=1
and a.billcode in ('DIS','ADD','DIR','ADR')
and a.notes  not like '%店间调拨单%'  
and (w.warename   like  '%脑心通胶囊%'   or  w. warename   like  '%香菊%')