Hi,
I am trying to creat a SmartList with Builder, however I cannot work out how to populate one field. I have created a View, but there is one field that needs a stored procedure to get it's value:
The View is this:
SELECT TOP(100) PERCENT
dbo.BOM50100.ITEMNMBR AS [Finished Item Number],
dbo.BOM50100.CMPTITNM AS [Component Item Number],
dbo.BOM50100.ITEMDESC AS [Component Description],
dbo.IV00101.ITMCLSCD AS [Item Class],
dbo.BOM50100.CMPITQTY AS [QTY in BOM],
dbo.BOM50100.UOFM AS [Component UOM],
dbo.IV00101.CURRCOST AS [Current Cost],
CAST(dbo.BOM50100.CMPITQTY * dbo.IV00101.CURRCOST AS DECIMAL(19, 5)) AS [Extended Current Cost],
dbo.IV00101.STNDCOST AS [Standard Cost],
CAST(dbo.BOM50100.CMPITQTY * dbo.IV00101.STNDCOST AS DECIMAL(19, 5)) AS [Extended Standard Cost],
CAST(0 AS DECIMAL(19,5)) AS [Last Landed Cost],
CAST(0 AS DECIMAL(19,5)) AS [Extended Last Landed Cost]
FROM dbo.BOM50100 INNER JOIN
dbo.IV00101 ON dbo.BOM50100.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (dbo.BOM50100.ITEMNMBR IN
( SELECT ITEMNMBR
FROM dbo.IV00101 AS IV00101_1
WHERE (ITMCLSCD LIKE '[0-4][0-9]')))
ORDER BY [Finished Item Number], [Component Item Number]
The Last Landed Cost can be retrieved from passing the Component Item Number to a stored procedure, but I don't know how to retrieve all 50,000 in one hit.
That Stored Procedure is this:
[dbo].[spLoadLastLandedCost]
@ItemNumber varchar(30) = NULL,
@Receipt varchar(20) = NULL
AS BEGIN
DECLARE @ITEM varchar(30),
@RCPT varchar(20)
SELECT @ITEM = @ItemNumber
--check wheter BOM or COmponent
if exists (Select ITEMNMBR from BOM50100 Where ITEMNMBR = @ITEM)
--item is BOM
Select TOP 1 @RCPT = DOCNUMBR
from IV30300
where itemnmbr = @ITEM and
doctype = 1 and
TRXLOCTN = 'ARDEN'
ORDER BY DOCDATE desc
Else
--item is Component
Select TOP 1 @RCPT = DOCNUMBR
from IV30300
where itemnmbr = @ITEM and
doctype = 4
ORDER BY DOCDATE desc
--get actual last cost(AUD)
Select SUM(unitcost*QtyRecvd)/NULLIF(SUM(QtyRecvd),0)
from IV10200
Where itemnmbr = @ITEM and
RCPTNMBR = @RCPT
Does anyone know how I can call the stored proc in a SELECT statement?
I have tried to temp table and populate with a cursor without any luck??
Any ideas would be most appreciated.
Thanks, Steve
