VIEW: APPS.HZ_DQM_AC_AS_V

Source

View Text - Preformatted

SELECT a.party_id dqparty_id, a.cust_account_id dqcust_account_id, s.cust_acct_site_id dqcust_acct_site_id, ps.party_site_id dqparty_site_id, s.bill_to_flag dqbill_to_flag, s.ship_to_flag dqship_to_flag, s.status dqstatus, NVL(arpt_sql_func_util.get_lookup_meaning ('REGISTRY_STATUS',s.status),s.status) dqstatusm, lo.address1 dqaddress1, lo.address2 dqaddress2, lo.city dqcity, lo.province dqprovince, lo.county dqcounty, t.territory_short_name dqcountry, lo.state dqstate, lo.postal_code dqpostal_code, s.org_id dqorg_id FROM hz_cust_accounts a, hz_cust_acct_sites_all s, hz_locations lo, fnd_territories_vl t, hz_party_sites ps, hz_matched_party_sites_gt g WHERE a.cust_account_id = s.cust_account_id AND s.party_site_id = ps.party_site_id AND NVL(s.status,'A') NOT IN ('M') AND ps.location_id = lo.location_id AND lo.country = t.territory_code(+) AND -s.cust_acct_site_id = g.party_site_id(+) AND g.search_context_id(+) = arh_dqm_tree_helper.ctxmax ORDER BY DECODE(g.score,NULL,-1,-g.score) DESC

View Text - HTML Formatted

SELECT A.PARTY_ID DQPARTY_ID
, A.CUST_ACCOUNT_ID DQCUST_ACCOUNT_ID
, S.CUST_ACCT_SITE_ID DQCUST_ACCT_SITE_ID
, PS.PARTY_SITE_ID DQPARTY_SITE_ID
, S.BILL_TO_FLAG DQBILL_TO_FLAG
, S.SHIP_TO_FLAG DQSHIP_TO_FLAG
, S.STATUS DQSTATUS
, NVL(ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING ('REGISTRY_STATUS'
, S.STATUS)
, S.STATUS) DQSTATUSM
, LO.ADDRESS1 DQADDRESS1
, LO.ADDRESS2 DQADDRESS2
, LO.CITY DQCITY
, LO.PROVINCE DQPROVINCE
, LO.COUNTY DQCOUNTY
, T.TERRITORY_SHORT_NAME DQCOUNTRY
, LO.STATE DQSTATE
, LO.POSTAL_CODE DQPOSTAL_CODE
, S.ORG_ID DQORG_ID
FROM HZ_CUST_ACCOUNTS A
, HZ_CUST_ACCT_SITES_ALL S
, HZ_LOCATIONS LO
, FND_TERRITORIES_VL T
, HZ_PARTY_SITES PS
, HZ_MATCHED_PARTY_SITES_GT G
WHERE A.CUST_ACCOUNT_ID = S.CUST_ACCOUNT_ID
AND S.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND NVL(S.STATUS
, 'A') NOT IN ('M')
AND PS.LOCATION_ID = LO.LOCATION_ID
AND LO.COUNTRY = T.TERRITORY_CODE(+)
AND -S.CUST_ACCT_SITE_ID = G.PARTY_SITE_ID(+)
AND G.SEARCH_CONTEXT_ID(+) = ARH_DQM_TREE_HELPER.CTXMAX ORDER BY DECODE(G.SCORE
, NULL
, -1
, -G.SCORE) DESC