--Order 20013742.80

----CLIENT table----

ALTER TABLE CLIENT ADD (ONETIMECHARGED NUMBER DEFAULT 0 NOT NULL
						CONSTRAINT OneTimeCharged_valid1 CHECK (ONETIMECHARGED IN (0, 1, 2))
			);

COMMENT ON COLUMN CLIENT.ONETIMECHARGED
  IS '0 = Not yet charged
1 = Not charged, skipped
2 = Charged
';

update client c set c.onetimecharged = 1;
commit;

----CUSTOMERRESIDENTIALSETTINGS table----

 
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS ADD (ONETIMECHARGE NUMBER DEFAULT 0 NOT NULL 
                                                           CONSTRAINT ONETIMECHARGE_VALID1 CHECK (ONETIMECHARGE IN (0, 1)));

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS ADD (ONETIMECHARGEPRODUCT_ID NUMBER NULL);

COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.ONETIMECHARGE
  IS '0 = No one time charge
1 = One time charge per new client
';

CREATE INDEX XIF9CUSTOMERRESIDENTIALSETTING ON CUSTOMERRESIDENTIALSETTINGS (ONETIMECHARGEPRODUCT_ID);

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  ADD CONSTRAINT CUSTRESSET_PRODUCT FOREIGN KEY (ONETIMECHARGEPRODUCT_ID)
  REFERENCES PRODUCT (PRODUCT_ID);

