views.sql

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

	bpcg.Greeting AS BPContactGreeting,
	bpc.Title, bpc.Phone,
	NULLIF (bpc.Name, bp.Name) AS ContactName,
	bpl.C_Location_ID,
	bp.ReferenceNo,
	pt.Name AS PaymentTerm, pt.DocumentNote AS PaymentTermNote,
    p.POReference,
    p.C_Currency_ID, p.M_PriceList_Version_ID,
    p.C_Campaign_ID,
    p.PlannedAmt, p.PlannedQty, p.PlannedMarginAmt, p.InvoicedAmt, p.InvoicedQty, p.ProjectBalanceAmt,
    p.IsCommitment, p.CommittedAmt, p.CommittedQty, p.DateContract, p.DateFinish, p.IsCommitCeiling,
    p.M_Warehouse_ID
FROM C_Project p
	LEFT OUTER JOIN C_BPartner bp ON (p.C_BPartner_ID=bp.C_BPartner_ID)
	INNER JOIN AD_OrgInfo oi ON (p.AD_Org_ID=oi.AD_Org_ID)
	LEFT OUTER JOIN C_PaymentTerm_Trl pt ON (p.C_PaymentTerm_ID=pt.C_PaymentTerm_ID)
    LEFT OUTER JOIN C_ProjectType pjt ON (p.C_ProjectType_ID=pjt.C_ProjectType_ID)
    LEFT OUTER JOIN C_Phase pjp ON (p.C_Phase_ID=pjp.C_Phase_ID)
	LEFT OUTER JOIN AD_User u ON (p.SalesRep_ID=u.AD_User_ID)
	LEFT OUTER JOIN C_BPartner ubp ON (u.C_BPartner_ID=ubp.C_BPartner_ID)
	LEFT OUTER JOIN C_Greeting bpg on (bp.C_Greeting_ID=bpg.C_Greeting_ID)
	LEFT OUTER JOIN AD_User bpc ON (p.AD_User_ID=bpc.AD_User_ID)
	LEFT OUTER JOIN C_Greeting bpcg on (bpc.C_Greeting_ID=bpcg.C_Greeting_ID)
	LEFT OUTER JOIN C_BPartner_Location bpl ON (p.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)
/

-- 
-- VIEW: C_Project_Details_v 
--
CREATE OR REPLACE VIEW C_Project_Details_v 
AS
SELECT pl.AD_Client_ID, pl.AD_Org_ID, pl.IsActive, pl.Created, pl.CreatedBy, pl.Updated, pl.UpdatedBy,
	'en_US' AS AD_Language,
    pj.C_Project_ID, pl.C_ProjectLine_ID,
    pl.Line, 
    pl.PlannedQty, pl.PlannedPrice, pl.PlannedAmt, pl.PlannedMarginAmt,
    pl.CommittedAmt,
    pl.M_Product_ID,
	COALESCE(p.Name, pl.Description) AS Name, -- main line
	CASE WHEN p.Name IS NOT NULL THEN pl.Description END AS Description, -- second line
	p.DocumentNote, -- third line
    p.UPC, p.SKU, p.Value AS ProductValue,
    pl.M_Product_Category_ID,
    pl.InvoicedAmt, pl.InvoicedQty, pl.CommittedQty
FROM C_ProjectLine pl
  INNER JOIN C_Project pj ON (pl.C_Project_ID=pj.C_Project_ID)
  LEFT OUTER JOIN M_Product p ON (pl.M_Product_ID=p.M_Product_ID)
WHERE pl.IsPrinted='Y'
/

-- 
-- VIEW: C_Project_Details_vt
--
CREATE OR REPLACE VIEW C_Project_Details_vt 
AS
SELECT pl.AD_Client_ID, pl.AD_Org_ID, pl.IsActive, pl.Created, pl.CreatedBy, pl.Updated, pl.UpdatedBy,
	l.AD_Language,
    pj.C_Project_ID, pl.C_ProjectLine_ID,
    pl.Line, 
    pl.PlannedQty, pl.PlannedPrice, pl.PlannedAmt, pl.PlannedMarginAmt,
    pl.CommittedAmt,
    pl.M_Product_ID,
	COALESCE(p.Name, pl.Description) AS Name, -- main line
	CASE WHEN p.Name IS NOT NULL THEN pl.Description END AS Description, -- second line
	p.DocumentNote, -- third line
    p.UPC, p.SKU, p.Value AS ProductValue,
    pl.M_Product_Category_ID,
    pl.InvoicedAmt, pl.InvoicedQty, pl.CommittedQty
FROM C_ProjectLine pl
  INNER JOIN C_Project pj ON (pl.C_Project_ID=pj.C_Project_ID)
  LEFT OUTER JOIN M_Product p ON (pl.M_Product_ID=p.M_Product_ID)
  INNER JOIN AD_Language l ON (l.IsSystemLanguage='Y')
