--select L.FNAME,A.FDIMENSIONID,A.FFINORGID from [dbo].T_HS_CALDIMENSIONS A
-- INNER JOIN T_ORG_ORGANIZATIONS_L L ON A.FFINORGID = L.FORGID AND L.FLOCALEID = 2052
USE [testdb0607]
GO
/****** Object: StoredProcedure [dbo].[STOCKSUM] Script Date: 2021/8/27 9:42:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[STOCKSUM]
@Organ nvarchar(50), --组织 5
@Organs nvarchar(50), --12352676
@year int, --年 2020
@month int, --月 12
@begindate datetime, --开始日期
@enddate datetime --结束日期
--@id int output
as
begin
if OBJECT_ID(N'testdb0607..TMP08077C99FB5011EB9CBD70B5E86',N'U') IS not NULL
begin
drop table TMP08077C99FB5011EB9CBD70B5E86
end
if OBJECT_ID(N'testdb0607..StockIn',N'U') IS not NULL
begin
drop table StockIn
end
CREATE TABLE #TM_HS_StockInSummaryData2 (FDIMID INT NULL, FACCTGDIMID INT NULL, FACCTGSYSTEMID INT NULL, FACCTGSYSTEMNAME NVARCHAR (255) NULL, FACCTGORGID INT NULL, FACCTGORGNAME NVARCHAR (255) NULL, FACCTPOLICYID INT NULL, FACCTPOLICYNAME NVARCHAR (255) NULL, FYear INT NULL, FPeriod INT NULL, FMATERIALBASEID INT NOT NULL DEFAULT 0, FMATERIALID NVARCHAR (255) NULL, FMATERIALNAME NVARCHAR (255) NULL, FMODEL NVARCHAR (510) NULL, FLOTNO VARCHAR (1020) NULL, FASSIPROPERTYID NVARCHAR (510) NULL, FMATERPROPERTY NVARCHAR (255) NULL, FMATERTYPE NVARCHAR (255) NULL, FBOMNO VARCHAR (255) NULL, FPLANNO NVARCHAR (255) NULL, FSEQUENCENO VARCHAR (255) NULL, FPROJECTNO VARCHAR (255) NULL, FOWNERID INT NULL, FOWNERNAME NVARCHAR (255) NULL, FSTOCKORGID INT NULL, FSTOCKORGNAME NVARCHAR (100) NULL, FSTOCKID INT NULL, FSTOCKNAME NVARCHAR (255) NULL, FSTOCKPLACEID INT NULL, FSTOCKPLACENAME NVARCHAR (255) NULL, FACCTGRANGEID NVARCHAR (255) NULL, FACCTGRANGENAME NVARCHAR (255) NULL, FEXPENSEID NVARCHAR (255) NULL, FUNITNAME NVARCHAR (255) NULL, FVALUATION NVARCHAR (255) NULL, FINITQTY DECIMAL (28, 10) NULL, FINITPRICE DECIMAL (28, 10) NULL, FINITAMOUNT DECIMAL (28, 10) NULL, FRECEIVEQTY DECIMAL (28, 10) NULL, FRECEIVEPRICE DECIMAL (28, 10) NULL, FRECEIVEAMOUNT DECIMAL (28, 10) NULL, FEXPENSENAME NVARCHAR (255) NULL, FSENDQTY DECIMAL (28, 10) NULL, FSENDPRICE DECIMAL (28, 10) NULL, FSENDAMOUNT DECIMAL (28, 10) NULL, FENDQTY DECIMAL (28, 10) NULL, FENDPRICE DECIMAL (28, 10) NULL, FENDAMOUNT DECIMAL (28, 10) NULL, FDBENDQTY DECIMAL (28, 10) NULL, FDBENDAMOUNT DECIMAL (28, 10) NULL, FDIFFENDQTY DECIMAL (28, 10) NULL, FDIFFENDAMOUNT DECIMAL (28, 10) NULL, FISTOTAL INT NULL, FASSIPROPNAME NVARCHAR (510) NULL, FSTOCKSTATUSID NVARCHAR (255) NULL, FGroupByField NVARCHAR (255) NULL, FDetailBillFormID NVARCHAR (255) NULL, FUnitID INT NULL)
CREATE CLUSTERED INDEX I655d9de9481647b3adc1bb0f29a9f ON #TM_HS_StockInSummaryData2 (FDIMID, FACCTGDIMID, FYEAR, FPERIOD)
INSERT INTO #TM_HS_StockInSummaryData2 with(tablock) (FDIMID, FACCTGDIMID, FACCTGSYSTEMID, FACCTGORGID, FACCTPOLICYID, FYear, FPeriod, FMATERIALBASEID, FMATERIALID, FMATERIALNAME, FMODEL, FLOTNO, FASSIPROPERTYID, FMATERPROPERTY, FMATERTYPE, FBOMNO, FPLANNO, FSEQUENCENO, FPROJECTNO, FOWNERID, FSTOCKORGID, FSTOCKSTATUSID, FSTOCKID, FSTOCKPLACEID, FACCTGRANGEID, FUNITID, FVALUATION, FDetailBillFormID, FISTOTAL)
SELECT HSDIM.FENTRYID fdimid, HSDIM.FENTRYID facctgdimid, CALD.FACCTSYSTEMID facctgsystemid, CALD.FFINORGID facctgorgid, CALD.FACCTPOLICYID facctpolicyid, @year, @month, HSDIM.FMASTERID fmaterialbaseid, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, MAT_L.FSPECIFICATION fmodel, HSDIM.FLOTNUMBER flotno, HSDIM.FAUXPROPID fassipropertyid, MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, HSDIM.FBOMID fbomno, HSDIM.FMTONO fplanno, N'' fsequenceno, HSDIM.FPROJECTNO fprojectno, 0 fownerid, 0 fstockorgid, 0 fstockstatusid, HSDIM.FSTOCKID fstockid, HSDIM.FSTOCKLOCID fstockplaceid, HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 FROM
T_HS_OUTACCTG AG INNER JOIN T_HS_INIVBALANCE_H IV ON AG.FID = IV.FID INNER JOIN T_HS_CALDIMENSIONS CALD ON (AG.FDIMENSIONID = CALD.FDIMENSIONID AND AG.FDIMENSIONID = @Organ) INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = IV.FACCTGDIMEENTRYID INNER JOIN t_bd_Material MAT ON HSDIM.FMASTERID = MAT.FMATERIALID INNER JOIN T_BD_MATERIALBASE MAT_T ON (MAT.FMATERIALID = MAT_T.FMATERIALID AND MAT.FUSEORGID = MAT_T.FUSEORGID) LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = HSDIM.FSTOCKID LEFT OUTER JOIN t_bd_Material_l
MAT_L ON (MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052) WHERE (((((((((IV.FENDINITKEY = '0' AND AG.FYEAR = @year) AND AG.FPERIOD = @month) AND CAlD.FACCTSYSTEMID = 1) AND CAlD.FFINORGID = @Organs) AND CAlD.FACCTPOLICYID = 1) AND AG.FYEAR = @year) AND AG.FPERIOD = @month))) UNION SELECT HSDIM.FENTRYID fdimid, HSDIM.FENTRYID facctgdimid, CALD.FACCTSYSTEMID facctgsystemid, CALD.FFINORGID facctgorgid, CALD.FACCTPOLICYID facctpolicyid, @year, @month, HSDIM.FMASTERID fmaterialbaseid, MAT.FNUMBER
fmaterialid, MAT_L.FNAME fmaterialname, MAT_L.FSPECIFICATION fmodel, HSDIM.FLOTNUMBER flotno, HSDIM.FAUXPROPID fassipropertyid, MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, HSDIM.FBOMID fbomno, HSDIM.FMTONO fplanno, N'' fsequenceno, HSDIM.FPROJECTNO fprojectno, 0
fownerid, 0 fstockorgid, 0 fstockstatusid, HSDIM.FSTOCKID fstockid, HSDIM.FSTOCKLOCID fstockplaceid, HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 FROM T_HS_OUTACCTG AG INNER JOIN T_HS_CALDIMENSIONS CALD
ON (AG.FDIMENSIONID = CALD.FDIMENSIONID AND AG.FDIMENSIONID = @Organ) INNER JOIN T_HS_OUTINSTOCKSEQ_H SEQ ON (AG.FID = SEQ.FACCTGID AND SEQ.FDOCUMENTSTATUS = 'C') INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = SEQ.FACCTGDIMEENTRYID INNER JOIN t_bd_Material MAT ON
HSDIM.FMASTERID = MAT.FMATERIALID INNER JOIN T_BD_MATERIALBASE MAT_T ON (MAT.FMATERIALID = MAT_T.FMATERIALID AND MAT.FUSEORGID = MAT_T.FUSEORGID) LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = HSDIM.FSTOCKID LEFT OUTER JOIN t_bd_Material_l MAT_L ON ((MAT.FMATERIALID =
MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052) AND MAT.FUSEORGID = MAT_L.FUSEORGID) WHERE (((((((1 = 1 AND CAlD.FACCTSYSTEMID = 1) AND CAlD.FFINORGID = @Organs) AND CAlD.FACCTPOLICYID = 1) AND AG.FYEAR = @year) AND AG.FPERIOD = @month))) UNION SELECT HSDIM.FENTRYID fdimid, HSDIM.FENTRYID facctgdimid, CALD.FACCTGSYSTEMID facctgsystemid, CALD.FACCTORGID facctgorgid, CALD.FACCTPOLICYID facctpolicyid, @year, @month, HSDIM.FMASTERID fmaterialbaseid, MAT.FNUMBER fmaterialid, MAT_L.FNAME fmaterialname, MAT_L.FSPECIFICATION fmodel, HSDIM.FLOTNUMBER flotno, HSDIM.FAUXPROPID fassipropertyid, MAT_T.FERPCLSID fmaterproperty, MAT_T.FCATEGORYID fmatertype, HSDIM.FBOMID fbomno, HSDIM.FMTONO fplanno, N'' fsequenceno, HSDIM.FPROJECTNO fprojectno, 0 fownerid, 0 fstockorgid, 0 fstockstatusid, HSDIM.FSTOCKID fstockid, HSDIM.FSTOCKLOCID fstockplaceid, HSDIM.FACCTGRANGEID facctgrangeid, MAT_T.FBASEUNITID funitid, HSDIM.FVALUATIONMETHOD fvaluatio, 'HS_INOUTSTOCKDETAILRPT', 0 FROM T_HS_AdjustmentBill CALD INNER JOIN T_HS_AdjustmentBillEntry ADJENTRY ON CALD.FID = ADJENTRY.FID INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = ADJENTRY.FACCTGDIMEENTRYID INNER JOIN t_bd_Material MAT ON HSDIM.FMASTERID = MAT.FMATERIALID INNER JOIN T_BD_MATERIALBASE MAT_T ON (MAT.FMATERIALID = MAT_T.FMATERIALID AND MAT.FUSEORGID = MAT_T.FUSEORGID) INNER JOIN T_BD_ACCOUNTPERIOD AG ON (CALD.FDATE BETWEEN AG.FPERIODSTARTDATE AND AG.FPERIODENDDATE) LEFT OUTER JOIN t_BD_Stock STOCK ON STOCK.FSTOCKID = HSDIM.FSTOCKID LEFT OUTER JOIN t_bd_Material_l MAT_L ON ((MAT.FMATERIALID = MAT_L.FMATERIALID AND MAT_L.FLOCALEID = 2052) AND MAT.FUSEORGID = MAT_L.FUSEORGID) WHERE ((((((((((CALD.FBUSINESSTYPE = '0' OR CALD.FISACCTGGENERATE = '1') AND CALD.FDOCUMENTSTATUS = 'C') AND CALD.FFORBIDSTATUS = 'A') AND CAlD.FACCTGSYSTEMID = 1) AND CAlD.FACCTORGID = @Organs) AND CAlD.FACCTPOLICYID = 1) AND AG.FYEAR = @year) AND AG.FPERIOD = @month)) )
MERGE INTO #TM_HS_StockInSummaryData2 T using(SELECT DIM.FENTRYID fdimeentryid, T1.FACCTGDIMEENTRYID, Sum(T1.FQty) finitqty, T0.FYEAR, T0.FPERIOD FROM T_HS_OUTACCTG T0 INNER JOIN T_HS_INIVBALANCE_H T1 ON T0.FID = T1.FID INNER JOIN T_HS_CALDIMENSIONS CALD ON T0.FDIMENSIONID = CALD.FDIMENSIONID INNER JOIN T_HS_StockDimension DIM ON T1.FACCTGDIMEENTRYID = DIM.FENTRYID WHERE (((((T1.FENDINITKEY = '0' AND CALD.FACCTSYSTEMID = 1) AND CALD.FFINORGID = @Organs) AND CALD.FACCTPOLICYID = 1) AND T0.FYEAR = @year) AND T0.FPERIOD = @month) GROUP BY DIM.FENTRYID, T1.FACCTGDIMEENTRYID, T0.FYEAR, T0.FPERIOD) RET ON ((((T.FDIMID = RET.FDimeEntryId AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID) AND T.FYEAR = RET.fyear) AND T.FPERIOD = RET.fperiod)) WHEN MATCHED THEN UPDATE SET T.FINITQTY = RET.FINITQTY;
MERGE INTO #TM_HS_StockInSummaryData2 t using(SELECT DIM.FENTRYID fdimeentryid, T1.FACCTGDIMEENTRYID, CAST(Sum(t1.FAMOUNT) AS NUMERIC(28, 10)) finitamount, T0.FYEAR, T0.FPERIOD FROM T_HS_OUTACCTG T0 INNER JOIN T_HS_INIVBALANCE_H T1 ON T0.FID = T1.FID INNER JOIN T_HS_CALDIMENSIONS CALD ON T0.FDIMENSIONID = CALD.FDIMENSIONID INNER JOIN T_HS_StockDimension DIM ON T1.FACCTGDIMEENTRYID = DIM.FENTRYID WHERE (((((T1.FENDINITKEY = '0' AND CALD.FACCTSYSTEMID = 1) AND CALD.FFINORGID = @Organs) AND CALD.FACCTPOLICYID = 1) AND T0.FYEAR = @year) AND T0.FPERIOD = @month) GROUP BY DIM.FENTRYID, T1.FACCTGDIMEENTRYID, T0.FYEAR, T0.FPERIOD) RET ON ((((T.FDIMID = RET.FDimeEntryId AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID) AND T.FYEAR = RET.fyear) AND T.FPERIOD = RET.fperiod)) WHEN MATCHED THEN UPDATE SET T.FINITPRICE = CASE T.FINITQTY WHEN 0 THEN 0 ELSE ROUND((RET.FINITAMOUNT / T.FINITQTY), 10) END, T.FINITAMOUNT = RET.FINITAMOUNT;
MERGE INTO #TM_HS_StockInSummaryData2 T using(SELECT T1.FACCTGDIMEENTRYID, DIM.FENTRYID fdimeentryid, Sum(CASE WHEN T1.FINOUTINDEX = '1' THEN T1.FQTY ELSE 0 END) freceiveqty, Sum(CASE WHEN T1.FINOUTINDEX = '0' THEN T1.FQTY ELSE 0 END) fsendqty FROM T_HS_OUTACCTG T0 INNER JOIN T_HS_CALDIMENSIONS CALD ON T0.FDIMENSIONID = CALD.FDIMENSIONID INNER JOIN T_HS_OUTINSTOCKSEQ_H T1 ON (T0.FID = T1.FACCTGID AND T1.FDOCUMENTSTATUS = 'C') INNER JOIN T_HS_StockDimension DIM ON T1.FACCTGDIMEENTRYID = DIM.FENTRYID WHERE ((((FACCTSYSTEMID = 1 AND FFINORGID = @Organs) AND FACCTPOLICYID = 1) AND T0.FYEAR = @year) AND T0.FPERIOD = @month) GROUP BY T1.FACCTGDIMEENTRYID, DIM.FENTRYID) RET ON ((T.FDIMID = RET.FDimeEntryId AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID)) WHEN MATCHED THEN UPDATE SET T.FRECEIVEQTY = RET.FRECEIVEQTY, T.FSENDQTY = RET.FSENDQTY;
MERGE INTO #TM_HS_StockInSummaryData2 T using(SELECT CAST(sum(rt2.FRECEIVEAMOUNT) AS NUMERIC(28, 10)) freceiveamount, CAST(sum(rt2.FSENDAMOUNT) AS NUMERIC(28, 10)) fsendamount, FACCTGDIMEENTRYID, FDIMEENTRYID FROM (SELECT t0.fyear, t0.fperiod, DIM.FENTRYID fdimeentryid, t1.FACCTGDIMEENTRYID, CASE WHEN (t1.FInOutIndex = '1' AND (t1.FINDEXENTRYID <> 32)) THEN t1.FAcctgAmount ELSE 0 END freceiveamount, CASE WHEN t1.FInOutIndex = '0' THEN t1.FAcctgAmount WHEN t1.FINDEXENTRYID = 32 THEN (0 - t1.FAcctgAmount) ELSE 0 END fsendamount FROM T_HS_OUTACCTG t0 INNER JOIN T_HS_CALDIMENSIONS cald ON t0.FDIMENSIONID = cald.FDIMENSIONID INNER JOIN T_HS_OUTINSTOCKSEQ_H t1 ON (t0.FID = t1.FACCTGID AND t1.FDocumentStatus = 'C') INNER JOIN T_HS_StockDimension DIM ON T1.FACCTGDIMEENTRYID = DIM.FENTRYID WHERE ((((cald.FACCTSYSTEMID = 1 AND cald.FFINORGID = @Organs) AND cald.FACCTPOLICYID = 1) AND T0.FYEAR = @year) AND T0.FPERIOD = @month) UNION ALL SELECT OUTACCT.FYEAR, OUTACCT.FPERIOD, DIM.FENTRYID fdimeentryid, ADJENTRY.FACCTGDIMEENTRYID, CASE WHEN T0.FINOUTINDEX = '1' THEN ADJENTRY.FADJUSTMENTAMOUNT ELSE 0 END freceiveamount, CASE WHEN T0.FINOUTINDEX = '1' THEN 0 ELSE ADJENTRY.FADJUSTMENTAMOUNT END fsendamount FROM T_HS_AdjustmentBill T0 INNER JOIN T_HS_AdjustmentBillEntry ADJENTRY ON T0.FID = ADJENTRY.FID INNER JOIN T_HS_OUTACCTG OUTACCT ON OUTACCT.FID = T0.FACCTGID INNER JOIN T_HS_StockDimension DIM ON ADJENTRY.FACCTGDIMEENTRYID = DIM.FENTRYID WHERE (((((((T0.FACCTGSYSTEMID = 1 AND T0.FACCTORGID = @Organs) AND T0.FACCTPOLICYID = 1) AND (T0.FBUSINESSTYPE = '0' OR T0.FISACCTGGENERATE = '1')) AND (T0.FDATE >= @begindate )) AND (T0.FDATE < @enddate)) AND T0.FDOCUMENTSTATUS = 'C') AND T0.FFORBIDSTATUS = 'A')) rt2 GROUP BY FACCTGDIMEENTRYID, FDIMEENTRYID) ret ON ((T.FDIMID = RET.FDIMEENTRYID AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID)) WHEN MATCHED THEN UPDATE SET t.FRECEIVEPRICE = CASE t.FRECEIVEQTY WHEN 0 THEN 0 ELSE ROUND((ret.FRECEIVEAMOUNT / t.FRECEIVEQTY), 10) END, t.FSENDPRICE = CASE t.FSENDQTY WHEN 0 THEN 0 ELSE ROUND((ret.FSENDAMOUNT / t.FSENDQTY), 10) END, t.FRECEIVEAMOUNT = ret.FRECEIVEAMOUNT, t.FSENDAMOUNT = ret.FSENDAMOUNT;
MERGE INTO #TM_HS_StockInSummaryData2 T using(SELECT CAST(sum(rt2.FRECEIVEAMOUNT) AS NUMERIC(28, 10)) freceiveamount, CAST(sum(rt2.FSENDAMOUNT) AS NUMERIC(28, 10)) fsendamount, FACCTGDIMEENTRYID, FDIMEENTRYID FROM (SELECT t0.fyear, t0.fperiod, DIM.FENTRYID fdimeentryid, t1.FACCTGDIMEENTRYID, CASE WHEN (t1.FInOutIndex = '1' AND (t1.FINDEXENTRYID <> 32)) THEN t1.FAcctgAmount ELSE 0 END freceiveamount, CASE WHEN t1.FInOutIndex = '0' THEN t1.FAcctgAmount WHEN t1.FINDEXENTRYID = 32 THEN (0 - t1.FAcctgAmount) ELSE 0 END fsendamount FROM T_HS_OUTACCTG t0 INNER JOIN T_HS_CALDIMENSIONS cald ON t0.FDIMENSIONID = cald.FDIMENSIONID INNER JOIN T_HS_OUTINSTOCKSEQ_H t1 ON (t0.FID = t1.FACCTGID AND t1.FDocumentStatus = 'C') INNER JOIN T_HS_StockDimension DIM ON T1.FACCTGDIMEENTRYID = DIM.FENTRYID WHERE ((((cald.FACCTSYSTEMID = 1 AND cald.FFINORGID = @Organs) AND cald.FACCTPOLICYID = 1) AND T0.FYEAR = @year) AND T0.FPERIOD = @month) UNION ALL SELECT OUTACCT.FYEAR, OUTACCT.FPERIOD, DIM.FENTRYID fdimeentryid, ADJENTRY.FACCTGDIMEENTRYID, CASE WHEN T0.FINOUTINDEX = '1' THEN ADJENTRY.FADJUSTMENTAMOUNT ELSE 0 END freceiveamount, CASE WHEN T0.FINOUTINDEX = '1' THEN 0 ELSE ADJENTRY.FADJUSTMENTAMOUNT END fsendamount FROM T_HS_AdjustmentBill T0 INNER JOIN T_HS_AdjustmentBillEntry ADJENTRY ON T0.FID = ADJENTRY.FID INNER JOIN T_HS_OUTACCTG OUTACCT ON OUTACCT.FID = T0.FACCTGID INNER JOIN T_HS_StockDimension DIM ON ADJENTRY.FACCTGDIMEENTRYID = DIM.FENTRYID WHERE (((((((T0.FACCTGSYSTEMID = 1 AND T0.FACCTORGID = @Organs) AND T0.FACCTPOLICYID = 1) AND (T0.FBUSINESSTYPE = '0' OR T0.FISACCTGGENERATE = '1')) AND (T0.FDATE >= @begindate)) AND (T0.FDATE < @enddate)) AND T0.FDOCUMENTSTATUS = 'C') AND T0.FFORBIDSTATUS = 'A')) rt2 GROUP BY FACCTGDIMEENTRYID, FDIMEENTRYID) ret ON ((T.FDIMID = RET.FDIMEENTRYID AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID)) WHEN MATCHED THEN UPDATE SET t.FRECEIVEPRICE = CASE t.FRECEIVEQTY WHEN 0 THEN 0 ELSE ROUND((ret.FRECEIVEAMOUNT / t.FRECEIVEQTY), 10) END, t.FSENDPRICE = CASE t.FSENDQTY WHEN 0 THEN 0 ELSE ROUND((ret.FSENDAMOUNT / t.FSENDQTY), 10) END, t.FRECEIVEAMOUNT = ret.FRECEIVEAMOUNT, t.FSENDAMOUNT = ret.FSENDAMOUNT;
UPDATE #TM_HS_StockInSummaryData2 SET FENDQTY = ((ISNULL(FINITQTY, 0) + ISNULL(FRECEIVEQTY, 0)) - ISNULL(FSENDQTY, 0)), FENDAMOUNT = ((ISNULL(FINITAMOUNT, 0) + ISNULL(FRECEIVEAMOUNT, 0)) - ISNULL(FSENDAMOUNT, 0)), FENDPRICE = CASE WHEN ((ISNULL(FINITQTY, 0) + ISNULL(FRECEIVEQTY, 0)) - ISNULL(FSENDQTY, 0)) = 0 THEN 0 ELSE ROUND((((ISNULL(FINITAMOUNT, 0) + ISNULL(FRECEIVEAMOUNT, 0)) - ISNULL(FSENDAMOUNT, 0)) / ((ISNULL(FINITQTY, 0) + ISNULL(FRECEIVEQTY, 0)) - ISNULL(FSENDQTY, 0))), 10) END
MERGE INTO #TM_HS_StockInSummaryData2 T using(SELECT FDIMEENTRYID, FACCTGDIMEENTRYID, Sum(FQty) fendqty, Sum(FAmount) fendamount, FYEAR, FPERIOD FROM (SELECT DIM.FENTRYID fdimeentryid, T1.FACCTGDIMEENTRYID, T1.FQty, T1.FAmount, T0.FYEAR, T0.FPERIOD FROM T_HS_OUTACCTG T0 INNER JOIN T_HS_INIVBALANCE_H T1 ON T0.FID = T1.FID INNER JOIN T_HS_CALDIMENSIONS CALD ON T0.FDIMENSIONID = CALD.FDIMENSIONID INNER JOIN T_HS_StockDimension DIM ON T1.FACCTGDIMEENTRYID = DIM.FENTRYID WHERE (((((T1.FENDINITKEY = '1' AND CALD.FACCTSYSTEMID = 1) AND CALD.FFINORGID = @Organs) AND CALD.FACCTPOLICYID = 1) AND T0.FYEAR = @year) AND T0.FPERIOD = 12) UNION ALL SELECT T1.FACCTGDIMEENTRYID, T1.FACCTGDIMEENTRYID, 0, -1 * T1.FADJUSTMENTAMOUNT, OUTACCT.FYEAR, OUTACCT.FPERIOD FROM T_HS_AdjustmentBill T0 INNER JOIN T_HS_AdjustmentBillEntry T1 ON T1.FID = T0.FID INNER JOIN T_HS_OUTACCTG OUTACCT ON OUTACCT.FID = T0.FACCTGID WHERE (((((((T0.FBUSINESSTYPE = '0' AND T0.FACCTGSYSTEMID = 1) AND T0.FACCTORGID = @Organs) AND T0.FACCTPOLICYID = 1) AND (T0.FDATE >= @begindate)) AND (T0.FDATE < @enddate)) AND T0.FDOCUMENTSTATUS = 'C') AND T0.FFORBIDSTATUS = 'A')) tm GROUP BY TM.FDIMEENTRYID, TM.FACCTGDIMEENTRYID, TM.FYEAR, TM.FPERIOD) RET ON ((((T.FDIMID = RET.FDimeEntryId AND T.FACCTGDIMID = RET.FACCTGDIMEENTRYID) AND T.FYEAR = RET.fyear) AND T.FPERIOD = RET.fperiod)) WHEN MATCHED THEN UPDATE SET T.FDBEndQTY = RET.FEndQTY, T.FDBEndAmount = RET.FEndAmount;
UPDATE #TM_HS_StockInSummaryData2 SET FDIFFEndQTY = (ISNULL(FENDQTY, 0) - ISNULL(FDBENDQTY, 0)), FDIFFENDAMOUNT = (ISNULL(FENDAMOUNT, 0) - ISNULL(FDBENDAMOUNT, 0))
INSERT INTO #TM_HS_StockInSummaryData2 with(tablock) (FMATERIALID, FMATERIALNAME, FMODEL, FUNITID, FMATERTYPE, FMATERPROPERTY, FMATERIALBASEID, FDIMID, FISTOTAL, FINITQTY, FINITPRICE, FINITAMOUNT, FRECEIVEQTY, FRECEIVEPRICE, FRECEIVEAMOUNT, FSENDQTY, FSENDPRICE, FSENDAMOUNT, FENDQTY, FENDPRICE, FENDAMOUNT) SELECT FMATERIALID, Min(FMATERIALNAME) fmaterialname, Min(FMODEL) fmodel, min(FUNITID) funitid, min(FMATERTYPE) fmatertype, min(FMATERPROPERTY) fmaterproperty, min(FMATERIALBASEID) fmaterialbaseid, (max(FDIMID) + 1) fdimid, 3 fistotal, sum(FINITQTY) finitqty, CASE WHEN sum(FINITQTY) = 0 THEN 0 ELSE ROUND((CAST(sum(FINITAMOUNT) AS NUMERIC(28, 10)) / sum(FINITQTY)), 10) END finitprice, sum(FINITAMOUNT) finitamount, sum(FRECEIVEQTY) freceiveqty, CASE WHEN sum(FRECEIVEQTY) = 0 THEN 0 ELSE ROUND((CAST(sum(FRECEIVEAMOUNT) AS NUMERIC(28, 10)) / sum(FRECEIVEQTY)), 10) END freceiveprice, sum(FRECEIVEAMOUNT) freceiveamount, sum(FSENDQTY) fsendqty, CASE WHEN sum(FSENDQTY) = 0 THEN 0 ELSE ROUND((CAST(sum(FSENDAMOUNT) AS NUMERIC(28, 10)) / sum(FSENDQTY)), 10) END fsendprice, sum(FSENDAMOUNT) fsendamount, sum(FENDQTY) fendqty, CASE WHEN sum(FENDQTY) = 0 THEN 0 ELSE ROUND((CAST(sum(FENDAMOUNT) AS NUMERIC(28, 10)) / sum(FENDQTY)), 10) END fendprice, sum(FENDAMOUNT) fendamount FROM #TM_HS_StockInSummaryData2 t0 GROUP BY FMATERIALID
DELETE FROM #TM_HS_StockInSummaryData2 WHERE (((((ISNULL(FINITQTY, 0) = 0 AND ISNULL(FINITAmount, 0) = 0) AND ISNULL(FRECEIVEQTY, 0) = 0) AND ISNULL(FRECEIVEamount, 0) = 0) AND ISNULL(FSENDQTY, 0) = 0) AND ISNULL(FSENDamount, 0) = 0)
MERGE INTO #TM_HS_StockInSummaryData2 t0 using(SELECT T.FID, CASE WHEN ISNULL(T.FShowName, N' ') = N' ' THEN CONVERT(VARCHAR(8000), N'') ELSE LEFT(T.FShowName, (LEN(T.FShowName) - 1)) END fshowname FROM (SELECT t0.FID fid, ((((CASE WHEN ISNULL(st01_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'') ELSE (CONVERT(NVARCHAR(255), st01_L.FNAME) + CONVERT(VARCHAR(8000), ';')) END + CASE WHEN ISNULL(st02_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'') ELSE (CONVERT(NVARCHAR(255), st02_L.FNAME) + CONVERT(VARCHAR(8000), ';')) END) + CASE WHEN ISNULL(st03_L.FNAME, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'') ELSE (CONVERT(NVARCHAR(255), st03_L.FNAME) + CONVERT(VARCHAR(8000), ';')) END) + CASE WHEN ISNULL(t0.FOPCODE, N' ') = ' ' THEN CONVERT(VARCHAR(8000), N'') ELSE (CONVERT(NVARCHAR(255), t0.FOPCODE) + CONVERT(VARCHAR(8000), ';')) END) + CONVERT(VARCHAR(8000), N'')) fshowname FROM T_BAS_FLEXVALUESDETAIL t0 LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st01_L ON (t0.FF100004 = st01_L.FEntryID AND st01_L.FLocaleId = 2052) LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st02_L ON (t0.FF100016 = st02_L.FEntryID AND st02_L.FLocaleId = 2052) LEFT OUTER JOIN T_BAS_FLEXVALUESENTRY_L st03_L ON (t0.FF100016 = st03_L.FEntryID AND st03_L.FLocaleId = 2052)) t) t1 ON (t1.FID = t0.FSTOCKPLACEID) WHEN MATCHED THEN UPDATE SET t0.FSTOCKPLACENAME = CASE WHEN (LEN(t1.FShowName) >= 255) THEN (LEFT(t1.FShowName, 252) + '...') ELSE t1.FShowName END;
SELECT T.FMATERIALBASEID, T.FMATERIALID, T.FMATERIALNAME, GROUPL.FNAME fmaterialgroup, T.FMODEL, T.FLOTNO, T.FASSIPROPERTYID, ENUML.FCAPTION fmaterproperty, MT.FNAME fmatertype, BOM.FNUMBER fbomno, T.FPLANNO, T.FSEQUENCENO, T.FPROJECTNO, T.FOWNERID, OWN.FNAME fownername,
T.FSTOCKORGID, SORG.FNAME fstockorgname, T.FSTOCKID, STOCK.FNAME fstockname, T.FSTOCKPLACEID, T.FSTOCKPLACENAME, RANG.FNUMBER facctgrangeid, RANGL.FNAME facctgrangename, UNITL.FNAME funitname, T.FUnitID, T.FINITQTY, T.FINITPRICE, T.FINITAMOUNT, T.FRECEIVEQTY, T.FRECEIVEPRICE,
T.FRECEIVEAMOUNT, T.FSENDQTY, T.FSENDPRICE, T.FSENDAMOUNT, T.FENDQTY, T.FENDPRICE, T.FENDAMOUNT, t.FSTOCKSTATUSID, sts.FNAME fstockstatusname, T.FDIMID, T.FACCTGDIMID, T.FASSIPROPNAME, 2 fdigits, 6 fpricedigits, unit.FPRECISION fqtydigits, ENUML1.FCAPTION fvaluation, T.FIsTotal,
T.FGroupByFIeld, T.FDetailBillFormId fdetailreportformid, ROW_NUMBER() OVER( ORDER BY T.FMATERIALID ASC, T.FDIMID ASC, T.FACCTGDIMID ASC, T.FISTOTAL DESC) fidentityid ,@Organ as Organ1,@year as year1,@month as month1
INTO TMP08077C99FB5011EB9CBD70B5E86
FROM #TM_HS_StockInSummaryData2 T LEFT OUTER JOIN T_BD_STOCKSTATUS_L sts ON (t.FSTOCKSTATUSID = sts.FSTOCKSTATUSID AND sts.FLOCALEID = 2052) LEFT OUTER JOIN T_BD_UNIT unit ON unit.funitid = t.FUnitID LEFT OUTER JOIN T_HS_ACCTGRANGE RANG ON T.FACCTGRANGEID = RANG.FACCTGRANGEID LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L SORG ON (T.FSTOCKORGID = SORG.FORGID AND SORG.FLOCALEID = 2052) LEFT OUTER JOIN T_ORG_ORGANIZATIONS_L OWN ON (T.FOWNERID = OWN.FORGID AND OWN.FLOCALEID = 2052) LEFT OUTER JOIN t_BD_Stock_L STOCK ON (T.FSTOCKID = STOCK.FSTOCKID AND STOCK.FLOCALEID = 2052) LEFT OUTER JOIN T_HS_ACCTGRANGE_L RANGL ON (T.FACCTGRANGEID = RANGL.FACCTGRANGEID AND RANGL.FLOCALEID = 2052) LEFT OUTER JOIN T_BD_UNIT_L UNITL ON (T.FUNITID = UNITL.FUNITID AND UNITL.FLOCALEID = 2052) LEFT OUTER JOIN T_BD_MATERIALCATEGORY_L MT ON (T.FMATERTYPE = MT.FCATEGORYID AND MT.FLOCALEID = 2052) LEFT OUTER JOIN t_eng_bom BOM ON BOM.FID = T.FBOMNO LEFT OUTER JOIN T_META_FORMENUMITEM ENUM ON (ENUM.FID = 'ac14913e-bd72-416d-a50b-2c7432bbff63' AND ENUM.FVALUE = T.FMATERPROPERTY) LEFT OUTER JOIN T_META_FORMENUMITEM_L ENUML ON (ENUM.FENUMID = ENUML.FENUMID AND ENUML.FLOCALEID = 2052) LEFT OUTER JOIN T_META_FORMENUMITEM ENUM1 ON (ENUM1.FID = 'eca675f6-d296-4ba9-b9df-170b7b286a73' AND ENUM1.FValue = t.FVALUATION) LEFT OUTER JOIN T_META_FORMENUMITEM_L ENUML1 ON (ENUM1.FENUMID = ENUML1.FENUMID AND ENUML1.FLOCALEID = 2052) LEFT OUTER JOIN t_bd_Material MAT ON T.FMATERIALBASEID = MAT.FMATERIALID LEFT OUTER JOIN T_BD_MATERIALGROUP_L GROUPL ON (MAT.FMATERIALGROUP = GROUPL.FID AND GROUPL.FLOCALEID = 2052) WHERE 1 = 1;
UPDATE TMP08077C99FB5011EB9CBD70B5E86 SET FQtyDigits = (SELECT max(unit.FPRECISION) FROM #TM_HS_StockInSummaryData2 t LEFT OUTER JOIN T_BD_UNIT unit ON unit.funitid = t.FUnitID WHERE t.FISTOTAL = 0), FMATERIALID = (FMATERIALID + N'-总计') WHERE FISTOTAL = 3;
if OBJECT_ID(N'testdb0607..T_UG_StockInSummaryTable',N'U') IS not NULL
begin
select * into StockIn from T_UG_StockInSummaryTable
drop table T_UG_StockInSummaryTable
end
else
begin
select FMATERIALID,FMATERIALBASEID,FENDPRICE,Organ1,year1,month1 into T_UG_StockInSummaryTable from TMP08077C99FB5011EB9CBD70B5E86 WHERE FMATERIALID LIKE '%总计'
end
select * into T_UG_StockInSummaryTable from StockIn union all select FMATERIALID,FMATERIALBASEID,FENDPRICE,Organ1,year1,month1 from TMP08077C99FB5011EB9CBD70B5E86 WHERE FMATERIALID LIKE '%总计'
select * from T_UG_StockInSummaryTable
drop table TMP08077C99FB5011EB9CBD70B5E86
drop table StockIn
end
库龄报表数据准备.docx(45.17KB)
推荐阅读