373: IF p_attribute = 'CITY' THEN
374: BEGIN
375: SELECT HZ_LOCATIONS.CITY
376: INTO l_matched_value
377: FROM HZ_PARTY_SITES, HZ_LOCATIONS
378: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
379: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
380: EXCEPTION
381: WHEN NO_DATA_FOUND THEN
374: BEGIN
375: SELECT HZ_LOCATIONS.CITY
376: INTO l_matched_value
377: FROM HZ_PARTY_SITES, HZ_LOCATIONS
378: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
379: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
380: EXCEPTION
381: WHEN NO_DATA_FOUND THEN
382: l_matched_value := 'Err';
375: SELECT HZ_LOCATIONS.CITY
376: INTO l_matched_value
377: FROM HZ_PARTY_SITES, HZ_LOCATIONS
378: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
379: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
380: EXCEPTION
381: WHEN NO_DATA_FOUND THEN
382: l_matched_value := 'Err';
383: END;
386: IF p_attribute = 'STATE' THEN
387: BEGIN
388: SELECT HZ_LOCATIONS.STATE
389: INTO l_matched_value
390: FROM HZ_PARTY_SITES, HZ_LOCATIONS
391: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
392: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
393: EXCEPTION
394: WHEN NO_DATA_FOUND THEN
387: BEGIN
388: SELECT HZ_LOCATIONS.STATE
389: INTO l_matched_value
390: FROM HZ_PARTY_SITES, HZ_LOCATIONS
391: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
392: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
393: EXCEPTION
394: WHEN NO_DATA_FOUND THEN
395: l_matched_value := 'Err';
388: SELECT HZ_LOCATIONS.STATE
389: INTO l_matched_value
390: FROM HZ_PARTY_SITES, HZ_LOCATIONS
391: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
392: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
393: EXCEPTION
394: WHEN NO_DATA_FOUND THEN
395: l_matched_value := 'Err';
396: END;
399: IF p_attribute = 'COUNTRY' THEN
400: BEGIN
401: SELECT HZ_LOCATIONS.COUNTRY
402: INTO l_matched_value
403: FROM HZ_PARTY_SITES, HZ_LOCATIONS
404: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
405: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
406: EXCEPTION
407: WHEN NO_DATA_FOUND THEN
400: BEGIN
401: SELECT HZ_LOCATIONS.COUNTRY
402: INTO l_matched_value
403: FROM HZ_PARTY_SITES, HZ_LOCATIONS
404: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
405: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
406: EXCEPTION
407: WHEN NO_DATA_FOUND THEN
408: l_matched_value := 'Err';
401: SELECT HZ_LOCATIONS.COUNTRY
402: INTO l_matched_value
403: FROM HZ_PARTY_SITES, HZ_LOCATIONS
404: WHERE HZ_PARTY_SITES.party_site_id = p_record_id
405: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id and rownum=1;
406: EXCEPTION
407: WHEN NO_DATA_FOUND THEN
408: l_matched_value := 'Err';
409: END;
2767: p_contact_ids HZ_PARTY_SEARCH.IDList;
2768: l_match_idx NUMBER;
2769: cursor get_cpts_for_party_sites is select contact_point_id
2770: from hz_contact_points
2771: where owner_table_name = 'HZ_PARTY_SITES'
2772: and primary_flag='Y'
2773: and owner_table_id = p_party_site_id;
2774: BEGIN
2775: IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
4184: l_party_id := p_party_id;
4185: FOR PARTY_SITES IN (
4186: SELECT party_site_id FROM (
4187: SELECT party_site_id,identifying_address_flag
4188: FROM HZ_PARTY_SITES
4189: WHERE party_id = p_party_id
4190: AND (status is null OR status = 'A')
4191: AND identifying_address_flag='Y'
4192: UNION
4191: AND identifying_address_flag='Y'
4192: UNION
4193:
4194: SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
4195: FROM HZ_PARTY_SITES
4196: WHERE party_id = p_party_id
4197: AND (status is null OR status = 'A')
4198: AND (identifying_address_flag IS NULL OR identifying_address_flag = 'N')
4199: UNION
4198: AND (identifying_address_flag IS NULL OR identifying_address_flag = 'N')
4199: UNION
4200:
4201: SELECT party_site_id,NVL(identifying_address_flag,'N') identifying_address_flag
4202: FROM HZ_PARTY_SITES
4203: WHERE (status is null OR status = 'A')
4204: AND party_id in (
4205: SELECT party_id
4206: FROM HZ_ORG_CONTACTS, HZ_RELATIONSHIPS
4236: and l_use_contact_info = 'Y'
4237: UNION
4238:
4239: SELECT CONTACT_POINT_ID
4240: FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
4241: WHERE PRIMARY_FLAG = 'Y'
4242: AND owner_table_name = 'HZ_PARTY_SITES'
4243: AND owner_table_id = party_site_id
4244: AND PARTY_ID = p_party_id
4238:
4239: SELECT CONTACT_POINT_ID
4240: FROM HZ_CONTACT_POINTS,HZ_PARTY_SITES
4241: WHERE PRIMARY_FLAG = 'Y'
4242: AND owner_table_name = 'HZ_PARTY_SITES'
4243: AND owner_table_id = party_site_id
4244: AND PARTY_ID = p_party_id
4245: AND IDENTIFYING_ADDRESS_FLAG = 'Y') LOOP
4246: l_contact_pt_ids(cpt) := CONTACT_POINTS.CONTACT_POINT_ID;
4386: ,HZ_LOCATIONS.CITY
4387: ,HZ_LOCATIONS.COUNTRY
4388: ,HZ_LOCATIONS.POSTAL_CODE
4389: ,HZ_LOCATIONS.STATE
4390: FROM HZ_PARTY_SITES, HZ_LOCATIONS
4391: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
4392: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
4393:
4394: I NUMBER;
4387: ,HZ_LOCATIONS.COUNTRY
4388: ,HZ_LOCATIONS.POSTAL_CODE
4389: ,HZ_LOCATIONS.STATE
4390: FROM HZ_PARTY_SITES, HZ_LOCATIONS
4391: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
4392: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
4393:
4394: I NUMBER;
4395: J NUMBER:=1;
4388: ,HZ_LOCATIONS.POSTAL_CODE
4389: ,HZ_LOCATIONS.STATE
4390: FROM HZ_PARTY_SITES, HZ_LOCATIONS
4391: WHERE HZ_PARTY_SITES.party_site_id = cp_party_site_id
4392: AND HZ_PARTY_SITES.location_id = HZ_LOCATIONS.location_id;
4393:
4394: I NUMBER;
4395: J NUMBER:=1;
4396: l_party_site_id NUMBER;