VIEW: APPS.IGS_OR_UNIT_HIST_V

Source

View Text - Preformatted

SELECT ouh1.org_unit_cd, ouh1.ou_start_dt, ouh1.hist_start_dt, ouh1.hist_end_dt, ouh1.hist_who, NVL( ouh1.ou_end_dt, NVL(igs_ge_date.igsdate( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'OU_END_DT', ouh1.org_unit_cd, ouh1.ou_start_dt, ouh1.hist_end_dt)), ihp.ou_end_dt)) end_dt, NVL( ouh1.description, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'DESCRIPTION', ouh1.org_unit_cd, ouh1.ou_start_dt, ouh1.hist_end_dt),hp.party_name)) description, NVL( ouh1.org_status, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'ORG_STATUS', ouh1.org_unit_cd, ouh1.ou_start_dt, ouh1.hist_end_dt), ihp.ou_org_status)) org_status, NVL( ouh1.org_type, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'ORG_TYPE', ouh1.org_unit_cd, ouh1.ou_start_dt, ouh1.hist_end_dt), ihp.ou_org_type)) org_type, NVL( ouh1.member_type, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'MEMBER_TYPE', ouh1.org_unit_cd, ouh1.ou_start_dt, ouh1.hist_end_dt), ihp.ou_member_type)) member_type, NVL( ouh1.institution_cd, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'INSTITUTION_CD', ouh1.org_unit_cd, ouh1.ou_start_dt, ouh1.hist_end_dt), ihp.institution_cd)) institution_cd, NVL( ouh1.name, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'NAME', ouh1.org_unit_cd, ouh1.ou_start_dt, ouh1.hist_end_dt), hp_inst.party_name)) name, ouh1.created_by, ouh1.creation_date, ouh1.last_updated_by, ouh1.last_update_date, ouh1.last_update_login FROM IGS_OR_UNIT_HIST_ALL ouh1, IGS_PE_HZ_PARTIES ihp, hz_parties hp, hz_parties hp_inst, IGS_PE_HZ_PARTIES ihp_inst WHERE ihp.oss_org_unit_cd = ouh1.org_unit_cd AND hp.party_id = ihp.party_id AND ihp.institution_cd = ihp_inst.oss_org_unit_cd AND ihp_inst.party_id = hp_inst.party_id UNION ALL SELECT hp2.party_number, ihp2.ou_start_dt, hp2.last_update_date, TO_DATE(NULL), hp2.last_updated_by, ihp2.ou_end_dt, hp2.party_name, ihp2.ou_org_status, ihp2.ou_org_type, ihp2.ou_member_type, ihp2.institution_cd, hp_inst2.party_name, hp2.created_by, hp2.creation_date, hp2.last_updated_by, hp2.last_update_date, hp2.last_update_login FROM hz_parties hp2, igs_pe_hz_parties ihp2, hz_parties hp_inst2, igs_pe_hz_parties ihp_inst2 WHERE hp2.party_id = ihp2.party_id AND ihp2.inst_org_ind = 'O' AND ihp2.institution_cd = ihp_inst2.oss_org_unit_cd AND ihp_inst2.party_id = hp_inst2.party_id GROUP BY hp2.party_number, ihp2.ou_start_dt, ihp2.ou_end_dt, hp2.party_name, ihp2.ou_org_status, ihp2.ou_org_type, ihp2.ou_member_type, ihp2.institution_cd, hp_inst2.party_name, hp2.created_by, hp2.creation_date, hp2.last_updated_by, hp2.last_update_date, hp2.last_update_login

View Text - HTML Formatted

