Develop Write Back Support
IFS Business Reporting & Analysis services supports the write back of information from e.g. a client as IFS Business Reporter to IFS Cloud.
In order to satisfy the write back functionality two requirements must be fulfilled:
- Write Back specific Meta Data should be defined for an Information Sources that supports write back functionality.
- A PL/SQL API, i.e. a method in a PL package, must be available. The method has the responsibility to take care of the collected write back data.
Two types of Write Back are supported:
- General Write Back Means that IFS Business Reporting & Analysis services handles most part of the processing and will collect transactions and pack them one-by-one into a standard attribute string and send it to a registered API. The API has the responsibility to take care of the attribute string and perform what is necessary, like e.g. creating a new record or updating an existing one. In IFS Business Reporter either all or a sub set of values can be written down. The General Write Back supports both these types, since this write back in both cases means sending one or more attribute strings without any possibility to perform pre- or post-processing.
- Advanced Write Back The processing of the collected write back information is entirely up to the registered API. The API must collect the data to be processed, handle errors, status response to IFS Business Reporter etc. An example of this is, the implementation made in the module BUDPRO (Budget Process). The code in this module can be used as source code reference. If Advanced Write Back supports modified write back or not is up to the implementation in each Information Source.
Note: When performing write back in the IFS Business Reporter client, it is totally possible to retrieve data from one Information Source and then write modifications of that data back to IFS Cloud using another write back Information Source. This leads to that the retrieved information in IFS Business Reporter does more or less never contain information about OBJID and OBJVERSION as in ordinary IFS Cloud clients.
The General Write Back is probably sufficient to handle most part of write back scenarios. The IFS Business Reporter write back framework will never write anything directly to a table, it will rely completely on registered APIs that have the full responsibility to take care of the data, for e.g. reading, validating and processing.
The Write Back is mainly handled according to the following:
- The client collects write back information from all available and write back enabled Information Sources.
Upon request the collected data is sent to IFS Cloud. This is done in two steps:
- A structure named XlrWbCollection is created in the client and sent to IFS Cloud. When this is completed, the write back data is posted in the database.
- In the next step the posted data is processed. This is handled by calling a PL/SQL API that has been registered in IFS Business Reporting & Analysis services via the Information Source specific Meta Data. During the processing, status information is added to current write back collection:
In the General Write Back case this is handled by the IFS Business Reporting & Analysis write back framework.
In the Advanced Write Back case this is handled entirely by the registered PL/SQL API.
When all write back data has been processed a structure called XlrWbResponse is built and sent back to IFS Business Reporter that scans the structure and presents the result to the end user.
The entity model for write back looks as follows:
Figure 1: Entity Model
When IFS Business Reporter collects write back data, a server model called XlrWbCollection, based on the entity model, is created and sent back to IFS Cloud.
The processing of the posted write back data must also make sure to set the status on Collection, Fact, Set and Row level.
When the processing of the posted write back data is complete, the response is collected in a server model, based on the entity model, named a XlrWbResponse and sent back to IFS Business Reporter that can present the result of the processing.
IFS Business Reporter specifics:
- The Excel workbook itself, as it looked when the Write Back was ordered, is sent to IFS Applications as a part of the write back structure. The workbook can be large and may require lot of memory so the processing performance might be affected. In Meta Data the information entity named XlrFactWriteBackInfo has an attribute named WriteBackSupportStoreFile and if it is set to TRUE for one of the involved write back facts then the Excel workbook will be saved as a binary object as a part of the posted write back collection. By setting the attribute to FALSE, the saving of the Excel workbook can be avoided but on the other hand, saving the Excel file in the database might be an important part of the current process and might be necessary for follow-up purposes.
- The client will gather all write back related information in all sheets in the work book associated with one Information Source will be grouped together into sets. Thus each affected Information Source has one or more write back data sets.
Each data set has one or more rows and in each row the row values are represented as attribute strings.
- The write back entity model supports status setting on fact, set and row level. Currently the posting always means that the collection, all facts, sets and rows get the status
Postedafter the posting step.
During server processing errors can be discovered on different level.
- Response/Collection level Supplies a total status for the processing of the current write back collection.
- Fact level Supplies a total status for each fact, Information Source, and a message.
- Set level Supplies a total status for each set and a message.
- Row level Supplies a total status for each row and a message.
If e.g. an error occurs on the set level for one of the sets associated with a fact, then the specific set will get ProcessedWithError status while the other data sets get ProcessedOk status. The fact and the response/collection level will in this case get ProcessedWithError status along with a message that says that, something is wrong on lower levels.
To make the status handling work, it is necessary to handle it correctly in the API that takes care of the write back. Especially applies for the Advanced Write Back case.
- Posting of write back information always means that a so called XlrWbCollection is created. Each collection has a unique identity.
- Each collection has one or more XlrWbFact aggregates, where each of these keeps track of all information associated with one specific Information Source (fact). Each XlrWbFact in a collection will have its own unique identity.
- For each XlrWbFact there are one or more data sets where each set has one or more rows.
For Advanced Write Back the structure is processed by the registered PL/SQL API basically in the following way:
- The IFS Business Reporting & Analysis writeback engine will provide the API with the identity of the collection and the master, where the master identity is the key in XlrWbFact.
- The API now has to loop over all levels in the write back structure.
- Each retrieved row is used to build input to a method in a LU that takes care of validations and data base access.
- Each row processing has to be reported back to the write back engine.
- If an error is found on a level and is reported back, then the write back engine will automatically propagate the error status to all levels above the error level. Only the error text on the current level has to be provided by the processing API.
- When all Master information has been processed a total processing status is returned back the caller (engine).
Errors found during processing can be handled either by stopping the processing immediately and reporting a general error on the highest level. In this case all data base changes are rolled back completely. Another option is to process all rows and keep track of the status for each processed row and reporting the status/result to the IFS Business Reporter framework. This case gets a bit more complicated since partial
ROLLBACKhas to be considered where all changes from last SAVEPOINT has to be rolled back.
For General Write Back the following applies:
- The writeback engine in IFS Business Reporting & Analysis will loop through the levels and collect all information and build an attribute string that is sent to the registered PL/SQL API for each Information Source.
- The API takes care of validations and LU-specific actions.
- If the write back attribute StopWbProcessingOnError (valid only for General Write Back ) is set to
TRUEthen an error will abort the execution and everything will be rolled back. If the attribute is set to
FALSEthen each error will be reported back to write back structure, making it possible to see the status for all handled rows at the same time.
General about the attribute strings with row information for each write back data set
- The client collects all write back values and finds out which metadata attribute that each value belongs to. Each write back attribute an corresponding write back indentiy. This identity will be used as the attribute name of the attribute value in the string and it should be a recocnizable attribute in the target write back package (entity)
- Apart from standard attributes the attribute string will for most cases also contain the attribute WB_ROW_CRUD_OPERATION that can have the values WB_ROW_CRUD_OPERATION or UPDATE. The CRUD operation thus defines if the row is new or a updated. It is important to make sure that the written business logic can take care of this non-entity attribute. The WB_ROW_CRUD_OPERATION attribute will be added to the attribute string only when using General Writeback. For Advanced Writeback the CRUD operation is not available but instead each row contains attribute strings both for representing the new values and the old values.
About Modified Write Back
IFS Business Reporter supports write back by either performing a complete write back or a so called modified write back.
- Complete write back means that all write back associated values are sent back to server. Thus it does not matter if the end-user has only modified a few values.
Modified write back means that only the modified values (rows) are collected and sent the server. General Write Back always supports modified write back. For Advanced Write Back is up to the one writing the PL interface to decide if it possible to process only sub sets of the available data in the BR client. In the Modified Write Back case the following information will be written back:
- All values related to current row status.
- All values related to the old row status, i.e. before update.
- Information about unique items for each data set.This is typically the items that the each set is repeated/grouped on, and it is necessary to know about these items to be able to handle the modified write back case as correctly as possible.
The first criteria to be fulfilled is that the Information Source supports modified write back. This is controlled by the attribute SupportModifiedWriteback in the enity
XlrFactWriteBackInfo in the Meta Data. When this attribute has been defined as
TRUE, it will be possible in IFS Business Reporter to handle modified write back.
The information about unique items in entity XlrWbItem is necessary to be able to handle the write back properly. The main problem is that the transactions represented in an Information Source always have a fixed lowest level while in a general client like IFS Business Reporter, it is possible to group these transactions in different ways, leading to a small problem; the write back information can be represented on a higher, aggregated, level than in the table that is the target. There are two ways of approaching this:
- The server code makes sure that the modified write back supplies enough values for each row, so that the write back is consistent with the target model in the target LU. If not this is the case, issue an error.
- Try to handle inconsistencies when it comes to how the written back information is grouped compared to the grouping level of the target model:
- For each set, find out which the unique items are (according to the sender)
- For each processed row
- Get the unique item values from the old write back row
- Delete information in the target table according to these values
- Insert the new values according to the new row
More implementation details can be found on the Write Back Implementation Details page.
One implementation example can be found on the Write Back Implementation Example page.