Peoplesoft / Oracle tips

Campus Solutions - Row level Security Components

2012-01-19T11:51:00.002-07:00

Here is a sample SQL that will provide the components for CS Row Level security. You can then reference the records within App Desginer and create *.dms scripts for exporting the user security.

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,
       a.portal_uri_seg2 AS component_dbname
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'
       AND e.portal_label LIKE 'Set Up SACR'
       AND d.portal_label LIKE 'Security';

Please also review this posting from PeoplesoftTipster.com

http://peoplesofttipster.com/2007/04/12/moving-peoplesoft-config-between-environments/#more-15

There are a number of tools you could use to achieve this, be it data mover, raw SQL or even an SQR or App Engine. All of these would need to be custom written. An alternative solution is to use the Manage Configuration Set functionality. It’s a delivered component (at least on more recent versions of the Application) that lets you choose the modules you wish to export config for, and it builds the DMS files (Data Mover Scripts) for you.

Sample Navigation:

http://<name:port>/psp/<environment>/EMPLOYEE/HRMS/c/EOLT_IMPLEMENTATION.EOLT_CONFIG_OPT.GBL

or go to PeopleTools > Portal > Structure and Content, drill down into Enterprise Components and edit Manage Implementations to not hide it from portal navigation (checkbox on first page).

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

2011-11-18T15:08:00.000-07:00

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

2011-11-18T09:29:00.000-07:00

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

Why are my Request Messages in Latin? Using SOAPUI

2011-08-11T11:01:00.001-07:00

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.

PeopleTools Table Reference

2011-08-09T01:05:00.000-07:00

Here is a great Peopletools reference for delivered tools tables.

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

AAWS - Admissions Application Web Services - Info

2011-08-08T16:01:00.000-07:00

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

2011-08-08T15:03:00.000-07:00

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.

SQL Formatter

2011-08-05T14:35:00.000-07:00

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

2011-08-05T09:16:00.001-07:00

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

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

2011-06-10T10:49:00.000-07:00

Great listing of Knowledge Management docs to review within My Oracle Support concerning the CS/HCM Split.

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)

How to Connect to a Oracle/Peoplesoft Database using PHP

2011-04-05T01:13:00.000-07:00

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

2011-04-05T00:40:00.000-07:00

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

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

2011-03-03T13:30:00.000-07:00

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;

PTF - PeopleSoft Test Framework

2010-11-11T01:57:00.000-07:00

Some great new functionality has been delivered with Peoplesoft Test Framework. Check out Peoplebooks

You are now able to automate tests within Peoplesoft and provide test cases for certain scenarios within the tool.

You can record and playback these tests within your environment.

1. Download or apply current tools release to be 8.51.xx
2. Install Peoplesoft Test Framework application. Current release is 8.51.05
3. Create Execution Options within the tools to reference your URL and database.

I would also recommend to toggle Skip PageSave to Yes within the "Options" tab, if you do not want to commit or save anything to your database.

More to come...

Take care
Jeromy

Applying Peoplesoft Maintenance

2010-09-14T14:57:00.003-07:00

Over the years there have been some talks on best practices for applying Peoplesoft Maintenance. (Bundles, Tax Updates, REGS..etc)

Here is my take:

First, one must choose a maintenance path. (Bundles or Maintenance Packs) and stay the course.

Maintenance Packs:

Maintenance Packs are nice for a new implementation or upgrade to get you current without applying individual Bundles
Maintenance Packs due tend to take some time to come out since it is a combination of Bundles for all modules.

Bundles:

Bundles will provide you the latest and greatest code line (Keeping current with Peoplesoft/Oracle)
Bundles are nice for North American customers given all the regulatory releases (REGS) and updates

The best way to search for Bundles within My Oracle Support:

Within "Updates and Fixes"
Apply to Release

Product Line = Campus Solutions
Product = Student Administration
Release = 9 (or 8.9 if you have not upgraded yet)
Bundles check box toggled

This will provide you a listing of all the current bundles.

Change Assistant is a great tool and application used to apply bundles and maintenance, if you are still applying bundles manually, now is the time to configure CA. It makes applying maintenance much easier.

Take care

Campus Solutions (CS) Bundle 18/25 sysaudit issues

2010-08-10T15:44:00.003-07:00

Within My Oracle Support - MOS

If you are seeing the following - IBRK-13, SYSLANG-11, IBRK-05, IBRK-19 Errors when Applying CS 9 Bundle 18 & CS 8.9 Bundle 25

Please review KM Doc ID 1167635.1

Hosted Peoplebooks

2010-07-20T16:04:00.003-07:00

Oracle PeopleSoft Enterprise Hosted PeopleBooks

Steps to Turn ON Logging for XMLP Reports for Debugging

2009-10-29T09:28:00.002-07:00

Steps to Turn ON Logging for XMLP Reports for Debugging.

Create a file named xdodebug.cfg and place it under [PS_HOME]\JRE\Lib] folder.The file should include the following line:

Under Windows
LogLevel=STATEMENT

LogDir=c:\temp

Under Unix
The file Should look like this (point to an appropriate temporary directory under the Unix box)
LogLevel=STATEMENT

LogDir=/ds3/ps/dssgrp/t849xr21/appserv/

Remove xdodebug.cfg when finished replicating the issue.

The logging is specifically useful for troubleshooting the Template(RTF/PDF) or Data File(XML file) specific issues. The generated XML file is the actual data file which is run from the MS Word Design Helper Preview mode to narrow down the issue.

Online Checks - using the GetNextNumberWithGapsCommit() function

2009-10-11T23:44:00.002-07:00

Here is a good sample for using the GetNextNumberWithGapsCommit() function for online paycheck numbers.

First on a RowInit() lets gray the field or make it display only on the page.

If %Component = Component.PAY_OL_SHEET Then
Gray(PAY_LINE.PAYCHECK_NBR);
End-If;

Next, lets get the Max() paycheck number from pay_check currently within Production and insert that number into a custom record.

SQLExec("SELECT MAX(A.PAYCHECK_NBR) FROM PS_PAY_CHECK A WHERE (A.FORM_ID = '******' OR A.FORM_ID = ' ')", &MAX_PAYCHECK_NBR);

INSERT INTO PS_ASU_HR_TRAN_NBR
(ASU_HR_APP_CODE, TRANSACTION_NBR)
VALUES
(
'OLCK' -- A place holder value if you plan to use other types of transactions
,'123456') -- This will be the max number currently within ps_pay_check

SavePreChange code

If %Component = Component.PAY_OL_SHEET Then
If None(PAY_LINE.PAYCHECK_NBR) Or
%Mode = "A" Then
&next_pay_check_nbr = GetNextNumberWithGapsCommit(ASU_HR_TRAN_NBR.TRANSACTION_NBR, 999999999999999, 1, "WHERE ASU_HR_APP_CODE = 'OLCK'");
PAY_LINE.PAYCHECK_NBR.Value = &next_pay_check_nbr;
End-If;
End-If;

PT_BRANDING - App Package pcode changes

2009-01-05T20:10:00.002-07:00

Here is a great mod to change the greeting within your PIA environment.

PT_BRANDING.BrandingBase.OnExecute (Application Package Peoplecode)

Within this method:

method GetIScriptHPDefaultHdrHTML
/+ Returns String +/;

Local string &ActiveTab, &PersHTML, &TabHTML, &cookieJSURL, &refreshHPJSURL, &Today, &Greeting, &homepageJS, &EntHeaderHTML;

Local ApiObject &Portal;

REM Get the active tab;
&ActiveTab = %Request.GetParameter("tab");

&Portal = PortalOpen();
%This.GetHPTabHTML(&Portal, &ActiveTab, &PersHTML, &TabHTML);
/* Start - ASU_UT0100 - 11/2007 - J.McMahon - Adding user and environment to branding */
/* &Greeting = &Portal.Homepage.Greeting; */
Local string &Current_date;
SQLExec("Select to_char(SYSDATE,'MM/DD/YYYY') from DUAL", &Current_date);

&Greeting = %UserId | " on " | %DbName | " " | &Current_date;
/* ASU_UT0100 - End */
&Portal.Close();

&cookieJSURL = %Response.GetJavaScriptURL(HTML.PORTAL_COOKIE);
&refreshHPJSURL = %Response.GetJavaScriptURL(HTML.PORTAL_REFRESHPAGE);
&homepageJS = GetHTMLText(HTML.PORTAL_HP_JS_INCLUDE, &cookieJSURL, &refreshHPJSURL);

&Today = "";

&EntHeaderHTML = %This.GetUniHeaderHTML(&PersHTML, &Today, &Greeting, &homepageJS, &TabHTML);

Return &EntHeaderHTML;
end-method;

Also, change within this method: GetUniHeaderHTML

/* Start - ASU_UT0100 - 11/2007 - J.McMahon - Adding user and environment to branding */
/* &Greeting = &Portal.Homepage.Greeting; */
Local string &Current_date;
SQLExec("Select to_char(SYSDATE,'MM/DD/YYYY') from DUAL", &Current_date);

&Greeting = %UserId | " on " | %DbName | " " | &Current_date;
/* ASU_UT0100 - End */

ERMS CRM - Closing a case via a email

2008-12-11T23:14:00.002-07:00

Here is a great sample of using ERMS within CRM to close a case via a email.

The first thing you will need to do is have your exchange admin setup a email account to send to. (CRMClose is an example alias)

One you have that setup, you will need to setup this account within ERMS.

Here is a query to view all email transaction coming in:

SELECT M.MCF_EMAIL_ID
, M.MCF_EMAIL_FROM
, M.MCF_EMAIL_SENDER
, M.MCF_UID
, M.MCF_WL_SUBJECT
, M.MCF_DTTM_SENT
, M.MCF_ATTACH_LIST
, M.MCF_ATTACH_SIZES
, M.MCF_IS_ATT_URL
, M.MCF_ATT_URL
, COALESCE(P.MCF_EMAIL_TEXT
, M.MCF_EMAIL_TEXT)
, M.MCF_CONTENT_TYPE
, IE.MAILBOX_ID
, IE.BUSINESS_UNIT
FROM PS_MCFEM_MAIL_MAIN M
, PS_MCFEM_MAIL_PART P
, PS_RB_IN_EMAIL IE
WHERE M.MCF_EMAIL_ID = P.MCF_EMAIL_ID (+)
AND M.MCF_EMAIL_ID = IE.MCF_EMAIL_ID (+)
AND M.MCF_EMAIL_STATUS = 0
AND P.MCF_EMAIL_PARTNO (+) = 1
AND IE.STRUCTURED_SW = 'N'
AND (m.MCF_EMAIL_FROM <> 'support@asu.edu'
AND m.MCF_EMAIL_FROM <> 'postmaster@exchange.asu.edu')

Send an email to your newly created account and verify that your subject and email text is within the records after the ERMS process is ran.

Using this format for this example:

Sent to: CRMClose
Subject: 343514#
Text: Please close case. Resolved JM

Here is the peoplecode to make it all happen within a App Engine process. Make sure to setup a state record to store the values from the query above.

/*** ASU Custom Mod - 12/2008 - J.McMahon ***/
/*** Create new logic to handle close case and update case note ***/
Declare Function GetFilePathServer PeopleCode ASU_FILEPATH_WK.ECFILELISTPATH FieldFormula;

Local ApiObject &Session;
Local ApiObject &asuCI;
Local boolean &genUse;

Local File &fileLog;
Local ApiObject &oSession, &oRcCaseCi, &oRcSolutionCI;
Local ApiObject &oRbqAdptrTmpv2Collection, &oRbqAdptrTmpv2;
Local ApiObject &oRbEmailIbVwCollection, &oRbEmailIbVw;
Local ApiObject &oRcInterestPrtCollection, &oRcInterestPrt;
Local ApiObject &oRfEntlDispCollection, &oRfEntlDisp;
Local ApiObject &oRfShowEntlCollection, &oRfShowEntl;
Local ApiObject &oRfShowPriceCollection, &oRfShowPrice;
Local ApiObject &oRcLinkCatVwCollection, &oRcLinkCatVw;
Local ApiObject &oRcCaseDisputeCollection, &oRcCaseDispute;
Local ApiObject &oRcCaseComplntCollection, &oRcCaseComplnt;
Local ApiObject &oRcResolutionCollection, &oRcResolution;
Local ApiObject &oDerivedUrIdxCollection, &oDerivedUrIdx;
Local ApiObject &oRbRidxWrk1Collection, &oRbRidxWrk1;
Local ApiObject &oRbRidxWrk2Collection, &oRbRidxWrk2;
Local ApiObject &oRbRidxWrk3Collection, &oRbRidxWrk3;
Local ApiObject &oRbRiObjVwCollection, &oRbRiObjVw;
Local ApiObject &oRcCaseNoteCollection, &oRcCaseNote;
Local ApiObject &oRcCaseAttachCollection, &oRcCaseAttach;
Local ApiObject &oRcAssocTableCollection, &oRcAssocTable;
Local ApiObject &oRcCaseSrchVwCollection, &oRcCaseSrchVw;
Local ApiObject &oEoecDsRuleVwCollection, &oEoecDsRuleVw;
Local ApiObject &oRcRelationVwCollection, &oRcRelationVw;
Local ApiObject &oRcActionHistCollection, &oRcActionHist;
Local ApiObject &oRcCaseBiCollection, &oRcCaseBi;
Local ApiObject &oRcCaseBiDetCollection, &oRcCaseBiDet;
Local ApiObject &oRcAuditVwCollection, &oRcAuditVw;
Local ApiObject &oRcTimeBiVw2Collection, &oRcTimeBiVw2;
Local ApiObject &oRbScrollL1n12Collection, &oRbScrollL1n12;
Local ApiObject &oRbScrollLvl2Collection, &oRbScrollLvl2;
Local ApiObject &oRbEmTransVwCollection, &oRbEmTransVw;
Local ApiObject &oRbWfPersidVwCollection, &oRbWfPersidVw;
Local ApiObject &oRbWfDeloptW1Collection, &oRbWfDeloptW1;
Local ApiObject &oRbWfDeloptW2Collection, &oRbWfDeloptW2;
Local ApiObject &oRfSoProdDispCollection, &oRfSoProdDisp;
Local ApiObject &oRfShowPriVwCollection, &oRfShowPriVw;
Local ApiObject &oRfAsgnPgdOutCollection, &oRfAsgnPgdOut;
Local ApiObject &oRfAsgnWkOutCollection, &oRfAsgnWkOut;
Local ApiObject &oRfAsgnWkdOutCollection, &oRfAsgnWkdOut;
Local ApiObject &oRcCaseWo2Collection, &oRcCaseWo2;
Local ApiObject &oRcResolutVwCollection, &oRcResolutVw;

Function errorHandler()
Local ApiObject &oPSMessageCollection, &oPSMessage;
Local number &i;
Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;

&oPSMessageCollection = &oSession.PSMessages;
For &i = 1 To &oPSMessageCollection.Count
&oPSMessage = &oPSMessageCollection.Item(&i);
&sErrMsgSetNum = &oPSMessage.MessageSetNumber;
&sErrMsgNum = &oPSMessage.MessageNumber;
&sErrMsgText = &oPSMessage.Text;
&fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
End-For;
rem ***** Delete the Messages from the collection *****;
&oPSMessageCollection.DeleteAll();
End-Function;

/*
Open Case Component Interface
*/
&Session = %Session;
&asuCI = &Session.GetCompIntfc(CompIntfc.RC_CASE_CI);

If None(&asuCI) Then
Error MsgGet(17831, 9114, "Message not found: Error calling GetCompIntfc");
End-If;

&genUse = False;

&subjLen = Find("#", ASU_MCFEM_AET.MCF_WL_SUBJECT);
&parsedCaseId = Substring(ASU_MCFEM_AET.MCF_WL_SUBJECT, 1, (&subjLen - 1));

If IsDigits(&parsedCaseId) Then

/*** ASU_CMCC0061 - Start Mod - J.McMahon ***/
Evaluate ASU_MCFEM_AET.MAILBOX_ID
When = "CRMClose"
/*** Close Case logic here ***/
If None(&parsedCaseId) Then
&text = ASU_MCFEM_AET.MCF_EMAIL_FROM | "," | ASU_MCFEM_AET.MCF_WL_SUBJECT | "," | ASU_MCFEM_AET.MCF_EMAIL_TEXT;
&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Email case id lookup error " | "Parsed Case Id = " | &parsedCaseId;
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

Else

try
rem ***** Set the Log File *****;

GetFilePathServer(&outFilePath, &inFilePath, %DbName);

&Process_Instance_val = ASU_MCFEM_AET.PROCESS_INSTANCE;

&fileLog = GetFile(&outFilePath | "ASU_CASE_GEN" | "_" | &Process_Instance_val | "_" | ".log", "W", %FilePath_Absolute);
&fileLog.WriteLine("Begin");
&fileLog.WriteLine("Process_Instance = : " | &Process_Instance_val);

rem ***** Get current PeopleSoft Session *****;
&oSession = %Session;

rem ***** Set the PeopleSoft Session Error Message Mode *****;
rem ***** 0 - None *****;
rem ***** 1 - PSMessage Collection only (default) *****;
rem ***** 2 - Message Box only *****;
rem ***** 3 - Both collection and message box *****;
&oSession.PSMessagesMode = 3;

rem ***** Get the Component Interface *****;
&oRcCaseCi = &oSession.GetCompIntfc(CompIntfc.RC_CASE_CI);
If &oRcCaseCi = Null Then
errorHandler();
throw CreateException(0, 0, "GetCompIntfc failed");
End-If;

&oRcSolutionCI = &oSession.GetCompIntfc(CompIntfc.RC_SOLUTION_CI);
If &oRcSolutionCI = Null Then
errorHandler();
throw CreateException(0, 0, "GetCompIntfc failed");
End-If;

rem ***** Set the Component Interface Mode *****;
&oRcCaseCi.InteractiveMode = True;
&oRcCaseCi.GetHistoryItems = True;
&oRcCaseCi.EditHistoryItems = True;

rem ***** Set the Component Interface Mode *****;
&oRcSolutionCI.InteractiveMode = True;
&oRcSolutionCI.GetHistoryItems = True;
&oRcSolutionCI.EditHistoryItems = True;

rem ***** Set Component Interface Get/Create Keys *****;
&oRcCaseCi.CASE_ID = &parsedCaseId;
&oRcCaseCi.DISP_TMPL_ID = "RC_SUPPORT";

&fileLog.WriteLine("Get and Create Keys " | "Case ID: " | &oRcCaseCi.CASE_ID);
&fileLog.WriteLine("Display Template " | &oRcCaseCi.DISP_TMPL_ID);

&oRcCaseCi.Get();

If &oRcCaseCi.RC_STATUS <> "RESOL" Then

&caseType = "CLOSED";

Local boolean &bRtn = &oRcCaseCi.SetCaseStatusToResolved();

&fileLog.WriteLine("SetCaseStatusToResolved Method Display " | &bRtn);

