AR Account Status

	select
		ARPT_SQL_FUNC_UTIL.get_lookup_meaning(
			'ACCOUNT_STATUS', decode(
			cp_site.collector_id, null, cp_cust.account_status,
			cp_site.account_status )  ) status        ,
		substrb(party.party_name,1,50) Name          ,  
		cust_acct.account_number           Number_A       ,
		cust_acct.cust_account_id               Customer_Id ,
                                     nvl(cp_site.collector_id,cp_cust.collector_id)           Collector_Id ,
		col.name                    Collector        ,
		site_uses.site_use_id              cp_site_use_id ,
		site_uses.location                 location,
		AR_ARXASR_XMLP_PKG.c_status_summary_labelformula(ARPT_SQL_FUNC_UTIL.get_lookup_meaning ( 'ACCOUNT_STATUS' , decode ( cp_site.collector_id , null , cp_cust.account_status , cp_site.account_status ) )) c_status_summary_label, 
 		AR_ARXASR_XMLP_PKG.c_data_not_foundformula(ARPT_SQL_FUNC_UTIL.get_lookup_meaning ( 'ACCOUNT_STATUS' , decode ( cp_site.collector_id , null , cp_cust.account_status , cp_site.account_status ) )) c_data_not_found
	from
		ar_collectors	                  col,
		hz_customer_profiles	                  cp_cust,
                                     hz_customer_profiles                    cp_site,
		hz_cust_accounts		cust_acct,
                                     hz_parties                                       party,
		hz_cust_site_uses		site_uses,
		hz_cust_acct_sites                        acct_site
                  where   acct_site.cust_account_id = cust_acct.cust_account_id
                   and   cust_acct.party_id = party.party_id
                  and     site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
                  and     site_uses.site_use_code = 'BILL_TO'
                  and     cp_site.site_use_id(+) = site_uses.site_use_id
                  and     cp_cust.cust_account_id = cust_acct.cust_account_id
                  and     cp_cust.site_use_id  is NULL
                  and     col.collector_id =  nvl(cp_site.collector_id,cp_cust.collector_id)
                 and     exists (
                        select 'x'
                        from &lp_ar_payment_schedules ps
                        where ps.status  = 'OP'
                        and   ps.customer_id    = cust_acct.cust_account_id
                        and   ps.customer_site_use_id = site_uses.site_use_id )
                  &lp_start_account_status1
                  &lp_end_account_status1
	&lp_customer_name_low
	&lp_customer_name_high
	&lp_customer_number_low
	&lp_customer_number_high
	&lp_collector_name_low
	&lp_collector_name_high
	UNION 
	select
		ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',
			cp_cust.account_status),
		substrb(party.party_name,1,50) ,
		cust_acct.account_number,
		cust_acct.cust_account_id,
		col.collector_id,
		col.name,
		decode(:p_order_by,NULL,0,-88888),
		to_char(null),
		AR_ARXASR_XMLP_PKG.c_status_summary_labelformula(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_cust.account_status)) c_status_summary_label, 
		AR_ARXASR_XMLP_PKG.c_data_not_foundformula(ARPT_SQL_FUNC_UTIL.get_lookup_meaning('ACCOUNT_STATUS',cp_cust.account_status)) c_data_not_found
	from
		ar_collectors		col,
		hz_customer_profiles	cp_cust,
		hz_cust_accounts cust_acct,
                                     hz_parties party
	where
		cp_cust.cust_account_id = cust_acct.cust_account_id
                   and            cust_acct.party_id = party.party_id
	and	cp_cust.site_use_id is null
	and	cp_cust.collector_id = col.collector_id
	and	exists (
			select 'x'
			from &lp_ar_payment_schedules ps
                                                        where ps.status  = 'OP'
			and   ps.customer_id 	= cust_acct.cust_account_id
			and   ps.customer_site_use_id is null )
        &lp_start_account_status2
        &lp_end_account_status2
	&lp_customer_name_low
	&lp_customer_name_high
	&lp_customer_number_low
	&lp_customer_number_high
	&lp_collector_name_low
	&lp_collector_name_high
&lp_order_by