views.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+CPM
 * Copyright (C) 1999-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: Views.sql,v 1.36 2003/03/21 02:21:17 jjanke Exp $
 ***
 * Title:	Views
 * Description:	
 *		Should be generated from ER/Studio
 *
 *			Report Views
 *				RV_C_Invoice, RV_C_InvoiceLine
 *				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
 *
 *			Utility Views
 *				R_Request_v
 *				C_Invoice_v, C_InvoiceLine_v
 *				C_Invoice_Candidate_v 
 *				M_InOut_Candidate_v 
 *				GL_JournalLine_Acct_v
 *				C_Payment_v
 *
 ************************************************************************/

-------------------------------------------------------------------------------

--	Invoice corrected for Credit Memo

CREATE OR REPLACE VIEW RV_C_Invoice
AS
SELECT i.C_Invoice_ID, 
	i.AD_Client_ID,i.AD_Org_ID,i.IsActive,i.Created,i.CreatedBy,i.Updated,i.UpdatedBy,
	i.IsSOTrx, i.DocumentNo, i.DocStatus, i.DocAction, 
	i.IsPrinted, i.IsDiscountPrinted, i.Processing, i.Processed, i.IsTransferred, i.IsPaid,
	i.C_DocType_ID, i.C_DocTypeTarget_ID, i.C_Order_ID, i.Description, i.IsApproved, 
	i.SalesRep_ID, i.DateInvoiced, i.DatePrinted, i.DateAcct,
	i.C_BPartner_ID, i.C_BPartner_Location_ID, i.C_BPartner_Contact_ID, b.C_BP_Group_ID,
	i.PoReference, i.DateOrdered, i.C_Currency_ID, i.PaymentRule, i.C_PaymentTerm_ID,
	i.M_PriceList_ID, i.C_Campaign_ID, i.C_Project_ID, i.C_Activity_ID, 
	--	Amounts
	i.C_Charge_ID,
	DECODE(SUBSTR(d.DocBaseType,3), 'C', i.ChargeAmt*-1, i.ChargeAmt) AS ChargeAmt,
	DECODE(SUBSTR(d.DocBaseType,3), 'C', i.TotalLines*-1, i.TotalLines) AS TotalLines,
	DECODE(SUBSTR(d.DocBaseType,3), 'C', i.GrandTotal*-1, i.GrandTotal) AS GrandTotal,
	DECODE(SUBSTR(d.DocBaseType,3), 'C', -1, 1) AS Multiplier
FROM  C_Invoice i, C_DocType d, C_BPartner b
WHERE i.C_DocType_ID=d.C_DocType_ID
  AND i.C_BPartner_ID=b.C_BPartner_ID
/

--	Invoice Lines corrected for Credit Memo

CREATE OR REPLACE VIEW RV_C_InvoiceLine 
AS SELECT 
	il.AD_Client_ID, il.AD_Org_ID,
	il.C_InvoiceLine_ID, i.C_Invoice_ID, i.SalesRep_ID,
	i.C_BPartner_ID, i.C_BP_Group_ID,
	il.M_Product_ID, p.M_Product_Category_ID,
	i.DateInvoiced, i.DateAcct,
	--	Qty
	il.QtyInvoiced*i.Multiplier AS QtyInvoiced,
	--	Item Amounts
	il.PriceList, il.PriceActual, il.PriceLimit, 
	DECODE(PriceList, 0, 0,
	  ROUND((PriceList-PriceActual)/PriceList*100,2)) AS Discount,
	DECODE(PriceLimit, 0, 0,
	  ROUND((PriceActual-PriceLimit)/PriceLimit*100,2)) AS Margin,
	--	Line Amounts
	ROUND(i.Multiplier*LineNetAmt, 2) AS LineNetAmt,
	ROUND(i.Multiplier*PriceList*QtyInvoiced, 2) AS LineListAmt,
	DECODE(NVL(il.PriceLimit, 0), 0, ROUND(i.Multiplier*LineNetAmt,2), 
		ROUND(i.Multiplier*PriceLimit*QtyInvoiced,2)) AS LineLimitAmt,
	ROUND(i.Multiplier*PriceList*QtyInvoiced-LineNetAmt,2) AS LineDiscountAmt,
	DECODE(NVL(il.PriceLimit,0), 0, 0, 
		ROUND(i.Multiplier*LineNetAmt-PriceLimit*QtyInvoiced,2)) AS LineOverLimitAmt
FROM  RV_C_Invoice i, C_InvoiceLine il, M_Product p
WHERE i.C_Invoice_ID=il.C_Invoice_ID
  AND il.M_Product_ID=p.M_Product_ID
/

--	Invoice By Day

CREATE OR REPLACE VIEW RV_C_Invoice_Day 
AS
SELECT AD_Client_ID, AD_Org_ID, SalesRep_ID,
	TRUNC(DateInvoiced, 'DD') AS DateInvoiced,	--	DD Day, DY Week, MM Month
	SUM(LineNetAmt) AS LineNetAmt,
	SUM(LineListAmt) AS LineListAmt,
	SUM(LineLimitAmt) AS LineLimitAmt,
	SUM(LineDiscountAmt) AS LineDiscountAmt,
	DECODE(SUM(LineListAmt), 0, 0,
	  ROUND((SUM(LineListAmt)-SUM(LineNetAmt))/SUM(LineListAmt)*100,2)) AS LineDiscount,
	SUM(LineOverLimitAmt) AS LineOverLimitAmt,
	DECODE(SUM(LineNetAmt), 0, 0,
	  100-ROUND((SUM(LineNetAmt)-SUM(LineOverLimitAmt))/SUM(LineNetAmt)*100,2)) AS LineOverLimit
FROM RV_C_InvoiceLine
GROUP BY AD_Client_ID, AD_Org_ID, SalesRep_ID,
	TRUNC(DateInvoiced, 'DD')
/

--	Invoice By Week

CREATE OR REPLACE VIEW RV_C_Invoice_Week 
AS
SELECT AD_Client_ID, AD_Org_ID, SalesRep_ID,
	TRUNC(DateInvoiced, 'DY') AS DateInvoiced,	--	DD Day, DY Week, MM Month
	SUM(LineNetAmt) AS LineNetAmt,
	SUM(LineListAmt) AS LineListAmt,
	SUM(LineLimitAmt) AS LineLimitAmt,
	SUM(LineDiscountAmt) AS LineDiscountAmt,
	DECODE(SUM(LineListAmt), 0, 0,
	  ROUND((SUM(LineListAmt)-SUM(LineNetAmt))/SUM(LineListAmt)*100,2)) AS LineDiscount,
	SUM(LineOverLimitAmt) AS LineOverLimitAmt,
	DECODE(SUM(LineNetAmt), 0, 0,
	  100-ROUND((SUM(LineNetAmt)-SUM(LineOverLimitAmt))/SUM(LineNetAmt)*100,2)) AS LineOverLimit
FROM RV_C_InvoiceLine
GROUP BY AD_Client_ID, AD_Org_ID, SalesRep_ID,
	TRUNC(DateInvoiced, 'DY')
/

--	Invoice By Month

CREATE OR REPLACE VIEW RV_C_Invoice_Month
AS
SELECT AD_Client_ID, AD_Org_ID, SalesRep_ID,
	TRUNC(DateInvoiced, 'MM') AS DateInvoiced,	--	DD Day, DY Week, MM Month
	SUM(LineNetAmt) AS LineNetAmt,
	SUM(LineListAmt) AS LineListAmt,
	SUM(LineLimitAmt) AS LineLimitAmt,
	SUM(LineDiscountAmt) AS LineDiscountAmt,
	DECODE(SUM(LineListAmt), 0, 0,
	  ROUND((SUM(LineListAmt)-SUM(LineNetAmt))/SUM(LineListAmt)*100,2)) AS LineDiscount,
	SUM(LineOverLimitAmt) AS LineOverLimitAmt,
	DECODE(SUM(LineNetAmt), 0, 0,
	  100-ROUND((SUM(LineNetAmt)-SUM(LineOverLimitAmt))/SUM(LineNetAmt)*100,2)) AS LineOverLimit
