HDL Error Details

Error Stats

SELECT

    ucm_content_id,

    data_set_id,

    data_set_name,

    imported_status,

    loaded_status,

    import_lines_success_count,

    import_lines_error_count,

    import_lines_total_count,

    import_success_count,

    import_error_count,

    loaded_count,

    error_count,

    request_id

FROM

    hrc_dl_data_sets

WHERE

    ucm_content_id in (:p_content_id)

Error Lines in HDL format

SELECT *
FROM (
SELECT err.orderby
, err.err_location
, err.message_type
, err.msg_text
, err.data_set_name
, err.data_file_name
, err.ucm_content_id
, DECODE(NVL(:P_ENABLE_TRACE,’N’),’Y’,err.stack_trace,NULL) STACK_TRACE
, err.ui_user_key
, SUBSTR(err.metadata,10,INSTR(err.metadata,’|’,1,2)-INSTR(err.metadata,’|’,1,1)-1) BusinessObject
, SUBSTR(err.file_line,INSTR(err.file_line,’|’,-1,1)+1,LENGTH(err.file_line)-INSTR(err.file_line,’|’,-1,1)) SourceSystemId,err.file_line
FROM
(SELECT 1 orderby
, ‘Zip File’ err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number(null) seq_num
, ” ui_user_key
, ” metadata
, ” file_line
, ds.request_id
, ds.data_set_name
, ” data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = ‘HRC_DL_DATA_SETS’
AND l.message_source_line_id = ds.data_set_id
UNION
SELECT 2 orderby
, ‘Data File’ err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number(”) seq_num
, ” ui_user_key
, ” metadata
, ” file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = ‘HRC_DL_DATA_SET_BUS_OBJS’
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
UNION
SELECT 3 orderby
, ‘Import’ err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, ” ui_user_key
, ” metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = ‘HRC_DL_FILE_LINES’
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fl.line_id = l.message_source_line_id
UNION
— File Headers
SELECT 4 orderby
, ‘METADATA’ err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, ” ui_user_key
, fl.text metadata
, ” file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_headers fh
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = ‘HRC_DL_FILE_HEADERS’
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fh.header_id = l.message_source_line_id
AND fl.line_id = fh.line_id
union
— file rows
SELECT 5 orderby
, ‘Hierarchy’ err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, ” ui_user_key
, (SELECT hl.text
FROM hrc_dl_file_lines hl
, hrc_dl_file_headers fh
WHERE fh.header_id= fr.header_id
AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = ‘HRC_DL_FILE_ROWS’
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fr.row_id = l.message_source_line_id
AND fl.line_id = fr.line_id
UNION
SELECT 6 orderby
, ‘Logical Object’ err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, ll.ui_user_key ui_user_key
, (SELECT hl.text
FROM hrc_dl_file_lines hl
, hrc_dl_file_headers fh
WHERE fh.header_id= fr.header_id
AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_logical_lines ll
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = ‘HRC_DL_LOGICAL_LINES’
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND ll.logical_line_id = l.message_source_line_id
AND fr.logical_line_id = ll.logical_line_id
AND fl.line_id = fr.line_id
UNION
SELECT 7 orderby
, ‘Physical Row’ err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, pl.ui_user_key ||’ ‘||pl.ui_date_from||’ ‘ ||pl.ui_date_to ui_user_key
, (SELECT hl.text
FROM hrc_dl_file_lines hl
, hrc_dl_file_headers fh
WHERE fh.header_id= fr.header_id
AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_physical_lines pl
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = ‘HRC_DL_PHYSICAL_LINES’
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND pl.physical_line_id = l.message_source_line_id
AND fr.row_id = pl.row_id
AND fl.line_id = fr.line_id
UNION
SELECT distinct 8 orderby
, ‘Service Error’ err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number(null)
, ”
, ” metadata
, ” file_line
, ds.request_id
, ”
, ” data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_service_requests sr
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = ‘HRC_DL_SERVICE_REQUESTS’
AND l.message_source_line_id = sr.service_call_id) err
WHERE 1=1
AND ucm_content_id=:P_CONTENT_ID
/*AND (DATA_FILE_NAME IN (:P_BUSINESS_OBJECT||’.dat’) OR LEAST(:P_BUSINESS_OBJECT) IS NULL)*/
AND ((SUBSTR(DATA_FILE_NAME,1,INSTR(DATA_FILE_NAME,’.’,1)-1) IN(:P_BUSINESS_OBJECT)) OR LEAST(:P_BUSINESS_OBJECT) IS NULL )
)
WHERE 1=1
AND msg_text like NVL(:P_MESSAGE_TEXT, ‘%’)
ORDER BY 1,4,5,6

List of Values: 

Content ID:

SELECT ucm_content_id||’ – ‘||Data_set_name as content,ucm_content_id
FROM HRC_DL_DATA_SETS ORDER BY LAST_UPDATE_DATE DESC

Business Object:

Select distinct SUBSTR(DSBO.DATA_FILE_NAME,1,INSTR(DSBO.DATA_FILE_NAME,’.’)-1) BO
FROM HRC_DL_DATA_SETS DS,
HRC_DL_DATA_SET_BUS_OBJS DSBO,
HRC_DL_BUSINESS_OBJECTS BO
WHERE
DS.UCM_CONTENT_ID =:P_CONTENT_ID
AND DS.DATA_SET_ID=DSBO.DATA_SET_ID
AND BO.BUSINESS_OBJECT_ID=DSBO.BUSINESS_OBJECT_ID