WITH missing_fields AS (
    SELECT
        s.skuId,
        s.privateLabelType,
        s.opr_id,
        s.dst_id,
        s.sortRank,
        s.specsheetImg,
        s.foodservice_rewards,
        s.star_awards,
        s.value_plus,
        s.cool_school,
        s.real_rewards_cafe,
        s.brand_points_plus
    FROM tbl_PRD_SKus s WITH (NOLOCK)
    WHERE
        s.ownerId = 1943
        AND s.fsl_tableCode = 'BRO'
        AND s.crmActive = 'Y'
        AND s.mfrid = 2402
        -- AND (COALESCE(s.opr_id, 0) > 0 OR COALESCE(s.dst_id, 0) > 0)
)

SELECT DISTINCT
    0 AS rowNumber,
    s.mfr_id,
    s.mfr_name,
    s.skuId,
    s.sku,
    s.mfrSkuId,
    s.skuDesc,
    s.packSizeDesc,
    s.discontinued,
    s.dotStockingStatus,
    s.privateLabelType,
    sku0.specsheetImg,
    sku0.sortRank,
    CASE
        WHEN sku0.sortRank < 99 THEN 1
        ELSE 2
    END AS priorityItem,
    CASE
        WHEN s.focusproduct = 'y' OR prd.focusproduct = 'y' THEN 'Y'
        ELSE 'N'
    END AS focusProduct,
    sku0.foodservice_rewards,
    sku0.star_awards,
    sku0.value_plus,
    sku0.cool_school,
    sku0.real_rewards_cafe,
    sku0.brand_points_plus
    -- FROM tbl_PRD_Skus s WITH (NOLOCK)
FROM tbl_DW_ProductHierarchy_BRO1943 s WITH (NOLOCK)
LEFT OUTER JOIN tbl_DW_ProductHierarchyPaths_BRO1943 prd WITH (NOLOCK)
    ON prd.catalogType = ' PRD'
    AND prd.catalogId = s.prodid
/* INNER JOIN tblManufacturers m WITH (NOLOCK)
    ON m.mfr_id = s.mfr_Id */
LEFT OUTER JOIN tbl_PRD_CustomerSKUs cs WITH (NOLOCK)
    ON cs.skuid = s.skuid
    AND NOT EXISTS (
        SELECT *
        FROM fsenablers.dbo.tbl_APP_Configurations WITH (NOLOCK)
        WHERE
            applicationCode = 'PRD'
            AND ownerid = s.mfr_id
            AND ownertype = 'MFR'
            AND parameterName = 'display_dot_codes'
            AND parameterValue = 'false'
    )
LEFT OUTER JOIN tbl_prd_memo memo WITH (NOLOCK)
    ON memo.linkid = s.skuid
    AND memo.linktype = 'sku'
    AND memo.memotype = 'D001'
LEFT OUTER JOIN missing_fields sku0
    ON sku0.skuId = s.skuId
WHERE
    s.ownerId IN (
        2396, 2402, 313, 221, 158, 2491, 4973, 4839, 9, 1370, 45, 61, 4384, 125, 136,
        1297, 1428, 869, 2323, 537, 3225, 3658, 226, 276, 2159, 241, 1866, 104, 926,
        78, 1543, 72, 2158, 245, 1560, 455, 1237, 450, 1, 2529, 1351, 734, 1099, 18,
        26, 725, 2078, 1214, 1168, 2830, 563, -1
    )
    AND s.ownerType = 'MFR'
    AND EXISTS (
        SELECT *
        FROM tbl_OFS_BrokerSkus WITH (NOLOCK)
        WHERE skuid = s.skuid
    )
    AND s.discontinued = 'N'
ORDER BY
    s.mfr_Name,
    priorityItem,
    s.sku,
    s.skuDesc;
-- --->