views2.sql

Java写的ERP系统

/*************************************************************************
 * 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: Views2.sql,v 1.21 2003/04/30 01:01:08 jjanke Exp $
 ***
 * Title:	Views (2)
 *
 * Description:
 *		These views should be generated out of ER/Studio, but due to bugs
 *		this needs to be done manually at this time.
 *
 *		Dictionary
 *			AD_Field_v / _vt
 *			AD_Tab_v / _vt
 *			AD_Window_vt
 *			AD_User_Roles_v
 *
 *		Report
 *			C_Invoice_Header_v / _vt
 *			C_Invoice_LineTax_v / _vt
 *			C_Order_Header_v / _vt
 *			C_Order_LineTax_v / _vt
 *			C_PaySelection_Check_v / _vt
 *			C_PaySelection_Remittance_v / _vt
 *			M_InOut_Header_v / _vt
 *			M_InOut_LineTax_v / _vt
 *
 *		Other
 *			R_Request_v
 *
 ************************************************************************/

-- 
-- VIEW: AD_Field_v 
--

CREATE OR REPLACE VIEW AD_Field_v 
AS
SELECT t.AD_Window_ID, f.AD_Tab_ID, f.AD_Field_ID, tbl.AD_Table_ID, f.AD_Column_ID, 
	f.Name, f.Description, f.Help, f.IsDisplayed, f.DisplayLogic, f.DisplayLength, 
	f.SeqNo, f.SortNo, f.IsSameLine, f.IsHeading, f.IsFieldOnly, f.IsReadOnly, 
	f.IsEncrypted AS IsEncryptedField, 
	c.ColumnName, c.FieldLength, c.VFormat, c.DefaultValue, c.IsKey, c.IsParent, 
	c.IsMandatory, c.IsIdentifier, c.IsTranslated, c.AD_Reference_Value_ID, 
	c.Callout, c.AD_Reference_ID, c.AD_Val_Rule_ID, c.AD_Process_ID, 
	c.ReadOnlyLogic, c.IsUpdateable, c.IsEncrypted AS IsEncryptedColumn, c.IsSelectionColumn,
	tbl.TableName, c.ValueMin, c.ValueMax, 
	fg.Name AS FieldGroup, vr.Code AS Validation
FROM AD_Field f 
  INNER JOIN AD_Tab t ON (f.AD_Tab_ID = t.AD_Tab_ID)
  LEFT OUTER JOIN AD_FieldGroup fg ON (f.AD_FieldGroup_ID = fg.AD_FieldGroup_ID) 
  LEFT OUTER JOIN AD_Column c ON (f.AD_Column_ID = c.AD_Column_ID)
    INNER JOIN AD_Table tbl ON (c.AD_Table_ID = tbl.AD_Table_ID)
    INNER JOIN AD_Reference r ON (c.AD_Reference_ID = r.AD_Reference_ID)
    LEFT OUTER JOIN AD_Val_Rule vr ON (c.AD_Val_Rule_ID=vr.AD_Val_Rule_ID)
WHERE f.IsActive = 'Y' 
  AND c.IsActive = 'Y'
/

-- 
-- VIEW: AD_Field_vt 
--

CREATE OR REPLACE VIEW AD_Field_vt 
AS
SELECT trl.AD_Language, t.AD_Window_ID, f.AD_Tab_ID, f.AD_Field_ID, tbl.AD_Table_ID, f.AD_Column_ID, 
	trl.Name, trl.Description, trl.Help, f.IsDisplayed, f.DisplayLogic, f.DisplayLength, 
	f.SeqNo, f.SortNo, f.IsSameLine, f.IsHeading, f.IsFieldOnly, f.IsReadOnly, 
	f.IsEncrypted AS IsEncryptedField, 
	c.ColumnName, c.FieldLength, c.VFormat, c.DefaultValue, c.IsKey, c.IsParent, 
	c.IsMandatory, c.IsIdentifier, c.IsTranslated, c.AD_Reference_Value_ID, 
	c.Callout, c.AD_Reference_ID, c.AD_Val_Rule_ID, c.AD_Process_ID, 
	c.ReadOnlyLogic, c.IsUpdateable, c.IsEncrypted AS IsEncryptedColumn, c.IsSelectionColumn,
	tbl.TableName, c.ValueMin, c.ValueMax, 
	fgt.Name AS FieldGroup, vr.Code AS Validation
