-- ABS-52925
------------   ARTRANSACTIONSHADOW   --------------
CREATE SEQUENCE SEQ_ARTRANSACTIONSHADOW
START WITH 1
INCREMENT BY 1;

CREATE TABLE ARTRANSACTIONSHADOW
(
ARTRANSACTIONSHADOW_ID NUMBER NOT NULL,
BUSINESSUNIT_ID NUMBER NOT NULL,
BILLTOCUSTOMER_LINK_ID NUMBER NOT NULL,
DELTOCUSTOMER_LINK_ID NUMBER NULL,
ARTRANSACTIONDATE DATE NOT NULL,
DEBITCREDIT VARCHAR2(1) NOT NULL CONSTRAINT DEBITCREDIT_VALID1_1 CHECK (DEBITCREDIT IN ('D','C')),
ARTRANSACTIONTYPE_ID NUMBER NOT NULL,
CURRENCY_ID NUMBER NOT NULL,
AMOUNT NUMBER(15,6) NOT NULL,
ROUTE_ID NUMBER NULL,
PAYMENTTYPE NUMBER NOT NULL,
REMITIDENTIFICATION VARCHAR2(16) NULL,
TIMESTAMP DATE NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
EXPORTTIMESTAMP DATE NULL,
EXPORTED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD384 CHECK (EXPORTED IN ('Y','N'))
);

COMMENT ON COLUMN ARTRANSACTIONSHADOW.DEBITCREDIT IS 'C=Credit (payment), 
D=Debit (charge)';

COMMENT ON COLUMN ARTRANSACTIONSHADOW.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
DebitCredit = D
0 = COD
1 = Charge
9 = Not applicable';

COMMENT ON COLUMN ARTRANSACTIONSHADOW.REMITIDENTIFICATION IS 'Checknr,Creditcardnr etc. Only filled when DebitCredit = C';

ALTER TABLE ARTRANSACTIONSHADOW ADD CONSTRAINT XPKARTRANSACTIONSHADOW PRIMARY KEY (ARTRANSACTIONSHADOW_ID);

CREATE UNIQUE INDEX XAK1ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (BUSINESSUNIT_ID ASC, BILLTOCUSTOMER_LINK_ID ASC,
ARTRANSACTIONDATE ASC, DEBITCREDIT ASC, ARTRANSACTIONTYPE_ID ASC, REMITIDENTIFICATION ASC);

CREATE INDEX XIE1ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (ARTRANSACTIONDATE ASC);
CREATE INDEX XIE2ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (EXPORTED ASC);

CREATE INDEX XIF1ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (BILLTOCUSTOMER_LINK_ID ASC);
CREATE INDEX XIF3ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (DELTOCUSTOMER_LINK_ID ASC);
CREATE INDEX XIF4ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (ARTRANSACTIONTYPE_ID ASC);
CREATE INDEX XIF5ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (CURRENCY_ID ASC);
CREATE INDEX XIF6ARTRANSACTIONSHADOW ON ARTRANSACTIONSHADOW (ROUTE_ID ASC);

ALTER TABLE ARTRANSACTIONSHADOW ADD CONSTRAINT ARTRANSACTIONSHADOW_BUID FOREIGN KEY (BUSINESSUNIT_ID)
REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

ALTER TABLE ARTRANSACTIONSHADOW ADD CONSTRAINT ARTRANSACTIONSHADOW_BCUSTID FOREIGN KEY (BILLTOCUSTOMER_LINK_ID)
REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

ALTER TABLE ARTRANSACTIONSHADOW ADD CONSTRAINT ARTRANSACTIONSHADOW_DCUSTID FOREIGN KEY (DELTOCUSTOMER_LINK_ID)
REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

ALTER TABLE ARTRANSACTIONSHADOW ADD CONSTRAINT ARTRANSACTIONSHADOW_ARTRANS FOREIGN KEY (ARTRANSACTIONTYPE_ID)
REFERENCES ARTRANSACTIONTYPE (ARTRANSACTIONTYPE_ID);

ALTER TABLE ARTRANSACTIONSHADOW ADD CONSTRAINT ARTRANSACTIONSHADOW_CURRID FOREIGN KEY (CURRENCY_ID)
REFERENCES CURRENCY (CURRENCY_ID);

ALTER TABLE ARTRANSACTIONSHADOW ADD CONSTRAINT ARTRANSACTIONSHADOW_RID FOREIGN KEY (ROUTE_ID)
REFERENCES ROUTE (ROUTE_ID);

