• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 16, 2020
  • Reading time:7 mins read
You are currently viewing Most Frequently Used SQLs for List of Values (LOVs)
Most Frequently Used SQLs for List of Values (LOVs)

Below are the list of SQLs that are most frequently used to get the list of values for business objects.

Legislative Data Group:
Select name from per_legislative_data_groups_vl
Secured Persons List:
SELECT DISTINCT PERSON_NUMBER FROM PER_PERSON_SECURED_LIST_V
           
            

Secured Departments List: SELECT DISTINCT SUBSTR(DEPT.NAME,1,INSTR(DEPT.NAME,’-‘,1)-1) DEPCODE FROM PER_DEPARTMENT_SECURED_LIST_V SEC_DEPT, PER_DEPARTMENTS DEPT WHERE DEPT.ORGANIZATION_ID=SEC_DEPT.ORGANIZATION_ID Payroll Name: select distinct payroll_name from pay_all_payrolls_f where TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date Country Codes: select country_code, GEOGRAPHY_ELEMENT1 from HZ_GEOGRAPHIES where GEOGRAPHY_TYPE = ‘COUNTRY’ Balance Category: select distinct USER_CATEGORY_NAME from PAY_BALANCE_CATEGORIES_VL Pay Action Status Lookups: SELECT meaning FROM hcm_lookups WHERE lookup_type = ‘PAY_ACTION_STATUS’ Payroll Flow Name: SELECT pfi.instance_name FROM pay_flow_instances pfi ORDER BY creation_date desc Element Classification: SELECT DISTINCT c.classification_name Classfication FROM pay_ele_classifications_vl c, per_legislative_data_groups_vl l WHERE c.legislation_code = l.legislation_code AND l.name IN (:P_LDG) ORDER BY c.classification_name Pay Periods: select ptp.period_name from pay_time_periods ptp, pay_all_payrolls_f pp where pp.payroll_id = ptp.payroll_id and pp.payroll_name = :OOS_PAYROLL and sysdate between pp.effective_start_date and pp.effective_end_date and ptp.period_category = ‘E’ and ( substr(ptp.period_name, instr(ptp.period_name, ‘ ‘, 1)+1, 4) = to_char(sysdate, ‘YYYY’) OR substr(ptp.period_name, instr(ptp.period_name, ‘ ‘, 1)+1, 4) = to_char(sysdate, ‘YYYY’) – 1 OR substr(ptp.period_name, instr(ptp.period_name, ‘ ‘, 1)+1, 4) = to_char(sysdate, ‘YYYY’) + 1) order by ptp.time_period_id Legal Employers: SELECT hauft.NAME FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE AND hauft.LANGUAGE = ‘US’ AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE AND houcf.CLASSIFICATION_CODE = ‘HCM_LEMP’ AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date Payroll Statutory Units (PSUs): SELECT hauft.NAME FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE AND hauft.LANGUAGE = ‘US’ AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE AND houcf.CLASSIFICATION_CODE = ‘HCM_PSU’ AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date Divisions: SELECT hauft.NAME FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE AND hauft.LANGUAGE = ‘US’ AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE AND houcf.CLASSIFICATION_CODE = ‘HCM_DIVISION’ AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date Business Units: SELECT hauft.NAME FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE AND hauft.LANGUAGE = ‘US’ AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE AND houcf.CLASSIFICATION_CODE = ‘FUN_BUSINESS_UNIT’ AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date Legal Reporting Units (LRUs): SELECT hauft.NAME FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE AND hauft.LANGUAGE = ‘US’ AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE AND houcf.CLASSIFICATION_CODE = ‘HCM_LRU’ AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date Tax Reporting Units (TRUs): SELECT hauft.NAME FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE AND hauft.LANGUAGE = ‘US’ AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE AND houcf.CLASSIFICATION_CODE = ‘HCM_TRU’ AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date Enterprise: SELECT hauft.NAME FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE AND hauft.LANGUAGE = ‘US’ AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE AND houcf.CLASSIFICATION_CODE = ‘HCM_TRU’ AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date Departments: SELECT hauft.NAME FROM HR_ORG_UNIT_CLASSIFICATIONS_F houcf, HR_ALL_ORGANIZATION_UNITS_F haouf, HR_ORGANIZATION_UNITS_F_TL hauft WHERE haouf.ORGANIZATION_ID = houcf.ORGANIZATION_ID AND haouf.ORGANIZATION_ID = hauft.ORGANIZATION_ID AND haouf.EFFECTIVE_START_DATE BETWEEN houcf.EFFECTIVE_START_DATE AND houcf.EFFECTIVE_END_DATE AND hauft.LANGUAGE = ‘US’ AND hauft.EFFECTIVE_START_DATE = haouf.EFFECTIVE_START_DATE AND hauft.EFFECTIVE_END_DATE = haouf.EFFECTIVE_END_DATE AND houcf.CLASSIFICATION_CODE = ‘DEPARTMENT’ AND SYSDATE BETWEEN hauft.effective_start_date AND hauft.effective_end_date Person Names: select full_name from per_person_names_f where name_type = ‘GLOBAL’ and trunc(sysdate) between effective_start_date and effective_end_date Work Schedules: select schedule_id work_schedule_id,schedule_name work_schedule_name from zmm_sr_schedules_vl where trunc(sysdate) between trunc(effective_from_date) and trunc(effective_to_date) Collective Labor Agreements: SELECT DISTINCT CA.COLLECTIVE_AGREEMENT_NAME, CA.COLLECTIVE_AGREEMENT_ID FROM PER_COL_AGREEMENTS_TL CA Supervisor Names: select distinct full_name from PER_PERSON_NAMES_F ppnf, PER_ASSIGNMENT_SUPERVISORS_F pasf where pasf.manager_id=ppnf.person_id AND ppnf.name_type=’GLOBAL’ AND pasf.manager_type=’LINE_MANAGER’ order by full_name Benefit Life Events: Select DISTINCT Name from BEN_LER_F where TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE Benefit Plans: select distinct pl.name from BEN_PL_F PL where (PL.NAME not like ‘Waive%’ AND PL.NAME not like ‘Volun%’) and TRUNC(SYSDATE) BETWEEN PL.EFFECTIVE_START_DATE AND PL.EFFECTIVE_END_DATE

That’s all for now…  Will keep updating this post with other queries as I want to maintain single point of reference for all List of Values.

If you have any questions, please feel free to reach out to me by posting in comments section.

If you are interested in learning Fusion Technical tools go through this post

If you liked the article, please share it with your friends/ colleagues/ teammates or anyone who might also benefit from it.