#实践案例#销售类型运费关联发货单据管控原创
金蝶云社区-夏日弥光
夏日弥光
7人赞赏了该文章 391次浏览 未经作者许可,禁止转载编辑于2023年02月16日 23:32:42
封面

一、业务背景

        星空针对销售运费和发货数据的管控比较薄弱,报销的每笔运费无法和实际出库数据一一匹配对应。

二、场景分析(现状)

        每次报销销售运费时,运输公司开一张发票可能附多达上百张运单,业务人员、财务人员基本无从核实每一笔运单是否真实,是否有重复报销的情况。

三、解决思路

        1.运输公司每次开票来对账,都会附带由系统打印出去并且客户签字的发货单,作为运费发生依据。那么我们在系统打印发货单时,可以把该笔发货单的单号作为二维码附带到单据。


上传图片

        

        2.在应付单扩展一个页签,如下图。报销运费时,由业务人员直接新增应付单,通过扫码枪的方式,一票一票扫描附件上的二维码,并且做强校验,已关联的应付单不允许保存。如果该发货单已经被关联,那么说明这单就有问题,需要重点确认了。


上传图片

上传图片

上传图片

        

        3.关联查询,通过插件处理,还可以让发货单据与应付单上下关联查询。关联查询部分代码主要参考论坛大神的无私分享。


