Oracle Fusion Useful Queries

Get POD Name

SELECT EXTERNAL_VIRTUAL_HOST "POD_Name" FROM ASK_DEPLOYED_DOMAINS WHERE DEPLOYED_DOMAIN_NAME = 'FADomain'

REST/ATOM Entry Details

SELECT ENTRY_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, ENTRY_XML
FROM HRC_ATOMPUB_ENTRIES
WHERE ENTRY_XML LIKE '%102000016429378%'
ORDER BY LAST_UPDATE_DATE DESC

Absence Entry Details

SELECT ABS.PER_ABSENCE_ENTRY_ID

, ABS_DTL.PER_ABS_ENTRY_DTL_ID

, ABS_DTL.DURATION

, ABS_VL.NAME

FROM ANC_PER_ABS_ENTRIES ABS

, ANC_PER_ABS_ENTRY_DTLS ABS_DTL

, ANC_ABSENCE_TYPES_VL ABS_VL

WHERE ABS_DTL.PER_ABSENCE_ENTRY_ID = ABS.PER_ABSENCE_ENTRY_ID

AND ABS_VL.ABSENCE_TYPE_ID = ABS.ABSENCE_TYPE_ID

AND TO_CHAR(ABS_DTL.ABSENCE_DATE, 'YYYY/MM/DD') = '2020/07/27'

-- AND ABS_VL.NAME = 'Mandated Sick Leave'

AND ABS.PERSON_ID = #;

SELECT PAPF.PERSON_NUMBER

, REC.START_TIME

, Atrbs.Attribute_Category 

, ABS_VL.NAME NAME1 

, REC.MEASURE

FROM FUSION.HWM_TM_REC REC

, FUSION.Hwm_Tm_Rep_Atrb_Usages atrbusg

, FUSION.Hwm_Tm_Rep_Atrbs atrbs

, FUSION.HWM_TM_ATRB_FLDS_VL flds

, PER_ALL_PEOPLE_F PAPF

, ANC_ABSENCE_TYPES_VL ABS_VL

WHERE 

REC.ORIG_TM_REC_ID IS NULL

--AND REC.DELETE_FLAG IS NULL

AND REC.LATEST_VERSION = 'Y'

AND Atrbusg.Usages_Source_Id = REC.tm_rec_id

AND Atrbusg.Usages_Source_Version = REC.Tm_Rec_Version

AND Atrbusg.Tm_Rep_Atrb_Id = Atrbs.Tm_Rep_Atrb_Id

AND Flds.Tm_Atrb_Fld_Id = Atrbs.Master_Attribute_Id

AND flds.name IN ('AbsenceType')

AND REC.RESOURCE_ID = PAPF.PERSON_ID

AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

--AND PAPF.PERSON_NUMBER = '80000105859'

AND ATRBS.ATTRIBUTE_CATEGORY = ABS_VL.ABSENCE_TYPE_ID

AND SYSDATE BETWEEN ABS_VL.EFFECTIVE_START_DATE AND ABS_VL.EFFECTIVE_END_DATE

AND  REC.START_TIME BETWEEN to_date('08/14/2016','MM/DD/YYYY') AND to_date('08/27/2016','MM/DD/YYYY')

ORDER BY 1,2;

Action Reason

SELECT * FROM PER_ACTION_REASONS_B WHERE ACTION_REASON_CODE IN ('HIR', 'REH');

SELECT * FROM PER_ACTION_REASONS_TL
WHERE LANGUAGE = 'US'
AND ACTION_REASON_ID IN (300000007520663,300000007520665);

BI Report Status

SELECT  rh.*

FROM fusion_ora_ess.request_history_view rh 

JOIN fusion_ora_ess.request_property_view rp 

ON (rh.requestid = rp.requestid 

AND rp.NAME = 'report_url' 

AND rp.VALUE LIKE '%Report_Name%'

)  ORDER BY rh.processstart desc

 

 

Fast Formula

SELECT F.FORMULA_ID, F.BASE_FORMULA_NAME, F.EFFECTIVE_START_DATE, F.FORMULA_TYPE_NAME
FROM FF_FORMULAS_B_F F, FF_FORMULA_TYPES T
WHERE F.BASE_FORMULA_NAME LIKE '%TL%'
AND T.FORMULA_TYPE_ID = F.FORMULA_TYPE_ID

Valueset FF

