SP_Notification Error

[gabinaya]

Hi,

Please any help me about below stored procedure. I added pricelist =1. But I need add Pricelist 1 & 3.

How to add pricelist 1 & 3 in the below stored Procedure
IF :OBJECT_TYPE = '17' AND (:TRANSACTION_TYPE ='A' OR :TRANSACTION_TYPE ='U')

THEN

DECLARE LineNum NVARCHAR;

select MAX (T1."LineNum") INTO LineNum FROM RDR1 T1 INNER JOIN ORDR T0 ON T0."DocEntry" = T1."DocEntry"

inner join ITM1 T2 on T1."ItemCode" = T2."ItemCode" and T2."PriceList" = '1'

Left outer join OITM T3 ON T3."ItemCode" = T1."ItemCode"

WHERE T1."DocEntry" = :list_of_cols_val_tab_del and T1."PriceBefDi" < T2."Price" AND T0."UserSign" not in ('1','119','120');

IF :LineNum != ''

THEN

error := 160901;

error_message := Concat(N'Not allow to reduce the Base Prices for the Items Applied by management. Please Contact Management, Please Check Line Item - ', LineNum+1);

CNT3 =0;

END IF;

END IF;

[azizelmir · Accepted solution]

so remove '1' and keep '3'

and T2."PriceList" in ('1','3').

[azizelmir]

Yes sure but now it takes both price list 1 AED and PriceList 3 for USD.

So What else you need from this SP ?

[gabinaya]

Item Base price AED 1.250 (PriceList = 1) and USD 0.340 (PriceList =3)

Now : if i make SO with AED 1.250 - No error / I make SO with USD 0.340 - Error

My Requirements: user are not allowed to reduce the base price accourding SO item price.

[azizelmir]

Try this modification by using EXISTS:

IF :OBJECT_TYPE = '17' AND (:TRANSACTION_TYPE ='A' OR :TRANSACTION_TYPE ='U')

BEGIN

	IF EXISTS( 
	
			select *  FROM RDR1 T1
			INNER JOIN ORDR T0 ON T0."DocEntry" = T1."DocEntry"
			inner join ITM1 T2 on T1."ItemCode" = T2."ItemCode" and T2."PriceList" in ('1','3')
			Left outer join OITM T3 ON T3."ItemCode" = T1."ItemCode"
			WHERE T1."DocEntry" = :list_of_cols_val_tab_del and T1."PriceBefDi" < T2."Price" 
			and T3."ItmsGrpCod" not in ('124') and T0."UserSign" not in ('119','120');	
				)
      		begin    				
				error := 160901;
				error_message := Concat(N'Not allow to reduce the Base Prices for the Items Applied by management. Please Contact Management, Please Check Line Item - ', LineNum+1);
			End
 
END

[gabinaya]

same error for USD Price

[azizelmir]

Maybe something missing in your code!

Did you test if it is working for only price list '1' ?

[gabinaya]

No it is working both price-list 1 and price-list 3 separate query, if i add together into one query it's consider only pricelist 1

[azizelmir]

Please try :

T2."PriceList" =IN('1',3')

[gabinaya]

IF :OBJECT_TYPE = '17' AND (:TRANSACTION_TYPE ='A' OR :TRANSACTION_TYPE ='U')

THEN

DECLARE LineNum NVARCHAR (200);

select MAX(T1."LineNum") INTO LineNum FROM RDR1 T1

INNER JOIN ORDR T0 ON T0."DocEntry" = T1."DocEntry"

inner join ITM1 T2 on T1."ItemCode" = T2."ItemCode" and T2."PriceList" in ('1','3')

Left outer join OITM T3 ON T3."ItemCode" = T1."ItemCode"

WHERE T1."DocEntry" = :list_of_cols_val_tab_del and T1."PriceBefDi" < T2."Price" and T3."ItmsGrpCod" not in ('124') and T0."UserSign" not in ('119','120');

IF :LineNum != ''

THEN

error := 160901;

error_message := Concat(N'Not allow to reduce the Base Prices for the Items Applied by management. Please Contact Management, Please Check Line Item - ', LineNum+1);

CNT3 =0;

END IF;

END IF;

T2."PriceList" = 1 (AED Price list)

T2."PriceList" = 3 (USD Price list)

I tried this query but it's not working

[narayanis]

Hi,

Try in instead of T2."PriceList" = '1'

Regards

[gabinaya]

i need add to both 1 & 3