rem ***** Set Component Interface Get/Create Keys *****;
&oRcSolutionCI.SETID = "ASU00";
&oRcSolutionCI.SOLUTION_ID = 0;

&oRcSolutionCI.Create();

&oRcSolutionCI.RC_SOLUTION_TYPE = "ADHC";
&oRcSolutionCI.SOLN_STATUS = "ACTV";
&oRcSolutionCI.RC_SOLN_VISIBILITY = "A";
&oRcSolutionCI.RC_SUMMARY = Substring(ASU_MCFEM_AET.MCF_EMAIL_TEXT, 1, 50);
&oRcSolutionCI.SOLUTION_DESCR = ASU_MCFEM_AET.MCF_EMAIL_TEXT;
&oRcSolutionCI.EXPIRY_IND = "N";
&oRcSolutionCI.USAGE_COUNT = 0;
&oRcSolutionCI.LAST_USAGE_DTTM = %Datetime;
&oRcSolutionCI.USER_UPDATE_DTTM = %Datetime;
rem &oRcSolutionCI.UPDATED_BY_USER.Value = %OperatorId;
&oRcSolutionCI.ROW_ADDED_DTTM = %Datetime;
rem &oRcSolutionCI.ROW_ADDED_OPRID.Value = %OperatorId;
&oRcSolutionCI.ROW_LASTMANT_DTTM = %Datetime;
&oRcSolutionCI.ROW_LASTMANT_OPRID = %OperatorId;

rem ***** Execute Save *****;
If Not &oRcSolutionCI.Save() Then;
errorHandler();
throw CreateException(0, 0, "Save failed");
End-If;

rem ***** Set/Get RC_RESOLUTION Collection Field Properties -- Parent: PS_ROOT Collection *****;
&oRcResolutionCollection = &oRcCaseCi.RC_RESOLUTION;

Local integer &i1468;
For &i1468 = 1 To &oRcResolutionCollection.Count;
&oRcResolution = &oRcResolutionCollection.Item(&i1468);
&fileLog.WriteLine("&oRcResolution.RSLN_SUMMARY = " | &oRcResolution.RSLN_SUMMARY);
rem &oRcResolution.RSLN_SUMMARY = [*];
&oRcResolution.RSLN_NOTES = ASU_MCFEM_AET.MCF_EMAIL_TEXT;
&fileLog.WriteLine("&oRcResolution.RSLN_NOTES = " | &oRcResolution.RSLN_NOTES);
&fileLog.WriteLine("&oRcResolution.SELECT_FLAG = " | &oRcResolution.SELECT_FLAG);
rem &oRcResolution.SELECT_FLAG = [*];
&fileLog.WriteLine("&oRcResolution.LONG_URL = " | &oRcResolution.LONG_URL);
rem &oRcResolution.LONG_URL = [*];
&oRcResolution.SOLUTION_ID = &oRcSolutionCI.SOLUTION_ID;
&fileLog.WriteLine("&oRcResolution.SOLUTION_ID = " | &oRcResolution.SOLUTION_ID);
&oRcResolution.RSLN_STATE = "1";
&fileLog.WriteLine("&oRcResolution.RSLN_STATE = " | &oRcResolution.RSLN_STATE);

End-For;

/*&strAdHocSolution = &ciCase.GetAdHocSolutionID();
&fileLog.WriteLine("AdHocSolutionID " | &strAdHocSolution);
Local boolean &bSolRtn = &oRcCaseCi.AttemptSolution(&strAdHocSolution, "Y", ASU_MCFEM_AET.MCF_EMAIL_TEXT, "1", "3");

&fileLog.WriteLine("AttemptSOlution Method Return " | &bSolRtn);

If &bSolRtn = True Then
&oRcCaseCi.Save();
End-If; */
rem ***** Execute Save *****;

If Not &oRcCaseCi.Save() Then;
errorHandler();
throw CreateException(0, 0, "Save failed");
End-If;
¬eSubj = "Case Closed by email from ";

If &bRtn = True Then
&oRcCaseCi.Save();
End-If;

rem ***** Execute CloseCase *****;
&Rtn_val = &oRcCaseCi.CloseCase();
If &Rtn_val = True Then

&text = "A case has been closed for you via CRMClose email account. If you have any further questions or concerns, please don't hesitate to contact us." | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "Please use this link to check the status of your case:" | "
";
&text = &text | "http://www.asu.edu/go/support/managecase/" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "ASU Help Desk" | "
";
&text = &text | "Arizona State University" | "
";
&text = &text | "helpdesk@asu.edu" | "
";
&text = &text | "(480) 965-6500" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "**This is an auto-generated message****Please do not reply to this email**" | "
";

&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Support case#" | &oRcCaseCi.CASE_ID | " has been closed";
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

MessageBox(0, "", 0, 0, " case id from save = " | &oRcCaseCi.CASE_ID);

&RET_Close_Note = &oRcCaseCi.Addnote(&oRcCaseCi.CASE_ID, ¬eSubj, ASU_MCFEM_AET.MCF_EMAIL_FROM | ": " | ASU_MCFEM_AET.MCF_EMAIL_TEXT, "I", "COMNT", "E");
MessageBox(0, "", 0, 0, " adding a note = " | &RET_Close_Note);
If &RET_Close_Note Then
&oRcCaseCi.Save();
&fileLog.WriteLine("Saved Closed Case: " | &RET_Close_Note | &Rtn_val);
End-If;

End-If;
Else /* Case Status is already Resolved */
&text = "This case already has a Resolution and a RESOL status associated with. If you have any further questions or concerns, please don't hesitate to contact us." | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "Please use this link to check the status of your case:" | "
";
&text = &text | "http://www.asu.edu/go/support/managecase/" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "ASU Help Desk" | "
";
&text = &text | "Arizona State University" | "
";
&text = &text | "helpdesk@asu.edu" | "
";
&text = &text | "(480) 965-6500" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "**This is an auto-generated message****Please do not reply to this email**" | "
";

&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Support case#" | &oRcCaseCi.CASE_ID | " has not been updated, because the status is already Resolved";
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

MessageBox(0, "", 0, 0, " case id from save = " | &oRcCaseCi.CASE_ID);
End-If;
catch Exception &ex
rem Handle the exception;
&fileLog.WriteLine(&ex.ToString());
end-try;
&fileLog.WriteLine("End");
&fileLog.Close();
SQLExec(SQL.ASU_UPD_INB_EML_STATUS, ASU_MCFEM_AET.MCF_EMAIL_ID);

End-If;
/*
after createing the base ase then update the inbound erms system table so we do not process all the emails again
*/
Break;
When = "CRMAddNote"
/*** Update case note logic here ***/
If None(&parsedCaseId) Then
&text = ASU_MCFEM_AET.MCF_EMAIL_FROM | "," | ASU_MCFEM_AET.MCF_WL_SUBJECT | "," | ASU_MCFEM_AET.MCF_EMAIL_TEXT;
&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Email case id lookup error " | "Parsed Case Id = " | &parsedCaseId;
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

Else

rem ***** Set Component Interface Get/Create Keys *****;
&asuCI.CASE_ID = &parsedCaseId;
&asuCI.DISP_TMPL_ID = "RC_SUPPORT";

&asuCI.Get();

&caseType = "NOTE";
¬eSubj = "Note added by email from ";

&RET_Note = &asuCI.Addnote(&asuCI.CASE_ID, ¬eSubj, ASU_MCFEM_AET.MCF_EMAIL_FROM | ": " | ASU_MCFEM_AET.MCF_EMAIL_TEXT, "I", "COMNT", "E");
MessageBox(0, "", 0, 0, " adding a note = " | &RET);
If &RET_Note Then
&asuCI.Save();
End-If;

If ASU_MCFEM_AET.MCF_ATTACH_LIST <> " " Then

Evaluate %DbName
When "ASUCMDEV"
&attachPath = "Insert your path";
Break;
When "ASUCMTST"
&attachPath = "Insert your path";
Break;
When "ASUCMPRD"
&attachPath = "Insert your path";
Break;
When-Other
&attachPath = "Insert your path";
Break;
End-Evaluate;

&strAttachGetSql = CreateSQL("select mcf_filename,MCF_ATT_URL from PS_MCFEM_MAIL_PART where mcf_email_id = :1 AND MCF_EMAIL_PARTNO > 2", ASU_MCFEM_AET.MCF_EMAIL_ID);

SQLExec("select count(*) from PS_MCFEM_MAIL_PART where mcf_email_id = :1 AND MCF_EMAIL_PARTNO > 2", ASU_MCFEM_AET.MCF_EMAIL_ID, &ncount);
MessageBox(0, "", 0, 0, " count of attachments = " | &ncount);

&recCaseAttach = CreateRecord(Record.RC_CASE_ATTACH);
&k = 1;
While &strAttachGetSql.Fetch(&AttachName, &attachURL)
&strattachPath = "";
&len = Find("?", &attachURL);
&urlAttach = Substring(&attachURL, 1, (&len - 1));

&attachment_name = &AttachName;
&attachment_name = Left(&attachment_name, 64);
&attachment_name = Substitute(&attachment_name, " ", "_");
&attachment_name = Substitute(&attachment_name, ";", "_");
&attachment_name = Substitute(&attachment_name, "+", "_");
&attachment_name = Substitute(&attachment_name, "%", "_");
&attachment_name = Substitute(&attachment_name, "&", "_");
&attachment_name = Substitute(&attachment_name, "'", "_");
&attachment_name = Substitute(&attachment_name, "!", "_");
&attachment_name = Substitute(&attachment_name, "@", "_");
&attachment_name = Substitute(&attachment_name, "#", "_");
&attachment_name = Substitute(&attachment_name, "$", "_");

&attachment_sysname = &asuCI.CASE_ID | &attachment_name;
&strattachPath = &attachPath | &urlAttach;

MessageBox(0, "", 0, 0, " attach path = " | &strattachPath);

MessageBox(0, "", 0, 0, " file name = " | &attachment_name | " sys file name = " | &attachment_sysname);

&RETCODE = PutAttachment(URL.RC_ATTACHMENTS, &attachment_sysname, &strattachPath);
MessageBox(0, "", 0, 0, " return code = " | &RETCODE);
If &RETCODE = %Attachment_Success Then
MessageBox(0, "", 0, 0, " entered create record");
&recCaseAttach = CreateRecord(Record.RC_CASE_ATTACH);
&recCaseAttach.Setdefault();

&recCaseAttach.CASE_ID.value = &asuCI.CASE_ID;
&recCaseAttach.BUSINESS_UNIT.value = ASU_MCFEM_AET.BUSINESS_UNIT;
&recCaseAttach.NOTE_SEQ_NBR.value = 1;
&recCaseAttach.ATTACH_SEQ_NBR.value = &k;
&recCaseAttach.ATTACHUSERFILE.value = &attachment_name;
&recCaseAttach.ATTACHSYSFILENAME.value = &attachment_sysname;
&recCaseAttach.RC_VISIBILITY.value = "I";
&recCaseAttach.DESCRIPTION.value = "From email";

MessageBox(0, "", 0, 0, " info for record before save is case Id " | &asuCI.CASE_ID | " business unit " | ASU_MCFEM_AET.BUSINESS_UNIT | " note seq " | &recCaseAttach.NOTE_SEQ_NBR.value | " attach seq number " | &recCaseAttach.ATTACH_SEQ_NBR.value);

&recCaseAttach.Insert();
CommitWork();

&k = &k + 1;
End-If;
End-While;
End-If;

SQLExec(SQL.ASU_UPD_INB_EML_STATUS, ASU_MCFEM_AET.MCF_EMAIL_ID);

End-If;
Break;
When-Other /*** other than email addresses evaluated ***/

&caseType = "NEW";

/*
Try to match email address to current consumer, contact in CRM
*/
SQLExec(SQL.ASU_GET_BOID_EML, ASU_MCFEM_AET.MCF_EMAIL_FROM, &n_bo_id);
/* now lets validate and fetch the mail box id and business unit just in case there was a failure in the select
SQL */

SQLExec("select mailbox_id,business_unit from PS_RB_IN_EMAIL where mcf_email_id = :1", ASU_MCFEM_AET.MCF_EMAIL_ID, ASU_MCFEM_AET.MAILBOX_ID, ASU_MCFEM_AET.BUSINESS_UNIT);

/*
Validate business unit from email box. if no valid business unit default one

*/
If None(ASU_MCFEM_AET.BUSINESS_UNIT) Then
ASU_MCFEM_AET.BUSINESS_UNIT = "UTO00";
End-If;
/*
Grab default worklist and detemine default provider group from worklist
*/
If All(ASU_MCFEM_AET.MAILBOX_ID) Then
&sqlStrPG = "select PROVIDER_GRP_ID from ps_RF_PROVIDER_GRP where rb_wf_grp_name = (select DEFAULT_WORKLIST from ps_RB_MAILBOX_DEFN where mailbox_id = :1)";
SQLExec(SQL.ASU_GET_PG_EML, ASU_MCFEM_AET.MAILBOX_ID, &strDflPrvGrpId);
Else
Evaluate ASU_MCFEM_AET.BUSINESS_UNIT
When "UTO00"
&strDflPrvGrpId = "UTOHD";
Break;
When "ASU00"
When "ENG00"
When "HRA00"
&strDflPrvGrpId = "HRESC";
Break;
When "UTO02"
&strDflPrvGrpId = "UTOHD";
Break;
When "WPC00"
&strDflPrvGrpId = "WPCTAC";
Break;
End-Evaluate;

End-If;

If None(&strDflPrvGrpId) Then
&strDflPrvGrpId = "UTOHD";
MessageBox(0, "", 0, 0, " using the default provider group ");
End-If;

If &strDflPrvGrpId = "UTO-ATS" Then
&strStatus = "OPNEM";
Else
&strStatus = "OPEN";
End-If;

MessageBox(0, "", 0, 0, " bo id = " | &n_bo_id | " email addr = " | ASU_MCFEM_AET.MCF_EMAIL_FROM);
/*
if we did not retreive a consumer or contact bo id then we get the default user
*/
Evaluate &n_bo_id
When 0
&sqlGetAnon = "Select BO_ID from ps_bo_name where first_name like 'General' and last_name like 'Customer' and rownum = 1";
SQLExec(SQL.ASU_GET_GEN_BOID, &n_bo_id);
If None(&n_bo_id) Then
&n_bo_id = 0;
&genUse = False;
Else
&genUse = True;
End-If;
End-Evaluate;
/*
finally ready to create the case if we have a contact, consumer bo id
*/

If None(&n_bo_id) Then
<* do nothing *>
MessageBox(0, "", 0, 0, " No customer/consumer account could be found for email address = " | ASU_MCFEM_AET.MCF_EMAIL_FROM);
Else
try
&asuCI.InteractiveMode = True;
&asuCI.CASE_ID = 999999999999999;
&asuCI.DISP_TMPL_ID = "RC_SUPPORT";
&asuCI.Create();

&asuCI.BUSINESS_UNIT = ASU_MCFEM_AET.BUSINESS_UNIT;
&asuCI.RC_VERTICAL = "SW";
&asuCI.MARKET = "GBL";
&asuCI.BO_ID_CUST = &n_bo_id;
&asuCI.ROLE_TYPE_ID_CUST = 9;
&asuCI.RC_STATUS = &strStatus;
&asuCI.RC_SOURCE = "EMAIL";
&asuCI.PROVIDER_GRP_ID = &strDflPrvGrpId;

&asuCI.RC_SUMMARY = ASU_MCFEM_AET.MCF_WL_SUBJECT;
&asuCI.RC_DESCRLONG = ASU_MCFEM_AET.MCF_EMAIL_FROM | ": " | ASU_MCFEM_AET.MCF_EMAIL_TEXT;

If Not (&asuCI.Save()) Then
MessageBox(0, "", 0, 0, " error saving CI = " | &asuCI);
&asuCI.Close();
Else
/*
after createing the base ase then update the inbound erms system table so we do not process all the emails again
*/
SQLExec(SQL.ASU_UPD_INB_EML_STATUS, ASU_MCFEM_AET.MCF_EMAIL_ID);

If &genUse = True And
&asuCI.RC_STATUS = "OPEN" Then
&text = "A case has been opened for you at the Arizona State University Help Desk and a technician will be in contact with you. If you have any further questions or concerns, please don't hesitate to contact us." | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "Please use this link to check the status of your case:" | "
";
&text = &text | "http://www.asu.edu/go/support/managecase/" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "ASU Help Desk" | "
";
&text = &text | "Arizona State University" | "
";
&text = &text | "helpdesk@asu.edu" | "
";
&text = &text | "(480) 965-6500" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "**This is an auto-generated message****Please do not reply to this email**" | "
";

&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Support case#" | &asuCI.CASE_ID | " has been created";
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;
End-If;

MessageBox(0, "", 0, 0, " case id from save = " | &asuCI.CASE_ID);
/*
time to add a note on the case, this will be the email body default preceeded by the from email address for reference
*/

&RET_New = &asuCI.Addnote(&asuCI.CASE_ID, ASU_MCFEM_AET.MCF_WL_SUBJECT, ASU_MCFEM_AET.MCF_EMAIL_FROM | ": " | ASU_MCFEM_AET.MCF_EMAIL_TEXT, "I", "COMNT", "E");
MessageBox(0, "", 0, 0, " adding a note = " | &RET);
If &RET_New Then
&asuCI.Save();
End-If;
/*
for the time being we are looking at attachments so we create attachment lists here. we may comment this out depending
on requirements
*/
If ASU_MCFEM_AET.MCF_ATTACH_LIST <> " " Then

Evaluate %DbName
When "ASUCMDEV"
&attachPath = "Insert your path";
Break;
When "ASUCMTST"
&attachPath = "Insert your path";
Break;
When "ASUCMPRD"
&attachPath = "Insert your path";
Break;
When-Other
&attachPath = "Insert your path";
Break;
End-Evaluate;

&strAttachGetSql = CreateSQL("select mcf_filename,MCF_ATT_URL from PS_MCFEM_MAIL_PART where mcf_email_id = :1 AND MCF_EMAIL_PARTNO > 2", ASU_MCFEM_AET.MCF_EMAIL_ID);

SQLExec("select count(*) from PS_MCFEM_MAIL_PART where mcf_email_id = :1 AND MCF_EMAIL_PARTNO > 2", ASU_MCFEM_AET.MCF_EMAIL_ID, &ncount);
MessageBox(0, "", 0, 0, " count of attachments = " | &ncount);

&recCaseAttach = CreateRecord(Record.RC_CASE_ATTACH);
&k = 1;
While &strAttachGetSql.Fetch(&AttachName, &attachURL)
&strattachPath = "";
&len = Find("?", &attachURL);
&urlAttach = Substring(&attachURL, 1, (&len - 1));

