视图
CREATE OR REPLACE VIEW CV_阿斯利康_采购 AS
SELECT a.execdate AS 日期,
d.vencuscode AS 销售方代码,
d.vencusname AS 销售方名称,
'' AS 采购方代码,
'XXX有限公司' AS 采购方名称,
c.warecode AS 产品代码,
c.warename AS 产品名称,
c.warespec AS 产品规格,
b.makeno AS 批号,
b.wareqty AS 数量,
c.wareunit AS 单位,
b.purprice AS 单价,
b.purprice * b.wareqty AS 金额,d.vencusname as 渠道外标识
FROM t_accept_h a
JOIN t_accept_d b
ON a.acceptno = b.acceptno
JOIN t_ware c
ON b.wareid = c.wareid
and a.compid = c.compid
JOIN t_vencus d
ON a.vencusno = d.vencusno
and a.compid = d.compid
JOIN t_ware_ext ext
ON c.wareid = ext.wareid
and ext.compid = c.compid
WHERE (a.execdate >= sysdate - 180)
and b.wareid in (select wareid from t_ware_class_base where compid = a.compid and classgroupno ='12' and classcode='1204');
CREATE OR REPLACE VIEW CV_阿斯利康_库存 AS
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') AS 日期,
h.vencuscode AS 供应商代码,
h.vencusname AS 供应商名称,
'' AS 公司代码,
'XXX有限公司' AS 公司名称,
t_ware.warecode AS 产品代码,
t_ware.warename AS 产品名称,
t_ware.warespec AS 产品规格,
i.makeno AS 批号,
c.wareqty AS 数量,
t_ware.wareunit AS 单位
FROM t_store_d c,t_store_i i,t_ware,t_vencus_base h
WHERE c.wareid = i.wareid
AND c.batid = i.batid and i.vencusno = h.vencusno
and c.compid=i.compid and t_ware.compid=2
and c.compid=t_ware.compid
--AND c.makeno = i.makeno
AND c.wareid = t_ware.wareid
AND c.busno = '2000'
AND c.wareqty <> '0'
AND t_Ware.wareid IN (SELECT b.wareid FROM t_ware_class_base b WHERE CLASSGROUPNO='12' AND classcode='1204')
;
CREATE OR REPLACE VIEW CV_阿斯利康_零售 AS
SELECT h.vencuscode AS 供应商代码,
h.vencusname AS 供应商名称,
'' AS 连锁总部代码,
'XXX有限公司' AS 连锁总部名称,
TO_CHAR(c.accdate, 'yyyy-mm-dd') AS 日期,
TO_CHAR( b.finaltime,'hh24:mi:ss') AS 时间,
--CONVERT(CHAR(10), b.finaltime, 108) AS 时间,
c.busno AS 门店代码,
f_get_busnoname(c.busno) AS 门店名称,
t.warecode AS 产品代码,
t.warename AS 产品名称,
t.warespec AS 产品规格,
'' AS 产品剂型,
c.makeno AS 批号,
round((c.wareqty+c.minqty/c.stdtomin)*c.times,2) AS 纯销数量,
t.wareunit AS 单位,
c.netprice AS 销售单价,
round((c.wareqty+c.minqty/c.stdtomin)*c.times*c.netprice,2) AS 销售金额,
c.saleno AS 零售单流水号,
'' AS 促销码,
'P' AS 数据类型
FROM t_sale_d c,t_sale_h b,t_ware t,t_store_i i,t_vencus_base h
WHERE c.saleno = b.saleno
AND c.busno = b.busno
AND b.compid = t.compid
and b.compid = i.compid
and t.compid=2
AND c.wareid = t.wareid
and c.wareid = i.wareid
AND c.batid = i.batid
and i.vencusno = h.vencusno
AND t.wareid IN (SELECT b.wareid FROM t_ware_class_base b WHERE CLASSGROUPNO='12' AND classcode='1204')
AND c.accdate>=SYSDATE-180
;
CREATE OR REPLACE VIEW CV_阿斯利康_销售 AS
SELECT TO_CHAR(a.execdate, 'yyyy-mm-dd') AS 日期,
h.vencuscode AS 供应商代码,
h.vencusname AS 供应商名称,
-- CONVERT(varchar(100),a.execdate, 23) AS
'' AS 销售方代码,
'XXX有限公司' AS 销售方名称,
CASE
WHEN a.billcode IN ('DIS', 'ADD') THEN
a.objbusno
WHEN a.billcode IN ('DIR', 'ADR') THEN
a.srcbusno
END AS 采购方代码,
CASE
WHEN a.billcode IN ('DIS', 'ADD') THEN
f_get_orgname(a.objbusno)
WHEN a.billcode IN ('DIR', 'ADR') THEN
f_get_orgname(a.srcbusno)
END AS 采购方名称,
t_ware.warecode AS 产品代码,
t_ware.warename AS 产品名称,
t_ware.warespec AS 产品规格,
c.makeno AS 批号,
CASE
WHEN a.billcode IN ('DIS', 'ADD') THEN
1
ELSE
-1
END * c.wareqty AS 数量,
t_ware.wareunit AS 单位,
c.purprice AS 单价,
CASE
WHEN a.billcode IN ('DIS', 'ADD') THEN
1
ELSE
-1
END * c.wareqty * c.purprice AS 金额
FROM t_dist_d c
LEFT JOIN t_store_i i ON c.wareid = i.wareid AND c.batid = i.batid AND c.makeno = i.makeno,
t_dist_h a,t_ware,t_vencus_base h
WHERE c.wareid = t_ware.wareid
AND a.distno = c.distno and i.vencusno = h.vencusno
and a.compid=t_ware.compid
-- and t_ware.wareid=u_ware_ext.wareid
AND a.billcode IN ('DIS', 'ADD', 'DIR', 'ADR')
AND a.status = 1 and t_ware.compid=2
AND NVL(c.wareqty, 0) <> '0'
AND t_Ware.wareid IN (SELECT b.wareid FROM t_ware_class_base b WHERE CLASSGROUPNO='12' AND classcode='1204')
-- and TO_CHAR(a.execdate, 'yyyy-mm-dd')>='2023-04-01'
AND a.execdate >= SYSDATE - 180
;
create or replace view cv_拜耳_采购数据 as
select
to_char(a.execdate,'yyyy/mm/dd') as 日期,
--a.acceptno as 入库单号,
s.vencuscode AS 销售方代码,
s.vencusname as 销售方名称,
--'' as 供应商城市,
'' as 采购方代码,
'' as 采购方名称,
q.warecode as 产品代码,
q.warename as 产品名称,
q.warespec as 产品规格,b.makeno as 批号, b.wareqty AS 数量,
q.wareunit as 单位,b.purprice as 单价,
round(b.wareqty * b.purprice,4) as 金额,
nvl(a.notes,'') as 附加说明,
to_char(nvl(a.createtime,a.lasttime),'yyyy/mm/dd') as 创建时间
--y.factoryname as 生产厂家,
--q.fileno AS 批准文号,
--to_char(b.invalidate,'yyyymmdd') as 产品效期
from t_accept_h a,t_accept_d b,t_ware q,t_vencus s ,t_factory y,s_busi
where a.acceptno=b.acceptno and a.vencusno = s.vencusno and a.compid = q.compid and a.compid = s.compid and a.busno = s_busi.busno and a.compid = s_busi.compid
and b.wareid=q.wareid and q.factoryid = y.factoryid
and a.status in (1,3)
and a.billcode = 'ACC'
and a.compid = 2
and f_get_classcode('12',q.warecode,q.compid)='1202'
and trunc(a.execdate) >= trunc(sysdate)-180
union all
select
to_char(a.execdate,'yyyy/mm/dd') as 日期,
--a.acceptno as 入库单号,
s.vencuscode AS 销售方代码,
s.vencusname as 销售方名称,
'' as 采购方代码,
'' as 采购方名称,
--'' as 供应商城市,
q.warecode as 产品代码,
q.warename as 产品名称,
q.warespec as 产品规格,b.makeno as 批号,-1* b.returnqty AS 数量,
q.wareunit as 单位,b.purprice as 单价,
round(-1* b.returnqty * b.purprice,4) as 金额,
nvl(a.notes,'') as 附加说明,
to_char(nvl(a.createtime,a.lasttime),'yyyy/mm/dd') as 创建时间
--y.factoryname as 生产厂家,
--q.fileno AS 批准文号,
--to_char(b.invalidate,'yyyymmdd') as 产品效期
from t_reaccept_h a,t_reaccept_d b,t_ware q,t_vencus s ,t_factory y,s_busi
where a.reacceptno=b.reacceptno and a.vencusno = s.vencusno and a.compid = q.compid and a.compid = s.compid and a.busno = s_busi.busno and a.compid = s_busi.compid
and b.wareid=q.wareid and q.factoryid = y.factoryid
and a.status in (1,3)
-- and a.billcode = 'ACC'
and a.compid = 2
and f_get_classcode('12',q.warecode,q.compid)='1202'
and trunc(a.execdate) >= trunc(sysdate)-180
;
CREATE OR REPLACE VIEW CV_拜耳_库存数据 AS
SELECT to_char(sysdate,'yyyymmdd') AS 日期,
'' as 公司代码,
'' as 公司名称,
q.warecode AS 产品代码,
q.warename AS 产品名称,
q.warespec AS 产品规格,i.makeno as 批号,c.wareqty AS 数量,
q.wareunit AS 单位,
--y.factoryname as 生产厂家,
'' as 附加说明,
to_char(sysdate,'yyyymmdd') AS 创建时间
-- to_char(i.invalidate,'yyyymmdd') as 产品效期,
--q.fileno AS 批准文号,
--'总仓库存' as 库存状态
FROM t_store_d c,t_store_i i,t_ware q ,t_factory y,s_busi
WHERE c.wareid = i.wareid and c.compid = q.compid
and c.busno = s_busi.busno and c.compid = s_busi.compid
and c.batid = i.batid and q.factoryid = y.factoryid
AND c.wareid = q.wareid
and c.compid = 2 and s_busi.orgtype = '10'
and c.wareqty <> 0
and f_get_classcode('12',q.warecode,q.compid)='1202'
;
CREATE OR REPLACE VIEW CV_拜耳_配送数据 AS
SELECT to_char(a.execdate,'yyyymmdd') AS 日期,
to_char(s_busi.busno) as 销售方代码,
'' as 销售方名称,
--a.distno as 销售单据号,
--case when a.billcode in ('DIS','ADD') then '销售单'
-- when a.billcode in ('DIR','ADR') then '销售退仓单' end as 单据类型,
case when a.billcode in ('DIS','ADD') then to_char(a.objbusno)
when a.billcode in ('DIR','ADR') then to_char(a.srcbusno) end as 采购方代码,
case when a.billcode in ('DIS','ADD') then (select orgname from s_busi where busno = a.objbusno and compid = a.compid )
when a.billcode in ('DIR','ADR') then (select orgname from s_busi where busno = a.srcbusno and compid = a.compid ) end as 采购方名称,
q.warecode AS 产品编码,
q.warename AS 产品名称,
q.warespec AS 产品规格,
c.makeno AS 批号,
case when a.billcode in ('DIS','ADD') then 1 else -1 end * c.wareqty AS 数量,
q.wareunit AS 单位, c.purprice AS 单价,
(case when a.billcode in ('DIS','ADD') then 1 else -1 end * c.wareqty) * c.purprice AS 金额,
-- '' as 送货地址,
'' as 发往城市,
-- case when a.billcode in ('DIS','ADD') then (select orgname from s_busi where busno = a.objbusno and compid = a.compid )
-- when a.billcode in ('DIR','ADR') then (select orgname from s_busi where busno = a.srcbusno and compid = a.compid ) end as 分公司名称,
-- '' as 分公司城市,
'' AS 采购方渠道 ,
'' AS 附加说明 ,
to_char(a.execdate,'yyyymmdd') AS 创建时间
-- y.factoryname as 生产厂家,
-- to_char(i.invalidate,'yyyymmdd') as 产品效期,
-- q.fileno AS 批准文号
FROM t_dist_d c
left join t_store_i i on c.wareid=i.wareid and c.batid = i.batid
,t_dist_h a,t_ware q,t_factory y,s_busi
WHERE c.wareid = q.wareid and a.compid = q.compid and q.factoryid = y.factoryid
and a.distno = c.distno and a.compid = s_busi.compid
and case when a.billcode in ('DIS','ADD') then a.srcbusno else a.objbusno end = s_busi.busno
and a.billcode in ('DIS','ADD','DIR','ADR')
and a.status in (1,3)
and nvl(c.wareqty,0)<>0 and a.compid = 2
and nvl(a.notes,'.') not like '期初库存导入%'
and f_get_classcode('12',q.warecode,q.compid)='1202'
and trunc(a.execdate) >= trunc(sysdate-180)
and TO_CHAR(a.execdate, 'yyyy-mm-dd')>='2023-04-01'
;
CREATE OR REPLACE VIEW CV_拜耳_销售数据 AS
SELECT to_char(c.accdate, 'yyyy/mm/dd') AS 日期,
-- c.saleno as 销售单据号,
-- '零售单据' as 单据类型,
to_char(s_busi.busno) as 销售方代码,
'' as 销售方名称,
'1' AS 采购方代码,
'顾客' AS 采购方名称,
q.warecode AS 产品编码,
q.warename as 产品名称,
q.warespec AS 产品规格,
c.makeno AS 批号,
round((c.wareqty + c.minqty / c.stdtomin) * c.times, 4) as 数量,
q.wareunit AS 单位,
c.netprice AS 单价,
round((c.wareqty + c.minqty / c.stdtomin) * c.times * c.netprice, 4) as 金额,
'' as 发往城市,
'' as 采购方渠道,
'' as 附加说明,
to_char(c.accdate, 'yyyy/mm/dd') AS 创建时间
--y.factoryname as 生产厂家,
--to_char(d.invalidate,'yyyymmdd') as 产品效期,
--q.fileno AS 批准文号
FROM t_sale_d c
left join t_store_i d
on c.wareid = d.wareid
and c.batid = d.batid
left join (select distinct h.compid, h.busno, h.cfno, d.wareid, h.username
from t_remote_prescription_h h, t_remote_prescription_d d
where h.cfno = d.cfno) f
on c.busno = f.busno
and c.cfno = f.cfno
and c.wareid = f.wareid, t_sale_h b, t_ware q, t_factory y, s_busi
WHERE c.saleno = b.saleno
and c.busno = b.busno
and c.wareid = q.wareid
and q.factoryid = y.factoryid
and b.compid = q.compid
and b.busno = s_busi.busno
and b.compid = s_busi.compid
AND b.compid = 2
and c.wareid in (select wareid
from t_ware_class_base
where compid = b.compid
and classgroupno = '88'
and classcode = '8806')
and trunc(c.accdate) >= trunc(sysdate - 180)
;
CREATE OR REPLACE VIEW CV_辉瑞_门店进货 AS
SELECT to_char(a.execdate,'yyyymmdd') AS 入库日期,
a.distno as 入库单号,
case when a.billcode in ('DIS','ADD') then a.srcbusno
when a.billcode in ('DIR','ADR') then a.objbusno end as 供应商ID,
case when a.billcode in ('DIS','ADD') then (select orgname from s_busi where busno = a.srcbusno and compid = a.compid )
when a.billcode in ('DIR','ADR') then (select orgname from s_busi where busno = a.objbusno and compid = a.compid ) end as 供应商名称,
'' as 供应商城市,
q.warecode AS 产品编码,
q.warename AS 产品名称,
q.warespec AS 产品规格,
q.wareunit AS 单位,
y.factoryname as 生产厂家,
case when a.billcode in ('DIS','ADD') then 1 else -1 end * c.wareqty AS 采购数量,
c.purprice AS 采购价格,
c.makeno AS 批号,
to_char(i.invalidate,'yyyymmdd') as 产品效期,
q.fileno AS 批准文号
FROM t_dist_d c
left join t_store_i i on c.wareid=i.wareid and c.batid = i.batid
,t_dist_h a,t_ware q,t_factory y
WHERE c.wareid = q.wareid and a.compid = q.compid and q.factoryid = y.factoryid
and a.distno=c.distno
and a.billcode in ('DIS','ADD','DIR','ADR')
and a.status in (1,3)
and nvl(c.wareqty,0)<>'0'
and nvl(a.notes,'.') not like '期初库存导入%'
and c.wareid in (select wareid from t_ware_class_base where compid = a.compid and classgroupno ='12' and classcode='1203')
and trunc(a.execdate) >= trunc(sysdate-120);
CREATE OR REPLACE VIEW CV_辉瑞_门店库存 AS
SELECT to_char(sysdate,'yyyymmdd') AS 库存日期,
q.warecode AS 产品编码,
q.warename AS 产品名称,
q.warespec AS 产品规格,
q.wareunit AS 单位,
y.factoryname as 生产厂家,
c.wareqty AS 库存数量,
i.makeno as 批号,
to_char(i.invalidate,'yyyymmdd') as 产品效期,
q.fileno AS 批准文号,
'门店库存' as 库存状态
FROM t_store_d c,t_store_i i,t_ware q,t_factory y
WHERE c.wareid=i.wareid and c.batid = i.batid
and c.compid = q.compid and q.factoryid = y.factoryid
AND c.wareid= q.wareid
and c.wareqty<>'0'
and c.wareid in (select wareid from t_ware_class_base where compid = c.compid and classgroupno ='12' and classcode='1203');
CREATE OR REPLACE VIEW CV_辉瑞_门店零售 AS
SELECT to_char(c.accdate,'yyyymmdd') AS 销售日期,
c.saleno as 销售单据号,
'零售单据' as 单据类型,
f.username as 客户名称,
'' as 客户ID,
'' as 客户城市,
'' as 送货地址,
'' as 分公司名称,
'' as 分公司城市,
q.warecode AS 产品编码,
q.warename as 产品名称,
q.warespec AS 产品规格,
q.wareunit AS 单位,
y.factoryname as 生产厂家,
c.makeno AS 批号,
to_char(d.invalidate,'yyyymmdd') as 产品效期,
round((c.wareqty+c.minqty/c.stdtomin)*c.times,4) as 销售数量,
c.netprice AS 含税单价,
q.fileno AS 批准文号
FROM t_sale_d c
left join t_store_i d on c.wareid=d.wareid and c.batid = d.batid
left join (select distinct h.compid,h.busno,h.cfno ,d.wareid,h.username
from t_remote_prescription_h h,t_remote_prescription_d d
where h.cfno=d.cfno) f on c.busno=f.busno and c.cfno=f.cfno and c.wareid=f.wareid
,t_sale_h b,t_ware q, t_factory y
WHERE c.saleno=b.saleno and c.busno=b.busno
and c.wareid= q.wareid and q.factoryid = y.factoryid
and b.compid = q.compid
and c.wareid in (select wareid from t_ware_class_base where compid = b.compid and classgroupno ='12' and classcode='1203')
and trunc(c.accdate) >= trunc(sysdate-120)
union all
--报损报溢
SELECT to_char(b.execdate,'yyyymmdd') AS 销售日期,
c.abnormityno as 销售单据号,
'零售单据' as 单据类型,
'' as 客户名称,
'' as 客户ID,
'' as 客户城市,
'' as 送货地址,
'' as 分公司名称,
'' as 分公司城市,
q.warecode AS 产品编码,
q.warename as 产品名称,
q.warespec AS 产品规格,
q.wareunit AS 单位,
y.factoryname as 生产厂家,
c.makeno AS 批号,
to_char(d.invalidate,'yyyymmdd') as 产品效期,
round(c.wareqtyb - c.wareqtya,4) as 销售数量,
c.purprice AS 含税单价,
q.fileno AS 批准文号
FROM t_abnormity_d c
left join t_store_i d on c.wareid=d.wareid and c.batid = d.batid
,t_abnormity_h b,t_ware q, t_factory y
WHERE c.abnormityno =b.abnormityno and b.status in (1,3)
and c.wareid= q.wareid and q.factoryid = y.factoryid
and b.compid = q.compid
and c.wareid in (select wareid from t_ware_class_base where compid = b.compid and classgroupno ='12' and classcode='1203')
and trunc(b.execdate) >= trunc(sysdate-120)
union all
--盘点单
SELECT to_char(b.execdate,'yyyymmdd') AS 销售日期,
c.checkno as 销售单据号,
'零售单据' as 单据类型,
'' as 客户名称,
'' as 客户ID,
'' as 客户城市,
'' as 送货地址,
'' as 分公司名称,
'' as 分公司城市,
q.warecode AS 产品编码,
q.warename as 产品名称,
q.warespec AS 产品规格,
q.wareunit AS 单位,
y.factoryname as 生产厂家,
c.makeno AS 批号,
to_char(d.invalidate,'yyyymmdd') as 产品效期,
round(c.accountqty - c.checkqty ,4) as 销售数量,
c.purprice AS 含税单价,
q.fileno AS 批准文号
FROM t_check_track_d c
left join t_store_i d on c.wareid=d.wareid and c.batid = d.batid
,t_check_track_h b,t_ware q, t_factory y
WHERE c.checkno =b.checkno and b.status in (1,3)
and c.wareid= q.wareid and q.factoryid = y.factoryid
and b.compid = q.compid
and c.wareid in (select wareid from t_ware_class_base where compid = b.compid and classgroupno ='12' and classcode='1203')
and trunc(b.execdate) >= trunc(sysdate-120)
;
create or replace view cv_辉瑞_总部采购 as
select
to_char(a.execdate,'yyyymmdd') as 入库日期,
a.acceptno as 入库单号,
s.vencuscode AS 供应商ID,
s.vencusname as 供应商名称,
'' as 供应商城市,
q.warecode as 产品编码,
q.warename as 产品名称,
q.warespec as 产品规格,
q.wareunit as 单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
b.wareqty AS 采购数量,
b.purprice as 采购单价,
b.makeno as 批号,
to_char(b.invalidate,'yyyymmdd') as 产品效期
from t_accept_h a,t_accept_d b,t_ware q,t_vencus s ,t_factory y
where a.acceptno=b.acceptno and a.vencusno = s.vencusno and a.compid = q.compid and a.compid = s.compid
and b.wareid=q.wareid and q.factoryid = y.factoryid
and a.status in (1,3)
and a.billcode = 'ACC'
and a.busno = 2000
and b.wareid in (select wareid from t_ware_class_base where compid = a.compid and classgroupno ='12' and classcode='1203')
and trunc(a.execdate) >= trunc(sysdate)-120
union all
select
to_char(a.execdate,'yyyymmdd') as 入库日期,
a.acceptno as 入库单号,
s.vencuscode AS 供应商ID,
s.vencusname as 供应商名称,
'' as 供应商城市,
q.warecode as 产品编码,
q.warename as 产品名称,
q.warespec as 产品规格,
q.wareunit as 单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
b.returnqty AS 采购数量,
b.purprice as 采购单价,
b.makeno as 批号,
to_char(b.invalidate,'yyyymmdd') as 产品效期
from t_reaccept_h a,t_reaccept_d b,t_ware q,t_vencus s ,t_factory y
where a.reacceptno=b.reacceptno and a.vencusno = s.vencusno and a.compid = q.compid and a.compid = s.compid
and b.wareid=q.wareid and q.factoryid = y.factoryid
and a.status in (1,3)
-- and a.billcode = 'ACC'
and a.busno = 2000
and b.wareid in (select wareid from t_ware_class_base where compid = a.compid and classgroupno ='12' and classcode='1203')
and trunc(a.execdate) >= trunc(sysdate)-120
;
CREATE OR REPLACE VIEW CV_辉瑞_总部库存 AS
SELECT to_char(sysdate,'yyyymmdd') AS 库存日期,
q.warecode AS 产品编码,
q.warename AS 产品名称,
q.warespec AS 产品规格,
q.wareunit AS 单位,
y.factoryname as 生产厂家,
c.wareqty AS 库存数量,
i.makeno as 批号,
to_char(i.invalidate,'yyyymmdd') as 产品效期,
q.fileno AS 批准文号,
'总仓库存' as 库存状态
FROM t_store_d c,t_store_i i,t_ware q ,t_factory y
WHERE c.wareid = i.wareid and c.compid = q.compid
and c.batid = i.batid and q.factoryid = y.factoryid
AND c.wareid = q.wareid
and c.busno = '2000'
and c.wareqty <> '0'
and c.wareid in (select wareid from t_ware_class_base where compid = c.compid and classgroupno ='12' and classcode='1203');
CREATE OR REPLACE VIEW CV_辉瑞_总部配送 AS
SELECT to_char(a.execdate,'yyyymmdd') AS 销售日期,
a.distno as 销售单据号,
case when a.billcode in ('DIS','ADD') then '销售单'
when a.billcode in ('DIR','ADR') then '销售退仓单' end as 单据类型,
case when a.billcode in ('DIS','ADD') then a.objbusno
when a.billcode in ('DIR','ADR') then a.srcbusno end as 客户ID,
case when a.billcode in ('DIS','ADD') then (select orgname from s_busi where busno = a.objbusno and compid = a.compid )
when a.billcode in ('DIR','ADR') then (select orgname from s_busi where busno = a.srcbusno and compid = a.compid ) end as 客户名称,
'' as 送货地址,
'' as 客户城市,
case when a.billcode in ('DIS','ADD') then (select orgname from s_busi where busno = a.objbusno and compid = a.compid )
when a.billcode in ('DIR','ADR') then (select orgname from s_busi where busno = a.srcbusno and compid = a.compid ) end as 分公司名称,
'' as 分公司城市,
q.warecode AS 产品编码,
q.warename AS 产品名称,
q.warespec AS 产品规格,
q.wareunit AS 单位,
y.factoryname as 生产厂家,
case when a.billcode in ('DIS','ADD') then 1 else -1 end * c.wareqty AS 销售数量,
c.purprice AS 含税单价,
c.makeno AS 批号,
to_char(i.invalidate,'yyyymmdd') as 产品效期,
q.fileno AS 批准文号
FROM t_dist_d c
left join t_store_i i on c.wareid=i.wareid and c.batid = i.batid
,t_dist_h a,t_ware q,t_factory y
WHERE c.wareid = q.wareid and a.compid = q.compid and q.factoryid = y.factoryid
and a.distno = c.distno
and a.billcode in ('DIS','ADD','DIR','ADR')
and a.status in (1,3)
and nvl(a.notes,'.') not like '期初库存导入%'
and nvl(c.wareqty,0)<>'0'
and c.wareid in (select wareid from t_ware_class_base where compid = a.compid and classgroupno ='12' and classcode='1203')
and trunc(a.execdate) >= trunc(sysdate-120);
CREATE OR REPLACE VIEW CV_扬子江_门店库存 AS
SELECT
c.compid as 企业编码,f_get_companyname(c.compid) as 企业名称,
to_char(sysdate,'yyyy-mm-dd') AS 库存日期,
s.vencuscode AS 经销商代码,
s.vencusname as 经销商名称,
'' as 客户代码,
f_get_companyname(c.compid) as 客户名称,
c.busno as 门店代码,
s_busi.orgname as 门店名称,
q.warecode AS 产品代码,
q.warename AS 产品名称,
q.warespec AS 产品规格,
'' as 产品简称,
'' as 包装规格,
'' as 件数,
q.wareunit AS 计量单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
c.wareqty AS 数量,
i.purprice as 单价,
i.makeno as 批号,
to_char(i.makedate,'yyyy-mm-dd') as 生产日期,
to_char(i.invalidate,'yyyy-mm-dd') as 有效期至,
'' as 库龄,
'正常库存' as 库存类型,
'' as 所属大区,
'' as 所属公司,
'' as 所属片区
FROM t_store_d c,t_store_i i,t_ware q,t_factory y,s_busi,t_vencus s
WHERE c.wareid=i.wareid
and c.batid=i.batid
and c.compid=i.compid
and q.compid=i.compid
AND c.wareid=q.wareid
and q.factoryid=y.factoryid
and c.busno=s_busi.busno
and c.compid=s_busi.compid
and i.compid=s.compid
and i.vencusno=s.vencusno
and s_busi.orgtype='30'
and c.wareqty<>0
and c.compid in (2)
and c.wareid in (select wareid from t_ware_class_base where compid=c.compid and classgroupno='12' and classcode='1201');
CREATE OR REPLACE VIEW CV_扬子江_门店零售 AS
SELECT
b.compid as 企业编码,f_get_companyname(b.compid) as 企业名称,
to_char(c.accdate,'yyyy-mm-dd') AS 销售日期,
c.saleno as 销售单据号,
'零售单据' as 类型,
'' as 客户代码,
f_get_companyname(b.compid) as 客户名称,
s.vencuscode AS 经销商代码,
s.vencusname as 经销商名称,
b.busno as 门店代码,
s_busi.orgname as 门店名称,
s_busi.address as 门店地址,
'' as 省份,
'' as 城市,
q.warecode AS 产品代码,
q.warename as 产品名称,
q.warespec AS 产品规格,
'' as 产品简称,
'' as 包装规格,
q.wareunit AS 计量单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
round((c.wareqty+c.minqty/c.stdtomin)*c.times,4) as 数量,
'' as 件数,
c.netprice AS 单价,
round((c.wareqty+c.minqty/c.stdtomin)*c.times,4) * c.netprice as 金额,
c.makeno AS 批号,
to_char(i.makedate,'yyyy-mm-dd') as 生产日期,
to_char(i.invalidate,'yyyy-mm-dd') as 有效期至,
'' as 所属大区,
'' as 所属公司,
'' as 所属片区
/*nvl(ltrim(rtrim(cc.notes)),cc.customername) AS 销售客户名称,*/
FROM t_sale_d c
left join (select distinct h.compid,h.busno,h.cfno ,d.wareid,h.username
from t_remote_prescription_h h,t_remote_prescription_d d
where h.cfno=d.cfno) f on c.busno=f.busno and c.cfno=f.cfno and c.wareid=f.wareid,t_sale_h b
left join t_sale_return_h e on b.compid=e.compid and b.busno=e.busno and b.saleno=e.saleno
left join t_sale_mes_customer cc on (b.busno=cc.busno and (b.saleno=cc.saleno or cc.saleno=e.retsaleno)),t_ware q,t_factory y,t_store_i i,t_vencus s,s_busi
WHERE c.saleno=b.saleno
and c.busno=b.busno
and c.wareid=q.wareid
and q.factoryid=y.factoryid
and b.compid=q.compid
and b.compid=i.compid
and c.wareid=i.wareid
and c.batid=i.batid
and i.compid=s.compid
and i.vencusno=s.vencusno
and b.busno=s_busi.busno
and b.compid=s_busi.compid
and b.compid in (2)
and c.wareid in (select wareid from t_ware_class_base where compid=b.compid and classgroupno='12' and classcode='1201')
and trunc(c.accdate)>=trunc(sysdate-90)
union all
--报损报溢
SELECT
b.compid as 企业编码,f_get_companyname(b.compid) as 企业名称,
to_char(b.execdate,'yyyy-mm-dd') AS 销售日期,
c.abnormityno as 销售单据号,
'损溢单据' as 类型,
'' as 客户代码,
f_get_companyname(b.compid) as 客户名称,
s.vencuscode AS 经销商代码,
s.vencusname as 经销商名称,
b.busno as 门店代码,
s_busi.orgname as 门店名称,
s_busi.address as 门店地址,
'' as 省份,
'' as 城市,
q.warecode AS 产品代码,
q.warename as 产品名称,
q.warespec AS 产品规格,
'' as 产品简称,
'' as 包装规格,
q.wareunit AS 计量单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
round(c.wareqtyb - c.wareqtya,4) as 数量,
'' as 件数,
c.purprice AS 单价,
round(c.wareqtyb - c.wareqtya,4) * c.purprice as 金额,
c.makeno AS 批号,
to_char(i.makedate,'yyyy-mm-dd') as 生产日期,
to_char(i.invalidate,'yyyy-mm-dd') as 有效期至,
'' as 所属大区,
'' as 所属公司,
'' as 所属片区
/*'' as 销售客户名称,*/
FROM t_abnormity_d c ,t_abnormity_h b ,t_ware q, t_factory y,t_store_i i,t_vencus s,s_busi
WHERE c.abnormityno =b.abnormityno and b.status in (1,3)
and c.wareid= q.wareid and q.factoryid = y.factoryid
and b.compid = q.compid
and b.compid=i.compid
and c.wareid=i.wareid
and c.batid=i.batid
and i.compid=s.compid
and i.vencusno=s.vencusno
and b.busno = s_busi.busno
and b.compid = s_busi.compid
and b.compid in (2)
and c.wareid in (select wareid from t_ware_class_base where compid=b.compid and classgroupno='12' and classcode='1201')
and trunc(b.execdate)>=trunc(sysdate-90)
union all
--盘点单
SELECT
b.compid as 企业编码,f_get_companyname(b.compid) as 企业名称,
to_char(b.execdate,'yyyy-mm-dd') AS 销售日期,
c.checkno as 销售单据号,
'盘点单据' as 类型,
'' as 客户代码,
f_get_companyname(b.compid) as 客户名称,
s.vencuscode AS 经销商代码,
s.vencusname as 经销商名称,
b.busno as 门店代码,
'' as 门店名称,
s_busi.address as 门店地址,
'' as 省份,
'' as 城市,
q.warecode AS 产品编码,
q.warename as 产品名称,
q.warespec AS 产品规格,
'' as 产品简称,
'' as 包装规格,
q.wareunit AS 计量单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
round(c.accountqty - c.checkqty ,4) as 数量,
'' as 件数,
c.purprice AS 单价,
round(c.accountqty - c.checkqty ,4) * c.purprice as 金额,
c.makeno AS 批号,
to_char(i.makedate,'yyyy-mm-dd') as 生产日期,
to_char(i.invalidate,'yyyy-mm-dd') as 有效期至,
'' as 所属大区,
'' as 所属公司,
'' as 所属片区
/*'' as 销售客户名称,*/
FROM t_check_track_d c ,t_check_track_h b,t_ware q, t_factory y,t_check_reg g,t_store_i i,t_vencus s,s_busi
WHERE c.checkno=b.checkno
and b.status in (1,3)
and g.checkno=b.checkno
and g.status = 3
and c.wareid= q.wareid
and q.factoryid = y.factoryid
and b.compid = q.compid
and b.compid=i.compid
and c.wareid=i.wareid
and c.batid=i.batid
and i.compid=s.compid
and i.vencusno=s.vencusno
and b.busno = s_busi.busno
and b.compid = s_busi.compid
and b.compid in (2)
and c.wareid in (select wareid from t_ware_class_base where compid = b.compid and classgroupno='12' and classcode='1201')
and trunc(b.execdate)>=trunc(sysdate-90)
;
create or replace view cv_扬子江_总部采购 as
select
a.compid as 企业编码,f_get_companyname(a.compid) as 企业名称,
to_char(a.execdate,'YYYY-MM-DD') as 入库日期,
s.vencuscode AS 供应商代码,
s.vencusname as 供应商名称,
'' as 供应商地址,
a.busno as 客户代码,
s_busi.orgname as 客户名称,
q.warecode as 产品代码,
q.warename as 产品名称,
q.warespec as 产品规格,
'' as 产品简称,
'' as 包装规格,
q.wareunit as 计量单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
b.wareqty AS 数量,
'' as 件数,
b.purprice as 单价,
b.wareqty * b.purprice as 金额,
b.makeno as 批号,
to_char(b.makedate,'YYYY-MM-DD') as 生产日期,
to_char(b.invalidate,'YYYY-MM-DD') as 有效期至,
a.acceptno as 入库单号,
'采购' as 类型
from t_accept_h a,t_accept_d b,t_ware q,t_vencus s ,t_factory y ,s_busi
where a.acceptno=b.acceptno
and a.vencusno = s.vencusno
and a.compid = q.compid
and a.compid = s.compid
and a.busno = s_busi.busno
and a.compid = s_busi.compid
and b.wareid=q.wareid and q.factoryid = y.factoryid
and a.billcode = 'ACC'
and a.status in (1,3)
and a.compid in (2)
and b.wareid in (select wareid from t_ware_class_base where compid = a.compid and classgroupno='12' and classcode='1201')
and trunc(a.execdate) >= trunc(sysdate)-90
union all
select
a.compid as 企业编码,f_get_companyname(a.compid) as 企业名称,
to_char(a.execdate,'YYYY-MM-DD') as 入库日期,
s.vencuscode AS 供应商代码,
s.vencusname as 供应商名称,
'' as 供应商地址,
a.busno as 客户代码,
s_busi.orgname as 客户名称,
q.warecode as 产品代码,
q.warename as 产品名称,
q.warespec as 产品规格,
'' as 产品简称,
'' as 包装规格,
q.wareunit as 计量单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
-1 * b.returnqty AS 数量,
'' as 件数,
b.purprice as 单价,
(-1 * b.returnqty * b.purprice) as 金额,
b.makeno as 批号,
to_char(b.makedate,'YYYY-MM-DD') as 生产日期,
to_char(b.invalidate,'YYYY-MM-DD') as 有效期至,
a.acceptno as 入库单号,
'采退' as 类型
from t_reaccept_h a,t_reaccept_d b,t_ware q,t_vencus s ,t_factory y,s_busi
where a.reacceptno=b.reacceptno
and a.vencusno = s.vencusno
and a.compid = q.compid
and a.compid = s.compid
and a.busno = s_busi.busno
and a.compid = s_busi.compid
and b.wareid=q.wareid and q.factoryid = y.factoryid
and a.status in (1,3)
and a.compid in (2)
and b.wareid in (select wareid from t_ware_class_base where compid = a.compid and classgroupno='12' and classcode='1201')
and trunc(a.execdate) >= trunc(sysdate)-90;
CREATE OR REPLACE VIEW CV_扬子江_总部库存 AS
SELECT
c.compid as 企业编码,f_get_companyname(c.compid) as 企业名称,
to_char(sysdate,'yyyy-mm-dd') AS 库存日期,
s.vencuscode AS 经销商代码,
s.vencusname as 经销商名称,
c.busno as 客户代码,
s_busi.orgname as 客户名称,
q.warecode AS 产品代码,
q.warename AS 产品名称,
q.warespec AS 产品规格,
'' as 产品简称,
'' as 包装规格,
'' as 件数,
q.wareunit AS 计量单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
c.wareqty AS 数量,
i.purprice as 单价,
i.makeno as 批号,
to_char(i.makedate,'yyyy-mm-dd') as 生产日期,
to_char(i.invalidate,'yyyy-mm-dd') as 有效期至,
'' as 仓库名称,
'' as 库龄,
'正常库存' as 库存类型
FROM t_store_d c,t_store_i i,t_ware q,t_factory y,s_busi,t_vencus s
WHERE c.wareid=i.wareid
and c.batid=i.batid
and c.compid=i.compid
and q.compid=i.compid
AND c.wareid=q.wareid
and q.factoryid=y.factoryid
and c.busno=s_busi.busno
and c.compid=s_busi.compid
and i.compid=s.compid
and i.vencusno=s.vencusno
and s_busi.orgtype='10'
and c.wareqty <> 0
and c.compid in (2)
and c.wareid in (select wareid from t_ware_class_base where compid=c.compid and classgroupno='12' and classcode='1201');
CREATE OR REPLACE VIEW CV_扬子江_总部配送 AS
SELECT
a.compid as 企业编码,f_get_companyname(a.compid) as 企业名称,
to_char(a.execdate,'yyyy-mm-dd') AS 配送日期,
a.distno as 配送单据号,
case when a.billcode in ('DIS','ADD') then '配送'
when a.billcode in ('DIR','ADR') then '配送退' end as 类型,
s.vencuscode AS 经销商代码,
s.vencusname as 经销商名称,
case when a.billcode in ('DIS','ADD') then a.srcbusno
when a.billcode in ('DIR','ADR') then a.objbusno end as 客户代码,
case when a.billcode in ('DIS','ADD') then (select orgname from s_busi where busno = a.srcbusno and compid = a.compid )
when a.billcode in ('DIR','ADR') then (select orgname from s_busi where busno = a.objbusno and compid = a.compid ) end as 客户名称,
case when a.billcode in ('DIS','ADD') then a.objbusno
when a.billcode in ('DIR','ADR') then a.srcbusno end as 销往门店代码,
case when a.billcode in ('DIS','ADD') then (select orgname from s_busi where busno = a.objbusno and compid = a.compid )
when a.billcode in ('DIR','ADR') then (select orgname from s_busi where busno = a.srcbusno and compid = a.compid ) end as 销往门店名称,
case when a.billcode in ('DIS','ADD') then (select address from s_busi where busno = a.objbusno and compid = a.compid )
when a.billcode in ('DIR','ADR') then (select address from s_busi where busno = a.srcbusno and compid = a.compid ) end as 配送地址,
'' as 省份,
'' as 城市,
q.warecode AS 产品代码,
q.warename AS 产品名称,
q.warespec AS 产品规格,
'' as 产品简称,
'' as 包装规格,
q.wareunit AS 计量单位,
y.factoryname as 生产厂家,
q.fileno AS 批准文号,
case when a.billcode in ('DIS','ADD') then 1 else -1 end * c.wareqty AS 数量,
'' as 件数,
c.purprice AS 单价,
(case when a.billcode in ('DIS','ADD') then 1 else -1 end * c.wareqty * c.purprice) as 金额,
c.makeno AS 批号,
to_char(i.makedate,'yyyy-mm-dd') as 生产日期,
to_char(i.invalidate,'yyyy-mm-dd') as 有效期至,
'' as 所属大区,
'' as 所属公司,
'' as 所属片区
FROM t_dist_d c,t_dist_h a,t_ware q,t_factory y,t_store_i i,t_vencus s
WHERE c.wareid=q.wareid
and a.compid=q.compid
and q.factoryid=y.factoryid
and a.distno=c.distno
and a.compid=i.compid
and c.wareid=i.wareid
and c.batid=i.batid
and i.compid=s.compid
and i.vencusno=s.vencusno
and a.billcode in ('DIS','ADD','DIR','ADR')
and a.status in (1,3)
and nvl(c.wareqty,0)<>'0'
and a.compid in (2)
and nvl(a.notes,'.') not like '期初库存导入%'
and c.wareid in (select wareid from t_ware_class_base where compid=a.compid and classgroupno='12' and classcode='1201')
and trunc(a.execdate)>=trunc(sysdate-90);
-- 创建用户
CREATE USER yangzijiang IDENTIFIED BY yangzijiang;
-- 授予连接数据库的权限
GRANT CREATE SESSION TO yangzijiang;
-- 撤销不必要的角色（如果有）
-- 例如：
-- REVOKE DATAPUMP_EXP_FULL_DATABASE FROM yangzijiang;
-- 撤销不必要的系统权限（如果有）
-- 例如：
REVOKE SELECT ANY TABLE FROM yangzijiang;
REVOKE SELECT ANY DICTIONARY FROM yangzijiang;
-- 撤销对其他视图的权限（如果有）
-- 例如：
-- REVOKE SELECT ON H2.cv_阿斯利康_库存 FROM yangzijiang;
-- 授予对特定视图的查询权限
GRANT SELECT ON H2.cv_扬子江_门店库存 TO yangzijiang;
GRANT SELECT ON H2.cv_扬子江_门店零售 TO yangzijiang;
GRANT SELECT ON H2.cv_扬子江_总部采购 TO yangzijiang;
GRANT SELECT ON H2.cv_扬子江_总部库存 TO yangzijiang;
GRANT SELECT ON H2.cv_扬子江_总部配送 TO yangzijiang;
-- 创建用户
CREATE USER astrazeneca IDENTIFIED BY astrazeneca;
-- 授予连接数据库的权限
GRANT CREATE SESSION TO astrazeneca;
-- 授予对特定视图的查询权限
GRANT SELECT ON H2.cv_阿斯利康_库存 TO astrazeneca;
GRANT SELECT ON H2.cv_阿斯利康_采购 TO astrazeneca;
GRANT SELECT ON H2.cv_阿斯利康_零售 TO astrazeneca;
GRANT SELECT ON H2.cv_阿斯利康_销售 TO astrazeneca;
-- 创建用户
CREATE USER bayer IDENTIFIED BY bayer;
-- 授予连接数据库的权限
GRANT CREATE SESSION TO bayer;
-- 授予对特定视图的查询权限
GRANT SELECT ON H2.cv_拜耳_采购数据 TO bayer;
GRANT SELECT ON H2.cv_拜耳_库存数据 TO bayer;
GRANT SELECT ON H2.cv_拜耳_配送数据 TO bayer;
GRANT SELECT ON H2.cv_拜耳_销售数据 TO bayer;
-- 创建用户
CREATE USER pfizer IDENTIFIED BY pfizer;
-- 授予连接数据库的权限
GRANT CREATE SESSION TO pfizer;
-- 授予对特定视图的查询权限
GRANT SELECT ON H2.cv_辉瑞_门店进货 TO pfizer;
GRANT SELECT ON H2.cv_辉瑞_门店库存 TO pfizer;
GRANT SELECT ON H2.cv_辉瑞_门店零售 TO pfizer;
GRANT SELECT ON H2.cv_辉瑞_总部采购 TO pfizer;
GRANT SELECT ON H2.cv_辉瑞_总部库存 TO pfizer;
GRANT SELECT ON H2.cv_辉瑞_总部配送 TO pfizer;
辉瑞流向
数据库类型:Oracle
数据库连接:XXX.XXX.XXX.XXX
数据库端口:1521
数据库名:hydee
数据库账号:pfizer
数据库密码:pfizer
数据源:
H2.cv_辉瑞_门店进货
H2.cv_辉瑞_门店库存
H2.cv_辉瑞_门店零售
H2.cv_辉瑞_总部采购
H2.cv_辉瑞_总部库存
H2.cv_辉瑞_总部配送
------------------------------------
扬子江流向
数据库类型:Oracle
数据库连接:XXX.XXX.XXX.XXX
数据库端口:1521
数据库名:hydee
数据库账号:yangzijiang
数据库密码:yangzijiang
数据源:
H2.cv_扬子江_门店库存
H2.cv_扬子江_门店零售
H2.cv_扬子江_总部采购
H2.cv_扬子江_总部库存
H2.cv_扬子江_总部配送
------------------------------------
扬子江流向
数据库类型:Oracle
数据库连接:XXX.XXX.XXX.XXX
数据库端口:1521
数据库名:hydee
数据库账号:astrazeneca
数据库密码:astrazeneca
数据源:
H2.cv_阿斯利康_库存
H2.cv_阿斯利康_采购
H2.cv_阿斯利康_零售
H2.cv_阿斯利康_销售
------------------------------------
拜耳流向
数据库类型:Oracle
数据库连接:XXX.XXX.XXX.XXX
数据库端口:1521
数据库名:hydee
数据库账号:bayer
数据库密码:bayer
数据源:
H2.cv_拜耳_采购数据
H2.cv_拜耳_库存数据
H2.cv_拜耳_配送数据
H2.cv_拜耳_销售数据