Fusion HCM Query
Payroll Run Result Value
SELECT
prr.source_id src_id,
prr.source_type,
prr.element_entry_id,
PPRA.PAYROLL_REL_ACTION_ID,
PPRA.PAYROLL_RELATIONSHIP_ID,
PPA.DATE_EARNED,
DECODE(PPA.ACTION_TYPE,'R','N','Q','Y') OFF_CYC,
PEC.BASE_CLASSIFICATION_NAME,
PET.BASE_ELEMENT_NAME,
PTP.END_DATE,
(SELECT ROUND(SUM(NVL(PRRV.RESULT_VALUE,0)),2)
FROM
PAY_INPUT_VALUES_VL PIV,
PAY_RUN_RESULT_VALUES PRRV
WHERE
PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PRRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.NAME= 'Hours Calculated'
AND PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE) HOURS,
(SELECT ROUND(SUM(NVL(PRRV.RESULT_VALUE,0)),2)
FROM
PAY_INPUT_VALUES_VL PIV,
PAY_RUN_RESULT_VALUES PRRV
WHERE
PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PRRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.NAME = ('Earnings Calculated')
AND PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE) EARN
,PRR.SOURCE_ID
FROM
PAY_PAYROLL_ACTIONS PPA,
PAY_PAYROLL_REL_ACTIONS PPRA,
PAY_TIME_PERIODS PTP,
PAY_ALL_PAYROLLS_F PAY,
PAY_ELEMENT_TYPES_VL PET,
PAY_ELE_CLASSIFICATIONS_VL PEC,
PAY_RUN_RESULTS PRR
WHERE
PPRA.ACTION_STATUS = 'C'
AND PPRA.RUN_TYPE_ID IS NOT NULL
AND PPRA.RETRO_COMPONENT_ID IS NULL
AND PPRA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE in ('R','Q')
AND PPA.ACTION_STATUS = 'C'
AND PPRA.PAYROLL_REL_ACTION_ID = PRR.PAYROLL_REL_ACTION_ID
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PPA.DATE_EARNED BETWEEN PEC.DATE_FROM AND NVL(PEC.DATE_TO,to_date('12-31-4712','MM-DD-YYYY'))
AND PPA.PAYROLL_ID = PTP.PAYROLL_ID
AND PTP.PERIOD_CATEGORY = 'E'
AND PTP.PAYROLL_ID = PAY.PAYROLL_ID
--AND PAY.PAYROLL_NAME =
AND PPA.DATE_EARNED BETWEEN PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE
--and trunc(PTP.END_DATE) = trunc(to_date((NVL(:P_END_DATE,(SELECT ED.ENDDATE FROM EDATE ED))),'MM-DD-YYYY'))
--and trunc(PTP.START_DATE) =
and PAYROLL_RELATIONSHIP_ID = 123
User Defined Query
SELECT fut.user_table_name user_table_name
,fut.range_or_match range_or_match
,fut.user_key_units user_key_units
,UPPER(fuc.user_column_name) user_column_name
,fuc.data_type data_type
,UPPER(fur.row_name) user_row_name
,TO_CHAR(fur.effective_start_date,'YYYY/MM/DD') effective_start_date
,TO_CHAR(fur.effective_end_date,'YYYY/MM/DD') effective_end_date
,fur.row_low_range_or_name row_low_range_or_name
,fur.row_name row_name
,fuci.value udt_value
,pldgt.name legislative_data_group
FROM fusion.ff_user_tables_vl fut
,fusion.ff_user_columns_vl fuc
,fusion.ff_user_rows_vl fur
,fusion.ff_user_column_instances_f fuci
,per_legislative_data_groups_tl pldgt
WHERE fut.user_table_id = fuc.user_table_id
AND fut.user_table_id = fur.user_table_id
AND trunc(Sysdate) BETWEEN fur.effective_start_date AND fur.effective_end_date
AND fuc.user_column_id = fuci.user_column_id
AND fur.user_row_id = fuci.user_row_id
AND trunc(Sysdate) BETWEEN fuci.effective_start_date AND fuci.effective_end_date
AND pldgt.legislative_data_group_id(+) = fut.legislative_data_group_id
AND pldgt.language(+)='US'
Query to get Assigned Payroll Name
Select papf.person_number,PAYROLL.PAYROLL_NAME
from
PER_ALL_PEOPLE_F PAPF,
PAY_PAY_RELATIONSHIPS_DN RELATIONSHIPS,
PAY_REL_GROUPS_DN TERMS,
PAY_ASSIGNED_PAYROLLS_DN ASSIGNED_PAYROLLS,
PAY_ALL_PAYROLLS_F PAYROLL
where PAPF.PERSON_ID = RELATIONSHIPS.PERSON_ID
AND RELATIONSHIPS.PAYROLL_RELATIONSHIP_ID = TERMS.PAYROLL_RELATIONSHIP_ID
and TERMS.GROUP_TYPE='T'
AND TERMS.RELATIONSHIP_GROUP_ID = ASSIGNED_PAYROLLS.PAYROLL_TERM_ID
AND ASSIGNED_PAYROLLS.PAYROLL_ID=PAYROLL.PAYROLL_ID;
from
PER_ALL_PEOPLE_F PAPF,
PAY_PAY_RELATIONSHIPS_DN RELATIONSHIPS,
PAY_REL_GROUPS_DN TERMS,
PAY_ASSIGNED_PAYROLLS_DN ASSIGNED_PAYROLLS,
PAY_ALL_PAYROLLS_F PAYROLL
where PAPF.PERSON_ID = RELATIONSHIPS.PERSON_ID
AND RELATIONSHIPS.PAYROLL_RELATIONSHIP_ID = TERMS.PAYROLL_RELATIONSHIP_ID
and TERMS.GROUP_TYPE='T'
AND TERMS.RELATIONSHIP_GROUP_ID = ASSIGNED_PAYROLLS.PAYROLL_TERM_ID
AND ASSIGNED_PAYROLLS.PAYROLL_ID=PAYROLL.PAYROLL_ID;
SQL Query to get associated sql query details OF Database Item
select fdi.DEFINITION_TEXT
,fr.text range_or_match
From FF_DATABASE_ITEMS fdi user_key_units
,FF_USER_ENTITIES fue user_column_name
,FF_ROUTES fr data_type
where fdi.USER_ENTITY_ID = fue.USER_ENTITY_ID user_row_name
and fr.ROUTE_ID = fue.ROUTE_ID user_row_name
and fdi.USER_NAME = 'ABS_EXT_ACR_BEGIN_BAL'-- DBI NAme user_row_name
How to schedule HCM Extraxt to run Sysdate+1
Below Query can be used in Refine HCM Extracts
Log in to the application as an HCM Administrator.
Navigator > Data Exchange > Refine HCM Extracts.
Select the Payroll Flow.
On the Manage Payroll Flow Patterns page, go to the Tasks tab, highlight the appropriate task, and click Edit.
Click the pencil icon below the tabs (Tasks, Task Sequence, Parameters).
Click the Edit pencil icon next to the task that you want to edit.
On the Edit Task Details: Basic Information flow, locate the Effective Date parameter and click on that row. The Effect Date: Parameter Details region will now show at the bottom of the screen.
In the Effect Date: Parameter Details region, set these parameter values:
Parameter Basis = Post SQL
SELECT NVL((SELECT flow_param_value
FROM pay_flow_param_values pfpv
, pay_flow_parameters pfp
WHERE pfpv.flow_instance_id =:pFlowInstanceId
And pfpv.base_flow_parameter_id = pfp.base_flow_parameter_id
And pfp.base_flow_parameter_name = 'EFFECTIVE_DATE'
And pfp.base_flow_id =:pFlowId
),(SYSDATE+1)) from dual
