Set of Books Configuration Overview

GL : Set of Books Configuration Overview:-
------------------------------------------------

SET OF BOOKS CONFIGURATION OVERVIEW

This SQL gives an overview of the set of book definitions and can be used when implementing multiple sets of books
to ensure consistent setup across countries and between environments.
Where clause can be added or commented out to just look at specific countries.

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

SELECT SOB.SET_OF_BOOKS_ID "ID"
, SOB.NAME
, SOB.SHORT_NAME
, SOB.DESCRIPTION
, SOB.CHART_OF_ACCOUNTS_ID "COA ID"
, FST.ID_FLEX_STRUCTURE_CODE "CHART OF ACCOUNTS"
, SOB.CURRENCY_CODE "CURR"
, PT.USER_PERIOD_TYPE "PERIOD"
, SOB.PERIOD_SET_NAME
, SOB.FUTURE_ENTERABLE_PERIODS_LIMIT "FUT. PER"
, SOB.LATEST_OPENED_PERIOD_NAME "LATEST OPEN"
, SOB.ATTRIBUTE1"OPERATIONAL BOOK"
, SOB.ATTRIBUTE2"PPL ?"
, SOB.ENABLE_REVAL_SS_TRACK_FLAG||'.'||ENABLE_SECONDARY_TRACK_FLAG"SEC SEG TRACK?"
, RET.SEGMENT1||'-'||RET.SEGMENT2||'-'||RET.SEGMENT3||'-'||RET.SEGMENT4||'-'||RET.SEGMENT5||'-'||RET.SEGMENT6 "RETAINED EARNINGS"
, TRAN.SEGMENT1||'-'||TRAN.SEGMENT2||'-'||TRAN.SEGMENT3||'-'||TRAN.SEGMENT4||'-'||TRAN.SEGMENT5||'-'||TRAN.SEGMENT6 "TRAN EARNINGS"
, '---JOURNALS---'
, SOB.ALLOW_INTERCOMPANY_POST_FLAG"INTERCO?"
, SOB.ENABLE_JE_APPROVAL_FLAG"JRNL APP?"
, SOB.ENABLE_AUTOMATIC_TAX_FLAG"AUTO TAX?"
, SOB.SUSPENSE_ALLOWED_FLAG"SUSP?"
, SOB.TRACK_ROUNDING_IMBALANCE_FLAG"TRK RND?"
, '---AV BAL---'
, SOB.ENABLE_AVERAGE_BALANCES_FLAG||SOB.CONSOLIDATION_SOB_FLAG||SOB.TRANSACTION_CALENDAR_ID||SOB.NET_INCOME_CODE_COMBINATION_ID
||SOB.DAILY_TRANSLATION_RATE_TYPE||SOB.TRANSLATE_EOD_FLAG||SOB.TRANSLATE_QATD_FLAG||SOB.TRANSLATE_YATD_FLAG "NOT USED"
, '---BUDGET CNTL---'
, SOB.ENABLE_BUDGETARY_CONTROL_FLAG||SOB.REQUIRE_BUDGET_JOURNALS_FLAG||SOB.RES_ENCUMB_CODE_COMBINATION_ID "NOT USED"
, '---MRC---'
, SOB.MRC_SOB_TYPE_CODE "NOT USED"
FROM GL_SETS_OF_BOOKS SOB, FND_ID_FLEX_STRUCTURES FST, GL_CODE_COMBINATIONS TRAN, GL_CODE_COMBINATIONS RET, GL_PERIOD_TYPES PT
WHERE FST.ID_FLEX_NUM = SOB.CHART_OF_ACCOUNTS_ID
AND RET.CODE_COMBINATION_ID(+) = SOB.RET_EARN_CODE_COMBINATION_ID
AND TRAN.CODE_COMBINATION_ID(+) = SOB.CUM_TRANS_CODE_COMBINATION_ID
AND PT.PERIOD_TYPE = SOB.ACCOUNTED_PERIOD_TYPE
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN (''HB')
ORDER BY 2

Read More

Segment Value Listing

GL : Segment Value Listing:-
--------------------------------

SEGMENT VALUE SET LISTINGS

Lists single or multiple segment value sets. This is used to perform a QA on chart of accounts values.

Examples of optional where clauses have also been provided below.

SELECT FFVS1.FLEX_VALUE_SET_NAME
--, FFVS1.FLEX_VALUE_SET_ID
, FFVAL1.FLEX_VALUE"VALUE"
, FFVAL1.SUMMARY_FLAG"PARENT ACC ?"
, FFVTL1.DESCRIPTION
, FFVAL1.ENABLED_FLAG
, FH.HIERARCHY_CODE
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),1,1)"BUDGET"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),3,1)"POST"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),5,1)"TYPE"
, SUBSTR(TO_CHAR(FFVAL1.COMPILED_Value_attributes),7,1)"Cntl"
, SUBSTR(TO_CHAR(ffval1.compiled_value_attributes),9,1)"Recon"
, ffval1.LAST_UPDATED_BY
, ffval1.LAST_UPDATE_DATE
FROM FND_FLEX_VALUES ffval1
, FND_FLEX_VALUES_TL ffvtl1
, FND_FLEX_VALUE_SETS ffvs1
, FND_ID_FLEX_SEGMENTS seg
, FND_FLEX_HIERARCHIES_VL fh
WHERE ffval1.FLEX_VALUE_SET_ID(+) = ffvs1.FLEX_VALUE_SET_ID
AND seg.FLEX_VALUE_SET_ID = ffvs1.FLEX_VALUE_SET_ID
AND seg.ID_FLEX_NUM = 51974 /* CoA ID is needed if segment is chart in multple CoA. Update for you configuration or remove if not applicable. */
AND ffval1.FLEX_VALUE_ID = ffvtl1.FLEX_VALUE_ID(+)
AND ffvs1.FLEX_VALUE_SET_NAME = 'Operations Account'
AND FFVAL1.STRUCTURED_HIERARCHY_LEVEL = FH.HIERARCHY_ID(+)
--AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) != 'N' -- NON-CONTROL ACCOUNTS ONLY
--AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) = 'Y' -- CONTROL ACCOUNTS ONLY
--AND FFVAL1.SUMMARY_FLAG = 'Y'
--AND FFVAL1.FLEX_VALUE >= '8000'
--AND FFVAL1.FLEX_VALUE <= '99999'
--AND FFVTL1.DESCRIPTION LIKE '%FTE%'
--AND FFVAL1.FLEX_VALUE LIKE '16%'
ORDER BY FFVS1.FLEX_VALUE_SET_NAME, FFVAL1.FLEX_VALUE

Read More