View: HZ_DQM_AC_C_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.HZ_DQM_AC_C_V

View Text

SELECT A.CUST_ACCOUNT_ID DQCUST_ACCOUNT_ID
, A.CUST_ACCOUNT_ROLE_ID DQCUST_ACCOUNT_ROLE_ID
, C.RELATIONSHIP_ID DQRELATIONSHIP_ID
, E.ORG_CONTACT_ID DQORG_CONTACT_ID
, D.PARTY_NAME DQPARTY_NAME
, H1.MEANING DQTITLE
, H.MEANING DQJOB_TITLE
, E.CONTACT_NUMBER DQCONTACT_NUMBER
, A.STATUS DQSTATUS
, NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('REGISTRY_STATUS'
, A.STATUS)
, A.STATUS) DQSTATUSM
FROM HZ_CUST_ACCOUNT_ROLES A
, HZ_PARTIES B
, HZ_RELATIONSHIPS C
, HZ_PARTIES D
, HZ_ORG_CONTACTS E
, HZ_MATCHED_CONTACTS_GT G
, AR_LOOKUPS H
, AR_LOOKUPS H1
WHERE A.PARTY_ID = B.PARTY_ID
AND A.CUST_ACCT_SITE_ID IS NULL
AND NVL(A.STATUS
, 'A') NOT IN ('M')
AND B.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND B.PARTY_ID = C.PARTY_ID
AND C.RELATIONSHIP_ID = E.PARTY_RELATIONSHIP_ID
AND C.DIRECTIONAL_FLAG = 'F'
AND C.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND C.SUBJECT_ID = D.PARTY_ID
AND -A.CUST_ACCOUNT_ROLE_ID = G.ORG_CONTACT_ID(+)
AND G.SEARCH_CONTEXT_ID(+) = ARH_DQM_TREE_HELPER.CTXMAX
AND H.LOOKUP_TYPE(+) = 'RESPONSIBILITY'
AND H.LOOKUP_CODE(+) = E.JOB_TITLE_CODE
AND H1.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND H1.LOOKUP_CODE(+) = E.TITLE ORDER BY DECODE(G.SCORE
, NULL
, -1
, -G.SCORE) DESC

Columns