--Order 20010790

ALTER TABLE INVOICEHEADER
	ADD (EXPORTPAYMENTSTIMESTAMP DATE NULL);


COMMENT ON COLUMN INVOICEHEADER.EXPORTPAYMENTSTIMESTAMP IS 'Export timestamp for payments interface. Null is not exported.';

ALTER TABLE ARTRANSACTION
	ADD (ORIGIN NUMBER(2)  DEFAULT 0 NOT NULL CONSTRAINT Origin_valid1 CHECK (ORIGIN IN (0, 1, 2, 3, 4, 5, 6, 7, 8)));


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

CREATE SEQUENCE SEQ_PAYMENTTRANSACTION
START WITH 1
INCREMENT BY 1;

CREATE TABLE PAYMENTTRANSACTION
(
	PAYMENTTRANSACTION_ID NUMBER NOT NULL ,
	ORIGIN               VARCHAR2(30) NULL ,
	LOCKBOXNUMBER        NUMBER NULL ,
	PAYMENTDATE          DATE NOT NULL ,
	BATCHNUMBER          NUMBER NULL ,
	AMOUNT               NUMBER NOT NULL ,
	CURRENCY_ID          NUMBER NULL ,
	MICRNUMBER           VARCHAR2(20) NULL ,
	CUSTOMER_LINK_ID     NUMBER NULL ,
	BUSINESSUNIT_ID      NUMBER NULL ,
	REFERENCE            VARCHAR2(50) NOT NULL ,
	PAYMENTTYPE          NUMBER DEFAULT  1  NOT NULL 
			CONSTRAINT  PaymentType_valid1 CHECK (PAYMENTTYPE IN (0, 1, 2, 3, 4, 						5, 6, 7, 9)),
	STATUS               NUMBER(1) NOT NULL 
			CONSTRAINT Status_valid1 CHECK (STATUS IN (0, 1, 2, 3, 4)),
	MESSAGENUMBER        NUMBER(2) NOT NULL 
			CONSTRAINT  MessageNumber_valid1 CHECK (MESSAGENUMBER IN (0, 1, 2, 3, 4)),
	IMPORTTIMESTAMP      DATE NOT NULL ,
	PROCESSINGTIMESTAMP  DATE NULL ,
	PROCESSINGUSER_ID    NUMBER NULL ,
	ARTRANSACTION_ID     NUMBER NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE PAYMENTTRANSACTION
ADD CONSTRAINT  XPKPAYMENTTRANSACTION PRIMARY KEY (PAYMENTTRANSACTION_ID)
USING INDEX TABLESPACE INDX;


COMMENT ON COLUMN PAYMENTTRANSACTION.PAYMENTTYPE IS '0 = Cash
1 = Check
2 = Credit card
3 = EFT
4 = EDI
5 = Acceptgiro
6 = Automatic collection
7 = Pay-by customer
9 = Not applicable / unknown';


COMMENT ON COLUMN PAYMENTTRANSACTION.STATUS IS '0 = New / Ready for processing
1 = Corrected / Ready for processing
2 = Error / Unable to process
3 = Automatically processed
4 = Manually processed';



COMMENT ON COLUMN PAYMENTTRANSACTION.MESSAGENUMBER IS '0 = OK / No error
1 = Customer not found / defined
2 = Customer number and MICR number don’t match
3 = Details error
4 = Sum of detail amounts is more than payment';



CREATE UNIQUE INDEX XAK1PAYMENTTRANSACTION ON PAYMENTTRANSACTION
(
	PAYMENTDATE   ASC,
	BUSINESSUNIT_ID   ASC,
	CUSTOMER_LINK_ID   ASC,
	REFERENCE   ASC
)
TABLESPACE INDX;



CREATE  INDEX XIE1PAYMENTTRANSACTION ON PAYMENTTRANSACTION
(
	BUSINESSUNIT_ID   ASC,
	STATUS   ASC
)
TABLESPACE INDX;



CREATE  INDEX XIF1PAYMENTTRANSACTION ON PAYMENTTRANSACTION
(CURRENCY_ID   ASC)
TABLESPACE INDX;



CREATE  INDEX XIF2PAYMENTTRANSACTION ON PAYMENTTRANSACTION
(CUSTOMER_LINK_ID   ASC)
TABLESPACE INDX;



CREATE  INDEX XIF3PAYMENTTRANSACTION ON PAYMENTTRANSACTION
(BUSINESSUNIT_ID   ASC)
TABLESPACE INDX;



CREATE  INDEX XIF4PAYMENTTRANSACTION ON PAYMENTTRANSACTION
(ARTRANSACTION_ID   ASC)
TABLESPACE INDX;


CREATE SEQUENCE SEQ_PAYMENTTRANSACTIONDETAIL
START WITH 1
INCREMENT BY 1;


CREATE TABLE PAYMENTTRANSACTIONDETAIL
(
	PAYMENTTRANSACTIONDETAIL_ID NUMBER NOT NULL ,
	PAYMENTTRANSACTION_ID NUMBER NOT NULL ,
	DETAILREFERENCE      VARCHAR2(20) NOT NULL ,
	INVOICENUMBER	     NUMBER DEFAULT 0 NOT NULL,
	ARTRANSACTION_ID     NUMBER NULL ,
	INVOICEHEADER_ID     NUMBER NULL,
	GROSSAMOUNT          NUMBER NOT NULL ,
	DISCOUNTAMOUNT       NUMBER NOT NULL ,
	MESSAGENUMBER        NUMBER(2) NOT NULL
			CONSTRAINT  MessageNumber_valid2 CHECK (MESSAGENUMBER IN (0, 1, 10, 11, 12, 13)) ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE PAYMENTTRANSACTIONDETAIL
ADD CONSTRAINT  XPKPAYMENTTRANSACTIONDETAIL PRIMARY KEY (PAYMENTTRANSACTIONDETAIL_ID)
USING INDEX TABLESPACE INDX;


COMMENT ON COLUMN PAYMENTTRANSACTIONDETAIL.MESSAGENUMBER IS '0 = OK / No error
1 = ERROR: Debtor or pay-by customer from invoice does not match with payment customer (note: MessageNumber in header should be set to 3).
10 = WARNING: Invoicenumber not found
11 = WARNING: Invoice is already (partly) paid
12 = WARNING: Gross amount does not match invoice amount
13 = WARNING: Discount amount does not match discount from invoice';


CREATE UNIQUE INDEX XAK1PAYMENTTRANSACTIONDETAIL ON PAYMENTTRANSACTIONDETAIL
(
	PAYMENTTRANSACTION_ID   ASC,
	DETAILREFERENCE   ASC
)
TABLESPACE INDX;



CREATE  INDEX XIF1PAYMENTTRANSACTIONDETAIL ON PAYMENTTRANSACTIONDETAIL
(PAYMENTTRANSACTION_ID   ASC)
TABLESPACE INDX;



CREATE  INDEX XIF2PAYMENTTRANSACTIONDETAIL ON PAYMENTTRANSACTIONDETAIL
(ARTRANSACTION_ID   ASC)
TABLESPACE INDX;


CREATE  INDEX XIF3PAYMENTTRANSACTIONDETAIL ON PAYMENTTRANSACTIONDETAIL
(INVOICEHEADER_ID   ASC)
TABLESPACE INDX;


ALTER TABLE PAYMENTTRANSACTION
ADD CONSTRAINT PAYMENTTRANSACTION_CURRENCY FOREIGN KEY (CURRENCY_ID) REFERENCES CURRENCY (CURRENCY_ID) ;


ALTER TABLE PAYMENTTRANSACTION
ADD CONSTRAINT PAYMENTTRANSACTION_CUSTOMER_LI FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID) ;


ALTER TABLE PAYMENTTRANSACTION
ADD CONSTRAINT PAYMENTTRANSACTION_BUSINESSUNI FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ;



ALTER TABLE PAYMENTTRANSACTION
ADD CONSTRAINT PAYMENTTRANSACTION_ARTRANSACTI FOREIGN KEY (ARTRANSACTION_ID) REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;




ALTER TABLE PAYMENTTRANSACTIONDETAIL
ADD CONSTRAINT PAYMENTTRANSACTIONDETAIL_PAYME FOREIGN KEY (PAYMENTTRANSACTION_ID) REFERENCES PAYMENTTRANSACTION (PAYMENTTRANSACTION_ID) ON DELETE CASCADE;



ALTER TABLE PAYMENTTRANSACTIONDETAIL
ADD CONSTRAINT PAYMENTTRANSACTIONDETAIL_ARTRA FOREIGN KEY (ARTRANSACTION_ID) REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;


ALTER TABLE PAYMENTTRANSACTIONDETAIL
ADD CONSTRAINT PAYMENTTRANSACTIONDETAIL_INVOI FOREIGN KEY (INVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE CASCADE;