WHERE pl.IsPrinted='Y'  
/

--
--  VIEW: RfQ Response Header
--
CREATE OR REPLACE VIEW C_RfQResponse_v
AS
SELECT rr.C_RfQResponse_ID, rr.C_RfQ_ID, 
    rr.AD_Client_ID, rr.AD_Org_ID, rr.IsActive, rr.Created, rr.CreatedBy, rr.Updated, rr.UpdatedBy,
	'en_US' AS AD_Language,
	oi.C_Location_ID AS Org_Location_ID, oi.TaxID,
    r.Name, r.Description, r.Help,
    r.C_Currency_ID, c.ISO_Code,
    r.DateResponse, r.DateWorkStart, r.DeliveryDays,
    rr.C_BPartner_ID, bp.Name AS BPName, bp.Name2 AS BPName2,
    rr.C_BPartner_Location_ID, bpl.C_Location_ID,
    rr.AD_User_ID, bpc.Title, bpc.Phone,
    NULLIF (bpc.Name, bp.Name) AS ContactName
FROM C_RfQResponse rr
  INNER JOIN C_RfQ r ON (rr.C_RfQ_ID=r.C_RfQ_ID)
  INNER JOIN AD_OrgInfo oi ON (rr.AD_Org_ID=oi.AD_Org_ID)
  INNER JOIN C_Currency c ON (r.C_Currency_ID=c.C_Currency_ID)
  INNER JOIN C_BPartner bp ON (rr.C_BPartner_ID=bp.C_BPartner_ID)
  INNER JOIN C_BPartner_Location bpl ON (rr.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)
  LEFT OUTER JOIN AD_User bpc ON (rr.AD_User_ID=bpc.AD_User_ID)
/

--
--  VIEW: RfQ Response Header (trl)
--
CREATE OR REPLACE VIEW C_RfQResponse_vt
AS
SELECT rr.C_RfQResponse_ID, rr.C_RfQ_ID, 
    rr.AD_Client_ID, rr.AD_Org_ID, rr.IsActive, rr.Created, rr.CreatedBy, rr.Updated, rr.UpdatedBy,
	l.AD_Language,
	oi.C_Location_ID AS Org_Location_ID, oi.TaxID,
    r.Name, r.Description, r.Help,
    r.C_Currency_ID, c.ISO_Code,
    r.DateResponse, r.DateWorkStart, r.DeliveryDays,
    rr.C_BPartner_ID, bp.Name AS BPName, bp.Name2 AS BPName2,
    rr.C_BPartner_Location_ID, bpl.C_Location_ID,
    rr.AD_User_ID, bpc.Title, bpc.Phone,
    NULLIF (bpc.Name, bp.Name) AS ContactName
FROM C_RfQResponse rr
  INNER JOIN C_RfQ r ON (rr.C_RfQ_ID=r.C_RfQ_ID)
  INNER JOIN AD_OrgInfo oi ON (rr.AD_Org_ID=oi.AD_Org_ID)
  INNER JOIN C_Currency c ON (r.C_Currency_ID=c.C_Currency_ID)
  INNER JOIN C_BPartner bp ON (rr.C_BPartner_ID=bp.C_BPartner_ID)
  INNER JOIN C_BPartner_Location bpl ON (rr.C_BPartner_Location_ID=bpl.C_BPartner_Location_ID)
  LEFT OUTER JOIN AD_User bpc ON (rr.AD_User_ID=bpc.AD_User_ID)
  INNER JOIN AD_Language l ON (l.IsSystemLanguage='Y')
/

--
--  VIEW: RfQ Response Line
--
CREATE OR REPLACE VIEW C_RfQResponseLine_v
AS
SELECT rrl.C_RfQResponse_ID, rrl.C_RfQResponseLine_ID, rrl.C_RfQLine_ID, 
    rq.C_RfQResponseLineQty_ID, rq.C_RfQLineQty_ID,
    rrl.AD_Client_ID, rrl.AD_Org_ID, rrl.IsActive, rrl.Created, rrl.CreatedBy, rrl.Updated, rrl.UpdatedBy,
	'en_US' AS AD_Language,
    rl.Line,
    rl.M_Product_ID, rl.M_AttributeSetInstance_ID,
	COALESCE(p.Name||productAttribute(rl.M_AttributeSetInstance_ID), rl.Description) AS Name, -- main line
	CASE WHEN p.Name IS NOT NULL THEN rl.Description END AS Description, -- second line
	p.DocumentNote, -- third line
    p.UPC, p.SKU, p.Value AS ProductValue,
    rl.Help,
    rl.DateWorkStart, rl.DeliveryDays,
    q.C_UOM_ID, uom.UOMSymbol, q.BenchmarkPrice,
    q.Qty, rq.Price, rq.Discount
