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

No replies
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;

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
1 + 4 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.