ksrameshbabu,
Since the variable @Temp contains multiple values you should put it in a temp TABLE variable. You can then join to it instead of using "Where in()". This works and it's fast.
Also, use a derived table to return the max ids, then join to this table on the max ids = ids (see above code).
Also, try to get rid of the cursor. If you have to loop look up "While" in BOL; it can replace most fast forward cursors.
cl
declare @Temp Table (PTSREFELIGID int)
Insert @Temp Values (NULL)--One row for each PTSREFELIGID
declare @PARTLOCID int
select pd3.AMOUNT, pd3.POINTS, pd3.ROUNDING, pd3.MAXPOINTS
From (
Select max(CAMPAIGNID) as maxCAMPAIGNID, max(CAMPAIGNSUBID) as maxCAMPAIGNSUBID
From @Temp t
JOIN POINTS_DETAILS pd1 on pd1.PTSREFELIGID = t.PTSREFELIGID
where pd0.PARTLOCID =@PARTLOCID
) pd2
JOIN POINTS_DETAILS pd3
on pd2.maxCAMPAIGNID = pd3.CAMPAIGNID
and pd2.maxCAMPAIGNSUBID = pd3.CAMPAIGNSUBID
Signature is NULL