declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @skuId int = ; declare @userId int = ; declare @augmentOwnerId int = ; declare @augmentOwnerType varchar(3) = ; declare @augmentSkuId int = ; with missing_fields as ( select s.skuId, s.additionalDesc, s.hasChildNutrition from tbl_PRD_Skus s with (nolock) where s.ownerid = @ownerId and s.fsl_TableCode = @ownerType and s.skuId = @skuId ), claimData as ( select #_col# = case when nc.#_col# = '1' then '#_label#' else null end , from tbl_PRD_NutritionalClaims nc with (nolock) where nc.ownerid = @augmentOwnerId and nc.fsl_tableCode = @augmentOwnerType and nc.prdId = @augmentSkuId and nc.prdLevel = 'SKU' ), nutritionalData as ( select @skuId as skuId, hasNutritionData = cast( case when exists ( select 1 from tbl_PRD_Nutrition n with (nolock) where n.ownerid = @augmentOwnerId and n.fsl_TableCode = @augmentOwnerType and n.linkId = @augmentSkuId and n.nutType = 'SKU' ) then 'Y' else 'N' end as CHAR(1)) ), customerSkusPre as ( select *, case when parent_cdr_recordId is null then cdr_recordId else parent_cdr_recordId end as parentGrpId from [tbl_DW_CustomerSKUs#_tableSuffix#] cs with (nolock) where cs.skuId = @skuId and ( 1 = 2 or ( marketsCovered like '%###_tid#;%' ) ) ), customerSkus as ( select cs.cdr_recordId customerId, cs.cdr_dstName customerName, cs.customerType, cs.customerSku, cdr_dstCompanyType as dstCompanyType, cs.parentGrpId, row_number() over ( partition by cs.parentGrpId, cs.customersku order by cs.parentGrpId, cs.customersku, cs.cdr_dstCompanyType desc ) rowNum from customerSkusPre cs with (nolock) ), skus as ( SELECT ph.skuId, ph.sku, ph.skuDesc, ph.packSizeDesc, ph.specsheetKey, concat(ph.sku, '-', ph.skuDesc) as skuFull, case when f.skuId is not null then 'Y' else 'N' end userFavorite, ph.discontinued, ph.focusProduct, case when ph.dotCode is not null then 'Y' else 'N' end availableViaDot, coalesce( ph.hasChildNutrition, 'N' ) hasChildNutrition, coalesce( nd.hasNutritionData, 'N' ) hasNutritionalData, ph.mfr_name, ph.mfr_id, ph.product, ph.shortDesc, case when ph.specsheetKey is not null and ph.mfrSKUId is not null then 'Y' else 'N' end as specsheetAvailable case when ph.specsheetKey is not null then 'Y' else 'N' end as specsheetAvailable ,ph.master_gross_weight ,ph.master_gross_weight_unit ,ph.master_net_weight ,ph.master_net_weight_unit ,ph.master_cube ,ph.master_cube_unit ,ph.master_unit_size_desc ,ph.master_height ,ph.master_height_unit ,ph.master_width ,ph.master_width_unit ,ph.master_length ,ph.master_length_unit ,ph.pallet_tie ,ph.pallet_high ,ph.pallet_extra ,ph.catch_weight ,ph.catch_weight_range ,ph.gtin ,ph.upc ,pkg.replaces_mfr_part_num ,pkg.pallet_cube ,pkg.pallet_cube_unit ,pkg.servings_per_case ,pkg.shelf_life ,pkg.shelf_life_ambient ,pkg.shelf_life_refrig ,pkg.shelf_life_frozen ,pkg.shelf_life_at_shipping ,ph.mfrLogoPath, ph.mfrIconPath , json_query( php.affiliationsJSON ) as affiliations ,ph.brand ,ph.brandId ,bLogo.contentPath as brandLogoPath ,bImage.contentPath as brandImagePath ,ph.dotCode ,ph.dotStockingStatus ,pkg.country_of_origin ,ph.catnum ,ph.category ,ph.skuUnitPrice_USD ,ph.qtyUOM ,mis.additionalDesc ,'#_prdSpecSheet#' as prdSpecLink ,'#_prdChildSpecSheet#' as prdCchildSpecLink ,'#variables.token#' as skuToken , (select distinct rtrim(m.memoType) memoType, mt.description memoDescription , m.memoData, m.memoSubType from tbl_PRD_Memo m with (nolock) inner join tbl_PRD_MemoTypes mt with (nolock) on m.memoType = mt.code where m.ownerid = @augmentOwnerId and m.fsl_TableCode = @augmentOwnerType and m.linkType = 'SKU' and m.linkId = @augmentSkuId order by mt.description, m.memoSubType for json path ) as memoData , ( select img.imageCode, img.fileSize, img.fileExt, img.uploadDate, img.contentId, img.recordSource, imgPath.contentPath as imagePath, case img.imageCode when 'MstrPkg' then 'Master Packaging' when 'SubPkg' then 'Sub Unit Packaging' when 'UnitPkg' then 'Unit Packaging' when 'Label' then 'Label' when 'OutOfPackage' then 'Out Of Package' when 'HiRes' then 'High Resolution' when 'HiResBS' then 'High Resolution Beauty Shot' when 'Img1' then 'Primary Image' when 'Img2' then 'Secondary Image' when 'Img3' then 'Third Image' when 'Img4' then 'Fourth Image' when 'Img5' then 'Fifth Image' when 'Img6' then 'Sixth Image' when 'Img7' then 'Seventh Image' when 'Img8' then 'Eight Image' when 'Img9' then 'Ninth Image' when 'Img10' then 'Tenth Image' when 'FactPanel' then 'Nutritional Fact Panel' when 'BC_UPCA' then 'UPCA BARCODE' when 'BC_ITF14' then 'ITF-14 BARCODE' when 'INTERNALVIEW' then 'Internal View' when 'INTERNALVIEW2' then 'Internal View 2' when 'INTERNALVIEW3' then 'Internal View 3' else REPLACE(img.imageCode, '_', ' ') end AS imageTitle, case img.imageCode when 'MstrPkg' then 110 when 'SubPkg' then 120 when 'UnitPkg' then 130 when 'Label' then 140 when 'OutOfPackage' then 150 when 'HiRes' then 160 when 'HiResBS' then 170 when 'Img1' then 10 when 'Img2' then 20 when 'Img3' then 30 when 'Img4' then 40 when 'Img5' then 50 when 'Img6' then 60 when 'Img7' then 70 when 'Img8' then 80 when 'Img9' then 90 when 'Img10' then 100 when 'FactPanel' then 160 when 'BC_UPCA' then 170 when 'BC_ITF14' then 180 when 'INTERNALVIEW' then 190 when 'INTERNALVIEW2' then 200 when 'INTERNALVIEW3' then 220 else 999 end AS imageSortOrder from tbl_PRD_Images img with (nolock) inner join qrySecureContentFullPathLookup imgPath with (nolock) on img.contentId = imgPath.contentId where ownerid = @augmentOwnerId and ownerType = @augmentOwnerType and linkType = 'SKU' and img.linkId = @augmentSkuId order by imageSortOrder for json path ) as imageData , ( select distinct * from( select #_col# as nutritionalClaim from claimData union ) as x for json path ) as nutritionalClaimData , ( select k12NutritionLabel = case when nullif(child.Bread_BreadAlt_UnitPerSrv,'') is not null then 'Bread/Bread Alt' when nullif(child.Fruit_VegetableAlt_UnitPerSrv,'') is not null then 'Fruit/Vegetable Alt' when nullif(child.FruitAlt_UnitPerSrv,'') is not null then 'Fruit Serving' when nullif(child.Grain_UnitPerSrv,'') is not null then 'Grain' when nullif(child.Meat_MeatAlt_UnitPerSrv,'') is not null then 'Meat/Meat Alt' when nullif(child.Milk_UnitPerSrv,'') is not null then 'Milk' when nullif(child.Vegetable_UnitPerSrv,'') is not null then 'Vegetable Serving' when nullif(child.WholeGrain_UnitPerSrv,'') is not null then 'Whole Grain' end, k12NutritionUnit = case when nullif(child.Bread_BreadAlt_UnitPerSrv,'') is not null then child.Bread_BreadAlt_UnitPerSrv when nullif(child.Fruit_VegetableAlt_UnitPerSrv,'') is not null then child.Fruit_VegetableAlt_UnitPerSrv when nullif(child.FruitAlt_UnitPerSrv,'') is not null then child.FruitAlt_UnitPerSrv when nullif(child.Grain_UnitPerSrv,'') is not null then child.Grain_UnitPerSrv when nullif(child.Meat_MeatAlt_UnitPerSrv,'') is not null then child.Meat_MeatAlt_UnitPerSrv when nullif(child.Milk_UnitPerSrv,'') is not null then child.Milk_UnitPerSrv when nullif(child.Vegetable_UnitPerSrv,'') is not null then child.Vegetable_UnitPerSrv when nullif(child.WholeGrain_UnitPerSrv,'') is not null then child.WholeGrain_UnitPerSrv end, k12NutritionMeasure = case when nullif(child.Bread_BreadAlt_UnitPerSrv,'') is not null then child.Bread_BreadAlt_UnitMeasure when nullif(child.Fruit_VegetableAlt_UnitPerSrv,'') is not null then child.Fruit_VegetableAlt_UnitMeasure when nullif(child.FruitAlt_UnitPerSrv,'') is not null then child.FruitAlt_UnitMeasure when nullif(child.Grain_UnitPerSrv,'') is not null then child.Grain_UnitMeasure when nullif(child.Meat_MeatAlt_UnitPerSrv,'') is not null then child.Meat_MeatAlt_UnitMeasure when nullif(child.Milk_UnitPerSrv,'') is not null then child.Milk_UnitMeasure when nullif(child.Vegetable_UnitPerSrv,'') is not null then child.Vegetable_UnitMeasure when nullif(child.WholeGrain_UnitPerSrv,'') is not null then child.WholeGrain_UnitMeasure end, child.Vegetable_Color from tbl_PRD_NutritionChild child with (nolock) where child.ownerid = @augmentOwnerId and child.fsl_tableCode = @augmentOwnerType and child.linkId = @augmentSkuId and child.nutType = 'SKU' for json path ) as K12Nutrition , ( select allergenText, LevelofContainment from tbl_PRD_AllergenInfo a with (nolock) left outer join tbl_PRD_AllergenTypes aat with (nolock) on aat.allergenId = a.allergenId where a.ownerid = @augmentOwnerId and a.ownerType = @augmentOwnerType and a.linkId = @augmentSkuId and a.linkType = 'SKU' order by a.LevelofContainment, aat.allergenText for json path ) as Allergens , ( select * from customerSkus with (nolock) where rowNum = 1 order by customerName, customerSKU select cs.customerId, cdr.cdr_dstname as customerName, cs.customerType, cs.customerSku, dst.dstCompanyType, case when dst.dstCompanyType = 'P' then dstId when dst.dstCompanyType = 'B' and ISNULL(dstparentCo, 0) = 0 then dstId else dst.dstparentCo end as parentGrpId from tbl_PRD_CustomerSkus cs with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.fsl_tablecode = cs.fsl_tableCode and cdr.ownerid = cs.ownerId and cdr.cdr_recordid = cs.customerid inner join tbl_DST_Distributors dst on dst.dstId = cdr.fsl_dstId where cs.fsl_tableCode = and cs.ownerId = and cs.skuid = and (cs.customertype = 'pdst' or cs.customertype = 'dst') ) as x ) as y where y.RowNum = 1 order by customerName ---> for json path, include_null_values ) as customerSkus FROM tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) left outer join tbl_BCRM_UserItemFavorites f with (nolock) on f.ownerId = ph.ownerId and f.ownerType = ph.ownerType and f.skuId = ph.skuId and f.fspro_userId = @userId left outer join tbl_DW_ProductHierarchyPaths#_tableSuffix# php with (nolock) on php.ownerId = ph.ownerId and php.ownerType = ph.ownerType and php.catalogType = 'SKU' and php.catalogId = ph.skuId left outer join tbl_prd_packaging pkg with (nolock) on pkg.linkid = ph.skuId and pkg.linktype = 'sku' and pkg.ownerid = ph.ownerId and pkg.fsl_TableCode = ph.ownerType left outer join tbl_MFR_Brands b with (nolock) on b.brand_id = ph.brandid left outer join qrySecureContentFullPathLookup bImage with (nolock) on b.image = bImage.contentid left outer join qrySecureContentFullPathLookup bLogo with (nolock) on b.logo = bLogo.contentid left outer join missing_fields mis on mis.skuId = ph.skuId left outer join nutritionalData nd on nd.skuId = ph.skuId WHERE ph.OwnerID = @ownerId AND ph.ownerType = @ownerType and ph.crmActive = 'Y' and ph.skuid = @skuId ) #forJSON( 'skus', attributes.dataStore, 'sku' )#