You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Send Email Option In Peoplecode

1 reply [Last post]
Guest's picture
Guest

Issues with Send email option in Peoplecode..

I am trying to create one update script file and send that file on email as an attachment.

The issue is, multiple emails are getting triggered for each update script.

Below peoplecode I am using with do select, kindly suggest how I can achieve this.

Do Select:

%SelectInit(INTFC_ID,ACTIVITY_ID_2,MIN_INTF_LI_NO,M AX_INTF_LI_NO)
SELECT DISTINCT intfc_id
, ACTIVITY_ID_2
, MIN (INTFC_LINE_NUM)
, MAX(INTFC_LINE_NUM)
FROM PS_INTF_PRJRES_STG
WHERE PC_INTFC_STATUS ='ERR'
AND PC_LOAD_STATUS = 'NEW'
AND analysis_type IN ('PCL', 'PAY')
GROUP BY INTFC_ID,ACTIVITY_ID_2
ORDER BY 1 ;

Peoplecode:

Local string &filename, &file_path, &emailFrom, &emailid, &emailcc;
Local File &fileNew;
Local string &CRLF;

&CRLF = Char(13) | Char(10);

REM Local date &date1;
rem &oprid = INTF_PRJRES_AET.OPRID.Value;
&INTFC_ID = INTF_PRJRES_AET.INTFC_ID.Value;
rem &ACTIVITY_ID_2 = INTF_PRJRES_AET.ACTIVITY_ID_2.Value;
&MIN_INTF = INTF_PRJRES_AET.MIN_INTF_LI_NO.Value;
&MAX_INTF = INTF_PRJRES_AET.MAX_INTF_LI_NO.Value;
rem &BU_OFFICE_LOC = INTF_PRJRES_AET.BU_OFFICE_LOC.Value;

REM &date1 = INTF_PRRES_STG.DATE1.Value;
&oprid = INTF_PRJRES_AET.OPRID.Value;
&date = %Date;

&file_path = "c:\temp\";
&filename = &date | "_Update Script" | ".txt";

MessageBox(0, "", 0, 0, "filename" | &filename);
&file = &file_path | &filename;

&fileNew = GetFile(&file, "W", %FilePath_Absolute);
rem &rec = CreateRecord(Record.CCI_INTFC_STG);

If &fileNew.IsOpen Then

&NUMOFSCRIPTS = 1;
&counter = 0;

&sql = CreateSQL("SELECT DISTINCT E.BUSINESS_UNIT,E.ACTIVITY_ID_2,a.project_id ,D.empnum ,D.OFFICE_LOC ,D.toc ,D.prg , D.emp_name, C.CUST_ID ,E.ACTIVITY_ID FROM sysadm.ps_tc_activity A ,cci_contract_intfc C ,cci_ASSIG_INTFC D , PS_INTF_PRJRES_STG E WHERE c.CONTRACT_ID = (SELECT MAX(B.PROJECT_id) FROM sysadm.ps_tc_activity b WHERE A.ACTIVITY_ID=B.ACTIVITY_ID ) AND D.EMP_NAME = ( SELECT MAX(H.EMP_NAME) FROM cci_ASSIG_INTFC H WHERE H.ASSIG_NUM=D.ASSIG_NUM) AND A.activity_id=D.ASSIG_NUM AND a.PROJECT_ID=c.CONTRACT_ID AND a.activity_id =E.ACTIVITY_ID_2 and e.intfc_id=:1", &INTFC_ID, &BUSINESS_UNIT, &project_id, &ACTIVITY_ID_2, &EMPLID, &BU_OFFICE_LOC, &RESOURCE_SUB_CAT, &PRODUCT, &DESCR, &CUST_ID, &ACTIVITY_ID, &ACTIVITY_ID_2);

While &sql.Fetch(&BUSINESS_UNIT, &ACTIVITY_ID_2, &project_id, &EMPLID, &BU_OFFICE_LOC, &RESOURCE_SUB_CAT, &PRODUCT, &DESCR, &CUST_ID, &ACTIVITY_ID)

