[Home] [Help]
View: XLE_LE_FROM_REGISTRATIONS
View Text
(
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' )