--ABS-82444 Customer Contract
ALTER TABLE PRODUCTLISTPERCUSTOMER ADD ERP_EXPORTED_SC VARCHAR2(1) DEFAULT 'N' NOT NULL;
ALTER TABLE PRODUCTLISTPERCUSTOMER
  ADD CONSTRAINT CHK_PLPC_ERP_EXPORTED_SC
  CHECK (ERP_EXPORTED_SC IN ('N','Y','X'));
CREATE INDEX INDX_PLPC_ERP_EXPORTED_SC ON PRODUCTLISTPERCUSTOMER (ERP_EXPORTED_SC);

ALTER TABLE CUSTOMER ADD ERP_EXPORTED_SC VARCHAR2(1) DEFAULT 'N' NOT NULL;
ALTER TABLE CUSTOMER
  ADD CONSTRAINT CHK_CUST_ERP_EXPORTED_SC
  CHECK (ERP_EXPORTED_SC IN ('N','Y','X'));
CREATE INDEX INDX_CUST_ERP_EXPORT_SC ON CUSTOMER (ERP_EXPORTED_SC);

CREATE TABLE ERP_O_CUST_SALESCOMMISSION
(
TRANSFER_ID 		NUMBER NOT NULL,
TRANSFER_STATUS 	NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_CUST_SALESCOMMISS_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER 		NUMBER NULL,
CUSTOMERNAME 		VARCHAR2(40) NULL,
CONTRACTCODE 		VARCHAR2(15) NULL,
CONTRACT_DESC 		VARCHAR2(50) NULL,
BUSINESSUNIT 		VARCHAR2(6) NULL,
MARKETSEGMENT_DESC 	VARCHAR2(40) NULL,
INSTALLDATE 		DATE NULL,
LOSTDATE 			DATE NULL,
YEARWEEK 			NUMBER(6) NULL,
INSTALLED_AMOUNT 	NUMBER NULL,
UNINSTALLED_AMOUNT 	NUMBER NULL,
EXT_ERR_CODE 		VARCHAR2(20) NULL,
EXT_ERR_DESC 		VARCHAR2(200) NULL,
EXT_BATCH_ID 		NUMBER NULL,
EXT_INST_ID 		VARCHAR2(40) NULL,
EXT_STATUS 			VARCHAR2(1) NULL,
EXT_TIMESTAMP 		DATE NULL
);
COMMENT ON COLUMN  ERP_O_CUST_SALESCOMMISSION.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_CUST_SALESCOMMISSION
  ADD CONSTRAINT PK_ERP_O_CUST_SALESCOMMISSION PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_CUST_SALESCOMMISSION
  ADD CONSTRAINT PK2_ERP_O_CUST_SALESCOMMISSION UNIQUE (TRANSFER_ID, CUSTOMERNUMBER, CONTRACTCODE);
CREATE INDEX INDX01_ERP_O_CUST_SALESCOMMISS ON ERP_O_CUST_SALESCOMMISSION(TRANSFER_STATUS);
CREATE INDEX INDX02_ERP_O_CUST_SALESCOMMISS ON ERP_O_CUST_SALESCOMMISSION(CUSTOMERNUMBER, CONTRACTCODE);

CREATE TABLE ERP_O_PROD_SALESCOMMISSION
(
TRANSFER_ID 		NUMBER NOT NULL,
TRANSFER_STATUS 	NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_PROD_SALESCOMMISS_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
CUSTOMERNUMBER 		NUMBER NULL,
CONTRACTCODE 		VARCHAR2(15) NULL,
PRODUCTCODE 		VARCHAR2(12) NULL,
TURNOVERGROUPCODE 	VARCHAR2(12) NULL,
INSTALLDATE 		DATE NULL,
LOSTDATE 			DATE NULL,
EXT_ERR_CODE 		VARCHAR2(20) NULL,
EXT_ERR_DESC 		VARCHAR2(200) NULL,
EXT_BATCH_ID 		NUMBER NULL,
EXT_INST_ID 		VARCHAR2(40) NULL,
EXT_STATUS 			VARCHAR2(1) NULL,
EXT_TIMESTAMP 		DATE NULL
);
COMMENT ON COLUMN  ERP_O_PROD_SALESCOMMISSION.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_PROD_SALESCOMMISSION
  ADD CONSTRAINT PK_ERP_O_PROD_SALESCOMMISSION PRIMARY KEY (TRANSFER_ID, CUSTOMERNUMBER, PRODUCTCODE);
ALTER TABLE ERP_O_PROD_SALESCOMMISSION
  ADD CONSTRAINT FK_ERP_O_PROD_SALESCOMMISSION FOREIGN KEY (TRANSFER_ID, CUSTOMERNUMBER, CONTRACTCODE)
  REFERENCES ERP_O_CUST_SALESCOMMISSION(TRANSFER_ID, CUSTOMERNUMBER, CONTRACTCODE) ON DELETE CASCADE;
CREATE INDEX INDX01_ERP_O_PROD_SALESCOMMISS ON ERP_O_PROD_SALESCOMMISSION(TRANSFER_ID);
CREATE INDEX INDX02_ERP_O_PROD_SALESCOMMISS ON ERP_O_PROD_SALESCOMMISSION(TRANSFER_STATUS);
CREATE INDEX INDX03_ERP_O_PROD_SALESCOMMISS ON ERP_O_PROD_SALESCOMMISSION(CUSTOMERNUMBER, PRODUCTCODE);

CREATE TABLE ERP_O_CUST_SALESCOMMISSION_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_O_CUST_SALESCOM_LOG_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN  ERP_O_CUST_SALESCOMMISSION_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN  ERP_O_CUST_SALESCOMMISSION_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_O_CUST_SALESCOMMISSION_LOG
  ADD CONSTRAINT PK_ERP_O_CUST_SALESCOMMIS_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_CUST_SALESCOMMISSION_LOG
  ADD CONSTRAINT FK_ERP_O_CUST_SALESCOMMIS_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_CUST_SALESCOMMISSION(TRANSFER_ID) ON DELETE CASCADE;
