Page Comparison

IS_CROSS_DOCK, PICK_TO_ZERO, IS_MORNING - эти поля определяют, подтянет ли SKU из контракта диаграмма соответствующего типа.
Сама диаграмма в таблицу RPM_SKU_STORE_PARAMS изменения не вносит.

    select top 100000
        ssp.SKU_ID, ssp.STORE_ID, ssp.PARCEL, ssp.CAPACITY, ssp.MIN_ORDER_QTY, 
        ssc.CONTRACT_ID, c.CONTRACT_NAME, sup.SUPPLIER_ID, sup.SUPPLIER_NAME, s.STORE_NAME, 
        p.ARTICLE_NAME, ssm.EXT_MANAGER_ID, convert(varchar, ssp.UPDATED, 121) as UPDATED, 
        ssp.UPDATED_BY, me1.NAME as UPDATED_BY_NAME, me.NAME as MANAGER_NAME, 
        p.ITEM_CODE, ls.QUANTITY as LATEST_STOCK, ib.SUB_SKU_ID, ib1.MASTER_SKU_ID,
        ssp.IS_ORDER, ssp.IS_PRODUCTION, ssp.IS_SHELF_LIMIT, p.SHELF_DAYS,
        (coalesce(pr.PRESENTATION_1, 0) + coalesce(pr.PRESENTATION_2, 0)) as PRESENTATION,
        (select sum(RAPI.QUANTITY)
        from RPM_ADD_PRESENTATION AS AP
        JOIN RPM_ADD_PRESENTATION_ITEMS RAPI ON RAPI.ID=AP.ID
        WHERE AP.IS_ACTIVE=1
        AND RAPI.SKU_ID = ssp.SKU_ID
        AND RAPI.STORE_ID = ssp.STORE_ID
        AND getdate() BETWEEN AP.START_DATE AND AP.END_DATE) as ADD_PRESENTATION_NUM,
        ass.SALES_VOLUME as AVG_SALES, l7ds.SALES_VOLUME LAST_SALES,
        (select max(d.ID) from RPM_DIAGRAM d where d.SPECIFICATION_ID=ssc.CONTRACT_ID 
               and d.STORE_ID=ssp.STORE_ID
               and d.PUSH_ID is null and d.END_DATE is null) DIAGRAM_ID,
        ssp.GUARANTEED_ORDER, ssp.IS_CROSS_DOCK, ssp.PICK_TO_ZERO, ssp.IS_MORNING,
        case when src.SKU_ID is not null then 1 else 0 end as AM, ssp.ADD_CYCLE,
        ssp.PLUS_D1D2FCST, ssp.ROUND_FACTOR, ssp.WRITEOFF_LIMIT,
        ssp.SHELF_LIFE_PERC, ssp.MAX_INCREASE_PERC,
        case when fm.model like '%group_new%' then 1
             when fm.model like '%reg_alloc%' then 2
             else 0 end IS_NEW
    from RPM_SKU_STORE_PARAMS ssp with (nolock)
    inner join PRODUCTS p on ssp.SKU_ID = p.SKU_ID 
          and (upper(p.ARTICLE_NAME) like '%:sku_name%' or upper(p.ITEM_CODE)
             like '%:sku_name%')
    inner join STORES s on s.STORE_ID = ssp.STORE_ID
    left join SKU_STORE_CONTRACT_V ssc on ssc.SKU_ID = ssp.SKU_ID
          and ssc.STORE_ID = ssp.STORE_ID
    left join CONTRACTS c on c.CONTRACT_ID = ssc.CONTRACT_ID
    left join SUPPLIERS sup on sup.SUPPLIER_ID = c.SUPPLIER_ID
    left join SKU_STORE_MANAGER ssm on ssm.SKU_ID = ssp.SKU_ID 
          and ssm.STORE_ID = ssp.STORE_ID
    left join MANAGER_EXT me on me.ID = ssm.EXT_MANAGER_ID
    left join MANAGER_EXT me1 on me1.ID = ssp.UPDATED_BY
    left join LATEST_STOCK ls on ls.SKU_ID = ssp.SKU_ID and ls.STORE_ID = ssp.STORE_ID
    left join FCST_SKU_MEAS fm on ssp.SKU_ID=fm.SKU_ID and ssp.STORE_ID=fm.STORE_ID
               and fm.ID=(select max(id) from fcst where loaded is not null)
    left join RPM_PRESENTATION pr on pr.SKU_ID = ssp.SKU_ID and pr.STORE_ID = ssp.STORE_ID
    left join AVG_SALES_SKU ass on ass.SKU_ID = ssp.SKU_ID and ass.STORE_ID = ssp.STORE_ID
    left join (select distinct SUB_SKU_ID from IF_BOM) ib on ib.SUB_SKU_ID = ssp.SKU_ID
    left join (select distinct MASTER_SKU_ID from IF_BOM) ib1 
          on ib1.MASTER_SKU_ID = ssp.SKU_ID
    left join LAST_DAYS_SALES l7ds on l7ds.SKU_ID = ssp.SKU_ID 
          and l7ds.STORE_ID = ssp.STORE_ID
    left join SKU_RANGE_CURRENT src on src.SKU_ID = ssp.SKU_ID 
          and src.STORE_ID = ssp.STORE_ID
    where ssp.STORE_ID [store]
      and ssp.SKU_ID [skus]
      and (:manager = -999 or ssm.EXT_MANAGER_ID = :manager)
      and (c.SUPPLIER_ID [suppliers] or -10 [suppliers])
      and (c.CONTRACT_ID like ':contract' or upper(c.CONTRACT_NAME) 
          like ':contract' or ':contract' = '%')
      and (:is_in_range in (-999, 0, 2) or src.SKU_ID is not null)
      and (:is_in_range in (-999, 1, 2) or src.SKU_ID is null)
      and (:is_new = -999
           or (:is_new = 0 and fm.model not like '%group_new%' and fm.model 
              not like '%reg_alloc%')
           or (:is_new = 1 and fm.model like '%group_new%')
           or (:is_new = 2 and fm.model like '%reg_alloc%') )
      and (:is_in_range in (-999, 0, 1)
           or (src.SKU_ID is not null
               and not exists (select 1 from rpm_orders_v o
                   where o.order_date >= cast(getdate() as date) 
               and o.sku_id=ssp.SKU_ID)))

    select PICK_TO_ZERO
    from RPM_SKU_STORE_PARAMS where sku_id=:sku_id and store_id=:store_id;

    select SKU_ID, MIN_ORDER_QTY, PARCEL,
        (case
            when exists (select 1 from SKU_RANGE
                where STORE_ID = :store_id and SKU_ID = ssp.SKU_ID
                    and START_DATE <= cast(getdate() as date)
                    and (END_DATE >= cast(getdate() as date) or END_DATE is null))
            then 1 else 0
            end) as IS_IN_RANGE
    from RPM_SKU_STORE_PARAMS ssp where SKU_ID [skus] and STORE_ID=:store_id

    select ID, NAME from MANAGER_EXT

   select CONTRACT_ID ID, CONTRACT_NAME NAME from CONTRACTS;

    select st.STORE_ID,st.STORE_NAME,
           p.SKU_ID, case when p.ITEM_CODE is null then p.ARTICLE_NAME 
           else concat('(', cast(p.ITEM_CODE as varchar), ') ', p.ARTICLE_NAME) end
           as ARTICLE_NAME,
        (case when
            exists(
                select 1 from SKU_RANGE
                where STORE_ID = st.STORE_ID and SKU_ID = p.SKU_ID
                    and START_DATE <= cast(getdate() as date)
                    and (END_DATE >= cast(getdate() as date) or END_DATE is null)
                )
        then 1 else 0 end) as IS_AM,
        ssc.CONTRACT_ID,
        ssc.PACKAGE_CONTENT,
        ssc.IS_ORDER,
        c.CONTRACT_NAME,
        s.SUPPLIER_ID,
        s.SUPPLIER_NAME
    from STORES st
    join PRODUCTS p on p.SKU_ID [skus]
      or p.ITEM_CODE '[item_codes]'
    left join SKU_STORE_CONTRACT_V ssc on ssc.SKU_ID = p.SKU_ID
        and ssc.STORE_ID = st.STORE_ID
    left join CONTRACTS c on c.CONTRACT_ID = ssc.CONTRACT_ID
    left join SUPPLIERS s on c.SUPPLIER_ID = s.SUPPLIER_ID
    where st.STORE_ID [stores]

    select d.ID as DIAGRAM_ID, coalesce(di.SKU_ID, 
        ssc.SKU_ID, ssc2.SKU_ID) as SKU_ID, d.STORE_ID,
        (select convert(varchar, min(ORDER_DATE), 121) from RPM_CALENDAR
         where DIAGRAM_ID = d.ID and IS_ACTIVE='1' and ORDER_DATE >= cast(getdate() as date)
        ) as CLOSEST_ORDER
    from RPM_DIAGRAM d
    left join RPM_DIAGRAM_ITEMS di on d.ID = di.DIAGRAM_ID
        and d.ALL_SKUS = '0'
    left join SKU_STORE_CONTRACT_V ssc on d.SPECIFICATION_ID = ssc.CONTRACT_ID
        and d.STORE_ID = ssc.STORE_ID
        and d.ALL_SKUS = '1'
        and coalesce(d.WAREHOUSE_TYPE, '0') != 'c'
    left join SKU_STORE_CONTRACT_V ssc2 on d.SPECIFICATION_ID = ssc2.CONTRACT_ID
        and d.STORE_ID = ssc2.STORE_ID
        and d.ALL_SKUS = '1'
        and d.WAREHOUSE_TYPE = 'c'
        and ssc2.IS_CROSSDOCK = 1
    join products p on coalesce(di.SKU_ID, ssc.SKU_ID, ssc2.SKU_ID) = p.SKU_ID
    where (coalesce(di.SKU_ID, ssc.SKU_ID, ssc2.SKU_ID) [skus]
           or p.ITEM_CODE '[item_codes]')
        and d.STORE_ID [stores]
        and d.START_DATE <= cast(getdate() as date)
        and (d.END_DATE >= cast(getdate() as date) or d.END_DATE is null)

    select ssp.SKU_ID, ssp.STORE_ID
    from RPM_SKU_STORE_PARAMS ssp
    where ssp.SKU_ID [skus] and ssp.STORE_ID [stores]

    update ssp
    set PARCEL = :parcel, MIN_ORDER_QTY = :min_order_qty, UPDATED = getdate(),
        UPDATED_BY = :user_id, IS_ORDER = :is_order, IS_PRODUCTION = :is_production,
        IS_SHELF_LIMIT = :is_shelf_limit, GUARANTEED_ORDER = :guar_order,
        IS_CROSS_DOCK = :is_cross_dock, PICK_TO_ZERO = :pick_to_zero,
        IS_MORNING = :is_morning, ADD_CYCLE = :add_cycle, PLUS_D1D2FCST = :plus_d1d2_fcst,
        ROUND_FACTOR = :round_factor, WRITEOFF_LIMIT = :writeoff_limit,
                       SHELF_LIFE_PERC = :shelf_life_perc,
        MAX_INCREASE_PERC = :max_increase_perc, CAPACITY = :capacity
    from RPM_SKU_STORE_PARAMS ssp
    where ssp.SKU_ID = :sku_id and ssp.STORE_ID = :store_id

    merge select st.STORE_ID,st.STORE_NAME,
           p.SKU_ID, case when p.ITEM_CODE is null then p.ARTICLE_NAME 
  into RPM_SKU_STORE_PARAMS ssp
    using (select :sku_id SKU_ID, :store_id STORE_ID) src
        elseon concat('(', cast(p.ITEM_CODE as varchar), ') ', p.ARTICLE_NAME) end(ssp.SKU_ID = src.SKU_ID and ssp.STORE_ID = src.STORE_ID)
    when matched then
     as ARTICLE_NAME,  update set PARCEL = :parcel, MIN_ORDER_QTY  (case when
 = :min_order_qty, UPDATED = getdate(),
        UPDATED_BY  exists(
     = :user_id, IS_ORDER = :is_order, IS_PRODUCTION = :is_production,
        IS_SHELF_LIMIT  select 1 from SKU_RANGE
       = :is_shelf_limit, GUARANTEED_ORDER = :guar_order,
        where STORE_IDIS_CROSS_DOCK = st.STORE_ID and SKU_ID = p.SKU_ID:is_cross_dock, PICK_TO_ZERO = :pick_to_zero,
        IS_MORNING = :is_morning,
        ADD_CYCLE = and START_DATE <= cast(getdate() as date):add_cycle, PLUS_D1D2FCST = :plus_d1d2_fcst,
        ROUND_FACTOR = :round_factor,
        WRITEOFF_LIMIT =  and (END_DATE >= cast(getdate() as date) or END_DATE is null):writeoff_limit, SHELF_LIFE_PERC = :shelf_life_perc,
        MAX_INCREASE_PERC = :max_increase_perc, CAPACITY = :capacity
    when not )matched then
       then 1insert else(
0 end) as IS_AM,         ssc.CONTRACTSKU_ID, STORE_ID, PARCEL, MIN_ORDER_QTY, UPDATED, UPDATED_BY,
   ssc.PACKAGE_CONTENT,         ssc.IS_ORDER, IS_PRODUCTION, IS_SHELF_LIMIT, GUARANTEED_ORDER, IS_CROSS_DOCK,
    c.CONTRACT_NAME,         s.SUPPLIER_ID,
 PICK_TO_ZERO, IS_MORNING, ADD_CYCLE, PLUS_D1D2FCST, 
     s.SUPPLIER_NAME     from STORES st
ROUND_FACTOR, WRITEOFF_LIMIT,
   join PRODUCTS p on p.SKU_ID [skus]       or p.ITEM_CODE '[item_codes]'SHELF_LIFE_PERC, MAX_INCREASE_PERC, CAPACITY
    left join SKU_STORE_CONTRACT_V ssc on ssc.SKU_ID = p.SKU_ID )
       and ssc.STORE_ID = st.STORE_IDvalues (
     left join CONTRACTS c on c.CONTRACT_ID = ssc.CONTRACT_ID
    left join SUPPLIERS s on c.SUPPLIER_ID = s.SUPPLIER_ID:sku_id, :store_id, :parcel, :min_order_qty, getdate(), :user_id,
           where st.STORE_ID [stores]