Purchase Order Commitments View

I been working on a report where the customer requested to view the payment voucher with its corresponding commitments information and had a need to have Committed Amount, Actual Amount and Budget Amount, view below details all the needed information about this subject:

SELECT     ACTINDX, BUDGETAMT,  
ISNULL((SELECT     SUM(DEBITAMT - CRDTAMNT) AS Actual FROM          dbo.GL20000 
WHERE      (OPENYEAR = MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS ACTUAL,

ISNULL((SELECT     SUM(DEBITAMT - CRDTAMNT) AS Actual FROM          dbo.GL10001 
WHERE      (YEAR1 = MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS UNPOSTED,

ISNULL((SELECT     SUM(Committed_Amount) FROM         dbo.CPO10110 WHERE      
(YEAR(REQDATE)= MAIN.YEAR1) AND (ACTINDX = MAIN.ACTINDX)), 0) AS Committed_Amount

FROM         
(SELECT     YEAR1, SUM(BUDGETAMT) AS BUDGETAMT, ACTINDX FROM         dbo.GL00201 AS MASTER
WHERE     (BUDGETID = (SELECT     TOP (1) BUDGETID FROM         dbo.CPO40002 
WHERE     (YEAR1 = YEAR(GETDATE())))) GROUP BY ACTINDX, YEAR1) AS MAIN

---

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com