r_report_daily_cashbank

--edit, Putra 28 April 2015 -> tambah kolom ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, remark, partner_name pada tr_daily_cashbank
--edit, Citrahadi 1 Okt 2015 -> tambah mutasi dari dokumen follow up cash advance settlement
--edit, Thea 20 Mei 2016 -> perhitungan untuk beginning balance
--Modified by Adrian, Sep 19, 2017,  menambahkan filter status doc
CREATE OR REPLACE FUNCTION r_report_daily_cashbank(character varying, bigint, bigint, bigint, character varying, bigint, bigint, 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;
    pCashbankId         ALIAS FOR $7;
    pDateStart          ALIAS FOR $8;
    pDateEnd            ALIAS FOR $9;
    pStatusDoc          ALIAS FOR $10;

    vEmptyId                bigint;
    vEmpty                  character varying(1);
    vCashBankInType         character varying(1);
    vCashBankOutType        character varying(1);
    vStatusReleased         character varying(1);
    vStatusVoid             character varying(1);
    vRecordTypeSaldoAwal    character varying(1);
    vRecordTypeKredit       character varying(1);
    vRecordTypeDebet        character varying(1);
    vTglAwalCashbankBalance character varying(8);
    vDocTypePosShop             bigint;
    vDocTypeReturnPosShop       bigint;
    vDocTypePosShopInshop       bigint;
    vDocTypeReturnPosShopInShop bigint;
    vDocTypeCashBankOut         bigint;
    vDocTypeCashBankInOther     bigint;
    vDocTypeCashBankInAr        bigint;
    vDocTypeEdcSettlement       bigint;
    vDocTypeFollowUpCashAdvanceSettlement       bigint;
    vCount                      bigint;

    vStatusApprovedDoc                      character varying;
    vStatusApprovedDocAndOsCbOut            character varying;
    vStatusApprovedDocAndOsCbIn             character varying;
    vAllStatusDoc                           character varying;
    vStatusInProgress                       character varying;
    vStatusDraft                            character varying;

BEGIN

    vEmptyId := -99;
    vEmpty   := ' ';
    vCashBankInType := 'I';
    vCashBankOutType := 'O';
    vStatusReleased := 'R';
    vStatusVoid := 'V';
    vRecordTypeSaldoAwal := 'A';
    vRecordTypeKredit := 'K';
    vRecordTypeDebet := 'D';
    vTglAwalCashbankBalance := ' ';
    vDocTypePosShop := 401;
    vDocTypeReturnPosShop := 402;
    vDocTypePosShopInShop := 403;
    vDocTypeReturnPosShopInShop := 404;
    vDocTypeCashBankOut := 611;
    vDocTypeCashBankInOther := 623;
    vDocTypeCashBankInAr := 621;
    vDocTypeEdcSettlement := 622;
    vDocTypeFollowUpCashAdvanceSettlement := 641;

    vStatusApprovedDoc := 'approvedDoc';
    vStatusApprovedDocAndOsCbOut := 'approvedDocAndOutstandingCBOutOnly';
    vStatusApprovedDocAndOsCbIn := 'approvedDocAndOutstandingCBInOnly';
    vAllStatusDoc := 'allStatusDoc';
    vStatusInProgress := 'I';
    vStatusDraft := 'D';

    DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId;
    DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;

    SELECT MAX(A.cash_bank_date) INTO vTglAwalCashbankBalance
    FROM cb_cashbank_balance A
    WHERE A.tenant_id = pTenantId
    AND A.cashbank_id = pCashbankId
    AND A.rec_type = vRecordTypeSaldoAwal
    AND A.cash_bank_date <= pDateStart;

    IF vTglAwalCashbankBalance IS null OR vTglAwalCashbankBalance IN ('', ' ') THEN

        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, A.cashbank_id, 1, -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
        ' ', ' ', ' ', ' ',
        SUM(CASE WHEN rec_type = vRecordTypeKredit THEN A.amount * -1 ELSE A.amount END) AS sum_amount
        FROM cb_cashbank_balance A
        WHERE A.tenant_id = pTenantId
        AND A.cashbank_id = pCashbankId
        AND A.cash_bank_date < pDateStart
        GROUP BY A.cashbank_id;

    ELSE

        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
        'Beginning Balance', 'Beginning Balance', pDateStart,
        ' ', ' ', ' ', ' ',
        SUM(A.amount) AS sum_amount
        FROM cb_cashbank_balance A
        WHERE A.tenant_id = pTenantId
        AND A.cashbank_id = pCashbankId
        AND A.cash_bank_date = vTglAwalCashbankBalance
        AND A.rec_type = vRecordTypeSaldoAwal
        GROUP BY A.cashbank_id;

        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
        'Beginning Balance', 'Beginning Balance', pDateStart,
        ' ', ' ', ' ', ' ',
        SUM(CASE WHEN rec_type = vRecordTypeKredit THEN A.amount * -1 ELSE A.amount END) AS sum_amount
        FROM cb_cashbank_balance A
        WHERE A.tenant_id = pTenantId
        AND A.cashbank_id = pCashbankId
        AND A.cash_bank_date >= vTglAwalCashbankBalance
        AND A.cash_bank_date < pDateStart
        AND A.rec_type <> vRecordTypeSaldoAwal
        GROUP BY A.cashbank_id;

        --ambil nilai untuk CB trf
        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
        'Beginning Balance', 'Beginning Balance', pDateStart,
        ' ', ' ', ' ', ' ',
        SUM(A.transfer_amount)
        FROM cb_transfer_cashbank A
        WHERE A.tenant_id = pTenantId
        AND A.cashbank_id = pCashbankId
        AND A.status_doc <> vStatusReleased
        AND A.doc_date >= vTglAwalCashbankBalance
        AND A.doc_date < pDateStart
        GROUP BY A.cashbank_id;

        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
        'Beginning Balance', 'Beginning Balance', pDateStart,
        ' ', ' ', ' ', ' ',
        SUM(B.payment_amount)
        FROM cb_transfer_cashbank A
        RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id = B.transfer_cashbank_id
        WHERE A.tenant_id = pTenantId
        AND A.cashbank_id = pCashbankId
        AND A.status_doc <> vStatusReleased
        AND A.doc_date >= vTglAwalCashbankBalance
        AND A.doc_date < pDateStart
        GROUP BY A.cashbank_id;

        --ambil nilai untuk CB out
        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, B.cashbank_id, -1,
        -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
        ' ', ' ', ' ', ' ',
        SUM(B.cashbank_amount)
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        WHERE A.tenant_id = pTenantId
        AND B.cashbank_id = pCashbankId
        AND A.doc_date >= vTglAwalCashbankBalance
        AND A.doc_date < pDateStart
        AND A.status_doc <> vStatusReleased
        AND A.doc_type_id = vDocTypeCashBankOut
        GROUP BY B.cashbank_id;

        --ambil nilai untuk FU CAS
        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, B.cashbank_id, -1,
        -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
        ' ', ' ', ' ', ' ',
        SUM(B.cashbank_amount)
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        WHERE A.tenant_id = pTenantId
        AND B.cashbank_id = pCashbankId
        AND A.doc_date >= vTglAwalCashbankBalance
        AND A.doc_date < pDateStart
        AND A.status_doc <> vStatusReleased
        AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
        AND A.ref_amount > 0
        GROUP BY B.cashbank_id;

        --Adrian, Sep 19, 2017, ambil nilai untuk O/S CB out
        IF (pStatusDoc = vStatusApprovedDocAndOsCbOut)
            OR (pStatusDoc = vAllStatusDoc) THEN

            INSERT INTO tr_daily_cashbank
            (session_id, tenant_id, cashbank_id, order_type_data,
            doc_type_id, doc_type_desc, doc_no, doc_date,
            mode_payment, bank_payment, no_payment, date_payment,
            amount)
            SELECT pSessionId, pTenantId, B.cashbank_id, -1,
            -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
            ' ', ' ', ' ', ' ',
            SUM(-1 * B.cashbank_amount)
            FROM cb_in_out_cashbank A
            INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
            WHERE A.tenant_id = pTenantId
            AND B.cashbank_id = pCashbankId
            AND A.doc_date >= vTglAwalCashbankBalance
            AND A.doc_date < pDateStart
            AND A.status_doc IN (vStatusDraft, vStatusInProgress)
            AND A.doc_type_id = vDocTypeCashBankOut
            GROUP BY B.cashbank_id;

        END IF;

        --Adrian, Sep 19, 2017, ambil nilai untuk O/S CB in
        IF (pStatusDoc = vStatusApprovedDocAndOsCbIn)
            OR (pStatusDoc = vAllStatusDoc) THEN

            --ambil nilai untuk O/S CB in AR
            INSERT INTO tr_daily_cashbank
            (session_id, tenant_id, cashbank_id, order_type_data,
            doc_type_id, doc_type_desc, doc_no, doc_date,
            mode_payment, bank_payment, no_payment, date_payment,
            amount)
            SELECT pSessionId, pTenantId, B.cashbank_id, -1,
            -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
            ' ', ' ', ' ', ' ',
            SUM(B.cashbank_amount)
            FROM cb_in_out_cashbank A
            INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
            WHERE A.tenant_id = pTenantId
            AND B.cashbank_id = pCashbankId
            AND A.doc_date >= vTglAwalCashbankBalance
            AND A.doc_date < pDateStart
            AND A.status_doc IN (vStatusDraft, vStatusInProgress)
            AND A.doc_type_id = vDocTypeCashBankInAr
            GROUP BY B.cashbank_id;

            --ambil nilai untuk O/S CB in other
            INSERT INTO tr_daily_cashbank
            (session_id, tenant_id, cashbank_id, order_type_data,
            doc_type_id, doc_type_desc, doc_no, doc_date,
            mode_payment, bank_payment, no_payment, date_payment,
            amount)
            SELECT pSessionId, pTenantId, B.cashbank_id, -1,
            -99, 'Beginning Balance', 'Beginning Balance', pDateStart,
            ' ', ' ', ' ', ' ',
            SUM(B.cashbank_amount)
            FROM cb_in_out_cashbank A
            INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
            WHERE A.tenant_id = pTenantId
            AND B.cashbank_id = pCashbankId
            AND A.doc_date >= vTglAwalCashbankBalance
            AND A.doc_date < pDateStart
            AND A.status_doc IN (vStatusDraft, vStatusInProgress)
            AND A.doc_type_id = vDocTypeCashBankInOther
            GROUP BY B.cashbank_id;

        END IF;

        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, cashbank_id, 1,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        SUM(amount)
        FROM tr_daily_cashbank
        WHERE session_id = pSessionId
        AND tenant_id = pTenantId
        AND order_type_data = -1
        GROUP BY cashbank_id, order_type_data, doc_type_id, doc_type_desc, doc_no, doc_date, mode_payment, bank_payment, no_payment, date_payment;

        DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId AND order_type_data = -1;

    END IF;

    -- cb trf
    INSERT INTO tr_daily_cashbank
    (session_id, tenant_id, cashbank_id, order_type_data,
    doc_type_id, doc_type_desc, doc_no, doc_date,
    mode_payment, bank_payment, no_payment, date_payment,
    amount, remark)
    SELECT pSessionId, pTenantId, pCashbankId, 2,
    A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
    A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
    -1 * A.transfer_amount, A.remark
    FROM cb_transfer_cashbank A
    WHERE A.tenant_id = pTenantId
    AND A.cashbank_id = pCashbankId
    AND A.status_doc = vStatusReleased
    AND A.doc_date BETWEEN pDateStart AND pDateEnd;

    /*
     * PS, 27 Maret 2015
     * update amount pada tr_daily_cashbank, dikurangi dengan cost_amount dari cb_transfer_cashbank_cost
     * memakai RIGHT JOIN karena transaksi di cb_transfer_cashbank_cost bisa kosong untuk sebuah transfer cashbank
     */
--  UPDATE tr_daily_cashbank C
--  SET amount = C.amount - B.cost_amount
--  FROM cb_transfer_cashbank A
--  RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id=B.transfer_cashbank_id
--  WHERE A.tenant_id = pTenantId
--  AND A.cashbank_id = pCashbankId
--  AND A.status_doc = vStatusReleased
--  AND A.doc_date BETWEEN pDateStart AND pDateEnd;

    UPDATE tr_daily_cashbank C
    SET amount = C.amount - B.payment_amount
    FROM cb_transfer_cashbank A
    RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id=B.transfer_cashbank_id
    WHERE A.tenant_id = pTenantId
    AND A.cashbank_id = pCashbankId
    AND A.doc_no = C.doc_no
    AND A.doc_date = C.doc_date
    AND A.doc_type_id = C.doc_type_id
    AND A.status_doc = vStatusReleased
    AND A.doc_date BETWEEN pDateStart AND pDateEnd;

    INSERT INTO tr_daily_cashbank
    (session_id, tenant_id, cashbank_id, order_type_data,
    doc_type_id, doc_type_desc, doc_no, doc_date,
    mode_payment, bank_payment, no_payment, date_payment,
    amount, remark)
    SELECT pSessionId, pTenantId, pCashbankId, 2,
    B.doc_type_id, f_get_doc_desc(B.doc_type_id), B.doc_no, B.doc_date,
    A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
    A.receive_amount, A.remark
    FROM cb_transfer_cashbank_receive A
    INNER JOIN cb_transfer_cashbank B ON A.transfer_cashbank_id = B.transfer_cashbank_id
    WHERE B.tenant_id = pTenantId
    AND A.cashbank_to_id = pCashbankId
    AND B.status_doc = vStatusReleased
    AND B.doc_date BETWEEN pDateStart AND pDateEnd;

    -- cash bank out
    /* Note : WTC & TKP 24 Mei 2016
     * tidak perlu join ke cb_in_out_cashbank_cost
     * dan untuk join ke cb_trx_cashbank_balance langsung di inner join aja
     */
    INSERT INTO tr_daily_cashbank
    (session_id, tenant_id, cashbank_id, order_type_data,
    doc_type_id, doc_type_desc, doc_no, doc_date,
    mode_payment, bank_payment, no_payment, date_payment,
    amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
    partner_name)
    SELECT pSessionId, pTenantId, B.cashbank_id, 2,
    A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
    B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
    (-1 * B.cashbank_amount), A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
    f_get_partner_name(A.partner_id) AS partner_name
    FROM cb_in_out_cashbank A
    INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
    LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
    LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
    WHERE A.tenant_id = pTenantId
    AND B.cashbank_id = pCashbankId
    AND A.doc_date BETWEEN pDateStart AND pDateEnd
    AND A.status_doc = vStatusReleased
    AND A.doc_type_id = vDocTypeCashBankOut
    GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

    -- cash bank in AR
    /* Note : WTC & TKP 24 Mei 2016
     * tidak perlu join ke cb_trx_cashbank_balance
     */
    INSERT INTO tr_daily_cashbank
    (session_id, tenant_id, cashbank_id, order_type_data,
    doc_type_id, doc_type_desc, doc_no, doc_date,
    mode_payment, bank_payment, no_payment, date_payment,
    amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
    partner_name)
    SELECT pSessionId, pTenantId, B.cashbank_id, 2,
    A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
    B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
    B.cashbank_amount - SUM(COALESCE(C.cost_amount, 0)),
    A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, f_get_partner_name(A.partner_id)
    FROM cb_in_out_cashbank A
    INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
    LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
    LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
    WHERE A.tenant_id = pTenantId
    AND B.cashbank_id = pCashbankId
    AND A.doc_date BETWEEN pDateStart AND pDateEnd
    AND A.status_doc = vStatusReleased
    AND A.doc_type_id = vDocTypeCashBankInAr
    GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

    -- cash bank in other
    /* Note : WTC & TKP 24 Mei 2016
     * tidak perlu join ke cb_trx_cashbank_balance
     *
     * Note : Didit 24 Agustus 2016
     * Perhitungkan juga dokumen yang statusnya void karena hasil konversi
     */
    INSERT INTO tr_daily_cashbank
    (session_id, tenant_id, cashbank_id, order_type_data,
    doc_type_id, doc_type_desc, doc_no, doc_date,
    mode_payment, bank_payment, no_payment, date_payment,
    amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
    partner_name)
    SELECT pSessionId, pTenantId, B.cashbank_id, 2,
    A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
    B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
    B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
    f_get_partner_name(A.partner_id)
    FROM cb_in_out_cashbank A
    INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
    LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
    LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
    WHERE A.tenant_id = pTenantId
    AND B.cashbank_id = pCashbankId
    AND A.doc_date BETWEEN pDateStart AND pDateEnd
    AND A.status_doc IN (vStatusReleased, vStatusVoid)
    AND A.doc_type_id = vDocTypeCashBankInOther
    GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

    --Adrian, Sep 19, 2017, O/S cash bank out
    IF (pStatusDoc = vStatusApprovedDocAndOsCbOut)
        OR (pStatusDoc = vAllStatusDoc) THEN

        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
        partner_name)
        SELECT pSessionId, pTenantId, B.cashbank_id, 2,
        A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
        B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
        (-1 * B.cashbank_amount), A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
        f_get_partner_name(A.partner_id) AS partner_name
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
        WHERE A.tenant_id = pTenantId
        AND B.cashbank_id = pCashbankId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.status_doc IN (vStatusDraft, vStatusInProgress)
        AND A.doc_type_id = vDocTypeCashBankOut
        GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

    END IF;

    --Adrian, Sep 19, 2017, O/S cash bank in
    IF (pStatusDoc = vStatusApprovedDocAndOsCbIn)
        OR (pStatusDoc = vAllStatusDoc) THEN

        --O/S cash bank in AR
        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
        partner_name)
        SELECT pSessionId, pTenantId, B.cashbank_id, 2,
        A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
        B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
        B.cashbank_amount - SUM(COALESCE(C.cost_amount, 0)),
        A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, f_get_partner_name(A.partner_id)
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
        WHERE A.tenant_id = pTenantId
        AND B.cashbank_id = pCashbankId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.status_doc IN (vStatusDraft, vStatusInProgress)
        AND A.doc_type_id = vDocTypeCashBankInAr
        GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

        --O/S cash bank in other
        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
        partner_name)
        SELECT pSessionId, pTenantId, B.cashbank_id, 2,
        A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
        B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
        B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
        f_get_partner_name(A.partner_id)
        FROM cb_in_out_cashbank A
        INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
        LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
        LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
        WHERE A.tenant_id = pTenantId
        AND B.cashbank_id = pCashbankId
        AND A.doc_date BETWEEN pDateStart AND pDateEnd
        AND A.status_doc IN (vStatusDraft, vStatusInProgress)
        AND A.doc_type_id = vDocTypeCashBankInOther
        GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

    END IF;

    -- edc settlement
    INSERT INTO tr_daily_cashbank
    (session_id, tenant_id, cashbank_id, order_type_data,
    doc_type_desc, doc_no, doc_date,
    mode_payment, bank_payment, no_payment, date_payment,
    amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
    partner_name)
    SELECT pSessionId, pTenantId, B.cashbank_id, 2,
    f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
    B.mode_payment, B.bank_payment, A.doc_no, A.doc_date,
    B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
    f_get_partner_name(A.partner_id)
    FROM cb_in_out_cashbank A
    INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
    LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
    LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
    WHERE A.tenant_id = pTenantId
    AND B.cashbank_id = pCashbankId
    AND A.doc_date BETWEEN pDateStart AND pDateEnd
    AND A.status_doc = vStatusReleased
    AND A.doc_type_id = vDocTypeEdcSettlement
    GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

    -- Added by Citrahadi, 2015-10-01
    -- follow up cash advance settlement
    -- ref_amount minus artinya follow up dgn cashbank in
    INSERT INTO tr_daily_cashbank
    (session_id, tenant_id, cashbank_id, order_type_data,
    doc_type_id, doc_type_desc, doc_no, doc_date,
    mode_payment, bank_payment, no_payment, date_payment,
    amount, remark, ref_id, ref_doc_type_id, ref_doc_no,
    ref_doc_date, partner_name)
    SELECT pSessionId, pTenantId, B.cashbank_id, 2,
    A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
    B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
    CASE WHEN A.ref_amount < 0  THEN B.cashbank_amount ELSE (-1 * B.cashbank_amount) END, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no,
    D.payment_doc_date, f_get_partner_name(A.partner_id) AS partner_name
    FROM cb_in_out_cashbank A
    INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
    LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
    LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
    WHERE A.tenant_id = pTenantId
    AND B.cashbank_id = pCashbankId
    AND A.doc_date BETWEEN pDateStart AND pDateEnd
    AND A.status_doc = vStatusReleased
    AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
    GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank,
        B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.ref_amount;

    /*
     * ambil data transaksi penjualan pos dan return pos untuk cash payment
     */
    INSERT INTO tr_kartu_kas_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
    amount_debit, amount_credit)
    SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
        A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
        SUM(B.conversion_amount), 0
    FROM i_trx_pos A, i_trx_pos_cash_payment B, m_document C, m_cashbank D, m_cashbank_ou E
    WHERE A.tenant_id = pTenantId AND
        --A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateStart AND pDateEnd AND
        A.doc_type_id IN (vDocTypePosShop, vDocTypePosShopInShop) AND
        A.trx_pos_id = B.trx_pos_id AND
        A.tenant_id = B.tenant_id AND
        A.process_no = B.process_no AND
        B.curr_payment_code = D.curr_code AND
        D.flg_cash_bank = 'C' AND
        D.cashbank_id = E.cashbank_id AND
        A.ou_id = E.ou_id AND
        A.doc_type_id = C.doc_type_id AND
        D.cashbank_id = pCashbankId
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;

    INSERT INTO tr_kartu_kas_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
    amount_debit, amount_credit)
    SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
        A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
        SUM(B.conversion_amount), 0
    FROM i_trx_pos A, i_trx_pos_cash_payment B, m_document C, m_cashbank D, m_cashbank_ou E
    WHERE A.tenant_id = pTenantId AND
        --A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateStart AND pDateEnd AND
        A.doc_type_id IN (vDocTypeReturnPosShop, vDocTypeReturnPosShopInShop) AND
        A.trx_pos_id = B.trx_pos_id AND
        A.tenant_id = B.tenant_id AND
        A.process_no = B.process_no AND
        B.curr_payment_code = D.curr_code AND
        D.flg_cash_bank = 'C' AND
        D.cashbank_id = E.cashbank_id AND
        A.ou_id = E.ou_id AND
        A.doc_type_id = C.doc_type_id AND
        D.cashbank_id = pCashbankId
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;

    /*
     * ambil data transaksi penjualan pos dan return pos untuk pengembalian cash
     */
    INSERT INTO tr_kartu_kas_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
    amount_debit, amount_credit)
    SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
        A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
        0, SUM(A.total_refund)
    FROM i_trx_pos A, m_document C, m_cashbank D, m_cashbank_ou E
    WHERE A.tenant_id = pTenantId AND
        --A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateStart AND pDateEnd AND
        A.doc_type_id IN (vDocTypePosShop, vDocTypePosShopInShop) AND
        A.curr_code = D.curr_code AND
        D.flg_cash_bank = 'C' AND
        D.cashbank_id = E.cashbank_id AND
        A.ou_id = E.ou_id AND
        A.doc_type_id = C.doc_type_id AND
        D.cashbank_id = pCashbankId
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;

    INSERT INTO tr_kartu_kas_by_doc_type
    (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
    doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
    amount_debit, amount_credit)
    SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
        A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
        0, SUM(A.total_refund)
    FROM i_trx_pos A, m_document C, m_cashbank D, m_cashbank_ou E
    WHERE A.tenant_id = pTenantId AND
        --A.ou_id = pOuId AND
        A.doc_date BETWEEN pDateStart AND pDateEnd AND
        A.doc_type_id IN (vDocTypeReturnPosShop, vDocTypeReturnPosShopInShop) AND
        A.curr_code = D.curr_code AND
        D.flg_cash_bank = 'C' AND
        D.cashbank_id = E.cashbank_id AND
        A.ou_id = E.ou_id AND
        A.doc_type_id = C.doc_type_id AND
        D.cashbank_id = pCashbankId
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;

    INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
    SELECT pSessionId, pTenantId, A.cashbank_id, 2,
           A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
           'CASH', ' ', ' ', ' ',
           SUM(A.amount_debit) - SUM(A.amount_credit)
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.session_id = pSessionId
    GROUP BY A.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date;

    INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
    SELECT pSessionId, pTenantId, A.cashbank_id, 2,
           A.doc_type_id, f_get_doc_desc(A.doc_type_id), 'VOID ' || A.doc_no, A.doc_date,
           'CASH', ' ', ' ', ' ',
           -1 * (SUM(A.amount_debit) - SUM(A.amount_credit))
    FROM tr_kartu_kas_by_doc_type A
    WHERE A.session_id = pSessionId
    AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
                    WHERE A.tenant_id = B.tenant_id AND
                    A.doc_no = B.doc_no AND
                    A.doc_date = B.doc_date AND
                    A.ou_id = B.ou_id)
    GROUP BY A.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date;

    SELECT COUNT(1) INTO vCount
    FROM tr_daily_cashbank
    WHERE session_id = pSessionId AND order_type_data = 1;

    IF vCount = 0 THEN
        INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount)
        SELECT pSessionId, pTenantId, pCashbankId, 1, 'Beginning Balance', 'Beginning Balance', pDateStart,
        ' ', ' ', ' ', ' ',
        0;
    END IF;

    Open pRefHeader FOR
    SELECT pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name, pDateStart AS date_start, pDateEnd AS date_end,
           pDateStart AS date_trx, f_get_username(pUserId) AS username, pDatetime AS datetime,
           pCashbankId AS cashbank_id, A.cashbank_code AS cashbank_code, A.cashbank_name AS cashbank_name,
           A.bank_code AS bank_code, A.account_no AS account_no, A.account_name1 AS account_name1, A.account_name2 AS account_name2,
           A.curr_code AS curr_code
    FROM m_cashbank A WHERE A.cashbank_id = pCashBankId;

    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    SELECT A.doc_type_desc AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no,
           A.mode_payment AS mode_payment, A.bank_payment AS bank_payment,
           A.ref_doc_date AS ref_doc_date, A.ref_doc_no AS ref_doc_no,
           CASE WHEN (A.no_payment = '-') THEN '' ELSE A.no_payment END AS no_payment,
           CASE WHEN (A.amount > 0 ) THEN A.amount ELSE 0 END AS debit,
           CASE WHEN (A.amount < 0 ) THEN -1 * A.amount ELSE 0 END AS credit,
           SUM(A.amount) OVER (ORDER BY A.order_type_data, A.doc_date, A.doc_no) AS amount,
           A.remark, A.partner_name,
           (CASE WHEN length(TRIM(A.remark)) = 0 THEN (CEIL(1/36.0))*21 ELSE (CEIL(length(TRIM(A.remark))/36.0))*21 END) AS height
    FROM tr_daily_cashbank A
    WHERE A.session_id = pSessionId
    AND A.tenant_id = pTenantId
    GROUP BY A.doc_type_desc, A.doc_date, A.doc_no, A.mode_payment, A.bank_payment, A.ref_doc_date, A.ref_doc_no, A.order_type_data, A.amount, A.remark, A.partner_name, A.no_payment
    ORDER BY A.order_type_data, A.doc_date, A.doc_no;

    RETURN NEXT pRefDetail;

    DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId;
    DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;

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