VIEW: APPS.IGS_OR_UNIT_V

Source

View Text - Preformatted

SELECT IHP.ROWID ROW_ID , HP.PARTY_ID , HP.PARTY_NUMBER TCA_PARTY_NUMBER, IHP.OSS_ORG_UNIT_CD 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 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 HP.PARTY_ID = IHP.PARTY_ID AND IHP.INST_ORG_IND = 'O' 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 IHP.ROWID ROW_ID
, HP.PARTY_ID
, HP.PARTY_NUMBER TCA_PARTY_NUMBER
, IHP.OSS_ORG_UNIT_CD 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
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 HP.PARTY_ID = IHP.PARTY_ID
AND IHP.INST_ORG_IND = 'O'
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 (+)