Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Function: cb_submit_cg_realization(bigint, character varying, character varying)
- /**
- * Modified by Adrian, Sep 25, 2017
- * Menambahkan perhitungan cost amount pada saat update cb_cashbank_balance
- */
- DROP FUNCTION cb_submit_cg_realization(bigint, character varying, character varying);
- CREATE OR REPLACE FUNCTION cb_submit_cg_realization(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vDocId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- vEmptyId bigint;
- vStatusRelease character varying(1);
- vStatusDraft character varying(1);
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vActivityCOA character varying(10);
- vCashBankCOA character varying(10);
- vJournalTrxId bigint;
- vCashBankId bigint;
- vDocDate character varying(8);
- vCashBankAmount numeric;
- vStatusAccept character varying(10);
- vStatusReject character varying(10);
- vGroupCoaPiutangCekGiro character varying(20);
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vCostAmount numeric;
- vCBAmount numeric;
- vSystemCOA character varying(10);
- BEGIN
- vEmptyId := -99;
- vStatusRelease := 'R';
- vStatusDraft := 'D';
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vActivityCOA := 'ACTIVITY';
- vCashBankCOA := 'CASHBANK';
- vStatusAccept := 'ACCEPT';
- vStatusReject := 'REJECT';
- vGroupCoaPiutangCekGiro := 'AyatSilangCekGiro';
- vSystemCOA := 'SYSTEM';
- --emptying temp table
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- --get variable
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'cb_submit_cg_realization' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vDocId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'cashbankInId';
- 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';
- --get detail document
- SELECT f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
- FROM cb_in_out_cashbank A
- WHERE A.in_out_cashbank_id = vDocId
- LIMIT 1 INTO result;
- vOuStructure := result.ou;
- vDocJournal := result.doc;
- --get cashbankId
- SELECT cashbank_id INTO vCashBankId
- FROM cb_cheque_giro_realization
- WHERE in_out_cashbank_id = vDocId
- LIMIT 1;
- --insert and then update data cashbank balance
- INSERT INTO cb_cashbank_balance
- (tenant_id, ou_id, cashbank_id, cash_bank_date,
- rec_type, curr_code, amount,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, C.ou_id, B.cashbank_id, A.doc_date,
- vSignDebit, B.curr_code, 0,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM cb_in_out_cashbank A
- INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- INNER JOIN m_cashbank_ou C ON B.cashbank_id = C.cashbank_id
- WHERE A.in_out_cashbank_id = vDocId AND
- B.realization_status = vStatusAccept AND
- NOT EXISTS (SELECT 1 FROM cb_cashbank_balance D
- WHERE A.tenant_id = D.tenant_id AND
- C.ou_id = D.ou_id AND
- B.cashbank_id = D.cashbank_id AND
- A.doc_date = D.cash_bank_date AND
- D.rec_type = vSignDebit)
- GROUP BY A.tenant_id, C.ou_id, B.cashbank_id, A.doc_date, B.curr_code;
- WITH amount AS (
- SELECT A.tenant_id, A.ou_id, A.doc_date, B.cashbank_id, SUM(B.cheque_giro_amount) AS cheque_giro_amount
- FROM cb_in_out_cashbank A
- INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- WHERE A.in_out_cashbank_id = vDocId AND
- B.realization_status = vStatusAccept
- GROUP BY A.tenant_id, A.ou_id, A.doc_date, B.cashbank_id
- )
- UPDATE cb_cashbank_balance D
- SET amount = D.amount + A.cheque_giro_amount,
- version = D.version + 1,
- update_datetime = vDatetime,
- update_user_id = vUserId
- FROM amount A
- WHERE D.tenant_id = A.tenant_id AND
- D.ou_id = A.ou_id AND
- D.cashbank_id = A.cashbank_id AND
- D.cash_bank_date = A.doc_date AND
- D.rec_type = vSignDebit;
- WITH cost_amount AS (
- WITH tt_in_out_cashbank AS(
- SELECT A.in_out_cashbank_id, B.cashbank_id
- FROM cb_in_out_cashbank A
- INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- GROUP BY A.in_out_cashbank_id, B.cashbank_id
- )
- SELECT A.tenant_id, A.ou_id, A.doc_date, B.cashbank_id, SUM(C.cost_amount) AS cost_amount
- FROM cb_in_out_cashbank A
- INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
- WHERE A.in_out_cashbank_id = vDocId
- GROUP BY A.tenant_id, A.ou_id, A.doc_date, B.cashbank_id
- )
- UPDATE cb_cashbank_balance D
- SET amount = D.amount - A.cost_amount,
- version = D.version + 1,
- update_datetime = vDatetime,
- update_user_id = vUserId
- FROM cost_amount A
- WHERE D.tenant_id = A.tenant_id AND
- D.ou_id = A.ou_id AND
- D.cashbank_id = A.cashbank_id AND
- D.cash_bank_date = A.doc_date AND
- D.rec_type = vSignDebit;
- -- update cb_in_out_cashbank : status doc to R
- UPDATE cb_in_out_cashbank
- SET status_doc = vStatusRelease,
- version = version + 1,
- update_datetime = vDatetime,
- update_user_id = vUserId
- WHERE in_out_cashbank_id = vDocId;
- --update data cheque/giro balance yg statusnya ACCEPT
- UPDATE cb_cheque_giro_balance A
- SET flg_realization = 'Y',
- realization_doc_type_id = B.doc_type_id,
- realization_doc_no = B.doc_no,
- realization_doc_date = B.doc_date,
- version = A.version + 1,
- update_datetime = vDatetime,
- update_user_id = vUserId,
- remark = C.remark
- FROM cb_in_out_cashbank B
- INNER JOIN cb_cheque_giro_realization C ON B.in_out_cashbank_id = C.in_out_cashbank_id
- WHERE B.in_out_cashbank_id = vDocId AND
- A.cheque_giro_balance_id = C.ref_balance_id AND
- C.realization_status = vStatusAccept;
- --update data cheque/giro balance yg statusnya REJECT
- UPDATE cb_cheque_giro_balance A
- SET flg_realization = 'N',
- flg_deposit = 'N',
- version = A.version + 1,
- update_datetime = vDatetime,
- update_user_id = vUserId,
- remark = C.remark
- FROM cb_in_out_cashbank B
- INNER JOIN cb_cheque_giro_realization C ON B.in_out_cashbank_id = C.in_out_cashbank_id
- WHERE B.in_out_cashbank_id = vDocId AND
- A.cheque_giro_balance_id = C.ref_balance_id AND
- C.realization_status = vStatusReject;
- /*
- * jurnal :
- * Credit CHEQUE_GIRO
- * Debit CHEQUE_GIRO_COST
- * Debit CASH_BANK
- */
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
- FROM cb_in_out_cashbank A
- WHERE A.in_out_cashbank_id = vDocId;
- SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
- 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, (vDocJournal).journal_type, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date,
- (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, vEmptyId, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.doc_date, A.ref_curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM cb_in_out_cashbank A
- WHERE A.in_out_cashbank_id = vDocId;
- 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, vJournalTrxId, 1,
- A.doc_type_id, A.in_out_cashbank_id,
- B.partner_id, vEmptyId, B.cashbank_id, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id , vGroupCoaPiutangCekGiro), B.curr_code, 0, vEmptyId,
- B.cheque_giro_amount, A.doc_date, vTypeRate,
- 1, 1, 'CHEQUE_GIRO', A.remark
- FROM cb_in_out_cashbank A
- INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- WHERE A.in_out_cashbank_id = vDocId AND
- B.realization_status = vStatusAccept;
- 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, vJournalTrxId, 1,
- A.doc_type_id, B.in_out_cashbank_cost_id,
- vEmptyId, vEmptyId, vEmptyId, B.ou_rc_id,
- B.segment_id, vSignDebit, vActivityCOA, B.activity_gl_id,
- E.coa_id, B.curr_code, 0, vEmptyId,
- B.cost_amount, A.doc_date, vTypeRate,
- 1, 1, 'CHEQUE_GIRO_COST', B.remark
- FROM cb_in_out_cashbank A
- INNER JOIN cb_in_out_cashbank_cost B ON A.in_out_cashbank_id = B.in_out_cashbank_id
- INNER JOIN m_activity_gl E ON B.activity_gl_id = E.activity_gl_id
- WHERE A.in_out_cashbank_id = vDocId;
- -- SUM All Cost
- IF EXISTS(SELECT 1 FROM cb_in_out_cashbank_cost A WHERE A.in_out_cashbank_id = vDocId) THEN
- SELECT SUM(A.cost_amount) INTO vCostAmount
- FROM cb_in_out_cashbank_cost A
- WHERE A.in_out_cashbank_id = vDocId;
- ELSE
- vCostAmount := 0;
- END IF;
- -- SUM All CashBank for Accept Cheque/Giro
- IF EXISTS(SELECT 1 FROM cb_cheque_giro_realization A WHERE A.in_out_cashbank_id = vDocId AND A.realization_status = vStatusAccept) THEN
- SELECT SUM(A.cheque_giro_amount) INTO vCBAmount
- FROM cb_cheque_giro_realization A
- WHERE A.in_out_cashbank_id = vDocId AND
- A.realization_status = vStatusAccept;
- ELSE
- vCBAmount := 0;
- END IF;
- 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, vJournalTrxId, 1,
- A.doc_type_id, A.in_out_cashbank_id,
- vEmptyId, vEmptyId, B.cashbank_id, vEmptyId,
- vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
- B.coa_id, B.curr_code, 0, vEmptyId,
- (vCBAmount - vCostAmount) AS amount, A.doc_date, vTypeRate,
- 1, 1, 'CASH_BANK', A.remark
- FROM cb_in_out_cashbank A, m_cashbank B
- WHERE A.in_out_cashbank_id = vDocId AND
- B.cashbank_id = vCashBankId;
- 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, 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.journal_desc, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- AND A.journal_desc IN ('CHEQUE_GIRO_COST', 'CASH_BANK');
- 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, 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.journal_desc, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- AND A.journal_desc = 'CHEQUE_GIRO';
- --emptying temp table
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement