[Home] [Help]
| Product: | AMS - Marketing |
|---|---|
| Description: | This view returns primary ship to contact for an account . Used for list generation. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PR.PARTY_ID AMS_CONTACT_ID
, PR.SUBJECT_ID CONTACT_PARTY_ID
, R.CUST_ACCOUNT_ID ACCT_ID
, R.CUST_ACCOUNT_ROLE_ID
, R.CURRENT_ROLE_STATE
, R.CURRENT_ROLE_STATE_EFFECTIVE
, R.CUST_ACCT_SITE_ID
, R.BEGIN_DATE
, R.END_DATE
, R.PRIMARY_FLAG
, R.ROLE_TYPE
, R.LAST_UPDATE_DATE
, R.SOURCE_CODE
, R.LAST_UPDATED_BY
, R.CREATION_DATE
, R.CREATED_BY
, P.PARTY_NUMBER
, P.PARTY_NAME
, P.PARTY_TYPE
, P.PERSON_PRE_NAME_ADJUNCT
, P.PERSON_FIRST_NAME
, P.PERSON_MIDDLE_NAME
, P.PERSON_LAST_NAME
, P.PERSON_NAME_SUFFIX
, P.PERSON_TITLE
, P.PERSON_ACADEMIC_TITLE
, P.PERSON_PREVIOUS_LAST_NAME
, P.KNOWN_AS
, P.PERSON_IDEN_TYPE
, P.PERSON_IDENTIFIER
, RESP.RESPONSIBILITY_TYPE
, PR.PARTY_RELATIONSHIP_ID AMS_RELATIONSHIP_ID
, PR.PARTY_ID RELATIONSHIP_PARTY_ID
FROM HZ_CUST_ACCOUNT_ROLES R
, HZ_CUST_ACCOUNTS A
, HZ_PARTIES P
, HZ_PARTY_RELATIONSHIPS PR
, HZ_ORG_CONTACTS C
, HZ_ROLE_RESPONSIBILITY RESP
WHERE A.CUST_ACCOUNT_ID = R.CUST_ACCOUNT_ID
AND R.PARTY_ID = PR.PARTY_ID
AND PR.SUBJECT_ID=P.PARTY_ID
AND PR.PARTY_RELATIONSHIP_ID=C.PARTY_RELATIONSHIP_ID
AND PR.CONTENT_SOURCE_TYPE='USER_ENTERED'
AND A.STATUS='A'
AND P.STATUS='A'
AND PR.STATUS='A'
AND C.STATUS='A'
AND RESP.PRIMARY_FLAG='Y'
AND R.CUST_ACCOUNT_ROLE_ID=RESP.CUST_ACCOUNT_ROLE_ID
AND R.CUST_ACCT_SITE_ID IS NULL
AND RESP.RESPONSIBILITY_TYPE='SHIP_TO'