FROM AD_Field f 
    INNER JOIN AD_Field_Trl trl ON (f.AD_Field_ID = trl.AD_Field_ID)
  INNER JOIN AD_Tab t ON (f.AD_Tab_ID = t.AD_Tab_ID)
  LEFT OUTER JOIN AD_FieldGroup_Trl fgt ON 
    (f.AD_FieldGroup_ID = fgt.AD_FieldGroup_ID AND trl.AD_Language=fgt.AD_Language)
  LEFT OUTER JOIN AD_Column c ON (f.AD_Column_ID = c.AD_Column_ID)
    INNER JOIN AD_Table tbl ON (c.AD_Table_ID = tbl.AD_Table_ID)
    INNER JOIN AD_Reference r ON (c.AD_Reference_ID = r.AD_Reference_ID)
    LEFT OUTER JOIN AD_Val_Rule vr ON (c.AD_Val_Rule_ID=vr.AD_Val_Rule_ID)
WHERE f.IsActive = 'Y' 
  AND c.IsActive = 'Y'
/

-- 
-- VIEW: AD_Tab_v 
--

CREATE OR REPLACE VIEW AD_Tab_v 
AS
SELECT t.AD_Tab_ID, t.AD_Window_ID, t.AD_Table_ID, t.Name, t.Description, 
t.Help, t.SeqNo, t.IsSingleRow, t.HasTree, t.IsInfoTab, 
tbl.TableName, tbl.AccessLevel, tbl.IsSecurityEnabled, tbl.IsDeleteable, 
tbl.IsHighVolume, tbl.IsView, 'N' AS HasAssociation, -- compatibility
t.IsTranslationTab, t.IsReadOnly, t.AD_Image_ID, t.TabLevel, 
t.WhereClause, t.OrderByClause, t.CommitWarning, 
t.AD_Column_ID, t.AD_Process_ID, t.IsSortTab, 
t.AD_ColumnSortOrder_ID, t.AD_ColumnSortYesNo_ID
FROM AD_Tab t 
	INNER JOIN AD_Table tbl ON (t.AD_Table_ID = tbl.AD_Table_ID)
WHERE t.IsActive='Y'
  AND tbl.IsActive='Y'
/

-- 
-- VIEW: AD_Tab_vt 
--

CREATE OR REPLACE VIEW AD_Tab_vt 
AS
SELECT trl.AD_Language, t.AD_Tab_ID, t.AD_Window_ID, t.AD_Table_ID, trl.Name, trl.Description, 
trl.Help, t.SeqNo, t.IsSingleRow, t.HasTree, t.IsInfoTab, 
tbl.TableName, tbl.AccessLevel, tbl.IsSecurityEnabled, tbl.IsDeleteable, 
tbl.IsHighVolume, tbl.IsView, 'N' AS HasAssociation, -- compatibility
t.IsTranslationTab, t.IsReadOnly, t.AD_Image_ID, t.TabLevel, 
t.WhereClause, t.OrderByClause, t.CommitWarning, 
t.AD_Column_ID, t.AD_Process_ID, t.IsSortTab, 
t.AD_ColumnSortOrder_ID, t.AD_ColumnSortYesNo_ID
FROM AD_Tab t 
	INNER JOIN AD_Table tbl ON (t.AD_Table_ID = tbl.AD_Table_ID)
	INNER JOIN AD_Tab_Trl trl ON (t.AD_Tab_ID = trl.AD_Tab_ID)
WHERE t.IsActive='Y'
  AND tbl.IsActive='Y'
/

-- 
-- VIEW: AD_User_Roles_v 
--

CREATE OR REPLACE VIEW AD_User_Roles_v 
AS
SELECT	u.Name, r.Name AS RoleName
FROM	AD_User_Roles ur
	INNER JOIN AD_User u ON (ur.AD_User_ID=u.AD_User_ID)
	INNER JOIN AD_Role r ON (ur.AD_Role_ID=r.AD_Role_ID)
/

-- 
-- VIEW: AD_Window_vt 
--

CREATE OR REPLACE VIEW AD_Window_vt 
AS 
SELECT trl.AD_Language, 
	bt.AD_Window_ID, trl.Name, trl.Description, trl.Help, bt.WindowType, 
	bt.AD_Color_ID, bt.AD_Image_ID, bt.IsActive
FROM AD_Window bt
	INNER JOIN AD_Window_Trl trl ON (bt.AD_Window_ID=trl.AD_Window_ID)
WHERE bt.IsActive='Y'
/

-- 
-- VIEW: C_Invoice_Header_v 
--

