VIEW: APPS.HZ_DQM_P_AC_V

Source

View Text - Preformatted

SELECT a.party_id dqparty_id, l1.meaning dqparty_type, l2.meaning dqcategory_code, a.cust_account_id dqcust_account_id, a.account_number dqaccount_number, l.meaning dqcustomer_type, a.account_name dqname, a.account_activation_date dqaccount_activation_date, a.status dqstatus, NVL(arpt_sql_func_util.get_lookup_meaning ('REGISTRY_STATUS',a.status),a.status) dqstatusm FROM hz_cust_accounts a, hz_parties p, ar_lookups l, ar_lookups l1, ar_lookups l2, hz_matched_parties_gt g WHERE a.party_id = p.party_id AND l.lookup_type(+) = 'CUSTOMER_TYPE' AND a.customer_type = l.lookup_code(+) AND NVL(a.status,'A') NOT IN ('M') AND l1.lookup_type(+) = 'PARTY_TYPE' AND l1.lookup_code(+) = p.party_type AND l2.lookup_type(+) = 'CUSTOMER_CATEGORY' AND l2.lookup_code(+) = p.category_code AND -a.cust_account_id = g.party_id(+) AND g.search_context_id(+) = arh_dqm_tree_helper.ctxmax ORDER BY DECODE(g.score,NULL,0,-g.score) DESC

View Text - HTML Formatted

SELECT A.PARTY_ID DQPARTY_ID
, L1.MEANING DQPARTY_TYPE
, L2.MEANING DQCATEGORY_CODE
, A.CUST_ACCOUNT_ID DQCUST_ACCOUNT_ID
, A.ACCOUNT_NUMBER DQACCOUNT_NUMBER
, L.MEANING DQCUSTOMER_TYPE
, A.ACCOUNT_NAME DQNAME
, A.ACCOUNT_ACTIVATION_DATE DQACCOUNT_ACTIVATION_DATE
, A.STATUS DQSTATUS
, NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('REGISTRY_STATUS'
, A.STATUS)
, A.STATUS) DQSTATUSM
FROM HZ_CUST_ACCOUNTS A
, HZ_PARTIES P
, AR_LOOKUPS L
, AR_LOOKUPS L1
, AR_LOOKUPS L2
, HZ_MATCHED_PARTIES_GT G
WHERE A.PARTY_ID = P.PARTY_ID
AND L.LOOKUP_TYPE(+) = 'CUSTOMER_TYPE'
AND A.CUSTOMER_TYPE = L.LOOKUP_CODE(+)
AND NVL(A.STATUS
, 'A') NOT IN ('M')
AND L1.LOOKUP_TYPE(+) = 'PARTY_TYPE'
AND L1.LOOKUP_CODE(+) = P.PARTY_TYPE
AND L2.LOOKUP_TYPE(+) = 'CUSTOMER_CATEGORY'
AND L2.LOOKUP_CODE(+) = P.CATEGORY_CODE
AND -A.CUST_ACCOUNT_ID = G.PARTY_ID(+)
AND G.SEARCH_CONTEXT_ID(+) = ARH_DQM_TREE_HELPER.CTXMAX ORDER BY DECODE(G.SCORE
, NULL
, 0
, -G.SCORE) DESC