-- ============================================================================= -- Returnable Asset Management (RAM) — scan-driven asset tracking, Phase 2 -- Migration: 20260719 — RAM Phase 2 schema (PostgreSQL variant) -- Database: tenant business DB — companion _SqlServer.sql defines the -- identical logical schema for SQL Server hosts. -- Reference: RAM_Modernization_Design_Reference.md §4.1–§4.10 -- -- Conventions (per GB5 dual-dialect precedent — see Entitlement/DXP pairs): -- * TINYINT -> SMALLINT, NVARCHAR(n) -> VARCHAR(n), DATETIME -> TIMESTAMP, -- GETDATE() -> NOW(). INT kept as INT (Postgres accepts it as an alias). -- * New tables: CREATE TABLE IF NOT EXISTS. New columns on existing tables: -- ADD COLUMN IF NOT EXISTS. Constraint additions on existing tables -- (CHECK/FK on MASSETTYPE, MASSET) are plain ALTER ... ADD CONSTRAINT — -- run this file once; re-running a constraint-add line will error if it -- already exists (same operational assumption as the MCLIENT precedent's -- simpler ALTER migrations — no per-constraint guard). -- -- Same corrections as the SQL Server companion: -- * MBIN.BINID is INT — MASSET.CURRENTBINID below is INT, not VARCHAR(20). -- * MSCANIDENTIFIER.ENTITYID stays VARCHAR(50) for forward generality. -- * MBIZTRANSACTIONTYPE/CLASS already exist — no new master table here. -- ============================================================================= -- ───────────────────────────────────────────────────────────────────────────── -- 1. MASSETTYPE — two opt-in flags + cycle-threshold config -- ───────────────────────────────────────────────────────────────────────────── ALTER TABLE MASSETTYPE ADD COLUMN IF NOT EXISTS ISSCANTRACKED SMALLINT NOT NULL DEFAULT 1; ALTER TABLE MASSETTYPE ADD COLUMN IF NOT EXISTS ISSTATEMACHINEENABLED SMALLINT NOT NULL DEFAULT 1; ALTER TABLE MASSETTYPE ADD CONSTRAINT CK_MASSETTYPE_STATEMACHINEREQUIRESSCAN CHECK (ISSTATEMACHINEENABLED = 1 OR ISSCANTRACKED = 0); ALTER TABLE MASSETTYPE ADD COLUMN IF NOT EXISTS CLEANINGCYCLETHRESHOLD INT NULL; ALTER TABLE MASSETTYPE ADD COLUMN IF NOT EXISTS DEEPCYCLETHRESHOLD INT NULL; ALTER TABLE MASSETTYPE ADD COLUMN IF NOT EXISTS DEFAULTDETENTIONDAYS INT NULL; -- ───────────────────────────────────────────────────────────────────────────── -- 2. MDEVICETYPE — scan-technology tag -- ───────────────────────────────────────────────────────────────────────────── ALTER TABLE MDEVICETYPE ADD COLUMN IF NOT EXISTS SCANTECHNOLOGY VARCHAR(50) NULL; -- ───────────────────────────────────────────────────────────────────────────── -- 3. MSCANIDENTIFIER -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS MSCANIDENTIFIER ( SCANIDENTIFIERID INT PRIMARY KEY, IDENTIFIERTYPE SMALLINT NOT NULL, IDENTIFIERVALUE VARCHAR(256) NOT NULL, ENTITYTYPE VARCHAR(50) NOT NULL, ENTITYID VARCHAR(50) NOT NULL, ISPRIMARY SMALLINT NOT NULL DEFAULT 0, VALIDFROM TIMESTAMP NOT NULL DEFAULT NOW(), VALIDTO TIMESTAMP NULL, SORTORDER INT NOT NULL DEFAULT 9999, STATUS SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, CREATEDBYID INT NOT NULL, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, OUID INT NOT NULL, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT UK_MSCANIDENTIFIER_VALUE UNIQUE(IDENTIFIERVALUE, TENANTID), CONSTRAINT FK_MSCANIDENTIFIER_CREATEDBYID FOREIGN KEY(CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MSCANIDENTIFIER_MODIFIEDBYID FOREIGN KEY(MODIFIEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MSCANIDENTIFIER_OUID FOREIGN KEY(OUID) REFERENCES MORGANIZATIONUNIT(OUID), CONSTRAINT FK_MSCANIDENTIFIER_TENANTID FOREIGN KEY(TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_MSCANIDENTIFIER_IDENTIFIERTYPE CHECK (IDENTIFIERTYPE IN (0,1,2,3,4,5)), CONSTRAINT CK_MSCANIDENTIFIER_ISPRIMARY CHECK (ISPRIMARY IN (0,1)), CONSTRAINT CK_MSCANIDENTIFIER_STATUS CHECK (STATUS IN (0,1,2,3,4,5)), CONSTRAINT CK_MSCANIDENTIFIER_SOURCETYPE CHECK (SOURCETYPE IN (1,2,3,4,5)) ); -- ───────────────────────────────────────────────────────────────────────────── -- 4. MSCANZONE -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS MSCANZONE ( SCANZONEID INT PRIMARY KEY, ZONECODE VARCHAR(30) NOT NULL, ZONENAME VARCHAR(100) NOT NULL, PARTYBRANCHLOCATIONID INT NULL, DEFAULTSCANINTENT SMALLINT NOT NULL DEFAULT 2, TXPOWERDBM SMALLINT NULL, RSSIMINIMUM SMALLINT NULL, REQUIRECONFIRM SMALLINT NOT NULL DEFAULT 1, SORTORDER INT NOT NULL DEFAULT 9999, STATUS SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, CREATEDBYID INT NOT NULL, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, OUID INT NOT NULL, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT UK_MSCANZONE_ZONECODE UNIQUE(ZONECODE, TENANTID), CONSTRAINT FK_MSCANZONE_PARTYBRANCHLOCATIONID FOREIGN KEY(PARTYBRANCHLOCATIONID) REFERENCES MPARTYBRANCHLOCATION(PARTYBRANCHLOCATIONID), CONSTRAINT FK_MSCANZONE_CREATEDBYID FOREIGN KEY(CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MSCANZONE_MODIFIEDBYID FOREIGN KEY(MODIFIEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MSCANZONE_OUID FOREIGN KEY(OUID) REFERENCES MORGANIZATIONUNIT(OUID), CONSTRAINT FK_MSCANZONE_TENANTID FOREIGN KEY(TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_MSCANZONE_DEFAULTSCANINTENT CHECK (DEFAULTSCANINTENT IN (0,1,2,3)), CONSTRAINT CK_MSCANZONE_REQUIRECONFIRM CHECK (REQUIRECONFIRM IN (0,1)), CONSTRAINT CK_MSCANZONE_STATUS CHECK (STATUS IN (0,1,2,3,4,5)), CONSTRAINT CK_MSCANZONE_SOURCETYPE CHECK (SOURCETYPE IN (1,2,3,4,5)) ); -- ───────────────────────────────────────────────────────────────────────────── -- 5. TSCANSESSION -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS TSCANSESSION ( SCANSESSIONID INT PRIMARY KEY, SESSIONCODE VARCHAR(50) NOT NULL, SCANZONEID INT NOT NULL, SCANDEVICEID INT NOT NULL, OPERATORID INT NOT NULL, SESSIONTYPE SMALLINT NOT NULL DEFAULT 0, SCANINTENT SMALLINT NOT NULL, MODULEID INT NOT NULL, BIZTRANSACTIONTYPEID INT NULL, REFERENCETYPE VARCHAR(30) NULL, REFERENCEID INT NULL, TOTALSCANNED INT NOT NULL DEFAULT 0, TOTALACCEPTED INT NOT NULL DEFAULT 0, TOTALREJECTED INT NOT NULL DEFAULT 0, SESSIONSTATUS SMALLINT NOT NULL DEFAULT 0, ISOFFLINE SMALLINT NOT NULL DEFAULT 1, SYNCEDON TIMESTAMP NULL, STARTEDON TIMESTAMP NOT NULL DEFAULT NOW(), CONFIRMEDON TIMESTAMP NULL, SORTORDER INT NOT NULL DEFAULT 9999, STATUS SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, CREATEDBYID INT NOT NULL, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), SOURCETYPE SMALLINT NOT NULL DEFAULT 5, OUID INT NOT NULL, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT UK_TSCANSESSION_SESSIONCODE UNIQUE(SESSIONCODE, TENANTID), CONSTRAINT FK_TSCANSESSION_SCANZONEID FOREIGN KEY(SCANZONEID) REFERENCES MSCANZONE(SCANZONEID), CONSTRAINT FK_TSCANSESSION_SCANDEVICEID FOREIGN KEY(SCANDEVICEID) REFERENCES MDEVICE(DEVICEID), CONSTRAINT FK_TSCANSESSION_OPERATORID FOREIGN KEY(OPERATORID) REFERENCES MUSER(USERID), CONSTRAINT FK_TSCANSESSION_CREATEDBYID FOREIGN KEY(CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_TSCANSESSION_MODIFIEDBYID FOREIGN KEY(MODIFIEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_TSCANSESSION_OUID FOREIGN KEY(OUID) REFERENCES MORGANIZATIONUNIT(OUID), CONSTRAINT FK_TSCANSESSION_TENANTID FOREIGN KEY(TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_TSCANSESSION_SESSIONTYPE CHECK (SESSIONTYPE IN (0,1)), CONSTRAINT CK_TSCANSESSION_SCANINTENT CHECK (SCANINTENT IN (0,1,2,3)), CONSTRAINT CK_TSCANSESSION_SESSIONSTATUS CHECK (SESSIONSTATUS IN (0,1,2)), CONSTRAINT CK_TSCANSESSION_ISOFFLINE CHECK (ISOFFLINE IN (0,1)), CONSTRAINT CK_TSCANSESSION_STATUS CHECK (STATUS IN (0,1,2,3,4,5)), CONSTRAINT CK_TSCANSESSION_SOURCETYPE CHECK (SOURCETYPE IN (1,2,3,4,5)) ); -- ───────────────────────────────────────────────────────────────────────────── -- 6. TSCANEVENT -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS TSCANEVENT ( SCANEVENTID INT PRIMARY KEY, SCANSESSIONID INT NOT NULL, SCANIDENTIFIERID INT NULL, RAWSCANNEDVALUE VARCHAR(256) NOT NULL, IDENTIFIERTYPE SMALLINT NOT NULL, SCANDEVICEID INT NOT NULL, SCANZONEID INT NOT NULL, OPERATORID INT NOT NULL, ENTITYTYPE VARCHAR(50) NULL, ENTITYID VARCHAR(50) NULL, SCANINTENT SMALLINT NOT NULL, SCANRESULT SMALLINT NOT NULL, REJECTREASONCODE VARCHAR(50) NULL, RSSI SMALLINT NULL, ISOFFLINE SMALLINT NOT NULL DEFAULT 1, DEVICETIMESTAMP TIMESTAMP NOT NULL, SERVERTIMESTAMP TIMESTAMP NULL, SYNCEDON TIMESTAMP NULL, OUID INT NOT NULL, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT FK_TSCANEVENT_SCANSESSIONID FOREIGN KEY(SCANSESSIONID) REFERENCES TSCANSESSION(SCANSESSIONID), CONSTRAINT FK_TSCANEVENT_SCANIDENTIFIERID FOREIGN KEY(SCANIDENTIFIERID) REFERENCES MSCANIDENTIFIER(SCANIDENTIFIERID), CONSTRAINT FK_TSCANEVENT_SCANDEVICEID FOREIGN KEY(SCANDEVICEID) REFERENCES MDEVICE(DEVICEID), CONSTRAINT FK_TSCANEVENT_SCANZONEID FOREIGN KEY(SCANZONEID) REFERENCES MSCANZONE(SCANZONEID), CONSTRAINT FK_TSCANEVENT_OPERATORID FOREIGN KEY(OPERATORID) REFERENCES MUSER(USERID), CONSTRAINT FK_TSCANEVENT_OUID FOREIGN KEY(OUID) REFERENCES MORGANIZATIONUNIT(OUID), CONSTRAINT FK_TSCANEVENT_TENANTID FOREIGN KEY(TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_TSCANEVENT_SCANINTENT CHECK (SCANINTENT IN (0,1,2,3)), CONSTRAINT CK_TSCANEVENT_SCANRESULT CHECK (SCANRESULT IN (0,1,2,3)), CONSTRAINT CK_TSCANEVENT_ISOFFLINE CHECK (ISOFFLINE IN (0,1)) ); -- ───────────────────────────────────────────────────────────────────────────── -- 7. MASSETSTATE -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS MASSETSTATE ( ASSETSTATEID INT PRIMARY KEY, ASSETTYPEID INT NOT NULL, STATECODE VARCHAR(30) NOT NULL, STATENAME VARCHAR(100) NOT NULL, STATEDESCRIPTION VARCHAR(500) NULL, ISREQUIRESSCAN SMALLINT NOT NULL DEFAULT 0, ISALLOWSSTOCK SMALLINT NOT NULL DEFAULT 1, ISTERMINAL SMALLINT NOT NULL DEFAULT 1, ALLOWEDTRANSITIONS VARCHAR(500) NULL, TRIGGERZONEID VARCHAR(200) NULL, SORTORDER INT NOT NULL DEFAULT 9999, STATUS SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, CREATEDBYID INT NOT NULL, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), OUID INT NOT NULL, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT UK_MASSETSTATE_TYPESTATECODE UNIQUE(ASSETTYPEID, STATECODE, TENANTID), CONSTRAINT FK_MASSETSTATE_ASSETTYPEID FOREIGN KEY(ASSETTYPEID) REFERENCES MASSETTYPE(ASSETTYPEID), CONSTRAINT FK_MASSETSTATE_CREATEDBYID FOREIGN KEY(CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MASSETSTATE_MODIFIEDBYID FOREIGN KEY(MODIFIEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MASSETSTATE_OUID FOREIGN KEY(OUID) REFERENCES MORGANIZATIONUNIT(OUID), CONSTRAINT FK_MASSETSTATE_TENANTID FOREIGN KEY(TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_MASSETSTATE_ISREQUIRESSCAN CHECK (ISREQUIRESSCAN IN (0,1)), CONSTRAINT CK_MASSETSTATE_ISALLOWSSTOCK CHECK (ISALLOWSSTOCK IN (0,1)), CONSTRAINT CK_MASSETSTATE_ISTERMINAL CHECK (ISTERMINAL IN (0,1)), CONSTRAINT CK_MASSETSTATE_STATUS CHECK (STATUS IN (0,1,2,3,4,5)) ); -- ───────────────────────────────────────────────────────────────────────────── -- 8. MASSET — position/custody/state snapshot columns -- (requires MASSETSTATE from step 7, and MBIN — pre-existing — for the FK) -- ───────────────────────────────────────────────────────────────────────────── ALTER TABLE MASSET ADD COLUMN IF NOT EXISTS CURRENTBINID INT NULL; ALTER TABLE MASSET ADD CONSTRAINT FK_MASSET_CURRENTBINID FOREIGN KEY(CURRENTBINID) REFERENCES MBIN(BINID); ALTER TABLE MASSET ADD COLUMN IF NOT EXISTS CURRENTLOCATIONID INT NULL; ALTER TABLE MASSET ADD COLUMN IF NOT EXISTS CURRENTCUSTODIANTYPE SMALLINT NULL; ALTER TABLE MASSET ADD CONSTRAINT CK_MASSET_CURRENTCUSTODIANTYPE CHECK (CURRENTCUSTODIANTYPE IN (0,1,2,3)); ALTER TABLE MASSET ADD COLUMN IF NOT EXISTS CURRENTCUSTODIANID INT NULL; ALTER TABLE MASSET ADD COLUMN IF NOT EXISTS CURRENTASSETSTATEID INT NULL; ALTER TABLE MASSET ADD CONSTRAINT FK_MASSET_CURRENTASSETSTATEID FOREIGN KEY(CURRENTASSETSTATEID) REFERENCES MASSETSTATE(ASSETSTATEID); ALTER TABLE MASSET ADD COLUMN IF NOT EXISTS LASTMOVEMENTON TIMESTAMP NULL; ALTER TABLE MASSET ADD COLUMN IF NOT EXISTS CYCLECOUNT INT NOT NULL DEFAULT 0; -- ───────────────────────────────────────────────────────────────────────────── -- 9. TASSETLEDGER -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS TASSETLEDGER ( ASSETLEDGERID INT PRIMARY KEY, OUID INT NOT NULL, ASSETID INT NOT NULL, BIZTRANSACTIONTYPEID INT NOT NULL, ASSETLEDGERNUMBER VARCHAR(50) NOT NULL, ASSETLEDGERDATE DATE NOT NULL, FROMSTATEID INT NULL, TOSTATEID INT NULL, FROMCUSTODIANTYPE SMALLINT NULL, FROMCUSTODIANID INT NULL, TOCUSTODIANTYPE SMALLINT NULL, TOCUSTODIANID INT NULL, FROMLOCATIONID INT NULL, TOLOCATIONID INT NULL, SCANEVENTID INT NULL, SCANSESSIONID INT NULL, REFERENCETYPE VARCHAR(30) NULL, REFERENCEID INT NULL, EXPECTEDRETURNDATE TIMESTAMP NULL, ACTUALRETURNDATE TIMESTAMP NULL, CYCLECOUNT INT NULL, OPERATORID INT NOT NULL, REMARKS VARCHAR(500) NULL, STATUS SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, CREATEDBYID INT NOT NULL, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), EVENTTIME TIMESTAMP NOT NULL DEFAULT NOW(), TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT FK_TASSETLEDGER_ASSETID FOREIGN KEY(ASSETID) REFERENCES MASSET(ASSETID), CONSTRAINT FK_TASSETLEDGER_FROMSTATEID FOREIGN KEY(FROMSTATEID) REFERENCES MASSETSTATE(ASSETSTATEID), CONSTRAINT FK_TASSETLEDGER_TOSTATEID FOREIGN KEY(TOSTATEID) REFERENCES MASSETSTATE(ASSETSTATEID), CONSTRAINT FK_TASSETLEDGER_SCANEVENTID FOREIGN KEY(SCANEVENTID) REFERENCES TSCANEVENT(SCANEVENTID), CONSTRAINT FK_TASSETLEDGER_SCANSESSIONID FOREIGN KEY(SCANSESSIONID) REFERENCES TSCANSESSION(SCANSESSIONID), CONSTRAINT FK_TASSETLEDGER_OPERATORID FOREIGN KEY(OPERATORID) REFERENCES MUSER(USERID), CONSTRAINT FK_TASSETLEDGER_CREATEDBYID FOREIGN KEY(CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_TASSETLEDGER_OUID FOREIGN KEY(OUID) REFERENCES MORGANIZATIONUNIT(OUID), CONSTRAINT FK_TASSETLEDGER_TENANTID FOREIGN KEY(TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_TASSETLEDGER_FROMCUSTODIANTYPE CHECK (FROMCUSTODIANTYPE IN (0,1,2,3)), CONSTRAINT CK_TASSETLEDGER_TOCUSTODIANTYPE CHECK (TOCUSTODIANTYPE IN (0,1,2,3)), CONSTRAINT CK_TASSETLEDGER_STATUS CHECK (STATUS IN (0,1,2,3,4,5)) ); -- ───────────────────────────────────────────────────────────────────────────── -- 10. MASSETPOOL -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS MASSETPOOL ( ASSETPOOLID INT PRIMARY KEY, POOLCODE VARCHAR(30) NOT NULL, POOLNAME VARCHAR(100) NOT NULL, PARTYBRANCHLOCATIONID INT NULL, TARGETCOUNT INT NULL, DESCRIPTION VARCHAR(500) NULL, SORTORDER INT NOT NULL DEFAULT 9999, STATUS SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, CREATEDBYID INT NOT NULL, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), OUID INT NOT NULL, TENANTID INT NOT NULL DEFAULT -1, CONSTRAINT UK_MASSETPOOL_POOLCODE UNIQUE(POOLCODE, TENANTID), CONSTRAINT FK_MASSETPOOL_PARTYBRANCHLOCATIONID FOREIGN KEY(PARTYBRANCHLOCATIONID) REFERENCES MPARTYBRANCHLOCATION(PARTYBRANCHLOCATIONID), CONSTRAINT FK_MASSETPOOL_CREATEDBYID FOREIGN KEY(CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MASSETPOOL_MODIFIEDBYID FOREIGN KEY(MODIFIEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MASSETPOOL_OUID FOREIGN KEY(OUID) REFERENCES MORGANIZATIONUNIT(OUID), CONSTRAINT FK_MASSETPOOL_TENANTID FOREIGN KEY(TENANTID) REFERENCES MCLIENT(CLIENTID), CONSTRAINT CK_MASSETPOOL_STATUS CHECK (STATUS IN (0,1,2,3,4,5)) ); -- ───────────────────────────────────────────────────────────────────────────── -- 11. MASSETPOOLDETAIL -- ───────────────────────────────────────────────────────────────────────────── CREATE TABLE IF NOT EXISTS MASSETPOOLDETAIL ( ASSETPOOLDETAILID INT PRIMARY KEY, ASSETPOOLID INT NOT NULL, SLNO SMALLINT NOT NULL, ASSETID INT NOT NULL, VALIDFROM TIMESTAMP NOT NULL DEFAULT NOW(), VALIDTO TIMESTAMP NULL, STATUS SMALLINT NOT NULL DEFAULT 1, VERSION SMALLINT NOT NULL DEFAULT 0, CREATEDBYID INT NOT NULL, CREATEDON TIMESTAMP NOT NULL DEFAULT NOW(), MODIFIEDBYID INT NOT NULL, MODIFIEDON TIMESTAMP NOT NULL DEFAULT NOW(), CONSTRAINT FK_MASSETPOOLDETAIL_ASSETPOOLID FOREIGN KEY(ASSETPOOLID) REFERENCES MASSETPOOL(ASSETPOOLID), CONSTRAINT FK_MASSETPOOLDETAIL_ASSETID FOREIGN KEY(ASSETID) REFERENCES MASSET(ASSETID), CONSTRAINT FK_MASSETPOOLDETAIL_CREATEDBYID FOREIGN KEY(CREATEDBYID) REFERENCES MUSER(USERID), CONSTRAINT FK_MASSETPOOLDETAIL_MODIFIEDBYID FOREIGN KEY(MODIFIEDBYID) REFERENCES MUSER(USERID), CONSTRAINT UK_MASSETPOOLDETAIL_POOLASSETVALIDFROM UNIQUE(ASSETPOOLID, ASSETID, VALIDFROM), CONSTRAINT CK_MASSETPOOLDETAIL_STATUS CHECK (STATUS IN (0,1,2,3,4,5)) ); -- ============================================================================= -- END OF MIGRATION 20260719_RAM_Phase2_Schema (PostgreSQL variant) -- =============================================================================