674 lines
32 KiB
Transact-SQL
674 lines
32 KiB
Transact-SQL
IF OBJECT_ID('dbo.[RP_YuSuanShiJiLiRun_FenXi]', 'P') IS NOT NULL
|
||
BEGIN
|
||
DROP PROCEDURE dbo.RP_YuSuanShiJiLiRun_FenXi;
|
||
PRINT '存储过程 dbo.[RP_YuSuanShiJiLiRun_FenXi] 已删除';
|
||
END;
|
||
GO
|
||
|
||
-- 创建新的存储过程
|
||
CREATE PROCEDURE dbo.RP_YuSuanShiJiLiRun_FenXi
|
||
@FYear NVARCHAR(10), ---年度
|
||
@FStartMonth NVARCHAR(10)
|
||
|
||
AS
|
||
BEGIN
|
||
|
||
--DECLARE @FYear nvarchar(10)=2025, @FStartMonth nvarchar(10)=3
|
||
DECLARE @sql NVARCHAR(MAX),@where2 NVARCHAR(max)='',@where3 NVARCHAR(max)='',@monthN INT=1;
|
||
DECLARE @begintime DATETIME,@endtime DATETIME,@yearBeginTime DATETIME,@fid INT,@FEntryID int;
|
||
DECLARE @anmountMonth DECIMAL(18,2)=0,@anmountYear DECIMAL(18,2)=0
|
||
SELECT @begintime=
|
||
CAST(
|
||
@FYear + '-' +
|
||
RIGHT('0' + @FStartMonth, 2) + '-' +
|
||
RIGHT('0' + 1, 2)
|
||
AS DATE
|
||
),
|
||
@yearBeginTime= CAST(
|
||
@FYear + '-01-01'
|
||
AS DATE
|
||
)
|
||
|
||
SELECT @endtime=DATEADD(MONTH,1,@begintime);
|
||
|
||
|
||
SELECT b.F_XM,months=@FStartMonth,years=@FYear,[BenQiYuSuan]=CAST(0 AS DECIMAL(18,2)),F_BQSJJE=CAST(0 AS DECIMAL(18,2))
|
||
,F_BQYSSJJECY=CAST(0 AS DECIMAL(18,2)) ,F_BNYSJE=CAST(0 AS DECIMAL(18,2)) ,F_BNSJJE=CAST(0 AS DECIMAL(18,2)),F_BNYSSJCY=CAST(0 AS DECIMAL(18,2))
|
||
INTO #T_YuSuanShiJiLiRunEntity
|
||
FROM F_XMKMTitle a
|
||
LEFT JOIN F_XMKMDetail b ON a.FUNITCONVERTRATEID=b.FUNITCONVERTRATEID
|
||
WHERE a.F_YEAR3=2025
|
||
|
||
DECLARE @i INT=1,@sql2 NVARCHAR(max)='',@endMonth INT = CONVERT(INT,@FStartMonth),@benqi NVARCHAR(50)='F_MONTH'+@FStartMonth;
|
||
while(@i<=@endMonth)
|
||
BEGIN
|
||
IF(@i<@endMonth)
|
||
BEGIN
|
||
SELECT @sql2 +='F_MONTH'+CONVERT(nvarchar(10),@i)+'+';
|
||
END
|
||
ELSE
|
||
BEGIN
|
||
SELECT @sql2 +='F_MONTH'+CONVERT(NVARCHAR(10),@i);
|
||
END
|
||
|
||
SELECT @i=@i+1;
|
||
END
|
||
SELECT @sql= N'UPDATE #T_YuSuanShiJiLiRunEntity SET [BenQiYuSuan]='+@benqi+',F_BNYSJE='+@sql2+'
|
||
FROM F_XMKMTitle a
|
||
LEFT JOIN F_XMKMDetail b ON a.FUNITCONVERTRATEID=b.FUNITCONVERTRATEID
|
||
WHERE a.F_YEAR3='''+@FYear+''' AND #T_YuSuanShiJiLiRunEntity.f_xm=b.F_XM ';
|
||
PRINT @sql
|
||
EXEC sp_executesql
|
||
@sql,
|
||
N'@FYear INT',
|
||
@FYear
|
||
|
||
|
||
|
||
|
||
|
||
------销售成本数据原始表1
|
||
SELECT t1.FCustomerID,t44.FNAME AS CustomerName,t33.FNUMBER AS CustomerCode,T1.FID, T1.FBILLNO,T2.FENTRYID,t2.FMaterialID,t55.FNUMBER, T2.FREALQTY ,t4.FAMOUNT_LC,T6.FNAME , FORMAT(t1.FDATE, 'yyyy-MM-01') AS FAPPROVEDATE2
|
||
,t55.fid AS groupID
|
||
INTO #xiaoshouTable
|
||
FROM T_SAL_OUTSTOCK T1 INNER JOIN T_SAL_OUTSTOCKENTRY T2
|
||
INNER JOIN T_SAL_OUTSTOCKENTRY_C T3 ON T3.FENTRYID = T2.FENTRYID
|
||
ON T1.FID = T2.FID
|
||
INNER JOIN T_SAL_OUTSTOCKENTRY_CE T4 ON T3.FENTRYID = T4.FENTRYID
|
||
LEFT JOIN T_HS_CALDIMENSIONS T5 ON T3.FDIMENSIONID = T5.FDIMENSIONID
|
||
LEFT JOIN T_BD_EXPENSE_L T6 ON T4.FEXPENSEID = T6.FEXPID AND T6.FLOCALEID = 2052
|
||
LEFT JOIN T_BD_MATERIAL ff ON t2.FMaterialID=ff.FMaterialID
|
||
INNER JOIN VHUB_T_BMMGroupLinkTable cc ON ff.FMATERIALGROUP = cc.[FMATERIALGROUP]
|
||
INNER JOIN VHUB_BD_BMMaterialGroup t55 ON cc.[FBMMATERIALGROUP]= t55.FID
|
||
LEFT JOIN T_BD_CUSTOMER t33 ON T1.FCUSTOMERID= t33.FCUSTID
|
||
LEFT JOIN T_BD_CUSTOMER_L t44 ON t33.FCUSTID = t44.FCUSTID
|
||
WHERE T1.FDATE>=@yearBeginTime AND t1.FDATE<@endtime AND t1.FDOCUMENTSTATUS='C'
|
||
|
||
|
||
|
||
|
||
-- 销售出库成本金额查询SQL
|
||
SELECT a.FCustomerID,a. CustomerName,a.FNUMBER,a.FNAME,a.FID
|
||
,SUM(a.FAMOUNT_LC) AS FAMOUNT_LC,FAPPROVEDATE2 , CustomerCode,groupID
|
||
INTO #XiaoShouChengBenJinE
|
||
FROM #xiaoshouTable a
|
||
GROUP BY a.FCustomerID,a.FNUMBER,a.FNAME,FAPPROVEDATE2 ,CustomerName,CustomerCode,groupID ,a.FID
|
||
ORDER BY a.FCustomerID, FNUMBER,FAPPROVEDATE2;
|
||
|
||
|
||
|
||
-----------退货成本数据原始表1
|
||
SELECT t1.FRETCUSTID,t44.FNAME AS CustomerName,t33.FNUMBER AS CustomerCode, T1.FID, T1.FBILLNO,T2.FENTRYID,t55.FNUMBER, FREALQTY
|
||
,t4.FAMOUNT_LC,T6.FNAME , FORMAT(t1.FDATE, 'yyyy-MM-01') AS FAPPROVEDATE2,t1.FDATE
|
||
,t55.fid AS groupID,t2.FMaterialID
|
||
INTO #tuihuoTable
|
||
FROM T_SAL_RETURNSTOCK T1 INNER JOIN T_SAL_RETURNSTOCKENTRY T2
|
||
INNER JOIN T_SAL_RETURNSTOCKENTRY_C T3 ON T3.FENTRYID = T2.FENTRYID
|
||
ON T1.FID = T2.FID
|
||
INNER JOIN T_SAL_RETURNSTOCKENTRY_CE T4 ON T3.FENTRYID = T4.FENTRYID
|
||
LEFT JOIN T_HS_CALDIMENSIONS T5 ON T3.FDIMENSIONID = T5.FDIMENSIONID
|
||
LEFT JOIN T_BD_EXPENSE_L T6 ON T4.FEXPENSEID = T6.FEXPID AND T6.FLOCALEID = 2052
|
||
LEFT JOIN T_BD_MATERIAL ff ON t2.FMaterialID=ff.FMaterialID
|
||
INNER JOIN VHUB_T_BMMGroupLinkTable cc ON ff.FMATERIALGROUP = cc.[FMATERIALGROUP]
|
||
INNER JOIN VHUB_BD_BMMaterialGroup t55 ON cc.[FBMMATERIALGROUP]= t55.FID
|
||
LEFT JOIN T_BD_CUSTOMER t33 ON T1.FRETCUSTID= t33.FCUSTID
|
||
LEFT JOIN T_BD_CUSTOMER_L t44 ON t33.FCUSTID = t44.FCUSTID
|
||
WHERE T1.FDATE>=@yearBeginTime AND t1.FDATE<@endtime AND t1.FDOCUMENTSTATUS='C'
|
||
|
||
|
||
-- 退货成本金额查询SQL
|
||
SELECT a.FRETCUSTID,CustomerName,a.FNUMBER,a.FNAME,a.FID
|
||
,SUM(a.FAMOUNT_LC) AS FAMOUNT_LC,FAPPROVEDATE2,CustomerCode,groupID
|
||
INTO #TuiHuoChengBenJinE
|
||
FROM #tuihuoTable a
|
||
GROUP BY a.FRETCUSTID,a.FNUMBER,a.FNAME,FAPPROVEDATE2 ,CustomerName,CustomerCode,groupID,a.FID
|
||
ORDER BY a.FRETCUSTID,FNUMBER,FAPPROVEDATE2,a.FID
|
||
|
||
--------------------------------------------旧版销售成本------------------------------------------------------------------------------
|
||
|
||
-- -- 退货成本查询SQL2
|
||
--SELECT a.FRETCUSTID,a.FMaterialID,a.FNUMBER,a.FNAME,SUM(FREALQTY) AS REALQTY,SUM(a.FAMOUNT_LC) AS FAMOUNT_LC,FAPPROVEDATE2 INTO #TuiHuoChengBen FROM (
|
||
--SELECT t1.FRETCUSTID, T1.FID, T1.FBILLNO,T2.FENTRYID,t2.FMaterialID,ff.FNUMBER, FREALQTY ,t4.FAMOUNT_LC,T6.FNAME , FORMAT(t1.FAPPROVEDATE, 'yyyy-MM-01') AS FAPPROVEDATE2
|
||
--FROM T_SAL_RETURNSTOCK T1 INNER JOIN T_SAL_RETURNSTOCKENTRY T2
|
||
--INNER JOIN T_SAL_RETURNSTOCKENTRY_C T3 ON T3.FENTRYID = T2.FENTRYID
|
||
--ON T1.FID = T2.FID
|
||
-- INNER JOIN T_SAL_RETURNSTOCKENTRY_CE T4 ON T3.FENTRYID = T4.FENTRYID
|
||
-- LEFT JOIN T_HS_CALDIMENSIONS T5 ON T3.FDIMENSIONID = T5.FDIMENSIONID
|
||
-- LEFT JOIN T_BD_EXPENSE_L T6 ON T4.FEXPENSEID = T6.FEXPID AND T6.FLOCALEID = 2052
|
||
-- LEFT JOIN T_BD_MATERIAL ff ON t2.FMaterialID=ff.FMaterialID
|
||
-- WHERE T1.FAPPROVEDATE>=@yearBeginTime AND t1.FAPPROVEDATE<@endtime
|
||
-- ) a
|
||
--GROUP BY a.FRETCUSTID,a.FMaterialID,a.FNUMBER,a.FNAME,FAPPROVEDATE2
|
||
--ORDER BY a.FRETCUSTID,FMaterialID,FAPPROVEDATE2
|
||
|
||
----SELECT * FROM #TuiHuoChengBen
|
||
|
||
----SELECT * , DATEFROMPARTS((bb.Year), (bb.Month), 1) AS FAPPROVEDATE INTO #TempNianDuXiaoShouYuSuan FROM NianDuXiaoShouYuSuan bb
|
||
----WHERE bb.Year=2025
|
||
|
||
----SELECT * FROM #TempNianDuXiaoShouYuSuan
|
||
|
||
|
||
-- -- 销售出库成本查询SQL
|
||
-- SELECT a.FCustomerID,a.FMaterialID,a.FNUMBER,a.FNAME,SUM(FREALQTY) AS RealQty,SUM(a.FAMOUNT_LC) AS FAMOUNT_LC,FAPPROVEDATE2 INTO #XiaoShouChengBen FROM (
|
||
--SELECT t1.FCustomerID,T1.FID, T1.FBILLNO,T2.FENTRYID,t2.FMaterialID,ff.FNUMBER, T2.FREALQTY ,t4.FAMOUNT_LC,T6.FNAME , FORMAT(t1.FAPPROVEDATE, 'yyyy-MM-01') AS FAPPROVEDATE2
|
||
--FROM T_SAL_OUTSTOCK T1 INNER JOIN T_SAL_OUTSTOCKENTRY T2
|
||
--INNER JOIN T_SAL_OUTSTOCKENTRY_C T3 ON T3.FENTRYID = T2.FENTRYID
|
||
--ON T1.FID = T2.FID
|
||
-- INNER JOIN T_SAL_OUTSTOCKENTRY_CE T4 ON T3.FENTRYID = T4.FENTRYID
|
||
-- LEFT JOIN T_HS_CALDIMENSIONS T5 ON T3.FDIMENSIONID = T5.FDIMENSIONID
|
||
-- LEFT JOIN T_BD_EXPENSE_L T6 ON T4.FEXPENSEID = T6.FEXPID AND T6.FLOCALEID = 2052
|
||
-- LEFT JOIN T_BD_MATERIAL ff ON t2.FMaterialID=ff.FMaterialID
|
||
-- WHERE T1.FAPPROVEDATE>=@yearBeginTime AND t1.FAPPROVEDATE<@endtime) a
|
||
--GROUP BY a.FCustomerID,a.FMaterialID,a.FNUMBER,a.FNAME,FAPPROVEDATE2
|
||
|
||
--SELECT * FROM #XiaoShouChengBen
|
||
------------------------------------------------------------------------------------------
|
||
|
||
DECLARE cursor_name CURSOR FOR
|
||
SELECT F_XM,months,years
|
||
FROM #T_YuSuanShiJiLiRunEntity
|
||
-- 2. 打开游标
|
||
OPEN cursor_name;
|
||
DECLARE @rowId INT = 0,@F_XM NVARCHAR(500),@months INT,@years INT ,
|
||
@amount DECIMAL(18, 4) = 0;
|
||
-- 3. 循环获取数据
|
||
|
||
FETCH NEXT FROM cursor_name
|
||
INTO @F_XM ,
|
||
@months ,
|
||
@years
|
||
|
||
WHILE @@FETCH_STATUS = 0
|
||
BEGIN
|
||
IF(@F_XM='营业收入')
|
||
BEGIN
|
||
--营业收入：取收款单上金额实际金额取数一致
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=(SELECT SUM(FALLAMOUNT) FROM T_AR_RECEIVABLE bb
|
||
left join T_AR_RECEIVABLEFIN cc on bb.fid=cc.fid
|
||
WHERE bb.FDATE>=@begintime AND bb.FDATE < @endtime AND bb.FDOCUMENTSTATUS='C' )
|
||
,F_BNSJJE=(SELECT SUM(FALLAMOUNT) FROM T_AR_RECEIVABLE bb
|
||
left join T_AR_RECEIVABLEFIN cc on bb.fid=cc.fid
|
||
WHERE bb.FDATE>=@yearBeginTime AND bb.FDATE < @endtime AND bb.FDOCUMENTSTATUS='C' )
|
||
WHERE F_XM=@F_XM
|
||
end
|
||
IF(@F_XM='营业成本')
|
||
BEGIN
|
||
-- 营业成本：存货核算料工费加总
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=ISNULL((SELECT SUM(FAMOUNT_LC) FROM #XiaoShouChengBenJinE bb WHERE bb.FAPPROVEDATE2>=@begintime AND bb.FAPPROVEDATE2 < @endtime ),0)
|
||
-ISNULL((SELECT SUM(FAMOUNT_LC) FROM #TuiHuoChengBenJinE bb WHERE bb.FAPPROVEDATE2>=@begintime AND bb.FAPPROVEDATE2 < @endtime ),0)
|
||
,F_BNSJJE=ISNULL((SELECT SUM(FAMOUNT_LC) FROM #XiaoShouChengBenJinE bb WHERE 1=1 ),0) -
|
||
ISNULL((SELECT SUM(FAMOUNT_LC) FROM #TuiHuoChengBenJinE bb WHERE 1=1 ),0)
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM='直接材料')
|
||
BEGIN
|
||
--直接材料：与成本实际金额-料取数一致
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=ISNULL((SELECT SUM(FAMOUNT_LC) FROM #XiaoShouChengBenJinE bb WHERE bb.FAPPROVEDATE2>=@begintime AND bb.FAPPROVEDATE2 < @endtime AND bb.FNAME='材料成本' ),0)
|
||
-ISNULL((SELECT SUM(FAMOUNT_LC) FROM #TuiHuoChengBenJinE bb WHERE bb.FAPPROVEDATE2>=@begintime AND bb.FAPPROVEDATE2 < @endtime AND bb.FNAME IN ('材料成本','委外加工费') ),0)
|
||
,F_BNSJJE=ISNULL((SELECT SUM(FAMOUNT_LC) FROM #XiaoShouChengBenJinE bb WHERE bb.FNAME IN ('材料成本','委外加工费') ),0) -
|
||
ISNULL((SELECT SUM(FAMOUNT_LC) FROM #TuiHuoChengBenJinE bb WHERE bb.FNAME IN ('材料成本','委外加工费') ),0)
|
||
WHERE F_XM=@F_XM
|
||
end
|
||
|
||
IF(@F_XM='直接人工')
|
||
BEGIN
|
||
-- 直接人工：与成本实际金额-工取数一致
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=ISNULL((SELECT SUM(FAMOUNT_LC) FROM #XiaoShouChengBenJinE bb WHERE bb.FAPPROVEDATE2>=@begintime AND bb.FAPPROVEDATE2 < @endtime AND bb.FNAME IN ('工资','委外加工费') ),0)
|
||
-ISNULL((SELECT SUM(FAMOUNT_LC) FROM #TuiHuoChengBenJinE bb WHERE bb.FAPPROVEDATE2>=@begintime AND bb.FAPPROVEDATE2 < @endtime AND bb.FNAME IN ('工资','委外加工费') ),0)
|
||
,F_BNSJJE=ISNULL((SELECT SUM(FAMOUNT_LC) FROM #XiaoShouChengBenJinE bb WHERE bb.FNAME IN ('工资' ) ),0) -
|
||
ISNULL((SELECT SUM(FAMOUNT_LC) FROM #TuiHuoChengBenJinE bb WHERE bb.FNAME IN ('工资') ),0)
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM='制造费用')
|
||
BEGIN
|
||
-- 制造费用：与成本实际金额-费取数一致
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=ISNULL((SELECT SUM(FAMOUNT_LC) FROM #XiaoShouChengBenJinE bb WHERE bb.FAPPROVEDATE2>=@begintime AND bb.FAPPROVEDATE2 < @endtime AND bb.FNAME='制造费用' ),0)
|
||
-ISNULL((SELECT SUM(FAMOUNT_LC) FROM #TuiHuoChengBenJinE bb WHERE bb.FAPPROVEDATE2>=@begintime AND bb.FAPPROVEDATE2 < @endtime AND bb.FNAME='制造费用' ),0)
|
||
,F_BNSJJE=ISNULL((SELECT SUM(FAMOUNT_LC) FROM #XiaoShouChengBenJinE bb WHERE bb.FNAME='制造费用' ),0) -
|
||
ISNULL((SELECT SUM(FAMOUNT_LC) FROM #TuiHuoChengBenJinE bb WHERE bb.FNAME='制造费用' ),0)
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
|
||
IF(@F_XM='工资')
|
||
BEGIN
|
||
-- 工资：工资本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
--UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=bbb.F_BQSJJE-#T_YuSuanShiJiLiRunEntity.F_BQSJJE,F_BNSJJE=bbb.F_BNSJJE-#T_YuSuanShiJiLiRunEntity.F_BNSJJE
|
||
--FROM (SELECT F_BQSJJE ,F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='制造费用') bbb
|
||
--WHERE F_XM=@F_XM
|
||
|
||
SELECT @anmountMonth=@anmountMonth+F_BQSJJE ,@anmountYear=@anmountYear+F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity WHERE F_XM=@F_XM
|
||
end
|
||
|
||
IF(@F_XM='职工福利费')
|
||
BEGIN
|
||
-- 职工福利费：职工福利费本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
--UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=bbb.F_BQSJJE-#T_YuSuanShiJiLiRunEntity.F_BQSJJE,F_BNSJJE=bbb.F_BNSJJE-#T_YuSuanShiJiLiRunEntity.F_BNSJJE
|
||
--FROM (SELECT F_BQSJJE ,F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='制造费用') bbb
|
||
--WHERE F_XM=@F_XM
|
||
|
||
SELECT @anmountMonth=@anmountMonth+F_BQSJJE ,@anmountYear=@anmountYear+F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM LIKE '%社保%' AND @F_XM LIKE '%公积金%' )
|
||
BEGIN
|
||
-- 社保、公积金：制造费用-住房公积金/养老保险/医疗保险/失业保险/生育医疗/工伤保险这六个科目本期借方发生额合计
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
--UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=bbb.F_BQSJJE-#T_YuSuanShiJiLiRunEntity.F_BQSJJE,F_BNSJJE=bbb.F_BNSJJE-#T_YuSuanShiJiLiRunEntity.F_BNSJJE
|
||
--FROM (SELECT F_BQSJJE ,F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='制造费用') bbb
|
||
--WHERE F_XM=@F_XM
|
||
|
||
SELECT @anmountMonth=@anmountMonth+F_BQSJJE ,@anmountYear=@anmountYear+F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM ='房租' )
|
||
BEGIN
|
||
-- 制造费用-房租本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
--UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=bbb.F_BQSJJE-#T_YuSuanShiJiLiRunEntity.F_BQSJJE,F_BNSJJE=bbb.F_BNSJJE-#T_YuSuanShiJiLiRunEntity.F_BNSJJE
|
||
--FROM (SELECT F_BQSJJE ,F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='制造费用') bbb
|
||
--WHERE F_XM=@F_XM
|
||
|
||
SELECT @anmountMonth=@anmountMonth+F_BQSJJE ,@anmountYear=@anmountYear+F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM ='水电费' )
|
||
BEGIN
|
||
-- 水电费：制造费用-水电费本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
--UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=bbb.F_BQSJJE-#T_YuSuanShiJiLiRunEntity.F_BQSJJE,F_BNSJJE=bbb.F_BNSJJE-#T_YuSuanShiJiLiRunEntity.F_BNSJJE
|
||
--FROM (SELECT F_BQSJJE ,F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='制造费用') bbb
|
||
--WHERE F_XM=@F_XM
|
||
|
||
SELECT @anmountMonth=@anmountMonth+F_BQSJJE ,@anmountYear=@anmountYear+F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM ='物料消耗' )
|
||
BEGIN
|
||
-- 物料消耗：制造费用-物料消耗本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
--UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=bbb.F_BQSJJE-#T_YuSuanShiJiLiRunEntity.F_BQSJJE,F_BNSJJE=bbb.F_BNSJJE-#T_YuSuanShiJiLiRunEntity.F_BNSJJE
|
||
--FROM (SELECT F_BQSJJE ,F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='制造费用') bbb
|
||
--WHERE F_XM=@F_XM
|
||
|
||
SELECT @anmountMonth=@anmountMonth+F_BQSJJE ,@anmountYear=@anmountYear+F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM LIKE '%摊销%' AND @F_XM LIKE '%折旧%' )
|
||
BEGIN
|
||
--摊销、折旧：制造费用-长期待摊费用摊销/无形资产摊销/折旧费这三个科目本期借方发生额合计
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
--UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=bbb.F_BQSJJE-#T_YuSuanShiJiLiRunEntity.F_BQSJJE,F_BNSJJE=bbb.F_BNSJJE-#T_YuSuanShiJiLiRunEntity.F_BNSJJE
|
||
--FROM (SELECT F_BQSJJE ,F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='制造费用') bbb
|
||
--WHERE F_XM=@F_XM
|
||
|
||
SELECT @anmountMonth=@anmountMonth+F_BQSJJE ,@anmountYear=@anmountYear+F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM ='其他' )
|
||
BEGIN
|
||
--其他：总制造费用减去上述费用之后差额
|
||
-- UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=@anmountMonth
|
||
--,F_BNSJJE=@anmountYear
|
||
--WHERE F_XM=@F_XM
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=bbb.F_BQSJJE-@anmountMonth,F_BNSJJE=bbb.F_BNSJJE-@anmountYear
|
||
FROM (SELECT F_BQSJJE ,F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='制造费用') bbb
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM LIKE '%毛利%' AND @F_XM not LIKE '%毛利率%' )
|
||
BEGIN
|
||
--毛利：营业收入减去营业成本
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='营业收入')-(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='营业成本')
|
||
,F_BNSJJE=(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='营业收入')-(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='营业成本')
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM LIKE '%毛利率%' )
|
||
BEGIN
|
||
|
||
--毛利率：毛利/营业收入，百分比后2位小数，四舍五入。
|
||
DECLARE @maoli DECIMAL(18,2),@shouRu DECIMAL(18,2),@maoliY DECIMAL(18,2),@shouRuY DECIMAL(18,2),@lvM DECIMAL(18,4)=0,@lvY DECIMAL(18,4)=0;
|
||
SELECT @maoli=(SELECT bb.BenQiYuSuan FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%毛利%' AND bb.F_XM not LIKE '%毛利率%' ),@maoliY=(SELECT bb.F_BNYSJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%毛利%' AND bb.F_XM not LIKE '%毛利率%' )
|
||
,@shouRu=(SELECT BenQiYuSuan FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='营业收入')
|
||
,@shouRuY=(SELECT F_BNYSJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='营业收入')
|
||
|
||
PRINT '收入'+CONVERT(NVARCHAR(200),@shouRuY)+'毛利:'+CONVERT(NVARCHAR(200),@maoliY)
|
||
|
||
IF(@shouRu<>0)
|
||
begin
|
||
SET @lvM=@maoli/@shouRu
|
||
END
|
||
|
||
IF(@shouRuY<>0)
|
||
begin
|
||
SET @lvY=@maoliY/@shouRuY
|
||
END
|
||
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET BenQiYuSuan=@lvM,F_BNYSJE=@lvY
|
||
WHERE F_XM=@F_XM
|
||
|
||
|
||
--毛利率：毛利/营业收入，百分比后2位小数，四舍五入。
|
||
|
||
SELECT @maoli=(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%毛利%' AND bb.F_XM not LIKE '%毛利率%' ),@maoliY=(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%毛利%' AND bb.F_XM not LIKE '%毛利率%' )
|
||
,@shouRu=(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='营业收入')
|
||
,@shouRuY=(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='营业收入')
|
||
|
||
PRINT '收入'+CONVERT(NVARCHAR(200),@shouRuY)+'毛利:'+CONVERT(NVARCHAR(200),@maoliY)
|
||
|
||
IF(@shouRu<>0)
|
||
begin
|
||
SET @lvM=@maoli/@shouRu
|
||
END
|
||
|
||
IF(@shouRuY<>0)
|
||
begin
|
||
SET @lvY=@maoliY/@shouRuY
|
||
END
|
||
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=@lvM,F_BNSJJE=@lvY
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
|
||
IF(@F_XM ='税金及附加' )
|
||
BEGIN
|
||
--税金及附加：营业税金及附加本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM ='销售费用' )
|
||
BEGIN
|
||
--销售费用：销售费用本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM ='管理费用' )
|
||
BEGIN
|
||
--管理费用：管理费用本期借方发生额总合计减去(管理费用-研究开发费)本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=F_BQSJJE-( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM='研发费用'))
|
||
,F_BNSJJE=F_BNSJJE-( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM='研发费用'))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM ='研发费用' )
|
||
BEGIN
|
||
--管理费用：管理费用本期借方发生额总合计减去(管理费用-研究开发费)本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
|
||
|
||
IF(@F_XM LIKE '%经营性利润%' )
|
||
BEGIN
|
||
--经营性利润：毛利-税金及附加-销售费用-管理费用-研发费用
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%毛利%' AND bb.F_XM not LIKE '%毛利率%' )-(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='税金及附加')
|
||
-(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='销售费用') -(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='管理费用') -(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='研发费用')
|
||
,F_BNSJJE=(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%毛利%' AND bb.F_XM not LIKE '%毛利率%' )-(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='税金及附加')
|
||
-(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='销售费用') -(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='管理费用') -(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='研发费用')
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
|
||
IF(@F_XM ='财务费用' )
|
||
BEGIN
|
||
--财务费用：财务费用本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
|
||
IF(@F_XM ='其他(补贴)' )
|
||
BEGIN
|
||
--其他(补贴)：其他收益本期贷方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FCredit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdCredit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM LIKE '%营业外收入%' )
|
||
BEGIN
|
||
--加：营业外收入：营业外收入本期贷方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FCredit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdCredit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM LIKE '%营业外支出%' )
|
||
BEGIN
|
||
--减：营业外支出：营业外支出本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
|
||
IF(@F_XM LIKE '%利润总额%' )
|
||
BEGIN
|
||
---利润总额（亏损总额以"-"号填列）：经营性利润-财务费用+其他（补贴）+营业外收入-营业外支出
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%经营性利润%' )-(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='财务费用')
|
||
+(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='其他(补贴)') +(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%营业外收入%')
|
||
-(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%营业外支出%')
|
||
,F_BNSJJE=(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%经营性利润%' )-(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='财务费用')
|
||
+(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM='其他(补贴)') +(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%营业外收入%')
|
||
-(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%营业外支出%')
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM LIKE '%所得税费用%' )
|
||
BEGIN
|
||
--减：所得税费用：所得税费用本期借方发生额
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=( SELECT SUM(FDebit) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
,F_BNSJJE=( SELECT SUM(FYtdDebitFor) AS FDebit FROM T_GL_BALANCE
|
||
WHERE FYear=@years AND FPeriod=@FStartMonth AND FDETAILID=0 AND FCURRENCYID=1
|
||
AND FAccountID IN (SELECT b.F_KEMU2 FROM F_XMKMDetail a
|
||
LEFT JOIN F_KeMu2 b ON a.FEntryID=B.FEntryID
|
||
WHERE a.F_XM=@F_XM))
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
IF(@F_XM LIKE '%净利润%' )
|
||
BEGIN
|
||
---四、净利润（净亏损以"-"号填列）利润总额-所得税费用
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQSJJE=(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%利润总额%' )
|
||
-(SELECT F_BQSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%所得税费用%')
|
||
,F_BNSJJE=(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%利润总额%' )
|
||
-(SELECT F_BNSJJE FROM #T_YuSuanShiJiLiRunEntity bb WHERE bb.F_XM LIKE '%所得税费用%')
|
||
WHERE F_XM=@F_XM
|
||
END
|
||
|
||
|
||
|
||
|
||
-- 获取下一条记录
|
||
FETCH NEXT FROM cursor_name
|
||
INTO @F_XM ,
|
||
@months ,
|
||
@years
|
||
|
||
END;
|
||
|
||
-- 4. 关闭游标
|
||
CLOSE cursor_name;
|
||
|
||
-- 5. 释放游标资源
|
||
DEALLOCATE cursor_name;
|
||
|
||
UPDATE #T_YuSuanShiJiLiRunEntity SET F_BQYSSJJECY=BenQiYuSuan-F_BQSJJE,F_BNYSSJCY=F_BNYSJE-F_BNSJJE
|
||
|
||
SELECT * FROM #T_YuSuanShiJiLiRunEntity
|
||
|
||
SELECT @fid=fid FROM T_YuSuanShiJiLiRun_FenXi
|
||
WHERE F_Date=@begintime
|
||
|
||
|
||
|
||
|
||
DROP TABLE #T_YuSuanShiJiLiRunEntity
|
||
-- DROP TABLE #TuiHuoChengBen
|
||
--DROP TABLE #XiaoShouChengBen
|
||
end
|
||
|