Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Adrian, Mar 21, 2017
- CREATE OR REPLACE FUNCTION cb_cancel_submit_cg_void(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;
- pCGVoidId ALIAS FOR $5;
- pRemarkApproval ALIAS FOR $6;
- pPrevProcessNo ALIAS FOR $7;
- vEmptyId bigint;
- vCGVoidDocTypeId bigint;
- vFlagNo character varying(1);
- vRoundingModeNonTax character varying(5);
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vFunctionSubmit character varying;
- vStatusDocReleased character varying(1);
- vStatusDocDraft character varying;
- vStatusDocInProgress character varying;
- vStatusDocVoid character varying(1);
- vWorkflowStatusDraft character varying;
- vPoId bigint;
- vZero bigint;
- vOuId bigint;
- vScheme character varying;
- vDocNo character varying;
- vEmpty character varying;
- vYes character varying;
- vNo character varying;
- BEGIN
- vEmptyId := -99;
- vFlagNo := 'N';
- vCGVoidDocTypeId := 629;
- vFunctionSubmit := 'cb_submit_cg_void';
- vStatusDocReleased := 'R';
- vStatusDocDraft := 'D';
- vStatusDocInProgress = 'I';
- vStatusDocVoid := 'V';
- vWorkflowStatusDraft := 'DRAFT';
- vZero := 0;
- vEmpty := '';
- vYes := 'Y';
- vNo := 'N';
- --RAISE EXCEPTION 'Function Submit For Document Cheque Giro Void 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 = pCGVoidId AND
- A.doc_type_id = C.doc_type_id AND
- A.doc_type_id = vCGVoidDocTypeId AND
- A.status_doc = vStatusDocReleased
- INTO result;
- IF FOUND THEN
- vOuStructure := result.ou;
- vOuId := result.ou_id;
- vDocNo := result.doc_no;
- vScheme := result.scheme;
- /*
- * validasi flg_deposit harus N dan flg_realization harus Y
- */
- IF NOT EXISTS (
- SELECT (1)
- FROM cb_cheque_giro_balance A, cb_cheque_giro_void B, cb_in_out_cheque_giro_payment C
- WHERE B.in_out_cashbank_id = pCGVoidId
- AND B.ref_id = C.in_out_cheque_giro_payment_id
- AND A.tenant_id = pTenantId
- AND A.in_out_cheque_giro_payment_id = C.in_out_cheque_giro_payment_id
- AND (
- A.flg_deposit <> vNo
- OR A.flg_realization <> vYes
- )
- ) THEN
- /*
- * validasi flg_alloc harus V
- */
- IF NOT EXISTS (
- SELECT (1)
- FROM fi_receipt_ar_balance A, cb_cheque_giro_void B, cb_in_out_cheque_giro_payment C
- WHERE B.in_out_cashbank_id = pCGVoidId
- AND B.ref_id = C.in_out_cheque_giro_payment_id
- AND A.receipt_ar_balance_id = C.in_out_cheque_giro_payment_id
- AND A.flg_alloc <> vStatusDocVoid
- ) THEN
- --update data cheque/giro balance
- UPDATE cb_cheque_giro_balance A
- SET flg_realization = vNo,
- version = A.version + 1,
- update_datetime = pDatetime,
- update_user_id = pUserId
- FROM cb_cheque_giro_void B
- INNER JOIN cb_in_out_cheque_giro_payment C ON B.ref_id = C.in_out_cheque_giro_payment_id
- WHERE A.tenant_id = pTenantId AND
- A.in_out_cheque_giro_payment_id = C.in_out_cheque_giro_payment_id AND
- B.in_out_cashbank_id = pCGVoidId;
- -- 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 = pCGVoidId;
- -- update data AR Balance
- UPDATE fi_receipt_ar_balance A
- SET flg_alloc = vNo,
- remark = D.remark,
- version = A.version + 1,
- update_datetime = pDatetime,
- update_user_id = pUserId
- FROM cb_cheque_giro_void B
- INNER JOIN cb_in_out_cheque_giro_payment C ON B.ref_id = C.in_out_cheque_giro_payment_id
- INNER JOIN cb_in_out_cashbank D ON D.in_out_cashbank_id = C.in_out_cashbank_id
- WHERE B.in_out_cashbank_id = pCGVoidId AND
- A.receipt_ar_balance_id = C.in_out_cheque_giro_payment_id AND
- A.flg_alloc = vStatusDocVoid;
- -- PERFORM f_reset_approval_to_draft
- PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pCGVoidId, 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 = pCGVoidId || '_' || vDocNo;
- ELSE
- RAISE EXCEPTION 'Document with id % is already allocated', pCGVoidId;
- END IF;
- ELSE
- RAISE EXCEPTION 'Document with id % is already used in other document', pCGVoidId;
- END IF;
- ELSE
- RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pCGVoidId;
- END IF;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement