Stored Procedure for Restricting Part Number without Blank Spaces while Adding/Updating an 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

Stored Procedure for Blocking Wrong Vendor Code/Customer Code Creation in BP Master Data

------------------------------------Wrong Vendor Code Creation in BP Master Data------------------------------------------------


If @object_type = '2' and @transaction_type in ( 'A','U')



BEGIN

If exists (select CardType , CardName, CardCode from OCRD

where CardType in ('S') and CardCode like 'C%'

and CardCode = @list_of_cols_val_tab_del



)

Select @error = 420002-1,

@error_message = 'Wrong Vendor Code Created.Pl Create appropriate Vendor Code for Vendor'



End

------------------------------------Wrong Customer Code Creation in BP Master Data------------------------------------------------


If @object_type = '2' and @transaction_type in ( 'A','U')



BEGIN

If exists (select CardType , CardName, CardCode from OCRD

where CardType in ('C') and CardCode like 'V%'

and CardCode = @list_of_cols_val_tab_del



)

Select @error = 420002-2,

@error_message = 'Wrong Customer Code Created.Pl Create appropriate Customer Code for Customer. '



End

Stored Procedure - Blocking Negative Discounts for SQ, SO, PO

-------------------------------Negative Discount in SQ-----------------------------------------------------------------------
 If @object_type = '23' and @transaction_type in ( 'A','U')



BEGIN
 If exists

(Select P.DocEntry, P.DocCur From OQUT P INNER JOIN QUT1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and P.DocCur = 'INR' and Q.DiscPrcnt < 0 )

Select @error = 2310000-01,

@error_message = 'Discount(%) should not be Negative. Pl Check Discount/Total Sales Price in SQ'



end
 -------------------------------Negative Discount in SO-----------------------------------------------
 If @object_type = '17' and @transaction_type in ( 'A','U')



BEGIN
 If exists

(Select P.DocEntry, P.DocCur From ORDR P INNER JOIN RDR1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and P.DocCur = 'INR' and Q.DiscPrcnt < 0 )

Select @error = 1710000-01,

@error_message = 'Discount(%) should not be Negative. Pl Check Discount/Total Sales Price in SO'



end
 -------------------------------Negative Discount in PO-------------------------------------------------
 If @object_type = '22' and @transaction_type in ( 'A','U')



BEGIN
 If exists

(Select P.DocEntry, P.DocCur From OPOR P INNER JOIN POR1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and P.DocCur = 'INR' and Q.DiscPrcnt < 0 )

Select @error = 2210000-01,

@error_message = 'Discount(%) should not be Negative. Pl Check Discount/Total(LC) in PO'



end

Stored Procedure - Owner Name Mandatory for All Purchase & Sales Documents

-----------------------------------SQ-OWner Name Mandatory--------------------------------------------------
If @object_type = '23' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From OQUT P INNER JOIN QUT1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 2300001-01,

@error_message = 'Please Set Owner Name for SQ'



end
-------------------------------------SO-OWner Name Mandatory---------------------------------------------------
If @object_type = '17' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From ORDR P INNER JOIN RDR1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 1700001-01,

@error_message = 'Please Set Owner Name for SO'



end
----------------------------------------------------------------------------------------------------

-------------------------------------Delivery - Owner Name Mandatory---------------------------------------------------------------------
If @object_type = '15' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From ODLN P INNER JOIN DLN1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 151-01,

@error_message = 'Please Set Owner Name for Delivery'



end
-------------------------------------Return - Owner Name Mandatory----------------------------------------------------------------------
If @object_type = '16' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From ORDN P INNER JOIN RDN1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 161-01,

@error_message ='Please Set Owner Name for Return'



end
-------------------------------------A/R Invoice - Owner Name Mandatory-----------------------------------------------------------------
If @object_type = '13' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From OINV P INNER JOIN INV1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 131-01,

@error_message ='Please Set Owner Name for A/R Invoice'



end
-------------------------------------A/R Cr.memo - Owner Name Mandatory----------------------------------------------------------------
If @object_type = '14' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From ORIN P INNER JOIN RIN1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 141-01,

@error_message ='Please Set Owner Name for A/R Cr.memo'



end
-------------------------------------PO - Owner Name Mandatory-------------------------------------------------------------------------------
If @object_type = '22' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From OPOR P INNER JOIN POR1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 221-01,

@error_message ='Please Set Owner Name for PO'



end
-------------------------------------GRPO - Owner Name Mandatory-----------------------------------------------------------------------
If @object_type = '20' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From OPDN P INNER JOIN PDN1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 201-01,

@error_message ='Please Set Owner Name for GRPO'



end
-------------------------------------Goods Return - Owner Name Mandatory----------------------------------------------------------------
If @object_type = '21' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From ORPD P INNER JOIN RPD1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 211,

@error_message ='Please Set Owner Name for Goods Return'



end


-------------------------------------AP Invoice - Owner Name Mandatory--------------------------------------------------------------------
If @object_type = '18' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From OPCH P INNER JOIN PCH1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 181-01,

@error_message ='Please Set Owner Name for AP Invoice'



end
-------------------------------------AP Cr.memo - Owner Name Mandatory--------------------------------------------------------------------
If @object_type = '19' and @transaction_type in ( 'A','U')



BEGIN
If exists (Select P.DocEntry, P.DocCur From ORPC P INNER JOIN RPC1 Q on P.DocEntry = Q.DocEntry

Where Q.DocEntry=@list_of_cols_val_tab_del

and isnull(P.OwnerCode ,'') = '' )

Select @error = 191-01,

@error_message ='Please Set Owner Name for AP Cr.memo'



end
--------------------------------------------------------------------------------------------------------------------------------------------