Tag Archive Business Unit

In Oracle Fusion HCM, a business unit can be linked to either a common reference data set or a specific one. When associated with a specific reference data set, the business unit gains access to departments defined within that set, as well as those available in the common reference data set. The SQL query provided in this article retrieves all departments accessible to the selected business unit, based on its reference data set associations.

WITH set_details
AS
(
  SELECT fabuv.bu_name 				business_unit_name
        ,fabuv.bu_id				business_unit_id
        ,fss.set_id
        ,fss.set_code
        ,fss.set_name
    FROM FND_SETID_SETS 		fss
        ,FND_SETID_ASSIGNMENTS 	        fsa
  	,FUN_ALL_BUSINESS_UNITS_V       fabuv
   WHERE fabuv.bu_name = 'Enterprise'
     AND fsa.determinant_type = 'BU'
     AND fsa.determinant_value = fabuv.bu_id
     AND fsa.reference_group_name = 'PER_DEPARTMENTS'
     AND fsa.set_id = fss.set_id
     AND fss.set_id <> 0
     AND fss.language = 'US'
     AND TRUNC(SYSDATE) BETWEEN fabuv.date_from AND fabuv.date_to
  UNION
  SELECT fabuv.bu_name 				                business_unit_name
        ,fabuv.bu_id					        business_unit_id
        ,0							set_id
        ,'COMMON'						set_code
        ,'Common'						set_name
    FROM FUN_ALL_BUSINESS_UNITS_V fabuv
   WHERE fabuv.bu_name = 'Discrete Automation'
     AND TRUNC(SYSDATE) BETWEEN fabuv.date_from AND fabuv.date_to   
)
SELECT pd.name					        department
      ,pd.organization_id 				department_id
      ,DECODE(pd.status,'A','Active','Inactive') 	department_status 
      ,set_details.business_unit_name
      ,set_details.set_name
  FROM per_departments 	pd
      ,set_details	set_details
 WHERE pd.set_id = set_details.set_id 	 
   AND TRUNC(SYSDATE) BETWEEN pd.effective_start_date AND pd.effective_end_date
ORDER BY 1