Write Back Implementation Details

This page provides detailed implementation details related to writeback via IFS Business Reporting & Analysis servics. This type of write back is mainly supported by the IFS Business Reporter client.

Write Back Meta Data

To enable writeback support in IFS Business Reporting & Analysis services it is necessary to supply some Meta Data. The following has to be defined:

• General Meta Data
• Fact Item Meta Data
• Dimension Item Meta Data
• Write Back Item Types

When it comes to write back specific items, they together define a write back transaction row and generally a fact item can be manually modified, supplied via e.g. a client like IFS Business Reporter, while a dimension item normally represents a basic data value that cannot be manually defined. Basic data items are in IFS Business Reporter defined either by reading the actual Information Source and using then as repeater items or by defining fixed or parameter driven values in the design.

Also find information in the Business Rerporting & Analysis Services Meta Data Model section.

General Meta Data

General Meta Data for write back means defining information like in the following example,

The following is defined:

• rec_.Write_Back_Supported:= 'TRUE'; The Information Source supports write back.
• rec_.Write_Back_Category:= Xlr_Meta_Util_API.WB_ADVANCED_TYPE_; The write back category is defined as advanced write back.
• rec_.Write_Back_Support_Store_File:= 'TRUE' The PL interface handles storage of the Excel file used when performing the write back (via IFS Business Reporter).
• rec_.Write_Back_Interface:= 'BUDPRO_SMART_CLIENT_UTIL_API.PROCESS_XLR_WRITE_BACK' The write back interface that handles the business logic part of the write back.
• rec_.Support_Modified_Writeback:= 'TRUE' The Information Source also supports modified write back.

This information is defined in the Meta Data file for the fact.

Fact Item Meta Data

All fact and dimension items that together defines a write back transaction row has to be specified.

Each fact item in the Information Source is typed with respect to write back and we must also define the name of the item in the write back interface. Both measure and light items can be used.

The example shows that the itemCURRENCY_AMOUNT in the fact &FACT (FACT_BUDGET_TEMPLATE_ROW), is typed as an optional write back item. This means that it can be written back with a new/modified value but it does not have to present in the transaction row. The name of the item in the write back interface is "CURR_AMOUNT"

This information is defined in the Meta Data file for the fact.

Dimension Item Meta Data

All fact and dimension items that together defines a write back transaction row has to be specified.

Each dimension item in the Information Source is typed with respect to write back and we must also define the name of the item in the write back interface. Dimension items cannot be modified in IFS Business Reporter such that the modified value is written back. Dimension items can get their values from parameters, criteria and from repeaters but once assigned by IFS Business Reporter it is not possible, as for light and measure items, to modify them. Even if an Excel cell related to a dimension item is modified, the value written back will still be the value initially assigned by the client.

The example shows that the item DIM_COMPANY.CODE in dimension DIM_COMPANY, connected to fact &FACT (FACT_BUDGET_TEMPLATE_ROW), is typed as a mandatory write back item. This item has to be available, in each transaction row,  to be able to perform the write back.  The name of the item in the write back interface is COMPANY

This information is defined in the Meta Data file for the fact.

Write Back Item Types

The following write back types are available for fact and dimension items:

• Xlr_Meta_Util_API.NO_WB_TYPE_ Defined for items that are not associated with write back.
• Xlr_Meta_Util_API.MANDATORY_WB_TYPE_ Defined for items that must be available in a write back transaction row.
• Xlr_Meta_Util_API.OPTIONAL_WB_TYPE_ Defined for items that can be available in a write back transaction row but does not have to be part it.
• Xlr_Meta_Util_API.KEY_WB_TYPE_ Defined for items that represent keys. Typically to be used for light items. If a light item is defined as a key write back item, it will automatically be a mandatory item and it will not be possible to define it as a write back item in IFS Business Reporter. So the functionality is more or less the same as a mandatory dimension item.

Write Back Interface

For Advanced Write Back the PL/SQL interface must look exactly as follows:

PROCEDURE Process_Write_Back(  
    process_status_code_  OUT  PLS_INTEGER,  
    message_              OUT  VARCHAR2,  
    collection_id_        IN   NUMBER,  
    master_id_            IN   NUMBER );

The name is irrelevant but the declaration (constructor) must look as above. The Write Back engine will call the interface via a dynamic PL block and it will provide the method with the collection and the master identities. Now it is up to the interface (e.g. Process_Write_Back) to read the data and to process it by using this identity. When the processing ends, it is expected that the interface returns a process status code and a message.

For General Write Back the interface must look exactly as follows:

PROCEDURE Process_Write_Back (  
    attr_     IN   VARCHAR2 );

