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

To fetch the payroll run result value for the particular element. ( Appreciation Bonus Letterv2)

/* Name: Appreciation Bonus Letterv2 Creation DATE: 30-06-2020

created by : Partha */

SELECT 

rownum,

PAP.PAYROLL_NAME,

PAPF.PERSON_NUMBER,

PPNF.FULL_NAME,

ELE.ELEMENT_NAME,

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,

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_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

--------------------------------------------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 PPNF.NAME_TYPE= 'GLOBAL'

--AND PPNF.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 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

--------------------------------------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

To fetch the assignment details as eff date and the accrual balance.

 /*Report Name: Annual Balance Healthy Check V2 DATE: 30-05-2020 

Created BY: Partha */

select rownum,

PAPF.PERSON_NUMBER,

PPNF.FULL_NAME,

PLE.NAME LE_NAME,

PJ.NAME JOB,

PGF.NAME GRADE,

HAP.NAME POSITION,

HTL.NAME DEPARTMENT,

PAAF.assignment_number,

TO_CHAR(PPOS.DATE_START,'DD-MM-YYYY') AS "HIRE DATE",

TO_CHAR(PPOS.ACTUAL_TERMINATION_DATE,'DD-MM-YYYY') AS "Termination DATE",

HVL.LOCATION_NAME LOCATION,

---------------------------CURRENT_BALANCE----------------------

nvl(round((SELECT SUM(APAE.VALUE) BAL

--AAPV.NAME 

                   FROM ANC_PER_ACRL_ENTRY_DTLS APAE,

   ANC_ABSENCE_PLANS_VL  AAPV 

                  WHERE APAE.PERSON_ID = PAPF.PERSON_ID

  /*(SELECT PAPF1.PERSON_ID FROM PER_ALL_PEOPLE_F PAPF1 

WHERE TRUNC(:eff_date) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE

AND PAPF1.PERSON_NUMBER = :P_PERSON_NUMBER)

*/

AND APAE.PL_ID =AAPV.ABSENCE_PLAN_ID(+)

                   -- AND APAE.PROCD_DATE = NVL(:eff_date,SYSDATE)

   AND APAE.PROCD_DATE <= :eff_date

   AND UPPER(AAPV.NAME) ='ANNUAL LEAVE'

   AND AAPV.PLAN_STATUS = 'A'

--AND (:eff_date)BETWEEN APAE.PROCD_DATE AND APAE.PROCD_DATE

),

                 2),

           0)  CURRENT_BALANCE,

 

----------------------------DURATION-----------------------

   /*

   NVL((select sum(ABE.DURATION) taken_leave

from ANC_PER_ABS_ENTRIES ABE

where ABE.PERSON_ID = PAPF.PERSON_ID

AND ABE.ABSENCE_TYPE_ID IN ('300000112735590','300000112735532')

AND ABE.ABSENCE_STATUS_CD = 'SUBMITTED'

AND ABE.START_DATE <= :eff_date

--AND TRUNC(:eff_date) BETWEEN ABE.START_DATE AND ABE.END_DATE

),

0) DURATION,

*/

------------------------CONSUMED_DAYS-----------------------

nvl(round((SELECT SUM(APAE.VALUE) BAL

                   FROM ANC_PER_ACRL_ENTRY_DTLS APAE

                  WHERE APAE.PERSON_ID = PAPF.PERSON_ID

                    AND APAE.TYPE = 'ABS'

--and APAE.pl_id = 300000112735584

                    AND APAE.PROCD_DATE <= :eff_date 

),

                 2),

           0) *(-1) CONSUMED_DAYS,

/*

(SELECT SUM(APAS.used) 

FROM ANC_PER_ACCRUAL_ENTRIES APAS

WHERE APAS.PERSON_ID = PAPF.PERSON_ID

AND ACCRUAL_PERIOD <= :eff_date

--AND APAS.PLAN_ID = '300000112735584'

) CONSUMED_DAYS,

*/

------------------------------SYSTEM_ACCRUAL-------------------------------------

nvl(round((SELECT SUM(APAE.VALUE) BAL

--AAPV.NAME 

                   FROM ANC_PER_ACRL_ENTRY_DTLS APAE,

   ANC_ABSENCE_PLANS_VL  AAPV 

                  WHERE APAE.PERSON_ID = PAPF.PERSON_ID

  /*(SELECT PAPF1.PERSON_ID FROM PER_ALL_PEOPLE_F PAPF1 

WHERE TRUNC(:eff_date) BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE

AND PAPF1.PERSON_NUMBER = :P_PERSON_NUMBER)

*/

AND APAE.PL_ID =AAPV.ABSENCE_PLAN_ID(+)

                   -- AND APAE.PROCD_DATE = NVL(:eff_date,SYSDATE)

   AND APAE.PROCD_DATE <= :eff_date

   AND UPPER(AAPV.NAME) ='ANNUAL LEAVE'

   AND AAPV.PLAN_STATUS = 'A'

--AND (:eff_date)BETWEEN APAE.PROCD_DATE AND APAE.PROCD_DATE

),

                 2),

           0)

   

   +

   nvl(round((SELECT SUM(APAE.VALUE) BAL

                   FROM ANC_PER_ACRL_ENTRY_DTLS APAE

                  WHERE APAE.PERSON_ID = PAPF.PERSON_ID

                    AND APAE.TYPE = 'ABS'

--and APAE.pl_id = 300000112735584

                    AND APAE.PROCD_DATE <= :eff_date 

),

                 2),

           0) *(-1) SYSTEM_ACCRUAL

from

PER_ALL_PEOPLE_F PAPF,

    PER_PERSON_NAMES_F PPNF,

PER_ALL_ASSIGNMENTS_F PAAF,

PER_GRADES_F_VL PGF,

PER_JOBS_F_TL PJ,

HR_ALL_POSITIONS_F_TL HAP,

HR_ORGANIZATION_UNITS_F_TL HTL,

PER_PERIODS_OF_SERVICE PPOS,

PER_LEGAL_EMPLOYERS PLE,

HR_LOCATIONS_ALL_F_VL HVL

WHERE

PAPF.PERSON_ID = PPNF.PERSON_ID

AND PAAF.PERSON_ID = PAPF.PERSON_ID

AND PAAF.PERSON_ID = PPNF.PERSON_ID

AND PJ.JOB_ID = PAAF.JOB_ID

AND PGF.GRADE_ID = PAAF.GRADE_ID

AND HAP.POSITION_ID = PAAF.POSITION_ID

AND HTL.ORGANIZATION_ID = PAAF.ORGANIZATION_ID

AND PAAF.PERSON_ID = PPOS.PERSON_ID

AND PAAF.LEGAL_ENTITY_ID = PLE.ORGANIZATION_ID

AND PPOS.ACTUAL_TERMINATION_DATE IS NULL

AND HVL.LOCATION_ID = PAAF.LOCATION_ID

AND PAAF.ASSIGNMENT_NUMBER = nvl(:Per_num,PAAF.ASSIGNMENT_NUMBER)

---------------------------------DATE CONDITION----------------------

AND TRUNC(:eff_date) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN PJ.EFFECTIVE_START_DATE AND PJ.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN PGF.EFFECTIVE_START_DATE AND PGF.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN HAP.EFFECTIVE_START_DATE AND HAP.EFFECTIVE_END_DATE

AND TRUNC(:eff_date) BETWEEN HTL.EFFECTIVE_START_DATE AND HTL.EFFECTIVE_END_DATE

AND TRUNC (:eff_date) BETWEEN PLE.EFFECTIVE_START_DATE AND PLE.EFFECTIVE_END_DATE

AND TRUNC (:eff_date) BETWEEN HVL.EFFECTIVE_START_DATE AND HVL.EFFECTIVE_END_DATE

--AND TRUNC (SYSDATE) BETWEEN APTL.EFFECTIVE_START_DATE AND APTL.EFFECTIVE_END_DATE

--------------------------------LANGUAGE--------------------------------

AND PPNF.NAME_TYPE = 'GLOBAL'

AND PJ.LANGUAGE = 'US'

AND HAP.LANGUAGE = 'US'

AND HTL.LANGUAGE = 'US'

--AND APTL.LANGUAGE = 'US'

-- AND PAPF.PERSON_NUMBER = nvl(:Per_num,PAPF.PERSON_NUMBER)

--AND HVL.LANGUAGE = 'US'

order by rownum desc

To fetch Manager rating of performance document and the run result value for the particular document. (Annual bonus letter)

 /* Name : Annual leave Bonus v3 DATE: 23-03-2020

CREATED BY : PARTHA

This report is to get the employee's performance rating and their annual bonus amount (annual bonus =element)

This report won't return the result if the run result doesn't exist. Commented 2019 hardcoded at the review period.

*/

SELECT

PAPF.PERSON_NUMBER,

PPF1.FULL_NAME AS "US NAME",

PPF2.FULL_NAME AS "AR NAME",

HVL2.CUSTOMARY_NAME AS "DOCUMENT NAME",

HVL2.DOCUMENT_TYPE_NAME "DOCUMENT TYPE",

HRTL.REVIEW_PERIOD_NAME,

ROUND(HVL2.CALCULATED_OVERALL_RATING)AS "CALCULATED_Manager_Rating",

-----------------------------------------MANAGER_OVER_ALL_RATING------------------------------------------------

(SELECT HRL.RATING_LEVEL_CODE MGR_RATING

FROM hra_eval_ratings HER, HRT_RATING_LEVELS_B HRL, hra_eval_sections HES,hra_evaluations HEVAL

WHERE 1=1

AND HES.EVALUATION_ID = HEVAL.EVALUATION_ID

AND HES.section_type_code = 'OS'

AND HER.evaluation_id = HES.EVALUATION_ID

and HER.role_type_code = 'MANAGER'

and HER.reference_id = HES.eval_section_id

AND HRL.rating_level_id=HER.PERFORMANCE_RATING_ID

AND HEVAL.TMPL_PERIOD_ID = HTPV.TMPL_PERIOD_ID

AND HEVAL.TMPL_PERIOD_ID = HVL2.TMPL_PERIOD_ID

AND HEVAL.ASSIGNMENT_ID=PAAM.ASSIGNMENT_ID

AND HEVAL.ASSIGNMENT_ID = HVL2.ASSIGNMENT_ID) AS "Manager Overall Rating",

----------------------------------------------Annual Bonus Amount-------------------------------------------

(SELECT

PRRV.RESULT_VALUE AS "Result"

FROM 

PAY_ELEMENT_TYPES_VL ELE,

PAY_RUN_RESULTS PRR, 

PAY_RUN_RESULT_VALUES PRRV,

PAY_INPUT_VALUES_VL PIV,

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 

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 = PPF1.PERSON_ID

AND PREL.PERSON_ID = PPF2.PERSON_ID

AND PAPF.PERSON_ID = PPF1.PERSON_ID

AND PAPF.PERSON_ID = PPF2.PERSON_ID

AND PPA.PAYROLL_ID = PTP.PAYROLL_ID

AND PTP.STATUS='O'

AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE

AND PTP.STATUS='O'

AND  PPA.DATE_EARNED BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE

AND PAP.PAYROLL_NAME IN (:PAYROLL)

AND PPA.EFFECTIVE_DATE IN (:Payroll_Process_Date)

AND  ELE.ELEMENT_NAME IN (:ELEMENT_NAME)

AND PIV.NAME= 'Pay Value' 

AND PEC.CLASSIFICATION_ID=ELE.CLASSIFICATION_ID

AND  PPRA.ACTION_STATUS = 'C'

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

AND PPRA.RETRO_COMPONENT_ID IS NULL) as "Annual Bonus",

------------------------------------------------DESCRIPTION----------------------------------------------------

/*

CASE WHEN (HVL2.OVERALL_RATING IS NULL) 

THEN  

(SELECT 

HRVL.rating_description 

FROM 

HRT_RATING_LEVELS_VL HRVL,

HRA_OVERALL_RATINGS_VL HVL

WHERE 

HVL.assignment_id=PAAM.ASSIGNMENT_ID

AND HVL.ROLE_TYPE_CODE='MANAGER'

AND HVL.TMPL_PERIOD_ID=HTPV.TMPL_PERIOD_ID

AND HRVL.RATING_MODEL_ID='300000068287301'

AND ROUND(HVL.CALCULATED_OVERALL_RATING,0)=HRVL.NUMERIC_RATING)

ELSE

(SELECT HRLL.RATING_DESCRIPTION FROM HRT_RATING_LEVELS_VL HRLL, hra_eval_ratings HER, HRT_RATING_LEVELS_B HRL, hra_eval_sections HES,hra_evaluations HEVAL

WHERE HRLL.rating_level_id = HRL.rating_level_id

AND HES.EVALUATION_ID = HEVAL.EVALUATION_ID

AND HES.section_type_code = 'OS'

AND HER.evaluation_id = HES.EVALUATION_ID

and HER.role_type_code = 'MANAGER'

and HER.reference_id = HES.eval_section_id

AND HRL.rating_level_id=HER.PERFORMANCE_RATING_ID

AND HEVAL.TMPL_PERIOD_ID = HTPV.TMPL_PERIOD_ID

AND HEVAL.TMPL_PERIOD_ID = HVL2.TMPL_PERIOD_ID

AND HEVAL.ASSIGNMENT_ID=PAAM.ASSIGNMENT_ID

AND HEVAL.ASSIGNMENT_ID = HVL2.ASSIGNMENT_ID)

END AS "DESCRIPTION"

*/

-----------------------------------------------MAIN FROM------------------------------------------------------

FROM 

PER_ALL_PEOPLE_F PAPF,

PER_PERSON_NAMES_F PPF1,

PER_PERSON_NAMES_F PPF2,

PER_ALL_ASSIGNMENTS_M PAAM,

HRA_OVERALL_RATINGS_VL HVL2,

HRT_REVIEW_PERIODS_TL HRTL,

HRA_TMPL_PERIODS_VL HTPV

-------------------------------------------------MAIN WHERE-----------------------------------------------------

WHERE 

PAPF.PERSON_ID = PPF1.PERSON_ID

AND PAPF.PERSON_ID = PPF2.PERSON_ID

AND PAPF.PERSON_ID = PAAM.PERSON_ID

AND PPF1.PERSON_ID = PAAM.PERSON_ID

AND PPF2.PERSON_ID = PAAM.PERSON_ID

AND PPF1.PERSON_ID = PPF2.PERSON_ID

AND HVL2.WORKER_ID = PAPF.PERSON_ID

AND HVL2.WORKER_ID = PPF1.PERSON_ID

AND HVL2.WORKER_ID = PPF2.PERSON_ID

AND HVL2.WORKER_ID = PAAM.PERSON_ID

AND HVL2.ASSIGNMENT_ID = PAAM.ASSIGNMENT_ID

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

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

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

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

AND HRTL.REVIEW_PERIOD_ID = HTPV.REVIEW_PERIOD_ID

AND HVL2.TMPL_PERIOD_ID = HTPV.TMPL_PERIOD_ID

AND PPF1.CHAR_SET_CONTEXT = 'US'

AND PPF2.CHAR_SET_CONTEXT = 'AR'

AND HVL2.ROLE_TYPE_CODE = 'MANAGER'

AND PAAM.ASSIGNMENT_TYPE = 'E'

AND HRTL.LANGUAGE = 'US'

AND (SELECT

PRRV.RESULT_VALUE AS "Result"

FROM 

PAY_ELEMENT_TYPES_VL ELE,

PAY_RUN_RESULTS PRR, 

PAY_RUN_RESULT_VALUES PRRV,

PAY_INPUT_VALUES_VL PIV,

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 

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 = PPF1.PERSON_ID

AND PREL.PERSON_ID = PPF2.PERSON_ID

AND PAPF.PERSON_ID = PPF1.PERSON_ID

AND PAPF.PERSON_ID = PPF2.PERSON_ID

AND PPA.PAYROLL_ID = PTP.PAYROLL_ID

AND PTP.STATUS='O'

AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE

AND PTP.STATUS='O'

AND  PPA.DATE_EARNED BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE

AND PAP.PAYROLL_NAME IN (:PAYROLL)

AND PPA.EFFECTIVE_DATE IN (:Payroll_Process_Date)

AND  ELE.ELEMENT_NAME IN (:ELEMENT_NAME)

AND PIV.NAME= 'Pay Value' 

AND PEC.CLASSIFICATION_ID=ELE.CLASSIFICATION_ID

AND  PPRA.ACTION_STATUS = 'C'

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

AND PPRA.RETRO_COMPONENT_ID IS NULL) IS NOT NULL

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

