Sunday, 8 May 2022

Fusion Financial GL

 General Ledger: It is the final model in the Financial ERP. Whenever money-related transactions happen in any module like AR, AP, Project then for each transaction two records will be created in Journal though Sub-Ledger (SLA). One record is for credit and the other is for debit. The Sum of debit and credit should be equal to zero. Otherwise, the reconciliation team will manage the transactions.

You must keep the track of the source from where entries are coming into the General ledger.

Sources maybe 

  • Payable
  • Receivables
  • Project Accounting
  • Inventory
  • Legacy systems like EBS, DB, and Flat files




If you want to use General Ledger then roles related to the general ledger must be added to the user along with Data Access must be provided to a user

Roles Related to General Ledger

  • General Accounting Manager
  • General Accountant
  • Financial Analyst


To add a role, go to Tools > Security Console > User > Search user "Fusion.User" and then edit the user and add role, and then run the LDP ESS job to effect these changes.


Now re-login to fusion, and General Accounting will be visible.



Now click on Journal, It will ask to select Data Access Set. Where Data Access Set controls the set of data accessed by various people. and it will be provided on the Ledger level, Legal Entity, and Business unit.


To provide access to the data access set, you must have to assign the Ledger to the user and Data Access Set related to the Ledger.

To achieve this you must have access to Set up and maintenance 


Then Search the task Manage Data Access Set Data Access for Users

Then enter user like. Fusion. User and then click on search then the list of roles and security context will be visible but the Security Context value is not assigned, so first assign the context values. 


Click on Authorize Data Access, and it will download the excel SecurityDataAccessTemplate containing the above lines of rows like Security context, Security context values, user name, role and Changed and status will be available.

Provide US Primary Ledger as security context value against ledger and US Primary ledger against Data access set

Then change these details by ADF Desktop Integration and data will be automatically changed into Fusion.

First, understand how can we create the Journal manually and what the details and related base tables are required.

To Create Journal go to Navigator > General Accounting then click on Journals

Then click on Tasks and it will list down all the tasks related to General Ledger.

Tasks maybe

  • Create Journals
  • Manage Journals
  • Import journals and many other tasks

Click on Create Journals

While creating the journals you may need to provide a number of details and these details can be categorized below

Journal batch: --> gl_je_batches where batch_id is the primary key

 Some of the details related to Journal Batch are 

  • Journal batch --> Will be stored in the table
  • Description
  • Accounting Period --> Will be derived from table
  • Source --> gl_je_sources
Journal: --> gl_je_headers where header_id is the PK and batch_id is the FK
Some of the details related to the Journal are related

  • Journal
  • Ledger
  • Accounting date
  • Category
  • Currency

Journal Line: --> gl_je_lines where header_id is the FK and JE_LINE_NUM is the PK

Some of the details related to Journal Lines are 

  • Line number
  • Account
  • Debit and Credit

How to get the Source from the front end

Go To Setup and maintenance > Financials > Search Task (Manage Journal Sources) and click on this task. All the available Sources will be visible here

How to get the Source from the back end

Go To navigator > Tools > Report and Analytics then click on browse Catalog

There will be two folders 

  • My Folders --> will be local to the user i.e. private folder
  • Shared Folders --> Will be used by the application and custom reports will be under custom folder


To get the data to create the report into the My Folder, To create the data model click on the data model 

Then click on the diagram and then select SQL Query and enter the name, Data Source, and SQL query

Select * from all_table where table_name like '%source%'
select * from gl_je_sources where je_source_name = 'manual'

Then click ok and click on Data and View then table view. Sources related to data will be visible.

Below are the tables and views related to source

  • gl_je_sources_b --> base table and doesn't contain description
  • gl_je_sources_tl --> translated table (child table of the base table) and joined based on je_source_name
  • gl_je_sources --> view

select gtl.description,gtl.user_je_source_name, gtl.je_source_name, gjs.je_source_key

from gl_je_sources_b gjs , gl_je_sources_tl gtl where gtl.je_source_name=gjs.je_source_name and gtl.language='US'

How to get the Category from the front end

Search the task Manage Journal Categories 

How to get the Category from the backend

  • gl_je_categories
  • gl_je_categories_b
  • gl_je_categories_tl


Once you will create the journals you may get batch details and journal headers details and journal lines from the below tables

To get the Batch Details select * from gl_je_batch_id

To get the Journal Header details select * from gl_je_headers where batch_id = 12345

To get the Journal Lines select * from gl_je_lines where header_id = 1234557

Line level details will have Account which is derived from gl_code_combinations table

gl_code_combinations table will have columns like segment1, segement2 etc.

company.line-of-business.account.cost-center.product.intercompany.future-use

101.10001.50023.0000.0000.0000.0000

select * from gl_code_combinations

gl_code_combinations table will have columns like segment1, segement2 etc.

  • company --> segement1
  • line-of-business --> segement2
  • account --> segement3
  • cost-center --> segement4
  • product --> segment5
  • intercompany --> segment6
  • future-use --> segment7


let's compare the frontend labels with tables with the below image









No comments:

Post a Comment