POZ_SUPPLIER_CONTACTS

Details

Schema: FUSION
Object owner: POZ
Object type: VIEW

Columns

Name
PARTY_NAME FULL_NAME VENDOR_CONTACT_ID VENDOR_SITE_ID VENDOR_ID LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN CREATION_DATE CREATED_BY FIRST_NAME MIDDLE_NAME LAST_NAME PREFIX TITLE MAIL_STOP AREA_CODE PHONE PHONE_COUNTRY_CODE PHONE_EXTENSION REQUEST_ID PROGRAM_APPLICATION_ID PROGRAM_ID PROGRAM_UPDATE_DATE CONTACT_NAME_ALT FIRST_NAME_ALT LAST_NAME_ALT DEPARTMENT JOB_TITLE SALUTATION PERSON_NAME_SUFFIX ADMINISTRATIVE_CONTACT STATUS EMAIL_ADDRESS URL ALT_AREA_CODE ALT_PHONE FAX_AREA_CODE FAX FAX_COUNTRY_CODE FAX_EXTENSION MOBILE_COUNTRY_CODE MOBILE_AREA_CODE MOBILE INACTIVE_DATE PER_PARTY_ID RELATIONSHIP_ID PARTY_SITE_ID ORG_CONTACT_ID ORG_PARTY_SITE_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15

Name

PARTY_NAME

FULL_NAME

VENDOR_CONTACT_ID

VENDOR_SITE_ID

VENDOR_ID

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

CREATION_DATE

CREATED_BY

FIRST_NAME

MIDDLE_NAME

LAST_NAME

PREFIX

TITLE

MAIL_STOP

AREA_CODE

PHONE

PHONE_COUNTRY_CODE

PHONE_EXTENSION

REQUEST_ID

PROGRAM_APPLICATION_ID

PROGRAM_ID

PROGRAM_UPDATE_DATE

CONTACT_NAME_ALT

FIRST_NAME_ALT

LAST_NAME_ALT

DEPARTMENT

JOB_TITLE

SALUTATION

PERSON_NAME_SUFFIX

ADMINISTRATIVE_CONTACT

STATUS

EMAIL_ADDRESS

URL

ALT_AREA_CODE

ALT_PHONE

FAX_AREA_CODE

FAX

FAX_COUNTRY_CODE

FAX_EXTENSION

MOBILE_COUNTRY_CODE

MOBILE_AREA_CODE

MOBILE

INACTIVE_DATE

PER_PARTY_ID

RELATIONSHIP_ID

PARTY_SITE_ID

ORG_CONTACT_ID

ORG_PARTY_SITE_ID

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

Query

