Skip to Content
0
Former Member
Sep 26, 2011 at 08:57 AM

Query for approval

112 Views

I have updated SBO_SP_TransactionNotification for validating sales order.The query is as follows -

if @object_type = '17' and @transaction_type IN ('A') 
begin 
		
        declare curItem  cursor  for Select PriceBefDi,ItemCode,SeriesName FROM 
	RDR1 INNER JOIN ORDR ON RDR1.DocEntry=ORDR.DocEntry 
        INNER JOIN OUSR ON OUSR.UserId=ORDR.UserSign 
	INNER JOIN NNM1 ON NNM1.Series=ORDR.Series 
	WHERE ORDR.DocEntry=@list_of_cols_val_tab_del AND USER_Code<>'manager'
 	declare @dblPrice numeric(10,2),@dblItemPrice numeric(10,2)
  	declare @strItemCode varchar(20),@strSeriesName as varchar(40)
	open curItem 
        fetch next from curItem into @dblPrice,@strItemCode,@strSeriesName
  	while @@fetch_status = 0 
	begin			     	
		SET @dblItemPrice=(select price from itm1 where itemcode=@strItemCode and pricelist=(select listnum from opln where listname='Sales price list' ))
		if @dblItemPrice>0 and charindex('DEM',@strSeriesName)=0
		begin			
			if @dblPrice<@dblItemPrice AND charindex('REP',@strSeriesName)=0
			begin
				set @error =1
				set @error_message = 'The price of the item ' + @strItemCode + ' should be equivalent to sales price list i.e.' + cast(@dblItemPrice as varchar)
			end
			else
			begin
				if @dblPrice>@dblItemPrice 
				begin
					set @error =1
					set @error_message = 'The price of the replacement item ' + @strItemCode + ' should not be greater than sales price list i.e.' + cast(@dblItemPrice as varchar)
				end
			end
		end
		fetch next from curItem into @dblPrice,@strItemCode,@strSeriesName
	end
	close curItem 
 	deallocate curItem
end

This stored procedure is working correctly as per the requirement.Sales order can not be added if the price is less than sales price list.

Now I want to remove this validation and add approval for this.If the rule is violated ,then the document will go for approval.In SAP I have created query for approval as,

SELECT DISTINCT 'True'  FROM OITM  WHERE ITEMCODE=$[$38.1]  AND U_Discount_Percent < $[$38.15]
UNION
SELECT (CASE WHEN $[$38.14]<>(select price from itm1 where itemcode=$[$38.1] and pricelist=(select listnum from opln where listname='Sales price list' )) 
THEN 'True' ELSE 'False'            END)

Here I am getting the price as varchar.Is it possible to call stored procedure SBO_SP_TransactionNotification from query ?