[Home] [Help]
4880: WHERE party_id = x_party_rel_party_id
4881: AND location_id = x_location_id;
4882:
4883: CURSOR phone_exists (x_hz_party_id number) IS
4884: SELECT 'Y' FROM hz_contact_points
4885: WHERE contact_point_type = 'PHONE'
4886: AND phone_line_type = 'GEN'
4887: AND owner_table_name = 'HZ_PARTIES'
4888: AND owner_table_id = x_hz_party_id
4892: AND NVL(phone_extension,'x') = NVL(x_phone_extention,'x');
4893:
4894:
4895: CURSOR email_exists (x_hz_party_id number) IS
4896: SELECT 'Y' FROM hz_contact_points
4897: WHERE contact_point_type = 'EMAIL'
4898: AND owner_table_name = 'HZ_PARTIES'
4899: AND owner_table_id = x_hz_party_id
4900: AND email_address = x_email_address;
13436: telex_rec hz_contact_point_v2pub.telex_rec_type := p_telex_rec;
13437: web_rec hz_contact_point_v2pub.web_rec_type := p_web_rec ;
13438:
13439: begin
13440: SELECT hz_contact_points_s.nextval into x_contact_point_id from dual;
13441:
13442: cpoint_rec.contact_point_id := x_contact_point_id;
13443: cpoint_rec.orig_system_reference := x_contact_point_id;
13444: cpoint_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
19121: and pty.party_type = 'PERSON';
19122:
19123: cursor c_email_address is
19124: select b2c.import_source_line_id, max(p.party_id)
19125: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c
19126: where b2c.import_list_header_id = p_import_list_header_id
19127: and b2c.load_status in ('ACTIVE','RELOAD')
19128: and b2c.person_exist = 'Y'
19129: and b2c.email_address is not null
19138: group by b2c.import_source_line_id;
19139:
19140: cursor c_ph_number is
19141: select b2c.import_source_line_id, max(p.party_id)
19142: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c
19143: where b2c.import_list_header_id = p_import_list_header_id
19144: and b2c.load_status in ('ACTIVE','RELOAD')
19145: and b2c.person_exist = 'Y'
19146: and b2c.phone_number is not null
19436: and pty.party_type = 'PERSON');
19437:
19438: cursor c_email_address is
19439: select b2c.import_source_line_id, max(p.party_id)
19440: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c
19441: where b2c.import_list_header_id = p_import_list_header_id
19442: and b2c.load_status in ('ACTIVE','RELOAD')
19443: and b2c.person_exist = 'Y'
19444: and b2c.orig_system_reference = p.orig_system_reference
19453: group by b2c.import_source_line_id;
19454:
19455: cursor c_ph_number is
19456: select b2c.import_source_line_id, max(p.party_id)
19457: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c
19458: where b2c.import_list_header_id = p_import_list_header_id
19459: and b2c.load_status in ('ACTIVE','RELOAD')
19460: and b2c.person_exist = 'Y'
19461: and b2c.phone_number is not null
19730: select b2b.import_source_line_id, max(per.party_id) from
19731: hz_parties org,
19732: hz_parties per,
19733: hz_relationships rel,
19734: hz_contact_points cpoint,
19735: hz_contact_points cpoint1,
19736: ams_hz_b2b_mapping_v b2b
19737: where b2b.import_list_header_id = p_import_list_header_id
19738: and b2b.load_status in ('ACTIVE','RELOAD')
19731: hz_parties org,
19732: hz_parties per,
19733: hz_relationships rel,
19734: hz_contact_points cpoint,
19735: hz_contact_points cpoint1,
19736: ams_hz_b2b_mapping_v b2b
19737: where b2b.import_list_header_id = p_import_list_header_id
19738: and b2b.load_status in ('ACTIVE','RELOAD')
19739: and b2b.ocont_exist = 'Y'
19771: select b2b.import_source_line_id,max(per.party_id) from
19772: hz_parties org,
19773: hz_parties per,
19774: hz_relationships rel,
19775: hz_contact_points cpoint,
19776: ams_hz_b2b_mapping_v b2b
19777: where b2b.import_list_header_id = p_import_list_header_id
19778: and b2b.load_status in ('ACTIVE','RELOAD')
19779: and b2b.ocont_exist = 'Y'
19803: select b2b.import_source_line_id,max(per.party_id) from
19804: hz_parties org,
19805: hz_parties per,
19806: hz_relationships rel,
19807: hz_contact_points cpoint,
19808: ams_hz_b2b_mapping_v b2b
19809: where b2b.import_list_header_id = p_import_list_header_id
19810: and b2b.load_status in ('ACTIVE','RELOAD')
19811: and b2b.ocont_exist = 'Y'
20094: select b2b.import_source_line_id, max(per.party_id) from
20095: hz_parties org,
20096: hz_parties per,
20097: hz_relationships rel,
20098: hz_contact_points cpoint,
20099: hz_contact_points cpoint1,
20100: ams_hz_b2b_mapping_v b2b
20101: where b2b.import_list_header_id = p_import_list_header_id
20102: and b2b.load_status in ('ACTIVE','RELOAD')
20095: hz_parties org,
20096: hz_parties per,
20097: hz_relationships rel,
20098: hz_contact_points cpoint,
20099: hz_contact_points cpoint1,
20100: ams_hz_b2b_mapping_v b2b
20101: where b2b.import_list_header_id = p_import_list_header_id
20102: and b2b.load_status in ('ACTIVE','RELOAD')
20103: and b2b.ocont_exist = 'Y'
20135: select b2b.import_source_line_id,max(per.party_id) from
20136: hz_parties org,
20137: hz_parties per,
20138: hz_relationships rel,
20139: hz_contact_points cpoint,
20140: ams_hz_b2b_mapping_v b2b
20141: where b2b.import_list_header_id = p_import_list_header_id
20142: and b2b.load_status in ('ACTIVE','RELOAD')
20143: and b2b.ocont_exist = 'Y'
20167: select b2b.import_source_line_id,max(per.party_id) from
20168: hz_parties org,
20169: hz_parties per,
20170: hz_relationships rel,
20171: hz_contact_points cpoint,
20172: ams_hz_b2b_mapping_v b2b
20173: where b2b.import_list_header_id = p_import_list_header_id
20174: and b2b.load_status in ('ACTIVE','RELOAD')
20175: and b2b.ocont_exist = 'Y'