eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IEM_EMAIL_PROC_PVT dependencies on HZ_PARTIES

Line 831: select PARTY_TYPE into l_party_type from HZ_PARTIES

827: IF l_tag_custid>0 THEN
828: -- Tag DAta return customer contact details. So create the interaction by logging contact,primary party and
829: -- relation id.
830: BEGIN
831: select PARTY_TYPE into l_party_type from HZ_PARTIES
832: where party_id = l_tag_custid;
833: EXCEPTION WHEN OTHERS THEN
834: l_logmessage:='Error in getting Party Type for Party id '||l_tag_custid;
835: raise ABORT_PROCESSING;

Line 1746: select party_type into l_party_type from hz_parties

1742: iem_logger(l_logmessage);
1743: end if;
1744: if l_customer_id>0 then
1745: -- Check to see if party identified is a contact or not
1746: select party_type into l_party_type from hz_parties
1747: where party_id=l_customer_id;
1748: if l_party_type='PARTY_RELATIONSHIP' then
1749: -- Try to identify the actual party here
1750: begin

Line 1758: where owner_table_name='HZ_PARTIES'

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)
1762: and contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 6049: FROM hz_parties a

6045:
6046: CURSOR caller_type
6047: IS
6048: SELECT party_type
6049: FROM hz_parties a
6050: WHERE a.party_id = g_customer_id
6051: AND a.status = 'A'
6052: AND a.party_type IN ('ORGANIZATION','PERSON');
6053:

Line 6632: from hz_parties

6628: g_contact_point_id := p_contact_point_id;
6629: g_contact_point_type := 'EMAIL';
6630:
6631: select party_type into g_contact_party_type
6632: from hz_parties
6633: where party_id = p_contact_id
6634: and rownum = 1;
6635: EXCEPTION
6636: when TOO_MANY_ROWS then

Line 7297: from hz_parties where party_number = p_customer_number;

7293: IF g_customer_id is NULL THEN
7294: IF p_customer_number is not null THEN
7295: BEGIN
7296: select party_id into x_customer_id
7297: from hz_parties where party_number = p_customer_number;
7298: EXCEPTION
7299: when TOO_MANY_ROWS then
7300: x_customer_id := null;
7301: l_logmessage:='p_customer_number fetch returned too many rows'|| SQL%ROWCOUNT;

Line 7320: from hz_parties where UPPER(party_name) = UPPER(p_customer_name);

7316: --it would be nice to have bitmap index on party_name for performance reasons
7317: ELSIF p_customer_name is not null then
7318: BEGIN
7319: select party_id into x_customer_id
7320: from hz_parties where UPPER(party_name) = UPPER(p_customer_name);
7321: EXCEPTION
7322: when TOO_MANY_ROWS then
7323: x_customer_id := null;
7324: l_logmessage:='p_customer_name fetch returned too many rows'|| SQL%ROWCOUNT;

Line 7363: where owner_table_name='HZ_PARTIES'

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: */
7367: Select owner_table_id into x_customer_id

Line 7368: from hz_contact_points a, hz_parties b

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

Line 7369: where a.owner_table_name='HZ_PARTIES'

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
7373: where contact_level_table='HZ_CONTACT_POINTS' and status='A')

Line 7399: where owner_table_name='HZ_PARTIES'

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;

Line 7407: from hz_contact_points a, hz_parties b

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

Line 7408: where a.owner_table_name='HZ_PARTIES'

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
7412: where contact_level_table='HZ_CONTACT_POINTS' and status='A')

Line 7519: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 7520: where a.owner_table_name='HZ_PARTIES'

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
7524: and directional_flag = 'B'

Line 7531: from hz_parties

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
7531: from hz_parties
7532: where party_number = p_contact_number;
7533:
7534: l_logmessage:='p_contact_number processing for party and party_type is '|| l_party_type;
7535: iem_logger(l_logmessage);

Line 7541: from hz_relationships a, hz_parties b

7537:
7538: IF (l_party_type = 'PARTY_RELATIONSHIP') THEN
7539: select distinct a.subject_id
7540: into g_customer_id
7541: from hz_relationships a, hz_parties b
7542: where a.party_id = b.party_id
7543: and directional_flag = 'B'
7544: and b.party_number = p_contact_number;
7545:

Line 7552: from hz_relationships a, hz_parties b

7548: --dbms_output.put_line(l_logmessage);
7549: ELSIF (l_party_type = 'PERSON') THEN
7550: select distinct a.subject_id
7551: into g_customer_id
7552: from hz_relationships a, hz_parties b
7553: where a.object_id = b.party_id
7554: and directional_flag = 'B'
7555: and b.party_number = p_contact_number;
7556:

Line 7562: from hz_parties

7558: iem_logger(l_logmessage);
7559: --dbms_output.put_line(l_logmessage);
7560: ELSIF (l_party_type = 'ORGANIZATION') THEN
7561: select party_id into g_customer_id
7562: from hz_parties
7563: where party_number = p_contact_number;
7564:
7565: l_logmessage:='p_contact_number inside ORGANIZATION ';
7566: iem_logger(l_logmessage);

Line 7591: from hz_relationships a, hz_parties b

7587: --i did distinct because there may be many contacts for a single party_id
7588: --since we are trying to find the party_id of type organization or person here a distinct is a better match
7589: select distinct a.subject_id
7590: into g_customer_id
7591: from hz_relationships a, hz_parties b
7592: where a.object_id = b.party_id
7593: and directional_flag = 'B'
7594: and upper(b.party_name) = upper(p_contact_name);
7595:

Line 7618: from hz_parties a, hz_party_relationships b, hz_contact_points c

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

Line 7624: and c.owner_table_name = 'HZ_PARTIES';

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'

Line 7627: from hz_contact_points a,hz_relationships b, hz_parties c

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'

Line 7628: where a.owner_table_name='HZ_PARTIES'

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'
7632: and a.owner_table_id = b.party_id

Line 7660: from hz_parties a, hz_party_relationships b, hz_contact_points c

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'

Line 7665: and c.owner_table_name = 'HZ_PARTIES';

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'
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'

Line 7668: from hz_contact_points a,hz_relationships b, hz_parties c

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'

Line 7669: where a.owner_table_name='HZ_PARTIES'

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'
7673: and a.owner_table_id = b.party_id

Line 7831: where owner_table_name='HZ_PARTIES'

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
7835: when TOO_MANY_ROWS then

Line 7894: where owner_table_name='HZ_PARTIES'

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
7898: when TOO_MANY_ROWS then

Line 8297: --party_id is the object_id of the type contact_of from hz_parties.party_id

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
8301:

Line 8326: from hz_parties

8322: --dbms_output.put_line(l_logmessage);
8323:
8324: --check what is the contact type for the contact number
8325: select upper(party_type) into l_party_type
8326: from hz_parties
8327: where party_number = p_contact_number;
8328:
8329: l_logmessage:='contact_party_type is ' || l_party_type ;
8330: iem_logger(l_logmessage);

Line 8356: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8357: where a.owner_table_name='HZ_PARTIES'

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
8361: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8364: and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)

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:]]*)')
8367: and b.subject_id = g_customer_id;
8368: ELSIF (l_party_type = 'PARTY_RELATIONSHIP') THEN

Line 8375: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8376: where a.owner_table_name='HZ_PARTIES'

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
8380: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8383: --and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)

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:]]*)')
8386: and b.subject_id = g_customer_id;
8387: END IF;

Line 8418: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8419: where a.owner_table_name='HZ_PARTIES'

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
8423: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8426: and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)

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)
8429: and b.subject_id = g_customer_id;
8430:

Line 8437: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8438: where a.owner_table_name='HZ_PARTIES'

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
8442: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8445: --and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)

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)
8448: and b.subject_id = g_customer_id;
8449: END IF;

Line 8490: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8491: where a.owner_table_name='HZ_PARTIES'

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
8495: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8498: and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)

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
8501: and rownum = 1;
8502: ELSIF (l_party_type = 'PARTY_RELATIONSHIP') THEN

Line 8508: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8509: where a.owner_table_name='HZ_PARTIES'

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
8513: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8516: --and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)

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
8519: and rownum = 1;
8520: END IF;

Line 8577: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8578: where a.owner_table_name='HZ_PARTIES'

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
8582: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8585: and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))

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:]]*)')
8588: and b.subject_id = g_customer_id;
8589: l_logmessage:='cotanct_name-phone'||p_contact_name||'--partyid:'||x_contact_party_id||'point_id'||x_contact_point_id ;

Line 8616: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8617: where a.owner_table_name='HZ_PARTIES'

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
8621: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8625: and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))

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)
8627: and b.subject_id = g_customer_id;
8628: l_logmessage:='cotanct_name-email'||p_contact_name||'--partyid:'||x_contact_party_id||'point_id'||x_contact_point_id ;
8629: iem_logger(l_logmessage);

Line 8661: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8662: where a.owner_table_name='HZ_PARTIES'

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
8666: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8669: and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))

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;
8672: l_logmessage:='cotanct_name-alone'||p_contact_name||'--partyid:'||x_contact_party_id||'point_id'||x_contact_point_id ;
8673: iem_logger(l_logmessage);

Line 8720: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8721: where a.owner_table_name='HZ_PARTIES'

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
8725: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 8766: from hz_contact_points a,hz_relationships b, hz_parties c

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

Line 8767: where a.owner_table_name='HZ_PARTIES'

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
8771: and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES

Line 9063: hz_parties p,

9059:
9060: BEGIN
9061: SELECT s.party_site_id into x_bill_to_site_id
9062: FROM hz_party_sites s,
9063: hz_parties p,
9064: hz_locations l,
9065: hz_party_site_uses u
9066: WHERE p.party_id = p_customer_id
9067: AND p.party_id = s.party_id

Line 9101: hz_parties p,

9097:
9098: BEGIN
9099: SELECT s.party_site_id into x_ship_to_site_id
9100: FROM hz_party_sites s,
9101: hz_parties p,
9102: hz_locations l,
9103: hz_party_site_uses u
9104: WHERE p.party_id = p_customer_id
9105: AND p.party_id = s.party_id