Oracle Fusion General Ledger Tables Setup Query

Query to identify companies assigned to legal entities and ledgers

Query lists all the BSV’s assigned to legal entity and/or directly to the ledger.
 It also lists any BSV’s created but not assigned to check if we missed anything. 
To use it for another customer, change the name of the Value Set for the primary balancing segment of the chart of accounts.

**********************************************************
select bsv.ledger_name "Ledger Name"
, nvl(bsv.legal_entity_name,'DIRECTLY ASSIGNED TO LEDGER') "Legal Entity"
, bsv.segment_value "Company"
, fvl.description "Description"
from gl_ledger_le_bsv_specific_v bsv
, fnd_flex_values_vl fvl
, fnd_flex_value_sets fvs
where fvl.flex_value = bsv.segment_value
and fvl.flex_value_set_id = fvs.flex_value_set_id
and fvs.flex_value_set_name = '3SM Technologies'
UNION
select 'ZZ UNASSIGNED' "Ledger Name"
, 'ZZ NOT APPLICABLE' "Legal Entity"
, ffv.flex_value "Company"
, fvl.description "Description"
from fnd_flex_values ffv
, fnd_flex_values_tl fvl
where ffv.flex_value_id = fvl.flex_value_id
and fvl.language = 'US'
and ffv.summary_flag = 'N'
and ffv.value_category = '3SM Technologies'
and ffv.flex_value NOT IN
(select segment_value
from gl_ledger_norm_seg_vals);

SELECT GLSV.SEGMENT_VALUE,
       GLL.LEDGER_ID,
       GLL.NAME LEDGER_NAME,
       GLL.DESCRIPTION LEDGER_DESCRIPTION,
       GLL.CURRENCY_CODE LEDGER_CURRENCY_CODE,
       GLSV.LEGAL_ENTITY_ID,
       (SELECT NAME
          FROM XLE_ENTITY_PROFILES
         WHERE LEGAL_ENTITY_ID = GLSV.LEGAL_ENTITY_ID) LEGAL_ENTITY_NAME,
         (SELECT BU_ID
           FROM FUN_ALL_BUSINESS_UNITS_V HOUF
          WHERE LEGAL_ENTITY_ID = GLSV.LEGAL_ENTITY_ID) BUSINESS_UNIT_ID,
       (SELECT bu_name
           FROM FUN_ALL_BUSINESS_UNITS_V HOUF
          WHERE LEGAL_ENTITY_ID = GLSV.LEGAL_ENTITY_ID) BUSINESS_UNIT_NAME,
          GLL.CHART_OF_ACCOUNTS_ID
  FROM GL_LEDGERS GLL,
       GL_LEDGER_SEGMENT_VALUES GLSV
WHERE GLL.LEDGER_ID = GLSV.LEDGER_ID
   AND NVL(GLL.CURRENCY_CODE,'X') <> 'X'
   AND GLL.NAME LIKE '%PRM%'
ORDER BY GLSV.SEGMENT_VALUE