存货核算是企业物料管理的核算功能，涉及的表较多，关系也较复杂，通过存储过程完成计算和数据更新，可以取得较好的用户体验。
存货核算过程一般每月月末执行一次，或在必要时由用户操作。
数据字典：
存货总账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;
正常情况下，存货核算数据在每项业务登记时已经完成，本过程用户数据发生非常调整时，如进价调整、售价调整等、实施期间的业务期间非正常跳转（回到月初、回到上月）等，或数据发生意外错误时，可以使用本过程一次性重新计算。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
