金蝶K3老单据下推老单据并修改字段名(以销售订单下推销售出库单为例,后台操作部分)

前提条件，在金蝶K3操作界面使用BOS在销售订单和销售出库单增加字段并设置单据下推携带关联

----销售出库单部分

--1 查看相关的数据表,例如销售出库的FHeadTable,FBillPOSHeadTable分别为ICStockBill, ICStockBill_21,则需要修改这两个表的字段

 

SELECT FHeadTable, FBillPOSHeadTable, *

    FROM ICTransactionType

       WHERE FName LIKE '%销售出库%'

 

 

 

--1.1 查看销售出库单新加字段对应的字段名,例如新加追踪号对应的字段FHeadSelfB0157

 

SELECT B1.*

    FROM ICTranSactionType A1

       LEFT JOIN ICTemplate B1 ON A1.FTemplateID=B1.FID

           WHERE A1.FName like '销售出库' AND B1.FCaption LIKE '%追踪号%'

 

 

--2 将销售出库单ICStockBill的字段FHeadSelfB0157改为FJKTraceBillID

 

 

 

 

 

 

 

--3 将销售出库单ICStockBill的字段FHeadSelfB0157改为FJKTraceBillID

 

 

 

--4 需要查询出对应的模板ID,例如查询出销售订单的FTemplateID为:S01,销售出库FTemplateID为:B01

 

SELECT FTemplateID,* FROM ICTranSactionType WHERE FName like '销售订单'    -- S01

SELECT FTemplateID,* FROM ICTranSactionType WHERE FName like '销售出库'    -- B01

 

 

 

--5 根据单据名称查询出单据类型ID,例如查询出销售订单的FID为:-81,销售出库FID为: -21

 

SELECT * FROM ICClassType WHERE FName_CHS LIKE '销售订单'    -- -81

SELECT * FROM ICClassType WHERE FName_CHS LIKE '销售出库'    -- -21

 

--6 根据源单类型ID(-81)和目标单类型ID(-21)查询对应的单据转换流程模板信息，查出字段FFieldName的值,例如查询出销售订单下推销售出库单对应流程的FFieldName为：FOrderInterID

 

SELECT FFieldName,* FROM ICClassLink WHERE FSourClassTypeID=-81 AND FDestClassTypeID=-21       -- FOrderInterID

 

 

 

--7 改注释表ICTemplate的字段, FHeadSelfB0157为原字段名,FJKTraceBillID为修改后的字段名,B01为前面查询出来的销售出库单的模板ID

UPDATE ICTemplate

    SET FFieldName='FJKTraceBillID'

        WHERE FID='B01' AND FFieldName='FHeadSelfB0157'

--7.1 查看修改结果

SELECT *

    FROM ICTemplate

       WHERE FID='B01' AND FFieldName='FJKTraceBillID'

 

 

 

--8 改下推关系表, FHeadSelfB0157为原字段名,FJKTraceBillID为修改后的字段名,B01为前面查询出来的销售出库单的模板ID, FOrderInterID为前面查询出的销售订单下推销售出库单对应流程的FFieldName

 

UPDATE ICSelbills

    SET FDstCtlField='FJKTraceBillID'

WHERE FID='B01' AND FFieldName='FOrderInterID' AND FDstCtlField='FHeadSelfB0157'

--8.1查看修改结果

 

SELECT *

    FROM ICSelbills

WHERE FID='B01' AND FFieldName='FOrderInterID' AND FDstCtlField='FJKTraceBillID'

 

--9 改注释表ICClassTableInfo的字段, FHeadSelfB0157为原字段名,FJKTraceBillID为修改后的字段名, ICStockBill为表名

 

UPDATE ICClassTableInfo

    SET FKey='FJKTraceBillID', FFieldName='FJKTraceBillID'

       WHERE FTableName='ICStockBill' AND FKey='FHeadSelfB0157' AND FFieldName='FHeadSelfB0157'

--9.1查看修改结果

 

SELECT FCaption_CHS, FKey, FFieldName, *

    FROM ICClassTableInfo

       WHERE FTableName='ICStockBill' AND FKey='FJKTraceBillID' AND FFieldName='FJKTraceBillID'

 

 

 

--10 改ICChatBillTitle，将原字段(FHeadSelfB0157)改成目标字段(FJKTraceBillID), FHeadSelfB0157为原字段名,FJKTraceBillID为修改后的字段名

 

UPDATE ICChatBillTitle

    SET FColName='FJKTraceBillID', FName='FJKTraceBillID'

       WHERE FTableName='ICStockBill' AND FColName='FHeadSelfB0157'

--10.1查看修改结果

 

SELECT *

    FROM ICChatBillTitle

       WHERE FTableName='ICStockBill' AND FColName='FJKTraceBillID' AND FName='FJKTraceBillID'

 

 

 

 

 

 

 

 

 

 

 

 

----销售订单单部分

--1 查看相关的数据表,例如销售出库的FHeadTable,FBillPOSHeadTable分别为SEOrder, SEOrder,则需要修改这两个表的字段

SELECT FHeadTable, FBillPOSHeadTable, *

    FROM ICTransactionType

       WHERE FName LIKE '%销售订单%'

 

 

 

--1.1 查看销售出库单新加字段对应的字段名,例如新加追踪号对应的字段FHeadSelfS0153

 

SELECT B1.FCaption, B1.FFieldName, B1.*

    FROM ICTranSactionType A1

       LEFT JOIN ICTemplate B1 ON A1.FTemplateID=B1.FID

           WHERE A1.FName like '销售订单' AND B1.FCaption LIKE '%追踪号%'

 

 

 

--2 将销售订单的字段FHeadSelfS0153改为FJKTraceBillID

 

 

 

 

 

 

 

 

 

--3 改注释表ICTemplate的字段, FHeadSelfS0153为原字段名,FJKTraceBillID为修改后的字段名,S01为前面查询出来的销售出库单的模板ID

 

UPDATE ICTemplate

    SET FFieldName='FJKTraceBillID'

        WHERE FID='S01' AND FFieldName='FHeadSelfS0153'

 

--3.1 查看修改结果

 

SELECT FCaption, FFieldName, *

    FROM ICTemplate

       WHERE FID='S01' AND FFieldName='FJKTraceBillID'

 

--4 改下推关系表, FHeadSelfS0153为原字段名,FJKTraceBillID为修改后的字段名,B01为前面查询出来的销售出库单的模板ID, FOrderInterID为前面查询出的销售订单下推销售出库单对应流程的FFieldName

 

UPDATE ICSelbills

    SET FName='FJKTraceBillID'

       WHERE FID='B01' AND FFieldName='FOrderInterID' AND FName='FHeadSelfS0153'--4.1查看修改结果

 

SELECT *

    FROM ICSelbills

WHERE FID='B01' AND FFieldName='FOrderInterID' AND FDstCtlField='FJKTraceBillID'

 

 

 

--5 改注释表ICClassTableInfo的字段, FHeadSelfS0153为原字段名,FJKTraceBillID为修改后的字段名, SEOrder为表名

 

UPDATE ICClassTableInfo

    SET FKey='FJKTraceBillID', FFieldName='FJKTraceBillID'

       WHERE FTableName='SEOrder' AND FKey='FHeadSelfS0153' AND FFieldName='FHeadSelfS0153'

 

--5.1查看修改结果

 

SELECT FCaption_CHS, FKey, FFieldName, *

    FROM ICClassTableInfo

       WHERE FTableName='SEOrder' AND FKey='FJKTraceBillID' AND FFieldName='FJKTraceBillID'

 

 

 

--6 改ICChatBillTitle，将原字段(FHeadSelfS0153)改成目标字段(FJKTraceBillID), FHeadSelfS0153为原字段名,FJKTraceBillID为修改后的字段名

 

UPDATE ICChatBillTitle

    SET FColName='FJKTraceBillID', FName='FJKTraceBillID'

       WHERE FTableName='SEOrder' AND FColName='FHeadSelfS0153'

 

--6.1查看修改结果

 

SELECT FColCaption, FColName, *

    FROM ICChatBillTitle

       WHERE FTableName='SEOrder' AND FColName='FJKTraceBillID' AND FName='FJKTraceBillID'