Flex Cube

FLEXCUBE

http://dc143.4shared.com/doc/zzOUkBmX/preview.html

I-flex Solutions Ltd

FPTBANK DATA DICTIONARY OF FREQUENTLY QUERIED TABLES

March 2008

FLEXCUBE


FLEXCUBE


© 2008 i-flex solutions limited

All rights reserved. No part of this work may be reproduced, stored in a retrieval system, adopted or transmitted in any form or by any means electronic, mechanical, photographic, graphic, optic recording or otherwise, translated in any language or computer language, without the prior written permission of i-flex solutions limited.

Due care has been taken to make this document and accompanying software package (if any) as accurate as possible. However, i-flex solutions limited makes no representation or warranties with respect to the contents hereof and shall not be responsible for any loss or damage caused to the user by the direct or indirect use of this document and the accompanying software package. Furthermore, i-flex solutions limited reserves the right to alter, modify or otherwise change in any manner the content hereof, without obligation of i-flex solutions limited to notify any person of such revision or changes.

All company and product names are trademarks of the respective companies with which they are associated.

Document Control

FLEXCUBE


File Name: FPTB-DATA-DICTIONARY.DOC

Creation Date: March 20, 2008

Author : Pathanjali Durbhaka

Last Saved By: Pathanjali Durbhaka

Group: I-flex solutions limited

Last Saved On: March 20,2008

Revision No: 1.0

Status: Final

FLEXCUBE


TABLE OF CONTENTS TABLE OF CONTENTS 3 INTRODUCTION AND SCOPE 4 TABLE NAMING CONVENTION 4 LIST OF INTERNAL MODULES 6 DATA DICTIONARY OF FREQUENTLY QUERIED TABLES 7

CORE ENTITIES TABLES 7 ACCOUNTING ENTRIES AND BALANCES TABLES 10 PRODUCT AND CONTRACT TABLES 11 15 END OF DAY AND OTHERS 15 15

FLEXCUBE


INTRODUCTION AND SCOPE This is not a standard release document and therefore contents might differ from the actual table structures in the database at any given time. The purpose and intent of this document is to provide a degree of familiarity to the user with the objects in the Flexcube database, in turn enabling them to understand and create reports and queries using Business Objects reporting Tool.

The table structures and the interpretation of the fields are subject to change with each release The naming convention might not be followed in all cases. New modules can be added and in some cases redundant modules revoked as part of the release process.

Note: It should be ensured that the information shared in this document should not be used for any kinds of data manipulation of the underlying tables as this would jeopardize the Support/Control procedures and would make it difficult to identify the cause of reported error (.i.e. if any).

TABLE NAMING CONVENTION

The tables, views and synonyms in most cases follow a standard naming convention.

The following the naming convention followed for Tables

First 2 characters should be the module name e.g. IF – For Interface module. Some of the modules are internal modules and might not be visible as distinct modules to the end users. Third character should be ‘T’ for table. Fourth character should be either ‘M’ for maintenance/master table and ‘B’ for Block / Detail table. An underscore is normally used as separator between the ‘segments’ of the name. Subsequent characters (after first 4) represent the description of the table further broken up into segments separated by underscore sign. Example FATM_PRODUCT_MASTER

FLEXCUBE


Views are named as follows

First 2 characters are module name Second 2 characters are ‘VW’ Subsequent characters represent the description of the table. Example ACVW_ALL_AC_ENTRIES

Synonyms

Have an ‘S’ added to the name typically as the fifth character leaving the rest of the names as is. E.g. ACVWS_ALL_AC_ENTRIES, STTMS_CUSTOMER

FLEXCUBE


LIST OF INTERNAL MODULES

The following are the list of modules which are present in the system. Not all modules are distinct for the user MODULE ID

MODULE DESCRIPTION

IC

Interest And Charges

SM

Security Management System

ST

Static Maintenance

DE

Data Entry

FT

Funds Transfer

CF

The ICCF

CY

The ccy

TA

The Tax

LM

Limits

GL

Gen Led

MS

Messaging

IS

Settlement Instructions

CA

CASA

AC

Accounting

RP

Reporting System

AE

Auto End of Day

LD

Loans and Deposits

MI

Mis

EI

End of Cycle

CO

Core Services

SI

Standing Instruction

