Peoplesoft / Oracle tips: March 2011

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;

Peoplesoft / Oracle tips

Thursday, March 3, 2011

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

Labels