CREATE SEQUENCE SYSLOGSEQ_GEN INCREMENT BY 1 START WITH 1 CACHE 20;

CREATE TABLE ACCOUNT (
       ACCOUNTID1           bigint NOT NULL,
       ACCOUNTID2           bigint NOT NULL,
       ACCOUNTTYPEID        INTEGER NOT NULL,
       CLNTID               INTEGER NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       ACCOUNTSUM           DECIMAL(18,6) NOT NULL,
       MAXCREDIT            DECIMAL(18,6) NOT NULL,
       ISBLOCKED            SMALLINT NOT NULL,
       ISLOCAL              SMALLINT NOT NULL,
       ISCLOSED             SMALLINT NOT NULL,
       OPENTIME             character varying(14) NOT NULL,
       CLOSETIME            character varying(14) NULL,
       DESCRIPTION          character varying(100) NULL,
       SYNCSTATUSFLAG       INTEGER NOT NULL,
       UPDATENUM            bigint NOT NULL
);

CREATE INDEX XIE1ACCOUNT ON ACCOUNT
(
       CLNTID                         ASC
);

CREATE INDEX XIE2ACCOUNT ON ACCOUNT
(
       ACCOUNTTYPEID                  ASC
);

CREATE INDEX XIE3ACCOUNT ON ACCOUNT
(
       UPDATENUM                      ASC
);

CREATE INDEX XIE4ACCOUNT ON ACCOUNT
(
       SYNCSTATUSFLAG                 ASC
);


ALTER TABLE ACCOUNT
       ADD CONSTRAINT XPKACCOUNT PRIMARY KEY (ACCOUNTID1, ACCOUNTID2);