The name is irrelevant but the declaration (constructor) must look as above. This method can only retrieve an attribute string and then process it. There is no way of pre- and/or post-processing and the functionality will be the same regardless if IFS Business Reporter has sent down all or only a few rows.

Public Views

To make it possible to read the write back information there are some public views available, supposed to be used in the Advanced Write Back scenario.

• Xlr_Wb_Set_Pub
• Xlr_Wb_Row_Pub
• Xlr_Wb_Security_Pub

View Xlr_Wb_Set_Pub

This view presents write back information on set level, i.e. all write back sets related to one write back master (Information Source) in one collection are made available via this view.

The following columns are available:

Column Name	Data Type	Description
COLLECTION_ID	NUMBER	Unique identity for the current write back collection. This identity is provided by the write back engine to the processing API.
MASTER_ID	NUMBER	Unique write back master identity associated with an Information Source during a write back transaction. This identity is provided by the write back engine to the processing API.
SET_ID	INTEGER	Write back set identity
SET_STATUS	VARCHAR2	Status of the write back set. Client value.
STATUS_MESSAGE	VARCHAR2	A message related to the set status.

View Xlr_Wb_Row_Pub

This view presents write back information on row level, i.e. all write back rows related to one set for a specific master (Information Source) in one specific write back collection are available via this view.

Column Name	Data Type	Description
COLLECTION_ID	NUMBER	Unique identity for the current write back collection. This identity is provided by the write back engine to the processing API.
MASTER_ID	NUMBER	Unique write back master identity associated with an Information Source during a write back transaction. This identity is provided by the write back engine to the processing API.
SET_ID	INTEGER	Write back set identity
ROW_NO	INTEGER	Row number within write back set.
ROW_STATUS	VARCHAR2	Status of the write back set. Client value.
STATUS_MESSAGE	VARCHAR2	A message related to the row status.
VALUE_ATTR_ONE	VARCHAR2	Attribute string containing current row specific values to written back. The attribute string is built as any other LU based attribute string.
VALUE_ATTR_TWO	VARCHAR2	Attribute string containing current row specific values to be written back. The row values are split into two separate attribute strings if the length of the first string exceeds 4000 characters. The attribute string is built as any other LU based attribute string and it can be concatenated together with VALUE_ATTR_ONE to get the complete row attribute string.
OLD_VALUE_ATTR_ONE	VARCHAR2	Attribute string containing the old row specific values before changed in IFS Business Reporter. The attribute string is built as any other LU based attribute string. This string is typically used when processing modified write back in the advanced case.
OLD_VALUE_ATTR_TWO	VARCHAR2	Attribute string containing the old row specific values before changed in IFS Business Reporter. The old row values are split into two separate attribute strings if the length of the first string exceeds 4000 characters. The attribute string is built as any other LU based attribute string and it can be concatenated together with OLD_VALUE_ATTR_ONE to get the complete old row attribute string. This string is typically used when processing modified write back in the advanced case.

View Xlr_Wb_Security_Pub

This view shows write back master identities associated with facts (Information Sources) that the current user is granted to access.

Column Name	Data Type	Description
COLLECTION_ID	NUMBER	Unique identity for the current write back collection. This identity is provided by the write back engine to the processing API.
MASTER_ID	NUMBER	Unique write back master identity associated with an Information Source during a write back transaction. This identity is provided by the write back engine to the processing API.
FACT_ID	VARCHAR2	Identity of the fact, Information Source.

Status Codes

There is only one set of status codes used on all levels in the write back entity model. The enumeration represented by the PL package Xlr_Write_Back_Status_API contains valid status values.

Status Code Db Value	Meaning
Posted	Means that the write back values have been successfully written back to the general write back storage.
Processed	Processing of posted data was successful o the current write back level.
ProcessedWithError	Processing of posted data has lead to and error on current write back level.

Status code usage guide:

• A write back collection should have db_status_code='Posted' to be valid for processing.
• A write back fact should have db_status_code='Posted' to be to be valid for processing of data related to one Information Source.
• A write back set should have db_status_code='Posted'  to be valid for processing.
• A write back row should have db_status_code='Posted'  to be valid for processing.
• During processing each handled row should be given a status code that defines the processing status, either db_status_code='Processed' or db_status_code='ProcessedWithError'. If an error occurs on a higher level then the current row status should be untouched. If an error occurs then an error message can be supplied. A message does not have to be supplied if the processing is Ok.
• During processing each handled set should be given a status code that defines the processing status, either db_status_code='Processed' or db_status_code='ProcessedWithError'. If an error occurs on a higher level then the current set status should be untouched. If an error occurs then an error message can be supplied and if the processing is Ok it is recommended to add a message as well since it adds value to the user looking in the client at the result.
• The overall status for the processing of all write back data related to the Information Source (fact) is the one returned by the write back interface. The value is either db_status_code='Processed' or db_status_code='ProcessedWithError'. A status message can be supplied as well. Try to supply a message in any case.

