VIEW: APPS.XLE_LE_FROM_REGISTRATIONS

Source

View Text - Preformatted

(
SELECT o2.organization_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,
  ar_lookups lkp,
  hr_organization_information o2,
  hr_organization_information o3
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 o3.org_information3 = to_char(lg.ledger_id)
 AND o3.organization_id = o2.organization_id
 AND o2.org_information_context = 'CLASS'
 AND o3.org_information_context = 'Operating Unit Information'
 AND o2.org_information1 = 'OPERATING_UNIT'
 AND o2.org_information2 = 'Y'
UNION
SELECT o2.organization_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,
  ar_lookups lkp,
  gl_ledgers primarylg,
  gl_ledgers lg,
  gl_ledger_relationships rs,
  gl_ledger_configurations cfg,
  gl_ledger_config_details cfgdet,
  hr_organization_information o2,
  hr_organization_information o3
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 o3.org_information3 = to_char(lg.ledger_id)
 AND o3.organization_id = o2.organization_id
 AND o2.org_information_context = 'CLASS'
 AND o3.org_information_context = 'Operating Unit Information'
 AND o2.org_information1 = 'OPERATING_UNIT'
 AND o2.org_information2 = 'Y' )

View Text - HTML Formatted

( SELECT O2.ORGANIZATION_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
, AR_LOOKUPS LKP
, HR_ORGANIZATION_INFORMATION O2
, HR_ORGANIZATION_INFORMATION O3 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 O3.ORG_INFORMATION3 = TO_CHAR(LG.LEDGER_ID)
AND O3.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O2.ORG_INFORMATION_CONTEXT = 'CLASS'
AND O3.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y' UNION SELECT O2.ORGANIZATION_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
, AR_LOOKUPS LKP
, GL_LEDGERS PRIMARYLG
, GL_LEDGERS LG
, GL_LEDGER_RELATIONSHIPS RS
, GL_LEDGER_CONFIGURATIONS CFG
, GL_LEDGER_CONFIG_DETAILS CFGDET
, HR_ORGANIZATION_INFORMATION O2
, HR_ORGANIZATION_INFORMATION O3 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 O3.ORG_INFORMATION3 = TO_CHAR(LG.LEDGER_ID)
AND O3.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O2.ORG_INFORMATION_CONTEXT = 'CLASS'
AND O3.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y' )