• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 16, 2022
  • Reading time:7 mins read
You are currently viewing How to fetch Enterprise HCM Information?
How to fetch Enterprise HCM Information?

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')
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.