General Ledger Useful SQL Scripts \u2013 Oracle Applications 11i<\/p>\n
Contents
\nGL Set of Books Configuration Overview 1
\nGL Summary Account Template Definition Review 2
\nGL Segment Value Listing 3
\nGL Period Status 3
\nGL Chart of Accounts Structure 4
\nGL Chart of Accounts Structure Overview 4
\nGL Journal Header Summary 5
\nGL Journal Line Based Trial Balance Report 5
\nGL Journal Lines With AP Source Reference Fields 6
\nGL Mass Allocation Rule Migration Script in Dataload Classic Format 7
\nGL Balances and Movements 8
\nGL Chart of Account Segment Hierarchy Ranges 9
\nGL Code Combinations CCIDs 9
\nGL CVR Cross Validation Rule Detail Listing 10
\nGL CVR Cross Validation Rule Overview 11
\nGL Flexfield Security Rule Assignments 11
\nGL Flexfield Security Rule Definitions 11
\nGL FSG Report and Components Overview 12
\nGL Interface Details 13
\nGL Interface Summary 13
\nGL Mass Allocation Formula review script 14
\nGL Mass Allocation Migration Script in Dataload Professional FLD format 14
\nGL Mass Allocation Rule Migration Script in Dataload Classic Format 17
\nGL ADI Journal Balances script 18
\nGL Autopost Definitions 19
\nHR Operating Unit and Legal Entity Configuration 19
\nDataload .dld GL Cross Validation Rules 20
\nDataload Professional .fld — Daily Rates load 21<\/p>\n
GL Set of Books Configuration Overview<\/p>\n
\/* SET OF BOOKS CONFIGURATION OVERVIEW
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nTHIS SQL GIVES AN OVERVIEW OF THE SET OF BOOK DEFINITIONS AND CAN BE USED WHEN IMPLEMENTING MULTIPLE SETS OF BOOKS
\nTO ENSURE CONSISTENT SETUP ACROSS COUNTRIES AND BETWEEN ENVIRONMENTS.
\nWHERE CLAUSE CAN BE ADDED OR COMMENTED OUT TO JUST LOOK AT SPECIFIC COUNTRIES. *\/<\/p>\n
SELECT SOB.SET_OF_BOOKS_ID “ID”
\n, SOB.NAME
\n, SOB.SHORT_NAME
\n, SOB.DESCRIPTION
\n, SOB.CHART_OF_ACCOUNTS_ID “COA ID”
\n, FST.ID_FLEX_STRUCTURE_CODE “CHART OF ACCOUNTS”
\n, SOB.CURRENCY_CODE “CURR”
\n, PT.USER_PERIOD_TYPE “PERIOD”
\n, SOB.PERIOD_SET_NAME
\n, SOB.FUTURE_ENTERABLE_PERIODS_LIMIT “FUT. PER”
\n, SOB.LATEST_OPENED_PERIOD_NAME “LATEST OPEN”
\n, SOB.ATTRIBUTE1″OPERATIONAL BOOK”
\n, SOB.ATTRIBUTE2″PPL ?”
\n, SOB.ENABLE_REVAL_SS_TRACK_FLAG||’.’||ENABLE_SECONDARY_TRACK_FLAG”SEC SEG TRACK?”
\n, RET.SEGMENT1||’-‘||RET.SEGMENT2||’-‘||RET.SEGMENT3||’-‘||RET.SEGMENT4||’-‘||RET.SEGMENT5||’-‘||RET.SEGMENT6 “RETAINED EARNINGS”
\n, TRAN.SEGMENT1||’-‘||TRAN.SEGMENT2||’-‘||TRAN.SEGMENT3||’-‘||TRAN.SEGMENT4||’-‘||TRAN.SEGMENT5||’-‘||TRAN.SEGMENT6 “TRAN EARNINGS”
\n, ‘—JOURNALS—‘
\n, SOB.ALLOW_INTERCOMPANY_POST_FLAG”INTERCO?”
\n, SOB.ENABLE_JE_APPROVAL_FLAG”JRNL APP?”
\n, SOB.ENABLE_AUTOMATIC_TAX_FLAG”AUTO TAX?”
\n, SOB.SUSPENSE_ALLOWED_FLAG”SUSP?”
\n, SOB.TRACK_ROUNDING_IMBALANCE_FLAG”TRK RND?”
\n, ‘—AV BAL—‘
\n, SOB.ENABLE_AVERAGE_BALANCES_FLAG||SOB.CONSOLIDATION_SOB_FLAG||SOB.TRANSACTION_CALENDAR_ID||SOB.NET_INCOME_CODE_COMBINATION_ID
\n ||SOB.DAILY_TRANSLATION_RATE_TYPE||SOB.TRANSLATE_EOD_FLAG||SOB.TRANSLATE_QATD_FLAG||SOB.TRANSLATE_YATD_FLAG “NOT USED”
\n, ‘—BUDGET CNTL—‘
\n, SOB.ENABLE_BUDGETARY_CONTROL_FLAG||SOB.REQUIRE_BUDGET_JOURNALS_FLAG||SOB.RES_ENCUMB_CODE_COMBINATION_ID “NOT USED”
\n, ‘—MRC—‘
\n, SOB.MRC_SOB_TYPE_CODE “NOT USED”
\nFROM GL_SETS_OF_BOOKS SOB, FND_ID_FLEX_STRUCTURES FST, GL_CODE_COMBINATIONS TRAN, GL_CODE_COMBINATIONS RET, GL_PERIOD_TYPES PT
\nWHERE FST.ID_FLEX_NUM = SOB.CHART_OF_ACCOUNTS_ID
\nAND RET.CODE_COMBINATION_ID(+) = SOB.RET_EARN_CODE_COMBINATION_ID
\nAND TRAN.CODE_COMBINATION_ID(+) = SOB.CUM_TRANS_CODE_COMBINATION_ID
\nAND PT.PERIOD_TYPE = SOB.ACCOUNTED_PERIOD_TYPE
\n–AND SUBSTR(SOB.SHORT_NAME,1,2) IN (‘BE’,’LU’,’ES’,’IT’,’HU’,’CZ’,’PL’,’RU’)
\nORDER BY 2
\nGL Summary Account Template Definition Review<\/p>\n
\/* GL SUMMARY TEMPLATE DEFINITIONS
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
\nSMALL SCRIPT SHOWING SUMMARY TEMPLATE CONFIGURATION ACROSS MULTIPLE BOOKS,
\n(TESTED ON VISION 11.5.10.2 JUL-2007 ) *\/
\nSELECT SOB.NAME
\n, ST.TEMPLATE_NAME
\n, ST.CONCATENATED_DESCRIPTION
\n, ST.ACCOUNT_CATEGORY_CODE”CAT”
\n, ST.START_ACTUALS_PERIOD_NAME “FROM”
\n, ST.SEGMENT1_TYPE||’-‘||ST.SEGMENT2_TYPE||’-‘||ST.SEGMENT3_TYPE||’-‘||ST.SEGMENT4_TYPE||’-‘||ST.SEGMENT5_TYPE||’-‘||
\n ST.SEGMENT6_TYPE||’-‘||ST.SEGMENT7_TYPE||’-‘||ST.SEGMENT8_TYPE||’-‘||ST.SEGMENT9_TYPE||’-‘||ST.SEGMENT10_TYPE “SEGMENT TYPE”
\nFROM GL_SUMMARY_TEMPLATES ST, GL_SETS_OF_BOOKS SOB
\nWHERE ST.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
\n–AND SUBSTR(SOB.NAME,1,2) IN (‘ES’,’BE’,’LU’)
\nGL Segment Value Listing<\/p>\n
\/* SEGMENT VALUE SET LISTINGS
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nLISTS SINGLE OR MULTIPLE SEGMENT VALUE SETS. THIS IS USED TO PERFORM A QA ON CHART OF ACCOUNTS VALUES.
\nEXAMPLES OF OPTIONAL WHERE CLAUSES HAVE ALSO BEEN PROVIDED BELOW.
\n( TESTED ON VISION 11.5.10.2 JUNE 2007 )*\/
\nSELECT FFVS1.FLEX_VALUE_SET_NAME
\n–, FFVS1.FLEX_VALUE_SET_ID
\n, FFVAL1.FLEX_VALUE”VALUE”
\n, FFVAL1.SUMMARY_FLAG”PARENT ACC ?”
\n, FFVTL1.DESCRIPTION
\n, FFVAL1.ENABLED_FLAG
\n, FH.HIERARCHY_CODE
\n, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),1,1)”BUDGET”
\n, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),3,1)”POST”
\n, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),5,1)”TYPE”
\n, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1)”CNTL”
\n, SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),9,1)”RECON”
\n–SELECT DISTINCT FFVS1.FLEX_VALUE_SET_NAME
\n, FFVAL1.LAST_UPDATED_BY
\n, FFVAL1.LAST_UPDATE_DATE
\nFROM FND_FLEX_VALUES FFVAL1
\n, FND_FLEX_VALUES_TL FFVTL1
\n, FND_FLEX_VALUE_SETS FFVS1
\n, FND_ID_FLEX_SEGMENTS SEG
\n, FND_FLEX_HIERARCHIES_VL FH
\nWHERE FFVAL1.FLEX_VALUE_SET_ID(+) = FFVS1.FLEX_VALUE_SET_ID
\nAND SEG.FLEX_VALUE_SET_ID = FFVS1.FLEX_VALUE_SET_ID
\nAND 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. *\/
\nAND FFVAL1.FLEX_VALUE_ID = FFVTL1.FLEX_VALUE_ID(+)
\nAND FFVS1.FLEX_VALUE_SET_NAME = ‘OPERATIONS ACCOUNT’
\nAND FFVAL1.STRUCTURED_HIERARCHY_LEVEL = FH.HIERARCHY_ID(+)
\n–AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) != ‘N’ — NON-CONTROL ACCOUNTS ONLY
\n–AND SUBSTR(TO_CHAR(FFVAL1.COMPILED_VALUE_ATTRIBUTES),7,1) = ‘Y’ — CONTROL ACCOUNTS ONLY
\n–AND FFVAL1.SUMMARY_FLAG = ‘Y’
\n–AND FFVAL1.FLEX_VALUE >= ‘8000’
\n–AND FFVAL1.FLEX_VALUE <= '99999'\n--AND FFVTL1.DESCRIPTION LIKE '%FTE%'\n--AND FFVAL1.FLEX_VALUE LIKE '16%'\nORDER BY FFVS1.FLEX_VALUE_SET_NAME, FFVAL1.FLEX_VALUE\nGL Period Status\n\n\/* GL PERIOD STATUSES\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007\nTWO SMALL SCRIPTS FOR REVIEWING OPEN PERIODS ACROSS MULTIPEL BOOKS. ( MONTH END CLOSE CHECKING OR AUTOMATED ALERTS )\nAND PERIOD STATUS FOR A GIVEN YEAR AND BOOK.\n(TESTED ON VISION 11.5.10.2 JUL-2007 ) *\/\nSELECT SOB.SHORT_NAME\n, PS.PERIOD_NAME\n, PS.SHOW_STATUS\n, PS.START_DATE||' TO '||PS.END_DATE \n, PS.PERIOD_YEAR\n, PS.PERIOD_NUM\nFROM GL_PERIOD_STATUSES_V PS, GL_SETS_OF_BOOKS SOB\nWHERE PS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID\nAND APPLICATION_ID = 101\n--AND PERIOD_YEAR = 2006\n--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('ES','LU','BE')\nAND PS.SHOW_STATUS NOT IN ('NEVER OPENED')\nORDER BY 1,5,6 DESC\n\nSELECT SOB.SHORT_NAME\n, PS.PERIOD_NAME\n, PS.START_DATE\n, PS.END_DATE \n, PS.PERIOD_YEAR\n, PS.PERIOD_NUM\n, PS.SHOW_STATUS\nFROM GL_PERIOD_STATUSES_V PS, GL_SETS_OF_BOOKS SOB\nWHERE PS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID\nAND APPLICATION_ID = 101\nAND PERIOD_YEAR = 2006\n--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('GB')\nORDER BY 1,5,6 DESC\nGL Chart of Accounts Structure\n\n\/* CHART OF ACCOUNTS STRUCTURE\nWritten by Daniel North, ORAFINAPPS Limited 2007\nGives an overview of the chart of accounts definitions and also status. \nThis is used when implementing multiple charts of accounts to ensure consistent setup across countries and between environments.\nWhere clause can be added or commented out to just look at specific countries. *\/\nSELECT FST.ID_FLEX_STRUCTURE_NAME\n--, FST.DESCRIPTION\n--, FST.ID_FLEX_NUM\n--, FST.ID_FLEX_STRUCTURE_CODE\n, FST.CROSS_SEGMENT_VALIDATION_FLAG\"X-VAL\"\n, FST.FREEZE_STRUCTURED_HIER_FLAG\"FZ-HIER\"\n, FST.FREEZE_FLEX_DEFINITION_FLAG\"FZ-DEFN\"\n, FSEG.SEGMENT_NUM \"SEG#\"\n, FSEG.SEGMENT_NAME \"SEG NAME\"\n, VS.FLEX_VALUE_SET_NAME \"VALUE SET\"\n, FSEG.FLEX_VALUE_SET_ID\"VAL_SET_ID\"\n, FSEG.DEFAULT_TYPE\"DEF TYPE\"\n, FSEG.DEFAULT_VALUE\"DEF. VALUE\"\n, FSEG.ENABLED_FLAG\"ENBLD\"\n, FSEG.REQUIRED_FLAG\"REQD\"\nFROM FND_ID_FLEX_STRUCTURES_VL FST, FND_ID_FLEX_SEGMENTS FSEG, FND_FLEX_VALUE_SETS VS\nWHERE FST.ID_FLEX_NUM = FSEG.ID_FLEX_NUM\nAND FSEG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID\n--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('BE','LU','ES','IT','HU','CZ','PL','RU')\nAND FST.APPLICATION_ID = 101\nAND FST.ID_FLEX_CODE = 'GL#'\nORDER BY 1, FSEG.SEGMENT_NUM\nGL Chart of Accounts Structure Overview\n\n\n\/* CHART OF ACCOUNTS STRUCTURE\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007\nGIVES AN OVERVIEW OF THE CHART OF ACCOUNTS DEFINITIONS AND ALSO STATUS. \nTHIS IS USED WHEN IMPLEMENTING MULTIPLE CHARTS OF ACCOUNTS TO ENSURE CONSISTENT SETUP ACROSS COUNTRIES AND BETWEEN ENVIRONMENTS.\nWHERE CLAUSE CAN BE ADDED OR COMMENTED OUT TO JUST LOOK AT SPECIFIC COUNTRIES. *\/\nSELECT FST.ID_FLEX_STRUCTURE_NAME\n--, FST.DESCRIPTION\n--, FST.ID_FLEX_NUM\n--, FST.ID_FLEX_STRUCTURE_CODE\n, FST.CROSS_SEGMENT_VALIDATION_FLAG\"X-VAL\"\n, FST.FREEZE_STRUCTURED_HIER_FLAG\"FZ-HIER\"\n, FST.FREEZE_FLEX_DEFINITION_FLAG\"FZ-DEFN\"\n, FSEG.SEGMENT_NUM \"SEG#\"\n, FSEG.SEGMENT_NAME \"SEG NAME\"\n, VS.FLEX_VALUE_SET_NAME \"VALUE SET\"\n, FSEG.FLEX_VALUE_SET_ID\"VAL_SET_ID\"\n, FSEG.DEFAULT_TYPE\"DEF TYPE\"\n, FSEG.DEFAULT_VALUE\"DEF. VALUE\"\n, FSEG.ENABLED_FLAG\"ENBLD\"\n, FSEG.REQUIRED_FLAG\"REQD\"\nFROM FND_ID_FLEX_STRUCTURES_VL FST, FND_ID_FLEX_SEGMENTS FSEG, FND_FLEX_VALUE_SETS VS\nWHERE FST.ID_FLEX_NUM = FSEG.ID_FLEX_NUM\nAND FSEG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID\n--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('BE','LU','ES','IT','HU','CZ','PL','RU')\nAND FST.APPLICATION_ID = 101\nAND FST.ID_FLEX_CODE = 'GL#'\nORDER BY 1, FSEG.SEGMENT_NUM\nGL Journal Header Summary\n\n\/* GL JOURNAL HEADER SUMMARY\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007\nSUMMARY LISTING OF JOURNAL HEADER RECORDS BY CATEGORY AND SOURCE ACROSS MULTIPLE SETS OF BOOKS.\n(TESTED ON VISION 11.5.10.2 JUNE 2007 ) *\/\nSELECT SOB.SHORT_NAME\"BOOK\"\n, GJH.STATUS\n, GJH.POSTED_DATE\n, GJH.CREATION_DATE\n, GLS.USER_JE_SOURCE_NAME\"SOURCE\"\n, GLC.USER_JE_CATEGORY_NAME\"CATEGORY\"\n, GJH.PERIOD_NAME\"PERIOD\"\n, GJB.NAME\"BATCH NAME\"\n, GJH.NAME\"JOURNAL NAME\"\n, GJH.CURRENCY_CODE\"CURRENCY\"\nFROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH,GL_SETS_OF_BOOKS SOB,\n GL_JE_SOURCES GLS, GL_JE_CATEGORIES GLC\nWHERE GJB.JE_BATCH_ID = GJH.JE_BATCH_ID\nAND GJH.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID\nAND GLS.JE_SOURCE_NAME = GJH.JE_SOURCE\nAND GLC.JE_CATEGORY_NAME = GJH.JE_CATEGORY\n--AND GJH.NAME = 'QUV-DECLARATION TVA 11\/04' -- JOURNAL NAME \n--AND GLS.USER_JE_SOURCE_NAME LIKE '%MASS%' -- JOURNAL SOURCE\n--AND GLC.USER_JE_CATEGORY_NAME= 'ADJUSTMENT' -- JOURNAL CATEGORY\n--AND GJH.PERIOD_NAME IN ('MAY-06') -- JOURNAL PERIOD\nAND (TRUNC(GJH.CREATION_DATE) >= TO_DATE(’01\/07\/2002′,’DD\/MM\/YYYY’)
\n OR TRUNC(GJH.POSTED_DATE) >= TO_DATE(’01\/07\/2002′,’DD\/MM\/YYYY’))
\n–AND SUBSTR(SOB.SHORT_NAME,1,2) IN (‘DE’)
\nORDER BY 1,2 DESC,3,4,5,7
\nGL Journal Line Based Trial Balance Report<\/p>\n
\/* GL JOURNAL BASED TRIAL BALANCE
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
\nCREATES A TRIAL BALANCE BASED ON JOURNAL LINES. CAN BE USED FOR NERVOUS DATA CONVERSION MANAGERS AS YOU CAN SEE THE IMPACT
\nOF JOURNALS ON ACCOUNT BALANCES WITHOUT THE NEED TO POST THE JOURNALS.
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 ) *\/
\nSELECT SOB.SHORT_NAME
\n, SOB.NAME
\n, GJH.NAME
\n, GCC.SEGMENT1||’-‘||GCC.SEGMENT2||’-‘||GCC.SEGMENT3||’-‘||GCC.SEGMENT4||’-‘||GCC.SEGMENT5||’-‘||GCC.SEGMENT6||’-‘||GCC.SEGMENT7||’-‘||GCC.SEGMENT8||’-‘||GCC.SEGMENT9 “ACCOUNT”
\n, GJH.CURRENCY_CODE
\n, SUM(GJL.ACCOUNTED_DR)”DR”
\n, SUM(GJL.ACCOUNTED_CR)”CR”
\n, SUM( NVL(GJL.ACCOUNTED_DR,0) – NVL(GJL.ACCOUNTED_CR,0))”END BALANCE”
\n, GJL.PERIOD_NAME
\nFROM GL_JE_LINES GJL
\n, GL_JE_HEADERS GJH
\n, GL_CODE_COMBINATIONS GCC
\n, GL_SETS_OF_BOOKS SOB
\nWHERE GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
\nAND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
\nAND GJL.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID
\nAND SOB.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID
\nAND SOB.SET_OF_BOOKS_ID = GJL.SET_OF_BOOKS_ID
\nAND GJL.PERIOD_NAME = ‘JUL-03’
\n–AND SOB.SHORT_NAME = ‘GBMAN’
\n–AND GJH.NAME LIKE ‘%PPL%’
\n–AND GCC.SEGMENT1 = ’85’
\n–AND GCC.SEGMENT2 = ’70’
\n–AND GCC.SEGMENT3 = ‘0000’
\n–AND GCC.SEGMENT4 = ‘88165’
\n–AND GJH.STATUS = ‘P’
\n–AND GJL.EFFECTIVE_DATE >= TO_DATE(’06\/04\/2002′,’DD\/MM\/YYYY’)
\n–AND GJL.EFFECTIVE_DATE <= TO_DATE('30\/11\/2002','DD\/MM\/YYYY')\nGROUP BY SOB.SHORT_NAME, SOB.NAME, GJH.NAME\n, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9\n,GJH.CURRENCY_CODE, GJL.PERIOD_NAME\nGL Journal Lines With AP Source Reference Fields\n\n\/* GL JOURNAL LINES WITH AP SOURCE REFERENCE FIELDS\nWritten by Daniel North, ORAFINAPPS Limited 2007\n(Tested on Vision 11.5.10.2 June 2007 ) *\/\nSELECT SOB.SHORT_NAME\"BOOK\"\n, GLS.USER_JE_SOURCE_NAME\"SOURCE\"\n, GLC.USER_JE_CATEGORY_NAME\"CATEGORY\"\n, GJB.NAME\"BATCH NAME\"\n, GJH.NAME\"JOURNAL NAME\"\n, GJH.CURRENCY_CODE\"CURRENCY\"\n, GJL.JE_LINE_NUM\"JRNL LINE#\"\n, GJL.EFFECTIVE_DATE\"ACCOUNTING DATE\"\n, GJH.PERIOD_NAME\"PERIOD\"\n, GJH.DATE_CREATED\"CREATED DATE\"\n, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6\n ||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 \"ACCOUNT COMBINATION\"\n, GJL.ENTERED_DR\n, GJL.ENTERED_CR\n, GJL.ACCOUNTED_DR\n, GJL.ACCOUNTED_CR\n, GJH.CURRENCY_CONVERSION_RATE\"CONV RATE\"\n, GJH.CURRENCY_CONVERSION_DATE\"CONV DATE\"\n, GJH.CURRENCY_CONVERSION_TYPE\"CONV TYPE\"\n, GJL.DESCRIPTION\n, GJL.REFERENCE_1\"AP VAND NAME\"\n, GJL.REFERENCE_2\"AP INV_ID\"\n, GJL.REFERENCE_3\"AP INV LINE#CHEQUEID\"\n, GJL.REFERENCE_4\"AP PAYDOC#\"\n, GJL.REFERENCE_5\"AP INVOICE #\"\n, GJL.REFERENCE_6\"AP ACCOUNTING TYPE\"\n, GJL.REFERENCE_7\"AP SOURCE ID\"\n, GJL.REFERENCE_8\"AP NA\"\n, GJL.REFERENCE_9\"AP DOCUMENT ID\"\n, GJL.REFERENCE_10\"AP LINE TYPE\"\nFROM GL_JE_BATCHES GJB, GL_JE_HEADERS GJH, GL_JE_LINES GJL,GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB,\n GL_JE_SOURCES GLS, GL_JE_CATEGORIES GLC\nWHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID\nAND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID\nAND GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID\nAND GJH.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID\nAND GLS.JE_SOURCE_NAME = GJH.JE_SOURCE\nAND GLC.JE_CATEGORY_NAME = GJH.JE_CATEGORY\nAND GLS.USER_JE_SOURCE_NAME = 'Payables'\nand GJH.PERIOD_NAME = 'JUL-04'\n--and sob.set_of_books_id = 87\norder by 1,2,3,4,5,7\n\nGL Mass Allocation Rule Migration Script in Dataload Classic Format\n\n\/* MASS ALLOCATION MIGRATION - DATALOAD CLASSIC LAYOUT\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007\nCREATES A PRE-FORMATED SPREADSHEET LAYOUT TO MIGRATE MASS ALLOCATIONS BETWEEN ENVIRONMENTS AND\/OR BOOKS USING DATALOAD CLASSIC.\nIT HAS BEEN WRITTEN FOR A 10 SEGMENT COA BUT CAN BE MODIFIED TO SUIT DIFFERENT STRUCTURES.\n(TESTED ON VISION 11.5.9 JAN-2007 ) *\/\nSELECT SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2)\"BOOK\"\n, GAB.NAME \"ALLOCATION NAME\"\n\/*, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN GAB.NAME ELSE NULL END )\"ALLOCATION NAME\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )\"TAB\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'A' ELSE NULL END )\"A\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )\"TAB\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN GAB.DESCRIPTION ELSE NULL END )\"ALLOC DESCRIPTION\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN '*AR' ELSE NULL END )\"TAB\"\n,*\/ \n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN '\\'||GAF.NAME ELSE NULL END )\"FORMULA NAME\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )\"TAB\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'ALLOCATION' ELSE NULL END )\"ALLOCATION\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )\"TAB\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN GAF.DESCRIPTION ELSE NULL END )\"FORMULA DESC\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )\"TAB\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )\"TAB\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN '*SB' ELSE NULL END )\"FCP\"\n, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )\"TAB\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN (CASE WHEN GAFL.LINE_NUMBER IN (1,2,3,4) THEN 'TAB' ELSE NULL END )ELSE NULL END )\"TAB\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT1 ELSE '\\'||TO_CHAR(GAFL.AMOUNT) END )\"1\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,0,1) ELSE NULL END )\"1T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT2 ELSE NULL END )\"2\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)ELSE NULL END )\"2T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT3 ELSE NULL END )\"3\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)ELSE NULL END )\"3T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT4 ELSE NULL END )\"4\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)ELSE NULL END )\"4T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT5 ELSE NULL END )\"5\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)ELSE NULL END )\"5T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT6 ELSE NULL END )\"6\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)ELSE NULL END )\"6T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT7 ELSE NULL END )\"7\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)ELSE NULL END )\"7T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT8 ELSE NULL END )\"8\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)ELSE NULL END )\"8T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT9 ELSE NULL END )\"9\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)ELSE NULL END )\"9T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||GAFL.SEGMENT10 ELSE NULL END )\"10\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN '\\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)ELSE NULL END )\"10T\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2,3,4,5) THEN 'ENT' ELSE NULL END )ELSE NULL END )\"TAB1\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2,3,4) THEN GAFL.CURRENCY_CODE ELSE NULL END )ELSE NULL END )\"CURR\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2,3,4) THEN 'TAB' ELSE NULL END )ELSE NULL END)\"TAB2\"\n, (CASE WHEN GAFL.AMOUNT IS NOT NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (2) THEN 'TAB' ELSE NULL END )ELSE NULL END)\"TAB2\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2,3) THEN GAFL.AMOUNT_TYPE ELSE NULL END )ELSE NULL END )\"PTD\/YTD\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (1,2) THEN '\\{TAB 3}' ELSE (CASE WHEN GAFL.LINE_NUMBER IN (3) THEN '\\{TAB 2}' ELSE NULL END) END)ELSE NULL END )\"TAB3\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*SAVE' ELSE NULL END )ELSE NULL END )\"*SAVE\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*PB' ELSE NULL END )ELSE NULL END )\"*PB\"\n, (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*NR' ELSE NULL END )ELSE NULL END )\"*NR\"\nFROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES GAFL, FND_ID_FLEX_STRUCTURES_VL FST\nWHERE GAB.ALLOCATION_BATCH_ID = GAF.ALLOCATION_BATCH_ID\nAND GAB.CHART_OF_ACCOUNTS_ID = FST.ID_FLEX_NUM\nAND GAF.ALLOCATION_FORMULA_ID = GAFL.ALLOCATION_FORMULA_ID\n--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('DE')\nORDER BY 1,GAB.NAME, GAF.NAME, GAFL.LINE_NUMBER\n\nGL Balances and Movements\n\n\/* GL BALANCES & MOVEMENTS\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007\nGIVES A TRIAL BALANCE WITH OPENING, MOVEMENT AND CLOSING BALANCES FOR UPTO TEN SEGMENTS IN THE CHART OF ACCOUNTS BY CURRENCY.\nTHIS CAN BE USED TO AS A QUICK METHOD OF RUNNING A TRIAL BALANCE FOR DATA EXTRACT IN THE DESIRED FORMAT. \nFOR EXAMPLE TO USE TO EXTRACT TO A THIRD PARTY REPORTING SYSTEM SUCH AS HYPERION\nIT IS RECOMMENDED THAT THIS SCRIPT IS RUN FOR A SINGLE PERIOD AND BOOK FIRST TO GAUGE PERFORMANCE IN YOUR ENVIRONMENT.\n(TESTED ON VISION 11.5.10.2 JUNE 2007 ) *\/\nSELECT SOB.NAME\n, GB.ACTUAL_FLAG\n, GB.PERIOD_NAME\n, GCC.CODE_COMBINATION_ID\n, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6\n ||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 \"DISTRIBUTION\"\n,SUM( NVL(GB.BEGIN_BALANCE_DR,0) - NVL(GB.BEGIN_BALANCE_CR,0))\"OPEN BAL\"\n,NVL(GB.PERIOD_NET_DR,0) \"DEBIT\"\n,NVL(GB.PERIOD_NET_CR,0) \"CREDIT\"\n,SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))\"NET MOVEMENT\"\n,SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0))\"CLOSE BAL\"\n, GB.CURRENCY_CODE\n, GB.TRANSLATED_FLAG\n, GB.TEMPLATE_ID\nFROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB\nWHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID\nAND GB.ACTUAL_FLAG = 'A'\nAND GB.CURRENCY_CODE = SOB.CURRENCY_CODE\nAND GB.TEMPLATE_ID IS NULL\nAND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID\nAND GB.PERIOD_NAME = 'APR-04'\nAND SUBSTR(SOB.SHORT_NAME,1,2) IN ('PR')\n--AND GCC.SEGMENT1 = '85'\n--AND GCC.SEGMENT2 = '70'\n--AND GCC.SEGMENT3 = '0000'\n--AND GCC.SEGMENT4 IN ('99659')\n--AND GCC.SEGMENT7 = 'T'\n--AND NVL(GB.TRANSLATED_FLAG,'X') != 'R'\nGROUP BY SOB.NAME\n, GB.ACTUAL_FLAG\n, GB.PERIOD_NAME\n, GCC.CODE_COMBINATION_ID\n, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6\n ||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10\n, NVL(GB.PERIOD_NET_DR,0)\n, NVL(GB.PERIOD_NET_CR,0)\n, GB.CURRENCY_CODE\n, GB.TRANSLATED_FLAG\n, GB.TEMPLATE_ID\nHAVING SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0)) <> 0
\nGL Chart of Account Segment Hierarchy Ranges<\/p>\n
\/* GL : CCHART OF ACCOUNT SEGMENT HIERARCHY RANGES
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nCHART OF ACCOUNT SEGMENT HIERARCHY RANGES AND ATTRIBUTES FOR PARENT ACCOUNTS
\n*\/
\nSELECT FVS.FLEX_VALUE_SET_NAME”VALUE SET”
\n, FV.FLEX_VALUE
\n, NH.PARENT_FLEX_VALUE “PARENT”
\n, FVT.DESCRIPTION
\n, NH.RANGE_ATTRIBUTE “INC C OR P?”
\n, NH.CHILD_FLEX_VALUE_LOW “FROM”
\n, NH.CHILD_FLEX_VALUE_HIGH “TO”
\n, NH.PARENT_FLEX_VALUE || ‘ : ‘ ||NH.RANGE_ATTRIBUTE || ‘ : ‘ ||
\n NH.CHILD_FLEX_VALUE_LOW || ‘ -> ‘ ||NH.CHILD_FLEX_VALUE_HIGH “HIERARCHY RANGE”
\n, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,1,1)”POSTING”
\n, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,3,1)”BUDGETING”
\n, SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,5,1)”ACC TYPE”
\n, FV.ENABLED_FLAG”ENABLED”
\n, FV.SUMMARY_FLAG”PARENT?”
\n, NH.LAST_UPDATE_DATE
\n, FV.HIERARCHY_LEVEL”LEVEL”
\nFROM FND_FLEX_VALUE_NORM_HIERARCHY NH, FND_FLEX_VALUE_SETS FVS, FND_FLEX_VALUES_TL FVT, FND_FLEX_VALUES FV
\nWHERE FVS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
\nAND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
\nAND FV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
\nAND NH.PARENT_FLEX_VALUE(+) = FVT.FLEX_VALUE_MEANING
\nAND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
\nAND FVS.FLEX_VALUE_SET_NAME LIKE ‘%ACCOUNT%’ — CHART OF ACCOUNTS SEGMENT NAME
\n— AND SUBSTR(FVS.FLEX_VALUE_SET_NAME,4,2) IN (‘BE’,’LU’,’ES’)
\nAND FV.SUMMARY_FLAG = ‘Y’
\nAND FV.FLEX_VALUE LIKE ‘%XYZ%’ — THIS IS THE PARENT SEGMENT VALUES
\n— AND NH.PARENT_FLEX_VALUE = ‘%%’
\n— AND FV.ENABLED_FLAG = ‘Y’
\n— AND FV.HIERARCHY_LEVEL = ‘2’
\nORDER BY 1,3
\nGL Code Combinations CCIDs<\/p>\n
\/* GL CODE COMBINATIONS
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nGL CODE COMBINATIONS EXTRACT. CAN BE SELECT BY CHART OF ACCOUNTS, SPECIFIC SEGMENT VALUES OR SPECIFIC CODE COMBINATION ATTRIBUTES.
\nTHIS CAN BE USED FOR CHART OF ACCOUNTS MAINTENANCE AND REVIEW
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 )*\/
\nSELECT FST.ID_FLEX_STRUCTURE_NAME
\n, GCC.SEGMENT1||’-‘||GCC.SEGMENT2||’-‘||GCC.SEGMENT3||’-‘||GCC.SEGMENT4||’-‘||GCC.SEGMENT5||’-‘||GCC.SEGMENT6
\n, GCC.CODE_COMBINATION_ID
\n, GCC.LAST_UPDATE_DATE
\n, GCC.JGZZ_RECON_FLAG
\n, GCC.START_DATE_ACTIVE
\n, GCC.END_DATE_ACTIVE
\n, GCC.DETAIL_POSTING_ALLOWED_FLAG
\n, GCC.ENABLED_FLAG
\n, GCC.SUMMARY_FLAG
\n, GCC.START_DATE_ACTIVE
\nFROM GL_CODE_COMBINATIONS GCC
\n, FND_ID_FLEX_STRUCTURES_VL FST
\nWHERE FST.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
\nAND FST.APPLICATION_ID = 101
\nAND FST.ID_FLEX_CODE = ‘GL#’
\n–AND GCC.SEGMENT1 IN (’25’,’26’,’30’)
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN (‘ES’,’BE’,’LU’)
\n–AND GCC.SEGMENT4 = ‘99901’
\nORDER BY 1,2,3
\nGL CVR Cross Validation Rule Detail Listing<\/p>\n
\/* CVR CROSS VALIDATION RULE DETAIL LISTING
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nPROVIDES DETAIL VIEW OF CVR DEFINITION INCLUDING ACCOUNT RANGES.
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 )*\/
\nSELECT FST.ID_FLEX_STRUCTURE_NAME
\n, R.FLEX_VALIDATION_RULE_NAME
\n, R.ENABLED_FLAG
\n–, R.ERROR_SEGMENT_COLUMN_NAME”ERR SEG”
\n–, TL.DESCRIPTION
\n–, TL.ERROR_MESSAGE_TEXT”ERROR MESSAGE”
\n, L.ENABLED_FLAG
\n, L.INCLUDE_EXCLUDE_INDICATOR”INC?”
\n, L.CONCATENATED_SEGMENTS_LOW “FROM”
\n, L.CONCATENATED_SEGMENTS_HIGH “TO”
\n, L.LAST_UPDATED_BY
\n, L.LAST_UPDATE_DATE
\nFROM FND_FLEX_VALIDATION_RULES R,
\n FND_FLEX_VDATION_RULES_TL TL,
\n FND_FLEX_VALIDATION_RULE_LINES L,
\n FND_ID_FLEX_STRUCTURES_VL FST
\nWHERE R.APPLICATION_ID = TL.APPLICATION_ID
\nAND FST.ID_FLEX_NUM = R.ID_FLEX_NUM
\nAND R.ID_FLEX_CODE = TL.ID_FLEX_CODE
\nAND R.ID_FLEX_NUM = TL.ID_FLEX_NUM
\nAND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
\nAND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
\nAND R.APPLICATION_ID = L.APPLICATION_ID
\nAND R.ID_FLEX_CODE = L.ID_FLEX_CODE
\nAND R.ID_FLEX_NUM = L.ID_FLEX_NUM
\nAND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
\nAND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
\nAND R.APPLICATION_ID = 101
\n— OPTIONAL FILTERS BELOW TO LIMIT QUERY TO SPECIFIC CVR OR LINES
\n–AND R.ERROR_SEGMENT_COLUMN_NAME = ‘SEGMENT5’
\n–AND TL.ERROR_MESSAGE_TEXT LIKE ‘%PLEASE USE A VALID R%’
\n–AND R.FLEX_VALIDATION_RULE_NAME LIKE ‘BE GROUP ERROR%’
\n–AND TL.ERROR_MESSAGE_TEXT LIKE ‘%94005%’
\n–AND L.INCLUDE_EXCLUDE_INDICATOR = ‘E’
\nORDER BY 1,R.FLEX_VALIDATION_RULE_NAME, L.INCLUDE_EXCLUDE_INDICATOR DESC, L.CONCATENATED_SEGMENTS_LOW<\/p>\n
GL CVR Cross Validation Rule Overview<\/p>\n
\/* CVR OVERVIEW (CROSS VALIDATION RULES )
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nPROVIDES VIEW OF HEADER LEVEL CROSS VALIDATION RULE DEFINITIONS TO
\nOBTAIN AN OVERVIEW OF RULES AND MESSAGES ACROSS MULTIPLE CHARTS OF ACCOUNTS
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 )*\/
\nSELECT FST.ID_FLEX_STRUCTURE_NAME”COA”
\n, R.FLEX_VALIDATION_RULE_NAME”RULE NAME”
\n, R.ENABLED_FLAG”ENB?”
\n, R.ERROR_SEGMENT_COLUMN_NAME”ERROR SEG”
\n, LENGTH(TL.ERROR_MESSAGE_TEXT)”ERROR LENGTH”
\n, TL.ERROR_MESSAGE_TEXT”MESSAGE”
\n, TL.CREATION_DATE
\n–SELECT COUNT(*), FST.ID_FLEX_STRUCTURE_NAME
\nFROM FND_FLEX_VALIDATION_RULES R,
\n FND_FLEX_VDATION_RULES_TL TL,
\n FND_ID_FLEX_STRUCTURES_VL FST
\nWHERE R.APPLICATION_ID = TL.APPLICATION_ID
\nAND FST.ID_FLEX_NUM = R.ID_FLEX_NUM
\nAND R.ID_FLEX_CODE = TL.ID_FLEX_CODE
\nAND R.ID_FLEX_NUM = TL.ID_FLEX_NUM
\nAND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
\nAND R.APPLICATION_ID = 101
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN (‘BE’,’LU’,’ES’) — LIMITS RESULTS TO SPECIFIC CHARTS OF ACCOUNTS
\n–AND LENGTH(TL.ERROR_MESSAGE_TEXT) > 150 — THIS IS USED FOR CHECK FOR MESSAGES OVER 150 CHARACTERS THAT CAN CAUSE SQL ERRORS IN I-EXPENSES
\nORDER BY 1,2
\nGL Flexfield Security Rule Assignments<\/p>\n
\/* FLEXFIELD SECURITY RULE ASSIGNMENTS TO RESPONSIBILITIES
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nLISTS SECURITY RULE ASSIGNMENTS TO RESPONSIBILITIES
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 )*\/
\nSELECT A.APPLICATION_NAME
\n, FVR.FLEX_VALUE_RULE_NAME
\n, R.RESPONSIBILITY_KEY
\nFROM FND_FLEX_VALUE_RULES FVR,
\nFND_FLEX_VALUE_RULE_USAGES RU, FND_RESPONSIBILITY R, FND_APPLICATION_TL A
\nWHERE FVR.FLEX_VALUE_RULE_ID = RU.FLEX_VALUE_RULE_ID
\nAND RU.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
\nAND RU.APPLICATION_ID = A.APPLICATION_ID
\nAND FVR.FLEX_VALUE_RULE_NAME LIKE ‘%’
\nORDER BY FLEX_VALUE_RULE_NAME
\nGL Flexfield Security Rule Definitions<\/p>\n
\/* FLEXFIELD SECURITY RULE DEFINITIONS
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nLISTS SECURITY RULE DEFINITIONS AND GL ACCOUNT RANGES
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 )*\/
\nSELECT A.APPLICATION_NAME “APPS” ,
\nFS.SEGMENT_NAME ,
\nFVS.FLEX_VALUE_SET_NAME ,
\n FVR.FLEX_VALUE_RULE_NAME ,
\nFVR.PARENT_FLEX_VALUE_LOW “PRNT L” ,
\nFVR.PARENT_FLEX_VALUE_HIGH “PRNT H” ,
\nFVRL.INCLUDE_EXCLUDE_INDICATOR “INC\/EXCL” ,
\nFVRL.FLEX_VALUE_LOW ,
\nFVRL.FLEX_VALUE_HIGH
\nFROM FND_FLEX_VALUE_RULES FVR, FND_FLEX_VALUE_SETS FVS,
\nFND_FLEX_VALUE_RULE_LINES FVRL, FND_ID_FLEX_SEGMENTS FS, FND_APPLICATION_TL A
\nWHERE FVR.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID
\nAND FVR.FLEX_VALUE_RULE_ID = FVRL.FLEX_VALUE_RULE_ID
\nAND FS.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID
\nAND FS.APPLICATION_ID = A.APPLICATION_ID
\nORDER BY A.APPLICATION_NAME ,FS.SEGMENT_NAME, FVS.FLEX_VALUE_SET_NAME,FVR.FLEX_VALUE_RULE_NAME ,
\nFVR.PARENT_FLEX_VALUE_LOW ,FVR.PARENT_FLEX_VALUE_HIGH ,FVRL.FLEX_VALUE_LOW, FVRL.FLEX_VALUE_HIGH
\nGL FSG Report and Components Overview<\/p>\n
\/* FSG REPORTS AND COMPONENTS OVERVIEW
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nDETAILS DEFINITIONS OF FSG REPORTS BY COMPONENT, AND INCLUDES SEVERAL SMALL SCRIPTS FOR LISTING ALL COMPONENTS ACROSS DIFFERENT CHARTS OF ACCOUNTS.
\nCAN BE USED FOR SOX AND SYSTEM AUDITS.
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 ) *\/<\/p>\n
— FSG REPORTS ————–
\nSELECT FST.ID_FLEX_STRUCTURE_NAME
\n, R.NAME
\n, R.REPORT_TITLE
\n, R.DESCRIPTION
\n, R.COLUMN_SET”COLUMN SET”
\n, RW2.STRUCTURE_ID
\n, RW2.DESCRIPTION”COL DESC”
\n, R.ROW_SET”ROW SET”
\n, RW.DESCRIPTION”ROW DESC”
\n, R.REPORT_DISPLAY_SET”DISPLAY SET”
\n, R.CONTENT_SET”CONTENT SET”
\n, R.ROW_ORDER”ROW ORDER”
\n, R.ROUNDING_OPTION “RND”
\n, U.USER_NAME
\n, U.DESCRIPTION
\n, R.CREATION_DATE
\nFROM RG_REPORTS_V R, FND_ID_FLEX_STRUCTURES_V FST, FND_USER U, RG_REPORT_AXIS_SETS_V RW, RG_REPORT_AXIS_SETS_V RW2
\nWHERE R.STRUCTURE_ID = FST.ID_FLEX_NUM
\nAND R.ROW_SET_ID = RW.AXIS_SET_ID
\nAND R.COLUMN_SET_ID = RW2.AXIS_SET_ID
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN (‘ES’,’BE’,’LU’)
\nAND R.CREATED_BY = U.USER_ID
\nORDER BY 1, 2<\/p>\n
— FSG ROW SETS AND COLUMN SETS —————-
\nSELECT FST.ID_FLEX_STRUCTURE_NAME “COA”
\n, DECODE(RW.AXIS_SET_TYPE,’R’,’ROW SET’,’C’,’COLUMN SET’,’##’) “ROW\/COLUMN”
\n, RW.NAME”SET NAME”
\n, RW.AXIS_SET_ID
\nFROM RG_REPORT_AXIS_SETS_V RW, FND_ID_FLEX_STRUCTURES_V FST
\nWHERE RW.STRUCTURE_ID = FST.ID_FLEX_NUM
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN (‘ES’,’BE’,’LU’)
\nORDER BY 1,2,3<\/p>\n
—- CONTENT SETS ————————
\nSELECT FST.ID_FLEX_STRUCTURE_NAME “COA”
\n, CS.NAME
\n, CS.CONTENT_SET_ID
\nFROM RG_REPORT_CONTENT_SETS CS, FND_ID_FLEX_STRUCTURES_V FST
\nWHERE CS.STRUCTURE_ID = FST.ID_FLEX_NUM
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN (‘ES’,’BE’,’LU’)<\/p>\n
—– ROW ORDERS —————————
\nSELECT FST.ID_FLEX_STRUCTURE_NAME “COA”
\n, RO.NAME “ROW ORDER”
\n, RO.DESCRIPTION “DESCRIPTION”
\n, RO.STRUCTURE_ID
\n, RO.ROW_ORDER_ID
\nFROM RG_ROW_ORDERS RO, FND_ID_FLEX_STRUCTURES_V FST
\nWHERE RO.STRUCTURE_ID = FST.ID_FLEX_NUM
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN (‘ES’,’BE’,’LU’)
\nORDER BY RO.NAME<\/p>\n
GL Interface Details<\/p>\n
\/* GL INTERFACE DETAIL
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nSHOWS TRANSACTIONS LEVEL DETAIL WITH FULL ACCOUNTING AND STATUS INFORMATION FOR EACH LINE IN THE GL INTERFACE ACROSS MULTIPLE SETS OF BOOKS
\nCAN BE USED FOR SOX AND SYSTEM AUDITS.
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 ) *\/
\nSELECT SOB.SHORT_NAME “BOOK”
\n, GLI.SET_OF_BOOKS_ID “SOB ID”
\n, TRUNC(GLI.ACCOUNTING_DATE) “GL DATE”
\n, GLI.CURRENCY_CODE “CUR”
\n, GLI.USER_JE_CATEGORY_NAME “JE CATEGOTY”
\n, GLI.USER_JE_SOURCE_NAME “JE SOURCE”
\n, GLI.ENTERED_DR “ENT DR”
\n, GLI.ENTERED_CR “ENT CR”
\n, GLI.ACCOUNTED_DR “ACC DR”
\n, GLI.ACCOUNTED_CR “ACC CR”
\n, GLI.SEGMENT1||’.’||GLI.SEGMENT2||’.’||GLI.SEGMENT3||’.’||GLI.SEGMENT4||’.’||GLI.SEGMENT5
\n ||’.’||GLI.SEGMENT6||’.’||GLI.SEGMENT7||’.’||GLI.SEGMENT8||’.’||GLI.SEGMENT9||’.’||GLI.SEGMENT10 “ACCOUNT COMB.”
\n, GLI.REFERENCE1 “REF 1”
\n, GLI.REFERENCE2 “REF 2”
\n, GLI.REFERENCE4 “REF 4”
\n, GLI.REFERENCE7 “REF 7”
\n, GLI.REFERENCE10 “REF 10”
\n, GLI.WARNING_CODE
\n, GLI.STATUS_DESCRIPTION
\n, GLI.STATUS
\n–SELECT GLI.REFERENCE10 “REF 10”
\n–SELECT DISTINCT GLI.SEGMENT4–,GLI.SEGMENT2, GLI.SEGMENT3, SOB.SHORT_NAME, GLI.SET_OF_BOOKS_ID
\nFROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
\nWHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID
\n–AND GLI.WARNING_CODE IS NOT NULL
\n–AND GLI.STATUS <> ‘P’
\nAND GLI.USER_JE_SOURCE_NAME = ‘PAYABLES’
\n–AND TRUNC(GLI.DATE_CREATED) > ’01-DEC-2005′
\n–AND GLI.CURRENCY_CODE = ‘GBP’
\n–AND (GLI.ENTERED_DR <> GLI.ACCOUNTED_DR
\n— OR GLI.ENTERED_CR <> GLI.ACCOUNTED_CR)
\n–AND GLI.USER_JE_CATEGORY_NAME = ‘BILL’
\n–AND SUBSTR(SOB.SHORT_NAME,1,2) IN (‘BE’)
\n–AND GLI.SEGMENT3 = ‘8181’
\n–AND GLI.STATUS_DESCRIPTION IS NOT NULL
\nORDER BY 3
\nGL Interface Summary<\/p>\n
\/* GL INTERFACE SUMMARY
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nSHOWS SUMMARY BY SOURCE, BOOK, REQUEST_ID AND GROUP_ID OF THE TRANSACTIONS IN THE GL INTERFACE ACROSS MULTIPLE SETS OF BOOKS
\nTHIS CAN BE USED FOR AD-HOC QUERIES SUCH AS MONTH AND OR TO INCLUDE IN AUTOMATED ORACLE ALERTS
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 ) *\/
\nSELECT SOB.SHORT_NAME”BOOK NAME”
\n, GLI.USER_JE_SOURCE_NAME “JRNL SOURCE”
\n, GLI.SET_OF_BOOKS_ID “BOOKS ID”
\n–, TRUNC(ACCOUNTING_DATE)”GL DATE”
\n, PERIOD_NAME”PERIOD”
\n, GLI.STATUS
\n, GLI.GROUP_ID
\n, GLI.REQUEST_ID
\n, TRUNC(GLI.DATE_CREATED) “CREATED DATE”
\n–,GLI.DATE_CREATED
\n, TRUNC(GLI.ACCOUNTING_DATE)”GL DATE”
\n, COUNT(*)
\nFROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
\nWHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID
\n–AND GLI.USER_JE_SOURCE_NAME = ‘PEOPLESOFT HR’
\nAND GLI.USER_JE_SOURCE_NAME = ‘RECEIVABLES’
\n–AND TRUNC(GLI.DATE_CREATED) > ’01-DEC-2005′
\n–AND GLI.USER_JE_SOURCE_NAME = ‘PAYROLL’
\n–AND SUBSTR(SOB.SHORT_NAME,1,2) IN (‘ES’,’BE’,’LU’)
\nGROUP BY SOB.SHORT_NAME,GLI.USER_JE_SOURCE_NAME, GLI.SET_OF_BOOKS_ID,
\n PERIOD_NAME, GLI.STATUS, GLI.GROUP_ID, TRUNC(GLI.DATE_CREATED),TRUNC(ACCOUNTING_DATE)–,GLI.DATE_CREATED
\n, GLI.REQUEST_ID
\n–,GLI.DATE_CREATED
\n–ORDER BY GLI.DATE_CREATED
\nGL Mass Allocation Formula review script<\/p>\n
\/* MASS ALLOCATION FORMULA REVIEW SCRIPT
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
\nWILL SHOW THE DEFINITION OF MASS ALLOCATION BATCHES AND LINES ACROSS MULTIPLE BOOKS IN AN EASY TO READ FORMAT FOR REVIEW IN EXCEL
\nIT HAS BEEN WRITTEN FOR A 10 SEGMENT COA BUT CAN BE MODIFIED TO SUIT DIFFERENT STRUCTURES.
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 ) *\/
\nSELECT FST.ID_FLEX_STRUCTURE_NAME”CHART OF ACCOUNTS”
\n, GAB.VALIDATION_STATUS “VALID?”
\n, GAB.NAME “ALLOCATION NAME”
\n, GAF.NAME “FORMULA NAME”
\n, GAF.FULL_ALLOCATION_FLAG”FULL?”
\n–, GAF.VALIDATION_STATUS”VALID?”
\n, GAFL.LINE_NUMBER”LINE #”
\n, DECODE(GAFL.LINE_NUMBER,1,’A’,2,’B’,3,’C’,4,’T’,5,’O’,’XXX’)”LINE”
\n, GAFL.AMOUNT”AMOUNT”
\n, GAFL.CURRENCY_CODE “CURR”
\n, GAFL.SEGMENT1||’-‘||GAFL.SEGMENT2||’-‘||GAFL.SEGMENT3||’-‘||GAFL.SEGMENT4||’-‘||GAFL.SEGMENT5||’-‘||GAFL.SEGMENT6
\n ||’-‘||GAFL.SEGMENT7||’-‘||GAFL.SEGMENT8||’-‘||GAFL.SEGMENT9||’-‘||GAFL.SEGMENT10 “ACCOUNT”
\n, GAFL.SEGMENT_TYPES_KEY “SEGMENT”
\n, GAFL.RELATIVE_PERIOD”PERIOD”
\n–, GAFL.TRANSACTION_CURRENCY”CURR”
\n, GAFL.ACTUAL_FLAG”ACTUAL?”
\n, GAFL.AMOUNT_TYPE”AMT TYPE”
\nFROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES GAFL
\n , FND_ID_FLEX_STRUCTURES_VL FST
\nWHERE GAB.ALLOCATION_BATCH_ID = GAF.ALLOCATION_BATCH_ID
\nAND GAB.CHART_OF_ACCOUNTS_ID = FST.ID_FLEX_NUM
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL.ALLOCATION_FORMULA_ID
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN (‘BE’,’LU’,’ES’)
\nORDER BY 1,3,4,6
\nGL Mass Allocation Migration Script in Dataload Professional FLD format<\/p>\n
\/* EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS
\n WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
\n THIS IS DESIGNED TO WORK WITH A 10 SEGMENT CHART OF ACCOUNTS, SO WILL NEED TO BE MODIFIED TO SUIT YOUR STRUCTURE
\n THIS EXTRACT WILL ONLY WORK WITH THE FOLLOWING CONDITIONS
\n 1- THAT LINES B&C ARE ACCOUNTS RATHER THAN VALUES. IF VALUES ARE USED THEN USE THE SECOND EXTRACT BELOW.
\n 2- THAT RELATIVE PERIOD IS CURRENT
\n 3- THAT AMOUNT TYPE IS ACTUAL
\n *\/
\nSELECT GAB.NAME
\n, GAF.NAME “FORMULA NAME”
\n, ‘ALLOCATION’
\n, GAF.DESCRIPTION “FORMULA DESC”
\n, GAFL.SEGMENT1″S11″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,1,1)”T”
\n, GAFL.SEGMENT2″S12″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)”T”
\n, GAFL.SEGMENT3″S13″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)”T”
\n, GAFL.SEGMENT4″S14″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)”T”
\n, GAFL.SEGMENT5″S15″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)”T”
\n, GAFL.SEGMENT6″S16″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)”T”
\n, GAFL.SEGMENT7″S17″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)”T”
\n, GAFL.SEGMENT8″S18″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)”T”
\n, GAFL.SEGMENT9″S19″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)”T”
\n, GAFL.SEGMENT10″S110″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)”T”
\n, GAFL.CURRENCY_CODE”CURR”
\n, GAFL.AMOUNT_TYPE”AMT TYPE”
\n, GAFL2.SEGMENT1″S21″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,1,1)”T”
\n, GAFL2.SEGMENT2″S22″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,3,1)”T”
\n, GAFL2.SEGMENT3″S23″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,5,1)”T”
\n, GAFL2.SEGMENT4″S24″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,7,1)”T”
\n, GAFL2.SEGMENT5″S25″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,9,1)”T”
\n, GAFL2.SEGMENT6″S26″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,11,1)”T”
\n, GAFL2.SEGMENT7″S27″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,13,1)”T”
\n, GAFL2.SEGMENT8″S28″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,15,1)”T”
\n, GAFL2.SEGMENT9″S29″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,17,1)”T”
\n, GAFL2.SEGMENT10″S210″
\n, SUBSTR(GAFL2.SEGMENT_TYPES_KEY,19,1)”T”
\n, GAFL2.CURRENCY_CODE”CURR”
\n, GAFL2.AMOUNT_TYPE”AMT TYPE”
\n, GAFL3.SEGMENT1″S31″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,1,1)”T”
\n, GAFL3.SEGMENT2″S32″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,3,1)”T”
\n, GAFL3.SEGMENT3″S33″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,5,1)”T”
\n, GAFL3.SEGMENT4″S34″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,7,1)”T”
\n, GAFL3.SEGMENT5″S35″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,9,1)”T”
\n, GAFL3.SEGMENT6″S36″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,11,1)”T”
\n, GAFL3.SEGMENT7″S37″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,13,1)”T”
\n, GAFL3.SEGMENT8″S38″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,15,1)”T”
\n, GAFL3.SEGMENT9″S39″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,17,1)”T”
\n, GAFL3.SEGMENT10″S310″
\n, SUBSTR(GAFL3.SEGMENT_TYPES_KEY,19,1)”T”
\n, GAFL3.CURRENCY_CODE”CURR”
\n, GAFL3.AMOUNT_TYPE”AMT TYPE”
\n, GAFL4.SEGMENT1″S41″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,1,1)”T”
\n, GAFL4.SEGMENT2″S42″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,3,1)”T”
\n, GAFL4.SEGMENT3″S43″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,5,1)”T”
\n, GAFL4.SEGMENT4″S44″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,7,1)”T”
\n, GAFL4.SEGMENT5″S45″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,9,1)”T”
\n, GAFL4.SEGMENT6″S46″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,11,1)”T”
\n, GAFL4.SEGMENT7″S47″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,13,1)”T”
\n, GAFL4.SEGMENT8″S48″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,15,1)”T”
\n, GAFL4.SEGMENT9″S49″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,17,1)”T”
\n, GAFL4.SEGMENT10″S410″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,19,1)”T”
\n, GAFL4.CURRENCY_CODE”CURR”
\n, GAFL5.SEGMENT1″S51″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,1,1)”T”
\n, GAFL5.SEGMENT2″S52″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,3,1)”T”
\n, GAFL5.SEGMENT3″S53″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,5,1)”T”
\n, GAFL5.SEGMENT4″S54″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,7,1)”T”
\n, GAFL5.SEGMENT5″S55″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,9,1)”T”
\n, GAFL5.SEGMENT6″S56″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,11,1)”T”
\n, GAFL5.SEGMENT7″S57″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,13,1)”T”
\n, GAFL5.SEGMENT8″S58″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,15,1)”T”
\n, GAFL5.SEGMENT9″S59″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,17,1)”T”
\n, GAFL5.SEGMENT10″S510″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,19,1)”T”
\nFROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES GAFL,GL_ALLOC_FORMULA_LINES GAFL2,GL_ALLOC_FORMULA_LINES GAFL3
\n ,GL_ALLOC_FORMULA_LINES GAFL4,GL_ALLOC_FORMULA_LINES GAFL5
\n , FND_ID_FLEX_STRUCTURES_VL FST
\nWHERE GAB.ALLOCATION_BATCH_ID = GAF.ALLOCATION_BATCH_ID
\nAND GAB.CHART_OF_ACCOUNTS_ID = FST.ID_FLEX_NUM
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL.ALLOCATION_FORMULA_ID
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL2.ALLOCATION_FORMULA_ID
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL3.ALLOCATION_FORMULA_ID
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL4.ALLOCATION_FORMULA_ID
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL5.ALLOCATION_FORMULA_ID
\nAND GAFL.LINE_NUMBER =1
\nAND GAFL2.LINE_NUMBER =2
\nAND GAFL3.LINE_NUMBER =3
\nAND GAFL4.LINE_NUMBER =4
\nAND GAFL5.LINE_NUMBER =5
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN (‘DE’)
\nAND GAFL2.AMOUNT IS NULL
\n–AND GAB.NAME LIKE ‘DE MAIN%’
\nORDER BY 1,2<\/p>\n
—==========================================================================================================================
\nGL Mass Allocation Rule Migration Script in Dataload Classic Format<\/p>\n
\/* EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS
\n WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED, COPYRIGHT 2007
\n IT IS DESIGNED TO WORK WITH A 10 SEGMENT CHART OF ACCOUNTS, SO WILL NEED TO BE MODIFIED TO SUIT YOUR STRUCTURE
\n THIS EXTRACT WILL ONLY WORK WITH THE FOLLOWING CONDITIONS
\n 1- THAT LINES B&C ARE VALUES NOT ACCOUNTS
\n 2- THAT RELATIVE PERIOD IS CURRENT
\n 3- THAT AMOUNT TYPE IS ACTUAL
\n *\/
\nSELECT GAB.NAME
\n, GAF.NAME “FORMULA NAME”
\n, ‘ALLOCATION’
\n, GAF.DESCRIPTION “FORMULA DESC”
\n, GAFL.SEGMENT1″S11″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,1,1)”T”
\n, GAFL.SEGMENT2″S12″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)”T”
\n, GAFL.SEGMENT3″S13″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)”T”
\n, GAFL.SEGMENT4″S14″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)”T”
\n, GAFL.SEGMENT5″S15″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)”T”
\n, GAFL.SEGMENT6″S16″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)”T”
\n, GAFL.SEGMENT7″S17″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)”T”
\n, GAFL.SEGMENT8″S18″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)”T”
\n, GAFL.SEGMENT9″S19″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)”T”
\n, GAFL.SEGMENT10″S110″
\n, SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)”T”
\n, GAFL.CURRENCY_CODE”CURR”
\n, GAFL.AMOUNT_TYPE”AMT TYPE”
\n, GAFL2.AMOUNT “B-AMT”
\n, GAFL3.AMOUNT “C-AMT”
\n, GAFL4.SEGMENT1″S41″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,1,1)”T”
\n, GAFL4.SEGMENT2″S42″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,3,1)”T”
\n, GAFL4.SEGMENT3″S43″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,5,1)”T”
\n, GAFL4.SEGMENT4″S44″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,7,1)”T”
\n, GAFL4.SEGMENT5″S45″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,9,1)”T”
\n, GAFL4.SEGMENT6″S46″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,11,1)”T”
\n, GAFL4.SEGMENT7″S47″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,13,1)”T”
\n, GAFL4.SEGMENT8″S48″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,15,1)”T”
\n, GAFL4.SEGMENT9″S49″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,17,1)”T”
\n, GAFL4.SEGMENT10″S410″
\n, SUBSTR(GAFL4.SEGMENT_TYPES_KEY,19,1)”T”
\n, GAFL4.CURRENCY_CODE”CURR”
\n, GAFL5.SEGMENT1″S51″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,1,1)”T”
\n, GAFL5.SEGMENT2″S52″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,3,1)”T”
\n, GAFL5.SEGMENT3″S53″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,5,1)”T”
\n, GAFL5.SEGMENT4″S54″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,7,1)”T”
\n, GAFL5.SEGMENT5″S55″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,9,1)”T”
\n, GAFL5.SEGMENT6″S56″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,11,1)”T”
\n, GAFL5.SEGMENT7″S57″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,13,1)”T”
\n, GAFL5.SEGMENT8″S58″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,15,1)”T”
\n, GAFL5.SEGMENT9″S59″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,17,1)”T”
\n, GAFL5.SEGMENT10″S510″
\n, SUBSTR(GAFL5.SEGMENT_TYPES_KEY,19,1)”T”
\nFROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES GAFL,GL_ALLOC_FORMULA_LINES GAFL2,GL_ALLOC_FORMULA_LINES GAFL3
\n ,GL_ALLOC_FORMULA_LINES GAFL4,GL_ALLOC_FORMULA_LINES GAFL5
\n , FND_ID_FLEX_STRUCTURES_VL FST
\nWHERE GAB.ALLOCATION_BATCH_ID = GAF.ALLOCATION_BATCH_ID
\nAND GAB.CHART_OF_ACCOUNTS_ID = FST.ID_FLEX_NUM
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL.ALLOCATION_FORMULA_ID
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL2.ALLOCATION_FORMULA_ID
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL3.ALLOCATION_FORMULA_ID
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL4.ALLOCATION_FORMULA_ID
\nAND GAF.ALLOCATION_FORMULA_ID = GAFL5.ALLOCATION_FORMULA_ID
\nAND GAFL.LINE_NUMBER =1
\nAND GAFL2.LINE_NUMBER =2
\nAND GAFL3.LINE_NUMBER =3
\nAND GAFL4.LINE_NUMBER =4
\nAND GAFL5.LINE_NUMBER =5
\nAND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN (‘DE’)
\nAND GAFL2.AMOUNT IS NOT NULL
\n–AND GAB.NAME LIKE ‘DE MAIN%’
\nORDER BY 1<\/p>\n
GL ADI Journal Balances script<\/p>\n
\/* GL ADI JOURNAL OF OPENING BALANCES & MOVEMENTS
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nGIVES A TRIAL BALANCE IN ADI FORMAT FOR UPTO TEN SEGMENTS IN THE CHART OF ACCOUNTS WITH DEBIT AND CREDIT BALANCE.
\nTHIS CAN BE USED TO EXTRACT GL BALANCES DATA FROM ONE ENVIRONMENT IN AND ADI JOURNAL FORMAT TO LOAD INTO ANOTHER ENVIRONMENT.
\nIT IS RECOMMENDED THAT THIS SCRIPT IS RUN FOR A SINGLE PERIOD AND BOOK FIRST TO GAUGE PERFORMANCE IN YOUR ENVIRONMENT.
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 )*\/
\nSELECT SOB.NAME
\n, GB.PERIOD_NAME
\n, GCC.SEGMENT1
\n, GCC.SEGMENT2
\n, GCC.SEGMENT3
\n, GCC.SEGMENT4
\n, GCC.SEGMENT5
\n, GCC.SEGMENT6
\n, GCC.SEGMENT7
\n, GCC.SEGMENT8
\n, GCC.SEGMENT9
\n, GCC.SEGMENT10
\n,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) – NVL(GB.PERIOD_NET_CR,0)) >= 0
\n THEN (SUM( NVL(GB.PERIOD_NET_DR,0) – NVL(GB.PERIOD_NET_CR,0)))
\n ELSE 0 END ) “DEBIT”
\n,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) – NVL(GB.PERIOD_NET_CR,0)) <= 0 \n THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))*-1) \n ELSE 0 END ) \"CREDIT\"\nFROM GL_BALANCES GB\n, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB\nWHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID\nAND GB.ACTUAL_FLAG = 'A'\n--AND GB.PERIOD_NAME = 'DEC-05'\nAND GB.CURRENCY_CODE = SOB.CURRENCY_CODE\nAND SUBSTR(SOB.SHORT_NAME,1,2) IN ('HK','JP','TH','SG','CN')\nAND GB.TEMPLATE_ID IS NULL\nAND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID\nGROUP BY SOB.NAME\n, GB.ACTUAL_FLAG\n, GB.PERIOD_NAME\n, GCC.SEGMENT1\n, GCC.SEGMENT2\n, GCC.SEGMENT3\n, GCC.SEGMENT4\n, GCC.SEGMENT5\n, GCC.SEGMENT6\n, GCC.SEGMENT7\n, GCC.SEGMENT8\n, GCC.SEGMENT9\n, GCC.SEGMENT10\n, NVL(GB.PERIOD_NET_DR,0)\n, NVL(GB.PERIOD_NET_CR,0)\nHAVING SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <> 0
\nORDER BY 1,2,3,4,5,6,7,8,9
\nGL Autopost Definitions<\/p>\n
\/* GL AUTOPOST DEFINITIONS
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nLISTS THE AUTOPOST DEFINITIONS BY BOOK SHOW THE JOURNAL SOURCE AND CATEGORY
\nTESTED ON VISION 11.5.10.2 JUNE 2007 *\/
\nSELECT SOB.NAME “BOOK”
\n, APS.AUTOPOST_SET_NAME “SET NAME”
\n, APS.DESCRIPTION
\n, APS.ENABLED_FLAG
\n, APS.SUBMIT_ALL_PRIORITIES_FLAG “SUBMIT ALL?”
\n, APO.ACTUAL_FLAG “ACTUAL FLAG”
\n, APO.PERIOD_NAME “PERIOD”
\n, APO.JE_SOURCE_NAME “SOURCE”
\n, APO.USER_JE_CATEGORY_NAME “CATEGORY”
\nFROM GL_AUTOMATIC_POSTING_SETS_V APS, GL_AUTOMATIC_POSTING_OPTIONS_V APO, GL_SETS_OF_BOOKS SOB
\nWHERE APO.AUTOPOST_SET_ID = APS.AUTOPOST_SET_ID
\nAND APS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
\n–AND SUBSTR(SOB.NAME,1,2) IN (‘ES’,’LU’,’BE’)
\nORDER BY 1
\nHR Operating Unit and Legal Entity Configuration<\/p>\n
\/* LEGAL ENTITIERS & ORGANIZATIONS
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nGIVES AN OVERVIEW OF THE LEGAL ENTITY AND OPERATING UNIT CONFIGURATION ACROSS MULTIPLE OU
\nTHIS IS USED WHEN IMPLEMENTING MULTIPLE OFFICES TO ENSURE CONSISTENT SETUP ACROSS COUNTRIES AND BETWEEN ENVIRONMENTS.
\nWHERE CLAUSE CAN BE ADDED OR COMMENTED OUT TO JUST LOOK AT SPECIFIC COUNTRIES.IF CONSISTENT NAMING CONVENTIONS HAVE BEEN USED.
\n( TESTED ON VISION 11.5.10.2 JUNE 2007 )*\/
\nSELECT HRO.ORGANIZATION_ID
\n, HRO.NAME
\n, HOI.ORG_INFORMATION_CONTEXT
\n, SOB2.NAME “LE SET OF BOOKS”
\n–, HOI.ORG_INFORMATION1
\n, HRO_LE.NAME “OU LEGAL ENT”
\n, HOI.ORG_INFORMATION2 “LE VAT CODES”
\n–, HOI.ORG_INFORMATION3
\n, SOB.NAME “OU SET OF BOOKS”
\nFROM HR_ALL_ORGANIZATION_UNITS_TL HRO, HR_ORGANIZATION_INFORMATION_V HOI,
\nGL_SETS_OF_BOOKS SOB,GL_SETS_OF_BOOKS SOB2, HR_ALL_ORGANIZATION_UNITS_TL HRO_LE
\nWHERE HOI.ORG_INFORMATION_CONTEXT IN (‘LEGAL ENTITY ACCOUNTING’,’OPERATING UNIT INFORMATION’)
\nAND HRO.ORGANIZATION_ID = HOI.ORGANIZATION_ID
\nAND TO_CHAR(SOB.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION3
\nAND TO_CHAR(SOB2.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION1
\nAND TO_CHAR(HRO_LE.ORGANIZATION_ID(+)) = HOI.ORG_INFORMATION2
\n–AND SUBSTR(HRO.NAME,1,2) IN (‘BE’,’LU’,’ES’)
\nORDER BY 2,3
\nDataload .dld GL Cross Validation Rules<\/p>\n
\/* DATALOAD (DLD) FORMAT SQL EXTRACT OF CVR CROSS VALIDATION RULES
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nEXTRACTS CVR’S FROM ONE ENVIRONMENT IN A DATALOAD FORMAT READY TO LOAD INTO THE NEXT ENVIRONMENT USING DATALOAD CLASSIC.
\nNOTE : THE SEGMENTS LOW&HIGH SUBSTINGS WILL NEED UPDATING TO MATCH YOUR SPECIFIC CHART OF ACCOUNTS DEFINITIONS
\n(TESTED ON 11.5.9 MAR 2007 )*\/
\nSELECT FST.ID_FLEX_STRUCTURE_NAME “BOOKS”
\n, L.INCLUDE_EXCLUDE_INDICATOR”INC?”
\n, R.FLEX_VALIDATION_RULE_NAME”NAME”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’THEN R.FLEX_VALIDATION_RULE_NAME ELSE NULL END )”NAME”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN ‘TAB’ ELSE NULL END )”Z”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN TL.DESCRIPTION ELSE NULL END )”DESCRIPTION”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN ‘TAB’ ELSE NULL END )”Z”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN ‘TAB’ ELSE NULL END )”Z”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN TL.ERROR_MESSAGE_TEXT ELSE NULL END )”MESSAGE”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN ‘TAB’ ELSE NULL END )”Z”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN DECODE(R.ERROR_SEGMENT_COLUMN_NAME,’SEGMENT1′,’ENTITY’,’SEGMENT2′,’OFFICE’,’SEGMENT3′,’GROUP’,’SEGMENT4′,’ACCOUNT’,’SEGMENT5′,’LOCAL’,’SEGMENT6′,’PARTNER’,’SEGMENT7′,’PROJECT’,’SEGMENT8′,’YEAR’,’XXXXX’) ELSE NULL END )”SEGMENT”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN ‘TAB’ ELSE NULL END )”Z”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN ‘TAB’ ELSE NULL END )”Z”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN ‘TAB’ ELSE NULL END )”Z”
\n, (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = ‘I’ THEN ‘TAB’ ELSE NULL END )”Z”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,0,2)”1L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,0,2)”1H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,4,2)”2L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,4,2)”2H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,7,4)”3L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,7,4)”3H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,12,5)”4L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,12,5)”4H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,18,6)”5L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,18,6)”5H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,25,4)”6L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,25,4)”6H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,30,5)”7L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,30,5)”7H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,36,4)”8L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,36,4)”8H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,41,4)”9L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,41,4)”9H”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_LOW,46,4)”10L”
\n, SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,46,4)”10H”
\n, ‘ENT’
\n, ‘*SL3’
\n, ‘*DN’
\n, ‘TAB’
\n, ‘*SL1’
\nFROM FND_FLEX_VALIDATION_RULES R,
\n FND_FLEX_VDATION_RULES_TL TL,
\n FND_FLEX_VALIDATION_RULE_LINES L,
\n FND_ID_FLEX_STRUCTURES_VL FST
\nWHERE R.APPLICATION_ID = TL.APPLICATION_ID
\nAND FST.ID_FLEX_NUM = R.ID_FLEX_NUM
\nAND R.ID_FLEX_CODE = TL.ID_FLEX_CODE
\nAND R.ID_FLEX_NUM = TL.ID_FLEX_NUM
\nAND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
\nAND R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
\nAND R.APPLICATION_ID = L.APPLICATION_ID
\nAND R.ID_FLEX_CODE = L.ID_FLEX_CODE
\nAND R.ID_FLEX_NUM = L.ID_FLEX_NUM
\nAND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
\nAND R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
\nAND R.APPLICATION_ID = 101
\nAND R.ID_FLEX_CODE = ‘GL#’
\n–AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN (‘BE’,’LU’)
\n–AND R.ERROR_SEGMENT_COLUMN_NAME = ‘SEGMENT5’
\n–AND TL.ERROR_MESSAGE_TEXT LIKE ‘%LOCAL%’
\n–AND SUBSTR(L.CONCATENATED_SEGMENTS_LOW,1,2)=’ZZ’
\nORDER BY 1,3,2 DESC, 12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30<\/p>\n
Dataload Professional .fld — Daily Rates load<\/p>\n
\/* DAILY RATES DLD PROFESSIONAL LOAD (DATE RANGES )
\nWRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
\nEXTRACTS SPECIFIC DAILY RATES IN A DATALOAD PROFESSIONAL FILE FORMAT .FLD
\nTO BE USED IN CONJUNCTION WITH A PREDEFINED .FLD FILE
\n(TESTED ON VISION 11.5.10.2 JUNE 2007 ) *\/
\nSELECT
\n GLR.FROM_CURRENCY
\n, GLR.TO_CURRENCY
\n, GLR.CONVERSION_DATE”FROM”
\n, GLR.CONVERSION_DATE”TO”
\n, RT.USER_CONVERSION_TYPE
\n, GLR.SHOW_CONVERSION_RATE “FROM > TO”
\n, GLR.SHOW_INVERSE_CON_RATE ” TO > FROM(INVERSE)”
\nFROM GL_DAILY_RATES_V GLR, FND_CURRENCIES C, GL_DAILY_CONVERSION_TYPES RT
\nWHERE GLR.FROM_CURRENCY = C.CURRENCY_CODE
\nAND GLR.CONVERSION_TYPE = RT.CONVERSION_TYPE
\nAND RT.USER_CONVERSION_TYPE LIKE ‘CORPORATE’
\n–AND CONVERSION_DATE > TO_DATE(’30-NOV-2002′,’DD-MON-YYYY’)
\n–AND CONVERSION_DATE > TO_DATE(’31-AUG-2006′,’DD-MON-YYYY’)
\n–AND SUBSTR(GLR.CONVERSION_DATE,1,2) = ’01’
\n–AND FROM_CURRENCY IN (‘GBP’)
\n–AND TO_CURRENCY NOT IN (‘GBP’)
\n–AND RT.USER_CONVERSION_TYPE LIKE ‘THAI%’
\nORDER BY 1,GLR.CONVERSION_DATE<\/HTML><\/HTML><\/p>\n","protected":false},"excerpt":{"rendered":"
General Ledger Useful SQL Scripts \u2013 Oracle Applications 11i Contents GL Set of Books Configuration Overview 1 GL Summary Account Template Definition Review 2 GL… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[29,127,124],"tags":[],"class_list":["post-15621","post","type-post","status-publish","format-standard","hentry","category-base-de-conhecimentos","category-applications","category-database"],"_links":{"self":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/15621","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=15621"}],"version-history":[{"count":5,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/15621\/revisions"}],"predecessor-version":[{"id":15626,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=\/wp\/v2\/posts\/15621\/revisions\/15626"}],"wp:attachment":[{"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=15621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=15621"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/orabr.virttus.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=15621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}