CREATE OR REPLACE FUNCTION r_stock_amount_card(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetail          REFCURSOR := 'refDetail';
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pUserId             ALIAS FOR $3;
    pRoleId             ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;
    pOuId               ALIAS FOR $6;
    pProductCode            ALIAS FOR $7;
    pProductName            ALIAS FOR $8;
    pPeriodStart            ALIAS FOR $9;
    pPeriodEnd          ALIAS FOR $10;

    vEmptyId            bigint := -99;
    vRgDocTypeId            bigint := 111;
    vDoDocTypeId            bigint := 311;
    vCnDocTypeId            bigint := 511;
    vRnDocTypeId            bigint := 502;
    vDoReceiptDocTypeId     bigint := 526;
    vAdjStockQtyDocTypeId       bigint := 521;
    vAdjStockAmountDocTypeId    bigint := 522;
    vCostingAllocationDocTypeId bigint := 528;
    vGoodsTransferOutDocTypeId  bigint := 533;
    vGoodsTransferInDocTypeId   bigint := 535;
    vDeliveryGoodsBorrowingDocTypeId    bigint := 551;
    vReturnGoodsBorrowingDocTypeId      bigint := 552;
    vDeliveryGoodsBorrowingToSalesDocTypeId     bigint := 377;
    vZero               numeric := 0;
    vEmptyString            character varying := '';
    vEmptyData          character varying := '-';
    vReleased           character varying := 'R';
    vFinal              character varying := 'F';
    vInProgress         character varying := 'I';
    vYes                character varying := 'Y';
    vBgnBalance         character varying := 'Beginning Balance';
    vBpbDoc             character varying := 'BPB';
    vDoDoc              character varying := 'DO';
    vCnDoc              character varying := 'Claim Note';
    vRnDoc              character varying := 'Return Note';
    vDoReceiptDoc           character varying := 'DO Receipt';
    vAdjStockQtyDoc         character varying := 'Adjustment Stock Qty';
    vAdjStockAmountDoc      character varying := 'Adjustment Stock Amount';
    vCostingAllocationDoc   character varying := 'Costing Allocation to Product';
    vGoodsTransferOutDoc    character varying := 'Goods Transfer Out';
    vDeliveryGoodsBorrowingDoc  character varying := 'Delivery Goods Borrowing';
    vDeliveryGoodsBorrowingToSalesDoc   character varying := 'Delivery Goods Borrowing to Sales Conversion';
    vFilterProductCode      character varying := '';
    vFilterProductName      character varying := '';
    vSignJournalCredit      character varying := 'C';
BEGIN

    IF(pProductCode <> '') THEN
        vFilterProductCode := ' AND UPPER(f_get_product_code(X.product_id)) LIKE UPPER(''%'||pProductCode||'%'')';
    END IF;

    IF(pProductName <> '') THEN
        vFilterProductName := ' AND UPPER(f_get_product_name(X.product_id)) LIKE UPPER(''%'||pProductName||'%'')';
    END IF;


    --saldo awal
    -- untuk yang ada beginning balance nya pada bulan tahun tersebut
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, $2, $2,
        A.doc_type_id, $3, $4, $4, $4, $4, sum(A.qty), sum(A.gl_amount),
        sum(A.qty), sum(A.gl_amount)
    from in_summary_monthly_amount A
    JOIN m_product X ON X.product_id = A.product_id
    where date_year_month = $5
    AND doc_type_id = $6
    AND A.ou_bu_id = $7
    AND A.tenant_id = $8'
    || vFilterProductCode || vFilterProductName ||
    ' group by A.product_id, X.product_code, X.product_name, A.doc_type_id
     order by A.product_id'
    USING pSessionId, vEmptyString, vBgnBalance, vZero, pPeriodStart, vEmptyId, pOuId, pTenantId;

    --untuk yang tidak ada beginning balance nya pada bulan tahun tersebut
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, $2, $2,
        $3, $4, $5, $5, $5, $5, $5, $5,
        $5, $5
    from m_product X
    where X.product_id NOT IN(select A.product_id from in_summary_monthly_amount A
        JOIN m_product X ON X.product_id = A.product_id
        where A.date_year_month = $6
        AND A.doc_type_id = $3
        AND A.tenant_id = $7
        group by A.product_id, X.product_code, X.product_name, A.doc_type_id, X.product_id) '
        || vFilterProductCode || vFilterProductName ||
    ' order by X.product_id'
    USING pSessionId, vEmptyString, vEmptyId, vBgnBalance, vZero, pPeriodStart, pTenantId;




    --RG
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date, A.doc_no,
        A.doc_type_id, $2, SUM(D.qty), SUM(D.gl_amount), $3, $3, $3, $3,
        SUM(D.qty), SUM(D.gl_amount)
    from pu_receive_goods A
    JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
    JOIN m_product X ON X.product_id = B.product_id
    INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
    JOIN gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.receive_goods_id = C.doc_id
        AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND E.ou_bu_id = C.ou_bu_id
    JOIN gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.receive_goods_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (E.ou_bu_id = $6 OR E.ou_id = $6)
    AND A.tenant_id = $7
    AND C.status_doc = $8 '
    || vFilterProductCode || vFilterProductName ||
    ' AND A.doc_type_id = $9
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
    USING pSessionId, vBpbDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vRgDocTypeId;

    --DO
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date, A.doc_no,
        A.doc_type_id, $2, $3, $3, SUM(D.qty), SUM(D.gl_amount), $3, $3,
        SUM(D.qty*(-1)), SUM(D.gl_amount*(-1))
    from sl_do A
    JOIN sl_do_item B ON A.do_id = B.do_id
    JOIN m_product X ON X.product_id = B.product_id
    INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
    JOIN gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.do_id = C.doc_id
        AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND E.ou_bu_id = C.ou_bu_id
    JOIN gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.do_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (E.ou_bu_id = $6 OR E.ou_id = $6)
    AND A.tenant_id = $7
    AND C.status_doc = $8 '
    || vFilterProductCode || vFilterProductName ||
    ' AND A.doc_type_id = $9
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
    USING pSessionId, vDoDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vDoDocTypeId;

    --CLAIM NOTE
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, $3, $3, SUM(D.qty), SUM(D.gl_amount), $3, $3,
        SUM(D.qty*(-1)), SUM(D.gl_amount*(-1))
    FROM in_inventory A
    JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_from_id = Z.ou_id
    join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
        AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
    join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND C.status_doc = $8
    AND A.doc_type_id = $9
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
    USING pSessionId, vCnDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vCnDocTypeId;

    --RETURN NOTE
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, SUM(D.qty), SUM(D.gl_amount), $3, $3, $3, $3,
        SUM(D.qty), SUM(D.gl_amount)
    FROM in_inventory A
    JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_from_id = Z.ou_id
    join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
        AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
    join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND C.status_doc = $8
    AND A.doc_type_id = $9
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
    USING pSessionId, vRnDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vRnDocTypeId;

    --DO RECEIPT
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date, A.doc_no,
        A.doc_type_id, $2, SUM(D.qty), SUM(D.gl_amount), $3, $3, $3, $3,
        SUM(D.qty), SUM(D.gl_amount)
    from in_do_receipt A
    JOIN in_do_receipt_item B ON A.do_receipt_id = B.do_receipt_id
    JOIN m_product X ON X.product_id = B.product_id
    INNER JOIN m_ou_structure E ON A.ou_id = E.ou_id
    JOIN gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.do_receipt_id = C.doc_id
        AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND E.ou_bu_id = C.ou_bu_id
    join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.do_receipt_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (E.ou_bu_id = $6 OR E.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND C.status_doc = $8
    AND A.doc_type_id = $9
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
    USING pSessionId, vDoReceiptDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vDoReceiptDocTypeId;


    --ADJ STOCK QTY
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, SUM(CASE WHEN D.sign_journal <> $10 THEN D.qty ELSE 0 END) AS qty_in,
        SUM(CASE WHEN D.sign_journal <> $10 THEN D.gl_amount ELSE 0 END) AS amount_in,
        SUM(CASE WHEN D.sign_journal = $10 THEN D.qty ELSE 0 END) AS qty_out,
        SUM(CASE WHEN D.sign_journal = $10 THEN D.gl_amount ELSE 0 END) AS amount_out,
        $3, $3,
        SUM(CASE WHEN D.sign_journal = $10 THEN D.qty * (-1) ELSE D.qty END), SUM(CASE WHEN D.sign_journal = $10 THEN D.gl_amount * (-1) ELSE D.gl_amount END)
    FROM in_inventory A
    JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_from_id = Z.ou_id
    join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
        AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
    join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND C.status_doc = $8
    AND A.doc_type_id = $9
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
    USING pSessionId, vAdjStockQtyDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vAdjStockQtyDocTypeId, vSignJournalCredit;

    --ADJ STOCK AMOUNT
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, SUM(CASE WHEN D.sign_journal <> $10 THEN D.qty ELSE 0 END) AS qty_in,
        SUM(CASE WHEN D.sign_journal <> $10 THEN D.gl_amount ELSE 0 END) AS amount_in,
        SUM(CASE WHEN D.sign_journal = $10 THEN D.qty ELSE 0 END) AS qty_out,
        SUM(CASE WHEN D.sign_journal = $10 THEN D.gl_amount ELSE 0 END) AS amount_out,
        $3, $3,
        SUM(CASE WHEN D.sign_journal = $10 THEN D.qty * (-1) ELSE D.qty END), SUM(CASE WHEN D.sign_journal = $10 THEN D.gl_amount * (-1) ELSE D.gl_amount END)
    FROM in_inventory A
    JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_from_id = Z.ou_id
    join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
        AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
    join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND C.status_doc = $8
    AND A.doc_type_id = $9
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
    USING pSessionId, vAdjStockAmountDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vAdjStockAmountDocTypeId, vSignJournalCredit;

    -- COSTING ALLOCATION TO PRODUCT
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, $3 AS qty_in,
        $3 AS amount_in,
        $3 AS qty_out,
        SUM(D.gl_amount) AS amount_out,
        $3, $3,
        $3, SUM(D.gl_amount)
    FROM in_inventory A
    JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_from_id = Z.ou_id
    join gl_journal_trx C ON A.doc_type_id = C.doc_type_id AND A.inventory_id = C.doc_id
        AND A.tenant_id = C.tenant_id AND A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND Z.ou_bu_id = C.ou_bu_id
    join gl_journal_trx_item D ON C.journal_trx_id = D.journal_trx_id AND B.product_id = D.product_id AND D.ref_id = B.inventory_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND C.status_doc = $8
    AND A.doc_type_id = $9
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id'
    USING pSessionId, vCostingAllocationDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vCostingAllocationDocTypeId;

    -- GOODS TRANSFER OUT
    -- Yang belum/sedang dibuatkan Goods Transfer In
    -- Added by Adrian, Jan 31, 2017
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, $3 AS qty_in,
        $3 AS amount_in,
        SUM(B.qty_realization) AS qty_out,
        $3,
        $3, $3,
        (SUM(B.qty_realization) * (-1)), $3
    FROM in_inventory A
    JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_from_id = Z.ou_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND A.status_doc = $8
    AND A.doc_type_id = $9
    AND A.inventory_id NOT IN(
        SELECT ref_id
        FROM in_inventory
        WHERE doc_type_id = $10 AND status_doc = $8
    )
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id, B.qty_realization'
    USING pSessionId, vGoodsTransferOutDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vGoodsTransferOutDocTypeId, vGoodsTransferInDocTypeId;

    -- GOODS TRANSFER OUT
    -- Yang sudah dibuatkan Goods Transfer In
    -- Added by Adrian, Jan 31, 2017
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, $3 AS qty_in,
        $3 AS amount_in,
        SUM(D.qty_out - D.qty_in) AS qty_out,
        $3,
        $3, $3,
        (SUM(D.qty_out - D.qty_in) * (-1)), $3
    FROM in_inventory A
    JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_from_id = Z.ou_id
    INNER JOIN in_inventory_item C ON C.ref_item_id = B.inventory_item_id
    INNER JOIN in_balance_transfer_in_item D ON D.inventory_item_id = C.inventory_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND A.status_doc = $8
    AND A.doc_type_id = $9
    AND D.flg_receipt <> $10
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id, B.qty_realization, B.qty_request, D.qty_out, D.qty_in'
    USING pSessionId, vGoodsTransferOutDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vGoodsTransferOutDocTypeId, vYes;

    -- DELIVERY GOODS BORROWING
    -- Added by Adrian, Jan 31, 2017
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, $3 AS qty_in,
        $3 AS amount_in,
        SUM(C.qty_do - C.qty_return - C.qty_sales) AS qty_out,
        $3,
        $3, $3,
        (SUM(C.qty_do - C.qty_return - C.qty_sales) * (-1)), $3
    FROM in_inventory A
    JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_from_id = Z.ou_id
    INNER JOIN in_inventory_borrow_balance_item C ON C.inventory_item_id = B.inventory_item_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND A.status_doc = $8
    AND A.doc_type_id = $9
    AND C.status_item <> $10
    AND C.qty_do > C.qty_return
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id, B.qty_realization'
    USING pSessionId, vDeliveryGoodsBorrowingDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vReleased, vDeliveryGoodsBorrowingDocTypeId, vFinal;

    -- DELIVERY GOODS BORROWING TO SALES CONVERSION
    -- Added by Adrian, Feb 14, 2017
    EXECUTE 'INSERT INTO tt_r_stock_amount_card(
       session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in, amount_in, qty_out, amount_out, qty_balance, amount_balance,
       qty_original, amount_original
    )
    SELECT $1, X.product_code, X.product_name, A.doc_date AS note_date, A.doc_no AS note_no,
        A.doc_type_id, $2, SUM(B.qty_so) AS qty_in,
        0 AS amount_in,
        SUM(B.qty_so) AS qty_out,
        SUM(B.gross_sell_price) AS amount_out,
        $3, $3,
        $3, (SUM(B.gross_sell_price) * -1)
    FROM sl_so A
    JOIN sl_so_item B ON A.so_id = B.so_id
    JOIN m_product X ON X.product_id = B.product_id
    join m_ou_structure Z ON A.ou_id = Z.ou_id
    INNER JOIN in_inventory_borrow_balance_item C ON C.inventory_item_id = B.ref_id
    WHERE SUBSTRING(A.doc_date, 1, 6) BETWEEN $4 AND $5
    AND (Z.ou_bu_id = $6 OR Z.ou_id = $6)
    AND A.tenant_id = $7 '
    || vFilterProductCode || vFilterProductName ||
    ' AND A.status_doc <> $8
    AND A.doc_type_id = $9
    AND C.status_item <> $10
    AND C.qty_do > C.qty_return
    GROUP BY X.product_code, X.product_name, A.doc_date, A.doc_no, A.doc_type_id, B.qty_so'
    USING pSessionId, vDeliveryGoodsBorrowingToSalesDoc, vZero, pPeriodStart, pPeriodEnd, pOuId, pTenantId, vInProgress, vDeliveryGoodsBorrowingToSalesDocTypeId, vFinal;



    --hapus data produk yang tidak ada transaksi pada periode tersebut
    WITH product_delete AS (
        SELECT session_id, product_code, COUNT(product_code) AS count_delete
        FROM tt_r_stock_amount_card
        WHERE session_id = pSessionId
        GROUP BY session_id, product_code
    )
    DELETE FROM tt_r_stock_amount_card A
    WHERE EXISTS (
        SELECT 1 FROM product_delete B
        WHERE A.product_code = B.product_code
        AND A.session_id = B.session_id
        AND B.count_delete = 1
    );


    Open pRefHeader FOR
    SELECT f_get_ou_name(pOuId) AS ou, pPeriodStart AS start_month_year, pPeriodEnd AS end_month_year,
    pProductCode AS product_code_filter,
    pProductName AS product_name_filter, pDatetime AS datetime, f_get_username(pUserId) AS username;

    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT session_id, product_code, product_name, transaction_date, transaction_no,
       doc_type_id, transaction_type, qty_in AS in_qty, amount_in AS in_amount, qty_out AS out_qty, amount_out AS out_amount,
       SUM(qty_original) OVER (PARTITION BY product_code ORDER BY transaction_date, doc_type_id, transaction_no) AS balance_qty,
       SUM(amount_original) OVER (PARTITION BY product_code ORDER BY transaction_date, doc_type_id, transaction_no) AS balance_amount,
       qty_original, amount_original
    FROM tt_r_stock_amount_card
    WHERE session_id = pSessionId
    ORDER BY product_code, transaction_date, doc_type_id, transaction_no;

    RETURN NEXT pRefDetail ;
    DELETE FROM tt_r_stock_amount_card WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;