&attachment_name = &AttachName;
&attachment_name = Left(&attachment_name, 64);
&attachment_name = Substitute(&attachment_name, " ", "_");
&attachment_name = Substitute(&attachment_name, ";", "_");
&attachment_name = Substitute(&attachment_name, "+", "_");
&attachment_name = Substitute(&attachment_name, "%", "_");
&attachment_name = Substitute(&attachment_name, "&", "_");
&attachment_name = Substitute(&attachment_name, "'", "_");
&attachment_name = Substitute(&attachment_name, "!", "_");
&attachment_name = Substitute(&attachment_name, "@", "_");
&attachment_name = Substitute(&attachment_name, "#", "_");
&attachment_name = Substitute(&attachment_name, "$", "_");

&attachment_sysname = &asuCI.CASE_ID | &attachment_name;
&strattachPath = &attachPath | &urlAttach;

MessageBox(0, "", 0, 0, " attach path = " | &strattachPath);

MessageBox(0, "", 0, 0, " file name = " | &attachment_name | " sys file name = " | &attachment_sysname);

&RETCODE = PutAttachment(URL.RC_ATTACHMENTS, &attachment_sysname, &strattachPath);
MessageBox(0, "", 0, 0, " return code = " | &RETCODE);
If &RETCODE = %Attachment_Success Then
MessageBox(0, "", 0, 0, " entered create record");
&recCaseAttach = CreateRecord(Record.RC_CASE_ATTACH);
&recCaseAttach.Setdefault();

&recCaseAttach.CASE_ID.value = &asuCI.CASE_ID;
&recCaseAttach.BUSINESS_UNIT.value = ASU_MCFEM_AET.BUSINESS_UNIT;
&recCaseAttach.NOTE_SEQ_NBR.value = 1;
&recCaseAttach.ATTACH_SEQ_NBR.value = &k;
&recCaseAttach.ATTACHUSERFILE.value = &attachment_name;
&recCaseAttach.ATTACHSYSFILENAME.value = &attachment_sysname;
&recCaseAttach.RC_VISIBILITY.value = "I";
&recCaseAttach.DESCRIPTION.value = "From email";

MessageBox(0, "", 0, 0, " info for record before save is case Id " | &asuCI.CASE_ID | " business unit " | ASU_MCFEM_AET.BUSINESS_UNIT | " note seq " | &recCaseAttach.NOTE_SEQ_NBR.value | " attach seq number " | &recCaseAttach.ATTACH_SEQ_NBR.value);

&recCaseAttach.Insert();
CommitWork();

&k = &k + 1;
End-If;
End-While;
End-If;
End-If;

catch Exception &asuCIException;

end-try;
End-If;

MessageBox(0, "", 0, 0, " mail id = " | ASU_MCFEM_AET.MCF_EMAIL_ID | " and subject line = " | ASU_MCFEM_AET.MCF_WL_SUBJECT | " email from = " | ASU_MCFEM_AET.MCF_EMAIL_FROM);

End-Evaluate;

Else

&text = "Please make sure you have you case id correct. If you have any further questions or concerns, please don't hesitate to contact us." | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "Please use this link to check the status of your case:" | "
";
&text = &text | "http://www.asu.edu/go/support/managecase/" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "ASU Help Desk" | "
";
&text = &text | "Arizona State University" | "
";
&text = &text | "helpdesk@asu.edu" | "
";
&text = &text | "(480) 965-6500" | "
";
&text = &text | "
";
&text = &text | "
";
&text = &text | "**This is an auto-generated message****Please do not reply to this email**" | "
";

&MAIL_FLAGS = 0;
&MAIL_TO = ASU_MCFEM_AET.MCF_EMAIL_FROM;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Support case#" | &parsedCaseId | " is not correct and does not contain numeric digits (Ex. 343567#) ";
&MAIL_TEXT = &text;
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_SENDER = "support@asu.edu";
&delim = ";";
&Content_Type = "Content-type: text/html; charset=utf8";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER, &delim, &Content_Type);

If Not (&RET = 0) Then
MessageBox(0, "", 0, 0, "Return status from mail = " | &RET);
End-If;

End-If;

Peoplecode samples - Hide a field on Page.Activate peoplecode

2008-12-04T08:20:00.003-07:00

I was working on a project today that required some graying of fields and hiding of fields on a page. Here are some samples:

How to gray a field? (Gray() and UnGray())

This is at scroll level 1.
Gray(Record.DERIVED_W3EB, CurrentRowNumber(), DERIVED_W3EB.LINK_PB);

How to hide a field? (Using .visible)

Local Rowset &LEVEL0, &level1, &level2;
Local Row &LEVEL0_ROW, &LEVEL1_ROW, &LEVEL2_ROW;
&LEVEL0 = GetLevel0();
&LEVEL0_ROW = &LEVEL0(1);
&level1 = &LEVEL0_ROW.GetRowset(Scroll.EMPLOYEE_REVIEW);
For &I = 1 To &level1.ActiveRowCount
&LEVEL1_ROW = &level1(&I);
&level2 = &LEVEL1_ROW.GetRowset(Scroll.REVIEW_REVIEWER);
For &J = 1 To &level2.ActiveRowCount
&LEVEL2_ROW = &level2(&J);
&Record = &LEVEL2_ROW.REVIEW_REVIEWER;
&Record.COMMENTS.visible = False;
End-For;
End-For;

Declare Function CheckMerchant PeopleCode FUNCLIB_W3EB.MERCHANTID FieldFormula;
Local Rowset &RSLocal1, &RSLevel1, &RSLEVEL1B, &RSL1BENEF;
Local Field &LinkLabelField;

Function Hide_Waived_Term_Rows(&RSLevel1 As Rowset)

For &I = &RSLevel1.ActiveRowCount To 1 Step - 1
rem Get Coverage Status;
&Coverage = &RSLevel1(&I).W3EB_SAVPLAN_VW.COVERAGE_ELECT.Value;
&TempRow = &RSLevel1(&I);
If &Coverage = "W" Or
&Coverage = "T" Or
(&RSLevel1.ActiveRowCount = 1 And
None(&RSLevel1(1).W3EB_SAVPLAN_VW.BENEFIT_PLAN.Value)) Then
&RSLevel1(&I).Visible = False;
DERIVED_W3EB.DESCR100.Visible = True;
DERIVED_W3EB.DESCR100.Value = MsgGetText(3001, 177, "Message Not Found");
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
DERIVED_W3EB.GRPB_LABEL2.Visible = False;
DERIVED_W3EB.TEXT_LABEL2.Visible = False;
Else
&ret = False;
&rsPlanType = GetLevel0()(1).W3EB_SAVSUMSRCH.PLAN_TYPE.Value;
&RSLocal1 = GetLevel0()(1).GetRowset(Scroll.W3EB_SAVPLAN_VW);
&rsBenefitPlan = &RSLocal1(1).W3EB_SAVPLAN_VW.BENEFIT_PLAN.Value;
&LinkLabelField = &RSLocal1(1).DERIVED_W3EB.NAME1;

/* Check for a Knowledge provider merchant and then hide or unhide the Authoria subpanel */
&RSLocal1(1).DERIVED_W3EB.LINK2_PB.Label = &LinkLabelField.Value;
&ret = CheckMerchant(&rsPlanType, &rsBenefitPlan, "KNOWLEDGE", &METHOD, &MERCHANT_ID, &PSPOLICYURL, &PSPROVIDERSURL);

If &ret = False Then

If Len(&PSPOLICYURL) <> 0 Then
&RSLocal1(1).DERIVED_W3EB.LINK2_PB.Visible = True;
&RSLocal1(1).DERIVED_W3EB.LINK2_PB.Label = &LinkLabelField.Value;
&LinkLabelField.Visible = False;
DERIVED_W3EB.URL_ID = &PSPOLICYURL;
Else
&RSLocal1(1).DERIVED_W3EB.LINK2_PB.Visible = False;
&LinkLabelField.Visible = True;
End-If;
Else
REM AUTH_WRK.PLAN_TYPE = W3EB_GLOBAL_WRK.PLAN_TYPE;
DERIVED_W3EB.MERCHANTID = &MERCHANT_ID;
&LinkLabelField.Visible = False;
End-If;
DERIVED_W3EB.DESCR100.Visible = False;
DERIVED_W3EB.GRPB_LABEL.Visible = True;
DERIVED_W3EB.GRPB_LABEL1.Visible = True;
DERIVED_W3EB.GRPB_LABEL2.Visible = True;
DERIVED_W3EB.TEXT_LABEL2.Visible = True;
&RSL1BENEF = GetLevel0()(1).GetRowset(Scroll.W3EB_SVBENEF_VW);
&VALUE = &RSL1BENEF(1).W3EB_SVBENEF_VW.BENEF_PCT.Value;
&ARC = &RSL1BENEF.ActiveRowCount;
If &ARC <= 1 And
None(&VALUE) Then
DERIVED_W3EB.TEXT_NO_DATA.Visible = False;
DERIVED_W3EB.ERROR_TEXT.Value = MsgGetExplainText(3001, 58, "Message_not Found");
/********** Begin Resolution 666486 *********************/
Else
DERIVED_W3EB.TEXT_NO_DATA.Visible = True;
/*********** End Resolution 666486 *********************/
End-If;
End-If;
End-For;
End-Function;

Function Hide_Waived_Term_Rows2(&RSLevel1 As Rowset)

Evaluate %Page
When Page.W3EB_DISAADD_MAIN
SQLExec("SELECT %DATEOUT(A.EFFDT), A.COVERAGE_ELECT FROM PS_DISABILITY_BEN A WHERE A.EMPLID = :1 AND A.EMPL_RCD = :2 AND A.PLAN_TYPE = :3 AND A.COVERAGE_BEGIN_DT = (SELECT MAX(X.COVERAGE_BEGIN_DT) FROM PS_DISABILITY_BEN X WHERE X.EMPLID = A.EMPLID AND X.EMPL_RCD = A.EMPL_RCD AND X.PLAN_TYPE = A.PLAN_TYPE AND X.COVERAGE_BEGIN_DT <= %DATEIN(:4)) AND A.EFFDT = (SELECT MAX(Z.EFFDT) FROM PS_DISABILITY_BEN Z WHERE Z.EMPLID = A.EMPLID AND Z.EMPL_RCD = A.EMPL_RCD AND Z.PLAN_TYPE = A.PLAN_TYPE AND Z.COVERAGE_BEGIN_DT = A.COVERAGE_BEGIN_DT)", W3EB_DIS_SRCH.EMPLID, W3EB_DIS_SRCH.EMPL_RCD, W3EB_DIS_SRCH.PLAN_TYPE, DERIVED_W3EB.ASOFDATE, &EFFDT, &COVERAGE_ELECT);

If &COVERAGE_ELECT = "W" Or
&COVERAGE_ELECT = "T" Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
End-If;
&VALUE = &RSLevel1(1).W3EB_DEPMAIN_VW.EFFDT.Value;
&I = &RSLevel1.ActiveRowCount;

If &I <= 1 And
None(&VALUE) Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
End-If;

When Page.W3EB_LIFEADD_MAIN

SQLExec("SELECT %DATEOUT(A.EFFDT), A.COVERAGE_ELECT FROM PS_LIFE_ADD_BEN A WHERE A.EMPLID = :1 AND A.EMPL_RCD = :2 AND A.PLAN_TYPE = :3 AND A.COVERAGE_BEGIN_DT = (SELECT MAX(X.COVERAGE_BEGIN_DT) FROM PS_LIFE_ADD_BEN X WHERE X.EMPLID = A.EMPLID AND X.EMPL_RCD = A.EMPL_RCD AND X.PLAN_TYPE = A.PLAN_TYPE AND X.COVERAGE_BEGIN_DT <= %DATEIN(:4)) AND A.EFFDT = (SELECT MAX(Z.EFFDT) FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = A.EMPLID AND Z.EMPL_RCD = A.EMPL_RCD AND Z.PLAN_TYPE = A.PLAN_TYPE AND Z.COVERAGE_BEGIN_DT = A.COVERAGE_BEGIN_DT)", W3EB_INS_SRCH.EMPLID, W3EB_INS_SRCH.EMPL_RCD, W3EB_INS_SRCH.PLAN_TYPE, DERIVED_W3EB.ASOFDATE, &EFFDT, &COVERAGE_ELECT);

If &COVERAGE_ELECT = "W" Or
&COVERAGE_ELECT = "T" Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
DERIVED_W3EB.GRPB_LABEL2.Visible = False;
End-If;
&VALUE = &RSLevel1(1).W3EB_BENLIFE_VW.EFFDT.Value;
&I = &RSLevel1.ActiveRowCount;
If &I <= 1 And
None(&VALUE) Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
DERIVED_W3EB.GRPB_LABEL1.Visible = False;
DERIVED_W3EB.GRPB_LABEL2.Visible = False;
End-If;
&RSLEVEL1B = GetLevel0()(1).GetRowset(Scroll.W3EB_PRIMBEN_VW);
If (&RSLEVEL1B.ActiveRowCount <= 1 And
None(&RSLEVEL1B(1).DERIVED_W3EB.COVERAGE_LVL2.Value)) Then
DERIVED_W3EB.GRPB_LABEL3.Visible = False;
DERIVED_W3EB.DESCRLONG.Visible = True;
Evaluate W3EB_INS_SRCH.PLAN_TYPE.Value
When 24
When 25
If All(&RSLEVEL1B(1).W3EB_PRIMBEN_VW.DEPENDENT_BENEF.Value) Then
DERIVED_W3EB.GRPB_LABEL3.Visible = True;
DERIVED_W3EB.DESCRLONG.Visible = True;
DERIVED_W3EB.TRANSFER_PB.Visible = False;
Else

/********* BEGIN RESOLUTION 305641 *********/
DERIVED_W3EB.DESCRLONG.Value = MsgGetText(3001, 260, "MESSAGE NOT FOUND");
/********* END RESOLUTION 305641 *********/
End-If;
When-Other
DERIVED_W3EB.DESCRLONG.Value = MsgGetExplainText(3001, 58, "Message Not Found");

End-Evaluate;
Else

DERIVED_W3EB.GRPB_LABEL3.Visible = True;
Evaluate W3EB_INS_SRCH.PLAN_TYPE.Value
When 24
When 25
DERIVED_W3EB.DESCRLONG.Visible = True;
DERIVED_W3EB.TRANSFER_PB.Visible = False;
When-Other
DERIVED_W3EB.DESCRLONG.Visible = True;
rem DERIVED_W3EB.TRANSFER_PB.Visible = True; /* the visibility is determined in Fillscroll()*/
End-Evaluate;
End-If;

Evaluate W3EB_INS_SRCH.PLAN_TYPE.Value
When 20
When 21
When 22
When 27
For &I = &RSLEVEL1B.ActiveRowCount To 1 Step - 1
If (&RSLEVEL1B.GetRow(&I).GetRecord(Record.W3EB_PRIMBEN_VW).GetField(Field.BENEF_PCT).Value = 0 And
&RSLEVEL1B.GetRow(&I).GetRecord(Record.W3EB_PRIMBEN_VW).GetField(Field.FLAT_AMOUNT).Value = 0) Then
HideRow(Record.W3EB_PRIMBEN_VW, &I);
End-If;
End-For;
End-Evaluate;

When Page.W3EB_LIFINSALLBYNM

/* The following code is used to check to see if no rows were returned after the Fill_View_Scroll Function was executed if no rows exsist, then all fields are hidden and a message is displayed */

&VALUE = &RSLevel1(1).W3EB_BENSUM_VW.EFFDT.Value;
&I = &RSLevel1.ActiveRowCount;
If &I <= 1 And /* a scroll with no 0 rows really contains 1 row with values for the keys */
None(&VALUE) Then
UnHide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = False;
Else
Hide(DERIVED_W3EB.TEXT_LABEL2);
DERIVED_W3EB.GRPB_LABEL.Visible = True;
End-If;

/* creates select from the Life_add_ben table for that emplid */

&SQL = CreateSQL("SELECT A.EMPL_RCD, A.PLAN_TYPE, %DATEOUT(A.EFFDT), A.COVERAGE_ELECT FROM PS_LIFE_ADD_BEN A WHERE A.EMPLID = :1", W3EB_EMPL_SRCH.EMPLID);

/* executes SQL */
While &SQL.Fetch(&EMPL_RCD, &PLAN_TYPE, &EFFDT, &COVERAGE_ELECT);

/* checks for waived or terminated elections and hides the appropriate rows*/
If (&COVERAGE_ELECT = "W" Or
&COVERAGE_ELECT = "T") Then
For &I = &RSLevel1.ActiveRowCount To 1 Step - 1
If (&RSLevel1(&I).W3EB_BENSUM_VW.EMPL_RCD.Value = &EMPL_RCD And
&RSLevel1(&I).W3EB_BENSUM_VW.PLAN_TYPE.Value = &PLAN_TYPE And
(&RSLevel1(&I).W3EB_BENSUM_VW.EFFDT.Value < &EFFDT And
&EFFDT <= DERIVED_W3EB.ASOFDATE)) Then
&RSLevel1(&I).Visible = False;
End-If;
End-For;
End-If;
End-While;
End-Evaluate;
End-Function;

XMLP from Field Change and Email *.rtf

2008-11-07T14:48:00.003-07:00

Here is a great sample that will invoke a XMLP and email the results to a email address within a page. We used this to send out Offer letters to faculty. My good colleague Roger and I worked on this one. Enjoy.

/*ASU_HRM0058 9/1/2008 rdavies3
Limited code to custom test page*/

/*** ASU_HRM0058 9/30/2008 J.McMahon ***/
/*** Added XMLP rowset logic, and email logic ***/

import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
import PSXP_ENGINE:*;
import PT_MCF_MAIL:*;

Declare Function GetFilePathServer PeopleCode ASU_FILEPATH_WK.ECFILELISTPATH FieldFormula;

/*Create an email object by setting individual parameters*/
Local PT_MCF_MAIL:MCFOutboundEmail &eMail = create PT_MCF_MAIL:MCFOutboundEmail();
Local Rowset &ASU_NOA_Letter_Rowset, &ASU_NOA_Empl_Rowset;
Local Row &person_row;
Local Record &person_rec, &derived_pb;

/*** Fetch Value from page ***/
&Email_to_from_page = FetchValue(Record.ASU_NOA_EE_HIST, 1, ASU_NOA_EE_HIST.EMAILID);
&Fiscal_Year = FetchValue(Record.ASU_NOA_EE_HIST, 1, ASU_NOA_EE_HIST.FISCAL_YEAR);
&Emplid = FetchValue(Record.ASU_NOA_EE_HIST, 1, ASU_NOA_EE_HIST.EMPLID);
&Empl_Rcd = FetchValue(Record.ASU_NOA_EE_HIST, 1, ASU_NOA_EE_HIST.EMPL_RCD);

/*ASU_HRM0058 9/1/2008 rdavies3
NOA save and process code*/
&ASU_NOA_Empl_Rowset = GetLevel0()(1).GetRowset(Scroll.ASU_NOA_EE_HIST);
&derived_pb = GetRecord(Record.ASU_DERIVED_HR);
&person_row = &ASU_NOA_Empl_Rowset(1);
&person_rec = &person_row.GetRecord(Record.ASU_NOA_EE_HIST);
&ASU_NOA_Letter_Rowset = &person_row.GetRowset(Scroll.ASU_NOA_LTRHIST);
&person_rec.LASTUPDOPRID.Value = %UserId;
&person_rec.LAST_ACTIVITY_TIME.Value = %Datetime;

If &person_rec.EMPLID.Value = %EmployeeId Then
&person_rec.COMPLETED.Value = "Y";
&person_rec.APPROVER_OPRID.Value = %UserId;
&person_rec.APPROVEDTTM.Value = %Datetime;
&derived_pb.ASU_HR_PB_TEST_EMA.Label = "Reprint and Email";
DoSave();
CommitWork();
/* do reprint*/
Else
DoSave();
CommitWork();
End-If;

GetFilePathServer(&outFilePath, &inFilePath, %DbName);

&sRptDefn = "ASUHRNOA";
&sTemplateId = "ASUHRNOA_1";
&sLangCode = "";
&dtAsOfDate = %Date;
&sOutputFmt = "PDF";
&RptOutputDir = &outFilePath | "HR/";

/*Set-Up Report*/
&ReportDef = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptDefn);
&ReportDef.Get();
&ReportDef.SetRuntimeDataXMLFile(&sOutputFile);

&ReportDef.OutDestination = &RptOutputDir;

/*Create Rowset*/
&rs = &ASU_NOA_Empl_Rowset;

/*Fill Rowset*/
rem &rs.FILL("WHERE FILL.FISCAL_YEAR = :1 and FILL.EMPLID = :2 and FILL.EMPL_RCD = :3", &Fiscal_Year, &Emplid, &Empl_Rcd);

/*Create Schema*/
&rds = create PSXP_XMLGEN:RowSetDS(); /*package method*/
&mySchema = &rds.GetXSDSchema(&rs);
&f1 = GetFile(&RptOutputDir | "ASU_HR_NOA.xsd", "W", %FilePath_Absolute);
&f1.WriteLine(&mySchema);
&f1.Close();

/*Create Sample XML File*/
&myXMLFile = &rds.GetXMLData(&rs, &RptOutputDir | "ASU_HR_NOA.xsd");
&f2 = GetFile(&RptOutputDir | "ASU_HR_NOA.xml", "W", %FilePath_Absolute);
&f2.WriteLine(&myXMLFile);
&f2.Close();

/* output format */
&sOutputFormat = &sOutputFmt;

/*Provide a Data Source for the Report*/
&ReportDef.SetRuntimeDataRowset(&rs);

/*Generate the Report*/
&ReportDef.ProcessReport(&sTemplateId, %Language_User, %Date, &sOutputFormat);

/*Publish the Report*/
&ReportDef.Publish("", &RptOutputDir, "XMLP", 9999999);
rem &sFileExt = GetFileExtension(&sOutputFormat);

/*Send Mail*/
rem &ToList = "Jeromy.McMahon@asu.edu";
&ToList = &Email_to_from_page;
&FromList = "support@asu.edu";
&ReplyToList = "";
&Subject = "NOA (Notice of Appointment) Letter Attached";
&eMail.Recipients = &ToList; /*comma separeted list of email addresses*/
&eMail.From = &FromList; /*from email address*/
&eMail.ReplyTo = &ReplyToList; /*in case the reply is to be sent to a different email address*/
&eMail.Subject = &Subject;

/*Body for multiple parts*/
Local string &plain_text = "Attached you will find your copy of the Notice of Appointment letter.";
Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart();
&text.Text = &plain_text;

Local PT_MCF_MAIL:MCFBodyPart &attach = create PT_MCF_MAIL:MCFBodyPart();

&attach.SetAttachmentContent(&RptOutputDir | "RptInst\" | "ASUHRNOA.pdf", %FilePath_Absolute, "ASUHRNOA.pdf", "ASUHRNOA", "", "");

Local PT_MCF_MAIL:MCFMultipart &mp = create PT_MCF_MAIL:MCFMultipart();
&mp.AddBodyPart(&text);
&mp.AddBodyPart(&attach);
&eMail.MultiPart = ∓

Local integer &resp = &eMail.Send();
/*now check &resp for the result*/
Local boolean &done;
Evaluate &resp
When %ObEmail_Delivered
/*every thing ok*/
&done = True;
Break;
When %ObEmail_NotDelivered
/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/
&done = False;
Break;
When %ObEmail_PartiallyDelivered
/*check &eMail.InvalidAddresses, &eMail.ValidSentAddresses and &eMail.ValidUnsentAddresses*/
&done = True;
Break;
When %ObEmail_FailedBeforeSending
/*get the formatted messages from &eMail.ErrorDescription, &eMail.ErrorDetails*/
&done = False;
Break;
End-Evaluate;

CommitWork();

Audit requests - SQL samples

2008-10-21T14:13:00.003-07:00

I have had the pleasure of creating mass amounts of data for the State auditors this week. Here are some queries that I have coded for this data.

Note: You will need to change the plan types and dates for your own companies and institutions.

SELECT DISTINCT B.EMPLID, B.NAME, TO_CHAR(B.PAY_END_DT,'YYYY-MM-DD'), A.PLAN_TYPE, A.BENEFIT_PLAN, A.DED_CUR, A.DEDCD, A.DED_CLASS, TO_CHAR(C.EFFDT,'YYYY-MM-DD'), C.COVERAGE_ELECT, C.COVRG_CD, C.EMPLID
FROM PS_PAY_DEDUCTION A, PS_PAY_CHECK B, PS_HEALTH_BENEFIT C
WHERE A.COMPANY = B.COMPANY
AND A.PAYGROUP = B.PAYGROUP
AND A.PAY_END_DT = B.PAY_END_DT
AND A.OFF_CYCLE = B.OFF_CYCLE
AND A.PAGE_NUM = B.PAGE_NUM
AND A.LINE_NUM = B.LINE_NUM
AND A.SEPCHK = B.SEPCHK
AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR
AND A.PLAN_TYPE IN ('1X','1Y','1Z','10','11') /*** Health and Dental plan types ***/
AND C.EMPLID = B.EMPLID
AND C.EMPL_RCD = B.EMPL_RCD
AND C.COVERAGE_ELECT = 'E'
AND ( C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_HEALTH_BENEFIT C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.EMPL_RCD = C_ED.EMPL_RCD
AND C.COBRA_EVENT_ID = C_ED.COBRA_EVENT_ID
AND C.PLAN_TYPE = C_ED.PLAN_TYPE
AND C.BENEFIT_NBR = C_ED.BENEFIT_NBR
AND C_ED.EFFDT <= B.PAY_END_DT))
AND B.PAY_END_DT BETWEEN TO_DATE('2007-07-01','YYYY-MM-DD') AND TO_DATE('2008-06-30','YYYY-MM-DD')
AND A.PLAN_TYPE = C.PLAN_TYPE
ORDER BY 1, 3, 4

-- By month for Audits

select distinct
A.*,B.empl_rcd, B.empl_class,B.reg_temp, B.jobcode,B.comprate,B.annual_rt, B.grade, B.sal_admin_plan
,C.HIRE_DT,TO_CHAR(((SYSDATE - C.HIRE_DT) / 365),99.99) TOTAL_YEARS_WORKED
,TO_CHAR((SYSDATE - C.HIRE_DT),999999) TOTAL_DAYS_WORKED
,D.*, E.*
FROM (PS_PAY_CHECK A LEFT OUTER JOIN PS_JOB B ON A.COMPANY = B.COMPANY AND A.PAYGROUP = B.PAYGROUP AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD ), PS_EMPLOYMENT C, PS_PAY_EARNINGS D, PS_PAY_OTH_EARNS E
WHERE (B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.EMPL_RCD = B_ED.EMPL_RCD
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.EMPL_RCD = B_ES.EMPL_RCD
AND B.EFFDT = B_ES.EFFDT)
AND B.EMPLID = C.EMPLID
AND B.EMPL_RCD = C.EMPL_RCD
AND A.COMPANY = D.COMPANY
AND A.PAYGROUP = D.PAYGROUP
AND A.PAY_END_DT = D.PAY_END_DT
AND A.OFF_CYCLE = D.OFF_CYCLE
AND A.PAGE_NUM = D.PAGE_NUM
AND A.LINE_NUM = D.LINE_NUM
AND A.SEPCHK = D.SEPCHK
AND A.COMPANY = E.COMPANY
AND A.PAYGROUP = E.PAYGROUP
AND A.PAY_END_DT = E.PAY_END_DT
AND A.OFF_CYCLE = E.OFF_CYCLE
AND A.PAGE_NUM = E.PAGE_NUM
AND A.LINE_NUM = E.LINE_NUM
AND A.SEPCHK = E.SEPCHK
AND A.CHECK_DT BETWEEN TO_DATE('2007-07-01','YYYY-MM-DD') AND TO_DATE('2007-07-31','YYYY-MM-DD'))
order by a.emplid, a.paygroup

Referral Information on TAM - Requiring referrals when applying for a position

2008-08-06T12:16:00.003-07:00

Local Rowset &RS_Value;
&RS_Value = GetLevel0()(CurrentRowNumber()).GetRowset(Scroll.HRS_APP_PROFILE)(1).GetRowset(Scroll.HRS_AL_SRCE_I);

For &i = 1 To &RS_Value.ActiveRowCount

If &RS_Value(&i).HRS_AL_SRCE_I.HRS_SOURCE_ID.Value <> "" Or
&RS_Value(&i).HRS_AL_SRCE_I.HRS_SUBSOURCE_ID.Value <> "" Then
Error ("Please enter a referral source before continuing on.");
Else
&controller.nextPage();
&controller.hideunhideNextPrevious();
&controller.populateJobFamilies();

HRS_CECNFRM_MSG.HRS_SAVE_CONFM.Visible = False;
HRS_CECNFRM_MSG.HRS_APP_CHECK_ICN.Visible = False;
HRS_CECNFRM_MSG.HRS_APP_CHECK_ICN.Enabled = False;

End-If;

End-For;

Primary job / Peoplesoft - dealing with multiple jobs - JOB_INDICATOR

2008-06-11T16:27:00.003-07:00

This solution is a great one to set the JOB_INDICATOR to primary. This logic is based upon max(annual_rt) and min empl_rcd if the annual_rt is the same for the jobs.

Currently, we have a AE process that will set all max current rows to N, and all future dated rows to N. Then we evaluate which jobs are primary.
Here is a sample:

/*** Update rows to N ***/

UPDATE PS_JOB A
SET A.job_indicator = 'N', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE A.job_indicator <> 'N'
AND A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT<=SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)

/*** Update future dated rows ***/
UPDATE PS_JOB A
SET A.job_indicator = 'N', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE A.job_indicator <> 'N'
AND A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT > SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)

/*** Insert values into temp table for processing ***/
%InsertSelect(ASU_HR0022_TBL, JOB A, PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE), EMPLID = A.EMPLID, EMPL_RCD = A.EMPL_RCD, EFFDT = A.EFFDT, EFFSEQ = A.EFFSEQ, EMPL_STATUS = A.EMPL_STATUS, FTE = A.FTE,ANNUAL_RT = A.ANNUAL_RT, JOB_INDICATOR = 'P', ASU_OLD_JOB_IND = A.JOB_INDICATOR)
FROM PS_JOB A
WHERE A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT <= SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)
AND A.EMPL_STATUS NOT IN ('T','R')
AND A.ANNUAL_RT = (
SELECT MAX(A3.ANNUAL_RT)
FROM PS_JOB A3
WHERE A3.EMPLID = A.EMPLID
AND a3.empl_status NOT IN ('T','R')
AND a3.effdt = (
SELECT MAX(a3x.effdt)
FROM ps_job a3x
WHERE a3x.emplid = a3.emplid
AND a3x.empl_rcd = a3.empl_rcd
AND a3x.effdt <= sysdate)
AND a3.effseq = (
SELECT MAX(a3y.effseq)
FROM ps_job a3y
WHERE a3y.emplid = a3.emplid
AND a3y.empl_rcd = a3.empl_rcd
AND a3y.effdt = a3.effdt))
AND A.EMPL_RCD = (
SELECT MIN(A4.EMPL_RCD)
FROM PS_JOB A4
WHERE A4.EMPLID = A.EMPLID
AND a4.empl_status NOT IN ('T','R')
AND a4.annual_rt = a.annual_rt
AND a4.effdt = (
SELECT MAX(a4x.effdt)
FROM ps_job a4x
WHERE a4x.emplid = a4.emplid
AND a4x.empl_rcd = a4.empl_rcd
AND a4x.effdt <= sysdate)
AND a4.effseq = (
SELECT MAX(a4y.effseq)
FROM ps_job a4y
WHERE a4y.emplid = a4.emplid
AND a4y.empl_rcd = a4.empl_rcd
AND a4y.effdt = a4.effdt))

/*** Perform update ***/

UPDATE PS_JOB A
SET A.job_indicator = 'P', A.LASTUPDDTTM = %CurrentDateIn, A.LASTUPDOPRID = 'ASU_PRIM_JOB'
WHERE EXISTS (
SELECT 'X'
FROM PS_ASU_HR0022_TBL B
WHERE B.emplid = A.emplid
AND B.EMPL_RCD = A.EMPL_RCD
AND B.EFFDT = A.EFFDT
AND B.EFFSEQ = A.EFFSEQ
AND B.JOB_INDICATOR = 'P')
AND A.JOB_INDICATOR <> 'P'

/*** Peoplecode for report ***/

/*** Keep in mind, you will need to create a function for your own environment's path - J.McMahon ***/

Declare Function GetFilePathServer PeopleCode ASU_FILEPATH_WK.ECFILELISTPATH FieldFormula;

Local Record &Readrec, &RecLine, &Readrec2, &RecLine2;
Local File &MYFILE;
Local SQL &SQL2, &SQL3;
Local string &Filename;

GetFilePathServer(&outFilePath, &inFilePath, ASU_HR0022_AET.DBNAME);

If ASU_HR0022_AET.PROCESS_INSTANCE > 0 Then
If ASU_HR0022_AET.DBNAME = "ASUSAPRD" Then
&Filename = &outFilePath | "HR/" | "logs/ASU_PRIM_JOB_PRD.txt";
Else
&Filename = &outFilePath | "HR/" | "logs/ASU_PRIM_JOB.txt";
End-If;
Else
&Filename = "R:\temp\ASU_PRIM_JOB.txt";
End-If;

&MYFILE = GetFile(&Filename, "W", %FilePath_Absolute);

If &MYFILE.IsOpen Then
&MYFILE.WriteLine(" ");
&MYFILE.WriteLine(" ");
&MYFILE.WriteLine("Process Instance: " | ASU_HR0022_AET.PROCESS_INSTANCE | " - ASU_HR0022 - Primary Job Status Report");

Local number &ErrorCount;
&ErrorCount = 0;

SQLExec("select ASU_FILLER_N5 from SYSADM.PS_ASU_HR0022_VW", &ErrorCount);

&MYFILE.WriteLine(" ");
&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(" ");

/* Error Report */
&MYFILE.WriteLine(&ErrorCount | " - Errors found during this run");
&MYFILE.WriteLine(" ");

If &ErrorCount > 0 Then
&MYFILE.WriteLine(" EMPLID " | Char(44) | " Count ");
&Readrec = CreateRecord(Record.ASU_HR0022_VW_T);
&SQL2 = CreateSQL("SELECT * FROM SYSADM.PS_ASU_HR0022_VW");
While &SQL2.Fetch(&Readrec)
&MYFILE.WriteLine(&Readrec.EMPLID.Value | Char(44) | &Readrec.ASU_FILLER_N5.Value);
End-While;
&MYFILE.WriteLine(" ");

End-If;

&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(" ");

/* Success Report */

Local number &changedCount;
&changedCount = 0;

SQLExec("select COUNT(*) FROM PS_ASU_HR0022_TBL where process_instance = :1", ASU_HR0022_AET.PROCESS_INSTANCE, &changedCount);

&MYFILE.WriteLine(" ------------------------------------------------------------ ");
&MYFILE.WriteLine(&changedCount | " - JOB records updated during this run - (INTO Temp Table - PS_ASU_HR0022_TBL");

&MYFILE.WriteLine(" ");
&MYFILE.WriteLine("JOB DETAILS:");
&MYFILE.WriteLine(" ");

&MYFILE.WriteLine("Process Instance" | Char(44) | " EMPLID " | Char(44) | " Empl Rcd " | Char(44) | " Effdt " | Char(44) | " Effseq " | Char(44) | "Empl Status" | Char(44) | " FTE " | Char(44) | " Annual Rt " | Char(44) | " Job Indicator " | Char(44) | "Old Job Ind");
&Readrec2 = CreateRecord(Record.ASU_HR0022_TMP);
&SQL3 = CreateSQL("SELECT A.PROCESS_INSTANCE, A.EMPLID, A.EMPL_RCD, %DateOut(A.EFFDT), A.EFFSEQ, A.EMPL_STATUS, A.FTE, A.ANNUAL_RT, A.JOB_INDICATOR, A.ASU_OLD_JOB_IND FROM PS_ASU_HR0022_TBL A WHERE A.PROCESS_INSTANCE = :1", ASU_HR0022_AET.PROCESS_INSTANCE);
While &SQL3.Fetch(&Readrec2)
&MYFILE.WriteLine(&Readrec2.PROCESS_INSTANCE.Value | Char(44) | &Readrec2.EMPLID.Value | Char(44) | &Readrec2.EMPL_RCD.Value | Char(44) | &Readrec2.EFFDT.Value | Char(44) | &Readrec2.EFFSEQ.Value | Char(44) | &Readrec2.EMPL_STATUS.Value | Char(44) | &Readrec2.FTE.Value | Char(44) | &Readrec2.ANNUAL_RT.Value | Char(44) | &Readrec2.JOB_INDICATOR.Value | Char(44) | &Readrec2.ASU_OLD_JOB_IND.Value);
End-While;
End-If;

&MYFILE.Close();

Creating Audit records - AUDIT_PERS_NID (sample)

2008-06-11T01:24:00.004-07:00

I wanted to create a post about audits and how to work with audits within Peoplesoft. There are many different types of audits you can place within peoplesoft. I am going to discuss the main two.

(Database level audits and online audits)

Sample: Need to create a audit for when users change or update SSN (National_id)

Record that will be audited: PS_PERS_NID

Audit Record created: PS_AUDIT_PERS_NID
(Make sure all keys are removed from the audit record)

Add fields: AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN (Make fields required, check auto-update for AUDIT_STAMP)

Create Database Trigger:

