[Home] [Help]
426: AND location_id = x_location_id;
427:
428:
429: CURSOR phone_exists (x_hz_party_id number,x_phone_type varchar) IS
430: SELECT 'Y' FROM hz_contact_points
431: WHERE contact_point_type = 'PHONE'
432: AND phone_line_type = x_phone_type
433: AND owner_table_name = 'HZ_PARTIES'
434: AND owner_table_id = x_hz_party_id
440:
441: -- srank a 7/31/2003 modified for COLT enhancements
442:
443: CURSOR org_phone_exists (x_hz_party_id number,x_org_phone_type varchar) IS
444: SELECT 'Y' FROM hz_contact_points
445: WHERE contact_point_type = 'PHONE'
446: AND phone_line_type = x_org_phone_type
447: AND owner_table_name = 'HZ_PARTIES'
448: AND owner_table_id = x_hz_party_id
449: and transposed_phone_number = l_org_transposed_phone_no;
450:
451:
452: CURSOR org_party_site_phone_exists (x_hz_party_id number,x_org_ps_phone_type varchar) IS
453: SELECT 'Y' FROM hz_contact_points
454: WHERE contact_point_type = 'PHONE'
455: AND phone_line_type = x_org_ps_phone_type
456: AND owner_table_name = 'HZ_PARTIES'
457: AND owner_table_id = x_hz_party_id
462: -- AND NVL(phone_extension,'x') = NVL(x_phone_extention,'x');
463:
464:
465: CURSOR fax_exists (x_hz_party_id number) IS
466: SELECT 'Y' FROM hz_contact_points
467: WHERE contact_point_type = 'PHONE'
468: AND phone_line_type = 'FAX'
469: AND owner_table_name = 'HZ_PARTIES'
470: AND owner_table_id = x_hz_party_id
472: AND NVL(phone_country_code,'x') = NVL(x_fax_country_code,'x')
473: AND NVL(phone_area_code,'x') = NVL(x_fax_area_code,'x');
474:
475: CURSOR url_exists (x_hz_party_id number) IS
476: SELECT 'Y' FROM hz_contact_points
477: WHERE contact_point_type = 'WEB'
478: AND owner_table_name = 'HZ_PARTIES'
479: AND owner_table_id = x_hz_party_id
480: AND url = x_url;
480: AND url = x_url;
481:
482:
483: CURSOR c_phone_id (x_hz_party_id number,x_phone_type varchar) IS
484: SELECT contact_point_id FROM hz_contact_points
485: WHERE contact_point_type = 'PHONE'
486: AND phone_line_type = x_phone_type
487: AND owner_table_name = 'HZ_PARTIES'
488: AND owner_table_id = x_hz_party_id
496:
497: -- sranka modified for colt enhancements 7/23/2003
498:
499: CURSOR c_org_phone_id (x_hz_party_id number,x_org_phone_type varchar) IS
500: SELECT contact_point_id FROM hz_contact_points
501: WHERE contact_point_type = 'PHONE'
502: AND phone_line_type = x_org_phone_type
503: AND owner_table_name = 'HZ_PARTIES'
504: AND owner_table_id = x_hz_party_id
504: AND owner_table_id = x_hz_party_id
505: and transposed_phone_number = l_org_transposed_phone_no;
506:
507: CURSOR c_org_party_site_phone_id (x_hz_party_id number,x_org_ps_phone_type varchar) IS
508: SELECT contact_point_id FROM hz_contact_points
509: WHERE contact_point_type = 'PHONE'
510: AND phone_line_type = x_org_ps_phone_type
511: AND owner_table_name = 'HZ_PARTIES'
512: AND owner_table_id = x_hz_party_id
514:
515:
516:
517: CURSOR c_url_id (x_hz_party_id number) IS
518: SELECT contact_point_id FROM hz_contact_points
519: WHERE contact_point_type = 'WEB'
520: AND owner_table_name = 'HZ_PARTIES'
521: AND owner_table_id = x_hz_party_id
522: AND url = x_url;
521: AND owner_table_id = x_hz_party_id
522: AND url = x_url;
523:
524: CURSOR c_fax_id (x_hz_party_id number) IS
525: SELECT contact_point_id FROM hz_contact_points
526: WHERE contact_point_type = 'PHONE'
527: AND phone_line_type = 'FAX'
528: AND owner_table_name = 'HZ_PARTIES'
529: AND owner_table_id = x_hz_party_id
531: AND NVL(phone_country_code,'x') = NVL(x_fax_country_code,'x')
532: AND NVL(phone_area_code,'x') = NVL(x_fax_area_code,'x');
533:
534: CURSOR email_exists (x_hz_party_id number) IS
535: SELECT 'Y' FROM hz_contact_points
536: WHERE contact_point_type = 'EMAIL'
537: AND owner_table_name = 'HZ_PARTIES'
538: AND owner_table_id = x_hz_party_id
539: AND upper(email_address) = upper(x_email_address);
541:
542: -- srank COLT Enhancements 7/19/2003
543:
544: CURSOR org_email_exists (x_hz_party_id number) IS
545: SELECT 'Y' FROM hz_contact_points
546: WHERE contact_point_type = 'EMAIL'
547: AND owner_table_name = 'HZ_PARTIES'
548: AND owner_table_id = x_hz_party_id
549: AND upper(email_address) = upper(x_org_email_address);
564: SELECT OBJECT_VERSION_NUMBER FROM hz_parties
565: WHERE PARTY_ID = x_party_rel_party_id;
566:
567: CURSOR c_email_id (x_hz_party_id number) IS
568: SELECT contact_point_id FROM hz_contact_points
569: WHERE contact_point_type = 'EMAIL'
570: AND owner_table_name = 'HZ_PARTIES'
571: AND owner_table_id = x_hz_party_id
572: AND email_address = x_email_address;
2096: x_contact_point_id := l_email_id;
2097: end if;
2098: cpoint_rec.contact_point_id := x_contact_point_id;
2099: select OBJECT_VERSION_NUMBER into l_cp_obj_number
2100: from hz_contact_points
2101: where contact_point_id = x_contact_point_id;
2102:
2103: hz_contact_point_v2pub.update_contact_point(
2104: 'F',
2801:
2802: -- Create contact points Email
2803:
2804: if x_email_address is not NULL and x_per_party_id is not null then
2805: -- SELECT hz_contact_points_s.nextval into x_contact_point_id from dual;
2806:
2807: x_return_status := null;
2808: x_msg_count := null;
2809: x_msg_data := null;
2863: x_contact_point_id := l_email_id;
2864: end if;
2865: cpoint_rec.contact_point_id := x_contact_point_id;
2866: select OBJECT_VERSION_NUMBER into l_cp_obj_number
2867: from hz_contact_points
2868: where contact_point_id = x_contact_point_id;
2869: hz_contact_point_v2pub.update_contact_point(
2870: 'F',
2871: cpoint_rec,
3634: select max(per.party_id) from
3635: hz_parties org,
3636: hz_parties per,
3637: hz_relationships rel,
3638: hz_contact_points cpoint
3639: where org.party_id = p_org_party_id
3640: and org.party_type = 'ORGANIZATION'
3641: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3642: and rel.SUBJECT_TYPE = 'PERSON'
3664: select max(per.party_id) from
3665: hz_parties org,
3666: hz_parties per,
3667: hz_relationships rel,
3668: hz_contact_points cpoint
3669: where org.party_id = p_org_party_id
3670: and org.party_type = 'ORGANIZATION'
3671: and org.orig_system_reference = p_orig_system_reference
3672: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3692: select max(per.party_id) from
3693: hz_parties org,
3694: hz_parties per,
3695: hz_relationships rel,
3696: hz_contact_points cpoint,
3697: hz_contact_points cpoint1
3698: where org.party_id = p_org_party_id
3699: and org.party_type = 'ORGANIZATION'
3700: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3693: hz_parties org,
3694: hz_parties per,
3695: hz_relationships rel,
3696: hz_contact_points cpoint,
3697: hz_contact_points cpoint1
3698: where org.party_id = p_org_party_id
3699: and org.party_type = 'ORGANIZATION'
3700: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3701: and rel.SUBJECT_TYPE = 'PERSON'
3729: select max(per.party_id) from
3730: hz_parties org,
3731: hz_parties per,
3732: hz_relationships rel,
3733: hz_contact_points cpoint,
3734: hz_contact_points cpoint1
3735: where org.party_id = p_org_party_id
3736: and org.party_type = 'ORGANIZATION'
3737: and org.orig_system_reference = p_orig_system_reference
3730: hz_parties org,
3731: hz_parties per,
3732: hz_relationships rel,
3733: hz_contact_points cpoint,
3734: hz_contact_points cpoint1
3735: where org.party_id = p_org_party_id
3736: and org.party_type = 'ORGANIZATION'
3737: and org.orig_system_reference = p_orig_system_reference
3738: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3766: select max(per.party_id) from
3767: hz_parties org,
3768: hz_parties per,
3769: hz_relationships rel,
3770: hz_contact_points cpoint
3771: where org.party_id = p_org_party_id
3772: and org.party_type = 'ORGANIZATION'
3773: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3774: and rel.SUBJECT_TYPE = 'PERSON'
3798: select max(per.party_id) from
3799: hz_parties org,
3800: hz_parties per,
3801: hz_relationships rel,
3802: hz_contact_points cpoint
3803: where org.party_id = p_org_party_id
3804: and org.party_type = 'ORGANIZATION'
3805: and org.orig_system_reference = p_orig_system_reference
3806: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4377: l_transposed_phone_no varchar2(60);
4378:
4379:
4380: cursor c_email_address is
4381: select max(p.party_id) from hz_contact_points cp,
4382: hz_parties p
4383: where p.customer_key = l_party_key
4384: and p.party_type = 'PERSON'
4385: and p.status = 'A'
4387: and cp.owner_table_name = 'HZ_PARTIES'
4388: and upper(cp.email_address) = upper(p_email_address);
4389:
4390: cursor c_email_address_with_osr is
4391: select max(p.party_id) from hz_contact_points cp,
4392: hz_parties p
4393: where p.customer_key = l_party_key
4394: and p.party_type = 'PERSON'
4395: and p.status = 'A'
4399: and upper(cp.email_address) = upper(p_email_address);
4400:
4401:
4402: cursor c_ph_number is
4403: select max(p.party_id) from hz_contact_points cp,
4404: hz_parties p
4405: where p.customer_key = l_party_key
4406: and p.party_type = 'PERSON'
4407: and p.status = 'A'
4414: and nvl(cp.phone_area_code,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4415: */
4416:
4417: cursor c_ph_number_with_osr is
4418: select max(p.party_id) from hz_contact_points cp,
4419: hz_parties p
4420: where p.customer_key = l_party_key
4421: and p.party_type = 'PERSON'
4422: and p.status = 'A'
4623: l_transposed_phone_no varchar2(60);
4624:
4625:
4626: cursor c_email_address is
4627: select max(p2.party_id) from hz_contact_points cp,
4628: hz_parties p1, hz_relationships hr, hz_parties p2
4629: where p2.customer_key = l_party_key
4630: and p1.party_type = 'PARTY_RELATIONSHIP'
4631: and p2.party_type = 'PERSON'
4638: and hr.relationship_code = 'CONTACT_OF'
4639: and hr.subject_id = p2.party_id;
4640:
4641: cursor c_email_address_with_osr is
4642: select max(p2.party_id) from hz_contact_points cp,
4643: hz_parties p1, hz_relationships hr, hz_parties p2
4644: where p2.customer_key = l_party_key
4645: and p1.party_type = 'PARTY_RELATIONSHIP'
4646: and p2.party_type = 'PERSON'
4655: and hr.subject_id = p2.party_id;
4656:
4657:
4658: cursor c_ph_number is
4659: select max(p2.party_id) from hz_contact_points cp,
4660: hz_parties p1, hz_relationships hr, hz_parties p2
4661: where p2.customer_key = l_party_key
4662: and p1.party_type = 'PARTY_RELATIONSHIP'
4663: and p2.party_type = 'PERSON'
4675: and nvl(cp.phone_area_code,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4676: */
4677:
4678: cursor c_ph_number_with_osr is
4679: select max(p2.party_id) from hz_contact_points cp,
4680: hz_parties p1, hz_relationships hr, hz_parties p2
4681: where p2.customer_key = l_party_key
4682: and p1.party_type = 'PARTY_RELATIONSHIP'
4683: and p2.party_type = 'PERSON'