Reporting on Fixed Assets Not Yet Placed in Service
Use SQL Reporting Services to report on fixed assets purchases still in the fixed asset clearing account(s).
When Purchasing Interface Options are turned on in Fixed Assets, payables invoices can be posted to one or more fixed asset clearing accounts. Identifying which transactions still remain in the clearing accounts can be time-consuming if you have to run a GL trial balance and determine which debits and credits offset and remain to typically be recorded in a spreadsheet for tracking purposes.
Assume the following:
- Purchasing Interface Options for PM and/or POP have been activated in the Fixed Assets Company Setup screen
- One or more fixed-asset purchasing posting accounts have been set up in the Fixed Assets Purchasing Posting Accounts Setup screen
Note: “Delete Purchasing Transactions Immediately” is checked below, but it is not necessary for the SQL report described to return a correct listing of asset dollars waiting to be placed in service.
One of the benefits of activating one or both of the Purchasing Interface Options is when data is transferred from Payables Management or Purchase Order Processing to Fixed Asset Management, it is saved in an intermediate table called the FA-Purchasing Post table. Information from this table can be used to report on all invoice amounts posted to the FA clearing account yet to be placed in service.
Some of the 61 fields contained in FA01100, the FA-Purchasing Post table, are shown below. Only a few data fields, such as Asset Description, Amounts, Dates and Vendor ID, are populated if the transaction source is from Purchasing>Transactions>Transaction Entry. If the transaction source is from a purchase order receipt entry, most of the fields will be populated, if the Purchasing Interface Option for POP is by Receipt Line. If the option is By Account, a limited number of fields will be populated.
Sample fields include:
The sample report below is based on data entered from a payables invoice. Since this is an SQL Reporting Services report, it can easily be exported to Excel.
Here is the SQL script used to develop the above report:
–Get data from open invoices
SELECT PM20000.TAXSCHID, PM20000.VENDORID, PM00200.VENDNAME, PM20000.DOCDATE, PM20000.DOCNUMBR, PM20000.DOCAMNT,
FROM PM20000 INNER JOIN
FA01100 ON PM20000.VENDORID = FA01100.VENDORID AND PM20000.DOCNUMBR = FA01100.DOCNUMBR INNER JOIN
PM00200 ON PM20000.VENDORID = PM00200.VENDORID
–Get data from historical invoices
SELECT PM30200.TAXSCHID, PM30200.VENDORID, PM00200_1.VENDNAME, PM30200.DOCDATE, PM30200.DOCNUMBR, PM30200.DOCAMNT,
FROM PM30200 INNER JOIN
FA01100 AS FA01100_1 ON PM30200.VENDORID = FA01100_1.VENDORID AND PM30200.DOCNUMBR = FA01100_1.DOCNUMBR INNER JOIN
PM00200 AS PM00200_1 ON PM30200.VENDORID = PM00200_1.VENDORID
ORDER BY PM00200.VENDNAME, PM20000.DOCDATE