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.AMS_LISTIMPORT_PVT dependencies on HZ_PARTIES

Line 4887: AND owner_table_name = 'HZ_PARTIES'

4883: CURSOR phone_exists (x_hz_party_id number) IS
4884: SELECT 'Y' FROM hz_contact_points
4885: WHERE contact_point_type = 'PHONE'
4886: AND phone_line_type = 'GEN'
4887: AND owner_table_name = 'HZ_PARTIES'
4888: AND owner_table_id = x_hz_party_id
4889: AND phone_number = x_phone_number
4890: AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
4891: AND NVL(phone_area_code,'x') = NVL(x_phone_area_code,'x')

Line 4898: AND owner_table_name = 'HZ_PARTIES'

4894:
4895: CURSOR email_exists (x_hz_party_id number) IS
4896: SELECT 'Y' FROM hz_contact_points
4897: WHERE contact_point_type = 'EMAIL'
4898: AND owner_table_name = 'HZ_PARTIES'
4899: AND owner_table_id = x_hz_party_id
4900: AND email_address = x_email_address;
4901:
4902: CURSOR PER_LOCATION_EXISTS IS

Line 5166: FROM hz_parties

5162: org_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';
5163: org_rec.application_id := 530;
5164: -- SELECT last_update_date INTO l_last_update_date
5165: SELECT OBJECT_VERSION_NUMBER INTO l_object_version
5166: FROM hz_parties
5167: WHERE party_id= x_org_party_id;
5168: x_date1 := l_last_update_date;
5169: org_rec.party_rec.status := 'A';
5170: hz_party_v2pub.update_organization(

Line 5200: FROM HZ_PARTIES

5196: SELECT subject_id into x_person_id FROM hz_relationships WHERE subject_type = 'PERSON'
5197: AND party_id = p_party_id;
5198: -- SELECT last_update_date INTO l_last_update_date1
5199: SELECT OBJECT_VERSION_NUMBER INTO l_object_version
5200: FROM HZ_PARTIES
5201: WHERE party_id = x_person_id;
5202: x_date1 := l_last_update_date1;
5203: person_rec.party_rec.party_id := x_person_id;
5204: person_rec.party_rec.status := 'A';

Line 5239: select last_update_date into x_date_three from hz_parties where party_id = p_party_id;

5235:
5236: SELECT ORG_CONTACT_ID, OBJECT_VERSION_NUMBER into x_org_contact_id ,l_object_version3 FROM hz_org_contacts WHERE
5237: PARTY_RELATIONSHIP_ID = x_relationship_id;
5238:
5239: select last_update_date into x_date_three from hz_parties where party_id = p_party_id;
5240: -- ocon_rec.status := 'A';
5241: ocon_rec.org_contact_id := x_org_contact_id;
5242: ocon_rec.party_rel_rec.status := 'A';
5243: ocon_rec.CREATED_BY_MODULE := 'AMS_LIST_IMPORT';

Line 5381: cpoint_rec.owner_table_name := 'HZ_PARTIES';

5377: x_msg_count := null;
5378: x_msg_data := null;
5379: cpoint_rec.contact_point_type := 'PHONE';
5380: cpoint_rec.status := 'A';
5381: cpoint_rec.owner_table_name := 'HZ_PARTIES';
5382: cpoint_rec.owner_table_id := x_party_rel_party_id;
5383: phone_rec.phone_line_type := 'GEN';
5384: phone_rec.phone_number := x_phone_number;
5385: phone_rec.phone_country_code := x_phone_country_code;

Line 5421: cpoint_rec.owner_table_name := 'HZ_PARTIES';

5417: x_msg_count := null;
5418: x_msg_data := null;
5419: cpoint_rec.contact_point_type := 'EMAIL';
5420: cpoint_rec.status := 'A';
5421: cpoint_rec.owner_table_name := 'HZ_PARTIES';
5422: cpoint_rec.owner_table_id := x_party_rel_party_id;
5423: email_rec.email_address := x_email_address;
5424: l_email_exists := NULL;
5425: open email_exists(x_party_rel_party_id);

Line 5567: FROM HZ_PARTIES

5563:
5564: -- SELECT last_update_date INTO l_last_update_date1
5565: l_object_version := NULL;
5566: SELECT OBJECT_VERSION_NUMBER INTO l_object_version
5567: FROM HZ_PARTIES
5568: WHERE party_id = p_party_id;
5569: x_date1 := l_last_update_date1;
5570: person_rec.party_rec.party_id := p_party_id;
5571: person_rec.party_rec.status := 'A';

Line 5680: cpoint_rec.owner_table_name := 'HZ_PARTIES';

5676: x_msg_count := null;
5677: x_msg_data := null;
5678: cpoint_rec.contact_point_type := 'PHONE';
5679: cpoint_rec.status := 'A';
5680: cpoint_rec.owner_table_name := 'HZ_PARTIES';
5681: cpoint_rec.owner_table_id := x_per_party_id;
5682: phone_rec.phone_line_type := 'GEN';
5683: phone_rec.phone_number := x_phone_number;
5684: phone_rec.phone_country_code := x_phone_country_code;

Line 5721: cpoint_rec.owner_table_name := 'HZ_PARTIES';

5717: x_msg_count := null;
5718: x_msg_data := null;
5719: cpoint_rec.contact_point_type := 'EMAIL';
5720: cpoint_rec.status := 'A';
5721: cpoint_rec.owner_table_name := 'HZ_PARTIES';
5722: cpoint_rec.owner_table_id := x_per_party_id;
5723: email_rec.email_address := x_email_address;
5724: l_email_exists := NULL;
5725: open email_exists(x_per_party_id);

Line 13961: set org_party_id = (select party_id from hz_parties where party_type = 'ORGANIZATION' and party_id = b2b.party_id and nvl(status,'A') = 'A') -- bug 5100612

13957: and org_party_id is null;
13958:
13959: --if party_id is of type organization then update org_party_id with party_id
13960: update ams_hz_b2b_mapping_v b2b
13961: set org_party_id = (select party_id from hz_parties where party_type = 'ORGANIZATION' and party_id = b2b.party_id and nvl(status,'A') = 'A') -- bug 5100612
13962: where import_list_header_id = p_import_list_header_id
13963: and party_id is not null
13964: and org_party_id is null;
13965:

Line 13981: SET person_party_id = (select party_id from hz_parties where party_type = 'PERSON' and party_id = b2c.party_id and nvl(status,'A') = 'A') -- bug 5100612,

13977:
13978: --aanjaria: bug 4913239: copy party_id to person_party_id so that existence check is by passed
13979: -- mayjain bug 5100612 this update should be done only if the party_id is a valid one.
13980: UPDATE ams_hz_b2c_mapping_v b2c
13981: SET person_party_id = (select party_id from hz_parties where party_type = 'PERSON' and party_id = b2c.party_id and nvl(status,'A') = 'A') -- bug 5100612,
13982: WHERE import_list_header_id = p_import_list_header_id
13983: AND party_id is not null
13984: AND person_party_id is null;
13985:

Line 18571: and exists (select 1 from hz_parties pty

18567: where b2b.import_list_header_id = 40236
18568: -- and nvl(b2b.org_exist,'N') = 'N'
18569: and b2b.org_key is not null
18570: and b2b.load_status in ('ACTIVE','RELOAD')
18571: and exists (select 1 from hz_parties pty
18572: where pty.customer_key = b2b.org_key
18573: and pty.party_type = 'ORGANIZATION'
18574: and pty.status = 'A')
18575: group by b2b.org_key;

Line 18581: hz_parties party, ams_hz_b2b_mapping_v b2b

18577:
18578: cursor c_address_country is
18579: select b2b.import_source_line_id, max(psite.party_id)
18580: from hz_party_sites psite, hz_locations loc,
18581: hz_parties party, ams_hz_b2b_mapping_v b2b
18582: where b2b.import_list_header_id = p_import_list_header_id
18583: and b2b.load_status in ('ACTIVE','RELOAD')
18584: and b2b.org_exist = 'Y'
18585: and b2b.address1 is not null

Line 18600: hz_parties party, ams_hz_b2b_mapping_v b2b

18596:
18597: cursor c_country is
18598: select b2b.import_source_line_id, max(psite.party_id)
18599: from hz_party_sites psite, hz_locations loc,
18600: hz_parties party, ams_hz_b2b_mapping_v b2b
18601: where b2b.import_list_header_id = p_import_list_header_id
18602: and b2b.load_status in ('ACTIVE','RELOAD')
18603: and b2b.org_exist = 'Y'
18604: and b2b.country is not null

Line 18616: from hz_parties pty, ams_hz_b2b_mapping_v b2b

18612: group by b2b.import_source_line_id;
18613:
18614: cursor c_max_party is
18615: select b2b.import_source_line_id, max(pty. party_id)
18616: from hz_parties pty, ams_hz_b2b_mapping_v b2b
18617: where b2b.import_list_header_id = p_import_list_header_id
18618: and b2b.load_status in ('ACTIVE','RELOAD')
18619: and b2b.org_exist = 'Y'
18620: and b2b.org_party_id is null

Line 18836: from hz_parties pty, ams_hz_b2b_mapping_v b2b

18832:
18833:
18834: cursor c_customer_exists is
18835: select b2b.org_key, 'Y'
18836: from hz_parties pty, ams_hz_b2b_mapping_v b2b
18837: where b2b.import_list_header_id = p_import_list_header_id
18838: and pty.customer_key = b2b.org_key
18839: and b2b.org_key is not null
18840: and b2b.org_party_id is null

Line 18851: hz_parties party, ams_hz_b2b_mapping_v b2b

18847:
18848: cursor c_address_country is
18849: select b2b.import_source_line_id, max(psite.party_id)
18850: from hz_party_sites psite, hz_locations loc,
18851: hz_parties party, ams_hz_b2b_mapping_v b2b
18852: where b2b.import_list_header_id = p_import_list_header_id
18853: and b2b.load_status in ('ACTIVE','RELOAD')
18854: and b2b.org_exist = 'Y'
18855: and b2b.address1 is not null

Line 18871: hz_parties party, ams_hz_b2b_mapping_v b2b

18867:
18868: cursor c_country is
18869: select b2b.import_source_line_id, max(psite.party_id)
18870: from hz_party_sites psite, hz_locations loc,
18871: hz_parties party, ams_hz_b2b_mapping_v b2b
18872: where b2b.import_list_header_id = p_import_list_header_id
18873: and b2b.load_status in ('ACTIVE','RELOAD')
18874: and b2b.org_exist = 'Y'
18875: and b2b.country is not null

Line 18888: from hz_parties pty, ams_hz_b2b_mapping_v b2b

18884: group by b2b.import_source_line_id;
18885:
18886: cursor c_max_party is
18887: select b2b.import_source_line_id, max(pty. party_id)
18888: from hz_parties pty, ams_hz_b2b_mapping_v b2b
18889: where b2b.import_list_header_id = p_import_list_header_id
18890: and b2b.load_status in ('ACTIVE','RELOAD')
18891: and b2b.org_exist = 'Y'
18892: and b2b.org_party_id is null

Line 19114: from hz_parties pty, ams_hz_b2c_mapping_v b2c

19110: and b2c.PERSON_PARTY_ID is null;
19111:
19112: cursor c_person_exists is
19113: select distinct b2c.import_source_line_id, 'Y'
19114: from hz_parties pty, ams_hz_b2c_mapping_v b2c
19115: where b2c.import_list_header_id = p_import_list_header_id
19116: and pty.customer_key = b2c.person_key
19117: and b2c.person_key is not null
19118: and b2c.PERSON_PARTY_ID is null

Line 19125: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c

19121: and pty.party_type = 'PERSON';
19122:
19123: cursor c_email_address is
19124: select b2c.import_source_line_id, max(p.party_id)
19125: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c
19126: where b2c.import_list_header_id = p_import_list_header_id
19127: and b2c.load_status in ('ACTIVE','RELOAD')
19128: and b2c.person_exist = 'Y'
19129: and b2c.email_address is not null

Line 19135: and cp.owner_table_name = 'HZ_PARTIES'

19131: and p.customer_key = b2c.person_key
19132: and p.party_type = 'PERSON'
19133: and p.status = 'A'
19134: and cp.owner_table_id = p.party_id
19135: and cp.owner_table_name = 'HZ_PARTIES'
19136: and upper(cp.email_address) = upper(b2c.email_address)
19137: --and cp.primary_flag = 'Y' -- Hbandi Commented for fixing the bug #8726177 (list import creates new party when imported phone number does not match primary)
19138: group by b2c.import_source_line_id;
19139:

Line 19142: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c

19138: group by b2c.import_source_line_id;
19139:
19140: cursor c_ph_number is
19141: select b2c.import_source_line_id, max(p.party_id)
19142: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c
19143: where b2c.import_list_header_id = p_import_list_header_id
19144: and b2c.load_status in ('ACTIVE','RELOAD')
19145: and b2c.person_exist = 'Y'
19146: and b2c.phone_number is not null

Line 19152: and cp.owner_table_name = 'HZ_PARTIES'

19148: and p.customer_key = b2c.person_key
19149: and p.party_type = 'PERSON'
19150: and p.status = 'A'
19151: and cp.owner_table_id = p.party_id
19152: and cp.owner_table_name = 'HZ_PARTIES'
19153: -- and cp.primary_flag = 'Y' -- Hbandi Commented for fixing the bug #8726177 (list import creates new party when imported phone number does not match primary)
19154: and cp.transposed_phone_number = b2c.transposed_phone_number
19155: -- and cp.phone_number = b2c.phone_number
19156: -- and nvl(cp.phone_country_code,nvl(b2c.phone_country_code,'x')) = nvl(b2c.phone_country_code,'x')

Line 19162: from hz_party_sites psite, hz_locations loc, hz_parties party, ams_hz_b2c_mapping_v b2c

19158: group by b2c.import_source_line_id;
19159:
19160: cursor c_address_country is
19161: select b2c.import_source_line_id, max(psite.party_id)
19162: from hz_party_sites psite, hz_locations loc, hz_parties party, ams_hz_b2c_mapping_v b2c
19163: where b2c.import_list_header_id = p_import_list_header_id
19164: and b2c.load_status in ('ACTIVE','RELOAD')
19165: and b2c.person_exist = 'Y'
19166: and b2c.address1 is not null

Line 19433: and exists (select 1 from hz_parties pty

19429: where b2c.import_list_header_id = p_import_list_header_id
19430: and b2c.person_key is not null
19431: and b2c.PERSON_PARTY_ID is null
19432: and b2c.load_status in ('ACTIVE','RELOAD')
19433: and exists (select 1 from hz_parties pty
19434: where pty.customer_key = b2c.person_key
19435: and pty.status = 'A'
19436: and pty.party_type = 'PERSON');
19437:

Line 19440: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c

19436: and pty.party_type = 'PERSON');
19437:
19438: cursor c_email_address is
19439: select b2c.import_source_line_id, max(p.party_id)
19440: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c
19441: where b2c.import_list_header_id = p_import_list_header_id
19442: and b2c.load_status in ('ACTIVE','RELOAD')
19443: and b2c.person_exist = 'Y'
19444: and b2c.orig_system_reference = p.orig_system_reference

Line 19450: and cp.owner_table_name = 'HZ_PARTIES'

19446: and p.customer_key = b2c.person_key
19447: and p.party_type = 'PERSON'
19448: and p.status = 'A'
19449: and cp.owner_table_id = p.party_id
19450: and cp.owner_table_name = 'HZ_PARTIES'
19451: and upper(cp.email_address) = upper(b2c.email_address)
19452: and cp.primary_flag = 'Y'
19453: group by b2c.import_source_line_id;
19454:

Line 19457: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c

19453: group by b2c.import_source_line_id;
19454:
19455: cursor c_ph_number is
19456: select b2c.import_source_line_id, max(p.party_id)
19457: from hz_contact_points cp, hz_parties p, ams_hz_b2c_mapping_v b2c
19458: where b2c.import_list_header_id = p_import_list_header_id
19459: and b2c.load_status in ('ACTIVE','RELOAD')
19460: and b2c.person_exist = 'Y'
19461: and b2c.phone_number is not null

Line 19468: and cp.owner_table_name = 'HZ_PARTIES'

19464: and p.customer_key = b2c.person_key
19465: and p.party_type = 'PERSON'
19466: and p.status = 'A'
19467: and cp.owner_table_id = p.party_id
19468: and cp.owner_table_name = 'HZ_PARTIES'
19469: and cp.primary_flag = 'Y'
19470: and cp.transposed_phone_number = b2c.transposed_phone_number
19471: -- and cp.phone_number = b2c.phone_number
19472: -- and nvl(cp.phone_country_code,nvl(b2c.phone_country_code,'x')) = nvl(b2c.phone_country_code,'x')

Line 19478: from hz_party_sites psite, hz_locations loc, hz_parties party, ams_hz_b2c_mapping_v b2c

19474: group by b2c.import_source_line_id;
19475:
19476: cursor c_address_country is
19477: select b2c.import_source_line_id, max(psite.party_id)
19478: from hz_party_sites psite, hz_locations loc, hz_parties party, ams_hz_b2c_mapping_v b2c
19479: where b2c.import_list_header_id = p_import_list_header_id
19480: and b2c.load_status in ('ACTIVE','RELOAD')
19481: and b2c.person_exist = 'Y'
19482: and b2c.address1 is not null

Line 19713: from hz_parties pty

19709: and b2b.load_status in ('ACTIVE','RELOAD')
19710: and b2b.OCONT_PARTY_ID is null
19711: and exists
19712: ( select null
19713: from hz_parties pty
19714: where pty.customer_key = b2b.person_key
19715: and pty.party_type = 'PERSON'
19716: and pty.status = 'A' );
19717: /*

Line 19719: from hz_parties pty, ams_hz_b2b_mapping_v b2b

19715: and pty.party_type = 'PERSON'
19716: and pty.status = 'A' );
19717: /*
19718: select b2b.import_source_line_id, 'Y'
19719: from hz_parties pty, ams_hz_b2b_mapping_v b2b
19720: where b2b.import_list_header_id = p_import_list_header_id
19721: and pty.customer_key = b2b.person_key
19722: and b2b.person_key is not null
19723: and b2b.load_status in ('ACTIVE','RELOAD')

Line 19731: hz_parties org,

19727: and b2b.OCONT_PARTY_ID is null;
19728: */
19729: cursor c_cont_email_phone is
19730: select b2b.import_source_line_id, max(per.party_id) from
19731: hz_parties org,
19732: hz_parties per,
19733: hz_relationships rel,
19734: hz_contact_points cpoint,
19735: hz_contact_points cpoint1,

Line 19732: hz_parties per,

19728: */
19729: cursor c_cont_email_phone is
19730: select b2b.import_source_line_id, max(per.party_id) from
19731: hz_parties org,
19732: hz_parties per,
19733: hz_relationships rel,
19734: hz_contact_points cpoint,
19735: hz_contact_points cpoint1,
19736: ams_hz_b2b_mapping_v b2b

Line 19746: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

19742: and b2b.org_party_id is not null
19743: and b2b.OCONT_PARTY_ID is null
19744: and org.party_id = b2b.org_party_id
19745: and org.party_type = 'ORGANIZATION'
19746: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
19747: and rel.SUBJECT_TYPE = 'PERSON'
19748: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
19749: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
19750: and rel.OBJECT_ID = org.party_id

Line 19748: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

19744: and org.party_id = b2b.org_party_id
19745: and org.party_type = 'ORGANIZATION'
19746: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
19747: and rel.SUBJECT_TYPE = 'PERSON'
19748: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
19749: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
19750: and rel.OBJECT_ID = org.party_id
19751: and rel.SUBJECT_ID = per.PARTY_ID
19752: and per.customer_key = b2b.person_key

Line 19756: and cpoint.owner_table_name = 'HZ_PARTIES'

19752: and per.customer_key = b2b.person_key
19753: and per.status = 'A'
19754: and org.status = 'A'
19755: and cpoint.owner_table_id = rel.party_id
19756: and cpoint.owner_table_name = 'HZ_PARTIES'
19757: and cpoint.contact_point_type = 'EMAIL'
19758: and upper(cpoint.email_address) = upper(b2b.email_address)
19759: and cpoint.status = 'A'
19760: and cpoint1.owner_table_id = rel.party_id

Line 19761: and cpoint1.owner_table_name = 'HZ_PARTIES'

19757: and cpoint.contact_point_type = 'EMAIL'
19758: and upper(cpoint.email_address) = upper(b2b.email_address)
19759: and cpoint.status = 'A'
19760: and cpoint1.owner_table_id = rel.party_id
19761: and cpoint1.owner_table_name = 'HZ_PARTIES'
19762: and cpoint1.contact_point_type = 'PHONE'
19763: and cpoint1.transposed_phone_number = b2b.transposed_phone_number
19764: -- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
19765: -- b2b.phone_area_code||'-'||b2b.phone_number||'-'||b2b.phone_extention

Line 19772: hz_parties org,

19768: group by b2b.import_source_line_id;
19769:
19770: cursor c_cont_email is
19771: select b2b.import_source_line_id,max(per.party_id) from
19772: hz_parties org,
19773: hz_parties per,
19774: hz_relationships rel,
19775: hz_contact_points cpoint,
19776: ams_hz_b2b_mapping_v b2b

Line 19773: hz_parties per,

19769:
19770: cursor c_cont_email is
19771: select b2b.import_source_line_id,max(per.party_id) from
19772: hz_parties org,
19773: hz_parties per,
19774: hz_relationships rel,
19775: hz_contact_points cpoint,
19776: ams_hz_b2b_mapping_v b2b
19777: where b2b.import_list_header_id = p_import_list_header_id

Line 19786: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

19782: and b2b.ocont_party_id is null
19783: and b2b.OCONT_PARTY_ID is null
19784: and org.party_id = b2b.org_party_id
19785: and org.party_type = 'ORGANIZATION'
19786: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
19787: and rel.SUBJECT_TYPE = 'PERSON'
19788: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
19789: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
19790: and rel.OBJECT_ID = org.party_id

Line 19788: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

19784: and org.party_id = b2b.org_party_id
19785: and org.party_type = 'ORGANIZATION'
19786: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
19787: and rel.SUBJECT_TYPE = 'PERSON'
19788: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
19789: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
19790: and rel.OBJECT_ID = org.party_id
19791: and rel.SUBJECT_ID = per.PARTY_ID
19792: and per.customer_key = b2b.person_key

Line 19796: and cpoint.owner_table_name = 'HZ_PARTIES'

19792: and per.customer_key = b2b.person_key
19793: and per.status = 'A'
19794: and org.status = 'A'
19795: and cpoint.owner_table_id = rel.party_id
19796: and cpoint.owner_table_name = 'HZ_PARTIES'
19797: and cpoint.contact_point_type = 'EMAIL'
19798: and upper(cpoint.email_address) = upper(b2b.email_address)
19799: and cpoint.status = 'A'
19800: group by b2b.import_source_line_id;

Line 19804: hz_parties org,

19800: group by b2b.import_source_line_id;
19801:
19802: cursor c_cont_phone is
19803: select b2b.import_source_line_id,max(per.party_id) from
19804: hz_parties org,
19805: hz_parties per,
19806: hz_relationships rel,
19807: hz_contact_points cpoint,
19808: ams_hz_b2b_mapping_v b2b

Line 19805: hz_parties per,

19801:
19802: cursor c_cont_phone is
19803: select b2b.import_source_line_id,max(per.party_id) from
19804: hz_parties org,
19805: hz_parties per,
19806: hz_relationships rel,
19807: hz_contact_points cpoint,
19808: ams_hz_b2b_mapping_v b2b
19809: where b2b.import_list_header_id = p_import_list_header_id

Line 19818: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

19814: and b2b.ocont_party_id is null
19815: and b2b.OCONT_PARTY_ID is null
19816: and org.party_id = b2b.org_party_id
19817: and org.party_type = 'ORGANIZATION'
19818: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
19819: and rel.SUBJECT_TYPE = 'PERSON'
19820: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
19821: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
19822: and rel.OBJECT_ID = org.party_id

Line 19820: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

19816: and org.party_id = b2b.org_party_id
19817: and org.party_type = 'ORGANIZATION'
19818: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
19819: and rel.SUBJECT_TYPE = 'PERSON'
19820: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
19821: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
19822: and rel.OBJECT_ID = org.party_id
19823: and rel.SUBJECT_ID = per.PARTY_ID
19824: and per.customer_key = b2b.person_key

Line 19828: and cpoint.owner_table_name = 'HZ_PARTIES'

19824: and per.customer_key = b2b.person_key
19825: and per.status = 'A'
19826: and org.status = 'A'
19827: and cpoint.owner_table_id = rel.party_id
19828: and cpoint.owner_table_name = 'HZ_PARTIES'
19829: and cpoint.contact_point_type = 'PHONE'
19830: and cpoint.transposed_phone_number = b2b.transposed_phone_number
19831: -- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
19832: -- b2b.phone_area_code||'-'||b2b.phone_number||'-'||b2b.phone_extention

Line 20083: from hz_parties pty, ams_hz_b2b_mapping_v b2b

20079: and b2b.org_party_id is not null;
20080:
20081: cursor c_person_exists is
20082: select b2b.import_source_line_id, 'Y'
20083: from hz_parties pty, ams_hz_b2b_mapping_v b2b
20084: where b2b.import_list_header_id = p_import_list_header_id
20085: and pty.customer_key = b2b.person_key
20086: and b2b.person_key is not null
20087: and b2b.load_status in ('ACTIVE','RELOAD')

Line 20095: hz_parties org,

20091: and b2b.org_party_id is not null;
20092:
20093: cursor c_cont_email_phone is
20094: select b2b.import_source_line_id, max(per.party_id) from
20095: hz_parties org,
20096: hz_parties per,
20097: hz_relationships rel,
20098: hz_contact_points cpoint,
20099: hz_contact_points cpoint1,

Line 20096: hz_parties per,

20092:
20093: cursor c_cont_email_phone is
20094: select b2b.import_source_line_id, max(per.party_id) from
20095: hz_parties org,
20096: hz_parties per,
20097: hz_relationships rel,
20098: hz_contact_points cpoint,
20099: hz_contact_points cpoint1,
20100: ams_hz_b2b_mapping_v b2b

Line 20110: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

20106: and b2b.org_party_id is not null
20107: and org.party_id = b2b.org_party_id
20108: and org.orig_system_reference = b2b.orig_system_reference
20109: and org.party_type = 'ORGANIZATION'
20110: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
20111: and rel.SUBJECT_TYPE = 'PERSON'
20112: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
20113: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
20114: and rel.OBJECT_ID = org.party_id

Line 20112: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

20108: and org.orig_system_reference = b2b.orig_system_reference
20109: and org.party_type = 'ORGANIZATION'
20110: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
20111: and rel.SUBJECT_TYPE = 'PERSON'
20112: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
20113: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
20114: and rel.OBJECT_ID = org.party_id
20115: and rel.SUBJECT_ID = per.PARTY_ID
20116: and per.customer_key = b2b.person_key

Line 20120: and cpoint.owner_table_name = 'HZ_PARTIES'

20116: and per.customer_key = b2b.person_key
20117: and per.status = 'A'
20118: and org.status = 'A'
20119: and cpoint.owner_table_id = rel.party_id
20120: and cpoint.owner_table_name = 'HZ_PARTIES'
20121: and cpoint.contact_point_type = 'EMAIL'
20122: and upper(cpoint.email_address) = upper(b2b.email_address)
20123: and cpoint.status = 'A'
20124: and cpoint1.owner_table_id = rel.party_id

Line 20125: and cpoint1.owner_table_name = 'HZ_PARTIES'

20121: and cpoint.contact_point_type = 'EMAIL'
20122: and upper(cpoint.email_address) = upper(b2b.email_address)
20123: and cpoint.status = 'A'
20124: and cpoint1.owner_table_id = rel.party_id
20125: and cpoint1.owner_table_name = 'HZ_PARTIES'
20126: and cpoint1.contact_point_type = 'PHONE'
20127: and cpoint1.transposed_phone_number = b2b.transposed_phone_number
20128: -- and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension =
20129: -- b2b.phone_area_code||'-'||b2b.phone_number||'-'||b2b.phone_extention

Line 20136: hz_parties org,

20132: group by b2b.import_source_line_id;
20133:
20134: cursor c_cont_email is
20135: select b2b.import_source_line_id,max(per.party_id) from
20136: hz_parties org,
20137: hz_parties per,
20138: hz_relationships rel,
20139: hz_contact_points cpoint,
20140: ams_hz_b2b_mapping_v b2b

Line 20137: hz_parties per,

20133:
20134: cursor c_cont_email is
20135: select b2b.import_source_line_id,max(per.party_id) from
20136: hz_parties org,
20137: hz_parties per,
20138: hz_relationships rel,
20139: hz_contact_points cpoint,
20140: ams_hz_b2b_mapping_v b2b
20141: where b2b.import_list_header_id = p_import_list_header_id

Line 20150: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

20146: and b2b.ocont_party_id is null
20147: and org.party_id = b2b.org_party_id
20148: and org.orig_system_reference = b2b.orig_system_reference
20149: and org.party_type = 'ORGANIZATION'
20150: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
20151: and rel.SUBJECT_TYPE = 'PERSON'
20152: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
20153: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
20154: and rel.OBJECT_ID = org.party_id

Line 20152: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

20148: and org.orig_system_reference = b2b.orig_system_reference
20149: and org.party_type = 'ORGANIZATION'
20150: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
20151: and rel.SUBJECT_TYPE = 'PERSON'
20152: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
20153: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
20154: and rel.OBJECT_ID = org.party_id
20155: and rel.SUBJECT_ID = per.PARTY_ID
20156: and per.customer_key = b2b.person_key

Line 20160: and cpoint.owner_table_name = 'HZ_PARTIES'

20156: and per.customer_key = b2b.person_key
20157: and per.status = 'A'
20158: and org.status = 'A'
20159: and cpoint.owner_table_id = rel.party_id
20160: and cpoint.owner_table_name = 'HZ_PARTIES'
20161: and cpoint.contact_point_type = 'EMAIL'
20162: and upper(cpoint.email_address) = upper(b2b.email_address)
20163: and cpoint.status = 'A'
20164: group by b2b.import_source_line_id;

Line 20168: hz_parties org,

20164: group by b2b.import_source_line_id;
20165:
20166: cursor c_cont_phone is
20167: select b2b.import_source_line_id,max(per.party_id) from
20168: hz_parties org,
20169: hz_parties per,
20170: hz_relationships rel,
20171: hz_contact_points cpoint,
20172: ams_hz_b2b_mapping_v b2b

Line 20169: hz_parties per,

20165:
20166: cursor c_cont_phone is
20167: select b2b.import_source_line_id,max(per.party_id) from
20168: hz_parties org,
20169: hz_parties per,
20170: hz_relationships rel,
20171: hz_contact_points cpoint,
20172: ams_hz_b2b_mapping_v b2b
20173: where b2b.import_list_header_id = p_import_list_header_id

Line 20182: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

20178: and b2b.ocont_party_id is null
20179: and org.party_id = b2b.org_party_id
20180: and org.orig_system_reference = b2b.orig_system_reference
20181: and org.party_type = 'ORGANIZATION'
20182: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
20183: and rel.SUBJECT_TYPE = 'PERSON'
20184: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
20185: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
20186: and rel.OBJECT_ID = org.party_id

Line 20184: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

20180: and org.orig_system_reference = b2b.orig_system_reference
20181: and org.party_type = 'ORGANIZATION'
20182: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
20183: and rel.SUBJECT_TYPE = 'PERSON'
20184: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'
20185: and rel.RELATIONSHIP_CODE = 'CONTACT_OF'
20186: and rel.OBJECT_ID = org.party_id
20187: and rel.SUBJECT_ID = per.PARTY_ID
20188: and per.customer_key = b2b.person_key

Line 20192: and cpoint.owner_table_name = 'HZ_PARTIES'

20188: and per.customer_key = b2b.person_key
20189: and per.status = 'A'
20190: and org.status = 'A'
20191: and cpoint.owner_table_id = rel.party_id
20192: and cpoint.owner_table_name = 'HZ_PARTIES'
20193: and cpoint.contact_point_type = 'PHONE'
20194: and cpoint.transposed_phone_number = b2b.transposed_phone_number
20195: -- and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension =
20196: -- b2b.phone_area_code||'-'||b2b.phone_number||'-'||b2b.phone_extention