f_check_log_product_balance_stock

/**
 * Adrian, Aug 7, 2017
 */

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

    vDocTypeIdReceiveGoods      bigint;
    vDocTypeIdDeliveryOrder     bigint;
    vDocTypeIdPOSShop           bigint;
    vDocTypeIdPOSReturnShop     bigint;
    vDocTypeIdPOSShopInShop     bigint;
    vDocTypeIdPOSReturnShopInShop bigint;
    vDocTypeIdAdjStockPOS       bigint;
    vDocTypeIdVoidPOSShop       bigint;
    vDocTypeIdVoidPOSShopInShop bigint;
    vDocTypeIdReturnNote        bigint;
    vDocTypeIdClaimNote         bigint;
    vDocTypeIdAdjStockQty       bigint;
    vDocTypeIdAdjStockAmount    bigint;
    vDocTypeIdDoReceipt         bigint;
    vDocTypeIdGoodsTrfOutOutlet bigint;
    vDocTypeIdGoodsTrfOut       bigint;
    vDocTypeIdGoodsTrfInOutlet  bigint;
    vDocTypeIdGoodsTrfIn        bigint;
    vDocTypeIdGoodsTrfInReceipt bigint;
    vGoodsTransferInReceiptLostDocTypeId bigint;
    vDocTypeIdGoodsBorrowing bigint;
    vDocTypeIdReturnGoodsBorrowing 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);

    vProductStatus              character varying(50);
    vProductStatusGood          character varying(4);

