compiere.sql

Java写的ERP系统

;
DROP TABLE M_DiscountSchema CASCADE CONSTRAINTS
;
DROP TABLE M_DiscountSchemaBreak CASCADE CONSTRAINTS
;
DROP TABLE M_DiscountSchemaLine CASCADE CONSTRAINTS
;
DROP TABLE M_EDI CASCADE CONSTRAINTS
;
DROP TABLE M_EDI_Info CASCADE CONSTRAINTS
;
DROP TABLE M_InOut CASCADE CONSTRAINTS
;
DROP TABLE M_InOutLine CASCADE CONSTRAINTS
;
DROP TABLE M_Inventory CASCADE CONSTRAINTS
;
DROP TABLE M_InventoryLine CASCADE CONSTRAINTS
;
DROP TABLE M_Locator CASCADE CONSTRAINTS
;
DROP TABLE M_MatchInv CASCADE CONSTRAINTS
;
DROP TABLE M_MatchPO CASCADE CONSTRAINTS
;
DROP TABLE M_Movement CASCADE CONSTRAINTS
;
DROP TABLE M_MovementLine CASCADE CONSTRAINTS
;
DROP TABLE M_PerpetualInv CASCADE CONSTRAINTS
;
DROP TABLE M_PriceList CASCADE CONSTRAINTS
;
DROP TABLE M_PriceList_Version CASCADE CONSTRAINTS
;
DROP TABLE M_Product CASCADE CONSTRAINTS
;
DROP TABLE M_Production CASCADE CONSTRAINTS
;
DROP TABLE M_ProductionLine CASCADE CONSTRAINTS
;
DROP TABLE M_ProductionPlan CASCADE CONSTRAINTS
;
DROP TABLE M_ProductPrice CASCADE CONSTRAINTS
;
DROP TABLE M_Product_Acct CASCADE CONSTRAINTS
;
DROP TABLE M_Product_BOM CASCADE CONSTRAINTS
;
DROP TABLE M_Product_Category CASCADE CONSTRAINTS
;
DROP TABLE M_Product_Category_Acct CASCADE CONSTRAINTS
;
DROP TABLE M_Product_Costing CASCADE CONSTRAINTS
;
DROP TABLE M_Product_PO CASCADE CONSTRAINTS
;
DROP TABLE M_Product_Trl CASCADE CONSTRAINTS
;
DROP TABLE M_Replenish CASCADE CONSTRAINTS
;
DROP TABLE M_Shipper CASCADE CONSTRAINTS
;
DROP TABLE M_Storage CASCADE CONSTRAINTS
;
DROP TABLE M_Substitute CASCADE CONSTRAINTS
;
DROP TABLE M_Transaction CASCADE CONSTRAINTS
;
DROP TABLE M_Warehouse CASCADE CONSTRAINTS
;
DROP TABLE M_Warehouse_Acct CASCADE CONSTRAINTS
;
DROP TABLE PA_Achievement CASCADE CONSTRAINTS
;
DROP TABLE PA_Color CASCADE CONSTRAINTS
;
DROP TABLE PA_Goal CASCADE CONSTRAINTS
;
DROP TABLE PA_Measure CASCADE CONSTRAINTS
;
DROP TABLE PA_MeasureCalc CASCADE CONSTRAINTS
;
DROP TABLE PA_Report CASCADE CONSTRAINTS
;
DROP TABLE PA_ReportColumn CASCADE CONSTRAINTS
;
DROP TABLE PA_ReportColumnSet CASCADE CONSTRAINTS
;
DROP TABLE PA_ReportLine CASCADE CONSTRAINTS
;
DROP TABLE PA_ReportLineSet CASCADE CONSTRAINTS
;
DROP TABLE PA_ReportSource CASCADE CONSTRAINTS
;
DROP TABLE R_ContactInterest CASCADE CONSTRAINTS
;
DROP TABLE R_InterestArea CASCADE CONSTRAINTS
;
DROP TABLE R_MailText CASCADE CONSTRAINTS
;
DROP TABLE R_Request CASCADE CONSTRAINTS
;
DROP TABLE R_RequestAction CASCADE CONSTRAINTS
;
DROP TABLE R_RequestProcessor CASCADE CONSTRAINTS
;
DROP TABLE R_RequestProcessor_Route CASCADE CONSTRAINTS
;
DROP TABLE R_RequestType CASCADE CONSTRAINTS
;
DROP TABLE S_ExpenseType CASCADE CONSTRAINTS
;
DROP TABLE S_Resource CASCADE CONSTRAINTS
;
DROP TABLE S_ResourceAssignment CASCADE CONSTRAINTS
;
DROP TABLE S_ResourceType CASCADE CONSTRAINTS
;
DROP TABLE S_ResourceUnAvailable CASCADE CONSTRAINTS
;
DROP TABLE S_TimeExpense CASCADE CONSTRAINTS
;
DROP TABLE S_TimeExpenseLine CASCADE CONSTRAINTS
;
DROP TABLE S_Training CASCADE CONSTRAINTS
;
DROP TABLE S_Training_Class CASCADE CONSTRAINTS
;
DROP TABLE TIRE_Storage CASCADE CONSTRAINTS
;
DROP TABLE T_InventoryValue CASCADE CONSTRAINTS
;
DROP TABLE T_Replenish CASCADE CONSTRAINTS
;
DROP TABLE T_Report CASCADE CONSTRAINTS
;
DROP TABLE T_Selection CASCADE CONSTRAINTS
;
DROP TABLE T_Selection2 CASCADE CONSTRAINTS
;
DROP TABLE T_Spool CASCADE CONSTRAINTS
;
DROP TABLE W_Basket CASCADE CONSTRAINTS
;
DROP TABLE W_Counter CASCADE CONSTRAINTS
;
DROP TABLE W_Request CASCADE CONSTRAINTS
;
-- 
-- TABLE: AD_Attachment 
--

