Translate

Wednesday, June 12, 2019

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
WHERE FST.ID_FLEX_NUM = FSEG.ID_FLEX_NUM
AND FSEG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('BE','LU','ES','IT','HU','CZ','PL','RU')
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
ORDER BY 1, FSEG.SEGMENT_NUM
GL Chart of Accounts Structure Overview

/* 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
WHERE FST.ID_FLEX_NUM = FSEG.ID_FLEX_NUM
AND FSEG.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('BE','LU','ES','IT','HU','CZ','PL','RU')
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
ORDER BY 1, FSEG.SEGMENT_NUM
GL Journal Header Summary
/* GL JOURNAL HEADER SUMMARY
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
SUMMARY LISTING OF JOURNAL HEADER RECORDS BY CATEGORY AND SOURCE ACROSS MULTIPLE SETS OF BOOKS.
(TESTED ON VISION 11.5.10.2  JUNE 2007 ) */
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     = 'QUV-DECLARATION TVA 11/04'  -- 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 ('DE')
ORDER BY 1,2 DESC,3,4,5,7
GL Journal Line Based Trial Balance Report
/* GL JOURNAL BASED TRIAL BALANCE
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED, COPYRIGHT 2007
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.
(TESTED ON VISION 11.5.10.2  JUNE 2007 ) */
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-03'
--AND SOB.SHORT_NAME = 'GBMAN'
--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
GL Journal Lines With AP Source Reference Fields
/* GL JOURNAL LINES WITH AP SOURCE REFERENCE FIELDS
Written by Daniel North,  ORAFINAPPS Limited 2007
(Tested on Vision 11.5.10.2  June 2007 ) */
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 = 87
order by 1,2,3,4,5,7

GL Mass Allocation Rule Migration Script in Dataload Classic Format
/* MASS ALLOCATION MIGRATION - DATALOAD CLASSIC LAYOUT
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED, COPYRIGHT 2007
CREATES A PRE-FORMATED SPREADSHEET LAYOUT TO MIGRATE MASS ALLOCATIONS BETWEEN ENVIRONMENTS AND/OR BOOKS USING DATALOAD CLASSIC.
IT HAS BEEN WRITTEN FOR A 10 SEGMENT COA BUT CAN BE MODIFIED TO SUIT DIFFERENT STRUCTURES.
(TESTED ON VISION 11.5.9 JAN-2007 ) */
SELECT SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2)"BOOK"
,      GAB.NAME "ALLOCATION NAME"
/*, (CASE WHEN GAFL.LINE_NUMBER = 1 THEN  GAB.NAME  ELSE NULL END )"ALLOCATION NAME"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN  'TAB' ELSE NULL END )"TAB"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'A' ELSE NULL END )"A"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN   'TAB' ELSE NULL END )"TAB"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN  GAB.DESCRIPTION  ELSE NULL END )"ALLOC DESCRIPTION"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN   '*AR' ELSE NULL END )"TAB"
,*/
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN  '\'||GAF.NAME  ELSE NULL END )"FORMULA NAME"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'ALLOCATION' ELSE NULL END )"ALLOCATION"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN  GAF.DESCRIPTION  ELSE NULL END )"FORMULA DESC"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN '*SB' ELSE NULL END )"FCP"
,   (CASE WHEN GAFL.LINE_NUMBER = 1 THEN 'TAB' ELSE NULL END )"TAB"
,      (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"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT1 ELSE '\'||TO_CHAR(GAFL.AMOUNT) END )"1"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,0,1) ELSE NULL END )"1T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT2 ELSE NULL END )"2"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)ELSE NULL END )"2T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT3 ELSE NULL END )"3"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)ELSE NULL END )"3T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT4 ELSE NULL END )"4"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)ELSE NULL END )"4T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT5 ELSE NULL END )"5"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)ELSE NULL END )"5T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT6 ELSE NULL END )"6"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)ELSE NULL END )"6T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT7 ELSE NULL END )"7"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)ELSE NULL END )"7T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT8 ELSE NULL END )"8"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)ELSE NULL END )"8T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT9 ELSE NULL END )"9"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)ELSE NULL END )"9T"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||GAFL.SEGMENT10 ELSE NULL END )"10"
,      (CASE WHEN GAFL.AMOUNT IS NULL THEN '\'||SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)ELSE NULL END )"10T"
,   (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"
,   (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"
,   (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"
,   (CASE WHEN GAFL.AMOUNT IS NOT NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (2) THEN 'TAB' ELSE NULL END )ELSE NULL END)"TAB2"
,   (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"
,   (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"
,   (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*SAVE' ELSE NULL END )ELSE NULL END )"*SAVE"
,   (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*PB' ELSE NULL END )ELSE NULL END )"*PB"
,   (CASE WHEN GAFL.AMOUNT IS NULL THEN(CASE WHEN GAFL.LINE_NUMBER IN (5) THEN '*NR' ELSE NULL END )ELSE NULL END )"*NR"
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 ('DE')
ORDER BY 1,GAB.NAME, GAF.NAME, GAFL.LINE_NUMBER

GL Balances and Movements
/* GL BALANCES & MOVEMENTS
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
GIVES A TRIAL BALANCE WITH OPENING, MOVEMENT AND CLOSING BALANCES FOR UPTO TEN SEGMENTS IN THE CHART OF ACCOUNTS BY CURRENCY.
THIS CAN BE USED TO AS A QUICK METHOD OF RUNNING A TRIAL BALANCE FOR DATA EXTRACT IN THE DESIRED FORMAT.
FOR EXAMPLE TO USE TO EXTRACT TO A THIRD PARTY REPORTING SYSTEM SUCH AS HYPERION
IT IS RECOMMENDED THAT THIS SCRIPT IS RUN FOR A SINGLE PERIOD AND BOOK FIRST TO GAUGE PERFORMANCE IN YOUR ENVIRONMENT.
(TESTED ON VISION 11.5.10.2  JUNE 2007 ) */
SELECT SOB.NAME
,      GB.ACTUAL_FLAG
,      GB.PERIOD_NAME
,      GCC.CODE_COMBINATION_ID
,      GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
       ||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 "DISTRIBUTION"
,SUM( NVL(GB.BEGIN_BALANCE_DR,0) - NVL(GB.BEGIN_BALANCE_CR,0))"OPEN BAL"
,NVL(GB.PERIOD_NET_DR,0) "DEBIT"
,NVL(GB.PERIOD_NET_CR,0) "CREDIT"
,SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))"NET MOVEMENT"
,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"
,      GB.CURRENCY_CODE
,      GB.TRANSLATED_FLAG
,      GB.TEMPLATE_ID
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND  GB.ACTUAL_FLAG = 'A'
AND    GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND  GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND  GB.PERIOD_NAME = 'APR-04'
AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('PR')
--AND GCC.SEGMENT1 = '85'
--AND GCC.SEGMENT2 = '70'
--AND GCC.SEGMENT3 = '0000'
--AND GCC.SEGMENT4 IN ('99659')
--AND GCC.SEGMENT7 = 'T'
--AND     NVL(GB.TRANSLATED_FLAG,'X') != 'R'
GROUP BY  SOB.NAME
,      GB.ACTUAL_FLAG
,      GB.PERIOD_NAME
,      GCC.CODE_COMBINATION_ID
,      GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
       ||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10
,      NVL(GB.PERIOD_NET_DR,0)
,      NVL(GB.PERIOD_NET_CR,0)
,      GB.CURRENCY_CODE
,      GB.TRANSLATED_FLAG
,      GB.TEMPLATE_ID
HAVING 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
GL Chart of Account Segment Hierarchy Ranges
/* GL : CCHART OF ACCOUNT SEGMENT HIERARCHY RANGES
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
CHART OF ACCOUNT SEGMENT HIERARCHY RANGES AND ATTRIBUTES FOR PARENT ACCOUNTS
*/
SELECT  FVS.FLEX_VALUE_SET_NAME"VALUE SET"
,      FV.FLEX_VALUE
,      NH.PARENT_FLEX_VALUE "PARENT"
,      FVT.DESCRIPTION
,      NH.RANGE_ATTRIBUTE "INC C OR P?"
,      NH.CHILD_FLEX_VALUE_LOW "FROM"
,      NH.CHILD_FLEX_VALUE_HIGH "TO"
,      NH.PARENT_FLEX_VALUE || ' : ' ||NH.RANGE_ATTRIBUTE || ' : ' ||
       NH.CHILD_FLEX_VALUE_LOW || ' -> ' ||NH.CHILD_FLEX_VALUE_HIGH "HIERARCHY RANGE"
,      SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,1,1)"POSTING"
,      SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,3,1)"BUDGETING"
,      SUBSTR(FV.COMPILED_VALUE_ATTRIBUTES,5,1)"ACC TYPE"
,      FV.ENABLED_FLAG"ENABLED"
,      FV.SUMMARY_FLAG"PARENT?"
,      NH.LAST_UPDATE_DATE
,      FV.HIERARCHY_LEVEL"LEVEL"
FROM FND_FLEX_VALUE_NORM_HIERARCHY NH, FND_FLEX_VALUE_SETS FVS, FND_FLEX_VALUES_TL FVT, FND_FLEX_VALUES FV
WHERE FVS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
AND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
AND FV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
AND NH.PARENT_FLEX_VALUE(+) = FVT.FLEX_VALUE_MEANING
AND FVS.FLEX_VALUE_SET_ID = NH.FLEX_VALUE_SET_ID
AND FVS.FLEX_VALUE_SET_NAME LIKE '%ACCOUNT%' --- CHART OF ACCOUNTS SEGMENT NAME
--  AND SUBSTR(FVS.FLEX_VALUE_SET_NAME,4,2) IN ('BE','LU','ES')
AND FV.SUMMARY_FLAG = 'Y'
AND FV.FLEX_VALUE LIKE '%XYZ%'  --- THIS IS THE PARENT SEGMENT VALUES
--  AND NH.PARENT_FLEX_VALUE = '%%'
--  AND FV.ENABLED_FLAG = 'Y'
--  AND FV.HIERARCHY_LEVEL = '2'
ORDER BY 1,3
GL Code Combinations CCIDs
/* GL CODE COMBINATIONS
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
GL CODE COMBINATIONS EXTRACT. CAN BE SELECT BY CHART OF ACCOUNTS, SPECIFIC SEGMENT VALUES OR SPECIFIC CODE COMBINATION ATTRIBUTES.
THIS CAN BE USED FOR CHART OF ACCOUNTS MAINTENANCE AND REVIEW
(TESTED ON VISION 11.5.10.2  JUNE 2007 )*/
SELECT FST.ID_FLEX_STRUCTURE_NAME
,    GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
,   GCC.CODE_COMBINATION_ID
,   GCC.LAST_UPDATE_DATE
,   GCC.JGZZ_RECON_FLAG
,   GCC.START_DATE_ACTIVE
,   GCC.END_DATE_ACTIVE 
,   GCC.DETAIL_POSTING_ALLOWED_FLAG
,   GCC.ENABLED_FLAG
,   GCC.SUMMARY_FLAG
,   GCC.START_DATE_ACTIVE
FROM GL_CODE_COMBINATIONS GCC
,    FND_ID_FLEX_STRUCTURES_VL FST
WHERE FST.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
--AND GCC.SEGMENT1 IN ('25','26','30')
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
--AND GCC.SEGMENT4 = '99901'
ORDER BY 1,2,3
GL CVR Cross Validation Rule Detail Listing
/* CVR CROSS VALIDATION RULE DETAIL LISTING
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
PROVIDES DETAIL VIEW OF CVR DEFINITION INCLUDING ACCOUNT RANGES.
(TESTED ON VISION 11.5.10.2  JUNE 2007 )*/
SELECT FST.ID_FLEX_STRUCTURE_NAME
,      R.FLEX_VALIDATION_RULE_NAME
,      R.ENABLED_FLAG
--,    R.ERROR_SEGMENT_COLUMN_NAME"ERR SEG"
--,    TL.DESCRIPTION
--,    TL.ERROR_MESSAGE_TEXT"ERROR MESSAGE"
,      L.ENABLED_FLAG
,      L.INCLUDE_EXCLUDE_INDICATOR"INC?"
,      L.CONCATENATED_SEGMENTS_LOW "FROM"
,      L.CONCATENATED_SEGMENTS_HIGH "TO"
,      L.LAST_UPDATED_BY
,      L.LAST_UPDATE_DATE
FROM   FND_FLEX_VALIDATION_RULES R,
       FND_FLEX_VDATION_RULES_TL TL,
       FND_FLEX_VALIDATION_RULE_LINES L,
       FND_ID_FLEX_STRUCTURES_VL FST
WHERE  R.APPLICATION_ID = TL.APPLICATION_ID
AND    FST.ID_FLEX_NUM = R.ID_FLEX_NUM
AND    R.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND    R.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND    R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND    R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND    R.APPLICATION_ID = L.APPLICATION_ID
AND    R.ID_FLEX_CODE = L.ID_FLEX_CODE
AND    R.ID_FLEX_NUM = L.ID_FLEX_NUM
AND    R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND    R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND    R.APPLICATION_ID = 101
--     OPTIONAL FILTERS BELOW TO LIMIT QUERY TO SPECIFIC CVR OR LINES
--AND    R.ERROR_SEGMENT_COLUMN_NAME = 'SEGMENT5'
--AND     TL.ERROR_MESSAGE_TEXT LIKE '%PLEASE USE A VALID R%'
--AND    R.FLEX_VALIDATION_RULE_NAME LIKE 'BE GROUP ERROR%'
--AND     TL.ERROR_MESSAGE_TEXT LIKE '%94005%'
--AND     L.INCLUDE_EXCLUDE_INDICATOR = 'E'
ORDER BY 1,R.FLEX_VALIDATION_RULE_NAME, L.INCLUDE_EXCLUDE_INDICATOR DESC, L.CONCATENATED_SEGMENTS_LOW

GL CVR Cross Validation Rule Overview
/* CVR OVERVIEW (CROSS VALIDATION RULES )
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
PROVIDES VIEW OF HEADER LEVEL CROSS VALIDATION RULE DEFINITIONS TO
OBTAIN AN OVERVIEW OF RULES AND MESSAGES ACROSS MULTIPLE CHARTS OF ACCOUNTS
(TESTED ON VISION 11.5.10.2  JUNE 2007 )*/
SELECT   FST.ID_FLEX_STRUCTURE_NAME"COA"
,      R.FLEX_VALIDATION_RULE_NAME"RULE NAME"
,      R.ENABLED_FLAG"ENB?"
,      R.ERROR_SEGMENT_COLUMN_NAME"ERROR SEG"
,      LENGTH(TL.ERROR_MESSAGE_TEXT)"ERROR LENGTH"
,      TL.ERROR_MESSAGE_TEXT"MESSAGE"
,      TL.CREATION_DATE
--SELECT COUNT(*), FST.ID_FLEX_STRUCTURE_NAME
FROM   FND_FLEX_VALIDATION_RULES R,
       FND_FLEX_VDATION_RULES_TL TL,
       FND_ID_FLEX_STRUCTURES_VL FST
WHERE  R.APPLICATION_ID = TL.APPLICATION_ID
AND    FST.ID_FLEX_NUM = R.ID_FLEX_NUM
AND    R.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND    R.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND    R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND    R.APPLICATION_ID = 101
--AND    SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('BE','LU','ES')  -- LIMITS RESULTS TO SPECIFIC CHARTS OF ACCOUNTS
--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
ORDER BY 1,2
GL Flexfield Security Rule Assignments
/* FLEXFIELD SECURITY RULE ASSIGNMENTS TO RESPONSIBILITIES
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
LISTS SECURITY RULE ASSIGNMENTS TO RESPONSIBILITIES
(TESTED ON VISION 11.5.10.2  JUNE 2007 )*/
SELECT A.APPLICATION_NAME
,      FVR.FLEX_VALUE_RULE_NAME
,      R.RESPONSIBILITY_KEY
FROM FND_FLEX_VALUE_RULES FVR,
FND_FLEX_VALUE_RULE_USAGES RU, FND_RESPONSIBILITY R, FND_APPLICATION_TL A
WHERE FVR.FLEX_VALUE_RULE_ID = RU.FLEX_VALUE_RULE_ID
AND RU.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
AND RU.APPLICATION_ID = A.APPLICATION_ID
AND FVR.FLEX_VALUE_RULE_NAME LIKE '%'
ORDER BY FLEX_VALUE_RULE_NAME
GL Flexfield Security Rule Definitions
/* FLEXFIELD SECURITY RULE DEFINITIONS
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
LISTS SECURITY RULE DEFINITIONS AND GL ACCOUNT RANGES
(TESTED ON VISION 11.5.10.2  JUNE 2007 )*/
SELECT A.APPLICATION_NAME   "APPS"            ,           
FS.SEGMENT_NAME                                  ,          
FVS.FLEX_VALUE_SET_NAME                          ,
    FVR.FLEX_VALUE_RULE_NAME                  ,
FVR.PARENT_FLEX_VALUE_LOW   "PRNT L"             ,
FVR.PARENT_FLEX_VALUE_HIGH  "PRNT H"             ,
FVRL.INCLUDE_EXCLUDE_INDICATOR "INC/EXCL"        ,
FVRL.FLEX_VALUE_LOW                              ,
FVRL.FLEX_VALUE_HIGH                         
FROM FND_FLEX_VALUE_RULES FVR, FND_FLEX_VALUE_SETS FVS,
FND_FLEX_VALUE_RULE_LINES FVRL, FND_ID_FLEX_SEGMENTS FS, FND_APPLICATION_TL A
WHERE FVR.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID
AND FVR.FLEX_VALUE_RULE_ID = FVRL.FLEX_VALUE_RULE_ID
AND FS.FLEX_VALUE_SET_ID = FVS.FLEX_VALUE_SET_ID
AND FS.APPLICATION_ID = A.APPLICATION_ID
ORDER BY A.APPLICATION_NAME ,FS.SEGMENT_NAME, FVS.FLEX_VALUE_SET_NAME,FVR.FLEX_VALUE_RULE_NAME                   ,
FVR.PARENT_FLEX_VALUE_LOW   ,FVR.PARENT_FLEX_VALUE_HIGH ,FVRL.FLEX_VALUE_LOW, FVRL.FLEX_VALUE_HIGH
GL FSG Report and Components Overview
/* FSG REPORTS AND COMPONENTS OVERVIEW
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
DETAILS DEFINITIONS OF FSG REPORTS BY COMPONENT, AND INCLUDES SEVERAL SMALL SCRIPTS FOR LISTING ALL COMPONENTS ACROSS DIFFERENT CHARTS OF ACCOUNTS.
CAN BE USED FOR SOX AND SYSTEM AUDITS.
(TESTED ON VISION 11.5.10.2  JUNE 2007 ) */

-- FSG REPORTS  --------------
SELECT FST.ID_FLEX_STRUCTURE_NAME
,      R.NAME
,      R.REPORT_TITLE
,      R.DESCRIPTION
,      R.COLUMN_SET"COLUMN SET"
,      RW2.STRUCTURE_ID
,      RW2.DESCRIPTION"COL DESC"
,      R.ROW_SET"ROW SET"
,      RW.DESCRIPTION"ROW DESC"
,      R.REPORT_DISPLAY_SET"DISPLAY SET"
,      R.CONTENT_SET"CONTENT SET"
,      R.ROW_ORDER"ROW ORDER"
,      R.ROUNDING_OPTION "RND"
,      U.USER_NAME
,      U.DESCRIPTION
,      R.CREATION_DATE
FROM 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
WHERE R.STRUCTURE_ID = FST.ID_FLEX_NUM
AND R.ROW_SET_ID = RW.AXIS_SET_ID
AND R.COLUMN_SET_ID = RW2.AXIS_SET_ID
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
AND R.CREATED_BY = U.USER_ID
ORDER BY 1, 2

-- FSG ROW SETS AND COLUMN SETS ----------------
SELECT  FST.ID_FLEX_STRUCTURE_NAME "COA"
,      DECODE(RW.AXIS_SET_TYPE,'R','ROW SET','C','COLUMN SET','##') "ROW/COLUMN"
,      RW.NAME"SET NAME"
,      RW.AXIS_SET_ID
FROM RG_REPORT_AXIS_SETS_V RW, FND_ID_FLEX_STRUCTURES_V FST
WHERE RW.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
ORDER BY 1,2,3

---- CONTENT SETS ------------------------
SELECT FST.ID_FLEX_STRUCTURE_NAME "COA"
,      CS.NAME
,      CS.CONTENT_SET_ID
FROM RG_REPORT_CONTENT_SETS CS, FND_ID_FLEX_STRUCTURES_V FST
WHERE CS.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')

----- ROW ORDERS ---------------------------
SELECT FST.ID_FLEX_STRUCTURE_NAME "COA"
,      RO.NAME "ROW ORDER"
,      RO.DESCRIPTION "DESCRIPTION"
,      RO.STRUCTURE_ID
,      RO.ROW_ORDER_ID
FROM RG_ROW_ORDERS RO, FND_ID_FLEX_STRUCTURES_V FST
WHERE RO.STRUCTURE_ID = FST.ID_FLEX_NUM
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('ES','BE','LU')
ORDER BY RO.NAME

GL Interface Details
/* GL INTERFACE DETAIL
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
SHOWS TRANSACTIONS LEVEL DETAIL WITH FULL ACCOUNTING AND STATUS INFORMATION FOR EACH LINE IN THE GL INTERFACE ACROSS MULTIPLE SETS OF BOOKS
CAN BE USED FOR SOX AND SYSTEM AUDITS.
(TESTED ON VISION 11.5.10.2  JUNE 2007 ) */
SELECT SOB.SHORT_NAME "BOOK"
,       GLI.SET_OF_BOOKS_ID "SOB ID"
,      TRUNC(GLI.ACCOUNTING_DATE) "GL DATE"
,      GLI.CURRENCY_CODE "CUR"
,      GLI.USER_JE_CATEGORY_NAME "JE CATEGOTY"
,      GLI.USER_JE_SOURCE_NAME "JE SOURCE"
,      GLI.ENTERED_DR "ENT DR"
,      GLI.ENTERED_CR "ENT CR"
,      GLI.ACCOUNTED_DR "ACC DR"
,      GLI.ACCOUNTED_CR "ACC CR"
,       GLI.SEGMENT1||'.'||GLI.SEGMENT2||'.'||GLI.SEGMENT3||'.'||GLI.SEGMENT4||'.'||GLI.SEGMENT5
        ||'.'||GLI.SEGMENT6||'.'||GLI.SEGMENT7||'.'||GLI.SEGMENT8||'.'||GLI.SEGMENT9||'.'||GLI.SEGMENT10 "ACCOUNT COMB."
,      GLI.REFERENCE1 "REF 1"
,      GLI.REFERENCE2 "REF 2"
,      GLI.REFERENCE4 "REF 4"
,      GLI.REFERENCE7 "REF 7"
,      GLI.REFERENCE10 "REF 10"
,      GLI.WARNING_CODE
,      GLI.STATUS_DESCRIPTION
,      GLI.STATUS
--SELECT GLI.REFERENCE10 "REF 10"
--SELECT DISTINCT GLI.SEGMENT4--,GLI.SEGMENT2, GLI.SEGMENT3, SOB.SHORT_NAME, GLI.SET_OF_BOOKS_ID
FROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID
--AND GLI.WARNING_CODE IS NOT NULL
--AND GLI.STATUS <> 'P'
AND GLI.USER_JE_SOURCE_NAME = 'PAYABLES'
--AND TRUNC(GLI.DATE_CREATED) > '01-DEC-2005'
--AND GLI.CURRENCY_CODE  = 'GBP'
--AND (GLI.ENTERED_DR <> GLI.ACCOUNTED_DR
--  OR GLI.ENTERED_CR <> GLI.ACCOUNTED_CR)
--AND GLI.USER_JE_CATEGORY_NAME = 'BILL'
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('BE')
--AND GLI.SEGMENT3 = '8181'
--AND GLI.STATUS_DESCRIPTION IS NOT NULL
ORDER BY 3
GL Interface Summary
/* GL INTERFACE SUMMARY
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
SHOWS SUMMARY BY SOURCE, BOOK, REQUEST_ID AND GROUP_ID OF THE TRANSACTIONS IN THE GL INTERFACE ACROSS MULTIPLE SETS OF BOOKS
THIS CAN BE USED FOR AD-HOC QUERIES SUCH AS MONTH AND OR TO INCLUDE IN AUTOMATED ORACLE ALERTS
(TESTED ON VISION 11.5.10.2  JUNE 2007 ) */
SELECT SOB.SHORT_NAME"BOOK NAME"
,      GLI.USER_JE_SOURCE_NAME "JRNL SOURCE"
,      GLI.SET_OF_BOOKS_ID "BOOKS ID"
--,    TRUNC(ACCOUNTING_DATE)"GL DATE"
,      PERIOD_NAME"PERIOD"
,      GLI.STATUS
,      GLI.GROUP_ID
,      GLI.REQUEST_ID
,      TRUNC(GLI.DATE_CREATED) "CREATED DATE"
--,GLI.DATE_CREATED
,      TRUNC(GLI.ACCOUNTING_DATE)"GL DATE"
,      COUNT(*)
FROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID
--AND GLI.USER_JE_SOURCE_NAME = 'PEOPLESOFT HR'
AND GLI.USER_JE_SOURCE_NAME = 'RECEIVABLES'
--AND TRUNC(GLI.DATE_CREATED) > '01-DEC-2005'
--AND GLI.USER_JE_SOURCE_NAME = 'PAYROLL'
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('ES','BE','LU')
GROUP BY  SOB.SHORT_NAME,GLI.USER_JE_SOURCE_NAME, GLI.SET_OF_BOOKS_ID,
PERIOD_NAME, GLI.STATUS,  GLI.GROUP_ID, TRUNC(GLI.DATE_CREATED),TRUNC(ACCOUNTING_DATE)--,GLI.DATE_CREATED
,      GLI.REQUEST_ID
--,GLI.DATE_CREATED
--ORDER BY GLI.DATE_CREATED
GL Mass Allocation Formula review script
/* MASS ALLOCATION FORMULA REVIEW SCRIPT
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED, COPYRIGHT 2007
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.
(TESTED ON VISION 11.5.10.2  JUNE 2007 ) */
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 ('BE','LU','ES')
ORDER BY 1,3,4,6
GL Mass Allocation Migration Script in Dataload Professional FLD format
/*  EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS
    WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED, COPYRIGHT 2007
    THIS IS DESIGNED TO WORK WITH A 10 SEGMENT CHART OF ACCOUNTS, SO WILL NEED TO BE MODIFIED TO SUIT YOUR STRUCTURE
    THIS EXTRACT WILL ONLY WORK WITH THE FOLLOWING CONDITIONS
    1- THAT LINES B&C ARE ACCOUNTS RATHER THAN VALUES.  IF VALUES ARE USED THEN USE THE SECOND EXTRACT BELOW.
    2- THAT RELATIVE PERIOD IS CURRENT
    3- THAT AMOUNT TYPE IS ACTUAL
    */
SELECT GAB.NAME
,      GAF.NAME "FORMULA NAME"
,      'ALLOCATION'
,      GAF.DESCRIPTION "FORMULA DESC"
,      GAFL.SEGMENT1"S11"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,1,1)"T"
,      GAFL.SEGMENT2"S12"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)"T"
,      GAFL.SEGMENT3"S13"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)"T"
,      GAFL.SEGMENT4"S14"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)"T"
,      GAFL.SEGMENT5"S15"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)"T"
,      GAFL.SEGMENT6"S16"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)"T"
,      GAFL.SEGMENT7"S17"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)"T"
,      GAFL.SEGMENT8"S18"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)"T"
,      GAFL.SEGMENT9"S19"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)"T"
,      GAFL.SEGMENT10"S110"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)"T"
,      GAFL.CURRENCY_CODE"CURR"
,      GAFL.AMOUNT_TYPE"AMT TYPE"
,      GAFL2.SEGMENT1"S21"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,1,1)"T"
,      GAFL2.SEGMENT2"S22"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,3,1)"T"
,      GAFL2.SEGMENT3"S23"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,5,1)"T"
,      GAFL2.SEGMENT4"S24"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,7,1)"T"
,      GAFL2.SEGMENT5"S25"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,9,1)"T"
,      GAFL2.SEGMENT6"S26"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,11,1)"T"
,      GAFL2.SEGMENT7"S27"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,13,1)"T"
,      GAFL2.SEGMENT8"S28"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,15,1)"T"
,      GAFL2.SEGMENT9"S29"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,17,1)"T"
,      GAFL2.SEGMENT10"S210"
,      SUBSTR(GAFL2.SEGMENT_TYPES_KEY,19,1)"T"
,      GAFL2.CURRENCY_CODE"CURR"
,      GAFL2.AMOUNT_TYPE"AMT TYPE"
,      GAFL3.SEGMENT1"S31"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,1,1)"T"
,      GAFL3.SEGMENT2"S32"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,3,1)"T"
,      GAFL3.SEGMENT3"S33"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,5,1)"T"
,      GAFL3.SEGMENT4"S34"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,7,1)"T"
,      GAFL3.SEGMENT5"S35"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,9,1)"T"
,      GAFL3.SEGMENT6"S36"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,11,1)"T"
,      GAFL3.SEGMENT7"S37"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,13,1)"T"
,      GAFL3.SEGMENT8"S38"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,15,1)"T"
,      GAFL3.SEGMENT9"S39"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,17,1)"T"
,      GAFL3.SEGMENT10"S310"
,      SUBSTR(GAFL3.SEGMENT_TYPES_KEY,19,1)"T"
,      GAFL3.CURRENCY_CODE"CURR"
,      GAFL3.AMOUNT_TYPE"AMT TYPE"
,      GAFL4.SEGMENT1"S41"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,1,1)"T"
,      GAFL4.SEGMENT2"S42"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,3,1)"T"
,      GAFL4.SEGMENT3"S43"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,5,1)"T"
,      GAFL4.SEGMENT4"S44"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,7,1)"T"
,      GAFL4.SEGMENT5"S45"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,9,1)"T"
,      GAFL4.SEGMENT6"S46"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,11,1)"T"
,      GAFL4.SEGMENT7"S47"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,13,1)"T"
,      GAFL4.SEGMENT8"S48"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,15,1)"T"
,      GAFL4.SEGMENT9"S49"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,17,1)"T"
,      GAFL4.SEGMENT10"S410"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,19,1)"T"
,      GAFL4.CURRENCY_CODE"CURR"
,      GAFL5.SEGMENT1"S51"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,1,1)"T"
,      GAFL5.SEGMENT2"S52"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,3,1)"T"
,      GAFL5.SEGMENT3"S53"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,5,1)"T"
,      GAFL5.SEGMENT4"S54"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,7,1)"T"
,      GAFL5.SEGMENT5"S55"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,9,1)"T"
,      GAFL5.SEGMENT6"S56"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,11,1)"T"
,      GAFL5.SEGMENT7"S57"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,13,1)"T"
,      GAFL5.SEGMENT8"S58"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,15,1)"T"
,      GAFL5.SEGMENT9"S59"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,17,1)"T"
,      GAFL5.SEGMENT10"S510"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,19,1)"T"
FROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES  GAFL,GL_ALLOC_FORMULA_LINES GAFL2,GL_ALLOC_FORMULA_LINES GAFL3
     ,GL_ALLOC_FORMULA_LINES GAFL4,GL_ALLOC_FORMULA_LINES GAFL5
     , 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 GAF.ALLOCATION_FORMULA_ID = GAFL2.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL3.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL4.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL5.ALLOCATION_FORMULA_ID
AND GAFL.LINE_NUMBER =1
AND GAFL2.LINE_NUMBER =2
AND GAFL3.LINE_NUMBER =3
AND GAFL4.LINE_NUMBER =4
AND GAFL5.LINE_NUMBER =5
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('DE')
AND GAFL2.AMOUNT IS NULL
--AND GAB.NAME LIKE 'DE MAIN%'
ORDER BY 1,2



---==========================================================================================================================
GL Mass Allocation Rule Migration Script in Dataload Classic Format
/*  EXTRACT MASS ALLOCATIONS INTO A DATALOAD PROFESIONAL FORMAT FOR MIGRATION BETWEEN ENVIRONMENTS OR BOOKS
    WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED, COPYRIGHT 2007
    IT IS DESIGNED TO WORK WITH A 10 SEGMENT CHART OF ACCOUNTS, SO WILL NEED TO BE MODIFIED TO SUIT YOUR STRUCTURE
    THIS EXTRACT WILL ONLY WORK WITH THE FOLLOWING CONDITIONS
    1- THAT LINES B&C ARE VALUES NOT ACCOUNTS
    2- THAT RELATIVE PERIOD IS CURRENT
    3- THAT AMOUNT TYPE IS ACTUAL
    */
SELECT GAB.NAME
,      GAF.NAME "FORMULA NAME"
,      'ALLOCATION'
,      GAF.DESCRIPTION "FORMULA DESC"
,      GAFL.SEGMENT1"S11"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,1,1)"T"
,      GAFL.SEGMENT2"S12"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,3,1)"T"
,      GAFL.SEGMENT3"S13"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,5,1)"T"
,      GAFL.SEGMENT4"S14"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,7,1)"T"
,      GAFL.SEGMENT5"S15"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,9,1)"T"
,      GAFL.SEGMENT6"S16"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,11,1)"T"
,      GAFL.SEGMENT7"S17"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,13,1)"T"
,      GAFL.SEGMENT8"S18"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,15,1)"T"
,      GAFL.SEGMENT9"S19"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,17,1)"T"
,      GAFL.SEGMENT10"S110"
,      SUBSTR(GAFL.SEGMENT_TYPES_KEY,19,1)"T"
,      GAFL.CURRENCY_CODE"CURR"
,      GAFL.AMOUNT_TYPE"AMT TYPE"
,      GAFL2.AMOUNT "B-AMT"
,      GAFL3.AMOUNT "C-AMT"
,      GAFL4.SEGMENT1"S41"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,1,1)"T"
,      GAFL4.SEGMENT2"S42"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,3,1)"T"
,      GAFL4.SEGMENT3"S43"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,5,1)"T"
,      GAFL4.SEGMENT4"S44"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,7,1)"T"
,      GAFL4.SEGMENT5"S45"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,9,1)"T"
,      GAFL4.SEGMENT6"S46"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,11,1)"T"
,      GAFL4.SEGMENT7"S47"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,13,1)"T"
,      GAFL4.SEGMENT8"S48"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,15,1)"T"
,      GAFL4.SEGMENT9"S49"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,17,1)"T"
,      GAFL4.SEGMENT10"S410"
,      SUBSTR(GAFL4.SEGMENT_TYPES_KEY,19,1)"T"
,      GAFL4.CURRENCY_CODE"CURR"
,      GAFL5.SEGMENT1"S51"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,1,1)"T"
,      GAFL5.SEGMENT2"S52"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,3,1)"T"
,      GAFL5.SEGMENT3"S53"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,5,1)"T"
,      GAFL5.SEGMENT4"S54"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,7,1)"T"
,      GAFL5.SEGMENT5"S55"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,9,1)"T"
,      GAFL5.SEGMENT6"S56"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,11,1)"T"
,      GAFL5.SEGMENT7"S57"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,13,1)"T"
,      GAFL5.SEGMENT8"S58"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,15,1)"T"
,      GAFL5.SEGMENT9"S59"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,17,1)"T"
,      GAFL5.SEGMENT10"S510"
,      SUBSTR(GAFL5.SEGMENT_TYPES_KEY,19,1)"T"
FROM GL_ALLOC_BATCHES GAB, GL_ALLOC_FORMULAS GAF, GL_ALLOC_FORMULA_LINES  GAFL,GL_ALLOC_FORMULA_LINES GAFL2,GL_ALLOC_FORMULA_LINES GAFL3
     ,GL_ALLOC_FORMULA_LINES GAFL4,GL_ALLOC_FORMULA_LINES GAFL5
     , 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 GAF.ALLOCATION_FORMULA_ID = GAFL2.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL3.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL4.ALLOCATION_FORMULA_ID
