VIEW: APPS.IGSFV_ORGANIZATION_UNIT_PARTY

Source

View Text - Preformatted

SELECT HP.PARTY_ID , PHP.OSS_ORG_UNIT_CD ORG_UNIT_CODE, HP.PARTY_NUMBER TCA_PARTY_NUMBER , HP.PARTY_NAME, PHP.INSTITUTION_CD, PHP.OU_START_DT, PHP.OU_END_DT, '_LA:PHP.OU_MEMBER_TYPE:IGS_LOOKUP_VALUES:OR_MEMBER_TYPE:MEANING', PHP.OU_ORG_STATUS, '_LA:PHP.OU_ORG_TYPE:IGS_LOOKUP_VALUES:OR_ORG_TYPE:MEANING', PID.PREF_UNIT_ID_TYPE, PID.PREF_UNIT_ALT_ID, OS.DESCRIPTION, PHP.CREATED_BY, PHP.CREATION_DATE, PHP.LAST_UPDATED_BY, PHP.LAST_UPDATE_DATE, PHP.LAST_UPDATE_LOGIN FROM IGS_PE_HZ_PARTIES PHP, HZ_PARTIES HP, IGS_OR_STATUS OS, (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 = PHP.PARTY_ID AND PHP.OU_ORG_STATUS = OS.ORG_STATUS(+) AND HP.PARTY_TYPE = 'ORGANIZATION' AND PHP.INST_ORG_IND = 'O' AND PHP.OSS_ORG_UNIT_CD = PID.ORG_STRUCTURE_ID(+) WITH READ ONLY

View Text - HTML Formatted

SELECT HP.PARTY_ID
, PHP.OSS_ORG_UNIT_CD ORG_UNIT_CODE
, HP.PARTY_NUMBER TCA_PARTY_NUMBER
, HP.PARTY_NAME
, PHP.INSTITUTION_CD
, PHP.OU_START_DT
, PHP.OU_END_DT
, '_LA:PHP.OU_MEMBER_TYPE:IGS_LOOKUP_VALUES:OR_MEMBER_TYPE:MEANING'
, PHP.OU_ORG_STATUS
, '_LA:PHP.OU_ORG_TYPE:IGS_LOOKUP_VALUES:OR_ORG_TYPE:MEANING'
, PID.PREF_UNIT_ID_TYPE
, PID.PREF_UNIT_ALT_ID
, OS.DESCRIPTION
, PHP.CREATED_BY
, PHP.CREATION_DATE
, PHP.LAST_UPDATED_BY
, PHP.LAST_UPDATE_DATE
, PHP.LAST_UPDATE_LOGIN
FROM IGS_PE_HZ_PARTIES PHP
, HZ_PARTIES HP
, IGS_OR_STATUS OS
, (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 = PHP.PARTY_ID
AND PHP.OU_ORG_STATUS = OS.ORG_STATUS(+)
AND HP.PARTY_TYPE = 'ORGANIZATION'
AND PHP.INST_ORG_IND = 'O'
AND PHP.OSS_ORG_UNIT_CD = PID.ORG_STRUCTURE_ID(+) WITH READ ONLY