HWR_COMBO_CONTACT

Details

Schema: FUSION
Object owner: HWR
Object type: VIEW

Columns

Name
ADDRESS_LINE_1 ADDRESS_LINE_2 ADDRESS_LINE_3 ADDRESS_LINE_4 ADDRESS BUILDING COUNTRY DERIVED_LOCALE FLOOR_NUMBER LATITUDE LONGITUDE LONG_POSTAL_CODE ONLINE_ACCOUNT ONLINE_ACCT_HOMEPAGE POSTAL_CODE REGION_1 REGION_2 REGION_3 TIMEZONE_CODE TOWN_OR_CITY URL CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE PRFL_ID SOURCE_ID FULLPHONENUMBER EMAIL EMAIL2

Name

ADDRESS_LINE_1

ADDRESS_LINE_2

ADDRESS_LINE_3

ADDRESS_LINE_4

ADDRESS

BUILDING

COUNTRY

DERIVED_LOCALE

FLOOR_NUMBER

LATITUDE

LONGITUDE

LONG_POSTAL_CODE

ONLINE_ACCOUNT

ONLINE_ACCT_HOMEPAGE

POSTAL_CODE

REGION_1

REGION_2

REGION_3

TIMEZONE_CODE

TOWN_OR_CITY

URL

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

PRFL_ID

SOURCE_ID

FULLPHONENUMBER

EMAIL

EMAIL2

Query

