POZ_ALL_SUPPLIER_CONTACTS

Details

Schema: FUSION
Object owner: POZ
Object type: VIEW

Columns

Name
SUP_PARTY_ID SUPPLIER_NAME PER_PARTY_ID PERSON_TITLE NAME PERSON_FIRST_NAME PERSON_MIDDLE_NAME PERSON_LAST_NAME SALUTATION LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER INACTIVE_DATE STATUS RELATIONSHIP_ID SUBJECT_ID PHONE_CONTACT_POINT_ID PHONE_COUNTRY_CODE PHONE_AREA_CODE PHONE_NUMBER PHONE_EXTENSION MOBILE_CONTACT_POINT_ID MOBILE_COUNTRY_CODE MOBILE_AREA_CODE MOBILE_NUMBER FAX_CONTACT_POINT_ID FAX_COUNTRY_CODE FAX_AREA_CODE FAX_NUMBER EMAIL_CONTACT_POINT_ID EMAIL_ADDRESS ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_NUMBER6 ATTRIBUTE_NUMBER7 ATTRIBUTE_NUMBER8 ATTRIBUTE_NUMBER9 ATTRIBUTE_NUMBER10 ATTRIBUTE_NUMBER11 ATTRIBUTE_NUMBER12 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 ATTRIBUTE_DATE6 ATTRIBUTE_DATE7 ATTRIBUTE_DATE8 ATTRIBUTE_DATE9 ATTRIBUTE_DATE10 ATTRIBUTE_DATE11 ATTRIBUTE_DATE12

Name

SUP_PARTY_ID

SUPPLIER_NAME

PER_PARTY_ID

PERSON_TITLE

NAME

PERSON_FIRST_NAME

PERSON_MIDDLE_NAME

PERSON_LAST_NAME

SALUTATION

LAST_UPDATE_DATE

LAST_UPDATED_BY

CREATION_DATE

CREATED_BY

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

INACTIVE_DATE

STATUS

RELATIONSHIP_ID

SUBJECT_ID

PHONE_CONTACT_POINT_ID

PHONE_COUNTRY_CODE

PHONE_AREA_CODE

PHONE_NUMBER

PHONE_EXTENSION

MOBILE_CONTACT_POINT_ID

MOBILE_COUNTRY_CODE

MOBILE_AREA_CODE

MOBILE_NUMBER

FAX_CONTACT_POINT_ID

FAX_COUNTRY_CODE

FAX_AREA_CODE

FAX_NUMBER

EMAIL_CONTACT_POINT_ID

EMAIL_ADDRESS

ATTRIBUTE_CATEGORY

ATTRIBUTE1

ATTRIBUTE2

ATTRIBUTE3

ATTRIBUTE4

ATTRIBUTE5

ATTRIBUTE6

ATTRIBUTE7

ATTRIBUTE8

ATTRIBUTE9

ATTRIBUTE10

ATTRIBUTE11

ATTRIBUTE12

ATTRIBUTE13

ATTRIBUTE14

ATTRIBUTE15

ATTRIBUTE16

ATTRIBUTE17

ATTRIBUTE18

ATTRIBUTE19

ATTRIBUTE20

ATTRIBUTE21

ATTRIBUTE22

ATTRIBUTE23

ATTRIBUTE24

ATTRIBUTE25

ATTRIBUTE26

ATTRIBUTE27

ATTRIBUTE28

ATTRIBUTE29

ATTRIBUTE30

ATTRIBUTE_NUMBER1

ATTRIBUTE_NUMBER2

ATTRIBUTE_NUMBER3

ATTRIBUTE_NUMBER4

ATTRIBUTE_NUMBER5

ATTRIBUTE_NUMBER6

ATTRIBUTE_NUMBER7

ATTRIBUTE_NUMBER8

ATTRIBUTE_NUMBER9

ATTRIBUTE_NUMBER10

