CREATE OR REPLACE FUNCTION f_migrate_data_trx_pos_to_debt_note_ar(
    character varying,
    bigint,
    character varying,
    bigint,
    bigint,
    character varying,
    character varying,
    numeric,
    bigint)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          alias for $1;
    pTenantId           alias for $2;
    pProcessNo          alias for $3;
    pOuId               alias for $4;
    pUserId             alias for $5;
    pDatetime           alias for $6;
    pFlgPkp             alias for $7;
    pCountBelumDiBayar  alias for $8;
    pTrxPosId           alias for $9;

    vStatusDoc          character varying;
    vDocTypeDebtNoteAr  bigint;
    vEmptyValue         bigint;
    vSpasiChar          character varying;
    vYes                character varying;
    vNo                 character varying;
    vCurrency           character varying;
    vNol                numeric;
    vWorkFlowStatus     character varying;
    vCoaId              bigint;
    vProcessName        character varying;
    vCountMinutes       integer;
    vParamKey           character varying;
    vStatusDocDraft     character varying;
    vWorkFlowStatusDraft character varying;
    vEmptyString        character varying;
    vDocTypeDPBalance   bigint;
    vFlowArDebtNoteId   bigint;
    vSchemeDebtNoteAr   character varying;
    vInvoiceArId        bigint;
    vInvoiceArBalanceId bigint;
    vNettAmountWithoutNonTax bigint;
    vMaxLineNo          bigint;
