--Order 20012279.70

------------------------------NATIONALACCOUNT-------------------

ALTER TABLE NATIONALACCOUNT
	ADD (COUNTRY_ID NUMBER NULL,
	     SUBCONTRACTOR_ID NUMBER NULL,
	     BENEFICIARYTYPE NUMBER  DEFAULT 0 NOT NULL CONSTRAINT BENEFICIARYTYPE_valid CHECK (BENEFICIARYTYPE IN (0, 1, 2)),
	     CORPMANSTARTDATE DATE  DEFAULT to_date('31/12/2099','dd/mm/yyyy') NOT NULL,
	     BUSINESSUNIT_ID NUMBER NULL,
	     CONTRACTTYPE_ID NUMBER NULL,
	     SUBCONTRACT NUMBER  DEFAULT 0 NOT NULL CONSTRAINT SUBCONTRACT_valid CHECK (SUBCONTRACT IN (0, 1)),
	     CORPMANENDDATE DATE  DEFAULT to_date('31/12/2099','dd/mm/yyyy') NOT NULL,
	     MANAGEDBY NUMBER NULL,
	     ACTIVE VARCHAR2(1)  DEFAULT 'Y' NOT NULL CONSTRAINT ACTIVE_valid1 CHECK (ACTIVE IN ('Y', 'N')));



COMMENT ON COLUMN NATIONALACCOUNT.BENEFICIARYTYPE IS 'Indicates the beneficiary type. Possible values:
0 = Not applicable (default)
1 = On behalf of
2 = Provide to
';

COMMENT ON COLUMN NATIONALACCOUNT.SUBCONTRACT IS 'Indicates whether the account is serviced via a sub contract or not. Possible values:
0 = not a sub contract (default)
1 = sub contract
';

COMMENT ON COLUMN NATIONALACCOUNT.ACTIVE IS 'Indicates whether national account is active or not. Possible values:
‘N’ = not active (default)
‘Y’ = active 
';

ALTER TABLE NATIONALACCOUNT
ADD CONSTRAINT NATIONALACCOUNT_COUNTRY_ID FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRY (COUNTRY_ID);

CREATE INDEX XIF1NATIONALACCOUNT ON NATIONALACCOUNT
(COUNTRY_ID   ASC);

ALTER TABLE NATIONALACCOUNT
ADD CONSTRAINT NATIONALACCOUNT_BU_ID FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

CREATE INDEX XIF2NATIONALACCOUNT ON NATIONALACCOUNT
(BUSINESSUNIT_ID   ASC);

ALTER TABLE NATIONALACCOUNT
ADD CONSTRAINT NATIONALACCOUNT_CONTRACTTYPE FOREIGN KEY (CONTRACTTYPE_ID) REFERENCES CONTRACTTYPE (CONTRACTTYPE_ID);

CREATE INDEX XIF3NATIONALACCOUNT ON NATIONALACCOUNT
(CONTRACTTYPE_ID   ASC);


ALTER TABLE NATIONALACCOUNT
ADD CONSTRAINT NATIONALACCOUNT_COMPETITOR FOREIGN KEY (SUBCONTRACTOR_ID) REFERENCES COMPETITOR (COMPETITOR_ID);

CREATE INDEX XIF4NATIONALACCOUNT ON NATIONALACCOUNT
(SUBCONTRACTOR_ID   ASC);

ALTER TABLE NATIONALACCOUNT
ADD CONSTRAINT NATIONALACCOUNT_EMPLOYEE FOREIGN KEY (MANAGEDBY) REFERENCES EMPLOYEE (EMPLOYEE_ID);

CREATE INDEX XIF5NATIONALACCOUNT ON NATIONALACCOUNT
(MANAGEDBY   ASC);

CREATE UNIQUE INDEX XAK3NATIONALACCOUNT ON NATIONALACCOUNT
(NATIONALACCOUNT_ID   ASC,COUNTRY_ID   ASC);

ALTER TABLE NATIONALACCOUNT
ADD CONSTRAINT  XAK3NATIONALACCOUNT UNIQUE (NATIONALACCOUNT_ID,COUNTRY_ID);






