[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:
1079: -- Nullify the created by module
1147: END IF;
1148: */
1149: ocon_rec.application_id := 530;
1150: ocon_rec.party_rel_rec.subject_type := 'PERSON';
1151: ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
1152: ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
1153: ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
1154:
1155:
1149: ocon_rec.application_id := 530;
1150: ocon_rec.party_rel_rec.subject_type := 'PERSON';
1151: ocon_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
1152: ocon_rec.party_rel_rec.object_type := 'ORGANIZATION';
1153: ocon_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
1154:
1155:
1156: -- sranka 1/15/2003
1157: -- assigning the value for the "orig_system_reference" for proper population of data in TCA while importing
1196: where PARTY_RELATIONSHIP_ID = l_rel_id ; -- x_party_rel_party_id;
1197:
1198: ocon_rec.org_contact_id := x_org_contact_id;
1199: select OBJECT_VERSION_NUMBER into l_party_obj_number
1200: from hz_parties
1201: where party_id = x_org_party_id;
1202: select OBJECT_VERSION_NUMBER into l_pr_obj_number
1203: from hz_relationships
1204: where relationship_id = l_rel_id and directional_flag = 'F'; -- x_party_rel_party_id;
1734: x_msg_count := null;
1735: x_msg_data := null;
1736: cpoint_rec.contact_point_type := 'EMAIL';
1737: cpoint_rec.status := 'A';
1738: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1739: cpoint_rec.owner_table_id := x_org_party_id;
1740: email_rec.email_address := org_email_rec.email_address;
1741: l_email_exists := NULL;
1742: open org_email_exists(x_org_party_id);
1775: x_msg_count := null;
1776: x_msg_data := null;
1777: cpoint_rec.contact_point_type := 'PHONE';
1778: cpoint_rec.status := 'A';
1779: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1780: cpoint_rec.owner_table_id := x_org_party_site_id;
1781: org_party_site_phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
1782: org_party_site_phone_rec.phone_number := org_phone_rec.phone_number;
1783: org_party_site_phone_rec.phone_country_code := org_phone_rec.phone_country_code;
1830: x_msg_count := null;
1831: x_msg_data := null;
1832: cpoint_rec.contact_point_type := 'PHONE';
1833: cpoint_rec.status := 'A';
1834: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1835: cpoint_rec.owner_table_id := x_org_party_id;
1836: phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
1837: phone_rec.phone_number := org_phone_rec.phone_number;
1838: phone_rec.phone_country_code := org_phone_rec.phone_country_code;
1880: x_msg_count := null;
1881: x_msg_data := null;
1882: cpoint_rec.contact_point_type := 'PHONE';
1883: cpoint_rec.status := 'A';
1884: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1885: if x_org_party_id is not null then
1886: cpoint_rec.owner_table_id := x_org_party_id;
1887: end if;
1888: if x_party_rel_party_id is not null then
1935: x_msg_count := null;
1936: x_msg_data := null;
1937: cpoint_rec.contact_point_type := 'PHONE';
1938: cpoint_rec.status := 'A';
1939: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1940: if x_org_party_id is not null then
1941: cpoint_rec.owner_table_id := x_org_party_id;
1942: end if;
1943: if x_party_rel_party_id is not null then
1989: x_msg_count := null;
1990: x_msg_data := null;
1991: cpoint_rec.contact_point_type := 'WEB';
1992: cpoint_rec.status := 'A';
1993: cpoint_rec.owner_table_name := 'HZ_PARTIES';
1994: if x_org_party_id is not null then
1995: cpoint_rec.owner_table_id := x_org_party_id;
1996: end if;
1997: web_rec.url := x_url;
2038: x_msg_count := null;
2039: x_msg_data := null;
2040: cpoint_rec.contact_point_type := 'EMAIL';
2041: cpoint_rec.status := 'A';
2042: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2043: if x_org_party_id is not null then
2044: cpoint_rec.owner_table_id := x_org_party_id;
2045: end if;
2046: if x_party_rel_party_id is not null then
2332: /*
2333: if x_generate_party_number = 'N' then
2334: select hz_party_number_s.nextval into x_party_number from dual;
2335: end if;
2336: select hz_parties_s.nextval into x_per_party_id from dual;
2337:
2338: person_rec.party_rec.party_number := x_party_number;
2339: person_rec.party_rec.party_id := x_per_party_id;
2340: */
2360: if (l_rec_update_flag = 'N' or (l_rec_update_flag = 'Y' and x_per_party_id is NULL)) then
2361: if x_generate_party_number = 'N' then
2362: select hz_party_number_s.nextval into x_party_number from dual;
2363: end if;
2364: select hz_parties_s.nextval into x_per_party_id from dual;
2365: person_rec.party_rec.party_number := x_party_number;
2366: person_rec.party_rec.party_id := x_per_party_id;
2367:
2368:
2388: else
2389: if l_rec_update_flag = 'Y' and x_per_party_id is not NULL then
2390: person_rec.party_rec.party_id := x_per_party_id;
2391: select OBJECT_VERSION_NUMBER into l_party_obj_number
2392: from hz_parties
2393: where party_id = x_per_party_id;
2394: person_rec.party_rec.orig_system_reference := null;
2395:
2396: -- Nullify the created by module
2662: x_msg_count := null;
2663: x_msg_data := null;
2664: cpoint_rec.contact_point_type := 'PHONE';
2665: cpoint_rec.status := 'A';
2666: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2667: cpoint_rec.owner_table_id := x_per_party_id;
2668: phone_rec.phone_line_type := nvl(x_phone_type,'GEN');
2669: phone_rec.phone_number := x_phone_number;
2670: phone_rec.phone_country_code := x_phone_country_code;
2712: x_msg_count := null;
2713: x_msg_data := null;
2714: cpoint_rec.contact_point_type := 'PHONE';
2715: cpoint_rec.status := 'A';
2716: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2717: cpoint_rec.owner_table_id := x_per_party_id;
2718: fax_rec.phone_line_type := 'FAX';--'GEN';
2719: fax_rec.phone_number := x_fax_number;
2720: fax_rec.phone_country_code := x_fax_country_code;
2761: x_msg_count := null;
2762: x_msg_data := null;
2763: cpoint_rec.contact_point_type := 'WEB';
2764: cpoint_rec.status := 'A';
2765: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2766: cpoint_rec.owner_table_id := x_per_party_id;
2767: web_rec.url := x_url;
2768: web_rec.web_type := 'com';
2769:
2809: x_msg_data := null;
2810: -- cpoint_rec.contact_point_id := x_contact_point_id;
2811: cpoint_rec.contact_point_type := 'EMAIL';
2812: cpoint_rec.status := 'A';
2813: cpoint_rec.owner_table_name := 'HZ_PARTIES';
2814: cpoint_rec.owner_table_id := x_per_party_id;
2815: -- cpoint_rec.orig_system_reference := x_contact_point_id;
2816: email_rec.email_address := x_email_address;
2817: l_email_exists := NULL;
2997: x_party_id number;
2998:
2999: cursor c_address_country is
3000: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3001: hz_parties party
3002: where psite.location_id = loc.location_id
3003: and loc.address1 = p_address1
3004: and loc.country = p_country
3005: and party.customer_key = l_party_key
3010: -- sranka 1/13/2003 created new cursor c_address_country_with_osr for including the orig_system_reference for duplication check
3011:
3012: cursor c_address_country_with_osr is
3013: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3014: hz_parties party
3015: where psite.location_id = loc.location_id
3016: and loc.address1 = p_address1
3017: and loc.country = p_country
3018: and party.customer_key = l_party_key
3022: and party.orig_system_reference = p_orig_system_reference;
3023:
3024: cursor c_country is
3025: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3026: hz_parties party
3027: where psite.location_id = loc.location_id
3028: and loc.country = p_country
3029: and party.customer_key = l_party_key
3030: and party.party_type = 'ORGANIZATION'
3035: -- created new cursor c_country_with_osr for including the orig_system_reference for duplication check
3036:
3037: cursor c_country_with_osr is
3038: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
3039: hz_parties party
3040: where psite.location_id = loc.location_id
3041: and loc.country = p_country
3042: and party.customer_key = l_party_key
3043: and party.party_type = 'ORGANIZATION'
3046: and party.orig_system_reference = p_orig_system_reference;
3047:
3048:
3049: cursor c_customer_exists is
3050: select 'Y' from hz_parties
3051: where customer_key = l_party_key
3052: and status = 'A'
3053: and party_type = 'ORGANIZATION';
3054:
3054:
3055: -- sranka 1/13/2003
3056: -- created new cursor c_customer_exists_with_osr for including the orig_system_reference for duplication check
3057: cursor c_customer_exists_with_osr is
3058: select 'Y' from hz_parties
3059: where customer_key = l_party_key
3060: and party_type = 'ORGANIZATION'
3061: and status = 'A'
3062: AND orig_system_reference = p_orig_system_reference;
3062: AND orig_system_reference = p_orig_system_reference;
3063:
3064:
3065: cursor c_max_party is
3066: select max(party_id) from hz_parties
3067: where customer_key = l_party_key
3068: and status = 'A'
3069: and party_type = 'ORGANIZATION';
3070:
3070:
3071: -- sranka 1/13/2003
3072: -- created new cursor c_max_party_with_osr for including the orig_system_reference for duplication check
3073: cursor c_max_party_with_osr is
3074: select max(party_id) from hz_parties
3075: where customer_key = l_party_key
3076: and status = 'A'
3077: and party_type = 'ORGANIZATION'
3078: and orig_system_reference = p_orig_system_reference;
3613: l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
3614: l_transposed_phone_no varchar(60);
3615:
3616: cursor c_customer_exists is
3617: select 'Y' from hz_parties
3618: where customer_key = l_party_key
3619: and status = 'A'
3620: and party_type = 'PERSON';
3621:
3622: -- sranka 1/15/2003
3623: -- created new cursor c_address_country_with_osr for including the orig_system_reference for duplication check
3624:
3625: cursor c_customer_exists_with_osr is
3626: select 'Y' from hz_parties
3627: where customer_key = l_party_key
3628: and party_type = 'PERSON'
3629: and status = 'A'
3630: and orig_system_reference = p_orig_system_reference;
3631:
3632:
3633: cursor c_cont_email is
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
3632:
3633: cursor c_cont_email is
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'
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'
3643: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3644: -- sranka 3/21/2003
3645: -- made changes for supporting EMPLOYEE_OF" relationship
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'
3643: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3644: -- sranka 3/21/2003
3645: -- made changes for supporting EMPLOYEE_OF" relationship
3646: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3647: -- and rel.RELATIONSHIP_CODE = NVL(ocon_rec.party_rel_rec.relationship_code,'CONTACT_OF')
3652: and rel.SUBJECT_ID = per.PARTY_ID
3653: and per.customer_key = l_party_key
3654: and per.status = 'A'
3655: and cpoint.owner_table_id = rel.party_id
3656: and cpoint.owner_table_name = 'HZ_PARTIES'
3657: and cpoint.contact_point_type = 'EMAIL'
3658: and upper(cpoint.email_address) = upper(p_email_address)
3659: and cpoint.status = 'A';
3660:
3661: -- sranka 1/15/2003 created new cursor c_cont_email_with_osr for including the orig_system_reference for duplication check
3662:
3663: cursor c_cont_email_with_osr is
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
3662:
3663: cursor c_cont_email_with_osr is
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'
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'
3673: and rel.SUBJECT_TYPE = 'PERSON'
3674: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3675: -- sranka 3/21/2003
3676: -- made changes for supporting EMPLOYEE_OF" relationship
3670: and org.party_type = 'ORGANIZATION'
3671: and org.orig_system_reference = p_orig_system_reference
3672: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3673: and rel.SUBJECT_TYPE = 'PERSON'
3674: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3675: -- sranka 3/21/2003
3676: -- made changes for supporting EMPLOYEE_OF" relationship
3677: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3678: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3681: and rel.SUBJECT_ID = per.PARTY_ID
3682: and per.customer_key = l_party_key
3683: and per.status = 'A'
3684: and cpoint.owner_table_id = rel.party_id
3685: and cpoint.owner_table_name = 'HZ_PARTIES'
3686: and cpoint.contact_point_type = 'EMAIL'
3687: and upper(cpoint.email_address) = upper(p_email_address)
3688: and cpoint.status = 'A';
3689:
3689:
3690:
3691: cursor c_cont_email_phone is
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
3690:
3691: cursor c_cont_email_phone is
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
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'
3702: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3703: -- sranka 3/21/2003
3704: -- made changes for supporting EMPLOYEE_OF" relationship
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'
3702: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3703: -- sranka 3/21/2003
3704: -- made changes for supporting EMPLOYEE_OF" relationship
3705: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3706: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3709: and rel.SUBJECT_ID = per.PARTY_ID
3710: and per.customer_key = l_party_key
3711: and per.status = 'A'
3712: and cpoint.owner_table_id = rel.party_id
3713: and cpoint.owner_table_name = 'HZ_PARTIES'
3714: and cpoint.contact_point_type = 'EMAIL'
3715: and upper(cpoint.email_address) = upper(p_email_address)
3716: and cpoint.status = 'A'
3717: and cpoint1.owner_table_id = rel.party_id
3714: and cpoint.contact_point_type = 'EMAIL'
3715: and upper(cpoint.email_address) = upper(p_email_address)
3716: and cpoint.status = 'A'
3717: and cpoint1.owner_table_id = rel.party_id
3718: and cpoint1.owner_table_name = 'HZ_PARTIES'
3719: and cpoint1.contact_point_type = 'PHONE'
3720: and cpoint1.transposed_phone_number = l_transposed_phone_no
3721: -- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
3722: -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3726: -- sranka 1/15/2003 created new cursor c_cont_email_phone_with_osr for including the orig_system_reference for duplication check
3727:
3728: cursor c_cont_email_phone_with_osr is
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
3727:
3728: cursor c_cont_email_phone_with_osr is
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
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'
3739: and rel.SUBJECT_TYPE = 'PERSON'
3740: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3741: -- sranka 3/21/2003
3742: -- made changes for supporting EMPLOYEE_OF" relationship
3736: and org.party_type = 'ORGANIZATION'
3737: and org.orig_system_reference = p_orig_system_reference
3738: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3739: and rel.SUBJECT_TYPE = 'PERSON'
3740: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3741: -- sranka 3/21/2003
3742: -- made changes for supporting EMPLOYEE_OF" relationship
3743: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3744: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3747: and rel.SUBJECT_ID = per.PARTY_ID
3748: and per.customer_key = l_party_key
3749: and per.status = 'A'
3750: and cpoint.owner_table_id = rel.party_id
3751: and cpoint.owner_table_name = 'HZ_PARTIES'
3752: and cpoint.contact_point_type = 'EMAIL'
3753: and upper(cpoint.email_address) = upper(p_email_address)
3754: and cpoint.status = 'A'
3755: and cpoint1.owner_table_id = rel.party_id
3752: and cpoint.contact_point_type = 'EMAIL'
3753: and upper(cpoint.email_address) = upper(p_email_address)
3754: and cpoint.status = 'A'
3755: and cpoint1.owner_table_id = rel.party_id
3756: and cpoint1.owner_table_name = 'HZ_PARTIES'
3757: and cpoint1.contact_point_type = 'PHONE'
3758: and cpoint1.transposed_phone_number = l_transposed_phone_no
3759: -- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
3760: -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3763:
3764:
3765: cursor c_cont_phone is
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
3764:
3765: cursor c_cont_phone is
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'
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'
3775: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3776: -- sranka 3/21/2003
3777: -- made changes for supporting EMPLOYEE_OF" relationship
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'
3775: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3776: -- sranka 3/21/2003
3777: -- made changes for supporting EMPLOYEE_OF" relationship
3778: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3779: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3782: and rel.SUBJECT_ID = per.PARTY_ID
3783: and per.customer_key = l_party_key
3784: and per.status = 'A'
3785: and cpoint.owner_table_id = rel.party_id
3786: and cpoint.owner_table_name = 'HZ_PARTIES'
3787: and cpoint.contact_point_type = 'PHONE'
3788: and cpoint.transposed_phone_number = l_transposed_phone_no
3789: -- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
3790: -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3795:
3796:
3797: cursor c_cont_phone_with_osr is
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
3796:
3797: cursor c_cont_phone_with_osr is
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'
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'
3807: and rel.SUBJECT_TYPE = 'PERSON'
3808: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3809: -- sranka 3/21/2003
3810: -- made changes for supporting EMPLOYEE_OF" relationship
3804: and org.party_type = 'ORGANIZATION'
3805: and org.orig_system_reference = p_orig_system_reference
3806: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3807: and rel.SUBJECT_TYPE = 'PERSON'
3808: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3809: -- sranka 3/21/2003
3810: -- made changes for supporting EMPLOYEE_OF" relationship
3811: -- and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
3812: and rel.RELATIONSHIP_CODE = NVL(p_relationship_code,'CONTACT_OF')
3815: and rel.SUBJECT_ID = per.PARTY_ID
3816: and per.customer_key = l_party_key
3817: and per.status = 'A'
3818: and cpoint.owner_table_id = rel.party_id
3819: and cpoint.owner_table_name = 'HZ_PARTIES'
3820: and cpoint.contact_point_type = 'PHONE'
3821: and cpoint.transposed_phone_number = l_transposed_phone_no
3822: -- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
3823: -- p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
3996: l_party_tbl hz_fuzzy_pub.PARTY_TBL_TYPE;
3997:
3998:
3999: cursor c_customer_exists is
4000: select MAX(PARTY_ID) from hz_parties
4001: where customer_key = l_party_key
4002: and status = 'A'
4003: and party_type = 'PERSON';
4004:
4003: and party_type = 'PERSON';
4004:
4005: cursor c_rel_party_id is
4006: select max(rel.party_id) from
4007: hz_parties org,
4008: hz_relationships rel
4009: where org.party_id = p_org_party_id
4010: and org.party_type = 'ORGANIZATION'
4011: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4007: hz_parties org,
4008: hz_relationships rel
4009: where org.party_id = p_org_party_id
4010: and org.party_type = 'ORGANIZATION'
4011: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4012: and rel.SUBJECT_TYPE = 'PERSON'
4013: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4014: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
4015: and rel.OBJECT_ID = org.party_id
4009: where org.party_id = p_org_party_id
4010: and org.party_type = 'ORGANIZATION'
4011: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4012: and rel.SUBJECT_TYPE = 'PERSON'
4013: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
4014: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
4015: and rel.OBJECT_ID = org.party_id
4016: and rel.SUBJECT_ID = l_per_party_id;
4017:
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'
4386: and cp.owner_table_id = p.party_id
4383: where p.customer_key = l_party_key
4384: and p.party_type = 'PERSON'
4385: and p.status = 'A'
4386: and cp.owner_table_id = p.party_id
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,
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'
4396: and p.orig_system_reference = p_orig_system_reference
4394: and p.party_type = 'PERSON'
4395: and p.status = 'A'
4396: and p.orig_system_reference = p_orig_system_reference
4397: and cp.owner_table_id = p.party_id
4398: and cp.owner_table_name = 'HZ_PARTIES'
4399: and upper(cp.email_address) = upper(p_email_address);
4400:
4401:
4402: cursor c_ph_number is
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'
4408: and cp.owner_table_id = p.party_id
4405: where p.customer_key = l_party_key
4406: and p.party_type = 'PERSON'
4407: and p.status = 'A'
4408: and cp.owner_table_id = p.party_id
4409: and cp.owner_table_name = 'HZ_PARTIES'
4410: and cp.transposed_phone_number = l_transposed_phone_no;
4411: /*
4412: and cp.phone_number = p_ph_number
4413: and nvl(cp.phone_country_code,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_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'
4423: and p.orig_system_reference = p_orig_system_reference
4421: and p.party_type = 'PERSON'
4422: and p.status = 'A'
4423: and p.orig_system_reference = p_orig_system_reference
4424: and cp.owner_table_id = p.party_id
4425: and cp.owner_table_name = 'HZ_PARTIES'
4426: and cp.transposed_phone_number = l_transposed_phone_no;
4427: /*
4428: and cp.phone_number = p_ph_number
4429: and nvl(cp.phone_country_code,nvl(p_ph_country_code,'x')) = nvl(p_ph_country_code,'x')
4431: */
4432:
4433: cursor c_address_country is
4434: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
4435: hz_parties party
4436: where psite.location_id = loc.location_id
4437: and loc.address1 = p_address1
4438: and loc.country = p_country
4439: and party.customer_key = l_party_key
4442: and psite.party_id = party.party_id;
4443:
4444: cursor c_address_country_with_osr is
4445: select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
4446: hz_parties party
4447: where psite.location_id = loc.location_id
4448: and loc.address1 = p_address1
4449: and loc.country = p_country
4450: and party.customer_key = l_party_key
4454: and psite.party_id = party.party_id;
4455:
4456:
4457: cursor c_person_exists is
4458: select 'Y' from hz_parties
4459: where customer_key = l_party_key
4460: and status = 'A'
4461: and party_type = 'PERSON';
4462:
4460: and status = 'A'
4461: and party_type = 'PERSON';
4462:
4463: cursor c_person_exists_with_osr is
4464: select 'Y' from hz_parties
4465: where customer_key = l_party_key
4466: and party_type = 'PERSON'
4467: and status = 'A'
4468: and orig_system_reference = p_orig_system_reference;
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'
4632: and p1.status = 'A'
4631: and p2.party_type = 'PERSON'
4632: and p1.status = 'A'
4633: and p2.status = 'A'
4634: and cp.owner_table_id = p1.party_id
4635: and cp.owner_table_name = 'HZ_PARTIES'
4636: and upper(cp.email_address) = upper(p_email_address)
4637: and p1.party_id = hr.party_id
4638: and hr.relationship_code = 'CONTACT_OF'
4639: and hr.subject_id = p2.party_id;
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'
4647: and p1.status = 'A'
4647: and p1.status = 'A'
4648: and p2.status = 'A'
4649: and p1.orig_system_reference = p_orig_system_reference
4650: and cp.owner_table_id = p1.party_id
4651: and cp.owner_table_name = 'HZ_PARTIES'
4652: and upper(cp.email_address) = upper(p_email_address)
4653: and p1.party_id = hr.party_id
4654: and hr.relationship_code = 'CONTACT_OF'
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'
4664: and p1.status = 'A'
4663: and p2.party_type = 'PERSON'
4664: and p1.status = 'A'
4665: and p2.status = 'A'
4666: and cp.owner_table_id = p1.party_id
4667: and cp.owner_table_name = 'HZ_PARTIES'
4668: and cp.transposed_phone_number = l_transposed_phone_no
4669: and p1.party_id = hr.party_id
4670: and hr.relationship_code = 'CONTACT_OF'
4671: and hr.subject_id = p2.party_id;
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'
4684: and p1.status = 'A'
4684: and p1.status = 'A'
4685: and p2.status = 'A'
4686: and p1.orig_system_reference = p_orig_system_reference
4687: and cp.owner_table_id = p1.party_id
4688: and cp.owner_table_name = 'HZ_PARTIES'
4689: and cp.transposed_phone_number = l_transposed_phone_no
4690: and p1.party_id = hr.party_id
4691: and hr.relationship_code = 'CONTACT_OF'
4692: and hr.subject_id = p2.party_id;
4697: */
4698:
4699: cursor c_address_country is
4700: select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4701: hz_parties party, hz_relationships hr, hz_parties p2
4702: where psite.location_id = loc.location_id
4703: and loc.address1 = p_address1
4704: and loc.country = p_country
4705: and party.customer_key = l_party_key
4713: and hr.subject_id = p2.party_id;
4714:
4715: cursor c_address_country_with_osr is
4716: select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4717: hz_parties party, hz_relationships hr, hz_parties p2
4718: where psite.location_id = loc.location_id
4719: and loc.address1 = p_address1
4720: and loc.country = p_country
4721: and party.customer_key = l_party_key
4730: and hr.subject_id = p2.party_id;
4731:
4732:
4733: cursor c_person_exists is
4734: select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4735: where hp2. customer_key = l_party_key
4736: and hp1.status = 'A'
4737: and hp2.status = 'A'
4738: and hp1.party_type = 'PARTY_RELATIONSHIP'
4741: and hr.subject_id = hp2.party_id
4742: and hp2.party_type = 'PERSON';
4743:
4744: cursor c_person_exists_with_osr is
4745: select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4746: where hp2. customer_key = l_party_key
4747: and hp1.status = 'A'
4748: and hp2.status = 'A'
4749: and hp1.party_type = 'PARTY_RELATIONSHIP'
4929: and col17 = p_country;
4930:
4931:
4932: cursor c_person_exists is
4933: select MAX(PARTY_ID) from hz_parties
4934: where customer_key = l_party_key
4935: and party_type = 'PERSON';
4936: begin
4937: