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
, 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
, 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 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
FROM
POZ_SUPPLIER_CONTACTS PVC
, HZ_PARTY_SITES HPS
, POZ_SUPPLIER_SITES_ALL_M PVS
, HZ_PARTY_USG_ASSIGNMENTS PartyUsageAssignment
, HZ_PARTIES HP
, HZ_RELATIONSHIPS hr
, 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 = hr.RELATIONSHIP_ID
AND hr.STATUS = 'A'
AND hr.DIRECTIONAL_FLAG ='F'
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 = hr.relationship_id
AND PartyUsageAssignment.OWNER_TABLE_NAME = 'HZ_RELATIONSHIPS'
)