AU2007200869A1

AU2007200869A1 - Data Exchange Between Insurer and Repairer

Info

Publication number: AU2007200869A1
Application number: AU2007200869A
Authority: AU
Inventors: Brett Lyons; John Uren
Original assignee: Insurance Australia Ltd
Current assignee: Insurance Australia Ltd
Priority date: 2001-03-01
Filing date: 2007-02-28
Publication date: 2007-03-22
Also published as: AUPR346201A0; NZ527509A; WO2002071281A1

Description

STitle 00Data Exchange Between Insurer and Repairer Technical Field O 5 This invention concerns a data exchange, and in particular a computer 0 system for exchanging data between an insurer and a repairer, for instance between software applications of a vehicle insurer and a crash repairer. The invention also concerns the structure of a database in which the messages are stored. In a further aspect it concerns a method of exchanging the data.

Summary of the Invention The invention is a computer system for exchanging data between a software application of an insurer and a repairer, comprising A message transceiving station, to send messages to and receive messages from the repairer. The messages being one of a closed set in the form of data wrapped in syntax to allow them to be mapped into a complex database structure which obeys relationship constraints and has referential integrity. The incoming messages are usually zipped.

A message receiver to unpack received messages to retrieve their data content.

A message data content parser to parse the data content and write it to tables organised in a predetermined schema in a first database that does not have the relationship constraints and referential integrity.

A data collector to extract the data from the first database, validate it and store it in tables organised in the predetermined schema in a second database that does have the relationship constraints and referential integrity.

An applications server may subsequently transfer data between the second database and applications software for processing.

S2 SThe data collector may operate to retrieve data from the database at set 00 time intervals, such as once a minute.

As a result of actioning incoming messages an outgoing message is usually produced, marked up in XML format and written to the repairer's mailbox so N 5 that the repairer can receive it. In contrast to incoming messages the outgoing messages are generally not zipped.

Monitoring software may run continuously to check for errors in the 0validation done by the data collector. The monitoring software may display any error and probable resolution. It has a connected knowledge base that is dynamic and allowed to grow and record resolutions to new errors as they are detected for the first time.

The monitoring software may also raise an alarm when a record has not been collected within a specified time, for example ten minutes, so that support staff may intervene.

The monitor software program is scalable to include the use of technologies such as paging, short message service (SMS), wireless application protocol (WAP) or auto e-mail.

The system can effectively and efficiently communicate quote information because it takes advantage of an efficiently built database structure, intelligent message transportation and a high level of quality control of the data through error checking and correction procedures.

The implementation of a data exchange between repairers and assessors via the Internet allows for quicker response times, and allows for a paperless process for detailed quote information transmission. The reduction in intermediary staff between repairers and assessors also reduces the risk of human error when using this data exchange mechanism. This mechanism as envisaged would direct and manage the communication process to the most appropriate assessor, thus allowing the most optimum method in handling particular claims.

3 SFrom the repairer and customer's perspective the repair process would be 00 transparent, and the complex processes of validation, approval and other associated checking and data extraction of the repairing process would be done Stypically on the transmission of the first message by the repairer to the assessor.

IND 00 O The invention also concerns the database which has been constructed to accept all relevant information concerning the insurance claims process between repairers and assessors, and keeps the relevant information stored in tables, where a list of tables is given below: Core Tables Work RequestForQuote QuoteSummary QuoteItem QuoteSummaryRate Invoice QuoteNote ClaimVehicle System Parameter Tables Code CodeType Action ActionStatus QuoteStatus Reference Tables RepairEffectiveRate Rate Assessor Repairer Area N 4 AreaType 0 0 RepairSubCategory RepairRateGroup Software -0 5 Period (not yet implemented) 00 Cross Reference Tables AreaAssessor SAreaRepairer RepairRateGroup Security Audit Tables Principal Role Capability Resource CCISAudit (temporarily implemented) ErrorLog User Defined Information QuoteUserDefined QuoteItemUserDefined The User Defined tables contain information belonging to the sender that is guaranteed to be returned unchanged. This enables the sender to funnel multiple categories of information into a single category for the recipients benefit but allows the dissemination of this information back into the senders categories on return.

The key relationships between the core set of tables for the passing of information are: The primary key of a RequestForQuote is a foreign key of the QuoteSummary table.

00 The QuoteSummary table has a primary key of the Repairer Id, the Quote Number generated by the Repairers Quotation Software package plus a blind key to allow multiple occurrences of QuoteSummary records and the keeping of \0s quotation history.

00

O

There are multiple QuoteSummaryRate records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the 0 QuoteSummaryRate table key.

There are multiple QuoteItem records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the QuoteItem table key.

There are potentially multiple Invoice records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the Invoice table key.

The QuoteNote table has a blind primary key but contains the foreign keys of the RequestForQuote and QuoteSummary that it pertains to.

The Claim Vehicle table primary key is comprised of the Claim Number and Vehicle Registration Number which form part of the primary key of the Request for Quote.

In a further aspect the invention is a method for exchanging data between an insurer application software and a repairer, comprising the steps of: Receiving a message from the repairer, where the message is one of a closed set in the form of data wrapped in syntax to allow it to be mapped into a complex database structure which obeys relationship constraints and referential integrity, and usually being zipped.

Unpacking the received message to retrieve its data content.

C"6 9 Parsing the data content and writing it to tables organised in a 00 predetermined schema in a first database that does not have the relationship constraints and referential integrity.

Extracting the data from the first database, validating it and storing it in ND 5 tables organised in the predetermined schema in a second database that does have the relationship constraints and referential integrity.

Then, transferring data between the second database and the applications software.

Brief Description of the Drawings Examples of the invention will now be described with reference to the accompanying drawings, in which: Fig. 1 is a block diagram of the network topology and hardware.

Fig. 2 is a message flow diagram of the message transportation.

Best Modes of the Invention Referring to figure 1, the diagram can be divided into three distinct groups. The first group is the repairers 10, the second is the Internet 11 and the third is the assessor's system ("the system") 12.

The system 12 is protected by a firewall 20 and comprises of three servers.

The Websuite server 21 is connected to the Gentran server 22 that in turn is connected to the Forte App server 23. The system 12 uses two Oracle databases, the Gentran database 24 that is connected to the Gentran server 22 and the ORM/Forte database 25 that is connected to the Forte applications server 23.

The Assessing Centre 26 connects to the Forte App server 23 to serve data out to individual assessors 27.

Messages 28 are transmitted between the repairers 10 and the system 12 via the Internet 11 in the form of data wrapped in XML syntax and zipped using 7 a known protocol such as HTTP or FTP. These messages pass through the 00 firewall 20 to and from the websuite server 21. The XML, once it has been unpacked is passed 14 to the Gentran server 22. The XML is parsed and sent through ODBC to the Gentran oracle database 24. The Forte applications server 00 5 23 runs a program called Forte Collector to transfer data 16 between the Gentran database 24 and the ORM/Forte database Turning now to Fig. 2, the flow of messages will be described in more detail. In this example the system 12 belongs to an insurer and communicates over the Internet 11 with repairers 10. The repairer 10 prepares a message 13 and transmits it to a mailbox 30 on the websuite server 21.

The message 13 comprises Data fields wrapped into an XML syntax which allows the specification of data type, length, valid values, grouping, nesting of elements of data and the assignment of mandatory/optional attributes. Because the reference data sets may be different at the repairer and insurers computer systems, the data is denormalised into specific tags or a series of valid values are specified for a given element. As a result the apparently flat messages include intelligence to allow them to be mapped into a complex database structure which obeys relationship constraints and referential integrity.

The Gentran server 22 duplicates the message and sends the duplicate to an application inbox where it is enacted upon by a delivery rule. The delivery rule assumes that the message is a zip file and initiates an Unzipper software 31 program to load, passing the name of the zip file as a parameter.

The unzipper software program ensures validity of the zip file, and that the XML file extracted from the zip file is valid and parses correctly. The unzipper is not responsible for validation of the data types or lengths of fields.

However, the unzipper extracts any images 33 that may be in the zip file.

Provided the XML file unzips correctly it is checked, by checking a message log 35, to verify that it is not a duplicate of an earlier message. Provided it is not a duplicate, a message is written to the message log 35 detailing that the

S

8 Smessage has been received. The XML file is then taken 14 by the Gentran server 00 and parsed 32.

If the zip file contains any images 33, they are written to a single directory on the local DASD 36. The images are subsequently moved 37 from the NT 00 5 DASD to a tower Unix server 38 where they are stored with index files.

Images abide by set naming conventions. The naming conventions could be: SRepairerId.QuoteNo.MsgNo.PicNo.jpg or RepairerId.QuoteNo.MsgNo.PicNo.idx In the event there is a problem with an incoming message, the unzipper will write a message 39 to an error table 40. An entry in the error table 40 causes an outbound XML error message to be prepared and placed in the outbound mailbox of the relevant repairer for transmission 41 back to the repairer. It is the repairer's responsibility to rectify any error.

If there is a validation problem during parsing the Gentran system writes an error message to one of its internal tables. The problem is then either actioned automatically or user intervention is sought.

After parsing is complete the Gentran system attempts to write the data into a set of tables 34 that are organised in a predetermined schema. These are tables in a Gentran Oracle database which has no relationship constraints or referential integrity. As a result all messages that will parse are stored.

The assessing centre 26 retrieves data from the Gentran system using collector software 43. The collector 43 extracts the data from each of the incoming message tables, validates it and creates new records 44 within the ORME/Forte database 25. This database includes relationship constraints and referential integrity so the validation includes examination for error conditions including referential integrity. The collector software 43 runs at a set time interval, currently once a minute.

9 SThe ORM Forte server 23 uses applications software 45 to action the 00 incoming messages, either automatically or using the assessing centre 26. As a result of actioning incoming messages an outgoing message 46 is usually produced, marked up in XML format and written to the repairer's mailbox 30 so

\O

00 5 that the repairer can receive it 47. These outgoing messages are not zipped.

A monitor software program 42 (which could be located on either a client machine or server depending on the support structure of the business group, supporting this system) is run continuously to check the errors arising in the validation being performed by the collector software 43. The program 42 also displays any system error and probable resolution. It has a connected knowledge base that is dynamic and allowed to grow and records/catalogues methods of resolutions to new errors detected.

Another function of the monitor software program 42 is to verify that the collector 43 is running at all times. The monitoring software 42 will raise an alarm when a record has not been collected within a specified time, for example ten minutes, so that support staff may intervene. The method by which the monitor software program's alarm is raised is through the comparison of the time stamp in the message log 35 to the time stamp in the Gentran tables 34 held by the Gentran database 24. If the difference between the two times is greater than a specified period, say 10 minutes, the alarm is raised.

The monitor software program 42 is scalable to include the use of technologies such as paging, short message service (SMS), wireless application protocol (WAP) or auto e-email.

In a typical scenario, the insurer transmits a "Request for Quote" message to the repairer 10 with insurance information on the property, the property details for example, make, model, engine number, and the customer's contact details. After receipt of a "Request for Quote" message, the repairers 10 in response, transmit their "Quotation" message containing an extensible mark-up [language (XML) file and images, zipped into a single file via the Internet 11 to 00 reach the system 12. Message communication is done through the Internet using a known protocol such as HTTP or FTP.

In particular, an incoming message from a repairer 10, could be one of the 00 5 following:

O

0 Job declined Quotation (original variation)

O

0 Total Loss Request Tax Invoice Adjustment Note Re-activate Invoice Tables used in the VCP database: ClaimVehicle QuoteImage Bonus Invoice RequestForQuote QuoteNote QuoteSummaryRate QuoteUserDefined QuoteStatus QuoteSummary Software Action Work Diary RepairRateGroup Repairer RepairSubcategory RepairEffectiveRate QuoteItemUserDefined QuoteItem Rate AreaType Area AreaRepairer AreaAssessor Assessor CCISAudit ErrorLog Period

ID

