Scenario: Want to have a list of commitments (subcontracts and purchase orders) by Job without having to run multiple reports.
- Open the VA Inquiries form
- Enter udPMCommitments in the Query Name field
- Enter PM Project Commitments (SL & PO) in the title and description fields
- Type = SQL
- Query Text list at the bottom.
- Change Columns as desired in Columns tab
- Setup Parameters as follows:
- Go to the PMProjectsForm VA Inquiry
- On Links tab add udPMCommitments in Related Query Field
- Set Display Seq = 1
- Double Click the udPMCommitments record to load the Inquiry links form and @JCCo to use Default Value and @Job Matching Column to be Project (see screen shot below)
- Open EM Projects Form
- Open Form Properties from the Tools menu
- Add a tab page Commitments on the Tab Pages tab.
- Select Commitments tab and click edit
- Set Control Type to 3 and select
udPMCommitments in the Query Name field.
- Save and close/reopen forms.
- Test tab to validate functionality.
Step 11 Screen Shot
SELECT * FROM ( SELECT b.SLCo as Co ,s.VendorGroup ,s.Vendor ,v.Name ,i.JCCo ,i.Job ,'SL' as Type ,b.SL as Commitment, sum(b.OrigItemCost) as OriginalContract, sum(b.ChangeOrderCost) as Changes, sum(b.OrigItemCost + b.ChangeOrderCost) as CurrentContract , sum(b.APTDAmt) as Billed , sum(CASE WHEN APTDStatus > 2 THEN b.APTDAmt ELSE 0 END) as Paid , sum(CASE WHEN APTDStatus = 2 and PayType = 4 THEN b.APTDAmt ELSE 0 END) as Retainage FROM brvSLSubContrByJob b JOIN SLIT i ON b.SLCo=i.SLCo and b.SL = i.SL JOIN SLHD s ON i.SLCo=s.SLCo and i.SL = s.SL JOIN APVM v ON s.VendorGroup = v.VendorGroup and s.Vendor = v.Vendor WHERE i.JCCo = @JCCo and i.Job = @Job GROUP BY b.SLCo,s.VendorGroup,s.Vendor,v.Name,i.JCCo,i.Job,b.SL UNION ALL SELECT POCo ,b.VendorGroup ,b.Vendor ,v.Name ,POITJCCo ,POITJob ,'PO' as Type ,PO as Commitment ,sum(OrigCost) as OriginalContract, sum(ChgTotCost) as Changes, sum(isnull(OrigCost,0) + isnull(ChgTotCost,0)) as CurrentContract ,sum(InvAmount) as Billed ,sum(PaidAmount) as Paid ,sum(Retainage) as Retainage FROM brvPOPurchStats b JOIN APVM v ON b.VendorGroup = v.VendorGroup and b.Vendor = v.Vendor WHERE POITJCCo = @JCCo and POITJob = @Job GROUP BY POCo,b.VendorGroup,b.Vendor,v.Name,POITJCCo,POITJob,PO )t1 ORDER BY Vendor