lc_Job_Entry_Dt = GET_VALUE_SET('XX_JOB_ENTRY_DATE','|=P_ASG_ID='''||l_Asg_Id||''''||'|P_JOB_ID='''||l_Job_Id||''''||'|P_ASG_START_DT='''||l_Asg_Start_Dt||'''')

SELECT VS.VALUE_SET_ID
    , VS.VALUE_SET_CODE
    , VS.VALIDATION_TYPE
    , VST.VALUE_COLUMN_NAME
    , VST.FROM_CLAUSE
    , VST.WHERE_CLAUSE
    , VST.ORDER_BY_CLAUSE
FROM FND_VS_VALUE_SETS VS
    , FND_VS_VT_TABLE_EXT VST
WHERE VS.VALUE_SET_CODE LIKE '%TL%'
    AND VST.VALUE_SET_ID = VS.VALUE_SET_ID

UDT Query

SELECT  ff_user_tables_pkg.get_table_value_ent (1, SYSDATE,'UDT_TABLE_NAME', 'EMAIL_DL' , 'ROW_NAME') ROW_NAME FROM DUAL

SELECT PLDG.LEGISLATION_CODE LEGISLATION_CODE
    , FUCT.USER_COLUMN_NAME COLUMN_NAME
    , FURT.ROW_NAME ROW_NAME
    , FUCIF.VALUE VALUE
    , BASE_USER_TABLE_NAME
FROM FUSION.FF_USER_TABLES FUT
    , PER_LEGISLATIVE_DATA_GROUPS PLDG
    , FUSION.FF_USER_COLUMN_INSTANCES_F FUCIF
    , FUSION.FF_USER_COLUMNS FUC
    , FUSION.FF_USER_COLUMNS_TL FUCT
    , FUSION.FF_USER_ROWS_F FURF
    , FUSION.FF_USER_ROWS_TL FURT
WHERE FUT.BASE_USER_TABLE_NAME LIKE '%UDT_TABLE_NAME%'
    AND FUT.LEGISLATIVE_DATA_GROUP_ID = PLDG.LEGISLATIVE_DATA_GROUP_ID
    AND FUT.USER_TABLE_ID = FUC.USER_TABLE_ID
    AND FUC.USER_COLUMN_ID = FUCT.USER_COLUMN_ID
    AND FUT.USER_TABLE_ID = FURF.USER_TABLE_ID
    AND FURF.USER_ROW_ID = FURT.USER_ROW_ID
    AND FUCIF.USER_ROW_ID = FURT.USER_ROW_ID
    AND FUCIF.USER_COLUMN_ID = FUCT.USER_COLUMN_ID
    AND FURT.LANGUAGE = 'US'
    AND FUCT.LANGUAGE = 'US'
    AND TRUNC(SYSDATE) BETWEEN FURF.EFFECTIVE_START_DATE
        AND FURF.EFFECTIVE_END_DATE
    AND TRUNC(SYSDATE) BETWEEN FUCIF.EFFECTIVE_START_DATE
        AND FUCIF.EFFECTIVE_END_DATE

 

Batchline status

select batch_line_status, count(batch_line_status) from pay_batch_lines
where batch_id in (select batch_id from pay_batch_headers where batch_name = 'UCMFA17640491')
group by batch_line_status

Payroll Balance

SELECT PPRD.PERSON_ID, PPA.EFFECTIVE_DATE, PPA.PAYROLL_ACTION_ID, PPRA.PAYROLL_REL_ACTION_ID
    , PB.BALANCE_NAME BAL_NAME
    , PRRV.RESULT_VALUE BAL
FROM PAY_PAY_RELATIONSHIPS_DN PPRD
    ,PAY_PAYROLL_REL_ACTIONS PPRA
    ,PAY_PAYROLL_ACTIONS PPA
    ,PAY_RUN_RESULTS PRR
    ,PAY_RUN_RESULT_VALUES PRRV
    ,PAY_BALANCE_FEEDS_F PBF
    ,PAY_BALANCE_TYPES_TL PB
WHERE 1 = 1
--    AND PPRD.PERSON_ID               = papf.person_id
    AND PPRA.PAYROLL_RELATIONSHIP_ID = PPRD.PAYROLL_RELATIONSHIP_ID
    AND PPRA.ACTION_STATUS = 'C'
    AND PPRA.RETRO_COMPONENT_ID IS NULL
    AND PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID
    AND PPA.ACTION_TYPE IN ('Q','R','V')
    AND PPA.ACTION_STATUS = 'C'
    AND PPA.EFFECTIVE_DATE BETWEEN SYSDATE - 365 and sysdate
    AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID
    AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
    AND PBF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
    AND PPA.EFFECTIVE_DATE BETWEEN PBF.EFFECTIVE_START_DATE AND PBF.EFFECTIVE_END_DATE
    AND PB.BALANCE_TYPE_ID = PBF.BALANCE_TYPE_ID
    AND PB.LANGUAGE = 'US'
    AND PB.BALANCE_NAME IN  ('FMLA hours worked','FMLA hours paid')

HCM Extract Parameters

select definition_name from per_ext_definitions_vl where base_definition_name in (
select distinct f.flow_name from
pay_flow_instances fi,
pay_flows_vl f,
pay_flow_parameters_vl fp,
pay_flow_param_values pv
where f.base_flow_id = fi.base_flow_id
and fp.base_flow_id = f.base_flow_id
and pv.flow_instance_id = fi.flow_instance_id
and fp.base_flow_parameter_id = pv.base_flow_parameter_id
and fp.BASE_FLOW_PARAMETER_NAME = 'DELETE_ARCHIVE' and NVL(pv.FLOW_PARAM_VALUE, 'ABC') <> 'ABC'
);

HCM Extract Delivery Options

select e.base_definition_name, DO.OUTPUT_TYPE, DO.BIP_REPORT_NAME, DO.BIP_TEMPLATE_NAME, DO.OUTPUT_NAME, do.delivery_option_name--,dtl.option_value
,LISTAGG(option_value,' | ') WITHIN GROUP (ORDER BY dtl.OPTION_TYPE) ftpValues
from PER_EXT_DELIVERY_OPTION_DTLS dtl
,PER_EXT_DELIVERY_OPTIONS_vl do
, PER_EXT_DEFINITIONS_vl e
where e.ext_definition_id = do.ext_definition_id
and do.ext_delivery_option_id = dtl.ext_delivery_option_id
and do.delivery_type = 'HCMCONNECT'
-- and dtl.OPTION_TYPE in ( 'PARAMETER4','PARAMETER1')
and e.base_definition_name like 'LB%'
group by e.base_definition_name,do.delivery_option_name, DO.OUTPUT_TYPE, DO.BIP_REPORT_NAME, DO.BIP_TEMPLATE_NAME, DO.OUTPUT_NAME;

HCM Extract Run Status

Select requestid, name, definition, parentrequestid, state, scheduled, processstart, processend
from fusion.ess_request_history
where parentrequestid in (SELECT pr.call_id
                            FROM pay_flow_Task_instances fti, pay_flow_tasks_vl ft, pay_checklists_vl pc, pay_requests pr
                            WHERE fti.flow_instance_id = (SELECT flow_instance_id
                                                            FROM pay_flow_instances
                                                            WHERE instance_name = '<Flow_Instance>')
                                AND fti.base_flow_task_id = ft.base_flow_task_id
                                AND ft.base_flow_task_id = pc.base_flow_task_id
                                AND fti.flow_task_instance_id = pr.flow_task_instance_id
                                AND call_type IN ('FLOW_ESS', 'ESS')
                            ) order by requestid ;

Entry Date / Job Entry Date / Position Entry Date

SELECT PAAM.PERSON_ID, PAAM.ASSIGNMENT_ID, PAAM.EFFECTIVE_START_DATE, PAAM.JOB_ID, PAAM.POSITION_ID
, ROW_NUMBER() OVER (PARTITION BY PAAM.PERSON_ID, PAAM.ASSIGNMENT_ID, PAAM.JOB_ID ORDER BY PAAM.EFFECTIVE_START_DATE DESC, PAAM.EFFECTIVE_SEQUENCE DESC) JOB_ROW_ID
, ROW_NUMBER() OVER (PARTITION BY PAAM.PERSON_ID, PAAM.ASSIGNMENT_ID, PAAM.POSITION_ID ORDER BY PAAM.EFFECTIVE_START_DATE DESC, PAAM.EFFECTIVE_SEQUENCE DESC) POS_ROW_ID
, RANK() OVER (PARTITION BY PAAM.PERSON_ID ORDER BY PAAM.EFFECTIVE_START_DATE DESC, PAAM.EFFECTIVE_SEQUENCE DESC) ASG_ROW_ID
FROM PER_ALL_ASSIGNMENTS_M PAAM
WHERE PAAM.PERSON_ID = 5478
AND PAAM.ASSIGNMENT_TYPE = 'E'
ORDER BY PAAM.EFFECTIVE_START_DATE DESC

Payroll checklist status

SELECT fl.instance_name--,ess.processstart
    ,pc.checklist_name||' - '||fti.status status
    ,fl.created_by SubBy
    ,to_char(fl.creation_date,'DD-MON-YYYY') SubOn
    ,pr.call_id as PrcssId
    ,(CASE when fti.status = 'COMPLETED'then NULL
         else (Select distinct MSG.MESSAGE from PAY_PAY_MSG_RESULTS_VL MSG where MSG.task_Instance_ID = fti.flow_task_Instance_ID and rownum <2)
    END) message_text
    --,MSG.MESSAGE message_text
    --,ess.processstart,ess.processend, (nvl(ess.processend,SYSTIMESTAMP) - ess.processstart) as duration
    --,ess.ABSPARENTID
    --,ess.requestid
FROM
    pay_flow_Task_instances fti,
    pay_flow_tasks_vl ft,
    pay_checklists_vl pc,
    pay_requests pr
    ,
    --fusion_ora_ess.request_history ess,
    pay_flow_instances fl
    --,PAY_PAY_MSG_RESULTS_VL MSG
WHERE fti.flow_instance_id = fl.flow_instance_id
    --AND fl.instance_name = 'LB_I_TA005B_CA_Payroll Relationship Details_BatchLoader_UCMFA09390232'
    AND fl.instance_name like '%US06162018%'
    AND fti.base_flow_task_id = ft.base_flow_task_id
    AND ft.base_flow_task_id = pc.base_flow_task_id
    AND fti.flow_task_instance_id = pr.flow_task_instance_id
    --AND call_type IN ('ESS')
    --and pr.call_id = ess.requestid
    and trunc(fl.creation_date) between sysdate-7 and sysdate
    and pr.call_id <> -1
order by fl.creation_date  desc,fl.instance_name;


SELECT PFI.FLOW_INSTANCE_ID
    , PFI.BASE_FLOW_ID
    , PFI.INSTANCE_NAME
    , PR.CALL_ID PROCESS_ID
    , PFI.STATUS
    , PFI.LAST_UPDATED_BY
    , PFI.LAST_UPDATE_DATE
FROM PAY_FLOW_INSTANCES PFI
    , PAY_REQUESTS PR
WHERE PFI.INSTANCE_NAME LIKE '%US06162018%'
AND PR.FLOW_INSTANCE_ID = PFI.FLOW_INSTANCE_ID
;

First pay date of an year

SELECT PTP.REGULAR_PROCESS_DATE

FROM PAY_TIME_PERIODS PTP

, PAY_ALL_PAYROLLS_F PAYROLL

WHERE TO_CHAR(PTP.REGULAR_PROCESS_DATE, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY')

AND PTP.PERIOD_CATEGORY = 'E'

AND PTP.PERIOD_NUM = 1

AND PAYROLL.PAYROLL_ID = PTP.PAYROLL_ID

AND PAYROLL.PAYROLL_NAME = 'US Biweekly'

AND SYSDATE BETWEEN PAYROLL.EFFECTIVE_START_DATE AND PAYROLL.EFFECTIVE_END_DATE

AND ROWNUM = 1

ORDER BY PTP.REGULAR_PROCESS_DATE

 

Department Tree

WITH DEPT_TREE_DS AS (
    SELECT PDT.PK1_VALUE
        , PDT.ANCESTOR_PK1_VALUE
        , PDT.DISTANCE
    FROM PER_DEPT_TREE_NODE_RF PDT
        , FND_TREE_VERSION FTV
    WHERE PDT.DISTANCE in (0,1,2,3,4,5,6)
        AND FTV.TREE_STRUCTURE_CODE = PDT.TREE_STRUCTURE_CODE
        AND FTV.ENTERPRISE_ID = PDT.ENTERPRISE_ID
        AND FTV.TREE_CODE = PDT.TREE_CODE
        AND FTV.TREE_VERSION_ID = PDT.TREE_VERSION_ID
        AND TRUNC(SYSDATE) BETWEEN FTV.EFFECTIVE_START_DATE AND FTV.EFFECTIVE_END_DATE
        AND FTV.STATUS = 'ACTIVE'
)
SELECT HAOU.ORGANIZATION_ID
    , HAOU.NAME  Department
    , HAOU.ATTRIBUTE1 DEPTID
    , HAOU.ATTRIBUTE6 Brand
    , DTD.distance
    , HAOU_Parent.NAME Parent
    , HAOU_Parent.ATTRIBUTE1 Parent_Name
    , HAOU_Parent.ATTRIBUTE2 Parent_Type
FROM HR_ALL_ORGANIZATION_UNITS_F_VL HAOU
    , DEPT_TREE_DS DTD
    , HR_ALL_ORGANIZATION_UNITS_F_VL HAOU_Parent
WHERE 1=1
    AND HAOU.ATTRIBUTE2 ='Store'
    AND TRUNC(SYSDATE) BETWEEN HAOU.EFFECTIVE_START_DATE AND HAOU.EFFECTIVE_END_DATE
    AND DTD.PK1_VALUE = HAOU.ORGANIZATION_ID
    AND HAOU_Parent.ORGANIZATION_ID = DTD.ANCESTOR_PK1_VALUE
    AND TRUNC(SYSDATE) BETWEEN HAOU_Parent.EFFECTIVE_START_DATE AND HAOU_Parent.EFFECTIVE_END_DATE
    and HAOU.ATTRIBUTE1 = '12345';

Gross Pay

SELECT NVL(f.balance_value,0)
FROM pay_payroll_rel_actions a
    , pay_pay_relationships_dn b
    , pay_payroll_actions c
    , pay_balance_types_vl d
    , pay_dimension_usages_vl e
    , table(pay_balance_view_pkg.get_balance_dimensions(d.balance_type_id,a.payroll_rel_action_id,null,NULL))f
WHERE b.person_id = (select distinct person_id from per_all_people_f where person_number = :{PARAMETER.P_NUMBER})
    and a.payroll_relationship_id = b.payroll_relationship_id
    and a.retro_component_id is null
    and c.payroll_action_id = a.payroll_action_id
    and a.payroll_rel_action_id = :{PARAMETER.P_PAY_REL_ACTION_ID}                                  
    and b.legislative_data_group_id = 300000007469926   
    and c.effective_date = to_date(:{PARAMETER.P_DATE},'YYYYMMDD')
    AND exists(select 1 from pay_run_results prr
                where prr.payroll_rel_action_id = a.payroll_rel_action_id)
    and f.balance_dimension_id = e.balance_dimension_id
    and nvl(d.legislation_code,  'CA') = 'CA'
    and nvl(d.legislative_data_group_id, 300000007469926) = 300000007469926
    and d.balance_name = :{PARAMETER.P_BAL}            -- 'Gross Pay'
    and e.dimension_name = :{PARAMETER.P_DIMEN}     -- 'Relationship Tax Unit Run'
    and nvl(e.legislation_code, 'CA') = 'CA'
    and nvl(e.legislative_data_group_id, 300000007469926)= 300000007469926
    and f.balance_value > 0

 

/* Performance rating */

SELECT HPB.PERSON_ID
                , HPI.ITEM_NUMBER_1 NUMERIC_RATING
                , HPI.PROFILE_ID
                , HPI.ITEM_DATE_2 DATE_FROM
                , HPI.ITEM_DATE_3 DATE_TO
                , HCTB.CONTEXT_NAME
            FROM HRT_PROFILES_B HPB
                , HRT_PROFILE_ITEMS HPI
                , HRT_CONTENT_TYPES_B HCTB
            WHERE HPI.CONTENT_TYPE_ID = HCTB.CONTENT_TYPE_ID
                AND HCTB.CONTEXT_NAME IN ('HIST_PERF_RATING')
                AND HPB.PROFILE_ID = HPI.PROFILE_ID
                AND HPI.ITEM_DATE_2 = (SELECT MAX(HPI1.ITEM_DATE_2)
                                        FROM HRT_PROFILE_ITEMS HPI1
                                        WHERE HPI.PROFILE_ID = HPI1.PROFILE_ID
                                    )

/* Regular Expression */

TRIM(REGEXP_REPLACE(XRV.REGISTRATION_NUMBER,'[^[:digit:]]+','')),

/* Process Monitor */

select fv.meaning, fv.lookup_type,rh.*
from fusion_ora_ess.request_history rh,fnd_lookup_values fv
where  fv.lookup_code=rh.state
and fv.language='US'
and fv.lookup_type like 'HRC_DL_ESS_STATE'
-- and definition like '%ACRPRC%'
-- and name like 'ACRPLENRCHILD'
and meaning in ('Running','Paused')
order by processend desc, submission desc