-- ABS-62304 / ABS-85384

---------- 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
14 = Scheduled Credit Card Payment';
ALTER TABLE ARTRANSACTION DROP CONSTRAINT ORIGIN_VALID1;
ALTER TABLE ARTRANSACTION ADD CONSTRAINT CHK_ARTRANSACTION_ORIGIN CHECK(ORIGIN IN(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14));


-------------- 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 credit card payment';
ALTER TABLE CREDITCARDTRANSACTION DROP CONSTRAINT ORIGIN_VALID2;
ALTER TABLE CREDITCARDTRANSACTION ADD CONSTRAINT CHK_CREDITCARDTRANS_ORIGIN CHECK(ORIGIN IN(1,2,3,4,5,6,7,8));

---------------- CREDITCARDCHARGESCHEDULE -----------------
CREATE SEQUENCE SEQ_CREDITCARDCHARGESCHEDULE 
START WITH 1
INCREMENT BY 1;

CREATE TABLE CREDITCARDCHARGESCHEDULE (CREDITCARDCHARGESCHEDULE_ID NUMBER NOT NULL,
                                       TYPE NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_CCCHARGESCHEDULE_TYPE CHECK(TYPE IN(1,2,3,4)),
                                       DAYOFMONTH NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CCCHARGESCHEDULE_DAYOFMO CHECK(DAYOFMONTH BETWEEN 0 AND 28),
                                       DAYOFWEEK NUMBER DEFAULT 0 NOT NULL CONSTRAINT  CHK_CCCHARGESCHEDULE_DAYOFWEEK CHECK(DAYOFWEEK BETWEEN 0 AND 7),
                                       DAYOFWEEKINMONTH NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CCCHARGESCHEDULE_DAYOFWINM CHECK(DAYOFWEEKINMONTH BETWEEN 0 AND 4),
                                       SYSTEMUSER_ID NUMBER NOT NULL,
                                       TIMESTAMP DATE NOT NULL);
COMMENT ON COLUMN CREDITCARDCHARGESCHEDULE.TYPE
IS '1 = Day of Month
2 = Last day of Month
3 = Day of week within month
4 = Day of week';

ALTER TABLE CREDITCARDCHARGESCHEDULE ADD CONSTRAINT PKCREDITCARDCHARGESCHEDULE PRIMARY KEY(CREDITCARDCHARGESCHEDULE_ID);

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

------------ CONTRACTCOMPONENTPAYMENT -----------
ALTER TABLE CONTRACTCOMPONENTPAYMENT ADD (PAYMENTMETHOD NUMBER NULL CONSTRAINT CHK_CONTRCOMPPAYM_PAYMENTMEHOD CHECK(PAYMENTMETHOD IN(1,2)),
                                          PAYMENTTYPE NUMBER NULL CONSTRAINT CHK_CONTRCOMPPAYM_PAYMENTTYPE CHECK(PAYMENTTYPE IN(1,2,3,4,5,6,7,8,9,10,11)),
                                          GENERATEPAYMENT NUMBER NULL CONSTRAINT CHK_CONTRCOMPPAYM_GENERATEPAYM CHECK(GENERATEPAYMENT IN(0,1,2)));
COMMENT ON COLUMN CONTRACTCOMPONENTPAYMENT.PAYMENTMETHOD
IS '1 = Cash on Delivery
2 = Charge';

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

COMMENT ON COLUMN CONTRACTCOMPONENTPAYMENT.GENERATEPAYMENT
IS '0 = Manually generate and apply payment
1 = Generate payment on statement or settlement, apply when authorized
2 = Automatic on a schedule - only visible if CREDITCARDCHARGESCHEDULE is in the license';

------------- CONTRACTCOMPONENTCCCHARGESCH --------------------
CREATE TABLE CONTRACTCOMPONENTCCCHARGESCH (CONTRACT_ID NUMBER NOT NULL,
                                           CREDITCARDCHARGESCHEDULE_ID NUMBER NOT NULL,
                                           SYSTEMUSER_ID NUMBER NOT NULL,
                                           TIMESTAMP DATE NOT NULL);
                                           
ALTER TABLE CONTRACTCOMPONENTCCCHARGESCH ADD CONSTRAINT XPKCONTRACTCOMPONENTCCCHARGESC PRIMARY KEY(CONTRACT_ID);

CREATE INDEX XIF1CONTRACTCOMPONENTCCCHARGES ON CONTRACTCOMPONENTCCCHARGESCH(CREDITCARDCHARGESCHEDULE_ID ASC);

ALTER TABLE CONTRACTCOMPONENTCCCHARGESCH ADD CONSTRAINT FK_CONTRCOMPCCCHARGE_CONTRACT FOREIGN KEY(CONTRACT_ID)
REFERENCES CONTRACT(CONTRACT_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTCOMPONENTCCCHARGESCH ADD CONSTRAINT FK_CONTRCOMPCCCHARGE_CCCHARGES FOREIGN KEY(CREDITCARDCHARGESCHEDULE_ID)
REFERENCES CREDITCARDCHARGESCHEDULE(CREDITCARDCHARGESCHEDULE_ID);

------------- CUSTOMERBILLINGINFO --------------
COMMENT ON COLUMN CUSTOMERBILLINGINFO.GENERATEPAYMENT
IS '0 = Payment for statement or invoice must be created manually via the cash application.
1 = Payment is generated when the statement is generated or the invoice is settled if the customer is a credit card customer. The payment is not yet applied.
2 = Payment is taken automatically via a scheduled process on a predefined frequency.
Default 0';

ALTER TABLE CUSTOMERBILLINGINFO DROP CONSTRAINT GENERATEPAYMENT_VALID1;
ALTER TABLE CUSTOMERBILLINGINFO ADD CONSTRAINT CUSTOMERBILLINGINFO_GENERATEPA CHECK(GENERATEPAYMENT IN(0,1,2));

---------------- CUSTOMERCCCHARGESCHEDULE ------------
CREATE TABLE CUSTOMERCCCHARGESCHEDULE (CUSTOMER_ID NUMBER NOT NULL,
                                       CREDITCARDCHARGESCHEDULE_ID NUMBER NOT NULL,
                                       SYSTEMUSER_ID NUMBER NOT NULL,
                                       TIMESTAMP DATE NOT NULL);
                                       
ALTER TABLE CUSTOMERCCCHARGESCHEDULE ADD CONSTRAINT XPKCUSTOMERCCCHARGESCHEDULE PRIMARY KEY(CUSTOMER_ID);

CREATE INDEX XIF1CUSTOMERCCCHARGESCHEDULE ON CUSTOMERCCCHARGESCHEDULE(CREDITCARDCHARGESCHEDULE_ID ASC);

ALTER TABLE CUSTOMERCCCHARGESCHEDULE ADD CONSTRAINT FK_CUSTCCCHARGESCH_CUSTOMER_ID FOREIGN KEY(CUSTOMER_ID)
REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE CUSTOMERCCCHARGESCHEDULE ADD CONSTRAINT FK_CUSTCCCHARGESCH_CCCHARGESCH FOREIGN KEY(CREDITCARDCHARGESCHEDULE_ID)
REFERENCES CREDITCARDCHARGESCHEDULE(CREDITCARDCHARGESCHEDULE_ID);