[go: up one dir, main page]
Scribd
Upload
128 views21 pages

SQL Queries for GL and COA Data

This document contains several SQL queries related to retrieving and summarizing data from Oracle E-Business Suite for the purpose of loading actual balances and chart of account segment values to Hyperion Planning. The queries select data from tables like GL_CODE_COMBINATIONS, GL_BALANCES, and FND_ID_FLEX_STRUCTURES_VL to summarize general ledger balances and retrieve chart of account segment values and flex value sets.

Uploaded by

Saptarshi Bose
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
128 views21 pages

SQL Queries for GL and COA Data

This document contains several SQL queries related to retrieving and summarizing data from Oracle E-Business Suite for the purpose of loading actual balances and chart of account segment values to Hyperion Planning. The queries select data from tables like GL_CODE_COMBINATIONS, GL_BALANCES, and FND_ID_FLEX_STRUCTURES_VL to summarize general ledger balances and retrieve chart of account segment values and flex value sets.

Uploaded by

Saptarshi Bose
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 21

Query to find reqd.

SOB-ID

select * from apps.gl_sets_of_books

--where name like 'ADAC%'; --1041


SET_OF_BOOKS_ID NAME SHORT_NAME CHART_OFCURRENCYPERIOD_S
1 2021 Oger Abu Dhabi Oger Abu Dh2021(AED) 50346 AED Oger_Cal
ACCOUNTESUSPENSE ALLOW_INTRACK_ROENABLE_A ENABLE_B REQUIRE_ ENABLE_JEENABLE_A CONSOLID
21 N Y N N Y Y N N N
TRANSLAT TRANSLAT TRANSLAT MRC_SOB_ALLOW_POLAST_UPD LAST_UPD CREATION CREATED_ LAST_UPDA
N N N N N ### 1133 ### 1133 148155
FUTURE_ENLATEST_O LATEST_E RET_EARNCUM_TRANRES_ENCUNET_INCO ROUNDINGTRANSACTDAILY_TRA
1 10-Jul 2010 1000 1005
EARLIEST DESCRIPTI ATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTE
ATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTEATTRIBUTECONTEXT GLOBAL_AGLOBAL_AT
GLOBAL_ATGLOBAL_ATGLOBAL_ATGLOBAL_ATGLOBAL_ATGLOBAL_ATGLOBAL_ATGLOBAL_ATGLOBAL_ATSLA_LEDGER_CASH_BASIS_F
N
SLA_LEDGER_CASH_BASIS_FLAG
Query to find the COA segments and the value_set for any SOB

-- The value set would give the values for a particular segment

select gsob.set_of_books_id,

gsob.name,

gsob.description,

ffsv.id_flex_num,

ffsv.id_flex_structure_code,

ffsv.id_flex_structure_name,

ffsv.description,

ffsv.enabled_flag,

ffsv.freeze_flex_definition_flag,

fifs.application_column_name "COA_segment",

fifs.segment_name,

fifs.segment_num "segment_no.",

fifs.display_size "segment_size",

fifs.flex_value_set_id

from apps.FND_ID_FLEX_STRUCTURES_VL ffsv,

apps.gl_sets_of_books gsob,

apps.FND_ID_FLEX_SEGMENTS fifs

where
ffsv.application_id=101 -- for GL

and ffsv.id_flex_code='GL#' --for GL

and fifs.id_flex_code='GL#' --for GL


and ffsv.id_flex_num=gsob.chart_of_accounts_id

and fifs.id_flex_num=gsob.chart_of_accounts_id

Err:509

order by fifs.application_column_name ;
SET_OF_BOOKS_ID NAME DESCRIPTION ID_FLEX_NUM
2 2021 Oger Abu Dhabi 50346
4 2021 Oger Abu Dhabi 50346
6 2021 Oger Abu Dhabi 50346
8 2021 Oger Abu Dhabi 50346
10 2021 Oger Abu Dhabi 50346
12 2021 Oger Abu Dhabi 50346
14 2021 Oger Abu Dhabi 50346
16 2021 Oger Abu Dhabi 50346
ID_FLEX_STRUCTURE_CODE ID_FLEX_ DESCRIPTION ENABLED_FLAG FREEZE_FLEX_DEFINIT
OGAD Chart of Accounts OGAD Chart of Accounts Y Y
OGAD Chart of Accounts OGAD Chart of Accounts Y Y
OGAD Chart of Accounts OGAD Chart of Accounts Y Y
OGAD Chart of Accounts OGAD Chart of Accounts Y Y
OGAD Chart of Accounts OGAD Chart of Accounts Y Y
OGAD Chart of Accounts OGAD Chart of Accounts Y Y
OGAD Chart of Accounts OGAD Chart of Accounts Y Y
OGAD Chart of Accounts OGAD Chart of Accounts Y Y
COA_segment SEGMENT_NAME segment_nsegment_siFLEX_VALUE_SET_ID
SEGMENT1 OGAD_Company 1 2 1014170
SEGMENT2 OGAD_LOB 2 2 1014171
SEGMENT3 OGAD_Project 3 6 1014172
SEGMENT4 OGAD_Cost_Center 4 4 1014173
SEGMENT5 OGAD_Accounts 5 5 1014174
SEGMENT6 OGAD_Sub_Accounts 6 4 1014175
SEGMENT7 OGAD_Intercompany 7 2 1014170
SEGMENT8 OGAD_Future 8 4 1014176
Base Query to find the GL balances to be loaded to Hyperion as actuals for a particular SOB

