458: RETURN l_matched_value;
459: END IF;
460: IF p_attribute = 'CITY' THEN
461: BEGIN
462: SELECT HZ_LOCATIONS.CITY
463: INTO l_matched_value
464: FROM HZ_PARTY_SITES, HZ_LOCATIONS
465: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
466: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
460: IF p_attribute = 'CITY' THEN
461: BEGIN
462: SELECT HZ_LOCATIONS.CITY
463: INTO l_matched_value
464: FROM HZ_PARTY_SITES, HZ_LOCATIONS
465: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
466: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
467: EXCEPTION
468: WHEN NO_DATA_FOUND THEN
462: SELECT HZ_LOCATIONS.CITY
463: INTO l_matched_value
464: FROM HZ_PARTY_SITES, HZ_LOCATIONS
465: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
466: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
467: EXCEPTION
468: WHEN NO_DATA_FOUND THEN
469: l_matched_value := 'Err';
470: END;
471: RETURN l_matched_value;
472: END IF;
473: IF p_attribute = 'POSTAL_CODE' THEN
474: BEGIN
475: SELECT HZ_LOCATIONS.POSTAL_CODE
476: INTO l_matched_value
477: FROM HZ_PARTY_SITES, HZ_LOCATIONS
478: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
479: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
473: IF p_attribute = 'POSTAL_CODE' THEN
474: BEGIN
475: SELECT HZ_LOCATIONS.POSTAL_CODE
476: INTO l_matched_value
477: FROM HZ_PARTY_SITES, HZ_LOCATIONS
478: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
479: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
480: EXCEPTION
481: WHEN NO_DATA_FOUND THEN
475: SELECT HZ_LOCATIONS.POSTAL_CODE
476: INTO l_matched_value
477: FROM HZ_PARTY_SITES, HZ_LOCATIONS
478: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
479: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
480: EXCEPTION
481: WHEN NO_DATA_FOUND THEN
482: l_matched_value := 'Err';
483: END;
484: RETURN l_matched_value;
485: END IF;
486: IF p_attribute = 'STATE' THEN
487: BEGIN
488: SELECT HZ_LOCATIONS.STATE
489: INTO l_matched_value
490: FROM HZ_PARTY_SITES, HZ_LOCATIONS
491: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
492: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
486: IF p_attribute = 'STATE' THEN
487: BEGIN
488: SELECT HZ_LOCATIONS.STATE
489: INTO l_matched_value
490: FROM HZ_PARTY_SITES, HZ_LOCATIONS
491: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
492: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
493: EXCEPTION
494: WHEN NO_DATA_FOUND THEN
488: SELECT HZ_LOCATIONS.STATE
489: INTO l_matched_value
490: FROM HZ_PARTY_SITES, HZ_LOCATIONS
491: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
492: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
493: EXCEPTION
494: WHEN NO_DATA_FOUND THEN
495: l_matched_value := 'Err';
496: END;
499: IF p_attribute = 'PARTY_SITE_NUMBER' THEN
500: BEGIN
501: SELECT HZ_PARTY_SITES.PARTY_SITE_NUMBER
502: INTO l_matched_value
503: FROM HZ_PARTY_SITES, HZ_LOCATIONS
504: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
505: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
506: EXCEPTION
507: WHEN NO_DATA_FOUND THEN
501: SELECT HZ_PARTY_SITES.PARTY_SITE_NUMBER
502: INTO l_matched_value
503: FROM HZ_PARTY_SITES, HZ_LOCATIONS
504: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
505: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
506: EXCEPTION
507: WHEN NO_DATA_FOUND THEN
508: l_matched_value := 'Err';
509: END;
510: RETURN l_matched_value;
511: END IF;
512: IF p_attribute = 'COUNTRY' THEN
513: BEGIN
514: SELECT HZ_LOCATIONS.COUNTRY
515: INTO l_matched_value
516: FROM HZ_PARTY_SITES, HZ_LOCATIONS
517: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
518: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
512: IF p_attribute = 'COUNTRY' THEN
513: BEGIN
514: SELECT HZ_LOCATIONS.COUNTRY
515: INTO l_matched_value
516: FROM HZ_PARTY_SITES, HZ_LOCATIONS
517: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
518: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
519: EXCEPTION
520: WHEN NO_DATA_FOUND THEN
514: SELECT HZ_LOCATIONS.COUNTRY
515: INTO l_matched_value
516: FROM HZ_PARTY_SITES, HZ_LOCATIONS
517: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
518: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
519: EXCEPTION
520: WHEN NO_DATA_FOUND THEN
521: l_matched_value := 'Err';
522: END;
6403: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
6404: ) IS
6405: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
6406: SELECT party_site_id
6407: ,translate(HZ_LOCATIONS.CITY, '%',' ')
6408: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
6409: ,translate(HZ_PARTY_SITES.PARTY_SITE_NUMBER, '%',' ')
6410: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6411: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6404: ) IS
6405: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
6406: SELECT party_site_id
6407: ,translate(HZ_LOCATIONS.CITY, '%',' ')
6408: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
6409: ,translate(HZ_PARTY_SITES.PARTY_SITE_NUMBER, '%',' ')
6410: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6411: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6412: FROM HZ_PARTY_SITES, HZ_LOCATIONS
6406: SELECT party_site_id
6407: ,translate(HZ_LOCATIONS.CITY, '%',' ')
6408: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
6409: ,translate(HZ_PARTY_SITES.PARTY_SITE_NUMBER, '%',' ')
6410: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6411: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6412: FROM HZ_PARTY_SITES, HZ_LOCATIONS
6413: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
6414: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
6407: ,translate(HZ_LOCATIONS.CITY, '%',' ')
6408: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
6409: ,translate(HZ_PARTY_SITES.PARTY_SITE_NUMBER, '%',' ')
6410: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6411: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6412: FROM HZ_PARTY_SITES, HZ_LOCATIONS
6413: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
6414: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
6415:
6408: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
6409: ,translate(HZ_PARTY_SITES.PARTY_SITE_NUMBER, '%',' ')
6410: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6411: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6412: FROM HZ_PARTY_SITES, HZ_LOCATIONS
6413: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
6414: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
6415:
6416: I NUMBER;
6410: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6411: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6412: FROM HZ_PARTY_SITES, HZ_LOCATIONS
6413: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
6414: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
6415:
6416: I NUMBER;
6417: J NUMBER:=1;
6418: l_party_site_id NUMBER;