VIEW: APPS.IGS_DA_XML_INSTITUTION_V

Source

View Text - Preformatted

SELECT oai.org_alternate_id_type, oai.org_alternate_id, '', oai.org_structure_id from IGS_OR_ORG_ALT_IDS oai, IGS_DA_SETUP ds WHERE oai.org_alternate_id_type = ds.default_inst_id_type and oai.end_date is NULL and ds.s_control_num = 1 UNION ALL select 'OSS', hp.party_number, '', hp.party_number from hz_parties hp where NOT EXISTS(select 'X' FROM igs_or_org_alt_ids oai, igs_da_setup ds, IGS_pe_hz_PARTIES IPZ WHERE oai.org_alternate_id_type = ds.default_inst_id_type AND oai.end_date is NULL and ds.s_control_num = 1 AND oai.org_structure_id = ipz.oss_org_unit_cd AND ipz.party_id = hp.party_id)

View Text - HTML Formatted

SELECT OAI.ORG_ALTERNATE_ID_TYPE
, OAI.ORG_ALTERNATE_ID
, ''
, OAI.ORG_STRUCTURE_ID
FROM IGS_OR_ORG_ALT_IDS OAI
, IGS_DA_SETUP DS
WHERE OAI.ORG_ALTERNATE_ID_TYPE = DS.DEFAULT_INST_ID_TYPE
AND OAI.END_DATE IS NULL
AND DS.S_CONTROL_NUM = 1 UNION ALL SELECT 'OSS'
, HP.PARTY_NUMBER
, ''
, HP.PARTY_NUMBER
FROM HZ_PARTIES HP
WHERE NOT EXISTS(SELECT 'X'
FROM IGS_OR_ORG_ALT_IDS OAI
, IGS_DA_SETUP DS
, IGS_PE_HZ_PARTIES IPZ
WHERE OAI.ORG_ALTERNATE_ID_TYPE = DS.DEFAULT_INST_ID_TYPE
AND OAI.END_DATE IS NULL
AND DS.S_CONTROL_NUM = 1
AND OAI.ORG_STRUCTURE_ID = IPZ.OSS_ORG_UNIT_CD
AND IPZ.PARTY_ID = HP.PARTY_ID)