See the NOVUS Walkthrough Guide for general information about the system.
The INBORD program processes data from a csv file, both sum insured + premium.
It will handle three types:
- risk go to risk structures
- premium - IBA - paid to insurers
- claim - IBA & CLAIM - extracted from insurers
What does INBORD do?
- INBORD captures the required parameters to drive the import. This depends on the type of bordereau and nature of the data in the bordereau.
- The input data for this program will depend on the cover holder and the type of bordereau being processed. The first character of the coverholder bordereau style drives the input.
- The input data should be saved in the BDX folder within the w drive of the data set.
- The program reads the .csv (comma separated variable) file in question and populates a temporary holding table called EXTERNAL_LOAD. The data is copied over line for line without any attempt to validate or cross validate the data. This step always succeeds. The csv file is renamed to its bordereau ID and stored in the bdx folder.
- The xls name is also requested but has no strategic influence on processing - this is for memorandum purposes so the user can find the xls file from which the csv file was extracted.
- The .csv file can be viewed once it has been specified.
- The program assigns a unique bordereau ID to the operation based on the input parameters. This should be noted in order to view the processing in NOVUS.
- The program applies a specific template (bordereau style) to the data in the EXTERNAL LOAD table as dictated by the nature of the bordereau, the cover holder and the business class. At the same time the data is rationalized by certificate number so that any ‘ins and outs’ are ironed out. If the data is malformed then this step will fail, but provided the layout of the bordereau is consistent then this will succeed. The rationalised data is loaded into the TRAN_LOG_PEND table
- The program allows the users to inspect the records in a separate window.
- The program then applies certain business rules which the data must satisfy. These rules can be enhanced at any time at the users’ request. The more generic rules include checking that there is a contract in place for the attachment date of the certificate or, in the case of claims, ensuring that the loss date falls within the period of the certificate. More exotic rules could include checking that the premium is generally in line with the sums insured and flagging records which are outside a specified standard deviation.
- The records are then grouped into ‘issues’ or ‘passes’. ‘Passes’ are written to the TRAN_LOG table where they remain until cleared down. ‘Issues’ and ‘passes’ can be inspected separately using the windows provided. All issues must be resolved before the posting process can take place. The records having issues are tagged with a code to signify the nature of the issue. This is the stage where the ‘split claim’ function applies (for a claims bordereau).
- The final posting process depends on the nature of the bordereau:
The certificate details are written to the risk_details area of the relevant contract.
The certificate details are written to the risk_details area of the relevant contract if the certificate number is new to the system. Otherwise the record is overwritten with the latest information appertaining to period of cover, sums insured and premium.
The records in the TRAN_LOG table are ordered and grouped together by contract, underwriting year, commission rate and market version number. Each bordereau record may contain separate sums insured and premium for each section. A single consolidated transaction accommodating each section (in the ETRAN program) is written for the sum of the premium for a given contract, underwriting year, commission rate and market version number. Each transaction has a header record (use the ETRANH program) which carries the inward bordereau ID. The ETRANH program is the best starting point to provide an overview of the processing – query the database by bordereau ID.
The construction of the items in each transaction can depend on the nature of the bordereau feed – which usually only contains gross premium by section. Within each set of records for a given contract and underwriting year the grouping makes sure that the set is consistent with respect to cover holder commission. The set is further subdivided if different market versions apply.
The market is referenced to ascertain the underwriters and their respective brokerage figures. The insurers IBA (insurance brokerage account) lines are calculated according to their signed line and brokerage values to which the coverholder’s commission has been added. The set of records in the IBA ledger will comprise a line for the coverholder, a line for each insurer and a line for brokerage.
IBA header records are written to the IBA_TTL table (in the ETRANH program) tagged with the bordereau ID. The TRAN_LOG_PEND records are stamped with the IBA transaction number to facilitate reconciliation.
This is processed in a similar manner as far as the IBA is concerned (except there is no client commission or brokerage concerned).
If a ‘holding record’ in the claims system for the stated claims file has been set up for a specified date of loss and certificate then the split claim holding area is populated with details of the claim.
The program supports splitters which allow re-runs on the bordereau for the same period. Any single character splitter can be used which is appended to the bordereau ID. Repeating the same bordereau without a splitter is not supported but care must be taken if a splitter is used to ensure that the records are not duplicates.
Prior to the final posting stage the process can be rolled back by deleting the bordereau.
The program ‘remembers’ how far it has proceeded with a given bordereau and in the event that the process is curtailed part-way through then the program will pick the process up from that point when re-run.
v 5.0.0 – Updated to latest version
v15M - WIP - to Eritrea.
v13J - FMX_224. moved location of sqlldr control files to q:\scripts\sqlctl.
v13M - push button on csv file display corrected.
v6T - First version of this form to use the new style. No other updates except the restyle.