USE KFFPROD

DECLARE @FROM          VARCHAR(10) = '<<ALLTRIM(lcCalledFrom)>>' 
DECLARE @DOCUMENTNO    VARCHAR(10) = '<<ALLTRIM(lcordrno)>>'
--DECLARE @DOCUMENTNO    VARCHAR(10) = '4015831'

DECLARE @HISTORYTYPE   VARCHAR(10) = '<<ALLTRIM(lchistorytype)>>'
--DECLARE @HISTORYTYPE   VARCHAR(10) = 'Daily'

DECLARE @ITEMLISTCODE   VARCHAR(20) = '<<ALLTRIM(lcitemlistcode)>>'
--DECLARE @ITEMLISTCODE  VARCHAR(20) = ''
--DECLARE @ITEMLISTCODE  VARCHAR(20) = (SELECT [Item List Code]       FROM [Kitchen Fresh Foods$Sales Header Import]      WHERE [Document No_] = @DOCUMENTNO)

DECLARE @DOCUMENTDATE  DATETIME    
DECLARE @MENUSEQNO     VARCHAR(10) 
DECLARE @MENUCYCLE     VARCHAR(20) 
DECLARE @CUSTOMER      VARCHAR(20) 
DECLARE @LOCATION      VARCHAR(20) 

IF @FROM = 'SALESORDER' 
   BEGIN
     SET @DOCUMENTDATE   = (SELECT [Document Date]           FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document No_] = @DOCUMENTNO and [document type]= 1)
     SET @MENUSEQNO      = (SELECT [Menu Sequence No_]       FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document No_] = @DOCUMENTNO and [document type]= 1)
     SET @MENUCYCLE      = (SELECT [Menu Cycle]              FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document No_] = @DOCUMENTNO and [document type]= 1)
     SET @CUSTOMER       = (SELECT [sell-to customer no_]    FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document No_] = @DOCUMENTNO and [document type]= 1)
     SET @LOCATION       = (SELECT [Customer Location]       FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document No_] = @DOCUMENTNO and [document type]= 1)
   END

IF @FROM = 'LINEGEN'
   BEGIN
     SET @DOCUMENTDATE = '<<DTOC(ldMenuDate)>>'
     SET @MENUSEQNO    = '<<ALLTRIM(lcMenuSeqNo)>>'
     SET @MENUCYCLE    = '<<ALLTRIM(lcMenuCycle)>>'
     SET @CUSTOMER     = '<<ALLTRIM(lcCustomer)>>'
     SET @LOCATION     = ''
   END

DECLARE @MASTER        VARCHAR(20) = (SELECT [master]               FROM [Kitchen Fresh Foods$Custom Fields - Customer] WHERE [Customer No_] = @CUSTOMER)
DECLARE @LABELCUST     VARCHAR(20) = (SELECT [Label Customer No_]   FROM [Kitchen Fresh Foods$Custom Fields - Customer] WHERE [Customer No_] = @CUSTOMER)
DECLARE @RTLPRICEGRP   VARCHAR(20) = (SELECT [Retail Price Group]   FROM [Kitchen Fresh Foods$Customer]                 WHERE [No_]          = @CUSTOMER)
DECLARE @CUSTPRICEGRP  VARCHAR(20) = (SELECT [Customer Price Group] FROM [Kitchen Fresh Foods$Customer]                 WHERE [No_]          = @CUSTOMER)

SET @LABELCUST = CASE @LABELCUST WHEN '' THEN @CUSTOMER ELSE @LABELCUST END