SQL_Statement
SELECT HP.PARTY_NAME, poz_util.format_name(PVC.per_party_id) FULL_NAME ,PVC.VENDOR_CONTACT_ID VENDOR_CONTACT_ID ,PVS.VENDOR_SITE_ID VENDOR_SITE_ID ,PVS.VENDOR_ID VENDOR_ID ,PVC.LAST_UPDATE_DATE LAST_UPDATE_DATE ,PVC.LAST_UPDATED_BY LAST_UPDATED_BY ,PVC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN ,PVC.CREATION_DATE CREATION_DATE ,PVC.CREATED_BY CREATED_BY ,substr(HP.PERSON_FIRST_NAME,1,15) FIRST_NAME ,substr(HP.PERSON_MIDDLE_NAME,1,15) MIDDLE_NAME ,substr(HP.PERSON_LAST_NAME,1,15) LAST_NAME ,NVL(HP. PERSON_PRE_NAME_ADJUNCT, HP.SALUTATION) PREFIX ,substr(HP.PERSON_TITLE,30) TITLE ,HPS.MAILSTOP MAIL_STOP ,(SELECT HCP4.PHONE_AREA_CODE FROM HZ_CONTACT_POINTS HCP4 WHERE HCP4.OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP4.OWNER_TABLE_ID AND HCP4.CONTACT_POINT_TYPE = 'PHONE' AND HCP4.PHONE_LINE_TYPE = 'GEN' AND HCP4.PRIMARY_FLAG = 'Y' and HCP4.STATUS='A' AND ROWNUM < 2 ) AREA_CODE ,(SELECT HCP7.PHONE_NUMBER FROM HZ_CONTACT_POINTS HCP7 WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP7.OWNER_TABLE_ID AND HCP7.CONTACT_POINT_TYPE = 'PHONE' AND HCP7.PHONE_LINE_TYPE = 'GEN' AND HCP7.PRIMARY_FLAG = 'Y' AND HCP7.STATUS='A' AND ROWNUM < 2) PHONE , (SELECT HCP1.PHONE_COUNTRY_CODE FROM HZ_CONTACT_POINTS HCP1 WHERE HCP1. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP1.OWNER_TABLE_ID AND HCP1.CONTACT_POINT_TYPE = 'PHONE' AND HCP1.PHONE_LINE_TYPE = 'GEN' AND HCP1.PRIMARY_FLAG = 'Y' and HCP1.STATUS='A' AND ROWNUM < 2) PHONE_COUNTRY_CODE ,(SELECT HCP7.PHONE_EXTENSION FROM HZ_CONTACT_POINTS HCP7 WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP7.OWNER_TABLE_ID AND HCP7.CONTACT_POINT_TYPE = 'PHONE' AND HCP7.PHONE_LINE_TYPE = 'GEN' AND HCP7.PRIMARY_FLAG = 'Y' and HCP7.STATUS='A' AND ROWNUM < 2) PHONE_EXTENSION ,PVC.REQUEST_ID REQUEST_ID ,PVC.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID ,PVC.PROGRAM_ID PROGRAM_ID ,PVC.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE ,TO_CHAR(null) CONTACT_NAME_ALT ,TO_CHAR(null) FIRST_NAME_ALT ,TO_CHAR(null) LAST_NAME_ALT ,HOC.DEPARTMENT DEPARTMENT , HOC.JOB_TITLE ,HP.SALUTATION ,HP.PERSON_NAME_SUFFIX ,( SELECT ROLE_TYPE as ADMINISTRATIVE_CONTACT FROM HZ_ORG_CONTACT_ROLES HOCR WHERE ROLE_TYPE = 'ADMINISTRATIVE_CONTACT_FOR' AND STATUS = 'A' AND HOC.ORG_CONTACT_ID = HOCR.ORG_CONTACT_ID(+)) ADMINISTRATIVE_CONTACT ,HP.status ,(SELECT HCP4.EMAIL_ADDRESS FROM HZ_CONTACT_POINTS HCP4 WHERE HCP4.OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP4.OWNER_TABLE_ID AND PVC.RELATIONSHIP_ID=HCP4.RELATIONSHIP_ID AND HCP4.CONTACT_POINT_TYPE = 'EMAIL' AND HCP4.STATUS ='A' AND HCP4.PRIMARY_FLAG = 'Y' AND ROWNUM < 2) EMAIL_ADDRESS ,(SELECT HCP7.URL FROM HZ_CONTACT_POINTS HCP7 WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP7.OWNER_TABLE_ID AND HCP7.CONTACT_POINT_TYPE = 'WEB' AND HCP7.STATUS ='A' AND HCP7.PRIMARY_FLAG = 'Y' AND ROWNUM < 2) URL ,(SELECT HCP4.PHONE_AREA_CODE FROM HZ_CONTACT_POINTS HCP4 WHERE HCP4. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP4.OWNER_TABLE_ID AND HCP4.CONTACT_POINT_TYPE = 'PHONE' AND HCP4.PHONE_LINE_TYPE = 'PHONE' AND HCP4.PRIMARY_FLAG = 'N' AND HCP4.STATUS ='A' AND ROWNUM < 2) ALT_AREA_CODE ,(SELECT HCP7.PHONE_NUMBER FROM HZ_CONTACT_POINTS HCP7 WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP7.OWNER_TABLE_ID AND HCP7.CONTACT_POINT_TYPE = 'PHONE' AND HCP7.PHONE_LINE_TYPE = 'PHONE' AND HCP7.PRIMARY_FLAG = 'N' AND HCP7.STATUS ='A' AND ROWNUM < 2) ALT_PHONE ,(SELECT HCP5.PHONE_AREA_CODE FROM HZ_CONTACT_POINTS HCP5 WHERE HCP5. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP5.OWNER_TABLE_ID AND HCP5.CONTACT_POINT_TYPE = 'PHONE' AND HCP5.PHONE_LINE_TYPE = 'FAX' AND HCP5.STATUS ='A' AND ROWNUM < 2) FAX_AREA_CODE ,(SELECT HCP6.PHONE_NUMBER FROM HZ_CONTACT_POINTS HCP6 WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP6.OWNER_TABLE_ID AND HCP6.CONTACT_POINT_TYPE = 'PHONE' AND HCP6.PHONE_LINE_TYPE = 'FAX' AND HCP6.STATUS='A' AND ROWNUM < 2) FAX ,(SELECT HCP2.PHONE_COUNTRY_CODE FROM HZ_CONTACT_POINTS HCP2 WHERE HCP2. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP2.OWNER_TABLE_ID AND HCP2.CONTACT_POINT_TYPE = 'PHONE' AND HCP2.PHONE_LINE_TYPE = 'FAX' AND HCP2.STATUS='A' AND ROWNUM < 2) FAX_COUNTRY_CODE ,(SELECT HCP6.PHONE_EXTENSION FROM HZ_CONTACT_POINTS HCP6 WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP6.OWNER_TABLE_ID AND HCP6.CONTACT_POINT_TYPE = 'PHONE' AND HCP6.PHONE_LINE_TYPE = 'FAX' AND HCP6.STATUS='A' AND ROWNUM < 2) FAX_EXTENSION ,(SELECT HCP8.PHONE_COUNTRY_CODE FROM HZ_CONTACT_POINTS HCP8 WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP8.OWNER_TABLE_ID AND HCP8.CONTACT_POINT_TYPE = 'PHONE' AND HCP8.PHONE_LINE_TYPE = 'MOBILE' AND HCP8.STATUS='A' AND ROWNUM < 2) MOBILE_COUNTRY_CODE ,(SELECT HCP8.PHONE_AREA_CODE FROM HZ_CONTACT_POINTS HCP8 WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP8.OWNER_TABLE_ID AND HCP8.CONTACT_POINT_TYPE = 'PHONE' AND HCP8.PHONE_LINE_TYPE = 'MOBILE' AND HCP8.STATUS ='A' AND ROWNUM < 2) MOBILE_AREA_CODE ,(SELECT HCP8.PHONE_NUMBER FROM HZ_CONTACT_POINTS HCP8 WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES' AND PVC.PER_PARTY_ID = HCP8.OWNER_TABLE_ID AND HCP8.CONTACT_POINT_TYPE = 'PHONE' AND HCP8.PHONE_LINE_TYPE = 'MOBILE' AND HCP8.STATUS='A' AND ROWNUM < 2) MOBILE ,nvl(PVC.inactive_date, PartyUsageAssignment.EFFECTIVE_END_DATE) INACTIVE_DATE ,PVC.PER_PARTY_ID PER_PARTY_ID ,PVC.RELATIONSHIP_ID RELATIONSHIP_ID ,PVC.PARTY_SITE_ID PARTY_SITE_ID ,PVC.ORG_CONTACT_ID ORG_CONTACT_ID ,PVC.ORG_PARTY_SITE_ID ORG_PARTY_SITE_ID ,PVC.ATTRIBUTE_CATEGORY ,PVC.ATTRIBUTE1 ,PVC.ATTRIBUTE2 ,PVC.ATTRIBUTE3 ,PVC.ATTRIBUTE4 ,PVC.ATTRIBUTE5 ,PVC.ATTRIBUTE6 ,PVC.ATTRIBUTE7 ,PVC.ATTRIBUTE8 ,PVC.ATTRIBUTE9 ,PVC.ATTRIBUTE10 ,PVC.ATTRIBUTE11 ,PVC.ATTRIBUTE12 ,PVC.ATTRIBUTE13 ,PVC.ATTRIBUTE14 ,PVC.ATTRIBUTE15 FROM POZ_SUPPLIER_CONTACTS PVC ,POZ_SUPPLIER_SITES_ALL_M PVS ,HZ_PARTIES HP ,HZ_RELATIONSHIPS HPR ,HZ_PARTY_SITES HPS ,HZ_PARTY_USG_ASSIGNMENTS PartyUsageAssignment ,HZ_ORG_CONTACTS HOC WHERE PVC.PER_PARTY_ID = HP.PARTY_ID AND PVC.ORG_PARTY_SITE_ID = HPS.PARTY_SITE_ID AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+) AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID AND HPR.STATUS = 'A' AND HPR.RELATIONSHIP_CODE = 'CONTACT_OF' AND HPR.RELATIONSHIP_TYPE = 'CONTACT' AND PVS.PARTY_SITE_ID = PVC.ORG_PARTY_SITE_ID AND (HP.PARTY_ID = PartyUsageAssignment.PARTY_ID AND PartyUsageAssignment.PARTY_USAGE_CODE = 'SUPPLIER_CONTACT' AND PartyUsageAssignment.STATUS_FLAG = 'A' AND PartyUsageAssignment.owner_table_id = HPR.relationship_id AND PartyUsageAssignment.OWNER_TABLE_NAME = 'HZ_RELATIONSHIPS')

SQL_Statement

SELECT

HP.PARTY_NAME,

poz_util.format_name(PVC.per_party_id) FULL_NAME

,PVC.VENDOR_CONTACT_ID VENDOR_CONTACT_ID

,PVS.VENDOR_SITE_ID VENDOR_SITE_ID

,PVS.VENDOR_ID VENDOR_ID

,PVC.LAST_UPDATE_DATE LAST_UPDATE_DATE

,PVC.LAST_UPDATED_BY LAST_UPDATED_BY

,PVC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN

,PVC.CREATION_DATE CREATION_DATE

,PVC.CREATED_BY CREATED_BY

,substr(HP.PERSON_FIRST_NAME,1,15) FIRST_NAME

,substr(HP.PERSON_MIDDLE_NAME,1,15) MIDDLE_NAME

,substr(HP.PERSON_LAST_NAME,1,15) LAST_NAME

,NVL(HP. PERSON_PRE_NAME_ADJUNCT, HP.SALUTATION) PREFIX

,substr(HP.PERSON_TITLE,30) TITLE

,HPS.MAILSTOP MAIL_STOP

,(SELECT HCP4.PHONE_AREA_CODE

FROM HZ_CONTACT_POINTS HCP4

WHERE HCP4.OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP4.OWNER_TABLE_ID

AND HCP4.CONTACT_POINT_TYPE = 'PHONE'

AND HCP4.PHONE_LINE_TYPE = 'GEN'

AND HCP4.PRIMARY_FLAG = 'Y' and HCP4.STATUS='A' AND ROWNUM < 2 ) AREA_CODE

,(SELECT HCP7.PHONE_NUMBER

FROM HZ_CONTACT_POINTS HCP7

WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP7.OWNER_TABLE_ID

AND HCP7.CONTACT_POINT_TYPE = 'PHONE'

AND HCP7.PHONE_LINE_TYPE = 'GEN'

AND HCP7.PRIMARY_FLAG = 'Y' AND HCP7.STATUS='A' AND ROWNUM < 2) PHONE

, (SELECT HCP1.PHONE_COUNTRY_CODE

FROM HZ_CONTACT_POINTS HCP1

WHERE HCP1. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP1.OWNER_TABLE_ID

AND HCP1.CONTACT_POINT_TYPE = 'PHONE'

AND HCP1.PHONE_LINE_TYPE = 'GEN'

AND HCP1.PRIMARY_FLAG = 'Y' and HCP1.STATUS='A' AND ROWNUM < 2) PHONE_COUNTRY_CODE

,(SELECT HCP7.PHONE_EXTENSION

FROM HZ_CONTACT_POINTS HCP7

WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP7.OWNER_TABLE_ID

AND HCP7.CONTACT_POINT_TYPE = 'PHONE'

AND HCP7.PHONE_LINE_TYPE = 'GEN'

AND HCP7.PRIMARY_FLAG = 'Y' and HCP7.STATUS='A' AND ROWNUM < 2) PHONE_EXTENSION

,PVC.REQUEST_ID REQUEST_ID

,PVC.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID

,PVC.PROGRAM_ID PROGRAM_ID

,PVC.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE

,TO_CHAR(null) CONTACT_NAME_ALT

,TO_CHAR(null) FIRST_NAME_ALT

,TO_CHAR(null) LAST_NAME_ALT

,HOC.DEPARTMENT DEPARTMENT

, HOC.JOB_TITLE

,HP.SALUTATION

,HP.PERSON_NAME_SUFFIX

,( SELECT ROLE_TYPE as ADMINISTRATIVE_CONTACT FROM HZ_ORG_CONTACT_ROLES HOCR

WHERE ROLE_TYPE = 'ADMINISTRATIVE_CONTACT_FOR' AND STATUS = 'A'

AND HOC.ORG_CONTACT_ID = HOCR.ORG_CONTACT_ID(+)) ADMINISTRATIVE_CONTACT

,HP.status

,(SELECT HCP4.EMAIL_ADDRESS

FROM HZ_CONTACT_POINTS HCP4

WHERE HCP4.OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP4.OWNER_TABLE_ID

AND PVC.RELATIONSHIP_ID=HCP4.RELATIONSHIP_ID

AND HCP4.CONTACT_POINT_TYPE = 'EMAIL'

AND HCP4.STATUS ='A'

AND HCP4.PRIMARY_FLAG = 'Y'

AND ROWNUM < 2) EMAIL_ADDRESS

,(SELECT HCP7.URL

FROM HZ_CONTACT_POINTS HCP7

WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP7.OWNER_TABLE_ID

AND HCP7.CONTACT_POINT_TYPE = 'WEB'

AND HCP7.STATUS ='A'

AND HCP7.PRIMARY_FLAG = 'Y' AND ROWNUM < 2) URL

,(SELECT HCP4.PHONE_AREA_CODE

FROM HZ_CONTACT_POINTS HCP4

WHERE HCP4. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP4.OWNER_TABLE_ID

AND HCP4.CONTACT_POINT_TYPE = 'PHONE'

AND HCP4.PHONE_LINE_TYPE = 'PHONE'

AND HCP4.PRIMARY_FLAG = 'N'

AND HCP4.STATUS ='A'

AND ROWNUM < 2) ALT_AREA_CODE

,(SELECT HCP7.PHONE_NUMBER

FROM HZ_CONTACT_POINTS HCP7

WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP7.OWNER_TABLE_ID

AND HCP7.CONTACT_POINT_TYPE = 'PHONE'

AND HCP7.PHONE_LINE_TYPE = 'PHONE'

AND HCP7.PRIMARY_FLAG = 'N'

AND HCP7.STATUS ='A'

AND ROWNUM < 2) ALT_PHONE

,(SELECT HCP5.PHONE_AREA_CODE

FROM HZ_CONTACT_POINTS HCP5

WHERE HCP5. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP5.OWNER_TABLE_ID

AND HCP5.CONTACT_POINT_TYPE = 'PHONE'

AND HCP5.PHONE_LINE_TYPE = 'FAX'

AND HCP5.STATUS ='A'

AND ROWNUM < 2) FAX_AREA_CODE

,(SELECT HCP6.PHONE_NUMBER

FROM HZ_CONTACT_POINTS HCP6

WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP6.OWNER_TABLE_ID

AND HCP6.CONTACT_POINT_TYPE = 'PHONE'

AND HCP6.PHONE_LINE_TYPE = 'FAX'

AND HCP6.STATUS='A'

AND ROWNUM < 2) FAX

,(SELECT HCP2.PHONE_COUNTRY_CODE

FROM HZ_CONTACT_POINTS HCP2

WHERE HCP2. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP2.OWNER_TABLE_ID

AND HCP2.CONTACT_POINT_TYPE = 'PHONE'

AND HCP2.PHONE_LINE_TYPE = 'FAX'

AND HCP2.STATUS='A'

AND ROWNUM < 2) FAX_COUNTRY_CODE

,(SELECT HCP6.PHONE_EXTENSION

FROM HZ_CONTACT_POINTS HCP6

WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP6.OWNER_TABLE_ID

AND HCP6.CONTACT_POINT_TYPE = 'PHONE'

AND HCP6.PHONE_LINE_TYPE = 'FAX'

AND HCP6.STATUS='A'

AND ROWNUM < 2) FAX_EXTENSION

,(SELECT HCP8.PHONE_COUNTRY_CODE

FROM HZ_CONTACT_POINTS HCP8

WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP8.OWNER_TABLE_ID

AND HCP8.CONTACT_POINT_TYPE = 'PHONE'

AND HCP8.PHONE_LINE_TYPE = 'MOBILE'

AND HCP8.STATUS='A'

AND ROWNUM < 2) MOBILE_COUNTRY_CODE

,(SELECT HCP8.PHONE_AREA_CODE

FROM HZ_CONTACT_POINTS HCP8

WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP8.OWNER_TABLE_ID

AND HCP8.CONTACT_POINT_TYPE = 'PHONE'

AND HCP8.PHONE_LINE_TYPE = 'MOBILE'

AND HCP8.STATUS ='A'

AND ROWNUM < 2) MOBILE_AREA_CODE

,(SELECT HCP8.PHONE_NUMBER

FROM HZ_CONTACT_POINTS HCP8

WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'

AND PVC.PER_PARTY_ID = HCP8.OWNER_TABLE_ID

AND HCP8.CONTACT_POINT_TYPE = 'PHONE'

AND HCP8.PHONE_LINE_TYPE = 'MOBILE'

AND HCP8.STATUS='A'

AND ROWNUM < 2) MOBILE

,nvl(PVC.inactive_date, PartyUsageAssignment.EFFECTIVE_END_DATE) INACTIVE_DATE

,PVC.PER_PARTY_ID PER_PARTY_ID

,PVC.RELATIONSHIP_ID RELATIONSHIP_ID

,PVC.PARTY_SITE_ID PARTY_SITE_ID

,PVC.ORG_CONTACT_ID ORG_CONTACT_ID

,PVC.ORG_PARTY_SITE_ID ORG_PARTY_SITE_ID

,PVC.ATTRIBUTE_CATEGORY

,PVC.ATTRIBUTE1

,PVC.ATTRIBUTE2

,PVC.ATTRIBUTE3

,PVC.ATTRIBUTE4

,PVC.ATTRIBUTE5

,PVC.ATTRIBUTE6

,PVC.ATTRIBUTE7

,PVC.ATTRIBUTE8

,PVC.ATTRIBUTE9

,PVC.ATTRIBUTE10

,PVC.ATTRIBUTE11

,PVC.ATTRIBUTE12

,PVC.ATTRIBUTE13

,PVC.ATTRIBUTE14

,PVC.ATTRIBUTE15

FROM

POZ_SUPPLIER_CONTACTS PVC

,POZ_SUPPLIER_SITES_ALL_M PVS

,HZ_PARTIES HP

,HZ_RELATIONSHIPS HPR

,HZ_PARTY_SITES HPS

,HZ_PARTY_USG_ASSIGNMENTS PartyUsageAssignment

,HZ_ORG_CONTACTS HOC

WHERE

PVC.PER_PARTY_ID = HP.PARTY_ID

AND PVC.ORG_PARTY_SITE_ID = HPS.PARTY_SITE_ID

AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)

AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID

AND HPR.STATUS = 'A'

AND HPR.RELATIONSHIP_CODE = 'CONTACT_OF'

AND HPR.RELATIONSHIP_TYPE = 'CONTACT'

AND PVS.PARTY_SITE_ID = PVC.ORG_PARTY_SITE_ID

AND (HP.PARTY_ID = PartyUsageAssignment.PARTY_ID AND PartyUsageAssignment.PARTY_USAGE_CODE = 'SUPPLIER_CONTACT' AND PartyUsageAssignment.STATUS_FLAG = 'A' AND PartyUsageAssignment.owner_table_id = HPR.relationship_id AND PartyUsageAssignment.OWNER_TABLE_NAME = 'HZ_RELATIONSHIPS')