[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | The entity retrieves all the records that are Institutions that are either created in the OSS subsystem or from the Oracle Receivables system. |
| Implementation/DBA Data: | Not implemented in this database |
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 (+)