[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 625
  • Last Modified:

Incorrect syntax near the keyword 'DECLARE'

I am writing a stored procedure amd I need to get data from a couple of tables and then add it to a temp table.  After this is done, i then want to get more data from some other tables and then append this to the same temp table that i created before and then use this temp table as my dataset.  I can get the data separately from the different tables but when I try to declare a cursor to take the data from one temporary table and insert it into another temporary table so i can get the data all in one table, i get an error:  
Msg 156, Level 15, State 1, Procedure BFForecastOffsiteReplenishment, Line 107
Incorrect syntax near the keyword 'DECLARE'.

I have looked through the code and i don't see anything that is wrong.  Does anyone know what i should be looking for?  Maybe there is a different way to go about this.  If you have a suggestion for that please let me know.  I have listed my code below.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
	BEGIN
		DROP  Procedure  BFForecastOffsiteReplenishment
	END
 
GO
 
CREATE Procedure BFForecastOffsiteReplenishment
 
AS
 
SET NOCOUNT ON
 
DECLARE
 
	@WorkGrp varchar(3),
	@WorkArea varchar(4),
	@Area varchar(10),
	@ProNum varchar(25),
	@Location varchar(10),
	@Quantity int,
	@Price numeric,
	@ExtCost numeric
 
DECLARE @InventoryTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
DECLARE @ReserveTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
DECLARE @ActiveTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As Part_Number,
		LH.DSP_LOCN As Location,
		PLD.ACTL_INVN_QTY As Quantity,
		IM.UNIT_PRICE As Price,
		PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
 
FROM
		ITEM_MASTER As IM INNER JOIN
		PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
		PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
		LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
 
ORDER BY 
		 Area, Location
 
INSERT INTO @ActiveTable
(	WorkGrp,
	WorkArea,
	Area,
	ProNum,
	Location,
	Quantity,
	Price,
	ExtCost)
	
DECLARE ActiveInvCursor CURSOR FAST_FORWARD FOR 
SELECT
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
FROM
		@ActiveTable
		
Open ActiveInvCursor
 
Fetch Next From ActiveInvCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
 
While @@FETCH_STATUS = 0 
	Begin
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Fetch Next From ActiveInvCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
	End
 
CLOSE ActiveInvCursor
DEALLOCATE ActiveInvCursor
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As ProNum,
		LH.DSP_LOCN As Location,
		CD.ACTL_QTY As Quantity, 
		IM.UNIT_PRICE As Price, 
		CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
 
FROM
		ITEM_MASTER IM INNER JOIN
		CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
		CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
		LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
		RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
 
ORDER BY
		Area, Location
 
INSERT INTO @ReserveTable
(	WorkGrp,
	WorkArea,
	Area,
	Pronum,
	Location,
	Quantity,
	Price,
	ExtCost)
 
DECLARE RsvInventoryCursor CURSOR FAST_FORWARD FOR 
SELECT
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
FROM
		@ReserveTable ReserveInventory
		
Open RsvInventoryCursor
 
Fetch Next From RsvInventoryCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
 
While @@FETCH_STATUS = 0 
	Begin
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Fetch Next From RsvInventoryCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
	End
 
CLOSE RsvInventoryCursor
DEALLOCATE RsvInventoryCursor
 
Select 
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
From
		@InventoryTable
 
Order By
		Area, Location
GO
 
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
 
GO

Open in new window

0
Jeff Geiselman
Asked:
Jeff Geiselman
1 Solution
 
BrandonGalderisiCommented:
on line 113 you have a DECLARE directly following an insert statement.


DECLARE ActiveInvCursor CURSOR FAST_FORWARD FOR

The same thing repeats on line 245.  

DECLARE RsvInventoryCursor CURSOR FAST_FORWARD FOR

Are you wanting to insert values or declare a cursor???
0
 
Jeff GeiselmanAuthor Commented:
I want to insert values into a temporary table, then get more data from the other tables and append this data to the temporary table previously created.  Once i have this done then I want to use this as my data set.
0
 
BrandonGalderisiCommented:
Ok.  I think this should do it...

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
	BEGIN
		DROP  Procedure  BFForecastOffsiteReplenishment
	END
 
GO
 
CREATE Procedure BFForecastOffsiteReplenishment
 
AS
 
SET NOCOUNT ON
 
DECLARE
 
	@WorkGrp varchar(3),
	@WorkArea varchar(4),
	@Area varchar(10),
	@ProNum varchar(25),
	@Location varchar(10),
	@Quantity int,
	@Price numeric,
	@ExtCost numeric
 
DECLARE @InventoryTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
DECLARE @ReserveTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
DECLARE @ActiveTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As Part_Number,
		LH.DSP_LOCN As Location,
		PLD.ACTL_INVN_QTY As Quantity,
		IM.UNIT_PRICE As Price,
		PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
 
FROM
		ITEM_MASTER As IM INNER JOIN
		PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
		PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
		LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
 
ORDER BY 
		 Area, Location
 
 
	
DECLARE ActiveInvCursor CURSOR FAST_FORWARD FOR 
SELECT
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
FROM
		@ActiveTable
		
Open ActiveInvCursor
 
Fetch Next From ActiveInvCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
 
While @@FETCH_STATUS = 0 
	Begin
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Fetch Next From ActiveInvCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
	End
 
CLOSE ActiveInvCursor
DEALLOCATE ActiveInvCursor
 
 
INSERT INTO @ReserveTable
(	WorkGrp,
	WorkArea,
	Area,
	Pronum,
	Location,
	Quantity,
	Price,
	ExtCost)
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As ProNum,
		LH.DSP_LOCN As Location,
		CD.ACTL_QTY As Quantity, 
		IM.UNIT_PRICE As Price, 
		CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
 
FROM
		ITEM_MASTER IM INNER JOIN
		CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
		CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
		LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
		RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
 
ORDER BY
		Area, Location
 
 
DECLARE RsvInventoryCursor CURSOR FAST_FORWARD FOR 
SELECT
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
FROM
		@ReserveTable ReserveInventory
		
Open RsvInventoryCursor
 
Fetch Next From RsvInventoryCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
 
While @@FETCH_STATUS = 0 
	Begin
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Fetch Next From RsvInventoryCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
	End
 
CLOSE RsvInventoryCursor
DEALLOCATE RsvInventoryCursor
 
Select 
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
From
		@InventoryTable
 
Order By
		Area, Location
GO
 
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
 
GO

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jeff GeiselmanAuthor Commented:
Ok, that seems to work, however i end up with two tables.  Can I then merge these two tables into one so that i am looking at one data set?
0
 
BrandonGalderisiCommented:
After looking further, your cursors are COMPLETELY unnecessary.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
	BEGIN
		DROP  Procedure  BFForecastOffsiteReplenishment
	END
 
GO
 
CREATE Procedure BFForecastOffsiteReplenishment
 
AS
 
SET NOCOUNT ON
 
DECLARE
 
	@WorkGrp varchar(3),
	@WorkArea varchar(4),
	@Area varchar(10),
	@ProNum varchar(25),
	@Location varchar(10),
	@Quantity int,
	@Price numeric,
	@ExtCost numeric
 
DECLARE @InventoryTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
Insert Into @InventoryTable
	(	WorkGrp,
		WorkArea,
		Area,
		Pronum,
		Location,
		Quantity,
		Price,
		ExtCost)
 
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As Part_Number,
		LH.DSP_LOCN As Location,
		PLD.ACTL_INVN_QTY As Quantity,
		IM.UNIT_PRICE As Price,
		PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
 
FROM
		ITEM_MASTER As IM INNER JOIN
		PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
		PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
		LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
 
UNION ALL
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As ProNum,
		LH.DSP_LOCN As Location,
		CD.ACTL_QTY As Quantity, 
		IM.UNIT_PRICE As Price, 
		CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
 
FROM
		ITEM_MASTER IM INNER JOIN
		CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
		CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
		LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
		RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
 
ORDER BY
		Area, Location
 
 
Select 
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
From
		@InventoryTable
 
Order By
		Area, Location
GO
 
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
 
GO

Open in new window

0
 
SharathData EngineerCommented:

You should mention the values or a query after thiese INSERTs.
like INSERT INTO @ActiveTable values (WorkGrp value,WorkArea value,...) or
INSERT INTO @ActiveTable SELECT WorkGrp,WorkArea,Area,ProNum,Location,Quantity,Price,ExtCost FROM OtherTable
I have commented these two INSERT statements. You can uncomment and make those two statement complet by adding the VALUES clause or another SELECT query.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
	BEGIN
		DROP  Procedure  BFForecastOffsiteReplenishment
	END
 
GO
 
CREATE Procedure BFForecastOffsiteReplenishment
 
AS
 
SET NOCOUNT ON
 
DECLARE
 
	@WorkGrp varchar(3),
	@WorkArea varchar(4),
	@Area varchar(10),
	@ProNum varchar(25),
	@Location varchar(10),
	@Quantity int,
	@Price numeric,
	@ExtCost numeric
 
DECLARE @InventoryTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
DECLARE @ReserveTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
DECLARE @ActiveTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric,
	ExtCost numeric
)
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As Part_Number,
		LH.DSP_LOCN As Location,
		PLD.ACTL_INVN_QTY As Quantity,
		IM.UNIT_PRICE As Price,
		PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
 
