Various Queries on Customer Data (HZ Tables)

 Various Queries on Customer Data (HZ Tables)

Product: TCA / Oracle Receivables
Overview
There have been many instances where I have been asked by the Business Users to provide Customer Listing reports based on different parameters. The different requests typically are as follows
1. Customer listing with all Sites for a specific Org
2. A listing of only those Customer with whom we have had transactions in the last ‘x’ years
3. A listing of all Customer Sites that do not have any Business purpose associated with it
4. Customer Listing By Collector
5. Customer Listing along with Profile Class names and Collector names
6. And so on …
We have the Standard Customer Listing Reports in the application. However these reports are not org stripped because the HZ_CUST_ACCOUNTS data is not org stripped plus the standard reports could not be used for all different flavors of the Business requests. In these cases, I used the following queries

Queries

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 ;

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 ;

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 ;

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 ;

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 ;

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 ;
Summary
For me these queries were very handy whenever I had any Customer Listing request from the Users. A little tweak here and there to these queries would fetch me all the data I needed. I always referred to the TRM to look for additional columns of these tables if there was any need to use them. A handy SQL query for a Consultant helps!

=========================================================================

Queries to select the source data

1. Query to select the response note from customer calls. These notes are at the account level and hence have no reference to the transaction

SELECT acc_customer_id "CUST_ACCOUNT_ID"
, ano_text note_text
, rcu_customer_number account_number
, 'RESPONSE' note_type
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
FROM ar_customer_calls_v
WHERE trunc(creation_date) >= trunc(&p_date_from)
AND trunc(creation_date) <= trunc(&p_date_to) ;

2. Query to select the customer call topic notes. If the customer_trx_id is NULL, it means that the note is at an account level. Else the note is at a transaction level.

SELECT cct_customer_trx_id customer_trx_id
, cct_customer_id cust_account_id
, ano_text note_text
, 'CALL_NOTE' note_type
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
FROM ar_customer_call_topics_v
WHERE trunc(creation_date) >= trunc(&p_date_from)
AND trunc(creation_date) <= trunc(&p_date_to) ;
Check if Source data is already migrated

1. Query for checking if the account level note is already migrated

SELECT DISTINCT COUNT (*)
INTO x_count
FROM jtf_notes_b jtfn
, jtf_notes_tl jtfn_tl
WHERE
jtfn.source_object_code = 'IEX_ACCOUNT'
AND jtfn.source_object_id = note_rec.cust_acct_id
http://oracle.anilrpatil.com Page 3
AND jtfn_tl.jtf_note_id = jtfn.jtf_note_id
AND jtfn_tl.source_lang = 'US'
AND UPPER(jtfn_tl.notes) = UPPER(note_rec.note_text)
AND jtfn.creation_date = note_rec.creation_date
AND jtfn.created_by = note_rec.created_by
AND jtfn.last_update_date = note_rec.last_update_date
AND jtfn.last_updated_by = note_rec.last_updated_by;
In the above query, note_rec is a record_type consisting the source data. If the x_count variable is not zero, it means that the note is already migrated.

2. Query for checking if the invoice level note is already migrated

SELECT DISTINCT COUNT (*)
INTO x_count
FROM jtf_notes_b jtfn
, jtf_notes_tl jtfn_tl
WHERE
jtfn.source_object_code = 'IEX_INVOICES'
AND jtfn.source_object_id = note_rec.payment_schedule_id
AND jtfn_tl.jtf_note_id = jtfn.jtf_note_id
AND jtfn_tl.source_lang = 'US'
AND UPPER(jtfn_tl.notes) = UPPER(note_rec.note_text)
AND jtfn.creation_date = note_rec.creation_date
AND jtfn.created_by = note_rec.created_by
AND jtfn.last_update_date = note_rec.last_update_date
AND jtfn.last_updated_by = note_rec.last_updated_by;
In the above query, note_rec is a record_type consisting the source data. If the x_count variable is not zero, it means that the note is already migrated.
JTF_NOTES_PUB API
1) Account Level Notes – The Account level notes are migrated using the following code
Declare
l_notes_detail CLOB;
l_note_type VARCHAR(30) := 'IEX_HIST';
l_note_status VARCHAR2 (1) := 'I';
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_note_id NUMBER ;
l_msg_index_out NUMBER;
BEGIN
jtf_notes_pub.create_note
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
p_jtf_note_id => NULL,
p_validation_level => 100,
p_source_object_id => note_rec.cust_account_id,
http://oracle.anilrpatil.com Page 4
p_source_object_code => 'IEX_ACCOUNT',
p_notes => note_rec.note_text ,
p_notes_detail => l_notes_detail,
p_entered_by => note_rec.created_by,
p_entered_date => note_rec.creation_date,
p_last_update_date => note_rec.last_update_date,
p_last_updated_by => note_rec.last_updated_by,
p_creation_date => note_rec.creation_date,
p_created_by => note_rec.created_by,
p_last_update_login => fnd_global.login_id,
p_note_type => l_note_type,
p_note_status => l_note_status,
x_jtf_note_id => l_note_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF (l_return_status <> 'S')
THEN
fnd_file.put_line(fnd_file.LOG,'l_return_status <> S ');
IF (fnd_msg_pub.count_msg > 0)
THEN
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get
(p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out
);
DBMS_OUTPUT.put_line ('Error:' || l_msg_data);
fnd_file.put_line(fnd_file.LOG,'ERROR :' || l_msg_data);
END LOOP;
END IF;
END IF;
COMMIT;
END ;
In the above query, note_rec is a record_type consisting the source date.
The value of l_note_type should be a valid lookup_code for lookup_type JTF_NOTE_TYPE
SELECT LOOKUP_CODE,MEANING,DESCRIPTION,
TAG,START_DATE_ACTIVE,END_DATE_ACTIVE,ENABLED_FLAG,
LOOKUP_TYPE
FROM FND_LOOKUP_VALUES_VL WHERE (nvl('', territory_code) = territory_code or territory_code is null)
AND lookup_type = 'JTF_NOTE_TYPE'
order by LOOKUP_CODE ;
2) Invoice Level Notes – For migrating the Invoice level notes, the code remains the same except the value passed to the parameters p_source_object_id and p_source_object_code . The values that needs to be passed to these parameters are
http://oracle.anilrpatil.com Page 5
p_source_object_id => note_rec.payment_schedule_id ,
p_source_object_code => 'IEX_INVOICES’
Summary
This document details one approach for migrating Call Notes from AR to Advanced Collections. These notes can then be viewed in the Notes Tab of the Collections Form.
References
Oracle Common Applications Components – API Reference Guide
Oracle Advanced Collections Implementation Guide
Oracle Advanced Collections User Guide