views.sql

大家共享愉快, 共享愉快, 共享愉快, 共享愉快,共享愉快

/*************************************************************************
 * 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: Views.sql,v 1.103 2005/12/27 06:19:09 jjanke Exp $
 ***
 * Title:	Views
 * Description:	
 *		These views should be generated out of ER/Studio, but due to bugs
 *		this needs to be done manually at this time.
 *      Afterwards run: PrintFormatUtil
 *
 *		Dictionary
 *			AD_Field_v / _vt
 *			AD_Tab_v / _vt
 *			AD_Window_vt
 *			AD_User_Roles_v
 *          AD_Org_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_Line_v / _vt
 *			C_Project_Header_v / _vt
 *			C_Project_Details_v / _vt
 *          C_RfQ_v, C_RfQLine_v, C_RfQLineQty_v /_vt
 *
 *		Other
 *			R_Request_v
 *
 *		Report Views
 *			RV_C_Invoice, RV_C_InvoiceLine, RV_C_InvoiceTax
 *			RV_C_Invoice_Day, RV_C_Invoice_Week, RV_C_Invoice_Month 
 *			RV_C_Invoice_CustomerProdQtr, RV_C_Invoice_CustomerVendQtr 
 *			RV_C_Invoice_ProdWeek, RV_C_Invoice_ProdMonth 
 *			RV_C_Invoice_VendorMonth
 *			RV_M_Transaction, RV_M_Transaction_Sum
 *			RV_OpenItem
 *			RV_Order_Open
 *			RV_Cash_Detail
 *			RV_BPartner
 *			RV_Product_Costing
 *          RV_ProjectCycle
 *          RV_Asset_Customer, RV_Asset_Delivery, RV_Asset_SumMonth
 *          RV_Storage, RV_Transaction
 *          RV_Click_Month, RV_Click_Umprocessed
 *          RV_UnPosted
 *          RV_WarehousePrice
 *          RV_Fact_Acct*
 *          RV_C_RfQ_UnAnswered, RV_C_RfQResponse
 *          RV_M_Requisition
 *          RV_InOutConfirm, RV_InOutLineConfirm
 *          RV_Allocation
 *          RV_InOutDetails
 *
 *		Utility Views
 *			C_Invoice_v, C_InvoiceLine_v
 *			C_Invoice_Candidate_v 
 *			M_InOut_Candidate_v 
 *			GL_JournalLine_Acct_v
 *			C_Payment_v
 *
 ************************************************************************/


-----   Temp Conveniance - original in createSQLJ.sql
 

/** Get Character at Position   */
CREATE OR REPLACE FUNCTION charAt
(
    p_string    VARCHAR2,
    p_pos       NUMBER
)
 	RETURN VARCHAR2
AS
BEGIN
    RETURN SUBSTR(p_string, p_pos, 1);
END;    
/
/** GetDate                     */
CREATE OR REPLACE FUNCTION getdate
 	RETURN DATE
AS
BEGIN
    RETURN SysDate;
END;    
/
/** First Of DD/DY/MM/Q         */
CREATE OR REPLACE FUNCTION firstOf
(
    p_date      DATE,
    p_datePart  VARCHAR2
)
 	RETURN DATE
AS
BEGIN
    RETURN TRUNC(p_date, p_datePart);
END;    
/
/** Add Number of Days      */
CREATE OR REPLACE FUNCTION addDays
(
    p_date      DATE,
    p_days      NUMBER
)
 	RETURN DATE
AS
BEGIN
    RETURN TRUNC(p_date) + p_days;
END;    
/
/** Difference in Days      */
CREATE OR REPLACE FUNCTION daysBetween
(
    p_date1     DATE,
    p_date2     DATE
)
 	RETURN NUMBER
AS
BEGIN
    RETURN (TRUNC(p_date1) - TRUNC(p_date2));
END;    
/


-- 
-- 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, f.ObscureType,
	c.ColumnName, c.ColumnSQL, 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.IsAlwaysUpdateable,
	c.ReadOnlyLogic, c.IsUpdateable, c.IsEncrypted AS IsEncryptedColumn, c.IsSelectionColumn,
	tbl.TableName, c.ValueMin, c.ValueMax, 
	fg.Name AS FieldGroup, vr.Code AS ValidationCode
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, f.ObscureType,
	c.ColumnName, c.ColumnSQL, 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.IsAlwaysUpdateable,
	c.ReadOnlyLogic, c.IsUpdateable, c.IsEncrypted AS IsEncryptedColumn, c.IsSelectionColumn,
	tbl.TableName, c.ValueMin, c.ValueMax, 
	fgt.Name AS FieldGroup, vr.Code AS ValidationCode
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.ReplicationType,
    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.ReadOnlyLogic, t.DisplayLogic,
    t.AD_Column_ID, t.AD_Process_ID, t.IsSortTab, t.IsInsertRecord, t.IsAdvancedTab,
    t.AD_ColumnSortOrder_ID, t.AD_ColumnSortYesNo_ID, t.Included_Tab_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.ReplicationType,
    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, trl.CommitWarning, t.ReadOnlyLogic, t.DisplayLogic,
    t.AD_Column_ID, t.AD_Process_ID, t.IsSortTab, t.IsInsertRecord, t.IsAdvancedTab,
    t.AD_ColumnSortOrder_ID, t.AD_ColumnSortYesNo_ID, t.Included_Tab_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_Org_v 
--
CREATE OR REPLACE VIEW AD_Org_v 
AS
SELECT o.AD_Client_ID, o.AD_Org_ID, 
    o.IsActive, o.Created, o.CreatedBy, o.Updated, o.UpdatedBy,
    o.Value, o.Name, o.Description, o.IsSummary,
    i.C_Location_ID, i.DUNS, i.TaxID,
    i.Supervisor_ID, i.Parent_Org_ID, 
    i.AD_OrgType_ID, i.M_Warehouse_ID,
    bp.C_BPartner_ID
FROM AD_Org o
    INNER JOIN AD_OrgInfo i ON (o.AD_Org_ID=i.AD_Org_ID)
    LEFT OUTER JOIN C_BPartner bp ON (o.AD_Org_ID=bp.AD_OrgBP_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, bt.WinWidth, bt.WinHeight,
    bt.IsSOTrx
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, bp.TaxID AS BPTaxID, bp.NAICS, bp.DUNS,
	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.Greeting AS BPGreeting,
	bp.Name, bp.Name2,
	bpcg.Greeting AS BPContactGreeting,
	bpc.Title, bpc.Phone,
	NULLIF (bpc.Name, bp.Name) AS ContactName,
	bpl.C_Location_ID, bp.ReferenceNo,
	i.Description,
	i.POReference,