CREATE OR REPLACE TRIGGER PERS_NID_TR
AFTER INSERT OR UPDATE OR DELETE ON PS_PERS_NID
FOR EACH ROW
DECLARE
V_AUDIT_OPRID VARCHAR2(64);
BEGIN
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);
IF INSERTING
THEN
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'A',:NEW.EMPLID,:NEW.COUNTRY,:NEW.NATIONAL_ID_TYPE,:NEW.NATIONAL_ID,:NEW.SSN_KEY_FRA,:NEW.PRIMARY_NID,:NEW.TAX_REF_ID_SGP,:NEW.LASTUPDDTTM,:NEW.LASTUPDOPRID);
ELSE
IF DELETING
THEN
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'D',:OLD.EMPLID,:OLD.COUNTRY,:OLD.NATIONAL_ID_TYPE,:OLD.NATIONAL_ID,:OLD.SSN_KEY_FRA,:OLD.PRIMARY_NID,:OLD.TAX_REF_ID_SGP,:OLD.LASTUPDDTTM,:OLD.LASTUPDOPRID);
ELSE
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'K',:OLD.EMPLID,:OLD.COUNTRY,:OLD.NATIONAL_ID_TYPE,:OLD.NATIONAL_ID,:OLD.SSN_KEY_FRA,:OLD.PRIMARY_NID,:OLD.TAX_REF_ID_SGP,:OLD.LASTUPDDTTM,:OLD.LASTUPDOPRID);
INSERT INTO PS_AUDIT_PERS_NID
VALUES (GET_PS_OPRID(V_AUDIT_OPRID),SYSDATE,'N',:NEW.EMPLID,:NEW.COUNTRY,:NEW.NATIONAL_ID_TYPE,:NEW.NATIONAL_ID,:NEW.SSN_KEY_FRA,:NEW.PRIMARY_NID,:NEW.TAX_REF_ID_SGP,:NEW.LASTUPDDTTM,:NEW.LASTUPDOPRID);
END IF;
END IF;
END PERS_NID_TR;
/

With a online audit, you would add this audit record to the record properties of PS_PERS_NID and migrate to PRD with those options you selected.

How to hide fields within subpage / page

2008-05-19T11:18:00.002-07:00

Here is a great sample of Page.Activate peoplecode that will hide fields within a page or subpage. You can also use a group box around a subpage to hide all the fields within that group box.

&admin = "N";
SQLExec("select 'Y' from psroleuser where roleuser = :1 and rolename = 'RoleName'", %OperatorId, &admin);
If &admin = "Y" Then
DERIVED_TL_WEEK.TL_TA_CALC_PB.Visible = True;
End-If;

/*** Within Sub Page ***/
TL_LINK_WRK.TL_TEXT_LBL5.Visible = False;
TL_LINK_WRK.TL_TEXT_LBL1.Visible = False;
TL_LINK_WRK.TL_TEXT_LBL2.Visible = False;

SQL for Savings Bonds Interface

2008-05-15T13:23:00.002-07:00

Here is a sample of a savings bond outbound interface.
(This would be your population query within a AE step)

SELECT NVL(A.EMPLID
, ' ')
, L.CHECK_DT
, 'B'
, NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN DED.SSN ELSE (
SELECT NID2.NATIONAL_ID
FROM PS_DEP_BENEF_NID NID2
WHERE NID2.EMPLID = A.EMPLID
AND NID2.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
FROM PS_NAMES NAM
WHERE NAM.EMPLID = DED.EMPLID
AND NAM.EFFDT = (
SELECT MAX(NAM_ED.EFFDT)
FROM PS_NAMES NAM_ED
WHERE NAM.EMPLID = NAM_ED.EMPLID
AND NAM.NAME_TYPE = NAM_ED.NAME_TYPE
AND NAM_ED.EFFDT <= SYSDATE) ) ELSE (
SELECT NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
FROM PS_DEPENDENT_BENEF NAM
WHERE NAM.EMPLID = A.EMPLID
AND NAM.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT (CASE WHEN AD.COUNTRY = 'USA' THEN '0' ELSE '1' END)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT >= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT (CASE WHEN DEPAD.COUNTRY = 'USA' THEN '0' ELSE '1' END)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), '0') , (CASE WHEN B.BOND_DLVY_ID IN ('EE', ' ') THEN '0' ELSE '1' END) ,
NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS1
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS1
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN DED.NAME ELSE (
SELECT NAM.NAME
FROM PS_DEPENDENT_BENEF NAM
WHERE NAM.EMPLID = A.EMPLID
AND NAM.DEPENDENT_BENEF = B.BOND_OWNER_ID) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS2
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS2
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.ADDRESS3
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.ADDRESS3
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.CITY
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.CITY
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT AD.STATE
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT DEPAD.STATE
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT SUBSTR(AD.POSTAL
,1
,5)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT SUBSTR(DEPAD.POSTAL
,1
,5)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , NVL((CASE WHEN B.BOND_OWNER_ID IN ('EE') THEN (
SELECT SUBSTR(AD.POSTAL
,7
,10)
FROM PS_ADDRESSES AD
WHERE A.EMPLID = AD.EMPLID
AND AD.ADDRESS_TYPE = 'HOME'
AND AD.EFFDT = (
SELECT MAX(AD1.EFFDT)
FROM PS_ADDRESSES AD1
WHERE AD1.EMPLID = AD.EMPLID
AND AD1.ADDRESS_TYPE = AD.ADDRESS_TYPE
AND AD1.EFFDT <= SYSDATE)) WHEN B.BOND_OWNER_ID NOT IN ('EE') THEN (
SELECT SUBSTR(DEPAD.POSTAL
,7
,10)
FROM PS_DEPENDENT_BENEF DEPAD
WHERE A.EMPLID = DEPAD.EMPLID
AND DEPAD.DEPENDENT_BENEF = B.BOND_OWNER_ID
AND DEPAD.ADDRESS_TYPE IN ('HOME', ' ')) END), ' ') , B.BOND_OTH_REG_TYPE, (CASE WHEN B.BOND_OTH_REG_TYPE = 'C' THEN 'OR'
WHEN B.BOND_OTH_REG_TYPE = 'B' THEN 'POD' ELSE ' ' END) , NVL((CASE WHEN B.BOND_OTH_REG_TYPE IN ('C', 'B')
AND B.BOND_OTH_REG_ID IN ('EE') THEN (
SELECT NVL(NAM.FIRST_NAME||' '||substr(NAM.MIDDLE_NAME
, 1
,1)||' '||NAM.LAST_NAME
, ' ')
FROM PS_NAMES NAM
, PS_ASU_DED_INTRFAC DED2
WHERE B.EMPLID = DED2.EMPLID
AND DED2.DEDCD = 'SAVBND'
AND NAM.EMPLID = B.EMPLID
AND NAM.EFFDT = (
SELECT MAX(NAM_ED.EFFDT)
FROM PS_NAMES NAM_ED
WHERE NAM.EMPLID = NAM_ED.EMPLID
AND NAM.NAME_TYPE = NAM_ED.NAME_TYPE
AND NAM_ED.EFFDT <= SYSDATE) ) WHEN B.BOND_OTH_REG_TYPE IN ('C','B')
AND B.BOND_OTH_REG_ID NOT IN ('EE')THEN (
SELECT NVL(D.FIRST_NAME||' '||substr(D.MIDDLE_NAME
, 1
,1)||' '||D.LAST_NAME
, ' ')
FROM PS_DEPENDENT_BENEF D
WHERE D.DEPENDENT_BENEF = B.BOND_OTH_REG_ID
AND B.EMPLID = D.EMPLID) END), ' ') , (CASE WHEN B.BOND_ID = '100' THEN '4' WHEN B.BOND_ID = '200' THEN '5' WHEN B.BOND_ID =
'500' THEN '6' WHEN B.BOND_ID = '1K' THEN '7' WHEN B.BOND_ID = '5K' THEN '8' WHEN B.BOND_ID = '10K' THEN '9' ELSE '0' END) ,
L.BOND_PURCH_UNITS, A.COMPANY, NVL(A.LOCATION, ' ') , NVL(DED.SSN, ' '), DED.PAY_END_DT, DED.PAY_END_DT, ' ', ' ', '9999999999'

FROM PS_ASU_DED_INTRFAC DED, PS_JOB A, PS_BOND_SPEC_DATA B, PS_BOND_LOG L
WHERE A.EFFDT = (
SELECT MAX(A1.EFFDT)
FROM PS_JOB A1
WHERE A1.EMPLID = A.EMPLID
AND A1.EMPL_RCD = A.EMPL_RCD
AND A1.EFFDT <= SYSDATE)
AND A.EFFSEQ = (
SELECT MAX(A2.EFFSEQ)
FROM PS_JOB A2
WHERE A2.EMPLID = A.EMPLID
AND A2.EMPL_RCD = A.EMPL_RCD
AND A2.EFFDT = A.EFFDT)
AND A.EMPL_STATUS IN ('A','L','P','S')
AND DED.EMPLID = A.EMPLID
AND DED.DEDCD = 'SAVBND' /* Add your dedcd here */
AND DED.DED_CLASS = 'A'
AND A.JOB_INDICATOR = 'P'
AND B.EMPLID = A.EMPLID
AND B.COMPANY = 'Your Company Here' /* Add your company here */
AND B.EFFDT = (
SELECT MAX(B1.EFFDT)
FROM PS_BOND_SPEC_DATA B1
WHERE B1.EMPLID = B.EMPLID
AND B1.COMPANY = B.COMPANY
AND B1.PRIORITY = B.PRIORITY
AND B1.EFFDT <= SYSDATE)
AND B.EMPLID = L.EMPLID
AND B.BOND_ID = L.BOND_ID
AND B.BOND_OWNER_ID = L.BOND_OWNER_ID
AND L.BOND_PURCH_UNITS >= 1
AND L.PAY_END_DT = DED.PAY_END_DT

SQL for Position Managment

2008-04-28T16:15:00.000-07:00

This is a good sample for obtaining your current SUP and reports_to. (Based upon position)

SELECT a.emplid
, a.position_nbr
, a.reports_to
, b.name
, b.first_name
, b.last_name
, b.middle_name
, c.phone
, c.extension
FROM ps_job a
, ps_names b
, ps_personal_phone c
, ps_position_data d
WHERE a.emplid = b.emplid
AND a.empl_status NOT IN ('R','T')
AND a.hr_status = 'A'
AND a.effdt = (
SELECT MAX(a1.effdt)
FROM ps_job a1
WHERE a1.emplid = a.emplid
AND a1.empl_rcd = a.empl_rcd
AND a1.effdt <= sysdate)
AND a.effseq = (
SELECT MAX(a2.effseq )
FROM ps_job a2
WHERE a2.emplid = a.emplid
AND a2.empl_rcd = a.empl_rcd
AND a2.effdt = a.effdt )
AND a.emplid = c.emplid
AND c.phone_type = 'WORK'
AND b.name_type = 'PRI'
AND b.effdt = (
SELECT MAX(b_ed.effdt)
FROM ps_names b_ED
WHERE b_ed.emplid = b.emplid
AND b_ed.name_type = b.name_type
AND b_ed.effdt <= sysdate)
AND a.position_nbr = d.position_nbr
AND d.eff_status = 'A'
AND d.effdt = (
SELECT MAX(d_ed.effdt)
FROM ps_position_data d_ED
WHERE d_ed.position_nbr = d.position_nbr
AND d_ed.eff_status = d.eff_status
AND d_ed.effdt <= sysdate)
AND A.POSITION_NBR <> A.REPORTS_TO

XMLP / App Engine / App Package

2008-04-28T15:57:00.003-07:00

Step by Step creating a Application Engine, that uses App Package, to create *.XML files from SQL objects.

1. Initialize State
%Select(OPRID,RUN_CNTL_ID)
SELECT OPRID
, RUN_CNTL_ID
FROM PS_PRCSRUNCNTL
WHERE OPRID=%OperatorId
AND RUN_CNTL_ID = %RunControl

2. Get Run Control parms - (If Any)
3. Get the data from a custom view, and place/insert into a TAO (temp table)

%InsertSelect(DISTINCT, ASU_TL_SB_TAO, ASU_TL_APROV_VW a, process_instance = %processinstance)
FROM %Table(ASU_TL_APROV_VW) a
WHERE a.reports_to = %Bind(REPORTS_TO)
AND a.reported_status = %Bind(REPORTED_STATUS)
AND a.dur BETWEEN %Bind(PERIOD_BEGIN_DT) AND %Bind(PERIOD_END_DT)

4. Process App Package call

import ASU_TL_APPROV_PRINT:Report;

Component ASU_TL_APPROV_PRINT:Report &oReport;

&mval = String(ASU_TL_SBL_AET.PROCESS_INSTANCE.Value);
&oReport = create ASU_TL_APPROV_PRINT:Report();
&oReport.PublishReport(&mval, ASU_TL_SBL_AET.PROCESS_INSTANCE.Value);

App Package code: Report - OnExecute

import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
import PSXP_ENGINE:*;
import ASU_TL_APPROV_PRINT:Util:ReportUtil;

import %metadata:*;
import %metadata:XMLPubRptMgr:*;

class Report
property string PrintFormat get set;
property string PrintSort get set;
property string XMLOutputOption get set;
property string XMLOutputFilePath get set;

method Report();
method SQLWhereClause();
method PublishReport(&parmRptReqNbr As string, &nProcInst As number);

/*last change method PublishReport();*/
method DisplayReport(&parmRptReqNbr As string, &parmReqSeqNbr As number, &PARMREQRN As string, &TEMPLATE As string);
method PrintReport(&parmRptReqNbr As string, &parmPrinterPath As string);

private

instance Rowset &rs_Child, &rs_Main, &rs_temp, &rs_temp2, &rs_pa,;
instance string &template_use, &sRptID, &sRptReqNbr, &print_format, &print_sort, &xmlfile, &xmlfilepath, &sSQLWhereClause, &process_instance, &pa_code, &rowtype;
instance number &RN, &nReqSeqNbr, &ProcessInstance, &iCount, &a, &b, &c, &e, &g, &h, &i, &j, &k, &m, &n, &t, &req_seq_nbr, &rpt_nbr, &paCount, &numrows;
instance date &effdt;
instance XmlDoc &objXMLData;
instance XmlNode &objBaseNode, &objTermNode;
instance XmlDoc &objXSDSchema;
instance string &strFileOutput;
instance string &sProcessId, &sOutputDir, &sOutputFile;

instance ASU_TL_APPROV_PRINT:Util:ReportUtil &objReportUtil;
instance PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
instance XmlDoc &inXMLDoc;
instance string &test;
instance SQL &SQL_TA1, &SQL_TA2, &SQL_TA3, &SQL_TA4, &SQL_TA5;

method BuildDataRowset();
method GenSchema();
method CreateXMLDataFile();

end-class;

get PrintFormat
/+ Returns String +/
Return &print_format;
end-get;

set PrintFormat
/+ &NewValue as String +/
/* Default Print Format to Landscape if blank */
If &NewValue = "" Then
&print_format = "1"
Else
&print_format = &NewValue;
End-If;
end-set;

get PrintSort
/+ Returns String +/
Return &print_sort;
end-get;

set PrintSort
/+ &NewValue as String +/
end-set;

get XMLOutputOption
/+ Returns String +/
Return &xmlfile;
end-get;

set XMLOutputOption
/+ &NewValue as String +/
&xmlfile = &NewValue;
end-set;

get XMLOutputFilePath
/+ Returns String +/
Return &xmlfilepath;
end-get;

set XMLOutputFilePath
/+ &NewValue as String +/
&xmlfilepath = &NewValue;
end-set;

method Report

/* Set Report ID*/
/*CHANGE LINE
THIS IS THE REPORT NAME*/
&sRptID = "ASUTLAPV";
/* create Report Utility object */
&objReportUtil = create ASU_TL_APPROV_PRINT:Util:ReportUtil();
/*CHANGE LINE
THIS IS THE CHILD ROW SET RECORD*/
&rs_Child = CreateRowset(Record.ASU_TL_SB_TAO);

end-method;

method SQLWhereClause

end-method;

method GenSchema
Local string &sAdapter, &sDSQueryName;
Local XmlNode &objXSDSchemaBaseNode;
Local Rowset &rsResult;
Local Row &rowResult;

&rsResult = CreateRowset(Record.ASU_TL_SBSTAO);

/* Generate Schema Header XMLDoc */
&objXSDSchema = &objReportUtil.CreateSchemaHeader(&rsResult);
&objXSDSchemaBaseNode = &objXSDSchema.DocumentElement;

end-method;

/* This method will create the datasource XMLfile used by XMLP */
/* It will be stored on the app or process scheduler server */
method CreateXMLDataFile
/*CHANGE LINE (s)
THIS IS THE PARENT RECORD*/
Local Record &rec = CreateRecord(Record.ASU_TL_SBSTAO);
/*THIS IS THE CHILD RECORD*/
Local Record &recchild = CreateRecord(Record.ASU_TL_SB_TAO);

Local number &nRptNbr;
Local SQL &SQL_HDR, &sql_line;
Local boolean &svw1_exists, &svw2_exists, &term_exists;
Local string &emplid, &prev_emplid, &detailorg;

/* Setup XML file directory */
/*CHANGE LINE
THIS IS THE OUTPUT FILE NAME*/
&sOutputFile = &objReportUtil.GetOutputFile("ASU_TL_SBL.xml");
Local File &FILE = GetFile(&sOutputFile, "W", %FilePath_Absolute);
Local Record &recTemp;
/*CHANGE LINE
THIS SELECTS ALL THE HEADER ROWS*/
&SQL_HDR = CreateSQL("%SELECTALL(:1)");
/* THIS IS THE FILE LAYOUT*/
If &FILE.SetFileLayout(FileLayout.ASU_TL_APPROV_RPT) Then
/* grab all the data for top level */
&FILE.WriteLine("");
&SQL_HDR.Execute(&rec);
End-If;
While &SQL_HDR.Fetch(&rec)

&FILE.Open(&sOutputFile, "A", "UTF8", %FilePath_Absolute);
/* I use Fetch & WriteRecord because that allows me to fetch the rows one by one and not keep
the whole thing in memory as would be required using a RowSet*/
&FILE.WriteRecord(&rec);
/* grab the Level 1 child data for current top row */

/*CHANGE LINE
THIS IS THE SQL TO GET THE DATA FOR THE CHILD*/
&sql_line = CreateSQL("%selectall(:1)");

&sql_line.Execute(&recchild);
While &sql_line.Fetch(&recchild);
&FILE.WriteRecord(&recchild);
/*CHANGE LINE
THIS WRITES THE CHILD FOOTER*/
&FILE.WriteLine("");
End-While;
/*CHANGE LINE
THIS WRITES THE PARRENT FOOTER*/
&FILE.WriteLine("");

&FILE.Close();

End-While;

/* close SQL objects */
&SQL_HDR.Close();

&FILE.Open(&sOutputFile, "A", "UTF8", %FilePath_Absolute);
&FILE.WriteLine("");
&FILE.Close();

end-method;

/* Output a transcript report request to the Repor Repository or to a Printer */
method PublishReport
/+ &parmRptReqNbr as String, +/
/+ &nProcInst as Number +/

Local PSXP_XMLGEN:RowSetDS &oRowSetDSDefn;
Local File &MYFILE;
Local number &nOutDestType;
Local string &rpt_template;
try
/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
%This.SQLWhereClause();

