asked on
There is already an object named '???????' in the database.

Not exactly sure why but the error I am getting I do not understand. I was testing this code and got the error on....
Msg 2714, Level 16, State 6, Line 40
There is already an object named '#SalesSummary' in the database.

I thought I would have to have that value there......NO?
USE [DATA_04]
Declare @Cus_no_Start varchar(12),
	 @Cus_no_End varchar(12), 
	 @DB_Include int -- 0 = Macola Only, 1 = RAMS Only, 2 = Macola and RAMS 

----------------------------------------------------
-- get static variables from date tables
----------------------------------------------------
declare @CurrFromDate int, @CurrToDate int, @CurrToDateYTD int, @CurrFromDateYTD int,
@PriorFromDateYTD int, @PriorToDateYTD int, @CurrWeekBeginDate int, @CurrWeekEndDate int,
@PriorWeekBeginDate int, @PriorWeekEndDate int, @PriorFromDate int, @PriorToDate int
select
   @CurrWeekEndDate 	= CurrWeekEndDate, 
   @CurrToDateYTD 	= CurrToDateYTD,
   @CurrFromDateYTD	= CurrFromDateYTD,
   @CurrFromDate 	= CurrFromDate,
   @CurrToDate 		= CurrToDate, 
   @CurrToDateYTD 	= CurrToDateYTD,
   @CurrWeekBeginDate 	= CurrWeekBeginDate,
   @CurrWeekEndDate 	= CurrWeekEndDate,
   @PriorWeekBeginDate 	= PriorWeekBeginDate,
   @PriorWeekEndDate 	= PriorWeekEndDate,
   @PriorFromDateYTD 	= PriorFromDateYTD,
   @PriorToDateYTD 	= PriorToDateYTD,
   @PriorFromDate 	= PriorFromDate,
   @PriorToDate 	= PriorToDate
from dbo.Rpt_ReportingDates
declare @CurrentWeek decimal(8,0), @NumberOfWeeks int, @CurrToDate_Period tinyint, @CurrToDateYTD_FiscalYear smallint
select 
   @CurrentWeek 	= CurrentWeek,
   @NumberOfWeeks 	= NumberOfWeeks,
   @CurrToDate_Period 	= CurrToDate_Period,
   @CurrToDateYTD_FiscalYear = CurrToDateYTD_FiscalYear
from dbo.vwFiscalDates

------------------------------------------------------------
-- create temp table for Sales Summary
------------------------------------------------------------

Create table #SalesSummary 
(
	[data_source] varchar(12),--  what database is the data coming from  macola or rams?
	PerType varchar(18) NULL,
	[IDVALUE] Varchar (6) Null,
	[ID_TITLE]Varchar (40) Null,
	[CurrWeekEndDate] [int] NULL ,
	[CurrToDate] [int] NULL ,
	[CurrToDateYTD] [int] NULL ,
	[CurrentWeek] [decimal](8, 0) NULL ,
	[NumberOfWeeks] [int] NULL ,
	[CurrToDate_Period] [tinyint] NULL ,
	[CurrToDateYTD_FiscalYear] [smallint] NULL ,	
	[Terr] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Terr_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Report_Sort] [int] NULL ,
	[BrandCode] Varchar (1) null,
	[TerrAdopted] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TerrAdoptedName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Adopted_Sort] [int] NULL ,
	[BRAND] Varchar (100) Null,
	[BRAND_ID] Varchar (6) Null,
	Item_no varchar(15),
	Gross_Sales decimal(38,2) NULL,
	Cost decimal(38,2) NULL
)

----------------------------------------------------
-- insert for MTD Current and Prior MACOLA
----------------------------------------------------
if (@DB_Include = 0 or @DB_Include = 2) 
BEGIN
INSERT INTO #SalesSummary 

