--order 502698
 /*
      CHANGE REPORT for Table TOUCHSCREENBUTTON
          - Adding a new relationship TOUCHSCREENBUTTON_REPAIRTYPE_I
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK TOUCHSCREENBUTTON_REPAIRTYPE_I of table TOUCHSCREENBUTTON, which references table REPAIRTYPE).
          - change null constraint from NOT NULL to NULL of column PRODUCT_ID
          - change null constraint from NOT NULL to NULL of column SIZEDEFINITION_ID
          - change null constraint from NOT NULL to NULL of column FINISHINGMETHOD_ID
          - Adding column REPAIRTYPE_ID
          - Adding column CUSTOMEROWNED
          - Changing definition of index XAK2TOUCHSCREENBUTTON
          - Adding index XIF5TOUCHSCREENBUTTON
      ACTION is ALTER or modify Table TOUCHSCREENBUTTON
      IMPACT ANALYSIS REPORT for ALTER or modify Table TOUCHSCREENBUTTON
          - index XIF2TOUCHSCREENBUTTON needs to be removed in order to proceed. 
          - index XIF3TOUCHSCREENBUTTON needs to be removed in order to proceed. 
          - index XIF4TOUCHSCREENBUTTON needs to be removed in order to proceed. 
      */
DROP INDEX XIF2TOUCHSCREENBUTTON;

DROP INDEX XIF3TOUCHSCREENBUTTON;

DROP INDEX XIF4TOUCHSCREENBUTTON;

DROP INDEX XAK2TOUCHSCREENBUTTON;

ALTER TABLE TOUCHSCREENBUTTON MODIFY (PRODUCT_ID NULL);


ALTER TABLE TOUCHSCREENBUTTON MODIFY (SIZEDEFINITION_ID NULL);


ALTER TABLE TOUCHSCREENBUTTON MODIFY (FINISHINGMETHOD_ID NULL);


ALTER TABLE TOUCHSCREENBUTTON ADD (REPAIRTYPE_ID        NUMBER NULL);


ALTER TABLE TOUCHSCREENBUTTON ADD (CUSTOMEROWNED        VARCHAR2(1) DEFAULT 'N' 
    NOT NULL
                                   CONSTRAINT YN_ValidationField2
                                          CHECK (CUSTOMEROWNED IN ('Y', 'N')));

CREATE UNIQUE INDEX XAK2TOUCHSCREENBUTTON ON TOUCHSCREENBUTTON
(
       TOUCHSCREENPAGE_ID             ASC,
       PRODUCT_ID                     ASC,
       SIZEDEFINITION_ID              ASC,
       FINISHINGMETHOD_ID             ASC,
       REPAIRTYPE_ID                  ,
       CUSTOMEROWNED                  
);

CREATE INDEX XIF5TOUCHSCREENBUTTON ON TOUCHSCREENBUTTON
(
       REPAIRTYPE_ID                  ASC
);

CREATE INDEX XIF2TOUCHSCREENBUTTON ON TOUCHSCREENBUTTON
(
       PRODUCT_ID                     ASC
);

CREATE INDEX XIF3TOUCHSCREENBUTTON ON TOUCHSCREENBUTTON
(
       SIZEDEFINITION_ID              ASC
);

CREATE INDEX XIF4TOUCHSCREENBUTTON ON TOUCHSCREENBUTTON
(
       FINISHINGMETHOD_ID             ASC
);
      
      /*
      CHANGE REPORT for Table TOUCHSCREENPAGE
          - modify validation rule from APPLICATION IN (1,2) to APPLICATION IN (1, 2, 3) of column APPLICATION
          WARNING : Load data statement for table TOUCHSCREENPAGE or the statement to add a validation rule may fail (existing data may violate the new validation rule for column APPLICATION).
          - Changing column definition of APPLICATION
      ACTION is ALTER or modify Table TOUCHSCREENPAGE
      */

