--ABS-85345 / ABS-90270
-----------------ARTRANSACTION------------------

COMMENT ON COLUMN ARTRANSACTION.PAYMENTTYPE
	IS 'DebitCredit = C
0 = Cash
1 = Check
2 = Credit card
3 = EFT
4 = EDI
5 = Acceptgiro
6 = Automatic collection
7 = Pay-by customer
8 = Credit Card Portal
9 = Not applicable
10 = ACH
DebitCredit = D
0 = COD
1 = Charge
9 = Not applicable';
	
COMMENT ON COLUMN ARTRANSACTION.ORIGIN
	IS 'This field is only used internally for trouble shooting. It identifies where the ARTransaction is created.
0 = Undefined
1 = Finalize invoices
2 = Consolidated invoices
3 = Settlement
4 = Generate statement
5 = Cash application – Manual added
6 = Cash application – Generated (apply)
7 = DB Procedure ProcessPaymentTransaction
8 = DB Procedure ProcessUnappliedCredit
9 = Credit card transactions – cancel / refund
10 = Process credit card transactions
11 = Credit Card Portal
12 = Customer Financials – Credit card on demand
13 = Web Portal Credit Card payment
14 = Scheduled Credit Card Payment
15 = Card Test transaction
16 = Scheduled ACH Payment';

ALTER TABLE ARTRANSACTION DROP CONSTRAINT CHK_ARTRANSACTION_ORIGIN;

ALTER TABLE ARTRANSACTION ADD CONSTRAINT CHK_ARTRANSACTION_ORIGIN CHECK (ORIGIN BETWEEN 0 AND 16);

-----------------CASHAPPLICATION------------
COMMENT ON COLUMN CASHAPPLICATION.ORIGIN
  IS '0 = Undefined (default)
1 = Cash application
2 = Settlement
3 = Credit card interface
4 = Conversion
5 = Credit Card Portal
6 = Web Portal (credit card payment)
7 = Credit Card Schedule
8 = ACH Schedule';

ALTER TABLE CASHAPPLICATION DROP CONSTRAINT ORIGIN_VALID3;

ALTER TABLE CASHAPPLICATION ADD CONSTRAINT CHK_CASHAPPLICATION_ORIGIN CHECK (ORIGIN BETWEEN 0 AND 8);

--------------------------CONTRACTCOMPONENTPAYMENT-------------------------
COMMENT ON COLUMN CONTRACTCOMPONENTPAYMENT.PAYMENTTYPE
	IS '1 = Check
2 = Credit Card
3 = EFT (Electronic File Transfer)
4 = EDI (Electronic Data Interchange)
5 = Accept Giro
6 = Automatic Collection
7 = Via Pay by Customer
8 = (Post)Bank
9 = Bacs
10 = Approval for payment (Billet a ordre) - France only.  No functionality, just for redisplay
11 = Letter of intent to pay (L.C.R. Lettre de Change Releve) - France only.  No functionality, Just for redisplay
12 = ACH (Automated Clearing House) - US Only';

ALTER TABLE CONTRACTCOMPONENTPAYMENT DROP CONSTRAINT CHK_CONTRCOMPPAYM_PAYMENTTYPE;

ALTER TABLE CONTRACTCOMPONENTPAYMENT ADD CONSTRAINT CHK_CONTRCOMPPAYM_PAYMENTTYPE CHECK (PAYMENTTYPE BETWEEN 1 AND 12);

------------CREDITCARDCHARGESCHEDULE----------------------
ALTER TABLE CREDITCARDCHARGESCHEDULE ADD (INVOICETYPETOINCLUDE NUMBER DEFAULT 0 NOT NULL 
	CONSTRAINT CHK_CCCSCH_INVTYPETOINCLUDE CHECK (INVOICETYPETOINCLUDE BETWEEN 0 AND 1));

COMMENT ON COLUMN CREDITCARDCHARGESCHEDULE.INVOICETYPETOINCLUDE
	IS '0 – All
1 – Only invoices that are attached to a Statement
';

DROP INDEX XAK1CREDITCARDCHARGESCHEDULE;

CREATE UNIQUE INDEX XAK1CREDITCARDCHARGESCHEDULE ON CREDITCARDCHARGESCHEDULE(TYPE ASC, DAYOFMONTH ASC, DAYOFWEEK ASC, DAYOFWEEKINMONTH ASC, INVOICETYPETOINCLUDE ASC);

-----------------CREDITCARDFIXEDPAYDATES--------------------------
ALTER TABLE CREDITCARDFIXEDPAYDATES ADD (PAYMENTTYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CCFIXEDPAYDATES_PAYTYPE CHECK (PAYMENTTYPE BETWEEN 0 AND 1));

COMMENT ON COLUMN CREDITCARDFIXEDPAYDATES.PAYMENTTYPE
	IS '0 = Credit Card (default)
1 = ACH (Automated Clearing House)
';

DROP INDEX XAK1CREDITCARDFIXEDPAYDATES;

CREATE UNIQUE INDEX XAK1CREDITCARDFIXEDPAYDATES ON CREDITCARDFIXEDPAYDATES(PLANT_ID ASC, PAYMENTDATE ASC, PAYMENTTYPE ASC);

-------------------------CREDITCARDTRANSACTION--------------------------
COMMENT ON COLUMN CREDITCARDTRANSACTION.ORIGIN
	IS '1 = Remittance (manual via Cash application)
2 = Refund
3 = Miscellaneous
4 = Generated via Statement
5 = Generated via Settlement
6 = Credit Card on Demand
7 = Web Portal Credit Card Payment
8 = Scheduled Electronic payment
9 = Card Test Transaction';

------------------------------------CUSTOMER----------------------------------------------
COMMENT ON COLUMN CUSTOMER.PAYMENTTYPE
	IS '0=Cash
1=Check
2=Credit Card
3=EFT
4=EDI
5=Accept Giro
6=Automatic Collection
7=Via Pay by Customer
8 = (Post)Bank
9 = Bacs
10 = Approval for payment (Billet a  ordre)
11 = Letter of intent to pay (L.C.R. Lettre de Change Relevé)
12 = ACH (Automated Clearing House) – US Only
';

----------------CUSTOMERACH-------------------
CREATE TABLE CUSTOMERACH (CUSTOMER_ID NUMBER NOT NULL,
			TOKEN VARCHAR2(19) NULL,
			ACCOUNTNUMBER VARCHAR2(114) NOT NULL,
			ROUTINGNUMBER VARCHAR2(54) NOT NULL,
			CREATEDDATE DATE NOT NULL,
			SYSTEMUSER_ID NUMBER NOT NULL,
			TIMESTAMP DATE NOT NULL);

COMMENT ON COLUMN CUSTOMERACH.ACCOUNTNUMBER
	IS 'field length is 19 but this should be stored as an encrypted field to be decrypted in the application.  Therefore length is multiplied by 6';

COMMENT ON COLUMN CUSTOMERACH.ROUTINGNUMBER
	IS 'field length is 9 but this should be stored as an encrypted field to be decrypted in the application.  Therefore length is multiplied by 6';

ALTER TABLE CUSTOMERACH ADD CONSTRAINT XPKCUSTOMERACH PRIMARY KEY(CUSTOMER_ID);

ALTER TABLE CUSTOMERACH ADD CONSTRAINT FK_CUSTOMERACH_CUSTOMERID FOREIGN KEY(CUSTOMER_ID)
REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE;

----------------------------REMITBATCH---------------------------------
COMMENT ON COLUMN REMITBATCH.PAYMENTTYPE
	IS '0 = Cash
1 = Check
2 = Credit card
3 = EFT
4 = EDI
5 = Acceptgiro
6 = Automatic collection
7 = Pay-by customer
8 = Credit Card Portal
9 = Not applicable
10 = Credit Card Web Portal
11 = Scheduled Credit Card Payment
12 = Scheduled ACH Payment
';

ALTER TABLE REMITBATCH DROP CONSTRAINT CHK_REMITBATCH_PAYMENT;

ALTER TABLE REMITBATCH ADD CONSTRAINT CHK_REMITBATCH_PAYMENTTYPE CHECK (PAYMENTTYPE BETWEEN 0 AND 12);

---------------------SYSTEMUSER----------------
ALTER TABLE SYSTEMUSER ADD (ACHAUTHORISED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_SYSTEMUSER_ACHAUTHORISED CHECK (ACHAUTHORISED IN ('Y','N')));

-------------------------PLANTPAYMENTCREDENTIALS---------------------------
CREATE TABLE PLANTPAYMENTCREDENTIALS(PLANT_ID NUMBER NOT NULL,
				     URL VARCHAR2(200) NOT NULL,
				     USERID VARCHAR2(240) NOT NULL,
				     PASSWORD VARCHAR2(240) NOT NULL,
				     MERCHANTID VARCHAR2(240) NOT NULL,
				     SYSTEMUSER_ID NUMBER NOT NULL,
				     TIMESTAMP DATE NOT NULL);

COMMENT ON COLUMN PLANTPAYMENTCREDENTIALS.USERID
	IS 'User ID for connection to 3rd party system
Encrypted database field
Encrypted via the application, can be decrypted.
Field length is 40, this must be enlarged with factor 6 in the database for encryption.
';

COMMENT ON COLUMN PLANTPAYMENTCREDENTIALS.PASSWORD
	IS 'Password for connection to 3rd party system
Encrypted database field
Encrypted via the application, can be decrypted.
Field length is 40, this must be enlarged with factor 6 in the database for encryption.
';

COMMENT ON COLUMN PLANTPAYMENTCREDENTIALS.MERCHANTID
	IS 'Merchant / Store ID for connection to 3rd party system
Encrypted database field
Encrypted via the application, can be decrypted.
Field length is 40, this must be enlarged with factor 6 in the database for encryption.
';

ALTER TABLE PLANTPAYMENTCREDENTIALS ADD CONSTRAINT XPKPLANTPAYMENTCREDENTIALS PRIMARY KEY(PLANT_ID);

ALTER TABLE PLANTPAYMENTCREDENTIALS ADD CONSTRAINT FK_PPAYMENTCREDENTIALS_PLANTID FOREIGN KEY(PLANT_ID)
REFERENCES PLANT(PLANT_ID);