AND GAF.ALLOCATION_FORMULA_ID = GAFL5.ALLOCATION_FORMULA_ID
AND GAFL.LINE_NUMBER =1
AND GAFL2.LINE_NUMBER =2
AND GAFL3.LINE_NUMBER =3
AND GAFL4.LINE_NUMBER =4
AND GAFL5.LINE_NUMBER =5
AND SUBSTR(FST.ID_FLEX_STRUCTURE_CODE,1,2) IN ('DE')
AND GAFL2.AMOUNT IS NOT NULL
--AND GAB.NAME LIKE 'DE MAIN%'
ORDER BY 1

GL ADI Journal Balances script
/* GL ADI JOURNAL OF OPENING BALANCES & MOVEMENTS
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
GIVES A TRIAL BALANCE IN ADI FORMAT FOR UPTO TEN SEGMENTS IN THE CHART OF ACCOUNTS WITH DEBIT AND CREDIT BALANCE.
THIS CAN BE USED TO EXTRACT GL BALANCES DATA FROM ONE ENVIRONMENT IN AND ADI JOURNAL FORMAT TO LOAD INTO ANOTHER ENVIRONMENT.
IT IS RECOMMENDED THAT THIS SCRIPT IS RUN FOR A SINGLE PERIOD AND BOOK FIRST TO GAUGE PERFORMANCE IN YOUR ENVIRONMENT.
(TESTED ON VISION 11.5.10.2  JUNE 2007 )*/
SELECT SOB.NAME
,      GB.PERIOD_NAME
,      GCC.SEGMENT1
,      GCC.SEGMENT2
,      GCC.SEGMENT3
,      GCC.SEGMENT4
,      GCC.SEGMENT5
,      GCC.SEGMENT6
,      GCC.SEGMENT7
,      GCC.SEGMENT8
,      GCC.SEGMENT9
,      GCC.SEGMENT10
,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) >= 0
            THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)))
            ELSE 0 END ) "DEBIT"   
