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;