gl_process_formula_recurring

CREATE OR REPLACE FUNCTION gl_process_formula_recurring(character varying, bigint, bigint, character varying, character varying, character varying, bigint)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pYearMonth          ALIAS FOR $4;
    pWhenProcess        ALIAS FOR $5;
    pDatetime           ALIAS FOR $6;
    pUserId             ALIAS FOR $7;

    vEmptyId                bigint;
    vRoundingAmount         integer;
    vEmptyValue             character varying(1);


    -- variable untuk tulis ke tabel jurnal trx
    vJournalTypeRecurring   character varying(20);
    vDocTypeIdRecurring     bigint;
    vPartnerId              bigint;
    vWarehouseId            bigint;
    vDocIdRecurring         bigint;
    vDocNoRecurring         character varying(20);
    vDocDateRecurring       character varying(8);
    vExtDocNo               character varying(20);
    vExtDocDate             character varying(8);
    vStatusDraft            character varying(1);
    vCashBankId             bigint;
    vRefDocTypeId           bigint;
    vRefId                  bigint;
    vDocDate                character varying(8);

    vTypeRate               character varying(3);
    vFLAG_SOURCE_COA        character varying(2);
    vEomDate                character varying(8);

    vRestOfMonth                numeric;
BEGIN

    vEmptyId := -99;
    vEmptyValue := ' ';
    vTypeRate := 'COM';
    vStatusDraft := 'D';

    -- variable untuk tulis ke tabel jurnal trx
    vJournalTypeRecurring   := 'GL.FORMULA';
    vDocTypeIdRecurring     := '1'; -- Recurring = 1, Allocation = 2
    vDocIdRecurring         := 751; -- docid baru untuk jurnal formula
    vDocNoRecurring         := vEmptyValue;
    vDocDateRecurring       := vEmptyValue;
    vPartnerId              := vEmptyId;
    vWarehouseId            := vEmptyId;
    vExtDocNo               := vEmptyValue;
    vExtDocDate             := vEmptyValue;
    vCashBankId             := vEmptyId;

    vRefDocTypeId           := vEmptyId;
    vRefId                  := vEmptyId;
    vDocDate                := vEmptyValue;
    vFLAG_SOURCE_COA        := 'GL';

    vRoundingAmount := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);

    vEomDate := TO_CHAR(TO_DATE(pYearMonth||'01','YYYYMMDD') + INTERVAL '1 MONTH - 1 DAY', 'YYYYMMDD');

     -- step 1 : insert ke temp, ambil dari tabel formula recurring
     -- step 2 : update kembali ke saldo formula , formula yang ada disaldo
     -- step 3 : insert yang tidak ada di saldo
     -- step 4 : insert ke saldo dari tabel gl formula , untuk formula yang belum ada disaldo
     -- step 5 : tulis ke log
     -- Step 6 : tulis ke gl_journal_trx
     -- Step 7 : tulis ke tt_journal_trx_item
     -- Step 8 : tulis ke gl_journal_trx_item
     -- Step 9 : tulis ke gl_journal_trx_mapping
     -- Step 10: tulis semua formula yang di process ke Admin process Formula
     -- Step 11: update  process di glformulaprocess

     -- ambil data detail journal formula recurring
    INSERT INTO tt_gl_journal_formula_recurring(
            session_id, journal_formula_recurring_id, tenant_id, journal_formula_id,
            line_no, recurring_curr_code, recurring_amount, recurring_rate_date,
            sign_journal, ou_branch_id, sub_ou_id, coa_id, ou_rc_id, segment_id,
            amount_factor_x, amount_factor_y, remark, used_recurr_amount, balance_used_amount, rounding_amount,
            start_periode, end_periode, journal_trx_id)
    SELECT pSessionId, C.journal_formula_recurring_id, C.tenant_id, C.journal_formula_id,
            C.line_no, C.recurring_curr_code, C.recurring_amount, C.recurring_rate_date,
            C.sign_journal, C.ou_branch_id, C.sub_ou_id, C.coa_id, C.ou_rc_id, C.segment_id,
            C.amount_factor_x, C.amount_factor_y, C.remark,
            ROUND(C.recurring_amount * C.amount_factor_x / C.amount_factor_y, vRoundingAmount), 0, 0,
            B.start_periode, B.end_periode, vEmptyId
    FROM gl_journal_formula A INNER JOIN gl_journal_formula_process B ON A.journal_formula_id = B.journal_formula_id
                            INNER JOIN gl_journal_formula_recurring C ON A.journal_formula_id = C.journal_formula_id
    WHERE A.tenant_id = pTenantId AND
        A.flg_validate = 'Y' AND
        pYearMonth BETWEEN B.start_periode AND B.end_periode AND
        NOT EXISTS ( SELECT 1 FROM gl_admin_process_formula E
                    WHERE  E.tenant_id = A.tenant_id AND
                            E.journal_formula_id = A.journal_formula_id AND
                            E.year_month = pYearMonth AND
                            E.flag_process = 'Y');

    -- hitung nilai berjalan recurring amount, ini jika saldo sudah pernah dibuat / bukan periode pertama kali
    UPDATE tt_gl_journal_formula_recurring A SET balance_used_amount = B.used_recurr_amount
    FROM gl_journal_formula_recurring_balance B
    WHERE A.session_id = pSessionId AND
        A.journal_formula_recurring_id = B.journal_formula_recurring_id;

    -- jika periode terakhir maka harus hitung nilai pembulatan nya
    UPDATE tt_gl_journal_formula_recurring A SET rounding_amount = recurring_amount - used_recurr_amount - balance_used_amount
    WHERE A.session_id = pSessionId AND
        A.end_periode = pYearMonth;

    SELECT DATE_PART('YEAR',
            AGE(TO_TIMESTAMP(A.end_periode, 'YYYYMM'), TO_TIMESTAMP(A.start_periode, 'YYYYMM'))
        ) * 12 + DATE_PART('MONTH',
            AGE(TO_TIMESTAMP(A.end_periode, 'YYYYMM'), TO_TIMESTAMP(A.start_periode, 'YYYYMM'))
        ) INTO vRestOfMonth
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId AND
        A.start_periode = pYearMonth;

    -- buat balance untuk periode proses = start periode
    INSERT INTO gl_journal_formula_recurring_balance(
            tenant_id, journal_formula_recurring_id, journal_formula_id,
            beg_recurr_amount, used_recurr_amount,
            rest_of_month,
            create_datetime, create_user_id, update_datetime, update_user_id, version)
    SELECT A.tenant_id, A.journal_formula_recurring_id, A.journal_formula_id,
            A.recurring_amount, A.used_recurr_amount,
            vRestOfMonth,
            pDatetime, pUserId, pDatetime, pUserId, 0
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId AND
        A.start_periode = pYearMonth;

    -- update balance untuk nilai used amount, dan sisa bulan
    UPDATE gl_journal_formula_recurring_balance A SET used_recurr_amount = A.used_recurr_amount + B.used_recurr_amount + B.rounding_amount,
                                                        rest_of_month = A.rest_of_month - 1
    FROM tt_gl_journal_formula_recurring B
    WHERE B.session_id = pSessionId AND
        A.journal_formula_id = B.journal_formula_id AND
        A.journal_formula_recurring_id = B.journal_formula_recurring_id AND
        B.start_periode <> pYearMonth;

    -- buat log data used recurring amount
    INSERT INTO gl_log_journal_formula_recurring_balance
    (tenant_id, journal_formula_recurring_id,
    journal_formula_id, year_month, used_recurr_amount, rounding_amount,
    create_datetime, create_user_id, update_datetime, update_user_id, version)
    SELECT A.tenant_id, A.journal_formula_recurring_id,
        A.journal_formula_id, pYearMonth, A.used_recurr_amount, A.rounding_amount,
        pDatetime, pUserId, pDatetime, pUserId, 0
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId;

    INSERT INTO gl_admin_process_formula
    (tenant_id, ou_id, journal_formula_id, journal_trx_id,
    year_month, flag_process, process_datetime, process_user_id,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT pTenantId, pOuId , A.journal_formula_id, NEXTVAL('gl_journal_trx_seq'),
           pYearMonth, 'Y', pDatetime, pUserId,
           0, pDatetime, pUserId, pDatetime,pUserId
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId
    GROUP BY A.journal_formula_id;

    UPDATE tt_gl_journal_formula_recurring A SET journal_trx_id = B.journal_trx_id
    FROM gl_admin_process_formula B
    WHERE A.session_id = pSessionId AND
        A.journal_formula_id = B.journal_formula_id AND
        B.year_month = pYearMonth;

    UPDATE gl_journal_formula_process A SET flg_process = 'Y' , last_process = pYearMonth,
                                            update_datetime = pDatetime, update_user_id = pUserId,
                                            version = version + 1
    FROM (SELECT journal_formula_id FROM tt_gl_journal_formula_recurring WHERE session_id = pSessionId GROUP BY journal_formula_id) B
    WHERE A.journal_formula_id = B.journal_formula_id;

    INSERT INTO gl_journal_trx
    (journal_trx_id, 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 B.journal_trx_id, A.tenant_id, vJournalTypeRecurring, vDocTypeIdRecurring, B.journal_trx_id,
        'JR'||'/'||A.journal_formula_id||'/'|| pYearMonth, vEomDate,
        A.ou_id, A.ou_branch_id, A.sub_ou_id, vPartnerId, vCashBankId, vWarehouseId, vExtDocNo, vExtDocDate,
        vEmptyId, A.journal_formula_id, vEomDate,
        f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), A.formula_desc, vStatusDraft, 'DRAFT',
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM gl_journal_formula A,
        (SELECT journal_trx_id, journal_formula_id FROM tt_gl_journal_formula_recurring WHERE session_id = pSessionId) B
    WHERE A.journal_formula_id = B.journal_formula_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, A.journal_trx_id, A.line_no,
        vEmptyId, A.journal_formula_recurring_id,
        vEmptyId, vEmptyId, vEmptyId, A.ou_rc_id,
        vEmptyId, A.sign_journal, vFLAG_SOURCE_COA, vEmptyId,
        A.coa_id, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), 0, vEmptyId,
        A.used_recurr_amount + A.rounding_amount, vEomDate, vTypeRate,
        1, 1, 'JV_RECURR_TARGET', A.remark
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId;

     --- Step 8 : tulis ke gl_journal_trx_item
    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, gl_curr_code, gl_amount, 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.curr_code, A.amount, A.journal_desc, A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId;

     --- Step 9 : tulis ke gl_journal_trx_mapping
    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, gl_curr_code, gl_amount, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT B.tenant_id, B.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY B.journal_trx_id),
        vEmptyId, A.journal_formula_id,
        vEmptyId, vEmptyId, vEmptyId, A.ou_rc_id,
        vEmptyId, A.sign_journal, vFLAG_SOURCE_COA, vEmptyId,
        A.coa_id, recurring_curr_code, 0, vEmptyId,
        B.amount,
        vEomDate,
        vTypeRate,
        1, 1, f_get_value_system_config_by_param_code(B.tenant_id, 'ValutaBuku'),
        B.amount,
        'JV_RECURR_MAPPING', A.remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM gl_journal_formula_mapping A,
        (SELECT tenant_id, journal_trx_id, journal_formula_id, recurring_curr_code, SUM(used_recurr_amount + rounding_amount) AS amount
        FROM tt_gl_journal_formula_recurring
        WHERE session_id = pSessionId
        GROUP BY tenant_id, journal_trx_id, journal_formula_id, recurring_curr_code) B
    WHERE A.journal_formula_id = B.journal_formula_id;

    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    DELETE FROM tt_gl_journal_formula_recurring WHERE  session_id = pSessionId;

 END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;