Order Customer Contact Information.sql

--SQL Query to Obtain Customer Contact Information
SELECT
	RelationshipPEO.RELATIONSHIP_REC_ID
  , RelationshipPEO.RELATIONSHIP_ID
  , RelationshipPEO.DIRECTIONAL_FLAG
  , RelationshipPEO.status
  , OrganizationPartyPEO.PARTY_NAME
  , OrganizationPartyPEO.PARTY_ID
  , PersonPartyPEO.PARTY_NAME AS PARTY_NAME1
  , PersonPartyPEO.PARTY_ID   AS PARTY_ID1
  , PersonPartyPEO.PERSON_FIRST_NAME
  , PersonPartyPEO.PERSON_LAST_NAME
  , PersonPartyPEO.status
	-- , HzLookupPEO.DESCRIPTION
  , HzLookupPEO.LOOKUP_TYPE
  , HzLookupPEO.LOOKUP_CODE
  , OrganizationContactPEO.JOB_TITLE
  , OrganizationContactPEO.ORG_CONTACT_ID
  , OrganizationContactPEO.DEPARTMENT
  , OrganizationPartyPEO.status
  , EmailPEO.EMAIL_ADDRESS
  , EmailPEO.CONTACT_POINT_ID
  , EmailPEO.CONTACT_POINT_TYPE
  , EmailPEO.status
  , PhonePEO.RAW_PHONE_NUMBER
  , PhonePEO.CONTACT_POINT_ID   AS CONTACT_POINT_ID1
  , PhonePEO.CONTACT_POINT_TYPE AS CONTACT_POINT_TYPE1
  , PhonePEO.PHONE_AREA_CODE
  , PhonePEO.PHONE_COUNTRY_CODE
  , PhonePEO.PHONE_NUMBER
  , PhonePEO.status
  , WebPEO.URL
  , WebPEO.CONTACT_POINT_ID   AS CONTACT_POINT_ID2
  , WebPEO.CONTACT_POINT_TYPE AS CONTACT_POINT_TYPE2
  , WebPEO.status
  , OrganizationPartyPEO.SIC_CODE
  , OrganizationPartyPEO.SIC_CODE_TYPE
  , OrganizationPartyPEO.PARTY_UNIQUE_NAME
  , PersonPartyPEO.SALUTATION
  , PersonPartyPEO.PERSON_PRE_NAME_ADJUNCT
  , PersonPartySitePEO.PARTY_SITE_ID AS PERSON_PARTY_SITE_ID
  , PersonPartySitePEO.status
  , LocationPEO.LOCATION_ID AS PERSON_LOCATION_ID
  , LocationPEO.ADDRESS1
  , LocationPEO.ADDRESS2
  , LocationPEO.ADDRESS3
  , LocationPEO.ADDRESS4
  , LocationPEO.CITY
  , LocationPEO.STATE
  , LocationPEO.POSTAL_CODE
  , LocationPEO.COUNTRY
  , PartySitePEO.PARTY_SITE_ID
  , PartySitePEO.PARTY_SITE_NAME
  , PartySitePEO.status
  , OrganizationContactPEO.REFERENCE_USE_FLAG
  , DECODE(PersonPartyPEO.PARTY_ID
			 , NVL( OrganizationPartyPEO.PREFERRED_CONTACT_PERSON_ID, -2), 'Y'
			 , 'N') AS PRIMARY_CONTACT_FLAG
  , OrganizationContactPEO.SALES_AFFINITY_CODE
  , OrganizationContactPEO.SALES_AFFINITY_COMMENTS
  , OrganizationContactPEO.SALES_BUYING_ROLE_CODE
  , OrganizationContactPEO.SALES_INFLUENCE_LEVEL_CODE
  , OrganizationContactPEO.DEPARTMENT_CODE
  , OrganizationContactPEO.JOB_TITLE_CODE
  , RelationshipPEO.START_DATE
  , RelationshipPEO.END_DATE
  , PersonPartyPEO.EMAIL_ADDRESS AS EMAIL_ADDRESS1
  , PersonPartyPEO.URL           AS URL1
  , PersonPartyPEO.PRIMARY_EMAIL_CONTACT_PT_ID
  , PersonPartyPEO.PRIMARY_PHONE_CONTACT_PT_ID
  , PersonPartyPEO.PREFERRED_CONTACT_METHOD
FROM
	fusion.HZ_RELATIONSHIPS      RelationshipPEO
  , fusion.HZ_PARTIES            OrganizationPartyPEO
  , fusion.HZ_PARTIES            PersonPartyPEO
  , fusion.HZ_RELATIONSHIP_TYPES RelationshipTypePEO
  , fusion.HZ_LOOKUPS            HzLookupPEO
  , fusion.HZ_ORG_CONTACTS       OrganizationContactPEO
  , fusion.HZ_CONTACT_POINTS     EmailPEO
  , fusion.HZ_CONTACT_POINTS     PhonePEO
  , fusion.HZ_CONTACT_POINTS     WebPEO
  , fusion.HZ_PARTY_SITES        PersonPartySitePEO
  , fusion.HZ_LOCATIONS          LocationPEO
  , fusion.HZ_PARTY_SITES        PartySitePEO
WHERE
	(
		(
			(
				(
					(
						(
							RelationshipPEO.OBJECT_ID              = OrganizationPartyPEO.PARTY_ID
							AND OrganizationPartyPEO.Party_NAME LIKE 'PMC%'
						)
						AND
						(
							RelationshipPEO.SUBJECT_ID = PersonPartyPEO.PARTY_ID
							--             AND PersonPartyPEO.PARTY_ID  like '&CUSTOMER_NAME'
						)
					)
					AND
					(
						(
							(
								(
									RelationshipPEO.RELATIONSHIP_TYPE = RelationshipTypePEO.RELATIONSHIP_TYPE
								)
								AND
								(
									RelationshipPEO.RELATIONSHIP_CODE = RelationshipTypePEO.FORWARD_REL_CODE
								)
							)
							AND
							(
								RelationshipPEO.SUBJECT_TYPE = RelationshipTypePEO.SUBJECT_TYPE
							)
						)
						AND
						(
							RelationshipPEO.OBJECT_TYPE = RelationshipTypePEO.OBJECT_TYPE
						)
					)
				)
				AND
				(
					RelationshipTypePEO.ROLE = HzLookupPEO.LOOKUP_CODE
				)
			)
			AND
			(
				RelationshipPEO.RELATIONSHIP_ID = OrganizationContactPEO.PARTY_RELATIONSHIP_ID
			)
		)
		AND
		(
			(
				RelationshipPEO.RELATIONSHIP_ID = EmailPEO.RELATIONSHIP_ID(+)
			)
			AND
			(
				RelationshipPEO.SUBJECT_ID         = EmailPEO.OWNER_TABLE_ID(+)
				AND EmailPEO.OWNER_TABLE_NAME(+)   = 'HZ_PARTIES'
				AND EmailPEO.CONTACT_POINT_TYPE(+) = 'EMAIL'
				AND EmailPEO.STATUS(+)             = 'A'
				--     AND EmailPEO.PRIMARY_FLAG(+)       = 'Y'
				AND SYSDATE BETWEEN EmailPEO.START_DATE(+) AND EmailPEO.END_DATE(+)
			)
		)
		AND
		(
			(
				RelationshipPEO.RELATIONSHIP_ID = PhonePEO.RELATIONSHIP_ID(+)
			)
			AND
			(
				RelationshipPEO.SUBJECT_ID         = PhonePEO.OWNER_TABLE_ID(+)
				AND PhonePEO.OWNER_TABLE_NAME(+)   = 'HZ_PARTIES'
				AND PhonePEO.CONTACT_POINT_TYPE(+) = 'PHONE'
				AND PhonePEO.STATUS(+)             = 'A'
				--     AND PhonePEO.PRIMARY_FLAG(+)       = 'Y'
				AND SYSDATE BETWEEN PhonePEO.START_DATE(+) AND PhonePEO.END_DATE(+)
			)
		)
		AND
		(
			(
				RelationshipPEO.RELATIONSHIP_ID = WebPEO.RELATIONSHIP_ID(+)
			)
			AND
			(
				RelationshipPEO.SUBJECT_ID       = WebPEO.OWNER_TABLE_ID(+)
				AND WebPEO.OWNER_TABLE_NAME(+)   = 'HZ_PARTIES'
				AND WebPEO.CONTACT_POINT_TYPE(+) = 'WEB'
				AND WebPEO.STATUS(+)             = 'A'
				--     AND WebPEO.PRIMARY_FLAG(+)       = 'Y'
				AND SYSDATE BETWEEN WebPEO.START_DATE(+) AND WebPEO.END_DATE(+)
			)
		)
		AND RelationshipPEO.SUBJECT_TYPE = 'PERSON'
		AND RelationshipPEO.STATUS       = 'A'
		AND
		(
			SYSDATE BETWEEN RelationshipPEO.START_DATE AND RelationshipPEO.END_DATE
		)
		AND HzLookupPEO.LOOKUP_TYPE = 'HZ_RELATIONSHIP_ROLE'
		AND
		(
			RelationshipPEO.SUBJECT_ID = PersonPartySitePEO.PARTY_ID(+)
		)
		AND
		(
			RelationshipPEO.RELATIONSHIP_ID = PersonPartySitePEO.RELATIONSHIP_ID(+)
		)
		AND
		(
			SYSDATE BETWEEN PersonPartySitePEO.START_DATE_ACTIVE(+) AND PersonPartySitePEO.END_DATE_ACTIVE(+)
		)
		AND
		(
			PersonPartySitePEO.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
		)
		AND
		(
			PersonPartySitePEO.STATUS(+) = 'A'
		)
		AND
		(
			PersonPartySitePEO.LOCATION_ID = LocationPEO.LOCATION_ID(+)
		)
		AND
		(
			OrganizationContactPEO.PARTY_SITE_ID = PartySitePEO.PARTY_SITE_ID(+)
		)
	)