i_process_pos_shop

CREATE OR REPLACE FUNCTION i_process_pos_shop(bigint, character varying, character varying, bigint, bigint, bigint, character varying, bigint)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;
    pOutletId           ALIAS FOR $4;
    pOuId               ALIAS FOR $5;
    pWarehouseId        ALIAS FOR $6;
    pDatetime           ALIAS FOR $7;
    pUserId             ALIAS FOR $8;

    vFlagNo                 character varying(1);
    vProductStatus          character varying(5);
    vEmptyId                bigint;
    vDocTypeAdjStock        bigint;
    vFlagCash               character varying(1);
    vDebitType              character varying(1);
    vFlagYes                character varying(1);
    vEmptyValue             character varying(1);
    vJournalType            character varying(20);
    vJournalTypeDoPos       character varying(20);
    vJournalTypeAdjStock    character varying(20);
    vSignDebit              character varying(1);
    vSignCredit             character varying(1);
    vTypeRate               character varying(3);
    vProductCOA             character varying(10);
    vSystemCOA              character varying(10);
    vTaxCOA                 character varying(10);
    vCashBankCOA            character varying(10);
    vDocTypeDoPos           bigint;
    vDocTypePos             bigint;
    vDocTypeNempilBarang    bigint;
    vRounding               integer;
    vParentOuId             bigint;
    vActivityCOA            character varying(10);
    vStatusPos              character varying(1);
    vActivityChargesId      bigint;
    vStatusDraft            character varying(1);

    vDocJournalDoPos        DOC_JOURNAL%ROWTYPE;
    vDocJournalAdjStock     DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    result                  RECORD;

    vLedgerCode             character varying(10);
    vRetail                 character varying(10);
    vPosDocTypeId bigint;
    vRoundingModeNonTax     character varying(5);
    vCount                  character varying;
    vFlagRole               character varying(1);
    vDebtNoteARDocScheme    character varying(10);
    vDocTypeDebtNoteAr      bigint;
    vDefaultRoleId          bigint;
    vFlowArDebtNoteId       bigint;
    vStateDraft             character varying(10);
    vFlgPkp                 character varying;
    vTotalTaxItem           integer;
    vTotalTaxItemNempil     integer;
    vTaxNempilBarang        bigint;
    vCountBelumDiBayar      numeric;
BEGIN

    vFlgPkp := 'N';
    vStatusPos := 'S';
    vFlagNo := 'N';
    vProductStatus := 'GOOD';
    vEmptyId := -99;
    vDocTypeAdjStock := 413;
    vFlagCash := 'C';
    vDebitType := 'D';
    vFlagYes := 'Y';
    vEmptyValue := ' ';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vProductCOA := 'PRODUCT';
    vSystemCOA := 'SYSTEM';
    vTaxCOA := 'TAX';
    vCashBankCOA := 'CASHBANK';
    vParentOuId := pOuId;
    vActivityCOA := 'ACTIVITY'; -- DanieL : tambahan sementara agar tidak error
    --vActivityChargesId := 2;
    vActivityChargesId := f_get_value_system_config_by_param_code(pTenantId, 'CHARGES');
    vStatusDraft := 'D';
    vJournalType := 'SALES.POS';
    vLedgerCode := 'SALES';
    vFlagRole := 'R';
    vDebtNoteARDocScheme := 'DF01';
    vDocTypeDebtNoteAr := 241;
    vStateDraft := 'DRAFT';
    vCountBelumDiBayar := 0;
    vTotalTaxItem := 0;
    vTotalTaxItemNempil := 0;

    vRetail := 'RETAIL';
    vTaxNempilBarang := 0;

    vPosDocTypeId := 401;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;


    SELECT f_get_value_system_config_by_param_code(pTenantId, 'taxNempilBarang') INTO vTaxNempilBarang;


    /* Nempil Barang, void pos , dan POS Shop */
    vDocTypeNempilBarang :=432;
    vDocTypePos          :=401;

    /*
     * jenis transaksi bayangan untuk Delivery Order POS untuk kepentingan jurnal inventory POS
     */
    vDocTypeDoPos := 431;
/*  NK, 22 Jan 2014
    SELECT f_get_parent_ou_bu(pTenantId, pOuId) INTO vParentOuId;
    SELECT f_get_journal_type(vDocTypeDoPos) INTO vJournalTypeDoPos;
    SELECT f_get_journal_type(vDocTypeAdjStock) INTO vJournalTypeAdjStock;
    */
    SELECT f_get_ou_bu_structure(pOuId) AS ou, f_get_document_journal(vDocTypeDoPos) as doc_do_pos, f_get_document_journal(vDocTypeAdjStock) as doc_adj_stock
        INTO result;

    vOuStructure := result.ou;
    vDocJournalDoPos := result.doc_do_pos;
    vDocJournalAdjStock := result.doc_adj_stock;

    /*
     * rounding nilai tax
     */
    vRounding := 0;

    -- Add by: Ping An 27Nov2015
    -- get flag_pkp
    SELECT C.flg_pkp INTO vFlgPkp
    FROM m_ou_structure A
    INNER JOIN t_ou B ON A.ou_bu_id = B.ou_id
    INNER JOIN t_ou_legal C ON B.ou_id = C.ou_id AND B.tenant_id = C.tenant_id
    WHERE A.ou_id = pOuId AND B.tenant_id = pTenantId;


    -- add by: Ping An , 26 Nov 2015 cek LUNAS , jika count = 0 berarti LUNAS
    -- lUNAS = guest atau corporate bayar lunas
    -- get Lunas
    SELECT count(B.under_payment_amount) INTO vCountBelumDiBayar
    FROM
    i_trx_pos A
    INNER JOIN i_trx_pos_ext E ON A.tenant_id = E.tenant_id AND A.process_no = E.process_no AND A.trx_pos_id = E.trx_pos_id
    INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
    INNER JOIN t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
    WHERE A.tenant_id = pTenantId
        AND A.process_no = pProcessNo
        AND A.ou_id = pOuId
        AND B.under_payment_amount > 0;


    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    DELETE FROM i_tt_pos_cash_balance WHERE session_id = pSessionId;

    DELETE FROM i_tt_pos_product_balance_stock WHERE session_id = pSessionId;

    DELETE FROM i_tt_nempil_barang_balance_stock WHERE session_id = pSessionId;

    DELETE FROM i_tt_nempil_barang_product WHERE session_id = pSessionId;

    /* UPDATE Saldo Nempil - Add by Ping An */
    /* Persiapan insert ke temp semua produk nempil dari transaksi nempil */
    INSERT INTO i_tt_nempil_barang_product
    (session_id, tenant_id, ou_id, product_code, product_name, qty)
    -- rev by:ping An 25nov2015, karena di trx nempil boleh punya item productcode yg sama di linenumber berbeda
    -- SELECT pSessionId, A.tenant_id, A.ou_id, B.product_code, B.product_name, SUM(B.qty)
    SELECT pSessionId, A.tenant_id, A.ou_id, B.product_code, vEmptyValue, SUM(B.qty)
    FROM i_trx_nempil_barang A, i_trx_nempil_barang_item B
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.process_no = B.process_no AND
        A.trx_nempil_barang_id = B.trx_nempil_barang_id AND
        A.tenant_id = B.tenant_id
    GROUP BY A.tenant_id, A.ou_id, B.product_code; --, B.product_name;

    -- update product name dengan productname yang max
    UPDATE i_tt_nempil_barang_product F SET product_name =
    (SELECT MAX(B.product_name)
        FROM i_trx_nempil_barang A, i_trx_nempil_barang_item B
        WHERE   A.tenant_id = pTenantId AND
                A.ou_id = pOuId AND
                A.process_no = pProcessNo AND
                A.process_no = B.process_no AND
                A.trx_nempil_barang_id = B.trx_nempil_barang_id AND
                A.tenant_id = B.tenant_id AND
                F.session_id = pSessionId AND
                F.tenant_id = B.tenant_id AND
                F.ou_id =A.ou_id AND
                F.product_code =B.product_code
                )
    WHERE
        F.product_name = vEmptyValue AND
        F.session_id = pSessionId;

    /* Persiapan insert ke temp semua produk nempil dari saldo nempil diERP */
    INSERT INTO i_tt_nempil_barang_product
    (session_id, tenant_id, ou_id, product_code, product_name, qty)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.product_code, A.product_name, SUM(A.qty)
    FROM in_nempil_barang_balance_stock A
    WHERE A.tenant_id = pTenantId AND
          A.ou_id = pOuId AND
          NOT EXISTS (SELECT 1 FROM i_tt_nempil_barang_product B
                         WHERE B.session_id = pSessionId AND
                               B.tenant_id = A.tenant_id AND
                               B.ou_id = A.ou_id AND
                               B.product_code = A.product_code )

    GROUP BY A.tenant_id, A.ou_id, A.product_code, A.product_name;

    /* 1.Tulis ke temp saldo nempil dari TRANSAKSI NEMPIL, bisa menambah atau megurangi saldo nempil
     */
    INSERT INTO i_tt_nempil_barang_balance_stock
    (session_id, tenant_id, ou_id, doc_type_id,
    doc_no, doc_date, partner_name, product_code, product_name, qty)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id,
        A.doc_no, A.doc_date, A.partner_name, B.product_code, D.product_name, SUM(B.qty)
    -- rev by: ping An, 26Nov 2015, karena productCodenya boleh sama di linenumber berbeda perlu join ke i_tt_nempil_barang_product
    -- FROM i_trx_nempil_barang A, i_trx_nempil_barang_item B,
    FROM i_trx_nempil_barang A, i_trx_nempil_barang_item B, i_tt_nempil_barang_product D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.process_no = B.process_no AND
        A.trx_nempil_barang_id = B.trx_nempil_barang_id AND
        A.tenant_id = B.tenant_id AND
        A.tenant_id = D.tenant_id AND
        A.ou_id = D.ou_id  AND
        B.product_code = D.product_code AND
        D.session_id = pSessionId

    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id,
        A.doc_no, A.doc_date, A.partner_name, B.product_code, D.product_name;

    /* 2.Tulis ke temp saldo nempil dari TRANSAKSI POS, mengurangi saldo nempil
     */
    INSERT INTO i_tt_nempil_barang_balance_stock
    (session_id, tenant_id, ou_id, doc_type_id,
    doc_no, doc_date, partner_name, product_code, product_name, qty)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id,
        A.doc_no, A.doc_date, C.partner_name, B.product_code, D.product_name, SUM(B.qty) * -1
    FROM i_trx_pos A, i_trx_pos_ext C, i_trx_pos_item_nempil_barang B, i_tt_nempil_barang_product D
    WHERE  A.tenant_id = pTenantId AND
           A.ou_id = pOuId AND
           A.process_no = pProcessNo AND
           A.status = vStatusPos AND
           A.process_no = B.process_no AND
           A.trx_pos_id = B.trx_pos_id AND
           A.tenant_id = B.tenant_id AND
           A.process_no = C.process_no AND
           A.trx_pos_id = C.trx_pos_id AND
           A.tenant_id = C.tenant_id AND
           A.tenant_id = D.tenant_id AND
           A.ou_id = D.ou_id  AND
           B.product_code = D.product_code AND
           D.session_id = pSessionId

    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id,
        A.doc_no, A.doc_date, C.partner_name, B.product_code, D.product_name;


    /*
     * update nempil barang balance stock  yang sudah ada
     */
    UPDATE in_nempil_barang_balance_stock SET qty = in_nempil_barang_balance_stock.qty + B.qty
    FROM (SELECT A.tenant_id, A.ou_id, A.product_code,
                SUM(A.qty) AS qty
        FROM i_tt_nempil_barang_balance_stock A
        WHERE A.session_id = pSessionId AND
              A.doc_type_id IN(vDocTypeNempilBarang, vDocTypePos)
        GROUP BY A.tenant_id, A.ou_id, A.product_code) B
    WHERE B.tenant_id = in_nempil_barang_balance_stock.tenant_id AND
        B.ou_id = in_nempil_barang_balance_stock.ou_id AND
        B.product_code = in_nempil_barang_balance_stock.product_code;


    /*
     * insert nempil barang balance stock yang data belum ada
     */
    INSERT INTO in_nempil_barang_balance_stock
    (tenant_id, ou_id, product_code, product_name,
    qty,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.product_code, A.product_name,
        sum(qty),
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_tt_nempil_barang_balance_stock A
    WHERE A.session_id = pSessionId AND
          A.doc_type_id IN(vDocTypeNempilBarang, vDocTypePos) AND
        NOT EXISTS (SELECT 1 FROM in_nempil_barang_balance_stock B
                    WHERE A.tenant_id = B.tenant_id AND
                        A.ou_id = B.ou_id AND
                        A.product_code = B.product_code)
    GROUP BY A.tenant_id, A.ou_id, A.product_code, A.product_name;


    /* insert log nempil barang balance stock berdasarkan data tampung di i_tt_nempil_barang_balance_stock
     */
    INSERT INTO in_log_nempil_barang_balance_stock
    (tenant_id, ou_id, doc_type_id, doc_no, doc_date,
     partner_name, product_code, product_name, qty, version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        A.partner_name, A.product_code, A.product_name, SUM(A.qty), 0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_tt_nempil_barang_balance_stock A
    WHERE A.session_id = pSessionId AND
          A.doc_type_id IN(vDocTypeNempilBarang, vDocTypePos)
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        A.partner_name, A.product_code, A.product_name;

    /* end add nempil barang by:Ping An */



    /*
     * mengolah data pos untuk :
     * 1.update in_product_balance_stock
     * 2.insert in_log_product_balance_stock
     * 3.update/insert cb_cashbank_balance
     * 4.insert cb_trx_cashbank_balance
     * 5.insert cb_balance_settlement
     *
     * mengolah data inventory adjusment stok untuk :
     * 1.update in_product_balance_stock
     * 2.insert in_log_product_balance_stock
     *
     * journal :
     * 1.journal DO POS Shop
     * 2.journal POS Shop
     */

    /*
     * buat data product pos yang akan digunakan untuk buat log product balance stock dan
     * update product balance stock
     * 1.product yang bukan product assembly ( product balance id <> -99 ) -- Didit : bukan service (product balance id <> -99)
     * 2.product assembly ( dari i_trx_pos_assembly_product )
     */
    INSERT INTO i_tt_pos_product_balance_stock
    (session_id, tenant_id, ou_id, doc_type_id, ref_id, partner_id,
    doc_no, doc_date, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, vEmptyId,
        A.doc_no, A.doc_date, B.product_id, pWarehouseId,
        B.product_balance_id, vProductStatus, B.base_uom_id, SUM(B.qty)
    FROM i_trx_pos A, i_trx_pos_item B
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos 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
    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;

    INSERT INTO i_tt_pos_product_balance_stock
    (session_id, tenant_id, ou_id, doc_type_id, ref_id, partner_id,
    doc_no, doc_date, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, vEmptyId,
        A.doc_no, A.doc_date, C.product_id, pWarehouseId,
        C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty)
    FROM i_trx_pos A, i_trx_pos_item B, i_trx_pos_assembly_product C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos 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
    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;

    /*
     * insert log product balance stock berdasarkan data tampung di i_tt_pos_product_balance_stock
     */
    INSERT INTO in_log_product_balance_stock
    (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 A.tenant_id, A.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, SUM(A.qty) * -1, 0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_tt_pos_product_balance_stock A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.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 ;

    /*
     * update product balance stock berdasarkan data tampung di i_tt_pos_product_balance_stock
     */
    WITH i_tt_pos_product_balance_stock_for_update_current AS (
        SELECT pSessionId AS session_id, A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status, SUM(A.qty) AS qty
        FROM i_tt_pos_product_balance_stock A
        WHERE A.tenant_id = pTenantId
        AND A.session_id = pSessionId
        GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status
    )
    UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty - A.qty, update_datetime = pDatetime , update_user_id = pUserId,
        version = in_product_balance_stock.version + 1
    FROM i_tt_pos_product_balance_stock_for_update_current A
    WHERE A.session_id = pSessionId AND
        A.tenant_id = in_product_balance_stock.tenant_id AND
        A.warehouse_id = in_product_balance_stock.warehouse_id AND
        A.product_id = in_product_balance_stock.product_id AND
        A.product_balance_id = in_product_balance_stock.product_balance_id AND
        A.product_status = in_product_balance_stock.product_status;

    /*
     * tampung data pos cash payment untuk update ke data cb_cashbank_balance
     * dan cb_trx_cashbank_balance
     */
    INSERT INTO i_tt_pos_cash_balance
    (session_id, tenant_id, ou_id, doc_type_id, doc_no, payment_id,
    partner_id, curr_code, amount,
    cashbank_id, cash_bank_date, ref_doc_type_id, ref_id)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
        A.partner_id, B.curr_payment_code, SUM(B.payment_amount),
        C.cashbank_id, A.doc_date, A.doc_type_id, A.trx_pos_id
    FROM i_trx_pos A, i_trx_pos_cash_payment B, i_cash_bank_data_outlet C, m_cashbank D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.process_no = B.process_no AND
        A.trx_pos_id = B.trx_pos_id AND
        A.tenant_id = B.tenant_id AND
        C.outlet_id = pOutletId AND
        C.cashbank_id = D.cashbank_id AND
        B.curr_payment_code = D.curr_code AND
        D.flg_cash_bank = vFlagCash
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
        A.partner_id, B.curr_payment_code, C.cashbank_id, A.doc_date;

    /*
     * tampung data pos total refund untuk update ke data cb_cashbank_balance
     * dan cb_trx_cashbank_balance
     */
    INSERT INTO i_tt_pos_cash_balance
    (session_id, tenant_id, ou_id, doc_type_id, doc_no, payment_id,
    partner_id, curr_code, amount,
    cashbank_id, cash_bank_date, ref_doc_type_id, ref_id)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
        A.partner_id, A.curr_code, SUM(A.total_refund) * -1,
        B.cashbank_id, A.doc_date, A.doc_type_id, A.trx_pos_id
    FROM i_trx_pos A, i_cash_bank_data_outlet B, m_cashbank C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.total_refund >= 0 AND -- Didit : penanda untuk corporate yg tidak hutang
        B.outlet_id = pOutletId AND
        B.cashbank_id = C.cashbank_id AND
        A.curr_code = C.curr_code AND
        C.flg_cash_bank = vFlagCash
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
        A.partner_id, A.curr_code, B.cashbank_id, A.doc_date;

    /*
     * insert ke data cb_trx_cashbank_balance
     */
    INSERT INTO cb_trx_cashbank_balance
    (tenant_id, ou_id, doc_type_id, payment_id,
    payment_doc_no, payment_doc_date, payment_remark,
    partner_id, partner_bank_id, curr_code, amount,
    due_date, flg_payment, ref_doc_type_id, ref_id,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.payment_id,
        A.doc_no, A.cash_bank_date, vEmptyValue,
        A.partner_id, vEmptyId, A.curr_code, SUM(A.amount),
        A.cash_bank_date, vFlagYes, A.ref_doc_type_id, A.ref_id,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_tt_pos_cash_balance A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.payment_id, A.doc_no, A.partner_id, A.curr_code,
        A.cashbank_id, A.cash_bank_date, A.ref_doc_type_id, A.ref_id;

    /*
     * insert cb_cashbank_balance yang data belum ada
     */
    INSERT INTO cb_cashbank_balance
    (tenant_id, ou_id, cashbank_id, cash_bank_date,
    rec_type, curr_code, amount,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, D.ou_id, A.cashbank_id, A.cash_bank_date,
        vDebitType, A.curr_code, 0,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_tt_pos_cash_balance A, m_cashbank_ou D
    WHERE A.session_id = pSessionId AND
        D.cashbank_id = A.cashbank_id AND
        NOT EXISTS (SELECT 1 FROM cb_cashbank_balance B
                    WHERE A.tenant_id = B.tenant_id AND
                        D.ou_id = B.ou_id AND
                        A.cashbank_id = B.cashbank_id AND
                        A.cash_bank_date = B.cash_bank_date AND
                        B.rec_type = vDebitType)
    GROUP BY A.tenant_id, A.cashbank_id, A.cash_bank_date, A.curr_code, D.ou_id;

    /*
     * update cb_cashbank_balance yang sudah ada
     */
    UPDATE cb_cashbank_balance SET amount = cb_cashbank_balance.amount + B.amount
    FROM (SELECT A.tenant_id, D.ou_id, A.cashbank_id, A.cash_bank_date, A.curr_code,
                SUM(A.amount) AS amount
        FROM i_tt_pos_cash_balance A, m_cashbank_ou D
        WHERE A.session_id = pSessionId AND
            D.cashbank_id = A.cashbank_id
        GROUP BY A.tenant_id, A.cashbank_id, A.cash_bank_date, A.curr_code, D.ou_id) B
    WHERE B.tenant_id = cb_cashbank_balance.tenant_id AND
        B.ou_id = cb_cashbank_balance.ou_id AND
        B.cashbank_id = cb_cashbank_balance.cashbank_id AND
        B.cash_bank_date = cb_cashbank_balance.cash_bank_date AND
        B.curr_code = cb_cashbank_balance.curr_code AND
        cb_cashbank_balance.rec_type = vDebitType;

    /*
     * buat saldo payment non cash, menggunakan EDC
     */
    INSERT INTO cb_balance_settlement
    (tenant_id, ou_id, doc_type_id, doc_no, doc_date,
    device_merchant_id, card_type, bank_card_code, card_no,
    curr_payment_code, amount_payment, flg_settle, cashbank_id, cash_bank_date,
    ref_doc_type_id, ref_id,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no,
        B.curr_payment_code, SUM(B.payment_amount), vFlagNo, vEmptyId, vEmptyValue,
        vEmptyId, vEmptyId,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_trx_pos A, i_trx_pos_non_cash_payment B
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.trx_pos_id = B.trx_pos_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id
    GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
        B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no,
        B.curr_payment_code;

    /*
     * buat data product adjusment stock yang akan digunakan untuk buat log product balance stock dan
     * update product balance stock
     */
    INSERT INTO i_tt_pos_product_balance_stock
    (session_id, tenant_id, ou_id, doc_type_id, ref_id, partner_id,
    doc_no, doc_date, product_id, warehouse_id,
    product_balance_id, product_status, base_uom_id, qty)
    SELECT pSessionId, A.tenant_id, A.ou_id, B.doc_type_id, B.trx_inventory_id, vEmptyId,
        B.doc_no, B.doc_date, C.product_id, B.warehouse_from_id,
        C.product_balance_id, C.product_status, C.base_uom_id, SUM(C.qty_realization) -- DanieL : perubahan qty menjadi qty_realization
    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 = pProcessNo 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_type_id = vDocTypeAdjStock 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 log product balance stock berdasarkan data tampung di i_tt_pos_product_balance_stock
     */
    INSERT INTO in_log_product_balance_stock
    (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 A.tenant_id, A.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, SUM(A.qty), 0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_tt_pos_product_balance_stock A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id = vDocTypeAdjStock AND
        A.product_balance_id <> vEmptyId
    GROUP BY A.tenant_id, A.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;

    /*
     * update product balance stock berdasarkan data tampung di i_tt_pos_product_balance_stock
     */
    WITH i_tt_pos_product_balance_stock_for_update_current_adj_stock AS (
        SELECT pSessionId AS session_id, A.doc_type_id, A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status, SUM(A.qty) AS qty
        FROM i_tt_pos_product_balance_stock A
        WHERE A.tenant_id = pTenantId
        AND A.session_id = pSessionId
        AND A.doc_type_id = vDocTypeAdjStock
        AND A.product_balance_id <> vEmptyId
        GROUP BY A.doc_type_id, A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status
    )
    UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty - A.qty, update_datetime = pDatetime , update_user_id = pUserId,
        version = in_product_balance_stock.version + 1
    FROM i_tt_pos_product_balance_stock_for_update_current_adj_stock A
    WHERE A.session_id = pSessionId AND
        A.doc_type_id = vDocTypeAdjStock AND
        A.product_balance_id <> vEmptyId AND
        A.tenant_id = in_product_balance_stock.tenant_id AND
        A.warehouse_id = in_product_balance_stock.warehouse_id AND
        A.product_id = in_product_balance_stock.product_id AND
        A.product_balance_id = in_product_balance_stock.product_balance_id AND
        A.product_status = in_product_balance_stock.product_status;

    /*
     * journal DO POS di group by product
     * Credit Inventory
     * Debit COGS
     */

    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournalDoPos).journal_type, (vDocJournalDoPos).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', pDatetime, pUserId)
    FROM i_trx_pos A
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos;

    INSERT INTO gl_journal_trx
    (tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
    ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
    ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, (vDocJournalDoPos).journal_type, vDocTypeDoPos, A.trx_pos_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, pWarehouseId, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_trx_pos A
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        EXISTS( -- added by Didit, 25 Juni 2018. Transaksi POS yang hanya produk nempil saja / service saja tidak perlu dibuatkan jurnal stock
            SELECT 1 FROM i_trx_pos_item B
            WHERE A.trx_pos_id = B.trx_pos_id AND
                A.process_no = B.process_no AND
                A.tenant_id = B.tenant_id
        );

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, vEmptyId,
        A.partner_id, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vProductCOA, vEmptyId,
        f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), SUM(B.qty), B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', vEmptyValue
    FROM i_trx_pos A, i_trx_pos_item B, gl_journal_trx C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.trx_pos_id = B.trx_pos_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id AND
        A.trx_pos_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        C.doc_type_id = vDocTypeDoPos AND
        C.journal_type = (vDocJournalDoPos).journal_type AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no
    GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
        A.partner_id, B.product_id, B.base_uom_id, A.doc_date;

    INSERT INTO gl_journal_trx_item
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId;
/*
 * NK, 1 Feb 2014
 * journal COGS tidak perlu breakdown per product, karena nilainya akan merupakan summary dari PRODUCT_STOCK
 * (sebelumnya breakdown per product)
 */
    INSERT INTO gl_journal_trx_mapping
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark, -- DanieL : penambahan journal_desc
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        vEmptyId, vEmptyId,
        vEmptyId, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, 0, vEmptyId,
        0, A.journal_date, A.type_rate,
        1, 1, 'COGS', vEmptyValue,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate, A.curr_code;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    /*
     * journal Adjusment Stock
     * Credit Inventory
     * Debit Biaya
     */
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournalAdjStock).journal_type, (vDocJournalAdjStock).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', pDatetime, pUserId)
    FROM i_trx_inventory A
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.doc_type_id = vDocTypeAdjStock;

    INSERT INTO gl_journal_trx
    (tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
    ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
    ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, (vDocJournalAdjStock).journal_type, A.doc_type_id, A.trx_inventory_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, vEmptyId, vEmptyId, pWarehouseId, vEmptyValue, vEmptyValue, -- DanieL : tidak ada field A.ext_doc_no, A.ext_doc_dat
        A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_trx_inventory A
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.doc_type_id = vDocTypeAdjStock;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, B.trx_inventory_item_id,
        vEmptyId, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vProductCOA, vEmptyId,
        f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark
    FROM i_trx_inventory A, i_trx_inventory_item B, gl_journal_trx C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.doc_type_id = vDocTypeAdjStock AND
        A.trx_inventory_id = B.trx_inventory_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id AND
        A.trx_inventory_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        C.journal_type =  (vDocJournalAdjStock).journal_type AND
        B.qty_realization < 0 AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, B.trx_inventory_item_id,
        vEmptyId, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vProductCOA, vEmptyId,
        f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark
    FROM i_trx_inventory A, i_trx_inventory_item B, gl_journal_trx C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.doc_type_id = vDocTypeAdjStock AND
        A.trx_inventory_id = B.trx_inventory_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id AND
        A.trx_inventory_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        C.journal_type =  (vDocJournalAdjStock).journal_type AND
        B.qty_realization > 0 AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no;

/*
 * NK, 1 Feb 2014
 * jurnal activity gl tidak di break down per product
 */
    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, D.journal_trx_id, 1,
        vEmptyId, vEmptyId,
        vEmptyId, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vActivityCOA, A.activity_gl_id,
        C.coa_id, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), 0, vEmptyId,
        0 , A.doc_date, vTypeRate,
        1, 1, 'ADJUSMENT_STOCK', vEmptyValue
    FROM i_trx_inventory A, m_activity_gl C, gl_journal_trx D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.doc_type_id = vDocTypeAdjStock AND
        A.activity_gl_id = C.activity_gl_id AND
        A.trx_inventory_id = D.doc_id AND
        A.tenant_id = D.tenant_id AND
        A.doc_type_id = D.doc_type_id AND
        D.journal_type =  (vDocJournalAdjStock).journal_type AND
        D.ou_bu_id = (vOuStructure).ou_bu_id AND
        D.ou_branch_id = (vOuStructure).ou_branch_id AND
        D.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = D.sub_ou_id AND
        A.doc_no = D.doc_no;

/*
    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, D.journal_trx_id, 1,
        A.doc_type_id, B.trx_inventory_item_id,
        vEmptyId, B.product_id, vEmptyId, A.ou_id,
        vEmptyId, vSignDebit, vActivityCOA, A.activity_gl_id,
        C.coa_id, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'ADJUSMENT_STOCK', B.remark
    FROM i_trx_inventory A, i_trx_inventory_item B, m_activity_gl C, gl_journal_trx D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.doc_type_id = vDocTypeAdjStock AND
        A.trx_inventory_id = B.trx_inventory_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id AND
        A.activity_gl_id = C.activity_gl_id AND
        A.trx_inventory_id = D.doc_id AND
        A.tenant_id = D.tenant_id AND
        A.doc_type_id = D.doc_type_id AND
        D.journal_type =  (vDocJournalAdjStock).journal_type AND
        B.qty_realization < 0 AND
        D.ou_bu_id = (vOuStructure).ou_bu_id AND
        D.ou_branch_id = (vOuStructure).ou_branch_id AND
        D.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = D.sub_ou_id AND
        A.doc_no = D.doc_no;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, D.journal_trx_id, 1,
        A.doc_type_id, B.trx_inventory_item_id,
        vEmptyId, B.product_id, vEmptyId, A.ou_id,
        vEmptyId, vSignCredit, vActivityCOA, A.activity_gl_id,
        C.coa_id, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'ADJUSMENT_STOCK', B.remark
    FROM i_trx_inventory A, i_trx_inventory_item B, m_activity_gl C, gl_journal_trx D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.doc_type_id = vDocTypeAdjStock AND
        A.trx_inventory_id = B.trx_inventory_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id AND
        A.activity_gl_id = C.activity_gl_id AND
        A.trx_inventory_id = D.doc_id AND
        A.tenant_id = D.tenant_id AND
        A.doc_type_id = D.doc_type_id AND
        D.journal_type =  (vDocJournalAdjStock).journal_type AND
        B.qty_realization > 0 AND
        D.ou_bu_id = (vOuStructure).ou_bu_id AND
        D.ou_branch_id = (vOuStructure).ou_branch_id AND
        D.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = D.sub_ou_id AND
        A.doc_no = D.doc_no;
*/
    INSERT INTO gl_journal_trx_item
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc = 'PRODUCT_STOCK';

    INSERT INTO gl_journal_trx_mapping
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc = 'ADJUSMENT_STOCK';

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    /*
     * journal POS
     * Credit Sales           nett price * qty, without tax
     * Credit BankCharges     add amount
     * Credit Tax Amount      tax amount ( sudah nett price )
     * Credit Rounding         rouding
     * Credit Refund Cash       refund amount
     * Debit Cash             cash payment
     * Debit AccSettlement    non cash payment
     */
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, vJournalType, vLedgerCode, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
    FROM i_trx_pos A
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos;

    INSERT INTO gl_journal_trx
    (tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
    ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
    ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, vJournalType, A.doc_type_id, A.trx_pos_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, pWarehouseId, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM i_trx_pos A
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, vEmptyId,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), B.curr_code, SUM(B.qty), B.base_uom_id,
        --f_get_amount_before_tax(SUM(B.nett_sell_price * B.qty),B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),vRoundingModeNonTax) , A.doc_date, vTypeRate,
        CASE WHEN vFlgPkp = vFlagYes THEN
        f_get_amount_before_tax(SUM(B.nett_sell_price * B.qty),B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),vRoundingModeNonTax) ELSE
        f_calculate_dpp_amount_from_gross_price(SUM(B.nett_sell_price * B.qty),1,0,B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),'RHU') + f_calculate_tax_amount_from_gross_price(SUM(B.nett_sell_price * B.qty),1,0,B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),'RHU')
        --f_get_amount_before_tax(SUM(B.nett_sell_price * B.qty),B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),vRoundingModeNonTax) + SUM(B.tax_amount)
        END
        , A.doc_date, vTypeRate,
        1, 1, 'SALES_POS', vEmptyValue
    FROM i_trx_pos A, i_trx_pos_item B, gl_journal_trx C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.trx_pos_id = B.trx_pos_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id AND
        A.trx_pos_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        C.journal_type = vJournalType AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no
    GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
        A.partner_id, B.curr_code, B.base_uom_id, B.flg_tax_amount, B.tax_percentage, A.doc_date;


    IF vFlgPkp = vFlagYes THEN
    -- hitung total tax item
    SELECT sum(B.tax_amount) INTO vTotalTaxItem
    FROM i_trx_pos A, i_trx_pos_item B
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.trx_pos_id = B.trx_pos_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id;

    IF vTotalTaxItem > 0 THEN

    SELECT sum(B.tax_amount)-vTotalTaxItem INTO vTotalTaxItemNempil
    FROM i_trx_pos A, i_trx_pos_tax B
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.trx_pos_id = B.trx_pos_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id;

    END IF;
    END IF;


-- jurnal penjualan dari produk nempil, 6 Nov 2015
    WITH data_tax_pos AS(
        SELECT A.trx_pos_id, A.tenant_id,
            A.doc_type_id, sum(B.tax_amount) AS tax_amount
        FROM i_trx_pos A, i_trx_pos_item B
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo AND
            A.status = vStatusPos AND
            A.trx_pos_id = B.trx_pos_id AND
            A.process_no = B.process_no AND
            A.tenant_id = B.tenant_id
        GROUP BY A.trx_pos_id, A.tenant_id,
            A.doc_type_id
    ),
    data_tax_total AS(
        SELECT A.trx_pos_id, A.tenant_id,
            A.doc_type_id, sum(B.tax_amount) AS tax_amount
        FROM i_trx_pos A, i_trx_pos_tax B
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo AND
            A.status = vStatusPos AND
            A.trx_pos_id = B.trx_pos_id AND
            A.process_no = B.process_no AND
            A.tenant_id = B.tenant_id
        GROUP BY A.trx_pos_id, A.tenant_id,
            A.doc_type_id
    ),
    data_tax_nempil AS(
        SELECT A.trx_pos_id, A.tenant_id,
            A.doc_type_id, A.tax_amount - COALESCE(B.tax_amount,0) AS tax_amount
        FROM data_tax_total A
        LEFT JOIN data_tax_pos B ON A.trx_pos_id = B.trx_pos_id AND
                                       A.tenant_id = B.tenant_id AND
                                       A.doc_type_id = B.doc_type_id
    )
    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, vEmptyId,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), B.curr_code, SUM(B.qty), vEmptyId,
        CASE WHEN vFlgPkp = vFlagYes THEN
        SUM(B.sell_price) - COALESCE(D.tax_amount, 0) ELSE -- sell price = gross sell price * qty
        SUM(B.sell_price)
        END,
        A.doc_date, vTypeRate,
        1, 1, 'SALES_POS', vEmptyValue
    FROM i_trx_pos A
    INNER JOIN i_trx_pos_item_nempil_barang B ON A.trx_pos_id = B.trx_pos_id AND
                                                 A.process_no = B.process_no AND
                                                 A.tenant_id = B.tenant_id
    INNER JOIN gl_journal_trx C ON A.trx_pos_id = C.doc_id AND
                                   A.tenant_id = C.tenant_id AND
                                   A.doc_type_id = C.doc_type_id AND
                                   A.doc_no = C.doc_no
    LEFT JOIN data_tax_nempil D ON A.trx_pos_id = D.trx_pos_id AND
                                   A.tenant_id = D.tenant_id AND
                                   A.doc_type_id = D.doc_type_id
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        C.journal_type = vJournalType AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
--      A.ou_id = C.sub_ou_id AND
    GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
        A.partner_id, B.curr_code, A.doc_date, D.tax_amount;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, B.trx_pos_add_cost_id,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaKartuKredit'), B.curr_code, 0, vEmptyId,
        B.add_amount, A.doc_date, vTypeRate,
        1, 1, 'CHARGES_POS', B.remark
    FROM i_trx_pos A, i_trx_pos_add_cost B, gl_journal_trx C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.trx_pos_id = B.trx_pos_id AND
        A.process_no = B.process_no AND
        A.tenant_id = B.tenant_id AND
        A.trx_pos_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        C.journal_type = vJournalType AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no AND
        B.activity_gl_id = vActivityChargesId;

        -- add if By: Ping An , 27 Nov 2015
    -- Jurnal VAT Hanya untuk yang PKP saja , kalo non PKP gak perlu dijurnal PPN karena non PKP tidak memungut dan melaporkan PPN
    IF vFlgPkp = vFlagYes THEN

        INSERT INTO tt_journal_trx_item
        (session_id, tenant_id, journal_trx_id, line_no,
        ref_doc_type_id, ref_id,
        partner_id, product_id, cashbank_id, ou_rc_id,
        segmen_id, sign_journal, flg_source_coa, activity_gl_id,
        coa_id, curr_code, qty, uom_id,
        amount, journal_date, type_rate,
        numerator_rate, denominator_rate, journal_desc, remark)
        SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
            A.doc_type_id, vEmptyId,
            A.partner_id, vEmptyId, vEmptyId, vEmptyId,
            vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
            D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
            B.tax_amount , A.doc_date, vTypeRate,
            1, 1, 'VAT_OUT_POS', vEmptyValue
        FROM i_trx_pos A, i_trx_pos_tax B, gl_journal_trx C, m_tax D
        WHERE A.tenant_id = pTenantId AND
            A.ou_id = pOuId AND
            A.process_no = pProcessNo AND
            A.status = vStatusPos AND
            A.trx_pos_id = B.trx_pos_id AND
            A.process_no = B.process_no AND
            A.tenant_id = B.tenant_id AND
            B.tax_id = D.tax_id AND
            A.trx_pos_id = C.doc_id AND
            A.tenant_id = C.tenant_id AND
            A.doc_type_id = C.doc_type_id AND
            C.journal_type = vJournalType AND
            C.ou_bu_id = (vOuStructure).ou_bu_id AND
            C.ou_branch_id = (vOuStructure).ou_branch_id AND
            C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
    --      A.ou_id = C.sub_ou_id AND
            A.doc_no = C.doc_no;


    END IF;


    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, vEmptyId,
        A.partner_id, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaPembulatanNilai'), A.curr_code, 0, vEmptyId,
        A.rounding_amount, A.doc_date, vTypeRate,
        1, 1, 'ROUNDING_POS', C.remark
    FROM i_trx_pos A, gl_journal_trx C
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.trx_pos_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        C.journal_type = vJournalType AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, vEmptyId,
        A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
        vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
        E.coa_id, A.curr_code, 0, vEmptyId,
        A.total_refund, A.doc_date, vTypeRate,
        1, 1, 'REFUND_POS', A.remark
    FROM i_trx_pos A, gl_journal_trx C, i_cash_bank_data_outlet D, m_cashbank E
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.trx_pos_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        D.outlet_id = pOutletId AND
        D.cashbank_id = E.cashbank_id AND
        A.curr_code = E.curr_code AND
        E.flg_cash_bank = vFlagCash AND
        C.journal_type = vJournalType AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no AND
        A.total_refund > 0;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, B.trx_pos_cash_payment_id,
        A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
        vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
        E.coa_id, B.curr_payment_code, 0, vEmptyId,
        B.payment_amount, A.doc_date, vTypeRate,
        1, 1, 'CASH_PAYMENT_POS', B.remark
    FROM i_trx_pos A, i_trx_pos_cash_payment B, gl_journal_trx C, i_cash_bank_data_outlet D, m_cashbank E
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.process_no = B.process_no AND
        A.trx_pos_id = B.trx_pos_id AND
        A.tenant_id = B.tenant_id AND
        D.outlet_id = pOutletId AND
        D.cashbank_id = E.cashbank_id AND
        B.curr_payment_code = E.curr_code AND
        E.flg_cash_bank = vFlagCash AND
        A.trx_pos_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        C.journal_type = vJournalType AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no AND
        B.payment_amount > 0; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
        A.doc_type_id, B.trx_pos_non_cash_payment_id,
        A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
        vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
        E.settle_coa_id, B.curr_payment_code, 0, vEmptyId,
        B.payment_amount, A.doc_date, vTypeRate,
        1, 1, 'NON_CASH_PAYMENT_POS', B.remark
    FROM i_trx_pos A, i_trx_pos_non_cash_payment B, gl_journal_trx C, i_device_merchant_data_outlet D, m_device_merchant E
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.process_no = B.process_no AND
        A.trx_pos_id = B.trx_pos_id AND
        A.tenant_id = B.tenant_id AND
        D.outlet_id = pOutletId AND
        B.device_merchant_id = D.device_merchant_id AND
        D.device_merchant_id = E.device_merchant_id AND
        A.trx_pos_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        C.journal_type = vJournalType AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no AND
        B.payment_amount > 0; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal



    -- hanya dijalankan jika ada kurang bayar(BELUM LUNAS PEMBAYARANNYA ), kalau sudah lunas tidak perlu dijurnal DP
    IF vCountBelumDiBayar > 0 THEN
        -- sisa pembayaran dipindahkan ke AR
            INSERT INTO tt_journal_trx_item
            (session_id, tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, journal_desc, remark)
            SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
                A.doc_type_id, A.ref_id,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
                f_get_ar_coa_partner(A.tenant_id, D.partner_id), A.curr_code, 0, vEmptyId,
                -- rev by Ping An 17 Nov 2015, ambil nilai utuhnya karena nnti di submit invoice debt note ar yg dari POS dijaga tidak akan dijurnal
                -- B.under_payment_amount, A.doc_date, vTypeRate,
                A.total_amount, A.doc_date, vTypeRate,
                1, 1, 'AR_POS', 'NILAI DEBIT NOTE AR'
            FROM i_trx_pos A, gl_journal_trx C, i_trx_pos_ext D, i_trx_pos_termin_payment E
            WHERE A.tenant_id = pTenantId AND
                A.ou_id = pOuId AND
                A.process_no = pProcessNo AND
                A.status = vStatusPos AND
                A.trx_pos_id = D.trx_pos_id AND
                -- rev by: Ping An , 28 Jan 2016 joinnya kurang process_no dan tenant_id
                A.process_no = D.process_no AND
                A.tenant_id = D.tenant_id AND
                A.tenant_id = E.tenant_id AND
                A.process_no = E.process_no AND
                A.trx_pos_id = E.trx_pos_id AND
                E.under_payment_amount > 0 AND

                A.trx_pos_id = C.doc_id AND
                A.tenant_id = C.tenant_id AND
                A.doc_type_id = C.doc_type_id AND
                C.journal_type = vJournalType AND
                C.ou_bu_id = (vOuStructure).ou_bu_id AND
                C.ou_branch_id = (vOuStructure).ou_branch_id AND
                C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
        --      A.ou_id = C.sub_ou_id AND
                A.doc_no = C.doc_no;


     END IF;



