SAP Stored Procedure - Block Document Add If...

[Former Member]

Hey there,

Trying to code in some business rules that I thought would be simple. I guess I was wrong, haha.

Basically, trying to stop the addition of a Sales Delivery if the business partner and warehouse meet certain criteria...

IF @object_type = '15' AND @transaction_type = 'A'

BEGIN

  IF (SELECT CardCode from ODLN  where CardCode = @list_of_cols_val_tab_del) IN ('BUSINESSPARTNER1','BUSINESSPARTNER2')

  BEGIN

  SELECT @error = -2425

  SELECT @error_message = N'Failure!'

  END

  END

This doesn't work at all, doesn't seem to check the document being added.

Help please?

Thanks in advance!

[Johan_H]

Hi Kamron,

The @list_of_cols_val_tab_del variable will return the DocEntry of the document.

Please try this:

IF @object_type = '15' AND @transaction_type = 'A'

BEGIN

  IF (SELECT CardCode from ODLN  where DocEntry = @list_of_cols_val_tab_del) IN ('BUSINESSPARTNER1','BUSINESSPARTNER2')

  BEGIN

  SELECT @error = -2425

  SELECT @error_message = N'Failure!'

  END

  END

Regards,

Johan

[Former Member]

Johan,

That worked great, and used what you wrote here to expand towards a fully working solution!

The final step is getting the order to check each line item for the Warehouse codes.

Here's what I have that works great for single line item orders, but when it is more than 1 line item, it fails because it's not looping each line item. Basically if the BP and Warehouses listed are on the order, it shouldn't be added.

--

IF @object_type = '15' AND @transaction_type = 'A'

BEGIN

  IF (SELECT CardCode from ODLN where DocEntry = @list_of_cols_val_tab_del) IN (BUSINESSPARTER1','BUSINESSPARTER2') AND (SELECT WhsCode from DLN1 where DocEntry = @list_of_cols_val_tab_del) IN ('WAREHOUSE1','WAREHOUSE2')

  BEGIN

  SELECT @error = -84302

  SELECT @error_message = N'You cannot add a Delivery for a US Customer with a Canadian Warehouse!'

  END

  END

--

Thanks,

Kamron

[Johan_H]

Hi Kamron,

Yep, an IN check is always single IN many and you are trying (potentially) many IN many.

No problem, the nice thing about checks in the WHERE clause is that you can turn them around, and because you are only checking for two warehouses, we can just split that into two separate checks:

Give this a try:

IF @object_type = '15' AND @transaction_type = 'A'

BEGIN

  IF (SELECT CardCode

      FROM ODLN

      WHERE DocEntry = @list_of_cols_val_tab_del) IN ('BUSINESSPARTER1','BUSINESSPARTER2')

    AND ('WAREHOUSE1' IN (select distinct WhsCode

                         from DLN1

                 where DocEntry = @list_of_cols_val_tab_del)

  OR 'WAREHOUSE2' IN (select distinct WhsCode

                             from DLN1

                     where DocEntry = @list_of_cols_val_tab_del))

  BEGIN

  SELECT @error = -84302

  SELECT @error_message = N'You cannot add a Delivery for a US Customer with a Canadian Warehouse!'

  END

  END

Regards,

Johan

[narayanis]

Hi,

if @object_type = 15 and @transaction_type = 'A'

Declare @minline22 int

Declare @maxline22 int

begin

  Set @minline22 = (select min(T0.linenum) from DLN1 T1 where

  T1.docentry=@list_of_cols_val_tab_del)

  set @maxline22 = (select max(T1.linenum) from DLN1 T1 where

  T0.docentry=@list_of_cols_val_tab_del)

   IF (SELECT CardCode from ODLN where DocEntry = @list_of_cols_val_tab_del) IN

   (BUSINESSPARTER1','BUSINESSPARTER2') AND (SELECT WhsCode from DLN1 where DocEntry =

   @list_of_cols_val_tab_del) IN ('WAREHOUSE1','WAREHOUSE2')

   BEGIN

          SELECT @error = -84302

          SELECT @error_message = N'You cannot add a Delivery for a US Customer with a Canadian

          Warehouse!'

  END

Set @minline22 = @minline22+1

  End

End

Hope this helps you.

Regards

Narayani