AND PAPF.PERSON_NUMBER = NVL(:PERSON_NUMBER,PAPF.PERSON_NUMBER)

AND HRTL.REVIEW_PERIOD_NAME IN (:REVIEW_PERIOD)

AND HVL2.DOCUMENT_TYPE_NAME IN (:Reference_Rating_Document_Type)

ORACLE Created Reports and queries:

                                    

1) Bursting queries with example.

2) To fetch the Manager's overall rating and payroll run result value (Annual Bonus letter).

3) To fetch the assignment details as of the effective date and their accrual balance.(Health check report).

4) To fetch the elements Payroll run result values. (Appreciation Bonus Letter).

5) To fetch the count of employee's contact relationships and their run result value(Education allowance report).

6)  Active and Terminated Employee Details

7) Annual Bonus Report (To fetch the payroll run result value of an element)

8) Active Employee Report (To find all the assignment and personal details)

ORACLE BURSTING QUERIES

 

                            Oracle Bursting Query

What is Bursting:

Bursting is a process of splitting data into blocks, generating documents for each block, and delivering the documents to one or more destinations.

We use bursting to trigger a mail for multiple people at the same time. The data for the report is generated by executing a query once and then splitting the data based on a Key value. For each block of the data, a separate document is generated and delivered.

What is Bursting Defenition :

A bursting definition is a component of the data model. After you have defined the data sets for the data model, you can set up one or more bursting definitions.

When you set up a bursting definition, you define the following:

• The Split By element is an element from the data that governs how the data is split. For example, to split a batch of invoices by each invoice, you may use an element called CUSTOMER_ID. The data set must be sorted or grouped by this element.

• The Deliver By element is the element from the data that governs how formatting and delivery options are applied. In the invoice example, it is likely that each invoice has delivery criteria determined by the customer; therefore, the Deliver By element would also be CUSTOMER_ID.

• The Delivery Query is a SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details.

SQL Query for the Bursting Query :

---------------------------------------------------------------------------------------------------------------------

SELECT 

PAPF.PERSON_NUMBER  KEY, --- MUST BE SAME AS DELIVER BY

'NMC_ACCRUAL_NOTIFICATION' TEMPLATE, --  REPORT TEMPLATE NAME

'EN-US' LOCALE, -- TEMPLATE FORMAT

'HTML' OUTPUT_FORMAT, -- OUTPUT FORMAT PDF RTF TEXT ETC..

PAPF.PERSON_NUMBER||' ' OUTPUT_NAME, --PDF OUTPUT NAME

'EMAIL' DEL_CHANNEL, -- DELIVERY CHANNEL NAME EMAIL,FAX,PRINT ETC..

PEA.EMAIL_ADDRESS PARAMETER1, -- TO MAIL ADDRESS PEA.EMAIL_ADDRESS

'' PARAMETER2, -- CC MAIL ADDRESS

'DONOTREPLY@GMAIL.COM' PARAMETER3,  -- FROM MAIL ADDRESS

PAPF.PERSON_NUMBER||'This mail is triggered by bursting' PARAMETER4 -- SUBJECT

FROM 

PER_PERSON_SECURED_LIST_V PAPF,

PER_ALL_ASSIGNMENTS_F PAAF,

PER_EMAIL_ADDRESSES PEA

WHERE 1 = 1

AND PAAF.PERSON_ID = PAPF.PERSON_ID

AND PEA.PERSON_ID= PAPF.PERSON_ID

AND PAPF.PRIMARY_EMAIL_ID= PEA.EMAIL_ADDRESS_ID

AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE  AND PAPF.EFFECTIVE_END_DATE

AND SYSDATE  BETWEEN PAAF.EFFECTIVE_START_DATE  AND PAAF.EFFECTIVE_END_DATE

AND PAAF.ASSIGNMENT_TYPE = 'E'

AND PAAF.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')

--AND PAAF.DATE_PROBATION_END<SYSDATE

--AND (PAPF.PERSON_NUMBER =:P_PERSON_NUMBER OR :P_PERSON_NUMBER IS NULL ) --Parameter

-------------------------------------------------------------------------------------------------------------------------------------

This is the bursting which we use to burst the letters in the PDF formation or in the HTML format and etc.

