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 |
