Hi,
I have the following query where I am trying to get the ship_to and bill_to locations for customers in our system.
select distinct
decode (hcsua.site_use_code,
'SHIP_TO', hcsua.location) "Customer Ship-To Code",
decode (hcsua.site_use_code,
'BILL_TO', hcsua.location) "Customer Bill-To Code",
hp.party_name "Customer Name",
decode (hcsua.site_use_code,
'SHIP_TO', hl.address1||' '||hl.address2 ||' '|| hl.city||' '|| hl.State||' '|| hl.Country||' '|| hl.postal_code) "Ship-TO Address",
decode (hcsua.site_use_code,
'BILL_TO', hl.address1||' '||hl.address2 ||' '|| hl.city||' '|| hl.State||' '|| hl.Country||' '|| hl.postal_code) "Bill-To Address"
--decode (hcsua.site_use_code,
-- 'BILL_TO', hcsua.attribute4,
-- 'SHIP_TO', hcsua.attribute2) "Broker Code",
from hz_cust_acct_sites_all hcsa,
hz_party_sites hps,
hz_cust_site_uses_all hcsua,
hz_cust_accounts hca,
hz_parties hp,
hz_locations hl
WHERE
hps.party_site_id = hcsa.party_site_id
AND hcsa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hca.cust_account_id = hcsa.cust_account_id
AND hp.party_id = hca.party_id
AND hl.location_id = hps.location_id
and hp.party_id = '1524'
I want to populate bill_to_location everywhere in the bill_to_code so that there is the same bill_to_code for all ship_to locations. Also, I want the bill-to address populated in all rows under the bil_to_address column.
Currently my sample output is like this..
Ship_TO Bill_To Customer Name Ship_To Add. Bill To
07347001 FOOD LION INC (J) FOOD LION INC 2110
07347002 FOOD LION INC (J) FOOD LION INC SNIDER
07347005 FOOD LION INC (J) FOOD LION INC INTERS
07347008 FOOD LION INC (J) FOOD LION INC 400
07347010 FOOD LION INC (J) FOOD LION INC (PA-DC7)
07347012 FOOD LION INC (J) FOOD LION INC 6500
07347014 FOOD LION INC (J) FOOD LION-BUTNER
07347015 FOOD LION INC (J) FOOD LION STORE
07347000 FOOD LION INC (J) P O BOX 519
Thanks
A/A
Edited by: asgar_amin on Apr 8, 2010 8:03 AM