INSERT ALL
  WHEN FLAG_CREATE_STREAM = 1 THEN
  INTO EMSG_STREAM VALUES (EMSG_STREAM_SEQ.NEXTVAL, EMSG_CAUSE, NULL, CLIENT_ID)
  WHEN FLAG_CREATE_STREAM > 0 THEN
  INTO EMSG VALUES (EMSG_STREAM_SEQ.CURRVAL, EMSG_SEQ.NEXTVAL, EMSG_TYPE, EMSG_TYPE_SUB, EMSG_DATE, EMSG_ADDRESS, EMSG_BODY, EMSG_STATUS)

SELECT...

, SEQ AS ( 

SELECT
  msg.EMSG_TYPE,
  msg.CLIENT_ID,
  ROW_NUMBER() OVER (PARTITION BY msg.CLIENT_ID ORDER BY msg.CLIENT_ID, msg.EMSG_TYPE) GRNUM
FROM 
  MSG_LIST msg,
  CLIENT_PROPERTY cp
WHERE msg.CLIENT_ID = cp.CLIENT_ID
)


SELECT 
  CLIENT_ID,
  GRNUM,
  CASE WHEN GRNUM = 1 THEN EMSG_STREAM_SEQ.NEXTVAL
       ELSE EMSG_STREAM_SEQ.NEXTVAL - GRNUM + 1
  END AS SSS
  
FROM SEQ