Sunshine Act (1.4.7.402)

Learn about the Sunshine Act and its reporting in business solution template.

Highlights of EFPIA Sunshine Act:

  • As of 2015, the European Federation of Pharmaceutical Industries and Associations requires all transfers of value to healthcare professionals and healthcare organizations to be documented and disclosed

  • The data in the report (e.g. sponsorship agreements, registration fees) are taken from a variety of areas (e.g. call expenses, event participants, information on clinical trials).

  • Back office staff can edit the data in the corresponding areas and then generate the data included in the report anew.



Note: A complete test dataset for Sunshine Act is available on request.

Datamodel









Technical flowchart



Button “TE_U066_CurrentYear” and “TE_U066_PreviousYear” calls a JavaScript function with callback. JS Function: u8.TeLSI.SunshineAct.execute($)

Furthermore there are two parameters which are given to the function.



Within the JS function two TD records are created.

u8Server and TD records

JS file lsisunshineact.js with function u8.TeLSI.SunshineAct.execute.

Via CRUD service two TD records are created. Via the parameters in “Text” field the u8server knows what to do. The second TD record depends on the status of the first TD record. Only if the first one is set to completed by the u8server, the second TD record is processed by the u8server.

First TD record is responsible for handling the WIN report and the export to the output file. The second TD record is responsible for handling the import of records which was created by the first TD.

First TD record:



Second TD record:



Both, the export and the import, use following path for the output / input file:

  • “web/web.data”
  • File name: sunshineact.txt

WIN Report and Transfer fields

Sunshine Act data is generated based on a WIN report. The content structure of the output file is defined by the selected XSLT. There are two reports, one for the current year and one for the previous year.

  • TE_LSI_SunshineActReport_CurrentYear
  • TE_LSI_SunshineActReport_PreviousYear


The file paths based on the root directory of the u8server. ( web\bin ) Form there file paths in the WIN report can be specified relative.

Note: With these file paths you cannot start the report via Aurea CRM WIN module.

Transfer field definitions

In transfer field definition all infoareas and filters are defined. With different dependencies fields ( and there values ) is selected from certain infoareas.

There are two definitions, one for the current year and one for the previous year.
  • TE_LSI_SunshineReport_TransferFields_CurrentYear
  • TE_LSI_SunshineReport_TransferFields_PreviousYear


Important: IMPORTANT: If you want to use a field later in the sunshine act report, you have to use prefix texts. For example there is the prefix “sunU011FI_TRAC” to add the value of these fields in the “Travel and Accommodation” pot.

Prefix Overview:

_TRAC > Travel & Accommodation ( U066, FId: 5003)

_DOGR > Donation & Grants ( U066, FId: 5004)

_REFE > Registration Fees ( U066, FId: 5005 )

_COFE > Consulting Fees ( U066, FId: 5006 )

_COEX > Consulting related expenses ( U066, FId: 5007 )

_REDE > Research & Development ( U066, FId: 5008 )

_SOAG > Sponsorship agreements ( U066, FId: 5002)

Sunshine Act “Pots”

Pot prefix texts is used in further XSLT to sum up values from the same „type“.



XSLT transformation

The report exports data to a file based on the structure given by the XSLT. File-Path: web\data\reports\te.lsi\xslt

There are also a file for current year and previous year.

  • SunshineAct_CurrentYear.xslt
  • SunshineAct_PreviousYear.xslt


With xslt sum function and contains – filter all fields with the same prefix text is summed up. $seperator is the separator for CSV structure = Pipe ( | )

Report – Output TXT File

Records have the same ID



The output file includes following sunshine act data fields:

ID, Title, Starts on, Ends on, Year, different pots,FI-StaNo, FI-SerNo, PE-StaNo, PE-SerNo

WIN Import

Field U066-Num is the unique index (id) to achieve that only ONE U066 record is created. Depending on the lines in the import file dependent U067 records is created and linked to exactly this U066 record.



The import is started by the CRMserver based on the second TD record which was created before.

Sunshine Act Data



Create report

Technical flowchart



Button

  • TE_U066_CreateReport

    Calls the JS function u8.TeLSI.Reports.doSunshineActReport($)

    Uses the queries TE_U066_SunshineAct and TE_ID_GetCurrentRepInfo

    Uses the XSLT file TE_U066_SunshineAct.xslt

  • TE_U066_SetToComplete

    Calls the trigger TE_U066_CompleteSunshineActReport and set the field “Publication” of the selected record to “true”. After this “Status” change, the record cannot be deleted anymore.

Queries

  • TE_U066_SunshineAct

  • TE_ID_GetCurrentRepInfo