Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Document Logs
Change log:

Date:

Author:

Doc Version:

Changes:

Ext.

Int.

Is in Core

26 May 2011

DWN

0.1

Doc. created

 

 

 

15 March 2012

CM

0.2

New parameter Tree

 

 

 

28 August 2012

CM

0.3

Added new parameter values

 

 

 

01 March 2013

JSK

0.4

Added extra column descriptions for CDR_FILE_DEF and CDR_RECORD_DEF.

 

 

 

07 October 2013

JSK

1.12

Added section 3.1.11 concerning Hookpoint Invokation in the Pre or Post process of file processing.

 

 

 

14 May 2014

KTH

1.12.1

Added new parameter: DB_SYS_PACKAGE_NAME

 

 

 

22 May 2014KTH1.12.2Added new Parameter: CHARACTER_ENCODING   
30 October 2015KTH1.13

Added new column SEQUENCE columns to CDR_RECORDFILE_DEF table

(Available for FileProcessor Integration project version 2.2)

   
26 NovemberKaruna Thulluri1.14

JSON File export feature added

Added new column XML_FILE_NAME_COLUMN columns to CDR_FILE_DEF table

(Available for FileProcessor Integration project version 2.23-SNAPSHOT. Yet to be released)

   

...

1 - Purpose of Document

...

Figure 2.1 (below) represents the above tables and their respective classes:

Drawio
diagramNameCdrFileDataModelFinalNew.drawio
revision1

Figure 2.1 Data Model for CDR file

...

The above design is shown in figure 2.3.1 below:

Image RemovedImage Added
Figure 2.3.1 File Processor UML.

...

COLUMN NAME

DESCRIPTION

NAME

Name of the Cdr File Definition

DESCRIPTION

Description of Cdr File Definition

KEY

Unique key of a file defintion

CODE

Code, in-case sub classing is needed in the future

MAX_RECORDS

Maximum number of records in a file (Default value -1 (no maximum number of records))

CDR_PARSER_ID

Parser used for parsing a file into records (CDR_PARSER_ID = CDR_PARSER.ID)

BILLING_SOURCE_TABLE_NAME

Table name for storing file information source

RECORD_SEPARATOR_ID

Type of separator used for separating one record from another (RECORD_SEPARATOR_ID = ITEM_SEPARATOR.ID). The default is a carriage return line feed

FILENAME_FORMAT

Format of filename to be loaded (Perl regular expression format). If set only filenames that match the format will be processed otherwise all files are processed

EXPORT_TYPE

The type of file records to be exported. Used when generating export files. The source table (for export) must have a column with name 'EXPORT_TYPE' whose value must match this value

PRE_EXPORT_PROCEDURE

Used when exporting files. Called before any final generation processing commences. Used to add extra export values that can only be added at time of export

XML_DOCUMENT_DEF_ID

Used when exporting XML files. (XML_DOCUMENT_DEF_ID = XML_DOCUMENT_DEF.ID)

BILLING_SOURCE_CLASS

Class used to model the table defined in the column 'BILLING_SOURCE_TABLE_NAME'

FILE_NAME_SUFFIX

Suffix (extension) to be added to the export filename

FILE_NAME_GENERATOR_METHOD

Method on the BILLING_SOURCE_CLASS used to generate a filename during export. This method must take the ID of the record in the 'BILLING_SOURCE_TABLE_NAME' table, and the CDR_FILE_DEF ID as parameters. For example for exporting a CDR file, the ID will be the ID of the appropriate SP_FILE_HEADER record linked to the rated BILLING_RECORDs (BILLING_RECORD.SP_FILE_ID = SP_FILE_HEADER.ID) being exported.

PRE_PROCESSOR_METHOD

Method in the BILLING_SOURCE_CLASS called before processing a file. The method must take CdrFileDef object as parameter. Could be used to validate if a file has already been loaded or to delete all records in a table before loading.

POST_PROCESSOR_METHOD

Method in the BILLING_SOURCE_CLASS called after processing a file. The method must take CdrFileDef object as parameter. Could be used for validating contents of the detail against information on the trailer.

RECORD_FOR_HEADER

Possible values are Y and N. This column is solely taken into an account, if the PERSIST column has the value Y. In case the first line in the loaded file must be stored in the BATCH_FILE_LINE table, the RECORD_FOR_HEADER column must be set to Y.

STATUS_COLUMN_NAME

String value. Defines the column name for status of billing source table. If left empty a default name of STATUS is used.

NUMBER_OF_RECS_COLUMN_NAME

String with default value 'NUMBER_OF_RECS'. Defines the column name, where the number of the content lines in a loaded file must be stored.

DATE_LOADED_COLUMN_NAME

String with default value 'DATE_LOADED'. Defines the column name, where the time of the loading must be stored. The column is mandatory in the billing source table.

SAVE_RECORD_ORDER

Char with possible values 'Y'/'N' and default value 'N'. Defines whether line number of a loaded record must be stored in the db.

RECORD_ORDER_COLUMN_NAME

String with default value 'LINE_IN_FILE'. Defines the column name, where the line number of the loaded record must be stored.

STATUS_VALUE

String with default value '0'. Defines the status of the loaded file.

COMMENT_COLUMN_NAME

Defines the column name in the billing source table (defined in the column billing_source_table_name), where error messages are posted, if a file generation fails. Not mandatory in the billing source table. If left out, error messages are simply not posted in the billing source table.

PRE_PROCESSOR_HOOKPOINT_KEY

Sets the Hookpoint key to be called in the pre-process. Is invoked with the prefix FILE_PROCESSOR_.

POST_PROCESSOR_HOOKPOINT_KEY

Sets the Hookpoint key to be called in the post-process. Is invoked with the prefix FILE_PROCESSOR_.

COLUMN NAME

DESCRIPTION

ID

Unique table ID

NAME

Name of record definition

CODE

Code for sub-classing should one of the following DETAIL, HEADER, TRAILER

DESCRIPTION

Record definition description

XML_FILE_NAME_COLUMN_NAME

The value of FILE_NAME_COLUMN

This column can be used to generate both a flat file and also an Xml file for the same source record. The value of XML_FILE_NAME_COLUMN must be should be the name of the column from the Source table where the generated XML filename will be saved to. The FILENAME column of Source table will be used to save the filename, if this If CDR_FILE_DEF.XML_FILE_NAME_COLUMN_NAMEis empty, the FILENAME column of Source table will be used to save the filename.

