views2.sql

Java写的ERP系统

SELECT ol.AD_Client_ID, ol.AD_Org_ID, ol.IsActive, ol.Created, ol.CreatedBy, ol.Updated, ol.UpdatedBy,
	uom.AD_Language,
	ol.C_Order_ID, ol.C_OrderLine_ID, ol.C_Tax_ID,
	ol.Line+(b.Line/100) AS Line,
	ol.QtyOrdered*b.BOMQty AS QtyInvoiced,
	uom.UOMSymbol,
	COALESCE(pt.Name, p.Name) AS Name,	-- main
	b.Description,
	COALESCE(pt.DocumentNote, p.DocumentNote) AS DocumentNote,
	null, null, null, null, null
FROM M_Product_BOM b	-- BOM lines
	INNER JOIN C_OrderLine ol ON (b.M_Product_ID=ol.M_Product_ID)
	INNER JOIN M_Product bp ON (bp.M_Product_ID=ol.M_Product_ID -- BOM Product
		AND bp.IsBOM='Y' AND bp.IsVerified='Y' AND bp.IsInvoicePrintDetails='Y')
	INNER JOIN M_Product p ON (b.M_ProductBOM_ID=p.M_Product_ID) -- BOM line product
	INNER JOIN C_UOM_Trl uom ON (p.C_UOM_ID=uom.C_UOM_ID)
	INNER JOIN M_Product_Trl pt ON (b.M_ProductBOM_ID=pt.M_Product_ID AND uom.AD_Language=pt.AD_Language)
UNION
SELECT o.AD_Client_ID, o.AD_Org_ID, o.IsActive, o.Created, o.CreatedBy, o.Updated, o.UpdatedBy,
	l.AD_Language, o.C_Order_ID, null, null,
	null,
	null, null,
	null,
	null, null, null,
	null, null,
	null, null
FROM C_Order o, AD_Language l
WHERE l.IsBaseLanguage='N' AND l.IsSystemLanguage='Y'
UNION
SELECT ot.AD_Client_ID, ot.AD_Org_ID, ot.IsActive, ot.Created, ot.CreatedBy, ot.Updated, ot.UpdatedBy,
	l.AD_Language, ot.C_Order_ID, null, ot.C_Tax_ID,
	null,
	null, null,
	t.Name,
	null, null, null,
	null,null,
	ot.TaxBaseAmt, ot.TaxAmt
FROM C_OrderTax ot
	INNER JOIN C_Tax t ON (ot.C_Tax_ID=t.C_Tax_ID), AD_Language l
WHERE l.IsBaseLanguage='N' AND l.IsSystemLanguage='Y'
/

-- 
-- VIEW: C_PaySelection_Check_v 
--

CREATE OR REPLACE VIEW C_PaySelection_Check_v 
AS
SELECT psc.AD_Client_ID, psc.AD_Org_ID, 
	'en_US' AS AD_Language,
	psc.C_PaySelection_ID, psc.C_PaySelectionCheck_ID,
	oi.C_Location_ID AS Org_Location_ID, oi.TaxID, 0 AS C_DocType_ID,
	bp.C_BPartner_ID, bp.Value AS BPValue,
	bpg.Name AS BPGreeting,
	bp.Name, bp.Name2,
	C_BPartner_RemitLocation(bp.C_BPartner_ID) AS C_Location_ID,
	bp.ReferenceNo, bp.POReference,
	ps.PayDate,
	psc.PayAmt, psc.PayAmt AS AmtInWords,
	psc.Qty, psc.PaymentRule, psc.DocumentNo
FROM C_PaySelectionCheck psc
	INNER JOIN C_PaySelection ps ON (psc.C_PaySelection_ID=ps.C_PaySelection_ID)
	INNER JOIN C_BPartner bp ON (psc.C_BPartner_ID=bp.C_BPartner_ID)
	LEFT OUTER JOIN C_Greeting bpg on (bp.C_Greeting_ID=bpg.C_Greeting_ID)
	INNER JOIN AD_OrgInfo oi ON (psc.AD_Org_ID=oi.AD_Org_ID)
