库存交易单

USE [Udit.Mms]
GO

/****** Object:  View [dbo].[TF_FILE]    Script Date: 2023/9/27 10:58:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[TF_FILE]
AS
--– MQ003: 13 借出、14借入、15 借出归还、16 借入归还
--–单别、单号、单据日期、交易对象（1.客户,2.供应商,3.人员,9.其它）、对象编号、对象简称
--–借出归还单头
--–单别、单号、单据日期、交易对象（1.客户,2.供应商,3.人员,9.其它）
--–对象编号、员工编号
select *
FROM      OPENQUERY(ERP, '
select replace(TF001+''-''+TF002,'''','''') TF001,CONVERT(datetime,TF024) TF024,TF004,TF005,TF006, MQ003
from UNP.dbo.INVTF
INNER JOIN UNP.dbo.CMSMQ ON MQ001=TF001 AND (MQ003=''13'' or MQ003=''14'')
WHERE TF020=''N''
UNION ALL
select replace(TH001+''-''+TH002,'''','''') TH001,CONVERT(datetime,TH023) TH023,TH004,TH005,TH008,MQ003
from UNP.dbo.INVTH AS A
INNER JOIN UNP.dbo.CMSMQ ON MQ001=TH001 AND (MQ003=''15'' or MQ003=''16'')
WHERE TH020=''N''')

GO

USE [Udit.Mms]
GO

/****** Object:  View [dbo].[TG_FILE]    Script Date: 2023/9/27 11:00:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



--借出归还单身
CREATE VIEW [dbo].[TG_FILE]
AS
--借出单单身
--单别、单号、序号、品号、品名、规格、转出仓库、转入仓库、数量、单价、金额
select *
FROM      OPENQUERY(ERP, '
select REPLACE(TG001+''-''+TG002,'''','''') TG001,convert(int,TG003) TG003,REPLACE(TG004,'''','''')TG004,
TG005,TG006,REPLACE(rtrim(ltrim(TG007)),'''','''') TG007,REPLACE(rtrim(ltrim(TG008)),'''','''')TG008,TG009,TG012,TG013,MQ003
from UNP.dbo.INVTG
INNER JOIN UNP.dbo.CMSMQ ON MQ001=TG001 AND (MQ003=''13'' OR MQ003=''14'')
WHERE TG022=''N''
UNION ALL
--借出归还单单身
--单别、单号、序号、品号、品名、规格、转出仓库、转入仓库、数量、单价、金额
select REPLACE(TI001+''-''+TI002,'''','''')TI001,convert(int,TI003) TI003,REPLACE(TI004,'''','''')TTI004,TI005,TI006,
REPLACE(rtrim(ltrim(TI007)),'''','''')TI007,REPLACE(rtrim(ltrim(TI008)),'''','''') TI008,TI009,TI012,TI013,MQ003
FROM UNP.dbo.INVTI
INNER JOIN UNP.dbo.CMSMQ ON MQ001=TI001 AND (MQ003=''15'' OR MQ003=''16'')
WHERE TI022=''N''')


GO