If both a flat file and , an XML file and a JSON file should be generated on the same source record:

  • FILENAME column of Source table will be used to save the filename of the flat file
  • The source table column name configured in XML_

    Then define 3 different CDR_FILE_DEF entries, each with proper column name set in the FILE_NAME_COLUMN

    of

    _NAME. For example:

    • First CDR_FILE_DEF record will be used to save the XML file name.

    3.1.2 CDR Record Definition (CDR_RECORD_DEF)

    A CDR Record Definition (CDR_RECORD_DEF) defines a record in a CDR file and has the following columns in the CDR_RECORD_DEF table.

    _ID
    • entry for flat file generation: CDR_FILE_DEF.FILE_NAME_COLUMN_NAMEis empty. So the generated flat file name will be saved to FILENAME column of Source table
    • Second CDR_FILE_DEF entry for XML file generation: CDR_FILE_DEF

    ID of CDR_FILE_DEF to which this record definition record belongs

    FIELD_SEPARATOR_ID

    Defines what separates one record field from another. Points to a record in the ITEM_SEPARATOR table (CDR_RECORD_DEF.FIELD_SEPARATOR_ID = ITEM_SEPRARATOR.ID)

    MAX_LENGTH

    Defines the length of the record; the default value being -1 meaning record is of variable length

    FULL_LENGTH

    If the MAX_LENGTH attribute is not -1, and the FULL_LENGTH attribute is set to ‘Y’, then the record must be of length MAX_LENGTH. Default value is N

    CDR_PARSER_ID

    Parser used to parse a record into fields. (CDR_RECORD_DEF.CDR_PARSER_ID = CDR_PARSER.ID

    RECORD_TABLE_NAME

    Name of the table where records are saved. Usually for header and trailer records, the value of this table is the SOURCE_TABLE while for detail records it is the record (BILLING_RECORD) table

    RECORD_TABLE_CODE

    Code to be used in the record table in-case sub-classing is to be used

    RECORD_IDENTIFIER_TYPE

    If this value is set, then the record will be identified using a CDR_RECORD_IDENTIFIER. Possible values are 'ALL' or 'AT_LEAST_ONE'. These are explained in subsection 3.1.3.

    PERSIST

    Determines if a record should be persisted. Sometimes, one may not need to persist a CDR_RECORD (as in the case where the record is a header of column name). Default value is 'Y' (meaning persist data) otherwise it is 'N' (meaning do not persist data).

    RECORD_CLASS

    The fully qualified class name of that models the table defined by the RECORD_TABLE_NAME

    SOURCE_TABLE_KEY

    This columns holds the column name in RECORD_TABLE_NAME table that points to the SOURCE TABLE. This is used by CDR_RECORD_DEFs.
    For file generator:
    For CDR_RECORD_DEF entries having CODE = 'DETAIL': If this field is empty, the value 'SP_FILE_ID' is used.
    For CDR_RECORD_DEF entries having CODE = 'HEADER' or 'TRAILER': If this field is empty, the value 'ID' is used. This is usually the case when the HEADER table is also the source table. So if the file should contain HEADER, TRAILER and DETAIL records and the data model in the DB contains an extra table for holding the SOURCE info, then all three CDR_RECORD_DEF entries (For HEADER, TRAILER and DETAIL) must contain appropriate value in SOURCE_TABLE_KEY. 
    For file loader:
    This is used only by CDR_RECORD_DEFs with CODE = 'DETAIL'. If this field is empty, the value 'BILLING_SOURCE_ID' is used.
    For example if the 'BILLING_SOURCE_TABLE_NAME' - of the appropriate CDR_FILE_DEF record - is IBASIS_MONTH and the detail record table name (RECORD_TABLE_NAME) is IBASIS_RECORD where IBASIS_RECORD.IBASIS_MONTH_ID = IBASIS_MONTH.ID, then the SOURCE_TABLE_KEY will have a value of ‘IBASIS_MONTH_ID’.

    STATUS_COLUMN_NAME

    String value. Name of the column where the record status must be stored.

    STATUS_VALUE

    Value to be set in status column defined in STATUS_COLUMN_NAME.

    HOOKPOINT_KEY

    If this is set, it will save this value into RECORD_TABLE_NAME.HOOKPOINT_KEY, not a mandatory value.

    SEQUENCE

    SEQUENCE column is used to define multiple record definitions for same record type (i.e. for example multiple record definitions for HEADER record for same File Definition) and define the sequence in which they can appear in the exported file. For example, if an exported file should contain 3 lines in the header and each of these lines has a difference structure, then it is possible to define 3 HEADER record definition entries with SEQUENCE 1, 2 and 3 based on the required order of these lines in the file. Then the file processor with get all the header definitions and handles one by one in the defined sequence, writing the lines matching that record definition only. 

    Important Note: If only one record definition entry is needed for a record type, then the SEQUENCE Column of that record definition entry must be left empty.

     

    Example:

    NameCODESEQUENCEDESCRIPTIONCDR_FILE_DEF_ID RECORD_TABLE_NAME
    DemoFile_Header1HEADER1Header record definition 1123456789DEMO_FILE_HEADER
    DemoFile_Header2HEADER2Header record definition 2 123456789DEMO_FILE_HEADER
    DemoFile_RecordDETAIL Detail record definition 123456789DEMO_FILE_RECORD
    DemoFile_FooterTRAILER Trailer record definition 123456789DEMO_FILE_FOOTER

    3.1.3 CDR Record Identifier (CDR_RECORD_IDENTIFIER)

    A CdrRecordIdentifier consists of:

    • a CDR_RECORD_DEF_ID (CDR Record DEF)
    • a field position (CDR_FIELD_POSITION), and
    • a default value (DEFAULT_FIELD_VALUE).

    A Cdr Record Definition can have zero or more CDR_RECORD_IDENTIFIERs. The CDR Record Identifier is used for identifying a CDR RECORD. For example, a Header Cdr Record might be identified by its first field being ‘HD’. In this example, a header CDR_RECORD_DEF will have an entry in the CDR_RECORD_IDENTIFIER table with CDR_FIELD_POSITION equalling 1 and the default field value equalling ‘HD’. In this situation, the RECORD_IDENTIFIER_TYPE attribute in the CDR_RECORD_DEF table will be ‘ALL’.
    In another example, a CDR detail record might be identified by at least one of the following values; ‘20’,’21’,’22’, in its first field. In this scenario the CDR_RECORD_DEF for the detail record will have three entries in the CDR_RECORD_IDENTIFIER table each with a field position equal to 1 and default field value ‘20’,’21’,’22’, respectively.

    3.1.4 CDR Field Definition (CDR_FIELD_DEF)

    A Cdr Field Defintion (class : CdrFieldDef, Table: CDR_FIELD_DEF), defines a field in a record (CDR_RECORD_DEF). Its attributes are shown in figure 2.1 and has the following columns.

    COLUMN NAME

    DESCRIPTION

    ID

    Unique ID

    NAME

    Name of Field

    CODE

    For sub-classing

    DESCRIPTION

    Description of field

    FILE_SPECIFICATION_NAME

    Name of field in the Specification document

    DB_COLUMN_NAME

    Name of field in the Database table

    CDR_FIELD_FORMAT_DEF_ID

    ID of the format definition used to format the field, (CDR_FIELD_DEF.CDR_FIELD_FORMAT_DEF_ID = CDR_FIELD_FORMAT_DEF.ID)

    SUMMABLE

    Not used

    SUM_FIELD_DEF_ID

    Not used

    MAX_LENGTH

    Defines the maximum length of a field

    FULL_LENGTH

    Defines if the length of the field must be equal to MAX_LENGTH.

    MANDATORY

    Defines if the field must have a value

    FIELD_POSITION

    Defines the position of the field in a record (Not actually used)

    START_INDEX

    Defines start index of field in record. If a record does not have index separated fields, then this value is set to -1

    END_INDEX

    Defines end index of field in record. If a record does not have index separated fields, then this value is set to -1

    FILLER

    Defines what to use to fill the field. For example leading or trailer zero, white space, etc. Used for exporting
    If a white space has to be used as filler, the value of this column must be WHITESPACE.

    PADDING

    Defines where the fillers should be placed on the field. Possible values are LEFT or RIGHT or NONE. Used for exporting
    If NONE is used, no padding is done even  if the FULL_LENGTH is set to 'Y'. So use it cautiously.

    FORMAT

    Defines the format of the field. If present, it is used, otherwise the value in the defined CDR_FIELD_FORMAT_DEF is used

    EXPORTER_METHOD

    A method on the CdrField class that must be called before this field is exported

    EMPTY_WHEN_ZERO

    Sometimes a field might have a value of zero in the database, but should be exported as an empty field. This field can take either 'Y' or 'N' as values. default value is 'N', meaning field is exported as it is, otherwise if the field has a value of zero, it is exported as an empty string

    3.1.5 CDR Record Field Definition (CDR_RECORD_FIELD_DEF)

    A CDR record field definition (Class:CdrRecordFieldDef, Table: CDR_RECORD_FIELD_DEF) defines the position (FIELD_POSITION) of a CdrFieldDef (hence a CdrField) in a CdrRecordDef (hence a CdrRecord). The CdrRecordFieldDef implements the ISequence interface. This interface ensures that no two CdrFieldDefs in a CdrRecordFieldDef have the same position. If a CdrFiedDef is added to a CdrRecordDef with the same position as an already existing CdrFieldDef, the new CdrFielddef will take the place of the old CdrFieldDef and the old CdrFielddef will be pushed on to a position behind.

    Each time a new CdrFieldDef is added to a CdrRecordDef, an entry is made in the CDR_RECORD_FIELD_DEF table. The method addCdrFieldDef(CdrFieldDef, int) takes the CdrFieldDef to be added and the position the field occupies. This method returns the next available position to which a new CdrFieldDef could be added. A CdrRecordDef consists of one or more CdrRecordFieldDefs.

    The following columns are found in the CDR_RECORD_FIELD_DEF table:

    COLUMN NAME

    DESCRIPTION

    ID

    Unique ID

    CDR_RECORD_DEF_ID

    ID of Record Defintion

    CDR_FIELD_DEF_ID

    ID of Field Definition

    FIELD_POSITION

    Position of the field in the record. Start position is 1

    3.1.6 CDR Field Format Definition (CDR_FIELD_FORMAT_DEF)

    A CDR field format definition (Class:CdrFieldFormatDef, Table: CDR_FIELD_FORMAT_DEF) defines the format of a field definition (CDR_FIELD_DEF). It has the following columns:

    Format of time, if this is a time format definition (CODE = 'TIME'. If format is

    ID

    Unique ID

    NAME

    Name of Field format definition

    CODE

    Code used for sub-classing. There are a number of sub-classes as shown in figure 2.1

    DESCRIPTION

    Description of field format definition

    FILLER

    Filler used to format field before export. If present in the corresponding CDR_FIELD_DEF, the filler from CDR_FIELD_DEF is used

    PADDING

    Padding used to format field before export. If present in the corresponding CDR_FIELD_DEF, the padding from CDR_FIELD_DEF is used

    DECIMAL_PLACES

    Number of decimal places to use. If decimal format is present in the corresponding CDR_FIELD_DEF, the format from CDR_FIELD_DEF is used

    DECIMAL_SEPARATOR

    No longer used. The COUNTRY_LOCALE now defines the decimal separator

    DATE_FORMAT

    Format of date, if this is a date format definition (CODE = 'DATE'). If format is present in the corresponding CDR_FIELD_DEF, the format from CDR_FIELD_DEF is used

    DATA_FORMAT

    Format of field data (regular expression), if this is a data format definition (CODE = 'DATA'). If format is present in the corresponding CDR_FIELD_DEF, the format from CDR_FIELD_DEF is used

    TIME_FORMAT

    • .FILE_NAME_COLUMN_NAME= XML_FILE_NAME. So the generated XML file name will be saved to XML_FILE_NAME column of Source table
    • Third CDR_FILE_DEF entry for JSON file generation: CDR_FILE_DEF.FILE_NAME_COLUMN_NAME= JSON_FILE_NAME. So the generated JSON file name will be saved to JSON_FILE_NAME column of Source table

    3.1.2 CDR Record Definition (CDR_RECORD_DEF)

    A CDR Record Definition (CDR_RECORD_DEF) defines a record in a CDR file and has the following columns in the CDR_RECORD_DEF table.

    COLUMN NAME

    DESCRIPTION

    ID

    Unique table ID

    NAME

    Name of record definition

    CODE

    Code for sub-classing should one of the following DETAIL, HEADER, TRAILER

    DESCRIPTION

    Record definition description

    CDR_FILE_DEF_ID

    ID of CDR_FILE_DEF to which this record definition record belongs

    FIELD_SEPARATOR_ID

    Defines what separates one record field from another. Points to a record in the ITEM_SEPARATOR table (CDR_RECORD_DEF.FIELD_SEPARATOR_ID = ITEM_SEPRARATOR.ID)

    MAX_LENGTH

    Defines the length of the record; the default value being -1 meaning record is of variable length

    FULL_LENGTH

    If the MAX_LENGTH attribute is not -1, and the FULL_LENGTH attribute is set to ‘Y’, then the record must be of length MAX_LENGTH. Default value is N

    CDR_PARSER_ID

    Parser used to parse a record into fields. (CDR_RECORD_DEF.CDR_PARSER_ID = CDR_PARSER.ID

    RECORD_TABLE_NAME

    Name of the table where records are saved. Usually for header and trailer records, the value of this table is the SOURCE_TABLE while for detail records it is the record (BILLING_RECORD) table

    RECORD_TABLE_CODE

    Code to be used in the record table in-case sub-classing is to be used

    RECORD_IDENTIFIER_TYPE

    If this value is set, then the record will be identified using a CDR_RECORD_IDENTIFIER. Possible values are 'ALL' or 'AT_LEAST_ONE'. These are explained in subsection 3.1.3.

    PERSIST

    Determines if a record should be persisted. Sometimes, one may not need to persist a CDR_RECORD (as in the case where the record is a header of column name). Default value is 'Y' (meaning persist data) otherwise it is 'N' (meaning do not persist data).

    RECORD_CLASS

    The fully qualified class name of that models the table defined by the RECORD_TABLE_NAME

    SOURCE_TABLE_KEY

    This columns holds the column name in RECORD_TABLE_NAME table that points to the SOURCE TABLE. This is used by CDR_RECORD_DEFs.
    For file generator:
    For CDR_RECORD_DEF entries having CODE = 'DETAIL': If this field is empty, the value 'SP_FILE_ID' is used.
    For CDR_RECORD_DEF entries having CODE = 'HEADER' or 'TRAILER': If this field is empty, the value 'ID' is used. This is usually the case when the HEADER table is also the source table. So if the file should contain HEADER, TRAILER and DETAIL records and the data model in the DB contains an extra table for holding the SOURCE info, then all three CDR_RECORD_DEF entries (For HEADER, TRAILER and DETAIL) must contain appropriate value in SOURCE_TABLE_KEY. 
    For file loader:
    This is used only by CDR_RECORD_DEFs with CODE = 'DETAIL'. If this field is empty, the value 'BILLING_SOURCE_ID' is used.
    For example if the 'BILLING_SOURCE_TABLE_NAME' - of the appropriate CDR_FILE_DEF record - is IBASIS_MONTH and the detail record table name (RECORD_TABLE_NAME) is IBASIS_RECORD where IBASIS_RECORD.IBASIS_MONTH_ID = IBASIS_MONTH.ID, then the SOURCE_TABLE_KEY will have a value of ‘IBASIS_MONTH_ID’.

    STATUS_COLUMN_NAME

    String value. Name of the column where the record status must be stored.

    STATUS_VALUE

    Value to be set in status column defined in STATUS_COLUMN_NAME.

    HOOKPOINT_KEY

    If this is set, it will save this value into RECORD_TABLE_NAME.HOOKPOINT_KEY, not a mandatory value.

    SEQUENCE

    SEQUENCE column is used to define multiple record definitions for same record type (i.e. for example multiple record definitions for HEADER record for same File Definition) and define the sequence in which they can appear in the exported file. For example, if an exported file should contain 3 lines in the header and each of these lines has a difference structure, then it is possible to define 3 HEADER record definition entries with SEQUENCE 1, 2 and 3 based on the required order of these lines in the file. Then the file processor with get all the header definitions and handles one by one in the defined sequence, writing the lines matching that record definition only. 

    Important Note: If only one record definition entry is needed for a record type, then the SEQUENCE Column of that record definition entry must be left empty.

     

    Example:

    NameCODESEQUENCEDESCRIPTIONCDR_FILE_DEF_ID RECORD_TABLE_NAME
    DemoFile_Header1HEADER1Header record definition 1123456789DEMO_FILE_HEADER
    DemoFile_Header2HEADER2Header record definition 2 123456789DEMO_FILE_HEADER
    DemoFile_RecordDETAIL Detail record definition 123456789DEMO_FILE_RECORD
    DemoFile_FooterTRAILER Trailer record definition 123456789DEMO_FILE_FOOTER

    3.1.3 CDR Record Identifier (CDR_RECORD_IDENTIFIER)

    A CdrRecordIdentifier consists of:

    • a CDR_RECORD_DEF_ID (CDR Record DEF)
    • a field position (CDR_FIELD_POSITION), and
    • a default value (DEFAULT_FIELD_VALUE).

    A Cdr Record Definition can have zero or more CDR_RECORD_IDENTIFIERs. The CDR Record Identifier is used for identifying a CDR RECORD. For example, a Header Cdr Record might be identified by its first field being ‘HD’. In this example, a header CDR_RECORD_DEF will have an entry in the CDR_RECORD_IDENTIFIER table with CDR_FIELD_POSITION equalling 1 and the default field value equalling ‘HD’. In this situation, the RECORD_IDENTIFIER_TYPE attribute in the CDR_RECORD_DEF table will be ‘ALL’.
    In another example, a CDR detail record might be identified by at least one of the following values; ‘20’,’21’,’22’, in its first field. In this scenario the CDR_RECORD_DEF for the detail record will have three entries in the CDR_RECORD_IDENTIFIER table each with a field position equal to 1 and default field value ‘20’,’21’,’22’, respectively.

    3.1.4 CDR Field Definition (CDR_FIELD_DEF)

    A Cdr Field Defintion (class : CdrFieldDef, Table: CDR_FIELD_DEF), defines a field in a record (CDR_RECORD_DEF). Its attributes are shown in figure 2.1 and has the following columns.

    COLUMN NAME

    DESCRIPTION

    ID

    Unique ID

    NAME

    Name of Field

    CODE

    For sub-classing

    DESCRIPTION

    Description of field

    FILE_SPECIFICATION_NAME

    Name of field in the Specification document

    DB_COLUMN_NAME

    Name of field in the Database table

    CDR_FIELD_FORMAT_DEF_ID

    ID of the format definition used to format the field, (CDR_FIELD_DEF.CDR_FIELD_FORMAT_DEF_ID = CDR_FIELD_FORMAT_DEF.ID)

    SUMMABLE

    Not used

    SUM_FIELD_DEF_ID

    Not used

    MAX_LENGTH

    Defines the maximum length of a field

    FULL_LENGTH

    Defines if the length of the field must be equal to MAX_LENGTH.

    MANDATORY

    Defines if the field must have a value

    FIELD_POSITION

    Defines the position of the field in a record (Not actually used)

    START_INDEX

    Defines start index of field in record. If a record does not have index separated fields, then this value is set to -1

    END_INDEX

    Defines end index of field in record. If a record does not have index separated fields, then this value is set to -1

    FILLER

    Defines what to use to fill the field. For example leading or trailer zero, white space, etc. Used for exporting
    If a white space has to be used as filler, the value of this column must be WHITESPACE.

    PADDING

    Defines where the fillers should be placed on the field. Possible values are LEFT or RIGHT or NONE. Used for exporting
    If NONE is used, no padding is done even  if the FULL_LENGTH is set to 'Y'. So use it cautiously.

    FORMAT

    Defines the format of the field. If present, it is used, otherwise the value in the defined CDR_FIELD_FORMAT_DEF is used

    EXPORTER_METHOD

    A method on the CdrField class that must be called before this field is exported

    EMPTY_WHEN_ZERO

    Sometimes a field might have a value of zero in the database, but should be exported as an empty field. This field can take either 'Y' or 'N' as values. default value is 'N', meaning field is exported as it is, otherwise if the field has a value of zero, it is exported as an empty string

    3.1.5 CDR Record Field Definition (CDR_RECORD_FIELD_DEF)

    A CDR record field definition (Class:CdrRecordFieldDef, Table: CDR_RECORD_FIELD_DEF) defines the position (FIELD_POSITION) of a CdrFieldDef (hence a CdrField) in a CdrRecordDef (hence a CdrRecord). The CdrRecordFieldDef implements the ISequence interface. This interface ensures that no two CdrFieldDefs in a CdrRecordFieldDef have the same position. If a CdrFiedDef is added to a CdrRecordDef with the same position as an already existing CdrFieldDef, the new CdrFielddef will take the place of the old CdrFieldDef and the old CdrFielddef will be pushed on to a position behind.

    Each time a new CdrFieldDef is added to a CdrRecordDef, an entry is made in the CDR_RECORD_FIELD_DEF table. The method addCdrFieldDef(CdrFieldDef, int) takes the CdrFieldDef to be added and the position the field occupies. This method returns the next available position to which a new CdrFieldDef could be added. A CdrRecordDef consists of one or more CdrRecordFieldDefs.

    The following columns are found in the CDR_RECORD_FIELD_DEF table:

    COLUMN NAME

    DESCRIPTION

    ID

    Unique ID

    CDR_RECORD_DEF_ID

    ID of Record Defintion

    CDR_FIELD_DEF_ID

    ID of Field Definition

    FIELD_POSITION

    Position of the field in the record. Start position is 1

    3.1.6 CDR Field Format Definition (CDR_FIELD_FORMAT_DEF)

    A CDR field format definition (Class:CdrFieldFormatDef, Table: CDR_FIELD_FORMAT_DEF) defines the format of a field definition (CDR_FIELD_DEF). It has the following columns:

    ID

    Unique ID

    NAME

    Name of Field format definition

    CODE

    Code used for sub-classing. There are a number of sub-classes as shown in figure 2.1

    DESCRIPTION

    Description of field format definition

    FILLER

    Filler used to format field before export. If present in the corresponding CDR_FIELD_DEF, the format filler from CDR_FIELD_DEF is used

    COUNTRY_LOCALEPADDING

    Locale Padding used to define the decimal separator, Used for Decimal (CODE = 'DECIMAL'), signed-decimal (CODE = 'SIGNED-DECIMAL') format definitions. Examples are such as "NL" and "GB"

    3.1.7 Item Separator (ITEM_SEPARATOR)

    An Item Separator (Class:ItemSeparator, Table: ITEM_SEPARATOR) defines how an item (CDR_RECORD (in a file) or CDR_FIELD (in a record)) is separated. An item separator has a SEPARATOR attribute that defines what separates a record/field from another. The default separator for a CDR_RECORD is a carriage return line feed while there is no default separator for a CDR_FIELD. This has to be defined.

    An Item separator is used by a CDR Parser to parse a CDR record and a CDR Field. Special types of Item separator exists. These include the Index Item Separator and the Null Item separator. As a NULL cannot be defined in the database, NULL item separator (Null delimiter) has been defined. When a NULL item separator is used, the system will set the correct Java definition of a null string as the separator. When records or fields are separated by indexes, the Index Item Separator (Index delimiter) is used. In order to use an Index Item separator the start index, end index and the max. length of the first record/field must be defined. Please note that the start index, end index and max. length on an Index Item separator are used only to identify the first record/field. The locations of the subsequent fields must be defined in the CDR_FIELD_DEF.

    The Item Separator table has the following columns:

    COLUMN NAME

    DESCRIPTION

    ID

    Unique ID

    NAME

    Name of separator

    CODE

    Code for sub-classing

    DESCRIPTION

    Description of separator

    SEPARATOR

    Defines the separator itself

    START_INDEX

    Defines the start index for Indexed fields delimiters (Non indexed delimiters will have a value of -1)

    END_INDEX

    Defines the end index of the first field for indexed delimiters (Non indexed delimiters will have a value of -1)

    MAX_LENGTH

    Maximum length of first field. Used by Indexed fields delimiters

    START_QUALIFIER

    A symbol to indicate the start of a field. A field might contain a delimiter but has a qualifier such as an open quotation mark. For example a semi column field separated record might look like this 24;35;"552;3636";454; and the START_QUALIFIER is " and END_QUALIFIER is ". The this record has 4 fields, as 552;3636 is considered a field since it is between the START AND END QUALIFIERs. This column is not mandatory

    END_QUALIFIER

    A symbol to indicate the end of a field . It is not mandatory

    3.1.8 CDR Parser

    A CDR Parser (Class:CdrParser, Table: CDR_PARSER) parses a CDR File into CDR records and a CDR record into CDR Fields. A CDR file definition knows the CDR parser it needs to parse a CDR file into CDR records while a CDR record knows the CDR parser it needs to parse a CDR record into fields. By default, a CDR Parser will use the carriage return line feed to parse a CDR file into CDR records. However, to parse a CDR record into CDR Fields a field separator (ITEM_SEPERATOR) is needed. This is set by the appropriate CDR Record Definition at run time.

    3.1.9 File Transmitter

    The File transmitter (Class:FileTransmitter, Table: FILE_TRANSMITTER) defines where a CDR File is located before processing, where it is put after successful or unsuccessful processing, for loading. It also knows where an export file is to be created and where it should be put after successful or unsuccessful file generation. The File transmitter handles the writing to a file during file generation (export).

    3.1.10 CDR File Processor

    The CDR File Processor (Class:CdrFileProcessor, Table: CDR_FILE_PROCESSOR) handles both loading and generation (export) of CDR files. A CDR File processor knows the CDR File Definition and File Transmitter to use when handling a CDR File. The CDR FILE PROCESSOR has as sub classes CdrFileLoader (CODE = 'LOADER') and CdrFileGenerator ( CODE = 'GENERATOR'). As the names imply, the CdrFileLoader handles loading of files while the CdrFileGenerator handles generating of files. The CdrFileGenerator does not handle exporting of a file to any third party. This will be handled by a different process.

    The CDR_FILE_PROCESSOR table has the following columns

    COLUMN NAME

    DESCRIPTION

    ID

    Unqiue ID

    NAME

    Name of file processor

    CODE

    Code for sub-classing (For loading CODE = 'LOADER' : For exporting CODE = 'GENERATOR'

    DESCRIPTION

    Description of file processor

    CDR_FILE_DEF_ID

    File definition ID of CDR_FILE_DEF processed by this CDR_FILE_PROCESSOR record

    FILE_TRANSMITTER_ID

    File transmitter used to located (before processing) and move files (after processing)

    BRAND_ID

    ID of BRAND that will be processed by this CDR_FILE_PROCESSOR record. Used only for generating export files. The BRAND_ID must equal the BRAND_ID on the BILLING_SOURCE_TABLE_NAME of the CDR_DEF_ID record defined by the ID of CDR_FILE_DEF processed by this CDR_FILE_PROCESSOR record. For example, for export of CDR files using the SP_FILE_HEADER as source table, the only records in the SP_FILE_HEADER table whose BRAND_ID is the same as the BRAND_ID for this record will be processed by this record.

    3.1.11 Invoking Hookpoint in the Pre or Post Process

    It is possible to invoke a hookpoint in both the pre process and post process of file processing. This functionality is activated by adding the Hookpoint Key to either the PRE_PROCESSOR_HOOKPOINT_KEY or POST_PROCESSOR_HOOKPOINT_KEY column of the CDR_FILE_DEF. If the PRE_PROCESSOR_HOOKPOINT_KEY value is not emty the hookpoint key with the prefix "FILE_PROCESSOR_" with the brand of the CDR_FILE_PROCESSOR is attempted invoked during the specific process. The particular CdrFile object by the FileProcessor is inserted into context under the key "CDR_FILE".

    3.2 Generating (Export) Files

    In order to generate CDR files for export, in addition to the tables defined in 3.1 above, the following table must also be defined.

    ...

    format field before export. If present in the corresponding CDR_FIELD_DEF, the padding from CDR_FIELD_DEF is used

    DECIMAL_PLACES

    Number of decimal places to use. If decimal format is present in the corresponding CDR_FIELD_DEF, the format from CDR_FIELD_DEF is used

    DECIMAL_SEPARATOR

    No longer used. The COUNTRY_LOCALE now defines the decimal separator

    DATE_FORMAT

    Format of date, if this is a date format definition (CODE = 'DATE'). If format is present in the corresponding CDR_FIELD_DEF, the format from CDR_FIELD_DEF is used

    DATA_FORMAT

    Format of field data (regular expression), if this is a data format definition (CODE = 'DATA'). If format is present in the corresponding CDR_FIELD_DEF, the format from CDR_FIELD_DEF is used

    TIME_FORMAT

    Format of time, if this is a time format definition (CODE = 'TIME'. If format is present in the corresponding CDR_FIELD_DEF, the format from CDR_FIELD_DEF is used

    COUNTRY_LOCALE

    Locale used to define the decimal separator, Used for Decimal (CODE = 'DECIMAL'), signed-decimal (CODE = 'SIGNED-DECIMAL') format definitions. Examples are such as "NL" and "GB"

    3.1.7 Item Separator (ITEM_SEPARATOR)

    An Item Separator (Class:ItemSeparator, Table: ITEM_SEPARATOR) defines how an item (CDR_RECORD (in a file) or CDR_FIELD (in a record)) is separated. An item separator has a SEPARATOR attribute that defines what separates a record/field from another. The default separator for a CDR_RECORD is a carriage return line feed while there is no default separator for a CDR_FIELD. This has to be defined.

    An Item separator is used by a CDR Parser to parse a CDR record and a CDR Field. Special types of Item separator exists. These include the Index Item Separator and the Null Item separator. As a NULL cannot be defined in the database, NULL item separator (Null delimiter) has been defined. When a NULL item separator is used, the system will set the correct Java definition of a null string as the separator. When records or fields are separated by indexes, the Index Item Separator (Index delimiter) is used. In order to use an Index Item separator the start index, end index and the max. length of the first record/field must be defined. Please note that the start index, end index and max. length on an Index Item separator are used only to identify the first record/field. The locations of the subsequent fields must be defined in the CDR_FIELD_DEF.

    The Item Separator table has the following columns:

    COLUMN NAME

    DESCRIPTION

    ID

    Unique ID

    NAME

    Name of separator

    CODE

    Code for sub-classing

    DESCRIPTION

    Description of separator

    SEPARATOR

    Defines the separator itself

    START_INDEX

    Defines the start index for Indexed fields delimiters (Non indexed delimiters will have a value of -1)

    END_INDEX

    Defines the end index of the first field for indexed delimiters (Non indexed delimiters will have a value of -1)

    MAX_LENGTH

    Maximum length of first field. Used by Indexed fields delimiters

    START_QUALIFIER

    A symbol to indicate the start of a field. A field might contain a delimiter but has a qualifier such as an open quotation mark. For example a semi column field separated record might look like this 24;35;"552;3636";454; and the START_QUALIFIER is " and END_QUALIFIER is ". The this record has 4 fields, as 552;3636 is considered a field since it is between the START AND END QUALIFIERs. This column is not mandatory

    END_QUALIFIER

    A symbol to indicate the end of a field . It is not mandatory

    3.1.8 CDR Parser

    A CDR Parser (Class:CdrParser, Table: CDR_PARSER) parses a CDR File into CDR records and a CDR record into CDR Fields. A CDR file definition knows the CDR parser it needs to parse a CDR file into CDR records while a CDR record knows the CDR parser it needs to parse a CDR record into fields. By default, a CDR Parser will use the carriage return line feed to parse a CDR file into CDR records. However, to parse a CDR record into CDR Fields a field separator (ITEM_SEPERATOR) is needed. This is set by the appropriate CDR Record Definition at run time.

    3.1.9 File Transmitter

    The File transmitter (Class:FileTransmitter, Table: FILE_TRANSMITTER) defines where a CDR File is located before processing, where it is put after successful or unsuccessful processing, for loading. It also knows where an export file is to be created and where it should be put after successful or unsuccessful file generation. The File transmitter handles the writing to a file during file generation (export).

    3.1.10 CDR File Processor

    The CDR File Processor (Class:CdrFileProcessor, Table: CDR_FILE_PROCESSOR) handles both loading and generation (export) of CDR files. A CDR File processor knows the CDR File Definition and File Transmitter to use when handling a CDR File. The CDR FILE PROCESSOR has as sub classes CdrFileLoader (CODE = 'LOADER') and CdrFileGenerator ( CODE = 'GENERATOR'). As the names imply, the CdrFileLoader handles loading of files while the CdrFileGenerator handles generating of files. The CdrFileGenerator does not handle exporting of a file to any third party. This will be handled by a different process.

    The CDR_FILE_PROCESSOR table has the following columns

    COLUMN NAME

    DESCRIPTION

    ID

    Unqiue ID

    NAME

    Name of file processor

    CODE

    Code for sub-classing (For loading CODE = 'LOADER' : For exporting CODE = 'GENERATOR'

    DESCRIPTION

    Description of file processor

    CDR_FILE_DEF_ID

    File definition ID of CDR_FILE_DEF processed by this CDR_FILE_PROCESSOR record

    FILE_TRANSMITTER_ID

    File transmitter used to located (before processing) and move files (after processing)

    BRAND_ID

    ID of BRAND that will be processed by this CDR_FILE_PROCESSOR record. Used only for generating export files. The BRAND_ID must equal the BRAND_ID on the BILLING_SOURCE_TABLE_NAME of the CDR_DEF_ID record defined by the ID of CDR_FILE_DEF processed by this CDR_FILE_PROCESSOR record. For example, for export of CDR files using the SP_FILE_HEADER as source table, the only records in the SP_FILE_HEADER table whose BRAND_ID is the same as the BRAND_ID for this record will be processed by this record.

    3.1.11 Invoking Hookpoint in the Pre or Post Process

    It is possible to invoke a hookpoint in both the pre process and post process of file processing. This functionality is activated by adding the Hookpoint Key to either the PRE_PROCESSOR_HOOKPOINT_KEY or POST_PROCESSOR_HOOKPOINT_KEY column of the CDR_FILE_DEF. If the PRE_PROCESSOR_HOOKPOINT_KEY value is not emty the hookpoint key with the prefix "FILE_PROCESSOR_" with the brand of the CDR_FILE_PROCESSOR is attempted invoked during the specific process. The particular CdrFile object by the FileProcessor is inserted into context under the key "CDR_FILE".

    3.2 Generating (Export) Files

    In order to generate CDR files for export, in addition to the tables defined in 3.1 above, the following table must also be defined.

    Unlike for loading where the header/trailer information of a CDR File is stored in a BILLING SOURCE table, the header/trailer information used for generating an export file is stored in an SP_FILE_HEADER table and the BILLING RECORD table must have a column named SP_FILE_ID, that points to the SP_FILE_HEADER record that will be used to generate the export file. An SP_FILE_HEADER table must have at least the following columns ID, FILENAME, BRAND_ID, STATUS and FILE_CREATED_DATE. These columns are inherently used within the model.

    Sometimes, when generating a file for export, some information needed for export (usually in the header and/or trailer records) is not yet present. This information could be generated by a pre-defined stored procedure that will be called before any file is generated for export. If such a pre-defined stored procedure is needed, then it must accept one the SP_FILE_HEADER_ID as parameter since all processing must depended on the SP_FILE to be generated. The column PRE_EXPOR_PROCEDURE on the CDR_FILE_DEF table holds the value of the pre-defined stored procedure. If such a procedure exists, it will be called before the export file is generated.

    To generate any other file, the header and trailer records should be stored in the BILLING_SOURCE_TABLE_NAME table defined in the CDR_FILE_DEF while the detail record definition (CDR_RECORD_DEF) pointing to this CDR_FILE_DEF should have a SOURCE_TABLE_KEY appropriate defined as described in section  3.1.2 CDR Record Definition (CDR_RECORD_DEF) and the BILLING_SOURCE_TABLE_NAME table must have a column following columns ID, FILENAME, BRAND_ID, STATUS and FILE_CREATED_DATE. These columns are inherently used within the model.

    Sometimes, when generating a file for export, some information needed for export (usually in the header and/or trailer records) is not yet present. This information could be generated by a pre-defined stored procedure that will be called before any file is generated for export. If such a pre-defined stored procedure is needed, then it must accept one the SP_FILE_HEADER_ID as parameter since all processing must depended on the SP_FILE to be generated. The column PRE_EXPOR_PROCEDURE on the CDR_FILE_DEF table holds the value of the pre-defined stored procedure. If such a procedure exists, it will be called before the export file is generated.

    To generate any other file, the header and trailer records should be stored in the BILLING_SOURCE_TABLE_NAME table defined in the CDR_FILE_DEF while the detail record definition (CDR_RECORD_DEF) pointing to this CDR_FILE_DEF should have a SOURCE_TABLE_KEY appropriate defined as described in section  3.1.2 CDR Record Definition (CDR_RECORD_DEF) and the BILLING_SOURCE_TABLE_NAME table must have a column following columns ID, FILENAME, BRAND_ID, STATUS and FILE_CREATED_DATE. All other processing is the same as described above.

    It should be noted that only records in the BILLING_SOURCE_TABLE_NAME table that have status = 1 (ready for export) will be pickec up by the CDR_PROCESSING_ENGINE (CODE = 'GENERATOR')

    3.3 Setup Of File Loader and Generator Engines

    The CdrFileProcessing engine handles loading files and generating files for export. The CdrFileProcessignEngine takes at most three parameters.

    • Parameter 1 is the engine name (“CDR_FILE_LOADER_ENGINE” (for CdrFileLoaderEngine) or “CDR_EXPOR_FILE_GENERATOR_ENGINE” (for CdrExportFileGeneratoeEngine)), or “CDR_FILE_PROCESSING_ENGINE” (for both loading and file generation)).
    • Parameter 2 is the Engine Instance Number
    • Parameter 3 is the type of processing (“LOADER” (for CdrFileLoaderEngine) or “GENERATOR” (for CdrExportFileGeneratoeEngine)).
      If parameter 3 is not set, then the engine will perform both loading and export file generation.

    4 Parameter Setup

    The following table shows the parameter setup that can be used by CdrFileprocessorEngine. The parameters are under the node "FILE_PROCESSING".

    NAME

    VALUE

    TYPE

    OPTIONAL

    DESCRIPTION

    MOVE_INVALID_FILE

    true|false

    String

    Yes

    Defines whether the engine should move files if they do not meet the pattern name criteria

    MAX_FILES_TO_LOAD

    integer bigger than 0

    Integer

    Yes

    Defines the number of files that are processed in a single engine iteration. If not present, the default value is 50

    BATCH_SIZE

    integer bigger than 0

    Integer

    Yes

    Number of SQL statements size used to load in batch. If not present, the default value is 100

    DB_SYS_PACKAGE_NAME

    SYS/CORE_SYS

    String

    Yes

    Defines the name of the sys package to be used in SQL-statements

    CHARACTER_ENCODING<Valid character encoding>StringYesDefines the character encoding to be used while importing the files. If this parameter exists, the encoding specified in this parameter will be used to read the file. Otherwise the default encoding will be used.

     

    All other processing is the same as described above.

    It should be noted that only records in the BILLING_SOURCE_TABLE_NAME table that have status = 1 (ready for export) will be pickec up by the CDR_PROCESSING_ENGINE (CODE = 'GENERATOR')

    3.3 Setup Of File Loader and Generator Engines

    The CdrFileProcessing engine handles loading files and generating files for export. The CdrFileProcessignEngine takes at most three parameters.

    • Parameter 1 is the engine name (“CDR_FILE_LOADER_ENGINE” (for CdrFileLoaderEngine) or “CDR_EXPOR_FILE_GENERATOR_ENGINE” (for CdrExportFileGeneratoeEngine)), or “CDR_FILE_PROCESSING_ENGINE” (for both loading and file generation)).
    • Parameter 2 is the Engine Instance Number
    • Parameter 3 is the type of processing (“LOADER” (for CdrFileLoaderEngine) or “GENERATOR” (for CdrExportFileGeneratoeEngine)).
      If parameter 3 is not set, then the engine will perform both loading and export file generation.

    4 Parameter Setup

    The following table shows the parameter setup that can be used by CdrFileprocessorEngine. The parameters are under the node "FILE_PROCESSING".

    NAME

    VALUE

    TYPE

    OPTIONAL

    DESCRIPTION

    MOVE_INVALID_FILE

    true|false

    String

    Yes

    Defines whether the engine should move files if they do not meet the pattern name criteria

    MAX_FILES_TO_LOAD

    integer bigger than 0

    Integer

    Yes

    Defines the number of files that are processed in a single engine iteration. If not present, the default value is 50

    BATCH_SIZE

    integer bigger than 0

    Integer

    Yes

    Number of SQL statements size used to load in batch. If not present, the default value is 100

    DB_SYS_PACKAGE_NAME

    SYS/CORE_SYS

    String

    Yes

    (Mandatory if CORE_SYS package is used)

    Defines the name of the sys package to be used in SQL-statements

    Default SYS (if this parameter is not defined)

    CHARACTER_ENCODING<Valid character encoding>StringYesDefines the character encoding to be used while importing the files. If this parameter exists, the encoding specified in this parameter will be used to read the file. Otherwise the default encoding will be used.

    5  MONITORING

    5.1 File Generator

    If the File Processor engine cannot generate the file due to an exception, it will insert an entry into EXCEPTION_LOG table. The ID from EXCEPTION_LOG.EXCEPTION_STACK_TRACE_ID can be used to find the corresponding entry from EXCEPTION_STACK_TRACE, which contains the stack trace of the exception. 

    Code Block
    languagesql
    select * from EXCEPTION_LOG where TABLE_NAME = 'CDR_FILE_PROCESSOR' and SOURCE_KEY = '<CDR_FILE_PROCESSOR.NAME for corresponding CDR_FILE_PROCESSOR entry>' order by id desc; 

    5.1 File Loader

    If the File Processor engine cannot load  the file due to an exception or due to errors while parsing the file, it will insert an entry into LOAD_ERRORS table. 

    File Processor engine inserts an entry into CDR_FILE_PROCESSOR_LOG table upon successful loading of the file.