AR Duplicate Customer

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Duplicate Customer Report (XML)
Short Name: RAXMRP_XML
DB package: AR_RAXMRP_XMLP_PKG
SELECT substr(upper(translate(party.party_name, 'a!&#/\~-$%^,''&*.','a')) , 1,:p_number_of_char)Name,
	substrb(party.party_name,1,50)          	Customer_Name ,
	look.meaning                            		Site_Code ,
	substr(cust.account_number,1,8)         	Customer_Number,
	loc.address1                            		Address ,
	site.location                           		Location ,
	loc.city                                		City ,
	loc.state                               		State ,
	loc.postal_code                         	Postal_Code ,
	terr.territory_short_name               	Country, 
	AR_RAXMRP_XMLP_PKG.c_data_not_foundformula(substr ( upper ( translate ( party.party_name , 'a!&#/\~-$%^,''&*.' , 'a' ) ) , 1 , :p_number_of_char )) C_DATA_NOT_FOUND
FROM
	hz_cust_site_uses               site,
	hz_cust_acct_sites              addr,
	hz_party_sites                     party_site,
	hz_locations                         loc,
	hz_cust_accounts                cust,
	hz_parties                            party,
	fnd_territories_vl                 terr,
	ar_lookups                           look
WHERE cust.cust_account_id = addr.cust_account_id
AND     cust.party_id = party.party_id
AND     addr.party_site_id = party_site.party_site_id
AND     loc.location_id = party_site.location_id
AND     addr.cust_acct_site_id = site.cust_acct_site_id
AND     loc.country =  terr.territory_code
AND     look.lookup_type = 'SITE_USE_CODE'
AND     look.lookup_code = site.site_use_code
and 	1<=
 		(     select  count(hz_parties.party_name)
        		      from     hz_parties ,hz_cust_accounts
	                      where   hz_cust_accounts.party_id = hz_parties.party_id
		      and	  hz_parties.party_id != party.party_id
	                      and   	  length(hz_parties.party_name) >= :p_number_of_char
        	                      and       upper(hz_parties.party_name) like upper(substr(party.party_name,1,:p_number_of_char))||'%'
		)
and upper(substr(translate(party.party_name, 'a!&#/\~-$%^,''&*.','a'),1,:p_number_of_char))
	like upper(substrb(translate(:cp_customer_name, 'a!&#/\~-$%^,''&*.','a'),1,:p_number_of_char)||'%')
and   length(party.party_name) >= :p_number_of_char
order by 1,2,4,3,5,6,7,8,9,10
Ask a question
Parameter Name SQL text Validation
Customer Name
 
LOV Oracle
Number of Characters
 
Number