Fusion General Ledger: Currency creation Setup/ Configuration

Definition: A system of money in general use in a particular country called currency

Types of currencies

1. Functional currency/Accounted currency/ Primary currency (Local currency)
2. Entered currency / Foreign currency (Foreign currency)
3. Reporting currency (Head quarter currency)
4. Stat currency

These three currencies called monitory currencies and Stat currency (Ratios, meters and feet) called non monitory currencies 

1. Local currency in India = INR
2. Local currency in USA = USD

For India INR is functional currency (Local currency) and USD is foreign currency 

For USA USD is functional currency (Local currency) and INR is foreign currency

• Go to set up and maintenance
• Set up: Financials
• Functional Area : Financial reporting structure 
• Task: %Mange %currencies%
• Select Manage currencies

•  Then select Manage currencies after UI (User Interface) will open)
•  All currencies are predefined in system and we can create also
•  Search for currency code (INR), it is predefined currency it should be Enabled then only we can use this currency, Start date should be back date
•  Save and close or save

SQL Queries to Track FAH Import Accounting Transactions process ID , Create Accounting for Data Set Process ID and Others

Query 1: Link Import Accounting Transactions with Create Accounting for Data Set

select * from FUSION_ORA_ESS.REQUEST_history where requestid in (select requestid from FUSION_ORA_ESS.REQUEST_PROPERTY where name = 'submit.argument23' and value = 'XLA_LOAD_<Import Accounting Transactions request ID>')

Query 2: Link Create Accounting for Data Set with Create Accounting: Subprocess and Post Subledger Journal Entries

select decode(name,'apps.fin.xla.xlafsnrpt.childRequests','Create Accounting Subprocess','apps.fin.xla.xlafsnrpt.gltparent','Post Subledger Journal Entries'),value from ESS_REQUEST_PROPERTY where requestid = <Create Accounting for Data Set request ID from Query 1> and name in ('apps.fin.xla.xlafsnrpt.gltparent','apps.fin.xla.xlafsnrpt.childRequests')

Query 3. Link Post Subledger Journal Entries with Post Subledger Journal Entries: Subprocess

select DECODE(name,'apps.fin.xla.xlagltrn.childRequests','Post Subledger Journal Entries Subprocess') jobname,value from ess_request_property where requestid = <Post Subledger Journal Entries request ID from Query 2> and name ='apps.fin.xla.xlagltrn.childRequests'

Query 4. Link Create Accounting: Subprocess with Update Subledger Accounting Balances

select prop1.requestid XLABABUP, hist1.requestid CASUBPROCESS from fusion_ora_ess.request_history hist1 , fusion_ora_ess.request_property prop1, fusion_ora_ess.request_history hist2 , fusion_ora_ess.request_property prop2 WHERE prop2.name = ('submit.argument8') and prop2.requestid = <a Create Accounting Subprocess request     ID from Query 2> and hist1.requestid = prop2.requestid and hist1.name = 'XLAFSNAPENG' and prop1.name = ('submit.argument3') and prop1.requestid = hist2.requestid and prop1.value = prop2.value and hist2.name = 'XLABABUP'

Query 5. Link Import Accounting Transactions with Create Accounting for Data Set, Post Subledger Journal Entries and Update Subledger Accounting Balances 

select base4.zipfile, substr(base4.loadfile,1,16) loadfile,base4.cadatasetid cadataset,

(select count(1) from fusion.xla_fsn_events_int where parent_request_id <> -1

and report_request_id = cadatasetid) event_count,

(select value from fusion_ora_ess.request_property

where requestid = cadatasetid

and name = 'submit.argument3') application_id,

(select value from fusion_ora_ess.request_property

where requestid = cadatasetid

and name = 'submit.argument4') ledger_id,

substr(replace(base4.casubprocess_ids,',','-'),1,300) casubprocess,

to_number(base4.accounting_batch_id) accountingbatchid,

to_number(base4.gltp) GLTParentProcessId,

to_number(base4.XLABABUP) XLABABUP,

prop5.value gltw ,

hist3.processstart importstarttime,

hist3.processend importendtime,

hist3.processend - hist3.processstart importspendtime,

base4.castart,

base4.caend,

base4.caend -base4.castart,

hist2.processstart xlababup_start_time,

hist2.processend xlababup_end_time,

hist2.processend - hist2.processstart xlababupspendtime,

nvl(hist2.processend,base4.caend) - hist3.processstart total_time_taken

from fusion_ora_ess.request_property prop5,

fusion_ora_ess.request_history hist2,

fusion_ora_ess.request_history hist3,

