存货核算是企业物料管理的核算功能，涉及的表较多，关系也较复杂，通过存储过程完成计算和数据更新，可以取得较好的用户体验。
存货核算过程一般每月月末执行一次，或在必要时由用户操作。
数据字典：
存货总账UTZZCH字段列表： ID int 4 键号 TextEdit NY char 6 年月 TextRead CKMC nvarchar 100 仓库 TextRead CHXH varchar 20 货号 TextRead JLDW varchar 20 计量单位 TextEdit QCJS int 4 期初件数 TextEdit 0;0;# QCSL decimal 9 期初数量 TextEdit #,###.00;-#,###.00;# QCDJ decimal 9 期初单价 TextEdit #,###.00;-#,###.00;# QCJE decimal 9 期初金额 TextEdit #,###.00;-#,###.00;# XQJS int 4 需求件数 TextEdit 0;0;# XQSL decimal 9 需求数量 TextEdit #,###.00;-#,###.00;# XQJE decimal 9 需求金额 TextEdit #,###.00;-#,###.00;# JHJS int 4 计划件数 TextEdit 0;0;# JHSL decimal 9 计划数量 TextEdit #,###.00;-#,###.00;# JHJE decimal 9 计划金额 TextEdit #,###.00;-#,###.00;# SRJS int 4 收入件数 TextEdit 0;0;# SRSL decimal 9 收入数量 TextEdit #,###.00;-#,###.00;# SRJE decimal 9 收入金额 TextEdit #,###.00;-#,###.00;# FCJS int 4 发出件数 TextEdit 0;0;# FCSL decimal 9 发出数量 TextEdit #,###.00;-#,###.00;# FCJE decimal 9 发出金额 TextEdit #,###.00;-#,###.00;# JCJS int 4 结存件数 TextEdit 0;0;# JCSL decimal 9 结存数量 TextEdit #,###.00;-#,###.00;# JCDJ decimal 9 结存单价 TextEdit #,###.00;-#,###.00;# JCJE decimal 9 结存金额 TextEdit #,###.00;-#,###.00;#
单据头DJCHO字段列表：（部分） OID varchar 30 计划单号 TextRead DJMC nvarchar 60 单据名称 TextRead NY char 6 年月 TextRead RQ date 3 日期 DateEdit yyyy-MM-dd DQBZ nvarchar 40 当前步骤 TextRead YWLB nvarchar 40 业务类别 TextEdit WLDW nvarchar 200 往来单位 ButtonEdit JSWB nvarchar 40 结算外币 TextRead SCBM nvarchar 100 生产部门 ListEdit CKMC nvarchar 100 仓库名称 ListEdit HTBH varchar 50 合同编号 TextEdit JFRQ datetime 8 交付日期 DateEdit yyyy-MM-dd YWY nvarchar 60 业务员 ComboEdit GLBJ nvarchar 100 关联标记 TextEdit BZ nvarchar 100 备注 TextEdit TZR nvarchar 20 制表人 TextRead SHR nvarchar 20 审核人 TextRead DJR nvarchar 20 登记人 TextRead
单据体DJCHM字段列表： MID int 4 计划序号 TextRead OID varchar 30 计划单号 TextRead DJMC nvarchar 60 单据名称 TextRead NY char 6 年月 TextRead DQBZ nvarchar 40 当前步骤 TextRead CHXH varchar 20 货号 ButtonEdit CHDH varchar 30 存货代号 ButtonEdit CHMC nvarchar 100 存货名称 TextRead XH1 nvarchar 100 型号 ButtonEdit XH2 nvarchar 100 规格 ButtonGgxh XH3 nvarchar 400 参数 TextEdit GG1 nvarchar 100 颜色 ComboEdit EDIT GG2 nvarchar 100 材质 TextEdit GG3 nvarchar 100 图号 TextEdit BZDW nvarchar 40 包装单位 NotEnter MJSL decimal 9 每件数量 TextEdit 0.00;0.00;# JLDW nvarchar 20 计量单位 NotEnter BZJS int 4 件数 TextEdit 0;0;# XQSL decimal 9 需求数量 TextEdit #,###.00;-#,###.00;# XQDJ decimal 9 需求单价 TextEdit #,###.00;-#,###.00;# XQJE decimal 9 需求金额 TextEdit #,###.00;-#,###.00;# JHSL decimal 9 计划数量 TextRead #,###.00;-#,###.00;# JHDJ decimal 9 计划单价 TextEdit #,###.00;-#,###.00;# JHJE decimal 9 计划金额 TextEdit #,###.00;-#,###.00;# RKSL decimal 9 入库数量 TextEdit #,###.00;-#,###.00;# RKDJ decimal 9 入库单价 TextEdit #,###.00;-#,###.00;# RKJE decimal 9 入库金额 TextEdit #,###.00;-#,###.00;# CKSL decimal 9 出库数量 TextEdit #,###.00;-#,###.00;# CKDJ decimal 9 出库单价 TextEdit #,###.00;-#,###.00;# CKJE decimal 9 出库金额 TextEdit #,###.00;-#,###.00;# JGSL decimal 9 加工数量 TextEdit #,###.00;-#,###.00;# JGDJ decimal 9 加工单价 TextEdit #,###.00;-#,###.00;# JGJE decimal 9 加工金额 TextEdit #,###.00;-#,###.00;# XSSL decimal 9 销售数量 TextEdit #,###.00;-#,###.00;# XSDJ decimal 9 销售单价 TextEdit #,###.00;-#,###.00;# XSJE decimal 9 销售金额 TextEdit #,###.00;-#,###.00;# WBJE decimal 9 外币金额 TextEdit #,###.00;-#,###.00;# ZZSL decimal 9 税率 TextEdit N2 ZZSE decimal 9 税额 TextEdit N2 WBSE decimal 9 外币税额 TextEdit N2 TP1 nvarchar 400 图片 FileEdit CLYT varchar 20 材料用途 TextRead DJRQ datetime 8 登记日期 DateEdit yyyy-MM-dd BZ nvarchar 400 备注 ButtonTextEdit MHH int 4 行号 TextEdit SFSH bit 1 是否审核 CheckEdit SFDJ bit 1 是否登记 CheckRead
存货核算存储过程代码：ms sql
ALTER PROCEDURE [dbo].[X9_HSCH] @HSNY VARCHAR(6), --核算年月 @DQYH nvarchar(10) --当前用户 AS BEGIN TRY SET NOCOUNT ON; --操作前请备份数据库 --一、清空存货总账入库数据 UPDATE UTZZCH SET JCJS=0,XQJS=0,XQSL=0,XQJE=0,JHJS=0,JHSL=0,JHJE=0,SRJS=0,SRSL=0,SRJE=0,FCJS=0,FCSL=0,FCJE=0 FROM UTZZCH WHERE NY=@HSNY; --二、更新存货总账数据,需求、计划、入库 WITH HSCH (NY,CHXH,XQJS,XQSL,XQJE,JHJS,JHSL,JHJE,SRJS,SRSL,SRJE) AS (SELECT O.NY,M.CHXH, SUM(CASE WHEN M.DJMC IN ('销售订单','需求计算单') THEN M.BZJS ELSE 0 END) AS XQJS, SUM(M.XQSL),SUM(M.XQJE), SUM(CASE WHEN M.DJMC IN ('采购订单','委外订单','生产计划单') THEN M.BZJS ELSE 0 END) AS JHJS,SUM(M.JHSL),SUM(M.JHJE), SUM(CASE WHEN M.DJMC IN ('采购入库单','委外入库单','完工入库单','盘点入库单') THEN M.BZJS ELSE 0 END) AS RKJS, SUM(M.RKSL),SUM(M.RKJE) FROM DJCHO O INNER JOIN DJCHM M ON O.OID=M.OID WHERE (O.NY = @HSNY) AND (O.DJR<>'') AND CHARINDEX('出库',O.DJMC)=0 GROUP BY O.NY,M.CHXH) UPDATE UTZZCH SET XQJS=DJ.XQJS,XQSL=DJ.XQSL,XQJE=DJ.XQJE,JHJS=DJ.JHJS,JHSL=DJ.JHSL,JHJE=DJ.JHJE, SRJS=DJ.SRJS,SRSL=DJ.SRSL,SRJE=DJ.SRJE FROM UTZZCH AS ZZ INNER JOIN HSCH AS DJ ON (ZZ.NY=DJ.NY AND ZZ.CHXH=DJ.CHXH) WHERE ZZ.NY=@HSNY; UPDATE UTZZCH SET JCDJ=XM.JHJJ FROM UTZZCH ZZ INNER JOIN MLCHXM XM ON ZZ.CHXH=XM.CHXH WHERE ZZ.NY=@HSNY AND (ZZ.QCSL+ZZ.SRSL)<=0; UPDATE UTZZCH SET JCDJ=(QCJE+SRJE)/(QCSL+SRSL) WHERE NY=@HSNY AND (QCSL+SRSL)>0 AND (QCJE+SRJE)>0; --三、更新单据数据，出库单价 UPDATE DJCHM SET CKDJ=ZZ.JCDJ,CKJE=ZZ.JCDJ*M.CKSL FROM DJCHM M INNER JOIN UTZZCH ZZ ON M.CHXH=ZZ.CHXH AND M.NY=ZZ.NY WHERE ZZ.NY=@HSNY AND CHARINDEX('出库',M.DJMC)<>0; --四、更新存货总账数据,出库 WITH HSCK (NY,CHXH,FCJS,FCSL,FCJE) AS (SELECT O.NY,M.CHXH, SUM(CASE WHEN M.DJMC IN ('领用出库单','委外出库单','销售出库单') THEN M.BZJS ELSE 0 END) AS CKJS,SUM(M.CKSL),SUM(M.CKJE) FROM DJCHO O INNER JOIN DJCHM M ON O.OID=M.OID WHERE (O.NY = @HSNY) AND (O.DJR<>'') AND CHARINDEX('出库',O.DJMC)<>0 GROUP BY O.NY,M.CHXH) UPDATE UTZZCH SET FCJS=DJ.FCJS,FCSL=DJ.FCSL,FCJE=DJ.FCJE FROM UTZZCH AS ZZ INNER JOIN HSCK AS DJ ON (ZZ.NY=DJ.NY AND ZZ.CHXH=DJ.CHXH) WHERE ZZ.NY=@HSNY; --五、更新总账结存数量金额 UPDATE UTZZCH SET JCSL=QCSL+SRSL-FCSL,JCJE=QCJE+SRJE-FCJE,JCJS=QCJS+SRJS-FCJS WHERE NY=@HSNY ; --六、更新总账余额 数量为0时金额不为零的核算误差 DECLARE @ZZID INT,@CHXH NVARCHAR(10); DECLARE @JCJE DECIMAL(16,2); DECLARE HSWC_cursor CURSOR LOCAL FOR SELECT ID,CHXH,JCJE FROM UTZZCH WHERE NY=@HSNY AND (JCSL=0 AND JCJE<>0); OPEN HSWC_cursor; FETCH FROM HSWC_cursor INTO @ZZID,@CHXH,@JCJE; WHILE @@FETCH_STATUS=0 BEGIN UPDATE UTZZCH SET JCJE=0,FCJE=FCJE+JCJE WHERE NY=@HSNY AND ID=@ZZID; WITH CKID (ID) AS (SELECT MAX(MID) AS ID FROM DJCHM WHERE NY=@HSNY AND CHXH=@CHXH AND (DJMC LIKE '%出库%')) UPDATE DJCHM SET CKJE=CKJE+@JCJE FROM DJCHM INNER JOIN CKID ON DJCHM.MID=CKID.ID; FETCH FROM HSWC_cursor INTO @ZZID,@CHXH,@JCJE; END CLOSE HSWC_cursor; DEALLOCATE HSWC_cursor ; --七、应付账款 --更新应付账款总账 借方贷方金额 采购入库单\委外入库单(加工费)部分 UPDATE UTZZWL SET JFFS=0,DFFS=0,WBJF=0,WBDF=0 WHERE NY=@HSNY AND WLDW>'50'; WITH HSYF (WLDW,JSWB,JFFS,DFFS,WBJF,WBDF) AS (SELECT O.WLDW,O.JSWB,0 AS JFFS, SUM(CASE WHEN O.DJMC='采购入库单' THEN M.CGJE WHEN O.DJMC='委外入库单' THEN M.JGJE ELSE 0 END) AS DFFS,0 AS WBJF, SUM(CASE WHEN O.DJMC='采购入库单' OR O.DJMC='委外入库单' THEN M.WBJE ELSE 0 END) AS WBDF FROM DJCHO O INNER JOIN DJCHM M ON O.OID = M.OID WHERE (O.NY = @HSNY) AND (O.DJR<>N'') AND (O.DJMC='采购入库单' OR O.DJMC='委外入库单') GROUP BY O.WLDW,O.JSWB) UPDATE UTZZWL SET JFFS=RK.JFFS,DFFS=RK.DFFS,WBJF=RK.WBJF,WBDF=RK.WBDF FROM UTZZWL AS ZZ INNER JOIN HSYF AS RK ON (ZZ.WLDW=RK.WLDW AND ZZ.WBMC=RK.JSWB) WHERE ZZ.NY=@HSNY; --更新应付账款总账 借方金额 付款单部分 WITH HSCN (WLDW,JSWB,FKJE,FKZR,FKJEWB,FKZRWB) AS (SELECT O.WLDW,O.JSWB,SUM(M.FKJE) AS FKJE, SUM(M.FKZR) AS FKZR,SUM(M.FKJEWB) AS FKWBJE,SUM(M.FKZRWB) AS FKZRWB FROM DJCNO O INNER JOIN DJCNM M ON O.OID = M.OID WHERE (O.NY = @HSNY) AND (M.FKJE <> 0) AND (O.DJMC='付款单') AND (O.DJR <> N'') GROUP BY O.WLDW,O.JSWB) UPDATE UTZZWL SET JFFS=ZZ.JFFS+FKJE+FKZR,WBJF=ZZ.WBJF+FKJEWB+FKZRWB FROM UTZZWL AS ZZ INNER JOIN HSCN AS CN ON (ZZ.WLDW=CN.WLDW AND ZZ.WBMC=CN.JSWB) WHERE ZZ.WLDW>'50' AND ZZ.NY=@HSNY; --更新应付账款总账余额 UPDATE UTZZWL SET YMYE=YCYE-DFFS+JFFS,WBYM=WBYC-WBDF+WBJF FROM UTZZWL WHERE NY=@HSNY; --六、更新以后年度总账：存货、往来 IF @HSNY<DBO.X9_DQNY() BEGIN DECLARE @SYNY CHAR(6); DECLARE @DQNY CHAR(6); SET @SYNY=@HSNY; DECLARE ZZNY_cursor CURSOR LOCAL FOR SELECT NY FROM MLNY WHERE NY>@HSNY; OPEN ZZNY_cursor; FETCH FROM ZZNY_cursor INTO @DQNY; WHILE @@FETCH_STATUS=0 BEGIN --更新UTZZCH WITH SYZZ (CHXH,JCJS,JCSL,JCDJ,JCJE) AS (SELECT CHXH,JCJS,JCSL, JCDJ, JCJE FROM UTZZCH WHERE (NY = @SYNY)) UPDATE UTZZCH SET QCJS=SYZZ.JCJS,QCSL=SYZZ.JCSL,QCDJ=SYZZ.JCDJ,QCJE=SYZZ.JCJE FROM UTZZCH AS ZZ INNER JOIN SYZZ ON ZZ.CHXH=SYZZ.CHXH WHERE ZZ.NY=@DQNY; UPDATE UTZZCH SET JCJS=QCJS+SRJS-FCJS,JCSL=QCSL+SRSL-FCSL,JCJE=QCJE+SRJE-FCJE WHERE NY=@DQNY ; UPDATE UTZZCH SET JCDJ=(QCJE+SRJE)/(QCSL+SRSL) WHERE NY=@DQNY AND (QCSL+SRSL)>0 AND (QCJE+SRJE)>0; --生成应付空账 INSERT INTO [UTZZWL] ([NY],[WLDW],WBMC) SELECT @DQNY AS NY,WLDH+'_'+WLMC AS WLDW,JSWB AS WBMC FROM MLWL WHERE DCMX=1 AND ((WLDH+'_'+WLMC) NOT IN (SELECT WLDW FROM UTZZWL WHERE NY=@DQNY)); --更新YFZZ WITH SYYF (WLDW,WBMC,YMYE,WBYM) AS (SELECT WLDW,WBMC,YMYE,WBYM FROM UTZZWL WHERE (NY = @SYNY)) UPDATE UTZZWL SET YCYE=SYYF.YMYE,WBYC=SYYF.WBYM FROM UTZZWL AS ZZ INNER JOIN SYYF ON (ZZ.WLDW=SYYF.WLDW AND ZZ.WBMC=SYYF.WBMC) WHERE ZZ.NY=@DQNY; UPDATE UTZZWL SET YMYE=YCYE+DFFS-JFFS,WBYM=WBYC+WBDF-WBJF FROM UTZZWL WHERE NY=@DQNY; SET @SYNY=@DQNY; FETCH NEXT FROM ZZNY_cursor INTO @DQNY; END CLOSE ZZNY_cursor; DEALLOCATE ZZNY_cursor ; END SELECT '存货核算已经完成！' END TRY BEGIN CATCH IF XACT_STATE() <> 0 BEGIN IF CURSOR_STATUS('LOCAL','HSWC_cursor')>=0 BEGIN CLOSE HSWC_cursor; DEALLOCATE HSWC_cursor ; END IF CURSOR_STATUS('LOCAL','ZZNY_cursor')>=0 BEGIN CLOSE ZZNY_cursor; DEALLOCATE ZZNY_cursor ; END END INSERT [dbo].[X9_ERRLOG] ([ErrorTime],[UserName], [ErrorNumber],[ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage]) VALUES (GETDATE(),CURRENT_USER, ERROR_NUMBER() ,ERROR_SEVERITY(), ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE()); SELECT '存货核算未能完成:'+CHAR(13)+CHAR(10)+ERROR_MESSAGE() ; END CATCH;
正常情况下，存货核算数据在每项业务登记时已经完成，本过程用户数据发生非常调整时，如进价调整、售价调整等、实施期间的业务期间非正常跳转（回到月初、回到上月）等，或数据发生意外错误时，可以使用本过程一次性重新计算。