RE

Reconciliation System

CS

Core

IF

Interfaces

CG

CLEARING

CM

Cash Management

FA

Fixed Assets

Please refer to Appendix 1 for details on the various table categorization based on the modules.

FLEXCUBE


DATA DICTIONARY OF FREQUENTLY QUERIED TABLES

CORE ENTITIES TABLES No. File

Table Name

Details

A1

STTM_BANK

Stores the Bank Details maintained through the Bank Maintenance screen under the Core Parameters Menu.

Unique Identification Fields : Bank_Code

A2

STTM_BRANCH

Stores the Branch Details maintained through the Branch Maintenance screen under the Core Parameters Menu.

Unique Identification Fields : Branch_Code

A3

STTM_DATES

Stores the Branch Date. Previous working Day, Current Date and the next working day are stored in this table.

Unique Identification Fields : Branch_Code

A4

SMTB_USER

Stores the User Details.

Unique Identification Fields : User_ID

A5

SMTB_CURRENT_USERS

Stores details of users who are currently logged into FlexCUBE.

Unique Identification Fields : User ID, Terminal

A6

STTM_CUSTOMER

Stores the customer details maintained through the CIF Maintenance screen.

Unique Identification Fields : Customer_No

FLEXCUBE


A7

STTM_CUST_ACCOUNT

Stores details of Customer Accounts and the account balances.

Unique Identification Fields : Cust_Ac_No, Cust_No

A8

STTB_ACCOUNT

Stores Both GLs and Customer Accounts. This would contain all customer accounts. It would only contains GLs which have been defined as “Leaf” and where the parameter “Posting Restrictions” is given as “Direct”.

Unique Identification Fields : Ac_Gl_No, AC_Or_Gl

A9

GLTM_GLMASTER

Master Table for the General Ledger that is maintained in the system.

Unique Identification Fields : Gl_Code

A10

STTM_LCL_HOL_MASTER

Stores the Holiday Calendar details for the Financial Year. This is the master table

Unique Identification Fields : Year, Month

A11

STTM_LCL_HOLIDAY

Stores the Holiday Calendar details for the Financial Year. This is the Detail table. “W” in the Holiday List column signifies a Working Day and “H” a holiday.

Unique Identification Fields : Year, Month

A12

CSTM_PRODUCT_UDF_FIELDS_MAP

Stores the Product Code and User Defined Fields (UDF) Field mapping. The order in which the UDFs are displayed in a contract is Cased on the field FIELD_NUM. A

FLEXCUBE


maximum of 50 UDFs can be maintained for a product.

Unique Identification Fields :Product_Code, Field_Name

A13

CSTM_CONTRACT_UDF_FIELDS

Stores the UDF Values specified for the UDFs at the Contract Level. Columns FIELD_VAL_1 would store the value of UDF FIELD_NUM 1, FIELD_VAL_2 would store the value of UDF FIELD_NUM 2 and so on.

Unique Identification Fields : Contract_Ref_No, Product_Code

A14

STTM_TRN_CODE

Stores the Transaction Code details including the description of the code.

Unique Identification Fields : Trn_Code

A15

UDTM_FIELDS

Stores the User Defined Field definition. This is a common table where all the UDF’s created are stored. The linkage to the respective function us controlled by another table (CSTMS_FUNCTION_UDF_FIELDS_MAP)

Unique Identification fields :FIELD_NAME A16

UDTM_LOV

Stores the List of Values (LOV) items for the UDF of LOV type in UDTM_FIELDS table

Unique Identification fields :FIELD_NAME;LOV

A17

CSTMS_FUNCTION_UDF_FIELDS_MAP Stores the linkage and the position of the various UDF linked to each Function ID

Unique Identification fields :

FLEXCUBE


FUNCTION_ID; FIELD_NAME; FIELD_NUM A18

CSTM_FUNCTION_USERDEF_FIELDS

Stores the values of the UDF fields for each record and all the function Ids under this common table.

There are 50 hardcoded fields FIELD_VAL_1 to FIELD_VAL_50 which store the UDF under the corresponding serial number as defined in the CSTMS_FUNCTION_UDF_FIELDS_MAP table. E.g. for function ID SIDDIARY the field FRAUD_BKNM and FRAUD_WARN are Field Num 1 and 2 respectively. The corresponding values for this function ID would be found under FIELD_VAL_1 and FIELD_VAL_2 columns where function_id=’SIDDIARY’ in this table Unique Identification fields: FUNCTION_ID,REC_KEY

A19

GLTM_MIS_CLASS

Stores the definition of the MIS classes of all types. Each MIS class under a type is assigned a serial number by the system.

Unique Identification fields: MIS_TYPE; MIS_CLASS A20

GLTM_MIS_CODE

Stores the Codes which are created for each MIS class.

Unique Identification Fields: MIS_CLASS; MIS_CODE A21

MITB_CLASS_MAPPING

Stores the MIS values linked to the various entities in the system. There are 10 Fields with names TXN_MIS_1 to TXN_MIS_10. The MIS class with CLASS_NUM= 1 has values stored under TXN_MIS_1 and so on.

Unique Identification Fields: BRANCH_CODE; UNIT_REFERENCE; UNIT_TYPE

FLEXCUBE


ACCOUNTING ENTRIES AND BALANCES TABLES B1

ACTB_DAILY_LOG

Accounting Entries passed by all the modules during the course of the day are stored in this table. Entries of the transactions that have been deleted during the day can be identified with the use of the column DELETE_STAT whose value would be D.

Unique Identification Fields : Ac_Entry_Sr_No

B2

ACTB_HISTORY

Accounting entries where the DELETE_STAT <> ‘D’ from the daily upload table (ACTB_DAILY_LOG) are moved to this table during the End of the Day process. The Transactions in ACTB_DAILY_LOG whose DELETE_STAT = ‘D’ are purged from the system during the End of Day process.


B3

ACVW_ALL_AC_ENTRIES This is a Join between the Tables ACTB_DAILY_LOG and ACTB_HISTORY. This gives details of all Transactions Old and today’s. However this excludes the transactions from ACTB_DAILY_LOG where the DELETE_STAT = ‘D’


B4

GLTB_GL_BAL

Stores the Balances of the GL. The Balances are stored Period-wise and Financial Cycle-wise. This would contain GL balances for past and current periods. Individual Entries for each GL can be retrieved from ACVW_ALL_AC_ENTRIES

Unique Identification Fields : Gl_Code, Fin_Year, Period_Code

B4

ACTB_VD_BAL

Stores Value Dated Balances of Customer Accounts and Internal GL Accounts . The balances are stored for every account for all value date on which there were entries.

FLEXCUBE


Unique Identification Fields : BRN, ACC, VAL_DT

B5

ACTB_ACCBAL_HISTORY Stores Book Dated Balances of Customer Accounts and Internal GL Accounts . The balances are stored for every account for all value date on which there were entries.

Unique Identification Fields : BRN, ACC, VAL_DT

B6

CUST_GLBAL_AC

Stores the Account number, current balance and the Customer (Roll up) GL.

Unique identification field:AC_NO

PRODUCT AND CONTRACT TABLES C1

CSTM_PRODUCT

Master table for Product Maintenance for all modules.

Unique Identification Fields : Product_Code

C2

CSTM_PRODUCT_ACCROLE

Stores the Accounting Roles defined in each of the products.


C3

CSTM_PRODUCT_EVENT

Stores the Events defined in each of the products.


C4

CSTM_PRODUCT_EVENT_ACCT_ENTRY Stores the Accounting Entries defined for each product.

FLEXCUBE


Unique Identification Fields : Product_Code, Event_Code

C5

CSTB_CONTRACT_EVENT_LOG

Stores details of all events that have taken place for all contract. Stores details like event code, value date of the event, maker and checker ID for the event.

Unique Identification Fields : Contract_Ref_No, Module

C6

CSTB_AMOUNT_DUE

Stores the Amount due, Amount Settled for each component (Principal and Interest) for all contracts.

Unique Identification Fields : Contract_Ref_No, Component

C7

CFTM_PRODUCT_ICCF

Stores the ICCF definition for all Products

Unique Identification Fields : Product, Component

C8

CFTM_RULE

Stores Details of All ICCF Rules.

Unique Identification Fields : Rule,

C9

CSTB_EVENT

Stores the Event Codes and description for All Modules

Unique Identification Fields : Event_Code, Module

FLEXCUBE


C10