上传图片

   public class ApplyLinkTransferSave : AbstractOperationServicePlugIn
    {
        public override void EndOperationTransaction(EndOperationTransactionArgs e)
        {
            base.EndOperationTransaction(e);
            foreach (DynamicObject p in e.DataEntitys)
            {
                string billTypeId = p["BillTypeID_Id"].ToString();
                if (billTypeId == "a83c007f22414b399b0ee9b9aafc75f9")//单据类型为标准应付单
                {
                    DynamicObjectCollection entityRows = p["AP_PAYABLEENTRY"] as DynamicObjectCollection;//单据体明细
                    if (entityRows.Count > 0)
                    {
                        string entrySourceType = entityRows[0]["FSOURCETYPE"].ToString();//源单类型
                        if (string.IsNullOrWhiteSpace(entrySourceType))//源单类型为空 非下推单据
                        {
                            DynamicObjectCollection entityRow = p["FTransferEntity"] as DynamicObjectCollection;//单据体 直接调拨单 
                            if (entityRow.Count > 0)
                            {
                                int j = 0;
                                foreach (DynamicObject i in entityRow)
                                {
                                    string sourceBillNo = entityRow[j]["FZJDBDNUMBERx"].ToString();
                                    string entityId = entityRow[j]["Id"].ToString();
                                    int Fstatus = Convert.ToInt32(entityRow[j]["FTransferEntityStatus"].ToString());
                                    if (Fstatus == 0)
                                    {
                                        string tagetBillNo = p["BillNo"].ToString();
                                        if (string.IsNullOrWhiteSpace(tagetBillNo))
                                        {
                                            throw new Exception("目标单(下游单据)的单据编号不能为空!");
                                        }
                                        var FFreightType = p["FFreightTypeX"].ToString();
                                        var targetBusinessInfo = FormMetaDataCache.GetCachedFormMetaData(this.Context, "AP_Payable").BusinessInfo;
                                        var errorMsg = string.Empty;
                                        if (CreateLinkForHead(ref errorMsg, Context, FormMetaDataCache.GetCachedFormMetaData(this.Context, "STK_TransferDirect").BusinessInfo, sourceBillNo, targetBusinessInfo, tagetBillNo))
                                        {
                                            string sql = @"  update T_AP_PAYABLETransfer set FTransferEntityStatus = 1  where FEntryID = '" + entityId + "'";//更新调拨单页签明细状态为已关联   正式 T_AP_PAYABLETransfer  测试 t_TransferEntity
                                            DataSet ds = DBServiceHelper.ExecuteDataSet(this.Context, sql);
                                        }
                                        else
                                        {
                                            throw new Exception("关联关系创建失败:" + errorMsg);
                                        }
                                    }
                                    j++;
                                }
                            }
                            else
                            {
                                throw new Exception("如果是报外贸运费,直接调拨单不能为空");
                            }
                        }
                        if (entrySourceType == "PUR_PurchaseOrder")//源单类型为采购订单
                        {
                            DynamicObjectCollection entityRow = p["FTransferEntity"] as DynamicObjectCollection;//单据体 直接调拨单 
                            if (entityRow.Count > 0)
                            {
                                int j = 0;
                                foreach (DynamicObject i in entityRow)
                                {
                                    string sourceBillNo = entityRow[j]["FZJDBDNUMBERx"].ToString();
                                    string entityId = entityRow[j]["Id"].ToString();
                                    int Fstatus = Convert.ToInt32(entityRow[j]["FTransferEntityStatus"].ToString());
                                    if (Fstatus == 0)
                                    {
                                        string tagetBillNo = p["BillNo"].ToString();
                                        if (string.IsNullOrWhiteSpace(tagetBillNo))
                                        {
                                            throw new Exception("目标单(下游单据)的单据编号不能为空!");
                                        }
                                        var FFreightType = p["FFreightTypeX"].ToString();
                                        var targetBusinessInfo = FormMetaDataCache.GetCachedFormMetaData(this.Context, "AP_Payable").BusinessInfo;
                                        var errorMsg = string.Empty;
                                        if (CreateLinkForHead(ref errorMsg, Context, FormMetaDataCache.GetCachedFormMetaData(this.Context, "STK_TransferDirect").BusinessInfo, sourceBillNo, targetBusinessInfo, tagetBillNo))
                                        {
                                            string sql = @"  update T_AP_PAYABLETransfer set FTransferEntityStatus = 1  where FEntryID = '" + entityId + "'";//更新调拨单页签明细状态为已关联   正式 T_AP_PAYABLETransfer  测试 t_TransferEntity
                                            DataSet ds = DBServiceHelper.ExecuteDataSet(this.Context, sql);
                                        }
                                        else
                                        {
                                            throw new Exception("关联关系创建失败:" + errorMsg);
                                        }
                                    }
                                    j++;
                                }
                            }
                        }
                    }
                }
            }
        }

        public bool CreateLinkForHead(ref string errorMsg, Context ctx, BusinessInfo sourceBusinessInfo, string sourceBillNo, BusinessInfo targetBusinessInfo, string targetBillNo)
        {
            #region 取数

            // 取源单
            var queryBuilderParemeter = new QueryBuilderParemeter();
            queryBuilderParemeter.BusinessInfo = sourceBusinessInfo;
            queryBuilderParemeter.FilterClauseWihtKey = string.Format("FBillNo='{0}'", sourceBillNo);
            var objs = BusinessDataServiceHelper.Load(ctx, sourceBusinessInfo.GetDynamicObjectType(), queryBuilderParemeter);
            if (objs == null || objs.Length == 0)
            {
                errorMsg = string.Format("源单(上游单据){0}不存在!", sourceBillNo);
                return false;
            }
            var sourceObj = objs[0];
            var sourceId = sourceObj[0].ToString();
            string FDomesticFreight = sourceObj["FDomesticFreight"].ToString();
            string FSeaFreight = sourceObj["FSeaFreight"].ToString();
            string FDomesticFreightx = sourceObj["FDomesticFreightx"].ToString();
            string FDomesticFreighty = sourceObj["FDomesticFreighty"].ToString();
            string FExportInsurance = sourceObj["FExportInsurance"].ToString();
            // 取目标单
            queryBuilderParemeter = new QueryBuilderParemeter();
            queryBuilderParemeter.BusinessInfo = targetBusinessInfo;
            queryBuilderParemeter.FilterClauseWihtKey = string.Format("FBillNo='{0}'", targetBillNo);
            objs = BusinessDataServiceHelper.Load(ctx, targetBusinessInfo.GetDynamicObjectType(), queryBuilderParemeter);
            if (objs == null || objs.Length == 0)
            {
                errorMsg = string.Format("目标单(下游单据){0}不存在!", targetBillNo);
                return false;
            }
            var targetObj = objs[0];
            var targetId = targetObj[0].ToString();
            //var FFreightType = targetObj[100].ToString();

            #endregion


            #region 创建关联关系
            string FFreightTypeX = targetObj["FFreightTypeX"].ToString();
            string[] strArray = FFreightTypeX.Split(',');
            int i = 0;
            foreach (var p in strArray)
            {
                string FFreightType = p;
                if ((FFreightType == "A" && FDomesticFreight == "False") || (FFreightType == "B" && FSeaFreight == "False") || (FFreightType == "C" && FDomesticFreightx == "False") || (FFreightType == "D" && FDomesticFreighty == "False") || (FFreightType == "E" && FExportInsurance == "False"))//国内运费创建关联
                {
                    if (FFreightType == "A") 
                    {
                        string sqlupdate = @" update T_STK_STKTRANSFERIN set FDomesticFreight = 1  where   FDomesticFreight =0 and FBILLNO = '" + sourceBillNo + "'";//反写直接调拨单
                        DataSet dss = DBServiceHelper.ExecuteDataSet(this.Context, sqlupdate);
                    }
                    if (FFreightType == "B")
                    {
                        string sqlL = @" update T_STK_STKTRANSFERIN set FSeaFreight = 1 where  FSeaFreight =0 and FBILLNO = '" + sourceBillNo + "'";//反写直接调拨单
                        DataSet ds = DBServiceHelper.ExecuteDataSet(this.Context, sqlL);
                    }
                    if (FFreightType == "C")
                    {
                        string sqlupdate = @" update T_STK_STKTRANSFERIN set FDomesticFreightx = 1  where   FDomesticFreightx =0 and FBILLNO = '" + sourceBillNo + "'";//反写直接调拨单
                        DataSet dss = DBServiceHelper.ExecuteDataSet(this.Context, sqlupdate);
                    }
                    if (FFreightType == "D")
                    {
                        string sqlupdate = @" update T_STK_STKTRANSFERIN set FDomesticFreighty = 1  where   FDomesticFreighty =0 and FBILLNO = '" + sourceBillNo + "'";//反写直接调拨单
                        DataSet dss = DBServiceHelper.ExecuteDataSet(this.Context, sqlupdate);
                    }
                    if (FFreightType == "E")
                    {
                        string sqlupdate = @" update T_STK_STKTRANSFERIN set FExportInsurance = 1  where   FExportInsurance =0 and FBILLNO = '" + sourceBillNo + "'";//反写直接调拨单
                        DataSet dss = DBServiceHelper.ExecuteDataSet(this.Context, sqlupdate);
                    }
                    if (i == 0)
                    {
                        var sqls = new List<string>();
                        var sql = string.Empty;
                        var instanceId = Guid.NewGuid().ToString();
                        // 流程实例
                        sql = string.Format(@"INSERT INTO T_BF_INSTANCE (FINSTANCEID,FFLOWID,FSOURCEID,FMASTERID,FSTATUS,FFIRSTFORMID,FFIRSTBILLID,FFIRSTBILLNO,FSTARTTIME)
                                    VALUES('{0}','','','{0}','A','{1}',{2},N'{3}',GETDATE())", instanceId, sourceBusinessInfo.GetForm().Id, sourceId, sourceBillNo);
                        sqls.Add(sql);
                        // 添加流程实例分录之根节点
                        // 此处仅关联单据头
                        var sourceEntity = sourceBusinessInfo.GetEntity("FBillHead");
                        var sourceTableName = sourceEntity.TableName;
                        // 检查源表定义是否存在
                        sql = string.Format(@"IF NOT EXISTS(SELECT 1 FROM T_BF_TABLEDEFINE WHERE FTABLENUMBER='{1}' AND FFORMID='{2}' AND FENTITYKEY='{3}')
                                    INSERT INTO T_BF_TABLEDEFINE
                                            ( FTABLEID ,
                                              FTABLENUMBER ,
                                              FFORMID ,
                                              FENTITYKEY ,
                                              FSEQ
                                            )
                                    VALUES  ( '{0}' , -- FTABLEID - varchar(36)
                                              '{1}' , -- FTABLENUMBER - varchar(30)
                                              '{2}' , -- FFORMID - varchar(36)
                                              '{3}' , -- FENTITYKEY - varchar(30)
                                              0  -- FSEQ - int
        )", Guid.NewGuid(), sourceTableName, sourceBusinessInfo.GetForm().Id, sourceEntity.Key);
                        sqls.Add(sql);
                        sql = string.Format(@"INSERT INTO T_BF_INSTANCEENTRY (FROUTEID,FINSTANCEID,FLINEID,FSTABLENAME,FSID,FTTABLENAME,FTID,FFIRSTNODE,FCREATETIME)
                                    VALUES ('{0}','{1}',0,'',0,'{2}',{3},'1',GETDATE())", Guid.NewGuid(), instanceId, sourceTableName, sourceId);
                        sqls.Add(sql);
                        // 添加流程实例分录之子节点
                        var targetEntity = targetBusinessInfo.GetEntity("FBillHead");
                        var targetTableName = targetEntity.TableName;
                        // 检查目标表定义是否存在
                        sql = string.Format(@"IF NOT EXISTS(SELECT 1 FROM T_BF_TABLEDEFINE WHERE FTABLENUMBER='{1}' AND FFORMID='{2}' AND FENTITYKEY='{3}')
                                    INSERT INTO T_BF_TABLEDEFINE
                                            ( FTABLEID ,
                                              FTABLENUMBER ,
                                              FFORMID ,
                                              FENTITYKEY ,
                                              FSEQ
                                            )
                                    VALUES  ( '{0}' , -- FTABLEID - varchar(36)
                                              '{1}' , -- FTABLENUMBER - varchar(30)
                                              '{2}' , -- FFORMID - varchar(36)
                                              '{3}' , -- FENTITYKEY - varchar(30)
                                              0  -- FSEQ - int
        )", Guid.NewGuid(), targetTableName, targetBusinessInfo.GetForm().Id, targetEntity.Key);
                        sqls.Add(sql);
                        sql = string.Format(@"INSERT INTO T_BF_INSTANCEENTRY (FROUTEID,FINSTANCEID,FLINEID,FSTABLENAME,FSID,FTTABLENAME,FTID,FFIRSTNODE,FCREATETIME)
                                    VALUES ('{0}','{1}',0,'{2}',{3},'{4}',{5},'0',GETDATE())", Guid.NewGuid(), instanceId, sourceTableName, sourceId, targetTableName, targetId);
                        sqls.Add(sql);
                        DBServiceHelper.ExecuteBatch(ctx, sqls);
                        //return true;
                        i++;
                    }
                }
                else
                {
                    errorMsg = string.Format("" + sourceBillNo + "调拨单已关联应付单,请检查");
                    return false;
                }
            }
            #endregion
            return true;
        } 

    }


赞 7