f_check_product_balance_stock

/**
 * Adrian, Aug 10, 2017
 * Function untuk cek apakah in_product_balance stock sesuai
 */

CREATE OR REPLACE FUNCTION f_check_product_balance_stock(character varying, bigint, bigint)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId                  ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pWarehouseId                ALIAS FOR $3;

    vDocTypeIdPOInternal        bigint;
    vDocTypeIdDeliveryOrder     bigint;
    vDocTypeIdReturnNote        bigint;
    vDocTypeIdClaimNote         bigint;
    vDocTypeIdAdjStockQty       bigint;
    vDocTypeIdDoReceipt         bigint;
    vDocTypeIdGoodsTrfOutOutlet bigint;
    vDocTypeIdGoodsTrfOut       bigint;
    vDocTypeIdGoodsBorrowing bigint;
    vDocTypeIdGoodsOutOther bigint;

    vStatusRelease              character varying(1);
    vSpaceValue                 character varying(1);
    vFlagYes                    character varying(1);
    vFlagNo                     character varying(1);
    vFlagLost                   character varying(1);
    vFlagMissing                character varying(1);
    vFlagRejected               character varying(1);
    vFlagCorrection             character varying(1);
    vStatusS                    character varying(1);
    vEmptyId                    bigint;
    vStatusVoid                 character varying(1);

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

