Following are the list of various frequently used notification to block entry in cases, hope these list will help you.
------------------------------------------------------------Block Purchase Order without Purchase Request-----------------------------
IF @object_type = N'22' AND ( @transaction_type = N'A' )
BEGIN
if exists (SELECT DocEntry
from POR1
where DocEntry=@list_of_cols_val_tab_del and ( ISNULL(baseref,'')='' And BaseType <> '1470000113' )and (BaseType= '-1' and agrno is null) )
BEGIN
SELECT @Error = 2
SELECT @error_message = 'Please Create Purchase Request First'
END
END
--------------------------------------------------------------END--------------------------------------------------------------------
If @object_type = '46' and @transaction_type in ( 'A','U')
BEGIN
declare @C nvarchar(16)
set @C=(Select isnull(I.CheckNum,'')
From vpm1 I
inner join ovpm j on j.docnum=i.docnum where j.docEntry=@list_of_cols_val_tab_del )
If @C!='' and exists
(Select I.CheckNum From vpm1 I
inner join ovpm J on j.docnum=i.docnum
Where I.CheckNum=@C and J.DocEntry!=@list_of_cols_val_tab_del)
Select @error =1, @error_message = 'Same Cheque Number Already Exists'
END
------------------------------------------------------------Block Purchase Order without Purchase Request-----------------------------
IF @object_type = N'22' AND ( @transaction_type = N'A' )
BEGIN
if exists (SELECT DocEntry
from POR1
where DocEntry=@list_of_cols_val_tab_del and ( ISNULL(baseref,'')='' And BaseType <> '1470000113' )and (BaseType= '-1' and agrno is null) )
BEGIN
SELECT @Error = 2
SELECT @error_message = 'Please Create Purchase Request First'
END
END
--------------------------------------------------------------END--------------------------------------------------------------------
--------------------------------------------------Block GRPO VALUE MISMATCH WITH PO Start---------------------------------
IF @object_type=N'20' AND (@transaction_type=N'A' OR @transaction_type=N'U')
BEGIN
DECLARE @DOCTOTAL DECIMAL(19,2)
SELECT @DOCTOTAL = OPDN.DocTotal - (SELECT DocTotal FROM OPOR WHERE OPOR.DocEntry = PDN1.BaseEntry AND OPOR.ObjType = PDN1.BaseType) FROM OPDN INNER JOIN PDN1 ON OPDN.DocEntry = PDN1.DocEntry
WHERE
OPDN.DocEntry =@list_of_cols_val_tab_del
IF(@DOCTOTAL > 0)
BEGIN
SET @error=1
SET @error_message='GRPO VALUE MISMATCH WITH PO !!!'
END
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block GRPO Price should not be greater than PO Price---------------------------------
IF @transaction_type = 'A' AND @object_type = '20'
BEGIN
if exists(Select T0.Price from [dbo].[PDN1] T0
inner join POR1 on T0.BaseEntry=POR1.DocEntry and T0.BaseLine =Por1.LineNum
Where T0.Price>POR1.Price
AND T0.DocEntry=@list_of_cols_val_tab_del)
begin
SELECT @error = 1, @error_message = 'GRPO Price should not be greater than PO Price!'
end
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Create PO first--------------------------------------------------------
IF @transaction_type = 'A' AND @object_type = '18'
BEGIN
if exists(Select T0.ItemCode from [dbo].[PCH1] T0
inner join OPCH T1 on T0.docentry = T1.DocEntry
Where T0.BaseEntry is null
And T1.DocType = 'I'
AND T0.DocEntry=@list_of_cols_val_tab_del)
begin
SELECT @error = 1, @error_message = 'Create PO first!'
end
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Purchase Order Project Code UPDATE for Header------------------------------------------------------
IF @object_type = N'22' AND ( @transaction_type = N'A' OR @transaction_type = N'U')
BEGIN
update OPOR
set Project=c.AsseType
from OPOR a,
POR1 b,
OLCT c
where a.DocEntry=b.DocEntry
and a.DocEntry=@list_of_cols_val_tab_del
and b.LocCode=c.Code
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Purchase Order Project Code UPDATE for LineItem-----------------------------------------------------
IF @object_type = N'22' AND ( @transaction_type = N'A' OR @transaction_type = N'U')
BEGIN
update POR1
set Project=c.AsseType
from OPOR a,
POR1 b,
OLCT c
where a.DocEntry=b.DocEntry
and a.DocEntry=@list_of_cols_val_tab_del
and b.LocCode=c.Code
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Project Code not be blank on outgoing payment-----------------------------------------------------
IF @object_type = N'46' AND ( @transaction_type = N'A')
BEGIN
if exists (SELECT PrjCode from ovpm where DocEntry =@list_of_cols_val_tab_del and (PrjCode is null or PrjCode =''))
BEGIN
SELECT @Error = 2
SELECT @error_message = 'Project Code should not be Blank!'
END
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Batches Quarantine Date is not Passed----------------------------------------------------
IF (@transaction_type IN ('A','U') AND @object_type = '17')
BEGIN
DECLARE @BatchNum Varchar(Max)
Select @BatchNum = stuff((select ',' + CONVERT(VarChar(Max), ISNULL(T0.BatchNum,''))
From IBT1 T0 Inner Join OIBT ON T0.ItemCode = OIBT.ItemCode And T0.BatchNum = OIBT.BatchNum-- And T0.WhsCode = OIBT.WhsCode
Inner Join RDR1 ON RDR1.ItemCode = T0.ItemCode
Inner Join ORDR on ORDR.DocEntry = RDR1.DocEntry
Where T0.BaseEntry = RDR1.DocEntry
And T0.BaseType = RDR1.ObjType And ORDR.DocEntry = @list_of_cols_val_tab_del
And DATEDIFF(DD,oibt.U_QtrDate,ordr.DocDate)<0
for xml path('')),1,1,'')
if @BatchNum IS NOT NULL
BEGIN
if @BatchNum <> ''
BEGIN
SELECT @Error = 9090
SELECT @error_message = 'Batches Quarantine Date is not passed '
END
END
End
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Check Number Update on incoming Payment ----------------------------------------------------
IF @object_type = N'24' AND ( @transaction_type = N'A' OR @transaction_type = N'U')
BEGIN
Declare @chknum as Varchar(30)
Set @chknum = (SELECT RCT1.CHECKNUM FROM RCT1 INNER JOIN ORCT ON ORCT.DocEntry=RCT1.DocNum AND ORCT.DOCENTRY=@list_of_cols_val_tab_del)
BEGIN
UPDATE OJDT SET ojdt.ref3= @chknum
FROM OJDT A, ORCT B
WHERE B.TRANSID=A.TRANSID
AND B.DOCENTRY=@list_of_cols_val_tab_del
END
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Part number without Blank spaces for Item----------------------------------------------------
IF @transaction_type in ('U', 'A' ) AND @object_type = '4'
BEGIN
If exists
(select T0.Frgnname ,T0.Itemcode , T0.Itemname
from OITM T0
WHERE T0.ItemCode =@list_of_cols_val_tab_del and CHARINDEX(' ', T0.[Frgnname]) > 0
)
SET @error = 401-01
SET @error_message = N'!!Pls Update Part Number without Blank Spaces!'
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Back Date Entry after Specific Date----------------------------------------------------
IF @transaction_type in (N'A',N'U') and @object_type = N'60'
BEGIN
IF EXISTS
(SELECT T0.[DocEntry] FROM OIGE T0 INNER JOIN OUSR T1 ON T0.UserSign = T1.INTERNAL_K
WHERE
((T0.[DocDate] < T1.[U_BK_DATE]) AND DocEntry = @list_of_cols_val_tab_del))
BEGIN
SELECT @error=1,
@error_message = 'Posting Date Is Not in Permissable Range, Contact Manager'
END
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Future Date Posting Document----------------------------------------------------
IF @transaction_type in (N'A') and @object_type = N'59'
BEGIN
IF EXISTS
(SELECT DocEntry FROM [dbo].[OIGN]
WHERE
((createdate < taxdate)AND DocEntry = @list_of_cols_val_tab_del))
BEGIN
SELECT @error=1,
@error_message = 'You Are Posting This Document In Future Date !!!!!'
END
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Received quantity more than Planned----------------------------------------------------
IF (@object_type = '59' AND @transaction_type= 'A')
and (Select top 1 i.BaseType from IGN1 i
Where i.DocEntry = @list_of_cols_val_tab_del)=202
BEGIN
if exists
(Select p.DocEntry
From OWOR p
Where
p.DocEntry=(select top 1 i.BaseEntry from IGN1 i where i.DocEntry = @list_of_cols_val_tab_del)
and p.CmpltQty > p.PlannedQty)
Begin
set @error =1
set @error_message = 'You can not Receieve more than planned !'
End
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Receipt of Production Order Already Exists----------------------------------------------------
If @object_type = '59' and @transaction_type in ( 'A','U')
BEGIN
declare @B nvarchar(16)
set @B=(Select isnull(I.BaseRef,'') From IGN1 I
Where I.DocEntry=@list_of_cols_val_tab_del)
If @B!='' and exists
(Select I.BaseRef From IGN1 I
Where I.BaseRef=@B and I.DocEntry!=@list_of_cols_val_tab_del)
Select @error =1, @error_message = 'Receipt of Production Order already Exists'
END
BEGIN
declare @B nvarchar(16)
set @B=(Select isnull(I.BaseRef,'') From IGN1 I
Where I.DocEntry=@list_of_cols_val_tab_del)
If @B!='' and exists
(Select I.BaseRef From IGN1 I
Where I.BaseRef=@B and I.DocEntry!=@list_of_cols_val_tab_del)
Select @error =1, @error_message = 'Receipt of Production Order already Exists'
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block ISSUE from Production when Quantity is greater than the 5% of planned Quantity in Production Order ----------------------------------------------------
IF @object_type = '60' AND @transaction_type in ('A','U')
and (Select top 1 i.BaseType from IGE1 i
Where i.DocEntry = @list_of_cols_val_tab_del)=202
BEGIN
if exists
(Select p.DocEntry
From wor1 p
LEFT OUTER JOIN IGE1 I ON I.baseentry = p.DocEntry and i.itemcode=p.itemcode
Where
p.DocEntry=(select top 1 i.BaseEntry from IGE1 i where i.DocEntry = @list_of_cols_val_tab_del)
and p.PlannedQty+p.PlannedQty*0.05<i.Quantity)
Begin
set @error =1
set @error_message = 'You can not issue more than 5% of planned !'
End
END
and (Select top 1 i.BaseType from IGE1 i
Where i.DocEntry = @list_of_cols_val_tab_del)=202
BEGIN
if exists
(Select p.DocEntry
From wor1 p
LEFT OUTER JOIN IGE1 I ON I.baseentry = p.DocEntry and i.itemcode=p.itemcode
Where
p.DocEntry=(select top 1 i.BaseEntry from IGE1 i where i.DocEntry = @list_of_cols_val_tab_del)
and p.PlannedQty+p.PlannedQty*0.05<i.Quantity)
Begin
set @error =1
set @error_message = 'You can not issue more than 5% of planned !'
End
END
----------------------------------------------------END-----------------------------------------------------------------------------
--------------------------------------------------Block Duplicate Cheque Number ----------------------------------------------------
If @object_type = '46' and @transaction_type in ( 'A','U')
BEGIN
declare @C nvarchar(16)
set @C=(Select isnull(I.CheckNum,'')
From vpm1 I
inner join ovpm j on j.docnum=i.docnum where j.docEntry=@list_of_cols_val_tab_del )
If @C!='' and exists
(Select I.CheckNum From vpm1 I
inner join ovpm J on j.docnum=i.docnum
Where I.CheckNum=@C and J.DocEntry!=@list_of_cols_val_tab_del)
Select @error =1, @error_message = 'Same Cheque Number Already Exists'
END
----------------------------------------------------END-----------------------------------------------------------------------------
IF @object_type = '59' AND (@transaction_type='A' or @transaction_type='U')
BEGIN
IF EXISTS (SELECT T0.DOCENTRY FROM dbo.IGN1 T0 WHERE T0.DOCENTRY = @list_of_cols_val_tab_del)
BEGIN
DECLARE @entry INT
SELECT @entry = T0.BASEENTRY FROM dbo.IGN1 T0 WHERE T0.DOCENTRY = @list_of_cols_val_tab_del
IF EXISTS (SELECT T1.ITEMCODE, T1.PLANNEDQTY, T2.QUANTITY, T2.BASEENTRY,t3.docdate,t5.docdate
FROM dbo.OWOR T0
INNER JOIN dbo.WOR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT OUTER JOIN dbo.IGE1 T2 ON T2.BASEENTRY = T0.DOCENTRY AND T1.ITEMCODE = T2.ITEMCODE
left outer join dbo.oige t3 on t2.docentry=t3.docentry
left outer join dbo.ign1 t4 on t4.baseentry=t0.docentry
left outer join dbo.oign t5 on t5.docentry=t4.docentry
WHERE T3.docdate > t5.docdate AND T0.DOCENTRY = @entry)
SELECT @Error = 1, @error_message = 'Date in Receipt of Production should not before the Date of Issue from Production '
END
END
---------------------------------------Block on Docdate of Receipt of Production is before the issue of production ----------------------------------------------------
IF @object_type = '59' AND (@transaction_type='A' or @transaction_type='U')
BEGIN
IF EXISTS (SELECT T0.DOCENTRY FROM dbo.IGN1 T0 WHERE T0.DOCENTRY = @list_of_cols_val_tab_del)
BEGIN
DECLARE @entry INT
SELECT @entry = T0.BASEENTRY FROM dbo.IGN1 T0 WHERE T0.DOCENTRY = @list_of_cols_val_tab_del
IF EXISTS (SELECT T1.ITEMCODE, T1.PLANNEDQTY, T2.QUANTITY, T2.BASEENTRY,t3.docdate,t5.docdate
FROM dbo.OWOR T0
INNER JOIN dbo.WOR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT OUTER JOIN dbo.IGE1 T2 ON T2.BASEENTRY = T0.DOCENTRY AND T1.ITEMCODE = T2.ITEMCODE
left outer join dbo.oige t3 on t2.docentry=t3.docentry
left outer join dbo.ign1 t4 on t4.baseentry=t0.docentry
left outer join dbo.oign t5 on t5.docentry=t4.docentry
WHERE T3.docdate > t5.docdate AND T0.DOCENTRY = @entry)
SELECT @Error = 1, @error_message = 'Date in Receipt of Production should not before the Date of Issue from Production '
END
END
----------------------------------------------------END-----------------------------------------------------------------------------
If @transaction_type IN('A','U') AND (@Object_type = N'4')
begin
declare @ForeginName nvarchar(100)
Select @ForeginName = T1.FrgnName from OITM T1
Where T1.ItemCode = @list_of_cols_val_tab_del AND ISNULL(T1.FrgnName,'') <> ''
IF Exists (SELECT Top 1 1 FROM OITM T0 WHERE T0.ItemCode <> @list_of_cols_val_tab_del AND T0.FrgnName IN(@ForeginName) AND ISNULL(T0.FrgnName,'') <> '')
BEGIN
Select -9912, 'Duplicate Foreign Name/Bin Card not allow in Item Master Data.'
Return
END
end
---------------------------------------Block Duplicate Foreign Name not allow in Item Master Data ----------------------------------------------------
If @transaction_type IN('A','U') AND (@Object_type = N'4')
begin
declare @ForeginName nvarchar(100)
Select @ForeginName = T1.FrgnName from OITM T1
Where T1.ItemCode = @list_of_cols_val_tab_del AND ISNULL(T1.FrgnName,'') <> ''
IF Exists (SELECT Top 1 1 FROM OITM T0 WHERE T0.ItemCode <> @list_of_cols_val_tab_del AND T0.FrgnName IN(@ForeginName) AND ISNULL(T0.FrgnName,'') <> '')
BEGIN
Select -9912, 'Duplicate Foreign Name/Bin Card not allow in Item Master Data.'
Return
END
end
----------------------------------------------------END-----------------------------------------------------------------------------
If @transaction_type IN('A','U') AND (@Object_type = N'204')
begin
IF EXISTS (SELECT TOP 1 T0.BaseEntry FROM DPO1 T0 WHERE T0.DocEntry = @list_of_cols_val_tab_del and T0.BaseEntry is null)
BEGIN
SELECT @error = 5002, @error_message = N'Can not select item manually. Copy items from PO.'
Return
END
end
---------------------------------------Block Select Item Manually ----------------------------------------------------
If @transaction_type IN('A','U') AND (@Object_type = N'204')
begin
IF EXISTS (SELECT TOP 1 T0.BaseEntry FROM DPO1 T0 WHERE T0.DocEntry = @list_of_cols_val_tab_del and T0.BaseEntry is null)
BEGIN
SELECT @error = 5002, @error_message = N'Can not select item manually. Copy items from PO.'
Return
END
end
----------------------------------------------------END-----------------------------------------------------------------------------
If @transaction_type = N'A' AND (@Object_type = N'22')
begin
if exists (SELECT T0.docnum FROM [dbo].OPOR T0
INNER JOIN [dbo].POR1 T1 ON T1.DocEntry = T0.DocEntry WHERE t0.SlpCode = -1 and T0.DOCENTRY = @list_of_cols_val_tab_del)
BEGIN
select @Error = 28, @error_message = 'Specify Sales Employee Name'
Select @Error,@error_message
Return
END
end
---------------------------------------Block Empty Sales Employee Entry----------------------------------------------------
If @transaction_type = N'A' AND (@Object_type = N'22')
begin
if exists (SELECT T0.docnum FROM [dbo].OPOR T0
INNER JOIN [dbo].POR1 T1 ON T1.DocEntry = T0.DocEntry WHERE t0.SlpCode = -1 and T0.DOCENTRY = @list_of_cols_val_tab_del)
BEGIN
select @Error = 28, @error_message = 'Specify Sales Employee Name'
Select @Error,@error_message
Return
END
end
----------------------------------------------------END-----------------------------------------------------------------------------
If (@Object_type = N'204' and @transaction_type ='A')
begin
select @c= count(distinct b.BaseEntry)
from ODPO a inner join DPO1 b on a.DocEntry =b.DocEntry
and a.DocEntry =@list_of_cols_val_tab_del
if @c>1
begin
select @Error = -5002, @error_message = 'Multiple Purchase Orders are not allowed'
Select @Error,@error_message
Return
end
end
---------------------------------------Block Multiple Sales order in single Down Payment----------------------------------------------------
If (@Object_type = N'204' and @transaction_type ='A')
begin
select @c= count(distinct b.BaseEntry)
from ODPO a inner join DPO1 b on a.DocEntry =b.DocEntry
and a.DocEntry =@list_of_cols_val_tab_del
if @c>1
begin
select @Error = -5002, @error_message = 'Multiple Purchase Orders are not allowed'
Select @Error,@error_message
Return
end
end
----------------------------------------------------END-----------------------------------------------------------------------------
If (@Object_type = N'22' and @transaction_type ='L')
begin
if exists(
select top 1 1
from OPOR a
inner join POR1 b on a.DocEntry =b.DocEntry
inner join DPO1 c on c.BaseEntry =b.DocEntry
and c.BaseLine =b.LineNum
and cast (c.BaseType as nvarchar(30)) =b.ObjType
and c.TargetType <> '19'
inner join odpo d on c.DocEntry =d.DocEntry
and d.CANCELED <>'Y'
where a.DocEntry=@list_of_cols_val_tab_del
)
begin
select @Error = '-50009', @error_message = 'Can not Close Purchase Order manually when Open Downpayment exists.'
Select @Error,@error_message
end
end
---------------------------------------Block Can not Close Purchase Order manually when Open Down payment Exists----------------------------------------------------
If (@Object_type = N'22' and @transaction_type ='L')
begin
if exists(
select top 1 1
from OPOR a
inner join POR1 b on a.DocEntry =b.DocEntry
inner join DPO1 c on c.BaseEntry =b.DocEntry
and c.BaseLine =b.LineNum
and cast (c.BaseType as nvarchar(30)) =b.ObjType
and c.TargetType <> '19'
inner join odpo d on c.DocEntry =d.DocEntry
and d.CANCELED <>'Y'
where a.DocEntry=@list_of_cols_val_tab_del
)
begin
select @Error = '-50009', @error_message = 'Can not Close Purchase Order manually when Open Downpayment exists.'
Select @Error,@error_message
end
end
----------------------------------------------------END-----------------------------------------------------------------------------
---------------------------------------Block manual entry Select From CFL List----------------------------------------------------
If @object_type = '18' And @transaction_type IN ('A','U')
Begin
if exists( Select top 1 1 From OPCH T0 where (isnull(T0.[U_VATGRP],'0') <> '' or T0.[U_VATGRP] is not null) And T0.DocEntry = @list_of_cols_val_tab_del)
begin
if not exists (Select top 1 1
From OPCH T0
WHERE T0.DocEntry = @list_of_cols_val_tab_del
AND T0.[U_VATGRP] IN (SELECT Expense_Group FROM [dbo].[@VAT_EXPGRP_LIST])
)
Begin
Select -50009, 'Enter **[Item Description(VAT)]** as Its mandatory. Please Choose from List only.'
return
End
End
end
Begin
if exists( Select top 1 1 From OPCH T0 where (isnull(T0.[U_VATGRP],'0') <> '' or T0.[U_VATGRP] is not null) And T0.DocEntry = @list_of_cols_val_tab_del)
begin
if not exists (Select top 1 1
From OPCH T0
WHERE T0.DocEntry = @list_of_cols_val_tab_del
AND T0.[U_VATGRP] IN (SELECT Expense_Group FROM [dbo].[@VAT_EXPGRP_LIST])
)
Begin
Select -50009, 'Enter **[Item Description(VAT)]** as Its mandatory. Please Choose from List only.'
return
End
End
end
----------------------------------------------------END-----------------------------------------------------------------------------
