--Order 20010684.80

---------------CREDITCARDTRANSACTION_OLD--------------

ALTER TABLE CREDITCARDTRANSACTION RENAME TO CREDITCARDTRANSACTION_OLD;


ALTER TABLE CREDITCARDTRANSACTION_OLD
RENAME COLUMN CREDITCARDTRANSACTION_ID TO CREDITCARDTRANSACTION_OLD_ID;


ALTER TABLE CREDITCARDTRANSACTION_OLD
RENAME CONSTRAINT XPKCREDITCARDTRANSACTION TO XPKCREDITCARDTRANSACTION_OLD;


ALTER TABLE CREDITCARDTRANSACTION_OLD
RENAME CONSTRAINT CREDITCARDTRANSACTION_ARTRANSA TO CREDITCARDTRANSACTION_OLD_ARTR;


ALTER TABLE CREDITCARDTRANSACTION_OLD
RENAME CONSTRAINT CREDITCARDTRANSACTION_BUSINESS TO CREDITCARDTRANSACTION_OLD_BUSI;


ALTER TABLE CREDITCARDTRANSACTION_OLD
RENAME CONSTRAINT CREDITCARDTRANSACTION_CUSTOMER TO CREDITCARDTRANSACTION_OLD_CUST;


ALTER TABLE CREDITCARDTRANSACTION_OLD
RENAME CONSTRAINT CREDITCARDTRANSACTION_INVOICEH TO CREDITCARDTRANSACTION_OLD_INVO;


ALTER TABLE CREDITCARDTRANSACTION_OLD
RENAME CONSTRAINT CREDITCARDTRANSACTION_REMITBAT TO CREDITCARDTRANSACTION_OLD_REMI;


ALTER INDEX XPKCREDITCARDTRANSACTION RENAME TO XPKCREDITCARDTRANSACTION_OLD;


ALTER INDEX XIE1CREDITCARDTRANSACTION RENAME TO XIE1CREDITCARDTRANSACTION_OLD;


ALTER INDEX XIF1CREDITCARDTRANSACTION RENAME TO XIF1CREDITCARDTRANSACTION_OLD;


ALTER INDEX XIF2CREDITCARDTRANSACTION RENAME TO XIF2CREDITCARDTRANSACTION_OLD;


ALTER INDEX XIF3CREDITCARDTRANSACTION RENAME TO XIF3CREDITCARDTRANSACTION_OLD;


ALTER INDEX XIF4CREDITCARDTRANSACTION RENAME TO XIF4CREDITCARDTRANSACTION_OLD;


ALTER INDEX XIF5CREDITCARDTRANSACTION RENAME TO XIF5CREDITCARDTRANSACTION_OLD;


RENAME SEQ_CREDITCARDTRANSACTION TO SEQ_CREDITCARDTRANSACTION_OLD;


---------------CREDITCARDTRANSACTION--------------

CREATE SEQUENCE SEQ_CREDITCARDTRANSACTION
START WITH 1
INCREMENT BY 1;