CREATE TABLE ACCOUNTDOC (
       ACCOUNTDOCID1        bigint NOT NULL,
       ACCOUNTDOCID2        bigint NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       CURRENCYRATE         DECIMAL(18,6) NOT NULL,
       DOCSUM               DECIMAL(18,6) NOT NULL,
       DCARDID              character varying(30) NULL,
       SAREAID              INTEGER NULL,
       SYSTEMID             INTEGER NULL,
       ACCOUNTDOCTIME       character varying(14) NOT NULL,
       ACCOUNTDOCSOURCE     INTEGER NOT NULL,
       ACCOUNTDOCTYPE       INTEGER NOT NULL,
       PARENTDOCID1         bigint NULL,
       PARENTDOCID2         bigint NULL,
       TRANSITEMSCOUNT      INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1ACCOUNTDOC ON ACCOUNTDOC
(
       PARENTDOCID1                   ASC,
       PARENTDOCID2                   ASC
);

CREATE INDEX XIE2ACCOUNTDOC ON ACCOUNTDOC
(
       UPDATENUM                      ASC
);

CREATE INDEX XIE3ACCOUNTDOC ON ACCOUNTDOC
(
       ACCOUNTDOCTIME                 ASC
);


ALTER TABLE ACCOUNTDOC
       ADD CONSTRAINT XPKACCOUNTDOC PRIMARY KEY (ACCOUNTDOCID1, ACCOUNTDOCID2);


CREATE TABLE ACCOUNTDOCROOT (
       ACCOUNTDOCID1        bigint NOT NULL,
       ACCOUNTDOCID2        bigint NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       CURRENCYRATE         DECIMAL(18,6) NOT NULL,
       DOCSUM               DECIMAL(18,6) NOT NULL,
       DCARDID              character varying(30) NULL,
       SAREAID              INTEGER NULL,
       SYSTEMID             INTEGER NULL,
       ACCOUNTDOCTIME       character varying(14) NOT NULL,
       ACCOUNTDOCSOURCE     INTEGER NOT NULL,
       ACCOUNTDOCTYPE       INTEGER NOT NULL,
       PARENTDOCID1         bigint NULL,
       PARENTDOCID2         bigint NULL,
       TRANSITEMSCOUNT      INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1ACCOUNTDOCROOT ON ACCOUNTDOCROOT
(
       ACCOUNTDOCID1                  ASC,
       ACCOUNTDOCID2                  ASC
);


CREATE TABLE ACCOUNTDOCSYNC (
       ACCOUNTDOCID1        bigint NOT NULL,
       ACCOUNTDOCID2        bigint NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       CURRENCYRATE         DECIMAL(18,6) NOT NULL,
       DOCSUM               DECIMAL(18,6) NOT NULL,
       DCARDID              character varying(30) NULL,
       SAREAID              INTEGER NULL,
       SYSTEMID             INTEGER NULL,
       ACCOUNTDOCTIME       character varying(14) NOT NULL,
       ACCOUNTDOCSOURCE     INTEGER NOT NULL,
       ACCOUNTDOCTYPE       INTEGER NOT NULL,
       PARENTDOCID1         bigint NULL,
       PARENTDOCID2         bigint NULL,
       TRANSITEMSCOUNT      INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1ACCOUNTDOCSYNC ON ACCOUNTDOCSYNC
(
       UPDATENUM                      ASC
);

CREATE INDEX XIE2ACCOUNTDOCSYNC ON ACCOUNTDOCSYNC
(
       ACCOUNTDOCTIME                 ASC
);


ALTER TABLE ACCOUNTDOCSYNC
       ADD CONSTRAINT XPKACCOUNTDOCSYNC PRIMARY KEY (ACCOUNTDOCID1, ACCOUNTDOCID2);


CREATE TABLE ACCOUNTROOT (
       ACCOUNTID1           bigint NOT NULL,
       ACCOUNTID2           bigint NOT NULL,
       ACCOUNTTYPEID        INTEGER NOT NULL,
       CLNTID               INTEGER NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       ACCOUNTSUM           DECIMAL(18,6) NOT NULL,
       MAXCREDIT            DECIMAL(18,6) NOT NULL,
       ISBLOCKED            SMALLINT NOT NULL,
       ISLOCAL              SMALLINT NOT NULL,
       ISCLOSED             SMALLINT NOT NULL,
       OPENTIME             character varying(14) NOT NULL,
       CLOSETIME            character varying(14) NULL,
       DESCRIPTION          character varying(100) NULL,
       SYNCSTATUSFLAG       INTEGER NOT NULL,
       ACCOUNTTYPEVALUE     INTEGER NOT NULL,
       ACCOUNTKINDVALUE     INTEGER NOT NULL,
       UPDATENUM            bigint NOT NULL
);


ALTER TABLE ACCOUNTROOT
       ADD CONSTRAINT XPKACCOUNTROOT PRIMARY KEY (ACCOUNTID1, ACCOUNTID2);


CREATE TABLE ACCOUNTSYNC (
       ACCOUNTID1           bigint NOT NULL,
       ACCOUNTID2           bigint NOT NULL,
       ACCOUNTTYPEID        INTEGER NOT NULL,
       CLNTID               INTEGER NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       ACCOUNTSUM           DECIMAL(18,6) NOT NULL,
       MAXCREDIT            DECIMAL(18,6) NOT NULL,
       ISBLOCKED            SMALLINT NOT NULL,
       ISLOCAL              SMALLINT NOT NULL,
       ISCLOSED             SMALLINT NOT NULL,
       OPENTIME             character varying(14) NOT NULL,
       CLOSETIME            character varying(14) NULL,
       DESCRIPTION          character varying(100) NULL,
       SYNCSTATUSFLAG       INTEGER NOT NULL,
       ACCOUNTTYPEVALUE     INTEGER NOT NULL,
       ACCOUNTKINDVALUE     INTEGER NOT NULL,
       UPDATENUM            bigint NOT NULL
);


ALTER TABLE ACCOUNTSYNC
       ADD CONSTRAINT XPKACCOUNTSYNC PRIMARY KEY (ACCOUNTID1, ACCOUNTID2);


CREATE TABLE ACCOUNTTRANS (
       ACCOUNTDOCID1        bigint NOT NULL,
       ACCOUNTDOCID2        bigint NOT NULL,
       ITEMORDER            INTEGER NOT NULL,
       ACCOUNTID1           bigint NOT NULL,
       ACCOUNTID2           bigint NOT NULL,
       TRANSSUM             DECIMAL(18,6) NOT NULL,
       DOCTRANSSUM          DECIMAL(18,6) NOT NULL,
       CURRENCYRATE         DECIMAL(18,6) NOT NULL,
       ACTIONTYPE           INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE ACCOUNTTRANS
       ADD CONSTRAINT XPKACCOUNTTRANS PRIMARY KEY (ACCOUNTDOCID1, ACCOUNTDOCID2, ITEMORDER);


CREATE TABLE ACCOUNTTRANSROOT (
       ACCOUNTDOCID1        bigint NOT NULL,
       ACCOUNTDOCID2        bigint NOT NULL,
       ITEMORDER            INTEGER NOT NULL,
       ACCOUNTID1           bigint NOT NULL,
       ACCOUNTID2           bigint NOT NULL,
       ISLOCAL              SMALLINT NOT NULL,
       TRANSSUM             DECIMAL(18,6) NOT NULL,
       DOCTRANSSUM          DECIMAL(18,6) NOT NULL,
       CURRENCYRATE         DECIMAL(18,6) NOT NULL,
       ACTIONTYPE           INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1ACCOUNTTRANSROOT ON ACCOUNTTRANSROOT
(
       ACCOUNTDOCID1                  ASC,
       ACCOUNTDOCID2                  ASC,
       ITEMORDER                      ASC
);

CREATE INDEX XIE2ACCOUNTTRANSROOT ON ACCOUNTTRANSROOT
(
       ACCOUNTID1                     ASC,
       ACCOUNTID2                     ASC
);


CREATE TABLE ACCOUNTTRANSSYNC (
       ACCOUNTDOCID1        bigint NOT NULL,
       ACCOUNTDOCID2        bigint NOT NULL,
       ITEMORDER            INTEGER NOT NULL,
       ACCOUNTID1           bigint NOT NULL,
       ACCOUNTID2           bigint NOT NULL,
       ISLOCAL              SMALLINT NOT NULL,
       TRANSSUM             DECIMAL(18,6) NOT NULL,
       DOCTRANSSUM          DECIMAL(18,6) NOT NULL,
       CURRENCYRATE         DECIMAL(18,6) NOT NULL,
       ACTIONTYPE           INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE ACCOUNTTRANSSYNC
       ADD CONSTRAINT XPKACCOUNTTRANSSYNC PRIMARY KEY (ACCOUNTDOCID1, ACCOUNTDOCID2, ITEMORDER);


CREATE TABLE ACCOUNTTYPE (
       ACCOUNTTYPEID        INTEGER NOT NULL,
       ACCOUNTTYPENAME      character varying(50) NOT NULL,
       ACCOUNTTYPEVALUE     INTEGER NOT NULL,
       ACCOUNTKINDVALUE     INTEGER NOT NULL,
       MAXCREDIT            DECIMAL(18,6) NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE ACCOUNTTYPE
       ADD CONSTRAINT XPKACCOUNTTYPE PRIMARY KEY (ACCOUNTTYPEID);


CREATE TABLE BACKACCOUNTTRANS (
       BACKACCOUNTTRANSID   character varying(36) NOT NULL,
       SAREAID              INTEGER NULL,
       SYSTEMID             INTEGER NULL,
       CLNTID               INTEGER NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       ACCOUNTTYPEID        INTEGER NOT NULL,
       TRANSTIME            character varying(14) NOT NULL,
       TRANSTYPE            INTEGER NOT NULL,
       TRANSSUM             DECIMAL(18,6) NOT NULL,
       AUTOCREATEACCOUNT    SMALLINT NOT NULL,
       ACCOUNTDOCSOURCE     INTEGER NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX IDX_BACKACCOUNTTRANS_TYPE ON BACKACCOUNTTRANS
(
       TRANSTYPE                      ASC
);


ALTER TABLE BACKACCOUNTTRANS
       ADD CONSTRAINT XPKBACKACCOUNTTRANS PRIMARY KEY (BACKACCOUNTTRANSID);


CREATE TABLE CLNT (
       CLNTID               INTEGER NOT NULL,
       CLNTGRPID            INTEGER NOT NULL,
       COMPANYID            INTEGER NULL,
       PROPERTYGRPID        INTEGER NULL,
       CLNTNAME             character varying(50) NOT NULL,
       CLNTBIRTHDAY         character varying(14) NULL,
       LOCKED               SMALLINT NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       PRIMARYEMAIL         character varying(50) NULL,
       UPDATENUM            INTEGER NOT NULL,
       PRIMARYPHONE         character varying(50) NULL
);


ALTER TABLE CLNT
       ADD PRIMARY KEY (CLNTID);


CREATE TABLE CLNTFORM (
       CLNTFORMID           INTEGER NOT NULL,
       CLNTFORMNAME         character varying(50) NOT NULL,
       ORDERNUM             INTEGER NOT NULL,
       USESAREA             SMALLINT NOT NULL,
       ACTIVEFROM           character varying(14) NULL,
       ACTIVETO             character varying(14) NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE CLNTFORM
       ADD CONSTRAINT XPKCLNTFORM PRIMARY KEY (CLNTFORMID);


CREATE TABLE CLNTFORMITEMS (
       CLNTFORMID           INTEGER NOT NULL,
       CLNTFORMITEMID       INTEGER NOT NULL,
       CLNTFORMITEM         character varying(100) NOT NULL,
       ORDERNUM             INTEGER NOT NULL,
       ISREQUIRED           SMALLINT NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE CLNTFORMITEMS
       ADD CONSTRAINT XPKCLNTFORMITEMS PRIMARY KEY (CLNTFORMID, CLNTFORMITEMID);


CREATE TABLE CLNTFORMPROPERTY (
       CLNTID               INTEGER NOT NULL,
       CLNTFORMID           INTEGER NOT NULL,
       CLNTFORMITEMID       INTEGER NOT NULL,
       CLNTPROPERTYVAL      character varying(1024) NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE CLNTFORMPROPERTY
       ADD CONSTRAINT XPKCLNTFORMPROPERTY PRIMARY KEY (CLNTID, CLNTFORMID, CLNTFORMITEMID);


CREATE TABLE CLNTGRP (
       CLNTGRPID            INTEGER NOT NULL,
       DISCID               INTEGER NULL,
       BONUSID              INTEGER NULL,
       CLNTGRPNAME          character varying(50) NOT NULL,
       CLNTGRPMANUAL        SMALLINT NOT NULL,
       CLNTGRPTYPE          INTEGER NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE CLNTGRP
       ADD CONSTRAINT XPKCLNTGRP PRIMARY KEY (CLNTGRPID);


CREATE TABLE CLNTPROPERTY (
       CLNTID               INTEGER NOT NULL,
       CLNTPROPERTYKEY      INTEGER NOT NULL,
       CLNTPROPERTYVAL      character varying(1024) NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE CLNTPROPERTY
       ADD CONSTRAINT XPKCLNTPROPERTY PRIMARY KEY (CLNTID, CLNTPROPERTYKEY);


CREATE TABLE CURRENCY (
       CURRENCYID           INTEGER NOT NULL,
       CURRENCYNAME         character varying(50) NOT NULL,
       CURRENCYSNAME        character varying(50) NULL,
       CURRENCYRATE         DECIMAL(18,6) NOT NULL,
       CURRENCYABBR         character varying(30) NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE CURRENCY
       ADD CONSTRAINT XPKCURRENCY PRIMARY KEY (CURRENCYID);


CREATE TABLE DATAPUMPCFG (
       CFGKEY               character varying(50) NOT NULL,
       PROFILEID            INTEGER NOT NULL,
       CFGVALUE             character varying(100) NOT NULL
);


ALTER TABLE DATAPUMPCFG
       ADD CONSTRAINT XPKDATAPUMPCFG PRIMARY KEY (CFGKEY, PROFILEID);


CREATE TABLE DATASYNCCLNT (
       CLNTKEY              character varying(50) NOT NULL,
       CLNTVALUE            character varying(100) NULL
);


ALTER TABLE DATASYNCCLNT
       ADD CONSTRAINT XPKDATASYNCCLNT PRIMARY KEY (CLNTKEY);


CREATE TABLE DATASYNCDIRS (
       DIRNAME              character varying(50) NOT NULL,
       SYNCDIR              INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL,
       SYNCPRIORITY         INTEGER NOT NULL
);


ALTER TABLE DATASYNCDIRS
       ADD CONSTRAINT XPKDATASYNCDIRS PRIMARY KEY (DIRNAME);


CREATE TABLE DCARD (
       DCARDID              character varying(30) NOT NULL,
       CLNTID               INTEGER NOT NULL,
       DCARDCODE            character varying(30) NOT NULL,
       DCARDNAME            character varying(50) NULL,
       ISPAYMENT            SMALLINT NOT NULL,
       PINCODE              character varying(256) NULL,
       LOCKED               SMALLINT NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE UNIQUE INDEX XIE1DCARD ON DCARD
(
       DCARDCODE                      ASC
);


ALTER TABLE DCARD
       ADD CONSTRAINT XPKDCARD PRIMARY KEY (DCARDID);


CREATE TABLE EVENTS (
       EVENTCODE            INTEGER NOT NULL,
       EVENTLENG            INTEGER NOT NULL,
       EVENTCONTENT         character varying(1024) NOT NULL
);


ALTER TABLE EVENTS
       ADD CONSTRAINT XPKEVENTS PRIMARY KEY (EVENTCODE, EVENTLENG);


CREATE TABLE FIRM (
       FIRMID               INTEGER NOT NULL,
       LOYALTYCLUBID        INTEGER NULL,
       CURRENCYID           INTEGER NULL,
       FIRMNAME             character varying(100) NOT NULL,
       FIRMADDR             character varying(100) NULL,
       DOCPREFIX            character varying(30) NULL,
       CERTIFICATENUM       character varying(100) NULL,
       MAILADDR             character varying(100) NULL,
       PHONES               character varying(50) NULL,
       REGISTRYNUM          character varying(50) NULL,
       TAXIDENTNUM          character varying(50) NULL,
       ASKPINCODE           SMALLINT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE FIRM
       ADD CONSTRAINT XPKFIRM PRIMARY KEY (FIRMID);


CREATE TABLE IMPORTREQUESTLOG (
       SAREAID              INTEGER NOT NULL,
       SYSTEMID             INTEGER NOT NULL,
       REQUESTID            INTEGER NOT NULL,
       ERRORCODE            INTEGER NOT NULL,
       LOGMESSAGE           character varying(2000) NULL,
       TIMEPARSED           character varying(14) NOT NULL
);


ALTER TABLE IMPORTREQUESTLOG
       ADD CONSTRAINT XPKIMPORTREQUESTLOG PRIMARY KEY (SAREAID, SYSTEMID, REQUESTID);


CREATE TABLE IMPORTTRANSLOG (
       TRANSLOGID           character varying(36) NOT NULL,
       ERRORCODE            INTEGER NOT NULL,
       LOGMESSAGE           character varying(2000) NULL,
       TIMEPARSED           character varying(14) NOT NULL
);


ALTER TABLE IMPORTTRANSLOG
       ADD CONSTRAINT XPKIMPORTTRANSLOG PRIMARY KEY (TRANSLOGID);


CREATE TABLE LOCALREQUEST (
       SAREAID              INTEGER NOT NULL,
       SYSTEMID             INTEGER NOT NULL,
       REQUESTID            INTEGER NOT NULL,
       TARGETSYSTEM         INTEGER NOT NULL,
       REQUESTTYPE          INTEGER NOT NULL,
       REQUESTTIME          character varying(14) NOT NULL,
       ITEMSCOUNT           INTEGER NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1LOCALREQUEST ON LOCALREQUEST
(
       UPDATENUM                      ASC
);


ALTER TABLE LOCALREQUEST
       ADD CONSTRAINT XPKLOCALREQUEST PRIMARY KEY (SAREAID, SYSTEMID, REQUESTID);


CREATE TABLE LOCALREQUESTITEMS (
       SAREAID              INTEGER NOT NULL,
       SYSTEMID             INTEGER NOT NULL,
       REQUESTID            INTEGER NOT NULL,
       REQUESTKEY           INTEGER NOT NULL,
       REQUESTVAL           character varying(1024) NULL,
       DELFLAG              INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1LOCALREQUESTITEMS ON LOCALREQUESTITEMS
(
       UPDATENUM                      ASC
);


ALTER TABLE LOCALREQUESTITEMS
       ADD CONSTRAINT XPKLOCALREQUESTITEMS PRIMARY KEY (SAREAID, SYSTEMID, REQUESTID, REQUESTKEY);


CREATE TABLE LOYALTYCLUB (
       LOYALTYCLUBID        INTEGER NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       ACCUMACCOUNTTYPEID   INTEGER NOT NULL,
       BONUSACCOUNTTYPEID   INTEGER NOT NULL,
       CLNTFORMID           INTEGER NULL,
       LOYALTYCLUBNAME      character varying(50) NOT NULL,
       ASKPINCODE           SMALLINT NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE LOYALTYCLUB
       ADD CONSTRAINT XPKLOYALTYCLUB PRIMARY KEY (LOYALTYCLUBID);


CREATE TABLE MPDBINFO (
       INFOKEY              character varying(30) NOT NULL,
       INFOVAL              character varying(100) NULL
);


ALTER TABLE MPDBINFO
       ADD CONSTRAINT XPKMPDBINFO PRIMARY KEY (INFOKEY);


CREATE TABLE PAYMENTSCHEME (
       PAYMENTSCHEMEID      INTEGER NOT NULL,
       PAYMENTSCHEMENAME    character varying(50) NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE PAYMENTSCHEME
       ADD CONSTRAINT XPKPAYMENTSCHEME PRIMARY KEY (PAYMENTSCHEMEID);


CREATE TABLE PAYMENTSCHEMEITEMS (
       PAYMENTSCHEMEID      INTEGER NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       ACCOUNTTYPEID        INTEGER NOT NULL,
       ORDERKEY             INTEGER NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE PAYMENTSCHEMEITEMS
       ADD CONSTRAINT XPKPAYMENTSCHEMEITEMS PRIMARY KEY (PAYMENTSCHEMEID, CURRENCYID, ACCOUNTTYPEID);


CREATE TABLE PCCLNTSUM (
       CLNTID               INTEGER NOT NULL,
       CURRENCYID           INTEGER NOT NULL,
       LOYALTYCLUBID        INTEGER NOT NULL,
       CLNTSUM              DECIMAL(18,6) NOT NULL,
       CLNTBONUS            DECIMAL(18,6) NOT NULL,
       UPDATETIME           character varying(14) NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1PCCLNTSUM ON PCCLNTSUM
(
       UPDATENUM                      ASC
);


ALTER TABLE PCCLNTSUM
       ADD CONSTRAINT XPKPCCLNTSUM PRIMARY KEY (CLNTID, CURRENCYID, LOYALTYCLUBID);


CREATE TABLE PCDATASYNCDIRS (
       DIRNAME              character varying(50) NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE PCDATASYNCDIRS
       ADD CONSTRAINT XPKPCDATASYNCDIRS PRIMARY KEY (DIRNAME);


CREATE TABLE PCREQUESTITEMSSYNC (
       SAREAID              INTEGER NOT NULL,
       SYSTEMID             INTEGER NOT NULL,
       REQUESTID            INTEGER NOT NULL,
       REQUESTKEY           INTEGER NOT NULL,
       REQUESTVAL           character varying(1024) NULL,
       DELFLAG              INTEGER NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE PCREQUESTITEMSSYNC
       ADD CONSTRAINT XPKPCREQUESTITEMSSYNC PRIMARY KEY (SAREAID, SYSTEMID, REQUESTID, REQUESTKEY);


CREATE TABLE PCREQUESTSYNC (
       SAREAID              INTEGER NOT NULL,
       SYSTEMID             INTEGER NOT NULL,
       REQUESTID            INTEGER NOT NULL,
       REQUESTTYPE          INTEGER NOT NULL,
       REQUESTTIME          character varying(14) NOT NULL,
       ITEMSCOUNT           INTEGER NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1PCREQUESTSYNC ON PCREQUESTSYNC
(
       UPDATENUM                      ASC
);


ALTER TABLE PCREQUESTSYNC
       ADD CONSTRAINT XPKPCREQUESTSYNC PRIMARY KEY (SAREAID, SYSTEMID, REQUESTID);


CREATE TABLE PCSERVERCFG (
       CFGKEY               INTEGER NOT NULL,
       CFGVALUE             character varying(1024) NULL
);


ALTER TABLE PCSERVERCFG
       ADD CONSTRAINT XPKPCSERVERCFG PRIMARY KEY (CFGKEY);


CREATE TABLE PCSYNCSERVERCFG (
       CFGKEY               INTEGER NOT NULL,
       CFGVALUE             character varying(1024) NULL
);


ALTER TABLE PCSYNCSERVERCFG
       ADD CONSTRAINT XPKPCSYNCSERVERCFG PRIMARY KEY (CFGKEY);


CREATE TABLE PROCESSINGPUMPCFG (
       CFGKEY               character varying(30) NOT NULL,
       CFGVALUE             character varying(100) NULL
);


ALTER TABLE PROCESSINGPUMPCFG
       ADD CONSTRAINT XPKPROCESSINGPUMPCFG PRIMARY KEY (CFGKEY);


CREATE TABLE PUMPPROFILE (
       PROFILEID            INTEGER NOT NULL,
       PROFILENAME          character varying(30) NOT NULL
);


ALTER TABLE PUMPPROFILE
       ADD CONSTRAINT XPKPUMPPROFILE PRIMARY KEY (PROFILEID);


CREATE TABLE SAREA (
       SAREAID              INTEGER NOT NULL,
       PRCLEVELID           INTEGER NOT NULL,
       CASHPROFILEID        INTEGER NOT NULL,
       ARTHOTKEYSETID       INTEGER NULL,
       FIRMID               INTEGER NOT NULL,
       WAREHOUSEID          INTEGER NULL,
       PRNSCHEMEID          INTEGER NULL,
       CURRENCYID           INTEGER NULL,
       SAREANAME            character varying(50) NOT NULL,
       SAREAADDR            character varying(100) NULL,
       ASKPINCODE           SMALLINT NULL,
       RECEIPTHEADER        character varying(100) NULL,
       DELFLAG              SMALLINT NOT NULL,
       RECEIPTFOOTER        character varying(100) NULL,
       UPDATENUM            INTEGER NOT NULL,
       RECEIPTFIRSTHEADER   character varying(100) NULL,
       RECEIPTENDFOOTER     character varying(100) NULL
);


ALTER TABLE SAREA
       ADD CONSTRAINT XPKSAREA PRIMARY KEY (SAREAID);


CREATE TABLE SAREASAUSER (
       SAREAID              INTEGER NOT NULL,
       SAUSERID             INTEGER NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE SAREASAUSER
       ADD CONSTRAINT XPKSAREASAUSER PRIMARY KEY (SAREAID, SAUSERID);


CREATE TABLE SAUSER (
       SAUSERID             INTEGER NOT NULL,
       SAUSERPROFILEID      INTEGER NOT NULL,
       SAUSERPASSWORD       character varying(30) NOT NULL,
       SAUSERNAME           character varying(50) NOT NULL,
       SAUSERDISABLED       SMALLINT NOT NULL,
       USERSAREACONTROL     SMALLINT NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE SAUSER
       ADD CONSTRAINT XPKSAUSER PRIMARY KEY (SAUSERID);


CREATE TABLE SAUSERPROFILE (
       SAUSERPROFILEID      INTEGER NOT NULL,
       SAUSERPROFILENAME    character varying(50) NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE SAUSERPROFILE
       ADD CONSTRAINT XPKSAUSERPROFILE PRIMARY KEY (SAUSERPROFILEID);


CREATE TABLE SAUSERPVG (
       SAUSERPVGKEY         INTEGER NOT NULL,
       SAUSERPROFILEID      INTEGER NOT NULL,
       SAUSERPVGVALUE       INTEGER NOT NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);


ALTER TABLE SAUSERPVG
       ADD CONSTRAINT XPKSAUSERPVG PRIMARY KEY (SAUSERPVGKEY, SAUSERPROFILEID);


CREATE TABLE SYSLOG (
       SYSTEMID             INTEGER NOT NULL,
       SEQ                  INTEGER NOT NULL DEFAULT nextval('SYSLOGSEQ_GEN'),
       SAREAID              INTEGER NOT NULL,
       CASHIERID            INTEGER NULL,
       EVENTTIME            character varying(14) NULL,
       EVENTCODE            INTEGER NULL,
       EVENTDATA            character varying(2000) NULL,
       DELFLAG              SMALLINT NOT NULL,
       UPDATENUM            INTEGER NOT NULL
);

CREATE INDEX XIE1SYSLOG ON SYSLOG
(
       UPDATENUM                      ASC
);

CREATE INDEX XIE2SYSLOG ON SYSLOG
(
       EVENTTIME                      ASC
);

CREATE INDEX XIE3SYSLOG ON SYSLOG
(
       EVENTCODE                      ASC
);


ALTER TABLE SYSLOG
       ADD CONSTRAINT XPKSYSLOG PRIMARY KEY (SYSTEMID, SEQ, SAREAID);


ALTER TABLE ACCOUNT
       ADD CONSTRAINT R_1 FOREIGN KEY (CLNTID)
                             REFERENCES CLNT  (CLNTID);


ALTER TABLE ACCOUNT
       ADD CONSTRAINT R_2 FOREIGN KEY (CURRENCYID)
                             REFERENCES CURRENCY  (CURRENCYID);


ALTER TABLE ACCOUNT
       ADD CONSTRAINT R_3 FOREIGN KEY (ACCOUNTTYPEID)
                             REFERENCES ACCOUNTTYPE  (ACCOUNTTYPEID);


ALTER TABLE ACCOUNTDOC
       ADD CONSTRAINT R_4 FOREIGN KEY (CURRENCYID)
                             REFERENCES CURRENCY  (CURRENCYID);


ALTER TABLE ACCOUNTTRANS
       ADD CONSTRAINT R_5 FOREIGN KEY (ACCOUNTID1, ACCOUNTID2)
                             REFERENCES ACCOUNT  (ACCOUNTID1, 
              ACCOUNTID2);


ALTER TABLE ACCOUNTTRANS
       ADD CONSTRAINT R_6 FOREIGN KEY (ACCOUNTDOCID1, ACCOUNTDOCID2)
                             REFERENCES ACCOUNTDOC  (ACCOUNTDOCID1, 
              ACCOUNTDOCID2)
                             ON DELETE CASCADE;


ALTER TABLE ACCOUNTTRANSSYNC
       ADD CONSTRAINT R_7 FOREIGN KEY (ACCOUNTDOCID1, ACCOUNTDOCID2)
                             REFERENCES ACCOUNTDOCSYNC  (
              ACCOUNTDOCID1, ACCOUNTDOCID2)
                             ON DELETE CASCADE;


ALTER TABLE CLNT
       ADD CONSTRAINT R_8 FOREIGN KEY (CLNTGRPID)
                             REFERENCES CLNTGRP  (CLNTGRPID);


ALTER TABLE CLNTFORMITEMS
       ADD CONSTRAINT R_9 FOREIGN KEY (CLNTFORMID)
                             REFERENCES CLNTFORM  (CLNTFORMID)
                             ON DELETE CASCADE;


ALTER TABLE CLNTFORMPROPERTY
       ADD CONSTRAINT R_10 FOREIGN KEY (CLNTID)
                             REFERENCES CLNT  (CLNTID)
                             ON DELETE CASCADE;


ALTER TABLE CLNTFORMPROPERTY
       ADD CONSTRAINT R_12 FOREIGN KEY (CLNTFORMID, CLNTFORMITEMID)
                             REFERENCES CLNTFORMITEMS  (CLNTFORMID, 
              CLNTFORMITEMID)
                             ON DELETE CASCADE;


ALTER TABLE CLNTPROPERTY
       ADD CONSTRAINT R_13 FOREIGN KEY (CLNTID)
                             REFERENCES CLNT  (CLNTID)
                             ON DELETE CASCADE;


ALTER TABLE DATAPUMPCFG
       ADD CONSTRAINT R_14 FOREIGN KEY (PROFILEID)
                             REFERENCES PUMPPROFILE  (PROFILEID);


ALTER TABLE DCARD
       ADD CONSTRAINT R_16 FOREIGN KEY (CLNTID)
                             REFERENCES CLNT  (CLNTID);


ALTER TABLE FIRM
       ADD CONSTRAINT R_17 FOREIGN KEY (CURRENCYID)
                             REFERENCES CURRENCY  (CURRENCYID);


ALTER TABLE FIRM
       ADD CONSTRAINT R_18 FOREIGN KEY (LOYALTYCLUBID)
                             REFERENCES LOYALTYCLUB  (LOYALTYCLUBID);


ALTER TABLE LOCALREQUESTITEMS
       ADD CONSTRAINT R_19 FOREIGN KEY (SAREAID, SYSTEMID, REQUESTID)
                             REFERENCES LOCALREQUEST  (SAREAID, 
              SYSTEMID, REQUESTID)
                             ON DELETE CASCADE;


ALTER TABLE LOYALTYCLUB
       ADD CONSTRAINT R_20 FOREIGN KEY (CLNTFORMID)
                             REFERENCES CLNTFORM  (CLNTFORMID);


ALTER TABLE LOYALTYCLUB
       ADD CONSTRAINT R_21 FOREIGN KEY (CURRENCYID)
                             REFERENCES CURRENCY  (CURRENCYID);


ALTER TABLE LOYALTYCLUB
       ADD CONSTRAINT R_22 FOREIGN KEY (BONUSACCOUNTTYPEID)
                             REFERENCES ACCOUNTTYPE  (ACCOUNTTYPEID);


ALTER TABLE LOYALTYCLUB
       ADD CONSTRAINT R_23 FOREIGN KEY (ACCUMACCOUNTTYPEID)
                             REFERENCES ACCOUNTTYPE  (ACCOUNTTYPEID);


ALTER TABLE PAYMENTSCHEMEITEMS
       ADD CONSTRAINT R_24 FOREIGN KEY (PAYMENTSCHEMEID)
                             REFERENCES PAYMENTSCHEME  (
              PAYMENTSCHEMEID);


ALTER TABLE PAYMENTSCHEMEITEMS
       ADD CONSTRAINT R_25 FOREIGN KEY (CURRENCYID)
                             REFERENCES CURRENCY  (CURRENCYID);


ALTER TABLE PAYMENTSCHEMEITEMS
       ADD CONSTRAINT R_26 FOREIGN KEY (ACCOUNTTYPEID)
                             REFERENCES ACCOUNTTYPE  (ACCOUNTTYPEID);


ALTER TABLE PCREQUESTITEMSSYNC
       ADD CONSTRAINT R_27 FOREIGN KEY (SAREAID, SYSTEMID, REQUESTID)
                             REFERENCES PCREQUESTSYNC  (SAREAID, 
              SYSTEMID, REQUESTID)
                             ON DELETE CASCADE;


ALTER TABLE SAREA
       ADD CONSTRAINT R_28 FOREIGN KEY (FIRMID)
                             REFERENCES FIRM  (FIRMID);


ALTER TABLE SAREA
       ADD CONSTRAINT R_29 FOREIGN KEY (CURRENCYID)
                             REFERENCES CURRENCY  (CURRENCYID);


ALTER TABLE SAREASAUSER
       ADD CONSTRAINT R_30 FOREIGN KEY (SAUSERID)
                             REFERENCES SAUSER  (SAUSERID);


ALTER TABLE SAREASAUSER
       ADD CONSTRAINT R_31 FOREIGN KEY (SAREAID)
                             REFERENCES SAREA  (SAREAID);


ALTER TABLE SAUSER
       ADD CONSTRAINT R_32 FOREIGN KEY (SAUSERPROFILEID)
                             REFERENCES SAUSERPROFILE  (
              SAUSERPROFILEID);


ALTER TABLE SAUSERPVG
       ADD CONSTRAINT R_33 FOREIGN KEY (SAUSERPROFILEID)
                             REFERENCES SAUSERPROFILE  (
              SAUSERPROFILEID)
                             ON DELETE CASCADE;



