[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | Gets the person details for the import process from the interface and person address tables |
| Implementation/DBA Data: | Not implemented in this database |
SELECT IP.ROWID ROW_ID
, IP.INTERFACE_ID
, IP.BATCH_ID
, IP.PERSON_ID
, IP.GIVEN_NAMES
, SUBSTR(IP.GIVEN_NAMES
, 1
, 1) GIVEN_NAME_1_CHAR
, IP.SURNAME SURNAME
, NULL SURNAME_5_CHAR
, IP.PREFERRED_GIVEN_NAME
, IP.SEX
, IP.BIRTH_DT
, IP.PREF_ALTERNATE_ID
, SI.ETHNIC_ORIGIN
, IA.INTERFACE_ADDR_ID
, UI.SITE_USE_CODE ADDR_TYPE
, REPLACE(IA.ADDR_LINE_1
, ' '
, '')
, REPLACE(IA.ADDR_LINE_2
, ' '
, '')
, REPLACE(IA.ADDR_LINE_3
, ' '
, '')
, REPLACE(IA.ADDR_LINE_4
, ' '
, '')
, IA.CITY
, IA.STATE
, IA.COUNTY
, IA.PROVINCE
, IA.COUNTRY
, IA.POSTCODE
, AI.PERSON_ID_TYPE
, AI.ALTERNATE_ID
, IP.CREATED_BY CREATED_DATE
, IP.CREATION_DATE CREATION_DATE
, IP.LAST_UPDATED_BY LAST_UPDATED_BY
, IP.LAST_UPDATE_DATE LAST_UPDATE_DATE
, IP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, IP.REQUEST_ID REQUEST_ID
, IP.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, IP.PROGRAM_ID PROGRAM_ID
, IP.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
FROM IGS_AD_INTERFACE IP
, IGS_AD_ADDR_INT IA
, IGS_AD_API_INT AI
, IGS_AD_STAT_INT SI
, IGS_AD_ADDRUSAGE_INT UI
WHERE IP.INTERFACE_ID = IA.INTERFACE_ID(+)
AND IP.INTERFACE_ID = AI.INTERFACE_ID(+)
AND IP.INTERFACE_ID = SI.INTERFACE_ID (+)
AND UI.INTERFACE_ADDR_ID(+) = IA.INTERFACE_ADDR_ID