CREATE TABLE CREDITCARDTRANSACTION
(
	CREDITCARDTRANSACTION_ID NUMBER NOT NULL ,
	BUSINESSUNIT_ID      NUMBER NOT NULL ,
	CREATIONDATE         DATE NOT NULL ,
	ORIGIN               NUMBER NOT NULL  CONSTRAINT  Origin_valid2 CHECK (ORIGIN IN (1, 2, 3, 4, 5)),
	INVOICEHEADER_ID     NUMBER NULL ,
	ARTRANSACTION_ID     NUMBER NOT NULL ,
	STATUS               NUMBER(1) NOT NULL  CONSTRAINT  Status_valid5 CHECK (STATUS IN (0, 1, 2, 3, 4, 5, 6)),
	LASTEXPORTTIMESTAMP  DATE NULL ,
	PROFILENUMBER        VARCHAR2(22) NULL ,
	MERCHANTID           NUMBER(15) DEFAULT  0  NOT NULL ,
	REFUNDAMOUNT         NUMBER DEFAULT  0  NOT NULL ,
	ORIGINALTRANSACTION_ID NUMBER NULL ,
	PARTIALREFUNDARTRANSACTION_ID NUMBER NULL ,
	RETRYTRACE           NUMBER(5) DEFAULT  0  NOT NULL ,
	CARDBRAND            VARCHAR2(2) NULL ,
	TXREFNUM             VARCHAR2(40) NULL ,
	TXREFIDX             NUMBER(4) NULL ,
	PROCSTATUS           VARCHAR2(6) NULL ,
	RESPDATETIME         DATE NULL ,
	APPROVALSTATUS       NUMBER(1) NULL  CONSTRAINT  ApprovalStatus_valid1 CHECK (APPROVALSTATUS IN (0, 1, 2)),
	RESPCODE             VARCHAR2(2) NULL ,
	AUTHORIZATIONCODE    VARCHAR2(6) NULL ,
	PROCSTATUSMESSAGE    VARCHAR2(80) NULL ,
	RESPCODEMESSAGE      VARCHAR2(80) NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE CREDITCARDTRANSACTION
ADD CONSTRAINT  XPKCREDITCARDTRANSACTION PRIMARY KEY (CREDITCARDTRANSACTION_ID) USING INDEX TABLESPACE INDX;


COMMENT ON COLUMN CREDITCARDTRANSACTION.ORIGIN IS '1 = Remittance (manual via Cash application)
2 = Refund
3 = Miscellaneous
4 = Generated via Statement
5 = Generated via Settlement';


COMMENT ON COLUMN CREDITCARDTRANSACTION.ARTRANSACTION_ID IS 'This must be the payment (credit) ARTransction or in case of a refund the debit refund transaction.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.STATUS IS '0 = New – Not processed
1 = Exported for authorization (authorization in progress)
2 = Authorized (successfully processed)
3 = Authorization rejected
4 = Canceled
5 = Refunded (canceled)
6 = Partially refunded';


COMMENT ON COLUMN CREDITCARDTRANSACTION.REFUNDAMOUNT IS 'Only filled for Refund transaction';


COMMENT ON COLUMN CREDITCARDTRANSACTION.ORIGINALTRANSACTION_ID IS 'Filled for Refund transaction, this is the original transaction that is refunded.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.RETRYTRACE IS 'Incremented with each attempt to export the transaction.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.CARDBRAND IS 'VI = Visa
MC = MasterCard
DI = Discover
AX = American Express
BL = Bill Me Later
Only the card type is returned, not the credit card number itself. Returned by the external system';


COMMENT ON COLUMN CREDITCARDTRANSACTION.TXREFNUM IS 'Gateway transaction reference number, generated by the Orbital gateway. Must be stored.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.TXREFIDX IS 'Gateway transaction index, generated by the Orbital gateway. Must be stored.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.PROCSTATUS IS 'Code ‘0’ means transaction is processed successfully.
Please note: successfully processed does not necessarily mean that the transaction is approved.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.RESPDATETIME IS 'Date / time the transaction was processed by Orbital.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.APPROVALSTATUS IS '0 = Declined
1 = Approved
2 = Message / system error
Returns the result of the authorization request (only if transaction is processed successfully).
If the procStatus is processed successfully and approvalStatus = 1 then the transaction is set to captured / approved, else the status is set to ‘failed’.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.RESPCODE IS 'Authorization response code. ‘00’ means approval';


COMMENT ON COLUMN CREDITCARDTRANSACTION.AUTHORIZATIONCODE IS 'Issuer approval code issued by the bank or service establishment for approvals. Stored for reference.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.PROCSTATUSMESSAGE IS 'The corresponding status message. Stored for reference and shown in log.';


COMMENT ON COLUMN CREDITCARDTRANSACTION.RESPCODEMESSAGE IS 'Corresponding approval message. Stored for reference and shown in log.';


CREATE UNIQUE INDEX XAK1CREDITCARDTRANSACTION ON CREDITCARDTRANSACTION
(ARTRANSACTION_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF1CREDITCARDTRANSACTION ON CREDITCARDTRANSACTION
(BUSINESSUNIT_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF2CREDITCARDTRANSACTION ON CREDITCARDTRANSACTION
(INVOICEHEADER_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF3CREDITCARDTRANSACTION ON CREDITCARDTRANSACTION
(ORIGINALTRANSACTION_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF4CREDITCARDTRANSACTION ON CREDITCARDTRANSACTION
(PARTIALREFUNDARTRANSACTION_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIE1CREDITCARDTRANSACTION ON CREDITCARDTRANSACTION
(CREATIONDATE   ASC) TABLESPACE INDX;


ALTER TABLE CREDITCARDTRANSACTION
ADD CONSTRAINT CREDITCARDTRANSACTION_BUSINESS FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);


ALTER TABLE CREDITCARDTRANSACTION
ADD CONSTRAINT CREDITCARDTRANSACTION_INVOICEH FOREIGN KEY (INVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;


ALTER TABLE CREDITCARDTRANSACTION
ADD CONSTRAINT CREDITCARDTRANSACTION_ARTRANSA FOREIGN KEY (ARTRANSACTION_ID) REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;


ALTER TABLE CREDITCARDTRANSACTION
ADD CONSTRAINT CREDITCARDTRANSACTION_CREDITCA FOREIGN KEY (ORIGINALTRANSACTION_ID) REFERENCES CREDITCARDTRANSACTION (CREDITCARDTRANSACTION_ID) ON DELETE CASCADE;


ALTER TABLE CREDITCARDTRANSACTION
ADD CONSTRAINT CREDITCARDTRANSACTION_ARTRANS FOREIGN KEY (PARTIALREFUNDARTRANSACTION_ID) REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE SET NULL;


------------------CREDITCARDSETTINGS-----------------
/*
ALTER TABLE CREDITCARDSETTINGS
DROP CONSTRAINT CREDITCARDSETTINGS_PLANT_ID;



ALTER TABLE CREDITCARDSETTINGS
DROP PRIMARY KEY CASCADE  DROP INDEX;


DROP INDEX XPKCREDITCARDSETTINGS;



DROP TABLE CREDITCARDSETTINGS CASCADE CONSTRAINTS PURGE;
*/

DROP TABLE CREDITCARDSETTINGS;


------------------ARTRANSACTIONTYPE-----------------

ALTER TABLE ARTRANSACTIONTYPE
	ADD (USAGE NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT Usage_valid1 CHECK (USAGE IN (0, 1, 2, 3, 4, 5)));


COMMENT ON COLUMN ARTRANSACTIONTYPE.USAGE IS '0 = General
1 = Used for neutralizing a canceled payment or refund
2 = Used for neutralizing a refunded payment
3 = Used for debit partly refund transaction
4 = Include reverse
5 = Used for NSFFee (NSF = Not sufficient funds)
Usage field is 0 for all predefined fields. Option 1 is used both for credit and for debit. There can be exactly 1 debit and exactly 1 credit.
Option 2 – 5 are only used for debit. For option 2, 3 and 5 there can only be exactly 1 ARtransactiontype defined. For option 4 there can be more.';


ALTER TABLE ARTRANSACTIONTYPE
      ADD CONSTRAINT DEBITCREDIT_USAGE CHECK ((DEBITCREDIT = 'D') or ((DEBITCREDIT = 'C') and (USAGE <= 1)));


UPDATE ARTRANSACTIONTYPE ART SET
       ART.USAGE = 4 WHERE ART.INCLUDEREVERSE = 'Y' AND ART.DEBITCREDIT = 'D';
COMMIT;

       
UPDATE ARTRANSACTIONTYPE ART SET
       ART.USAGE = 5 WHERE ART.NSFFEE = 'Y' AND ART.DEBITCREDIT = 'D';
COMMIT;


ALTER TABLE ARTRANSACTIONTYPE
  DROP COLUMN INCLUDEREVERSE;


ALTER TABLE ARTRANSACTIONTYPE
  DROP COLUMN NSFFEE;


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


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

------------------BUSINESSUNIT-----------------

ALTER TABLE BUSINESSUNIT
	ADD (ZIPPASSWORD VARCHAR2(240) NULL);



ALTER TABLE BUSINESSUNIT
	ADD (SFTPUSERNAME VARCHAR2(240) NULL);



ALTER TABLE BUSINESSUNIT
	ADD (SFTPPASSWORD VARCHAR2(240) NULL);



ALTER TABLE BUSINESSUNIT
	ADD (SFTPPRIMARYADDRESS VARCHAR2(300));



ALTER TABLE BUSINESSUNIT
	ADD (SFTPSECUNDARYADDRESS VARCHAR2(300));



ALTER TABLE BUSINESSUNIT
	ADD (MERCHANTID NUMBER(15)  DEFAULT 0 NOT NULL);


COMMENT ON COLUMN BUSINESSUNIT.ZIPPASSWORD IS 'Encrypted database field
Used in credit card module to protect Zip files
Field length is 40, this must be enlarged with factor 4/3 in the database for encryption.';


COMMENT ON COLUMN BUSINESSUNIT.SFTPUSERNAME IS 'Encrypted database field
Used in credit card module to send credit card transactions file via SFTP.
Field length is 40, this must be enlarged with factor 4/3 in the database for encryption.';


COMMENT ON COLUMN BUSINESSUNIT.SFTPPASSWORD IS 'Encrypted database field
Field length is 40, this must be enlarged with factor 4/3 in the database for encryption.';



COMMENT ON COLUMN BUSINESSUNIT.SFTPPRIMARYADDRESS IS 'Encrypted database field
Field length is 50, this must be enlarged with factor 4/3 in the database for encryption.';



COMMENT ON COLUMN BUSINESSUNIT.SFTPSECUNDARYADDRESS IS 'Encrypted database field
Field length is 50, this must be enlarged with factor 4/3 in the database for encryption.';



------------------CUSTOMERBILLINGINFO-----------------

ALTER TABLE CUSTOMERBILLINGINFO
  DROP COLUMN CREDITCARDTYPE CASCADE CONSTRAINTS;


ALTER TABLE CUSTOMERBILLINGINFO
  DROP COLUMN CREDITCARDNUMBER CASCADE CONSTRAINTS;


ALTER TABLE CUSTOMERBILLINGINFO
  DROP COLUMN EXPIRATIONYEAR CASCADE CONSTRAINTS;


ALTER TABLE CUSTOMERBILLINGINFO
  DROP COLUMN EXPIRATIONMONTH CASCADE CONSTRAINTS;


ALTER TABLE CUSTOMERBILLINGINFO
  DROP COLUMN VERIFICATIONVALUE CASCADE CONSTRAINTS;


ALTER TABLE CUSTOMERBILLINGINFO
  DROP COLUMN CREDITCARDNAME CASCADE CONSTRAINTS;


ALTER TABLE CUSTOMERBILLINGINFO
  DROP COLUMN CREATETRANSACTIONFROMSTATEMENT CASCADE CONSTRAINTS;


ALTER TABLE CUSTOMERBILLINGINFO
	ADD (PROFILENUMBER VARCHAR2(32) NULL);


ALTER TABLE CUSTOMERBILLINGINFO
	ADD (GENERATEPAYMENT NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT GeneratePayment_valid1 CHECK (GENERATEPAYMENT IN (0, 1)));


ALTER TABLE CUSTOMERBILLINGINFO
	ADD (CREDITCARDINFO CLOB NULL);


COMMENT ON COLUMN CUSTOMERBILLINGINFO.PROFILENUMBER IS 'Encrypted database field. This field is used to link a customer to a profile in the external credit card authorization system. The profile contains all the credit card information.';


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


COMMENT ON COLUMN CUSTOMERBILLINGINFO.CREDITCARDINFO IS 'Free text field for entering internal credit card info.';

----------------insert processes-------------

insert into process
  (process_id, description, active)
values
  ('98',
   'Export Credit Card transactions for authorization and capture',
   'Y');

insert into process
  (process_id, description, active)
values
  ('99',
   'Import Credit Card Transactions authorization and capture results',
   'Y');

