Oracle Fusion Business Unit, Ledgers, Value Set SQLs

--BUSINESS UNIT
----------------------
SELECT BusinessUnitEO.NAME BU_NAME,
  LocationDPEO.DESCRIPTION LOCATION,
  PersonNameDPEO.LIST_NAME MANAGER_NAME,
  DECODE(BusinessUnitEO.STATUS,'A','Active','Inactive') STATUS,
  GL_LEDGER_LE_V.LEGAL_ENTITY_NAME AS LEGAL_ENTITY_NAME,
  GL_LEDGER_LE_V.LEDGER_NAME       AS LEDGER_NAME,
  SetIdSetPEO.SET_CODE,
  SetIdSetPEO.SET_NAME,
  GL_LEDGER_LE_V.CHART_OF_ACCOUNTS_ID,
  GL_LEDGER_LE_V.LEGAL_ENTITY_ID,
  GL_LEDGER_LE_V.LEDGER_ID
FROM  FUSION.HR_ORGANIZATION_V BusinessUnitEO,
   FUSION.HR_ORG_DETAILS_BY_CLASS_V BusinessUnitDetailEO,
   FUSION.GL_LEDGER_LE_V GL_LEDGER_LE_V,
   FUSION.FUN_FIN_BUSINESS_UNITS_V FinancialsBusinessUnitPEO,
   FUSION.FND_SETID_SETS_VL SetIdSetPEO,
   FUSION.HR_LOCATIONS_ALL_F_VL LocationDPEO,
   FUSION.PER_PERSON_NAMES_F_V PersonNameDPEO
WHERE (BusinessUnitEO.ORGANIZATION_ID            = BusinessUnitDetailEO.ORGANIZATION_ID)
AND (BusinessUnitEO.CLASSIFICATION_CODE          = BusinessUnitDetailEO.CLASSIFICATION_CODE)
AND BusinessUnitDetailEO.ORG_INFORMATION_CONTEXT = BusinessUnitEO.CLASSIFICATION_CODE
AND BusinessUnitEO.CLASSIFICATION_CODE           = 'FUN_BUSINESS_UNIT'
AND (BusinessUnitDetailEO.ORG_INFORMATION2       = GL_LEDGER_LE_V.LEGAL_ENTITY_ID)
AND (BusinessUnitDetailEO.ORG_INFORMATION3       = GL_LEDGER_LE_V.LEDGER_ID)
AND (BusinessUnitDetailEO.ORG_INFORMATION7       = FinancialsBusinessUnitPEO.BU_ID(+))
AND (BusinessUnitDetailEO.ORG_INFORMATION4       = SetIdSetPEO.SET_ID(+))
AND (BusinessUnitEO.LOCATION_ID                  = LocationDPEO.LOCATION_ID(+))
AND (BusinessUnitDetailEO.ORG_INFORMATION1       = PersonNameDPEO.PERSON_ID(+))
--AND GL_LEDGER_LE_V.CHART_OF_ACCOUNTS_ID          --=:P_CHART_OF_ACCOUNTS_ID
ORDER BY BusinessUnitEO.NAME

---------
GL_LEDGERS
--------
select GL_LEDGERS.LEDGER_ID LEDGER_ID,
GL_LEDGERS.NAME as NAME,
GL_LEDGERS.SHORT_NAME as SHORT_NAME,
GL_LEDGERS.DESCRIPTION as DESCRIPTION,
GL_LEDGERS.LEDGER_CATEGORY_CODE as LEDGER_CATEGORY_CODE,
GL_LEDGERS.LE_LEDGER_TYPE_CODE as LE_LEDGER_TYPE_CODE,
GL_LEDGERS.COMPLETION_STATUS_CODE as COMPLETION_STATUS_CODE,
GL_LEDGERS.CURRENCY_CODE as CURRENCY_CODE,
GL_LEDGERS.PERIOD_SET_NAME as PERIOD_SET_NAME,
GL_LEDGERS.ACCOUNTED_PERIOD_TYPE as ACCOUNTED_PERIOD_TYPE,
GL_LEDGERS.ALLOW_INTERCOMPANY_POST_FLAG as ALLOW_INTERCOMPANY_POST_FLAG,
   GL_CALENDARS.USER_PERIOD_SET_NAME CALENDAR_NAME,
   DECODE(GL_LEDGERS.LEDGER_CATEGORY_CODE,'PRIMARY','Primary','SECONDARY','Secondary',GL_LEDGERS.LEDGER_CATEGORY_CODE) as LEDGER_CATEGORY,
