DBA Data[Home] [Help]

VIEW: APPS.PO_VENDOR_CONTACTS

Source

View Text - Preformatted

SELECT
    PVC.VENDOR_CONTACT_ID	VENDOR_CONTACT_ID
   ,PVS.VENDOR_SITE_ID		VENDOR_SITE_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,1,30)	TITLE     --Bug9215166
   ,HPS.MAILSTOP	MAIL_STOP
   ,(SELECT HP2.PRIMARY_PHONE_AREA_CODE
     FROM hz_parties hp2
     WHERE PVC.REL_PARTY_ID =hp2.party_id
       AND HP2.primary_phone_line_type='GEN') AREA_CODE
   ,(SELECT HP2.PRIMARY_PHONE_NUMBER
     FROM hz_parties hp2
     WHERE PVC.REL_PARTY_ID =hp2.party_id
       AND HP2.primary_phone_line_type='GEN') PHONE
   ,PVC.REQUEST_ID	REQUEST_ID
   ,PVC.PROGRAM_APPLICATION_ID	PROGRAM_APPLICATION_ID
   ,PVC.PROGRAM_ID	PROGRAM_ID
   ,PVC.PROGRAM_UPDATE_DATE	PROGRAM_UPDATE_DATE
   ,HP. ORGANIZATION_NAME_PHONETIC	CONTACT_NAME_ALT
   ,HP.PERSON_FIRST_NAME_PHONETIC 	FIRST_NAME_ALT
   ,HP.PERSON_LAST_NAME_PHONETIC 	LAST_NAME_ALT
   ,HOC.DEPARTMENT	DEPARTMENT
   ,nvl((SELECT HCP8.EMAIL_ADDRESS
    FROM HZ_CONTACT_POINTS HCP8
    WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'
    AND PVC.REL_PARTY_ID         = HCP8.OWNER_TABLE_ID
    AND HCP8.CONTACT_POINT_TYPE  = 'EMAIL'
    --AND HCP8.PRIMARY_FLAG        = 'Y'
    --AND HCP8.email_address is not null
    AND STATUS                   = 'A'
    AND ROWNUM                   < 2
    ), hp2.email_address) EMAIL_ADDRESS
   ,HP2.URL	URL
   ,(SELECT HCP4.PHONE_AREA_CODE
     FROM HZ_CONTACT_POINTS HCP4
     WHERE HCP4. OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND PVC.REL_PARTY_ID = HCP4.OWNER_TABLE_ID
     AND HCP4.CONTACT_POINT_TYPE = 'PHONE'
     AND HCP4.PHONE_LINE_TYPE = 'PHONE'
     AND HCP4.PRIMARY_FLAG = 'N'
     AND ROWNUM < 2) ALT_AREA_CODE
   ,(SELECT HCP7.PHONE_NUMBER
     FROM HZ_CONTACT_POINTS HCP7
     WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND PVC.REL_PARTY_ID = HCP7.OWNER_TABLE_ID
     AND HCP7.CONTACT_POINT_TYPE = 'PHONE'
     AND HCP7.PHONE_LINE_TYPE = 'PHONE'
     AND HCP7.PRIMARY_FLAG = 'N'
     AND ROWNUM < 2) ALT_PHONE
   ,(SELECT HCP5.PHONE_AREA_CODE
     FROM HZ_CONTACT_POINTS HCP5
     WHERE HCP5. OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND PVC.REL_PARTY_ID = HCP5.OWNER_TABLE_ID
     AND HCP5.CONTACT_POINT_TYPE = 'PHONE'
     AND HCP5.PHONE_LINE_TYPE = 'FAX'
     /* AND HCP5.PRIMARY_FLAG = 'N' Commented for bug#9200951 */
     AND HCP5.STATUS='A' /* Added for bug#9200951 */
     AND ROWNUM < 2) FAX_AREA_CODE
   ,(SELECT HCP6.PHONE_NUMBER
     FROM HZ_CONTACT_POINTS HCP6
     WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND PVC.REL_PARTY_ID = HCP6.OWNER_TABLE_ID
     AND HCP6.CONTACT_POINT_TYPE = 'PHONE'
     AND HCP6.PHONE_LINE_TYPE = 'FAX'
     /* AND HCP6.PRIMARY_FLAG = 'N' Commented for bug#9200951 */
     AND HCP6.STATUS='A' /* Added for bug#9200951 */
     AND ROWNUM < 2) FAX
   ,LEAST(NVL(HPR.END_DATE, TO_DATE('12/31/4712','MM/DD/RRRR')), NVL(PVC.INACTIVE_DATE, TO_DATE('12/31/4712','MM/DD/RRRR'))) INACTIVE_DATE /* Bug 7551007 */
   ,PVC.PER_PARTY_ID		PER_PARTY_ID
   ,PVC.RELATIONSHIP_ID		RELATIONSHIP_ID
   ,PVC.REL_PARTY_ID		REL_PARTY_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
   ,PVS.VENDOR_ID