(select base3.*,prop4.value gltp from fusion_ora_ess.request_property prop4, (SELECT base2.*,

prop3.requestid XLABABUP

FROM fusion.ess_request_property prop3,(

SELECT base.loadfile,

base.zipfile,

base.cadatasetid,

base.castart,

base.caend,

base.process_ids casubprocess_ids,

value accounting_batch_id

FROM fusion.ess_request_property prop2,

(SELECT zipfile,

loadfile,

cadatasetid,

castart,

caend,

process_name,

process_ids

FROM

(SELECT base0.*,

--hist.requestid cadatasetid ,

DECODE(prop.name,'apps.fin.xla.xlafsnrpt.childRequests','Create Accounting

Subprocesses' ) process_name,

prop.value process_ids

FROM fusion.ESS_REQUEST_PROPERTY prop,

fusion_ora_ess.request_history hist,

(SELECT hist0.requestid cadatasetid ,

prop0.value loadfile,

hist0.processstart castart,

prop1.value zipfile,

hist0.processend caend

FROM FUSION_ORA_ESS.REQUEST_history hist0,

FUSION_ORA_ESS.REQUEST_PROPERTY prop0,

FUSION_ORA_ESS.REQUEST_PROPERTY prop1

WHERE hist0.requestid = prop0.requestid

AND prop0.name = 'submit.argument23'

AND prop0.value LIKE 'XLA_LOAD_%'

and prop1.requestid = substr(prop0.value,10,length(prop0.value))

and prop1.name = 'submit.argument2'

AND TRUNC(hist0.processstart)> to_date(<Start Date of the Import Accounting

Transactions request>,'dd-MON-YYYY')

) base0

WHERE prop.requestid = hist.requestid

AND prop.name IN ('apps.fin.xla.xlafsnrpt.childRequests')

AND hist.requestid = base0.cadatasetid

)

) base

WHERE prop2.name IN ('submit.argument8')

AND prop2.requestid = DECODE (instr(base.process_ids,',',1), 0,

base.process_ids,SUBSTR(base.process_ids,0,instr(base.process_ids,',',1)-1))

AND base.process_name = 'Create Accounting Subprocesses') base2

WHERE prop3.value (+)= base2.accounting_batch_id

AND prop3.name (+)= 'submit.argument3') base3

WHERE base3.cadatasetid = prop4.requestid

AND prop4.name = 'apps.fin.xla.xlafsnrpt.gltparent'

) base4

where base4.gltp = prop5.requestid

and prop5.name = 'apps.fin.xla.xlagltrn.childRequests'

and hist2.requestid (+) = to_number(base4.XLABABUP)

and hist3.requestid = to_number(substr(base4.loadfile,10))

ORDER BY cadatasetid

To Restrict the Access To Only Accounting Hub And Allow Access To specific Sub Ledger(AP,AR)

Users would like to secure Fusion Accounting Hub by subledger. For example if there is an external application that feeds payroll data into Fusion Accounting Hub with employee information it would best if users could access Accounts Payables , Accounts Receivables but not the payroll information in the transaction

It is possible to restrict user access by subledger application via the below steps.

Please note that is not possible to create the permissions so users can view the GL journal, drill into the subledger journal but not drill into the transaction. If the users do not have the privilege to view/manage suledger transaction, then the user cannot drill down to the transaction. For Fusion Accounting Hub Cloud subledgers, user who can view the subledger journals will be able to view the transaction data until the transaction data is purged. There is no separate privilege to control access.

To restrict user access by subledger application, please follow the below steps -

1. Create a custom role in Security Console by copying the General Accounting Manager role: Select Copy top role and inherited roles copy options
2. Edit the custom role
3. Navigate to Data Security Policies (DSP's) train stop and edit the policies defined for Subledger Application Data Resources (filter on Policy Name column by 'Subledger')
4. Update policies defined for Subledger Source Transaction database resource as follows
• Database Resource: Subledger Source Transaction
• Data Set: Select by instance set
• Condition Name: Access Subledger Source Transactions for a Ledger
• Parameter1: enter the application ID, for example, 200 is the application ID for Payables
• Actions: Manage Subledger Source
5. Update policies defined for Subledger Application database resource as follows
• Database Resource: Subledger Application
• Data Set: Select by instance set
• Condition Name: Subledger Application Instance Set
• Parameter1: enter the application ID, for example, 200 is the application ID for Payables
• Actions: Manage Subledger Application Data
6. Assign this new role to a user.
7. Save and Close.
8. Edit the below two inherited roles by removing two DSPs that are noted above (Subledger Source Transaction and Subledger Application) DSPs.
• Financial Analyst Custom
• General Accountant Custom

1. Set up the data access set for the user with the new custom role above in the Manage Data Access Set for User. For example, set up the 'Ledger', 'Business Unit', 'Data Access Set' context value for the user and the new role.