I have got a couple of queries on the otmfaq.com site at: http://www.otmfaq.com/forums/f24/customer-information-616-new/
Thus I’ve provided the queries here:
select 'CUS-'||rac.customer_id customer_XID, rac.customer_name, 'ZAF' customer_country, 'CUS-'||rac.customer_id||'-'||arv.party_location_id customer_location_XID, rac.customer_name||','||substr(arv.city,1,10)||','||substr(arv.province,1,4)||','||nvl((SELECT b.iso_territory_code FROM FND_TERRITORIES_VL b WHERE UPPER(b.territory_code) = UPPER(arv.country)),'TBA') location_name, arv.address1, arv.address2,arv.address3, arv.address4, arv.city, arv.province, arv.postal_code, nvl((SELECT b.iso_territory_code FROM FND_TERRITORIES_VL b WHERE UPPER(b.territory_code) = UPPER(arv.country)),'TBA') country from ra_customers rac left join ar_addresses_v arv on rac.customer_id = arv.customer_id;
select 'CUS-'||rac.customer_id customer_XID, rac.customer_name, 'ZAF' customer_country, 'CUS-'||rac.customer_id||'-'||arv.party_location_id customer_location_XID, rac.customer_name||','||substr(arv.city,1,10)||','||substr(arv.province,1,4)||','||nvl((SELECT b.iso_territory_code FROM FND_TERRITORIES_VL b WHERE UPPER(b.territory_code) = UPPER(arv.country)),'TBA') location_name, nvl(arv.address1,' ') address1, nvl(arv.address2,' ') address2,nvl(arv.address3,' ') address3, nvl(arv.address4,' ') address4, nvl(arv.city,' ') city, nvl(arv.province,' ') province, nvl(arv.postal_code,' ') postal_code, nvl((SELECT b.iso_territory_code FROM FND_TERRITORIES_VL b WHERE UPPER(b.territory_code) = UPPER(arv.country)),'TBA') country from ra_customers rac left join ar_addresses_v arv on rac.customer_id = arv.customer_id;
--Customers
select 'CUS-'||rac.customer_id customer_XID, rac.customer_name, 'ZAF' customer_country from ra_customers rac;
--Customer locations
select 'CUS-'||rac.customer_id||'-'||arv.party_location_id customer_location_XID, rac.customer_name||','||substr(arv.city,1,10)||','||substr(arv.province,1,4)||','||nvl((SELECT b.iso_territory_code FROM FND_TERRITORIES_VL b WHERE UPPER(b.territory_code) = UPPER(arv.country)),'TBA') location_name, arv.address1, arv.address2,arv.address3, arv.address4, arv.city, arv.province, arv.postal_code, nvl((SELECT b.iso_territory_code FROM FND_TERRITORIES_VL b WHERE UPPER(b.territory_code) = UPPER(arv.country)),'TBA') country, 'CUS-'||rac.customer_id customer_parent from ra_customers rac left join ar_addresses_v arv on rac.customer_id = arv.customer_id;
For some of this you will need to set the environment first:
begin dbms_application_info.set_client_info(<your_org_id>); end;
