-- ABS-62311 / ABS-74524
--- CONTRACTCOMPONENTGRMCHARGES ---
ALTER TABLE CONTRACTCOMPONENTGRMCHARGES ADD (MERCHANDISEBUYOUTCHARGE VARCHAR2(1) NULL
						CONSTRAINT YN_VALIDATIONFIELD456 CHECK (MERCHANDISEBUYOUTCHARGE IN ('Y','N')));

COMMENT ON COLUMN CONTRACTCOMPONENTGRMCHARGES.MERCHANDISEBUYOUTCHARGE IS 'Only used for module MERCHANDISEBUYOUTCHARGE';

--- CONTRACTCOMPONENTBGPROTECTION ---
ALTER TABLE CONTRACTCOMPONENTBGPROTECTION ADD (MBYOPPERCENTAGE NUMBER DEFAULT NULL NULL);

COMMENT ON COLUMN CONTRACTCOMPONENTBGPROTECTION.MBYOPPERCENTAGE IS '0.0 means no protection. Only used for module MERCHANDISEBUYOUTCHARGE.';

ALTER TABLE CONTRACTCOMPONENTBGPROTECTION ADD CONSTRAINT MBYOPPERCENTAGE_VALID1 CHECK ((MBYOPPERCENTAGE BETWEEN 0.0 AND 100.0) OR MBYOPPERCENTAGE IS NULL);


--- CONTRACTPRODUCTORDERDELIVERY ---
ALTER TABLE CONTRACTPRODUCTORDERDELIVERY DROP CONSTRAINT ALLOWRETURNTOSTOCK_VALID1_1;

COMMENT ON COLUMN CONTRACTPRODUCTORDERDELIVERY.ALLOWRETURNTOSTOCK IS '0 = Yes (default)
1 = Yes, excluding special sizes
2 = No
3 = No, Charge MBYO';

ALTER TABLE CONTRACTPRODUCTORDERDELIVERY ADD CONSTRAINT ALLOWRETURNTOSTOCK_VALID1_1 CHECK (ALLOWRETURNTOSTOCK IN (0,1,2,3));

--- CONTRACTPRODUCTBGPROTECTION ---
ALTER TABLE CONTRACTPRODUCTBGPROTECTION ADD (MBYOPPERCENTAGE NUMBER DEFAULT NULL NULL);

COMMENT ON COLUMN CONTRACTPRODUCTBGPROTECTION.MBYOPPERCENTAGE IS 'NULL means the value is taken from the contract level. 0.0 means no protection. Only used for module MERCHANDISEBUYOUTCHARGE.';

ALTER TABLE CONTRACTPRODUCTBGPROTECTION ADD CONSTRAINT MBYOPPERCENTAGE_VALID2 CHECK ((MBYOPPERCENTAGE BETWEEN 0.0 AND 100.0) OR MBYOPPERCENTAGE IS NULL);

--- CUSTOMERBGPROTECTIONPRICE ---
ALTER TABLE CUSTOMERBGPROTECTIONPRICE DROP CONSTRAINT PRICECOMPONENT_VALID1_1;

COMMENT ON COLUMN CUSTOMERBGPROTECTIONPRICE.PRICECOMPONENT IS '1 = Garment Maintenance Protection (GMP)
2 = Garment Setup Protection (GSP)
3 = Garment Loss Protection (GLP)
4 = Garment Repair Protection (GRP)
5 = Merchandise Buyout Protection (MBYOP)';

ALTER TABLE CUSTOMERBGPROTECTIONPRICE ADD CONSTRAINT PRICECOMPONENT_VALID1 CHECK (PRICECOMPONENT IN (1,2,3,4,5));

--- CUSTOMERBUDGETPROTECTION ---
ALTER TABLE CUSTOMERBUDGETPROTECTION ADD (MBYOPPERCENTAGE NUMBER DEFAULT 0 NOT NULL
						CONSTRAINT MBYOPPERCENTAGE_VALID3 CHECK (MBYOPPERCENTAGE BETWEEN 0.0 AND 100.0));

COMMENT ON COLUMN CUSTOMERBUDGETPROTECTION.MBYOPPERCENTAGE IS '0.0 means no protection. Only used for module MERCHANDISEBUYOUTCHARGE.';

--- CUSTOMEREXTRAINFO ---
ALTER TABLE CUSTOMEREXTRAINFO ADD (MERCHANDISEBUYOUTCHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL
					CONSTRAINT YN_VALIDATIONFIELD457 CHECK (MERCHANDISEBUYOUTCHARGE IN ('Y','N')));

