In this article we will try to fetch Enterprise HCM Information configuration settings that we setup while configuring the Enterprise.
SQL Query to fetch Enterprise HCM Information:
SELECT ent.NAME ENT_NAME
,TO_CHAR(ent.EFFECTIVE_START_DATE, 'MM/DD/YYYY') ENT_START_DATE
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'ACTIVE_INACTIVE'
AND LOOKUP_CODE = ent.STATUS
) ENT_STATUS
,actl.ACTION_REASON
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'INTL_EXTL'
AND LOOKUP_CODE = org.INTERNAL_EXTERNAL_FLAG
) ENT_INT_EXT_FLAG
,org.INTERNAL_ADDRESS_LINE ENT_INT_ADDR_LINE
,hla.LOCATION_CODE ENT_LOCATION
,enti.ORG_INFORMATION21 ENT_GLB_PERS_NAME
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_ENTP_WORKER_NUM'
AND LOOKUP_CODE = enti.ORG_INFORMATION2
) ENT_WRK_NUM_GEN
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_EMP_MODEL'
AND LOOKUP_CODE = enti.ORG_INFORMATION19
) ENT_EMPLOYMENT_MODEL
,(
SELECT RTRIM(MEANING, ' ')
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'HRC_YES_NO'
AND LOOKUP_CODE = enti.ORG_INFORMATION20
) ENT_ETERM_OVERRIDE_ASG
,enti.ORG_INFORMATION5 ENT_PPL_GRP_FF
,enti.ORG_INFORMATION_NUMBER1 ENT_INIT_PERSON_NUMBER
,(
SELECT DECODE(MEANING, 'NID', 'National ID', MEANING)
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'ORA_PER_PERSON_DUP_SRCH_OPTION'
AND LOOKUP_CODE = enti.ORG_INFORMATION1
) ENT_DUP_PERSON_CHECK
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_SAL_LEVEL'
AND LOOKUP_CODE = enti.ORG_INFORMATION6
) ENT_SALARY_LEVEL
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_NUM_GEN_METHOD'
AND LOOKUP_CODE = enti.ORG_INFORMATION22
) ENT_PER_NUM_GEN
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_USER_ROLE_PROV'
AND LOOKUP_CODE = entu.ORG_INFORMATION11
) ENT_USER_ACT_CREN
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_USER_ROLE_PROV'
AND LOOKUP_CODE = entu.ORG_INFORMATION14
) ENT_USER_ACT_ROLE
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'PER_USER_ROLE_PROV'
AND LOOKUP_CODE = entu.ORG_INFORMATION15
) ENT_USER_ACT_MAINT
,(
SELECT RTRIM(MEANING, ' ')
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'HRC_YES_NO'
AND LOOKUP_CODE = entu.ORG_INFORMATION16
) ENT_TERM_USER_CRT
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'FREQUENCY'
AND LOOKUP_CODE = entw.ORG_INFORMATION4
) ENT_STD_WRK_HRS_FREQ
,entw.ORG_INFORMATION_NUMBER1 ENT_STD_WRK_HOURS
,entw.ORG_INFORMATION1 AS ENT_START_TIME
,entw.ORG_INFORMATION2 AS ENT_END_TIME
,entc.ORG_INFORMATION1 AS ENT_TRANS_PROC_CONV_RT_TYPE
,entc.ORG_INFORMATION2 AS ENT_REPORTING_CONV_RT_TYPE
,entps.ORG_INFORMATION6 AS ENT_ENABLE_POS_SYNC
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'ORA_PER_ORG_MGR_SYNC'
AND LOOKUP_CODE = entps.ORG_INFORMATION5
) ENT_POS_SYNC_MANAGER
,entps.ORG_INFORMATION1 AS ENT_POS_SYNC_DEPT
,entps.ORG_INFORMATION2 AS ENT_POS_SYNC_JOB
,entps.ORG_INFORMATION3 AS ENT_POS_SYNC_LOC
,entps.ORG_INFORMATION4 AS ENT_POS_SYNC_GRADE
,entps.ORG_INFORMATION7 AS ENT_POS_SYNC_FULLPART
,entps.ORG_INFORMATION8 AS ENT_POS_SYNC_REG_TEMP
,entps.ORG_INFORMATION9 AS ENT_POS_SYNC_FTE_WKH
,entps.ORG_INFORMATION10 AS ENT_POS_SYNC_PROB_PER
,entps.ORG_INFORMATION11 AS ENT_POS_SYNC_BARG_UNIT
,entps.ORG_INFORMATION12 AS ENT_POS_SYNC_ALLOW_OVRD
,entps.ORG_INFORMATION13 AS ENT_POS_SYNC_ST_END_TM
,entps.ORG_INFORMATION14 AS ENT_POS_SYNC_GR_LADDER
,entps.ORG_INFORMATION15 AS ENT_POS_SYNC_MAP_FLEX
,entps.ORG_INFORMATION17 AS ENT_POS_SYNC_MAP_EMP_CAT
,entph.ORG_INFORMATION1 AS ENT_POS_HIER_USE_POS_HIER
,entph.ORG_INFORMATION2 AS ENT_POS_HIER_USE_POS_TREES
,DECODE(entbo.ORG_INFORMATION1, 'Y', 'Yes', 'N', 'No', ' ') AS ENT_EDIT_HCM_BOBJ
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'ORA_PER_EMPL_ERR_TYPE'
AND LOOKUP_CODE = entps.ORG_INFORMATION1
) AS ENT_EMPLCONFIG_FUTRECVAL
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'ORA_PER_EMPL_ERR_TYPE'
AND LOOKUP_CODE = entps.ORG_INFORMATION2
) AS ENT_EMPLCONFIG_EXSUBTERM
,entempc.ORG_INFORMATION3 AS ENT_EMPLCONFIG_APRV_RGN_COLL
,entempc.ORG_INFORMATION4 AS ENT_EMPLCONFIG_DEF_ENTSNRTY_DT
,entempc.ORG_INFORMATION5 AS ENT_EMPLCONFIG_AUT_CONV_PENDW
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'ORA_PER_TALEO_RECRUIT_INTG'
AND LOOKUP_CODE = entempc.ORG_INFORMATION6
) AS ENT_EMPLCONFIG_REC_INTEG
,(
SELECT MEANING
FROM HCM_LOOKUPS
WHERE LOOKUP_TYPE = 'ORA_PER_WS_OBJ_CODE_GEN_METHOD'
AND LOOKUP_CODE = entwsc.ORG_INFORMATION1
) AS ENT_WSCONFIG_POSGEN_M
,entwsc.ORG_INFORMATION_NUMBER1 AS ENT_WSCONFIG_INIT_POSCODE
,entwmsc.ORG_INFORMATION_NUMBER1 AS ENT_WMSC_ORG_MSRCH
,entwmsc.ORG_INFORMATION_NUMBER2 AS ENT_WMSC_GRD_MSRCH
,entwmsc.ORG_INFORMATION_NUMBER3 AS ENT_WMSC_GRLD_MSRCH
,entwmsc.ORG_INFORMATION_NUMBER4 AS ENT_WMSC_GRRT_MSRCH
,entwmsc.ORG_INFORMATION_NUMBER5 AS ENT_WMSC_JOB_MSRCH
,entwmsc.ORG_INFORMATION_NUMBER7 AS ENT_WMSC_JFAM_MSRCH
,entwmsc.ORG_INFORMATION_NUMBER8 AS ENT_WMSC_LOC_MSRCH
,entwmsc.ORG_INFORMATION_NUMBER9 AS ENT_WMSC_POS_MSRCH
FROM PER_ENTERPRISES ent
,HR_ALL_ORGANIZATION_UNITS_F org
,PER_ACTION_OCCURRENCES pao
,PER_ACTION_REASONS_TL actl
,HR_LOCATIONS_ALL hla
,HR_ORGANIZATION_INFORMATION_F enti
,HR_ORGANIZATION_INFORMATION_F entu
,HR_ORGANIZATION_INFORMATION_F entw
,HR_ORGANIZATION_INFORMATION_F entc
,HR_ORGANIZATION_INFORMATION_F entps
,HR_ORGANIZATION_INFORMATION_F entph
,HR_ORGANIZATION_INFORMATION_F entempc
,HR_ORGANIZATION_INFORMATION_F entbo
,HR_ORGANIZATION_INFORMATION_F entwsc
,HR_ORGANIZATION_INFORMATION_F entwmsc
WHERE ent.ORGANIZATION_ID = org.ORGANIZATION_ID
AND org.ACTION_OCCURRENCE_ID = pao.ACTION_OCCURRENCE_ID(+)
AND pao.ACTION_REASON_ID = actl.ACTION_REASON_ID(+)
AND org.LOCATION_ID = hla.LOCATION_ID(+)
AND ent.ORGANIZATION_ID = enti.ORGANIZATION_ID(+)
AND enti.ORG_INFORMATION_CONTEXT(+) = 'PER_ENTERPRISE_INFO'
AND ent.ORGANIZATION_ID = entu.ORGANIZATION_ID(+)
AND entu.ORG_INFORMATION_CONTEXT(+) = 'PER_ENT_USER_ROLE_PROV_INFO'
AND ent.ORGANIZATION_ID = entw.ORGANIZATION_ID(+)
AND entw.ORG_INFORMATION_CONTEXT(+) = 'PER_WORK_DAY_INFO'
AND entc.ORGANIZATION_ID(+) = ent.ORGANIZATION_ID
AND entc.ORG_INFORMATION_CONTEXT(+) = 'PER_CURRENCY_SUPPORT'
AND entps.ORGANIZATION_ID(+) = ent.ORGANIZATION_ID
AND entps.ORG_INFORMATION_CONTEXT(+) = 'ORA_PER_POS_SYNC_ATTRS'
AND entph.ORGANIZATION_ID(+) = ent.ORGANIZATION_ID
AND entph.ORG_INFORMATION_CONTEXT(+) = 'ORA_PER_POS_HIER_CONFIG'
AND entempc.ORGANIZATION_ID(+) = ent.ORGANIZATION_ID
AND entempc.ORG_INFORMATION_CONTEXT(+) = 'ORA_EMPL_CONF'
AND entbo.ORGANIZATION_ID(+) = ent.ORGANIZATION_ID
AND entbo.ORG_INFORMATION_CONTEXT(+) = 'ORA_PER_EDIT_HCM_BUSINESS_OBJECTS'
AND entwsc.ORGANIZATION_ID(+) = ent.ORGANIZATION_ID
AND entwsc.ORG_INFORMATION_CONTEXT(+) = 'ORA_PER_WS_CONFIGURATION'
AND entwmsc.ORGANIZATION_ID(+) = ent.ORGANIZATION_ID
AND entwmsc.ORG_INFORMATION_CONTEXT(+) = 'ORA_PER_WS_CONFIGURATION_MIN_CHAR'
AND sysdate BETWEEN NVL(ent.EFFECTIVE_START_DATE, '19010101')
AND NVL(ent.EFFECTIVE_END_DATE, '42171231')
AND sysdate BETWEEN NVL(org.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(org.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(hla.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(hla.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(enti.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(enti.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entu.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entu.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entw.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entw.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entc.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entc.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entps.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entps.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entph.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entph.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entempc.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entempc.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entbo.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entbo.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entwsc.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entwsc.EFFECTIVE_END_DATE(+), '42171231')
AND sysdate BETWEEN NVL(entwsc.EFFECTIVE_START_DATE(+), '19010101')
AND NVL(entwsc.EFFECTIVE_END_DATE(+), '42171231')
AND actl.LANGUAGE (+) = USERENV('LANG')
