HDL - Load Report - BI Query

/* G_1 */

SELECT HDS.UCM_CONTENT_ID AS UCM_ID
    , HDS.DATA_SET_ID
    , HDS.DATA_SET_NAME AS FLOW_NAME
    , HDS.CREATION_DATE
    , BOJS.BUSINESS_OBJECT_ID
    , BOJS.DATA_SET_BUS_OBJ_ID
    , BOJS.IMPORT_SUCCESS_COUNT AS TOTAL_SENT_TO_HCM
    , BOJS.LOADED_COUNT AS SUCCESS
    , BOJS.ERROR_COUNT AS ERROR
FROM HRC_DL_DATA_SETS HDS
    , HRC_DL_DATA_SET_BUS_OBJS BOJS
    , HRC_DL_BUSINESS_OBJECTS BO
WHERE 1=1
    AND HDS.DATA_SET_NAME LIKE '%FLOW_NAME%'
    AND TRUNC(HDS.CREATION_DATE) BETWEEN :FromDate AND :ToDate
    AND HDS.ERROR_COUNT <> 0
    AND BOJS.DATA_SET_ID = HDS.DATA_SET_ID
    AND BO.BUSINESS_OBJECT_ID = BOJS.BUSINESS_OBJECT_ID
    AND BO.BUS_OBJ_FILE_DISCRIMINATOR = 'ElementEntry'
ORDER BY HDS.CREATION_DATE DESC 

/* G_2 & G_3*/

SELECT DFH.DATA_SET_BUS_OBJ_ID,
    DFR.TRANSFER_STATUS,
    DFR.IMPORT_STATUS,
    DFR.LINE_OPERATION,
--    DFR.ROW_ID,   
    DFR.LOGICAL_LINE_ID,
    DFR_C.LOGICAL_LINE_ID C_LINE_ID,
    DFR_C.PVAL006 ASSIGNMENT_NUMBER,
    DFR_C.PVAL008 ELEMENT_NAME,
    DFR_C.PVAL015 SEQ_NUM,
    DFR_C.PVAL003 INPUT_TYPE,
    DFR_C.PVAL_SECURE001 TL_QUANTITY,
    DFR_C.PVAL010 LDG,
    DFR_C.P_EFFECTIVE_START_DATE EFF_START_DT,
    DFR_C.P_EFFECTIVE_END_DATE EFF_END_DT,
    DLL.PARENT_LOGICAL_LINE_ID PARENT_LINE_ID,
    DLL.IMPORTED_STATUS,
    DECODE(DLL.VALIDATED_LOADED_STATUS, 'LOADED_SUCCESS', 'SUCCESS', 'ERROR') VALIDATED_LOADED_STATUS,
    DLL.CHUNK_ID,
    DLL.VO_MAPPING_ID,
    DLL.REQUEST_ID,
    DLL.UI_USER_KEY,
    
    CASE WHEN DML2.MESSAGE_SOURCE_TABLE_NAME IS NULL THEN DML3.MESSAGE_SOURCE_TABLE_NAME ELSE DML2.MESSAGE_SOURCE_TABLE_NAME END MESSAGE_SOURCE_TABLE_NAME,
    CASE WHEN DML2.MESSAGE_SOURCE_TABLE_NAME IS NULL THEN DML3.ORIGINATING_PROCESS ELSE DML2.ORIGINATING_PROCESS END ORIGINATING_PROCESS,
    CASE WHEN DML2.MESSAGE_SOURCE_TABLE_NAME IS NULL THEN DML3.GENERATED_BY ELSE DML2.GENERATED_BY END GENERATED_BY,
    CASE WHEN DML2.MESSAGE_SOURCE_TABLE_NAME IS NULL THEN DML3.MESSAGE_TYPE ELSE DML2.MESSAGE_TYPE END MESSAGE_TYPE,
    CASE WHEN DML2.MESSAGE_SOURCE_TABLE_NAME IS NULL THEN DML3.MSG_TEXT ELSE DML2.MSG_TEXT END MSG_TEXT,
    CASE WHEN DML2_C.MESSAGE_SOURCE_TABLE_NAME IS NULL THEN DML3_C.MSG_TEXT ELSE DML2_C.MSG_TEXT END MSG_TEXT_C,
    CASE WHEN DML2.MSG_TEXT IS NULL THEN DML3.MESSAGE_SOURCE_LINE_ID ELSE DML2.MESSAGE_SOURCE_LINE_ID END MESSAGE_SOURCE_LINE_ID
