--Order 20013799.70

----INVOICEHEADER table----

ALTER TABLE INVOICEHEADER ADD (PDFARCHIVED VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD103
                                          CHECK (PDFARCHIVED IN ('Y', 'N')));
ALTER TABLE INVOICEHEADER ADD (PDFARCHIVEDAPPENDIX VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD104
                                          CHECK (PDFARCHIVEDAPPENDIX IN ('Y', 'N')));

CREATE INDEX XIE7INVOICEHEADER ON INVOICEHEADER (INVOICEBUSINESSUNIT_ID, PDFARCHIVED);
CREATE INDEX XIE8INVOICEHEADER ON INVOICEHEADER (INVOICEBUSINESSUNIT_ID, PDFARCHIVEDAPPENDIX);



----PACKINGNOTE table----

ALTER TABLE PACKINGNOTE ADD (PDFARCHIVED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD105
                                         CHECK (PDFARCHIVED IN ('Y', 'N')));

CREATE INDEX XIE14PACKINGNOTE ON PACKINGNOTE (PROCESSBUSINESSUNIT_ID, PDFARCHIVED);


----CLIENTDELIVERYNOTE table----

ALTER TABLE CLIENTDELIVERYNOTE ADD (PDFARCHIVED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD106
                                         CHECK (PDFARCHIVED IN ('Y', 'N')));

CREATE INDEX XIE5CLIENTDELIVERYNOTE ON CLIENTDELIVERYNOTE (PROCESSBUSINESSUNIT_ID, PDFARCHIVED);


----WEIGHINGTICKET table----

ALTER TABLE WEIGHINGTICKET ADD (PDFARCHIVED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD107
                                         CHECK (PDFARCHIVED IN ('Y', 'N')));

CREATE INDEX XIE4WEIGHINGTICKET ON WEIGHINGTICKET (PROCESSBUSINESSUNIT_ID, PDFARCHIVED);


----WORKORDER table----

ALTER TABLE WORKORDER ADD (PDFARCHIVED VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD108
                                         CHECK (PDFARCHIVED IN ('Y', 'N')));

CREATE INDEX XIE4WORKORDER ON WORKORDER (BUSINESSUNIT_ID, PDFARCHIVED);


----PLANT table----

ALTER TABLE PLANT ADD (KEYWORDFILEFORMAT NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT KEYWORDFILEFORMAT_VALID1_1
                                         CHECK (KEYWORDFILEFORMAT IN (0, 1)));

COMMENT ON COLUMN PLANT.KEYWORDFILEFORMAT
  IS '0 = Not applicable / no keyword file
1 = Saperion
';


----PLANTPDFEXPORT table----

CREATE TABLE PLANTPDFEXPORT
(
       PLANT_ID      NUMBER NOT NULL,
       REPORTNUMBER  NUMBER NOT NULL,
       REPORTNAME    VARCHAR2(25) NOT NULL,
       EXPORTPDF     VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD109
                                         CHECK (EXPORTPDF IN ('Y', 'N')),
       SYSTEMUSER_ID NUMBER NOT NULL,
       TIMESTAMP     DATE NOT NULL
);

ALTER TABLE PLANTPDFEXPORT
ADD CONSTRAINT  XPKPLANTPDFEXPORT PRIMARY KEY (PLANT_ID, REPORTNUMBER);

CREATE UNIQUE INDEX XAK1PLANTPDFEXPORT ON PLANTPDFEXPORT (PLANT_ID, REPORTNAME);

CREATE INDEX XIF1PLANTPDFEXPORT ON PLANTPDFEXPORT (PLANT_ID ASC);

ALTER TABLE PLANTPDFEXPORT
  ADD CONSTRAINT PLANTPDFEXPORT_PLANT FOREIGN KEY (PLANT_ID)
  REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

insert into plantpdfexport
  (plant_id, reportnumber, reportname, exportpdf, systemuser_id, timestamp)
  select p.plant_id, 1, 'Delivery Note', 'N', 0, sysdate from plant p;
commit;

insert into plantpdfexport
  (plant_id, reportnumber, reportname, exportpdf, systemuser_id, timestamp)
  select p.plant_id, 2, 'Weighing Ticket', 'N', 0, sysdate from plant p;
commit;

insert into plantpdfexport
  (plant_id, reportnumber, reportname, exportpdf, systemuser_id, timestamp)
  select p.plant_id, 3, 'Invoice', 'N', 0, sysdate from plant p;
commit;

insert into plantpdfexport
  (plant_id, reportnumber, reportname, exportpdf, systemuser_id, timestamp)
  select p.plant_id, 4, 'Invoice Appendix', 'N', 0, sysdate from plant p;
commit;

insert into plantpdfexport
  (plant_id, reportnumber, reportname, exportpdf, systemuser_id, timestamp)
  select p.plant_id, 5, 'Sales Note', 'N', 0, sysdate from plant p;
commit;

insert into plantpdfexport
  (plant_id, reportnumber, reportname, exportpdf, systemuser_id, timestamp)
  select p.plant_id, 6, 'Client Delivery Note', 'N', 0, sysdate from plant p;
commit;