SELECT GCC.SEGMENT1,

GCC.SEGMENT2,

GCC.SEGMENT3,

GCC.SEGMENT4,

GCC.SEGMENT5,

GCC.SEGMENT6,

'Actual' AS "SCENARIO",

'Final' AS "VERSION",

SUBSTR(GB.PERIOD_NAME,1,3) "PERIOD", -- imp is to parameterize the period of data load –otherwise it will a be a hu

('FY' || SUBSTR(GB.PERIOD_NAME,5,2)) "YEAR",

SUM(NVL(GB.PERIOD_NET_DR, 0)) - SUM(NVL(GB.PERIOD_NET_CR, 0)) "DATA VALUE"

FROM gl.GL_CODE_COMBINATIONS GCC,

apps.FND_ID_FLEX_STRUCTURES_VL FST,

apps.gl_sets_of_books GSOB,

gl.gl_balances GB

WHERE FST.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID

and GSOB.CHART_OF_ACCOUNTS_ID=GCC.CHART_OF_ACCOUNTS_ID

and GB.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND FST.APPLICATION_ID = 101

AND FST.ID_FLEX_CODE = 'GL#'

and GCC.DETAIL_BUDGETING_ALLOWED_FLAG ='Y'

and GCC.DETAIL_POSTING_ALLOWED_FLAG='Y'
and GCC.ENABLED_FLAG='Y'

and GCC.SUMMARY_FLAG='N'

group by GCC.SEGMENT1, GCC.SEGMENT2, GCC.SEGMENT3, GCC.SEGMENT4, GCC.SEGMENT5, GCC.SEGMENT6, 'Actual', 'Fina

ORDER BY 1,2,3 ;
E,1,3), ('FY' || SUBSTR(GB.PERIOD_NAME,5,2)), GCC.START_DATE_ACTIVE
SEGMENT1 SEGMENT2 SEGMENT3 SEGMENT4 SEGMENT5 SEGMENT6
1 02 01 000000 0001 52319 0000
2 02 01 000000 0001 52319 0000
3 02 01 000000 0001 52319 0000
4 02 01 000000 0001 52319 0000
5 02 01 000000 0001 52319 0000
6 02 01 000000 0001 52319 0000
7 02 01 000000 0001 52319 0000
8 02 01 000000 0001 52319 0000
9 02 01 000000 0001 52319 0000
10 02 01 000000 0001 52319 0000
11 02 01 000000 0001 52319 0000
13 02 01 000000 0001 52341 0000
14 02 01 000000 0001 52341 0000
15 02 01 000000 0001 52341 0000
17 02 01 000000 0001 52341 0000
SCENARIO VERSION PERIOD YEAR DATA VALUE
Actual Final Adj FY10 0
Actual Final Apr FY10 0
Actual Final Aug FY10 100
Actual Final Dec FY10 100
Actual Final Jul FY10 0
Actual Final Jun FY10 100
Actual Final Mar FY10 100
Actual Final May FY10 0
Actual Final Nov FY10 0
Actual Final Oct FY10 0
Actual Final Sep FY10 0
Actual Final Adj FY10 0
Actual Final Apr FY10 0
Actual Final Aug FY10 1000
Actual Final Dec FY10 0
Child - Parent relationship in a value set -- imp for metadata load to Planning

SELECT v.flex_value,
h.parent_flex_value,

v.description alias,

v.summary_flag,

h.range_attribute

--decode(h.range_attribute,'C','Child','P','Parent') "Parent/Child"

FROM apps.fnd_flex_values_vl v,

apps.fnd_flex_value_norm_hierarchy h,

apps.fnd_flex_value_sets s

WHERE h.flex_value_set_id = v.flex_value_set_id

AND s.flex_value_set_id = v.flex_value_set_id

AND h.range_attribute IN ('P','C')

--and h.FLEX_VALUE_SET_ID=1025247 ; -- this is the i/p parameter


FLEX_VALUE PARENT_FLEX_VALUE ALIAS SUMMARY_FLAG RANGE_ATTRIBUTE
1 D10000000 D10000000 Common Applications Y P
2 D10000000 D10000000 Common Applications Y P
3 D10000000 D10000000 Common Applications Y P
4 D10000000 D10000000 Common Applications Y P
5 D10000000 D10000000 Common Applications Y P
6 D10000000 D10000000 Common Applications Y P
7 D10000000 D10000000 Common Applications Y P
8 D10000000 D10000000 Common Applications Y P
9 D10000000 D10000000 Common Applications Y P
10 D10000000 D10000000 Common Applications Y P
11 D10000000 D10100000 Common Applications Y P
12 D10000000 D10100000 Common Applications Y P
13 D10000000 D10100000 Common Applications Y P
14 D10000000 D10100000 Common Applications Y P
15 D10000000 D10100000 Common Applications Y P
16 D10000000 D10100000 Common Applications Y P
17 D10000000 D10200000 Common Applications Y P

You might also like