• 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?
- How to fetch Enterprise HCM Information?
Total Visits: 27

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.