cb_submit_payment_order_cb

-- 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;