Daily Used SQL Queries Library

Let us know in comment section what else queries you are looking for, we will be updating the Library accordingly

Common Lookup -

select * from HCM_LOOKUPS where lookup_type = 'LOOKUP_NAME' and meaning = 'MEANING' and description = 'DESCRIPTION'

Value Set -

select vtl.FLEX_VALUE_MEANING
from FND_FLEX_VALUES v, fnd_flex_value_sets vs, FND_FLEX_VALUES_TL vtl
where FLEX_VALUE_SET_NAME = 'BANK_LIST'
and v.FLEX_VALUE_SET_ID = vs.FLEX_VALUE_SET_ID
and v.FLEX_VALUE_ID = vtl.FLEX_VALUE_ID
and LANGUAGE = 'US'
and v.FLEX_VALUE = 'ABC'

Document of Records -

select sum(DEI_ATTRIBUTE_NUMBER2) from HR_DOCUMENTS_OF_RECORD
where DEI_ATTRIBUTE_CATEGORY  ='GLB_EXECUTIVE_COMPLIMENTARY_ROOM_NIGHTS_REQUEST'
and PERSON_ID = :{PARAMETER.PERSONID}
and TO_CHAR(CREATION_DATE,'YYYY')= TO_CHAR(SYSDATE,'YYYY')

Query to get Active and Terminated Employees in Last one Year duration -

SELECT substr(ppnf.display_name,1,80)||' '||papf.person_number
FROM PER_PERSON_SECURED_LIST_V papf,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_periods_of_service pps
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(papf.effective_start_date) AND TRUNC(papf.effective_end_date)
AND TRUNC (SYSDATE) BETWEEN TRUNC (ppnf.effective_start_date) AND TRUNC (ppnf.effective_end_date)
AND papf.person_id = paam.person_id
AND papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND paam.effective_latest_change = 'Y'
AND paam.assignment_type = 'E'
AND paam.primary_assignment_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(paam.effective_start_date) AND TRUNC(paam.effective_end_date)
AND pps.period_of_service_id(+) = paam.period_of_service_id
and (paam.ASS_ATTRIBUTE_CATEGORY = 'A' 
     or paam.ASS_ATTRIBUTE_CATEGORY like 'B%' )
AND (paam.assignment_status_type = 'ACTIVE' OR 
    (paam.assignment_status_type = 'INACTIVE' and NVL(pps.actual_termination_date-365,SYSDATE)>=SYSDATE ))

Get Overtime Status of Job (EXEMPT/NONEXEMPT)

select B.INFORMATION1 from PER_JOBS_F_TL A, PER_JOB_LEG_F B
where A.JOB_ID=B.JOB_ID
and A.NAME = 'JOB_NAME'

Get Employee Date of Birth(DOB)

select pers.DATE_OF_BIRTH,
to_char(pers.DATE_OF_BIRTH,'DD-MM') as DateAndMonth,
papf.PERSON_NUMBER,pea.EMAIL_ADDRESS
from PER_PERSONS pers,
PER_ALL_PEOPLE_F papf,
PER_EMAIL_ADDRESSES pea
where papf.PERSON_ID = pers.PERSON_ID 
and papf.PERSON_ID = pea.PERSON_ID
and to_char(sysdate,'DD-MM')=to_char(pers.DATE_OF_BIRTH,'DD-MM')

Get Legal Entity, DOJ, PPG details based on the Month of Joining

