VRM_EXCEPTION_LIST_V

Details

• Schema: FUSION

• Object owner: VRM

• Object type: VIEW

Columns

Name
DOCUMENT_LINE_ID ERROR_MESSAGE_NAME HEADERFLAG

Query

SQL_Statement

SELECT document_line_id, TRANSLATE (LTRIM (text, '/'), '*/', '*,') error_message_name, 'N' headerflag FROM (SELECT ROW_NUMBER () OVER (PARTITION BY document_line_id ORDER BY document_line_id, lvl DESC) rn, document_line_id, text FROM (SELECT document_line_id, LEVEL lvl, SYS_CONNECT_BY_PATH (error_message_name,'/') text FROM (SELECT document_line_id, error_message_name, ROW_NUMBER () OVER (PARTITION BY document_line_id ORDER BY document_line_id,error_message_name) x FROM (SELECT DISTINCT error_line_id as document_line_id, message_text AS error_message_name FROM vrm_revenue_document_errors WHERE error_line_level = 'L' ) ORDER BY document_line_id, error_message_name ) a CONNECT BY document_line_id = PRIOR document_line_id AND x - 1 = PRIOR x ) ) WHERE rn = 1 UNION ALL SELECT document_line_id, TRANSLATE (LTRIM (text, '/'), '*/', '*,') error_message_name, 'Y' headerflag FROM (SELECT ROW_NUMBER () OVER (PARTITION BY document_line_id ORDER BY document_line_id, lvl DESC) rn, document_line_id, text FROM (SELECT document_line_id, LEVEL lvl, SYS_CONNECT_BY_PATH (error_message_name,'/') text FROM (SELECT document_line_id, error_message_name AS error_message_name, ROW_NUMBER () OVER (PARTITION BY document_line_id ORDER BY document_line_id,error_message_name) x FROM (SELECT DISTINCT error_line_id as document_line_id, message_text AS error_message_name FROM vrm_revenue_document_errors WHERE error_line_level = 'H' ) ORDER BY document_line_id, error_message_name ) a CONNECT BY document_line_id = PRIOR document_line_id AND x - 1 = PRIOR x ) ) WHERE rn = 1 ORDER BY document_line_id