Tyson Moore

Consultant, Developer, Volunteer

Archive for the ‘PeopleSoft’ category

Selecting the translate values from PSXLATITEM table by joining to another table

January 14th, 2010

Our current Version: PS8.9; People Tools: 8.47How can we select the translate values from PSXLATITEM table by joining to another table?I want the description(XLATLONGNAME) in the field value rather th
How can I get the translate values description selected in the query?
Record used :
PSXLATITEM.
HRS_JO_RQMT_I
fiels selected:
SCHEDULED_TYPE
SHIFT
Translate values of Schduled_type:
P
F
O
Long names:
par time
Full time
Optional
I want the descriptions (long names) of the tanslate values should be displayed in the output rather than
F, P, O
Any suggestions ?/ Thanks.

If you are doing this via the online Query tool, you can change the Field settings (on the Field tab) to display the Long description rather than the ID for the fields you are selecting.

If you are just writing SQL, I would suggest using XLATTABLE_VW instead of PSXLATITEM to obtain the long description of the field(s) you are selecting. In my queries, I typically join to that view with the following criteria:

SELECT D.XLATLONGNAME AS SCHEDULE_TYPE
FROM PS_HRS_JO_RQMT_I A, XLATTABLE_VW B
WHERE B.FIELDNAME = 'SCHEDULED_TYPE'
AND A.SCHEDULED_TYPE = B.FIELDVALUE AND B.EFFDT
(SELECT MAX(B_ED.EFFDT) FROM XLATTABLE_VW B_ED
WHERE B.FIELDNAME = B_ED.FIELDNAME
AND B.FIELDVALUE = B_ED.FIELDVALUE
AND B_ED.EFFDT <= SYSDATE);

Share and Enjoy:

No comments »

Posted in IT Toolbox, PeopleSoft, Query

Tags: peoplesoft query xlat

Per Diem Rate Information Source

January 13th, 2010

The PeopleSoft delivered method of loading location amounts uses the CONUS/OCONUS files. Where do you get the files? The GSA website only provides an excel format for domestic rates. For alaska and hawaii, there is a link on the GSA’s website to the department of defense for a file that is in a different format than the domestic rates provided by the GSA. For foreign rates, there is yet another link from the GSA’s website to The Office of Allowances’ foreign per diem rates.

I noticed that if I just download everything through the department of defense, the rate for the domestic locations are not the same as what is provided for through the GSA’s website.
Furthermore, how do you handle the changing formats for rate information through the various sources for that information?

The per diem information seems to exist in very dynamic formats, and it makes coding for importing the information very difficult.

You should be able to save the Excel files that are produced from the GSA website (CONUS and OCONUS) using the file formats (columns) as depicted on the following website:

http://www.defensetravel.dod.mil/pdc-archive/PD-RATES/Ascii-Delimited/fstruct.html

Save the Excel file as a semi-colon delimited text file. To do this, you’ll need to change your current default delimiter by following these steps (assuming you’re using Microsoft Windows and Microsoft Excel):

1. In Microsoft Windows, click the Start button, and then click Control Panel.
2. Open the Regional and Language Options dialog box.
3. Do one of the following:
* In Windows Vista, click the Formats tab, and then click Customize this format.
* In Windows XP, click the Regional Options tab, and then click Customize.
4. Type a new separator in the List separator box.
5. Click OK twice.

Then you just need to get the files with the correct column order and save it as a CSV. Change the file extension to TXT and you’ll be all set.

Share and Enjoy:

No comments »

Posted in IT Toolbox, PeopleSoft, Time & Expense

Tags: conus oconus peoplesoft time & expense per diem

how to retire an asset that is in Transfer status in Peoplesoft asset management

January 13th, 2010

We have an asset in Transfer status with some balance on it. How do I retire this asset?
Suggestions please

I’d be happy to help. I need some additional information, though. When you say that the asset is in Transfer status but has a balance on it, do you mean that someone performed a partial transfer on the asset, thus leaving the balance? Or was the transfer somehow corrupted and accidentally left a balance in the old cost center/BU?

I believe it’s a partial transfer and thus leaving balance.

If the partial transfer was within the same business unit, PeopleSoft will want to fully retire the asset, or partially retire both “parts” of the asset. This is because this is still technically one “asset”, just split between cost centers. The only way I would know how to fully “retire” part of the transfer is to go in and perform a cost adjustment on the line you wish to “retire.” Adjust it all the way down to zero and create the retirement entries manually in the GL.

If the partial transfer was an interunit transfer, you should just be able to go to the Retire page and fully retire the asset in question.

…

A great response by kettbs on IT Toolbox to this question:

If you did a partial transfer of cost from one asset to another both assets should be in a status of “In Service”. If you have an asset that has cost and/or depreciation and is in the status of “Transfered”, there is an issue with the asset. Normally the issue maybe be caused by an adjustment to the cost of the asset such as adding additional cost. Review the cost tables and all the chartfields including the Chartfield Sequence Number Field and Activity Switch field. Determine if the chartfield information is the same for all entries in the cost and depreciation tables. (this includes, dept, projects, etc.).

Once you have determine which fields the cost is in, add a new asset for .01 with the same chartfield information that the original assets has. Run depreciation and then transfer the .01 asset to the original asset. Because you are transferring cost back into the asset with the same chartfields this process should change the status of the asset to “in Service” and you should be able to retire it (after running depreciation calculation.) This does not require any SQL’s it is all manual.