• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:December 2, 2021
  • Reading time:2 mins read
You are currently viewing SQL Query to find the mapping between Business Unit and Reference Data Set
SQL Query to find the mapping between Business Unit and Reference Data Set
- SQL Query to find the mapping between Business Unit and Reference Data Set
Total Visits: 20

In this article, we will fetch the mapping between Business Units and Reference Data Sets.

SQL Query:

SELECT HAUFT.NAME AS BU_CODE,
	HAUFT.NAME AS BU_DESCRIPTION,
	HOUCF.LEGISLATION_CODE AS COUNTRY,
	TO_CHAR(HAUFT.EFFECTIVE_START_DATE, 'YYYY/MM/DD') AS EFFECTIVE_START_DATE,
	TO_CHAR(HAUFT.EFFECTIVE_END_DATE, 'YYYY/MM/DD') AS EFFECTIVE_END_DATE,
	HOUCF.STATUS AS STATUS,
	FS.SET_CODE AS SET_CODE,
	FS.SET_NAME AS SET_NAME,
	HAUFT.LAST_UPDATED_BY AS LAST_UPDATED_BY,
	TO_CHAR(HAUFT.LAST_UPDATE_DATE, 'YYYY/MM/DD') AS LAST_UPDATE_DATE
FROM HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF,
	HR_ALL_ORGANIZATION_UNITS_F HAOUF,
	HR_ORGANIZATION_UNITS_F_TL HAUFT,
	HR_ORGANIZATION_INFORMATION_F HOIF,
	FND_SETID_SETS_VL FS
WHERE HAOUF.ORGANIZATION_ID = HOUCF.ORGANIZATION_ID
	AND HAOUF.ORGANIZATION_ID = HAUFT.ORGANIZATION_ID
	AND HAOUF.ORGANIZATION_ID = HOIF.ORGANIZATION_ID
	AND HAOUF.EFFECTIVE_START_DATE BETWEEN HOUCF.EFFECTIVE_START_DATE
		AND HOUCF.EFFECTIVE_END_DATE
	AND HAUFT.LANGUAGE = 'US'
	AND HAUFT.EFFECTIVE_START_DATE = HAOUF.EFFECTIVE_START_DATE
	AND HAUFT.EFFECTIVE_END_DATE = HAOUF.EFFECTIVE_END_DATE
	AND FS.SET_ID(+) = HOIF.ORG_INFORMATION4
	AND HOUCF.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.