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.
