--ABSN-1170
CREATE TABLE ERP_I_CLIENT
( TRANSFER_ID               NUMBER NOT NULL,
  TRANSFER_STATUS           NUMBER(1) DEFAULT 0 NOT NULL,
  CUSTOMERNUMBER            NUMBER NOT NULL,
  DEPARTMENTNUMBER          NUMBER(6) NOT NULL,
  CONSUMPTIONPOINTNUMBER    NUMBER NOT NULL,
  ROOMNUMBER                VARCHAR2(12) NOT NULL,
  CLIENTNUMBER              NUMBER(6) NULL,  --will be generated when left blank, when blank its post=Y
  NAME                      VARCHAR2(40) NOT NULL,
  ADMINNUMBER               VARCHAR2(20),
  SEARCHNAME                VARCHAR2(20),
  LABELNAME                 VARCHAR2(40),
  LEGACYCODE                VARCHAR2(12),
  FIRSTNAME                 VARCHAR2(40),
  GENDER                    NUMBER DEFAULT 0,
  EMAIL                     VARCHAR2(300),
  PHONENUMBER               VARCHAR2(30),
  MOBILEPHONE               VARCHAR2(30),
  FLAGCODE                  VARCHAR2(6),
  FLAGSTARTDATE             DATE,
  REMARK                    VARCHAR2(80),
  DATEACTIVE                DATE,
  DATEINACTIVE              DATE DEFAULT TO_DATE('31-12-2099','DD-MM-YYYY'),
  PRESORTNUMBER             NUMBER,
  PUBLICSERVICENUMBER       VARCHAR2(20),
  ONETIMECHARGED            NUMBER,
  BUDGETCLIENT              NUMBER DEFAULT 0,
  ALLOWONETIMECHARGE        NUMBER,  
  CLIENTTYPE                NUMBER DEFAULT 0,
  ALLOWREPAIR               NUMBER DEFAULT 0,
  BIRTHDATE                 DATE,
  DATEOUTOFSERVICE          DATE, --DECEASEDDATE
  CLIENTCATEGORYCODE        VARCHAR2(6),
  CLIENTINVOICECATEGORYCODE VARCHAR2(6),
  INVOICETO                 NUMBER DEFAULT 0,
  SENDINVOICETO             NUMBER DEFAULT 0,
  BANKACCOUNTNUMBER         VARCHAR2(30),
  IBANNUMBER                VARCHAR2(20),
  PAYMENTTYPE               NUMBER DEFAULT 0,
  SELFPAYMENT               NUMBER DEFAULT 0,
  SENDINVOICEBY             NUMBER DEFAULT 0,
  LAUNDRYSERVICESPARTICIPANT VARCHAR2(1),
  INSURANCECOMPANY          NUMBER, --CLIENTBILLINGINFO
  INSURANCEPOLICYNUMBER     VARCHAR2(25), --CLIENTBILLINGINFO
  BANKINSTITUTION_IDCODE    VARCHAR2(30), --CLIENTBILLINGINFO
  LAUNDRYBANKINSTITUTION    VARCHAR2(30), --CLIENTBILLINGINFO
  INVOICECAPPEDEXCESSAMOUNTS NUMBER(1), --CLIENTBILLINGINFO
  DEBTORNUMBER              VARCHAR2(12), --CLIENTBILLINGINFO
  NOFREEOFCHARGELABEL       NUMBER(4), --CLIENTBILLINGINFO, default 0
  TIMESTAMP                 DATE DEFAULT SYSDATE,
  POST_YN                   VARCHAR2(1)
);
COMMENT ON COLUMN ERP_I_CLIENT.TRANSFER_STATUS IS '0=ready to be read,
1=read correctly';
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT PK_ERP_CLIENT PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX1_ERP_I_CLIENT ON ERP_I_CLIENT(CUSTOMERNUMBER, DEPARTMENTNUMBER);
CREATE INDEX INDX2_ERP_I_CLIENT ON ERP_I_CLIENT(CUSTOMERNUMBER, CLIENTNUMBER);
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_BUDGETCLIENT CHECK (BUDGETCLIENT IN (0,1,2));
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_PAYMENTTYPE CHECK (PAYMENTTYPE IN (0,1,2,3,4,5,6));
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_DATECHECK CHECK (DATEINACTIVE >= DATEACTIVE);
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_FLAGCODE CHECK (FLAGCODE = UPPER(LTRIM(RTRIM(FLAGCODE))));
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_GENDERCHECK CHECK (GENDER IN (0,1,2));
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_INVOICETO CHECK (INVOICETO IN (0,1,2));
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_SELFPAYMENT CHECK (SELFPAYMENT IN (0,1,2));
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_SENDINVOICETO CHECK (SENDINVOICETO IN (0,1,2));
ALTER TABLE ERP_I_CLIENT ADD CONSTRAINT CHK_ERP_I_CLIENT_INVAPPEDEXCESAMNTS CHECK (INVOICECAPPEDEXCESSAMOUNTS IN (0,1));
COMMENT ON COLUMN ERP_I_CLIENT.CUSTOMERNUMBER IS 'Must exist';
COMMENT ON COLUMN ERP_I_CLIENT.CLIENTNUMBER IS 'Must be unique per customer';
COMMENT ON COLUMN ERP_I_CLIENT.DEPARTMENTNUMBER IS 'Must exist as a department for the customer';
COMMENT ON COLUMN ERP_I_CLIENT.CONSUMPTIONPOINTNUMBER IS 'Obligated for private person';
COMMENT ON COLUMN ERP_I_CLIENT.ROOMNUMBER IS 'Obligated for residents, when not known fill ''-''';
COMMENT ON COLUMN ERP_I_CLIENT.GENDER IS '0 = Unknown or not applicable, 1 = Male, 2 = Female';
COMMENT ON COLUMN ERP_I_CLIENT.PRESORTNUMBER IS 'Presort group number. Only applicable if customer is set to using presorting (if not store 0).';
COMMENT ON COLUMN ERP_I_CLIENT.DATEOUTOFSERVICE IS 'Do not fill when client active, but leave empty!';
COMMENT ON COLUMN ERP_I_CLIENT.INVOICETO IS '0 = All to customer, 1 = All to client, 2 = Split';
COMMENT ON COLUMN ERP_I_CLIENT.SENDINVOICEBY IS '0 = (Regular) mail 1 = Email';
COMMENT ON COLUMN ERP_I_CLIENT.SENDINVOICETO IS '0 = Customer, 1 = Client contact person, 2 = Client';
COMMENT ON COLUMN ERP_I_CLIENT.PAYMENTTYPE IS '0 = Cash /Not applicable
1 = Check
2 = Credit Card
3 = EFT
4 = EDI
5 = Accept Giro
6 = Automatic Collection
Default 0 (paid via Customer) ';
COMMENT ON COLUMN ERP_I_CLIENT.BUDGETCLIENT IS 'This attribute is only used for module TEXTILECONTROLLING
  0 = Client is not using budget and no self payer
  1 = Client is not using budget and is self payer
  2 = Client is using budget';
COMMENT ON COLUMN ERP_I_CLIENT.SELFPAYMENT IS '0=Inherit from PLPC(Default), 1 = Customer Pays, 2 = Client pays';
COMMENT ON COLUMN ERP_I_CLIENT.PUBLICSERVICENUMBER IS 'In some countries each person has a unique number that is provided by the authorities. In Dutch BSN (Burger Service Nummer) .';
COMMENT ON COLUMN ERP_I_CLIENT.ONETIMECHARGED IS '0 = Not yet charged, 1 = Not charged, skipped, 2 = Charged';
COMMENT ON COLUMN ERP_I_CLIENT.ALLOWONETIMECHARGE IS '0 = Not applicable / One time charge not allowed, 1 = One time charge allowed.';
COMMENT ON COLUMN ERP_I_CLIENT.CLIENTTYPE IS '0 = Normal Client, 1 = Unknown Client (client for garments for which it is unknown to who they belong), 2 = Pool Client per Department 3 = Pool Client per Customer';
COMMENT ON COLUMN ERP_I_CLIENT.ALLOWREPAIR IS '0 = Not applicable / Defined on Customer, 1 = Repair Allowed';
COMMENT ON COLUMN ERP_I_CLIENT.INVOICECAPPEDEXCESSAMOUNTS IS  '0 = Not applicable / To customer, 1 = To insurance company';

