CREATE OR REPLACE FUNCTION f_update_balance_stock_for_xcom(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, BIGINT, CHARACTER VARYING, CHARACTER VARYING)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId                  ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pUserId                     ALIAS FOR $3;
    pRoleId                     ALIAS FOR $4;
    pDatetime                   ALIAS FOR $5;
    pOuId                       ALIAS FOR $6;
    pStartDate                  ALIAS FOR $7;
    pEndDate                    ALIAS FOR $8;
--    pFlgConsignment               ALIAS FOR $9;

    vStartDateLog               character varying;
    vEndDateLog                 character varying;
    vLastClosingGeneralLedger   character varying;
    vFilterOuId                 character varying;
    vYes                        character varying := 'Y';
    vNo                         character varying := 'N';
    vEmptyId                    bigint := -99;

BEGIN

    DELETE FROM tr_summary_update_product_stock WHERE session_id = pSessionId;

    -- last closing ledger sebelum pStartDate
    SELECT COALESCE(MAX(date_year_month), MIN(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;

    -- insert from log product
--    IF pFlgConsignment = 'N' THEN
    -- insert from log product WHEN pFlgConsignment = 'N'
        EXECUTE '
        INSERT INTO tr_summary_update_product_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,
            warehouse_id, product_status, os_doc_qty
        )
        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,
                A.warehouse_id, A.product_status, 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,
                    A.warehouse_id, A.product_status
            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
             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,
                    A.warehouse_id, A.product_status
            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 ) A
            GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
        USING pSessionId, pTenantId, pOuId, pStartDate, pEndDate, vNo;

--    ELSE

    -- insert from log product WHEN pFlgConsignment = 'Y'
        EXECUTE '
        INSERT INTO tr_summary_update_product_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,
            warehouse_id, product_status, os_doc_qty
        )
        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,
                A.warehouse_id, A.product_status, 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,
                    A.warehouse_id, A.product_status
            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
             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,
                    A.warehouse_id, A.product_status
            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
             ) A
            GROUP BY A.product_id, A.product_code, A.product_name, A.product_balance_id, A.warehouse_id, A.product_status '
        USING pSessionId, pTenantId, pOuId, pStartDate, pEndDate, vYes;

--    END IF;

    -- update nilai saldo awal qty
--    IF pFlgConsignment = 'N' THEN

    -- update nilai saldo awal qty WHEN pFlgConsignment = 'N'
        WITH tt_in_summary_monthly_qty AS (
            SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty, warehouse_id, product_status
            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, warehouse_id, product_status
        )
        UPDATE tr_summary_update_product_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
            AND A.warehouse_id = B.warehouse_id
            AND A.product_status = B.product_status
            AND A.session_id = pSessionId;
--    ELSE

    -- update nilai saldo awal qty WHEN pFlgConsignment = 'Y'
        WITH tt_in_summary_monthly_qty AS (
            SELECT tenant_id, product_id, product_balance_id, SUM(qty) AS qty, warehouse_id, product_status
            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, warehouse_id, product_status
        )
        UPDATE tr_summary_update_product_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
            AND A.warehouse_id = B.warehouse_id
            AND A.product_status = B.product_status
            AND A.session_id = pSessionId;
