需求：借出单转销售，在U8系统中是自动生成发货单，销售出库单并自动审核，有发货记录无销售订单记录，需求有销售订单记录。在借出转换单上做触发器时，发现借出转换单表HY_DZ_BorrowOutChange无法操作，不知道什么原因。所以采用存储过程定时执行，采用此语句，在发货单中整单关联能关联到销售订单。语句如下：
ALTER PROCEDURE P_Quck_JCZX
WITH ENCRYPTION
AS
DECLARE @cSOCode NVARCHAR(500); --销售订单号
DECLARE @ID INT; --主表ID
DECLARE @iSOsID INT; --销售订单子表标识2
DECLARE @iFatherId INT; --单据编号生成控制表中主表ID流水号
DECLARE @iChildId INT; --单据编号生成控制表中子表ID流水号
DECLARE @Total INT; --发货单表体行数
DECLARE @iRowNo INT; --行号
DECLARE @cDLCode NVARCHAR(500); --发货单号
DECLARE @err INT; --错误数
SET @err = 0;
SET @cDLCode =
(
SELECT TOP 1
cDLCode
FROM dbo.DispatchList
WHERE ISNULL(cSourceCode, '') <> ''
AND ISNULL(cDefine1, '') = ''
AND dDate>'2022-03-01'
ORDER BY DLID
);--每次取一条数据执行，在自定义项1中打上标记，作为条件判断，发货单表头中cSourceCode字段为借出转换单号，
--此字段不为空说明是从借出转换单生成的发货单，自定义项1打上标记说明已经执行生成销售订单
SET @cSOCode = N'SCZX' + CAST(DATEDIFF(SECOND, '1970-01-01 08:00:00', dateadd(hh,-1,getdate())) AS NVARCHAR(100));
SET @ID =
(
SELECT '1' + RIGHT('000000000' + CAST(
(
SELECT iFatherId + 1
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '998'
AND cVouchType = 'Somain'
) AS NVARCHAR), 9)
);
SET @iSOsID =
(
SELECT '1' + RIGHT('000000000' + CAST(
(
SELECT iChildId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '998'
AND cVouchType = 'Somain'
) AS NVARCHAR), 9)
);
SET @iFatherId =
(
SELECT iFatherId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '998'
AND cVouchType = 'Somain'
);
SET @iChildId =
(
SELECT iChildId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '998'
AND cVouchType = 'Somain'
);
SET @Total =
(
SELECT MAX(irowno)
FROM dbo.DispatchList
JOIN dbo.DispatchLists
ON DispatchLists.DLID = DispatchList.DLID
AND DispatchList.cDLCode = @cDLCode
);
BEGIN TRANSACTION;
--插入销售订单主表
INSERT INTO dbo.SO_SOMain
(
cSTCode, --销售类型编码
dDate, --单据日期
cSOCode, --销售订单号
cCusCode, --客户编码
cDepCode, --部门编码
cPersonCode, --业务员编码
cCusOAddress, --发货地址
cexch_name, --币种名称
iExchRate, --汇率
iTaxRate, --表头税率
iStatus, --状态（0-未审核；1-已审核）
cMaker, --制单人
cVerifier, --审核人
cCusName, --客户名称
ID, --销售订单主表标识
iVTid, --单据模版号
cBusType, --业务类型
dPreMoDateBT, --预完工日期
dPreDateBT, --预发货日期
caddcode, --发货地址编码
iverifystate, --审核状态
iswfcontrolled, --是否控制工作流
dverifydate, --审核日期
dcreatesystime, --制单时间
dverifysystime, --审核时间
cinvoicecompany, --开票单位编码
cMemo --备注
)
SELECT cSTCode,
dateadd(hh,-1,dateadd(hh,-1,getdate())), --时间往前推一个小时
@cSOCode,
cCusCode,
cDepCode,
cPersonCode,
cShipAddress,
cexch_name,
iExchRate,
iTaxRate,
1,
cMaker,
cVerifier,
cCusName,
@ID,
--131447,
95,
cBusType,
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
N'0001',
0,
0,
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
cinvoicecompany,
'借出转销售生单'
FROM dbo.DispatchList
WHERE cDLCode = @cDLCode;
SET @err = @err + @@ERROR;
--插入销售订单子表
SET @iRowNo = 1;
WHILE @iRowNo <= @Total
BEGIN
INSERT INTO dbo.SO_SODetails
(
--AutoID, --销售订单子表标识
cSOCode, --销售订单号
cInvCode, --存货编码
dPreDate, --预发货日期
iQuantity, --数量
iUnitPrice, --原币无税单价
iTaxUnitPrice, --原币含税单价
iMoney, --原币无税金额
iTax, --原币税额
iSum, --原币价税合计
iDisCount, --原币折扣额
iNatUnitPrice, --本币无税单价
iNatMoney, --本币无税金额
iNatTax, --本币税额
iNatSum, --本币价税合计
iNatDisCount, --本币折扣额
iFHQuantity, --累计发货数量
iFHMoney, --累计原币发货金额
iSOsID, --销售订单子表标识2
KL, --扣率
KL2, --二次扣率
cInvName, --存货名称
iTaxRate, --税率
ID, --销售订单主表标识
dPreMoDate, --预完工日期
iRowNo, --行号
foutquantity, --出库数量
idemandtype, --需求跟踪方式（1-销售订单行号；4-需求分类号5-销售订单号）
fVeriDispQty, --已审核订货数量
fVeriDispSum, --已审核订货原币金额
bsaleprice --报价含税标识
)
SELECT @cSOCode,
cInvCode,
dateadd(hh,-1,getdate()),
iQuantity,
iUnitPrice,
iTaxUnitPrice,
iMoney,
iTax,
iSum,
0.0000,
iNatUnitPrice,
iNatMoney,
iNatTax,
iNatSum,
0.0000,
iQuantity,
iSum,
@iSOsID + @iRowNo,
KL,
KL2,
cInvName,
iTaxRate,
@ID,
dateadd(hh,-1,getdate()),
@iRowNo,
iQuantity,
1,
iQuantity,
iSum,
1
FROM dbo.DispatchLists
WHERE DLID =
(
SELECT DLID FROM DispatchList WHERE cDLCode = @cDLCode
)
AND DispatchLists.irowno = @iRowNo;
SET @err = @err + @@ERROR;
UPDATE dbo.DispatchList
SET cSOCode = @cSOCode,
cDefine1 = @cSOCode
WHERE cDLCode = @cDLCode;
SET @err = @err + @@ERROR;
UPDATE dbo.DispatchLists
SET cSoCode = @cSOCode,
cordercode=@cSOCode,--该字段不写入，后续参照做发票时选择不到发货单
iSOsID = @iSOsID + @iRowNo
WHERE DLID =
(
SELECT DLID FROM dbo.DispatchList WHERE cDLCode = @cDLCode
)
AND DispatchLists.irowno = @iRowNo;
SET @iRowNo = @iRowNo + 1;
SET @err = @err + @@ERROR;
END;
--更新单据表头表体ID号
UPDATE UFSystem.dbo.UA_Identity
SET iFatherId = @iFatherId + 1,
iChildId = @iChildId + @Total
WHERE cAcc_Id = '998'
AND cVouchType = 'Somain';
SET @err = @err + @@ERROR;
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION;
END;
ELSE
BEGIN
COMMIT TRANSACTION;
END;
---苏州
ALTER PROCEDURE P_Quck_JCZX
WITH ENCRYPTION
AS
DECLARE @cSOCode NVARCHAR(500); --销售订单号
DECLARE @ID INT; --主表ID
DECLARE @iSOsID INT; --销售订单子表标识2
DECLARE @iFatherId INT; --单据编号生成控制表中主表ID流水号
DECLARE @iChildId INT; --单据编号生成控制表中子表ID流水号
DECLARE @Total INT; --发货单表体行数
DECLARE @iRowNo INT; --行号
DECLARE @cDLCode NVARCHAR(500); --发货单号
DECLARE @err INT; --错误数
SET @err = 0;
SET @cDLCode =
(
SELECT TOP 1
cDLCode
FROM dbo.DispatchList
WHERE ISNULL(cSourceCode, '') <> ''
AND ISNULL(cDefine1, '') = ''
AND dDate>'2022-03-01'
ORDER BY DLID
);--每次取一条数据执行，在自定义项1中打上标记，作为条件判断，发货单表头中cSourceCode字段为借出转换单号，
--此字段不为空说明是从借出转换单生成的发货单，自定义项1打上标记说明已经执行生成销售订单
SET @cSOCode = N'SCZX' + CAST(DATEDIFF(SECOND, '1970-01-01 08:00:00', dateadd(hh,-1,getdate())) AS NVARCHAR(100));
SET @ID =
(
SELECT '1' + RIGHT('000000000' + CAST(
(
SELECT iFatherId + 1
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '001'
AND cVouchType = 'Somain'
) AS NVARCHAR), 9)
);
SET @iSOsID =
(
SELECT '1' + RIGHT('000000000' + CAST(
(
SELECT iChildId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '001'
AND cVouchType = 'Somain'
) AS NVARCHAR), 9)
);
SET @iFatherId =
(
SELECT iFatherId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '001'
AND cVouchType = 'Somain'
);
SET @iChildId =
(
SELECT iChildId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '001'
AND cVouchType = 'Somain'
);
SET @Total =
(
SELECT MAX(irowno)
FROM dbo.DispatchList
JOIN dbo.DispatchLists
ON DispatchLists.DLID = DispatchList.DLID
AND DispatchList.cDLCode = @cDLCode
);
BEGIN TRANSACTION;
--插入销售订单主表
INSERT INTO dbo.SO_SOMain
(
cSTCode, --销售类型编码
dDate, --单据日期
cSOCode, --销售订单号
cCusCode, --客户编码
cDepCode, --部门编码
cPersonCode, --业务员编码
cCusOAddress, --发货地址
cexch_name, --币种名称
iExchRate, --汇率
iTaxRate, --表头税率
iStatus, --状态（0-未审核；1-已审核）
cMaker, --制单人
cVerifier, --审核人
cCusName, --客户名称
ID, --销售订单主表标识
iVTid, --单据模版号
cBusType, --业务类型
dPreMoDateBT, --预完工日期
dPreDateBT, --预发货日期
caddcode, --发货地址编码
iverifystate, --审核状态
iswfcontrolled, --是否控制工作流
dverifydate, --审核日期
dcreatesystime, --制单时间
dverifysystime, --审核时间
cinvoicecompany, --开票单位编码
cMemo --备注
)
SELECT cSTCode,
dateadd(hh,-1,dateadd(hh,-1,getdate())), --时间往前推一个小时
@cSOCode,
cCusCode,
cDepCode,
cPersonCode,
cShipAddress,
cexch_name,
iExchRate,
iTaxRate,
1,
cMaker,
cVerifier,
cCusName,
@ID,
--131447,
95,
cBusType,
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
N'0001',
0,
0,
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
cinvoicecompany,
'借出转销售生单'
FROM dbo.DispatchList
WHERE cDLCode = @cDLCode;
SET @err = @err + @@ERROR;
--插入销售订单子表
SET @iRowNo = 1;
WHILE @iRowNo <= @Total
BEGIN
INSERT INTO dbo.SO_SODetails
(
--AutoID, --销售订单子表标识
cSOCode, --销售订单号
cInvCode, --存货编码
dPreDate, --预发货日期
iQuantity, --数量
iUnitPrice, --原币无税单价
iTaxUnitPrice, --原币含税单价
iMoney, --原币无税金额
iTax, --原币税额
iSum, --原币价税合计
iDisCount, --原币折扣额
iNatUnitPrice, --本币无税单价
iNatMoney, --本币无税金额
iNatTax, --本币税额
iNatSum, --本币价税合计
iNatDisCount, --本币折扣额
iFHQuantity, --累计发货数量
iFHMoney, --累计原币发货金额
iSOsID, --销售订单子表标识2
KL, --扣率
KL2, --二次扣率
cInvName, --存货名称
iTaxRate, --税率
ID, --销售订单主表标识
dPreMoDate, --预完工日期
iRowNo, --行号
foutquantity, --出库数量
idemandtype, --需求跟踪方式（1-销售订单行号；4-需求分类号5-销售订单号）
fVeriDispQty, --已审核订货数量
fVeriDispSum, --已审核订货原币金额
bsaleprice --报价含税标识
)
SELECT @cSOCode,
cInvCode,
dateadd(hh,-1,getdate()),
iQuantity,
iUnitPrice,
iTaxUnitPrice,
iMoney,
iTax,
iSum,
0.0000,
iNatUnitPrice,
iNatMoney,
iNatTax,
iNatSum,
0.0000,
iQuantity,
iSum,
@iSOsID + @iRowNo,
KL,
KL2,
cInvName,
iTaxRate,
@ID,
dateadd(hh,-1,getdate()),
@iRowNo,
iQuantity,
1,
iQuantity,
iSum,
1
FROM dbo.DispatchLists
WHERE DLID =
(
SELECT DLID FROM DispatchList WHERE cDLCode = @cDLCode
)
AND DispatchLists.irowno = @iRowNo;
SET @err = @err + @@ERROR;
UPDATE dbo.DispatchList
SET cSOCode = @cSOCode,
cDefine1 = @cSOCode
WHERE cDLCode = @cDLCode;
SET @err = @err + @@ERROR;
UPDATE dbo.DispatchLists
SET cSoCode = @cSOCode,
cordercode = @cSOCode,
iSOsID = @iSOsID + @iRowNo
WHERE DLID =
(
SELECT DLID FROM dbo.DispatchList WHERE cDLCode = @cDLCode
)
AND DispatchLists.irowno = @iRowNo;
SET @iRowNo = @iRowNo + 1;
SET @err = @err + @@ERROR;
END;
--更新单据表头表体ID号
UPDATE UFSystem.dbo.UA_Identity
SET iFatherId = @iFatherId + 1,
iChildId = @iChildId + @Total
WHERE cAcc_Id = '001'
AND cVouchType = 'Somain';
SET @err = @err + @@ERROR;
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION;
END;
ELSE
BEGIN
COMMIT TRANSACTION;
END;
---南通
ALTER PROCEDURE P_Quck_JCZX
WITH ENCRYPTION
AS
DECLARE @cSOCode NVARCHAR(500); --销售订单号
DECLARE @ID INT; --主表ID
DECLARE @iSOsID INT; --销售订单子表标识2
DECLARE @iFatherId INT; --单据编号生成控制表中主表ID流水号
DECLARE @iChildId INT; --单据编号生成控制表中子表ID流水号
DECLARE @Total INT; --发货单表体行数
DECLARE @iRowNo INT; --行号
DECLARE @cDLCode NVARCHAR(500); --发货单号
DECLARE @err INT; --错误数
SET @err = 0;
SET @cDLCode =
(
SELECT TOP 1
cDLCode
FROM dbo.DispatchList
WHERE ISNULL(cSourceCode, '') <> ''
AND ISNULL(cDefine1, '') = ''
AND dDate>'2022-03-01'
ORDER BY DLID
);--每次取一条数据执行，在自定义项1中打上标记，作为条件判断，发货单表头中cSourceCode字段为借出转换单号，
--此字段不为空说明是从借出转换单生成的发货单，自定义项1打上标记说明已经执行生成销售订单
SET @cSOCode = N'SCZX' + CAST(DATEDIFF(SECOND, '1970-01-01 08:00:00', dateadd(hh,-1,getdate())) AS NVARCHAR(100));
SET @ID =
(
SELECT '1' + RIGHT('000000000' + CAST(
(
SELECT iFatherId + 1
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '004'
AND cVouchType = 'Somain'
) AS NVARCHAR), 9)
);
SET @iSOsID =
(
SELECT '1' + RIGHT('000000000' + CAST(
(
SELECT iChildId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '004'
AND cVouchType = 'Somain'
) AS NVARCHAR), 9)
);
SET @iFatherId =
(
SELECT iFatherId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '004'
AND cVouchType = 'Somain'
);
SET @iChildId =
(
SELECT iChildId
FROM UFSystem.dbo.UA_Identity
WHERE cAcc_Id = '004'
AND cVouchType = 'Somain'
);
SET @Total =
(
SELECT MAX(irowno)
FROM dbo.DispatchList
JOIN dbo.DispatchLists
ON DispatchLists.DLID = DispatchList.DLID
AND DispatchList.cDLCode = @cDLCode
);
BEGIN TRANSACTION;
--插入销售订单主表
INSERT INTO dbo.SO_SOMain
(
cSTCode, --销售类型编码
dDate, --单据日期
cSOCode, --销售订单号
cCusCode, --客户编码
cDepCode, --部门编码
cPersonCode, --业务员编码
cCusOAddress, --发货地址
cexch_name, --币种名称
iExchRate, --汇率
iTaxRate, --表头税率
iStatus, --状态（0-未审核；1-已审核）
cMaker, --制单人
cVerifier, --审核人
cCusName, --客户名称
ID, --销售订单主表标识
iVTid, --单据模版号
cBusType, --业务类型
dPreMoDateBT, --预完工日期
dPreDateBT, --预发货日期
caddcode, --发货地址编码
iverifystate, --审核状态
iswfcontrolled, --是否控制工作流
dverifydate, --审核日期
dcreatesystime, --制单时间
dverifysystime, --审核时间
cinvoicecompany, --开票单位编码
cMemo --备注
)
SELECT cSTCode,
dateadd(hh,-1,dateadd(hh,-1,getdate())), --时间往前推一个小时
@cSOCode,
cCusCode,
cDepCode,
cPersonCode,
cShipAddress,
cexch_name,
iExchRate,
iTaxRate,
1,
cMaker,
cVerifier,
cCusName,
@ID,
--131447,
95,
cBusType,
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
N'0004',
0,
0,
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
dateadd(hh,-1,getdate()),
cinvoicecompany,
'借出转销售生单'
FROM dbo.DispatchList
WHERE cDLCode = @cDLCode;
SET @err = @err + @@ERROR;
--插入销售订单子表
SET @iRowNo = 1;
WHILE @iRowNo <= @Total
BEGIN
INSERT INTO dbo.SO_SODetails
(
--AutoID, --销售订单子表标识
cSOCode, --销售订单号
cInvCode, --存货编码
dPreDate, --预发货日期
iQuantity, --数量
iUnitPrice, --原币无税单价
iTaxUnitPrice, --原币含税单价
iMoney, --原币无税金额
iTax, --原币税额
iSum, --原币价税合计
iDisCount, --原币折扣额
iNatUnitPrice, --本币无税单价
iNatMoney, --本币无税金额
iNatTax, --本币税额
iNatSum, --本币价税合计
iNatDisCount, --本币折扣额
iFHQuantity, --累计发货数量
iFHMoney, --累计原币发货金额
iSOsID, --销售订单子表标识2
KL, --扣率
KL2, --二次扣率
cInvName, --存货名称
iTaxRate, --税率
ID, --销售订单主表标识
dPreMoDate, --预完工日期
iRowNo, --行号
foutquantity, --出库数量
idemandtype, --需求跟踪方式（1-销售订单行号；4-需求分类号5-销售订单号）
fVeriDispQty, --已审核订货数量
fVeriDispSum, --已审核订货原币金额
bsaleprice --报价含税标识
)
SELECT @cSOCode,
cInvCode,
dateadd(hh,-1,getdate()),
iQuantity,
iUnitPrice,
iTaxUnitPrice,
iMoney,
iTax,
iSum,
0.0000,
iNatUnitPrice,
iNatMoney,
iNatTax,
iNatSum,
0.0000,
iQuantity,
iSum,
@iSOsID + @iRowNo,
KL,
KL2,
cInvName,
iTaxRate,
@ID,
dateadd(hh,-1,getdate()),
@iRowNo,
iQuantity,
1,
iQuantity,
iSum,
1
FROM dbo.DispatchLists
WHERE DLID =
(
SELECT DLID FROM DispatchList WHERE cDLCode = @cDLCode
)
AND DispatchLists.irowno = @iRowNo;
SET @err = @err + @@ERROR;
UPDATE dbo.DispatchList
SET cSOCode = @cSOCode,
cDefine1 = @cSOCode
WHERE cDLCode = @cDLCode;
SET @err = @err + @@ERROR;
UPDATE dbo.DispatchLists
SET cSoCode = @cSOCode,
cordercode = @cSOCode,
iSOsID = @iSOsID + @iRowNo
WHERE DLID =
(
SELECT DLID FROM dbo.DispatchList WHERE cDLCode = @cDLCode
)
AND DispatchLists.irowno = @iRowNo;
SET @iRowNo = @iRowNo + 1;
SET @err = @err + @@ERROR;
END;
--更新单据表头表体ID号
UPDATE UFSystem.dbo.UA_Identity
SET iFatherId = @iFatherId + 1,
iChildId = @iChildId + @Total
WHERE cAcc_Id = '004'
AND cVouchType = 'Somain';
SET @err = @err + @@ERROR;
IF @err <> 0
BEGIN
ROLLBACK TRANSACTION;
END;
ELSE
BEGIN
COMMIT TRANSACTION;
END;
1030
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
