How Can We Help?

Award Budget & Expenditure Synchronisation (Views)Award Budget & Expenditure Synchronisation (Views)

BUDGET (Mandatory)

This view provides the budgets for the financial overview below.

Column name	Data type	Size	Mandatory	Note
AWARD_ID	String	1024	Yes	The award to add the budget to. The ID can be the external source id, the internal Pure id or a classified source id on the existing Award
FUNDING_ID	String	1024	Yes	The FUNDING_ID references the funding (together with the AWARD_ID). The FUNDING_ID can be the external source id, the internal Pure id or an index of the funding on the award. In the cases where funding does not have relevance for your data, the FUNDING_ID should be stated as 0, indicating index 0. In this case there should only be one funding per award.
BUDGET_ID	String	1024	Yes	An ID that identifies this specific budget. This ID is used to relate budget lines and expenditure. The BUDGET_ID needs to be unique and non-changing per combination of AWARD_ID and FUNDING_ID. E.g. AWARD_ID = 1000, FUNDING_ID = 1, and BUDGET_ID = 1 - and AWARD_ID = 1000, FUNDING_ID = 2, and BUDGET_ID = 1. If you don't have a BUDGET_ID please assign one and if you don't have multiple budgets per funding then please just set it to 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.

SQL used to check the quality of the views	Description/Comment	Applicable if
select * from BUDGET where AWARD_id is null or FUNDING_ID is null or BUDGET_ID is null	Check for missing mandatory fields
select b1.AWARD_id, b1.FUNDING_ID, b1.BUDGET_ID from BUDGET b1 inner join BUDGET b2 on b1.AWARD_id = b2.AWARD_id where b1.FUNDING_ID = b2.FUNDING_ID and b1.BUDGET_ID = b2.BUDGET_ID group by b1.AWARD_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 award as a financial funding or non financial fundings
select b1.AWARD_id, b1.FUNDING_ID, b1.BUDGET_ID, b1.ORGANISATION_ID, b1.COST_CENTER from BUDGET b1 inner join BUDGET b2 on b1.AWARD_id = b2.AWARD_id where b1.FUNDING_ID = b2.FUNDING_ID and (b1.ORGANISATION_ID is null or b1.COST_CENTER is null) group by b1.AWARD_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 * from BUDGET b where not exists (select * from BUDGET_LINE bl where bl.AWARD_ID = b.AWARD_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

BUDGET_LINE (Mandatory)

This view provides the budget lines and values for the financial overview below:

Column name	Data type	Size	Mandatory	Note
AWARD_ID	String	1024	Yes	The award to add the budget line to
FUNDING_ID	String	1024	Yes	The FUNDING_ID references the Funding ID (together with the AWARD_ID) provided in the AWARD_FINANCIAL_FUNDINGS view.
BUDGET_ID	String	1024	Yes	The BUDGET_ID references the Budget ID (together with the AWARD_ID and FUNDING_ID) provided in the 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 award. 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 BUDGET_LINE where AWARD_id is null or FUNDING_ID is null or BUDGET_ID is null or BUDGET_LINE is null or BUDGET_VALUE is null	Check for missing mandatory fields
select * from BUDGET_LINE bl where not exists (select * from BUDGET b where bl.AWARD_ID = b.AWARD_ID and bl.FUNDING_ID = b.FUNDING_ID and bl.BUDGET_ID = b.BUDGET_ID)	Check that the corresponding budget could be found
select * from BUDGET_LINE bl where not exists (select * from EXPENDITURE e where bl.AWARD_ID = e.AWARD_ID and bl.FUNDING_ID = e.FUNDING_ID and bl.BUDGET_ID = e.BUDGET_ID and bl.BUDGET_LINE = e.BUDGET_LINE)	Check that there exists at least one expenditure related to this budget line

BUDGET_LINE_YEARLY_BUDGET (Optional)

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
AWARD_ID	String	1024	Yes	The award to add the budget line yearly budget to
FUNDING_ID	String	1024	Yes	The FUNDING_ID references the Funding ID (together with the AWARD_ID) provided in the AWARD_FINANCIAL_FUNDINGS view.
BUDGET_ID	String	1024	Yes	The BUDGET_ID references the Budget ID (together with the AWARD_ID and FUNDING_ID) provided in the BUDGET view.
BUDGET_LINE	Classification value (view/edit classification configuration)	1024	Yes	The BUDGET_LINE references the BUDGET_LINE in the BUDGET_LINE view (together with the AWARD_ID, FUNDING_ID and BUDGET_ID) provided in the 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 award 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

EXPENDITURE (Mandatory)

This view provide data to the expenditure overview below.

Column name	Data type	Size	Mandatory	Note
AWARD_ID	String	1024	Yes	The award to add the expenditure to
FUNDING_ID	String	1024	Yes	The FUNDING_ID references the the Funding ID (together with the AWARD_ID) provided in the AWARD_FINANCIAL_FUNDINGS view.
BUDGET_ID	String	1024	Yes	The BUDGET_ID references the Budget ID (together with the AWARD_ID and FUNDING_ID) provided in the 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 so make sure this is setup correctly.
YEAR	Integer		Yes	The year for the expenditure value. Eg. 2012
MONTH	Integer		Yes	The month for the expenditure value. Eg. 10
EXPENDITURE_VALUE	Double		Yes	The actual expenditure value to add to the award. If you use multiple budget lines, you have to provide a expenditure value for each of them. The total expenditure will be calculated as the sum of all of these. You do not need to provide a expenditure value for each of the budget lines you have specified.

SQL used to check the quality of the views	Description/Comment	Applicable if
select * from EXPENDITURE where AWARD_id is null or FUNDING_ID is null or BUDGET_ID is null or BUDGET_LINE is null or YEAR is null or MONTH is null or EXPENDITURE_VALUE is null	Check for missing mandatory fields
select * from EXPENDITURE e where not exists (select * from BUDGET_LINE bl where bl.AWARD_ID = e.AWARD_ID and bl.FUNDING_ID = e.FUNDING_ID and bl.BUDGET_ID = e.BUDGET_ID and bl.BUDGET_LINE = e.BUDGET_LINE)	Check for reference to non existing budget line

AWARD_IDS (Optional) - Available from Pure 5.5.x

An award 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 award you have to supply an AWARD_ID which will be this awards primary ID. Besides this you have the posibility to add any other ID's you would like to have stored against the award.

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

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

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

Column name	Data type	Size	Mandatory	Note
AWARD_ID	String	1024	Yes	The award 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 AWARD_IDS where AWARD_ID is null or ID_SOURCE is null or ID is null	Check for missing mandatory fields
select distinct AWARD_ID from AWARD_IDS ai where not exists (select * from AWARD_DATA a where ai.AWARD_ID = a.AWARD_ID)	Check for reference to non existing awards

AWARD_LINKS (Optional) - Available from Pure 5.5.x

This view contains the information about award links.

Column name	Data type	Size	Mandatory	Note

AWARD_ID	String	1024	Yes	The ID of the award the link will be added to. The AWARD_ID must exist in the AWARD_DATA view above.
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 November 21, 2023