--ABS-38331/ ABS-54552

---SUPPLIER table---
ALTER TABLE SUPPLIER DROP COLUMN ILNNUMBER;
ALTER TABLE SUPPLIER DROP COLUMN EDI_POEXPORTFILEFORMAT;
ALTER TABLE SUPPLIER DROP COLUMN EDI_POCONFIRMFILEFORMAT;
ALTER TABLE SUPPLIER ADD (ELECTRONICDATATRANSFER NUMBER DEFAULT 0 NOT NULL
                          CONSTRAINT ELECTRONICDATATRANSFER_VALID1 CHECK (ELECTRONICDATATRANSFER IN (0, 1, 2)));

COMMENT ON COLUMN SUPPLIER.SENDPURCHASEORDER
  IS '0 = By regular mail
1 = By fax (default)
2 = By e-mail
3 = By mail
4 = By file
5 = Electronic data transfer only';

COMMENT ON COLUMN SUPPLIER.ELECTRONICDATATRANSFER
  IS 'Indicates if electronic data transfer is used in communication with supplier. Possible values:
0 = Not applicable / not used
1 = By file
2 = By EDI';

update supplier su set su.electronicdatatransfer = 1, su.sendpurchaseorder = 5
where su.sendpurchaseorder = 4;
commit;

update supplier su set su.electronicdatatransfer = 2, su.sendpurchaseorder = 5
where su.sendpurchaseorder = 3;
commit;

---SUPPLIEREDISETTINGS table---
CREATE TABLE SUPPLIEREDISETTINGS
(
  SUPPLIER_ID         NUMBER NOT NULL,
  ILNNUMBER           NUMBER NULL,
  POEXPORTFILEFORMAT  NUMBER DEFAULT 0 NOT NULL CONSTRAINT POEXPORTFILEFORMAT_VALID1
                      CHECK (POEXPORTFILEFORMAT IN (0, 1, 2)),
  POCONFIRMFILEFORMAT NUMBER DEFAULT 0 NOT NULL CONSTRAINT POCONFIRMFILEFORMAT_VALID1
                      CHECK (POCONFIRMFILEFORMAT IN (0, 1, 2)),
  SYSTEMUSER_ID       NUMBER NOT NULL,
  TIMESTAMP           DATE NOT NULL
)
;

COMMENT ON COLUMN SUPPLIEREDISETTINGS.POEXPORTFILEFORMAT
  IS 'Indicates if purchase order will be exported to supplier via EDI and which file format is used. Possible values:
0 = Not applicable
1 = ABS standard PO export
2 = Mulliez PO export
';

COMMENT ON COLUMN SUPPLIEREDISETTINGS.POCONFIRMFILEFORMAT
  IS 'Indicates if purchase order confirmations will be imported from supplier via EDI and which file format is used. Possible values:
0 = Not applicable
1 = ABS standard PO confirmation
2 = Mulliez PO confirmation
';

ALTER TABLE SUPPLIEREDISETTINGS
  ADD CONSTRAINT XPKSUPPLIEREDISETTINGS PRIMARY KEY (SUPPLIER_ID);
  
ALTER TABLE SUPPLIEREDISETTINGS
  ADD CONSTRAINT SUPPLIEREDISETTINGS_SUPP FOREIGN KEY (SUPPLIER_ID)
  REFERENCES SUPPLIER (SUPPLIER_ID) ON DELETE CASCADE;