海典H2 供应商查询语句

wbv123

采购订单流向

select
       ''   as po_id,
       a.billno as po_no,
      a.execdate as po_time,
       a.vencusno as enterprise_inner_code,
      d.vencusname as enterprise_name,
       a.makeno     as po_batch_no,
       a.wareqty   as po_quantity,
       a.makedate   as po_product_time,
       a.invalidate as po_effective_time,
       a.purprice   as po_price,
       b.warecode   as goods_in_sn,
       b.warename   as goods_name,
       b.fileno     as po_license,
       b.warespec   as po_specifications,
       b.wareunit  as po_unit,
       c.factoryname as po_manufacturer,
       ''                 as po_source
  FROM H2.T_RPT_ACCEPT A  , H2.T_WARE B, H2.T_FACTORY c , H2.T_VENCUS D
  WHERE A.WAREID = B.WAREID
   AND A.COMPID = B.COMPID
    AND B.FACTORYID = C.FACTORYID
    AND A.VENCUSNO = D.VENCUSNO
   AND A.COMPID = D.COMPID
  AND  c.factoryname   LIKE'%北京%'

wbv123

销售订单流向
select '' as so_id,
       sheet.distno as so_no,
       sheet.execdate as so_time,
       store.busno as enterprise_inner_code,
       store.orgname as enterprise_name,
       sheet_detail.makeno as so_batch_no,
       case
         when sheet.billcode in ('DIR', 'ADR') then
          -1 * sheet_detail.wareqty
         else
          sheet_detail.wareqty
       end as so_quantity,
       sheet_detail.makedate as so_product_time,
       sheet_detail.invalidate as so_effective_time,
       prod.lastsaleprice as so_price,
       prod.wareid as goods_in_sn,
       prod.warename as goods_name,
       prod.fileno as so_license,
       prod.warespec   as so_specifications,
       prod.wareunit as so_unit,
       prod.factoryname     as  so_manufacturer,
       '其它渠道销售' as so_source
  from h2.t_dist_h sheet
inner join h2.t_dist_d sheet_detail
    on sheet.distno = sheet_detail.distno
inner join h2.v_ware prod
    on sheet_detail.wareid = prod.wareid
   and prod.compid = sheet.compid
inner join h2.s_busi store
    on case
         when sheet.billcode in ('DIR', 'ADR') then
          sheet.srcbusno
         else
          sheet.objbusno
       end = store.busno
   and store.compid = sheet.compid
where prod.factoryname like '%北京%'
   and sheet.billcode in ('DIR', 'DIS', 'ADR', 'ADD')

wbv123

库存流向
select (w.warecode || d.makeno) as gb_id_no,
       max(d.CREATETIME) as gb_time,
sysdate as gb_date,
       w.warecode as in_sn,
       d.makeno as gb_batch_no,
       min(d.makedate) as gb_produce_time,
       min(d.invalidate) as gb_end_time,
       max(w.areaname) as gb_produce_address,
       max(t.wareqty) as gb_number,
       max(w.warename) as gb_name,
       max(w.fileno) as gb_license,
       max(w.warespec) as gb_specifications,
       max(w.wareunit) as gb_unit,
       max(w.factoryname) as gb_manufacturer
  from h2.t_store_d t, h2.v_ware w, h2.t_store_i d
where t.wareid = w.wareid
   and t.wareid = d.wareid
   and t.batid = d.batid
   and t.compid=w.compid
   and t.compid=d.compid
   and t.busno = '2000'
   and t.wareqty > 0
   and  w.factoryname   LIKE'%北京%'  
group by w.warecode， d.makeno

wbv123

连锁纯销售流向
select '' as so_id,
       h.saleno as so_no,
       h.accdate as so_time,
       d.busno as shop_no,
        s.orgname as shop_name,
       '' as enterprise_name,
       d.makeno as so_batch_no,
       d.wareqty as so_quantity,
       i.makedate as so_product_time,
       d.invalidate as so_effective_time,
       d.netprice as so_price,
       b.warecode as goods_in_sn,
       b.warename as goods_name,
       b.fileno as so_license,
       b.warespec as so_specifications,
       b.wareunit as so_unit,
       f.factoryname as so_manufacturer,
       '其它渠道销售' as so_source
  from  H2.t_sale_h h
  left join H2.t_sale_d d on h.saleno=d.saleno and   h.busno = d.busno
  left  join H2.s_busi  s on d.busno=s.busno
  left join H2.T_WARE B on h.compid=b.compid and d.wareid=b.wareid
  left join H2.t_store_i  i on h.compid=i.compid and d.batid=i.batid and d.wareid=i.wareid
  left join  H2.T_FACTORY f  on b.factoryid=f.factoryid
  where 1= 1
AND  f.factoryname   LIKE'%北京%'  

wbv123

连锁库存流向
select (s.busno||w.warecode || d.makeno) as gb_id_no,
       max(d.CREATETIME) as gb_time,
       sysdate as gb_date,
       s.busno as shop_no,
       s.orgname as shop_name,
       w.warecode as in_sn,
       d.makeno as gb_batch_no,
       min(d.makedate) as gb_produce_time,
       min(d.invalidate) as gb_end_time,
       max(w.areaname) as gb_produce_address,
       max(t.wareqty) as gb_number,
       max(w.warename) as gb_name,
       max(w.fileno) as gb_license,
       max(w.warespec) as gb_specifications,
       max(w.wareunit) as gb_unit,
       max(w.factoryname) as gb_manufacturer
  from h2.t_store_d t, h2.v_ware w, h2.t_store_i d,h2.s_busi s
where t.wareid = w.wareid
   and t.wareid = d.wareid
   and t.batid = d.batid
   and t.compid=w.compid
   and t.compid=d.compid
   and t.busno=s.busno
   and t.busno <> '2000'
   and t.wareqty > 0
   and  w.factoryname   LIKE'%北京%'  
group by w.warecode， d.makeno,s.busno,s.orgname