-- ABS-48366/ ABS-58107

--ERP_O_STOCKLEDGERACCOUNT table--
CREATE SEQUENCE SEQ_TRANSFER
START WITH 1
INCREMENT BY 1;

CREATE TABLE ERP_O_STOCKLEDGERACCOUNT
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NULL,
  BUSINESSUNITCODE    VARCHAR2(6) NULL,
  PERIOD              NUMBER(2) NULL,
  TRANSACTIONDATE     DATE NULL,
  TRANSACTIONYEAR     NUMBER(4) NULL,
  TRANSACTIONWEEK     NUMBER(2) NULL,
  BOOKNUMBER          NUMBER NULL,
  SUPPLIERNUMBER      NUMBER NULL,
  SUPPLIERDESCRIPTION VARCHAR2(40) NULL,
  LEDGERACCOUNT       VARCHAR2(15) NULL,
  AMOUNT              NUMBER NULL,
  EXPORTDATE          DATE DEFAULT TRUNC(SYSDATE) NULL
);

COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.TRANSFER_ID
  IS 'Sequential number for a group of stockroom transactions';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.TRANSFER_STATUS
  IS '0 ready to be read, 1 Read correctly';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.BUSINESSUNITCODE
  IS 'Business unit code';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.PERIOD
  IS '1 – 13 week number from transaction date. =>Period = (week–1) div 4 + 1 (except week 53 = period 13)';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.TRANSACTIONDATE
  IS 'Transaction date stocktransaction';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.TRANSACTIONYEAR
  IS 'year transaction date stocktransaction';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.TRANSACTIONWEEK
  IS 'week from transaction date stocktransaction';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.BOOKNUMBER
  IS 'In case of receive from PO this is the PO number, else the date (DDMMYY)';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.SUPPLIERNUMBER
  IS 'Supplier number';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.SUPPLIERDESCRIPTION
  IS 'Suppier Name, Or in case no supplier defined then "Autom. boeking ABSSolute"';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.LEDGERACCOUNT
  IS 'Ledger account';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT.EXPORTDATE
  IS 'Date of export';

ALTER TABLE ERP_O_STOCKLEDGERACCOUNT
  ADD CONSTRAINT PK_ERP_O_STOCKLEDGERACCOUNT PRIMARY KEY (TRANSFER_ID);
  
CREATE INDEX INDX_ERP_O_STOCKLEDGERACCNT_01 ON ERP_O_STOCKLEDGERACCOUNT (EXPORTDATE ASC, TRANSFER_STATUS ASC);

-- ERP_O_STOCKLEDGERACCOUNT_LOG table --
CREATE TABLE ERP_O_STOCKLEDGERACCOUNT_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN ERP_O_STOCKLEDGERACCOUNT_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE ERP_O_STOCKLEDGERACCOUNT_LOG
  ADD CONSTRAINT PK_ERP_O_STOCKLEDGERACC_LOG PRIMARY KEY (TRANSFER_ID);

ALTER TABLE ERP_O_STOCKLEDGERACCOUNT_LOG
  ADD CONSTRAINT FK_ERP_O_STOCKLEDGERACC_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES ERP_O_STOCKLEDGERACCOUNT (TRANSFER_ID) ON DELETE CASCADE;