SELECT DISTINCT

	'Macola',
	'2 - MTD Current',
	Rpt_Territory.RAMSID as IDVALUE, -- IDVALUE
	'0' AS ID_TITLE, -- ID_TITLE
	@CurrWeekEndDate as CurrWeekEndDate, 
    @CurrToDate as CurrToDate, 
    @CurrToDateYTD as CurrToDateYTD, 
    @CurrentWeek AS CurrentWeek,
    @NumberofWeeks AS NumberofWeeks,
    @CurrToDate_Period AS CurrToDate_Period,
    @CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
    ARCUSFIL_SQL.Terr as Terr,
	Rpt_Territory.Terr_Name as Terr_Name,
	Rpt_Territory.Report_Sort as Report_Sort,
 	Case when Rpt_TerritoryAdopted.RAMSID is null then 'A'
		Else 'B' end as BrandCode,
    Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
    Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
    Rpt_Territory.Report_Sort as Adopted_Sort,
	PR_ID.ID_DESC AS BRAND,
	PD.PRD_R_BRANDNAME AS BRAND_ID,
	OELINHST_SQL.Item_no, 
	Gross_Sales = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate  
         THEN sls_amt ELSE 0 END),
	Cost = SUM(CASE WHEN OEHDRHST_SQL.Inv_dt BETWEEN @PriorFromDate AND @PriorToDate 
         THEN cost_amt ELSE 0 END)
  
       
FROM OELINHST_SQL
	INNER JOIN OEHDRHST_SQL 
		ON OEHDRHST_SQL.Ord_type = OELINHST_SQL.Ord_type 
		AND OEHDRHST_SQL.Ord_no = OELINHST_SQL.Ord_no 
		AND OEHDRHST_SQL.Inv_no = OELINHST_SQL.Inv_no
	INNER JOIN ARCUSFIL_SQL 
		ON ARCUSFIL_SQL.Cus_no = OELINHST_SQL.Cus_no

	INNER JOIN Rpt_Territory ON Rpt_Territory.Terr_id = ARCUSFIL_SQL.Terr

/********************/-- Here is where I pick up IDVALUE from Ross using Macola Item number
	LEFT OUTER JOIN DATA_04.dbo.IMITMIDX_SQL IMITMIDX_SQL 
		ON OELINHST_SQL.item_no = IMITMIDX_SQL.item_no
           INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                        ON PD.PRD_PRODUCT = '0' + IMITMIDX_SQL.Item_no   
            INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
/********************/--

	LEFT JOIN Rpt_Territory AS Rpt_TerritoryAdopted 
		ON OELINHST_SQL.item_no BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
		AND Rpt_TerritoryAdopted.End_Item_no

	
--WHERE  	(@Cus_no_Start = '*' or (ARCUSFIL_SQL.Cus_no >= @Cus_no_Start 
		--and ARCUSFIL_SQL.Cus_no <= @Cus_no_End))
GROUP BY 
	Rpt_Territory.RAMSID ,
    ARCUSFIL_SQL.Terr,
	Rpt_Territory.Terr_Name,
	Rpt_Territory.Report_Sort,
	Rpt_TerritoryAdopted.RAMSID ,
    Rpt_TerritoryAdopted.Terr_id ,
    Rpt_TerritoryAdopted.Terr_name ,
    Rpt_Territory.Report_Sort,
	PR_ID.ID_DESC,
	PD.PRD_R_BRANDNAME,
	OELINHST_SQL.Item_no 

END

----------------------------------------------------
-- insert for MTD Current RAMS
----------------------------------------------------

if (@DB_Include = 1 or @DB_Include = 2) 
BEGIN

INSERT INTO #SalesSummary 

SELECT DISTINCT

		'RAMS',
		'2 - MTD Current',
