VIEW: APPS.JAI_RGM_ORGANIZATIONS_V

Source

View Text - Preformatted

SELECT o.name, o.organization_id, jor.regime_code, jor.regime_id , jor.attribute_value, jor.location_id FROM HR_ALL_ORGANIZATION_UNITS O, HR_ORGANIZATION_INFORMATION O2, ( SELECT rgms.regime_id regime_id , rgms.regime_code regime_code , rgpt.location_id location_id , rgrg.attribute_value attribute_value , rgpt.organization_type organization_type , rgrg.registration_type registration_type , rgrg.attribute_code attribute_code , rgrg.attribute_type_code attribute_type_code , rgpt.organization_id organization_id FROM JAI_RGM_DEFINITIONS rgms, jai_rgm_parties rgpt, JAI_RGM_REGISTRATIONS rgrg WHERE rgms.regime_id = rgpt.regime_id AND rgms.regime_id = rgrg.regime_id AND NOT EXISTS ( SELECT 1 FROM jai_rgm_party_regns rprg WHERE rprg.regime_org_id = rgpt.regime_org_id AND rprg.registration_id = rgrg.registration_id ) UNION ALL SELECT rgms.regime_id regime_id , rgms.regime_code regime_code , rgpt.location_id location_id , rprg.attribute_value attribute_value , rgpt.organization_type organization_type , rgrg.registration_type registration_type , rgrg.attribute_code attribute_code , rgrg.attribute_type_code attribute_type_code , rgpt.organization_id organization_id FROM JAI_RGM_DEFINITIONS rgms, jai_rgm_parties rgpt, JAI_RGM_REGISTRATIONS rgrg, jai_rgm_party_regns rprg WHERE rgms.regime_id = rgpt.regime_id AND rgms.regime_id = rgrg.regime_id AND rgrg.registration_id = rprg.registration_id AND rprg.regime_org_id = rgpt.regime_org_id ) jor WHERE O.organization_id = jor.organization_id AND O.ORGANIZATION_ID = O2.ORGANIZATION_ID AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS' AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT' AND O2.ORG_INFORMATION2 = 'Y' AND jor.organization_type = 'OU' AND jor.registration_type = 'OTHERS' AND jor.attribute_type_code = 'PRIMARY' AND ((jor.regime_code = 'VAT' AND jor.attribute_code = 'REGISTRATION_NO' ) OR (jor.regime_code = 'SERVICE' AND jor.attribute_code = 'SERVICE_TAX_REGISTRATION_NO' ) ) UNION ALL SELECT o.name, O.organization_id, jor.regime_code, jor.regime_id, jor.attribute_value, jor.location_id FROM HR_ALL_ORGANIZATION_UNITS O, ( SELECT rgms.regime_id regime_id , rgms.regime_code regime_code , rgpt.location_id location_id , rgrg.attribute_value attribute_value , rgpt.organization_type organization_type , rgrg.registration_type registration_type , rgrg.attribute_code attribute_code , rgrg.attribute_type_code attribute_type_code , rgpt.organization_id organization_id FROM JAI_RGM_DEFINITIONS rgms, jai_rgm_parties rgpt, JAI_RGM_REGISTRATIONS rgrg WHERE rgms.regime_id = rgpt.regime_id AND rgms.regime_id = rgrg.regime_id AND NOT EXISTS ( SELECT 1 FROM jai_rgm_party_regns rprg WHERE rprg.regime_org_id = rgpt.regime_org_id AND rprg.registration_id = rgrg.registration_id ) UNION ALL SELECT rgms.regime_id regime_id , rgms.regime_code regime_code , rgpt.location_id location_id , rprg.attribute_value attribute_value , rgpt.organization_type organization_type , rgrg.registration_type registration_type , rgrg.attribute_code attribute_code , rgrg.attribute_type_code attribute_type_code , rgpt.organization_id organization_id FROM JAI_RGM_DEFINITIONS rgms, jai_rgm_parties rgpt, JAI_RGM_REGISTRATIONS rgrg, jai_rgm_party_regns rprg WHERE rgms.regime_id = rgpt.regime_id AND rgms.regime_id = rgrg.regime_id AND rgrg.registration_id = rprg.registration_id AND rprg.regime_org_id = rgpt.regime_org_id ) jor WHERE O.organization_id = jor.organization_id AND jor.organization_type = 'IO' AND jor.registration_type = 'OTHERS' AND jor.attribute_type_code = 'PRIMARY' AND ((jor.regime_code = 'VAT' AND jor.attribute_code ='REGISTRATION_NO' ) OR ( jor.regime_code = 'SERVICE' AND jor.attribute_code ='SERVICE_TAX_REGISTRATION_NO' ))