/

-- 
-- VIEW: C_PaySelection_Check_v 
--

CREATE OR REPLACE VIEW C_PaySelection_Check_vt 
AS
SELECT psc.AD_Client_ID, psc.AD_Org_ID, 
	l.AD_Language,
	psc.C_PaySelection_ID, psc.C_PaySelectionCheck_ID,
	oi.C_Location_ID AS Org_Location_ID, oi.TaxID, 0 AS C_DocType_ID,
	bp.C_BPartner_ID, bp.Value AS BPValue,
	bpg.Name AS BPGreeting,
	bp.Name, bp.Name2,
	C_BPartner_RemitLocation(bp.C_BPartner_ID) AS C_Location_ID,
	bp.ReferenceNo, bp.POReference,
	ps.PayDate,
	psc.PayAmt, psc.PayAmt AS AmtInWords,
	psc.Qty, psc.PaymentRule, psc.DocumentNo
FROM	C_PaySelectionCheck psc
	INNER JOIN C_PaySelection ps ON (psc.C_PaySelection_ID=ps.C_PaySelection_ID)
	INNER JOIN C_BPartner bp ON (psc.C_BPartner_ID=bp.C_BPartner_ID)
	INNER JOIN AD_OrgInfo oi ON (psc.AD_Org_ID=oi.AD_Org_ID)
	CROSS JOIN AD_Language l
	LEFT OUTER JOIN C_Greeting_Trl bpg on (bp.C_Greeting_ID=bpg.C_Greeting_ID AND bpg.AD_Language=l.AD_Language)
WHERE l.IsBaseLanguage='N' AND l.IsSystemLanguage='Y'
/

-- 
-- VIEW: C_PaySelection_Remittance_v 
--

CREATE OR REPLACE VIEW C_PaySelection_Remittance_v 
AS
SELECT psl.AD_Client_ID, psl.AD_Org_ID, 
	'en_US' AS AD_Language,
	psl.C_PaySelection_ID, psl.C_PaySelectionLine_ID, 
	psl.C_PaySelectionCheck_ID,
	psl.PaymentRule, psl.Line, psl.PayAmt, psl.DifferenceAmt, 
	i.C_BPartner_ID, i.DocumentNo, i.DateInvoiced, i.GrandTotal
FROM C_PaySelectionLine psl
  INNER JOIN C_Invoice i ON (psl.C_Invoice_ID=i.C_Invoice_ID)
/

-- 
-- VIEW: C_PaySelection_Remittance_vt
--

CREATE OR REPLACE VIEW C_PaySelection_Remittance_vt 
AS
SELECT psl.AD_Client_ID, psl.AD_Org_ID, 
	l.AD_Language,
	psl.C_PaySelection_ID, psl.C_PaySelectionLine_ID, 
	psl.C_PaySelectionCheck_ID,
	psl.PaymentRule, psl.Line, psl.PayAmt, psl.DifferenceAmt, 
	i.C_BPartner_ID, i.DocumentNo, i.DateInvoiced, i.GrandTotal
FROM C_PaySelectionLine psl
  INNER JOIN C_Invoice i ON (psl.C_Invoice_ID=i.C_Invoice_ID), AD_Language l
WHERE l.IsBaseLanguage='N' AND l.IsSystemLanguage='Y'
/

-- 
-- VIEW: M_InOut_Header_v 
--

