226: SELECT wc.carrier_name Company_Name,
227: wc.carrier_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
228: hps.creation_date Cdate,
229: 'CARRIER' Company_Type
230: FROM wsh_locations wl, hz_party_sites hps,
231: wsh_carriers_v wc
232: WHERE wl.source_location_id = p_location_id
233: AND wl.location_source_code = 'HZ'
234: AND wl.source_location_id = hps.location_id
239: SELECT hp.party_name Company_Name,
240: hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
241: hps.creation_date Cdate,
242: 'CUSTOMER' Company_Type
243: FROM wsh_locations wl, hz_party_sites hps, hz_parties hp,
244: hz_cust_acct_sites_all hcas
245: WHERE wl.source_location_id = p_location_id
246: AND wl.location_source_code = 'HZ'
247: AND wl.source_location_id = hps.location_id
254: hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
255: hps.creation_date Cdate,
256: 'SUPPLIER' Company_Type
257: FROM wsh_locations wl, po_vendors po, hz_relationships rel,
258: hz_party_sites hps, hz_parties hp
259: WHERE wl.source_location_id = p_location_id
260: AND wl.source_location_id = hps.location_id
261: AND hps.party_id = hp.party_id
262: AND hp.status='A'
365: wl.wsh_location_id LocId,
366: wc.carrier_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
367: hps.creation_date cdate,
368: 'CARRIER' company_type
369: FROM wsh_locations wl, hz_party_sites hps, wsh_carriers_v wc,
370: fte_location_parameters flp
371: WHERE wl.wsh_location_id = flp.location_id(+)
372: AND flp.location_id IS NULL
373: AND wl.location_source_code = 'HZ'
380: wl.wsh_location_id LocId,
381: hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
382: hps.creation_date Cdate,
383: 'CUSTOMER' Company_Type
384: FROM wsh_locations wl, hz_party_sites hps, hz_parties hp,
385: hz_cust_acct_sites_all hcas, fte_location_parameters flp,
386: wsh_location_owners wlo
387: WHERE wl.wsh_location_id = flp.location_id(+)
388: AND wl.wsh_location_id = wlo.wsh_location_id
400: wl.wsh_location_id LocId,
401: hp.party_name||'_'||nvl(hps.party_site_name,hps.party_site_number) Site,
402: hps.creation_date Cdate, 'SUPPLIER' Company_Type
403: FROM wsh_locations wl, po_vendors po, hz_relationships rel,
404: hz_party_sites hps, hz_parties hp, fte_location_parameters flp
405: WHERE wl.wsh_location_id = flp.location_id(+)
406: AND flp.location_id IS NULL
407: AND wl.source_location_id = hps.location_id
408: AND hp.status='A'
943: BEGIN
944: DELETE FROM FTE_LOCATION_PARAMETERS
945: WHERE location_id IN (
946: SELECT wl.wsh_location_id WSH_LOCATION_ID
947: FROM hz_party_sites hps,
948: hz_cust_acct_sites_all hcas,
949: hz_cust_site_uses_all hcsu, po_location_associations_all pla,
950: wsh_locations wl
951: WHERE pla.customer_id = hcas.cust_account_id