VIEW: APPS.IGS_OR_INST_OUTER_V

Source

View Text - Preformatted

SELECT HP.ROWID ROW_ID , HP.PARTY_ID , HP.PARTY_NUMBER TCA_PARTY_NUMBER, NVL(IHP.OSS_ORG_UNIT_CD,HP.PARTY_NUMBER) INSTITUTION_CD, IHP.OSS_ORG_UNIT_CD OSS_ORG_UNIT_CD, HP.PARTY_NAME , IHP.OI_INSTITUTION_STATUS , IHP.OI_LOCAL_INSTITUTION_IND , IHP.OI_OS_IND , IHP.OI_GOVT_INSTITUTION_CD , IHP.OI_INSTITUTION_TYPE , OIT.SYSTEM_INST_TYPE, OIT.DESCRIPTION , IHP.OI_INST_CONTROL_TYPE , OICT.MEANING CTRL_DESCRIPTION , pid.PREF_INST_ID_TYPE, pid.PREF_INST_ALT_ID, HP.LAST_UPDATED_BY , HP.LAST_UPDATE_DATE , HP.CREATED_BY , HP.CREATION_DATE , HP.LAST_UPDATE_LOGIN , HP.ATTRIBUTE_CATEGORY , HP.ATTRIBUTE1 , HP.ATTRIBUTE2 , HP.ATTRIBUTE3 , HP.ATTRIBUTE4 , HP.ATTRIBUTE5 , HP.ATTRIBUTE6 , HP.ATTRIBUTE7 , HP.ATTRIBUTE8 , HP.ATTRIBUTE9 , HP.ATTRIBUTE10 , HP.ATTRIBUTE11 , HP.ATTRIBUTE12 , HP.ATTRIBUTE13 , HP.ATTRIBUTE14 , HP.ATTRIBUTE15 , HP.ATTRIBUTE16 , HP.ATTRIBUTE17 , HP.ATTRIBUTE18 , HP.ATTRIBUTE19 , HP.ATTRIBUTE20 , HP.ATTRIBUTE21, HP.ATTRIBUTE22, HP.ATTRIBUTE23, HP.ATTRIBUTE24, HP.PARTY_TYPE, IHP.INST_EPS_CODE , IHP.INST_PRIORITY_CD , INP.MEANING , IHP.INST_PHONE_COUNTRY_CODE , IHP.INST_PHONE_AREA_CODE , IHP.INST_PHONE_NUMBER , IHP.ADV_STUDIES_CLASSES , IHP.HONORS_CLASSES , IHP.CLASS_SIZE , IHP.SEC_SCHOOL_LOCATION_ID , INP1.NAME , INP1.DESCRIPTION , IHP.PERCENT_PLAN_HIGHER_EDU, HP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER FROM HZ_PARTIES HP, IGS_PE_HZ_PARTIES IHP, IGS_OR_ORG_INST_TYPE OIT, IGS_LOOKUP_VALUES OICT, IGS_LOOKUP_VALUES INP, IGS_AD_CODE_CLASSES INP1, (SELECT oli.ORG_STRUCTURE_ID, oli.ORG_ALTERNATE_ID_TYPE PREF_INST_ID_TYPE, oli.ORG_ALTERNATE_ID PREF_INST_ALT_ID FROM IGS_OR_ORG_ALT_IDS oli, IGS_OR_ORG_ALT_IDTYP olt WHERE oli.ORG_ALTERNATE_ID_TYPE = olt.ORG_ALTERNATE_ID_TYPE AND olt.PREF_INST_IND = 'Y' AND (SYSDATE BETWEEN oli.START_DATE AND NVL(oli.END_DATE, SYSDATE)) ) pid WHERE IHP.PARTY_ID (+) = HP.PARTY_ID AND NVL(IHP.INST_ORG_IND, 'I') = 'I' AND HP.PARTY_TYPE = 'ORGANIZATION' AND IHP.OI_INSTITUTION_TYPE = OIT.INSTITUTION_TYPE (+) AND IHP.OI_INST_CONTROL_TYPE = OICT.LOOKUP_CODE (+) AND OICT.LOOKUP_TYPE(+) = 'OR_INST_CTL_TYPE' AND IHP.INST_PRIORITY_CD = INP.LOOKUP_CODE (+) AND INP.LOOKUP_TYPE(+) = 'OR_INST_PRIORITY_CD' AND IHP.SEC_SCHOOL_LOCATION_ID = INP1.CODE_ID(+) AND IHP.OSS_ORG_UNIT_CD = pid.org_structure_id (+)

View Text - HTML Formatted

