r_summary_mutation_stock

CREATE OR REPLACE FUNCTION r_summary_mutation_stock(character varying, bigint, bigint, character varying, bigint, character varying, bigint, bigint, 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;
    pDatetime                   ALIAS FOR $4;
    pOuId                       ALIAS FOR $5;
    pProductCodeName            ALIAS FOR $6;
    pCtgrProductId              ALIAS FOR $7;
    pSubCtgrProductId           ALIAS FOR $8;
    pBrandId                    ALIAS FOR $9;
    pStartDate                  ALIAS FOR $10;
    pEndDate                    ALIAS FOR $11;
    pFlgConsignment             ALIAS FOR $12;

    vLastClosingGeneralLedger   character varying := '';
    vStartDateLog               character varying := '';
    vEndDateLog                 character varying := '';
    vEmptyId                    bigint := -99;
    vEmptyValue                 character varying := '';
    vFilterOuId                 character varying := '';
    vFilterProduct              character varying := '';
    vFilterCtgrProductId        character varying := '';
    vFilterSubCtgrProductId     character varying := '';
    vFilterBrandId              character varying := '';
    vStartMonthlyQtyDate        character varying := '';
    vEndMonthlyQtyDate          character varying := '';
    vNo                         character varying := 'N';
    vYes                        character varying := 'Y';

BEGIN

    DELETE FROM tr_summary_mutation_stock WHERE session_id = pSessionId;

    -- last closing ledger sebelum pStartDate
    SELECT COALESCE(MAX(date_year_month), '') AS last_closing_gl INTO vLastClosingGeneralLedger
    FROM m_admin_process_ledger
    WHERE tenant_id = pTenantId AND
        ou_id = pOuId AND
        ledger_code = 'GL' AND
        status_ledger = '1' AND
        date_year_month <= SUBSTRING(pStartDate, 1, 6);

    -- tanggal awal untuk transaksi diantara tgl saldo awal sampai sebelum pStartDate
    vStartDateLog = vLastClosingGeneralLedger || '01';
    SELECT TO_CHAR(TO_DATE(pStartDate,'YYYYMMDD') + interval '-1 Day','YYYYMMDD') INTO vEndDateLog;

    IF pOuId <> vEmptyId THEN
        vFilterOuId := ' AND A.ou_id = ' || pOuId;
    END IF;

    IF pProductCodeName <> vEmptyValue THEN
        vFilterProduct := ' AND (UPPER(B.product_code) LIKE UPPER(''%' || pProductCodeName || '%'') OR UPPER(B.product_name) LIKE UPPER(''%' || pProductCodeName || '%'')) ';
    END IF;

    IF pCtgrProductId <> vEmptyId THEN
        vFilterCtgrProductId := ' AND B.ctgr_product_id = ' || pCtgrProductId;
    END IF;

    IF pSubCtgrProductId <> vEmptyId THEN
        vFilterSubCtgrProductId := ' AND B.sub_ctgr_product_id = ' || pSubCtgrProductId;
    END IF;

    IF pBrandId <> vEmptyId THEN
        vFilterBrandId := ' AND B.brand_id = ' || pBrandId;
    END IF;

    -- insert from log product
    IF pFlgConsignment = 'N' THEN
        EXECUTE '
        INSERT INTO tr_summary_mutation_stock(
            session_id, tenant_id, ou_id, product_id, product_code, product_name,
            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation
        )
        SELECT $1, $2, $3, A.product_id, A.product_code, A.product_name,
                A.product_balance_id, 0, 0, SUM(A.mutasi_minus), SUM(A.mutasi_positif), 0
        FROM (
            SELECT A.product_id, B.product_code, B.product_name,
                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif
            FROM in_log_product_balance_stock A
            INNER JOIN m_product B ON A.product_id = B.product_id
            INNER JOIN m_product_custom C ON B.product_id = C.product_id
            WHERE A.doc_date BETWEEN $4 AND $5
                AND A.ou_id = $3
                AND C.flg_buy_konsinyasi = $6 ' ||
                vFilterProduct ||
                vFilterCtgrProductId ||
                vFilterSubCtgrProductId ||
                vFilterBrandId ||
            ' UNION ALL
            SELECT A.product_id, B.product_code, B.product_name,
                    A.product_balance_id, 0, 0, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif
            FROM in_log_product_consignment_balance_stock A
            INNER JOIN m_product B ON A.product_id = B.product_id
            INNER JOIN m_product_custom C ON B.product_id = C.product_id
            WHERE A.doc_date BETWEEN $4 AND $5
                AND A.ou_id = $3
                AND C.flg_buy_konsinyasi = $6 ' ||
                vFilterProduct ||
                vFilterCtgrProductId ||
                vFilterSubCtgrProductId ||
                vFilterBrandId ||
            ' ) A
            GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id '
        USING pSessionId, pTenantId, pOuId, pStartDate, pEndDate, vNo;

    ELSE
        EXECUTE '
        INSERT INTO tr_summary_mutation_stock(
            session_id, tenant_id, ou_id, product_id, product_code, product_name,
            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation
        )
        SELECT $1, $2, $3, A.product_id, A.product_code, A.product_name,
                A.product_balance_id, 0, 0, SUM(A.mutasi_minus), SUM(A.mutasi_positif), 0
        FROM (
            SELECT A.product_id, B.product_code, B.product_name,
                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif
            FROM in_log_product_balance_stock A
            INNER JOIN m_product B ON A.product_id = B.product_id
            INNER JOIN m_product_custom C ON B.product_id = C.product_id
            WHERE A.doc_date BETWEEN $4 AND $5
                AND A.ou_id = $3
                AND C.flg_buy_konsinyasi = $6
                AND A.doc_type_id <> 111 ' ||
                vFilterProduct ||
                vFilterCtgrProductId ||
                vFilterSubCtgrProductId ||
                vFilterBrandId ||
            ' UNION ALL
            SELECT A.product_id, B.product_code, B.product_name,
                    A.product_balance_id, ABS(CASE WHEN qty < 0 THEN qty ELSE 0 END) AS mutasi_minus,
                    CASE WHEN qty >= 0 THEN qty ELSE 0 END AS mutasi_positif
            FROM in_log_product_consignment_balance_stock A
            INNER JOIN m_product B ON A.product_id = B.product_id
            INNER JOIN m_product_custom C ON B.product_id = C.product_id
            WHERE A.doc_date BETWEEN $4 AND $5
                AND A.ou_id = $3
                AND C.flg_buy_konsinyasi = $6 ' ||
                vFilterProduct ||
                vFilterCtgrProductId ||
                vFilterSubCtgrProductId ||
                vFilterBrandId ||
            ' ) A
            GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id '
        USING pSessionId, pTenantId, pOuId, pStartDate, pEndDate, vYes;

    END IF;

    -- update nilai saldo awal qty
    IF pFlgConsignment = 'N' THEN
        WITH tt_in_summary_monthly_qty AS (
            SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty
            FROM in_summary_monthly_qty
            WHERE tenant_id = pTenantId
                AND doc_type_id = vEmptyId
                AND date_year_month = vLastClosingGeneralLedger
                AND ou_id = pOuId
            GROUP BY tenant_id, product_id, product_balance_id
        )
        UPDATE tr_summary_mutation_stock A
        SET last_balance_qty = B.qty,
            expectation = B.qty
        FROM tt_in_summary_monthly_qty B
        WHERE A.tenant_id = B.tenant_id
            AND A.product_id = B.product_id
            AND A.product_balance_id = B.product_balance_id;
    ELSE
        WITH tt_in_summary_monthly_qty AS (
            SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty
            FROM in_summary_monthly_qty_consignment
            WHERE tenant_id = pTenantId
                AND doc_type_id = vEmptyId
                AND date_year_month = vLastClosingGeneralLedger
                AND ou_id = pOuId
            GROUP BY tenant_id, product_id, product_balance_id
        )
        UPDATE tr_summary_mutation_stock A
        SET last_balance_qty = B.qty,
            expectation = B.qty
        FROM tt_in_summary_monthly_qty B
        WHERE A.tenant_id = B.tenant_id
            AND A.product_id = B.product_id
            AND A.product_balance_id = B.product_balance_id;
    END IF;

    -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi
    IF pFlgConsignment = 'N' THEN
        EXECUTE '
        INSERT INTO tr_summary_mutation_stock(
            session_id, tenant_id, ou_id, product_id, product_code, product_name,
            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation
        )
        SELECT $1, $2, $3, A.product_id, B.product_code, B.product_name,
                A.product_balance_id, 0, SUM(A.qty), 0, 0, SUM(A.qty)
        FROM in_summary_monthly_qty A
        INNER JOIN m_product B ON A.product_id = B.product_id
        INNER JOIN m_product_custom C ON B.product_id = C.product_id
        WHERE A.doc_type_id = $4
                AND A.date_year_month = $5
                AND A.ou_id = $3
                AND C.flg_buy_konsinyasi = $6 ' ||
                vFilterProduct ||
                vFilterCtgrProductId ||
                vFilterSubCtgrProductId ||
                vFilterBrandId ||
        ' AND NOT EXISTS(
            SELECT 1 FROM tr_summary_mutation_stock D
            WHERE A.tenant_id = D.tenant_id
                AND A.product_id = D.product_id
                AND A.product_balance_id = D.product_balance_id
        ) GROUP BY A.product_id, B.product_code, B.product_name, A.product_balance_id '
        USING pSessionId, pTenantId, pOuId, vEmptyId, vLastClosingGeneralLedger, vNo;
    ELSE

        EXECUTE '
        INSERT INTO tr_summary_mutation_stock(
            session_id, tenant_id, ou_id, product_id, product_code, product_name,
            product_balance_id, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation
        )
        SELECT $1, $2, $3, A.product_id, B.product_code, B.product_name,
                A.product_balance_id, 0, SUM(A.qty), 0, 0, SUM(A.qty)
        FROM in_summary_monthly_qty_consignment A
        INNER JOIN m_product B ON A.product_id = B.product_id
        INNER JOIN m_product_custom C ON B.product_id = C.product_id
        WHERE A.doc_type_id = $4
                AND A.date_year_month = $5
                AND A.ou_id = $3
                AND C.flg_buy_konsinyasi = $6 ' ||
                vFilterProduct ||
                vFilterCtgrProductId ||
                vFilterSubCtgrProductId ||
                vFilterBrandId ||
        ' AND NOT EXISTS(
            SELECT 1 FROM tr_summary_mutation_stock D
            WHERE A.tenant_id = D.tenant_id
                AND A.product_id = D.product_id
                AND A.product_balance_id = D.product_balance_id
        ) GROUP BY A.product_id, B.product_code, B.product_name, A.product_balance_id '
        USING pSessionId, pTenantId, pOuId, vEmptyId, vLastClosingGeneralLedger, vYes;
    END IF;

    -- update nilai saldo awal amount
    WITH tt_in_summary_monthly_amount AS (
        SELECT tenant_id, product_id, gl_curr_code, SUM(gl_amount) AS gl_amount
        FROM in_summary_monthly_amount
        WHERE tenant_id = pTenantId
            AND doc_type_id = vEmptyId
            AND date_year_month = vLastClosingGeneralLedger
            AND ou_bu_id = (f_get_ou_bu_structure(pOuId)).ou_bu_id
            AND ou_branch_id = (f_get_ou_bu_structure(pOuId)).ou_branch_id
            AND ou_sub_bu_id = (f_get_ou_bu_structure(pOuId)).ou_sub_bu_id
        GROUP BY tenant_id, product_id, gl_curr_code
    )
    UPDATE tr_summary_mutation_stock A
    SET total_amount = B.gl_amount
    FROM tt_in_summary_monthly_amount B
    WHERE A.tenant_id = B.tenant_id
        AND A.product_id = B.product_id;

    IF pStartDate <> vStartDateLog THEN
        -- update nilai saldo awal qty + log product
        IF pFlgConsignment = 'N' THEN
            -- product non consignment
            EXECUTE '
            WITH tt_in_log_product_balance_stock AS (
                SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(qty) AS qty
                FROM in_log_product_balance_stock A
                INNER JOIN m_product B ON A.product_id = B.product_id
                INNER JOIN m_product_custom C ON B.product_id = C.product_id
                WHERE A.doc_date BETWEEN $1 AND $2
                    AND A.ou_id = $3
                    AND C.flg_buy_konsinyasi = $4 ' ||
                    vFilterProduct ||
                    vFilterCtgrProductId ||
                    vFilterSubCtgrProductId ||
                    vFilterBrandId ||
                ' GROUP BY A.tenant_id, A.product_id, A.product_balance_id
            )
            UPDATE tr_summary_mutation_stock A
            SET last_balance_qty = A.last_balance_qty + B.qty,
                expectation = A.expectation + B.qty
            FROM tt_in_log_product_balance_stock B
            WHERE A.tenant_id = B.tenant_id
                AND A.product_id = B.product_id
                AND A.product_balance_id = B.product_balance_id
            ' USING vStartDateLog, vEndDateLog, pOuId, vNo;

            EXECUTE '
            WITH tt_in_log_product_consignment_balance_stock AS (
                SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(qty) AS qty
                FROM in_log_product_consignment_balance_stock A
                INNER JOIN m_product B ON A.product_id = B.product_id
                INNER JOIN m_product_custom C ON B.product_id = C.product_id
                WHERE A.doc_date BETWEEN $1 AND $2
                    AND A.ou_id = $3
                    AND C.flg_buy_konsinyasi = $4 ' ||
                    vFilterProduct ||
                    vFilterCtgrProductId ||
                    vFilterSubCtgrProductId ||
                    vFilterBrandId ||
                ' GROUP BY A.tenant_id, A.product_id, A.product_balance_id
            )
            UPDATE tr_summary_mutation_stock A
            SET last_balance_qty = A.last_balance_qty + B.qty,
                expectation = A.expectation + B.qty
            FROM tt_in_log_product_consignment_balance_stock B
            WHERE A.tenant_id = B.tenant_id
                AND A.product_id = B.product_id
                AND A.product_balance_id = B.product_balance_id
            ' USING vStartDateLog, vEndDateLog, pOuId, vNo;
        ELSE
            -- product non consignment
            EXECUTE '
            WITH tt_in_log_product_balance_stock AS (
                SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(qty) AS qty
                FROM in_log_product_balance_stock A
                INNER JOIN m_product B ON A.product_id = B.product_id
                INNER JOIN m_product_custom C ON B.product_id = C.product_id
                WHERE A.doc_date BETWEEN $1 AND $2
                    AND A.ou_id = $3
                    AND C.flg_buy_konsinyasi = $4
                    AND A.doc_type_id <> 111 ' ||
                    vFilterProduct ||
                    vFilterCtgrProductId ||
                    vFilterSubCtgrProductId ||
                    vFilterBrandId ||
                ' GROUP BY A.tenant_id, A.product_id, A.product_balance_id
            )
            UPDATE tr_summary_mutation_stock A
            SET last_balance_qty = A.last_balance_qty + B.qty,
                expectation = A.expectation + B.qty
            FROM tt_in_log_product_balance_stock B
            WHERE A.tenant_id = B.tenant_id
                AND A.product_id = B.product_id
                AND A.product_balance_id = B.product_balance_id
            ' USING vStartDateLog, vEndDateLog, pOuId, vYes;

            EXECUTE '
            WITH tt_in_log_product_consignment_balance_stock AS (
                SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(qty) AS qty
                FROM in_log_product_consignment_balance_stock A
                INNER JOIN m_product B ON A.product_id = B.product_id
                INNER JOIN m_product_custom C ON B.product_id = C.product_id
                WHERE A.doc_date BETWEEN $1 AND $2
                    AND A.ou_id = $3
                    AND C.flg_buy_konsinyasi = $4 ' ||
                    vFilterProduct ||
                    vFilterCtgrProductId ||
                    vFilterSubCtgrProductId ||
                    vFilterBrandId ||
                ' GROUP BY A.tenant_id, A.product_id, A.product_balance_id
            )
            UPDATE tr_summary_mutation_stock A
            SET last_balance_qty = A.last_balance_qty + B.qty,
                expectation = A.expectation + B.qty
            FROM tt_in_log_product_consignment_balance_stock B
            WHERE A.tenant_id = B.tenant_id
                AND A.product_id = B.product_id
                AND A.product_balance_id = B.product_balance_id
            ' USING vStartDateLog, vEndDateLog, pOuId, vYes;
        END IF;
    END IF;

    Open pRefHeader FOR
    SELECT pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name,
            pStartDate AS start_date, pEndDate AS end_date, pDatetime AS print_datetime;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
        SELECT product_id, product_code, product_name, total_amount, last_balance_qty, mutasi_minus, mutasi_positif, expectation
        FROM tr_summary_mutation_stock
        WHERE session_id = pSessionId
            AND tenant_id = pTenantId
        ORDER BY product_code;
    RETURN NEXT pRefDetail ;

    DELETE FROM tr_summary_mutation_stock WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/