Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Adrian, Mar 22, 2017
- CREATE OR REPLACE FUNCTION cb_cancel_submit_cg_realization(bigint, character varying, bigint, character varying, bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pDatetime ALIAS FOR $4;
- pDocId ALIAS FOR $5;
- pRemarkApproval ALIAS FOR $6;
- pPrevProcessNo ALIAS FOR $7;
- vDocType bigint;
- vEmptyId bigint;
- vFunctionSubmit character varying;
- vStatusDocReleased character varying;
- vStatusDocDraft character varying;
- vStatusDocInProgress character varying;
- vStatusDocVoid character varying;
- vWorkflowStatusDraft character varying;
- vYes character varying;
- vNo character varying;
- vZero bigint;
- vOuId bigint;
- vJournalTrxId bigint;
- vDocNo character varying;
- vDocDate character varying;
- vScheme character varying;
- vDocJournal DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vLedgerCode character varying;
- vStatusLedgerNotDone character varying;
- vOuStructureJournalItem OU_BU_STRUCTURE%ROWTYPE;
- vRefAmount numeric;
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vActivityCOA character varying(10);
- vRefId bigint;
- vRefDocTypeId bigint;
- vCbInOtherId bigint;
- vPaymentAmountConversion numeric;
- vStatusAccept character varying(10);
- vStatusReject character varying(10);
- vEmpty character varying;
- BEGIN
- vDocType := 625;
- vEmptyId := -99;
- vFunctionSubmit := 'cb_submit_cg_realization';
- vStatusDocReleased := 'R';
- vStatusDocDraft := 'D';
- vStatusDocInProgress := 'I';
- vStatusDocVoid := 'V';
- vWorkflowStatusDraft := 'DRAFT';
- vYes := 'Y';
- vNo := 'N';
- vZero := 0;
- vLedgerCode := 'CASH.BANK';
- vStatusLedgerNotDone := '0';
- vSignDebit := 'D';
- vStatusAccept := 'ACCEPT';
- vStatusReject := 'REJECT';
- vEmpty := '';
- --RAISE EXCEPTION 'Function Submit For Document Cheque Giro Realization is not yet Created';
- -- get data
- select f_get_ou_bu_structure(A.ou_id) AS ou, A.ou_id, A.doc_no, A.doc_date,
- f_get_document_journal(A.doc_type_id) as doc, C.scheme
- FROM cb_in_out_cashbank A, m_document C
- WHERE A.in_out_cashbank_id = pDocId AND
- A.doc_type_id = C.doc_type_id AND
- A.doc_type_id = vDocType AND
- A.status_doc = vStatusDocReleased
- INTO result;
- IF FOUND THEN
- vOuStructure := result.ou;
- vOuId := result.ou_id;
- vDocNo := result.doc_no;
- vDocDate := result.doc_date;
- vDocJournal := result.doc;
- vScheme := result.scheme;
- -- Cek status ledger not yet closed
- IF EXISTS (
- SELECT 1
- FROM m_admin_process_ledger e, m_ou_structure f, t_ou g
- WHERE e.tenant_id = pTenantId
- AND e.ou_id = f.ou_bu_id
- AND f.ou_id = vOuId
- AND e.date_year_month = SUBSTR(vDocDate, 1, 6)
- AND e.ledger_code = vLedgerCode AND
- f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN
- /*
- * validasi flg_realization for accepted cg
- */
- IF NOT EXISTS (
- SELECT (1)
- FROM cb_cheque_giro_balance A, cb_in_out_cashbank B, cb_cheque_giro_realization C
- WHERE B.in_out_cashbank_id = pDocId
- AND B.in_out_cashbank_id = C.in_out_cashbank_id
- AND A.cheque_giro_balance_id = C.ref_balance_id
- AND C.realization_status = vStatusAccept
- AND flg_realization <> vYes
- ) THEN
- /*
- * validasi flg_realization dan flg_deposit for rejected cg
- */
- IF NOT EXISTS (
- SELECT (1)
- FROM cb_cheque_giro_balance A, cb_in_out_cashbank B, cb_cheque_giro_realization C
- WHERE B.in_out_cashbank_id = pDocId
- AND B.in_out_cashbank_id = C.in_out_cashbank_id
- AND A.cheque_giro_balance_id = C.ref_balance_id
- AND C.realization_status = vStatusReject
- AND (
- flg_realization <> vNo
- OR flg_deposit <> vNo
- )
- ) THEN
- --update data cashbank balance
- 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 = pDocId 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 = pDatetime,
- update_user_id = pUserId
- 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;
- -- update cb_in_out_cashbank
- UPDATE cb_in_out_cashbank
- SET status_doc = vStatusDocDraft,
- workflow_status = vWorkflowStatusDraft,
- version = version + 1,
- update_datetime = pDatetime,
- update_user_id = pUserId
- WHERE in_out_cashbank_id = pDocId;
- --update data cheque/giro balance yg statusnya ACCEPT
- WITH old_remark AS (
- SELECT log_deposit_administation_cg_id, remark, cheque_giro_balance_id,
- ROW_NUMBER() OVER(
- PARTITION BY cheque_giro_balance_id
- ORDER BY log_deposit_administation_cg_id DESC
- ) AS row_number
- FROM cb_log_deposit_administration_cheque_giro
- )
- UPDATE cb_cheque_giro_balance A
- SET flg_realization = vNo,
- realization_doc_type_id = vEmptyId,
- realization_doc_no = vEmpty,
- realization_doc_date = vEmpty,
- version = A.version + 1,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- remark = D.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
- INNER JOIN old_remark D ON D.cheque_giro_balance_id = C.ref_balance_id
- WHERE B.in_out_cashbank_id = pDocId AND
- A.cheque_giro_balance_id = C.ref_balance_id AND
- D.row_number = 1 AND
- C.realization_status = vStatusAccept;
- --update data cheque/giro balance yg statusnya REJECT
- WITH old_remark AS (
- SELECT log_deposit_administation_cg_id, remark, cheque_giro_balance_id,
- ROW_NUMBER() OVER(
- PARTITION BY cheque_giro_balance_id
- ORDER BY log_deposit_administation_cg_id DESC
- ) AS row_number
- FROM cb_log_deposit_administration_cheque_giro
- )
- UPDATE cb_cheque_giro_balance A
- SET flg_realization = vNo,
- flg_deposit = vYes,
- version = A.version + 1,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- remark = D.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
- INNER JOIN old_remark D ON D.cheque_giro_balance_id = C.ref_balance_id
- WHERE B.in_out_cashbank_id = pDocId AND
- A.cheque_giro_balance_id = C.ref_balance_id AND
- D.row_number = 1 AND
- C.realization_status = vStatusReject;
- -- PERFORM cancel gl_cancel_admin_journal_trx
- PERFORM gl_cancel_admin_journal_trx(pTenantId, (vOuStructure).ou_bu_id, vOuId, (vDocJournal).journal_type, f_get_year_month_date(vDocDate), 'DAILY', pDatetime, pUserId);
- -- Find journal trx id
- SELECT journal_trx_id INTO vJournalTrxId
- FROM gl_journal_trx
- WHERE tenant_id = pTenantId
- AND journal_type = (vDocJournal).journal_type
- AND doc_type_id = vDocType
- AND doc_id = pDocId
- AND doc_no = vDocNo
- AND doc_date = vDocDate
- AND ou_bu_id = (vOuStructure).ou_bu_id
- AND ou_branch_id = (vOuStructure).ou_branch_id
- AND ou_sub_bu_id = (vOuStructure).ou_sub_bu_id;
- -- DELETE gl_journal_trx
- DELETE FROM gl_journal_trx
- WHERE journal_trx_id = vJournalTrxId;
- -- DELETE gl_journal_trx_item
- DELETE FROM gl_journal_trx_item
- WHERE journal_trx_id = vJournalTrxId;
- -- DELETE gl_journal_trx_mapping
- DELETE FROM gl_journal_trx_mapping
- WHERE journal_trx_id = vJournalTrxId;
- -- PERFORM f_reset_approval_to_draft
- PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pDocId, vDocNo, pDatetime, pRemarkApproval);
- -- UPDATE t_process_message
- UPDATE t_process_message
- SET process_no = pPrevProcessNo,
- update_datetime = pDatetime,
- update_user_id = pUserId,
- version = version + 1
- WHERE tenant_id = pTenantId
- AND process_name = vFunctionSubmit
- AND process_no = pDocId || '_' || vDocNo;
- ELSE
- RAISE EXCEPTION 'Item in document with id % is already used for other documnet', pDocId;
- END IF;
- ELSE
- RAISE EXCEPTION 'Item in document with id % is already used for other documnet', pDocId;
- END IF;
- ELSE
- RAISE EXCEPTION 'Admin Process Ledger for Cashbank in year month % is already closed', SUBSTR(vDocDate, 1, 6);
- END IF;
- ELSE
- RAISE EXCEPTION 'Document with id % is not found or is on approval progress', pDocId;
- END IF;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement