常规单据控制套餐
大约 4 分钟
方案说明
- 方案集合B1系统常规企业经常用的单据控制代码
- 方案控制点考虑适用于通用型企业或者项目
- 方案控制不断更新补充完善
示例代码
------------------------------------------------------- 主数据部分 -------------------------------------------------------
----业务伙伴主数据：业务伙伴名称不能重复
IF @object_type IN ( '2' )
AND ( @transaction_type = 'A'
OR @transaction_type = 'u'
)
BEGIN
DECLARE @cardname NVARCHAR(100)--客户名称
DECLARE @cardtype NVARCHAR(1)--业务伙伴类型
DECLARE @count1 NVARCHAR(10)
DECLARE @KEY NVARCHAR(100)
SELECT @cardname = t1.CardName
FROM OCRD t1
WHERE t1.CardCode = @list_of_cols_val_tab_del
SELECT @cardtype = t1.CardType
FROM OCRD t1
WHERE t1.CardCode = @list_of_cols_val_tab_del
SELECT @count1 = COUNT(t2.CardCode)
FROM OCRD t2
WHERE t2.CardName = @cardname
AND t2.CardType = @cardtype
IF @count1 > 1
BEGIN
SET @error = 1
SET @error_message = N'业务伙伴名称:''' + @cardname
+ N'''业务伙伴已存在，请检查!'
END
END
----业务伙伴主数据：客户名称不能为空
IF @object_type = '2'
BEGIN
IF @transaction_type = 'A'
OR @transaction_type = 'U'
BEGIN
DECLARE @cardname1 nvarchar(100);
DECLARE @cardType1 nvarchar(100);
SELECT @cardname1 = T0.[CardName]
FROM OCRD T0
WHERE t0.CardCode = @list_of_cols_val_tab_del;
SELECT @cardType1 = T0.[CardType]
FROM OCRD T0
WHERE t0.CardCode = @list_of_cols_val_tab_del;
IF @cardname1 IS NULL
AND @cardType1 = 'C'
BEGIN
SET @error = 1;
SET @error_message = '客户名称不能为空！';
END;
END;
END
------------------------------------------------------- 销售部分 -------------------------------------------------------
IF @object_type = '15'
BEGIN
IF @transaction_type = 'A'
BEGIN
IF EXISTS (
SELECT T1.[BaseEntry]
FROM ODLN T0
INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[BaseEntry] IS NULL
AND T0.DocEntry = @list_of_cols_val_tab_del
)
BEGIN
SET @error = 151;
SET @error_message = '销售交货单必须有来源单据于订单';
END;
END;
END
----备注：控制同样适用于预收款发票，因为预收款发票的单据清单也是203
----预收款申请单：控制必须有来源单据
IF @object_type = '203'
BEGIN
IF @transaction_type = 'A'
OR @transaction_type = 'U'
BEGIN
IF EXISTS (
SELECT T1.[BaseEntry]
FROM ODPI T0
INNER JOIN DPI1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[BaseEntry] IS NULL
AND T0.DOCENTRY = @list_of_cols_val_tab_del
)
BEGIN
SET @error = 203;
SET @error_message = '预收款单必须有来源单据，不能直接添加 !';
END;
END;
END
------------------------------------------------------- 采购部分 -------------------------------------------------------
----采购订单：单价必须填写
IF @object_type='22'
begin
if @transaction_type='A' OR @transaction_type = 'U'
begin
if exists(
SELECT T1.[Price] FROM OPOR T0 INNER JOIN POR1 T1
ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[Price] is null or T1.[Price] <= 0 and T1.[DocEntry]=@list_of_cols_val_tab_del
)
begin
select @error=1
select @error_message='订单单价必须填写'
end
end
end
-------预付款不能超订单总价
IF @object_type='204'
begin
if @transaction_type='A' OR @transaction_type = 'U'
begin
if exists(
SELECT 1 FROM OPOR T0 INNER JOIN
(
SELECT T1.[BaseEntry],SUM(T0.[DocTotal]) as 'yf' FROM ODPO T0 INNER JOIN DPO1 T1 ON T0.[DocEntry] = T1.[DocEntry] GROUP BY T1.[BaseEntry]
) TA ON ta.baseentry=t0.docentry INNER JOIN DPO1 T2 ON T2.[BaseEntry]=t0.docentry WHERE T0.[DocTotal]-ta.yf<0
AND t2.DocEntry=@list_of_cols_val_tab_del
)
begin
select @error=1
select @error_message='预付款不能超订单总价'
end
end
end
if @object_type = '22' --服务采购订单限制必须填写科目
begin
if @transaction_type='A' OR @transaction_type = 'U'
begin
if exists(
SELECT T1.[AcctCode] FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[DocType] ='S' AND T1.[AcctCode] IS NULL AND T1.DocEntry = @list_of_cols_val_tab_del
)
begin
select @error=1
select @error_message='服务采购订单限制必须填写科目!'
end
end
end
----采购收货单：必须有来源单据
IF @object_type = '20'
BEGIN
IF @transaction_type = 'A'
OR @transaction_type = 'U'
BEGIN
IF EXISTS (
SELECT T1.[BaseEntry]
FROM OPDN T0
INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[BaseEntry] IS NULL
AND T0.DocEntry = @list_of_cols_val_tab_del
)
BEGIN
SET @error = 201;
SET @error_message = '采购收货单必须有来源单据于订单';
END;
END;
END
----采购退货单：单价必须填写
IF @object_type='21'
begin
if @transaction_type='A' OR @transaction_type = 'U'
begin
if exists(
SELECT T1.[Price] FROM ORPD T0 INNER JOIN RPD1 T1
ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[Price] is null or T1.[Price] <= 0 and T1.[DocEntry]=@list_of_cols_val_tab_del
)
begin
select @error=1
select @error_message='采购退货单单价必须填写'
end
end
end
------------------------------------------------------- 财务部分 -------------------------------------------------------
----应收发票：单据来源必须有来源单据
IF @object_type = '13'
BEGIN
IF @transaction_type = 'A'
BEGIN
IF EXISTS (
SELECT T1.[BaseEntry]
FROM OINV T0
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[BaseEntry] IS NULL
AND T0.DocEntry = @list_of_cols_val_tab_del
)
BEGIN
SET @error = 131;
SET @error_message = '应收发票必须有来源单据 ！';
END;
END;
END
----应收发票：物料类发票不能直接复制从销售订单
IF @object_type = '13'
BEGIN
IF @transaction_type = 'A'
BEGIN
IF EXISTS (
SELECT T1.[BaseEntry]
FROM OINV T0
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[DocType]='I' AND T1.[BaseType]='17'
AND T0.DocEntry = @list_of_cols_val_tab_del
)
BEGIN
SET @error = 132;
SET @error_message = '物料类应收发票不能直接复制从销售订单！';
END;
END;
END
----应付发票：单据来源必须有来源单据
IF @object_type = '18'
BEGIN
IF @transaction_type = 'A'
BEGIN
IF EXISTS (
SELECT T1.[BaseEntry]
FROM OINV T0
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[BaseEntry] IS NULL
AND T0.DocEntry = @list_of_cols_val_tab_del
)
BEGIN
SET @error = 181;
SET @error_message = '应收发票必须有来源单据 ！';
END;
END;
END
----应付发票：物料类应收发票不能直接复制从采购订单
IF @object_type = '18'
BEGIN
IF @transaction_type = 'A'
BEGIN
IF EXISTS (
SELECT T1.[BaseEntry]
FROM OINV T0
INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T0.[DocType]='I' AND T1.[BaseType]='22'
AND T0.DocEntry = @list_of_cols_val_tab_del
)
BEGIN
SET @error = 182;
SET @error_message = '物料类应收发票不能直接复制从采购订单！';
END;
END;
END
------------------------------------------------------- 仓库部分 -------------------------------------------------------
----库存收货单：单价必须填写
IF @object_type='59'
begin
if @transaction_type='A' OR @transaction_type = 'U'
begin
if exists(
SELECT T0.[Price] FROM IGN1 T0 INNER JOIN OIGN T1
ON T0.[DocEntry] = T1.[DocEntry] WHERE T0.[Price] is null or T0.[Price]<=0 and T0.[DocEntry]=@list_of_cols_val_tab_del
)
begin
select @error=59-1
select @error_message='库存收货单单价必须填写'
end
end
end