General Ledger Useful SQL ScriptsOracle Applications 11i.docx
- 文档编号:10551862
- 上传时间:2023-02-21
- 格式:DOCX
- 页数:55
- 大小:29.14KB
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
/*
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- General Ledger Useful SQL Scripts Oracle Applications 11i 11
链接地址:https://www.bdocx.com/doc/10551862.html