CREATE TABLE AD_Attachment(
	AD_Attachment_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,
	Created				DATE				DEFAULT SYSDATE NOT NULL,
	CreatedBy			NUMBER(10, 0)	   NOT NULL,
	Updated				DATE				DEFAULT SYSDATE NOT NULL,
	UpdatedBy			NUMBER(10, 0)	   NOT NULL,
	AD_Table_ID			NUMBER(10, 0)	   NOT NULL,
	Record_ID			NUMBER(10, 0)	   NOT NULL,
	Title				NVARCHAR2(60)	   NOT NULL,
	Text				NVARCHAR2(2000),
	BinaryData			BLOB,
	CHECK (IsActive in ('Y','N')),
	CONSTRAINT AD_Attachment_Key PRIMARY KEY (AD_Attachment_ID)
) 
;


-- 
-- TABLE: AD_Attribute 
--

CREATE TABLE AD_Attribute(
	AD_Attribute_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,
	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,
	Description				 NVARCHAR2(255),
	Help					 NVARCHAR2(2000),
	AD_Table_ID				 NUMBER(10, 0)		NOT NULL,
	AD_Reference_ID			 NUMBER(10, 0)		NOT NULL,
	AD_Reference_Value_ID	 NUMBER(10, 0),
	AD_Val_Rule_ID			 NUMBER(10, 0),
	Callout					 NVARCHAR2(60),
	ValueMin				 NVARCHAR2(20),
	ValueMax				 NVARCHAR2(20),
	DefaultValue			 NVARCHAR2(2000),
	IsReadOnly				 CHAR(1)			 DEFAULT 'N' NOT NULL,
	IsUpdateable			 CHAR(1)			 DEFAULT 'Y' NOT NULL,
	IsMandatory				 CHAR(1)			 DEFAULT 'N' NOT NULL,
	IsEncrypted				 CHAR(1)			 DEFAULT 'N' NOT NULL,
	FieldLength				 NUMBER(10, 0),
	DisplayLength			 NUMBER(10, 0),
	SeqNo					 NUMBER(10, 0),
	DisplayLogic			 NVARCHAR2(2000),
	VFormat					 NVARCHAR2(60),
	IsSameLine				 CHAR(1)			 DEFAULT 'N' NOT NULL,
	IsHeading				 CHAR(1)			 DEFAULT 'N' NOT NULL,
	IsFieldOnly				 CHAR(1)			 DEFAULT 'N' NOT NULL,
	CHECK (IsActive in ('Y','N')),
	CHECK (IsReadOnly in ('Y','N')),
	CHECK (IsUpdateable in ('Y','N')),
	CHECK (IsMandatory in ('Y','N')),
	CHECK (IsEncrypted in ('Y','N')),
	CHECK (IsSameLine in ('Y','N')),
	CHECK (IsHeading in ('Y','N')),
	CHECK (IsFieldOnly in ('Y','N')),
	CONSTRAINT AD_Attribute_Key PRIMARY KEY (AD_Attribute_ID)
) 
;


COMMENT ON TABLE AD_Attribute IS 'User Defined Attributes'
;
COMMENT ON COLUMN AD_Attribute.AD_Table_ID IS 'AD_Table_ID Definition'
;
COMMENT ON COLUMN AD_Attribute.FieldLength IS 'Only for VARCHAR and CHAR'
;
-- 
-- TABLE: AD_Attribute_Value 
--