MessageBox(0, "", 0, 0, "In While loop ");

&fileNew.WriteLine(" ");
&strng = " Executed below scripts on database ";
&fileNew.WriteLine(&strng);
&fileNew.WriteLine(" ");
&string1 = "update PS_INTFC_PROJ_RES set PROJECT_ID='" | &project_id | "'" | "," | "activity_id ='" | &ACTIVITY_ID_2 | "'" | "," | "EMPLID ='" | &EMPLID | "'" | ",";

MessageBox(0, "", 0, 0, "BU_OFFICE_LOC " | &BU_OFFICE_LOC);
If &BU_OFFICE_LOC = "BOCAR" Then
&string_BU1 = "BUSINESS_UNIT = 'CROSS',BUSINESS_UNIT_GL ='CROSS'" | "," | "BUSINESS_UNIT_BI ='" | &BU_OFFICE_LOC | "'" | "," | "BUSINESS_UNIT_AR ='" | &BU_OFFICE_LOC | "'" | "," | "BUSINESS_UNIT_PC ='" | &BU_OFFICE_LOC | "'" | ",";
&string_BU2 = &string1 | &string_BU1;
Else
&string_BU1 = "BUSINESS_UNIT ='" | &BU_OFFICE_LOC | "'" | "," | "BUSINESS_UNIT_GL ='" | &BU_OFFICE_LOC | "'" | "," | "'BUSINESS_UNIT_BI ='" | &BU_OFFICE_LOC | "'" | "," | "'BUSINESS_UNIT_AR ='" | &BU_OFFICE_LOC | "'" | "," | "'BUSINESS_UNIT_PC ='" | &BU_OFFICE_LOC | "'" | ",";
&string_BU2 = &string1 | &string_BU1;
End-If;
&string_add1 = "RESOURCE_SUB_CAT='" | &RESOURCE_SUB_CAT | "'" | "," | "PRODUCT='" | &PRODUCT | "'" | "," | "CUST_ID='" | &CUST_ID | "'" | "," | "DESCR='" | &DESCR | "'";
&string_final = &string_BU2 | &string_add1;
&fileNew.WriteLine(&string_final);
<* If &DUMMY_FIELD = "Y" Then
&string2 = "WHERE intfc_id in ('" | &INTFC_ID | "') AND MESSAGE_SET_NBR <> '13120' AND MESSAGE_NBR <> '2002' ";
&fileNew.WriteLine(&string2);
Else*>
&string2 = "WHERE intfc_id in ('" | &INTFC_ID | "') AND INTFC_LINE_NUM BETWEEN '" | &MIN_INTF | "' and '" | &MAX_INTF | "'";
&fileNew.WriteLine(&string2);
<*End-If;*>
&string3 = "AND PC_INTFC_STATUS ='ERR' AND PC_LOAD_STATUS = 'NEW' AND analysis_type ='PAY' AND ACTIVITY_ID='" | &ACTIVITY_ID | "'" | " AND " | "BUSINESS_UNIT ='" | &BUSINESS_UNIT | "'" | ";";
&fileNew.WriteLine(&string3);
&fileNew.WriteLine(" ");
&fileNew.WriteLine(" ");
&sql_exec = "SELECT COUNT (*) FROM PS_INTFC_PROJ_RES " | &string2 | &string3;
MessageBox(0, "", 0, 0, "sql_exec " | &sql_exec);
SQLExec(&sql_exec, &cntr);

&string4 = "-- Updated " | &cntr | " rows for the above mentioned query --";
&fileNew.WriteLine(&string4);
&fileNew.WriteLine(&CRLF);
&fileNew.WriteLine("---------------");
&fileNew.WriteLine(&CRLF);
End-While;
&NUMOFSCRIPTS = &NUMOFSCRIPTS + 1;
&counter = &counter + 1;
rem End-If;

