gl_process_journal_formula_recurring_201808201516

/**
 * Adrian, Aug 16, 2018
 */

CREATE OR REPLACE FUNCTION gl_process_journal_formula_recurring(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE

    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vJournalFormulaId       bigint;
    vYearMonth              character varying(6);
    vUserId                 bigint;
    vDatetime               character varying(14);
    vJournalVoucherDocNo    character varying;

    vEmptyId                bigint;
    vRoundingAmount         integer;
    vEmptyValue             character varying(1);
    vNo                     character varying(1);
    vYes                    character varying(1);
    vFinal                  character varying(1);
    vOuId                   bigint;
    vGlLedgerCode           character varying;
    vStatusLedgerDone       character varying;
    vExtDocNo               character varying;
    vExtDocDate             character varying;
    vPeriod                 bigint;

    vJournalTypeJournalVoucher  character varying(20);
    vDocTypeIdJournalVOucher    bigint;
    vStatusDraft                character varying(1);
    vTypeRate                   character varying(3);
    vFLAG_SOURCE_COA            character varying(3);
    vEomDate                    character varying(8);
    vJournalTrxId               bigint;

BEGIN

    vEmptyId := -99;
    vEmptyValue := ' ';
    vNo := 'N';
    vYes := 'Y';
    vFinal := 'F';
    vGlLedgerCode := 'GL';
    vStatusLedgerDone := '1';
    vTypeRate := 'COM';
    vStatusDraft := 'D';
    vDocTypeIdJournalVoucher := 721;
    vFLAG_SOURCE_COA := 'COA';

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'gl_process_journal_formula_recurring' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vJournalFormulaId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'journalFormulaId';

    SELECT CAST(A.process_parameter_value AS character varying(6)) INTO vYearMonth
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'yearMonth';

        SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    SELECT CAST(A.process_parameter_value AS character varying) INTO vJournalVoucherDocNo
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'journalVoucherDocNo';

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

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

    SELECT journal_type FROM m_document_journal WHERE doc_type_id = vDocTypeIdJournalVoucher INTO vJournalTypeJournalVoucher;

    SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;



    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;

    DELETE FROM tt_gl_journal_formula_recurring WHERE  session_id = pSessionId;


    -- Validasi Journal Formula ada
    SELECT A.ou_id, A.ext_doc_no, A.ext_doc_date
    INTO vOuId, vExtDocNo, vExtDocDate
    FROM gl_journal_formula A
    WHERE A.journal_formula_id = vJournalFormulaId
        AND A.tenant_id = pTenantId
        AND A.flg_validate = vYes;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Journal Formula Recurring is not found or is not valid yet';
    END IF;

    -- Validasi belum tutup bulan
    IF EXISTS (SELECT 1 FROM m_admin_process_ledger e, m_ou_structure f
        WHERE e.tenant_id = pTenantId AND e.ou_id = f.ou_bu_id AND f.ou_id = vOuId AND
            e.date_year_month = vYearMonth AND e.ledger_code = vGlLedgerCode
            AND e.status_ledger = vStatusLedgerDone) THEN
        RAISE EXCEPTION 'Ledger GL for OU % and Period % already closed', vOuId, vYearMonth;
    END IF;

    -- Validasi Journal Formula untuk bulan tersebut belum diproses
    IF EXISTS (SELECT 1 FROM gl_admin_process_formula A
        WHERE A.journal_formula_id = vJournalFormulaId
            AND A.year_month = vYearMonth) THEN
        RAISE EXCEPTION 'Journal Formula Recurring with Id % and Period % already processed', vJournalFormulaId, vYearMonth;
    END IF;

     -- Insert detail recurring ke table temp
    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,
            CASE WHEN ROUND(C.recurring_amount * C.amount_factor_x / C.amount_factor_y, vRoundingAmount) > D.beg_recurr_amount THEN
                D.beg_recurr_amount
            ELSE
                ROUND(C.recurring_amount * C.amount_factor_x / C.amount_factor_y, vRoundingAmount)
            END,
            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
                            LEFT JOIN gl_journal_formula_recurring_balance D ON C.tenant_id = D.tenant_id AND C.journal_formula_recurring_id = D.journal_formula_recurring_id
    WHERE A.tenant_id = pTenantId AND
        A.flg_validate = vYes AND
        vYearMonth BETWEEN B.start_periode AND B.end_periode AND
        A.journal_formula_id = vJournalFormulaId 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 = vYearMonth AND
                            E.flag_process = vYes);

     -- Update saldo terakhir ke table temp
    UPDATE tt_gl_journal_formula_recurring A SET balance_used_amount = B.recurr_on_the_road
    FROM gl_journal_formula_recurring_balance B
    WHERE A.session_id = pSessionId AND
        A.journal_formula_recurring_id = B.journal_formula_recurring_id;

    -- Untuk periode terakhir, Jika x * periode / y = 1, hitung pembulatan
    SELECT CAST(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'))
    ) + 1 AS bigint)
    FROM gl_journal_formula_process A
    WHERE A.journal_formula_id = vJournalFormulaId
    INTO vPeriod;

    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 = vYearMonth AND
        (A.amount_factor_x * vPeriod / A.amount_factor_y) = 1;



    -- Update balance untuk nilai used amount, dan sisa bulan
    -- untuk process yang sudah pernah dijalankan
    UPDATE gl_journal_formula_recurring_balance A SET used_recurr_amount = B.used_recurr_amount + B.rounding_amount,
                                                        rest_of_month = A.rest_of_month - 1,
                                                        recurr_on_the_road = A.recurr_on_the_road + B.used_recurr_amount + B.rounding_amount,
                                                        total_rounding_amount = A.total_rounding_amount + B.rounding_amount,
                                                        beg_recurr_amount = A.beg_recurr_amount - B.used_recurr_amount - B.rounding_amount,
                                                        update_datetime = vDatetime,
                                                        update_user_id = vUserId,
                                                        version = version + 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 <> vYearMonth
        EXISTS (
            SELECT 1
            FROM gl_journal_formula_recurring_balance Z
            WHERE Z.tenant_id = B.tenant_id
                AND Z.journal_formula_recurring_id = B.journal_formula_recurring_id
        );

    -- Insert saldo untuk process pertama kali
    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,
            recurr_on_the_road, total_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,
            A.recurring_amount - (A.used_recurr_amount + A.rounding_amount), A.used_recurr_amount + A.rounding_amount,
            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'))
            ),
            A.used_recurr_amount + A.rounding_amount , A.rounding_amount,
            vDatetime, vUserId, vDatetime, vUserId, 0
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId AND
        --A.start_periode = vYearMonth
        NOT EXISTS (
            SELECT 1
            FROM gl_journal_formula_recurring_balance Z
            WHERE Z.tenant_id = A.tenant_id
                AND Z.journal_formula_recurring_id = A.journal_formula_recurring_id
        );

    -- Insert log recurring
    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, vYearMonth, A.used_recurr_amount + A.rounding_amount, A.rounding_amount,
        vDatetime, vUserId, vDatetime, vUserId, 0
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId;

    -- Insert admin process formula
    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, vOuId , A.journal_formula_id, vJournalTrxId,
           vYearMonth, vYes, vDatetime, vUserId,
           0, vDatetime, vUserId, vDatetime,vUserId
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId
    GROUP BY A.journal_formula_id;

    -- Update journal_trx_id pada table temp
    UPDATE tt_gl_journal_formula_recurring A SET journal_trx_id = vJournalTrxId
    WHERE A.session_id = pSessionId;

    -- Update gl_journal_formula_process
    UPDATE gl_journal_formula_process A SET flg_process = vYes, last_process = vYearMonth,
                                            update_datetime = vDatetime, update_user_id = vUserId,
                                            version = version + 1
    WHERE A.journal_formula_id = vJournalFormulaId;

    -- Update flg_process pada gl_journal_formula_process menjadi Final
    -- Jika last_process = end_periode
    UPDATE gl_journal_formula_process A SET flg_process = vFinal
    WHERE A.journal_formula_id = vJournalFormulaId
        AND A.last_process = A.end_periode;

    -- Insert header Journal Voucher
    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,
    flg_fix, flg_validate,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vJournalTrxId, A.tenant_id, vJournalTypeJournalVoucher, vDocTypeIdJournalVoucher, vEmptyId,
        vJournalVoucherDocNo, vEomDate,
        A.ou_id, A.ou_branch_id, A.sub_ou_id, vEmptyId, vEmptyId, vEmptyId, vExtDocNo, vExtDocDate,
        vEmptyId, A.journal_formula_id, vEomDate,
        f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), A.formula_name || ' - ' || A.formula_desc, vStatusDraft, 'DRAFT',
        vNo, vYes,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM gl_journal_formula A
    WHERE A.journal_formula_id = vJournalFormulaId;


    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,
    ou_id, sub_ou_id)
    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, 'JF.' || vYearMonth, A.remark,
        A.ou_branch_id, A.sub_ou_id
    FROM tt_gl_journal_formula_recurring A
    WHERE A.session_id = pSessionId AND
        A.used_recurr_amount + A.rounding_amount <> 0;

    -- Insert item Journal Voucher
    -- Untuk mapping Journal Formula Recurring
    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,
    ou_branch_id, ou_sub_bu_id)
    SELECT B.tenant_id, B.journal_trx_id, 1,
        vEmptyId, A.journal_formula_mapping_id,
        vEmptyId, vEmptyId, vEmptyId, A.ou_rc_id,
        A.segment_id, A.sign_journal, vFLAG_SOURCE_COA, vEmptyId,
        A.coa_id, B.recurring_curr_code, 0, vEmptyId,
        B.amount, vEomDate, vTypeRate,
        1, 1, B.recurring_curr_code, B.amount, 'JF.' || vYearMonth, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId,
        A.ou_branch_id, A.sub_ou_id
    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 AND
        B.amount <> 0;

    -- Insert item Journal Voucher
    -- Untuk detail Journal Formula Recurring
    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,
    ou_branch_id, ou_sub_bu_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id) + 1,
        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, vDatetime, vUserId, vDatetime, vUserId,
        A.ou_id, A.sub_ou_id
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId;

    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;
 /