--ABS-108930
ALTER TABLE EDI_O_PURCHASEORDERHEADER ADD COMPANYCODE VARCHAR2(10);
COMMENT ON COLUMN EDI_O_PURCHASEORDERHEADER.POEXPORTFORMAT
  is '1 = ABS standard PO Export
2 = Mulliez PO Export
3 = Alsico PO Export';
COMMENT ON COLUMN EDI_O_PURCHASEORDERHEADER.COMPANYCODE
  IS 'Optional company code. Will only be filled for Alsico when defined as flex field on business unit level';
ALTER TABLE EDI_O_PURCHASEORDERHEADER
  DROP CONSTRAINT CHK_EDI_O_PURCHASEORDERHEAD_PO;
ALTER TABLE EDI_O_PURCHASEORDERHEADER
  ADD CONSTRAINT CHK_EDI_O_PURCHASEORDERHEAD_PO
  CHECK (POEXPORTFORMAT IN (0,1,2,3));

CREATE TABLE EDI_O_PURCHASEORDERLINE_ALS
(
  TRANSFER_ID          NUMBER NOT NULL,
  PURCHASEORDERLINE_ID NUMBER NOT NULL,
  DETAILLINENUMBER     NUMBER DEFAULT 1 NOT NULL,
  EXTERNALPOLINENUMBER NUMBER NOT NULL,
  EXPORTEDPOLINENUMBER NUMBER NOT NULL,
  WORKORDERLINE_ID     NUMBER,
  REQUIRESEMBROIDERY   VARCHAR2(1) DEFAULT 'N' NOT NULL,
  QUANTITYORDERED      NUMBER DEFAULT 0 NOT NULL,
  SKUCODE              VARCHAR2(7),
  SUNDRYITEMTEXT       VARCHAR2(30),
  LINESTATUS           VARCHAR2(3),
  SERVICECODE          VARCHAR2(5),
  DESIGNCODE           VARCHAR2(5),
  EMBROIDERYTEXT       VARCHAR2(30),
  EMBROIDDERYFONT      VARCHAR2(10),
  EMBROIDERYSTYLE      VARCHAR2(5),
  EMBROIDERYSIZE       NUMBER
);
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINE_ALS.TRANSFER_ID
  IS 'Foreign key to EDI_O_PURCHASEORDERLINE, Part of compound primary key';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINE_ALS.PURCHASEORDERLINE_ID
  IS 'Foreign key to EDI_O_PURCHASEORDERLINE, part of compound primary key';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINE_ALS.DETAILLINENUMBER
  IS 'Part of compound primary';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINE_ALS.EXTERNALPOLINENUMBER
  IS 'Unique line number within an exported PO, used to uniquely identify the exact exported line since one PO line in ABS can be exported as multiple lines. 
Basically, only needed for the PO confirmation.';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINE_ALS.EXPORTEDPOLINENUMBER
  IS 'Unique line number within an exported PO, used to uniquely identify the exact exported line since one PO line in ABS can be exported as multiple lines. 
Basically, only needed for the PO confirmation.';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINE_ALS.WORKORDERLINE_ID
  IS 'Link to work order line, optional so may be empty';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINE_ALS.REQUIRESEMBROIDERY
  IS 'Indicates if embroidery is required that should be done by supplier';
ALTER TABLE EDI_O_PURCHASEORDERLINE_ALS
  ADD CONSTRAINT PK_EDI_O_PURCHASEORDERLINE_ALS PRIMARY KEY (TRANSFER_ID, PURCHASEORDERLINE_ID, DETAILLINENUMBER);
ALTER TABLE EDI_O_PURCHASEORDERLINE_ALS
  ADD CONSTRAINT FK_EDI_O_PURCHASEORDERLINE_ALS FOREIGN KEY (TRANSFER_ID, PURCHASEORDERLINE_ID)
  REFERENCES EDI_O_PURCHASEORDERLINE (TRANSFER_ID, PURCHASEORDERLINE_ID) ON DELETE CASCADE;

CREATE TABLE EDI_O_PURCHASEORDERLINE_EMBR_ALS
(
TRANSFER_ID          NUMBER NOT NULL,
PURCHASEORDERLINE_ID NUMBER NOT NULL,
DETAILLINENUMBER     NUMBER DEFAULT 1 NOT NULL,
EMBROIDERYSEQNUMBER  NUMBER DEFAULT 1 NOT NULL,
EMBROIDERYCODE       VARCHAR2(6) NOT NULL,
EMBROIDERYLOCATION   VARCHAR2(98) NOT NULL
);
ALTER TABLE EDI_O_PURCHASEORDERLINE_EMBR_ALS
  ADD CONSTRAINT PK_EDI_O_PURCHORDLINE_EMBR_ALS PRIMARY KEY (TRANSFER_ID, PURCHASEORDERLINE_ID, DETAILLINENUMBER, EMBROIDERYSEQNUMBER);
ALTER TABLE EDI_O_PURCHASEORDERLINE_EMBR_ALS
  ADD CONSTRAINT FK_EDI_O_PURCHORDLINE_EMBR_ALS FOREIGN KEY (TRANSFER_ID, PURCHASEORDERLINE_ID, DETAILLINENUMBER)
  REFERENCES EDI_O_PURCHASEORDERLINE_ALS (TRANSFER_ID, PURCHASEORDERLINE_ID, DETAILLINENUMBER) ON DELETE CASCADE;