CREATE TABLE AD_Attribute_Value(
	AD_Attribute_ID	   NUMBER(10, 0)	  NOT NULL,
	Record_ID		   NUMBER(10, 0)	  NOT NULL,
	V_Number		   NUMBER,
	V_Date			   DATE,
	V_String		   NVARCHAR2(2000),
	CONSTRAINT AD_Attribute_Value_Key PRIMARY KEY (AD_Attribute_ID, Record_ID)
) 
;


COMMENT ON TABLE AD_Attribute_Value IS 'Values for user defined attributes'
;
-- 
-- TABLE: AD_ClientInfo 
--

CREATE TABLE AD_ClientInfo(
	AD_Client_ID			  NUMBER(10, 0)	   NOT NULL,
	AD_Org_ID				  NUMBER(10, 0)	   NOT NULL,
	IsActive				  CHAR(1)			DEFAULT 'Y' NOT NULL,
	Created					  DATE				DEFAULT SYSDATE NOT NULL,
	CreatedBy				  NUMBER(10, 0)	   NOT NULL,
	Updated					  DATE				DEFAULT SYSDATE NOT NULL,
	UpdatedBy				  NUMBER(10, 0)	   NOT NULL,
	IsDiscountLineAmt		  CHAR(1)			DEFAULT 'Y' NOT NULL,
	C_Calendar_ID			  NUMBER(10, 0),
	C_AcctSchema1_ID		  NUMBER(10, 0),
	Acct2_Active			  CHAR(1)			DEFAULT 'N' NOT NULL,
	C_AcctSchema2_ID		  NUMBER(10, 0),
	Acct3_Active			  CHAR(1)			DEFAULT 'N' NOT NULL,
	C_AcctSchema3_ID		  NUMBER(10, 0),
	C_UOM_Volume_ID			  NUMBER(10, 0),
	C_UOM_Weight_ID			  NUMBER(10, 0),
	C_UOM_Length_ID			  NUMBER(10, 0),
	C_UOM_Time_ID			  NUMBER(10, 0),
	AD_Tree_Menu_ID			  NUMBER(10, 0),
	AD_Tree_Org_ID			  NUMBER(10, 0),
	AD_Tree_BPartner_ID		  NUMBER(10, 0),
	AD_Tree_Project_ID		  NUMBER(10, 0),
	AD_Tree_SalesRegion_ID	  NUMBER(10, 0),
	AD_Tree_Product_ID		  NUMBER(10, 0),
	M_ProductFreight_ID		  NUMBER(10, 0),
	C_BPartnerCashTrx_ID	  NUMBER(10, 0),
	PA_Goal_ID				  NUMBER(10, 0),
	CHECK (IsActive in ('Y','N')),
	CHECK (IsDiscountLineAmt in ('Y','N')),
	CHECK (Acct2_Active in ('Y','N')),
	CHECK (Acct3_Active in ('Y','N')),
	CONSTRAINT AD_ClientInfo_Key PRIMARY KEY (AD_Client_ID)
) 
;


COMMENT ON COLUMN AD_ClientInfo.M_ProductFreight_ID IS 'Default Product for Freightcosts'
;
-- 
-- TABLE: AD_Color 
--

CREATE TABLE AD_Color(
	AD_Color_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,
	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,
	IsDefault		  CHAR(1)			DEFAULT 'N' NOT NULL,
	ColorType		  CHAR(1)		   NOT NULL,
	Red				  NUMBER(10, 0)	   NOT NULL,
	Green			  NUMBER(10, 0)	   NOT NULL,
	Blue			  NUMBER(10, 0)	   NOT NULL,
	Alpha			  NUMBER(10, 0)	   NOT NULL,
	AD_Image_ID		  NUMBER(10, 0),
	ImageAlpha		  NUMBER		   NOT NULL,
	Red_1			  NUMBER(10, 0),
	Green_1			  NUMBER(10, 0),
	Blue_1			  NUMBER(10, 0),
	Alpha_1			  NUMBER(10, 0),
	LineWidth		  NUMBER(10, 0),
	LineDistance	  NUMBER(10, 0),
	StartPoint		  NUMBER(10, 0),
	RepeatDistance	  NUMBER(10, 0),
	CHECK (IsActive in ('Y','N')),
	CONSTRAINT AD_Color_Key PRIMARY KEY (AD_Color_ID)
) 
;


