644: IF p_attribute = 'CITY' THEN
645: BEGIN
646: SELECT HZ_LOCATIONS.CITY
647: INTO l_matched_value
648: FROM HZ_PARTY_SITES, HZ_LOCATIONS
649: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
650: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
651: EXCEPTION
652: WHEN NO_DATA_FOUND THEN
645: BEGIN
646: SELECT HZ_LOCATIONS.CITY
647: INTO l_matched_value
648: FROM HZ_PARTY_SITES, HZ_LOCATIONS
649: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
650: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
651: EXCEPTION
652: WHEN NO_DATA_FOUND THEN
653: l_matched_value := 'Err';
646: SELECT HZ_LOCATIONS.CITY
647: INTO l_matched_value
648: FROM HZ_PARTY_SITES, HZ_LOCATIONS
649: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
650: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
651: EXCEPTION
652: WHEN NO_DATA_FOUND THEN
653: l_matched_value := 'Err';
654: END;
657: IF p_attribute = 'POSTAL_CODE' THEN
658: BEGIN
659: SELECT HZ_LOCATIONS.POSTAL_CODE
660: INTO l_matched_value
661: FROM HZ_PARTY_SITES, HZ_LOCATIONS
662: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
663: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
664: EXCEPTION
665: WHEN NO_DATA_FOUND THEN
658: BEGIN
659: SELECT HZ_LOCATIONS.POSTAL_CODE
660: INTO l_matched_value
661: FROM HZ_PARTY_SITES, HZ_LOCATIONS
662: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
663: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
664: EXCEPTION
665: WHEN NO_DATA_FOUND THEN
666: l_matched_value := 'Err';
659: SELECT HZ_LOCATIONS.POSTAL_CODE
660: INTO l_matched_value
661: FROM HZ_PARTY_SITES, HZ_LOCATIONS
662: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
663: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
664: EXCEPTION
665: WHEN NO_DATA_FOUND THEN
666: l_matched_value := 'Err';
667: END;
670: IF p_attribute = 'PROVINCE' THEN
671: BEGIN
672: SELECT HZ_LOCATIONS.PROVINCE
673: INTO l_matched_value
674: FROM HZ_PARTY_SITES, HZ_LOCATIONS
675: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
676: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
677: EXCEPTION
678: WHEN NO_DATA_FOUND THEN
671: BEGIN
672: SELECT HZ_LOCATIONS.PROVINCE
673: INTO l_matched_value
674: FROM HZ_PARTY_SITES, HZ_LOCATIONS
675: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
676: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
677: EXCEPTION
678: WHEN NO_DATA_FOUND THEN
679: l_matched_value := 'Err';
672: SELECT HZ_LOCATIONS.PROVINCE
673: INTO l_matched_value
674: FROM HZ_PARTY_SITES, HZ_LOCATIONS
675: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
676: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
677: EXCEPTION
678: WHEN NO_DATA_FOUND THEN
679: l_matched_value := 'Err';
680: END;
683: IF p_attribute = 'STATE' THEN
684: BEGIN
685: SELECT HZ_LOCATIONS.STATE
686: INTO l_matched_value
687: FROM HZ_PARTY_SITES, HZ_LOCATIONS
688: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
689: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
690: EXCEPTION
691: WHEN NO_DATA_FOUND THEN
684: BEGIN
685: SELECT HZ_LOCATIONS.STATE
686: INTO l_matched_value
687: FROM HZ_PARTY_SITES, HZ_LOCATIONS
688: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
689: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
690: EXCEPTION
691: WHEN NO_DATA_FOUND THEN
692: l_matched_value := 'Err';
685: SELECT HZ_LOCATIONS.STATE
686: INTO l_matched_value
687: FROM HZ_PARTY_SITES, HZ_LOCATIONS
688: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
689: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
690: EXCEPTION
691: WHEN NO_DATA_FOUND THEN
692: l_matched_value := 'Err';
693: END;
696: IF p_attribute = 'COUNTY' THEN
697: BEGIN
698: SELECT HZ_LOCATIONS.COUNTY
699: INTO l_matched_value
700: FROM HZ_PARTY_SITES, HZ_LOCATIONS
701: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
702: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
703: EXCEPTION
704: WHEN NO_DATA_FOUND THEN
697: BEGIN
698: SELECT HZ_LOCATIONS.COUNTY
699: INTO l_matched_value
700: FROM HZ_PARTY_SITES, HZ_LOCATIONS
701: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
702: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
703: EXCEPTION
704: WHEN NO_DATA_FOUND THEN
705: l_matched_value := 'Err';
698: SELECT HZ_LOCATIONS.COUNTY
699: INTO l_matched_value
700: FROM HZ_PARTY_SITES, HZ_LOCATIONS
701: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
702: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
703: EXCEPTION
704: WHEN NO_DATA_FOUND THEN
705: l_matched_value := 'Err';
706: END;
709: IF p_attribute = 'COUNTRY' THEN
710: BEGIN
711: SELECT HZ_LOCATIONS.COUNTRY
712: INTO l_matched_value
713: FROM HZ_PARTY_SITES, HZ_LOCATIONS
714: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
715: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
716: EXCEPTION
717: WHEN NO_DATA_FOUND THEN
710: BEGIN
711: SELECT HZ_LOCATIONS.COUNTRY
712: INTO l_matched_value
713: FROM HZ_PARTY_SITES, HZ_LOCATIONS
714: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
715: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
716: EXCEPTION
717: WHEN NO_DATA_FOUND THEN
718: l_matched_value := 'Err';
711: SELECT HZ_LOCATIONS.COUNTRY
712: INTO l_matched_value
713: FROM HZ_PARTY_SITES, HZ_LOCATIONS
714: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
715: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
716: EXCEPTION
717: WHEN NO_DATA_FOUND THEN
718: l_matched_value := 'Err';
719: END;
5278: p_contact_ids HZ_PARTY_SEARCH.IDList;
5279: l_match_idx NUMBER;
5280: cursor get_cpts_for_party_sites is select contact_point_id
5281: from hz_contact_points
5282: where owner_table_name = 'HZ_PARTY_SITES'
5283: and primary_flag='Y'
5284: and owner_table_id = p_party_site_id;
5285: BEGIN
5286: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
6811: l_party_id := p_party_id;
6812: FOR PARTY_SITES IN (
6813: SELECT party_site_id FROM (
6814: SELECT party_site_id,identifying_address_flag
6815: FROM HZ_PARTY_SITES
6816: WHERE party_id = p_party_id
6817: AND (status is null OR status = 'A')
6818: AND identifying_address_flag='Y'
6819: UNION
6818: AND identifying_address_flag='Y'
6819: UNION
6820:
6821: SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
6822: FROM HZ_PARTY_SITES
6823: WHERE party_id = p_party_id
6824: AND (status is null OR status = 'A')
6825: AND (identifying_address_flag IS NULL OR identifying_address_flag = 'N')
6826: AND ROWNUM<6
6826: AND ROWNUM<6
6827: UNION
6828:
6829: SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
6830: FROM HZ_PARTY_SITES
6831: WHERE (status is null OR status = 'A')
6832: AND party_id in (
6833: SELECT party_id
6834: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
6879: AND ROWNUM<6
6880: UNION
6881:
6882: SELECT CONTACT_POINT_ID
6883: FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
6884: WHERE PRIMARY_FLAG = 'Y'
6885: AND owner_table_name = 'HZ_PARTY_SITES'
6886: AND owner_table_id = party_site_id
6887: AND PARTY_ID = p_party_id
6881:
6882: SELECT CONTACT_POINT_ID
6883: FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
6884: WHERE PRIMARY_FLAG = 'Y'
6885: AND owner_table_name = 'HZ_PARTY_SITES'
6886: AND owner_table_id = party_site_id
6887: AND PARTY_ID = p_party_id
6888: AND IDENTIFYING_ADDRESS_FLAG = 'Y') LOOP
6889: l_contact_pt_ids(cpt) := CONTACT_POINTS.CONTACT_POINT_ID;
7063: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7064: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7065: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7066: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7067: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7068: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7069: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7070:
7071: I NUMBER;
7064: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7065: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7066: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7067: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7068: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7069: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7070:
7071: I NUMBER;
7072: J NUMBER:=1;
7065: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7066: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7067: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7068: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7069: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7070:
7071: I NUMBER;
7072: J NUMBER:=1;
7073: l_party_site_id NUMBER;