General Ledger Useful SQL Scripts – Oracle Applications 11i

               

General Ledger Useful SQL Scripts – Oracle Applications 11i

Contents

GL Set of Books Configuration Overview                                                                                                       1

GL Summary Account Template Definition Review                                                                                       2

GL Segment Value Listing                                                                                                                              3

GL Period Status                                                                                                                                             3

GL Chart of Accounts Structure                                                                                                                      4

GL Chart of Accounts Structure Overview                                                                                                     4

GL Journal Header Summary                                                                                                                          5

GL Journal Line Based Trial Balance Report                                                                                                  5

GL Journal Lines With AP Source Reference Fields                                                                                       6

GL Mass Allocation Rule Migration Script in Dataload Classic Format                                                        7

GL Balances and Movements                                                                                                                        8

GL Chart of Account Segment Hierarchy Ranges                                                                                          9

GL Code Combinations CCIDs                                                                                                                        9

GL CVR Cross Validation Rule Detail Listing                                                                                               10

GL CVR Cross Validation Rule Overview                                                                                                     11

GL Flexfield Security Rule Assignments                                                                                                      11

GL Flexfield Security Rule Definitions                                                                                                         11

GL FSG Report and Components Overview                                                                                                  12

GL Interface Details                                                                                                                                     13

GL Interface Summary                                                                                                                                  13

GL Mass Allocation Formula review script                                                                                                  14

GL Mass Allocation Migration Script in Dataload Professional FLD format                                                14

GL Mass Allocation Rule Migration Script in Dataload Classic Format                                                      17

GL ADI Journal Balances script                                                                                                                    18

GL Autopost Definitions                                                                                                                               19

HR Operating Unit and Legal Entity Configuration                                                                                     19

Dataload .dld GL Cross Validation Rules                                                                                                     20

Dataload Professional .fld -- Daily Rates load                                                                                            21

GL Set of Books Configuration Overview

/* SET OF BOOKS CONFIGURATION OVERVIEW

WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007

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. */

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 ('BE','LU','ES','IT','HU','CZ','PL','RU')

ORDER BY 2

GL Summary Account Template Definition Review

/* GL SUMMARY TEMPLATE DEFINITIONS

WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED, COPYRIGHT 2007

SMALL SCRIPT SHOWING SUMMARY TEMPLATE CONFIGURATION ACROSS MULTIPLE BOOKS,

(TESTED ON VISION 11.5.10.2 JUL-2007 ) */

SELECT SOB.NAME

,      ST.TEMPLATE_NAME

,      ST.CONCATENATED_DESCRIPTION

,      ST.ACCOUNT_CATEGORY_CODE"CAT"

,      ST.START_ACTUALS_PERIOD_NAME "FROM"

,      ST.SEGMENT1_TYPE||'-'||ST.SEGMENT2_TYPE||'-'||ST.SEGMENT3_TYPE||'-'||ST.SEGMENT4_TYPE||'-'||ST.SEGMENT5_TYPE||'-'||

       ST.SEGMENT6_TYPE||'-'||ST.SEGMENT7_TYPE||'-'||ST.SEGMENT8_TYPE||'-'||ST.SEGMENT9_TYPE||'-'||ST.SEGMENT10_TYPE "SEGMENT TYPE"

FROM GL_SUMMARY_TEMPLATES ST, GL_SETS_OF_BOOKS SOB

WHERE ST.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID

--AND SUBSTR(SOB.NAME,1,2) IN ('ES','BE','LU')

GL Segment Value Listing

/* SEGMENT VALUE SET LISTINGS

WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007

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.

( TESTED ON VISION 11.5.10.2  JUNE 2007 )*/

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"

--SELECT DISTINCT FFVS1.FLEX_VALUE_SET_NAME

, 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

GL Period Status

/* GL PERIOD STATUSES

WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED, COPYRIGHT 2007

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.

(TESTED ON VISION 11.5.10.2 JUL-2007 ) */

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 = 2006

--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('ES','LU','BE')

AND PS.SHOW_STATUS NOT IN ('NEVER OPENED')

ORDER BY 1,5,6 DESC

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 ('GB')

ORDER BY 1,5,6 DESC

GL Chart of Accounts Structure

/* CHART OF ACCOUNTS STRUCTURE

Written by Daniel North,  ORAFINAPPS Limited 2007

Gives an overview of the chart of accounts definitions and also status.  

This is used when implementing multiple charts of accounts to ensure consistent setup across countries and between environments.

Where clause can be added or commented out to just look at specific countries. */

SELECT  FST.ID_FLEX_STRUCTURE_NAME

--,    FST.DESCRIPTION

--,    FST.ID_FLEX_NUM

--,    FST.ID_FLEX_STRUCTURE_CODE

,      FST.CROSS_SEGMENT_VALIDATION_FLAG"X-VAL"

,      FST.FREEZE_STRUCTURED_HIER_FLAG"FZ-HIER"

,      FST.FREEZE_FLEX_DEFINITION_FLAG"FZ-DEFN"

,      FSEG.SEGMENT_NUM "SEG#"

,      FSEG.SEGMENT_NAME "SEG NAME"

,      VS.FLEX_VALUE_SET_NAME "VALUE SET"

,      FSEG.FLEX_VALUE_SET_ID"VAL_SET_ID"

,      FSEG.DEFAULT_TYPE"DEF TYPE"

,      FSEG.DEFAULT_VALUE"DEF. VALUE"

,      FSEG.ENABLED_FLAG"ENBLD"

,      FSEG.REQUIRED_FLAG"REQD"

FROM FND_ID_FLEX_STRUCTURES_VL FST, FND_ID_FLEX_SEGMENTS FSEG, FND_FLEX_VALUE_SETS VS