Transcription
Oracle Financial Services Analytical ApplicationsData Model UtilitiesUser GuideRelease 7.1 / 7.2Part No. E17560-01December 2011
Oracle Financial Services Analytical Applications Data Model Utilities User Guide, Release 7.1Part No. E17560-01Copyright 2011, Oracle and/or its affiliates. All rights reserved.Primary Author:Surag RamachandranContributing Author:Contributor:Jilna Surag, Anuradha MuralidharanAravind VenketaramanOracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron,the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced MicroDevices. UNIX is a registered trademark of The Open Group.This software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software,any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are"commercial computer software" pursuant to the applicable Federal Acquisition Regulation andagency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptationof the programs, including any operating system, integrated software, any programs installed on thehardware, and/or documentation, shall be subject to license terms and license restrictions applicable to theprograms. No other rights are granted to the U.S. Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.This software or hardware and documentation may provide access to or information on content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services. OracleCorporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to youraccess to or use of third-party content, products, or services.
ContentsSend Us Your CommentsPreface1IntroductionList of Acronyms used in the document. 1-12Object ManagementAdding Dimension Tables and Key Dimension (Leaf) Registration. 2-1Adding Custom Instrument Tables. 2-16Adding Custom Transaction Tables. 2-21Adding Custom Lookup Tables. 2-24Object Registration And Validation. 2-29Defining Alternate Rate Output Columns. 2-40User Defined Properties. 2-41Modifying the precision of Balance Columns In Ledger Stat. 2-453UtilitiesReverse Population. 3-1Product Instrument Mapping. 3-5Instrument Synchronization. 3-8Ledger Load Undo. 3-124Data LoadersDimension Loaders. 4-1Historical Rates Data Loader. 4-17iii
Forecast Rate Data Loader. 4-21Prepayment Rate Data Loader. 4-36Stage Instrument Table Loader. 4-40Transaction Summary Table Loader. 4-46Ledger Data Loader. 4-51Pricing Management Transfer Rate Population Procedure.4-64ALMBI Transformation. 4-66Hierarchy Transformation.4-675Mapping Export in Metadata BrowserProcedure. 5-1iv
Send Us Your CommentsOracle Financial Services Analytical Applications Data Model Utilities User Guide, Release 7.1Part No. E17560-01Oracle welcomes customers' comments and suggestions on the quality and usefulness of this document.Your feedback is important, and helps us to best meet your needs as a user of our products. For example: Are the implementation steps correct and complete?Did you understand the context of the procedures?Did you find any errors in the information?Does the structure of the information help you with your tasks?Do you need different information or graphics? If so, where, and in what format?Are the examples correct? Do you need more examples?If you find any errors or have any other suggestions for improvement, then please tell us your name, thename of the company who has licensed our products, the title and part number of the documentation andthe chapter, section, and page number (if available).Note: Before sending us your comments, you might like to check that you have the latest version of thedocument and if any concerns are already addressed. To do this, access the new Oracle E-Business SuiteRelease Online Documentation CD available on My Oracle Support and www.oracle.com. It contains themost current Documentation Library plus all documents revised or released recently.Send your comments to us using the electronic mail address: appsdoc us@oracle.comPlease give your name, address, electronic mail address, and telephone number (optional).If you need assistance with Oracle software, then please contact your support representative or OracleSupport Services.If you require training or instruction in using Oracle software, then please contact your Oracle local officeand inquire about our Oracle University offerings. A list of Oracle offices is available on our Web site atwww.oracle.com.v
PrefaceIntended AudienceWelcome to Release 7.1 of the Oracle Financial Services Analytical Applications Data ModelUtilities User Guide.See Related Information Sources on page viii for more Oracle E-Business Suite productinformation.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the OracleAccessibility Program website athttp://www.oracle.com/pls/topic/lookup?ctx acc&id docacc.Access to Oracle SupportOracle customers have access to electronic support through My Oracle Support. Forinformation, visit http://www.oracle.com/pls/topic/lookup?ctx acc&id info or visithttp://www.oracle.com/pls/topic/lookup?ctx acc&id trs if you are hearing impaired.Structure1 IntroductionThis document contains various chapters related to data model utilities and dataloaders available within Oracle Financial Services Analytical Applications (OFSAA).The four chapters present in this document are: Object Management, Utilities, DataLoaders, and Mapping Export in Metadata Browser.2 Object ManagementThis chapter details the steps involved in adding various client data objects into themodel.3 Utilitiesvii
This chapter details the steps involved in executing various data model utilities that areavailable within OFSAA.4 Data LoadersThis chapter details the steps involved in executing various data loaders that areavailable within OFSAA. Data loaders move data from staging layer to processinglayer.5 Mapping Export in Metadata BrowserRelated Information SourcesDo Not Use Database Tools to Modify Oracle E-Business Suite DataOracle STRONGLY RECOMMENDS that you never use SQL*Plus, Oracle DataBrowser, database triggers, or any other tool to modify Oracle E-Business Suite dataunless otherwise instructed.Oracle provides powerful tools you can use to create, store, change, retrieve, andmaintain information in an Oracle database. But if you use Oracle tools such asSQL*Plus to modify Oracle E-Business Suite data, you risk destroying the integrity ofyour data and you lose the ability to audit changes to your data.Because Oracle E-Business Suite tables are interrelated, any change you make using anOracle E-Business Suite form can update many tables at once. But when you modifyOracle E-Business Suite data using anything other than Oracle E-Business Suite, youmay change a row in one table without making corresponding changes in related tables.If your tables get out of synchronization with each other, you risk retrieving erroneousinformation and you risk unpredictable results throughout Oracle E-Business Suite.When you use Oracle E-Business Suite to modify your data, Oracle E-Business Suiteautomatically checks that your changes are valid. Oracle E-Business Suite also keepstrack of who changes information. If you enter information into database tables usingdatabase tools, you may store invalid information. You also lose the ability to track whohas changed your information because SQL*Plus and other database tools do not keep arecord of changes.viii
1IntroductionThis document contains various chapters related to data model utilities and dataloaders available within Oracle Financial Services Analytical Applications (OFSAA).The four chapters present in this document are: Object Management, Utilities, DataLoaders, and Mapping Export in Metadata Browser.This chapter covers the following topics: List of Acronyms used in the documentList of Acronyms used in the documentAcronymDescriptionAAIAnalytical Applications InfrastructureALMAsset Liability ManagementAMHMAttributes, Members and HierarchyManagementCOAChart Of AccountsF2TFile to TableFDMFinancial Data ManagerGLGeneral LedgerGTTGlobal Temporary TableIntroduction1-1
1-2AcronymDescriptionICCInformation Command CenterINFODOMInformation DomainIPInternet ProtocolOFSOracle Financial ServicesOFSAOracle Financial Services ApplicationsOFSAAOracle Financial Services AnalyticalApplicationsOFSAAIOracle Financial Services AnalyticalApplications InfrastructurePFTProfitabilityPL/SQLProcedural Language /Structured QueryLanguageT2TTable to TableTPTransfer PricingUDPUser-Defined PropertyUIUser InterfaceOracle Financial Services Analytical Applications Data Model Utilities User Guide
2Object ManagementThis chapter details the steps involved in adding various client data objects into themodel.This chapter covers the following topics: Adding Dimension Tables and Key Dimension (Leaf) Registration Adding Custom Instrument Tables Adding Custom Transaction Tables Adding Custom Lookup Tables Object Registration And Validation Defining Alternate Rate Output Columns User Defined Properties Modifying the precision of Balance Columns In Ledger StatAdding Dimension Tables and Key Dimension (Leaf) RegistrationThe following section details the process in which users can add custom keydimensions to the OFSAA application. Users can view the registered dimension withinthe AMHM screens. Also, users can add members and hierarchies for the dimensionthrough AMHM screens.Registering a new Key Dimension (called as Leaf in OFSA 4.5) requires the followingsteps: Add a set of dimension tables to store leaf values in ERwin model. Add the key dimension column to required Entities in ERwin model. Assign the Processing Key Column Property (Key Dimension Columns only). Upload the model.Object Management2-1
Register the Key Dimension. Modify Unique indexes (Key Leaf Dimension only). Validate tables.Each of these steps is discussed in detail in the following sections.Adding Dimension TablesEach key dimension contains a set of the following tables: DIM DIMENSION B - Stores leaf and node member codes within the dimension. DIM DIMENSION TL - Stores names of leaf and node and their translations. DIM DIMENSION ATTR - Stores attribute values for the attributes of thedimension. DIM DIMENSION HIER - Stores parent-child relationship of members andnodes that are part of hierarchies.Note: Replace DIMENSION with the keyword representing the keydimension.Seeded key dimension tables are present in 'Fusion – Dimensions' subject area withinthe ERwin model. The above tables need to be created for the new dimension. For moreinformation on creating dimension tables in ERwin, see leaflet(AddingAndCustomizingLeaf.pdf).Note: For ease of use, user can copy an existing set of dimension tableseg, for ORG UNIT dimension and rename the tables (in both physicaland logical view) to represent the new dimension.Table structure of one of the seeded key dimension is given below with remarks on howthis can be used as the basis for modeling new key dimensions.DIM ORG UNIT BStores the ID of the members (leaf and nodes) of the dimension.2-2Oracle Financial Services Analytical Applications Data Model Utilities User Guide
Column RemarksORG UNIT IDOrganization Unit IDNUMBER(14)NOTNULLLeaf columnwhich stores theid for theorganization unitdimensionColumn nameand descriptionshould reflectthe newdimension.Datatype andotherconstraintsshould beretained.ORG UNIT DISPLAY CODEOrganization UnitDisplayCodeNUMBER(14)NULLLeaf columnwhich stores thedisplay code forthe organizationunit dimensionColumn nameand descriptionshould reflectthe newdimension.Datatype andotherconstraintsshould beretained.ENABLED FLAGEnabledFlagVARCHAR2(1)NOTNULLStore if the item isenabled or notInternally usedand henceshould beretained in thesame formwithin the newdimension table.LEAF ONLY FLAGLeaf orNode FlagVARCHAR2(1)NOTNULLIndicates if themember is leafonly or notInternally usedand henceshould beretained in thesame formwithin the newdimension table.Object Management2-3
2-4Column RemarksDEFINITION ge that isused to defineInternally usedand henceshould beretained in thesame formwithin the newdimension table.CREATED BYCreatedByVARCHAR2(30)NOTNULLIndicates whocreated this itemInternally usedand henceshould beretained in thesame formwithin the newdimension table.CREATION DATECreationDateTIMESTAMPNOTNULLIndicates whenwas this itemcreatedInternally usedand henceshould beretained in thesame formwithin the newdimension table.LAST MODIFIED BYLastModifiedByVARCHAR2(30)NOTNULLIndicates whomodified this itemInternally usedand henceshould beretained in thesame formwithin the newdimension table.LAST MODIFIED DATELastModifiedDateTIMESTAMPNOTNULLIndicates whenwas this itemmodifiedInternally usedand henceshould beretained in thesame formwithin the newdimension table.Oracle Financial Services Analytical Applications Data Model Utilities User Guide
Column RemarksORG UNIT CODEORG UNIT CODEVARCHAR2(20)NULLThis column isused by stagingand contains thealpha-numericcodes for eachdimensionmember. Stagingdimension tablecontains uniquealpha-numericcodes and aunique numericidentifier isgenerated whileloading intoFusion dimensiontable.Column nameand descriptionshould reflectthe newdimension.Datatype andotherconstraintsshould beretained.DIM ORG UNIT TLStores the names and descriptions of the members (leaf and nodes) of the dimension invarious languages.Column nternally usedand henceshould beretained in thesame formwithin the newdimensiontable.Object Management2-5
2-6Column RemarksORG UNIT IDOrganization Unit IDNUMBER(14)NOTNULLLeaf columnwhich storesthe id for theorganizationunit dimensionColumn nameanddescriptionshould reflectthe newdimension.Datatype andotherconstraintsshould beretained.ORG UNIT NAMEOrganization UnitNameVARCHAR2(150)NOTNULLLeaf columnwhich storesthe name fortheorganizationunit dimensionColumn nameanddescriptionshould reflectthe newdimension.Datatype andotherconstraintsshould Description ofan ItemInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.CREATED BYCreated ByVARCHAR2(30)NOTNULLIndicates whocreated thisitemInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.Oracle Financial Services Analytical Applications Data Model Utilities User Guide
Column RemarksCREATION DATECreationDateTIMESTAMPNOTNULLIndicates whenwas this itemcreatedInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.LAST MODIFIED BYLastModifiedByVARCHAR2(30)NOTNULLIndicates whomodified thisitemInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.LAST MODIFIED DATELastModifiedDateTIMESTAMPNOTNULLIndicates whenwas this itemmodifiedInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.DIM ORG UNIT ATTRStores the values of the attributes of the members (leaf and nodes) of the dimension.Object Management2-7
2-8Column RemarksORG UNIT IDOrganization Unit IDNUMBER(14)NOTNULLLeafcolumnwhichstores the idfor theorganisation unitdimensionColumn nameanddescriptionshould reflectthe newdimension.Datatype andotherconstraintsshould beretained.ATTRIBUTE IDAttributeIDNUMBER(22)NOTNULLStoresattribute idnumber fora memberof adimensionInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.DIM ATTRIBUTE is fieldstores thenumbervalues fortheattribute ofa memberInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.DIM ATTRIBUTE This fieldstores thevarcharvalues fortheattribute ofa memberInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.Oracle Financial Services Analytical Applications Data Model Utilities User Guide
Column RemarksNUMBER ASSIGN VALUENumericValue Of AMemberNUMBER(22)NULLThis fieldstores thenumbervalues fortheattribute ofa memberInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.VARCHAR ASSIGN VALUEVarcharMemberValueVARCHAR2(1000)NULLThis fieldstores thevarcharvalues fortheattribute ofa memberInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.DATE ASSIGN VALUEDate ValueDATENULLDate valuethat isassignedInternally usedand henceshould beretained in thesame formwithin the newdimensiontable.DIM ORG UNIT HIERStores the parent-child relationship of various nodes and leaf within hierarchies of thedimension.Object Management2-9
2-10Column RemarksHIERARCHY IDHierarchy IDNUMBER(10)NOTNULLUnique Idthat isgenerated foreveryhierarchythat iscreatedInternally used andhence should be retainedin the same form withinthe new dimension table.PARENT IDParent IDNUMBER(14)NOTNULLColumn thatstore the idof the childmemberInternally used andhence should be retainedin the same form withinthe new dimension table.CHILD IDChildMemberIDNUMBER(14)NOTNULLStore child idnumber for adimensionInternally used andhence should be retainedin the same form withinthe new dimension table.PARENT DEPTH NUMParentDepthNumberNUMBER(14)NOTNULLStores parentdepthnumberInternally used andhence should be retainedin the same form withinthe new dimension table.CHILD DEPTHNUMChildDepthNumberNUMBER(14)NOTNULLStores childdepthnumberInternally used andhence should be retainedin the same form withinthe new dimension table.DISPLAY ORDER NUMDisplayOrderNumberNUMBER(14)NOTNULLStores thedisplay ordernumber forthe memberInternally used andhence should be retainedin the same form withinthe new dimension table.SINGLE DEPTH FLAGSingleDepthFlagVARCHAR2(1)NOTNULLIndicates ifthe hierarchyis of singledepth or notInternally used andhence should be retainedin the same form withinthe new dimension table.CREATED BYCreatedByVARCHAR2(30)NOTNULLIndicateswho createdthis itemInternally used andhence should be retainedin the same form withinthe new dimension table.Oracle Financial Services Analytical Applications Data Model Utilities User Guide
Column RemarksCREATION DATECreationDateTIMESTAMPNOTNULLIndicateswhen wasthis itemcreatedInternally used andhence should be retainedin the same form withinthe new dimension table.LAST MODIFIED ified thisitemInternally used andhence should be retainedin the same form withinthe new dimension table.LAST MODIFIED DATELastModifiedDateTIMESTAMPNOTNULLIndicateswhen wasthis itemmodifiedInternally used andhence should be retainedin the same form withinthe new dimension table.Adding Dimension Column To Required ObjectsDimension column can be added to the following set of Client Data Objects: Tables classified as 'Instruments' and 'Instrument Profitability' Tables classified as 'Transaction Profitability' Ledger Stat table.Dimension can be of the types – Ledger Only or Both. If the dimension is classified as'Ledger Only', the dimension column needs to be added only to Ledger Stat table. If thedimension is classified as 'Both', the dimension column needs to be added to Ledger Stattable and other tables classified as Instruments and Transactions.For adding key dimension column to tables that are classified as 'Instruments' and'Instrument Profitability', add the column to LEAF COLUMNS super-class table.For adding key dimension column to tables that are classified as 'TransactionProfitability', add the column to TRANS LEAF COLUMNS super-class table.For adding key dimension column to Ledger Stat table, add the column toLEDGER LEAF COLUMNS super-class table.Note: Columns of super-class tables that are linked to sub-class tableare rolled down to the sub-class table during 'Model Upload' operation.Object Management2-11
Assigning Processing Key Property'Processing Key' is a column level User Defined Property (UDP) in ERwin model. Thisproperty can have two values – Yes or No. Only those objects where the column wasadded to the unique index are affected.For tables classified as 'Transaction Profitability, this property needs to be set as 'Yes' forone or more of the key dimension columns.For Ledger Stat table, this property needs to be set as 'Yes' for all key dimensioncolumns.Uploading ERwin ModelERwin model with the above changes needs to be uploaded in OFSAAI environment.Uploading the model creates these additional tables and sets these properties within theatomic schema.After upload, user can verify the changes in the schema as well as query OFSAAImetadata tables like REV COLUMN PROPERTIES for viewing properties assigned toeach column.For more information on data model upload process, see OFSAAI User Guide.Leaf RegistrationOracle Financial Services Analytical Applications Infrastructure (OFSAAI) provides anLeaf Registration procedure to add the new Key Dimension Column to the Dimensionsmetadata registry (REV DIMENSIONS B, REV DIMENSIONS TL).Leaf Registration ProcedureThis procedure performs the following: Registers key dimension Invalidates all Client Data Objects.Executing Leaf Registration ProcedureYou can execute this procedure either from SQL*Plus or from within a PL/SQL block orfrom ICC Batch screen within OFSAAI framework.To run the procedure from SQL*Plus, login to SQL*Plus as the Schema Owner. Thefunction requires 19 parameters. The syntax for calling the procedure is:2-12Oracle Financial Services Analytical Applications Data Model Utilities User Guide
functionrev leaf registration(batch run id varchar2,mis date varchar2memDataType varchar2,dimName varchar2,description varchar2,memberBTableName varchar2,memberTLTableName varchar2,hierarchyTableName varchar2,attributeTableName varchar2,memberCol varchar2,memberDispCodeCol varchar2,memberNameCol varchar2,memberDescCol varchar2,dimTypeCode varchar2,simpleDimFlag varchar2,keyDimFlag char,writeFlag varchar2,catalogTableType char,flattenedTableName varchar2) batch run id : any string to identify the executed batch. mis date : in the format YYYYMMDD. memDataType : member data type of Dimension as inNUMBER,VARCHAR2,CHAR. dimName : name of the dimension to be added (less than 21 chars). description : description of the dimension (less than 255 chars). memberBTableName : Member Base Table Name input as either null or a valuewith suffix ' CD' or ' B'. memberTLTableName : Member TL Table Name input as either null or name of thetable. hierarchyTableName : Hierarchy Table Name input as either null or name of thetable. attributeTableName : Attribute Table Name input as either null or name of thetable. memberCol : Member Column Name input as either null or name of the column. memberDispCodeCol : Member Display Code Column Name input as either null orname of the column. memberNameCol : Member Name Column input as either null or name of thecolumn. memberDescCol : Member Description Column input as either null or name of theObject Management2-13
column. dimTypeCode : Code for the dimension Type as in 'PROD for product type', 'ORGNfor Organizational Unit', 'CCOA for Common Chart of Accounts', 'FINELE forFinancial Element', 'GL for General Ledger Account', 'OTHER for any other type'.All user defined dimensions will have DIMENSION TYPE CODE as 'OTHER'.User defined dimensions which are product related will haveDIMENSION TYPE CODE as 'PROD'. simpleDimFlag : 'Y' or 'N' to determine Simple Dimension.Simple dimensions are created to store CODE and Descriptions. These tables areused by the User Interfaces to list values in drop downs / radio buttons, and so on.Simple dimensions are not reverse populated.ExampleCountry, Currencies, Customer Type. keyDimFlag : 'Y' or 'N' to determine Key Dimension.Key dimensions are dimensions which get reverse populated to the legacy tables.ExampleProduct, Org Unit, General Ledger. writeFlag : 'Y' or 'N' to determine whether Dimension should appear in drop downlist in Dimension Management Members. catalogTableType : 'L' or 'B' to determine table type for key dimensions. flattenedTableName : Flattened Table Name input as either null or name of thetable.For Example2-14Oracle Financial Services Analytical Applications Data Model Utilities User Guide
Declarenum number;Beginnum : rev leaf registration('BATCH NO 01','20101216','NUMBER','PRODUCT 1','Cost Transfer Product Type ID','DIM PRODUCT 1 B','DIM PRODUCT 1 TL','DIM PRODUCT 1 HIER','DIM PRODUCT 1 ATTR','PRODUCT 1 ID','PRODUCT 1 DISPLAY CODE','PRODUCT 1 N PROD TABLE');End;To execute the procedure from OFSAAI ICC framework, create a new Batch with theTask as TRANSFORM DATA and specify the following parameters for the task: Datastore Type:- Select appropriate datastore from list Datastore Name:- Select appropriate name from the list IP address:- Select the IP address from the list Rule Name:- batch leaf registration Parameter List:- Member Data type , Dimension Name, Dimension Description,Member Base Table Name, Member Translation Table Name, Hierarchy TableName, Attribute Table Name, Member Column , Member Display Code Column,Member Name Column, Member Description Column , Dimension Type Code ,Simple Dimension Flag , Key Dimension Flag , writeFlag, Catalog Table Type ,Flatten Table NameModify Unique Ind
This document contains various chapters related to data model utilities and data loaders available within Oracle Financial Services Analytical Applications (OFSAA). The four chapters present in this document are: Object Management, Utilities, Data Loaders, and Mapping Export in Metadata Browser. This chapter covers the following topics:
