[Home] [Help]
4870: SELECT RELATIONSHIP_ID, OBJECT_VERSION_NUMBER FROM hz_relationships WHERE subject_type = 'PERSON'
4871: AND party_id = p_party_id;
4872:
4873: CURSOR LOCATION_EXISTS IS
4874: SELECT party_site_id FROM hz_party_sites
4875: WHERE party_id = x_org_party_id
4876: AND location_id = x_location_id;
4877:
4878: CURSOR CHECK_PSITE_EXISTS IS
4875: WHERE party_id = x_org_party_id
4876: AND location_id = x_location_id;
4877:
4878: CURSOR CHECK_PSITE_EXISTS IS
4879: SELECT party_site_id FROM hz_party_sites
4880: WHERE party_id = x_party_rel_party_id
4881: AND location_id = x_location_id;
4882:
4883: CURSOR phone_exists (x_hz_party_id number) IS
4899: AND owner_table_id = x_hz_party_id
4900: AND email_address = x_email_address;
4901:
4902: CURSOR PER_LOCATION_EXISTS IS
4903: SELECT party_site_id FROM hz_party_sites
4904: WHERE party_id = x_per_party_id
4905: AND location_id = x_location_id;
4906:
4907: party_rec hz_party_v2pub.party_rec_type;
13480:
13481:
13482: cursor c_addr_ps is
13483: select max(loc.location_id)
13484: from hz_party_sites ps, hz_locations loc
13485: where ps.party_id = p_party_id
13486: and ps.location_id = loc.location_id
13487: and loc.address_key = l_address_key
13488: and loc.country = p_country
13572:
13573: begin
13574: x_gen_party_site_number := fnd_profile.value('HZ_GENERATE_PARTY_SITE_NUMBER');
13575: x_party_site_number := null;
13576: select hz_party_sites_s.nextval into x_party_site_id from dual;
13577: if x_gen_party_site_number = 'N' then
13578: select hz_party_site_number_s.nextval into x_party_site_number from dual;
13579: end if;
13580:
18576:
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'
18595: group by b2b.import_source_line_id;
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'
18846:
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'
18866: group by b2b.import_source_line_id;
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'
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
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
20389: and b2b.address1 is not null;
20390:
20391: cursor c_addr_ps is
20392: select b2b.import_source_line_id,max(loc.location_id)
20393: from hz_party_sites ps, hz_locations loc,ams_hz_b2b_mapping_v b2b
20394: where b2b.import_list_header_id = p_import_list_header_id
20395: and b2b.load_status in ('ACTIVE','RELOAD')
20396: and b2b.address_key is not null
20397: and b2b.org_party_id is not null
20547: and b2b.org_address1 is not null;
20548:
20549: cursor c_addr_ps is
20550: select b2b.import_source_line_id,max(loc.location_id)
20551: from hz_party_sites ps, hz_locations loc,ams_hz_b2b_mapping_v b2b
20552: where b2b.import_list_header_id = p_import_list_header_id
20553: and b2b.load_status in ('ACTIVE','RELOAD')
20554: and b2b.org_address_key is not null
20555: and b2b.org_party_id is not null
20705: and b2c.address1 is not null;
20706:
20707: cursor c_addr_ps is
20708: select b2c.import_source_line_id,max(loc.location_id)
20709: from hz_party_sites ps, hz_locations loc,ams_hz_b2c_mapping_v b2c
20710: where b2c.import_list_header_id = p_import_list_header_id
20711: and b2c.load_status in ('ACTIVE','RELOAD')
20712: and b2c.address_key is not null
20713: and b2c.person_party_id is not null