CREATE VIEW SD_HOSPITAL.VW_YK_YPTJDZDYDY
 (
 YPTJPC, YXBMBH, YPMC00, YPGG00, TJDW00, ZMKC00, SJKC00, KL0000, YPFDJ0, 
XPFDJ0, GJPJDJ, YLSDJ0, XLSDJ0, QRRQ00, QRSJ00, MXBZ00, QRRQ01, QRSJ01, 
BZ0000, YPJCL0, C_LSCJ, C_GJCJ, C_GJSY, C_TJSY

 )
 AS 
SELECT 
T.YPTJPC,--药品调价批次 
X.YXBMBH,--部门编号 
X.YPMC00,--药品名称 
X.YPGG00,--药品规格 
X.TJDW00,--调价单位 
X.ZMKC00,--库存 
X.SJKC00,--实际库存 
X.KL0000,--扣率 
X.YPFDJ0,--原进价 
X.XPFDJ0,--现进价 
X.GJPJDJ,--购进平均单价 
X.YLSDJ0,--原零售单价 
X.XLSDJ0,--现零售单价 
X.QRRQ00,--确认日期 
X.QRSJ00,--确认时间 
X.BZ0000 MXBZ00,--备注 
T.QRRQ00 QRRQ01,--确认日期 
T.QRSJ00 QRSJ01,--确认时间 
T.BZ0000,--备注 
D.YPJCL0+1 YPJCL0,--药品加成率 
X.XLSDJ0-X.YLSDJ0  C_LSCJ,--零售差价 
X.XPFDJ0-X.YPFDJ0  C_GJCJ,--购进差价 
--ROUND(X.SJKC00*(X.XPFDJ0-X.YPFDJ0),2)  C_GJSY,--购进损益 
--(select GJSLJE+GJFCJE from YK_YPMXZ0 where djh000=x.YPTJPC and ypnm00=d.ypnm00 ) C_GJSY,--购进损益
(X.XPFDJ0-X.YPFDJ0)*X.SJKC00 C_GJSY,  --医大附二修改为此句
ROUND(X.SJKC00*(X.XLSDJ0-X.YLSDJ0),2)  C_TJSY--调价损益 
FROM YK_YPTJ00 T,YK_YPTJMX X,BM_YD0000 D
WHERE T.YPTJPC=X.YPTJPC AND X.YPNM00=D.YPNM00
 --AND T.YPTJPC='000901'
 --AND X.YXBMBH='72'
 ORDER BY TJMXPC
/