Sending Email Using Application Engine (XML Publisher Report as Attachment)

Our understanding of XML Publisher are getting broader. There are a lot that you can do with this tool, I decided to incorporate the email functionality of PeopleSoft with the XML Publisher. How would you like if the batch thatproduced report output last night can be emailed to you automatically? I know a lot of people would love that!

With our knowledge on how to code XMLP report, we will just add codes to our existing PeopleCode in XML Publisher Part 2 that tells the PeopleSoft to send that report to an email. Here's the code (added codes are in bold text).
import PSXP_RPTDEFNMANAGER:*;
import PSXP_XMLGEN:*;
import PT_MCF_MAIL:*;

/*Create an email object by setting individual parameters*/
Local PT_MCF_MAIL:MCFOutboundEmail &eMail = createPT_MCF_MAIL:MCFOutboundEmail();

&sRptDefn = "JOB_DEFN";
&sTemplateId = "JOB_TEMP";
&sLangCode = "";
&dtAsOfDate = %Date;
&sOutputFmt = "PDF";
&RptOutputDir = "c:\temp\" "XMLP";

&ReportDef.OutDestination = &RptOutputDir;

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

/*Create Rowset*/
&rs = CreateRowset(Record.PERSONAl_DATA);

/*Fill Rowset*/&rs.FILL("WHERE FILL.EMPLID LIKE 'EID000%'");

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

/*Create Sample XML File*/
&myXMLFile = &rds.GetXMLData(&rs, "c:\temp\JOB_XSD.xsd");
&f2 = GetFile("c:\temp\JOB_XML.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", JOB_AET.PROCESS_INSTANCE);
&sFileExt = GetFileExtension(&sOutputFormat);


/*Send Mail*/&ToList = "to_user@yahoo.com";&FromList = "from_user@acme.com";&ReplyToList = "from_user@acme.com";
&Subject = "Batch Run Email";
&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 = "Test for XML Email from PeopleSoft";
Local PT_MCF_MAIL:MCFBodyPart &text = createPT_MCF_MAIL:MCFBodyPart();
&text.Text = &plain_text;

Local PT_MCF_MAIL:MCFBodyPart &attach = createPT_MCF_MAIL:MCFBodyPart();&attach.SetAttachmentContent(&RptOutputDir "JOB_DEFN.pdf",%FilePath_Absolute, "JOB_DEFN.pdf", "JOB_DEFN", "", "");

Local PT_MCF_MAIL:MCFMultiPart &mp = createPT_MCF_MAIL:MCFMultiPart();
&mp.AddBodyPart(&text);
&mp.AddBodyPart(&attach);
&eMail.Multipart = ∓

/*Override the default SMTP parameters specified in app server configuration file*/
&eMail.SMTPServer = "smtp.service.acme.com"; /*just an example*/
&eMail.SMTPPort = 25; /*usually this is 25 by default*/

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();

Peoplecode: Writing to file using Write Rowset

Local File &myFile;
Local Rowset &fileRowset1, &fileRowset;
Local Row &fileRow;

&myFile = GetFile("c:\temp\test.txt", "W", %FilePath_Absolute);

If &myFile.SetFileLayout(FileLayout.TEST_FILE_LAYOUT) Then
   &fileRowset = &myFile.CreateRowset();
   &fileRowset = GetLevel0();
   &fileRow = &fileRowset.GetRow(1);
   &fileRowset1 = &fileRow.GetRowset(Scroll.TEST_BU_REC);
   &myFile.WriteRowset(&fileRowset1);
End-If;

******* Comment ********

The code should be written on the key fields
For accessing further levels we have to give full scroll path

****************** to access next levels ********************
Local File &myFile;
Local Rowset &fileRowset1, &fileRowset;
Local Row &fileRow;

&myFile = GetFile("c:\temp\myfiledept.txt", "W", %FilePath_Absolute);

If &myFile.SetFileLayout(FileLayout.TEST_FILE_LAYOUT) Then
   &fileRowset = &myFile.CreateRowset();
   &fileRowset = GetLevel0();
   &fileRow = &fileRowset.GetRow(1);
   &fileRowset1 = &fileRow.GetRowset(Scroll.TEST_BU_REC).GetRow(1).GetRowset(Scroll.TEST_DEPT_REC);
   &myFile.WriteRowset(&fileRowset1);
End-If;

********************** end ************************************

Peoplecode: Writing to file using Write Record

