Query to get Customer’s with Profile Class Name, Collector Name & Bill To Address

Run below statement to initialize to respective operating unit.

exec mo_global.set_policy_context('S', <<p_org_id>>);

  SELECT party.party_name Customer_Name,
         cust.account_number Customer_Number,
         pc.name Profile_Class_Name,
         coll.name Collector_Name,
         SUBSTRB (look.meaning, 1, 8) Site_Use,
         acct_site.cust_acct_site_id Address_Id,
         SUBSTRB (loc.address1, 1, 30) Address_Line_1,
         SUBSTRB (loc.city, 1, 15) City,
         SUBSTRB (loc.state, 1, 2) State,
         SUBSTRB (loc.postal_code, 1, 10) Zip_Code,
         cust.status Cust_Status
    FROM ar_lookups look,
         ar_lookups look_status,
         hz_cust_accounts_all cust,
         hz_parties party,
         hz_cust_site_uses_all site_uses,
         hz_cust_acct_sites_all acct_site,
         hz_party_sites party_site,
         hz_locations loc,
         hz_customer_profiles prof,
         hz_cust_profile_classes pc,
         ar_collectors coll
   WHERE     cust.cust_account_id = acct_site.cust_account_id
         AND cust.party_id = party.party_id
         AND acct_site.party_site_id = party_site.party_site_id(+)
         AND loc.location_id(+) = party_site.location_id
         AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
         AND look.lookup_type(+) = 'SITE_USE_CODE'
         AND look.lookup_code(+) = site_uses.site_use_code
         AND look_status.lookup_type(+) = 'CODE_STATUS'
         AND look_status.lookup_code(+) = NVL (cust.status, 'A')
         AND cust.cust_account_id = prof.cust_account_id(+)
         AND prof.collector_id = coll.collector_id(+)
         AND prof.profile_class_id = pc.profile_class_id
         AND prof.site_use_id IS NULL
         AND SITE_USES.site_use_id IN (SELECT DISTINCT trx.bill_to_site_use_id
                                         FROM ra_customer_trx trx
                                        WHERE trx.creation_date > SYSDATE - 60)
ORDER BY party.party_name;