650: END IF;
651: IF p_entity = 'PARTY_SITES' THEN
652: IF p_attribute = 'CITY' THEN
653: BEGIN
654: SELECT HZ_LOCATIONS.CITY
655: INTO l_matched_value
656: FROM HZ_PARTY_SITES, HZ_LOCATIONS
657: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
658: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
652: IF p_attribute = 'CITY' THEN
653: BEGIN
654: SELECT HZ_LOCATIONS.CITY
655: INTO l_matched_value
656: FROM HZ_PARTY_SITES, HZ_LOCATIONS
657: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
658: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
659: EXCEPTION
660: WHEN NO_DATA_FOUND THEN
654: SELECT HZ_LOCATIONS.CITY
655: INTO l_matched_value
656: FROM HZ_PARTY_SITES, HZ_LOCATIONS
657: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
658: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
659: EXCEPTION
660: WHEN NO_DATA_FOUND THEN
661: l_matched_value := 'Err';
662: END;
663: RETURN l_matched_value;
664: END IF;
665: IF p_attribute = 'POSTAL_CODE' THEN
666: BEGIN
667: SELECT HZ_LOCATIONS.POSTAL_CODE
668: INTO l_matched_value
669: FROM HZ_PARTY_SITES, HZ_LOCATIONS
670: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
671: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
665: IF p_attribute = 'POSTAL_CODE' THEN
666: BEGIN
667: SELECT HZ_LOCATIONS.POSTAL_CODE
668: INTO l_matched_value
669: FROM HZ_PARTY_SITES, HZ_LOCATIONS
670: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
671: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
672: EXCEPTION
673: WHEN NO_DATA_FOUND THEN
667: SELECT HZ_LOCATIONS.POSTAL_CODE
668: INTO l_matched_value
669: FROM HZ_PARTY_SITES, HZ_LOCATIONS
670: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
671: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
672: EXCEPTION
673: WHEN NO_DATA_FOUND THEN
674: l_matched_value := 'Err';
675: END;
676: RETURN l_matched_value;
677: END IF;
678: IF p_attribute = 'PROVINCE' THEN
679: BEGIN
680: SELECT HZ_LOCATIONS.PROVINCE
681: INTO l_matched_value
682: FROM HZ_PARTY_SITES, HZ_LOCATIONS
683: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
684: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
678: IF p_attribute = 'PROVINCE' THEN
679: BEGIN
680: SELECT HZ_LOCATIONS.PROVINCE
681: INTO l_matched_value
682: FROM HZ_PARTY_SITES, HZ_LOCATIONS
683: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
684: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
685: EXCEPTION
686: WHEN NO_DATA_FOUND THEN
680: SELECT HZ_LOCATIONS.PROVINCE
681: INTO l_matched_value
682: FROM HZ_PARTY_SITES, HZ_LOCATIONS
683: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
684: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
685: EXCEPTION
686: WHEN NO_DATA_FOUND THEN
687: l_matched_value := 'Err';
688: END;
689: RETURN l_matched_value;
690: END IF;
691: IF p_attribute = 'STATE' THEN
692: BEGIN
693: SELECT HZ_LOCATIONS.STATE
694: INTO l_matched_value
695: FROM HZ_PARTY_SITES, HZ_LOCATIONS
696: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
697: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
691: IF p_attribute = 'STATE' THEN
692: BEGIN
693: SELECT HZ_LOCATIONS.STATE
694: INTO l_matched_value
695: FROM HZ_PARTY_SITES, HZ_LOCATIONS
696: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
697: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
698: EXCEPTION
699: WHEN NO_DATA_FOUND THEN
693: SELECT HZ_LOCATIONS.STATE
694: INTO l_matched_value
695: FROM HZ_PARTY_SITES, HZ_LOCATIONS
696: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
697: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
698: EXCEPTION
699: WHEN NO_DATA_FOUND THEN
700: l_matched_value := 'Err';
701: END;
702: RETURN l_matched_value;
703: END IF;
704: IF p_attribute = 'COUNTY' THEN
705: BEGIN
706: SELECT HZ_LOCATIONS.COUNTY
707: INTO l_matched_value
708: FROM HZ_PARTY_SITES, HZ_LOCATIONS
709: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
710: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
704: IF p_attribute = 'COUNTY' THEN
705: BEGIN
706: SELECT HZ_LOCATIONS.COUNTY
707: INTO l_matched_value
708: FROM HZ_PARTY_SITES, HZ_LOCATIONS
709: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
710: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
711: EXCEPTION
712: WHEN NO_DATA_FOUND THEN
706: SELECT HZ_LOCATIONS.COUNTY
707: INTO l_matched_value
708: FROM HZ_PARTY_SITES, HZ_LOCATIONS
709: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
710: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
711: EXCEPTION
712: WHEN NO_DATA_FOUND THEN
713: l_matched_value := 'Err';
714: END;
715: RETURN l_matched_value;
716: END IF;
717: IF p_attribute = 'COUNTRY' THEN
718: BEGIN
719: SELECT HZ_LOCATIONS.COUNTRY
720: INTO l_matched_value
721: FROM HZ_PARTY_SITES, HZ_LOCATIONS
722: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
723: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
717: IF p_attribute = 'COUNTRY' THEN
718: BEGIN
719: SELECT HZ_LOCATIONS.COUNTRY
720: INTO l_matched_value
721: FROM HZ_PARTY_SITES, HZ_LOCATIONS
722: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
723: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
724: EXCEPTION
725: WHEN NO_DATA_FOUND THEN
719: SELECT HZ_LOCATIONS.COUNTRY
720: INTO l_matched_value
721: FROM HZ_PARTY_SITES, HZ_LOCATIONS
722: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
723: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
724: EXCEPTION
725: WHEN NO_DATA_FOUND THEN
726: l_matched_value := 'Err';
727: END;
7063: x_party_site_list OUT NOCOPY HZ_PARTY_SEARCH.party_site_list
7064: ) IS
7065: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
7066: SELECT party_site_id
7067: ,translate(HZ_LOCATIONS.CITY, '%',' ')
7068: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
7069: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7070: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7071: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7064: ) IS
7065: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
7066: SELECT party_site_id
7067: ,translate(HZ_LOCATIONS.CITY, '%',' ')
7068: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
7069: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7070: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7071: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7072: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7065: CURSOR c_party_sites(cp_party_site_id NUMBER) IS
7066: SELECT party_site_id
7067: ,translate(HZ_LOCATIONS.CITY, '%',' ')
7068: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
7069: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7070: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7071: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7072: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7073: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7066: SELECT party_site_id
7067: ,translate(HZ_LOCATIONS.CITY, '%',' ')
7068: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
7069: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7070: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7071: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7072: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7073: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7074: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7067: ,translate(HZ_LOCATIONS.CITY, '%',' ')
7068: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
7069: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7070: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7071: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7072: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7073: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7074: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7075: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7068: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
7069: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7070: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7071: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7072: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7073: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7074: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7075: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7076:
7069: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7070: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7071: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7072: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7073: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7074: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7075: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7076:
7077: I NUMBER;
7071: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7072: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7073: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7074: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7075: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7076:
7077: I NUMBER;
7078: J NUMBER:=1;
7079: l_party_site_id NUMBER;