POZ_ALL_SUPPLIER_CONTACTS_V
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 |
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 |