One last piece of advice i need. i am able to run this for the first time, but i need to delete the dbo.tblPlexActualPrice table everytime the SPROC runs so i can be re-created. I tried to add a DELETE FROM and INSERT INTO statement but it error out on the syntax where is creates the table.

USE [ConstarOLAP_PROPHIX_FactDb]

GO

/****** Object: StoredProcedure [dbo].[ActualPurchasePriceExport] Script Date: 07/08/2013 13:16:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =======================================================

-- Author:Tom Stagliano, Constar

-- Create date: June 25, 2013

-- Description:Prophix Actual Purchase Price Export SPROC

-- =======================================================

ALTER PROCEDURE [dbo].[ActualPurchasePriceExport]

AS

BEGIN

DECLARE @Start_Date DATETIME = NULL

DECLARE @Part_Type_MP VARCHAR(1000) = ''

SET @Part_Type_MP = ',' + @Part_Type_MP + ','

SET @Start_Date = ISNULL(@Start_Date, DATEADD(yy, 50, GETDATE()))

SET @Start_Date = dateadd(ms,-3,dateadd(day,1,DATEADD(dd, DATEDIFF(dd,0,@Start_Date), 0)))

DECLARE

@sqlquery NVARCHAR(MAX),

@finalQuery NVARCHAR(MAX),

@q CHAR(1)=''''

SET @sqlquery = N'SELECT ' +

'PLK.Part_no, ' +

'PLK.line_item_key, ' +

'PLK.unit_price ' +

'FROM ' +

'(SELECT p.part_no, pli.unit_price, MAX(PLI.Line_Item_Key) AS [Line_Item_Key] ' +

'FROM Purchasing_v_Line_Item_e AS PLI ' +

'JOIN Part_v_Part_e AS P ' +

'ON p.plexus_customer_no = pli.plexus_customer_no ' +

'AND p.part_key = pli.part_key ' +

'WHERE pli.add_date <= ' + @q + @q + CONVERT(VARCHAR(30), @Start_Date,120)+ @q + @q +

' AND (' + @q + @q + @Part_Type_MP + @q + @q +

' = '''',,'''' OR (CHARINDEX('''','''' + CAST(P.Part_Type as VARCHAR(50)) + '''','''', ' +

@q + @q + @Part_Type_MP + @q + @q + ') >0))' +

'GROUP by p.part_no, pli.unit_price ' +

') AS PLK'

-- SET @finalQuery = N'INSERT INTO dbo.tblActualPrice ' + CHAR(10)

CREATE TABLE dbo.tblPlexActualPrice(ResultsID int identity(1,1) NOT NULL PRIMARY KEY, Part varchar(50), ActualCost money)

SET @finalQuery = N'INSERT INTO dbo.tblPlexActualPrice (Part,ActualCost) SELECT ' +

'part_no AS Part, ' +

'unit_price AS ActualCost ' +

'FROM OPENQUERY (PLEXREPORTSERVER, ''' + @sqlquery + ''')'

EXEC (@finalQuery)

END