I have found a solution of sorts for this issue. It is quite messy with a couple of temp tables etc but it appears to work. I am sure some good coding experts can actually make it much smoother and faster. Here is my code

SELECT idno = 0, x.xfer_no, x.from_loc, x.to_loc, x.date_shipped, x.date_entered, x.date_recvd, xl.line_no, xl.part_no, xl.description,

xl.shipped, xl.qty_rcvd, xl.UoM, U.Description AS UoM_Desc, xl.conv_factor, xl.cost, IL.avg_cost

INTO #Xfer_Details

FROMxfers x

INNER JOINxfer_list xl ON x.xfer_no = xl.xfer_no

INNER JOINUOM_list U ON xl.UoM = U.UoM

INNER JOINinv_list IL ON IL.Location = 'TAV1' AND IL.Part_no = xl.part_no

WHERE x.to_loc = 'TAV1'

GO

-- Update IDNo field in temp table #Xfer_details

DECLARE @ID INT

SET @ID = 0

UPDATE #Xfer_details

SET @ID = idno =@id+1

GO

-- Create Temp table #Xfer_cost

CREATE TABLE #Xfer_Cost (

xfer_date datetime,

xfer_no INT,

xfer_line INT,

From_Loc VARCHAR(10),

Item VARCHAR(16),

Item_Description VARCHAR(55),

Item_UoM VARCHAR(2),

Receipt_Date VARCHAR(10),

Last_Receipt_no INT,

Branch VARCHAR(5),

Qty_Received DECIMAL(28,8),

Receipt_UoM CHAR(2),

UoM_Description VARCHAR(30),

Conv_Factor DECIMAL(28,8),

GroupCode VARCHAR(30),

Resource_Type VARCHAR(30),

Void VARCHAR(3),

PO_no INT,

PO_TaxCode VARCHAR(20),

Unit_Cost DECIMAL(28,8)

)

-- Populate #Xfer_cost - setting parameter values

DECLARE @IntFlag INT

DECLARE @MaxNo INT

SELECT @MaxNo = MAX(IDno) FROM #Xfer_Details

SET @IntFlag = 1

WHILE (@IntFlag <= @MaxNo)

BEGIN

DECLARE @Date DateTime

DECLARE @Item VARCHAR(16)

DECLARE @Location VARCHAR(16)

DECLARE @xfer_no INT

DECLARE @xfer_line INT

DECLARE @From_loc VARCHAR(10)

DECLARE @Xfer_date Datetime

SELECT @date =x.date_shipped , @Item = x.part_no, @Location = x.from_loc, @xfer_no = x.xfer_no, @xfer_line = x.line_no, @from_loc = x.from_loc, @xfer_date = x.date_shipped

FROM #Xfer_Details x

WHERE x.Idno = @IntFlag

-- Populating #Xfer_cost - Calling stored procedure

INSERT INTO #xfer_cost

EXEC SnS_LastPriceByItem @Date, @item, @location, @xfer_no, @xfer_line, @from_loc, @xfer_date

SET @IntFlag = @IntFlag + 1

END

GO

-- Final Select from #Xfer_Cost

SELECTx.IDno, x.xfer_no, x.line_no, x.from_loc, x.to_loc, x.date_shipped, x.date_recvd, x.part_no, x.description, x.shipped, x.qty_rcvd, x.UoM, x.UoM_Desc, x.conv_factor,

x.cost, x.avg_cost, c.branch, c.receipt_date, c.last_receipt_no, c.qty_received, c.receipt_uom, c.uom_description, c.conv_factor, c.groupcode, c.resource_type,

c.po_no, c.po_taxcode, c.unit_cost, VEPPieceCost =

CASEWHEN PO_TaxCode = 'INPVEP' THEN unit_cost/c.conv_factor

WHEN PO_TaxCode = 'INPVIP' THEN (unit_cost/c.conv_factor)/1.15

WHEN PO_TaxCode = 'EXEMPT' THEN unit_cost/c.conv_factor

END

FROM #xfer_Details X

LEFT OUTER JOIN #xfer_cost C ON X.xfer_no = C.xfer_no AND X.line_no = C.xfer_line

-- Clean up temp tables

DROP TABLE #Xfer_Details

DROP TABLE #Xfer_Cost

All constructive comments are invited.