Skip to Content
author's profile photo Former Member
Former Member

Query for approval

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 ?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 26, 2011 at 12:23 PM

    Hi,

    Approval can only be at header level or first line item. Is that ok with you?

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2011 at 05:09 AM

    Hi Dilip,

    I have tried the following query for approval :

    SELECT 'TRUE' 
    FROM OITM JOIN ITM1 ON OITM.ItemCode=ITM1.ItemCode 
    JOIN OPLN ON OPLN.ListNum=ITM1.PriceList AND UPPER(LTRIM(RTRIM(OPLN.ListName)))='SALES PRICE LIST'
    WHERE OITM.ItemCode =$[$38.1.0] AND $[$38.14.NUMBER] != ITM1.Price
    

    It works for all the lines in Sales Order.Even If there is a single item with Price before Discount not equal to Sales Price List Value,

    System will send the document for approval.

    I have put the condition $[$38.14.NUMBER] ! = ITM1.Price assuming that the price in SO should be exactly equal to Sales Price Price List .

    Try this query at your end and let me know the result.

    Thanks and Regards,

    Pooja Singh.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.