View Text - HTML Formatted

SELECT O.NAME
, O.ORGANIZATION_ID
, JOR.REGIME_CODE
, JOR.REGIME_ID
, JOR.ATTRIBUTE_VALUE
, JOR.LOCATION_ID
FROM HR_ALL_ORGANIZATION_UNITS O
, HR_ORGANIZATION_INFORMATION O2
, ( SELECT RGMS.REGIME_ID REGIME_ID
, RGMS.REGIME_CODE REGIME_CODE
, RGPT.LOCATION_ID LOCATION_ID
, RGRG.ATTRIBUTE_VALUE ATTRIBUTE_VALUE
, RGPT.ORGANIZATION_TYPE ORGANIZATION_TYPE
, RGRG.REGISTRATION_TYPE REGISTRATION_TYPE
, RGRG.ATTRIBUTE_CODE ATTRIBUTE_CODE
, RGRG.ATTRIBUTE_TYPE_CODE ATTRIBUTE_TYPE_CODE
, RGPT.ORGANIZATION_ID ORGANIZATION_ID
FROM JAI_RGM_DEFINITIONS RGMS
, JAI_RGM_PARTIES RGPT
, JAI_RGM_REGISTRATIONS RGRG
WHERE RGMS.REGIME_ID = RGPT.REGIME_ID
AND RGMS.REGIME_ID = RGRG.REGIME_ID
AND NOT EXISTS ( SELECT 1
FROM JAI_RGM_PARTY_REGNS RPRG
WHERE RPRG.REGIME_ORG_ID = RGPT.REGIME_ORG_ID
AND RPRG.REGISTRATION_ID = RGRG.REGISTRATION_ID ) UNION ALL SELECT RGMS.REGIME_ID REGIME_ID
, RGMS.REGIME_CODE REGIME_CODE
, RGPT.LOCATION_ID LOCATION_ID
, RPRG.ATTRIBUTE_VALUE ATTRIBUTE_VALUE
, RGPT.ORGANIZATION_TYPE ORGANIZATION_TYPE
, RGRG.REGISTRATION_TYPE REGISTRATION_TYPE
, RGRG.ATTRIBUTE_CODE ATTRIBUTE_CODE
, RGRG.ATTRIBUTE_TYPE_CODE ATTRIBUTE_TYPE_CODE
, RGPT.ORGANIZATION_ID ORGANIZATION_ID
FROM JAI_RGM_DEFINITIONS RGMS
, JAI_RGM_PARTIES RGPT
, JAI_RGM_REGISTRATIONS RGRG
, JAI_RGM_PARTY_REGNS RPRG
WHERE RGMS.REGIME_ID = RGPT.REGIME_ID
AND RGMS.REGIME_ID = RGRG.REGIME_ID
AND RGRG.REGISTRATION_ID = RPRG.REGISTRATION_ID
AND RPRG.REGIME_ORG_ID = RGPT.REGIME_ORG_ID ) JOR
WHERE O.ORGANIZATION_ID = JOR.ORGANIZATION_ID
AND O.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O2.ORG_INFORMATION_CONTEXT||'' = 'CLASS'
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y'
AND JOR.ORGANIZATION_TYPE = 'OU'
AND JOR.REGISTRATION_TYPE = 'OTHERS'
AND JOR.ATTRIBUTE_TYPE_CODE = 'PRIMARY'
AND ((JOR.REGIME_CODE = 'VAT'
AND JOR.ATTRIBUTE_CODE = 'REGISTRATION_NO' ) OR (JOR.REGIME_CODE = 'SERVICE'
AND JOR.ATTRIBUTE_CODE = 'SERVICE_TAX_REGISTRATION_NO' ) ) UNION ALL SELECT O.NAME
, O.ORGANIZATION_ID
, JOR.REGIME_CODE
, JOR.REGIME_ID
, JOR.ATTRIBUTE_VALUE
, JOR.LOCATION_ID
FROM HR_ALL_ORGANIZATION_UNITS O
, ( SELECT RGMS.REGIME_ID REGIME_ID
, RGMS.REGIME_CODE REGIME_CODE
, RGPT.LOCATION_ID LOCATION_ID
, RGRG.ATTRIBUTE_VALUE ATTRIBUTE_VALUE
, RGPT.ORGANIZATION_TYPE ORGANIZATION_TYPE
, RGRG.REGISTRATION_TYPE REGISTRATION_TYPE
, RGRG.ATTRIBUTE_CODE ATTRIBUTE_CODE
, RGRG.ATTRIBUTE_TYPE_CODE ATTRIBUTE_TYPE_CODE
, RGPT.ORGANIZATION_ID ORGANIZATION_ID
FROM JAI_RGM_DEFINITIONS RGMS
, JAI_RGM_PARTIES RGPT
, JAI_RGM_REGISTRATIONS RGRG
WHERE RGMS.REGIME_ID = RGPT.REGIME_ID
AND RGMS.REGIME_ID = RGRG.REGIME_ID
AND NOT EXISTS ( SELECT 1
FROM JAI_RGM_PARTY_REGNS RPRG
WHERE RPRG.REGIME_ORG_ID = RGPT.REGIME_ORG_ID
AND RPRG.REGISTRATION_ID = RGRG.REGISTRATION_ID ) UNION ALL SELECT RGMS.REGIME_ID REGIME_ID
, RGMS.REGIME_CODE REGIME_CODE
, RGPT.LOCATION_ID LOCATION_ID
, RPRG.ATTRIBUTE_VALUE ATTRIBUTE_VALUE
, RGPT.ORGANIZATION_TYPE ORGANIZATION_TYPE
, RGRG.REGISTRATION_TYPE REGISTRATION_TYPE
, RGRG.ATTRIBUTE_CODE ATTRIBUTE_CODE
, RGRG.ATTRIBUTE_TYPE_CODE ATTRIBUTE_TYPE_CODE
, RGPT.ORGANIZATION_ID ORGANIZATION_ID
FROM JAI_RGM_DEFINITIONS RGMS
, JAI_RGM_PARTIES RGPT
, JAI_RGM_REGISTRATIONS RGRG
, JAI_RGM_PARTY_REGNS RPRG
WHERE RGMS.REGIME_ID = RGPT.REGIME_ID
AND RGMS.REGIME_ID = RGRG.REGIME_ID
AND RGRG.REGISTRATION_ID = RPRG.REGISTRATION_ID
AND RPRG.REGIME_ORG_ID = RGPT.REGIME_ORG_ID ) JOR
WHERE O.ORGANIZATION_ID = JOR.ORGANIZATION_ID
AND JOR.ORGANIZATION_TYPE = 'IO'
AND JOR.REGISTRATION_TYPE = 'OTHERS'
AND JOR.ATTRIBUTE_TYPE_CODE = 'PRIMARY'
AND ((JOR.REGIME_CODE = 'VAT'
AND JOR.ATTRIBUTE_CODE ='REGISTRATION_NO' ) OR ( JOR.REGIME_CODE = 'SERVICE'
AND JOR.ATTRIBUTE_CODE ='SERVICE_TAX_REGISTRATION_NO' ))