FROM
		ITEM_MASTER As IM INNER JOIN
		PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
		PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
		LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
 
ORDER BY 
		 Area, Location
 
--INSERT INTO @ActiveTable
--(	WorkGrp,
--	WorkArea,
--	Area,
--	ProNum,
--	Location,
--	Quantity,
--	Price,
--	ExtCost)
	
DECLARE ActiveInvCursor CURSOR FAST_FORWARD FOR 
SELECT
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
FROM
		@ActiveTable
		
Open ActiveInvCursor
 
Fetch Next From ActiveInvCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
 
While @@FETCH_STATUS = 0 
	Begin
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Fetch Next From ActiveInvCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
	End
 
CLOSE ActiveInvCursor
DEALLOCATE ActiveInvCursor
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As ProNum,
		LH.DSP_LOCN As Location,
		CD.ACTL_QTY As Quantity, 
		IM.UNIT_PRICE As Price, 
		CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
 
FROM
		ITEM_MASTER IM INNER JOIN
		CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
		CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
		LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
		RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
 
ORDER BY
		Area, Location
 
--INSERT INTO @ReserveTable
--(	WorkGrp,
--	WorkArea,
--	Area,
--	Pronum,
--	Location,
--	Quantity,
--	Price,
--	ExtCost)
 
DECLARE RsvInventoryCursor CURSOR FAST_FORWARD FOR 
SELECT
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
FROM
		@ReserveTable ReserveInventory
		
