SELECT PERS.PERSON_NUMBER AS PERSON_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 PERIOD_STRT_DT
,to_char(PY_PRD.END_DATE,'MM/DD/YYYY') AS PERIOD_END_DT
,ELEM_DET.ELEMENT_NAME AS ELEMNT_NAME
,ELEM_DET.REPORTING_NAME AS REP_NAME
,hr_general.decode_lookup ('PAY_UNITS', INP_VAL_DET.UOM) AS UOM
,INP_VAL_DET.BASE_NAME AS INPUT_VAL_NAME
,PY_RUN_RESLT.RESULT_VALUE AS RESLT_VALE
,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
