[Home] [Help]
364: select PER_IMP_XML_ELEMENT_ID from ams_hz_b2c_mapping_v
365: where import_source_line_id = i_import_source_line_id;
366:
367: CURSOR PARTY_REL_EXISTS IS
368: SELECT party_id, relationship_id FROM hz_relationships
369: WHERE object_id = x_org_party_id
370: AND subject_id = x_per_party_id
371: AND subject_table_name = 'HZ_PARTIES'
372: AND subject_type = 'PERSON'
391: and status = 'A'
392: AND party_id = l_b2c_party_id;
393:
394: cursor orgpartyid is
395: SELECT object_id FROM hz_relationships
396: WHERE subject_type = 'PERSON'
397: AND subject_table_name = 'HZ_PARTIES'
398: AND object_type = 'ORGANIZATION'
399: AND object_table_name = 'HZ_PARTIES'
551:
552:
553:
554: cursor c_relationship is
555: SELECT OBJECT_VERSION_NUMBER FROM hz_relationships
556: WHERE RELATIONSHIP_ID = nvl(x_party_relationship_id,l_rel_id)
557: and subject_type = 'PERSON';
558:
559: cursor c_org_cont_rel is
1195: select OBJECT_VERSION_NUMBER into l_party_obj_number
1196: from hz_parties
1197: where party_id = x_org_party_id;
1198: select OBJECT_VERSION_NUMBER into l_pr_obj_number
1199: from hz_relationships
1200: where relationship_id = l_rel_id and directional_flag = 'F'; -- x_party_rel_party_id;
1201:
1202: select OBJECT_VERSION_NUMBER into l_con_obj_number
1203: from hz_org_contacts
3626: cursor c_cont_email is
3627: select max(per.party_id) from
3628: hz_parties org,
3629: hz_parties per,
3630: hz_relationships rel,
3631: hz_contact_points cpoint
3632: where org.party_id = p_org_party_id
3633: and org.party_type = 'ORGANIZATION'
3634: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3656: cursor c_cont_email_with_osr is
3657: select max(per.party_id) from
3658: hz_parties org,
3659: hz_parties per,
3660: hz_relationships rel,
3661: hz_contact_points cpoint
3662: where org.party_id = p_org_party_id
3663: and org.party_type = 'ORGANIZATION'
3664: and org.orig_system_reference = p_orig_system_reference
3684: cursor c_cont_email_phone is
3685: select max(per.party_id) from
3686: hz_parties org,
3687: hz_parties per,
3688: hz_relationships rel,
3689: hz_contact_points cpoint,
3690: hz_contact_points cpoint1
3691: where org.party_id = p_org_party_id
3692: and org.party_type = 'ORGANIZATION'
3721: cursor c_cont_email_phone_with_osr is
3722: select max(per.party_id) from
3723: hz_parties org,
3724: hz_parties per,
3725: hz_relationships rel,
3726: hz_contact_points cpoint,
3727: hz_contact_points cpoint1
3728: where org.party_id = p_org_party_id
3729: and org.party_type = 'ORGANIZATION'
3758: cursor c_cont_phone is
3759: select max(per.party_id) from
3760: hz_parties org,
3761: hz_parties per,
3762: hz_relationships rel,
3763: hz_contact_points cpoint
3764: where org.party_id = p_org_party_id
3765: and org.party_type = 'ORGANIZATION'
3766: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3790: cursor c_cont_phone_with_osr is
3791: select max(per.party_id) from
3792: hz_parties org,
3793: hz_parties per,
3794: hz_relationships rel,
3795: hz_contact_points cpoint
3796: where org.party_id = p_org_party_id
3797: and org.party_type = 'ORGANIZATION'
3798: and org.orig_system_reference = p_orig_system_reference
3997:
3998: cursor c_rel_party_id is
3999: select max(rel.party_id) from
4000: hz_parties org,
4001: hz_relationships rel
4002: where org.party_id = p_org_party_id
4003: and org.party_type = 'ORGANIZATION'
4004: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4005: and rel.SUBJECT_TYPE = 'PERSON'
4617:
4618:
4619: cursor c_email_address is
4620: select max(p2.party_id) from hz_contact_points cp,
4621: hz_parties p1, hz_relationships hr, hz_parties p2
4622: where p2.customer_key = l_party_key
4623: and p1.party_type = 'PARTY_RELATIONSHIP'
4624: and p2.party_type = 'PERSON'
4625: and p1.status = 'A'
4632: and hr.subject_id = p2.party_id;
4633:
4634: cursor c_email_address_with_osr is
4635: select max(p2.party_id) from hz_contact_points cp,
4636: hz_parties p1, hz_relationships hr, hz_parties p2
4637: where p2.customer_key = l_party_key
4638: and p1.party_type = 'PARTY_RELATIONSHIP'
4639: and p2.party_type = 'PERSON'
4640: and p1.status = 'A'
4649:
4650:
4651: cursor c_ph_number is
4652: select max(p2.party_id) from hz_contact_points cp,
4653: hz_parties p1, hz_relationships hr, hz_parties p2
4654: where p2.customer_key = l_party_key
4655: and p1.party_type = 'PARTY_RELATIONSHIP'
4656: and p2.party_type = 'PERSON'
4657: and p1.status = 'A'
4669: */
4670:
4671: cursor c_ph_number_with_osr is
4672: select max(p2.party_id) from hz_contact_points cp,
4673: hz_parties p1, hz_relationships hr, hz_parties p2
4674: where p2.customer_key = l_party_key
4675: and p1.party_type = 'PARTY_RELATIONSHIP'
4676: and p2.party_type = 'PERSON'
4677: and p1.status = 'A'
4690: */
4691:
4692: cursor c_address_country is
4693: select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4694: hz_parties party, hz_relationships hr, hz_parties p2
4695: where psite.location_id = loc.location_id
4696: and loc.address1 = p_address1
4697: and loc.country = p_country
4698: and party.customer_key = l_party_key
4706: and hr.subject_id = p2.party_id;
4707:
4708: cursor c_address_country_with_osr is
4709: select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4710: hz_parties party, hz_relationships hr, hz_parties p2
4711: where psite.location_id = loc.location_id
4712: and loc.address1 = p_address1
4713: and loc.country = p_country
4714: and party.customer_key = l_party_key
4723: and hr.subject_id = p2.party_id;
4724:
4725:
4726: cursor c_person_exists is
4727: select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4728: where hp2. customer_key = l_party_key
4729: and hp1.status = 'A'
4730: and hp2.status = 'A'
4731: and hp1.party_type = 'PARTY_RELATIONSHIP'
4734: and hr.subject_id = hp2.party_id
4735: and hp2.party_type = 'PERSON';
4736:
4737: cursor c_person_exists_with_osr is
4738: select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4739: where hp2. customer_key = l_party_key
4740: and hp1.status = 'A'
4741: and hp2.status = 'A'
4742: and hp1.party_type = 'PARTY_RELATIONSHIP'
4747: and hp2.orig_system_reference = p_orig_system_reference;
4748:
4749: cursor c_get_party_id(c_rel_party_id NUMBER) IS
4750: select hr.subject_id
4751: from hz_relationships hr
4752: where hr.party_id = c_rel_party_id
4753: and hr.relationship_code = 'CONTACT_OF';
4754: begin
4755: