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