-- ABS-140251 Automatic Closeout of Over & Under Payments / ABS-149332

-- create table SmallValueWriteOffControl

CREATE TABLE SMALLVALUEWRITEOFFCONTROL
(
       ARTRANSACTIONTYPECODE VARCHAR2(3) NOT NULL,
       PAYMENTTYPE VARCHAR2(10) NOT NULL,
       PAYMENTREFERENCE VARCHAR2(50) NOT NULL,
       WRITEOFFUNASSIGNED VARCHAR2(1) DEFAULT 'N' NOT NULL
);

-- indexes, constraints/checks

CREATE UNIQUE INDEX XAK1SMALLVALUEWRITEOFFCONTROL ON SMALLVALUEWRITEOFFCONTROL(ARTRANSACTIONTYPECODE, PAYMENTTYPE, PAYMENTREFERENCE);

ALTER TABLE SMALLVALUEWRITEOFFCONTROL
ADD CONSTRAINT YN_WRITEOFFUNASSIGNED_VALIDATION1
CHECK (WRITEOFFUNASSIGNED IN ('Y', 'N'));

-- RemitBatch table
ALTER TABLE RemitBatch
   DROP CONSTRAINT CHK_REMITBATCH_PAYMENTTYPE;

ALTER TABLE RemitBatch
   ADD CONSTRAINT CHK_REMITBATCH_PAYMENTTYPE
  CHECK (PAYMENTTYPE BETWEEN 0 AND 15);

-- ARTRANSACTION table
ALTER TABLE ARTRANSACTION
   DROP CONSTRAINT CHK_ARTRANSACTION_ORIGIN;

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

-- adding comments on columns
COMMENT ON COLUMN SMALLVALUEWRITEOFFCONTROL.WRITEOFFUNASSIGNED
  IS  'YN default N
       Defines if the records need to already be assigned
       (so invoice part paid or payment part applied) before it can be written off';
       
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
       11 = Scheduled Credit Card Payment
       12 = Scheduled ACH Payment
       13 = ACH Payment
       14 = Small Value Write Off
       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
       17 = Route Assistant from Mark other invoices
       18 = Electronic Payment Dialog
       19 = Web Portal ACH Payment
       20 = Small Value write off';
 
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
       13 = ACH Payment
       14 = Small value invoice write off
       15 = Small value payment write off';