BEGIN

    vDocTypeIdPOInternal := 151;
    vDocTypeIdDeliveryOrder := 311;
    vDocTypeIdClaimNote := 511;
    vDocTypeIdAdjStockQty := 521;
    vDocTypeIdGoodsTrfOutOutlet := 532;
    vDocTypeIdGoodsTrfOut := 533;
    vDocTypeIdGoodsBorrowing := 551;
    vDocTypeIdGoodsOutOther := 568;

    vStatusRelease := 'R';
    vSpaceValue := ' ';
    vFlagYes := 'Y';
    vFlagNo := 'N';
    vFlagLost := 'L';
    vFlagMissing := 'M';
    vFlagRejected := 'R';
    vFlagCorrection := 'C';
    vStatusS := 'S';
    vEmptyId := -99;
    vStatusVoid := 'V';

    /* Year month summary terakhir */
    SELECT MAX (date_year_month) FROM in_summary_monthly_qty WHERE warehouse_id = pWarehouseId INTO vLastSummaryPeriod;
    SELECT vLastSummaryPeriod || '01' INTO vStartTrx;

    /* Kosongkan semua tabel temporary */
    DELETE FROM tt_check_product_balance_stock_for_trx WHERE session_id = pSessionId;
    DELETE FROM tt_check_product_balance_stock_for_temp WHERE session_id = pSessionId;
    DELETE FROM tt_check_product_balance_stock_for_result WHERE session_id = pSessionId;


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


    /* 2. Simpan perubahan qty product dari log */
    /* 2.a. Simpan perubahan qty product dari log untuk qty > 0 */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        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, log_product_balance_stock_id, doc_no, doc_date, vSpaceValue,
        product_id, product_balance_id, product_status, base_uom_id, qty
    FROM in_log_product_balance_stock
    WHERE tenant_id = pTenantId AND
        doc_date >= vStartTrx AND
        qty > 0 AND
        warehouse_id = pWarehouseId;

    /* 2.b. Simpan perubahan qty product dari log untuk qty < 0 */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        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, log_product_balance_stock_id, doc_no, doc_date, vSpaceValue,
        product_id, product_balance_id, product_status, base_uom_id, qty
    FROM in_log_product_balance_stock
    WHERE tenant_id = pTenantId AND
        doc_date >= vStartTrx AND
        qty < 0 AND
        warehouse_id = pWarehouseId;


    /* 3. Simpan perubahan qty product dari dokumen yang masih DRAFT dan IN PROGRESS (bukan R) */
    /* 3.a. PO INTERNAL */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        product_id, product_balance_id, product_status, base_uom_id, os_qty)
    SELECT pSessionId, A.tenant_id, B.warehouse_sell_id,
        B.ou_sell_id, B.doc_type_id, B.po_internal_id, A.po_internal_item_id, B.doc_no, B.doc_date, B.status_doc,
        A.product_id, C.product_balance_id, C.product_status, C.base_uom_id, C.qty_int * -1
    FROM pu_po_internal_item A
    INNER JOIN pu_po_internal B ON A.tenant_id = B.tenant_id AND A.po_internal_id = B.po_internal_id
    INNER JOIN pu_po_internal_product C ON A.tenant_id = C.tenant_id AND A.po_internal_item_id = C.po_internal_item_id
    WHERE A.tenant_id = pTenantId AND
        B.doc_date >= vStartTrx AND
        B.doc_type_id = vDocTypeIdPOInternal AND
        B.status_doc <> vStatusRelease AND
        B.warehouse_sell_id = pWarehouseId;

    /* 3.b. DELIVERY ORDER */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        product_id, product_balance_id, product_status, base_uom_id, os_qty)
    SELECT pSessionId, A.tenant_id, B.warehouse_id,
        B.ou_id, B.doc_type_id, B.do_id, A.do_item_id, B.doc_no, B.doc_date, B.status_doc,
        A.product_id, C.product_balance_id, C.product_status, C.base_uom_id, C.qty_dlv_int * -1
    FROM sl_do_item A
    INNER JOIN sl_do B ON A.tenant_id = B.tenant_id AND A.do_id = B.do_id
    INNER JOIN sl_do_product C ON A.tenant_id = C.tenant_id AND A.do_item_id = C.do_item_id
    WHERE A.tenant_id = pTenantId AND
        B.doc_date >= vStartTrx AND
        B.doc_type_id = vDocTypeIdDeliveryOrder AND
        B.status_doc <> vStatusRelease AND
        B.warehouse_id = pWarehouseId;


    /* 3.c. CLAIM NOTE */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        product_id, product_balance_id, product_status, base_uom_id, os_qty)
    SELECT pSessionId, A.tenant_id, B.warehouse_from_id,
        B.ou_from_id, B.doc_type_id, B.inventory_id, A.inventory_item_id, B.doc_no, B.doc_date, B.status_doc,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty_realization * -1
    FROM in_inventory_item A
    INNER JOIN in_inventory B ON A.tenant_id = B.tenant_id AND A.inventory_id = B.inventory_id
    WHERE A.tenant_id = pTenantId AND
        B.doc_date >= vStartTrx AND
        B.doc_type_id = vDocTypeIdClaimNote AND
        B.status_doc <> vStatusRelease AND
        B.warehouse_from_id = pWarehouseId;


    /* 3.c. ADJUSTMENT STOCK QTY dengan qty < 0 */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        product_id, product_balance_id, product_status, base_uom_id, os_qty)
    SELECT pSessionId, A.tenant_id, B.warehouse_from_id,
        B.ou_from_id, B.doc_type_id, B.inventory_id, A.inventory_item_id, B.doc_no, B.doc_date, B.status_doc,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty_realization
    FROM in_inventory_item A
    INNER JOIN in_inventory B ON A.tenant_id = B.tenant_id AND A.inventory_id = B.inventory_id
    WHERE A.tenant_id = pTenantId AND
        B.doc_date >= vStartTrx AND
        B.doc_type_id = vDocTypeIdAdjStockQty AND
        B.status_doc <> vStatusRelease AND
        A.qty_realization < 0 AND
        B.warehouse_from_id = pWarehouseId;


    /* 3.d. GOODS TRANSFER OUT */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        product_id, product_balance_id, product_status, base_uom_id, os_qty)
    SELECT pSessionId, A.tenant_id, B.warehouse_from_id,
        B.ou_from_id, B.doc_type_id, B.inventory_id, A.inventory_item_id, B.doc_no, B.doc_date, B.status_doc,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty_realization * -1
    FROM in_inventory_item A
    INNER JOIN in_inventory B ON A.tenant_id = B.tenant_id AND A.inventory_id = B.inventory_id
    WHERE A.tenant_id = pTenantId AND
        B.doc_date >= vStartTrx AND
        B.doc_type_id = vDocTypeIdGoodsTrfOut AND
        B.status_doc <> vStatusRelease AND
        B.warehouse_from_id = pWarehouseId;


    /* 3.e. DELIVERY GOODS BORROWING */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        product_id, product_balance_id, product_status, base_uom_id, os_qty)
    SELECT pSessionId, A.tenant_id, B.warehouse_from_id,
        B.ou_from_id, B.doc_type_id, B.inventory_id, A.inventory_item_id, B.doc_no, B.doc_date, B.status_doc,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty_realization * -1
    FROM in_inventory_item A
    INNER JOIN in_inventory B ON A.tenant_id = B.tenant_id AND A.inventory_id = B.inventory_id
    WHERE A.tenant_id = pTenantId AND
        B.doc_date >= vStartTrx AND
        B.doc_type_id = vDocTypeIdGoodsBorrowing AND
        B.status_doc <> vStatusRelease AND
        B.warehouse_from_id = pWarehouseId;


    /* 3.f. DELIVERY GOODS BORROWING */
    INSERT INTO tt_check_product_balance_stock_for_trx
        (session_id, tenant_id, warehouse_id,
        ou_id, doc_type_id, ref_id, ref_item_id, doc_no, doc_date, status_doc,
        product_id, product_balance_id, product_status, base_uom_id, os_qty)
    SELECT pSessionId, A.tenant_id, B.warehouse_from_id,
        B.ou_from_id, B.doc_type_id, B.inventory_id, A.inventory_item_id, B.doc_no, B.doc_date, B.status_doc,
        A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty_realization * -1
    FROM in_inventory_item A
    INNER JOIN in_inventory B ON A.tenant_id = B.tenant_id AND A.inventory_id = B.inventory_id
    WHERE A.tenant_id = pTenantId AND
        B.doc_date >= vStartTrx AND
        B.doc_type_id = vDocTypeIdGoodsOutOther AND
        B.status_doc <> vStatusRelease AND
        B.warehouse_from_id = pWarehouseId;


    /* 4. Simpan ke dalam table temp, group by unique index in_product_balance stock */
    INSERT INTO tt_check_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, os_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), SUM(os_qty)
    FROM tt_check_product_balance_stock_for_trx
    WHERE session_id = pSessionId AND
        tenant_id = pTenantId AND
        warehouse_id = pWarehouseId
    GROUP BY session_id, tenant_id, warehouse_id,
        product_id, product_balance_id, product_status, base_uom_id;


    /* 5. Update product balance stock id,
     *  qty yang tersimpan,
     *  dan total qty yang diharapkan pada table temp */
    UPDATE tt_check_product_balance_stock_for_temp Z
    SET product_balance_stock_id = A.product_balance_stock_id,
        actual_qty = A.qty,
        expected_qty = summary_qty + add_qty + sub_qty + os_qty
    FROM in_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 AND
        Z.warehouse_id = pWarehouseId;


    /* 6. Simpan ke dalam table result untuk data yang tidak ditemukan/tidak sesuai */
    INSERT INTO tt_check_product_balance_stock_for_result
        (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, os_qty, expected_qty, actual_qty)
    SELECT 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, os_qty, expected_qty, actual_qty
    FROM tt_check_product_balance_stock_for_temp
    WHERE session_id = pSessionId AND
        tenant_id = pTenantId AND
        warehouse_id = pWarehouseId AND
        (product_balance_stock_id = vEmptyId OR
        expected_qty <> actual_qty);


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