[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;
2092: x_contact_point_id := l_email_id;
2093: end if;
2094: cpoint_rec.contact_point_id := x_contact_point_id;
2095: select OBJECT_VERSION_NUMBER into l_cp_obj_number
2096: from hz_contact_points
2097: where contact_point_id = x_contact_point_id;
2098:
2099: hz_contact_point_v2pub.update_contact_point(
2100: 'F',
2794:
2795: -- Create contact points Email
2796:
2797: if x_email_address is not NULL and x_per_party_id is not null then
2798: -- SELECT hz_contact_points_s.nextval into x_contact_point_id from dual;
2799:
2800: x_return_status := null;
2801: x_msg_count := null;
2802: x_msg_data := null;
2856: x_contact_point_id := l_email_id;
2857: end if;
2858: cpoint_rec.contact_point_id := x_contact_point_id;
2859: select OBJECT_VERSION_NUMBER into l_cp_obj_number
2860: from hz_contact_points
2861: where contact_point_id = x_contact_point_id;
2862: hz_contact_point_v2pub.update_contact_point(
2863: 'F',
2864: cpoint_rec,
3627: select max(per.party_id) from
3628: hz_parties org,
3629: hz_parties per,
3630: hz_relationships rel,
3631: hz_contact_points cpoint
3632: where org.party_id = p_org_party_id
3633: and org.party_type = 'ORGANIZATION'
3634: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3635: and rel.SUBJECT_TYPE = 'PERSON'
3657: select max(per.party_id) from
3658: hz_parties org,
3659: hz_parties per,
3660: hz_relationships rel,
3661: hz_contact_points cpoint
3662: where org.party_id = p_org_party_id
3663: and org.party_type = 'ORGANIZATION'
3664: and org.orig_system_reference = p_orig_system_reference
3665: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3685: select max(per.party_id) from
3686: hz_parties org,
3687: hz_parties per,
3688: hz_relationships rel,
3689: hz_contact_points cpoint,
3690: hz_contact_points cpoint1
3691: where org.party_id = p_org_party_id
3692: and org.party_type = 'ORGANIZATION'
3693: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3686: hz_parties org,
3687: hz_parties per,
3688: hz_relationships rel,
3689: hz_contact_points cpoint,
3690: hz_contact_points cpoint1
3691: where org.party_id = p_org_party_id
3692: and org.party_type = 'ORGANIZATION'
3693: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3694: and rel.SUBJECT_TYPE = 'PERSON'
3722: select max(per.party_id) from
3723: hz_parties org,
3724: hz_parties per,
3725: hz_relationships rel,
3726: hz_contact_points cpoint,
3727: hz_contact_points cpoint1
3728: where org.party_id = p_org_party_id
3729: and org.party_type = 'ORGANIZATION'
3730: and org.orig_system_reference = p_orig_system_reference
3723: hz_parties org,
3724: hz_parties per,
3725: hz_relationships rel,
3726: hz_contact_points cpoint,
3727: hz_contact_points cpoint1
3728: where org.party_id = p_org_party_id
3729: and org.party_type = 'ORGANIZATION'
3730: and org.orig_system_reference = p_orig_system_reference
3731: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3759: select max(per.party_id) from
3760: hz_parties org,
3761: hz_parties per,
3762: hz_relationships rel,
3763: hz_contact_points cpoint
3764: where org.party_id = p_org_party_id
3765: and org.party_type = 'ORGANIZATION'
3766: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3767: and rel.SUBJECT_TYPE = 'PERSON'
3791: select max(per.party_id) from
3792: hz_parties org,
3793: hz_parties per,
3794: hz_relationships rel,
3795: hz_contact_points cpoint
3796: where org.party_id = p_org_party_id
3797: and org.party_type = 'ORGANIZATION'
3798: and org.orig_system_reference = p_orig_system_reference
3799: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4370: l_transposed_phone_no varchar2(60);
4371:
4372:
4373: cursor c_email_address is
4374: select max(p.party_id) from hz_contact_points cp,
4375: hz_parties p
4376: where p.customer_key = l_party_key
4377: and p.party_type = 'PERSON'
4378: and p.status = 'A'
4380: and cp.owner_table_name = 'HZ_PARTIES'
4381: and upper(cp.email_address) = upper(p_email_address);
4382:
4383: cursor c_email_address_with_osr is
4384: select max(p.party_id) from hz_contact_points cp,
4385: hz_parties p
4386: where p.customer_key = l_party_key
4387: and p.party_type = 'PERSON'
4388: and p.status = 'A'
4392: and upper(cp.email_address) = upper(p_email_address);
4393:
4394:
4395: cursor c_ph_number is
4396: select max(p.party_id) from hz_contact_points cp,
4397: hz_parties p
4398: where p.customer_key = l_party_key
4399: and p.party_type = 'PERSON'
4400: and p.status = 'A'
4407: and nvl(cp.phone_area_code,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4408: */
4409:
4410: cursor c_ph_number_with_osr is
4411: select max(p.party_id) from hz_contact_points cp,
4412: hz_parties p
4413: where p.customer_key = l_party_key
4414: and p.party_type = 'PERSON'
4415: and p.status = 'A'
4616: l_transposed_phone_no varchar2(60);
4617:
4618:
4619: cursor c_email_address is
4620: select max(p2.party_id) from hz_contact_points cp,
4621: hz_parties p1, hz_relationships hr, hz_parties p2
4622: where p2.customer_key = l_party_key
4623: and p1.party_type = 'PARTY_RELATIONSHIP'
4624: and p2.party_type = 'PERSON'
4631: and hr.relationship_code = 'CONTACT_OF'
4632: and hr.subject_id = p2.party_id;
4633:
4634: cursor c_email_address_with_osr is
4635: select max(p2.party_id) from hz_contact_points cp,
4636: hz_parties p1, hz_relationships hr, hz_parties p2
4637: where p2.customer_key = l_party_key
4638: and p1.party_type = 'PARTY_RELATIONSHIP'
4639: and p2.party_type = 'PERSON'
4648: and hr.subject_id = p2.party_id;
4649:
4650:
4651: cursor c_ph_number is
4652: select max(p2.party_id) from hz_contact_points cp,
4653: hz_parties p1, hz_relationships hr, hz_parties p2
4654: where p2.customer_key = l_party_key
4655: and p1.party_type = 'PARTY_RELATIONSHIP'
4656: and p2.party_type = 'PERSON'
4668: and nvl(cp.phone_area_code,nvl(p_ph_area_code,'x')) = nvl(p_ph_area_code,'x');
4669: */
4670:
4671: cursor c_ph_number_with_osr is
4672: select max(p2.party_id) from hz_contact_points cp,
4673: hz_parties p1, hz_relationships hr, hz_parties p2
4674: where p2.customer_key = l_party_key
4675: and p1.party_type = 'PARTY_RELATIONSHIP'
4676: and p2.party_type = 'PERSON'