SQL to calculate the final rating score and produce HDL-ready columns

The main data model is responsible for finding the relevant performance documents, calculating questionnaire scores, combining those scores with overall summary ratings, and deriving the final tier description. The query starts by identifying recently updated evaluations so that the process only works on records that changed within the configured time window. This is useful for scheduled automation because it avoids recalculating the entire population every time the flow runs.

The QSTNR_SCORE section calculates the questionnaire score for each worker. In this example, the total questionnaire score is divided by three and weighted at 25 percent. The result is rounded to two decimal places so that it can be combined cleanly with the other rating values.

The COMBINED_SCORES section then brings together two types of rows: one row for the questionnaire contribution and another row for the Overall Summary section rating. These rows are combined using UNION ALL, which allows the final calculation step to sum both score components for the same worker and assignment.

The final output maps the numeric score to a rating tier. For example, scores below 2.50 are categorized as 1 – Learner, while scores greater than or equal to 4.50 are categorized as 5 – Exceeded Expectations. This mapped rating name becomes the value loaded into the performance document.

Main Data Model –

			
SELECT DISTINCT worker_id
    FROM fusion.hra_evaluations
    WHERE last_update_date >= (SYSDATE - 60/1440)
    --AND STATUS_CODE = 'COMP' 
),
QSTNR_SCORE AS (
    
    SELECT 
        WORKER_ID,
        ROUND((SUM(TO_NUMBER(TOTAL_SCORE))/3)* 0.25,2) AS SCORE
    FROM (
        SELECT htpt.CUSTOMARY_NAME
                ,HQP.subject_id
                ,HQP.PARTICIPANT_ID
                ,HQP.creation_date
                ,EVAL.WORKER_ID
                ,EVAL.EVALUATION_ID
                ,HQP.questionnaire_id
                ,EVAL.STATUS_CODE
                ,TOTAL_SCORE 
        FROM 
                fusion.HRA_EVALUATIONS EVAl,
                fusion.hra_tmpl_periods_tl htpt,
                fusion.hra_eval_sections hes,
                fusion.hrq_qstnr_participants hqp ,
                fusion.hrq_questionnaires_tl hqnrt ,
                fusion.hrq_qstnr_responses hqnrres ,
                fusion.hrq_qstn_responses hqtnr,
                fusion.hrq_qstn_answers_tl hqanst,
                fusion.hrq_qstnr_questions hqnrq ,
                fusion.hrq_questions_b hqb ,
                fusion.hrq_questions_tl hqt ,
                fusion.hrt_review_periods_vl hrpv
        WHERE 1=1
            --  TRIGGER LINK ADDED
            and eval.worker_id in (SELECT worker_id FROM RECENT_FEEDBACK)
            and EVAL.tmpl_period_id = htpt.tmpl_period_id
            and htpt.language = userenv('LANG')
            and hes.evaluation_id=eval.evaluation_id
            and hes.section_type_code='QUESTIONNAIRE'
            and hqp.subject_id(+)=to_char(hes.evaluation_id)
            and hqnrt.language(+)=sys_context('USERENV','LANG')
            and hqnrt.questionnaire_id(+)=hqp.questionnaire_id
            and hqnrt.QUESTIONNAIRE_ID (+) IN ('300000325595735')
            and hqnrres.qstnr_participant_id(+)=hqp.qstnr_participant_id
            and hqtnr.qstnr_response_id(+)=hqnrres.qstnr_response_id
            and hqanst.qstn_answer_id(+)=hqtnr.qstn_answer_id
            and hqanst.language(+)=sys_context('USERENV','LANG')
            and hqnrq.qstnr_question_id(+)=hqtnr.qstnr_question_id
            and hqb.question_id(+)=hqnrq.question_id
            and hqt.question_id(+)=hqb.question_id 
            and hqT.language(+)=sys_context('USERENV','LANG')
            and hqt.qstn_version_num(+)=hqb.qstn_version_num
            and EVAL.review_period_id=hrpv.review_period_id
            AND htpt.CUSTOMARY_NAME IN ('OE - Annual Final Review 2025-26') 
        GROUP BY htpt.CUSTOMARY_NAME
                ,HQP.subject_id
                ,HQP.PARTICIPANT_ID
                ,HQP.creation_date
                ,EVAL.WORKER_ID
                ,EVAL.EVALUATION_ID
                ,HQP.questionnaire_id
                ,EVAL.STATUS_CODE
                ,TOTAL_SCORE
    )
    GROUP BY WORKER_ID
),
COMBINED_SCORES AS (
    -- 1st Row: QUESTIONNAIRE
    SELECT papf.person_number               AS PERSON_NUMBER,
           paam.assignment_number           AS ASSIGNMENT_NUMBER,
           mp.person_number                 AS MANAGER_PERSON_NUMBER,
           tp.customary_name                AS PERFORMANCE_DOCUMENT,
           qs.SCORE                         AS FINAL_CALCULATED_SCORE
    FROM   fusion.hra_evaluations ev,
           fusion.per_all_people_f papf,
           fusion.hra_tmpl_periods_vl tp,
           fusion.hra_eval_sections es,
           fusion.hra_tmpl_sections ts,
           fusion.hra_section_defns_vl sdf,
           fusion.per_all_assignments_m paam,
           fusion.per_all_people_f mp,
           QSTNR_SCORE qs
    WHERE  ev.worker_id = papf.person_id
           AND papf.person_id IN (SELECT worker_id FROM RECENT_FEEDBACK) 
           AND qs.WORKER_ID(+) = ev.worker_id
           AND Trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
           AND ev.tmpl_period_id = tp.tmpl_period_id
           AND es.evaluation_id = ev.evaluation_id
           AND ts.section_id = es.tmpl_section_id
           AND sdf.section_def_id = ts.section_def_id
           AND es.section_type_code = 'QUESTIONNAIRE'
           AND tp.customary_name = 'OE - Annual Final Review 2025-26'
           AND papf.person_id = paam.person_id
           AND paam.primary_flag = 'Y'
           AND paam.assignment_type IN ('E', 'C')
           AND Trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
           AND ev.manager_id = mp.person_id(+)
           AND Trunc(sysdate) BETWEEN mp.effective_start_date(+) AND mp.effective_end_date(+)
    UNION ALL
    -- 2nd Row: OS (Overall Summary)
    SELECT papf.person_number               AS PERSON_NUMBER,
           paam.assignment_number           AS ASSIGNMENT_NUMBER,
           mp.person_number                 AS MANAGER_PERSON_NUMBER,
           tp.customary_name                AS PERFORMANCE_DOCUMENT,
           evrt.calculated_rating           AS FINAL_CALCULATED_SCORE
    FROM   fusion.hra_evaluations ev,
           fusion.per_all_people_f papf,
           fusion.hra_tmpl_periods_vl tp,
           fusion.hra_eval_sections es,
           fusion.hra_tmpl_sections ts,
           fusion.hra_section_defns_vl sdf,
           fusion.hra_eval_ratings evrt,
           fusion.per_all_assignments_m paam,
           fusion.per_all_people_f mp
    WHERE  ev.worker_id = papf.person_id
           -- 5 MINUTE TRIGGER LINK
           AND papf.person_id IN (SELECT worker_id FROM RECENT_FEEDBACK)
           AND Trunc(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
           AND ev.tmpl_period_id = tp.tmpl_period_id
           AND es.evaluation_id = ev.evaluation_id
           AND ts.section_id = es.tmpl_section_id
           AND sdf.section_def_id = ts.section_def_id
           AND es.eval_section_id = evrt.reference_id
           AND es.evaluation_id = evrt.evaluation_id
           AND es.section_type_code = 'OS'
           AND tp.customary_name = 'Annual Final Review 2025-26'
           AND evrt.calculated_rating IS NOT NULL
           AND papf.person_id = paam.person_id
           AND paam.primary_flag = 'Y'
           AND paam.assignment_type IN ('E', 'C')
           AND Trunc(sysdate) BETWEEN paam.effective_start_date AND paam.effective_end_date
           AND ev.manager_id = mp.person_id(+)
           AND Trunc(sysdate) BETWEEN mp.effective_start_date(+) AND mp.effective_end_date(+)
),
FINAL_CALC AS (
    -- both score add
    SELECT PERSON_NUMBER,
           ASSIGNMENT_NUMBER,
           MANAGER_PERSON_NUMBER,
           PERFORMANCE_DOCUMENT,
           ROUND(SUM(FINAL_CALCULATED_SCORE), 2) AS FINAL_SCORE
    FROM   COMBINED_SCORES
    GROUP BY PERSON_NUMBER, ASSIGNMENT_NUMBER, MANAGER_PERSON_NUMBER, PERFORMANCE_DOCUMENT
)
-- Final Output 
SELECT PERSON_NUMBER,
       ASSIGNMENT_NUMBER,
       MANAGER_PERSON_NUMBER,
       PERFORMANCE_DOCUMENT,
       FINAL_SCORE,
       'ORA_RATING_PD'              AS operationn,
       'Manager'                    AS particpant,
       'Overall Summary Section'    AS sec_name,
       'OS'                         AS sec_type,
       -- Tier Description
       CASE 
           WHEN FINAL_SCORE < 2.50 THEN '1 - Learner'
           WHEN FINAL_SCORE BETWEEN 2.50 AND 3.00 THEN '2 - Performance Needs Improvement'
           WHEN FINAL_SCORE BETWEEN 3.01 AND 3.99 THEN '3 - Successful'
           WHEN FINAL_SCORE BETWEEN 4.00 AND 4.49 THEN '4 - Highly Successful'
           WHEN FINAL_SCORE >= 4.50 THEN '5 - Exceeded Expectations'
           ELSE 'Unrated' 
       END AS TIER_DESCRIPTION
FROM   FINAL_CALC

		

Call ID Data Model

The Call ID data model retrieves the ESS request call ID for the current flow task instance. This call ID is important because it is used as the bursting key and as part of the generated content title or content ID. When the task instance ID is not available, the query returns a fallback value so the data model can still be tested independently.

			
Select call_id 
from pay_flow_task_instances fti
,pay_flow_tasks_vl ft
,pay_requests r
where ft.flow_task_name = 'Generate Data'
and ft.base_flow_task_id = fti.base_flow_task_id 
and fti.flow_task_instance_id = r.flow_task_instance_id 
and r.call_type= 'ESS'
and  fti.flow_task_instance_id = :TASK_INSTANCE_ID
union
select 1234
from dual
where :TASK_INSTANCE_ID is null

		

BI Publisher Bursting to UCM

After the report output is generated, BI Publisher bursting is used to deliver the file to Oracle WebCenter Content. The delivery channel is configured as WCC, the server name is set to FA_UCM_PROVISIONED, and the security group is set to FAFusionImportExport. This pattern is commonly used when preparing files that will later be consumed by HCM Data Loader.

The bursting query generates a text file named PerfDocComplete.dat. The title and content ID are dynamically constructed using the call ID so that each generated file remains traceable to the ESS request that created it.

			
select to_char(call_id) as "KEY",
'PerfDocComplete' TEMPLATE,
'en-US' LOCALE,
'TEXT' OUTPUT_FORMAT,
'WCC' DEL_CHANNEL,
'FA_UCM_PROVISIONED' PARAMETER1,  /* Server Name */
'FAFusionImportExport' PARAMETER2,  /* Security Group */
:xdo_user_name PARAMETER3,  /* Author of the File */
'PerfDocComplete'||to_char(call_id) PARAMETER5,  /* Title */
'PerfDocComplete.dat' PARAMETER6,  /* Output File Name */
:TASK_INSTANCE_ID PARAMETER7,  /* Comments (Optional) */
'PerfDocComplete'||to_char(call_id) PARAMETER8,   /* Content ID (Optional) If you specify the ID, it must be unique. If you don't specify the ID, the system generates a unique one. */
'FALSE' PARAMETER9  /* Custom metadata (true/false). Specify ‘false’. */
from
(
select r.call_id
from pay_flow_task_instances fti
,pay_flow_tasks_vl ft
,pay_requests r
where ft.flow_task_name = 'Generate Data'
and ft.base_flow_task_id = fti.base_flow_task_id 
and fti.flow_task_instance_id = r.flow_task_instance_id 
and r.call_type= 'ESS'
and  fti.flow_task_instance_id = :TASK_INSTANCE_ID
union
select 1234
from dual
where :TASK_INSTANCE_ID is null
)

		

Report Template –

The report template is designed to generate a pipe-delimited text output that matches the structure expected by the HDL transformation formula. The template must place each value in the correct position because the fast formula reads the incoming file using positional fields such as POSITION1, POSITION2, and so on.