--ABS-118333
CREATE TABLE ERP_O_SPM_SALESCOMMISSION
(
  TRANSFER_ID                NUMBER,
  TRANSFER_STATUS            NUMBER DEFAULT 0 NOT NULL,
  DATASOURCE                 VARCHAR(6),
  TRANSACTION_TYPE           VARCHAR2(12),
  INVOICEBUSINESSUNITCODE    VARCHAR2(6) NOT NULL,
  INVOICE_NUMBER             NUMBER NOT NULL,
  INVOICE_LINENUMBER         NUMBER NOT NULL,
  INVOICE_AMOUNT             NUMBER(15,6) NOT NULL,
  INVOICE_DATE               DATE,
  INVOICE_FREQUENCY          NUMBER,
  PRODUCT_INSTALLDATE        DATE,
  CUSTOMER_INSTALLDATE       DATE,
  COSTPRICE                  NUMBER(15,6),
  CURRENCY_CODE              VARCHAR2(3) NOT NULL,
  LINE_QUANTITY              NUMBER,
  LINE_AMOUNT                NUMBER(15,6),
  LINE_AMOUNT_WEEK_1         NUMBER,
  LINE_AMOUNT_WEEK_8_AVG     NUMBER,
  LINE_CODE                  VARCHAR2(16) NOT NULL,
  LINE_DESCRIPTION           VARCHAR2(40) NOT NULL,
  TURNOVERGROUP_CODE         VARCHAR2(12),
  TURNOVERGROUP_DESC         VARCHAR2(30),
  WEARERNUMBER               NUMBER,
  CONTRACT_CODE              VARCHAR2(15),
  CONTRACT_TYPE_ID           NUMBER,
  CONTRACT_TYPE_CODE         VARCHAR2(6),
  CONTRACT_DESCRIPTION       VARCHAR2(50),
  MARKET_SEGMENT             VARCHAR2(6),
  MARKET_SEGMENT_DESCRIPTION VARCHAR2(40),
  LOSTACCOUNTFLAG            VARCHAR(1),
  DELTOCUSTOMERNUMBER        NUMBER,
  DELTO_STREET               VARCHAR2(30),
  DELTO_CITY                 VARCHAR2(30),
  DELTO_STATE                VARCHAR2(20),
  DELTO_ZIPCODE              VARCHAR2(15),
  BILLTO_CUSTOMERNUMBER      NUMBER NOT NULL,
  BILLTO_STREET              VARCHAR2(30),
  BILLTO_CITY                VARCHAR2(30),
  BILLTO_STATE               VARCHAR2(20),
  BILLTO_ZIPCODE             VARCHAR2(15),
  CONTRACT_STARTDATE         DATE,
  CONTRACT_ENDDATE           DATE,
  CONTRACT_DURATION          NUMBER,
  CONTRACT_RENEWED_DATE      DATE,
  REGIONCODE                 VARCHAR2(6),
  ROUTECODE                  VARCHAR2(6),
  CONTRACT_SALESEMPLOYEE1    NUMBER,
  CONTRACT_SALESEMPLOYEE2    NUMBER,
  SALESEMPLOYEE1PERCENTAGE   NUMBER,
  SALESEMPLOYEE2PERCENTAGE   NUMBER,
  GROSSMARGIN                NUMBER,
  GROSSMARGIN_PERCENTAGE     NUMBER,
  COMMISSION_TYPE            VARCHAR2(40),
  PRODUCT_WEEKNUMBER         NUMBER,
  CONTRACT_WEEKNUMBER        NUMBER,
  CREDITEDINVOICENUMBER      NUMBER,
  SALESEMPLOYEE1_AMOUNT      NUMBER,
  SALESEMPLOYEE2_AMOUNT      NUMBER,
  INVOICELINE_PRICE          NUMBER(15,6),
  TIMESTAMP                  DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_O_SPM_SALESCOMMISSION.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_SPM_SALESCOMMISSION
  ADD CONSTRAINT PK_ERP_O_SPM_SALESCOMMISSION PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1_ERP_O_SPM_SALESCOM ON ERP_O_SPM_SALESCOMMISSION (INVOICEBUSINESSUNITCODE, INVOICE_DATE, BILLTO_CUSTOMERNUMBER);
CREATE INDEX INDX_ERP_O_SPM_SALESCOM_TS ON ERP_O_SPM_SALESCOMMISSION (TRANSFER_STATUS); 

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

CREATE TABLE ERP_O_SPM_CUST_SALESCOMISION
(
  TRANSFER_ID           NUMBER,
  TRANSFER_STATUS       NUMBER DEFAULT 0 NOT NULL,
  DATASOURCE            CHAR(6),
  BUSINESSUNIT          VARCHAR2(6) NOT NULL,
  CUSTOMERNUMBER        NUMBER NOT NULL,
  CUSTOMER_NAME         VARCHAR2(40) NOT NULL,
  CONTRACTCODE          VARCHAR2(15),
  CONTRACTTYPE_ID       NUMBER,
  CONTRACTTYPE_CODE     VARCHAR2(6),
  CONTRACT_DESCRIPTION  VARCHAR2(50),
  CONTRACT_INSTALLDATE  DATE,
  DELTO_ADDRESS1        VARCHAR2(30),
  DELTO_ADDRESS2        VARCHAR2(30),
  DELTO_CITY            VARCHAR2(30),
  DELTO_STATE           VARCHAR2(20),
  DELTO_ZIPCODE         VARCHAR2(15),
  DELTO_COUNTRYCODE     VARCHAR2(3),
  LOSTDATE              DATE,
  ROUTECODE             VARCHAR2(6),
  INSTALLED_AMOUNT      NUMBER,
  UNINSTALLED_AMOUNT    NUMBER,
  LOCAL_CURRENCY        VARCHAR2(3),
  MARKETSEGMENT         VARCHAR2(6),
  MARKETSEGMENT_DESC    VARCHAR2(40),
  NATIONALACCOUNT_CODE  VARCHAR2(15),
  EMPLOYEE1             NUMBER,
  EMPLOYEE2             NUMBER,
  TIMESTAMP             DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_O_SPM_CUST_SALESCOMISION.TRANSFER_STATUS
  IS '-1 = staging, 0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_SPM_CUST_SALESCOMISION
  ADD CONSTRAINT PK_ERP_O_SPM_CUST_SALESCOMISION PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIF1_ERP_O_SPM_C_SALESCOM ON ERP_O_SPM_CUST_SALESCOMISION (BUSINESSUNIT, CUSTOMERNUMBER);
CREATE INDEX INDX_ERP_O_SPM_C_SALESCOM_TS ON ERP_O_SPM_CUST_SALESCOMISION (TRANSFER_STATUS); 

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