Query to Retrieve Common Lookup Types, Codes, Meanings, and Tags in Oracle HCM

SELECT FLVB.LOOKUP_TYPE, FLVT.MEANING, FLVB.LOOKUP_CODE ,  '|'||FLVB.TAG , FAT.APPLICATION_NAME ,  FLVT.DESCRIPTION
FROM  FND_LOOKUP_VALUES_B FLVB, FND_LOOKUP_VALUES_TL FLVT, FND_APPLICATION_TL FAT
WHERE FLVB.ENABLED_FLAG = 'Y' 
AND   TRUNC(SYSDATE) BETWEEN  TRUNC(NVL(FLVB.START_DATE_ACTIVE,SYSDATE))  AND  TRUNC(NVL(FLVB.END_DATE_ACTIVE,SYSDATE))
AND   FLVB.LOOKUP_TYPE = FLVT.LOOKUP_TYPE
AND   FLVB.LOOKUP_CODE  = FLVT.LOOKUP_CODE
AND   FLVT.LANGUAGE = USERENV('LANG')
AND   FLVT.VIEW_APPLICATION_ID = FAT.APPLICATION_ID 
AND   FLVB.LOOKUP_TYPE IN  ('TITLE' , 'MILITARY_RANK','MAR_STATUS','SEX','EMAIL_TYPE','PER_NATIONAL_IDENTIFIER_TYPE','PHONE_TYPE','PER_DRIVERS_LICENSE_TYPE'
,'CONTRACT_TYPE','ADDRESS_TYPE','ORA_PER_VISA_PERMIT_TYPE','PER_VISA_PERMIT_TYPE','PER_VISA_PERMIT_STATUS','XLAT_VISA_REASONS','PER_VISA_PERMIT_CATEGORY'
,'XLAT_PAY_GROUP','EMP_CAT','EMPLOYEE_CATG','PROBATION_PERIOD','PER_SUPERVISOR_TYPE', 'CONTACT', 'XLAT_VETTING_LEVEL') 
ORDER BY  FLVB.LOOKUP_TYPE, FLVT.MEANING

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.