269: l_loc_object_version_number NUMBER := 1;
270:
271: CURSOR c_party_site_use(l_site_use_id NUMBER) IS
272: SELECT object_version_number
273: FROM hz_party_site_uses
274: WHERE party_site_use_id = l_site_use_id
275: ORDER BY party_site_use_id DESC;
276:
277: CURSOR c_get_location_ovn(l_location_id VARCHAR2) IS
1018: --v2
1019: CURSOR c_party_site_use IS
1020: SELECT party_site_use_id, object_version_number
1021: FROM ( SELECT party_site_use_id, object_version_number
1022: FROM hz_party_site_uses
1023: WHERE party_site_id = p_party_site_id
1024: AND site_use_type = p_site_use_type
1025: ORDER BY status, party_site_use_id DESC
1026: )
1276: l_contact_org_id NUMBER;
1277:
1278: cursor c_getPrimAddrId_2(l_party_id NUMBER,l_org_id NUMBER,hr_type VARCHAR2,site_type VARCHAR2) IS
1279: SELECT ps.party_site_id
1280: FROM hz_party_sites ps, hz_party_site_uses psu, hz_locations loc, hr_organization_information hr
1281: WHERE
1282: ps.party_id = l_party_id AND
1283: ps.status = 'A' AND
1284: ps.location_id = loc.location_id AND
1318: );
1319:
1320: cursor c_getPrimAddrId_1(l_party_id NUMBER,l_org_id NUMBER,hr_type VARCHAR2,site_type VARCHAR2) IS
1321: SELECT ps.party_site_id
1322: FROM hz_party_sites ps, hz_party_site_uses psu
1323: WHERE
1324: ps.party_id = l_party_id AND
1325: ps.status = 'A' AND
1326: ps.party_site_id = psu.party_site_id AND
2022: IS
2023:
2024: cursor c_psu is
2025: select party_site_use_id,site_use_type,object_version_number
2026: from hz_party_site_uses
2027: where party_site_id = p_party_site_id
2028: for update nowait;
2029:
2030: l_api_name CONSTANT VARCHAR2(30) := 'delete_address';
2236: --
2237: BEGIN
2238: SELECT party_site_use_id
2239: INTO l_psu_id
2240: FROM ( SELECT distinct party_site_use_id,status FROM hz_party_site_uses
2241: WHERE site_use_type = p_party_site_use.site_use_type
2242: AND party_site_id = p_party_site_use.party_site_id
2243: AND (primary_per_type = 'N' OR primary_per_type = 'Y')
2244: ORDER BY status,party_site_use_id desc)
2263: IBE_UTIL.debug('party_site_use is found');
2264: END IF;
2265: BEGIN
2266: SELECT distinct party_site_use_id INTO l_psu_id
2267: FROM hz_party_site_uses
2268: WHERE NVL(status, 'A') = p_party_site_use.status
2269: AND party_site_use_id = p_party_site_use.party_site_use_id
2270: AND primary_per_type = p_party_site_use.primary_per_type;
2271: EXCEPTION