SQL_Statement
SELECT IUH1.ADDRESS_LINE_1, IUH1.ADDRESS_LINE_2, IUH1.ADDRESS_LINE_3, IUH1.ADDRESS_LINE_4, IUH1.ADDRESS, IUH1.BUILDING, IUH1.COUNTRY, IUH1.DERIVED_LOCALE, IUH1.FLOOR_NUMBER, IUH1.LATITUDE, IUH1.LONGITUDE, IUH1.LONG_POSTAL_CODE, IUH1.ONLINE_ACCOUNT, IUH1.ONLINE_ACCT_HOMEPAGE, IUH1.POSTAL_CODE, IUH1.REGION_1, IUH1.REGION_2, IUH1.REGION_3, IUH1.TIMEZONE_CODE, IUH1.TOWN_OR_CITY, IUH1.URL, IUH1.CREATED_BY, IUH1.CREATION_DATE, IUH1.LAST_UPDATED_BY, IUH1.LAST_UPDATE_DATE, IUH1.PRFL_ID, IUH1.SOURCE_ID, IUH1.FULLPHONENUMBER, IUH1.EMAIL, IUH1.EMAIL2 FROM ( SELECT ADDRESSDPEO.ADDRESS_LINE_1, ADDRESSDPEO.ADDRESS_LINE_2, ADDRESSDPEO.ADDRESS_LINE_3, ADDRESSDPEO.ADDRESS_LINE_4, NVL2(ADDRESSDPEO.ADDRESS_LINE_1,ADDRESSDPEO.ADDRESS_LINE_1 \|\| ' ','') \|\| NVL2(ADDRESSDPEO.ADDRESS_LINE_2,ADDRESSDPEO.ADDRESS_LINE_2 \|\| ' ','') \|\| NVL2(ADDRESSDPEO.ADDRESS_LINE_3,ADDRESSDPEO.ADDRESS_LINE_3 \|\| ' ','') \|\| NVL2(ADDRESSDPEO.ADDRESS_LINE_4,ADDRESSDPEO.ADDRESS_LINE_4 \|\| ' ','') \|\| NVL2(ADDRESSDPEO.BUILDING,ADDRESSDPEO.BUILDING \|\| ' ','') \|\| NVL2(ADDRESSDPEO.FLOOR_NUMBER,ADDRESSDPEO.FLOOR_NUMBER \|\| ' ','') \|\| NVL2(ADDRESSDPEO.TOWN_OR_CITY,ADDRESSDPEO.TOWN_OR_CITY \|\| ' ','') \|\| NVL2(ADDRESSDPEO.REGION_1,ADDRESSDPEO.REGION_1 \|\| ' ','') \|\| NVL2(ADDRESSDPEO.REGION_2,ADDRESSDPEO.REGION_2 \|\| ' ','') \|\| NVL2(ADDRESSDPEO.REGION_3,ADDRESSDPEO.REGION_3 \|\| ' ','') \|\| NVL2(ADDRESSDPEO.COUNTRY,ADDRESSDPEO.COUNTRY \|\| ' ','') \|\| NVL2(ADDRESSDPEO.POSTAL_CODE,ADDRESSDPEO.POSTAL_CODE \|\| ' ','') AS ADDRESS, ADDRESSDPEO.BUILDING, ADDRESSDPEO.COUNTRY, ADDRESSDPEO.DERIVED_LOCALE, ADDRESSDPEO.FLOOR_NUMBER, NULL AS LATITUDE, NULL AS LONGITUDE, ADDRESSDPEO.LONG_POSTAL_CODE, NULL AS ONLINE_ACCOUNT, NULL AS ONLINE_ACCT_HOMEPAGE, ADDRESSDPEO.POSTAL_CODE, ADDRESSDPEO.REGION_1, ADDRESSDPEO.REGION_2, ADDRESSDPEO.REGION_3, ADDRESSDPEO.TIMEZONE_CODE, ADDRESSDPEO.TOWN_OR_CITY, NULL AS URL, PERSONADDRESSUSAGEDPEO.ADDRESS_ID, ADDRESSDPEO.ADDRESS_ID AS ADDRESSDPEODDRESS_ID, P.CREATED_BY, P.CREATION_DATE, P.LAST_UPDATED_BY, P.LAST_UPDATE_DATE, P.PRFL_ID, P.SOURCE_ID, P.FULLPHONENUMBER, P.EMAIL, P.EMAIL2 FROM ( SELECT DISTINCT PROFILEB_1.CREATED_BY, PROFILEB_1.CREATION_DATE, PROFILEB_1.LAST_UPDATED_BY, PROFILEB_1.LAST_UPDATE_DATE, PROFILEB_1.LAST_UPDATE_LOGIN, PROFILEB_1.PRFL_ID, PROFILEB_1.SOURCE_ID, NVL2(PHONEPEO.AREA_CODE,PHONEPEO.AREA_CODE \|\| ' ','') \|\| PHONEPEO.PHONE_NUMBER AS FULLPHONENUMBER, PROFILEB_1.EMAIL, HWR_CONTACT_INFO_VL.EMAIL AS EMAIL2 FROM HWR_SRC_SOURCE_B SRCB INNER JOIN HWR_SRC_SRCTYPE_B STB ON SRCB.SOURCE_TYPE_ID = STB.SOURCE_TYPE_ID INNER JOIN HWR_PER_PROFILE_B PROFILEB_1 ON SRCB.SOURCE_ID = PROFILEB_1.SOURCE_ID INNER JOIN PER_PHONES PHONEPEO ON PHONEPEO.PERSON_ID = PROFILEB_1.PRFL_ID INNER JOIN HWR_CONTACT_INFO_VL ON HWR_CONTACT_INFO_VL.PRFL_ID = PROFILEB_1.PRFL_ID WHERE PROFILEB_1.IS_ACTIVE_FLAG = 'Y' AND STB.SOURCE_TYPE_KEY = 'Fusion' ) P, PER_ALL_ASSIGNMENTS_M ASSIGNMENTDPEO, PER_ADDRESSES_F ADDRESSDPEO, PER_PERSON_ADDR_USAGES_F PERSONADDRESSUSAGEDPEO, PER_ASSIGNMENT_SUPERVISORS_F ASSIGNMENTSUPERVISORDPEO WHERE ASSIGNMENTDPEO.PERSON_ID (+) = TO_NUMBER(P.PRFL_ID) AND ASSIGNMENTDPEO.PRIMARY_FLAG = 'Y' AND ASSIGNMENTDPEO.ASSIGNMENT_TYPE IN ( 'E', 'C', 'N' ) AND PERSONADDRESSUSAGEDPEO.PERSON_ID = ASSIGNMENTDPEO.PERSON_ID AND ASSIGNMENTSUPERVISORDPEO.ASSIGNMENT_ID (+) = ASSIGNMENTDPEO.ASSIGNMENT_ID AND ASSIGNMENTSUPERVISORDPEO.PRIMARY_FLAG (+) = 'Y' AND ASSIGNMENTSUPERVISORDPEO.MANAGER_TYPE (+) = 'LINE_MANAGER' AND ( ASSIGNMENTSUPERVISORDPEO.ASSIGNMENT_ID IS NOT NULL OR ASSIGNMENTDPEO.PERSON_ID IN ( SELECT DISTINCT INNERASSIGNSUPERVISORPEO.MANAGER_ID FROM PER_ASSIGNMENT_SUPERVISORS_F INNERASSIGNSUPERVISORPEO ) ) ) IUH1 UNION ALL SELECT IUH2.ADDRESS_LINE_1, IUH2.ADDRESS_LINE_2, IUH2.ADDRESS_LINE_3, IUH2.ADDRESS_LINE_4, IUH2.ADDRESS, IUH2.BUILDING, IUH2.COUNTRY, IUH2.DERIVED_LOCALE, IUH2.FLOOR_NUMBER, IUH2.LATITUDE, IUH2.LONGITUDE, IUH2.LONG_POSTAL_CODE, IUH2.ONLINE_ACCOUNT, IUH2.ONLINE_ACCT_HOMEPAGE, IUH2.POSTAL_CODE, IUH2.REGION_1, IUH2.REGION_2, IUH2.REGION_3, IUH2.TIMEZONE_CODE, IUH2.TOWN_OR_CITY, IUH2.URL, IUH2.CREATED_BY, IUH2.CREATION_DATE, IUH2.LAST_UPDATED_BY, IUH2.LAST_UPDATE_DATE, IUH2.PRFL_ID, IUH2.SOURCE_ID, IUH2.FULLPHONENUMBER, IUH2.EMAIL, IUH2.EMAIL2 FROM ( SELECT CONTACTINFO.ADDRESS_LINE_1, CONTACTINFO.ADDRESS_LINE_2, CONTACTINFO.ADDRESS_LINE_3, CONTACTINFO.ADDRESS_LINE_4, CONTACTINFO.ADDRESS, CONTACTINFO.BUILDING, CONTACTINFO.CONTACT_INFO_ID, CONTACTINFO.COUNTRY, CONTACTINFO.DERIVED_LOCALE, CONTACTINFO.FLOOR_NUMBER, CONTACTINFO.HASHING_TYPE, CONTACTINFO.LAST_UPDATE_LOGIN, CONTACTINFO.LATITUDE, CONTACTINFO.LONGITUDE, CONTACTINFO.LONG_POSTAL_CODE, CONTACTINFO.ONLINE_ACCOUNT, CONTACTINFO.ONLINE_ACCT_HOMEPAGE, CONTACTINFO.POSTAL_CODE, CONTACTINFO.REGION_1, CONTACTINFO.REGION_2, CONTACTINFO.REGION_3, CONTACTINFO.TIMEZONE_CODE, CONTACTINFO.TOWN_OR_CITY, CONTACTINFO.URL, CONTACTINFO.CREATED_BY, CONTACTINFO.CREATION_DATE, CONTACTINFO.LAST_UPDATED_BY, CONTACTINFO.LAST_UPDATE_DATE, CONTACTINFO.PRFL_ID, CONTACTINFO.SOURCE_ID, CONTACTINFO.PHONE AS FULLPHONENUMBER, NULL AS EMAIL, CONTACTINFO.EMAIL AS EMAIL2 FROM HWR_CONTACT_INFO_VL CONTACTINFO, HWR_SRC_SOURCE_B SRCB, HWR_SRC_SRCTYPE_B SRCTYPETL WHERE CONTACTINFO.SOURCE_ID = SRCB.SOURCE_ID AND SRCB.SOURCE_TYPE_ID = SRCTYPETL.SOURCE_TYPE_ID AND SRCTYPETL.SOURCE_TYPE_KEY != 'Fusion' ) IUH2

