[Home] [Help]
1696: /********************* BUG 2085071 Fix ************************/
1697: /* Old Select statement -- Incorrect and hence commented out.
1698: SELECT a.postal_code, a.city, a.state, a.country
1699: INTO l_postal_code, l_city, l_state, l_country
1700: FROM hz_locations a, hz_party_sites s
1701: WHERE a.location_id = s.location_id
1702: AND s.party_site_id = p_customer_site_id;
1703: */
1704:
1721:
1722: SELECT LOC.POSTAL_CODE, LOC.CITY, NVL(LOC.STATE, LOC.PROVINCE), LOC.COUNTRY
1723: INTO l_postal_code, l_city, l_state, l_country
1724: FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
1725: HZ_PARTY_SITES PARTY_SITE,
1726: HZ_LOCATIONS LOC,
1727: HZ_CUST_ACCT_SITES_ALL ACCT_SITE
1728: WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
1729: AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
2254: FROM WSH_REGION_LOCATIONS
2255: WHERE location_id IN
2256: (SELECT LOC.LOCATION_ID
2257: FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
2258: HZ_PARTY_SITES PARTY_SITE,
2259: HZ_LOCATIONS LOC,
2260: HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2261: WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2262: AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
2278: FROM WSH_REGION_LOCATIONS
2279: WHERE location_id IN
2280: (SELECT LOC.LOCATION_ID
2281: FROM HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
2282: HZ_PARTY_SITES PARTY_SITE,
2283: HZ_LOCATIONS LOC,
2284: HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2285: WHERE LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2286: AND PARTY_SITE.party_site_id =ACCT_SITE.party_site_id
2297: (session_id, partner_site_id, region_id, region_type, zone_flag, partner_type)
2298: SELECT p_session_id, p_customer_site_id, wrl.region_id, wrl.region_type, l_NO, l_partner_type --2814895
2299: FROM WSH_REGION_LOCATIONS WRL,
2300: HZ_CUST_SITE_USES_ALL SITE_USES_ALL,
2301: HZ_PARTY_SITES PARTY_SITE,
2302: HZ_LOCATIONS LOC,
2303: HZ_CUST_ACCT_SITES_ALL ACCT_SITE
2304: WHERE WRL.location_id = LOC.LOCATION_ID
2305: AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
2320: INSERT INTO msc_regions_temp
2321: (session_id,partner_site_id,region_id,region_type,zone_flag, partner_type) --2814895
2322: SELECT p_session_id,p_party_site_id,wrl.region_id,wrl.region_type,l_NO,l_partner_type
2323: FROM WSH_REGION_LOCATIONS WRL,
2324: HZ_PARTY_SITES PARTY_SITE
2325: WHERE WRL.location_id = PARTY_SITE.LOCATION_ID
2326: AND PARTY_SITE.party_site_id = p_party_site_id
2327: AND WRL.location_source = l_HZ
2328: AND WRL.region_id is not null;