Here is a code that i developed to get the Accounts Payable Aging that can easily be exported or access thru excel.
----
declare @Asof as datetime
set @AsOf = '9/30/2010'
SELECT VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR
INTO [#APTB1]
FROM PM20000
WHERE (PSTGDATE <= @AsOf) and DOCTYPE in(1,2,3,5,6)
INSERT INTO [#APTB1]
(VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR)
SELECT VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR
FROM PM30200
WHERE (PSTGDATE <= @AsOf) and DOCTYPE in(1,2,3,5,6) AND (VOIDED = 0)
INSERT INTO [#APTB1]
(VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR)
SELECT VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR
FROM PM30200
WHERE (PSTGDATE <= @AsOf) and DOCTYPE in(1,2,3,5,6) AND (VOIDED = 1)
and rtrim(VCHRNMBR)+rtrim(VENDORID)+cast(DOCTYPE as varchar(1)) not in (SELECT rtrim(VCHRNMBR)+rtrim(VENDORID)+cast(DOCTYPE as varchar(1))
FROM PM30200
WHERE (VOIDPDATE <= @AsOf) AND (VOIDED = 1) and DOCTYPE in(1,2,3,5,6))
/*
INSERT INTO [#APTB1]
(VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR)
SELECT VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, VOIDPDATE, DOCNUMBR, DOCAMNT*-1, CURTRXAM-1, DUEDATE, PORDNMBR
FROM PM30200
WHERE (VOIDPDATE <= CONVERT(DATETIME, '2010-09-30 00:00:00', 102)) AND (VOIDED = 1) and DOCTYPE in(1,2,3,5,6)
*/
SELECT VENDORID, DOCDATE, DATE1, GLPOSTDT, VCHRNMBR, DOCTYPE, APFRDCNM, ApplyFromGLPostDate, APFRMAPLYAMT, ActualApplyToAmount,
ActualDiscTakenAmount, ActualWriteOffAmount, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, APPLDAMT
INTO [#APTB2]
FROM PM30300
WHERE (DATE1 <= @AsOf)
INSERT INTO [#APTB2]
(VENDORID, DOCDATE, DATE1, GLPOSTDT, VCHRNMBR, DOCTYPE, APFRDCNM, ApplyFromGLPostDate, APFRMAPLYAMT, ActualApplyToAmount,
ActualDiscTakenAmount, ActualWriteOffAmount, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, APPLDAMT)
SELECT VENDORID, DOCDATE, DOCDATE AS Expr1, ApplyToGLPostDate, VCHRNMBR, DOCTYPE, APFRDCNM, DOCDATE AS Expr2, APFRMAPLYAMT,
ActualApplyToAmount, ActualDiscTakenAmount, ActualWriteOffAmount, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, APPLDAMT
FROM PM20100
WHERE (DOCDATE <= @AsOf) and KEYSOURC<>'REMITTANCE'
SELECT VENDORID, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, SUM(APPLDAMT) AS APPLDAMT, SUM(ActualDiscTakenAmount) AS ActualDiscTakenAmount
INTO [#APTB3]
FROM [#APTB2]
GROUP BY VENDORID, APTVCHNM, APTODCTY, APTODCNM, APTODCDT
INSERT INTO [#APTB3]
(VENDORID, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, APPLDAMT,ActualDiscTakenAmount)
sELECT VENDORID, VCHRNMBR, DOCTYPE, MIN(APFRDCNM) AS APFRDCNM, DOCDATE, ISNULL(SUM(APPLDAMT), 0) AS APPLDAMT,
0 AS ActualDiscTakenAmount
FROM [#APTB2]
GROUP BY VENDORID, VCHRNMBR, DOCTYPE, DOCDATE
/*SELECT [#APTB1].VCHRNMBR, [#APTB1].VENDORID, [#APTB1].DOCTYPE, [#APTB1].DOCDATE, [#APTB1].PSTGDATE, [#APTB1].DOCNUMBR, [#APTB1].CURTRXAM,
[#APTB1].DUEDATE, [#APTB1].PORDNMBR, SUM([#APTB1].DOCAMNT) AS DOCAMNT, SUM([#APTB1].DOCAMNT - ISNULL([#APTB3].APPLDAMT, 0))
AS RemainingAmount, SUM(ISNULL([#APTB3].ActualDiscTakenAmount,0)) AS ActualDiscTakenAmount
INTO [#APTB4]
FROM [#APTB1] LEFT OUTER JOIN
[#APTB3] ON [#APTB1].VENDORID = [#APTB3].VENDORID AND [#APTB1].VCHRNMBR = [#APTB3].APTVCHNM AND
[#APTB1].DOCTYPE = [#APTB3].APTODCTY AND [#APTB1].DOCNUMBR = [#APTB3].APTODCNM AND [#APTB1].DOCDATE = [#APTB3].APTODCDT
GROUP BY [#APTB1].VCHRNMBR, [#APTB1].VENDORID, [#APTB1].DOCTYPE, [#APTB1].DOCDATE, [#APTB1].PSTGDATE, [#APTB1].DOCNUMBR,
[#APTB1].CURTRXAM, [#APTB1].DUEDATE, [#APTB1].PORDNMBR*/
SELECT [#APTB1].VCHRNMBR, [#APTB1].VENDORID, [#APTB1].DOCTYPE, [#APTB1].DOCDATE, [#APTB1].PSTGDATE, [#APTB1].DOCNUMBR, [#APTB1].CURTRXAM,
[#APTB1].DUEDATE, [#APTB1].PORDNMBR, SUM([#APTB1].DOCAMNT) AS DOCAMNT, SUM([#APTB1].DOCAMNT - ISNULL([#APTB3].APPLDAMT, 0))
AS RemainingAmount, SUM(ISNULL([#APTB3].ActualDiscTakenAmount,0)) AS ActualDiscTakenAmount
INTO [#APTB4]
from [#APTB1] LEFT OUTER JOIN
[#APTB3] ON [#APTB1].VENDORID = [#APTB3].VENDORID AND [#APTB1].VCHRNMBR = [#APTB3].APTVCHNM AND
[#APTB1].DOCTYPE = [#APTB3].APTODCTY
GROUP BY [#APTB1].VCHRNMBR, [#APTB1].VENDORID, [#APTB1].DOCTYPE, [#APTB1].DOCDATE, [#APTB1].PSTGDATE, [#APTB1].DOCNUMBR,
[#APTB1].CURTRXAM, [#APTB1].DUEDATE, [#APTB1].PORDNMBR
UPDATE [#APTB4]
SET DOCAMNT = DOCAMNT * - 1, RemainingAmount = RemainingAmount * - 1
WHERE (DOCTYPE IN (4,5,6))
UPDATE [#APTB4]
SET ActualDiscTakenAmount = ActualDiscTakenAmount * - 1
SELECT [#APTB4].VCHRNMBR, PM00200.VENDNAME, [#APTB4].VENDORID, [#APTB4].DOCTYPE, [#APTB4].DOCDATE, [#APTB4].PSTGDATE,[#APTB4].DOCNUMBR, [#APTB4].DUEDATE,
[#APTB4].PORDNMBR, SUM([#APTB4].DOCAMNT) AS DOCAMNT, SUM([#APTB4].RemainingAmount) AS RemainingAmount,
SUM([#APTB4].ActualDiscTakenAmount) AS ActualDiscTakenAmount, SUM([#APTB4].RemainingAmount + [#APTB4].ActualDiscTakenAmount)
AS Balance
FROM [#APTB4] INNER JOIN
PM00200 ON [#APTB4].VENDORID = PM00200.VENDORID
GROUP BY [#APTB4].VCHRNMBR, PM00200.VENDNAME, [#APTB4].VENDORID, [#APTB4].DOCTYPE, [#APTB4].DOCDATE, [#APTB4].DOCNUMBR,
[#APTB4].DUEDATE, [#APTB4].PORDNMBR,[#APTB4].PSTGDATE
HAVING (SUM([#APTB4].RemainingAmount + [#APTB4].ActualDiscTakenAmount) <> 0)
order by [#APTB4].VENDORID
drop table [#APTB1]
drop table [#APTB2]
drop table [#APTB3]
drop table [#APTB4]
From: Gerald Buenafe
Sent: Tuesday, October 05, 2010 4:10 PM
To: Gerald Buenafe
Cc: geraldbuenafe@hotmail.com
Subject: AP Aging
SELECT VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR
INTO [#APTB1]
FROM PM20000
WHERE (PSTGDATE <= CONVERT(DATETIME, '2010-09-30 00:00:00', 102)) and DOCTYPE in(1,2,3,5,6)
INSERT INTO [#APTB1]
(VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR)
SELECT VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR
FROM PM30200
WHERE (PSTGDATE <= CONVERT(DATETIME, '2010-09-30 00:00:00', 102)) and DOCTYPE in(1,2,3,5,6) AND (VOIDED = 0)
INSERT INTO [#APTB1]
(VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR)
SELECT VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR
FROM PM30200
WHERE (PSTGDATE <= CONVERT(DATETIME, '2010-09-30 00:00:00', 102)) and DOCTYPE in(1,2,3,5,6) AND (VOIDED = 1)
and rtrim(VCHRNMBR)+rtrim(VENDORID)+cast(DOCTYPE as varchar(1)) not in (SELECT rtrim(VCHRNMBR)+rtrim(VENDORID)+cast(DOCTYPE as varchar(1))
FROM PM30200
WHERE (VOIDPDATE <= CONVERT(DATETIME, '2010-09-30 00:00:00', 102)) AND (VOIDED = 1) and DOCTYPE in(1,2,3,5,6))
/*
INSERT INTO [#APTB1]
(VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR, DOCAMNT, CURTRXAM, DUEDATE, PORDNMBR)
SELECT VCHRNMBR, VENDORID, DOCTYPE, DOCDATE, VOIDPDATE, DOCNUMBR, DOCAMNT*-1, CURTRXAM-1, DUEDATE, PORDNMBR
FROM PM30200
WHERE (VOIDPDATE <= CONVERT(DATETIME, '2010-09-30 00:00:00', 102)) AND (VOIDED = 1) and DOCTYPE in(1,2,3,5,6)
*/
SELECT VENDORID, DOCDATE, DATE1, GLPOSTDT, VCHRNMBR, DOCTYPE, APFRDCNM, ApplyFromGLPostDate, APFRMAPLYAMT, ActualApplyToAmount,
ActualDiscTakenAmount, ActualWriteOffAmount, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, APPLDAMT
INTO [#APTB2]
FROM PM30300
WHERE (DATE1 <= CONVERT(DATETIME, '2010-09-30 00:00:00', 102))
INSERT INTO [#APTB2]
(VENDORID, DOCDATE, DATE1, GLPOSTDT, VCHRNMBR, DOCTYPE, APFRDCNM, ApplyFromGLPostDate, APFRMAPLYAMT, ActualApplyToAmount,
ActualDiscTakenAmount, ActualWriteOffAmount, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, APPLDAMT)
SELECT VENDORID, DOCDATE, DOCDATE AS Expr1, ApplyToGLPostDate, VCHRNMBR, DOCTYPE, APFRDCNM, DOCDATE AS Expr2, APFRMAPLYAMT,
ActualApplyToAmount, ActualDiscTakenAmount, ActualWriteOffAmount, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, APPLDAMT
FROM PM20100
WHERE (DOCDATE <= CONVERT(DATETIME, '2010-09-30 00:00:00', 102)) and KEYSOURC<>'REMITTANCE'
SELECT VENDORID, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, SUM(APPLDAMT) AS APPLDAMT, SUM(ActualDiscTakenAmount) AS ActualDiscTakenAmount
INTO [#APTB3]
FROM [#APTB2]
GROUP BY VENDORID, APTVCHNM, APTODCTY, APTODCNM, APTODCDT
INSERT INTO [#APTB3]
(VENDORID, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, APPLDAMT,ActualDiscTakenAmount)
sELECT VENDORID, VCHRNMBR, DOCTYPE, MIN(APFRDCNM) AS APFRDCNM, DOCDATE, ISNULL(SUM(APPLDAMT), 0) AS APPLDAMT,
0 AS ActualDiscTakenAmount
FROM [#APTB2]
GROUP BY VENDORID, VCHRNMBR, DOCTYPE, DOCDATE
/*SELECT [#APTB1].VCHRNMBR, [#APTB1].VENDORID, [#APTB1].DOCTYPE, [#APTB1].DOCDATE, [#APTB1].PSTGDATE, [#APTB1].DOCNUMBR, [#APTB1].CURTRXAM,
[#APTB1].DUEDATE, [#APTB1].PORDNMBR, SUM([#APTB1].DOCAMNT) AS DOCAMNT, SUM([#APTB1].DOCAMNT - ISNULL([#APTB3].APPLDAMT, 0))
AS RemainingAmount, SUM(ISNULL([#APTB3].ActualDiscTakenAmount,0)) AS ActualDiscTakenAmount
INTO [#APTB4]
FROM [#APTB1] LEFT OUTER JOIN
[#APTB3] ON [#APTB1].VENDORID = [#APTB3].VENDORID AND [#APTB1].VCHRNMBR = [#APTB3].APTVCHNM AND
[#APTB1].DOCTYPE = [#APTB3].APTODCTY AND [#APTB1].DOCNUMBR = [#APTB3].APTODCNM AND [#APTB1].DOCDATE = [#APTB3].APTODCDT
GROUP BY [#APTB1].VCHRNMBR, [#APTB1].VENDORID, [#APTB1].DOCTYPE, [#APTB1].DOCDATE, [#APTB1].PSTGDATE, [#APTB1].DOCNUMBR,
[#APTB1].CURTRXAM, [#APTB1].DUEDATE, [#APTB1].PORDNMBR*/
SELECT [#APTB1].VCHRNMBR, [#APTB1].VENDORID, [#APTB1].DOCTYPE, [#APTB1].DOCDATE, [#APTB1].PSTGDATE, [#APTB1].DOCNUMBR, [#APTB1].CURTRXAM,
[#APTB1].DUEDATE, [#APTB1].PORDNMBR, SUM([#APTB1].DOCAMNT) AS DOCAMNT, SUM([#APTB1].DOCAMNT - ISNULL([#APTB3].APPLDAMT, 0))
AS RemainingAmount, SUM(ISNULL([#APTB3].ActualDiscTakenAmount,0)) AS ActualDiscTakenAmount
INTO [#APTB4]
from [#APTB1] LEFT OUTER JOIN
[#APTB3] ON [#APTB1].VENDORID = [#APTB3].VENDORID AND [#APTB1].VCHRNMBR = [#APTB3].APTVCHNM AND
[#APTB1].DOCTYPE = [#APTB3].APTODCTY
GROUP BY [#APTB1].VCHRNMBR, [#APTB1].VENDORID, [#APTB1].DOCTYPE, [#APTB1].DOCDATE, [#APTB1].PSTGDATE, [#APTB1].DOCNUMBR,
[#APTB1].CURTRXAM, [#APTB1].DUEDATE, [#APTB1].PORDNMBR
UPDATE [#APTB4]
SET DOCAMNT = DOCAMNT * - 1, RemainingAmount = RemainingAmount * - 1
WHERE (DOCTYPE IN (4,5,6))
UPDATE [#APTB4]
SET ActualDiscTakenAmount = ActualDiscTakenAmount * - 1
SELECT [#APTB4].VCHRNMBR, PM00200.VENDNAME, [#APTB4].VENDORID, [#APTB4].DOCTYPE, [#APTB4].DOCDATE, [#APTB4].DOCNUMBR, [#APTB4].DUEDATE,
[#APTB4].PORDNMBR, SUM([#APTB4].DOCAMNT) AS DOCAMNT, SUM([#APTB4].RemainingAmount) AS RemainingAmount,
SUM([#APTB4].ActualDiscTakenAmount) AS ActualDiscTakenAmount, SUM([#APTB4].RemainingAmount + [#APTB4].ActualDiscTakenAmount)
AS Balance
FROM [#APTB4] INNER JOIN
PM00200 ON [#APTB4].VENDORID = PM00200.VENDORID
GROUP BY [#APTB4].VCHRNMBR, PM00200.VENDNAME, [#APTB4].VENDORID, [#APTB4].DOCTYPE, [#APTB4].DOCDATE, [#APTB4].DOCNUMBR,
[#APTB4].DUEDATE, [#APTB4].PORDNMBR
HAVING (SUM([#APTB4].RemainingAmount + [#APTB4].ActualDiscTakenAmount) <> 0)
order by [#APTB4].VENDORID
drop table [#APTB1]
drop table [#APTB2]
drop table [#APTB3]
drop table [#APTB4]
Thursday, October 21, 2010
How To Find the Accounts used by an Asset
Below is a query to find what are the accounts used in Fixed Assets.
-----
SELECT FA00100.ASSETID, FA00100.ASSETIDSUF, FA00100.ASSETDESC, FA00100.LOCATNID, FA00100.ASSETSTATUS,
GL00105_1.ACTNUMBR_2 AS DepreciationExpenseLocation, GL00105_1.ACTNUMST AS DepreciationExpenseAccount,
GL00105.ACTNUMBR_2 AS DepreciationReserverLocation, GL00105.ACTNUMST AS DepreciationReserveAccount,
GL00105_2.ACTNUMBR_2 AS PriorYearDepreciationLocation, GL00105_2.ACTNUMST AS PriorYearDepreciationAccount,
GL00105_3.ACTNUMBR_2 AS AssetCostLocation, GL00105_3.ACTNUMST AS AssetCostAccount, GL00105_4.ACTNUMBR_2 AS ProceedsLocation,
GL00105_4.ACTNUMST AS ProceedsAccount, GL00105_5.ACTNUMBR_2 AS GainandLossLocation, GL00105_5.ACTNUMST AS GainandLossAccount,
GL00105_6.ACTNUMBR_2 AS NonRecognizedLocation, GL00105_6.ACTNUMST AS NonRecognizedAccount, GL00105_7.ACTNUMBR_2 AS ClearingLocation,
GL00105_7.ACTNUMST AS ClearingAccount
FROM FA00400 INNER JOIN
FA00100 ON FA00400.ASSETINDEX = FA00100.ASSETINDEX INNER JOIN
GL00105 ON FA00400.DEPRRESVACCTINDX = GL00105.ACTINDX INNER JOIN
GL00105 AS GL00105_2 ON FA00400.PRIORYRDEPRACCTINDX = GL00105_2.ACTINDX INNER JOIN
GL00105 AS GL00105_3 ON FA00400.ASSETCOSTACCTINDX = GL00105_3.ACTINDX INNER JOIN
GL00105 AS GL00105_4 ON FA00400.PROCEEDSACCTINDX = GL00105_4.ACTINDX INNER JOIN
GL00105 AS GL00105_5 ON FA00400.RECGAINLOSSACCTINDX = GL00105_5.ACTINDX INNER JOIN
GL00105 AS GL00105_6 ON FA00400.NONRECGAINLOSSACCTINDX = GL00105_6.ACTINDX INNER JOIN
GL00105 AS GL00105_7 ON FA00400.CLEARINGACCTINDX = GL00105_7.ACTINDX LEFT OUTER JOIN
GL00105 AS GL00105_1 ON FA00400.DEPREXPACCTINDX = GL00105_1.ACTINDX
---
-----
SELECT FA00100.ASSETID, FA00100.ASSETIDSUF, FA00100.ASSETDESC, FA00100.LOCATNID, FA00100.ASSETSTATUS,
GL00105_1.ACTNUMBR_2 AS DepreciationExpenseLocation, GL00105_1.ACTNUMST AS DepreciationExpenseAccount,
GL00105.ACTNUMBR_2 AS DepreciationReserverLocation, GL00105.ACTNUMST AS DepreciationReserveAccount,
GL00105_2.ACTNUMBR_2 AS PriorYearDepreciationLocation, GL00105_2.ACTNUMST AS PriorYearDepreciationAccount,
GL00105_3.ACTNUMBR_2 AS AssetCostLocation, GL00105_3.ACTNUMST AS AssetCostAccount, GL00105_4.ACTNUMBR_2 AS ProceedsLocation,
GL00105_4.ACTNUMST AS ProceedsAccount, GL00105_5.ACTNUMBR_2 AS GainandLossLocation, GL00105_5.ACTNUMST AS GainandLossAccount,
GL00105_6.ACTNUMBR_2 AS NonRecognizedLocation, GL00105_6.ACTNUMST AS NonRecognizedAccount, GL00105_7.ACTNUMBR_2 AS ClearingLocation,
GL00105_7.ACTNUMST AS ClearingAccount
FROM FA00400 INNER JOIN
FA00100 ON FA00400.ASSETINDEX = FA00100.ASSETINDEX INNER JOIN
GL00105 ON FA00400.DEPRRESVACCTINDX = GL00105.ACTINDX INNER JOIN
GL00105 AS GL00105_2 ON FA00400.PRIORYRDEPRACCTINDX = GL00105_2.ACTINDX INNER JOIN
GL00105 AS GL00105_3 ON FA00400.ASSETCOSTACCTINDX = GL00105_3.ACTINDX INNER JOIN
GL00105 AS GL00105_4 ON FA00400.PROCEEDSACCTINDX = GL00105_4.ACTINDX INNER JOIN
GL00105 AS GL00105_5 ON FA00400.RECGAINLOSSACCTINDX = GL00105_5.ACTINDX INNER JOIN
GL00105 AS GL00105_6 ON FA00400.NONRECGAINLOSSACCTINDX = GL00105_6.ACTINDX INNER JOIN
GL00105 AS GL00105_7 ON FA00400.CLEARINGACCTINDX = GL00105_7.ACTINDX LEFT OUTER JOIN
GL00105 AS GL00105_1 ON FA00400.DEPREXPACCTINDX = GL00105_1.ACTINDX
---
Tuesday, April 7, 2009
Subscribe to:
Posts (Atom)