1. Uploading Records from Upload Table
  2. Upload Framework

7 Upload Framework

This topic describes a standard framework for uploading records.

Upload Framework supports the upload of both maintenance and transaction screens. Different steps involved in Bulk Upload are listed below:
  1. Data is populated in the upload tables through Macro Excel Upload or any other utility.
  2. Trigger on Master Upload Table would insert entries into a process table with Upload Status as U (Unprocessed). One entry would be inserted into the process table for each record. Function ID would also be updated in the process table along with other information.
  3. Upload routine is invoked for a particular function ID by the user from CVDUPLOD screen/stub.
  4. On processing the routine, the system would process all the unprocessed records from the process table for the particular function ID. This would be done using a cursor on the process table.
  5. An adapter package converts the upload table types to base table type data. Then it invokes the main package of the function ID.
  6. After processing of each record, process table columns for uploaded status, error code, etc would be updated by the system.
From the above steps, we can derive the components required for a particular function ID to be brought under this framework.
  1. Process Table
  2. Upload Tables
  3. Trigger on Master Upload Table
  4. Adapter package for Upload Routine
  5. Wrapper code in CVDUPLOD screen processing logic to call the adapter package based on the function ID

Naming Convention

The framework does not enforce a standard naming convention for upload tables. Existing upload tables can be re-used in this framework. If any new upload table is introduced, it is recommended to follow the naming convention as mentioned below.

Note:

In the naming convention, the fourth letter of the base table is to be replaced with U.
For example,
  • Base Table Name - STTM_CUSTOMER
  • Upload Table Name - STTU_CUSTOMER
Recommended to follow the naming convention for consulting/client-developed Upload Table Name as Table name _U_EXTGBL.

Process Table

For uploading, each record is processed from a cursor on process tables. This is common across all function IDs. There are 2 process tables:
  • CSTB_EXT_CONTRACT_STAT
  • STTB_UPLOAD_MASTER

Table 7-1 CSTB_EXT_CONTRACT_STAT

Name Type Characters Nullable
BRANCH_CODE VARCHAR2 3 CHAR N
SOURCE VARCHAR2 20 CHAR N
PRODUCT_CODE VARCHAR2 4 CHAR Y
COUNTERPARTY VARCHAR2 35 CHAR Y
EXTERNAL_INIT_DATE DATE NA Y
MODULE VARCHAR2 2 CHAR Y
EXTERNAL_REF_NO VARCHAR2 20 CHAR N
IMPORT_STATUS VARCHAR2 1 CHAR Y
CITICUBE_REF_NO VARCHAR2 16 CHAR Y
POST_IMPORT_STATUS CHAR 1 CHAR Y
EXPORT_STATUS CHAR 1 CHAR Y
USER_ID VARCHAR2 12 CHAR Y
JOBNO NUMBER 2 Y
CONTRACT_REF_NO VARCHAR2 16 CHAR Y
ERR_CODE VARCHAR2 11 CHAR Y
ERR_MESSAGE VARCHAR2 255 CHAR Y
ACTION_CODE VARCHAR2 10 CHAR Y
FUNCTION_ID VARCHAR2 8 CHAR Y
EXTERNAL_SEQ_NO NUMBER 22 N
UPLOAD_ID VARCHAR2 16 CHAR Y
Here a particular record from upload tables would be picked by a combination of EXTERNAL_REF_NO, EXTERNAL_SEQ_NO, BRANCH_CODE, and SOURCE. Columns like EXPORT_STATUS, CONTRACT_REF_NO, ERR_CODE, and ERR_MESSAGE would be updated by the system after processing. UPLOAD_ID signifies the thread of execution. The upload routine can be invoked in multiple threads if multiple upload IDs are present.

Table 7-2 STTB_UPLOAD_MASTER

Name Type Characters Nullable
MAINTENANCE_SEQ_NO VARCHAR2 16 CHAR N
BRANCH_CODE VARCHAR2 3 CHAR N
SOURCE_CODE VARCHAR2 15 CHAR N
MAINTENANCE_TYPE VARCHAR2 15 CHAR Y
UPLOAD_STATUS CHAR 1 CHAR Y
UPLOAD_INITIATION_DATE DATE NA Y
USER_ID VARCHAR2 12 CHAR Y
ACTION_CODE VARCHAR2 15 CHAR Y
SOURCE_SEQ_NO NUMBER NA N
UPLOAD_ID VARCHAR2 16 CHAR Y
Here a particular record from upload tables would be picked by a combination of MAINTENANCE_SEQ_NO, SOURCE_SEQ_NO, BRANCH_CODE, and SOURCE_CODE. UPLOAD_STATUS would be updated by the system after processing a record. UPLOAD_ID signifies the thread of execution. The upload routine can be invoked in multiple threads if multiple upload ids are present.

Upload Tables

Refer to the topic unresolvable-reference.html#GUID-F1BA2FC3-7571-4B69-901D-E6E246E8155B for detailed information.

Trigger on Upload Table

Refer to the topic unresolvable-reference.html#GUID-0F8336E5-5EA4-497D-BAFB-B3D72B3FEF2D for detailed information.

Upload Adapter Package

Upload Packages would handle type conversions and processing records after conversion.
  • Naming Convention - Modulepks_FunctionID_Ext_Upload

    For example, fxpks_fxfdtronl_ext_upload

Based on structure, upload packages can be broadly classified as follows:

Table 7-3 Upload Packages

Upload Packages Description
Transaction Upload Adapter Records will be processed based on the cursor on CSTB_EXT_CONTRACT_STAT. Code to handle SUBSYSTAT will be present.
Maintenance Upload Adapter Records will be processed based on the cursor on STTB_UPLOAD_MASTER
Transaction Call forms Upload Adapter It will be called from the Transaction Upload Package. Code to update SUBSYSSTAT will be present
Maintenance Call Forms Upload Adapter It will be called from Maintenance Upload Packages