[Home] [Help]
(
SELECT r.REGISTRATION_ID,
lkp.LOOKUP_CODE LEGISLATIVE_CATEGORY_CODE,
lkp.MEANING LEGISLATIVE_CATEGORY_MEANING,
r.REGISTRATION_NUMBER REGISTRATION_NUMBER,
rgm.COUNTRY_CODE COUNTRY_OF_REGISTRATION,
r.TAX_REGIME_CODE || DECODE(r.TAX, NULL, NULL, '-' || r.TAX) TRANSACTION_TAX_REGN_CONTEXT,
r.EFFECTIVE_FROM EFFECTIVE_FROM,
r.EFFECTIVE_TO EFFECTIVE_TO,
e.LEGAL_ENTITY_ID LEGAL_ENTITY_ID,
e.NAME LEGAL_ENTITY_NAME
FROM ZX_REGISTRATIONS r,
ZX_PARTY_TAX_PROFILE p,
XLE_ETB_PROFILES e,
ZX_REGIMES_B rgm,
AR_LOOKUPS lkp
WHERE r.PARTY_TAX_PROFILE_ID = p.PARTY_TAX_PROFILE_ID
AND p.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
AND p.PARTY_ID = e.PARTY_ID
AND r.TAX_REGIME_CODE = rgm.TAX_REGIME_CODE
AND lkp.LOOKUP_TYPE = 'LEGISLATIVE_CATEGORY'
AND lkp.LOOKUP_CODE = 'TRANSACTION_TAX'
UNION
SELECT r.REGISTRATION_ID,
lkp.LOOKUP_CODE LEGISLATIVE_CATEGORY_CODE,
lkp.MEANING LEGISLATIVE_CATEGORY_MEANING,
r.REGISTRATION_NUMBER,
g.GEOGRAPHY_CODE COUNTRY_OF_REGISTRATION,
NULL TRANSACTION_TAX_REGN_CONTEXT,
r.EFFECTIVE_FROM,
r.EFFECTIVE_TO,
e.LEGAL_ENTITY_ID,
e.NAME LEGAL_ENTITY_NAME
FROM XLE_REGISTRATIONS r,
XLE_ENTITY_PROFILES e,
XLE_JURISDICTIONS_B j,
HZ_GEOGRAPHIES g,
AR_LOOKUPS lkp
WHERE r.SOURCE_ID = e.LEGAL_ENTITY_ID
AND r.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND r.JURISDICTION_ID = j.JURISDICTION_ID
AND j.GEOGRAPHY_ID = g.GEOGRAPHY_ID
AND lkp.LOOKUP_TYPE = 'LEGISLATIVE_CATEGORY'
AND lkp.LOOKUP_CODE = j.LEGISLATIVE_CAT_CODE)
(
SELECT R.REGISTRATION_ID
,
LKP.LOOKUP_CODE LEGISLATIVE_CATEGORY_CODE
,
LKP.MEANING LEGISLATIVE_CATEGORY_MEANING
,
R.REGISTRATION_NUMBER REGISTRATION_NUMBER
,
RGM.COUNTRY_CODE COUNTRY_OF_REGISTRATION
,
R.TAX_REGIME_CODE || DECODE(R.TAX
, NULL
, NULL
, '-' || R.TAX) TRANSACTION_TAX_REGN_CONTEXT
,
R.EFFECTIVE_FROM EFFECTIVE_FROM
,
R.EFFECTIVE_TO EFFECTIVE_TO
,
E.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
,
E.NAME LEGAL_ENTITY_NAME
FROM ZX_REGISTRATIONS R
,
ZX_PARTY_TAX_PROFILE P
,
XLE_ETB_PROFILES E
,
ZX_REGIMES_B RGM
,
AR_LOOKUPS LKP
WHERE R.PARTY_TAX_PROFILE_ID = P.PARTY_TAX_PROFILE_ID
AND P.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
AND P.PARTY_ID = E.PARTY_ID
AND R.TAX_REGIME_CODE = RGM.TAX_REGIME_CODE
AND LKP.LOOKUP_TYPE = 'LEGISLATIVE_CATEGORY'
AND LKP.LOOKUP_CODE = 'TRANSACTION_TAX'
UNION
SELECT R.REGISTRATION_ID
,
LKP.LOOKUP_CODE LEGISLATIVE_CATEGORY_CODE
,
LKP.MEANING LEGISLATIVE_CATEGORY_MEANING
,
R.REGISTRATION_NUMBER
,
G.GEOGRAPHY_CODE COUNTRY_OF_REGISTRATION
,
NULL TRANSACTION_TAX_REGN_CONTEXT
,
R.EFFECTIVE_FROM
,
R.EFFECTIVE_TO
,
E.LEGAL_ENTITY_ID
,
E.NAME LEGAL_ENTITY_NAME
FROM XLE_REGISTRATIONS R
,
XLE_ENTITY_PROFILES E
,
XLE_JURISDICTIONS_B J
,
HZ_GEOGRAPHIES G
,
AR_LOOKUPS LKP
WHERE R.SOURCE_ID = E.LEGAL_ENTITY_ID
AND R.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND R.JURISDICTION_ID = J.JURISDICTION_ID
AND J.GEOGRAPHY_ID = G.GEOGRAPHY_ID
AND LKP.LOOKUP_TYPE = 'LEGISLATIVE_CATEGORY'
AND LKP.LOOKUP_CODE = J.LEGISLATIVE_CAT_CODE)
|
|
|
|