system.sql

来自「Java写的ERP系统」· SQL 代码 · 共 75 行

/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: System.sql,v 1.3 2003/01/06 03:06:44 jjanke Exp $
 ***
 * Title:	Create System
 * Description:
 *			Creates System Table
 *			Requires a "userName", which you get when you have a 
 *			support contract or purchased migration service.
 *			The script may case errors; 
 *			If you see 'Success' at the end, everything is OK
 *			
 * Usage:
 *			sqlplus compiere/compiere @System.sql userName
 ************************************************************************/

--	Create New System Table
CREATE TABLE AD_System(
    AD_System_ID    NUMBER(10, 0)     NOT NULL,
    AD_Client_ID    NUMBER(10, 0)     NOT NULL,
    AD_Org_ID       NUMBER(10, 0)     NOT NULL,
    IsActive        CHAR(1)           DEFAULT 'Y' NOT NULL
                    CHECK (IsActive in ('Y','N')),
    Created         DATE              DEFAULT SYSDATE NOT NULL,
    CreatedBy       NUMBER(10, 0)     NOT NULL,
    Updated         DATE              DEFAULT SYSDATE NOT NULL,
    UpdatedBy       NUMBER(10, 0)     NOT NULL,
    Name            NVARCHAR2(60)     NOT NULL,
    UserName        NVARCHAR2(60)     NOT NULL,
    Info            NVARCHAR2(255)    NOT NULL,
    Version         NVARCHAR2(20)     NOT NULL,
    CONSTRAINT AD_System_Key PRIMARY KEY (AD_System_ID)
    USING INDEX
TABLESPACE INDX
);

--	Create System Record
INSERT INTO AD_System
  (AD_System_ID,AD_Client_ID,AD_Org_ID, 
  IsActive,Created,CreatedBy,Updated,UpdatedBy,
  Name, UserName, Info, Version)
SELECT 	0,0,0,'Y', SysDate,0,SysDate,0, '-','-','-','?'
FROM 	Dual
WHERE NOT EXISTS 
  (SELECT * FROM AD_System WHERE AD_System_ID=0);

-- Add Info
UPDATE	AD_System
SET UserName='&1',
	Info = (SELECT SYS_CONTEXT('USERENV','DB_NAME')||'.'||SYS_CONTEXT('USERENV','DB_DOMAIN')
	|| ',IP=' || SYS_CONTEXT('USERENV','IP_ADDRESS')
	|| ',' || SYS_CONTEXT('USERENV','HOST')
	|| ',' || SYS_CONTEXT('USERENV','INSTANCE')
	|| ',UID=' || SYS_CONTEXT('USERENV','CURRENT_USER')
	|| ',' || SYS_CONTEXT('USERENV','CURRENT_USERID')
	|| ',C#=' || (SELECT COUNT(*) FROM AD_Client)
	FROM DUAL),
	Updated=SysDate;

--	Success Message
SELECT	'Success' AS "Result"
FROM	AD_System
WHERE	AD_System_ID=0;

--	Commit
COMMIT;

EXIT