易助存货与会计总账对账.ppt
存货与会计对账,吴彦,目录,为什么存货与会计要进行对账为什么可以对账对账一般的处理流程差异的处理方法,为什么存货类的报表要和会计总账进行对账？,为了保证业务类的单据和财务报表的一致性，以及确保数据的准确性。,为什么存货类的报表可以和会计总账进行对账？,我们系统会计模块提供了自动分录抛转凭证的功能，在一般情况下（客户做账比较规范，不是一会儿抛到这个科目，一会儿抛到那个科目，且没有手工切凭证）存货类的报表和会计总账某科目的余额是可以对上的。,例子：收发存汇总表本月进货成本总计是300，总账里科目1243库存商品本月余额是366，不相等。,一般处理步骤：,1.在会计凭证单身档查看需要核对的会计科目里都有哪些单据抛入（在单身内容很多的情况下，可以用语句SELECT DISTINCT NDB017 FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB005=1405 AND NDA003 BETWEEN 20120701 AND 20120731来看来源）,一般处理步骤：,2.察看相关的单据都是在收发存汇总表的那个项目里3.然后核对,上述例子在小恐龙中筛选NDB005=1243,一般处理步骤：,发现其来源是21和60A，我们从BIN文件夹里的match知道21代表蓝字进货发票，60A代理红字采购发票。我们知道蓝字进货发票取单进货单，红字采购发票取单退货单，而收发存汇总表本期的进货成本的来源就是进货单和退货单，理论上讲通过取单做的蓝字进货发票和红字采购发票抛转凭证之后是可以和收发存汇总表的本月的进货成本对上的，这里为什么有差异？,一般处理步骤：,核对发票之后我们发现有一张蓝字进货发票不是通过取单做的，其发票来源是其他，正是这张发票抛转过去造成了差异。（我们可以通过SQL语句来排除这些发票，无来源的发票在YSFGSB采购发票单身档中GSB004和GSB005是空的）。,一般处理步骤：,上面的例子我们是以进退货成本为例的，总体细分下来对账可以细分为以下的14个模块，现分别对每一模块提供语句进行对账，看是否均能对得上。说明：LNA006字段是日期字段，根据实际的对账日期进行限定条件的设置，NDB005是要对账的科目，下面均以1405为例。,一般处理步骤：,收发存汇总表本期进货成本和总账科目对账对应模块1(进退货成本)收发存汇总表本期入库成本和总账科目对账对应模块6(生产入库)+模块7(生产入库退回)+模块8(委外进货)+模块9(委外退货)+模块3(入库成本)收发存汇总表本期销货成本和总账科目对账对应模块2(销退货成本),一般处理步骤：,收发存汇总表本期耗料成本和总账科目对账对应模块4(出库成本)+模块10(生产入库(子件)+模块11(生产退回(子件)+模块12(委外进货(子件)+模块13(委外退货(子件)收发存汇总表本期调整成本和总账科目对账对应模块14(调整成本),1-进退货成本,-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE(LNA001=18 OR LNA001=19)AND LNA006 BETWEEN 20090201 AND 20090228-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE(NDB017=21 OR NDB017=60A)AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,2-销退货成本,-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE(LNA001=33 OR LNA001=34)AND LNA006 BETWEEN 20090201 AND 20090228-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE(NDB017=33A OR NDB017=34A)AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,3-入库成本,-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=45 AND LNA006 BETWEEN 20090201 AND 20090228-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=45A AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,4-出库成本,-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=46 AND LNA006 BETWEEN 20090201 AND 20090228-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=46A AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,5-领退料成本,-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=82 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=O-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=82 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,6-生产入库成本(主件),-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=84 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=I-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=84 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,7-生产入库退回(主件),-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=85 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=O-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=85 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,8-委外进货成本(主件),-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=89 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=I-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=89 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,9-委外退货成本(主件),-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=90 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=O-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=90 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,10-生产入库(子件),-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=84 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=O-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=84 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,11-生产退回(子件),-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=85 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=I-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=85 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,12-委外进货(子件),-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=89 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=O-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=89 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,13-委外退回(子件),-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=90 AND LNA006 BETWEEN 20090201 AND 20090228 AND LNA004=I-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=90 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,14-调整成本,-业务单据金额SELECT SUM(LNA013*LNA015)FROM JSKLNA WHERE LNA001=02 AND LNA006 BETWEEN 20090201 AND 20090228-会计凭证金额SELECT SUM(NDB010*NDB006)FROM KJSNDB LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002 WHERE NDB017=02A AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405,一般处理步骤：,以上14个模块中的每2个语句执行的结果应该是一致的，否则该模块的凭证抛转的肯定有问题，发现是哪个模块有问题后，那就容易查多了，可以写更为细分的语句精确定位到是哪张单子有问题了，下面以生产入库单为例，进行细分。,差异处理方法：,下面的语句即可定位到哪张生产入库单和哪张凭证的金额对不上：SELECT NDB001,NDB002,NDB017,NDB018,NDB010 as 凭证金额,LNA013*LNA015 AS 入库成本,NDB010-LNA013*LNA015 AS 差异成本 FROM JSKLNALEFT JOIN KJSNDB ON LNA001=NDB017 AND LNA002=NDB018LEFT JOIN KJSNDA ON NDA001=NDB001 AND NDA002=NDB002WHERE NDB017=84 AND NDA003 BETWEEN 20090201 AND 20090228 AND NDB005=1405 AND LNA004=IAND NDB010-LNA013*LNA0150.0000001,差异处理方法：,上面这条语句可以查询到有问题的生产入库单单号及凭证类别和号码，找到这些号码后，具体看下是什么问题导致，是否重新抛转凭证即可！,思考题,课后练习：库存汇总表信息日期选某个月月末筛选存货仓，成本的总计和会计总账科目的该月余额对不上，如何查？思路：1上个月的期末余额和库存汇总表对上了吗？2抛转过去的凭证是否都是对上的？3是否还有未抛转的单据？,Thanks for watching,
