General Ledger Useful SQL ScriptsOracle Applications 11i.docx
《General Ledger Useful SQL ScriptsOracle Applications 11i.docx》由会员分享，可在线阅读，更多相关《General Ledger Useful SQL ScriptsOracle Applications 11i.docx（55页珍藏版）》请在冰豆网上搜索。
GeneralLedgerUsefulSQLScriptsOracleApplications11i
GeneralLedgerUsefulSQLScripts–OracleApplications11i
Contents
GLSetofBooksConfigurationOverview1
GLSummaryAccountTemplateDefinitionReview2
GLSegmentValueListing3
GLPeriodStatus3
GLChartofAccountsStructure4
GLChartofAccountsStructureOverview4
GLJournalHeaderSummary5
GLJournalLineBasedTrialBalanceReport5
GLJournalLinesWithAPSourceReferenceFields6
GLMassAllocationRuleMigrationScriptinDataloadClassicFormat7
GLBalancesandMovements8
GLChartofAccountSegmentHierarchyRanges9
GLCodeCombinationsCCIDs9
GLCVRCrossValidationRuleDetailListing10
GLCVRCrossValidationRuleOverview11
GLFlexfieldSecurityRuleAssignments11
GLFlexfieldSecurityRuleDefinitions11
GLFSGReportandComponentsOverview12
GLInterfaceDetails13
GLInterfaceSummary13
GLMassAllocationFormulareviewscript14
GLMassAllocationMigrationScriptinDataloadProfessionalFLDformat14
GLMassAllocationRuleMigrationScriptinDataloadClassicFormat17
GLADIJournalBalancesscript18
GLAutopostDefinitions19
HROperatingUnitandLegalEntityConfiguration19
Dataload.dldGLCrossValidationRules20
DataloadProfessional.fld--DailyRatesload21
GLSetofBooksConfigurationOverview
/*SETOFBOOKSCONFIGURATIONOVERVIEW
WRITTENBYDANIELNORTH,ORAFINAPPSLIMITED2007
THISSQLGIVESANOVERVIEWOFTHESETOFBOOKDEFINITIONSANDCANBEUSEDWHENIMPLEMENTINGMULTIPLESETSOFBOOKS
TOENSURECONSISTENTSETUPACROSSCOUNTRIESANDBETWEENENVIRONMENTS.
WHERECLAUSECANBEADDEDORCOMMENTEDOUTTOJUSTLOOKATSPECIFICCOUNTRIES.*/
SELECTSOB.SET_OF_BOOKS_ID"ID"
SOB.NAME
SOB.SHORT_NAME
SOB.DESCRIPTION
SOB.CHART_OF_ACCOUNTS_ID"COAID"
FST.ID_FLEX_STRUCTURE_CODE"CHARTOFACCOUNTS"
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"LATESTOPEN"
SOB.ATTRIBUTE1"OPERATIONALBOOK"
SOB.ATTRIBUTE2"PPL?
"
SOB.ENABLE_REVAL_SS_TRACK_FLAG||'.'||ENABLE_SECONDARY_TRACK_FLAG"SECSEGTRACK?
"
RET.SEGMENT1||'-'||RET.SEGMENT2||'-'||RET.SEGMENT3||'-'||RET.SEGMENT4||'-'||RET.SEGMENT5||'-'||RET.SEGMENT6"RETAINEDEARNINGS"
TRAN.SEGMENT1||'-'||TRAN.SEGMENT2||'-'||TRAN.SEGMENT3||'-'||TRAN.SEGMENT4||'-'||TRAN.SEGMENT5||'-'||TRAN.SEGMENT6"TRANEARNINGS"
'---JOURNALS---'
SOB.ALLOW_INTERCOMPANY_POST_FLAG"INTERCO?
"
SOB.ENABLE_JE_APPROVAL_FLAG"JRNLAPP?
"
SOB.ENABLE_AUTOMATIC_TAX_FLAG"AUTOTAX?
"
SOB.SUSPENSE_ALLOWED_FLAG"SUSP?
"
SOB.TRACK_ROUNDING_IMBALANCE_FLAG"TRKRND?
"
'---AVBAL---'
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"NOTUSED"
'---BUDGETCNTL---'
SOB.ENABLE_BUDGETARY_CONTROL_FLAG||SOB.REQUIRE_BUDGET_JOURNALS_FLAG||SOB.RES_ENCUMB_CODE_COMBINATION_ID"NOTUSED"
'---MRC---'
SOB.MRC_SOB_TYPE_CODE"NOTUSED"
FROMGL_SETS_OF_BOOKSSOB,FND_ID_FLEX_STRUCTURESFST,GL_CODE_COMBINATIONSTRAN,GL_CODE_COMBINATIONSRET,GL_PERIOD_TYPESPT
WHEREFST.ID_FLEX_NUM=SOB.CHART_OF_ACCOUNTS_ID
ANDRET.CODE_COMBINATION_ID（+）=SOB.RET_EARN_CODE_COMBINATION_ID
ANDTRAN.CODE_COMBINATION_ID（+）=SOB.CUM_TRANS_CODE_COMBINATION_ID
ANDPT.PERIOD_TYPE=SOB.ACCOUNTED_PERIOD_TYPE
--ANDSUBSTR（SOB.SHORT_NAME,1,2）IN（'BE','LU','ES','IT','HU','CZ','PL','RU'）
ORDERBY2
GLSummaryAccountTemplateDefinitionReview
/*GLSUMMARYTEMPLATEDEFINITIONS
WRITTENBYDANIELNORTH,ORAFINAPPSLIMITED,COPYRIGHT2007
SMALLSCRIPTSHOWINGSUMMARYTEMPLATECONFIGURATIONACROSSMULTIPLEBOOKS,
（TESTEDONVISION11.5.10.2JUL-2007）*/
SELECTSOB.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"SEGMENTTYPE"
FROMGL_SUMMARY_TEMPLATESST,GL_SETS_OF_BOOKSSOB
WHEREST.SET_OF_BOOKS_ID=SOB.SET_OF_BOOKS_ID
--ANDSUBSTR（SOB.NAME,1,2）IN（'ES','BE','LU'）
GLSegmentValueListing
/*SEGMENTVALUESETLISTINGS
WRITTENBYDANIELNORTH,ORAFINAPPSLIMITED2007
LISTSSINGLEORMULTIPLESEGMENTVALUESETS.THISISUSEDTOPERFORMAQAONCHARTOFACCOUNTSVALUES.
EXAMPLESOFOPTIONALWHERECLAUSESHAVEALSOBEENPROVIDEDBELOW.
（TESTEDONVISION11.5.10.2JUNE2007）*/
SELECTFFVS1.FLEX_VALUE_SET_NAME
--,FFVS1.FLEX_VALUE_SET_ID
FFVAL1.FLEX_VALUE"VALUE"
FFVAL1.SUMMARY_FLAG"PARENTACC?
"
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"
--SELECTDISTINCTFFVS1.FLEX_VALUE_SET_NAME
FFVAL1.LAST_UPDATED_BY
FFVAL1.LAST_UPDATE_DATE
FROMFND_FLEX_VALUESFFVAL1
FND_FLEX_VALUES_TLFFVTL1
FND_FLEX_VALUE_SETSFFVS1
FND_ID_FLEX_SEGMENTSSEG
FND_FLEX_HIERARCHIES_VLFH
WHEREFFVAL1.FLEX_VALUE_SET_ID（+）=FFVS1.FLEX_VALUE_SET_ID
ANDSEG.FLEX_VALUE_SET_ID=FFVS1.FLEX_VALUE_SET_ID
ANDSEG.ID_FLEX_NUM=51974/*COAIDISNEEDEDIFSEGMENTISCHARTINMULTPLECOA.UPDATEFORYOUCONFIGURATIONORREMOVEIFNOTAPPLICABLE.*/
ANDFFVAL1.FLEX_VALUE_ID=FFVTL1.FLEX_VALUE_ID（+）
ANDFFVS1.FLEX_VALUE_SET_NAME='OPERATIONSACCOUNT'
ANDFFVAL1.STRUCTURED_HIERARCHY_LEVEL=FH.HIERARCHY_ID（+）
--ANDSUBSTR（TO_CHAR（FFVAL1.COMPILED_VALUE_ATTRIBUTES）,7,1）!
='N'--NON-CONTROLACCOUNTSONLY
--ANDSUBSTR（TO_CHAR（FFVAL1.COMPILED_VALUE_ATTRIBUTES）,7,1）='Y'--CONTROLACCOUNTSONLY
--ANDFFVAL1.SUMMARY_FLAG='Y'
--ANDFFVAL1.FLEX_VALUE>='8000'
--ANDFFVAL1.FLEX_VALUE<='99999'
--ANDFFVTL1.DESCRIPTIONLIKE'%FTE%'
--ANDFFVAL1.FLEX_VALUELIKE'16%'
ORDERBYFFVS1.FLEX_VALUE_SET_NAME,FFVAL1.FLEX_VALUE
GLPeriodStatus
/*GLPERIODSTATUSES
WRITTENBYDANIELNORTH,ORAFINAPPSLIMITED,COPYRIGHT2007
TWOSMALLSCRIPTSFORREVIEWINGOPENPERIODSACROSSMULTIPELBOOKS.（MONTHENDCLOSECHECKINGORAUTOMATEDALERTS）
ANDPERIODSTATUSFORAGIVENYEARANDBOOK.
（TESTEDONVISION11.5.10.2JUL-2007）*/
SELECTSOB.SHORT_NAME
PS.PERIOD_NAME
PS.SHOW_STATUS
PS.START_DATE||'TO'||PS.END_DATE
PS.PERIOD_YEAR
PS.PERIOD_NUM
FROMGL_PERIOD_STATUSES_VPS,GL_SETS_OF_BOOKSSOB
WHEREPS.SET_OF_BOOKS_ID=SOB.SET_OF_BOOKS_ID
ANDAPPLICATION_ID=101
--ANDPERIOD_YEAR=2006
--ANDSUBSTR（SOB.SHORT_NAME,1,2）IN（'ES','LU','BE'）
ANDPS.SHOW_STATUSNOTIN（'NEVEROPENED'）
ORDERBY1,5,6DESC
SELECTSOB.SHORT_NAME
PS.PERIOD_NAME
PS.START_DATE
PS.END_DATE
PS.PERIOD_YEAR
PS.PERIOD_NUM
PS.SHOW_STATUS
FROMGL_PERIOD_STATUSES_VPS,GL_SETS_OF_BOOKSSOB
WHEREPS.SET_OF_BOOKS_ID=SOB.SET_OF_BOOKS_ID
ANDAPPLICATION_ID=101
ANDPERIOD_YEAR=2006
--ANDSUBSTR（SOB.SHORT_NAME,1,2）IN（'GB'）
ORDERBY1,5,6DESC
GLChartofAccountsStructure
/*CHARTOFACCOUNTSSTRUCTURE
WrittenbyDanielNorth,ORAFINAPPSLimited2007
Givesanoverviewofthechartofaccountsdefinitionsandalsostatus.
Thisisusedwhenimplementingmultiplechartsofaccountstoensureconsistentsetupacrosscountriesandbetweenenvironments.
Whereclausecanbeaddedorcommentedouttojustlookatspecificcountries.*/
SELECTFST.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"SEGNAME"
VS.FLEX_VALUE_SET_NAME"VALUESET"
FSEG.FLEX_VALUE_SET_ID"VAL_SET_ID"
FSEG.DEFAULT_TYPE"DEFTYPE"
FSEG.DEFAULT_VALUE"DEF.VALUE"
FSEG.ENABLED_FLAG"ENBLD"
FSEG.REQUIRED_FLAG"REQD"
FROMFND_ID_FLEX_STRUCTURES_VLFST,FND_ID_FLEX_SEGMENTSFSEG,FND_FLEX_VALUE_SETSVS
WHEREFST.ID_FLEX_NUM=FSEG.ID_FLEX_NUM
ANDFSEG.FLEX_VALUE_SET_ID=VS.FLEX_VALUE_SET_ID
--ANDSUBSTR（FST.ID_FLEX_STRUCTURE_CODE,1,2）IN（'BE','LU','ES','IT','HU','CZ','PL','RU'）
ANDFST.APPLICATION_ID=101
ANDFST.ID_FLEX_CODE='GL#'
ORDERBY1,FSEG.SEGMENT_NUM
GLChartofAccountsStructureOverview
/*CHARTOFACCOUNTSSTRUCTURE
WRITTENBYDANIELNORTH,ORAFINAPPSLIMITED2007
GIVESANOVERVIEWOFTHECHARTOFACCOUNTSDEFINITIONSANDALSOSTATUS.
THISISUSEDWHENIMPLEMENTINGMULTIPLECHARTSOFACCOUNTSTOENSURECONSISTENTSETUPACROSSCOUNTRIESANDBETWEENENVIRONMENTS.
WHERECLAUSECANBEADDEDORCOMMENTEDOUTTOJUSTLOOKATSPECIFICCOUNTRIES.*/
SELECTFST.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"SEGNAME"
VS.FLEX_VALUE_SET_NAME"VALUESET"
FSEG.FLEX_VALUE_SET_ID"VAL_SET_ID"
FSEG.DEFAULT_TYPE"DEFTYPE"
FSEG.DEFAULT_VALUE"DEF.VALUE"
FSEG.ENABLED_FLAG"ENBLD"
FSEG.REQUIRED_FLAG"REQD"
FROMFND_ID_FLEX_STRUCTURES_VLFST,FND_ID_FLEX_SEGMENTSFSEG,FND_FLEX_VALUE_SETSVS
WHEREFST.ID_FLEX_NUM=FSEG.ID_FLEX_NUM
ANDFSEG.FLEX_VALUE_SET_ID=VS.FLEX_VALUE_SET_ID
--ANDSUBSTR（FST.ID_FLEX_STRUCTURE_CODE,1,2）IN（'BE','LU','ES','IT','HU','CZ','PL','RU'）
ANDFST.APPLICATION_ID=101
ANDFST.ID_FLEX_CODE='GL#'
ORDERBY1,FSEG.SEGMENT_NUM
GLJournalHeaderSummary
/*
升级会员 