Sunday, November 13, 2011

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

Related Posts:

Related Posts with Thumbnails