FROM RV_C_InvoiceLine
GROUP BY AD_Client_ID, AD_Org_ID, SalesRep_ID,
	TRUNC(DateInvoiced, 'MM')
/

--	Invoice By Customer and Quarter

CREATE OR REPLACE VIEW RV_C_Invoice_CustomerProdQtr 
AS
SELECT il.AD_Client_ID, il.AD_Org_ID,
	il.C_BPartner_ID, il.M_Product_Category_ID,
	TRUNC(il.DateInvoiced, 'Q') AS DateInvoiced,	--	DD Day, DY Week, MM Month
	SUM(LineNetAmt) AS LineNetAmt,
	SUM(LineListAmt) AS LineListAmt,
	SUM(LineLimitAmt) AS LineLimitAmt,
	SUM(LineDiscountAmt) AS LineDiscountAmt,
	DECODE(SUM(LineListAmt), 0, 0,
	  ROUND((SUM(LineListAmt)-SUM(LineNetAmt))/SUM(LineListAmt)*100,2)) AS LineDiscount,
	SUM(LineOverLimitAmt) AS LineOverLimitAmt,
	DECODE(SUM(LineNetAmt), 0, 0,
	  100-ROUND((SUM(LineNetAmt)-SUM(LineOverLimitAmt))/SUM(LineNetAmt)*100,2)) AS LineOverLimit,
	SUM(QtyInvoiced) AS QtyInvoiced
FROM RV_C_InvoiceLine il
GROUP BY il.AD_Client_ID, il.AD_Org_ID, il.C_BPartner_ID, il.M_Product_Category_ID,
	TRUNC(il.DateInvoiced, 'Q')
/

--	Invoice By Vendor and Quarter

CREATE OR REPLACE VIEW RV_C_Invoice_CustomerVendQtr 
AS
SELECT il.AD_Client_ID, il.AD_Org_ID,
	il.C_BPartner_ID, po.C_BPartner_ID AS Vendor_ID,
	TRUNC(il.DateInvoiced, 'Q') AS DateInvoiced,	--	DD Day, DY Week, MM Month
	SUM(LineNetAmt) AS LineNetAmt,
	SUM(LineListAmt) AS LineListAmt,
	SUM(LineLimitAmt) AS LineLimitAmt,
	SUM(LineDiscountAmt) AS LineDiscountAmt,
	DECODE(SUM(LineListAmt), 0, 0,
	  ROUND((SUM(LineListAmt)-SUM(LineNetAmt))/SUM(LineListAmt)*100,2)) AS LineDiscount,
	SUM(LineOverLimitAmt) AS LineOverLimitAmt,
	DECODE(SUM(LineNetAmt), 0, 0,
	  100-ROUND((SUM(LineNetAmt)-SUM(LineOverLimitAmt))/SUM(LineNetAmt)*100,2)) AS LineOverLimit,
	SUM(QtyInvoiced) AS QtyInvoiced
FROM RV_C_InvoiceLine il, M_Product_PO po
WHERE il.M_Product_ID=po.M_Product_ID
GROUP BY il.AD_Client_ID, il.AD_Org_ID, il.C_BPartner_ID, po.C_BPartner_ID,
	TRUNC(il.DateInvoiced, 'Q')
/

--	Invoice By Product and Week

CREATE OR REPLACE VIEW RV_C_Invoice_ProdWeek 
AS
SELECT il.AD_Client_ID, il.AD_Org_ID, il.M_Product_Category_ID,
	TRUNC(il.DateInvoiced, 'DY') AS DateInvoiced,
	SUM(il.LineNetAmt) AS LineNetAmt,
	SUM(il.LineListAmt) AS LineListAmt,
	SUM(il.LineLimitAmt) AS LineLimitAmt,
	SUM(il.LineDiscountAmt) AS LineDiscountAmt,
	DECODE(SUM(LineListAmt), 0, 0,
	  ROUND((SUM(LineListAmt)-SUM(LineNetAmt))/SUM(LineListAmt)*100,2)) AS LineDiscount,
	SUM(LineOverLimitAmt) AS LineOverLimitAmt,
	DECODE(SUM(LineNetAmt), 0, 0,
	  100-ROUND((SUM(LineNetAmt)-SUM(LineOverLimitAmt))/SUM(LineNetAmt)*100,2)) AS LineOverLimit,
	SUM(QtyInvoiced) AS QtyInvoiced
