IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='PROC_信用限額檢查' AND TYPE='P')
DROP PROC PROC_信用限額檢查
GO
create  PROC PROC_信用限額檢查
@CUST VARCHAR(10)
as
CREATE TABLE #C
(
客戶編碼 VARCHAR(10),
信用限額 NUMERIC(18,6) DEFAULT(0),
預收金額 NUMERIC(18,6) DEFAULT(0),
未開票金額  NUMERIC(18,6) DEFAULT(0),
應收凈額 NUMERIC(18,6) DEFAULT(0),
訂單剩余金額 NUMERIC(18,6) DEFAULT(0),
信用凈額 AS 信用限額+預收金額-未開票金額-應收凈額-訂單剩余金額)

CREATE TABLE #CC
(客戶編碼 VARCHAR(10),
銷售訂單 INT,
出庫金額 NUMERIC(18,6)  DEFAULT(0),
借方金額 NUMERIC(18,6)  DEFAULT(0))
/*插入信用限額*/
INSERT INTO #C (客戶編碼,信用限額)
SELECT 客戶編碼,信用限額 FROM 客戶主文件 WHERE 客戶編碼=@CUST
/*插入預收金額*/
INSERT INTO #C (客戶編碼,預收金額) SELECT 客戶編碼,預收金額 FROM 銷售單主表 WHERE 客戶編碼=@CUST

/*計算并插入訂單剩余金額(含增值稅)*/
INSERT INTO #C (客戶編碼,訂單剩余金額)
SELECT 客戶編碼,SUM((訂購數量-出庫數量)*發票單價*(1+增值稅率))
FROM 銷售出庫單明細表 WHERE 客戶編碼=@CUST  GROUP BY 客戶編碼/*訂購狀態的銷售訂單剩余金額*/
/*計算并插入應收余額*/
INSERT INTO #C (客戶編碼,應收凈額 )SELECT 客戶編碼,SUM(借方金額-貸方金額) 
FROM 應收賬款明細賬 WHERE 客戶編碼=@CUST GROUP BY 客戶編碼/*應收凈額*/
/*在發貨金額中,已生應收賬款的金額,即插入已開票金額*/
INSERT INTO #CC (客戶編碼,銷售訂單,借方金額 )SELECT 客戶編碼,銷售單,SUM(借方金額) 
FROM 應收賬款明細賬 WHERE 客戶編碼=@CUST GROUP BY 客戶編碼,銷售單

/*插入已發貨金額*/
INSERT INTO #Cc (客戶編碼,銷售訂單,出庫金額)
SELECT 客戶編碼,銷售單號,SUM(出庫數量*發票單價*(1+增值稅率))
FROM 銷售出庫單明細表 WHERE 客戶編碼=@CUST GROUP BY 客戶編碼,銷售單號/*訂購狀態的銷售訂單剩余金額*/

/*插入已發貨未生成應收賬款的金額,即未開票金額*/
INSERT INTO #C (客戶編碼,未開票金額)
SELECT 客戶編碼,SUM(出庫金額-借方金額)
FROM #CC WHERE 客戶編碼=@CUST GROUP BY 客戶編碼

SELECT 客戶編碼,	SUM(信用限額) AS 信用限額,SUM(預收金額) AS 預收金額,
SUM(未開票金額) AS 未開票金額,SUM(應收凈額) AS 應收凈額,SUM(訂單剩余金額) AS 訂單剩余金額,
SUM(信用凈額) AS 信用凈額  FROM #C
GROUP BY 客戶編碼
-- EXEC PROC_信用限額檢查 'C01'