Generate File using PeoleCode and email generated file

When there is requirement that they need CSV file and email the generated file to specific user, following sample PeopleCode could help you create such functionality:

Local File &File;
Local SQL &SQL1, &SQL2, &SQL3;

Local string &reportPath;
Local string &FilPath;

/*Check server where process is being executed*/
&PrcsInstace = TEST_STATE_AET.PROCESS_INSTANCE;
SQLExec("SELECT SERVERNAMERQST FROM ps_pmn_prcslist  WHERE  prcsinstance = :1", &PrcsInstace, &Server);

&find = 0;
&find = Find("UNX", &Server);

/* DEFINE FOLDER PATH AS PER SERVER WHERE PROCESS IS BEING EXECUTED*/
If &find = 0 Then
   &reportPath = GetEnv("PS_SERVDIR") | "\files\";
Else
   &reportPath = GetEnv("PS_SERVDIR") | "/files/";
End-If;
&FilPath = &reportPath | "TEST_FILE_" | TEST_STATE_AET.PROCESS_INSTANCE | ".TXT";


&File = GetFile(&FilPath, "W", %FilePath_Absolute);
If &File.IsOpen Then
   &SQL1 = CreateSQL("SELECT RPad(BUSINESS_UNIT,15,' ') %Concat ',  '%Concat RTrim(DATE_FIELD) %Concat '   ,' %Concat LPad(' ',(13-Length(RTrim(TIME_FIELD)))/2,' ')%Concat RTrim(TIME_FIELD) %Concat RPad(' ',Round((13-Length(RTrim(TIME_FIELD)))/2),' ')%Concat ',' %Concat LPad(' ',(18-Length(To_Char(COUNT_LINES)))/2,' ')%Concat To_Char(COUNT_LINES) %Concat RPad(' ',Round((18-Length(To_Char(COUNT_LINES)))/2),' ')    %Concat ',' %Concat LPad(' ',(17-Length(To_Char(GROSS_AMT)))/2,' ') %Concat To_Char(GROSS_AMT) %Concat RPad(' ',Round((17-Length(To_Char(GROSS_AMT)))/2),' ')  %Concat ',' %Concat DESCR15 FROM %table(TEST_RECORD_NAME)              WHERE PROCESS_INSTANCE = :1 ORDER BY BUSINESS_UNIT", &PrcsInstace);

   /*Write Header Line*/
   &Header = "SAMPLE PROGRAM TO PRINT CSV FILE DATE:" | String(%Date);
  
   &File.WriteLine(&Header);
   &File.WriteLine("");
   &File.WriteLine("BUSINESS UNIT  ,DATE          ,TIME         ,TOTAL AMOUNT    ,AMOUNT          ,STATUS");
   &File.WriteLine("---------------,---------------,-------------,------------------,-----------------,-------------");
   While &SQL1.Fetch(&String)
      If RTrim(&String, " ") <> "" Then
         &File.WriteLine(&String);
      End-If;
   End-While;
  
  
Else
   Error ("File not open");
End-If;
&File.Close();

/*Send email to generated file*/
/* Added PeopleCode to send generated Text File*/

Local SQL &SQLOutDest2;
Local string &reportPath;
Local string &FilPath;

&PrcsInstace = TEST_STATE_AET.PROCESS_INSTANCE;
SQLExec("SELECT SERVERNAMERQST FROM ps_pmn_prcslist  WHERE  prcsinstance = :1", &PrcsInstace, &Server);

&find = 0;
&find = Find("UNX", &Server);

If &find = 0 Then
   &reportPath = GetEnv("PS_SERVDIR") | "\files\";
Else
   &reportPath = GetEnv("PS_SERVDIR") | "/files/";
End-If;
&FilPath = &reportPath | "TEST_FILE_" | TEST_STATE_AET.PROCESS_INSTANCE | ".TXT";


/*Get EmailIDs from TEST_ROLE role to sent mail*/
&SQLOutDest2 = CreateSQL("SELECT DISTINCT EMAILID FROM PSROLEUSER R, PSOPRDEFN O   WHERE R.ROLENAME = 'TEST_ROLE' AND R.ROLEUSER = O.OPRID");

/*CHECK FOR DUPLICATE EMAIL IDS*/
While &SQLOutDest2.Fetch(&Email)
   If Find(&Email, &OutDest) = 0 Then
      &OutDest = &OutDest | ";" | &Email;
   End-If;
End-While;

/*Initialize Parameters to sent mail*/
&MAIL_FLAGS = 0;
&MAIL_TO = &OutDest;
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "TEST CSV FILE";
&MAIL_TEXT = "";
&MAIL_FILES = &FilPath;
&MAIL_TITLES = "TEST_FILE_" | TEST_STATE_AET.PROCESS_INSTANCE | ".TXT";

/*Send Mail*/
&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_SENDER);

/*Log Message about status of sending emails*/
If &RET <> 0 Then
   MessageBox(0, "", 0, 0, "Sending Email Failed, Email IDs are  " | &OutDest);
Else
   MessageBox(0, "", 0, 0, "Succesfuly sent Emails to following Email IDs " | &OutDest);
   /*Delete Generated File*/
   &File = GetFile(&FilPath, "W", %FilePath_Absolute);
   &File.delete();
End-If;