views.sql

Java写的ERP系统

--	Product Transactions Detail

CREATE OR REPLACE VIEW RV_M_Transaction AS
SELECT t.AD_Client_ID,t.AD_Org_ID, t.MovementDate, t.MovementQty, 
	t.M_Product_ID, t.M_Locator_ID,
	p.M_Product_Category_ID, p.Value, 
	po.C_BPartner_ID, po.PricePO, po.PriceLastPO, po.PriceList
FROM M_Transaction t, M_Product p, M_Product_PO po
WHERE t.M_Product_ID=p.M_Product_ID
AND t.M_Product_ID=po.M_Product_ID AND po.IsCurrentVendor='Y'
/

--	Business Partners

CREATE OR REPLACE VIEW RV_BPartner 
AS
SELECT p.AD_Client_ID, p.AD_Org_ID,
	p.C_BPartner_ID, p.Value, p.Name, p.ReferenceNo,
	p.SO_CreditLimit - p.SO_CreditUsed AS SO_CreditAvailable, 
	p.SO_CreditLimit, p.SO_CreditUsed, p.IsCustomer, p.IsVendor,
	p.ActualLifetimeValue AS Revenue,
	c.Name AS Contact, c.Phone,
	a.Postal, a.City, c.EMail
FROM C_BPartner p, C_BPartner_Contact c, C_BPartner_Location l, C_Location a
WHERE p.C_BPartner_ID=c.C_BPartner_ID (+) 
 AND p.C_BPartner_ID=l.C_BPartner_ID (+) 
 AND l.C_Location_ID=a.C_Location_ID (+)
/

--	Open Items

CREATE OR REPLACE VIEW RV_OpenItem 
AS
SELECT i.AD_Org_ID, i.AD_Client_ID, 
	i.DocumentNo, i.C_Invoice_ID, i.C_Order_ID, i.C_BPartner_ID, i.IsSOTrx,
	i.DateInvoiced, p.NetDays, 
	i.DateInvoiced+p.NetDays AS DateDue,
	C_PaymentTerm_DueDays(i.C_PaymentTerm_ID, i.DateInvoiced, SysDate) AS DaysDue,
	i.GrandTotal, 
	C_Invoice_Paid(i.C_Invoice_ID, i.C_Currency_ID, 1) AS PaidAmt,
	C_Invoice_Open(i.C_Invoice_ID) AS OpenAmt
FROM RV_C_Invoice i, C_PaymentTerm p
WHERE i.IsPaid='N' -- AND C_Invoice_Open(i.C_Invoice_ID) <> 0
  AND i.C_PaymentTerm_ID=p.C_PaymentTerm_ID
/

--	Open Orders

CREATE OR REPLACE VIEW RV_Order_Open
AS
SELECT l.AD_Client_ID, l.AD_Org_ID, 
	l.IsActive, l.Created, l.CreatedBy, l.Updated, l.UpdatedBy,
	o.C_Order_ID, o.DocStatus, o.DocAction, o.C_DocType_ID, o.IsApproved, o.IsCreditApproved,
	o.SalesRep_ID, o.C_BPartner_ID, o.C_BPartner_Location_ID, o.C_BPartner_Contact_ID,
	o.POReference, o.C_Currency_ID, o.IsSOTrx,
	l.C_OrderLine_ID, l.DateOrdered, l.DatePromised, l.M_Product_ID, l.M_WareHouse_ID, l.DirectShip,
	l.C_UOM_ID, l.QtyOrdered, l.QtyReserved, l.QtyDelivered, l.QtyInvoiced, l.PriceActual,
	l.QtyOrdered-l.QtyDelivered AS QtyToDeliver,
	l.QtyOrdered-l.QtyInvoiced AS QtyToInvoice,
	(l.QtyOrdered-l.QtyInvoiced)*l.PriceActual AS NetAmtToInvoice
FROM C_Order o, C_OrderLine l
WHERE o.C_Order_ID=l.C_Order_ID
  AND (l.QtyOrdered<>l.QtyDelivered OR l.QtyOrdered<>l.QtyInvoiced)
/

--	Cash Journal Detail

