[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_rec_update_flag varchar(1) := 'N';
select rented_list_flag, nvl(RECORD_UPDATE_FLAG,'N') from ams_imp_list_headers_all
where import_list_header_id = p_import_list_header_id;
select ORG_IMP_XML_ELEMENT_ID from ams_hz_b2b_mapping_v
where import_source_line_id = i_import_source_line_id;
select PER_IMP_XML_ELEMENT_ID from ams_hz_b2c_mapping_v
where import_source_line_id = i_import_source_line_id;
SELECT party_id, relationship_id FROM hz_relationships
WHERE object_id = x_org_party_id
AND subject_id = x_per_party_id
AND subject_table_name = 'HZ_PARTIES'
AND subject_type = 'PERSON'
AND object_type = 'ORGANIZATION'
AND object_table_name = 'HZ_PARTIES'
-- sranka 3/4/2003
-- made changes for supporting EMPLOYEE_OF" relationship
-- AND relationship_code = 'CONTACT_OF';
SELECT 'Y' FROM hz_parties
WHERE party_type = 'PARTY_RELATIONSHIP'
and status = 'A'
AND party_id = l_b2b_party_id;
SELECT 'Y' FROM hz_parties
WHERE party_type = 'PERSON'
and status = 'A'
AND party_id = l_b2c_party_id;
SELECT object_id FROM hz_relationships
WHERE subject_type = 'PERSON'
AND subject_table_name = 'HZ_PARTIES'
AND object_type = 'ORGANIZATION'
AND object_table_name = 'HZ_PARTIES'
-- sranka 3/4/2003
-- made changes for supporting EMPLOYEE_OF" relationship
-- AND relationship_code = 'CONTACT_OF';
SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_org_party_id
AND location_id = x_location_id;
SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_org_party_id
AND location_id = x_org_location_id;
SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_per_party_id
AND location_id = x_location_id;
SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_party_rel_party_id
AND location_id = x_location_id;
SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = x_phone_type
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
and transposed_phone_number = l_transposed_phone_no;
SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = x_org_phone_type
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
and transposed_phone_number = l_org_transposed_phone_no;
SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = x_org_ps_phone_type
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
and transposed_phone_number = l_org_ps_transposed_phone_no;
SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = 'FAX'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
AND phone_number = x_fax_number
AND NVL(phone_country_code,'x') = NVL(x_fax_country_code,'x')
AND NVL(phone_area_code,'x') = NVL(x_fax_area_code,'x');
SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type = 'WEB'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
AND url = x_url;
SELECT contact_point_id FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = x_phone_type
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
and transposed_phone_number = l_transposed_phone_no;
SELECT contact_point_id FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = x_org_phone_type
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
and transposed_phone_number = l_org_transposed_phone_no;
SELECT contact_point_id FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = x_org_ps_phone_type
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
and transposed_phone_number = l_org_ps_transposed_phone_no;
SELECT contact_point_id FROM hz_contact_points
WHERE contact_point_type = 'WEB'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
AND url = x_url;
SELECT contact_point_id FROM hz_contact_points
WHERE contact_point_type = 'PHONE'
AND phone_line_type = 'FAX'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
AND phone_number = x_fax_number
AND NVL(phone_country_code,'x') = NVL(x_fax_country_code,'x')
AND NVL(phone_area_code,'x') = NVL(x_fax_area_code,'x');
SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type = 'EMAIL'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
AND upper(email_address) = upper(x_email_address);
SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type = 'EMAIL'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
AND upper(email_address) = upper(x_org_email_address);
SELECT OBJECT_VERSION_NUMBER FROM hz_relationships
WHERE RELATIONSHIP_ID = nvl(x_party_relationship_id,l_rel_id)
and subject_type = 'PERSON';
SELECT OBJECT_VERSION_NUMBER FROM hz_org_contacts
WHERE PARTY_RELATIONSHIP_ID = nvl(x_party_relationship_id,l_rel_id);
SELECT OBJECT_VERSION_NUMBER FROM hz_parties
WHERE PARTY_ID = x_party_rel_party_id;
SELECT contact_point_id FROM hz_contact_points
WHERE contact_point_type = 'EMAIL'
AND owner_table_name = 'HZ_PARTIES'
AND owner_table_id = x_hz_party_id
AND email_address = x_email_address;
select ORG_PARTY_ID,OCONT_PARTY_ID,PARTY_LOCATION_ID,ORG_KEY,org_location_id
from ams_hz_b2b_mapping_v
where import_source_line_id = i_import_source_line_id;
select person_PARTY_ID,PARTY_LOCATION_ID
from ams_hz_b2c_mapping_v
where import_source_line_id = i_import_source_line_id;
select party_id from hz_parties
where customer_key = src_org_key
and status = 'A'
and party_type = 'ORGANIZATION';
select 1 from hz_parties
where party_id = p_party_id
and party_type in ('PARTY_RELATIONSHIP','ORGANIZATION')
and status = 'A';
select 1 from hz_parties
where party_id = p_party_id -- bug 5100612 mayjain
and party_type in ('PERSON')
and status = 'A';
FETCH c_rented into x_rented_list_flag, l_rec_update_flag;
select hz_party_number_s.nextval into x_party_number from dual;
select hz_parties_s.nextval into x_org_party_id from dual;
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_org_party_id is NULL)) then
x_party_number := null;
select hz_party_number_s.nextval into x_party_number from dual;
select hz_parties_s.nextval into x_org_party_id from dual;
if l_rec_update_flag = 'Y' and x_org_party_id is not NULL then
select OBJECT_VERSION_NUMBER into l_party_obj_number
from hz_parties
where party_id = x_org_party_id;
hz_party_v2pub.update_organization(
'F',
org_rec,
l_party_obj_number,
x_organization_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
select hz_party_number_s.nextval into x_party_number from dual;
select hz_parties_s.nextval into x_per_party_id from dual;
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_per_party_id is NULL)) then
if x_generate_party_number = 'N' then
select hz_party_number_s.nextval into x_party_number from dual;
select hz_parties_s.nextval into x_per_party_id from dual;
if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
person_rec.party_rec.party_id := x_per_party_id;
select OBJECT_VERSION_NUMBER into l_party_obj_number
from hz_parties
where party_id = x_per_party_id;
hz_party_v2pub.update_person(
'F',
person_rec,
l_party_obj_number,
x_person_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
Select hz_org_contacts_s.nextval into x_org_contact_id from dual;
select hz_party_number_s.nextval into x_party_number from dual;
select hz_contact_numbers_s.nextval into x_contact_number from dual;
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and p_pr_party_id is NULL)) then
hz_party_contact_v2pub.create_org_contact(
'F',
ocon_rec,
x_org_contact_id,
x_party_relationship_id,
x_party_rel_party_id,
x_party_number,
x_return_status,
x_msg_count,
x_msg_data);
if l_rec_update_flag = 'Y' and p_pr_party_id is not NULL then
select ORG_CONTACT_ID into x_org_contact_id from hz_org_contacts
where PARTY_RELATIONSHIP_ID = l_rel_id ; -- x_party_rel_party_id;
select OBJECT_VERSION_NUMBER into l_party_obj_number
from hz_parties
where party_id = x_org_party_id;
select OBJECT_VERSION_NUMBER into l_pr_obj_number
from hz_relationships
where relationship_id = l_rel_id and directional_flag = 'F'; -- x_party_rel_party_id;
select OBJECT_VERSION_NUMBER into l_con_obj_number
from hz_org_contacts
where ORG_CONTACT_ID = x_org_contact_id;
hz_party_contact_v2pub.update_org_contact(
'F',
ocon_rec,
l_con_obj_number,
l_pr_obj_number,
l_party_obj_number,
x_return_status,
x_msg_count,
x_msg_data);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_language_use_reference_id is NULL)) then
HZ_PERSON_INFO_V2PUB.create_person_language(
FND_API.G_FALSE,
language_rec,
x_language_use_reference_id,
x_return_status,
x_msg_count,
x_msg_data
);
if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
select OBJECT_VERSION_NUMBER,language_use_reference_id into l_language_obj_number,l_language_use_reference_id
from hz_person_language
where party_id = x_per_party_id
AND native_language = 'Y';
HZ_PERSON_INFO_V2PUB.update_person_language(
FND_API.G_FALSE,
language_rec,
l_language_obj_number,
x_return_status ,
x_msg_count ,
x_msg_data
);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_org_location_id is NULL)) then
l_address_key :=hz_fuzzy_pub.Generate_Key (
p_key_type => 'ADDRESS',
p_address1 => org_location_rec.address1,
p_postal_code => org_location_rec.postal_code);
select count(*) INTO l_address_key_count from hz_locations where address_key=l_address_key;
if l_rec_update_flag = 'Y' and x_org_location_id is not NULL then
select OBJECT_VERSION_NUMBER into l_loc_obj_number
from hz_locations
where location_id = x_org_location_id;
hz_location_v2pub.update_location(
'F',
org_location_rec,
l_loc_obj_number,
x_return_status,
x_msg_count,
x_msg_data
);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_org_lp_psite_id is NULL)) then
AMS_ListImport_PVT.create_party_site(
org_psite_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_org_party_site_id, -- sranka
x_party_site_number
);
if l_rec_update_flag = 'Y' and l_org_lp_psite_id is not NULL then
org_psite_rec.party_site_id := l_org_lp_psite_id;
select OBJECT_VERSION_NUMBER into l_ps_obj_number
from hz_party_sites
where party_site_id = l_org_lp_psite_id;
hz_party_site_v2pub.update_party_site(
'F',
org_psite_rec,
l_ps_obj_number,
x_return_status,
x_msg_count,
x_msg_data
);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_location_id is NULL)) then
AMS_ListImport_PVT.create_location (
location_rec ,
x_return_status ,
x_msg_count ,
x_msg_data ,
x_location_id );
if l_rec_update_flag = 'Y' and x_location_id is not NULL then
select OBJECT_VERSION_NUMBER into l_loc_obj_number
from hz_locations
where location_id = x_location_id;
hz_location_v2pub.update_location(
'F',
location_rec,
l_loc_obj_number,
x_return_status,
x_msg_count,
x_msg_data
);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_lp_psite_id is NULL)) then
AMS_ListImport_PVT.create_party_site(
psite_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_party_site_id,
x_party_site_number
);
if l_rec_update_flag = 'Y' and l_lp_psite_id is not NULL then
psite_rec.party_site_id := l_lp_psite_id;
select OBJECT_VERSION_NUMBER into l_ps_obj_number
from hz_party_sites
where party_site_id = l_lp_psite_id;
hz_party_site_v2pub.update_party_site(
'F',
psite_rec,
l_ps_obj_number,
x_return_status,
x_msg_count,
x_msg_data
);
hz_party_contact_v2pub.update_org_contact(
'F',
ocon_rec,
l_object_version1,
l_object_version2,
l_object_version3,
x_return_status,
x_msg_count,
x_msg_data);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_email_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
phone_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_phone_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
org_party_site_phone_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_phone_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
phone_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_phone_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
phone_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_fax_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
fax_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_url_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
fax_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_email_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
phone_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if ((x_phone_number is not NULL OR x_email_address is not NULL ) and l_rec_update_flag = 'Y' and x_party_rel_party_id is not null) then
phone_rec.phone_line_type := 'GEN';
select OBJECT_VERSION_NUMBER into l_cp_obj_number
from hz_contact_points
where contact_point_id = x_contact_point_id;
hz_contact_point_v2pub.update_contact_point(
'F',
cpoint_rec,
edi_rec,
email_rec,
phone_rec,
telex_rec,
web_rec,
l_cp_obj_number,
x_return_status,
x_msg_count,
x_msg_data);
UPDATE ams_imp_source_lines
SET party_id = i_party_id,
organization_id = x_org_party_id,
load_status = 'SUCCESS',
contact_point_id = l_phone_id,
location_id = x_location_id,
enabled_flag = l_enabled_flag
-- WHERE import_source_line_id = org_rec.party_rec.orig_system_reference
-- sranka 1/14/2003
WHERE import_source_line_id = l_import_source_line_id
AND import_list_header_id = p_import_list_header_id;
select hz_party_number_s.nextval into x_party_number from dual;
select hz_parties_s.nextval into x_per_party_id from dual;
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_per_party_id is NULL)) then
if x_generate_party_number = 'N' then
select hz_party_number_s.nextval into x_party_number from dual;
select hz_parties_s.nextval into x_per_party_id from dual;
if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
person_rec.party_rec.party_id := x_per_party_id;
select OBJECT_VERSION_NUMBER into l_party_obj_number
from hz_parties
where party_id = x_per_party_id;
hz_party_v2pub.update_person(
'F',
person_rec,
l_party_obj_number,
x_person_profile_id,
x_return_status,
x_msg_count,
x_msg_data
);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_location_id is NULL)) then
HZ_PERSON_INFO_V2PUB.create_person_language(
FND_API.G_FALSE,
language_rec,
x_language_use_reference_id,
x_return_status,
x_msg_count,
x_msg_data
);
if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
IF person_rec.person_first_name IS NOT NULL AND language_rec.language_name is not NULL then
select OBJECT_VERSION_NUMBER,language_use_reference_id into l_language_obj_number,l_language_use_reference_id
from hz_person_language
where party_id = x_per_party_id
AND native_language = 'Y';
HZ_PERSON_INFO_V2PUB.update_person_language(
FND_API.G_FALSE,
language_rec,
l_language_obj_number,
x_return_status ,
x_msg_count ,
x_msg_data
);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_location_id is NULL)) then
AMS_ListImport_PVT.create_location (
location_rec ,
x_return_status ,
x_msg_count ,
x_msg_data ,
x_location_id );
if l_rec_update_flag = 'Y' and x_location_id is not NULL then
select OBJECT_VERSION_NUMBER into l_loc_obj_number
from hz_locations
where location_id = x_location_id;
hz_location_v2pub.update_location(
'F',
location_rec,
l_loc_obj_number,
x_return_status,
x_msg_count,
x_msg_data
);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_lp_psite_id is NULL)) then
AMS_ListImport_PVT.create_party_site(
psite_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_party_site_id,
x_party_site_number
);
if l_rec_update_flag = 'Y' and l_lp_psite_id is not NULL then
psite_rec.party_site_id := l_lp_psite_id;
select OBJECT_VERSION_NUMBER into l_ps_obj_number
from hz_party_sites
where party_site_id = l_lp_psite_id;
hz_party_site_v2pub.update_party_site(
'F',
psite_rec,
l_ps_obj_number,
x_return_status,
x_msg_count,
x_msg_data
);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_phone_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
phone_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_fax_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
fax_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_url_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
fax_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and l_email_exists is NULL)) then
AMS_ListImport_PVT.create_contact_point(
cpoint_rec,
edi_rec,
email_rec,
phone_rec,
telex_rec,
web_rec,
x_return_status,
x_msg_count,
x_msg_data,
x_contact_point_id);
if ((x_phone_number is not NULL OR x_email_address is not NULL ) and l_rec_update_flag = 'Y' and x_per_party_id is not null) then
phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
select OBJECT_VERSION_NUMBER into l_cp_obj_number
from hz_contact_points
where contact_point_id = x_contact_point_id;
hz_contact_point_v2pub.update_contact_point(
'F',
cpoint_rec,
edi_rec,
email_rec,
phone_rec,
telex_rec,
web_rec,
l_cp_obj_number,
x_return_status,
x_msg_count,
x_msg_data);
UPDATE ams_imp_source_lines
SET party_id = x_per_party_id,
load_status = 'SUCCESS',
contact_point_id = l_phone_id,
location_id = x_location_id,
enabled_flag = l_enabled_flag
-- sranka 1/14/2003
-- WHERE import_source_line_id = person_rec.party_rec.orig_system_reference // original
WHERE import_source_line_id = l_import_source_line_id
AND import_list_header_id = p_import_list_header_id;
select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
hz_parties party
where psite.location_id = loc.location_id
and loc.address1 = p_address1
and loc.country = p_country
and party.customer_key = l_party_key
and party.party_type = 'ORGANIZATION'
and party.status = 'A'
and psite.party_id = party.party_id;
select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
hz_parties party
where psite.location_id = loc.location_id
and loc.address1 = p_address1
and loc.country = p_country
and party.customer_key = l_party_key
and party.party_type = 'ORGANIZATION'
and party.status = 'A'
and psite.party_id = party.party_id
and party.orig_system_reference = p_orig_system_reference;
select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
hz_parties party
where psite.location_id = loc.location_id
and loc.country = p_country
and party.customer_key = l_party_key
and party.party_type = 'ORGANIZATION'
and party.status = 'A'
and psite.party_id = party.party_id;
select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
hz_parties party
where psite.location_id = loc.location_id
and loc.country = p_country
and party.customer_key = l_party_key
and party.party_type = 'ORGANIZATION'
and party.status = 'A'
and psite.party_id = party.party_id
and party.orig_system_reference = p_orig_system_reference;
select 'Y' from hz_parties
where customer_key = l_party_key
and status = 'A'
and party_type = 'ORGANIZATION';
select 'Y' from hz_parties
where customer_key = l_party_key
and party_type = 'ORGANIZATION'
and status = 'A'
AND orig_system_reference = p_orig_system_reference;
select max(party_id) from hz_parties
where customer_key = l_party_key
and status = 'A'
and party_type = 'ORGANIZATION';
select max(party_id) from hz_parties
where customer_key = l_party_key
and status = 'A'
and party_type = 'ORGANIZATION'
and orig_system_reference = p_orig_system_reference;
select import_type, batch_id, nvl(ERROR_THRESHOLD,0)
from ams_imp_list_headers_all
where import_list_header_id = p_import_list_header_id;
select 'Y'
from ams_list_import_errors
where import_list_header_id = p_import_list_header_id
and import_source_line_id = p_import_source_line_id
and batch_id = l_batch_id;
select file_type from ams_imp_documents where import_list_header_id = p_import_list_header_id;
select org_imp_xml_element_id, add_imp_xml_element_id, ocont_imp_xml_element_id, cp_imp_xml_element_id,
em_imp_xml_element_id from ams_hz_b2b_mapping_v where import_list_header_id = p_import_list_header_id
and import_source_line_id = p_import_source_line_id;
select per_imp_xml_element_id, add_imp_xml_element_id, cp_imp_xml_element_id, em_imp_xml_element_id
from ams_hz_b2c_mapping_v where import_list_header_id = p_import_list_header_id
and import_source_line_id = p_import_source_line_id;
update ams_imp_source_lines
set load_status = 'ERROR' , ENABLED_FLAG = null
where import_list_header_id = p_import_list_header_id
and import_source_line_id = p_import_source_line_id;
update ams_list_import_errors
set col1 = substr(col1||','||p_error_text,1,4000)
where import_list_header_id = p_import_list_header_id
and import_source_line_id = p_import_source_line_id
and batch_id = l_batch_id;
INSERT INTO ams_list_import_errors
(
LIST_IMPORT_ERROR_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
IMPORT_SOURCE_LINE_ID,
IMPORT_LIST_HEADER_ID,
IMPORT_TYPE,
ERROR_TYPE,
BATCH_ID,
col1
)
VALUES
(
ams_list_import_errors_s.nextval, -- LIST_IMPORT_ERROR_ID,
FND_GLOBAL.User_ID, -- LAST_UPDATED_BY,
SYSDATE, -- LAST_UPDATE_DATE,
SYSDATE, -- CREATION_DATE,
FND_GLOBAL.User_ID, -- CREATED_BY,
FND_GLOBAL.Conc_Login_ID, -- LAST_UPDATE_LOGIN,
p_import_source_line_id,
p_import_list_header_id,
l_imp_type, -- IMPORT_TYPE,
'E', -- ERROR_TYPE,
l_batch_id,
substr(p_error_text,1,4000)
);
update AMS_IMP_XML_ELEMENTS
set ERROR_TEXT = substr(p_error_text,1,2000),
LOAD_STATUS = 'ERROR'
where imp_xml_element_id = l_imp_xml_element_id;
AMS_ListImport_PVT.update_element_error (
p_import_list_header_id,l_imp_xml_element_id,
p_field_name,upper(p_field_name)||' :'||substr(p_error_text,1,2000));
SELECT user_status_id into l_user_status_id FROM ams_user_statuses_vl
WHERE system_status_type = 'AMS_IMPORT_STATUS' AND
system_status_code = 'ERROR' and default_flag = 'Y';
UPDATE ams_imp_list_headers_all
set status_code = l_lookup_code,
user_status_id = l_user_status_id,
status_date = sysdate
where import_list_header_id = p_import_list_header_id;
update ams_imp_source_lines
set load_status = 'ERROR' , ENABLED_FLAG = null
where import_list_header_id = p_import_list_header_id
and import_source_line_id = p_import_source_line_id;
update ams_list_import_errors
set col350 = substr(col350||','||p_error_text,1,4000)
where import_list_header_id = p_import_list_header_id
and import_source_line_id = p_import_source_line_id;
INSERT INTO ams_list_import_errors
(
LIST_IMPORT_ERROR_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
IMPORT_SOURCE_LINE_ID,
IMPORT_LIST_HEADER_ID,
IMPORT_TYPE,
ERROR_TYPE,
BATCH_ID,
col1
)
VALUES
(
ams_list_import_errors_s.nextval, -- LIST_IMPORT_ERROR_ID,
FND_GLOBAL.User_ID, -- LAST_UPDATED_BY,
SYSDATE, -- LAST_UPDATE_DATE,
SYSDATE, -- CREATION_DATE,
FND_GLOBAL.User_ID, -- CREATED_BY,
FND_GLOBAL.Conc_Login_ID, -- LAST_UPDATE_LOGIN,
p_import_source_line_id,
p_import_list_header_id,
l_imp_type, -- IMPORT_TYPE,
'E', -- ERROR_TYPE,
l_batch_id,
substr(p_error_text,1,4000)
);
select 'Y' from hz_parties
where customer_key = l_party_key
and status = 'A'
and party_type = 'PERSON';
select 'Y' from hz_parties
where customer_key = l_party_key
and party_type = 'PERSON'
and status = 'A'
and orig_system_reference = p_orig_system_reference;
select max(per.party_id) from
hz_parties org,
hz_parties per,
hz_relationships rel,
hz_contact_points cpoint
where org.party_id = p_org_party_id
and org.party_type = 'ORGANIZATION'
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.SUBJECT_TYPE = 'PERSON'
and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
-- sranka 3/21/2003
-- made changes for supporting EMPLOYEE_OF" relationship
-- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
-- and rel.RELATIONSHIP_CODE = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
-- and rel.RELATIONSHIP_TYPE = NVL(ocon_rec.party_rel_rec.relationship_type,'CONTACT')
and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
and rel.OBJECT_ID = org.party_id
and rel.SUBJECT_ID = per.PARTY_ID
and per.customer_key = l_party_key
and per.status = 'A'
and cpoint.owner_table_id = rel.party_id
and cpoint.owner_table_name = 'HZ_PARTIES'
and cpoint.contact_point_type = 'EMAIL'
and upper(cpoint.email_address) = upper(p_email_address)
and cpoint.status = 'A';
select max(per.party_id) from
hz_parties org,
hz_parties per,
hz_relationships rel,
hz_contact_points cpoint
where org.party_id = p_org_party_id
and org.party_type = 'ORGANIZATION'
and org.orig_system_reference = p_orig_system_reference
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.SUBJECT_TYPE = 'PERSON'
and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
-- sranka 3/21/2003
-- made changes for supporting EMPLOYEE_OF" relationship
-- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
and rel.OBJECT_ID = org.party_id
and rel.SUBJECT_ID = per.PARTY_ID
and per.customer_key = l_party_key
and per.status = 'A'
and cpoint.owner_table_id = rel.party_id
and cpoint.owner_table_name = 'HZ_PARTIES'
and cpoint.contact_point_type = 'EMAIL'
and upper(cpoint.email_address) = upper(p_email_address)
and cpoint.status = 'A';
select max(per.party_id) from
hz_parties org,
hz_parties per,
hz_relationships rel,
hz_contact_points cpoint,
hz_contact_points cpoint1
where org.party_id = p_org_party_id
and org.party_type = 'ORGANIZATION'
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.SUBJECT_TYPE = 'PERSON'
and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
-- sranka 3/21/2003
-- made changes for supporting EMPLOYEE_OF" relationship
-- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
and rel.OBJECT_ID = org.party_id
and rel.SUBJECT_ID = per.PARTY_ID
and per.customer_key = l_party_key
and per.status = 'A'
and cpoint.owner_table_id = rel.party_id
and cpoint.owner_table_name = 'HZ_PARTIES'
and cpoint.contact_point_type = 'EMAIL'
and upper(cpoint.email_address) = upper(p_email_address)
and cpoint.status = 'A'
and cpoint1.owner_table_id = rel.party_id
and cpoint1.owner_table_name = 'HZ_PARTIES'
and cpoint1.contact_point_type = 'PHONE'
and cpoint1.transposed_phone_number = l_transposed_phone_no
-- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
-- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
and (cpoint1.phone_line_type<>'FAX' or cpoint1.phone_line_type is null)
and cpoint1.status = 'A';
select max(per.party_id) from
hz_parties org,
hz_parties per,
hz_relationships rel,
hz_contact_points cpoint,
hz_contact_points cpoint1
where org.party_id = p_org_party_id
and org.party_type = 'ORGANIZATION'
and org.orig_system_reference = p_orig_system_reference
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.SUBJECT_TYPE = 'PERSON'
and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
-- sranka 3/21/2003
-- made changes for supporting EMPLOYEE_OF" relationship
-- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
and rel.OBJECT_ID = org.party_id
and rel.SUBJECT_ID = per.PARTY_ID
and per.customer_key = l_party_key
and per.status = 'A'
and cpoint.owner_table_id = rel.party_id
and cpoint.owner_table_name = 'HZ_PARTIES'
and cpoint.contact_point_type = 'EMAIL'
and upper(cpoint.email_address) = upper(p_email_address)
and cpoint.status = 'A'
and cpoint1.owner_table_id = rel.party_id
and cpoint1.owner_table_name = 'HZ_PARTIES'
and cpoint1.contact_point_type = 'PHONE'
and cpoint1.transposed_phone_number = l_transposed_phone_no
-- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
-- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
and (cpoint1.phone_line_type<>'FAX' or cpoint1.phone_line_type is null)
and cpoint1.status = 'A';
select max(per.party_id) from
hz_parties org,
hz_parties per,
hz_relationships rel,
hz_contact_points cpoint
where org.party_id = p_org_party_id
and org.party_type = 'ORGANIZATION'
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.SUBJECT_TYPE = 'PERSON'
and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
-- sranka 3/21/2003
-- made changes for supporting EMPLOYEE_OF" relationship
-- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
and rel.OBJECT_ID = org.party_id
and rel.SUBJECT_ID = per.PARTY_ID
and per.customer_key = l_party_key
and per.status = 'A'
and cpoint.owner_table_id = rel.party_id
and cpoint.owner_table_name = 'HZ_PARTIES'
and cpoint.contact_point_type = 'PHONE'
and cpoint.transposed_phone_number = l_transposed_phone_no
-- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
-- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
and (cpoint.phone_line_type<>'FAX' or cpoint.phone_line_type is null)
and cpoint.status = 'A';
select max(per.party_id) from
hz_parties org,
hz_parties per,
hz_relationships rel,
hz_contact_points cpoint
where org.party_id = p_org_party_id
and org.party_type = 'ORGANIZATION'
and org.orig_system_reference = p_orig_system_reference
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.SUBJECT_TYPE = 'PERSON'
and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
-- sranka 3/21/2003
-- made changes for supporting EMPLOYEE_OF" relationship
-- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
and rel.RELATIONSHIP_TYPE = NVL(p_relationship_type,'CONTACT')
and rel.OBJECT_ID = org.party_id
and rel.SUBJECT_ID = per.PARTY_ID
and per.customer_key = l_party_key
and per.status = 'A'
and cpoint.owner_table_id = rel.party_id
and cpoint.owner_table_name = 'HZ_PARTIES'
and cpoint.contact_point_type = 'PHONE'
and cpoint.transposed_phone_number = l_transposed_phone_no
-- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
-- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
and (cpoint.phone_line_type<>'FAX' or cpoint.phone_line_type is null)
and cpoint.status = 'A';
select MAX(PARTY_ID) from hz_parties
where customer_key = l_party_key
and status = 'A'
and party_type = 'PERSON';
select max(rel.party_id) from
hz_parties org,
hz_relationships rel
where org.party_id = p_org_party_id
and org.party_type = 'ORGANIZATION'
and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.SUBJECT_TYPE = 'PERSON'
and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
and rel.OBJECT_ID = org.party_id
and rel.SUBJECT_ID = l_per_party_id;
SELECT PARTY_ID FROM AMS_PARTY_SOURCES
WHERE PARTY_ID = l_rel_party_id
and upper(COL26) = upper(p_email_address);
SELECT PARTY_ID FROM AMS_PARTY_SOURCES
WHERE PARTY_ID = l_rel_party_id
and upper(COL26) = upper(p_email_address)
and COL28 = p_phone_area_code
and COL29 = p_phone_number
and COL30 = p_phone_extension;
SELECT PARTY_ID FROM AMS_PARTY_SOURCES
WHERE PARTY_ID = l_rel_party_id
and COL28 = p_phone_area_code
and COL29 = p_phone_number
and COL30 = p_phone_extension;
insert into ams_party_sources
(
party_sources_id,
party_id,
IMPORT_SOURCE_LINE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
IMPORT_LIST_HEADER_ID,
LIST_SOURCE_TYPE_ID,
USED_FLAG,
OVERLAY_FLAG,
OVERLAY_DATE,
COL1,
COL2,
COL3,
COL4,
COL5,
COL6,
COL7,
COL8,
COL9,
COL10,
COL11,
COL12,
COL13,
COL14,
COL15,
COL16,
COL17,
COL18,
COL19,
COL20,
COL21,
COL22,
COL23,
COL24,
COL25,
COL26,
COL27,
COL28,
COL29,
COL30,
COL31,
COL32,
COL33,
COL34,
COL35,
COL36,
COL37,
COL201,
COL202,
COL203,
COL204,
COL205,
COL206,
COL207,
COL208,
COL209,
COL210,
COL211,
COL212,
COL213,
COL214,
COL215,
COL216,
COL217,
COL218,
COL219,
COL220,
COL221,
COL222,
COL223,
COL224,
COL225,
COL226,
COL227,
COL228,
COL229,
COL230,
COL231,
COL232,
COL233,
COL234,
COL235,
COL236,
COL237,
COL238,
COL239,
COL240,
COL241,
COL242,
COL243,
COL244,
COL245,
COL246,
COL247,
COL248,
COL249,
COL250
)
select
ams_party_sources_s.nextval,
LINE.PARTY_ID,
LINE.IMPORT_SOURCE_LINE_ID,
LINE.OBJECT_VERSION_NUMBER,
LINE.LAST_UPDATE_DATE,
LINE.LAST_UPDATED_BY,
LINE.CREATION_DATE,
LINE.CREATED_BY,
LINE.LAST_UPDATE_LOGIN,
LINE.IMPORT_LIST_HEADER_ID,
header.LIST_SOURCE_TYPE_ID,
'N',
nvl(p_overlay,'N'),
SYSDATE,
LINE.COL1,
LINE.COL2,
LINE.COL3,
LINE.COL4,
LINE.COL5,
LINE.COL6,
LINE.COL7,
LINE.COL8,
LINE.COL9,
LINE.COL10,
LINE.COL11,
LINE.COL12,
LINE.COL13,
LINE.COL14,
LINE.COL15,
LINE.COL16,
LINE.COL17,
LINE.COL18,
LINE.COL19,
LINE.COL20,
LINE.COL21,
LINE.COL22,
LINE.COL23,
LINE.COL24,
LINE.COL25,
LINE.COL26,
LINE.COL27,
LINE.COL28,
LINE.COL29,
LINE.COL30,
LINE.COL31,
LINE.COL32,
LINE.COL33,
LINE.COL34,
LINE.COL35,
LINE.COL36,
LINE.COL37,
LINE.COL201,
LINE.COL202,
LINE.COL203,
LINE.COL204,
LINE.COL205,
LINE.COL206,
LINE.COL207,
LINE.COL208,
LINE.COL209,
LINE.COL210,
LINE.COL211,
LINE.COL212,
LINE.COL213,
LINE.COL214,
LINE.COL215,
LINE.COL216,
LINE.COL217,
LINE.COL218,
LINE.COL219,
LINE.COL220,
LINE.COL221,
LINE.COL222,
LINE.COL223,
LINE.COL224,
LINE.COL225,
LINE.COL226,
LINE.COL227,
LINE.COL228,
LINE.COL229,
LINE.COL230,
LINE.COL231,
LINE.COL232,
LINE.COL233,
LINE.COL234,
LINE.COL235,
LINE.COL236,
LINE.COL237,
LINE.COL238,
LINE.COL239,
LINE.COL240,
LINE.COL241,
LINE.COL242,
LINE.COL243,
LINE.COL244,
LINE.COL245,
LINE.COL246,
LINE.COL247,
LINE.COL248,
LINE.COL249,
LINE.COL250
from ams_imp_source_lines line,
ams_imp_list_headers_all header
where line.import_source_line_id = p_import_source_line_id
and line.import_list_header_id = p_import_list_header_id
and line.import_list_header_id = header.import_list_header_id;
select max(p.party_id) from hz_contact_points cp,
hz_parties p
where p.customer_key = l_party_key
and p.party_type = 'PERSON'
and p.status = 'A'
and cp.owner_table_id = p.party_id
and cp.owner_table_name = 'HZ_PARTIES'
and upper(cp.email_address) = upper(p_email_address);
select max(p.party_id) from hz_contact_points cp,
hz_parties p
where p.customer_key = l_party_key
and p.party_type = 'PERSON'
and p.status = 'A'
and p.orig_system_reference = p_orig_system_reference
and cp.owner_table_id = p.party_id
and cp.owner_table_name = 'HZ_PARTIES'
and upper(cp.email_address) = upper(p_email_address);
select max(p.party_id) from hz_contact_points cp,
hz_parties p
where p.customer_key = l_party_key
and p.party_type = 'PERSON'
and p.status = 'A'
and cp.owner_table_id = p.party_id
and cp.owner_table_name = 'HZ_PARTIES'
and cp.transposed_phone_number = l_transposed_phone_no;
select max(p.party_id) from hz_contact_points cp,
hz_parties p
where p.customer_key = l_party_key
and p.party_type = 'PERSON'
and p.status = 'A'
and p.orig_system_reference = p_orig_system_reference
and cp.owner_table_id = p.party_id
and cp.owner_table_name = 'HZ_PARTIES'
and cp.transposed_phone_number = l_transposed_phone_no;
select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
hz_parties party
where psite.location_id = loc.location_id
and loc.address1 = p_address1
and loc.country = p_country
and party.customer_key = l_party_key
and party.party_type = 'PERSON'
and party.status = 'A'
and psite.party_id = party.party_id;
select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
hz_parties party
where psite.location_id = loc.location_id
and loc.address1 = p_address1
and loc.country = p_country
and party.customer_key = l_party_key
and party.party_type = 'PERSON'
and party.status = 'A'
and party.orig_system_reference = p_orig_system_reference
and psite.party_id = party.party_id;
select 'Y' from hz_parties
where customer_key = l_party_key
and status = 'A'
and party_type = 'PERSON';
select 'Y' from hz_parties
where customer_key = l_party_key
and party_type = 'PERSON'
and status = 'A'
and orig_system_reference = p_orig_system_reference;
select max(p2.party_id) from hz_contact_points cp,
hz_parties p1, hz_relationships hr, hz_parties p2
where p2.customer_key = l_party_key
and p1.party_type = 'PARTY_RELATIONSHIP'
and p2.party_type = 'PERSON'
and p1.status = 'A'
and p2.status = 'A'
and cp.owner_table_id = p1.party_id
and cp.owner_table_name = 'HZ_PARTIES'
and upper(cp.email_address) = upper(p_email_address)
and p1.party_id = hr.party_id
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = p2.party_id;
select max(p2.party_id) from hz_contact_points cp,
hz_parties p1, hz_relationships hr, hz_parties p2
where p2.customer_key = l_party_key
and p1.party_type = 'PARTY_RELATIONSHIP'
and p2.party_type = 'PERSON'
and p1.status = 'A'
and p2.status = 'A'
and p1.orig_system_reference = p_orig_system_reference
and cp.owner_table_id = p1.party_id
and cp.owner_table_name = 'HZ_PARTIES'
and upper(cp.email_address) = upper(p_email_address)
and p1.party_id = hr.party_id
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = p2.party_id;
select max(p2.party_id) from hz_contact_points cp,
hz_parties p1, hz_relationships hr, hz_parties p2
where p2.customer_key = l_party_key
and p1.party_type = 'PARTY_RELATIONSHIP'
and p2.party_type = 'PERSON'
and p1.status = 'A'
and p2.status = 'A'
and cp.owner_table_id = p1.party_id
and cp.owner_table_name = 'HZ_PARTIES'
and cp.transposed_phone_number = l_transposed_phone_no
and p1.party_id = hr.party_id
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = p2.party_id;
select max(p2.party_id) from hz_contact_points cp,
hz_parties p1, hz_relationships hr, hz_parties p2
where p2.customer_key = l_party_key
and p1.party_type = 'PARTY_RELATIONSHIP'
and p2.party_type = 'PERSON'
and p1.status = 'A'
and p2.status = 'A'
and p1.orig_system_reference = p_orig_system_reference
and cp.owner_table_id = p1.party_id
and cp.owner_table_name = 'HZ_PARTIES'
and cp.transposed_phone_number = l_transposed_phone_no
and p1.party_id = hr.party_id
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = p2.party_id;
select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
hz_parties party, hz_relationships hr, hz_parties p2
where psite.location_id = loc.location_id
and loc.address1 = p_address1
and loc.country = p_country
and party.customer_key = l_party_key
and party.party_type = 'PARTY_RELATIONSHIP'
and p2.party_type = 'PERSON'
and party.status = 'A'
and p2.status = 'A'
and psite.party_id = party.party_id
and party.party_id = hr.party_id
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = p2.party_id;
select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
hz_parties party, hz_relationships hr, hz_parties p2
where psite.location_id = loc.location_id
and loc.address1 = p_address1
and loc.country = p_country
and party.customer_key = l_party_key
and party.party_type = 'PARTY_RELATIONSHIP'
and p2.party_type = 'PERSON'
and party.status = 'A'
and p2.status = 'A'
and party.orig_system_reference = p_orig_system_reference
and psite.party_id = party.party_id
and party.party_id = hr.party_id
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = p2.party_id;
select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
where hp2. customer_key = l_party_key
and hp1.status = 'A'
and hp2.status = 'A'
and hp1.party_type = 'PARTY_RELATIONSHIP'
and hp1.party_id = hr.party_id
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = hp2.party_id
and hp2.party_type = 'PERSON';
select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
where hp2. customer_key = l_party_key
and hp1.status = 'A'
and hp2.status = 'A'
and hp1.party_type = 'PARTY_RELATIONSHIP'
and hp1.party_id = hr.party_id
and hr.relationship_code = 'CONTACT_OF'
and hr.subject_id = hp2.party_id
and hp2.party_type = 'PERSON'
and hp2.orig_system_reference = p_orig_system_reference;
select hr.subject_id
from hz_relationships hr
where hr.party_id = c_rel_party_id
and hr.relationship_code = 'CONTACT_OF';
SELECT PARTY_ID FROM AMS_PARTY_SOURCES
WHERE PARTY_ID = p_party_id
and upper(COL26) = upper(p_email_address);
select party_id from AMS_PARTY_SOURCES
where party_id = p_party_id
and col29 = p_ph_number
and nvl(col27,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_code,'x')
and nvl(col28,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
select party_id from AMS_PARTY_SOURCES
where party_id = p_party_id
and col18 = p_address1
and col17 = p_country;
select MAX(PARTY_ID) from hz_parties
where customer_key = l_party_key
and party_type = 'PERSON';
PROCEDURE update_rented_list_party (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_party_id IN NUMBER
) IS
BEGIN
SAVEPOINT update_rented_list_party_pub;
AMS_ListImport_PVT.update_rented_list_party (
p_party_id => p_party_id,
p_return_status => x_return_status,
p_msg_count => x_msg_count,
p_msg_data => x_msg_data);
ROLLBACK TO update_rented_list_party_pub;
ROLLBACK TO update_rented_list_party_pub;
ROLLBACK TO update_rented_list_party_pub;
FND_MSG_PUB.add_exc_msg(g_pkg_name, 'update_rented_list_party');
end update_rented_list_party;
select import_type from ams_imp_list_headers_all
where import_list_header_id = p_import_list_header_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = p_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_org_record_in_tbl(i).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_loc_record_in_tbl(j).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_con_record_in_tbl(k).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_pho_record_in_tbl(l).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_eml_record_in_tbl(m).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = p_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_per_record_in_tbl(i).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_loc_record_in_tbl(j).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_pho_record_in_tbl(k).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'SUCCESS'
where imp_xml_element_id = l_eml_record_in_tbl(l).imp_xml_element_id;
select import_type from ams_imp_list_headers_all
where import_list_header_id = p_import_list_header_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = p_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_org_record_in_tbl(i).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_loc_record_in_tbl(j).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_con_record_in_tbl(k).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_pho_record_in_tbl(l).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_eml_record_in_tbl(m).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = p_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_per_record_in_tbl(i).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_loc_record_in_tbl(j).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_pho_record_in_tbl(k).imp_xml_element_id;
update AMS_IMP_XML_ELEMENTS
set LOAD_STATUS = 'DUPLICATE'
where imp_xml_element_id = l_eml_record_in_tbl(l).imp_xml_element_id;