VIEW: APPS.IGS_OR_INST_HIST_V

Source

View Text - Preformatted

SELECT ih1.institution_cd, ih1.hist_start_dt, ih1.hist_end_dt, ih1.hist_who, ih1.name name, ih1.inst_phone_country_code inst_phone_country_code, ih1.inst_phone_area_code inst_phone_area_code, ih1.inst_phone_number inst_phone_number, ih1.inst_priority_cd institution_priority, inp.meaning priority_description, ih1.eps_code eps_code, ih1.institution_status institution_status, istat.description inst_status_desc, ih1.local_institution_ind local_institution_ind, ih1.os_ind os_ind, ih1.govt_institution_cd govt_institution_cd, ih1.description description, ih1.institution_type institution_type, oit.description inst_type_desc, ih1.inst_control_type inst_control_type, oict.meaning inst_ctl_type_desc, ih1.created_by, ih1.creation_date, ih1.last_updated_by, ih1.last_update_date, ih1.last_update_login FROM IGS_OR_INST_HIST_ALL ih1, IGS_LOOKUP_VALUES inp, IGS_OR_ORG_INST_TYPE oit, IGS_LOOKUP_VALUES oict, IGS_OR_INST_STAT istat WHERE ih1.INST_PRIORITY_CD=inp.LOOKUP_CODE(+) AND inp.LOOKUP_TYPE(+) = 'OR_INST_PRIORITY_CD' AND ih1.institution_type = oit.institution_type AND ih1.inst_control_type = oict.LOOKUP_CODE AND oict.LOOKUP_TYPE = 'OR_INST_CTL_TYPE' AND ih1.institution_status = istat.institution_status UNION ALL SELECT IHP.OSS_ORG_UNIT_CD, hp.last_update_date, TO_DATE(NULL), hp.last_updated_by, hp.party_name, ihp.inst_phone_country_code, ihp.inst_phone_area_code, ihp.inst_phone_number, ihp.inst_priority_cd, lk1.meaning, ihp.inst_eps_code, ihp.oi_institution_status, istat2.description, ihp.oi_local_institution_ind, ihp.oi_os_ind, ihp.oi_govt_institution_cd, gic2.meaning, ihp.oi_institution_type, oit2.description, ihp.oi_inst_control_type, lk2.meaning, hp.created_by, hp.creation_date, hp.last_updated_by, hp.last_update_date, hp.last_update_login FROM IGS_PE_HZ_PARTIES ihp, HZ_PARTIES hp, IGS_LOOKUP_VALUES gic2, IGS_OR_ORG_INST_TYPE oit2, IGS_OR_INST_STAT istat2 , IGS_LOOKUP_VALUES lk1, IGS_LOOKUP_VALUES lk2 WHERE hp.party_id = ihp.party_id AND ihp.inst_org_ind = 'I' AND ihp.oi_govt_institution_cd = gic2.lookup_code (+) AND gic2.lookup_type (+) = 'OR_INST_GOV_CD' AND ihp.oi_institution_type = oit2.institution_type AND ihp.oi_institution_status = istat2.institution_status AND ihp.INST_PRIORITY_CD = lk1.LOOKUP_CODE(+) AND lk1.LOOKUP_TYPE(+) = 'OR_INST_PRIORITY_CD' AND ihp.oi_inst_control_type = lk2.LOOKUP_CODE AND lk2.LOOKUP_TYPE = 'OR_INST_CTL_TYPE' GROUP BY hp.party_number, hp.party_name, ihp.oss_org_unit_cd, ihp.inst_phone_country_code, ihp.inst_phone_area_code, ihp.inst_phone_number, ihp.inst_priority_cd, lk1.meaning, ihp.inst_eps_code, ihp.oi_institution_status, istat2.description, ihp.oi_local_institution_ind, ihp.oi_os_ind, ihp.oi_govt_institution_cd, gic2.meaning, ihp.oi_institution_type, oit2.description, ihp.oi_inst_control_type, lk2.meaning, hp.created_by, hp.creation_date, hp.last_updated_by, hp.last_update_date, hp.last_update_login

View Text - HTML Formatted