BEGIN

    vDocTypeIdReceiveGoods := 111;
    vDocTypeIdDeliveryOrder := 311;
    vDocTypeIdPOSShop := 401;
    vDocTypeIdPOSReturnShop := 402;
    vDocTypeIdPOSShopInShop := 403;
    vDocTypeIdPOSReturnShopInShop := 404;
    vDocTypeIdAdjStockPOS := 413;
    vDocTypeIdVoidPOSShop := 405;
    vDocTypeIdVoidPOSShopInShop := 406;
    vDocTypeIdReturnNote := 502;
    vDocTypeIdClaimNote := 511;
    vDocTypeIdAdjStockQty := 521;
    vDocTypeIdAdjStockAmount := 522;
    vDocTypeIdDoReceipt := 526;
    vDocTypeIdGoodsTrfOutOutlet := 532;
    vDocTypeIdGoodsTrfOut := 533;
    vDocTypeIdGoodsTrfInOutlet := 534;
    vDocTypeIdGoodsTrfIn := 535;
    vDocTypeIdGoodsTrfInReceipt := 536;
    vGoodsTransferInReceiptLostDocTypeId := 537;
    vDocTypeIdGoodsBorrowing := 551;
    vDocTypeIdReturnGoodsBorrowing := 552;
    vDocTypeIdGoodsOutOther := 568;

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

    vProductStatusGood := 'GOOD';

    SELECT product_status_code INTO vProductStatus
    FROM m_product_status
    WHERE flg_buy = vFlagYes;

    /* Kosongkan semua tabel temporary */
    DELETE FROM tt_check_log_product_balance_stock_for_trx WHERE session_id = pSessionId;
    DELETE FROM tt_check_log_product_balance_stock_for_log WHERE session_id = pSessionId;
    DELETE FROM tt_check_log_product_balance_stock_for_result WHERE session_id = pSessionId;

    /* 1. Simpan semua data transaksi yang seharusnya masuk ke in_log_product_balance_stock */

    /* 1.a. RECEIVE GOODS */
    /* 1.a.1) Item receive goods yang tidak memiliki serial number */
    WITH temp_pu_product_balance AS (
        SELECT A.tenant_id, A.doc_type_id, A.receive_goods_id AS ref_id, A.doc_no, A.doc_date, A.partner_id,
            B.product_id, vSpaceValue AS serial_number, vSpaceValue AS lot_number, A.warehouse_id, vProductStatus AS product_status, D.base_uom_id, SUM(B.qty_rcv_int) AS qty_int_rcv,
            D.flg_stock
        FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item D, pu_po E
        WHERE A.receive_goods_id = B.receive_goods_id AND
            NOT EXISTS (SELECT 1 FROM pu_receive_goods_product C
                    WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
            NOT EXISTS (SELECT 1 FROM pu_receive_goods_product_auto_sn C
                    WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
            B.ref_id = D.po_item_id AND
            D.po_id = E.po_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.status_doc = vStatusRelease AND
            A.doc_type_id = vDocTypeIdReceiveGoods AND
            A.tenant_id = pTenantId AND
            A.warehouse_id = pWarehouseId
        GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
            A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no,
            E.po_id, E.doc_no, E.doc_date, D.po_item_id,
            D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
    (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, C.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv)
    FROM temp_pu_product_balance A, m_warehouse_ou B, in_product_balance C
    WHERE A.flg_stock = 'Y' AND
        A.warehouse_id = B.warehouse_id AND
        A.tenant_id = C.tenant_id AND
        A.product_id = C.product_id AND
        A.serial_number = C.serial_number AND
        A.lot_number = C.lot_number
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, C.product_balance_id, A.warehouse_id, A.base_uom_id;

    /* 1.a.2) Item receive goods yang memiliki serial number dan tidak di-generate */
    WITH temp_pu_product_balance AS (
        SELECT A.tenant_id, A.doc_type_id, A.receive_goods_id AS ref_id, A.doc_no, A.doc_date, A.partner_id,
            B.product_id, C.serial_number AS serial_number, C.lot_number AS lot_number, A.warehouse_id, vProductStatus AS product_status, D.base_uom_id, SUM(C.qty_rcv_int) AS qty_int_rcv,
            D.flg_stock
        FROM pu_receive_goods A, pu_receive_goods_item B, pu_receive_goods_product C, pu_po_item D, pu_po E
        WHERE A.receive_goods_id = B.receive_goods_id AND
            B.receive_goods_item_id = C.receive_goods_item_id AND
            B.ref_id = D.po_item_id AND
            D.po_id = E.po_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.status_doc = vStatusRelease AND
            A.doc_type_id = vDocTypeIdReceiveGoods AND
            A.tenant_id = pTenantId AND
            A.warehouse_id = pWarehouseId
        GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
            C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
            A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_id, B.receive_goods_item_id, A.doc_no,
            E.po_id, E.doc_no, E.doc_date, D.po_item_id,
            D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
    (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, C.product_balance_id, A.warehouse_id, vProductStatus, A.base_uom_id, SUM(A.qty_int_rcv)
    FROM temp_pu_product_balance A, m_warehouse_ou B, in_product_balance C
    WHERE A.flg_stock = vFlagYes AND
        A.warehouse_id = B.warehouse_id AND
        A.tenant_id = C.tenant_id AND
        A.product_id = C.product_id AND
        A.serial_number = C.serial_number AND
        A.lot_number = C.lot_number
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, C.product_balance_id, A.warehouse_id, A.base_uom_id;


    /* 1.b. DELIVERY ORDER */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
    (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
        C.product_id, vSpaceValue, vSpaceValue, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id, SUM(C.qty_dlv_int) * -1
    FROM sl_do A, sl_do_item B, sl_do_product C, m_warehouse_ou D
    WHERE A.do_id = B.do_id AND
        B.do_item_id = C.do_item_id AND
        A.warehouse_id = D.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdDeliveryOrder AND
        A.tenant_id = pTenantId AND
        A.warehouse_id = pWarehouseId
    GROUP BY A.tenant_id, D.ou_id, A.doc_type_id, A.do_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
            C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.base_uom_id;


    /* 1.c. POS Shop */
    /* 1.c.1) Item POS Shop yang bukan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
            B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
        FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusS, vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id <> vEmptyId AND
            C.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSShop AND
            C.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;

    /* 1.c.2) Item POS Shop yang merupakan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
            C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
        FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusS, vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id = vEmptyId AND
            B.trx_pos_id = C.trx_pos_id AND
            B.process_no = C.process_no AND
            B.tenant_id = C.tenant_id AND
            B.line_no = C.ref_line_no AND
            D.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSShop AND
            D.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;

    /* 1.c.3) Item POS Shop yang akan dimasukkan sebagai adjustment stock */
    /*WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id AS ref_id, vEmptyId AS partner_id,
            B.doc_no, B.doc_date, C.product_id, B.warehouse_from_id AS warehouse_id,
            C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty_realization) AS qty
        FROM i_outlet A, i_trx_inventory B, i_trx_inventory_item C
        WHERE A.tenant_id = pTenantId AND
            A.tenant_id = B.tenant_id AND
            A.ou_id = B.ou_id AND
            B.process_no = C.process_no AND
            B.trx_inventory_id = C.trx_inventory_id AND
            B.tenant_id = C.tenant_id AND
            B.doc_date BETWEEN pDateFrom AND pDateTo AND
            B.doc_type_id = vDocTypeIdAdjStockPOS AND
            C.product_balance_id <> vEmptyId
        GROUP BY A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id, B.warehouse_from_id,
            B.doc_no, B.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty)
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    WHERE A.doc_type_id = vDocTypeIdAdjStockPOS AND
        A.product_balance_id <> vEmptyId
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id;*/

    /* 1.d. POS Return Shop */
    /* 1.d.1) Item POS Return Shop yang bukan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
            B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
        FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusS, vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id <> vEmptyId AND
            C.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSReturnShop AND
            C.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;

    /* 1.d.2) Item POS Return Shop yang merupakan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
            C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
        FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusS, vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id = vEmptyId AND
            B.trx_pos_id = C.trx_pos_id AND
            B.process_no = C.process_no AND
            B.tenant_id = C.tenant_id AND
            B.line_no = C.ref_line_no AND
            D.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSReturnShop AND
            D.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;


    /* 1.e. POS Shop In Shop */
    /* 1.e.1) Item POS Shop In Shop yang bukan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
            B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
        FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusS, vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id <> vEmptyId AND
            C.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSShopInShop AND
            C.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;

    /* 1.e.2) Item POS Shop In Shop yang merupakan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
            C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
        FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusS, vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id = vEmptyId AND
            B.trx_pos_id = C.trx_pos_id AND
            B.process_no = C.process_no AND
            B.tenant_id = C.tenant_id AND
            B.line_no = C.ref_line_no AND
            D.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSShopInShop AND
            D.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;

    /* 1.e.3) Item POS Shop In Shop yang akan dimasukkan sebagai adjustment stock */
    /*WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id AS ref_id, vEmptyId AS partner_id,
            B.doc_no, B.doc_date, C.product_id, B.warehouse_from_id AS warehouse_id,
            C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty_realization) AS qty
        FROM i_outlet A, i_trx_inventory B, i_trx_inventory_item C
        WHERE A.tenant_id = pTenantId AND
            A.tenant_id = B.tenant_id AND
            A.ou_id = B.ou_id AND
            B.process_no = C.process_no AND
            B.trx_inventory_id = C.trx_inventory_id AND
            B.tenant_id = C.tenant_id AND
            B.doc_date BETWEEN pDateFrom AND pDateTo AND
            B.doc_type_id = vDocTypeIdAdjStockPOS AND
            C.product_balance_id <> vEmptyId
        GROUP BY A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id, B.warehouse_from_id,
            B.doc_no, B.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty)
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    WHERE A.doc_type_id = vDocTypeIdAdjStockPOS AND
        A.product_balance_id <> vEmptyId
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id;*/

    /* 1.f. POS Return Shop In Shop */
    /* 1.f.1) Item POS Return Shop In Shop yang bukan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
            B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) AS qty
        FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusS, vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id <> vEmptyId AND
            C.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSReturnShopInShop AND
            C.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;

    /* 1.f.2) Item POS Return Shop yang merupakan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
            C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) AS qty
        FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusS, vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id = vEmptyId AND
            B.trx_pos_id = C.trx_pos_id AND
            B.process_no = C.process_no AND
            B.tenant_id = C.tenant_id AND
            B.line_no = C.ref_line_no AND
            D.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSReturnShopInShop AND
            D.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id;


    /* 1.g. Void POS Shop */
    /* 1.g.1) Item Void POS Shop yang bukan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
            B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) * -1 AS qty
        FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id <> vEmptyId AND
            C.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSShop AND
            C.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, vDocTypeIdVoidPOSShop, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;

    /* 1.g.2) Item Void POS Shop yang merupakan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
            C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) * -1 AS qty
        FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id = vEmptyId AND
            B.trx_pos_id = C.trx_pos_id AND
            B.process_no = C.process_no AND
            B.tenant_id = C.tenant_id AND
            B.line_no = C.ref_line_no AND
            D.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSShop AND
            D.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, vDocTypeIdVoidPOSShop, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;


    /* 1.h. Void POS Shop In Shop */
    /* 1.h.1) Item Void POS Shop In Shop yang bukan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, B.product_id, C.warehouse_id,
            B.product_balance_id, vProductStatusGood AS product_status, B.base_uom_id, SUM(B.qty) * -1 AS qty
        FROM i_trx_pos A, i_trx_pos_item B, m_warehouse_ou C
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id <> vEmptyId AND
            C.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSShopInShop AND
            C.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id, C.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, vDocTypeIdVoidPOSShopInShop, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;

    /* 1.h.2) Item Void POS Shop In Shop yang merupakan product assembly */
    WITH temp_pos_product_balance_stock AS (
        SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id AS ref_id, vEmptyId AS partner_id,
            A.doc_no, A.doc_date, C.product_id, D.warehouse_id,
            C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty) * -1 AS qty
        FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C, m_warehouse_ou D
        WHERE A.tenant_id = pTenantId AND
            A.status IN (vStatusVoid) AND
            A.process_no = B.process_no AND
            A.trx_pos_id = B.trx_pos_id AND
            A.tenant_id = B.tenant_id AND
            B.product_balance_id = vEmptyId AND
            B.trx_pos_id = C.trx_pos_id AND
            B.process_no = C.process_no AND
            B.tenant_id = C.tenant_id AND
            B.line_no = C.ref_line_no AND
            D.ou_id = A.ou_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.doc_type_id = vDocTypeIdPOSShopInShop AND
            D.warehouse_id = pWarehouseId
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
            A.doc_no, A.doc_date, C.product_id, C.product_balance_id, C.product_status, C.base_uom_id, D.warehouse_id
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, B.ou_id, vDocTypeIdVoidPOSShopInShop, A.ref_id, A.doc_no, A.doc_date, A.partner_id,
        A.product_id, vSpaceValue, vSpaceValue, A.product_balance_id, A.warehouse_id, A.product_status, A.base_uom_id, SUM(A.qty) * -1
    FROM temp_pos_product_balance_stock A
    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
    GROUP BY A.tenant_id, B.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
        A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;


    /* 1.i. RETURN NOTE */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id   AND
        A.warehouse_to_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdReturnNote AND
        A.tenant_id = pTenantId AND
        A.warehouse_to_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;


    /* 1.j. CLAIM NOTE*/
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        C.warehouse_id = A.warehouse_from_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdClaimNote AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;


    /* 1.k. ADJUSTMENT STOCK QTY*/
    /* 1.k.1) Item Adjustment Stock Qty dengan Qty > 0 */
    WITH temp_in_product_balance AS (
        SELECT A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_id, B.product_status,
            B.serial_number, B.lot_number, B.qty_realization, B.base_uom_id,
            COALESCE(C.flg_buy_konsinyasi, vFlagNo) AS flg_buy_konsinyasi
        FROM in_inventory A
        INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
        LEFT JOIN m_product_custom C ON B.product_id = C.product_id AND C.flg_buy_konsinyasi = vFlagYes
        WHERE A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.status_doc = vStatusRelease AND
            A.doc_type_id = vDocTypeIdAdjStockQty AND
            A.tenant_id = pTenantId
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.serial_number, B.lot_number, D.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM in_inventory A, temp_in_product_balance B, m_warehouse_ou C, in_product_balance D
    WHERE A.inventory_id = B.inventory_id AND
        B.qty_realization > 0 AND
        B.flg_buy_konsinyasi = vFlagNo AND
        A.warehouse_from_id = C.warehouse_id AND
        B.tenant_id = D.tenant_id AND
        B.product_id = D.product_id AND
        B.serial_number = D.serial_number AND
        B.lot_number = D.lot_number AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.serial_number, B.lot_number, D.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;

    /* 1.k.2) Item Adjustment Stock Qty dengan Qty < 0 */
    WITH temp_in_product_balance AS (
        SELECT A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_id, B.product_status,
            B.serial_number, B.lot_number, B.qty_realization, B.base_uom_id,
            COALESCE(C.flg_buy_konsinyasi, vFlagNo) AS flg_buy_konsinyasi
        FROM in_inventory A
        INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
        LEFT JOIN m_product_custom C ON B.product_id = C.product_id AND C.flg_buy_konsinyasi = vFlagYes
        WHERE A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.status_doc = vStatusRelease AND
            A.doc_type_id = vDocTypeIdAdjStockQty AND
            A.tenant_id = pTenantId
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.serial_number, B.lot_number, D.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM in_inventory A, temp_in_product_balance B, m_warehouse_ou C, in_product_balance D
    WHERE A.inventory_id = B.inventory_id AND
        B.qty_realization < 0 AND
        B.flg_buy_konsinyasi = vFlagNo AND
        A.warehouse_from_id = C.warehouse_id AND
        B.tenant_id = D.tenant_id AND
        B.product_id = D.product_id AND
        B.serial_number = D.serial_number AND
        B.lot_number = D.lot_number AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.serial_number, B.lot_number, D.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;


    /* 1.l. ADJUSTMENT STOCK AMOUNT */
    WITH temp_in_product_price_balance AS (
        SELECT A.tenant_id, A.ou_from_id AS ou_id, A.doc_type_id, A.doc_no, A.doc_date, A.partner_id, A.warehouse_from_id AS warehouse_id,
            A.inventory_id, B.inventory_item_id, B.product_id, B.product_status,
            B.serial_number, B.lot_number, B.qty_realization, B.base_uom_id
        FROM in_inventory A, in_inventory_item B
        WHERE A.inventory_id = B.inventory_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.status_doc = vStatusRelease AND
            A.doc_type_id = vDocTypeIdAdjStockAmount AND
            A.tenant_id = pTenantId
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, B.tenant_id, C.ou_id, B.doc_type_id, B.inventory_id, B.doc_no, B.doc_date, B.partner_id,
        B.product_id, B.serial_number, B.lot_number, D.product_balance_id, B.warehouse_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM temp_in_product_price_balance B, m_warehouse_ou C, in_product_balance D
    WHERE B.warehouse_id = C.warehouse_id AND
        B.tenant_id = D.tenant_id AND
        B.product_id = D.product_id AND
        B.serial_number = D.serial_number AND
        B.lot_number = D.lot_number AND
        B.warehouse_id = pWarehouseId
    GROUP BY B.tenant_id, C.ou_id, B.doc_type_id, B.inventory_id, B.doc_no, B.doc_date, B.partner_id,
        B.product_id, B.serial_number, B.lot_number, D.product_balance_id, B.warehouse_id, B.product_status, B.base_uom_id;


    /* 1.m. DEIVERY ORDER RECEIPT */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
        C.product_id, vSpaceValue, vSpaceValue, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id, SUM(C.qty_return)
    FROM in_do_receipt A, in_do_receipt_item B, in_do_receipt_product C, sl_do D, m_warehouse_ou E
    WHERE A.do_receipt_id = B.do_receipt_id AND
        B.do_receipt_item_id = C.do_receipt_item_id AND
        A.ref_id = D.do_id AND
        A.warehouse_id = E.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdDoReceipt AND
        A.tenant_id = pTenantId AND
        A.warehouse_id = pWarehouseId
    GROUP BY A.tenant_id, E.ou_id, A.doc_type_id, A.do_receipt_id, A.doc_no, A.doc_date, D.partner_ship_to_id,
            C.product_id, C.product_balance_id, A.warehouse_id, C.product_status, C.uom_id;


    /* 1.n. GOODS TRANSFER OUT FROM OUTLET*/
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        A.warehouse_from_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdGoodsTrfOutOutlet AND
        A.tenant_id = pTenantId AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;


    /* 1.o. GOODS TRANSFER OUT */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        A.warehouse_from_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdGoodsTrfOut AND
        A.tenant_id = pTenantId AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;


    /* 1.p. GOODS TRANSFER IN FROM OUTLET */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        A.warehouse_to_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdGoodsTrfInOutlet AND
        A.tenant_id = pTenantId AND
        A.warehouse_to_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;


    /* 1.q. GOODS TRANSFER IN */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        A.warehouse_to_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdGoodsTrfIn AND
        A.tenant_id = pTenantId AND
        A.warehouse_to_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;


    /* 1.r. GOODS TRANSFER IN RECEIPT */
    /* 1.r.1) Item GTI Receipt yang missing dan rejected */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        B.reason_receipt_code IN (vFlagMissing,vFlagRejected) AND
        A.warehouse_from_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdGoodsTrfInReceipt AND
        A.tenant_id = pTenantId AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;

    /* 1.r.2) Item GTI Receipt yang correction */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        B.reason_receipt_code = vFlagCorrection AND
        A.warehouse_to_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdGoodsTrfInReceipt AND
        A.tenant_id = pTenantId AND
        A.warehouse_to_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;

    /* 1.r.3) Item GTI Receipt yang lost */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, vGoodsTransferInReceiptLostDocTypeId, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(-1 * B.qty_realization)
    FROM in_inventory A, in_inventory_item_receipt B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        B.reason_receipt_code = vFlagLost AND
        A.warehouse_from_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdGoodsTrfInReceipt AND
        A.tenant_id = pTenantId AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
             B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;


    /* 1.s. DELIVERY GOODS BORROWING */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        A.warehouse_from_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdGoodsBorrowing AND
        A.tenant_id = pTenantId AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;


    /* 1.t. RETURN GOODS BORROWING */
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization)
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        A.warehouse_to_id = C.warehouse_id AND
        A.doc_date BETWEEN pDateFrom AND pDateTo AND
        A.status_doc = vStatusRelease AND
        A.doc_type_id = vDocTypeIdReturnGoodsBorrowing AND
        A.tenant_id = pTenantId AND
        A.warehouse_from_id = pWarehouseId
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;


    /* 1.u. DELIVERY GOODS OUT OTHER */
    WITH temp_in_product_balance AS (
        SELECT A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_balance_id, B.product_id, B.product_status,
            B.serial_number, B.product_expired_date, B.product_year_made, B.lot_number, B.qty_realization, B.base_uom_id
        FROM in_inventory A, in_inventory_item B
        WHERE A.inventory_id = B.inventory_id AND
            A.doc_date BETWEEN pDateFrom AND pDateTo AND
            A.status_doc = vStatusRelease AND
            A.doc_type_id = vDocTypeIdGoodsOutOther AND
            A.tenant_id = pTenantId AND
            A.warehouse_from_id = pWarehouseId
    )
    INSERT INTO tt_check_log_product_balance_stock_for_trx
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, vSpaceValue, vSpaceValue, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, -1 * SUM(B.qty_realization)
    FROM in_inventory A, temp_in_product_balance B, m_warehouse_ou C
    WHERE A.inventory_id = B.inventory_id AND
        A.warehouse_from_id = C.warehouse_id
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;


    /* 2. Merge log untuk POS */
    WITH grouped_tt_check_log_product_balance_stock_for_trx AS(
        SELECT session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
            product_id, serial_number, lot_number, product_
            balance_id, warehouse_id, product_status, base_uom_id, SUM(qty) AS summed_qty
        FROM tt_check_log_product_balance_stock_for_trx
        WHERE doc_type_id IN (vDocTypeIdPOSShop) AND
            session_id = pSessionId AND
            tenant_id = pTenantId AND
            warehouse_id = pWarehouseId
        GROUP BY session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
            product_id, serial_number, lot_number, product_balance_id, warehouse_id, product_status, base_uom_id
    )
    UPDATE tt_check_log_product_balance_stock_for_trx Z SET qty = A.summed_qty
    FROM grouped_tt_check_log_product_balance_stock_for_trx A
    WHERE A.session_id = Z.session_id AND
        A.tenant_id = Z.tenant_id AND
        A.ou_id = Z.ou_id AND
        A.doc_type_id = Z.doc_type_id AND
        A.ref_id = Z.ref_id AND
        A.doc_no = Z.doc_no AND
        A.doc_date = Z.doc_date AND
        A.partner_id = Z.partner_id AND
        A.product_id = Z.product_id AND
        A.serial_number = Z.serial_number AND
        A.lot_number = Z.lot_number AND
        A.product_balance_id = Z.product_balance_id AND
        A.warehouse_id = Z.warehouse_id AND
        A.product_status = Z.product_status AND
        A.base_uom_id = Z.base_uom_id AND
        Z.session_id = pSessionId AND
        Z.tenant_id = pTenantId AND
        Z.warehouse_id = pWarehouseId AND
        Z.doc_type_id IN (vDocTypeIdPOSShop, vDocTypeIdPOSReturnShop, vDocTypeIdPOSShopInShop, vDocTypeIdPOSReturnShopInShop);


    /* 3. Simpan log_product_balance_stock yang termasuk dalam periode */
    INSERT INTO tt_check_log_product_balance_stock_for_log
        (session_id, log_product_balance_stock_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, warehouse_id, product_balance_id, product_status, base_uom_id, qty,
        version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pSessionId, log_product_balance_stock_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, warehouse_id, product_balance_id, product_status, base_uom_id, qty,
        version, create_datetime, create_user_id, update_datetime, update_user_id
    FROM in_log_product_balance_stock
    WHERE doc_date BETWEEN pDateFrom AND pDateTo AND
        tenant_id = pTenantId AND
        warehouse_id = pWarehouseId;


    /* 4. Update id untuk log_product_balance_stock yang ditemukan */
    UPDATE tt_check_log_product_balance_stock_for_trx Z
    SET log_product_balance_stock_id = A.log_product_balance_stock_id
    FROM tt_check_log_product_balance_stock_for_log A
    WHERE A.tenant_id = Z.tenant_id AND
        A.ou_id = Z.ou_id AND
        A.doc_type_id = Z.doc_type_id AND
        A.ref_id = Z.ref_id AND
        A.doc_no = Z.doc_no AND
        A.doc_date = Z.doc_date 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
        A.qty = Z.qty AND
        Z.session_id = pSessionId AND
        Z.tenant_id = pTenantId AND
        Z.warehouse_id = pWarehouseId;


    /* 5. Simpan log yang kurang sesuai ke dalam tabel result */
    INSERT INTO tt_check_log_product_balance_stock_for_result
        (session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, expected_warehouse_id, expected_product_balance_id, product_status, base_uom_id, expected_qty)
    SELECT session_id, tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
        product_id, warehouse_id, product_balance_id, product_status, base_uom_id, qty
    FROM tt_check_log_product_balance_stock_for_trx
    WHERE log_product_balance_stock_id = vEmptyId AND
        session_id = pSessionId AND
        tenant_id = pTenantId AND
        warehouse_id = pWarehouseId;


    /* 6. Cari log_product_balance_stock yang kurang sesuai */
    UPDATE tt_check_log_product_balance_stock_for_result Z
    SET actual_qty = A.qty, actual_product_balance_id = A.product_balance_id, actual_warehouse_id = A.warehouse_id
    FROM in_log_product_balance_stock A
    WHERE A.tenant_id = Z.tenant_id AND
        A.ou_id = Z.ou_id AND
        A.doc_type_id = Z.doc_type_id AND
        A.ref_id = Z.ref_id AND
        A.doc_no = Z.doc_no AND
        A.doc_date = Z.doc_date AND
        A.product_id = Z.product_id AND
        A.product_status = Z.product_status AND
        Z.session_id = pSessionId AND
        Z.tenant_id = pTenantId AND
        Z.expected_warehouse_id = pWarehouseId;


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