/****** Object: View [V_DPL1] Script Date: 2021-02-03 17:50:18 ******/ DROP VIEW [V_DPL1] GO /****** Object: View [V_DSL1] Script Date: 2021-02-03 17:50:18 ******/ DROP VIEW [V_DSL1] GO /****** Object: View [V_MDL1] Script Date: 2021-02-03 17:50:18 ******/ DROP VIEW [V_MDL1] GO /****** Object: View [V_MSL1] Script Date: 2021-02-03 17:50:18 ******/ DROP VIEW [V_MSL1] GO /****** Object: View [V_MSL1] Script Date: 2021-02-03 17:50:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [V_MSL1] AS --2.物资进货单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice, B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 2 AS xTypeID , B.ID AS xTTradeID , K.ID AS MSK_ID , A.TerminalNO , B.Remark , A.BBH01 AS Join_ID , (SELECT TOP 1 BBH04 FROM BBH1 M WHERE M.BBH01=A.BBH01) AS Join_Name , B.PresellPrice , B.PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM MSB1 A join MSB2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join MSK1 K ON K.xTypeID = 2 and K.xTTradeID = B.ID WHERE A.AuditingDate IS NOT NULL --3.物资退货单 UNION ALL SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 3 AS xTypeID , B.ID AS xTTradeID , B.MSK_ID , A.TerminalNO , B.Remark , A.BBH01 AS Join_ID , (SELECT TOP 1 BBH04 FROM BBH1 M WHERE M.BBH01=A.BBH01) AS Join_Name , B.PresellPrice , B.PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM MSC1 A join MSC2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MSK1 K ON K.ID = B.MSK_ID WHERE A.AuditingDate IS NOT NULL --4.物资出库单 UNION ALL SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 4 AS xTypeID , B.ID AS xTTradeID , B.MSK_ID , A.TerminalNO , B.Remark , A.DeptID AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = A.DeptID) AS Join_Name , B.PresellPrice , B.PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM MSD1 A join MSD2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MSK1 K ON K.ID = B.MSK_ID WHERE A.AuditingDate IS NOT NULL --5.物资退库单 UNION ALL SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 5 AS xTypeID , B.ID AS xTTradeID , B.MSK_ID , A.TerminalNO , B.Remark , A.DeptID AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = A.DeptID) AS Join_Name , B.PresellPrice , B.PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM MSE1 A join MSE2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MSK1 K ON K.ID = B.MSK_ID WHERE A.AuditingDate IS NOT NULL UNION ALL --6.物资损益单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 6 AS xTypeID , B.ID AS xTTradeID , B.MSK_ID , A.TerminalNO , B.Remark , B.BCK01 AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = B.BCK01) AS Join_Name , B.PresellPrice , B.PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM MSF1 A join MSF2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MSK1 K ON K.ID = B.MSK_ID WHERE A.BillType <> 1 --普通损益 and A.AuditingDate IS NOT NULL UNION ALL --7.物资盘点单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 7 AS xTypeID , B.ID AS xTTradeID , B.MSK_ID , A.TerminalNO , B.Remark , B.BCK01 AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = B.BCK01) AS Join_Name , B.PresellPrice , B.PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM MSF1 A join MSF2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MSK1 K ON K.ID = B.MSK_ID WHERE A.BillType = 1 --盘点损益 and A.AuditingDate IS NOT NULL UNION ALL --8.物资调入单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 8 AS xTypeID , B.ID AS xTTradeID , K.ID AS MSK_ID , A.TerminalNO , B.Remark , A.FStoreID AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = A.FStoreID) AS Join_Name , B.PresellPrice , B.PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM MSH1 A join MSH2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join MSK1 K ON K.xTypeID = 8 and K.xTTradeID = B.ID WHERE A.AuditingDate IS NOT NULL UNION ALL --9.物资调出单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 9 AS xTypeID , B.ID AS xTTradeID , B.MSK_ID , A.TerminalNO , B.Remark , A.TStoreID AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = A.TStoreID) AS Join_Name , B.PresellPrice , B.PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM MSI1 A join MSI2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MSK1 K ON K.ID = B.MSK_ID WHERE A.AuditingDate IS NOT NULL UNION ALL --进货价、预售价差额 SELECT B.BCK01, A.BBY01, B.Unit, B.PackSize, B.BatchNo , 0, A.DiffPurchasePrice, A.DiffPurchaseAmount, 0, 0, 0, 0 , A.sysdate, '', '', '', 10 AS xTypeID, 0, MSK1_ID as MSK_ID , '', '', 0, '', A.DiffPresellPrice, A.DiffPresellAmount, B.BBH01, B.ProduceDate, B.ExpiryDate FROM MSK2 A WITH(NOLOCK) JOIN MSK1 B WITH(NOLOCK) ON B.ID = A.MSK1_ID union all --销售价差额 SELECT B.DeptID, B.BBY01, B.Unit, B.PackSize, '' , 0, 0, 0, B.DiffPrice, B.DiffAmount, 0, 0 , A.AuditingDate, A.Assessor, A.BillNO, A.Abstract, 11 AS xTypeID, 0 as xTtradeID, B.MSK_ID , A.TerminalNO, '' AS Remark, 0, '', 0 AS PresellPrice, 0 AS PresellAmount, 0, null, null FROM MSJ1 A JOIN MSJ3 B ON B.BCK01 = A.BCK01 AND B.BillID = A.BillID WHERE A.AuditingDate IS NOT NULL GO /****** Object: View [V_MDL1] Script Date: 2021-02-03 17:50:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [V_MDL1] AS --2.科室材料入库单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 2 AS xTypeID , B.ID AS xTTradeID , K.ID AS MDK_ID , A.TerminalNO , B.Remark , A.StoreID AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.StoreID) AS Join_Name , B.PresellPrice , B.PresellAmount FROM MDB1 A join MDB2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join MDK1 K ON K.xTypeID = 2 and K.xTTradeID = B.ID WHERE A.AuditingDate IS NOT NULL UNION ALL --3.科室材料退库单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 3 AS xTypeID , B.ID AS xTTradeID , B.MDK_ID , A.TerminalNO , B.Remark , A.StoreID AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.StoreID) AS Join_Name , B.PresellPrice , B.PresellAmount FROM MDC1 A join MDC2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MDK1 K ON K.ID = B.MDK_ID WHERE A.AuditingDate IS NOT NULL UNION ALL --4.科室材料发出单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 4 AS xTypeID , B.ID AS xTTradeID , B.MDK_ID , A.TerminalNO , B.Remark , 0 AS Join_ID , '零售' AS Join_Name , B.PresellPrice , B.PresellAmount FROM MDD1 A join MDD2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MDK1 K ON K.ID = B.MDK_ID WHERE B.Quantity >= 0 --A.BillType in (0, 1, 2) --0.门诊病人发 1.住院病人发 2.自用 and A.AuditingDate IS NOT NULL UNION ALL --5.科室材料退回单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , -B.Quantity , B.PurchasePrice , -B.PurchaseAmount , B.RetailPrice , -B.RetailAmount , B.CostPrice , -B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 5 AS xTypeID , B.ID AS xTTradeID , B.MDK_ID , A.TerminalNO , B.Remark , 0 AS Join_ID , '零售' AS Join_Name , B.PresellPrice , -1*B.PresellAmount FROM MDD1 A join MDD2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MDK1 K ON K.ID = B.MDK_ID WHERE B.Quantity < 0--A.BillType in (3, 4, 5) --3.门诊病人退 4.住院病人退 5.自退 and A.AuditingDate IS NOT NULL UNION ALL --6.科室材料损益单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 6 AS xTypeID , B.ID AS xTTradeID , B.MDK_ID , A.TerminalNO , B.Remark , A.BCK01 AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.BCK01) AS Join_Name , B.PresellPrice , B.PresellAmount FROM MDF1 A join MDF2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MDK1 K ON K.ID = B.MDK_ID WHERE A.BillType <> 1 --普通损益 and A.AuditingDate IS NOT NULL UNION ALL --7.科室材料盘点单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 7 AS xTypeID , B.ID AS xTTradeID , B.MDK_ID , A.TerminalNO , B.Remark , A.BCK01 AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.BCK01) AS Join_Name , B.PresellPrice , B.PresellAmount FROM MDF1 A join MDF2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MDK1 K ON K.ID = B.MDK_ID WHERE A.BillType = 1 --盘点损益 and A.AuditingDate IS NOT NULL UNION ALL --8.科室材料调入单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 8 AS xTypeID , B.ID AS xTTradeID , K.ID AS MDK_ID , A.TerminalNO , B.Remark , A.FDeptID AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.FDeptID) AS Join_Name , B.PresellPrice , B.PresellAmount FROM MDH1 A join MDH2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join MDK1 K ON K.xTypeID = 8 and K.xTTradeID = B.ID WHERE A.AuditingDate IS NOT NULL UNION ALL --9.科室材料调出单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 9 AS xTypeID , B.ID AS xTTradeID , B.MDK_ID , A.TerminalNO , B.Remark , A.TDeptID AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.TDeptID) AS Join_Name , B.PresellPrice , B.PresellAmount FROM MDI1 A join MDI2 B ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN MDK1 K ON K.ID = B.MDK_ID WHERE A.AuditingDate IS NOT NULL UNION ALL --进货价、预售价差额 SELECT B.BCK01, A.BBY01, B.Unit, B.PackSize, B.BatchNo , 0, A.DiffPurchasePrice, A.DiffPurchaseAmount, 0, 0, 0, 0 , A.sysdate, '', '', '', 10 AS xTypeID, 0, MDK1_ID as MDK_ID , '', '', 0, '', A.DiffPresellPrice, A.DiffPresellAmount FROM MDK2 A WITH(NOLOCK) JOIN MDK1 B WITH(NOLOCK) ON B.ID = A.MDK1_ID union all SELECT B.DeptID, B.BBY01, B.Unit, B.PackSize, '' , 0, 0, 0, B.DiffPrice, B.DiffAmount, 0, 0 , A.AuditingDate, A.Assessor, A.BillNO, A.Abstract, 11 AS xTypeID, 0 as xTtradeID, B.MSK_ID as MDK_ID , A.TerminalNO, '', 0, '', 0, 0 FROM MSJ1 A JOIN MSJ3 B ON B.BCK01 = A.BCK01 AND B.BillID = A.BillID WHERE A.AuditingDate IS NOT NULL GO /****** Object: View [V_DSL1] Script Date: 2021-02-03 17:50:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [V_DSL1] AS --2.药库进货单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 2 AS xTypeID , B.ID AS xTTradeID , K.ID AS DSK_ID , A.TerminalNO , B.Remark , A.BBH01 AS Join_ID , (SELECT TOP 1 BBH04 FROM BBH1 M WHERE M.BBH01=A.BBH01) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate , A.BillType FROM DSB1 A WITH(NOLOCK) join DSB2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join DSK1 K WITH(NOLOCK) ON K.ID = B.DSK_ID--K.xTypeID = 2 and K.xTTradeID = B.ID WHERE A.AuditingDate IS NOT NULL --3.药库退货单 UNION ALL SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 3 AS xTypeID , B.ID AS xTTradeID , B.DSK_ID , A.TerminalNO , B.Remark , A.BBH01 AS Join_ID , (SELECT TOP 1 BBH04 FROM BBH1 M WHERE M.BBH01=A.BBH01) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate , A.BillType FROM DSC1 A WITH(NOLOCK) join DSC2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DSK1 K WITH(NOLOCK) ON K.ID = B.DSK_ID WHERE A.AuditingDate IS NOT NULL --4.药库出库单 UNION ALL SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 4 AS xTypeID , B.ID AS xTTradeID , B.DSK_ID , A.TerminalNO , B.Remark , A.DeptID AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = A.DeptID) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate , A.BillType FROM DSD1 A WITH(NOLOCK) join DSD2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DSK1 K WITH(NOLOCK) ON K.ID = B.DSK_ID WHERE A.AuditingDate IS NOT NULL --5.药库退库单 UNION ALL SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 5 AS xTypeID , B.ID AS xTTradeID , B.DSK_ID , A.TerminalNO , B.Remark , A.DeptID AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = A.DeptID) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate , A.BillType FROM DSE1 A WITH(NOLOCK) join DSE2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DSK1 K WITH(NOLOCK) ON K.ID = B.DSK_ID WHERE A.AuditingDate IS NOT NULL UNION ALL --6.药库损益单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 6 AS xTypeID , B.ID AS xTTradeID , B.DSK_ID , A.TerminalNO , B.Remark , B.BCK01 AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = B.BCK01) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate , A.BillType FROM DSF1 A WITH(NOLOCK) join DSF2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DSK1 K WITH(NOLOCK) ON K.ID = B.DSK_ID WHERE (A.BillType <> 1) --普通损益 and A.AuditingDate IS NOT NULL UNION ALL --7.药库盘点单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 7 AS xTypeID , B.ID AS xTTradeID , B.DSK_ID , A.TerminalNO , B.Remark , B.BCK01 AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = B.BCK01) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate , A.BillType FROM DSF1 A WITH(NOLOCK) join DSF2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DSK1 K WITH(NOLOCK) ON K.ID = B.DSK_ID WHERE A.BillType = 1 --盘点损益 and A.AuditingDate IS NOT NULL UNION ALL --8.药库调入单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 8 AS xTypeID , B.ID AS xTTradeID , K.ID DSK_ID , A.TerminalNO , B.Remark , A.FStoreID AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = A.FStoreID) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate , A.BillType FROM DSH1 A WITH(NOLOCK) join DSH2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join DSK1 K WITH(NOLOCK) ON K.xTypeID = 8 and K.xTTradeID = B.ID WHERE A.AuditingDate IS NOT NULL UNION ALL --9.药库调出单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 9 AS xTypeID , B.ID AS xTTradeID , B.DSK_ID , A.TerminalNO , B.Remark , A.TStoreID AS Join_ID , (SELECT TOP 1 M.BCK03 FROM BCK1 M WHERE M.BCK01 = A.TStoreID) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate , A.BillType FROM DSI1 A WITH(NOLOCK) join DSI2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DSK1 K WITH(NOLOCK) ON K.ID = B.DSK_ID WHERE A.AuditingDate IS NOT NULL UNION ALL SELECT B.BCK01, A.BBY01, B.Unit, B.PackSize, B.BatchNo , 0, A.DiffPrice, A.DiffAmount, 0, 0, 0, 0 , A.sysdate, '', '', '', 10 AS xTypeID, 0, A.DSK1_ID as DSK_ID, '', '', 0, '', A.DiffPresellPrice, A.DiffPresellAmount, 0, NULL, NULL, 0 FROM DSK2 A WITH(NOLOCK) JOIN DSK1 B WITH(NOLOCK) ON B.ID = A.DSK1_ID union all SELECT B.DeptID, B.BBY01, B.Unit, B.PackSize, '' , 0, 0, 0, B.DiffPrice, B.DiffAmount, 0, 0 , A.AuditingDate, A.Assessor, A.BillNO, A.Abstract, 11 AS xTypeID, 0 as xTtradeID, B.DSK_ID , A.TerminalNO, '', 0, '', 0, 0, 0, null, null, 0 FROM DSJ1 A WITH(NOLOCK) JOIN DSJ3 B WITH(NOLOCK) ON B.BCK01 = A.BCK01 AND B.BillID = A.BillID WHERE B.DeptProPerty = 1 and A.AuditingDate IS NOT NULL GO /****** Object: View [V_DPL1] Script Date: 2021-02-03 17:50:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --药房单据联合记录 CREATE VIEW [V_DPL1] AS --2.药房入库单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 2 AS xTypeID , B.ID AS xTTradeID , K.ID AS DPK_ID , A.TerminalNO , B.Remark , A.StoreID AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.StoreID) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM DPB1 A WITH(NOLOCK) join DPB2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID --K.xTypeID = 2 and K.xTTradeID = B.ID WHERE A.AuditingDate IS NOT NULL UNION ALL --3.药房退库单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 3 AS xTypeID , B.ID AS xTTradeID , B.DPK_ID , A.TerminalNO , B.Remark , A.StoreID AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.StoreID) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM DPC1 A WITH(NOLOCK) join DPC2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID WHERE A.AuditingDate IS NOT NULL UNION ALL --4.药房发药单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 4 AS xTypeID , B.ID AS xTTradeID , B.DPK_ID , A.TerminalNO , B.Remark , 0 AS Join_ID , case when a.BillType=0 then '门诊发药' when a.BillType=1 then '住院发药' else '零售发药' end AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM V_DPD1_FULL A WITH(NOLOCK) join DPD2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID WHERE B.Quantity >= 0 --A.BillType in (0, 1) --0.门诊病人发药 1.住院病人发药 and A.AuditingDate IS NOT NULL UNION ALL SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 4 AS xTypeID , B.ID AS xTTradeID , B.DPK_ID , A.TerminalNO , B.Remark , 0 AS Join_ID , case when a.BillType=0 then '门诊发药' when a.BillType=1 then '住院发药' else '零售发药' end AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM V_DPD1_FULL A WITH(NOLOCK) join DPD2_2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID WHERE B.Quantity >= 0 --A.BillType in (0, 1) --0.门诊病人发药 1.住院病人发药 and A.AuditingDate IS NOT NULL UNION ALL --5.药房退药单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , -1*B.Quantity , B.PurchasePrice , -1*B.PurchaseAmount , B.RetailPrice , -1*B.RetailAmount , B.CostPrice , -1*B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 5 AS xTypeID , B.ID AS xTTradeID , B.DPK_ID , A.TerminalNO , B.Remark , 0 AS Join_ID --, (CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.DeptID) END) AS Join_Name , case when a.BillType=2 then '门诊退药' when a.BillType in (1,3) then '住院退药' else '零售退药' end AS Join_Name , K.PresellPrice , -1*K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM V_DPD1_FULL A WITH(NOLOCK) join DPD2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID WHERE B.Quantity < 0 --A.BillType in (2, 3) --2.门诊病人退药 3.住院病人退药 and A.AuditingDate IS NOT NULL UNION ALL SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , -1*B.Quantity , B.PurchasePrice , -1*B.PurchaseAmount , B.RetailPrice , -1*B.RetailAmount , B.CostPrice , -1*B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 5 AS xTypeID , B.ID AS xTTradeID , B.DPK_ID , A.TerminalNO , B.Remark , 0 AS Join_ID --, (CASE WHEN A.DeptID=0 THEN '零售' ELSE (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.DeptID) END) AS Join_Name , case when a.BillType=2 then '门诊退药' when a.BillType in (1,3) then '住院退药' else '零售退药' end AS Join_Name , K.PresellPrice , -1*K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM V_DPD1_FULL A WITH(NOLOCK) join DPD2_2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID WHERE B.Quantity < 0 --A.BillType in (2, 3) --2.门诊病人退药 3.住院病人退药 and A.AuditingDate IS NOT NULL UNION ALL --6.药房损益单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 6 AS xTypeID , B.ID AS xTTradeID , B.DPK_ID , A.TerminalNO , B.Remark , A.BCK01 AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.BCK01) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM DPF1 A WITH(NOLOCK) join DPF2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID WHERE A.BillType <> 1 --普通损益 and A.AuditingDate IS NOT NULL UNION ALL --7.药房盘点单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 7 AS xTypeID , B.ID AS xTTradeID , B.DPK_ID , A.TerminalNO , B.Remark , A.BCK01 AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.BCK01) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM DPF1 A WITH(NOLOCK) join DPF2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID WHERE A.BillType = 1 --盘点损益 and A.AuditingDate IS NOT NULL UNION ALL --8.药房调入单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 8 AS xTypeID , B.ID AS xTTradeID , K.ID AS DPK_ID , A.TerminalNO , B.Remark , A.FDeptID AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.FDeptID) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM DPH1 A WITH(NOLOCK) join DPH2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID join DPK1 K WITH(NOLOCK) ON K.xTypeID = 8 and K.xTTradeID = B.ID WHERE A.AuditingDate IS NOT NULL UNION ALL --9.药房调出单 SELECT B.BCK01 , B.BBY01 , B.Unit , B.PackSize , B.BatchNo , B.Quantity , B.PurchasePrice , B.PurchaseAmount , B.RetailPrice , B.RetailAmount , B.CostPrice , B.CostAmount , A.AuditingDate , A.Assessor , A.BillNO , A.Abstract , 9 AS xTypeID , B.ID AS xTTradeID , B.DPK_ID , A.TerminalNO , B.Remark , A.TDeptID AS Join_ID , (SELECT BCK03 FROM BCK1 M WHERE M.BCK01 = A.TDeptID) AS Join_Name , K.PresellPrice , K.PresellPrice * B.Quantity as PresellAmount , K.BBH01 , K.ProduceDate , K.ExpiryDate FROM DPI1 A WITH(NOLOCK) join DPI2 B WITH(NOLOCK) ON A.BCK01 = B.BCK01 and A.BillID = B.BillID JOIN DPK1 K WITH(NOLOCK) ON K.ID = B.DPK_ID WHERE A.AuditingDate IS NOT NULL UNION ALL SELECT B.BCK01, A.BBY01, B.Unit, B.PackSize, B.BatchNo , 0, A.DiffPrice, A.DiffAmount, 0, 0, 0, 0 , A.sysdate, '', '', '', 10 AS xTypeID, 0, A.DPK1_ID as DPK_ID, '', '', 0, '', A.DiffPresellPrice, A.DiffPresellAmount, 0, NULL, NULL FROM DPK2 A WITH(NOLOCK) JOIN DPK1 B WITH(NOLOCK) ON B.ID = A.DPK1_ID union all SELECT B.DeptID, B.BBY01, B.Unit, B.PackSize, '' , 0, 0, 0, B.DiffPrice, B.DiffAmount, 0, 0 , A.AuditingDate, A.Assessor, A.BillNO, A.Abstract, 11 AS xTypeID, 0 as xTtradeID, B.DSK_ID as DPK_ID , A.TerminalNO, '', 0, '', 0, 0, 0, null, null FROM DSJ1 A WITH(NOLOCK) JOIN DSJ3 B WITH(NOLOCK) ON B.BCK01 = A.BCK01 AND B.BillID = A.BillID WHERE B.DeptProPerty = 2 and A.AuditingDate IS NOT NULL GO