transaction notification to block goods receipt po from adding without putting unit price in sap b1

[wale_adegbola]

Good day,

i want query that will mandate users to put the unit price on a GOODS RECEIPT PO without it should not be added except they put the unit Price and also for AR INVOICE.

Hope to get your response.

[impradeepy]

Hi,

-------------Block GRPO when Price is 0------------------------------

If ( (@object_type = '20') and @transaction_type ='A')

BEGIN

if exists

(

SELECT t0.DocEntry FROM PDN1 T0 WHERE isnull(T0.Price,0)=0 AND

T0.DOCENTRY = @list_of_cols_val_tab_del

)

BEGIN

SET @error = 29

SET @error_message = 'Grpo cannot be added if price is 0'

END

END

----------------------------------------------------------------------------------------------------------------------------------

----------Block Invoice when Price is 0------------------------------

If ( (@object_type = '13') and @transaction_type ='A')

BEGIN

if exists

(

SELECT t0.DocEntry FROM INV1 T0 WHERE isnull(T0.Price,0)=0 AND

T0.DOCENTRY = @list_of_cols_val_tab_del

)

BEGIN

SET @error = 30

SET @error_message = 'AR Invoice cannot be added if price is 0'

END

END

------------------------------------------------------------------------------------------------

[wale_adegbola]

Thanks so much for your response. i was able to add the query to the transaction notification and it was successful.

[narayanis]

If Exists(Select INV1.Price From INV1 Where INV1.DocEntry = @list_of_cols_val_tab_del and Isnull(INV1.Price,0)=0)

[narayanis]

closing bracket on the first line is missing. Close it and check

If @Object_Type = '22' and (@Transaction_Type In ('A','U')

[wale_adegbola]

Thanks for your help but it's still giving this error message.

Msg 4145, Level 15, State 1, Procedure SBO_SP_TransactionNotification, Line 27

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

[narayanis]

Hi,

If @Object_Type = '22' and (@Transaction_Type In ('A','U')

Begin

If Exists(Select INV1.Price From INV1 Where INV1.DocEntry = @list_of_cols_val_tab_del and Isnull(INV1.Price,0))

Begin

Select @Error = -22, @Error_Message = 'Unit Price Can Not Be Zero'

End

End

[wale_adegbola]

Thanks for your response but it's still giving this error message again

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@Object_Type".

Msg 137, Level 15, State 2, Line 5

Must declare the scalar variable "@list_of_cols_val_tab_del".

Msg 137, Level 15, State 1, Line 9

Must declare the scalar variable "@Error"

[narayanis]

Can you please share the error message

[wale_adegbola]

error-message-for-transaction-notification-for-nhp.jpg

Find Attach Error message.

[narayanis]

Hi,

Check this

If @Object_Type = '22' And (@Transaction_Type In ('A' , 'U') Then

Declare Cnt int;

Select IsNull(Count(*),0) Into Cnt From PDN1 Where PDN1.Price = 0 And PDN.DocEntry = @list_of_cols_val_tab_del;

if Cnt > 0 then

error := -17;

error_message := 'Unit Price Can Not Be Zero';

End If;

End If;

[wale_adegbola]

Thanks for your response but the query didn't work as it prompted error message.

[FarazMaqsud]

FOR HANA DB:

IF :object_type='20' and (:transaction_type = 'A' or :transaction_type = 'U') then
SELECT COUNT (*) INTO CNT 
			FROM PDN1 T0 
					--LEFT JOIN POR1 T1 ON T0."BaseType" = T1."ObjType" 
					--AND T0."BaseEntry" = T1."DocEntry" 
					--AND T0."BaseLine" = T1."LineNum"
					--INNER JOIN OITM T3  ON T0."ItemCode"=T3."ItemCode"
					--INNER JOIN OPDN T4 ON T0."DocEntry" = T4."DocEntry"
					--INNER JOIN OCRD T5 ON T5."CardCode"=T4."CardCode"
							
							WHERE T0."DocEntry" = :list_of_cols_val_tab_del 
							AND   T0."Price" = 0 ;
							
					IF :CNT>0 then
							error := 2008;
							error_message := 'Please Enter Unit price' ;
							CNT := 0 ;
					END IF ;
					END IF;

[wale_adegbola]

Thanks for your response but i need the one for SQL server not HANA DB.