|
POSC Specifications Version 2.2 |
Usage Document Business_associates |
Epicentre introduced the concept of a business_associate to handle the very general concept of an individual, group, company, regulatory body, etc. that one does business with. The standard way that most databases work is to separate the concept of a person from that of a company. This caused many problems in the general usage that Epicentre envisioned. Consequently, these generally separate classifications were lumped into a single entity, with an attribute to identify whether the business associate is a person, company, etc.
This article explores four main areas in using the business associate. First, is the instantiation in the simplest situation, so that the business_associate entity can be referenced elsewhere. This is covered in the first section.
Second is the storage of more information about the business_associate. This include information about the person or company, which may include addresses, phone numbers, services, responsibilities, contacts made, etc. The second section gives examples of these.
The third area is the use of business_associates. Most databases use business_associates by giving the role as an attribute, and expect the user to fill in the identifier. For example, a well entity in many databases would have an attribute 'operator,' another one for 'owner,' another for 'sitting geologist,' etc. Or, for the case of seismic, there would be an attribute 'processor.' Epicentre handles these uniformly by having a many-to-many relationship between an entity and a business_associate that is resolved by a role entity. The use of business_associate with this model would say that a company is related to a well in the role of operator. A person is related to a well in the role of sitting geologist. And so forth. Details of this pattern are given in the third section.
Finally, it is often desirable to relate business_associates to other business_associates. For example, we might want to say that Tom works for Mobil. Or that Mary is our contact at the BLM (Bureau of Land Management). It is also possible to group business_associates together into working groups. For example, Epicentre can group Albert, Bennett, and Carl into a project team for the GoForIt Prospect. It is also possible to group companies. A good example is the CAGC group (Conoco, Arco, Getty, and Cities Service) that had a formal combination that would bid and develop in the offshore Gulf of Mexico. Examples of these groupings will be shown in the fourth section.
In summary, Epicentre has two generalities that set it apart from other databases. The first is the use of business_associate in general rather than person, company, agency, consortium, etc. as particualr entities. The second generalization is the way business_associate is used in relationships to other entities. A few examples of their use will show this generality.
At its simplest, all you need to supply for a business_associate instance is a name and a kind. The kind is controlled by the reference entity, ref_business_associate. A third attribute that could be useful is the ref_naming_system which describes the namespace of the name.
As an example, and for later use, we will instantiate our company (MyCompany) and four individuals who work for the company. We will also instantiate a service company that does seismic work for us.
--> Begin example: simplest business_associate
zantac = BUSINESS_ASSOCIATE {
identifier --> 'Zantac Exploration';
kind --> @PRV_company;
ref_naming_system --> @PRV_common_name; };
albert = BUSINESS_ASSOCIATE {
identifier --> 'Albert Albertson';
kind --> @PRV_person;
ref_naming_system --> @PRV_common_name; };
bennett = BUSINESS_ASSOCIATE {
identifier --> 'Bennett Bennettson';
kind --> @PRV_person;
ref_naming_system --> @PRV_common_name; };
carl = BUSINESS_ASSOCIATE {
identifier --> 'Carl Carlson';
kind --> @PRV_person;
ref_naming_system --> @PRV_common_name; };
dennis = BUSINESS_ASSOCIATE {
identifier --> 'Dennis Dennisson';
kind --> @PRV_person;
ref_naming_system --> @PRV_common_name; };
eric = BUSINESS_ASSOCIATE {
identifier --> 'Eric Ericson';
kind --> @PRV_person;
ref_naming_system --> @PRV_common_name; };
prettygood = BUSINESS_ASSOCIATE {
identifier --> 'Pretty Good Seismic';
kind --> @PRV_company;
ref_naming_system --> @PRV_common_name; };
--> End example: simplest business_associate.
There are a number of different names for a business_associate. In the above example, the "common name" was used. Other names may be stored in the business_associate_alias. In this way, it is possible to indicate the many different identifiers (not always "names") that can be used for a business_associate. As an example, we will store the full name, nickname(s), social security number for Albert Albertson.
It should be noted that, for the aliases, the ref_naming_system is a mandatory attribute. For this reason, it is recommended that ref_naming_system always be used for business_associate, even though it is not mandatory.
--> Begin example: business_associate_alias.
alb = BUSINESS_ASSOCIATE_ALIAS {
identifier --> 'Albert';
ref_naming_system --> @PRV_nickname;
business_associate --> @albert; };
al = BUSINESS_ASSOCIATE_ALIAS {
identifier --> 'Al';
ref_naming_system --> @PRV_nickname;
business_associate --> @albert; };
albert_full = BUSINESS_ASSOCIATE_ALIAS {
identifier --> 'Albert Alvin Albertson';
ref_naming_system --> @PRV_full_name;
business_associate --> @albert; };
222 = BUSINESS_ASSOCIATE_ALIAS {
identifier --> '222-22-2222';
ref_naming_system --> @PRV_US_social_security_number;
business_associate --> @albert; };
--> End example: business_associate_alias
Of course, a similar set of names can be stored for the other business_associates. Note that the aliases do not necessarily need to be unique. For example, there may be another person in the database whose nickname is 'Al.'
Here are some queries to obtain the stored information. The queries are all in the POSC query language (PSQL).
Get all the business_associates and their types:
select identifier, kind.kind from business_associate;
Get all the business_associates that are persons:
select identifier from business_associate where kind.kind = 'person';
Get all the names and name types of a particular business_associate;
select business_associate.identifier, identifier, ref_naming_system.kind from business_associate_alias where business_associate.identifier = 'Albert Albertson';
There is a wealth of information that can be stored about a business_associate. Among these are the addresses, phone numbers, security clearances, services, capabilities, training, etc.
The first example will be phone numbers. This is an area that has caused great difficulty for may databases. In order to overcome these difficulties, Epicentre has a relationship that allows many phone numbers for each business_associate. Each phone number is classified by its kind, which is controlled by a reference entity.
Most people have encountered entries on forms: Home phone, Business phone. This is used to fill in these areas in a database. But then along came faxes -- Home and office. Then came cellular phones. And so forth. The addition of all these different kinds of phones overwhelmed databases. The response is either to "cheat" by putting the pager number somewhere with a notation that it is the pager number (never a good practice), or to put 10 or 12 attributes for all these different possibilities. Epicentre has a logical alternative to this proliferation of telephone numbers. It uses a ref_phone_number to specify the type of phone number that is being stored, and a ref_address_qualifier to qualify it as home, office, or permanent.
Example: telephone numbers
al_home = PHONE_NUMBER { --Albert's home phone
area_code --> '281'; --Houston suburbs
local_phone_number --> '123-4567' ;
ref_phone_number --> SET(@PRV_voice) ;
ref_address_qualifier --> @PRV_home ;
business_associate --> @albert; };
al_office = PHONE_NUMBER { --Albert's office phone
area_code --> '713'; --Houston
local_phone_number --> '765-4321' ;
ref_phone_number --> SET(@PRV_voice) ;
ref_address_qualifier --> @PRV_office
business_associate --> @albert; };
al_fax = PHONE_NUMBER { --Albert's fax number at office
area_code --> '713';
local_phone_number --> '765-1234' ;
ref_phone_number --> SET(@PRV_fax) ;
ref_address_qualifier --> @PRV_office ;
business_associate --> @albert; };
al_cell = PHONE_NUMBER { --Albert's cellular phone
area_code --> '281';
local_phone_number --> '987-6543' ;
ref_phone_number --> SET(@PRV_mobile) ;
business_associate --> @albert; };
al_secy = PHONE_NUMBER { --Albert's secretary
area_code --> '713'; --Houston
local_phone_number --> '765-1111' ;
ref_phone_number --> SET(@PRV_voice, @PRV_secretary) ;
ref_address_qualifier --> @PRV_office ;
business_associate --> @albert; };
(* Now do some for the company *)
pg_main = PHONE_NUMBER { --Main phone number
area_code --> '713'; --Houston
local_phone_number --> '555-7890' ;
ref_phone_number --> SET(@PRV_voice) ;
ref_address_qualifier --> @PRV_office ;
business_associate --> @prettygood ; };
pg_fax = PHONE_NUMBER { --Company's main fax
area_code --> '713';
local_phone_number --> '555-0987' ;
ref_phone_number --> SET(@PRV_fax) ;
ref_address_qualifier --> @PRV_office ;
business_associate --> @prettygood ; };
pg_modem = PHONE_NUMBER { --Company dial-up modem
area_code --> '713';
local_phone_number --> '765-0000' ;
ref_phone_number --> SET(@PRV_modem) ;
ref_address_qualifier --> @PRV_office ;
business_associate --> @prettygood ; };
--> End example: phone numbers.
Note the pattern. Each instance gives the phone number, the type of number, a qualifier (optional), and whose number it is. Note also that the phone_number entity has an attribute for country code and an attribute for extension, if either of those are needed. In addition, there is an effective_date and an expiry_date. If you receive a note from a company, "As of June 1, our phone numbers will be xxx-...," then you can note the expiration of the old number and record the new number.
Addresses are treated just like phone numbers. The attributes are different, but the treatment is the same. In particular, a business_associate can have many addresses. A phone number is one type of address. Epicentre has three other types of addresses: mailing_address, physical_address, and email_address.
An example of each will be shown for Pretty Good Seismic. Note in the example that the physical address is different from the mailing address. This often happens, but is not a requirement. Even when they are same, though, both should be given. This makes queries simpler (give me the mailing addresses of certain business_associates).
--> Begin example: Addresses.
phys = PHYSICAL_ADDRESS { --street address of the office.
street_name --> 'Westheimer';
street_number --> '22222';
room_number --> '555'; -- if appropriate
geopolitical_feature --> @PRV_Houston;
business_associate --> @prettygood ; };
(* Note that PRV_Houston implies Texas, since the reference entity,
geopolitical_feature, contains that information. *)
mailing = MAILING_ADDRESS {
line_1 --> 'Westside Processing Group';
line_2 --> 'P.O. Box 231097';
postal_code --> '77099';
geopolitical_feature --> @PRV_Houston;
business_associate --> @prettygood; };
email = EMAIL_ADDRESS {
data_value --> 'info@prettygood.net.com';
business_associate --> @prettygood ; };
---> End example: Addresses
A company may provide services for other companies. It is often useful to record these services. This allows a company (or group, or agency, or person) to be more fully defined. For example, let us record that Pretty Good Seismic provides seismic processing and data brokering of seismic. This can distinguish it from another company that might provide seismic acquisition and spec surveys, as well as seismic processing. Or another company that provides seismic interpretation. None of this information is evident from the "identifier" of the company. So it needs to be stored elsewhere.
In Epicentre, this information is stored in business_associate_service. In its simplest form, this records the service (controlled by ref_business_associate_service) and the business_associate. Additional information is inherited from transient_association.
It should be noted that ref_business_associate_service is a local reference entity. Thus, POSC does not supply a standard set of instances. It is necessary to define your own set of services and to instantiate them (this is shown also in the example for two of the services.).
In addition, it is possible to list the services or capabilities of employees. We also record the fact that Albert Albertson is able to do programming, seismic interpretation, and well log analysis. Note that this does not say that Albert is a programmer, or that he is a seismic interpreter or a well log analyst. He may be hired as a seismic processor. But it does list these as capabilities or services that Albert can perform.
---> Example: Business_associate_service.
RV_seismic_processing = REF_BUSINESS_ASSOCIATE_SERVICE {
kind --> 'seismic processing' ;
description --> 'The business_associate will process seismic data.';
source --> @RV_MyCompany; }; --an instance of ref_source
RV_seismic_brokering = REF_BUSINESS_ASSOCIATE_SERVICE {
kind --> 'seismic brokering' ;
description --> 'The company acts as an agent to sell seismic data
owned by one company to another company.';
source --> @RV_MyCompany; };
processing = BUSINESS_ASSOCIATE_SERVICE {
ref_business_associate_service --> @RV_seismic_processing ;
business_associate --> @prettygood ; };
broker = BUSINESS_ASSOCIATE_SERVICE {
ref_business_associate_service --> @RV_seismic_brokering ;
business_associate --> @prettygood ; };
(* Now give some services that Albert can provide *)
programming = BUSINESS_ASSOCIATE_SERVICE {
ref_business_associate_service --> @RV_programming;
business_associate --> @albert ; };
interp = BUSINESS_ASSOCIATE_SERVICE {
ref_business_associate_service --> @RV_seismic_interpretation;
business_associate --> @albert ; };
loganal = BUSINESS_ASSOCIATE_SERVICE {
ref_business_associate_service --> @RV_well_log_analysis;
business_associate --> @albert ; };
----> End Example: Business_associate_service.
Note that we have not yet said who Albert (or Bennett or Carl or Dennis) work for. That will come later.
All phone numbers of a business_associate (and the kind).
Caution: Actual returned values depend on the implementation, since ref_phone_number
is an aggregate, and various implementation handle aggregates differently
select area_code, local_phone_number, ref_phone_number.kind, ref_address_qualifier.name from phone_number where business_associate.identifier = 'Albert Albertson';
The fax number of a business_associate.
select area_code, local_phone_number, ref_address_qualifier.name from phone_number where business_associate.identifier = 'Albert Albertson' and ref_phone_number.kind = 'fax';
The mailing address of a business_associate.
select business_associate.identifier, line_1, line_2, line_3, geopolitical_feature.identifier, geopolitical_feature.unique_member_of.identifier, postal_code from mailing_address where business_associate.identifier = 'Pretty Good Seismic';
The email address of a business_associate.
select data_value from email_address where business_associate.identifier = 'Albert Albertson';
The services supplied by a company or a person.
select ref_business_associate_service.kind from business_associate_service where business_associate.identifier = 'Pretty Good Seismic';
All the business_associates that can perform a given service
select business_associate.identifier from business_associate_service where ref_business_associate_service.kind = 'seismic interpretation';
Warning: The previous section mentioned also that these phone numbers and addresses may have an expiration_date or an effective_date attached to them. Where more than one value of address or phone number is returned for a business_associate, it may be necessary to also check these dates to see which ones are active, and which ones have expired.
The previous section indicated how Epicentre can be used for a company database. This section gives another use of the business_associate - to act in roles. Some examples of roles are operator, driller, seismic processor, geologist, land owner, etc. Epicentre handles these in a common way.
Rather than listing the roles as attributes, Epicentre puts a "role" entity between the activity or object and the business_associate. This "role" entity allows us to tie the object to the business_associate, and to define the role.
For example, the WELL entity has a business_associate_facility_association that ties the well (or any facility) to a business_associate. The roles that can be used are defined in the reference entity, ref_business_associate_facility_role. Epicentre v2.2 had four values for the roles: operator, owner, drilling contractor, and maintenance. More can be added as needed.
It is interesting to look at the reasons for this structure, not only from the viewpoint of the relationship between a well (facility) and a business_associate, but as a general structure that is widely used in Epicentre. Consider the conventional way of having an operator and driller recorded for a well.:
entity: well attribute: operator (string) attribute: driller (string)
The user would enter a name for the operator, and another name for the driller. But two things are immediately wrong: there is no control over the names, and the companies are not shared. For example, a user could enter 'AAA Offshore' in one instance, and 'AAA Offshore, Inc' in another, and 'AAA Offshore US' in yet another - and may be talking about the same company. Also, there is no way to explicitly say that the 'AAA Offshore' entered as a well operator is the same as the 'AAA Offshore' entered in another entity somewhere else.
The solution is to create a business_associate entity with all the correct names, and have the entities point to them. Note that the two attributes have become two relationships. I.e., instead of the domain of the attributes being strings, they have now become instances.
This solved the two problems mentioned, but there is a third problem. The above only defines two roles for a business_associate: 'operator' and 'driller. There may be other roles that a person would like to record: 'bankroller', 'sitting geologist', 'regulatory agency contact', etc. Under the above, each of these additional roles would require another relationship. This leads to a proliferation of attributes, one for each role that might show up.
The Epicentre solution to this is to put a "role" entity (business_associate_facility_association or business_associate_activity_role) between the two, and let a reference entity (ref_business_associate_facility_role) define the roles. The model looks as follows:
This allows the business_associate to be associated with an activity (such as drilling the well), with the ref_business_associate_activity_role being used to define the role. In the above example, the driller could be associated with a well (actually, wellbore) activity in the role of 'driller.' It also allows the business_associate to be associated with a facility (such as a well or wellbore). An example of this would be a business_associate that is in the role of 'operator' for a well. See the Well Operator Example for an example of this usage.
The following will produce two activities - seismic processing and geoscience interpretation - and will use the business_associate_activity_role to say that Pretty Good Seismic did the processing, and Albert did the interpretation:
----> Begin Example: Using business_associate No. 1.
(* Instantiate the activity. Then tie to a business_associate. *)
process = SEISMIC_PROCESSING_ACTIVITY {
name --> 'Migration processing of Line G-24'; --some line in our database
ref_existence_kind --> @PRV_actual; };
whodidit = BUSINESS_ASSOCIATE_ACTIVITY_ROLE {
ref_business_associate_activity_role --> @PRV_processor;
business_associate --> @prettygood ; -- done by Pretty Good Seismic
activity --> @process ; };
interpret = GEOSCIENCE_INTERPRETATION {
name --> 'Interpretation of migrated line G-24';
ref_existence_kind --> @PRV_actual;};
interpreter = BUSINESS_ASSOCIATE_ACTIVITY_ROLE {
ref_business_associate_activity_role --> @PRV_interpreter;
business_associate --> @albert ; -- done by Albert Albertson
activity --> @interpret ; };
----> End Example: Using business_associate No. 1.
Of course, it is possible to include many more details in these instances - such as date, type of activity, etc. This example is to show only how the attachment to business_associate is made.
In general, a query in this section is used to find out who (person, company, etc.) is involved in what. Generally, this query is made on the role entity, since it contains the hooks to the appropriate information.
Find who the interpreter is.
select business_associate.identifier, activity.name from business_associate_activity_role where ref_business_associate_activity_role.kind = 'interpreter';
Find who the operator of a particular well is
select business_associate.identifier from business_associate_facility_association where facility[well].identifier = 'Lamb Ranch #1' and ref_business_associate_facility_role.kind = 'operator';
So far, business_associates have been treated individually. This section would like to specify groupings and relationships of business_associates. Examples would be the employees of a company, a project team, a consortium of companies, a consultant for a project.
These relationships are captured in the entity business_association. This entity allows us to say that A is related to B in a given role. For example, Albert Albertson is related to Zantac Exploration in the role of employee. Or Conoco is (was) a member of the consortium, CAGC*.
| * Note:CAGC no longer exists. Since an attribute of business_assjociate is end_time, it is possible to record this fact. For example, end_time --> '1979-06-01' would record that CAGC no longer exists after June 1, 1979. The purpose of keeping a no-longer existing business_associate is that there are still many lease maps that have CAGC on them. There also may be archived files in the corporate database that refer to CAGC. |
There is also a reference entity called ref_business_association_structure. This allows the use of a term that may have differenct meanings to different companies. For example, two companies merged with one having a job naming system with lower numbers than the second. So a job name of "12" in one company compared to a name of "20" or "21" in the other. This allows us say that a person has a name of "17," and state which naming system is being used. In one case, a "17" would be low, and in the other case, quite high.
In the following examples, we will state that some of the business_associates are employees of a company, and that another is a consultant. Then we will form a project team of three people within the company, with one of them as the team leader.
---> Begin Example: Working for Zantac
(* First form the employee-employer relationship *)
RV_zantac_hierarchy = REF_BUSINESS_ASSOCIATION_STRUCTURE {
identifier --> 'Zantac hierarchy';
description --> 'Zantac company organization with Zantac as the
employer and employees working for Zantac. Zantac
also has consultants and project teams. The
project teams are made up of individuals and/or
consultants, with one designated as team leader.'
source --> @RV_MyCompany; };
(* Indicate that the relationship works for persons and the company. *)
employee_rule = REF_BUSINESS_ASSOCIATION_STRUCTURE_RULE {
ref_business_association_role --> @PRV_employee;
ref_business_association_structure --> @RV_zantac_hierarchy ;
associated_type --> @PRV_company;
business_associate_type --> @PRV_person;
source --> @MyCompany; };
(* A person may also be a consultant for the company *)
consultant_rule = REF_BUSINESS_ASSOCIATION_STRUCTURE_RULE {
ref_business_association_role --> @PRV_consultant;
ref_business_association_structure --> @RV_zantac_hierarchy ;
associated_type --> @PRV_company;
business_associate_type --> @PRV_person;
source --> @MyCompany; };
(* Now apply the rules, to have the business_associates work for Zantac *)
alworks = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_employee;
business_associate --> @albert ;
associated_with --> @zantac ;
ref_business_association_structure --> @RV_zantac_hierarchy ;};
benworks = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_employee;
business_associate --> @bennett ;
associated_with --> @zantac ;
ref_business_association_structure --> @RV_zantac_hierarchy ;};
carlworks = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_employee;
business_associate --> @carl ;
associated_with --> @zantac ;
ref_business_association_structure --> @RV_zantac_hierarchy ;};
dennisworks = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_employee;
business_associate --> @dennis ;
associated_with --> @zantac ;
ref_business_association_structure --> @RV_zantac_hierarchy ;};
ericworks = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_consultant;
(* Note that Eric is in the role of consultant, not employee. *)
business_associate --> @eric ;
associated_with --> @zantac ;
ref_business_association_structure --> @RV_zantac_hierarchy
(* I can also record the fact that Eric's consulting contract runs out May 31, 1997 *)
end_time --> TIMESTAMP {
date --> DATE {
year --> 1997;
month --> 5;
day --> 31; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
};
---> End Example: Working for Zantac
Now we form the project team, and put Carl Carlson, Dennis Dennisson, and Eric Ericson on it. Dennis will be the team leader.
---> Begin Example: Milford Prospect Team
(* Set up the role of team leader *)
RV_team_leader = REF_BUSINESS_ASSOCIATE_JOB_TITLE {
kind --> 'team leader';
description --> 'The leader of a project team in the Zantac system.';
source --> @RV_MyCompany;
(* The structure rule of 'Zantac hierarchy' applies to project teams in Zantac. *)
team_in_zantac = REF_BUSINESS_ASSOCIATION_STRUCTURE_RULE {
ref_business_association_role --> @PRV_work_group;
ref_business_association_structure --> @RV_zantac_hierarchy ;
associated_type --> @PRV_company;
business_associate_type --> @PRV_project_team;
source --> @MyCompany; };
(* Persons can be on a project team *)
person_on_team = REF_BUSINESS_ASSOCIATION_STRUCTURE_RULE {
ref_business_association_role --> @PRV_work_group_member;
ref_business_association_structure --> @RV_zantac_hierarchy ;
associated_type --> @PRV_project_team;
business_associate_type --> @PRV_person;
source --> @MyCompany; };
(* The "Milford Prospect Team" will be a business_associate *)
milfordteam = BUSINESS_ASSOCIATE {
identifier --> 'Milford Prospect Team';
kind --> @PRV_project_team;
ref_naming_system --> @PRV_common_name; };
formteam = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_work_group;
business_associate --> @milfordteam ;
associated_with --> @zantac ;
ref_business_association_structure --> @RV_zantac_hierarchy ; };
carlonteam = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_work_group_member;
business_associate --> @carl;
associated_with --> @milfordteam ;
ref_business_association_structure --> @RV_zantac_hierarchy ; };
dennisonteam = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_work_group_member;
business_associate --> @dennis ;
associated_with --> @milfordteam ;
ref_business_association_structure --> @RV_zantac_hierarchy ; };
ericonteam = BUSINESS_ASSOCIATION {
ref_business_association_role --> @PRV_work_group_member;
business_associate --> @eric ;
associated_with --> @milfordteam ;
ref_business_association_structure --> @RV_zantac_hierarchy ; };
(* Now give Dennis the title of team leader *)
dennisisleader = BUSINESS_ASSOCIATE_JOB_TITLE {
ref_business_associate_job_title --> @RV_team_leader ;
business_association --> @dennisonteam ; }'
---> End Example: Milford Prospect Team
To find out all the employees in Zantac:
select business_associate.identifier from business_association where associated_with.identifier = 'Zantac Exploration' and ref_business_association_role.kind = 'employee';
To find the employees and consultants working for Zantac, look for 'person':
select business_associate.identifier from business_association where associated_with.identifier = 'Zantac Exploration' and business_associate.kind.kind = 'person';
To find the project teams, and their members:
select associated_with.identifier, business_associate.identifier from business_association where associated_with.kind.kind = 'project team';
To find the leader of all project teams:
select business_association.business_associate.identifier, business_association.associated_with.identifier from business_associate_job_title where ref_business_associate_job_title.kind = 'team leader';
Companies have partial ownership of various assets. Generally, these partial ownerships are of leases and wells. But they can also have partial ownership of other companies.
In Epicentre, all of these ownership percentages are shown in one of two ways. They either use the derived_interest_makeup entity or the interest_designation entity to show these percentages of ownership. The ERD diagram for this is shown in EPC3.
The examples following will show the use of these entities. Use the following scenario. On Dec. 12, 1991, Zantac Exploration spins off Clueless Energy Corp., retaining a 40% ownership in it. With this spin-off, Clueless Energy gets 'Lease A' in Texas which was previously owned by Zantac. In addition, Zantac keeps 50% of 'Lease B' in Oklahoma.
On March 18, 1994, Clueless Energy obtains 30% interest in 'Lease C' in Oklahoma. Its partners are not recorded in the database.
On June 12, 1996, Zantac obtains a 20% interest in 'Lease D' in New Mexico. It is in partnership with Googleplex Properties (30%) and Forward Looking Exploration (50%).
On July 27, 1996, Forward Looking sells out 40% of its share to Risque Ventures.
The above scenario will be instantiated on a historical basis. I.e., the Express-I will indicate what to instantiate with each of the happenings.
Note that the partial ownership of a company does not involve a contract. In general, the interest_designation entity is used for contractual interests. The derived_interest_makeup is used if it is not contractual.
Begin with the spin-off of Clueless Energy.
---> Example: Partial ownership of a company
clueless = BUSINESS_ASSOCIATE {
identifier --> 'Clueless Energy';
kind --> @PRV_company;
description --& 'Clueless Energy is a spin-off company from Zantac Exploration.';
start_time --> TIMESTAMP {
date --> DATE {
year --> 1991;
month --> 12;
day --> 12; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
ref_naming_system --> @PRV_common_name; };
(* Note that we have not stated that Clueless Energy is a
subsidiary of Zantac. It is not. It is a separate company *)
partowner = DERIVED_INTEREST_MAKEUP {
start_time --> TIMESTAMP {
date --> DATE {
year --> 1991;
month --> 12;
day --> 12; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
interest_for --> @clueless;
interest_of --> @zantac
fraction --> .40; };
--> End Example: Partial ownership of a company
'Lease A' is totally owned by Clueless, and Zantac has a 40% ownership only because it is a partial owner of Clueless. This will be recorded by giving Clueless Energy the full 100% ownership of the lease. Since a lease is a contractual agreement, it will be done by using the interest_designation entity. The 40% interest of Zantac is derived from the 40% stake that Zantac has in Clueless Energy.
One possible way to do this is to use the derived_interest_makeup to show that this interest is derived from one or more contracts. This method is not recommended.
It would cause problems if we tried to give Zantac 40% ownership using the derived interest mechanism. First of all, it is not an ownership portion that could be sold or traded to another party. The interest in the lease is totally because of the ownership of Clueless Energy. Secondly, it would cause problems if Zantac changed its fractional ownership of Clueless Energy. For example, if Zantac sells half of its stock in Clueless, it now owns 20% of Clueless. This could be indicated as in the partial ownership of a company example with fraction now being .20. But it would be difficult to now go back and change all the lease proportions to reflect this change. Finally, it would beg the question of who owns the other 60% of the lease.
'Lease B' will be divided between Clueless Energy and Zantac Exploration. Because Zantac owns a part of Clueless, the Zantac stockholders will really see more than 50% of the returns. But the record in Epicentre will show 50% ownership.
--> Begin example: Partial Ownership of a Lease
(* Create a surface_right to produce. This is the lease *)
leasearights = SURFACE_RIGHT {
identifier --> 'Lease A exploration rights';
ref_surface_right --> @PRV_area_of_mutual_interest; };
(* Form the land parcels for Lease A *)
leasea = LAND_PROPERTY_PARCEL {
identifier --> 'Lease A in Texas';
surface_right --> @leasearights;
};
(* Place the land leased in the land area of Texas *)
inTexas = TOPOLOGICAL_RELATIONSHIP {
primary_topological_object --> @texasLandArea;
(*Note: texasLandArea is general_surface_area instance that
is the land representing the geopolitical_feature, Texas*)
secondary_topological_object --> @leasea;
ref_object_intersection --> @PRV_inside;
boundary_overlap --> False; };
(* Now give Clueless Energy 100% ownership *)
ownleasea = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1991;
month --> 12;
day --> 12; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --18 Aug 1999
date --> DATE {
year --> 1999;
month --> 8;
day --> 18; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_operator; --ref_contract_designation
contract_designation_object --> SET( @leasearights );
business_associate --> @clueless
percent --> 100.; };
(* The same steps apply to 'Lease B' *)
leasebrights = SURFACE_RIGHT {
identifier --> 'Lease B exploration rights';
ref_surface_right --> @PRV_area_of_mutual_interest; };
(* Form the land parcels for Lease B *)
leaseb = LAND_PROPERTY_PARCEL {
identifier --> 'Lease B in Oklahoma';
surface_right --> @leasebrights;
};
(* Place the land leased in the land area of Oklahoma *)
inOkla1 = TOPOLOGICAL_RELATIONSHIP {
primary_topological_object --> @oklaLandArea;
(*Note: oklaLandArea is general_surface_area instance that
is the land representing the geopolitical_feature, Oklahoma*)
secondary_topological_object --> @leaseb;
ref_object_intersection --> @PRV_inside;
boundary_overlap --> False; };
(* Give Clueless Energy 50% ownership *)
ownleaseb1 = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1991;
month --> 12;
day --> 12; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --9 Jun 2000
date --> DATE {
year --> 2000;
month --> 6;
day --> 9; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_operator; --ref_contract_designation
contract_designation_object --> SET( @leasebrights );
business_associate --> @clueless
percent --> 50.; };
(* Give Zantac Exploration 50% ownership *)
ownleaseb2 = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1991;
month --> 12;
day --> 12; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --9 Jun 2000
date --> DATE {
year --> 2000;
month --> 6;
day --> 9; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_lessee; --ref_contract_designation
contract_designation_object --> SET( @leasebrights );
business_associate --> @zantac
percent --> 50.; };
--> End example: Partial Ownership of a Lease
On March 18, 1994, Clueless Energy obtained a new lease, with a 30% working interest. It is desired to store this information, but it is not necessary to store the percentage interests of the other lease holders.
--> Begin Example: Obtain new lease
leasecrights = SURFACE_RIGHT {
identifier --> 'Lease C exploration rights';
ref_surface_right --> @PRV_area_of_mutual_interest; };
(* Form the land parcels for Lease C *)
leaseb = LAND_PROPERTY_PARCEL {
identifier --> 'Lease C in Oklahoma';
surface_right --> @leasecrights;
};
(* Place the land leased in the land area of Oklahoma *)
inOkla2 = TOPOLOGICAL_RELATIONSHIP {
primary_topological_object --> @oklaLandArea;
secondary_topological_object --> @leasec;
ref_object_intersection --> @PRV_inside;
boundary_overlap --> False; };
(* Give Clueless Energy 30% ownership *)
ownleasec = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1994;
month --> 3;
day --> 18; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --17 Mar 2004
date --> DATE {
year --> 2004;
month --> 3;
day --> 17; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_lessee; --ref_contract_designation
contract_designation_object --> SET( @leasecrights );
business_associate --> @clueless
percent --> 30.; };
--> End Example: Obtain new lease.
The percentage of ownership may change during the life of a lease. This is reflected with Lease D in the above scenario. The response in Epicentre is to create new instances of interest_designation. In addition, instances of derived_interest_makeup can be used to keep track of the actual percentage interest from the various "deals" that have been made.
--> Begin Example: Change Percentage.
(* Form two new companies as business_associates *)
google = BUSINESS_ASSOCIATE {
identifier --> 'Googleplex Properties';
kind --> @PRV_company;
ref_naming_system --> @PRV_common_name; };
forward = BUSINESS_ASSOCIATE {
identifier --> 'Forward Looking Exploration';
kind --> @PRV_company;
ref_naming_system --> @PRV_common_name; };
(* Build the surface right *)
leasedrights = SURFACE_RIGHT {
identifier --> 'Lease D exploration rights';
ref_surface_right --> @PRV_area_of_mutual_interest; };
(* Form the land parcels for Lease D *)
leaseb = LAND_PROPERTY_PARCEL {
identifier --> 'Lease D in New Mexico';
surface_right --> @leasedrights;
};
(* Place the land leased in the land area of Oklahoma *)
inNM = TOPOLOGICAL_RELATIONSHIP {
primary_topological_object --> @nmLandArea;
secondary_topological_object --> @leased;
ref_object_intersection --> @PRV_inside;
boundary_overlap --> False; };
(* Give Clueless Energy 20% ownership *)
ownleased1 = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1996;
month --> 6;
day --> 12; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --11 June 2001
date --> DATE {
year --> 2001;
month --> 6;
day --> 11; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_lessee; --ref_contract_designation
contract_designation_object --> SET( @leasedrights );
business_associate --> @clueless
percent --> 20.; };
(* Give Googleplex Properties a 30% interest *)
ownleased2 = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1996;
month --> 6;
day --> 12; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --11 June 2001
date --> DATE {
year --> 2001;
month --> 6;
day --> 11; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_lessee; --ref_contract_designation
contract_designation_object --> SET( @leasedrights );
business_associate --> @google
percent --> 30.; };
(* Give Forward Looking a 50% interest *)
ownleased3 = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1996;
month --> 6;
day --> 12; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --11 June 2001
date --> DATE {
year --> 2001;
month --> 6;
day --> 11; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_lessee; --ref_contract_designation
contract_designation_object --> SET( @leasedrights );
business_associate --> @forward
percent --> 50.; };
(* Form the new company for the July 27 transaction *)
risque = BUSINESS_ASSOCIATE {
identifier --> 'Risque Ventures';
kind --> @PRV_company;
ref_naming_system --> @PRV_common_name; };
(* Give Risque and Forward Looking a 50% interest *)
ownleased4 = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1996;
month --> 7;
day --> 27; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --11 June 2001
date --> DATE {
year --> 2001;
month --> 6;
day --> 11; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_lessee; --ref_contract_designation
contract_designation_object --> SET( @leasedrights );
business_associate --> @forward
percent --> 50.; };
ownleased5 = INTEREST_DESIGNATION {
effective_date --> TIMESTAMP {
date --> DATE {
year --> 1996;
month --> 7;
day --> 27; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
expiry_date --> TIMESTAMP { --11 June 2001
date --> DATE {
year --> 2001;
month --> 6;
day --> 11; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
kind --> @PRV_working; --ref_interest_designation;
business_associate_role --> @PRV_lessee; --ref_contract_designation
contract_designation_object --> SET( @leasedrights );
business_associate --> @forward
percent --> 50.; };
(* Because of the percentage of a percentage, it is useful to
summarize the final derived interests using derived_interest_makeup *)
finalrisque = DERIVED_INTEREST_MAKEUP {
start_time --> TIMESTAMP {
date --> DATE {
year --> 1996;
month --> 7;
day --> 27; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
end_time --> TIMESTAMP { --11 June 2001
date --> DATE {
year --> 2001;
month --> 6;
day --> 11; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
interest_for --> @risque;
fraction --> .25;
interest_of --gt; @leasedrights;
basis --> SET( @ownleasd3, @ownleased4, @ownleased5 );
};
finalforward = DERIVED_INTEREST_MAKEUP {
start_time --> TIMESTAMP {
date --> DATE {
year --> 1996;
month --> 7;
day --> 27; };
time --> TIME {
hour --> 00;
minute --> 00;
second --> 00; };
};
end_time --> TIMESTAMP { --11 June 2001
date --> DATE {
year --> 2001;
month --> 6;
day --> 11; };
time --> TIME {
hour --> 23;
minute --> 59;
second --> 59; };
};
interest_for --> @forward;
fraction --> .25;
interest_of --gt; @leasedrights;
basis --> SET( @ownleasd3, @ownleased4, @ownleased5 );
};
--> End Example: Change Percentage.