Most Frequently Used SQLs For List Of Values (LOVs)

Skip to content

• Post category:BI Publisher
• Post comments:0 Comments
• Post last modified:June 16, 2020
• Reading time:4 mins read

Tags: BI Publisher, SQL Query 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.

Read more articles

Previous PostSQL Query to get Fast Formula and related info Next PostMost frequently used Date, Number and String functions

You Might Also Like

SQL Query to fetch the Learner Course Progress Report

December 1, 2021

Etext template tips and tricks Part 1

April 15, 2020

SQL Query to get the Dropped Dependents from Benefits Coverage

July 31, 2020

Leave a Reply Cancel reply

CommentEnter your name or username to comment Enter your email address to comment Enter your website URL (optional)

Save my name, email, and website in this browser for the next time I comment.

Search Knowledgebase

Search Generic filters Exact matches only Search in title Search in content Search in excerpt

VIP Membership Plans

Recent Posts

• SQL query to pull Payroll Run Results for employees with multiple assignments?
• How to get the return value of Table Valueset in FF from SQL Query?
• Advanced Edit not available for HCM Extracts from 24D
• How to get the count of data loaded using HDL for business objects?
• How do we extract the Grade Ladder step rates?
• How do we reduce RTF template size when many images exist in the template?
• How to find the Lookup Type for an attribute that supports HDL load?
• How to modify the reports/value sets for Payroll table changes in 24A?
• What is a Progression Grade Ladder and how to load it using HDL?
• How to resolve “Template type etext is not yet supported” error for BI Report?

Tag Cloud

Alerts Approvals Audit BI Publisher Bursting Calculation Cards content server Data Disposal Data Scrambling EL Expressions ERD etext Fast Formula Flexfields Functional General HCM Data Loader HCM Extract Helpful Tools Interview Questions Latest Features LBAC Lookup Migration Notifications Offer Letter OTBI Analytics Payroll Payroll Batch Loader Payroll Flow Performance Management Personalizations Profile Options REST Services Scheduled Processes Security SOAP Services SQL Query Tables Templates Transformation Formula UDT Valueset VIP Content Web Services

Archives

Archives Select Month October 2025 (1) December 2024 (2) May 2024 (2) April 2024 (1) March 2024 (4) February 2024 (1) December 2023 (1) November 2023 (5) October 2023 (2) August 2023 (2) July 2023 (21) June 2023 (8) May 2023 (11) April 2023 (5) March 2023 (1) February 2023 (7) January 2023 (1) December 2022 (5) November 2022 (8) October 2022 (7) September 2022 (1) June 2022 (10) May 2022 (2) April 2022 (2) March 2022 (6) February 2022 (1) January 2022 (5) December 2021 (18) November 2021 (3) October 2021 (8) September 2021 (13) August 2021 (10) July 2021 (14) June 2021 (11) May 2021 (9) April 2021 (15) March 2021 (6) February 2021 (14) January 2021 (3) December 2020 (7) November 2020 (10) October 2020 (6) September 2020 (9) August 2020 (13) July 2020 (16) June 2020 (28) May 2020 (37) April 2020 (35) May 2019 (2) April 2019 (2) March 2019 (5) February 2019 (4) January 2019 (24) Close Menu Menu

• Home
• VIP Access
  • Log In
  • Manage VIP Account
  • Register for VIP Plan
  • VIP Member-Only Content
• Blog
• About Us
• Technical
  • HCM Data Loader
  • HCM Extract
  • BI Publisher
  • Fast Formula
  • Security
  • OTBI Analytics
  • Personalizations
  • Scheduled Processes
• Functional
  • Core HR
  • Absence Management
  • Payroll
  • Benefits
  • Performance Management
  • Talent Management
  • Time & Labor
  • Helpdesk
  • Learning
  • Recruiting
  • Onboarding
• Interview Tips
  • HCM Extracts Questions
  • HCM Data Loader Questions
  • BI Reports Questions
• Log In
• • Search Generic filters Exact matches only Search in title Search in content Search in excerpt

Don`t copy text!