-- TAMBAHKAN JURNAL DP add by Ping An 18 Nov 2015
-- karena nantinya di function submit invoice AR, debt note ar yang tercreate dari POS tidak akan di jurnal, jadi perlu dijurnal disini DP ARnya
-- jika ada bayar termin dari customer corporate
        --      K   m_activity_gl                   SYSTEM  DP_AR_POS
INSERT INTO tt_journal_trx_item
            (   session_id, tenant_id, journal_trx_id, line_no,
                ref_doc_type_id, ref_id,
                partner_id, product_id, cashbank_id, ou_rc_id,
                segmen_id, sign_journal, flg_source_coa, activity_gl_id,
                coa_id, curr_code, qty, uom_id,
                amount, journal_date, type_rate,
                numerator_rate, denominator_rate, journal_desc, remark)
        SELECT  pSessionId, A.tenant_id, C.journal_trx_id, 1,
                A.trx_pos_id, B.trx_pos_termin_payment_id,
                A.partner_id, vEmptyId, vEmptyId, vEmptyId,
                vEmptyId, vSignCredit, vSystemCOA, D.activity_gl_id,
                D.coa_id, B.curr_payment_code, 0, vEmptyId,
                B.payment_amount, A.doc_date, vTypeRate,
                1, 1, 'DP_AR_POS', 'NILAI DP AR'
        FROM i_trx_pos A, i_trx_pos_termin_payment B, gl_journal_trx C, m_activity_gl D
    WHERE A.tenant_id = pTenantId AND
        A.ou_id = pOuId AND
        A.process_no = pProcessNo AND
        A.status = vStatusPos AND
        A.process_no = B.process_no AND
        A.trx_pos_id = B.trx_pos_id AND
        A.tenant_id = B.tenant_id AND
        A.trx_pos_id = C.doc_id AND
        A.tenant_id = C.tenant_id AND
        A.doc_type_id = C.doc_type_id AND
        C.journal_type = vJournalType AND
        C.ou_bu_id = (vOuStructure).ou_bu_id AND
        C.ou_branch_id = (vOuStructure).ou_branch_id AND
        C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
