View: IGS_OR_UNIT_OUTER_V

View Text

SELECT HP.ROWID ROW_ID
, INST_ORG_IND
, HP.PARTY_ID
, HP.PARTY_NUMBER TCA_PARTY_NUMBER
, NVL(IHP.OSS_ORG_UNIT_CD
, HP.PARTY_NUMBER) ORG_UNIT_CD
, IHP.OSS_ORG_UNIT_CD OSS_ORG_UNIT_CD
, IHP.OU_START_DT
, IHP.OU_END_DT
, HP.PARTY_NAME DESCRIPTION
, IHP.OU_ORG_STATUS
, OS.DESCRIPTION
, IHP.OU_ORG_TYPE
, OT.MEANING TYPE_DESC
, IHP.OU_MEMBER_TYPE
, MT.MEANING MEMBER_DESC
, IHP.INSTITUTION_CD
, PID.PREF_UNIT_ID_TYPE
, PID.PREF_UNIT_ALT_ID
, HP.CREATED_BY
, HP.CREATION_DATE
, HP.LAST_UPDATED_BY
, HP.LAST_UPDATE_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
, HP.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
FROM HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IHP
, IGS_OR_STATUS OS
, IGS_LOOKUP_VALUES OT
, IGS_LOOKUP_VALUES MT
, (SELECT OLI.ORG_STRUCTURE_ID
, OLI.ORG_ALTERNATE_ID_TYPE PREF_UNIT_ID_TYPE
, OLI.ORG_ALTERNATE_ID PREF_UNIT_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_UNIT_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
, 'O') = 'O'
AND HP.PARTY_TYPE(+) = 'ORGANIZATION'
AND IHP.OU_ORG_TYPE = OT.LOOKUP_CODE (+)
AND OT.LOOKUP_TYPE(+) = 'OR_ORG_TYPE'
AND IHP.OU_ORG_STATUS = OS.ORG_STATUS (+)
AND IHP.OU_MEMBER_TYPE = MT.LOOKUP_CODE (+)
AND MT.LOOKUP_TYPE(+) = 'OR_MEMBER_TYPE'
AND IHP.OSS_ORG_UNIT_CD = PID.ORG_STRUCTURE_ID (+)

Columns