FROM
    AP_SUPPLIER_CONTACTS PVC
   ,AP_SUPPLIER_SITES_ALL PVS
   ,HZ_PARTIES HP
   ,HZ_RELATIONSHIPS HPR
   ,HZ_PARTY_SITES HPS
   ,HZ_ORG_CONTACTS HOC
   ,HZ_PARTIES HP2
   ,AP_SUPPLIERS APS
WHERE
   PVC.PER_PARTY_ID = HP.PARTY_ID
   AND PVC.REL_PARTY_ID = HP2.PARTY_ID
   AND PVC.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.DIRECTIONAL_FLAG='F'
   AND PVS.PARTY_SITE_ID  = PVC.ORG_PARTY_SITE_ID
   AND PVS.VENDOR_ID = APS.VENDOR_ID
   AND NVL( APS.VENDOR_TYPE_LOOKUP_CODE, 'DUMMY' ) <> 'EMPLOYEE'
UNION
SELECT
    PVC.VENDOR_CONTACT_ID	VENDOR_CONTACT_ID
   ,PVC.VENDOR_SITE_ID		VENDOR_SITE_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,1,30)	TITLE      --Bug9215166
   ,NULL	MAIL_STOP
   ,HP2.PRIMARY_PHONE_AREA_CODE	AREA_CODE
   ,HP2.PRIMARY_PHONE_NUMBER	PHONE
   ,PVC.REQUEST_ID	REQUEST_ID
   ,PVC.PROGRAM_APPLICATION_ID	PROGRAM_APPLICATION_ID
   ,PVC.PROGRAM_ID	PROGRAM_ID
   ,PVC.PROGRAM_UPDATE_DATE	PROGRAM_UPDATE_DATE
   ,HP. ORGANIZATION_NAME_PHONETIC	CONTACT_NAME_ALT
   ,HP.PERSON_FIRST_NAME_PHONETIC 	FIRST_NAME_ALT
   ,HP.PERSON_LAST_NAME_PHONETIC 	LAST_NAME_ALT
   ,HOC.DEPARTMENT	DEPARTMENT
   ,nvl((SELECT HCP8.EMAIL_ADDRESS
    FROM HZ_CONTACT_POINTS HCP8
    WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'
    AND PVC.REL_PARTY_ID         = HCP8.OWNER_TABLE_ID
    AND HCP8.CONTACT_POINT_TYPE  = 'EMAIL'
    --AND HCP8.PRIMARY_FLAG        = 'Y'
    --AND HCP8.email_address is not null
    AND STATUS                   = 'A'
    AND ROWNUM                   < 2
    ), hp2.email_address) EMAIL_ADDRESS
   ,HP2.URL	URL
   ,(SELECT HCP4.PHONE_AREA_CODE
     FROM HZ_CONTACT_POINTS HCP4
     WHERE HCP4. OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND PVC.REL_PARTY_ID = HCP4.OWNER_TABLE_ID
     AND HCP4.CONTACT_POINT_TYPE = 'PHONE'
     AND HCP4.PHONE_LINE_TYPE = 'PHONE'
     AND HCP4.PRIMARY_FLAG = 'N'
     AND ROWNUM < 2) ALT_AREA_CODE
   ,(SELECT HCP7.PHONE_NUMBER
     FROM HZ_CONTACT_POINTS HCP7
     WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND PVC.REL_PARTY_ID = HCP7.OWNER_TABLE_ID
     AND HCP7.CONTACT_POINT_TYPE = 'PHONE'
     AND HCP7.PHONE_LINE_TYPE = 'PHONE'
     AND HCP7.PRIMARY_FLAG = 'N'
     AND ROWNUM < 2) ALT_PHONE
   ,(SELECT HCP5.PHONE_AREA_CODE
     FROM HZ_CONTACT_POINTS HCP5
     WHERE HCP5. OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND PVC.REL_PARTY_ID = HCP5.OWNER_TABLE_ID
     AND HCP5.CONTACT_POINT_TYPE = 'PHONE'
     AND HCP5.PHONE_LINE_TYPE = 'FAX'
     /* AND HCP5.PRIMARY_FLAG = 'N' Commented for bug#9200951 */
     AND HCP5.STATUS='A' /* Added for bug#9200951 */
     AND ROWNUM < 2) FAX_AREA_CODE
   ,(SELECT HCP6.PHONE_NUMBER
     FROM HZ_CONTACT_POINTS HCP6
     WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES'
     AND PVC.REL_PARTY_ID = HCP6.OWNER_TABLE_ID
     AND HCP6.CONTACT_POINT_TYPE = 'PHONE'
     AND HCP6.PHONE_LINE_TYPE = 'FAX' 
     /* AND HCP6.PRIMARY_FLAG = 'N' Commented for bug#9200951 */
     AND HCP6.STATUS='A' /* Added for bug#9200951 */
     AND ROWNUM < 2) FAX
   ,HPR.END_DATE		INACTIVE_DATE
   ,PVC.PER_PARTY_ID		PER_PARTY_ID
   ,PVC.RELATIONSHIP_ID		RELATIONSHIP_ID
   ,PVC.REL_PARTY_ID		REL_PARTY_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
   ,APS.VENDOR_ID
