AR Account Status

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Account Status Report (XML)
Short Name: ARXASR_XML
DB package: AR_ARXASR_XMLP_PKG
	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
Parameter Name SQL text Validation
Ledger ID
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Collector High
 
LOV Oracle
Collector Low
 
LOV Oracle
Account Status High
 
LOV Oracle
Account Status Low
 
LOV Oracle
Order By
 
LOV Oracle
Ledger Currency
 
LOV Oracle