SELECT IH1.INSTITUTION_CD
, IH1.HIST_START_DT
, IH1.HIST_END_DT
, IH1.HIST_WHO
, IH1.NAME NAME
, IH1.INST_PHONE_COUNTRY_CODE INST_PHONE_COUNTRY_CODE
, IH1.INST_PHONE_AREA_CODE INST_PHONE_AREA_CODE
, IH1.INST_PHONE_NUMBER INST_PHONE_NUMBER
, IH1.INST_PRIORITY_CD INSTITUTION_PRIORITY
, INP.MEANING PRIORITY_DESCRIPTION
, IH1.EPS_CODE EPS_CODE
, IH1.INSTITUTION_STATUS INSTITUTION_STATUS
, ISTAT.DESCRIPTION INST_STATUS_DESC
, IH1.LOCAL_INSTITUTION_IND LOCAL_INSTITUTION_IND
, IH1.OS_IND OS_IND
, IH1.GOVT_INSTITUTION_CD GOVT_INSTITUTION_CD
, IH1.DESCRIPTION DESCRIPTION
, IH1.INSTITUTION_TYPE INSTITUTION_TYPE
, OIT.DESCRIPTION INST_TYPE_DESC
, IH1.INST_CONTROL_TYPE INST_CONTROL_TYPE
, OICT.MEANING INST_CTL_TYPE_DESC
, IH1.CREATED_BY
, IH1.CREATION_DATE
, IH1.LAST_UPDATED_BY
, IH1.LAST_UPDATE_DATE
, IH1.LAST_UPDATE_LOGIN
FROM IGS_OR_INST_HIST_ALL IH1
, IGS_LOOKUP_VALUES INP
, IGS_OR_ORG_INST_TYPE OIT
, IGS_LOOKUP_VALUES OICT
, IGS_OR_INST_STAT ISTAT
WHERE IH1.INST_PRIORITY_CD=INP.LOOKUP_CODE(+)
AND INP.LOOKUP_TYPE(+) = 'OR_INST_PRIORITY_CD'
AND IH1.INSTITUTION_TYPE = OIT.INSTITUTION_TYPE
AND IH1.INST_CONTROL_TYPE = OICT.LOOKUP_CODE
AND OICT.LOOKUP_TYPE = 'OR_INST_CTL_TYPE'
AND IH1.INSTITUTION_STATUS = ISTAT.INSTITUTION_STATUS UNION ALL SELECT IHP.OSS_ORG_UNIT_CD
, HP.LAST_UPDATE_DATE
, TO_DATE(NULL)
, HP.LAST_UPDATED_BY
, HP.PARTY_NAME
, IHP.INST_PHONE_COUNTRY_CODE
, IHP.INST_PHONE_AREA_CODE
, IHP.INST_PHONE_NUMBER
, IHP.INST_PRIORITY_CD
, LK1.MEANING
, IHP.INST_EPS_CODE
, IHP.OI_INSTITUTION_STATUS
, ISTAT2.DESCRIPTION
, IHP.OI_LOCAL_INSTITUTION_IND
, IHP.OI_OS_IND
, IHP.OI_GOVT_INSTITUTION_CD
, GIC2.MEANING
, IHP.OI_INSTITUTION_TYPE
, OIT2.DESCRIPTION
, IHP.OI_INST_CONTROL_TYPE
, LK2.MEANING
, HP.CREATED_BY
, HP.CREATION_DATE
, HP.LAST_UPDATED_BY
, HP.LAST_UPDATE_DATE
, HP.LAST_UPDATE_LOGIN
FROM IGS_PE_HZ_PARTIES IHP
, HZ_PARTIES HP
, IGS_LOOKUP_VALUES GIC2
, IGS_OR_ORG_INST_TYPE OIT2
, IGS_OR_INST_STAT ISTAT2
, IGS_LOOKUP_VALUES LK1
, IGS_LOOKUP_VALUES LK2
WHERE HP.PARTY_ID = IHP.PARTY_ID
AND IHP.INST_ORG_IND = 'I'
AND IHP.OI_GOVT_INSTITUTION_CD = GIC2.LOOKUP_CODE (+)
AND GIC2.LOOKUP_TYPE (+) = 'OR_INST_GOV_CD'
AND IHP.OI_INSTITUTION_TYPE = OIT2.INSTITUTION_TYPE
AND IHP.OI_INSTITUTION_STATUS = ISTAT2.INSTITUTION_STATUS
AND IHP.INST_PRIORITY_CD = LK1.LOOKUP_CODE(+)
AND LK1.LOOKUP_TYPE(+) = 'OR_INST_PRIORITY_CD'
AND IHP.OI_INST_CONTROL_TYPE = LK2.LOOKUP_CODE
AND LK2.LOOKUP_TYPE = 'OR_INST_CTL_TYPE' GROUP BY HP.PARTY_NUMBER
, HP.PARTY_NAME
, IHP.OSS_ORG_UNIT_CD
, IHP.INST_PHONE_COUNTRY_CODE
, IHP.INST_PHONE_AREA_CODE
, IHP.INST_PHONE_NUMBER
, IHP.INST_PRIORITY_CD
, LK1.MEANING
, IHP.INST_EPS_CODE
, IHP.OI_INSTITUTION_STATUS
, ISTAT2.DESCRIPTION
, IHP.OI_LOCAL_INSTITUTION_IND
, IHP.OI_OS_IND
, IHP.OI_GOVT_INSTITUTION_CD
, GIC2.MEANING
, IHP.OI_INSTITUTION_TYPE
, OIT2.DESCRIPTION
, IHP.OI_INST_CONTROL_TYPE
, LK2.MEANING
, HP.CREATED_BY
, HP.CREATION_DATE
, HP.LAST_UPDATED_BY
, HP.LAST_UPDATE_DATE
, HP.LAST_UPDATE_LOGIN