AR Customer Profiles

SELECT	prof.cust_account_profile_id C_CUSTOMER_PROFILE_ID_a,
	&P_SORT1 dcolsort_num,
	&P_SORT2 dcolsort_id,
	cust.cust_account_id C_CUSTOMER_ID,
	substrb(party.party_name,1,50) C_CUSTOMER_NAME,
	cust.account_number C_CUSTOMER_NUMBER,
                  site.location C_SITE_LOCATION,
                  substrb(loc.ADDRESS1,1,25)||' '||substrb(loc.ADDRESS2,1,25)||' '||
                    substrb(loc.ADDRESS3,1,25)||' '||substrb(loc.ADDRESS4,1,25)||' '||
                    loc.CITY||  decode(addr.cust_acct_site_id, '', '', ',')
                   ||' '||NVL(loc.STATE, loc.PROVINCE)||' '||
                    loc.COUNTRY C_ADDRESS,
	class.name C_CLASS_NAME,
	SUBSTRB(class.description,1,44) C_CLASS_DESCRIPTION,
	SUBSTRB(cd_stat.meaning,1,12) C_STATUS,
	term.name C_TERMS_NAME,
	DECODE(prof.discount_terms, 'Y', :nls_yes, :nls_no) C_DISC_ALLOWED,
	NVL(prof.discount_grace_days,0) C_DISCOUNT_GRACE_DAYS,
	NVL(prof.payment_grace_days, 0) C_RECEIPT_GRACE_DAYS,
	DECODE(prof.override_terms, 'Y', :nls_yes, :nls_no) C_OVERRIDE_TERMS,
	DECODE(prof.send_statements, 'Y', :nls_yes, :nls_no) C_SEND_STATEMENTS,
	SUBSTRB(cyc.name,1,10) C_CYCLE_NAME,
	DECODE(prof.credit_balance_statements,
		'Y', :nls_yes, :nls_no) C_SEND_CREDIT_STATEMENTS,
	DECODE(prof.interest_charges, 'Y', :nls_yes,:nls_no) C_CHARGE_INTEREST,
	NVL(prof.interest_period_days, 0) C_INT_PERIOD_DAYS,
	DECODE(prof.dunning_letters, 'Y', :nls_yes, :nls_no) C_DUNNING_LETTERS,
	dun.name C_DUN_LETTER_SET,
	auto.hierarchy_name C_AUTOCASH_RULE_SET,
	coll.name C_COLLECTOR,
	SUBSTRB(prof.credit_rating,1,15) C_CREDIT_RATING,
	DECODE(prof.credit_hold, 'Y', :nls_yes, :nls_no) C_CREDIT_HOLD,
	DECODE(prof.credit_checking, 'Y', :nls_yes, :nls_no) C_CHECK_CREDIT,
	NVL(prof.tolerance,0) C_TOLERANCE,
	prof.risk_code C_RISK_CODE,
	NVL(prof.percent_collectable,0) C_PERCENT_COLLECTABLE,
	SUBSTRB(acc_stat.meaning,1,15) C_ACCOUNT_STATUS,
	DECODE(prof.auto_rec_incl_disputed_flag,
	       'Y', :nls_yes, :nls_no) C_REC_INCL_DISPUTED_ITEMS,
	DECODE(prof.charge_on_finance_charge_flag,
	       'Y', :nls_yes, :nls_no) C_COMPOUND_INTEREST,
	tax_printing.meaning C_TAX_PRINTING,
	group_rules.name C_GROUPING_RULE,
                   DECODE(prof.cons_inv_flag,'Y',:nls_yes,:nls_no)  C_CONSINV_FLAG,
                DECODE(prof.cons_inv_flag,'Y',nvl(prof.cons_inv_type,'SUMMARY'),prof.cons_inv_type)
                                                                                                      C_CONSINV_TYPE
FROM	ar_lookups cd_stat,
                  ar_lookups acc_stat,
                  ar_lookups tax_printing,
	ra_grouping_rules group_rules,
                  ar_autocash_hierarchies auto,
	ar_dunning_letter_sets dun,
                  ar_statement_cycles cyc, ra_terms term,
	ar_collectors coll,
                  hz_cust_profile_classes class,
	hz_customer_profiles prof,
                  hz_cust_accounts cust,
                  hz_parties party,
                  hz_cust_site_uses site,
                  hz_cust_acct_sites  addr,
                  hz_party_sites party_site,
                  hz_locations loc
WHERE	cust.account_number BETWEEN
		DECODE(:p_low_cust_num,  'ALL', &cust_default,
					 null, &cust_default,
					 :p_low_cust_num)
  	    AND DECODE(:p_high_cust_num, 'ALL', &cust_default,
		 			 null, &cust_default,
					 :p_high_cust_num)
AND	cust.cust_account_id = prof.cust_account_id
AND         cust.party_id = party.party_id
AND	class.profile_class_id = prof.profile_class_id
AND	coll.collector_id 	= prof.collector_id
AND	cyc.statement_cycle_id(+)	= prof.statement_cycle_id
AND	dun.dunning_letter_set_id(+) = prof.dunning_letter_set_id
AND	auto.autocash_hierarchy_id(+) = prof.autocash_hierarchy_id
AND          group_rules.grouping_rule_id(+) = prof.grouping_rule_id
AND	acc_stat.lookup_type(+)	= 'ACCOUNT_STATUS'
AND	acc_stat.lookup_code(+)    = prof.account_status
AND	cd_stat.lookup_type	= 'CODE_STATUS'
AND	cd_stat.lookup_code	= prof.status
AND          tax_printing.lookup_type(+) = 'TAX_PRINTING_OPTION'
AND          tax_printing.lookup_code(+) = prof.tax_printing_option
AND	term.term_id(+)         = prof.standard_terms
AND          prof.site_use_id = site.site_use_id(+)
AND          site.cust_acct_site_id = addr.cust_acct_site_id(+)
AND          addr.party_site_id = party_site.party_site_id(+)
AND          loc.location_id(+)= party_site.location_id
--ORDER BY cust.account_number,cust.cust_account_id
ORDER BY 3,2,7,8,1,20,33,34,35,36