CREATE OR REPLACE VIEW RV_Cash_Detail
AS
SELECT cl.C_Cash_ID, cl.C_CashLine_ID,
	c.AD_Client_ID, c.AD_Org_ID, cl.IsActive, cl.Created, cl.CreatedBy, cl.Updated, cl.UpdatedBy,
	c.C_CashBook_ID, c.Name, c.StatementDate, c.DateAcct, c.Processed, c.Posted,
	cl.Line, cl.Description, cl.CashType, cl.C_Currency_ID, cl.Amount, 
	C_Currency_Convert(cl.Amount,cl.C_Currency_ID,cb.C_Currency_ID,c.StatementDate,null, c.AD_Client_ID, c.AD_Org_ID) AS ConvertedAmt,
	cl.C_BankAccount_ID, cl.C_Invoice_ID, cl.C_Charge_ID
FROM C_Cash c, C_CashLine cl, C_CashBook cb
WHERE c.C_Cash_ID=cl.C_Cash_ID
  AND c.C_CashBook_ID=cb.C_CashBook_ID
/

CREATE OR REPLACE VIEW RV_Product_Costing
AS
SELECT	pc.M_Product_ID, pc.C_AcctSchema_ID, p.Value, p.Name, p.M_Product_Category_ID,
	pc.AD_Client_ID, pc.AD_Org_ID, pc.IsActive, pc.Created,pc.CreatedBy,pc.Updated,pc.UpdatedBy,
	pc.CurrentCostPrice,
	--	Standard Costing
	pc.FutureCostPrice, pc.CostStandard, 
	pc.CostStandardPOQty, pc.CostStandardPOAmt, 
	DECODE (pc.CostStandardPOQty,0,0, pc.CostStandardPOAmt/pc.CostStandardPOQty) AS CostStandardPODiff,
	pc.CostStandardCumQty, pc.CostStandardCumAmt,
	DECODE (pc.CostStandardCumQty,0,0, pc.CostStandardCumAmt/pc.CostStandardCumQty) AS CostStandardInvDiff,
	--	Average Costing
	pc.CostAverage,
	pc.CostAverageCumQty, pc.CostAverageCumAmt,
	pc.TotalInvQty, pc.TotalInvAmt,
	DECODE (pc.TotalInvQty,0,0, pc.TotalInvAmt/pc.TotalInvQty) AS TotalInvCost,
	--	LastPrice
	pc.PriceLastPO, pc.PriceLastInv
FROM	M_Product_Costing pc, M_Product p
WHERE	pc.M_Product_ID=p.M_Product_ID
/

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

CREATE OR REPLACE VIEW R_Request_v 
AS
SELECT	*
FROM	R_Request
WHERE	IsActive='Y' AND Processed='N' 
  AND	SysDate > DateNextAction
/

-------------------------------------------------------------------------------
--	Corrected for Credit Memo (See RV_C_Invoice)

CREATE OR REPLACE VIEW C_Invoice_v 
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.Processing, i.Processed, i.C_DocType_ID, 
i.C_DocTypeTarget_ID, i.C_Order_ID, i.Description, i.IsApproved, i.IsTransferred, 
i.SalesRep_ID, i.DateInvoiced, i.DatePrinted, i.DateAcct, i.C_BPartner_ID, i.C_BPartner_Location_ID, 
i.C_BPartner_Contact_ID, i.POReference, i.DateOrdered, i.C_Currency_ID, i.PaymentRule, 
i.C_PaymentTerm_ID, i.C_Charge_ID, i.M_PriceList_ID, i.C_Campaign_ID, i.C_Project_ID, 
i.C_Activity_ID, i.IsPrinted, i.IsDiscountPrinted, i.IsPaid,
CASE WHEN SUBSTR(d.DocBaseType,3)='C' THEN i.ChargeAmt*-1 ELSE i.ChargeAmt END AS ChargeAmt, 
CASE WHEN SUBSTR(d.DocBaseType,3)='C' THEN i.TotalLines*-1 ELSE i.TotalLines END AS TotalLines, 
CASE WHEN SUBSTR(d.DocBaseType,3)='C' THEN i.GrandTotal*-1 ELSE i.GrandTotal END AS GrandTotal, 
CASE WHEN SUBSTR(d.DocBaseType,3)='C' THEN -1 ELSE 1 END AS Multiplier,
CASE WHEN SUBSTR(d.DocBaseType,2,1)='P' THEN -1 ELSE 1 END AS MultiplierAP,
d.DocBaseType
FROM C_Invoice i
 INNER JOIN C_DocType d ON (i.C_DocType_ID=d.C_DocType_ID)
