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 4886: AND owner_table_name = 'HZ_PARTIES'

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

Line 4897: AND owner_table_name = 'HZ_PARTIES'

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

Line 5165: FROM hz_parties

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

Line 5199: FROM HZ_PARTIES

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

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

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

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

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

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

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

Line 5566: FROM HZ_PARTIES

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

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

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

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

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

Line 13960: 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

13956: and org_party_id is null;
13957:
13958: --if party_id is of type organization then update org_party_id with party_id
13959: update ams_hz_b2b_mapping_v b2b
13960: 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
13961: where import_list_header_id = p_import_list_header_id
13962: and party_id is not null
13963: and org_party_id is null;
13964:

Line 13980: 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,

13976:
13977: --aanjaria: bug 4913239: copy party_id to person_party_id so that existence check is by passed
13978: -- mayjain bug 5100612 this update should be done only if the party_id is a valid one.
13979: UPDATE ams_hz_b2c_mapping_v b2c
13980: 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,
13981: WHERE import_list_header_id = p_import_list_header_id
13982: AND party_id is not null
13983: AND person_party_id is null;
13984:

Line 18570: and exists (select 1 from hz_parties pty

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

Line 18580: hz_parties party, ams_hz_b2b_mapping_v b2b

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

Line 18599: hz_parties party, ams_hz_b2b_mapping_v b2b

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

Line 18615: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

Line 18835: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

Line 18850: hz_parties party, ams_hz_b2b_mapping_v b2b

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

Line 18870: hz_parties party, ams_hz_b2b_mapping_v b2b

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

Line 18887: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

Line 19113: from hz_parties pty, ams_hz_b2c_mapping_v b2c

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

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

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

Line 19134: and cp.owner_table_name = 'HZ_PARTIES'

19130: and p.customer_key = b2c.person_key
19131: and p.party_type = 'PERSON'
19132: and p.status = 'A'
19133: and cp.owner_table_id = p.party_id
19134: and cp.owner_table_name = 'HZ_PARTIES'
19135: and upper(cp.email_address) = upper(b2c.email_address)
19136: and cp.primary_flag = 'Y'
19137: group by b2c.import_source_line_id;
19138:

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

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

Line 19151: and cp.owner_table_name = 'HZ_PARTIES'

19147: and p.customer_key = b2c.person_key
19148: and p.party_type = 'PERSON'
19149: and p.status = 'A'
19150: and cp.owner_table_id = p.party_id
19151: and cp.owner_table_name = 'HZ_PARTIES'
19152: and cp.primary_flag = 'Y'
19153: and cp.transposed_phone_number = b2c.transposed_phone_number
19154: -- and cp.phone_number = b2c.phone_number
19155: -- and nvl(cp.phone_country_code,nvl(b2c.phone_country_code,'x')) = nvl(b2c.phone_country_code,'x')

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

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

Line 19432: and exists (select 1 from hz_parties pty

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

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

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

Line 19449: and cp.owner_table_name = 'HZ_PARTIES'

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

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

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

Line 19467: and cp.owner_table_name = 'HZ_PARTIES'

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

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

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

Line 19712: from hz_parties pty

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

Line 19718: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

Line 19730: hz_parties org,

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

Line 19731: hz_parties per,

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

Line 19745: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 19747: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 19755: and cpoint.owner_table_name = 'HZ_PARTIES'

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

Line 19760: and cpoint1.owner_table_name = 'HZ_PARTIES'

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

Line 19771: hz_parties org,

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

Line 19772: hz_parties per,

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

Line 19785: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 19787: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 19795: and cpoint.owner_table_name = 'HZ_PARTIES'

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

Line 19803: hz_parties org,

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

Line 19804: hz_parties per,

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

Line 19817: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 19819: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 19827: and cpoint.owner_table_name = 'HZ_PARTIES'

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

Line 20082: from hz_parties pty, ams_hz_b2b_mapping_v b2b

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

Line 20094: hz_parties org,

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

Line 20095: hz_parties per,

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

Line 20109: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 20111: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 20119: and cpoint.owner_table_name = 'HZ_PARTIES'

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

Line 20124: and cpoint1.owner_table_name = 'HZ_PARTIES'

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

Line 20135: hz_parties org,

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

Line 20136: hz_parties per,

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

Line 20149: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 20151: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 20159: and cpoint.owner_table_name = 'HZ_PARTIES'

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

Line 20167: hz_parties org,

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

Line 20168: hz_parties per,

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

Line 20181: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 20183: and rel.OBJECT_TABLE_NAME = 'HZ_PARTIES'

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

Line 20191: and cpoint.owner_table_name = 'HZ_PARTIES'

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