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;
5286: p_contact_ids HZ_PARTY_SEARCH.IDList;
5287: l_match_idx NUMBER;
5288: cursor get_cpts_for_party_sites is select contact_point_id
5289: from hz_contact_points
5290: where owner_table_name = 'HZ_PARTY_SITES'
5291: and primary_flag='Y'
5292: and owner_table_id = p_party_site_id;
5293: BEGIN
5294: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
6822: l_party_id := p_party_id;
6823: FOR PARTY_SITES IN (
6824: SELECT party_site_id FROM (
6825: SELECT party_site_id,identifying_address_flag
6826: FROM HZ_PARTY_SITES
6827: WHERE party_id = p_party_id
6828: AND (status is null OR status = 'A')
6829: AND identifying_address_flag='Y'
6830: UNION
6829: AND identifying_address_flag='Y'
6830: UNION
6831:
6832: SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
6833: FROM HZ_PARTY_SITES
6834: WHERE party_id = p_party_id
6835: AND (status is null OR status = 'A')
6836: AND (identifying_address_flag IS NULL OR identifying_address_flag = 'N')
6837: AND ROWNUM<6
6837: AND ROWNUM<6
6838: UNION
6839:
6840: SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
6841: FROM HZ_PARTY_SITES
6842: WHERE (status is null OR status = 'A')
6843: AND party_id in (
6844: SELECT party_id
6845: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
6890: AND ROWNUM<6
6891: UNION
6892:
6893: SELECT CONTACT_POINT_ID
6894: FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
6895: WHERE PRIMARY_FLAG = 'Y'
6896: AND owner_table_name = 'HZ_PARTY_SITES'
6897: AND owner_table_id = party_site_id
6898: AND PARTY_ID = p_party_id
6892:
6893: SELECT CONTACT_POINT_ID
6894: FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
6895: WHERE PRIMARY_FLAG = 'Y'
6896: AND owner_table_name = 'HZ_PARTY_SITES'
6897: AND owner_table_id = party_site_id
6898: AND PARTY_ID = p_party_id
6899: AND IDENTIFYING_ADDRESS_FLAG = 'Y') LOOP
6900: l_contact_pt_ids(cpt) := CONTACT_POINTS.CONTACT_POINT_ID;
7074: ,translate(HZ_LOCATIONS.COUNTY, '%',' ')
7075: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7076: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7077: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7078: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7079: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7080: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7081:
7082: I NUMBER;
7075: ,translate(HZ_LOCATIONS.POSTAL_CODE, '%',' ')
7076: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7077: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7078: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7079: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7080: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7081:
7082: I NUMBER;
7083: J NUMBER:=1;
7076: ,translate(HZ_LOCATIONS.PROVINCE, '%',' ')
7077: ,translate(HZ_LOCATIONS.STATE, '%',' ')
7078: FROM HZ_PARTY_SITES, HZ_LOCATIONS
7079: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
7080: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
7081:
7082: I NUMBER;
7083: J NUMBER:=1;
7084: l_party_site_id NUMBER;