DECLARE @SD1  DATETIME = DATEADD(D,-7,@DOCUMENTDATE)
DECLARE @SD2  DATETIME = DATEADD(D,-14,@DOCUMENTDATE)
DECLARE @SD3  DATETIME = DATEADD(D,-21,@DOCUMENTDATE)
DECLARE @SD4  DATETIME = DATEADD(D,-28,@DOCUMENTDATE)
DECLARE @SD5  DATETIME = DATEADD(D,-35,@DOCUMENTDATE)
DECLARE @SD6  DATETIME = DATEADD(D,-42,@DOCUMENTDATE)
DECLARE @SD7  DATETIME = DATEADD(D,-49,@DOCUMENTDATE)
DECLARE @SD8  DATETIME = DATEADD(D,-56,@DOCUMENTDATE)
DECLARE @SD9  DATETIME = DATEADD(D,-63,@DOCUMENTDATE)
DECLARE @SD10 DATETIME = DATEADD(D,-70,@DOCUMENTDATE)
DECLARE @LAST DATETIME = @SD10

IF @HISTORYTYPE = 'Last' 
   BEGIN
      SET @SD1 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @DOCUMENTDATE and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD1 IS NOT NULL SET @LAST = @SD1
      SET @SD2 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD1          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD2 IS NOT NULL SET @LAST = @SD2
      SET @SD3 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD2          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD3 IS NOT NULL SET @LAST = @SD3
      SET @SD4 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD3          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD4 IS NOT NULL SET @LAST = @SD4
      SET @SD5 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD4          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD5 IS NOT NULL SET @LAST = @SD5
      SET @SD6 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD5          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD6 IS NOT NULL SET @LAST = @SD6
      SET @SD7 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD6          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD7 IS NOT NULL SET @LAST = @SD7
      SET @SD8 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD7          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD8 IS NOT NULL SET @LAST = @SD8
      SET @SD9 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD8          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD9 IS NOT NULL SET @LAST = @SD9
      SET @SD10 = (SELECT TOP 1 [Document Date] FROM [Kitchen Fresh Foods$Sales Header Import] WHERE [Document Date] < @SD9          and [document type]= 1 AND [Sell-To Customer No_] = @CUSTOMER AND [Customer Location] = @LOCATION ORDER BY [Document Date] DESC )
      IF @SD10 IS NOT NULL SET @LAST = @SD10
   END

DECLARE @PL INTEGER = CASE @HISTORYTYPE WHEN 'Weekly' THEN 7 ELSE 1 END  --Length of History Period 1 day or 7 days
DECLARE @ED1 DATETIME  = DATEADD(D,@PL-1,@SD1)
DECLARE @ED2 DATETIME  = DATEADD(D,@PL-1,@SD2)
DECLARE @ED3 DATETIME  = DATEADD(D,@PL-1,@SD3)
DECLARE @ED4 DATETIME  = DATEADD(D,@PL-1,@SD4)
DECLARE @ED5 DATETIME  = DATEADD(D,@PL-1,@SD5)
DECLARE @ED6 DATETIME  = DATEADD(D,@PL-1,@SD6)
DECLARE @ED7 DATETIME  = DATEADD(D,@PL-1,@SD7) 
DECLARE @ED8 DATETIME  = DATEADD(D,@PL-1,@SD8) 
DECLARE @ED9 DATETIME  = DATEADD(D,@PL-1,@SD9) 
DECLARE @ED10 DATETIME = DATEADD(D,@PL-1,@SD10) 

IF OBJECT_ID('tempdb..#Temp1')         IS NOT NULL DROP TABLE #Temp1
IF OBJECT_ID('tempdb..#Temp2')         IS NOT NULL DROP TABLE #Temp2
IF OBJECT_ID('tempdb..#Temp3')         IS NOT NULL DROP TABLE #Temp3
IF OBJECT_ID('tempdb..#Temp4')         IS NOT NULL DROP TABLE #Temp4
IF OBJECT_ID('tempdb..#Temp5')         IS NOT NULL DROP TABLE #Temp5
IF OBJECT_ID('tempdb..#Temp6')         IS NOT NULL DROP TABLE #Temp6
IF OBJECT_ID('tempdb..#Temp7')         IS NOT NULL DROP TABLE #Temp7
IF OBJECT_ID('tempdb..#OrderHist')     IS NOT NULL DROP TABLE #OrderHist
IF OBJECT_ID('tempdb..#WasteHist')     IS NOT NULL DROP TABLE #WasteHist
IF OBJECT_ID('tempdb..#OrderWasteHist')IS NOT NULL DROP TABLE #OrderWasteHist

