Customize OBIA Financial Analytics GL Ledger Account

User_QE77W

Hi Community, Im new to OBIA (11.1.1.7)11g integrated with ODI. Can you please assist me with updating the mapping or scenario for the Ledger Account. I want to update this mapping so that we only extract one Ledger account for example WB Ledger IV. Please assist with steps to achieve this. Thank you.

Charles M

Hi,

This is definitely not my area of expertise. But, I see that there haven't been any responses here ... so I'll see if I can offer anything (perhaps, I will also learn something along the way ). I see a table named W_LEDGER_D in the Bi Apps data warehouse. It has the following description:

Stores information about the ledgers, each row includes the ledger name, short name, description, ledger

currency, calendar, period type, chart of accounts, and other information.

When I looked through the ODI mappings, I found that SDE_ORA_LedgerDimension.W_LEDGER_DS_SQ_GL_LEDGERS, SDE_ORA_LedgerDimension.W_LEDGER_DS; SIL_LedgerDimension.W_LEDGER_D are responsible for for loading that table. There may also be others, I am not sure. These are for an EBS 12.2 source - you're going to need to let the community know what your source is in order to get the right mappings.

My intuition tells me that this isn't going to be a simple task. Hopefully, this gets you started. Please let us know. Also, there are functional configuration resources available. Are you familiar with any of these? Do you have a My Oracle Support account?

Regards,

Charles

User_QE77W

Hi Charles, Thank you for a reply and suggestions. My source is Oracle E-Business Suite R12.1.3.x. I have done the functional configuration part and yes I do have My Oracle Support account. I will go through your suggestions and update here. Thank you

Charles M

Sure. One other table that looks related is: W_GL_ACCOUNT_D. Here's the description of it:

This dimension table stores your General Ledger accounts. In Oracle, this table stores the GL Code

Combinations. The GROUP_ACCOUNT_NUM field categorizes GL accounts into various groups. This

categorization denotes the nature of the GL account. Some of the seeded GROUP_ACCOUNT_NUM values are

¿PPAID EXP¿ Prepaid Expense, ¿CASH¿, Cash, ¿WIP INV¿ Work in Process Inventory, etc.

If that sounds useful, then you'd just need the related mappings that load it (I haven't looked through ODI yet).

Also, it may be worthwhile to install the sample app. Are you familiar with that? That way, you could query these tables and see if the data looks like it is what you are trying to get.

Regards,

Charles

User_QE77W

Hi,

I have made changes to these two mappings (SDE_ORA_DomainGeneral_SQ_GL_LEDGER and SDE_ORA_LedgerDimension) and run a fresh load. However the W_LEDGER_D dimension has all the records I am not too sure whether Im missing a step here?

Attached are the steps followed to attempt the customization.

Then re-generated the load plan an  run a fresh load. However the W_LEDGER_D dimension has all the records.

Your assistance would be highly appreciated.

Thank you

User_QE77W

Hi,

I have noticed that the execution code for SDE_ORAR1213_ADAPTOR_SDE_ORA_LEDGERDIMENSION - 5 > Load data has not included the filter LEDGER_ID.

select

SQ_GL_LEDGERS.NAME    C1_LEDGER_NAME,

SQ_GL_LEDGERS.SHORT_NAME    C2_LEDGER_SHORT_NAME,

SQ_GL_LEDGERS.DESCRIPTION    C3_LEDGER_DESC,

TO_CHAR(SQ_GL_LEDGERS.CHART_OF_ACCOUNTS_ID)    C4_CHART_OF_ACCOUNTS,

COALESCE(SQ_GL_LEDGERS.CURRENCY_CODE,'__UNASSIGNED__')    C5_CURRENCY_CODE,

SQ_GL_LEDGERS.PERIOD_SET_NAME    C6_CALENDER_NAME,

COALESCE(SQ_GL_LEDGERS.SLA_ACCOUNTING_METHOD_CODE,'__UNASSIGNED__')    C7_SLA_ACCOUNTING_METHOD_CODE,

SQ_GL_LEDGERS.USER_PERIOD_TYPE    C8_PERIOD_TYPE,

