How Can We Help?
Applications synchronisation (Views)Applications synchronisation (Views)
VIEW CREATE STATEMENTS
Download Postgres application view create statements
Download Oracle application view create statements
Download MSSQL server application view create statements
Base data
APPLICATION_DATA (Mandatory)
This view is used to list all the application you want to synchronise into Pure. This means that one row should exist in this view pr application you want to have in Pure. For each application you have to specify an APPLICATION_ID which will be the primary key for this application in the rest of the views. This means that this ID has to be unique for each application and cannot change over time. In the rest of the views, the APPLICATION_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 application
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The ID of the Application in your backend system. This ID will be the primary key for the particular Application and can thus not change over time. It will furthermore be used to make the correct connection to related content. | |
| APPLICATION_TYPE | Locked classification value | 1024 | Yes |
The type of application, possible options are: Base-UK
Base-DE
|
|
| TITLE | String(*) | 1024 | Yes | The title of the application | |
| SHORT_TITLE | String(*) | 256 | No | The short title of the application | |
| ACRONYM | String | 64 | No | The acronym for the application | |
| EXPECTED_START_DATE | Date | No | |||
| EXPECTED_END_DATE | Date | No | |||
| SUBMISSION_DEADLINE | Date | No | |||
| COLLABORATIVE_APPLICATION | Boolean | Yes | Specify whether this is a collaborative application or not. If it is a collaborative application, the collaborators (external organisations) must be specified in the EXTERNAL_APP_COLLABORATORS view | ||
| MANAGED_BY_ORG_ID | String | 1024 | Yes | This field is used to specify which internal organisation is managing the application. The MANAGED_BY_ORG_ID must match either a Pure ID, classification defined ID, source ID or Pure UUID of an organisation already present in Pure. | |
| MANAGED_IN_PURE | Boolean | No |
Indicates whether or not an application should be managed inside Pure. If set to TRUE the synchronisation will only import the application and not lock any fields/relations. In subsequent synchronisation runs, the synchronisation will not update the application, unless the MANAGED_IN_PURE state is changed to FALSE. If changed the application will be picked up by the synchronisation and handled as a synchronised content and fields and relations will be locked based on the configuration. The default behaviour for the synchronisation is to consider MANAGED_IN_PURE as FALSE if data us not supplied for it. |
||
| VISIBILITY | String | 12 | No |
The visibility of the application. If you supply a value it must be one of the following: public, campus, restricted, confidential If you do not supply a value the visibility field will be set to confidential by default. |
|
| WORFLOW | String | 256 | No |
Basic Workflow valid values: entryInProgress, forApproval, approved, validated Award Management Workflow valid values: draftProposal, preApproval, submittedToFunder, awaitingReplyFromFunder, receivedReplyFromFunder, aborted |
(*) 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 | Applicable if |
|---|---|---|
| select APPLICATION_ID, count(APPLICATION_ID) from APPLICATION_DATA group by APPLICATION_ID having count(APPLICATION_ID) > 1 | Check for duplicate application ids | |
| select * from APPLICATION_DATA where APPLICATION_ID is null or APPLICATION_TYPE is null or TITLE is null or COLLABORATIVE_APPLICATION is null or MANAGED_BY_ORG_ID is null | Check for missing mandatory fields | |
|
BASE-UK select * from APPLICATION_DATA where APPLICATION_TYPE not in ('researchcouncils/outlineapplication','researchcouncils/proposal','researchcouncils/renewal','researchcouncils/additionalfunding','eu/outlineapplication','eu/proposal','eu/negotiation','industry/proposal','industry/extension','othergovernmentdepartments/proposal','othergovernmentdepartments/extension','other/proposal','other/extension') BASE-DE select * from APPLICATION_DATA where APPLICATION_TYPE not in ('eu/stage1','eu/stage2','eu/proposal','eu/negotiation','federalresearchfunding/projectoutline','federalresearchfunding/fullproposal','federalresearchfunding/renewal','federalresearchfunding/additionalfunding','statescommunalfunding/projectoutline','statescommunalfunding/fullproposal','statescommunalfunding/renewal','statescommunalfunding/additionalfunding','privatesector/proposal','privatesector/additionalfunding','noncommercialfunding/proposal','noncommercialfunding/additionalfunding','germansciencefoundation/proposal','other/proposal','other/additionalfunding') |
Check for invalid application types | |
| select * from APPLICATION_DATA where VISIBILITY not in ('public', 'campus', 'restricted', 'confidential') | Check for invalid visibility | |
| select * from APPLICATION_DATA a where not exists (select * from INTERNAL_APPLICANTS ia where a.APPLICATION_ID = ia.APPLICATION_ID) | Check if there exists applications without any internal applicants | |
| select APPLICATION_ID from APPLICATION_DATA where APPLICATION_ID not in (select APPLICATION_ID from INTERNAL_APP_ORGANISATIONS) | Check if there exists applications without any internal organisations | |
| select * from APPLICATION_DATA a where COLLABORATIVE_APPLICATION = 1 and not exists (select * from EXTERNAL_APP_COLLABORATORS eac where a.APPLICATION_ID = eac.APPLICATION_ID) | Check for missing external collaborators when collaborative application | EXTERNAL_APP_COLLABORATORS is available |
| select * from APPLICATION_DATA a where COLLABORATIVE_APPLICATION = 1 | Check whether there are applications marked as collaborative application but there is no external collaborators | EXTERNAL_APP_COLLABORATORS is NOT available |
INTERNAL_APPLICANTS (Mandatory)
This view is used to specify the internal applicants on the application (i.e. staff from within your institution). One row must be provided pr application on the application. It is mandatory to add at least one internal applicant to each application.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the applicant to | |
| PERSON_ID | String | 1024 | Yes | The ID of the person to add as an applicant. The PERSON_ID must match either a Pure ID, classification defined ID, source ID or Pure UUID of a person already present in Pure. | |
| ORGANISATION_ID | String | 1024 | Yes | The ID of the organisation that should be added to this application in the content of the supplied applicant. The ORGANISATION_ID must match either a Pure ID, classification defined ID, source ID or Pure UUID of an organisation already present in Pure. | |
| ROLE | Locked classification value | 1024 | Yes |
The role of the applicant. Possible values are
for Principal Investigator and Co-Investigator. |
|
| ACADEMIC_OWNERSHIP_PERCENTAGE | Double | No | The academic ownership of the application for this applicant. If no value is provided the field will be set to "unspecified" in Pure, which differs from the value 0. Provided values should be between 0 and 1. | ||
| PLANNED_RESEARCHER_COMMITMENT | Double | No | The planned researcher commitment of the application for this applicant. If no value is provided the field will be set to "unspecified" in Pure, which differs from the value 0. Provided values should be between 0 and 1. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from INTERNAL_APPLICANTS where APPLICATION_ID is null or PERSON_ID is null or ORGANISATION_ID is null or ROLE is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from INTERNAL_APPLICANTS ia where not exists (select * from APPLICATION_DATA a where ia.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications | |
| select distinct ORGANISATION_ID from INTERNAL_APPLICANTS ia where not exists (select * from ORGANISATION_DATA o where ia.ORGANISATION_ID = o.ORGANISATION_ID) | Check for reference to non existing organisations | |
| select * from INTERNAL_APPLICANTS where ACADEMIC_OWNERSHIP_PERCENTAGE < 0 or ACADEMIC_OWNERSHIP_PERCENTAGE > 1 | Check for invalid academic ownership percentage |
EXTERNAL_APPLICANTS (Optional)
This view is used to specify the external applicants on the application (i.e. applications that do not work for your institution. If you never provide information on external applicants (or do not have them), you should not implement this view.
For each external applicant you have on an application one row should be in the view.
Depending on whether you synchronise external organisations in a separate synchronisation job or not, the view functions a bit differently.
Without separate external organisation synchronisation job
If you do not have a separate external organisation synchronisation job, then you need to use the fields called EXTERNAL_ORG_NAME and EXTERNAL_ORG_TYPE to specify the organisation for the external applicant.
When the synchronisation runs it does the following for each row in the view:
- Do a lookup in the list of external persons already in Pure to see if a person can found which matches exactly on firstname, lastname and country
- Do a lookup in the list of external organisations already in Pure to see if an organisations can be found which name matches exactly the name specified in the view and has the type specified in the view related to it
- If none of the two can be found a new external person and a new external organisation will be create. The two will be related to each other and be related to the application as well
- If an external person can be found but the external organisation cannot, then a new external person and a new external organisation will be create. The two will be related to each other and be related to the application as well
- If an external person cannot found, but the external organisation can, then a new external person is created and related to the found external organisation. The two will be related to each other and be related to the application as well
- If both an external person and an external organisation can be found, but the external organisation related the to the found external person in Pure is not the same as the external organisation specified in the view, then a new external person and a new external organisation will be create. The two will be related to each other and be related to the application as well
- If both an external person and an external organisation can be found, and the external organisation related to the found external person in Pure is the same as the external organisation specified in the view, then the found external person and external organisation is used and will be related to the application
Please note that the external person and external organisation is not kept synchronised, so if e.g. the name of an external applicant is changed in your backend system and feed into the views, what will happen is that a new external person will be created (since the one supplied no longer matches) and related to the application, while the existing one will be kept in Pure (but possible without any relations to content unless other content in the views matches this person).
With separate external organisation synchronisation job
When the synchronisation runs it does the following for each row in the view:
- Do a lookup in the list of external persons already in Pure to see if a person can found which matches exactly on firstname, lastname and country
- Find the external organisation already created in Pure via the EXTERNAL_ORG_ID value
- If the external organisation cannot be found, and error will be flagged (Question to Atira: Will the external person still be added ?)
- If the external person cannot be found a new external person will be create and the found external organisation will be related to it. The two will be related to the application as well
- if the external person can be found but the external organisation related the to the found external person in Pure is not the same as the external organisation specified in the view, a new external person will be create and the found external organisation will be related to it. The two will be related to the application as well
- If the external person can be found and the external organisation related to the found external person in Pure is the same as the external organisation specified in the view, then the found external person and external organisation is used and will be related to the application
Please note that the external person is not kept synchronised, so if the name of an external applicant is changed in your backend system and feed into the views, what will happen is that a new external person will be created (since the one supplied no longer matches) and related to the application, while the existing one will be kept in Pure (but possible without any relations to content unless other content in the views matches this person).
With no external organisation specified for external applicants
If you do not want to supply any external organisation information for your external applicants you should simply leave all of the following fields empty: EXTERNAL_ORG_NAME, EXTERNAL_ORG_TYPE, EXTERNAL_ORG_ID
When the synchronisation runs it does the following for each row in the view:
- Do a lookup in the list of external persons already in Pure to see if a person can found which matches exactly on firstname, lastname and country
- If the external person is found it will be related to the application. No external organisation will be related, even if the external person found is related to an external organisation in Pure
- If the external person is not found a new external person will be created in Pure and related to the application. No external organisation will be related to the created external person
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the applicant to | |
| FIRSTNAME | String | 1024 | Yes | ||
| LASTNAME | String | 1024 | Yes | ||
| COUNTRY | Classification (Locked set of values) | 1024 | No | The two digit countrycountr code from the ISO-3166 standard. E.g "gb" for United Kingdom, "us" for United States, "de" for Germany. The value is the last part of the countries classification in Pure. | |
| ROLE | Locked classification value | 1024 | Yes |
The role of the applicant. Possible values are
for Principal Investigator and Co-Investigator. |
|
| EXTERNAL_ORG_NAME | String | 1024 | No | Use this field to specify the name of the external organisation the external applicant is related to. When the synchronisation runs a lookup in exiting external organisations will be made to try to find one with an exact match on the name. If such an external organisation can be found, this one will be used and thus related to the external applicant. If no exact match can be found a new external organisation will be made and related to the external applicant. | |
| EXTERNAL_ORG_TYPE | Classification value (view/edit classification configuration) | 1024 |
When specifying the EXTERNAL_ORG_NAME you can also specify a type for the external organisation. Provide the URI for one of the values in the classification scheme. This type is used for matching the external organisation, unless this type of behavior is disabled in the synchronisation. The type "unknown" organisation type is ignored during matching, to provide the best possible matches. In the cases where a new external organisation is created in Pure as part of the synchronisation, this type will be applied to the created external organisation. In the cases where an existing external organisation is used (e.g. a match was found), this field is disregarded and the type is thus not changed. |
||
| EXTERNAL_ORG_ID | String | 1024 | No | Use this field to specify the ID of the external organisation the external applicant is related to.The EXTERNAL_ORG_ID must match either a Pure ID, source ID or Pure UUID of an external organisation already present in Pure. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from EXTERNAL_APPLICANTS where APPLICATION_ID is null or FIRSTNAME is null or LASTNAME is null or ROLE is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from EXTERNAL_APPLICANTS ea where not exists (select * from APPLICATION_DATA a where ea.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications | |
| select * from EXTERNAL_APPLICANTS where EXTERNAL_ORG_NAME is not null and EXTERNAL_ORG_ID is not null; | Check if both EXTERNAL_ORG_ID and EXTERNAL_ORG_NAME has been specified |
INT_APP_CO_MANAGING_ORG (Optional)
You can insert co-managing-organisations, but remember you can not to insert managedByOrganisation as a co-managing-organisation.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the applicant organisation to | |
| ORGANISATION_ID | String | 1024 | Yes |
The ID of the co-managing organisation that should be added to this application. The co-managing organisation, can not be the managedByOrganisation. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from INT_APP_ORG where APPLICATION_ID is null or ORGANISATION_ID is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from INT_APP_ORG iao where not exists (select * from APPLICATION_DATA a where iao.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications |
INTERNAL_APP_ORGANISATIONS (Optional)
Besides the internal organisations added though the INTERNAL_APPLICANTS view it is possible to add additional internal organisations. These will be added to the application on a general level, i.e., they will not be related though an applicant.
You should only use this view if you have additional internal organisations you want to add to your applications, and you should only put those organisation that has not already been related though an internal applicant in this view. For these reasons the view is therefore also optional.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the applicant organisation to | |
| ORGANISATION_ID | String | 1024 | Yes | The ID of the organisation that should be added to this application. The organisation will be added to the application on a general level, i.e., not though a specific applicant. The ORGANISATION_ID must match either a Pure ID, classification defined ID, source ID or Pure UUID of an organisation already present in Pure. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from INTERNAL_APP_ORGANISATIONS where APPLICATION_ID is null or ORGANISATION_ID is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from INTERNAL_APP_ORGANISATIONS iao where not exists (select * from APPLICATION_DATA a where iao.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications |
EXTERNAL_APP_ORGANISATIONS (Optional)
Besides the external organisations added though the EXTERNAL_APPLICANTS view it is possible to add additional external organisations. These will be added to the application on a general level, i.e., they will not be related though an external applicant.
You should only use this view if you have additional external organisations you want to add to your applications, and you should only put those organisation that has not already been related though an external applicant in this view. For these reasons the view is therefore also optional.
Depending on whether you synchronise external organisations in a separate synchronisation job or not, the view functions a bit differently.
Without separate external organisation synchronisation job
Specify the relation though the EXTERNAL_ORG_NAME and EXTERNAL_ORG_TYPE fields.
When the synchronisation runs it does the following for each row in the view:
- Do a lookup in the list of external organisations already in Pure to see if an organisations can be found which name matches exactly the name specified in the view and has the type specified in the view related to it
- If a match is found the existing external organisation is used and related to the application
- If no match is found an new external organisation with the specified type will be created in Pure and related to the application
With separate external organisation synchronisation job
Specify the relation though the EXTERNAL_ORG_ID field.
When the synchronisation runs it does the following for each row in the view:
- Find the external organisation already created in Pure via the EXTERNAL_ORG_ID value
- If the external organisation exists in Pure it will be related to the application
- If the external organisation does not exists in Pure an error will be raised (Note to Atira: Should this only be a warning?)
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the applicant organisation to | |
| EXTERNAL_ORG_NAME | String | 1024 | No | Use this field to specify the name of the external organisation. | |
| EXTERNAL_ORG_TYPE | Classification value (view/edit classification configuration) | 1024 | When specifying the EXTERNAL_ORG_NAME you can also specify a type for the external organisation. Provide the URI for one of the values in the classification scheme. This type is used for matching the external organisation, unless this type of behavior is disabled in the synchronisation. The type "unknown" organisation type is ignored during matching, to provide the best possible matches. | ||
| EXTERNAL_ORG_ID | String | 1024 | No | Use this field to specify the ID of the external organisation the external applicant is related to.The EXTERNAL_ORG_ID must match either a Pure ID, source ID or Pure UUID of an external organisation already present in Pure. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select distinct APPLICATION_ID from EXTERNAL_APP_ORGANISATIONS eao where not exists (select * from APPLICATION_DATA a where eao.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications | |
| select * from EXTERNAL_APP_ORGANISATIONS where (EXTERNAL_ORG_NAME is not null and EXTERNAL_ORG_ID is not null) or (EXTERNAL_ORG_NAME is null and EXTERNAL_ORG_ID is null) | Check that either external_org_name or external_org_id is specified |
EXTERNAL_APP_COLLABORATORS (Optional)
If your application is a collaborative application you have to specify which external organisations you are collaborating with on the application. Your own internal organisation will be added automatically.
Depending on whether you synchronise external organisations in a separate synchronisation job or not, the view functions a bit differently.
Without separate external organisation synchronisation job
Specify the relation though the EXTERNAL_ORG_NAME and EXTERNAL_ORG_TYPE fields.
When the synchronisation runs it does the following for each row in the view:
- Do a lookup in the list of external organisations already in Pure to see if an organisations can be found which name matches exactly the name specified in the view and has the type specified in the view related to it
- If a match is found the existing external organisation is used and related to the application as an external collaborator
- If no match is found an new external organisation with the specified type will be created in Pure and related to the application as an external collaborator
With separate external organisation synchronisation job
Specify the relation though the EXTERNAL_ORG_ID field.
When the synchronisation runs it does the following for each row in the view:
- Find the external organisation already created in Pure via the EXTERNAL_ORG_ID value
- If the external organisation exists in Pure it will be related to the application as an external collaborator
- If the external organisation does not exists in Pure an error will be raised (Note to Atira: Should this only be a warning?)
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the collaborator to | |
| EXTERNAL_ORG_NAME | String | 1024 | No | Use this field to specify the name of the external organisation. | |
| EXTERNAL_ORG_TYPE | Classification value (view/edit classification configuration) | 1024 | When specifying the EXTERNAL_ORG_NAME you can also specify a type for the external organisation. Provide the URI for one of the values in the classification scheme. This type is used for matching the external organisation, unless this type of behavior is disabled in the synchronisation. The type "unknown" organisation type is ignored during matching, to provide the best possible matches. | ||
| EXTERNAL_ORG_ID | String | 1024 | No | Use this field to specify the ID of the external organisation the external applicant is related to.The EXTERNAL_ORG_ID must match either a Pure ID, source ID or Pure UUID of an external organisation already present in Pure. | |
| LEAD_COLLABORATOR | Boolean | No | Specifies the lead collaborator. Only one lead collaborator is allowed per application. If no external collaborators are marked as lead, then the internal organisation will be lead. | ||
| COLLABORATOR_TYPE | Classification value | 1024 | No |
The type of collaborator, allowed options are:
|
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select distinct APPLICATION_ID from EXTERNAL_APP_COLLABORATORS eac where not exists (select * from APPLICATION_DATA a where eac.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications | |
| select * from EXTERNAL_APP_COLLABORATORS where (EXTERNAL_ORG_NAME is not null and EXTERNAL_ORG_ID is not null) or (EXTERNAL_ORG_NAME is null and EXTERNAL_ORG_ID is null) | Check that either external_org_name or external_org_id is specified |
Funding data
APP_FINANCIAL_FUNDINGS (Mandatory)
This view is used to specify a financial funding to add to the application. An application can have a number of fundings attached to it (but in most cases there will be only one). These fundings can either be financial or non financial. This view is used to add one or more financial fundings to the application. If you want to add non financial fundings as well you should use the view called APP_NON_FINANCIAL_FUNDINGS for that. Please note that an application can have both financial and non financial fundings attached to it, in which case you have to put data in both views for such an application.
Into this view you should put one row per financial funding you have on each of your applications. You specify to which application the funding should be attached by supplying the APPLICATION_ID which much match one of the APPLICATION_IDs you have supplied in the APPLICATION_DATA view. You must also supply an ID for the funding itself (the FUNDING_ID field). This ID must be unique within the specific application and is used when you want to specify the collaborators split (via the APP_COLLABORATOR_SPLIT) view.
For the financial funding itself you have to supply the funder, which should be an external organisation. The same lookup rules as described for the EXTERNAL_APPLICANTS view will be applied to try to match against an existing external organisation in Pure.
Besides this you have to supply the financial summary information for the funding. Please refer to the documentation after the view specification for further information on how to fill in each of the financial summary fields.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the funding to | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| FUNDING_ID | String | 1024 | Yes |
An ID that identifies this specific funding. This ID is used to relate further content such as classification scheme values, amounts for external collaborators etc. The FUNDING_ID needs to be unique and non-changing per APPLICATION_ID. E.g. APPLICATION_ID = 1000 and FUNDING_ID = 1, APPLICATION_ID = 1001 and FUNDING_ID = 1. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| EXTERNAL_ORG_NAME | String | 1024 | No | Use this field to specify the name of the funding organisation. This will be represented as an external organisation in Pure and related to the application with a "Funder" classification value on the relation | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| EXTERNAL_ORG_TYPE | Classification value (view/edit classification configuration) | 1024 | When specifying the EXTERNAL_ORG_NAME you can also specify a type for the external organisation. Provide the URI for one of the values in the classification scheme. This type is used for matching the external organisation, unless this type of behavior is disabled in the synchronisation. The type "unknown" organisation type is ignored during matching, to provide the best possible matches. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| EXTERNAL_ORG_ID | String | 1024 | No | Use this field to specify the ID of the external organisation the external applicant is related to.The EXTERNAL_ORG_ID must match either a Pure ID, source ID or Pure UUID of an external organisation already present in Pure. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| FUNDING_PROJECT_SCHEME | String | 1024 | No | The funding project scheme (research programme) of the funding. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| APPLIED_AMOUNT_APPLIED_CUR | Double | No | The amount you are submitting to the funder to apply for (in the applied currency) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| APPLIED_CURRENCY | Classification value | 1024 | No |
The currency the applied amount was applied in. Possible options are listed below. You must provide the ISO code.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| APPLIED_AMOUNT | Double | Yes | The amount you are submitting to the funder to apply for (in the system currency). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| INSTITUTIONAL_CONTRIBUTION | Double | No |
If you want to fill in information of full economical cost (fEC) you have to supply values in this field and the two below. Please see the example below this view specification for how to fill values in these fields. This field is used to specify the amount your institution have to add to the application in order to satisfy the fEC percentage set for this funding. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| INSTITUTIONAL_FEC | Double | No | This fields holds the total value for the funding, i.e. the sum of APPLIED_AMOUNT and INSTITUTIONAL_CONTRIBUTION | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| FEC_PERCENTAGE | Double | No | This field specifies the fEC percentage to be used on this funding. The value '0.1' will be interpreted as 10%, '1' as 100%, '1.5' as 150% etc. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| INSTITUTIONAL_PART | Double | No | Should only be provided if this is a collaborative application | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| VISIBILITY | String | 12 | No |
The visibility of the funding. If you supply a value it must be one of the following: public, campus, restricted If you do not supply a value the visibility field will be set to the value of the application. The application visibility overrules the funding visibility, which means you can't have a restricted application with a public visible funding. It's is possible to have a public visible application with a restricted funding. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| COST_CODE | String | 1024 | No | Financial control codes used to control the budget and expenditure on awards and applications on the funding |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APP_FINANCIAL_FUNDINGS where APPLICATION_ID is null or FUNDING_ID is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from APP_FINANCIAL_FUNDINGS aff where not exists (select * from APPLICATION_DATA a where aff.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications | |
| select aff1.APPLICATION_ID, aff1.FUNDING_ID from APP_FINANCIAL_FUNDINGS aff1 inner join APP_FINANCIAL_FUNDINGS aff2 on aff1.APPLICATION_ID = aff2.APPLICATION_ID where aff1.FUNDING_ID = aff2.FUNDING_ID group by aff1.APPLICATION_ID, aff1.FUNDING_ID having count(aff1.FUNDING_ID) > 1 | Check whether the same funding id is used twice for the same application | |
| select * from APP_FINANCIAL_FUNDINGS where (EXTERNAL_ORG_NAME is not null and EXTERNAL_ORG_ID is not null) or (EXTERNAL_ORG_NAME is null and EXTERNAL_ORG_ID is null) | Check that either external_org_name or external_org_id is specified | |
| select * from APP_FINANCIAL_FUNDINGS where FEC_PERCENTAGE < 0 | Check for invalid fEC percentage | |
| select * from APP_FINANCIAL_FUNDINGS aff inner join APPLICATION_DATA a on aff.APPLICATION_ID = a.APPLICATION_ID where INSTITUTIONAL_PART > 0 and a.COLLABORATIVE_APPLICATION = 0 | Check for institutional part on a non collaborative application | |
| select * from APP_FINANCIAL_FUNDINGS where VISIBILITY not in ('public', 'campus', 'restricted'); | Check for invalid visibility |
Financial summary with fEC for a non collaborative application
Below you can see an example of how to fill in the fields for the financial summary in the case where you want to provide fEC information on a non collaborative application.
In the example the fEC percentage is set to 80%. The amount of money we are after is 100.000 GBP which is recorded in the INSTITUTIONAL_FEC field. Given the fEC percentage the funder can be asked for 80.000 GBP (recorded in the APPLIED_AMOUNT field) and the institution have to contribute 20.000 (recorded in the INSTITUTIONAL_CONTRIBUTION field)
Financial summary with fEC for a collaborative application
Below you can see an example of how to fill in the fields for the financial summary in the case where you want to provide fEC information on a non collaborative application. In the example the application is a collaboration between the local institution and one external partner.
As with the example above the fEC percentage is also set to 80% and the amount applied for with the funder is likewise 80.000 GBP. This amount of money is split equally between the two collaborators, each collaborator getting 40.000 GBP from the funder. The actual split of the money is specified using the view called APP_COLLABORATOR_SPLIT which can be seen below.
The 40.000 is recorded in the field called COLLABORATORS_SPLIT in the APP_COLLABORATOR_SPLIT view. Since the fEC percentage is 80%, the local institution has to contribute 10.000 GBP (which is put into the field called INSTITUTIONAL_CONTRIBUTION as above), and the total amount the local institution will get is 50.000 (which is recorded in the INSTITUTIONAL_FEC field).
For the external partner the fEC is not recorded, only the partners split of the amount you are asking the funder for.
APP_NON_FINANCIAL_FUNDINGS (Optional)
This view is used to specify a non financial funding to add to the application. An application can have a number of fundings attached to it (but in most cases there will be only one). These fundings can either be financial or non financial. This view is used to add one or more non financial fundings to the application. If you want to add financial fundings as well you should use the view called APP_FINANCIAL_FUNDINGS for that. Please note that an application can have both financial and non financial fundings attached to it, in which case you have to put data in both views for such an application.
Into this view you should put one row per non financial funding you have on each of your applications. You specify to which application the funding should be attached by supplying the APPLICATION_ID which much match one of the APPLICATION_IDs you have supplied in the APPLICATION_DATA view. You must also supply an ID for the funding itself (the FUNDING_ID field). This ID must be unique within the specific application and is used when you want to specify the collaborators split (via the APP_COLLABORATOR_SPLIT) view.
For the non financial funding itself you have to supply the funder, which should be an external organisation. The same lookup rules as described for the EXTERNAL_APPLICANTS view will be applied to try to match against an existing external organisation in Pure.
Besides this you can supply an estimated value of the non financial funding as well as a description of it.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the funding to | |
| FUNDING_ID | String | 1024 | Yes |
An ID that identifies this specific funding. This ID is used to relate further content such as classification scheme values, amounts for external collaborators etc. The FUNDING_ID needs to be unique and non-changing per APPLICATION_ID. E.g. APPLICATION_ID = 1000 and FUNDING_ID = 1, APPLICATION_ID = 1001 and FUNDING_ID = 1. |
|
| EXTERNAL_ORG_NAME | String | 1024 | No | U | |
| EXTERNAL_ORG_TYPE | Classification value (view/edit classification configuration) | 1024 | When specifying the EXTERNAL_ORG_NAME you can also specify a type for the external organisation. Provide the URI for one of the values in the classification scheme. This type is used for matching the external organisation, unless this type of behavior is disabled in the synchronisation. The type "unknown" organisation type is ignored during matching, to provide the best possible matches. | ||
| EXTERNAL_ORG_ID | String | 1024 | No | Use this field to specify the ID of the external organisation the external applicant is related to.The EXTERNAL_ORG_ID must match either a Pure ID, source ID or Pure UUID of an external organisation already present in Pure. | |
| FUNDING_PROJECT_SCHEME | String | 1024 | No | ||
| ESTIMATED_VALUE | Double | No | |||
| INSTITUTIONAL_ESTIMATED_VALUE | Double | No | Should only be provided if this is a collaborative application. | ||
| DESCRIPTION | String | CLOB | No | ||
| VISIBILITY | String | 12 | No |
The visibility of the funding. If you supply a value it must be one of the following: public, campus, restricted If you do not supply a value the visibility field will be set to the value of the application. The application visibility overrules the funding visibility, which means you can't have a restricted application with a public visible funding. It's is possible to have a public visible application with a restricted funding. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APP_NON_FINANCIAL_FUNDINGS where application_id is null or funding_id is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from APP_NON_FINANCIAL_FUNDINGS anff where not exists (select * from APPLICATION_DATA a where anff.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications | |
| select anff1.application_id, anff1.funding_id from APP_NON_FINANCIAL_FUNDINGS anff1 inner join APP_NON_FINANCIAL_FUNDINGS anff2 on anff1.application_id = anff2.application_id where anff1.FUNDING_ID = anff2.FUNDING_ID group by anff1.application_id, anff1.funding_id having count(anff1.funding_id) > 1 | Check whether the same funding id is used twice for the same application | |
| select anff.APPLICATION_ID, anff.FUNDING_ID from APP_NON_FINANCIAL_FUNDINGS anff inner join APP_FINANCIAL_FUNDINGS aff on anff.APPLICATION_ID = aff.APPLICATION_ID where aff.FUNDING_ID = anff.FUNDING_ID | Check whether the same funding id is used twice for the same application as a financial funding and non financial fundings | |
| select * from APP_NON_FINANCIAL_FUNDINGS where (EXTERNAL_ORG_NAME is not null and EXTERNAL_ORG_ID is not null) or (EXTERNAL_ORG_NAME is null and EXTERNAL_ORG_ID is null) | Check that either external_org_name or external_org_id is specified | |
| select * from APP_NON_FINANCIAL_FUNDINGS where VISIBILITY not in ('public', 'campus', 'restricted'); | Check for invalid visibility |
APP_COLLABORATOR_SPLIT (Optional)
If your application is a collaborative application you can chose if you want to specify how the money applied for should be split between the collaborators. This is an optional view and you do not have to implement this if you do not want to supply the split or if you do not have collaborative application (it is perfectly OK to have a collaborative application on which you record your collaborators but not the split between them).
As a rule of thumb, if you chose to record the split between the collaborators you have to put in one row in this view pr collaborator pr application pr funding.
Please note that besides identifying the application to make the split for (via the APPLICATION_ID field) you also have to identify the funding (via the FUNDING_ID field). This is because you can have multiple fundings on an application.
Besides identifying the application and funding to add the split to you also have to specify the collaborator that you are supplying the split for. These must match the same collaborators that you have supplied in the EXTERNAL_APP_COLLABORATORS view. They are looked up using the same methods as described for the EXTERNAL_APPLICANTS view.
The final field to provide is the actual split. If the split related to a financial funding this represent a part of the applied amount. If the split is for a non financial funding this represents a part of the estimated value.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the funding to | |
| FUNDING_ID | String | 1024 | Yes | The Funding ID references the the Funding ID (together with the APPLICATION_ID) provided in the APP_FINANCIAL_FUNDINGS view. | |
| EXTERNAL_ORG_NAME | String | 1024 | No | Use this field to specify the name of the funding organisation. This will be represented as an external organisation in Pure and related to the application with a "Funder" classification value on the relation | |
| EXTERNAL_ORG_TYPE | Classification value (view/edit classification configuration) | 1024 | When specifying the EXTERNAL_ORG_NAME you can also specify a type for the external organisation. Provide the URI for one of the values in the classification scheme. This type is used for matching the external organisation, unless this type of behavior is disabled in the synchronisation. The type "unknown" organisation type is ignored during matching, to provide the best possible matches. | ||
| EXTERNAL_ORG_ID | String | 1024 | No | Use this field to specify the ID of the external organisation the external applicant is related to.The EXTERNAL_ORG_ID must match either a Pure ID, source ID or Pure UUID of an external organisation already present in Pure. | |
| COLLABORATORS_SPLIT | Double | No | This field is used for specify the share that this particular collaborator is getting from the total applied amount. Please see the documentation for the APP_FINANCIAL_FUNDINGS views for further information on how to supply the financial summary data. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APP_COLLABORATOR_SPLIT acs where not exists (select * from APP_FINANCIAL_FUNDINGS aff, APP_NON_FINANCIAL_FUNDINGS anff where (aff.APPLICATION_ID = acs.APPLICATION_ID and aff.FUNDING_ID = acs.FUNDING_ID) or (anff.APPLICATION_ID = acs.APPLICATION_ID and anff.FUNDING_ID = acs.FUNDING_ID)) | Check that the corresponding non financial funding or financial funding could be found | |
| select * from APP_COLLABORATOR_SPLIT acs where not exists (select * from EXTERNAL_APP_COLLABORATORS eac where acs.APPLICATION_ID = eac.APPLICATION_ID and (eac.EXTERNAL_ORG_NAME is not null and eac.EXTERNAL_ORG_NAME = acs.EXTERNAL_ORG_NAME and eac.EXTERNAL_ORG_TYPE = acs.EXTERNAL_ORG_TYPE) or (eac.EXTERNAL_ORG_ID is not null and eac.EXTERNAL_ORG_ID = acs.EXTERNAL_ORG_ID)) | Checks that a corresponding external organisation could be found in EXTERNAL_APP_COLLABORATORS | |
| select * from APP_COLLABORATOR_SPLIT where (EXTERNAL_ORG_NAME is not null and EXTERNAL_ORG_ID is not null) or (EXTERNAL_ORG_NAME is null and EXTERNAL_ORG_ID is null) | Check that either external_org_name or external_org_id is specified |
APP_FUNDING_CLASSIFICATION (Optional)
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the funding to | |
| FUNDING_ID | String | 1024 | Yes | The Funding ID references the the Funding ID (together with the APPLICATION_ID) provided in the APP_FINANCIAL_FUNDINGS view. | |
| CLASSIFICATION_VALUE | Classification value | 1024 | Yes |
The classification value. You may only supply a single classification per classification scheme for each combination of APPLICATION_ID and FUNDING_ID. Base-UK: HESA and REF classifications are available. HESA
REF
/dk/atira/pure/funding/refincomesources/10 /dk/atira/pure/funding/refincomesources/11 /dk/atira/pure/funding/refincomesources/12 /dk/atira/pure/funding/refincomesources/13 /dk/atira/pure/funding/refincomesources/14
Base-DE:
|
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APP_FUNDING_CLASSIFICATION where APPLICATION_ID is null or FUNDING_ID is null or CLASSIFICATION_VALUE is null | Checking for missing mandatory fields | |
| select * from APP_FUNDING_CLASSIFICATION afc where not exists (select * from APP_FINANCIAL_FUNDINGS aff, APP_NON_FINANCIAL_FUNDINGS anff where (aff.APPLICATION_ID = afc.APPLICATION_ID and aff.FUNDING_ID = afc.FUNDING_ID) or (anff.APPLICATION_ID = afc.APPLICATION_ID and anff.FUNDING_ID = afc.FUNDING_ID)) | Check that the corresponding non financial funding or financial funding could be found |
APP_BUDGET (Optional)
This view provides the budgets for the financial overview below. With Pure version 5.4+ is will be possible to synchronize budgets on existing fundings, only the view APP_BUDGET, APP_BUDGET_LINE is required.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the budget to | |
| FUNDING_ID | String | 1024 | Yes | The FUNDING_ID references the Funding ID (together with the APPLICATION_ID) provided in the APP_FINANCIAL_FUNDINGS view. | |
| BUDGET_ID | String | 1024 | Yes |
An ID that identifies this specific budget. This ID is used to relate budget lines. The BUDGET_ID needs to be unique and non-changing per combination of APPLICATION_ID and FUNDING_ID. E.g. APPLICATION_ID= 1000, FUNDING_ID = 1, and BUDGET_ID = 1 |
|
| ORGANISATION_ID | String | 1024 | No (Yes) |
The ID of the organisation this budget is associated with. The ORGANISATION_ID must match one of the ORGANISATION_ID rows provided in the organisation synchronisation. If you have more than one budget per funding then this field is mandatory. |
|
| COST_CENTER | Classification value ( view/edit classification configuration) | 1024 | No (Yes) |
The cost center this budget is associated with. Provide the URI for one of the values in the classification scheme. If you have more than one budget per funding then this field is mandatory. |
|
| COST_CODE | String | 1024 | No | Financial control codes used to control the budget and expenditure on awards and applications on the funding |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APP_BUDGET where APPLICATION_ID is null or FUNDING_ID is null or BUDGET_ID is null | Check for missing mandatory fields | |
| select * from APP_BUDGET b where not exists (select * from APP_FINANCIAL_FUNDINGS aff, APP_NON_FINANCIAL_FUNDINGS anff where (aff.APPLICATION_ID= b.APPLICATION_ID and aff.FUNDING_ID = b.FUNDING_ID) or (anff.APPLICATION_ID = b.APPLICATION_ID and anff.FUNDING_ID = b.FUNDING_ID)) | Check that the corresponding non financial funding or financial funding could be found | |
| select b1.APPLICATION_ID, b1.FUNDING_ID, b1.BUDGET_ID from APP_BUDGET b1 inner join APP_BUDGET b2 on b1.APPLICATION_ID = b2.APPLICATION_ID where b1.FUNDING_ID = b2.FUNDING_ID and b1.BUDGET_ID = b2.BUDGET_ID group by b1.APPLICATION_ID, b1.FUNDING_ID, b1.BUDGET_ID having count(b1.BUDGET_ID) > 1 | Check whether the same budget id is used twice for the same application as a financial funding or non financial fundings | |
| select b1.APPLICATION_ID, b1.FUNDING_ID, b1.BUDGET_ID, b1.ORGANISATION_ID, b1.COST_CENTER from APP_BUDGET b1 inner join APP_BUDGET b2 on b1.APPLICATION_ID = b2.APPLICATION_ID where b1.FUNDING_ID = b2.FUNDING_ID and (b1.ORGANISATION_ID is null or b1.COST_CENTER is null) group by b1.APPLICATION_ID, b1.FUNDING_ID, b1.BUDGET_ID, b1.ORGANISATION_ID, b1.COST_CENTER having count(b1.BUDGET_ID) > 1 | Check whether ORGANISATION_ID and COST_CENTER is set when more than one budget per funding | |
| select distinct ORGANISATION_ID from APP_BUDGET b where not exists (select * from ORGANISATION_DATA o where b.ORGANISATION_ID = o.ORGANISATION_ID) | Check for reference to non existing organisations | |
| select * from APP_BUDGET b where not exists (select * from APP_BUDGET_LINE bl where bl.APPLICATION_ID = b.APPLICATION_ID and bl.FUNDING_ID = b.FUNDING_ID and bl.BUDGET_ID = b.BUDGET_ID) | Check that at least one budget line is related to this budget |
APP_BUDGET_LINE (Optional)
This view provides the budget lines and values for the financial overview below:
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the budget line to | |
| FUNDING_ID | String | 1024 | Yes | The FUNDING_ID references the Funding ID (together with the APPLICATION_ID) provided in the APP_FINANCIAL_FUNDINGS view. | |
| BUDGET_ID | String | 1024 | Yes | The BUDGET_ID references the Budget ID (together with the APPLICATION_ID and FUNDING_ID) provided in the APP_BUDGET view. | |
| BUDGET_LINE | Classification value (view/edit classification configuration) | 1024 | Yes | The budget line to add the budget value for. If you do not want to provide budget against individual budget lines, please make sure the module the module is configured for this and that the budget line classification schema only contains one single classification value called Total. Please consult your Atira project manager to make sure this is setup correctly. | |
| BUDGET_VALUE | Double | Yes | The actual budget value to add to the application. If you use multiple budget lines, you have to provide a budget value for each of them. The total budget will be calculated as the sum of all of these. You do not need to provide a budget value for each of the budget lines you have specified | ||
| COST_CENTER | Classification value ( view/edit classification configuration) | 1024 | No | New in Pure 4.18.0: The cost center this budget line is associated with. Provide the URI for one of the values in the classification scheme. | |
| SPECIFICATION | String | 256 | No | New in Pure 4.18.0: A descriptive text for this budget line. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APP_BUDGET_LINE where APPLICATION_ID is null or FUNDING_ID is null or BUDGET_ID is null or APP_BUDGET_LINE is null or BUDGET_VALUE is null | Check for missing mandatory fields | |
| select * from APP_BUDGET_LINE bl where not exists (select * from APP_BUDGET b where bl.APPLICATION_ID = b.APPLICATION_ID and bl.FUNDING_ID = b.FUNDING_ID and bl.BUDGET_ID = b.BUDGET_ID) | Check that the corresponding budget could be found |
APP_BUDGET_LINE_YEARLY_BUDGET
This view provides the yearly budget for the budget lines. This view is optional and is only to be used if you want to provide yearly budgets to Pure, instead of just a total budget per budget line.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the budget line yearly budget to | |
| FUNDING_ID | String | 1024 | Yes | The FUNDING_ID references the Funding ID (together with the APPLICATION_ID) provided in the APP_FINANCIAL_FUNDINGS view. | |
| BUDGET_ID | String | 1024 | Yes | The BUDGET_ID references the Budget ID (together with the APPLICATION_ID and FUNDING_ID) provided in the APP_BUDGET view. | |
| BUDGET_LINE | Classification value (view/edit classification configuration) | 1024 | Yes | The APP_BUDGET_LINE references the APP_BUDGET_LINE in the APP_BUDGET_LINE view (together with the APPLICATION_ID, FUNDING_ID and BUDGET_ID) provided in the APP_BUDGET view. | |
| YEAR | Integer | Yes | The year for the budget value. Eg. 2012 | ||
| BUDGET_VALUE | Double | Yes | The actual budget value to add to the application for the specified year. If you use multiple budget lines, you have to provide a budget value for each of them. The total budget will be calculated as the sum of all of these. You do not need to provide a budget value for each of the budget lines you have specified |
Additional data
INT_APPLICANTS_COMMITMENT (Optional)
This view is used to specify the internal applicants researcher commitment on the application. One row must be provided pr applicant on the applicant pr month.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the commitment to. | |
| PERSON_ID | String | 1024 | Yes | The ID of the person to add as an applicant. The PERSON_ID must match one of the PERSON_ID rows provided in the person/staff synchronisation. | |
| YEAR | Integer | Yes | The year for the researcher commitment. Eg. 2012. | ||
| MONTH | Integer | Yes | The month for the researcher commitment Eg. 7 for July. | ||
| PLANNED_COMMITMENT_PERCENTAGE | Double | No | The planned researcher commitment of the application for this applicant for this month. If no value is provided the field will be set to "unspecified" in Pure, which differs from the value 0. Provided values should be between 0 and 1. | ||
| ACTUAL_COMMITMENT_PERCENTAGE | Double | No | The actual researcher commitment of the application for this awardholder for this month. If no value is provided the field will be set to "unspecified" in Pure, which differs from the value 0. Provided values should be between 0 and 1. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from INT_APPLICANTS_COMMITMENT where APPLICATION_id is null or PERSON_ID is null YEAR is null or MONTH is null | Check for missing mandatory fields | |
| select * from INT_APPLICANTS_COMMITMENT c where not exists (select * from INTERNAL_APPLICANTS p where p.APPLICATION_ID = c.APPLICATION_ID and p.PERSON_ID = c.PERSON_ID) | Check for reference to non existing internal applicants |
APP_APP_RELATION (Optional)
This view is used to specify the relation between different types of applications. As an example, if you have submitted both an outline application and a new application to a funder, both of these should be represented individually in the APPLICATION_DATA view with different APPLICATION_ID values. The APP_APP_RELATION view is then used to specify the relation between these, i.e., that the new application was made after the outline application was approved. It is very important that these relationships are represented correctly since reporting on e.g. success rates could otherwise be off.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | ||
| TARGET_APPLICATION_ID | String | 1024 | Yes |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APP_APP_RELATION where APPLICATION_ID is null or TARGET_APPLICATION_ID is null or APPLICATION_ID = TARGET_APPLICATION_ID; | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from APP_APP_RELATION aap where not exists (select * from APPLICATION_DATA a where aap.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications | |
| select distinct TARGET_APPLICATION_ID from APP_APP_RELATION aap where not exists (select * from APPLICATION_DATA a where aap.TARGET_APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing target applications |
APPLICATION_ACTIVITY_TYPE (Optional)
This view is used for classifying your application with one or more Nature of activity types. The Nature of activity type is a classification scheme so you can select your own value to chose from, typical examples could be "Research", "Clinical trial" etc. Since you are allowed to select more than one Nature of activity type for each application you have to supply this data in a separate view, mapping the APPLICATION_ID to the classification scheme value URI.
If you do not want to synchronise any Nature of activity types to your applications at all you should not implement this view
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | ||
| ACTIVITY_TYPE | Classification value (view/edit classification configuration) | 1024 | Yes | Nature of activity type. Provide the classification key (e.g. "research") for one of the values in the classification scheme. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APPLICATION_ACTIVITY_TYPE where APPLICATION_ID is null or ACTIVITY_TYPE is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from APPLICATION_ACTIVITY_TYPE aat where not exists (select * from APPLICATION_DATA a where aat.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications |
APPLICATION_DESCRIPTIONS (Optional)
An application can have a number of descriptions. How many should be available in your system is controlled by a classification scheme. Adding more types of descriptions to your application is thus just a matter of adding more values to this classification scheme. Please note that all applications will have all the description fields though.
Since you can have multiple descriptions on an application, you have to supply these via a separate view. In this you relate the APPLICATION_ID, description classification scheme value URI and the description itself.
If you do not want to synchronise any descriptions into your applications you should not implement this view.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the description to | |
| DESCRIPTION_TYPE | Classification value (view/edit classification configuration) | 1024 | Yes | The type of description to add. Provide the URI for one of the values in the classification scheme. | |
| DESCRIPTION_TEXT | String (*) | CLOB | Yes | The actual description text |
(*) 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 | Applicable if |
|---|---|---|
| select * from APPLICATION_DESCRIPTIONS where APPLICATION_ID is null or DESCRIPTION_TYPE is null or DESCRIPTION_TEXT is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from APPLICATION_DESCRIPTIONS ad where not exists (select * from APPLICATION_DATA a where ad.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications |
APPLICATION_IDS (Optional)
An application can be known by multiple ID's in different systems. This could be you award management system, costing system, the funders system (funder reference code) or similar. For each application you have to supply an APPLICATION_ID which will be this applications primary ID. Besides this you have the posibility to add any other ID's you would like to have stored against the application.
Which other ID's you can add to an application is controlled via a classification scheme, so you can tailor this to your specific needs.
Since you can have multiple ID's against an application, you have to supply these additional ones in a separate view. In this you relate the APPLICATION_ID, ID classification scheme value URI and the actual ID.
If you do not want to synchronised more ID's than the APPLICATION_ID you should not implement this view.
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application 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 URI for one of the values in the classification scheme | |
| ID | String | 64 | Yes | The actual ID value |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APPLICATION_IDS where APPLICATION_ID is null or ID_SOURCE is null or ID is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from APPLICATION_IDS ai where not exists (select * from APPLICATION_DATA a where ai.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications |
APPLICATION_STATUS (Optional)
An application can have a number of statuses - one current and the rest in the past. The APPLICATION_STATUS view is used to supply these.
If you only want to supply the current status for the application you should make sure that at all times the view only contain one row pr application.
If you want to preserve the status history you have two options:
- Make sure all the statuses are in the view. In that case all will be synchronised and the one with the latest date will be the current one
- Only supply the newest status but configure the sync job to retain all old statuses. In this case the synchronisation job will not delete any statuses at all, but will only mark them as in the past when they are removed from the view, and thus be building the status history over time
Please notice that the statuses has to be selected form a predefined and generalized set of statuses. These will most likely not exist in your backend system so you have to make a mapping between these and the statuses used in Pure. In some cases you might need to map more than one status in your backend system to the same status in Pure (depending if your detail level on statuses is higher than that used in Pure).
| Column name | Data type | Size | Mandatory | Note | Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The application to add the status to | |
| STATUS | Classification value | 1024 | Yes |
The status to apply to the application. Provide one of the following values:
With Award Management Module enabled, the mapping is the following:
|
|
| STATUS_DATE | Date | Yes | The date when the status was applied. The date is used to order the presentation of the statuses in Pure, and the status with the most current WHEN date will be displayed as the current status. |
| SQL used to check the quality of the views | Description/Comment | Applicable if |
|---|---|---|
| select * from APPLICATION_STATUS where APPLICATION_ID is null or STATUS is null or STATUS_DATE is null | Check for missing mandatory fields | |
| select distinct APPLICATION_ID from APPLICATION_STATUS app where not exists (select * from APPLICATION_DATA a where app.APPLICATION_ID = a.APPLICATION_ID) | Check for reference to non existing applications |
APPLICATION_KEYWORDS (Optional)
This view contains the information about APPLICATION 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 |
Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The ID for the APPLICATION to add this keyword. The APPLICATION_ID must exist in the APPLICATION_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 APPLICATION 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.
select * from APPLICATION_DATA where APPLICATION_TYPE not in ('researchcouncils/outlineapplication','researchcouncils/proposal','researchcouncils/renewal','researchcouncils/additionalfunding','eu/outlineapplication','eu/proposal','eu/negotiation','industry/proposal','industry/extension','othergovernmentdepartments/proposal','othergovernmentdepartments/extension','other/proposal','other/extension')
APPLICATION_DOCUMENT - Available from Pure 5.5.x
| Column name | Data type | Size | Mandatory | Note |
Sync type. For each field pick on of: 'Yes', 'No', 'Once' |
|---|---|---|---|---|---|
| APPLICATION_ID | String | 1024 | Yes | The unique value for the thesis | |
| DOCUMENT_ID | String | 1024 | Yes | The unique value for the student thesis document relation | |
| TYPE | Classification | 1024 | Yes | Value from classification scheme /dk/atira/pure/core/document/types | |
| VALUE | String/Blob | 1024 | Yes | Where to fetch the document from for example an URL. Use Blob if BYTE protocol is used | |
| PROTOCOL | String | 1024 | Yes |
The following protocols are supported: BYTE, FILE & HTTP It is important that the value used is exactly identical to the above mentioned. |
|
| FILE_NAME | String | 1024 | No | The document filename | |
| MIME_TYPE | String | 1024 | No | The document mimetype | |
| FILE_TITLE | String | 1024 | No | The document title | |
| VISIBILITY | String | 1024 | No |
If you want to you can set the visibility of the person. It can be either Publicly available, Campus (IP address) or Restricted to Pure users. If you supply a value it must be one of the following: public, campus, restricted If no value is specified, Publicly available will be selected automatically. |
COST_CODEPublished at November 17, 2023