/*Get OrderHist*/
SELECT l.[item no_],
      'OrderQty1'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD1  AND @ED1  THEN l.[qty_ ordered] ELSE 0 END), 
      'OrderQty2'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD2  AND @ED2  THEN l.[qty_ ordered] ELSE 0 END), 
      'OrderQty3'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD3  AND @ED3  THEN l.[qty_ ordered] ELSE 0 END),
      'OrderQty4'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD4  AND @ED4  THEN l.[qty_ ordered] ELSE 0 END),
      'OrderQty5'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD5  AND @ED5  THEN l.[qty_ ordered] ELSE 0 END),
      'OrderQty6'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD6  AND @ED6  THEN l.[qty_ ordered] ELSE 0 END),
      'OrderQty7'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD7  AND @ED7  THEN l.[qty_ ordered] ELSE 0 END),
      'OrderQty8'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD8  AND @ED8  THEN l.[qty_ ordered] ELSE 0 END),
      'OrderQty9'  = SUM(CASE WHEN h.[Document Date] BETWEEN @SD9  AND @ED9  THEN l.[qty_ ordered] ELSE 0 END),
      'OrderQty10' = SUM(CASE WHEN h.[Document Date] BETWEEN @SD10 AND @ED10 THEN l.[qty_ ordered] ELSE 0 END)
INTO #OrderHist
FROM [Kitchen Fresh Foods$Sales Header Import] h 
join [Kitchen Fresh Foods$Sales line Import] l ON h.[Document Type] = l.[Document Type] AND h.[Document No_] = l.[Document No_] 
WHERE h.[Sell-To Customer No_] = @CUSTOMER AND 
      h.[Customer Location]    = @LOCATION AND 
      h.[Document Date] BETWEEN  CASE WHEN @HISTORYTYPE = '' THEN @DOCUMENTDATE ELSE @LAST END AND @DOCUMENTDATE
GROUP BY [Item No_]

/*Get Waste Hitory*/
 SELECT w.[item no_],
       'WasteQty1' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD1  AND @ED1  THEN w.[quantity] ELSE 0 END), 
       'WasteQty2' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD2  AND @ED2  THEN w.[quantity] ELSE 0 END), 
       'WasteQty3' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD3  AND @ED3  THEN w.[quantity] ELSE 0 END), 
       'WasteQty4' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD4  AND @ED4  THEN w.[quantity] ELSE 0 END), 
       'WasteQty5' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD5  AND @ED5  THEN w.[quantity] ELSE 0 END), 
       'WasteQty6' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD6  AND @ED6  THEN w.[quantity] ELSE 0 END), 
       'WasteQty7' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD7  AND @ED7  THEN w.[quantity] ELSE 0 END),
       'WasteQty8' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD8  AND @ED8  THEN w.[quantity] ELSE 0 END),
       'WasteQty9' = SUM(CASE WHEN w.[Waste Date] BETWEEN @SD9  AND @ED9  THEN w.[quantity] ELSE 0 END),
       'WasteQty10'= SUM(CASE WHEN w.[Waste Date] BETWEEN @SD10 AND @ED10 THEN w.[quantity] ELSE 0 END)
INTO #WasteHist
FROM [Kitchen Fresh Foods$Customer Waste] w 
WHERE w.[customer no_] = @CUSTOMER AND 
      w.[location no_] = @LOCATION AND 
      w.[Waste Date] BETWEEN  @LAST AND @DOCUMENTDATE
GROUP BY [Item No_]

SELECT 'Item No_' = ISNULL(a.[Item No_],ISNULL(w.[Item No_],'')),
      OrderQty1,OrderQty2,OrderQty3,OrderQty4,OrderQty5,OrderQty6,OrderQty7,OrderQty8,OrderQty9,OrderQty10,
      WasteQty1,WasteQty2,WasteQty3,WasteQty4,WasteQty5,WasteQty6,WasteQty7,WasteQty8,WasteQty9,WasteQty10
INTO #OrderWasteHist
FROM #OrderHist a
FULL JOIN #WasteHist w ON w.[item no_] = a.[Item No_]

SELECT [Item No_],
       [Menu Section],      
      0 as [Qty_ Ordered],
      'Item Menued' = CASE ISNULL([Item No_],'') WHEN '' THEN 0 ELSE 1 END 
INTO #Temp6
FROM [Kitchen Fresh Foods$Menu Cycle] mc 
WHERE [Menu Cycle] = @MENUCYCLE AND [Menu Sequence No_] = @MENUSEQNO

SELECT '' as [Menu Section],
      [Item No_],
      'Qty_ Ordered' = ISNULL([Qty_ Ordered],0),
      0 as [Item Menued]
INTO #Temp7
FROM [Kitchen Fresh Foods$Sales Line Import]
where [Document Type] = 1 AND [Document No_] = @DOCUMENTNO

SELECT mc.[Menu Section],
      ISNULL(mc.[Item No_],sl.[item no_]) as [item no_],
      'Qty_ Ordered' = ISNULL(sl.[Qty_ Ordered],0),
      'Item Menued' = CASE ISNULL(mc.[Item No_],'') WHEN '' THEN 0 ELSE 1 END 
INTO #Temp1
FROM #TEMP6 mc
FULL JOIN #temp7 sl ON mc.[Item No_] = sl.[Item No_]

/* Merge history */
SELECT 'Menu Section' = ISNULL(a.[Menu Section],'Z-HISTORY'), 
      'Item Menued'  = ISNULL(a.[item menued],0),
      'Item No_'     = ISNULL(a.[Item No_],ISNULL(b.[Item No_],'')),
      'Qty_ Ordered' = ISNULL(a.[Qty_ Ordered],0),
      OrderQty1=ISNULL(OrderQty1,0),OrderQty2=ISNULL(OrderQty2,0),OrderQty3=ISNULL(OrderQty3,0),OrderQty4=ISNULL(OrderQty4,0),OrderQty5=ISNULL(OrderQty5,0),
      OrderQty6=ISNULL(OrderQty6,0),OrderQty7=ISNULL(OrderQty7,0),OrderQty8=ISNULL(OrderQty8,0),OrderQty9=ISNULL(OrderQty9,0),OrderQty10=ISNULL(OrderQty10,0),
      WasteQty1=ISNULL(WasteQty1,0),WasteQty2=ISNULL(WasteQty2,0),WasteQty3=ISNULL(WasteQty3,0),WasteQty4=ISNULL(WasteQty4,0),WasteQty5=ISNULL(WasteQty5,0),
      WasteQty6=ISNULL(WasteQty6,0),WasteQty7=ISNULL(WasteQty7,0),WasteQty8=ISNULL(WasteQty8,0),WasteQty9=ISNULL(WasteQty9,0),WasteQty10=ISNULL(WasteQty10,0)
INTO #TEMP2
FROM #TEMP1 a
FULL JOIN #OrderWasteHist b ON a.[Item No_] = b.[Item No_]


/* Lookup item and customer fields*/
SELECT a.[item menued], 
      a.[Menu Section],
      a.[Item No_], 
      I.[Description],
       'Item Desc Long' = I.[Description],
       'Item Desc Short' = I.[Description 2],
      'Show on Menu from item' = cfi.[Show on Menu] ,
      a.[Qty_ Ordered],
      cfi.[Shelf Life (Days)],        
      cfi.[Sales Description Line 1], cfi.[Sales Description Line 2], cfi.[Sales Description Line 3], cfi.[Sales Description Line 4],
      OrderQty1,OrderQty2,OrderQty3,OrderQty4,OrderQty5,OrderQty6,OrderQty7,OrderQty8,OrderQty9,OrderQty10,
      WasteQty1,WasteQty2,WasteQty3,WasteQty4,WasteQty5,WasteQty6,WasteQty7,WasteQty8,WasteQty9,WasteQty10
