Employee End of Service Query R12 - Orapoint

Overview: this query is used to fetch employee end of service data in Oracle R12 Application.

select 
A.FULL_NAME,
ROUND(MODON_HR_PAY_GENERAL_PKG.GET_ELEMENTS_AMOUNT(A.BUSINESS_GROUP_ID,A.EMPLOYEE_NUMBER,'Basic Salary'),3) BASIC_SALARY
 ,b.MONTH_1 EOS_CURR_MONTH_VALUE,
 a.year_1 EOS_CURR_YEAR_VALUE
 ,ROUND((A.END_DATE- A.DATE_START)/365,3)YEARS_OF_SERVICE,A.EOS_ELIGIBILITY
 from (
 SELECT
 papf.person_id person_id,
     PAPF.FULL_NAME full_name,
     PBVV.VALUE year_1,
     PAPF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID,
     PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
     PTP.END_DATE END_DATE,
     PPOS.DATE_START DATE_START,
     PPG.SEGMENT8 EOS_ELIGIBILITY
 FROM
     pay_balance_values_v PBVV,
     PER_ALL_ASSIGNMENTS_F PAAF,
     PER_ALL_PEOPLE_F PAPF,
     PER_TIME_PERIODS PTP,PER_PERIODS_OF_SERVICE PPOS,
     PAY_PEOPLE_GROUPS PPG
 WHERE 1=1
 AND PPOS.PERSON_ID = PAPF.PERSON_ID
 and papf.business_group_id = :P_BUSINESS_GROUP_ID
     AND PBVV.balance_name LIKE 'END_OF_SERVICE_PROVISION_MODON_BAL'
     AND   database_item_suffix LIKE '_ASG_YTD'
     AND PAPF.PERSON_ID = NVL(:P_PERSON_ID,PAAF.PERSON_ID)
     AND PAPF.PERSON_ID = PAAF.PERSON_ID
     AND PBVV.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
     AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
     AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
     AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
     AND PAAF.PRIMARY_FLAG = 'Y'
     AND PTP.TIME_PERIOD_ID = :P_PERIOD_ID
     AND PTP.PAYROLL_ID =:P_PAYROLL_ID
     AND PBVV.EFFECTIVE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE
     AND PPG.PEOPLE_GROUP_ID = PAAF.PEOPLE_GROUP_ID
     AND PPG.SEGMENT8 = 'YES'
     )a
 ,    
     --AND PPA.PAYROLL_ACTION_ID = PBVV.PAYROLL_ACTION_ID
     --AND PTP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
     --UNION
     (SELECT
     papf.person_id person_id,
     PAPF.FULL_NAME full_name,
     PBVV.VALUE MONTH_1,
     PAPF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID,
     PAPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
     PTP.END_DATE END_DATE,
     PPOS.DATE_START DATE_START,
     PPG.SEGMENT8 EOS_ELIGIBILITY
 FROM
     pay_balance_values_v PBVV,
     PER_ALL_ASSIGNMENTS_F PAAF,
     PER_ALL_PEOPLE_F PAPF,
     PER_TIME_PERIODS PTP,PER_PERIODS_OF_SERVICE PPOS,
     PAY_PEOPLE_GROUPS PPG
 WHERE 1=1
 AND PPOS.PERSON_ID = PAPF.PERSON_ID
 and papf.business_group_id = :P_BUSINESS_GROUP_ID
     AND PBVV.balance_name LIKE 'END_OF_SERVICE_PROVISION_MODON_BAL'
     AND   database_item_suffix LIKE '_ASG_RUN'
     AND PAPF.PERSON_ID = NVL(:P_PERSON_ID,PAAF.PERSON_ID)
     AND PAPF.PERSON_ID = PAAF.PERSON_ID
     AND PBVV.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
     AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
     AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
     AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
     AND PAAF.PRIMARY_FLAG = 'Y'
     AND PTP.TIME_PERIOD_ID = :P_PERIOD_ID
     AND PTP.PAYROLL_ID =:P_PAYROLL_ID
     AND PBVV.EFFECTIVE_DATE BETWEEN PTP.START_DATE AND PTP.END_DATE
     AND PPG.PEOPLE_GROUP_ID = PAAF.PEOPLE_GROUP_ID
     AND PPG.SEGMENT8 = 'YES'
     )b
     --AND PPA.PAYROLL_ACTION_ID = PBVV.PAYROLL_ACTION_ID
     --AND PTP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
     where a.person_id = b.person_id
     order by a.employee_number asc;