CREATE TABLE ERP_I_CLIENTCONTACTPERSON
(
  TRANSFER_ID            NUMBER NOT NULL,
  TRANSFER_STATUS        NUMBER DEFAULT 0 NOT NULL,
  CONTACTPERSONNUMBER    NUMBER NOT NULL,
  NAME                   VARCHAR2(40) NOT NULL,
  PHONE                  VARCHAR2(30),
  MOBILEPHONE            VARCHAR2(30),
  FAX                    VARCHAR2(30),
  EMAIL                  VARCHAR2(300),
  INVOICECONTACTPERSON   VARCHAR2(1), --Default N
  STREET                 VARCHAR2(30),
  STREET2                VARCHAR2(30),  
  ZIPCODE                VARCHAR2(15),
  CITY                   VARCHAR2(30),
  STATE                  VARCHAR2(20),
  COUNTY                 VARCHAR2(20),
  CONTACTTYPE            NUMBER, --Default 0
  SENDINVOICEBY          NUMBER, --Default 0
  TIMESTAMP              DATE DEFAULT SYSDATE NOT NULL
);
ALTER TABLE ERP_I_CLIENTCONTACTPERSON ADD CONSTRAINT PK_ERP_I_CLIENTCONTACTPERSON PRIMARY KEY (TRANSFER_ID, CONTACTPERSONNUMBER);
ALTER TABLE ERP_I_CLIENTCONTACTPERSON ADD CONSTRAINT FK_ERP_I_CLIENTCONTACTPERSON FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CLIENT (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_I_CLIENTCONTACTPERSON ADD CONSTRAINT CHK_ERP_I_CLNTCNTPERSON_STATUS CHECK (TRANSFER_STATUS IN (-1,0,1));
ALTER TABLE ERP_I_CLIENTCONTACTPERSON ADD CONSTRAINT CHK_ERP_I_CLNTCNTPERSON_SENDINVOICEBY CHECK (SENDINVOICEBY IN (0, 1));
ALTER TABLE ERP_I_CLIENTCONTACTPERSON ADD CONSTRAINT CHK_ERP_I_CLNTCNTPERSON_INVCONTACTPERSON CHECK (INVOICECONTACTPERSON IN ('Y', 'N'));
COMMENT ON COLUMN ERP_I_CLIENTCONTACTPERSON.TRANSFER_STATUS IS '0=ready to be read,
1=read correctly';
COMMENT ON COLUMN ERP_I_CLIENTCONTACTPERSON.CONTACTTYPE IS '0 = Other,
1 = Invoice Receiver,
2 = Family,
3 = Insurrance company';
COMMENT ON COLUMN ERP_I_CLIENTCONTACTPERSON.SENDINVOICEBY IS '0 = (Regular) mail,
1 = Email';

CREATE TABLE ERP_I_CLIENT_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_CLIENT_LOG.TRANSFER_STATUS IS '0=ready to be read,
1=read correctly';
COMMENT ON COLUMN ERP_I_CLIENT_LOG.PROCESS_STATUS IS '0=processed correctly, 
1=process stopped with errors';
ALTER TABLE ERP_I_CLIENT_LOG ADD CONSTRAINT PK_ERP_I_CLIENT_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_CLIENT_LOG ADD CONSTRAINT FK_ERP_I_CLIENT_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_I_CLIENT (TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_I_CLIENT_LOG ADD CONSTRAINT CHK_ERP_I_CLIENT_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1));
