on 09-22-2014 10:31 AM
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
Hi Olga,
Is there any connection between the ABC and BCD item?
Do you have a BOM for this items?
Regards
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
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
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
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
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
Hi,
Try:
IF @OBJECT_TYPE = '13' AND @TRANSACTION_TYPE IN ('A', 'U')
BEGIN
IF EXISTS(
SELECT T1.AvgPrice FROM INV1 T0
join OITW T1 on T1.ItemCode= T0.ItemCode AND T1.WhsCode= T0.WhsCode AND T0.WhsCode IN ('WH002','WH003')
where IsNull(T1.AvgPrice, 0) = 0 AND T0.DocEntry = @list_of_cols_val_tab_del)
Begin
SELECT @ERROR=13,@ERROR_MESSAGE='Item cost is 0!'
End
END
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
I have modified Mr.Augusto Silva query to only for warehouse WH002 & WH003
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 T0.[WhsCode] IN ('WH002','WH003') and 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
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.