Customer collector name at site level query
Introduction:
This SQL query is used to fetching the data of customer and collector name at site level.
Cause of the issue:
User’s unable to find out how many customers does not have collector setup to send dunning reports.
How do we solve:
By providing this report users can able to find out how many customers does not have collector details. So that they can update.
SQL Query:
with hca as (
SELECT
hca.account_number,
hca.cust_account_id AS cust_account_id,
hca.party_id AS account_party_id,
hca.status AS account_status,
hca.ATTRIBUTE10 AS ATTRIBUTE10,
( decode(hca.attribute_category, ‘All’, hca.attribute4, NULL) ) Customer_Group,
setidset.set_id AS set_id_set_id,
setidset.set_name AS set_id_set_name
FROM
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
fnd_setid_sets_vl setidset
WHERE
hca.cust_account_id = hcas.cust_account_id (+)
AND hcas.set_id = setidset.set_id (+)
AND ( CASE
WHEN setidset.set_name IN ( :P_BU ) THEN
1
WHEN ( coalesce(NULL, :P_BU) IS NULL ) THEN
1
END = 1 )
group by hca.account_number,
hca.cust_account_id,
hca.party_id,
hca.status,
hca.ATTRIBUTE10,
( decode(hca.attribute_category, ‘All’, hca.attribute4, NULL) ),
setidset.set_id,
setidset.set_name
)
SELECT
party.party_name Customer_Name
,cust.account_number Customer_Acc_Number
,pc.name Prof_Class_Name
,coll.name Collector_Name
,cust.ATTRIBUTE10 Cust_Portfolio
,cust.Customer_Group
,ft.territory_short_name Customer_country
,cust.set_id_set_name Business_Unit
,cust.account_status Cust_Account_Status
,to_char(prof.effective_start_date,’YYYY-MM-DD’) Prof_Start_Date
,to_char(prof.effective_end_date,’YYYY-MM-DD’) Prof_End_Date
,to_char(PROF.LAST_UPDATE_DATE,’YYYY-MM-DD’) Prof_Last_Update_Date
,(SELECT a.NAME
FROM ra_terms a
WHERE a.term_id = prof.standard_terms) payment_term_name
FROM
hca cust
,hz_parties party
,fnd_territories_vl ft
,hz_customer_profiles_f prof
,hz_cust_profile_classes pc
,ar_collectors coll
WHERE
1 = 1
AND cust.account_party_id = party.party_id
AND party.country = ft.territory_code(+)
AND cust.cust_account_id = prof.cust_account_id(+)
AND prof.profile_class_id = pc.profile_class_id
AND prof.collector_id = coll.collector_id
and trunc(sysdate) between trunc(prof.effective_start_date)
and trunc(prof.effective_end_date)
and prof.site_use_id is null
and party.PARTY_TYPE = ‘ORGANIZATION’
ORDER BY
party.party_name,
cust.account_number