INTO #Temp3 
FROM #temp2 a
LEFT JOIN [Kitchen Fresh Foods$Customer]                 c   ON c.[No_] = @CUSTOMER
LEFT JOIN [Kitchen Fresh Foods$Custom Fields - Item]     cfi ON cfi.[Item No_] = a.[Item No_]
LEFT JOIN [Kitchen Fresh Foods$Item]                     I   ON I.[No_] = a.[Item No_]

/* Determine "Master specific" item fields and bring in the menu lineno and removed switch*/
SELECT a.[Item No_], 
      a.[Show on Menu from item], 
      a.[item menued],
      a.[Qty_ Ordered],
      a.[Sales Description Line 1], a.[Sales Description Line 2],a.[Sales Description Line 3],a.[Sales Description Line 4],
       a.[item desc long],a.[item desc short],
      'Menu Section'       = CASE ISNULL(ci.[Menu Section],'')      WHEN '' THEN a.[Menu Section]      ELSE '0-' + ci.[Menu Section] END ,
      'Description'        = CASE ISNULL(ci.[Description Short],'') WHEN '' THEN a.[Description]       ELSE ci.[Description Short] END ,
      'Shelf Life (Days)'  = CASE ISNULL(ci.[Shelf Life (days)],0)  WHEN 0  THEN a.[Shelf Life (Days)] ELSE ci.[Shelf Life (days)] END ,
      'Price Code'         = ISNULL(ci.[Price Code],'') ,
      'PC Retail Value'    = ISNULL(ci.[Price Code Retail Value],0) ,
      'User Defined Code'  = ISNULL(ci.[User Defined Code],'') ,
      'Show on Menu'       = ISNULL(ci.[Show on Menu],a.[Show on Menu from item]) ,
      'Removed'            = ISNULL((SELECT DISTINCT ml.removed FROM [Kitchen Fresh Foods$Menu Lines] ml  WHERE ml.[Customer No_] = @CUSTOMER AND ml.[Menu Sequence No_]= @MENUSEQNO AND ml.[Item List Code] = '' AND  ML.[Item No_] =a.[Item No_]),0),
      OrderQty1,OrderQty2,OrderQty3,OrderQty4,OrderQty5,OrderQty6,OrderQty7,OrderQty8,OrderQty9,OrderQty10,
      WasteQty1,WasteQty2,WasteQty3,WasteQty4,WasteQty5,WasteQty6,WasteQty7,WasteQty8,WasteQty9,WasteQty10
INTO #temp4
FROM #temp3 a
LEFT JOIN [Kitchen Fresh Foods$Customer Items]  ci  ON ci.[customer no_] = @MASTER AND ci.[item no_] = a.[Item No_] AND ci.[Item List Code] = ''



/*
LEFT JOIN [Kitchen Fresh Foods$Menu Lines]      ml  ON ml.[Customer No_] = @LABELCUST AND ml.[Menu Sequence No_]= @MENUSEQNO AND ml.[Item List Code] = '' AND  ML.[Item No_] =a.[Item No_] 
*/

