Tyson Moore

Consultant, Developer, Volunteer

SQL Developer Tweaks

January 16th, 2010 by Tyson No comments »

I came across Duncan Davies‘s PeopleSoft Tipster blog when searching for ways to customize and optimize SQL Developer to make my job easier. He has some great tips for tweaking Oracle’s free SQL Developer tool to look better and work better for the consultants of the world. Some tips that I’ve started using:

1) Change the Font. Developers like to indent and line up their code to make it more readable. Why deliver the product with a default font that isn’t fixed width, therefore making this harder? The first change I make is to swap the font (Tools > Preferences > Code Editor > Fonts) for Lucida Console, 11pts – although the font size may vary depending upon monitor resolution and the state of your eyesight.

…

3) Syntax Colours. Picking a better colour scheme eases readability as you’ll be able to pick out strings, operators and brackets/braces quicker but the delivered scheme highlights keywords in bold, which throws out the alignment if you’ve pick a fixed-width font. I normally make the following changes (Tools > Preferences > Code Editor > Syntax Colours):

Remove the bold highlight from ‘Default Element Name’ and ‘Default Keyword’.

Change Default Separator to purple

Change Default String to red

Uncheck ‘Enable highlight’ against Current SQL

I also use several of the Toolbar Shortcuts he mentions, such as F9 to execute SQL queries.

Thanks for the great article, Duncan!

Share and Enjoy:

No comments »

Posted in PeopleSoft, SQL

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

January 14th, 2010 by Tyson No comments »

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 by Tyson No comments »

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: