-- ABS-62311 / ABS-74524
--- NONGARMENTMBYO ---
CREATE SEQUENCE SEQ_NONGARMENTMBYO
START WITH 1
INCREMENT BY 1;

CREATE TABLE NONGARMENTMBYO
(
	NONGARMENTMBYO_ID NUMBER NOT NULL,
	PLPC_ID NUMBER NOT NULL,
	RENTALQTYADJUSTMENT_ID NUMBER NULL,
	QUANTITY NUMBER NOT NULL,
	CHARGEAFTERDATE DATE NOT NULL,
	INVOICELINE_ID NUMBER NULL,
	MBYOPROTECTION VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD461 CHECK (MBYOPROTECTION IN ('Y','N')),
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN NONGARMENTMBYO.QUANTITY IS '0 means the quantity is not finalized yet and will be completed during invoicing';
COMMENT ON COLUMN NONGARMENTMBYO.CHARGEAFTERDATE IS 'The date which the charge cannot be raised before';

ALTER TABLE NONGARMENTMBYO ADD CONSTRAINT XPKNONGARMENTMBYO PRIMARY KEY (NONGARMENTMBYO_ID);

CREATE INDEX XIF1NONGARMENTMBYO ON NONGARMENTMBYO (PLPC_ID ASC);
CREATE INDEX XIF2NONGARMENTMBYO ON NONGARMENTMBYO (RENTALQTYADJUSTMENT_ID ASC);
CREATE INDEX XIF3NONGARMENTMBYO ON NONGARMENTMBYO (INVOICELINE_ID ASC);

ALTER TABLE NONGARMENTMBYO ADD CONSTRAINT NONGARMENTMBYO_PLPC FOREIGN KEY (PLPC_ID)
REFERENCES PRODUCTLISTPERCUSTOMER (PRODUCTLISTPERCUSTOMER_ID) ON DELETE CASCADE;

ALTER TABLE NONGARMENTMBYO ADD CONSTRAINT NONGARMENTMBYO_RQA FOREIGN KEY (RENTALQTYADJUSTMENT_ID)
REFERENCES RENTALQTYADJUSTMENT (RENTALQTYADJUSTMENT_ID) ON DELETE CASCADE;

ALTER TABLE NONGARMENTMBYO ADD CONSTRAINT NONGARMENTMBYO_IL FOREIGN KEY (INVOICELINE_ID)
REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE SET NULL;