350: RETURN l_matched_value;
351: END IF;
352: IF p_attribute = 'CITY' THEN
353: BEGIN
354: SELECT HZ_LOCATIONS.CITY
355: INTO l_matched_value
356: FROM HZ_PARTY_SITES, HZ_LOCATIONS
357: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
358: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
352: IF p_attribute = 'CITY' THEN
353: BEGIN
354: SELECT HZ_LOCATIONS.CITY
355: INTO l_matched_value
356: FROM HZ_PARTY_SITES, HZ_LOCATIONS
357: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
358: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
359: EXCEPTION
360: WHEN NO_DATA_FOUND THEN
354: SELECT HZ_LOCATIONS.CITY
355: INTO l_matched_value
356: FROM HZ_PARTY_SITES, HZ_LOCATIONS
357: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
358: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
359: EXCEPTION
360: WHEN NO_DATA_FOUND THEN
361: l_matched_value := 'Err';
362: END;
363: RETURN l_matched_value;
364: END IF;
365: IF p_attribute = 'POSTAL_CODE' THEN
366: BEGIN
367: SELECT HZ_LOCATIONS.POSTAL_CODE
368: INTO l_matched_value
369: FROM HZ_PARTY_SITES, HZ_LOCATIONS
370: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
371: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
365: IF p_attribute = 'POSTAL_CODE' THEN
366: BEGIN
367: SELECT HZ_LOCATIONS.POSTAL_CODE
368: INTO l_matched_value
369: FROM HZ_PARTY_SITES, HZ_LOCATIONS
370: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
371: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
372: EXCEPTION
373: WHEN NO_DATA_FOUND THEN
367: SELECT HZ_LOCATIONS.POSTAL_CODE
368: INTO l_matched_value
369: FROM HZ_PARTY_SITES, HZ_LOCATIONS
370: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
371: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
372: EXCEPTION
373: WHEN NO_DATA_FOUND THEN
374: l_matched_value := 'Err';
375: END;
376: RETURN l_matched_value;
377: END IF;
378: IF p_attribute = 'PROVINCE' THEN
379: BEGIN
380: SELECT HZ_LOCATIONS.PROVINCE
381: INTO l_matched_value
382: FROM HZ_PARTY_SITES, HZ_LOCATIONS
383: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
384: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
378: IF p_attribute = 'PROVINCE' THEN
379: BEGIN
380: SELECT HZ_LOCATIONS.PROVINCE
381: INTO l_matched_value
382: FROM HZ_PARTY_SITES, HZ_LOCATIONS
383: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
384: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
385: EXCEPTION
386: WHEN NO_DATA_FOUND THEN
380: SELECT HZ_LOCATIONS.PROVINCE
381: INTO l_matched_value
382: FROM HZ_PARTY_SITES, HZ_LOCATIONS
383: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
384: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
385: EXCEPTION
386: WHEN NO_DATA_FOUND THEN
387: l_matched_value := 'Err';
388: END;
389: RETURN l_matched_value;
390: END IF;
391: IF p_attribute = 'STATE' THEN
392: BEGIN
393: SELECT HZ_LOCATIONS.STATE
394: INTO l_matched_value
395: FROM HZ_PARTY_SITES, HZ_LOCATIONS
396: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
397: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
391: IF p_attribute = 'STATE' THEN
392: BEGIN
393: SELECT HZ_LOCATIONS.STATE
394: INTO l_matched_value
395: FROM HZ_PARTY_SITES, HZ_LOCATIONS
396: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
397: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
398: EXCEPTION
399: WHEN NO_DATA_FOUND THEN
393: SELECT HZ_LOCATIONS.STATE
394: INTO l_matched_value
395: FROM HZ_PARTY_SITES, HZ_LOCATIONS
396: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
397: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
398: EXCEPTION
399: WHEN NO_DATA_FOUND THEN
400: l_matched_value := 'Err';
401: END;
402: RETURN l_matched_value;
403: END IF;
404: IF p_attribute = 'COUNTY' THEN
405: BEGIN
406: SELECT HZ_LOCATIONS.COUNTY
407: INTO l_matched_value
408: FROM HZ_PARTY_SITES, HZ_LOCATIONS
409: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
410: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
404: IF p_attribute = 'COUNTY' THEN
405: BEGIN
406: SELECT HZ_LOCATIONS.COUNTY
407: INTO l_matched_value
408: FROM HZ_PARTY_SITES, HZ_LOCATIONS
409: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
410: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
411: EXCEPTION
412: WHEN NO_DATA_FOUND THEN
406: SELECT HZ_LOCATIONS.COUNTY
407: INTO l_matched_value
408: FROM HZ_PARTY_SITES, HZ_LOCATIONS
409: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
410: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
411: EXCEPTION
412: WHEN NO_DATA_FOUND THEN
413: l_matched_value := 'Err';
414: END;
415: RETURN l_matched_value;
416: END IF;
417: IF p_attribute = 'COUNTRY' THEN
418: BEGIN
419: SELECT HZ_LOCATIONS.COUNTRY
420: INTO l_matched_value
421: FROM HZ_PARTY_SITES, HZ_LOCATIONS
422: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
423: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
417: IF p_attribute = 'COUNTRY' THEN
418: BEGIN
419: SELECT HZ_LOCATIONS.COUNTRY
420: INTO l_matched_value
421: FROM HZ_PARTY_SITES, HZ_LOCATIONS
422: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
423: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
424: EXCEPTION
425: WHEN NO_DATA_FOUND THEN
419: SELECT HZ_LOCATIONS.COUNTRY
420: INTO l_matched_value
421: FROM HZ_PARTY_SITES, HZ_LOCATIONS
422: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
423: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
424: EXCEPTION
425: WHEN NO_DATA_FOUND THEN
426: l_matched_value := 'Err';
427: END;
5645: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
5646: ) IS
5647: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5648: SELECT party_site_id
5649: ,translate(HZ_LOCATIONS.CITY, '%',' ')
5650: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
5651: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
5652: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5653: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
5646: ) IS
5647: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5648: SELECT party_site_id
5649: ,translate(HZ_LOCATIONS.CITY, '%',' ')
5650: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
5651: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
5652: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5653: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
5654: ,translate(HZ_LOCATIONS.STATE, '%',' ')
5647: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
5648: SELECT party_site_id
5649: ,translate(HZ_LOCATIONS.CITY, '%',' ')
5650: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
5651: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
5652: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5653: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
5654: ,translate(HZ_LOCATIONS.STATE, '%',' ')
5655: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5648: SELECT party_site_id
5649: ,translate(HZ_LOCATIONS.CITY, '%',' ')
5650: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
5651: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
5652: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5653: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
5654: ,translate(HZ_LOCATIONS.STATE, '%',' ')
5655: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5656: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
5649: ,translate(HZ_LOCATIONS.CITY, '%',' ')
5650: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
5651: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
5652: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5653: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
5654: ,translate(HZ_LOCATIONS.STATE, '%',' ')
5655: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5656: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
5657: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
5650: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
5651: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
5652: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5653: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
5654: ,translate(HZ_LOCATIONS.STATE, '%',' ')
5655: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5656: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
5657: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
5658:
5651: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
5652: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
5653: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
5654: ,translate(HZ_LOCATIONS.STATE, '%',' ')
5655: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5656: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
5657: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
5658:
5659: I NUMBER;
5653: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
5654: ,translate(HZ_LOCATIONS.STATE, '%',' ')
5655: FROM HZ_PARTY_SITES, HZ_LOCATIONS
5656: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
5657: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
5658:
5659: I NUMBER;
5660: J NUMBER:=1;
5661: l_party_site_id NUMBER;