,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <= 0
            THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))*-1)
            ELSE 0 END )     "CREDIT"
FROM GL_BALANCES GB
, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND  GB.ACTUAL_FLAG = 'A'
--AND  GB.PERIOD_NAME = 'DEC-05'
AND    GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('HK','JP','TH','SG','CN')
AND  GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
GROUP BY  SOB.NAME
,      GB.ACTUAL_FLAG
,      GB.PERIOD_NAME
,      GCC.SEGMENT1
,      GCC.SEGMENT2
,      GCC.SEGMENT3
,      GCC.SEGMENT4
,      GCC.SEGMENT5
,      GCC.SEGMENT6
,      GCC.SEGMENT7
,      GCC.SEGMENT8
,      GCC.SEGMENT9
,      GCC.SEGMENT10
,      NVL(GB.PERIOD_NET_DR,0)
,      NVL(GB.PERIOD_NET_CR,0)
HAVING SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <> 0
ORDER BY 1,2,3,4,5,6,7,8,9
GL Autopost Definitions
/* GL AUTOPOST DEFINITIONS
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
LISTS THE AUTOPOST DEFINITIONS BY BOOK SHOW THE JOURNAL SOURCE AND CATEGORY
TESTED ON VISION 11.5.10.2  JUNE 2007 */
SELECT SOB.NAME "BOOK"
,      APS.AUTOPOST_SET_NAME "SET NAME"
,      APS.DESCRIPTION
,      APS.ENABLED_FLAG
,      APS.SUBMIT_ALL_PRIORITIES_FLAG "SUBMIT ALL?"
,      APO.ACTUAL_FLAG "ACTUAL FLAG"
,      APO.PERIOD_NAME "PERIOD"
,      APO.JE_SOURCE_NAME "SOURCE"
,      APO.USER_JE_CATEGORY_NAME "CATEGORY"
FROM GL_AUTOMATIC_POSTING_SETS_V APS, GL_AUTOMATIC_POSTING_OPTIONS_V  APO, GL_SETS_OF_BOOKS SOB
WHERE APO.AUTOPOST_SET_ID = APS.AUTOPOST_SET_ID
AND APS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
--AND SUBSTR(SOB.NAME,1,2) IN ('ES','LU','BE')
ORDER BY 1
HR Operating Unit and Legal Entity Configuration
/* LEGAL ENTITIERS & ORGANIZATIONS
WRITTEN BY DANIEL NORTH, ORAFINAPPS LIMITED 2007
GIVES AN OVERVIEW OF THE LEGAL ENTITY AND OPERATING UNIT CONFIGURATION ACROSS MULTIPLE OU
THIS IS USED WHEN IMPLEMENTING MULTIPLE OFFICES TO ENSURE CONSISTENT SETUP ACROSS COUNTRIES AND BETWEEN ENVIRONMENTS.
WHERE CLAUSE CAN BE ADDED OR COMMENTED OUT TO JUST LOOK AT SPECIFIC COUNTRIES.IF CONSISTENT NAMING CONVENTIONS HAVE BEEN USED.
( TESTED ON VISION 11.5.10.2  JUNE 2007 )*/
SELECT HRO.ORGANIZATION_ID
,      HRO.NAME
,      HOI.ORG_INFORMATION_CONTEXT
,      SOB2.NAME "LE SET OF BOOKS"
--,    HOI.ORG_INFORMATION1
,      HRO_LE.NAME "OU LEGAL ENT"
,      HOI.ORG_INFORMATION2 "LE VAT CODES"
--,    HOI.ORG_INFORMATION3
,      SOB.NAME "OU SET OF BOOKS"
FROM   HR_ALL_ORGANIZATION_UNITS_TL HRO, HR_ORGANIZATION_INFORMATION_V  HOI,
GL_SETS_OF_BOOKS SOB,GL_SETS_OF_BOOKS SOB2, HR_ALL_ORGANIZATION_UNITS_TL HRO_LE
WHERE HOI.ORG_INFORMATION_CONTEXT IN ('LEGAL ENTITY ACCOUNTING','OPERATING UNIT INFORMATION')
AND HRO.ORGANIZATION_ID = HOI.ORGANIZATION_ID
AND TO_CHAR(SOB.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION3
AND TO_CHAR(SOB2.SET_OF_BOOKS_ID(+)) = HOI.ORG_INFORMATION1
AND TO_CHAR(HRO_LE.ORGANIZATION_ID(+)) = HOI.ORG_INFORMATION2
--AND SUBSTR(HRO.NAME,1,2) IN ('BE','LU','ES')
ORDER BY 2,3
Dataload .dld GL Cross Validation Rules
/* DATALOAD (DLD) FORMAT SQL EXTRACT OF CVR CROSS VALIDATION RULES
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
EXTRACTS CVR'S FROM ONE ENVIRONMENT IN A DATALOAD FORMAT READY TO LOAD INTO THE NEXT ENVIRONMENT USING DATALOAD CLASSIC.
NOTE : THE SEGMENTS LOW&HIGH SUBSTINGS WILL NEED UPDATING TO MATCH YOUR SPECIFIC CHART OF ACCOUNTS DEFINITIONS
(TESTED ON 11.5.9 MAR 2007 )*/
SELECT FST.ID_FLEX_STRUCTURE_NAME "BOOKS"
,      L.INCLUDE_EXCLUDE_INDICATOR"INC?"
,      R.FLEX_VALIDATION_RULE_NAME"NAME"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I'THEN R.FLEX_VALIDATION_RULE_NAME ELSE NULL END )"NAME"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN 'TAB' ELSE NULL END )"Z"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN   TL.DESCRIPTION ELSE NULL END )"DESCRIPTION"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN      'TAB' ELSE NULL END )"Z"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN      'TAB' ELSE NULL END )"Z"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN   TL.ERROR_MESSAGE_TEXT ELSE NULL END )"MESSAGE"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN      'TAB' ELSE NULL END )"Z"
,   (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"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN      'TAB' ELSE NULL END )"Z"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN      'TAB' ELSE NULL END )"Z"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN      'TAB' ELSE NULL END )"Z"
,   (CASE WHEN L.INCLUDE_EXCLUDE_INDICATOR = 'I' THEN      'TAB' ELSE NULL END )"Z"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,0,2)"1L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,0,2)"1H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,4,2)"2L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,4,2)"2H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,7,4)"3L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,7,4)"3H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,12,5)"4L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,12,5)"4H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,18,6)"5L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,18,6)"5H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,25,4)"6L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,25,4)"6H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,30,5)"7L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,30,5)"7H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,36,4)"8L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,36,4)"8H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,41,4)"9L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,41,4)"9H"
,      SUBSTR(L.CONCATENATED_SEGMENTS_LOW,46,4)"10L"
,      SUBSTR(L.CONCATENATED_SEGMENTS_HIGH,46,4)"10H"
,      'ENT'
,      '*SL3'
,      '*DN'
,      'TAB'
,      '*SL1' 
FROM   FND_FLEX_VALIDATION_RULES R,
       FND_FLEX_VDATION_RULES_TL TL,
       FND_FLEX_VALIDATION_RULE_LINES L,
       FND_ID_FLEX_STRUCTURES_VL FST
WHERE  R.APPLICATION_ID = TL.APPLICATION_ID
AND    FST.ID_FLEX_NUM = R.ID_FLEX_NUM
AND    R.ID_FLEX_CODE = TL.ID_FLEX_CODE
AND    R.ID_FLEX_NUM = TL.ID_FLEX_NUM
AND    R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND    R.FLEX_VALIDATION_RULE_NAME = TL.FLEX_VALIDATION_RULE_NAME
AND    R.APPLICATION_ID = L.APPLICATION_ID
AND    R.ID_FLEX_CODE = L.ID_FLEX_CODE
AND    R.ID_FLEX_NUM = L.ID_FLEX_NUM
AND    R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND    R.FLEX_VALIDATION_RULE_NAME = L.FLEX_VALIDATION_RULE_NAME
AND    R.APPLICATION_ID = 101
AND    R.ID_FLEX_CODE = 'GL#'
--AND    SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('BE','LU')
--AND    R.ERROR_SEGMENT_COLUMN_NAME = 'SEGMENT5'
--AND     TL.ERROR_MESSAGE_TEXT LIKE '%LOCAL%'
--AND    SUBSTR(L.CONCATENATED_SEGMENTS_LOW,1,2)='ZZ'
ORDER BY 1,3,2 DESC, 12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30


Dataload Professional .fld -- Daily Rates load
/* DAILY RATES DLD PROFESSIONAL LOAD (DATE RANGES )
WRITTEN BY DANIEL NORTH,  ORAFINAPPS LIMITED 2007
EXTRACTS SPECIFIC DAILY RATES IN A DATALOAD PROFESSIONAL FILE FORMAT .FLD
TO BE USED IN CONJUNCTION WITH A PREDEFINED .FLD FILE
(TESTED ON VISION 11.5.10.2  JUNE 2007 ) */
SELECT
      GLR.FROM_CURRENCY