ALTER TABLE TOUCHSCREENPAGE DROP CONSTRAINT APPLICATION_VALID;

ALTER TABLE TOUCHSCREENPAGE ADD ( CONSTRAINT APPLICATION_VALID
                                      CHECK (APPLICATION IN (1,2,3)) );

COMMENT ON COLUMN TOUCHSCREENPAGE.APPLICATION IS '1 = Residential Services (default)
2 = Soil Count
3 = Variable Charge Registration';

      /*
      CHANGE REPORT for Table CUSTOMER
          - Adding column VARIABLESERVICES
      ACTION is ALTER or modify Table CUSTOMER
      */

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE DISABLE;

ALTER TABLE CUSTOMER ADD (VARIABLESERVICES     NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT VariableServices_Valid
                                          CHECK (VARIABLESERVICES IN (0, 1, 2)));

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE ENABLE;

COMMENT ON COLUMN CUSTOMER.VARIABLESERVICES IS '0 = Do not allow (default)
1 = Allow, do not charge
2 = Allow and charge';
      
      /*
      CHANGE REPORT for Table INVOICELINESUBTYPE
          - Adding a new relationship INVOICELINESUBTYPE_TURNOVERGRO
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK INVOICELINESUBTYPE_TURNOVERGRO of table INVOICELINESUBTYPE, which references table TURNOVERGROUP).
          - Adding column TURNOVERGROUP_ID
          - Adding index XIF2INVOICELINESUBTYPE
      ACTION is ALTER or modify Table INVOICELINESUBTYPE
      */

ALTER TABLE INVOICELINESUBTYPE ADD (TURNOVERGROUP_ID     NUMBER NULL);

CREATE INDEX XIF2INVOICELINESUBTYPE ON INVOICELINESUBTYPE
(
       TURNOVERGROUP_ID               ASC
);

      
      /*
      CHANGE REPORT for Table MODIFICATION
          - Adding a new relationship MODIFICATION_REPAIRTYPE_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK MODIFICATION_REPAIRTYPE_ID of table MODIFICATION, which references table REPAIRTYPE).
          - Adding a new relationship MODIFICATION_REPAIRTYPECOG_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK MODIFICATION_REPAIRTYPECOG_ID of table MODIFICATION, which references table REPAIRTYPE).
          - Adding a new relationship MODIFICATION_UNCONFIRMEDFLAG_I
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK MODIFICATION_UNCONFIRMEDFLAG_I of table MODIFICATION, which references table FLAG).
          - Adding column REPAIRTYPECOG_ID
          - Adding column REPAIRTYPE_ID
          - Adding column UNCONFIRMEDFLAG_ID
          - Adding index XIF1MODIFICATION
          - Adding index XIF2MODIFICATION
          - Adding index XIF3MODIFICATION
      ACTION is ALTER or modify Table MODIFICATION
      */

ALTER TABLE MODIFICATION ADD (REPAIRTYPECOG_ID     NUMBER NULL);


ALTER TABLE MODIFICATION ADD (REPAIRTYPE_ID        NUMBER NULL);


ALTER TABLE MODIFICATION ADD (UNCONFIRMEDFLAG_ID   NUMBER NULL);

CREATE INDEX XIF1MODIFICATION ON MODIFICATION
(
       REPAIRTYPECOG_ID               ASC
);

CREATE INDEX XIF2MODIFICATION ON MODIFICATION
(
       REPAIRTYPE_ID                  ASC
);

CREATE INDEX XIF3MODIFICATION ON MODIFICATION
(
       UNCONFIRMEDFLAG_ID             ASC
);

      
      /*
      CHANGE REPORT for Table MODIFICATIONGROUP
          - Adding a new relationship MODIFICATIONGROUP_REPAIRTYPE_I
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK MODIFICATIONGROUP_REPAIRTYPE_I of table MODIFICATIONGROUP, which references table REPAIRTYPE).
          - Adding a new relationship MODIFICATIONGROUP_REPAIRTYPECO
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK MODIFICATIONGROUP_REPAIRTYPECO of table MODIFICATIONGROUP, which references table REPAIRTYPE).
          - Adding a new relationship MODIFICATIONGROUP_UNCONFIRMEDF
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK MODIFICATIONGROUP_UNCONFIRMEDF of table MODIFICATIONGROUP, which references table FLAG).
          - Adding column REPAIRTYPECOG_ID
          - Adding column REPAIRTYPE_ID
          - Adding column UNCONFIRMEDFLAG_ID
          - Adding index XIF1MODIFICATIONGROUP
          - Adding index XIF2MODIFICATIONGROUP
          - Adding index XIF3MODIFICATIONGROUP
      ACTION is ALTER or modify Table MODIFICATIONGROUP
      */

ALTER TABLE MODIFICATIONGROUP ADD (REPAIRTYPECOG_ID     NUMBER NULL);


ALTER TABLE MODIFICATIONGROUP ADD (REPAIRTYPE_ID        NUMBER NULL);


ALTER TABLE MODIFICATIONGROUP ADD (UNCONFIRMEDFLAG_ID   NUMBER NULL);

CREATE INDEX XIF1MODIFICATIONGROUP ON MODIFICATIONGROUP
(
       REPAIRTYPECOG_ID               ASC
);

CREATE INDEX XIF2MODIFICATIONGROUP ON MODIFICATIONGROUP
(
       REPAIRTYPE_ID                  ASC
);

CREATE INDEX XIF3MODIFICATIONGROUP ON MODIFICATIONGROUP
(
       UNCONFIRMEDFLAG_ID             ASC
);

      
      /*
      CHANGE REPORT for Table REPAIRHISTORY
          - Adding column QUANTITY
          - Adding column CUSTOMEROWNED
      ACTION is ALTER or modify Table REPAIRHISTORY
      */

ALTER TABLE REPAIRHISTORY ADD (QUANTITY             NUMBER DEFAULT 1 NOT NULL);


ALTER TABLE REPAIRHISTORY ADD (CUSTOMEROWNED        VARCHAR2(1) DEFAULT 'N' NOT 
    NULL
                                   CONSTRAINT YN_Validation
                                          CHECK (CUSTOMEROWNED IN ('Y', 'N')));

      
      /*
      CHANGE REPORT for Table REPAIRTYPE
          - Adding a new relationship REPAIRTYPE_ACTIVITY_ID
          WARNING : Create FK statement may fail (existing data may violate the new FK rules: adding a new FK REPAIRTYPE_ACTIVITY_ID of table REPAIRTYPE, which references table ACTIVITY).
          - Adding column CATEGORY
          - Adding column SELECTABLEFOR
          - Adding column ACTIVITY_ID
          - Adding index XIF1REPAIRTYPE
      ACTION is ALTER or modify Table REPAIRTYPE
      */

ALTER TABLE REPAIRTYPE ADD (CATEGORY             NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT Category_Valid
                                          CHECK (CATEGORY IN (1, 2, 3)));

COMMENT ON COLUMN REPAIRTYPE.CATEGORY IS '1 = Regular Repair (default)
2 = Service Charge
3 = Modifications Charge';

ALTER TABLE REPAIRTYPE ADD (SELECTABLEFOR        NUMBER DEFAULT 0 NOT NULL
                                   CONSTRAINT SelectableFor_Valid
                                          CHECK (SELECTABLEFOR IN (0, 1, 2)));

COMMENT ON COLUMN REPAIRTYPE.SELECTABLEFOR IS '0 = LOG and COG (default)
1 = LOG only
2 = COG only';

ALTER TABLE REPAIRTYPE ADD (ACTIVITY_ID          NUMBER NULL);

CREATE INDEX XIF1REPAIRTYPE ON REPAIRTYPE
(
       ACTIVITY_ID                    ASC
);