Open RsvInventoryCursor
 
Fetch Next From RsvInventoryCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
 
While @@FETCH_STATUS = 0 
	Begin
		Insert Into @InventoryTable
			(	WorkGrp,
				WorkArea,
				Area,
				Pronum,
				Location,
				Quantity,
				Price,
				ExtCost)
		Values
			(	@WorkGrp,
				@WorkArea,
				@Area,
				@ProNum,
				@Location,
				@Quantity,
				@Price,
				@ExtCost)
		
		Fetch Next From RsvInventoryCursor Into
		@WorkGrp,
		@WorkArea,
		@Area,
		@ProNum,
		@Location,
		@Quantity,
		@Price,
		@ExtCost
	End
 
CLOSE RsvInventoryCursor
DEALLOCATE RsvInventoryCursor
 
Select 
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
From
		@InventoryTable
 
Order By
		Area, Location
GO
 
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
 
GO

Open in new window

0
 
Jeff GeiselmanAuthor Commented:
That is it, only my price and extcost fields are turning to integer and i am loosing my decimal.  Is there a way that I can keep them numeric instead of integer?
0
 
BrandonGalderisiCommented:
You have to define the @price specifically.

Ex:

DECLARE @InventoryTable TABLE
(
      WorkGrp varchar(3),
      WorkArea varchar(4),
      Area varchar(10),
      ProNum varchar(25),
      Location varchar(10),
      Quantity int,
      Price numeric(11,2),
      ExtCost numeric(11,2)
)


updated procedure
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'BFForecastOffsiteReplenishment')
	BEGIN
		DROP  Procedure  BFForecastOffsiteReplenishment
	END
 
GO
 
CREATE Procedure BFForecastOffsiteReplenishment
 
AS
 
SET NOCOUNT ON
 
 
 
DECLARE @InventoryTable TABLE
(
	WorkGrp varchar(3),
	WorkArea varchar(4),
	Area varchar(10),
	ProNum varchar(25),
	Location varchar(10),
	Quantity int,
	Price numeric(11,2),
	ExtCost numeric(11,2)
)
 
 
Insert Into @InventoryTable
	(	WorkGrp,
		WorkArea,
		Area,
		Pronum,
		Location,
		Quantity,
		Price,
		ExtCost)
 
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As Part_Number,
		LH.DSP_LOCN As Location,
		PLD.ACTL_INVN_QTY As Quantity,
		IM.UNIT_PRICE As Price,
		PLD.ACTL_INVN_QTY * IM.UNIT_PRICE AS Extended_Cost
 
FROM
		ITEM_MASTER As IM INNER JOIN
		PICK_LOCN_DTL As PLD ON IM.SKU_ID = PLD.SKU_ID INNER JOIN
		PICK_LOCN_HDR As PLH ON PLD.LOCN_ID = PLH.LOCN_ID INNER JOIN
		LOCN_HDR As LH ON PLH.LOCN_ID = LH.LOCN_ID
 
UNION ALL
 
SELECT
		LH.WORK_GRP As WorkGrp,
		LH.WORK_AREA As WorkAera,
		Case
			When LH.WORK_GRP = 'EAA' Or LH.WORK_GRP = 'CPC' Or LH.WORK_GRP = 'CPN' Then 'On-Site'
			When LH.WORK_GRP = 'OFF' Then 'Off-Site'
			When LH.WORK_GRP = 'RSV' And (LH.WORK_AREA = 'DR01' Or LH.WORK_AREA = 'DR02' Or
				 LH.WORK_AREA = 'DR03' Or LH.WORK_AREA = 'DR04' Or LH.WORK_AREA = 'DR05' Or
				 LH.WORK_AREA = 'DR06' Or LH.WORK_AREA = 'DR07' Or LH.WORK_AREA = 'DR14' Or
				 LH.WORK_AREA = 'DR15' Or LH.WORK_AREA = 'DR16' Or LH.WORK_AREA = 'DR17' Or
				 LH.WORK_AREA = 'DR18' Or LH.WORK_AREA = 'DR19' Or LH.WORK_AREA = 'DRE1' Or
				 LH.WORK_AREA = 'DRE2' Or LH.WORK_AREA = 'QA01' Or LH.WORK_AREA = 'R01A' Or
				 LH.WORK_AREA = 'R01B' Or LH.WORK_AREA = 'R03A' Or LH.WORK_AREA = 'R04A' Or
				 LH.WORK_AREA = 'R06A' Or LH.WORK_AREA = 'R07A' Or LH.WORK_AREA = 'R07B' Or
				 LH.WORK_AREA = 'R08A' Or LH.WORK_AREA = 'R09A' Or LH.WORK_AREA = 'R10A' Or
				 LH.WORK_AREA = 'R11A' Or LH.WORK_AREA = 'R12A' Or LH.WORK_AREA = 'R13A' Or
				 LH.WORK_AREA = 'R14A' Or LH.WORK_AREA = 'R14B' Or LH.WORK_AREA = 'R16A' Or
				 LH.WORK_AREA = 'R17A' Or LH.WORK_AREA = 'R18A' Or LH.WORK_AREA = 'R19A' Or
				 LH.WORK_AREA = 'R61A' Or LH.WORK_AREA = 'R64A' Or LH.WORK_AREA = 'R65A' Or
				 LH.WORK_AREA = 'R66A' Or LH.WORK_AREA = 'R67A' Or LH.WORK_AREA = 'R68A' Or
				 LH.WORK_AREA = 'R69A' Or LH.WORK_AREA = 'R70A' Or LH.WORK_AREA = 'R71A' Or
				 LH.WORK_AREA = 'R72A' Or LH.WORK_AREA = 'R73A' Or LH.WORK_AREA = 'R74A' Or
				 LH.WORK_AREA = 'R78A' Or LH.WORK_AREA = 'R80A' Or LH.WORK_AREA = 'R81A' Or
				 LH.WORK_AREA = 'R82A' Or LH.WORK_AREA = 'R83A' Or LH.WORK_AREA = 'R84A' Or
				 LH.WORK_AREA = 'R85A' Or LH.WORK_AREA = 'R86A' Or LH.WORK_AREA = 'R88A') Then 'On-Site'
			When LH.WORK_GRP = 'RSV' And LH.WORK_AREA = 'DR30' Then 'Off-Site'
		End As Area,
		IM.SKU_DESC As ProNum,
		LH.DSP_LOCN As Location,
		CD.ACTL_QTY As Quantity, 
		IM.UNIT_PRICE As Price, 
		CD.ACTL_QTY * IM.UNIT_PRICE AS ExtCost
 
FROM
		ITEM_MASTER IM INNER JOIN
		CASE_DTL CD ON IM.SKU_ID = CD.SKU_ID INNER JOIN
		CASE_HDR CH ON CD.CASE_NBR = CH.CASE_NBR INNER JOIN
		LOCN_HDR LH ON CH.LOCN_ID = LH.LOCN_ID AND LH.WHSE = CH.WHSE INNER JOIN
		RESV_LOCN_HDR RLH ON LH.LOCN_ID = RLH.LOCN_ID
 
ORDER BY
		Area, Location
 
 
Select 
		WorkGrp,
		WorkArea,
		Area,
		ProNum,
		Location,
		Quantity,
		Price,
		ExtCost
From
		@InventoryTable
 
Order By
		Area, Location
GO
 
GRANT EXEC ON BFForecastOffsiteReplenishment TO PUBLIC
 
GO

Open in new window

0
 
Jeff GeiselmanAuthor Commented:
Your a genius!  Thanks for the help! You saved me a lot of time and research!!!!
0
 
reb73Commented:
I don't think multiple cursors are justified for what you are trying to extract, a simple union query would appear to suffice..
0
 
BrandonGalderisiCommented:
yup... posted that in http://#a23652703
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now