556: IF p_attribute = 'CITY' THEN
557: BEGIN
558: SELECT HZ_LOCATIONS.CITY
559: INTO l_matched_value
560: FROM HZ_PARTY_SITES, HZ_LOCATIONS
561: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
562: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
563: EXCEPTION
564: WHEN NO_DATA_FOUND THEN
557: BEGIN
558: SELECT HZ_LOCATIONS.CITY
559: INTO l_matched_value
560: FROM HZ_PARTY_SITES, HZ_LOCATIONS
561: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
562: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
563: EXCEPTION
564: WHEN NO_DATA_FOUND THEN
565: l_matched_value := 'Err';
558: SELECT HZ_LOCATIONS.CITY
559: INTO l_matched_value
560: FROM HZ_PARTY_SITES, HZ_LOCATIONS
561: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
562: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
563: EXCEPTION
564: WHEN NO_DATA_FOUND THEN
565: l_matched_value := 'Err';
566: END;
569: IF p_attribute = 'POSTAL_CODE' THEN
570: BEGIN
571: SELECT HZ_LOCATIONS.POSTAL_CODE
572: INTO l_matched_value
573: FROM HZ_PARTY_SITES, HZ_LOCATIONS
574: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
575: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
576: EXCEPTION
577: WHEN NO_DATA_FOUND THEN
570: BEGIN
571: SELECT HZ_LOCATIONS.POSTAL_CODE
572: INTO l_matched_value
573: FROM HZ_PARTY_SITES, HZ_LOCATIONS
574: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
575: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
576: EXCEPTION
577: WHEN NO_DATA_FOUND THEN
578: l_matched_value := 'Err';
571: SELECT HZ_LOCATIONS.POSTAL_CODE
572: INTO l_matched_value
573: FROM HZ_PARTY_SITES, HZ_LOCATIONS
574: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
575: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
576: EXCEPTION
577: WHEN NO_DATA_FOUND THEN
578: l_matched_value := 'Err';
579: END;
582: IF p_attribute = 'STATE' THEN
583: BEGIN
584: SELECT HZ_LOCATIONS.STATE
585: INTO l_matched_value
586: FROM HZ_PARTY_SITES, HZ_LOCATIONS
587: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
588: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
589: EXCEPTION
590: WHEN NO_DATA_FOUND THEN
583: BEGIN
584: SELECT HZ_LOCATIONS.STATE
585: INTO l_matched_value
586: FROM HZ_PARTY_SITES, HZ_LOCATIONS
587: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
588: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
589: EXCEPTION
590: WHEN NO_DATA_FOUND THEN
591: l_matched_value := 'Err';
584: SELECT HZ_LOCATIONS.STATE
585: INTO l_matched_value
586: FROM HZ_PARTY_SITES, HZ_LOCATIONS
587: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
588: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
589: EXCEPTION
590: WHEN NO_DATA_FOUND THEN
591: l_matched_value := 'Err';
592: END;
595: IF p_attribute = 'COUNTRY' THEN
596: BEGIN
597: SELECT HZ_LOCATIONS.COUNTRY
598: INTO l_matched_value
599: FROM HZ_PARTY_SITES, HZ_LOCATIONS
600: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
601: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
602: EXCEPTION
603: WHEN NO_DATA_FOUND THEN
596: BEGIN
597: SELECT HZ_LOCATIONS.COUNTRY
598: INTO l_matched_value
599: FROM HZ_PARTY_SITES, HZ_LOCATIONS
600: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
601: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
602: EXCEPTION
603: WHEN NO_DATA_FOUND THEN
604: l_matched_value := 'Err';
597: SELECT HZ_LOCATIONS.COUNTRY
598: INTO l_matched_value
599: FROM HZ_PARTY_SITES, HZ_LOCATIONS
600: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
601: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
602: EXCEPTION
603: WHEN NO_DATA_FOUND THEN
604: l_matched_value := 'Err';
605: END;
4893: p_contact_ids HZ_PARTY_SEARCH.IDList;
4894: l_match_idx NUMBER;
4895: cursor get_cpts_for_party_sites is select contact_point_id
4896: from hz_contact_points
4897: where owner_table_name = 'HZ_PARTY_SITES'
4898: and primary_flag='Y'
4899: and owner_table_id = p_party_site_id;
4900: BEGIN
4901: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
6383: l_party_id := p_party_id;
6384: FOR PARTY_SITES IN (
6385: SELECT party_site_id FROM (
6386: SELECT party_site_id,identifying_address_flag
6387: FROM HZ_PARTY_SITES
6388: WHERE party_id = p_party_id
6389: AND (status is null OR status = 'A')
6390: AND identifying_address_flag='Y'
6391: UNION
6390: AND identifying_address_flag='Y'
6391: UNION
6392:
6393: SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
6394: FROM HZ_PARTY_SITES
6395: WHERE party_id = p_party_id
6396: AND (status is null OR status = 'A')
6397: AND (identifying_address_flag IS NULL OR identifying_address_flag = 'N')
6398: AND ROWNUM<6
6398: AND ROWNUM<6
6399: UNION
6400:
6401: SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
6402: FROM HZ_PARTY_SITES
6403: WHERE (status is null OR status = 'A')
6404: AND party_id in (
6405: SELECT party_id
6406: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
6451: AND ROWNUM<6
6452: UNION
6453:
6454: SELECT CONTACT_POINT_ID
6455: FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
6456: WHERE PRIMARY_FLAG = 'Y'
6457: AND owner_table_name = 'HZ_PARTY_SITES'
6458: AND owner_table_id = party_site_id
6459: AND PARTY_ID = p_party_id
6453:
6454: SELECT CONTACT_POINT_ID
6455: FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
6456: WHERE PRIMARY_FLAG = 'Y'
6457: AND owner_table_name = 'HZ_PARTY_SITES'
6458: AND owner_table_id = party_site_id
6459: AND PARTY_ID = p_party_id
6460: AND IDENTIFYING_ADDRESS_FLAG = 'Y') LOOP
6461: l_contact_pt_ids(cpt) := CONTACT_POINTS.CONTACT_POINT_ID;
6617: ,translate(HZ_LOCATIONS.CITY, '%',' ')
6618: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
6619: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6620: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6621: FROM HZ_PARTY_SITES, HZ_LOCATIONS
6622: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
6623: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
6624:
6625: I NUMBER;
6618: ,translate(HZ_LOCATIONS.COUNTRY, '%',' ')
6619: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6620: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6621: FROM HZ_PARTY_SITES, HZ_LOCATIONS
6622: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
6623: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
6624:
6625: I NUMBER;
6626: J NUMBER:=1;
6619: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
6620: ,translate(HZ_LOCATIONS.STATE, '%',' ')
6621: FROM HZ_PARTY_SITES, HZ_LOCATIONS
6622: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
6623: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
6624:
6625: I NUMBER;
6626: J NUMBER:=1;
6627: l_party_site_id NUMBER;