Search This Blog

Saturday, November 25, 2017

Fusion HDL Error Analysis

Fusion Error Analysis Summary:

SELECT '1' KEY, ucm_content_id, data_set_name, imported_status,
       transfer_status,
       TO_CHAR (ds.last_update_date,
                'dd-mon-rrrr hh24:mi:ss'
               ) batch_submission_date,
       import_lines_total_count, import_error_count, loaded_count,
       error_count
  FROM hrc_dl_data_sets ds
 WHERE ucm_content_id =  'UCMFA00009281'
/* (SELECT MAX (ucm_content_id)
                           FROM hrc_dl_data_sets hdd
                          WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
        AND hdd.LOADED_STATUS = 'ERROR')*/

Fusion Error Analysis Details :
SELECT  DISTINCT ds.ucm_content_id ucm_content_id1
                ,fr.key_source_owner data_source1
                ,hdbo.bus_obj_file_discriminator bo
                ,ds.data_set_name file_name
                ,TO_CHAR (ds.last_update_date
                         ,'dd-mon-rrrr hh24:mi:ss')
                    batch_submission_date1
                ,l.seq_num
                ,fr.key_source_id source_id
                ,INITCAP (ll.imported_status) import_status
                ,INITCAP (REPLACE (ll.validated_loaded_status
                                  ,'_'
                                  ,' '))
                    loaded_status
                , (SELECT  fhl.text
                   FROM    hrc_dl_file_lines fhl
                   WHERE   fhl.line_id = fh.line_id)
                    metadata
                ,ml.msg_text error_msg
                ,l.text error_data
FROM    hrc_dl_logical_lines ll
       ,hrc_dl_file_rows fr
       ,hrc_dl_message_lines ml
       ,hrc_dl_data_set_bus_objs dsbo
       ,hrc_dl_data_sets ds
       ,hrc_dl_file_lines l
       ,hrc_dl_business_objects hdbo
       ,hrc_dl_file_headers fh
WHERE       ucm_content_id = 'UCMFA00009281'
        /* (SELECT MAX (ucm_content_id)
                                   FROM hrc_dl_data_sets hdd
                                  WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
                                  AND hdd.LOADED_STATUS = 'ERROR')*/
        AND ds.data_set_id = dsbo.data_set_id
        AND ml.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ll.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ml.message_source_table_name = 'HRC_DL_LOGICAL_LINES'
        AND ml.message_source_line_id = ll.logical_line_id
        AND ll.logical_line_id = fr.logical_line_id
        AND fr.line_id = l.line_id
        AND fr.header_id = fh.header_id
        AND hdbo.business_object_id = dsbo.business_object_id
-- AND ds.transfer_status = 'ERROR'
UNION
SELECT  DISTINCT ds.ucm_content_id
                ,fr.key_source_owner data_source
                ,hdbo.bus_obj_file_discriminator bo
                ,ds.data_set_name file_name
                ,TO_CHAR (ds.last_update_date
                         ,'dd-mon-rrrr hh24:mi:ss')
                    batch_submission_date
                ,l.seq_num
                ,fr.key_source_id source_id
                ,INITCAP (ll.imported_status) import_status
                ,INITCAP (REPLACE (ll.validated_loaded_status
                                  ,'_'
                                  ,' '))
                    loaded_status
                , (SELECT  fhl.text
                   FROM    hrc_dl_file_lines fhl
                   WHERE   fhl.line_id = fh.line_id)
                    metadata
                ,ml.msg_text error_msg
                ,l.text error_data
FROM    hrc_dl_physical_lines ll
       ,hrc_dl_file_rows fr
       ,hrc_dl_message_lines ml
       ,hrc_dl_data_set_bus_objs dsbo
       ,hrc_dl_data_sets ds
       ,hrc_dl_file_lines l
       ,hrc_dl_file_headers fh
       ,hrc_dl_business_objects hdbo
WHERE       ucm_content_id =  'UCMFA00009281'
        /* (SELECT MAX (ucm_content_id)
                             FROM hrc_dl_data_sets hdd
                            WHERE TRUNC (SYSDATE) = TRUNC (hdd.creation_date)
                            AND hdd.LOADED_STATUS = 'ERROR')*/
        AND ds.data_set_id = dsbo.data_set_id
        AND ml.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ll.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
        AND ml.message_source_table_name = 'HRC_DL_PHYSICAL_LINES'
        AND ml.message_source_line_id = ll.physical_line_id
        AND ll.logical_line_id = fr.logical_line_id
        AND fr.line_id = l.line_id
        AND fr.header_id = fh.header_id
        AND hdbo.business_object_id = dsbo.business_object_id

 

No comments:

Post a Comment