AR Customer Collector Portfolio Report

customer_name,

account_number,

portfolio,

portfolio_description,

business_unit,

account_profile_class_effective_end_date,

account_profile_class,

account_profile_collector

FROM

(

SELECT

hp.party_name                                customer_name,

hca.account_number                           account_number,

hca.attribute10                              portfolio,

(

CASE

WHEN hca.attribute10 = ‘Default – Portfolio’  THEN

‘Portfolio Default for Unclassified Customer Accounts’

WHEN hca.attribute10 != ‘Default – Portfolio’ THEN

hca.attribute10

END

)                                            portfolio_description,

hou.name                                     business_unit,

to_char(hcpf.effective_end_date, ‘yy/mm/dd’) account_profile_class_effective_end_date,

(

SELECT

name AS profile_class

FROM

hz_cust_profile_classes

WHERE

profile_class_id = hcpf.profile_class_id

)                                            account_profile_class,

(

SELECT

name

FROM

ar_collectors

WHERE

collector_id = hcpf.collector_id

)                                            account_profile_collector

FROM

hz_parties hp,

(

SELECT

*

FROM

hz_cust_accounts

WHERE

status = ‘A’

)          hca,

(

SELECT

name,

organization_id

FROM

hr_operating_units

where 1=1

and sysdate BETWEEN date_from AND date_to

)          hou,

(

SELECT

*

FROM

hz_customer_profiles_f

WHERE

1 = 1

AND site_use_id IS NULL

AND status = ‘A’

AND sysdate BETWEEN effective_start_date AND effective_end_date

)          hcpf,

hz_cust_acct_sites_all hcsa,

hz_cust_site_uses_all hcsu,

FND_SETID_SETS fss

WHERE

1 = 1

AND hp.party_id = hca.party_id

AND hcpf.cust_account_id (+) = hca.cust_account_id

and hca.cust_account_id = hcsa.cust_account_id

AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id

AND HCSU.SITE_USE_CODE = ‘BILL_TO’

and hcsa.set_id = fss.set_id

AND fss.set_name = hou.name

AND     (

(

hou.organization_id IN (:P_BUNIT)

AND     coalesce (:P_BUNIT, NULL) IS NOT NULL

)

OR      coalesce (:P_BUNIT, NULL) IS NULL

)

) group by business_unit,

account_profile_class,

portfolio,

portfolio_description,

customer_name,

account_number,

account_profile_collector,

account_profile_class_effective_end_date

order by account_profile_collector asc