Oracle HCM Extract Schedule Query

 SELECT pfv.base_flow_name Base_flow_name
       ,
       pfi.instance_name,
       (SELECT To_char(Min(fti1.creation_date),'DD-MON-YYYY')
        FROM   pay_flow_task_instances fti1
        WHERE  pfi.root_flow_instance_id=fti1.flow_instance_id)
       FIRST_SCHEDULE_DATE,
       To_char(fti.scheduled_date,'DD-MON-YYYY')
       Current_Schedule_Date,
       fti.status
       CURRENT_SCHEDULE_STATUS,
       (SELECT Listagg(fp.parameter_name
                       || '='
                       || pv.flow_param_value,',')
                 within GROUP(ORDER BY fp.parameter_name)
        FROM   pay_flow_parameters_vl fp,
               pay_flow_param_values pv
        WHERE  1 = 1 
               AND fp.base_flow_id=pfv.base_flow_id
               AND pv.flow_instance_id=pfi.flow_instance_id
               AND fp.base_flow_parameter_id=pv.base_flow_parameter_id
               AND flow_param_value IS NOT NULL
               AND pfi.instance_name=pfi.instance_name)
       Flow_parameters_Latest_Run,
       fti.created_by
       Schedule_Created_By
FROM   pay_flow_task_instances fti,
       pay_flow_tasks_vl ft,
       pay_flow_instances pfi,
       pay_flows_vl pfv
WHERE  1 = 1 
       AND pfv.base_flow_id=pfi.base_flow_id
       AND fti.base_flow_task_id=ft.base_flow_task_id
       AND pfi.flow_instance_id=fti.flow_instance_id
       AND fti.scheduled_date IS NOT NULL
       AND fti.status='SCHEDULED'
       AND pfi.status='SCHEDULED'
ORDER  BY flow_name DESC

Element entries query

 SELECT
    'BURSTING_KEY' BUR_KEY,
    person_number,
    plan_id,
    assignment_id,
    person_id,
    start_date,
    end_date,
    SUM(ELE1)      ELE1,
    SUM(ELE2)      ELE2,
    SUM(ELE3)      ELE3,
    SUM(ELE4)      ELE4
FROM
    (
        SELECT
            papf.person_number,
            paaf.ass_attribute8                                                                       plan_id,
            paaf.assignment_id,
            paaf.person_id,
            to_char(ptp.start_date,'YYYYMMDD','NLS_DATE_LANGUAGE=AMERICAN') start_date,
to_char(ptp.end_date,'YYYYMMDD','NLS_DATE_LANGUAGE=AMERICAN') end_date,
            decode(ele.element_name, 'Ele1',(prrv.result_value), 0) ELE1,
            decode(ele.element_name, 'Ele2',(prrv.result_value), 0) ELE2,
            decode(ele.element_name, 'Ele3',(prrv.result_value), 0) ELE3,
            decode(ele.element_name, 'Ele4',(prrv.result_value), 0) ELE4
        FROM
            pay_element_types_vl     ele,
            pay_run_results          prr,
            pay_run_result_values    prrv,
            pay_input_values_vl      piv,
            pay_payroll_rel_actions  ppra,
            pay_payroll_assignments  ppasg,
            per_all_assignments_f    paaf,
            per_all_people_f         papf,
            pay_payroll_actions      ppa,
            pay_all_payrolls_f       pap,
            pay_time_periods         ptp
        WHERE
                1 = 1
            AND ele.element_type_id = prr.element_type_id
            AND prr.run_result_id = prrv.run_result_id
            AND prrv.input_value_id = piv.input_value_id
            AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id
            AND ppra.payroll_relationship_id = ppasg.payroll_relationship_id
            AND ppasg.hr_assignment_id = paaf.assignment_id
            AND paaf.person_id = papf.person_id
            AND ppra.payroll_action_id = ppa.payroll_action_id
            AND ppa.payroll_id = pap.payroll_id
            AND pap.payroll_id = ptp.payroll_id
            AND ((ppra.run_type_id IS NOT NULL AND ppa.action_type in ('R','Q','V')) 
OR ppa.action_type in ('B'))
AND ppra.RETRO_COMPONENT_ID is NULL
            AND paaf.assignment_type = 'E'
            AND paaf.primary_flag = 'Y'
            AND paaf.ass_attribute_category = 'CA'
            AND paaf.ass_attribute8 IS NOT NULL
            AND ((ele.element_name IN ('Element1','Element2')
            AND piv.name = 'Pay Value')
OR (ele.element_name IN ('Element3','Element4')
            AND piv.name = 'Earnings'))
            AND ptp.period_category = 'E'
            AND :p_eff_date BETWEEN pap.effective_start_date AND pap.effective_end_date
            AND :p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND :p_eff_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
            AND :p_eff_date BETWEEN ele.effective_start_date AND ele.effective_end_date
            AND :p_eff_date BETWEEN piv.effective_start_date AND piv.effective_end_date
            AND :p_eff_date BETWEEN ptp.start_date AND ptp.end_date
            AND ppa.effective_date BETWEEN ptp.start_date AND ptp.end_date
            AND papf.person_number = nvl(:p_person_number, papf.person_number)
    )
