January 21st, 2010 by Tyson
3 comments »
I need to export (using SQL) a PeopleSoft tree – showing all Tree Nodes and Leafs from the Top of the tree all the way down. levels are not used on the tree I am trying to retrieve. Can someone please help – it would be much appreciated!
Here is the SQL I use to get the Parent Node, Node, Detail Value (Leaf), and Level from trees in PeopleSoft via SQL:
Note: Click View Plain in the codeblock below to see all the SQL correctly. I’m working on my current WP theme to get it to quit interfering with my syntax highlighter.
SELECT PSTREENODE.SETID,
PSTREENODE.TREE_NAME,
PSTREENODE.EFFDT,
PARENT_NODE_NAME AS PARENTNODE,
TREE_NODE AS NODENAME,
TREE_NODE AS TREENODE,
TREE_LEVEL_NUM,
TREE_NODE_NUM
FROM PSTREENODE
--- REPLACE WITH YOUR SETID
WHERE PSTREENODE.SETID = 'SHARE'
--- REPLACE WITH YOUR TREE NAME
AND TREE_NAME = 'ACCOUNT'
--- REPLACE WITH YOUR TREE EFFECTIVE DATE
AND EFFDT = TO_DATE('01011900','MMDDYYYY')
UNION ALL
--- REPLACE WITH YOUR SETID
SELECT 'SHARE',
N.TREE_NAME,
--- REPLACE WITH YOUR TREE EFFECTIVE DATE
TO_DATE('01011900','MMDDYYYY'),
N.TREE_NODE AS PARENTNODE,
A.DESCR AS NODENAME,
--- REPLACE A.ACCOUNT WITH A.your_chartfield
A.ACCOUNT AS TREENODE,
TREE_LEVEL_NUM,
N.TREE_NODE_NUM
FROM PSTREENODE N
INNER JOIN PSTREELEAF L
ON N.SETID = L.SETID
AND N.TREE_NAME = L.TREE_NAME
--- REPLACE WITH YOUR SETID
AND N.SETID = 'SHARE'
--- REPLACE WITH YOUR TREE NAME
AND N.TREE_NAME = 'ACCOUNT'
--- REPLACE WITH YOUR TREE EFFECTIVE DATE
AND N.EFFDT = TO_DATE('01011900','MMDDYYYY')
AND N.EFFDT = L.EFFDT
AND N.TREE_NODE_NUM = L.TREE_NODE_NUM
--- REPLACE TABLE WITH LOOKUP TABLE FOR YOUR chartfield
INNER JOIN PS_GL_ACCOUNT_TBL A
--- REPLACE A.ACCOUNT WITH A.your_chartfield
ON A.ACCOUNT >= L.RANGE_FROM
--- REPLACE A.ACCOUNT WITH A.your_chartfield
AND A.ACCOUNT <= L.RANGE_TO
--- REPLACE WITH YOUR SETID
AND L.SETID = 'SHARE'
ORDER BY TREE_NODE_NUM,
TREE_LEVEL_NUM,
TREENODE;
Remember to read the comments on each line to see where and what you need to replace in the query, depending on the tree you are querying and the ChartField stored on the tree.
January 21st, 2010 by Tyson
No comments »
We need to accelerate depreciation on the tax books for thousands of assets in prior years, and retain the cost, and future depreciation.
So for example, we need to take 50% of the asset depreciation in the year it was put in service, i.e. 2007. The asset would depreciation the remaining value through it’s life.
The partial retirement process gets me the results except it retires the asset, and adjusts the cost.
The adjust accumulated depreciation screen seems to get what we need, however, it is creating additional DPR and/or PDP entries (depending on trans_dt) used. these are small amounts but I can’t figure out where it is getting the number. If I knew how it was getting the nubmer I could adjust my plug to get the answers we need.
This is only for tax books. It is related to new repair regulations where a statistical method was applied by auditors, and we need to sync PeopleSoft to match the new values.
Thanks for any advice or detailed explanation on how the adjust accum depreciation screen works.
I may need a little more information before providing proper guidance. Have the thousands of assets had any transactions (transfers, cost adjustments, recategorizations, etc.) performed on them since they were put in service? Unfortunately PeopleSoft won’t allow you to make any adjustments to assets prior to the last possible change made to the asset (e.g. the new transaction date must be greater than or equal to the latest available transaction date associated to the asset).
Since the assets have very likely been touched since their in-service date, my recommendation would lean towards re-converting the tax books for the assets in question. When you perform the re-conversion, you can add the 50% accumulated depreciation as of the asset’s in-service date (which would accomplish the FY2007 example you provided in your question). Then the asset would depreciate as usual for the remainder of its useful life.
Alternatively, here is a quick explanation of the Adjust Accumulated Depreciation page that is delivered:
If the asset has already begun depreciating, use whatever transaction and accounting date needed and enter values for the Depr Accum Adjustment, and, if applicable, the Year to Date Depr Adjust.
This will create an ADD entry for the accumulated depreciation adjustment, which populates the PS_DEPRECIATION table. Then, a RAD (Reserve Adjustment) entry gets created on the PS_OPEN_TRANS table. After you run Depreciation Calculation it should adjust the future depreciation of the asset based on the cost basis, the total accumulated depreciation, and the transaction date used for the adjustment.
As for the DPR and PDP entries that get created, you may want to ensure that the Accounting Date is equal to the Transaction Date used when adjusting the accumulated depreciation (even though accounting entries aren’t being created, as you are only adjusting the tax books). You’ll likely have to open up the periods in question, though, as PeopleSoft may still try to validate that the accounting period is open.
January 19th, 2010 by Tyson
No comments »
Our company is in the process of implementing Expenses 9.0 (we do not have PSoft HR, but do have AP and GL). We have a requirement to allow users to be paid either via check or direct deposit (EFT). I have no problems getting the check portion to work. However, it seems no matter what configuration changes or user changes that I make payment is always being staged as System Check. Can anyone point me in the right direction as to what configuration area I should be looking at?
I am assuming you have your employees set up as vendors in the system. If this is the case, you should be able to modify their vendor record to point to Electronic Funds Transfer rather than Check.
Go to Vendors > Vendor Information > Add/Update > Vendor. Find the employee and click on the Location tab. Click the Payables hyperlink. Expand the Additional Payables Options section. In the Additional Payment Information section, select Specify and then Electronic Funds Transfer.
