select
(
select distinct
listagg(gl0.name,', ') within group (order by gl0.name) over (partition by glsna.ledger_id) ledger_set
from
gl_ledger_set_norm_assign glsna,
gl_ledgers gl0
where
gl.ledger_id=glsna.ledger_id and
nvl(glsna.status_code,'X')<>'D' and
glsna.ledger_set_id=gl0.ledger_id
) ledger_set,
gl.name ledger,
xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101) ledger_category,
gl.currency_code currency,
xfi.name legal_entity,
xfi.legal_entity_identifier,
(
select distinct
listagg(gleb.flex_segment_value,', ') within group (order by gleb.flex_segment_value) over (partition by gleb.legal_entity_id) segment_value
from
gl_legal_entities_bsvs gleb
where
xfi.legal_entity_id=gleb.legal_entity_id
) balancing_segment_value,
(
select distinct
listagg(decode(ffvs.flex_value_set_id,null,gl_ledgers_pkg.get_bsv_desc('LE',gleb.flex_value_set_id,gleb.flex_segment_value),ffvs.description),', ') within group (order by gleb.flex_segment_value) over (partition by gleb.legal_entity_id) description
from
gl_legal_entities_bsvs gleb,
fnd_flex_values_vl ffvs
where
xfi.legal_entity_id=gleb.legal_entity_id and
gleb.flex_value_set_id=ffvs.flex_value_set_id(+) and
gleb.flex_segment_value=ffvs.flex_value(+)
) bsv_description,
xfi.registration_number,
ftv.territory_short_name country,
ftv.territory_short_name||nvl2(xfi.legislative_cat_code,' - '||xxen_util.meaning(xfi.legislative_cat_code,'LEGISLATIVE_CATEGORY',222),null) juristdiction,
xfi.address_line_1,
xfi.address_line_2,
xfi.address_line_3,
xfi.town_or_city city,
xfi.postal_code,
(select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.application_id=101 and fifsv.id_flex_code='GL#') chart_of_accounts_name,
gl.ledger_id,
gl.chart_of_accounts_id,
xfi.legal_entity_id
from
gl_ledgers gl,
gl_ledger_config_details glcd,
xle_firstparty_information_v xfi,
fnd_territories_vl ftv
where
1=1 and
gl.object_type_code='L' and
gl.configuration_id=glcd.configuration_id(+) and
glcd.object_type_code(+)='LEGAL_ENTITY' and
glcd.setup_step_code(+)='NONE' and
glcd.object_id=xfi.legal_entity_id(+) and
xfi.country=ftv.territory_code(+)
order by
chart_of_accounts_name,
ledger_set,
ledger,
legal_entity,
currency,
country |
