[Home] [Help]
367: CURSOR PARTY_REL_EXISTS IS
368: SELECT party_id, relationship_id FROM hz_relationships
369: WHERE object_id = x_org_party_id
370: AND subject_id = x_per_party_id
371: AND subject_table_name = 'HZ_PARTIES'
372: AND subject_type = 'PERSON'
373: AND object_type = 'ORGANIZATION'
374: AND object_table_name = 'HZ_PARTIES'
375: -- sranka 3/4/2003
370: AND subject_id = x_per_party_id
371: AND subject_table_name = 'HZ_PARTIES'
372: AND subject_type = 'PERSON'
373: AND object_type = 'ORGANIZATION'
374: AND object_table_name = 'HZ_PARTIES'
375: -- sranka 3/4/2003
376: -- made changes for supporting EMPLOYEE_OF" relationship
377: -- AND relationship_code = 'CONTACT_OF';
378: AND relationship_code = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
378: AND relationship_code = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
379: AND relationship_type = NVL(ocon_rec.party_rel_rec.relationship_type,'CONTACT');
380:
381: cursor b2bparty is
382: SELECT 'Y' FROM hz_parties
383: WHERE party_type = 'PARTY_RELATIONSHIP'
384: and status = 'A'
385: AND party_id = l_b2b_party_id;
386:
385: AND party_id = l_b2b_party_id;
386:
387:
388: cursor b2cparty is
389: SELECT 'Y' FROM hz_parties
390: WHERE party_type = 'PERSON'
391: and status = 'A'
392: AND party_id = l_b2c_party_id;
393:
393:
394: cursor orgpartyid is
395: SELECT object_id FROM hz_relationships
396: WHERE subject_type = 'PERSON'
397: AND subject_table_name = 'HZ_PARTIES'
398: AND object_type = 'ORGANIZATION'
399: AND object_table_name = 'HZ_PARTIES'
400: -- sranka 3/4/2003
401: -- made changes for supporting EMPLOYEE_OF" relationship
395: SELECT object_id FROM hz_relationships
396: WHERE subject_type = 'PERSON'
397: AND subject_table_name = 'HZ_PARTIES'
398: AND object_type = 'ORGANIZATION'
399: AND object_table_name = 'HZ_PARTIES'
400: -- sranka 3/4/2003
401: -- made changes for supporting EMPLOYEE_OF" relationship
402: -- AND relationship_code = 'CONTACT_OF';
403: AND relationship_code = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
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
435: and transposed_phone_number = l_transposed_phone_no;
436: -- AND phone_number = x_phone_number
437: -- AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
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
458: and transposed_phone_number = l_org_ps_transposed_phone_no;
459: -- AND phone_number = x_phone_number
460: -- AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
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
471: AND phone_number = x_fax_number
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;
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
489: and transposed_phone_number = l_transposed_phone_no;
490: /*
491: AND phone_number = x_phone_number
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
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
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
513: and transposed_phone_number = l_org_ps_transposed_phone_no;
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;
523:
524: CURSOR c_fax_id (x_hz_party_id number) IS
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
530: AND phone_number = x_fax_number
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);
540:
541:
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);
550:
551:
560: SELECT OBJECT_VERSION_NUMBER FROM hz_org_contacts
561: WHERE PARTY_RELATIONSHIP_ID = nvl(x_party_relationship_id,l_rel_id);
562:
563: cursor c_party_rel is
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
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;
573:
574: cursor c_b2b_source_rec is
581: from ams_hz_b2c_mapping_v
582: where import_source_line_id = i_import_source_line_id;
583:
584: cursor c_org_party is
585: select party_id from hz_parties
586: where customer_key = src_org_key
587: and status = 'A'
588: and party_type = 'ORGANIZATION';
589:
587: and status = 'A'
588: and party_type = 'ORGANIZATION';
589:
590: cursor c_validate_b2b is
591: select 1 from hz_parties
592: where party_id = p_party_id
593: and party_type in ('PARTY_RELATIONSHIP','ORGANIZATION')
594: and status = 'A';
595:
593: and party_type in ('PARTY_RELATIONSHIP','ORGANIZATION')
594: and status = 'A';
595:
596: cursor c_validate_b2c is
597: select 1 from hz_parties
598: where party_id = p_party_id -- bug 5100612 mayjain
599: and party_type in ('PERSON')
600: and status = 'A';
601:
805: x_party_number := null;
806: if x_generate_party_number = 'N' then
807: select hz_party_number_s.nextval into x_party_number from dual;
808: end if;
809: select hz_parties_s.nextval into x_org_party_id from dual;
810:
811: org_rec.party_rec.party_number := x_party_number;
812: org_rec.party_rec.party_id := x_org_party_id;
813: */
832: x_party_number := null;
833: if x_generate_party_number = 'N' then
834: select hz_party_number_s.nextval into x_party_number from dual;
835: end if;
836: select hz_parties_s.nextval into x_org_party_id from dual;
837: org_rec.party_rec.party_number := x_party_number;
838: org_rec.party_rec.party_id := x_org_party_id;
839: hz_party_v2pub.create_organization(
840: 'F',
853: end if;
854: else
855: if l_rec_update_flag = 'Y' and x_org_party_id is not NULL then
856: select OBJECT_VERSION_NUMBER into l_party_obj_number
857: from hz_parties
858: where party_id = x_org_party_id;
859: org_rec.party_rec.party_id := x_org_party_id;
860:
861:
1014: /*
1015: if x_generate_party_number = 'N' then
1016: select hz_party_number_s.nextval into x_party_number from dual;
1017: end if;
1018: select hz_parties_s.nextval into x_per_party_id from dual;
1019:
1020: person_rec.party_rec.party_number := x_party_number;
1021: person_rec.party_rec.party_id := x_per_party_id;
1022:
1037: if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_per_party_id is NULL)) then
1038: if x_generate_party_number = 'N' then
1039: select hz_party_number_s.nextval into x_party_number from dual;
1040: end if;
1041: select hz_parties_s.nextval into x_per_party_id from dual;
1042:
1043: person_rec.party_rec.party_number := x_party_number;
1044: person_rec.party_rec.party_id := x_per_party_id;
1045:
1071: else
1072: if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
1073: person_rec.party_rec.party_id := x_per_party_id;
1074: select OBJECT_VERSION_NUMBER into l_party_obj_number
1075: from hz_parties
1076: where party_id = x_per_party_id;
1077: person_rec.party_rec.orig_system_reference := null;
1078: hz_party_v2pub.update_person(
1079: 'F',
1143: END IF;
1144: */
1145: ocon_rec.application_id := 530;
1146: ocon_rec.party_rel_rec.subject_type := 'PERSON';
1147: ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
1148: ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
1149: ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
1150:
1151:
1145: ocon_rec.application_id := 530;
1146: ocon_rec.party_rel_rec.subject_type := 'PERSON';
1147: ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
1148: ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
1149: ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
1150:
1151:
1152: -- sranka 1/15/2003
1153: -- assigning the value for the "orig_system_reference" for proper population of data in TCA while importing
1192: where PARTY_RELATIONSHIP_ID = l_rel_id ; -- x_party_rel_party_id;
1193:
1194: ocon_rec.org_contact_id := x_org_contact_id;
1195: select OBJECT_VERSION_NUMBER into l_party_obj_number
1196: from hz_parties
1197: where party_id = x_org_party_id;
1198: select OBJECT_VERSION_NUMBER into l_pr_obj_number
1199: from hz_relationships
1200: where relationship_id = l_rel_id and directional_flag = 'F'; -- x_party_rel_party_id;
1730: x_msg_count := null;
1731: x_msg_data := null;
1732: cpoint_rec.contact_point_type := 'EMAIL';
1733: cpoint_rec.status := 'A';
1734: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1735: cpoint_rec.owner_table_id := x_org_party_id;
1736: email_rec.email_address := org_email_rec.email_address;
1737: l_email_exists := NULL;
1738: open org_email_exists(x_org_party_id);
1771: x_msg_count := null;
1772: x_msg_data := null;
1773: cpoint_rec.contact_point_type := 'PHONE';
1774: cpoint_rec.status := 'A';
1775: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1776: cpoint_rec.owner_table_id := x_org_party_site_id;
1777: org_party_site_phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
1778: org_party_site_phone_rec.phone_number := org_phone_rec.phone_number;
1779: org_party_site_phone_rec.phone_country_code := org_phone_rec.phone_country_code;
1826: x_msg_count := null;
1827: x_msg_data := null;
1828: cpoint_rec.contact_point_type := 'PHONE';
1829: cpoint_rec.status := 'A';
1830: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1831: cpoint_rec.owner_table_id := x_org_party_id;
1832: phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
1833: phone_rec.phone_number := org_phone_rec.phone_number;
1834: phone_rec.phone_country_code := org_phone_rec.phone_country_code;
1876: x_msg_count := null;
1877: x_msg_data := null;
1878: cpoint_rec.contact_point_type := 'PHONE';
1879: cpoint_rec.status := 'A';
1880: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1881: if x_org_party_id is not null then
1882: cpoint_rec.owner_table_id := x_org_party_id;
1883: end if;
1884: if x_party_rel_party_id is not null then
1931: x_msg_count := null;
1932: x_msg_data := null;
1933: cpoint_rec.contact_point_type := 'PHONE';
1934: cpoint_rec.status := 'A';
1935: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1936: if x_org_party_id is not null then
1937: cpoint_rec.owner_table_id := x_org_party_id;
1938: end if;
1939: if x_party_rel_party_id is not null then
1985: x_msg_count := null;
1986: x_msg_data := null;
1987: cpoint_rec.contact_point_type := 'WEB';
1988: cpoint_rec.status := 'A';
1989: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1990: if x_org_party_id is not null then
1991: cpoint_rec.owner_table_id := x_org_party_id;
1992: end if;
1993: web_rec.url := x_url;
2034: x_msg_count := null;
2035: x_msg_data := null;
2036: cpoint_rec.contact_point_type := 'EMAIL';
2037: cpoint_rec.status := 'A';
2038: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2039: if x_org_party_id is not null then
2040: cpoint_rec.owner_table_id := x_org_party_id;
2041: end if;
2042: if x_party_rel_party_id is not null then
2328: /*
2329: if x_generate_party_number = 'N' then
2330: select hz_party_number_s.nextval into x_party_number from dual;
2331: end if;
2332: select hz_parties_s.nextval into x_per_party_id from dual;
2333:
2334: person_rec.party_rec.party_number := x_party_number;
2335: person_rec.party_rec.party_id := x_per_party_id;
2336: */
2356: if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_per_party_id is NULL)) then
2357: if x_generate_party_number = 'N' then
2358: select hz_party_number_s.nextval into x_party_number from dual;
2359: end if;
2360: select hz_parties_s.nextval into x_per_party_id from dual;
2361: person_rec.party_rec.party_number := x_party_number;
2362: person_rec.party_rec.party_id := x_per_party_id;
2363:
2364:
2384: else
2385: if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
2386: person_rec.party_rec.party_id := x_per_party_id;
2387: select OBJECT_VERSION_NUMBER into l_party_obj_number
2388: from hz_parties
2389: where party_id = x_per_party_id;
2390: person_rec.party_rec.orig_system_reference := null;
2391: hz_party_v2pub.update_person(
2392: 'F',
2655: x_msg_count := null;
2656: x_msg_data := null;
2657: cpoint_rec.contact_point_type := 'PHONE';
2658: cpoint_rec.status := 'A';
2659: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2660: cpoint_rec.owner_table_id := x_per_party_id;
2661: phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
2662: phone_rec.phone_number := x_phone_number;
2663: phone_rec.phone_country_code := x_phone_country_code;
2705: x_msg_count := null;
2706: x_msg_data := null;
2707: cpoint_rec.contact_point_type := 'PHONE';
2708: cpoint_rec.status := 'A';
2709: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2710: cpoint_rec.owner_table_id := x_per_party_id;
2711: fax_rec.phone_line_type := 'FAX';--'GEN';
2712: fax_rec.phone_number := x_fax_number;
2713: fax_rec.phone_country_code := x_fax_country_code;
2754: x_msg_count := null;
2755: x_msg_data := null;
2756: cpoint_rec.contact_point_type := 'WEB';
2757: cpoint_rec.status := 'A';
2758: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2759: cpoint_rec.owner_table_id := x_per_party_id;
2760: web_rec.url := x_url;
2761: web_rec.web_type := 'com';
2762:
2802: x_msg_data := null;
2803: -- cpoint_rec.contact_point_id := x_contact_point_id;
2804: cpoint_rec.contact_point_type := 'EMAIL';
2805: cpoint_rec.status := 'A';
2806: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2807: cpoint_rec.owner_table_id := x_per_party_id;
2808: -- cpoint_rec.orig_system_reference := x_contact_point_id;
2809: email_rec.email_address := x_email_address;
2810: l_email_exists := NULL;
2990: x_party_id number;
2991:
2992: cursor c_address_country is
2993: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
2994: hz_parties party
2995: where psite.location_id = loc.location_id
2996: and loc.address1 = p_address1
2997: and loc.country = p_country
2998: and party.customer_key = l_party_key
3003: -- sranka 1/13/2003 created new cursor c_address_country_with_osr for including the orig_system_reference for duplication check
3004:
3005: cursor c_address_country_with_osr is
3006: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3007: hz_parties party
3008: where psite.location_id = loc.location_id
3009: and loc.address1 = p_address1
3010: and loc.country = p_country
3011: and party.customer_key = l_party_key
3015: and party.orig_system_reference = p_orig_system_reference;
3016:
3017: cursor c_country is
3018: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3019: hz_parties party
3020: where psite.location_id = loc.location_id
3021: and loc.country = p_country
3022: and party.customer_key = l_party_key
3023: and party.party_type = 'ORGANIZATION'
3028: -- created new cursor c_country_with_osr for including the orig_system_reference for duplication check
3029:
3030: cursor c_country_with_osr is
3031: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3032: hz_parties party
3033: where psite.location_id = loc.location_id
3034: and loc.country = p_country
3035: and party.customer_key = l_party_key
3036: and party.party_type = 'ORGANIZATION'
3039: and party.orig_system_reference = p_orig_system_reference;
3040:
3041:
3042: cursor c_customer_exists is
3043: select 'Y' from hz_parties
3044: where customer_key = l_party_key
3045: and status = 'A'
3046: and party_type = 'ORGANIZATION';
3047:
3047:
3048: -- sranka 1/13/2003
3049: -- created new cursor c_customer_exists_with_osr for including the orig_system_reference for duplication check
3050: cursor c_customer_exists_with_osr is
3051: select 'Y' from hz_parties
3052: where customer_key = l_party_key
3053: and party_type = 'ORGANIZATION'
3054: and status = 'A'
3055: AND orig_system_reference = p_orig_system_reference;
3055: AND orig_system_reference = p_orig_system_reference;
3056:
3057:
3058: cursor c_max_party is
3059: select max(party_id) from hz_parties
3060: where customer_key = l_party_key
3061: and status = 'A'
3062: and party_type = 'ORGANIZATION';
3063:
3063:
3064: -- sranka 1/13/2003
3065: -- created new cursor c_max_party_with_osr for including the orig_system_reference for duplication check
3066: cursor c_max_party_with_osr is
3067: select max(party_id) from hz_parties
3068: where customer_key = l_party_key
3069: and status = 'A'
3070: and party_type = 'ORGANIZATION'
3071: and orig_system_reference = p_orig_system_reference;
3606: l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
3607: l_transposed_phone_no varchar(60);
3608:
3609: cursor c_customer_exists is
3610: select 'Y' from hz_parties
3611: where customer_key = l_party_key
3612: and status = 'A'
3613: and party_type = 'PERSON';
3614:
3615: -- sranka 1/15/2003
3616: -- created new cursor c_address_country_with_osr for including the orig_system_reference for duplication check
3617:
3618: cursor c_customer_exists_with_osr is
3619: select 'Y' from hz_parties
3620: where customer_key = l_party_key
3621: and party_type = 'PERSON'
3622: and status = 'A'
3623: and orig_system_reference = p_orig_system_reference;
3624:
3625:
3626: cursor c_cont_email is
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
3625:
3626: cursor c_cont_email is
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'
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'
3636: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3637: -- sranka 3/21/2003
3638: -- made changes for supporting EMPLOYEE_OF" relationship
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'
3636: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3637: -- sranka 3/21/2003
3638: -- made changes for supporting EMPLOYEE_OF" relationship
3639: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3640: -- and rel.RELATIONSHIP_CODE = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
3645: and rel.SUBJECT_ID = per.PARTY_ID
3646: and per.customer_key = l_party_key
3647: and per.status = 'A'
3648: and cpoint.owner_table_id = rel.party_id
3649: and cpoint.owner_table_name = 'HZ_PARTIES'
3650: and cpoint.contact_point_type = 'EMAIL'
3651: and upper(cpoint.email_address) = upper(p_email_address)
3652: and cpoint.status = 'A';
3653:
3654: -- sranka 1/15/2003 created new cursor c_cont_email_with_osr for including the orig_system_reference for duplication check
3655:
3656: cursor c_cont_email_with_osr is
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
3655:
3656: cursor c_cont_email_with_osr is
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'
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'
3666: and rel.SUBJECT_TYPE = 'PERSON'
3667: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3668: -- sranka 3/21/2003
3669: -- made changes for supporting EMPLOYEE_OF" relationship
3663: and org.party_type = 'ORGANIZATION'
3664: and org.orig_system_reference = p_orig_system_reference
3665: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3666: and rel.SUBJECT_TYPE = 'PERSON'
3667: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3668: -- sranka 3/21/2003
3669: -- made changes for supporting EMPLOYEE_OF" relationship
3670: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3671: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3674: and rel.SUBJECT_ID = per.PARTY_ID
3675: and per.customer_key = l_party_key
3676: and per.status = 'A'
3677: and cpoint.owner_table_id = rel.party_id
3678: and cpoint.owner_table_name = 'HZ_PARTIES'
3679: and cpoint.contact_point_type = 'EMAIL'
3680: and upper(cpoint.email_address) = upper(p_email_address)
3681: and cpoint.status = 'A';
3682:
3682:
3683:
3684: cursor c_cont_email_phone is
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
3683:
3684: cursor c_cont_email_phone is
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
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'
3695: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3696: -- sranka 3/21/2003
3697: -- made changes for supporting EMPLOYEE_OF" relationship
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'
3695: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3696: -- sranka 3/21/2003
3697: -- made changes for supporting EMPLOYEE_OF" relationship
3698: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3699: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3702: and rel.SUBJECT_ID = per.PARTY_ID
3703: and per.customer_key = l_party_key
3704: and per.status = 'A'
3705: and cpoint.owner_table_id = rel.party_id
3706: and cpoint.owner_table_name = 'HZ_PARTIES'
3707: and cpoint.contact_point_type = 'EMAIL'
3708: and upper(cpoint.email_address) = upper(p_email_address)
3709: and cpoint.status = 'A'
3710: and cpoint1.owner_table_id = rel.party_id
3707: and cpoint.contact_point_type = 'EMAIL'
3708: and upper(cpoint.email_address) = upper(p_email_address)
3709: and cpoint.status = 'A'
3710: and cpoint1.owner_table_id = rel.party_id
3711: and cpoint1.owner_table_name = 'HZ_PARTIES'
3712: and cpoint1.contact_point_type = 'PHONE'
3713: and cpoint1.transposed_phone_number = l_transposed_phone_no
3714: -- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
3715: -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3719: -- sranka 1/15/2003 created new cursor c_cont_email_phone_with_osr for including the orig_system_reference for duplication check
3720:
3721: cursor c_cont_email_phone_with_osr is
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
3720:
3721: cursor c_cont_email_phone_with_osr is
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
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'
3732: and rel.SUBJECT_TYPE = 'PERSON'
3733: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3734: -- sranka 3/21/2003
3735: -- made changes for supporting EMPLOYEE_OF" relationship
3729: and org.party_type = 'ORGANIZATION'
3730: and org.orig_system_reference = p_orig_system_reference
3731: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3732: and rel.SUBJECT_TYPE = 'PERSON'
3733: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3734: -- sranka 3/21/2003
3735: -- made changes for supporting EMPLOYEE_OF" relationship
3736: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3737: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3740: and rel.SUBJECT_ID = per.PARTY_ID
3741: and per.customer_key = l_party_key
3742: and per.status = 'A'
3743: and cpoint.owner_table_id = rel.party_id
3744: and cpoint.owner_table_name = 'HZ_PARTIES'
3745: and cpoint.contact_point_type = 'EMAIL'
3746: and upper(cpoint.email_address) = upper(p_email_address)
3747: and cpoint.status = 'A'
3748: and cpoint1.owner_table_id = rel.party_id
3745: and cpoint.contact_point_type = 'EMAIL'
3746: and upper(cpoint.email_address) = upper(p_email_address)
3747: and cpoint.status = 'A'
3748: and cpoint1.owner_table_id = rel.party_id
3749: and cpoint1.owner_table_name = 'HZ_PARTIES'
3750: and cpoint1.contact_point_type = 'PHONE'
3751: and cpoint1.transposed_phone_number = l_transposed_phone_no
3752: -- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
3753: -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3756:
3757:
3758: cursor c_cont_phone is
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
3757:
3758: cursor c_cont_phone is
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'
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'
3768: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3769: -- sranka 3/21/2003
3770: -- made changes for supporting EMPLOYEE_OF" relationship
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'
3768: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3769: -- sranka 3/21/2003
3770: -- made changes for supporting EMPLOYEE_OF" relationship
3771: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3772: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3775: and rel.SUBJECT_ID = per.PARTY_ID
3776: and per.customer_key = l_party_key
3777: and per.status = 'A'
3778: and cpoint.owner_table_id = rel.party_id
3779: and cpoint.owner_table_name = 'HZ_PARTIES'
3780: and cpoint.contact_point_type = 'PHONE'
3781: and cpoint.transposed_phone_number = l_transposed_phone_no
3782: -- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
3783: -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3788:
3789:
3790: cursor c_cont_phone_with_osr is
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
3789:
3790: cursor c_cont_phone_with_osr is
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'
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'
3800: and rel.SUBJECT_TYPE = 'PERSON'
3801: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3802: -- sranka 3/21/2003
3803: -- made changes for supporting EMPLOYEE_OF" relationship
3797: and org.party_type = 'ORGANIZATION'
3798: and org.orig_system_reference = p_orig_system_reference
3799: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3800: and rel.SUBJECT_TYPE = 'PERSON'
3801: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3802: -- sranka 3/21/2003
3803: -- made changes for supporting EMPLOYEE_OF" relationship
3804: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3805: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3808: and rel.SUBJECT_ID = per.PARTY_ID
3809: and per.customer_key = l_party_key
3810: and per.status = 'A'
3811: and cpoint.owner_table_id = rel.party_id
3812: and cpoint.owner_table_name = 'HZ_PARTIES'
3813: and cpoint.contact_point_type = 'PHONE'
3814: and cpoint.transposed_phone_number = l_transposed_phone_no
3815: -- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
3816: -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3989: l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
3990:
3991:
3992: cursor c_customer_exists is
3993: select MAX(PARTY_ID) from hz_parties
3994: where customer_key = l_party_key
3995: and status = 'A'
3996: and party_type = 'PERSON';
3997:
3996: and party_type = 'PERSON';
3997:
3998: cursor c_rel_party_id is
3999: select max(rel.party_id) from
4000: hz_parties org,
4001: hz_relationships rel
4002: where org.party_id = p_org_party_id
4003: and org.party_type = 'ORGANIZATION'
4004: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4000: hz_parties org,
4001: hz_relationships rel
4002: where org.party_id = p_org_party_id
4003: and org.party_type = 'ORGANIZATION'
4004: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4005: and rel.SUBJECT_TYPE = 'PERSON'
4006: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4007: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
4008: and rel.OBJECT_ID = org.party_id
4002: where org.party_id = p_org_party_id
4003: and org.party_type = 'ORGANIZATION'
4004: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4005: and rel.SUBJECT_TYPE = 'PERSON'
4006: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4007: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
4008: and rel.OBJECT_ID = org.party_id
4009: and rel.SUBJECT_ID = l_per_party_id;
4010:
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'
4379: and cp.owner_table_id = p.party_id
4376: where p.customer_key = l_party_key
4377: and p.party_type = 'PERSON'
4378: and p.status = 'A'
4379: and cp.owner_table_id = p.party_id
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,
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'
4389: and p.orig_system_reference = p_orig_system_reference
4387: and p.party_type = 'PERSON'
4388: and p.status = 'A'
4389: and p.orig_system_reference = p_orig_system_reference
4390: and cp.owner_table_id = p.party_id
4391: and cp.owner_table_name = 'HZ_PARTIES'
4392: and upper(cp.email_address) = upper(p_email_address);
4393:
4394:
4395: cursor c_ph_number is
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'
4401: and cp.owner_table_id = p.party_id
4398: where p.customer_key = l_party_key
4399: and p.party_type = 'PERSON'
4400: and p.status = 'A'
4401: and cp.owner_table_id = p.party_id
4402: and cp.owner_table_name = 'HZ_PARTIES'
4403: and cp.transposed_phone_number = l_transposed_phone_no;
4404: /*
4405: and cp.phone_number = p_ph_number
4406: and nvl(cp.phone_country_code,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_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'
4416: and p.orig_system_reference = p_orig_system_reference
4414: and p.party_type = 'PERSON'
4415: and p.status = 'A'
4416: and p.orig_system_reference = p_orig_system_reference
4417: and cp.owner_table_id = p.party_id
4418: and cp.owner_table_name = 'HZ_PARTIES'
4419: and cp.transposed_phone_number = l_transposed_phone_no;
4420: /*
4421: and cp.phone_number = p_ph_number
4422: and nvl(cp.phone_country_code,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_code,'x')
4424: */
4425:
4426: cursor c_address_country is
4427: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
4428: hz_parties party
4429: where psite.location_id = loc.location_id
4430: and loc.address1 = p_address1
4431: and loc.country = p_country
4432: and party.customer_key = l_party_key
4435: and psite.party_id = party.party_id;
4436:
4437: cursor c_address_country_with_osr is
4438: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
4439: hz_parties party
4440: where psite.location_id = loc.location_id
4441: and loc.address1 = p_address1
4442: and loc.country = p_country
4443: and party.customer_key = l_party_key
4447: and psite.party_id = party.party_id;
4448:
4449:
4450: cursor c_person_exists is
4451: select 'Y' from hz_parties
4452: where customer_key = l_party_key
4453: and status = 'A'
4454: and party_type = 'PERSON';
4455:
4453: and status = 'A'
4454: and party_type = 'PERSON';
4455:
4456: cursor c_person_exists_with_osr is
4457: select 'Y' from hz_parties
4458: where customer_key = l_party_key
4459: and party_type = 'PERSON'
4460: and status = 'A'
4461: and orig_system_reference = p_orig_system_reference;
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'
4625: and p1.status = 'A'
4624: and p2.party_type = 'PERSON'
4625: and p1.status = 'A'
4626: and p2.status = 'A'
4627: and cp.owner_table_id = p1.party_id
4628: and cp.owner_table_name = 'HZ_PARTIES'
4629: and upper(cp.email_address) = upper(p_email_address)
4630: and p1.party_id = hr.party_id
4631: and hr.relationship_code = 'CONTACT_OF'
4632: and hr.subject_id = p2.party_id;
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'
4640: and p1.status = 'A'
4640: and p1.status = 'A'
4641: and p2.status = 'A'
4642: and p1.orig_system_reference = p_orig_system_reference
4643: and cp.owner_table_id = p1.party_id
4644: and cp.owner_table_name = 'HZ_PARTIES'
4645: and upper(cp.email_address) = upper(p_email_address)
4646: and p1.party_id = hr.party_id
4647: and hr.relationship_code = 'CONTACT_OF'
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'
4657: and p1.status = 'A'
4656: and p2.party_type = 'PERSON'
4657: and p1.status = 'A'
4658: and p2.status = 'A'
4659: and cp.owner_table_id = p1.party_id
4660: and cp.owner_table_name = 'HZ_PARTIES'
4661: and cp.transposed_phone_number = l_transposed_phone_no
4662: and p1.party_id = hr.party_id
4663: and hr.relationship_code = 'CONTACT_OF'
4664: and hr.subject_id = p2.party_id;
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'
4677: and p1.status = 'A'
4677: and p1.status = 'A'
4678: and p2.status = 'A'
4679: and p1.orig_system_reference = p_orig_system_reference
4680: and cp.owner_table_id = p1.party_id
4681: and cp.owner_table_name = 'HZ_PARTIES'
4682: and cp.transposed_phone_number = l_transposed_phone_no
4683: and p1.party_id = hr.party_id
4684: and hr.relationship_code = 'CONTACT_OF'
4685: and hr.subject_id = p2.party_id;
4690: */
4691:
4692: cursor c_address_country is
4693: select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4694: hz_parties party, hz_relationships hr, hz_parties p2
4695: where psite.location_id = loc.location_id
4696: and loc.address1 = p_address1
4697: and loc.country = p_country
4698: and party.customer_key = l_party_key
4706: and hr.subject_id = p2.party_id;
4707:
4708: cursor c_address_country_with_osr is
4709: select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4710: hz_parties party, hz_relationships hr, hz_parties p2
4711: where psite.location_id = loc.location_id
4712: and loc.address1 = p_address1
4713: and loc.country = p_country
4714: and party.customer_key = l_party_key
4723: and hr.subject_id = p2.party_id;
4724:
4725:
4726: cursor c_person_exists is
4727: select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4728: where hp2. customer_key = l_party_key
4729: and hp1.status = 'A'
4730: and hp2.status = 'A'
4731: and hp1.party_type = 'PARTY_RELATIONSHIP'
4734: and hr.subject_id = hp2.party_id
4735: and hp2.party_type = 'PERSON';
4736:
4737: cursor c_person_exists_with_osr is
4738: select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4739: where hp2. customer_key = l_party_key
4740: and hp1.status = 'A'
4741: and hp2.status = 'A'
4742: and hp1.party_type = 'PARTY_RELATIONSHIP'
4922: and col17 = p_country;
4923:
4924:
4925: cursor c_person_exists is
4926: select MAX(PARTY_ID) from hz_parties
4927: where customer_key = l_party_key
4928: and party_type = 'PERSON';
4929: begin
4930: