-- ABS-6276/Order 20016099.60 Customer PO Enhancements
-----------------------CONTRACTPURCHASEORDER-------------------------

CREATE SEQUENCE SEQ_CONTRACTPURCHASEORDER
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTPURCHASEORDER
	(CONTRACTPURCHASEORDER_ID NUMBER  NOT NULL,
	CONTRACT_ID NUMBER NOT NULL,
	PURCHASEORDERNUMBER VARCHAR2(40) NOT NULL,
	STARTDATE DATE NOT NULL,
	EXPIRATIONDATE DATE NOT NULL,
	VALUE NUMBER DEFAULT 0 NOT NULL,
	INVOICEDAMOUNT NUMBER DEFAULT 0 NOT NULL,
	WARNPERCENTAGE NUMBER(5,2) DEFAULT 0.00 NOT NULL CONSTRAINT WARNPERCENTAGE_valid CHECK (WARNPERCENTAGE BETWEEN 0.00 AND 100.00),
	WARNDAYS NUMBER(4) DEFAULT 0 NOT NULL CONSTRAINT WARNDAYS_valid CHECK (WARNDAYS BETWEEN 0 AND 9999),
	CLOSEACTION NUMBER DEFAULT 1 NOT NULL CONSTRAINT CLOSEACTION_valid CHECK (CLOSEACTION IN (0, 1, 2)),
	MAXPERCENTAGE NUMBER(5,2) DEFAULT 100.00 NOT NULL CONSTRAINT MAXPERCENTAGE_valid CHECK (MAXPERCENTAGE BETWEEN 100.00 and 999.00),
	INVOICELINETYPESPERPO VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD242 CHECK (INVOICELINETYPESPERPO IN ('Y', 'N')),
	BUSINESSUNIT_ID NUMBER NULL,
	SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
	TIMESTAMP DATE NOT NULL);

COMMENT ON COLUMN CONTRACTPURCHASEORDER.CLOSEACTION IS '0 = PO is closed when value is reached
1 = Continue until expired (exceed PO value)
2 = Continue until expired or maximum percentage';

ALTER TABLE CONTRACTPURCHASEORDER
ADD CONSTRAINT  XPKCONTRACTPURCHASEORDER  PRIMARY KEY (CONTRACTPURCHASEORDER_ID);

CREATE UNIQUE INDEX XAK1CONTRACTPURCHASEORDER ON CONTRACTPURCHASEORDER
(CONTRACT_ID    ASC, PURCHASEORDERNUMBER ASC, STARTDATE ASC);

CREATE  INDEX XIF1CONTRACTPURCHASEORDER ON CONTRACTPURCHASEORDER
(CONTRACT_ID   ASC);

CREATE  INDEX XIF2CONTRACTPURCHASEORDER ON CONTRACTPURCHASEORDER
(BUSINESSUNIT_ID   ASC);

ALTER TABLE CONTRACTPURCHASEORDER
ADD CONSTRAINT CONTRACTPO_CONTRACT FOREIGN KEY (CONTRACT_ID) REFERENCES CONTRACT(CONTRACT_ID);

ALTER TABLE CONTRACTPURCHASEORDER
ADD CONSTRAINT CONTRACTPO_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT(BUSINESSUNIT_ID) ON DELETE SET NULL;



-----------------------CONTRACTPOINVOICELINETYPE-------------------------

CREATE TABLE CONTRACTPOINVOICELINETYPE
	(CONTRACTPURCHASEORDER_ID NUMBER  NOT NULL,
	INVOICELINETYPE_ID NUMBER NOT NULL,
	SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
	TIMESTAMP DATE NOT NULL);

ALTER TABLE CONTRACTPOINVOICELINETYPE
ADD CONSTRAINT  XPKCONTRACTPOINVOICELINETYPE  PRIMARY KEY (CONTRACTPURCHASEORDER_ID, INVOICELINETYPE_ID);

CREATE  INDEX XIF1CONTRACTPOINVTYPE ON CONTRACTPOINVOICELINETYPE
(CONTRACTPURCHASEORDER_ID   ASC);


CREATE  INDEX XIF2CONTRACTPOINVTYPE ON CONTRACTPOINVOICELINETYPE
(INVOICELINETYPE_ID   ASC);

ALTER TABLE CONTRACTPOINVOICELINETYPE
ADD CONSTRAINT CONTRACTPOINVTYPE_CONTRACTPO FOREIGN KEY (CONTRACTPURCHASEORDER_ID) REFERENCES CONTRACTPURCHASEORDER(CONTRACTPURCHASEORDER_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTPOINVOICELINETYPE
ADD CONSTRAINT  CONTRACTPOINVTYPE_INVLINETY FOREIGN KEY (INVOICELINETYPE_ID) REFERENCES INVOICELINETYPE(INVOICELINETYPE_ID) ON DELETE CASCADE;


-----------------------CONTRACTPOCUSTOMER-------------------------

CREATE SEQUENCE SEQ_CONTRACTPOCUSTOMER
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTPOCUSTOMER
	(CONTRACTPOCUSTOMER_ID NUMBER  NOT NULL,
	CONTRACTPURCHASEORDER_ID NUMBER NOT NULL,
	CONTRACTCUSTOMER_ID NUMBER NOT NULL,
	STARTDATE DATE NOT NULL,
	ENDDATE DATE NOT NULL,
	SYSTEMUSER_ID NUMBER DEFAULT 0 NOT NULL,
	TIMESTAMP DATE NOT NULL);

ALTER TABLE CONTRACTPOCUSTOMER
ADD CONSTRAINT  XPKCONTRACTPOCUSTOMER_ID  PRIMARY KEY (CONTRACTPOCUSTOMER_ID);

CREATE UNIQUE INDEX XAK1CONTRACTPOCUSTOMER ON CONTRACTPOCUSTOMER
(CONTRACTPURCHASEORDER_ID  ASC, CONTRACTCUSTOMER_ID ASC);

CREATE  INDEX XIF1CONTRACTPOCUSTOMER ON CONTRACTPOCUSTOMER
(CONTRACTPURCHASEORDER_ID   ASC);

CREATE  INDEX XIF2CONTRACTPOCUSTOMER ON CONTRACTPOCUSTOMER
(CONTRACTCUSTOMER_ID   ASC);

ALTER TABLE CONTRACTPOCUSTOMER
ADD CONSTRAINT CONTRACTPOCUST_CONTRACTPO FOREIGN KEY (CONTRACTPURCHASEORDER_ID) REFERENCES CONTRACTPURCHASEORDER(CONTRACTPURCHASEORDER_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTPOCUSTOMER
ADD CONSTRAINT CONTRACTPOCUST_CONTRACTCUST FOREIGN KEY (CONTRACTCUSTOMER_ID) REFERENCES CONTRACTCUSTOMER(CONTRACTCUSTOMER_ID) ON DELETE CASCADE;

-----------------------CUSTOMER-------------------------

ALTER TABLE CUSTOMER
	ADD (POREQUIRED NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT POREQUIRED_valid CHECK (POREQUIRED IN (0, 1)));

COMMENT ON COLUMN CUSTOMER.POREQUIRED IS '0 = PO is optional / Invoice is settled/finalized when PO is not filled
1 = PO is mandatory. Invoice is not settled / finalized when PO info is missing.
';


-----------------------PRODUCTLISTPERCUSTOMER-------------------------

ALTER TABLE PRODUCTLISTPERCUSTOMER
	ADD (CONTRACTPURCHASEORDER_ID NUMBER NULL);

CREATE  INDEX XIF25PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER
(CONTRACTPURCHASEORDER_ID   ASC);

ALTER TABLE PRODUCTLISTPERCUSTOMER
ADD CONSTRAINT PRODUCTLISTPERCUST_CONTRACTPO FOREIGN KEY (CONTRACTPURCHASEORDER_ID) REFERENCES CONTRACTPURCHASEORDER(CONTRACTPURCHASEORDER_ID) ON DELETE SET NULL;

-----------------------INVOICELINE-------------------------

ALTER TABLE INVOICELINE
	ADD (CONTRACTPURCHASEORDER_ID NUMBER NULL);

CREATE  INDEX XIF8INVOICELINE ON INVOICELINE
(CONTRACTPURCHASEORDER_ID   ASC);

ALTER TABLE INVOICELINE
ADD CONSTRAINT INVOICELINE_CONTRACTPO FOREIGN KEY (CONTRACTPURCHASEORDER_ID) REFERENCES CONTRACTPURCHASEORDER(CONTRACTPURCHASEORDER_ID) ON DELETE SET NULL;

-----------------------PROCESS SCHEDULER-------------------------

INSERT INTO PROCESS(PROCESS_ID,DESCRIPTION,ACTIVE) VALUES ('125','Import Contract Purchase Order','Y');
COMMIT;

-- Create table IMP_CONTRACTPO
CREATE SEQUENCE SEQ_IMP_CONTRACTPO
START WITH 1
INCREMENT BY 1;

create table IMP_CONTRACTPO
(
  IMP_CONTRACTPO_ID   NUMBER  NOT NULL,
  STATUS              NUMBER DEFAULT -1 NOT NULL constraint CHK1_IMP_CONTRACTPO_STATUS  check (STATUS IN (-1, 0,1,2,3)),
  MESSAGE             VARCHAR2(1000),
  PROCESSINGTIMESTAMP DATE,
  PONUMBER            VARCHAR2(40) NOT NULL,
  CONTRACTCODE        VARCHAR2(15),
  CUSTOMERNUMBER      NUMBER NOT NULL,
  BUSINESSUNITCODE    VARCHAR2(6),
  STARTDATE           DATE NOT NULL,
  EXPIRATIONDATE      DATE NOT NULL constraint CHK4_IMP_CONTRTPO_EXPIRDATE check (EXPIRATIONDATE > STARTDATE),
  VALUE               NUMBER NOT NULL,
  WARNLEVEL           NUMBER DEFAULT 80 NOT NULL constraint CHK2_IMP_CONTRACTPO_WARNLEVEL check (WARNLEVEL BETWEEN 0 AND 100),
  CLOSEACTION         NUMBER DEFAULT 0 NOT NULL constraint CHK3_IMP_CONTRTPO_CLOSEACTION check (CLOSEACTION IN (0,1,2)),
  MAXPERCENTAGE       NUMBER(5,2) DEFAULT 100.00 NOT NULL CONSTRAINT MXPRCT_valid CHECK (MAXPERCENTAGE BETWEEN 100.00 and 999.00),
  PRODUCTCODE         VARCHAR2(12),
  SIZECODE            VARCHAR2(12),
  FINISHINGMETHODCODE VARCHAR2(6),
  PRICE               NUMBER,
  SYSTEMUSER_ID       NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP       DATE NOT NULL
);
-- Add comments to the columns 
comment on column IMP_CONTRACTPO.status
  is '-1 = New
0 = New
1 = Successfully imported
2 = Imported with warning
3 = Error, not imported';
comment on column IMP_CONTRACTPO.message
  is 'Process Warning / Error message';
comment on column IMP_CONTRACTPO.PROCESSINGTIMESTAMP
  is 'Date / time of processing';
comment on column IMP_CONTRACTPO.ponumber
  is 'PURCHASEORDERNUMBER';
comment on column IMP_CONTRACTPO.expirationdate
  is 'StartDate >= startdate';
comment on column IMP_CONTRACTPO.warnlevel
  is 'Percentage (between 0.00 and 100.00).';
comment on column IMP_CONTRACTPO.closeaction
  is '0 = Close PO
1 = Exceed value (until expiration date is reached)
2 = Continue until expired or maximum percentage';
comment on column IMP_CONTRACTPO.productcode
  is 'Optional to specify PO for specific product. Must be an existing product code';
comment on column IMP_CONTRACTPO.sizecode
  is 'Must be an existing size code';
comment on column IMP_CONTRACTPO.finishingmethodcode
  is 'Must be an existing finishingmethod code';
comment on column IMP_CONTRACTPO.price
  is 'If filled the Price must match the price in the PLPC,
for BM 4 the wash price, for BM 1 - 3 the rent price';

ALTER TABLE IMP_CONTRACTPO
  ADD CONSTRAINT  XPKIMP_CONTRACTPO  PRIMARY KEY (IMP_CONTRACTPO_ID);

-- Create/Recreate indexes 
create index INDX1_CONTRACTPO on IMP_CONTRACTPO (STATUS);

alter table INVOICELINETYPE
  add constraint XAK1INVOICELINETYPE unique (TYPE);

-- Create table IMP_CONTRACTPOINVLINETYPE
CREATE SEQUENCE SEQ_IMP_CONTRACTPOINVLINETYPE
START WITH 1
INCREMENT BY 1;

create table IMP_CONTRACTPOINVLINETYPE
(
  IMP_CONTRACTPOINVLINETYPE_ID   NUMBER  NOT NULL,
  STATUS                NUMBER DEFAULT 0 NOT NULL constraint CHK1_IMP_CONTRPOINVLTP_STATUS check (STATUS IN (0,1,2,3)),
  MESSAGE               VARCHAR2(1000),
  PROCESSINGTIMESTAMP   DATE,
  PONUMBER              VARCHAR2(40) NOT NULL,
  INVOICELINETYPENUMBER NUMBER NOT NULL constraint CHK1_IMP_CONTRPOINVLTP_INVTPNR check (INVOICELINETYPENUMBER NOT IN (14,18)),
  SYSTEMUSER_ID         NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP             DATE NOT NULL
);
-- Add comments to the columns 
comment on column IMP_CONTRACTPOINVLINETYPE.status
  is '0 = New
1 = Successfully imported
2 = Imported with warning
3 = Error, not imported';
comment on column IMP_CONTRACTPOINVLINETYPE.message
  is 'Process Warning / Error message';
comment on column IMP_CONTRACTPOINVLINETYPE.processingtimestamp
  is 'Date / time of processing';
comment on column IMP_CONTRACTPOINVLINETYPE.ponumber
  is 'PURCHASEORDERNUMBER';
comment on column IMP_CONTRACTPOINVLINETYPE.invoicelinetypenumber
  is '01= Rent
02= Delivery (wash)
03= Soil Weight
04= Clean Weight
05= Sales
06= Repair
07= Replacement charge
08= Automatic Replacement (non-garments)
09= Loss charge
10= Remaining value charge
11= Overuse charge
12= Prep / Emblem charge
13= Finance charge
15= Miscellaneous Invoice line
16= Special Charge
17= Minimum invoice amount
19= Emblem Rent
20= Locker Rent
21= Locker service
22= Lease
23= Stock Charge
24= Lost Customer Residual Value Charge
25= Lost Revenue Charge
26= Residential wash
27= Residential labels
28= Service costs
29= Capped Excess Amounts
30= Client rent
31= Budget
Note: 14 (Cash delay) and 18 (Tax) are not used on PO';

-- Create/Recreate primary, unique and foreign key constraints 
ALTER TABLE IMP_CONTRACTPOINVLINETYPE
  ADD CONSTRAINT  XPKIMP_CONTRACTPOINVLINETYPE  PRIMARY KEY (IMP_CONTRACTPOINVLINETYPE_ID);

CREATE UNIQUE INDEX XAK1IMP_CONTRACTPOINVLINETYPE ON IMP_CONTRACTPOINVLINETYPE
  (PONUMBER    ASC, INVOICELINETYPENUMBER ASC, PROCESSINGTIMESTAMP ASC);

-- Create/Recreate indexes 
create index INDX1_CONTRACTPOINVLINETYPE on IMP_CONTRACTPOINVLINETYPE (STATUS);

alter table IMP_CONTRACTPOINVLINETYPE
  add constraint FK_IMP_CONTRACTPOINVLINETYPE foreign key (INVOICELINETYPENUMBER)
  references INVOICELINETYPE (TYPE);  


---------------------CONTRACTPURCHASEORDER-------------------

ALTER TABLE CONTRACTPURCHASEORDER
  ADD (MANUALPO VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD243 CHECK (MANUALPO IN ('Y', 'N')));


---------------------CONTRACTPURCHASEORDERLINE-------------------

CREATE SEQUENCE SEQ_CONTRACTPURCHASEORDERLINE
START WITH 1
INCREMENT BY 1;

CREATE TABLE CONTRACTPURCHASEORDERLINE
	(CONTRACTPURCHASEORDERLINE_ID NUMBER  NOT NULL,
	CONTRACTPURCHASEORDER_ID NUMBER NOT NULL,
	LINENUMBER NUMBER NOT NULL,
	CUSTOMER_LINK_ID NUMBER NOT NULL,
	PRODUCT_LINK_ID NUMBER NOT NULL,
	SIZEDEFINITION_LINK_ID NUMBER NOT NULL,
	FINISHINGMETHOD_ID NUMBER NULL,
	WEARER_ID NUMBER NULL,
	QUANTITY NUMBER NULL,
	PRICE NUMBER NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL);

ALTER TABLE CONTRACTPURCHASEORDERLINE
ADD CONSTRAINT  XPKCONTRACTPURCHASEORDERLINE  PRIMARY KEY (CONTRACTPURCHASEORDERLINE_ID);

CREATE INDEX XIF1CONTRACTPURCHASEORDERLINE ON CONTRACTPURCHASEORDERLINE (CONTRACTPURCHASEORDER_ID ASC);

CREATE INDEX XIF2CONTRACTPURCHASEORDERLINE ON CONTRACTPURCHASEORDERLINE (CUSTOMER_LINK_ID ASC);

CREATE INDEX XIF3CONTRACTPURCHASEORDERLINE ON CONTRACTPURCHASEORDERLINE (PRODUCT_LINK_ID ASC);

CREATE INDEX XIF4CONTRACTPURCHASEORDERLINE ON CONTRACTPURCHASEORDERLINE (SIZEDEFINITION_LINK_ID ASC);

CREATE INDEX XIF5CONTRACTPURCHASEORDERLINE ON CONTRACTPURCHASEORDERLINE (FINISHINGMETHOD_ID ASC);

CREATE INDEX XIF6CONTRACTPURCHASEORDERLINE ON CONTRACTPURCHASEORDERLINE (WEARER_ID ASC);

ALTER TABLE CONTRACTPURCHASEORDERLINE 
  ADD CONSTRAINT CONTRACTPOLINE_CONTRACTPO FOREIGN KEY (CONTRACTPURCHASEORDER_ID)
  REFERENCES CONTRACTPURCHASEORDER(CONTRACTPURCHASEORDER_ID) ON DELETE CASCADE;

ALTER TABLE CONTRACTPURCHASEORDERLINE 
  ADD CONSTRAINT CONTRACTPOLINE_CUST_LINK FOREIGN KEY (CUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK(CUSTOMER_LINK_ID);

ALTER TABLE CONTRACTPURCHASEORDERLINE 
  ADD CONSTRAINT CONTRACTPOLINE_PROD_LINK FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK(PRODUCT_LINK_ID);

ALTER TABLE CONTRACTPURCHASEORDERLINE 
  ADD CONSTRAINT CONTRACTPOLINE_SIZEDEF_LINK FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK(SIZEDEFINITION_LINK_ID);

ALTER TABLE CONTRACTPURCHASEORDERLINE 
  ADD CONSTRAINT CONTRACTPOLINE_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD(FINISHINGMETHOD_ID);

ALTER TABLE CONTRACTPURCHASEORDERLINE 
  ADD CONSTRAINT CONTRACTPOLINE_WEARER FOREIGN KEY (WEARER_ID)
  REFERENCES WEARER(WEARER_ID) ON DELETE SET NULL;


---------------------INVOICELINETYPE-------------------

ALTER TABLE INVOICELINETYPE
  ADD (INCLUDEPOCHECK VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT YN_VALIDATIONFIELD244 CHECK (INCLUDEPOCHECK IN ('Y', 'N')));

UPDATE INVOICELINETYPE I
SET I.INCLUDEPOCHECK = 'N'
WHERE I.INVOICELINETYPE_ID IN (14,18);

COMMIT;

---------------------WEARERINVENTORY-------------------

ALTER TABLE WEARERINVENTORY
  ADD (CONTRACTPURCHASEORDER_ID NUMBER NULL,
            CONTRACTPURCHASEORDERLINE_ID NUMBER NULL );

CREATE INDEX XIF8WEARERINVENTORY ON WEARERINVENTORY (CONTRACTPURCHASEORDERLINE_ID ASC);

CREATE INDEX XIF9WEARERINVENTORY ON WEARERINVENTORY (CONTRACTPURCHASEORDER_ID ASC);

ALTER TABLE WEARERINVENTORY
  ADD CONSTRAINT WEARERINV_CONTRACTPOLINE FOREIGN KEY (CONTRACTPURCHASEORDERLINE_ID)
  REFERENCES CONTRACTPURCHASEORDERLINE(CONTRACTPURCHASEORDERLINE_ID) ON DELETE SET NULL;

ALTER TABLE WEARERINVENTORY
  ADD CONSTRAINT WEARERINV_CONTRACTPO FOREIGN KEY (CONTRACTPURCHASEORDER_ID)
  REFERENCES CONTRACTPURCHASEORDER(CONTRACTPURCHASEORDER_ID) ON DELETE SET NULL;


---------------------PRODUCTLISTPERCUSTOMER-------------------

ALTER TABLE PRODUCTLISTPERCUSTOMER
  ADD (CONTRACTPURCHASEORDERLINE_ID NUMBER NULL );

CREATE INDEX XIF26PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER(CONTRACTPURCHASEORDERLINE_ID ASC);

ALTER TABLE PRODUCTLISTPERCUSTOMER
  ADD CONSTRAINT PLPC_CONTRACTPOLINE FOREIGN KEY (CONTRACTPURCHASEORDERLINE_ID)
  REFERENCES CONTRACTPURCHASEORDERLINE(CONTRACTPURCHASEORDERLINE_ID) ON DELETE SET NULL;

---------------------INVOICELINE-------------------

ALTER TABLE INVOICELINE
  ADD (CONTRACTPURCHASEORDERLINE_ID NUMBER NULL );

CREATE INDEX XIF7INVOICELINE ON INVOICELINE(CONTRACTPURCHASEORDERLINE_ID ASC);

ALTER TABLE INVOICELINE
  ADD CONSTRAINT INVLINE_CONTRACTPOLINE FOREIGN KEY (CONTRACTPURCHASEORDERLINE_ID)
  REFERENCES CONTRACTPURCHASEORDERLINE(CONTRACTPURCHASEORDERLINE_ID) ON DELETE SET NULL;

-- Modify columns IMP_CONTRACTPO
alter table IMP_CONTRACTPO modify expirationdate default TO_DATE('31-12-2099','DD-MM-YYYY');
alter table IMP_CONTRACTPO modify customernumber null;

-- Add columns IMP_CONTRACTPO
alter table IMP_CONTRACTPO add 
	(customerlegacycode VARCHAR2(12),
	 manualpo VARCHAR2(1) default 'N' not null constraint CHK5_IMP_CONTRTPO_MANUALPO check (MANUALPO IN ('N','Y')),
	 linenumber number,
	 linetype number default 0 not null constraint CHK6_IMP_CONTRTPO_LineType check (LineType in (0,1)),
	 wearernumber number,
	 quantity number);

-- Add comments to the columns 
comment on column IMP_CONTRACTPO.manualpo
  is 'Possible values Y and N, default Y';
comment on column IMP_CONTRACTPO.linenumber
  is 'Optional to specify PO for specific product (Linetype <> 0)';
comment on column IMP_CONTRACTPO.linetype
  is '0 = Blank PO
1 = Product specific';
comment on column IMP_CONTRACTPO.finishingmethodcode
  is 'Optional to specify PO for specific product (Linetype <> 0)
Must be an existing finishingmethod code when used';
comment on column IMP_CONTRACTPO.price
  is 'LineType <> 0';
comment on column IMP_CONTRACTPO.wearernumber
  is 'Needed for Wearer Inventory adjustment';
comment on column IMP_CONTRACTPO.quantity
  is 'LineType <> 0';  

CREATE UNIQUE INDEX XAK1IMP_CONTRACTPO ON IMP_CONTRACTPO
(CONTRACTCODE    ASC, CUSTOMERNUMBER ASC, PONUMBER ASC, LINENUMBER ASC, PROCESSINGTIMESTAMP ASC);

  
--ABS-7333
-----------------------PLANT-------------------------

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

COMMENT ON COLUMN PLANT.SUBCONTRACTOR IS '0 = Not Subcontractor
1 = Subcontractor';


-----------------------CUSTOMER-------------------------

ALTER TABLE CUSTOMER
	ADD (SUBCONTRACTOR NUMBER(1) DEFAULT 0 NOT NULL CONSTRAINT SUBCONTRACTOR2_valid CHECK (SUBCONTRACTOR IN (0, 1)),
	          COMPETITOR_ID NUMBER NULL);

CREATE INDEX XIF31CUSTOMER ON CUSTOMER(COMPETITOR_ID ASC);

ALTER TABLE CUSTOMER
  ADD CONSTRAINT CUSTOMER_COMPETITOR FOREIGN KEY (COMPETITOR_ID)
  REFERENCES COMPETITOR (COMPETITOR_ID) ON DELETE SET NULL;

COMMENT ON COLUMN CUSTOMER.SUBCONTRACTOR IS '0 = Not Subcontractor
1 = Subcontractor';

--ABS-7653
-----------------------PRODUCTGROUP-------------------------

ALTER TABLE PRODUCTGROUP
	ADD (FULLLOAD NUMBER(1) NULL CONSTRAINT FULLLOAD_valid CHECK (FULLLOAD IN (0, 1)));

COMMENT ON COLUMN PRODUCTGROUP.FULLLOAD IS '0 = not available for FullLoad functionality
     1 = available for FullLoad functionality';
