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

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

http://support.oracle.com


CS/HR Split

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

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

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

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

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

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

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

How to Connect to a Oracle/Peoplesoft Database using PHP

Entry from within tnsnames.ora file

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

 This is sample script to connect and execute a query:

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

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

SQL Query to find all Records within a Specific Peoplesoft Component

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

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

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

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

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

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

SELECT A.HRS_JOB_OPENING_ID

, B.HRS_JO_PST_SEQ
, C.HRS_SEQ_NUM
, B.POSTING_TITLE
, C.HRS_JO_PST_OPN_DT "Open Date"
, C.HRS_JO_PST_CLS_DT "Close Date"
, A.HRS_PRM_LOCATION "Location"
, I.DESCRLONG "College/Division"
, P.DESCRLONG "Scope OF Search"
, R.DESCRLONG "Grant Funded Position"
, G.DESCRLONG "Posted Rate OF Pay"
, H.DESCRLONG "Duties
AND Responsibilities"
, N.DESCRLONG "Minimum Qualifications"
, O.DESCRLONG "Desired Qualifications"
, Q.DESCRLONG "Department Statement/Gen Info"
, J.DESCRLONG "Background CHECK Statement"
, K.DESCRLONG "Standard Statement"
, L.DESCRLONG "Close Date"
, M.DESCRLONG "Instructions TO Apply"
FROM PS_HRS_JOB_OPENING A
, PS_HRS_JO_POSTING B
, PS_HRS_JO_PST_DST C
, PS_HRS_SOURCE D LEFT OUTER JOIN PS_HRS_SOURCE_SITE S ON (S.HRS_SOURCE_ID = D.HRS_SOURCE_ID
AND S.EFFDT = D.EFFDT)
, PS_HRS_STS_JO_I E
, PS_HRS_JO_RSEC_VW F
, PS_HRS_JO_PST_DSCR G
, PS_HRS_JO_PST_DSCR H
, PS_HRS_JO_PST_DSCR I
, PS_HRS_JO_PST_DSCR J
, PS_HRS_JO_PST_DSCR K
, PS_HRS_JO_PST_DSCR L
, PS_HRS_JO_PST_DSCR M
, PS_HRS_JO_PST_DSCR N
, PS_HRS_JO_PST_DSCR O
, PS_HRS_JO_PST_DSCR P
, PS_HRS_JO_PST_DSCR Q
, PS_HRS_JO_PST_DSCR R
WHERE A.HRS_JOB_OPENING_ID = B.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = C.HRS_JOB_OPENING_ID
AND B.HRS_JO_PST_SEQ = C.HRS_JO_PST_SEQ
AND C.HRS_JO_PST_TYPE = 'E'
AND (C.HRS_JO_PST_CLS_DT > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
OR C.HRS_JO_PST_CLS_DT IS NULL)
AND C.HRS_JO_PST_OPN_DT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')
AND C.HRS_SOURCE_ID = D.HRS_SOURCE_ID
AND D.HRS_SOURCE_TYPE = '40'
AND A.STATUS_CODE = E.STATUS_CODE
AND E.OPEN_STS_IND = 'Y'
AND A.HRS_JOB_OPENING_ID = F.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = G.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = H.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = I.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = J.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = K.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = L.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = M.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = N.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = O.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = P.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = Q.HRS_JOB_OPENING_ID
AND A.HRS_JOB_OPENING_ID = R.HRS_JOB_OPENING_ID
AND G.HRS_JO_DESCR_TYP = 'P'
AND H.HRS_JO_DESCR_TYP = 'R'
AND I.HRS_JO_DESCR_TYP = 'S'
AND J.HRS_JO_DESCR_TYP = 'L'
AND K.HRS_JO_DESCR_TYP = 'I'
AND L.HRS_JO_DESCR_TYP = 'E'
AND M.HRS_JO_DESCR_TYP = 'C'
AND N.HRS_JO_DESCR_TYP = 'Q'
AND O.HRS_JO_DESCR_TYP = 'J'
AND P.HRS_JO_DESCR_TYP = 'B'
AND Q.HRS_JO_DESCR_TYP = 'D'
AND R.HRS_JO_DESCR_TYP = 'G'
AND D.EFFDT=(
SELECT MAX(EFFDT)
FROM PS_HRS_SOURCE X
WHERE X.HRS_SOURCE_ID=D.HRS_SOURCE_ID
AND X.EFFDT<=TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))
AND D.HRS_SOURCE_STATUS = '10'
AND F.ROWSECCLASS = 'HCDPALL'
 
 
2. Create custom AE, Component, Page, Record...etc
 
3. Create step within AE to write file.  (Here is a sample from my program)
 
/* RSS 2.0 Feed Creation for Job Postings - J.McMahon */

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

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


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

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


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

While &SQL1.Fetch(&Readrec)

&COUNT = &COUNT + 1;

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

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

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

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

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

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

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

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

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

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

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

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

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

 &ITEM = "<title>" | &Readrec.HRS_JOB_OPENING_ID.Value | &tab | &Readrec.POSTING_TITLE.Value | "</title>";
      &PUBDATE = "<pubDate>" | %Datetime | "</pubDate>";
      &LINK_DETAIL = "<link>" | &Link_value_detail | &Readrec.HRS_JOB_OPENING_ID.Value | "</link>";
      &COMMENTS = "<comments></comments>";
      rem &LINK_DETAIL = "<link>" | &Link_value_header | "</link>";
     
      &CATEGORY = "<category>Careers</category>";
     
      /* Had Values Concatenated, but was having issues with the formatting - writting each line */
     
      &DESCR_DETAILS = "<description>" | "<![CDATA[" | "<strong>You can login and apply using:  Username: GUEST  Password: password</strong>" | "<p><strong>College/Division</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &College | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Scope of Search</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Scope | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Grant Funded Position</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Grant | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Posted Rate of Pay</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Posted | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Duties and Responsibilities</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Duties | Char(10) | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Minimum Qualifications</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &MinQ | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Desired Qualifications</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Des | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Department Statement/Gen Info</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Dep_statement | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Background Check Statement</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Background | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Standard Statement</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Standard | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Close Date</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Close_date | "</p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p><strong>Instructions to Apply</strong></p>";
      &DESCR_DETAILS = &DESCR_DETAILS | "<p>" | &Apply | "</p>";
      &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, "&", "&#38;");
     
     
     
      &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&#61;HRS_CE_JOB_DTL&#38;JobOpeningId" | "&#61;";
   Else
      &Link_value_detail = "https://yourvaluegoeshere/psp/" | &Env | "/EMPLOYEE/HRMS/c/HRS_HRAM.HRS_CE.GBL?Page&#61;HRS_CE_JOB_DTL&#38;JobOpeningId" | "&#61;";
   End-If;
   zzz_RC.URL_1 = &Link_value_detail;
  
End-If;

PTF - PeopleSoft Test Framework

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

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

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

Oracle PeopleSoft Enterprise Hosted PeopleBooks

Steps to Turn ON Logging for XMLP Reports for Debugging

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

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

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

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

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

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

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