CFTB_CONTRACT_INTEREST

Stores Interest Details such as Component, Rate, waiver etc for all contracts

Unique Identification Fields : Contract_Ref_No, Component Event_Seq_No

C11

CSTB_CONTRACT

Stores Basic details of Contracts for all modules. This would contain one record for each contract. This stores the latest Event and Version number of the contract.

Unique Identification Fields : Contract_Ref_no, Module_Code C12

LDTB_CONTRACT_MASTER

Stores Basic details of MM Contracts. The Basic details would be those entered / displayed in the Main Screen of the Contract Input screen. This stores records for every event and every version of the contract. Unique Identification Fields : Contract_Ref_No, Version_No, Event_Seq_No,

C13

LDTB_CONTRACT_PREFERENCE

Stores details with regard to Preferences set in the MM Contract. These preferences are set in the Main Screen of the Contract Input screen.

Unique Identification Fields : Contract_Ref_No, Version_No, Event_Seq_No,

FLEXCUBE


C14

LDTB_CONTRACT_SCHEDULES

Stores the Schedule definition for MM Contracts.

Unique Identification Fields : Contract_Ref_No, Component,

C15

LDTB_CONTRACT_CALANCE

Stores the Current Out-standing Principal for MM Contracts

Unique Identification Fields : Contract_Ref_No C16

LDTB_CONTRACT_ICCF_CALC

Stores the details of ICCF Calculations for each of the components for CD contracts. It stores the total amount, Rate, numCer of days and daily average amount for each component.

Unique Identification Fields : Contract_Ref_No, Component, C17

LDTB_CONTRACT_ICCF_DETAILS

Stores the accrual details for each of the components for MM contracts. Stores the Accrual till date, Amount Liquidated and current net accrual.

Unique Identification Fields : Contract_Ref_No, Component,

C18

LDTB_CONTRACT_LIQ

Stores details regarding Payments or fore closure like Amount due, amount paid, overdue days incase of penalty component. Unique Identification Fields : Contract_Ref_No, Component, Event_Seq_No

FLEXCUBE


C19

LDTB_CONTRACT_LIQ_SUMMARY

Stores additional details regarding payments or fore closure.

Unique Identification Fields : Contract_Ref_No, Event_Seq_No

C20

LDTB_CONTRACT_AMEND_DUE

Stores details regarding future value dated amendments for MM Contracts. These are amendments which are applicaCle from a future date.

Unique Identification Fields : Contract_Ref_No, Event_Seq_No, Value_Date

C21

LDTM_PRODUCT_MASTER

Stores Product Preferences of MM Products.

Unique Identification Fields : Product

C22

LDTM_PRODUCT_DFLT_SCHEDULES

Stores the Default Schedules that are defined at the product level for MM Products

Unique Identification Fields : Product

C23

DETB_TELLER_MASTER

Stores the Details of the transactions posted by the Teller products.

Unique Identification Fields: REFERENCE_NO; EVENT_SEQ_NO C24

DETB_BATCH_MASTER

Stores the Details of the Batches opened for the Day. The table gets cleared at end of day.

Unique Identification Fields: BRANCH_CODE; BATCH_NUMBER

FLEXCUBE


C25

FTTB_CONTRACT_MASTER

Stores the Details of the contracts under the FT module.

Unique Identification Fields: CONTRACT_REF_NO; EVENT_SEQ_NO

FLEXCUBE


END OF DAY AND OTHERS F1

EITB_PENDING_PROGRAMS Stores the list of End of Day programs to be executed at each stage of the End of Day process.

Unique Identification Fields :

F2

EIVW_PENDING_ITEMS

Stores the list of items which are pending authorization. This is also displayed in the “Unauth Transactions” Screen that can be found under the menu “End of Cycle  Pre-EOD Checks”.

Unique Identification Fields : BR, RN

F3

STTB_VALUE_DESC

Stores the translation for Field Values. This table can be used to get the value description of the values found in other tables such as record stat, contract status and so on.

Unique Identification Fields : Function_ID, Item Name (as in the table), Item Value, Item_Val_Desc

F4

AETB_PROCESS_PROGRESS Stores the status of the programs that are run automatically by the system. Unique Identification Fields : Function_ID, Branch code

Flex Cube

Recommend Documents