ATTRIBUTE_NUMBER11

ATTRIBUTE_NUMBER12

ATTRIBUTE_DATE1

ATTRIBUTE_DATE2

ATTRIBUTE_DATE3

ATTRIBUTE_DATE4

ATTRIBUTE_DATE5

ATTRIBUTE_DATE6

ATTRIBUTE_DATE7

ATTRIBUTE_DATE8

ATTRIBUTE_DATE9

ATTRIBUTE_DATE10

ATTRIBUTE_DATE11

ATTRIBUTE_DATE12

Query

SQL_Statement
SELECT Supp_Party.party_id SUP_PARTY_ID, Supp_Party.party_name SUPPLIER_NAME, Per_Parties.party_id PER_PARTY_ID, Per_Parties.person_title, Per_Parties.party_name NAME, Per_Parties.person_first_name, Per_Parties.person_middle_name, Per_Parties.person_last_name, Per_Parties.PERSON_PRE_NAME_ADJUNCT AS SALUTATION, Per_Parties.last_update_date, Per_Parties.last_updated_by, Per_Parties.creation_date, Per_Parties.created_by, Per_Parties.last_update_login, Per_Parties.object_version_number, ( CASE WHEN TRUNC(PartyUsageAssignment.EFFECTIVE_END_DATE) = TRUNC(to_date('31-12-4712','DD-MM-YYYY') ) THEN NULL ELSE PartyUsageAssignment.EFFECTIVE_END_DATE END) AS INACTIVE_DATE, StatusLookup.displayed_field AS STATUS, Rel.RELATIONSHIP_ID, Rel.SUBJECT_ID, Phone.contact_point_id phone_contact_point_id, Phone.phone_country_code, Phone.phone_area_code, Phone.phone_number, Phone.phone_extension, Mobile.contact_point_id mobile_contact_point_id, Mobile.phone_country_code as mobile_country_code, Mobile.phone_area_code as mobile_area_code, Mobile.phone_number as mobile_number, Fax.contact_point_id fax_contact_point_id, Fax.phone_country_code as fax_country_code, Fax.phone_area_code as fax_area_code, Fax.phone_number as fax_number, Email.contact_point_id email_contact_point_id, Email.email_address, Per_Profiles.attribute_category, Per_Profiles.attribute1, Per_Profiles.attribute2, Per_Profiles.attribute3, Per_Profiles.attribute4, Per_Profiles.attribute5, Per_Profiles.attribute6, Per_Profiles.attribute7, Per_Profiles.attribute8, Per_Profiles.attribute9, Per_Profiles.attribute10, Per_Profiles.attribute11, Per_Profiles.attribute12, Per_Profiles.attribute13, Per_Profiles.attribute14, Per_Profiles.attribute15, Per_Profiles.attribute16, Per_Profiles.attribute17, Per_Profiles.attribute18, Per_Profiles.attribute19, Per_Profiles.attribute20, Per_Profiles.attribute21, Per_Profiles.attribute22, Per_Profiles.attribute23, Per_Profiles.attribute24, Per_Profiles.attribute25, Per_Profiles.attribute26, Per_Profiles.attribute27, Per_Profiles.attribute28, Per_Profiles.attribute29, Per_Profiles.attribute30, Per_Profiles.attribute_number1, Per_Profiles.attribute_number2, Per_Profiles.attribute_number3, Per_Profiles.attribute_number4, Per_Profiles.attribute_number5, Per_Profiles.attribute_number6, Per_Profiles.attribute_number7, Per_Profiles.attribute_number8, Per_Profiles.attribute_number9, Per_Profiles.attribute_number10, Per_Profiles.attribute_number11, Per_Profiles.attribute_number12, Per_Profiles.attribute_Date1, Per_Profiles.attribute_Date2, Per_Profiles.attribute_Date3, Per_Profiles.attribute_Date4, Per_Profiles.attribute_Date5, Per_Profiles.attribute_Date6, Per_Profiles.attribute_Date7, Per_Profiles.attribute_Date8, Per_Profiles.attribute_Date9, Per_Profiles.attribute_Date10, Per_Profiles.attribute_Date11, Per_Profiles.attribute_Date12 FROM Poz_Suppliers Supp, Hz_Parties Supp_Party, Hz_Parties Per_Parties, Hz_Relationships Rel, Poz_Lookup_Codes StatusLookup, Hz_Contact_Points Phone, Hz_Contact_Points Fax, Hz_Contact_Points Email, HZ_PARTY_USG_ASSIGNMENTS PartyUsageAssignment, Hz_Contact_points Mobile, Hz_Person_Profiles Per_Profiles WHERE Supp_Party.Party_Id = Supp.Party_Id AND Per_Parties.Status = 'A' AND Per_Parties.Party_Type ='PERSON' AND Supp_Party.Party_Type ='ORGANIZATION' AND (Rel.Subject_Id = Per_Parties.Party_Id AND Rel.Object_Id = Supp_Party.Party_Id AND Rel.Relationship_Type = 'CONTACT' AND Rel.relationship_code = 'CONTACT_OF' AND Rel.SUBJECT_TYPE = 'PERSON' AND Rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND Rel.OBJECT_TYPE = 'ORGANIZATION' AND Rel.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND Rel.STATUS = 'A') AND StatusLookup.lookup_type(+) = 'POZ_STATUS' AND (CASE WHEN sysdate >= NVL(PartyUsageAssignment.EFFECTIVE_START_DATE, sysdate-1) AND sysdate < NVL(PartyUsageAssignment.EFFECTIVE_END_DATE, sysdate +1) THEN 'ACTIVE' ELSE 'INACTIVE' END) = StatusLookup.lookup_code(+) AND (Phone.Owner_Table_id(+) = Rel.Subject_id AND Phone.Relationship_id(+) = Rel.Relationship_id AND Phone.Owner_Table_Name(+) = 'HZ_PARTIES' AND Phone.Contact_Point_Type(+) = 'PHONE' AND Phone.Phone_Line_Type(+) = 'GEN' AND Phone.Status(+) = 'A' AND Phone.Primary_Flag(+) = 'Y') AND (Fax.Owner_Table_id(+) = Rel.Subject_id AND Fax.Relationship_id(+) = Rel.Relationship_id AND Fax.Owner_Table_Name(+) = 'HZ_PARTIES' AND Fax.Contact_Point_Type(+) = 'PHONE' AND Fax.Phone_Line_Type(+) = 'FAX' AND Fax.Status(+) = 'A') AND (Mobile.Owner_Table_id(+) = Rel.Subject_id AND Mobile.Relationship_id(+) = Rel.Relationship_id AND Mobile.Owner_Table_Name(+) = 'HZ_PARTIES' AND Mobile.Contact_Point_Type(+) = 'PHONE' AND Mobile.Phone_Line_Type(+) = 'MOBILE' AND Mobile.Status(+) = 'A') AND (Per_Parties.PARTY_ID = PartyUsageAssignment.PARTY_ID AND PartyUsageAssignment.PARTY_USAGE_CODE = 'SUPPLIER_CONTACT' AND PartyUsageAssignment.STATUS_FLAG = 'A' AND PartyUsageAssignment.OWNER_TABLE_ID = Rel.RELATIONSHIP_ID AND PartyUsageAssignment.OWNER_TABLE_NAME = 'HZ_RELATIONSHIPS') AND (Email.Owner_Table_id(+) = Rel.Subject_id AND Email.Relationship_id(+) = Rel.Relationship_id AND Email.Owner_Table_Name(+) = 'HZ_PARTIES' AND Email.Contact_Point_Type(+) = 'EMAIL' AND Email.Status(+) = 'A' AND Email.Primary_Flag(+) = 'Y') and Rel.Subject_Id = Per_Profiles.Party_Id

SQL_Statement

SELECT

Supp_Party.party_id SUP_PARTY_ID,

Supp_Party.party_name SUPPLIER_NAME,

Per_Parties.party_id PER_PARTY_ID,

Per_Parties.person_title,

Per_Parties.party_name NAME,

Per_Parties.person_first_name,

Per_Parties.person_middle_name,

Per_Parties.person_last_name,

Per_Parties.PERSON_PRE_NAME_ADJUNCT AS SALUTATION,

Per_Parties.last_update_date,

Per_Parties.last_updated_by,

Per_Parties.creation_date,

Per_Parties.created_by,

Per_Parties.last_update_login,

Per_Parties.object_version_number,

(

CASE

WHEN TRUNC(PartyUsageAssignment.EFFECTIVE_END_DATE) = TRUNC(to_date('31-12-4712','DD-MM-YYYY') )

THEN NULL

ELSE PartyUsageAssignment.EFFECTIVE_END_DATE

END) AS INACTIVE_DATE,

StatusLookup.displayed_field AS STATUS,

Rel.RELATIONSHIP_ID,

Rel.SUBJECT_ID,

Phone.contact_point_id phone_contact_point_id,

Phone.phone_country_code,

Phone.phone_area_code,

Phone.phone_number,

Phone.phone_extension,

Mobile.contact_point_id mobile_contact_point_id,

Mobile.phone_country_code as mobile_country_code,

Mobile.phone_area_code as mobile_area_code,

Mobile.phone_number as mobile_number,

Fax.contact_point_id fax_contact_point_id,

Fax.phone_country_code as fax_country_code,

Fax.phone_area_code as fax_area_code,

Fax.phone_number as fax_number,

Email.contact_point_id email_contact_point_id,

Email.email_address,

Per_Profiles.attribute_category,

Per_Profiles.attribute1,

Per_Profiles.attribute2,

Per_Profiles.attribute3,

Per_Profiles.attribute4,

Per_Profiles.attribute5,

Per_Profiles.attribute6,

Per_Profiles.attribute7,

Per_Profiles.attribute8,

Per_Profiles.attribute9,

Per_Profiles.attribute10,

Per_Profiles.attribute11,

Per_Profiles.attribute12,

Per_Profiles.attribute13,

Per_Profiles.attribute14,

Per_Profiles.attribute15,

Per_Profiles.attribute16,

Per_Profiles.attribute17,

Per_Profiles.attribute18,

Per_Profiles.attribute19,

Per_Profiles.attribute20,

Per_Profiles.attribute21,

Per_Profiles.attribute22,

Per_Profiles.attribute23,

Per_Profiles.attribute24,

Per_Profiles.attribute25,

Per_Profiles.attribute26,

Per_Profiles.attribute27,

Per_Profiles.attribute28,

Per_Profiles.attribute29,

Per_Profiles.attribute30,

Per_Profiles.attribute_number1,

Per_Profiles.attribute_number2,

Per_Profiles.attribute_number3,

Per_Profiles.attribute_number4,

Per_Profiles.attribute_number5,

Per_Profiles.attribute_number6,

Per_Profiles.attribute_number7,

Per_Profiles.attribute_number8,

Per_Profiles.attribute_number9,

Per_Profiles.attribute_number10,

Per_Profiles.attribute_number11,

Per_Profiles.attribute_number12,

Per_Profiles.attribute_Date1,

Per_Profiles.attribute_Date2,

Per_Profiles.attribute_Date3,

Per_Profiles.attribute_Date4,

Per_Profiles.attribute_Date5,

Per_Profiles.attribute_Date6,

Per_Profiles.attribute_Date7,

Per_Profiles.attribute_Date8,

Per_Profiles.attribute_Date9,

Per_Profiles.attribute_Date10,

Per_Profiles.attribute_Date11,

