VIEW: APPS.IGS_OR_UNIT_OUTER_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)