CREATE OR REPLACE VIEW C_Invoice_Header_v 
AS
SELECT i.AD_Client_ID, i.AD_Org_ID, i.IsActive, i.Created, i.CreatedBy, i.Updated, i.UpdatedBy, 
	'en_US' AS AD_Language,
	i.C_Invoice_ID, i.IsSOTrx, i.DocumentNo, i.DocStatus,  i.C_DocType_ID,
	i.C_BPartner_ID, bp.Value AS BPValue,
	oi.C_Location_ID AS Org_Location_ID, oi.TaxID,
	dt.PrintName AS DocumentType, dt.DocumentNote AS DocumentTypeNote,
	i.C_Order_ID, i.SalesRep_ID, COALESCE(ubp.Name, u.Name) AS SalesRep_Name,
	i.DateInvoiced,
	bpg.Name AS BPGreeting,
	bp.Name, bp.Name2,
	bpcg.Name AS BPContactGreeting,
	bpc.Title,
	NULLIF (bpc.Name, bp.Name) AS ContactName,
	bpl.C_Location_ID,
	bp.ReferenceNo,
	i.Description,
	i.POReference,
	i.DateOrdered,
	i.C_Currency_ID,
	pt.Name AS PaymentTerm, pt.DocumentNote AS PaymentTermNote,
	i.C_Charge_ID, i.ChargeAmt,
	i.TotalLines,
	i.GrandTotal,
	i.M_PriceList_ID,
	i.IsTaxIncluded,
	i.C_Campaign_ID,
	i.C_Project_ID,
	i.C_Activity_ID,
	i.IsPaid
FROM C_Invoice i
	INNER JOIN C_DocType dt ON (i.C_DocType_ID=dt.C_DocType_ID)
	INNER JOIN C_PaymentTerm pt ON (i.C_PaymentTerm_ID=pt.C_PaymentTerm_ID)
	INNER JOIN C_BPartner bp ON (i.C_BPartner_ID=bp.C_BPartner_ID)
	LEFT OUTER JOIN C_Greeting bpg on (bp.C_Greeting_ID=bpg.C_Greeting_ID)
	INNER JOIN C_BPartner_Location bpl ON (i.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)
	LEFT OUTER JOIN C_BPartner_Contact bpc ON (i.C_BPartner_Contact_ID=bpc.C_BPartner_Contact_ID)
	LEFT OUTER JOIN C_Greeting bpcg on (bpc.C_Greeting_ID=bpcg.C_Greeting_ID)
	INNER JOIN AD_OrgInfo oi ON (i.AD_Org_ID=oi.AD_Org_ID)
	LEFT OUTER JOIN AD_User u ON (i.SalesRep_ID=u.AD_User_ID)
	LEFT OUTER JOIN C_BPartner ubp ON (u.C_BPartner_ID=ubp.C_BPartner_ID)
/

-- 
-- VIEW: C_Invoice_Header_vt
--

CREATE OR REPLACE VIEW C_Invoice_Header_vt 
AS
SELECT i.AD_Client_ID, i.AD_Org_ID, i.IsActive, i.Created, i.CreatedBy, i.Updated, i.UpdatedBy, 
	dt.AD_Language,
	i.C_Invoice_ID, i.IsSOTrx, i.DocumentNo, i.DocStatus,  i.C_DocType_ID,
	i.C_BPartner_ID, bp.Value AS BPValue,
	oi.C_Location_ID AS Org_Location_ID, oi.TaxID,
	dt.PrintName AS DocumentType, dt.DocumentNote AS DocumentTypeNote,
	i.C_Order_ID, i.SalesRep_ID, COALESCE(ubp.Name, u.Name) AS SalesRep_Name,
	i.DateInvoiced,
	bpg.Name AS BPGreeting,
	bp.Name, bp.Name2,
	bpcg.Name AS BPContactGreeting,
	bpc.Title,
	NULLIF (bpc.Name, bp.Name) AS ContactName,
	bpl.C_Location_ID,
	bp.ReferenceNo,
	i.Description,
	i.POReference,
	i.DateOrdered,
	i.C_Currency_ID,
	pt.Name AS PaymentTerm, pt.DocumentNote AS PaymentTermNote,
	i.C_Charge_ID, i.ChargeAmt,
	i.TotalLines,
	i.GrandTotal,
	i.M_PriceList_ID,
	i.IsTaxIncluded,
	i.C_Campaign_ID,
	i.C_Project_ID,
	i.C_Activity_ID,
	i.IsPaid
