--ABS-78757 Sales Commission Interface
ALTER TABLE INVOICEHEADEREXTRAINFO ADD ERP_EXPORTED_SALESCOMISSION VARCHAR2(1) DEFAULT 'N' NOT NULL;
ALTER TABLE INVOICEHEADEREXTRAINFO
  ADD CONSTRAINT CHK_INVOICEHEADEREI_ERPSALES
  CHECK (ERP_EXPORTED_SALESCOMISSION IN ('N','Y','X'));
CREATE INDEX INDX_INVOICEHEADEREI_ERPSALES ON INVOICEHEADEREXTRAINFO (ERP_EXPORTED_SALESCOMISSION);

CREATE TABLE ERP_O_SALESCOMMISSION
(
TRANSFER_ID 		NUMBER NOT NULL,
TRANSFER_STATUS 	NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_ERP_O_SALESCOMMISSION_V1 CHECK (TRANSFER_STATUS IN (-1,0,1)),
INVOICETYPE 		VARCHAR2(20) NULL,
INVOICEDATE 		DATE NULL,
INVOICENUMBER 		NUMBER NULL,
INVOICE_AMOUNT 		NUMBER DEFAULT 0 NOT NULL,
CURRENCYCODE 		VARCHAR2(3) NULL,
STARTYEAR 			NUMBER NULL,
STARTWEEK 			NUMBER NULL,
ENDYEAR 			NUMBER NULL,
ENDWEEK 			NUMBER NULL,
LINENUMBER 			NUMBER NULL,
PRODUCT_WEEKNUMBER 	NUMBER NULL,
LINE_QTY 			NUMBER NULL,
LINE_AMOUNT 		NUMBER NULL,
LINE_CODE 			VARCHAR2(16) NULL,
LINE_DESCRIPTION 	VARCHAR2(40) NULL,
CREDIT 				VARCHAR2(1) NULL,
INVENTORYQTY 		NUMBER NULL,
DELIVEREDQTY 		NUMBER NULL,
PRODUCTGROUPCODE 	VARCHAR2(12) NULL,
SIZECODE 			VARCHAR2(12) NULL,
FINISHINGMETHODCODE VARCHAR2(6) NULL,
REPLACEMENTPRICE 	NUMBER NULL,
WEARERNUMBER 		NUMBER NULL,
TURNOVERGROUP_CODE 	VARCHAR2(12) NULL,
TURNOVERGROUP_DESC 	VARCHAR2(30) NULL,
CONTRACT_CODE 		VARCHAR2(15) NULL,
DELTOCUSTOMERNUMBER NUMBER NULL,
BILLTOCUSTOMERNUMBER NUMBER NULL,
CONTRACTSTARTDATE 	DATE NULL,
CONTRACTENDDATE 	DATE NULL,
CONTRACT_DURATION 	NUMBER NULL,
INVOICEBUSINESSUNIT VARCHAR2(6) NULL,
ROUTECODE 			VARCHAR2(6) NULL,
INSTALL_DATE 		DATE NULL,
CONTRACT_SALESEMPLOYEE1 NUMBER NULL,
CONTRACT_SALESEMPLOYEE2 NUMBER NULL,
SALESEMPLOYEE1PERCENTAGE NUMBER NULL,
SALESEMPLOYEE2PERCENTAGE NUMBER NULL,
SALESEMPLOYEE1_AMOUNT NUMBER NULL,
SALESEMPLOYEE2_AMOUNT NUMBER NULL,
INVOICELINE_PRICE 	NUMBER NULL,
GROSSMARGIN_PERCENTAGE NUMBER NULL,
GROSSMARGIN 		NUMBER NULL,
INVOICEFREQUENCYNUMBER NUMBER NULL,
SPLITLEVEL 			NUMBER NULL,
SPLITUPCODE 		VARCHAR2(15) NULL,
SPLITPROCESSEDFLAG 	VARCHAR2(1) NULL,
LOSTACCOUNTFLAG 	VARCHAR2(1) NULL,
RENTAL_DS_FLAG 		VARCHAR2(1) NULL,
COMMISSION_TYPE 	VARCHAR2(40) 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_SALESCOMMISSION.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
ALTER TABLE ERP_O_SALESCOMMISSION
  ADD CONSTRAINT ERP_O_SALESCOMMISSION PRIMARY KEY (TRANSFER_ID);
CREATE INDEX INDX01_ERP_O_SALESCOMMISSION ON ERP_O_SALESCOMMISSION(TRANSFER_STATUS);
CREATE INDEX INDX02_ERP_O_SALESCOMMISSION ON ERP_O_SALESCOMMISSION(CONTRACT_CODE);

CREATE TABLE ERP_O_SALESCOMMISSION_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL  CONSTRAINT CHK_ERP_O_SALESCOMMIS_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_SALESCOMMISSION_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN  ERP_O_SALESCOMMISSION_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE ERP_O_SALESCOMMISSION_LOG
  ADD CONSTRAINT PK_ERP_O_SALESCOMMISSION_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_O_SALESCOMMISSION_LOG
  ADD CONSTRAINT FK_ERP_O_SALESCOMMISSION_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_SALESCOMMISSION(TRANSFER_ID) ON DELETE CASCADE;