--ABS-91171
CREATE TABLE ERP_I_PACKINGNOTE
(
  TRANSFER_ID            NUMBER NOT NULL,
  TRANSFER_STATUS        NUMBER DEFAULT 0 NOT NULL,
  NOTENUMBER             NUMBER NOT NULL,
  PROCESSINGBUSINESSUNIT VARCHAR2(6) NOT NULL,
  INTERNALPROCESSINGBU   VARCHAR2(6) NOT NULL,
  INVOICEBUSINESSUNIT    VARCHAR2(6) NOT NULL,
  STATUS                 VARCHAR2(2) DEFAULT '30' NOT NULL,
  PRODUCTIONDATE         DATE NOT NULL,
  DELIVERYDATE           DATE NOT NULL,
  CUSTOMERNUMBER         NUMBER NOT NULL,
  DEPARTMENTNUMBER       NUMBER DEFAULT 1 NOT NULL,
  CONSUMPTIONPOINTNUMBER NUMBER DEFAULT 1 NOT NULL,
  DELIVERYPOINT          VARCHAR2(12) NOT NULL,
  PLANTDEPARTMENTCODE    VARCHAR2(6) NOT NULL,
  TRANSPORTARTICLE       VARCHAR2(6) NOT NULL,
  ROUTENUMBER            NUMBER NULL,
  STOPNUMBER             NUMBER NULL,
  REFERENCENUMBER        VARCHAR2(30) NULL,
  STRAIGHTEVENRETURN     VARCHAR2(1) DEFAULT 'N' NOT NULL,
  SHOWONINVOICEAPPENDIX  VARCHAR2(1) DEFAULT 'Y' NOT NULL,
  REMARK                 VARCHAR2(1000) NULL,
  PNREMARK               VARCHAR2(1000) NULL,
  FREEOFCHARGEREASONCODE VARCHAR2(6) NULL,
  CLEANROOMBATCHNUMBER   NUMBER NULL,
  EXTERNALSTERILIZATIONLOT VARCHAR2(50) NULL,
  TIMESTAMP              DATE DEFAULT SYSDATE NOT NULL  
);
CREATE INDEX INDX_ERP_I_PACKINGNOTE_STATUS ON ERP_I_PACKINGNOTE (TRANSFER_STATUS);
ALTER TABLE ERP_I_PACKINGNOTE  ADD CONSTRAINT PK_ERP_I_PACKINGNOTE PRIMARY KEY (TRANSFER_ID);
COMMENT ON COLUMN ERP_I_PACKINGNOTE.PRODUCTIONDATE
  IS 'Date of packing note';
COMMENT ON COLUMN ERP_I_PACKINGNOTE.DELIVERYDATE
  IS 'Date of delivery note';
COMMENT ON COLUMN ERP_I_PACKINGNOTE.PLANTDEPARTMENTCODE
  IS 'Must be an existing plant department code';
COMMENT ON COLUMN ERP_I_PACKINGNOTE.TRANSPORTARTICLE
  IS 'Transport article code';
COMMENT ON COLUMN ERP_I_PACKINGNOTE.FREEOFCHARGEREASONCODE
  IS 'Must be an existing reason code';
  
CREATE TABLE ERP_I_PACKINGNOTELINE
(
  TRANSFER_ID                  NUMBER NOT NULL,
  TRANSFER_STATUS              NUMBER DEFAULT 0 NOT NULL,
  NOTENUMBER                   NUMBER NOT NULL,
  PRODUCTCODE                  VARCHAR2(12) NOT NULL,
  SIZECODE                     VARCHAR2(12) NOT NULL,
  FINISHINGMETHOD              VARCHAR2(6) DEFAULT '-' NOT NULL,
  STANDARDAMOUNT               NUMBER DEFAULT 0 NOT NULL,
  VARIATIONAMOUNT              NUMBER DEFAULT 0 NOT NULL,
  DELIVEREDAMOUNT              NUMBER DEFAULT 0 NOT NULL,
  TOBEDELIVEREDAMOUNT          NUMBER DEFAULT 0 NOT NULL,
  REMARKFORPACKINGINSTRUCTIONS VARCHAR2(80),
  CUSTOMCODE                   VARCHAR2(16),
  CUSTOMDESCRIPTION            VARCHAR2(30),
  PUTUPTYPE                    NUMBER DEFAULT 1 NOT NULL,
  NOCHARGEFIRSTISSUE           NUMBER DEFAULT 0 NOT NULL,
  DELIVEREDCOGQTY              NUMBER DEFAULT 0 NOT NULL,
  NOCHARGEFIRSTISSUECOG        NUMBER DEFAULT 0 NOT NULL,
  PACKINGSEQUENCE              NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP                    DATE DEFAULT SYSDATE NOT NULL 
);
CREATE INDEX INDX1_ERP_I_PACKINGNOTELINE ON ERP_I_PACKINGNOTELINE (TRANSFER_ID);
ALTER TABLE ERP_I_PACKINGNOTELINE ADD CONSTRAINT FK_ERP_I_PACKINGNOTELINE FOREIGN KEY (TRANSFER_ID)
 REFERENCES ERP_I_PACKINGNOTE(TRANSFER_ID) ON DELETE CASCADE;
ALTER TABLE ERP_I_PACKINGNOTELINE ADD CONSTRAINT PK_ERP_I_PACKINGNOTELINE PRIMARY KEY (TRANSFER_ID, NOTENUMBER, PRODUCTCODE, SIZECODE, FINISHINGMETHOD); 
COMMENT ON COLUMN ERP_I_PACKINGNOTELINE.STANDARDAMOUNT
  IS 'Quantity from standard delivery';
COMMENT ON COLUMN ERP_I_PACKINGNOTELINE.VARIATIONAMOUNT
  IS 'Sum of quantities from delivery variations';
COMMENT ON COLUMN ERP_I_PACKINGNOTELINE.PUTUPTYPE
  IS '1 = Put up per customer (Packingnote)
2 = Bulk put up
3 = Bundle tag';
COMMENT ON COLUMN ERP_I_PACKINGNOTELINE.PACKINGSEQUENCE
  IS 'The items will be packed in this sequence';
ALTER TABLE ERP_I_PACKINGNOTELINE
  ADD CONSTRAINT CHK_ERP_I_PKL_NOCHARGEFIRSTISSUE
  CHECK (NOCHARGEFIRSTISSUE >= 0);
ALTER TABLE ERP_I_PACKINGNOTELINE
  ADD CONSTRAINT CHK_ERP_I_PKL_PUTUPTYPE
  CHECK (PUTUPTYPE IN (0,1,2,3));
ALTER TABLE ERP_I_PACKINGNOTELINE
  ADD CONSTRAINT CHK_ERP_I_PKL_STANDARDAMOUNT
  CHECK (STANDARDAMOUNT >= 0);
ALTER TABLE ERP_I_PACKINGNOTELINE
  ADD CONSTRAINT CHK_ERP_I_PKL_VARIATIONAMOUNT
  CHECK (VARIATIONAMOUNT >= 0);

CREATE TABLE ERP_I_PACKINGNOTE_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);
COMMENT ON COLUMN ERP_I_PACKINGNOTE_LOG.TRANSFER_STATUS IS '0=ready to be read, 1=read correctly';
COMMENT ON COLUMN ERP_I_PACKINGNOTE_LOG.PROCESS_STATUS IS '0=processed correctly, 1=process stopped with errors';
ALTER TABLE ERP_I_PACKINGNOTE_LOG ADD CONSTRAINT PK_ERP_I_PACKINGNOTE_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE ERP_I_PACKINGNOTE_LOG ADD CONSTRAINT FK_ERP_I_PACKINGNOTE_LOG FOREIGN KEY (TRANSFER_ID)
 REFERENCES ERP_I_PACKINGNOTE(TRANSFER_ID) ON DELETE CASCADE;