FROM C_RfQResponseLineQty rq
  INNER JOIN C_RfQLineQty q ON (rq.C_RfQLineQty_ID=q.C_RfQLineQty_ID)
  INNER JOIN C_UOM uom ON (q.C_UOM_ID=uom.C_UOM_ID)
  INNER JOIN C_RfQResponseLine rrl ON (rq.C_RfQResponseLine_ID=rrl.C_RfQResponseLine_ID)
  INNER JOIN C_RfQLine rl ON (rrl.C_RfQLine_ID=rl.C_RfQLine_ID)
  LEFT OUTER JOIN M_Product p ON (rl.M_Product_ID=p.M_Product_ID)
WHERE rq.IsActive='Y' AND q.IsActive='Y'
  AND rrl.IsActive='Y' AND rl.IsActive='Y'
/

--
--  VIEW: RfQ Response Line (trl)
--
CREATE OR REPLACE VIEW C_RfQResponseLine_vt
AS
SELECT rrl.C_RfQResponse_ID, rrl.C_RfQResponseLine_ID, rrl.C_RfQLine_ID, 
    rq.C_RfQResponseLineQty_ID, rq.C_RfQLineQty_ID,
    rrl.AD_Client_ID, rrl.AD_Org_ID, rrl.IsActive, rrl.Created, rrl.CreatedBy, rrl.Updated, rrl.UpdatedBy,
	l.AD_Language,
    rl.Line,
    rl.M_Product_ID, rl.M_AttributeSetInstance_ID,
	COALESCE(p.Name||productAttribute(rl.M_AttributeSetInstance_ID), rl.Description) AS Name, -- main line
	CASE WHEN p.Name IS NOT NULL THEN rl.Description END AS Description, -- second line
	p.DocumentNote, -- third line
    p.UPC, p.SKU, p.Value AS ProductValue,
    rl.Help,
    rl.DateWorkStart, rl.DeliveryDays,
    q.C_UOM_ID, uom.UOMSymbol,
    q.Qty, rq.Price, rq.Discount
FROM C_RfQResponseLineQty rq
  INNER JOIN C_RfQLineQty q ON (rq.C_RfQLineQty_ID=q.C_RfQLineQty_ID)
  INNER JOIN C_UOM uom ON (q.C_UOM_ID=uom.C_UOM_ID)
  INNER JOIN C_RfQResponseLine rrl ON (rq.C_RfQResponseLine_ID=rrl.C_RfQResponseLine_ID)
  INNER JOIN C_RfQLine rl ON (rrl.C_RfQLine_ID=rl.C_RfQLine_ID)
  LEFT OUTER JOIN M_Product p ON (rl.M_Product_ID=p.M_Product_ID)
  INNER JOIN AD_Language l ON (l.IsSystemLanguage='Y')
WHERE rq.IsActive='Y' AND q.IsActive='Y'
  AND rrl.IsActive='Y' AND rl.IsActive='Y'
/

--
--  VIEW: RfQ Reponse Line Qty
--
CREATE OR REPLACE VIEW C_RfQResponseLineQty_v
AS
SELECT rq.C_RfQResponseLine_ID, rq.C_RfQResponseLineQty_ID, rq.C_RfQLineQty_ID,
    rq.AD_Client_ID, rq.AD_Org_ID, rq.IsActive, rq.Created, rq.CreatedBy, rq.Updated, rq.UpdatedBy,
	'en_US' AS AD_Language,
    q.C_UOM_ID, uom.UOMSymbol,
    q.Qty, rq.Price, rq.Discount
FROM C_RfQResponseLineQty rq
  INNER JOIN C_RfQLineQty q ON (rq.C_RfQLineQty_ID=q.C_RfQLineQty_ID)
  INNER JOIN C_UOM uom ON (q.C_UOM_ID=uom.C_UOM_ID)
WHERE rq.IsActive='Y' AND q.IsActive='Y'
/

--
--  VIEW: RfQ Reponse Line Qty (trl)
--
CREATE OR REPLACE VIEW C_RfQResponseLineQty_vt
AS
SELECT rq.C_RfQResponseLine_ID, rq.C_RfQResponseLineQty_ID, rq.C_RfQLineQty_ID,
    rq.AD_Client_ID, rq.AD_Org_ID, rq.IsActive, rq.Created, rq.CreatedBy, rq.Updated, rq.UpdatedBy,
	l.AD_Language,
    q.C_UOM_ID, uom.UOMSymbol,
    q.Qty, rq.Price, rq.Discount
FROM C_RfQResponseLineQty rq
  INNER JOIN C_RfQLineQty q ON (rq.C_RfQLineQty_ID=q.C_RfQLineQty_ID)
  INNER JOIN C_UOM uom ON (q.C_UOM_ID=uom.C_UOM_ID)
  INNER JOIN AD_Language l ON (l.IsSystemLanguage='Y')