COALESCE(SQ_GL_LEDGERS.LEDGER_CATEGORY_CODE,'__UNASSIGNED__')    C9_LEDGER_CATEGORY_CODE,

TO_CHAR(SQ_GL_LEDGERS.CREATED_BY)    C10_CREATED_BY_ID,

TO_CHAR(SQ_GL_LEDGERS.LAST_UPDATED_BY)    C11_CHANGED_BY_ID,

SQ_GL_LEDGERS.CREATION_DATE    C12_CREATED_ON_DT,

SQ_GL_LEDGERS.LAST_UPDATE_DATE    C13_CHANGED_ON_DT,

TO_CHAR(SQ_GL_LEDGERS.LEDGER_ID)    C14_INTEGRATION_ID

from

( /* Subselect from SDE_ORA_LedgerDimension.W_LEDGER_DS_SQ_GL_LEDGERS

*/

select

 

  

   GL_LEDGERS.LEDGER_ID LEDGER_ID,

GL_LEDGERS.NAME NAME,

GL_LEDGERS.SHORT_NAME SHORT_NAME,

GL_LEDGERS.DESCRIPTION DESCRIPTION,

GL_LEDGERS.LEDGER_CATEGORY_CODE LEDGER_CATEGORY_CODE,

GL_LEDGERS.CHART_OF_ACCOUNTS_ID CHART_OF_ACCOUNTS_ID,

GL_LEDGERS.CURRENCY_CODE CURRENCY_CODE,

GL_LEDGERS.PERIOD_SET_NAME PERIOD_SET_NAME,

GL_LEDGERS.SLA_ACCOUNTING_METHOD_CODE SLA_ACCOUNTING_METHOD_CODE,

GL_LEDGERS.LAST_UPDATE_DATE LAST_UPDATE_DATE,

GL_LEDGERS.LAST_UPDATED_BY LAST_UPDATED_BY,

GL_LEDGERS.CREATION_DATE CREATION_DATE,

GL_LEDGERS.CREATED_BY CREATED_BY,

XLA_ACCTG_METHODS_TL.NAME SLA_ACCOUNTING_METHOD_NAME,

GL_PERIOD_TYPES.USER_PERIOD_TYPE USER_PERIOD_TYPE

from APPS.XLA_ACCTG_METHODS_TL   XLA_ACCTG_METHODS_TL, APPS.GL_LEDGERS   GL_LEDGERS, APPS.GL_PERIOD_TYPES   GL_PERIOD_TYPES

where (1=1)

And (GL_LEDGERS.SLA_ACCOUNTING_METHOD_CODE=XLA_ACCTG_METHODS_TL.ACCOUNTING_METHOD_CODE(+))

AND (GL_LEDGERS.SLA_ACCOUNTING_METHOD_TYPE=XLA_ACCTG_METHODS_TL.ACCOUNTING_METHOD_TYPE_CODE(+))

AND (GL_LEDGERS.ACCOUNTED_PERIOD_TYPE=GL_PERIOD_TYPES.PERIOD_TYPE)

----- EXPECTING TO HAVE A LEGDER_ID AS A FILTER

And (NVL(GL_LEDGERS.COMPLETE_FLAG,'Y')='Y')

And (GL_LEDGERS.OBJECT_TYPE_CODE='L')

And (XLA_ACCTG_METHODS_TL.LANGUAGE(+)= '#BIAPPS.LANGUAGE_BASE')

)   SQ_GL_LEDGERS

where (1=1)

Charles M

Hi,

Very important ... if you are doing customizations, there is a process to follow. Have you seen this document? https://docs.oracle.com/cd/E63231_01/doc/BIAET/GUID-C9E7D7FD-3C17-4247-962B-56DD9E8A2B6B.htm#BIASA24484

So, I'd suspect that the reason your code is not being reflected is because the scenario wasn't regenerated (or, generated as a new scenario) - the scenarios are what's being executed by the load plan. And, recall, that they are "compiled" code. Thus, they need to be "re-compiled" if any changes are made to the mappings, packages, etc. https://docs.oracle.com/middleware/11119/odi/develop/scenarios.htm#ODIDG467

Regards,

Charles