PAY_BL_MESSAGE_LINES_VL

Details

• Schema: FUSION

• Object owner: PAY

• Object type: VIEW

Columns

Name
MESSAGE_ID SOURCE_ID MESSAGE_LEVEL LINE_SEQUENCE MESSAGE_TEXT DESCR_COL

Query

SQL_Statement

SELECT PAY_MESSAGE_LINES.MESSAGE_ID MESSAGE_ID, PAY_MESSAGE_LINES.SOURCE_ID SOURCE_ID, PAY_MESSAGE_LINES.MESSAGE_LEVEL MESSAGE_LEVEL, PAY_MESSAGE_LINES.LINE_SEQUENCE LINE_SEQUENCE, decode(instrb(PAY_MESSAGE_LINES.LINE_TEXT,'<TEXT>'),0,PAY_MESSAGE_LINES.LINE_TEXT,substrb(PAY_MESSAGE_LINES.LINE_TEXT,instrb(PAY_MESSAGE_LINES.LINE_TEXT,'<TEXT>')+6,instrb(PAY_MESSAGE_LINES.LINE_TEXT,'</TEXT>')-instrb(PAY_MESSAGE_LINES.LINE_TEXT,'<TEXT>')-6)||substrb(PAY_MESSAGE_LINES.LINE_TEXT,instrb(PAY_MESSAGE_LINES.LINE_TEXT,'<CAUSE>')+7,instrb(PAY_MESSAGE_LINES.LINE_TEXT,'</CAUSE>')-instrb(PAY_MESSAGE_LINES.LINE_TEXT,'<CAUSE>')-7)||substrb(PAY_MESSAGE_LINES.LINE_TEXT,instrb(PAY_MESSAGE_LINES.LINE_TEXT,'<DETAILS>')+9,instrb(PAY_MESSAGE_LINES.LINE_TEXT,'</DETAILS>')-instrb(PAY_MESSAGE_LINES.LINE_TEXT,'<DETAILS>')-9)) as MESSAGE_TEXT, PAY_MESSAGE_LINES.SOURCE_TYPE DESCR_COL FROM PAY_MESSAGE_LINES WHERE PAY_MESSAGE_LINES.SOURCE_TYPE IN ('G','H') AND PAY_MESSAGE_LINES.LINE_TEXT NOT LIKE '<?xml version%' union SELECT PAY_MESSAGE_LINES.MESSAGE_ID MESSAGE_ID, PAY_BATCH_LINES.BATCH_ID SOURCE_ID, PAY_MESSAGE_LINES.MESSAGE_LEVEL MESSAGE_LEVEL, PAY_MESSAGE_LINES.LINE_SEQUENCE LINE_SEQUENCE, PAY_MESSAGE_LINES.LINE_TEXT MESSAGE_TEXT, PAY_MESSAGE_LINES.SOURCE_TYPE DESCR_COL FROM PAY_MESSAGE_LINES, PAY_BATCH_LINES WHERE PAY_MESSAGE_LINES.SOURCE_TYPE IN ('L') AND PAY_MESSAGE_LINES.SOURCE_ID = PAY_BATCH_LINES.BATCH_LINE_ID