CREATE OR REPLACE VIEW M_InOut_Header_v 
AS
SELECT io.AD_Client_ID, io.AD_Org_ID, io.IsActive, io.Created, io.CreatedBy, io.Updated, io.UpdatedBy,
	'en_US' AS AD_Language,
	io.M_InOut_ID, io.IsSOTrx, io.DocumentNo, io.DocStatus,	 io.C_DocType_ID,
	io.C_BPartner_ID, bp.Value AS BPValue,
	oi.C_Location_ID AS Org_Location_ID, oi.TaxID,
	io.M_Warehouse_ID,
	wh.C_Location_ID AS Warehouse_Location_ID,
	dt.PrintName AS DocumentType, dt.DocumentNote AS DocumentTypeNote,
	io.C_Order_ID, 
	io.MovementDate, io.MovementType,
	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,
	io.Description,
	io.POReference,
	io.DateOrdered,
	io.M_Shipper_ID, io.DeliveryRule, io.DeliveryViaRule, io.PriorityRule
FROM M_InOut io
	INNER JOIN C_DocType dt ON (io.C_DocType_ID=dt.C_DocType_ID)
	INNER JOIN C_BPartner bp ON (io.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 (io.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)
	LEFT OUTER JOIN C_BPartner_Contact bpc ON (io.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 (io.AD_Org_ID=oi.AD_Org_ID)
	INNER JOIN M_Warehouse wh ON (io.M_Warehouse_ID=wh.M_Warehouse_ID)
/

-- 
-- VIEW: M_InOut_Header_v 
--

CREATE OR REPLACE VIEW M_InOut_Header_vt 
AS
SELECT io.AD_Client_ID, io.AD_Org_ID, io.IsActive, io.Created, io.CreatedBy, io.Updated, io.UpdatedBy,
	dt.AD_Language,
	io.M_InOut_ID, io.IsSOTrx, io.DocumentNo, io.DocStatus,	 io.C_DocType_ID,
	io.C_BPartner_ID, bp.Value AS BPValue,
	oi.C_Location_ID AS Org_Location_ID, oi.TaxID,
	io.M_Warehouse_ID,
	wh.C_Location_ID AS Warehouse_Location_ID,
	dt.PrintName AS DocumentType, dt.DocumentNote AS DocumentTypeNote,
	io.C_Order_ID, 
	io.MovementDate, io.MovementType,
	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,
	io.Description,
	io.POReference,
	io.DateOrdered,
	io.M_Shipper_ID, io.DeliveryRule, io.DeliveryViaRule, io.PriorityRule
FROM M_InOut io
	INNER JOIN C_DocType_Trl dt ON (io.C_DocType_ID=dt.C_DocType_ID)
	INNER JOIN C_BPartner bp ON (io.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 (io.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)
	LEFT OUTER JOIN C_BPartner_Contact bpc ON (io.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 (io.AD_Org_ID=oi.AD_Org_ID)
	INNER JOIN M_Warehouse wh ON (io.M_Warehouse_ID=wh.M_Warehouse_ID)
/

-- 
-- VIEW: M_InOut_Line_v 
--

CREATE OR REPLACE VIEW M_InOut_Line_v 
AS
SELECT iol.AD_Client_ID, iol.AD_Org_ID, iol.IsActive, iol.Created, iol.CreatedBy, iol.Updated, iol.UpdatedBy,
	'en_US' AS AD_Language,
	iol.M_InOut_ID, iol.M_InOutLine_ID, 
	iol.Line, 
	iol.MovementQty, uom.UOMSymbol, 
	COALESCE(p.Name, iol.Description) AS Name, -- main line
	CASE WHEN p.Name IS NOT NULL THEN iol.Description END AS Description, -- second line
	p.DocumentNote, -- third line
	iol.M_Locator_ID, iol.Lot, iol.SerNo
FROM	M_InOutLine iol
	INNER JOIN C_UOM uom ON (iol.C_UOM_ID=uom.C_UOM_ID)
	LEFT OUTER JOIN M_Product p ON (iol.M_PRODUCT_ID=p.M_PRODUCT_ID)
UNION
SELECT iol.AD_Client_ID, iol.AD_Org_ID, iol.IsActive, iol.Created, iol.CreatedBy, iol.Updated, iol.UpdatedBy,
	'en_US' AS AD_Language,
	iol.M_InOut_ID, iol.M_InOutLine_ID, 
	iol.Line+(b.Line/100) AS Line,
	iol.MovementQty*b.BOMQty AS QtyInvoiced,
	uom.UOMSymbol,
	p.Name, -- main line
	b.Description, -- second line
	p.DocumentNote, -- third line
	iol.M_Locator_ID, iol.Lot, iol.SerNo
FROM M_Product_BOM b	-- BOM lines
	INNER JOIN M_InOutLine iol ON (b.M_Product_ID=iol.M_Product_ID)
	INNER JOIN M_Product bp ON (bp.M_Product_ID=iol.M_Product_ID -- BOM Product
		AND bp.IsBOM='Y' AND bp.IsVerified='Y' AND bp.IsPickListPrintDetails='Y')
	INNER JOIN M_Product p ON (b.M_ProductBOM_ID=p.M_Product_ID) -- BOM line product
	INNER JOIN C_UOM uom ON (p.C_UOM_ID=uom.C_UOM_ID)
/

-- 
-- VIEW: M_InOut_Line_vt
--

CREATE OR REPLACE VIEW M_InOut_Line_vt 
AS
SELECT iol.AD_Client_ID, iol.AD_Org_ID, iol.IsActive, iol.Created, iol.CreatedBy, iol.Updated, iol.UpdatedBy,
	uom.AD_Language,
	iol.M_InOut_ID, iol.M_InOutLine_ID, 
	iol.Line, 
	iol.MovementQty, uom.UOMSymbol, 
	COALESCE(COALESCE(pt.Name,p.Name), iol.Description) AS Name, -- main line
	CASE WHEN COALESCE(pt.Name,p.Name) IS NOT NULL THEN iol.Description END AS Description, -- second line
	COALESCE(pt.DocumentNote, p.DocumentNote) AS DocumentNote, -- third line
	iol.M_Locator_ID, iol.Lot, iol.SerNo
FROM	M_InOutLine iol
	INNER JOIN C_UOM_Trl uom ON (iol.C_UOM_ID=uom.C_UOM_ID)
	LEFT OUTER JOIN M_Product p ON (iol.M_PRODUCT_ID=p.M_PRODUCT_ID)
	LEFT OUTER JOIN M_Product_Trl pt ON (iol.M_PRODUCT_ID=pt.M_PRODUCT_ID AND uom.AD_Language=pt.AD_Language)
UNION
SELECT iol.AD_Client_ID, iol.AD_Org_ID, iol.IsActive, iol.Created, iol.CreatedBy, iol.Updated, iol.UpdatedBy,
	uom.AD_Language,
	iol.M_InOut_ID, iol.M_InOutLine_ID, 
	iol.Line+(b.Line/100) AS Line,
	iol.MovementQty*b.BOMQty AS QtyInvoiced,
	uom.UOMSymbol,
	COALESCE (pt.Name, p.Name) AS Name, -- main line
	b.Description, -- second line
	COALESCE (pt.DocumentNote, p.DocumentNote) AS DocumentNote, -- third line
	iol.M_Locator_ID, iol.Lot, iol.SerNo
FROM M_Product_BOM b	-- BOM lines
	INNER JOIN M_InOutLine iol ON (b.M_Product_ID=iol.M_Product_ID)
	INNER JOIN M_Product bp ON (bp.M_Product_ID=iol.M_Product_ID -- BOM Product
		AND bp.IsBOM='Y' AND bp.IsVerified='Y' AND bp.IsPickListPrintDetails='Y')
	INNER JOIN M_Product p ON (b.M_ProductBOM_ID=p.M_Product_ID) -- BOM line product
	INNER JOIN C_UOM_Trl uom ON (p.C_UOM_ID=uom.C_UOM_ID)
	INNER JOIN M_Product_Trl pt ON (iol.M_PRODUCT_ID=pt.M_PRODUCT_ID AND uom.AD_Language=pt.AD_Language)
/

-- 
-- VIEW: R_Request_v 
--

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