[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
1199: select OBJECT_VERSION_NUMBER into l_party_obj_number
1200: from hz_parties
1201: where party_id = x_org_party_id;
1202: select OBJECT_VERSION_NUMBER into l_pr_obj_number
1203: from hz_relationships
1204: where relationship_id = l_rel_id and directional_flag = 'F'; -- x_party_rel_party_id;
1205:
1206: select OBJECT_VERSION_NUMBER into l_con_obj_number
1207: from hz_org_contacts
3633: cursor c_cont_email is
3634: select max(per.party_id) from
3635: hz_parties org,
3636: hz_parties per,
3637: hz_relationships rel,
3638: hz_contact_points cpoint
3639: where org.party_id = p_org_party_id
3640: and org.party_type = 'ORGANIZATION'
3641: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3663: cursor c_cont_email_with_osr is
3664: select max(per.party_id) from
3665: hz_parties org,
3666: hz_parties per,
3667: hz_relationships rel,
3668: hz_contact_points cpoint
3669: where org.party_id = p_org_party_id
3670: and org.party_type = 'ORGANIZATION'
3671: and org.orig_system_reference = p_orig_system_reference
3691: cursor c_cont_email_phone is
3692: select max(per.party_id) from
3693: hz_parties org,
3694: hz_parties per,
3695: hz_relationships rel,
3696: hz_contact_points cpoint,
3697: hz_contact_points cpoint1
3698: where org.party_id = p_org_party_id
3699: and org.party_type = 'ORGANIZATION'
3728: cursor c_cont_email_phone_with_osr is
3729: select max(per.party_id) from
3730: hz_parties org,
3731: hz_parties per,
3732: hz_relationships rel,
3733: hz_contact_points cpoint,
3734: hz_contact_points cpoint1
3735: where org.party_id = p_org_party_id
3736: and org.party_type = 'ORGANIZATION'
3765: cursor c_cont_phone is
3766: select max(per.party_id) from
3767: hz_parties org,
3768: hz_parties per,
3769: hz_relationships rel,
3770: hz_contact_points cpoint
3771: where org.party_id = p_org_party_id
3772: and org.party_type = 'ORGANIZATION'
3773: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3797: cursor c_cont_phone_with_osr is
3798: select max(per.party_id) from
3799: hz_parties org,
3800: hz_parties per,
3801: hz_relationships rel,
3802: hz_contact_points cpoint
3803: where org.party_id = p_org_party_id
3804: and org.party_type = 'ORGANIZATION'
3805: and org.orig_system_reference = p_orig_system_reference
4004:
4005: cursor c_rel_party_id is
4006: select max(rel.party_id) from
4007: hz_parties org,
4008: hz_relationships rel
4009: where org.party_id = p_org_party_id
4010: and org.party_type = 'ORGANIZATION'
4011: and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
4012: and rel.SUBJECT_TYPE = 'PERSON'
4624:
4625:
4626: cursor c_email_address is
4627: select max(p2.party_id) from hz_contact_points cp,
4628: hz_parties p1, hz_relationships hr, hz_parties p2
4629: where p2.customer_key = l_party_key
4630: and p1.party_type = 'PARTY_RELATIONSHIP'
4631: and p2.party_type = 'PERSON'
4632: and p1.status = 'A'
4639: and hr.subject_id = p2.party_id;
4640:
4641: cursor c_email_address_with_osr is
4642: select max(p2.party_id) from hz_contact_points cp,
4643: hz_parties p1, hz_relationships hr, hz_parties p2
4644: where p2.customer_key = l_party_key
4645: and p1.party_type = 'PARTY_RELATIONSHIP'
4646: and p2.party_type = 'PERSON'
4647: and p1.status = 'A'
4656:
4657:
4658: cursor c_ph_number is
4659: select max(p2.party_id) from hz_contact_points cp,
4660: hz_parties p1, hz_relationships hr, hz_parties p2
4661: where p2.customer_key = l_party_key
4662: and p1.party_type = 'PARTY_RELATIONSHIP'
4663: and p2.party_type = 'PERSON'
4664: and p1.status = 'A'
4676: */
4677:
4678: cursor c_ph_number_with_osr is
4679: select max(p2.party_id) from hz_contact_points cp,
4680: hz_parties p1, hz_relationships hr, hz_parties p2
4681: where p2.customer_key = l_party_key
4682: and p1.party_type = 'PARTY_RELATIONSHIP'
4683: and p2.party_type = 'PERSON'
4684: and p1.status = 'A'
4697: */
4698:
4699: cursor c_address_country is
4700: select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4701: hz_parties party, hz_relationships hr, hz_parties p2
4702: where psite.location_id = loc.location_id
4703: and loc.address1 = p_address1
4704: and loc.country = p_country
4705: and party.customer_key = l_party_key
4713: and hr.subject_id = p2.party_id;
4714:
4715: cursor c_address_country_with_osr is
4716: select max(p2.party_id) from hz_party_sites psite, hz_locations loc,
4717: hz_parties party, hz_relationships hr, hz_parties p2
4718: where psite.location_id = loc.location_id
4719: and loc.address1 = p_address1
4720: and loc.country = p_country
4721: and party.customer_key = l_party_key
4730: and hr.subject_id = p2.party_id;
4731:
4732:
4733: cursor c_person_exists is
4734: select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4735: where hp2. customer_key = l_party_key
4736: and hp1.status = 'A'
4737: and hp2.status = 'A'
4738: and hp1.party_type = 'PARTY_RELATIONSHIP'
4741: and hr.subject_id = hp2.party_id
4742: and hp2.party_type = 'PERSON';
4743:
4744: cursor c_person_exists_with_osr is
4745: select 'Y' from hz_parties hp1, hz_relationships hr, hz_parties hp2
4746: where hp2. customer_key = l_party_key
4747: and hp1.status = 'A'
4748: and hp2.status = 'A'
4749: and hp1.party_type = 'PARTY_RELATIONSHIP'
4754: and hp2.orig_system_reference = p_orig_system_reference;
4755:
4756: cursor c_get_party_id(c_rel_party_id NUMBER) IS
4757: select hr.subject_id
4758: from hz_relationships hr
4759: where hr.party_id = c_rel_party_id
4760: and hr.relationship_code = 'CONTACT_OF';
4761: begin
4762: