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 ?