GROUP BY
    'BURSTING_KEY',
    person_number,
    plan_id,
    assignment_id,
    person_id,
    start_date,
    end_date

HCM query to fetch LE,department, position

 SELECT PAPF.person_number
,PPNF.full_name
,To_char(PPOS_MAIN.date_start, 'DD-MON-YYYY', 'nls_date_language=American') hire_date
,dept.NAME dept_name
,pos.NAME pos_name
,HRL.NAME LE_NAME
,hc.value HEADCOUNT
,To_char(PAAF.effective_start_date, 'DD-MON-YYYY', 'nls_date_language=American') effective_start_date
,To_char(PAAF.effective_end_date, 'DD-MON-YYYY', 'nls_date_language=American') effective_end_date
,To_char(PPOS_MAIN.actual_termination_date, 'DD-MON-YYYY', 'nls_date_language=American') termination_date
,pastvl.user_status assignment_status
FROM per_all_people_f PAPF
,per_person_names_f PPNF
,per_all_assignments_f PAAF
,per_periods_of_service PPOS_MAIN
,pay_payroll_assignments PPASG
,per_assignment_status_types_vl pastvl
,per_legal_employers HRL
,HR_ALL_ORGANIZATION_UNITS_F_VL dept
,HR_ALL_POSITIONS_F_VL pos
,PER_ASSIGN_WORK_MEASURES_F_V hc
WHERE PAPF.person_id = PPNF.person_id
AND PPNF.name_type = 'GLOBAL'
AND PPOS_MAIN.date_start = (
SELECT Max(date_start)
FROM per_periods_of_service PPOS
WHERE PPOS.person_id = PAPF.person_id
)
AND PPOS_MAIN.person_id = PAPF.person_id
AND PAAF.person_id = PAPF.person_id
AND PAAF.assignment_type IN ('E')
AND PPOS_MAIN.period_of_service_id = PAAF.period_of_service_id
AND PAAF.assignment_id = PPASG.hr_assignment_id
AND PAAF.legal_entity_id = HRL.organization_id
AND HRL.STATUS = 'A'
AND paaf.organization_id = dept.organization_id
AND paaf.position_id = pos.position_id(+)
AND paaf.assignment_id = hc.assignment_id(+)
AND hc.UNIT(+) = 'HEAD'
AND Nvl(PPOS_MAIN.actual_termination_date, Trunc(SYSDATE)) BETWEEN PPASG.start_date
AND PPASG.end_date
AND PAAF.assignment_status_type_id = PASTVL.assignment_status_type_id --AND PAAF.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'
AND Trunc(SYSDATE) BETWEEN PAPF.effective_start_date
AND PAPF.effective_end_date
AND Trunc(SYSDATE) BETWEEN PPNF.effective_start_date
AND PPNF.effective_end_date
AND Trunc(SYSDATE) BETWEEN PAAF.effective_start_date
AND PAAF.effective_end_date
AND Trunc(SYSDATE) BETWEEN HRL.effective_start_date
AND HRL.effective_end_date
AND Trunc(SYSDATE) BETWEEN dept.effective_start_date
AND dept.effective_end_date
AND Trunc(SYSDATE) BETWEEN pos.effective_start_date(+)
AND pos.effective_end_date(+)
AND Trunc(SYSDATE) BETWEEN hc.effective_start_date(+)
AND hc.effective_end_date(+)
AND PAPF.person_number IN ('12345')
x

Enable Advanced Edit in Oracle HCM Extracts

1. Navigate to Support and Maintenance

2. Go to task Manage common Lookups

3. Open ORA_PER_EXT_CONFIG lookup

4. Add Lookup code - EXT_PUI_ENABLE and meaning as Yes

5. Save and Close.

Adding BI Publisher Add IN in MS word

 Follow the DOC ID 2435208.1

Most important point is Note: The versions of Microsoft Office, BI Publisher Desktop, and the Java JRE must all match as either 32-bit or 64-bit.

Get current logged in user in BIP

select SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER') from dual

This query will give the current logged in user in BIP

Select multiple values in BI Report parameter dropdown

When we want to allow a BI Report parameter to take in multiple values, we need to write the where clause as below.

select * from (select 'a' col1 from dual

union

select 'b' col1 from dual

union

select 'c' col1 from dual

union

select 'd' col1 from dual

union

select 'e' col1 from dual

union

select 'f' col1 from dual

)

where 1=1

--and col1 in (:p_col1)

AND ( ( Coalesce(NULL, :p_col1) IS NULL ) OR ( col1 IN ( :p_col1) ) )

If and col1 in (:p_col1) is used then when we select All, then nothing is printed instead of all the values. 

Coalesce function returns the first non null value from its parameters