VIEW: APPS.IGS_DA_XML_TE_INSTITUTION_V

Source

View Text - Preformatted

SELECT cwdt.alt_id_type, cwdt.alt_id_value, '', drf.batch_id from igs_da_req_ftrs drf, igs_or_cwlk_dtl cwdt, igs_or_cwlk cwlk, igs_da_setup ds WHERE drf.feature_code = 'EOI' and cwlk.institution_code = drf.feature_value and cwlk.crosswalk_id = cwdt.crosswalk_id and cwdt.alt_id_type = ds.default_inst_id_type and ds.s_control_num = 1 UNION ALL select 'OSS', hp.party_number, '', drf.batch_id from igs_da_req_ftrs drf, hz_parties hp, igs_pe_hz_parties ipz where drf.feature_code = 'EOI' and ipz.oss_org_unit_cd = drf.feature_value AND ipz.party_id = hp.party_id and NOT EXISTS(select 'X' from igs_or_cwlk_dtl cwdt, igs_or_cwlk cwlk, igs_da_setup ds where ipz.oss_org_unit_cd = cwlk.institution_code and ipz.party_id = hp.party_id and cwlk.crosswalk_id = cwdt.crosswalk_id and cwdt.alt_id_type = ds.default_inst_id_type and ds.s_control_num = 1)

View Text - HTML Formatted

SELECT CWDT.ALT_ID_TYPE
, CWDT.ALT_ID_VALUE
, ''
, DRF.BATCH_ID
FROM IGS_DA_REQ_FTRS DRF
, IGS_OR_CWLK_DTL CWDT
, IGS_OR_CWLK CWLK
, IGS_DA_SETUP DS
WHERE DRF.FEATURE_CODE = 'EOI'
AND CWLK.INSTITUTION_CODE = DRF.FEATURE_VALUE
AND CWLK.CROSSWALK_ID = CWDT.CROSSWALK_ID
AND CWDT.ALT_ID_TYPE = DS.DEFAULT_INST_ID_TYPE
AND DS.S_CONTROL_NUM = 1 UNION ALL SELECT 'OSS'
, HP.PARTY_NUMBER
, ''
, DRF.BATCH_ID
FROM IGS_DA_REQ_FTRS DRF
, HZ_PARTIES HP
, IGS_PE_HZ_PARTIES IPZ
WHERE DRF.FEATURE_CODE = 'EOI'
AND IPZ.OSS_ORG_UNIT_CD = DRF.FEATURE_VALUE
AND IPZ.PARTY_ID = HP.PARTY_ID
AND NOT EXISTS(SELECT 'X'
FROM IGS_OR_CWLK_DTL CWDT
, IGS_OR_CWLK CWLK
, IGS_DA_SETUP DS
WHERE IPZ.OSS_ORG_UNIT_CD = CWLK.INSTITUTION_CODE
AND IPZ.PARTY_ID = HP.PARTY_ID
AND CWLK.CROSSWALK_ID = CWDT.CROSSWALK_ID
AND CWDT.ALT_ID_TYPE = DS.DEFAULT_INST_ID_TYPE
AND DS.S_CONTROL_NUM = 1)