FROM RV_C_InvoiceLine il
GROUP BY il.AD_Client_ID, il.AD_Org_ID, il.M_Product_Category_ID,
	TRUNC(il.DateInvoiced, 'DY')
/

--	Invoice By Product and Month

CREATE OR REPLACE VIEW RV_C_Invoice_ProdMonth 
AS
SELECT il.AD_Client_ID, il.AD_Org_ID, il.M_Product_Category_ID,
	TRUNC(il.DateInvoiced, 'MM') AS DateInvoiced,
	SUM(il.LineNetAmt) AS LineNetAmt,
	SUM(il.LineListAmt) AS LineListAmt,
	SUM(il.LineLimitAmt) AS LineLimitAmt,
	SUM(il.LineDiscountAmt) AS LineDiscountAmt,
	DECODE(SUM(LineListAmt), 0, 0,
	  ROUND((SUM(LineListAmt)-SUM(LineNetAmt))/SUM(LineListAmt)*100,2)) AS LineDiscount,
	SUM(LineOverLimitAmt) AS LineOverLimitAmt,
	DECODE(SUM(LineNetAmt), 0, 0,
	  100-ROUND((SUM(LineNetAmt)-SUM(LineOverLimitAmt))/SUM(LineNetAmt)*100,2)) AS LineOverLimit,
	SUM(QtyInvoiced) AS QtyInvoiced
FROM RV_C_InvoiceLine il
GROUP BY il.AD_Client_ID, il.AD_Org_ID, il.M_Product_Category_ID,
	TRUNC(il.DateInvoiced, 'MM')
/

--	Invoice By Vendor and Month

CREATE OR REPLACE VIEW RV_C_Invoice_VendorMonth 
AS
SELECT il.AD_Client_ID, il.AD_Org_ID,
	po.C_BPartner_ID,
	TRUNC(il.DateInvoiced, 'MM') AS DateInvoiced,	--	DD Day, DY Week, MM Month
	SUM(LineNetAmt) AS LineNetAmt,
	SUM(LineListAmt) AS LineListAmt,
	SUM(LineLimitAmt) AS LineLimitAmt,
	SUM(LineDiscountAmt) AS LineDiscountAmt,
	DECODE(SUM(LineListAmt), 0, 0,
	  ROUND((SUM(LineListAmt)-SUM(LineNetAmt))/SUM(LineListAmt)*100,2)) AS LineDiscount,
	SUM(LineOverLimitAmt) AS LineOverLimitAmt,
	DECODE(SUM(LineNetAmt), 0, 0,
	  100-ROUND((SUM(LineNetAmt)-SUM(LineOverLimitAmt))/SUM(LineNetAmt)*100,2)) AS LineOverLimit,
	SUM(QtyInvoiced) AS QtyInvoiced
FROM RV_C_InvoiceLine il, M_Product_PO po
WHERE il.M_Product_ID=po.M_Product_ID
GROUP BY il.AD_Client_ID, il.AD_Org_ID, po.C_BPartner_ID,
	TRUNC(il.DateInvoiced, 'MM')
/

--	Product Transactions

CREATE OR REPLACE VIEW RV_M_Transaction_Sum
AS
SELECT t.AD_Client_ID, t.AD_Org_ID, 
	t.MovementType, l.M_Warehouse_ID, t.M_Locator_ID, t.M_Product_ID, t.MovementDate,
	SUM(t.MovementQty) AS MovementQty
FROM M_Transaction t, M_Locator l
WHERE t.M_Locator_ID=l.M_Locator_ID
GROUP BY t.AD_Client_ID, t.AD_Org_ID, 
	t.MovementType, l.M_Warehouse_ID, t.M_Locator_ID, t.M_Product_ID, t.MovementDate
/