Moving on to part 3 in this series looking at Journals and Data Management. In the first part I provided an overview of Journals and went through in detail the workflow process. In the previous post I covered loading Journals through the simplified interface. In this post I am moving on to the options available for loading journals to FCCS through Data Management, the same can be achieved with on-premise FDMEE using hybrid functionality.
Data Management supports two methods for loading journals, this can be either through a standard text file or using an Excel template.
I am going to start with the Excel template and I am hoping the background provided in the previous posts will make it easier to understand. If you have ever loaded journals from on-premise FDMEE to HFM the concept is very similar.
The advantage of loading journals through the Excel template is that the template already exists and can be downloaded, there is no need to build a new template and generate the output in exactly the correct format that is required when importing through the simplified interface. There is also the benefit of being able to apply mappings as the source journal members may not be exactly matched to the ones in FCCS.
It is possible to use an existing location to import a journal Excel template, but I am going to begin with a new import format.
There is no need to fill out the mappings in the import format as they will be handled through the template.
I created the import format using a numeric data type, so the amount column will be available which is also in the Excel template.
A new location can then be created, which is assigned to the import format.
You will need a data load rule for the new location. There is no requirement to fill in the file name as it can be provided when importing the journal.
The important part is that the “Load Type” in the rules target options is set to “Journal”. Once this has been set, the workbench will have an option to load the journal.
There is an option to specify the journal status, this is where I disagree with the documentation.
The documentation states there are four options available for the journal status: Working, Submitted, Approved, Rejected
In the previous post I covered the options available and the rules are:
“If the Journal Workflow is enabled, you can only import Working journals. If the Journal Workflow is disabled, you can import Working and Posted journals.”
I will show that the rules still apply in Data Management when I get on to importing a journal.
To be able to import journals we will need the Excel template, this can be downloaded from the workbench by selecting “Download Template” and then “Journal”
The download will be named “Journal.xls” and is in the old Excel 97-2003 “xls” format, there is no problem opening the Excel template and saving it is a newer “xlsx” format.
The Excel template will be populated with a matching location, category and period to the one that was selected when downloaded from the workbench.
When importing the Journal Excel template, the location, category and period must match to the one selected in Data Management. I will demonstrate shortly what happens if this is not done.
The template contains all the necessary dimensions and by default includes two custom dimensions.
The template can be changed and not all columns need to be populated, though there are some additional rules that need to be followed.
The template contains hidden rows between 16 and 20. These are read and validated when the journal is imported.
In column I and rows 16-19 there is a formula that just points to the location, category, period and load method in column A.
You might think that row 15 is the one defining the dimensions, this is not the case and it is row 20 where that is done.
The following table describes the values that can be used for defining dimensions.
If you are unclear on what the custom dimension values should be then you can have a look at the target application and the data table column names.
For example, if you take the “Product” dimension, the column is “UD5” which maps to “5” in the Excel template, it would also be valid to assign it to “UD5” in the Excel template.
The template can be changed to fit your requirements or a new one created, but the above logic should be applied. The main driver to where metadata definitions and data exists in the template is controlled by a named range called “upsJournal”.
If you are creating your own template, then as long as the named range starts with “ups” you can name it what you like.
The named range should cover the metadata definition and the data rows.
By default, the Excel template is defined to show dollars in the amount column, you may be shocked to realise but not everybody operates in dollar amounts :)
The formatting of the amount column can be updated in the template and it should not affect the import.
So, let’s update the template with a simple journal example based on what I have used in previous posts.
Members for entity, intercompany and movement do not match the target application, these will be updated by using mappings in Data Management.
Multi-GAAP has been left empty as this will also be handled by mappings.
I updated “Custom2” to “Product”, remember it is the hidden row value in the named range that defines the dimension. I have hidden the “Custom1” column or alternatively it could be deleted as it is only optional.
It doesn’t look like you can assign a description for the journal in the template unless there is an undocumented feature. It is possible to provide description for the individual journal lines though.
There is an important note that needs to be understood when loading journal data, this is taken from the documentation:
“When loading journals to an Oracle Financial Consolidation and Close Cloud target, consider that Data Management does not determine the account types or select the credits/debits. All positive numbers are loaded as credits and all negative numbers are loaded as debits. If you need to designate other credit or debit signs for your account type, use the change sign feature in Data Load Mappings or another customized method to handle credits/debits changes for your journal loads.”
Data Management supports two methods for loading journals, this can be either through a standard text file or using an Excel template.
I am going to start with the Excel template and I am hoping the background provided in the previous posts will make it easier to understand. If you have ever loaded journals from on-premise FDMEE to HFM the concept is very similar.
The advantage of loading journals through the Excel template is that the template already exists and can be downloaded, there is no need to build a new template and generate the output in exactly the correct format that is required when importing through the simplified interface. There is also the benefit of being able to apply mappings as the source journal members may not be exactly matched to the ones in FCCS.
It is possible to use an existing location to import a journal Excel template, but I am going to begin with a new import format.
There is no need to fill out the mappings in the import format as they will be handled through the template.
I created the import format using a numeric data type, so the amount column will be available which is also in the Excel template.
A new location can then be created, which is assigned to the import format.
You will need a data load rule for the new location. There is no requirement to fill in the file name as it can be provided when importing the journal.
The important part is that the “Load Type” in the rules target options is set to “Journal”. Once this has been set, the workbench will have an option to load the journal.
There is an option to specify the journal status, this is where I disagree with the documentation.
The documentation states there are four options available for the journal status: Working, Submitted, Approved, Rejected
In the previous post I covered the options available and the rules are:
“If the Journal Workflow is enabled, you can only import Working journals. If the Journal Workflow is disabled, you can import Working and Posted journals.”
I will show that the rules still apply in Data Management when I get on to importing a journal.
To be able to import journals we will need the Excel template, this can be downloaded from the workbench by selecting “Download Template” and then “Journal”
The download will be named “Journal.xls” and is in the old Excel 97-2003 “xls” format, there is no problem opening the Excel template and saving it is a newer “xlsx” format.
The Excel template will be populated with a matching location, category and period to the one that was selected when downloaded from the workbench.
When importing the Journal Excel template, the location, category and period must match to the one selected in Data Management. I will demonstrate shortly what happens if this is not done.
The template contains all the necessary dimensions and by default includes two custom dimensions.
The template can be changed and not all columns need to be populated, though there are some additional rules that need to be followed.
The template contains hidden rows between 16 and 20. These are read and validated when the journal is imported.
In column I and rows 16-19 there is a formula that just points to the location, category, period and load method in column A.
You might think that row 15 is the one defining the dimensions, this is not the case and it is row 20 where that is done.
The following table describes the values that can be used for defining dimensions.
If you are unclear on what the custom dimension values should be then you can have a look at the target application and the data table column names.
For example, if you take the “Product” dimension, the column is “UD5” which maps to “5” in the Excel template, it would also be valid to assign it to “UD5” in the Excel template.
The template can be changed to fit your requirements or a new one created, but the above logic should be applied. The main driver to where metadata definitions and data exists in the template is controlled by a named range called “upsJournal”.
If you are creating your own template, then as long as the named range starts with “ups” you can name it what you like.
The named range should cover the metadata definition and the data rows.
By default, the Excel template is defined to show dollars in the amount column, you may be shocked to realise but not everybody operates in dollar amounts :)
The formatting of the amount column can be updated in the template and it should not affect the import.
So, let’s update the template with a simple journal example based on what I have used in previous posts.
Members for entity, intercompany and movement do not match the target application, these will be updated by using mappings in Data Management.
Multi-GAAP has been left empty as this will also be handled by mappings.
I updated “Custom2” to “Product”, remember it is the hidden row value in the named range that defines the dimension. I have hidden the “Custom1” column or alternatively it could be deleted as it is only optional.
It doesn’t look like you can assign a description for the journal in the template unless there is an undocumented feature. It is possible to provide description for the individual journal lines though.
There is an important note that needs to be understood when loading journal data, this is taken from the documentation:
“When loading journals to an Oracle Financial Consolidation and Close Cloud target, consider that Data Management does not determine the account types or select the credits/debits. All positive numbers are loaded as credits and all negative numbers are loaded as debits. If you need to designate other credit or debit signs for your account type, use the change sign feature in Data Load Mappings or another customized method to handle credits/debits changes for your journal loads.”
On to importing the journal. As the data load rule was defined as a journal in the target options there will be a “Load Journal” button available in the workbench.
Selecting “Load Journal” will open a new window where the journal file can be uploaded and then selected.
Before you can import the journal to Data Management it must be checked. A verification process is executed where the file is checked to make sure the POV matches to that currently selected in the workbench, the named range in the file is also validated.
If the file checks out a successful message will be displayed.
The “Post” button will then be enabled.
Clicking “Post” does not post the journal to FCCS, it only means it will be imported into Data Management.
If the file is loaded successfully it will be mapped as with standard Data Management functionality.
In the process logs you can see how the Excel file was processed.
INFO [AIF]: EPMFDM-140274:Message - Adding Named Range: upsJournal
INFO [AIF]: EPMFDM-140274:Message - Start Cell:B16
INFO [AIF]: EPMFDM-140274:Message - End Cell:G33
INFO [AIF]: EPMFDM-140274:Message - Period :2018-12-31
INFO [AIF]: EPMFDM-140274:Message - Category :Actual
INFO [AIF]: EPMFDM-140274:Message - Location :Journal_Example
INFO [AIF]: EPMFDM-140274:Message - JournalId :Sales Adjustment
INFO [AIF]: EPMFDM-140274:Message - Using LoadMethod :REPLACE
INFO [AIF]: EPMFDM-140274:Message - Extracting metadata: End
INFO [AIF]: EPMFDM-140274:Message - Cell: B22 ColumnName: ACCOUNT Value:Operating Sales
INFO [AIF]: EPMFDM-140274:Message - Cell: C22 ColumnName: ENTITY Value:13
INFO [AIF]: EPMFDM-140274:Message - Cell: D22 ColumnName: ICP Value:No
INFO [AIF]: EPMFDM-140274:Message - Cell: E22 ColumnName: UD3 Value:Net Income
INFO [AIF]: EPMFDM-140274:Message - Cell: H22 ColumnName: UD5 Value:Smart Televisions
INFO [AIF]: EPMFDM-140274:Message - Cell: I22 ColumnName: AMOUNT Value:2000
INFO [AIF]: EPMFDM-140274:Message - Cell: B23 ColumnName: ACCOUNT Value:Returns and Allowances
INFO [AIF]: EPMFDM-140274:Message - Cell: C23 ColumnName: ENTITY Value:13
INFO [AIF]: EPMFDM-140274:Message - Cell: D23 ColumnName: ICP Value:No
INFO [AIF]: EPMFDM-140274:Message - Cell: E23 ColumnName: UD3 Value:Net Income
INFO [AIF]: EPMFDM-140274:Message - Cell: H23 ColumnName: UD5 Value:Smart Televisions
INFO [AIF]: EPMFDM-140274:Message - Cell: I23 ColumnName: AMOUNT Value:-2000
Before moving on I think it is worth trying to break some of the validation rules when importing the journal.
The Excel file was updated to a different location than the one currently selected.
An error message is displayed information the location does not match.
Now for an unmatched period.
As expected an error message is displayed to inform the period does not match the one currently selected.
It is shame that even though the template states to enter period name this does not seem to be correct.
The Excel file matches to the period name in the POV bar.
The same error is generated.
It looks like it must always match the period key and not the period name.
Anyway, back to the import that was successful.
Mappings have been applied and the validation step is shown as successful.
It is important that the “Data Source” dimension is mapped to the journal input member.
Just as when importing Journals through the simplified interface, it is possible to map to a target member name or alias.
At this point in the process the journal has not been created in the FCCS application.
An export can be run which will push the journal to the target application.
As the export was successful, the journal has been created.
Opening the journal confirms all the lines have been correctly loaded.
If you look at the process log generated in Data Management, it provides an indication to how the journal is created.
DEBUG [AIF]: Created journal load data file: /u03/inbox/outbox/FCCS_152.jlf
A journal “jlf” file is generated in the outbox before it is loaded to the target application.
If the file is downloaded and opened, you will notice the file is the same format as when importing journals through the simplified interface which I covered in the previous post.
So basically, all that Data Management is doing behind the scenes is producing the same formatted journal file and then importing it into the application.
If you want to assign the journal to a group, the Excel template will need to include a label and group definition like the following:
In the workbench it is possible to show the “Journal ID” column which is where the information from the Excel template is loaded to.
In the journal file created by Data Management you can see the journal section now includes the group.
When pushed to the target application the journal is assigned to the group.
Now it is time to break some of the rules for creating journals to see how they are handled in Data Management.
As I mentioned earlier, the documentation states the journal status can be working, submitted, approved or rejected, which I don’t agree with.
If I update the journal status to “Submitted” and run an export...
the export fails.
The process log highlights what the issue is.
INFO [AIF]: Executing journal data load to FCCS
DEBUG [AIF]: Executing request...
Parsing Journal file...
Error at line 5 : Only working(W) and posted(P) journals are allowed. "!JOURNAL=Sales Adjustment,,Submitted"
Parsing completed with errors...
ERROR [AIF]: Error loading journal data file: Error
As you would expect the rules follow the same logic as when importing through the simplified interface. When workflow is enabled, only working journals can be created. If workflow is not enabled, they can be working or posted.
How about running an export with a user that does not have access to members in journal?
The export fails, and the process log provides the error was due to invalid access levels.
INFO [AIF]: Executing journal data load to FCCS
DEBUG [AIF]: Executing request...
Parsing Journal file...
Parsing completed successfully...
Importing Journals...
Saving Journals for POV [Scenario=Actual,Year=FY18,Period=Dec] starts
Sales Adjustment
Error: You do not have edit access to the journal.
Saving Journals for POV [Scenario=Actual,Year=FY18,Period=Dec] ends
Journal import completed with error(s).
Next for an application that does not have workflow enabled, the status was updated to posted.
The POV was set to a period which is not open, the export fails.
No surprise that the process log now contains the error about an unopened period.
INFO [AIF]: Executing journal data load to FCCS
DEBUG [AIF]: Executing request...
Parsing Journal file...
Parsing completed successfully...
Importing Journals...
Saving Journals for POV [Scenario=Actual,Year=FY18,Period=Dec] starts
Sales Adjustment
Error: Action Post cannot be performed on the journal with label Sales Adjustment as the Period is not open.
Saving Journals for POV [Scenario=Actual,Year=FY18,Period=Dec] ends
Journal import completed with error(s).
Even though the process fails it follows the same logic as importing through the simplified interface, the journal will be created but with a status of working.
This time I opened the period and successfully ran the export again in Data Management.
The journal has been created and posted within the application.
There is not mention of “Posted” journal status in the Data Management documentation but you can see the output file is created with “P” for posted.
How about trying to post an unbalanced journal?
Once again, the export fails.
The log informs that the journal cannot be posted as it failed the scan.
INFO [AIF]: Executing journal data load to FCCS
DEBUG [AIF]: Executing request...
Parsing Journal file...
Parsing completed successfully...
Importing Journals...
Saving Journals for POV [Scenario=Actual,Year=FY18,Period=Dec] starts
Sales Adjustment
Error: Action Post cannot be performed on the journal due to scan error(s).
Saving Journals for POV [Scenario=Actual,Year=FY18,Period=Dec] ends
Journal import completed with error(s).
The journal will still be created with a working status.
If a scan is run on the journal in the application, it will fail as it is unbalanced.
I think that covers enough detail about loading Excel-based journals through Data Management. At the beginning of this blog post I said that there are two methods available for loading journals through Data Management. In the next and final part, I will go through loading journals using a standard text file.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.