/* Create XML File */
WriteToLog(%ApplicationLogFence_Level1, "CreateXMLFile Start: " | String(%Datetime));
%This.CreateXMLDataFile();
WriteToLog(%ApplicationLogFence_Level1, "CreateXMLFile End: " | String(%Datetime));
/* create the report definition */

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn("ASUTLSBL");
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&sOutputFile);

/* Create the PDF report if option is not XML ONLY */

If %This.XMLOutputOption <> "X" Then
/* generate the report document using the default format (PDF) on the report template */
Local string &tempFormat;
&tempFormat = &oRptDefn.GetDefaultOutputFormat();

&rpt_template = &template_use;
&oRptDefn.ProcessReport(&rpt_template, "", %Date, &tempFormat);

/* find destination (%OutDestType is only when this is called from an AE */
/* if called from from component peoplecode, the OutDestType should be given */
Local string &dir, &rrid;
Local number &pos;
&dir = GetEnv("PS_SERVDIR");
&pos = Find("prcs", &dir);

If &pos > 0 Then
/* This is being run on the process scheduler - this metavariable only available in AE */
&nOutDestType = %OutDestType;
Else
/* This is being run on the appserver - must send to Web (Report Manager) */
&nOutDestType = 6;

End-If;

/* send output to destination */
Evaluate &nOutDestType
When 3 /* Printer */
&oRptDefn.PrintOutput(%FilePath);
Break;
When 6 /* Web */
&sOutputDir = &oRptDefn.OutDestination;
SQLExec("SELECT contentid FROM PS_CDM_LIST where prcsinstance = :1", &nProcInst, &rrid);
MessageBox(0, "", 0, 0, &sRptID);
WriteToLog(%ApplicationLogFence_Error, &sRptID);

&objReportUtil.PostReport(&nProcInst, "", &sOutputDir, &rrid, "", "", Date(""), "");
Break;
End-Evaluate;

End-If;

/* Copy temporary XML file to user Output File Path */

If (%This.XMLOutputOption = "X" Or
%This.XMLOutputOption = "Y") And
%This.XMLOutputFilePath <> "" Then
&strFileOutput = &objReportUtil.writeOutputFile(RTrim(%This.XMLOutputFilePath) | "ASU_TL_SBL" | &parmRptReqNbr, ".xml");
MessageBox(0, "", 0, 0, &parmRptReqNbr);
MessageBox(0, "", 0, 0, "ABOVVE");
Else
/* Delete temporary XML file */
&objReportUtil.deleteDataXML();
End-If;

catch Exception &Err

WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;

end-method;

method DisplayReport
/+ &parmRptReqNbr as String, +/
/+ &parmReqSeqNbr as Number, +/
/+ &PARMREQRN as String, +/
/+ &TEMPLATE as String +/
try
&template_use = &TEMPLATE;

/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
&nReqSeqNbr = &parmReqSeqNbr;

/* Create XML File */
%This.CreateXMLDataFile();

&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptID);
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataXMLFile(&sOutputFile);
&oRptDefn.ProcessReport(&template_use, "", %Date, "");
CommitWork();
&oRptDefn.DisplayOutput();

/* Delete temporary XML file */
&objReportUtil.deleteDataXML();

catch Exception &Err
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;
end-method;

method PrintReport
/+ &parmRptReqNbr as String, +/
/+ &parmPrinterPath as String +/
rem Local PSXP_XMLGEN:RowSetDS &oRowSetDSDefn;
Local File &MYFILE;

/* create datasource rowset */
&sRptReqNbr = &parmRptReqNbr;
%This.SQLWhereClause();
/* &rs_Main = CreateRowset(Record.SSR_TSRSLT_HDR, &rs_pa, &rs_term);
&rs_Main.Fill(&sSQLWhereClause, &sRptReqNbr);*/
/*ravi version*/
&rs_Main = CreateRowset(Record.ASU_TL_TEW_HDR);
&rs_Main.Fill();
%This.BuildDataRowset();

/* Publish Output */

/* create the report definition */
rem Local PSXP_RPTDEFNMANAGER:ReportDefn &oRptDefn;
&sRptID = "ASUTLAPV";
&oRptDefn = create PSXP_RPTDEFNMANAGER:ReportDefn(&sRptID);
&oRptDefn.Get();
&oRptDefn.SetRuntimeDataRowset(&rs_Main);

/* generate the report document using the default format (PDF) on the report template */
&oRptDefn.ProcessReport("", "", %Date, "");

/* send output to destination */
rem &oRptDefn.PrintOutput(&parmPrinterPath);
&oRptDefn.PrintOutput("\\vdbux42:cc212hp_secure");

end-method;

method BuildDataRowset

try
/* Create Rowset to be converted to XML */
For &a = 1 To &rs_Main.ActiveRowCount
&req_seq_nbr = &rs_Main(&a).SSR_TSRSLT_HDR.REQUEST_SEQ_NBR.Value;
&rpt_nbr = &rs_Main(&a).SSR_TSRSLT_HDR.REPORT_NUMBER.Value;
&rs_pa = &rs_Main(&a).GetRowset(Scroll.SSR_TSRSLT_SVW1);

&paCount = 1;
End-For
catch Exception &Err
WriteToLog(%ApplicationLogFence_Error, &Err.ToString());

end-try;
end-method;

Here is the reportUtil:

import PSXP_XMLGEN:RowSetDS;
import PSXP_RPTDEFNMANAGER:*;

class ReportUtil;

method ReportUtil();
method CreateSchemaHeader(&rsResult As Rowset) Returns XmlDoc;
method AddRowSetSchema(&rsAdd As Rowset, &objBaseNode As XmlNode) Returns XmlNode;

method CreateXMLDataHeader(&rsResult As Rowset, &nProcessInstance As number) Returns XmlDoc;
method AddRowSetXMLData(&rsAdd As Rowset, &objBaseNode As XmlNode, &nNode As number, &NumRows As number, &nProcessInstance As number) Returns XmlNode;

method GetOutputFile(&parmFileName As string) Returns string;

method getRuntimeDistribution(&processInstanceId As number, &oRptViewerArray2 As array of PSXP_RPTDEFNMANAGER:ReportViewer) Returns array of PSXP_RPTDEFNMANAGER:ReportViewer;
method PostReport(&processInstanceId As number, &sDbName As string, &outDest As string, &sReportDescr As string, &folderName As string, &serverName As string, &dExpire As date, &sburstVal As string);

method getDefaultFolderName() Returns string;
method writeOutputFile(&strFileName As string, &strFileType As string) Returns string;
method deleteDataXML();

private
method ParseStringToXMLDoc(&sXmlStringIn As string) Returns XmlDoc;
method CheckDupeRowSetElement(&objBaseNode As XmlNode) Returns XmlNode;

instance PSXP_XMLGEN:RowSetDS &objRowSetDS;

instance string &sServDir, &sDirSep, &sOutputDir, &sOutputFile;
instance array of PSXP_RPTDEFNMANAGER:ReportViewer &oRptViewerArray;
instance PostReport &Post;

end-class;

method ReportUtil /* Constructor */

&objRowSetDS = create PSXP_XMLGEN:RowSetDS();

&sServDir = GetEnv("PS_SERVDIR");
&sDirSep = "/";
If Substring(&sServDir, 1, 1) <> "/" Then
&sDirSep = "\"
End-If;

end-method;

method CreateSchemaHeader
/+ &rsResult as Rowset +/
/+ Returns XmlDoc +/
Local string &sXSDString;
Local XmlDoc &objXSDSchema;

&sXSDString = &objRowSetDS.getXSDSchema(&rsResult);
&objXSDSchema = %This.ParseStringToXMLDoc(&sXSDString);

Return &objXSDSchema;

end-method;

method AddRowSetSchema
/+ &rsAdd as Rowset, +/
/+ &objBaseNode as XmlNode +/
/+ Returns XmlNode +/
Local XmlNode &objElementNode, &objThisXSDNode;
Local string &sRowSetName, &sRowsetXsdName, &sRowsetXsdType;

&sRowSetName = &objRowSetDS.getRowSetName(&rsAdd);

&sRowsetXsdName = "rs_" | &sRowSetName;

&objElementNode = &objBaseNode.GetChildNode(4).GetChildNode(1);
&objThisXSDNode = &objElementNode.AddElement("xsd:element");
&objThisXSDNode.AddAttribute("name", &sRowsetXsdName);
&sRowsetXsdType = &sRowsetXsdName | "type";
&objThisXSDNode.AddAttribute("type", &sRowsetXsdType);

&objRowSetDS.addXSDSchema(&objBaseNode As XmlNode, &rsAdd As Rowset);

/* Check for duplicate simpleType coming from &rsAdd */
&objBaseNode = %This.CheckDupeRowSetElement(&objBaseNode);

Return &objBaseNode;

end-method;

method CreateXMLDataHeader
/+ &rsResult as Rowset, +/
/+ &nProcessInstance as Number +/
/+ Returns XmlDoc +/
Local string &sXMLString;
Local XmlDoc &objXMLData;
/*
If &nProcessInstance = 0 Then
&rsResult = %This.AddSampleData(&rsResult);
End-If;
*/
&sXMLString = &objRowSetDS.getXMLData(&rsResult, "");
&objXMLData = %This.ParseStringToXMLDoc(&sXMLString);

Return &objXMLData;

end-method;

method ParseStringToXMLDoc
/+ &sXmlStringIn as String +/
/+ Returns XmlDoc +/

Local XmlDoc &objXMLDocOut;
Local boolean &booReturn;

&objXMLDocOut = CreateXmlDoc("");

&booReturn = &objXMLDocOut.ParseXmlString(&sXmlStringIn);
If &booReturn Then
Return &objXMLDocOut;

rem Else;
rem throw create SCC_COMMGEN:Util:CommGenException(14015, 999, "999 Error creating XMLDoc" | "", "", "", "", "", "");
End-If;
end-method;

method AddRowSetXMLData
/+ &rsAdd as Rowset, +/
/+ &objBaseNode as XmlNode, +/
/+ &nNode as Number, +/
/+ &NumRows as Number, +/
/+ &nProcessInstance as Number +/
/+ Returns XmlNode +/
Local XmlNode &objThisXMLNode;
Local string &sRowSetName, &sRowsetXsdName;
Local number &nRowCount;

&sRowSetName = &objRowSetDS.getRowSetName(&rsAdd);

&sRowsetXsdName = "rs_" | &sRowSetName;

&objThisXMLNode = &objBaseNode.GetChildNode(&nNode).AddElement(&sRowsetXsdName);
&objThisXMLNode.AddAttribute("rowsetname", &sRowSetName);
&objThisXMLNode.AddAttribute("numrows", String(&NumRows));

/*
If &nProcessInstance = 0 Then
&rsAdd = %This.AddSampleData(&rsAdd);
End-If;
*/
&objRowSetDS.addXMLData(&objThisXMLNode As XmlNode, &rsAdd As Rowset);

/* If 1 return the current node passed in else return the child node */
/*
If &nOption = 1 Then
Return &objBaseNode;
Else
Return &objThisXMLNode;
End-If;
*/
Return &objBaseNode;
rem Return &objThisXMLNode;
end-method;

method CheckDupeRowSetElement
/+ &objBaseNode as XmlNode +/
/+ Returns XmlNode +/
Local array of XmlNode &arrBaseElementList;
Local XmlNode &removedNode;
Local string &sAttributeValue1, &sAttributeValue2;
Local number &i, &j;

&arrBaseElementList = &objBaseNode.GetElementsByTagName("xsd:simpleType");

For &i = 1 To &arrBaseElementList.Len
&sAttributeValue1 = &arrBaseElementList [&i].GetAttributeValue("name");
For &j = &i + 1 To &arrBaseElementList.Len
&sAttributeValue2 = &arrBaseElementList [&j].GetAttributeValue("name");
If &sAttributeValue1 = &sAttributeValue2 Then
&removedNode = &objBaseNode.RemoveChildNode(&arrBaseElementList [&j]);
End-If;
End-For;
End-For;

Return &objBaseNode;
end-method;

method GetOutputFile
/+ &parmFileName as String +/
/+ Returns String +/

Local string &sProcessId;
/* Create a file to hold the XML data */
&sProcessId = UuidGen();

CreateDirectory(&sServDir | "/files/XMLP/" | &sProcessId, %FilePath_Absolute);
CreateDirectory(&sServDir | "/files/XMLP/" | &sProcessId | "/XMLData", %FilePath_Absolute);
&sOutputDir = &sServDir | &sDirSep | "files" | &sDirSep | "XMLP" | &sDirSep | &sProcessId;
&sOutputFile = &sOutputDir | &sDirSep | "XMLData" | &sDirSep | &parmFileName;

Return &sOutputFile
end-method;

method getDefaultFolderName
/+ Returns String +/
Local string &foldername;

/* mdu ICE 1520060000 */
SQLExec("SELECT PSRF_FOLDER_NAME FROM PSRF_FLIST_TBL WHERE PSRF_IS_DEFAULT = 'Y'", &foldername);

Return &foldername;
end-method;

method PostReport
/+ &processInstanceId as Number, +/
/+ &sDbName as String, +/
/+ &outDest as String, +/
/+ &sReportDescr as String, +/
/+ &folderName as String, +/
/+ &serverName as String, +/
/+ &dExpire as Date, +/
/+ &sburstVal as String +/

rem Local PostReport &Post;
Local string &sUserType, &sUserId, &distNode, &os, &sFolderName, &sServerName, &sOutDestFormat;
Local boolean &bRet;
Local integer &iViewer;
Local number &RptInstance;
Local string &sDistNodeURL, &sFileName, &sRptURL;

WriteToLog(%ApplicationLogFence_Level1, "PostReportDistribute : Process Instance : " | &processInstanceId | " DBName : " | &sDbName | "Destination: " | &outDest | " ReportDescr : " | &sReportDescr);

/* create postReportObject */
&Post = SetPostReport();
&Post.ProcessName = "XMLP"; /* do not change. used by Report Search view */
&Post.ProcessType = "XML Publisher";

&outDest = &outDest | "/RptInst";
&outDest = Substitute(&outDest, "/", &sDirSep);
&Post.SourceReportPath = &outDest;

/*
If (&sburstVal <> "") Then
&sburstVal = " [" | &sburstVal | "] ";
Else
&sburstVal = " ";
End-If;
*/

rem &Post.ReportDescr = %This.ID | &sburstVal | &sReportDescr;
&Post.ReportDescr = &sReportDescr;

If (&processInstanceId <> 0) Then
/* get servername, foldername from scheduler request table */
SQLExec("SELECT PSRF_FOLDER_NAME, SERVERNAMERUN FROM PSPRCSRQST WHERE PRCSINSTANCE = :1", &processInstanceId, &sFolderName, &sServerName);
Else
/* get servername from report node */;
SQLExec("SELECT DISTNODENAME, OPSYS from PS_CDM_DIST_NODE", &distNode, &os);
/* WinMessage(&distNode);*/
SQLExec("SELECT SERVERNAME from PS_SERVERDEFN WHERE DISTNODENAME = :1 and OPSYS = :2", &distNode, &os, &sServerName);
End-If;

/* if method parameters exist for folder and server, otherwise use default values */
If &folderName = "" Then
&folderName = &sFolderName;
End-If;
If &folderName = "" Then
&folderName = %This.getDefaultFolderName();
End-If;
&Post.ReportFolder = &folderName;

If &serverName = "" Then
&serverName = &sServerName;
End-If;
&Post.ServerName = &serverName;

If All(&dExpire) Then
&Post.ExpirationDate = &dExpire;
Else
/* &Post.ExpirationDate = %DATE;*/
End-If;

&sOutDestFormat = "PDF";
&Post.OutDestFormat = &sOutDestFormat;
WriteToLog(%ApplicationLogFence_Level1, "The value is" | &processInstanceId);
If (&processInstanceId <> 0) Then
WriteToLog(%ApplicationLogFence_Level1, "Posting");
&Post.ProcessInstance = &processInstanceId;
End-If;

/* set distribution options */
Local string &sDistIdType_User, &sDistIdType_Role;
&oRptViewerArray = %This.getRuntimeDistribution(&processInstanceId, &oRptViewerArray);

If &oRptViewerArray <> Null Then
For &iViewer = 1 To &oRptViewerArray.Len
&sUserType = &oRptViewerArray [&iViewer].Type;
&sUserId = &oRptViewerArray [&iViewer].ID;
If (&sUserType = "2") Then
&sUserType = &sDistIdType_User;
End-If;
If (&sUserType = "3") Then
&sUserType = &sDistIdType_Role;
End-If;
WriteToLog(%ApplicationLogFence_Level1, "PostReportDistribute : usertype " | &sUserType | " userid " | &sUserId);
&Post.AddDistributionOption(&sUserType, &sUserId);
End-For;
Else
&Post.AddDistributionOption("2", %UserId);
REM &Post.AddDistributionOption("User", "PS");
rem WriteToLog(%ApplicationLogFence_Warning, "*** Warning! Report access is not set.");
End-If;

/* do post */
&Post.Put();

&RptInstance = &Post.ReportId;
&processInstanceId = &Post.ProcessInstance;

If (&RptInstance > 0) Then
MessageBox(0, "", 63, 119, "Successfully processed request with Rpt. ID %1 for Process %2 to post from directory %3", &RptInstance, &Post.ProcessName, &Post.SourceReportPath);
Else
MessageBox(0, "", 63, 122, "Not successful for process request for Process %1 to post from directory %2", &Post.ProcessName, &Post.SourceReportPath);
End-If;

If &RptInstance > 0 Then
WriteToLog(%ApplicationLogFence_Level1, "Publish : Report Instance : " | &RptInstance | " Process Instance : " | &processInstanceId);

/* formulate the report file url */
/* cannot be directly obtained from database (PS_CDM_LIST) since commit is not done yet.*/

<*
/*get distribution node url */
SQLExec("select C.URL from PSPRCSRQST A, PS_SERVERDEFN B, PS_CDM_DIST_NODE C WHERE A.SERVERNAMERUN = B.SERVERNAME AND B.DISTNODENAME = C.DISTNODENAME", &sDistNodeURL);
If %This.UseBurstValueAsOutputFileName Then
&sFileName = &sburstVal;
Else
&sFileName = &ID;
End-If;
*>
&sFileName = &sReportDescr;
&sFileName = &sFileName | "." | Lower(&sOutDestFormat);

&sRptURL = &sDistNodeURL | "/" | &RptInstance | "/" | &sFileName;
WriteToLog(%ApplicationLogFence_Level1, "distnode URL = " | &sDistNodeURL | " report URL : " | &sRptURL);
/* WinMessage("distnode URL = " | &sDistNodeURL | " report URL : " | &sRptURL);*/

Else
WriteToLog(%ApplicationLogFence_Error, "*** Warning! Report could not be posted");
End-If;

end-method;