/
COMMENT ON TABLE C_Invoice_v IS 'Invoice Information corrected for Credit Memos'
/
CREATE OR REPLACE VIEW C_InvoiceLine_v 
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, il.M_Product_ID,  
	i.DocumentNo, i.DateInvoiced, i.DateAcct,
	i.IsSOTrx, i.DocStatus,
	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,
	il.QtyInvoiced AS QtyInvoiced,
	il.Line, il.C_OrderLine_ID, il.C_UOM_ID
FROM C_Invoice_v i, C_InvoiceLine il
WHERE i.C_Invoice_ID=il.C_Invoice_ID
/
COMMENT ON TABLE C_InvoiceLine_v IS 'Invoice Line Summary for Reporting Views - Corrected for Credit Memos'
/

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

CREATE OR REPLACE VIEW C_Invoice_Candidate_v 
AS
SELECT	
	o.AD_Client_ID, o.AD_Org_ID, o.C_BPartner_ID, o.C_Order_ID,
	o.DocumentNo, o.DateOrdered, o.C_DocType_ID,
	SUM((l.QtyOrdered-l.QtyInvoiced)*l.PriceActual) AS TotalLines
FROM	C_Order o
  INNER JOIN C_OrderLine l ON (o.C_Order_ID=l.C_Order_ID)
  INNER JOIN C_BPartner bp ON (o.C_BPartner_ID=bp.C_BPartner_ID)
  LEFT OUTER JOIN C_InvoiceSchedule si ON (bp.C_InvoiceSchedule_ID=si.C_InvoiceSchedule_ID)
WHERE	o.DocStatus IN ('CO','CL','IP')	-- Standard Orders are IP
	--	not Offers and open Walkin-Receipts
	AND o.C_DocType_ID IN (SELECT C_DocType_ID FROM C_DocType
		WHERE DocBaseType='SOO' AND DocSubTypeSO NOT IN ('ON','OB','WR'))
	--	we need to invoice
	AND	l.QtyOrdered <> l.QtyInvoiced
	--
	AND (
		--	Immediate
		o.InvoiceRule='I'
		--	Order compete ** not supported **
		OR	o.InvoiceRule='O'
		--	Delivery
		OR	(o.InvoiceRule='D' AND l.QtyInvoiced<>l.QtyDelivered)
		--	Order Schedule, but none defined on Business Partner level
		OR	(o.InvoiceRule='S' AND bp.C_InvoiceSchedule_ID IS NULL)
		--	Schedule defined at BP
		OR	(o.InvoiceRule='S' AND bp.C_InvoiceSchedule_ID IS NOT NULL AND
				(
				--	Daily or none
					(si.InvoiceFrequency IS NULL OR si.InvoiceFrequency='D')
				--	Weekly
				OR	(si.InvoiceFrequency='W')
				--	Bi-Monthly
				OR	(si.InvoiceFrequency='T'
					AND (TRUNC(o.DateOrdered) <= TRUNC(SysDate,'MM')+si.InvoiceDayCutoff-1
						AND TRUNC(SysDate) >= TRUNC(o.DateOrdered,'MM')+si.InvoiceDay-1)
					OR	(TRUNC(o.DateOrdered) <= TRUNC(SysDate,'MM')+si.InvoiceDayCutoff+14
						AND TRUNC(SysDate) >= TRUNC(o.DateOrdered,'MM')+si.InvoiceDay+14)
					)
				--	Monthly
				OR	(si.InvoiceFrequency='M'
					AND TRUNC(o.DateOrdered) <= TRUNC(SysDate,'MM')+si.InvoiceDayCutoff-1	-- after cutoff
					AND TRUNC(SysDate) >= TRUNC(o.DateOrdered,'MM')+si.InvoiceDay-1)			-- after invoice day
				)
			)
		)
GROUP BY o.AD_Client_ID, o.AD_Org_ID, o.C_BPartner_ID, o.C_Order_ID,
	o.DocumentNo, o.DateOrdered, o.C_DocType_ID
/

CREATE OR REPLACE VIEW M_InOut_Candidate_v 
AS
SELECT	
	o.AD_Client_ID, o.AD_Org_ID, o.C_BPartner_ID, o.C_Order_ID,
	o.DocumentNo, o.DateOrdered, o.C_DocType_ID,
	SUM((l.QtyOrdered-l.QtyDelivered)*l.PriceActual) AS TotalLines