Code CodeType Resource Capability Principal Role Listed on the following pages, in order in which they are listed here, is the detail for each of the Tables: CREATE TABLE TBVCP_ACTION CDE_REPAIRMODELTYPE VARCHAR2 (20) NOT NULL, CDE_REPAIRMODELCODE VARCHAR2 (20) NOT NULL, CDEQUOTESTATUS VARCHAR2 (20) NOT NULL, CDEACTIONTYPE VARCHAR2 (20) NOT NULL, CDE_ACTIONCODE VARCHAR2 (20) NOT NULL, 00 PRIMARY KEY( CDE_REPAIRMODELTYPE, CDEREPAIRMODELCODE, CDEQUOTESTATUS, C CDE_ACTIONTYPE, CDE_ACTIONCODE) CREATE TABLE TB_VCP_ACTIONSTATUS SCDEACTIONTYPE VARCHAR2 (20) NOT NULL, 00 CDEACTIONCODE VARCHAR2 (20) NOT NULL, CDE_QUOTESTATUS VARCHAR2 (20) NOT NULL, CDERESULTINGQUOTESTATUS VARCHAR2 PRIMARY KEY (CDE_ACTIONTYPE, CDE_ACTIONCODE, CDE_QUOTESTATUS) (N CREATE TABLE TB_VCP_AREA( CDEAREATYPE VARCHAR2 (10) NOT NULL, CDE_AREA VARCHAR2 (20) NOT NULL, CDEPARENTAREATYPE VARCHAR2 CDE_PARENTAREA VARCHAR2 TXTDESCRIPTION VARCHAR2 (30) NOT NULL, PRIMARY KEY (CDE_AREATYPE, CDE_AREA) CREATE TABLE TB_VCPAREAASSESSOR CDEAREATYPE VARCHAR2 (10) NOT NULL, CDE_AREA VARCHAR2 (20) NOT NULL, CDE_ASSESSOR VARCHAR2 (20) NOT NULL, PRIMARY KEY( CDE_AREATYPE, CDE_AREA, CDE_ASSESSOR) CREATE TABLE TB_VCP_AREAREPAIRER CDE_AREATYPE VARCHAR2 (10) NOT NULL, CDE_AREA VARCHAR2 (20) NOT NULL, CDE_REPAIRER VARCHAR2 (20) NOT NULL, PRIMARY KEY (CDE_AREATYPE, CDE_AREA, CDE_REPAIRER) CREATE TABLE TBVCP_AREATYPE( CDE_AREATYPE VARCHAR2 (10) NOT NULL, CDE_PARENTAREATYPE VARCHAR2 TXTDESCRIPTION VARCHAR2 (30) NOT NULL, PRIMARY KEY (CDE_AREATYPE) CREATE TABLE TB_VCP_ASSESSOR( CDE_ASSESSOR VARCHAR2 (20) NOT NULL, NAM_FIRSTNAME VARCHAR2 (30) NOT NULL, NAMSURNAME VARCHAR2 (30) NOT NULL, X 13 TXTPHONE VARCHAR2 TXTFAX VARCHAR2 00 TXT EMAIL VARCHAR2 SADRSTREET I VARCHAR2 ADRSTREET2 VARCHAR2 ADRSTREET3 VARCHAR2 ADRSUBURB VARCHAR2 0 ADR STATE VARCHAR2 00 ADR POSTCODE VARCHAR2 TXT MOBILE VARCHAR2 PRIMARY KEY( CDE_ASSESSOR) CREATE TABLE TB_VCP_BONUS( c 15 CDEREPAIRER VARCHAR2 (20) NOT NULL, TXTQUOTENO VARCHAR2 (20) NOT NULL, NUM_SEQ NUMBER (38) NOT NULL, CDECLAIM VARCHAR2 (20) NOT NULL, CDEREGO VARCHAR2 (10) NOT NULL, NUM_REINSTATEMENTSEQ NUMBER (38) NOT NULL, CDE_SETTLEMENTTYPE VARCHAR2 (20) NOT NULL, CDEREFERUNDERWRITING VARCHAR2 CDE_REGOSTATE VARCHAR2 CDE_WRITEOFFREPAIR VARCHAR2 AMT_PRIORVALUE NUMBER (10,2) NOT NULL, AMT_SALVAGEVALUE NUMBER (10,2) NOT NULL, AMT_TOWING NUMBER (10,2) NOT NULL, CDE_SALVAGECENTRE VARCHAR2 CDETOWINGPROVIDER VARCHAR2 DTECUSTOMERNOTIFIED DATE, AMT_REMOVEREPLACE NUMBER (10,2) NOT NULL, AMTREPAIR NUMBER (10,2) NOT NULL, AMT_PAINT NUMBER (10,2) NOT NULL, AMTPARTS NUMBER (10,2) NOT NULL, AMT_SUNDRYMISC NUMBER (10,2) NOT NULL, PRIMARY KEY (CDE_REPAIRER, TXTQUOTENO, NUM_SEQ) CREATE TABLE TB_VCP_CAPABILITY NAM_PRINCIPAL VARCHAR2 (30) NOT NULL, NAM_RESOURCE VARCHAR2 (30) NOT NULL, CDE_PRIVS VARCHAR2 (30) NOT NULL, PRIMARY KEY (NAM_PRINCIPAL, NAM_RESOURCE) CREATE TABLE TB_VCP_CCISAUDIT( NUM_CCISAUDITID NUMBER (38) NOT NULL, TXT_VERSION VARCHAR2 CDE_REPAIRER VARCHAR2 (20) NOT NULL, NAM_REPAIRERNAME VARCHAR2 CDE_CLAIMNUMBER VARCHAR2 (20) NOT NULL, 14 DT NIENDT ACHR 2) TEINCIDENTDTE VARCHAR2 00 TXT_-INCIDENTD)ESCRIPTION LONG, CI FLGVEHICLETOWERINDICATOR CHAR TXTDAMAGEDAREA VARCHAR2 AMTSUMINSURED VARCHAR2 AMTINSURANCEEXCESS VARCHAR2 AMTOWNERSCONTRIBUTION VARCHAR2 00 DTE QUOTEDATE VARCHAR2 CDEREGO VARCHAR2 CDE-yIN VARCHAR2 TXTVEHICLEDETAILS VARCHAR2 (200), NAMCUSTOMERNAME VARCHAR2 (200), TXTCUSTOMERHOMEPHONE VARCHAR2 CI 15 TXTCUSTOMERBUS[NESSPHONE VARCHAR2 FLGAUTHORISEDINDICATOR CHAR CDEJOBCATEGORY VARCHAR2 FLGHIRECARINDICATOR CHAR PRIMARY KEY NUM_CCISAUDITID) CREATE TABLE TBVCP_-CLAIMVEHICLE( CDECLAIM VARCHAR2 (20) NOT NULL, CDEREGO VARCHAR2 (10) NOT NULL, CDEVIN VARCHAR2 (30) NOT NULL, CDEBODYSTYLE VARCHAR2 TXTSERIES VARCHAR2 DTEBUILD DATE, DTEMANSTART DATE, DIEMANEND DATE, CDEMANPAINTGROUP VARCHAR2 CDEMANTRIM VARCHAR2 CDEOPTION VARCHAR2 CDEPAINT VARCHAR2 TXTENGINENO VARCHAR2 (30) NOT NULL, AMTODOMETER NUMBER (38), NUMENGINESIZE NUMBER NUMCYLINDERS NUMBER (38), TXTCOLOUR VARCHAR2 CDEMAKE VARCHAR2 (20) NOT NULL, TXT-EQUIPMENTLEVEL VARCHAR2 CDETRANSMISSIONTYPE VARCHAR2 (20) NOT NULL, CDETRANSMISSIONCODE VARCHAR.2 (20) NOT NULL, CDEMODEL VARCHAR2 TXTDESCRIPTION VARCHAR2 CDEODOMETERTYPE VARCHAR2 CDEODOMETERCODE VARCHAR2 PRIMARY KEY CDECLAIM, CDEREGO) CREATE TABLE TBVCPCODE( CDECODETYPE VARCHAR2 (20) NOT NULL, 00 CDECODE VARCHAR2 (20) NOT NULL, CI TXTDESCRIPTION VARCHAR2 (30) NOT NULL, TXTSHORTDESCRIPTION VARCHAR2 (15) NOT NULL, NUMSORTCODE NUMBER (38), PRIMARY KEY( CDECODETYPE, CDECODE) 00 CREATE TABLE TBVCPDIARY NUMDIARY NUMBER (38) NOT NULL, CDEWORK NUMBER (38), CDEASSESSOR VARCHAR2 C Is CDEREPAIRER VARCHAR2 TXTQUOTENO VARCHAR2 NUMQUOTESUMMARYSEQ NUMBER (38), DTECREATED DATE NOT NULL, DTEACTION DATE, CDEREASONTYPE VARCHAR2 CDEREASONCODE VARCHAR2 TXTDOCUMENT LONG, PRIMARY KEY (NUMDIARY) CREATE TABLE TBVCPERRORLOG CDEUSERID VARCHAR2 (100) NOT NULL, TXTAPPLICATIONID VARCHAR2 (100) NOT NULL, TXTPKTIME VARCHAR2 (21) NOT NULL, TXTREPAIRERABN VARCHAR2 (4000), TXTINSURANCECLAIMNO VARCHAR2 (4000), TXTREGISTRATIONNO VARCHAR2 (4000), NUMREINSTATEMENTNO VARCHAR2 (4000), TXTQUOTENO VARCHAR2 (4000), NUMRELATESTOMESSAGENO NUMBER TXTREPAIRERID VARCHAR2 (4000), NAMREPAIRERNAME VARCHAR2 (4000), TXTSEVERITY VARCHAR2 (4000), TXTERRORTYPE VARCHAR2 (4000), TXTVALIDITYDESCRIPTION VARCHAR2 (4000), TXTVALIDITYLINENO VARCHAR2 (4000), TXTVALIDITYCOLUMNNO VARCHAR2 (4000), TXTDATATYPEDESCRIPTION VARCHAR2 (4000), TXTDATATYPEELEMENTNAME VARCHAR2 (4000), TXTDATATYPEVALUE VARCHAR2 (4000), TXTDATACONTENTDESCRIPTION VARCHAR2 (4000), TXTDATACONTENTELEMENTNAME VARCHAR2 (4000), TXTDATACONTENTVALUE VARCHAR2 (4000), TXTMESSAGEORDERDESCRIPTION VARCHAR2 (4000), NUMMESSAGEORDEREXPECTEDNO NUMBER NUMMESSAGEORDERRECEIVEDNO NUMBER 16 TXTMESSAGETYPEDESCRIPTION VARCHAR2 (4000), TXTDTDVERSIONDESCRIPTION VARCHAR2 (4000), 00 TXTDTDVERSIONRECEIVEDVER VARCHAR2 (4000), TXTMISCELLANEOUSDESCRIPTION VARCHAR2 (4000), TXTDTDVERSION VARCHAR2 (4000), TXTSOURCEABN VARCHAR2 (4000), TXTSOURCEORGNAME VARCHAR2 (4000), TXTDESTABN VARCHAR2 (4000), 00 TXTDESTORGNAME VARCHAR2 (4000), TXTAPPVERSION VARCHAR2 (4000), TXTAPPNAME VARCHAR2 (4000), TXTTIMESTAMP VARCHAR2 (4000), FLGPROCESSINBOUND CHAR TXTTIMESTAMP N INBOUND VARCHAR2 (4000), cI 15 TXTTIMESTAMPYINBOUND VARCHAR2 (4000), FLGPROCESSOUTBOUND CHAR TXTTIMESTAMPNOUTBOUND VARCHAR2 (4000), TXTTIMESTAMPYOUTBOUND VARCHAR2 (4000)) CREATE TABLE TB_VCPID CDEID VARCHAR2 (10) NOT NULL, NAMTABLE VARCHAR2 (30) NOT NULL, NAMCOLUMN VARCHAR2 (30) NOT NULL, PRIMARY KEY (CDE ID) CREATE TABLE TBVCPINVOICE( CDEREPAIRER VARCHAR2 (20) NOT NULL, TXTQUOTENO VARCHAR2 (20) NOT NULL, NUM_SEQ NUMBER (38) NOT NULL, NUMINVOICESEQ NUMBER (38) NOT NULL, TXTINVOICENO VARCHAR2 (20) NOT NULL, DTEINVOICE DATE NOT NULL, DTECOMPLETION DATE NOT NULL, TXTABN VARCHAR2 (20) NOT NULL, TXTDESCRIPTION VARCHAR2 (100) NOT NULL, AMTGSTRATE NUMBER (10,2) NOT NULL, AMTINVOICE NUMBER(10,2) NOT NULL, CDEINVOICESTATUSTYPE VARCHAR2 CDEINVOICESTATUSCODE VARCHAR2 DTETRANSACTIONDATE DATE, TXTTAXLITERAL VARCHAR2 NUMTOTALEXCLUDINGGST NUMBER(I0,2), NUMTOTALGSTAMT NUMBER (10,2), NUMTOTALINCLUDINGGST NUMBER (10,2), NUMEXCESSTOCOLLECT NUMBER (10,2), NUMOWNERSCONTRIBUTION NUMBER (10,2), NUMTOTALPAYABLEAMT NUMBER (10,2), NUMSUPPLYDIFFAMT NUMBER (10,2), PRIMARY KEY( CDEREPAIRER, TXT_QUOTENO, NUMSEQ, NUM_INVOICESEQ) 00 CREATE TABLE TB_VCP_PERIOD CDE_PERIOD VARCHAR2 (20) NOT NULL, TXT DESCRIPTION VARCHAR2 (30) NOT NULL, NUM DAYS NUMBER (38), 00 AMTHOURS NUMBER (10,2), 00 PRIMARY KEY( CDE_PERIOD) CREATE TABLE TBVCP_PRINCIPAL( NAM PRINCIPAL VARCHAR2 (30) NOT NULL, c 15 CDETYPE VARCHAR2 (30) NOT NULL, TXTPASSWORD VARCHAR2 (30) NOT NULL, NAM_BONUSID VARCHAR2 TXTBONUSPASSWORD VARCHAR2 NAM_TOWERID VARCHAR2 TXTTOWERPASSWORD VARCHAR2 PRIMARY KEY( NAM_PRINCIPAL) CREATE TABLE TB_VCP_QUOTEIMAGE( CDE_CLAIM VARCHAR2 (20) NOT NULL, CDEREGO VARCHAR2 (10) NOT NULL, CDEREPAIRER VARCHAR2 (20) NOT NULL, NUM_REINSTATEMENTSEQ NUMBER (38) NOT NULL, CDE_IFN NUMBER (38) NOT NULL, PRIMARY KEY (CDE_CLAIM, CDE_REGO, CDEREPAIRER, NUM_REINSTATEMENTSEQ, CDE IFN) CREATE TABLE TB_VCP_QUOTEIMPACT CDE_CLAIM VARCHAR2 (20) NOT NULL, CDE_REGO VARCHAR2 (10) NOT NULL, CDE_REPAIRER VARCHAR2 (20) NOT NULL, NUM_REINSTATEMENTSEQ NUMBER (38) NOT NULL, CDE_IMPACTAREATYPE VARCHAR2 (20) NOT NULL, CDE_IMPACTAREACODE VARCHAR2 (20) NOT NULL, CDEIMPACTSEVERITYTYPE VARCHAR2 (20) NOT NULL, CDE_IMPACTSEVERITYCODE VARCHAR2 (20) NOT NULL, PRIMARY KEY( CDE_CLAIM, CDE_REGO, CDEREPAIRER, NUMREINSTATEMENTSEQ, CDEIMPACTAREATYPE, CDE_IMPACTAREACODE) CREATE TABLE TB_VCP_QUOTEITEM CDE_REPAIRER VARCHAR2 (20) NOT NULL, TXT_QUOTENO VARCHAR2 (20) NOT NULL, SNUM_SEQ NUMBER (38) NOT NULL, NUM_ITEMSEQ NUMBER (38) NOT NULL, 00 CDE_PARTSOURCE VARCHAR2 (11), N CDE_PARTORIENTATION VARCHAR2 AMT_VALUE NUMBER (10,2), CDE_ITEM VARCHAR2 TXTITEMDESCRIPTION VARCHAR2 0 FLGSUBLET CHAR(I), 00 TXT SUBLETABN VARCHAR2 S 10 AMTHOURS NUMBER (10,2), AMTOVERRIDEHOURLYRATE NUMBER CDE_PARTNUMBER VARCHAR2 NUM_INVOICE NUMBER (38), AMT_PARTQUANTITY NUMBER (10,2), C 15 CDEREPAIRCATEGORYTYPE VARCHAR2 (20) NOT NULL, CDEREPAIRCATEGORYCODE VARCHAR2 (20) NOT NULL, CDEREPAIRSUBCATEGORY VARCHAR2 (20) NOT NULL, CDEQUOTEITEMSTATUSTYPE VARCHAR2 (20) NOT NULL, CDE_QUOTEITEMSTATUSCODE VARCHAR2 (20) NOT NULL, CDE_OCCURANCEORIGINTYPE VARCHAR2 (20) NOT NULL, CDE_OCCURANCEORIGINCODE VARCHAR2 (20) NOT NULL, TXTCOMMENT VARCHAR2 (100), CDE_REASONTYPE VARCHAR2 CDEREASONCODE VARCHAR2 CDE_ASSESSOR VARCHAR2 DTE_TRANSACTIONTIMESTAMP DATE, AMTUNITVALUE NUMBER(10,2), NUM_CYCLE NUMBER (38), FLG_REPORTON CHAR CDE_PARTSOURCETYPE VARCHAR2 CDE_PARTORIENTATIONTYPE VARCHAR2 PRIMARY KEY (CDE_REPAIRER, TXTQUOTENO, NUM_SEQ, NUM_ITEMSEQ) CREATE TABLE TB_VCP_QUOTEITEMUSERDEFINED CDE_REPAIRER VARCHAR2 (20) NOT NULL, TXTQUOTENO VARCHAR2 (20) NOT NULL, NUM_SEQ NUMBER (38) NOT NULL, NUM_ITEMSEQ NUMBER (38) NOT NULL, TXTDEFINEATTRIBUTE VARCHAR2 (40) NOT NULL, TXT DEFINEVALUE VARCHAR2 (40) NOT NULL, NUM_MESSAGENO NUMBER (38), CDE_CLAIM VARCHAR2 PRIMARY KEY( CDE_REPAIRER, TXTQUOTENO, NUM_SEQ, NUMITEMSEQ, TXTDEFINEATTRIBUTE, TXT_DEFINEVALUE) CREATE TABLE TB_VCPQUOTENOTE( NUM_QUOTENOTE NUMBER (38) NOT NULL, NAM_AUTHOR VARCHAR2 (30) NOT NULL, C 19 CDE CLAIM VARCHAR2 (20) NOT NULL, SCDEREGO VARCHAR2 (10) NOT NULL, 00 CDEREPAIRER VARCHAR2 (20) NOT NULL, NUMREINSTATEMENTSEQ NUMBER (38) NOT NULL, DTECREATED DATE, TXT DOCUMENT LONG NOT NULL, CDENOTETYPE VARCHAR2 NOTECODE VARCHAR2 00 TXTQUOTENO VARCHAR2 NUMSEQ NUMBER (38), PRIMARY KEY (NUM_QUOTENOTE) CREATE TABLE TBVCP_QUOTESTATUS CDEQUOTESTATUS VARCHAR2 (20) NOT NULL, CDEDEFAULTPRIORITYTYPE VARCHAR2 (20) NOT NULL, CDEDEFAULTPRIORITYCODE VARCHAR2 (20) NOT NULL, TXTDESCRIPTION VARCHAR2 (30) NOT NULL, CDEMODETYPE VARCHAR2 CDEMODECODE VARCHAR2 PRIMARY KEY( CDEQUOTESTATUS) CREATE TABLE TB_VCP_QUOTESUMMARY CDEREPAIRER VARCHAR2 (20) NOT NULL, TXTQUOTENO VARCHAR2 (20) NOT NULL, NUMSEQ NUMBER (38) NOT NULL, CDECLAIM VARCHAR2 (20) NOT NULL, CDE_REGO VARCHAR2 (10) NOT NULL, NUMREINSTATEMENTSEQ NUMBER (38) NOT NULL, CDEALLOCATEDASSESSOR VARCHAR2 (20) NOT NULL, DTEQUOTATION DATE, NUMMESSAGE NUMBER (38), CDEQUALITYSURVEYTYPE VARCHAR2 CDEQUALITYSURVEYCODE VARCHAR2 CDECUSTOMERSURVEYTYPE VARCHAR2 CDECUSTOMERSURVEYCODE VARCHAR2 CDECOSTCOMPARISONTYPE VARCHAR2 CDE_COSTCOMPARISONCODE VARCHAR2 DTE START DATE, DTE END DATE, CDE_REPAIRMODELTYPE VARCHAR2 (20) NOT NULL, CDEREPAIRMODELCODE VARCHAR2 (20) NOT NULL, CDE_QUOTETYPE VARCHAR2 (20) NOT NULL, CDEQUOTECODE VARCHAR2 (20) NOT NULL, CDEQUOTESTATUS VARCHAR2 AMTOWNERCOLLECTCONTRIBUTION NUMBER (10,2), TXTUSERDEFINED LONG, CDESOFTWARE VARCHAR.2(10) NOT NULL, TXTSOFTWAREVERSION VARCHAR2 (10) NOT NULL, 00 CDEOCCURANCEORIG[NTYPE VARCHAR2 CDEOCCURANCEORIGINCODE VARCHAR2 TXTREPAIRERCONTACT VARCHAR2 CDEEVALUATIONTYPE VARCHAR2 CDEEVALUATIONCODE VARCHAR2 VARCHAR2 00 CDEPROGRESSCODE VARCHAR2 NUMCYCLE NUMBER (3 8), CDEVEHICLELOCATIONTYPE VARCHAR2 CDEVEHICLELOCATIONCODE VARCHAR2 CDEASSESSOR VARCHAR2 DTETRANSACTIONTIMESTAMP

DATE,

(N PRIMARY KEY (CDE_REPAIRER, TXT_QUOTENO, NUMSEQ) CREATE TABLE TB_VCPQUOTESUMMARYRATE CDEREPAIRER VARCHAR2 (20) NOT NULL, TXTQUOTENO VARCHAR2 (20) NOT NULL, NUM_SEQ NUMBER (38) NOT NULL, CDEREPAIRCATEGORYTYPE VARCHAR2 (20) NOT NULL, CDEREPAIRCATEGORYCODE VARCHAR2 (20) NOT NULL, CDEREPAIRSUBCATEGORY VARCHAR2 (20) NOT NULL, AMT_-OURLYRATE NUMBER CDEOCCURANCEORIGINTYPE VARCHAR2 CDEOCCURRANCEORIGINCODE VARCHAR2 CDE-QUOTERATESTATUSTYPE VARCHAR2 CDEQUOTERATESTATUSCODE VARCHAR2 PRIMARY KEY CDEREPAIRER, TXT_QUOTENO, NUMSEQ, CDE_REPAIRCATEGORYTYPE, CDEREPAIRCATEGORYCODE, CDEREPAIRSUBCATEGORY) CREATE TABLE TB_VCPQUOTEUSERDEFINED CDEREPAIRER VARCHAR2 (20) NOT NULL, TXTQUOTENO VARCHAR2 (20) NOT NULL, NUM_SEQ NUMBER (38) NOT NULL, TXTDEFINEATTRIBUTE VARCHAR2 (40) NOT NULL, TXTDEFINEVALUE VARCHAR2 (40) NOT NULL, NUMMESSAGENO NUMBER (38), PRIMARY KEY (CDEREPAIRER, TXT QUOTENO, NUM_SEQ, TXTDEFINEATTRIBUTE,

TXTDEFINEVALUE)

CREATE TABLE TBVCPRATE( CDEAREATYPE VARCHAR2 (10) NOT NULL, so CDEAREA VARCHAR2 (20) NOT NULL, CDERATETYPE VARCHAR2 (20) NOT NULL, CDERATECODE VARCHAR2 (20) NOT NULL, 21 DTEEFFECTIVEDATE DATE NOT NULL, AMTRATE NUMBER (12,4) NOT NULL, 00 PRIMARY KEY (CDEAREATYPE, CDE_AREA, CDE_RATETYPE, CDERATECODE,

DTEEFFECTIVEDATE)

00 22 CREATE TABLE TBVCPREPAIREFFECTIVERATE CDERATEGROIJPTYPE VARCHAR2 (20) NOT NULL, 00 CDERATEGROUPCODE VARCHAR2 (20) NOT NULL, CDEREPAIRCATEGORYTYPE VARCHAR2 (20) NOT NULL, CDEREPAIRCATEGORYCODE VARCHAR2 (20) NOT NULL, CDEREPAIRSUBCATEGORY VARCHAR2 (20) NOT NULL, DTESTART DATE NOT NULL, AMTHOURLYRATE NUMBER NOT NULL, 00 PRIMARY KEY( CDERATEGROUPTYPE, CDERATEGROUPCODE, CDEREPAIRCATEGORYTYPE, CDEREPAIRCATEGORYCODE, CDEREPAIRSUBCATEGORY,

DTESTART)

CI 15 CREATE TABLE TBVCPREPAIRER( CDEREPAIRER VARCHAR2 (20) NOT NULL, TXTABN VARCHAR2 TXTURL VARCHAR2 TXTEMAIL VARCHAR2 (60) NOT NULL, CDEDEFAULTASSESSOR VARCHAR2 CDEPAYMENTMETHODTYPE VARCHAR2 (20) NOT NULL, CDEPAYMENTMETHODCODE VARCHAR2 (20) NOT NULL, CDECOMMSMETHODTYPE VARCHAR2 (20) NOT NULL, CDECOMMSMETHODCODE VARCHAR2 (20) NOT NULL, TXTREPAIRERNAME VARCHAR2 (66), CDEBONUSREPAIRER VARCHAR2 PRIMARY KEY( CDEREPAIRER) CREATE TABLE TBVCPREPAIRERRATEGROUP CDECOMPANYID VARCHAR2 (30) NOT NULL, CDERATEGROUPTYPE VARCHAR2 (20) NOT NULL, CDERATEGROUPCODE VARCHAR2 (20) NOT NULL, CDEREPAIRER VARCHAR2 (20) NOT NULL, TXTDESCRIPTION VARCHAR2 (30) NOT NULL.

PRIMARY KEY( CDECOMPANYID, CDERATEGROUPTYPE, CDERATEGROUPCODE,

CDEREPAIRER)

23 CREATE TABLE TBVCPREPAIRSUBCATEGORY CDE_REPAIRCATEGORYTYPE VARCHAR2 (20) NOT NULL, 00 CDE_REPAIRCATEGORYCODE VARCHAR2 (20) NOT NULL, N CDE_REPAIRSUBCATEGORY VARCHAR2 (20) NOT NULL, TXT_DESCRIPTION VARCHAR2 (30) NOT NULL, NUMSORTCODE NUMBER (38), TXTSHORTDESCRIPTION VARCHAR2 00 PRIMARY KEY (CDEREPAIRCATEGORYTYPE, CDEREPAIRCATEGORYCODE,

CDEREPAIRSUBCATEGORY)

CREATE TABLE TB_VCP_REQUESTFORQUOTE CDE_CLAIM VARCHAR2 (20) NOT NULL, c 15 CDE_REGO VARCHAR2 (10) NOT NULL, CDE_REPAIRER VARCHAR2 (20) NOT NULL, NUM_REINSTATEMENTSEQ NUMBER (38) NOT NULL, CDEQUOTETYPE VARCHAR2 (20) NOT NULL, CDE_QUOTECODE VARCHAR2 (20) NOT NULL, CDE_REPAIRMODELTYPE VARCHAR2 (20) NOT NULL, CDE_REPAIRMODELCODE VARCHAR2 (20) NOT NULL, CDE_COMPLETIONREINSPECTIONTYPE VARCHAR2 CDECOMPLETIONREINSPECTIONCODE VARCHAR2 FLG_TOW CHAR NOT NULL, FLGHIRECAR CHAR NOT NULL, AMTINSURANCEEXCESS NUMBER (10,2), NUM_MESSAGE NUMBER (38), DTEREQUESTTIME DATE, DTE_QUOTEEXPECTED DATE, CDEQUOTESTATUS VARCHAR2 TXTCONTRACTTEXT VARCHAR2 (1000), PRIMARY KEY (CDE_CLAIM, CDE_REGO, CDE_REPAIRER, NUMREINSTATEMENTSEQ) CREATE TABLE TB_VCPRESOURCE( TXT_DESCRIPTION VARCHAR2 (60) NOT NULL, NAM_RESOURCE VARCHAR2 (30) NOT NULL, PRIMARY KEY (NAM_RESOURCE) CREATE TABLE TB_VCPROLE( CDEROLENAME VARCHAR2 (30) NOT NULL, NAM_PRINCIPAL VARCHAR2 (30) NOT NULL, PRIMARY KEY (CDE_ROLENAME, NAM_PRINCIPAL) CREATE TABLE TBVCPSOFTWARE CDE_SOFTWARE VARCHAR2 (10) NOT NULL, TXT_VERSION VARCHAR2 (10) NOT NULL, CDECERTIFICATIONTYPE VARCHAR2 S24 CDECERTIFICATIONCODE VARCHAR2 TXTDOCUMENT LONG NOT NULL, 00 DTE CERTIFICATIONDATE DATE, PRIMARY KEY CDE_SOFTWARE, TXT_VERSION) ,0 CREATE TABLE TB VCPWORK 00 CDEWORK NUMBER (38) NOT NULL, S 10 DTE_CREATED DATE NOT NULL, SDTE_ACTIONDUE

DATE,

CDE_ALLOCATEDASSESSOR VARCHAR2 (20) NOT NULL, CDE_PRIORITYTYPE VARCHAR2 (20) NOT NULL, CDE_PRIORITYCODE VARCHAR2 (20) NOT NULL, 1 15 CDE_REPAIRAREATYPE VARCHAR2 (10) NOT NULL, CDE_REPAIRAREA VARCHAR2 (20) NOT NULL, CDE_WORKREASONTYPE VARCHAR2 CDE_WORKREASONCODE VARCHAR2 CDE_QUOTESTATUS VARCHAR2 CDE_CLAIM VARCHAR2 CDE_REGO VARCHAR2 CDE_REPAIRER VARCHAR2 NUM_REINSTATEMENTSEQ NUMBER (38), TXTQUOTENO VARCHAR2 NUM_QUOTESUMMARYSEQ NUMBER (38), CDE_PROGRESSTYPE VARCHAR2 CDE_PROGRESSCODE VARCHAR2 PRIMARY KEY (CDE_WORK) It will be appreciated by persons skilled in the art that numerous variations and/or modifications may be made to the invention as shown in the specific embodiments without departing from the spirit or scope of the invention as broadly described. The present embodiments are, therefore, to be considered in all respects as illustrative and not restrictive.

Claims

1. A computer system for exchanging data between software applications of an insurer and a repairer, comprising: 0 5 a message transceiving station, to send messages to and receive messages 00 from the repairer, where each message is one of a closed set and is in the form of data wrapped in syntax to allow it to be mapped into a database structure which obeys relationship constraints and has referential integrity; a message receiver to unpack received messages to retrieve their data content; a message data content parser to parse the data content and write it to tables organised in a predetermined schema in a first database that does not have the relationship constraints and referential integrity; a data collector to extract the data from the first database, validate it and store it in tables organised in the predetermined schema in a second database that does have the relationship constraints and referential integrity.

2. A computer system according to claim 1, further comprising an applications server to subsequently transfer data between the second database and applications software for processing.

3. A computer system according to claim 1 or 2, where the data collector operates to retrieve data from the database at predetermined time intervals.

4. A computer system according to any preceding claim, where, as a result of actioning an incoming message, an outgoing message is produced, marked up in XML format and written to the repairer's mailbox so that the repairer can receive it.

A computer system according to claim 1, where monitoring software runs continuously to check for errors in the validation done by the data collector. 26

6. A computer system according to claim 5, where the monitoring 00 software displays errors and their probable resolution.

7. A computer system according to claim 6, where the monitoring software has a knowledge base that is dynamic and allowed to grow and record N 5 resolutions to new errors as they are detected for the first time. 00 8

8. A computer system according to claim 5, where the monitoring software raises an alarm when a record has not been collected within a specified time.

9. A computer system according to claim 1, where the second database has one or more of the following tables: Core Tables Work RequestForQuote QuoteSummary QuoteItem QuoteSummaryRate Invoice QuoteNote ClaimVehicle System Parameter Tables Code CodeType Action ActionStatus QuoteStatus Reference Tables RepairEffectiveRate Rate Assessor 27 Repairer 00 Area AreaType RepairSubCategory RepairRateGroup 0 0 Software SPeriod (not yet implemented) Cross Reference Tables C N

10 AreaAssessor AreaRepairer RepairRateGroup Security Audit Tables Principal Role Capability Resource CCISAudit (temporarily implemented) ErrorLog User Defined Information QuoteUserDefined QuoteItemUserDefined A computer system according to claim 9, where the key relationships between the core set of tables for the passing of information include the primary key of a RequestForQuote being a foreign key of the QuoteSummary table.

11. A computer system according to claim 9 or 10, where the QuoteSummary table has a primary key of the Repairer Id, the Quote Number generated by the Repairers Quotation Software package plus a blind key to allow 28 multiple occurrences of QuoteSummary records and the keeping of quotation history.

12. A computer system according to claim 9, 10 or 11, where there are multiple QuoteSummaryRate records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the QuoteSummaryRate table key.

13. A computer system according to any one of claims 9 to 12, where there are multiple QuoteItem records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the QuoteItem table key.

14. A computer system according to any one of claims 9 to 13, where there are multiple Invoice records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the Invoice table key.

A computer system according to any one of claims 9 to 14, where the QuoteNote table has a blind primary key but contains the foreign keys of the RequestForQuote and QuoteSummary that it pertains to.

16. A computer system according to any one of claims 9 to 15, where the Claim Vehicle table primary key is comprised of the Claim Number and Vehicle Registration Number which form part of the primary key of the Request for Quote.

17. A method for exchanging data between an insurer software application and a repairer software application, comprising the steps of: receiving a message from the repairer, where the message is one of a closed set in the form of data wrapped in syntax to allow it to be mapped into a complex database structure which obeys relationship constraints and referential integrity; unpacking the received message to retrieve its data content; parsing the data content and writing it to tables organised in a predetermined schema in a first database that does not have the relationship constraints and referential integrity; and extracting the data from the first database, validating it and storing it in tables organised in the predetermined schema in a second database that does have the relationship constraints and referential integrity; S29

18. A method according to claim 17, further comprising the subsequent o00 transferring of data from the second database to the insurer applications software for processing.

19. A method according to claim 17 or 18, where the data collector oO 5 operates to retrieve data from the database at predetermined time intervals.

A method according to any one of claims 17, 18 or 19, comprising the further step of producing an outgoing message as a result of actioning an incoming message, the outgoing message being marked up in XML format and written to the repairer's mailbox so that the repairer can receive it.

21. A method according to claim 17, comprising the further step of running monitoring software continuously to check for errors in the validation done by the data collector.

22. A method according to claim 21, comprising the further step of the monitoring software displaying errors and their probable resolution.

23. A method according to claim 22, comprising the further step of the monitoring software having a knowledge base recording resolutions to new errors as they are detected for the first time.

24. A method according to claim 21, comprising the further step of the monitoring software raisings an alarm when a record has not been collected within a specified time.

A method according to claim 17, where the second database has one or more of the following tables: Core Tables Work RequestForQuote QuoteSummary QuoteItem QuoteSummaryRate Invoice C- SQuoteNote 00 ClaimVehicle System Parameter Tables 0 5 Code 0 CodeType Action b ActionStatus 0QuoteStatus Reference Tables RepairEffectiveRate Rate Assessor Repairer Area AreaType RepairSubCategory RepairRateGroup Software Period (not yet implemented) Cross Reference Tables AreaAssessor AreaRepairer RepairRateGroup Security Audit Tables Principal Role Capability Resource CCISAudit (temporarily implemented) iErrorLog 00 User Defined Information QuoteUserDefined QuoteItemUserDefined 00

26. A method according to claim 25, where the key relationships between the core set of tables for the passing of information include the primary Skey of a RequestForQuote being a foreign key of the QuoteSummary table.

27. A method according to claim 25 or 260, where the QuoteSummary table has a primary key of the Repairer Id, the Quote Number generated by the Repairers Quotation Software package plus a blind key to allow multiple occurrences of QuoteSummary records and the keeping of quotation history.

28. A method according to claim 25, 26 or 27, where there are multiple QuoteSummaryRate records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the QuoteSummaryRate table key.

29. A method according to any one of claims 25 to 28, where there are multiple QuoteItem records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the QuoteItem table key.

30. A mehtod according to any one of claims 25 to 28, where there are multiple Invoice records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the Invoice table key.

31. A computer system according to any one of claims 25 to 30, where the QuoteNote table has a blind primary key but contains the foreign keys of the RequestForQuote and QuoteSummary that it pertains to.

32. A computer system according to any one of claims 25 to 31, where the Claim Vehicle table primary key is comprised of the Claim Number and Vehicle Registration Number which form part of the primary key of the Request for Quote.