Per_Profiles.attribute_Date12

FROM

Poz_Suppliers Supp,

Hz_Parties Supp_Party,

Hz_Parties Per_Parties,

Hz_Relationships Rel,

Poz_Lookup_Codes StatusLookup,

Hz_Contact_Points Phone,

Hz_Contact_Points Fax,

Hz_Contact_Points Email,

HZ_PARTY_USG_ASSIGNMENTS PartyUsageAssignment,

Hz_Contact_points Mobile,

Hz_Person_Profiles Per_Profiles

WHERE

Supp_Party.Party_Id = Supp.Party_Id

AND Per_Parties.Status = 'A'

AND Per_Parties.Party_Type ='PERSON'

AND Supp_Party.Party_Type ='ORGANIZATION'

AND (Rel.Subject_Id = Per_Parties.Party_Id

AND Rel.Object_Id = Supp_Party.Party_Id

AND Rel.Relationship_Type = 'CONTACT'

AND Rel.relationship_code = 'CONTACT_OF'

AND Rel.SUBJECT_TYPE = 'PERSON'

AND Rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

AND Rel.OBJECT_TYPE = 'ORGANIZATION'

AND Rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

AND Rel.STATUS = 'A')

AND StatusLookup.lookup_type(+) = 'POZ_STATUS'

AND (CASE WHEN sysdate >= NVL(PartyUsageAssignment.EFFECTIVE_START_DATE, sysdate-1) AND sysdate < NVL(PartyUsageAssignment.EFFECTIVE_END_DATE, sysdate +1)

THEN 'ACTIVE' ELSE 'INACTIVE' END) = StatusLookup.lookup_code(+)

AND (Phone.Owner_Table_id(+) = Rel.Subject_id

AND Phone.Relationship_id(+) = Rel.Relationship_id

AND Phone.Owner_Table_Name(+) = 'HZ_PARTIES'

AND Phone.Contact_Point_Type(+) = 'PHONE'

AND Phone.Phone_Line_Type(+) = 'GEN'

AND Phone.Status(+) = 'A'

AND Phone.Primary_Flag(+) = 'Y')

AND (Fax.Owner_Table_id(+) = Rel.Subject_id

AND Fax.Relationship_id(+) = Rel.Relationship_id

AND Fax.Owner_Table_Name(+) = 'HZ_PARTIES'

AND Fax.Contact_Point_Type(+) = 'PHONE'

AND Fax.Phone_Line_Type(+) = 'FAX'

AND Fax.Status(+) = 'A')

AND (Mobile.Owner_Table_id(+) = Rel.Subject_id

AND Mobile.Relationship_id(+) = Rel.Relationship_id

AND Mobile.Owner_Table_Name(+) = 'HZ_PARTIES'

AND Mobile.Contact_Point_Type(+) = 'PHONE'

AND Mobile.Phone_Line_Type(+) = 'MOBILE'

AND Mobile.Status(+) = 'A')

AND (Per_Parties.PARTY_ID = PartyUsageAssignment.PARTY_ID

AND PartyUsageAssignment.PARTY_USAGE_CODE = 'SUPPLIER_CONTACT'

AND PartyUsageAssignment.STATUS_FLAG = 'A'

AND PartyUsageAssignment.OWNER_TABLE_ID = Rel.RELATIONSHIP_ID

AND PartyUsageAssignment.OWNER_TABLE_NAME = 'HZ_RELATIONSHIPS')

AND (Email.Owner_Table_id(+) = Rel.Subject_id

AND Email.Relationship_id(+) = Rel.Relationship_id

AND Email.Owner_Table_Name(+) = 'HZ_PARTIES'

AND Email.Contact_Point_Type(+) = 'EMAIL'

AND Email.Status(+) = 'A'

AND Email.Primary_Flag(+) = 'Y')

and Rel.Subject_Id = Per_Profiles.Party_Id