Local Record &RecLine;
Local File &MYFILE;
Local SQL &SQL2;

&MYFILE = GetFile("c:\temp\test.txt", "W", %FilePath_Absolute);


If &MYFILE.SetFileLayout(FileLayout.TEST_FILE_LAYOUT) Then
   &RecLine = CreateRecord(Record.TEST_EMP_REC);
   &SQL2 = CreateSQL("%Selectall(:1)", &RecLine);
   While &SQL2.Fetch(&RecLine)
      &MYFILE.WriteRecord(&RecLine);
   End-While;
End-If;

&MYFILE.Close();

SQLExec : Return: 8015 - Bind value is too long

You get this error in an online page or while running a Application engine program. This error happens when you try to insert more than 254 characters in a long field using sqlexec and do not use %TextIn meta sql.

Resolution

Use %TextIn meta-sql for the bind variable that is used for inserting into a long field. For e.g. %TextIn(:1)

%TextIn is documented in peoplebooks and is mandatory for all insertions/update of LongChar fields using sqlexec for all database platforms.

Here are some resolutions that discusses this issue in Metalink – Oracle support site.

E-AE Application Engine PeopleCode Step with SQLExec Receives Error; return code 8015 "Bind value is too long" [ID 889806.1]

E-PC:"Bind value is too long" Error When Using SQLExec to Insert into Long Char Field [ID 620874.1]

How to use CTRL+J in Google Chrome

Here is the solution, both works for me.
1. First press and hold J then press CTRL and release J.
2. Press CTRL+SHIFT+J Twice
3. Click on the page tab and hold your click, then press CTRL+J

Thanks
Sharath

How to create a Daemon-Only Application Engine Program

A daemon program is an Application Engine program of daemon type that checks for an event.

§         When an event occurs, the daemon will trigger the process to handle the event.

§         Daemons allow processes to be driven based on events rather than a schedule.

§         Events can be internal or external to the PeopleSoft application database.

Example:

Applicaiton Engine program with following requirements can be configured as Daemon-only AE

program.

•      Moniror a Temp Directory
•      Look  for a file to process.
•      if there is a file in the directory, process it.

To Accomplish this, first create an AE program to read and process the fiel. In the AE properties > 

Advanced tab, set the program type to Deamon Only.

Once the program is ready, tie it to a Daemon Group and activate the Daemon on  the server. After 

enabling the PSDAEMON, stop and start the process scheduler.

This configuration will trigger the AE Program, once the file has been placed in the TEMP Directory which 

is programmed in the AE program.

Application Engine program composition.

Application	Section	Step	Action
Application Engine Program	Callable block of logic.	Logical grouping of actions.	Lines of code.
Composed of one or more sections	Composed of one or more steps. Specified by: ·  Market. ·  Effective date. ·  Effective status. ·  Platform.	Composed of one or more actions. Smallest amount of work that can be committed.	Used to: ·  Retrieve or manipulate data. ·  Control program flow.
First section MAIN 12- character name for application	Executed only when called, except for MAIN.	Executed from top to bottom within the section.	Must be unique within step. Executed in specific order.

Multiple Reports in SQR

Multiple Reports feature is very handful  when you need to stream the output to different reports based on some business conditions. DECLARE-REPORT and USE_REPORT are two commands used to accomplish this. Below example generates two reports based on the employee salary. each report will have a separate header.

declare-report rep1
   layout=layout1
end-declare

  declare-report rep2
    layout=layout2
  end-declare
 END-SETUP

 begin-heading 4 for-reports=rep1
  
    print 'Employee Listing Grade B' (1,1) center bold shade
    print '=' (+1,1,80) fill
    print 'EID' (3,1) bold
    print 'EName' (,10) bold
    print 'Salary' (,30) bold
 end-heading

 begin-heading 4 for-reports=rep2

    

    print 'Employee Listing Grade A' (1,1) center bold shade

    print '=' (+1,1,80) fill

    print 'EID' (3,1) bold

    print 'EName' (,10) bold

    print 'Salary' (,30) bold

 end-heading

BEGIN-PROGRAM
BEGIN-SELECT
NS_EID
NS_ENAME
NS_SALARY
    if &ns_salary <35000
      use-report rep1 
    else
      use-report rep2
    end-if
  
    print &NS_EID (+1,1)
    print &NS_ENAME (,10)
    print &NS_SALARY (,30)
FROM PS_NS_EMP_TBL
end-select
end-program