SP TN to block document if item cost is 0 for specific WH (A/R Invoice/A/R Credit memo/GRPO)

[Former Member]

Hi,

Is it possible to block a document (A/R Invoice/A/R Credit memo/GRPO) if found the item cost of the part for the WH is 0.

Can this be done through SP TN?

E.g:

Document: A/R Credit memo A

Content: "Item" | "Qty" | "WH" | "Item Cost"

a) ABC | 10 | WH001 | 0

b) BCD | 20 | WH001 | 20

Result: User can create the  A/R Credit memo A.

Document: A/R Credit memo B

Content: "Item" | "Qty" | "WH" | "Item Cost"

a) ABC | 10 | WH002 | 0

b) BCD | 20 | WH003 | 0

Result: User cannot create the document until the item cost for ABC & BCD in WH002 & WH003 were given.

Thanks

[former_member186712 · Accepted solution]

Hi Olga,

Is there any connection between the ABC and BCD item?

Do you have a BOM for this items?

Regards

[Former Member · Accepted solution]

Hi Augusto,

There is no relation between ABC & BCD.

Items will have BOM.

May I know the reason behind your question? How will it influence on the TN?

Thanks ^^

[former_member186712 · Accepted solution]

The reason of my question how to connect both items.

If the items are hardcoded

try the below SP

IF @OBJECT_TYPE = '14' AND (@TRANSACTION_TYPE = 'A' or @TRANSACTION_TYPE = 'U')

  BEGIN

   IF EXISTS(
    SELECT T1.AvgPrice FROM RIN1 T0
  left outer join OITW T1 on T1.ItemCode= 'BCD'
    where  IsNull(T1.AvgPrice, 0) = 0 and T0.Price = 0 and T0.DocEntry = @list_of_cols_val_tab_del)
   BEGIN
    SELECT @ERROR=1,@ERROR_MESSAGE='Please insert the price !'
  END
end

-- Select the return values
select @error, @error_message

end

This SP sees if BCD has a average price 0 and the price on the credit note line = 0 gives an error.

I'm not sure if this is what you need.

Regards,

[Former Member · Accepted solution]

Hi Augusto,

The item cannot be hardcoded, because there are many items.

The main control is based on the WH.

Eg.

WH001 = All items can have items cost or 0 item cost.

WH002 = All items must have items cost

WH003 = All items must have items cost.

I had tried to modify from your query, it seem it will work.

IF @OBJECT_TYPE = '14' AND (@TRANSACTION_TYPE = 'A' or @TRANSACTION_TYPE = 'U')

  BEGIN

   IF EXISTS(

    SELECT T3.AvgPrice FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode

    where  T1.[WhsCode] IN ('WH002','WH003') and T3.AvgPrice = 0 and T1.DocEntry = @list_of_cols_val_tab_del)

   BEGIN

    SELECT @ERROR=1,@ERROR_MESSAGE='Please check the item cost !'

  END

end

Thanks

[former_member186712 · Accepted solution]

Hi Olga,

I'm glad to help.

Please close the thread and if you wish mark the answers has Helpful answer or Correct answer.

Regards.

[Former Member · Accepted solution]

Hi Augusto,

Sorry, my bad.

At first, I tested the item in WH002 with no item cost, it will block and notify the user.

Then the item in WH002 was given an item cost, it will block too.

Can you help again?

Thanks

[former_member186712 · Accepted solution]

Hi Olga,

IF @OBJECT_TYPE = '14' AND (@TRANSACTION_TYPE = 'A' or @TRANSACTION_TYPE = 'U')

  BEGIN

   IF EXISTS(

    SELECT T3.AvgPrice FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode

    where  T1.[WhsCode] IN ('WH002','WH003') and T3.AvgPrice = 0 and T1.DocEntry = @list_of_cols_val_tab_del)

   BEGIN

    SELECT @ERROR=1,@ERROR_MESSAGE='Please check the item cost !'

  END

end

I'm going to translate what is in this query.

If a user creates a Credit Note to the warehouse WH002 or WH003

If the avgprice in one warehouse is 0 (Warehouse 01, WH001, WH002, WH003 and all others warehouses in the system)

if will give the error message

Is this what you need?

I'm still not sure about your needs so write in words what you need.

Regards

[Former Member · Accepted solution]

Hi Augusto,

Thank you for the prompt replied.

What I need is it only check the avgprice for the hardcoded WH in query and match with the document WH, not all the WH in the systems.

Condition A:

If a user creates a Credit Note to the warehouse WH002

If the avgprice of WH002 is 0. it will give the error message

If avgprice of WH002 is not 0. it will allow user to create the document. (even WH003 is 0, only check the WH of the document based on the query)

Condition B:

If a user creates a Credit Note to the warehouse WH002 and  WH003

If the avgprice in one of the warehouse is 0 (WH002, WH003 a) it will give the error message.

It only allow user to create the document if both the avgprice in warehouse is not 0.

Thanks

[former_member186712 · Accepted solution]

Hi Olga,

Try this:

IF @OBJECT_TYPE = '14' AND (@TRANSACTION_TYPE = 'A' or @TRANSACTION_TYPE = 'U')

  BEGIN

   IF EXISTS(

    SELECT T3.AvgPrice FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode and T3.[WhsCode] IN ('WH002','WH003')

    where  T1.[WhsCode] IN ('WH002','WH003') and T3.AvgPrice = 0 and T1.DocEntry = @list_of_cols_val_tab_del)

   BEGIN

    SELECT @ERROR=1,@ERROR_MESSAGE='Please check the item cost !'

  END

end

[Former Member · Accepted solution]

Hi Augusto,

I had tested, it seem that it will block WH002 & WH003 too even item has item cost.

Can you confirm?

Thanks

[former_member186712 · Accepted solution]

Hi,

I dont think so because the instruction

and T3.AvgPrice = 0 means that the Average price has to be 0 to have an error.

Regards

[Former Member · Accepted solution]

Hi Augusto,

You are right, maybe I am blur just now.

It works, thanks a lot for your time and effort.

Good luck.

Thanks