SQL_Statement

SELECT

IUH1.ADDRESS_LINE_1,

IUH1.ADDRESS_LINE_2,

IUH1.ADDRESS_LINE_3,

IUH1.ADDRESS_LINE_4,

IUH1.ADDRESS,

IUH1.BUILDING,

IUH1.COUNTRY,

IUH1.DERIVED_LOCALE,

IUH1.FLOOR_NUMBER,

IUH1.LATITUDE,

IUH1.LONGITUDE,

IUH1.LONG_POSTAL_CODE,

IUH1.ONLINE_ACCOUNT,

IUH1.ONLINE_ACCT_HOMEPAGE,

IUH1.POSTAL_CODE,

IUH1.REGION_1,

IUH1.REGION_2,

IUH1.REGION_3,

IUH1.TIMEZONE_CODE,

IUH1.TOWN_OR_CITY,

IUH1.URL,

IUH1.CREATED_BY,

IUH1.CREATION_DATE,

IUH1.LAST_UPDATED_BY,

IUH1.LAST_UPDATE_DATE,

IUH1.PRFL_ID,

IUH1.SOURCE_ID,

IUH1.FULLPHONENUMBER,

IUH1.EMAIL,

IUH1.EMAIL2

FROM

(

SELECT

ADDRESSDPEO.ADDRESS_LINE_1,

ADDRESSDPEO.ADDRESS_LINE_2,

ADDRESSDPEO.ADDRESS_LINE_3,

ADDRESSDPEO.ADDRESS_LINE_4,

NVL2(ADDRESSDPEO.ADDRESS_LINE_1,ADDRESSDPEO.ADDRESS_LINE_1

|| ' ','')

|| NVL2(ADDRESSDPEO.ADDRESS_LINE_2,ADDRESSDPEO.ADDRESS_LINE_2

|| ' ','')

|| NVL2(ADDRESSDPEO.ADDRESS_LINE_3,ADDRESSDPEO.ADDRESS_LINE_3

|| ' ','')

|| NVL2(ADDRESSDPEO.ADDRESS_LINE_4,ADDRESSDPEO.ADDRESS_LINE_4

|| ' ','')

|| NVL2(ADDRESSDPEO.BUILDING,ADDRESSDPEO.BUILDING

|| ' ','')

|| NVL2(ADDRESSDPEO.FLOOR_NUMBER,ADDRESSDPEO.FLOOR_NUMBER

|| ' ','')

|| NVL2(ADDRESSDPEO.TOWN_OR_CITY,ADDRESSDPEO.TOWN_OR_CITY

|| ' ','')

|| NVL2(ADDRESSDPEO.REGION_1,ADDRESSDPEO.REGION_1

|| ' ','')

|| NVL2(ADDRESSDPEO.REGION_2,ADDRESSDPEO.REGION_2

|| ' ','')

|| NVL2(ADDRESSDPEO.REGION_3,ADDRESSDPEO.REGION_3

|| ' ','')

|| NVL2(ADDRESSDPEO.COUNTRY,ADDRESSDPEO.COUNTRY

|| ' ','')

|| NVL2(ADDRESSDPEO.POSTAL_CODE,ADDRESSDPEO.POSTAL_CODE

|| ' ','') AS ADDRESS,

ADDRESSDPEO.BUILDING,

ADDRESSDPEO.COUNTRY,

ADDRESSDPEO.DERIVED_LOCALE,

ADDRESSDPEO.FLOOR_NUMBER,

NULL AS LATITUDE,

NULL AS LONGITUDE,

ADDRESSDPEO.LONG_POSTAL_CODE,

NULL AS ONLINE_ACCOUNT,

NULL AS ONLINE_ACCT_HOMEPAGE,

ADDRESSDPEO.POSTAL_CODE,

ADDRESSDPEO.REGION_1,

ADDRESSDPEO.REGION_2,

ADDRESSDPEO.REGION_3,

ADDRESSDPEO.TIMEZONE_CODE,

ADDRESSDPEO.TOWN_OR_CITY,

NULL AS URL,

PERSONADDRESSUSAGEDPEO.ADDRESS_ID,

ADDRESSDPEO.ADDRESS_ID AS ADDRESSDPEODDRESS_ID,

P.CREATED_BY,

P.CREATION_DATE,

P.LAST_UPDATED_BY,

P.LAST_UPDATE_DATE,

P.PRFL_ID,

P.SOURCE_ID,

P.FULLPHONENUMBER,

P.EMAIL,

P.EMAIL2

FROM

(

SELECT DISTINCT

PROFILEB_1.CREATED_BY,

PROFILEB_1.CREATION_DATE,

PROFILEB_1.LAST_UPDATED_BY,

PROFILEB_1.LAST_UPDATE_DATE,

PROFILEB_1.LAST_UPDATE_LOGIN,

PROFILEB_1.PRFL_ID,

PROFILEB_1.SOURCE_ID,

NVL2(PHONEPEO.AREA_CODE,PHONEPEO.AREA_CODE

|| ' ','')

|| PHONEPEO.PHONE_NUMBER AS FULLPHONENUMBER,

PROFILEB_1.EMAIL,

HWR_CONTACT_INFO_VL.EMAIL AS EMAIL2

FROM

HWR_SRC_SOURCE_B SRCB

INNER JOIN

HWR_SRC_SRCTYPE_B STB

ON

SRCB.SOURCE_TYPE_ID = STB.SOURCE_TYPE_ID

INNER JOIN

HWR_PER_PROFILE_B PROFILEB_1

ON

SRCB.SOURCE_ID = PROFILEB_1.SOURCE_ID

INNER JOIN

PER_PHONES PHONEPEO

ON

PHONEPEO.PERSON_ID = PROFILEB_1.PRFL_ID

INNER JOIN

HWR_CONTACT_INFO_VL

ON

HWR_CONTACT_INFO_VL.PRFL_ID = PROFILEB_1.PRFL_ID

WHERE

PROFILEB_1.IS_ACTIVE_FLAG = 'Y'

AND

STB.SOURCE_TYPE_KEY = 'Fusion'

) P,

PER_ALL_ASSIGNMENTS_M ASSIGNMENTDPEO,

PER_ADDRESSES_F ADDRESSDPEO,

PER_PERSON_ADDR_USAGES_F PERSONADDRESSUSAGEDPEO,

PER_ASSIGNMENT_SUPERVISORS_F ASSIGNMENTSUPERVISORDPEO

WHERE

ASSIGNMENTDPEO.PERSON_ID (+) = TO_NUMBER(P.PRFL_ID)

AND ASSIGNMENTDPEO.PRIMARY_FLAG = 'Y'

AND ASSIGNMENTDPEO.ASSIGNMENT_TYPE IN (

'E',

'C',

'N'

)

AND

PERSONADDRESSUSAGEDPEO.PERSON_ID = ASSIGNMENTDPEO.PERSON_ID

AND

ASSIGNMENTSUPERVISORDPEO.ASSIGNMENT_ID (+) = ASSIGNMENTDPEO.ASSIGNMENT_ID

AND

ASSIGNMENTSUPERVISORDPEO.PRIMARY_FLAG (+) = 'Y'

AND

ASSIGNMENTSUPERVISORDPEO.MANAGER_TYPE (+) = 'LINE_MANAGER'

AND

(

ASSIGNMENTSUPERVISORDPEO.ASSIGNMENT_ID IS NOT NULL

OR

ASSIGNMENTDPEO.PERSON_ID IN (

SELECT DISTINCT

INNERASSIGNSUPERVISORPEO.MANAGER_ID

FROM

PER_ASSIGNMENT_SUPERVISORS_F INNERASSIGNSUPERVISORPEO

)

) IUH1

UNION ALL

SELECT

IUH2.ADDRESS_LINE_1,

IUH2.ADDRESS_LINE_2,

IUH2.ADDRESS_LINE_3,

IUH2.ADDRESS_LINE_4,

IUH2.ADDRESS,

IUH2.BUILDING,

IUH2.COUNTRY,

IUH2.DERIVED_LOCALE,

IUH2.FLOOR_NUMBER,

IUH2.LATITUDE,

IUH2.LONGITUDE,

IUH2.LONG_POSTAL_CODE,

IUH2.ONLINE_ACCOUNT,

IUH2.ONLINE_ACCT_HOMEPAGE,

IUH2.POSTAL_CODE,

IUH2.REGION_1,

IUH2.REGION_2,

IUH2.REGION_3,

IUH2.TIMEZONE_CODE,

IUH2.TOWN_OR_CITY,

IUH2.URL,

IUH2.CREATED_BY,

IUH2.CREATION_DATE,

IUH2.LAST_UPDATED_BY,

IUH2.LAST_UPDATE_DATE,

IUH2.PRFL_ID,

IUH2.SOURCE_ID,

IUH2.FULLPHONENUMBER,

IUH2.EMAIL,

IUH2.EMAIL2

FROM

(

SELECT

CONTACTINFO.ADDRESS_LINE_1,

CONTACTINFO.ADDRESS_LINE_2,

CONTACTINFO.ADDRESS_LINE_3,

CONTACTINFO.ADDRESS_LINE_4,

CONTACTINFO.ADDRESS,

CONTACTINFO.BUILDING,

CONTACTINFO.CONTACT_INFO_ID,

CONTACTINFO.COUNTRY,

CONTACTINFO.DERIVED_LOCALE,

CONTACTINFO.FLOOR_NUMBER,

CONTACTINFO.HASHING_TYPE,

CONTACTINFO.LAST_UPDATE_LOGIN,

CONTACTINFO.LATITUDE,

CONTACTINFO.LONGITUDE,

CONTACTINFO.LONG_POSTAL_CODE,

CONTACTINFO.ONLINE_ACCOUNT,

CONTACTINFO.ONLINE_ACCT_HOMEPAGE,

CONTACTINFO.POSTAL_CODE,

CONTACTINFO.REGION_1,

CONTACTINFO.REGION_2,

CONTACTINFO.REGION_3,

CONTACTINFO.TIMEZONE_CODE,

CONTACTINFO.TOWN_OR_CITY,

CONTACTINFO.URL,

CONTACTINFO.CREATED_BY,

CONTACTINFO.CREATION_DATE,

CONTACTINFO.LAST_UPDATED_BY,

CONTACTINFO.LAST_UPDATE_DATE,

CONTACTINFO.PRFL_ID,

CONTACTINFO.SOURCE_ID,

CONTACTINFO.PHONE AS FULLPHONENUMBER,

NULL AS EMAIL,

CONTACTINFO.EMAIL AS EMAIL2

FROM

HWR_CONTACT_INFO_VL CONTACTINFO,

HWR_SRC_SOURCE_B SRCB,

HWR_SRC_SRCTYPE_B SRCTYPETL

WHERE

CONTACTINFO.SOURCE_ID = SRCB.SOURCE_ID

AND

SRCB.SOURCE_TYPE_ID = SRCTYPETL.SOURCE_TYPE_ID

AND

SRCTYPETL.SOURCE_TYPE_KEY != 'Fusion'

) IUH2