There are many types of formats to burst the report. Please find below the formates.

Bursting Formats and their values to be entered in the bursting queries:

Output Format	Value to Enter in Bursting Query	Template Types That Can Generate This Output Format
Interactive	N/A	Not supported for bursting
HTML	html	BI Publisher, RTF, XSL Stylesheet (FO)
PDF	pdf	BI Publisher, RTF, PDF, Flash, XSL Stylesheet (FO)
RTF	rtf	BI Publisher, RTF, XSL Stylesheet (FO)
Excel (mhtml)	excel	BI Publisher, RTF, Excel, XSL Stylesheet (FO)
Excel (html)	excel2000	BI Publisher, RTF, Excel, XSL Stylesheet (FO)
Excel (*.xlsx)	xlsx	BI Publisher, RTF, XSL Stylesheet (FO)
PowerPoint (mhtml)	ppt	BI Publisher, RTF, XSL Stylesheet (FO)
PowerPoint (.*pptx)	pptx	BI Publisher, RTF, XSL Stylesheet (FO)
MHTML	mhtml	BI Publisher, RTF, Flash, XSL Stylesheet (FO)
PDF/A	pdfa	BI Publisher, RTF, XSL Stylesheet (FO)
PDF/X	pdfx	BI Publisher, RTF, XSL Stylesheet (FO)
Zipped PDFs	pdfz	BI Publisher, RTF, PDF, XSL Stylesheet (FO)
FO Formatted XML	xslfo	BI Publisher, RTF, XSL Stylesheet (FO)
Data (XML)	xml	BI Publisher, RTF, PDF, Excel, Flash, XSL Stylesheet (FO), Etext, XSL Stylesheet (HTML XML/Text)
Data (CSV)	csv	BI Publisher, RTF, PDF, Excel, Flash, XSL Stylesheet (FO), XSL Stylesheet (HTML XML/Text), Etext
XML	txml	XSL Stylesheet (HTML XML/Text)
Text	text	XSL Stylesheet (HTML XML/Text), Etext
Flash	flash	Flash

Oracle referred documents:

Basics of the ORacle Bursting query :

https://docs.oracle.com/middleware/12213/bip/BIPDM/GUID-A926D588-426F-47C7-8A9A-B0A7959C5814.htm#BIPDM350

-------------------------------------------------------------------------------------------------------------------

                                                    Thank You

                            Please comment for more information

Oracle Basic Queries (BI)

 

Contents

Select Statement: 1

Example In the BI Queries: 1

Querying All Data: 1

Examples in the BI queries: 1

Selecting Specific Columns: 2

Examples in BI Queries: 2

Selecting Rows with WHERE condition: 2

Examples in BI Queries: 2

Operators: 3

 

Select Statement:

All the Query to search the result begins from the select statement.

Syntax:

SELECT column_names FROM table_name [WHERE condition]

Only the SELECT and FROM clauses are mandatory. If your query does not have a WHERE clause, the result will include all rows in the table. If your query has a WHERE clause then only the rows satisfying the WHERE condition will be returned.

 

Example In the BI Queries:

 

Select person_number from per_all_people_f

Select person_id from Per_all_assignments_m

 

Querying All Data:

The asterisk (*) means all columns in the table.  Shows all columns from the table.

 

Syntax:

SELECT * FROM table_name [WHERE condition]

 

Examples in the BI queries:

 

Select * from per_all_people_f

Select * from per_All_assignments_m 

Selecting Specific Columns:

 

To query specific columns, list the columns in the SELECT clause. Suppose you want multiple columns in order then list the columns one by one in the select clause.

Syntax:

SELECT Column_name_1, Column_name_2  FROM table_name

 

Examples in BI Queries:

 

Select person_id , person_number from per_all_people_f

Select assignment_id , person_id from per_all_assignments_m

 

Selecting Rows with WHERE condition:

 

To query a specific record or rows, where conditions are used.

Syntax:

SELECT Column_name_1, Column_name_2  FROM table_name [WHERE condition]

 

 

Examples in BI Queries:

 

Select * from per_all_people_f

Where person_number = ‘100’   à Result: All the columns for the record 100 will appear.

Operators:

 

Operator	Description
=	Equal to
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
!=	Not equal to