POZ_SUPP_SITES_CONTACTS

Details

Schema: FUSION
Object owner: POZ
Object type: VIEW

Columns

Name
VENDOR_ID SUPP_PARTY_ID VENDOR_SITE_ID PERSON_PARTY_ID PRC_BU_ID PARTY_SITE_ID CARRIER_ID

Name

VENDOR_ID

SUPP_PARTY_ID

VENDOR_SITE_ID

PERSON_PARTY_ID

PRC_BU_ID

PARTY_SITE_ID

CARRIER_ID

Query

SQL_Statement
SELECT Supp.Vendor_Id, Supp.Party_Id as supp_party_id, Sites.Vendor_Site_Id, Per_Parties.party_id as person_party_id, Sites.prc_bu_id, Sites.party_site_id, Sites.carrier_id FROM Poz_Suppliers Supp, Poz_Supplier_Sites_All_M Sites, Hz_Parties Supp_Party, HZ_PARTY_USG_ASSIGNMENTS PartyUsageAssignment, Hz_Parties Per_Parties, Hz_Relationships Rel WHERE Supp.vendor_id = Sites.vendor_id (+) And Supp_Party.Party_Id = Supp.Party_Id And Supp_Party.Party_Type = 'ORGANIZATION' AND (Rel.Subject_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 Per_Parties.Status (+) = 'A' And Per_Parties.Party_Type (+) ='PERSON' 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')

SQL_Statement

SELECT

Supp.Vendor_Id,

Supp.Party_Id as supp_party_id,

Sites.Vendor_Site_Id,

Per_Parties.party_id as person_party_id,

Sites.prc_bu_id,

Sites.party_site_id,

Sites.carrier_id

FROM

Poz_Suppliers Supp,

Poz_Supplier_Sites_All_M Sites,

Hz_Parties Supp_Party,

HZ_PARTY_USG_ASSIGNMENTS PartyUsageAssignment,

Hz_Parties Per_Parties,

Hz_Relationships Rel

WHERE

Supp.vendor_id = Sites.vendor_id (+)

And Supp_Party.Party_Id = Supp.Party_Id

And Supp_Party.Party_Type = 'ORGANIZATION'

AND (Rel.Subject_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 Per_Parties.Status (+) = 'A'

And Per_Parties.Party_Type (+) ='PERSON'

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')