TCA..Useful Scripts !!!!

To get the list of customers created in a financial year:

SELECT DISTINCT hl.address1 "Customer Name",
hl.address2 "Address2",
hl.address3 "Address3",
hl.city ,
hl.state,
hl.postal_code,
hl.country,
DECODE(hcsa.status,'A','Active','Inactive') status,HCSA.creation_date
FROM apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcsa,
apps.hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND TO_DATE(TO_CHAR(HCSA.creation_date,'DD-MON-YYYY'),'DD-MON-YYYY') >= '28-JUN-2010'
ORDER BY hl.address1,HCSA.creation_date



To get the list of customers whose credit limit changed in the financial year:

SELECT DISTINCT hl.address1 "Customer Name",
hl.address2 "Address2",
hl.address3 "Address3",
hl.city ,
hl.state,
hl.postal_code,
hl.country,
DECODE(hcsa.status,'A','Active','Inactive') status,
hcpa.last_update_date
FROM apps.hz_parties hp,
apps.hz_party_sites hps,
apps.hz_locations hl,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcsa,
apps.hz_cust_site_uses_all hcsu,
apps.hz_customer_profiles cp,
apps.hz_cust_profile_amts hcpa
WHERE hp.party_id = hps.party_id
AND hps.location_id = hl.location_id
AND hp.party_id = hca.party_id
AND hcsa.party_site_id = hps.party_site_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
and cp.cust_account_id = hca.cust_account_id
AND hcpa.cust_account_profile_id = cp.cust_account_profile_id
AND cp.site_use_id IS NULL
AND hcpa.site_use_id IS NULL
AND TO_DATE(TO_CHAR(hcpa.last_update_date,'DD-MON-YYYY'),'DD-MON-YYYY') >= '28-JUN-2010'
ORDER BY hcpa.last_update_date


Customers with Credit Limit:

SELECT DISTINCT hp.party_name "Customer Name",
DECODE(hp.status,'A','Active','Inactive') status,
hcpa.trx_credit_limit "Trx Credit Limit" ,
hcpa.overall_credit_limit "Overall Credit Limit"
FROM apps.hz_parties hp,
apps.hz_cust_accounts_all hca,
apps.hz_cust_acct_sites_all hcsa,
apps.hz_cust_site_uses_all hcsu,
apps.hz_customer_profiles cp,
apps.hz_cust_profile_amts hcpa
WHERE hp.party_id = hca.party_id
AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
and cp.cust_account_id = hca.cust_account_id
AND hcpa.cust_account_profile_id = cp.cust_account_profile_id
AND cp.site_use_id IS NULL
AND hcpa.site_use_id IS NULL
ORDER BY hp.party_name


Customers with last 12 month gross sales :

select cust.address1 "Customer Name",
cust.address2 "Address1",
cust.address3 "Address3",
cust.city,
cust.state,
cust.postal_code,
cust.country,
nvl(sum(aps.amount_due_original),0) "Gross Sales"
from ar_payment_schedules_all aps,
(select distinct cust_acct.cust_account_id Customer_id,
acct_site.cust_acct_site_id,
cust_acct.account_number Customer_Number,
loc.address1 ,
loc.address2 ,
loc.address3,
loc.city,
loc.state,
loc.country,
loc.postal_code,
site_uses.site_use_id Site_Use_id
from
hz_cust_accounts cust_acct,
hz_parties party,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_site_uses_all site_uses,
fnd_territories_vl terr,
ar_collectors coll,
hz_customer_profiles cp
where cp.cust_account_id = cust_acct.cust_account_id
and cust_acct.party_id = party.party_id
and cp.collector_id = coll.collector_id
and site_uses.status = 'A'
and cust_acct.cust_account_id = acct_site.cust_account_id
and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
and acct_site.party_site_id = party_site.party_site_id
and loc.location_id = party_site.location_id
and site_uses.site_use_code = 'BILL_TO'
and site_uses.status = 'A'
and loc.country = terr.territory_code
and cp.cust_account_profile_id = (
select max(cp2.cust_account_profile_id)
from hz_customer_profiles cp2
where cust_acct.cust_account_id = cp2.cust_account_id
and (site_uses.site_use_id = cp2.site_use_id
or
cp2.site_use_id is null
))
order by loc.address1) cust
where aps.customer_id = cust.customer_id
and aps.customer_site_use_id = cust.site_use_id
and aps.invoice_currency_code = 'USD'
and aps.trx_date between
add_months(sysdate, -12) and sysdate
and aps.class not in ('CM', 'PMT')
GROUP BY cust.cust_acct_site_id,cust.address1, cust.address2,
cust.address3 ,
cust.city,
cust.state,
cust.postal_code,
cust.country
ORDER BY cust.address1