Queries on HZ Tables

1. Customer listing with all Sites for a specific Org 


execute dbms_application_info.set_client_info('Org_id') ;

SELECT

  substrb(party.party_name,1,50)         Customer_Name ,
  cust.account_number                    Customer_Number ,
  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 cust,
  hz_parties party,
  hz_cust_site_uses site_uses,
  hz_cust_acct_sites acct_site,
  hz_party_sites party_site,
  hz_locations loc
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')
ORDER BY
  party.party_name  ;



2. Customer listing with only Identifying Addresses for a specific Org 

The IDENTIFYING_ADDRESS_FLAG column of HZ_PARTY_SITES table indicates
if the Address is Identifying Address or not. (Values = Y or N).

(The Select and the From clause is the same as above query)

execute dbms_application_info.set_client_info('Org_id') ;

SELECT

  . . .
FROM
  . . .
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 party_site.identifying_address_flag = ‘Y’
ORDER BY
  party.party_name  ;




3. Listing of all Customer Sites that do not have any Business Purpose
The SITE_USE_CODE of the HZ_CUST_SITE_USES_ALL table stores the ‘Business Purpose’ code of the site. If we need a listing of Customer sites that do not have any Business Purpose, we add the where clause of  ‘site_uses.site_use_code is NULL ‘ to the query. This listing was used for data cleanup purpose.
 
(The Select and the From clause is the same as above query)


execute dbms_application_info.set_client_info('Org_id') ;

SELECT

  . . .
FROM
  . . .
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 site_uses.site_use_code is NULL
ORDER BY
  party.party_name  ;




4. Listing of all Customer with Primary Bill To Address
The address with a ‘Bill To’ business purpose has the SITE_USE_CODE column of the HZ_CUST_SITE_USES_ALL table as ‘BILL_TO’

(The Select and the From clause is the same as above query)

execute dbms_application_info.set_client_info('Org_id') ;

SELECT

  . . .
FROM
  . . .
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 site_uses.site_use_code  = 'BILL_TO'
  AND site_uses.primary_flag   = 'Y'
  AND site_uses.status   = 'A'
ORDER BY
  party.party_name  ;



5. Listing of all Customer with Bill To Address with whom we had transaction in the 
last 1 year  


The BILL_TO_SITE_USE_ID of the RA_CUSTOMER_TRX_ALL table stores the
SITE_USE_ID of HZ_CUST_SITE_USES_ALL table.

(The Select and the From clause is the same as above query)

execute dbms_application_info.set_client_info('Org_id') ;

SELECT

  . . .
FROM
  . . .
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 SITE_USES.site_use_id in (SELECT distinct trx.bill_to_site_use_id from ra_customer_trx trx
where trx.creation_date > sysdate - 365)
ORDER BY
  party.party_name  ;



6. Listing of Customer’s with Profile Class Name, Collector Name, Bill To Address  
execute dbms_application_info.set_client_info('Org_id') ;

SELECT

  substrb(party.party_name,1,50)         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 cust,
  hz_parties party,
  hz_cust_site_uses site_uses,
  hz_cust_acct_sites 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  ;