AcumaticaSQLQueriesAndFunctions/PurchaseOrderReceipts.sql

IF OBJECT_ID ('dbo.PurchaseOrderReceipts','V') IS NOT NULL DROP VIEW dbo.PurchaseOrderReceipts
GO 
CREATE VIEW dbo.PurchaseOrderReceipts
AS
SELECT r.CompanyID, 
	   r.ReceiptNbr,
       r.ReceiptType,
       r.Status,
       r.ReceiptDate,
       r.FinPeriodID,
       r.VendorID,
       r.VendorLocationID,
       r.ProjectID,
       c.ContractCD,
       r.CuryOrderTotal,  
       r.Invtrefnbr,
       (SELECT sum(t.CuryTranAmt) from APTran t where t.CompanyID = r.CompanyID and t.ReceiptNbr = r.ReceiptNbr) as BilledAmount,
       (SELECT sum(a.PPVAmt) from POAccrualSplit a where a.CompanyID  = r.CompanyID and a.POReceiptType = r.ReceiptType and a.POReceiptNbr = r.ReceiptNbr) as PPVAmount,
       (SELECT top 1 pr.PONbr from POOrderReceipt pr where pr.CompanyID = r.CompanyID and pr.ReceiptType = r.ReceiptType and pr.ReceiptNbr = r.ReceiptNbr) as PONumber,
       (SELECT p.Status from POOrder p where p.CompanyID = r.CompanyID and p.OrderNbr = (SELECT top 1 pr.PONbr from POOrderReceipt pr where pr.CompanyID = r.CompanyID and pr.ReceiptType = r.ReceiptType and pr.ReceiptNbr = r.ReceiptNbr)) as POStatus,
       (SELECT sum(o.CuryOrderTotal)
from POOrderReceipt pr 
join POOrder o on pr.CompanyID = o.CompanyID and pr.POType = o.OrderType and pr.PONbr = o.OrderNbr 
where pr.CompanyID = r.CompanyID 
and pr.ReceiptType = r.ReceiptType 
and pr.ReceiptNbr = r.ReceiptNbr) as POTotal
FROM POReceipt r
left join Contract c on r.CompanyID = c.CompanyID and r.ProjectID = c.ContractID