,     GLR.TO_CURRENCY
,     GLR.CONVERSION_DATE"FROM"
,     GLR.CONVERSION_DATE"TO"
,     RT.USER_CONVERSION_TYPE
,     GLR.SHOW_CONVERSION_RATE "FROM > TO"
,     GLR.SHOW_INVERSE_CON_RATE " TO > FROM(INVERSE)"
FROM GL_DAILY_RATES_V GLR, FND_CURRENCIES C, GL_DAILY_CONVERSION_TYPES RT
WHERE GLR.FROM_CURRENCY = C.CURRENCY_CODE
AND GLR.CONVERSION_TYPE = RT.CONVERSION_TYPE
AND RT.USER_CONVERSION_TYPE LIKE 'CORPORATE'
--AND CONVERSION_DATE > TO_DATE('30-NOV-2002','DD-MON-YYYY')
--AND CONVERSION_DATE > TO_DATE('31-AUG-2006','DD-MON-YYYY')
--AND SUBSTR(GLR.CONVERSION_DATE,1,2) = '01'
--AND FROM_CURRENCY IN ('GBP')
--AND TO_CURRENCY NOT IN ('GBP')
--AND RT.USER_CONVERSION_TYPE LIKE 'THAI%'
ORDER BY 1,GLR.CONVERSION_DATE
- No comments:

Trigger for Button ( NEW, SAVE, FIND, CLEAR, EXIT )


To use Trigger:

Right click item > PL/SQL Editor
Select (User-named) > OK

*(User-named) - eg. for button NEW... right click button NEW > Property Palette , then change name from 'PUSH_BUTTON7' to 'BTN_NEW' . Then only Right click item > PL/SQL Editor > Select (User-named) > OK



Trigger for New and Clear Buttons

:course_block.course_name:=''; --assigne the null value to the course_name item on the course_block
:course_block.course_time:='';
:course_block.room:='';

Trigger for Save Button

begin
if :course_block.course_name is not null then
   insert into course (course_name, course_time, room)
   values (:course_block.course_name, :course_block.course_time, :course_block.room);
   message ('1 RECORD WAS INSERTED');
else
   message ('You need to specify a valid course name');
end if;
exception
   when dup_val_on_index then
   message ('A course with this name already exists.');
end;

Trigger for Save Button (Save, Commit, Clear)


begin
if :course_block.course_name is not null then
   insert into course (course_name, course_time, room)
   values (:course_block.course_name, :course_block.course_time, :course_block.room);
   :course_block.course_name:=''; --assigne the null value to the course_name item on the course_block
   :course_block.course_time:='';
   :course_block.room:='';
   message ('1 RECORD WAS INSERTED');
   commit;
else
   message ('You need to specify a valid course name');
end if;
exception
   when dup_val_on_index then
   message ('A course with this name already exists.');
end;


Trigger for Find Button

declare
   a course.course_name%type;
begin
   a:=:course_block.course_name;
   if a is not null then
      select course_name, course_time, room into :course_block.course_name, :course_block.course_time, :course_block.room
      from course where course.course_name=a;
   else
      message ('You need to specify a course name to be able to search courses');
   end if;
   exception
      when no_data_found then
      message ('A course with this name does not exist.');
      :course_block.course_name:='';
      :course_block.course_time:='';
      :course_block.room:='';
end;

Trigger for Exit Button

exit_form();
- No comments:

FireFox, IE crash with Forms 10g, Replace Jinitiator with JRE


From Oracle Forms, after start OC4J Instance, click Run Form icon.
This will open browser (chrome or i.e or firefox depend on default browser)

Fig: crash browser

url: http://vip-pc:8889/forms/frmservlet 
error: No plug-in available to display this content.

Solution: 
Resource: http://www.exploreoracle.com/2011/01/06/firefox-ie-crash-with-forms-10g-replace-jinitiator-with-jre/

Open formsweb.cfg i.e E:\DevSuiteHome_1\forms\server\formsweb.cfg
Replace 2 lines as below.

change from

# System parameter: default base HTML file
baseHTML=base.htm
# System parameter: base HTML file for use with JInitiator client
baseHTMLjinitiator=basejini.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
baseHTMLjpi=basejpi.htm
change to
Make changes as shown in bold face below.
# System parameter: default base HTML file
baseHTML=basejpi.htm
# System parameter: base HTML file for use with JInitiator client
baseHTMLjinitiator=basejpi.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
baseHTMLjpi=basejpi.htm

***ORIGINAL ARTICLE
FireFox, IE crash with Forms 10g, Replace Jinitiator with JRE
Oracle Jinitiator is no longer compatible with oracle forms services. By default, Oracle Forms services 10g uses Jinitiator 1.3.1.22 to run Forms applications. Jinitiator was compatible with Internet Explorer 6 and Firefox version 2.0. But latest internet explorer like IE 8 and 9 crash with Jinitiator. Similar behavior is seen with Firfox version 3.0 and later.

To resolve this problem, you must configure JRE to work with forms services 10g. It is really easy and requires only very little work. Forms services 10g are by default configured to run with JRE 1.4.2_06. To get this working, locate following lines in your FORMSWEB.CFG file,

# System parameter: default base HTML file
baseHTML=base.htm
# System parameter: base HTML file for use with JInitiator client
baseHTMLjinitiator=basejini.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
baseHTMLjpi=basejpi.htm

Make changes as shown in bold face below.

# System parameter: default base HTML file
baseHTML=basejpi.htm
# System parameter: base HTML file for use with JInitiator client
baseHTMLjinitiator=basejpi.htm
# System parameter: base HTML file for use with Sun’s Java Plug-In
baseHTMLjpi=basejpi.htm

Make sue that following parmaments in FORMSWEB.CFG are as mentioned below.

jpi_download_page=http://java.sun.com/products/archive/j2se/1.4.2_06/index.html
jpi_classid=clsid:CAFEEFAC-0014-0002-0006-ABCDEFFEDCBA
jpi_codebase=http://java.sun.com/products/plugin/autodl/jinstall-1_4_2-windows-i586.cab#Version=1,4,2,06
jpi_mimetype=application/x-java-applet;jpi-version=1.4.2_06

Now install J2RE version 1.4.2_06  from following URL,

http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase14-419411.html#j2re-1.4.2_06-oth-JPR



OR

you can also omit version no from following line,

jpi_mimetype=application/x-java-applet;jpi-version=1.4.2_06

after ommiting version, above line will look like following

jpi_mimetype=application/x-java-applet

Now you can use any latest JRE version. (Thanks to S Shamji  and JoeAStudent for thier comments related to version change )

Remove Jinitiator from you computer and restart OC4J instance. Now your Forms application 10g will use Sun’s Java plug-in instead of Jinitiator.

Update:

Looks like Firefox 12 does not work by omitting version no from formsweb.cfg. Instead you need to provide correct installed version.

For example, If you have jre 1.6.x installed in your system then edit following line as under.

jpi_mimetype=application/x-java-applet;jpi-version=1.6

The above line covers JRE 6 with all update levels.

If still facing problem then provide the exact version of  JRE with update level as under.

jpi_mimetype=application/x-java-applet;jpi-version=1.6.0_24

In “formsweb.cfg”,  jpi_classid indicates the version of JRE. “clsid:CAFEEFAC-0014-0002-0006-ABCDEFFEDCBA” indicates that 1.4.2_06 or any later version can be used.
- No comments:

Tuesday, May 28, 2019

Create Bulk GL Code Combinations Using API


DECLARE

CURSOR c1
   IS
   SELECT  a.rowid row_id,a.*
FROM    temp_gl_code a
WHERE   process_flag= 'N';
   
     
  l_segment1                 GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2                 GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3                 GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4                 GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5                 GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6                 GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination        BOOLEAN;
  l_cr_combination           BOOLEAN;
  l_ccid                     GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num            FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs                GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1               VARCHAR2(240);
  p_error_msg2               VARCHAR2(240);
  v_error_flag               BOOLEAN;
  v_error_msg                VARCHAR2(3000) :=NULL;
  v_count1                   NUMBER;
 v_count2                    NUMBER;
 v_row_count                 NUMBER := 0;
 v_req_message               VARCHAR2(3000);
 v_req_id                    NUMBER;
 v_req_status                BOOLEAN;
 v_rphase                    VARCHAR2(20);
 v_rstatus                   VARCHAR2(20);
 v_dphase                    VARCHAR2(20);
 v_dstatus                   VARCHAR2(20);
  -- ----------------------
--START OF VALIDATION
-- ----------------------
BEGIN

 FOR i in c1

   LOOP
     v_error_flag := FALSE;
     v_error_msg := NULL;
 
  l_segment1  := i.segment1;
  l_segment2  := i.segment2;
  l_segment3  := i.segment3;
  l_segment4  := i.segment4;
  l_segment5  := i.segment5;
  l_segment6  := i.segment6;
  l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;



  BEGIN
    SELECT
    id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code        = 'GL#'
       AND id_flex_structure_code='COVAD_ACCOUNTING_FLEXFIELD';
  EXCEPTION
  WHEN OTHERS THEN
    l_structure_num:=NULL;
  END;

  ---------------Check if CCID exits with the above Concatenated Segments---------------
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
  WHEN OTHERS THEN
    l_ccid         :=NULL;
  END;
 
    -- -------------------------------------------------------------------------------
-- UPDATING THE PROCESS FLAG FOR ERRORS IN temp_gl_code TABLE
-- -------------------------------------------------------------------------------
    IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------
    --DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);
    v_error_msg := v_error_msg ||'Combination already Exists '|| '; ' ;
 
        UPDATE  temp_gl_code
        SET process_flag                = 'E'
        ,errror_message                 = 'Validation Error:'||v_error_msg
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'N' ;
COMMIT;
    ELSE
 
DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CHECK_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS
                          );
    p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;
 
    BEGIN
        UPDATE  temp_gl_code
        SET process_flag                = 'E'
        ,errror_message                  = FND_FLEX_KEYVAL.ERROR_MESSAGE
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'N' ;
COMMIT;
    END;

    IF l_valid_combination then

      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------

      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CREATE_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS );
       
          p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;
       
       
   IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
        WHERE concatenated_segments = l_conc_segs;
     
        UPDATE  temp_gl_code
        SET process_flag                = 'S'
        ,errror_message                  = 'Import Success='|| l_ccid
        ,last_update_date               = SYSDATE
        WHERE rowid                     = i.row_id
        AND process_flag                = 'E' ;
    COMMIT;
    ELSE
        -------------Error in creating a combination-----------------
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);
    END IF;
  END IF;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;
- No comments:

Drill Down Query From GL into Project Accounting subledger

The below query will  provide the complete breakup of posted Journals with transaction distribution wise breakup details of Project Accounting sub-ledger modules.

SELECT      GLCC.concatenated_segments segments
                    ,GJL.period_name
                    ,GJH.name journal_name
                    ,GJB.name batch_name
                    ,GJH.je_source journal_source
                    ,GJH.je_category journal_category
                    ,GLCC.segment1 entity_segment
                    ,GLCC.segment2 project_segment
                    ,FFV.attribute1 project_vertical_dff
                    ,GLCC.segment3
                    ,GLCC.segment4
                    ,GLCC.segment5
                    ,GLCC.segment6
                    ,GLCC.segment7
                    ,TO_CHAR (GJH.doc_sequence_value) gl_doc_no
                    ,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date
                    ,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date
                    ,PE.expenditure_group document_number
                    ,PE.expenditure_group document_description
                    ,TO_CHAR (PEI.expenditure_item_date, 'DD-MON-YYYY')  document_date
--                  ,PPA.project_status_code document_status     
                    ,PPA.segment1 project_code 
                    ,PT.task_number task_code        
                    ,PEI.expenditure_type  
                    ,PEI.raw_cost transaction_cur_amount
                    ,PPA.project_currency_code entered_currency_code
                    ,PPA.project_rate_type exchange_rate_type
                    ,PEI.project_exchange_rate exchange_rat
   FROM       gl_je_batches GJB
                    ,gl_je_headers GJH
                    ,gl_je_lines   GJL
                    ,gl_code_combinations_kfv GLCC
                    ,gl_import_references GIR
                    ,xla_ae_headers  XAH
                    ,xla_ae_lines    XAL
                    ,xla_events      XE
                    ,xla_distribution_links XDL
                    ,pa_cost_distribution_lines_all PDL
                    ,pa_expenditure_items_all PEI
                    ,pa_expenditures_all PE
                    ,pa_tasks PT
                    ,pa_projects_all PPA
                    ,fnd_flex_value_sets FVS
                    ,fnd_flex_values FFV
         WHERE  GJB.je_batch_id         = GJH.je_batch_id          
         AND    GJH.je_header_id        = GJL.je_header_id
         AND    GJL.code_combination_id = GLCC.code_combination_id
         AND    GJL.je_header_id        = GIR.je_header_id
         AND    GJH.je_batch_id         = GIR.je_batch_id
         AND    GJL.je_line_num         = GIR.je_line_num
         AND    GIR.gl_sl_link_id       = XAL.gl_sl_link_id
         AND    GIR.gl_sl_link_table    = XAL.gl_sl_link_table
         AND    XAH.ae_header_id        = XAL.ae_header_id
         AND    XAH.application_id      = XAL.application_id
         AND    XAH.event_id            = XE.event_id
         AND    XAL.ae_header_id        = XDL.ae_header_id
         AND    XAL.ae_line_num         = XDL.ae_line_num
         AND    XDL.source_distribution_id_num_1 = PDL.expenditure_item_id 
         AND    PDL.expenditure_item_id  = PEI.expenditure_item_id
         AND    PEI.expenditure_id       = PE.expenditure_id
         AND    PEI.task_id              = PT.task_id
         AND    PT.project_id            = PPA.project_id
         AND    FFV.flex_value_set_id    = FVS.flex_value_set_id(+)
         AND    GLCC.segment2            = FFV.flex_value(+) 
         AND UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')
    --  AND    XAH.entity_id        = XTE.entity_id
    --  AND xte.application_id = 275
        AND    GJH.je_source               = 'Project Accounting'
        AND    GJH.je_category             = 'Miscellaneous Transaction'
        AND    GJH.status                  = 'P' 
        AND    GJH.default_effective_date >= lc_gl_date_from
        AND    GJH.default_effective_date <= lc_gl_date_to
        AND    TRUNC (GJH.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to ,TRUNC (GJH.posted_date))
        AND    GJH.je_source               = NVL (p_gl_source, GJH.je_source)
        AND    GJH.je_category             = NVL (p_gl_category, GJH.je_category)
        --        AND    GLCC.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)
        AND    GLCC.segment1 BETWEEN  lc_segment_from(1) AND  lc_segment_to(1)
        AND    GLCC.segment2 BETWEEN  lc_segment_from(2) AND  lc_segment_to(2)
        AND    GLCC.segment3 BETWEEN  lc_segment_from(3) AND  lc_segment_to(3)
        AND    GLCC.segment4 BETWEEN  lc_segment_from(4) AND  lc_segment_to(4)
        AND    GLCC.segment5 BETWEEN  lc_segment_from(5) AND  lc_segment_to(5)
        AND    GLCC.segment6 BETWEEN  lc_segment_from(6) AND  lc_segment_to(6)
        AND    GLCC.segment7 BETWEEN  lc_segment_from(7) AND  lc_segment_to(7)

        AND    NVL (FFV.attribute1, '-1')      = NVL (p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))
- No comments:

GL DRILL Down Query From GL into Fixed Assets Sub ledger Module

The below query will  provide the complete breakup of posted Journals with transaction distribution wise breakup details of Fixed assets(all sources) sub-ledger modules.

SELECT  GCC.concatenated_segments segments,
               GJL.period_name,
               GJH.NAME journal_name,
               GJB.NAME batch_name,
               GJH.je_source journal_source,
               GJH.je_category journal_category,
               GCC.segment1 entity_segment,
               GCC.segment2 project_segment,
               FFV.attribute1 project_vertical_dff,
               GCC.segment3,
               GCC.segment4,
               GCC.segment5,
               GCC.segment6,
               GCC.segment7,
               TO_CHAR (GJH.doc_sequence_value) gl_doc_no,
               TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date,
               TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date,
               NULL sla_event_class,
               XAH.event_type_code sla_event_type,
               TO_CHAR (FTH.asset_id) document_number,
               FT.description document_description,
               TO_CHAR (FB.date_placed_in_service, 'DD-MON-YYYY') document_date,
       NVL ((XAL.unrounded_entered_cr) * -1,XAL.unrounded_entered_dr) transaction_cur_amount,
               GL.currency_code entered_currency_code,
  NVL((XAL.unrounded_accounted_cr)*-1,XAL.unrounded_accounted_dr) functional_currency_amount
  FROM   xla_ae_lines XAL,
               xla_ae_headers XAH,
               xla.xla_transaction_entities XTE,
               gl_je_headers GJH,
               gl_je_lines GJL,
               gl_je_batches GJB,
               gl_import_references GIR,
               gl_je_categories GJC,
               gl_ledgers GL,
               gl_code_combinations_kfv GCC,
               fa_additions_tl FT,
               fa_books FB,
               fa_transaction_headers FTH,
               fnd_flex_value_sets FVS,
               fnd_flex_values FFV
 WHERE         1 = 1
   AND         GJL.je_header_id                     = GJH.je_header_id
   AND         GJC.je_category_name            = GJH.je_category
   AND         GCC.code_combination_id         = GJL.code_combination_id
   AND         GIR.je_header_id                     = GJH.je_header_id
   AND         XAL.gl_sl_link_id                      = GIR.gl_sl_link_id
   AND         GIR.je_line_num                       = GJL.je_line_num
   AND         GJB.je_batch_id                       = GJH.je_batch_id
   AND         XAH.ae_header_id                   = XAL.ae_header_id
   AND         XTE.entity_id                            = XAH.entity_id
   AND         FB.date_ineffective  IS NULL
   AND         GJH.actual_flag                        = 'A'
   AND         GJH.status                                = 'P'
   AND         GJH.je_source                          = 'Assets'
   AND         GJC.user_je_category_name  IN   ('Transfer', 'Reclass')
   AND         GJH.je_source                         <> 'Consolidation'
   AND         FT.asset_id                                = FTH.asset_id
   AND         FT.asset_id                                =  FB.asset_id
   AND         XTE.source_id_int_1(+)            = FTH.transaction_header_id
   AND         FB.book_type_code                  = FTH.book_type_code
   AND         GJH.ledger_id                           = GL.ledger_id
   AND         FFV.flex_value_set_id               = FVS.flex_value_set_id(+)
   AND         GCC.segment2                          = FFV.flex_value(+)
   AND         UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')
   AND         GJH.default_effective_date     >= lc_gl_date_from
   AND         GJH.default_effective_date     <= lc_gl_date_to
 AND TRUNC (gjh.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (gjh.posted_date))AND NVL (lc_gl_posted_to,TRUNC (gjh.posted_date))
   AND          GJH.je_source                  = NVL (p_gl_source, GJH.je_source)
   AND          GJH.je_category                = NVL (p_gl_category, GJH.je_category)
   AND      gcc.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)
   AND          GCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)
   AND          GCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)
   AND          GCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)
   AND          GCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)
   AND          GCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)
   AND          GCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)
   ---AND          GCC.segment7 BETWEEN lc_segment_from(7) AND lc_segment_to(7)
   --AND          NVL (FFV.attribute1, '-1')      = NVL (:p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))