FROM HRC_DL_FILE_HEADERS DFH,
    HRC_DL_VO_MAPS VM,
    HRC_DL_BUSINESS_OBJECTS DBO,
    --HRC_DL_MESSAGE_LINES DML,
--    HRC_DL_STG_FILE_ROWS DFR,
    HRC_DL_FILE_ROWS DFR,
    --LOGICAL ERROR
--    HRC_DL_STG_LOGICAL_LINES DLL,
    HRC_DL_LOGICAL_LINES DLL,
    HRC_DL_MESSAGE_LINES DML2,
    --PHYSICAL ERROR
--    HRC_DL_STG_PHYSICAL_LINES DPL,
    HRC_DL_PHYSICAL_LINES DPL,
    HRC_DL_MESSAGE_LINES DML3,
    --CHILD RECORD
--    HRC_DL_STG_FILE_ROWS DFR_C,
    HRC_DL_FILE_ROWS DFR_C,
--    HRC_DL_STG_LOGICAL_LINES DLL_C,
    HRC_DL_LOGICAL_LINES DLL_C,
    HRC_DL_MESSAGE_LINES DML2_C,
--    HRC_DL_STG_PHYSICAL_LINES DPL_C,
    HRC_DL_PHYSICAL_LINES DPL_C,
    HRC_DL_MESSAGE_LINES DML3_C
    
WHERE DFH.PARENT_BUSINESS_OBJECT_ID IS NULL
    AND VM.VO_MAPPING_ID = DFH.VO_MAPPING_ID
    AND DBO.BUSINESS_OBJECT_ID = VM.BUSINESS_OBJECT_ID
    AND DFR.HEADER_ID = DFH.HEADER_ID
    AND DFR.PARENT_LOGICAL_LINE_ID = 0
    AND DLL.LOGICAL_LINE_ID = DFR.LOGICAL_LINE_ID
    AND DLL.VALIDATED_LOADED_STATUS = 'LOADED_ERROR'
    AND DML2.MESSAGE_SOURCE_LINE_ID(+) = DLL.LOGICAL_LINE_ID
    AND DPL.LOGICAL_LINE_ID(+) = DLL.LOGICAL_LINE_ID
    AND DML3.MESSAGE_SOURCE_LINE_ID(+) = DPL.PHYSICAL_LINE_ID
    AND DFR_C.PARENT_LOGICAL_LINE_ID(+) = DFR.LOGICAL_LINE_ID
    AND DLL_C.LOGICAL_LINE_ID(+) = DFR_C.LOGICAL_LINE_ID
    AND DML2_C.MESSAGE_SOURCE_LINE_ID(+) = DLL_C.LOGICAL_LINE_ID
    AND DPL_C.LOGICAL_LINE_ID(+) = DLL_C.LOGICAL_LINE_ID
    AND DML3_C.MESSAGE_SOURCE_LINE_ID(+) = DPL_C.PHYSICAL_LINE_ID

/* G_4 */

SELECT HDS.UCM_CONTENT_ID AS UCM_ID
    , HDS.DATA_SET_ID
    , HDS.DATA_SET_NAME AS FLOW_NAME
    , HDS.CREATION_DATE
    , BOJS.BUSINESS_OBJECT_ID
    , BOJS.DATA_SET_BUS_OBJ_ID
    , BOJS.IMPORT_SUCCESS_COUNT AS TOTAL_SENT_TO_HCM
    , BOJS.LOADED_COUNT AS SUCCESS
    , BOJS.ERROR_COUNT AS ERROR
FROM HRC_DL_DATA_SETS HDS
    , HRC_DL_DATA_SET_BUS_OBJS BOJS
    , HRC_DL_BUSINESS_OBJECTS BO
WHERE 1=1
    AND HDS.DATA_SET_NAME LIKE '%FLOW_NAME%'
    AND TRUNC(HDS.CREATION_DATE) BETWEEN :FromDate AND :ToDate
--    AND HDS.ERROR_COUNT <> 0
    AND BOJS.DATA_SET_ID = HDS.DATA_SET_ID
    AND BO.BUSINESS_OBJECT_ID = BOJS.BUSINESS_OBJECT_ID
    AND BO.BUS_OBJ_FILE_DISCRIMINATOR = 'ElementEntry'
ORDER BY HDS.CREATION_DATE DESC

 

/* G_10 */

 SELECT FND_GLOBAL.USER_NAME AS "USER_NAME"
, TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MM PM') AS "RUNTIME"
 FROM DUAL