应收折算本位币_proc.sql

SQL server 进销存软件数据库设计。大家积极下载

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='应收折算本位币_PROC' AND TYPE='P')
DROP PROC 应收折算本位币_PROC
GO
create  PROC 应收折算本位币_PROC
AS
CREATE TABLE [#应收明细本位币]
 (
	[客户编码] [varchar] (10)  ,
	[销售单] [int]  ,
	[货币] [varchar] (10)  ,
	[凭证号] [int] ,
	[发票号码] [varchar] (20),
	[发票日期] [datetime] NULL,
	[借方金额] [numeric](18, 6)  DEFAULT (0),
        借方本位币 AS 借方金额*汇率 ,
	[贷方金额] [numeric](18, 6)  DEFAULT (0),
        贷方本位币 AS 贷方金额*汇率,
	[余额] [numeric](18, 6)  DEFAULT (0),
        汇率 NUMERIC(18,6)DEFAULT (0),
        外币编码 VARCHAR(10),
        年月 DATETIME)

INSERT INTO #应收明细本位币 (客户编码,销售单,货币,凭证号,发票号码,发票日期,借方金额,贷方金额,余额,汇率,外币编码,年月)
select a.客户编码,a.销售单,a.货币,a.凭证号,a.发票号码,a.发票日期,a.借方金额,a.贷方金额,a.余额,b.汇率,b.外币编码,b.年月
from 应收账款明细账 a join 货币汇率 b on a.货币=b.外币编码 
where year(B.年月)= year(GETDATE())  AND MONTH(B.年月)=month (GETDATE())
SELECT 客户编码,销售单,货币,凭证号,发票号码,发票日期,借方金额,借方本位币,贷方金额,贷方本位币,余额 
from #应收明细本位币

-- EXEC 应收折算本位币_PROC