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

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

Period Status script

GL : Period Status script:-
-----------------------------

GL PERIOD STATUSES

Two small scripts for reviewing open periods across multipel books. ( Month end close checking or automated alerts )
and period status for a given year and book.

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

select sob.short_name
, ps.period_name
, ps.show_status
, ps.start_date||' to '||ps.end_date
, ps.period_year
, ps.period_num
from GL_PERIOD_STATUSES_V ps, GL_SETS_OF_BOOKS sob
where ps.set_of_Books_id = sob.SET_OF_BOOKS_ID
and application_id = 101
--and period_year = 2008
--and substr(sob.short_name,1,2) in ('hb')
and ps.show_status not in ('Never Opened')
order by 1,5,6 desc

Second Script

select sob.short_name
, ps.period_name
, ps.start_date
, ps.end_date
, ps.period_year
, ps.period_num
, ps.show_status
from GL_PERIOD_STATUSES_V ps, GL_SETS_OF_BOOKS sob
where ps.set_of_Books_id = sob.SET_OF_BOOKS_ID
and application_id = 101
and period_year = 2006
--and substr(sob.short_name,1,2) in ('hb')
order by 1,5,6 desc

Mass Allocation Formula review script

GL : Mass Allocation Formula review script:-
---------------------------------------------------

MASS ALLOCATION FORMULA REVIEW SCRIPT

Will show the definition of mass allocation batches and lines across multiple books in an easy to read format for review in excel
It has been written for a 10 segment CoA but can be modified to suit different structures.

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

select fst.ID_FLEX_STRUCTURE_NAME"Chart of Accounts"
, gab.VALIDATION_STATUS "Valid?"
, gab.name "Allocation Name"
, gaf.name "Formula Name"
, gaf.full_allocation_flag"Full?"
--, gaf.validation_status"Valid?"
, gafl.line_number"Line #"
, DECODE(gafl.line_number,1,'A',2,'B',3,'C',4,'T',5,'O','XXX')"Line"
, gafl.AMOUNT"Amount"
, gafl.currency_code "Curr"
, gafl.SEGMENT1||'-'||gafl.SEGMENT2||'-'||gafl.SEGMENT3||'-'||gafl.SEGMENT4||'-'||gafl.SEGMENT5||'-'||gafl.SEGMENT6
||'-'||gafl.SEGMENT7||'-'||gafl.SEGMENT8||'-'||gafl.SEGMENT9||'-'||gafl.SEGMENT10 "Account"
, gafl.segment_types_key "Segment"
, gafl.relative_period"Period"
--, gafl.transaction_currency"Curr"
, gafl.actual_flag"Actual?"
, gafl.amount_type"Amt Type"
from GL_ALLOC_BATCHES gab, GL_ALLOC_FORMULAS gaf, GL_ALLOC_FORMULA_LINES gafl
, FND_ID_FLEX_STRUCTURES_VL fst
where gab.allocation_batch_id = gaf.allocation_batch_id
and gab.CHART_OF_ACCOUNTS_ID = fst.id_flex_num
and gaf.allocation_formula_id = gafl.allocation_formula_id
--and substr(fst.ID_FLEX_STRUCTURE_CODE,1,2) in ('HB')
order by 1,3,4,6

Journal Lines With AP Source Reference Fields

GL : Journal Lines With AP Source Reference Fields:-
-------------------------------------------------------------

GL JOURNAL LINES WITH AP SOURCE REFERENCE FIELDS

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

SELECT SOB.SHORT_NAME"BOOK"
, GLS.USER_JE_SOURCE_NAME"SOURCE"
, GLC.USER_JE_CATEGORY_NAME"CATEGORY"
, GJB.NAME"BATCH NAME"
, GJH.NAME"JOURNAL NAME"
, GJH.CURRENCY_CODE"CURRENCY"
, GJL.JE_LINE_NUM"JRNL LINE#"
, GJL.EFFECTIVE_DATE"ACCOUNTING DATE"
, GJH.PERIOD_NAME"PERIOD"
, GJH.DATE_CREATED"CREATED DATE"
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 "ACCOUNT COMBINATION"
, GJL.ENTERED_DR
, GJL.ENTERED_CR
, GJL.ACCOUNTED_DR
, GJL.ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_RATE"CONV RATE"
, GJH.CURRENCY_CONVERSION_DATE"CONV DATE"
, GJH.CURRENCY_CONVERSION_TYPE"CONV TYPE"
, GJL.DESCRIPTION
, GJL.REFERENCE_1"AP VAND NAME"
, GJL.REFERENCE_2"AP INV_ID"
, GJL.REFERENCE_3"AP INV LINE#CHEQUEID"
, GJL.REFERENCE_4"AP PAYDOC#"
, GJL.REFERENCE_5"AP INVOICE #"
, GJL.REFERENCE_6"AP ACCOUNTING TYPE"
, GJL.REFERENCE_7"AP SOURCE ID"
, GJL.REFERENCE_8"AP NA"
, GJL.REFERENCE_9"AP DOCUMENT ID"
, GJL.REFERENCE_10"AP LINE TYPE"
FROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH, GL_JE_LINES GJL,GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB,
GL_JE_SOURCES GLS, GL_JE_CATEGORIES GLC
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND GJH.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND GLS.JE_SOURCE_NAME = GJH.JE_SOURCE
AND GLC.JE_CATEGORY_NAME = GJH.JE_CATEGORY
AND GLS.USER_JE_SOURCE_NAME = 'Payables'
and GJH.PERIOD_NAME = 'JUL-04'
--and sob.set_of_books_id = 17
order by 1,2,3,4,5,7