SELECT OUH1.ORG_UNIT_CD
, OUH1.OU_START_DT
, OUH1.HIST_START_DT
, OUH1.HIST_END_DT
, OUH1.HIST_WHO
, NVL( OUH1.OU_END_DT
, NVL(IGS_GE_DATE.IGSDATE( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'OU_END_DT'
, OUH1.ORG_UNIT_CD
, OUH1.OU_START_DT
, OUH1.HIST_END_DT))
, IHP.OU_END_DT)) END_DT
, NVL( OUH1.DESCRIPTION
, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'DESCRIPTION'
, OUH1.ORG_UNIT_CD
, OUH1.OU_START_DT
, OUH1.HIST_END_DT)
, HP.PARTY_NAME)) DESCRIPTION
, NVL( OUH1.ORG_STATUS
, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'ORG_STATUS'
, OUH1.ORG_UNIT_CD
, OUH1.OU_START_DT
, OUH1.HIST_END_DT)
, IHP.OU_ORG_STATUS)) ORG_STATUS
, NVL( OUH1.ORG_TYPE
, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'ORG_TYPE'
, OUH1.ORG_UNIT_CD
, OUH1.OU_START_DT
, OUH1.HIST_END_DT)
, IHP.OU_ORG_TYPE)) ORG_TYPE
, NVL( OUH1.MEMBER_TYPE
, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'MEMBER_TYPE'
, OUH1.ORG_UNIT_CD
, OUH1.OU_START_DT
, OUH1.HIST_END_DT)
, IHP.OU_MEMBER_TYPE)) MEMBER_TYPE
, NVL( OUH1.INSTITUTION_CD
, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'INSTITUTION_CD'
, OUH1.ORG_UNIT_CD
, OUH1.OU_START_DT
, OUH1.HIST_END_DT)
, IHP.INSTITUTION_CD)) INSTITUTION_CD
, NVL( OUH1.NAME
, NVL( IGS_AU_GEN_003.AUDP_GET_OUH_COL( 'NAME'
, OUH1.ORG_UNIT_CD
, OUH1.OU_START_DT
, OUH1.HIST_END_DT)
, HP_INST.PARTY_NAME)) NAME
, OUH1.CREATED_BY
, OUH1.CREATION_DATE
, OUH1.LAST_UPDATED_BY
, OUH1.LAST_UPDATE_DATE
, OUH1.LAST_UPDATE_LOGIN
FROM IGS_OR_UNIT_HIST_ALL OUH1
, IGS_PE_HZ_PARTIES IHP
, HZ_PARTIES HP
, HZ_PARTIES HP_INST
, IGS_PE_HZ_PARTIES IHP_INST
WHERE IHP.OSS_ORG_UNIT_CD = OUH1.ORG_UNIT_CD
AND HP.PARTY_ID = IHP.PARTY_ID
AND IHP.INSTITUTION_CD = IHP_INST.OSS_ORG_UNIT_CD
AND IHP_INST.PARTY_ID = HP_INST.PARTY_ID UNION ALL SELECT HP2.PARTY_NUMBER
, IHP2.OU_START_DT
, HP2.LAST_UPDATE_DATE
, TO_DATE(NULL)
, HP2.LAST_UPDATED_BY
, IHP2.OU_END_DT
, HP2.PARTY_NAME
, IHP2.OU_ORG_STATUS
, IHP2.OU_ORG_TYPE
, IHP2.OU_MEMBER_TYPE
, IHP2.INSTITUTION_CD
, HP_INST2.PARTY_NAME
, HP2.CREATED_BY
, HP2.CREATION_DATE
, HP2.LAST_UPDATED_BY
, HP2.LAST_UPDATE_DATE
, HP2.LAST_UPDATE_LOGIN
FROM HZ_PARTIES HP2
, IGS_PE_HZ_PARTIES IHP2
, HZ_PARTIES HP_INST2
, IGS_PE_HZ_PARTIES IHP_INST2
WHERE HP2.PARTY_ID = IHP2.PARTY_ID
AND IHP2.INST_ORG_IND = 'O'
AND IHP2.INSTITUTION_CD = IHP_INST2.OSS_ORG_UNIT_CD
AND IHP_INST2.PARTY_ID = HP_INST2.PARTY_ID GROUP BY HP2.PARTY_NUMBER
, IHP2.OU_START_DT
, IHP2.OU_END_DT
, HP2.PARTY_NAME
, IHP2.OU_ORG_STATUS
, IHP2.OU_ORG_TYPE
, IHP2.OU_MEMBER_TYPE
, IHP2.INSTITUTION_CD
, HP_INST2.PARTY_NAME
, HP2.CREATED_BY
, HP2.CREATION_DATE
, HP2.LAST_UPDATED_BY
, HP2.LAST_UPDATE_DATE
, HP2.LAST_UPDATE_LOGIN