Hey Patrick,
A similar case was reported some time ago, the BOE ETL job was not copying all the information from PPM database to BOE database.
Please execute the following SQL in BO schema to bring all requests to RPT_DIM_REQUESTS
declare
l_file_name CONSTANT VARCHAR2(50) := 'load_common_data.sql';
l_module_name CONSTANT VARCHAR2(50) := 'main';
l_func_name CONSTANT VARCHAR2(50) := 'main';
reqdim_start_year_boundry DATE;
begin
reqdim_start_year_boundry := RPT_UTIL.GET_START_BOUNDRY(RPT_CONSTANTS.PRT_PARAM_REQDIM_START_DATE);
-- truncate the table first
execute IMMEDIATE 'truncate table RPT_DIM_REQUESTS';
-- invoke request dimension population script
RPT_POPULATE_DIMENSION.RPT_POPULATE_DIM_REQUEST(reqdim_start_year_boundry);
DBMS_OUTPUT.PUT_LINE('completed populating the Request Dimension table');
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name,$$plsql_line, 'completed populating the Request Dimension table');
DBMS_OUTPUT.PUT_LINE('Gathering statistics for the Request Dimension table '|| ' ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line, 'Gathering statistics for the Request Dimension table');
DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER,tabname=>'RPT_DIM_REQUESTS', force=>TRUE);
DBMS_OUTPUT.PUT_LINE('Finshed gathering statistics for the Request Dimension table '|| ' ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line, 'Finished gathering statistics for the Request Dimension table');
-- Req custom Params Dim Tables : truncate the table first
-- Req Dimention be MUST be popualted before populating following Custom Parameters for Req Header and Req Details Custom parameters.
execute IMMEDIATE 'truncate table RPT_DIM_REQ_HDR_CUSTOM_PARAMS';
execute IMMEDIATE 'truncate table RPT_DIM_REQ_DTL_CUSTOM_PARAMS';
RPT_POPULATE_DIMENSION.RPT_POPULATE_REQ_CUSTOM_PARAMS('H');
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name,$$plsql_line, 'Completed populating Req Header Custom Parameters Dimension table');
DBMS_OUTPUT.PUT_LINE('Gathering statistics for the Req Header Custom Parameters Dimension table '|| ' ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line, 'Gathering statistics for the Req Header Custom Parameters Dimension table');
DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER,tabname=>'RPT_DIM_REQ_HDR_CUSTOM_PARAMS', force=>TRUE);
DBMS_OUTPUT.PUT_LINE('Finshed gathering statistics for the Req Header Custom Parameters Dimension table '|| ' ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line, 'Finished gathering statistics for the Req Header Custom Parameters Dimension table');
RPT_POPULATE_DIMENSION.RPT_POPULATE_REQ_CUSTOM_PARAMS('D');
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name,$$plsql_line, 'Completed populating Req Details Custom Parameters Dimension table');
DBMS_OUTPUT.PUT_LINE('Gathering statistics for the Req Details Custom Parameters Dimension table '|| ' ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line, 'Gathering statistics for the Req Details Custom Parameters Dimension table');
DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER,tabname=>'RPT_DIM_REQ_DTL_CUSTOM_PARAMS', force=>TRUE);
DBMS_OUTPUT.PUT_LINE('Finshed gathering statistics for the Req Details Custom Parameters Dimension table '|| ' ' || TO_CHAR (CURRENT_DATE, 'MON-DD-YYYY HH24:MI:SS'));
rpt_event_util.LOG_INFO_EVENT(l_func_name, l_module_name, l_file_name, $$plsql_line, 'Finished gathering statistics for the Req Details Custom Parameters Dimension table');
end;
Alonso
