In this article, we will try to fetch the Payroll Run Results for a specific payroll period. We will try to fetch all input values for elements and not the reserved input value only.
SQL Query to fetch Payroll Run Results with all Input Values:
SELECT PERS.PERSON_NUMBER AS PERS_NBR
,PERS.FIRST_NAME AS FIRST_NAME
,PERS.LAST_NAME AS LAST_NAME
,PY_PRD.PERIOD_NAME AS PERD_NAME
,to_char(PY_PRD.START_DATE,'MM/DD/YYYY') AS PERD_STRT_DT
,to_char(PY_PRD.END_DATE,'MM/DD/YYYY') AS PERD_END_DT
,ELEM_DET.ELEMENT_NAME AS ELEMNT_NM
,ELEM_DET.REPORTING_NAME AS REP_NM
,hr_general.decode_lookup ('PAY_UNITS', INP_VAL_DET.UOM) AS UOM
,INP_VAL_DET.BASE_NAME AS INPUT_VAL_NM
,PY_RUN_RESLT.RESULT_VALUE AS RESLT_VAL
,PY_RUN_RESLT.RUN_RESULT_ID
,JOB_NM.NAME AS JOB_NAME
,LOC.LOCATION_NAME LOCATION_NAME
,INP_VAL_DET.DISPLAY_SEQUENCE
FROM (
SELECT RunResultValuePEO.RUN_RESULT_ID
,RunResultValuePEO.INPUT_VALUE_ID
,RunResultValuePEO.RESULT_VALUE
,PayrollActionPEO.PAYROLL_ACTION_ID AS PAYROLL_ACTION_ID470
,PayrollActionPEO.EFFECTIVE_DATE
,PayrollActionPEO.EARN_TIME_PERIOD_ID
,PayrollActionPEO.PAYROLL_ID
,PayrollRelationshipPEO.PERSON_ID
FROM FUSION.PAY_RUN_RESULT_VALUES RunResultValuePEO
,FUSION.PAY_RUN_RESULTS RunResultPEO
,FUSION.PAY_PAYROLL_REL_ACTIONS PayrollRelationshipActionPEO
,FUSION.PAY_PAYROLL_ACTIONS PayrollActionPEO
,FUSION.PAY_PAY_RELATIONSHIPS_DN PayrollRelationshipPEO
WHERE (
RunResultValuePEO.RUN_RESULT_ID = RunResultPEO.RUN_RESULT_ID
AND RunResultPEO.PAYROLL_REL_ACTION_ID = PayrollRelationshipActionPEO.PAYROLL_REL_ACTION_ID
AND PayrollRelationshipActionPEO.PAYROLL_ACTION_ID = PayrollActionPEO.PAYROLL_ACTION_ID
AND PayrollRelationshipActionPEO.PAYROLL_RELATIONSHIP_ID = PayrollRelationshipPEO.PAYROLL_RELATIONSHIP_ID
)
) PY_RUN_RESLT
,(
SELECT TimePeriodPEO.TIME_PERIOD_ID
,TimePeriodPEO.END_DATE
,TimePeriodPEO.PERIOD_NAME
,TimePeriodPEO.START_DATE
FROM FUSION.PAY_TIME_PERIODS TimePeriodPEO
) PY_PRD
,(SELECT PAYROLL_ID, payroll_name, effective_start_date, effective_end_date FROM pay_all_payrolls_f) PAP
,(
SELECT InputValueDPEO.INPUT_VALUE_ID
,InputValueDPEO.EFFECTIVE_END_DATE
,InputValueDPEO.EFFECTIVE_START_DATE
,InputValueDPEO.ELEMENT_TYPE_ID
,InputValueDPEO.UOM
,InputValueDPEO.BASE_NAME
,InputValueDPEO.DISPLAY_SEQUENCE
FROM FUSION.PAY_INPUT_VALUES_F InputValueDPEO
) INP_VAL_DET
,(
SELECT ElementTypeDPEO.ELEMENT_TYPE_ID
,ElementTypeDPEO.EFFECTIVE_END_DATE
,ElementTypeDPEO.EFFECTIVE_START_DATE
,ElementTypeTranslationPEO.ELEMENT_NAME
,ElementTypeTranslationPEO.REPORTING_NAME
,ElementTypeTranslationPEO.LANGUAGE AS LANGUAGE500
,ElementTypeTranslationPEO.ELEMENT_TYPE_ID AS ELEMENT_TYPE_ID1
FROM FUSION.PAY_ELEMENT_TYPES_F ElementTypeDPEO
,FUSION.PAY_ELEMENT_TYPES_TL ElementTypeTranslationPEO
WHERE (
ElementTypeDPEO.ELEMENT_TYPE_ID = ElementTypeTranslationPEO.ELEMENT_TYPE_ID
AND (
TRUNC(SYSDATE) BETWEEN ElementTypeDPEO.EFFECTIVE_START_DATE
AND ElementTypeDPEO.EFFECTIVE_END_DATE
)
)
) ELEM_DET
,(
SELECT AssignmentPEO.ASSIGNMENT_ID
,AssignmentPEO.EFFECTIVE_START_DATE
,AssignmentPEO.EFFECTIVE_END_DATE
,AssignmentPEO.EFFECTIVE_LATEST_CHANGE
,AssignmentPEO.EFFECTIVE_SEQUENCE
,AssignmentPEO.ASSIGNMENT_STATUS_TYPE
,AssignmentPEO.PERSON_ID
,AssignmentPEO.BUSINESS_UNIT_ID
,AssignmentPEO.JOB_ID
,AssignmentPEO.LOCATION_ID
FROM FUSION.PER_ALL_ASSIGNMENTS_M AssignmentPEO
WHERE (
(AssignmentPEO.EFFECTIVE_LATEST_CHANGE = 'Y')
AND (AssignmentPEO.PRIMARY_FLAG = 'Y')
AND AssignmentPEO.assignment_type = 'E'
)
) ASG
,(
SELECT PersonPEO.PERSON_ID AS PERSON_ID271
,PersonDetailsPEO.PERSON_ID AS PERSON_ID415
,PersonDetailsPEO.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE424
,PersonDetailsPEO.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE433
,PersonDetailsPEO.PERSON_NUMBER
,PersonNamePEO.PERSON_NAME_ID AS PERSON_NAME_ID564
,PersonNamePEO.EFFECTIVE_START_DATE AS EFFECTIVE_START_DATE574
,PersonNamePEO.EFFECTIVE_END_DATE AS EFFECTIVE_END_DATE584
,PersonNamePEO.LAST_NAME AS LAST_NAME
,PersonNamePEO.FIRST_NAME AS FIRST_NAME
FROM FUSION.PER_PERSONS PersonPEO
,FUSION.PER_ALL_PEOPLE_F PersonDetailsPEO
,FUSION.PER_PERSON_NAMES_F_V PersonNamePEO
WHERE (
PersonPEO.PERSON_ID = PersonDetailsPEO.PERSON_ID
AND PersonPEO.PERSON_ID = PersonNamePEO.PERSON_ID
AND (
TRUNC(SYSDATE) BETWEEN PersonDetailsPEO.EFFECTIVE_START_DATE
AND PersonDetailsPEO.EFFECTIVE_END_DATE
)
AND (
TRUNC(SYSDATE) BETWEEN PersonNamePEO.EFFECTIVE_START_DATE
AND PersonNamePEO.EFFECTIVE_END_DATE
)
)
) PERS
,(
SELECT JB.NAME
,JB.JOB_ID
FROM FUSION.PER_JOBS_F_TL JB
WHERE TRUNC(SYSDATE) BETWEEN JB.EFFECTIVE_START_DATE
AND JB.EFFECTIVE_END_DATE
) JOB_NM
,(
SELECT LOC1.LOCATION_ID
,LOCATN.INTERNAL_LOCATION_CODE
,LOC_DT.LOCATION_NAME
FROM FUSION.PER_LOCATION_DETAILS_F LOC1
,FUSION.PER_LOCATION_DETAILS_F_TL LOC_DT
,FUSION.PER_LOCATIONS LOCATN
WHERE TRUNC(SYSDATE) BETWEEN LOC1.EFFECTIVE_START_DATE
AND LOC1.EFFECTIVE_END_DATE
AND LOC1.LOCATION_DETAILS_ID = LOC_DT.LOCATION_DETAILS_ID
AND TRUNC(SYSDATE) BETWEEN LOC_DT.EFFECTIVE_START_DATE
AND LOC_DT.EFFECTIVE_END_DATE
AND LOC1.LOCATION_ID = LOCATN.LOCATION_ID
AND LOCATN.BUSINESS_GROUP_ID = LOC1.BUSINESS_GROUP_ID
AND LOC_DT.BUSINESS_GROUP_ID = LOC1.BUSINESS_GROUP_ID
) LOC
WHERE PY_RUN_RESLT.EARN_TIME_PERIOD_ID = PY_PRD.TIME_PERIOD_ID
AND PY_RUN_RESLT.INPUT_VALUE_ID = INP_VAL_DET.INPUT_VALUE_ID
AND INP_VAL_DET.ELEMENT_TYPE_ID = ELEM_DET.ELEMENT_TYPE_ID
AND PY_RUN_RESLT.PERSON_ID = ASG.PERSON_ID
AND ASG.PERSON_ID = PERS.PERSON_ID271(+)
AND PY_RUN_RESLT.PAYROLL_ID = PAP.PAYROLL_ID
AND ((COALESCE(null, :PAYROLL_NAME) is null) OR (PAP.PAYROLL_NAME IN (:PAYROLL_NAME)))
AND ((COALESCE(null, :PERIOD_NAME) is null) OR (PY_PRD.PERIOD_NAME IN (:PERIOD_NAME)))
AND ((COALESCE(null, :INPUT_NAME) is null) OR (INP_VAL_DET.BASE_NAME IN (:INPUT_NAME)))
AND ((COALESCE(null, :ELEMENT_NAME) is null) OR (ELEM_DET.ELEMENT_NAME IN (:ELEMENT_NAME)))
AND ASG.LOCATION_ID = LOC.LOCATION_ID(+)
AND ASG.JOB_ID = JOB_NM.JOB_ID(+)
AND PY_RUN_RESLT.EFFECTIVE_DATE >= INP_VAL_DET.EFFECTIVE_START_DATE
AND PY_RUN_RESLT.EFFECTIVE_DATE <= INP_VAL_DET.EFFECTIVE_END_DATE
AND ELEM_DET.LANGUAGE500 = 'US' AND ASG.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
--AND PY_PRD.END_DATE BETWEEN :FROM_DATE AND :TO_DATE
AND PY_RUN_RESLT.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE
order by PERS.PERSON_NUMBER, PY_RUN_RESLT.RUN_RESULT_ID, ELEM_DET.ELEMENT_NAME, INP_VAL_DET.DISPLAY_SEQUENCE
