K3 做售服任务的相关处理流程（新单下推老单反写信息）

为了有效处理售服任务的物料跟踪情况，制作相关单据进行处理。

一、建立售服订单维护平台
1、要求所有售服任务做销售订单，在销售订单界面上增加“售服单号”列。

2、利用K+自定义报表功能做一个维护平台。
首先写存储过程

ALTER PROCEDURE [dbo].[jlkj_CHR_售服任务单]
@SHBH varchar(20),
@StartDate date,
@EndDate date
AS

BEGIN

SET NOCOUNT ON;

if exists(select 1 from tempdb.sys.objects where name='##SHRW_SHOW')
DROP TABLE ##SHRW_SHOW;

-----先建展示表

create table ##SHRW_SHOW(
finterid int,
FBillNo varchar(20),
FDate date,
SHBH varchar(20),--售服单号
type varchar(20),--业务类型
dept varchar(20),--部门
SALER varchar(20),--业务员
custer varchar(200),--客户
ht_date date,--合同交期
PS_date date ,--最近配送日期
FExplanation varchar(250),--摘要
qtbl varchar(5),--齐套比例
psbl varchar(5),--领料比例
ECDATE date , --预计完成日期
kp varchar(10) --是否开票
);

---插入数据
 insert into ##SHRW_SHOW
 (
 finterid  ,
FBillNo  ,
FDate  ,
SHBH  ,--售服单号
type  ,--业务类型
dept  ,--部门
SALER  ,--业务员
custer  ,--客户
FExplanation,  --摘要
ECDATE --预计完成日期
)
select 
finterid,
t1.FBillNo,
t1.FDate,
t1.FHeadSelfS0162 SHBH,--售服单号
A.FNAME type,--业务类型
b.FName dept,--部门
D.fname SALER,--业务员
c.FName custer,--客户
t1.FExplanation,--摘要
t1.FHeadSelfS0163 --预计完成日期

from  SEOrder T1
inner join  t_BOS200000000 A ON  t1.FHeadSelfS0153=A.FID
inner join t_Department B on t1.FDeptID=b.FItemID
inner join T_Organization c on t1.fcustid=c.FItemID
inner  join t_emp D ON t1.fempid=D.FItemID
where 
t1.fstatus=1  and t1.FCancellation=0 and FClosed=0 
and  (t1.FDeptID=26392 or t1.fDeptID=62888)
and (ltrim(rtrim(t1.FHeadSelfS0162)) is not null and  ltrim(rtrim(t1.FHeadSelfS0162))<>'' )



--更新最晚合同交期
UPDATE  t1 set T1.ht_date= T2.maxdate  FROM ##SHRW_SHOW T1   
INNER JOIN 
(
select A.FINTERID,max(b.FAdviceConsignDate) maxDATE from  ##SHRW_SHOW A
INNER JOIN SEOrderEntry b ON A.FINTERID=B.FInterID  
GROUP BY A.FINTERID)T2 ON T1.FINTERID=T2.FINTERID



--更新最近配送日期
UPDATE  t1 set T1.PS_date= T2.maxdate  FROM ##SHRW_SHOW T1   
INNER JOIN 
(
select a.FID_SRC,max(B.FDate) maxdate from  t_BOS257800033 A
inner join  t_BOS257800033Entry2 B on a.FID=b.FID
group by a.FID_SRC
)T2 ON T1.FINTERID=T2.FID_SRC


--更新齐套,发料比例 此处从##TAB_SHOW取数，所以先要执行jlkj_CHR_售服任务物料清单或前台的售后物料清单管理
if exists(select 1 from tempdb.sys.objects where name='##TAB_SHOW')
UPDATE  t1 set T1.qtbl= T2.qt,T1.psbl= T2.ps  FROM ##SHRW_SHOW T1   
INNER JOIN 
(
select SHBH,
rtrim(convert(char(4), cast(round(sum(
case when
finteger1>=finteger then finteger
else finteger1 end
)/
(sum(finteger)+0.0) *100,0) as int)))+'%' ps,

rtrim(convert(char(4), cast(round(sum(
case when
finteger1>=finteger then finteger
when
skc>=finteger then finteger
else skc end 
)/
(sum(finteger)+0.0) *100,0) as int)))+'%' qt
from  ##TAB_SHOW
group by SHBH
)T2 ON T1.SHBH=T2.SHBH
;

