Wednesday, 5 October 2016

Excel Macro for SaveAs Filename

Haven't posted much about Excel but this one has been the most common request from business users.

This is a macro to override the "Save As" filename.

Wednesday, 2 March 2016

REGEXP_LIKE To Find Unwanted Characters

This SQL will search for filenames that contain characters other than

  1. a-z
  2. A-Z
  3. 0-9
  4. underscore
  5. parenthesis
  6. dash
  7. period
  8. square brackets

Friday, 5 February 2016

Find Temp Tables used by Application Engine

I've been running into situations where our application engines are not clearing the temp tables even after process instance has been deleted.

Also, multiple occurrences of the process halting for no reason.

Oracle's workaround is to clear the temp tables and restart the process.

But which temp tables do you clear?

Try this script.

Monday, 18 January 2016

Easy Import of Config File

I want to import PeopleSoft configuration files without going through the UI.

Let's use a .bat file.

Friday, 15 January 2016

Remove Dead Worklist Entries

I want to remove worklist entries which have dead links.

But they only gave me a screenshot of the worklist page.

  1. Get OPRID of recipient
  2. Get OPRID of sender. If you look at the screenshot, it's the guy under FROM column.
  3. Get EOAWTHREAD_ID. If worklist is delivered, look at the 2nd value of the Link column.
  4. Execute SQL below.

Wednesday, 4 March 2015

Export All Results from ORACLE SQL DEVELOPER

I want to export all my query results from Oracle SQL Developer

I have two (2) options.

  1. Use /*csv*/
  2. CTRL + End

Oracle SQL Developer
Version 2.1.1.64
Build MAIN-64.65

SELECT /*csv*/ *
FROM PS_JOB

Or just click on query results and click Ctrl + End.

Thursday, 26 February 2015

Use SUBSELECT in PS Query Expression

I encountered this error when I used a subselect in my PSQuery expression.

FROM clauses are not allowed in expressions (50,536)
SQL error. Stmt #: 5682 Error Position: 107 Return: 936 - ORA-00936: missing expression
A SQL error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=936, Message=ORA-00936: missing expression (50,380)

Here's the workaround:

Tuesday, 20 January 2015

SQL To Identify Actual Customized PeopleCode

For some reason, the demo environment is not up to date with all the patches and bundles applied to production.

I need to do a full compare of all the PeopleCode in the system and I need to identify which ones are actual customizations vs patches.

  1. Create the compare report.
  2. Run this SQL.

Friday, 16 January 2015

Top 10 Awesome Meta-SQL

Here is a list of my top 10 awesome meta-sql that makes my lazy life easier.
%EffdtCheck
%InsertSelect
%InsertValues
%Join
%KeyEqual
%SQL
%SelectAll
%SelectDistinct
%SelectByKey
%SelectByKeyEffDt

Now for some examples...

Tuesday, 13 January 2015

Create Tooltips + Hyperlink To Another Component

I need two things today.

  1. Create a link for every row in the grid. Each link will transfer the page to a new component and use the search keys automatically.
  2. Create a hover or mouseover label for the links.

Step by step guide below.