BEGIN
    vStatusDoc := 'I';
    vDocTypeDebtNoteAr := 241;
    vDocTypeDPBalance := 252;
    vEmptyValue := -99;
    vSpasiChar := ' ';
    vYes := 'Y';
    vNo := 'N';
    vCurrency := 'IDR';
    vNol := 0;
    vWorkFlowStatus := 'APPROVED';
    vProcessName := 'fi_submit_invoice_ar';
    vCountMinutes := 1;
    vParamKey := 'invoiceArId';
    vStatusDocDraft := 'D';
    vWorkFlowStatusDraft := 'DRAFT';
    vEmptyString := '';
    vSchemeDebtNoteAr = 'DF01';


    -- hanya dijalankan jika ada kurang bayar(BELUM LUNAS PEMBAYARANNYA ), kalau sudah lunas tidak tulis debtnoteAR
    IF pCountBelumDiBayar > 0 THEN

        /* get coa id*/
        vCoaId := (SELECT f_get_system_coa_by_group_coa(pTenantId, 'PendapatanPenjualan'));

        SELECT nextval('fi_invoice_ar_seq') INTO vInvoiceArId;
        SELECT nextval('fi_invoice_ar_balance_seq') INTO vInvoiceArBalanceId;

        IF pFlgPkp = vYes THEN
            /* PKP */
            /*
             * insert ke tabel finance invoice ar
             * dengan status_doc = D dan workflow_status = DRAFT
             *
             * mod by Didit, 29 Nov 2016
             * Debt Note AR langsung approved
             * ubah perhitungan tax
             */
            INSERT INTO fi_invoice_ar(
                invoice_ar_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
                partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
                due_date, curr_code, total_tax_base_amount, total_amount, tax_amount,
                remark, status_doc, workflow_status, version, create_datetime,
                create_user_id, update_datetime, update_user_id)
            SELECT vInvoiceArId, A.tenant_id, vDocTypeDebtNoteAr, A.doc_no, A.doc_date, c.ou_parent_id,
                D.partner_id, A.doc_no, A.doc_date, A.doc_type_id, A.trx_pos_id,
                B.payment_due_date, vCurrency, vNol, vNol, vNol,
                A.remark, vStatusDoc, vWorkFlowStatus, vNol, pDatetime,
                pUserId, pDatetime, pUserId
            FROM i_trx_pos A
            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
            INNER JOIN m_partner D ON A.partner_id = D.partner_id AND A.tenant_id = D.tenant_id
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
                AND B.under_payment_amount > 0
            ORDER BY doc_date;

            SELECT COALESCE(A.nett_amount - SUM(CASE WHEN B.tax_id = -99 AND B.tax_amount = 0 THEN B.base_amount ELSE 0 END), A.nett_amount) INTO vNettAmountWithoutNonTax
            FROM i_trx_pos A
            INNER JOIN i_trx_pos_termin_payment 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_tax B ON A.trx_pos_id = B.trx_pos_id AND A.process_no = B.process_no AND A.tenant_id = B.tenant_id
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
            GROUP BY A.nett_amount;

            -- insert ke tabel fi_invoice_ar_tax jika ou_bu_id punya flg_pkp = Y
            INSERT INTO fi_invoice_ar_tax(
                tenant_id, invoice_ar_id, tax_id, flg_amount,
                tax_percentage, base_amount,
                tax_amount, tax_no, tax_date, tax_curr_code,
                gov_tax_amount, remark, version, create_datetime, create_user_id,
                update_datetime, update_user_id)
            SELECT A.tenant_id, C.invoice_ar_id, D.tax_id, D.flg_amount,
                -- D.percentage, E.under_payment_amount, B.tax_amount, vEmptyString, A.doc_date, vCurrency,
                -- rev by Ping An 17 Nov 2015, base_amount ambil dari nilai total amount di POS, bukan ambil nilai kurang bayarnya saja
                -- D.percentage, A.total_amount, B.tax_amount, vEmptyString, A.doc_date, vCurrency,
                -- set base amountnya ambil dari nett amount di pos(tanpa rounding) - tax
                D.percentage, SUM(vNettAmountWithoutNonTax - f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, B.flg_amount, B.tax_percentage)),
                SUM(f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, B.flg_amount, B.tax_percentage)), vEmptyString, A.doc_date, vCurrency,
                SUM(f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, B.flg_amount, B.tax_percentage)), A.remark, vNol, pDatetime, pUserId,
                pDatetime, pUserId
            FROM i_trx_pos A
            INNER JOIN i_trx_pos_termin_payment 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_tax 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 fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
            INNER JOIN m_tax D ON D.tax_id = B.tax_id
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
                AND B.tax_id <> -99
                AND B.tax_amount <> 0
            GROUP BY A.tenant_id, C.invoice_ar_id, D.tax_id, D.flg_amount, D.percentage, A.doc_date, A.remark;


            /* insert fi_invoice_ar_cost non tax */
            INSERT INTO fi_invoice_ar_cost(
                tenant_id, invoice_ar_id, line_no, activity_gl_id,
                ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
                remark, version, create_datetime, create_user_id, update_datetime,
                update_user_id, segment_id)
            SELECT A.tenant_id, C.invoice_ar_id, ROW_NUMBER() OVER (PARTITION BY C.invoice_ar_id), E.activity_gl_id,
                vEmptyValue, vCurrency, F.base_amount, -99, vNol, vNol,
                A.remark, vNol, pDatetime, pUserId, pDatetime,
                pUserId, vEmptyValue
            FROM i_trx_pos A
            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 i_trx_pos_tax F ON A.tenant_id = F.tenant_id AND A.process_no = F.process_no AND A.trx_pos_id = F.trx_pos_id
            INNER JOIN fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
            INNER JOIN m_activity_gl E ON E.coa_id = vCoaId AND A.tenant_id = E.tenant_id
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
                AND F.tax_id = -99
                AND F.tax_amount = 0;

            SELECT COALESCE(MAX(line_no), 0) INTO vMaxLineNo
            FROM fi_invoice_ar_cost
            WHERE invoice_ar_id = vInvoiceArId;

            /* insert fi_invoice_ar_cost with tax */
            INSERT INTO fi_invoice_ar_cost(
                tenant_id, invoice_ar_id, line_no, activity_gl_id,
                ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
                remark, version, create_datetime, create_user_id, update_datetime,
                update_user_id, segment_id)
            SELECT A.tenant_id, C.invoice_ar_id, vMaxLineNo + ROW_NUMBER() OVER (PARTITION BY C.invoice_ar_id), E.activity_gl_id,
                vEmptyValue, vCurrency, vNettAmountWithoutNonTax - COALESCE(f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, F.flg_amount, F.tax_percentage), vNol), COALESCE(F.tax_id, vEmptyValue), COALESCE(F.tax_percentage, vNol), COALESCE(f_get_tax_amount(A.tenant_id, vNettAmountWithoutNonTax, F.flg_amount, F.tax_percentage), vNol),
                A.remark, vNol, pDatetime, pUserId, pDatetime,
                pUserId, vEmptyValue
            FROM i_trx_pos A
            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 i_trx_pos_tax F ON A.tenant_id = F.tenant_id AND A.process_no = F.process_no AND A.trx_pos_id = F.trx_pos_id
            INNER JOIN fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
            LEFT JOIN m_tax D ON F.tax_id = D.tax_id
            INNER JOIN m_activity_gl E ON E.coa_id = vCoaId AND A.tenant_id = E.tenant_id
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
                AND F.tax_id <> -99
                AND F.tax_amount <> 0;

            SELECT COALESCE(MAX(line_no), 0) INTO vMaxLineNo
            FROM fi_invoice_ar_cost
            WHERE invoice_ar_id = vInvoiceArId;

            /* untuk PKP ambil dan tulis juga nilai roundingnya jika ada */
            /* insert rounding tanpa tax */
            INSERT INTO fi_invoice_ar_cost(
                tenant_id, invoice_ar_id, line_no, activity_gl_id,
                ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
                remark, version, create_datetime, create_user_id, update_datetime,
                update_user_id, segment_id)
            SELECT A.tenant_id, C.invoice_ar_id, vMaxLineNo + ROW_NUMBER() OVER (PARTITION BY C.invoice_ar_id), E.activity_gl_id,
                vEmptyValue, vCurrency, A.rounding_amount, vEmptyValue, vNol, vNol, -- tanpa tax
                A.remark, vNol, pDatetime, pUserId, pDatetime,
                pUserId, vEmptyValue
            FROM i_trx_pos A
            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 i_trx_pos_tax F ON A.tenant_id = F.tenant_id AND A.process_no = F.process_no AND A.trx_pos_id = F.trx_pos_id
            INNER JOIN fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
            LEFT JOIN m_tax D ON F.tax_id = D.tax_id
            INNER JOIN m_activity_gl E ON E.coa_id = vCoaId AND A.tenant_id = E.tenant_id
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
                AND A.rounding_amount > 0;

            /*
             * insert fi_invoice_ar_balance
             * untuk saldo DP partner corporate yg bayar sebagian
             * untuk yang PKP Saldo DP, flg_payment = N dan payment_amount = 0
             */
            INSERT INTO fi_invoice_ar_balance(
                    invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
                    doc_no, doc_date, ext_doc_no, ext_doc_date, ref_doc_type_id,
                    ref_id, partner_id, due_date, curr_code, amount, remark, payment_amount,
                    flg_payment, version, create_datetime, create_user_id, update_datetime,
                    update_user_id)
            SELECT vInvoiceArBalanceId, A.tenant_id, c.ou_parent_id, vDocTypeDPBalance, F.invoice_ar_id,
                A.doc_no, A.doc_date, vSpasiChar, vSpasiChar, A.doc_type_id,
                A.trx_pos_id, A.partner_id,
                -- rev by Ping An 16 Nov 2015, due_date ambil dari docdatenya POS karena DP sudah kejadian dan sudah approved  B.payment_due_date,
                A.doc_date,
                A.curr_code, -1 * A.total_payment, vSpasiChar, vNol,
                vNo, vNol, pDatetime, pUserId, pDatetime,
                pUserId
            FROM i_trx_pos A
            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
            INNER JOIN fi_invoice_ar F ON A.doc_no = F.doc_no AND A.doc_date = F.doc_date AND A.tenant_id = F.tenant_id AND F.doc_type_id = vDocTypeDebtNoteAr
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
                AND B.payment_amount > 0;
                -- B.payment_amount > 0 untuk cek apakah ada yang dibayar
                -- F.invoice_ar_id : unique index -> fi_invoice_ar_balance adalah tenant_id, doc_type_id, dan invoice_ar_id.
                -- jadi tidak dipantek -99, krn pasti akan kena error unique constraint
        ELSE
            /* NON PKP */
            /*
             * insert ke tabel finance invoice ar
             * dengan status_doc = I dan workflow_status = APPROVED
             *
             */
            INSERT INTO fi_invoice_ar(
                invoice_ar_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
                partner_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id,
                due_date, curr_code, total_tax_base_amount, total_amount, tax_amount,
                remark, status_doc, workflow_status, version, create_datetime,
                create_user_id, update_datetime, update_user_id)
            SELECT vInvoiceArId, A.tenant_id, vDocTypeDebtNoteAr, A.doc_no, A.doc_date, C.ou_parent_id,
                D.partner_id, A.doc_no, A.doc_date, vEmptyValue, vEmptyValue,
                B.payment_due_date, vCurrency, vNol, vNol, vNol,
                A.remark, vStatusDoc, vWorkFlowStatus, vNol, pDatetime,
                pUserId, pDatetime, pUserId
            FROM i_trx_pos A
            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
            INNER JOIN m_partner D ON A.partner_id = D.partner_id AND A.tenant_id = D.tenant_id
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
                AND B.under_payment_amount > 0
            ORDER BY doc_date;


            /* insert fi_invoice_ar_cost */
            INSERT INTO fi_invoice_ar_cost(
                tenant_id, invoice_ar_id, line_no, activity_gl_id,
                ou_rc_id, curr_code, add_amount, tax_id, tax_percentage, tax_amount,
                remark, version, create_datetime, create_user_id, update_datetime,
                update_user_id, segment_id)
            SELECT A.tenant_id, C.invoice_ar_id, ROW_NUMBER() OVER (PARTITION BY C.invoice_ar_id), E.activity_gl_id,
                vEmptyValue, vCurrency, B.under_payment_amount, vEmptyValue, vNol, vNol,
                A.remark, vNol, pDatetime, pUserId, pDatetime,
                pUserId, vEmptyValue
            FROM i_trx_pos A
            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 fi_invoice_ar C ON A.doc_no = C.doc_no AND A.doc_date = C.doc_date AND A.tenant_id = C.tenant_id AND C.doc_type_id = vDocTypeDebtNoteAr
            --INNER JOIN m_tax D ON A.tenant_id = D.tenant_id
            INNER JOIN m_activity_gl E ON E.coa_id = vCoaId AND A.tenant_id = E.tenant_id
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId;

            /*
             * insert fi_invoice_ar_balance
             * untuk saldo DP partner corporate yg bayar sebagian
             * untuk yang Non PKP Saldo DP, flg_payment = Y dan payment_amount = amount
             */
            INSERT INTO fi_invoice_ar_balance(
                    invoice_ar_balance_id, tenant_id, ou_id, doc_type_id, invoice_ar_id,
                    doc_no, doc_date, ext_doc_no, ext_doc_date, ref_doc_type_id,
                    ref_id, partner_id, due_date, curr_code, amount, remark, payment_amount,
                    flg_payment, version, create_datetime, create_user_id, update_datetime,
                    update_user_id)
            SELECT vInvoiceArBalanceId, A.tenant_id, c.ou_parent_id, vDocTypeDPBalance, F.invoice_ar_id,
                A.doc_no, A.doc_date, vSpasiChar, vSpasiChar, A.doc_type_id,
                A.trx_pos_id, A.partner_id,
                -- rev by Ping An 16 Nov 2015, due_date ambil dari docdatenya POS karena DP sudah kejadian dan sudah approved  B.payment_due_date,
                A.doc_date,
                A.curr_code, -1 * A.total_payment, vSpasiChar, -1 * A.total_payment,
                vYes, vNol, pDatetime, pUserId, pDatetime,
                pUserId
            FROM i_trx_pos A
            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
            INNER JOIN fi_invoice_ar F ON A.doc_no = F.doc_no AND A.doc_date = F.doc_date AND A.tenant_id = F.tenant_id AND F.doc_type_id = vDocTypeDebtNoteAr
            WHERE A.tenant_id = pTenantId
                AND A.process_no = pProcessNo
                AND A.ou_id = pOuId
                AND A.trx_pos_id = pTrxPosId
                AND B.payment_amount > 0;
                -- B.payment_amount > 0 untuk cek apakah ada yang dibayar
                -- F.invoice_ar_id : unique index -> fi_invoice_ar_balance adalah tenant_id, doc_type_id, dan invoice_ar_id.
                -- jadi tidak dipantek -99, krn pasti akan kena error unique constraint

        END IF;

        /* generate_process_message_for_submit_doc */
        /**
         * mod by Didit, 29 Nov 2016
         * Debt Note AR langsung approved, tidak pedulikan flag PKP
         */
        --IF pFlgPkp = vNo THEN

        PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessName, B.invoice_ar_id ||'_'||B.doc_no,
                pDatetime, vParamKey, B.invoice_ar_id::character varying, pUserId)
        FROM i_trx_pos A
        INNER JOIN fi_invoice_ar B ON A.tenant_id = B.tenant_id AND A.doc_no = B.doc_no AND A.doc_date = B.doc_date AND B.doc_type_id = vDocTypeDebtNoteAr
        WHERE A.tenant_id = pTenantId
            AND A.process_no = pProcessNo
            AND A.ou_id = pOuId
            AND A.trx_pos_id = pTrxPosId;

        -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
        SELECT awe_flow_id INTO vFlowArDebtNoteId
        FROM awe_flow
        WHERE scheme = vSchemeDebtNoteAr AND
            flg_validate = 'Y' AND
            active = 'Y';

         -- 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 A.invoice_ar_id||'_'||A.doc_no, A.tenant_id, vSchemeDebtNoteAr, A.invoice_ar_id, A.doc_no, A.doc_date, vWorkFlowStatus,
            A.remark, A.create_user_id, vEmptyValue, 'R', 'AR DEBT NOTE'||A.doc_no,
            '{}', vFlowArDebtNoteId, pDatetime, pUserId, vEmptyValue,
            pDatetime, pUserId, vEmptyValue, 0
        FROM fi_invoice_ar A
        WHERE A.invoice_ar_id = vInvoiceArId
            AND A.status_doc = 'R';

        --END IF;

        /**
         * mod By Didit, 1 Des 2016
         * untuk alokasi otomatis saldo DP ke debt note ar
         */
        IF EXISTS(SELECT 1 FROM fi_invoice_ar_balance WHERE invoice_ar_balance_id = vInvoiceArBalanceId) THEN
            PERFORM f_automatic_credit_alloc_ar(pSessionId, pTenantId, vInvoiceArId, vInvoiceArBalanceId, pUserId, pDatetime);
        END IF;

    END IF;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/