-- ABS-104338
--- CUSTOMER---
COMMENT ON COLUMN CUSTOMER.CHARGERESIDUALVALUE
  IS 'Y/N (for garments)';

COMMENT ON COLUMN CUSTOMER.CHARGELOSSCHARGE
  IS 'Y/N. Default value N (for garments)';

--- CUSTOMERGENERAL
CREATE TABLE CUSTOMERGENERAL
(
  CUSTOMER_ID NUMBER NOT NULL,
  NONGARMENTCHARGERESIDUALVALUE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CUSTGEN_CHRES CHECK (NONGARMENTCHARGERESIDUALVALUE  IN ('Y','N')),
  NONGARMENTCHARGELOSSCHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CUSTGEN_CHLOSS CHECK (NONGARMENTCHARGELOSSCHARGE IN ('Y','N')),
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL
);

ALTER TABLE CUSTOMERGENERAL
  ADD CONSTRAINT XPKCUSTOMERGENERAL PRIMARY KEY (CUSTOMER_ID);
ALTER TABLE CUSTOMERGENERAL
  ADD CONSTRAINT FK_CUSTOMERGENERAL_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID)
  REFERENCES CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE;

CREATE OR REPLACE PROCEDURE INSERT_CUSTOMERGENERAL AS
STMT VARCHAR2(5000);
BEGIN
  FOR X IN (SELECT CU.CUSTOMER_ID, CU.CHARGERESIDUALVALUE, CU.CHARGELOSSCHARGE FROM CUSTOMER CU WHERE CU.CUSTOMER_ID NOT IN (SELECT CG.CUSTOMER_ID FROM CUSTOMERGENERAL CG) ) LOOP
    BEGIN
STMT := 'INSERT INTO CUSTOMERGENERAL
(CUSTOMER_ID,
NONGARMENTCHARGERESIDUALVALUE,
NONGARMENTCHARGELOSSCHARGE,
SYSTEMUSER_ID,
TIMESTAMP)
      SELECT '||X.CUSTOMER_ID||','||''''||X.CHARGERESIDUALVALUE||''''||','||''''|| X.CHARGELOSSCHARGE||''''||', GETMINSYSTEMUSER(), SYSDATE FROM DUAL';
 EXECUTE IMMEDIATE(STMT);       
COMMIT;
    EXCEPTION WHEN OTHERS  THEN NULL;
END;
    END LOOP;
END INSERT_CUSTOMERGENERAL;
/
EXECUTE INSERT_CUSTOMERGENERAL;

DROP PROCEDURE  INSERT_CUSTOMERGENERAL;

----CONTRACTATTRIBUTE
ALTER TABLE CONTRACTATTRIBUTE DROP CONSTRAINT GROUPNUMBER_VALID1_1;

COMMENT ON COLUMN CONTRACTATTRIBUTE.GROUPNUMBER IS '1 = Miscellaneous
2 = Garments - Settings
3 = Garments - Locker Management
4 = Garments - Charges
5 = Garments - Wearer Functions
6 = Garments - Emblem Templates
7 = Invoicing
8 = Payment
9 = Residential
10 = Contract Product - Billing
11 = Contract Product - Pricing
12 = Contract Product - Logistics
13 = Contract Product - Miscellaneous
14 = Contract Product - Budget Protection
15 = Contract Product - ImageCare
16 = Emblem Template
17 = Wearer Functions
18 = Delivery
19 = Contract Special Charge
20 = Non-Garments - Charges';

ALTER TABLE CONTRACTATTRIBUTE ADD CONSTRAINT CHK_CONTRACTATT_GROUPNB CHECK (GROUPNUMBER BETWEEN 1 AND 20);


--- CONTRACTCOMPONENTGRMCHARGES 
COMMENT ON COLUMN CONTRACTCOMPONENTGRMCHARGES .CHARGERESIDUALVALUE
  IS 'Y/N (for garments)';

COMMENT ON COLUMN CONTRACTCOMPONENTGRMCHARGES .CHARGELOSSCHARGE
  IS 'Y/N. Default value N (for garments)';

-------CONTRACTCOMPONENTNONGRMCHARGES

CREATE TABLE CONTRACTCOMPONENTNONGRMCHARGES
(
  CONTRACT_ID NUMBER NOT NULL,
  CHARGERESIDUALVALUE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTRCOMPNGRMCH_CHRES CHECK (CHARGERESIDUALVALUE IN ('Y','N')),
  CHARGELOSSCHARGE VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CONTRCOMPNGRMCH_CHLOSS CHECK (CHARGELOSSCHARGE IN ('Y','N')),
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL
);

ALTER TABLE CONTRACTCOMPONENTNONGRMCHARGES
  ADD CONSTRAINT XPKCONTRCOMPNONGRMCH PRIMARY KEY (CONTRACT_ID);
ALTER TABLE CONTRACTCOMPONENTNONGRMCHARGES
  ADD CONSTRAINT FK_CONTRCOMPNONGRMCH_CONTR FOREIGN KEY (CONTRACT_ID)
  REFERENCES CONTRACT(CONTRACT_ID) ON DELETE CASCADE;

CREATE OR REPLACE PROCEDURE INSERT_CONTRACTCOMPNONGRMCHARG AS
STMT VARCHAR2(5000);
BEGIN
  FOR X IN (SELECT CCGC.CONTRACT_ID, CCGC.CHARGERESIDUALVALUE, CCGC.CHARGELOSSCHARGE FROM CONTRACTCOMPONENTGRMCHARGES CCGC WHERE CCGC.CONTRACT_ID NOT IN (SELECT CCNGC.CONTRACT_ID FROM CONTRACTCOMPONENTNONGRMCHARGES CCNGC)) LOOP
    BEGIN
STMT := 'INSERT INTO CONTRACTCOMPONENTNONGRMCHARGES
(CONTRACT_ID,
CHARGERESIDUALVALUE,
CHARGELOSSCHARGE,
SYSTEMUSER_ID,
TIMESTAMP)
      SELECT '||X.CONTRACT_ID||','||''''||X.CHARGERESIDUALVALUE||''''||','||''''|| X.CHARGELOSSCHARGE||''''||', GETMINSYSTEMUSER(), SYSDATE FROM DUAL';
 EXECUTE IMMEDIATE(STMT);
COMMIT;
    EXCEPTION WHEN OTHERS  THEN NULL;
END;
    END LOOP;
END INSERT_CONTRACTCOMPNONGRMCHARG;
/
EXECUTE INSERT_CONTRACTCOMPNONGRMCHARG ;

DROP PROCEDURE  INSERT_CONTRACTCOMPNONGRMCHARG;