select  distinct(B.PERSON_NUMBER),
(case when C.MIDDLE_NAMES is null then C.FIRST_NAME||' '||C.LAST_NAME
else
C.FIRST_NAME||' '||C.MIDDLE_NAMES||' '||C.LAST_NAME end) As "Full_Name",H.NAME, TO_CHAR(T.DATE_START,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') AS "Date of Joining",
P.SEGMENT1 "Housing_Eligibility"
FROM PER_ALL_ASSIGNMENTS_M A, PER_ALL_PEOPLE_F B, PER_PERSON_NAMES_F C, PER_PERIODS_OF_SERVICE T, PER_PEOPLE_GROUPS P, HR_ORGANIZATION_UNITS_F_TL H
WHERE A.PRIMARY_FLAG = 'Y'
AND A.PERSON_ID = B.PERSON_ID
AND A.PERSON_ID = C.PERSON_ID
AND T.PERSON_ID = B.PERSON_ID
AND A.PERIOD_OF_SERVICE_ID=T.PERIOD_OF_SERVICE_ID
and a.SYSTEM_PERSON_TYPE in ('EMP','CWK')
and trunc(sysdate) between a.effective_start_date and a.effective_end_date
and trunc(sysdate) between b.effective_start_date and b.effective_end_date
and trunc(sysdate) between h.effective_start_date and h.effective_end_date
and A.effective_latest_change = 'Y'
and trunc(sysdate) between c.effective_start_date and c.effective_end_date
and A.assignment_status_type in ('ACTIVE')
and A.assignment_type='E' 
and to_char(T.DATE_START,'MM-YYYY')='06-2022'
and A.PEOPLE_GROUP_ID=P.PEOPLE_GROUP_ID
and A.LEGAL_ENTITY_ID = h.ORGANIZATION_ID
and C.NAME_TYPE='GLOBAL'
order by b.person_number desc

Get the Work Schedule detail of employee

SELECT K.SCHEDULE_NAME, K1.START_DATE 
FROM PER_SCHEDULE_ASSIGNMENTS K1, PER_ALL_ASSIGNMENTS_M K2,
hr_all_organization_units hou,
ZMM_SR_SCHEDULES_TL K,
hr_org_unit_classifications_f hoc
WHERE K1.RESOURCE_ID = K2.ASSIGNMENT_ID
and K1.SCHEDULE_ID = K.SCHEDULE_ID
and K2.person_id='300000008791383'
and k1.RESOURCE_TYPE = 'ASSIGN'
and hou.organization_id = hoc.organization_id
and sysdate BETWEEN HOU.effective_start_date(+) AND HOU.effective_end_date(+)
and hoc.organization_id(+)=K2.legal_entity_id

Get Department Details

select A.name, B.classification_code, A.effective_start_date,A.effective_end_date ,A.organization_id 
 from hr_organization_units_f_tl A,
 HR_ORG_UNIT_CLASSIFICATIONS_F B
 where A.organization_id = B.organization_id
 and trunc(sysdate) between B.effective_start_date and B.EFFECTIVE_END_DATE
 and classification_code='DEPARTMENT'
and  A.effective_start_date != '1951/01/01'
and LANGUAGE='US'

Get Customer and Site details

select ACCOUNT_NAME, PARTY_SITE_NAME
from HZ_Cust_Accounts HCA, HZ_PARTY_SITES HPS
where HCA.party_id=HPS.party_id
and ACCOUNT_NAME = :{SEGMENT.Account Name}

Get User-defined table (UDT) Details

SELECT
fuc.USER_COLUMN_NAME,
fur.ROW_LOW_RANGE_OR_NAME,
fuci.VALUE
FROM
 fusion.ff_user_tables_vl fut
,fusion.ff_user_columns_vl fuc
,fusion.ff_user_rows_vl fur
,fusion.ff_user_column_instances_f fuci
WHERE 1=1
AND fut.user_table_name='<UDT_table_name>'
AND fuc.USER_COLUMN_NAME='<UDT_COLUMN_NAME>'
AND fut.user_table_id = fuc.user_table_id
AND fut.user_table_id = fur.user_table_id
AND TRUNC(SYSDATE) BETWEEN fur.effective_start_date AND fur.effective_end_date
AND fuc.user_column_id = fuci.user_column_id
AND fur.user_row_id = fuci.user_row_id
AND TRUNC(SYSDATE) BETWEEN fuci.effective_start_date AND fuci.effective_end_date

to be continued . . .