End-If;
&fileNew.Close();

/*** Send and email ***/
/*If %DbName = "PS89PRDF" Then
SQLExec("select RTrim(STRING_TEXT) from PS_STRINGS_TBL WHERE PROGRAM_ID = 'CCI_INTF' AND STRING_ID = 'EMAIL_FROM' AND STRING_TEXT <> ' '", &emailFrom);
If None(&emailFrom) Or
&emailFrom = "." Then
&emailFrom = %SMTPSender;
End-If;
End-If;*/
REM MOUSUMI &MAIL_TO = " email@removed ";
&MAIL_TO = " email@removed ";
SQLExec("select RTrim(STRING_TEXT) from PS_STRINGS_TBL WHERE PROGRAM_ID = 'CCI_INTF' AND STRING_ID = 'EMAIL_TO' AND STRING_TEXT <> ' '", &emailid);
If None(&emailid) Or
&emailid = "." Then
SQLExec("select emailid from %Table(PSOPRDEFN) where oprid = :1", &oprid, &emailid);
&emailid = RTrim(&emailid);
MessageBox(0, "", 0, 0, "&emailid " | &emailid);
End-If;
&MAIL_TO = &emailid;
&MAIL_FLAGS = 0;

/*SQLExec("select RTrim(STRING_TEXT) from PS_STRINGS_TBL WHERE PROGRAM_ID = 'CCI_INTF' AND STRING_ID = 'EMAIL_CC' AND STRING_TEXT <> ' '", &emailcc);
If None(&emailcc) Or
&emailcc = "." Then
&emailcc = "";
End-If;

&MAIL_CC = &emailcc;
&MAIL_BCC = "";*/

&MAIL_SUBJECT = "Interface exception update scripts –" | &date;
If &cnt_scripts <> "0" Then
&MAIL_TEXT1 = "Hi,";
&MAIL_TEXT2 = " ";
&MAIL_TEXT3 = "Please find the above attached update scripts and run these scripts in Production.";
&MAIL_TEXT4 = " ";
Else
&MAIL_TEXT1 = "Hi,";
&MAIL_TEXT2 = " ";
&MAIL_TEXT3 = "There is no single row to update.";
&MAIL_TEXT4 = " ";
&filename = "Update_script_No_rows_for_update" | ".txt";
&MAIL_SUBJECT = "Interface exception update script having no rows to update for -" | &date;

End-If;
&MAIL_TEXT6 = "Thanks,";
&MAIL_TEXT7 = "CCH-ITPeopleSoftSupport Team";

&MAIL_TEXT = &MAIL_TEXT1 | &CRLF;
&MAIL_TEXT = &MAIL_TEXT | &CRLF | &MAIL_TEXT2;
&MAIL_TEXT = &MAIL_TEXT | &CRLF | &MAIL_TEXT3;
&MAIL_TEXT = &MAIL_TEXT | &CRLF | &MAIL_TEXT4;
&MAIL_TEXT = &MAIL_TEXT | &CRLF | &MAIL_TEXT6;
&MAIL_TEXT = &MAIL_TEXT | &CRLF | &MAIL_TEXT7;
&MAIL_TEXT = &MAIL_TEXT | &CRLF;
rem &pshome = GetEnv("PS_HOME");
rem &pshome = "/datafiles/interfaceout/";
&MAIL_FILES = &file;
&MAIL_TITLES = &filename;
If %DbName = "PS89PRDF" Then
&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &emailFrom);
Else
MessageBox(0, "", 0, 0, "3 " | &ACTIVITY_ID);
&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES);
End-If;
If (&RET = 0) Then
MessageBox(0, "Alert", 0, 0, "Email has been sent.");
Else
MessageBox(0, "Alert", 0, 0, "Unable to send email.");
End-If;

Guest's picture
Guest
Re: Send Email Option In Peoplecode

Do the multiple emails trigger for each row Do Select returns or you are talking about entire process.