-- ABS-59024/ ABS-63033

-------   BUSINESSUNITCREDITCARDEBC   -----------
DROP TABLE BUSINESSUNITCREDITCARDEBC CASCADE CONSTRAINTS;

-------  PLANTCREDITCARDEBC  --------------
CREATE TABLE PLANTCREDITCARDEBC
(
PLANT_ID NUMBER NOT NULL,
ENVIRONMENT NUMBER DEFAULT 0 NOT NULL CONSTRAINT ENVIRONMENT_VALID1_1 CHECK (ENVIRONMENT IN (0, 1)),
TESTAPI VARCHAR2(100) NULL,
PINCODE VARCHAR2(40) NOT NULL,
SOURCEKEY VARCHAR2(240) NOT NULL,
CLIENT_IP VARCHAR2(15) NOT NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN PLANTCREDITCARDEBC.ENVIRONMENT IS '0 = Test environment
1 = Live / production environment
';

COMMENT ON COLUMN PLANTCREDITCARDEBC.TESTAPI IS 'Only filled if Environment = 0';
COMMENT ON COLUMN PLANTCREDITCARDEBC.CLIENT_IP IS 'Must be a real IP address (format nnn.nnn.nnn.nnn where nnn between 0 and 255)';

ALTER TABLE PLANTCREDITCARDEBC ADD CONSTRAINT XPKPLANTCREDITCARDEBC PRIMARY KEY (PLANT_ID);

ALTER TABLE PLANTCREDITCARDEBC ADD CONSTRAINT PLANTCCEBC_PLANT FOREIGN KEY (PLANT_ID)
REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

------ PROCESS ----------
INSERT INTO PROCESS (PROCESS_ID, DESCRIPTION, ACTIVE) values ('145','Settle Credit Card Transactions EBizCharge Gateway','Y');
COMMIT;