--    END IF;

    -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi
    --IF pFlgConsignment = 'N' THEN

    -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi WHEN pFlgConsignment = 'N'
        EXECUTE '
        INSERT INTO tr_summary_update_product_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,
            warehouse_id, product_status, os_doc_qty
        )
        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),
                A.warehouse_id, A.product_status, 0
        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
         AND NOT EXISTS(
            SELECT 1 FROM tr_summary_update_product_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, A.warehouse_id, A.product_status '
        USING pSessionId, pTenantId, pOuId, vEmptyId, vLastClosingGeneralLedger, vNo;

--    ELSE

    -- insert product yang hanya punya saldo awal qty dan tidak pernah punya log transaksi WHEN pFlgConsignment = 'Y'
        EXECUTE '
        INSERT INTO tr_summary_update_product_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,
            warehouse_id, product_status, os_doc_qty
        )
        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),
                A.warehouse_id, A.product_status, 0
        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
         AND NOT EXISTS(
            SELECT 1 FROM tr_summary_update_product_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, A.warehouse_id, A.product_status '
        USING pSessionId, pTenantId, pOuId, vEmptyId, vLastClosingGeneralLedger, vYes;
--    END IF;

    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, A.warehouse_id, A.product_status
                FROM in_log_product_balance_stock A
                INNER JOIN m_product_custom B ON A.product_id = B.product_id
                WHERE A.doc_date BETWEEN $1 AND $2
                    AND A.ou_id = $3
                    AND B.flg_buy_konsinyasi = $4
                GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_id, A.product_status
            )
            UPDATE tr_summary_update_product_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
                AND A.warehouse_id = B.warehouse_id
                AND A.product_status = B.product_status
                AND A.session_id = $5
            ' USING vStartDateLog, vEndDateLog, pOuId, vNo, pSessionId;

            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, A.warehouse_id, A.product_status
                FROM in_log_product_consignment_balance_stock A
                INNER JOIN m_product_custom B ON A.product_id = B.product_id
                WHERE A.doc_date BETWEEN $1 AND $2
                    AND A.ou_id = $3
                    AND B.flg_buy_konsinyasi = $4
                 GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_id, A.product_status
            )
            UPDATE tr_summary_update_product_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
                AND A.warehouse_id = B.warehouse_id
                AND A.product_status = B.product_status
                AND A.session_id = $5
            ' USING vStartDateLog, vEndDateLog, pOuId, vNo, pSessionId;
--        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, A.warehouse_id, A.product_status
                FROM in_log_product_balance_stock A
                INNER JOIN m_product_custom B ON A.product_id = B.product_id
                WHERE A.doc_date BETWEEN $1 AND $2
                    AND A.ou_id = $3
                    AND B.flg_buy_konsinyasi = $4
                    AND A.doc_type_id <> 111
                GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_id, A.product_status
            )
            UPDATE tr_summary_update_product_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
                AND A.warehouse_id = B.warehouse_id
                AND A.product_status = B.product_status
                AND A.session_id = $5
            ' USING vStartDateLog, vEndDateLog, pOuId, vYes, pSessionId;

            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, A.warehouse_id, A.product_status
                FROM in_log_product_consignment_balance_stock A
                INNER JOIN m_product_custom B ON A.product_id = B.product_id
                WHERE A.doc_date BETWEEN $1 AND $2
                    AND A.ou_id = $3
                    AND B.flg_buy_konsinyasi = $4
                GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_id, A.product_status
            )
            UPDATE tr_summary_update_product_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
                AND A.warehouse_id = B.warehouse_id
                AND A.product_status = B.product_status
                AND A.session_id = $5
            ' USING vStartDateLog, vEndDateLog, pOuId, vYes, pSessionId;
--        END IF;
    END IF;

    EXECUTE '
    WITH tt_os_document_qty AS (
        SELECT A.tenant_id, A.product_id, A.product_balance_id, SUM(A.qty_realization) AS qty, A.warehouse_from_id AS warehouse_id, A.product_status
        FROM vw_in_product_out_with_outstanding_document A
        WHERE A.tenant_id = $2
            AND A.ou_from_id = $3
        GROUP BY A.tenant_id, A.product_id, A.product_balance_id, A.warehouse_from_id, A.product_status
    )
    UPDATE tr_summary_update_product_stock A
    SET os_doc_qty = B.qty
    FROM tt_os_document_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
        AND A.warehouse_id = B.warehouse_id
        AND A.product_status = B.product_status
        AND A.session_id = $1
    ' USING pSessionId, pTenantId, pOuId;

    UPDATE in_product_balance_stock A
    SET qty = B.last_balance_qty + B.mutasi_positif - B.mutasi_minus - B.os_doc_qty
    FROM tr_summary_update_product_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
        AND A.warehouse_id = B.warehouse_id
        AND A.product_status = B.product_status
        AND B.session_id = pSessionId;

    DELETE FROM tr_summary_update_product_stock WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 /