Handling Row Response

During processing of a write back row a status should be defined.

For General Write Back this handled by the write back engine.

For Advanced Write Back it is up to the processing interface to supply a processing response to the write back engine. This is done by calling the method Row_Response in the package Xlr_Wb_Util_API.

-- Package: Xlr_Wb_Util_API  
-- Record definition  
TYPE Write_Back_Row_Info_Rec IS RECORD  
(  
    collection_id          PLS_INTEGER,  
    master_id              PLS_INTEGER,  
    set_id                 PLS_INTEGER,  
    row_no                 PLS_INTEGER,  
    row_status             VARCHAR2(100),  
    row_status_message     VARCHAR2(2000)     
);  
-- Procedure definition  
PROCEDURE Row_Response(  
   row_info_   IN  Xlr_Wb_Util_API.Write_Back_Row_Info_Rec);  
    

Below follows one example how to call this method:

PROCEDURE Give_Row_Response___(  
   wb_collection_id_     IN NUMBER,  
   wb_master_id_         IN NUMBER,  
   set_id_               IN PLS_INTEGER,  
   row_no_               IN PLS_INTEGER,  
   status_code_db_       IN PLS_INTEGER,  
   message_              IN VARCHAR2)  
IS  
  row_info_   Xlr_Wb_Util_API.Write_Back_Row_Info_Rec;  
BEGIN  
   -- Give response for current row to Write Back API  
   row_info_.collection_id       := wb_collection_id_;    
   row_info_.master_id           := wb_master_id_;  
   row_info_.set_id              := set_id_;  
   row_info_.row_no              := row_no_;   
   row_info_.row_status_         := Xlr_Write_Back_Status_API.Decode(status_code_db_);   
   row_info_.row_status_message  := message_;   
   Xlr_Wb_Util_API.Row_Response(row_info_);   
END Give_Row_Response___;  

Note: If an error is reported, then the error will be propagated up to highest level and on all levels the error code will be set. A standard error message will be provided on the above levels.

Handling Set Response

During processing of a write back set a set status should be defined.

For General Write Back this handled by the write back engine.

For Advanced Write Back it is up to the processing interface to supply a processing response to the write back engine. This can be done by calling the method Set_Response in the package Xlr_Wb_Util_API.

-- Package: Xlr_Wb_Util_API  
-- Record definition  
TYPE Write_Back_Set_Info_Rec IS RECORD  
(  
    collection_id       PLS_INTEGER,  
    master_id           PLS_INTEGER,  
    set_id              PLS_INTEGER,  
    set_status          VARCHAR2(100),  
    set_status_message  VARCHAR2(2000)     
);  
-- Procedure definition  
PROCEDURE Set_Response(  
    set_info_   IN  Xlr_Wb_Util_API.Write_Back_Set_Info_Rec);  
    

Below follows one example how to call this method:

PROCEDURE Give_Set_Response___(  
   wb_collection_id_     IN NUMBER,  
   wb_master_id_         IN NUMBER,  
   set_id_               IN PLS_INTEGER,  
   status_code_db_       IN PLS_INTEGER,  
   message_              IN VARCHAR2)  
IS  
   set_info_ Xlr_Wb_Util_API.Write_Back_Set_Info_Rec;  
BEGIN  
   set_info_.collection_id       := wb_collection_id_;  
   set_info_.master_id           := wb_master_id_;   
   set_info_.set_id              := set_id_;   
   set_info_.set_status          := Xlr_Write_Back_Status_API.Decode(status_code_db_);  
   set_info_.set_status_message  := message_;   
   Xlr_Write_Back_Util_API.Set_Response(set_info_);  
END Give_Set_Response___;

Note: If an error is reported, then the error will be propagated up to the highest level and on all levels the error code will be set. A standard error message will be provided on the above levels.

How To Get The Excel Workbook

If it is required by the business logic to handle the IFS Business Reporter workbook, previously saved along with the write back data, then the method Get_File_Object in package Xlr_Wb_Util_API can be used. It returns a BLOB, i.e. the workbook as a binary object.

-- Package: Xlr_Wb_Util_API  
FUNCTION Get_File_Object(  
   collection_id_    IN NUMBER ) RETURN BLOB;  

A code example is supplied here:

FUNCTION Get_Wb_File___(  
   wb_collection_id_ IN NUMBER ) RETURN BLOB  
IS  
   file_object_ BLOB;  
BEGIN  
   file_object_ := Xlr_Wb_Util_API.Get_File_Object(wb_collection_id_);  
   RETURN(file_object_);  
END Get_File___;

Implementation Example

More implementation details can be in the following Write Back Implementation Example