How Can We Help?

Organisation synchronisation (Views)Organisation synchronisation (Views)

VIEW CREATE STATEMENTS

Download Postgres Organisation View Create Statements

Download Oracle Organisation View Create Statements

Download MSSQL Organisation View Create Statements

DATABASE DATATYPE MAPPINGS

Datatype to database mappings

Datatype	Postgres	Oracle	MSSQL
Double	Numeric	Number	Float
Long	Numeric	Number	Int
Integer	Numeric	Number	Int
Date	Date	Date	Date
String	Varchar	Varchar	Varchar
String/Clob	Text	Clob	Ntext
String/Blob	Bytea	Blob	Image
Classification	Varchar	Varchar	Varchar
Boolean	Boolean	Numeric(1,0)	Bit

MANDATORY VIEWS

ORGANISATION_DATA

This view is used to list all the organisations you want to synchronise into Pure. This means that one row should exist in this view pr organisation you want to have in Pure. For each organisation you have to specify an ORGANISATION_ID which will be the primary key for this organisation in the rest of the views. This means that this ID has to be unique for each organisation and cannot change over time. In the rest of the views, the ORGANISATION_ID will be used to relate content when using in a one-to-many relation.

The rest of the view is used to specify the simple meta data on the organisation

(*) To handle multiple languages _EN or _XX must be added to the column name.

SQL used to check the quality of the views	Description/Comment
select ORGANISATION_ID, count(ORGANISATION_ID) from ORGANISATION_DATA group by ORGANISATION_ID having count(ORGANISATION_ID) > 1	Check for duplicate organisation ids
select * from ORGANISATION_DATA where ORGANISATION_ID is null or TYPE is null or NAME is null	Check for missing mandatory fields
select distinct OWNER from ORGANISATION_DATA o1 where not exists (select * from ORGANISATION_DATA o2 where o1.OWNER = o2.ORGANISATION_ID)	Check for OWNER reference to non existing organisations
select distinct OWNER from ORGANISATION_DATA o where not exists (select * from ORGANISATION_HIERARCHY oh where o.OWNER = oh.PARENT_ORGANISATION_ID and o.ORGANISATION_ID = oh.CHILD_ORGANISATION_ID)	Check that the OWNER is actually a parent to the current organisation
select distinct TAKEN_OVER_BY from ORGANISATION_DATA o1 where not exists (select * from ORGANISATION_DATA o2 where o1.TAKEN_OVER_BY = o2.ORGANISATION_ID)	Check for TAKEN_OVER_BY reference to non existing organisations
select * from ORGANISATION_DATA where VISIBILITY not in ('public', 'campus', 'restricted')	Check for invalid visibility

ORGANISATION_HIERARCHY

This view specifies the organisational hierarchy, i.e. relations between one organisational unit and its parent/child organisational unit. There can be multiple entries per organisational unit, i.e. an organisational unit can have multiple parents.

Use the hierarchy view to see the hierarchy:

Column name	Data type	Size	Mandatory	Note
PARENT_ORGANISATION_ID	String	1024	Yes	The ID for the parent organisation in the relationship
CHILD_ORGANISATION_ID	String	1024	Yes	The ID for the child organisation in the relationship. 'Root' organisations for example University should never appear as child organisation.

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_HIERARCHY where PARENT_ORGANISATION_ID is null or CHILD_ORGANISATION_ID is null or PARENT_ORGANISATION_ID = CHILD_ORGANISATION_ID	Check for missing mandatory fields
select distinct CHILD_ORGANISATION_ID from ORGANISATION_HIERARCHY where not exists (select * from ORGANISATION_DATA where ORGANISATION_HIERARCHY.CHILD_ORGANISATION_ID = ORGANISATION_DATA.ORGANISATION_ID)	The CHILD_ORGANISATION_IDs in the hierarchy table where there is no ORGANISATION_ID in the ORGANISATION_DATA
select distinct PARENT_ORGANISATION_ID from ORGANISATION_HIERARCHY where not exists (select * from ORGANISATION_DATA where PARENT_ORGANISATION_ID = ORGANISATION_DATA.ORGANISATION_ID)	The PARENT_ORGANISATION_IDs in the hierarchy table where there is no ORGANISATION_ID in the ORGANISATION_DATA

SELECT * FROM ORGANISATION_DATA where ORGANISATION_ID not in (SELECT CHILD_ORGANISATION_ID FROM ORGANISATION_HIERARCHY);

OPTIONAL VIEWS

ORGANISATION_NAME_VARIANTS

An organisation can be known by different names depending on the context.

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the name variant to
TYPE	Classification value (view/edit classification configuration)	1024	Yes	The type of name variant. Provide the last part of the uri (key) of the classification.
NAME_VARIANT	String (*)	1024	Yes	The actual value.

(*) To handle multiple languages _EN or _XX must be added to the column name.

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_NAME_VARIANTS where ORGANISATION_ID is null or TYPE is null or NAME_VARIANT is null	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_NAME_VARIANTS op where not exists (select * from ORGANISATION_DATA o where op.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations
select o1.ORGANISATION_ID, o1.TYPE from ORGANISATION_NAME_VARIANTS o1 inner join ORGANISATION_NAME_VARIANTS o2 on o1.ORGANISATION_id = o2.ORGANISATION_id where o1.TYPE = o2.TYPE group by o1.ORGANISATION_id, o1.TYPE having count(o1.TYPE) > 1	Check whether the same type is used twice for the same organisation

ORGANISATION_PROFILE_INFO

If you have a textual description of the Organisations, this can be placed here. The data will be stored in XHTML in Pure, so the data can be formatted, but we need to know if it is. If it is HTML formatted we normally do a tidy up process to get it to be XHTML. Some formatting may be lost in this process (depending on the quality of the formatting)

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the profile information to
TYPE	Classification value (view/edit classification configuration)	1024	Yes	The type of profile information. Provide the last part of the uri (key) of the classification.
PROFILE_INFO	String (*)	clob	Yes	The actual value

(*) To handle multiple languages _EN or _XX must be added to the column name.

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_PROFILE_INFO where ORGANISATION_ID is null or TYPE is null or PROFILE_INFO is null	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_PROFILE_INFO op where not exists (select * from ORGANISATION_DATA o where op.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations
select o1.ORGANISATION_ID, o1.TYPE from ORGANISATION_PROFILE_INFO o1 inner join ORGANISATION_PROFILE_INFO o2 on o1.ORGANISATION_id = o2.ORGANISATION_id where o1.TYPE = o2.TYPE group by o1.ORGANISATION_id, o1.TYPE having count(o1.TYPE) > 1	Check whether the same type is used twice for the same organisation

ORGANISATION_PHOTO

If you have photos associated to the organisations, this can be placed here. It is supported to retrieve the photo from different types of sources, and you must specify which type is used for each row.

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the photo to
TYPE	Classification value (view/edit classification configuration)	1024	Yes	The type of photo. Provide the last part of the uri (key) of the classification.
PHOTO_VALUE	String/Blob		Yes	The actual value of the photo. Note the data type can vary based on what type of protocol
PHOTO_PROTOCOL	String	20	Yes	Used to select what type of protocol is used to retrieve the PHOTO_VALUE. The following protocols are supported HTTP - The PHOTO_VALUE must be a fully qualified URL to the photo. Note the data type must be string BYTE - The PHOTO_VALUE must contain a byte array of the entire photo. Note the data type must be clob FILE - The PHOTO_VALUE must be an absolute path to the file in the filesystem. Note the file must be readable my the tomcat running Pure.

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_PHOTO where ORGANISATION_ID is null or TYPE is null or PHOTO_VALUE is null or PHOTO_PROTOCOL is null	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_PHOTO op where not exists (select * from ORGANISATION_DATA o where op.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations
select o1.ORGANISATION_ID, o1.TYPE from ORGANISATION_PHOTO o1 inner join ORGANISATION_PHOTO o2 on o1.ORGANISATION_id = o2.ORGANISATION_id where o1.TYPE = o2.TYPE group by o1.ORGANISATION_id, o1.TYPE having count(o1.TYPE) > 1	Check whether the same type is used twice for the same organisation
select * from ORGANISATION_PHOTO where PHOTO_PROTOCOL not in ('HTTP', 'BYTE', 'FILE')	Check for invalid protocol type

ORGANISATION_PHONE_NUMBERS

There is synchronisation support for one phone number per type. If you want to synchronise two general phone number for one organisations, you can do that by adding types like "phone 1" and "phone 2".

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the phone number to
TYPE	Classification value (view/edit classification configuration)	1024	Yes	The type of phone number. Provide the last part of the uri (key) of the classification.
PHONE_NUMBER	String	64	Yes	The actual value.

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_PHONE_NUMBERS where ORGANISATION_ID is null or TYPE is null or PHONE_NUMBER is null	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_PHONE_NUMBERS op where not exists (select * from ORGANISATION_DATA o where op.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations
select o1.ORGANISATION_ID, o1.TYPE from ORGANISATION_PHONE_NUMBERS o1 inner join ORGANISATION_PHONE_NUMBERS o2 on o1.ORGANISATION_id = o2.ORGANISATION_id where o1.TYPE = o2.TYPE group by o1.ORGANISATION_id, o1.TYPE having count(o1.TYPE) > 1	Check whether the same type is used twice for the same organisation

ORGANISATION_EMAILS

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the email to
TYPE	Classification value (view/edit classification configuration)	1024	Yes	The type of email. Provide the last part of the uri (key) of the classification.
EMAIL	String	255	Yes	The actual value.

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_EMAILS where ORGANISATION_ID is null or TYPE is null or EMAIL is null	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_EMAILS oe where not exists (select * from ORGANISATION_DATA o where oe.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations
select o1.ORGANISATION_ID, o1.TYPE from ORGANISATION_EMAILS o1 inner join ORGANISATION_EMAILS o2 on o1.ORGANISATION_id = o2.ORGANISATION_id where o1.TYPE = o2.TYPE group by o1.ORGANISATION_id, o1.TYPE having count(o1.TYPE) > 1	Check whether the same type is used twice for the same organisation

ORGANISATION_WEB_ADDRESSES

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the web address to
TYPE	Classification value (view/edit classification configuration)	1024	Yes	The type of web address. Provide the last part of the uri (key) of the classification.
WEB_ADDRESS	String (*)	1024	Yes	The actual value.

(*) To handle multiple languages _EN or _XX must be added to the column name.

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_WEB_ADDRESSES where ORGANISATION_ID is null or TYPE is null or WEB_ADDRESS is null	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_WEB_ADDRESSES ow where not exists (select * from ORGANISATION_DATA o where ow.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations
select o1.ORGANISATION_ID, o1.TYPE from ORGANISATION_WEB_ADDRESSES o1 inner join ORGANISATION_WEB_ADDRESSES o2 on o1.ORGANISATION_id = o2.ORGANISATION_id where o1.TYPE = o2.TYPE group by o1.ORGANISATION_id, o1.TYPE having count(o1.TYPE) > 1	Check whether the same type is used twice for the same organisation

ORGANISATION_ADDRESSES

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the address to
TYPE	Classification value (view/edit classification configuration)	1024	Yes	The type of address. Provide the last part of the uri (key) of the classification.
CITY	String	1024	No
POSTAL_CODE	String	1024	No
STREET	String	1024	No
BUILDING	String	1024	No
COUNTRY	Classification (standard set)	1024	No	The Country is a classification scheme. The country keys are the ISO 3166-1 codes (see http://en.wikipedia.org/wiki/ISO_3166-1_alpha-2#Officially_assigned_code_elements)
SUBDIVISION	Classification (standard set)	1024	No	A subdivision of a country. Should be the last two tokens in a uri from: dk/atira/pure/core/countries/subdivision e.g. "us/ak" for Alaska. Note that the first token of the subdivision must match the country.
GEOSPATIAL_POINT	String	512	No	Uses the Google Maps format
GEOSPATIAL_POLYGON	String	clob	No	Uses the Google Maps format
DISPLAY_FORMAT	String	2048	No	If the display format is available on an address, this value is shown instead of the city, street etc fields. To create a line break in the displayFormat, please see these examples: Oracle and Postgres: 'this is a ' \|\| chr(13) \|\| ' linebreak' Microsoft SQL: 'this is a ' + CHAR(13) + ' linebreak' Please be aware that other characters may apply based on your local settings

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_ADDRESSES where ORGANISATION_ID is null or TYPE is null or ADDRESS is null	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_ADDRESSES oa where not exists (select * from ORGANISATION_DATA o where oa.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations
select o1.ORGANISATION_ID, o1.TYPE from ORGANISATION_ADDRESSES o1 inner join ORGANISATION_ADDRESSES o2 on o1.ORGANISATION_id = o2.ORGANISATION_id where o1.TYPE = o2.TYPE group by o1.ORGANISATION_id, o1.TYPE having count(o1.TYPE) > 1	Check whether the same type is used twice for the same organisation

ORGANISATION_KEYWORDS

This view contains the information about an organisation keywords. There is three different types of keywords you can synchronise.

Structured keywords only. Here you must specify the LOGICAL_NAME and TYPE column to identify what keyword you want to add.
Structured keywords and free keywords. Here you must specify LOGICAL_NAME, TYPE, and FREE_KEYWORD. Note the keyword group with the LOGICAL_NAME must support free keywords.
Free keywords only. Here you must specify LOGICAL_NAME and FREE_KEYWORD. Note the keyword group with the LOGICAL_NAME must support free keywords without structured keywords.

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The ID for the organisation to add this keyword. The ORGANISATION_ID must exist in the ORGANISATION_DATA view above.
LOGICAL_NAME	String	1024	Yes	The logical name of the keyword group configuration in Pure, e.g. 'keywordContainers' NOTE there must exists a keyword group for Organisation which has the specified logical name
TYPE	Classification	255	no	The type of structured keyword to add. Note you must provide the last part of the uri (key) of the classification you want to use. Note the allowed types depends on what classifications are in the keyword group with the provided logical name. I.e. if the classification scheme is Keywords (/dk/atira/pure/core/keywords) then a valid type value is 'A/AC' which will result in the keyword 'AC Collections. Series. Collected works'
FREE_KEYWORD	String(*)	1024	no	The free keyword to add. If no locale is specified in the column name, the keyword is added to all supported locales. If locales are provided, all supported locales must be specified To provide a locale you need to suffix the language to the column name and create a column for each supported locale. In the case of French and English this would result in the following columns: FREE_KEYWORD_EN FREE_KEYWORD_FR If locales are specified FREE_KEYWORD must not be present in your view without a locale suffix. The language should be appended in ISO 639-1 (two-letter codes)

(*) To handle multiple languages _EN or _XX must be added to the column name.

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_KEYWORDS where ORGANISATION_ID is null or LOGICAL_NAME is null or (TYPE is null and FREE_KEYWORD is null)	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_KEYWORDS ok where not exists (select * from ORGANISATION_DATA o where ok.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations

ORGANISATION_IDS

An organisation can be known by multiple ID's in different systems. For each organisation you have to supply an ORGANISATION_ID which will be this organisations primary ID. Besides this you have the possibility to add any other ID's you would like to have stored against the organisation.

Which other ID's you can add to an organisation is controlled via a classification scheme, so you can tailor this to your specific needs.

Since you can have multiple ID's against an organisation, you have to supply these additional ones in a separate view. In this you relate the ORGANISATION_ID, ID classification scheme value URI and the actual ID.

If you do not want to synchronised more ID's than the ORGANISATION_ID you should not implement this view.

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the ID to
ID_SOURCE	Classification value (view/edit classification configuration)	1024	Yes	The source where this ID is related to. Provide the last part of the uri (key) of the classification.
ID	String	64	Yes	The actual ID value

SQL used to check the quality of the views	Description/Comment
select * from ORGANISATION_IDS where ORGANISATION_ID is null or ID_SOURCE is null or ID is null	Check for missing mandatory fields
select distinct ORGANISATION_ID from ORGANISATION_IDS oi where not exists (select * from ORGANISATION_DATA o where oi.ORGANISATION_ID = o.ORGANISATION_ID)	Check for reference to non existing organisations
select o1.ORGANISATION_ID, o1.ID_SOURCE from ORGANISATION_IDS o1 inner join ORGANISATION_IDS o2 on o1.ORGANISATION_id = o2.ORGANISATION_id where o1.ID_SOURCE = o2.ID_SOURCE group by o1.ORGANISATION_id, o1.ID_SOURCE having count(o1.ID_SOURCE) > 1	Check whether the same source is used twice for the same organisation

ORGANISATION_COST_CENTERS (Module requirement)

Synchronisation of cost centers on organisations are only available if the Unified Project Model is enabled.

	Data type	Size	Mandatory	Note	Sync type. For each field pick on of: 'Yes', 'No', 'Once'
ORGANISATION_ID	String	1024	Yes	The organisation to add the cost center to.
TYPE	Classification	1024	Yes	The cost center classification. Provide the last part of the uri (key) of the classification.

ORGANISATION_LINKS

This view contains the information about organisation links.

Column name	Data type	Size	Mandatory	Note
ORGANISATION_ID	String	1024	Yes	The organisation to add the link to.
LINK_ID	String	1024	Yes	The ID of the link added, if links is not available in your source system, use row index
LINK_URL	String	1024	Yes	The Uniform Resource Locator (URL)
LINK_TYPE	Classification	255	No	The type of URL provided, this is an optional structured classifier. You must provide the last part of the URI (the classification key) of the classification you want to use. This classification has to exists in the list of possible link types for awards in Pure
LINK_DESCRIPTION	String(*)	1024	No_	A description for the link. Descriptions has to be added for all supported submission locales in your Pure system. Translations are provided in columns suffixed with language abbreviations. In case of of French and English this would result in the following columns: LINK_DESCRIPTION_EN LINK_DESCRIPTION_FR If locales are specified LINK_DESCRIPTION must not be present in your view without a language suffix. The language abbreviation should be appended as ISO 639-1 (two-letter codes)

Published at June 18, 2024

How Can We Help?

Search Results

Filter By Category

Contact Us

Organisation synchronisation (Views)Organisation synchronisation (Views)

VIEW CREATE STATEMENTS

DATABASE DATATYPE MAPPINGS

Datatype to database mappings

MANDATORY VIEWS

ORGANISATION_DATA

ORGANISATION_HIERARCHY

OPTIONAL VIEWS

ORGANISATION_NAME_VARIANTS

ORGANISATION_PROFILE_INFO

ORGANISATION_PHOTO

ORGANISATION_PHONE_NUMBERS

ORGANISATION_EMAILS

ORGANISATION_WEB_ADDRESSES

ORGANISATION_ADDRESSES

ORGANISATION_KEYWORDS

ORGANISATION_IDS

ORGANISATION_COST_CENTERS (Module requirement)

ORGANISATION_LINKS