Scenario: Want to have a list of commitments (subcontracts and purchase orders) by Job without having to run multiple reports.

Requirements: None

Example Solution:

  1. Open the VA Inquiries form
  2. Enter udPMCommitments in the Query Name field
  3. Enter PM Project Commitments (SL & PO) in the title and description fields
  4. Type = SQL
  5. Query Text list at the bottom.
  6. Change Columns as desired in Columns tab
  7. Setup Parameters as follows:
    1. @JCCo,Type=5,Datatype=bJCCO
    2. @Job,Type=4,DataType=bJob
  8. Go to the PMProjects Form VA Inquiry
  9. On Links tab add udPMCommitments in Related Query Field
  10. Set Display Seq = 1
  11. 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)
  12. Open EM Projects Form
  13. Open Form Properties from the Tools menu
  14. Add a tab page Commitments on the Tab Pages tab.
  15. Select Commitments tab and click edit
  16. Set Control Type to 3 and select
    udPMCommitments in the Query Name field.
  17. Save and close/reopen forms.
  18. Test tab to validate functionality.
Step 11 Screen Shot

SQL Query

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