[Home] [Help]
21: --added for contact_point_type
22: g_contact_point_type VARCHAR2(100);
23: g_party_role_code VARCHAR2 (100) := 'CONTACT';
24: --this is to hold the party_id value for the hz_party_relationships table
25: --and then that value is used in the hz_contact_points.owner_table_id to find
26: --the contact_point id
27: g_owner_table_id number;
28: --siahmed 12.1.3 end of advanced sr creation
29:
1753: (relationship_code='CONTACT_OF' or relationship_code='EMPLOYEE_OF')
1754: and status='A';
1755: l_contact_id:=l_customer_id;
1756: select contact_point_id into l_contact_point_id
1757: from hz_contact_points
1758: where owner_table_name='HZ_PARTIES'
1759: and owner_table_id=l_customer_id
1760: and contact_point_type='EMAIL'
1761: and upper(email_address)=upper(l_sender)
1759: and owner_table_id=l_customer_id
1760: and contact_point_type='EMAIL'
1761: and upper(email_address)=upper(l_sender)
1762: and contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
1763: where contact_level_table='HZ_CONTACT_POINTS' and status='A');
1764:
1765:
1766: if g_statement_log then
1767: l_logmessage:='getting contact point id '||l_contact_point_id;
7358: ELSIF p_customer_phone is not null then
7359: BEGIN
7360: /*
7361: Select owner_table_id into x_customer_id
7362: From hz_contact_points
7363: where owner_table_name='HZ_PARTIES'
7364: and contact_point_type = 'PHONE'
7365: and phone_number =p_customer_phone;
7366: */
7364: and contact_point_type = 'PHONE'
7365: and phone_number =p_customer_phone;
7366: */
7367: Select owner_table_id into x_customer_id
7368: from hz_contact_points a, hz_parties b
7369: where a.owner_table_name='HZ_PARTIES'
7370: and a.contact_point_type='PHONE'
7371: and a.status='A'
7372: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
7369: where a.owner_table_name='HZ_PARTIES'
7370: and a.contact_point_type='PHONE'
7371: and a.status='A'
7372: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
7373: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
7374: and a.owner_table_id = b.party_id
7375: and b.party_type in ('PERSON', 'ORGANIZATION')
7376: and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_customer_phone,'([[:punct:]|[:space:]]*)');
7377:
7394: ELSIF p_customer_email is not null then
7395: BEGIN
7396: /*
7397: Select owner_table_id into x_customer_id
7398: From hz_contact_points
7399: where owner_table_name='HZ_PARTIES'
7400: and contact_point_type = 'EMAIL'
7401: and upper(email_address) =upper(p_customer_email);
7402: */
7403: l_logmessage:='p_customer_email address is '|| p_customer_email;
7404: iem_logger(l_logmessage);
7405: --dbms_output.put_line(l_logmessage);
7406: Select a.owner_table_id into x_customer_id
7407: from hz_contact_points a, hz_parties b
7408: where a.owner_table_name='HZ_PARTIES'
7409: and a.contact_point_type = 'EMAIL'
7410: and a.status='A'
7411: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
7408: where a.owner_table_name='HZ_PARTIES'
7409: and a.contact_point_type = 'EMAIL'
7410: and a.status='A'
7411: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
7412: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
7413: and a.owner_table_id = b.party_id
7414: and b.party_type in ('PERSON', 'ORGANIZATION')
7415: and upper(a.email_address) =upper(p_customer_email);
7416:
7515: --find the customer information.
7516: /* --old query
7517: Select distinct b.subject_id
7518: into g_customer_id
7519: from hz_contact_points a,hz_relationships b, hz_parties c
7520: where a.owner_table_name='HZ_PARTIES'
7521: and a.status='A'
7522: and a.primary_flag = 'Y'
7523: and a.owner_table_id = b.party_id
7522: and a.primary_flag = 'Y'
7523: and a.owner_table_id = b.party_id
7524: and directional_flag = 'B'
7525: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
7526: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
7527: and b.object_id = c.party_id
7528: and c.party_number = p_contact_number;
7529: */
7530: select upper(party_type) into l_party_type
7614: --i did distinct because there may be many contacts for a single party_id
7615: --since we are trying to find the party_id distinct is a valid assumption
7616: /*
7617: select distinct b.object_id into g_customer_id
7618: from hz_parties a, hz_party_relationships b, hz_contact_points c
7619: where c.phone_number = p_contact_phone
7620: where and reverse(c.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
7621: and a.party_id = b.subject_id
7622: and c.owner_table_id = b.party_id
7623: and c.contact_point_type = 'PHONE'
7624: and c.owner_table_name = 'HZ_PARTIES';
7625: */
7626: Select distinct b.subject_id into g_customer_id
7627: from hz_contact_points a,hz_relationships b, hz_parties c
7628: where a.owner_table_name='HZ_PARTIES'
7629: and a.contact_point_type='PHONE'
7630: and a.status='A'
7631: and a.primary_flag = 'Y'
7631: and a.primary_flag = 'Y'
7632: and a.owner_table_id = b.party_id
7633: and directional_flag = 'B'
7634: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
7635: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
7636: and b.object_id = c.party_id
7637: and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)');
7638: EXCEPTION
7639: when TOO_MANY_ROWS then
7656: --i did distinct because there may be many contacts for a single party_id
7657: --since we are trying to find the party_id distinct is a valid assumption
7658: /*
7659: select distinct b.object_id into g_customer_id
7660: from hz_parties a, hz_party_relationships b, hz_contact_points c
7661: where upper(c.email_address) =upper(p_contact_email)
7662: and a.party_id = b.subject_id
7663: and c.owner_table_id = b.party_id
7664: and c.contact_point_type = 'EMAIL'
7664: and c.contact_point_type = 'EMAIL'
7665: and c.owner_table_name = 'HZ_PARTIES';
7666: */
7667: Select distinct b.subject_id into g_customer_id
7668: from hz_contact_points a,hz_relationships b, hz_parties c
7669: where a.owner_table_name='HZ_PARTIES'
7670: and a.contact_point_type='EMAIL'
7671: and a.status='A'
7672: and a.primary_flag = 'Y'
7672: and a.primary_flag = 'Y'
7673: and a.owner_table_id = b.party_id
7674: and directional_flag = 'B'
7675: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
7676: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
7677: and b.object_id = c.party_id
7678: and upper(a.email_address) =upper(p_contact_email);
7679:
7680: EXCEPTION
7803:
7804: IF (g_customer_id IS NOT NULL) AND (p_customer_phone IS NOT NULL) THEN
7805: BEGIN
7806: Select contact_point_id into x_customer_phone_id
7807: From hz_contact_points
7808: where contact_point_type = 'PHONE'
7809: --and phone_number = p_customer_phone
7810: and reverse(transposed_phone_number)=REGEXP_REPLACE(p_customer_phone,'([[:punct:]|[:space:]]*)')
7811: and owner_table_id = g_customer_id;
7826: END;
7827: ELSE
7828: BEGIN
7829: Select contact_point_id into x_customer_phone_id
7830: From hz_contact_points
7831: where owner_table_name='HZ_PARTIES'
7832: and contact_point_type = 'PHONE'
7833: and reverse(transposed_phone_number)=REGEXP_REPLACE(p_customer_phone,'([[:punct:]|[:space:]]*)');
7834: EXCEPTION
7866: --convert email address to uppercase to prevent case relative mismatch
7867: IF (g_customer_id IS NOT Null) AND (p_customer_email IS NOT NULL) THEN
7868: BEGIN
7869: Select contact_point_id into x_customer_email_id
7870: From hz_contact_points
7871: where contact_point_type = 'EMAIL'
7872: and upper(email_address) =upper(p_customer_email)
7873: and owner_table_id = g_customer_id;
7874: EXCEPTION
7889:
7890: ELSE
7891: BEGIN
7892: Select contact_point_id into x_customer_email_id
7893: From hz_contact_points
7894: where owner_table_name='HZ_PARTIES'
7895: and contact_point_type = 'EMAIL'
7896: and upper(email_address) =upper(p_customer_email);
7897: EXCEPTION
8292: -- contact processing
8293: -------------------------------------------------------------
8294: --this are few essential information whihc is needed
8295: --party_id of type contact
8296: --contact_point_id from hz_contact_points of the contact
8297: --party_id is the object_id of the type contact_of from hz_parties.party_id
8298: --contact_point id is the contact_point_id from hz_contact_points where object_id
8299: --NOTE: Make sure we are not calling this procedure if the account type if of Internal
8300: --if its internal then we will just use the passed contact_party_id and contact_point_id
8294: --this are few essential information whihc is needed
8295: --party_id of type contact
8296: --contact_point_id from hz_contact_points of the contact
8297: --party_id is the object_id of the type contact_of from hz_parties.party_id
8298: --contact_point id is the contact_point_id from hz_contact_points where object_id
8299: --NOTE: Make sure we are not calling this procedure if the account type if of Internal
8300: --if its internal then we will just use the passed contact_party_id and contact_point_id
8301:
8302: Procedure getContactNumber (p_contact_number IN VARCHAR2,
8352: --i am using reglar expression here to check if there are any unwanted chars that are
8353: --passed by users and strip those off.
8354: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8355: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8356: from hz_contact_points a,hz_relationships b, hz_parties c
8357: where a.owner_table_name='HZ_PARTIES'
8358: and a.contact_point_type='PHONE'
8359: and a.status='A'
8360: and a.owner_table_id = b.party_id
8358: and a.contact_point_type='PHONE'
8359: and a.status='A'
8360: and a.owner_table_id = b.party_id
8361: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8362: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8363: and b.party_id = c.party_id
8364: and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
8365: --and c.party_number =p_contact_number
8366: and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
8371: --dbms_output.put_line(l_logmessage);
8372:
8373: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8374: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8375: from hz_contact_points a,hz_relationships b, hz_parties c
8376: where a.owner_table_name='HZ_PARTIES'
8377: and a.contact_point_type='PHONE'
8378: and a.status='A'
8379: and a.owner_table_id = b.party_id
8377: and a.contact_point_type='PHONE'
8378: and a.status='A'
8379: and a.owner_table_id = b.party_id
8380: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8381: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8382: and b.party_id = c.party_id
8383: --and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
8384: and c.party_number =p_contact_number
8385: and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
8414: --dbms_output.put_line(l_logmessage);
8415:
8416: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8417: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8418: from hz_contact_points a,hz_relationships b, hz_parties c
8419: where a.owner_table_name='HZ_PARTIES'
8420: and a.contact_point_type = 'EMAIL'
8421: and a.status='A'
8422: and a.owner_table_id = b.party_id
8420: and a.contact_point_type = 'EMAIL'
8421: and a.status='A'
8422: and a.owner_table_id = b.party_id
8423: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8424: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8425: and b.party_id = c.party_id
8426: and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
8427: --and c.party_number =p_contact_number
8428: and upper(c.email_address) = upper(p_contact_email)
8433: iem_logger(l_logmessage);
8434: --dbms_output.put_line(l_logmessage);
8435: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8436: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8437: from hz_contact_points a,hz_relationships b, hz_parties c
8438: where a.owner_table_name='HZ_PARTIES'
8439: and a.contact_point_type = 'EMAIL'
8440: and a.status='A'
8441: and a.owner_table_id = b.party_id
8439: and a.contact_point_type = 'EMAIL'
8440: and a.status='A'
8441: and a.owner_table_id = b.party_id
8442: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8443: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8444: and b.party_id = c.party_id
8445: --and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
8446: and c.party_number =p_contact_number
8447: and upper(c.email_address) = upper(p_contact_email)
8486: iem_logger(l_logmessage);
8487: --dbms_output.put_line(l_logmessage);
8488: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8489: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8490: from hz_contact_points a,hz_relationships b, hz_parties c
8491: where a.owner_table_name='HZ_PARTIES'
8492: and a.status='A'
8493: and a.primary_flag = 'Y'
8494: and a.owner_table_id = b.party_id
8492: and a.status='A'
8493: and a.primary_flag = 'Y'
8494: and a.owner_table_id = b.party_id
8495: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8496: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8497: and b.party_id = c.party_id
8498: and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
8499: -- and c.party_number =p_contact_number
8500: and b.subject_id = g_customer_id
8504: iem_logger(l_logmessage);
8505: --dbms_output.put_line(l_logmessage);
8506: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8507: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8508: from hz_contact_points a,hz_relationships b, hz_parties c
8509: where a.owner_table_name='HZ_PARTIES'
8510: and a.status='A'
8511: and a.primary_flag = 'Y'
8512: and a.owner_table_id = b.party_id
8510: and a.status='A'
8511: and a.primary_flag = 'Y'
8512: and a.owner_table_id = b.party_id
8513: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8514: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8515: and b.party_id = c.party_id
8516: --and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
8517: and c.party_number =p_contact_number
8518: and b.subject_id = g_customer_id
8573: IF (contact_rec.column_name = 'CONTACT_PHONE') THEN
8574: BEGIN
8575: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8576: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8577: from hz_contact_points a,hz_relationships b, hz_parties c
8578: where a.owner_table_name='HZ_PARTIES'
8579: and a.contact_point_type='PHONE'
8580: and a.status='A'
8581: and a.owner_table_id = b.party_id
8579: and a.contact_point_type='PHONE'
8580: and a.status='A'
8581: and a.owner_table_id = b.party_id
8582: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8583: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8584: and b.party_id = c.party_id
8585: and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))
8586: --and upper(c.party_name) =upper(p_contact_name)
8587: and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
8612: ELSIF (contact_rec.column_name = 'CONTACT_EMAIL') THEN
8613: BEGIN
8614: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8615: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8616: from hz_contact_points a,hz_relationships b, hz_parties c
8617: where a.owner_table_name='HZ_PARTIES'
8618: and a.contact_point_type = 'EMAIL'
8619: and a.status='A'
8620: and a.owner_table_id = b.party_id
8618: and a.contact_point_type = 'EMAIL'
8619: and a.status='A'
8620: and a.owner_table_id = b.party_id
8621: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8622: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8623: and b.party_id = c.party_id
8624: -- and upper(c.party_name) =upper(p_contact_name)
8625: and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))
8626: and upper(c.email_address) = upper(p_contact_email)
8657: IF x_contact_party_id IS NULL THEN
8658: BEGIN
8659: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8660: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8661: from hz_contact_points a,hz_relationships b, hz_parties c
8662: where a.owner_table_name='HZ_PARTIES'
8663: and a.status='A'
8664: and a.primary_flag = 'Y'
8665: and a.owner_table_id = b.party_id
8663: and a.status='A'
8664: and a.primary_flag = 'Y'
8665: and a.owner_table_id = b.party_id
8666: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8667: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8668: and b.party_id = c.party_id
8669: and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))
8670: --and upper(c.party_name) =upper(p_contact_name)
8671: and b.subject_id = g_customer_id;
8716: IF (g_contact_party_id IS NULL) THEN
8717: BEGIN
8718: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8719: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8720: from hz_contact_points a,hz_relationships b, hz_parties c
8721: where a.owner_table_name='HZ_PARTIES'
8722: and a.contact_point_type='PHONE'
8723: and a.status='A'
8724: and a.owner_table_id = b.party_id
8722: and a.contact_point_type='PHONE'
8723: and a.status='A'
8724: and a.owner_table_id = b.party_id
8725: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8726: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8727: and b.party_id = c.party_id
8728: and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
8729: and b.subject_id = g_customer_id;
8730: EXCEPTION
8762: IF (g_contact_party_id IS NULL) THEN
8763: BEGIN
8764: Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
8765: into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
8766: from hz_contact_points a,hz_relationships b, hz_parties c
8767: where a.owner_table_name='HZ_PARTIES'
8768: and a.contact_point_type='EMAIL'
8769: and a.status='A'
8770: and a.owner_table_id = b.party_id
8768: and a.contact_point_type='EMAIL'
8769: and a.status='A'
8770: and a.owner_table_id = b.party_id
8771: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
8772: where contact_level_table='HZ_CONTACT_POINTS' and status='A')
8773: and b.party_id = c.party_id
8774: and upper(a.email_address)=upper(p_contact_email)
8775: and b.subject_id = g_customer_id;
8776: EXCEPTION