----更新是否已开发票 
update t1 set
t1.kp=
(case when t2.sl>=t2.Wkp THEN ''
else '已开票' end ) 
from ##SHRW_SHOW t1
inner join 
(
select a.FInterID,sum(a.FQty) sl,sum(a.FEntrySelfS0167) wkp from   SEOrderEntry  a
inner join ##SHRW_SHOW b on a.FInterID=b.finterid
group by  a.FInterID
)t2 on t1.finterid=t2.finterid where 1=1




----展示
select 
finterid fid,
FBillNo 订单号,
FDate 日期,
SHBH 售服单号,
type 业务类型,
dept 部门,	
SALER 业务员,
custer 客户,
ht_date 最晚合同交期,
PS_date 最近配送日期,
FExplanation 摘要,
case when 
qtbl='null' then '' else qtbl  end 齐套比例,
case when 
psbl='null' then '' else psbl  end 发料比例,
ECDATE 预计完成日期,
kp 是否开票


from ##SHRW_SHOW
where 1=1 and  SHBH  like '%'+@SHBH+'%'  and FDate>=@StartDate and FDate<=@EndDate

end

然后用K+做维护平台

//过滤代码
KpFilter.AddText("SHBH","售后编号",15,50，200,50) 
KpFilter.AddText("StartDate","日期",15,100,150,50) 
KpFilter.AddText("EndDate","日期",300,100,150,50)

//报表代码
Dim SQL As String ="exec jlkj_CHR_售服任务单 '" & KpFilter.Value("SHBH") & "','" & KpFilter.Value("StartDate") & "','" & KpFilter.Value("EndDate") & "' " 
KpRpt.DataTable             = KpTool.SQLTable(SQL)
KpRpt.IsEdit                = True'表格字段是否可编辑 
KpRpt.IsShowFooter          = True 
KpRpt.IsCellValueToRowValue = True
KpRpt.ColSort               = True
KpRpt.HeadColShowOrder      = True 
KpRpt.HeadColWidth          = 60
KpRpt.AutoFilterRow         = True

//方法代码
'单元格是否可编辑    
     <KpEvent("单元格是否可编辑")>
    Public Function 单元格_是否可编辑(FieldName As String, RowInt As Integer, Value As Object) As Boolean
        Try
            if FieldName="售服单号"  then
                   return true
              end if
                if FieldName="预计完成日期"  then
                   return true
              end if
               if FieldName="摘要"  then
                   return true
               else 
                   return false
              end if
       Catch ex As Exception
            MsgBox(String.Format("单元格是否可编辑出错，错误信息：{0}", ex.Message), MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "自定义报表代码插件")
        End Try
        Return True
    End Function                  
 '单元格值变化    
     <KpEvent("单元格值变化")>
    Public Sub 单元格_值变化(FieldName As String, RowInt As Integer, Value As Object)
        Try
            if FieldName="摘要" then
                KpTool.SQLExecute("update SEOrder set FExplanation='"& Value("摘要") &"' where FInterID=" & Value("FID")&" and FHeadSelfS0162=" &"'"& Value("售服单号")&"'" )
            end if
            if FieldName="预计完成日期" then
                KpTool.SQLExecute("update SEOrder set fheadselfs0163='" & Value("预计完成日期") &"' where finterid=" & Value("FID") &" and FHeadSelfS0162=" &"'"& Value("售服单号")&"'" )
            end if           
        Catch ex As Exception
            MsgBox(String.Format("单元格值变化出错，错误信息：{0}", ex.Message), MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "自定义报表代码插件")
        End Try
    End Sub
    

二、做物料清单表
由于售服的物料不走MRP计划，另外设计出清单会比较晚，而且后期会有增减，所以需要一张表单来登记售服任务的物料明细
1、先建BOS表单

2、日常维护，同样建一个K+报表进行维护
先建存储过程

ALTER PROCEDURE [dbo].[jlkj_CHR_售服任务物料清单]
@SHBH varchar(20)
AS

BEGIN

SET NOCOUNT ON;

if exists(select 1 from tempdb.sys.objects where name='##TAB_SHBH')
drop table ##TAB_SHBH;
if exists(select 1 from tempdb.sys.objects where name='##TAB_SHWLB')
drop table ##TAB_SHWLB;
if exists(select 1 from tempdb.sys.objects where name='##TAB_SHOW')
drop table ##TAB_SHOW;

----建售后任务单号总表
select rtrim(ltrim(FHeadSelfS0162)) AS SHBH  into ##TAB_SHBH from  seorder where  FClosed=0 and (rtrim(ltrim(FHeadSelfS0162)) is not null and  rtrim(ltrim(FHeadSelfS0162))<>'' ) ;

--select * FROM  ##TAB_SHBH;
----建展示表
 select
 a.fid,--单据内码
 A.FSANo SHBH,--售服单号


 b.FIndex, --明细序号
 b.FBase, --物料代码
 b.FInteger,--BOM数量
 B.FDate1,--交期
 0  SKC,--实库存 
 0  total_zz,--在制总数
 0  rw_zz,--任务在制
 0  rw_sq,--任务采购申请数
 0  total_zt,--在途总数
 0  total_dj,--待检总数
 0  rw_zt,--任务在途
 B.FInteger1,--已领数量
 B.FDate,--配送日期
 B.FBZ, --备注
 B.FZBJ, --组部件
 a.fsaler,--业务员
 B.fdate5 YJDATE --预计交货日期
 into ##TAB_SHOW
 from  t_BOS257800033 A
INNER JOIN t_BOS257800033Entry2 B  on a.fid=b.FID
left   join  ##TAB_SHBH c ON C.SHBH=A.FSANo  and b.fclose<>1 --将来如果所有任务都从订单下推的话，这里要改回inner
;

--建售后物料表
select fbase  into ##TAB_SHWLB from  ##TAB_show group by fbase;

--更新实库存SKC
update T2 set  T2.SKC=T1.sl 
from  
(select a.fitemid fitemid,
sum(a.FQty ) sl 
from  ICInventory a  
inner join ##TAB_SHWLB b on a.FItemID=b.FBase 
where a.FStockID in (26598,66585) and a.fqty<>0
group by a.fitemid
) T1
inner join ##TAB_SHOW T2 on T1.FItemID=T2.FBase 
WHERE 1=1

--更新在制总数total_zz
update T2 set  T2.total_zz=T1.sl 
from  
(select A.FItemID,  sum(A.FAUXQTY) SL
from  ICMO A  
inner join ##TAB_SHWLB b on a.FItemID=b.fbase
WHERE     A.FStatus=1 AND A.FSuspend<>1  and a.FCancellation<>1
AND A.FAUXSTOCKQTY<A.FAuxQty 
and a.FWorkShop=54492 --机加任务
and a.FHeadSelfJ01100=104857 --新制
and (a.FHeadSelfJ01111='' or a.FHeadSelfJ01111 is null)
group by A.FItemID
) T1
inner join ##TAB_SHOW T2 on T1.FItemID=T2.FBase 
WHERE 1=1

