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),
預(yù)收金額 NUMERIC(18,6) DEFAULT(0),
未開票金額  NUMERIC(18,6) DEFAULT(0),
應(yīng)收凈額 NUMERIC(18,6) DEFAULT(0),
訂單剩余金額 NUMERIC(18,6) DEFAULT(0),
信用凈額 AS 信用限額+預(yù)收金額-未開票金額-應(yīng)收凈額-訂單剩余金額)

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

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

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

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

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