r_history_master_product

--Adrian, Oct 30
--History Master Product

CREATE OR REPLACE FUNCTION r_history_master_product(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, BIGINT, CHARACTER VARYING, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefDetail              REFCURSOR := 'refDetail';

    pSessionId              ALIAS FOR $1;
    pTenantId               ALIAS FOR $2;
    pUserId                 ALIAS FOR $3;
    pRoleId                 ALIAS FOR $4;
    pDatetime               ALIAS FOR $5;

    pProductCode            ALIAS FOR $6;
    pProductName            ALIAS FOR $7;
    pSubCtgrProductId       ALIAS FOR $8;
    pGroupBrand             ALIAS FOR $9;
    pBrandId                ALIAS FOR $10;
    pSerialNumber           ALIAS FOR $11;
    pLotNumber              ALIAS FOR $12;
    pExpiredDate            ALIAS FOR $13;

    vYes                    character varying(1) := 'Y';
    vNo                     character varying(1) := 'N';
    vEmptyString            character varying(1) := '';
    vRawProductDataType     character varying(4) := 'RAW1';
    vRawBalanceDataType     character varying(4) := 'RAW2';
    vRawReceiveDataType         character varying(4) := 'RAW3';
    vGoodDataType           character varying(4) := 'GOOD';
    vFilterProductCode      character varying := '';
    vFilterProductName      character varying := '';
    vFilterSubCtgrProduct   character varying := '';
    vFilterGroupBrand       character varying := '';
    vFilterBrand            character varying := '';
    vFilterSerialNumber     character varying := '';
    vFilterLotNumber        character varying := '';
    vFilterExpiredDate      character varying := '';
    vEmptyId                bigint := -99;
    vDocTypeRG              bigint := 111;
    vAll                    character varying := 'ALL';

BEGIN

    -- RESET TABLE TEMP
    DELETE FROM tt_product WHERE session_id = pSessionId;
    DELETE FROM tt_r_product_list WHERE session_id = pSessionId;

    -- CREATE FILTER PRODUCT CODE
    IF (pProductCode<>vEmptyString) THEN
        vFilterProductCode := ' AND UPPER(A.product_code) LIKE UPPER(''%'||pProductCode||'%'') ';
    END IF;

    -- CREATE FILTER PRODUCT NAME
    IF (pProductName<>vEmptyString) THEN
        vFilterProductName := ' AND UPPER(A.product_name) LIKE UPPER(''%'||pProductName||'%'') ';
    END IF;

    -- CREATE FILTER SUB CTGR PRODUCT
    IF (pSubCtgrProductId<>vEmptyId) THEN
        vFilterSubCtgrProduct := ' AND A.sub_ctgr_product_id = '||pSubCtgrProductId||' ';
    END IF;

    -- CREATE FILTER GROUP BRAND
    IF (pGroupBrand<>vEmptyString) THEN
        vFilterGroupBrand := ' AND COALESCE(B.group_brand, '''||vEmptyString||''') = '''||pGroupBrand||''' ';
    END IF;

    -- CREATE FILTER BRAND
    IF (pBrandId<>vEmptyId) THEN
        vFilterBrand := ' AND A.brand_id = '||pBrandId||' ';
    END IF;

    -- CREATE FILTER SERIAL NUMBER
    IF (pSerialNumber<>vEmptyString) THEN
        vFilterSerialNumber := ' AND UPPER(Y.serial_number) LIKE UPPER(''%'||pSerialNumber||'%'') ';
    END IF;

    -- CREATE FILTER LOT NUMBER
    IF (pLotNumber<>vEmptyString) THEN
        vFilterLotNumber := ' AND Y.lot_number = '''||pLotNumber||''' AND (TRIM(Z.serial_number) <> '||vEmptyString||' AND TRIM(Z.lot_number) <> '||vEmptyString||') ';
    END IF;

    -- CREATE FILTER EXPIRED DATE
    IF (pExpiredDate<>vEmptyString) THEN
        vFilterExpiredDate := ' AND Y.product_expired_date <= '''||pExpiredDate||''' ';
    END IF;

    -- SIAPKAN DATA PRODUCT SESUAI FILTER --
    EXECUTE '
        INSERT INTO tt_product(
            session_id, product_id, tenant_id, product_code, product_name,
            sub_ctgr_product_id, brand_id)
        SELECT $1, A.product_id, A.tenant_id, A.product_code, A.product_name,
            A.sub_ctgr_product_id, A.brand_id
        FROM m_product A
        WHERE A.tenant_id = $2
        AND EXISTS (
            SELECT 1 FROM m_brand_ext B
            WHERE A.brand_id = B.brand_id '
            || vFilterGroupBrand || '
        ) AND EXISTS (
            SELECT 1 FROM in_product_balance Y
            WHERE A.product_id = Y.product_id '
            || vFilterSerialNumber
            || vFilterLotNumber
            || vFilterExpiredDate || '
        ) '
        || vFilterProductCode
        || vFilterProductName
        || vFilterSubCtgrProduct
        || vFilterBrand
    USING pSessionId, pTenantId, vEmptyString;

    -- INIT RAW DATA PRODUCT
    EXECUTE '
        INSERT INTO tt_r_product_list(
                session_id, type_data, num_row,
                product_id, product_code, product_name,
                sub_ctgr_product_id, sub_ctgr_product_code,
                sub_ctgr_product_name,
                group_brand,
                group_brand_parent,
                brand_id, brand_code, brand_name,
                color, size,
                product_balance_id,
                serial_number, lot_number, product_expired_date,
                rcv_date)
        SELECT $1, $3, ROW_NUMBER () OVER (PARTITION BY A.product_id) AS num_row,
                A.product_id, A.product_code, A.product_name,
                A.sub_ctgr_product_id, f_get_sub_ctgr_product_code(A.sub_ctgr_product_id) AS sub_ctgr_product_code,
                f_get_sub_ctgr_product_name(A.sub_ctgr_product_id) AS sub_ctgr_product_name,
                COALESCE(B.group_brand, $4),
                COALESCE(f_get_group_brand_parent_by_group_brand(B.group_brand), $4) AS group_brand_parent,
                A.brand_id, f_get_brand_code(A.brand_id) AS brand_code,
                f_get_brand_name(A.brand_id) AS brand_name,
                COALESCE(C.color, $4) AS color, COALESCE(C.size, $4) AS size,
                $5,
                $4, $4, $4,
                $4
        FROM tt_product A
        LEFT JOIN m_brand_ext B ON A.brand_id = B.brand_id
        LEFT JOIN m_product_custom C ON A.product_id = C.product_id
        WHERE A.session_id = $1'
        || vFilterGroupBrand
    USING pSessionId, pTenantId, vRawProductDataType, vEmptyString, vEmptyId;

    -- INIT RAW DATA BALANCE
    EXECUTE '
        INSERT INTO tt_r_product_list(
                session_id, type_data, num_row,
                product_id, product_code, product_name,
                sub_ctgr_product_id, sub_ctgr_product_code,
                sub_ctgr_product_name,
                group_brand,
                group_brand_parent,
                brand_id, brand_code, brand_name,
                color, size,
                product_balance_id,
                serial_number, lot_number, product_expired_date,
                rcv_date)
        SELECT $1, $3, ROW_NUMBER () OVER (PARTITION BY Y.product_id ORDER BY Y.product_expired_date) AS num_row,
                Y.product_id, $4, $4,
                $5, $4,
                $4,
                $4,
                $4,
                $5, $4, $4,
                $4, $4,
                Y.product_balance_id,
                Y.serial_number, Y.lot_number, Y.product_expired_date,
                $4
        FROM in_product_balance Y
        INNER JOIN tt_product B ON Y.product_id = B.product_id
        WHERE B.session_id = $1 '
        || vFilterSerialNumber
        || vFilterLotNumber
        || vFilterExpiredDate
    USING pSessionId, pTenantId, vRawBalanceDataType, vEmptyString, vEmptyId;

    -- INIT RAW DATA RECEIVE
    INSERT INTO tt_r_product_list(
            session_id, type_data, num_row,
            product_id, product_code, product_name,
            sub_ctgr_product_id, sub_ctgr_product_code,
            sub_ctgr_product_name,
            group_brand,
            group_brand_parent,
            brand_id, brand_code, brand_name,
            color, size,
            product_balance_id,
            serial_number, lot_number, product_expired_date,
            rcv_date)
    SELECT pSessionId, vRawReceiveDataType, ROW_NUMBER () OVER (PARTITION BY A.product_id) AS num_row,
            A.product_id, vEmptyString, vEmptyString,
            vEmptyId, vEmptyString,
            vEmptyString,
            vEmptyString,
            vEmptyString,
            vEmptyId, vEmptyString, vEmptyString,
            vEmptyString, vEmptyString,
            A.product_balance_id,
            vEmptyString, vEmptyString, vEmptyString,
            SUBSTR(MIN(A.create_datetime), 1, 8)
    FROM in_log_product_balance_stock A
    INNER JOIN tt_product B ON A.product_id = B.product_id
    WHERE B.session_id = pSessionId
    AND A.doc_type_id = vDocTypeRG
    GROUP BY A.product_id, A.product_balance_id;

    -- INSERT GOOD DATA
    WITH product AS (
        SELECT * FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawProductDataType -- RAW PRODUCT
    ), product_balance AS (
        SELECT * FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawBalanceDataType -- RAW BALANCE
    ), product_receive AS (
        SELECT * FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawReceiveDataType -- RAW RECEIVE
    ), product_and_balance AS (
        SELECT COALESCE(A.session_id, B.session_id) AS session_id,
        row_number() over(PARTITION BY COALESCE(A.product_id, B.product_id) ORDER BY COALESCE(A.num_row, B.num_row)) AS num_row,
        COALESCE(A.product_id, B.product_id) AS product_id, A.product_code, A.product_name,
        A.sub_ctgr_product_id, A.sub_ctgr_product_code,
        A.sub_ctgr_product_name,
        A.group_brand,
        A.group_brand_parent,
        A.brand_id, A.brand_code, A.brand_name,
        A.color, A.size,
        COALESCE(B.product_balance_id, vEmptyId) AS product_balance_id,
        COALESCE(B.serial_number, vEmptyString) AS serial_number, COALESCE(B.lot_number, vEmptyString) AS lot_number,
        COALESCE(B.product_expired_date, vEmptyString) AS product_expired_date
        FROM product A
        FULL OUTER JOIN product_balance B ON A.product_id = B.product_id AND A.num_row = B.num_row
    ), product_balance_receive AS (
        SELECT COALESCE(A.session_id, B.session_id) AS session_id,
        row_number() over(PARTITION BY COALESCE(A.product_id, B.product_id) ORDER BY COALESCE(A.num_row, B.num_row)) AS num_row,
        COALESCE(A.product_id, B.product_id) AS product_id, A.product_code, A.product_name,
        A.sub_ctgr_product_id, A.sub_ctgr_product_code,
        A.sub_ctgr_product_name,
        A.group_brand,
        A.group_brand_parent,
        A.brand_id, A.brand_code, A.brand_name,
        A.color, A.size,
        A.product_balance_id,
        A.serial_number, A.lot_number,
        A.product_expired_date,
        COALESCE(B.rcv_date, vEmptyString) AS rcv_date
        FROM product_and_balance A
        LEFT JOIN product_receive B ON A.product_id = B.product_id AND A.product_balance_id = B.product_balance_id
    )
    INSERT INTO tt_r_product_list(
            session_id, type_data, num_row,
            product_id, product_code, product_name,
            sub_ctgr_product_id, sub_ctgr_product_code,
            sub_ctgr_product_name,
            group_brand,
            group_brand_parent,
            brand_id, brand_code, brand_name,
            color, size,
            product_balance_id,
            serial_number, lot_number, product_expired_date,
            rcv_date)
    SELECT pSessionId, vGoodDataType, ROW_NUMBER () OVER (PARTITION BY A.product_id ORDER BY num_row),
            A.product_id, A.product_code, A.product_name,
            A.sub_ctgr_product_id, A.sub_ctgr_product_code,
            A.sub_ctgr_product_name,
            A.group_brand,
            A.group_brand_parent,
            A.brand_id, A.brand_code, A.brand_name,
            A.color, A.size,
            A.product_balance_id,
            A.serial_number, A.lot_number, A.product_expired_date,
            A.rcv_date
    FROM product_balance_receive A;

    -- HAPUS DATA RAW
    DELETE FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawBalanceDataType;
    DELETE FROM tt_r_product_list WHERE session_id = pSessionId AND type_data = vRawReceiveDataType;

    Open pRefHeader FOR
        SELECT CASE WHEN (pProductCode <> vEmptyString) THEN
                    pProductCode
                ELSE
                    vAll
                END AS product_code,
                CASE WHEN (pProductName <> vEmptyString) THEN
                    pProductName
                ELSE
                    vAll
                END AS product_name,
                CASE WHEN (pSubCtgrProductId <> vEmptyId) THEN
                    f_get_sub_ctgr_product_name(pSubCtgrProductId)
                ELSE
                    vAll
                END AS sub_ctgr_product_name,
                CASE WHEN (pGroupBrand <> vEmptyString) THEN
                    pGroupBrand
                ELSE
                    vAll
                END AS group_brand,
                CASE WHEN (pBrandId <> vEmptyId) THEN
                    f_get_brand_code(pBrandId) || ' - ' || f_get_brand_name(pBrandId)
                ELSE
                    vAll
                END AS brand,
                CASE WHEN (pSerialNumber <> vEmptyString) THEN
                    pSerialNumber
                ELSE
                    vAll
                END AS serial_number,
                CASE WHEN (pLotNumber <> vEmptyString) THEN
                    pLotNumber
                ELSE
                    vAll
                END AS lot_number,
                CASE WHEN (pExpiredDate <> vEmptyString) THEN
                    pExpiredDate
                ELSE
                    vEmptyString
                END AS expired_date,
                f_get_username(pUserId) AS username,
                SUBSTR(pDatetime, 1, 8) AS print_date;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR

        SELECT A.product_id, A.product_code, A.product_name,
            A.sub_ctgr_product_id, A.sub_ctgr_product_code,
            A.sub_ctgr_product_name,
            A.group_brand,
            A.group_brand_parent,
            A.brand_id, A.brand_code, A.brand_name,
            A.color, A.size AS product_size,
            A.product_balance_id,
            A.serial_number, A.lot_number, A.product_expired_date,
            A.rcv_date
        FROM tt_r_product_list A
        INNER JOIN tt_product C ON C.product_id = A.product_id
        WHERE A.session_id = pSessionId
        AND A.session_id = C.session_id
        AND A.type_data = vGoodDataType
        ORDER BY C.product_code, C.product_name, A.product_expired_date, A.num_row;

    RETURN NEXT pRefDetail;

    -- RESET TABLE TEMP
    DELETE FROM tt_product WHERE session_id = pSessionId;
    DELETE FROM tt_r_product_list WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  /