部门费用支出表存储过程sql
PROCEDURE [dbo].[ABF_Cust_DeptExpenseDetailInfo] @Org NVARCHAR(100), --组织 @SOB NVARCHAR(100), --账簿 @Period NVARCHAR(100), --期间 @Dept NVARCHAR(1000), --部门 @Customer NVARCHAR(1000), --客户 @Seller NVARCHAR(1000), --业务员 @NatureAccount NVARCHAR(100), --标准科目 @NatureAccountName NVARCHAR(100), --标准科目 @Abstract NVARCHAR(100), --摘要 @Status BIT, --记账 @FeeType INT --费用类型 AS BEGIN IF OBJECT_ID(N'tempdb.dbo.#Cust_ExpenseInfoTable_Temp') IS NOT NULL BEGIN TRUNCATE TABLE #Cust_ExpenseInfoTable_Temp DROP TABLE #Cust_ExpenseInfoTable_Temp END CREATE TABLE #Cust_ExpenseInfoTable_Temp ( ID INT IDENTITY(1,1) PRIMARY KEY, PeriodString NVARCHAR(100), ProjectName NVARCHAR(20) NOT NULL, AdminCode NVARCHAR(20) NOT NULL, --管理 SaleCode NVARCHAR(20) NOT NULL, --销售 ManuCode NVARCHAR(20) NOT NULL, --制造 Expense01 DECIMAL(16,2) NOT NULL DEFAULT 0, --总经办 Expense02 DECIMAL(16,2) NOT NULL DEFAULT 0, --销售中心 Expense0201 DECIMAL(16,2) NOT NULL DEFAULT 0, --销售部 Expense0202 DECIMAL(16,2) NOT NULL DEFAULT 0, --市场运营部 Expense0203 DECIMAL(16,2) NOT NULL DEFAULT 0, --跟单组 Expense04 DECIMAL(16,2) NOT NULL DEFAULT 0, --管理中心 Expense0401 DECIMAL(16,2) NOT NULL DEFAULT 0, --财务部 Expense0402 DECIMAL(16,2) NOT NULL DEFAULT 0, --IT部 Expense0403 DECIMAL(16,2) NOT NULL DEFAULT 0, --考勤组 Expense0404 DECIMAL(16,2) NOT NULL DEFAULT 0, --船务专员 Expense0405 DECIMAL(16,2) NOT NULL DEFAULT 0, --人力资源部 Expense0499 DECIMAL(16,2) NOT NULL DEFAULT 0, --中心公共组 Expense06 DECIMAL(16,2) NOT NULL DEFAULT 0, --研发中心 Expense0601 DECIMAL(16,2) NOT NULL DEFAULT 0, --研发部 Expense0602 DECIMAL(16,2) NOT NULL DEFAULT 0, --产品部 Expense07 DECIMAL(16,2) NOT NULL DEFAULT 0, --采购部 Expense08 DECIMAL(16,2) NOT NULL DEFAULT 0, --品质稽查组 Expense09 DECIMAL(16,2) NOT NULL DEFAULT 0, --制造中心 Expense0901 DECIMAL(16,2) NOT NULL DEFAULT 0, --工程部 Expense0902 DECIMAL(16,2) NOT NULL DEFAULT 0, --品质部 Expense0903 DECIMAL(16,2) NOT NULL DEFAULT 0, --生产部 Expense090301 DECIMAL(16,2) NOT NULL DEFAULT 0, --雾化车间 Expense090302 DECIMAL(16,2) NOT NULL DEFAULT 0, --注油车间 Expense090303 DECIMAL(16,2) NOT NULL DEFAULT 0, --组装车间 Expense090304 DECIMAL(16,2) NOT NULL DEFAULT 0, --包装车间 Expense090399 DECIMAL(16,2) NOT NULL DEFAULT 0, --生产公共组 Expense0904 DECIMAL(16,2) NOT NULL DEFAULT 0, --物控部 Expense090401 DECIMAL(16,2) NOT NULL DEFAULT 0, --PC组 Expense090402 DECIMAL(16,2) NOT NULL DEFAULT 0, --MC组 Expense090403 DECIMAL(16,2) NOT NULL DEFAULT 0, --仓库部 Expense090499 DECIMAL(16,2) NOT NULL DEFAULT 0 --物控公共组 ) IF @FeeType IS NULL OR @FeeType=1 BEGIN INSERT INTO #Cust_ExpenseInfoTable_Temp ( AdminCode, SaleCode, ManuCode, ProjectName ) SELECT '660201','660101','510101','工资' UNION SELECT '660202','660102','510102','奖金' UNION SELECT '660203','660103','510103','福利费' UNION SELECT '660204','660104','510104','社会保险费' UNION SELECT '660205','660105','510105','住房公积金' UNION SELECT '660206','660106','510106','折旧费' UNION SELECT '660207','660107','510107','商业保险费' UNION SELECT '660208','660108','510108','租赁费' UNION SELECT '660209','660109','510109','办公费' UNION SELECT '660210','660110','510110','差旅费' UNION SELECT '660211','660111','510111','通讯费' UNION SELECT '660212','660112','510112','业务招待费' UNION SELECT '660213','660113','510113','物料消耗' UNION SELECT '660214','660114','510114','检验检测费' UNION SELECT '660215','660115','510115','厂房租金' UNION SELECT '660216','660116','510116','物业费' UNION SELECT '660217','660117','510117','水费' UNION SELECT '660218','660118','510118','电费' UNION SELECT '660219','660119','510119','维修费' UNION SELECT '660220','660120','510120','运输费用' UNION SELECT '660221','660121','510121','公务用车费' UNION SELECT '660222','660122','510122','劳务费' UNION SELECT '660223','660123','510123','招聘费' UNION SELECT '660224','660124','510124','伙食费用' UNION SELECT '660225','660125','510125','咨询服务费' UNION SELECT '660226','660126','510126','广告费' UNION SELECT '660227','660127','510127','业务宣传费' UNION SELECT '660228','660128','510128','职工教育经费' UNION SELECT '660229','660129','510129','工会经费' UNION SELECT '660230','660130','510130','费用摊销' UNION SELECT '660231','660131','510131','保安费' UNION SELECT '660232','660132','510132','诉讼费' UNION SELECT '660233','660133','510133','商标注册费' UNION SELECT '660234','660134','510134','通关费' UNION SELECT '660235','660135','510135','业务佣金' UNION SELECT '660236','660136','510136','样品费' UNION SELECT '660237','660137','510137','礼品费' UNION SELECT '660238','660138','510138','劳保费' UNION SELECT '660239','660139','510139','税费' UNION SELECT '660240','660140','510140','认证费' UNION SELECT '660241','660141','510141','残疾保证金' UNION SELECT '660242','660142','510142','业务折扣费' UNION SELECT '660243','660143','510143','低值消耗品' UNION SELECT '660244','660144','510144','环保费' UNION SELECT '660299','660199','510199','其他' END IF @FeeType=0 BEGIN INSERT INTO #Cust_ExpenseInfoTable_Temp ( AdminCode, SaleCode, ManuCode, ProjectName ) SELECT '','','530101','人员工资' UNION SELECT '','','53010101','职工工资' UNION SELECT '','','53010102','奖金与提成' UNION SELECT '','','53010103','社会保险费' UNION SELECT '','','53010104','公积金' UNION SELECT '','','53010105','福利费' UNION SELECT '','','530102','直接投入' UNION SELECT '','','53010201','材料费' UNION SELECT '','','53010202','水电费' UNION SELECT '','','53010203','房屋物业费' UNION SELECT '','','53010204','样品费' UNION SELECT '','','53010205','模具费' UNION SELECT '','','53010206','工装费' UNION SELECT '','','53010207','检验费' UNION SELECT '','','53010208','租赁费' UNION SELECT '','','53010209','认证费' UNION SELECT '','','530103','折旧费' UNION SELECT '','','530104','费用摊销' UNION SELECT '','','53010401','软件费用摊销' UNION SELECT '','','53010402','专利费用摊销' UNION SELECT '','','53010403','许可费用摊销' UNION SELECT '','','53010404','技术转让摊销' UNION SELECT '','','530105','咨询服务费' UNION SELECT '','','53010501','产品设计费' UNION SELECT '','','53010502','工程设计费' UNION SELECT '','','53010503','产品专利费' UNION SELECT '','','530106','委托设计费' UNION SELECT '','','530107','其他费用' UNION SELECT '','','53010701','差旅费' UNION SELECT '','','53010702','招待费' UNION SELECT '','','53010703','快递费' UNION SELECT '','','53010704','办公费' UNION SELECT '','','53010705','交通费' UNION SELECT '','','53010706','维修费' UNION SELECT '','','53010707','手板费' UNION SELECT '','','53010799','其他' END IF OBJECT_ID(N'tempdb.dbo.#Cust_ExpenseDataTable_Temp') IS NOT NULL BEGIN TRUNCATE TABLE #Cust_ExpenseDataTable_Temp DROP TABLE #Cust_ExpenseDataTable_Temp END CREATE TABLE #Cust_ExpenseDataTable_Temp ( ID INT IDENTITY(1,1) PRIMARY KEY, AccountCode NVARCHAR(20) NOT NULL, AccountAmount DECIMAL(16,2) NOT NULL DEFAULT 0, TotalAmount DECIMAL(16,2) NOT NULL DEFAULT 0, DeptCode NVARCHAR(20) NOT NULL ) ---------------------------------------------------------------------------------------- DECLARE @SqlSub NVARCHAR(MAX) SET @SqlSub = 'INSERT INTO #Cust_ExpenseDataTable_Temp (AccountCode,DeptCode,AccountAmount) SELECT E.Code, CASE WHEN E.Code=''660244'' AND C.Segment4=''0'' THEN ''0405'' ELSE C.Segment4 END Segment4 ,SUM(A.AccountedDr) Amount FROM dbo.GL_Entry AS A LEFT JOIN dbo.GL_Entry_Trl AS A1 ON A1.ID=A.ID AND A1.SysMLFlag=''zh-CN'' LEFT JOIN dbo.GL_Voucher AS B ON B.ID=A.Voucher LEFT JOIN dbo.CBO_Account AS C ON C.ID=A.Account LEFT JOIN dbo.CBO_NaturalAccountSOBProperty AS D ON D.ID=C.SOBNaturalAccount LEFT JOIN dbo.CBO_NaturalAccount AS E ON E.ID=D.NaturalAccount LEFT JOIN dbo.CBO_NaturalAccount_Trl AS E1 ON E1.ID=E.ID AND E1.SysMLFlag=''zh-CN'' LEFT JOIN dbo.Base_SOBAccountingPeriod AS F ON F.ID=B.PostedPeriod WHERE 1=1 ' DECLARE @PeriodString NVARCHAR(100) = '期间：' IF @Period IS NOT NULL AND LEN(@Period)>1 BEGIN IF(CHARINDEX('or',@Period)>0) BEGIN SET @SqlSub+=' AND ' + REPLACE(SUBSTRING(@Period,3,CHARINDEX('or',@Period)-3),'Period', ' F.DisplayName ') SET @PeriodString+=REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING(@Period,3,CHARINDEX('or',@Period)-3),'Period', ''),'between',''),'and',' 至 '),'''','') END ELSE BEGIN SET @SqlSub+= REPLACE(SUBSTRING(@Period,3,LEN(@Period)-3),'Period',' AND F.DisplayName ') SET @PeriodString+=REPLACE(REPLACE(SUBSTRING(@Period,3,LEN(@Period)-3),'Period = N',''),'''','') END END IF @NatureAccount IS NOT NULL AND LEN(@NatureAccount)>1 BEGIN SET @SqlSub+= REPLACE(SUBSTRING(@NatureAccount,3,LEN(@NatureAccount)-3),'NatureAccount',' AND E.Code ') END IF @NatureAccountName IS NOT NULL AND LEN(@NatureAccountName)>1 BEGIN SET @SqlSub+= REPLACE(SUBSTRING(@NatureAccountName,3,LEN(@NatureAccountName)-3),'@NatureAccountName',' AND E1.Name ') END IF @Dept IS NOT NULL AND LEN(@Dept)>1 BEGIN SET @SqlSub+= REPLACE(SUBSTRING(@Dept,3,LEN(@Dept)-3),'Dept',' AND C.Segment4 ') END IF @Abstract IS NOT NULL AND LEN(@Abstract)>1 BEGIN SET @SqlSub+= REPLACE(SUBSTRING(@Abstract,3,LEN(@Abstract)-3),'Abstract',' AND A1.Abstracts ') END IF @Status=1 BEGIN SET @SqlSub+= ' AND B.VoucherStatus = 4 ' END SET @SqlSub+=' GROUP BY E.Code,C.Segment4 ' EXEC sp_executesql @SqlSub DECLARE @DeptCode NVARCHAR(200) DECLARE cDeptCode CURSOR FOR SELECT Code FROM dbo.CBO_Department WHERE Effective_IsEffective=1 ORDER BY Code OPEN cDeptCode FETCH NEXT FROM cDeptCode INTO @DeptCode WHILE @@FETCH_STATUS=0 BEGIN DECLARE @ResultSql NVARCHAR(MAX)='' SET @ResultSql+=' UPDATE A SET A.Expense'+@DeptCode+'=B.AccountAmount FROM #Cust_ExpenseInfoTable_Temp A INNER JOIN #Cust_ExpenseDataTable_Temp B ON A.AdminCode=B.AccountCode AND B.DeptCode='''+@DeptCode+''' UPDATE A SET A.Expense'+@DeptCode+'=B.AccountAmount FROM #Cust_ExpenseInfoTable_Temp A INNER JOIN #Cust_ExpenseDataTable_Temp B ON A.SaleCode=B.AccountCode AND B.DeptCode='''+@DeptCode+''' UPDATE A SET A.Expense'+@DeptCode+'=B.AccountAmount FROM #Cust_ExpenseInfoTable_Temp A INNER JOIN #Cust_ExpenseDataTable_Temp B ON A.ManuCode=B.AccountCode AND B.DeptCode='''+@DeptCode+'''' IF @FeeType=0 BEGIN SET @ResultSql+=' UPDATE A SET A.Expense'+@DeptCode+'=ISNULL(B.ExpenseTotal,0) FROM #Cust_ExpenseInfoTable_Temp A LEFT JOIN (SELECT SUBSTRING(ManuCode,1,6) manucode,SUM(Expense'+@DeptCode+') ExpenseTotal FROM #Cust_ExpenseInfoTable_Temp WHERE LEN(ManuCode)=8 GROUP BY SUBSTRING(ManuCode,1,6)) AS B ON B.manucode=A.ManuCode WHERE LEN(A.ManuCode)=6' END PRINT @ResultSql EXEC sp_executesql @ResultSql FETCH NEXT FROM cDeptCode INTO @DeptCode END CLOSE cDeptCode DEALLOCATE cDeptCode UPDATE #Cust_ExpenseInfoTable_Temp SET PeriodString=@PeriodString WHERE ID=1 SELECT * FROM #Cust_ExpenseInfoTable_Temp DROP TABLE #Cust_ExpenseDataTable_Temp DROP TABLE #Cust_ExpenseInfoTable_Temp END
按照客制化需求的格式，预置了报表行项目，在这基础上建立了科目及项目基础表，按照基础表去获取凭证分录数据进行统计。
管理费用和研发费用用一个格式，制造费用使用另一种格式。