-- ABS-67952 / ABS-73613

------------------ WEBUSER ------------------
ALTER TABLE WEBUSER ADD (PAYINVOICES NUMBER DEFAULT 0 NOT NULL
      CONSTRAINT PAYINVOICES_VALID1 CHECK (PAYINVOICES IN (0,1)));

COMMENT ON COLUMN WEBUSER.PAYINVOICES
  IS '0 = Not applicable or not allowed
1 = Pay invoices allowed
Default 0.
Option 1 is used in combination with the credit card interface.';

  
------------------ ARTRANSACTION ------------------
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 ';

ALTER TABLE ARTRANSACTION
  DROP CONSTRAINT ORIGIN_VALID1;
ALTER TABLE ARTRANSACTION
  ADD CONSTRAINT ORIGIN_VALID1 CHECK (ORIGIN IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13));
  

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

ALTER TABLE CASHAPPLICATION
  DROP CONSTRAINT ORIGIN_VALID3;
ALTER TABLE CASHAPPLICATION
  ADD CONSTRAINT ORIGIN_VALID3
  CHECK (ORIGIN IN (0, 1, 2, 3, 4, 5, 6));
  
------------------ REMITBATCH ------------------
ALTER TABLE REMITBATCH MODIFY (PAYMENTTYPE NUMBER(2));

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';

ALTER TABLE REMITBATCH
  DROP CONSTRAINT PAYMENTTYPE_VALID;
ALTER TABLE REMITBATCH
  ADD CONSTRAINT PAYMENTTYPE_VALID
  CHECK ((PAYMENTTYPE IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) ) OR (PAYMENTTYPE IS NULL));

------------------ CREDITCARDTRANSACTION ------------------
ALTER TABLE CREDITCARDTRANSACTION ADD (WEBUSER_ID NUMBER NULL);

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';
COMMENT ON COLUMN CREDITCARDTRANSACTION.WEBUSER_ID
  IS 'When transaction is done via the web portal the webuser is linked.';

CREATE INDEX XIF4CREDITCARDTRANSACTION ON CREDITCARDTRANSACTION (WEBUSER_ID ASC);

ALTER TABLE CREDITCARDTRANSACTION
  ADD CONSTRAINT CREDITCARDTRANSACTION_WEBUSER FOREIGN KEY (WEBUSER_ID)
  REFERENCES WEBUSER (WEBUSER_ID) ON DELETE SET NULL;

ALTER TABLE CREDITCARDTRANSACTION
  DROP CONSTRAINT ORIGIN_VALID2;
ALTER TABLE CREDITCARDTRANSACTION
  ADD CONSTRAINT ORIGIN_VALID2
  CHECK (ORIGIN IN (1, 2, 3, 4, 5, 6, 7));