FROM C_Invoice i
	INNER JOIN C_DocType_Trl dt ON (i.C_DocType_ID=dt.C_DocType_ID)
	INNER JOIN C_PaymentTerm_Trl pt ON (i.C_PaymentTerm_ID=pt.C_PaymentTerm_ID AND dt.AD_Language=pt.AD_Language)
	INNER JOIN C_BPartner bp ON (i.C_BPartner_ID=bp.C_BPartner_ID)
	LEFT OUTER JOIN C_Greeting_Trl bpg on (bp.C_Greeting_ID=bpg.C_Greeting_ID AND dt.AD_Language=bpg.AD_Language)
	INNER JOIN C_BPartner_Location bpl ON (i.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)
	LEFT OUTER JOIN C_BPartner_Contact bpc ON (i.C_BPartner_Contact_ID=bpc.C_BPartner_Contact_ID)
	LEFT OUTER JOIN C_Greeting_Trl bpcg on (bpc.C_Greeting_ID=bpcg.C_Greeting_ID AND dt.AD_Language=bpcg.AD_Language)
	INNER JOIN AD_OrgInfo oi ON (i.AD_Org_ID=oi.AD_Org_ID)
	LEFT OUTER JOIN AD_User u ON (i.SalesRep_ID=u.AD_User_ID)
	LEFT OUTER JOIN C_BPartner ubp ON (u.C_BPartner_ID=ubp.C_BPartner_ID)
/

-- 
-- VIEW: C_Invoice_LineTax_v 
--

CREATE OR REPLACE VIEW C_Invoice_LineTax_v 
AS
SELECT il.AD_Client_ID, il.AD_Org_ID, il.IsActive, il.Created, il.CreatedBy, il.Updated, il.UpdatedBy,
	'en_US' AS AD_Language,
	il.C_Invoice_ID, il.C_InvoiceLine_ID, il.C_Tax_ID,
	il.Line, 
	CASE WHEN il.QtyInvoiced<>0 OR il.M_Product_ID IS NOT NULL THEN il.QtyInvoiced END AS QtyInvoiced, 
	CASE WHEN il.QtyInvoiced<>0 OR il.M_Product_ID IS NOT NULL THEN uom.UOMSymbol END AS UOMSymbol, 
	COALESCE(p.Name, il.Description) AS Name, -- main line
	CASE WHEN p.Name IS NOT NULL THEN il.Description END AS Description, -- second line
	p.DocumentNote, -- third line
	ra.Description AS ResourceDescription, -- forth line
	CASE WHEN i.IsDiscountPrinted='Y' AND (il.PriceList<>0 OR il.M_Product_ID IS NOT NULL) THEN il.PriceList END AS PriceList,
	CASE WHEN i.IsDiscountPrinted='Y' AND il.PriceList>il.PriceActual THEN (il.PriceList-il.PriceActual)/il.PriceList*100 END AS Discount,
	CASE WHEN il.PriceActual<>0 OR il.M_Product_ID IS NOT NULL THEN il.PriceActual END AS PriceActual, 
	CASE WHEN il.LineNetAmt<>0 OR il.M_Product_ID IS NOT NULL THEN il.LineNetAmt END AS LineNetAmt
FROM	C_InvoiceLine il
	INNER JOIN C_UOM uom ON (il.C_UOM_ID=uom.C_UOM_ID)
	INNER JOIN C_Invoice i ON (il.C_Invoice_ID=i.C_Invoice_ID)
	LEFT OUTER JOIN M_Product p ON (il.M_Product_ID=p.M_Product_ID)
	LEFT OUTER JOIN S_ResourceAssignment ra ON (il.S_ResourceAssignment_ID=ra.S_ResourceAssignment_ID)
UNION
SELECT il.AD_Client_ID, il.AD_Org_ID, il.IsActive, il.Created, il.CreatedBy, il.Updated, il.UpdatedBy,
	'en_US' AS AD_Language,
	il.C_Invoice_ID, il.C_InvoiceLine_ID, il.C_Tax_ID,
	il.Line+(b.Line/100) AS Line,
	il.QtyInvoiced*b.BOMQty AS QtyInvoiced,
	uom.UOMSymbol,
	p.Name,	-- main
	b.Description,
	p.DocumentNote,
	null, null, null, null, null
FROM M_Product_BOM b	-- BOM lines
	INNER JOIN C_InvoiceLine il ON (b.M_Product_ID=il.M_Product_ID)
	INNER JOIN M_Product bp ON (bp.M_Product_ID=il.M_Product_ID -- BOM Product
		AND bp.IsBOM='Y' AND bp.IsVerified='Y' AND bp.IsInvoicePrintDetails='Y')