COMMENT ON COLUMN AD_Color.ColorType IS 'Normal - Gradient - Line - Picture'
;
-- 
-- TABLE: AD_Desktop 
--

CREATE TABLE AD_Desktop(
	AD_Desktop_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,
	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,
	Description		 NVARCHAR2(255),
	Help			 NVARCHAR2(2000),
	AD_Image_ID		 NUMBER(10, 0),
	AD_Color_ID		 NUMBER(10, 0),
	CHECK (IsActive in ('Y','N')),
	CONSTRAINT AD_Desktop_Key PRIMARY KEY (AD_Desktop_ID)
) 
;


-- 
-- TABLE: AD_DesktopWorkbench 
--

CREATE TABLE AD_DesktopWorkbench(
	AD_DesktopWorkbench_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,
	Created					  DATE				DEFAULT SYSDATE NOT NULL,
	CreatedBy				  NUMBER(10, 0)	   NOT NULL,
	Updated					  DATE				DEFAULT SYSDATE NOT NULL,
	UpdatedBy				  NUMBER(10, 0)	   NOT NULL,
	AD_Workbench_ID			  NUMBER(10, 0)	   NOT NULL,
	AD_Desktop_ID			  NUMBER(10, 0)	   NOT NULL,
	SeqNo					  NUMBER(10, 0)	   NOT NULL,
	CHECK (IsActive in ('Y','N')),
	CONSTRAINT AD_DesktopWorkbench_Key PRIMARY KEY (AD_DesktopWorkbench_ID)
) 
;


-- 
-- TABLE: AD_Desktop_Trl 
--

CREATE TABLE AD_Desktop_Trl(
	AD_Desktop_ID	 NUMBER(10, 0)		NOT NULL,
	AD_Language		 VARCHAR2(6)		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,
	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,
	Description		 NVARCHAR2(255),
	Help			 NVARCHAR2(2000),
	IsTranslated	 CHAR(1)			 DEFAULT 'N' NOT NULL,
	CHECK (IsActive in ('Y','N')),
	CHECK (IsTranslated in ('Y','N')),
	CONSTRAINT AD_Desktop_Trl_Key PRIMARY KEY (AD_Desktop_ID, AD_Language)
) 
;


-- 
-- TABLE: AD_Element 
--

CREATE TABLE AD_Element(
	AD_Element_ID	  NUMBER(10, 0)		 NOT NULL,
	AD_Client_ID	  NUMBER(10, 0)		 NOT NULL,
	AD_Org_ID		  NUMBER(10, 0),
	IsActive		  CHAR(1)			  DEFAULT 'Y' NOT NULL,
	Created			  DATE				  DEFAULT SYSDATE NOT NULL,
	CreatedBy		  NUMBER(10, 0),
	Updated			  DATE				  DEFAULT SYSDATE NOT NULL,
	UpdatedBy		  NUMBER(10, 0),
	ColumnName		  VARCHAR2(40)		 NOT NULL,
	EntityType		  CHAR(1)			  DEFAULT 'D' NOT NULL,
	Name			  NVARCHAR2(60),
	PrintName		  NVARCHAR2(60)		 NOT NULL,
	Description		  NVARCHAR2(255),
	Help			  NVARCHAR2(2000),
	PO_Name			  NVARCHAR2(60),
	PO_PrintName	  NVARCHAR2(60),
	PO_Description	  NVARCHAR2(255),
	PO_Help			  NVARCHAR2(2000),
	CHECK (IsActive in ('Y','N')),
	CONSTRAINT AD_Element_Key PRIMARY KEY (AD_Element_ID)
	USING INDEX
TABLESPACE INDX
) 
;


-- 
-- TABLE: AD_Element_Trl 
--

CREATE TABLE AD_Element_Trl(
	AD_Element_ID	  NUMBER(10, 0)		 NOT NULL,
	AD_Language		  VARCHAR2(6)		 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,
	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,
	PrintName		  NVARCHAR2(60)		 NOT NULL,
	Description		  NVARCHAR2(255),
	Help			  NVARCHAR2(2000),
	PO_Name			  NVARCHAR2(60),
	PO_PrintName	  NVARCHAR2(60),
	PO_Description	  NVARCHAR2(255),
	PO_Help			  NVARCHAR2(2000),
	IsTranslated	  CHAR(1)			  DEFAULT 'N' NOT NULL,
	CHECK (IsActive in ('Y','N')),
	CHECK (IsTranslated in ('Y','N')),
	CONSTRAINT AD_Element_Trl_Key PRIMARY KEY (AD_Element_ID, AD_Language)