...
Date: | Author: | 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 201307 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 2014 | KTH | 1.12.2 | Added new Parameter: CHARACTER_ENCODING | |||
| 30 October 201530 October 2015 | KTH | 1.13 | Added new column SEQUENCE to CDR_RECORD_DEF table Added new column XML_FILE_NAME_COLUMN to CDR_FILE_DEF table (Available for FileProcessor Integration project version 2.2) |
...
A CDR record is made up of one or more CDR Fieldsfields, separated by a pre-defined separator.
A CDR record may be identified by one or more CDR_RECORD_IDENTIFIERs. The default record (NO CDR_RECORD_IDENTIFIER) is a detail record.
A CDR Field contains values in a pre-defined format, which will henceforth be referred to as a CDRFieldFormatDef (CDR_FIELD_FORMAT_DEF).
...
A CDR File is made up of one or more physical lines (lines in a normal file). A CDR File is also made up of one or more logical lines. A physical line may contain one or more logical lines. A logical line represents a CDR Recordrecord. Hence, it is possible for a logical line to span one or more physical lines. A delimiter or separator determines the separation of a physical line into logical lines.
...
To model the description presented in section 2.1 , the following tables are defined:
...
A file definition (CDR_FILE_DEF) defines the source table where the source information of files to be loaded are stored. The file definition defines a BILLING_SOURCE_CLASS, a filename format, and a pre- and post-processor method. These methods - if present - must be located in the defined BILLING_SOURCE_CLASS. If the filename format is present, then only filenames that match this format will be loaded, otherwise all files defined in the incoming folder will be loaded. The location of the incoming, processed and error folders are defined in a file transmitter (FILE_TRANSMITTER) linked to a file processor (CDR_FILE_PROCESSOR). A file definition also defines a parser (CDR_PARSER) for parsing the file into one or more records.
...
Each record definition consists of one or more field definitions (CDR_FIELD_DEF) that which define the fields that make up a record and the source table of the values these fields will assume in the export file. A record definition contains a field separator (ITEM_SEPARATOR : CDR_RECORD_DEF.FIELD_SEPARATOR_ID = ITEM_SEPARATOR.ID) that defines a delimiter for separating fields within a record.
A record definition also defines a parser (CDR_PARSER) for parsing the record into one or more fields using the defined field separator.
...
A CDR File (CDR_FILE_DEF) is processed by a CDR File Processor (CDR_FILE_PROCESSOR), i.e. the file is loaded and parsed or generated and made ready for export. This processing is modeled modelled by the following classes.
...
The above design is shown in figure 2.3.1 below:
Figure 2.3.1 File Processor UML.
...
To configure the loading of a file, the following must be defined or configured.
3.1.1 CDR File Definition (CDR_FILE_DEF)
A CdrFileDef defines a CDR file and is defined by a unique ID and KEY. A CDR File Definition must define a CdrParser (CDR_PARSER_ID pointing to a record in the CDR_PARSER table) to be used for parsing the records in the CDR File, and a record separator (RECORD_SEPARATOR_ID pointing to a record in the ITEM_SEPARATOR table). In a normal scenario, it is assumed that the record separator will be a carriage return line feed. In addition to the above mentioned attributes some more attributes are also present as shown in figure 1.1. A CdrFiledef consists of one or more CdrRecordDefs. The column present in the CDR_FILE_DEF table have been presented in figure 2.1. Some important columns worth describing include:
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 record 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 Processpre-process. Is invoked with the prefix FILE_PROCESSOR_. |
POST_PROCESSOR_HOOKPOINT_KEY | Sets the Hookpoint key to be called in the Post Processpost-process. Is invoked with the prefix FILE_PROCESSOR_. |
| XML_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 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 CDR_FILE_DEF.XML_FILE_NAME_COLUMN is empty. If both a flat file and an XML file should be generated on the same source record:
|
...
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 for 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 section 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. | ||||||||||||||||||||||||||||||
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 defintions definitions for HEDAER 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:
|
...
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_IDENTIFER 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.
...
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 |
PADDING | Defines where the fillers should be placed on the field. Possible values are LEFT or RIGHT or NONE. Used for exporting |
FORMAT | Defines the format of the field. If present, it is used, else 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 |
...
A CDR field format definition (Class:CdrFieldFormatDef, Table: CDR_FIELD_FORMAT_DEF) defines the format of a field defitnion definition (CDR_FIELD_DEF). It has the following columns:
...
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.
...
- 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.
...
NAME | VALUE | TYPE | OPTIONAL | DESCRIPTION |
|---|---|---|---|---|
MOVE_INVALID_FILE | true|false | String | Yes | Defines if whether the engine should move files in the event 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> | String | Yes | Defines 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. |
...