--      A.ou_id = C.sub_ou_id AND
        A.doc_no = C.doc_no AND
        B.under_payment_amount > 0 AND -- ditambah filter: jika LUNAS tidak perlu di jurnal DP
        B.payment_amount > 0 AND -- ditambah filter : jika tidak ada pembayaran juga tidak perlu di jurnal DP
        D.activity_gl_id = f_get_activity_gl_id_for_downpayment(A.tenant_id);

-- END PENAMBAHKAN JURNAL DP add by Ping An 18 Nov 2015

    INSERT INTO gl_journal_trx_item
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc IN ('SALES_POS','CHARGES_POS','VAT_OUT_POS', 'ROUNDING_POS', 'REFUND_POS', 'DP_AR_POS');

    INSERT INTO gl_journal_trx_mapping
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
         -- rev by Ping An 18nov2015 A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS');
         A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS','AR_POS');

    -- buat nota debit ar untuk transaksi pos yg masih hutang
    -- added by didit, 25 September 2015
    PERFORM f_migrate_data_trx_pos_to_debt_note_ar(pSessionId, A.tenant_id, A.process_no, A.ou_id, pUserId, pDatetime, vFlgPkp, CASE WHEN COALESCE(B.under_payment_amount, -1) < 0 THEN 0 ELSE 1 END, A.trx_pos_id)
    FROM i_trx_pos A
    LEFT JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
    WHERE A.tenant_id = pTenantId
        AND A.process_no = pProcessNo
        AND A.ou_id = pOuId;

    /**
       Bagian ini untuk melengkapi data awe
       Harus dipastikan sudah:
         1. di setting system config ROLE.IARD.ID -> diisi dengan Role ID Invoice AR Debt
         2. sudah set approval flow untuk INVOICE AR DEBT DF01, dan hanya 1
    **/
    -- Mendapatkan default role yang dipakai dari sysconfig
    /*
      * Mod By Didit, 1 Des 2016
     * tidak perlu lagi insert ke tabel awe_*
     * karena dokumennya sekarang sudah langsung approved semua
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'ROLE.IARD.ID') INTO vDefaultRoleId;

    -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
    SELECT awe_flow_id INTO vFlowArDebtNoteId
    FROM awe_flow WHERE scheme = vDebtNoteARDocScheme AND flg_validate = vFlagYes LIMIT 1;

    -- Generate unique Request ID
    --SELECT f_make_uid() INTO vReqId;

    -- Generate data awe_currdoc_status
    INSERT INTO awe_currdoc_status(
                req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
                remark, current_user_id, current_role_id, flg_user_role, label,
                data, flow_id, create_datetime, create_user_id, create_role_id,
                update_datetime, update_user_id, update_role_id, version)
    SELECT f_make_uid(), pTenantId, vDebtNoteARDocScheme, B.invoice_ar_id, B.doc_no, B.doc_date, vStateDraft,
           ' ', vEmptyId, vDefaultRoleId, vFlagRole, 'AR DEBT NOTE'||B.doc_no,
           '{}', vFlowArDebtNoteId, pDateTime, pUserId, vDefaultRoleId, pDateTime,pUserId, vDefaultRoleId, 0
    FROM i_trx_pos A
    INNER JOIN fi_invoice_ar B ON A.doc_no = B.doc_no AND A.doc_date = B.doc_date AND A.tenant_id = B.tenant_id AND B.doc_type_id = vDocTypeDebtNoteAr
    WHERE B.status_doc = vStatusDraft
        AND A.tenant_id = pTenantId
        AND A.process_no = pProcessNo
        AND A.ou_id = pOuId;

    -- Generate data awe_worklist
    INSERT INTO awe_worklist(
            req_id, user_id, role_id, no_item, tenant_id, scheme, doc_id,
            doc_no, doc_date, status, remark, flg_user_role, flg_to_do, create_datetime,
            create_user_id, update_datetime, update_user_id, version)
    SELECT C.req_id, -99, vDefaultRoleId, 1, pTenantId, vDebtNoteARDocScheme, B.invoice_ar_id,
        B.doc_no, B.doc_date, vStateDraft, ' ', vFlagRole, vFlagYes, pDateTime,
        pUserId, pDateTime, pUserId, 0
    FROM i_trx_pos A
    INNER JOIN fi_invoice_ar B ON A.doc_no = B.doc_no AND A.doc_date = B.doc_date AND A.tenant_id = B.tenant_id AND B.doc_type_id = vDocTypeDebtNoteAr
    INNER JOIN awe_currdoc_status C ON C.doc_id = B.invoice_ar_id AND C.doc_no = B.doc_no AND C.doc_date = B.doc_date
    WHERE B.status_doc = vStatusDraft
        AND A.tenant_id = pTenantId
        AND A.process_no = pProcessNo
        AND A.ou_id = pOuId;

    -- Generate data awe_historydoc
    INSERT INTO awe_historydoc(
            tenant_id, req_id, doc_id, scheme, user_id, role_id,
            activity, previous_state, next_state, remark, next_user_id, next_role_id,
            flg_user_role, activity_datetime, version)
    SELECT pTenantId, C.req_id, B.invoice_ar_id, vDebtNoteARDocScheme, vEmptyId, vDefaultRoleId,
        'CREATE', '', 'DRAFT', '', vEmptyId, vEmptyId,
        'U', pDateTime, 0
    FROM i_trx_pos A
    INNER JOIN fi_invoice_ar B ON A.doc_no = B.doc_no AND A.doc_date = B.doc_date AND A.tenant_id = B.tenant_id AND B.doc_type_id = vDocTypeDebtNoteAr
    INNER JOIN awe_currdoc_status C ON C.doc_id = B.invoice_ar_id AND C.doc_no = B.doc_no AND C.doc_date = B.doc_date
    WHERE B.status_doc = vStatusDraft
        AND A.tenant_id = pTenantId
        AND A.process_no = pProcessNo
        AND A.ou_id = pOuId;
    */

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    DELETE FROM i_tt_pos_cash_balance WHERE session_id = pSessionId;

    DELETE FROM i_tt_pos_product_balance_stock WHERE session_id = pSessionId;

    DELETE FROM i_tt_nempil_barang_balance_stock WHERE session_id = pSessionId;
    DELETE FROM i_tt_nempil_barang_product WHERE session_id = pSessionId;

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