--更新任务总数rw_zz
update T2 set  T2.rw_zz=T1.sl 
from  
(select a.FNote,A.FItemID,  sum(A.FAUXQTY) SL
from  ICMO A  
inner join ##TAB_SHWLB b on a.FItemID=b.fbase 
WHERE     A.FStatus=1 AND A.FSuspend<>1  and a.FCancellation<>1
AND A.FAUXSTOCKQTY<A.FAuxQty 
and a.FWorkShop=54492 --机加任务
and a.FHeadSelfJ01100=104857 --新制
and (a.FHeadSelfJ01111='' or a.FHeadSelfJ01111 is null)
group by a.FNote,A.FItemID
) T1
inner join ##TAB_SHOW T2 on T1.FItemID=T2.FBase  and t1.FNote=T2.SHBH
WHERE 1=1

--更新采购申请任务数rw_sq
update T2 set  T2.rw_sq=T1.sl 
from  
( SELECT a.fuse,A.FITEMID,  sum(A.FQty) sl
  FROM  PORequestEntry A
  inner join PORequest B  on A.FInterID=B.FInterID 
  inner join ##TAB_SHWLB c on a.FItemID=c.FBASE 
  WHERE     B.FCancellation=0 AND B.FClosed=0
  GROUP by  a.fuse,A.FITEMID
) T1
inner join ##TAB_SHOW T2 on T1.FItemID=T2.FBase  and t1.FUse=T2.SHBH
WHERE 1=1


--更新待检总数total_dj,计划数-合格数,不考虑退料
update T2 set  T2.total_dj=T1.sl 
from  
(  SELECT A.FITEMID, SUM(A.FQty-a.FAuxQtyPass) sl 
  FROM  POInStockEntry A
  inner join POInStock B  on A.FInterID=B.FInterID 
  inner join ##TAB_SHWLB c on a.FItemID=c.FBASE 
  WHERE   A.FQty-A.FAuxQtyPass>0   AND B.FCancellation=0 AND   B.FClosed=0 and b.FTranType=72
  GROUP BY A.FITEMID
) T1
inner join ##TAB_SHOW T2 on T1.FItemID=T2.FBase 
WHERE 1=1

--更新在途总数total_zt
update T2 set  T2.total_zt=T1.sl 
from  
(  SELECT A.FITEMID, SUM(A.FQty-A.FCommitQty) sl 
  FROM  POOrderEntry A
  inner join POOrder B  on A.FInterID=B.FInterID 
  inner join ##TAB_SHWLB c on a.FItemID=c.FBASE 
  WHERE   A.FQty-A.FCommitQty>0   AND B.FCancellation=0 AND (A.FMrpAutoClosed=1 or  A.FMrpClosed=0)
  GROUP BY A.FITEMID
) T1
inner join ##TAB_SHOW T2 on T1.FItemID=T2.FBase 
WHERE 1=1

--更新任务总数rw_zt
update T2 set  T2.rw_zt=T1.sl 
from  
( SELECT A.FNOTE,A.FITEMID, SUM(A.FQty-A.FCommitQty) sl 
  FROM  POOrderEntry A
  inner join POOrder B  on A.FInterID=B.FInterID 
  inner join ##TAB_SHWLB c on a.FItemID=c.FBASE 
  WHERE   A.FQty-A.FCommitQty>0   AND B.FCancellation=0 AND (A.FMrpAutoClosed=1 or  A.FMrpClosed=0)
  GROUP BY A.FNOTE,A.FITEMID
) T1
inner join ##TAB_SHOW T2 on T1.FItemID=T2.FBase  and t1.FNote=T2.SHBH
WHERE 1=1


