CREATE PROCEDURE USP_GetOrderamendment_SLvalue --Add the parameters for the stored
procedure here ( @OrderId uniqueidentifier ) AS --declare @OrderId uniqueidentifier
BEGIN Declare @baseOrderNumber nvarchar(200), @baseOrderId
nvarchar(200),@baseOriginalO
rdervalue nvarchar(200); --set @orderId='e38bb04f-3439-ea11-a969-000d3aa16502';
--set @baseOrderNumber='00049974' select
@baseOrderNumber=wipro_ordernumber,@baseOriginalOrdervalue=wipro_originalordervalue
from SalesOrder where SalesOrderId=@OrderId
if CHARINDEX('-',@baseOrderNumber) > 0 set @baseOrderNumber=
LEFT(@baseOrderNumber, CHARINDEX('-', @baseOrderNumber) - 1) select
@baseOrderId=SalesOrderId,@baseOriginalOrdervalue=wipro_originalordervalue from
SalesOrder where wipro_ordernumber=@baseO
rderNumber select @baseOriginalOrdervalue 'TotalOrderValue' select
@baseOrderId as orderid,
coalesce(sum(isnull((wo.wipro_originalordervalue/[Link])*
[Link],0)),0) as amendmentvalue from SalesOrder wo inner join
transactioncur
rency tr on [Link]=[Link] inner join SalesOrder
parentob on parentob.wipro_ordernumber = @baseOrderNumber inner join
transactioncurrency parenttr on
[Link]=[Link] inne
r join StringMap str on [Link]='wipro_orderapprovalstage' where
wo.wipro_ordernumber like @baseOrderNumber+'-%' and [Link]=0 and
[Link]=0 and [Link] like '%approved%' and [Link] like
'%requestforeclosure%' and str
.Value like '%foreclosurerejected%' END