-- ABS-67952 / ABS-73874

-------------------- WEBPORTALPAYMENTSESSION --------------------
CREATE SEQUENCE SEQ_WEBPORTALPAYMENTSESSION
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEBPORTALPAYMENTSESSION
( WEBPORTALPAYMENTSESSION_ID NUMBER NOT NULL,
  TRANSACTIONLOOKUPKEY       NUMBER NOT NULL,
  WEBUSER_ID                 NUMBER NOT NULL,
  CUSTOMER_ID                NUMBER NOT NULL,
  REFERENCE                  VARCHAR2(30) NULL,
  STARTTIMESTAMP             DATE NOT NULL,
  AMOUNT                     NUMBER NOT NULL,
  OPENCREDITAMOUNT           NUMBER NOT NULL,
  ENVIRONMENT                NUMBER NOT NULL CONSTRAINT ENVIRONMENT_VALID1 CHECK (ENVIRONMENT IN (0,1)),
  PROCESSOPENCREDITS         NUMBER NOT NULL CONSTRAINT PROCESSOPENCREDITS_VALID1 CHECK (PROCESSOPENCREDITS IN (0,1)),
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL);

COMMENT ON COLUMN WEBPORTALPAYMENTSESSION.TRANSACTIONLOOKUPKEY
  IS 'Unique number to identify the transaction. Use the ARTransaction_ID (of the first selected invoice or statement).';
COMMENT ON COLUMN WEBPORTALPAYMENTSESSION.AMOUNT
  IS 'Net amount to pay';
COMMENT ON COLUMN WEBPORTALPAYMENTSESSION.OPENCREDITAMOUNT
  IS 'Total amount on open credits';
COMMENT ON COLUMN WEBPORTALPAYMENTSESSION.ENVIRONMENT
  IS '0 = Test
1 = Live / Production';
COMMENT ON COLUMN WEBPORTALPAYMENTSESSION.PROCESSOPENCREDITS
  IS '0 = Pay full amount
1 = Subtract open credit amount from full amount';

ALTER TABLE WEBPORTALPAYMENTSESSION
  ADD CONSTRAINT XPKWEBPORTALPAYMENTSESSION PRIMARY KEY (WEBPORTALPAYMENTSESSION_ID);

CREATE UNIQUE INDEX XAK1WEBPORTALPAYMENTSESSION ON WEBPORTALPAYMENTSESSION (TRANSACTIONLOOKUPKEY ASC);
CREATE INDEX XIF1WEBPORTALPAYMENTSESSION ON WEBPORTALPAYMENTSESSION (WEBUSER_ID ASC);
CREATE INDEX XIF2WEBPORTALPAYMENTSESSION ON WEBPORTALPAYMENTSESSION (CUSTOMER_ID ASC);

ALTER TABLE WEBPORTALPAYMENTSESSION
  ADD CONSTRAINT WEBPORTALPAYMENTSES_WEBUSER FOREIGN KEY (WEBUSER_ID)
  REFERENCES WEBUSER (WEBUSER_ID) ON DELETE CASCADE;
ALTER TABLE WEBPORTALPAYMENTSESSION
  ADD CONSTRAINT WEBPORTALPAYMENTSES_CUSTOMER FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER (CUSTOMER_ID) ON DELETE CASCADE;


-------------------- WEBPORTALPAYMENTSESSLINE --------------------
CREATE SEQUENCE SEQ_WEBPORTALPAYMENTSESSLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEBPORTALPAYMENTSESSLINE
( WEBPORTALPAYMENTSESSLINE_ID NUMBER NOT NULL,
  WEBPORTALPAYMENTSESSION_ID  NUMBER NOT NULL,
  ARTRANSACTION_ID            NUMBER NOT NULL,
  AMOUNT                      NUMBER NOT NULL,
  SYSTEMUSER_ID               NUMBER NOT NULL,
  TIMESTAMP                   DATE NOT NULL);

COMMENT ON COLUMN WEBPORTALPAYMENTSESSLINE.AMOUNT
  IS 'Net amount to pay for this debit transaction (Amount minus TotalAppliedAmount)';
  
ALTER TABLE WEBPORTALPAYMENTSESSLINE
  ADD CONSTRAINT XPKWEBPORTALPAYMENTSESSLINE PRIMARY KEY (WEBPORTALPAYMENTSESSLINE_ID);

CREATE UNIQUE INDEX XAK1WEBPORTALPAYMENTSESSLINE ON WEBPORTALPAYMENTSESSLINE (WEBPORTALPAYMENTSESSION_ID ASC, ARTRANSACTION_ID ASC);
CREATE INDEX XIF1WEBPORTALPAYMENTSESSLINE ON WEBPORTALPAYMENTSESSLINE (WEBPORTALPAYMENTSESSION_ID ASC);
CREATE INDEX XIF2WEBPORTALPAYMENTSESSLINE ON WEBPORTALPAYMENTSESSLINE (ARTRANSACTION_ID ASC);

ALTER TABLE WEBPORTALPAYMENTSESSLINE
  ADD CONSTRAINT WEBPAYMENTSESSLINE_PAYMENTSESS FOREIGN KEY (WEBPORTALPAYMENTSESSION_ID)
  REFERENCES WEBPORTALPAYMENTSESSION (WEBPORTALPAYMENTSESSION_ID) ON DELETE CASCADE;
ALTER TABLE WEBPORTALPAYMENTSESSLINE
  ADD CONSTRAINT WEBPAYMENTSESSLINE_ARTRANSAC FOREIGN KEY (ARTRANSACTION_ID)
  REFERENCES ARTRANSACTION (ARTRANSACTION_ID) ON DELETE CASCADE;

-------------------- PLANTCREDITCARDEBC --------------------
ALTER TABLE PLANTCREDITCARDEBC ADD (TESTUSERID VARCHAR2(240) NULL,
                                    TESTPASSWORD VARCHAR2(240) NULL,
                                    TESTSECURITYID VARCHAR2(240) NULL);

COMMENT ON COLUMN PLANTCREDITCARDEBC.TESTUSERID
  IS 'User ID for web portal credit card interface test environment';