method getRuntimeDistribution
/+ &processInstanceId as Number, +/
/+ &oRptViewerArray2 as Array of PSXP_RPTDEFNMANAGER:ReportViewer +/
/+ Returns Array of PSXP_RPTDEFNMANAGER:ReportViewer +/
Local PSXP_RPTDEFNMANAGER:ReportViewer &oRptViewer;
Local SQL &sqlDist;
Local string &distIdType, &distId, &sUserType;

&sqlDist = CreateSQL("SELECT DISTIDTYPE, DISTID FROM PS_PRCSRQSTDIST WHERE PRCSINSTANCE = :1", &processInstanceId);
While &sqlDist.Fetch(&distIdType, &distId);
If &distIdType = "2" Then
&sUserType = "USER";
Else
&sUserType = "ROLE";
End-If;
&oRptViewer = create PSXP_RPTDEFNMANAGER:ReportViewer(&sUserType, &distId);
WriteToLog(%ApplicationLogFence_Level1, "Distribution : IdType = " | &sUserType | " Id = " | &distId);

If (&oRptViewerArray2 = Null) Or
(&oRptViewerArray2.Len = 0) Then
&oRptViewerArray2 = CreateArray(&oRptViewer);
Else
&oRptViewerArray2.Push(&oRptViewer);
End-If;

End-While;
&sqlDist.Close();

Return &oRptViewerArray2;

end-method;

method writeOutputFile
/+ &strFileName as String, +/
/+ &strFileType as String +/
/+ Returns String +/
Local File &oFile, &oOutputDataXMLFile;
Local string &sFilePath, &sInput;

&sFilePath = &strFileName | &strFileType;
&oFile = GetFile(&sOutputFile, "R", %FilePath_Absolute);
If &oFile.IsOpen Then
&oOutputDataXMLFile = GetFile(&sFilePath, "W", "UTF8", %FilePath_Absolute);
If &oOutputDataXMLFile.IsOpen Then
&sInput = &oFile.GetString( True);
&oOutputDataXMLFile.WriteLine(&sInput);
&oOutputDataXMLFile.Close();
End-If;
&oFile.Close();
End-If;

Return &sFilePath;
end-method;

method deleteDataXML

Local string &sOutDest, &sXmlFile;
Local integer &i;
Local File &oFile;

&oFile = GetFile(&sOutputFile, "R", %FilePath_Absolute);
If &oFile.IsOpen Then
&oFile.Delete();
WriteToLog(%ApplicationLogFence_Level1, "Deleted Data file: " | &sOutputFile);
End-If;
RemoveDirectory(&sOutputDir, %FilePath_Absolute + %Remove_Subtree);
end-method;

5. You have to create your *.rtf template and place all the Data Source (XML File) within XMLP.
6. Don't forget the file layout within your AE.
7. Reports will show up within XMLP Report Manager

Update Trace definitions for better performance in PRD

2008-04-17T09:54:00.000-07:00

-- Statement to select current date/time

SELECT to_char(sysdate,'mm/dd/yyyy hh24:mi:ss') from dual;

-- Statement to select current DB

SELECT name from v$database;

--Select all process definitions that have a -TRACE flag
select * from PS_PRCSDEFN
WHERE PARMLIST LIKE '-TRACE%';

--Remove Trace Flags on Process Definitions

update ps_prcsdefn
set parmlist = ' ',parmlisttype = '0'
WHERE PARMLIST LIKE '-TRACE%';

commit;

Audits for Projects and Records - FInd null or blank descrs

2008-04-15T12:37:00.002-07:00

Here is some sample SQL to find blank descrs within projects and records...etc. There should be some useful names to custom objects.

select
recname,
recdescr,
descrlong
from psrecdefn
where recname LIKE '%ASU%' -- This would be your custom naming convention
and recdescr in (' ','')

select
projectname,
projectdescr,
lastupddttm,
lastupdoprid,
descrlong
from PSPROJECTDEFN
where projectname like '%ASU%' --This would be your custom naming convention
and projectdescr in (' ', '')

Sample Pell and FA Disbursements query

2008-04-14T15:24:00.002-07:00

Here is a good sample for FA disbursements: (Pell, Perkins...etc)

SELECT
AA.NATIONAL_ID,
',',
A.EMPLID,
',',
S.NAME,
',',
F.CAMPUS_FA,
',',
D.FEDERAL_ID,
',',
B.DISBURSED_BALANCE,
',',
SUM(B.DISBURSED_BALANCE)
FROM PS_PERS_NID AA,
PS_STDNT_AWARDS A,
PS_STDNT_AWRD_DISB B,
PS_DISB_ID_TBL C,
PS_ITEM_TYPE_FA D,
PS_STDNT_AWRD_ACTV E,
PS_STDNT_FA_TERM F,
PS_NAMES S
WHERE
A.EMPLID = AA.EMPLID
AND A.EMPLID = S.EMPLID
AND B.DISBURSED_BALANCE > 0
AND B.EMPLID = A.EMPLID
AND B.INSTITUTION = A.INSTITUTION
AND B.ITEM_TYPE = A.ITEM_TYPE
AND B.ACAD_CAREER = A.ACAD_CAREER
AND C.INSTITUTION = B.INSTITUTION
AND C.AID_YEAR = B.AID_YEAR
AND C.ACAD_CAREER = B.ACAD_CAREER
AND C.DISBURSEMENT_PLAN = A.DISBURSEMENT_PLAN
AND C.DISBURSEMENT_ID = B.DISBURSEMENT_ID
AND D.SETID = A.SETID
AND D.ITEM_TYPE = A.ITEM_TYPE
AND D.AID_YEAR = A.AID_YEAR
AND D.EFFDT =
(SELECT MAX(D1.EFFDT)
FROM PS_ITEM_TYPE_FA D1
WHERE D1.SETID = D.SETID
AND D1.ITEM_TYPE = D.ITEM_TYPE
AND D1.AID_YEAR = D.AID_YEAR
AND D1.EFFDT <= SYSDATE
)
AND D.DISBURSE_METHOD = 'A'
AND D.EFF_STATUS = 'A'
AND D.FA_SOURCE = 'F'
AND D.AGGREGATE_AREA IN ('FSEOG','PELL','PERKINS')
AND E.EMPLID = B.EMPLID
AND E.INSTITUTION = B.INSTITUTION
AND E.AID_YEAR = B.AID_YEAR
AND E.ITEM_TYPE = B.ITEM_TYPE
AND E.ACAD_CAREER = B.ACAD_CAREER
AND E.DISBURSEMENT_ID = B.DISBURSEMENT_ID
AND E.AWARD_DISB_ACTION = 'P'
AND E.DISB_AMOUNT > 0
AND E.ACTION_DTTM =
(SELECT MAX(E1.ACTION_DTTM)
FROM PS_STDNT_AWRD_ACTV E1
WHERE E1.EMPLID = E.EMPLID
AND E1.INSTITUTION = E.INSTITUTION
AND E1.AID_YEAR = E.AID_YEAR
AND E1.ITEM_TYPE = E.ITEM_TYPE
AND E1.ACAD_CAREER = E.ACAD_CAREER
AND E1.DISBURSEMENT_ID = E.DISBURSEMENT_ID
AND TRUNC(E1.ACTION_DTTM) <= SYSDATE
)
AND TRUNC(E.ACTION_DTTM) BETWEEN TO_DATE('01-JAN-2007','DD-MON-YYYY') AND TO_DATE('10-JAN-2008','DD-MON-YYYY')
AND F.EMPLID = A.EMPLID
AND F.INSTITUTION = A.INSTITUTION
AND F.STRM = C.STRM
AND F.AID_YEAR = A.AID_YEAR
AND F.EFFDT =
(SELECT MAX(F1.EFFDT)
FROM PS_STDNT_FA_TERM F1
WHERE F1.EMPLID = F.EMPLID
AND F1.INSTITUTION = F.INSTITUTION
AND F1.AID_YEAR = F.AID_YEAR
AND F1.STRM = F.STRM
AND F1.EFFDT <= SYSDATE
)
AND F.EFFSEQ =
(SELECT MAX(C1.EFFSEQ)
FROM PS_STDNT_FA_TERM C1
WHERE C1.EMPLID = F.EMPLID
AND C1.INSTITUTION = F.INSTITUTION
AND C1.AID_YEAR = F.AID_YEAR
AND C1.STRM = F.STRM
AND C1.EFFDT = F.EFFDT)
AND ROWNUM < 100
GROUP BY AA.NATIONAL_ID, A.EMPLID, S.NAME, F.CAMPUS_FA, D.FEDERAL_ID, B.DISBURSED_BALANCE
ORDER BY AA.NATIONAL_ID, A.EMPLID, S.NAME, D.FEDERAL_ID

Decode Peoplecode - decodepc.java

2008-03-05T23:57:00.001-07:00

decodePC.sqr as written by David L. Price has been rewritten to decodePC.java and expanded. It's general purpose is decoding PeopleCode stored as BLOB. The PeopleSoft database is accessed via a JDBC connection therefore there is no need to use Application Designer to look at PeopleCode, Application Engine programs, SQL, and XSLT. decodePC.java can read Application Engine programs and Application Packages and output them to a text file so you can get on with solving business problems.

Example of compiling decodePC.java with Oracle JDBC on a Unix box from a command prompt in the directory where decodePC.java exists:

javac -classpath /directorypath/ojdbc14.jar ./decodePC.java

Example of running decodePC.class with Oracle JDBC on a Unix box from a command prompt in the directory where decodePC.class exists:

java -classpath .:/directorypath/ojdbc14.jar decodePC

Download the code here from the site!

Oracle Disk Reads Script / Oracle Tuning Efforts

2008-02-25T10:04:00.002-07:00

Here are some good scripts for Oracle tuning.

REM This SQL script will produce a current listing of
REM suspect SQL statements based on a specified
REM level of disk reads against the database instance.

SELECT DISK_READS, SQL_TEXT
FROM V$SQLAREA
WHERE DISK_READS >= &DISK_READS
ORDER BY DISK_READS DESC
/

REM This SQL script will display all current terminal
REM sessions attached to a specific database instance.

spool c:\temp\session.lis

SELECT a.sid sessionno,
substr(a.username,1,10) user_name,
substr(a.schemaname,1,10) schema,
substr(a.osuser,1,15) client_user,
substr(a.client_info,1,25) dbms_client_info,
substr(a.machine,1,15) wk_station,
substr(a.program,1,31) program_name,
a.status,
to_char(a.logon_time,'DD-MON-YYYY HH24:MM:SS') logontime,
b.name command_action
FROM v$session a, sys.audit_actions b
WHERE a.command = b.action
/

spool off

REM This SQL script will produce a current listing of
REM column statistics for a specific table.

col num_distinct format 999,999,999
col num_nulls format 999,999,999

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, DENSITY
FROM DBA_TAB_COL_STATISTICS
WHERE TABLE_NAME = upper('&TABLE_NAME')
ORDER BY COLUMN_NAME
/

REM This SQL script will change the SQL prompt to
REM refer to the instance being used at the time.
REM
REM This will eliminate the need to execute the
REM statement:
REM
REM SELECT name from v$database;

set termout off

column x new_value y

SELECT rtrim(instance, chr(0)) x
FROM v$thread
/

set sqlprompt '&Y>'
set termout on

REM
REM Encountering: SP2-0110: Cannot create save file "afiedt.buf"
REM
REM The user may not have permission to edit in that
REM specific directory.
REM

set editfile C:\TEMP\AFIEDT.BUF

REM
REM Set line and page size lengths
REM

set linesize 100
set pagesize 80

Peoplesoft Tools Tables

2008-01-28T00:14:00.000-07:00

PSST0101 blog site had started listing out the tools tables. I wanted to post this listing on my blog as well. I have also provided the link to the orginal blog.

Enjoy

Projects

PSPROJECTDEFN — Project header table
PSPROJECTITEM — Definitions in the project
Fields

PSDBFIELD — Fields in the system
PSXLATITEM — Translate Values
Records

PSRECDEFN — Record header table
PSRECFIELD — Fields in the record (subrecords not expanded)
PSRECFIELDALL — Fields in the record (subrecords expanded)
PSKEYDEFN — Indexes
Pages

(Note: Pages still have the name panels in the PeopleTools table names)

PSPNLDEFN — Page header table
PSPNLFIELD — Page controls
PSPNLHTMLAREA — Static HTML Areas on Pages
Components

(Note: Components still have the name panel group in the PeopleTools table names)

PSPNLGRPDEFN — Component header table
PSPNLGROUP — Pages in the components
Menus

PSMENUDEFN — Menu header table
PSMENUITEM — Items (components) on the menu
Security

PSCLASSDEFN — Permission List header table
PSAUTHITEM — Menu items granted security by permission lists
PSROLEDEFN — Role header table
PSROLECLASS — Permission Lists in roles
PSOPERDEFN — User ID header table
PSROLEUSER — Roles granted to users
Portal

PSPRSMDEFN — Content References and Folders
Change Control

PSCHGCTLHIST — shows history of locked definitions with project name, incident, and description
PSCHGCTLLOCK — shows definitions that are currently locked
Application Engine

PSAEAPPLDEFN — header record; 1 row per app engine
PSAEAPPLSTATE — state records assigned to app engines
PSAEAPPLTEMPTBL — temp tables assigned to app engines
PSAESECTDEFN — sections
PSAESTEPDEFN — steps
PSAESTEPMSGDEFN
PSAESTMTDEFN — actions
HTML Definitions

PSCONTDEFN — header record; last update time, etc.
PSCONTENT — stores actual text in the HTML definition

Excellent Backup tool for source code

2008-01-27T23:43:00.000-07:00

Here is a great site and software tool to backup source code. You can specify only certain file extensions *.sqr, *.sql, *.js etc. With this backup tool, you can also schedule backups to copy only the changes to the network share or jump drive.

This site is developed by a colleague of mine and his software is top shelf.
http://www.glaciermicrosystems.com/products/products.html

PS Queries / a user can not see / Security

2008-01-24T16:14:00.000-07:00

This query was developed to capture a listing of queries that a user does not have access to.

SELECT DISTINCT A.OPRID, A.QRYNAME, A.DESCR, B.RECNAME
FROM PSQRYDEFN A, PSQRYRECORD B
WHERE A.OPRID = B.OPRID AND
A.QRYNAME = B.QRYNAME AND
A.QRYTYPE = 1
and not exists (select 'x' from PSROLECLASS D , PSROLEUSER E , PSQRYACCLSTRECS F
WHERE D.ROLENAME = E.ROLENAME AND
F.CLASSID = D.CLASSID AND
F.VERSION = ( SELECT VERSION FROM PSVERSION D WHERE D.OBJECTTYPENAME = 'QAL') AND
E.ROLEUSER = A.OPRID and
f.recname = b.recname)
and a.oprid = 'Place OPRID here'

PS Listing of Payroll / HR tables

2008-01-23T14:24:00.000-07:00

Here is a great sample SQL for capturing PS Payroll and HR tables within PS. This SQL was provided by my colleague Roger Davies.

select a.RECNAME, decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename) table_name, a.rectype, a.OBJECTOWNERID, xlat.xlatlongname, num_rows
from psrecdefn a, psxlatitem xlat, all_tables b
where a.objectownerid in ('AWFA', 'FGL', 'HBA', 'HBN', 'HEB', 'HER', 'HFSA', 'HHP', 'HHR', 'HMCF', 'HPY', 'HRAM', 'HRAT', 'HSP', 'HTC', 'HTL', 'HTLA', 'HTLI', 'HTLR', 'HTLX', 'HRAM','HRAT','HSP' )
and xlat.fieldname = 'OBJECTOWNERID'
and xlat.fieldvalue = a.OBJECTOWNERID
and xlat.effdt = (select max(xlat1.effdt) from psxlatitem xlat1
where xlat1.fieldname = xlat.fieldname
and xlat1.fieldvalue = xlat.fieldvalue)
and b.table_name = decode(a.SQLTABLENAME, ' ', 'PS_' || a.recname, a.sqltablename)
and b.NUM_ROWS > 0

How to Trace your APP Engine

2007-12-27T15:09:00.001-07:00

AE Process Definition - Override options tab

-TRACE 7 -TOOLSTRACEPC 3596 -TOOLSTRACESQL 131 (review peoplebooks on which tracing values you need to place)

PSACCESLOG - Review users who login

2007-12-27T14:42:00.000-07:00

Here are some queries to detail who has logged into the Peoplesoft system (Web Tier)

-- Unique IP Address Count, per hour, for the last 24 hours
-- Change the "1" to be "7" for seven days of activity
select DTTM, count(*)
from (select distinct a.logipaddress "IP",
to_char(a.logindttm,'MM/DD/YYYY HH24') "DTTM"
from psaccesslog a
where a.logindttm > sysdate - 1)
group by DTTM;

-- Find the IP Activity for a individual, for the last 24 hours
-- Change the "1" to be "7" for seven days of activity
select a.*, b.oprdefndesc from psaccesslog a, psoprdefn b
where a.logindttm > sysdate - 7 -- This indicates today and the past seven days...
and a.oprid = b.oprid
and b.oprdefndesc like '%Smith%'
ORDER BY A.LOGINDTTM DESC;

How to Default sysdate (Current Date) for Crystal Report and PeopleTools Query prompt automatically

2007-12-27T14:03:00.001-07:00

I found a great blog entry about defaulting query prompts from Keton K. I wanted to post on here as well.

If you have a Query or Crystal Report, that has a date prompt and you want to schedule the report daily, so that date value is defaulted to sysdate (Current date), you can do this as follows.
Open the Query in PeopleTools Query tool.
Go to Criteria tab. Right Click on Expression2 Column and Select Expression or Expr-Expr if you are using between operator.

Type the following in Edit Expression.

current date
decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate),:1)

current date - 1
decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)-1,:1)

current date + 1
decode(:1,TO_DATE('1900-01-01','YYYY-MM-DD'),trunc(sysdate)+1,:1)

replace :1 with the actual prompt value. You must first create this prompt.
Now you need to pass 01/01/1900 as an input parameter if you want to run the query for current date. Passing any other values will make the query run for that date.
This way you can achieve both i.e. run the query for a user selected date or run a query for current date which can be used to schedule. Note : you can use any date as a replacement for sysdate and not just 01/01/1900.
Also you can default it to any day relative to current date for e.g. trunc(sysdate) - 1 or trunc(sysdate) + 1 etc.
If you want to know what parameter user has passed in your report, you can add the following expression as field in your query.
Create an expression of type Date in left hand side Under Expressions.

Current Date
decode(:1,'1900-01-01',to_char(trunc(sysdate),'YYYY-MM-DD') ,:1)

Current Date - 1
decode(:1,'1900-01-01',to_char((trunc(sysdate)-1),'YYYY-MM-DD') ,:1)

Current Date + 1
decode(:1,'1900-01-01',to_char((trunc(sysdate)+1),'YYYY-MM-DD') ,:1)

Objects within Peoplesoft Projects