WHERE rq.IsActive='Y' AND q.IsActive='Y'
/


-- 
-- VIEW: M_InOutConfirm_v 
--
CREATE OR REPLACE VIEW M_InOutConfirm_v 
AS
SELECT ioc.AD_Client_ID, ioc.AD_Org_ID, ioc.IsActive, ioc.Created, ioc.CreatedBy, ioc.Updated, ioc.UpdatedBy,
  'en_US' AS AD_Language,
  ioc.M_InOutConfirm_ID,
  ioc.DocumentNo, ioc.ConfirmType,
  ioc.IsApproved, ioc.IsCancelled, ioc.Description,
  --
  io.M_InOut_ID, io.Description AS ShipDescription,
  io.C_BPartner_ID, io.C_BPartner_Location_ID, io.AD_User_ID,
  io.SalesRep_ID, io.C_DocType_ID, dt.PrintName AS DocumentType,
  io.C_Order_ID, io.DateOrdered, io.MovementDate, io.MovementType, 
  io.M_Warehouse_ID, io.POReference,
  io.DeliveryRule, io.FreightCostRule,
  io.DeliveryViaRule, io.M_Shipper_ID, PriorityRule,
  ioc.Processed
FROM M_InOutConfirm ioc
  INNER JOIN M_InOut io ON (ioc.M_InOut_ID=io.M_InOut_ID)
  INNER JOIN C_DocType dt ON (io.C_DocType_ID=dt.C_DocType_ID)
/

-- 
-- VIEW: M_InOutConfirm_vt
--
CREATE OR REPLACE VIEW M_InOutConfirm_vt 
AS
SELECT ioc.AD_Client_ID, ioc.AD_Org_ID, ioc.IsActive, ioc.Created, ioc.CreatedBy, ioc.Updated, ioc.UpdatedBy,
  dt.AD_Language,
  ioc.M_InOutConfirm_ID,
  ioc.DocumentNo, ioc.ConfirmType,
  ioc.IsApproved, ioc.IsCancelled, ioc.Description,
  --
  io.M_InOut_ID, io.Description AS ShipDescription,
  io.C_BPartner_ID, io.C_BPartner_Location_ID, io.AD_User_ID,
  io.SalesRep_ID, io.C_DocType_ID, dt.PrintName AS DocumentType,
  io.C_Order_ID, io.DateOrdered, io.MovementDate, io.MovementType, 
  io.M_Warehouse_ID, io.POReference,
  io.DeliveryRule, io.FreightCostRule,
  io.DeliveryViaRule, io.M_Shipper_ID, PriorityRule,
  ioc.Processed
FROM M_InOutConfirm ioc
  INNER JOIN M_InOut io ON (ioc.M_InOut_ID=io.M_InOut_ID)
  INNER JOIN C_DocType_Trl dt ON (io.C_DocType_ID=dt.C_DocType_ID)
/

-- 
-- VIEW: M_InOut_LineConfirm_v 
--
CREATE OR REPLACE VIEW M_InOut_LineConfirm_v 
AS
SELECT iolc.AD_Client_ID, iolc.AD_Org_ID, iolc.IsActive, iolc.Created, iolc.CreatedBy, iolc.Updated, iolc.UpdatedBy,
	'en_US' AS AD_Language,
    iolc.M_InOutLineConfirm_ID, iolc.M_InOutConfirm_ID,
    iolc.TargetQty, iolc.ConfirmedQty, iolc.DifferenceQty, iolc.ScrappedQty,
    iolc.Description, iolc.Processed,
	iol.M_InOut_ID, iol.M_InOutLine_ID, 
	iol.Line, 
	iol.MovementQty, uom.UOMSymbol, ol.QtyOrdered-ol.QtyDelivered AS QtyBackOrdered,
	COALESCE(p.Name, iol.Description) AS Name, -- main line
	CASE WHEN p.Name IS NOT NULL THEN iol.Description END AS ShipDescription, -- second line
	p.DocumentNote, -- third line
    p.UPC, p.SKU, p.Value AS ProductValue,
	iol.M_Locator_ID, l.M_Warehouse_ID, l.X, l.Y, l.Z,
    iol.M_AttributeSetInstance_ID, asi.M_AttributeSet_ID, asi.SerNo, asi.Lot, asi.M_Lot_ID,asi.GuaranteeDate
FROM M_InOutLineConfirm iolc
    INNER JOIN M_InOutLine iol ON (iolc.M_InOutLine_ID=iol.M_InOutLine_ID)
	INNER JOIN C_UOM uom ON (iol.C_UOM_ID=uom.C_UOM_ID)
	LEFT OUTER JOIN M_Pro