f_recalculate_product_balance_stock

/**
 * Adrian, Dec 28, 2017
 * Function untuk melakukan recalculate p_product_balance stock
 */

CREATE OR REPLACE FUNCTION f_recalculate_product_balance_stock(character varying, bigint, bigint, bigint, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId                  ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pOuId                       ALIAS FOR $3;
    pUserId                     ALIAS FOR $4;
    pDatetime                   ALIAS FOR $5;

    vSpaceValue                 character varying(1);
    vFlagYes                    character varying(1);
    vFlagNo                     character varying(1);
    vFlagError                  character varying(1);
    vStatusDraft                character varying(1);
    vEmptyId                    bigint;
    vDefaultSummaryPeriod       character varying(6);

    vLastSummaryPeriod          character varying(6);
    vStartTrx                   character varying(8);

BEGIN

    vSpaceValue := ' ';
    vFlagYes := 'Y';
    vFlagNo := 'N';
    vFlagError := 'E';
    vStatusDraft := 'D';
    vEmptyId := -99;
    vDefaultSummaryPeriod := '200001';

    /* Year month summary terakhir */
    SELECT COALESCE(MAX(date_year_month), vDefaultSummaryPeriod) FROM p_summary_monthly_qty INTO vLastSummaryPeriod;
    SELECT vLastSummaryPeriod || '01' INTO vStartTrx;

    /* Kosongkan semua tabel temporary */
    DELETE FROM tt_recalculate_product_balance_stock_for_log WHERE session_id = pSessionId;
    DELETE FROM tt_recalculate_product_balance_stock_for_temp WHERE session_id = pSessionId;

    /* Cek Apakah ada Dokumen yang masih dalam progress */
    IF EXISTS(
            SELECT 1 FROM p_trx_inventory A WHERE A.status IN (vStatusDraft)
        ) THEN
            RAISE EXCEPTION 'Cannot do recalculate for there is in progress document(s)';
    END IF;

    /* 1. Simpan stock awal product, doc_type_id dibuat -99 */
    INSERT INTO tt_recalculate_product_balance_stock_for_log
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, doc_no, doc_date,
        product_id, product_balance_id, product_status, base_uom_id, summary_qty)
    SELECT pSessionId, tenant_id, warehouse_id,
        sub_ou_id, vEmptyId, summary_monthly_qty_id, vSpaceValue, date_year_month,
        product_id, product_balance_id, product_status, base_uom_id, qty
    FROM p_summary_monthly_qty
    WHERE tenant_id = pTenantId AND
        sub_ou_id = pOuId AND
        date_year_month = vLastSummaryPeriod;


    /* 2. Simpan perubahan qty product dari log */
    /* 2.a. Simpan perubahan qty product dari log untuk qty > 0 */
    INSERT INTO tt_recalculate_product_balance_stock_for_log
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, doc_no, doc_date,
        product_id, product_balance_id, product_status, base_uom_id, add_qty)
    SELECT pSessionId, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, doc_no, doc_date,
        product_id, product_balance_id, product_status, base_uom_id, qty
    FROM p_log_product_balance_stock
    WHERE tenant_id = pTenantId AND
        ou_id = pOuId AND
        doc_date >= vStartTrx AND
        qty > 0;

    /* 2.b. Simpan perubahan qty product dari log untuk qty < 0 */
    INSERT INTO tt_recalculate_product_balance_stock_for_log
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, doc_no, doc_date,
        product_id, product_balance_id, product_status, base_uom_id, sub_qty)
    SELECT pSessionId, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, doc_no, doc_date,
        product_id, product_balance_id, product_status, base_uom_id, qty
    FROM p_log_product_balance_stock
    WHERE tenant_id = pTenantId AND
        ou_id = pOuId AND
        doc_date >= vStartTrx AND
        qty < 0;

    /* 3. Simpan ke dalam table temp, group by unique index p_product_balance stock */
    INSERT INTO tt_recalculate_product_balance_stock_for_temp
        (session_id, product_balance_stock_id, tenant_id, warehouse_id,
        product_id, product_balance_id, product_status, base_uom_id,
        summary_qty, add_qty, sub_qty)
    SELECT session_id, vEmptyId, tenant_id, warehouse_id,
        product_id, product_balance_id, product_status, base_uom_id,
        SUM(summary_qty), SUM(add_qty), SUM(sub_qty)
    FROM tt_recalculate_product_balance_stock_for_log
    WHERE session_id = pSessionId AND
        tenant_id = pTenantId AND
        ou_id = pOuId
    GROUP BY session_id, tenant_id, warehouse_id,
        product_id, product_balance_id, product_status, base_uom_id;

    /* 4.a. Update total qty yang diharapkan pada table temp */
    UPDATE tt_recalculate_product_balance_stock_for_temp Z
    SET expected_qty = Z.summary_qty + Z.add_qty + Z.sub_qty
    FROM p_product_balance_stock A
    WHERE Z.session_id = pSessionId AND
        Z.tenant_id = pTenantId;

    /* 4.b. Update product balance stock id dan qty yang tersimpan */
    UPDATE tt_recalculate_product_balance_stock_for_temp Z
    SET product_balance_stock_id = A.product_balance_stock_id,
        actual_qty = A.qty
    FROM p_product_balance_stock A
    WHERE A.tenant_id = Z.tenant_id AND
        A.warehouse_id = Z.warehouse_id AND
        A.product_id = Z.product_id AND
        A.product_balance_id = Z.product_balance_id AND
        A.product_status = Z.product_status AND
        Z.session_id = pSessionId AND
        Z.tenant_id = pTenantId;

    /* 5. Simpan ke dalam table update untuk data yang tidak ditemukan/tidak sesuai */
    INSERT INTO p_recalculate_product_balance_stock
        (product_balance_stock_id, tenant_id, warehouse_id,
        product_id, product_balance_id, product_status, base_uom_id,
        summary_qty, add_qty, sub_qty, expected_qty, actual_qty, flg_update,
        version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT product_balance_stock_id, tenant_id, warehouse_id,
        product_id, product_balance_id, product_status, base_uom_id,
        summary_qty, add_qty, sub_qty, expected_qty, actual_qty, vFlagNo,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_recalculate_product_balance_stock_for_temp
    WHERE session_id = pSessionId AND
        tenant_id = pTenantId AND
        (product_balance_stock_id = vEmptyId OR
        expected_qty <> actual_qty);

    /* 6. Update flg_update menjadi E untuk expected qty yang minus */
    UPDATE p_recalculate_product_balance_stock
        SET flg_update = vFlagError
    WHERE tenant_id = pTenantId
        AND flg_update = vFlagNo
        AND expected_qty < 0;

    /* 7. Update product_balance stock yang tidak sesuai */
    UPDATE p_product_balance_stock Z
        SET qty = A.expected_qty,
            update_datetime = pDatetime,
            update_user_id = pUserId,
            version = Z.version + 1
    FROM p_recalculate_product_balance_stock A
    WHERE A.tenant_id = pTenantId AND
        A.flg_update = vFlagNo AND
        A.product_balance_stock_id = Z.product_balance_stock_id AND
        A.expected_qty <> A.actual_qty;

    /* 8. Insert untuk product balance stock yang tidak ditemukan */
    INSERT INTO p_product_balance_stock(
        tenant_id, warehouse_id, product_id,
        product_balance_id, product_status, base_uom_id, qty, version,
        create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.warehouse_id, A.product_id,
        A.product_balance_id, A.product_status, A.base_uom_id, A.expected_qty, 0,
        pDatetime, pUserId, pDatetime, pUserId
    FROM p_recalculate_product_balance_stock A
    WHERE A.tenant_id = pTenantId AND
        A.flg_update = vFlagNo AND
        A.product_balance_stock_id = vEmptyId;

    /* 8. Update flg_update menjadi Y */
    UPDATE p_recalculate_product_balance_stock
        SET flg_update = vFlagYes
    WHERE tenant_id = pTenantId AND
        flg_update = vFlagNo;

    /* Kosongkan semua tabel temporary */
    DELETE FROM tt_recalculate_product_balance_stock_for_log WHERE session_id = pSessionId;
    DELETE FROM tt_recalculate_product_balance_stock_for_temp WHERE session_id = pSessionId;

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