SCRIPT()
/* =============================================  */
/*  NAME      :  D:\HASP_MM\Apps\Populate_ORMEDClosetLists.sql  */
/*  PROJECT   :  HASP  */
/*  AUTHOR    :  Allan Brocklehurst   */
/*  LAST MOD  :  2008-12-02 9:33AM  */
/*  ============================================== */

BEGIN

DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt ;
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name = ? ';
OPEN InfoCursor using 'ORMEDClosetLists' ;

IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE "ORMEDClosetLists" ';
END IF;

EXECUTE IMMEDIATE 'CREATE TABLE "ORMEDClosetLists"
(
"Institution" VARCHAR(20) COLLATE "ANSI",
"Organizational Unit" INTEGER COLLATE "ANSI",
"Description" VARCHAR(75) COLLATE "ANSI",
"ClosetID" VARCHAR(10) COLLATE "ANSI",
"ClosetName" VARCHAR(40) COLLATE "ANSI",
"Shelf" VARCHAR(10) COLLATE "ANSI",
"Bin" VARCHAR(10) COLLATE "ANSI",
"ItemID" VARCHAR(20) COLLATE "ANSI",
"Material" VARCHAR(20) COLLATE "ANSI",
"Plant" INTEGER COLLATE "ANSI",
"DHA" VARCHAR(10) COLLATE "ANSI",
"Max Stock Level" FLOAT,
"Order UOM" VARCHAR(10) COLLATE "ANSI",
"OID" GUID DEFAULT CURRENT_GUID,
"LastMod" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
VERSION 1
UNENCRYPTED
INDEX PAGE SIZE 4096
BLOB BLOCK SIZE 512
PUBLISH BLOCK SIZE 512
PUBLISH COMPRESSION 0
MAX ROW BUFFER SIZE 32768
MAX INDEX BUFFER SIZE 65536
MAX BLOB BUFFER SIZE 32768
MAX PUBLISH BUFFER SIZE 32768';


EXECUTE IMMEDIATE ' CREATE INDEX  "LastMod" ON "ORMEDClosetLists" ("LastMod"  COLLATE "ANSI")';
EXECUTE IMMEDIATE ' CREATE INDEX  "Institution" ON "ORMEDClosetLists" ("Institution"  COLLATE "ANSI")';
EXECUTE IMMEDIATE ' CREATE INDEX  "ClosetID" ON "ORMEDClosetLists" ("ClosetID"  COLLATE "ANSI")';
EXECUTE IMMEDIATE ' CREATE INDEX  "ClosetName" ON "ORMEDClosetLists" ("ClosetName"  COLLATE "ANSI")';
EXECUTE IMMEDIATE ' CREATE INDEX  "Mkey" ON "ORMEDClosetLists" ("Institution","ClosetID"  COLLATE "ANSI")';
EXECUTE IMMEDIATE ' CREATE INDEX  "ItemID" ON "ORMEDClosetLists" ("ItemID"  COLLATE "ANSI")';
EXECUTE IMMEDIATE ' CREATE INDEX  "Plant" ON "ORMEDClosetLists" ("Plant"  COLLATE "ANSI")';
EXECUTE IMMEDIATE ' ALTER TABLE  "ORMEDClosetLists" ADD CONSTRAINT "PrimaryKey" PRIMARY KEY ("OID")';

END
   