/* Determine "Customer Specific" item fields*/
SELECT a.[Item No_], 
       a.[Removed],
       a.[Show on Menu from item],
      a.[Qty_ Ordered], 
      a.[item menued],
      a.[Sales Description Line 1], a.[Sales Description Line 2],a.[Sales Description Line 3],a.[Sales Description Line 4],
       a.[item desc long],a.[item desc short],
      'Menu Section'      = CASE ISNULL(ci.[Menu Section],'')           WHEN '' THEN a.[Menu Section]      ELSE '0-' + ci.[Menu Section] END ,
      'Description'       = CASE ISNULL(ci.[Description Short],'')      WHEN '' THEN a.[Description]       ELSE ci.[Description Short] END ,
      'Shelf Life (Days)' = CASE ISNULL(ci.[Shelf Life (days)],0)       WHEN 0  THEN a.[Shelf Life (Days)] ELSE ci.[Shelf Life (days)] END ,
      'Price Code'        = CASE ISNULL(ci.[Price Code],'')             WHEN '' THEN a.[Price Code]        ELSE ci.[Price Code] END  ,
      'PC Retail Value'   = CASE ISNULL(ci.[Price Code Retail Value],0) WHEN 0  THEN a.[PC Retail Value]   ELSE ci.[Price Code Retail Value] END  ,      
      'User Defined Code' = CASE ISNULL(ci.[User Defined Code],'')      WHEN '' THEN a.[User Defined Code] ELSE ci.[User Defined Code] END  ,
      'Show on Menu'      = ISNULL(ci.[Show on Menu],a.[Show on Menu]) ,
      OrderQty1,OrderQty2,OrderQty3,OrderQty4,OrderQty5,OrderQty6,OrderQty7,OrderQty8,OrderQty9,OrderQty10,
      WasteQty1,WasteQty2,WasteQty3,WasteQty4,WasteQty5,WasteQty6,WasteQty7,WasteQty8,WasteQty9,WasteQty10
INTO #temp5
FROM #temp4 a
LEFT JOIN [Kitchen Fresh Foods$Customer Items]  ci  ON ci.[customer no_] = @CUSTOMER AND ci.[item no_] = a.[Item No_] AND ci.[Item List Code] = ''

/*
LEFT JOIN [Kitchen Fresh Foods$Customer Items]  ci  ON ci.[customer no_] = @LABELCUST AND ci.[item no_] = a.[Item No_] AND ci.[Item List Code] = ''
*/

