To fetch the count of employee relationship.(Education allowance report).

 /* Name: Education allowance report Creation DATE: 27-07-2020 

Created by : Partha*/

SELECT 

rownum,

PAP.PAYROLL_NAME,

PAPF.PERSON_NUMBER,

PPNF.FULL_NAME PPNF_US,

PPNF_AR.FULL_NAME PPNF_AR,

ELE.ELEMENT_NAME,

PEA.EMAIL_ADDRESS,

--PEC.BASE_CLASSIFICATION_NAME,

--PIV.NAME INPUT_VALUE_NAME,

--PPA.DATE_EARNED,

TO_CHAR(PPA.DATE_EARNED,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "DATE EARNED",

--TO_CHAR(PPA.EFFECTIVE_DATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "EFFECTIVE_DATE",

--PTP.PERIOD_NAME ,

PRRV.RESULT_VALUE,

(

SELECT LEAST (count (PCR.CONTACT_TYPE),3) "count"

--SELECT count (PCR.CONTACT_TYPE) "count"

--EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM PP.DATE_OF_BIRTH) AS "AGE"

FROM PER_CONTACT_RELSHIPS_F PCR,

PER_PERSON_NAMES_F PPNF1,

PER_PERSONS PP,

PER_ALL_PEOPLE_F PAPF1

WHERE PPNF1.PERSON_ID = PCR.CONTACT_PERSON_ID

AND PP.PERSON_ID = PPNF1.PERSON_ID

AND PAPF1.PERSON_ID = PP.PERSON_ID 

AND TRUNC(SYSDATE)  BETWEEN PPNF1.EFFECTIVE_START_DATE  AND PPNF1.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE)  BETWEEN PCR.EFFECTIVE_START_DATE  AND PCR.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE)  BETWEEN PAPF1.EFFECTIVE_START_DATE  AND PAPF1.EFFECTIVE_END_DATE

AND PCR.CONTACT_TYPE = 'C'

AND PPNF1.NAME_TYPE = 'GLOBAL'

--AND PCR.PERSON_ID IN (SELECT PERSON_ID FROM PER_ALL_PEOPLE_F WHERE PERSON_NUMBER = '15454')

AND PCR.PERSON_ID = PPNF.PERSON_ID

AND EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM PP.DATE_OF_BIRTH)  BETWEEN 3 AND 22

) COUNT

--PPA.PAYROLL_ACTION_ID

---------------------------------------------FROM----------------------------------

FROM 

PAY_ELEMENT_TYPES_VL ELE,

PAY_RUN_RESULTS PRR, 

PAY_RUN_RESULT_VALUES PRRV,

PAY_INPUT_VALUES_VL PIV,

PER_PERSON_NAMES_F PPNF,

PER_PERSON_NAMES_F PPNF_AR,

PER_PERSON_SECURED_LIST_V   PAPF,

PAY_PAYROLL_REL_ACTIONS PPRA,

PAY_PAY_RELATIONSHIPS_DN PREL,

PAY_PAYROLL_ACTIONS PPA,

PAY_ALL_PAYROLLS_F PAP,

PAY_TIME_PERIODS PTP,

PAY_ELE_CLASSIFICATIONS PEC,

PER_EMAIL_ADDRESSES PEA

--------------------------------------------WHERE STATEMENT--------------------------------------

WHERE 

PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID

AND PPA.PAYROLL_ID = PAP.PAYROLL_ID

AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID

--AND PPRA.RUN_TYPE_ID IS NOT NULL

AND PRR.ELEMENT_TYPE_ID = ELE.ELEMENT_TYPE_ID

AND PRRV.RUN_RESULT_ID(+) = PRR.RUN_RESULT_ID

AND PIV.INPUT_VALUE_ID(+) = PRRV.INPUT_VALUE_ID

AND PREL.PAYROLL_RELATIONSHIP_ID = PPRA.PAYROLL_RELATIONSHIP_ID

AND PREL.PERSON_ID = PPNF.PERSON_ID

AND PAPF.PERSON_ID = PPNF.PERSON_ID

AND PAPF.PERSON_ID = PPNF_AR.PERSON_ID

AND PEA.PERSON_ID = PAPF.PERSON_ID

--AND PPNF.NAME_TYPE= 'GLOBAL'

AND PPNF.CHAR_SET_CONTEXT = 'US'

AND PPNF_AR.CHAR_SET_CONTEXT = 'AR'

AND PPA.PAYROLL_ID = PTP.PAYROLL_ID

AND PPA.EFFECTIVE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE

AND  PPA.EFFECTIVE_DATE  BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND  PPA.EFFECTIVE_DATE  BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

AND  PPA.EFFECTIVE_DATE  BETWEEN PPNF_AR.EFFECTIVE_START_DATE AND PPNF_AR.EFFECTIVE_END_DATE

AND PTP.STATUS='O'

AND  PPA.EFFECTIVE_DATE BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE

AND  PPA.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE

AND  PPA.EFFECTIVE_DATE BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE

AND PIV.NAME= 'Pay Value'

AND PEC.CLASSIFICATION_ID=ELE.CLASSIFICATION_ID

AND  PIV.UOM = 'M'

--AND PJL.LANGUAGE = 'US'

--AND  PPRA.ACTION_STATUS = 'C'

AND  PPA.ACTION_STATUS IN ('C', 'E')

AND PPRA.RETRO_COMPONENT_ID IS NULL

AND PEA.EMAIL_TYPE = 'W1'

--------------------------------------PARAMETER--------------------------------

AND  PPA.EFFECTIVE_DATE  BETWEEN :START_DATE AND :END_DATE

AND PAPF.PERSON_NUMBER  = nvl(:P_PERSON_NUMBER,PAPF.PERSON_NUMBER)

AND ELE.ELEMENT_NAME = :ELEMENT_NAME

ORDER BY ROWNUM DESC