UNION ALL
SELECT         GCC.concatenated_segments segments,
               GJL.period_name,
               GJH.NAME journal_name,
               GJB.NAME batch_name,
               GJH.je_source journal_source,
               GJH.je_category journal_category,
               GCC.segment1 entity_segment,
               GCC.segment2 project_segment,
               FFV.attribute1 project_vertical_dff,
               GCC.segment3,
               GCC.segment4,
               GCC.segment5,
               GCC.segment6,
               GCC.segment7,
               TO_CHAR (GJH.doc_sequence_value) gl_doc_no,
               TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date,
               TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date,
               NULL sla_event_class,
               XAH.event_type_code sla_event_type,
               TO_CHAR (FTH.asset_id) document_number,
               FT.description document_description,
               TO_CHAR (FB.date_placed_in_service, 'DD-MON-YYYY') document_date,
        NVL ((XAL.unrounded_entered_cr) * -1,XAL.unrounded_entered_dr) transaction_cur_amount,
               GL.currency_code entered_currency_code,
               NVL ((XAL.unrounded_accounted_cr) * -1,XAL.unrounded_accounted_dr) functional_currency_amount
  FROM         xla_ae_lines XAL,
               xla_ae_headers XAH,
               xla.xla_transaction_entities XTE,
               gl_je_headers GJH,
               gl_je_lines GJL,
               gl_je_batches GJB,
               gl_import_references GIR,
               gl_je_categories GJC,
               gl_ledgers GL,
               gl_code_combinations_kfv GCC,
               fa_additions_tl FT,
               fa_books FB,
               fa_transaction_headers FTH,
               fnd_flex_value_sets FVS,
               fnd_flex_values FFV
 WHERE         1 = 1
   AND         GJL.je_header_id                           = GJH.je_header_id
   AND         GJC.je_category_name                  = GJH.je_category
   AND         GCC.code_combination_id             = GJL.code_combination_id
   AND         GIR.je_header_id                           = GJH.je_header_id
   AND         XAL.gl_sl_link_id                            = GIR.gl_sl_link_id
   AND         GIR.je_line_num                            = GJL.je_line_num
   AND         GJB.je_batch_id                            = GJH.je_batch_id
   AND         XAH.ae_header_id                        = XAL.ae_header_id
   AND         XTE.entity_id                                 = XAH.entity_id
   AND         GJH.ledger_id                                = GL.ledger_id
   AND         GJH.actual_flag                             = 'A'
   AND         GJH.status                                     = 'P'
   AND         GJH.je_source                               = 'Assets'
   AND         FB.date_ineffective    IS NULL
   AND         GJC.user_je_category_name    IN('Addition', 'CIP Addition', 'Adjustment', 'CIP Adjustment','Retirement','CIP Retirement')
   AND         GJH.je_source                              <> 'Consolidation'
   AND         FT.asset_id                                      = FTH.asset_id
   AND         FT.asset_id                                      = FB.asset_id
   AND         XTE.source_id_int_1(+)                   = FTH.transaction_header_id
   AND         FB.book_type_code                         = FTH.book_type_code
   AND         GJH.default_effective_date            >= lc_gl_date_from
   AND         GJH.default_effective_date            <= lc_gl_date_to
   AND         FFV.flex_value_set_id                      = FVS.flex_value_set_id(+)
   AND         GCC.segment2                                 = FFV.flex_value(+)
   AND         UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')
   AND         TRUNC (GJH.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to,TRUNC (GJH.posted_date))
   AND          GJH.je_source                  = NVL (p_gl_source, GJH.je_source)
   AND          GJH.je_category                = NVL (p_gl_category, GJH.je_category)
--   AND      gcc.concatenated_segments BETWEEN (p_account_from) AND (p_account_to)
   AND          GCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)
   AND          GCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)
   AND          GCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)
   AND          GCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)
   AND          GCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)
   AND          GCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)
   AND          GCC.segment7 BETWEEN lc_segment_from(7) AND lc_segment_to(7)
  --- AND          NVL (FFV.attribute1, '-1')      = NVL (:p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))
   UNION ALL
   SELECT            GLCC.concatenated_segments segments
                    ,GJL.period_name
                    ,GJH.name journal_name
                    ,GJB.name batch_name
                    ,GJH.je_source journal_source
                    ,GJH.je_category journal_category
                    ,GLCC.segment1 entity_segment
                    ,GLCC.segment2 project_segment
                    ,FFV.attribute1 project_vertical_dff
                    ,GLCC.segment3
                    ,GLCC.segment4
                    ,GLCC.segment5
                    ,GLCC.segment6
                    ,GLCC.segment7
                    ,TO_CHAR (GJH.doc_sequence_value) gl_doc_no
                    ,TO_CHAR (GJH.default_effective_date, 'DD-MON-YYYY') gl_date
                    ,TO_CHAR (GJH.posted_date, 'DD-MON-YYYY') posted_date
                    ,(SELECT NAME
                             FROM xla_event_classes_tl XECT
                            WHERE XECT.event_class_code = XDL.event_class_code
                              AND XECT.application_id = XDL.application_id) sla_event_class
                    ,XAH.event_type_code sla_event_type
                    ,TO_CHAR(FDS.asset_id) document_number
                    ,FAD.description document_description
                    ,TO_CHAR (FB.date_placed_in_service, 'DD-MON-YYYY') document_date
                    ,NVL((XDL.unrounded_accounted_cr)*-1,(XDL.unrounded_accounted_dr)) transaction_cur_amount
                    ,GL.currency_code entered_currency_code
                    ,NVL((XDL.unrounded_accounted_cr)*-1,(XDL.unrounded_accounted_dr)) functional_currency_amount
  FROM         fa_additions FAD
                    ,fa_books FB
                    ,fa_book_controls FBC
                    ,fa_deprn_summary FDS
                    ,xla_distribution_links XDL
                    ,xla_ae_lines XAL
                    ,xla_ae_headers XAH
                    ,gl_import_references GIR
                    ,gl_je_lines GJL
                    ,gl_je_headers GJH
                    ,gl_je_batches GJB
                    ,gl_code_combinations_kfv GLCC
                    ,gl_ledgers GL
                    ,fnd_flex_value_sets FVS
                    ,fnd_flex_values FFV
 WHERE      1=1
   AND      FAD.asset_id                             = FB.asset_id
   AND      FB.book_type_code                  = FBC.book_type_code
   AND      FB.DATE_INEFFECTIVE IS NULL
   AND      FAD.asset_id                             = FDS.asset_id
   AND      FB.book_type_code                  =  FDS.book_type_code
   AND      FBC.book_type_code               = FDS.book_type_code
   AND      fds.deprn_source_code            ='DEPRN'
   AND      FDS.asset_id                            = XDL.source_distribution_id_num_1
   AND      FB.book_type_code                 = XDL.source_distribution_id_char_4
   AND      FBC.set_of_books_id               = GL.ledger_id
   AND      XDL.ae_header_id                   = XAL.ae_header_id
   AND      XDL.ae_line_num                    = XAL.ae_line_num
   AND      XAL.ae_header_id                   = XAH.ae_header_id
   AND      XAH.application_id                  = XAL.application_id
   AND      XAL.gl_sl_link_id                      = GIR.gl_sl_link_id
   AND      XAL.gl_sl_link_table                 = GIR.gl_sl_link_table
   AND      GIR.je_line_num                      = GJL.je_line_num
   AND      GIR.je_header_id                    = GJL.je_header_id
   AND      GIR.je_header_id                    = GJH.je_header_id
   AND      GJH.je_batch_id                      = GJB.je_batch_id
   AND      GJL.code_combination_id       = GLCC.code_combination_id
   AND      GJH.status                               = 'P'
-- and UPPER(XDL.rounding_class_code) ='ASSET'
   AND      UPPER(FDS.deprn_source_code) IN ('BOOKS','DEPRN')
   AND      UPPER (GJH.je_source)                  = 'ASSETS'
   AND      UPPER (GJH.je_category)               = 'DEPRECIATION'
   AND      FFV.flex_value_set_id                      = FVS.flex_value_set_id(+)
   AND      GLCC.segment2                               = FFV.flex_value(+)
   AND      UPPER (FVS.flex_value_set_name) = UPPER ('PLL_Project')
   AND      GJH.default_effective_date     >= lc_gl_date_from
   AND      GJH.default_effective_date     <= lc_gl_date_to
   AND      TRUNC (GJH.posted_date) BETWEEN NVL (lc_gl_posted_from,TRUNC (GJH.posted_date))AND NVL (lc_gl_posted_to,TRUNC (GJH.posted_date))
   AND      GJH.je_source                   = NVL (p_gl_source, GJH.je_source)
   AND      GJH.je_category                 = NVL (p_gl_category, GJH.je_category)
   AND      GLCC.segment1 BETWEEN lc_segment_from(1) AND lc_segment_to(1)
   AND      GLCC.segment2 BETWEEN lc_segment_from(2) AND lc_segment_to(2)
   AND      GLCC.segment3 BETWEEN lc_segment_from(3) AND lc_segment_to(3)
   AND      GLCC.segment4 BETWEEN lc_segment_from(4) AND lc_segment_to(4)
   AND      GLCC.segment5 BETWEEN lc_segment_from(5) AND lc_segment_to(5)
   AND      GLCC.segment6 BETWEEN lc_segment_from(6) AND lc_segment_to(6)
   AND      GLCC.segment7 BETWEEN lc_segment_from(7) AND lc_segment_to(7)
   ---AND      NVL (FFV.attribute1, '-1')      = NVL (:p_proj_vertical_dff, NVL (FFV.attribute1, '-1'))

- No comments:
Subscribe to: Posts (Atom)

Accounts Receivable-AR setups in Oracle EBS Functional Financials - Apps R12

   1. Oracle Receivables -> Setup -> System ->System Options     a. Receivable GL related setup    b. Transaction and Customer rela...