GDSN Connect Export Full - v2.3.3 34e9af27-d4dd-4d07-8ee8-a1914c5244ec <_parameter_version>0C:\Users\hhoaglin\Desktop\Edison Export\GDSNDataExportFull.csvDelimitted44Always3434162c1d85f-d041-430b-9095-fd6d2ac0fd30<_parameter_version>0SHOW:allHIDE:SHOW:0HIDE:allSHOW:AllTimeSHOW:TodaySHOW:-1SHOW:all:3HIDE:0SHOW:SHOW:SELECT DISTINCT CASE WHEN ItemSource = '0' THEN '' WHEN ItemSource = '1'THEN [ITEM_EXTERNAL_IDENTIFIER].ExternalIdentifier END AS [DEX_ID], CASE WHEN ItemSource = '0' THEN '' WHEN ItemSource = '1' THEN CASE WHEN CHARINDEX('_',ExternalIdentifier) > 0 THEN SUBSTRING(ExternalIdentifier, 1, CHARINDEX('_', ExternalIdentifier + '_') - 1) ELSE '' END END AS [DEX_Local_Menu_ID], [CATALOG_NUMBER].[CatalogNumber] AS [Catalog_Number], [ITEM_USAGE_CATEGORY].Description AS [Item_Usage_Category], [ITEM_USAGE_TYPE].Description AS [Item_Usage_Type], [ITEM_STORAGE_CATEGORY].Description AS [Storage_Category], [ORDERING_CATEGORY].Description AS [Ordering_Category], [TAX_CATEGORY].Description AS [Tax_Category], '' AS [GTIN_Base], [CATALOG_NUMBER].[Description] AS [Item_Name_Menu], CONVERT(varchar(128),BRAND.Description) AS [Brand_Name], '' AS [Manufacturer], [ITEM].[ProductCode] AS [Product_Code], '' AS [Item_Image_URL], '' AS [CN Label_Number], '' AS [CN Label_Statement], '' AS [CN Label_Expiration_Date], '' AS [CN Label_File_URL], '' AS [Product_Formulation_Statement], '' AS [PFS_File_URL], '' AS [Serving_Size_Description], CONVERT(varchar(128),[NutritionQuantity]) AS [Serving_Size_Weight], CONVERT(varchar(128),[BASE_UNIT].Description) AS [Serving_Size_Weight_UOM], [CN_Code] AS [CN_Code], CASE WHEN [CN_Code] IS NULL THEN 'Local' WHEN [CN_Code] IS NOT NULL THEN 'USDA' END AS [Nutrient_Source], '' AS [Serving_Size_Measure], '' AS [Serving_Size_Measure_UOM], '' AS [Meal_Credit_Serving_Size], '' AS [Meal_Credit_Serving_Size_UOM], '' AS [Meal_Credits], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '208' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Calories_kcal], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '204' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Total_Fat_g], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '605' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Trans_Fat_g], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '606' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Sat_Fat_g], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '601' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Cholesterol_mg], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '307' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Sodium_mg], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '306' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Potassium_mg], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '303' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Iron_mg], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '205' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Total_Carbs_g], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '291' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Dietary_Fiber_g], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '269' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Total_Sugar_g], '' AS [Added_Sugar_g], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '203' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Protein_g], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '301' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Calcium_mg], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '318' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Vitamin_A_IU], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '401' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Vitamin_C_mg], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '328' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Vitamin_D_mcg], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '255' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Moisture_g], CONVERT(varchar(128),(SELECT Amount FROM ITEM_NUTRIENT INNER JOIN NUTRIENT ON ITEM_NUTRIENT.NutrientID = NUTRIENT.NUTRIENT_RECID WHERE NUTRIENT.CN_Nutrient_Code = '207' AND ITEM_NUTRIENT.ItemID in (SELECT ITEM.ITEM_RECID))) AS [Ash_g], '' AS [Ingredient_Statement], STUFF((SELECT '||' + Allergen.Description FROM ALLERGEN LEFT JOIN ITEM_ALLERGEN ON ALLERGEN.ALLERGEN_RECID = ITEM_ALLERGEN.AllergenID WHERE ITEM_ALLERGEN.ItemID IN (SELECT ITEM.ITEM_RECID) FOR XML PATH ('')),1,2,'') AS [Allergen_Claims], --CONVERT(varchar(128),(SELECT STRING_AGG(DESCRIPTION, '||') --FROM ALLERGEN --INNER JOIN ITEM_ALLERGEN --ON ALLERGEN.ALLERGEN_RECID = ITEM_ALLERGEN.AllergenID --WHERE ITEM_ALLERGEN.ItemID IN (SELECT ITEM.ITEM_RECID))) AS [Allergen_Claims], '' AS [Country_Of_Origin], '' AS [GPC_Code], '' AS [Storage_Instructions], '' AS [Max_Storage_Temperature], '' AS [Min_Storage_Temperature], '' AS [Directions_And_Notes], CASE WHEN CHARINDEX('_',ExternalIdentifier) > 0 THEN SUBSTRING(ExternalIdentifier, CHARINDEX('_', ExternalIdentifier) + 1, LEN(ExternalIdentifier)) ELSE '' END AS [DEX_Local_Item_ID], CONVERT(varchar(128),Item.GlobalUID) AS [District_Item_ID], CONVERT(varchar(128),CATALOG_Number.Description) AS [Item_Name_Inventory], CONVERT(varchar(128),CATALOG_Number.Description) AS [Item_Name_Vendor], CONVERT(varchar(128), CASE WHEN BulkBase_UnitID IS NULL THEN 'Normal' WHEN BulkBase_UnitID IS NOT NULL THEN 'Bulk' END) AS [Purchase_Unit_Type], -- AS [BulkBase_Unit], -- where to get unitid '' AS [GTIN_PU], [SITE].[SiteName] AS [Vendor_Name], VendorItemNumber AS [Vendor_Product_ID], STUFF((SELECT '||' + CONVERT(varchar(1000),DateEffective) + ',' + CONVERT(varchar(1000),PurchasedCasePrice) + ',' + CONVERT(varchar(1000),DonatedValuePerCase) FROM Price_Contract PC LEFT JOIN Price_Contract_Item PCI ON PC.PRICE_CONTRACT_RECID = PCI.Price_ContractID WHERE DateExpires > convert(date,getdate()) AND PCI.ItemID IN (SELECT ITEM.ITEM_RECID) FOR XML PATH('')),1,2,'') AS [Vendor_Price], --CONVERT(varchar(1000), (SELECT STRING_AGG(CONVERT(varchar(1000),DateEffective) + ',' + CONVERT(varchar(1000),PurchasedCasePrice) + ',' + CONVERT(varchar(1000),DonatedValuePerCase) + ',' + '','||') --FROM Price_Contract PC --INNER JOIN Price_Contract_Item PCI --ON PC.PRICE_CONTRACT_RECID = PCI.Price_ContractID --WHERE DateExpires > convert(date,getdate()) --AND PCI.ItemID IN (SELECT ITEM.ITEM_RECID))) AS [Vendor_Price], CONVERT(varchar(128),Item.Usable_Grams) AS [Usable_Weight_Per_Primary_Unit_g], CONVERT(varchar(128),Item.Pieces_Per_Unit) AS [Pieces_Per_Primary_Unit], CONVERT(varchar(128),Item.UnitDescription) AS [Primary_Unit], CONVERT(varchar(128),'') AS [Servings_Per_Inventory_Unit], CONVERT(varchar(128),Item.CaseSize) AS [Case_Size], '' AS [Menuable_Item], -- create a recipe if it's a 1 and done? CASE WHEN PreferenceSequence = '1' THEN '1' ELSE '0' END AS [Primary Item] FROM ITEM LEFT JOIN PRICE_CONTRACT_ITEM ON PRICE_CONTRACT_ITEM.ItemID = Item.ITEM_RECID LEFT JOIN PRICE_CONTRACT ON PRICE_CONTRACT_ITEM.Price_ContractID = PRICE_CONTRACT.PRICE_CONTRACT_RECID LEFT JOIN CATALOG_NUMBER ON Item.Catalog_NumberID = CATALOG_NUMBER.CATALOG_NUMBER_RECID LEFT JOIN BRAND ON Item.BrandID = BRAND.BRAND_RECID LEFT JOIN ITEM_USAGE_CATEGORY ON CATALOG_NUMBER.Item_Usage_CategoryID = ITEM_USAGE_CATEGORY.ITEM_USAGE_CATEGORY_RECID LEFT JOIN ITEM_USAGE_TYPE ON ITEM_USAGE_TYPE.ITEM_USAGE_TYPE_RECID = ITEM_USAGE_CATEGORY.Item_Usage_TypeID LEFT JOIN BASE_UNIT ON ITEM.NutritionBase_UnitID = BASE_UNIT.BASE_UNIT_RECID LEFT OUTER JOIN PARTIAL_USE_UNIT ON Item.Partial_Use_UnitID = PARTIAL_USE_UNIT.PARTIAL_USE_UNIT_RECID LEFT JOIN ITEM_STORAGE_CATEGORY ON CATALOG_NUMBER.Item_Storage_CategoryID = ITEM_STORAGE_CATEGORY.ITEM_STORAGE_CATEGORY_RECID LEFT JOIN ORDERING_CATEGORY ON CATALOG_NUMBER.Ordering_CategoryID = ORDERING_CATEGORY.ORDERING_CATEGORY_RECID LEFT JOIN TAX_CATEGORY ON CATALOG_NUMBER.Tax_CategoryID = TAX_CATEGORY.TAX_CATEGORY_RECID LEFT JOIN [SITE] on [PRICE_CONTRACT].[FromSiteID] = [SITE].SITE_RECID LEFT JOIN [ITEM_EXTERNAL_IDENTIFIER] ON ITEM_EXTERNAL_IDENTIFIER.ItemID = ITEM.ITEM_RECID AND ITEM_EXTERNAL_IDENTIFIER.ExternalIdentifierType = '1' WHERE LEFT(CATALOG_NUMBER.CatalogNumber,2) <> 'LO' AND LEFT(Catalog_Number.Description,8) <> 'Leftover' AND ITEM.Inactive = '0' AND BRAND.Description <> 'Manufactured'0SHOW:0---