--- DISTRIBPOINTBGPROTECTION ---
ALTER TABLE DISTRIBPOINTBGPROTECTION MODIFY (MATERIALMAINTENANCE DEFAULT 'N');
ALTER TABLE DISTRIBPOINTBGPROTECTION MODIFY (GARMENTSETUP DEFAULT 'N');
ALTER TABLE DISTRIBPOINTBGPROTECTION MODIFY (GARMENTMAINTENANCE DEFAULT 'N');
ALTER TABLE DISTRIBPOINTBGPROTECTION MODIFY (GARMENTLOSS DEFAULT 'N');
ALTER TABLE DISTRIBPOINTBGPROTECTION MODIFY (GARMENTREPAIR DEFAULT 'N');

ALTER TABLE DISTRIBPOINTBGPROTECTION ADD (MBYOPROTECTION VARCHAR2(1) DEFAULT 'N' NOT NULL
						CONSTRAINT YN_VALIDATIONFIELD458 CHECK (MBYOPROTECTION IN ('Y','N')));

COMMENT ON COLUMN DISTRIBPOINTBGPROTECTION.MBYOPROTECTION IS 'Only used for module MERCHANDISEBUYOUTCHARGE';

--- GENERALBGPROTECTIONPRICE ---
ALTER TABLE GENERALBGPROTECTIONPRICE DROP CONSTRAINT PRICECOMPONENT_VALID1_2;

COMMENT ON COLUMN GENERALBGPROTECTIONPRICE.PRICECOMPONENT IS '1 = Garment Maintenance Protection (GMP)
2 = Garment Setup Protection (GSP)
3 = Garment Loss Protection (GLP)
4 = Garment Repair Protection (GRP)
5 = Merchandise Buyout Protection (MBYOP)';

ALTER TABLE GENERALBGPROTECTIONPRICE ADD CONSTRAINT PRICECOMPONENT_VALID2 CHECK (PRICECOMPONENT IN (1,2,3,4,5));

--- NONUNIQUEITEMMBYO ---
CREATE TABLE NONUNIQUEITEMMBYO
(
	NONUNIQUEITEM_ID NUMBER NOT NULL,
	MBYOCHARGEINVOICELINE_ID NUMBER NULL,
	MBYOPROTECTION VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD459 CHECK (MBYOPROTECTION IN ('Y','N')),
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE NONUNIQUEITEMMBYO ADD CONSTRAINT XPKNONUNIQUEITEMMBYO PRIMARY KEY (NONUNIQUEITEM_ID);

CREATE INDEX XIF1NONUNIQUEITEMMBYO ON NONUNIQUEITEMMBYO (MBYOCHARGEINVOICELINE_ID ASC);

ALTER TABLE NONUNIQUEITEMMBYO ADD CONSTRAINT NONUNIQUEITEMMBYO_NUI FOREIGN KEY (NONUNIQUEITEM_ID)
REFERENCES NONUNIQUEITEM (NONUNIQUEITEM_ID) ON DELETE CASCADE;

ALTER TABLE NONUNIQUEITEMMBYO ADD CONSTRAINT NONUNIQUEITEMMBYO_MBYOIL FOREIGN KEY (MBYOCHARGEINVOICELINE_ID)
REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;

--- PLPCBUDGETPROTECTION ---
ALTER TABLE PLPCBUDGETPROTECTION ADD (MBYOPPERCENTAGE NUMBER NULL);

ALTER TABLE PLPCBUDGETPROTECTION ADD CONSTRAINT MBYOPPERCENTAGE_VALID4 CHECK ((MBYOPPERCENTAGE BETWEEN 0.0 AND 100.0) OR MBYOPPERCENTAGE IS NULL);

--- UNIQUEITEMMBYO ---
CREATE TABLE UNIQUEITEMMBYO
(
	UNIQUEITEM_ID NUMBER NOT NULL,
	MBYOCHARGEINVOICELINE_ID NUMBER NULL,
	MBYOPROTECTION VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD460 CHECK (MBYOPROTECTION IN ('Y','N')),
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE UNIQUEITEMMBYO ADD CONSTRAINT XPKUNIQUEITEMMBYO PRIMARY KEY (UNIQUEITEM_ID);

CREATE INDEX XIF1UNIQUEITEMMBYO ON UNIQUEITEMMBYO (MBYOCHARGEINVOICELINE_ID ASC);

ALTER TABLE UNIQUEITEMMBYO ADD CONSTRAINT UNIQUEITEMMBYO_UID FOREIGN KEY (UNIQUEITEM_ID)
REFERENCES UNIQUEITEM (UNIQUEITEM_ID) ON DELETE CASCADE;

ALTER TABLE UNIQUEITEMMBYO ADD CONSTRAINT UNIQUEITEMMBYO_MBYOIL FOREIGN KEY (MBYOCHARGEINVOICELINE_ID)
REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;