2007-12-18T16:17:00.002-07:00

This SQL was given to me by a great colleague - Jan Jerabek. This SQL will pull all the objects out of your project for cut and paste efforts :-)

select decode(OBJECTTYPE, '0','Record', '1','Index', '2','Field', '3','Field Format', '4','Translate Value', '5','Pages', '6','Menus', '7','Components', '8','PeopleCode Record PeopleCode', '9','PeopleCode Menu PeopleCode', '10','Query', '11','Tree Structures', '12','Trees', '13','Access group', '14','Color', '15','Style', '16','Not used', '17','Business process', '18','Activity', '19','Role', '20','Process Definition', '21','Server Definition', '22','Process Type Definition', '23','Job Definitions', '24','Recurrence Definition', '25','Message Catalog Entries', '26','Dimension', '27','Cube Definitions', '28','Cube Instance Definitions', '29','Business Interlink', '30','SQL', '31','File Layout Definition', '32','Component Interfaces', '33','Application Engine program', '34','Application Engine section', '35','Message Node', '36','Message Channel', '37','Message', '38','Approval rule set', '39','PeopleCode Message PeopleCode', '40','PeopleCode Subscription PeopleCode', '41','Not Used', '42','PeopleCode Component Interface PeopleCode', '43','PeopleCode Application Engine PeopleCode', '44','PeopleCode Page PeopleCode', '45','PeopleCode Page Field PeopleCode', '46','PeopleCode Component PeopleCode', '47','PeopleCode Component Record PeopleCode', '48','PeopleCode Component Rec Fld PeopleCode', '49','Image', '50','Style sheet', '51','HTML', '52','Not used', '53','Permission List', '54','Portal Registry Definitions', '55','Portal Registry Structures', '56', 'URL Definitions', '57', 'Application Packages', '58', 'PeopleCode Application Package Peoplecode', '59', 'Portal Registry User Homepage', '60', 'Problem Type', '61', 'Archive Templates', '62', 'XSLT', '63', 'Portal Registry User Favorite', '64', 'Mobile Page', '65', 'Relationships', '66', 'PeopleCode Component Interface Property Peoplecode', '67', 'Optimization Models', '68', 'File References', '69', 'File Type Codes', '70', 'Archive Object Definitions', '71', 'Archive Templates (Type 2)', '72', 'Diagnostic Plug In', '73', 'Analytic Model','UNKNOWN','80','Service Operations' ,'81' ,'Service Operation Handlers','82','Service Operation Versions','83','Service Operation Routings',
'84','IB Queues','85','XMLP Template Definition','86',
'XMLP Report Definition' ,'87','XMLP File Definition','88',
'XMLP Data Source Definition') OBJECTTYPE, '', OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3, OBJECTVALUE4 from PSPROJECTITEM where PROJECTNAME = 'Project name goes here' order by objecttype, OBJECTVALUE1, OBJECTVALUE2, OBJECTVALUE3

Years of Service / Salary Query - Reports_to

2007-12-18T16:11:00.000-07:00

Here is a sql/report that I needed to write to track years of service and salary info. (Set the values in red)

select distinct a.reports_to, a.emplid, b.name, (a.comprate * 26), a.grade, c.min_rt_annual, c.mid_rt_annual, c.max_rt_annual, a.sal_admin_plan ,E.HIRE_DT,
TO_CHAR(((SYSDATE - P.BIRTHDATE) / 365), 99) AGE
,TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) TOTAL_YEARS_WORKED
,TO_CHAR((SYSDATE - E.HIRE_DT),999999) TOTAL_DAYS_WORKED
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 5 THEN '5 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - 1825) * -1), 99999) END) TOTAL_DAYS_UNTIL_5_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 10 THEN '10 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 2)) * -1), 999999) END) TOTAL_DAYS_UNTIL_10_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 15 THEN '15 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 3)) * -1), 999999) END) TOTAL_DAYS_UNTIL_15_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 20 THEN '20 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 4)) * -1), 999999) END) TOTAL_DAYS_UNTIL_20_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 25 THEN '25 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 5)) * -1), 999999) END) TOTAL_DAYS_UNTIL_25_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 30 THEN '30 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 6)) * -1), 999999) END) TOTAL_DAYS_UNTIL_30_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 35 THEN '35 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 7)) * -1), 999999) END) TOTAL_DAYS_UNTIL_35_YRS_SERV
,(CASE WHEN TO_CHAR(((SYSDATE - E.HIRE_DT) / 365),99.99) >= 40 THEN '40 Years of Service Obtained' ELSE TO_CHAR((((SYSDATE - E.HIRE_DT) - (1825 * 8)) * -1), 999999) END) TOTAL_DAYS_UNTIL_40_YRS_SERV
from ps_job a, ps_names b, ps_sal_grade_tbl c, ps_employment e, ps_person p
where a.reports_to in ('&reports_to')
and a.emplid = b.emplid
and a.emplid = e.emplid
and a.emplid = p.emplid
and b.name_type = 'PRI'
and b.effdt = (select max(b1.effdt) from ps_names b1
where b1.emplid = b.emplid
and b1.name_type = 'PRI'
and b1.effdt <= sysdate)
and c.setid = '&setid'
and c.grade = a.grade
and c.sal_admin_plan = a.sal_admin_plan
and c.effdt = (select max(c1.effdt) from ps_sal_grade_tbl c1
where c1.setid = c.setid
and c1.grade = c.grade
and c1.sal_admin_plan = c.sal_admin_plan
and c1.effdt <= sysdate)
and a.effdt = (select max(a1.effdt) from ps_job a1
where a1.emplid = a.emplid
and a1.empl_rcd = a.empl_rcd
and a1.effseq = a.effseq
and a1.effdt <= sysdate)
and a.effseq = (select max(a2.effseq ) from ps_job a2
where a2.emplid = a.emplid
and a2.empl_rcd = a.empl_rcd
and a2.effdt = a.effdt )
order by a.reports_to, total_years_worked desc, b.name

Finding Menu Path

2007-12-11T12:50:00.001-07:00

Here are some good SQL's to have in your library.

When a process name is known:

SELECT DISTINCT
PRCS.PRCSTYPE,
PRCS.PRCSNAME,
PRCS.DESCR,
PAGE.PNLGRPNAME as Component,
'Home > ' || RTRIM(M.MENUGROUP) || ' > ' || RTRIM(M.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as Location
FROM PSMENUDEFN M,
PSMENUITEM ITEM,
PS_PRCSDEFNPNL PAGE,
PS_PRCSDEFN PRCS
WHERE M.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PRCS.PRCSNAME = 'PY_PULL_COST'

When a component name is known:

SELECT DISTINCT PRCS.PRCSTYPE , PRCS.PRCSNAME , PRCS.DESCR ,
PAGE.PNLGRPNAME as Component , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE , PS_PRCSDEFN PRCS
WHERE MENU.MENUNAME = ITEM.MENUNAMEAND ITEM.PNLGRPNAME = PAGE.PNLGRPNAME
AND PAGE.PRCSTYPE = PRCS.PRCSTYPE
AND PAGE.PRCSNAME = PRCS.PRCSNAME
AND PAGE.PNLGRPNAME = 'component-name'

When a record name is known:

SELECT DISTINCT PFLD.RECNAME ,
PFLD.PNLNAME as Page , 'Home > ' || RTRIM(MENU.MENUGROUP) || ' > ' ||
RTRIM(MENU.MENULABEL) || ' > ' || RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP , PSPNLFIELD PFLD
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = PFLD.PNLNAME
AND PFLD.RECNAME = 'record-name'

When a page name is known:

SELECT DISTINCT COMP.PNLNAME as Page , 'Home > ' ||
RTRIM(MENU.MENUGROUP) || ' > ' || RTRIM(MENU.MENULABEL) || ' > ' ||
RTRIM(ITEM.BARLABEL) || ' > ' || ITEM.ITEMLABEL as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP
WHERE MENU.MENUNAME = ITEM.MENUNAME
AND ITEM.PNLGRPNAME = COMP.PNLGRPNAME
AND COMP.PNLNAME = 'page-name'

Peoplecode to Read and Write a File - Send email within Peoplecode using SendMail() function

2007-11-19T16:29:00.000-07:00

Here is some sample code to read and write a file. You will have to design your code to handle your environment paths...

/*** Read a file ***/

Local File &ASU_ADDR_WRK;
Local Rowset &rsInput_Rowset;
Local Record &Rec1;
Local SQL &SQL1;
&Rec1 = CreateRecord(Record.ASU_ADDR_WRK);
&SQL1 = CreateSQL("%Insert(:1)");
If ASU_ADDR_AET.PROCESS_INSTANCE > 0 Then
&pshome = GetEnv("PS_HOME");
&InboundDirectory = &pshome "/datafiles/interfacein/";
&Filename = &InboundDirectory “asu_addr.csv";
End-If;
If FileExists(&Filename, %FilePath_Absolute) Then
&ASU_ADDR_WRK = GetFile(&Filename, "R", "A", %FilePath_Absolute);
End-If;
&ASU_ADDR_WRK.SetFileLayout(FileLayout.ASU_ADDR_WRK);
&rsInput_Rowset = CreateRowset(Record.ASU_ADDR_WRK);
&rsInput_Rowset = &ASU_ADDR_WRK.ReadRowset();
While &rsInput_Rowset <> Null
&rsInput_Rowset.GetRow(1).ASU_ADDR_WRK.CopyFieldsTo(&Rec1);
&SQL1.Execute(&Rec1);
&rsInput_Rowset = &ASU_ADDR_WRK.ReadRowset();
End-While;
&ASU_ADDR_WRK.Close();

/*** Write to a file ***/

Local File &ASU_ADDR_DUP;
Local Record &Rec3;
Local SQL &SQL3;
&Rec3 = CreateRecord(Record.ASU_ADDR_DUP);
&SQL3 = CreateSQL("%SelectAll (:1) ORDER BY EMPLID, ADDRESS_TYPE", &Rec3);
If ASU_ADDR_AET.PROCESS_INSTANCE > 0 Then
&pshome = GetEnv("PS_HOME");
&OutboundDirectory = &pshome "/ datafiles/interfaceout/"; ";
&FileName2 = &OutboundDirectory “asu_addr_dup.csv";
End-If;
&ASU_ADDR_DUP = GetFile(&FileName2, "W", "A", %FilePath_Absolute);
If &ASU_ADDR_DUP.IsOpen Then
If &ASU_ADDR_DUP.SetFileLayout(FileLayout.ASU_ADDR_DUP) Then;
While &SQL3.Fetch(&Rec3)
&ASU_ADDR_DUP.WriteRecord(&Rec3);
End-While;
End-If;
End-If;
&ASU_ADDR_DUP.Close();

/*** Send and email ***/

&MAIL_FLAGS = 0;
&MAIL_TO = “Jeromy.McMahon@asu.edu";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = “ASU Application Engine Training Class";
&MAIL_TEXT = "See attachment for the file created in AE class";
&pshome = GetEnv("PS_HOME");
&pshome "/datafiles/interfaceout/";
&MAIL_FILES = &pshome "asu_addr_dup.csv";
&MAIL_TITLES = “asu_addr_dup.csv";
&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES);

Find Employees who are terminated and not locked out of the system

2007-11-19T16:19:00.000-07:00

Here is a rough query that will display all the employees who have a max termed row and are not locked out of the system.

SELECT DISTINCT A.OPRID, A.EMPLID, C.ALTER_EMPLID, A.ACCTLOCK, B.NAME, A.LASTUPDDTTM, A.LASTPSWDCHANGE FROM PSOPRDEFN A, PS_NAMES B, PS_PERS_DATA_EFFDT C, PS_JOB JOBWHERE A.ACCTLOCK <> 1 AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND JOB.EMPLID = A.EMPLID AND JOB.EFFDT = (SELECT MAX(JOB1.EFFDT) FROM PS_JOB JOB1 WHERE JOB1.EMPLID = JOB.EMPLID AND JOB1.EFFSEQ = JOB.EFFSEQ AND JOB1.EFFDT <= SYSDATE) AND JOB.EFFSEQ = (SELECT MAX(JOB2.EFFSEQ) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.EFFDT = JOB.EFFDT) AND JOB.ACTION = 'TER'

Updating the PS_PAY_CALENDAR

2007-11-19T16:02:00.000-07:00

Here is some sample code that will set the update flags for PS_PAY_CALENDAR.

update ps_pay_calendar
set PAY_SHEETS_RUN = 'Y', PAY_PRECALC_RUN = 'Y', PAY_CALC_RUN = 'Y', PAY_CONFIRM_START = 'Y', PAY_CONFIRM_RUN = 'Y', SINGLE_CHECK = 'Y', PAY_OFF_CYCLE_CLS = 'Y'
where run_id = '20070805' -- You will have to replace with your specific run_id

update ps_pay_calendar
set PAY_CONFIRM_START = 'Y', PAY_CONFIRM_RUN = 'Y'
where run_id = '20070729'

DBA Extents Segments - Sample SQL

2007-11-19T15:56:00.000-07:00

select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocksfrom sys.v_$session s, sys.v_$sort_usage u where s.saddr = u.session_addr

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name

select s.osuser, s.process, s.username, s.serial#, sum(u.blocks)*vp.value/1024 sort_sizefrom sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter vpwhere s.saddr = u.session_addr and vp.name = 'db_block_size' --and s.osuser like '&1'group by s.osuser, s.process, s.username, s.serial#, vp.value

-- Free Table Space

SELECT /* + RULE */ df.tablespace_name tspace,df.bytes/(1024*1024) tot_ts_size,sum(fs.bytes)/(1024*1024) free_ts_size,nvl(round(sum(fs.bytes)*100/df.bytes),1) ts_pct,round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1 FROM dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) dfWHERE fs.tablespace_name(+) = df.tablespace_name GROUP BY df.tablespace_name, df.bytesunion all

SELECT /* + RULE */ df.tablespace_name tspace, fs.bytes/(1024*1024) tot_ts_size, sum(df.bytes_free)/(1024*1024) free_ts_size, nvl(round((sum(fs.bytes)- df.bytes_used) *100/fs.bytes),1) ts_pct, round((sum(fs.BYTES) - df.BYTES_free )*100/fs.bytes) ts_pct1FROM dba_temp_files fs , (select tablespace_name, bytes_free, bytes_used from V$temp_space_header group by tablespace_name, bytes_free, bytes_used ) dfWHERE fs.tablespace_name(+) = df.tablespace_nameGROUP BY df.tablespace_name, fs.bytes, df.bytes_free,df.BYTES_usedorder by 4 desc

--Table spaces

SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_freeFROM v$sort_segment A, ( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total

--Rows for each statement using sort segment space:

SELECT S.sid ',' S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.sqladdr address, Q.hash_value, Q.sql_textFROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS WHERE T.session_addr = S.saddr AND T.sqladdr = Q.address (+)AND T.tablespace = TBS.tablespace_name ORDER BY S.sid

Peoplesoft perfomance

2007-11-17T23:37:00.000-07:00

Monitoring Tools - Oracle Database

Written by David Vandiver
(Topic formerly known as "Active Sessions with SQL Statements")
This is a collection of SQL statements that you can run to see activity and performance on your PeopleSoft system.
You will need read permission to the following tables:
v$session
psoprdefn
v$sqlarea
v$sql_cursor
v$sql_bind_data
v%sql

The first SQL gives you active sessions, including the operator's name from the PSOPRDEFN table. There is also a "Kill Statement" built as one of the columns. This allows you to copy and paste the "Kill SQL" column and send it to the dba quickly.
-- Sessions and PSOPRDEFN

select nvl(b.oprdefndesc,'Blank'), a.CLIENT_INFO,a.OSUSER, a.machine, a.PROGRAM,a.LOCKWAIT, a.LOGON_TIME,'ALTER SYSTEM KILL SESSION ''' a.SID ',' a.SERIAL# ''';' "Kill SQL"from v$session a, psoprdefn bwhere status = 'ACTIVE'and a.USERNAME is not nulland substr(a.client_info, 1, 7) = b.oprid (+);

The last three are still being tweaked, but should work.

-- For statistics use:

SELECT * FROM (SELECT hash_value , sum(disk_reads) , sum(buffer_gets) ,sum(rows_processed), sum(buffer_gets)/greatest(sum(rows_processed),1) , sum(executions) , sum(buffer_gets)/greatest(sum(executions), 1) from V$SQL where command_type in (2,3,6,7) group by hash_value order by 5 desc) WHERE rownum <= 10;

--To get the SQL text for the above statistics use the following:

select t.SQL_TEXT from v$sqlarea t where t.HASH_VALUE in (SELECT hash_value FROM (SELECT hash_value , sum(disk_reads) , sum(buffer_gets) ,sum(rows_processed), sum(buffer_gets)/greatest(sum(rows_processed),1) , sum(executions) , sum(buffer_gets)/greatest(sum(executions), 1) from V$SQL where command_type in (2,3,6,7) group by hash_value order by 5 desc) WHERE rownum <= 10);

--To find the top ten run sql’s:

SELECT * FROM (SELECT hash_value,address,substr(sql_text,1,40) sql, buffer_gets, executions, buffer_gets/executions "Gets/Exec", sql_text "Full SQL Text" FROM V$SQLAREA WHERE buffer_gets > 100000 AND executions > 10 ORDER BY buffer_gets DESC)WHERE rownum <= 10;

Hidden Folders and Content References

2007-11-17T23:22:00.000-07:00

I wanted to post this on my blog as well. This was orginally posted by peoplesoftexperts.blogspot.com

If you do not see a folder or content Reference (Menu Item) in Left hand side navigation, (Applicable to 8.4x ) then you must check to see if the folder or content reference is not marked as hidden. Other reasons could be security. Here is a SQL to find out all the objects that are hidden.
To find all the folders which are hidden from Portal Navigation.
select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'F'
select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_labelfrom PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE' and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and b.portal_Reftype = 'F' and a.portal_name = b.portal_name and a.portal_objname = b.portal_objnameand b.portal_name = c.portal_nameand b.portal_prntobjname = c.portal_objname
To find all the content references which are hidden from Portal Navigation.
select * from PSPRSMSYSATTRVL where portal_name = 'EMPLOYEE' and PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and portal_Reftype = 'C'

select a.portal_objname,b.portal_label,b.portal_prntobjname,c.portal_labelfrom PSPRSMSYSATTRVL a,psprsmdefn b,psprsmdefn c where a.portal_name = 'EMPLOYEE' and a.PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and b.portal_Reftype = 'C' and a.portal_name = b.portal_name and a.portal_objname = b.portal_objnameand b.portal_name = c.portal_nameand b.portal_prntobjname = c.portal_objname

Replace EMPLOYEE with Your portal name.
Some of the Other names are.
CUSTOMER
DEMOSITE
EMPLOYEE
MOBILE
PORTAL
PS_SITETEMPLATE
SUPPLIER

Portal Content Reference/folder Attributes are stored in
PSPRSMSYSATTR
PSPRSMSYSATTRVL