Journal Line Based Trial Balance Report

GL: Journal Line Based Trial Balance Report:-
-----------------------------------------------------

GL JOURNAL BASED TRIAL BALANCE

Creates a trial balance based on Journal Lines. Can be used for nervous data conversion managers as you can see the impact
of journals on account balances without the need to post the journals.

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

SELECT SOB.SHORT_NAME
, SOB.NAME
, GJH.NAME
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9 "ACCOUNT"
, GJH.CURRENCY_CODE
, SUM(GJL.ACCOUNTED_DR)"DR"
, SUM(GJL.ACCOUNTED_CR)"CR"
, SUM( NVL(GJL.ACCOUNTED_DR,0) - NVL(GJL.ACCOUNTED_CR,0))"END BALANCE"
, GJL.PERIOD_NAME
FROM GL_JE_LINES GJL
, GL_JE_HEADERS GJH
, GL_CODE_COMBINATIONS GCC
, GL_SETS_OF_BOOKS SOB
WHERE GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJL.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID
AND SOB.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID
AND SOB.SET_OF_BOOKS_ID = GJL.SET_OF_BOOKS_ID
AND GJL.PERIOD_NAME = 'JUL-08'
--AND SOB.SHORT_NAME = 'HBC'
--AND GJH.NAME LIKE '%PPL%'
--AND GCC.SEGMENT1 = '85'
--AND GCC.SEGMENT2 = '70'
--AND GCC.SEGMENT3 = '0000'
--AND GCC.SEGMENT4 = '88165'
--AND GJH.STATUS = 'P'
--AND GJL.EFFECTIVE_DATE >= TO_DATE('06/04/2002','DD/MM/YYYY')
--AND GJL.EFFECTIVE_DATE <= TO_DATE('30/11/2002','DD/MM/YYYY')
GROUP BY SOB.SHORT_NAME, SOB.NAME, GJH.NAME
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9
,GJH.CURRENCY_CODE, GJL.PERIOD_NAME

Journal Header Summary

GL : Journal Header Summary:-
------------------------------------

GL Journal Header Summary

Summary listing of journal header records by category and source across multiple sets of books.

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

SELECT sob.SHORT_NAME"Book"
, gjh.STATUS
, gjh.POSTED_DATE
, gjh.CREATION_DATE
, gls.user_je_source_name"Source"
, glc.user_je_category_name"Category"
, gjh.PERIOD_NAME"Period"
, gjb.NAME"Batch Name"
, gjh.NAME"Journal Name"
, gjh.currency_code"Currency"
FROM GL_JE_BATCHES gjb, GL_JE_HEADERS gjh,GL_SETS_OF_BOOKS sob,
GL_JE_SOURCES gls, GL_JE_CATEGORIES glc
WHERE gjb.JE_BATCH_ID = gjh.JE_BATCH_ID
AND gjh.SET_OF_BOOKS_ID = sob.set_of_books_id
AND gls.JE_SOURCE_NAME = gjh.JE_SOURCE
AND glc.JE_CATEGORY_NAME = gjh.JE_CATEGORY
--and gjh.NAME = 'XYZ' -- JOURNAL NAME
--and gls.user_je_source_name like '%Mass%' -- JOURNAL SOURCE
--AND glc.user_je_category_name= 'Adjustment' -- JOURNAL CATEGORY
--and gjh.PERIOD_NAME in ('MAY-06') -- JOURNAL PERIOD
and (trunc(gjh.creation_date) >= to_date('01/07/2002','dd/mm/yyyy')
or trunc(gjh.posted_date) >= to_date('01/07/2002','dd/mm/yyyy'))
--and substr(sob.short_name,1,2) in ('HB')
order by 1,2 desc,3,4,5,7