[Home] [Help]
1748: if l_party_type='PARTY_RELATIONSHIP' then
1749: -- Try to identify the actual party here
1750: begin
1751: select object_id into l_party_id
1752: from HZ_RELATIONSHIPS where party_id= l_customer_id and
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
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
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:
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:
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:
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'
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'
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
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
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
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
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
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
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
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
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
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
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