CREATE OR REPLACE FUNCTION f_automatic_credit_alloc_ar(character varying, bigint, bigint, bigint, bigint, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId              alias for $1;
    pTenantId               alias for $2;
    pInvoiceArId            alias for $3; -- invoice_ar_id dokumen debt note ar
    pInvoiceArBalanceId     alias for $4; -- invoice_ar_balance_id saldo dp
    pUserId                 alias for $5;
    pDatetime               alias for $6;

    vAllocationArId         bigint;
    vAmount                 numeric;
    vDocTypeCreditAllocAr   bigint;
    vDocTypeDebtNoteAr      bigint;
    vEmptyId                bigint;
    vEmptyValue             character varying;
    vInProgress             character varying;
    vStatusDocApproved      character varying;
    vParamKey               character varying;
    vFlowAllocCreditArId    bigint;
    vSchemeAllocCreditAr    character varying;
BEGIN

    vEmptyId := -99;
    vEmptyValue := '';
    vInProgress := 'I';
    vStatusDocApproved := 'APPROVED';
    vDocTypeDebtNoteAr := 241;
    vDocTypeCreditAllocAr := 262;
    vParamKey := 'allocCreditArId';
    vSchemeAllocCreditAr := 'DH02';

    SELECT nextval('fi_allocation_ar_seq') INTO vAllocationArId;

    SELECT amount INTO vAmount
    FROM fi_invoice_ar_balance
    WHERE invoice_ar_balance_id = pInvoiceArBalanceId;

    INSERT INTO fi_allocation_ar(
    allocation_ar_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id,
    partner_id, partner_bank_id, ext_doc_no, ext_doc_date, ref_doc_type_id,
    ref_id, due_date, curr_code, debit_amount, credit_amount, payment_amount,
    remark, status_doc, workflow_status, version, create_datetime,
    create_user_id, update_datetime, update_user_id, ref_amount)
    SELECT vAllocationArId, A.tenant_id, vDocTypeCreditAllocAr, A.doc_no, A.doc_date, A.ou_id,
            A.partner_id, vEmptyId, vEmptyValue, vEmptyValue, A.doc_type_id,
            A.invoice_ar_balance_id, A.doc_date, A.curr_code, -1 * A.amount, -1 * A.amount, 0,
            vEmptyValue, vInProgress, vStatusDocApproved, 0, pDatetime,
            pUserId, pDatetime, pUserId, -1 * A.amount
    FROM fi_invoice_ar_balance A
    WHERE A.invoice_ar_balance_id = pInvoiceArBalanceId;

    INSERT INTO fi_allocation_ar_invoice(
    tenant_id, allocation_ar_id, ref_doc_type_id,
    ref_id, curr_code, debit_amount, credit_amount, payment_amount,
    version, create_datetime, create_user_id, update_datetime, update_user_id,
    ref_remain_amount)
    SELECT A.tenant_id, vAllocationArId, A.doc_type_id,
            A.invoice_ar_balance_id, A.curr_code, -1 * vAmount, 0, -1 * vAmount,
            0, pDatetime, pUserId, pDatetime, pUserId,
            A.amount
    FROM fi_invoice_ar_balance A
    WHERE A.tenant_id = pTenantId
        AND A.doc_type_id = vDocTypeDebtNoteAr
        AND A.invoice_ar_id = pInvoiceArId;

    PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, 'fi_submit_alloc_credit_ar', A.allocation_ar_id||'_'||A.doc_no,
            pDatetime, vParamKey, A.allocation_ar_id::character varying, pUserId)
    FROM fi_allocation_ar A
    WHERE A.allocation_ar_id = vAllocationArId;

    -- Mendapatkan default approval flow ID
    SELECT awe_flow_id INTO vFlowAllocCreditArId
    FROM awe_flow
    WHERE scheme = vSchemeAllocCreditAr 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.allocation_ar_id||'_'||A.doc_no, A.tenant_id, vSchemeAllocCreditAr, A.allocation_ar_id, A.doc_no, A.doc_date, vStatusDocApproved,
        A.remark, A.create_user_id, vEmptyId, 'R', 'AR_CREDIT_ALLOCATION'||A.doc_no,
        '{}', vFlowAllocCreditArId, pDatetime, pUserId, vEmptyId,
        pDatetime, pUserId, vEmptyId, 0
    FROM fi_allocation_ar A
    WHERE A.allocation_ar_id = vAllocationArId
        AND A.status_doc = 'R';

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /