Peoplesoft / Oracle tips: 2011

Friday, November 18, 2011

Find Navigation Path or BreadCrumbs for a Component in Peoplesoft / Oracle

There have been some great blog posts for this type of query. Here are a few that I like

From CompShack - http://www.compshack.com/breadcrumb/breadcrumb-sql-to-find-navigation-path-for-component-pia

From Peoplesoft Tipster - http://peoplesofttipster.com/2007/12/09/portal-navigation/

/* Provide navigation location for component */
SELECT a.portal_name,
       e.portal_label AS parent4_folder,
       d.portal_label AS parent3_folder,
       c.portal_label AS parent2_folder,
       b.portal_label AS parent_folder,
       a.portal_label AS component
FROM   psprsmdefn a
       left join psprsmdefn b
         ON b.portal_name = a.portal_name
            AND b.portal_objname = a.portal_prntobjname
       left join psprsmdefn c
         ON c.portal_name = b.portal_name
            AND c.portal_objname = b.portal_prntobjname
       left join psprsmdefn d
         ON d.portal_name = c.portal_name
            AND d.portal_objname = c.portal_prntobjname
       left join psprsmdefn e
         ON e.portal_name = d.portal_name
            AND e.portal_objname = d.portal_prntobjname
WHERE  a.portal_reftype = 'C'
       -- Parameter to set:  (In this case I used the component - ADM_APPL_MAINTNCE)
       AND a.portal_uri_seg2 = 'ADM_APPL_MAINTNCE'

Alliance 2012

Looking forward to attending and presenting at the HEUG Alliance Conference 2012 in Nashville!

Here is the technical track session I will be presenting with a great colleague Andrew Cesario.

Wed, Mar 21, 2012 (09:15 AM - 10:15 AM)

Technical

30009:Campus Solutions AAWS: A Technical Overview

Thursday, August 11, 2011

Why are my Request Messages in Latin? Using SOAPUI

I have been scratching my head on this one for days and it was a simple setting. I did change the value many times but didn't realize that I needed to reload the WSDL definition for the changes to take effect. :-)

The sample values (in Latin) are created because of the soapUI Preferences.
If you dislike having these sample values or having to adjust them by hand, you can turn them off by ticking off WSDL Settings->Sample Values checkbox. When you add the WSDL again, the new sample requests will have no sample values and just a question mark. "? "

Settings: Make sure this is unchecked.

When you uncheck the setting, you will have just the ? mark.

Tuesday, August 9, 2011

PeopleTools Table Reference

Here is a great Peopletools reference for delivered tools tables.

http://www.go-faster.co.uk/peopletools/#PT

Monday, August 8, 2011

AAWS - Admissions Application Web Services - Info

My Oracle Support KM docs to review:

CS 9.0 Bundle #21 Functional Documentation and Additional Features April 2011 (Doc ID 1314754.1)

Specific files to download:
1. CS_Bundle_21_Manage_Admission_Transactions.pdf (1.85 MB)
2. AAWS Users Guide.pdf (541 KB)
3. AAWS Developers Guide.pdf (1.11 MB)

Within Oracle/Peoplesoft there are 9 different web services provided with AAWS and 2 for Payment Processing.

There are six services for Application Processing:

Create Application - SAD_CREATEAPPL
Get Application - SAD_GETAPPL
Get Application(s) - SAD_GETAPPLS
Get Attachment - SAD_GETATTACH
Save Application - SAD_SAVEAPPL
Submit Application - SAD_SUBMITAPPL

There are three for User Registration:

User Authentication - SCC_USERREG_AUTHENTICATE
User Registration - SCC_USERREG_CREATEACCT
Get a List of Values - SCC_GET_LOV

JDBI - Making Clean APIs without enbedded SQL

I found this article and information interesting. http://jdbi.org/ and http://jdbi.org/getting_jdbi/

Not having to embed SQL directly into Java. By using this, one could create a packaged stored procedure, which would then make a cleaner API.

Friday, August 5, 2011

SQL Formatter

I have used this website for many years now. It is a great site to format SQL and Text. Check it out!

http://www.dpriver.com/pp/sqlformat.htm

Book Review - Oracle Peoplesoft Enterprise Financial Management 9.1 Implementation

I have started to read and review this reference on Oracle Peoplesoft Enterprise Financial Management 9.1 Implementation. The preliminary view thus far is optimistic and it appears to be a great reference for understanding Financial Management. The author provides some outstanding screen shots and functional design items to reference. The book's layout is organized and has great flow between chapters.

The functional and technical items covered are as follows listed by Chapter:

Chapter 1 - PeopleSoft Financials Fundamentals
Chapter 2 - PeopleSoft Security
Chapter 3: PeopleSoft Billing Module
Chapter 4: PeopleSoft Accounts Receivable Module
Chapter 5: PeopleSoft Asset Management Module
Chapter 6: PeopleSoft Accounts Payable Module
Chapter 7: PeopleSoft General Ledger Module
Chapter 8: PeopleSoft Expenses Module
Chapter 9: PeopleSoft Commitment Control

Book Link to Publisher - http://link.packtpub.com/GnLy0X

Friday, June 10, 2011

Campus Solutions / HCM Split documentation - List of KM docs on My Oracle Support

Great listing of Knowledge Management docs to review within My Oracle Support concerning the CS/HCM Split. (Campus Solutions Split from HCM)

http://support.oracle.com

CS/HR Split

Campus Solutions to HCM Integration: A Case Study on Integration Using the Owner Subscriber Option (Doc ID 1297158.1)

Campus Solutions CS - HCM Integration Support Released! (Doc ID 1267773.1)

CS Bundle #19 Functional Documentation for Campus Solutions 9.0 & Feature Pack 4(Doc ID 1259484.1)

Campus Solutions Announces Support and Documentation Availability for the Subscriber Only Model of the CS-HCM Separate Instance Integration (Doc ID 1320568.1)

Campus Solutions Announces Support and Documentation Availability for the Subscriber Only Model of the CS-HCM Separate Instance Integration (Doc ID 1320568.1)

CS/HCM Subscriber Only or Subscriber/Subscriber KM doc - (Doc ID 1412093.1)

Please note that HR core maintenance will still need to be applied to Campus Solutions.

Tuesday, April 5, 2011

How to Connect to a Oracle/Peoplesoft Database using PHP

Entry from within tnsnames.ora file

ORA_SERVICE = (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME=ora_service)))

This is sample script to connect and execute a query:

$db_conn["host"] = "ORA_SERVICE"; # service name in the tnsnames.ora file 
$db_conn["user"] = "myuser"; # username 
$db_conn["pass"] = "mypass"; # password 
$db_conn["library"] = "OCI"; 
 
$connect_id = ocilogon($db_conn["user"], $db_conn["pass"], $db_conn["host"]); 
 
$query = "SELECT * FROM table"; 
$statement = ociparse($connect_id, $query); 
ociexecute($statement); 
 
$result = array(); while(ocifetchinto($statement, $tmp, OCI_ASSOC + OCI_RETURN_NULLS + OCI_RETURN_LOBS)) { 
  array_push($result, $tmp); } 
 
ocifreestatement($statement); 
 
var_dump($result); # result is here

Please also review this connection link for PHP - http://www.php.net/manual/en/function.oci-connect.php

SQL Query to find all Records within a Specific Peoplesoft Component

I found this query on CompShack and wanted to also post on here. I have used the following sample: 'ADM_APPL_MAINTNCE'

This query would be useful when you do a CNTL+J and are wanting to know the table names without going into App Designer.

SELECT DISTINCT (recname)
FROM psrecdefn
WHERE recname IN
(SELECT DISTINCT (recname)
FROM pspnlfield
WHERE pnlname IN
(SELECT DISTINCT (b.pnlname)
FROM pspnlgroup a, pspnlfield b
WHERE ( a.pnlname = b.pnlname OR a.pnlname =b.subpnlname)
AND a.pnlgrpname = 'ADM_APPL_MAINTNCE' -- specify your component name)
AND recname <> ' ')
UNION
SELECT DISTINCT (recname)
FROM pspnlfield
WHERE pnlname IN
(SELECT DISTINCT (b.subpnlname)
FROM pspnlgroup a,pspnlfield b
WHERE (a.pnlname = b.pnlname OR a.pnlname = b.subpnlname )
AND a.pnlgrpname = 'ADM_APPL_MAINTNCE') -- specify your component name)
AND recname <> ' ')
AND rectype in ('0') -- specify record type, default is 0 for table
--AND rectype in ('0', '1', '2') -- specify all types
ORDER BY recname ASC

Thursday, March 3, 2011

Create RSS 2.0 Feed from Application Engine (AE) / Peoplecode for Peoplesoft Job Postings

Scope of the project: Create RSS feed for Peoplesoft Job Postings

1. Create custom view that provides all the long datatypes for the values you want to display in the feed. (These will be different based upon your institution or company)

SELECT A.HRS_JOB_OPENING_ID

, B.HRS_JO_PST_SEQ
, C.HRS_SEQ_NUM
, B.POSTING_TITLE
, C.HRS_JO_PST_OPN_DT "Open Date"
, C.HRS_JO_PST_CLS_DT "Close Date"
, A.HRS_PRM_LOCATION "Location"
, I.DESCRLONG "College/Division"
, P.DESCRLONG "Scope OF Search"
, R.DESCRLONG "Grant Funded Position"
, G.DESCRLONG "Posted Rate OF Pay"
, H.DESCRLONG "Duties
AND Responsibilities"
, N.DESCRLONG "Minimum Qualifications"
, O.DESCRLONG "Desired Qualifications"
, Q.DESCRLONG "Department Statement/Gen Info"
, J.DESCRLONG "Background CHECK Statement"
, K.DESCRLONG "Standard Statement"
, L.DESCRLONG "Close Date"
, M.DESCRLONG "Instructions TO Apply"
FROM PS_HRS_JOB_OPENING A
, PS_HRS_JO_POSTING B
, PS_HRS_JO_PST_DST C
, PS_HRS_SOURCE D LEFT OUTER JOIN PS_HRS_SOURCE_SITE S ON (S.HRS_SOURCE_ID = D.HRS_SOURCE_ID
AND S.EFFDT = D.EFFDT)
, PS_HRS_STS_JO_I E
, PS_HRS_JO_RSEC_VW F
, PS_HRS_JO_PST_DSCR G
, PS_HRS_JO_PST_DSCR H
, PS_HRS_JO_PST_DSCR I
, PS_HRS_JO_PST_DSCR J
, PS_HRS_JO_PST_DSCR K
, PS_HRS_JO_PST_DSCR L
, PS_HRS_JO_PST_DSCR M
, PS_HRS_JO_PST_DSCR N
, PS_HRS_JO_PST_DSCR O
, PS_HRS_JO_PST_DSCR P
, PS_HRS_JO_PST_DSCR Q
, PS_HRS_JO_PST_DSCR R
WHERE A.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = C.HRS_JOB_OPENING_ID
AND B.HRS_JO_PST_SEQ = C.HRS_JO_PST_SEQ
AND C.HRS_JO_PST_TYPE = 'E'
AND (C.HRS_JO_PST_CLS_DT > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
OR C.HRS_JO_PST_CLS_DT IS NULL)
AND C.HRS_JO_PST_OPN_DT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
AND C.HRS_SOURCE_ID = D.HRS_SOURCE_ID
AND D.HRS_SOURCE_TYPE = '40'
AND A.STATUS_CODE = E.STATUS_CODE
AND E.OPEN_STS_IND = 'Y'
AND A.HRS_JOB_OPENING_ID = F.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = G.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = H.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = I.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = J.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = K.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = L.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = M.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = N.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = O.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = P.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = Q.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = R.HRS_JOB_OPENING_ID
AND G.HRS_JO_DESCR_TYP = 'P'
AND H.HRS_JO_DESCR_TYP = 'R'
AND I.HRS_JO_DESCR_TYP = 'S'
AND J.HRS_JO_DESCR_TYP = 'L'
AND K.HRS_JO_DESCR_TYP = 'I'
AND L.HRS_JO_DESCR_TYP = 'E'
AND M.HRS_JO_DESCR_TYP = 'C'
AND N.HRS_JO_DESCR_TYP = 'Q'
AND O.HRS_JO_DESCR_TYP = 'J'
AND P.HRS_JO_DESCR_TYP = 'B'
AND Q.HRS_JO_DESCR_TYP = 'D'
AND R.HRS_JO_DESCR_TYP = 'G'
AND D.EFFDT=(
SELECT MAX(EFFDT)
FROM PS_HRS_SOURCE X
WHERE X.HRS_SOURCE_ID=D.HRS_SOURCE_ID
AND X.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))
AND D.HRS_SOURCE_STATUS = '10'
AND F.ROWSECCLASS = 'HCDPALL'

2. Create custom AE, Component, Page, Record...etc

3. Create step within AE to write file. (Here is a sample from my program)

/* RSS 2.0 Feed Creation for Job Postings - J.McMahon */

Local File &MYFILE_rss;
Local Record &Readrec;
Local SQL &SQL1;
Local string &FileName1, &inFilePath, &outFilePath, &subString;
Local integer &COUNT;
&tab = Char(9);
&nl = Char(13);

&URL_value = "your URL for image";
&Link_value_header = STATE_REC_AET.URL;
&Title_value = STATE_REC_AET.TITLE_MAIN;
&Link_value_detail = STATE_REC_AET.URL_1;

&FileName1 = "C:\temp\JM_rss_sample.rss";
&MYFILE_rss = GetFile(&FileName1, "W", %FilePath_Absolute);

MessageBox(0, "", 0, 0, &FileName1);

&FIRSTROW = "Y";
If &MYFILE_rss.IsOpen Then
&Readrec = CreateRecord(Record.SOME_CUSTOM_VW);
&SQL1 = CreateSQL("%Selectall(:1) ", &Readrec);

While &SQL1.Fetch(&Readrec)

&COUNT = &COUNT + 1;

If &FIRSTROW = "Y" Then
 &XML_Top = "<?xml version=" | """1.0""" | " encoding=" | """utf-8""" | "?>";
 &RSS_Version = "<rss version=" | """2.0""" | ">";
 &Channel_tag = "<channel>";
 &Title = "<title>" | &Title_value | "</title>";
 &Link_rss = "<link>" | &Link_value_header | "</link>";
 &Descr = "<description>This Feed will contain <Your Company> job postings.</description>";
 &Lang = "<language>en-us</language>";
 &Copyright = "<copyright>2010-2015</copyright>";

 &MYFILE_rss.WriteLine(&XML_Top);
 &MYFILE_rss.WriteLine(&RSS_Version);
 &MYFILE_rss.WriteLine(&Channel_tag);
 &MYFILE_rss.WriteLine(&Title);
 &MYFILE_rss.WriteLine(&Link_rss);
 &MYFILE_rss.WriteLine(&Descr);
 &MYFILE_rss.WriteLine(&Lang);
 &MYFILE_rss.WriteLine(&Copyright);

 &MYFILE_rss.WriteLine("<image>");
 &MYFILE_rss.WriteLine("<title>" | &Title_value | "</title>");
 &MYFILE_rss.WriteLine("<link>" | &Link_value_header | "</link>");
 &MYFILE_rss.WriteLine("<url>" | &URL_value | "</url>");
 &MYFILE_rss.WriteLine("</image>");

 &FIRSTROW = "N";
 End-If;

/* MessageBox(0, "", 0, 0, &Readrec.HRS_JOB_OPENING_ID.Value);
MessageBox(0, "", 0, 0, &Readrec.POSTING_TITLE.Value);
MessageBox(0, "", 0, 0, &Readrec.DESCRLONG.Value);
*/
&College = &Readrec.DESCRLONG.Value;
&College = RTrim(LTrim(&College));

&Scope = &Readrec.ZZZ_DESCR_LONG.Value;
&Scope = RTrim(LTrim(&Scope));

&Grant = &Readrec.ZZZ_DESCR_LONG2.Value;
&Grant = RTrim(LTrim(&Grant));

&Posted = &Readrec.ZZZ_DESCR_LONG3.Value;
&Posted = RTrim(LTrim(&Posted));

&Duties = &Readrec.ZZZ_DESCR_LONG4.Value;
&Duties = RTrim(LTrim(&Duties));

&MinQ = &Readrec.ZZZ_DESCR_LONG5.Value;
&MinQ = RTrim(LTrim(&MinQ));

&Des = &Readrec.ZZZ_DESCR_LONG6.Value;
&Des = RTrim(LTrim(&Des));

&Dep_statement = &Readrec.ZZZ_DESCR_LONG7.Value;
&Dep_statement = RTrim(LTrim(&Dep_statement));

