ALTER PROCEDURE proc_xsddgz_lj
AS
--声明变量
--销售订单
BEGIN
DECLARE @fbillno NVARCHAR(255)
DECLARE @ddfentryid INT
DECLARE @fhdfentryid INT
CREATE TABLE #t(DDID INT,FHDID INT ,CKDID INT,ZGYSDID INT,CWYSDID INT)
CREATE TABLE #tt(DDID INT,FHDID INT ,CKDID INT,ZGYSDID INT,CWYSDID INT)
CREATE TABLE #ttt(DDID INT,FHDID INT ,CKDID INT,ZGYSDID INT,CWYSDID INT)
CREATE TABLE #tttt(DDID INT,FHDID INT ,CKDID INT,ZGYSDID INT,CWYSDID INT)
DECLARE @t TABLE(DDID INT,FHDID INT ,CKDID INT,ZGYSDID INT,CWYSDID INT)
--DECLARE @proc_xsddgz_lj TABLE(
--DDID INT,FHDID INT ,CKDID INT,ZGYSDID INT,CWYSDID INT
--)
SET @fbillno = 'RA181274390'
INSERT INTO #t(DDID)SELECT soe.FENTRYID
FROM T_SAL_ORDER so
INNER JOIN dbo.T_SAL_ORDERENTRY soe ON so.FID = soe.FID
--WHERE so.FBILLNO = @fbillno
--SELECT *FROM #t
--drop TABLE #t
--DROP TABLE #tt
--发货单
INSERT INTO #tt(DDID,FHDID)
SELECT t.DDID,fhd.FENTRYID FROM #t t LEFT JOIN
(
SELECT sde.FENTRYID,sdel.FSID FROM dbo.T_SAL_DELIVERYNOTICEENTRY sde
INNER JOIN T_SAL_DELIVERYNOTICEENTRY_lk sdel ON sde.FENTRYID = sdel.FENTRYID
INNER JOIN T_SAL_DELIVERYNOTICE sd ON sd.fid = sde.FID
WHERE sd.FDOCUMENTSTATUS='C' )fhd ON t.DDID = fhd.FSID
TRUNCATE TABLE #t
INSERT INTO #t SELECT *FROM #tt
TRUNCATE TABLE #tt
--出库单
INSERT INTO #tt (DDID,FHDID,CKDID)
SELECT t.DDID,t.FHDID,ck.FENTRYID FROM #t t LEFT JOIN (
SELECT soe.FENTRYID,soel.FSID,soe.FACTQTY FROM dbo.T_SAL_OUTSTOCKENTRY soe
INNER JOIN dbo.T_SAL_OUTSTOCKENTRY_LK soel ON soe.FENTRYID = soel.FENTRYID
INNER JOIN T_SAL_OUTSTOCK so ON so.FID = soe.FID
WHERE so.FDOCUMENTSTATUS = 'C') ck ON ck.FSID =t.FHDID
TRUNCATE TABLE #t
INSERT INTO #t SELECT *FROM #tt
TRUNCATE TABLE #tt
--暂估应收
INSERT INTO #ttt (DDID,FHDID,CKDID,ZGYSDID)
SELECT t.DDID,t.FHDID,t.CKDID ,zgys.FENTRYID FROM #t t LEFT JOIN (
SELECT ree.FENTRYID,relk.FSID,ree.FPRICEQTY FROM dbo.T_AR_RECEIVABLE re
INNER JOIN dbo.T_AR_RECEIVABLEENTRY ree ON re.FID = ree.FID
INNER JOIN dbo.T_AR_RECEIVABLEENTRY_LK relk ON ree.FENTRYID = relk.FENTRYID
WHERE re.FSETACCOUNTTYPE=2 AND re.FDOCUMENTSTATUS = 'C' AND ree.FSOURCETYPE='SAL_OUTSTOCK')
zgys ON zgys.FSID = t.CKDID
TRUNCATE TABLE #t
INSERT INTO #t SELECT *FROM #ttt
TRUNCATE TABLE #ttt
--财务应收
INSERT INTO #tttt (DDID,FHDID,CKDID,ZGYSDID,CWYSDID)
SELECT t.DDID,t.FHDID,t.CKDID ,t.ZGYSDID,cwys.FENTRYID FROM #t t LEFT JOIN (
SELECT ree.FENTRYID,relk.FSID,ree.FPRICEQTY,re.FSETACCOUNTTYPE FROM dbo.T_AR_RECEIVABLE re
INNER JOIN dbo.T_AR_RECEIVABLEENTRY ree ON re.FID = ree.FID
INNER JOIN dbo.T_AR_RECEIVABLEENTRY_LK relk ON ree.FENTRYID = relk.FENTRYID
WHERE re.FSETACCOUNTTYPE=3 AND re.FDOCUMENTSTATUS = 'C')
cwys ON cwys.FSID = t.ZGYSDID
TRUNCATE TABLE #t
INSERT INTO #t SELECT *FROM #tttt
TRUNCATE TABLE #tttt
INSERT INTO @t ( DDID, FHDID, CKDID, ZGYSDID, CWYSDID )SELECT *FROM #t
DROP TABLE #t
DROP TABLE #tt
DROP TABLE #ttt
DROP TABLE #tttt
SELECT *FROM @t
end
----执行EXEC proc_xsddgz_lj