IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='應收_PROC' AND TYPE='P')
DROP PROC 應收_PROC
GO


create procedure 應收_PROC
@SUM CHAR(1)
AS
CREATE TABLE #應收

(
序號 INT IDENTITY(1,1),
科目編碼 VARCHAR(30),
銷售訂單 INT,
憑證號 INT,
行 INT,
貨幣 VARCHAR(10),
借方_原幣 NUMERIC(18,6) DEFAULT(0),
借方_本幣 NUMERIC(18,6) DEFAULT(0),
貸方_原幣 NUMERIC(18,6) DEFAULT(0),
貸方_本幣  NUMERIC(18,6) DEFAULT(0))


INSERT INTO #應收 (銷售訂單,憑證號,貨幣,借方_原幣,科目編碼 )
SELECT 銷售訂單,憑證號,貨幣,發票金額+增值稅金額,
'應收賬款'FROM 銷售記賬憑證
WHERE 發票金額+增值稅金額>0 
OR 發票金額+增值稅金額<0/*出庫時大于零,退貨時小于零*/
ORDER BY 憑證號 
INSERT INTO #應收 (銷售訂單,憑證號,貨幣,貸方_原幣,科目編碼 )
SELECT 銷售訂單,憑證號,貨幣,發票金額, '產品銷售收入' FROM 銷售記賬憑證
WHERE 發票金額>0  OR 發票金額<0 ORDER BY 憑證號

INSERT INTO #應收 (銷售訂單,憑證號,貨幣,貸方_原幣,科目編碼 )
SELECT 銷售訂單,憑證號,貨幣,增值稅金額 , '應交稅金-增值稅' FROM 銷售記賬憑證
WHERE 增值稅金額>0 OR 增值稅金額<0 ORDER BY 憑證號


DECLARE EXCS SCROLL CURSOR FOR
SELECT 外幣編碼,匯率,年月 FROM 貨幣匯率 
OPEN EXCS
DECLARE @外幣編碼 VARCHAR(30),@匯率 NUMERIC(18,6),@年月 DATETIME
FETCH EXCS INTO @外幣編碼,@匯率,@年月
WHILE @@FETCH_STATUS=0
BEGIN
UPDATE #應收 SET 借方_本幣=借方_原幣*@匯率,貸方_本幣=貸方_原幣*@匯率 WHERE 貨幣=@外幣編碼 
AND  year(GETDATE())=year(@年月)   AND month (GETDATE())=MONTH(@年月)
FETCH EXCS INTO @外幣編碼,@匯率,@年月
END
CLOSE EXCS
DEALLOCATE EXCS

IF @SUM='D'
SELECT * FROM #應收
ELSE
IF  @SUM='S'
BEGIN
SELECT 科目編碼,SUM(借方_原幣) AS 借方_原幣,SUM(貸方_原幣) AS  貸方_原幣,
SUM(借方_本幣) AS 借方_本幣,SUM(貸方_本幣) AS  貸方_本幣
FROM #應收 GROUP BY 科目編碼
END