DECODE(GL_LEDGERS.COMPLETION_STATUS_CODE,'CONFIRMED','Confirmed','NOT_STARTED','Not Started','ERROR','Error',GL_LEDGERS.COMPLETION_STATUS_CODE) as COMPLETION_STATUS,
   GL_BALANCES_CUBES.APPLICATION_NAME CUBE
 from FUSION.GL_LEDGERS GL_LEDGERS, FUSION.GL_CALENDARS GL_CALENDARS, GL_BALANCES_CUBES GL_BALANCES_CUBES
WHERE  GL_LEDGERS.CHART_OF_ACCOUNTS_ID=:P_CHART_OF_ACCOUNTS_ID
AND GL_LEDGERS.PERIOD_SET_NAME =GL_CALENDARS.PERIOD_SET_NAME
AND GL_LEDGERS.ACCOUNTED_PERIOD_TYPE =GL_CALENDARS.PERIOD_TYPE
AND GL_BALANCES_CUBES.PERIOD_SET_NAME(+)=GL_LEDGERS.PERIOD_SET_NAME
AND GL_BALANCES_CUBES.CHART_OF_ACCOUNTS_ID(+)=GL_LEDGERS.CHART_OF_ACCOUNTS_ID
ORDER BY  GL_LEDGERS.NAME
---------

--VALUE SET BASED ON COA
-----
SELECT 
  FND_KF_SEGMENTS_VL.NAME,
  FND_KF_SEGMENTS_VL.SEGMENT_CODE,
  FND_KF_SEGMENTS_VL.PROMPT,
  FND_KF_SEGMENTS_VL.SHORT_PROMPT,
  FND_VS_VALUE_SETS.VALUE_SET_CODE
FROM FUSION.FND_KF_STR_INSTANCES_VL FND_KF_STR_INSTANCES_VL,
  FUSION.FND_KF_STRUCTURES_VL FND_KF_STRUCTURES_VL,
  FUSION.FND_KF_SEGMENTS_VL FND_KF_SEGMENTS_VL,
  FND_VS_VALUE_SETS FND_VS_VALUE_SETS,
  FND_KF_SEGMENT_INSTANCES FND_KF_SEGMENT_INSTANCES
WHERE FND_KF_STR_INSTANCES_VL.STRUCTURE_INSTANCE_NUMBER=:P_COA_ID
AND FND_KF_STRUCTURES_VL.STRUCTURE_ID                  = FND_KF_STR_INSTANCES_VL.STRUCTURE_ID
AND FND_KF_SEGMENTS_VL.STRUCTURE_ID                    =FND_KF_STRUCTURES_VL.STRUCTURE_ID
AND FND_KF_STR_INSTANCES_VL.KEY_FLEXFIELD_CODE         ='GL#'
AND FND_VS_VALUE_SETS.VALUE_SET_ID                  =FND_KF_SEGMENT_INSTANCES.VALUE_SET_ID
AND FND_KF_SEGMENT_INSTANCES.STRUCTURE_INSTANCE_ID    =FND_KF_STR_INSTANCES_VL.STRUCTURE_INSTANCE_ID
AND FND_KF_SEGMENT_INSTANCES.SEGMENT_CODE=FND_KF_SEGMENTS_VL.SEGMENT_CODE
AND FND_KF_SEGMENTS_VL.ENABLED_FLAG ='Y'
ORDER BY SEQUENCE_NUMBER

-------

SELECT segvalsets.value_set_code             ,
value                                       ,
segvals.description                      ,
enabled_flag                                ,
start_date_active                           ,
end_date_active                             ,
summary_flag                                ,
flex_value_attribute1 AS POSTING_ALLOWED    ,
flex_value_attribute2 AS BUDGETING_ALLOWED  ,
flex_value_attribute3 AS ACCOUNT_TYPE       ,
flex_value_attribute4 AS CONTROL_ACCOUNT    ,
flex_value_attribute5 AS RECONCILIATION_FLAG,
flex_value_attribute6 AS FINANCIAL_CATEGORY
FROM fusion.fnd_vs_values_vl segvals,
fusion.fnd_vs_value_sets segvalsets
WHERE segvals.value_set_id = segvalsets.value_Set_id
AND segvalsets.value_Set_id IN
(SELECT value_set_id
  FROM FUSION.fnd_kf_segment_instances
 WHERE structure_instance_id IN
 (SELECT fusion.fnd_kf_str_instances_b.structure_instance_id
    FROM FUSION.fnd_kf_str_instances_b
   WHERE application_id =101
 AND key_flexfield_code = 'GL#'
 )
)


--- Lookup for flex_value_attribute3 ( ACCOUNT_TYPE     ) A: Asset etc
SELECT DISTINCT 
LOOKUP_TYPE,
LOOKUP_CODE,
MEANING,
DESCRIPTION,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE
FROM fnd_lookup_values_vl WHERE view_application_id = 101
AND LOOKUP_TYPE ='ACCOUNT TYPE'

--and meaning like 'Asset'

check GL_LOOKUPS