FROM	C_Order o
  INNER JOIN C_OrderLine l ON (o.C_Order_ID=l.C_Order_ID)
WHERE	(o.DocStatus = 'CO'	AND o.IsDelivered='N')
	--	not Offers and open Walkin-Receipts
	AND o.C_DocType_ID IN (SELECT C_DocType_ID FROM C_DocType
		WHERE DocBaseType='SOO' AND DocSubTypeSO NOT IN ('ON','OB','WR'))
	--	we need to ship
	AND	l.QtyOrdered <> l.QtyDelivered
	AND		l.DirectShip='N' AND l.M_Product_ID IS NOT NULL
	--
GROUP BY o.AD_Client_ID, o.AD_Org_ID, o.C_BPartner_ID, o.C_Order_ID,
	o.DocumentNo, o.DateOrdered, o.C_DocType_ID
/

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

CREATE OR REPLACE VIEW C_Payment_v 
AS
SELECT C_Payment_ID, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
DocumentNo, DateTrx, IsReceipt, C_DocType_ID, TrxType,
C_BankAccount_ID, C_BPartner_ID, C_Invoice_ID, C_BP_BankAccount_ID, C_PaymentBatch_ID,
TenderType, CreditCardType, CreditCardNumber, CreditCardVV, CreditCardExpMM, CreditCardExpYY,
MICR, RoutingNo, AccountNo, CheckNo,
A_Name, A_Street, A_City, A_State, A_Zip, A_Ident_DL, A_Ident_SSN, A_EMail,
VoiceAuthCode, Orig_TrxID, PONum,
C_Currency_ID, 
CASE IsReceipt WHEN 'Y' THEN PayAmt ELSE PayAmt*-1 END AS PayAmt,
CASE IsReceipt WHEN 'Y' THEN DiscountAmt ELSE DiscountAmt*-1 END AS DiscountAmt, 
CASE IsReceipt WHEN 'Y' THEN WriteOffAmt ELSE WriteOffAmt*-1 END AS WriteOffAmt,
CASE IsReceipt WHEN 'Y' THEN TaxAmt ELSE TaxAmt*-1 END AS TaxAmt, 
CASE IsReceipt WHEN 'Y' THEN OverUnderAmt ELSE OverUnderAmt*-1 END AS OverUnderAmt,
CASE IsReceipt WHEN 'Y' THEN 1 ELSE -1 END AS MultiplierAP,
IsOverUnderPayment, IsApproved,
R_PNRef, R_Result, R_RespMsg, R_AuthCode, R_AVSAddr, R_AVSZip, R_Info,
Processing, OProcessing, DocStatus, DocAction,
IsReconciled, IsAllocated, IsOnline, Processed, Posted
FROM C_Payment
/
COMMENT ON TABLE C_Payment_v IS 'Payment Information corrected for AP/AR'
/

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

CREATE OR REPLACE VIEW GL_JournalLine_Acct_v
AS
SELECT 
	gl.GL_JournalLine_ID, gl.AD_Client_ID, gl.AD_Org_ID, gl.IsActive, 
	gl.Created, gl.CreatedBy, gl.Updated, gl.UpdatedBy, gl.GL_Journal_ID, 
	gl.Line, gl.IsGenerated, gl.Description, 
	gl.AmtSourceDr, gl.AmtSourceCr, gl.C_Currency_ID, 
	gl.CurrencyRateType, gl.CurrencyRate, gl.DateAcct, 
	gl.AmtAcctDr, gl.AmtAcctCr, gl.C_UOM_ID, gl.Qty, gl.C_ValidCombination_ID, 
	vc.C_AcctSchema_ID, vc.Account_ID, vc.M_Product_ID, vc.C_BPartner_ID,
	vc.AD_OrgTrx_ID, vc.C_LocFrom_ID, vc.C_LocTo_ID, vc.C_SalesRegion_ID, 
	vc.C_Project_ID, vc.C_Campaign_ID, vc.User1_ID, vc.User2_ID, 
	vc.IsFullyQualified, vc.C_Activity_ID
FROM GL_JournalLine gl, C_ValidCombination vc
WHERE gl.C_ValidCombination_ID = vc.C_ValidCombination_ID
/