527509
DATA EXCHANGE BETWEEN INSURER AND REPAIRER.
Technical Field
This invention concerns a data exchange, and in particular a computer 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 IS 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 integrily. The incoming messages are usually zipped.
A message receiver to unpack received messages to retrieve their data 20 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 25 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.
2
The data collector may operate to retrieve data from the database at set time intervals, such as once a minute.
As a result of actioning incoming messages an outgoing message is visually produced, marked up in XML format and written to the repairer's mailbox so 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 validation 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.
From the repairer and customer's perspective the repair process would be transparent, and the complex processes of validation, approval and other associated checking and data extraction of the repairing process would be done typically on the transmission of the first message by the repairer to the assessor.
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 Quoteltem 15 QuoteSummaryRate
Invoice QuoteNote ClaimVehicle
System Parameter Tables
Code
CodeType Action ActionStatus 25 QuoteStatus
Reference Tables
RepairEffectiveRate Rate
Assessor
Repairer Area
WO 02/071281 PCT/AU02/00176
4
AreaType RepairSubCategoiy RepairRateGroup Software
Period (not yet implemented)
Cross Reference Tables
AreaAssessor AreaRepairer 10 RepairRateGroup
Security & Audit Tables
Principal Role
Capability
Resource
CCISAudit (temporarily implemented)
ErrorLog
User Defined Information
QuoteUserDefined QuoteltemUserDefined
The User Defined tables contain information belonging to the sender that 25 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.
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.
There are multiple QuoteSummaryRate records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the QuoteSummaryRate table key.
There are multiple Quoteltem records for each QuoteSummary and therefore the QuoteSummary primary key forms part of the Quoteltem 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.
6
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.
Extracting the data from the first database, validating it and storing it in 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. 15 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 20 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 25 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
a known protocol such as HTTP or FTP. These messages pass through the 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 15 through ODBC to the Gentran oracle database 24. The Forte applications server 5 23 runs a program called Forte Collector to transfer data 16 between the Gentran database 24 and the OKM/Forte database 25.
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 10 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 is 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 20 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 25 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
PCT/ATJ02/00176
message has been received. The XML file is then taken 14 by the Gentran server 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 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:
RepairerId.QuoteNo.MsgNo.PicNo.jpg or Repairerld.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 15 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 20 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 25 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 integrily. The collector software 43 runs at a set time interval, currently once a minute.
The OEM Forte server 23 uses applications software 45 to action the 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 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 10 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 15 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 20 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 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 5 following:
Job declined
Quotation (original & variation)
Total Loss Request Tax Invoice 10 Adjustment Note
Re-activate Invoice
Tables used in the VCP database:
ClaimVehicle Quotelmage Bonus Invoice
RequestForQuote
QuoteNote
QuoteSummaryRate
QuoteUserDefined
QuoteStatus
QuoteSummary
Software
Action
Work
Diary
11
RepairRateGroup Repairer
RepairSubcategory
RepairEffectiveRate
QuoteltemUserDefined
Quoteltem
Rate
AxeaType Area
AreaRepairer
AreaAssessor
Assessor
CGISAudit
ErrorLog
Period m
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 TB VCP ACTION (
CDE REPAJRMODELTYPE VARCHAR2 (20) NOT NULL,
CDE REPAIRMODELCODE VARCHAR2 (20) NOT NULL,
CDEjQUOTESTATUS VARCHAR2(20) NOT NULL,
CDE_ACTIONTYPE VARCHAR2 (20) NOT NULL,
12
CDE_ACTIONCODE VARCHAR2 (20) NOT NULL,
PRIMARY KEY ( CDE REPAIRMODELTYPE, CDE_REPAIRMODELCODE, CDE QUOTESTATUS, CDE_ACnONTYPE» CDE_ACTIONCODE )
CREATE TABLE TB_VCP_ACTIONSTATUS (
CDE_ACTIONTYPE VARCHAR2 (20) NOT NULL,
CDEACTIONCODE VARCHAR2(20) NOT NULL,
CDE QUOTESTATUS VARCHAR2 (20) NOT NULL, CDE_RESULHNGQUOTESTATUS VARCHAR2 (20),
PRIMARY KEY ( CDE_ACTIONTYPE, CDE ACTIONCODE, CDE_QUOTESTATUS )
CREATE TABLE TB_VCP AREA (
CDE AREATYPE VARCHAR2(10) NOT NULL, CDE_AREA VARCHAR2 (20) NOT NULL, CDEJPARENTAREATYPE VARCHAR2 (10), CDE_PARENTAREA VARCHAR2 (20), TXT_DESCREPTION VARCHAR2 (30) NOT NULL,
PRIMARY KEY ( CDE_AREATYPE, CDE_AREA )
CREATE TABLE TB_VCP_AREAASSESSOR (
CDE AREATYPE 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,
CDEJREPAIRER VARCHAR2 (20) NOT NULL,
PRIMARY KEY ( CDE_AREATYPE, CDE_AREA, CDEJREPAIRER )
CREATE TABLE TB VCP_AREATYPE (
CDE_AREATYPE VARCHAR2 (10) NOT NULL, CDE PARENTAREATYPE VARCHAR2 (10),
TXT_DESCRIPTION VARCHAR2 (30) NOT NULL,
PRIMARY KEY ( CDE_AREATYPE )
CREATE TABLE TB_VCP ASSESSOR ( CDE_ASSESSOR VARCHAR2 (20) NOT NULL, NAM_F1RSTNAME VARCHAR2 (30) NOT NULL, NAM SURNAME VARCHAR2 (30) NOT NULL,
13
TXT PHONE VARCHAR2 (20),
TXT_FAX VARCHAR2 (20),
TXTJEMAIL VARCHAR2 (60),
ADR STREET1 VARCHAR2 (60),
ADR STREET2 VARCHAR2 (60),
ADR STREET3 VARCHAR2 (60),
ADR SUBURB VARCHAR2 (30),
ADR STATE VARCHAR2 (10),
ADR POSTCODE VARCHAR2 (10),
TXT_MOBILE VARCHAR2 (20),
PRIMARY KEY ( CDE_ASSESSOR )
CREATE TABLE TB VCP BONUS (
CDE REPAIRER VARCHAR2 (20) NOT NULL, TXT_QUOTENO VARCHAR2(20) NOT NULL,
NUM_SEQ NUMBER (38) NOT NULL,
CDE CLAIM VARCHAR2 (20) NOT NULL,
CDE REGO VARCHAR2 (10) NOT NULL,
NUM REINSTATEMENTSEQ NUMBER (38) NOT NULL, CDE SETrLEMENTTYPE VARCHAR2 (20) NOT NULL, CDE REFERUNDERWRTTING VARCHAR2 (20), CDE REGOSTATE VARCHAR2 (20), CDE WRITEOFFREPAIR VARCHAR2 (20), 25 AMT PRIORVALUE NUMBER (10,2) NOT NULL, AMT_SALVAGEVALUE NUMBER (10,2) NOT NULL, AMT TOWING NUMBER (10,2) NOT NULL, CDE_SALVAGECENTRE VARCHAR2 (20), CDE_TOWINGPRO VIPER VARCHAR2 (20), 30 DTE CUSTOMERNOTEFIED DATE,
AMT REMOVEREPLACE NUMBER (10,2) NOT NULL, AMT REPAIR NUMBER (10,2) NOT NULL,
AMT PAINT NUMBER (10,2) NOT NULL,
AMT PARTS NUMBER (10,2) NOT NULL, 35 AMT_SUNDRYMISC NUMBER (10,2) NOT NULL,
PRIMARY KEY ( CDEJREPAIRER, TXT_QUOTENO, NUM_SEQ )
CREATE TABLE TB VCP_CAPABILITY ( 40 NAM PRINCIPAL VARCHAR2 (30) NOT NULL, NAMJRESOURCE VARCHAR2(30) NOT NULL,
CDE PRIVS VARCHAR2 (30) NOT NULL,
45
PRIMARY KEY ( NAMJPRINCIPAL, NAMJRESOURCE )
CREATE TABLE IB_VCP_CCISAUDIT ( NUM CCISAUDITID NUMBER (38) NOT NULL, TXT VERSION VARCHAR2 (10),
50 CDE_REPAIRER VARCHAR2 (20) NOT NULL,
NAM REPAIRERNAME VARCHAR2 (60), CDE CLAIMNUMBER VARCHAR2 (20) NOT NULL,
14
dte_incidenida'ie varchar2 (20), tmejncidenttime varchar2 (20), txt incidentdescription LONG, flg vehicletowerindicator char(l), 5 txtdamagedarea varchar2 (20), amt suminsured varchar2 (20), amtjnsuranceexcess varchar2 (20), amt ownerscontribution varchar2 (20), dtejquotedate varchar2 (20), 10 cde_rego varchar2 (10),
CDEVIN VARCHAR2 (30),
TXT_vehicledetails VARCHAR2 (200), NAM CUSTOMERNAME VARCHAR2 (200), TXT CTJSTOMERHOMEPHONE VARCHAR2 (20), 15 TXT~CUSTOMERBUSINESSPHONE VARCHAR2 (20), FLGIaUTHORISEDINDICATOR CHAR(l), CDEJOBCATEGORY VARCHAR2 (20), FLGJDRECARINDICATOR CHAR(l),
PRIMARY KEY ( NUM_CCISAXJDITID )
CREATE TABLE TB_VCP_CLAIMVEfflCLE (
CDE_CLAIM VARCHAR2 (20) NOT NULL, 25 CDEJREGO VARCHAR2(10) NOT NULL,
CDE_VIN VARCHAR2 (30) NOT NULL,
CDE BODYSTYLE VARCHAR2 (30),
TXT_SERIES VARCHAR2 (30),
DTEBUILD DATE,
DTE MANSTART DATE,
DTElMANEND DATE,
CDE MANPAINTGROUP VARCHAR2 (10), CDE MANTRIM VARCHAR2 (10),
CDE OPTION VARCHAR2 (10),
CDEJPAINT VARCHAR2 (10),
TXT ENGINENO VARCHAR2 (30). NOT NULL, AMT ODOMETER NUMBER (38),
NUM ENGINESIZE NUMBER (5,2),
NUM CYLINDERS NUMBER (38), 40 TXT COLOUR VARCHAR2 (20),
CDE MAKE VARCHAR2 (20) NOT NULL,
TXT EQUIPMENTLEVEL VARCHAR2 (10), CDEJTRANSMISSIONTYPE VARCHAR2 (20) NOT NULL, CDE TRANSMISSIONCODE VARCHAR2(20) NOT NULL, 45 CDEMODEL VARCHAR2 (20),
TXT_DESCRIPTION VARCHAR2 (30), CDE_ODOMETERTYPE VARCHAR2 (20), CDE_ODOMETERCODE VARCHAR2 (20),
50 PRIMARY KEY ( CDEJXAIM, CDE_REGO )
CREATE TABLE TB VCP_CODE (
CDE CODETYPE VARCHAR2 (20) NOT NULL, CDEICODE VARCHAR2 (20) NOT NULL, TXT DESCRIPTION VARCHAR2 (30) NOT NULL, 5 TXT SHORTDESCRIPTION YARCHAR2 (15) NOT NULL, NUM_SORTCODE NUMBER (38),
PRIMARY KEY (CDE_CODETYPE, CDE_CODE )
CREATE TABLE TB VCP_DIARY(
NUM DIARY NUMBER (38) NOT NULL,
CDEJVORK NUMBER (38),
CDE_ASSESSOR VARCHAR2 (20), 15 CDE REPAIRER VARCHAR2 (20),
TXT_QUOTENO VARCHAR2 (20), NUM_QUOTESUMMARYSEQ NUMBER (38), DTE CREATED DATE NOT NULL,
DTE_ACTION DATE,
CDEJREASONTYPB VARCHAR2 (20), CDEJREASONCODE VARCHAR2 (20),
TXT_DOCUMENT LONG,
PRIMARY KEY (NUM_DIARY )
CREATE TABLE TB VCP_ERRORLOG (
CDEJJSERID VARCHAR2 (100) NOT NULL,
TXT APPLICATION© VARCHAR2 (100) NOT NULL, 30 TXT PKTIME VARCHAR2 (21) NOT NULL,
TXTJREPAIRERABN VARCHAR2 (4000),
TXT_INSURANCECLAIMNO VARCHAR2 (4000), TXT_REGISTRATIONNO VARCHAR2 (4000), NUM REINSTATEMENTNO VARCHAR2 (4000), 35 TXT QUOTENO VARCHAR2 (4000),
NUM RELATESTOMESSAGENO NUMBER (3), TXTJREPAIRERID VARCHAR2 (4000),
NAMJREPAIRERNAME VARCHAR2 (4000),
TXT_SE VERITY VARCHAR2 (4000),
40 TXTJERRORTYPE VARCHAR2 (4000),
TXT_VALIDITYDESCRIPTION VARCHAR2 (4000), TXT VALIDITYLINENO VARCHAR2 (4000), TXT_VALIDITYCOLUMNNO VARCHAR2 (4000), TXTT_DATATYPEDESCRIPTION VARCHAR2 (4000), 45 TXT DATATYPEELEMENTNAME VARCHAR2 (4000), TXTJDATATYPEVALUE VARCHAR2 (4000), TXTJDATACONTENTDESCRIPTION VARCHAR2 (4000), TXTJDATACONTENTELEMENTNAME VARCHAR2 (4000), TXTJDATACONTENTVALUE VARCHAR2 (4000), 50 TXT_MESSAGEORDERDESCRIPTION VARCHAR2 (4000), NUM_MESSAGEORDEREXPECTEDNO NUMBER (3), NUM MESSAGEORDERRECEIVEDNO NUMBER (3),
16
TXT_MESSAGETYPEDESCRIPTION VARCHAR2 (4000), TXT DTDVERSIONDESOUPTION VARCHAR2 (4000), TXT*DTDVERSIONRECEIVEDVER VARCHAR2 (4000), TXTT_MISCELLANEOUSDESCRIPTION VARCHAR2 (4000), 5 TXT_DTDVERSION VARCHAR2 (4000),
TXT_SOURCE ABN VARCHAR2 (4000),
TXTSOURCE ORGNAME VARCHAR2 (4000), TXT DEST ABN VARCHAR2 (4000),
TXTJDEST-ORGNAME VARCHAR2 (4000), 10 TXT APPVERSION VARCHAR2 (4000),
TXT~APPNAME VARCHAR2 (4000),
TXT.HMESTAMP VARCHAR2 (4000),
FLG_PROCESS_INBOUND CHAR(l),
TXT TIME STAMP_N_INBOUND VARCHAR2 (4000), 15 TXT~HMESTAMP_Y INBOUND VARCHAR2 (4000), FLG_PROCESS OUTBOUND CHAR(l), TXTJTIMESTAMP N OUTBOUND VARCHAR2 (4000), TXT_TIMESTAMP_Y_OUTBOUND VARCHAR2 (4000))
CREATE TABLE TB VCP ID (
CDE ID VARCHAR2 (10) NOT NULL,
NAMJTABLE VARCHAR2 (30) NOT NULL, NAM_COLUMN VARCHAR2 (30) NOT NULL,
PRIMARY KEY ( CDE_ID )
CREATE TABLE TB_VCPJNVOICE ( 30 CDE REPAIRER VARCHAR2 (20) NOT NULL, TXT_QUOTENO VARCHAR2 (20) NOT NULL, NUM_SEQ NUMBER (38) NOT NULL,
NUMJNVOICESEQ NUMBER (38) NOT NULL, TXT INVOICENO VARCHAR2 (20) NOT NULL, 35 DTE~INVOICE DATE NOT NULL,
DTE COMPLETION DATE NOT NULL, TXT_ABN VARCHAR2 (20) NOT NULL,
TXT_DESCRIPTION VARCHAR2 (100) NOT NULL, AMT GSTRATE NUMBER (10,2) NOT NULL, 40 AMT INVOICE NUMBER (10,2) NOT NULL, CDE INVOICESTATUSTYPE VARCHAR2 (20), CDE INVOICESTATUSCODE VARCHAR2 (20), DTE_TRANSACTIONDATE DATE, TXT TAXLITERAL VARCHAR2 (30), 45 NUMJTOTALEXCLUDINGGST NUMBER (10,2), NUM TOTALGSTAMT NUMBER (10,2), NUM_TOTALINCLUDINGGST NUMBER (10,2), NUM_EXCESSTOCOLLECT NUMBER (10,2), NUM_OWNERSCONTRIBUTION NUMBER (10,2), 50 NUM_TOT ALPAYABLEAMT NUMBER (10,2), NUM SUPPLYDIFFAMT NUMBER (10,2),
17
PRIMARY KEY ( CDE_REPAIRER, TXT_QUOTENO, NUMJSEQ, NUM_INVOICESEQ )
CREATE TABLE TB_VCP_PERIOD ( 5 CDE PERIOD VARCHAR2(20) NOT NULL, TXT DESCRIPTION VARCHAR2 (30) NOT NULL, NUM_DAYS NUMBER (38),
AMT_HOURS NUMBER (10,2),
0 PRIMARY KEY ( CDE_PERIOD )
CREATE TABLE TB_VCP_PRINCIPAL ( NAM_PRINCIPAL VARCHAR2 (30) NOT NULL, CDE TYPE VARCHAR2 (30) NOT NULL, TXT~PASSWORD VARCHAR2 (30) NOT NULL, NAM_BONU SID VARCHAR2 (30), TXTJBONUSPASSWORD VARCHAR2 (30), NAM_TOWERTD VARCHAR2 (30), TXTJTOWERP AS SWORD VARCHAR2 (30),
PRIMARY KEY ( NAM_PRINCIPAL )
CREATE TABLE TB_VCP_QUOTEIMAGE (
CDE CLAIM VARCHAR2 (20) NOT NULL,
CDEJREGO VARCHAR2 (10) NOT NULL,
CDE REPAIRER VARCHAR2 (20) NOT NULL,
NUM REINSTATEMENTSEQ NUMBER (38) NOT NULL,
CDEJFN NUMBER (38) NOT NULL,
PRIMARY KEY ( ODE CLAIM, CDE REGO, CDEJREPAIRER, NUM REINSTATEMENTSEQ, CDEJFN )
CREATE TABLE TB_VCP_QUOTEIMPACT (
CDE_CLA1M 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,
CDE IMPACTSEVERITYTYPE VARCHAR2 (20) NOT NULL, CDE_IMPACTSEVERrrYCODE varchar2 (20) NOT NULL,
PRIMARY KEY (CDEJXAIM, CDEJREGO, CDE REPAIRER, NUM REINSTATEMENTSEQ, CDE_IMPACTAREATYPE, CDEJMPACTAREACODE )
CREATE TABLE TB_VCP_QUOTEITEM ( CDEJREPAIRER VARCHAR2 (20) NOT NULL,
TXT QUOTENO VARCHAR2 (20) NOT NULL,
18
NUM SEQ NUMBER (38) NOT NULL,
NUM'riEMSEQ NUMBER (38) NOT NULL,
CDEJPARTSOURCE VARCHAR2 (11), CDEJPARTORIENTATION VARCHAR2 (20), 5 AMT_VALUE NUMBER (10,2),
CDEJTEM VARCHAR2 (20),
IXT 1TEMDESCRIPTION VARCHAR2 (40),
FLG_SUBLET CHAR(l),
TXT_SUBLETABN VARCHAR2 (20),
0 AMT HOURS NUMBER (10,2),
AMT_OVERRIDEHOURLYRATE NUMBER (5,2), CDE PARTNUMBER VARCHAR2 (30),
NUM INVOICE NUMBER (38),
AMT"PARTQUANnTY NUMBER (10,2), 5 CDEJREPAIRCATEGORYTYPE VARCHAR2 (20) NOT NULL, CDE REPAIRC ATEGORY CODE VARCHAR2 (20) NOT NULL, CDE REPAIRSUBCATEGORY VARCHAR2(20) NOT NULL, CDE_QUOTElTEMSTATUSTYPE VARCHAR2 (20) NOT NULL, CDE_QUOTErTEMSTATUSCODE VARCHAR2 (20) NOT NULL, 0 CDE_OCCURANCEORIGINTYPE VARCHAR2 (20) NOT NULL, CDE_OCCURANCEORIGINCODE VARCHAR2 (20) NOT NULL, TXT COMMENT VARCHAR2 (100),
CDE_REASONTYPE VARCHAR2 (20), CDE REASONCODE VARCHAR2 (20), 5 CDElASSESSOR VARCHAR2 (20),
DTE_TRANSACTIONTIMESTAMP DATE,
AMTJJNITVALUE NUMBER (10,2),
NUM_CYCLE NUMBER (38),
FLGJREPORTON CHAR(l),
0 CDE_PARTSOURCETYPE VARCHAR2 (20), CDEJPARTORIENTATIONTYPE VARCHAR2 (20),
PRIMARY KEY ( CDEJREPAIRER, TXT_QUOTENO, NUM_SEQ, NUM_ITEMSEQ )
CREATE TABLE TB_VCP QUOTETTEMUSERDEFINED ( CDE_REPAIRER VARCHAR2 (20) NOT NULL, TXT QUOTENO VARCHAR2 (20) NOT NULL, NUM SEQ NUMBER (38) NOT NULL, 0 NUMJTEMSEQ NUMBER (38) NOT NULL,
TXT_DEFINEATTRIBUTE VARCHAR2 (40) NOT NULL, TXT DEFINE VALUE VARCHAR2 (40) NOT NULL, NUMJMESSAGENO • NUMBER (38),
CDE_CLAIM VARCHAR2 (20),
PRIMARY KEY (CDEJREPAIRER, TXT_QUOTENO, NUM_SEQ NUMJTEMSEQ, TXTJDEFIb®ATTRIBUTE, TXT_DEFTNEVALUE )
0 CREATE TABLE TB_VCP_QUOTENOTE (
NUM_QUOTENOTE NUMBER (38) NOT NULL, NAM AUTHOR VARCHAR2 (30) NOT NULL,
19
CDE CLAIM VARCHAR2 (20) NOT NULL, CDE_REGO VARCHAR2 (10) NOT NULL,
CDE REPAIRER VARCHAR2 (20) NOT NULL, NUM_REINSTATEMENTSEQ NUMBER (38) NOT NULL, 5 DTE CREATED DATE,
TXTDOCUMENT LONG NOT NULL, CDE_NOTETYPE . VARCHAR2 (20),
CDENOTECODE VARCHAR2 (20),
TXT_QUOTENO VARCHAR2 (20),
NUM_SEQ NUMBER (38),
PRIMARY KEY ( NUMjQUOTENOTE )
CREATE TABLE TB VCP QUOTESTATUS ( CDE_QUOTESTATUS VARCHAR2 (20) NOT NULL, CDEJDEFAULTPRIORITYTYFE VARCHAR2 (20) NOT NULL, 20 CDE_DEFAULTPRIORITYCODE VARCHAR2 (20) NOT NULL, TXT DESCRIPTION VARCHAR2 (30) NOT NULL, CDEJrfODETYPE VARCHAR2 (20),
CDE_MODECODE VARCHAR2 (20),
PRIMARY KEY ( CDE_QUOTESTATUS )
CREATE TABLE TB_VCP QUOTESUMMARY (
CDE_REPAIRER VARCHAR2 (20) NOT NULL,
TXT_QUOTENO VARCHAR2 (20) NOT NULL,
NUM_SEQ NUMBER (38) NOT NULL,
CDE CLAIM VARCHAR2 (20) NOT NULL,
CDEJREGO VARCHAR2 (10) NOT NULL,
NUM REINSTATEMENTSEQ NUMBER (38) NOT NULL, 35 CDE_ALLOCATEDASSESSOR VARCHAR2 (20) NOT NULL, DTE_QUOTATION DATE,
NUM MESSAGE NUMBER (38),
CDE QUALITYSURVEYTYPE VARCHAR2 (20), CDElQUALITYSURVEYCQDE VARCHAR2 (20), 40 CDE_CUSTOMERSURVEYTYPE VARCHAR2 (20), CDE_CUSTOMERSURVEYCODE VARCHAR2 (20), CDECOSTCOMPARISONTYPE VARCHAR2 (20), CDE_COSTCOMPARISONCODE VARCHAR2 (20), DTE_START DATE,
45 DTE_END DATE,
CDE_REPAIRMODELTYPE VARCHAR2 (20) NOT NULL, CDE REPAIRMODELCODE VARCHAR2 (20) NOT NULL, CDE_QUOTETYPE VARCHAR2 (20) NOT NULL,
CDE_QUOTECODE VARCHAR2(20) NOT NULL,
50 CDE_QUOTESTATUS VARCHAR2 (20),
AMT_OWNERCOLLECTCONTRIBUnON NUMBER (10,2), TXT USERDEFINED LONG,
CDE SOFTWARE VARCHAR2 (10) NOT NULL,
TXTJSOFTWAREVERSION VARCHAR2 (10) NOT NULL, CDE_OCCURANCEORIGINTYPE VARCHAR2 (20), CDE OCCURANCEORIGINCODE VARCHAR2 (20), 5 TXTJREPAIRERCONTACT VARCHAR2 (30), CDE_EVALUATIONTYPE VARCHAR2 (20), CDEJBVALUAHONCODE VARCHAR2 (20), CDE_PROGRESSTYPE VARCHAR2 (20),
CDEPROGRESSCODE VARCHAR2 (20),
0 NUMCYCLE NUMBER (38),
CDEJVEHICLELOCATIONTYPE VARCHAR2 (20), CDE_VEHICLELOCATIONCODE VARCHAR2 (20), CDE_ASSESSOR VARCHAR2 (20),
DTE TRANSACTIONTIMESTAMP DATE,
PRIMARY KEY ( CDEJREPAIRER, TXTjQUOTENO, NUM_SEQ )
CREATE TABLE TB VCP_QUOTESUMMARYRATE (
0 CDE REPAIRER ~ VARCHAR2 (20) NOT NULL,
TXT_QUOTENO VARCHAR2 (20) NOT NULL,
NUM_SEQ NUMBER (38) NOT NULL,
CDE REPAIRCATEGORYTYPE VARCHAR2 (20) NOT NULL,
CDE_REPAIRCATEGORYCODE VARCHAR2 (20) NOT NULL, 5 CDE_REPAIRSUBCATEGORY VARCHAR2 (20) NOT NULL,
AMT HOURLYRATE NUMBER (5,2),
CDE_OCCURANCEORIGENTYPE VARCHAR2 (20),
CDE_OCCURRANCEORIGINCODE VARCHAR2 (20),
CDE_QUOTERATESTATUSTYPE VARCHAR2 (20),
0 CDE_QUOTERATESTATUSCODE VARCHAR2 (20),
PRIMARY KEY (CDE_REPAIRER, TXT_QUOTENO, NUM_SEQ, CDE REPAIRCATEGORYTYPE, CDE_REPAIRC ATEGORY CODE, CDE_REPAIRSUBCATEGORY)
CREATE TABLE TB_VCP_QUOTEUSERDEFINED (
CDE REPAIRER VARCHAR2(20) NOT NULL,
TXT_QUOTENO VARCHAR2 (20) NOT NULL,
NUM SEQ NUMBER (38) NOT NULL,
0 TXT3EFINEATTRIBUTE VARCHAR2 (40) NOT NULL,
TXT DEFINEVALUE VARCHAR2 (40) NOT NULL,
NUM_MESSAGENO NUMBER (38),
PRIMARY KEY (CDE REPAIRER, TXT_QUOTENO, NUM_SEQ, TXT DEFINEATTRIBUTE, 5 TXT_DEFINEVALUE)
CREATE TABLE TB_VCP_RATE (
CDE AREATYPE VARCHAR2 (10) NOT NULL,
0 CDE AREA VARCHAR2 (20) NOT NULL,
CDE RATETYPE VARCHAR2 (20) NOT NULL,
CDEJRATECODE VARCHAR2 (20) NOT NULL,
21
DTE EFFECTTVEDATE DATE NOT NULL,
AMTJRATE NUMBER (12,4) NOT NULL,
PRIMARY KEY ( CDE AREATYPE, CDE_AREA, CDEJRATETYPE, CDE_RATECODE, 5 DTEJSFFECTIVEDATE)
22
CREATE TABLE TB VCP REPAIREFFECTTVERATE (
CDE RATEGROUPTYFE VARCHAR2 (20) NOT NULL,
CDElRATEGROUPCODE VARCHAR2 (20) NOT NULL,
CDE REPAIRCATEGORYTYPE VARCHAR2 (20) NOT NULL, 5 CDE REPAIRCATEGORYCODE VARCHAR2 (20) NOT NULL,
CDEJREPAIRSUBCATEGORY VARCHAR2 (20) NOT NULL,
DTE START DATE NOT NULL,
AMT_HOURLYRATE NUMBER (5,2) NOT NULL,
PRIMARY KEY (CDE RATEGROUPTYFE, CDEJRATEGROUPCODE,
CDE_REPAIRCATEGORYTYPE, CDEREPAIRCATEGORYCODE, CDE_REPAIRSUB CATEGORY, DTE_START )
CREATE TABLE TB VCP REPAIRER (
CDE_REP AIRER VARCHAR2 (20) NOT NULL,
TXT ABN VARCHAR2 (20),
TXT_URL VARCHAR2 (60),
TXT EMAIL VARCHAR2 (60) NOT NULL,
CDE~DEFAULTASSESSOR VARCHAR2 (20),
CDE~PAYMENTMETHODTYPE VARCHAR2 (20) NOT NULL, CDEJPAYMENTMETHODCODE VARCHAR2 (20) NOT NULL, CDE COMMSMETHODTYPE VARCHAR2 (20) NOT NULL, CDE COMMSMETHODCODE VARCHAR2(20) NOT NULL, 25 TXT REPAIRERNAME VARCHAR2 (66),
CDE_BONUSREP AIRER VARCHAR2 (20),
PRIMARY KEY ( CDEJREPAIRER )
CREATE TABLE TB_VCP REPAIRERRATEGROUP (
CDE_COMPANYID VARCHAR2 (30) NOT NULL,
CDE_RATEGROUPTYPE VARCHAR2 (20) NOT NULL,
CDE RATEGROUPCODE VARCHAR2 (20) NOT NULL,
CDE REPAIRER VARCHAR2 (20) NOT NULL,
TXT_DESCRIPTION VARCHAR2 (30) NOT NULL,
PRIMARY KEY ( CDE COMPANYID, CDE RATEGROUPTYPE, CDE RATEGROUPCODE, CDE REPAIRER)
40
23
CREATE TABLE TB_VCP_REPAIRSUBCATEGORY ( CDE REPAIRCATEGORYTYPE VARCHAR2 (20) NOT NULL,
CDE~REP AIRCATEGORYCODE VARCHAR2 (20) NOT NULL, CDEJREPAIRSUBCATEGORY VARCHAR2 (20) NOT NULL, 5 TXTJDESCRIPTTON VARCHAR2 (30) NOT NULL,
NUM SORTCODE NUMBER (38),
TXT_SHORTDESCRIPTION VARCHAR2 (15),
PRIMARY KEY ( CDE REPAIRCATEGORYTYPE, CDE_REPAIRCATEGORY CODE, 10 CDE_REPAIRSUBCATEGORY)
CREATE TABLE TB VCP REQUESTFORQUOTE (
CDE_CLAJM VARCHAR2 (20) NOT NULL,
CDE_rego VARCHAR2 (10) NOT NULL,
CDE_REPAIRER VARCHAR2 (20) NOT NULL,
NUMJREINSTATEMENTSEQ NUMBER (38) NOT NULL, CDE_QUOTETYPE VARCHAJR2 (20) NOT NULL,
CDE_QUOTECODE VARCHAR2 (20) NOT NULL,
CDE REPAIRMODELTYPE VARCHAR2 (20) NOT NULL, CDEJREPAIRMODELCODE VARCHAR2 (20) NOT NULL, CDE_COMPLETIONREINSPECTIONTYPE VARCHAR2 (20), CDE COMPLETIONREINSPECnONCODE varchar2 (20), FLG~TOW CHAR(l) NOT NULL,
FLGIhERECAR CHAR(l) NOT NULL,
AMT_INSURANCEEXCESS NUMBER (10,2), NUM_MESSAGE NUMBER (38),
DTEREQUESTTEME DATE,
DTE_QUOTEEXPECTED DATE,
CDE_QUOTESTATUS VARCHAR2 (20),
TXT CONTRACTTEXT VARCHAR2 (1000),
PRIMARY KEY ( CDE_CLAIM, CDE_REGO, CDE_REPAJBRER, NUM_REINSTATEMENTSEQ)
CREATE TABLE TB VCP RESOURCE (
TXT_DESCRIPTION VARCHAR2(60) NOT NULL,
NAM_RESOURCE VARCHAR2 (30) NOT NULL,
40 PRIMARY KEY ( NAMJRESOURCE )
CREATE TABLE TB_VCP_ROLE (
CDE_ROLENAME VARCHAR2 (30) NOT NULL, 45 NAMJPRINCIPAL VARCHAR2 (30) NOT NULL,
PRIMARY KEY ( CDEJROLENAME, NAMJPRINCIPAL )
CREATE TABLE TB VCP SOFTWARE ( 50 CDE_SOFTWARE ~ VARCHAR2 (10) NOT NULL, TXT_VERSION VARCHAR2 (10) NOT NULL, CDE_CERTMCATIONTYPE VARCHAR2 (20),
24
C3DE_CERTIFICATIONCODE VARCHAR2 (20), TXTJDOCUMENT LONG NOT NULL, DTE~CERTIFICATIONDATE DATE,
PRIMARY KEY ( CDEJSOFTWARE, TXTJVERSION )
CREATE TABLE TB VCP WORK(
CDE WORK NUMBER (38) NOT NULL,
DTElCREATED DATE NOT NULL, DTE_ACTTONDUE DATE,
CDE_ALLOCATEDASSESSOR VARCHAR2 (20) NOT NULL, CDE PRIORITYTYPE VARCHAR2(20) NOT NULL, CDE~PRIORITYCODE VARCHAR2 (20) NOT NULL, 15 CDEJREPAIRAREATYPE VARCHAR2 (10) NOT NULL, CDE REPAIRAREA VARCHAR2 (20) NOT NULL, CDEIWORKREASONTYPE VARCHAR2 (20), CDE WORKREASONCODE VARCHAR2 (20), CDElQUOTESTATUS VARCHAR2 (20), 20 CDE CLAIM VARCHAR2 (20),
CDEJREGO VARCHAR2 (10),
CDE REPAIRER VARCHAR2 (20), NUMJREINSTATEMENTSEQ NUMBER (38), TXT QUOTENO VARCHAR2 (20), 25 NUM_QUOTESUMMARYSEQ NUMBER (38), CDE_PROGRESSTYPE VARCHAR2 (20), CDE_PROGRESSCODE VARCHAR2 (20),
PRIMARY KEY ( CDEJWORK)
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 35 as broadly described. The present embodiments are, therefore, to be considered in all respects as illustrative and not restrictive.