/* Determine Customer Specific "List Code" item fields*/
SELECT a.[Item No_], 
      a.[item menued],
      a.[Sales Description Line 1], a.[Sales Description Line 2],a.[Sales Description Line 3],a.[Sales Description Line 4],
       a.[item desc long],a.[item desc short],
      'Qty'                 = a.[Qty_ Ordered],  
       'Line No_'            = CASE @ITEMLISTCODE WHEN '' THEN ISNULL((SELECT TOP 1 ml.[line no_] FROM [Kitchen Fresh Foods$Menu Lines] ml  WHERE ml.[Customer No_] = @CUSTOMER AND ml.[Menu Sequence No_]= @MENUSEQNO AND ml.[Item List Code] = @ITEMLISTCODE AND  ML.[Item No_] =a.[Item No_]),0) ELSE 0 END ,
       'Removed'             = CASE @ITEMLISTCODE WHEN '' THEN a.[removed]           ELSE ISNULL((SELECT DISTINCT ml.removed FROM [Kitchen Fresh Foods$Menu Lines] ml  WHERE ml.[Customer No_] = @CUSTOMER AND ml.[Menu Sequence No_]= @MENUSEQNO AND ml.[Item List Code] = @ITEMLISTCODE AND  ML.[Item No_] =a.[Item No_]),0) END ,
      'Menu Section'        = CASE @ITEMLISTCODE WHEN '' THEN a.[Menu Section]      ELSE CASE ISNULL(ci.[Menu Section],'') WHEN '' THEN a.[Menu Section] ELSE '0-' + ci.[Menu Section] END END ,
      'Description'         = CASE @ITEMLISTCODE WHEN '' THEN a.[Description]       ELSE CASE ISNULL(ci.[Description Short],'') WHEN '' THEN a.[Description] ELSE ci.[Description Short] END END ,
      'Shelf Life (Days)'   = CASE @ITEMLISTCODE WHEN '' THEN a.[Shelf Life (Days)] ELSE CASE ISNULL(ci.[Shelf Life (days)],0) WHEN 0 THEN a.[Shelf Life (Days)] ELSE ci.[Shelf Life (days)] END END ,
      'Price Code'          = CASE @ITEMLISTCODE WHEN '' THEN a.[Price Code]        ELSE ISNULL(ci.[Price Code],'') END  ,
      'PC Retail Value'     = CASE @ITEMLISTCODE WHEN '' THEN a.[PC Retail Value]   ELSE ISNULL(ci.[Price Code Retail Value],0) END  ,
      'User Defined Code'   = CASE @ITEMLISTCODE WHEN '' THEN a.[User Defined Code] ELSE ISNULL(ci.[User Defined Code],'') END  ,
      'Show on Menu'        = CASE @ITEMLISTCODE WHEN '' THEN a.[Show on Menu]      ELSE ISNULL(ci.[Show on Menu],a.[Show on Menu]) END ,
      'Tray Qty'            = ISNULL(UOM.[Qty_ per Unit of Measure],0),
      'Healthy'             = cfi.[healthy labeled],
      'Favorite'            = ISNULL(clf.[favorite],0),
      'Base Cost'           = ISNULL((select top 1 isnull([Unit Price],0) FROM [Kitchen Fresh Foods$sales Price] WHERE [Sales Type] = 1 AND [Sales Code] = @CUSTPRICEGRP AND [Item No_] = a.[Item No_] AND GETDATE() BETWEEN [Starting Date] AND [Ending Date]),0),
      'Suggested Retail Price'  = ISNULL((select top 1 isnull([Retail Price],0) FROM [Kitchen Fresh Foods$Retail Price] WHERE [Sales Type] = 1 AND [Sales Code] = @RTLPRICEGRP AND [Item No_] = a.[Item No_] AND GETDATE() BETWEEN [Starting Date] AND [Ending Date]),0),
      cfi.[Label Weight Type], cfi.[User Label Weight],i.[Net Weight],
      OrderQty1,OrderQty2,OrderQty3,OrderQty4,OrderQty5,OrderQty6,OrderQty7,OrderQty8,OrderQty9,OrderQty10,
      WasteQty1,WasteQty2,WasteQty3,WasteQty4,WasteQty5,WasteQty6,WasteQty7,WasteQty8,WasteQty9,WasteQty10,
      Date1=@SD1, Date2=@SD2, Date3=@SD3, Date4=@SD4, Date5=@SD5, Date6=@SD6, Date7=@SD7, Date8=@SD8, Date9=@SD9, Date10=@SD10
FROM #temp5 a
LEFT JOIN [Kitchen Fresh Foods$Customer Items]       ci  ON ci.[customer no_] = @CUSTOMER AND ci.[item no_] = a.[Item No_] AND ci.[Item List Code] = @ITEMLISTCODE
LEFT JOIN [Kitchen Fresh Foods$Item Unit of Measure] uom ON uom.[item no_]    = a.[Item No_] AND uom.Code ='TRAY'
LEFT JOIN [Kitchen Fresh Foods$Custom Fields - Item] cfi ON cfi.[Item No_]    = a.[Item No_]
LEFT JOIN [Kitchen Fresh Foods$Item]                   i ON i.[No_]           = a.[Item No_]
LEFT JOIN [Kitchen Fresh Foods$Customer Location Favorites] clf on clf.[Customer No_] = @CUSTOMER AND clf.[Location No_] = @LOCATION and clf.[item no_] = a.[Item No_]
ORDER BY [Menu Section], [Line No_],[Price Code],[Suggested Retail Price], a.[Item No_] 


/*LEFT JOIN [Kitchen Fresh Foods$Menu Lines]            ml ON ml.[Customer No_] = @CUSTOMER AND ml.[Menu Sequence No_]= @MENUSEQNO AND ml.[Item List Code] = @ITEMLISTCODE AND  ML.[Item No_] =a.[Item No_] */

/*'Line No_'            = CASE @ITEMLISTCODE WHEN '' THEN ISNULL(ml.[Line No_],0) ELSE 0 END ,*/
