1. Uploading Records from Upload Table
  2. Upload Tables

8 Upload Tables

This topic provides an overview of the Upload Table.

Each data source in the function ID must be mapped to corresponding Upload Tables in Open Development Tool.

Data Source Column Mapping - Mapping of Upload Table Columns to Base Table Columns has to be done after proper analysis. Avoid including internal processing columns/invisible field columns etc to upload Table. This will reduce the complexity of the upload table.

Guidelines

Some guidelines for mapping upload table/columns with base table/columns are listed below:
  • The Master Data Source of the function ID should always be mapped to an Upload Table (except in the case of some call forms where it is not feasible). This upload table would be referred to as Master Upload Table.
  • Map the Upload Tables to only Normal Data Sources as per Open Development Tool configuration. For query, in only summary data sources, upload tables are not required.
  • More than one data source in the function ID can be mapped to a single upload table.

    Note:

    All the base tables should have one-to-one relationships with each other in this scenario.

    For example, both CSTB_CONTRACT and FXTB_CONTRACT_MASTER can be mapped to the same upload table, say, FXTB_UPLOAD_MASTER.

  • If the master data source is a common table used across many functions (For example, CSTB_CONTRACT), try grouping it with any of its child tables, so that the master upload table is unique for the Function ID.

    For example, both CSTB_CONTRACT and FXTB_CONTRACT_MASTER can be mapped to the same upload table, say, FXTB_UPLOAD_MASTER.

  • It is recommended to provide the same column names to both base table columns and upload table columns. This avoids complexity for both the developer and the user.
  • Apart from mapped columns from Base Table, Upload Table should have a standard set of columns as defined below:

    Table 8-1 Standard Column Names

    Column Name Remarks
    SOURCE_CODE This field specifies the external source.
    SOURCE_REF / MAINTENANCE_SEQ_NO This field specifies the external reference number. SOURCE_REF is used for Contract upload tables while MAINTENANCE_SEQ_NO is for maintenance upload tables.
    SOURCE_SEQ_NO This field specifies the source sequence number.
    BRANCH_CODE This field specifies the branch code.
    FUNCTION_ID This column is required only in the Upload Master table. This is mandatory if the same upload master tables are used for multiple function IDs. For example, Parent and Child Functions
    ACTION_CODE This is required only in Upload Master Table. If not present, then only NEW operation would be supported by the upload framework for the function ID.
    UPLOAD_ID This is required only in Upload Master Table. Different values can be inserted for this column in batches to process the upload routine in multiple threads. This is an optional column, mostly used in transaction screens.
    UPLOAD_STATUS Required only if Upload Table and Master Table are the same. For example, PC contract, This is used mostly in transaction screens. This field is optional.
    MODULE This field displays the module code of the function ID. Used mostly in transaction screens. This field is optional.
    SOURCE_OPERATION This field specifies the Source Operation Code. This needs to be present in only the master upload table if any. If not present, then the system would try to derive the default SOURCE_OPERATION for a particular action code. This field is optional.
    SOURCE_CODE, SOURCE_REF / MAINTENANCE_SEQ_NO, SOURCE_SEQ_NO, and BRANCH_CODE form the composite primary key for any master upload table. For detailed upload tables, the 4 columns mentioned above along with a unique identifier for the record, if any, form the primary key FUNCTION_ID, ACTION_CODE, UPLOAD_ID, UPLOAD_STATUS, MODULE and SOURCE_OPERATION are optional columns in Master Upload Table.
  • For Transaction screens, the EXTERNAL_REF_NO of the upload table has to be mandatorily mapped to a base table column. This is required to derive the reference number in case of any modified operation. Most often, this column can be found in CSTB_CONTRACT.
  • More than one data source can be mapped to the same upload table differentiated by the upload table where clause.

    For Instance, if two different legs of a transaction (buy and sell) of a deal are captured by two data sources in function ID (same table with different aliases), then one upload table can be used for both tables. Upload where clause for both these data sources should be such that the adapter picks proper data to base table data types. Note that in this scenario, both data sources should not be directly related to each other. A difference has to be noted between this scenario and the case where 2 data sources with a one-to-one relationship are mapped to the same upload table.

  • For call form function IDs, the master data source would often be a view for propagating the record key to the call form. In such instances, master data sources should not have any upload table mapped to them. For example, CSTBS_CONTRACT__ADV is the master data source for the Advice Call form but data is uploaded only in CSTB_CONTRACT_EVENT_ADVICE. Hence upload table should not be mapped to CSTBS_CONTRACT__ADV.
Refer to the following sample Master Upload Table definitions:
  • Mainteneace_Master_Upload_Table.sql
  • TXN_MASTER_UPLOAD_TABLE.sql
  • DETAIL_UPLOAD_TABLE.sql
  • Callform_Upload_table.sql