&Background = &Readrec.ZZZ_DESCR_LONG8.Value;
&Background = RTrim(LTrim(&Background));

&Standard = &Readrec.ZZZ_DESCR_LONG9.Value;
&Standard = RTrim(LTrim(&Standard));

&Close_date = &Readrec.ZZZ_DESCR_LONG10.Value;
&Close_date = RTrim(LTrim(&Close_date));

&Apply = &Readrec.ZZZ_DESCR_LONG11.Value;
&Apply = RTrim(LTrim(&Apply));

&ITEM = "<title>" | &Readrec.HRS_JOB_OPENING_ID.Value | &tab | &Readrec.POSTING_TITLE.Value | "</title>";
 &PUBDATE = "<pubDate>" | %Datetime | "</pubDate>";
 &LINK_DETAIL = "<link>" | &Link_value_detail | &Readrec.HRS_JOB_OPENING_ID.Value | "</link>";
 &COMMENTS = "<comments></comments>";
 rem &LINK_DETAIL = "<link>" | &Link_value_header | "</link>";

 &CATEGORY = "<category>Careers</category>";

 /* Had Values Concatenated, but was having issues with the formatting - writting each line */

 &DESCR_DETAILS = "<description>" | "<![CDATA[" | "You can login and apply using: Username: GUEST Password: password" | "College/Division";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &College | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Scope of Search";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Scope | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Grant Funded Position";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Grant | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Posted Rate of Pay";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Posted | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Duties and Responsibilities";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Duties | Char(10) | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Minimum Qualifications";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &MinQ | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Desired Qualifications";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Des | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Department Statement/Gen Info";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Dep_statement | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Background Check Statement";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Background | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Standard Statement";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Standard | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Close Date";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Close_date | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "Instructions to Apply";
 &DESCR_DETAILS = &DESCR_DETAILS | "" | &Apply | "";
 &DESCR_DETAILS = &DESCR_DETAILS | "]]>" | "</description>";



 /* Need to remove the '&' special characture from the long datatypes, will not work within RSS feed - J.McMahon */
 &DESCR_DETAILS = Substitute(&DESCR_DETAILS, "&", "&");



 &MYFILE_rss.WriteLine("<item>");
 &MYFILE_rss.WriteLine(&ITEM);
 &MYFILE_rss.WriteLine(&PUBDATE);
 &MYFILE_rss.WriteLine(&LINK_DETAIL);
 rem &MYFILE_rss.WriteLine(&COMMENTS);
 &MYFILE_rss.WriteLine(&CATEGORY);
 &MYFILE_rss.WriteLine(&DESCR_DETAILS);

 &MYFILE_rss.WriteLine("</item>");


 &COUNT = &COUNT + 1;

 End-While;

 &MYFILE_rss.WriteLine("</channel>");
 &MYFILE_rss.WriteLine("</rss>");

 REM End-If;
 REM &COUNT1 = &COUNT | " ";
 REM &COUNT1 = Substring(&COUNT1, 1, 10);

End-If;

4. This code I had placed within a Run control record Row_Init pcode.

(Keep in mind I have updated these names, example: "zzz")

If %Mode = "A" Then
 zzz_RC.OPRID = %OperatorId;
 zzz_RC.ECONEFILEPATH = &outFilePath | "HR/";
 zzz_RC.FILE_NAME = "rss_jobs.xml";
 zzz_RC.TITLE_MAIN = "Job Postings";
 zzz_RC.URL = "Place your URL here for Careers";

 SQLExec("select lower(DBNAME) from PSDBOWNER where OWNERID='SYSADM'", &InstanceName);
 &Env = &InstanceName;

 If &Env = "prd" Then
 &Link_value_detail = "https://yourvaluegoeshere%3e/psp/" | &Env | "/EMPLOYEE/HRMS/c/HRS_HRAM.HRS_CE.GBL?Page=HRS_CE_JOB_DTL&JobOpeningId" | "=";
 Else
 &Link_value_detail = "https://yourvaluegoeshere/psp/" | &Env | "/EMPLOYEE/HRMS/c/HRS_HRAM.HRS_CE.GBL?Page=HRS_CE_JOB_DTL&JobOpeningId" | "=";
 End-If;
 zzz_RC.URL_1 = &Link_value_detail;

End-If;