FROM
    AP_SUPPLIER_CONTACTS PVC
   ,HZ_PARTIES HP
   ,HZ_RELATIONSHIPS HPR
   ,HZ_ORG_CONTACTS HOC
   ,HZ_PARTIES HP2
   ,AP_SUPPLIERS APS
WHERE
   PVC.PER_PARTY_ID = HP.PARTY_ID
   AND PVC.REL_PARTY_ID = HP2.PARTY_ID
   AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)
   AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID
   AND HPR.DIRECTIONAL_FLAG='F'
   AND PVC.ORG_PARTY_SITE_ID IS NULL
   AND PVC.VENDOR_SITE_ID IS NULL
   AND HPR.OBJECT_ID = APS.PARTY_ID
   AND HPR.RELATIONSHIP_CODE = 'CONTACT_OF'
   AND HPR.OBJECT_TYPE = 'ORGANIZATION'
   AND NVL( APS.VENDOR_TYPE_LOOKUP_CODE, 'DUMMY' ) <> 'EMPLOYEE'
View Text - HTML Formatted

SELECT PVC.VENDOR_CONTACT_ID VENDOR_CONTACT_ID
, PVS.VENDOR_SITE_ID VENDOR_SITE_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
, 1
, 30) TITLE --BUG9215166
, HPS.MAILSTOP MAIL_STOP
, (SELECT HP2.PRIMARY_PHONE_AREA_CODE
FROM HZ_PARTIES HP2
WHERE PVC.REL_PARTY_ID =HP2.PARTY_ID
AND HP2.PRIMARY_PHONE_LINE_TYPE='GEN') AREA_CODE
, (SELECT HP2.PRIMARY_PHONE_NUMBER
FROM HZ_PARTIES HP2
WHERE PVC.REL_PARTY_ID =HP2.PARTY_ID
AND HP2.PRIMARY_PHONE_LINE_TYPE='GEN') PHONE
, PVC.REQUEST_ID REQUEST_ID
, PVC.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PVC.PROGRAM_ID PROGRAM_ID
, PVC.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, HP. ORGANIZATION_NAME_PHONETIC CONTACT_NAME_ALT
, HP.PERSON_FIRST_NAME_PHONETIC FIRST_NAME_ALT
, HP.PERSON_LAST_NAME_PHONETIC LAST_NAME_ALT
, HOC.DEPARTMENT DEPARTMENT
, NVL((SELECT HCP8.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS HCP8
WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP8.OWNER_TABLE_ID
AND HCP8.CONTACT_POINT_TYPE = 'EMAIL' --AND HCP8.PRIMARY_FLAG = 'Y' --AND HCP8.EMAIL_ADDRESS IS NOT NULL
AND STATUS = 'A'
AND ROWNUM < 2 )
, HP2.EMAIL_ADDRESS) EMAIL_ADDRESS
, HP2.URL URL
, (SELECT HCP4.PHONE_AREA_CODE
FROM HZ_CONTACT_POINTS HCP4
WHERE HCP4. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP4.OWNER_TABLE_ID
AND HCP4.CONTACT_POINT_TYPE = 'PHONE'
AND HCP4.PHONE_LINE_TYPE = 'PHONE'
AND HCP4.PRIMARY_FLAG = 'N'
AND ROWNUM < 2) ALT_AREA_CODE
, (SELECT HCP7.PHONE_NUMBER
FROM HZ_CONTACT_POINTS HCP7
WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP7.OWNER_TABLE_ID
AND HCP7.CONTACT_POINT_TYPE = 'PHONE'
AND HCP7.PHONE_LINE_TYPE = 'PHONE'
AND HCP7.PRIMARY_FLAG = 'N'
AND ROWNUM < 2) ALT_PHONE
, (SELECT HCP5.PHONE_AREA_CODE
FROM HZ_CONTACT_POINTS HCP5
WHERE HCP5. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP5.OWNER_TABLE_ID
AND HCP5.CONTACT_POINT_TYPE = 'PHONE'
AND HCP5.PHONE_LINE_TYPE = 'FAX' /*
AND HCP5.PRIMARY_FLAG = 'N' COMMENTED FOR BUG#9200951 */
AND HCP5.STATUS='A' /* ADDED FOR BUG#9200951 */
AND ROWNUM < 2) FAX_AREA_CODE
, (SELECT HCP6.PHONE_NUMBER
FROM HZ_CONTACT_POINTS HCP6
WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP6.OWNER_TABLE_ID
AND HCP6.CONTACT_POINT_TYPE = 'PHONE'
AND HCP6.PHONE_LINE_TYPE = 'FAX' /*
AND HCP6.PRIMARY_FLAG = 'N' COMMENTED FOR BUG#9200951 */
AND HCP6.STATUS='A' /* ADDED FOR BUG#9200951 */
AND ROWNUM < 2) FAX
, LEAST(NVL(HPR.END_DATE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR'))
, NVL(PVC.INACTIVE_DATE
, TO_DATE('12/31/4712'
, 'MM/DD/RRRR'))) INACTIVE_DATE /* BUG 7551007 */
, PVC.PER_PARTY_ID PER_PARTY_ID
, PVC.RELATIONSHIP_ID RELATIONSHIP_ID
, PVC.REL_PARTY_ID REL_PARTY_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
, PVS.VENDOR_ID FROM AP_SUPPLIER_CONTACTS PVC
, AP_SUPPLIER_SITES_ALL PVS
, HZ_PARTIES HP
, HZ_RELATIONSHIPS HPR
, HZ_PARTY_SITES HPS
, HZ_ORG_CONTACTS HOC
, HZ_PARTIES HP2
, AP_SUPPLIERS APS WHERE PVC.PER_PARTY_ID = HP.PARTY_ID
AND PVC.REL_PARTY_ID = HP2.PARTY_ID
AND PVC.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.DIRECTIONAL_FLAG='F'
AND PVS.PARTY_SITE_ID = PVC.ORG_PARTY_SITE_ID
AND PVS.VENDOR_ID = APS.VENDOR_ID
AND NVL( APS.VENDOR_TYPE_LOOKUP_CODE
, 'DUMMY' ) <> 'EMPLOYEE' UNION SELECT PVC.VENDOR_CONTACT_ID VENDOR_CONTACT_ID
, PVC.VENDOR_SITE_ID VENDOR_SITE_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
, 1
, 30) TITLE --BUG9215166
, NULL MAIL_STOP
, HP2.PRIMARY_PHONE_AREA_CODE AREA_CODE
, HP2.PRIMARY_PHONE_NUMBER PHONE
, PVC.REQUEST_ID REQUEST_ID
, PVC.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, PVC.PROGRAM_ID PROGRAM_ID
, PVC.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, HP. ORGANIZATION_NAME_PHONETIC CONTACT_NAME_ALT
, HP.PERSON_FIRST_NAME_PHONETIC FIRST_NAME_ALT
, HP.PERSON_LAST_NAME_PHONETIC LAST_NAME_ALT
, HOC.DEPARTMENT DEPARTMENT
, NVL((SELECT HCP8.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS HCP8
WHERE HCP8. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP8.OWNER_TABLE_ID
AND HCP8.CONTACT_POINT_TYPE = 'EMAIL' --AND HCP8.PRIMARY_FLAG = 'Y' --AND HCP8.EMAIL_ADDRESS IS NOT NULL
AND STATUS = 'A'
AND ROWNUM < 2 )
, HP2.EMAIL_ADDRESS) EMAIL_ADDRESS
, HP2.URL URL
, (SELECT HCP4.PHONE_AREA_CODE
FROM HZ_CONTACT_POINTS HCP4
WHERE HCP4. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP4.OWNER_TABLE_ID
AND HCP4.CONTACT_POINT_TYPE = 'PHONE'
AND HCP4.PHONE_LINE_TYPE = 'PHONE'
AND HCP4.PRIMARY_FLAG = 'N'
AND ROWNUM < 2) ALT_AREA_CODE
, (SELECT HCP7.PHONE_NUMBER
FROM HZ_CONTACT_POINTS HCP7
WHERE HCP7. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP7.OWNER_TABLE_ID
AND HCP7.CONTACT_POINT_TYPE = 'PHONE'
AND HCP7.PHONE_LINE_TYPE = 'PHONE'
AND HCP7.PRIMARY_FLAG = 'N'
AND ROWNUM < 2) ALT_PHONE
, (SELECT HCP5.PHONE_AREA_CODE
FROM HZ_CONTACT_POINTS HCP5
WHERE HCP5. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP5.OWNER_TABLE_ID
AND HCP5.CONTACT_POINT_TYPE = 'PHONE'
AND HCP5.PHONE_LINE_TYPE = 'FAX' /*
AND HCP5.PRIMARY_FLAG = 'N' COMMENTED FOR BUG#9200951 */
AND HCP5.STATUS='A' /* ADDED FOR BUG#9200951 */
AND ROWNUM < 2) FAX_AREA_CODE
, (SELECT HCP6.PHONE_NUMBER
FROM HZ_CONTACT_POINTS HCP6
WHERE HCP6. OWNER_TABLE_NAME = 'HZ_PARTIES'
AND PVC.REL_PARTY_ID = HCP6.OWNER_TABLE_ID
AND HCP6.CONTACT_POINT_TYPE = 'PHONE'
AND HCP6.PHONE_LINE_TYPE = 'FAX' /*
AND HCP6.PRIMARY_FLAG = 'N' COMMENTED FOR BUG#9200951 */
AND HCP6.STATUS='A' /* ADDED FOR BUG#9200951 */
AND ROWNUM < 2) FAX
, HPR.END_DATE INACTIVE_DATE
, PVC.PER_PARTY_ID PER_PARTY_ID
, PVC.RELATIONSHIP_ID RELATIONSHIP_ID
, PVC.REL_PARTY_ID REL_PARTY_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
, APS.VENDOR_ID FROM AP_SUPPLIER_CONTACTS PVC
, HZ_PARTIES HP
, HZ_RELATIONSHIPS HPR
, HZ_ORG_CONTACTS HOC
, HZ_PARTIES HP2
, AP_SUPPLIERS APS WHERE PVC.PER_PARTY_ID = HP.PARTY_ID
AND PVC.REL_PARTY_ID = HP2.PARTY_ID
AND PVC.ORG_CONTACT_ID = HOC.ORG_CONTACT_ID(+)
AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID
AND HPR.DIRECTIONAL_FLAG='F'
AND PVC.ORG_PARTY_SITE_ID IS NULL
AND PVC.VENDOR_SITE_ID IS NULL
AND HPR.OBJECT_ID = APS.PARTY_ID
AND HPR.RELATIONSHIP_CODE = 'CONTACT_OF'
AND HPR.OBJECT_TYPE = 'ORGANIZATION'
AND NVL( APS.VENDOR_TYPE_LOOKUP_CODE
, 'DUMMY' ) <> 'EMPLOYEE'