XLE_LE_FROM_REGISTRATIONS

Details

Schema: FUSION
Object owner: XLE
Object type: VIEW

Columns

Name
OPERATING_UNIT_ID LEDGER_ID LEGAL_ENTITY_ID LEGAL_ENTITY_NAME REGISTRATION_ID LEGISLATIVE_CATEGORY_CODE LEGISLATIVE_CATEGORY_MEANING REGISTRATION_NUMBER COUNTRY_OF_REGISTRATION TRANSACTION_TAX_REGN_CONTEXT EFFECTIVE_FROM EFFECTIVE_TO

Name

OPERATING_UNIT_ID

LEDGER_ID

LEGAL_ENTITY_ID

LEGAL_ENTITY_NAME

REGISTRATION_ID

LEGISLATIVE_CATEGORY_CODE

LEGISLATIVE_CATEGORY_MEANING

REGISTRATION_NUMBER

COUNTRY_OF_REGISTRATION

TRANSACTION_TAX_REGN_CONTEXT

EFFECTIVE_FROM

EFFECTIVE_TO

Query

SQL_Statement
( SELECT bu.bu_id operating_unit_id, lg.ledger_id, xlep.legal_entity_id, xlep.name legal_entity_name, reg.registration_id, lkp.lookup_code legislative_category_code, lkp.meaning legislative_category_meaning, reg.registration_number, geo.geography_code country_of_registration, NULL transaction_tax_regn_context, reg.effective_from, reg.effective_to FROM gl_ledgers primarylg, gl_ledgers lg, gl_ledger_relationships rs, gl_ledger_configurations cfg, gl_ledger_config_details cfgdet, xle_entity_profiles xlep, xle_registrations reg, xle_jurisdictions_b jur, hz_geographies geo, hz_lookups lkp, fun_all_business_units_v bu WHERE rs.application_id = 101 AND lg.ledger_id = rs.target_ledger_id AND lg.ledger_category_code = rs.target_ledger_category_code AND primarylg.ledger_id = rs.primary_ledger_id AND primarylg.ledger_category_code = 'PRIMARY' AND rs.target_ledger_category_code = 'PRIMARY' AND cfg.configuration_id = primarylg.configuration_id AND cfgdet.configuration_id(+) = cfg.configuration_id AND cfgdet.object_type_code(+) = 'LEGAL_ENTITY' AND cfgdet.object_id = xlep.legal_entity_id AND reg.source_id = xlep.legal_entity_id AND reg.source_table = 'XLE_ENTITY_PROFILES' AND reg.jurisdiction_id = jur.jurisdiction_id AND jur.geography_id = geo.geography_id AND lkp.lookup_type = 'LEGISLATIVE_CATEGORY' AND lkp.lookup_code = jur.legislative_cat_code AND bu.primary_ledger_id = to_char(lg.ledger_id) AND bu.status = 'A' UNION SELECT bu.bu_id operating_unit_id, lg.ledger_id, est.legal_entity_id legal_entity_id, lep.name legal_entity_name, reg.registration_id, lkp.lookup_code legislative_category_code, lkp.meaning legislative_category_meaning, reg.registration_number registration_number, rgm.country_code country_of_registration, reg.tax_regime_code \|\| decode(reg.tax, NULL, NULL, '-' \|\| reg.tax) transaction_tax_regn_context, reg.effective_from effective_from, reg.effective_to effective_to FROM zx_registrations reg, zx_party_tax_profile ptp, xle_etb_profiles est, xle_entity_profiles lep, zx_regimes_b rgm, hz_lookups lkp, gl_ledgers primarylg, gl_ledgers lg, gl_ledger_relationships rs, gl_ledger_configurations cfg, gl_ledger_config_details cfgdet, fun_all_business_units_v bu WHERE rs.application_id = 101 AND lg.ledger_id = rs.target_ledger_id AND lg.ledger_category_code = rs.target_ledger_category_code AND primarylg.ledger_id = rs.primary_ledger_id AND primarylg.ledger_category_code = 'PRIMARY' AND rs.target_ledger_category_code = 'PRIMARY' AND cfg.configuration_id = primarylg.configuration_id AND cfgdet.configuration_id(+) = cfg.configuration_id AND cfgdet.object_type_code(+) = 'LEGAL_ENTITY' AND cfgdet.object_id = est.legal_entity_id AND est.legal_entity_id = lep.legal_entity_id AND reg.party_tax_profile_id = ptp.party_tax_profile_id AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT' AND ptp.legal_establishment_flag = 'Y' AND ptp.party_id = est.party_id AND reg.tax_regime_code = rgm.tax_regime_code AND lkp.lookup_type = 'LEGISLATIVE_CATEGORY' AND lkp.lookup_code = 'TRANSACTION_TAX' AND bu.primary_ledger_id = to_char(lg.ledger_id) AND bu.status = 'A' )

SQL_Statement

(

SELECT bu.bu_id operating_unit_id,

lg.ledger_id,

xlep.legal_entity_id,

xlep.name legal_entity_name,

reg.registration_id,

lkp.lookup_code legislative_category_code,

lkp.meaning legislative_category_meaning,

reg.registration_number,

geo.geography_code country_of_registration,

NULL transaction_tax_regn_context,

reg.effective_from,

reg.effective_to

FROM gl_ledgers primarylg,

gl_ledgers lg,

gl_ledger_relationships rs,

gl_ledger_configurations cfg,

gl_ledger_config_details cfgdet,

xle_entity_profiles xlep,

xle_registrations reg,

xle_jurisdictions_b jur,

hz_geographies geo,

hz_lookups lkp,

fun_all_business_units_v bu

WHERE rs.application_id = 101

AND lg.ledger_id = rs.target_ledger_id

AND lg.ledger_category_code = rs.target_ledger_category_code

AND primarylg.ledger_id = rs.primary_ledger_id

AND primarylg.ledger_category_code = 'PRIMARY'

AND rs.target_ledger_category_code = 'PRIMARY'

AND cfg.configuration_id = primarylg.configuration_id

AND cfgdet.configuration_id(+) = cfg.configuration_id

AND cfgdet.object_type_code(+) = 'LEGAL_ENTITY'

AND cfgdet.object_id = xlep.legal_entity_id

AND reg.source_id = xlep.legal_entity_id

AND reg.source_table = 'XLE_ENTITY_PROFILES'

AND reg.jurisdiction_id = jur.jurisdiction_id

AND jur.geography_id = geo.geography_id

AND lkp.lookup_type = 'LEGISLATIVE_CATEGORY'

AND lkp.lookup_code = jur.legislative_cat_code

AND bu.primary_ledger_id = to_char(lg.ledger_id)

AND bu.status = 'A'

UNION

SELECT bu.bu_id operating_unit_id,

lg.ledger_id,

est.legal_entity_id legal_entity_id,

lep.name legal_entity_name,

reg.registration_id,

lkp.lookup_code legislative_category_code,

lkp.meaning legislative_category_meaning,

reg.registration_number registration_number,

rgm.country_code country_of_registration,

reg.tax_regime_code || decode(reg.tax, NULL, NULL, '-' || reg.tax) transaction_tax_regn_context,

reg.effective_from effective_from,

reg.effective_to effective_to

FROM zx_registrations reg,

zx_party_tax_profile ptp,

xle_etb_profiles est,

xle_entity_profiles lep,

zx_regimes_b rgm,

hz_lookups lkp,

gl_ledgers primarylg,

gl_ledgers lg,

gl_ledger_relationships rs,

gl_ledger_configurations cfg,

gl_ledger_config_details cfgdet,

fun_all_business_units_v bu

WHERE rs.application_id = 101

AND lg.ledger_id = rs.target_ledger_id

AND lg.ledger_category_code = rs.target_ledger_category_code

AND primarylg.ledger_id = rs.primary_ledger_id

AND primarylg.ledger_category_code = 'PRIMARY'

AND rs.target_ledger_category_code = 'PRIMARY'

AND cfg.configuration_id = primarylg.configuration_id

AND cfgdet.configuration_id(+) = cfg.configuration_id

AND cfgdet.object_type_code(+) = 'LEGAL_ENTITY'

AND cfgdet.object_id = est.legal_entity_id

AND est.legal_entity_id = lep.legal_entity_id

AND reg.party_tax_profile_id = ptp.party_tax_profile_id

AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'

AND ptp.legal_establishment_flag = 'Y'

AND ptp.party_id = est.party_id

AND reg.tax_regime_code = rgm.tax_regime_code

AND lkp.lookup_type = 'LEGISLATIVE_CATEGORY'

AND lkp.lookup_code = 'TRANSACTION_TAX'

AND bu.primary_ledger_id = to_char(lg.ledger_id)

AND bu.status = 'A' )