ALTER TABLE ARTRANSACTIONSHADOW ADD CONSTRAINT ARTRANSACTIONSHADOW_CHECK CHECK ((DEBITCREDIT='C' 
AND PAYMENTTYPE IN (0,1,2,3,4,5,6,7,8,9)) OR (DEBITCREDIT='D' AND PAYMENTTYPE IN (0,1,9)));


------------   CASHAPPLICATIONSHADOW   --------------
CREATE SEQUENCE SEQ_CASHAPPLICATIONSHADOW
START WITH 1
INCREMENT BY 1;

CREATE TABLE CASHAPPLICATIONSHADOW
(
CASHAPPLICATIONSHADOW_ID NUMBER NOT NULL,
DEBITARTRANSACTION_ID NUMBER NOT NULL,
CREDITARTRANSACTIONSHADOW_ID NUMBER NOT NULL,
APPLYDATE DATE NOT NULL,
APPLYTIMESTAMP DATE NOT NULL,
APPLYSYSTEMUSER_ID NUMBER NOT NULL,
AMOUNT NUMBER(15,6) NOT NULL,
EXPORTTIMESTAMP DATE NULL,
EXPORTED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD385 CHECK (EXPORTED IN ('Y','N'))
);

COMMENT ON COLUMN CASHAPPLICATIONSHADOW.DEBITARTRANSACTION_ID IS 'Must be ARTransaction of type Debit (DebitCredit = D)';

COMMENT ON COLUMN CASHAPPLICATIONSHADOW.CREDITARTRANSACTIONSHADOW_ID IS 'Must be ARTransaction of type Credit (DebitCredit = C)';

ALTER TABLE CASHAPPLICATIONSHADOW ADD CONSTRAINT XPKCASHAPPLICATIONSHADOW PRIMARY KEY (CASHAPPLICATIONSHADOW_ID);

CREATE INDEX XIE1CASHAPPLICATIONSHADOW ON CASHAPPLICATIONSHADOW (APPLYDATE ASC);
CREATE INDEX XIE2CASHAPPLICATIONSHADOW ON CASHAPPLICATIONSHADOW (EXPORTED ASC);
CREATE INDEX XIF1CASHAPPLICATIONSHADOW ON CASHAPPLICATIONSHADOW (DEBITARTRANSACTION_ID ASC);
CREATE INDEX XIF2CASHAPPLICATIONSHADOW ON CASHAPPLICATIONSHADOW (CREDITARTRANSACTIONSHADOW_ID ASC);

ALTER TABLE CASHAPPLICATIONSHADOW ADD CONSTRAINT CASHAPPLICATIONSHADOW_DART FOREIGN KEY (DEBITARTRANSACTION_ID)
REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;

ALTER TABLE CASHAPPLICATIONSHADOW ADD CONSTRAINT CASHAPPLICATIONSHADOW_CARTS FOREIGN KEY (CREDITARTRANSACTIONSHADOW_ID)
REFERENCES ARTRANSACTIONSHADOW (ARTRANSACTIONSHADOW_ID) ON DELETE CASCADE;

------------   ROUTETRANSACTIONTYPE   --------------
INSERT INTO ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID,FACTORYDESCRIPTION,ALLOWMANUALAPPROVAL_YN,APPROVEMANUALLY_YN, ROUTETRANSACTIONCATEGORY_ID, SYSTEMUSER_ID,TIMESTAMP) 
VALUES (750, 'Email Statement','N','N', 0, GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID,FACTORYDESCRIPTION,ALLOWMANUALAPPROVAL_YN,APPROVEMANUALLY_YN, ROUTETRANSACTIONCATEGORY_ID, SYSTEMUSER_ID,TIMESTAMP) 
VALUES (751, 'Email Invoice','N','N', 0, GETMINSYSTEMUSER(),SYSDATE);
COMMIT;

INSERT INTO ROUTETRANSACTIONTYPE(ROUTETRANSACTIONTYPE_ID,FACTORYDESCRIPTION,ALLOWMANUALAPPROVAL_YN,APPROVEMANUALLY_YN, ROUTETRANSACTIONCATEGORY_ID, SYSTEMUSER_ID,TIMESTAMP) 
VALUES (752, 'Apply Payment','N','N', 0, GETMINSYSTEMUSER(),SYSDATE);
COMMIT;