Most Frequently Used SQLs for List of Values (LOVs)
Most Frequently Used SQLs for List of Values (LOVs)

 2,068 total views

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

Want to learn Fusion Technical tools? Checkout this article

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.

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply