How to extract the Chart of Account (COA) from Oracle

Here are a few useful SQL scripts used by GL consultants:

1) This SQL lists all GL Segment values:

select fvs.FLEX_VALUE_SET_NAME, ffv.FLEX_VALUE, ffvt.FLEX_VALUE_MEANING, ffvt.DESCRIPTION

,ffv.ENABLED_FLAG, ffv.SUMMARY_FLAG

,ffv.START_DATE_ACTIVE ,ffv.END_DATE_ACTIVE, ffv.HIERARCHY_LEVEL

,replace(ffv.COMPILED_VALUE_ATTRIBUTES,chr(10),'_') Budget_Post

from applsys.FND_FLEX_VALUES_TL ffvt, applsys.FND_FLEX_VALUES ffv, applsys.FND_FLEX_VALUE_SETS fvs,

applsys.FND_ID_FLEX_SEGMENTS fseg, applsys.FND_ID_FLEX_STRUCTURES fifs,

applsys.FND_ID_FLEXS fif, applsys.FND_APPLICATION_TL fat

where ffvt.LANGUAGE = 'US'

and ffvt.FLEX_VALUE_ID = ffv.FLEX_VALUE_ID

and ffv.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID

and fvs.FLEX_VALUE_SET_ID = fseg.FLEX_VALUE_SET_ID

and fseg.APPLICATION_ID = fifs.APPLICATION_ID

and fseg.ID_FLEX_CODE = fifs.ID_FLEX_CODE

and fseg.ID_FLEX_NUM = fifs.ID_FLEX_NUM

and fifs.ID_FLEX_CODE = fif.ID_FLEX_CODE

and fifs.application_id = fif.application_id

and fif.application_id = fat.application_id

and fif.ID_FLEX_NAME = 'Accounting Flexfield'

and fat.APPLICATION_NAME = 'General Ledger'

and fat.LANGUAGE = 'US'

2) This SQL lists all code combinations created:

SELECT

gcc.code_combination_id

,gcc.segment1

,gcc.segment2

,gcc.segment3

,gcc.segment4

,gcc.segment5

,gcc.segment6

,gcc.segment7

,gcc.segment8

,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,1,gcc.segment1),1,40) segment1_desc

,SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,2,gcc.segment2),1,40) segment2_desc

,DECODE(gcc.segment3,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,3,gcc.segment3),1,40)) segment3_desc

,DECODE(gcc.segment4,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,4,gcc.segment4),1,40)) segment4_desc

,DECODE(gcc.segment5,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,5,gcc.segment5),1,40)) segment5_desc

,DECODE(gcc.segment6,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,6,gcc.segment6),1,40)) segment6_desc

,DECODE(gcc.SEGMENT7,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,7,gcc.segment7),1,40)) segment7_desc

,DECODE(gcc.SEGMENT9,NULL,'',SUBSTR(apps.gl_flexfields_pkg.get_description_sql( gcc.chart_of_accounts_id,8,gcc.segment8),1,40)) segment8_desc

,gcc.chart_of_accounts_id chart_of_accounts_id

,gcc.account_type

FROM

gl_code_combinations gcc

-- For references visit my friend's blog here

NAB Online vs. NAB Direct

As an Oracle consultant, I often need to configure Oracle to create EFT files that get sent electronically to bank institutions for making payments to suppliers.


In Australia, there is a Bank Institution that currently has 2 different software offerings (Online & Direct) that connect to an ERP system like Oracle. This institution is NAB, National Australia Bank.


And during conversations I had with a financial director of one of my previous engagements, the difference between NAB Online and NAB direct was explained to me as per below, or this is what I understoond the difference was:


NAB Online: is the current version of software provided by NAB for enabling EFT payments, and has a built-in authorisation process for every payments which require 2 people to approve payments before the bank releases the funds.



NAB Direct: is the new software offering which has more automation when it comes to process and configuration, but the authorisation process from the previous version (NAB Online) is no longer offered in the new software, and this is critical per the Finance Director.

The director in question raised the new NAB offering, NAB Direct, as a risky piece of software and left me with these questions: "How can you prevent a techie person from dropping an EFT payment file in the designated NAB directory and tell me who will approve this payment?"

I could not argue with him regarding the director's last point. And I knew I could not diverge the director's irrational distrust of others towards a viable workaround or solution. But I could have had easily provided the director with a few hundred other ways the "techies" could cheat the system.

Here are a few benefits of "NAB Direct" listed their website:


• Leverages your ERP investment – use your ERP system to add ‘end to end’ connectivity between you and NAB.

• Helps save time and reduces costs – fully automatic straight through processing virtually eliminates the needfor manual reconciliation.

• Reduces risk – process high-volume transactions with limited human interaction, thereby reducing operationalrisk.

• Strong security – four layers of security, including digital signatures and message encryption, help to ensure yourconnection to NAB is protected.

My personal opinion about this issue is that the new R12 Oracle Payments module can easily handle the authorisation process within Oracle. The 'end to end' connectivity between Oracle and NAB should be leveraged as well. If a payment goes through by fraud, this will get flagged in the cash management module - alerts can also be defined to notify HR, the legal department, and your Finance Director.

As a side note, if your techies cannot be trusted, I suggest you start looking at outsourcing their tasks to an insured and qualified 3rd party vendor with a reputation to protect.

Oracle Profile Options

Here are a few profile options that I use often in an Oracle implementation.

'Site Name:' and 'Java Color Scheme'
These are used for distinguishing Oracle eBusiness Suite environments - the name gets displayed on top of the "Form" Window. Make sure you share this with your favourite DBA's as they should update this profile option every time they clone environments.

Utilities Diagnostics:
In case you don't get along with your DBA, and you don't know the APPS password, this is an excellent tool for troubleshooting issues raised in Oracle. You can still examine fields via the Help > Diagnostics > Examine menu path when this profile option is set to 'Yes' at your user level. Some DBA's also use this profile option to control the display of the diagnostics menu, by setting up the following profile option: 'Hide Diagnostics menu entry'.

Search for all profile options starting with "OIE:%":
When setting up Internet Expenses, make sure you review these profile options and what each of them are used for, even before the scoping phase of a project starts, this will definately help you with your implementation of the iExpense module, and have a better understanding of the functionalities available in this module.

Please send me your profile options - so we can build a little library of tips and tricks.