Trying to find Default Expense Account discerption

Khalid Aldosari-Oracle

Hello everyone,

I am working in BI Report and I am trying to find Default Expense Account discerption. I found the code but I could not find the discretions.

the code found it in this table gl_code_combinations.

the path to get those detail from fusion My Client Group > Person Management > Search for Employee > Employment Info > Expenses
Information

Thank you in advance

Nathan CCC

Alternative use the physical sql in a dataset with data source applicationDB_FSCM in a data model in OTBI.

select all t.*

, (fnd_flex_xml_publisher_apis.process_kff_combination_1

(upper('FLEXFIELD'), upper('GL'), upper('GL#'), t.chart_of_accounts_id, null, t.code_combination_id, upper('ALL'), upper('Y')

, upper('VALUE')))

as concatenated_code

, (fnd_flex_xml_publisher_apis.process_kff_combination_1

(upper('FLEXFIELD'), upper('GL'), upper('GL#'), t.chart_of_accounts_id, null, t.code_combination_id, upper('ALL'), upper('Y')

, upper('FULL_DESCRIPTION')))

as concatenated_desc

, (fnd_flex_xml_publisher_apis.process_kff_combination_1

(upper('FLEXFIELD'), upper('GL'), upper('GL#'), t.chart_of_accounts_id, null, t.code_combination_id, '1', upper('Y')

, upper('FULL_DESCRIPTION')))

as segment1_desc

, (fnd_flex_xml_publisher_apis.process_kff_combination_1

(upper('FLEXFIELD'), upper('GL'), upper('GL#'), t.chart_of_accounts_id, null, t.code_combination_id, '2', upper('Y')

, upper('FULL_DESCRIPTION')))

as segment2_desc

, (fnd_flex_xml_publisher_apis.process_kff_combination_1

(upper('FLEXFIELD'), upper('GL'), upper('GL#'), t.chart_of_accounts_id, null, t.code_combination_id, '3', upper('Y')

, upper('FULL_DESCRIPTION')))

as segment3_desc

…

-- https://docs.oracle.com/en/cloud/saas/financials/22d/oedmf/glcodecombinations-12960.html#glcodecombinations-12960

-- gl_code_combinations_pk code_combination_id

-- gl_code_combinations_u1  unique  default code_combination_id

from gl_code_combinations t

Nathan CCC

Hi, here is the SQL to get the code and description

select all 0 s_0

, "Employee"."Employee Default Expense Concatenated Segments" as default_expense_account_code

, "Employee"."Employee Default Expense Account" as default_expense_account_description

from "Expenses - Expense Transactions Real Time"

order by 1 asc nulls last, 2 asc nulls last

fetch first 7 rows only

BTW It uses a PLSQL function call to get the description for a code combination

FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1(
'FLEXFIELD'
,'GL'
,'GL#'
,V371846109.CHART_OF_ACCOUNTS_ID
,NULL
,V371846109.CODE_COMBINATION_ID
,'ALL','Y','FULL_DESCRIPTION')

Khalid Aldosari-Oracle

@Nathan CCC Thank you so much but I am trying to mapping that to bi Publisher but did not show anything.

Nathan CCC

Hi, Not sure what you mean by "mapping that to". But I can cut paste that query ok into a data set with data source Oracle BI EE in a data model in OTBI. It returns rows no problem.

Khalid Aldosari-Oracle

@Nathan CCC Thank you so much appreciate your time I never used Oracle Bi EE before thats why I was confused. in this point how I can connect it with my DM in FSCM?

Iann hick

Hi @Nathan CCC do you happen to know how to use FND_FLEX_XML_PUBLISHER_APIS.PROCESS_KFF_COMBINATION_1 in BIP data model ? I tried to query the SQL above but its erroring out. PROCESS_KFF_COMBINATION_1 is where the description of the COA is stored which I need to include in my report. Thank you