---有些领料与物料表未做关联，判断有无领料，进行模糊查询并更新。
---如果做到领料都和物料表关联，则可以屏蔽
update A SET A.FInteger1=B.SL FROM  ##TAB_SHOW A
INNER JOIN 
(
select YT,SHBH,FITEMID,SUM(SL) sl from (
Select distinct(v1.fbillno),ltrim(rtrim(V1.FUse))  YT,t.shbh SHBH,u1.FItemID,
u1.FQty  sl 
from ICStockBill v1 
inner  JOIN ICStockBillEntry u1 ON     v1.FInterID = u1.FInterID  
inner join ##TAB_SHOW T on  u1.FItemID=t.fbase  and ltrim(rtrim(V1.FUse)) like '%'+ltrim(rtrim(t.shbh))+'%'
 where 1=1 AND  v1.FCancellation = 0
and (v1.FTranType=24 or v1.FTranType=29)  and t.FInteger1=0)TT
GROUP BY YT,SHBH,FITEMID
)B ON A.SHBH=B.SHBH AND A.FBASE=B.FITEMID 
where a.FInteger1=0




----展示表
SELECT a.fid,
a.SHBH 售后编号,
D.FName	 业务员,
c.FNumber 组件代码,
c.FName 组件名称,
A.FIndex 序号,
B.FItemID 物料内码,
B.FNumber 物料代码,
B.FName 物料名称,
B.FModel 物料规格,
A.FInteger BOM数量,
A.FDate1 任务交期,
A.SKC 实库存,
A.TOTAL_ZZ 在制总数,
A.rw_zz 其中任务在制,
A.rw_sq 任务采申数,
A.total_zt 在途总数,
A.total_dj 待检总数,
A.RW_ZT 其中任务在途,
A.FDATE 配送日期,
A.FInteger1 已领数量,
A.FBZ 备注,
a.YJDATE 预计交货日期
FROM ##TAB_SHOW a
inner join t_ICItem b on a.FBase=b.FItemID
left  join  t_Item_3004  C ON a.FZBJ=c.FItemID
left  join t_emp D ON A.FSALER=D.FItemID
where 1=1 and a.SHBH  like '%'+@SHBH+'%' 
order by a.fid,A.FIndex


end

再维护K+的自定义报表

//过滤代码
KpFilter.AddText("SHBH","售后编号",15,50，200,50) 

//报表代码
Dim SQL As String ="exec jlkj_CHR_售服任务物料清单 '" & KpFilter.Value("SHBH") & "'" 
KpRpt.DataTable             = KpTool.SQLTable(SQL)
KpRpt.IsEdit                = True'表格字段是否可编辑 
KpRpt.IsShowFooter          = True 
KpRpt.IsCellValueToRowValue = True
KpRpt.ColSort               = True
KpRpt.HeadColShowOrder      = True 
KpRpt.HeadColWidth          = 60
KpRpt.AutoFilterRow         = True

//方法代码

'单元格是否可编辑    
     <KpEvent("单元格是否可编辑")>
    Public Function 单元格_是否可编辑(FieldName As String, RowInt As Integer, Value As Object) As Boolean
        Try
            if FieldName="物料代码"  then
                   return true
              end if
               if FieldName="售后编号"  then
                   return true
              end if
            if FieldName="配送日期"  then
                   return true
              end if
               if FieldName="预计交货日期"  then
                   return true
              end if
            if FieldName="备注"  then
                   return true
            else 
                   return false
            end if
       Catch ex As Exception
            MsgBox(String.Format("单元格是否可编辑出错，错误信息：{0}", ex.Message), MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "自定义报表代码插件")
        End Try
        Return True
    End Function                  
 '单元格值变化    
     <KpEvent("单元格值变化")>
    Public Sub 单元格_值变化(FieldName As String, RowInt As Integer, Value As Object)
        Try
            if FieldName="备注" then
                KpTool.SQLExecute("update t_BOS257800033Entry2 set FBZ='" & Value("备注") &"' where FID=" & Value("FID") &" and Findex=" & Value("序号"))
            end if
            
            if FieldName="配送日期" then
               KpTool.SQLExecute("update t_BOS257800033Entry2 set FDATE='" & Value("配送日期") &"' where FID=" & Value("FID") &" and Findex=" & Value("序号"))
            end if
           if FieldName="预计交货日期" then
               KpTool.SQLExecute("update t_BOS257800033Entry2 set FDATE5='" & Value("预计交货日期") &"' where FID=" & Value("FID") &" and Findex=" & Value("序号"))
            end if
           
           
        Catch ex As Exception
            MsgBox(String.Format("单元格值变化出错，错误信息：{0}", ex.Message), MsgBoxStyle.Information Or MsgBoxStyle.OkOnly, "自定义报表代码插件")
        End Try
    End Sub
    