UPDATE invoicelinesubtype ilst
SET ilst.factorydescription='Regular Repair'
WHERE ilst.subtype=0
AND ilst.invoicelinetype_id=6;
   
insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             1,
             6,
             'Regular Repair COG',
             1,
             sysdate
        from dual;

 insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             2,
             6,
             'Service Charge',
             1,
             sysdate
        from dual;

 insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             3,
             6,
             'Service Charge COG',
             1,
             sysdate
        from dual;

 insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             4,
             6,
             'Modification Charge',
             1,
             sysdate
        from dual;

 insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             5,
             6,
             'Modification Charge COG',
             1,
             sysdate
        from dual;


--TOUCHSCREENBUTTON table 
alter table touchscreenbutton  add constraint chk_ProdSizeFmRepairType CHECK (((product_id is NOT null) AND (SIZEDEFINITION_id is NOT null) AND (finishingmethod_id is NOT null)
AND  (repairtype_id  IS  null)) OR ((product_id IS  null) AND (SIZEDEFINITION_id IS  null) AND (finishingmethod_id IS  null)
AND  (repairtype_id  IS  NOT null)));


--REPAIRHISTORY table
update repairhistory rh
	   set rh.customerowned = 'Y'
	 where rh.repairhistory_id in
	       (select r.repairhistory_id
	          from repairhistory r, uniqueitem u
	         where u.customerowned = 'Y'
	           and u.uniqueitem_id = r.uniqueitem_link_id);

ALTER TABLE INVOICELINESUBTYPE
       ADD  ( CONSTRAINT INVOICELINESUBTYPE_TURNOVERGRO
              FOREIGN KEY (TURNOVERGROUP_ID)
                             REFERENCES TURNOVERGROUP ) ;


ALTER TABLE MODIFICATION
       ADD  ( CONSTRAINT MODIFICATION_REPAIRTYPE_ID
              FOREIGN KEY (REPAIRTYPE_ID)
                             REFERENCES REPAIRTYPE ) ;


ALTER TABLE MODIFICATION
       ADD  ( CONSTRAINT MODIFICATION_REPAIRTYPECOG_ID
              FOREIGN KEY (REPAIRTYPECOG_ID)
                             REFERENCES REPAIRTYPE ) ;


ALTER TABLE MODIFICATION
       ADD  ( CONSTRAINT MODIFICATION_UNCONFIRMEDFLAG_I
              FOREIGN KEY (UNCONFIRMEDFLAG_ID)
                             REFERENCES FLAG ) ;


ALTER TABLE MODIFICATIONGROUP
       ADD  ( CONSTRAINT MODIFICATIONGROUP_REPAIRTYPE_I
              FOREIGN KEY (REPAIRTYPE_ID)
                             REFERENCES REPAIRTYPE ) ;


ALTER TABLE MODIFICATIONGROUP
       ADD  ( CONSTRAINT MODIFICATIONGROUP_REPAIRTYPECO
              FOREIGN KEY (REPAIRTYPECOG_ID)
                             REFERENCES REPAIRTYPE ) ;


ALTER TABLE MODIFICATIONGROUP
       ADD  ( CONSTRAINT MODIFICATIONGROUP_UNCONFIRMEDF
              FOREIGN KEY (UNCONFIRMEDFLAG_ID)
                             REFERENCES FLAG ) ;


ALTER TABLE REPAIRTYPE
       ADD  ( CONSTRAINT REPAIRTYPE_ACTIVITY_ID
              FOREIGN KEY (ACTIVITY_ID)
                             REFERENCES ACTIVITY ) ;


ALTER TABLE TOUCHSCREENBUTTON
       ADD  ( CONSTRAINT TOUCHSCREENBUTTON_REPAIRTYPE_I
              FOREIGN KEY (REPAIRTYPE_ID)
                             REFERENCES REPAIRTYPE ) ;

