Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Adrian, Aug 16, 2018
- */
- CREATE OR REPLACE FUNCTION gl_process_journal_formula_recurring(character varying, bigint, bigint, character varying, character varying, bigint)
- RETURNS void AS
- $BODY$
- DECLARE
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pJournalFormulaId ALIAS FOR $3;
- pYearMonth ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pUserId ALIAS FOR $6;
- vEmptyId bigint;
- vRoundingAmount integer;
- vEmptyValue 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;
- 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 := ' ';
- vYes := 'Y';
- vFinal := 'F';
- vGlLedgerCode := 'GL';
- vStatusLedgerDone := '1';
- vTypeRate := 'COM';
- vStatusDraft := 'D';
- -- variable untuk tulis ke tabel jurnal trx
- vDocTypeIdJournalVoucher := 721;
- vFLAG_SOURCE_COA := 'COA';
- 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');
- SELECT journal_type FROM m_document_journal WHERE doc_type_id = vDocTypeIdJournalVoucher INTO vJournalTypeJournalVoucher;
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
- -- 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 = pJournalFormulaId
- AND A.tenant_id = pTenantId
- AND A.flg_validate = vYes;
- IF NOT FOUND THEN
- RAISE EXCEPTION 'Journal Formula Recurring is not found or has is not valid yet';
- END IF;
- IF (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 = pYearMonth AND e.ledger_code = vGlLedgerCode
- AND e.status_ledger = vStatusLedgerDone) THEN
- RAISE EXCEPTION 'Ledger GL for OU % and Period % already closed', vOuId, pYearMonth;
- END IF;
- -- Insert detail recurring ke table tamp
- 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 = vYes AND
- pYearMonth BETWEEN B.start_periode AND B.end_periode AND
- A.journal_formula_id = pJournalFormulaId 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 = vYes);
- -- Update saldo terakhir ke table temp
- 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;
- -- Untuk periode terakhir, hitung pembulatan
- 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;
- -- 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,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- 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 <> pYearMonth
- 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,
- pDatetime, pUserId, pDatetime, pUserId, 0
- FROM tt_gl_journal_formula_recurring A
- WHERE A.session_id = pSessionId AND
- --A.start_periode = pYearMonth
- 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, pYearMonth, A.used_recurr_amount + A.rounding_amount, A.rounding_amount,
- pDatetime, pUserId, pDatetime, pUserId, 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,
- pYearMonth, vYes, pDatetime, pUserId,
- 0, pDatetime, pUserId, pDatetime,pUserId
- FROM tt_gl_journal_formula_recurring A
- WHERE A.session_id = pSessionId;
- -- 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 = pYearMonth,
- update_datetime = pDatetime, update_user_id = pUserId,
- version = version + 1
- WHERE A.journal_formula_id = pJournalFormulaId;
- -- 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 = pJournalFormulaId
- 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,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT vJournalTrxId, A.tenant_id, vJournalTypeJournalVoucher, vDocTypeIdJournalVoucher, vJournalTrxId,
- 'JR'||'/'||A.journal_formula_id||'/'|| pYearMonth, 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_desc || ' - ' || A.formula_desc, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM gl_journal_formula A
- WHERE A.journal_formula_id = pJournalFormulaId;
- 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, 'JF.' || pYearMonth, A.remark
- FROM tt_gl_journal_formula_recurring A
- WHERE A.session_id = pSessionId;
- -- 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)
- 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.' || pYearMonth, 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;
- -- 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)
- 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, pDatetime, pUserId, pDatetime, pUserId
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement