海典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

wbv123

SELECT
  a.accdate AS 销售日期,
  z.factoryname AS 生产厂家,
  '' AS 门店代码,
  d.orgname AS 门店名称,
  c.wareid AS 产品代码,
  c.warename AS 产品名称,
  c.warespec AS 产品规格,
  '' AS 有效期,
  c.wareunit AS 单位,
  b.makeno AS 批号,
  b.wareqty AS 数量,
  b.NETPRICE AS 含税单价,
  b.wareqty * b.NETPRICE AS 含税金额,
  '' AS 批准文号,
  '定西1111111111公司' AS 连锁名称,
  '' AS 连锁分公司名称,
  '' AS 动销类型
FROM
  t_sale_h a,
  t_sale_d b,
  t_ware c,
  t_factory z,
  s_busi d
WHERE
  a.saleno = b.saleno
  AND c.wareid = b.wareid
  AND d.busno = a.busno
  AND c.factoryid = z.factoryid
  AND a.accdate >=sysdate-60
and ((z.factoryname like '%111制药有限公司%' and  c.warename like '%萘普1111胶囊%')
or (z.factoryname like '%柏111技术（安徽）股份有限公司%' and  c.warename like '%复合11111营养饮%')
or (z.factoryname like '%1111健品有限公司%' and  c.warename like '%维生素C咀嚼片%')
or(z.factoryname like '%1111业有限公司%' and  (c.warename like '%强力枇杷露%'
or c.warename like '%肠炎宁颗粒%' or c.warename like '%复111香胃片%' or c.warename like '%复方111口服液%'
or c.warename like '%复1111无片%' or c.warename like '%复方111草滴丸%' or c.warename like '%抗1111炎片%'
or c.warename like '%复111竹沥液%' or c.warename like '%寿11111汁%' or c.warename like '%肿1111片%'))
or (z.factoryname like '%11111限公司%' and  (c.warename like '%肠炎宁片%'
or c.warename like '%牛黄上清胶囊%'))
or (z.factoryname like '%云111111陶药业有限公司%' and  (c.warename like '%感冒疏风颗粒%'
or c.warename like '%肠舒片%' or c.warename like '%感冒清热颗粒%' or c.warename like '%红草止鼾胶囊%'
or c.warename like '%红药胶囊%'or c.warename like '%消炎止咳片%'))
or (z.factoryname like '%浙11111物制药有限公司%' and  (c.warename like '%奥美拉唑肠溶胶囊%'
or c.warename like '%青霉素皮试剂%' or c.warename like '%乙酰半胱氨酸泡腾片%' or c.warename like '%注射用奥美拉唑钠%'))
or (z.factoryname like '%浙11111份有限公司%' and  (c.warename like '%铝碳酸镁咀嚼片%'
or c.warename like '%安神补脑颗粒%' or c.warename like '%积雪苷霜软膏%'))
or (z.factoryname like '%浙111111公司%' and  (c.warename like '%盐酸氨溴索口服溶液%'
or c.warename like '%复方鱼腥草合剂%' or c.warename like '%咳舒糖浆%' or c.warename like '%猴菇饮%'
or c.warename like '%三两半药酒%' or c.warename like '%麝香解痛膏%'or c.warename like '%麝香镇痛膏%'
or c.warename like '%肾宝合剂%'))
)

wbv123

配送数据  SELECT
    tii.execdate AS 日期,
    s_bill.billname 单据类型,
    f_get_orgname(tii.busno) 流向单位,
    t_vencus.vencusname 供应商名称,
    t_ware.warename 产品名称,
    t_ware.warecode 产品代码,
    t_ware.warespec 产品规格,
    t_ware.wareunit 单位,
    CASE WHEN tii.billcode = 'SAL' THEN -tii.inqty ELSE tii.inqty END 销售数量,
    t_store_i.makeno 批号,
    t_store_i.invalidate 有效期,
    tii.factoryname 生产厂家,
    '' 上级供应商
FROM
    t_store_inout_list tii
LEFT JOIN t_store_i
    ON t_store_i.compid = tii.compid
    AND t_store_i.wareid = tii.wareid
    AND t_store_i.batid = tii.batid
LEFT JOIN t_ware
    ON t_ware.compid = tii.compid
    AND t_ware.wareid = tii.wareid
LEFT JOIN t_vencus
    ON t_vencus.compid = t_store_i.compid
    AND t_vencus.vencusno = t_store_i.vencusno
LEFT JOIN s_bill
    ON tii.billcode = s_bill.billcode
WHERE
    t_store_i.compid = 2
AND tii.factoryname LIKE '%厂家%'

AND tii.billcode IN ('DIS','DIR')
AND tii.busno = 2000

wbv123

配送    SELECT
    tii.execdate AS 日期,
    s_bill.billname 单据类型,
    f_get_orgname(tii.busno) 流向单位,
    t_vencus.vencusname 供应商名称,
    t_ware.warename 产品名称,
    t_ware.warecode 产品代码,
    t_ware.warespec 产品规格,
    t_ware.wareunit 单位,
    CASE WHEN tii.billcode = 'SAL' THEN -tii.inqty ELSE tii.inqty END 销售数量,
    t_store_i.makeno 批号,
    t_store_i.invalidate 有效期,
    tii.factoryname 生产厂家,
    '' 上级供应商
FROM
    t_store_inout_list tii
LEFT JOIN t_store_i
    ON t_store_i.compid = tii.compid
    AND t_store_i.wareid = tii.wareid
    AND t_store_i.batid = tii.batid
LEFT JOIN t_ware
    ON t_ware.compid = tii.compid
    AND t_ware.wareid = tii.wareid
LEFT JOIN t_vencus
    ON t_vencus.compid = t_store_i.compid
    AND t_vencus.vencusno = t_store_i.vencusno
LEFT JOIN s_bill
    ON tii.billcode = s_bill.billcode
WHERE
    t_store_i.compid = 2
AND tii.factoryname LIKE '%福牌%'
AND tii.execdate >= to_date('$begTime','yyyy-mm-dd hh24:mi:ss')

AND tii.execdate < to_date('$endTime','yyyy-mm-dd hh24:mi:ss')
AND tii.billcode IN ('DIS','DIR')
AND tii.busno <> 888

wbv123

select
to_char(SYSDATE,'yyyy-mm-dd') 库存日期,
f_get_factoryname(w.factoryid) 生产厂家,
w.WARECODE 产品代码,
w.warename 产品名称,
w.warespec 产品规格,
i.makeno 批次,
sum(d.wareqty) 库存数量,
w.wareunit 单位,
'' 库存拥有者  
from t_store_d d ,t_store_i i ,t_ware w ,t_vencus v
where d.wareid=i.wareid and d.batid=i.batid and d.wareid=w.wareid
and v.vencusno=i.vencusno
and d.wareqty>0
and
group by f_get_factoryname(w.factoryid),w.WARECODE,
w.warename,w.warespec,i.makeno,d.wareqty,w.wareunit

wbv123

select
h.execdate 采购日期,
v.VENCUSNO 供应商代码,
v.vencusname 上级供应商,
f_get_factoryname(w.factoryid) 生产厂家,
w.WARECODE 产品代码,
w.warename 产品名称,
w.warespec 产品规格,
d.makeno 批次,
d.INVALIDATE 有效期,
d.wareqty 采购数量 ,
w.wareunit 单位,
d.PURPRICE  单价,
d.PURPRICE*d.wareqty 金额,
'' 流向单位  
from t_accept_d d ,t_accept_h h,t_vencus v ,t_ware w
where d.acceptno=h.acceptno
and h.vencusno=v.vencusno
and d.wareid=w.wareid
AND h.execdate>= TRUNC(SYSDATE - 60)
AND h.execdate < TRUNC(SYSDATE)