Introduction: Customer & Business Unit link Table in Oracle Fusion.
Cause of the issue: Since the customer is not assigned to a BU in Fusion. Customer Site is assigned to a data reference set and the data reference set is assigned in Manage Business Units Set assignments page to customer sites.
How do we solve: We need to map the Data Reference set id from HZ_CUST_ACCT_SITES_ALL / HZ_CUST_SITE_USES_ALL to below query to get he BU mapping for a Specific Customer account.
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,
fs.set_id
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’
