SAP Business One Pre Transaction Notification SQL Query For Various Business Scenario Example

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--------------------------------------------------------------------
--------------------------------------------------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



----------------------------------------------------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




----------------------------------------------------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-----------------------------------------------------------------------------


---------------------------------------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-----------------------------------------------------------------------------


---------------------------------------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-----------------------------------------------------------------------------


---------------------------------------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-----------------------------------------------------------------------------


---------------------------------------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-----------------------------------------------------------------------------


---------------------------------------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-----------------------------------------------------------------------------


---------------------------------------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






----------------------------------------------------END-----------------------------------------------------------------------------