SELECT HP.ROWID ROW_ID
, HP.PARTY_ID
, HP.PARTY_NUMBER TCA_PARTY_NUMBER
, NVL(IHP.OSS_ORG_UNIT_CD
, HP.PARTY_NUMBER) INSTITUTION_CD
, IHP.OSS_ORG_UNIT_CD OSS_ORG_UNIT_CD
, HP.PARTY_NAME
, IHP.OI_INSTITUTION_STATUS
, IHP.OI_LOCAL_INSTITUTION_IND
, IHP.OI_OS_IND
, IHP.OI_GOVT_INSTITUTION_CD
, IHP.OI_INSTITUTION_TYPE
, OIT.SYSTEM_INST_TYPE
, OIT.DESCRIPTION
, IHP.OI_INST_CONTROL_TYPE
, OICT.MEANING CTRL_DESCRIPTION
, PID.PREF_INST_ID_TYPE
, PID.PREF_INST_ALT_ID
, HP.LAST_UPDATED_BY
, HP.LAST_UPDATE_DATE
, HP.CREATED_BY
, HP.CREATION_DATE
, HP.LAST_UPDATE_LOGIN
, HP.ATTRIBUTE_CATEGORY
, HP.ATTRIBUTE1
, HP.ATTRIBUTE2
, HP.ATTRIBUTE3
, HP.ATTRIBUTE4
, HP.ATTRIBUTE5
, HP.ATTRIBUTE6
, HP.ATTRIBUTE7
, HP.ATTRIBUTE8
, HP.ATTRIBUTE9
, HP.ATTRIBUTE10
, HP.ATTRIBUTE11
, HP.ATTRIBUTE12
, HP.ATTRIBUTE13
, HP.ATTRIBUTE14
, HP.ATTRIBUTE15
, HP.ATTRIBUTE16
, HP.ATTRIBUTE17
, HP.ATTRIBUTE18
, HP.ATTRIBUTE19
, HP.ATTRIBUTE20
, HP.ATTRIBUTE21
, HP.ATTRIBUTE22
, HP.ATTRIBUTE23
, HP.ATTRIBUTE24
, HP.PARTY_TYPE
, IHP.INST_EPS_CODE
, IHP.INST_PRIORITY_CD
, INP.MEANING
, IHP.INST_PHONE_COUNTRY_CODE
, IHP.INST_PHONE_AREA_CODE
, IHP.INST_PHONE_NUMBER
, IHP.ADV_STUDIES_CLASSES
, IHP.HONORS_CLASSES
, IHP.CLASS_SIZE
, IHP.SEC_SCHOOL_LOCATION_ID
, INP1.NAME
, INP1.DESCRIPTION
, IHP.PERCENT_PLAN_HIGHER_EDU
, HP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
, IGS_OR_ORG_INST_TYPE OIT
, IGS_LOOKUP_VALUES OICT
, IGS_LOOKUP_VALUES INP
, IGS_AD_CODE_CLASSES INP1
, (SELECT OLI.ORG_STRUCTURE_ID
, OLI.ORG_ALTERNATE_ID_TYPE PREF_INST_ID_TYPE
, OLI.ORG_ALTERNATE_ID PREF_INST_ALT_ID
FROM IGS_OR_ORG_ALT_IDS OLI
, IGS_OR_ORG_ALT_IDTYP OLT
WHERE OLI.ORG_ALTERNATE_ID_TYPE = OLT.ORG_ALTERNATE_ID_TYPE
AND OLT.PREF_INST_IND = 'Y'
AND (SYSDATE BETWEEN OLI.START_DATE
AND NVL(OLI.END_DATE
, SYSDATE)) ) PID
WHERE IHP.PARTY_ID (+) = HP.PARTY_ID
AND NVL(IHP.INST_ORG_IND
, 'I') = 'I'
AND HP.PARTY_TYPE = 'ORGANIZATION'
AND IHP.OI_INSTITUTION_TYPE = OIT.INSTITUTION_TYPE (+)
AND IHP.OI_INST_CONTROL_TYPE = OICT.LOOKUP_CODE (+)
AND OICT.LOOKUP_TYPE(+) = 'OR_INST_CTL_TYPE'
AND IHP.INST_PRIORITY_CD = INP.LOOKUP_CODE (+)
AND INP.LOOKUP_TYPE(+) = 'OR_INST_PRIORITY_CD'
AND IHP.SEC_SCHOOL_LOCATION_ID = INP1.CODE_ID(+)
AND IHP.OSS_ORG_UNIT_CD = PID.ORG_STRUCTURE_ID (+)