3、领料
当齐套比例是100%时，核对库存数量是否充足，通知仓库配送。
这里做单据转换流程

参考https://blog.csdn.net/weixin_44819434/article/details/130081228?spm=1001.2014.3001.5502
更新出库时物料默认的仓库和仓位

INSERT INTO ICSelbills (FID, FFieldName, FDstCtlField, FSelType, FDK, FColName, FName, FTableName, FTableAlias, FAction, FROB, FAllowEdited)
VALUES('B04','257800033','FSCStockID',0,0,'S0','Fbase','t_BOS257800033Entry2','u1','(select A.FITEMID from t_Stock A
INNER JOIN T_ICITEM B ON A.FItemID=B.FDefaultLoc
WHERE B.Fitemid=u1.Fbase)',0,0)
INSERT INTO ICSelbills (FID, FFieldName, FDstCtlField, FSelType, FDK, FColName, FName, FTableName, FTableAlias, FAction, FROB, FAllowEdited)
VALUES('B04','257800033','FSCStockID',0,1,'S1','Fbase','t_BOS257800033Entry2','u1','(select A.FName from t_Stock A
INNER JOIN T_ICITEM B ON A.FItemID=B.FDefaultLoc
WHERE B.Fitemid=u1.Fbase)',0,0)
INSERT INTO ICSelbills (FID, FFieldName, FDstCtlField, FSelType, FDK, FColName, FName, FTableName, FTableAlias, FAction, FROB, FAllowEdited)
VALUES('B04','257800033','FSCStockID',0,2,'S2','Fbase','t_BOS257800033Entry2','u1','(select A.FNumber from t_Stock A
INNER JOIN T_ICITEM B ON A.FItemID=B.FDefaultLoc
WHERE B.Fitemid=u1.Fbase)',0,0)

INSERT INTO ICSelbills (FID, FFieldName, FDstCtlField, FSelType, FDK, FColName, FName, FTableName, FTableAlias, FAction, FROB, FAllowEdited)
VALUES('B04','257800033','FDCSPID',0,0,'P0','Fbase','t_BOS257800033Entry2','u1','(select A.FSPID from t_StockPlace a
inner join T_ICITEM B ON A.FSPID=b.FSPID
WHERE B.Fitemid=u1.Fbase)',0,0)
INSERT INTO ICSelbills (FID, FFieldName, FDstCtlField, FSelType, FDK, FColName, FName, FTableName, FTableAlias, FAction, FROB, FAllowEdited)
VALUES('B04','257800033','FDCSPID',0,1,'P1','Fbase','t_BOS257800033Entry2','u1','(select A.FNAME from t_StockPlace a
inner join T_ICITEM B ON A.FSPID=b.FSPID
WHERE B.Fitemid=u1.Fbase)',0,0)
INSERT INTO ICSelbills (FID, FFieldName, FDstCtlField, FSelType, FDK, FColName, FName, FTableName, FTableAlias, FAction, FROB, FAllowEdited)
VALUES('B04','257800033','FDCSPID',0,2,'P2','Fbase','t_BOS257800033Entry2','u1','(select A.FNUMBER from t_StockPlace a
inner join T_ICITEM B ON A.FSPID=b.FSPID
WHERE B.Fitemid=u1.Fbase)',0,0)

三、最后跟单员查看售服订单平台上收费的任务是否开票，免费的任务与业务员确认，是否完结。来开关闭售服订单。