#实践案例#销售类型运费关联发货单据管控原创
夏日弥光
8人赞赏了该文章
668次浏览
未经作者许可，禁止转载编辑于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;
}
}
赞 88人点赞
还没有人点赞，快来当第一个点赞的人吧！
打赏0人打赏
还没有人打赏，快来当第一个打赏的人吧！
推荐阅读