--		SubString(CUST_ID.ID_DESC,3,2) as CM_SELLZONE,
		CUST_ID.IDVALUE,
		CUST_ID.ID_TITLE,
		@CurrWeekEndDate as CurrWeekEndDate, 
		@CurrToDate as CurrToDate, 
		@CurrToDateYTD as CurrToDateYTD, 
		@CurrentWeek AS CurrentWeek,
		@NumberofWeeks AS NumberofWeeks,
		@CurrToDate_Period AS CurrToDate_Period,
		@CurrToDateYTD_FiscalYear AS CurrToDateYTD_FiscalYear,
		SubString(CUST_ID.ID_DESC,3,2) as Terr,
		CUST_ID.ID_DESC as Terr_name,
		Rpt_Territory.Report_Sort as Report_Sort,
		left(PR_ID.ID_DESC,1) as BrandCode,
		Rpt_TerritoryAdopted.Terr_id AS TerrAdopted,
		Rpt_TerritoryAdopted.Terr_name AS TerrAdoptedName,
		Rpt_TerritoryAdopted.Report_Sort as Adopted_Sort,
		PR_ID.ID_DESC AS BRAND,
		PD.PRD_R_BRANDNAME AS BRAND_ID,
		OD.OD_PRODUCT as Item_no,
		Gross_Sales = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 -- SBT = 3 (Pay By Scan)
									AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') -- Sale or BuyBack
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)
							   THEN COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_FINAL_PRICE , 0)
							   ELSE 0 END),
		Cost = SUM(CASE WHEN (OH.OH_SALE_TYPE = 'O' AND OH.OH_CUSTOMER_TYPE_ID IN ('000001','000003') 
									AND CM.CM_CUST_SUB_TYPE <> 3 AND OD.OD_RETURN_REASON_CODE IN ('000000','000001') 
									AND CONVERT(char(8),OH.OH_DELIVERY_DATE,112) BETWEEN @CurrFromDate AND @CurrToDate)									
							   THEN	COALESCE(OD.OD_TOTAL_DELIV_QUANTITY * OD.OD_PRODUCT_COST_PRICE,0)
							   ELSE 0 END)
		
						   
FROM    CHLROS05.RAMSDB.dbo.ROSS_SALESHDR AS OH
        INNER JOIN  CHLROS05.RAMSDB.dbo.ROSS_SALESDTL AS OD         
               ON OD.OD_TICKET_NUMBER = OH_TICKET_NUMBER                                           
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_CUSTOMER AS CM
                  ON OH.OH_CUSTOMER_NUMBER = CM.CM_CUSTKEY
                  AND CM_ACCT_GLPOSTS = '000004'
                  AND left(CM.CM_SELLZONE,1) = '4'
                  AND CM.CM_CUST_SUB_TYPE <> 3
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as CUST_ID  
                        ON CM.CM_SELLZONE = CUST_ID.IDVALUE
                        AND CUST_ID.ID_TITLE = N'Sale Zone'
		left outer join dbo.Rpt_Territory on
			Rpt_Territory.RAMSID = CUST_ID.IDVALUE
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_PRODUCTS AS PD
                        ON PD.PRD_PRODUCT = OD.OD_PRODUCT   
        INNER JOIN CHLROS05.RAMSDB.dbo.ROSS_IDCODE as PR_ID  
                        ON PD.PRD_R_BRANDNAME = PR_ID.IDVALUE
                        AND PR_ID.ID_TITLE = N'Product Brand Name'
        LEFT OUTER JOIN dbo.Rpt_Territory AS Rpt_TerritoryAdopted 
						ON Right(OD.OD_PRODUCT,7) BETWEEN Rpt_TerritoryAdopted.Start_Item_no 
						AND Rpt_TerritoryAdopted.End_Item_no

--WHERE     OH.OH_SALE_TYPE = 'O'
--          AND (ID.ID_TITLE = N'Sale Zone')

GROUP BY    
		CUST_ID.IDVALUE,
		CUST_ID.ID_TITLE,
		SubString(CUST_ID.ID_DESC,3,2) ,
		CUST_ID.ID_DESC ,
		Rpt_Territory.Report_Sort,
		left(PR_ID.ID_DESC,1),
		Rpt_TerritoryAdopted.Terr_id,
		Rpt_TerritoryAdopted.Terr_name,
		Rpt_TerritoryAdopted.Report_Sort,
		PR_ID.ID_DESC,
		PD.PRD_R_BRANDNAME ,
		OD.OD_PRODUCT 

END

----------------------------------------------------
-- final select
----------------------------------------------------


SELECT * FROM #SalesSummary
Open in new window
ASKER CERTIFIED SOLUTION