Setting Financial Close Management Views on Oracle Databases Only

If you are planning to use the Financial Close Management dashboards, you must create the following views:

FCC_DELAYED_TASK_PREDECESSORS – This view is used in the Schedule Roadblocks report to obtain a list of predecessor tasks for tasks that have one or more succeeding tasks that are late.
FCC_FAN_OUT_BOTTLENECK – This view is used in the Schedule Roadblocks report to obtain counts of all sucessor tasks (fan-out count), even if they are not late.
FCC_DELAY_BOTTLENECK – This view is used in the Schedule Roadblocks report to identify the sum of all played tasks.
FCC_PRIOR_PERIOD_ANALYSIS – This view is used in the Schedule Comparison report to obtain the Schedule ID, Schedule workdays, Tasks Complete till day, and TotalTasks in Schedule.
FCC_SCHEDULE_COMPARISON – This view is used in the Schedule Comparison report, and provides a union of the data from the FCC_PRIOR_PERIOD_ANALYSIS with the FCM_OFMA table, in the event that there are no workdays.

Caution!

If you copy the script from the document PDF, the script may run on to the next page. Ensure that you capture all lines for the selected script, and that you DO NOT capture the document footer or page number when copying, as shown in the screen capture below; otherwise, the script will fail:

When copying the script from the document PDF, ensure you capture all lines in the script, and do not capture the document footer and page number.

The views are created in an Oracle database, using any tool, such as SQL Developer. The views are displayed in the Physical Layer in the Oracle BI Administration Tool. For additional information on creating views, see the Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

Caution!

Only Oracle databases are supported for this release

To add Financial Close Management Views:

Using SQL Developer, connect to the Oracle Hyperion Financial Close Management database.

Create the FCC_DELAYED_TASK_PREDECESSORS view, as follows:

CREATE OR REPLACE FORCE VIEW "FCC_DELAYED_TASK_PREDECESSORS" ("PREDECESSOR_TASK_ID") AS
 select DISTINCT pred.predecessor_task_id
 from fcc_tasks successor_task
 INNER JOIN fcc_deployments depl1 ON successor_task.source_id = depl1.deployment_id
 inner join fcc_task_predecessors pred on successor_task.task_id = pred.task_id
where depl1.status = 'OPEN'
 and successor_task.scheduled_start_date < sysdate
 and successor_task.status_id = 32
 and pred.predecessor_task_type = 'FINISH-TO-START';

Create the FCC_FAN_OUT_BOTTLENECK view, as follows:

CREATE OR REPLACE FORCE VIEW "FCC_FAN_OUT_BOTTLENECK" ("PREDECESSOR_TASK_ID", "FAN_OUT") AS 
  select p1.predecessor_task_id, count(*) as "fan-out"
  from fcc_task_predecessors p1
where p1.predecessor_task_id in (select PREDECESSOR_TASK_ID from fcc_delayed_task_predecessors)
  and p1.predecessor_task_type = 'FINISH-TO-START'
GROUP by p1.predecessor_task_id;

Create the FCC_DELAY_BOTTLENECK view, as follows:

CREATE OR REPLACE FORCE VIEW "FCC_DELAY_BOTTLENECK" ("PREDECESSOR_TASK_ID", "TOTAL_DELAY_IN_DAYS") AS 
  select p1.predecessor_task_id, sum(sysdate - successor_task.scheduled_start_date) as total_delay_in_days
  from  fcc_task_predecessors p1
inner join fcc_tasks successor_task on p1.task_id = successor_task.task_id
where p1.predecessor_task_id in (select PREDECESSOR_TASK_ID from fcc_delayed_task_predecessors)
  and sysdate - successor_task.scheduled_start_date > 0
  and p1.predecessor_task_type = 'FINISH-TO-START'
GROUP by p1.predecessor_task_id;

Create the FCC_PRIOR_PERIOD_ANALYSIS view, as follows:

CREATE OR REPLACE FORCE VIEW "FCC_PRIOR_PERIOD_ANALYSIS" ("DEPLOYMENT_ID", "COMPLETED_TASKS", "TOTAL_TASKS", "DAYS_FRM_ZERO_DATE")
 AS    
SELECT dc.deployment_id as DEPLOYMENT_ID, 
SUM(tc.taskCompleted) as COMPLETED_TASKS ,
dc.total as TOTAL_TASKS ,
(TRUNC(ft.ACTUAL_END_DATE) - TRUNC(dc.day_zero_date)) AS DAYS_FRM_ZERO_DATE
   FROM
     (SELECT DISTINCT TRUNC(ACTUAL_END_DATE) AS actual_end_date,       source_id
     FROM fcc_tasks
     ) ft,
     (SELECT deployment_id,
       (SELECT COUNT(1) FROM fcc_tasks fct WHERE fcd.deployment_id= fct.source_id
       ) total,
       day_zero_date
     FROM fcc_deployments fcd
     GROUP BY deployment_id ,
       day_zero_date     
) dc ,
     (SELECT COUNT(1) taskCompleted ,
       fccd.deployment_id,
       (TRUNC(fcct.ACTUAL_END_DATE) - TRUNC(fccd.day_zero_date)) taskday
     FROM fcc_tasks fcct,
       fcc_deployments fccd
     WHERE fccd.deployment_id= fcct.source_id
     GROUP BY fccd.deployment_id,       
(TRUNC(fcct.ACTUAL_END_DATE) - TRUNC(fccd.day_zero_date))
     ) tc
   WHERE dc.deployment_id= ft.source_id
   AND tc.deployment_id  = ft.source_id
   AND ( tc.taskday <= (TRUNC(ft.ACTUAL_END_DATE) -TRUNC(dc.day_zero_date)))
   GROUP BY dc.deployment_id,
     dc.total,
     (TRUNC(ft.ACTUAL_END_DATE) - TRUNC(dc.day_zero_date))
   ORDER BY deployment_id ,
     (TRUNC(ft.ACTUAL_END_DATE) -TRUNC(dc.day_zero_date));

Create the FCC_SCHEDULE_COMPARISON view, as follows:

CREATE OR REPLACE FORCE VIEW "FCC_SCHEDULE_COMPARISON" ("WORK_DAYS", "COMPLETED_TASKS", "TOTAL_TASKS", "DEPLOYMENT_ID")
AS
  SELECT WORKDAYS WORK_DAYS,
    NVL(
    (SELECT NVL(completed_tasks, 0)
    FROM fcc_prior_period_analysis
    WHERE fcc_prior_period_analysis.deployment_id = FCD.deployment_id
    AND fcc_prior_period_analysis.days_frm_zero_date = WORKDAYS
    ) ,(
    (SELECT COUNT(1) taskCompleted
    FROM fcc_tasks fcct,
      fcc_deployments fccd
    WHERE fccd.deployment_id = FCD.deployment_id
    AND fccd.deployment_id = fcct.source_id
    AND (TRUNC(FCCT.ACTUAL_END_DATE) - TRUNC(fccd.day_zero_date)) <= WORKDAYS
    ) )) COMPLETE_TASKS,
    NVL(
    (SELECT NVL(TOTAL_tasks, 0)
    FROM fcc_prior_period_analysis
    WHERE fcc_prior_period_analysis.deployment_id = FCD.deployment_id
    AND fcc_prior_period_analysis.days_frm_zero_date = WORKDAYS
    ) ,
    (SELECT NVL(TOTAL_tasks, 0)
    FROM fcc_prior_period_analysis
    WHERE fcc_prior_period_analysis.deployment_id = FCD.deployment_id
    AND ROWNUM <2
    ) ) TOTAL_TASKS ,
    ( NVL(
    (SELECT NVL(deployment_id, fcd.deployment_id)
    FROM fcc_prior_period_analysis
    WHERE fcc_prior_period_analysis.deployment_id = FCD.deployment_id
    AND fcc_prior_period_analysis.days_frm_zero_date = WORKDAYS
    ) ,fcd.deployment_id) ) DEPLOYMENT_ID
  FROM FCM_OFMA,
    FCC_DEPLOYMENTS FCD;

Continue the configuration with Renaming Custom Dimensions.