Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Function: cb_submit_payment_order_cb(bigint, character varying, character varying)
- -- DROP FUNCTION cb_submit_payment_order_cb(bigint, character varying, character varying);
- CREATE OR REPLACE FUNCTION cb_submit_payment_order_cb(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vPOCbId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- vFlagNo character varying(1);
- vEmptyValue character varying(1);
- vEmptyId bigint;
- vStatusRelease character varying(1);
- BEGIN
- vFlagNo := 'N';
- vEmptyValue := ' ';
- vEmptyId := -99;
- vStatusRelease := 'R';
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'cb_submit_payment_order_cb' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vPOCbId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'paymentOrderId';
- 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';
- INSERT INTO cb_trx_cashbank_balance
- (tenant_id, ou_id, doc_type_id, payment_id, payment_doc_no,
- payment_doc_date, payment_remark, partner_id, partner_bank_id, curr_code,
- amount, due_date, flg_payment, ref_doc_type_id, ref_id,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.payment_order_id, A.doc_no,
- A.doc_date, A.remark, A.partner_id, A.partner_bank_id, A.curr_code,
- SUM(B.add_amount), A.due_date, vFlagNo, vEmptyId, vEmptyId,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM cb_payment_order A, cb_payment_order_cost B
- WHERE A.payment_order_id = vPOCbId
- AND A.payment_order_id = b.payment_order_id
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.payment_order_id, A.doc_no, A.doc_date, A.remark, A.partner_id, A.partner_bank_id, A.curr_code, A.due_date;
- UPDATE cb_payment_order SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- WHERE payment_order_id = vPOCbId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- ALTER FUNCTION cb_submit_payment_order_cb(bigint, character varying, character varying)
- OWNER TO sts;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement