USE ILS
GO

IF OBJECT_ID('dbo.KC_CONSOLIDATE_EXP_CAP', 'TF') IS NOT NULL
    DROP FUNCTION dbo.KC_CONSOLIDATE_EXP_CAP
GO
CREATE FUNCTION dbo.KC_CONSOLIDATE_EXP_CAP (
      @lpn nvarchar(32)
    , @str varchar(8) = ''
)

/*  Author : Michael Knyazev
    Create Date: -
    Modify: Lestat Kim
    Modify date: 19-04-2017
    Assignment: Consolidate - Exclude differing lots - EpirationDate
    Description: Консолидация по ОСГ только для безразмерной ячейки
    Example: select * from dbo.KC_CONSOLIDATE_EXP_CAP ('LPN14032017')
*/

RETURNS @t TABLE (LOCATION nvarchar(25))
AS
BEGIN
    DECLARE @having_exp_classic nvarchar(1024) = '
        HAVING count(m.lpn) < m.capacity
            AND (sum(m.TOTAL_WEIGHT) + __m.TOTAL_WEIGHT) !> __m.MAXIMUM_WEIGHT
            AND ABS(DATEDIFF(
                    DAY, ISNULL( MIN(m.EXPIRATION_DATE), 0 ), __m.EXPIRATION_DATE
                )) < __m.shelf_life
            AND ABS(DATEDIFF(
                    DAY, ISNULL( MAX(m.EXPIRATION_DATE), 0 ), __m.EXPIRATION_DATE
                )) < __m.shelf_life
    ';
    DECLARE @having_exp_cap nvarchar(1024) = '
        HAVING count(m.lpn) < m.capacity
            AND (sum(m.TOTAL_WEIGHT) + __m.TOTAL_WEIGHT) !> __m.MAXIMUM_WEIGHT
            AND ABS(DATEDIFF(
                    DAY, ISNULL( MIN(m.EXPIRATION_DATE), 0 ), __m.EXPIRATION_DATE
                )) < __m.shelf_life
            AND ABS(DATEDIFF(
                    DAY, ISNULL( MAX(m.EXPIRATION_DATE), 0 ), __m.EXPIRATION_DATE
                )) < __m.shelf_life
    ';
    DECLARE @having_exp_l nvarchar(1024) = '
        HAVING count(m.lpn) < m.capacity
            AND (sum(m.TOTAL_WEIGHT) + __m.TOTAL_WEIGHT) !> __m.MAXIMUM_WEIGHT
            AND (sum(m.LENGTH) + __m.LENGTH) !>m.DEPTH
            AND ABS(DATEDIFF(
                    DAY, ISNULL( MIN(m.EXPIRATION_DATE), 0 ), __m.EXPIRATION_DATE
                )) < __m.shelf_life
            AND ABS(DATEDIFF(
                    DAY, ISNULL( MAX(m.EXPIRATION_DATE), 0 ), __m.EXPIRATION_DATE
                )) < __m.shelf_life
    ';
    DECLARE @having_permanent nvarchar(1024) = '
        HAVING count(m.lpn) < m.capacity
            AND (sum(m.TOTAL_WEIGHT) + __m.TOTAL_WEIGHT) !> __m.MAXIMUM_WEIGHT
            AND (sum(m.LENGTH) + __m.LENGTH) !>m.DEPTH
            AND ABS(DATEDIFF(
                    DAY, ISNULL( MIN(m.EXPIRATION_DATE), 0 ), __m.EXPIRATION_DATE
                )) < __m.shelf_life
            AND ABS(DATEDIFF(
                    DAY, ISNULL( MAX(m.EXPIRATION_DATE), 0 ), __m.EXPIRATION_DATE
                )) < __m.shelf_life
    ';

    DECLARE @sql nvarchar(max) = '
    WITH
        kc_lpn as (
            SELECT LPN, ERP_PLANT, LAYOUT
            FROM dbo.KC_LPN
        ),
        __li as (
            SELECT li.LOGISTICS_UNIT as lpn, li.LOCATION,
                li.ITEM, l.LOCATION_TYPE, li.COMPANY,
                li.EXPIRATION_DATE, i.ITEM_CATEGORY10,
                lt.HEIGHT, lt.MAXIMUM_WEIGHT, li.TOTAL_WEIGHT
            FROM LOCATION_INVENTORY as li
                JOIN LOCATION l on li.LOCATION = l.LOCATION
                    AND l.ACTIVE = ''Y''
                    AND l.MULTI_ITEM = ''N''
                    AND l.LOCATION_CLASS != ''FROZEN''
                JOIN LOCATION_TYPE as lt on l.LOCATION_TYPE = lt.LOCATION_TYPE
                JOIN ITEM as i on li.ITEM = i.ITEM
        ),
        gen_shelf_life as (
            SELECT gcd.USER1VALUE as location_type,
                gcd.USER2VALUE as item_cat10, gcd.USER3VALUE as shelf_life
            FROM GENERIC_CONFIG_DETAIL as gcd
            WHERE RECORD_TYPE = ''KC_SHELF_LIFE_COMP''
        ),
        gen_loc_pallet_type as (
            SELECT USER1VALUE as loc_type,
                USER2VALUE as typ, USER3VALUE as capacity
            FROM GENERIC_CONFIG_DETAIL
            WHERE RECORD_TYPE = ''KC_LOCATION_PALLET_TYPE''
        ),
        gen_pallet_layout as (
            SELECT IDENTIFIER as layout,
                USER1VALUE as typ, USER4VALUE as height
            FROM GENERIC_CONFIG_DETAIL
            WHERE RECORD_TYPE = ''KC_PALLET_LAYOUT''
        ),
        li___gen_loc_pallet_type as (
            SELECT __li.ITEM, __li.lpn,
                g.capacity
            FROM __li
                JOIN (
                    SELECT distinct g.loc_type, g.capacity
                    FROM gen_loc_pallet_type as g
                )g on __li.LOCATION_TYPE = g.loc_type
        ),
        kc_lpn___gen_pallet_layout___gen_log_pallet_type as(
            SELECT k.LPN, k.ERP_PLANT,
                k.LAYOUT, gpl.typ, gpl.height
            FROM kc_lpn as k
                JOIN gen_pallet_layout as gpl on k.LAYOUT = gpl.layout
                JOIN gen_loc_pallet_type as glpt on gpl.typ = glpt.typ
        ),
        gcd_depth as (
            SELECT USER1VALUE as loc_type,
                USER2VALUE as DEPTH
            FROM GENERIC_CONFIG_DETAIL
            WHERE RECORD_TYPE = ''KC_DEPTH''
        ),
        cont as (
            SELECT LENGTH as LENGTH,
               CONTAINER_TYPE as PALLET_TYPE
            FROM CONTAINER_TYPE
        ),

        main as (
            SELECT-- DISTINCT
                l.lpn, l.ITEM, l.COMPANY, --l.LOCATION_TYPE
                l.LOCATION,
                l.EXPIRATION_DATE, l.HEIGHT, l.MAXIMUM_WEIGHT, l.TOTAL_WEIGHT,
                O1.LAYOUT, O1.typ, O1.height as __height, O1.ERP_PLANT,
                O2.capacity,
                sl.item_cat10, sl.shelf_life,
                GD.depth,
                CONT.LENGTH

            FROM __li as l
                JOIN kc_lpn___gen_pallet_layout___gen_log_pallet_type as O1
                    ON l.lpn = O1.LPN
                JOIN li___gen_loc_pallet_type as O2 ON l.lpn = O2.lpn
                JOIN gen_shelf_life as sl ON l.LOCATION_TYPE = sl.location_type
                    AND l.ITEM_CATEGORY10 = sl.item_cat10
                JOIN gcd_depth as GD on l.LOCATION_TYPE = GD.loc_type
                JOIN cont as CONT on 01.typ=CONT.PALLET_TYPE
        ),
        __main as (
            SELECT DISTINCT *
            FROM main
            WHERE lpn = @lpn
        ),
        final as (
            SELECT DISTINCT m.LOCATION
            FROM main AS m
                JOIN __main AS __m ON m.ITEM = __m.ITEM
                    AND m.HEIGHT !< __m.__height
                    AND m.COMPANY = __m.COMPANY
                    AND m.ERP_PLANT = __m.ERP_PLANT
                    AND m.typ = __m.typ
            WHERE m.lpn != @lpn
            GROUP BY m.capacity, m.LOCATION, __m.TOTAL_WEIGHT, __m.MAXIMUM_WEIGHT,
                __m.EXPIRATION_DATE, __m.shelf_life
            __HAVING
        )

        SELECT *
        FROM final ;
    '

    IF @str = 'EXP_CAP'
        SET @str = REPLACE(@str, '__HAVING', @having_exp_cap)

    ELSE IF @str = 'EXP_L'
        SET @str = REPLACE(@str, '__HAVING', @having_exp_l)

    ELSE IF @str = 'PERMANENT'
        SET @str = REPLACE(@str, '__HAVING', @having_permanent)

    ELSE
        SET @str = REPLACE(@str, '__HAVING', @having_exp_classic);


    INSERT @t
        EXEC @str;

    RETURN
END;
GO